[GTM] R12: Awaiting Export Screening

At Sales Order line, after order booked, you see the line status became "Awaiting Export Screening" and not "Awaiting Shipping". Why?

It is caused by a extra Oracle module that we have integration with, GLOBAL TRADE MANAGEMENT (GTM). GTM will do the screening and feedback to EBS that whether this order line has passed the screening rule. If Yes, order line status will changed to "Awaiting Shipping". If Not, order line status will remained as "Awaiting Export Screening", and as a result, you cannot proceed with Pick Release.

[QP] R12: BACK_CALCULATION_ERROR: QP_PREQ_PUB.BACK_CAL: NO MANUAL ADJ

BACK_CALCULATION_ERROR: QP_PREQ_PUB.BACK_CAL: NO MANUAL ADJ error after you tried to override the price defaulted from Price List.


Fix => Define Discount Modifier, set it to Manual, Modifier Type = Discount, Override flag = Yes. Assign it to the right Qualifier.

[OM] R12: Sales Agreement Activation Date

During creation of Sales Order, we can tie the Sales Order to a Sales Agreement. Sales Agreement pre-stores information like Customer, Accounting, Pricing, etc.

We can controls how the Sales Agreement get applied (similar to functionality of Qualifier in Price List). At the Main tab, we can defined Item Context, which controls how the Sales Agreement take effects. At the Pricing tab, it can links to Price List, which will get defaulted when Sales Agreement is selected.

Note:
Activation Date in Sales Agreement, it is the effective start date (OE_BLANKET_LINES_ALL.START_DATE_ACTIVE), it ties to Request Date at the order line.

[SYS] R12: Enable Audit Trail

Step to enable AuditTrail:

(1) AuditTrail->Install
ONT Oracle Order Management Account

(2) AuditTrail->Groups
Application Name: Order Management
Audit Group: XX_ABC_GROUP
Group State: Enabled
...
User Table Name: OE_ORDER_LINES_ALL

(3) AuditTrail->Tables
User Table Name: OE_ORDER_LINES_ALL
Table Name: OE_ORDER_LINES_ALL
Application: Order Management
Column: UNIT_SELLING_PRICE, etc

After completed above steps, run "AuditTrail Update Tables". It will then add additional columns into the table and create a new trigger on it.

Schedule "Audit History Consolidator" to collect the audit data.

[PO] R12: Sales Order Reference in PR Interface Table

Drop Shipment, a booked Sales Order will trigger interface records into PO_REQUISITIONS_INTERFACE_ALL table.

How to trace back the original SO number from this table?

Get GROUP_CODE, which is reference back to the Sales Order header_id.

[QP] R12: Modifier: New Price

We can apply a modifier that totally replace the Unit Selling Price in R12.

Original List Price and Unit Selling Price is $10, a qualified modifier with value of $20. When this modifier applied, we will get Unit Selling Price of $20.

Oracle Pricing Manager
Modifier->Modifier Setup->Modifiers Summary->Override=Yes
Modifier->Modifier Setup->Discount/Charges->Modifier Type=Discount
Modifier->Modifier Setup->Discount/Charges->Application Method=New Price

[OM] R12: Intercompany Pricing

After enabling Advance Pricing for Intercompany, we can have intercompany AR invoice price different from trade invoice price.

e.g.
A & B represent 2 different OUs within same organization.
A sells to B.
B sells to external customer.

B is defined as internal customer for A. A will issue Intercompany AR invoice to B.

We can define interco price list to be tied to the internal customer B.
Customer -> Address -> Business Purposes -> Bill To -> Order Management -> Price List

[AR] R12: Customer Name Search

In the Standard Customer form, it is now in OA page (Web Page), no more Oracle form.

For Customer Name Search, it matches the similarity of the word you entered and actual data. However, the intelligence is very basic and limited, do not expect too much.

E.g.

Search name: JUPITESS

It can returns Actual name: JUPITERS

[SYS] R12: Browser Crashed When Both R11i and R12 Runs Concurrently

Browser: IE7

I opened R12 instance. Then followed by launching R11i applet in the same machine, browser crashed. Both R11i and R12 can't be opened at the same time.

Issue fixed after I turn off "Proxy Server" in IE.

Tools->Internet Options->Connections->LAN settings

[SYS] R12: Profile Option, Enable Advance Pricing for Intercompany Invoice

