------------- Purchase and sales invoices which are not there in ageing ------------
SELECT a.trans_cntrl_no, a.comp_code, a.ac_unit_hdr, a.mainac_code, a.subac_code, a.invoice_no, a.invoice_dt
FROM fa_trans_hdr a
WHERE a.vch_grp_type in ('P', 'S')
AND (a.comp_code, a.mainac_code) in (SELECT m.comp_code, m.mainac_code FROM Fa_mainac_mst m
WHERE m.ap_ar_type in ('P', 'R')
AND m.billwise_adj_yn = 'Y')
AND a.data_mig_by is null
AND a.trans_approved = 'Y'
Minus
SELECT a.fa_trans_cntrl_no, a.comp_code, a.ac_unit, a.mainac_code, a.subac_code, a.bill_ref_no, a.bill_ref_dt
FROM Fa_agetrans_dtl a
WHERE a.age_ref_type='BR'
AND (a.comp_code, a.mainac_code) in (SELECT m.comp_code, m.mainac_code FROM Fa_mainac_mst m
WHERE m.ap_ar_type in ('P', 'R')
AND m.billwise_adj_yn = 'Y')
/
------------------- Insert Missing bills ------
Declare
Cursor c is
SELECT * FROM Fa_trans_hdr Where trans_cntrl_no in (
SELECT trans_cntrl_no from (
SELECT a.trans_cntrl_no, a.comp_code, a.ac_unit_hdr, a.mainac_code, a.subac_code, a.invoice_no, a.invoice_dt
FROM fa_trans_hdr a
WHERE a.vch_grp_type in ('P', 'S')
AND (a.comp_code, a.mainac_code) in (SELECT m.comp_code, m.mainac_code FROM Fa_mainac_mst m
WHERE m.ap_ar_type in ('P', 'R')
AND m.billwise_adj_yn = 'Y')
AND a.data_mig_by is null
AND a.trans_approved = 'Y'
Minus
SELECT a.fa_trans_cntrl_no, a.comp_code, a.ac_unit, a.mainac_code, a.subac_code, a.bill_ref_no, a.bill_ref_dt
FROM Fa_agetrans_dtl a
WHERE a.age_ref_type='BR'
AND (a.comp_code, a.mainac_code) in (SELECT m.comp_code, m.mainac_code FROM Fa_mainac_mst m
WHERE m.ap_ar_type in ('P', 'R')
AND m.billwise_adj_yn = 'Y')))
Order by trans_cntrl_no ;
pErrorNo number ;
pErrorMsg varchar2(1000);
Begin
For i in c
Loop
Fa_age_trans_pkg.Ins_age_trans_bill ( i.comp_code, i.entry_unit_code, i.trans_cntrl_no, i.trans_approved_by,
i.crtd_ip, pErrorNo, pErrorMsg ) ;
End Loop;
End;
/
------------------------- End of Insert missing bills ----------
--------------- Purchase and Sales Bill mismatch -----------
SELECT r.trans_cntrl_no, r.comp_code, r.ac_unit_hdr, r.mainac_code, r.pv_subac_code, r.ag_subac_code,
r.pv_invoice_no, r.ag_invoice_no, r.pv_invoice_dt, r.ag_invoice_dt, r.pv_bill_amt, r.ag_bill_amt
FROM (
SELECT a.trans_cntrl_no, a.comp_code, a.ac_unit_hdr, a.mainac_code, a.subac_code pv_subac_code,
b.subac_code ag_subac_code, a.invoice_no pv_invoice_no, b.bill_ref_no ag_invoice_no,
a.invoice_dt pv_invoice_dt, b.bill_ref_dt ag_invoice_dt,
Decode(a.vch_drcr_tag, 'C', a.vch_amt, Nvl(a.vch_amt, 0)*(-1)) pv_bill_amt, b.age_trans_amt ag_bill_amt
FROM Fa_trans_hdr a, Fa_agetrans_dtl b
WHERE a.trans_cntrl_no = b.fa_trans_cntrl_no
AND a.vch_grp_type in ('P', 'S')
AND a.data_mig_by is null
AND (a.comp_code, a.mainac_code) in (SELECT m.comp_code, m.mainac_code FROM Fa_mainac_mst m
WHERE m.ap_ar_type in ('P', 'R')
AND m.billwise_adj_yn = 'Y')
AND b.age_ref_type='BR'
) r
WHERE r.pv_subac_code <> r.ag_subac_code
OR Abs(r.pv_bill_amt) <> Abs(r.ag_bill_amt)
OR r.pv_invoice_no <> r.ag_invoice_no
OR r.pv_invoice_dt <> r.ag_invoice_dt
/
--------Miss match in Debit note and credit card bill details ------
SELECT * FROM (
SELECT a.trans_cntrl_no, a.comp_code, a.ac_unit_hdr, a.mainac_code, a.subac_code pv_subac_code,
b.subac_code ag_subac_code, Nvl(a.vch_amt, 0) pv_bill_amt, Nvl(b.trans_amt, 0)*(-1) ag_bill_amt
FROM Fa_trans_hdr a,
( SELECT g.fa_trans_cntrl_no, g.subac_code, sum(Nvl(g.trans_amt, 0)) trans_amt FROM Fa_agetrans_dtl_vu g
WHERE g.vch_grp_type in ('D', 'R')
GROUP BY g.fa_trans_cntrl_no, g.subac_code ) b
WHERE a.trans_cntrl_no = b.fa_trans_cntrl_no(+)
AND a.data_mig_by is null
AND a.vch_grp_type in ('D', 'R')
AND (a.comp_code, a.mainac_code) in (SELECT m.comp_code, m.mainac_code FROM Fa_mainac_mst m
WHERE m.ap_ar_type in ('P', 'R')
AND m.billwise_adj_yn = 'Y')
) r
WHERE r.pv_subac_code <> r.ag_subac_code
OR r.pv_bill_amt <> r.ag_bill_amt
/
--------Miss match in dtl and ageing bill details ------
SELECT * FROM (
SELECT a.trans_cntrl_no, a.comp_code, a.ac_unit_dtl, a.mainac_code pv_mainac_code, b.mainac_code ag_mainac_code,
a.subac_code pv_subac_code, b.subac_code ag_subac_code, Nvl(a.trans_amt, 0) pv_bill_amt,
Nvl(b.trans_amt, 0)*(-1) ag_bill_amt
FROM Fa_trans_dtl a, Fa_trans_hdr h,
( SELECT g.fa_trans_cntrl_no, g.fa_trans_dtl_id, g.mainac_code, g.subac_code,
sum(Nvl(g.trans_amt, 0)) trans_amt
FROM Fa_agetrans_dtl_vu g
WHERE Nvl(g.fa_trans_dtl_id, 0) <> 0
GROUP BY g.fa_trans_cntrl_no, g.fa_trans_dtl_id, g.mainac_code, g.subac_code ) b
WHERE a.trans_cntrl_no = h.trans_cntrl_no
AND a.trans_cntrl_no = b.fa_trans_cntrl_no(+)
AND a.trans_dtl_id = b.fa_trans_dtl_id(+)
AND a.data_mig_by is null
AND h.vch_grp_type in ('B', 'C', 'J')
AND (a.comp_code, a.mainac_code) in (SELECT m.comp_code, m.mainac_code FROM Fa_mainac_mst m
WHERE m.ap_ar_type in ('P', 'R')
AND m.billwise_adj_yn = 'Y')
) r
WHERE r.pv_subac_code <> r.ag_subac_code
OR r.pv_subac_code <> r.ag_subac_code
OR r.pv_bill_amt <> r.ag_bill_amt
/
------------- ageing cntrl mismatch -------
select * from fa_agetrans_dtl
where age_ref_type in ('AV', 'AVR', 'AA') and advance_trans_dtl_id is null
/
select * from fa_agetrans_dtl
where age_ref_type in ('BR', 'ADJM', 'ADJP', 'AR', 'ARR', 'AA') and bill_cntrl_no is null
/
No comments:
Post a Comment