Thursday, September 18, 2014

Ordermangement Shipping Issues and Fixes


How to address a return order that shows error, Cust_Trx_Type_Id is required for Invoice Interface.

See Note 339989.1 - What to Check Upon Error - Required Attribute Customer
Trx Type Id is Missing

Unable to Invoice sales orders. Error "Description is required for Invoice Interface"

See Note 364292.1 - Description Is Required For Invoice Interface In Order Management 11.5.10

Message Batch Source Name is required for invoice interface

See Note 343430.1 - Batch_Source_Name is required for Invoice Interface Error Prevents Invoicing

Bill Only Orders Do Not Get Invoiced when Released from Hold

In order to achieve this functionality:
1) Created a hold source named Invoice Interface Hold: Workflow Item - Order Line Workflow activity - Line level Invoice interface
2) Enter a sales order with 2 lines
3) Apply this hold to one of the lines
4) Book, pick release, ship confirm the lines
5) Upon fulfillment, Invoice Interface fails for one line (because of the hold) The Other line is interfaced
6) Query the sales order line, release the hold on the other line and progress the order. The other line is subsequently interfaced

Additional reference:

Note 339036.1 -When Hold is Removed Workflow does not Automatically Progress the Line Past Invoice Interface Eligible.

Receivables Interface Sales_order_line Does Not Match Order Management Line Numbers

In Receivables it is a character field and only contains the "line number" not the composite line number as seen in Order Management. Here is an explanation for the OM - Invoice Interface functionality:

1. Oracle Order Management inserts information into two of the three Auto invoice

2. The Auto-Invoice program collects the data from above mentioned tables and
populates RA_CUSTOMER_TRX tables. Once the transaction tables are populated,

3. Order Management line numbers are populated in the following manner within RA_INTERFACE_LINES.:
INTERFACE_LINE_ATTRIBUTE12 (shipment_number)

4. Receivables provides the following transaction attributes that you can use in your line ordering rules (from the table

5. Suggested change to the line invoicing rule to align sorting as values are
    defined in Order Management (OM)
Go to the Line Ordering screen
Navigate: AR> Setup > Transaction > Auto invoice > Line Ordering
Set it to order by Order Line Number, interface_line_attribute6, interface_line_attribute12, interface_line_attribute13, interface_line_attribute14

Freight Charges Are Getting Into Receivables With Line Type As 'Freight' and Freight Charge is not Taxed

Taxes on charges are not calculated at this time during order entry, even if charges are
taxable in the jurisdiction. If it is necessary for charges to be taxed, the
user should set the ‘TAX: Invoice Freight as Revenue’ profile option to Yes
and also set up a dummy Freight item in Inventory that is taxable and
specify it in the ‘TAX: Inventory Item for Freight’ profile option. Then
OM’s Invoicing Integration will send the charges to Receivable’s
Autoinvoice as Lines with that Inventory Item on them, rather than as
Freight. There the charges can be taxed as required and revenue
accounting for the charges using AutoAccounting can be done.

Note: Be sure to set Profile option Tax: Invoice Freight as Revenue set to Yes
for the Responsibility that is used to run the Workflow Background process.

How Does The Invoice Date Get Derived From The Sales Order Line?

Oracle Receivables derives the invoice date from either the ship date actual or the sales order date.

For shippable lines, Order Management derives the ship date actual as follows:

SELECT dl.initial_pickup_date
FROM wsh_new_deliveries dl
WHERE dl.delivery_id = <delivery id of the line>;

If this is null then the sales order date will be used as invoice date. Please
set the intial pickup date correctly for shippable lines because that is what
will be the invoice date.

For non-shippable lines, since there is no shipping date,
1. you can choose to have the ship date actual as NULL (which allows AR to derive the invoice date as order date)
2. you can choose to have the ship date actual as sysdate
(the date when invoice ice iscompleted) by setting the profile option
OM:Set Receivables Transaction Date as Current Date for non shippable lines.
This profile option is available as part of Patch 2008789.
Reference Note 227816.1 for more details on this patch.

Two Invoices Generated For Lines With Same Invoice Rule

On a sales order with two order lines using the same Invoice Rule, there are two separate invoices generated. In the OE_ORDER_LINES_ALL table, the INVOICING_RULE_ID is the same (-2) for both lines on the sales order. The problem is that only certain lines are being populated in RA_INTERFACE_LINES_ALL table with the INVOICING_RULE_ID field. This causes the lines to end up on separate invoices. Invoice rule is a required grouping attribute. These lines have the same invoice rule in OM and should appear on the same invoice.

In this situation, the same Invoicing Rule was used but, a different Accounting Rule was used Line 1: Invoice Rule : Advance Invoice, Accounting Rule : Immediate and Line 2: Invoice Rule: Advance Invoice Accounting Rule: 12 month revenue
The solution is to set up a new accounting rule that has the same properties as IMMEDIATE (ACCOUNTING_RULE_ID=1) with a new name (ie:IMMEDIATELY) this will give the rule a different ACCOUNTING_RULE_ID. Then Invoice Rule will then get passed to RA_INTERFACE_LINES_ALL and both lines will end up on the same invoice

Why Can the Purchase Order Number on the Invoice be Different to the Value on the Sales Order Header?

The Invoice Interface program puts the oe_order_lines_all.customer_po_number to ra_interface_lines_all.purchase_order and the AutoInvoice program puts the value ra_interface_lines_all.purchase_order to ra_customer_trx_all.purchase_order.
So in normal cases, there should not be any differences between oe_order_headers_all.customer_po_number and ra_customer_trx_all.purchase_order.

However, there are 2 possible ways in which there can be difference between the oe_order_headers_all.customer_po_number and ra_customer_trx_all.purchase_order

a) the value of ra_interface_lines_all.purchase_order might have been modified manually BEFORE AutoInvoicing is done - from the form Receivables -> Control -> AutoInvoice -> Interface Lines or from SQL*Plus by running an update script
b) the oe_order_headers_all.customer_po_number changed BEFORE the order is closed, but AFTER the order line is closed - ie. invoiced. This can be done by simply updating the Customer PO from the Sales order header

