Find query behind the Form or OA Framework page

FORMS

The simplest way to find a query in a form is to go to

Help -> Diagnostics -> Examine from the navigation toolbar

select SYSTEM block

and LAST_QUERY field

This approach has the disadvantage that it only shows the last query run by the form while there can be multiple queries run in different blocks.

To see a query in a block, first check the name of the block.

Now go to Help -> Diagnostics -> Item

Change the Object to 'Block'

and in 'Object1' select the name of the block.

The 'Value' field will automatically display the query that was run to retrieve the data in the block.

OA FRAMEWORK

The OA framework is not as flexible as Forms, but there is still a way to see the queries run by OA framework pages.

Forst set the prodile FND: Diagnostics to Yes for your user.

Now, when you log to any JSP page you will see 'About Page' in the bottom left corner.
Click on it and then click on 'Expand all' as on the picture below.

When you scroll down you will see the views on which the records are based

Click on the view name to see the query.

How to find a report

Quite often business users ask me on how to get some specific data from Oracle. Before I start sketching any functional design for a new report, I check the list of all concurrent requests in Oracle to see if there isn't already one available. When I do find it, it is difficult to determine the responsibility where it is assigned to. To work around this inconvenience I prepare the below query that lists all the reports containing a specific string in the name, their responsibilities and application. Quite often this makes life a bit easier.

select resp.responsibility_name,

pr.user_concurrent_program_name,

ex.executable_name,

ex.application_name

from FND_EXECUTABLES_FORM_V ex,

FND_CONCURRENT_PROGRAMS_VL pr,

FND_REQUEST_GROUP_UNITS rgu,

FND_REQUEST_GROUPS rg,

FND_RESPONSIBILITY_VL resp

where 1=1

and pr.executable_id=ex.executable_id

and rgu.request_group_id=rg.request_group_id

and rgu.request_unit_id=pr.concurrent_program_id

and resp.request_group_id=rg.request_group_id

and pr.user_concurrent_program_name like '%Invoice Audit%'

This will not work if the report is assigned to a menu function, rather than the responsibility.

How to create a price list based on item cost - Part II, Intercompany Price List in Oracle

You can find my presentation on this topic from 2015 OAUG here

In my previous post I described how to create a price list based on cost. Why would we want to have a price list based on cost? From my experience there are 2 common scenarios where this comes handy:

1. RMA price list for obsolete items that are no longer priced, but customers keep returning them.


2. For intercompany transactions where the transfer price between organizations is cost plus markup, and it would be cumbersome to have a manually maintained price list where all the costs are already defined.

The RMA situation is not that common, but all multinational companies have intercompany transfers. I have seen one company where the number of internal price list lines exceeded 180,000 while the regular sales price lists had only 42,000. They could no longer maintain it and had to write custom concurrent requests to do it for them.

The beauty of the below solution is that it requires no customizations and it will price all intercompany transactions based on ‘Cost + markup’ logic, with no maintenance required at all.
To begin with, I recommend creating separate responsibility for Intercompany pricing, to keep the IC and Sales price lists, modifiers, formulas, qualifiers and modifiers separate. This is not required, as alternatively you can set up the relevant profile options on a dedicated user level, separate responsibility is by far a cleaner solution.

Go to Application Developer -> Profiles and check the responsibility checkboxes for the below:

Now create your custom responsibility
(N) System Administrator -> Security -> Responsibility -> Define

Set the QP: Pricing Transaction Entity and QP: Source System Code profiles on the Responsibility Level.

Add the responsibility to your user. You have successfully set up your tools to begin working with Intercompany Pricing.
In addition to the above, set the following profiles on the site level

- CST: Transfer Pricing Option - Yes, Price As Incoming Cost (read more about other settings)
- INV: Advanced Pricing for Inter-Org Transfers - Yes
- INV:Advanced Pricing for Intercompany Invoice - Yes
- INV: Always suffix inter-Company AP Invoice number – Yes (optional)
- INV: Inter-Organization Currency Conversion – Corporate
- INV: Intercompany Currency Conversion - Corporate
- INV:Intercompany Invoice for Internal Orders – Yes (optional)

