⬅ Back NM RAO 24-12-2022.sql
use T202223

  
IF OBJECTPROPERTY (OBJECT_ID('Tmp_OPENING_PURCHASE_IMAGES_DETAIL'),'ISTABLE') = 1 DROP TABLE Tmp_OPENING_PURCHASE_IMAGES_DETAIL
IF OBJECTPROPERTY (OBJECT_ID('Tmp_OPENING_PURCHASE_ADDLESS_DETAIL'),'ISTABLE') = 1 DROP TABLE Tmp_OPENING_PURCHASE_ADDLESS_DETAIL
IF OBJECTPROPERTY (OBJECT_ID('Tmp_OPENING_PURCHASE_TRANSPORT_DETAIL'),'ISTABLE') = 1 DROP TABLE Tmp_OPENING_PURCHASE_TRANSPORT_DETAIL
IF OBJECTPROPERTY (OBJECT_ID('Tmp_OPENING_PURCHASE_ITEM_DETAIL'),'ISTABLE') = 1 DROP TABLE Tmp_OPENING_PURCHASE_ITEM_DETAIL
IF OBJECTPROPERTY (OBJECT_ID('Tmp_OPENING_PURCHASE_DETAIL'),'ISTABLE') = 1 DROP TABLE Tmp_OPENING_PURCHASE_DETAIL

CREATE TABLE Tmp_OPENING_PURCHASE_DETAIL (VouNo varchar(15),VouCurDate DateTime ,VouSuppCode varchar(7),VouCustCode varchar(7),VouCustSubAgentCode varchar(7),SuppBillNo Varchar(15) ,SuppBillDate DateTime ,SuppDueDays Numeric(3),VouGrossAmt Money ,VouNetAmt Money ,CompCode varchar(7),Uname varchar(255) ,CustBillForwarding varchar(9) ,VouStatus varchar(7),OrderForm Varchar(1),FirmCode Varchar(7),VouSrNo Varchar(15),PackChrg Money,SuppDisc Varchar(20),SuppRDPerMts Varchar(20),SuppRDPerPcs Varchar(20),GRLess Money,RtnGds Money,RecdAmt Money,AddLess Money,SweetLess Money,Marka Varchar(30), GSTPerc Money, CGSTPerc Money, CGSTAmt Money, SGSTPerc Money, SGSTAmt Money, IGSTPerc Money, IGSTAmt Money, CESSPerc Money, CESSAmt Money, AddPerc Money, LessPerc Money, SweetLessPerc Money, BrokPerc Money, BrokAmt Money)
Create Table Tmp_OPENING_PURCHASE_ADDLESS_DETAIL(VouNo varchar(15),VatAddPerc Money,VatAddAmt Money,AddAmt Money,LessAmt Money,VouRemark Varchar(50),EntryNo Varchar(20),CompCode Varchar(7),Dhara Money,Days Numeric(6),PackPerPcs Money,BankComm Money,CourierCharge Money,DharaAmt Money,PackingPerPcsAmt Money,BankCommAmt Money,DharaRD Money,DharaRDAmt Money,DiscPerc Money, DiscAmt Money)
Create Table Tmp_OPENING_PURCHASE_ITEM_DETAIL(VouNo varchar(15) ,ItemName Varchar(30),ItemPcs Money,ItemCut Money,ItemMts Money,ItemPer Varchar(1),ItemRate Money,ItemAmount Money,CompCode Varchar(7),OrdNo Varchar(15),OrdISrNo Numeric(3),ItemGroupName Varchar(30),ItemFold Money,ItemFMts Money,ISrNo Numeric(6), Bale Numeric(6))
Create Table Tmp_OPENING_PURCHASE_TRANSPORT_DETAIL(VouNo varchar(15),TransName Varchar(35),TransLRNo Varchar(25),TransLRCase Numeric(3),TransLRDate DateTime,TransDespStation Varchar(35),CompCode Varchar(7))
CREATE TABLE Tmp_OPENING_PURCHASE_IMAGES_DETAIL (VouNo varchar(15),FirmCode Varchar(7),ImgPath Varchar(700))
    
