AP Expenses

➜ Back to Library
Description
Categories: Enginatics
Repository: Github
Detail AP expense report listing corresponding AP invoices with status, line details, expense type and expense justification. Including projects task related expenses.
Run AP Expenses and other Oracle EBS reports with Blitz Report™ on our demo environment
select
gl.name Ledger,
hou.name operating_unit,
ppx.full_name employee_name,
xxen_util.meaning(aerha.source,'SOURCE',200) source,
decode(ppx.current_npw_flag, 'Y', ppx.npw_number, ppx.employee_number) employee_number,
aerha.invoice_num expense_report_number,
aerha.description purpose,
case when aerha.source in ('Both Pay','CREDIT CARD') then aps.vendor_name end card_provider,
trunc(aerha.week_end_date) expense_report_date,
aerha.total expense_amount,
aerha.payment_currency_code currency,
nvl(aerha.report_submitted_date,aerha.creation_date) report_submitted_date,
initcap(aerha.receipts_status) original_receipt_status,
aerha.receipts_received_date,
&image_receipt_columns
xxen_util.meaning(case when aerha.audit_code in ('PAPERLESS_AUDIT','RECEIPT_BASED') and nvl(aerha.workflow_approved_flag,'M') in ('N','M') then 'Y' else 'N' end,'YES_NO',0) requires_audit,
xxen_util.meaning(nvl2(aaq.auditor_id,'Y','N'),'YES_NO',0) auditor_assigned,
ap_web_audit_utils.get_audit_reason(aerha.report_header_id) audit_reason,
decode(aaq.auditor_id,-1,fnd_message.get_string('SQLAP','OIE_AUD_FALLBACK_AUDITOR'),xxen_util.user_name(aaq.auditor_id)) auditor,
xxen_util.meaning(nvl(aerha.audit_code,'AUDIT'),'OIE_AUDIT_TYPES',200) audit_type,
xxen_util.user_name(aerha.last_audited_by) last_audited_by,
xxen_util.user_name(aerha.last_updated_by) last_updated_by,
ap_web_expense_form.get_num_total_violations(aerha.report_header_id) num_total_violations,
ap_web_expense_form.get_num_violation_lines(aerha.report_header_id) num_violation_lines,
aerha.report_filing_number,
xxen_util.meaning(nvl2(aia.cancelled_date,'CANCELLED',aerha.expense_status_code),'EXPENSE REPORT STATUS',200) expense_status,
decode(aerha.workflow_approved_flag,'S','saved','I','implicit save','R','manager rejected','T','returned to preparer','M','manager approved','P','payables approved','A','automatically approved','W','withdrawn','Y','manager and payables approved','submitted') workflow_approved_status,
xxen_util.meaning(case when aerha.workflow_approved_flag in ('Y','A','M') then 'Y' end,'YES_NO',0) mgmt_reviewed_flag,
xxen_util.meaning(case when aerha.audit_code='AUTO_APPROVE' or aerha.workflow_approved_flag='A' then 'Y' end,'YES_NO',0) auto_approved_flag,
xxen_util.meaning(case when aerha.workflow_approved_flag in ('Y','A','P') or aerha.audit_code='AUTO_APPROVE' then 'Y' end,'YES_NO',0) ap_reviewed_flag,
xxen_util.meaning(decode(aia.payment_status_flag,'Y','Y'),'YES_NO',0) ap_status_paid_flag,
xxen_util.meaning(nvl2(aia.invoice_num,'Y',null),'YES_NO',0) ap_status_invoice_created_flag,
xxen_util.meaning(decode(decode(aia.payment_status_flag,'P','Y','N'),'Y','Y'),'YES_NO',0) ap_status_partially_paid_flag,
xxen_util.meaning(nvl2(aerha.holding_report_header_id,'Y',null),'YES_NO',0) hold_flag,
&lines_columns
&per_diem_columns
&proj_tasks_columns
aerha.expense_report_id
from
gl_ledgers gl,
hr_operating_units hou,
ap_expense_report_headers_all aerha,
per_people_x ppx,
ap_aud_queues aaq,
ap_suppliers aps,
ap_invoices_all aia,
(select aerla.* from ap_expense_report_lines_all aerla where '&show_lines'='Y') aerla,
&per_diem_table
(select aerda.* from ap_exp_report_dists_all aerda where '&show_protasks'='Y') aerda
&proj_tasks_tables
where
1=1 and
aerha.set_of_books_id=gl.ledger_id and
hou.organization_id=aerha.org_id and
aerha.employee_id=ppx.person_id(+) and
aerha.vendor_id=aps.vendor_id(+) and
aerha.vouchno=aia.invoice_id(+) and
aerha.report_header_id=aaq.expense_report_id(+) and
aerha.report_header_id=aerla.report_header_id(+) and
&per_diem_join
aerla.report_line_id=aerda.report_line_id(+)
&proj_tasks_joins
Parameter Name SQL text Validation
Ledger 
gl.name=:ledger and 
hou.organization_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat union select fnd_global.org_id from dual)
 LOV
