[PO] Price Column at Purchase Order Lines and Requisition Lines

I have a PR, entered in foreign currency, e.g. JPY. My functional currency is USD. The PR was converted to PO successfully.

In standard view, at PR lines, there is a Price column. At PO lines, there is also a Price column. PR line's price column will show price in functional currency (e.g. USD), PO line's price column will show price in foreign currency that i entered (e.g. JPY).

[ALRT] Manually Trigger a Alert

How to manually trigger a Alert which has Frequency set as "On Demand"?

Ans: Under Alert Manager responsibility, Request -> Check, enter Application name, Alert name, Start Date and click "Submit Request".

[PO] Vacation Rules

You are a busy PR approver and plan to go vacation the following week. You do not have chance to approve PR during your absence. What you can do is to setup a Vacation Rulues so that you can delegate the task to your backup person. Your backup person will approve/reject on your behalf. Once the backup person performed the action, notification will flow as per original path.

Note that you can set the Start Date and End Date of your rule, and you can specify time as well.

[SYS] No Organizations Are Currently Defined

After setting a new Operating Unit (OU), I encountered this problem when i tried to open any forms that are related to Inventory Organization.

Steps I used in organization setup (has to be in sequential mode):
(1) Setup new Set of Book
(2) Setup new Location
(3) Setup new Legal Entity
(4) Setup new Operating Unit
(5) Setup new Inventory Organization
(6) Setup new responsibility
(7) Setup Profile Option for new responsibility

Everything is smooth till I click on the "Change Organization" in my new responsibility. It prompted me "No Organizations are currently defined.". These happened to other forms that use inventory organization as well, such as Material Transactions, Receipts, etc.

This has something to do with Organization Access. After checked Organization Access, ORG_ACCESS table is empty. Setup has been verified many times, nothing was missed out.

We finally discovered it was a customization on standard view, ORG_ACCESS_VIEW. This is root cause of the issue.

[SYS] Utilities:Diagnostics

To disable the password promting when i click "Examine", set the Profile Option to value "Yes" for Utilities:Diagnostics.

[INV] Copy Item Template

Setup -> Items -> Templates

First enter a new template name at Item Template form, then go Tool -> Copy Template. Select the template name that you want to copy from.

Note that you can't copy item template from 1 inventory org to another. You can only copy item template within the same inventory organization. If source Item Template has no organization tied to it, it can be copied to any organizations.

[OM] Cannot Perform Shipping Transactions

At Shipping Transactions form, Action combo box is disabled. Can't perform pick release, create delivery, etc.

Grant role to user at OM responsibility. At Shipping Execution Grants form, add record, enter user name & save.

Setup -> Shipping -> Grants and Role Definitions -> Grants

Default role is "Upgrade Role", which you can maintain it in

Setup -> Shipping -> Grants and Role Definitions -> Define Roles

[OM] Launch Pick Release Failed

My Sales Order (SO) has been booked successfully. Order header status is Booked, order line status is Awaiting Shipping. Quantity on-hand is sufficient. At Shipping Transactions form, I performed "Launch Pick Release", order line status remained Awaiting Shipping, it did not change to Picked.

No warning or error was prompted.

In my case, it was due to Credit Hold. Go Order Header -> Actions -> Additional Order Information, I saw Hold with message "Credit check hold applied. Overall limit exceeded.". GoActions -> Release Holds to release hold before proceed to Pick Release.

In order to perform Release Holds action, you need to be given authorities. Get the Holds Name and check the authorizations at Setup -> Orders -> Holds.

[OM] Where to Setup New Sales Order Type

Under Order Management responsibility, Setup -> Transactions Types -> Define

[SYS] Form Personalizations - Set Property Value for Descriptive Flexfield

Assuming you have have 3 flexfields for each line of record, but for setting the value for Target Object, there is only 1 Target Object available for 3 flexfields, e.g. {something}.DESCFLEX, how to set value for 3 of them?

Ans: At the Value field, enter something like this => Value1.Value2.Value3

Separate each value by a dot.

