TAAS

Solutions

1) GUI Change
-Add Status - like ledger Statement
2)SQL
SELECT tr.fk_productivity_mapping_id,
      MAX(ac.vhr_account_code) AS vhr_account_code,
      MAX(ac.vhr_account_name) AS vhr_account_name,
      MAX(ac.vhr_display_name) AS vhr_display_name,
      SUM(CASE WHEN tr.dbl_ledg_cur_credit > 0 THEN tr.dbl_ledg_cur_credit ELSE 0.00 END) AS dbl_sale,--Currency replace based on Input currency
      SUM(CASE WHEN tr.dbl_ledg_cur_debit > 0 THEN tr.dbl_ledg_cur_debit ELSE 0.00 END) AS dbl_refund,--Currency replace based on Input currency
      SUM(tmp.dbl_profit) AS dbl_profit,
      SUM(tmp.dbl_expense) AS dbl_expense
FROM transaction.tbl_transaction AS tr
INNER JOIN accounts.tbl_account AS ac
    ON tr.fk_productivity_mapping_id = ac.pk_account_id
INNER JOIN (SELECT tr.fk_productivity_mapping_id,
        CASE WHEN tr.dbl_ledg_cur_credit > 0 THEN tr.dbl_ledg_cur_credit ELSE 0.00 END AS dbl_profit, --Currency replace based on Input currency
          CASE WHEN tr.dbl_ledg_cur_debit > 0 THEN tr.dbl_ledg_cur_debit ELSE 0.00 END AS dbl_expense--Currency replace based on Input currency
    FROM transaction.tbl_transaction AS tr
        INNER JOIN accounts.tbl_account AS ac
            ON fk_productivity_mapping_id = ac.pk_account_id
    WHERE tr.sin_posting_status = 1 
        AND tr.sin_transaction_status = 1 
        AND tr.sin_document_status = 1
        AND tr.dat_transaction BETWEEN '2023-08-01' AND '2023-08-30'
        AND ac.sin_ac_category IN (3,4) -- Income and Expense account only
        --...
    ) AS tmp
    ON tr.fk_productivity_mapping_id = tmp.fk_productivity_mapping_id
WHERE tr.sin_posting_status = 1 
    AND tr.sin_transaction_status = 1 
    AND tr.sin_document_status = 1
    AND tr.sin_record_type IN (3, 4)
    AND tr.vhr_sys_module_name IN ('SALE', 'REFUND')
    AND tr.dat_transaction BETWEEN '2023-08-01' AND '2023-08-30'
    AND (ac.sin_ac_category = 18 --Cutomer
        OR (ac.sin_ac_category IN (1,2,3,4) AND sin_if_customer = 1) -- COA Act as Customer
        OR (ac.sin_ac_category = 19 AND sin_if_customer = 1) --Supplier act as custmer
        OR (ac.sin_ac_category = 20 AND sin_if_customer = 1) --Employee act as custmer
        OR (ac.sin_ac_category = 21 AND sin_if_customer = 1)) --Commission Agent act as custmer
    --...
GROUP BY tr.fk_productivity_mapping_id
ORDER BY (SUM(tmp.dbl_profit) - SUM(tmp.dbl_expense)) DESC

-- Net = dbl_sale-dbl_refund
--Net Profit = dbl_profit - dbl_expense

TESTING DONE

ISSUE FIXED

1.SHOWING LEDGER ACCOUNTS (INCOME/EXPENSE) DATA FROM RECEIPT THAT NOT RELATED WITH THE PRODUCTIVITY OF THE CUSTOMER

TESTING DONE

TESTING DONE