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;