TAAS

GUI Changes:-
1) Currency(Check ledger stmt) - Remove Base2(**) item, Default Emty
2) Debit and Credit Checkbox set after _ Consider Matching Done After To Date_ default Tick all(3) check boxes
3) Add *Consider Not Approved Documents Checkbox - Default Untick
4) Payment Terms row also include more(Expand) div
5) Payment Terms Select box need a checkbox before, Title-Tick ON then allow to Edit, Otherwise take each customers Payment Terms
6) All customer min ledgers load and Tick on all
7) ON and Disable Debit Checkbox
8) List #
Code
Name
Currency
Current
-Net Balance
-Outstanding Dr
-Outstanding Cr
-Net Outstanding
As On Date
-Net Balance
-Outstanding Dr
-Outstanding Cr
-Net Outstanding
Payment Terms
Outstanding (Note: Consider Consider Matching Done After To Date input)
Due Days
Due Amount

Points
1) Branch and Depart select all then skip where conditions

Currency = Base1(...) Selected Case SQL:-

SELECT ac3.pk_account_id,
    ac3.vhr_account_code,
    ac3.vhr_account_name,

    cu1.dbl_credit_limit,
    cu1.sin_credit_limit_based_on,

    pt1.pk_payment_terms_id,
    pt1.sin_payment_mode,
    pt1.sin_next_day_week_month,
    pt1.sin_based_on,
    pt1.vhr_payment_term_string,

    str2.vhr_currency,

    (str2.dbl_due_debit + str2.dbl_effective_debit + str2.dbl_debit) AS dbl_debit,
    (str2.dbl_due_credit + str2.dbl_effective_credit + str2.dbl_credit) AS dbl_credit,
    (str2.dbl_due_outstanding_debit + str2.dbl_effective_outstanding_debit + str2.dbl_outstanding_debit) AS dbl_outstanding_debit,
    (str2.dbl_due_outstanding_credit + str2.dbl_effective_outstanding_credit + str2.dbl_outstanding_credit AS dbl_outstanding_credit,

        (str2.dbl_due_debit + str2.dbl_effective_debit) AS dbl_effective_debit,
    (str2.dbl_due_credit + str2.dbl_effective_credit) AS dbl_effective_credit,
    (str2.dbl_due_outstanding_debit + str2.dbl_effective_outstanding_debit) AS dbl_effective_outstanding_debit,
    (str2.dbl_due_outstanding_credit + str2.dbl_effective_outstanding_credit) AS dbl_effective_outstanding_credit,

    str2.dbl_due_debit,
    str2.dbl_due_credit,
    str2.dbl_due_outstanding_debit,
    str2.dbl_due_outstanding_credit
FROM (
    SELECT fk_ledger_id,
        vhr_base_currency AS vhr_currency,

        SUM(dbl_base_cur_debit) AS dbl_debit,
        SUM(dbl_base_cur_credit) AS dbl_credit,
        SUM(dbl_base_cur_outstanding_debit) AS dbl_outstanding_debit,
        SUM(dbl_base_cur_outstanding_credit) AS dbl_outstanding_credit,

                SUM(dbl_base_cur_effective_debit) AS dbl_effective_debit,
        SUM(dbl_base_cur_effective_credit) AS dbl_effective_credit,
        SUM(dbl_base_cur_effective_outstanding_debit) AS dbl_effective_outstanding_debit,
        SUM(dbl_base_cur_effective_outstanding_credit) AS dbl_effective_outstanding_credit,

        SUM(dbl_base_cur_due_debit) AS dbl_due_debit,
        SUM(dbl_base_cur_due_credit) AS dbl_due_credit,
        SUM(dbl_base_cur_due_outstanding_debit) AS dbl_due_outstanding_debit,
        SUM(dbl_base_cur_due_outstanding_credit) AS dbl_due_outstanding_credit
    FROM (
        SELECT tr1.fk_ledger_id,
            tr1.vhr_base_currency,

            0.00 AS dbl_base_cur_debit,
            0.00 AS dbl_base_cur_credit,
            0.00 AS dbl_base_cur_outstanding_debit,
            0.00 AS dbl_base_cur_outstanding_credit,

                        0.00 AS dbl_base_cur_effective_debit,
            0.00 AS dbl_base_cur_effective_credit,
            0.00 AS dbl_base_cur_effective_outstanding_debit,
            0.00 AS dbl_base_cur_effective_outstanding_credit,

            tr1.dbl_base_cur_debit AS dbl_base_cur_due_debit,
            tr1.dbl_base_cur_credit AS dbl_base_cur_due_credit,
            CASE WHEN tr1.dbl_base_cur_debit > 0 THEN tr1.dbl_base_cur_outstanding ELSE 0.00 END AS dbl_base_cur_due_outstanding_debit,
            CASE WHEN tr1.dbl_base_cur_credit > 0 THEN tr1.dbl_base_cur_outstanding ELSE 0.00 END AS dbl_base_cur_due_outstanding_credit
        FROM transaction.tbl_transaction tr1
            INNER JOIN accounts.tbl_account ac1
                ON tr1.fk_ledger_id = ac1.pk_account_id
        WHERE tr1.dat_transaction <= <AS ON DATE>
            AND tr1.fk_main_ledger_id IN (1,2)
             AND tr1.fk_branch_id = 1
             AND tr1.fk_department_id = 1
                         AND tr1.vhr_sys_module_name IN/NOT IN (???)
             AND tr1.sin_posting_status = 1
             AND (ac1.sin_ac_category = 18 OR ac1.sin_if_customer = 1)

                UNION ALL 

        SELECT tr2.fk_ledger_id,
            tr2.vhr_base_currency,

            0.00 AS dbl_base_cur_debit,
            0.00 AS dbl_base_cur_credit,
            0.00 AS dbl_base_cur_outstanding_debit,
            0.00 AS dbl_base_cur_outstanding_credit,

                        tr2.dbl_base_cur_debit AS dbl_base_cur_effective_debit,
            tr2.dbl_base_cur_credit AS dbl_base_cur_effective_credit,
            CASE WHEN tr1.dbl_base_cur_debit > 0 THEN tr1.dbl_base_cur_outstanding ELSE 0.00 END AS dbl_base_cur_effective_outstanding_debit,
            CASE WHEN tr1.dbl_base_cur_credit > 0 THEN tr1.dbl_base_cur_outstanding ELSE 0.00 END AS dbl_base_cur_effective_outstanding_credit,

            0.00 AS dbl_base_cur_due_debit,
            0.00 AS dbl_base_cur_due_credit,
            0.00 AS dbl_base_cur_due_outstanding_debit,
            0.00 AS dbl_base_cur_due_outstanding_credit
        FROM transaction.tbl_transaction tr2
            INNER JOIN accounts.tbl_account ac2
                ON tr2.fk_ledger_id = ac2.pk_account_id
        WHERE (tr2.dat_transaction > <AS ON DATE> AND tr2.dat_transaction <= <Effective Date>)
            AND tr2.fk_main_ledger_id IN (1,2)
            AND tr2.fk_branch_id = 1
            AND tr2.fk_department_id = 1
                        AND tr2.vhr_sys_module_name IN/NOT IN (???)
            AND tr2.sin_posting_status = 1
            AND (ac2.sin_ac_category = 18 OR ac2.sin_if_customer = 1)

        UNION ALL 

        SELECT tr3.fk_ledger_id,
            tr3.vhr_base_currency,

            tr3.dbl_base_cur_debit,
            tr3.dbl_base_cur_credit,
            CASE WHEN tr3.dbl_base_cur_debit > 0 THEN tr3.dbl_base_cur_outstanding ELSE 0.00 END AS dbl_base_cur_outstanding_debit,
            CASE WHEN tr3.dbl_base_cur_credit > 0 THEN tr3.dbl_base_cur_outstanding ELSE 0.00 END AS dbl_base_cur_outstanding_credit,

                        0.00 AS dbl_base_cur_effective_debit,
            0.00 AS dbl_base_cur_effective_credit,
            0.00 AS dbl_base_cur_effective_outstanding_debit,
            0.00 AS dbl_base_cur_effective_outstanding_credit,

            0.00 AS dbl_base_cur_due_debit,
            0.00 AS dbl_base_cur_due_credit,
            0.00 AS dbl_base_cur_due_outstanding_debit,
            0.00 AS dbl_base_cur_due_outstanding_credit
        FROM transaction.tbl_transaction tr3
            INNER JOIN accounts.tbl_account ac3
                ON tr3.fk_ledger_id = ac3.pk_account_id
        WHERE tr3.dat_transaction > <Effective Date>
            AND tr3.fk_main_ledger_id IN (1,2)
            AND tr3.fk_branch_id = 1
            AND tr3.fk_department_id = 1
                        AND tr3.vhr_sys_module_name IN/NOT IN (???)
            AND tr3.sin_posting_status = 1
            AND (ac3.sin_ac_category = 18 OR ac3.sin_if_customer = 1)) AS str1
    GROUP BY fk_ledger_id, vhr_base_currency) AS str2

    INNER JOIN accounts.tbl_account ac3
        ON str2.fk_ledger_id = ac3.pk_account_id
    INNER JOIN accounts.tbl_customer cu1
        ON ac3.pk_account_id = cu1.fk_account_id
    LEFT JOIN accounts.tbl_payment_terms AS pt1
        ON cu1.fk_payment_terms_id = pt1.pk_payment_terms_id
WHERE cu1.dbl_credit_limit > 0 
    AND (cu1.sin_credit_limit_based_on = 2 OR str2.vhr_currency = ac3.vhr_default_currency_code)
    AND (str2.dbl_due_debit > 0.00 OR str2.dbl_due_credit > 0.00)
ORDER BY ac3.vhr_account_name;

Currency = Not Base1(...) Selected Case SQL:-

SELECT ac3.pk_account_id,
    ac3.vhr_account_code,
    ac3.vhr_account_name,

    cu1.dbl_credit_limit,
    cu1.sin_credit_limit_based_on,

    pt1.pk_payment_terms_id,
    pt1.sin_payment_mode,
    pt1.sin_next_day_week_month,
    pt1.sin_based_on,
    pt1.vhr_payment_term_string,

    str2.vhr_currency,

    ...str2.dbl_debit,
    ...str2.dbl_credit,
    ...str2.dbl_outstanding_debit,
    ...str2.dbl_outstanding_credit,

        ...

    str2.dbl_due_debit,
    str2.dbl_due_credit,
    str2.dbl_due_outstanding_debit,
    str2.dbl_due_outstanding_credit
FROM (
    SELECT fk_ledger_id,
        vhr_ledg_currency AS vhr_currency,

        SUM(dbl_ledg_cur_debit) AS dbl_debit,
        SUM(dbl_ledg_cur_credit) AS dbl_credit,
        SUM(dbl_ledg_cur_outstanding_debit) AS dbl_outstanding_debit,
        SUM(dbl_ledg_cur_outstanding_credit) AS dbl_outstanding_credit,

                ...

        SUM(dbl_ledg_cur_due_debit) AS dbl_due_debit,
        SUM(dbl_ledg_cur_due_credit) AS dbl_due_credit,
        SUM(dbl_ledg_cur_due_outstanding_debit) AS dbl_due_outstanding_debit,
        SUM(dbl_ledg_cur_due_outstanding_credit) AS dbl_due_outstanding_credit
    FROM (
        SELECT tr1.fk_ledger_id,
            tr1.vhr_ledg_currency,

            0.00 AS dbl_ledg_cur_debit,
            0.00 AS dbl_ledg_cur_credit,
            0.00 AS dbl_ledg_cur_outstanding_debit,
            0.00 AS dbl_ledg_cur_outstanding_credit,

            tr1.dbl_ledg_cur_debit AS dbl_ledg_cur_due_debit,
            tr1.dbl_ledg_cur_credit AS dbl_ledg_cur_due_credit,
            CASE WHEN tr1.dbl_ledg_cur_debit > 0 THEN tr1.dbl_ledg_cur_outstanding ELSE 0.00 END AS dbl_ledg_cur_due_outstanding_debit,
            CASE WHEN tr1.dbl_ledg_cur_credit > 0 THEN tr1.dbl_ledg_cur_outstanding ELSE 0.00 END AS dbl_ledg_cur_due_outstanding_credit
        FROM transaction.tbl_transaction tr1
            INNER JOIN accounts.tbl_account ac1
                ON tr1.fk_ledger_id = ac1.pk_account_id
        WHERE tr1.dat_transaction <= <AS ON DATE>
            AND tr1.fk_main_ledger_id IN (1,2)
             AND tr1.fk_branch_id = 1
             AND tr1.fk_department_id = 1
             AND tr1.sin_posting_status = 1
                         AND tr1.vhr_sys_module_name IN/NOT IN (???)
             AND tr1.vhr_ledg_currency = '???'
             AND (ac1.sin_ac_category = 18 OR ac1.sin_if_customer = 1)

                UNION ALL 

        SELECT tr2.fk_ledger_id,
            tr2.vhr_base_currency, --***1->change to vhr_ledg_currency 

            0.00 AS dbl_ledg_cur_debit,
            0.00 AS dbl_ledg_cur_credit,
            0.00 AS dbl_ledg_cur_outstanding_debit,
            0.00 AS dbl_ledg_cur_outstanding_credit,

                        tr2.dbl_ledg_cur_debit AS dbl_ledg_cur_effective_debit,
            tr2.dbl_ledg_cur_credit AS dbl_ledg_cur_effective_credit,
            CASE WHEN tr1.dbl_ledg_cur_debit > 0 THEN tr1.dbl_ledg_cur_outstanding ELSE 0.00 END AS dbl_ledg_cur_effective_outstanding_debit,
            CASE WHEN tr1.dbl_ledg_cur_credit > 0 THEN tr1.dbl_ledg_cur_outstanding ELSE 0.00 END AS dblledg_cur_effective_outstanding_credit,

            0.00 AS dbl_ledg_cur_due_debit,
            0.00 AS dbl_ledg_cur_due_credit,
            0.00 AS dbl_ledg_cur_due_outstanding_debit,
            0.00 AS dbl_ledg_cur_due_outstanding_credit
        FROM transaction.tbl_transaction tr2
            INNER JOIN accounts.tbl_account ac2
                ON tr2.fk_ledger_id = ac2.pk_account_id
        WHERE (tr2.dat_transaction > <AS ON DATE> AND tr2.dat_transaction <= <Effective Date>)
            AND tr2.fk_main_ledger_id IN (1,2)
            AND tr2.fk_branch_id = 1
            AND tr2.fk_department_id = 1
                        AND tr2.vhr_sys_module_name IN/NOT IN (???)
            AND tr2.sin_posting_status = 1
            AND (ac2.sin_ac_category = 18 OR ac2.sin_if_customer = 1)

        UNION ALL 

        SELECT tr3.fk_ledger_id,
            tr3.vhr_ledg_currency,

            tr3.dbl_ledg_cur_debit,
            tr3.dbl_ledg_cur_credit,
            CASE WHEN tr3.dbl_ledg_cur_debit > 0 THEN tr3.dbl_ledg_cur_outstanding ELSE 0.00 END AS dbl_ledg_cur_outstanding_debit,
            CASE WHEN tr3.dbl_ledg_cur_credit > 0 THEN tr3.dbl_ledg_cur_outstanding ELSE 0.00 END AS dbl_ledg_cur_outstanding_credit,

            0.00 AS dbl_ledg_cur_due_debit,
            0.00 AS dbl_ledg_cur_due_credit,
            0.00 AS dbl_ledg_cur_due_outstanding_debit,
            0.00 AS dbl_ledg_cur_due_outstanding_credit
        FROM transaction.tbl_transaction tr3
            INNER JOIN accounts.tbl_account ac3
                ON tr3.fk_ledger_id = ac3.pk_account_id
        WHERE tr3.dat_transaction > <Effective Date>
            AND tr3.fk_main_ledger_id IN (1,2)
            AND tr3.fk_branch_id = 1
            AND tr3.fk_department_id = 1
                        AND tr3.vhr_sys_module_name IN/NOT IN (???)
            AND tr3.sin_posting_status = 1
            AND tr3.vhr_ledg_currency = '???'
            AND (ac3.sin_ac_category = 18 OR ac3.sin_if_customer = 1)) AS str1
    GROUP BY fk_ledger_id, vhr_ledg_currency) AS str2

    INNER JOIN accounts.tbl_account ac3
        ON str2.fk_ledger_id = ac3.pk_account_id
    INNER JOIN accounts.tbl_customer cu1
        ON ac3.pk_account_id = cu1.fk_account_id
    LEFT JOIN accounts.tbl_payment_terms AS pt1
        ON cu1.fk_payment_terms_id = pt1.pk_payment_terms_id
WHERE cu1.dbl_credit_limit > 0 
    AND (cu1.sin_credit_limit_based_on = 1 AND str2.vhr_currency = ac3.vhr_default_currency_code)
    AND (str2.dbl_due_debit > 0.00 OR str2.dbl_due_credit > 0.00)
ORDER BY ac3.vhr_account_name;

CS Coding Logic
1) Query Payment teams is null then take GUI Payment teams BASED ON Checkbox ON, Otherwise Apply Input payment team for all customers
1.1) Pass to API GUI Payment teams Payment Mode and Days
1.2) Due days calculate -> DueDate = GlobalFunctions.FnGetPaymentDueDate -> DueDate - Effective date(Input)
2) Find Payment teams and Effective date base Due days and Due amount Calculate
3) Effective Date > As on date - other wise block
4) Outstanding Column Value is dbl_due_outstanding_debit-dbl_due_outstanding_credit(If Credit Checkbox ON)-After As on Date matched amount(If checkbox OFF)

