ON t_order_hdr
FOR UPDATE
AS
BEGIN
IF EXISTS(Select * from INSERTED where Status = 'A' )
BEGIN
Declare @company_code glcompany,
@locn_code gllocn,
@lang_id smallint,
@so_no varchar(30)
Select @so_no = (Select Order_no from INSERTED)
Select @company_code = (Select company_no from INSERTED)
Select @locn_code = (Select invoice_locn from INSERTED)
IF EXISTS(Select '*' from Saleorder_Edk1 where saleorder_no = @so_no and Mail = 0)
BEGIN
-----------------------------------------------------------
CREATE TABLE #salesorder_som(
company_no GLCOMPANY NULL,
locn_no GLLOCN NULL,
order_no cmn_tranno NULL,
order_srl_no smallint NULL,
item_no IMSSTKNO NULL,
variant IMSSTKVAR NULL,
description som_description NULL,
req_qty cmn_Pqty NULL,
unit_price Numeric(18,2) NULL,
ship_cust_no cmn_cust_ven_code NULL,
ship_address_id som_address_id NULL,
dest_point som_dest_pt NULL,
shipment_mode dms_ship_mode NULL,
carrier_no dms_crr_no NULL,
req_dt datetime NULL,
item_value cmn_Pamt NULL,
ord_cust_no cmn_cust_ven_code NULL,
bill_cust_no cmn_cust_ven_code NULL,
order_amount cmn_Pamt NULL,
freight_ind cmn_flag NULL,
freight_amount cmn_Pamt NULL,
order_discount cmn_Prate NULL,
order_tax cmn_Prate NULL,
order_charge cmn_Prate NULL,
item_discount cmn_Prate NULL,
item_tax cmn_Prate NULL,
item_charge cmn_Prate NULL,
po_no cmn_tranno NULL,
order_dt datetime NULL,
po_dt datetime NULL,
ord_address_id som_address_id NULL,
bill_address_id som_address_id NULL,
tax_no cmn_tax_cd NULL,
tax_amount cmn_Pamt NULL,
disc_no cmn_tax_cd NULL,
disc_amount cmn_Pamt NULL,
crr_name CMN_DESCRIPTION NULL,
ship_add varchar(250) NULL,
bill_add varchar(250) NULL,
order_add varchar(250) NULL,
ship_cust_name varchar(50) NULL,
bill_cust_name varchar(50) NULL,
order_cust_name varchar(50) NULL,
uom varchar(10) NULL,
tax_desc varchar(50) NULL,
Saletype varchar(250) NULL,
Min_Tol numeric(18, 2) NULL,
Max_Tol numeric(18, 2) NULL,
VAT_CST varchar(20) NULL,
Budg_Freight numeric(18, 2) NULL,
Max_Freight numeric(18, 2) NULL,
order_taken varchar(50) NULL,
commision_comp varchar(50) NULL,
Distributor_name varchar(50) NULL,
Payment_remarks varchar(100) NULL,
sale_type varchar(20) NULL,
due_days varchar(50) NULL,
loading_charges varchar(20) NULL,
charge numeric(18, 2) NULL,
basic_rate numeric(18, 2) NULL,
excise numeric(18, 2) NULL,
cst_vat numeric(18, 2) NULL,
freight numeric(18, 2) NULL,
octroi_amt numeric(18, 2) NULL,
loading_amt numeric(18, 0) NULL,
Rate_Type varchar(20) NULL,
Delivery_Address varchar(150) NULL,
basic_rate1 numeric(18, 2) NULL,
Total_qty Numeric(18,3) NULL,
Payment_remarks1 varchar(150) NULL,
)
-----------------------------------------------------------------------------------------------
insert into #salesorder_som (company_no,locn_no,order_no,order_srl_no,item_no,variant,description,req_qty,unit_price,
ship_cust_no,ship_address_id,dest_point,shipment_mode,carrier_no,req_dt,item_value)
select a.company_no,a.locn_no,a.order_no,a.order_srl_no,a.item_no,a.variant,a.description,a.req_qty,a.unit_price,
a.ship_cust_no,a.ship_address_id,a.dest_point,a.shipment_mode,a.carrier_no,a.req_dt,a.item_value
from t_order_line_nos a
where a.company_no = @company_code
and a.locn_no = @locn_code
and a.order_no = @so_no
update a
set a.ord_cust_no = b.ord_cust_no,
a.bill_cust_no = b.bill_cust_no,
a.order_amount = b.order_amount,
a.freight_ind = b.freight_ind,
a.freight_amount = b.freight_amount,
a.order_discount = b.order_discount,
a.order_tax = b.order_tax,
a.order_charge = b.order_charge,
a.item_discount = b.item_discount,
a.item_tax = b.item_tax,
a.item_charge = b.item_charge,
a.po_no = b.po_no,
a.order_dt = b.order_dt,
a.po_dt = b.po_dt,
a.ord_address_id = b.ord_address_id,
a.bill_address_id = b.bill_address_id
from #salesorder_som a, t_order_hdr b
where a.order_no = b.order_no
and a.company_no = b.company_no
and a.locn_no = b.locn_no
update a
set a.tax_no = b.tax_no,
a.tax_amount = b.tax_amount
from #salesorder_som a, t_order_tax b
where a.order_no = b.order_no
update a
set a.disc_no = b.disc_no,
a.disc_amount = b.disc_amount
from #salesorder_som a, t_order_discount b
where a.order_no = b.order_no
update a
set a.crr_name = b.crr_name
from #salesorder_som a,shp..dms_m_carrier_hdr b
where a.carrier_no = b.crr_no
update a
set a.ship_add = b.address_1+' '+b.address_2+' '+b.address_3+' '+b.city+' '+b.state+' '+b.zip_no
from #salesorder_som a,m_cust_address b
where a.ship_cust_no = b.cust_no
and a.ship_address_id = b.address_id
update a
set a.order_add = b.address_1+' '+b.address_2+' '+b.address_3+' '+b.city+' '+b.state+' '+b.zip_no
from #salesorder_som a,m_cust_address b
where a.ord_cust_no = b.cust_no
and a.ord_address_id = b.address_id
update a
set a.bill_add = b.address_1+' '+b.address_2+' '+b.address_3+' '+b.city+' '+b.state+' '+b.zip_no
from #salesorder_som a,m_cust_address b
where a.bill_cust_no = b.cust_no
and a.bill_address_id = b.address_id
update a
set a.ship_cust_name = b.cust_name
from #salesorder_som a,common..sms_cmn_customer b
where a.ship_cust_no = b.cust_no
update a
set a.bill_cust_name = b.cust_name
from #salesorder_som a,common..sms_cmn_customer b
where a.bill_cust_no = b.cust_no
update a
set a.order_cust_name = b.cust_name
from #salesorder_som a,common..sms_cmn_customer b
where a.ord_cust_no = b.cust_no
update a
set a.description = b.description
from #salesorder_som a,common..ims_variant_master b
where a.item_no = b.stock_no
and a.variant = b.variant_no
update a
set a.uom = b.stock_uom
from #salesorder_som a,common..ims_stock_master b
where a.item_no = b.stock_no
update a
set a.tax_desc = b.tcd_desc
from #salesorder_som a,m_tcd b
where a.tax_no = b.tcd_no
and a.locn_no = b.locn_no
update #salesorder_som
set Saletype = a.Saletype,
Min_Tol = a.Min_Tol,
Max_Tol = a.Max_Tol,
VAT_CST = a.VAT_CST,
Budg_Freight = a.Budg_Freight,
Max_Freight = a.Max_Freight,
order_taken = a.order_taken,
commision_comp = a.commision_comp,
Distributor_name = a.Distributor_name,
Payment_remarks = a.Payment_remarks,
sale_type = a.sale_type,
due_days = a.due_days,
loading_charges = a.loading_charges,
charge = a.charge,
Rate_Type = a.Rate_Type,
Delivery_Address = a.Delivery_Address
from saleorder_edk1 a ,#salesorder_som b
where a.saleorder_no = b.order_no
----------------------------------------------------------------------------------------------
Declare @order_no cmn_tranno,
@Frt_SO Numeric(18,2),
@order_litem_no smallint,
@Saletype varchar(150),
@Excise_Duty varchar(2),
@Excise_p Numeric(18,4),
@Octroi varchar(20),
@Octroi_p Numeric(18,4),
@VAT_CST varchar(20),
@VAT_CST_p Numeric(18,4),
@Budg_Freight Numeric(18,2),
@Max_Freight Numeric(18,2),
@unit_rate Numeric(28,8),
@Without_VC Numeric(28,8),
@Without_Excise Numeric(28,8),
@Without_Budg_Freight Numeric(28,8),
@Without_Octroi Numeric(28,8),
@Max_Tol Numeric(28,8)
-------------------------------------------------
Select @Saletype = (Select Saletype from SOM..saleorder_edk1 where saleorder_no = @so_no)
Select @Max_Tol = (Select Max_Tol from SOM..saleorder_edk1 where saleorder_no = @so_no)
Select @Budg_Freight = (Select Budg_Freight from SOM..saleorder_edk1 where saleorder_no = @so_no)
Select @Max_Freight = (Select Max_Freight from SOM..saleorder_edk1 where saleorder_no = @so_no)
Select @Octroi = (Select Octroi from SOM..saleorder_edk1 where saleorder_no = @so_no)
Set @order_no = @so_no
--------------------------------------------------
IF (@Saletype = 'Ex-Factory(Basic Only)')
BEGIN
UPDATE #salesorder_som
SET basic_rate = a.unit_price,
excise = 0,
cst_vat = 0,
freight = 0,
octroi_amt = 0,
loading_amt = 0
FROM #salesorder_som a ,t_order_line_nos b
WHERE a.order_no = b.order_no
and a.item_no = b.item_no
and a.variant = b.variant
END
------------------------------------------------------------------
ELSE IF (@Saletype = 'Ex-Factory(Including Taxes)')
BEGIN
DECLARE lineno_CURSOR CURSOR FOR
Select DISTINCT order_srl_no from #salesorder_som
OPEN lineno_CURSOR
FETCH NEXT
From lineno_CURSOR into @order_litem_no
WHILE @@FETCH_STATUS = 0
BEGIN
Select @unit_rate = 0
Select @Without_VC = 0
Select @Without_Excise = 0
Select @Without_Budg_Freight = 0
Select @Saletype = (Select Saletype from SOM..saleorder_edk1 where saleorder_no = @order_no)
Select @Excise_Duty = (Select Excise_Duty from SOM..saleorder_edk1 where saleorder_no = @order_no)
Select @VAT_CST = (Select VAT_CST from SOM..saleorder_edk1 where saleorder_no = @order_no)
Select @Budg_Freight = (Select Max_Freight from SOM..saleorder_edk1 where saleorder_no = @order_no)
Select @unit_rate = (Select a.unit_price
FROM #salesorder_som a ,t_order_line_nos b
WHERE a.order_no = b.order_no
and a.order_srl_no = b.order_srl_no
and a.order_srl_no = @order_litem_no
and a.item_no = b.item_no
and a.variant = b.variant)
--- VAT/CST----------
IF (@VAT_CST = 'VAT@4%')
BEGIN
Select @Without_VC = (@unit_rate*0.961538462)
Select @VAT_CST_p = 0.04
END
IF (@VAT_CST = 'CST@4%')
BEGIN
Select @Without_VC = (@unit_rate*0.961538462)
Select @VAT_CST_p = 0.04
END
ELSE IF (@VAT_CST = 'VAT@5%')
BEGIN
Select @Without_VC = (@unit_rate*0.952380952)
Select @VAT_CST_p = 0.05
END
ELSE IF (@VAT_CST = 'CST@5%')
BEGIN
Select @Without_VC = (@unit_rate*0.952380952)
Select @VAT_CST_p = 0.05
END
ELSE IF (@VAT_CST = 'CST@2%')
BEGIN
Select @Without_VC = (@unit_rate*0.980392157)
Select @VAT_CST_p = 0.02
END
ELSE IF (@VAT_CST = '0.00%')
BEGIN
Select @Without_VC = @unit_rate
Select @VAT_CST_p = 0.00
END
---------------------
---EXCISE------------
IF(@Excise_Duty = '1')
BEGIN
Select @Without_Excise = (@Without_VC*0.88999644)
Select @Excise_p = 0.1236
END
ELSE IF(@Excise_Duty = '0')
BEGIN
Select @Without_Excise = @Without_VC
Select @Excise_p = 0.0
END
UPDATE #salesorder_som
SET basic_rate = @Without_Excise,
excise = @Without_Excise*@Excise_p,
cst_vat = (@Without_Excise+@Without_Excise*@Excise_p)*@VAT_CST_p,
freight = 0,
octroi_amt = 0,
loading_amt = 0
FROM #salesorder_som a
WHERE a.order_srl_no = @order_litem_no
FETCH NEXT
From lineno_CURSOR into @order_litem_no
END
CLOSE lineno_CURSOR
DEALLOCATE lineno_CURSOR
END
------------------------------------------------------------------
ELSE IF (LTRIM(RTRIM(@Saletype)) = 'F.O.R Price(Total Freight Bourne by Company)')
BEGIN
DECLARE lineno_CURSOR CURSOR FOR
Select DISTINCT order_srl_no from #salesorder_som
OPEN lineno_CURSOR
FETCH NEXT
From lineno_CURSOR into @order_litem_no
WHILE @@FETCH_STATUS = 0
BEGIN
Select @unit_rate = 0
Select @Without_Octroi = 0
Select @Without_Budg_Freight = 0
Select @Without_VC = 0
Select @Without_Excise = 0
Select @Saletype = (Select Saletype from saleorder_edk1 where saleorder_no = @order_no)
Select @Excise_Duty = (Select Excise_Duty from saleorder_edk1 where saleorder_no = @order_no)
Select @VAT_CST = (Select VAT_CST from saleorder_edk1 where saleorder_no = @order_no)
Select @Budg_Freight = (Select Max_Freight from saleorder_edk1 where saleorder_no = @order_no)
Select @unit_rate = (Select a.unit_price
FROM #salesorder_som a ,t_order_line_nos b
WHERE a.order_no = b.order_no
and a.order_srl_no = b.order_srl_no
and a.order_srl_no = @order_litem_no)
---------- OCTROI----------
IF(@Octroi = 'Octroi')
BEGIN
Select @Without_Octroi = (Select @unit_rate*0.969320991)
Select @Octroi_p = 0.03165
END
ELSE IF(@Octroi = 'Entry TAX')
BEGIN
Select @Without_Octroi = (Select @unit_rate*0.99009901)
Select @Octroi_p = 0.01
END
ELSE IF(@Octroi = '0')
BEGIN
Select @Without_Octroi = (Select @unit_rate)
Select @Octroi_p = 0.0
END
---------- Freight----------
Select @Without_Budg_Freight = (@Without_Octroi - @Budg_Freight)
---------- VAT/CST----------
IF (@VAT_CST = 'VAT@4%')
BEGIN
Select @Without_VC = (@Without_Budg_Freight*0.961538462)
Select @VAT_CST_p = 0.04
END
IF (@VAT_CST = 'CST@4%')
BEGIN
Select @Without_VC = (@Without_Budg_Freight*0.961538462)
Select @VAT_CST_p = 0.04
END
ELSE IF (@VAT_CST = 'VAT@5%')
BEGIN
Select @Without_VC = (@Without_Budg_Freight*0.952380952)
Select @VAT_CST_p = 0.05
END
ELSE IF (@VAT_CST = 'CST@5%')
BEGIN
Select @Without_VC = (@Without_Budg_Freight*0.952380952)
Select @VAT_CST_p = 0.05
END
ELSE IF (@VAT_CST = 'CST@2%')
BEGIN
Select @Without_VC = (@Without_Budg_Freight*0.980392157)
Select @VAT_CST_p = 0.02
END
ELSE IF (@VAT_CST = '0.00%')
BEGIN
Select @Without_VC = @Without_Budg_Freight
Select @VAT_CST_p = 0.00
END
---------- EXCISE----------
IF(@Excise_Duty = '1')
BEGIN
Select @Without_Excise = (@Without_VC*0.88999644)
Select @Excise_p = 0.1236
END
ELSE IF(@Excise_Duty = '0')
BEGIN
Select @Without_Excise = @Without_VC
Select @Excise_p = 0.0
END
UPDATE #salesorder_som
SET basic_rate = @Without_Excise,
excise = @Without_Excise*@Excise_p,
cst_vat = (@Without_Excise+@Without_Excise*@Excise_p)*@VAT_CST_p,
freight = @Budg_Freight,
octroi_amt = (@Without_Excise+@Without_Excise*@Excise_p+(@Without_Excise+@Without_Excise*@Excise_p)*@VAT_CST_p+@Budg_Freight)*@Octroi_p,
loading_amt = a.Charge
FROM #salesorder_som a
WHERE a.order_srl_no = @order_litem_no
FETCH NEXT
From lineno_CURSOR into @order_litem_no
END
CLOSE lineno_CURSOR
DEALLOCATE lineno_CURSOR
END
------------------------------------------------------------------
ELSE IF (@Saletype = 'F.O.R Price(Above Maximum Freight Bourne by Party)')
BEGIN
DECLARE lineno_CURSOR CURSOR FOR
Select DISTINCT order_srl_no from #salesorder_som
OPEN lineno_CURSOR
FETCH NEXT
From lineno_CURSOR into @order_litem_no
WHILE @@FETCH_STATUS = 0
BEGIN
Select @unit_rate = 0
Select @Without_Octroi = 0
Select @Without_Budg_Freight = 0
Select @Without_VC = 0
Select @Without_Excise = 0
Select @Saletype = (Select Saletype from SOM..saleorder_edk1 where saleorder_no = @order_no)
Select @Excise_Duty = (Select Excise_Duty from SOM..saleorder_edk1 where saleorder_no = @order_no)
Select @VAT_CST = (Select VAT_CST from SOM..saleorder_edk1 where saleorder_no = @order_no)
Select @Budg_Freight = (Select Max_Freight from SOM..saleorder_edk1 where saleorder_no = @order_no)
Select @unit_rate = (Select b.unit_price
FROM #salesorder_som a ,t_order_line_nos b
WHERE a.order_no = b.order_no
and a.order_srl_no = b.order_srl_no
and a.order_srl_no = @order_litem_no
and a.item_no = b.item_no
and a.variant = b.variant)
---------- OCTROI----------
IF(@Octroi = 'Octroi')
BEGIN
Select @Without_Octroi = (Select @unit_rate*0.969320991)
Select @Octroi_p = 0.03165
END
ELSE IF(@Octroi = 'Entry TAX')
BEGIN
Select @Without_Octroi = (Select @unit_rate*0.99009901)
Select @Octroi_p = 0.01
END
ELSE IF(@Octroi = '0')
BEGIN
Select @Without_Octroi = (Select @unit_rate)
Select @Octroi_p = 0.0
END
---------- Freight----------
Select @Without_Budg_Freight = (@Without_Octroi - @Budg_Freight)
---------- VAT/CST----------
IF (@VAT_CST = 'VAT@4%')
BEGIN
Select @Without_VC = (@Without_Budg_Freight*0.961538462)
Select @VAT_CST_p = 0.04
END
IF (@VAT_CST = 'CST@4%')
BEGIN
Select @Without_VC = (@Without_Budg_Freight*0.961538462)
Select @VAT_CST_p = 0.04
END
ELSE IF (@VAT_CST = 'VAT@5%')
BEGIN
Select @Without_VC = (@Without_Budg_Freight*0.952380952)
Select @VAT_CST_p = 0.05
END
ELSE IF (@VAT_CST = 'CST@5%')
BEGIN
Select @Without_VC = (@Without_Budg_Freight*0.952380952)
Select @VAT_CST_p = 0.05
END
ELSE IF (@VAT_CST = 'CST@2%')
BEGIN
Select @Without_VC = (@Without_Budg_Freight*0.980392157)
Select @VAT_CST_p = 0.02
END
ELSE IF (@VAT_CST = '0.00%')
BEGIN
Select @Without_VC = @Without_Budg_Freight
Select @VAT_CST_p = 0.00
END
---------- EXCISE----------
IF(@Excise_Duty = '1')
BEGIN
Select @Without_Excise = (@Without_VC*0.88999644)
Select @Excise_p = 0.1236
END
ELSE IF(@Excise_Duty = '0')
BEGIN
Select @Without_Excise = @Without_VC
Select @Excise_p = 0.0
END
UPDATE #salesorder_som
SET basic_rate = @Without_Excise,
excise = @Without_Excise*@Excise_p,
cst_vat = (@Without_Excise+@Without_Excise*@Excise_p)*@VAT_CST_p,
freight = @Budg_Freight,
octroi_amt = (@Without_Excise+@Without_Excise*@Excise_p+(@Without_Excise+@Without_Excise*@Excise_p)*@VAT_CST_p+@Budg_Freight)*@Octroi_p,
loading_amt = a.Charge
FROM #salesorder_som a
WHERE a.order_srl_no = @order_litem_no
FETCH NEXT
From lineno_CURSOR into @order_litem_no
END
CLOSE lineno_CURSOR
DEALLOCATE lineno_CURSOR
END
------------------------------------------------------------------
ELSE IF (@Saletype = 'Consignment Sales (Basic + Excise)')
BEGIN
DECLARE lineno_CURSOR CURSOR FOR
Select DISTINCT order_srl_no from #salesorder_som
OPEN lineno_CURSOR
FETCH NEXT
From lineno_CURSOR into @order_litem_no
WHILE @@FETCH_STATUS = 0
BEGIN
Select @unit_rate = 0
Select @Without_Octroi = 0
Select @Without_Budg_Freight = 0
Select @Without_VC = 0
Select @Without_Excise = 0
Select @Saletype = (Select Saletype from SOM..saleorder_edk1 where saleorder_no = @order_no)
Select @Excise_Duty = (Select Excise_Duty from SOM..saleorder_edk1 where saleorder_no = @order_no)
Select @VAT_CST = (Select VAT_CST from SOM..saleorder_edk1 where saleorder_no = @order_no)
Select @Budg_Freight = (Select Max_Freight from SOM..saleorder_edk1 where saleorder_no = @order_no)
Select @unit_rate = (Select a.unit_price
FROM #salesorder_som a ,t_order_line_nos b
WHERE a.order_no = b.order_no
and a.order_srl_no = b.order_srl_no
and a.order_srl_no = @order_litem_no
and a.item_no = b.item_no
and a.variant = b.variant)
---------- EXCISE----------
IF(@Excise_Duty = '1')
BEGIN
Select @Without_Excise = (@unit_rate*0.88999644)
Select @Excise_p = 0.1236
END
ELSE IF(@Excise_Duty = '0')
BEGIN
Select @Without_Excise = @unit_rate
Select @Excise_p = 0.00
END
UPDATE #salesorder_som
SET basic_rate = @Without_Excise,
excise = @Without_Excise*@Excise_p,
cst_vat = 0,
freight = 0,
octroi_amt = 0,
loading_amt = 0
FROM #salesorder_som a
WHERE a.order_srl_no = @order_litem_no
FETCH NEXT
From lineno_CURSOR into @order_litem_no
END
CLOSE lineno_CURSOR
DEALLOCATE lineno_CURSOR
END
------------------------------------------------------------------
ELSE IF (@Saletype = 'Stock Transfer (Basic + Excise)')
BEGIN
DECLARE lineno_CURSOR CURSOR FOR
Select DISTINCT order_srl_no from #salesorder_som
OPEN lineno_CURSOR
FETCH NEXT
From lineno_CURSOR into @order_litem_no
WHILE @@FETCH_STATUS = 0
BEGIN
Select @unit_rate = 0
Select @Without_Octroi = 0
Select @Without_Budg_Freight = 0
Select @Without_VC = 0
Select @Without_Excise = 0
Select @Saletype = (Select Saletype from SOM..saleorder_edk1 where saleorder_no = @order_no)
Select @Excise_Duty = (Select Excise_Duty from SOM..saleorder_edk1 where saleorder_no = @order_no)
Select @VAT_CST = (Select VAT_CST from SOM..saleorder_edk1 where saleorder_no = @order_no)
Select @Budg_Freight = (Select Max_Freight from SOM..saleorder_edk1 where saleorder_no = @order_no)
Select @unit_rate = (Select a.unit_price
FROM #salesorder_som a ,t_order_line_nos b
WHERE a.order_no = b.order_no
and a.order_srl_no = b.order_srl_no
and a.order_srl_no = @order_litem_no
and a.item_no = b.item_no
and a.variant = b.variant)
---------- EXCISE----------
IF(@Excise_Duty = '1')
BEGIN
Select @Without_Excise = (@unit_rate*0.88999644)
Select @Excise_p = 0.1236
END
ELSE IF(@Excise_Duty = '0')
BEGIN
Select @Without_Excise = @unit_rate
Select @Excise_p = 0.00
END
UPDATE #salesorder_som
SET basic_rate = @Without_Excise,
excise = @Without_Excise*@Excise_p,
cst_vat = 0,
freight = 0,
octroi_amt = 0,
loading_amt = 0
FROM #salesorder_som a
WHERE a.order_srl_no = @order_litem_no
FETCH NEXT
From lineno_CURSOR into @order_litem_no
END
CLOSE lineno_CURSOR
DEALLOCATE lineno_CURSOR
END
-----------------------------------------------------------------------------------------------
UPDATE #salesorder_som
Set Basic_Rate1 = Basic_Rate
UPDATE #salesorder_som
Set Basic_Rate1 = Basic_Rate-Max_Freight
From #salesorder_som
Where LTRIM(RTRIM(Saletype)) = 'Stock Transfer (Basic + Excise)'
UPDATE #salesorder_som
Set Basic_Rate1 = Basic_Rate-Max_Freight
From #salesorder_som
Where LTRIM(RTRIM(Saletype)) = 'Consignment Sales (Basic + Excise)'
Declare @Tot_qty Numeric(18,3),
@Rate_Type varchar(50),
@Sale_Type varchar(50),
@Due_Days Numeric(18,0),
@Payment_Terms varchar(50)
--------------------------------------
Select @Tot_qty = 0
Select @Rate_Type = (Select Rate_Type from Saleorder_edk1 where Saleorder_no = @so_no)
IF(LTRIM(RTRIM(@Rate_Type)) = 'GROUP')
BEGIN
Select @Tot_qty = (Select MAX(Req_qty) from #salesorder_som where LTRIM(RTRIM(Rate_Type)) = 'GROUP' group by order_no)
END
IF(LTRIM(RTRIM(@Rate_Type)) = 'Individual')
BEGIN
Select @Tot_qty = (Select SUM(Req_qty) from #salesorder_som where LTRIM(RTRIM(Rate_Type)) = 'Individual' group by order_no)
END
UPDATE #salesorder_som
Set Total_qty = @Tot_qty
----------------------------------------------
Select @Sale_Type = (Select LTRIM(RTRIM(Sale_Type)) from Saleorder_edk1 where Saleorder_no = @so_no)
IF(@Sale_Type = 'Cash Sale')
BEGIN
Select @Payment_Terms = 'Cash Sale.'
END
ELSE IF(@Sale_Type = 'Credit Sale')
BEGIN
Select @Due_Days = (Select Due_days from Saleorder_edk1 where Saleorder_no = @so_no)
Select @Payment_Terms = CAST(@Due_Days AS VARCHAR)+' days'+' Credit Sale.'
END
UPDATE #salesorder_som
Set Payment_remarks1 = @Payment_Terms +' '+Payment_remarks
----------------------------------------------
DECLARE @tableHTML1 NVARCHAR(MAX),
@subjec1 varchar(50)
Select @subjec1 = 'Sale Order ( '+@so_no+' )For Approval'
Set @tableHTML1 =
N'
Respected Sir,
' +N'
Sale Order has been generated today as follows;
' +N'' +
N' ' +
CAST ( ( SELECT distinct td = a.Order_no,'',
td = CONVERT(VARCHAR(11), a.order_dt, 106),''
FROM #salesorder_som as a
where a.order_no = @so_no
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'
| Order No | Order Date |
|---|
N'
' +
N'
' +
N'' +
N' ' +
CAST ( ( SELECT distinct td = a.bill_cust_name,'',
td = a.Total_qty,''
FROM #salesorder_som as a
where a.order_no = @so_no
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'
| Party Name | Total Qty. in MT |
|---|
N'
' +
N'
' +
N'' +
N' ' +
CAST ( ( SELECT distinct td = a.Saletype,'',
td = a.Payment_remarks1,''
FROM #salesorder_som as a
where a.order_no = @so_no
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'
| Order Type | Payment Remarks |
|---|
N'
' +
N'
' +
N'' +
N' ' +
N' ' +
CAST ( ( SELECT td = a.description,'',
td = a.unit_price,'',
td = a.basic_rate1,'',
td = a.Max_Freight,''
FROM #salesorder_som as a
where a.order_no = @so_no
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'
| Item Description | Total Rate | Basic Rate | Max. Freight |
|---|
N'
' +
N'
' +
N'
With Best Regards,
' +N'
Kohinoor Steel P Ltd.
'+N'
' + N'
' +
N'
*******This is an automated message. Please do not reply*******
'EXEC msdb.dbo.sp_send_dbmail
@recipients='pb@kohinoorindia.in',
@copy_recipients = 'priyanka@kohinoorindia.in;avinash@kohinoorsteel.com',
@blind_copy_recipients = 'erp@kohinoorindia.in',
@subject = @subjec1,
@body = @tableHTML1,
@body_format = 'HTML'
Update Saleorder_Edk1
Set mail = '1'
from Saleorder_Edk1
where saleorder_no = @so_no
--select * from #salesorder_som
delete from #salesorder_som
END
END
END
No comments:
Post a Comment