CHS Std Tariff/Service Group Import

Standard Tariff Import

only when std tariff cnt is 0 then only import is allowed
Same like create it will behave
The hardcoded columns which we are inserting those will no be there in download model
Tax Type must be N for unregister gstin entity a readingxl errorcode
Others common validation needs to be handle that is custom & data annotations
code + name in Sysgenxl
1000 only allowed

Service Group Import

only when service group cnt is 0 then only import is allowed
main & line both together they have to import like vendormst
computetypecode needs to be Z for unregister gstin entity a readingxl errorcode
code + name in Sysgenxl
other columns, validation, data annotations needs to be handle
for computetypecode only one service user can add else multiple service
If compute type is I then only those tariffs are allowed which tax ind is Y (Intra state) else all tariffs are allowed, refer tariff combo query for active duplicate tariffs not allowed(in that single group), only taxgroup NA
custom validations & data annotations needs to be handle
Now for final Insert Case 1: Compute Type I (Inclusive)
- different query for creating gst after getting basic value from tariff Case 2: Compute Type E (Exclusive)
- If taxind Y calculate cgst & sgst
- If taxind I calculae IGST
- In single group there can can be either tax Y, N or I, N there can not be Y & I together already custom validation we have

Inclusive Query

WITH A (tariffrate, tariffcode, taxgroup) AS
(
    -- Base rate for each tariffcode
    SELECT 100, t.tariffcode, 'NA'
    FROM #TempTariff t

    UNION ALL

    SELECT s.amount, s.tariffcode, s.taxgroup
    FROM stdtariffmst s
    JOIN #TempTariff t ON s.tariffcode = t.tariffcode
    WHERE s.taxgroup = 'G1' AND s.isactive = 'Y'

    UNION ALL

    SELECT s.amount, s.tariffcode, s.taxgroup
    FROM stdtariffmst s
    JOIN #TempTariff t ON s.tariffcode = t.tariffcode
    WHERE s.taxgroup = 'G2' AND s.isactive = 'Y'
),
B (computedamount, tariffcode, taxgroup) AS
(
    SELECT CAST(ROUND(@amount * tariffrate / 118, 2) AS NUMERIC(12,2)),
           tariffcode,
           taxgroup
    FROM A
),
C (tariffcode, roundoffamount) AS
(
    SELECT tariffcode,
           @amount - SUM(computedamount)
    FROM B
    GROUP BY tariffcode
),
D (amount, tariffcode) AS
(
    -- row for NA
    SELECT b.computedamount + c.roundoffamount, b.tariffcode
    FROM B b
    JOIN C c ON b.tariffcode = c.tariffcode
    WHERE b.taxgroup = 'NA'

    UNION ALL

    -- rows for G1 and G2
    SELECT computedamount, tariffcode
    FROM B
    WHERE taxgroup != 'NA'
)

INSERT INTO ServiceGroupMst1 (amount, groupcode, tariffcode)
SELECT amount, @groupcode, tariffcode
FROM D;

Exclusive Query

For Tax ind Y below given query need to comine as CTE & perform calculation

 var totamount = @"SELECT COALESCE(SUM(SG1.amount), 0) AS totalamount FROM ServiceGroupMst1 SG1
        LEFT JOIN StdTariffMst ST ON SG1.tariffcode = ST.tariffcode
        WHERE groupcode = @groupcode AND taxind = 'Y'";

        var exclusivequery = @"WITH A (tariffrate, tariffcode) AS
        (
            SELECT amount, tariffcode FROM stdtariffmst WHERE taxgroup = 'G1' AND isactive = 'Y'
            UNION ALL
            SELECT amount, tariffcode FROM stdtariffmst WHERE taxgroup = 'G2' AND isactive = 'Y'
        ),
        B (computedamount, tariffcode) AS
        (
            SELECT CAST(ROUND(@amount*tariffrate/100,2) AS NUMERIC(12,2)), tariffcode FROM A
        )
        INSERT INTO ServiceGroupMst1 (amount,groupcode,tariffcode)
        SELECT computedamount,@groupcode,tariffcode FROM B";

For Tax ind I below given query need to comine as CTE & perform calculation

var totamount = @"SELECT COALESCE(SUM(SG1.amount), 0) AS totalamount FROM ServiceGroupMst1 SG1
        LEFT JOIN StdTariffMst ST ON SG1.tariffcode = ST.tariffcode
        WHERE groupcode = @groupcode AND taxind = 'I'";

        var exclusivequery = @"WITH A (tariffrate, tariffcode) AS
        (
            SELECT amount, tariffcode FROM stdtariffmst WHERE taxgroup = 'G3' AND isactive = 'Y'
        ),
        B (computedamount, tariffcode) AS
        (
            SELECT CAST(ROUND(@amount*tariffrate/100,2) AS NUMERIC(12,2)), tariffcode FROM A
        )
        INSERT INTO ServiceGroupMst1 (amount,groupcode,tariffcode)
        SELECT computedamount,@groupcode,tariffcode FROM B";

Now last step update servicegroup main level amount also by doing sum of line level

AI Insights [Pending Indent, IIRS Consumption, BOM, Cash Bank Balances]CHS Whats App API Setup-Old