Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 hi to all.......

Author  Topic 

avijit111@gmail.com
Yak Posting Veteran

55 Posts
Posted - 2009-09-19 : 10:59:50
ALTER PROC ENQUIRY_DETAILS_AVI
(
@company glcompany ,
@locn gllocn ,
@lang_id char(1) ,
@docnofrom varchar(25),
@docnoto varchar(25),
@selection char
)
as
begin

set nocount on

select @company = ltrim(rtrim(@company))
select @locn = ltrim(rtrim(@locn))
select @docnofrom = ltrim(rtrim(@docnofrom))
select @docnoto = ltrim(rtrim(@docnoto))

declare @suffix varchar(4)

select @suffix = right(ltrim(rtrim(@docnofrom)),4)

declare @hostId CMN_HOSTID

select @hostId = convert(char,@@spid)


declare @date datetime,
@enq_date varchar(20)

select @date = getdate()-1
select @enq_date = convert(varchar(20),@date,103)









DELETE ENQUIRY_DETAILS_FN
WHERE host_id = @hostId


insert into ENQUIRY_DETAILS_FN (company, location, enqry_no, enqry_dt, ven_code, indent_no,
enqry_srl, item_code, variant, uom, qty, need_date, last_reply_date,
indent_srl ,host_id)

select ped.company_code, ped.locn_no, ped.enquiry_no,convert(varchar(20),peh.enquiry_date,103),peh.vendor_code,
ped.indent_no, ped.enquiry_serial, ped.stock_no, ped.stock_variant, ped.purchase_uom,
ped.enquired_qty, peh.due_date, peh.reply_date, ped.indent_serial,@hostId
from pur_enquiry_detail ped (NOLOCK),
pur_enquiry_header peh (NOLOCK)
where peh.company_code = ped.company_code
and peh.locn_no = ped.locn_no

and peh.enquiry_no = ped.enquiry_no


and peh.company_code = @company
and peh.locn_no = @locn
and convert(varchar(20),peh.enquiry_date,103)= @enq_date

and ped.enquiry_no between @docnofrom and @docnoto
and substring(ped.enquiry_no,12,4) =@suffix









/********************************




if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[pur_enquiry_detail]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[pur_enquiry_detail]
GO

CREATE TABLE [dbo].[pur_enquiry_detail] (
[company_code] [GLCOMPANY] NOT NULL ,
[locn_no] [GLLOCN] NOT NULL ,
[enquiry_no] [cmn_tranno] NOT NULL ,
[enquiry_serial] [cmn_slno] NOT NULL ,
[indent_no] [cmn_tranno] NULL ,
[indent_serial] [cmn_slno] NULL ,
[stock_type] [IMSTYPE] NOT NULL ,
[stock_no] [IMSSTKNO] NOT NULL ,
[stock_variant] [IMSSTKVAR] NOT NULL ,
[revision_no] [IMSREVNO] NULL ,
[drawing_id] [IMSSDESC] NULL ,
[purchase_uom] [IMSUOMNO] NOT NULL ,
[enquired_qty] [cmn_Pqty] NOT NULL ,
[remarks] [CMN_DESCRIPTION] NULL ,
[created_date] [datetime] NOT NULL ,
[user_id] [cmn_userid] NOT NULL ,
[modified_date] [datetime] NULL ,
[modified_user_id] [cmn_userid] NULL ,
[timestamp] [timestamp] NOT NULL
) ON [PRIMARY]
GO




if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[pur_enquiry_header]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[pur_enquiry_header]
GO

CREATE TABLE [dbo].[pur_enquiry_header] (
[company_code] [GLCOMPANY] NOT NULL ,
[locn_no] [GLLOCN] NOT NULL ,
[enquiry_no] [cmn_tranno] NOT NULL ,
[enquiry_status] [PURFLAG] NOT NULL ,
[vendor_code] [cmn_cust_ven_code] NOT NULL ,
[enquiry_date] [datetime] NOT NULL ,
[reply_date] [datetime] NOT NULL ,
[payment_mode] [PURSDESC] NULL ,
[pay_term_no] [purdoc] NULL ,
[remarks] [CMN_DESCRIPTION] NULL ,
[dealing_emp_no] [empnum] NULL ,
[quality_indicator] [PURFLAG] NOT NULL ,
[schedule_flag] [PURFLAG] NOT NULL ,
[due_date] [datetime] NULL ,
[create_locn_no] [GLLOCN] NOT NULL ,
[created_date] [datetime] NOT NULL ,
[user_id] [cmn_userid] NOT NULL ,
[modified_date] [datetime] NULL ,
[modified_user_id] [cmn_userid] NULL ,
[timestamp] [timestamp] NOT NULL
) ON [PRIMARY]
GO
/*******************************************************/

Dear All,
in out put the enquiry_date not fetching data....problem in conversion datetime ....
still i m getting without below line ...not understanding properly why not getting output...after putting below code...
/*and convert(varchar(20),peh.enquiry_date,103)= @enq_date*/

i m sending dll also ..
please help me out...

webfred
Master Smack Fu Yak Hacker

8781 Posts
Posted - 2009-09-19 : 12:49:02
Looks fine.
Maybe there are no records from one day in the past?

No, you're never too old to Yak'n'Roll if you're too young to die.
Go to Top of Page

avijit111@gmail.com
Yak Posting Veteran

55 Posts
Posted - 2009-09-19 : 13:18:49
there is record ...
Go to Top of Page

avijit111@gmail.com
Yak Posting Veteran

55 Posts
Posted - 2009-09-19 : 13:23:30
how i will configure that datetime record....
please help me out from that...
Go to Top of Page

avijit111@gmail.com
Yak Posting Veteran

55 Posts
Posted - 2009-09-19 : 16:09:42
might be but logic is correct or not that i m confuse because output not showing anything...
Go to Top of Page

webfred
Master Smack Fu Yak Hacker

8781 Posts
Posted - 2009-09-20 : 11:59:07
OK - to see what is going on try this:
Do that select without "and convert(varchar(20),peh.enquiry_date,103)= @enq_date" like you did before.
In the select list add a column that is showing your @enq_date.
Now have a look at the record set returned and compare by your eyes if the @enq_date and the enquiry_date are different or not.

No, you're never too old to Yak'n'Roll if you're too young to die.
Go to Top of Page

avijit111@gmail.com
Yak Posting Veteran

55 Posts
Posted - 2009-09-21 : 10:06:47
ALTER PROC ENQUIRY_DETAILS_AVI
(
@company glcompany,
@locn gllocn,
@lang_id char(1)
--@docnofrom varchar(25),
--@docnoto varchar(25),
--@selection char
)
as
begin

set nocount on

select @company = ltrim(rtrim(@company))
select @locn = ltrim(rtrim(@locn))
--select @docnofrom = ltrim(rtrim(@docnofrom))
--select @docnoto = ltrim(rtrim(@docnoto))
declare @from_date datetime,
@to_date datetime
select @from_date = getdate()
select @to_date = getdate()