Set up shipping networks in inventory responsibility.
Log into the Intercompany Pricing responsibility -> Setup -> Attribute Management -> Attribute Linking and Mapping. Select Intercompany Transaction as the Pricing Entity and Pricing Context, then map the same COST attributes here as for the Order Fulfillment Entity in the previous post.

Map both pricing attributes using the PL/SQL logic, the same as in previous post.
For cost:

cst_cost_api.get_item_cost(1, INV_IC_ORDER_PUB.G_LINE.INVENTORY_ITEM_ID, INV_IC_ORDER_PUB.G_LINE.SHIP_FROM_ORG_ID,NULL,NULL)
Note that record OE_ORDER_PUB.G_LINE in Intercompany pricing entity is replaced with INV_IC_ORDER_PUB.G_LINE

For Exchange Rate:

gl_currency_api.get_rate(CSD_COST_ANALYSIS_UTIL.get_GLCurrencyCode(qa_moac_pkg.derive_ou_id(INV_IC_ORDER_PUB.G_LINE.SHIP_FROM_ORG_ID)),'USD', sysdate,'Corporate')

The 'USD' needs to be replaced with the currency of the intercompany transactions for your installation.

This will work on R12 as long as you don’t use Global Purchase Agreements nor have Oracle Process Manufacturing enabled for any of your organizations that do intercompany pricing. In those cases you will need to use additional logic, which I am omitting here to keep the case simple. As I mentioned in my previous post additional coding may be needed for 11i where the qa_moac_pkg.derive_ou_id function does not exist.
After you are done mapping run the Build Attribute Mapping Rules Concurrent request .
I think that all of the above should be seeded, but it isn’t. The enhancement requests to do so are sitting out on metalink for the last couple of years. On top of that the All Items Product Attribute is not mapped for the ‘Intercompany Transaction’ pricing entity.

Lets fix this obvious omission.

Press Link Attributes button.

Run Build Attribute Mapping request again.

Now that all the core setups are done lets create an Intercompany pricing formula.

Because this formula is created in the Intercompany Pricing responsibility, it is visible, but not updateable in the regular Oracle Pricing Manager or any OM responsibility With the formula ready we can create the Intercompany Price List.

Make sure to list all basic units of measure that can be transacted. In the above example I had 4.

Note the Pricing Transaction Entity for your price list. It will not be usable in Order Management or any other sales applications.

There is a bug in EBS, that Oracle Support did not fix since I first discovered it in 2006. While the sales applications residing in Order Fulfillment Pricing Entity do not see the Intercompany Transactions Entity, the opposite is not true and regular sales Price lists are visible in the Intercompany Transactions entity. To work around this problem assign precedence =1 to your intercompany price list lines.

There is another bug that prevents an Internal Order form being booked if the Intercompany price list is not assigned to the Internal Order type. To work around it assign it to the Internal Order type and also to the internal customer/customer site on the customer definition. From the logical perspective this is not really needed since the intercompany pricing is done by the Trip Stop interface which happens AFTER the order is booked and shipped and all the prices on the intercompany order are dummy values.

Now that you have your intercompany price list set up, you can create Internal Requisitions, Internal Sales Orders, ship them, create Intercompany AP and AR invoices and never worry about updating your intercompany price list.

The Global Purchase Agreement, transfers originating from multi node transactions, and different markups between different organizations, including logical nodes can be also set up to work, but this is a too long topic to cover in this post.

How to create a price list based on item cost - Part I

To create a price list that has prices of all items based on the item cost plus markup you could use the standard functionality to copy the costs into prices and create a price list that has a cost plus markup. While it works, it is not very flexible and as cost changes and new items are added the price list requires constant attention and manual updates.

Instead doing that you can create a price list that has only one line for ALL ITEMS and dynamically sources the current cost and applies markup at the moment of the actual transaction. This does not require any maintenance, other than updating the costs which is done by the costing department anyway. This approach consists of 3 steps:

1. Map the item cost as a pricing attribute
2. Use the pricing attribute to create a dynamic pricing formula that adds markup
3. Create ALL ITEMS price list and assign the pricing formula there.

1. Mapping the cost as a pricing attribute

I could utilize the standard Pricing Attribute context, but I recommend adding a separate one for better transparency. You can later build upon it and create additional attributes such as Material Cost, Overhead cost etc.

