SALFIT TRIGGER PROCEDURE

CREATE TRIGGER SALFIT_LOCK                    
   ON  H01_IDENTFCATN                  
   FOR  INSERT,UPDATE                  
AS                      
BEGIN  
 
                 
 IF EXISTS (SELECT '*' FROM INSERTED            
       WHERE H01_CONF_DATE IS NULL
         AND H01_REHIRE_DATE IS NULL
         AND H01_ACQUISITION_DATE IS NULL
         AND H01_EXIT_DATE IS NULL
         AND H01_EFFECTIVE_DATE_TO IS NULL)
 BEGIN  
   
DECLARE @NAME VARCHAR(100)      
SET @NAME = (SELECT A.EMP_NAME FROM SALARY_FITMENT_CODE A,INSERTED B    
WHERE A.AUTO_CODE = B.H01_SOCIAL_INSURANCE_NO)      
     
EXEC SALFIT_ASKY @NAME    
   
 SET NOCOUNT ON                    
 IF NOT EXISTS (SELECT '*' FROM SALARY_FITMENT_CODE A,INSERTED B                  
       WHERE A.AUTO_CODE = B.H01_SOCIAL_INSURANCE_NO    
  AND B.H01_EFFECTIVE_DATE_FROM IS NOT NULL)    
 BEGIN                    
 RAISERROR('ERROR: PLEASE VERIFY PRF NO.',16,1)                    
 END      
       
 IF NOT EXISTS (SELECT '*' FROM SALARY_FITMENT_CODE A,INSERTED B                  
       WHERE A.DOB = B.H01_BIRTH_DATE    
  AND A.AUTO_CODE = B.H01_SOCIAL_INSURANCE_NO    
  AND B.H01_EFFECTIVE_DATE_FROM IS NOT NULL)      
 BEGIN                    
 RAISERROR('ERROR: PLEASE VERIFY EMPLOYEE BIRTH DATE',16,1)                    
 END      
       
 IF NOT EXISTS (SELECT '*' FROM SALARY_FITMENT_CODE A,INSERTED B                  
       WHERE A.DOJ = B.H01_JOIN_DATE    
  AND A.AUTO_CODE = B.H01_SOCIAL_INSURANCE_NO    
  AND B.H01_EFFECTIVE_DATE_FROM IS NOT NULL)      
 BEGIN                    
 RAISERROR('ERROR: PLEASE VERIFY EMPLOYEE JOINING DATE',16,1)                    
 END    
     
DECLARE @NAMES VARCHAR(100)        
SET @NAMES = (SELECT LETTER FROM ASCII_SALFIT)  
   
--SELECT @NAMES  
   
DECLARE @EMPNAME VARCHAR(100)  
SELECT @EMPNAME = LOWER(ISNULL(B.H01_FIRST_NAME,'')+ISNULL(B.H01_MIDDLE_NAME,'')+ISNULL(B.H01_LAST_NAME,''))  
FROM SALARY_FITMENT_CODE A,INSERTED B    
WHERE A.AUTO_CODE = B.H01_SOCIAL_INSURANCE_NO  
   
--SELECT @EMPNAME  
   
IF (@NAMES != @EMPNAME)  
 BEGIN                    
 RAISERROR('ERROR: PLEASE VERIFY EMPLOYEE NAME',16,1)                    
 END  
     
END  
END

SALFIT ASCII PROCEDURE

CREATE PROC SALFIT_ASKY          
(          
@STRING VARCHAR(100)          
)          
AS BEGIN  
DELETE FROM ASCII_SALFIT
/*    
CREATE TABLE ASCII_SALFIT
(  
ASCII INT NULL,  
LETTER VARCHAR(100)    
)  
*/      
SET TEXTSIZE 0          
SET NOCOUNT ON          
-- SELECT LOWER(@STRING)        
DECLARE @POSITION INT          
SET @POSITION = 1          
WHILE @POSITION <= DATALENGTH(@STRING)          
   BEGIN          
INSERT INTO ASCII_SALFIT          
   SELECT ASCII(SUBSTRING(@STRING, @POSITION, 1)),          
      CHAR(ASCII(SUBSTRING(@STRING, @POSITION, 1)))          
    SET @POSITION = @POSITION + 1          
   END          
DELETE FROM ASCII_SALFIT WHERE LETTER = ''
SET NOCOUNT OFF          
       
DECLARE @NAMES VARCHAR(8000)      
     
SELECT @NAMES = COALESCE(@NAMES +'','') + LETTER FROM ASCII_SALFIT    
     
INSERT INTO ASCII_SALFIT(LETTER)      
SELECT @NAMES    
DELETE FROM ASCII_SALFIT WHERE ASCII IS NOT NULL  
UPDATE ASCII_SALFIT SET LETTER = LOWER(LETTER) WHERE ASCII IS NULL
SELECT * FROM ASCII_SALFIT  
END          
         
-- EXEC SALFIT_ASKY 'ARKA GUPTA'

HTML MAIL USING SQL PROCEDURE

--- EXEC html_mail      
       
Alter procedure html_mail       
AS       
BEGIN       
       
DECLARE @tableHTML1  as NVARCHAR(MAX),         
 @subjec1 as varchar(50)         
         
Select @subjec1 = 'EMPLOYEE DETAILS MAIL'     
                   
Set @tableHTML1 =                                    
N'

Dear Sir,

' +
N'' +                               
    N' ' +                                 
   
CAST ( ( SELECT distinct  td = a.emp_code,'',                                
                          td = a.emp_name,'',
                          td = a.emp_add,''
         
                        FROM EMP_DETAILS as a                               
                                                 
                    FOR XML PATH('tr'), TYPE  
                               
    ) AS NVARCHAR(MAX) ) +
            
    N'

EMPLOYEE CODE	EMPLOYEE NAME	ADDRESS

' +                      
    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       
    @profile_name='ERP',                                  
    @recipients='arkaa4@gmail.com',
    --@copy_recipients = 'priyanka@kohinoorindia.in;avinash@kohinoorsteel.com',                           
    --@blind_copy_recipients = 'erp@kohinoorindia.in',                                   
    @subject =@subjec1,   
    @body = @tableHTML1,                                  
    @body_format = 'HTML'
     
 end      
---------------------------------------------------------------------       
--- EXEC html_mail

SENT MAIL USING SQL PROCEDURE

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