For Sales Orders at Closed Status, it is not possible to change the Purchase order number.

Bill To On Order Header Does Not Match Bill To On The Invoice

Bill To from the Sales Order line is passed to Invoice Interface. This may or may not be different from the Bill To of the Sales Order header. It is the Sales Order line Bill To which appears on the Invoice.

Additional References:
Note 785547.1 - How Does One Set Up Invoice To Show The Same Bill-To Address
as Order Header ?

Freight Charges Added To Delivery Are Split Into Multiple Lines On The Invoice

This is currently an enhancement request Bug 4542163 - Freight Charges Added to Delivery
Are Split Into Multiple Lines on the Invoice

Autoinvoice Rejected Error Non Negative Total Amount

Query up the Order Number in the Receivables under the Control -> AutoInvoice -> Interface Lines
and change the Invoiced Quantity to same as that on the order line. Ordered Quantity, Shipped quantity and Invoiced Qty should be a positive number in the OE_ORDER_LINES_ALL table for non-return orders .
Make this change, run the AutoInvoice again and this line should get processed correctly.

New Transaction Type Does Not Process Line Through Invoicing

The required setup for Transaction Sources (Receivables > Setup > Transactions > Source) Transaction Type in not correct. It is set to Value. Per Oracle Order Management Open Interfaces, API & Electronic Messaging Guide, Release 11i, page
2-160 Table 2-47, the required setting for this field is ID.
To implement the solution, please execute the following steps:
1. Query the Source on the Transaction Sources (Receivables > Setup > Transactions > Sources)
2. Change setting for Transaction Type (Other Information tab) set to ID.
3. Run AutoInvoice

Why Are Adjustment Records Rounding Differently in AR Interface Table RA_INTERFACE_LINES_ALL Than Shown in OE_PRICE_ADJUSTMENTS?

For the answer, please refer to the details and screen shots outlined in Note 1542786.1

Thursday, September 11, 2014



Setting up the Oracle Master  Scheduling/MRP and Oracle Supply Chain Planning modules is easy.  Determining how your business will leverage the features,
is the hard part. This document can be used as a guide during your implementation. 

Before you set up Oracle Supply Chain Planning, you must complete the setup for the following: products:

Oracle Inventory
Oracle Purchasing
Oracle Bills of Material
Oracle Work in Process
Oracle Project Manufacturing

Planning Setup Checklist

