IGI Secondary Invoice Approval : Secondary Approval of Invoices - draft

select  distinct decode(di.igi_sap_flag, 'Y', :p_status_1,:p_status_2) " Status"
,            gr.group_name  "Group"
,            di.batch_name   "Batch No"
,            SUBSTR(di.vendor_name,1,22) "Vendor Name"
,            di.invoice_num "Invoice Number"
,            di.invoice_date " Invoice  Date"
,            di.invoice_amount amount
,            di.entered_by "Entered By"
,           DECODE(di.igi_sap_flag,
                       'Y',fu2.user_name,fu3.user_name) "Authorised by"
from 
        fnd_user fu1,  
        fnd_user fu2,
        fnd_user fu3,
        igi_ap_inv_distributions_v di,
        igi_sap_invoice_clerk cl,
        igi_sap_department_approvers ap,
        igi_sap_group gr,
        ap_invoices_v ai,
        ap_invoice_distributions_v aid,
        ap_holds_v ahv
where  di.approver_id = nvl(:p_approver_id,di.approver_id)    
and      di.created_by = cl.clerk_id
and      cl.group_id = gr.group_id
and      gr.group_id = ap.group_id
and      ap.approver_id = di.approver_id
and      gr.group_id = nvl(:p_group_id,gr.group_id)
AND     decode(:p_batch_id,NULL,
               nvl(di.batch_id,-99999),
                 di.batch_id)
         =      decode(:p_batch_id,NULL,
                 nvl(di.batch_id,-99999),
                 :p_batch_id)
and     di.vendor_id = nvl(:p_vendor_id,di.vendor_id)
and     di.invoice_id = nvl(:p_invoice_id,di.invoice_id)
and     di.created_by = fu1.user_id
and     di.invoice_date between nvl(:p_start_date,di.invoice_date)
        and nvl(:p_end_date,di.invoice_date)
and     di.last_updated_by = fu2.user_id
and     di.approver_id = fu3.user_id
and     di.invoice_id = aid.invoice_id
and     di.dist_rowid = aid.row_id
and     di.invoice_id = ahv.invoice_id
and     aid.invoice_id = ai.invoice_id
and      aid.awt_invoice_id IS NULL
and      ai.cancelled_date IS NULL
and      ai.cancelled_by IS NULL
and      di.igi_sap_flag = decode(nvl(:p_release_lookup_code_1,'x'),'x',
		   di.igi_sap_flag,'AUTHORISED','Y','PENDING','N')
and     nvl(ahv.release_reason,'x') != 'INVOICE MODIFIED'
group by
         decode(di.igi_sap_flag,'Y',:p_status_1,:p_status_2)
,            gr.group_name
,            di.batch_name
,            SUBSTR(di.vendor_name,1,22)
,            di.invoice_num
,            di.invoice_date
,            di.entered_by
,            di.invoice_amount
,           DECODE(di.igi_sap_flag,
                       'Y',fu2.user_name,fu3.user_name)