RLM Compare Schedule To Demand

SELECT     RCSD.SCH_HEADER_ID AHEADER_ID,
                 RCSD.CUSTOMER_ID  CUSTOMER_NUMBER,
	 RCSD.CUSTOMER_NAME,
                 RCSD.SCHEDULE_TYPE  ASCHEDULE_TYPE,
                 flv3.MEANING   SCHEDULE_TYPE_MEAN,
	 RCSD.SCHEDULE_SOURCE,
	 RCSD.ECE_TP_TRANSLATOR_CODE	  TP_CODE,
	 RCSD.ECE_TP_LOCATION_CODE_EXT	  TP_LOCATION_CODE,
	 RCSD.SCHEDULE_REFERENCE_NUM,
	 RCSD.SCHED_HORIZON_START_DATE	  HORIZON_START_DATE,
	 RCSD.SCHED_HORIZON_END_DATE	  HORIZON_END_DATE,
	 RCSD.SCHED_GENERATION_DATE  	  ISSUE_DATE,
	 RCSD.CREATION_DATE           	                  PROCESSED_DATE,
	 RCSD.SCHEDULE_PURPOSE,
                 flv4.MEANING  SCHEDULE_PURPOSE_MEAN,
	  RCSD.SHIP_FROM_ORG_ID,
	  RCSD.SHIP_TO_ORG_ID,
                  RCSD.SHIP_TO_ADDRESS_ID,
	  RCSD.CUSTOMER_ITEM_ID,
	  RCSD.INVENTORY_ITEM_ID,
	  RMA.CUSTOMER_ITEM_NUMBER,
	  RMA.CUSTOMER_ITEM_DESC,
	  RMA.INVENTORY_ITEM_NUMBER,
	  RMA.INVENTORY_ITEM_DESC,
	  RMA.FROZEN_DAY_FROM,
	  RMA.FROZEN_DAY_TO,
	  RMA.FIRM_DAY_FROM,
	  RMA.FIRM_DAY_TO,
	  RMA.FORECAST_DAY_FROM,
	  RMA.FORECAST_DAY_TO,
	  RMA.INTRANSIT_TIME,
                    RMA.TIME_UOM_CODE ,
                    RMA.FROZEN_FLAG ,
                    RMA.SHIP_DELIVERY_PATTERN,
	 RMA.ROUND_TO_STANDARD_PACK,
	 RMA.INTRANSIT_CALC_BASIS,
                    RMA.EXCLUDE_NON_WORKDAYS_FLAG,
	 RMA.UNSHIPPED_FIRM_DISP_CD 	                PREHORIZON_DESPOSITION_CODE,
	 RCSD.ITEM_DETAIL_SUBTYPE  	                BUCKET_TYPE, 
	  flv2.MEANING                                                       BUCKET_TYPE_MEAN,
                  RCSD.ITEM_DETAIL_TYPE      	                DEMAND_TYPE,
                  flv1.MEANING                                                       DEMAND_TYPE_MEAN ,
	  RCSD.WEEK_START_DATE,
	  RCSD.WEEK_NAME,
                  RCSD.CUSTOMER_DOCK_CODE,              
                  RCSD.CUSTOMER_JOB,                    
                  RCSD.CUST_MODEL_SERIAL_NUMBER,        
                  RCSD.CUST_PRODUCTION_LINE,            
                  RCSD.CUST_PRODUCTION_SEQ_NUM,         
                  RCSD.CUST_PO_NUMBER  SCH_PO_NUMBER,                  
                  RCSD.CUSTOMER_ITEM_REVISION,          
                  RCSD.PULL_SIGNAL_START_SERIAL_NUM,    
                  RCSD.PULL_SIGNAL_END_SERIAL_NUM,      
                  RCSD.PULL_SIGNAL_REF_NUM,             
                  RCSD.CUSTOMER_REQUEST_DATE,
                  RCSD.RECORD_YEAR,           
                  RCSD.SCH_LINE_ID	  		  SCHEDULE_LINE_ID,
                  DECODE(:P_SCHEDULE_TYPE, 'SEQUENCED', NULL,to_char(RCSD.START_DATE_TIME,'DD-MON-RR HH24:MI:SS') )      		                                        REQUEST_DATE_NON_SEQUENCED,
	 DECODE(:P_SCHEDULE_TYPE, 'SEQUENCED', to_char(RCSD.START_DATE_TIME,'DD-MON-RR HH24:MI:SS'), NULL)       		  REQUEST_DATE_SEQUENCED,
	   RCSD.ITEM_DETAIL_QUANTITY		  SCHEDULE_QUANTITY,
 	   RCSD.UOM_CODE,
                   OLA.ORDERED_QUANTITY,
	   OLA.ORDER_QUANTITY_UOM,
                DECODE(:P_SCHEDULE_TYPE, 'SEQUENCED', NULL,to_char( OLA.SCHEDULE_SHIP_DATE,'DD-MON-RR HH24:MI:SS'))		SCHEDULE_SHIP_DATE_NON_SEQ,
               DECODE(:P_SCHEDULE_TYPE, 'SEQUENCED', to_char(OLA.SCHEDULE_SHIP_DATE,'DD-MON-RR HH24:MI:SS'), NULL)		SCHEDULE_SHIP_DATE_SEQ,
	OLA.HEADER_ID			  	ORDER_NUMBER,
                TO_CHAR(OLA.LINE_NUMBER) ||
              DECODE(OLA.SHIPMENT_NUMBER, NULL, NULL, '.' ||
              TO_CHAR(OLA.SHIPMENT_NUMBER))||
              DECODE(OLA.OPTION_NUMBER, NULL, NULL, '.' ||
              TO_CHAR(OLA.OPTION_NUMBER)) ||
              DECODE(OLA.COMPONENT_NUMBER, NULL, NULL,
              DECODE(OLA.OPTION_NUMBER, NULL, '.',NULL)||
                 '.'||TO_CHAR(OLA.COMPONENT_NUMBER))||
             DECODE(OLA.SERVICE_NUMBER,NULL,NULL,
                 DECODE(OLA.COMPONENT_NUMBER, NULL, '.' , NULL) ||
                        DECODE(OLA.OPTION_NUMBER, NULL, '.', NULL ) ||
                          '.'|| TO_CHAR(OLA.SERVICE_NUMBER)) ORDER_LINE_NUMBER,
              OLA.CUST_PO_NUMBER		  PURCHASE_ORDER_NO,
               DECODE(:P_SCHEDULE_TYPE,'SEQUENCED',OLA.REQUEST_DATE,NULL)  REQUEST_DATE_TIME, 
	RLM_RLMDMCOM_XMLP_PKG.cf_customer_number_meanformula(RCSD.CUSTOMER_ID) CF_CUSTOMER_NUMBER_MEAN, 
	RLM_RLMDMCOM_XMLP_PKG.cf_ship_toformula(RCSD.SHIP_TO_ADDRESS_ID) CF_SHIP_TO, 
	RLM_RLMDMCOM_XMLP_PKG.cf_frozen_flagformula(RMA.FROZEN_FLAG) CF_FROZEN_FLAG, 
	RLM_RLMDMCOM_XMLP_PKG.cf_time_uom_codeformula(RMA.TIME_UOM_CODE) CF_TIME_UOM_CODE, 
	RLM_RLMDMCOM_XMLP_PKG.cf_ship_from_org_idformula(RCSD.SHIP_FROM_ORG_ID) CF_SHIP_FROM_ORG_ID, 
	RLM_RLMDMCOM_XMLP_PKG.cf_pre_horizon_disp_codeformul(RMA.UNSHIPPED_FIRM_DISP_CD) CF_PRE_HORIZON_DISP_CODE, 
	RLM_RLMDMCOM_XMLP_PKG.cf_intransit_calc_basisformula(RMA.INTRANSIT_CALC_BASIS) CF_INTRANSIT_CALC_BASIS, 
	RLM_RLMDMCOM_XMLP_PKG.cf_exclude_nonworkdaysformula(RMA.EXCLUDE_NON_WORKDAYS_FLAG) CF_EXCLUDE_NONWORKDAYS, 
	RLM_RLMDMCOM_XMLP_PKG.cf_match_seqformula(RCSD.CUSTOMER_ITEM_REVISION, RCSD.CUSTOMER_DOCK_CODE, RCSD.CUSTOMER_JOB, RCSD.CUST_PRODUCTION_LINE, RCSD.CUST_MODEL_SERIAL_NUMBER, RCSD.CUST_PO_NUMBER, RCSD.CUST_PRODUCTION_SEQ_NUM, RCSD.RECORD_YEAR, RCSD.CUSTOMER_REQUEST_DATE, RCSD.PULL_SIGNAL_REF_NUM, RCSD.PULL_SIGNAL_START_SERIAL_NUM, RCSD.PULL_SIGNAL_END_SERIAL_NUM, DECODE ( :P_SCHEDULE_TYPE , 'SEQUENCED' , OLA.REQUEST_DATE , NULL )) CF_MATCH_SEQ,
	RLM_RLMDMCOM_XMLP_PKG.CP_SEQ_MATCH_LABEL_p CP_SEQ_MATCH_LABEL,
	RLM_RLMDMCOM_XMLP_PKG.CP_SEQ_COLUMN_p CP_SEQ_COLUMN, 
	RLM_RLMDMCOM_XMLP_PKG.cf_ordernumformula(OLA.HEADER_ID) CF_ORDER_NUM