Required steps for System Administration, General Ledger, Inventory, Purchasing, Bills of Material, and WIP modules need to be completed before
Planning is setup.

The following steps need to be completed in the order shown:

Step 1:

Define your Master Scheduling/MRP Setup Parameters (Required) 
NAV/Supply Chain Planning/Setup/Parameters or
NAV/Material Planning/Setup/Parameters

You can override the execution defaults, when defining the MPS, MRP or DRP for your organization(s).   
To have all past due demand (sales orders, forecast, etc.) reflected in your plans, select Null for Include MDS Days. 
Entering a value will limit the past due information visible in the Plan.

Step 2: 

Define your Deliver-to Locations (Optional)

Step 3: 

Define your Employees (Optional)
NAV/Work In Progress/Setup/Employees
Note: Enter Employees through Human Resources, if installed.  
A required step when implementing planning recommendations from the Planner Workbench (Step 21).

Step 4: 

Set Profile Options (Required with Defaults)
NAV/Profiles/System/Find MRP

Note: Use the Sysadmin Responsibility. The value for Snapshot Workers can be increased depending on the number of items being planned.  Use the
following formula to determine how many Standard Managers are required to support the process.  The value for the Planning Manager Max Workers should
not be more than the number of Standard Managers.

(Snapshot workers value *2) + 4 = # of Standard Managers
(2*2) + 4 = 8 Standard Managers

Recommended initial values:

MRP:Environment variable to set path for MRP files Null/Blank
MRP:Planning Manager Max Workers                        6
MRP:Retain Dates within Calendar Boundary               Yes
MRP:Snapshot Workers                                    2
MRP:Use Direct Load Option                              No

Step 5: 

Define your Forecast Sets (Optional)
NAV/Supply Chain Planning/Forecast/Sets
NAV/Material Planning/Forecast/Sets

Step 6:

Define your Master Demand Schedules (MDS)(Optional)
NAV/Supply Chain Planning/MDS/Names
NAV/Material Planning/MDS/Names

Step 7:

Define your Master Production Schedules (MPS) (Optional)
NAV/Supply Chain Planning/MPS/Names
NAV/Material Planning/MPS/Names

Step 8: 

Define your Material Requirements Plans (MRP);
Define your Distribution Requirements Plans (DRP) (Optional)
NAV/Supply Chain Planning/MRP/Names
NAV/Supply Chain Planning/DRP/Names
NAV/Material Planning/MRP/Names

Step 10:

Define your Sourcing Rules or Bills of Distribution (BOD)
NAV/Supply Chain Planning/Sourcing
Note: When you select All Orgs for your Sourcing Rule, it can be used by all organizations when defining an Assignment Set.  A Local
Sourcing Rule would only have the Org selected, and can only be used by the defining organization.   The Planning Active box will only be checked
if the allocation % = 100.  The planning process will not use the rule, unless the Planning Active box is checked

Step 11: 

Create your Assignment Sets
NAV/Supply Chain Planning /Sourcing/AssignSourcing Rules/BOD
Note:  A Local Sourcing Rule assigned at the Item-Organization level, overrides all others for that item.

Step 12: 

Define your Inter-organization Shipping Network
NAV/Inventory/Setup/Organizations/Shipping Network

Step 13:

Define Shipping Methods and associated intransit lead times
NAV/Inventory/Setup/Organization/Shipping Methods
NAV/Inventory/Setup/Organization/Shipping Network
  Special Menu:  Shipping Methods  Intransit Times

Step 14: 

Run the Information Audit (Optional-highly recommended)
NAV/Supply Chain Planning/Reports/Audit Information Report
NAV/Material Planning/Reports/Audit Information Report

Step 15: 

Define your Planning Parameters (Required)
NOTE: this is a duplicate of Step 1.

Step 16: 

Start the Planning Manager (Required)
NAV/Supply Chain Planning/Setup/Planning Manager or
NAV/Material Planning/Setup/Planning Manager
(See screen shot below; the active box should be checked and you should see current messages)

Step 17:

Define your Planners (Optional)

Step 18: 

Define your Planning Exception Sets (Optional)
NAV/Supply Chain Planning/Setup/Exception Sets
Or NAV/ Material Planning/Setup/Exception Sets

Step 19:

