SELECT a.branch_id, 
       a.YEAR, 
       a.srno, 
       a.branch_id 
       ||'-' 
       ||a.YEAR 
       ||'-' 
       ||a.srno   leas_acc, 
       a.REF, 
       a.insp_id, 
       b.NAME, 
       b.home_phone1 
       ||'.' 
       ||b.home_phone2 h_phone, 
       b.mobile, 
       b.area, 
       b.city, 
       a.trans_date, 
       a.inst, 
       Decode(Sign((a.inst * Trunc(Months_between(SYSDATE,a.trans_date)) + a.down_payment) - (Nvl(a.leasing,0) + Nvl(a.late_fee,0))),1,Nvl(a.leasing,0) + Nvl(a.late_fee,0) - Nvl(c.recv_amt,0), 
                                                                                                                                     a.inst * Trunc(Months_between(SYSDATE,a.trans_date)) + a.down_payment - Nvl(c.recv_amt,0)) dues, 
       Decode(Sign((a.inst * Trunc(Months_between(SYSDATE,a.trans_date)) + a.down_payment) - (Nvl(a.leasing,0) + Nvl(a.late_fee,0))),1,Nvl(a.leasing,0) + Nvl(a.late_fee,0) - Nvl(c.recv_amt,0), 
                                                                                                                                     (a.inst * Trunc(Months_between(SYSDATE,a.trans_date)) + a.down_payment - Nvl(c.recv_amt,0)) / a.inst) n_inst, 
       c.recv_amt, 
       Decode(c.last_recv,NULL,a.trans_date, 
                          last_recv) last_recv, 
       Trunc(SYSDATE - Decode(c.last_recv,NULL,a.trans_date, 
                                          c.last_recv), 
             0) late_days, 
       d.prom_date 
FROM   leas_acc a, 
       address_book b, 
       (SELECT   lea_year, 
                 acc_branch, 
                 lea_acc, 
                 Sum(amount)     recv_amt, 
                 Max(trans_date) last_recv 
        FROM     inst_trans 
        GROUP BY lea_year, 
                 acc_branch, 
                 lea_acc) c, 
       (SELECT   lea_year, 
                 acc_branch, 
                 lea_acc, 
                 Max(prom_date) prom_date 
        FROM     acc_diary 
        GROUP BY lea_year, 
                 acc_branch, 
                 lea_acc) d 
WHERE  a.YEAR = b.YEAR (+) 
       AND a.branch_id = b.branch_id (+) 
       AND a.srno = b.srno (+) 
       AND b.person_id = 0 
       AND a.status NOT IN ('S','C','D') 
       AND a.branch_id = :BRANCH 
       AND a.YEAR = c.lea_year (+) 
       AND a.branch_id = c.acc_branch (+) 
       AND a.srno = c.lea_acc (+) 
       AND a.YEAR = d.lea_year (+) 
       AND a.branch_id = d.acc_branch (+) 
       AND a.srno = d.lea_acc (+) &cond

Declare
	P_INST VARCHAR2(300);
	P_COND VARCHAR2(1500);
	
BEGIN
	
	P_INST:=' AND ROUND(DECODE(SIGN((A.INST*TRUNC(MONTHS_BETWEEN(SYSDATE,A.TRANS_DATE))+A.DOWN_PAYMENT)-(NVL(A.LEASING,0)+NVL(A.LATE_FEE,0))),1,
NVL(A.LEASING,0)+NVL(A.LATE_FEE,0)-NVL(C.RECV_AMT,0),(A.INST*TRUNC(MONTHS_BETWEEN(SYSDATE,A.TRANS_DATE))+A.DOWN_PAYMENT-NVL(C.RECV_AMT,0))/A.INST),'||''''||'999'||''''||')>'||5;
	
	P_COND:=P_INST;

	WEB.show_document('/reports/rwservlet?server='||:PARAMETER.reps
																							  ||'&ssoconn=default/oracledb/userid'
                                              	||'&report=acc_due_list2.rep'
                                              	||'&desformat=PDF'
                                              	||'&destype=CACHE'
                                              	||'&branch='||:PARAMETER.branch_id
                                              	||'&cond='||p_cond
																			        	||'&paramform=no'
																			        ,'_blank');
END;