Hi
Footers not getting printed
DECLARE @TipoObjeto VARCHAR(10) DECLARE @DocEntry INT DECLARE @Comando VARCHAR(MAX) DECLARE @Tabela VARCHAR(3) SET @TipoObjeto = {?ObjectId@} SET @DocEntry = {?DocKey@} IF @TipoObjeto = '112' -- Draft BEGIN SET @Tabela = 'DRF' -- Draft/Rascunhos - ODRF END ELSE BEGIN SET @Tabela = 'INV' -- Pedido de compra - O[TABELA] END SET @COMANDO ='
SELECT CONVERT(VARCHAR,O[TABELA].DOCDATE,105) DOCDATE, CONVERT(VARCHAR,O[TABELA].DocDueDate,105) DocDueDate,
O[TABELA].CARDCODE, O[TABELA].DOCNUM, O[TABELA].CARDNAME, O[TABELA].NUMATCARD, ORDR.numatcard As ''CustOrderno'', CONVERT(VARCHAR,ORDR.TaxDate,105) As ''CustOrderDate'', O[TABELA].TaxDate, O[TABELA].DOCENTRY, O[TABELA].Header, O[TABELA].Comments As ''Remarks'', [TABELA]1.LineNum, [TABELA]1.ITEMCODE, O[TABELA].DISCSUM, [TABELA]1.QUANTITY, OITM.SalUnitMsr As ''SaleUoM'', (Case When [TABELA]1.Currency <> ''INR'' then [TABELA]1.GTotalFC Else [TABELA]1.GTotal End ) As GTotal, [TABELA]1."U_UTL_BOX" as ''No. of Box'', 0.0 As ''SchQty'', 0.0 As ''RepQty'', Case When O[TABELA].U_DDYN = ''N'' Then RIGHT(CONVERT(VARCHAR(10), b0.MnfDate, 105), 7) Else CONVERT(VARCHAR,b0.MnfDate,105) end As ''MFG'', '''' As Pack, B0.MnfSerial as ''BatchNum'' , isnull(B1.Quantity,0) as ''BthQty'', Case When O[TABELA].U_DDYN = ''N'' Then RIGHT(CONVERT(VARCHAR(10), b0.ExpDate, 105), 7) Else CONVERT(VARCHAR,b0.ExpDate,105) end As ''ExpDate'', T3.U_UTL_PLTS as ''Pallets'',T3.U_UTL_NBOX as ''Boxes'',T3.U_UTL_NTWT as ''Net Weight'',T3.U_UTL_GSWT as ''Gross Weight'',T3.U_UTL_DMNS as ''Dimensions'',
OCRD.Cellular, OCRD.Phone1, OCRD.Fax ''OCRDFAX'',
(select crd1.gstregnno from crd1 where crd1.cardcode=O[TABELA].cardcode and O[TABELA].shiptocode=crd1.address and CRD1.ADRESTYPE=''S'') as ShipToGSTReg , (select crd1.gstregnno from crd1 where crd1.cardcode=O[TABELA].cardcode and O[TABELA].paytocode=crd1.address and CRD1.ADRESTYPE=''B'') as BillToGSTReg , CASE WHEN ISNULL(T.BpGSTN,'''') = '''' THEN ''No'' ELSE ''Yes'' END AS ''GSTRegistered'',
(SELECT CompnyName FROM OADM) CompanyName, (SELECT CompnyAddr FROM OADM) CompanyAddr, (Select RevOffice FROM OADM) CompanyPAN, (Select Phone1 FROM OADM) CompanyPhone, (Select E_Mail FROM OADM) Email, (Select Fax FROM OADM) CompanyFax, (CASE WHEN [TABELA]1.CURRENCY = ''INR'' THEN isnull((O[TABELA].DOCTOTAL),0) ELSE isnull((O[TABELA].DOCTOTALFC),0) END ) DOCTOTAL,
OLCT.GSTRegnNo, [TABELA]1.DSCRIPTION As DSCRIPTION,upper(cast([TABELA]1.Text as nvarchar(max))) as [Text], [TABELA]1.PriceBefDi as PRICE, (Case When [TABELA]1.Currency <> ''INR'' then [TABELA]1.totalFrgn Else [TABELA]1.LineTotal End ) AS LINETOTAL, [TABELA]1.UNITMSR, [TABELA]1.DiscPrcnt , O[TABELA].RoundDif ,
(SELECT Case When O[TABELA].DOCCUR = ''INR'' Then SUM(LineTotal) Else Sum(totalFrgn) End FROM [TABELA]1 T WHERE T.DocEntry = O[TABELA].DocEntry) Taxable , (SELECT SUM(VatSum) FROM O[TABELA] WHERE T.DocEntry = O[TABELA].DocEntry) GSTTotal , (SELECT ISNULL(SUM(T.TaxSum),0.0) FROM [TABELA]4 T WHERE T.staType = ''-100'' AND T.TaxRate = 2.50 AND T.DocEntry = O[TABELA].DocEntry) CGST5, (SELECT ISNULL(SUM(T.TaxSum),0.0) FROM [TABELA]4 T WHERE T.staType = ''-100'' AND T.TaxRate = 6.0 AND T.DocEntry = O[TABELA].DocEntry) CGST12, (SELECT ISNULL(SUM(T.TaxSum),0.0) FROM [TABELA]4 T WHERE T.staType = ''-100'' AND T.TaxRate = 9.0 AND T.DocEntry = O[TABELA].DocEntry) CGST18, (SELECT ISNULL(SUM(T.TaxSum),0.0) FROM [TABELA]4 T WHERE T.staType = ''-100'' AND T.TaxRate = 14.0 AND T.DocEntry = O[TABELA].DocEntry) CGST28,
(SELECT ISNULL(SUM(T.TaxSum),0.0) FROM [TABELA]4 T WHERE T.staType = ''-150'' AND T.TaxRate = 2.50 AND T.DocEntry = O[TABELA].DocEntry) UTGST5, (SELECT ISNULL(SUM(T.TaxSum),0.0) FROM [TABELA]4 T WHERE T.staType = ''-150'' AND T.TaxRate = 6.0 AND T.DocEntry = O[TABELA].DocEntry) UTGST12, (SELECT ISNULL(SUM(T.TaxSum),0.0) FROM [TABELA]4 T WHERE T.staType = ''-150'' AND T.TaxRate = 9.0 AND T.DocEntry = O[TABELA].DocEntry) UTGST18, (SELECT ISNULL(SUM(T.TaxSum),0.0) FROM [TABELA]4 T WHERE T.staType = ''-150'' AND T.TaxRate = 14.0 AND T.DocEntry = O[TABELA].DocEntry) UTGST28,
(SELECT ISNULL(SUM(T.TaxSum),0.0) FROM [TABELA]4 T WHERE T.staType = ''-110'' AND T.TaxRate = 2.50 AND T.DocEntry = O[TABELA].DocEntry) SGST5, (SELECT ISNULL(SUM(T.TaxSum),0.0) FROM [TABELA]4 T WHERE T.staType = ''-110'' AND T.TaxRate = 6.0 AND T.DocEntry = O[TABELA].DocEntry) SGST12, (SELECT ISNULL(SUM(T.TaxSum),0.0) FROM [TABELA]4 T WHERE T.staType = ''-110'' AND T.TaxRate = 9.0 AND T.DocEntry = O[TABELA].DocEntry) SGST18, (SELECT ISNULL(SUM(T.TaxSum),0.0) FROM [TABELA]4 T WHERE T.staType = ''-110'' AND T.TaxRate = 14.0 AND T.DocEntry = O[TABELA].DocEntry) SGST28, (SELECT ISNULL(SUM(T.TaxSum),0.0) FROM [TABELA]4 T WHERE T.staType = ''-120'' AND T.TaxRate = 5.0 AND T.DocEntry = O[TABELA].DocEntry) IGST5, (SELECT ISNULL(SUM(T.TaxSum),0.0) FROM [TABELA]4 T WHERE T.staType = ''-120'' AND T.TaxRate = 12.0 AND T.DocEntry = O[TABELA].DocEntry) IGST12, (SELECT ISNULL(SUM(T.TaxSum),0.0) FROM [TABELA]4 T WHERE T.staType = ''-120'' AND T.TaxRate = 18.0 AND T.DocEntry = O[TABELA].DocEntry) IGST18, (SELECT ISNULL(SUM(T.TaxSum),0.0) FROM [TABELA]4 T WHERE T.staType = ''-120'' AND T.TaxRate = 28.0 AND T.DocEntry = O[TABELA].DocEntry) IGST28, ISNULL((SELECT TOP 1 ISNULL(TAXRATE,0) FROM [TABELA]4 WHERE [TABELA]4.DOCENTRY = [TABELA]1.DOCENTRY AND [TABELA]4.STATYPE IN (''-100'') AND [TABELA]4.RELATETYPE = 1 and [TABELA]4.LINENUM=[TABELA]1.LINENUM) ,0) AS CGSTRATE, ISNULL((SELECT TOP 1 ISNULL(TAXRATE,0) FROM [TABELA]4 WHERE [TABELA]4.DOCENTRY = [TABELA]1.DOCENTRY AND [TABELA]4.STATYPE IN (''-110'') AND [TABELA]4.RELATETYPE = 1 and [TABELA]4 .LINENUM=[TABELA]1 .LINENUM) ,0) AS SGSTRATE, ISNULL((SELECT TOP 1 ISNULL(TAXRATE,0) FROM [TABELA]4 WHERE [TABELA]4.DOCENTRY = [TABELA]1.DOCENTRY AND [TABELA]4.STATYPE IN (''-120'') AND [TABELA]4.RELATETYPE = 1 and [TABELA]4.LINENUM=[TABELA]1.LINENUM) ,0) AS IGSTRATE, ISNULL((SELECT TOP 1 ISNULL(TAXRATE,0) FROM [TABELA]4 WHERE [TABELA]4.DOCENTRY = [TABELA]1.DOCENTRY AND [TABELA]4.STATYPE IN (''-150'') AND [TABELA]4.RELATETYPE = 1 and [TABELA]4.LINENUM=[TABELA]1.LINENUM) ,0) AS UTGSTRATE, (SELECT ISNULL(SUM([TABELA]4.TaxSum),0.0) FROM [TABELA]4 WHERE [TABELA]4.DocEntry = O[TABELA].DocEntry AND RelateType > 1 AND [TABELA]4.staType IN (''-100'')) As CGSTFreight, (SELECT ISNULL(SUM([TABELA]4.TaxSum),0.0) FROM [TABELA]4 WHERE [TABELA]4.DocEntry = O[TABELA].DocEntry AND RelateType > 1 AND [TABELA]4.staType IN (''-110'')) As SGSTFreight, (SELECT ISNULL(SUM([TABELA]4.TaxSum),0.0) FROM [TABELA]4 WHERE [TABELA]4.DocEntry = O[TABELA].DocEntry AND RelateType > 1 AND [TABELA]4.staType IN (''-120'')) As IGSTFreight,
ISNULL((SELECT R4.TaxSum FROM [TABELA]4 R4 WHERE R4.staType IN (''-100'') AND RelateType = 1 AND R4.DocEntry = O[TABELA].DocEntry AND R4.LineNum = [TABELA]1.LineNum),0.0) AS CGSTAMNT, ISNULL((SELECT R4.TaxSum FROM [TABELA]4 R4 WHERE R4.staType IN (''-110'') AND RelateType = 1 AND R4.DocEntry = O[TABELA].DocEntry AND R4.LineNum = [TABELA]1.LineNum),0.0) AS SGSTAMNT, ISNULL((SELECT R4.TaxSum FROM [TABELA]4 R4 WHERE R4.staType IN (''-120'') AND RelateType = 1 AND R4.DocEntry = O[TABELA].DocEntry AND R4.LineNum = [TABELA]1.LineNum),0.0) AS IGSTAMNT, ISNULL((SELECT R4.TaxSum FROM [TABELA]4 R4 WHERE R4.staType IN (''-150'') AND RelateType = 1 AND R4.DocEntry = O[TABELA].DocEntry AND R4.LineNum = [TABELA]1.LineNum),0.0) AS UTGSTAMNT, ISNULL((SELECT R4.TaxSum FROM [TABELA]4 R4 WHERE R4.staType IN (''7'') AND RelateType = 1 AND R4.DocEntry = O[TABELA].DocEntry AND R4.LineNum = [TABELA]1.LineNum),0.0) AS TCS,
CASE WHEN [TABELA]1.itemcode in (''SERVICES'',''NI20000'') THEN (select REPLACE(OCHP.CHAPTERID,''.'','''') FROM OCHP WHERE OCHP.ABSENTRY =[TABELA]1.hsnentry ) ELSE (SELECT REPLACE(OCHP.CHAPTERID,''.'','''') FROM OCHP WHERE OCHP.ABSENTRY = OITM.CHAPTERID ) END As ''TARIFF HEADING'', (select ServCode FROM OSAC WHERE OSAC.ABSENTRY =[TABELA]1.SacEntry) As ''TARIFF HEADING1'', OLCT.GSTREGNNO LOCTGSTREGNO, OLCT.TanCirNo As ''MfrLNo.'' , OLCT.TanOfficer As ''CINNo'' ,
OLCT.PanNo As ''LocPANNo'', (SELECT GSTCode FROM OCST WHERE OCST.Code = OLCT.State AND Country = ''IN'') As ''LocStateCode'', (SELECT Name FROM OCST WHERE OCST.Code = OLCT.State AND Country = ''IN'') As ''LocStateName'', OCST.GSTCODE, Isnull(OLCT.TanWardNo,'''') As ''WHLNo.'' , OLCT.TanOfficer As ''InsPNo.'', OLCT.PANofficer As ''IEC_Code'', OLCT.U_UTL_FAX as ''Company'',
ISNULL(T.Address2B,'''') + '' '' + ISNULL(T.Address3B,'''') ''BillToDLNo'', crd7.TaxId0 AS ''BP Pan No.'',crd7.TaxId0 ''SHIP Pan No.'', [TABELA]26.TransDocNo ''LR NO'', CONVERT(VARCHAR,[TABELA]26.TransDate,105) ''LR Date'', OETM.ModeName As ''TransMode'', ODLN.DOCNUM As ''ChallanNo'', CONVERT(VARCHAR,ODLN.TaxDate,105) as ''Challandate'', O[TABELA].U_UTL_NBOX AS ''NoofCase'',O[TABELA].Comments, [TABELA]26."TransName" as ''Transporter Name'', [TABELA]26.EWayBillNo, CONVERT(VARCHAR,[TABELA]26.EwbDate,105) As EwbDate,[TABELA]26.VehicleNo,
(ISNULL(OLCT.STREET,'''') +'' ''+ ISNULL(OLCT.BLOCK,'''') +'' ''+ Convert(Varchar(Max),ISNULL(OLCT.Building,'''')) +'' ''+ ISNULL(OLCT.CITY ,'''') +'' ''+ ISNULL(OLCT.ZIPCODE,'''') +'' ''+ ISNULL(OLCT.State,'''') +'' ''+ case when ISNULL(OLCT.Country,'''')=''IN'' THEN ''India'' End ) AS LocAddress, (ISNULL(CAST(OLCT.Building as nvarchar(max)),'''') + (ISNULL(OLCT.BLock,'''') +'' ''+ ISNULL(OLCT.Street,'''') +'' ''+ ISNULL(OLCT.CITY ,'''') +'' ''+ ISNULL(OLCT.ZIPCODE,'''') +'','' --'' ''+ Case when ISNULL(OLCT.State,'''')=''PB'' then ''Punjab'' end +'' ''+ case when ISNULL(OLCT.Country,'''')=''IN'' THEN ''INDIA'' End ) )AS WhsAddress,
--(SELECT TOP 1 (cast((ISNULL(T.building,'''')) AS NVARCHAR) + '' '' + ISNULL(T.Street,'''') + '' '' + ISNULL(T.Block,'''') + ISNULL(T.City,'''') + '' '' + ISNULL(OCST.Name,'''') + '' '' + ISNULL(T.ZipCode,'''') + '' '' + ISNULL(OCRY.Name,'''')) As BillToAddess -- FROM CRD1 T LEFT OUTER JOIN OCST ON OCST.CODE = T.STATE AND OCST.COUNTRY = ''IN'' -- LEFT OUTER JOIN OCRY ON OCRY.CODE = T.COUNTRY -- WHERE T.AdresType = ''B'' AND T.CardCode = OCRD.CARDCODE) As ''Bill To Address'' ,
(cast((ISNULL(T.buildingB,'''')) AS NVARCHAR(max)) + '' '' + ISNULL(T.StreetB,'''') + '' '' + ISNULL(T.BlockB,'''') + ISNULL(T.CityB,'''') + '' '' + Isnull((sELECT top 1 ISNULL(OCST.Name,'''') FROM OCST WHERE OCST.CODE = T.StateB) ,'''') + '' '' + ISNULL(T.ZipCodeB,'''') + '' '' + ISNULL(OCRY.Name,'''')) As ''Bill to Address'' ,
--( CAST((ISNULL(CRD1.building,'''')) AS NVARCHAR) + '' '' + ISNULL(CRD1.Street,'''') + '' '' + ISNULL(CRD1.Block,'''') + ISNULL(CRD1.City,'''') + '' '' + ISNULL(OCST.Name,'''') + '' '' + ISNULL(CRD1.ZipCode,'''') + '' '' + ISNULL(OCRY.Name,'''')) ''Ship to Address'', (cast((ISNULL(T.buildingS,'''')) AS NVARCHAR(max)) + '' '' + ISNULL(T.StreetS,'''') + '' '' + ISNULL(T.BlockS,'''') + ISNULL(T.CityS,'''') + '' '' + ISNULL(OCST.Name,'''') + '' '' + ISNULL(T.ZipCodeS,'''') + '' '' + ISNULL(OCRY.Name,'''')) As ''Ship to Address'' ,
ISNULL((SELECT OCST.NAME FROM OCST WHERE OCST.CODE = T.StateB AND OCST.COUNTRY = ''IN''),'''') ''BillToState'', ISNULL((SELECT OCST.GSTCode FROM OCST WHERE OCST.CODE = T.StateB AND OCST.COUNTRY = ''IN''),'''') ''BillGSTCODE'',
ISNULL((SELECT OCST.NAME FROM OCST WHERE OCST.CODE = T.STATES AND OCST.COUNTRY = ''IN''),'''') ''ShipToState'' , T.BPStatGSTN AS ''ShipGSTCODE'', OITM.U_UTL_PKSZ as ''Pack Size'',
ISNULL(T.ZipCodeB,'''') ''BillToZIP'', ISNULL(T.ZipCodeS,'''') ''ShipToZIP'',
NNM1.SeriesName as Series, NNM1.BeginStr, OLCT.Location, OCST.Name as ''StateName'', OCST.GSTCode As ''StateCode'', '''' As ''Department'', (SELECT TOP 1 OCTG.PymntGroup FROM OCTG WHERE OCTG.GroupNum=OCRD.GroupNum) ''PaymentTerms'', isnull((SELECT SUM([TABELA]3.LineTotal) FROM [TABELA]3 WHERE [TABELA]3.DocEntry=O[TABELA].DocEntry),0.00) As ''Freight'', ''N'' As ''ReverserChrges'', O[TABELA].DOCCUR As ''DocCurr'',OCRN.DocCurrCod ''CurrSym'', OCRN.ChkNAme as ''CurrFName'', OCRN.Chk100Name as ''CurrSName'', (Select Top 1 ODSC.BankName from OADM left join ODSC on OADM.DflBnkCode = ODSC.BankCode) As ''BankName'', (Select Top 1 OADM.DflBnkAcct from OADM ) As ''ACNo.'', (Select Top 1 DSC1.SWIFTNUM from OADM Left Join DSC1 on OADM.DflBnkAcct = DSC1.Account) As ''IFSC'', (Select Top 1 DSC1.Branch from OADM Left Join DSC1 on OADM.DflBnkAcct = DSC1.Account) As ''Branch'', (Select Sum(A.TaxSum) From [TABELA]4 A Where O[TABELA].DocEntry=A.DocEntry and A.StaCode like ''%TCS%'') As ''TCSAmt'' ,(O[TABELA].DpmAmnt+O[TABELA].DpmVat) As ''DownPaymentValue''
,dln1.docentry,O[TABELA].u_lut
-----------------------------------------------IRN & QR Code-----------------------------------------
,(Select AA.U_UTL_QRPT from [@UTL_MDEXTH] AA where AA.U_UTL_BaseEntry =O[TABELA].DocEntry and AA.U_UTL_IST =''S'' AND AA.U_UTL_QRPT is not null group by AA.U_UTL_QRPT ) [QR Code]
,(Select Distinct AA.U_UTL_IRN from [@UTL_MDEXTH] AA where AA.U_UTL_BaseEntry = O[TABELA].DocEntry and AA.U_UTL_IST =''S'' AND AA.U_UTL_QRPT is not null group by AA.U_UTL_IRN) [IRN No] ,(Select Distinct AA.U_UTL_AckNo from [@UTL_MDEXTH] AA where AA.U_UTL_BaseEntry = O[TABELA].DocEntry and AA.U_UTL_IST =''S'' AND AA.U_UTL_QRPT is not null group by AA.U_UTL_AckNo) [Ack no] ,(Select Distinct AA.U_UTL_IRNGENDT from [@UTL_MDEXTH] AA where AA.U_UTL_BaseEntry = O[TABELA].DocEntry and AA.U_UTL_IST =''S'' AND AA.U_UTL_QRPT is not null group by AA.U_UTL_IRNGENDT) [Ack dt]
FROM O[TABELA] INNER JOIN [TABELA]1 ON O[TABELA].DOCENTRY=[TABELA]1.DOCENTRY LEFT JOIN OCRD ON O[TABELA].CARDCODE= OCRD.CARDCODE LEFT JOIN [TABELA]26 on O[TABELA].DocEntry=[TABELA]26.DocEntry LEFT JOIN OETM ON OETM.ModeCode = [TABELA]26.TransMode LEFT JOIN [TABELA]12 T on O[TABELA].DocEntry=T.DocEntry LEFT JOIN DLN1 ON [TABELA]1.BaseEntry=DLN1.DOCENTRY AND [TABELA]1.BaseLine=DLN1.LineNum AND [TABELA]1.BaseType = ''15'' LEFT JOIN ODLN ON DLN1.DOCENTRY=ODLN.DOCENTRY LEFT JOIN RDR1 ON DLN1.BaseEntry=RDR1.DOCENTRY AND DLN1.BaseLine=RDR1.LineNum LEFT JOIN ORDR ON ORDR.DocEntry=RDR1.DOCENTRY --LEFT JOIN CRD1 ON O[TABELA].CARDCODE= CRD1.CARDCODE AND CRD1.ADRESTYPE=''S'' LEFT JOIN CRD7 on O[TABELA].CardCode=CRD7.CardCode AND CRD7.ADdrTYPE=''S'' and O[TABELA].shiptocode=crd7.address INNER JOIN OITM ON OITM.ITEMCODE = [TABELA]1.ITEMCODE LEFT JOIN OWHS ON OWHS.WhsCode =[TABELA]1.WhsCode LEFT JOIN OLCT ON OLCT.CODE =[TABELA]1.LOCCODE LEFT JOIN OCST ON OCST.CODE =T.STATEB AND OCST.COUNTRY = ''IN'' LEFT JOIN OCRY ON OCRY.CODE =T.COUNTRYB LEFT JOIN NNM1 ON O[TABELA].SERIES=NNM1.SERIES --LEFT JOIN CRD1 T3 ON O[TABELA].CARDCODE= T3.CARDCODE AND O[TABELA].PayToCode=T3.ADDRESS AND T3.ADRESTYPE=''B'' LEFT JOIN IBT1 B1 ON B1.ItemCode = DLN1.ItemCode AND B1.BaseType = ''15'' AND B1.BaseEntry = DLN1.DocEntry AND B1.BaseLinNum = DLN1.LineNum LEFT JOIN OBTN B0 ON B0.DistNumber = B1.BatchNum AND B0.ItemCode = B1.ItemCode LEFT JOIN OSHP ON O[TABELA].TrnspCode=OSHP.TrnspCode LEFT JOIN OCRN ON O[TABELA].DOCCUR = OCRN.CurrCode LEFT JOIN [@UTL_NOOFBOXHED] T2 ON O[TABELA].DocEntry=T2.U_UTL_DOCE and [TABELA]1.LineNum=T2.U_UTL_LNO LEFT JOIN [@UTL_NOOFBOXDET] T3 ON B1.BatchNum=T3.U_UTL_BTCH and T2.DocEntry=t3.DocEntry
where o[TABELA].docentry=@docentry'
SELECT @COMANDO = REPLACE(@COMANDO, '[TABELA]', @Tabela) SELECT @COMANDO = REPLACE(@COMANDO, '@DocEntry', @DocEntry) EXEC(@COMANDO) SET QUOTED_IDENTIFIER ON
Thanks