--declare @suffix varchar(4)

--select @suffix = right(ltrim(rtrim(@from_date)),4)

declare @hostId CMN_HOSTID
select @hostId = convert(char,@@spid)

DELETE ENQUIRY_DETAILS_FN
WHERE host_id = @hostId

insert into ENQUIRY_DETAILS_FN (company, location, enqry_no, enqry_dt, ven_code, indent_no,
enqry_srl, item_code, variant, uom, qty, need_date, last_reply_date,
indent_srl ,host_id)

select *

select ped.company_code, ped.locn_no, ped.enquiry_no, peh.enquiry_date, peh.vendor_code,
ped.indent_no, ped.enquiry_serial, ped.stock_no, ped.stock_variant, ped.purchase_uom,
ped.enquired_qty, peh.due_date, peh.reply_date, ped.indent_serial,@hostId
from pur_enquiry_detail ped (NOLOCK),
pur_enquiry_header peh (NOLOCK)
where ped.company_code = peh.company_code
and ped.locn_no = peh.locn_no
and ped.enquiry_no = peh.enquiry_no
and ped.company_code = @company
and ped.locn_no = @locn
--and ped.enquiry_no between @docnofrom and @docnoto
and peh.enquiry_date between @from_date and @to_date
--and substring(ped.enquiry_no,12,4) =@suffix





update ENQUIRY_DETAILS_FN
set ven_name = pvm.vendor_name,
add1 = pvm.vendor_add1,
add2 = pvm.vendor_add2,
add3 = pvm.vendor_add3,
email = pvm.telex_no,
city = pvm.city,
state = pvm.state,
country = pvm.country,
pin_code = pvm.zip_code
from common..pur_company_vendor_master pvm (NOLOCK),
ENQUIRY_DETAILS_FN enq (NOLOCK)
where enq.host_id = @hostId
AND enq.company = pvm.company_code
and enq.ven_code = pvm.vendor_code

update ENQUIRY_DETAILS_FN
set indent_dt = pih.indent_date,
dept = pih.purpose
from pomdb..pur_indent_header pih (NOLOCK),
ENQUIRY_DETAILS_FN enq (NOLOCK)
where enq.host_id = @hostId
AND enq.company = pih.company_code
and enq.location = pih.locn_no
and enq.indent_no = pih.indent_no

update ENQUIRY_DETAILS_FN
set department = substring(UPPER(a.param_value),1,4)
from pomdb..pur_comp_parameter_detail a (NOLOCK), ENQUIRY_DETAILS_FN b
where B.host_id = @hostId
AND a.company_code = @company
and a.param_class = 'USRDFL'
and a.param_cat = 'POM'
and a.param_type = 'WUSD'
and a.param_code = b.dept

update ENQUIRY_DETAILS_FN
set pouds_item_des1 = ltrim(rtrim(UM.M4)),
pouds_item_des2 = ltrim(rtrim(UM.M5))
from vvk.UDSNEW0000000507_M UM (NOLOCK),
ENQUIRY_DETAILS_FN en (NOLOCK)
where en.host_id = @hostId
AND um.location_code = en.location
and substring(um.key_value,21,15) = en.indent_no
and um.M1 = en.indent_srl
and um.M2 = en.item_code

select * from ENQUIRY_DETAILS_FN (NOLOCK)
order by enqry_no,enqry_srl

set nocount off
end
************************
I would like to get current date data..
like 18.09.2009 date data only show in the output....means current date data only i want to show....
please configure me above coding like that....

with regards and love avijit...
Go to Top of Page

avijit111@gmail.com
Yak Posting Veteran

55 Posts
Posted - 2009-09-21 : 10:09:35
same I m sending bcoz forgot to give you dll...


ALTER PROC ENQUIRY_DETAILS_AVI
(
@company glcompany,
@locn gllocn,
@lang_id char(1)
--@docnofrom varchar(25),
--@docnoto varchar(25),
--@selection char
)
as
begin

set nocount on

select @company = ltrim(rtrim(@company))
select @locn = ltrim(rtrim(@locn))
--select @docnofrom = ltrim(rtrim(@docnofrom))
--select @docnoto = ltrim(rtrim(@docnoto))
declare @from_date datetime,
@to_date datetime
select @from_date = getdate()
select @to_date = getdate()





--declare @suffix varchar(4)

--select @suffix = right(ltrim(rtrim(@from_date)),4)

declare @hostId CMN_HOSTID
select @hostId = convert(char,@@spid)

DELETE ENQUIRY_DETAILS_FN
WHERE host_id = @hostId

insert into ENQUIRY_DETAILS_FN (company, location, enqry_no, enqry_dt, ven_code, indent_no,
enqry_srl, item_code, variant, uom, qty, need_date, last_reply_date,
indent_srl ,host_id)

select *

select ped.company_code, ped.locn_no, ped.enquiry_no, peh.enquiry_date, peh.vendor_code,
ped.indent_no, ped.enquiry_serial, ped.stock_no, ped.stock_variant, ped.purchase_uom,
ped.enquired_qty, peh.due_date, peh.reply_date, ped.indent_serial,@hostId
from pur_enquiry_detail ped (NOLOCK),
pur_enquiry_header peh (NOLOCK)
where ped.company_code = peh.company_code
and ped.locn_no = peh.locn_no
and ped.enquiry_no = peh.enquiry_no
and ped.company_code = @company
and ped.locn_no = @locn
--and ped.enquiry_no between @docnofrom and @docnoto
and peh.enquiry_date between @from_date and @to_date
--and substring(ped.enquiry_no,12,4) =@suffix





update ENQUIRY_DETAILS_FN
set ven_name = pvm.vendor_name,
add1 = pvm.vendor_add1,
add2 = pvm.vendor_add2,
add3 = pvm.vendor_add3,
email = pvm.telex_no,
city = pvm.city,
state = pvm.state,
country = pvm.country,
pin_code = pvm.zip_code
from common..pur_company_vendor_master pvm (NOLOCK),
ENQUIRY_DETAILS_FN enq (NOLOCK)
where enq.host_id = @hostId
AND enq.company = pvm.company_code
and enq.ven_code = pvm.vendor_code

update ENQUIRY_DETAILS_FN
set indent_dt = pih.indent_date,
dept = pih.purpose
from pomdb..pur_indent_header pih (NOLOCK),
ENQUIRY_DETAILS_FN enq (NOLOCK)
where enq.host_id = @hostId
AND enq.company = pih.company_code
and enq.location = pih.locn_no
and enq.indent_no = pih.indent_no

update ENQUIRY_DETAILS_FN
set department = substring(UPPER(a.param_value),1,4)
from pomdb..pur_comp_parameter_detail a (NOLOCK), ENQUIRY_DETAILS_FN b
where B.host_id = @hostId
AND a.company_code = @company
and a.param_class = 'USRDFL'
and a.param_cat = 'POM'
and a.param_type = 'WUSD'
and a.param_code = b.dept

