PROCEDURE proc_merge IS
BEGIN
declare
v_com varchar2(5000);
BEGIN
V_COM := '
Merge into allvouchers trg
using
(select voucher_type,voucher_no,voucher_date,cheque_no,bank_code,narration,debit,credit from(
select pv_voucher_type Voucher_type, pv_no Voucher_No, pv_date Voucher_date, Cheque_no, Acc_code Bank_code, Narration,Debit,Credit
from
(select a.pv_voucher_type,a.pv_no, a.pv_date, a.cheque_no, a.pv_gl_credit_code acc_code , b.ca_title,null Debit,
a.pv_gl_credit_amount Credit, a.Pv_narration Narration
from gl_pv a, ca_gl b where a.pv_gl_credit_Code = b.ca_code and a.pv_date > to_date (''30/06/2010'',''dd/mm/yyyy'')
union all
select a.pv_voucher_type,a.pv_voucher_no,a.pv_date,b.cheque_no,a.pv_acc_code,c.ca_title,a.pv_debit, Null credit,a.pv_desc
from payment_detail a, gl_pv b, ca_gl c
where a.pv_voucher_no = b.pv_no
and a.pv_date = b.pv_date
and a.pv_voucher_type = b.pv_voucher_type
and pv_acc_code = c.ca_code and a.pv_debit > 0
and a.pv_date > to_date (''30/06/2010'',''dd/mm/yyyy'')
union all
select a.pv_voucher_type, a.pv_voucher_no,a.pv_date, b.cheque_no, a.pv_acc_code, c.ca_title,
null ,a.pv_credit, a.pv_desc
from payment_detail a , gl_pv b , ca_gl c
where a.pv_voucher_no = b.pv_no
and a.pv_date = b.pv_date
and a.pv_acc_code = c.ca_code
and pv_credit >0 and a.pv_date > to_date (''30/06/2010'',''dd/mm/yyyy'')
union all
select a.pv_voucher_type, a.pv_no,pv_date,a.cheque_no , a.wht_debt_ac, b.ca_title , null, a.wht_amount, a.wht_narration
from gl_pv a , ca_gl b where a.wht_debt_ac = b.ca_code and wht_amount > 0
and a.pv_date > to_date (''30/06/2010'',''dd/mm/yyyy'')
)
where acc_code = :bankcode
----------------------
Union all
--------------------- R.Voucher ----------------
select a.rv_voucher_type, a.rv_no, a.rv_date, a.chequeno, b.rv_acc_code, a.rv_narration,b.rv_debit, b.rv_credit
from gl_receipt a, receipt_detail b , ca_gl c
where a.rv_no = b.rv_voucher_no
and a.rv_date = b.rv_date
and b.rv_acc_code = c.ca_code
and b.rv_acc_code = :bankcode
and a.rv_date > to_date (''30/06/2010'',''dd/mm/yyyy'')
----------------------- SRV --------------
Union all
select a.vm_voucher_type ,a.vm_no ,a.vm_date,a.chequeno,b.vd_acc_code, b.vd_desc VNARRAION,
b.vd_debit VDEBIT,b.vd_credit VCREDIT
from vouchermaster a, voucherdetail b , ca_gl c
where a.vm_no= b.vd_no
and a.vm_voucher_type = b.vm_voucher_type
and a.vm_date = b.vm_date
and b.vd_acc_code = c.ca_code
and b.vd_acc_code = :bankcode
and a.vm_date > to_date (''30/06/2010'',''dd/mm/yyyy'')
and (a.show_voucher =''Y'' or Show_Voucher is Null)
and a.vm_voucher_type =''SRV''
)) Src
on (trg.v_type = src.voucher_type and trg.v_no = src.voucher_no and trg.v_date = src.voucher_date)
when matched then
update set
trg.v_chq = src.cheque_no, trg.v_bankcode = src.bank_code ,trg.v_debit=src.debit, trg.v_credit=src.credit, trg.v_narration=src.narration
when not matched then
insert (trg.v_type, trg.v_no, trg.v_date, trg.v_chq, trg.v_bankcode, trg.v_debit, trg.v_credit, trg.v_narration)
values (src.voucher_type, src.voucher_no, src.voucher_date, src.cheque_no, src.bank_code, src.debit, src.credit, src.narration)
';
FORMS_DDL(V_COM);
end;
END;