INSERT INTO Tmp_OPENING_PURCHASE_DETAIL SELECT * FROM OPENING_PURCHASE_DETAIL
INSERT INTO Tmp_OPENING_PURCHASE_TRANSPORT_DETAIL SELECT * FROM OPENING_PURCHASE_TRANSPORT_DETAIL
INSERT INTO Tmp_OPENING_PURCHASE_ITEM_DETAIL SELECT * FROM Tmp_OPENING_PURCHASE_ITEM_DETAIL
INSERT INTO Tmp_OPENING_PURCHASE_ADDLESS_DETAIL SELECT * FROM OPENING_PURCHASE_ADDLESS_DETAIL
INSERT INTO Tmp_OPENING_PURCHASE_IMAGES_DETAIL SELECT * FROM OPENING_PURCHASE_IMAGES_DETAIL

update Tmp_OPENING_PURCHASE_DETAIL set VouNo='2122'+right('000000'+VouNo,6)
update Tmp_OPENING_PURCHASE_TRANSPORT_DETAIL set VouNo='2122'+right('000000'+VouNo,6)
update Tmp_OPENING_PURCHASE_ITEM_DETAIL set VouNo='2122'+right('000000'+VouNo,6)
update Tmp_OPENING_PURCHASE_ADDLESS_DETAIL set VouNo='2122'+right('000000'+VouNo,6)
update Tmp_OPENING_PURCHASE_IMAGES_DETAIL set VouNo='2122'+right('000000'+VouNo,6)


IF OBJECTPROPERTY (OBJECT_ID('OPENING_PURCHASE_IMAGES_DETAIL'),'ISTABLE') = 1 DROP TABLE OPENING_PURCHASE_IMAGES_DETAIL
IF OBJECTPROPERTY (OBJECT_ID('OPENING_PURCHASE_ADDLESS_DETAIL'),'ISTABLE') = 1 DROP TABLE OPENING_PURCHASE_ADDLESS_DETAIL
IF OBJECTPROPERTY (OBJECT_ID('OPENING_PURCHASE_ITEM_DETAIL'),'ISTABLE') = 1 DROP TABLE OPENING_PURCHASE_ITEM_DETAIL
IF OBJECTPROPERTY (OBJECT_ID('OPENING_PURCHASE_TRANSPORT_DETAIL'),'ISTABLE') = 1 DROP TABLE OPENING_PURCHASE_TRANSPORT_DETAIL
IF OBJECTPROPERTY (OBJECT_ID('OPENING_PURCHASE_DETAIL'),'ISTABLE') = 1 DROP TABLE OPENING_PURCHASE_DETAIL

