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.

EBS Product & Acronym Listing | ORACLE Technology Information

Do you often ask yourself what does 'AME' stand for? I do - here's an easy way to find out.
Navigate here for the EBS Product Acronym Listing.

How to extract the Chart of Account (COA) from Oracle

Here are a few useful SQL scripts used by GL consultants:

1) This SQL lists all GL Segment values:

select fvs.FLEX_VALUE_SET_NAME, ffv.FLEX_VALUE, ffvt.FLEX_VALUE_MEANING, ffvt.DESCRIPTION

,ffv.ENABLED_FLAG, ffv.SUMMARY_FLAG

,ffv.START_DATE_ACTIVE ,ffv.END_DATE_ACTIVE, ffv.HIERARCHY_LEVEL

,replace(ffv.COMPILED_VALUE_ATTRIBUTES,chr(10),'_') Budget_Post

from applsys.FND_FLEX_VALUES_TL ffvt, applsys.FND_FLEX_VALUES ffv, applsys.FND_FLEX_VALUE_SETS fvs,

applsys.FND_ID_FLEX_SEGMENTS fseg, applsys.FND_ID_FLEX_STRUCTURES fifs,

applsys.FND_ID_FLEXS fif, applsys.FND_APPLICATION_TL fat

where ffvt.LANGUAGE = 'US'

and ffvt.FLEX_VALUE_ID = ffv.FLEX_VALUE_ID

and ffv.FLEX_VALUE_SET_ID = fvs.FLEX_VALUE_SET_ID

and fvs.FLEX_VALUE_SET_ID = fseg.FLEX_VALUE_SET_ID

and fseg.APPLICATION_ID = fifs.APPLICATION_ID

and fseg.ID_FLEX_CODE = fifs.ID_FLEX_CODE

and fseg.ID_FLEX_NUM = fifs.ID_FLEX_NUM

and fifs.ID_FLEX_CODE = fif.ID_FLEX_CODE

and fifs.application_id = fif.application_id

and fif.application_id = fat.application_id

and fif.ID_FLEX_NAME = 'Accounting Flexfield'

and fat.APPLICATION_NAME = 'General Ledger'

and fat.LANGUAGE = 'US'

2) This SQL lists all code combinations created:

SELECT

gcc.code_combination_id

,gcc.segment1

,gcc.segment2

,gcc.segment3

,gcc.segment4

,gcc.segment5

,gcc.segment6

,gcc.segment7

,gcc.segment8

,SUBSTR(apps.gl_flexfields_pkg.get_description_sql( gcc.chart_of_accounts_id,1,gcc.segment1),1,40) segment1_desc

,SUBSTR(apps.gl_flexfields_pkg.get_description_sql( gcc.chart_of_accounts_id,2,gcc.segment2),1,40) segment2_desc

,DECODE(gcc.segment3,NULL,'',SUBSTR(apps.gl_flexfields_pkg.get_description_sql( gcc.chart_of_accounts_id,3,gcc.segment3),1,40)) segment3_desc

,DECODE(gcc.segment4,NULL,'',SUBSTR(apps.gl_flexfields_pkg.get_description_sql( gcc.chart_of_accounts_id,4,gcc.segment4),1,40)) segment4_desc

,DECODE(gcc.segment5,NULL,'',SUBSTR(apps.gl_flexfields_pkg.get_description_sql( gcc.chart_of_accounts_id,5,gcc.segment5),1,40)) segment5_desc

,DECODE(gcc.segment6,NULL,'',SUBSTR(apps.gl_flexfields_pkg.get_description_sql( gcc.chart_of_accounts_id,6,gcc.segment6),1,40)) segment6_desc

,DECODE(gcc.SEGMENT7,NULL,'',SUBSTR(apps.gl_flexfields_pkg.get_description_sql( gcc.chart_of_accounts_id,7,gcc.segment7),1,40)) segment7_desc

,DECODE(gcc.SEGMENT9,NULL,'',SUBSTR(apps.gl_flexfields_pkg.get_description_sql( gcc.chart_of_accounts_id,8,gcc.segment8),1,40)) segment8_desc

,gcc.chart_of_accounts_id chart_of_accounts_id

,gcc.account_type

FROM

gl_code_combinations gcc

-- For references visit my friend's blog here