Another helpful query from my friend Jon B.

This query is useful when troubleshooting the WF mailer, and to see if there is an issue with it.

Select * From Wf_Notifications
Where Trunc(Begin_Date) > Trunc(Sysdate-3)
order by 1 desc;

When the WF Mailer issue gets resolved, all notifications with a FAILED or ERROR mail status can be resent using the following concurrent program under the System Administrator responsibility: Resend Failed/Error Workflow Notifications

How to set the context in R12

--sometimes you need to run the following:

ALTER SESSION SET NLS_LANGUAGE= 'AMERICAN';

--to set the context, provide the org_id in the SQL script below:
begin
mo_global.set_policy_context('S',"<"org_id">");
end;

--to fetch data for all operating units, and for a specific application
begin
mo_global.init('SQLAP');
end;

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)