update ENQUIRY_DETAILS_FN
set pouds_item_des1 = ltrim(rtrim(UM.M4)),
pouds_item_des2 = ltrim(rtrim(UM.M5))
from vvk.UDSNEW0000000507_M UM (NOLOCK),
ENQUIRY_DETAILS_FN en (NOLOCK)
where en.host_id = @hostId
AND um.location_code = en.location
and substring(um.key_value,21,15) = en.indent_no
and um.M1 = en.indent_srl
and um.M2 = en.item_code

select * from ENQUIRY_DETAILS_FN (NOLOCK)
order by enqry_no,enqry_srl

set nocount off
end
************************
I would like to get current date data..
like 18.09.2009 date data only show in the output....means current date data only i want to show....
please configure me above coding like that....

with regards and love avijit..
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[pur_enquiry_detail]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[pur_enquiry_detail]
GO

CREATE TABLE [dbo].[pur_enquiry_detail] (
[company_code] [GLCOMPANY] NOT NULL ,
[locn_no] [GLLOCN] NOT NULL ,
[enquiry_no] [cmn_tranno] NOT NULL ,
[enquiry_serial] [cmn_slno] NOT NULL ,
[indent_no] [cmn_tranno] NULL ,
[indent_serial] [cmn_slno] NULL ,
[stock_type] [IMSTYPE] NOT NULL ,
[stock_no] [IMSSTKNO] NOT NULL ,
[stock_variant] [IMSSTKVAR] NOT NULL ,
[revision_no] [IMSREVNO] NULL ,
[drawing_id] [IMSSDESC] NULL ,
[purchase_uom] [IMSUOMNO] NOT NULL ,
[enquired_qty] [cmn_Pqty] NOT NULL ,
[remarks] [CMN_DESCRIPTION] NULL ,
[created_date] [datetime] NOT NULL ,
[user_id] [cmn_userid] NOT NULL ,
[modified_date] [datetime] NULL ,
[modified_user_id] [cmn_userid] NULL ,
[timestamp] [timestamp] NOT NULL
) ON [PRIMARY]
GO




if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[pur_enquiry_header]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[pur_enquiry_header]
GO

CREATE TABLE [dbo].[pur_enquiry_header] (
[company_code] [GLCOMPANY] NOT NULL ,
[locn_no] [GLLOCN] NOT NULL ,
[enquiry_no] [cmn_tranno] NOT NULL ,
[enquiry_status] [PURFLAG] NOT NULL ,
[vendor_code] [cmn_cust_ven_code] NOT NULL ,
[enquiry_date] [datetime] NOT NULL ,
[reply_date] [datetime] NOT NULL ,
[payment_mode] [PURSDESC] NULL ,
[pay_term_no] [purdoc] NULL ,
[remarks] [CMN_DESCRIPTION] NULL ,
[dealing_emp_no] [empnum] NULL ,
[quality_indicator] [PURFLAG] NOT NULL ,
[schedule_flag] [PURFLAG] NOT NULL ,
[due_date] [datetime] NULL ,
[create_locn_no] [GLLOCN] NOT NULL ,
[created_date] [datetime] NOT NULL ,
[user_id] [cmn_userid] NOT NULL ,
[modified_date] [datetime] NULL ,
[modified_user_id] [cmn_userid] NULL ,
[timestamp] [timestamp] NOT NULL
) ON [PRIMARY]
GO
please try to help me out.....
Go to Top of Page

avijit111@gmail.com
Yak Posting Veteran

55 Posts
Posted - 2009-09-26 : 09:20:09
hi to all...

In the below procedure i m trying to get current date data. but i think my procedure are not correct. actually i tried to implement but not getting any data...before one day data is there in the database but my output getting blank.I think conversion process was not correct...please try to help me out from that ....i m sending proc and dll also ... Im trying get from (po_date).. only current date purchase order show from po_date ....

help me out....pls

create proc purchase_ord_mail




@company glcompany,
@locn gllocn,
@lang_id char(1)

as
begin
-- declare @today datetime

--select @today = convert(datetime,getdate()-2,104)

SET NOCOUNT ON

select @company = ltrim(rtrim(@company))
select @locn = ltrim(rtrim(@locn))




create table #po_main
(
company glcompany,
location gllocn,
order_index int,
po_no varchar(30),
po_dt datetime,
rfq_no varchar(30),
rfq_dt datetime,
qtn_no varchar(30),
qtn_dt datetime,
pur_req_no varchar(30),
pur_req_dt datetime,
delivery_dt datetime,
po_cell varchar(20),
carrier_name varchar(80),
ven_code varchar(30),
ven_desc varchar(70),
add1 varchar(50),
add2 varchar(50),
add3 varchar(50),
city varchar(50),
zip varchar(20),
po_srl_no cmn_slno,
item_code varchar(40),
item_des1 varchar(255),
item_des2 varchar(255),
variant varchar(15),
ex_tax_code varchar(15),
ex_tax_desc varchar(100),
cst_tax_code varchar(18),
cst_tax_desc varchar(60),
gst_tax_code varchar(18),
gst_tax_desc varchar(60),
pac_tax_code varchar(18),
pac_tax_desc varchar(60),
frt_tax_code varchar(18),
frt_tax_desc varchar(60),
han_tax_code varchar(18),
han_tax_desc varchar(60),
inc_tax_code varchar(18),
inc_tax_desc varchar(60),
tax_code varchar(18),
cust_tax_code varchar(18),
oc_tax_code varchar(18),
oc_tax_desc varchar(60),

disp_tax_code varchar(15),
disp_tax_type varchar(18),
disp_cal_discount numeric(10,2),
display_discount numeric(10,2),
tax_desc varchar(60),
tax_type varchar(15),
rate_basis varchar(45),
uom varchar(15),
qty numeric(10,2) default 0.0,
rate numeric(10,2) default 0.0,
discount numeric(10,2) default 0.0,
item_value numeric(12,2) default 0.0,
total_value numeric(14,2) default 0.0,
currency varchar(10),
exchng_rate numeric(7,2) default 0.0,
pay_term_code varchar(20),
pay_term_desc varchar(60),
departmentcode varchar(20),
departmentdesc varchar(60),
comment varchar(255),
requester empnum null,
requester_name varchar(40) null,
vend_type varchar(20) null,
vend_GST_no varchar(40) null,
vend_CST_no varchar(40) null,
VAT_tax_code varchar(18),
VAT_tax_desc varchar(60),
vend_tin_no varchar(30),
po_catagoty varchar(10)
)