[SYS] Form Personalizations - Setting Property Value by Calling a SQL Query

Can be done.

Enter the value as below:

=(SELECT AGENT_ID FROM PO_HEADERS_ALL WHERE SEGMENT1=:RCV_TRANSACTION.PO_NUMBER)

:RCV_TRANSACTION.PO_NUMBER is a Target Object ("Item" Type) name, which you can refer after you clicked on "Insert Item Value..." button. It can also be found under Target ObjectLOV with Object Type "Item".

[SYS] Java Cache Caused Internet Explorer Crashed

I was trying to launch the Oracle form (in Java applet), IE crashed. I tried on Firefox, it did not get launched at all. Restarted my system, problem still there.

Before I decided to call Helpdesk to examine my system, I found a trick that can save me a lot of time dealing with Helpdesk.

Checked "Control Panel", "JInitiator 1.3.1.29" (your version might be different), "Cache" tab. My total file size in JAR Cache folder has exceeded the limit. I have then removed all cache files in that folder, increased my size limit, and it works!!!

[OM] Pick List Ship-To Address Not Get Updated

It is an address issue.

The Ship-to Address appears on the Pick List (for ISO) is retrieved from wsh_locations table. As for IR-ISO setup, ship-to-address comes from Customer address instead of Location address.

When we updates the customer address, hz_locations table get update. By right, bothhz_locations table and wsh_locations table should be synchronized automatically. But, in my case, it was not. Business event "oracle.apps.ar.hz.Location.update" is enabled, everything else is fine. But the synchronize is not happening.

Logged a tar and Oracle came back with suggestion to run "Import Shipping Location" program in OM responsibility to synchronize the table. These was their reply (copied from some another article).

"...The 'Import Shipping locations' concurrent program imports all of the internal and external
locations from HR/HZ LOCATIONS tables into the WSH_LOCATIONS tables.

This program should be executed at least once when upgrading to 11.5.9. After the
upgrade, it should not be required to rerun this concurrent program since any
changes in the HR/HZ LOCATIONS tables are automatically synced up with WSH
tables.

There are a few exceptions when Import Shipping Locations concurrent program should be run. These exceptions would be only when any updates to a
Customer's information does not get reflected when creating a new sales order
or when you find any inconsistencies in Location records when interfaced from
HR to WSH Locations, such as an incorrect country code..."

Question is the auto-sync is not happening and Oracle can't give reason why.

End up I have to run "Import Shipping Location" program to solve the issue (and it took 3.5 hours to complete with the Start-Date value = sysdate-4).

[SYS] Trick - Maximise Form

Due to "bad" design of Oracle Form UI, the grid information in Oracle form will not maximise eventhough I maximised the form. 1 typical example is Material Transaction form.

There a trick to "force" the grid to grow bigger, and then fit to the maximised form.

First, maximise the form.

Then, move the mouse in between 2 fields until a double-headed arrow appears.

Double Click.

You will see this magic works.

[AR] Customer - Types & Address

Oracle has 2 customer types, Person or Organization.

Person is mainly use for retails business whereas Organization is used for company that has company as target customer.

Customer is differenciated by Party Number and Customer Number. If Customer Type isPerson, Party Number refers to Person Number, if Customer Type is Organization, Party Number refers to Organization Number.

Party Number and Customer Number are cross OUs and organizations, this means the same customer just need to be defined once. In different OUs, the Party Number and Customer Number will be shared and it will be differenciated by Customer Address.

[OM] HL Pick Slip SRS Report -> Completed Warning

After Pick Release action, pick slip report will get submitted, but it often completed warning.

How to know if actual picking taken place? Picking could be failed or success even the program completed warning. E.g. For printing issue, even it completed warning, but the stock has been picked.

From Requests list, click on the request, view Output. If there is records there, it was successfully picked. Another faster way, observe the request in the list, if the 4th and 5th parameters have value, it is successful.

[WIP] Missing Item Code in LOV

In order to build an assembly item in WIP, the "Pick Components" check box should NOT be checked (Order Management tab in Item form).

