Approval Management(AME) in Oracle Order Management

Oracle Approvals Management (AME) is a self‐service web application that enables us to define business rules governing the process for approving transactions in Oracle applications that have integrated AME. 

In R12.2.2, Order Management integrates with Oracle Approval Management (AME) to leverage the robust capabilities of the approval rules engine to dynamically determine the list of approvers for Quotes, Sales Agreements and Returns (RMA). 
Order Management has been integrated with Oracle Approval Management for the workflow "Negotiation Flow - Generic with Approval" and "Order Flow - Mixed or Return with Approval" to provide an additional option to use Approvals Management Engine (AME) for the approval mechanism of order lines with the above work flows.
We can enable this option by selecting the Use Approvals Management Engine check box on the Order Management Transaction Types window. For the AME enabled transactions, Order Management utilizes the AME capabilities of a central business rules repository and a common execution engine, to manage approvals for its business processes. 

Read more »

Negotiation Quotes - Offer Expiration Date and send notification prior to default value

The Flow for the Quote Offer Expiration process is like below:

->We can limit the validity of the Quote to a specific period and assign an offer expiration date. 

->The workflow updates status at expiration, and expired transactions may be purged. This date can be manually entered, or defaulted by using defaulting rules and selecting a defaulting condition for negotiation.

->If we select the system variable and enter sysdate+7, the expiration date will default 7 days from the current system date. Notifications can be generated prior to expiration. 

->An offer expiration date is necessary to ensure successful closure of a Quote in progress. Although this is an optional field, transactions which have not been manually processed to a lost status remain active until an expiration date is assigned. Dates entered are honored by workflow and when the date has passed the Quote automatically moves to a expired status in the workflow and becomes eligible for purge.

->When the Quote is due to expire, a notification can be generated that will be sent a variable number of days before the offer expires. Business needs to decide when the pre-expiration notification should be sent and we can assign a value accordingly to workflow item attribute for negotiation (OENH) item type.
e.g. if quote expires in 10 days and notification should be sent 2 days before expiration, Pre-Expiration Time Percentage (PRE_EXPIRE_TIME_PERCENT) attribute should beset to 20% (20% of 10 days => 2 days). This setup is not needed if default of 10% is fine i.e. by default, notification will be sent 1 day prior if quote is to expire in 10 days.

To alter the defaul time for notification to be sent, below steps need to be followed:

1. Open the Oracle Workflow Builder Tool for 'OM Negotiation Header (OENH)' item type and navigate to 'XX Offer Expiration - Negotiation' sub-process under 'XX Negotiation Flow - Generic with Approval' runnable process. 

2. Under this sub-process, for the first most activity 'Assign', open its property palette, and click on the 'Node Attributes' tab. 

3. For Item attribute 'Pre-Expiration Time Percentage', please change its 'Numeric Value' from 10 to 90 (as per their business) 

4. Apply and save the changes. 

5. Now launch Oracle Applications and using this workflow process, please create one negotiation flow. 

The same can be cross-verified through following read only sql statements -
 
 select  item_type, name, number_default
  from wf_item_attributes
  where   item_type = 'OENH'
  and     name      = 'PRE_EXPIRE_TIME_PERCENT'; 

  select item_type, item_key, name,  number_value
  from   WF_ITEM_ATTRIBUTE_VALUES
  where  item_type = 'OENH'
  and    item_key  = '&Enter_header_id'
  and    name = 'PRE_EXPIRE_TIME_PERCENT';

Read more »

Quote Negotiation Workflow- Quote To Order

There are 3 seeded negotiation flows:

• Negotiation Flow - Simple: This workflow does not require any approvals nor customer acceptance. However the quote can either expire or get lost if it does not progress to being converted to an order.
• Negotiation Flow—Generic: Simple negotiation flow, without approval. Prepares quote document, get customer final acceptance, convert quote to the Sales Order.
• Negotiation Flow—Generic with Approval: Flow with Approval. Prepare quote document, get management approval, get customer final acceptance, and convert the quote to an order.
  
  
  
  These flows can be associated to transaction types for both Sales Orders and Sales Agreements. Both can be converted to an order. Quotes can be converted to sales orders in either the Entered or Booked status (if the booking activity is synchronous).
  
  
  Below is the generic flow with approval:

Draft - A quote is created and saved in DRAFT status. 

Internal Approval - 

After a quote has been put together, it can be submitted for approval. The relevant documents can be routed to various people in the organization, including people from Sales, Business Practice, Legal, or Finance, for review.

Neogitaions -> Quote -> Actions -> Progress Order

Status gets updated to 'Pending Internal Approval'.