Define your Demand Classes (Optional)
NAV/Supply Chain Planning/Setup/Demand Classes
OR NAV/Material Planning/Setup/Demand Classes
Note: Selecting a Demand Class when defining your MDS or MPS,restricts data seen in the schedules.  Only Sales Orders with the
demand class entered at the line level will be included in your MDS. 
Only Discrete Jobs with the demand class added,(NAV/WIP/Discrete/Discrete Jobs/More) will be included in your MPS.

Step 20: 

Create Source Lists (Optional)
NAV/Supply Chain Planning/Forecast or MDS or MPS /Source List
NAV/Material Planning/Forecast or MDS or MPS/Source List

Step 21: 

Set Up Planner Workbench (Required)
For instructions, see: Setting Up Planner Workbench:
Oracle Master Scheduling/MRP and Supply Chain Planning User's Guide Release 11: page 1-28.

Step 22: 

Set Up for Supplier Planned Inventories (Optional) For instructions on setting up Supplier Planned Inventories, see the prerequisites in
Oracle Master Scheduling/MRP and Oracle Supply Chain Planning User's Guide Release 11, page 5-8.

Monday, August 25, 2014

Table Lock in ASCP application

Script to Find the Table Lock

SELECT NVL(S.USERNAME,'Internal') username,
NVL(S.TERMINAL,'None') terminal,
U1.NAME||'.'||SUBSTR(T1.NAME,1,20) tab,
2,'Row Share',
3,'Row Exclusive',
5,'Share Row Exclusive',
6,'Exclusive',NULL) lmode,
2,'Row Share',
3,'Row Exclusive',
5,'Share Row Exclusive',
6,'Exclusive',NULL) request
ORDER BY 1,2,5

3. dba can then kill these sessions
4. plan will continue normally.

Thursday, August 21, 2014

Re-Compile Invalid Schema Objects

Recompiling Invalid Schema Objects

Operations such as upgrades, patches and DDL changes can invalidate schema objects. Provided these changes don't cause compilation failures the objects will be revalidated by on-demand automatic recompilation, but this can take an unacceptable time to complete, especially where complex dependencies are present. For this reason it makes sense to recompile invalid objects in advance of user calls. It also allows you to identify if any changes have broken your code base. This article presents several methods for recompiling invalid schema objects.

Identifying Invalid Objects
The Manual Approach
Custom Script
utlrp.sql and utlprp.sql
Identifying Invalid Objects

The DBA_OBJECTS view can be used to identify invalid objects using the following query.

COLUMN object_name FORMAT A30
SELECT owner,
FROM   dba_objects
WHERE  status = 'INVALID'
ORDER BY owner, object_type, object_name;
With this information you can decide which of the following recompilation methods is suitable for you.

The Manual Approach

For small numbers of objects you may decide that a manual recompilation is sufficient. The following example shows the compile syntax for several object types.

Notice that the package body is compiled in the same way as the package specification, with the addition of the word "BODY" at the end of the command.

An alternative approach is to use the DBMS_DDL package to perform the recompilations.

This method is limited to PL/SQL objects, so it is not applicable for views.

Custom Script

In some situations you may have to compile many invalid objects in one go. One approach is to write a custom script to identify and compile the invalid objects. The following example identifies and recompiles invalid packages and package bodies.

  FOR cur_rec IN (SELECT owner,
                         DECODE(object_type, 'PACKAGE', 1,
                                             'PACKAGE BODY', 2, 2) AS recompile_order
                  FROM   dba_objects
                  WHERE  object_type IN ('PACKAGE', 'PACKAGE BODY')
                  AND    status != 'VALID'
                  ORDER BY 4)
      IF cur_rec.object_type = 'PACKAGE' THEN
        EXECUTE IMMEDIATE 'ALTER ' || cur_rec.object_type ||
            ' "' || cur_rec.owner || '"."' || cur_rec.object_name || '" COMPILE';
        EXECUTE IMMEDIATE 'ALTER PACKAGE "' || cur_rec.owner ||
            '"."' || cur_rec.object_name || '" COMPILE BODY';
      END IF;
        DBMS_OUTPUT.put_line(cur_rec.object_type || ' : ' || cur_rec.owner ||
                             ' : ' || cur_rec.object_name);
