ONT Sales Order Workflow Status

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,
ONT_OEXOEWFR_XMLP_PKG.cf_typeformula(1) CF_Type,
ONT_OEXOEWFR_XMLP_PKG.cf_booked_displayformula(sol.booked_flag) CF_booked_display,
ONT_OEXOEWFR_XMLP_PKG.cf_item_y_nformula(mtlv.concatenated_segments,mtlv.description) CF_ITEM_Y_N,
ONT_OEXOEWFR_XMLP_PKG.cf_line_numberformula(sol.line_number,(nvl (sol.shipment_number ,- 1 ) ),(nvl (sol.option_number ,- 1 ) ),(nvl (sol.component_number ,- 1 ) ),(nvl (sol.service_number ,- 1 ) )) CF_line_number,
ONT_OEXOEWFR_XMLP_PKG.cf_ordered_dateformula(sol.line_id,soh.header_id,soh.ordered_date) CF_ordered_date,
ONT_OEXOEWFR_XMLP_PKG.cf_extended_valueformula(soh.header_id,sol.line_id,(sol.ordered_quantity * sol.unit_selling_price ),sol.line_category_code) CF_EXTENDED_VALUE,
ONT_OEXOEWFR_XMLP_PKG.cf_hold_valueformula(soh.header_id,sol.line_id) CF_HOLD_VALUE
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' -- 9246299
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' -- 9246299
AND soh.ordered_date <= (sysdate - :P_OVERDUE_DAYS)
&PARAM_WHERE_CLAUSE
)
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,
ONT_OEXOEWFR_XMLP_PKG.cf_typeformula(2) CF_Type,
ONT_OEXOEWFR_XMLP_PKG.cf_booked_displayformula(sol.booked_flag) CF_booked_display,
ONT_OEXOEWFR_XMLP_PKG.cf_item_y_nformula(mtlv.concatenated_segments,mtlv.description) CF_ITEM_Y_N,
ONT_OEXOEWFR_XMLP_PKG.cf_line_numberformula(sol.line_number,(nvl (sol.shipment_number ,- 1 ) ),(nvl (sol.option_number ,- 1 ) ),(nvl (sol.component_number ,- 1 ) ),(nvl (sol.service_number ,- 1 ) )) CF_line_number,
ONT_OEXOEWFR_XMLP_PKG.cf_ordered_dateformula(sol.line_id,soh.header_id,soh.ordered_date) CF_ordered_date,
ONT_OEXOEWFR_XMLP_PKG.cf_extended_valueformula(soh.header_id,sol.line_id,(sol.ordered_quantity * sol.unit_selling_price ),sol.line_category_code) CF_EXTENDED_VALUE,
ONT_OEXOEWFR_XMLP_PKG.cf_hold_valueformula(soh.header_id,sol.line_id) CF_HOLD_VALUE
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' -- 9246299
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' -- 9246299
AND sol.request_date <= (sysdate - :P_OVERDUE_DAYS)
&PARAM_WHERE_CLAUSE
)
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,
ONT_OEXOEWFR_XMLP_PKG.cf_typeformula(3) CF_Type,
ONT_OEXOEWFR_XMLP_PKG.cf_booked_displayformula(sol.booked_flag) CF_booked_display,
ONT_OEXOEWFR_XMLP_PKG.cf_item_y_nformula(mtlv.concatenated_segments,mtlv.description) CF_ITEM_Y_N,
ONT_OEXOEWFR_XMLP_PKG.cf_line_numberformula(sol.line_number,(nvl (sol.shipment_number ,- 1 ) ),(nvl (sol.option_number ,- 1 ) ),(nvl (sol.component_number ,- 1 ) ),(nvl (sol.service_number ,- 1 ) )) CF_line_number,
ONT_OEXOEWFR_XMLP_PKG.cf_ordered_dateformula(sol.line_id,soh.header_id,soh.ordered_date) CF_ordered_date,
ONT_OEXOEWFR_XMLP_PKG.cf_extended_valueformula(soh.header_id,sol.line_id,(sol.ordered_quantity * sol.unit_selling_price ),sol.line_category_code) CF_EXTENDED_VALUE,
ONT_OEXOEWFR_XMLP_PKG.cf_hold_valueformula(soh.header_id,sol.line_id) CF_HOLD_VALUE
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' -- 9246299
AND sol.schedule_ship_date <= (sysdate - :P_OVERDUE_DAYS)
&PARAM_WHERE_CLAUSE
)
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,
ONT_OEXOEWFR_XMLP_PKG.cf_typeformula(3) CF_Type,
ONT_OEXOEWFR_XMLP_PKG.cf_booked_displayformula(sol.booked_flag) CF_booked_display,
ONT_OEXOEWFR_XMLP_PKG.cf_item_y_nformula(mtlv.concatenated_segments,mtlv.description) CF_ITEM_Y_N,
ONT_OEXOEWFR_XMLP_PKG.cf_line_numberformula(sol.line_number,(nvl (sol.shipment_number ,- 1 ) ),(nvl (sol.option_number ,- 1 ) ),(nvl (sol.component_number ,- 1 ) ),(nvl (sol.service_number ,- 1 ) )) CF_line_number,
ONT_OEXOEWFR_XMLP_PKG.cf_ordered_dateformula(sol.line_id,soh.header_id,soh.ordered_date) CF_ordered_date,
ONT_OEXOEWFR_XMLP_PKG.cf_extended_valueformula(soh.header_id,sol.line_id,(sol.ordered_quantity * sol.unit_selling_price ),sol.line_category_code) CF_EXTENDED_VALUE,
ONT_OEXOEWFR_XMLP_PKG.cf_hold_valueformula(soh.header_id,sol.line_id) CF_HOLD_VALUE
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' -- 9246299
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')
&PARAM_WHERE_CLAUSE
)
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,
ONT_OEXOEWFR_XMLP_PKG.cf_typeformula(4) CF_Type,
ONT_OEXOEWFR_XMLP_PKG.cf_booked_displayformula(sol.booked_flag) CF_booked_display,
ONT_OEXOEWFR_XMLP_PKG.cf_item_y_nformula(mtlv.concatenated_segments,mtlv.description) CF_ITEM_Y_N,
ONT_OEXOEWFR_XMLP_PKG.cf_line_numberformula(sol.line_number,(nvl (sol.shipment_number ,- 1 ) ),(nvl (sol.option_number ,- 1 ) ),(nvl (sol.component_number ,- 1 ) ),(nvl (sol.service_number ,- 1 ) )) CF_line_number,
ONT_OEXOEWFR_XMLP_PKG.cf_ordered_dateformula(sol.line_id,soh.header_id,soh.ordered_date) CF_ordered_date,
ONT_OEXOEWFR_XMLP_PKG.cf_extended_valueformula(soh.header_id,sol.line_id,(sol.ordered_quantity * sol.unit_selling_price ),sol.line_category_code) CF_EXTENDED_VALUE,
ONT_OEXOEWFR_XMLP_PKG.cf_hold_valueformula(soh.header_id,sol.line_id) CF_HOLD_VALUE
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' -- 9246299
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')
&PARAM_WHERE_CLAUSE
)
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,
ONT_OEXOEWFR_XMLP_PKG.cf_typeformula(4) CF_Type,
ONT_OEXOEWFR_XMLP_PKG.cf_booked_displayformula(sol.booked_flag) CF_booked_display,
ONT_OEXOEWFR_XMLP_PKG.cf_item_y_nformula(mtlv.concatenated_segments,mtlv.description) CF_ITEM_Y_N,
ONT_OEXOEWFR_XMLP_PKG.cf_line_numberformula(sol.line_number,(nvl (sol.shipment_number ,- 1 ) ),(nvl (sol.option_number ,- 1 ) ),(nvl (sol.component_number ,- 1 ) ),(nvl (sol.service_number ,- 1 ) )) CF_line_number,
ONT_OEXOEWFR_XMLP_PKG.cf_ordered_dateformula(sol.line_id,soh.header_id,soh.ordered_date) CF_ordered_date,
ONT_OEXOEWFR_XMLP_PKG.cf_extended_valueformula(soh.header_id,sol.line_id,(sol.ordered_quantity * sol.unit_selling_price ),sol.line_category_code) CF_EXTENDED_VALUE,
ONT_OEXOEWFR_XMLP_PKG.cf_hold_valueformula(soh.header_id,sol.line_id) CF_HOLD_VALUE
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' -- 9246299
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')
&PARAM_WHERE_CLAUSE
)
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,
ONT_OEXOEWFR_XMLP_PKG.cf_typeformula(6) CF_Type,
ONT_OEXOEWFR_XMLP_PKG.cf_booked_displayformula(sol.booked_flag) CF_booked_display,
ONT_OEXOEWFR_XMLP_PKG.cf_item_y_nformula(mtlv.concatenated_segments,mtlv.description) CF_ITEM_Y_N,
ONT_OEXOEWFR_XMLP_PKG.cf_line_numberformula(sol.line_number,(nvl (sol.shipment_number ,- 1 ) ),(nvl (sol.option_number ,- 1 ) ),(nvl (sol.component_number ,- 1 ) ),(nvl (sol.service_number ,- 1 ) )) CF_line_number,
ONT_OEXOEWFR_XMLP_PKG.cf_ordered_dateformula(sol.line_id,soh.header_id,soh.ordered_date) CF_ordered_date,
ONT_OEXOEWFR_XMLP_PKG.cf_extended_valueformula(soh.header_id,sol.line_id,(sol.ordered_quantity * sol.unit_selling_price ),sol.line_category_code) CF_EXTENDED_VALUE,
ONT_OEXOEWFR_XMLP_PKG.cf_hold_valueformula(soh.header_id,sol.line_id) CF_HOLD_VALUE
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' -- 9246299
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')
&PARAM_WHERE_CLAUSE
)
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,
ONT_OEXOEWFR_XMLP_PKG.cf_typeformula(7) CF_Type,
ONT_OEXOEWFR_XMLP_PKG.cf_booked_displayformula(sol.booked_flag) CF_booked_display,
ONT_OEXOEWFR_XMLP_PKG.cf_item_y_nformula(mtlv.concatenated_segments,mtlv.description) CF_ITEM_Y_N,
ONT_OEXOEWFR_XMLP_PKG.cf_line_numberformula(sol.line_number,(nvl (sol.shipment_number ,- 1 ) ),(nvl (sol.option_number ,- 1 ) ),(nvl (sol.component_number ,- 1 ) ),(nvl (sol.service_number ,- 1 ) )) CF_line_number,
ONT_OEXOEWFR_XMLP_PKG.cf_ordered_dateformula(sol.line_id,soh.header_id,soh.ordered_date) CF_ordered_date,
ONT_OEXOEWFR_XMLP_PKG.cf_extended_valueformula(soh.header_id,sol.line_id,(sol