Thursday, September 13, 2012

Oracle iExpense Report Tables with Policy Violation Details

Below are queries for Oracle iExpense to find out the Policy violation detail for iExpense reports.

--Expense Report Details
SELECT attribute_category,org_id
FROM   apps.ap_expense_report_headers_all a
WHERE  a.invoice_num = <Expense report number>

FROM   apps.ap_expense_report_lines_all
WHERE  report_header_id =<Report Header id>

FROM   apps.ap_exp_report_dists_all
WHERE  report_header_id =<Report Header id>

--Fetch Invoice corresponding for Expense report
FROM   apps.ap_invoices_all aia
WHERE  aia.invoice_num =  <Expense Report Number>

--Parameters for a Specific Expense Template used to fetch the Policy line id for Expense Line
SELECT expense_report_id
FROM   apps.ap_expense_reports_all
WHERE  report_type = <attribute_category from Expense header table>
AND    org_id = <org_id from Expense Header table>

FROM   apps.ap_expense_report_params_all aerp
WHERE  expense_report_id = <id from above query>
AND    prompt = <item_description of expense line>

--Policy Header and details
WHERE  policy_id = <POLICY ID>

WHERE  policy_id = <POLICY ID>

--Policy Violations for Expense Report header and Line
FROM   apps.ap_pol_violations_all apv
WHERE  apv.report_header_id =< report header id>
AND    apv.distribution_line_number = <dist line id>

--Table of Policy violations without duplicate violation enteries used for accurate counts
(SELECT report_header_id, distribution_line_number, violation_type, MAX(VIOLATION_NUMBER)
 FROM   apps.ap_pol_violations_all
 GROUP BY report_header_id, distribution_line_number, violation_type)

--Find the Employee/Non Employee attendees for a Expense report line
FROM   oie_attendees_all oie
WHERE  report_line_id = <Report line id>
AND    oie.employee_flag = 'N'

--Non Employee
FROM   oie_attendees_all oie
WHERE  report_line_id = <Report line id>
AND    oie.employee_flag = 'Y'

at erp, erp, oracleapps, oracleapps erp, oracle erp, oracle ebusiness, oracle application,   
about oracle, oracle developer, oracle jobs, finance software, finance accounting accounting, r12 oracle, what is oracle database, oracle database, Oracle 11g, 11g database, oracle iexpense


rajeev said...

Oracle Apps R12 and Fusion Cloud Self Paced Training Videos by Industry Experts. Please Check

Amelia said...

Great blog, good information is given about Expense software .It was worth reading.Thanks Expense Report Software | Expense Tracker

Smith said...

Oracle Apps R12 and Oracle Fusion Cloud Self Paced Online Training Videos Published on Udemy with Life Time Access & Live Meeting Support to Clear your Queries. Avail 25% to 80% discount. Please Check for Never Before Offers and Discount Coupon Codes.

Rainbow Training Institute said...

Good Blog. Thanks for sharing this information and helpful for me

Oracle Fusion HCM Online Training
Oracle Fusion SCM Online Training
Oracle Fusion Financials Online Training
Oracle Integration Cloud Online Training
Oracle Fusion Technical Online Training