TAAS

Query

                                         SELECT fk_main_ledger_id,
                                         MIN(pk_account_id) AS pk_account_id,
                                         MIN(pk_transaction_id) AS pk_transaction_id,
                                         vhr_account_code,vhr_account_name,
                                         vhr_code as vhr_ml_code,
                                         vhr_name as vhr_ml_name,
                                         vhr_display_name,

                                         SUM(dbl_amount_dr) AS dbl_amount_dr,
                                         SUM(dbl_amount_cr) AS dbl_amount_cr,

                                         SUM(dbl_amount_ob_dr) AS dbl_amount_ob_dr,
                                         SUM(dbl_amount_ob_cr) AS dbl_amount_ob_cr

                                    FROM

                                        (SELECT ac.pk_account_id,
                                            ac.vhr_account_code,
                                            ac.vhr_account_name,
                                            tr.pk_transaction_id,
                                            tr.vhr_ledg_currency,
                                            tr.vhr_base_currency,
                                            tr.vhr_cons_currency,
                                            tr.fk_main_ledger_id,
                                            coa.vhr_code,
                                            coa.vhr_name,
                                            ac.vhr_display_name,

                                            0.00 AS dbl_amount_dr,
                                            0.00 AS dbl_amount_cr,

                                            tr.dbl_ledg_cur_debit AS dbl_amount_ob_dr,
                                            tr.dbl_ledg_cur_credit AS dbl_amount_ob_cr

                                        FROM transaction.tbl_transaction AS tr 
                                            LEFT JOIN accounts.tbl_account AS ac ON tr.fk_ledger_id = ac.pk_account_id
                                            LEFT JOIN accounts.tbl_chart_of_ac AS coa ON coa.pk_chart_of_ac_id = tr.fk_main_ledger_id
                                            LEFT JOIN document.tbl_documents AS doc ON doc.pk_documents_id = tr.fk_documents_id
                                            --WHERE CONDITION OB DATA (Only Date Filter is different)

                                      UNION ALL 

                                        SELECT ac.pk_account_id,
                                            ac.vhr_account_code,
                                            ac.vhr_account_name,
                                            tr.pk_transaction_id,
                                            tr.vhr_ledg_currency,
                                            tr.vhr_base_currency,
                                            tr.vhr_cons_currency,
                                            tr.fk_main_ledger_id,
                                            coa.vhr_code,
                                            coa.vhr_name,
                                            ac.vhr_display_name,

                                            dbl_ledg_cur_debit AS dbl_amount_dr,
                                            dbl_ledg_cur_credit AS dbl_amount_cr,

                                            0.00 AS dbl_amount_ob_dr,
                                            0.00 AS dbl_amount_ob_cr

                                        FROM transaction.tbl_transaction as tr 
                                            LEFT JOIN accounts.tbl_account as ac ON tr.fk_ledger_id = ac.pk_account_id
                                            LEFT JOIN accounts.tbl_chart_of_ac as coa ON coa.pk_chart_of_ac_id = tr.fk_main_ledger_id
                                            LEFT JOIN document.tbl_documents as doc ON doc.pk_documents_id = tr.fk_documents_id
                                            --WHERE CONDITION CURRENT DATA
                                        ) As tmp
                                    GROUP BY fk_main_ledger_id, vhr_account_code, vhr_account_name, vhr_code, vhr_name, vhr_display_name
                                    ORDER BY pk_account_id, fk_main_ledger_id 

TESTING DONE