AP Invoice Approval Status

➜ Back to Library
Description
Categories: Enginatics
Repository: Github
Imported from Concurrent Program
Application: Payables
Source: Invoice Approval Status
Short Name: APXAPRST

When India Localization is installed, the following GST columns are also included: GST Registration No, GST PAN No, GST TAN No, CGST Amount, SGST Amount, IGST Amount, CESS Amount, Custom Amount, Unclassified Tax Amount, Taxable Value, HSN SAC Code, CGST Rate, SGST Rate, IGST  ...  Imported from Concurrent Program
Application: Payables
Source: Invoice Approval Status
Short Name: APXAPRST

When India Localization is installed, the following GST columns are also included: GST Registration No, GST PAN No, GST TAN No, CGST Amount, SGST Amount, IGST Amount, CESS Amount, Custom Amount, Unclassified Tax Amount, Taxable Value, HSN SAC Code, CGST Rate, SGST Rate, IGST Rate, CESS Rate, CGST Recoverable, CGST Non-Recoverable, SGST Recoverable, SGST Non-Recoverable, IGST Recoverable, IGST Non-Recoverable, Reverse Charge Flag, Place of Supply, Ship From State.   more 
select
x.legal_entity,
x.operating_unit,
x.party_name supplier,
x.supplier_number,
x.vendor_site_code supplier_site,
x.invoice_num invoice_number,
x.invoice_status,
x.invoice_date,
x.invoice_currency_code currency,
x.approval_context,
x.line_number,
x.approval_status,
x.action_date,
x.action,
x.approver_name approver,
x.applicable_amount,
x.amount_approved reviewed_amount,
case
when :p_sort_by='T' and 1=row_number() over (partition by x.invoice_id order by x.approval_history_id desc nulls last,x.action_date desc) then case when x.wfapproval_status in ('INITIATED','SENT','NEEDS WFREAPPROVAL') then x.invoice_amount else 0 end
when :p_sort_by='A' then case when x.action_code in ('SENT') then x.amount_approved else 0 end
end pending_amount,
case
when :p_sort_by='T' and 1=row_number() over (partition by x.invoice_id order by x.approval_history_id desc nulls last,x.action_date desc) then case when x.wfapproval_status in ('REJECTED','REJECT','STOPPED') then x.invoice_amount else 0 end
when :p_sort_by='A' then case when x.action_code in ('REJECT','REJECTED','STOPPED') then x.amount_approved else 0 end
end rejected_amount,
case
when :p_sort_by='T' and 1=row_number() over (partition by x.invoice_id order by x.approval_history_id desc nulls last,x.action_date desc) then case when x.wfapproval_status in ('WFAPPROVED','MANUALLY APPROVED') then x.invoice_amount else 0 end
when :p_sort_by='A' then case when x.action_code in ('APPROVED','MANUALLY APPROVED') then x.amount_approved else 0 end
end approved_amount,
&il_reg_columns
&il_columns_amt
&il_columns_rate
&il_columns_rec
&il_columns_tp
x.approval_history_id
from
(
select
x.legal_entity,
x.operating_unit,
x.party_name supplier,
x.supplier_number,
x.vendor_site_code supplier_site,
x.invoice_num invoice_number,
x.invoice_status,
x.invoice_date,
x.invoice_currency_code currency,
x.approval_context,
x.line_number,
x.approval_status,
x.action_date,
x.action,
x.approver_name approver,
x.applicable_amount,
x.amount_approved reviewed_amount,
x.party_name,
x.vendor_id,
x.vendor_site_id,
x.vendor_site_code,
x.wfapproval_status,
x.invoice_currency_code,
x.invoice_num,
x.amount_approved,
x.approver_name,
x.invoice_id,
x.invoice_amount,
x.action_code,
case
when :p_sort_by='T' and 1=row_number() over (partition by x.invoice_id order by x.approval_history_id desc nulls last,x.action_date desc) then case when x.wfapproval_status in ('INITIATED','SENT','NEEDS WFREAPPROVAL') then x.invoice_amount else 0 end
when :p_sort_by='A' then case when x.action_code in ('SENT') then x.amount_approved else 0 end
end pending_amount,
case
when :p_sort_by='T' and 1=row_number() over (partition by x.invoice_id order by x.approval_history_id desc nulls last,x.action_date desc) then case when x.wfapproval_status in ('REJECTED','REJECT','STOPPED') then x.invoice_amount else 0 end
when :p_sort_by='A' then case when x.action_code in ('REJECT','REJECTED','STOPPED') then x.amount_approved else 0 end
end rejected_amount,
case
when :p_sort_by='T' and 1=row_number() over (partition by x.invoice_id order by x.approval_history_id desc nulls last,x.action_date desc) then case when x.wfapproval_status in ('WFAPPROVED','MANUALLY APPROVED') then x.invoice_amount else 0 end
when :p_sort_by='A' then case when x.action_code in ('APPROVED','MANUALLY APPROVED') then x.amount_approved else 0 end
end approved_amount,
x.approval_history_id
from
(
(
select -- Q1 By Approver/By Trading Partner - Document Approval
xep.name legal_entity,
mo_global.get_ou_name(aia.org_id) operating_unit,
aia.vendor_id,
hp.party_name,
aps.segment1 supplier_number,
aia.vendor_site_id,
assa.vendor_site_code,
aia.wfapproval_status,
aia.invoice_currency_code,
fc.precision,
aia.invoice_num,
xxen_util.ap_invoice_status(aia.invoice_id,aia.invoice_amount,aia.payment_status_flag,aia.invoice_type_lookup_code,aia.validation_request_id) invoice_status,
aiaha.amount_approved,
aiaha.approver_name,
alc2.displayed_field action,
aiaha.last_update_date action_date,
aiaha.iteration,
alc.displayed_field approval_status,
aia.invoice_id,
aia.invoice_date,
aia.invoice_amount,
aia.invoice_amount applicable_amount,
nvl(alc3.displayed_field,'Document Approval') approval_context,
aiaha.response action_code,
to_number(null) line_number,
aiaha.approval_history_id
from
ap_inv_aprvl_hist_all aiaha,
ap_invoices_all aia,
gl_ledgers gl,
ap_suppliers aps,
ap_supplier_sites_all assa,
hz_parties hp,
fnd_currencies fc,
xle_entity_profiles xep,
ap_lookup_codes alc,
ap_lookup_codes alc2,
ap_lookup_codes alc3
where
:p_reporting_level=:p_reporting_level and
nvl(:p_status,'?')=nvl(:p_status,'?') and
1=1 and
2=2 and
aia.org_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat union select fnd_global.org_id from dual where fnd_release.major_version=11) and
aiaha.invoice_id=aia.invoice_id and
aia.set_of_books_id=gl.ledger_id and
aia.invoice_currency_code=fc.currency_code and
hp.party_id=aia.party_id and
aps.vendor_id(+)=aia.vendor_id and
assa.vendor_site_id(+)=aia.vendor_site_id and
xep.legal_entity_id(+)=aia.legal_entity_id and
alc.lookup_code=aia.wfapproval_status and
alc.lookup_type='AP_WFAPPROVAL_STATUS' and
alc2.lookup_code(+)=aiaha.response and
alc2.lookup_type(+)='AP_WFAPPROVAL_STATUS' and
alc3.lookup_type(+)='AP_WFAPPROVAL_CONTEXT' and
alc3.lookup_code(+)=aiaha.history_type and
(aiaha.history_type is null or
aiaha.history_type='DOCUMENTAPPROVAL'
)
union
select -- Q2 By Approver/By Trading Partner - Line Approval
xep.name legal_entity,
mo_global.get_ou_name(aia.org_id) operating_unit,
aia.vendor_id,
hp.party_name,
aps.segment1 supplier_number,
aia.vendor_site_id,
assa.vendor_site_code,
ail.wfapproval_status,
aia.invoice_currency_code,
fc.precision,
aia.invoice_num,
xxen_util.ap_invoice_status(aia.invoice_id,aia.invoice_amount,aia.payment_status_flag,aia.invoice_type_lookup_code,aia.validation_request_id) invoice_status,
aiaha.amount_approved,
aiaha.approver_name,
alc2.displayed_field action,
aiaha.last_update_date action_date,
aiaha.iteration,
alc.displayed_field approval_status,
aia.invoice_id,
aia.invoice_date,
decode(aia.wfapproval_status,'INITIATED',0,aia.invoice_amount) invoice_amount,
ail.amount applicable_amount,
alc3.displayed_field approval_context,
aiaha.response action_code,
aiaha.line_number,
aiaha.approval_history_id
from
ap_inv_aprvl_hist_all aiaha,
ap_invoice_lines_all ail,
ap_invoices_all aia,
gl_ledgers gl,
ap_suppliers aps,
ap_supplier_sites_all assa,
hz_parties hp,
fnd_currencies fc,
xle_entity_profiles xep,
ap_lookup_codes alc,
ap_lookup_codes alc2,
ap_lookup_codes alc3
where
1=1 and
2=2 and
aia.org_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat union select fnd_global.org_id from dual where fnd_release.major_version=11) and
aia.invoice_id=ail.invoice_id and
aiaha.invoice_id=ail.invoice_id and
aiaha.line_number=ail.line_number and
aiaha.history_type='LINESAPPROVAL' and
aia.set_of_books_id=gl.ledger_id and
aia.invoice_currency_code=fc.currency_code and
hp.party_id=aia.party_id and
alc.lookup_code=ail.wfapproval_status and
alc.lookup_type='AP_WFAPPROVAL_STATUS' and
aps.vendor_id(+)=aia.vendor_id and
assa.vendor_site_id(+)=aia.vendor_site_id and
xep.legal_entity_id(+)=aia.legal_entity_id and
alc2.lookup_code(+)=aiaha.response and
alc2.lookup_type(+)='AP_WFAPPROVAL_STATUS' and
alc3.lookup_type(+)='AP_WFAPPROVAL_CONTEXT' and
alc3.lookup_code(+)=aiaha.history_type
union
select --Q3 By Trading Partner
xep.name legal_entity,
mo_global.get_ou_name(aia.org_id) operating_unit,
aia.vendor_id,
hp.party_name,
aps.segment1 supplier_number,
aia.vendor_site_id,
assa.vendor_site_code,
aia.wfapproval_status,
aia.invoice_currency_code,
fc.precision,
aia.invoice_num,
xxen_util.ap_invoice_status(aia.invoice_id,aia.invoice_amount,aia.payment_status_flag,aia.invoice_type_lookup_code,aia.validation_request_id) invoice_status,
0 amount,
'N/A' approver,
'Required' action,
null action_date,
null iteration,
'Required' approval_status,
aia.invoice_id,
aia.invoice_date,
aia.invoice_amount,
aia.invoice_amount applicable_amount,
'Invoice Approval' approval_context,
'N/A' action_code,
to_number(null) line_number,
0 approval_history_id
from
ap_invoices_all aia,
gl_ledgers gl,
ap_suppliers aps,
ap_supplier_sites_all assa,
hz_parties hp,
fnd_currencies fc,
xle_entity_profiles xep,
ap_lookup_codes alc
where
:p_sort_by='T' and
1=1 and
3=3 and
aia.org_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat union select fnd_global.org_id from dual where fnd_release.major_version=11) and
aia.set_of_books_id=gl.ledger_id and
aia.invoice_currency_code=fc.currency_code and
hp.party_id=aia.party_id and
aps.vendor_id(+)=aia.vendor_id and
assa.vendor_site_id(+)=aia.vendor_site_id and
xep.legal_entity_id(+)=aia.legal_entity_id and
alc.lookup_code=aia.wfapproval_status and
alc.lookup_type='AP_WFAPPROVAL_STATUS'
)
union all
select --Q4 Future Approvers
xep.name legal_entity,
mo_global.get_ou_name(aia.org_id) operating_unit,
aia.vendor_id,
hp.party_name,
aps.segment1 supplier_number,
aia.vendor_site_id,
assa.vendor_site_code,
aia.wfapproval_status,
aia.invoice_currency_code,
fc.precision,
aia.invoice_num,
xxen_util.ap_invoice_status(aia.invoice_id,aia.invoice_amount,aia.payment_status_flag,aia.invoice_type_lookup_code,aia.validation_request_id) invoice_status,
awh.amount_approved,
listagg(awh.approver_name,''||chr(10)) within group(order by decode(awh.iteration,0,9999,awh.iteration),decode(awh.line_number,null,9999,awh.line_number),awh.last_update_date,awh.approver_order_number) approver_name,
awh.response_dsp action,
awh.last_update_date action_date,
awh.iteration,
xxen_util.meaning(aia.wfapproval_status,'AP_WFAPPROVAL_STATUS',200) approval_status,
aia.invoice_id,
aia.invoice_date,
aia.invoice_amount,
aia.invoice_amount applicable_amount,
xxen_util.meaning(awh.approval_context,'AP_WFAPPROVAL_CONTEXT',200) approval_context,
awh.response action_code,
null line_number,
null approval_history_id
from
ap_wfapproval_history_v awh,
ap_invoices_all aia,
gl_ledgers gl,
ap_suppliers aps,
ap_supplier_sites_all assa,
hz_parties hp,
fnd_currencies fc,
xle_entity_profiles xep
where
1=1 and
2=2 and
awh.response='FUTURE' and
aia.org_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat union select fnd_global.org_id from dual where fnd_release.major_version=11) and
awh.invoice_id=aia.invoice_id and
aia.set_of_books_id=gl.ledger_id and
aia.invoice_currency_code=fc.currency_code and
hp.party_id=aia.party_id and
aps.vendor_id(+)=aia.vendor_id and
assa.vendor_site_id(+)=aia.vendor_site_id and
xep.legal_entity_id(+)=aia.legal_entity_id
group by 
xep.name,
mo_global.get_ou_name(aia.org_id),
aia.vendor_id,
hp.party_name,
aps.segment1,
aia.vendor_site_id,
assa.vendor_site_code,
aia.wfapproval_status,
aia.invoice_currency_code,
fc.precision,
aia.invoice_num,
awh.amount_approved,
awh.response_dsp,
awh.last_update_date,
awh.iteration,
aia.invoice_id,
aia.invoice_date,
aia.invoice_amount,
xxen_util.meaning(awh.approval_context,'AP_WFAPPROVAL_CONTEXT',200),
awh.response,
xxen_util.ap_invoice_status(aia.invoice_id,aia.invoice_amount,aia.payment_status_flag,aia.invoice_type_lookup_code,aia.validation_request_id)
) x
) x
&il_from
where
:p_reporting_level=:p_reporting_level
&il_where
order by
x.legal_entity,
x.operating_unit,
decode(:p_sort_by,'A',x.approver_name,null),
decode(:p_sort_by,'A',x.invoice_currency_code,null),
decode(:p_sort_by,'A',x.approval_context,null),
decode(:p_sort_by,'A',x.action_date,null) desc,
decode(:p_sort_by,'T',x.party_name,null),
decode(:p_sort_by,'T',x.vendor_site_code,null),
decode(:p_sort_by,'T',x.invoice_currency_code,null),
x.approval_history_id desc nulls last,
x.invoice_date,
x.invoice_num,
x.action_date desc
Parameter NameSQL textValidation
Reporting Level
 
LOV Oracle
Reporting Context
decode(:p_reporting_level,1000,gl.name,3000,mo_global.get_ou_name(aia.org_id),null)=:p_reporting_entity
LOV
Sort By
 
LOV
Trading Partner
aia.party_id=:p_party_id
LOV Oracle
Supplier Site
aia.vendor_site_id=:p_vendor_site_id
LOV Oracle
Approver
exists 
(select 
 aiaha.invoice_id 
 from 
 ap_inv_aprvl_hist_all aiaha 
 where 
 aiaha.invoice_id = aia.invoice_id and 
 (aiaha.approver_id = :p_approver or 
  (aiaha.approver_id is null and
   aiaha.approver_name in (select hecv.full_name from hr_employees_current_v hecv where hecv.employee_id = :p_approver) 
  )
 )
)
LOV
Invoice Date From
aia.invoice_date>=:p_invoice_date_from
Date
Invoice Date To
aia.invoice_date<:p_invoice_date_to+1
Date
Approval Status
 
LOV
Download
Blitz Report™