CREATE TABLE OPENING_PURCHASE_DETAIL (VouNo varchar(15),VouCurDate DateTime ,VouSuppCode varchar(7),VouCustCode varchar(7),VouCustSubAgentCode varchar(7),SuppBillNo Varchar(15) ,SuppBillDate DateTime ,SuppDueDays Numeric(3),VouGrossAmt Money ,VouNetAmt Money ,CompCode varchar(7),Uname varchar(255) ,CustBillForwarding varchar(9) ,VouStatus varchar(7),OrderForm Varchar(1),FirmCode Varchar(7),VouSrNo Varchar(15),PackChrg Money,SuppDisc Varchar(20),SuppRDPerMts Varchar(20),SuppRDPerPcs Varchar(20),GRLess Money,RtnGds Money,RecdAmt Money,AddLess Money,SweetLess Money,Marka Varchar(30), GSTPerc Money, CGSTPerc Money, CGSTAmt Money, SGSTPerc Money, SGSTAmt Money, IGSTPerc Money, IGSTAmt Money, CESSPerc Money, CESSAmt Money, AddPerc Money, LessPerc Money, SweetLessPerc Money, BrokPerc Money, BrokAmt Money)
Create Table OPENING_PURCHASE_ADDLESS_DETAIL(VouNo varchar(15),VatAddPerc Money,VatAddAmt Money,AddAmt Money,LessAmt Money,VouRemark Varchar(50),EntryNo Varchar(20),CompCode Varchar(7) Foreign Key References COMPANY_DETAIL(CompCode),Dhara Money,Days Numeric(6),PackPerPcs Money,BankComm Money,CourierCharge Money,DharaAmt Money,PackingPerPcsAmt Money,BankCommAmt Money,DharaRD Money,DharaRDAmt Money,DiscPerc Money, DiscAmt Money)
Create Table OPENING_PURCHASE_ITEM_DETAIL(VouNo varchar(15) ,ItemName Varchar(30),ItemPcs Money,ItemCut Money,ItemMts Money,ItemPer Varchar(1),ItemRate Money,ItemAmount Money,CompCode Varchar(7) Foreign Key References COMPANY_DETAIL(CompCode),OrdNo Varchar(15),OrdISrNo Numeric(3),ItemGroupName Varchar(30),ItemFold Money,ItemFMts Money,ISrNo Numeric(6), Bale Numeric(6))
Create Table OPENING_PURCHASE_TRANSPORT_DETAIL(VouNo varchar(15),TransName Varchar(35),TransLRNo Varchar(25),TransLRCase Numeric(3),TransLRDate DateTime,TransDespStation Varchar(35),CompCode Varchar(7) Foreign Key References COMPANY_DETAIL(CompCode))
CREATE TABLE OPENING_PURCHASE_IMAGES_DETAIL (VouNo varchar(15),FirmCode Varchar(7),ImgPath Varchar(700))
            
INSERT INTO OPENING_PURCHASE_DETAIL SELECT * FROM Tmp_OPENING_PURCHASE_DETAIL
INSERT INTO OPENING_PURCHASE_TRANSPORT_DETAIL SELECT * FROM Tmp_OPENING_PURCHASE_TRANSPORT_DETAIL
INSERT INTO OPENING_PURCHASE_ITEM_DETAIL SELECT * FROM Tmp_OPENING_PURCHASE_ITEM_DETAIL
INSERT INTO OPENING_PURCHASE_ADDLESS_DETAIL SELECT * FROM Tmp_OPENING_PURCHASE_ADDLESS_DETAIL
INSERT INTO OPENING_PURCHASE_IMAGES_DETAIL SELECT * FROM Tmp_OPENING_PURCHASE_IMAGES_DETAIL

IF OBJECTPROPERTY (OBJECT_ID('Tmp_OPENING_PURCHASE_IMAGES_DETAIL'),'ISTABLE') = 1 DROP TABLE Tmp_OPENING_PURCHASE_IMAGES_DETAIL
IF OBJECTPROPERTY (OBJECT_ID('Tmp_OPENING_PURCHASE_ADDLESS_DETAIL'),'ISTABLE') = 1 DROP TABLE Tmp_OPENING_PURCHASE_ADDLESS_DETAIL
IF OBJECTPROPERTY (OBJECT_ID('Tmp_OPENING_PURCHASE_ITEM_DETAIL'),'ISTABLE') = 1 DROP TABLE Tmp_OPENING_PURCHASE_ITEM_DETAIL
IF OBJECTPROPERTY (OBJECT_ID('Tmp_OPENING_PURCHASE_TRANSPORT_DETAIL'),'ISTABLE') = 1 DROP TABLE Tmp_OPENING_PURCHASE_TRANSPORT_DETAIL
IF OBJECTPROPERTY (OBJECT_ID('Tmp_OPENING_PURCHASE_DETAIL'),'ISTABLE') = 1 DROP TABLE Tmp_OPENING_PURCHASE_DETAIL
-----------------FOR ITEM DETAIL
insert into OPENING_PURCHASE_ITEM_DETAIL SELECT VouNo, '', 0, 0, 0, 'P', 0, VouGrossAmt, CompCode, '', 0, '', 0, 0, 1,0 FROM OPENING_PURCHASE_DETAIL WHERE VouNo NOT IN (SELECT VouNo FROM OPENING_PURCHASE_ITEM_DETAIL)