Pre AME rollout - HR sanity check scripts

Prior to rolling out AME for iProcurement and/or Internet Expenses, it is important to ensure the HR hierarchy is clean with no breaks or holes in it.

Here's an example of a script to help identify corrupt data in the HR hierarchy (e.g. direct reports with no supervisors, missing default expense accounts, jobs (positions), office location, email address)


select distinct ppf.full_name empl_name
from per_all_assignments_f paa,
     per_people_f ppf,
     fnd_user f
where paa.person_id = ppf.person_id
  and f.end_date is null
  and ppf.person_id = f.employee_id
and paa.supervisor_id is null --with missing supervisor record
and sysdate BETWEEN paa.effective_start_date AND paa.effective_end_date
and paa.primary_flag = 'Y'
union
select distinct ppf.full_name empl_name
from per_all_assignments_f paa,
     per_people_f ppf,
     fnd_user f
where paa.person_id = ppf.person_id
  and f.end_date is null
  and ppf.person_id = f.employee_id
and paa.default_code_comb_id is null --with missing default expense account
and sysdate BETWEEN paa.effective_start_date AND paa.effective_end_date
and paa.primary_flag = 'Y'
union
select distinct ppf.full_name empl_name
from per_all_assignments_f paa,
     per_people_f ppf,
     fnd_user f
where paa.person_id = ppf.person_id
  and f.end_date is null
  and ppf.person_id = f.employee_id
and paa.job_id is null  --with missing job (or position, for fetching the DOA)
and sysdate BETWEEN paa.effective_start_date AND paa.effective_end_date
and paa.primary_flag = 'Y'
union
select distinct ppf.full_name empl_name
from per_all_assignments_f paa,
     per_people_f ppf,
     fnd_user f
where paa.person_id = ppf.person_id
  and f.end_date is null
  and ppf.person_id = f.employee_id
and paa.location_id is null  --with missing location (used to create supplier records of Employees with OIE)
and sysdate BETWEEN paa.effective_start_date AND paa.effective_end_date
and paa.primary_flag = 'Y'
union
select distinct ppf.full_name empl_name
from per_all_assignments_f paa,
     per_people_f ppf,
     fnd_user f
where paa.person_id = ppf.person_id
  and f.end_date is null
  and ppf.person_id = f.employee_id
and ppf.email_address is null  --with missing email address (to send email notifications)
and sysdate BETWEEN paa.effective_start_date AND paa.effective_end_date
and paa.primary_flag = 'Y'
order by 1;

Review of OIE processes, expense items, expense report templates

Here's a script that extracts all expense report templates and expense items with accounting information, the extract should be reviewed by the Finance and/or Accounts Payable team(s) on a regular basis and make changes when required:

select decode (exi.org_id,1,'',2,'org name 2',3,'org name 3',4,'org name 4') organisation,
ext.report_type template_name, exi.prompt, exi.vat_code, category_code, exi.flex_concactenated, exi.flex_description, exi.creation_date
from AP_EXPENSE_REPORT_PARAMS_ALL exi,
ap_expense_reports_all ext
where ext.org_id = exi.org_id
and ext.expense_report_id = exi.expense_report_id
and ext.inactive_date is null
and ext.web_enabled_flag = 'Y'
and exi.end_date is null
order by 1, 2, 3;

Some tech tips and links

Where are the .wft files located, like APEXP.wft ?

WFLOAD - what does this command do in PUTTY?
Have a read of this blog entry here.

More to come - keep watching !

Tax Codes not enabled for Internet Expenses

Try the following SQL scripts to help you identify if the setup of tax codes were correctly done or upgraded:

--Query 1
SELECT lookup_code, meaning, description
FROM zx_input_classifications_v
WHERE lookup_type = 'ZX_INPUT_CLASSIFICATIONS'
AND org_id =
AND enabled_flag = 'Y'

--Query 2
SELECT lookup_code, meaning, description
FROM zx_input_classifications_v
WHERE lookup_type = 'ZX_WEB_EXP_TAX_CLASSIFICATIONS'
AND org_id =  
AND enabled_flag = 'Y'

If the tax code appears in both queries, then the tax code should appear in the Internet Expenses module, when the tax code only appears in query 1 above, then you know there is an issue with the tax code.

To fix this issue, follow the additional setups identified in Oracle note 1312692.1 : Payables - Upgrade - Tax Codes not Enabled for Internet Expenses OIE

Oracle Profile Options

Here are a few profile options that I use often in an Oracle implementation.

'Site Name:' and 'Java Color Scheme'
These are used for distinguishing Oracle eBusiness Suite environments - the name gets displayed on top of the "Form" Window. Make sure you share this with your favourite DBA's as they should update this profile option every time they clone environments.

Utilities Diagnostics:
In case you don't get along with your DBA, and you don't know the APPS password, this is an excellent tool for troubleshooting issues raised in Oracle. You can still examine fields via the Help > Diagnostics > Examine menu path when this profile option is set to 'Yes' at your user level. Some DBA's also use this profile option to control the display of the diagnostics menu, by setting up the following profile option: 'Hide Diagnostics menu entry'.

Search for all profile options starting with "OIE:%":
When setting up Internet Expenses, make sure you review these profile options and what each of them are used for, even before the scoping phase of a project starts, this will definately help you with your implementation of the iExpense module, and have a better understanding of the functionalities available in this module.

Please send me your profile options - so we can build a little library of tips and tricks.