Operating Unit 
hou.name=:operating_unit
 LOV
Expense Status 
aerha.expense_status_code=xxen_util.lookup_code(:expense_status_code,'EXPENSE REPORT STATUS',200)
 LOV
Report Submitted Date From 
nvl(aerha.report_submitted_date,aerha.creation_date)>=:exp_rep_sub_date
 Date
Report Submitted Date To 
nvl(aerha.report_submitted_date,aerha.creation_date)<:exp_rep_sub_to+1
 Date
Employee Name 
ppx.full_name=:full_name
 LOV
Expense Report Number 
aerha.invoice_num=:exp_report_num
 Char
Auditor Name 
decode(aaq.auditor_id,-1,fnd_message.get_string('SQLAP','OIE_AUD_FALLBACK_AUDITOR'),ap_web_policy_utils.get_employee_name(aaq.auditor_id))=:auditor_name
 LOV
Audit Reason 
exists 
(select null
 from ap_aud_audit_reasons aaar
 where aaar.report_header_id = aerha.report_header_id
 and   AP_WEB_POLICY_UTILS.get_lookup_meaning('OIE_AUDIT_REASONS',aaar.audit_reason_code) =  :audit_reason
)
 LOV
Audit Type 
ap_web_policy_utils.get_lookup_meaning('OIE_AUDIT_TYPES',nvl(aerha.audit_code,'AUDIT'))=:audit_type
 LOV
Source 
aerha.source=xxen_util.lookup_code(:source,'SOURCE',200)
 LOV
Expense Type 
aerla.item_description=:expense_type
 LOV
Show Lines 
Y
 LOV
Show per diem details 
trunc(opdb.start_date) pdm_date,
opdb.amount pdm_amount,
opdb.number_of_meals,
opdb.meals_amount,
xxen_util.meaning(decode(opdb.breakfast_flag,'Y','Y'),'YES_NO',0) breakfast_flag,
xxen_util.meaning(decode(opdb.lunch_flag,'Y','Y'),'YES_NO',0) lunch_flag,
xxen_util.meaning(decode(opdb.dinner_flag,'Y','Y'),'YES_NO',0) dinner_flag,
opdb.accommodation_amount,
xxen_util.meaning(decode(opdb.accommodation_flag,'Y','Y'),'YES_NO',0) accommodation_flag,
opdb.hotel_name,
opdb.night_rate_type,
opdb.night_rate_amount,
opdb.pdm_rate,
opdb.cust_meals_amount,
opdb.cust_accommodation_amount,
opdb.cust_night_rate_amount,
opdb.cust_pdm_rate,
 LOV
Show Projects Tasks 
ppa.segment1 project_number,
ppa.name project_name,
pt.task_number,
pt.task_name,
 LOV
Download
 
Related Blitz Reports
GL Account Analysis (Distributions) GL Account Analysis COPY OF: GL Account Analysis AP Invoices and Lines CAC Invoice Price Variance GL Account Analysis (Distributions) 11g FA Asset Book Details PO Headers and Lines PO Purchase Requisitions with PO Details
Blitz Report™

Blitz Report™ provides multiple benefits: