Query to get the workflow status of a OM sales order

SELECT 1 TYPE,

       soh.header_id,

       sol.line_id,

       soh.order_number,

       tt.name,

       sol.line_number,

       soh.ordered_date,

       mtlv.segment1,

       mtlv.description,

       sol.ordered_quantity,

       sol.order_quantity_uom,

       (sol.ordered_quantity * sol.unit_selling_price) line_value,

       soh.transactional_curr_code,

       sol.booked_flag,

       sol.line_category_code,

       (NVL (sol.shipment_number, -1)) shipment_number,

       (NVL (sol.option_number, -1)) option_number,

       (NVL (sol.component_number, -1)) component_number,

       (NVL (sol.service_number, -1)) service_number,

       mtlv.concatenated_segments

  FROM oe_order_lines_all sol,

       oe_order_headers_all soh,

       oe_transaction_types_tl tt,

       mtl_system_items_vl mtlv

 WHERE :P_ACTIONS = 'ENTERED_NOT_BOOKED'

       AND (    soh.header_id = sol.header_id

            AND sol.booked_flag = 'N'

            AND sol.flow_status_code = 'ENTERED'

            AND mtlv.inventory_item_id = sol.inventory_item_id

            AND tt.language = USERENV ('LANG')

            AND tt.transaction_type_id = soh.order_type_id

            AND sol.open_flag = 'Y'

            AND soh.ordered_date <= (SYSDATE - :P_OVERDUE_DAYS))

       AND NVL (soh.org_id, 0) = NVL (:p_org_id, 0)

       AND NVL (sol.org_id, 0) = NVL (:p_org_id, 0)

UNION

SELECT 2 TYPE,

       soh.header_id,

       sol.line_id,

       soh.order_number,

       tt.name,

       sol.line_number,

       sol.request_date,

       mtlv.segment1,

       mtlv.description,

       sol.ordered_quantity,

       sol.order_quantity_uom,

       (sol.ordered_quantity * sol.unit_selling_price) line_value,

       soh.transactional_curr_code,

       sol.booked_flag,

       sol.line_category_code,

       (NVL (sol.shipment_number, -1)) shipment_number,

       (NVL (sol.option_number, -1)) option_number,

       (NVL (sol.component_number, -1)) component_number,

       (NVL (sol.service_number, -1)) service_number,

       mtlv.concatenated_segments

  FROM oe_order_lines_all sol,

       oe_order_headers_all soh,

       oe_transaction_types_tl tt,

       mtl_system_items_vl mtlv

 WHERE :P_ACTIONS = 'BOOKED_NOT_SCHED'

       AND (    soh.header_id = sol.header_id

            AND sol.booked_flag = 'Y'

            AND sol.flow_status_code = 'BOOKED'

            AND NVL (sol.schedule_status_code, 'N') = 'N'

            AND mtlv.inventory_item_id = sol.inventory_item_id

            AND tt.language = USERENV ('LANG')

            AND tt.transaction_type_id = soh.order_type_id

            AND sol.open_flag = 'Y'

            AND sol.request_date <= (SYSDATE - :P_OVERDUE_DAYS))

       AND NVL (soh.org_id, 0) = NVL (:p_org_id, 0)

       AND NVL (sol.org_id, 0) = NVL (:p_org_id, 0)

UNION

SELECT 3 TYPE,

       soh.header_id,

       sol.line_id,

       soh.order_number,

       tt.name,

       sol.line_number,

       sol.schedule_ship_date,

       mtlv.segment1,

       mtlv.description,

       sol.ordered_quantity,

       sol.order_quantity_uom,

       (sol.ordered_quantity * sol.unit_selling_price) line_value,

       soh.transactional_curr_code,

       sol.booked_flag,

       sol.line_category_code,

       (NVL (sol.shipment_number, -1)) shipment_number,

       (NVL (sol.option_number, -1)) option_number,

       (NVL (sol.component_number, -1)) component_number,

       (NVL (sol.service_number, -1)) service_number,

       mtlv.concatenated_segments

  FROM oe_order_lines_all sol,

       oe_order_headers_all soh,

       oe_transaction_types_tl tt,

       mtl_system_items_vl mtlv

 WHERE :P_ACTIONS = 'SCHED_NOT_PK'

       AND (    soh.header_id = sol.header_id

            AND NVL (sol.schedule_status_code, 'N') = 'SCHEDULED'

            AND NVL (sol.shipping_interfaced_flag, 'N') = 'N'

            AND mtlv.inventory_item_id = sol.inventory_item_id

            AND mtlv.organization_id = sol.ship_from_org_id

            AND tt.language = USERENV ('LANG')

            AND tt.transaction_type_id = soh.order_type_id

            AND sol.open_flag = 'Y'

            AND sol.schedule_ship_date <= (SYSDATE - :P_OVERDUE_DAYS))

       AND NVL (soh.org_id, 0) = NVL (:p_org_id, 0)

       AND NVL (sol.org_id, 0) = NVL (:p_org_id, 0)

UNION

SELECT 3 TYPE,

       soh.header_id,

       sol.line_id,

       soh.order_number,

       tt.name,

       sol.line_number,

       sol.schedule_ship_date,

       mtlv.segment1,

       mtlv.description,

       sol.ordered_quantity,

       sol.order_quantity_uom,

       (sol.ordered_quantity * sol.unit_selling_price) line_value,

       soh.transactional_curr_code,

       sol.booked_flag,

       sol.line_category_code,

       (NVL (sol.shipment_number, -1)) shipment_number,

       (NVL (sol.option_number, -1)) option_number,

       (NVL (sol.component_number, -1)) component_number,

       (NVL (sol.service_number, -1)) service_number,

       mtlv.concatenated_segments

  FROM wsh_delivery_details wdd,

       oe_order_lines_all sol,

       oe_order_headers_all soh,

       oe_transaction_types_tl tt,

       mtl_system_items_vl mtlv

 WHERE :P_ACTIONS = 'SCHED_NOT_PK'

       AND (    wdd.released_status IN ('R', 'B')

            AND soh.header_id = wdd.source_header_id

            AND tt.language = USERENV ('LANG')

            AND tt.transaction_type_id = soh.order_type_id

            AND wdd.source_line_id = sol.line_id

            AND NVL (sol.schedule_status_code, 'N') = 'SCHEDULED'

            AND mtlv.inventory_item_id = sol.inventory_item_id

            AND mtlv.organization_id = sol.ship_from_org_id

            AND sol.open_flag = 'Y'

            AND sol.schedule_ship_date <= (SYSDATE - :P_OVERDUE_DAYS)

            AND wdd.delivery_detail_id IN

                   (SELECT MIN (wdd1.delivery_detail_id)

                      FROM wsh_delivery_details wdd1

                     WHERE     wdd.source_line_id = wdd1.source_line_id

                           AND wdd1.released_status IN ('R', 'B')

                           AND wdd1.source_code = 'OE'))

       AND NVL (soh.org_id, 0) = NVL (:p_org_id, 0)

       AND NVL (sol.org_id, 0) = NVL (:p_org_id, 0)

UNION