If it is checked, it will not appear in the Assembly LOV when user tried to create a WIP discrete job. It will also not appear in Routing LOV. Anyway, it will still appears in BOM LOV.

[OM] Unable to Pick Release Internal Sales Order (ISO)

ISO was created from Internal Requisition (IR), order line status is "Awaiting Shipping", shipment line status is "Ready to Release", next step is "Pick Release". Performed Pick Release, shipment line status is not changed. As usual, no error shown in the log.

After investigation, found onhand no issue, inventory period is opened, what else could be the cause?

Ans: The ISO has tied to subinventory ABC, but the manual Pick Release is picked from subinventory DEF. So, change the "Pick From" subinventory to ABC, problem solved. Note: ISO subinventory is defaulted from IR subinventory.

[GL] Receiving Journal Transfer to GL by Journal Import

Found a good article on Receiving (Purchasing) Journal transfer to GL by Journal Import.

Related SQL:
SELECT *
FROM apps.gl_interface
WHERE user_je_source_name = 'Purchasing'
AND user_je_category_name = 'Receiving'

In R11i for Inventory Destination Transactions, the Receiving Transaction Processor Debits and Credits the Receiving Account and Inventory AP Accrual Account respectively in GL_INTERFACE and RCV_RECEIVING_SUB_LEDGER. These Transactions in GL_INTERFACE are identified with User_je_source_name ='Purchasing' and user_je_category_name 'Receiving'. In RCV_RECEIVING_SUB_LEDGER these transactions are identified by column accrual_method_flag ='O'
In R11i for Period End Transactions ie Transactions with accrue_on_receipt_flag ='N' in PO_DISTRIBUTIONS_ALL, no accounting transactions are generated on receipt of goods. The Receipt_Accrual_Period-End program generates the Accounting Entries in GL_INTERFACE and RCV_RECEIVING_SUB_LEDGER simultaneously by Debiting Charge Account and Crediting Expense AP Accrual Account. In GL_INTERFACE the Transactions are identified by column names user_je_source_name='Purchasing' and user_je_category_name ='Acccrual'. In RCV_RECEIVING_SUB_LEDGER the Transactions are identified by column Accrual_Method_flag ='P'.

In R11i both On Line Transactions and Period End Transactions are moved from GL_INTERFACE to GL_IMPORT_REFERENCES,GL_JE_HEADERS and GL_JE_LINES by JOURNAL IMPORT.

In R12 GL_INTEFACE TABLE is no longer directly used for Receiving Transactions. The Receiving Transaction Processor only populates the RCV_RECEIVING_SUB_LEDGER with Inventory Destination Transactions. The Receipt_Accrual_Period-End program also only populates the RCV_RECEIVING_SUB_LEDGER with Expense Destination Period End Accruals.

Create Accounting-Receiving Concurrent program from Cost Mangement Responsibility transfers the journals in RCV_RECEIVING_SUB_LEDGER to SLA Tables. Once the accounting entries are created in receiving subledger for the receiving transactions, Create Accounting process gets started depending on the profile option CST:Receiving Accounting option and the encumbrance set up.

On submitting this program, it spawns the Accounting program which will do the validations and picks the data from table rcv_receiving_sub_ledger and process it according to the accrual method set for the POs

Depending on the accrual method and the value for profile SLA:Disable journal import, this spawned program "Accounting program" inserts the data into gl_interface first and then calls the Journal import program and posting program and populates the accounting events in subledger table xla_ae_headers and entries in xla_ae_lines and xla_distribution_links.
If there is any problem with the Journal import, the data will get rolled back from gl_interface. Once the Journal import is completed, data will get populated in gl_import_references,gl_je_headers and gl_je_lines.

......

Refer to Metalink Note 827125.1 for details.

[WIP] Work Order is Canceled and Onhand Stock Not Released

Stock is reserved due to the allocation done during WIP Component Pick Release process. Even job is canceled, the allocation will not be released. Hence, the onhand is still not available for other transactions' use.

In order to increase the availability, we need to remove the job allocation.

Go to "Transact Move Orders" menu, go to "Pick Wave" tab, "Work Order" section, enter the job number, query the job. Cancel allocation at Tools->Cancel Allocations

[OM] Meaning of wsh_new_deliveries.released_status

Meaning of wsh_new_deliveries.released_status field value, reproduced from Document406189.1.

B Backordered Line failed to be allocated in Inventory
C Shipped Line has been shipped
D Cancelled Line is Cancelled
N Not ready for release Line is not ready to be released
R Ready to release Line is ready to be released
S Released to Warehouse Line has been released to Inventory for processing
X Not Applicable Line is not applicable for Pick Release
Y Staged Line has been picked and staged by Inventory

[OM] Order Shipping Line Status Stuck in "Shipped", Next Step "Run Interfaces"

Obviously "Interface Trip Stop" program has been ran, but status is not change. What could be the reason behind?

Check error records in Transaction Open Interface (Transactions->Transaction Open Interface). Any error stuck with error in the interface will cause this issue.

Table:
mtl_transactions_interface

Fields:
process_flag => 3, if error
organization_id => Inventory Organization
error_code

[OM] OE_ORDER_HEADERS_ALL -> SOLD_TO_ORG_ID and SHIP_TO_ORG_ID

At the Order Header table, SOLD_TO_ORG_ID refers to customer_id whereas SHIP_TO_ORG_ID is the ORGANIZATION_ID from OE_SHIP_TO_ORGS_V. From the SHIP_TO_ORG_ID field, we can trace back the actual ship-to address for a particular order.

Before we can use OE_SHIP_TO_ORGS_V view in sql, we have to first initialize the view. However, we can skip it by going direct to the tables.

--to get actual ship to address for a Sales Order
select hl.*
from
apps.hz_cust_site_uses_all cs,
apps.hz_cust_acct_sites_all cas,
apps.hz_party_sites ps,
apps.hz_locations hl
where
hl.location_id=ps.location_id and
ps.party_site_id=cas.party_site_id and
cas.cust_acct_site_id=cs.cust_acct_site_id and
hl.country='IN' and
hl.state='JAMMU AND KASHMIR' and
cs.site_use_code='SHIP_TO' and
cs.org_id=123
order by cs.site_use_id desc

--to get order base on ship to address
-- cs.site_use_id equals oe_order_headers_all.ship_to_org_id
select * from apps.oe_order_headers_all where ship_to_org_id in (
select cs.site_use_id
from
apps.hz_cust_site_uses_all cs,
apps.hz_cust_acct_sites_all cas,
apps.hz_party_sites ps,
apps.hz_locations hl
where
hl.location_id=ps.location_id and
ps.party_site_id=cas.party_site_id and
cas.cust_acct_site_id=cs.cust_acct_site_id and
hl.country='IN' and
hl.state='JAMMU AND KASHMIR' and
cs.site_use_code='SHIP_TO' and
cs.org_id=123 )
and order_type_id=2345

[SYS] Concurrent Program Always in Pending Status

Some times we found out a submitted request is always in Pending status, we need to know if the manager is running fine. To determine which concurrent manager is handling a specific concurrent program, run this,

SELECT user_concurrent_program_name, user_concurrent_queue_name
FROM apps.fnd_concurrent_programs_tl cp,
apps.fnd_concurrent_queue_content cqc,
apps.fnd_concurrent_queues_tl cq
WHERE cqc.type_application_id(+) = cp.application_id
AND cqc.type_id(+) = cp.concurrent_program_id
AND cqc.type_code(+) = 'P'
AND cqc.include_flag(+) = 'I'
AND cp.LANGUAGE = 'US'
AND cp.user_concurrent_program_name = '&USER_CONCURRENT_PROGRAM_NAME'
AND NVL (cqc.concurrent_queue_id, 0) = cq.concurrent_queue_id
AND NVL (cqc.queue_application_id, 0) = cq.application_id
AND cq.LANGUAGE = 'US'

[GL] Manual Journal Import Needed