Set "INV:Advanced Pricing for Intercompany Invoice" to Yes.

Note that additional license needs to be purchase from Oracle.

[SYS] R12: Password

In R12, password is case sensitive.

[SYS] Given a List of Records (varchar) Returned From a SQL Statement, Which is Ordered First, Which is Ordered Last

Given below SQL,

SELECT

category_concat_segs

FROM apps.mtl_categories_v

WHERE structure_name LIKE 'Purchasing%'

AND disable_date IS NULL

AND category_concat_segs LIKE 'ABC.DEF%'

Return results were,

ABC.DEF1

ABC.DEF2

ABC.DEF3

ABC.DEF4

ABC.DEF5

I want to get only 'ABC.DEF1' & 'ABC.DEF5' from a single SQL statement. These will be used for my Item Category approval rule setup in Approval Group, low range and high range respectively.

Revised SQL below:

SELECT MIN(category_concat_segs), MAX(category_concat_segs)

FROM apps.mtl_categories_v

WHERE structure_name LIKE 'Purchasing%'

AND disable_date IS NULL

AND category_concat_segs LIKE 'ABC.DEF%'

[BOM] How Many Levels of Bills You Defined?

Sample Bills of Material (BOM) structure:

A

|

B

|

C D

Often, this is the frequent asked question. Take above example, Oracle says it has 2 levels, not 3.

[BOM] Delete Groups

I was trying to delete a component from a Bill. When i clicked on Delete button, "Delete Component" popped up and prompted me to enter " Name" and "Description", a button named "New Group" is situated next to the "Name" field. How can i proceed from there?

"Delete Groups", a control built in in BOM module to trace what and when a record has been deleted. In short, each deletion is traceable by the Delete Group name.

To proceed with the deletion, click "New Group", enter the Name (mandatory) and Description (optional), save it. Then, go back to the Navigator, Delete Groups, query out the record by Group Name. On the main screen, you can see your parent item. Click on "Components " button, you can see the component that you want to delete, with status "Pending". There are 2 buttons, namely "Delete Group" and "Check Group ".

As the name spelt, "Check Group" is to check the integrity of your Delete Group, concurrent program "Delete Item Information" will be submitted once you click on that button. View Output, you will see a "Success" message if the check is OK. Component status will change to "Check ok" after that. NO deletion will be performed.

You can skip "Check Group" and proceed direct to "Delete Group". Once clicked, the same concurrent program will be submitted and this time deletion will be performed (different parameter from " Check Group"). Component status will be changed to "Deleted".

The same procedure applied to routing component deletion.

[PO] What Are The Steps Required To Setup An Internal Requisition In Purchasing?

Excellant note. A simple and easy-to-understanding guide from Oracle Support, Note:406312.1.

A quick glance and I leant something new, Receiving for IR can be automated if we set the Receipt Routing to "Direct" in Shipping Networks .

[SYS] Security Rule - Block From Entering, What About Viewing?

You can enter only certain range of Cost Center once you set Security Rule up, but you CAN still view the document (e.g. PR Summary) that was created using a Cost Center that is out of you range in Security Rule.

[PO] Position Hierarchy Used for PR/PO Approval

A PR/PO was submitted for approval, how to check which Position Hierarchy user used? From Apps front end, it is impossible. Check it out from backend tables.

Sample codes:

SELECT approval_path_id FROM apps.PO_ACTION_HISTORY WHERE object_id in (SELECT requisition_header_id FROM apps.PO_REQUISITION_HEADERS_ALL WHERE segment1='myPRNumber')

Link approval_path_id to HR.PER_POSITION_STRUCTURES table to get hierarchy name used.

[PO] Receiving for PO with Status "Requires Reapproval"

Standard Purchase Order with 3 lines. I changed line 1 quantity. Header status changed to "Requires Reapproval". In normal scenario, I cannot do anymore Receiving activity as the status is not Approved. If my changes involved price and currency, receiving activity will generate accounting entries with wrong amount. A control is needed.

However, for a particular PO, I am able to perform receiving. The PO with "Requires Reapproval" appeared in the LOV when i tried to perform receiving. I can proceed further to next screen. However, the line i changed (line 1) is not appearing. Only the lines that i did not change appeared and allow me to do receiving. Why?