Setup -> Attribute Management -> Context and Attributes

Select a column for attribute mapping, i.e. PRICING_ATTRIBUTE1 and a numeric value set such as Number_15. Do not select the seeded ‘Number’ value set as it is not numeric.

Now that we have our new pricing context defined go to

Setup -> Attribute Management -> Context and Attributes

Now, map the attributes on line level only.

In the ‘Global Object Name’ field you can see the name of the record structure that has all the variables that can be used when deriving the price. In this case it is OE_ORDER_PUB.G_LINE

You can go to the package definition of OE_ORDER_PUB and see what fields the G_LINE record contains

Now, if I just typed OE_ORDER_PUB.G_LINE.UNIT_COST in the User Value String then my pricing attribute would return the unit cost and it would be usable. The problem with this approach is that it is limited to Order Management and the UNIT_COST field is not always populated even there.

Lets take a more generic approach that should work for any record type.

In my previous post I used the

cst_cost_api.get_item_cost(1,inventory_item_id, organization_id,NULL,NULL)

function to get the cost of any item.

I will paste this function directly into the ‘User Value String’ of the pricing attribute mapping. The inventory_item_id and organization_id need to be substituted by the OE_ORDER_PUB.G_LINE variables:

Nvl(cst_cost_api.get_item_cost(1, OE_ORDER_PUB.G_LINE.INVENTORY_ITEM_ID, OE_ORDER_PUB.G_LINE.SHIP_FROM_ORG_ID,NULL,NULL),0)

The NVL prevents the pricing engine to error out when no cost is defined. Depending on the situation, you may want it to error out.

This is what needs to be pasted into the ‘User Value String’ field.

Now, go to Tools and select the ‘Build Attribute Mapping’ to complete the definition

This would work fine, except that my shipping warehouse can be in USD and my order in EUR. Since the cost is always returned in the currency of the set of books of the warehouse the above expression needs to be multiplied by the exchange rate that you can also get from my previous post:

gl_currency_api.get_rate(from_currency_code,to_currency_code, conversion_date, Conversion_type)

For transparency it is better to source the exchange rate as a separate pricing attribute such as CONV_RATE and then multiply them in the pricing formula

Now, TO_CURRENCY_CODE is OE_ORDER_PUB.G_HDR.TRANSACTIONAL_CURR_CODE

Note that I took currency from the header record, which is also populated at runtime.

Conversion date is OE_ORDER_PUB.G_LINE. PRICING_DATE

And conversion type is fixed such as ‘Corporate’

The only remaining parameter is

from_currency_code

To find the operating unit of the shipping warehouse in R12 use the below function:

qa_moac_pkg.derive_ou_id(OE_ORDER_PUB.G_LINE.SHIP_FROM_ORG_ID).

Let me know if you find a relevant function in 11i. The ones I found are either not public, or they are not available to every installation, such as the Indian localization function jai_cmn_utils_pkg.get_operating_unit( 'anything', SHIP_FROM_ORG_ID). In 11i you may have end up writing your own function.

Now that the operating unit of the shipping warehouse is known, the CSD_COST_ANALYSIS_UTIL.get_GLCurrencyCode function can be used to derive the from_currency. The full expression is:

CSD_COST_ANALYSIS_UTIL.get_GLCurrencyCode(qa_moac_pkg.derive_ou_id(OE_ORDER_PUB.G_LINE.SHIP_FROM_ORG_ID))

The complete expression that finds the conversion rate between the shipping warehouse and the order operating unit is :

gl_currency_api.get_rate(

CSD_COST_ANALYSIS_UTIL.get_GLCurrencyCode(qa_moac_pkg.derive_ou_id(OE_ORDER_PUB.G_LINE.SHIP_FROM_ORG_ID)),

OE_ORDER_PUB.G_HDR.TRANSACTIONAL_CURR_CODE,

sysdate,

'Corporate')

Create and map the Conversion Rate Pricing Attribute the same way as Item Cost.

Now that have both item cost and the conversion rate defined we can build our Pricing Formula

2. Pricing Formula

Pricing Formula -> Formula setup

Go to Tools -> Build Formula Package

3. Price List

The Price list needs to be defined for all base units of measure.

Now the price list is ready to be used.