Find the Workflow of Order or Line in Order Apps

To find the workflow for Sales Order or Line

SELECT transaction_type_id, tl.*

  FROM oe_transaction_types_tl tl

 WHERE name LIKE '%Non%Standard%';

  SELECT *

    FROM oe_order_lines_all

   WHERE line_type_id = :transaction_type_id 

     AND creation_date IS NOT NULL

ORDER BY creation_date DESC;

SELECT *

  FROM oe_order_headers_all

 WHERE header_id = 2374832;

To open the workflow for specific order, search the Order Number' IN "Sales Order  or Order organizer" form under "OM Superuser" responsibility and switch to Line then under Tools Menu CHECK THE workflow.

Query to display Sales Order Line Number in Oracle Apps

SELECT  TO_CHAR (l.line_number) 
        || DECODE (l.shipment_number, NULL, NULL, '.' || TO_CHAR (l.shipment_number))
        || DECODE (l.option_number, NULL, NULL, '.' || TO_CHAR (l.option_number))
        || DECODE (l.component_number, NULL, NULL, 
                   DECODE (l.option_number, NULL, '.', NULL)|| '.' ||TO_CHAR (l.component_number))
        || DECODE (l.service_number, NULL, NULL, 
                   DECODE (l.component_number, NULL, '.', NULL)
        || DECODE (l.option_number, NULL, '.', NULL) || '.'|| TO_CHAR (l.service_number))
          line_number,
       h.order_number
  FROM oe_order_headers_all h, oe_order_lines_all l
 WHERE     h.header_id = l.header_id
       AND l.open_flag = 'Y'
       AND h.header_id = :P_header_id
;

Query to find delivery details along with Ship method in Oracle Apps

It will find the list of all delivery details for specified SO orders if any


  SELECT wdd.source_header_type_name ORDER_TYPE,
         wdd.source_header_number ORDER_NUMBER,
         wdd.source_line_number ORDER_LINE_NUMBER,
         wdd.CUST_PO_NUMBER, 
         wnd.delivery_id,
         wnd.ship_method_code,
         wnd.waybill,
         wdd.ORG_ID OPERATING_UNIT_ID,
         wdd.SUBINVENTORY,
         wdd.SHIPPED_QUANTITY
    FROM APPS.wsh_new_deliveries wnd,
         APPS.wsh_delivery_assignments wda,
         APPS.wsh_delivery_details wdd
   WHERE     wnd.delivery_id = wda.delivery_id
         AND wda.delivery_detail_id = wdd.delivery_detail_id
         AND wdd.ORG_ID IN (27, 121, 36, 201) -- US OU, US DEVICES OU, Germany OU, EUR DEVICES OU
         AND wnd.confirm_date > '31-AUG-2007'
         AND wdd.source_header_number = :P_Order_Number
ORDER BY wdd.SOURCE_HEADER_NUMBER, wdd.SOURCE_LINE_NUMBER
;
--------------------------------------------------------------------------------------------------------