The reason being the PO is an consigned PO, which has consigned item (doubled check it from Shipment). System allows receiving for the unchanged line because receiving activity for consigned PO will NOT generate any accounting entries, no impacts. Furthermore, transaction price does not refer to PO, but BPA.

[SYS] Login Page Filled with User ID Automatically

For normal scenario, you type in the URL for Oracle Apps instance, click Apps Logon Links, then E-Business Home Page, enter your Username andPassword, you will see the HTML (jsp) menus.

To simplify the steps, you first login to HTML menus, click logout, copy the URL from the Address field in your browser. That is the address you use to access the login page, with your Username defaulted.

[PO] Exchange Rate for A New Line

I have a PO create months back, exchange rate was captured at the header level. If i add a new po line now, previous captured rate will be applied to the line, shipment and distribution, not today's rate.

Double check the exchange rate on Distributions line, under "More" tab.

[PO] Owner Can Approve

Whether a preparer can approve the document, e.g. PR/PO depends on setup in Setup -> Purchasing -> Document Types, if "Owner Can Approve" flag is check, preparer can approve by himself. Means, preparer just need to click "Approve... " and "OK" button, the document will be approved (assume approval limit is satisfied).

However, if preparer wants to use hierarchy other than default hierarchy, he/she needs to specified the "Forward To" person to himself. PO will be first changed to " Pre-Approved" status before it get approved eventually.

[INV] FRM-40200: Field is protected against update

Tried to query consigned transactions in "Find Material Transactions" form. Choose "Consumption Advice " with value "Error", above error encountered.

"Consumption Advice" field is not selectable, unless Transaction Types field has values " Transfer to Consigned" or "Transfer to Regular".

[PO] Position with Status Blank

Create a new position, leave the Status field blank, save. Oracle considers it as Valid or Invalid. Ans: Valid

[WIP] Main Tables for Work In Process (WIP) Job

WIP_DISCRETE_JOBS_V - work order main info

WIP_REQUIREMENT_OPERATIONS - material and resource requirements

Sample codes:

SELECT MSI.SEGMENT1 AS "PART NO", WDJ.WIP_ENTITY_NAME, WRO.WIP_ENTITY_ID, WRO.OPERATION_SEQ_NUM,
WRO.REQUIRED_QUANTITY, WRO.QUANTITY_ISSUED
FROM WIP_REQUIREMENT_OPERATIONS WRO, WIP_DISCRETE_JOBS_V WDJ,
MTL_SYSTEM_ITEMS MSI
WHERE WRO.REQUIRED_QUANTITY-WRO.QUANTITY_ISSUED>0
AND WRO.ORGANIZATION_ID=1234
AND WRO.ORGANIZATION_ID=WDJ.ORGANIZATION_ID
AND WRO.ORGANIZATION_ID=MSI.ORGANIZATION_ID
AND WRO.INVENTORY_ITEM_ID=MSI.INVENTORY_ITEM_ID
AND WRO.WIP_ENTITY_ID=WDJ.WIP_ENTITY_ID
AND WDJ.STATUS_TYPE=3 --Released
AND MSI.SEGMENT1='myItem'

[WIP] Before You Start

To get started with WIP module, you must first setup the WIP parameters (Setup -> Parameters) and setup at least 1 WIP Accounting Class (Setup -> WIP Accounting Classes).

[SYS] Session Timeout Issues

Try to increase the value for the following profile options.

ICX:Session Timeout - set it to number of minutes before timeout

ICX: Limit time - set it to number of hours before timeout

[INV] Pending Transactions

For inventory pending transactions, you can use a single responsibility to view all Inventory Organization, even though the Inventory Org. is under another OU which is registered under another responsibility.

[SYS] Item Cost Table

BOM.CST_ITEM_COSTS - table to store item cost

Important Fields:

cost_type_id (1-Froze, 2-Pending)

based_on_rollup_flag (1-Yes, 2-No)

[INV] Which OU is an Inventory Organization falled Under?

In Inventory responsibility, navigate Setup -> Organizations -> Organizations , query the organization name, click Others button, choose Accounting Information. You can see the assignment for Set of Books , Legal Entity and Operating Unit.

[PO] Item Not Found During Purchase Requisition (PR) Creation

