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;

AME - Approval of CEO's Requisitions

Just following up on an AME Oracle thread, and providing a quicker solution when the CEO's requisitions need to be approved by someone else in the company (e.g. CFO)

Reference: https://communities.oracle.com/portal/server.pt/community/view_discussion_topic/216?threadid=115624


As a quicker solution, instead of creating a new package, you can update the seeded attribute "ALLOW_REQUESTOR_APPORVAL" with the following value:

select
decode ( tpid.query_string, prepid.preparer_id, 'true', decode (PO_AME_SETUP_PVT.can_preparer_approve(:transactionId),'Y', 'true', 'false'))
from (select query_string
from ame_attribute_usages aau, ame_attributes aa
where aa.attribute_id = aau.attribute_id
and aa.name = 'TOP_SUPERVISOR_PERSON_ID'
and sysdate between aa.start_date and aa.end_date
and sysdate between aau.start_date and aau.end_date
and aau.application_id = -184 ) tpid,
(select preparer_id from po_requisition_headers_all
where requisition_header_id = :transactionId) prepid

I need to admit I had some help from a colleague: thanks JB!

AME - Tax Calculated

Here's a workaround to an issue we encountered in AME configuration for Payables Invoice Approvals:

Background:
====================
The query is from the seeded attribute: SUPPLIER_INVOICE_TAX_CALCULATED

select AP_WORKFLOW_PKG.get_attribute_value(:transactionId, null, 'SUPPLIER_INVOICE_TAX_CALCULATED', 'header') from dual

In our case, we replace the :transactionID with an invoice that the tax was calculate (ID: 54077), and the result still returns 'N', we are expecting to return 'Y'


Problem Description:
====================
Invalid condition in the following: (It always returns 'N')

select AP_WORKFLOW_PKG.get_attribute_value(54077, null, 'SUPPLIER_INVOICE_TAX_CALCULATED', 'header') from dual

ELSIF p_attribute_name= 'SUPPLIER_INVOICE_TAX_CALCULATED' THEN

SELECT sum(decode(tax_already_calculated_flag, 'Y',
1, 0)), count(line_number)
INTO l_sum_calc, l_line_count
FROM ap_invoice_lines_all
WHERE invoice_id = p_invoice_id
AND line_type_lookup_code not in ('TAX','AWT');

IF l_sum_calc >0 and l_sum_matched = l_line_count THEN
l_return_val := 'Y';
ELSE
l_return_val := 'N';

Summary:
====================
In plain English terms, the l_sum_matched is not defined in the package at this point in the procedure and therefore always returns 'N'


Workaround:
====================
Update the seeded attribute with the following:

SELECT decode(sum(decode(tax_already_calculated_flag, 'Y',1, 0)),count(line_number),'Y','N')
FROM ap_invoice_lines_all
where invoice_id = :transactionId
and line_type_lookup_code <> 'TAX'

I would not be able to have done this all by myself, so I will not take full credit here - I have to say thank to my colleague here: Thanks JB (and not the Scotch JB)