RLM Schedule/Release

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