SELECT 4 TYPE,

       soh.header_id,

       sol.line_id,

       soh.order_number,

       tt.name,

       sol.line_number,

       moline.creation_date,

       mtlv.segment1,

       mtlv.description,

       sol.ordered_quantity,

       sol.order_quantity_uom,

       (sol.ordered_quantity * sol.unit_selling_price) line_value,

       soh.transactional_curr_code,

       sol.booked_flag,

       sol.line_category_code,

       (NVL (sol.shipment_number, -1)) shipment_number,

       (NVL (sol.option_number, -1)) option_number,

       (NVL (sol.component_number, -1)) component_number,

       (NVL (sol.service_number, -1)) service_number,

       mtlv.concatenated_segments

  FROM wsh_delivery_details wdd,

       oe_order_lines_all sol,

       oe_order_headers_all soh,

       oe_transaction_types_tl tt,

       mtl_system_items_vl mtlv,

       mtl_txn_request_lines moline

 WHERE :P_ACTIONS = 'PK_NOT_SP_CONFIRM'

       AND (    wdd.released_status IN ('S', 'Y')

            AND wdd.source_line_id = sol.line_id

            AND soh.header_id = sol.header_id

            AND sol.flow_status_code = 'AWAITING_SHIPPING'

            AND mtlv.inventory_item_id = wdd.inventory_item_id

            AND mtlv.organization_id = wdd.organization_id

            AND tt.language = USERENV ('LANG')

            AND tt.transaction_type_id = soh.order_type_id

            AND moline.txn_source_line_id = sol.line_id

            AND wdd.move_order_line_id = moline.line_id

            AND sol.open_flag = 'Y'

            AND moline.creation_date <= SYSDATE - :P_OVERDUE_DAYS

            AND wdd.delivery_detail_id IN

                   (SELECT MIN (wdd1.delivery_detail_id)

                      FROM wsh_delivery_details wdd1

                     WHERE     wdd1.source_line_id = wdd.source_line_id

                           AND wdd1.released_status IN ('S', 'Y')

                           AND wdd1.source_code = 'OE'))

       AND NVL (soh.org_id, 0) = NVL (:p_org_id, 0)

       AND NVL (sol.org_id, 0) = NVL (:p_org_id, 0)

UNION

SELECT 4 TYPE,

       soh.header_id,

       sol.line_id,

       soh.order_number,

       tt.name,

       sol.line_number,

       moline.creation_date,

       mtlv.segment1,

       mtlv.description,

       sol.ordered_quantity,

       sol.order_quantity_uom,

       (sol.ordered_quantity * sol.unit_selling_price) line_value,

       soh.transactional_curr_code,

       sol.booked_flag,

       sol.line_category_code,

       (NVL (sol.shipment_number, -1)) shipment_number,

       (NVL (sol.option_number, -1)) option_number,

       (NVL (sol.component_number, -1)) component_number,

       (NVL (sol.service_number, -1)) service_number,

       mtlv.concatenated_segments

  FROM wsh_delivery_details wdd,

       oe_order_lines_all sol,

       oe_order_headers_all soh,

       oe_transaction_types_tl tt,

       mtl_system_items_vl mtlv,

       mtl_txn_request_lines moline

 WHERE :P_ACTIONS = 'PK_NOT_SHIP_CONFIRM'

       AND (    wdd.released_status IN ('S', 'Y')

            AND wdd.source_line_id = sol.line_id

            AND soh.header_id = sol.header_id

            AND sol.flow_status_code = 'AWAITING_SHIPPING'

            AND mtlv.inventory_item_id = wdd.inventory_item_id

            AND mtlv.organization_id = wdd.organization_id

            AND tt.language = USERENV ('LANG')

            AND tt.transaction_type_id = soh.order_type_id

            AND moline.txn_source_line_id = sol.line_id

            AND wdd.move_order_line_id = moline.line_id

            AND sol.open_flag = 'Y'

            AND moline.creation_date <= SYSDATE - :P_OVERDUE_DAYS

            AND wdd.delivery_detail_id IN

                   (SELECT MIN (wdd1.delivery_detail_id)

                      FROM wsh_delivery_details wdd1

                     WHERE     wdd1.source_line_id = wdd.source_line_id

                           AND wdd1.released_status IN ('S', 'Y')

                           AND wdd1.source_code = 'OE'))

       AND NVL (soh.org_id, 0) = NVL (:p_org_id, 0)

       AND NVL (sol.org_id, 0) = NVL (:p_org_id, 0)

UNION

SELECT 6 TYPE,

       soh.header_id,

       sol.line_id,

       soh.order_number,

       tt.name,

       sol.line_number,

       wts.actual_departure_date,

       mtlv.segment1,

       mtlv.description,

       sol.ordered_quantity,

       sol.order_quantity_uom,

       (sol.ordered_quantity * sol.unit_selling_price) line_value,

       soh.transactional_curr_code,

       sol.booked_flag,

       sol.line_category_code,

       (NVL (sol.shipment_number, -1)) shipment_number,

       (NVL (sol.option_number, -1)) option_number,

       (NVL (sol.component_number, -1)) component_number,

       (NVL (sol.service_number, -1)) service_number,

       mtlv.concatenated_segments

  FROM wsh_delivery_details wdd,

       wsh_trip_stops wts,

       wsh_delivery_legs wdl,

       wsh_new_deliveries wnd,

       wsh_delivery_assignments wna,

       oe_order_lines_all sol,

       oe_order_headers_all soh,

       oe_transaction_types_tl tt,

       mtl_system_items_vl mtlv

 WHERE :P_ACTIONS = 'SP_NOT_OM_INT'

       AND (    wdd.oe_interfaced_flag = 'N'

            AND wdd.released_status = 'C'

            AND wna.delivery_detail_id = wdd.delivery_detail_id

            AND wts.actual_departure_date <= (SYSDATE - :P_OVERDUE_DAYS)

            AND wnd.delivery_id = wna.delivery_id

            AND wdl.delivery_id = wnd.delivery_id

            AND wdl.sequence_number <= 10

            AND wts.stop_id = wdl.pick_up_stop_id

            AND wdd.source_line_id = sol.line_id

            AND soh.header_id = sol.header_id

            AND sol.flow_status_code = 'AWAITING_SHIPPING'

            AND mtlv.inventory_item_id = wdd.inventory_item_id

            AND mtlv.organization_id = wdd.organization_id

            AND tt.language = USERENV ('LANG')

            AND tt.transaction_type_id = soh.order_type_id

            AND sol.open_flag = 'Y'

            AND wdd.delivery_detail_id IN

                   (SELECT MIN (wdd1.delivery_detail_id)

                      FROM wsh_delivery_details wdd1

                     WHERE     wdd.source_line_id = wdd1.source_line_id

                           AND wdd1.released_status = 'C'

                           AND wdd1.source_code = 'OE'))

       AND NVL (soh.org_id, 0) = NVL (:p_org_id, 0)

       AND NVL (sol.org_id, 0) = NVL (:p_org_id, 0)

UNION

SELECT 7 TYPE,

       soh.header_id,

       sol.line_id,

       soh.order_number,

       tt.name,

       sol.line_number,

       wts.actual_departure_date,

       mtlv.segment1,

       mtlv.description,

       sol.ordered_quantity,

       sol.order_quantity_uom,

       (sol.ordered_quantity * sol.unit_selling_price) line_value,

       soh.transactional_curr_code,

       sol.booked_flag,

       sol.line_category_code,

       (NVL (sol.shipment_number, -1)) shipment_number,

       (NVL (sol.option_number, -1)) option_number,

       (NVL (sol.component_number, -1)) component_number,

       (NVL (sol.service_number, -1)) service_number,

       mtlv.concatenated_segments

  FROM wsh_delivery_details wdd,

       wsh_trip_stops wts,

       wsh_delivery_legs wdl,

       wsh_new_deliveries wnd,

       wsh_delivery_assignments wna,

       oe_order_lines_all sol,

       oe_order_headers_all soh,

       oe_transaction_types_tl tt,

       mtl_system_items_vl mtlv

 WHERE :P_ACTIONS = 'SP_CONFIRM_NOT_INVOICE_INT'

       AND (    wdd.released_status = 'C'

            AND wna.delivery_detail_id = wdd.delivery_detail_id

            AND wts.actual_departure_date <= (SYSDATE - :P_OVERDUE_DAYS)

            AND wnd.delivery_id = wna.delivery_id

            AND wdl.delivery_id = wnd.delivery_id

            AND wdl.sequence_number <= 10                         -- first leg

            AND wts.stop_id = wdl.pick_up_stop_id

            AND sol.line_id = wdd.source_line_id

            AND wdd.source_header_id = soh.header_id

            AND soh.header_id = sol.header_id

            AND NVL (sol.invoice_interface_status_code, 'NO') = 'NO'

            AND mtlv.inventory_item_id = wdd.inventory_item_id

            AND mtlv.organization_id = wdd.organization_id

            AND tt.language = USERENV ('LANG')

            AND tt.transaction_type_id = soh.order_type_id

            AND sol.open_flag = 'Y'

            AND wdd.delivery_detail_id IN

                   (SELECT MIN (wdd1.delivery_detail_id)

                      FROM wsh_delivery_details wdd1

                     WHERE     wdd.source_line_id = wdd1.source_line_id

                           AND wdd1.released_status = 'C'

                           AND wdd1.source_code = 'OE'))

       AND NVL (soh.org_id, 0) = NVL (:p_org_id, 0)

       AND NVL (sol.org_id, 0) = NVL (:p_org_id, 0)

UNION

SELECT 12 TYPE,

       soh.header_id,

       sol.line_id,

       soh.order_number,

       tt.name,

       sol.line_number,

       sol.actual_shipment_date,

       mtlv.segment1,

       mtlv.description,

       sol.ordered_quantity,

       sol.order_quantity_uom,

       (sol.ordered_quantity * sol.unit_selling_price) line_value,

       soh.transactional_curr_code,

       sol.booked_flag,

       sol.line_category_code,

       (NVL (sol.shipment_number, -1)) shipment_number,

       (NVL (sol.option_number, -1)) option_number,

       (NVL (sol.component_number, -1)) component_number,

       (NVL (sol.service_number, -1)) service_number,

       mtlv.concatenated_segments

  FROM oe_order_lines_all sol,

       oe_order_headers_all soh,

       oe_transaction_types_tl tt,

       mtl_system_items_vl mtlv

 WHERE :P_ACTIONS = 'SP_NOT_CLOSED'

       AND (    tt.transaction_type_id = soh.order_type_id

            AND tt.language = USERENV ('LANG')

            AND sol.shipped_quantity IS NOT NULL

            AND NVL (sol.actual_shipment_date, SYSDATE) <

                   (SYSDATE - :P_OVERDUE_DAYS)

            AND soh.header_id = sol.header_id

            AND sol.open_flag = 'Y'

            AND mtlv.inventory_item_id = sol.inventory_item_id

            AND mtlv.organization_id = sol.ship_from_org_id)

       AND NVL (soh.org_id, 0) = NVL (:p_org_id, 0)

       AND NVL (sol.org_id, 0) = NVL (:p_org_id, 0)

UNION

SELECT 13 TYPE,

       soh.header_id,

       sol.line_id,

       soh.order_number,

       tt.name,

       sol.line_number,

       soh.ordered_date,

       mtlv.segment1,

       mtlv.description,

       sol.ordered_quantity,

       sol.order_quantity_uom,

       (sol.ordered_quantity * sol.unit_selling_price) line_value,

       soh.transactional_curr_code,

       sol.booked_flag,

       sol.line_category_code,

       (NVL (sol.shipment_number, -1)) shipment_number,

       (NVL (sol.option_number, -1)) option_number,

       (NVL (sol.component_number, -1)) component_number,

       (NVL (sol.service_number, -1)) service_number,

       mtlv.concatenated_segments

  FROM oe_order_lines_all sol,

       oe_order_headers_all soh,

       oe_transaction_types_tl tt,

       mtl_system_items_vl mtlv

 WHERE :P_ACTIONS = 'INVOICE_INT_NOT_CLOSED'

       AND (    tt.transaction_type_id = soh.order_type_id

            AND tt.language = USERENV ('LANG')

            AND soh.header_id = sol.header_id

            AND sol.open_flag = 'Y'

            AND sol.flow_status_code <> 'CLOSED'

            AND NVL (sol.invoice_interface_status_code, 'NO') = 'YES'

            AND mtlv.inventory_item_id = sol.inventory_item_id

            AND mtlv.organization_id = sol.ship_from_org_id)

       AND NVL (soh.org_id, 0) = NVL (:p_org_id, 0)

       AND NVL (sol.org_id, 0) = NVL (:p_org_id, 0)

UNION

