AP Expenses

Description
Categories: Enginatics, Financials, Procurement
AP expenses with corresponding AP invoices, their status and line details, such as per diem or project and tasks
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 asu.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 asu,
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=asu.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 NameSQL textValidation
Show Projects Tasks
Y
LOV
Show per diem details
Y
LOV
Show Lines
Y
LOV
Operating Unit
hou.name=:operating_unit
LOV
Employee Name
papf.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
Show Projects Tasks
Y
Show Projects Tasks
ppa.segment1 project_number,
ppa.name project_name,
pt.task_number,
pt.task_name,
Show per diem details
Y
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,
Show Lines
aerla.report_header_id,
aerla.report_line_id,
aerla.distribution_line_number line_number,
aerla.item_description expense_type,
aerla.amount line_amount,
aerla.currency_code line_currency,
decode(aerla.itemization_parent_id,-1,to_number(null),ap_web_audit_utils.get_allowable_amount(aerla.report_header_id,aerla.distribution_line_number)) allowable_amount,
decode(aerla.itemization_parent_id,null,decode(ap_web_audit_utils.is_personal_expense(aerla.web_parameter_id),'Y',
(select decode(accta.category,'BUSINESS',accta.transaction_amount, to_number(NULL))
from ap_credit_card_trxns_all accta
where accta.trx_id=aerla.credit_card_trx_id
)),aerla.receipt_currency_amount) line_receipt_amount,
xxen_util.meaning(decode(aerla.receipt_verified_flag,'Y','Y'),'YES_NO',0) receipt_verified_flag,
xxen_util.meaning(decode(aerla.receipt_required_flag,'Y','Y'),'YES_NO',0) receipt_required_flag,
xxen_util.meaning(decode(aerla.receipt_missing_flag,'Y','Y'),'YES_NO',0) receipt_missing_flag,
aerla.justification,
aerla.start_expense_date,
aerla.end_expense_date,
(aerla.end_expense_date - aerla.start_expense_date)+1 num_of_days,
decode(aerla.credit_card_trx_id,null,'N','Y') cc_trx,
ap_web_audit_utils.get_expense_item_info(aerla.web_parameter_id,'category_code','VARCHAR2') category_code,
ap_web_audit_utils.is_personal_expense(aerla.web_parameter_id) personal_expense,
Source
aerha.source=xxen_util.lookup_code(:source,'SOURCE',200)
LOV
Expense Report Number
aerha.invoice_num=:exp_report_num
Report Submitted Date To
nvl(aerha.report_submitted_date,aerha.creation_date)<:exp_rep_sub_to+1
Date
Report Submitted Date From
nvl(aerha.report_submitted_date,aerha.creation_date)>=:exp_rep_sub_date
Date
Expense Status
aerha.expense_status_code=xxen_util.lookup_code(:expense_status_code,'EXPENSE REPORT STATUS',200)
LOV