insert into #po_main (company, location, po_no, po_dt, po_srl_no, item_code,
variant, uom, qty, rate, item_value, currency,
exchng_rate, qtn_no, ven_code, pay_term_code, delivery_dt,
comment, rate_basis, order_index, departmentcode, po_catagoty
)
select pd.company_code, pd.locn_no, pd.po_no, ph.po_date,
pd.po_serial, pd.stock_no, pd.stock_variant,
pd.purchase_uom, pd.ordered_qty_puom, pd.rate,
pd.basic_value, ph.tr_currency_code, ph.tr_exchange_rate,
ph.quot_no, ph.vendor_code, ph.pay_term_no, ph.delivery_date,
ph.remarks, ph.fob_point, 1, ph.where_used, ph.po_category
from pomdb..pur_po_detail pd (nolock),
pomdb..pur_po_header ph (nolock)
where pd.company_code = ph.company_code
and pd.locn_no = ph.locn_no
and pd.po_no = ph.po_no
and pd.company_code = @company
and pd.locn_no = @locn
--and ph.po_date = convert(datetime,substring(convert(char(20),@today,101),1,10),101)



------------------- Filter Begin ---------------------------

If ltrim(rtrim(@fromdocno)) != ''
begin
delete from #po_main
where po_no not between ltrim(rtrim(@fromdocno)) and ltrim(rtrim(@todocno))
end
------------------- Filter End ---------------------------

update #po_main
set ven_desc = cvm.vendor_name,
add1 = cvm.vendor_add1,
add2 = cvm.vendor_add2,
add3 = cvm.vendor_add3,
city = cvm.city,
zip = cvm.zip_code
from common..pur_company_vendor_master cvm (nolock),
#po_main pm
where pm.company = cvm.company_code
and pm.ven_code = cvm.vendor_code



update #po_main
set vend_tin_no = hdr.tin
from common..ivat_pom_vendor_hdr hdr (nolock),
#po_main pm
where pm.company = hdr.company_code
and pm.location = hdr.location_no
and pm.ven_code = hdr.vendor_code




update #po_main
set rfq_no = pq.enquiry_no
from pomdb..pur_quot_header pq (nolock),
#po_main pm
where pm.company = pq.company_code
and pm.location = pq.locn_no
and pm.ven_code = pq.vendor_code
and pm.qtn_no = pq.quot_no


update #po_main
set qtn_no = pq.offer_no,
qtn_dt = pq.offer_date
from pomdb..pur_quot_header pq (nolock),
#po_main pm
where pm.company = pq.company_code
and pm.location = pq.locn_no
and pm.ven_code = pq.vendor_code
and pm.qtn_no = pq.quot_no


update #po_main
set rfq_dt = pe.enquiry_date
from pomdb..pur_enquiry_header pe (nolock),
#po_main pm
where pm.company = pe.company_code
and pm.location = pe.locn_no
and pm.ven_code = pe.vendor_code
and pm.rfq_no = pe.enquiry_no


update #po_main
set pur_req_no = ppi.indent_no
from pomdb..pur_po_indent ppi (nolock),
#po_main pm
where pm.company = ppi.company_code
and pm.location = ppi.locn_no
and pm.po_no = ppi.po_no
and pm.po_srl_no = ppi.po_serial


update #po_main
set pur_req_dt = pih.indent_date
from pomdb..pur_indent_header pih (nolock),
#po_main pm
where pm.company = pih.company_code
and pm.location = pih.locn_no
and pm.pur_req_no = pih.indent_no

update #po_main
set requester = a.req_emp_no
from pomdb..pur_indent_header a (nolock), #po_main b
where a.indent_no = b.pur_req_no

update #po_main
set requester_name = rtrim(ISNULL(H04_First_Name,''))+' '+rtrim(ISNULL(H04_Middle_Name,''))+' '+rtrim(ISNULL(H04_Last_Name,''))
from common..h04_namemaster a (nolock), #po_main b
where a.C03_Organisation_code = @company
and a.H01_emp_num = b.requester

update #po_main
set vend_type = ud.H1002
from ramco.UDSNEW0000000548 ud (nolock),
#po_main pm
where pm.company = ud.company_code
and pm.location = ud.location_code
and pm.ven_code = ud.H1001

update #po_main
set vend_GST_no = ad.Gst_no
from scl_vendor_adnl_info_hdr ad (nolock),
#po_main pm
where pm.company = ad.company_code
and pm.location = ad.locn_code
and pm.ven_code = ad.vendor_code

update #po_main
set vend_CST_no = ad.Cst_no
from scl_vendor_adnl_info_hdr ad (nolock),
#po_main pm
where pm.company = ad.company_code
and pm.location = ad.locn_code
and pm.ven_code = ad.vendor_code


select company_code, locn_no, po_no, po_serial, tax_serial, adjust_code,
adjust_type, adjust_value
into #tmp1
from pomdb..pur_po_adjust (nolock)
where po_no between @fromdocno and @todocno
order by po_no, tax_serial






update #po_main
set tax_code = t1.adjust_code,
tax_type = t1.adjust_type,
discount = t1.adjust_value
from #tmp1 t1,
#po_main pm
where pm.location = t1.locn_no
and pm.po_no = t1.po_no
and t1.adjust_type = 'D'
and pm.order_index = 1
and pm.po_srl_no = CASE
WHEN t1.po_serial = 0 THEN pm.po_srl_no
ELSE
t1.po_serial
END


update #po_main
set oc_tax_code = t1.adjust_code

from #tmp1 t1, #po_main pm
where pm.location = t1.locn_no
and pm.po_no = t1.po_no
and t1.adjust_code like 'OC%'

update #po_main
set disp_tax_code = t1.adjust_code,
disp_tax_type = t1.adjust_type
from #tmp1 t1,
#po_main pm
where pm.location = t1.locn_no
and pm.po_no = t1.po_no
and t1.adjust_type = 'D'

and pm.po_srl_no = CASE
WHEN t1.po_serial = 0 THEN pm.po_srl_no
ELSE
t1.po_serial
END



update #po_main
set oc_tax_desc = po.adjust_desc
from pur_adjust_master po (nolock),
#po_main pm
where pm.company = po.company_code
and pm.location = po.locn_no
and pm.oc_tax_code = po.adjust_code
and pm.tax_type is not null


update #po_main
set tax_desc = po.adjust_desc
from pur_adjust_master po (nolock),
#po_main pm
where pm.company = po.company_code
and pm.location = po.locn_no
and pm.tax_code = po.adjust_code
and pm.tax_type is not null




update #po_main
set disp_cal_discount = (CASE WHEN disp_tax_type = 'D' THEN convert(numeric(10,2),substring(disp_tax_code,2,4),0) END)
from #po_main
where po_no between @fromdocno and @todocno





update #po_main
set display_discount = CASE WHEN disp_tax_type = 'D' THEN (disp_cal_discount /100 ) END

update #po_main
set display_discount = 0
where display_discount is null



update #po_main

set item_value = ((qty*rate)*(100-display_discount))/100




update #po_main
set ex_tax_code = pa.adjust_code
from pomdb..pur_po_adjust pa (nolock),
#po_main pm
where pm.location = pa.locn_no
and pm.po_no = pa.po_no
and pa.adjust_type = 'T'
and pm.order_index = 1
and pa.adjust_code like 'E%'



update #po_main
set ex_tax_desc = po.adjust_desc
from pur_adjust_master po (nolock),
#po_main pm
where pm.company = po.company_code
and pm.location = po.locn_no
and pm.ex_tax_code = po.adjust_code

