ENGINATICS
SYSTEM_ADMINISTRATOR
SYSADMIN
82288223F25B3869E053B46B63588994
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
Y
ONT
OEXOEWFR_XML
AR
ONT تقرير موقف دفق الأعمال لأوامر المبيعات
Application: إدارة الطلبات
Source: تقرير موقف دفق الأعمال لأوامر المبيعات (XML)
Short Name: OEXOEWFR_XML
DB package: ONT_OEXOEWFR_XMLP_PKG
D
ONT Workflowstatus für Kundenaufträge
Application: Order Management
Source: Workflowstatus für Kundenaufträge (XML)
Short Name: OEXOEWFR_XML
DB package: ONT_OEXOEWFR_XMLP_PKG
F
ONT Etat Statut du workflow des commandes client
Application: Order Management
Source: Etat Statut du workflow des commandes client (XML)
Short Name: OEXOEWFR_XML
DB package: ONT_OEXOEWFR_XMLP_PKG
US
ONT Sales Order Workflow Status
Application: Order Management
Source: Sales Order Workflow Status Report (XML)
Short Name: OEXOEWFR_XML
DB package: ONT_OEXOEWFR_XMLP_PKG
ZHS
ONT 销售订单工作流状态报表
Application: 订单管理
Source: 销售订单工作流状态报表 (XML)
Short Name: OEXOEWFR_XML
DB package: ONT_OEXOEWFR_XMLP_PKG
BI Publisher
¶m_where_clause
:cp_type
:operating_unit
:p_actions
:p_conc_request_id
:p_item
:p_line_category
:p_no_data
:p_order_no_high
:p_order_no_low
:p_order_type
:p_org_id
:p_overdue_days
:p_report1
:p_report10
:p_report11
:p_report12
:p_report13
:p_report2
:p_report3
:p_report4
:p_report5
:p_report6
:p_report7
:p_report8
:p_report9
:p_sob_id
:param_where_clause
:rp_company_name
1
10
:p_overdue_days
Number
Y
AR
الأيام متجاوزة الاستحقاق
D
Verzugstage
F
Jours de retard
US
Overdue Days
ZHS
逾期天数
2
20
:p_actions
LOV Oracle
ONT_SRS_ACTIONS
Y
select
lookup_code id,
meaning value,
description description
from
oe_lookups
where lookup_type='REPORT_ACTION'
order by meaning
Y
AR
الإجراءات
D
Vorgänge
US
Actions
ZHS
操作
3
30
:p_order_type
LOV Oracle
ONT_MOAC_SRS_TRANSACTION_TYPES
Y
select
ot.name id,
ot.name value,
null description
from
oe_transaction_types_vl ot,hr_operating_units ou
where
ot.org_id=ou.organization_id
and (:$flex$.ont_moac_srs_organizations:null is null or :$flex$.ont_moac_srs_organizations:null=ot.org_id)
order by ot.name
AR
نوع الطلب
D
Auftragsart
F
Type de commande
US
Order Type
ZHS
订单类型
4
40
:p_order_no_low
LOV Oracle
ONT_MOAC_SRS_ORDER_NUMBER
Y
select
order_number id,
order_number value,
null description
from
oe_order_headers oh,hr_operating_units ou
where oh.org_id=ou.organization_id
and (:$flex$.ont_moac_srs_organizations:null is null or :$flex$.ont_moac_srs_organizations:null=oh.org_id) order by oh.order_number desc
AR
من رقم الطلب
D
Von Auftragsnummer
F
Numéro commande - De
US
From Order Number
ZHS
订单编号自
5
50
:p_order_no_high
LOV Oracle
ONT_MOAC_SRS_ORDER_NUMBER
Y
select
order_number id,
order_number value,
null description
from
oe_order_headers oh,hr_operating_units ou
where oh.org_id=ou.organization_id
and (:$flex$.ont_moac_srs_organizations:null is null or :$flex$.ont_moac_srs_organizations:null=oh.org_id) order by oh.order_number desc
AR
إلى رقم الطلب
D
Bis Auftragsnummer
F
Numéro commande - A
US
To Order Number
ZHS
终止订单编号
6
60
:p_line_category
LOV Oracle
ONT_SRS_LINE_CATEGORY_RPT
Y
select
ffvv.flex_value id,
ffvv.flex_value value,
ffvv.flex_value_meaning||nvl2(ffvv.description,': '||ffvv.description,null) description
from
fnd_flex_values_vl ffvv
where
ffvv.flex_value_set_id=1011149
order by
ffvv.flex_value
AR
فئة السطر
D
Positionskategorie
F
Catégorie de ligne
US
Line Category
ZHS
行类别
7
70
:p_item
LOV Oracle
ONT_SRS_ITEM_CHOICE
Y
select
ffvv.flex_value id,
ffvv.flex_value value,
ffvv.flex_value_meaning||nvl2(ffvv.description,': '||ffvv.description,null) description
from
fnd_flex_values_vl ffvv
where
ffvv.flex_value_set_id=1011148
order by
ffvv.flex_value
AR
عرض الصنف
D
Artikelanzeige
F
Affichage article
US
Item Display
ZHS
项目显示