Pages

OracleEBSpro is purely for knowledge sharing and learning purpose, with the main focus on Oracle E-Business Suite Product and other related Oracle Technologies.

I'm NOT responsible for any damages in whatever form caused by the usage of the content of this blog.

I share my Oracle knowledge through this blog. All my posts in this blog are based on my experience, reading oracle websites, books, forums and other blogs. I invite people to read and suggest ways to improve this blog.


Monday, August 22, 2016

Pick Release Sales order - Oracle Apps Technical

What is Pick Release?

Pick release perform process starts, once the Order is scheduled and booked, then we need to release the order  to warehouse for shipping 

1: ENTER THE SALES ORDER
Once order is entered.
The Header information stored in OE_ORDER_HEADERS_ALL and the Line information stored in OE_ORDER_LINES_ALL, when the order is entered.
The Column: Flow_Status_Code is available both OE header and line tables, to define status of order at each stage        
  Flow_Status_Code consists of 4 types
1.       Entered
2.       Cancelled
3.       Closed
4.       Booked

For initial stage, once order enters = > Column: Flow_Status_Code in OE_ORDER_HEADERS_ALL is ‘Entered’
When the order is booked, Flow_Status_Code column in header change accordingly

2:BOOK THE SALES ORDER
Tables Affected:              
OE_ORDER_HEADERS_ALL   - Column: Flow_Status_Code => ‘Entered’
OE_ORDER_LINES_ALL - Column: Flow_Status_Code => ‘AWAITING_SHIPPING’
Records will be created in the table WSH_DELIVERY_DETAILS
  WSH_DELIVERY_DETAILS – Column Released_Status => ‘R’(Ready to release)
Also record into inserted into WSH_DELIVERY_ASSIGNMENTS
At this stage,”DEMAND INTERFACE PROGRAM” runs in the background and inserts into MTL_DEMAND

3:PICK RELEASE
 In Release Sales order window.
In shipping tab:
Auto Create Delivery: Yes
Auto Pick Confirm: Yes
Auto Pack Delivery: Yes
In Inventory Tab:
            Auto Allocate: Yes
Enter the Ware house like M1 ,M2, Etc
 Click on “Execute button”, After successful completion , you will get popup message
Now , Pick Release process in turn will kick off several other request program like
ü  Auto Pack report
ü  Shipping Execution Report
ü  Pick Slip Report 
Tables Affected:
If Autocreate Delivery is set to ‘Yes’ then a new record is created in the table WSH_NEW_DELIVERIES.
DELIVERY_ID is populated in the table WSH_DELIVERY_ASSIGNMENTS.
The RELEASED_STATUS in WSH_DELIVERY_DETAILS => set to ‘Y’ (Pick Confirmed)
 if Auto Pick Confirm is set to Yes otherwise RELEASED_STATUS => ‘S’ (Release to Warehouse).
IF Auto Pick Confirm in the above step is set to NO, then the following should be done.
Navigation: Inventory Super User > Move Order> Transact Move Order
In the HEADER tab, enter the BATCH NUMBER (from the above step) of the order. Click FIND. Click on VIEW/UPDATE Allocation, then Click TRANSACT button. Then Transact button will be deactivated then just close it and go to next step

4. SHIP CONFIRM THE ORDER
Once Shipping Transaction Successfully completed, After then few Concurrent program will be trigger .i.e.,
ü  INTERFACE TRIP Stop
ü  Commercial Invoice
ü  Packing Slip Report
ü  Bill of Lading
Tables Affected:
WSH_DELIVERY_DETAILS  - Column : Released_Status=> ‘C’ (Ship Confirmed)
OE_ORDER_HEADERS_ALL – Column: Flow_Status_Code => ‘Booked’
OE_ORDER_LINES_ALL – Column : Flow_status_Code => ‘Shipped’

…   The Final process move into Invoice
Hints :
Pick Release Status – WSH_DELIVERY_DETAILS
Column Name: Released_Status .
B. Backordered – Line failed to be allocated in Inventory
C: Shipped – Line has been shipped
D: Cancelled – Line is cancelled
N: Not ready to 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
Sample Scripts / List of Tables 
OE_ORDER_HEADERS_ALL, OE_ORDER_LINES_ALL,WSH_DELIVERY_DETAILS, WSH_DELIVERY_ASSIGNMENTS,
MTL_DEMAND
Sample script :1
SELECT oha.order_number sales_order,
  oha.org_id,
  ola.line_number,
  ola.shipment_number,
  ola.flow_status_code,
  wdd.delivery_detail_id,
  wdd.inv_interfaced_flag,
  wdd.oe_interfaced_flag,
  Decode (wdd.released_status,'C','Shipped','B','Backordered','D','Cancelled','N','Not Ready for  Release','R','Ready to Release',  'S','Released to Warehouse','X','Not Applicable','Y','Staged')as Released_Status
FROM apps.oe_order_headers_all oha,
            apps.oe_order_lines_all ola,
            apps.wsh_delivery_details wdd
WHERE oha.header_id                = ola.header_id
AND oha.org_id                     = ola.org_id
AND oha.header_id                  = wdd.source_header_id
AND ola.line_id                    = wdd.source_line_id
AND oha.booked_flag                = 'Y'
AND NVL (ola.cancelled_flag, 'N') <> 'Y'
 AND wdd.released_status in ('R','B')  
AND ola.flow_status_code = 'CLOSED' –Change condition i.e.,  'AWAITING_SHIPPING'
AND oha.order_number = 12341719 --  pass  Order number
AND oha.org_id = 201;  -- pass Org id
Sample Script :2
SELECT wnd.delivery_id,
  wnd.name delivery_name,
  wdd.source_header_number so_order_number,
  oola.line_number so_line_number,
  wdd.source_header_id so_header_id,
  wdd.source_line_id so_line_id,
  wdd.shipping_instructions,
  wdd.inventory_item_id,
  wdd.requested_quantity_uom,
  msi.description item_description,
  msi.revision_qty_control_code ,
  wdd.ship_method_code carrier,
  wdd.shipment_priority_code priority,
  wdd.organization_id,
  wnd.initial_pickup_location_id,
  Decode (wdd.released_status,'C','Shipped','B','Backordered','D','Cancelled','N','Not Ready for Release','R','Ready to Release',
  'S','Released to Warehouse','X','Not Applicable','Y','Staged')as Released_Status,
  wdd.source_code
FROM mtl_system_items_vl msi,
                oe_order_lines_all oola,
  wsh_delivery_details wdd,
                 wsh_delivery_assignments wda,
                wsh_new_deliveries wnd
WHERE wnd.delivery_id      = 2323   --- pass delivery ID
AND wda.delivery_id        = wnd.delivery_id(+)
AND wdd.delivery_detail_id = wda.delivery_detail_id
AND wdd.inventory_item_id  = msi.inventory_item_id(+)
AND wdd.organization_id    = msi.organization_id(+)
AND wdd.source_line_id     = oola.line_id
AND wdd.source_header_id   = oola.header_id;

References:
http://appsraj.blogspot.com/2014/07/pick-release-sales-order-oracle-apps.html

1 comment:

  1. Thank you for sharing this informative information with us. Its very helpful. ERP with Sales

    ReplyDelete