How Shipping Dates are calculated in Oracle R12

Delivery's 'Initial Pickup' and 'Ultimate Ship to Date' are calculated based on ‘Delivery Date Calculation Method’ provided in Shipping Global Parameter's setup. It can be either of following

• Earliest Ship Date, Latest Delivery Date
• Scheduled Date, Requested Date

Navigation:

Oracle Order Management > Shipping > Setup > Global Parameters

Initial Pickup Date:

‘Initial Pickup Date’ is the date on which the items will be shipped out of the warehouse. While creating delivery or assigning delivery line to a delivery, Initial Pick up date is calculated.

If 'Delivery Date Calculation Method' in Global Parameter is set to 'Earliest Ship Date, Latest Delivery Date' then Initial Pickup Date will be Max of (Earliest Pickup Date)

If 'Delivery Date Calculation Method' in Global Parameter is set to 'Scheduled Date, Requested Date' then Initial Pickup Date will be Max of (Date Scheduled)

While closing the pick-up stop manually or during Ship Confirm process, 'Initial Pickup Date' will always be updated with 'Actual Departure Date' (Initial Pickup Date = Actual Departure Date).

Ultimate Ship to Date:

If 'Delivery Date Calculation Method' in Global Parameter is set to 'Earliest Ship Date, Latest Delivery Date' then Ultimate Ship to date will be NVL(Min(Latest Dropoff date), Greatest[Min(Date Requested), Min(Date Scheduled)])

(i.e) Ultimate Ship to date will be Min of (Latest Dropoff date). If Latest Dropoff date field value is null then whichever date is greater in between Min (Date Requested) and Min (Date Scheduled) will be taken as Ultimate Ship to date.

If 'Delivery Date Calculation Method' in Global Parameter is set to 'Scheduled Date, Requested Date' then Ultimate Ship to date will be Min (Date Requested) if it less than Min (Date Scheduled) else Ultimate Ship to date will be Min (Date Scheduled).

If derived ‘Ultimate Ship to Date’ happens to be less than 'Initial Pickup Date' derived then Ultimate Ship to Date = Initial Pickup Date.

While closing the pick-up stop manually or during Ship Confirm process, if 'Ultimate Ship to Date' is less than the 'Actual Departure Date' then Ultimate Ship to Date = Actual Departure Date.

Note:

'Delivery Date Calculation Method' in Global Parameter field can be left null also. If left null, the Initial Pickup Date and Ultimate Ship to Date will be Max of (System Date)

---X---

Interface Trip Stop – SRS Program

The Interface Trip Stop (ITS) will be triggered at the time of Ship Confirm if the check box ‘Defer Interface’ is not checked. ITS can also be executed from concurrent request.

Interface Trip Stop does the following things,

• Updating the Order Management Data (OE_ORDER_LINES_ALL)
• Triggering the Inventory Interface (to update the Inventory tables)

ITS updates the following fields on OE_ORDER_LINES_ALL table:

• Shipped_Quantity
• Shipping_Quantity
• Actual_Shipment_Date

Inventory Interface will be triggered only if the first part, that means related to OM has successfully completed and flag OE_INTERFACED_FLAG = ‘Y’ on WSH_DELIVERY_DETAILS.  

If value of this flag is N/P then Inventory Interface will never be triggered. And even if you try to submit the Inventory Interface - SRS, delivery detail with OE_INTERFACED_FLAG =’N’ will never be picked up.

OE_INTERFACED_FLAG = Y -- signifies ITS has interfaced shipping data to OM

INV_INTERFACED_FLAG = Y -- signifies ITS has interfaced shipping data to INV

As mentioned earlier if the ‘Defer Interface’ checkbox is checked then Interface trip stop concurrent will not be triggered automatically, we should run it manually.

Navigation:

Oracle Order Management > Shipping > Interfaces > Run

Select Mode as ‘All’

If you want to process a particular transaction, then select the same from the list of values of ‘Trip Stop’ and ‘Delivery’.

If wanted to process all the pending transactions leave the fields blank and submit the concurrent.

As scheduling other concurrent’s, we can also schedule ITS – SRS process to run at regular intervals to process different trip stops.

But keep in mind that Deadlock errors may occur when multiple Interface trip stop processes are running at the same time.

To avoid this we have to make ITS and ITS-SRS processes incompatible with themselves as follows,