FROM   RLM_COMP_SCHED_TO_DEMAND_TEMP  RCSD,
            RLM_MATCH_SETUP_TEMP	           RMA,
            OE_ORDER_LINES 		          OLA,
            FND_LOOKUP_VALUES_VL FLV1 ,
            FND_LOOKUP_VALUES_VL FLV2,
            FND_LOOKUP_VALUES_VL FLV3,
            FND_LOOKUP_VALUES_VL FLV4
  WHERE      RMA.CUSTOMER_ID   = RCSD.CUSTOMER_ID
      AND	   RMA.SHIP_FROM_ORG_ID    	= RCSD.SHIP_FROM_ORG_ID
      AND	   RMA.SHIP_TO_ORG_ID	= RCSD.SHIP_TO_ORG_ID
      AND	   RMA.CUSTOMER_ITEM_ID	= RCSD.CUSTOMER_ITEM_ID
      AND	   RMA.INVENTORY_ITEM_ID   	= RCSD.INVENTORY_ITEM_ID
      AND	   OLA.SOURCE_DOCUMENT_LINE_ID(+)  = RCSD.SCH_LINE_ID
      AND      OLA.SOURCE_DOCUMENT_ID (+)  = RCSD.SCH_HEADER_ID 
      AND      OLA.SOURCE_DOCUMENT_TYPE_ID(+) = 5