SELECT 4 TYPE,

       soh.header_id,

       sol.line_id,

       soh.order_number,

       tt.name,

       sol.line_number,

       moline.creation_date,

       mtlv.segment1,

       mtlv.description,

       sol.ordered_quantity,

       sol.order_quantity_uom,

       (sol.ordered_quantity * sol.unit_selling_price) line_value,

       soh.transactional_curr_code,

       sol.booked_flag,

       sol.line_category_code,

       (NVL (sol.shipment_number, -1)) shipment_number,

       (NVL (sol.option_number, -1)) option_number,

       (NVL (sol.component_number, -1)) component_number,

       (NVL (sol.service_number, -1)) service_number,

       mtlv.concatenated_segments

  FROM wsh_delivery_details wdd,

       oe_order_lines_all sol,

       oe_order_headers_all soh,

       oe_transaction_types_tl tt,

       mtl_system_items_vl mtlv,

       ic_txn_request_lines moline

 WHERE :P_ACTIONS = 'PK_NOT_SP_CONFIRM'

       AND (    wdd.released_status IN ('S', 'Y')

            AND wdd.source_line_id = sol.line_id

            AND soh.header_id = sol.header_id

            AND sol.flow_status_code = 'AWAITING_SHIPPING'

            AND mtlv.inventory_item_id = wdd.inventory_item_id

            AND mtlv.organization_id = wdd.organization_id

            AND tt.language = USERENV ('LANG')

            AND tt.transaction_type_id = soh.order_type_id

            AND moline.txn_source_line_id = sol.line_id

            AND wdd.move_order_line_id = moline.line_id

            AND sol.open_flag = 'Y'

            AND moline.creation_date <= SYSDATE - :P_OVERDUE_DAYS

            AND wdd.delivery_detail_id IN

                   (SELECT MIN (wdd1.delivery_detail_id)

                      FROM wsh_delivery_details wdd1

                     WHERE     wdd1.source_line_id = wdd.source_line_id

                           AND wdd1.released_status IN ('S', 'Y')

                           AND wdd1.source_code = 'OE'))

       AND NVL (soh.org_id, 0) = NVL (:p_org_id, 0)

       AND NVL (sol.org_id, 0) = NVL (:p_org_id, 0)

UNION

SELECT 4 TYPE,

       soh.header_id,

       sol.line_id,

       soh.order_number,

       tt.name,

       sol.line_number,

       moline.creation_date,

       mtlv.segment1,

       mtlv.description,

       sol.ordered_quantity,

       sol.order_quantity_uom,

       (sol.ordered_quantity * sol.unit_selling_price) line_value,

       soh.transactional_curr_code,

       sol.booked_flag,

       sol.line_category_code,

       (NVL (sol.shipment_number, -1)) shipment_number,

       (NVL (sol.option_number, -1)) option_number,

       (NVL (sol.component_number, -1)) component_number,

       (NVL (sol.service_number, -1)) service_number,

       mtlv.concatenated_segments

  FROM wsh_delivery_details wdd,

       oe_order_lines_all sol,

       oe_order_headers_all soh,

       oe_transaction_types_tl tt,

       mtl_system_items_vl mtlv,

       ic_txn_request_lines moline

 WHERE :P_ACTIONS = 'PK_NOT_SHIP_CONFIRM'

       AND (    wdd.released_status IN ('S', 'Y')

            AND wdd.source_line_id = sol.line_id

            AND soh.header_id = sol.header_id

            AND sol.flow_status_code = 'AWAITING_SHIPPING'

            AND mtlv.inventory_item_id = wdd.inventory_item_id

            AND mtlv.organization_id = wdd.organization_id

            AND tt.language = USERENV ('LANG')

            AND tt.transaction_type_id = soh.order_type_id

            AND moline.txn_source_line_id = sol.line_id

            AND wdd.move_order_line_id = moline.line_id

            AND sol.open_flag = 'Y'

            AND moline.creation_date <= SYSDATE - :P_OVERDUE_DAYS

            AND wdd.delivery_detail_id IN

                   (SELECT MIN (wdd1.delivery_detail_id)

                      FROM wsh_delivery_details wdd1

                     WHERE     wdd1.source_line_id = wdd.source_line_id

                           AND wdd1.released_status IN ('S', 'Y')

                           AND wdd1.source_code = 'OE'))

       AND NVL (soh.org_id, 0) = NVL (:p_org_id, 0)

       AND NVL (sol.org_id, 0) = NVL (:p_org_id, 0)

ORDER BY 4,

         6,

         16,

         17,

         18,

         19