The list of approvers is defined at the Transaction Type level. The document must be approved by each participant in the list before the transaction is eligible to move forward in the workflow. If the approver fails to respond within the time limit, the system will re-send the notification. If the approver again fails to respond, the system will either send the notification to the next approver (if the current approver is not the last approver), or reject the notification based on the system parameter setup.

The Approver List can be accessed two ways:

1. From the Transaction Type setup window: (N) > Orders, Returns > Setup > Transaction Type > Define. Select the Approvals button to bring up the Approver List.
2. Navigate directly to the window: (N) > Orders, Returns > Setup > Transaction Type > Approvals.

If an approver is deleted from the list the notifications still need to be processed.

If an approver is added to the list and any transaction is pending approval they will receive a notification.

The user will receive a notification and must approve or reject.

Customer Acceptance - Once the quote is approved by the internal management, the status gets updated to 'Pending Customer Acceptance'.

Neogitaions -> Quote -> Actions -> Customer Acceptance -> Customer Accepted in the acceptance form

Once the customer accepts the quote, it gets converted to sales order in a 'Entered' status.  This can now be booked like a typical sales order and progressed.

In 12.2 order management is now integrated with AME (approvals management engine).  This will allow you to perform hierarchical approvals during the quoting process and even dynamically generate a list of approvers based on attribute values, conditions, approval types and approval groups.

Read more »

Query - Link AR XLA and GL

SELECT 
       rcta.trx_number transaction_num, 
       rcta.trx_date transaction_date,       
       gjh.posted_date posted_date, 
       gjh.je_source, 
       gjh.je_category,
       gjb.NAME je_batch_name, 
       gjh.NAME journal_name, 
       gjl.je_line_num je_line, 
       gjl.description je_line_descr,
       xal.entered_cr global_cr, 
       xal.entered_dr global_dr,
       xal.currency_code global_cur, 
       ac.customer_name vendor_customer,
       xal.accounting_class_code transaction_type, 
       xal.accounted_cr local_cr,
       xal.accounted_dr local_dr, 
       gl.currency_code local_cur,
       (NVL (xal.accounted_dr, 0) - NVL (xal.accounted_cr, 0)
       ) transaction_amount,
       gl.currency_code transaction_curr_code, 
       gjh.period_name fiscal_period,
       gl.NAME ledger_name
  FROM apps.gl_je_headers gjh,
       apps.gl_je_lines gjl,
       apps.gl_import_references gir,
       xla.xla_ae_lines xal,
       xla.xla_ae_headers xah,
       apps.gl_code_combinations glcc,
       xla.xla_transaction_entities xte,
       apps.ra_customer_trx_all rcta,
       apps.gl_ledgers gl,
       apps.gl_balances gb,
       apps.ar_customers ac,
       apps.gl_je_batches gjb
 WHERE 1 = 1
   AND gjh.je_header_id = gjl.je_header_id
   AND gjl.je_header_id = gir.je_header_id
   AND gjh.je_source = 'Receivables'
   AND gjl.je_line_num = gir.je_line_num
   AND gir.gl_sl_link_id = xal.gl_sl_link_id
   AND gir.gl_sl_link_table = xal.gl_sl_link_table
   AND xal.ae_header_id = xah.ae_header_id
   AND xal.application_id = xah.application_id
   AND xal.code_combination_id = glcc.code_combination_id
   AND xte.entity_id = xah.entity_id
   AND xte.entity_code = 'TRANSACTIONS'
   AND xte.ledger_id = gl.ledger_id
   AND xte.application_id = xal.application_id
   AND NVL (xte.source_id_int_1, -99) = rcta.customer_trx_id
   AND gjh.ledger_id = gl.ledger_id
   AND gb.code_combination_id = glcc.code_combination_id
   AND gb.period_name = gjh.period_name
   AND gb.currency_code = gl.currency_code
   AND gjh.je_batch_id = gjb.je_batch_id
   AND rcta.bill_to_customer_id = ac.customer_id(+)
   AND rcta.trx_number = :trx_number
  

Read more »

Query - Check if ITS has successfully interfaced the delivery detail to OM and INV

SELECT 

delivery_detail_id, 

released_status, 

oe_interfaced_flag, 

inv_interfaced_flag
FROM wsh_delivery_details
WHERE source_code = 'OE' 

AND source_line_id = :line_id;

Read more »

Oracle Order To Cash Process for Shippable Item

Order to cash (OTC or O2C) is a set of business processes that involve receiving and fulfilling customer requests for goods or services. It is the complete process of entering an order into the system (Sales Order), delivery the good(s) (Shipping), and then producing the Invoice for the good(s) which are Ordered by the Customer.

O2C cycle  can be summarized in below steps:

• Enter the Sales Order
• Book the Sales Order
• Pick Release
• Ship Confirm
• Create Invoice

Now let's see the underlying affected tables for each of the above steps and the Status for each step.

Step	Order Header Status	Order Line Status	Shipping Transaction Status	Tables Affected
Enter Sales Order	Entered	Entered		New Records populated in below tables: OE_ORDER_HEADERS_ALL (FLOW_STATUS_CODE = ENTERED, BOOKED_FLAG = N, Primary key=HEADER_ID) OE_ORDER_LINES_ALL (FLOW_STATUS_CODE = ENTERED,  BOOKED_FLAG = N, OPEN_FLAG = Y, Primary key= LINE_ID)
Book Sales Order	Booked	Awaiting Shipping	Ready To release	OE_ORDER_HEADERS_ALL (FLOW_STATUS_CODE as BOOKED, BOOKED_FLAG updated to Y) OE_ORDER_LINES_ALL (FLOW_STATUS_CODE as AWAITING_SHIPPING, BOOKED_FLAG updated Y) WSH_DELIVERY_DETAILS (DELIVERY_DETAIL_ID is assigned here, RELEASED_STATUS ‘R’ ready to release, LINE_ID comes as SOURCE_LINE_ID) WSH_DELIVERY_ASSIGNMENTS (DELIVERY_ASSIGNMENT_ID is assigned for DELIVERY_DETAIL_ID present in WSH_DELIVERY_DETAILS, DELIVERY_ID remains blank till this stage) MTL_DEMAND (LINE_ID come as a reference in DEMAND_SOURCE_LINE)
Pick Release	Booked	Picked	Released to Warehouse -> Staged/Pick Confirmed	WSH_NEW_DELIVERIES (SOURCE_HEADER_ID= order header ID, STATUS_CODE=OP =>open) WSH_DELIVERY_ASSIGNMENTS (DELIVERY_ID gets assigned which comes from WSH_NEW_DELIVERIES) WSH_DELIVERY_DETAILS (RELEASED_STATUS ‘S’ ‘submitted for release’) MTL_TXN_REQUEST_HEADERS MTL_TXN_REQUEST_LINES (LINE_ID goes as TXN_SOURCE_LINE_ID) MTL_MATERIAL_TRANSACTIONS_TEMP (link to above tables through MOVE_ORDER_HEADER_ID/LINE_ID, this table holds the record temporally) MTL_SERIAL_NUMBERS_TEMP (if item is serial controlled at receipt then record goes in this table) MTL_SERIAL_NUMBERS (enter value in GROUP_MARK_ID ) MTL_RESERVATIONS
Ship Confirm	Booked	Shipped	Shipped	OE_ORDER_LINES_ALL (flow_status_code =‘SHIPPED’) WSH_DELIVERY_DETAILS. released_status=‘C’ (‘Shipped’) Data from MTL_TRANSACTIONS_INTERFACE is moved to MTL_MATERIAL_TRANACTIONS and MTL_MATERIAL_TRANSACTIONS is updated with Sales Order Issue transaction. Data is deleted from MTL_DEMAND, MTL_RESERVATIONS Item reduced from MTL_ONHAND_QUANTITIES MTL_TRANSACTION_ACCOUNTS is updated with accounting information.
Create Invoice				RA_INTERFACE_LINES_ALL (interface table into which the data is transferred from order management) RA_CUSTOMER_TRX_ALL (CUST_TRX_ID is primary key to link it to TRX_LINES table and TRX_NUMBER is the invoice number) RA_CUSTOMER_TRX_LINES_ALL (LINE_ATTRIBUTE_1 and LINE_ATTRIBUTE_6 are linked to order number and LINE_ID of the orders)

1. Enter Sales Order

This is first step, when the order is entered in the system, it creates a record in order headers and Order Lines table.

2. Book Sales Order

This is next step, when Order is booked. The status of the order header has changed from Entered to 'Booked', and the status of the line (check the Status field in the Main tab of the lines) has changed from Entered to 'Awaiting Shipping'.

In shipping transaction form order status is "Ready to Release".

Check if the order lines are scheduled or not. If any of the order line is in BOOKED status, it means that the order line is not yet scheduled.

Schedule Order

·         Run the Schedule Order concurrent program to schedule the order. After the concurrent program is scheduled the order line will move to Awaiting Shipping status.

·         Still if the order lines do not go to Awaiting Shipping, check for any holds on the order line/ header. Release the holds (if any) and re-run the Schedule Order program.

·         You can even schedule the order line in the following way: Right click on the order line and progress the line to schedule it.

At the same time when order is booked ‘Demand interface program’ is triggered in the background and demand of the item with specified quantity is created and these demand information is stored in MTL_DEMAND.

3. Pick Release

Pick Release is the process of putting reservation on on-hand quantity available in the inventory and pick them for particular sales order.

Pick release can be done from 'Release Sales Order' form or 'Pick release SRS' program can be scheduled in background. In both of these cases all lines of the order gets pick released depending on the Picking rule used. If specific line/s needs to be pick release it can be done from 'Shipping Transaction form. For this case Pick Release is done from 'Release Sales Order' form with Pick Confirm=NO.

*In shipping transaction form order status remains "Released to Warehouse" and all the material still remains in source sub-inventory. We need to do Move Order Transaction for this order. Till this no material transaction has been posted to MTL_MATERIAL_TRANSACTIONS

Pick Confirm/ Move Order Transaction

If Auto Pick confirm is not selected while pick release, then we have to manually do the transact move order process. In fact, pick confirm also triggers the transact move order process in which the inventory is moved from item sub inventory to staging area.Here material transaction occurs.

Order line status becomes 'Picked' on Sales Order and 'Staged/Pick Confirmed' on Shipping Transaction Form. Move order tables are populated. Here request is generated to move item from Source (RM or FG) sub-inventory to staging sub-inventory.

* This step can be eliminated if we set Pick Confirm=YES at the time of Pick Release

4. Ship Confirm

The items on the delivery gets shipped to customer at this stage.

The Interface Trip Stop (ITS) program gets submitted in the background and it should  be completed successfully.

The Status in Shipping Transaction screen will now be ‘Shipped’.

Optional: Check if the item has actually been shipped out of inventory.

Make sure you are in the correct organization. If not, navigate to Inventory > Change Organization and select the correct one.

*Navigate to Inventory > Transactions > Material Transactions

*Enter the following information in the Find Material Transactions screen and click on Find

1. Transaction Date : Ensure the transaction date is within the range specified
2. Source Type : Sales Order
3. Source :

• Evoke the LOV and click on the button Combinations
• Enter your Sales Order Number and Order Type, then click OK(Example : 66666.Mixed.ORDER ENTRY)

*Go to the Transaction Type tab, and notice the line with Transaction Type = Sales Order Issue. This is the Inventory Transaction that happened when the item was shipped out of the warehouse M1, hence the negative quantity.

*The other two lines with Transaction Type = Sales Order Pick, are for the move order transaction that happened automatically during Pick Release (automatic as per the pick release parameters we had set in this testflow), when the item was moved from the pick-from subinventory to the staging subinventory. Hence there is one line each for a negative and positive transaction from/to each subinventory. The final shipment to the customer (sales order issue) is made from the staging subinventory.

5. Create Invoice

Run the Workflow Background Process with the following parameters.

Item Type : OM Order Line

Process Deferred : Yes

Process Timeout : No

Workflow Background Process inserts the records RA_INTERFACE_LINES_ALL with

• INTERFACE_LINE_CONTEXT     =     ‘ORDER ENTRY’
• INTERFACE_LINE_ATTRIBUTE1= Order_number
• INTERFACE_LINE_ATTRIBUTE3= Delivery_id

AR concurrent requests Auto invoice Master Program and Auto invoice import program gets kicked off automatically to import the invoices into AR. OM inserts invoice data into the AR interface tables through 'Invoice Interface', which runs in the background. AR picks up the data from the interface tables through the 'Autoinvoice' concurrent program.

Invoicing workflow activity transfers shipped item information to Oracle Receivables.

The Invoice created can be seen using the Receivables responsibility.

Read more »

Query - Link OM, WSH and AR

SELECT ooh.order_number

              ,ool.line_id

              ,ool.ordered_quantity

              ,ool.shipped_quantity

              ,ool.invoiced_quantity

              ,wdd.delivery_detail_id

              ,wnd.delivery_id

              ,rctl.interface_line_attribute1

              ,rctl.interface_line_attribute3

              ,rctl.interface_line_attribute6

              ,rct.org_id

              ,rct.creation_date

              ,trx_number

              ,rctl.quantity_ordered

              ,rct.interface_header_context

  FROM oe_order_headers_all ooh

             ,oe_order_lines_all ool

             ,wsh_delivery_details wdd

             ,wsh_new_deliveries wnd

             ,wsh_delivery_assignments wda

             ,ra_customer_trx_all rct

             ,ra_customer_trx_lines_all rctl

 WHERE ooh.header_Id=ool.header_id

      AND wdd.source_header_id=ooh.header_id

      AND wdd.source_line_id = ool.line_id

      AND wdd.delivery_detail_Id=wda.delivery_detail_id

      AND wda.delivery_id=wnd.delivery_id

      AND rctl.interface_line_attribute1=to_char(ooh.order_number)

      AND rctl.interface_line_attribute6=to_char(ool.line_id)

      AND rctl.interface_line_attribute3=to_char(wnd.delivery_id)

      AND rctl.customer_trx_id=rct.customer_trx_id

      AND rct.interface_header_context='ORDER ENTRY'

      AND ooh.order_number = :p_order_number

Read more »