CREATE TRIGGER dbo.ORDER_MAIL
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

Arka Guptta Blog

Friday, March 29, 2013

FREE PDF EBOOK DOWNLOAD

Tuesday, March 26, 2013

PIVOT TABLE IN SQL SERVER

Monday, March 11, 2013

Dynamic Crystal Report

Friday, March 8, 2013

ORDER MAIL TRIGGER

Respected Sir,

Sale Order has been generated today as follows;

With Best Regards,

Kohinoor Steel P Ltd.

This is an automated message. Please do not reply

Arka Gupta's Blog

Blog Archive