update #po_main
set ex_tax_desc = rtrim(ltrim(ex_tax_desc))+' + '+rtrim(ltrim(po.adjust_desc))
from pur_adjust_master po (nolock),
#tmp1 pm
where pm.company_code = po.company_code
and pm.locn_no = po.locn_no
and substring(pm.adjust_code,1,2) = 'CE'
and pm.adjust_code = po.adjust_code

update #po_main
set ex_tax_desc = rtrim(ltrim(ex_tax_desc))+' + '+rtrim(ltrim(po.adjust_desc))
from pur_adjust_master po (nolock),
#tmp1 pm
where pm.company_code = po.company_code
and pm.locn_no = po.locn_no
and substring(pm.adjust_code,1,4) = 'HCE1'
and pm.adjust_code = po.adjust_code

update #po_main
set ex_tax_desc = rtrim(ltrim(ex_tax_desc))+' + '+rtrim(ltrim(po.adjust_desc))
from pur_adjust_master po (nolock),
#tmp1 pm
where pm.company_code = po.company_code
and pm.locn_no = po.locn_no
and substring(pm.adjust_code,1,4) = 'HC16'
and pm.adjust_code = po.adjust_code



update #po_main
set ex_tax_desc = rtrim(ltrim(ex_tax_desc))+' + '+rtrim(ltrim(po.adjust_desc))
from pur_adjust_master po (nolock),
#tmp1 pm
where pm.company_code = po.company_code
and pm.locn_no = po.locn_no
and substring(pm.adjust_code,1,4) = 'HC14'
and pm.adjust_code = po.adjust_code

update #po_main
set ex_tax_desc = rtrim(ltrim(ex_tax_desc))+' + '+rtrim(ltrim(po.adjust_desc))
from pur_adjust_master po (nolock),
#tmp1 pm
where pm.company_code = po.company_code
and pm.locn_no = po.locn_no
and substring(pm.adjust_code,1,4) = 'HCE0'
and pm.adjust_code = po.adjust_code

update #po_main
set ex_tax_desc = rtrim(ltrim(ex_tax_desc))+' + '+rtrim(ltrim(po.adjust_desc))
from pur_adjust_master po (nolock),
#tmp1 pm
where pm.company_code = po.company_code
and pm.locn_no = po.locn_no
and substring(pm.adjust_code,1,4) = 'HC08'
and pm.adjust_code = po.adjust_code


update #po_main
set cst_tax_code = pa.adjust_code
from pomdb..pur_po_adjust pa (nolock),
#po_main pm
where pm.location = pa.locn_no
and pm.po_no = pa.po_no
and pa.adjust_type = 'T'
and pm.order_index = 1
and pa.adjust_code like 'C%'
and substring(pa.adjust_code,1,2) != 'CE'



update #po_main
set cst_tax_desc = po.adjust_desc
from pur_adjust_master po (nolock),
#po_main pm
where pm.company = po.company_code
and pm.location = po.locn_no
and pm.cst_tax_code = po.adjust_code

update #po_main
set gst_tax_code = pa.adjust_code
from pomdb..pur_po_adjust pa (nolock),
#po_main pm
where pm.location = pa.locn_no
and pm.po_no = pa.po_no
and pa.adjust_type = 'T'
and pm.order_index = 1
and pa.adjust_code like 'G%'





update #po_main
set gst_tax_desc = po.adjust_desc
from pur_adjust_master po (nolock),
#po_main pm
where pm.company = po.company_code
and pm.location = po.locn_no
and pm.gst_tax_code = po.adjust_code


update #po_main
set pac_tax_code = pa.adjust_code
from pomdb..pur_po_adjust pa (nolock),
#po_main pm
where pm.location = pa.locn_no
and pm.po_no = pa.po_no
and pa.adjust_type in ('C','T')
and pm.order_index = 1
and pa.adjust_code like 'P%'





update #po_main
set pac_tax_desc = po.adjust_desc
from pur_adjust_master po (nolock),
#po_main pm
where pm.company = po.company_code
and pm.location = po.locn_no
and pm.pac_tax_code = po.adjust_code


update #po_main
set frt_tax_code = pa.adjust_code
from pomdb..pur_po_adjust pa (nolock),
#po_main pm
where pm.location = pa.locn_no
and pm.po_no = pa.po_no
and pa.adjust_type = 'T'
and pm.order_index = 1
and pa.adjust_code like 'F%'





update #po_main
set frt_tax_desc = po.adjust_desc
from pur_adjust_master po (nolock),
#po_main pm
where pm.company = po.company_code
and pm.location = po.locn_no
and pm.frt_tax_code = po.adjust_code


update #po_main
set han_tax_code = pa.adjust_code
from pomdb..pur_po_adjust pa (nolock),
#po_main pm
where pm.location = pa.locn_no
and pm.po_no = pa.po_no
and pa.adjust_type = 'C'
and pm.order_index = 1
and pa.adjust_code like 'H%'





update #po_main
set han_tax_desc = po.adjust_desc
from pur_adjust_master po (nolock),
#po_main pm
where pm.company = po.company_code
and pm.location = po.locn_no
and pm.han_tax_code = po.adjust_code



update #po_main
set inc_tax_code = pa.adjust_code
from pomdb..pur_po_adjust pa (nolock),
#po_main pm
where pm.location = pa.locn_no
and pm.po_no = pa.po_no
and pa.adjust_type = 'C'
and pm.order_index = 1
and pa.adjust_code like 'I%'




update #po_main
set inc_tax_desc = po.adjust_desc
from pur_adjust_master po (nolock),
#po_main pm
where pm.company = po.company_code
and pm.location = po.locn_no
and pm.inc_tax_code = po.adjust_code


update #po_main
set VAT_tax_code = pa.adjust_code
from pomdb..pur_po_adjust pa (nolock),
#po_main pm
where pm.location = pa.locn_no
and pm.po_no = pa.po_no
and pa.adjust_type = 'T'
and pm.order_index = 1
and SUBSTRING(adjust_code,1,2) in('VV','VN','VA')

update #po_main
set VAT_tax_desc = po.adjust_desc
from pur_adjust_master po (nolock),
#po_main pm
where pm.company = po.company_code
and pm.location = po.locn_no
and pm.VAT_tax_code = po.adjust_code



update #po_main
set VAT_tax_desc = rtrim(ltrim(VAT_tax_desc))+' + '+rtrim(ltrim(po.adjust_desc))
from pur_adjust_master po (nolock),
#po_main pm
where pm.company = po.company_code
and pm.location = po.locn_no
and SUBSTRING(po.adjust_code,1,3) in ('VA1')
and pm.VAT_tax_code = 'VV400'

update #po_main
set VAT_tax_desc = rtrim(ltrim(VAT_tax_desc))+' + '+rtrim(ltrim(po.adjust_desc))
from pur_adjust_master po (nolock),
#po_main pm
where pm.company = po.company_code
and pm.location = po.locn_no
and SUBSTRING(po.adjust_code,1,3) in ('VA2')
and pm.VAT_tax_code = 'VV1250'