This approach is fine if you have a specific task in mind, but be aware that you may end up compiling some objects multiple times depending on the order they are compiled in. It is probably a better idea to use one of the methods provided by Oracle since they take the code dependencies into account.


The COMPILE_SCHEMA procedure in the DBMS_UTILITY package compiles all procedures, functions, packages, and triggers in the specified schema. The example below shows how it is called from SQL*Plus.

EXEC DBMS_UTILITY.compile_schema(schema => 'SCOTT');

The UTL_RECOMP package contains two procedures used to recompile invalid objects. As the names suggest, the RECOMP_SERIAL procedure recompiles all the invalid objects one at a time, while the RECOMP_PARALLEL procedure performs the same task in parallel using the specified number of threads. Their definitions are listed below.

   schema   IN   VARCHAR2    DEFAULT NULL,
   flags    IN   PLS_INTEGER DEFAULT 0);

   schema   IN   VARCHAR2    DEFAULT NULL,
   flags    IN   PLS_INTEGER DEFAULT 0);
The usage notes for the parameters are listed below.

schema - The schema whose invalid objects are to be recompiled. If NULL all invalid objects in the database are recompiled.
threads - The number of threads used in a parallel operation. If NULL the value of the "job_queue_processes" parameter is used. Matching the number of available CPUs is generally a good starting point for this value.
flags - Used for internal diagnostics and testing only.
The following examples show how these procedures are used.

-- Schema level.
EXEC UTL_RECOMP.recomp_serial('SCOTT');
EXEC UTL_RECOMP.recomp_parallel(4, 'SCOTT');

-- Database level.
EXEC UTL_RECOMP.recomp_serial();
EXEC UTL_RECOMP.recomp_parallel(4);

-- Using job_queue_processes value.
EXEC UTL_RECOMP.recomp_parallel();
EXEC UTL_RECOMP.recomp_parallel(NULL, 'SCOTT');
There are a number of restrictions associated with the use of this package including:

Parallel execution is perfomed using the job queue. All existing jobs are marked as disabled until the operation is complete.
The package must be run from SQL*Plus as the SYS user, or another user with SYSDBA.
The package expects the STANDARD, DBMS_STANDARD, DBMS_JOB and DBMS_RANDOM to be present and valid.
Runnig DDL operations at the same time as this package may result in deadlocks.
utlrp.sql and utlprp.sql

The utlrp.sql and utlprp.sql scripts are provided by Oracle to recompile all invalid objects in the database. They are typically run after major database changes such as upgrades or patches. They are located in the $ORACLE_HOME/rdbms/admin directory and provide a wrapper on the UTL_RECOMP package. The utlrp.sql script simply calls the utlprp.sql script with a command line parameter of "0". The utlprp.sql accepts a single integer parameter that indicates the level of parallelism as follows.

0 - The level of parallelism is derived based on the CPU_COUNT parameter.
1 - The recompilation is run serially, one object at a time.
N - The recompilation is run in parallel with "N" number of threads.
Both scripts must be run as the SYS user, or another user with SYSDBA, to work correctly.

For more information see:


Thursday, August 14, 2014

Create Incomplete PO From ASCP Release Work Bench

Create Incomplete PO From ASCP Release Work Bench 

There is no Standard Function available in ASCP to Release the as Incomplete from ASCP

But a Simple Trigger Can Fix This Requirement

For ASCP:  Add a trigger on table msc_purchase_requisitions_interface (on destination), to change the status before insert on MSC_PO_REQUISITIONS_INTERFACE for each row

end if;

Hope the Information is Helpful 

Wednesday, August 13, 2014

Released From ASCP- Required Completion Date In OPM Batch Is Earlier Than The Batch Start Date

Tuesday, August 12, 2014

Re open Purchase Orders - Oracle ( Mass Cancel)

Finally closed or Canceled PO's cannot be reopened.

This is the standard functionality of the application. Finally Close or Cancel actions are meant to prevent further modifications to the purchase order. This includes billing or receiving. Moreover there is a warning message whenever these actions are performed. The user is warned that this cannot be undone.

If there is a receipt against this PO and the requirement is to return it, it will be necessary to create a miscellaneous issue in Inventory for the concerned quantity and it will adjust the inventory stock.

If there is an invoice against this PO and the requirement is to cancel it, it will be necessary to create a credit memo using the same information entered on the original invoice.