namespace ComplianceDAL.Query.GSTEWayBill; // Faithful port of GB4's Unisoft.AdminDAL.Query.EWayQueryBuilder.EWayBillQueryBuilder // (GET_EWAY_JSON_FOR_HEAD / GET_EWAY_JSON_FOR_DETAIL / GET_EWAY_JSON_TEMP_SAC_CODE / GET_BIZ_CLASS_FOR_DOC, // SQL Server variant) — every BIZTRANSACTIONCLASSID branch kept as-is per explicit instruction not to // trim to only currently-used classes. Independent of CanonicalBuilderQB — E-Invoice is untouched. // Only adaptations from GB4: ":clientid"/":documentid" (GB4's dynamic CriteriaDTO/string-replace // placeholders) become real @ClientId/@DocumentId Dapper parameters. GB4's ClearTax-only branch // (LoginDTO.ClientId == -1399999874) is intentionally not ported, per instruction. public static class EWayBillPayloadQB { // GB4 stages the SAC/HSN lookup through a physical #temp table (GET_EWAY_JSON_TEMP_QUERY populates // it, GET_EWAY_JSON_TEMP_SAC_CODE reads it). Folded into one query here via a CTE scoped to // @DocumentId — same eligibility filter, same SAC lookup, no temp table/same-session plumbing needed. public const string GET_EWAY_SAC_CODE = @" ;WITH EligibleDetail AS ( SELECT dtl.DOCUMENTDETAILID FROM TMMDETAIL dtl, TMMHEAD head, MBIZTRANSACTIONTYPE BIZTYPE WHERE 1 = 1 AND dtl.DOCUMENTID = head.DOCUMENTID AND head.BIZTRANSACTIONTYPEID = BIZTYPE.BIZTRANSACTIONTYPEID AND dtl.DOCUMENTID = @DocumentId AND ( dtl.stockposttype = CASE WHEN BIZTYPE.BIZTRANSACTIONCLASSID IN (-1399999944, -1399999940, -1399999938, -1399999855, -1399999926, -1399999925, -1399999922, -1399999920, -1399999919) THEN 1 END OR dtl.stockposttype <> CASE WHEN BIZTYPE.BIZTRANSACTIONCLASSID IN (-1399999943, -1399999942, -1399999939, -1399999875, -1399999936, -1399999929, -1399999927, -1399999924, -1399999921) THEN 1 END OR dtl.stockposttype <> CASE WHEN BIZTYPE.BIZTRANSACTIONCLASSID NOT IN (-1399999943, -1399999942, -1399999939, -1399999875, -1399999936, -1399999929, -1399999927, -1399999924, -1399999921, -1399999944, -1399999940, -1399999938, -1399999855, -1399999926, -1399999925, -1399999922, -1399999920, -1399999919) THEN -1 END ) ) SELECT d.DUTYTARIFFCODE FROM EligibleDetail a, TMMDETAILPROCESS b, MPROCESS c, MDUTYTARIFF d, TMMHead e, TMMDETAIL f, MBIZTRANSACTIONTYPE biztype, TMMDETAIL old WHERE 1 = 1 AND b.PROCESSID = c.PROCESSID AND c.GSTTARIFFID = d.DUTYTARIFFID AND e.documentid = f.documentid AND e.biztransactiontypeid = biztype.biztransactiontypeid AND e.documentid = @DocumentId AND f.stockposttype = CASE WHEN biztype.BIZTRANSACTIONCLASSID = -1399999940 THEN 1 ELSE 2 END AND a.documentdetailid = old.DOCUMENTDETAILID AND e.documentid = old.documentid AND CASE WHEN biztype.BIZTRANSACTIONCLASSID IN (-1399999944, -1399999940, -1399999938, -1399999855, -1399999926, -1399999925, -1399999922, -1399999920, -1399999919) THEN f.documentdetailid ELSE a.documentdetailid END = b.DOCUMENTDETAILID"; // Used only when billLists[i].subSupplyType == 4 and GET_EWAY_SAC_CODE returned no rows — checks // whether this document is a Miscellaneous Delivery Challan (no process-linked HSN needed in that case). public const string GET_BIZ_CLASS_FOR_DOC = @" SELECT COUNT(b.BIZTRANSACTIONCLASSID) FROM TMMHEAD a, MBIZTRANSACTIONTYPE b WHERE a.BIZTRANSACTIONTYPEID = b.BIZTRANSACTIONTYPEID AND b.BIZTRANSACTIONCLASSID = -1799999911 AND a.documentid = @DocumentId"; public const string GET_EWAY_HEADER = @" SELECT case when @ClientId=-1399999740 then storeaddress.GSTIN else fromadd.gstin end AS userGstin, case when class.biztransactionclassid =-1399999943 then 'I' ELSE CASE WHEN TRANSTYPE.TYPE IN( 1, 4, 6, 7, 8 ) THEN 'O'--1 Sales,4 Purchase Return,6 Job Work,7.Jobwork return,8 subcontract ELSE 'I' END END AS supplyType, -- MTRANSACTIONTYPE TYPE -0.Transfer,1.Sales,2.Purchase,3.Sales Return,4.Purchase Return,5.Others,6.Jobwork,7.Jobwork return,8.subcontract,9.subcontract return -- EWABILL SUBSUPPLYTYPE 1 Supply,2 Import,3 Export,4 Job Work,5 For Own Use,6 Job work Returns,7 Sales Return,8 Others,9 SKD/CKD,10 Line Sales,11 Recipient Not Known,12 Exhibition or Fairs case when class.biztransactionclassid =-1399999943 then 6 ELSE CASE WHEN class.biztransactionclassid in( -1799999902,-1799999911,-1399999821) then case when class.biztransactionclassid=-1799999911 then 4 else 5 end else CASE TRANSTYPE.TYPE WHEN 0 THEN 5 WHEN 1 THEN CASE WHEN TRANSTYPE.LOCATIONTYPE = 2 THEN 3 ELSE 1 END WHEN 2 THEN CASE WHEN TRANSTYPE.LOCATIONTYPE = 2 THEN 2 ELSE 1 END WHEN 3 THEN 7 WHEN 4 THEN 1 WHEN 5 THEN 8 WHEN 6 THEN 4 WHEN 7 THEN 6 WHEN 8 THEN 4 WHEN 9 THEN 6 ELSE 1 END END END AS subSupplyType, CASE WHEN class.biztransactionclassid in( -1799999902,-1799999911,-1399999821) --Delivery Order and Miscellaneous Delivery Challan and BRCHTROUT then '' else CASE TRANSTYPE.TYPE WHEN 0 THEN TRANSTYPE.TRANSACTIONTYPENAME WHEN 1 THEN CASE WHEN TRANSTYPE.LOCATIONTYPE = 2 THEN TRANSTYPE.TRANSACTIONTYPENAME ELSE TRANSTYPE.TRANSACTIONTYPENAME END WHEN 2 THEN CASE WHEN TRANSTYPE.LOCATIONTYPE = 2 THEN TRANSTYPE.TRANSACTIONTYPENAME ELSE TRANSTYPE.TRANSACTIONTYPENAME END WHEN 3 THEN TRANSTYPE.TRANSACTIONTYPENAME WHEN 4 THEN TRANSTYPE.TRANSACTIONTYPENAME WHEN 5 THEN 'Others' WHEN 6 THEN TRANSTYPE.TRANSACTIONTYPENAME WHEN 7 THEN TRANSTYPE.TRANSACTIONTYPENAME WHEN 8 THEN TRANSTYPE.TRANSACTIONTYPENAME WHEN 9 THEN TRANSTYPE.TRANSACTIONTYPENAME ELSE TRANSTYPE.TRANSACTIONTYPENAME END END AS subSupplyDesc, CASE WHEN CLASS.BIZTRANSACTIONCLASSID IN( -1799999904, -1799999918, -1499999974 ) --SALESINVC,MINAINSP,MIN THEN 'INV' ELSE 'CHL' END AS docType, case when class.biztransactionclassid =-1399999943 then a.PARTYREFERENCENUMBER else A.DOCUMENTNUMBER end AS docNo, CONVERT(VARCHAR(10), case when class.biztransactionclassid =-1399999943 then a.PARTYREFERENCEDATE else A.DOCUMENTDATE end , 103) AS docDate, CASE WHEN A.BILLTOADDRESSID=A.SHIPTOADDRESSID THEN 1 ELSE 2 END as transType, CASE WHEN class.biztransactionclassid IN ( -179999992, --Purchase Invoice -1499999975, --PurchaseOrder -1799999918, --MIN And Inspection -1799999912, --Miscellaneous MIN -1399999943, --Subcontract MIN -1399999942, --SubContract MIN and Inspection -1499999974 )--Material Inward THEN toaddd.GSTIN ELSE CASE WHEN (class.biztransactionclassid IN (-1399999821) or @ClientId=-1399999740) then storeaddress.GSTIN else fromadd.GSTIN end END AS fromGstin, CASE WHEN class.biztransactionclassid IN ( -179999992, -1499999975, -1799999918, -1799999912, -1399999943, -1399999942, -1499999974 ) THEN toparty.PARTYNAME ELSE CASE WHEN class.biztransactionclassid IN ( -1399999821 ) then toparty.PARTYNAME else fromou.DISPLAYNAME end END AS fromTrdName, CASE WHEN class.biztransactionclassid IN ( -179999992, -1499999975, -1799999918, -1799999912, -1399999943, -1399999942, -1499999974 ) THEN toaddd.ADDRESSLINE1 ELSE CASE WHEN (class.biztransactionclassid IN (-1399999821) or @ClientId=-1399999740) then storeaddress.ADDRESSLINE1 else fromadd.ADDRESSLINE1 end END AS fromAddr1, CASE WHEN class.biztransactionclassid IN ( -179999992, -1499999975, -1799999918, -1799999912, -1399999943, -1399999942, -1499999974 ) THEN toaddd.ADDRESSLINE2 ELSE CASE WHEN (class.biztransactionclassid IN (-1399999821) or @ClientId=-1399999740) then storeaddress.ADDRESSLINE2 else fromadd.ADDRESSLINE2 end END AS fromAddr2, CASE WHEN class.biztransactionclassid IN ( -179999992, -1499999975, -1799999918, -1799999912, -1399999943, -1399999942, -1499999974 ) THEN TOCITY.CITYNAME ELSE CASE WHEN (class.biztransactionclassid IN (-1399999821) or @ClientId=-1399999740) then storecity.CITYNAME else fromCITY.CITYNAME end END AS fromPlace, CASE WHEN class.biztransactionclassid IN ( -179999992, -1499999975, -1799999918, -1799999912, -1399999943, -1399999942, -1499999974 ) THEN cast(toaddd.ZIPCODE as int) ELSE CASE WHEN (class.biztransactionclassid IN (-1399999821) or @ClientId=-1399999740) then cast(storeaddress.ZIPCODE as int) else cast( fromadd.ZIPCODE as int) end END AS fromPincode, CASE WHEN class.biztransactionclassid IN ( -179999992, -1499999975, -1799999918, -1799999912, -1399999943, -1399999942, -1499999974 ) THEN tostate.GSTSTATECODE ELSE CASE WHEN (class.biztransactionclassid IN (-1399999821) or @ClientId=-1399999740) then storestate.GSTSTATECODE else fromstate.GSTSTATECODE end END AS fromStateCode, CASE WHEN class.biztransactionclassid IN ( -179999992, -1499999975, -1799999918, -1799999912, -1399999943, -1399999942, -1499999974 ) THEN fromadd.GSTIN ELSE CASE WHEN toaddd.GSTIN is null or toaddd.GSTIN ='' then 'URP' else billtoadd.GSTIN end END AS toGstin, CASE WHEN class.biztransactionclassid IN ( -179999992, -1499999975, -1799999918, -1799999912, -1399999943, -1399999942, -1499999974 ) THEN fromou.DISPLAYNAME ELSE PB.PARTYBRANCHNAME END AS toTrdName, CASE WHEN class.biztransactionclassid IN ( -179999992, -1499999975, -1799999918, -1799999912, -1399999943, -1399999942, -1499999974 ) THEN fromadd.ADDRESSLINE1 ELSE toaddd.ADDRESSLINE1 END AS toAddr1, CASE WHEN class.biztransactionclassid IN ( -179999992, -1499999975, -1799999918, -1799999912, -1399999943, -1399999942, -1499999974 ) THEN fromadd.ADDRESSLINE2 ELSE toaddd.ADDRESSLINE2 END AS toAddr2, CASE WHEN class.biztransactionclassid IN ( -179999992, -1499999975, -1799999918, -1799999912, -1399999943, -1399999942, -1499999974 ) THEN fromCITY.CITYNAME ELSE TOCITY.CITYNAME END AS toPlace, CASE WHEN class.biztransactionclassid IN ( -179999992, -1499999975, -1799999918, -1799999912, -1399999943, -1399999942, -1499999974 ) THEN cast(fromadd.ZIPCODE as int) ELSE cast(toaddd.ZIPCODE as int) END AS toPincode, CASE WHEN class.biztransactionclassid IN ( -179999992, -1499999975, -1799999918, -1799999912, -1399999943, -1399999942, -1499999974 ) THEN fromstate.GSTSTATECODE ELSE tostate.GSTSTATECODE END AS toStateCode, case when BIZTYPE.BIZTRANSACTIONCLASSID in(-1399999943,-1399999942,-1399999939,-1399999875,-1399999936,-1399999929,-1399999927,-1399999921,-1399999944,-1399999940,-1399999938,-1399999855,-1399999925,-1399999922,-1399999920, -1399999919)--SubContract and Dc Related then isnull(detail.ITEMPOSTEDCOST,0) else case when BIZTYPE.BIZTRANSACTIONCLASSID in(-1399999924) then isnull(a.TOTALITEMBASICVALUE,0) else case when BIZTYPE.BIZTRANSACTIONCLASSID in(-1399999926) then isnull(allmmdetail.ITEMGROSSVALUE,0) else ISNULL(charges.TotalRELISATIONVALUE,0) end end end as totalValue, case BIZTYPE.BIZTRANSACTIONCLASSID when -1399999944 then detail.ITEMPOSTEDCOST -- Subcontract DC when -1399999926 then allmmdetail.ITEMGROSSVALUE -- JobWorkDeliveryChallan else ISNULL(a.TOTALNETVALUE,0) end as totInvValue, Isnull(charges.CGSTValue, 0) AS cgstValue, Isnull(charges.SGSTValue, 0) AS sgstValue, Isnull(charges.IGSTValue, 0) AS igstValue, Isnull(charges.CESSGSTValue, 0) AS cessValue, Isnull(headercharge.CESNONADVLVALUE,0) AS TotNonAdvolVal, case BIZTYPE.BIZTRANSACTIONCLASSID when -1399999944 then 0 -- Subcontract DC when -1399999926 then 0 -- JobWorkDeliveryChallan else Isnull((a.TOTALOTHERAMOUNTPLUS-a.TOTALOTHERAMOUNTMINUS),0) end AS OthValue, CASE a.MODEOFDELIVERY WHEN 0 THEN 3 WHEN 1 THEN 4 WHEN 2 THEN 1 WHEN 3 THEN 2 ELSE 1 END transMode, a.EXCISEREGISTERLASTNO AS transDistance, transporter.PARTYNAME AS transporterName, ISNULL(transporteraddress.GSTIN ,'') AS transporterId, A.LRNUMBER AS transDocNo, CONVERT(VARCHAR(10), A.DOCUMENTDATE, 103) AS transDocDate, case when vehicle.VEHICLEID=-1 then '' else vehicle.VEHICLENUMBER end AS vehicleNo FROM TMMHEAD a CROSS apply (SELECT Sum(CESNONADVLVALUE) CESNONADVLVALUE FROM TMMCHARGESPIVOT WHERE DOCUMENTID = a.DOCUMENTID) headercharge LEFT OUTER JOIN MTRANSACTIONTYPE TRANSTYPE ON ( A.DUTYTRANSACTIONTYPEID = TRANSTYPE.TRANSACTIONTYPEID ) LEFT OUTER JOIN Morganizationunit fromou ON ( A.ouid = fromou.OUID ) LEFT OUTER JOIN MBIZTRANSACTIONTYPE BIZTYPE ON ( A.BIZTRANSACTIONTYPEID = BIZTYPE.BIZTRANSACTIONTYPEID ) LEFT OUTER JOIN MBIZTRANSACTIONCLASS CLASS ON ( BIZTYPE.BIZTRANSACTIONCLASSID = CLASS.BIZTRANSACTIONCLASSID ) LEFT OUTER JOIN MADDRESS fromadd ON ( CASE WHEN A.BILLFROMADDRESSID =-1 THEN A.OUID ELSE A.BILLFROMADDRESSID END = CASE WHEN A.BILLFROMADDRESSID =-1 THEN fromadd.OBJECTID ELSE fromadd.ADDRESSID END AND CASE WHEN A.BILLFROMADDRESSID =-1 THEN fromadd.OBJECTTYPEID ELSE -1 END = CASE WHEN A.BILLFROMADDRESSID =-1 THEN -1899999977 ELSE -1 END ) LEFT OUTER JOIN MSTATE fromstate ON ( fromadd.STATEID = fromstate.STATEID ) LEFT OUTER JOIN MCITY fromCITY ON ( fromadd.CITYID = fromCITY.CITYID ) LEFT OUTER JOIN MPARTY toparty ON ( A.PARTYID = toparty.PARTYID ) LEFT OUTER JOIN MADDRESS storeaddress ON ( A.STOREID = storeaddress.OBJECTID AND storeaddress.OBJECTTYPEID = -1899997963 ) LEFT OUTER JOIN MSTATE storestate ON ( storeaddress.STATEID = storestate.STATEID ) LEFT OUTER JOIN MCITY storeCITY ON ( storeaddress.CITYID = storeCITY.CITYID ) LEFT OUTER JOIN MADDRESS toaddd ON ( a.SHIPTOADDRESSID = toaddd.ADDRESSID) LEFT OUTER JOIN MADDRESS billtoadd ON ( a.BILLTOADDRESSID = billtoadd.ADDRESSID) LEFT OUTER JOIN MPARTYBRANCH PB ON (TOADDD.OBJECTID=PB.PARTYBRANCHID) LEFT OUTER JOIN MSTATE tostate ON ( toaddd.STATEID = tostate.STATEID ) LEFT OUTER JOIN MCITY TOCITY ON ( toaddd.CITYID = TOCITY.CITYID ) OUTER apply (SELECT Sum(CASE WHEN chargedetail.CHARGENATURE = 'CGST' THEN charges.calculatedvalue ELSE 0 END)CGSTValue, Sum(CASE WHEN chargedetail.CHARGENATURE = 'SGST' THEN charges.calculatedvalue ELSE 0 END)SGSTValue, Sum(CASE WHEN chargedetail.CHARGENATURE = 'IGST' THEN charges.calculatedvalue ELSE 0 END)IGSTValue, Sum(CASE WHEN chargedetail.CHARGENATURE = 'CESSGST' THEN charges.calculatedvalue ELSE 0 END)CESSGSTValue, sum( CASE WHEN chargedetail.CHARGENATURE = 'AVAL' THEN charges.CALCULATEDVALUE ELSE 0 END ) AS TotalRELISATIONVALUE FROM Tmmcharges charges, Mchargedetail chargedetail, TMMDETAIL temp WHERE charges.chargedetailid = chargedetail.chargedetailid AND chargedetail.CHARGENATURE IN ( 'CGST', 'SGST', 'IGST', 'CESSGST','AVAL' ) AND charges.DOCUMENTID = A.DOCUMENTID and charges.DOCUMENTDETAILID<>-1 and charges.Documentdetailid=temp.Documentdetailid and temp.documentid = A.DOCUMENTID -- Inlined #temp eligibility filter (was GET_EWAY_JSON_TEMP_QUERY) — BIZTYPE here is the -- document's own biztransactiontype already joined above, same as the original head/BIZTYPE join. AND ( temp.stockposttype = CASE WHEN BIZTYPE.BIZTRANSACTIONCLASSID IN (-1399999944, -1399999940, -1399999938, -1399999855, -1399999926, -1399999925, -1399999922, -1399999920, -1399999919) THEN 1 END OR temp.stockposttype <> CASE WHEN BIZTYPE.BIZTRANSACTIONCLASSID IN (-1399999943, -1399999942, -1399999939, -1399999875, -1399999936, -1399999929, -1399999927, -1399999924, -1399999921) THEN 1 END OR temp.stockposttype <> CASE WHEN BIZTYPE.BIZTRANSACTIONCLASSID NOT IN (-1399999943, -1399999942, -1399999939, -1399999875, -1399999936, -1399999929, -1399999927, -1399999924, -1399999921, -1399999944, -1399999940, -1399999938, -1399999855, -1399999926, -1399999925, -1399999922, -1399999920, -1399999919) THEN -1 END ) )charges LEFT OUTER JOIN MPARTY transporter ON( A.TRANSPORTERID = transporter.PARTYID ) LEFT OUTER JOIN MADDRESS transporteraddress ON( transporter.DEFAULTADDRESSID = transporteraddress.ADDRESSID ) LEFT OUTER JOIN MVEHICLE vehicle ON( A.VEHICLEID = vehicle.VEHICLEID ) OUTER APPLY(select sum(allmmdetail.ITEMGROSSVALUE ) ITEMGROSSVALUE from TMMDETAIL allmmdetail where allmmdetail.DOCUMENTID=a.DOCUMENTID) allmmdetail OUTER apply (SELECT sum(dtl.ITEMBASICVALUE ) ITEMPOSTEDCOST from TMMDETAIL dtl ,TMMHEAD head,MBIZTRANSACTIONTYPE BIZTYPE where 1=1 and dtl.DOCUMENTID=head.DOCUMENTID and head.BIZTRANSACTIONTYPEID=BIZTYPE.BIZTRANSACTIONTYPEID and ( dtl.stockposttype= case when BIZTYPE.BIZTRANSACTIONCLASSID in(-1399999944,-1399999940,-1399999938,-1399999855,-1399999926,-1399999925,-1399999922,-1399999920, -1399999919 ) then 1 end or dtl.stockposttype<> case when BIZTYPE.BIZTRANSACTIONCLASSID in(-1399999943,-1399999942,-1399999939,-1399999875,-1399999936,-1399999929,-1399999927,-1399999924,-1399999921) then 1 end or dtl.stockposttype<> case when BIZTYPE.BIZTRANSACTIONCLASSID not in(-1399999943,-1399999942,-1399999939,-1399999875,-1399999936,-1399999929,-1399999927,-1399999924,-1399999921,-1399999944,-1399999940,-1399999938,-1399999855,-1399999926,-1399999925,-1399999922,-1399999920, -1399999919) then -1 end ) and dtl.STOCKPOSTTYPE<>0 and dtl.documentid=a.documentid)detail WHERE 1 = 1 AND A.documentid = @DocumentId"; public const string GET_EWAY_DETAIL = @" select b.SLNO as itemNo, item.ITEMNAME as productName, b.productdescription as productDesc, cast(dutytarrif.DUTYTARIFFCODE as INT) as hsnCode , b.GOODQUANTITY as quantity, uom.UOMCODE as qtyUnit, case when BIZTYPE.BIZTRANSACTIONCLASSID in(-1399999943,-1399999942,-1399999939,-1399999875,-1399999936,-1399999929,-1399999927,-1399999921,-1399999944,-1399999940,-1399999938,-1399999855,-1399999926,-1399999925,-1399999922,-1399999920, -1399999919)--SubContract and Dc Related then isnull(b.ITEMBASICVALUE,0) else case when BIZTYPE.BIZTRANSACTIONCLASSID in(-1399999924) then isnull(b.ITEMBASICVALUE,0) else ISNULL(charges.DETAILRELISATIONVALUE,0) end end as taxableAmount, CASE WHEN BIZTYPE.biztransactionclassid IN ( -179999992, -1499999975, -1799999918, -1799999912, -1399999943, -1399999942, -1499999974 ) THEN ISNULL(charges.PurSGSTPer,0) ELSE ISNULL(charges.SGSTPer,0) END AS sgstRate, CASE WHEN BIZTYPE.biztransactionclassid IN ( -179999992, -1499999975, -1799999918, -1799999912, -1399999943, -1399999942, -1499999974 ) THEN ISNULL(charges.PurCGSTPer,0) ELSE ISNULL(charges.CGSTPer,0) END AS cgstRate, CASE WHEN BIZTYPE.biztransactionclassid IN ( -179999992, -1499999975, -1799999918, -1799999912, -1399999943, -1399999942, -1499999974 ) THEN ISNULL(charges.PurIGSTPer,0) ELSE ISNULL(charges.IGSTPer,0) END AS igstRate, CASE WHEN BIZTYPE.biztransactionclassid IN ( -179999992, -1499999975, -1799999918, -1799999912, -1399999943, -1399999942, -1499999974 ) THEN ISNULL(charges.PurCESSGSTPer,0) ELSE ISNULL(charges.CESSGSTPer,0) END AS cessRate, cast(0.00 as Numeric(18,4)) As cessNonAdvol from TMMHEAD a LEFT OUTER JOIN MBIZTRANSACTIONTYPE BIZTYPE ON ( A.BIZTRANSACTIONTYPEID = BIZTYPE.BIZTRANSACTIONTYPEID ) left outer join TMMDETAIL b on(a.DOCUMENTID=b.DOCUMENTID) left outer join MITEM item on(b.ITEMID=item.itemid) left outer join MUOM uom on(b.TRANSACTIONUOMID=uom.UOMID) left outer join MDUTYTARIFF dutytarrif on(item.GSTTARIFFID=dutytarrif.DUTYTARIFFID) OUTER apply (SELECT sum( CASE WHEN chargedetail.CHARGENATURE = 'CGST' THEN charges.INPUTVALUE ELSE 0 END )CGSTPer, sum( CASE WHEN chargedetail.CHARGENATURE = 'SGST' THEN charges.INPUTVALUE ELSE 0 END )SGSTPer, sum( CASE WHEN chargedetail.CHARGENATURE = 'IGST' THEN charges.INPUTVALUE ELSE 0 END )IGSTPer, sum( CASE WHEN chargedetail.CHARGENATURE = 'CESSGST' THEN 0.0 ELSE 0.0 END )CESSGSTPer, sum( CASE WHEN chargedetail.CHARGENATURE = 'AVAL' THEN charges.CALCULATEDVALUE ELSE 0 END ) AS DETAILRELISATIONVALUE, sum( CASE WHEN chargedetail.CHARGENATURE = 'CGSTIN' THEN charges.INPUTVALUE ELSE 0 END )PurCGSTPer, sum( CASE WHEN chargedetail.CHARGENATURE = 'SGSTIN' THEN charges.INPUTVALUE ELSE 0 END )PurSGSTPer, sum( CASE WHEN chargedetail.CHARGENATURE = 'IGSTIN' THEN charges.INPUTVALUE ELSE 0 END )PurIGSTPer, sum( CASE WHEN chargedetail.CHARGENATURE = 'CESSGSTIN' THEN 0.0 ELSE 0.0 END )PurCESSGSTPer FROM Tmmcharges charges, Mchargedetail chargedetail WHERE charges.chargedetailid = chargedetail.chargedetailid AND chargedetail.CHARGENATURE IN ( 'CGST', 'SGST', 'IGST', 'CESSGST','AVAL','SGSTIN', 'CGSTIN','IGSTIN','CESSGSTIN' ) AND charges.DOCUMENTDETAILID = b.DOCUMENTDETAILID and charges.DOCUMENTID=b.DOCUMENTID)charges WHERE 1 = 1 and ( b.stockposttype= case when BIZTYPE.BIZTRANSACTIONCLASSID in(-1399999944,-1399999940,-1399999938,-1399999855,-1399999926,-1399999925,-1399999922,-1399999920, -1399999919 ) then 1 end or b.stockposttype<> case when BIZTYPE.BIZTRANSACTIONCLASSID in(-1399999943,-1399999942,-1399999939,-1399999875,-1399999936,-1399999929,-1399999927,-1399999924,-1399999921) then 1 end or b.stockposttype<> case when BIZTYPE.BIZTRANSACTIONCLASSID not in(-1399999943,-1399999942,-1399999939,-1399999875,-1399999936,-1399999929,-1399999927,-1399999924,-1399999921,-1399999944,-1399999940,-1399999938,-1399999855,-1399999926,-1399999925,-1399999922,-1399999920, -1399999919) then -1 end ) AND a.documentid = @DocumentId"; }