SELECT r.trans_cntrl_no, a.comp_code, a.vch_grp_type, a.cb_sub_type, a.vch_prefix, a.vch_no, a.vch_dt, r.hdr_amt, r.dtl_amt
FROM (
SELECT trans_cntrl_no, sum(hdr_amt) hdr_amt, sum(dtl_amt)*(-1) dtl_amt
FROM (
SELECT h.trans_cntrl_no, Decode(h.vch_drcr_tag, 'D', Nvl(h.vch_amt, 0), (Nvl(h.vch_amt, 0)*(-1))) hdr_amt,
0 dtl_amt
FROM Fa_trans_hdr h
WHERE h.trans_approved = 'Y'
Union All
SELECT d.trans_cntrl_no, 0 hdr_amt,
Decode(d.trans_drcr_tag, 'D', Nvl(d.trans_amt, 0), (Nvl(d.trans_amt, 0)*(-1))) dtl_amt
FROM Fa_trans_dtl d, Fa_trans_hdr h
WHERE d.trans_cntrl_no = h.trans_cntrl_no
AND h.trans_approved = 'Y'
)
GROUP BY trans_cntrl_no
HAVING Round(sum(hdr_amt), 2) <> (Round(sum(dtl_amt), 2)*(-1))
) r, Fa_trans_hdr a
WHERE r.trans_cntrl_no = a.trans_cntrl_no
AND a.data_mig_by is null
ORDER BY a.comp_code, a.vch_dt, a.vch_no
/
-------------------- Dtl and sub dtl mismatch – Assets ---------
SELECT r.trans_cntrl_no, a.comp_code, a.vch_grp_type, a.cb_sub_type, a.vch_prefix, a.vch_no, a.vch_dt, r.dtl_amt,
r.sub_dtl_amt
FROM (
SELECT trans_cntrl_no, sum(dtl_amt) dtl_amt, sum(sub_dtl_amt) sub_dtl_amt
FROM (
SELECT d.trans_cntrl_no, Decode(d.trans_drcr_tag, 'D', Nvl(d.trans_amt, 0), (Nvl(d.trans_amt, 0)*(-1))) dtl_amt,
0 sub_dtl_amt
FROM Fa_trans_dtl d
WHERE (d.comp_code, d.mainac_code, d.subac_code) in (SELECT Distinct c.comp_code, s.asset_mainac_code, s.asset_subac_code
FROM Fa_asset_cat_mst c, Fa_asset_subcat_mst s
WHERE c.asset_cat_id = s.asset_cat_id )
Union All
SELECT a.fa_trans_cntrl_no, 0 dtl_amt, a.asset_acq_value sub_dtl_amt
FROM Fa_asset_mst a
WHERE a.fa_trans_cntrl_no is not null
)
GROUP BY trans_cntrl_no
HAVING Abs(sum(dtl_amt)) <> sum(sub_dtl_amt)
) r, Fa_trans_hdr a
WHERE r.trans_cntrl_no = a.trans_cntrl_no
AND a.data_mig_by is null
ORDER BY a.vch_dt, a.comp_code, a.vch_no
/
------------- Tax mismatch ------------
SELECT r.trans_cntrl_no, h.comp_code, h.entry_unit_code, h.vch_grp_type, h.vch_code, h.vch_no, h.vch_dt,
r.trans_dtl_id, r.dtl_tax_amt, r.subdtl_tax_amt
FROM (
SELECT trans_cntrl_no, trans_dtl_id, Sum(dtl_tax_amt) dtl_tax_amt, Sum(subdtl_tax_amt) subdtl_tax_amt
FROM (
SELECT d.trans_cntrl_no, d.trans_dtl_id, Nvl(d.tax_amt, 0) dtl_tax_amt, 0 subdtl_tax_amt
FROM Fa_trans_dtl d
WHERE Nvl(d.tax_amt, 0) <> 0
Union All
SELECT t.ref_cntrl_no, t.ref_dtl_id, 0 dtl_tax_amt, Nvl(t.tax_amount, 0) subdtl_tax_amt
FROM Fa_trans_tax_dtl t
WHERE tax_type not in ('B', 'S')
)
GROUP BY trans_cntrl_no, trans_dtl_id
HAVING Sum(dtl_tax_amt) <> Sum(subdtl_tax_amt)
) r, Fa_trans_hdr h
WHERE r.trans_cntrl_no = h.trans_cntrl_no
AND h.data_mig_by is null
/
