/forums/uploadfile/f61a1a/08202023162033PM/HSN_Sale_Item.rarHi
I have Sub Report attached. How to get Docentry & Object Id values in Sub Report.
Below is the Main Report
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, 0.0 As ''SchQty'', 0.0 As ''RepQty'', B0.MnfSerial as ''BatchNum'' , isnull(B1.Quantity,0) as ''BthQty'', 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'', 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'', 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'', 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'' , 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 where o[TABELA].docentry=@docentry' SELECT @COMANDO = REPLACE(@COMANDO, '[TABELA]', @Tabela) SELECT @COMANDO = REPLACE(@COMANDO, '@DocEntry', @DocEntry) EXEC(@COMANDO) SET QUOTED_IDENTIFIER ON
/forums/uploadfile/f61a1a/08202023162033PM/HSN_Sale_Item.rar
Thanks