Script to identify inactive supplier records

The following script can be used to identify the supplier records not used for a specific number of days:

SELECT aps.vendor_id, aps.vendor_name, aps.segment1, aps.vendor_type_lookup_code FROM ap_suppliers aps WHERE NOT EXISTS (SELECT DISTINCT vendor_id FROM (SELECT vendor_id FROM ap_invoices_all WHERE creation_date > (sysdate - p_days) AND org_id = UNION ALL SELECT vendor_id FROM po_headers_all WHERE creation_date > (sysdate - p_days) AND closed_code <> 'CLOSED') AND org_id = WHERE vendor_id = aps.vendor_id) AND aps.end_date_active IS NULL; 

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;

Adding a DFF to an OA Framework Page

iProcurement is used as an example in this blog.

Here are the steps to enable DFF's on OAF pages. You can do this for any OAF page in Oracle Apps R12.

Start by adding a DFF to Create Purchase Order page in Buyer Work Center. First we need to define the DFF. The steps are as follows:
1. Create a new Value Set.
2. Define Values for the value set created in step 1.
3. Add Value Set to the Flex Field Segment in Application: Purchasing and Title: PO Headers.
4. Freeze and Compile the FlexField definition.

Enable this DFF in Buyer Work Center. the steps are as follows:
1. Enable profile Personalize Self-Service Defn to yes at the user level.
2. Click Personalize Page at the top of the Create Purchase Order page.
3. Look for Flex: (HeaderRN.DescFlexfields)
4. Click Edit (Pencil)
5. Chang Rendered from false to true
6. Click Apply
7. Click Return to Application

One more example: Add DFF to iProcurement Requisition header in the same way. Define a DFF again:
1. Create a new Value Set.
2. Define Values for the value set created in step 1.
3. Add Value Set to the Flex Field Segment in Application: Purchasing and Title: ReqExpress Headers.
4. Freeze and Compile the FlexField definition.

Enable DFF in IProc HTML pages are as follows:
1. Enable profile Personalize Self-Service Defn to yes at the user level
2. Log in to iProcurement
3. Go to Checkout: Requisition Information page
4. Click Personalize Page at the top of the page
5. Look for Flex: (ReqHeaderDFF)
6. Click Edit (Pencil)
7. Change Rendered from false to true. Click Apply
8. Click Return to Application

Clearing Cache Mid Tiers

When you assign a responsibility to your user, and then try to access this responsibility, but it's a self service application, not a core application form.
You get the following message: "Internet Expenses is not a valid responsibility for the current user. Please contact your System Administrator"
Navigate here for step by step instructions with screen shots.

In summary, here are the steps:

1) Log in and select the "Functional Administrator" responsibility

2) Select "Core Services" (the tab at the top right)

3) Select "Caching Framework" (second option from the right on the blue bar)

4) Select "Global Configuration" (bottom option on the left)
This page shows you the currently configured Caching Statistics and Policy. The bit we're interested in though is the "Clear All Cache" button the right-hand side.

5) Click "Clear All Cache"
Read the message

6) Click "Yes"

And we're done, the user should now be able to log in with the new responsibility.