Navigation: Application Developer > Concurrent > Program

Query ‘Interface Trip Stop – SRS’ program, Click ‘Incompatibilities’ box.

In the Name field select ‘Interface Trip Stop’. For ‘Scope’ select ‘Program’ and for ‘Type’ select ‘Global’. If you select 'domain' for type, then the system will allow two ITS processes to run simultaneously if they are run in different domains. 

Now Query ‘Interface Trip Stop’ and make it incompatible with ‘Interface Trip Stop – SRS’ in the same way as above.

This will prevent more than one ITS or ITS-SRS process from running at the same time.  All other ITS or ITS-SRS processes will be added to a queue and will be processed when the current ITS or ITS-SRS process completes.  Therefore, multiple ITS processes will not become deadlocked with each other.

---X---

Please run and save all Account and Inventory Valuation report outputs before changing any valuation accounts.

Sub-inventory form allows changing valuation accounts only if there is no on-hand in sub-inventory, no un-costed transactions in MTL_MATERIAL_TRANSACTIONS and no pending transactions (MTL_MATERIAL_TRANSACTIONS_TEMP).

When attempted to change the valuation accounts, system will display a warning message “Please run and save all Account and Inventory Valuation report outputs before changing any valuation accounts.”

This is a warning message. We can choose ‘OK’, then proceed with changing the accounts. If ‘Cancel’ is clicked then the existing old account value is put back for the account.

Note:

To avoid reconcillation issues it is recommended to create manual jounal entries to transfer the balance from the discontinued accounts and the accounts be changed at the time of period close.

---X---

Tables involved in Order to Cash (O2C) Cycle - Oracle R12

Order Entry:

OE_ORDER_HEADERS_ALL

At the time of Order entry, the Flow_Status_Code is ‘Entered’

OE_ORDER_LINES_ALL

OE_PRICE_ADJUSTMENTS – when discount gets applied

OE_ORDER_PRICE_ATTRIBS – if line has price attributes

OE_ORDER_HOLDS_ALL – if any hold applied for order like Credit check etc…

Header_id is the link between OE_ORDER_HEADERS_ALL and OE_ORDER_LINES_ALL tables.

Order Booking:

WSH_DELIVERY_DETAILS

WSH_DELIVERY_ASSIGNMENTS

When the Order is Booked, the Flow_Status_Code in OE_ORDER_HEADERS_ALL will be ‘Booked’ and the Flow_Status_Code in OE_ORDER_LINES_ALL will be ‘Awaiting Shipping’.

New records will be created in WSH_DELIVERY_DETAILS with Released_Status=’R’ (Ready to Release). Also Record(s) will be inserted into WSH_DELIVERY_ASSIGNMENTS.

Launch Pick Release:

Flow_status_code in OE_ORDER_HEADERS_ALL will be ‘Picked’ or ‘Awaiting Shipping’ depending on Auto Pick Confirm (set to No or Yes)

Released_Status in WSH_DELIVERY_DETAILS will be ‘S’ (Submitted for Release) or ‘Y’ (Pick Confirmed). These values again depend on the parameters given at Auto Pick Confirm (set to No or Yes)

WSH_DELIVERY_ASSIGNMENTS - Delivery_ID is populated here (from Delivery_Detail_ID with reference to WSH_DELIVERY_DETAILS table)

MTL_TRANSACTIONS_INTERFACE is updated with Sales order Pick transaction.

Ship Confirm:

Data from MTL_TRANSACTIONS_INTERFACE is moved to MTL_MATERIAL_TRANACTIONS and MTL_MATERIAL_TRANSACTIONS is updated with Sales Order Issue transaction.

Flow_status_code in OE_ORDER_LINES_ALL will be ‘Shipped’

Released_Status in WSH_DELIVERY_DETAILS will be ‘C’ (means shipped)

Creating Invoices in Receivables:

Workflow Background Process inserts new records in RA_INTERFACE_LINES_ALL table.

Auto Invoice Master Program transfers the data from RA_INTERFACE_LINES_ALL to the following tables,

RA_CUSTOMER_TRX_ALL: Stores Invoice header information.

RA_CUSTOMER_TRX_LINES_ALL: Stores Invoice lines information.

Creating Receipt:

AR_CASH_RECEIPTS_ALL

Flow_status_code in OE_ORDER_LINES_ALL should be ‘CLOSED’

---X---