In a Operation Unit (OU), I have 2 inventory organization (inventory child type), e.g. OrgA & OrgB. I need to raise a PR to purchase ItemC under OrgB. In Item setup, ItemC is created in Inventory Master org, and assigned to OrgB. Purchasable flag has been checked in both Inventory Master and OrgB. When i keyed in the item during PR creation, item not found, why?

The reason being the OrgA was assigned in Financial Options setup (Setup -> Organizations -> Financial Options), Supplier - Purchasing, Inventory Organization field. If OrgA is assigned, only item assigned to OrgA can be found in the LOV.

We can setup Inventory Master org in each OU, then assign the Inventory Master org in the Financial Options. From there, we can choose any items that was created under the Inventory Master org.

[PO] Backend Field for PR

We know that we can view Purchase Requisition (PR) number from PO distribution screen (Line -> Shipment -> Distributions, for standard PO). Which field in the backend table is used to link to PR tables?

Ans: LINE_LOCATION_ID field in APPS.PO_LINE_LOCATIONS_ALL table that links to APPS.PO_REQUISITION_LINES_ALL table

[PO] Organization And Location At Requisitions Form, Where Can We Set The Default Value For Those?

Both values are default from the Employee setup. In Purchasing responsibility, navigate Setup -> Personnel -> Employees, click "More ..." button, move to "Assignment" tab, enter it at Location field.

[SYS] FND_CONCURRENT.SET_COMPLETION_STATUS

To set concurrent program completion status, use FND_CONCURRENT.SET_COMPLETION_STATUS in your SQL file. However, will it work if your executable is PL/SQL package? Yes, it will still work but you need to define 2 extra parameters in your procedure (first 2).

e.g.

Procedure XXX (errbuf VARCHAR2(100), retcode INTEGER, ...)

[PO] Matching Setting in Purchase Order Shipment Line

Invoice matching can be set in five different areas of Oracle Purchasing:
In the list below, a setting at any level will override the settings above it.

1. Oracle Purchasing Options
a. Navigate to: Setup > Organizations > Purchasing Options
b. Select Default Alternative Region

2. Supplier Information
a. Navigate to: Supply Base > Suppliers
b. Query on specific supplier
c. Click on Open
d. Select Receiving Alternative Region

3. Line Types
a. Navigate to: Setup > Purchasing > Line Types
b. In the Receipt Required field: Yes = 3-way, No = 2-way

4. Items
a. Navigate to: Items > Master Items
b. Query on specific item
c. Select Purchasing Alternative Region
d. In the Invoice Matching section: Yes = 3-way, No = 2-way

5. Purchase Order Shipments
a. Navigate to: Purchase Orders > Purchase Orders
b. Enter (header and) line information
c. Click on Shipments button
d. Select More Alternative Region

Above notes are reproduced from Oracle Support Note:225630.1.

[OM] Allocation Error During Transact Move Order

"Error: The material sourcing process failed to create picking suggestions for line 2 of move order number 59641464"

"APP-INV-05297: Invalid transaction and serial control combination"

Encountered the above error message after clicked on "Allocate" button in Transact Move Orders form. Even after I backordered the line, I could not proceed to Pick Release, failed Pick Release.

Root Cause: Item lot reserved for the order line is no more valid.

Action: Unreserve the invalid item lot, pick release, transact move order & ship confirm successfully.

[INV] Pending Transactions Table - MTL_MATERIAL_TRANSACTIONS_TEMP

An excerpt from a good Oracle Support note [ID 294391.1]:

Transaction Mode Number / Description

NULL or 1: Online Processing
2: Concurrent Processing
3: Background Processing
8: Internal Processing (Not visible in the pending transactions form)

EXPLANATION
Here is an explanation of each:

NULL or 1: Online Processing
Online processing is used by the Oracle Applications to immediately process records. This does not require that a concurrent program be run. The Transaction Manager does not process transactions marked for online processing.

2: Concurrent Processing
Transactions marked as concurrent transaction mode are processed by a dedicated transaction worker to explicitly process a set of transactions. The Transaction Manager does not process transactions marked for concurrent processing.

3: Background Processing
Interface transactions marked for Background processing will be picked up by the transaction manager polling process and assigned to a transaction worker. These transactions will not be processed unless the transaction manager is running.

8: Internal Processing
Transaction mode 8 is not a mode normally visible to the user as it is used for internal processing. Transactions with this mode are not visible in the Pending transactions form. Transaction_mode of 8 is used internally to identify if records came from the interface table to the pending table or came directly into the pending table.

......

In short, resetting of transaction_mode to 3 will allow Inventory Manager to reprocess the pending records

Sample SQL:

update mtl_material_transactions_temp
set process_flag = 'Y',
lock_flag = 'N',
transaction_mode = 3,
error_code = NULL,
error_explanation = NULL
where process_flag in ('Y','E');

[INV] Reservations Interface Manager

What does the "Reservations Interface Manager" program do? [Inventory]On-hand,Availability->Reservations Interface Manager

Similarly to Inventory Manager, Reservations Interface Manager processes the the interface records in MTL_RESERVATIONS_INTERFACE table into MTL_RESERVATIONS permanent table.

[PO] Where to Setup Invoice Matching Option?

Setup -> Financial Option, Supply - Entry tab, Invoice Matching Option field.

Supply -> Sites, Control tab, Invoice Matching Option field.

(A Setting At Any Level Will Override The Settings Above It)

2 type of options available, Purchase Order / Receipt

[PO] R12 Purchasing Options

Setup -> Organizations -> Purchasing Options

In R11i, it is devided into 5 sections, "Receipt Accounting", "Control", "Default", "Internal Requisition", "Numbering" and "Tax Defaults".

In R12, (this form was converted to JSP format), only 4 sections available (some of the fields been merged), "Document Control", "Document Defaults ", "Receipt Accounting" and "Document Numbering".

New fields in R12:

Document Control

Security Hierarchy

Maximum Attachment Size

Email Attachment Filename

Enforce Supplier Hold

RFQ Required (from "Default" tab in R11i)

Document Defaults

Internal Requisition Order Type (from "Internal Requisition" tab in R11i)

Internal Requisition Order Source (from "Internal Requisition" tab in R11i)

Tax Defaults was removed from Purchasing Options. Need to confirm if it has been permanently removed or moved to other form.

[PO] Self-Billing Flag in PO table

How to know if a Purchase Order (PO) was created under supplier site with Self-Billing?

Not from front end. But check the PAY_ON_CODE field in PO.PO_HEADERS_ALL table. It should reflect "RECEIPT" value.

[PO] APP-PO-14376: Please enter a GL Date within an open purchasing period

You were trying to do Receiving. Once you navigate out of the Receipt Header form, the above message prompted. You have to open Purchasing Periodsunder Setup -> Control Purchasing Periods.

[PO] Can I Setup Only Document Total Rule in Approval Group

Yes. But in the Approval Assignment, you can only assign Blanket Purchase Agreement document type to that particular Approval Group. If you intend to assign other document type, e.g. Standard Purchase Order, you need at least another rule setup in Approval Group, which is Account Range.

[PO] Source Field in Purchase Requisition (PR) Screen Does Not Show "Supplier"

Make sure item is purchasable in the selected inventory organization.

[PO] BPA Price Break - Discount (%) field

It is a field to display percentage of discount applied to the new price compare to the very first price.

For example,

Break Price for line 1 = $100, line 2 = $80, line 3 = $70

Value for Discount (%) field fo line 1 = 0, line 3 = 20, line 3 = 30

[SYS] How to Revert My Start Page?

You have set the start page in Oracle Applications. On each successful login, it will brings you to the page. Now, you change your mind, you want to either disable the start page or change to other page. You could not get back to the HTML Preferences screen, as the start page will bring you directly to the specific Oracle form.

Ans: At any responsibilities, navigate Edit -> Preferences -> Profiles, query Profile Name "Applications Start page", change or remove the "User Value".

[PO] Missing Purchase Order (PO) Distribution After RTV - FIXED

With reference back to my problem logged to Metalink TAR, it was fixed after applied patch 4463796.

This patch upgrade the version of PAPOUTLB.pls to 115.3.90.4. Eventhough Project Accounting is not used, but this patch works.

How to check PAPOUTLB.pls version?

SELECT fi.file_id
,filename
,version
FROM apps.ad_files fi, apps.ad_file_versions ve
WHERE filename LIKE 'PAPOUTLB.%'
AND ve.file_id = fi.file_id
AND REPLACE(version, '.','') =
(SELECT MAX(TO_NUMBER(REPLACE(version, '.', '')))
FROM apps.ad_file_versions ven
WHERE ven.file_id = fi.file_id);