PA Expense Report Entry

Description
Categories: BI Publisher
Application: Projects
Source: AUD: Expense Report Entry (XML)
Short Name: PAXEXONE_XML
DB package: PA_PAXEXONE_XMLP_PKG
select distinct 
       ai.vendor_id,
       per.full_name "Employee Name:",
       NVL (per.employee_number, per.npw_number) "Employee Number:",
       pa_utils.GetWeekending(aid.expenditure_item_date) "Week Ending Date:",
       ai.CREATION_DATE "Initial Submission Date:",
       ai.source "Entry Method",
       ai.DESCRIPTION "Description:",
       aid.INVOICE_ID,
       aid.EXPENDITURE_ITEM_DATE "Date",
       to_char(aid.expenditure_item_date,'FMDay') "Week Day",
       nvl(aid.quantity_invoiced,0) "Quantity",
       nvl(aid.amount,0) "Amount",
       lk.meaning "Unit",
       aid.expenditure_type "Expenditure Type",
       pa.name     "Project Name",
       pa.segment1 "Project Number",
       ta.task_number "Task Number",
       ta.task_Name "Task name",
       aid.description "Description", 
	PA_PAXEXONE_XMLP_PKG.c_dis_sum_invoiceformula(:C_SUM_INVOICE) C_dis_Sum_invoice, 
	PA_PAXEXONE_XMLP_PKG.c_bill_pctformula(:C_SUM_INVOICE, :C_sum_invoice1) C_bill_pct, 
	PA_PAXEXONE_XMLP_PKG.c_amountformula(nvl ( aid.amount , 0 )) C_Amount
from per_people_f per,
       hr_organization_units org,
       pa_expenditure_types et,
       pa_tasks ta,
       pa_lookups lk,
       pa_projects_all pa,
       po_vendors pv,   
       ap_invoices ai,
       &lp_ap_invoice_dist aid 
WHERE  ai.INVOICE_ID = aid.INVOICE_ID
  AND  ai.INVOICE_TYPE_LOOKUP_CODE = 'EXPENSE REPORT'
  AND  per.person_id between nvl(:Employee_id,0)  
                         and nvl(:Employee_id,999999999999999)
  and  (per.employee_number IS NOT NULL OR per.npw_number IS NOT NULL)   and pa_utils.GetWeekending(aid.expenditure_item_date)
      between :date_low and :date_high
  and aid.expenditure_organization_id = nvl(:incurred_org,aid.EXPENDITURE_ORGANIZATION_ID)
  and pv.vendor_id = ai.vendor_id
  and pv.vendor_type_lookup_code = 'EMPLOYEE'
  and pv.employee_id = per.person_id
  and org.organization_id = aid.expenditure_organization_id
  and aid.task_id = ta.task_id
  and pa.project_id = ta.project_id
  and et.expenditure_type = aid.expenditure_type
  and et.unit_of_measure = lk.lookup_code
  and ai.invoice_type_lookup_code = 'EXPENSE REPORT'
  and lk.lookup_type = 'UNIT'     
  and pa_addition_flag <> 'Y'
Parameter Name SQL text Validation
Week Ending Date From
 
Date
Week Ending Date To
 
Date
Employee Name
 
LOV Oracle
Incurred By Organization
 
LOV Oracle
Set of Books Currency
 
LOV Oracle
Include Routing History
 
LOV Oracle