A case study:
User submitted "General Ledger Transfer Program" from AR module, "Journal Import" will be called internally. What happened is "Journal Import"completed error with error, "ORA-01653: unable to extend table GL.GL_IMPORT_REFERENCES by 16 in tab".

GL journal batch is not created.

After DBA fixed the tablespace issue, user reran "General Ledger Transfer Program" as the program says no records to be interfaced, completed warning.

What to do next: Go GL module, Journals->Import->Run, submit journal import with the right Group ID (can be retrieved from "General Ledger Transfer Program" log).

[SYS] Default Type: Profile

We have different value in Default Type in concurrent program parameter, Constant, Current Date, etc. I want to share about "Profile" in this post.

For Default Type Profile, the Default Value indicates Profile Name (In Application Developer responsiblity, Name field in Profile). Note that the User Profile Name is what we see in the System Administrator responsibility.

The value which returns to the concurrent program is the value for the Profile Name, either user, responsibility, or other levels.

SQL:

SELECT profile_option_name, user_profile_option_name
FROM apps.fnd_profile_options_vl
WHERE profile_option_name = 'MY_CUSTOM_PROFILE_NAME'

profile_option_name is referring to Profile Name, user_profile_option_name is referring to User Profile Name.

[INV] Item Cross References in Item Search

We have setup different Cross Reference Types for OM usage. Each type was defined in different inventory organization.

Now, in Production instance I do not have access to view the assignment (Items->Cross References was excluded). How can I check it?

Front End:

Use Item Search (Items->Item Search). Enter the inventory org, make sure the Show value is Cross References. Enter the Cross Reference Type andValue, click Find. We can see the assignment in the next screen.

Back End:

select cross_reference_type type, cross_reference selling_sku,
inv_item_concat_segs stocking_sku, description
from apps.MTL_CROSS_REFERENCES_V where cross_reference_type='ABC_XREF' and
cross_reference in
('ABCD');

[OM] Validation failed for the field - Ship To

Encountered this error during Order Copy (at header level).

Root Cause:

Customer Site Location is deactivated.

Fix:

Activate it. Customer->Standard. Choose the correct address base on the ship to address shown in the sale order, click "Open". Identify "Ship To" underUsage column, check the Active checkbox.

Some useful backend table for these:

Take oe_order_headers_all.ship_to_org_id to hz_cust_site_uses_all.site_use_id, hz_cust_site_uses_all.location field refers Customer Site Location.

We can get the Customer Site Number by using

select ps.party_site_number
from
apps.hz_cust_site_uses_all cs,
apps.hz_cust_acct_sites_all cas,
apps.hz_party_sites ps,
apps.hz_locations hl
where
hl.location_id=ps.location_id and
ps.party_site_id=cas.party_site_id and
cas.cust_acct_site_id=cs.cust_acct_site_id and
cs.site_use_code='SHIP_TO' and
cs.org_id=123 and
cs.site_use_id = 12345678

[INV] APP-PO-14736: You cannot source from and deliver to the same subinventories

IR Creation

I have Requisitions Preferences as follows.

Destination Type: Inventory

Organization: A1

Location: A1Location

Subinventory: A1Sub

Source: Inventory

Organization: A2

Click Apply. Exit Requisitions form.

Open Requisitions form, select Type as Internal Requisition, tab. The above Preferences will be defaulted.

Enter the item code at line level, APP-PO-14736 error prompted, my source organization is overwritten to A1, source subinventory has the value of A1Sub. Why?

Checked Item and Item Transaction Defaults, nothing special was found. Finally, drilled down to Subinventories level. For A1Sub subinventory, there existItem Subinventories link. For the item code i entered, the Sourcing Organization is A1 and Subinventory is A1Sub. These value get defaulted to sourcing org and subinv after item code was entered (with field exit) in Requisitions form. As the result, sourcing subinv and deliver-to subinv is the same, which leads to above error.

Solution:

• Remove this Item Subinventories defaulting, or
• Manual correct the sourcing org and subinv at Requisitions form level, or
• Change to another Destination Subinventory

[SYS] FND: Default Request Days

