ONT Sales Order Workflow Status

Description
Categories: BI Publisher, Sales
Application: Order Management
Source: Sales Order Workflow Status Report (XML)
Short Name: OEXOEWFR_XML
DB package: ONT_OEXOEWFR_XMLP_PKG
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.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_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
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' -- 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 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,
ONT_OEXOEWFR_XMLP_PKG.cf_typeformula(12) 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='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' -- 9246299
AND mtlv.inventory_item_id=sol.inventory_item_id
AND mtlv.organization_id=sol.ship_from_org_id
&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 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,
ONT_OEXOEWFR_XMLP_PKG.cf_typeformula(13) 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='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' -- 9246299
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
&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
,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')
&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
,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' -- 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)
ORDER BY 4,6,16,17,18,19
Parameter NameSQL textValidation
Item Display
 
LOV Oracle
Line Category
 
LOV Oracle
To Order Number
 
LOV Oracle
From Order Number
 
LOV Oracle
Order Type
 
LOV Oracle
Actions
 
LOV Oracle
Overdue Days
 
Number