Bug Fixed.

Pending To Do
-----------------
Search to Do based on( Credit Limit, Credit Limit based On) According to New Changed In Customer
Module Sorting.

--***1->change to vhr_ledg_currency

ISSUE FOUNDED

1.SHOW ACT US CLIENT A/C'S DATA (BASED ON CONSIDER FILTER)
2.SHOW DATA BASED ON SELECTING PAYMENT TERMS ITEMS WITHOUT CHECK BOX

New Change

=> Bill Submission - checkbox default untick
=> Payment Terms - checkbox default untick
=> Currency Default - Ledger Currency
=> Payment Terms Default Blank
=> Bill Submission - selectbox(2nd) - default blank
=> popup LedgerSummaryPupup - list Icons click
=> Bill Submission
=> Select 'As On Date' then disable and Empty - Bill Submission(Multiselectbox, Selectbox and Checkbox) - Red *
=> Select 'Base Date' then enable Bill Submission(Multiselectbox-Tick all, Selectbox(Empty) and Checkbox((Untick))) - Remove *
=> 'Base Date*' set last month end date - default, Effactive date - Current date
=> Add Customer input(Code and Name)

=> SQL and Logic
--'Base Date' or select a Bill Submission Term No and all customer then create each 'Bill Submission Term' Bill Submission Date(<='Base Date') list [(Bill Submission Term No, Bill Submission Date), ...]
--'As On Date' then create a 'Bill Submission Term' Bill Submission Date(='As On Date') list [(NOT NULL, 'As On Date')]
--LOOP ALL [(Bill Submission Term No, Bill Submission Date), ...]    
-- UNION - IF Not empty
    SELECT ac.pk_account_id,
        MAX(ac.vhr_account_code) AS vhr_account_code,
        MAX(ac.vhr_account_name) AS vhr_account_name,

        --Bill Submission Date::Date(input string to date object) AS dat_bill_submission
        MAX(cus.fk_payment_terms_id) AS fk_payment_terms_id,
        MAX(cus.sin_bill_submission_term) AS sin_bill_submission_term,
        --MAX(cus.sin_no_of_submission_days) AS sin_no_of_submission_days,
        MAX(cus.sin_grace_period) AS sin_grace_period,
        MAX(exd.sin_extra_days) AS sin_extra_days,

        MAX(tr.vhr_ledg_currency) AS vhr_ledg_currency,
        SUM(tr.dbl_ledg_cur_debit) AS dbl_ledg_cur_debit,
        SUM(tr.dbl_ledg_cur_credit) AS dbl_ledg_cur_credit,
        SUM(tr.dbl_ledg_cur_outstanding) AS dbl_ledg_cur_outstanding,-- dbl_ledg_cur_dr_outstanding, dbl_ledg_cur_cr_outstanding

        SUM(tr.dbl_base_cur_debit) AS dbl_base_cur_debit,
        SUM(tr.dbl_base_cur_credit) AS dbl_base_cur_credit,
        SUM(tr.dbl_base_cur_outstanding ) AS dbl_base_cur_outstanding -- dbl_base_cur_dr_outstanding, dbl_base_cur_cr_outstanding

    FROM transaction.tbl_transaction tr
        INNER JOIN accounts.tbl_account ac
            ON tr.fk_ledger_id = ac.pk_account_id
        INNER JOIN accounts.tbl_customer cus
            ON ac.pk_account_id = cus.fk_account_id
        LEFT JOIN (SELECT fk_account_id,
                    SUM(sin_extra_days) AS sin_extra_days
                FROM creditlimit.tbl_extra_limit
                WHERE dat_effective_from >= '2024-03-01' AND dat_effective_to <= '2024-03-30' --'2024-03-01'->Bill Date, '2024-03-30'->Effective Date
                GROUP BY fk_account_id) exd
            ON ac.pk_account_id = exd.fk_account_id
    WHERE cus.fk_payment_terms_id IS NOT  NULL --IF INPUT THEN Not need
        AND cus.sin_bill_submission_term = 1 --IF INPUT  and ALL CUSTOMER then Not need
        --AND tr.dat_transaction <= Bill Submission Date
        --Input based WHERE add
    GROUP BY pk_account_id, vhr_ledg_currency -- vhr_ledg_currency not need Currency != Ledger Currency
    --IF Calculation Based On == Outstanding
    -- Consider considerMatchingDoneAfterEffectiveDate