"FND: Default Request Days" profile decides the default value shown in "Select the Number of Days to View" field after you choose View->Requests

[SYS] PHASE_CODE and STATUS_CODE in FND_CONCURRENT_REQUESTS Table

It has been a bit confusing, hence i summarized the fields' values and meaning as follows:

PHASE_CODE
P - Pending
C - Completed
R - Running

STATUS_CODE
Q - Standby //Scheduled
R - Running
C - Normal
D - Cancelled
E - Error
G - Warning
I - Normal //Inactive

[OM] Query Manager for Shipping Transactions Form

Org Code is not defaulted to the OU's inventory organization, but it is defaulted to another inventory organization which has no relationship with current OU. Somemore, Org Code field is disabled. Why?

Ans: Setup->Shipping->Grants and Role Definitions->Grants. Grant to your Oracle user id, the Org Code will be enabled with blank. Set the Org Code from there (the field is optional).

[OM] Shipping Method - Lookups

To look at the complete list of Shipping Method, you need to go to Setup->Organizations->Shipping Methods in Inventory responsibility. It is a System Lookups, type: SHIP_METHOD.You can add/modify/remove Shipping Method there.

[OM] Picked Quantity Auto Transferred Back After Backordered?

Picked quantity (Pick Release process) will NOT be transferred back automatically to original locator/subinventory after Backordered transaction.

[SYS] Best Practise - To Update a Record Value in Setup Form

Read a documentation, it mentioned the best practise to update a record value during Oracle setup (at form level). Instead of bulk query and update 1 record, query the specific record, update it.

You know why? This is to prevent potential bug on the form that will update all records even you update 1.

[SYS] Where is Value Set Used?

Value Set in Oracle stores set of values, so that it can be used in elsewhere, in Descriptive Flexfield Segments, Key Flexfield Segments and even Concurrent Program Parameters.How do we know the where-used?

Ans: Navigate to Application->Validation->Set, open the form, query the value set, click "Usages" button.

[INV] Where did the Onhand Quantity Gone?

Onhand shows 12400, Available is 9400, no reservation for this item, where did the 3000 qty gone?

Ans: Check Pending Transactions

[SYS] Default Value for Organization Code

To set default value for Organization Code in the concurrent program parameter, use this.

select organization_code from org_organization_definitions where organization_id=:$PROFILES$.mfg_organization_id

[OM] 1 Delivery Has Many Delivery Lines, How to Backorder Only 1 Delivery Line

At Shipping Transactions form, query the delivery line by order number and item code (assuming this item has only 1 delivery line), enter the Shipped Qty as 0, Backordered Qty as the total quantity at the delivery line.Click Delivery tab, select "Ship Entered Quantities", press OK. If a delivery has multiple lines, only the selected line will be backordered.

[OM] Setting Up Internal Customer for IR-ISO

How does customer get recognized during Internal Sales Order (ISO) creation?

Ans: Location. In IR, there is a Ship-to-Location, and this internal location must tie to the same location defined in Customer Address (Customers->Addresses->Location->Internal Location). Each unique location can only assigned to only 1 customer and 1 address.Table for customer address and location: RA_SITE_USES_ALL

[OM] Auto Allocation in Transact Move Order for a Sales Order

If the SO shipment status is now "Released to Warehouse", we need to perform Transact Move Orders. For the Move Order, which setup trigger auto allocation?

Ans: In the Release Rules form, Inventory tab, Auto Allocate selection (Yes/No)

[INV] Item Reservation History Report

Is there such a report in OM or Inventory? Ans: No. It is categorized an enhancement request. Refer Note 842548.1

~OracleA2Z~: This is correct as of Jul 31, 2009.

[SYS] Hard to Find the Flexfield Title and Application?

We need to trace back and check the flexfield setup, some time it's a bit difficult to guess the title name.Click on the record, go menu Help->Diagnostics->Examine, change the Block value to "$DESCRIPTIVE_FLEXFIELD$", check the field Value. E.g If it shows "Flexfield Segment Values (Application Object Library)", means the Descriptive Flexfield Segments Title is "Flexfield Segment Values"