TAAS

OLD - NOT USE

SELECT    fk_productivity_mapping_id,
                                                 pk_account_id,
                                                 MAX(vhr_account_code) AS account_code,
                                                 MAX(vhr_account_name) AS account_name,
                                                 MAX(vhr_display_name) AS display_name,
                                                 SUM(tmp.dbl_sale) AS dbl_sale,
                                                 SUM(tmp.dbl_refund) AS dbl_refund,
                                                 SUM(tmp.dbl_profit) AS dbl_profit,
                                                 SUM(tmp.dbl_expense) AS dbl_expense

                                                 FROM (SELECT tr.fk_productivity_mapping_id,
                                                              ac.vhr_account_code,
                                                              ac.vhr_account_name,
                                                              ac.vhr_display_name,
                                                              pk_account_id,    
                                                              CASE WHEN tr.dbl_ledg_cur_credit > 0 THEN tr.dbl_ledg_cur_credit ELSE 0.00 END AS dbl_sale,
                                                              CASE WHEN tr.dbl_ledg_cur_debit > 0 THEN tr.dbl_ledg_cur_debit ELSE 0.00 END AS dbl_refund,
                                                              0.00 AS dbl_profit,
                                                              0.00 AS dbl_expense
                                                        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.sin_record_type = 5 
                                                        -- ?? WHERE

                                                        UNION ALL

                                                        SELECT tr.fk_productivity_mapping_id,
                                                               ac2.vhr_account_code,
                                                               ac2.vhr_account_name,
                                                               ac2.vhr_display_name,
                                                               ac2.pk_account_id,    
                                                               0.00 AS dbl_sale,
                                                               0.00 AS dbl_refund,
                                                               CASE WHEN tr.dbl_ledg_cur_credit > 0 THEN tr.dbl_ledg_cur_credit ELSE 0.00 END AS dbl_profit,
                                                               CASE WHEN tr.dbl_ledg_cur_debit > 0 THEN tr.dbl_ledg_cur_debit ELSE 0.00 END AS dbl_expense
                                                        FROM transaction.tbl_transaction AS tr
                                                        INNER JOIN accounts.tbl_account AS ac1
                                                            ON tr.fk_ledger_id = ac1.pk_account_id 
                                                        INNER JOIN accounts.tbl_account ac2
                                                            ON tr.fk_productivity_mapping_id = ac2.pk_account_id
                                                        WHERE tr.sin_posting_status = 1 AND tr.sin_transaction_status = 1 AND tr.sin_document_status = 1
                                                        AND ac1.sin_ac_category IN (3, 4)
                                                        -- ??? WHERE
                                                      ) AS tmp

                                                GROUP BY fk_productivity_mapping_id, pk_account_id

New 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_refund,--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_sale,--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 (5, 6)
    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 = 19 --Supplier
        OR (ac.sin_ac_category IN (1,2,3,4) AND sin_if_supplier = 1) -- COA Act as Supplier
        OR (ac.sin_ac_category = 18 AND sin_if_supplier = 1) --Customer act as Supplier
        OR (ac.sin_ac_category = 22 AND sin_if_supplier = 1)) --Airline 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

New Query

SELECT mt.fk_productivity_mapping_id,
    MIN(ac.vhr_account_code) AS vhr_account_code,
    MIN(ac.vhr_account_name) AS vhr_account_name,
    MIN(ac.vhr_display_name) AS vhr_display_name,
    SUM(mt.dbl_sale) AS dbl_sale,
    SUM(mt.dbl_refund) AS dbl_refund,
    SUM(mt.dbl_profit) AS dbl_profit,
    SUM(mt.dbl_expense) AS dbl_expense
FROM ( SELECT tr.fk_productivity_mapping_id,
        CASE WHEN tr.dbl_base_cur_credit > 0 THEN tr.dbl_base_cur_credit ELSE 0.00 END AS dbl_sale,
        CASE WHEN tr.dbl_base_cur_debit > 0 THEN tr.dbl_base_cur_debit ELSE 0.00 END AS dbl_refund,
        0.00 AS dbl_profit,
        0.00 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
    WHERE tr.sin_posting_status = 1 
        AND tr.sin_transaction_status = 1 
        AND tr.sin_document_status = 1
        AND tr.sin_record_type IN (5, 6)
        AND tr.vhr_sys_module_name IN ('SALE', 'REFUND')
        AND tr.dat_transaction BETWEEN CAST('06-09-2023 06:30:00' AS DATE) AND CAST('06-09-2023 06:30:00' AS DATE)
        AND tr.fk_branch_id = ANY(STRING_TO_ARRAY('1', ',')::BIGINT[])  
        AND tr.sin_posting_status = ANY(STRING_TO_ARRAY('1,3', ',')::BIGINT[])  
        AND  (  ac.sin_ac_category = 19 )  AND  (  ac.sin_ac_category = 19 OR ac.sin_ac_category IN (1,2,3,4) AND sin_if_supplier = 1 )  
        AND  (  ac.sin_ac_category = 19 OR ac.sin_ac_category IN (1,2,3,4) AND sin_if_supplier = 1 OR ac.sin_ac_category = 18 AND sin_if_supplier = 1 )  
        AND  (  ac.sin_ac_category = 19 OR ac.sin_ac_category IN (1,2,3,4) AND sin_if_supplier = 1 OR ac.sin_ac_category = 18 AND sin_if_supplier = 1 OR ac.sin_ac_category = 22 AND sin_if_supplier = 1 ) 

    UNION ALL

    SELECT tr.fk_productivity_mapping_id,
        0.00 AS dbl_sale,
        0.00 AS dbl_refund,
        CASE WHEN tr.dbl_base_cur_credit > 0 THEN tr.dbl_base_cur_credit ELSE 0.00 END AS dbl_profit,
        CASE WHEN tr.dbl_base_cur_debit > 0 THEN tr.dbl_base_cur_debit ELSE 0.00 END 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
    WHERE tr.sin_posting_status = 1
        AND tr.sin_transaction_status = 1
        AND tr.sin_document_status = 1
        AND tr.dat_transaction BETWEEN CAST('06-09-2023 06:30:00' AS DATE) AND CAST('06-09-2023 06:30:00' AS DATE)
        AND ac.sin_ac_category IN(3,4)
        AND tr.vhr_sys_module_name NOT IN ('SALE', 'REFUND')
        AND   tr.fk_branch_id = ANY(STRING_TO_ARRAY('1', ',')::BIGINT[])  
        AND tr.sin_posting_status = ANY(STRING_TO_ARRAY('1,3', ',')::BIGINT[])  
        AND  (  ac.sin_ac_category = 19 )  AND  (  ac.sin_ac_category = 19 OR ac.sin_ac_category IN (1,2,3,4) AND sin_if_supplier = 1 )  
        AND  (  ac.sin_ac_category = 19 OR ac.sin_ac_category IN (1,2,3,4) AND sin_if_supplier = 1 OR ac.sin_ac_category = 18 AND sin_if_supplier = 1 )  
        AND  (  ac.sin_ac_category = 19 OR ac.sin_ac_category IN (1,2,3,4) AND sin_if_supplier = 1 OR ac.sin_ac_category = 18 AND sin_if_supplier = 1 OR ac.sin_ac_category = 22 AND sin_if_supplier = 1 ) 
) AS mt
INNER JOIN accounts.tbl_account AS ac
    ON mt.fk_productivity_mapping_id = ac.pk_account_id
GROUP BY mt.fk_productivity_mapping_id;

TESTING DONE

Issue Found
------------------------
Productivity Amount is wrong when sharing

TESTING DONE