RLM Schedule/Release

Description
Categories: BI Publisher, Manufacturing
Application: Release Management
Source: Schedule/Release Report (XML)
Short Name: RLMSCHRL_XML
DB package: RLM_RLMSCHRL_XMLP_PKG
SELECT
sih.SCHEDULE_REFERENCE_NUM,
sih.SCHEDULE_SOURCE,
sih.SCHEDULE_TYPE atype,
flv0.MEANING stype,
sih.SCHEDULE_TYPE_EXT,
sih.SCHEDULE_PURPOSE,
flv7.MEANING spurpose,
sih.SCHEDULE_PURPOSE_EXT,
sih.SCHED_GENERATION_DATE,sih.EDI_TEST_INDICATOR,
substrb(sih.CUST_NAME_EXT, 1, 60) cust_name_ext,
sih.CUSTOMER_ID,
sih.CUSTOMER_EXT,
sih.ECE_TP_TRANSLATOR_CODE,sih.ECE_TP_LOCATION_CODE_EXT,
sih.SCHED_HORIZON_START_DATE,sih.SCHED_HORIZON_END_DATE,
sih.HEADER_ID,sih.PROCESS_STATUS,
sih.CREATION_DATE,
flv1.MEANING  status,
sih.LAST_UPDATE_DATE,sih.EDI_CONTROL_NUM_1,
sih.EDI_CONTROL_NUM_2,sih.EDI_CONTROL_NUM_3,
sih.CUST_ADDRESS_1_EXT,sih.CUST_ADDRESS_2_EXT,
sih.CUST_ADDRESS_3_EXT,sih.CUST_ADDRESS_4_EXT,
sih.CUST_CITY_EXT,sih.CUST_STATE_EXT,
sih.CUST_COUNTRY_EXT,sih.CUST_POSTAL_CD_EXT,
sil.CUST_SHIP_FROM_ORG_EXT,sil.CUST_SHIP_TO_EXT,sil.CUSTOMER_ITEM_EXT,
sil.CUSTOMER_ITEM_ID,
sil.ITEM_DETAIL_TYPE,sil.ITEM_DETAIL_SUBTYPE,
sil.INVENTORY_ITEM_ID,sil.INTRMD_SHIP_TO_ID,sil.BILL_TO_ADDRESS_ID,
sil.CUST_MODEL_SERIAL_NUMBER,sil.CUSTOMER_JOB,sil.HEADER_Id,
sil.LINE_NUMBER,sil.CUST_PRODUCTION_SEQ_NUM,
sil.SCHEDULE_ITEM_NUM,sil.SHIP_FROM_ORG_ID,sil.SHIP_TO_ORG_ID,
sil.CUST_PO_NUMBER,sil.SHIP_TO_ADDRESS_ID,
decode(sil.item_detail_type,'0',:P_detail_type,'1',:P_detail_type,'2',:P_detail_type,'6',:P_detail_type,
'3',:P_Auth,'4',:P_shipped_rec_cum,'5',:P_other) detail_type1,
flv2.MEANING detail_type,
flv3.MEANING sub_type,
flv5.MEANING qty_type,
flv6.MEANING date_type,
sil.ITEM_DETAIL_QUANTITY,sil.UOM_CODE,sil.DATE_TYPE_CODE,
sil.START_DATE_TIME,sil.END_DATE_TIME,sil.QTY_TYPE_CODE,
sil.SHIP_DEL_PATTERN_EXT,sil.PROCESS_STATUS,sil.CUSTOMER_DOCK_CODE,
sil.INDUSTRY_ATTRIBUTE1,
flv4.MEANING lprocess_status, 
	RLM_RLMSCHRL_XMLP_PKG.cf_schedule_sourceformula(sih.SCHEDULE_SOURCE) CF_schedule_source, 
	RLM_RLMSCHRL_XMLP_PKG.cf_address1formula(sih.CUST_ADDRESS_1_EXT, sih.CUST_ADDRESS_2_EXT, sih.CUST_ADDRESS_3_EXT, sih.CUST_ADDRESS_4_EXT) CF_Address1, 
	RLM_RLMSCHRL_XMLP_PKG.cf_address2formula(sih.CUST_ADDRESS_1_EXT, sih.CUST_ADDRESS_2_EXT, sih.CUST_ADDRESS_3_EXT, sih.CUST_ADDRESS_4_EXT) CF_Address2, 
	RLM_RLMSCHRL_XMLP_PKG.cf_address4formula(sih.CUST_ADDRESS_1_EXT, sih.CUST_ADDRESS_2_EXT, sih.CUST_ADDRESS_3_EXT, sih.CUST_ADDRESS_4_EXT) CF_Address4, 
	RLM_RLMSCHRL_XMLP_PKG.cf_address3formula(sih.CUST_ADDRESS_1_EXT, sih.CUST_ADDRESS_2_EXT, sih.CUST_ADDRESS_3_EXT, sih.CUST_ADDRESS_4_EXT) CF_Address3, 
	RLM_RLMSCHRL_XMLP_PKG.cf_test_indicatorformula(sih.EDI_TEST_INDICATOR) CF_test_indicator, 
	RLM_RLMSCHRL_XMLP_PKG.cf_customer_numformula(sih.CUSTOMER_ID) CF_CUSTOMER_NUM, 
	RLM_RLMSCHRL_XMLP_PKG.cf_ship_del_patternformula(sil.SHIP_DEL_PATTERN_EXT) CF_ship_del_pattern, 
	RLM_RLMSCHRL_XMLP_PKG.cf_inv_item_conc_segmentformul(sil.INVENTORY_ITEM_ID, sil.SHIP_FROM_ORG_ID) CF_inv_item_conc_segment, 
	RLM_RLMSCHRL_XMLP_PKG.cf_ship_to_locformula(sih.SCHEDULE_SOURCE, sil.SHIP_TO_ADDRESS_ID, sil.CUST_SHIP_TO_EXT) CF_ship_to_loc, 
	RLM_RLMSCHRL_XMLP_PKG.cf_bill_to_locformula(sil.BILL_TO_ADDRESS_ID) CF_bill_to_loc, 
	RLM_RLMSCHRL_XMLP_PKG.cf_ship_from_orgformula(sih.SCHEDULE_SOURCE, sil.SHIP_FROM_ORG_ID, sil.CUST_SHIP_FROM_ORG_EXT) CF_ship_from_org, 
	RLM_RLMSCHRL_XMLP_PKG.cf_cust_item_numformula(sih.SCHEDULE_SOURCE, sil.CUSTOMER_ITEM_ID, sih.CUSTOMER_ID, sil.CUSTOMER_ITEM_EXT) CF_cust_item_num