update #po_main
set VAT_tax_desc = rtrim(ltrim(VAT_tax_desc))+' + '+rtrim(ltrim(po.adjust_desc))
from pur_adjust_master po (nolock),
#po_main pm
where pm.company = po.company_code
and pm.location = po.locn_no
and SUBSTRING(po.adjust_code,1,4) in ('VAN1')
and pm.VAT_tax_code = 'VN400'

update #po_main
set VAT_tax_desc = rtrim(ltrim(VAT_tax_desc))+' + '+rtrim(ltrim(po.adjust_desc))
from pur_adjust_master po (nolock),
#po_main pm
where pm.company = po.company_code
and pm.location = po.locn_no
and SUBSTRING(po.adjust_code,1,4) in ('VAN2')
and pm.VAT_tax_code = 'VN1250'

update #po_main
set VAT_tax_desc = rtrim(ltrim(VAT_tax_desc))+' + '+rtrim(ltrim(po.adjust_desc))
from pur_adjust_master po (nolock),
#po_main pm
where pm.company = po.company_code
and pm.location = po.locn_no
and SUBSTRING(po.adjust_code,1,3) in ('VAF')
and pm.VAT_tax_code = 'VVFLAT'

update #po_main
set VAT_tax_desc = rtrim(ltrim(VAT_tax_desc))+' + '+rtrim(ltrim(po.adjust_desc))
from pur_adjust_master po (nolock),
#po_main pm
where pm.company = po.company_code
and pm.location = po.locn_no
and SUBSTRING(po.adjust_code,1,3) in ('VNF')
and pm.VAT_tax_code = 'VANFLA'


update #po_main
set total_value = ph.po_total_value
from #po_main t,
pomdb..pur_po_header ph (nolock)
where ph.company_code = t.company
and ph.locn_no = t.location
and ph.po_no = t.po_no


update #po_main
set cust_tax_code = substring(tax_code,1,1)


update #po_main
set pur_req_no = substring(pur_req_no,5,12)


update #po_main
set item_des1 = ltrim(rtrim(UM.M6)),
item_des2 = ltrim(rtrim(UM.M7))
from vvk.UDSNEW0000000509_M UM (nolock),
#po_main PM
where pm.location = um.location_code
and pm.po_no = substring(um.key_value,21,15)
and pm.po_srl_no = um.M1
and pm.item_code = um.M2


update #po_main
set item_des1 = ltrim(rtrim(UM.STOCK_DESC)),
item_des2 = ''
from pur_po_adhoc_stock UM (nolock),
#po_main PM
where PM.company = UM.company_code
AND pm.location = um.locn_no
AND PM.ITEM_des1 is null
and pm.po_no = um.PO_NO
and pm.po_srl_no = um.PO_SERIAL




update #po_main
set po_cell = ltrim(rtrim(um.H1004))
from vvk.UDSNEW0000000509 UM (nolock),
#po_main PM
where um.location_code = pm.location
and UM.H1002 = pm.po_no


update #po_main
set carrier_name = ltrim(rtrim(um.H1003))
from vvk.UDSNEW0000000509 UM (nolock),
#po_main PM
where um.location_code = pm.location
and UM.H1002 = pm.po_no


update #po_main
set pay_term_desc = te.pay_term_desc
from pomdb..pur_payterm_header te (nolock),
#po_main pm
where te.pay_term_no = pm.pay_term_code
and te.locn_no = pm.location


update #po_main
set departmentdesc = prm.param_value
from pomdb..pur_comp_parameter_detail prm (nolock),
#po_main pm
where pm.departmentcode = prm.param_code
and param_type = 'WUSD'
and param_class = 'USRDFL'
and param_cat = 'POM'






declare @VAT_HEAD varchar(150)
select @VAT_HEAD = (select distinct 'Tax Invoice as per Rule 42 of Gujarat VAT rules 2006 must be issued else the amount of tax shall not be paid.'
from #po_main
where VAT_tax_code is not null)


select *, @VAT_HEAD vat_head
from #po_main
order by po_no, order_index, po_srl_no





SET NOCOUNT OFF
end



/***********************************************


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[pur_po_detail]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[pur_po_detail]
GO

CREATE TABLE [dbo].[pur_po_detail] (
[company_code] [GLCOMPANY] NOT NULL ,
[locn_no] [GLLOCN] NOT NULL ,
[po_no] [cmn_tranno] NOT NULL ,
[po_serial] [cmn_slno] NOT NULL ,
[quot_serial] [cmn_slno] NULL ,
[stock_type] [IMSTYPE] NOT NULL ,
[stock_no] [IMSSTKNO] NOT NULL ,
[stock_variant] [IMSSTKVAR] NOT NULL ,
[revision_no] [IMSREVNO] NULL ,
[bom_no] [purdoc] NULL ,
[purchase_uom] [IMSUOMNO] NOT NULL ,
[inspection_flag] [PURFLAG] NOT NULL ,
[ref_doc_no] [cmn_tranno] NULL ,
[ref_doc_serial] [cmn_slno] NULL ,
[ordered_qty_puom] [cmn_Pqty] NOT NULL ,
[received_qty_puom] [cmn_Pqty] NOT NULL ,
[accepted_qty_puom] [cmn_Pqty] NOT NULL ,
[rejected_qty_puom] [cmn_Pqty] NOT NULL ,
[returned_qty_puom] [cmn_Pqty] NOT NULL ,
[moved_qty_puom] [cmn_Pqty] NOT NULL ,
[balance_rec_qty_puom] [cmn_Pqty] NOT NULL ,
[freeze_accept_qty_puom] [cmn_Pqty] NOT NULL ,
[freeze_reject_qty_puom] [cmn_Pqty] NOT NULL ,
[nt_closed_qty_puom] [cmn_Pqty] NOT NULL ,
[tolerance_type] [apflag] NOT NULL ,
[tolerance_percent] [cmn_Pamt] NOT NULL ,
[matching_indicator] [apmatchtype] NOT NULL ,
[matched_qty] [cmn_Pqty] NOT NULL ,
[matched_value] [cmn_Pamt] NOT NULL ,
[matched_status] [apmatchflag] NULL ,
[billed_qty] [cmn_Pqty] NOT NULL ,
[billed_value] [cmn_Pamt] NOT NULL ,
[rate_per] [cmn_Pqty] NOT NULL ,
[rate] [cmn_Prate] NOT NULL ,
[basic_value] [cmn_Pamt] NOT NULL ,
[total_value] [cmn_Pamt] NOT NULL ,
[po_sl_status] [PURFLAG] NOT NULL ,
[created_date] [datetime] NOT NULL ,
[user_id] [cmn_userid] NOT NULL ,
[modified_date] [datetime] NULL ,
[modified_user_id] [cmn_userid] NULL ,
[timestamp] [timestamp] NOT NULL ,
[vat_category] [VAT_CATEGORY_CODE] NULL ,
[vat_class] [VAT_CLASS_CODE] NULL ,
[vat_rate] [cmn_Pamt] NULL ,
[vat_amount] [cmn_Pamt] NULL ,
[vat_service_item] [IMSSTKNO] NULL
) ON [PRIMARY]
GO



if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[pur_po_header]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[pur_po_header]
GO

CREATE TABLE [dbo].[pur_po_header] (
[company_code] [GLCOMPANY] NOT NULL ,
[locn_no] [GLLOCN] NOT NULL ,
[po_no] [cmn_tranno] NOT NULL ,
[amend_no] [cmn_slno] NOT NULL ,
[amend_date] [datetime] NULL ,
[amend_reference] [CMN_DESCRIPTION] NULL ,
[amend_desc] [CMN_DESCRIPTION] NULL ,
[ack_no] [PURSDESC] NULL ,
[ack_date] [datetime] NULL ,
[ack_remarks] [CMN_DESCRIPTION] NULL ,
[po_date] [datetime] NOT NULL ,
[po_type] [PURPOTYPE] NOT NULL ,
[po_category] [PURPOTYPE] NOT NULL ,
[po_mode] [PURPOTYPE] NOT NULL ,
[buyer_code] [purdoc] NULL ,
[module_id] [cmn_slno] NOT NULL ,
[po_status] [PURFLAG] NOT NULL ,
[onetime_vendor_flag] [PURFLAG] NOT NULL ,
[po_close_date] [datetime] NULL ,
[vendor_code] [cmn_cust_ven_code] NOT NULL ,
[payto_vendor] [cmn_cust_ven_code] NOT NULL ,
[pay_term_no] [purdoc] NULL ,
[advance_payable] [cmn_Pamt] NOT NULL ,
[advance_paid] [cmn_Pamt] NOT NULL ,
[schedule_flag] [PURFLAG] NOT NULL ,
[delivery_date] [datetime] NULL ,
[shipment_flag] [PURFLAG] NOT NULL ,
[gi_flag] [PURFLAG] NOT NULL ,
[quality_indicator] [PURFLAG] NOT NULL ,
[base_curr_value] [cmn_Pamt] NOT NULL ,
[po_value] [cmn_Pamt] NOT NULL ,
[po_addnl_value] [cmn_Pamt] NOT NULL ,
[po_total_value] [cmn_Pamt] NOT NULL ,
[tr_currency_code] [GLCURRCODE] NOT NULL ,
[tr_exchange_rate] [cmn_Pexch_rate] NOT NULL ,
[quot_no] [cmn_tranno] NULL ,
[reason] [CMN_DESCRIPTION] NULL ,
[payment_mode] [PURSDESC] NULL ,
[ins_liability] [PURFLAG] NOT NULL ,
[ins_term] [CMN_DESCRIPTION] NULL ,
[ins_amount] [cmn_Pamt] NOT NULL ,
[valid_from] [datetime] NULL ,
[valid_to] [datetime] NULL ,
[allocated_amount] [cmn_Pamt] NOT NULL ,
[remaining_amount] [cmn_Pamt] NOT NULL ,
[lc_applicable_flag] [PURFLAG] NOT NULL ,
[last_nego_date] [datetime] NULL ,
[last_ship_date] [datetime] NULL ,
[trans_ship_flag] [PURFLAG] NOT NULL ,
[part_ship_flag] [PURFLAG] NOT NULL ,
[authorise_user_name] [cmn_username] NULL ,
[authorise_date] [datetime] NULL ,
[prev_po_status] [PURFLAG] NULL ,
[ap_locn_no] [GLLOCN] NULL ,
[fob_point] [PURSDESC] NULL ,
[where_used] [PURSDESC] NULL ,
[payment_flag] [PURFLAG] NOT NULL ,
[freeze_date] [datetime] NULL ,
[inco_terms] [PURSDESC] NULL ,
[fs_fund_number] [FSFUNDNO] NULL ,
[asset_locn] [GLLOCN] NULL ,
[asset_id] [FAASSET] NULL ,
[cc_allocate_flag] [PURFLAG] NOT NULL ,
[remarks] [CMN_DESCRIPTION] NULL ,
[create_locn_no] [GLLOCN] NOT NULL ,
[created_date] [datetime] NOT NULL ,
[user_id] [cmn_userid] NOT NULL ,
[modified_date] [datetime] NULL ,
[modified_user_id] [cmn_userid] NULL ,
[timestamp] [timestamp] NOT NULL ,
[vat_desirable] [PURFLAG] NULL ,
[vat_inclusive] [PURFLAG] NULL ,
[vat_category] [VAT_CATEGORY_CODE] NULL ,
[vat_class] [VAT_CLASS_CODE] NULL
) ON [PRIMARY]
GO


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[pur_company_vendor_master]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[pur_company_vendor_master]
GO

CREATE TABLE [dbo].[pur_company_vendor_master] (
[company_code] [GLCOMPANY] NOT NULL ,
[vendor_code] [cmn_cust_ven_code] NOT NULL ,
[vendor_name] [CMN_NAME] NOT NULL ,
[vendor_add1] [CMN_DESCRIPTION] NULL ,
[vendor_add2] [CMN_DESCRIPTION] NULL ,
[vendor_add3] [CMN_DESCRIPTION] NULL ,
[city] [CMN_DESCRIPTION] NULL ,
[state] [CMN_DESCRIPTION] NULL ,
[country] [CMN_DESCRIPTION] NULL ,
[zip_code] [CMN_DESCRIPTION] NULL ,
[vendor_type] [PURFLAG] NOT NULL ,
[vendor_category] [PURFLAG] NULL ,
[vendor_class] [PURSDESC] NULL ,
[lang_id] [PURFLAG] NOT NULL ,
[tax_group] [PURSDESC] NULL ,
[customer_flag] [PURFLAG] NOT NULL ,
[customer_code] [cmn_cust_ven_code] NULL ,
[vendor_status] [PURFLAG] NOT NULL ,
[approved_flag] [PURFLAG] NOT NULL ,
[phone_no] [PURPHONE] NULL ,
[telex_no] [CMN_NAME] NULL ,
[fax_no] [PURPHONE] NULL ,
[payment_mode] [PURSDESC] NULL ,
[pay_term_no] [purdoc] NULL ,
[reason] [CMN_DESCRIPTION] NULL ,
[freeze_date] [datetime] NULL ,
[currency_code] [GLCURRCODE] NULL ,
[parent_vendor] [cmn_cust_ven_code] NULL ,
[create_locn_no] [GLLOCN] NOT NULL ,
[created_date] [datetime] NOT NULL ,
[user_id] [cmn_userid] NOT NULL ,
[modified_date] [datetime] NULL ,
[modified_user_id] [cmn_userid] NULL ,
[timestamp] [timestamp] NOT NULL ,
[ch_id] [VAT_ID] NULL ,
[ec_id] [VAT_ID] NULL ,
[po_allowed] [PURFLAG] NULL ,
[mo_allowed] [PURFLAG] NULL ,
[check_allowed] [PURFLAG] NULL ,
[vendor_representative] [CMN_NAME] NULL ,
[country_code] [GLCNTCODE] NULL ,
[invoice_subtrantype] [PURSDESC] NULL
) ON [PRIMARY]
GO


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ivat_pom_vendor_hdr]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ivat_pom_vendor_hdr]
GO

CREATE TABLE [dbo].[ivat_pom_vendor_hdr] (
[company_code] [GLCOMPANY] NULL ,
[location_no] [GLLOCN] NULL ,
[vendor_code] [cmn_cust_ven_code] NULL ,
[state_code] [CMN_DESCRIPTION] NULL ,
[tin] [RC_UDS_VC30] NULL
) ON [PRIMARY]
GO



if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[pur_quot_adjust]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[pur_quot_adjust]
GO

CREATE TABLE [dbo].[pur_quot_adjust] (
[company_code] [GLCOMPANY] NOT NULL ,
[locn_no] [GLLOCN] NOT NULL ,
[quot_no] [cmn_tranno] NOT NULL ,
[quot_serial] [cmn_slno] NOT NULL ,
[tax_serial] [cmn_slno] NOT NULL ,
[adjust_code] [cmn_tax_cd] NOT NULL ,
[adjust_type] [PURFLAG] NOT NULL ,
[adjust_flag] [PURFLAG] NOT NULL ,
[flat_indicator] [PURFLAG] NOT NULL ,
[adjust_value] [cmn_Pamt] NOT NULL ,
[formula_id] [fb_variable] NULL ,
[include_stock_tcd] [PURFLAG] NOT NULL ,
[created_date] [datetime] NOT NULL ,
[user_id] [cmn_userid] NOT NULL ,
[modified_date] [datetime] NULL ,
[modified_user_id] [cmn_userid] NULL ,
[timestamp] [timestamp] NOT NULL
) ON [PRIMARY]
GO



if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[pur_enquiry_header]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[pur_enquiry_header]
GO

CREATE TABLE [dbo].[pur_enquiry_header] (
[company_code] [GLCOMPANY] NOT NULL ,
[locn_no] [GLLOCN] NOT NULL ,
[enquiry_no] [cmn_tranno] NOT NULL ,
[enquiry_status] [PURFLAG] NOT NULL ,
[vendor_code] [cmn_cust_ven_code] NOT NULL ,
[enquiry_date] [datetime] NOT NULL ,
[reply_date] [datetime] NOT NULL ,
[payment_mode] [PURSDESC] NULL ,
[pay_term_no] [purdoc] NULL ,
[remarks] [CMN_DESCRIPTION] NULL ,
[dealing_emp_no] [empnum] NULL ,
[quality_indicator] [PURFLAG] NOT NULL ,
[schedule_flag] [PURFLAG] NOT NULL ,
[due_date] [datetime] NULL ,
[create_locn_no] [GLLOCN] NOT NULL ,
[created_date] [datetime] NOT NULL ,
[user_id] [cmn_userid] NOT NULL ,
[modified_date] [datetime] NULL ,
[modified_user_id] [cmn_userid] NULL ,
[timestamp] [timestamp] NOT NULL
) ON [PRIMARY]
GO


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[pur_po_adjust]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[pur_po_adjust]
GO

CREATE TABLE [dbo].[pur_po_adjust] (
[company_code] [GLCOMPANY] NOT NULL ,
[locn_no] [GLLOCN] NOT NULL ,
[po_no] [cmn_tranno] NOT NULL ,
[po_serial] [cmn_slno] NOT NULL ,
[tax_serial] [cmn_slno] NOT NULL ,
[adjust_code] [cmn_tax_cd] NOT NULL ,
[adjust_type] [PURFLAG] NOT NULL ,
[adjust_flag] [PURFLAG] NOT NULL ,
[flat_indicator] [PURFLAG] NOT NULL ,
[adjust_value] [cmn_Pamt] NOT NULL ,
[formula_id] [fb_variable] NULL ,
[include_stock_tcd] [PURFLAG] NOT NULL ,
[created_date] [datetime] NOT NULL ,
[user_id] [cmn_userid] NOT NULL ,
[modified_date] [datetime] NULL ,
[modified_user_id] [cmn_userid] NULL ,
[timestamp] [timestamp] NOT NULL
) ON [PRIMARY]
GO



if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[pur_adjust_master]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[pur_adjust_master]
GO

CREATE TABLE [dbo].[pur_adjust_master] (
[company_code] [GLCOMPANY] NOT NULL ,
[locn_no] [GLLOCN] NOT NULL ,
[adjust_code] [cmn_tax_cd] NOT NULL ,
[adjust_type] [PURFLAG] NOT NULL ,
[adjust_value] [cmn_Pamt] NOT NULL ,
[adjust_desc] [CMN_DESCRIPTION] NOT NULL ,
[formula_id] [fb_variable] NULL ,
[stock_val_incl_flag] [PURFLAG] NOT NULL ,
[pay_vendor_flag] [PURFLAG] NOT NULL ,
[flat_indicator] [PURFLAG] NOT NULL ,
[currency_flag] [PURFLAG] NOT NULL ,
[expense_acct_code] [GLACCOUNT] NULL ,
[active_flag] [PURFLAG] NOT NULL ,
[create_locn_no] [GLLOCN] NOT NULL ,
[created_date] [datetime] NOT NULL ,
[user_id] [cmn_userid] NOT NULL ,
[modified_date] [datetime] NULL ,
[modified_user_id] [cmn_userid] NULL ,
[timestamp] [timestamp] NOT NULL ,
[vatable] [PURFLAG] NULL ,
[act_as_tax] [cmn_flag] NULL
) ON [PRIMARY]

GO



if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[pur_comp_parameter_detail]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[pur_comp_parameter_detail]
GO

CREATE TABLE [dbo].[pur_comp_parameter_detail] (
[company_code] [GLCOMPANY] NOT NULL ,
[param_class] [PURSDESC] NOT NULL ,
[param_cat] [PURSDESC] NOT NULL ,
[param_type] [PURSDESC] NOT NULL ,
[param_code] [PURSDESC] NULL ,
[param_value] [CMN_DESCRIPTION] NOT NULL ,
[param_update] [PURFLAG] NOT NULL ,
[param_desc] [PURTDESC] NULL ,
[param_datatype] [PURFLAG] NOT NULL ,
[param_pattern] [PURSDESC] NULL ,
[param_length] [cmn_slno] NULL ,
[param_sp_name] [sysname] NULL ,
[created_date] [datetime] NOT NULL ,
[user_id] [cmn_userid] NOT NULL ,
[modified_date] [datetime] NULL ,
[modified_user_id] [cmn_userid] NULL ,
[timestamp] [timestamp] NOT NULL ,
[param_id] [int] NULL ,
[add_date] [datetime] NULL ,
[ver_flag] [cmn_flag] NULL
) ON [PRIMARY]
GO

**********************/
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site