How to setup a defaulting DFF value of Default Type SQL Statement

If you need to repeat a value already part of the original transaction and automatically populate it in another area of the same transaction, in this case: the data is located on the AP Invoice header and it needs to be passed to the invoice line.

Then you need to create a DFF with a default type of 'SQL Statement' at the destination level and pass a bind variable into the 'where' clause of the select statement.

Here's an example: let's say there is a value (e.g. attribute4) in the AP invoice header that needs to be copied down to each AP invoice lines, you simply need to have the following default SQL statement for the DFF created at the invocie line level:

select aph.attribute4
from ap_invoice_lines_all apl, ap_invoices_all aph
where aph.invoice_id = apl.invoice_id
and apl.invoice_id = :INV_SUM_FOLDER.invoice_id

In order for the attribute4 from the invoice header to default automatically to the invoice lines, the last clause of the query above requires to pass a bind variable, in this case, it corresponds to ":INV_SUM_FOLDER.invoice_id"

To get the value above, Navigate to the form where the data is located, then extract the 'block'.'field' information by navigating to Help > Diagnostic > Examine

Prefix the combination of the 'block'.'field' with a " : "

My friend Cathy B. also pointed me to this excellent reference explaining Bind Variables: http://docs.oracle.com/cd/A60725_05/html/comnls/us/fnd/values19.htm

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)

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.