IGI Secondary Invoice Approval : Secondary Approval of Invoices - draft

Description
Categories: BI Publisher, Financials
Application: Public Sector Financials International
Source: Secondary Invoice Approval : Secondary Approval of Invoices (XML) - Not Supported: Reserved For Future Use
Short Name: IGIPSAPR_XML
DB package: IGI_IGIPSAPR_XMLP_PKG
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)
Parameter Name SQL text Validation
Invoice End Date
 
Date
Invoice Start Date
 
Date
Invoice Number
 
LOV Oracle
Batch Name
 
LOV Oracle
Vendor Name
 
LOV Oracle
Approver User Name
 
LOV Oracle
Group Name
 
LOV Oracle
Status
 
LOV Oracle