FROM 
rlm_schedule_interface_hdr_v sih, 
rlm_schedule_interface_lines_v sil ,
fnd_lookup_values_vl flv0,
fnd_lookup_values_vl flv1,
fnd_lookup_values_vl flv2,
fnd_lookup_values_vl flv3,
fnd_lookup_values_vl flv4,
fnd_lookup_values_vl flv5,
fnd_lookup_values_vl flv6,
fnd_lookup_values_vl flv7
WHERE 
sil.header_id = sih.header_id
and flv0.lookup_type = 'RLM_SCHEDULE_TYPE'
and flv0.lookup_code = sih.schedule_type
and flv1.lookup_type = 'RLM_PROCESS_STATUS'
and flv1.lookup_code = sih.process_status
and flv2.lookup_type = 'RLM_DETAIL_TYPE_CODE'
and flv2.lookup_code = sil.item_detail_type
and flv3.lookup_type = 'RLM_DETAIL_SUBTYPE_CODE'
and flv3.lookup_code = sil.item_detail_subtype
and flv4.lookup_type = 'RLM_PROCESS_STATUS'
and flv4.lookup_code = sil.process_status
and flv5.lookup_type = 'RLM_QTY_TYPE_CODE'
and flv5.lookup_code = sil.qty_type_code
and flv6.lookup_type = 'RLM_DATE_TYPE_CODE'
and flv6.lookup_code = sil.date_type_code
and flv7.lookup_type = 'RLM_SCHEDULE_PURPOSE'
and flv7.lookup_code = sih.schedule_purpose
&P_WHERE_CLAUSE
ORDER BY 1 ASC, 2 ASC,  3 ASC,  20 ASC,  5 ASC,  6 ASC,
  8 ASC,  19 ASC,  4 ASC,  7 ASC,  9 ASC,  21 ASC,  13 ASC,
  10 ASC,  11 ASC,  12 ASC,  14 ASC,  15 ASC,  16 ASC,
  17 ASC,  18 ASC,  23 ASC,  24 ASC,  25 ASC,  26 ASC,
  27 ASC,  28 ASC,  29 ASC,  30 ASC,  22 ASC,  31 ASC,
  33 ASC,  32 ASC,  34 ASC,  67 ASC,  66 ASC,  37 ASC,
  52 ASC,  35 ASC,  36 ASC,  41 ASC,  42 ASC,  40 ASC,
  51 ASC,  50 ASC,  49 ASC,  44 ASC,  64 ASC,  43 ASC,
  45 ASC,  53 ASC,  sih.last_update_date DESC,
  sih.sched_generation_date DESC,
  sil.line_number ASC
Parameter NameSQL textValidation
Other Demand
 
Authorization Demand
 
Shipped/Received/CUM Info
 
Title
 
Past Due/Firm/Forecast
 
Process Status
 
LOV Oracle
Process Date To
 
DateTime
Process Date From
 
DateTime
TP Location Code To
 
LOV Oracle
TP Location Code From
 
LOV Oracle
Trading Partner To
 
LOV Oracle
Trading Partner From
 
LOV Oracle
Issue Date To
 
DateTime
Issue Date From
 
DateTime
Horizon End Date
 
DateTime
Horizon Start Date
 
DateTime
Schedule Source
 
Schedule Purpose
 
LOV Oracle
Ship To
 
LOV Oracle
Ship From Org
 
LOV Oracle
Schedule Reference Num
 
LOV Oracle
Schedule Type
 
LOV Oracle
Customer Name
 
LOV Oracle