SELECT e.exception_id exception_id,
e.request_id request_id,
substrb(e.cust_name_ext, 1, 60) customer_name,
e.ece_tp_translator_code trading_partner,
e.ece_tp_location_code_ext tp_location,
e.schedule_reference_num schedule_num,
e.schedule_type schedule_type,
e.schedule_purpose schedule_purpose,
e.schedule_source schedule_source,
e.horizon_start_date horizon_start_date,
to_char(e.horizon_end_date,'DD-MON-YY') horizon_end_date,
e.sched_generation_date sched_generation_date,
e.schedule_line_number schedule_line_num,
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.cust_intermd_shipto_ext sched_intermd_shipto,
e.cust_bill_to_ext sched_billto,
e.cust_uom_ext sched_cust_uom,
e.cust_job_number sched_cust_job,
e.customer_prod_seq_num sched_cust_prod_seq,
e.cust_model_serial_num sched_model_serial,
TO_DATE(null) sched_request_date,
TO_DATE(null) sched_schedule_date,
e.item_detail_quantity sched_order_quantity,
TO_DATE(null) sched_primary_quantity,
TO_CHAR(null) sched_cust_po,
TO_CHAR(null) sched_record_year,
TO_CHAR(null) sched_prod_line,
TO_CHAR(null) sched_dock_code,
TO_NUMBER(null) oe_order_number,
TO_NUMBER(null) order_type_id,
TO_NUMBER(null) oe_line_number,
TO_NUMBER(null) order_quantity,
TO_DATE(null) order_request_date,
TO_DATE(null) order_ship_date,
TO_NUMBER(null) order_ship_quantity,
TO_NUMBER(null) order_cancel_quantity,
TO_CHAR(null) order_cust_po,
TO_CHAR(null) order_record_year,
TO_CHAR(null) order_cust_uom,
TO_CHAR(null) order_prod_line,
TO_CHAR(null) order_dock_code,
TO_CHAR(null) order_prod_seq,
TO_CHAR(null) order_cust_job,
TO_CHAR(null) order_model_serial,
NVL(msg.message_category,0) message_category,
NVL(msg.message_category_desc, lu.meaning) message_category_desc,
RLM_RLMDPDER_XMLP_PKG.cf_schedule_typeformula(e.schedule_type) CF_schedule_type,
RLM_RLMDPDER_XMLP_PKG.cf_schedule_purposeformula(e.schedule_purpose) CF_schedule_purpose,
RLM_RLMDPDER_XMLP_PKG.cf_schedule_sourceformula(e.schedule_source) CF_schedule_source,
RLM_RLMDPDER_XMLP_PKG.cf_order_typeformula(TO_NUMBER ( null )) CF_order_type,
RLM_RLMDPDER_XMLP_PKG.cf_item_detail_typeformula(e.item_detail_type) CF_item_detail_type,
RLM_RLMDPDER_XMLP_PKG.cf_item_detail_subtypeformula(e.item_detail_subtype) CF_item_detail_subtype,
RLM_RLMDPDER_XMLP_PKG.cf_quantity_typeformula(e.qty_type_code) CF_quantity_type
FROM rlm_demand_exceptions e,
rlm_message_category_v msg,
fnd_lookup_values lu
WHERE e.order_header_id IS NULL
AND e.order_line_id IS NULL
AND e.interface_header_id IS NULL
AND e.schedule_header_id IS NULL
AND e.message_name = msg.message_name
AND lu.lookup_type = 'RLM_MESSAGE_CATEGORY'
AND lu.lookup_code = '1'
AND lu.view_application_id = 0
AND lu.meaning = nvl(msg.message_category_desc, lu.meaning)
&p_where_clause
&p_where_clause2
UNION
SELECT e.exception_id exception_id,
e.request_id request_id,
substrb(e.cust_name_ext, 1, 60) customer_name,
e.ece_tp_translator_code trading_partner,
e.ece_tp_location_code_ext tp_location,
e.schedule_reference_num schedule_num,
e.schedule_type schedule_type,
e.schedule_purpose schedule_purpose,
e.schedule_source schedule_source,
e.horizon_start_date horizon_start_date,
to_char(e.horizon_end_date,'DD-MON-YY') horizon_end_date,
e.sched_generation_date sched_generation_date,
e.schedule_line_number schedule_line_num,
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.cust_intermd_shipto_ext sched_intermd_shipto,
e.cust_bill_to_ext sched_billto,
e.cust_uom_ext sched_cust_uom,
e.cust_job_number sched_cust_job,
e.customer_prod_seq_num sched_cust_prod_seq,
e.cust_model_serial_num sched_model_serial,
TO_DATE(null) sched_request_date,
TO_DATE(null) sched_schedule_date,
e.item_detail_quantity sched_order_quantity,
TO_DATE(null) sched_primary_quantity,
TO_CHAR(null) sched_cust_po,
TO_CHAR(null) sched_record_year,
TO_CHAR(null) sched_prod_line,
TO_CHAR(null) sched_dock_code,
TO_NUMBER(null) oe_order_number,
TO_NUMBER(null) order_type_id,
TO_NUMBER(null) oe_line_number,
TO_NUMBER(null) order_quantity,
TO_DATE(null) order_request_date,
TO_DATE(null) order_ship_date,
TO_NUMBER(null) order_ship_quantity,
TO_NUMBER(null) order_cancel_quantity,
TO_CHAR(null) order_cust_po,
TO_CHAR(null) order_record_year,
TO_CHAR(null) order_cust_uom,
TO_CHAR(null) order_prod_line,
TO_CHAR(null) order_dock_code,
TO_CHAR(null) order_prod_seq,
TO_CHAR(null) order_cust_job,
TO_CHAR(null) order_model_serial,
NVL(msg.message_category,0) message_category,
NVL(msg.message_category_desc, lu.meaning) message_category_desc,
RLM_RLMDPDER_XMLP_PKG.cf_schedule_typeformula(e.schedule_type) CF_schedule_type,
RLM_RLMDPDER_XMLP_PKG.cf_schedule_purposeformula(e.schedule_purpose) CF_schedule_purpose,
RLM_RLMDPDER_XMLP_PKG.cf_schedule_sourceformula(e.schedule_source) CF_schedule_source,
RLM_RLMDPDER_XMLP_PKG.cf_order_typeformula(TO_NUMBER ( null )) CF_order_type,
RLM_RLMDPDER_XMLP_PKG.cf_item_detail_typeformula(e.item_detail_type) CF_item_detail_type,
RLM_RLMDPDER_XMLP_PKG.cf_item_detail_subtypeformula(e.item_detail_subtype) CF_item_detail_subtype,
RLM_RLMDPDER_XMLP_PKG.cf_quantity_typeformula(e.qty_type_code) CF_quantity_type
FROM rlm_demand_exceptions e,
rlm_message_category_v msg,
fnd_lookup_values lu,
rlm_schedule_headers_all rlm
WHERE e.order_header_id IS NULL
AND e.order_line_id IS NULL
AND e.message_name = msg.message_name
AND lu.lookup_type = 'RLM_MESSAGE_CATEGORY'
AND lu.lookup_code = '1'
AND lu.view_application_id = 0
AND lu.meaning = nvl(msg.message_category_desc, lu.meaning)
and e.request_id = rlm.request_id
and e.interface_header_id = rlm.interface_header_id
and rlm.org_id = :CP_Org_id
and rlm.process_status = 5
&p_where_clause
&p_where_clause2
UNION
SELECT e.exception_id exception_id,
e.request_id request_id,
substrb(e.cust_name_ext, 1, 60) customer_name,
e.ece_tp_translator_code trading_partner,
e.ece_tp_location_code_ext tp_location,
e.schedule_reference_num schedule_num,
e.schedule_type schedule_type,
e.schedule_purpose schedule_purpose,
e.schedule_source schedule_source,
e.horizon_start_date horizon_start_date,
to_char(e.horizon_end_date,'DD-MON-YY') horizon_end_date,
e.sched_generation_date sched_generation_date,
e.schedule_line_number schedule_line_num,
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.cust_intermd_shipto_ext sched_intermd_shipto,
e.cust_bill_to_ext sched_billto,
e.cust_uom_ext sched_cust_uom,
e.cust_job_number sched_cust_job,
e.customer_prod_seq_num sched_cust_prod_seq,
e.cust_model_serial_num sched_model_serial,
TO_DATE(null) sched_request_date,
TO_DATE(null) sched_schedule_date,
e.item_detail_quantity sched_order_quantity,
TO_DATE(null) sched_primary_quantity,
TO_CHAR(null) sched_cust_po,
TO_CHAR(null) sched_record_year,
TO_CHAR(null) sched_prod_line,
TO_CHAR(null) sched_dock_code,
TO_NUMBER(null) oe_order_number,
TO_NUMBER(null) order_type_id,
TO_NUMBER(null) oe_line_number,
TO_NUMBER(null) order_quantity,
TO_DATE(null) order_request_date,
TO_DATE(null) order_ship_date,
TO_NUMBER(null) order_ship_quantity,
TO_NUMBER(null) order_cancel_quantity,
TO_CHAR(null) order_cust_po,
TO_CHAR(null) order_record_year,
TO_CHAR(null) order_cust_uom,
TO_CHAR(null) order_prod_line,
TO_CHAR(null) order_dock_code,
TO_CHAR(null) order_prod_seq,
TO_CHAR(null) order_cust_job,
TO_CHAR(null) order_model_serial,
NVL(msg.message_category,0) message_category,
NVL(msg.message_category_desc, lu.meaning) message_category_desc,
RLM_RLMDPDER_XMLP_PKG.cf_schedule_typeformula(e.schedule_type) CF_schedule_type,
RLM_RLMDPDER_XMLP_PKG.cf_schedule_purposeformula(e.schedule_purpose) CF_schedule_purpose,
RLM_RLMDPDER_XMLP_PKG.cf_schedule_sourceformula(e.schedule_source) CF_schedule_source,
RLM_RLMDPDER_XMLP_PKG.cf_order_typeformula(TO_NUMBER ( null )) CF_order_type,
RLM_RLMDPDER_XMLP_PKG.cf_item_detail_typeformula(e.item_detail_type) CF_item_detail_type,
RLM_RLMDPDER_XMLP_PKG.cf_item_detail_subtypeformula(e.item_detail_subtype) CF_item_detail_subtype,
RLM_RLMDPDER_XMLP_PKG.cf_quantity_typeformula(e.qty_type_code) CF_quantity_type
FROM rlm_demand_exceptions e,
rlm_message_category_v msg,
fnd_lookup_values lu,
rlm_interface_headers_all rlm
WHERE e.order_header_id IS NULL
AND e.order_line_id IS NULL
AND e.message_name = msg.message_name
AND lu.lookup_type = 'RLM_MESSAGE_CATEGORY'
AND lu.lookup_code = '1'
AND lu.view_application_id = 0
AND lu.meaning = nvl(msg.message_category_desc, lu.meaning)
and e.request_id = rlm.request_id
and e.interface_header_id = rlm.header_id
and rlm.org_id = :CP_Org_id
&p_where_clause
&p_where_clause2
UNION
SELECT e.exception_id exception_id,
e.request_id request_id,
substrb(e.cust_name_ext, 1, 60) customer_name,
e.ece_tp_translator_code trading_partner,
e.ece_tp_location_code_ext tp_location,
e.schedule_reference_num schedule_num,
e.schedule_type schedule_type,
e.schedule_purpose schedule_purpose,
e.schedule_source schedule_source,
e.horizon_start_date horizon_start_date,
to_char(e.horizon_end_date,'DD-MON-YY') horizon_end_date,
e.sched_generation_date sched_generation_date,
e.schedule_line_number schedule_line_num,
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.cust_intermd_shipto_ext sched_intermd_shipto,
e.cust_bill_to_ext sched_billto,
e.cust_uom_ext sched_cust_uom,
e.cust_job_number sched_cust_job,
e.customer_prod_seq_num sched_cust_prod_seq,
e.cust_model_serial_num sched_model_serial,
TO_DATE(null) sched_request_date,
TO_DATE(null) sched_schedule_date,
e.item_detail_quantity sched_order_quantity,
TO_DATE(null) sched_primary_quantity,
TO_CHAR(null) sched_cust_po,
TO_CHAR(null) sched_record_year,
TO_CHAR(null) sched_prod_line,
TO_CHAR(null) sched_dock_code,
oh.order_number oe_order_number,
oh.order_type_id order_type_id,
TO_NUMBER(null) oe_line_number,
TO_NUMBER(null) order_quantity,
TO_DATE(null) order_request_date,
TO_DATE(null) order_ship_date,
TO_NUMBER(null) order_ship_quantity,
TO_NUMBER(null) order_cancel_quantity,
TO_CHAR(null) order_cust_po,
TO_CHAR(null) order_record_year,
TO_CHAR(null) order_cust_uom,
TO_CHAR(null) order_prod_line,
TO_CHAR(null) order_dock_code,
TO_CHAR(null) order_prod_seq,
TO_CHAR(null) order_cust_job,
TO_CHAR(null) order_model_serial,
NVL(msg.message_category,0) message_category,
NVL(msg.message_category_desc, lu.meaning) message_category_desc,
RLM_RLMDPDER_XMLP_PKG.cf_schedule_typeformula(e.schedule_type) CF_schedule_type,
RLM_RLMDPDER_XMLP_PKG.cf_schedule_purposeformula(e.schedule_purpose) CF_schedule_purpose,
RLM_RLMDPDER_XMLP_PKG.cf_schedule_sourceformula(e.schedule_source) CF_schedule_source,
RLM_RLMDPDER_XMLP_PKG.cf_order_typeformula(TO_NUMBER ( null )) CF_order_type,
RLM_RLMDPDER_XMLP_PKG.cf_item_detail_typeformula(e.item_detail_type) CF_item_detail_type,
RLM_RLMDPDER_XMLP_PKG.cf_item_detail_subtypeformula(e.item_detail_subtype) CF_item_detail_subtype,
RLM_RLMDPDER_XMLP_PKG.cf_quantity_typeformula(e.qty_type_code) CF_quantity_type
FROM rlm_demand_exceptions e,
oe_order_headers_all oh,
rlm_message_category_v msg,
fnd_lookup_values lu
WHERE e.order_header_id IS NOT NULL
AND e.order_line_id IS NULL
AND oh.header_id(+) = e.order_header_id
and oh.org_id = :CP_Org_id
AND e.message_name = msg.message_name
AND lu.lookup_type = 'RLM_MESSAGE_CATEGORY'
AND lu.lookup_code = '1'
AND lu.view_application_id = 0
AND lu.meaning = nvl(msg.message_category_desc, lu.meaning)
&p_where_clause
&p_where_clause1
UNION
SELECT e.exception_id exception_id,
e.request_id request_id,
substrb(e.cust_name_ext, 1, 60) customer_name,
e.ece_tp_translator_code trading_partner,
e.ece_tp_location_code_ext tp_location,
e.schedule_reference_num schedule_num,
e.schedule_type schedule_type,
e.schedule_purpose schedule_purpose,
e.schedule_source schedule_source,
e.horizon_start_date horizon_start_date,
to_char(e.horizon_end_date,'DD-MON-YY') horizon_end_date,
e.sched_generation_date sched_generation_date,
e.schedule_line_number schedule_line_num,
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.cust_intermd_shipto_ext sched_intermd_shipto,
e.cust_bill_to_ext sched_billto,
e.cust_uom_ext sched_cust_uom,
e.cust_job_number sched_cust_job,
e.customer_prod_seq_num sched_cust_prod_seq,
e.cust_model_serial_num sched_model_serial,
TO_DATE(null) sched_request_date,
TO_DATE(null) sched_schedule_date,
e.item_detail_quantity sched_order_quantity,
TO_DATE(null) sched_primary_quantity,
TO_CHAR(null) sched_cust_po,
TO_CHAR(null) sched_record_year,
TO_CHAR(null) sched_prod_line,
TO_CHAR(null) sched_dock_code,
oh.order_number oe_order_number,
oh.order_type_id order_type_id,
ol.line_number oe_line_number,
ol.ordered_quantity order_quantity,
ol.request_date order_request_date,
ol.schedule_ship_date order_ship_date,
ol.shipped_quantity order_ship_quantity,
ol.cancelled_quantity order_cancel_quantity,
ol.cust_po_number order_cust_po,
ol.industry_attribute1 order_record_year,
ol.industry_attribute8 order_cust_uom,
ol.customer_production_line order_prod_line,
ol.customer_dock_code order_dock_code,
ol.cust_production_seq_num order_prod_seq,
ol.customer_job order_cust_job,
ol.cust_model_serial_number order_model_serial,
NVL(msg.message_category,0) message_category,
NVL(msg.message_category_desc, lu.meaning) message_category_desc,
RLM_RLMDPDER_XMLP_PKG.cf_schedule_typeformula(e.schedule_type) CF_schedule_type,
RLM_RLMDPDER_XMLP_PKG.cf_schedule_purposeformula(e.schedule_purpose) CF_schedule_purpose,
RLM_RLMDPDER_XMLP_PKG.cf_schedule_sourceformula(e.schedule_source) CF_schedule_source,
RLM_RLMDPDER_XMLP_PKG.cf_order_typeformula(TO_NUMBER ( null )) CF_order_type,
RLM_RLMDPDER_XMLP_PKG.cf_item_detail_typeformula(e.item_detail_type) CF_item_detail_type,
RLM_RLMDPDER_XMLP_PKG.cf_item_detail_subtypeformula(e.item_detail_subtype) CF_item_detail_subtype,
RLM_RLMDPDER_XMLP_PKG.cf_quantity_typeformula(e.qty_type_code) CF_quantity_type
FROM rlm_demand_exceptions e,
oe_order_headers_all oh,
oe_order_lines_all ol,
rlm_message_category_v msg,
fnd_lookup_values lu
WHERE e.order_header_id IS NOT NULL
AND e.order_line_id IS NOT NULL
AND oh.header_id = e.order_header_id
AND ol.line_id(+)= e.order_line_id
AND oh.org_id = ol.org_id
AND oh.org_id = :CP_Org_id
AND e.message_name = msg.message_name
AND lu.lookup_type = 'RLM_MESSAGE_CATEGORY'
AND lu.lookup_code = '1'
AND lu.view_application_id = 0
AND lu.meaning = nvl(msg.message_category_desc, lu.meaning)
&p_where_clause
&p_where_clause1
ORDER BY 2 DESC,6 ASC,4 ASC,5 ASC,3 ASC,7 ASC,8 ASC,9 ASC,12 ASC,10 ASC,11 ASC,46 ASC,47 ASC,62 ASC,63 ASC , request_id desc , oe_order_number , schedule_line_num |