namespace MMDAL.Query.Register { public static class OUWiseGSTQB { // Reuses TMMCHARGESPIVOT's already-proven join key (documentid, documentdetailid) and GST // column set from MMDocumentQB.GET_MM_DOCUMENT_DETAIL — *INPUT columns are the rate%, // *VALUE columns are the computed amount (confirmed via MMDocumentPrintDTO's existing // CgstRate/CgstAmount mapping). Sales-vs-Sales-Return netting resolved via // MBIZTRANSACTIONCLASS.BIZTRANSACTIONCLASSNAME (not hardcoded magic IDs like legacy). #region Document-line grain detail public const string GET_OUWISE_GST_DETAIL = @" SELECT h.DOCUMENTID AS DocumentId, h.DOCUMENTNUMBER AS DocumentNumber, h.DOCUMENTDATE AS DocumentDate, ou.ORGANIZATIONUNITNAME AS OUName, h.OUID AS OUId, bt.BIZTRANSACTIONCLASSID AS BiztransactionClassId, p.PARTYCODE AS PartyCode, p.PARTYNAME AS PartyName, ISNULL(cp.AVAL, d.ITEMNETVALUE) AS TaxableValue, ISNULL(cp.CGSTINPUT,0) AS CgstPercent, ISNULL(cp.CGSTVALUE,0) AS CgstValue, ISNULL(cp.SGSTINPUT,0) AS SgstPercent, ISNULL(cp.SGSTVALUE,0) AS SgstValue, ISNULL(cp.IGSTINPUT,0) AS IgstPercent, ISNULL(cp.IGSTVALUE,0) AS IgstValue, ISNULL(cp.CESSGSTINPUT,0) AS CessgstPercent, ISNULL(cp.CESSGSTVALUE,0) AS CessgstAmount, ISNULL(cp.CGSTININPUT,0) AS CgstinPercent, ISNULL(cp.CGSTINVALUE,0) AS CgstinAmount, ISNULL(cp.SGSTININPUT,0) AS SgstinPercent, ISNULL(cp.SGSTINVALUE,0) AS SgstinAmount, ISNULL(cp.IGSTININPUT,0) AS IgstinPercent, ISNULL(cp.IGSTINVALUE,0) AS IgstinAmount, ISNULL(cp.CESSGSTININPUT,0) AS CessgstinPercent, ISNULL(cp.CESSGSTINVALUE,0) AS CessgstinAmount, d.BILLVALUEDETAIL AS BillValueDetail, CASE WHEN bc.BIZTRANSACTIONCLASSNAME LIKE '%Return%' THEN -h.TOTALNETVALUE ELSE h.TOTALNETVALUE END AS SalesNetValue, CASE WHEN bc.BIZTRANSACTIONCLASSNAME LIKE '%Return%' THEN -ISNULL(cp.CGSTVALUE,0) ELSE ISNULL(cp.CGSTVALUE,0) END AS SalesCgstValue, CASE WHEN bc.BIZTRANSACTIONCLASSNAME LIKE '%Return%' THEN -ISNULL(cp.SGSTVALUE,0) ELSE ISNULL(cp.SGSTVALUE,0) END AS SalesSgstValue, CASE WHEN bc.BIZTRANSACTIONCLASSNAME LIKE '%Return%' THEN -ISNULL(cp.IGSTVALUE,0) ELSE ISNULL(cp.IGSTVALUE,0) END AS SalesIgstValue, CASE WHEN bc.BIZTRANSACTIONCLASSNAME LIKE '%Return%' THEN 0 ELSE ISNULL(cp.AVAL, d.ITEMNETVALUE) END AS SalesTaxableValue, CASE WHEN bc.BIZTRANSACTIONCLASSNAME LIKE '%Return%' THEN ISNULL(cp.AVAL, d.ITEMNETVALUE) ELSE 0 END AS SalesReturnTaxableValue FROM TMMHEAD h JOIN TMMDETAIL d ON d.DOCUMENTID = h.DOCUMENTID JOIN MBIZTRANSACTIONTYPE bt ON bt.BIZTRANSACTIONTYPEID = h.BIZTRANSACTIONTYPEID JOIN MBIZTRANSACTIONCLASS bc ON bc.BIZTRANSACTIONCLASSID = bt.BIZTRANSACTIONCLASSID JOIN MORGANIZATIONUNIT ou ON ou.OUID = h.OUID JOIN MPARTY p ON p.PARTYID = h.PARTYID LEFT JOIN TMMCHARGESPIVOT cp ON cp.DOCUMENTID = d.DOCUMENTID AND cp.DOCUMENTDETAILID = d.DOCUMENTDETAILID WHERE bc.BIZTRANSACTIONCLASSNAME LIKE 'Sales%' AND (@OuId IS NULL OR h.OUID = @OuId) AND h.DOCUMENTDATE >= @PeriodFromDate AND h.DOCUMENTDATE <= @PeriodToDate"; #endregion #region OU + BizTransactionClass grain summary public const string GET_OUWISE_GST_SUMMARY = @" SELECT h.OUID AS OUId, ou.ORGANIZATIONUNITNAME AS OUName, bt.BIZTRANSACTIONCLASSID AS BiztransactionClassId, 0 AS DocumentId, '' AS DocumentNumber, CAST(GETDATE() AS DATETIME) AS DocumentDate, SUM(ISNULL(cp.AVAL, d.ITEMNETVALUE)) AS TaxableValue, 0 AS CgstPercent, SUM(ISNULL(cp.CGSTVALUE,0)) AS CgstValue, 0 AS SgstPercent, SUM(ISNULL(cp.SGSTVALUE,0)) AS SgstValue, 0 AS IgstPercent, SUM(ISNULL(cp.IGSTVALUE,0)) AS IgstValue, 0 AS CessgstPercent, SUM(ISNULL(cp.CESSGSTVALUE,0)) AS CessgstAmount, 0 AS CgstinPercent, SUM(ISNULL(cp.CGSTINVALUE,0)) AS CgstinAmount, 0 AS SgstinPercent, SUM(ISNULL(cp.SGSTINVALUE,0)) AS SgstinAmount, 0 AS IgstinPercent, SUM(ISNULL(cp.IGSTINVALUE,0)) AS IgstinAmount, 0 AS CessgstinPercent, SUM(ISNULL(cp.CESSGSTINVALUE,0)) AS CessgstinAmount, SUM(d.BILLVALUEDETAIL) AS BillValueDetail, SUM(CASE WHEN bc.BIZTRANSACTIONCLASSNAME LIKE '%Return%' THEN -h.TOTALNETVALUE ELSE h.TOTALNETVALUE END) AS SalesNetValue, SUM(CASE WHEN bc.BIZTRANSACTIONCLASSNAME LIKE '%Return%' THEN -ISNULL(cp.CGSTVALUE,0) ELSE ISNULL(cp.CGSTVALUE,0) END) AS SalesCgstValue, SUM(CASE WHEN bc.BIZTRANSACTIONCLASSNAME LIKE '%Return%' THEN -ISNULL(cp.SGSTVALUE,0) ELSE ISNULL(cp.SGSTVALUE,0) END) AS SalesSgstValue, SUM(CASE WHEN bc.BIZTRANSACTIONCLASSNAME LIKE '%Return%' THEN -ISNULL(cp.IGSTVALUE,0) ELSE ISNULL(cp.IGSTVALUE,0) END) AS SalesIgstValue, SUM(CASE WHEN bc.BIZTRANSACTIONCLASSNAME LIKE '%Return%' THEN 0 ELSE ISNULL(cp.AVAL, d.ITEMNETVALUE) END) AS SalesTaxableValue, SUM(CASE WHEN bc.BIZTRANSACTIONCLASSNAME LIKE '%Return%' THEN ISNULL(cp.AVAL, d.ITEMNETVALUE) ELSE 0 END) AS SalesReturnTaxableValue FROM TMMHEAD h JOIN TMMDETAIL d ON d.DOCUMENTID = h.DOCUMENTID JOIN MBIZTRANSACTIONTYPE bt ON bt.BIZTRANSACTIONTYPEID = h.BIZTRANSACTIONTYPEID JOIN MBIZTRANSACTIONCLASS bc ON bc.BIZTRANSACTIONCLASSID = bt.BIZTRANSACTIONCLASSID JOIN MORGANIZATIONUNIT ou ON ou.OUID = h.OUID LEFT JOIN TMMCHARGESPIVOT cp ON cp.DOCUMENTID = d.DOCUMENTID AND cp.DOCUMENTDETAILID = d.DOCUMENTDETAILID WHERE bc.BIZTRANSACTIONCLASSNAME LIKE 'Sales%' AND (@OuId IS NULL OR h.OUID = @OuId) AND h.DOCUMENTDATE >= @PeriodFromDate AND h.DOCUMENTDATE <= @PeriodToDate GROUP BY h.OUID, ou.ORGANIZATIONUNITNAME, bt.BIZTRANSACTIONCLASSID"; #endregion #region Online-sales (IsIORatio-flagged lines) document-line grain public const string GET_OUWISE_GST_ONLINE_SALES = @" SELECT h.DOCUMENTID AS DocumentId, h.DOCUMENTNUMBER AS DocumentNumber, h.DOCUMENTDATE AS DocumentDate, ou.ORGANIZATIONUNITNAME AS OUName, bt.ISIORATIO AS IsIORatio, ISNULL(cp.AVAL, d.ITEMNETVALUE) AS TaxableValue, ISNULL(cp.CGSTINPUT,0) AS CgstPercent, ISNULL(cp.CGSTVALUE,0) AS CgstValue, ISNULL(cp.SGSTINPUT,0) AS SgstPercent, ISNULL(cp.SGSTVALUE,0) AS SgstValue, d.BILLVALUEDETAIL AS BillValueDetail, CASE WHEN bc.BIZTRANSACTIONCLASSNAME LIKE '%Return%' THEN -h.TOTALNETVALUE ELSE h.TOTALNETVALUE END AS SalesNetValue, CASE WHEN bc.BIZTRANSACTIONCLASSNAME LIKE '%Return%' THEN -ISNULL(cp.CGSTVALUE,0) ELSE ISNULL(cp.CGSTVALUE,0) END AS SalesCgstValue, CASE WHEN bc.BIZTRANSACTIONCLASSNAME LIKE '%Return%' THEN -ISNULL(cp.SGSTVALUE,0) ELSE ISNULL(cp.SGSTVALUE,0) END AS SalesSgstValue, CASE WHEN bc.BIZTRANSACTIONCLASSNAME LIKE '%Return%' THEN -ISNULL(cp.IGSTVALUE,0) ELSE ISNULL(cp.IGSTVALUE,0) END AS SalesIgstValue, CASE WHEN bc.BIZTRANSACTIONCLASSNAME LIKE '%Return%' THEN 0 ELSE ISNULL(cp.AVAL, d.ITEMNETVALUE) END AS SalesTaxableValue, CASE WHEN bc.BIZTRANSACTIONCLASSNAME LIKE '%Return%' THEN ISNULL(cp.AVAL, d.ITEMNETVALUE) ELSE 0 END AS SalesReturnTaxableValue FROM TMMHEAD h JOIN TMMDETAIL d ON d.DOCUMENTID = h.DOCUMENTID JOIN MBIZTRANSACTIONTYPE bt ON bt.BIZTRANSACTIONTYPEID = h.BIZTRANSACTIONTYPEID JOIN MBIZTRANSACTIONCLASS bc ON bc.BIZTRANSACTIONCLASSID = bt.BIZTRANSACTIONCLASSID JOIN MORGANIZATIONUNIT ou ON ou.OUID = h.OUID LEFT JOIN TMMCHARGESPIVOT cp ON cp.DOCUMENTID = d.DOCUMENTID AND cp.DOCUMENTDETAILID = d.DOCUMENTDETAILID WHERE bc.BIZTRANSACTIONCLASSNAME LIKE 'Sales%' AND bt.ISIORATIO = 1 AND (@OuId IS NULL OR h.OUID = @OuId) AND h.DOCUMENTDATE >= @PeriodFromDate AND h.DOCUMENTDATE <= @PeriodToDate"; #endregion } }