Extremely sorry for incomplete question, however I mentioned that I m using SQL Server 2000. Yes you are right my question was
how do I improve the performance of the second UDF
Also I encode/attach my UDFs (I again apologize if I wrongly do the same as I don't know the exact way to attach my code, however, if u will guide I will be care full in near future.
Alter Function f_PendingDCDetails (@SID Varchar(32), @iFID varchar(32), @iTID varchar(32),
@CID Varchar(32), @EnDate Varchar(11), @iWR TinyINt, @iLoc Varchar(8), @iScrVNo Varchar(80),
@iVtp VarChar(20), @iSubType Varchar(8), @iFinancialYear Varchar(8), @iMnth VarChar(8), @ivYear Varchar(8),
@iLocation VarChar(8), @iForLocation Varchar(8), @iAorB Varchar(1), @iVNo Integer, @iSrNo Integer, @ForWhichForm VarChar(2))
Returns @T Table (
QueryNo Varchar (4),
ScrVoucher_No Varchar (101),
Vtp Varchar (20),
SubType Varchar (8),
FinancialYear Varchar (8),
Mnth Varchar (8),
vYear Varchar (8),
Location Varchar (8),
ForLocation Varchar (8),
AorB Varchar (1),
vno Varchar (4),
srno Varchar (4),
dated DateTime,
Item_ID Varchar (32),
Delivered Double Precision,
Returned Integer,
Invoiced Double Precision,
Rate Double Precision,
Remarks Text,
Project_ID Varchar (32),
Client_ID Varchar (32),
Emp_ID Varchar (32),
DeliveryChallanNo Varchar (70),
GDNDelivered Double Precision,
EngineNo Varchar (64),
FrameNo Varchar (64),
VRegNo Varchar (32),
ClaimType Varchar (32),
PrincipalCompany_ID Varchar (32),
STPercent Double Precision,
EDPercent Double Precision,
OTPercent Double Precision,
DiscP Double Precision,
DiscA Double Precision,
GrossRate Double Precision,
MainDiscP Double Precision,
Party_ID Varchar (5),
ClientPONo Varchar (200),
ClientPODate DateTime,
PaymentTerms Varchar (32),
Job_ID Varchar (32),
CostCenter_ID Varchar (32),
BatchNo Varchar (40),
ExpiryDate DateTime,
Transporter_ID Varchar (32),
BQO Double Precision,
BQD Double Precision,
Gift_ID Varchar (32),
GQO Double Precision,
GQD Double Precision,
BiltyNo Varchar (120),
BiltyDate DateTime,
OldSoftNo Varchar (32)
)
as
Begin
Insert @T
Select Max(T.QueryNo) QueryNo, Max(T.ScrVoucher_No) ScrVoucher_No, Max(T.Vtp) Vtp, Max(T.SubType) SubType, Max(T.FinancialYear) FinancialYear,
Max(T.Mnth) Mnth, Max(T.vYear) vYear, Max(T.Location) Location, Max(T.ForLocation) ForLocation, Max(T.AorB) AorB, Max(T.vno) vno,
Max(T.srno) srno, Max(T.dated) dated, Max(T.Item_ID) Item_ID, Sum(T.Delivered) Delivered, Sum(T.Returned) Returned, Sum(T.Invoiced) Invoiced,
Sum(T.Rate) Rate, Max(T.Remarks) Remarks, Max(T.Project_ID) Project_ID, Max(T.Client_ID) Client_ID, Max(T.Emp_ID) Emp_ID, Max(T.DeliveryChallanNo)
DeliveryChallanNo, Sum(T.GDNDelivered) GDNDelivered, Max(T.EngineNo) EngineNo, Max(T.FrameNo) FrameNo, Max(T.VRegNo) VRegNo, Max(T.ClaimType)
ClaimType, Max(T.PrincipalCompany_ID) PrincipalCompany_ID, Sum(T.STPercent) STPercent, Sum(T.EDPercent) EDPercent, Sum(T.OTPercent) OTPercent,
Sum(T.DiscP) DiscP, Sum(T.DiscA) DiscA, Sum(T.GrossRate) GrossRate, Sum(T.MainDiscP) MainDiscP, Max(T.Party_ID) Party_ID, Max(T.ClientPONo)
ClientPONo, Max(T.ClientPODate) ClientPODate, Max(T.PaymentTerms) PaymentTerms, Max(T.Job_ID) Job_ID, Max(T.CostCenter_ID) CostCenter_ID,
Max(T.BatchNo) BatchNo, Max(T.ExpiryDate) ExpiryDate, Max(T.Transporter_ID) Transporter_ID, Sum(T.BQO) BQO, Sum(T.BQD) BQD, Max(T.Gift_ID)
Gift_ID, Sum(T.GQO) GQO, Sum(T.GQD) GQD, Max(T.BiltyNo) BiltyNo, Max(T.BiltyDate) BiltyDate, Max(T.OldSoftNo) OldSoftNo From (
Select 1 QueryNo, T.ScrVoucher_No+'/'+Cast(TD.SrNo as Varchar(30)) ScrVoucher_No, TD.Vtp, TD.SubType, TD.FinancialYear, TD.Mnth, TD.vYear,
TD.Location, TD.ForLocation, TD.AorB, TD.vno, TD.srno, T.dated, TD.Item_ID, TD.Qty Delivered, 0.00 Returned, 0.00 Invoiced,
CD.Rate Rate, ' ' Remarks, O.Project_ID, T.Client_ID, C.Emp_ID Emp_ID, T.ScrVoucher_No DeliveryChallanNo, 0.00 GDNDelivered,
TD.EngineNo, TD.FrameNo, '' VRegNo, '' ClaimType, '' PrincipalCompany_ID, C.SalesTaxPercentage STPercent, 0 EDPercent, 0 OTPercent, CD.DiscP,
CD.DiscAmt DiscA, CD.GrossRate, 0 MainDiscP, C.Party_ID, C.PONo ClientPONo, C.PO_Date ClientPODate, '' PaymentTerms,
TD.Job_ID, O.CostCenter_ID, TD.BatchNo, TD.ExpiryDate, T.Transporter_ID, TD.BonusQty BQO, 0 BQD, TD.Gift_ID, TD.GiftQty GQO, 0 GQD, '' BiltyNo, Null BiltyDate, T.OldSoftNo from DeliveryChallan_Det TD
Left Join DeliveryChallan T on TD.VTP=T.VTP and TD.SubType=T.SubType and TD.FinancialYear=T.FinancialYear and TD.Mnth=T.Mnth and TD.vYear=T.vYear and TD.Location=T.Location and TD.ForLocation=T.ForLocation and TD.AorB=T.AorB and TD.VNo=T.VNo
Left Join ClientOrder_Det CD On IsNull(TD.CO_VTP,'')=CD.VTP and IsNull(TD.CO_SubType,'')=CD.SubType and IsNull(TD.CO_FinancialYear,'')=CD.FinancialYear and IsNull(TD.CO_Mnth,'')=CD.Mnth and IsNull(TD.CO_vYear,'')=CD.vYear and IsNull(TD.CO_Location,'')=CD.Location and IsNull(TD.CO_ForLocation,'')=CD.ForLocation and IsNull(TD.CO_AorB,'')=CD.AorB and IsNull(TD.CO_VNo,0)=CD.VNo and IsNull(TD.CO_SrNo,0)=CD.SrNo
Left Join ClientOrder C On CD.VTP=C.VTP and CD.SubType=C.SubType and CD.FinancialYear=C.FinancialYear and CD.Mnth=C.Mnth and CD.vYear=C.vYear and CD.Location=C.Location and CD.ForLocation=C.ForLocation and CD.AorB=C.AorB and CD.VNo=C.VNo
Left Join Offer_Det OD On IsNull(CD.Ref_VTP,'')=OD.VTP and IsNull(CD.Ref_SubType,'')=OD.SubType and IsNull(CD.Ref_FinancialYear,'')=OD.FinancialYear and IsNull(CD.Ref_Mnth,'')=OD.Mnth and IsNull(CD.Ref_vYear,'')=OD.vYear and IsNull(CD.Ref_Location,'')=OD.Location and IsNull(CD.Ref_ForLocation,'')=OD.ForLocation and IsNull(CD.Ref_AorB,'')=OD.AorB and IsNull(CD.Ref_VNo,IsNull(OD.Ref_VNo,''))=OD.VNo and IsNull(CD.Ref_SrNo,IsNull(OD.Ref_SrNo,''))=OD.SrNo
Left Join Offer O On OD.VTP=O.VTP and OD.SubType=O.SubType and OD.FinancialYear=O.FinancialYear and OD.Mnth=O.Mnth and OD.vYear=O.vYear and OD.Location=O.Location and OD.ForLocation=O.ForLocation and OD.AorB=O.AorB and OD.VNo=O.VNo
Left Join GlobalSettingsNew G On G.ID='COPYGOODSDELIVERYNOTETOSALESINVOICE'
Where (@SID='' or T.Client_ID=@SID)
and (@iFID='' or TD.Item_ID Between @iFID and @iTID)
and (@EnDate='' or T.dated<=@EnDate)
and (@iLoc='' or T.Location=@iLoc)
and (@iScrVNo='' or T.ScrVOucher_No=@iScrVNo)
and (@iVTP='' or (T.VTP=@iVTP and T.Mnth=@iMnth and T.vYear=@ivYear and T.Location=@iLocation and T.ForLocation=@iForLocation and T.AorB=@iAorB and T.VNo=@iVNo))
and (@iSrNo=0 or TD.SrNo=@iSrNo)
Union All
Select 2, T.ScrVoucher_No+'/'+Cast(TD.SrNo as Varchar(30))
ScrVoucher_No, TD.Vtp, TD.SubType, TD.FinancialYear, TD.Mnth, TD.vYear,
TD.Location, TD.ForLocation, TD.AorB, TD.vno, TD.srno, T.dated,
TD.Item_ID, TD.Qty Delivered, 0.00 Returned, 0.00 Invoiced,
CD.Rate Rate, ' ' Remarks, Null Project_ID, T.Client_ID, C.Emp_ID Emp_ID,
T.ScrVoucher_No DeliveryChallanNo, 0.00 GDNDelivered, TD.EngineNo, TD.FrameNo, '' VRegNo, '' ClaimType,
'' PrincipalCompany_ID, CD.SalesTaxPerc, CD.EDPercent, CD.OTPercent, CD.DiscP, CD.DiscAmt DiscA, CD.GrossRate, 0 MainDiscP,
IsNull(C.Party_ID, T.Client_ID), C.PONo, C.PO_Date, C.PaymentTerms_ID, Null Job_ID, Null CostCenter_ID, TD.BatchNo, TD.ExpDate, '', 0 BQO, 0 BQD, Null Gift_ID, 0 GQO, 0 GQD, T.BiltyNo, T.BuiltyDate, '' from GDN_Det TD
Left Join GDN T on TD.VTP=T.VTP and TD.SubType=T.SubType and TD.FinancialYear=T.FinancialYear and TD.Mnth=T.Mnth and TD.vYear=T.vYear and TD.Location=T.Location and TD.ForLocation=T.ForLocation and TD.AorB=T.AorB and TD.VNo=T.VNo
Left Join ClientOrder_Det CD On IsNull(TD.COVTP,'')=CD.VTP and IsNull(TD.COSubType,'')=CD.SubType and IsNull(TD.COFinancialYear,'')=CD.FinancialYear and IsNull(TD.COMnth,'')=CD.Mnth and IsNull(TD.COvYear,'')=CD.vYear and IsNull(TD.COLocation,'')=CD.Location and IsNull(TD.COForLocation,'')=CD.ForLocation and IsNull(TD.COAorB,'')=CD.AorB and IsNull(TD.COVNo,0)=CD.VNo and IsNull(TD.COSrNo,0)=CD.SrNo
Left Join ClientOrder C On CD.VTP=C.VTP and CD.SubType=C.SubType and CD.FinancialYear=C.FinancialYear and CD.Mnth=C.Mnth and CD.vYear=C.vYear and CD.Location=C.Location and CD.ForLocation=C.ForLocation and CD.AorB=C.AorB and CD.VNo=C.VNo
Left Join GlobalSettingsNew G On G.ID='COPYGOODSDELIVERYNOTETOSALESINVOICE'
Where (G.vluCheckBox<>0 and (DB_Name()<>'ERPZxmcoP' or (DB_Name()='ERPZxmcoP' and TD.COVTP='CRQ')))
and TD.COVtp<>'PRN'
and (@SID='' or T.Client_ID=@SID)
and (@iFID='' or TD.Item_ID Between @iFID and @iTID)
and (@EnDate='' or T.dated<=@EnDate)
and (@iLoc='' or T.Location=@iLoc)
and (@iScrVNo='' or T.ScrVOucher_No=@iScrVNo)
and (@iVTP='' or (T.VTP=@iVTP and T.Mnth=@iMnth and T.vYear=@ivYear and T.Location=@iLocation and T.ForLocation=@iForLocation and T.AorB=@iAorB and T.VNo=@iVNo))
and (@iSrNo=0 or TD.SrNo=@iSrNo)
Union All
Select 4, Case When JOS.VTP Is Null Then J.ScrVoucher_No+'/'+Cast(JD.SrNo as Varchar(30)) Else JOS.ScrVoucher_No+'/1' End ScrVoucher_No,
Case When JOS.VTP Is Null Then JD.Vtp Else JOS.VTP End VTP,
Case When JOS.VTP Is Null Then JD.SubType Else JOS.SubType End SubType,
Case When JOS.VTP Is Null Then JD.FinancialYear Else JOS.FinancialYear End FinancialYear,
Case When JOS.VTP Is Null Then JD.Mnth Else JOS.Mnth End Mnth,
Case When JOS.VTP Is Null Then JD.vYear Else JOS.vYear End vYear,
Case When JOS.VTP Is Null Then JD.Location Else JOS.Location End Location,
Case When JOS.VTP Is Null Then JD.ForLocation Else JOS.ForLocation End ForLocation,
Case When JOS.VTP Is Null Then JD.AorB Else JOS.AorB End AorB,
Case When JOS.VTP Is Null Then JD.vno Else JOS.vno End vno,
Case When JOS.VTP Is Null Then JD.srno Else 1 End srno,
Case When JOS.VTP Is Null Then J.dated Else JOS.Dated End,
IsNull(JD.Item_ID, TD.Item_ID), TD.Qty Delivered, 0.00 Returned, 0.00 Invoiced,
IsNull(JD.Rate,0) Rate, ' ' Remarks, Null Project_ID, IsNull(J.Client_ID, JOS.Client_ID), IsNull(J.Mech_ID,JOS.JobAssigned_ID) Emp_ID,
IsNull(J.ScrVoucher_No, JOS.ScrVoucher_No) DeliveryChallanNo, 0.00 GDNDelivered, Null EngineNo, Null FrameNo, J.VRegNo, J.ClaimType,
J.PrincipalCompany_ID, JD.SalesTaxPerc, JD.EDPercent, JD.OTPercent, JD.DiscP, JD.DiscA, JD.GrossRate, J.DiscPerc,
J.Party_ID, J.PORefNo, Null, J.PaymentTerms, Null Job_ID, Null CostCenter_ID, '' BatchNo, Null ExpiryDate, '', 0 BQO, 0 BQD, Null Gift_ID, 0 GQO, 0 GQD, '' BiltyNo, Null BiltyDate, '' from StoreIssue_Det TD
Left Join StoreIssue T on TD.VTP=T.VTP and TD.SubType=T.SubType and TD.FinancialYear=T.FinancialYear and TD.Mnth=T.Mnth and TD.vYear=T.vYear and TD.Location=T.Location and TD.ForLocation=T.ForLocation and TD.AorB=T.AorB and TD.VNo=T.VNo
Left Join StoreReq_Det SD On IsNull(TD.SQVTP,'')=SD.VTP and IsNull(TD.SQSubType,'')=SD.SubType and IsNull(TD.SQFinancialYear,'')=SD.FinancialYear and IsNull(TD.SQMnth,'')=SD.Mnth and IsNull(TD.SQvYear,'')=SD.vYear and IsNull(TD.SQLocation,'')=SD.Location and IsNull(TD.SQForLocation,'')=SD.ForLocation and IsNull(TD.SQAorB,'')=SD.AorB and IsNull(TD.SQVNo,0)=SD.VNo and IsNull(TD.SQSrNo,0)=SD.SrNo
Left Join StoreReq S On SD.VTP=S.VTP and SD.SubType=S.SubType and SD.FinancialYear=S.FinancialYear and SD.Mnth=S.Mnth and SD.vYear=S.vYear and SD.Location=S.Location and SD.ForLocation=S.ForLocation and SD.AorB=S.AorB and SD.VNo=S.VNo
Left Join JobOrder3SDealers_Det JD On IsNull(SD.EPOVTP,'')=JD.VTP and IsNull(SD.EPOSubType,'')=JD.SubType and IsNull(SD.EPOFinancialYear,'')=JD.FinancialYear and IsNull(SD.EPOMnth,'')=JD.Mnth and IsNull(SD.EPOvYear,'')=JD.vYear and IsNull(SD.EPOLocation,'')=JD.Location and IsNull(SD.EPOForLocation,'')=JD.ForLocation and IsNull(SD.EPOAorB,'')=JD.AorB and IsNull(SD.EPOVNo,0)=JD.VNo and IsNull(SD.EPOSrNo,0)=JD.SrNo
Left Join JobOrder3SDealers J On JD.VTP=J.VTP and JD.SubType=J.SubType and JD.FinancialYear=J.FinancialYear and JD.Mnth=J.Mnth and JD.vYear=J.vYear and JD.Location=J.Location and JD.ForLocation=J.ForLocation and JD.AorB=J.AorB and JD.VNo=J.VNo
Left Join JobOrderAfterSale JOS On IsNull(S.EPOVTP,'')=JOS.VTP and IsNull(S.EPOSubType,'')=JOS.SubType and IsNull(S.EPOFinancialYear,'')=JOS.FinancialYear and IsNull(S.EPOMnth,'')=JOS.Mnth and IsNull(S.EPOvYear,'')=JOS.vYear and IsNull(S.EPOLocation,'')=JOS.Location and IsNull(S.EPOForLocation,'')=JOS.ForLocation and IsNull(S.EPOAorB,'')=JOS.AorB and IsNull(S.EPOVNo,0)=JOS.VNo
Left Join Vtp2 V On IsNull(J.VTP, JOS.VTP)=V.ID2
Where DB_Name()='ERPLCTRLM'
and ((V.ID1='JO3S' and IsNull(JD.ForWorkOrder,0)=0) or V.ID1='JOAS')
and (@SID='' or J.Client_ID=@SID)
and (@iFID='' or JD.Item_ID Between @iFID and @iTID)
and (@EnDate='' or T.dated<=@EnDate)
and (@iLoc='' or T.Location=@iLoc)
and (@iScrVNo='' or Case When JOS.VTP Is Null Then J.ScrVOucher_No Else JOS.ScrVoucher_No End=@iScrVNo)
and (@iVTP='' or (IsNull(JOS.VTP, IsNull(JD.VTP, T.VTP))=@iVTP and IsNull(JOS.Mnth, IsNull(JD.Mnth, T.Mnth))=@iMnth
and IsNull(JOS.vYear, IsNull(JD.vYear, T.vYear))=@ivYear and IsNull(JOS.Location, IsNull(JD.Location, T.Location))=@iLocation
and IsNull(JOS.ForLocation, IsNull(JD.ForLocation, T.ForLocation))=@iForLocation and IsNull(JOS.AorB, IsNull(JD.AorB, T.AorB))=@iAorB
and IsNull(JOS.VNo, IsNull(JD.VNo, T.VNo))=@iVNo))
and (@iSrNo=0 or TD.SrNo=@iSrNo)
Union All
Select 5, T.ScrVoucher_No+'/'+Cast(TD.SrNo as Varchar(30))
ScrVoucher_No, TD.Vtp, TD.SubType, TD.FinancialYear, TD.Mnth, TD.vYear,
TD.Location, TD.ForLocation, TD.AorB, TD.vno, TD.srno, T.dated,
TD.Service_ID, TD.Qty Delivered, 0.00 Returned, 0.00 Invoiced,
TD.Rate Rate, ' ' Remarks, Null Project_ID, T.Client_ID, T.Mech_ID Emp_ID,
T.ScrVoucher_No DeliveryChallanNo, 0.00 GDNDelivered, Null EngineNo, Null FrameNo, T.VRegNo, T.ClaimType,
T.PrincipalCompany_ID, TD.SalesTaxPerc, TD.EDPercent, TD.OTPercent, TD.DiscP, TD.DiscA, TD.GrossRate,
T.DiscPerc, T.Party_ID, '', Null, T.PaymentTerms, Null Job_ID, Null CostCenter_ID, '' BatchNo, Null ExpiryDate, '', 0 BQO, 0 BQD, Null Gift_ID, 0 GQO, 0 GQD, '' BiltyNo, Null BiltyDate, '' from JobOrder3SDealers_Services TD
Left Join JobOrder3SDealers T on TD.VTP=T.VTP and TD.SubType=T.SubType and TD.FinancialYear=T.FinancialYear and TD.Mnth=T.Mnth and TD.vYear=T.vYear and TD.Location=T.Location and TD.ForLocation=T.ForLocation and TD.AorB=T.AorB and TD.VNo=T.VNo
Where (@SID='' or T.Client_ID=@SID)
and (@iFID='' or TD.Service_ID Between @iFID and @iTID)
and (@EnDate='' or T.dated<=@EnDate)
and (@iLoc='' or T.Location=@iLoc)
and (@iScrVNo='' or T.ScrVOucher_No=@iScrVNo)
and (@iVTP='' or (T.VTP=@iVTP and T.Mnth=@iMnth and T.vYear=@ivYear and T.Location=@iLocation and T.ForLocation=@iForLocation and T.AorB=@iAorB and T.VNo=@iVNo))
and (@iSrNo=0 or TD.SrNo=@iSrNo)
Union All
Select 6, J.ScrVoucher_No+'/'+Cast(JD.SrNo as Varchar(30))
ScrVoucher_No, JD.Vtp, JD.SubType, JD.FinancialYear, JD.Mnth, JD.vYear,
JD.Location, JD.ForLocation, JD.AorB, JD.vno, JD.srno, J.dated,
JD.Item_ID, TD.Qty Delivered, 0.00 Returned, 0.00 Invoiced,
JD.Rate Rate, ' ' Remarks, Null Project_ID, J.Client_ID, J.Mech_ID Emp_ID,
J.ScrVoucher_No DeliveryChallanNo, 0.00 GDNDelivered, Null EngineNo, Null FrameNo, J.VRegNo, J.ClaimType,
J.PrincipalCompany_ID, 0, 0, 0, 0, 0, 0, 0, '', '', Null, '', Null Job_ID, Null CostCenter_ID, '' BatchNo, Null ExpiryDate, '', 0 BQO, 0 BQD, Null Gift_ID, 0 GQO, 0 GQD, '' BiltyNo, Null BiltyDate, '' from MarketingWO_Det TD
Left Join MarketingWO T on TD.VTP=T.VTP and TD.SubType=T.SubType and TD.FinancialYear=T.FinancialYear and TD.Mnth=T.Mnth and TD.vYear=T.vYear and TD.Location=T.Location and TD.ForLocation=T.ForLocation and TD.AorB=T.AorB and TD.VNo=T.VNo
Left Join JobOrder3SDealers_Det JD On IsNull(TD.PQVTP,'')=JD.VTP and IsNull(TD.PQSubType,'')=JD.SubType and IsNull(TD.PQFinancialYear,'')=JD.FinancialYear and IsNull(TD.PQMnth,'')=JD.Mnth and IsNull(TD.PQvYear,'')=JD.vYear and IsNull(TD.PQLocation,'')=JD.Location and IsNull(TD.PQForLocation,'')=JD.ForLocation and IsNull(TD.PQAorB,'')=JD.AorB and IsNull(TD.PQVNo,0)=JD.VNo and IsNull(TD.PQSrNo,0)=JD.SrNo
Left Join JobOrder3SDealers J On JD.VTP=J.VTP and JD.SubType=J.SubType and JD.FinancialYear=J.FinancialYear and JD.Mnth=J.Mnth and JD.vYear=J.vYear and JD.Location=J.Location and JD.ForLocation=J.ForLocation and JD.AorB=J.AorB and JD.VNo=J.VNo
Where JD.VTP Is NOt Null and (@SID='' or J.Client_ID=@SID)
and (@iFID='' or JD.Item_ID Between @iFID and @iTID)
and (@EnDate='' or T.dated<=@EnDate)
and (@iLoc='' or T.Location=@iLoc)
and (@iScrVNo='' or J.ScrVOucher_No=@iScrVNo)
and (@iVTP='' or (J.VTP=@iVTP and J.Mnth=@iMnth and J.vYear=@ivYear and J.Location=@iLocation and J.ForLocation=@iForLocation and J.AorB=@iAorB and J.VNo=@iVNo))
and (@iSrNo=0 or TD.SrNo=@iSrNo)
Union All
Select 7, IsNull(TD.CO_VoucherNo,''), IsNull(TD.CO_Vtp, ''), IsNull(TD.CO_SubType, ''), IsNull(TD.CO_FinancialYear, ''),
IsNull(TD.CO_Mnth, ''), IsNull(TD.CO_vYear, ''), IsNull(TD.CO_Location, ''), IsNull(TD.CO_ForLocation, ''),
IsNull(TD.CO_AorB, ''), IsNull(TD.CO_Vno, ''), TD.CO_srno, Null, IsNull(TD.Item_ID, TD.Service_ID), 0.00,
0.00, TD.Qty, 0, '', Null CostCenter_ID, T.Client_ID, '', '', 0.00, TD.EngineNo, TD.FrameNo, '', '', '', 0, 0, 0,
0, 0, 0, 0, '', '', Null, '', TD.Service_ID Job_ID, Null CostCenter_ID, '' BatchNo, Null ExpiryDate, '', 0 BQO, TD.BonusQty BQD, Null Gift_ID, 0 GQO, TD.GiftQty GQD, '' BiltyNo, Null BiltyDate, '' from SaleInvoice_Det TD
Left Join SaleInvoice T on TD.VTP=T.VTP and TD.SubType=T.SubType and TD.FinancialYear=T.FinancialYear and TD.Mnth=T.Mnth and TD.vYear=T.vYear and TD.Location=T.Location and TD.ForLocation=T.ForLocation and TD.AorB=T.AorB and TD.VNo=T.VNo
Where (@iWR=0 or @iWR=4 or @iWR=2) and (@SID='' or T.Client_ID=@SID)
and (@iFID='' or TD.Item_ID Between @iFID and @iTID)
and (@EnDate='' or T.dated<=@EnDate)
and IsNull(TD.CO_VTP,'')<>''
and (@iLoc='' or T.Location=@iLoc)
and (@iScrVNo='' or TD.CO_ScrVoucher_No=@iScrVNo)
and (@iVTP='' or (IsNull(TD.CO_VTP,'')=@iVTP and IsNull(TD.CO_Mnth,'')=@iMnth and IsNull(TD.CO_vYear,'')=@ivYear and IsNull(TD.CO_Location,'')=@iLocation and IsNull(TD.CO_ForLocation,'')=@iForLocation and IsNull(TD.CO_AorB,'')=@iAorB and IsNull(TD.CO_VNo,0)=@iVNo))
and (@iSrNo=0 or TD.SrNo=@iSrNo)
Union All
Select 8, IsNull(TD.COVoucherNo,''), IsNull(TD.COVtp, ''), IsNull(TD.COSubType, ''), IsNull(TD.COFinancialYear, ''),
IsNull(TD.COMnth, ''), IsNull(TD.COvYear, ''), IsNull(TD.COLocation, ''), IsNull(TD.COForLocation, ''),
IsNull(TD.COAorB, ''), IsNull(TD.COVno, ''), TD.COsrno, Null, TD.Item_ID, 0.00,
0.00, 0.00, 0, '', Null CostCenter_ID, T.Client_ID, '', '', TD.Qty, '', '', '', '', '', CD.SalesTaxPerc, CD.EDPercent, CD.OTPercent,
0, 0, 0, 0, '', '', Null, '', Null Job_ID, Null CostCenter_ID, '' BatchNo, Null ExpiryDate, '', 0 BQO, TD.BonusQty BQD, Null Gift_ID, 0 GQO, TD.GiftQty GQD, '' BiltyNo, Null BiltyDate, '' from GDN_Det TD
Left Join GDN T on TD.VTP=T.VTP and TD.SubType=T.SubType and TD.FinancialYear=T.FinancialYear and TD.Mnth=T.Mnth and TD.vYear=T.vYear and TD.Location=T.Location and TD.ForLocation=T.ForLocation and TD.AorB=T.AorB and TD.VNo=T.VNo
Left Join ClientOrder_Det CD On IsNull(TD.COVTP,'')=CD.VTP and IsNull(TD.COSubType,'')=CD.SubType and IsNull(TD.COFinancialYear,'')=CD.FinancialYear and IsNull(TD.COMnth,'')=CD.Mnth and IsNull(TD.COvYear,'')=CD.vYear and IsNull(TD.COLocation,'')=CD.Location and IsNull(TD.COForLocation,'')=CD.ForLocation and IsNull(TD.COAorB,'')=CD.AorB and IsNull(TD.COVNo,0)=CD.VNo and IsNull(TD.COSrNo,0)=CD.SrNo
Left Join ClientOrder C On CD.VTP=C.VTP and CD.SubType=C.SubType and CD.FinancialYear=C.FinancialYear and CD.Mnth=C.Mnth and CD.vYear=C.vYear and CD.Location=C.Location and CD.ForLocation=C.ForLocation and CD.AorB=C.AorB and CD.VNo=C.VNo
Left Join Vtp2 V On V.ID2=TD.COVTP
Where (@iWR=1 or @iWR=2)
and V.ID1 In ('DC') and (@SID='' or T.Client_ID=@SID)
and (@iFID='' or TD.Item_ID Between @iFID and @iTID)
and (@EnDate='' or T.dated<=@EnDate)
and IsNull(TD.COVTP,'')<>''
and (@iLoc='' or T.Location=@iLoc)
and (@iScrVNo='' or TD.COScrVoucher_No=@iScrVNo)
) T
Group By T.VTP, T.SubType, T.FinancialYear, T.Mnth, T.vYear, T.Location, T.ForLocation,
T.AorB, T.VNo, T.SrNo, T.Item_ID
Having (@iWR=4 or (@iWR=0 and sum(T.Delivered)>sum(T.Invoiced-T.Returned)) or
(@iWR=1 and sum(T.Delivered)>Sum(T.GDNDelivered)) or
(@iWR=2 and sum(T.Delivered)<sum(T.Invoiced-T.Returned)) or
(@iWR=3 and sum(T.Delivered)<Sum(T.GDNDelivered)))
Return
End
Alter Function f_PendingDC (@SID Varchar(32), @iFID varchar(32), @iTID varchar(32), @CID Varchar(32),
@EnDate Varchar(11), @iWR TinyInt, @iLoc Varchar(8), @iScrVNo Varchar(80),
@iVtp VarChar(20), @iSubType Varchar(8), @iFinancialYear Varchar(8), @iMnth VarChar(8), @ivYear Varchar(8),
@iLocation VarChar(8), @iForLocation Varchar(8), @iAorB Varchar(1), @iVNo Integer, @iSrNo Integer, @ForWhichForm VarChar(2))
Returns @T Table (
ScrVoucher_NoWithSrNo Varchar (101),
VTP Varchar (20),
SubType Varchar (8),
FinancialYear Varchar (8),
Mnth Varchar (8),
vYear Varchar (8),
Location Varchar (8),
ForLocation Varchar (8),
AorB Varchar (1),
VNo Varchar (4),
SrNo Varchar (4),
Dated DateTime,
Client_ID Varchar (32),
Client_Title Varchar (90),
Item_ID Varchar (32),
Item_Title Varchar (250),
Delivered Double Precision,
Returned Varchar (4),
Invoiced Double Precision,
Unit Varchar (10),
Rate Double Precision,
Remarks Varchar (1000),
Emp_ID Varchar (32),
Emp_Title Varchar (70),
DeliveryChallanNo Varchar (70),
GDNDelivered Double Precision,
iWR TinyInt,
EngineNo Varchar (64),
FrameNo Varchar (64),
ScrVoucher_No Varchar (70),
ServiceOrPart Varchar (8),
VRegNo Varchar (32),
ClaimType Varchar (32),
PrincipalCompany_ID Varchar (32),
PrincipalCompany_Title Varchar (90),
STPercent Double Precision,
EDPercent Double Precision,
OTPercent Double Precision,
DiscP Double Precision,
DiscA Double Precision,
GrossRate Double Precision,
MainDiscP Double Precision,
Party_ID Varchar (5),
Party_Title Varchar (70),
ClientPONo Varchar (200),
ClientPODate DateTime,
Main_VTP Varchar (10),
SaleRate Double Precision,
PaymentTerms Varchar (32),
PaymentTermsCoa Varchar (10),
Job_ID Varchar (32),
Job_Title Varchar (170),
CostCenter_ID Varchar (32),
Project_ID Varchar (32),
CostCenter_Title Varchar (120),
Project_Title Varchar (70),
BatchNo Varchar (40),
ExpiryDate DateTime,
Transporter_ID Varchar (32),
Transporter_Title Varchar (90),
QtyPerCTN Double Precision,
BQO Double Precision,
BQD Double Precision,
Gift_ID Varchar (32),
Gift_Title Varchar (250),
GQO Double Precision,
GQD Double Precision,
BiltyNo Varchar (120),
BiltyDate DateTime,
OldSoftNo Varchar (32)
)
as
Begin
Insert @T
Select (T.ScrVoucher_No) ScrVoucher_NoWithSrNo, (T.Vtp) VTP, (T.SubType) SubType, (T.FinancialYear)
FinancialYear, (T.Mnth) Mnth, (T.vYear) vYear, (T.Location) Location, (T.ForLocation)
ForLocation, (T.AorB) AorB, (T.VNo) VNo, (T.srno) SrNo, (T.dated) Dated,
(T.Client_ID) Client_ID, (C.Title) Client_Title, (T.Item_ID) Item_ID, (IsNull(I.Title, D.Title))
Item_Title, (T.Delivered) Delivered, (T.Returned) Returned, (T.Invoiced)
Invoiced, (I.Unit) Unit, (T.Rate) Rate, (Substring(T.Remarks, 1, 1000))
Remarks, (T.Emp_ID) Emp_ID, (ED.Title) Emp_Title, (T.DeliveryChallanNo) DeliveryChallanNo,
(T.GDNDelivered) GDNDelivered, @iWR iWR, (T.EngineNo) EngineNo,
(T.FrameNo) FrameNo, (T.DeliveryChallanNo) ScrVoucher_No,
(Case When I.ID Is Null Then 'Services' Else 'Parts' End) ServiceOrPart, (T.VRegNo) VRegNo,
(T.ClaimType) ClaimType, (CP.ID) PrincipalCompany_ID, (CP.Title) PrincipalCompany_Title,
(T.STPercent) STPercent, (T.EDPercent) EDPercent, (T.OTPercent) OTPercent, (T.DiscP) DiscP,
(T.DiscA) DiscA, (T.GrossRate) GrossRate, (T.MainDiscP) MainDiscP,
(A.ID) Party_ID, (A.Title) Party_Title, (T.ClientPONo) ClientPONo, (T.ClientPODate) ClientPODate,
(V.ID1) Main_VTP, (T.GrossRate) SaleRate, (T.PaymentTerms) PaymentTerms, (CA.Terms) PaymentTermsCoa,
(T.Job_ID) Job_ID, (DJ.Title) Job_Title, (T.CostCenter_ID) CostCenter_ID, (T.Project_ID) Project_ID,
(CC.Title) CostCenter_Title, (DP.Title) Project_Title, (T.BatchNo) BatchNo, (T.ExpiryDate) ExpiryDate,
(TT.ID) Transporter_ID, (TT.Title) Transporter_Title, (Case When MR.VTP Is Null Then I.PackSize Else MR.PackSize End) QtyPerCTN,
(T.BQO) BQO, (T.BQD) BQD, (T.Gift_ID) Gift_ID, (G.Title) Gift_Title, (T.GQO) GQO, (T.GQD) GQD,
(T.BiltyNo) BiltyNo, (T.BiltyDate) BiltyDate, (T.OldSoftNo) OldSoftNo
from f_PendingDCDetails (@SID, @iFID, @iTID, @CID, @EnDate, @iWR, @iLoc, @iScrVNo, @iVtp, @iSubType, @iFinancialYear, @iMnth, @ivYear, @iLocation, @iForLocation, @iAorB, @iVNo, @iSrNo, @ForWhichForm) T
Left Join Coa31 I on I.id=T.Item_ID
Left Join Coa31 G on G.id=T.Gift_ID
Left Join Coa32 C On T.Client_ID=C.ID
Left Join EmployeeDetails ED On T.Emp_ID=ED.ID
Left Join DefJobs D On T.Item_ID=D.ID
Left Join Coa32 CP On T.PrincipalCompany_ID=CP.ID
Left Join AnalysisCodes A On T.Party_ID=A.ID
Left Join Vtp2 V On T.VTP=V.ID2
Left Join Coa321 CA on CA.ID=Case WHen CP.ID Is Null Then C.ID Else CP.ID End
Left Join DefJobs DJ On DJ.ID=T.Job_ID
Left Join CostCenters CC On CC.ID=T.CostCenter_ID
Left Join DefProjects DP On DP.ID=T.Project_ID
Left Join Coa32 TT On T.Transporter_ID=TT.ID
Left Join ManufacturingOrder_Det M On T.BatchNo=M.BatchNo
Left Join MiscItemRecepie MR On IsNull(M.RefVTP,'')=MR.VTP and IsNull(M.RefSubType,'')=MR.SubType and IsNull(M.RefFinancialYear,'')=MR.FinancialYear and IsNull(M.RefMnth,'')=MR.Mnth and IsNull(M.RefvYear,'')=MR.vYear and IsNull(M.RefLocation,'')=MR.Location and IsNull(M.RefForLocation,'')=MR.ForLocation and IsNull(M.RefAorB,'')=MR.AorB and IsNull(M.RefVNo,'')=MR.VNo
Return
End
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
