ONT Sales Order Workflow Status

➜ Back to Library
Description
Categories: BI Publisher
Application: Order Management
Source: Sales Order Workflow Status Report (XML)
Short Name: OEXOEWFR_XML
DB package: ONT_OEXOEWFR_XMLP_PKG

Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS

Contact us to schedule a demo or if you need help with the installation

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 Name SQL text Validation
Overdue Days 
 
 Number
Actions 
 
 LOV Oracle
Order Type 
 
 LOV Oracle
From Order Number 
 
 LOV Oracle
To Order Number 
 
 LOV Oracle
Line Category 
 
 LOV Oracle
Item Display 
 
 LOV Oracle
Download
   
Blitz Report™

Blitz Report™ provides multiple benefits: