select e.request_id request_id,
e.ece_tp_translator_code trading_partner,
e.ece_tp_location_code_ext tp_location,
e.schedule_purpose schedule_purpose,
e.schedule_source schedule_source,
e.horizon_start_date horizon_start_date,
e.horizon_end_date horizon_end_date,
e.message_name message_name,
e.cust_ship_from_org_ext ship_from,
e.cust_ship_to_ext ship_to,
e.customer_item_ext customer_item,
e.inventory_item inventory_item,
e.item_detail_type item_detail_type,
e.item_detail_subtype item_detail_subtype,
e.item_detail_quantity item_detail_quantity,
e.qty_type_code qty_type_code,
e.interface_header_id interface_header_id,
e.interface_line_id interface_line_id,
e.schedule_header_id schedule_header_id,
e.schedule_line_id schedule_line_id,
e.start_date_time start_date_time,
e.message_text message_text,
e.order_header_id order_header_id,
e.order_line_id order_line_id,
e.exception_level exception_severity,
e.creation_date creation_date,
e.purge_status purge_status,
oe.order_number order_number,
oel.line_number order_line_num,
e.schedule_reference_num schedule_num,
e.schedule_type schedule_type,
e.sched_generation_date sched_generation_date,
sch.cust_name_ext customer_name,
e.schedule_line_number sch_line_num,
oev.name order_type,
RLM_RLMDPPSR_XMLP_PKG.cf_schedule_typeformula(e.schedule_type) CF_schedule_type,
RLM_RLMDPPSR_XMLP_PKG.cf_purge_eligibleformula(e.purge_status) CF_purge_eligible,
RLM_RLMDPPSR_XMLP_PKG.cf_purge_statusformula(e.purge_status) CF_purge_status
from rlm_demand_exceptions e, oe_order_headers_all oe, oe_order_lines_all oel, rlm_schedule_interface_hdr_v sch, oe_order_types_v oev
where e.request_id is not null
and e.order_header_id = oe.header_id(+)
and e.schedule_header_id = sch.header_id(+)
and e.order_line_id = oel.line_id(+)
and oe.order_type_id = oev.order_type_id(+)
and e.origin_table = sch.origin_table(+)
and sch.ORG_ID = oe.ORG_ID
and sch.ORG_ID = oel.ORG_ID
&p_where_clause
--order by 27 desc,30,32,31,2,33,1,22,8,19,35,29,34,28,3,4,5,6,7,9,10,11,12,13,14,15,16,17,18,21,23,24,25,26,20
ORDER BY 27 DESC,30 ASC,32 ASC,31 ASC,2 ASC,33 ASC,1 ASC,22 ASC,8 ASC,19 ASC |