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,
&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 NameSQL textValidation
Show Lines
Y
LOV
Show Projects Tasks
ppa.segment1 project_number,
ppa.name project_name,
pt.task_number,
pt.task_name,
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
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
Expense Type
aerla.item_description=:expense_type
LOV
EBS Release Version
aerha.image_receipts_status,
aerha.image_receipts_received_date,
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
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
Expense Type
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,
Expense Type
Y
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,
Show per diem details
Y
Show Projects Tasks
Y
Show per diem details
aerla.report_line_id=opdb.report_line_id(+) and
Show per diem details
(select opdb.* from oie_pdm_daily_breakups opdb) opdb,
Show Projects Tasks
select case decode(fnd_release.major_version,'11','11','12')
when '11'
then 'and aerla.project_id=ppa.project_id(+) and aerla.task_id=pt.task_id(+)'
else 'and aerda.project_id=ppa.project_id(+) and aerda.task_id=pt.task_id(+)'
end
from dual
Show Projects Tasks
,pa_projects_all ppa
,pa_tasks pt