RLM Release Management Exceptions

Description
Categories: BI Publisher
Application: Release Management
Source: Release Management Exceptions Report (XML)
Short Name: RLMDPDER_XML
DB package: RLM_RLMDPDER_XMLP_PKG
Run RLM Release Management Exceptions and other Oracle EBS reports with Blitz Report™ on our demo environment
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
Parameter Name SQL text Validation
From Request ID
 
LOV Oracle
To Request ID