AND    OLA.header_id (+)= RCSD.order_header_id
AND    OLA.inventory_item_id (+) = RCSD.inventory_item_id
      AND    FLV1.LOOKUP_TYPE = 'RLM_DETAIL_TYPE_CODE'
      AND    FLV1.LOOKUP_CODE =RCSD.ITEM_DETAIL_TYPE
      AND  FLV1.ENABLED_FLAG ='Y' 
      AND    FLV2.LOOKUP_TYPE = 'RLM_DETAIL_SUBTYPE_CODE'
      AND    FLV2.LOOKUP_CODE =RCSD.ITEM_DETAIL_SUBTYPE
     AND    FLV2.ENABLED_FLAG ='Y'
     AND    FLV3.LOOKUP_CODE  =RCSD.SCHEDULE_TYPE 
     AND    FLV3.LOOKUP_TYPE = 'RLM_SCHEDULE_TYPE'
     AND    FLV3.ENABLED_FLAG = 'Y'
     AND    FLV4.LOOKUP_CODE  = RCSD.SCHEDULE_PURPOSE 
     AND    FLV4.LOOKUP_TYPE = 'RLM_SCHEDULE_PURPOSE'
     AND    FLV4.ENABLED_FLAG = 'Y'
	 ORDER BY 1 ASC,2 ASC,3 ASC,4 ASC,5 ASC,6 ASC,7 ASC,15 ASC,14 ASC,13 ASC,10 ASC,12 ASC,9 ASC,8 ASC,11 ASC,17 ASC,18 ASC,33 ASC,32 ASC,26 ASC,59 ASC,38 ASC,65 ASC,36 ASC,35 ASC,34 ASC,31 ASC,30 ASC,29 ASC,28 ASC,27 ASC,21 ASC,25 ASC,24 ASC,22 ASC,20 ASC,23 ASC,19 ASC,37 ASC,16 ASC,43 ASC,58 ASC,57 ASC,39 ASC,40 ASC,41 ASC,42 ASC,44 ASC,45 ASC,46 ASC,47 ASC,48 ASC,49 ASC,50 ASC,51 ASC,55 ASC,52 ASC,53 ASC,54 ASC,56 ASC,60 ASC,61 ASC,69 ASC , RCSD.CUSTOMER_ID , RCSD.CREATION_DATE , RCSD.SHIP_FROM_ORG_ID , RCSD.SHIP_TO_ORG_ID , RCSD.CUSTOMER_ITEM_ID , RCSD.INVENTORY_ITEM_ID , RCSD.WEEK_START_DATE , REQUEST_DATE_NON_SEQUENCED , SCHEDULE_SHIP_DATE_NON_SEQ