AP Expenses

Description
Categories: Enginatics, Toolkit - Operations
Repository: Github Columns: Ledger, Operating Unit, Employee Name, Source, Employee Number, Expense Report Number, Purpose, Card Provider, Expense Report Date, Expense Amount ...
Detail AP expense report listing corresponding AP invoices with status, line details, expense type and expense justification. Including projects task related expenses.
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,
aerha.image_receipts_status,
aerha.image_receipts_received_date,
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
&pro_taks_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,
(select opdb.* from oie_pdm_daily_breakups opdb where '&show_per_diem'='Y') opdb,
(select aerda.* from ap_exp_report_dists_all aerda where '&show_protasks'='Y') aerda,
pa_projects_all ppa,
pa_tasks pt
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
aerla.report_line_id=opdb.report_line_id(+) and
aerla.report_line_id=aerda.report_line_id(+) and
aerda.project_id=ppa.project_id(+) and
aerda.task_id=pt.task_id(+)
Parameter Name SQL text Validation
Show Lines
Y
LOV
Show per diem details
Y
LOV
Show Projects Tasks
Y
LOV
Operating Unit
hou.name=:operating_unit
LOV
Employee Name
ppx.full_name=:full_name
LOV
Ledger
gl.name=:ledger
LOV
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 Type
ap_web_policy_utils.get_lookup_meaning('OIE_AUDIT_TYPES',nvl(aerha.audit_code,'AUDIT'))=:audit_type
LOV
Audit Reason
ap_web_audit_utils.get_audit_reason(aerha.report_header_id)=:audit_reason
LOV
Expense Type
aerla.item_description=:expense_type
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
Expense Report Number
aerha.invoice_num=:exp_report_num
Source
aerha.source=xxen_util.lookup_code(:source,'SOURCE',200)
LOV
Expense Type