How to get Docentry & Object Id values in Sub Report

/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