namespace ComplianceDAL.Query.CanonicalBuilder; public static class CanonicalBuilderQB { // Faithful port of GB4's EInvoiceQueryBuilder.GET_EINVOICE_HEADER (SQL Server variant) — same // tables/joins/CASE mappings, confirmed by the user to still be the real GB5 schema. Only two // adaptations from the GB4 source: ":clientid" (a literal string-replace in GB4) is bound as a // real @ClientId parameter, and an explicit @DocumentId filter replaces GB4's dynamic // CriteriaDTO-based filter (ApplyEInvoiceFilter), since this is always called for one document. public const string GET_CANONICAL_INVOICE_HEADER = @" SELECT '1.1' AS Version, '' AS Irn, 'GST' AS TranDtlsTaxSch, CASE c.GCMCODE WHEN '1' THEN 'B2B' WHEN '2' THEN 'B2C' WHEN '3' THEN 'B2B' WHEN '4' THEN 'B2B' WHEN '13' THEN 'SEZWP' WHEN '14' THEN 'SEZWOP' WHEN '5' THEN 'EXPWP' WHEN '6' THEN 'EXPWOP' WHEN '15' THEN 'DEXP' WHEN '10' THEN 'B2B' WHEN '11' THEN 'B2B' WHEN '18' THEN 'EXPWOP' WHEN '19' THEN 'EXPWP' WHEN '20' THEN 'B2B' WHEN '21' THEN 'B2B' WHEN '25' THEN 'B2B' WHEN '26' THEN 'EXPWOP' WHEN '27' THEN 'EXPWP' WHEN '28' THEN 'B2B' WHEN '29' THEN 'B2B' ELSE '*' END AS TranDtlsSupTyp, 'N' AS TranDtlsRegRev, '' AS TranDtlsEcmGstin, 'N' AS TranDtlsIgstOnIntra, CASE CLASS.BIZTRANSACTIONCLASSID WHEN (-1799999904) THEN 'INV' --SALESINVC WHEN (-1799999911) THEN 'INV' --Miscellaneous Delivery Challan WHEN (-1399999924) THEN 'INV' --JWBP WHEN -1399999949 THEN 'DBN' --DEBITNOTE WHEN -1399999948 THEN 'CRN' --CREDITNOTE WHEN -1799999922 THEN 'DBN' --PURCHRET WHEN -1399999855 THEN 'DBN' --SUBBRTN WHEN -1399999919 THEN 'CRN' --JWBR WHEN -1799999903 THEN 'CRN' --SALESRET END AS DocDtlsTyp, A.documentnumber AS DocDtlsNo, CONVERT(VARCHAR, A.DOCUMENTDATE, 103) AS DocDtlsDt, CASE WHEN @ClientId = -1399999740 THEN StoreAddress.GSTIN ELSE selleraddress.GSTIN END AS SellerDtlsGstin, company.COMPANYNAME AS SellerDtlsLglNm, company.COMPANYNAME AS SellerDtlsTrdNm, CASE WHEN @ClientId = -1399999740 THEN StoreAddress.ADDRESSLINE1 + ' ' + StoreAddress.ADDRESSLINE2 ELSE selleraddress.ADDRESSLINE1 + ' ' + selleraddress.ADDRESSLINE2 END AS SellerDtlsAddr1, CASE WHEN @ClientId = -1399999740 THEN StoreAddress.ADDRESSLINE3 + ' ' + StoreAddress.ADDRESSLINE4 ELSE selleraddress.ADDRESSLINE3 + ' ' + selleraddress.ADDRESSLINE4 END AS SellerDtlsAddr2, CASE WHEN @ClientId = -1399999740 THEN StoreAddress.LOCATIONCODE ELSE sellerlocation.LOCATIONCODE END AS SellerDtlsLoc, CASE WHEN @ClientId = -1399999740 THEN StoreAddress.ZIPCODE ELSE Selleraddress.ZIPCODE END AS SellerDtlsPin, CASE WHEN @ClientId = -1399999740 THEN Substring(StoreAddress.GSTIN, 1, 2) ELSE Substring(selleraddress.GSTIN, 1, 2) END AS SellerDtlsState, CASE WHEN @ClientId = -1399999740 THEN StoreAddress.Mobile ELSE selleraddress.Mobile END AS SellerDtlsPh, CASE WHEN @ClientId = -1399999740 THEN [DBO].GETCOLUMNVALUE(StoreAddress.Mail, ';', 1) ELSE [DBO].GETCOLUMNVALUE(selleraddress.Mail, ';', 1) END AS SellerDtlsEm, Billtoaddress.GSTIN AS BuyerDtlsGstin, billtoaddressparty.PartyName AS BuyerDtlsLglNm, billtoaddressparty.PartyName AS BuyerDtlsTrdNm, BilltoaddressState.GSTSTATECODE AS BuyerDtlsPos, Billtoaddress.ADDRESSLINE1 + ' ' + Billtoaddress.ADDRESSLINE2 AS BuyerDtlsAddr1, Billtoaddress.ADDRESSLINE3 + ' ' + Billtoaddress.ADDRESSLINE4 AS BuyerDtlsAddr2, Billtoaddresslocation.LOCATIONNAME AS BuyerDtlsLoc, Billtoaddress.ZIPCODE AS BuyerDtlsPin, BilltoaddressState.GSTSTATECODE AS BuyerDtlsState, Billtoaddress.Mobile AS BuyerDtlsPh, [DBO].GETCOLUMNVALUE(Billtoaddress.Mail, ';', 1) AS BuyerDtlsEm, CASE WHEN Shipfromaddressparty.PARTYID = -1 THEN 'NOTREQ' ELSE Shipfromaddressparty.PARTYNAME END AS DispDtlsNm, Shipfromaddress.addressLine1 + ' ' + Shipfromaddress.addressLine2 AS DispDtlsAddr1, Shipfromaddress.addressLine3 + ' ' + Shipfromaddress.addressLine4 AS DispDtlsAddr2, Shipfromaddresslocation.LocationName AS DispDtlsLoc, Shipfromaddress.ZIPCODE AS DispDtlsPin, ShipfromaddressState.GSTSTATECODE AS DispDtlsStcd, CASE WHEN ((Billtoaddress.addressid = shiptoaddress.addressid) OR shiptoaddress.addressid = -1) THEN '*' ELSE shiptoaddress.GSTIN END AS ShipDtlsGstin, Shiptoaddresspartybranch.partyBRANCHName AS ShipDtlsLglNm, Shiptoaddresspartybranch.partyBRANCHName AS ShipDtlsTrdNm, shiptoaddress.addressLine1 + ' ' + shiptoaddress.addressLine2 AS ShipDtlsAddr1, shiptoaddress.addressLine3 + ' ' + shiptoaddress.addressLine4 AS ShipDtlsAddr2, shiptoaddresslocation.LocationName AS ShipDtlsLoc, shiptoaddress.ZIPCODE AS ShipDtlsPin, shiptoaddressstate.GSTSTATECODE AS ShipDtlsStcd, headercharge.AVALAMOUNT AS ValDtlsAssVal, headercharge.CGSTAmount AS ValDtlsCgstVal, headercharge.SGSTAmount AS ValDtlsSgstVal, headercharge.IGSTAmount AS ValDtlsIgstVal, headercharge.CESSGSTAmount AS ValDtlsCesVal, headercharge.STCESSGSTAmount AS ValDtlsStCesVal, headercharge.HEADERDISCOUNT AS ValDtlsDiscAmt, CAST(headercharge.HEADEROTHCHARGES AS Numeric(18, 2)) AS ValDtlsOtherCharge, CAST(headercharge.ROUNDOFF AS Numeric(18, 2)) AS ValDtlsRndOffAmt, CASE WHEN Class.biztransactionclassid IN (-1799999903, -1399999949, -1399999948) THEN A.BILLVALUE ELSE a.TOTALNETVALUE END AS ValDtlsTotInvVal, CASE WHEN Class.biztransactionclassid IN (-1799999903, -1399999949, -1399999948) THEN A.BILLVALUEFC ELSE a.TOTALNETVALUEFC END AS ValDtlsTotInvValFc FROM TMMHead A CROSS apply(SELECT TOP(1) * FROM MADDRESS WHERE OBJECTTYPEID = -1899999977 AND Objectid = A.OUID) selleraddress CROSS apply(SELECT TOP(1) aaa.*, bbb.LOCATIONCODE FROM MADDRESS aaa, MLOCATION bbb WHERE aaa.OBJECTTYPEID = -1899997963 AND aaa.LOCATIONID = bbb.LOCATIONID AND Objectid = A.FROMSTOREID) StoreAddress CROSS apply (SELECT Sum(cp.AVAL) AVALAMOUNT, Sum(cp.CGSTVALUE + cp.CGSTINVALUE) CGSTAmount, Sum(cp.SGSTVALUE + cp.SGSTINVALUE) SGSTAmount, Sum(cp.IGSTVALUE + cp.IGSTINVALUE) IGSTAmount, Sum(cp.CESSGSTVALUE + CESNONADVLVALUE) CESSGSTAmount, Sum(cp.STATECESSVALUE) STCESSGSTAmount, Sum(cp.HEADEROTHCHARGES) HEADEROTHCHARGES, Sum(cp.HEADERDISCOUNT) HEADERDISCOUNT, Sum(cp.ROUNDOFF) ROUNDOFF FROM TMMCHARGESPIVOT cp, TMMDETAIL cpdtl, MBIZTRANSACTIONTYPE biztype WHERE 1 = 1 AND cp.DOCUMENTID = a.DOCUMENTID AND cp.DOCUMENTDETAILID = cpdtl.DOCUMENTDETAILID AND biztype.BIZTRANSACTIONTYPEID = a.BIZTRANSACTIONTYPEID AND cpdtl.STOCKPOSTTYPE = CASE WHEN biztype.biztransactionclassid IN (-1399999924, -1399999936) THEN 2 ELSE cpdtl.STOCKPOSTTYPE END ) headercharge CROSS apply (SELECT Sum(DISCOUNTAMOUNT) DISCOUNTAMOUNT FROM TMMDETAIL WHERE DOCUMENTID = a.DOCUMENTID) dtltotdiscount, MTRANSACTIONTYPE B, mgcm C, mbiztransactionTYPE BIZTYPE, mbiztransactionclass CLASS, MORGANIZATIONUNIT ou, MCOMPANY company, MLOCATION Sellerlocation, MSTATE SellerState, MADDRESS Billtoaddress, MLOCATION Billtoaddresslocation, MSTATE BilltoaddressState, MPARTYBRANCH billtoaddresspartybranch, MPARTY billtoaddressparty, MSTORE store, MLOCATION storeaddresslocation, MSTATE storeaddressState, MADDRESS Shiptoaddress, MLOCATION Shiptoaddresslocation, MSTATE ShiptoaddressState, MPARTYBRANCH Shiptoaddresspartybranch, MPARTY Shiptoaddressparty, MPARTYBRANCH Shipfromaddresspartybranch, MPARTY Shipfromaddressparty, MADDRESS Shipfromaddress, MLOCATION Shipfromaddresslocation, MSTATE ShipfromaddressState WHERE 1 = 1 AND a.STATUS = 1 AND a.DUTYtRANSACTIONtYPEID = b.TRANSACTIONTYPEID AND B.GSTCATEGORYID = c.gcmid AND a.biztransactiontypeid = BIZTYPE.biztransactiontypeid AND BIZTYPE.biztransactionclassid = CLASS.biztransactionclassid AND A.OUID = ou.OUID AND ou.COMPANYID = company.CompanyId AND selleraddress.LOCATIONID = Sellerlocation.LOCATIONID AND selleraddress.STATEID = SellerState.STATEID AND a.BILLTOADDRESSID = Billtoaddress.ADDRESSID AND Billtoaddress.LOCATIONID = Billtoaddresslocation.LOCATIONID AND Billtoaddress.STATEID = BilltoaddressState.STATEID AND Billtoaddress.OBJECTID = billtoaddresspartybranch.PARTYBRANCHID AND billtoaddresspartybranch.PARTYID = billtoaddressparty.PARTYID AND StoreAddress.OBJECTID = store.STOREID AND StoreAddress.LOCATIONID = storeaddresslocation.LOCATIONID AND StoreAddress.STATEID = storeaddressState.STATEID AND a.SHIPTOADDRESSID = Shiptoaddress.ADDRESSID AND Shiptoaddress.LOCATIONID = Shiptoaddresslocation.LOCATIONID AND Shiptoaddress.STATEID = ShiptoaddressState.STATEID AND Shiptoaddress.OBJECTID = Shiptoaddresspartybranch.PARTYBRANCHID AND Shiptoaddresspartybranch.PARTYID = Shiptoaddressparty.PARTYID AND Shipfromaddresspartybranch.PARTYBRANCHID = A.SHIPFROMPARTYBRANCHID AND Shipfromaddressparty.PARTYID = A.SHIPFROMPARTYID AND Shipfromaddress.ADDRESSID = A.SHIPFROMADDRESSID AND Shipfromaddresslocation.LOCATIONID = Shipfromaddress.LOCATIONID AND ShipfromaddressState.STATEID = Shipfromaddress.STATEID AND Class.biztransactionclassid IN (-1799999904, -1399999949, -1399999948, -1799999922, -1399999855, -1399999919, -1799999903, -1399999924, -1799999911) AND A.documentid = @DocumentId"; // Faithful port of GB4's EInvoiceQueryBuilder.GET_EINVOICE_DETAIL (SQL Server variant) — same // tables/joins, same CASE mappings. Only adaptation: an explicit @DocumentId filter replaces GB4's // dynamic CriteriaDTO-based filter (ApplyEInvoiceFilter), since this is always called for one document. public const string GET_CANONICAL_INVOICE_DETAIL = @" SELECT cast(Row_number() OVER (ORDER BY dtl.documentdetailid) AS varchar(20)) AS SlNo, CASE WHEN item.ISACTIVITY = 0 THEN 'Y' ELSE 'N' END AS IsServc, dtl.PRODUCTDESCRIPTION AS PrdDesc, CASE WHEN Class.biztransactionclassid IN (-1399999924) THEN isnull(dutytable.DUTYTARIFFCODE, '0') ELSE dutytarrif.DUTYTARIFFCODE END AS HsnCd, item.ITEMCODE AS Barcde, CAST(dtl.TRANSACTIONQUANTITY AS NUMERIC(18, 3)) AS Qty, CAST(CASE WHEN dtl.ISFREE = 0 THEN dtl.TRANSACTIONQUANTITY ELSE 0 END AS NUMERIC(18, 3)) AS FreeQty, transactionuom.uomcode AS Unit, CAST(dtl.TRANSACTIONRATE AS Numeric(18, 2)) AS UnitPrice, CAST(dtl.ITEMBASICVALUE AS Numeric(18, 2)) AS TotAmt, CAST(detailcharge.DISCPERVALUE AS Numeric(18, 2)) AS Discount, CAST(detailcharge.AVALAmount AS Numeric(18, 2)) AS PreTaxVal, CAST(detailcharge.AVALAmount AS Numeric(18, 2)) AS AssAmt, CAST(detailcharge.GstRate AS Numeric(18, 2)) AS GstRt, CAST(detailcharge.IGSTAmount AS Numeric(18, 2)) AS IgstAmt, CAST(detailcharge.CGSTAMOUNT AS Numeric(18, 2)) AS CgstAmt, CAST(detailcharge.SGSTAmount AS Numeric(18, 2)) AS SgstAmt, CAST(detailcharge.CESSGSTPer AS Numeric(18, 2)) AS CesRt, CAST(detailcharge.CESSGSTAMOUNT AS Numeric(18, 2)) AS CesAmt, CAST(detailcharge.CesNonAdvlAmount AS Numeric(18, 2)) AS CesNonAdvlAmt, CAST(detailcharge.STCESSGSTPer AS Numeric(18, 2)) AS StateCesRt, CAST(detailcharge.STCESSGSTAmount AS Numeric(18, 2)) AS StateCesAmt, CAST(detailcharge.StateCesNonAdvlAmount AS Numeric(18, 2)) AS StateCesNonAdvlAmt, CAST(detailcharge.DETAILOTHCHARGES AS Numeric(18, 2)) AS OthChrg, CASE WHEN Class.biztransactionclassid IN (-1399999949, -1399999948, -1799999903) THEN CAST(dtl.ITEMREALISATIONVALUE AS Numeric(18, 2)) ELSE CAST(dtl.ITEMGROSSVALUE + detailcharge.DETAILDISCOUNT AS Numeric(18, 2)) END AS TotItemVal, '' AS OrdLineRef, '' AS OrgCntry, '' AS PrdSlNo FROM TMMHead A, TMMDETAIL dtl LEFT OUTER JOIN TMMDETAILPROCESS dtlprocess ON (dtl.documentdetailid = dtlprocess.documentdetailid) LEFT OUTER JOIN MPROCESS proce ON (dtlprocess.processid = proce.processid) LEFT OUTER JOIN MDUTYTARIFF dutytable ON (proce.GSTTARIFFID = dutytable.DUTYTARIFFID) OUTER apply (SELECT AVAL AS AVALAMOUNT, CGSTINPUT + SGSTINPUT + IGSTINPUT + CGSTININPUT + SGSTININPUT + IGSTININPUT AS GstRate, IGSTVALUE + IGSTINVALUE AS IGSTAmount, CGSTVALUE + CGSTINVALUE AS CGSTAmount, SGSTVALUE + SGSTINVALUE AS SGSTAmount, CESSGSTINPUT + CESSGSTININPUT AS CESSGSTPer, CESSGSTVALUE + CESSGSTINVALUE AS CESSGSTAmount, CESNONADVLVALUE AS CesNonAdvlAmount, STATECESSINPUT AS STCESSGSTPer, STATECESSVALUE AS STCESSGSTAmount, DISCPERVALUE + DISCAMTVALUE AS DISCPERVALUE, STATECESNONADVLVALUE AS StateCesNonAdvlAmount, DETAILOTHCHARGES AS DETAILOTHCHARGES, DETAILDISCOUNT AS DETAILDISCOUNT FROM TMMCHARGESPIVOT WHERE DOCUMENTID = dtl.DOCUMENTID AND documentdetailid = dtl.documentdetailid) detailcharge, MTRANSACTIONTYPE B, mgcm C, mbiztransactionTYPE BIZTYPE, mbiztransactionclass CLASS, MITEM item, MDUTYTARIFF dutytarrif, MUOM transactionuom WHERE 1 = 1 AND A.documentid = dtl.documentid AND a.DUTYtRANSACTIONtYPEID = b.TRANSACTIONTYPEID AND B.GSTCATEGORYID = c.gcmid AND a.biztransactiontypeid = BIZTYPE.biztransactiontypeid AND BIZTYPE.biztransactionclassid = CLASS.biztransactionclassid AND dtl.itemid = item.itemid AND item.GSTTARIFFID = dutytarrif.DUTYTARIFFID AND dtl.transactionuomid = transactionuom.uomid AND Class.biztransactionclassid IN (-1799999904, -1399999949, -1399999948, -1799999922, -1399999855, -1399999919, -1799999903, -1399999924, -1799999911) AND dtl.STOCKPOSTTYPE = CASE WHEN Class.biztransactionclassid IN (-1399999924, -1399999936) THEN 2 ELSE dtl.STOCKPOSTTYPE END AND dtl.KITTYPE = 0 AND A.documentid = @DocumentId"; // Batch canonical build for a date range (for SyncGSTOutward / on-demand pull). // Kept as a single flattened join (header columns repeat per line; CanonicalBuilderDAL groups by // DocumentId and reads header fields from the first row of each group) — splitting into header + // detail per document would mean one extra round trip per document across the whole date range, // which defeats the point of a batch pull. Same verified joins as the single-document queries above. // Same tables as the header/detail queries above (MADDRESS polymorphic OBJECTTYPEID/OBJECTID // lookup, mgcm — not MGSTCATEGORY, which does not exist) — kept flattened per-line rather than // split into header+detail since this pulls many documents across a date range at once. // No CLIENTID/TENANTID filter — verified live that TMMHEAD has neither column; tenant isolation // for OLTP business tables happens at the database level (one DB per tenant), not via a // discriminator column, matching the single-document queries above which likewise filter on // nothing but @DocumentId. An earlier version of this query filtered on `h.TENANTID = @TenantId`, // which doesn't exist — meaning this query (and therefore SyncGSTOutwardAsync end-to-end) would // have thrown "Invalid column name 'TENANTID'" the moment it was actually run; fixed 2026-08-06. public const string GET_CANONICAL_INVOICES_BATCH = @" SELECT h.DOCUMENTID AS DocumentId, h.DOCUMENTNUMBER AS DocumentNumber, h.DOCUMENTDATE AS DocumentDate, CLASS.BIZTRANSACTIONCLASSID AS BizTransactionClassId, COALESCE(C.GCMCODE, '') AS GSTCategoryCode, COALESCE(selleraddress.GSTIN, '') AS SupplierGSTIN, COALESCE(Billtoaddress.GSTIN, '') AS ReceiverGSTIN, COALESCE(billtoaddressparty.PartyName, '') AS ReceiverLegalName, COALESCE(Billtoaddress.ZIPCODE, '') AS ReceiverPincode, COALESCE(BilltoaddressState.GSTSTATECODE, '') AS ReceiverStateCode, td.DOCUMENTDETAILID AS LineNumber, td.ITEMID AS ItemId, COALESCE(dutytariff.DUTYTARIFFCODE, '') AS HSNCode, COALESCE(uom.UOMCODE, '') AS UOM, COALESCE(td.TRANSACTIONACTUALQUANTITY, 0) AS Quantity, COALESCE(cp.AVAL, 0) AS TaxableValue, COALESCE(cp.IGSTINPUT, 0) AS IGSTRate, COALESCE(cp.IGSTVALUE, 0) AS IGSTAmount, COALESCE(cp.CGSTINPUT, 0) AS CGSTRate, COALESCE(cp.CGSTVALUE, 0) AS CGSTAmount, COALESCE(cp.SGSTINPUT, 0) AS SGSTRate, COALESCE(cp.SGSTVALUE, 0) AS SGSTAmount, COALESCE(cp.CESSGSTINPUT, 0) AS CessRate, COALESCE(cp.CESSGSTVALUE, 0) AS CessAmount FROM TMMHEAD h OUTER apply(SELECT TOP(1) * FROM MADDRESS WHERE OBJECTTYPEID = -1899999977 AND Objectid = h.OUID) selleraddress INNER JOIN MTRANSACTIONTYPE b ON b.TRANSACTIONTYPEID = h.DUTYTRANSACTIONTYPEID INNER JOIN mgcm C ON C.gcmid = b.GSTCATEGORYID INNER JOIN mbiztransactionTYPE BIZTYPE ON BIZTYPE.biztransactiontypeid = h.biztransactiontypeid INNER JOIN mbiztransactionclass CLASS ON CLASS.biztransactionclassid = BIZTYPE.biztransactionclassid LEFT JOIN MADDRESS Billtoaddress ON Billtoaddress.ADDRESSID = h.BILLTOADDRESSID LEFT JOIN MSTATE BilltoaddressState ON BilltoaddressState.STATEID = Billtoaddress.STATEID LEFT JOIN MPARTYBRANCH billtoaddresspartybranch ON Billtoaddress.OBJECTID = billtoaddresspartybranch.PARTYBRANCHID LEFT JOIN MPARTY billtoaddressparty ON billtoaddresspartybranch.PARTYID = billtoaddressparty.PARTYID INNER JOIN TMMDETAIL td ON td.DOCUMENTID = h.DOCUMENTID INNER JOIN MITEM mi ON mi.ITEMID = td.ITEMID LEFT JOIN MDUTYTARIFF dutytariff ON dutytariff.DUTYTARIFFID = mi.GSTTARIFFID LEFT JOIN MUOM uom ON uom.UOMID = td.TRANSACTIONUOMID LEFT JOIN TMMCHARGESPIVOT cp ON cp.DOCUMENTID = td.DOCUMENTID AND cp.DOCUMENTDETAILID = td.DOCUMENTDETAILID LEFT JOIN MORGANIZATIONUNIT ou ON ou.OUID = h.OUID WHERE h.STATUS = 1 AND h.DOCUMENTDATE >= @FromDate AND h.DOCUMENTDATE <= @ToDate AND (@OUId = -1 OR h.OUID = @OUId) AND (@CompanyId = -1 OR ou.COMPANYID = @CompanyId) AND CLASS.biztransactionclassid IN (-1799999904, -1399999949, -1399999948, -1799999922, -1399999855, -1399999919, -1799999903, -1399999924, -1799999911) ORDER BY h.DOCUMENTDATE, h.DOCUMENTID, td.DOCUMENTDETAILID"; // Purchase-side counterpart of GET_CANONICAL_INVOICES_BATCH — NOT a parameterized "@Direction" // variant of the same query. The role assignment inverts (OU is the buyer/receiver here, not the // seller) and the supplier's own invoice identity comes from a different pair of columns // (PARTYREFERENCENUMBER/PARTYREFERENCEDATE — our own DOCUMENTNUMBER/DOCUMENTDATE is OUR document // numbering, not the vendor's invoice number, and 2A/2B matching depends on the latter), so // conditionalizing the existing query would mean CASE-wrapping nearly every column — see // EWayBillPayloadQB.cs for what that looks like in practice (avoid repeating it here). // BILLFROMADDRESSID/BILLFROMPARTYID/BILLFROMPARTYBRANCHID (verified live on GB5DEMO's TMMHEAD) // are the vendor-side mirror of BILLTOADDRESSID used above. // Class list: -1799999923 Purchase Invoice (confirmed live via MBIZTRANSACTIONCLASS), // -1399999837 Purchase Supplementary Invoice (name suggests a corrective/additional tax invoice — // confirm with a functional owner before relying on it for a filed return; excluded from this list: // Purchase Order/Requisition/Quotation/Enquiry/Contract classes, which create no ITC claim, and // Purchase Return (-1799999922), which is correctly classified as an OUTWARD supply above. public const string GET_CANONICAL_PURCHASE_DOCUMENTS_BATCH = @" SELECT h.DOCUMENTID AS DocumentId, h.PARTYREFERENCENUMBER AS DocumentNumber, h.PARTYREFERENCEDATE AS DocumentDate, CLASS.BIZTRANSACTIONCLASSID AS BizTransactionClassId, COALESCE(C.GCMCODE, '') AS GSTCategoryCode, COALESCE(Billfromaddress.GSTIN, '') AS SupplierGSTIN, COALESCE(ownaddress.GSTIN, '') AS ReceiverGSTIN, COALESCE(ownaddress.ZIPCODE, '') AS ReceiverPincode, COALESCE(ownstate.GSTSTATECODE, '') AS ReceiverStateCode, td.DOCUMENTDETAILID AS LineNumber, td.ITEMID AS ItemId, COALESCE(dutytariff.DUTYTARIFFCODE, '') AS HSNCode, COALESCE(uom.UOMCODE, '') AS UOM, COALESCE(td.TRANSACTIONACTUALQUANTITY, 0) AS Quantity, COALESCE(cp.AVAL, 0) AS TaxableValue, COALESCE(cp.IGSTINPUT, 0) AS IGSTRate, COALESCE(cp.IGSTVALUE, 0) AS IGSTAmount, COALESCE(cp.CGSTINPUT, 0) AS CGSTRate, COALESCE(cp.CGSTVALUE, 0) AS CGSTAmount, COALESCE(cp.SGSTINPUT, 0) AS SGSTRate, COALESCE(cp.SGSTVALUE, 0) AS SGSTAmount, COALESCE(cp.CESSGSTINPUT, 0) AS CessRate, COALESCE(cp.CESSGSTVALUE, 0) AS CessAmount FROM TMMHEAD h OUTER apply(SELECT TOP(1) * FROM MADDRESS WHERE OBJECTTYPEID = -1899999977 AND Objectid = h.OUID) ownaddress LEFT JOIN MSTATE ownstate ON ownstate.STATEID = ownaddress.STATEID INNER JOIN MTRANSACTIONTYPE b ON b.TRANSACTIONTYPEID = h.DUTYTRANSACTIONTYPEID INNER JOIN mgcm C ON C.gcmid = b.GSTCATEGORYID INNER JOIN mbiztransactionTYPE BIZTYPE ON BIZTYPE.biztransactiontypeid = h.biztransactiontypeid INNER JOIN mbiztransactionclass CLASS ON CLASS.biztransactionclassid = BIZTYPE.biztransactionclassid LEFT JOIN MADDRESS Billfromaddress ON Billfromaddress.ADDRESSID = h.BILLFROMADDRESSID INNER JOIN TMMDETAIL td ON td.DOCUMENTID = h.DOCUMENTID INNER JOIN MITEM mi ON mi.ITEMID = td.ITEMID LEFT JOIN MDUTYTARIFF dutytariff ON dutytariff.DUTYTARIFFID = mi.GSTTARIFFID LEFT JOIN MUOM uom ON uom.UOMID = td.TRANSACTIONUOMID LEFT JOIN TMMCHARGESPIVOT cp ON cp.DOCUMENTID = td.DOCUMENTID AND cp.DOCUMENTDETAILID = td.DOCUMENTDETAILID LEFT JOIN MORGANIZATIONUNIT ou ON ou.OUID = h.OUID WHERE h.STATUS = 1 AND h.DOCUMENTDATE >= @FromDate AND h.DOCUMENTDATE <= @ToDate AND (@OUId = -1 OR h.OUID = @OUId) AND (@CompanyId = -1 OR ou.COMPANYID = @CompanyId) AND CLASS.biztransactionclassid IN (-1799999923, -1399999837) ORDER BY h.DOCUMENTDATE, h.DOCUMENTID, td.DOCUMENTDETAILID"; }