RLM Compare Schedule To Demand

Description
Categories: BI Publisher, Manufacturing
Application: Release Management
Source: Compare Schedule To Demand Report (XML)
Short Name: RLMDMCOM_XML
DB package: RLM_RLMDMCOM_XMLP_PKG
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
Parameter Name SQL text Validation
Title
 
Discrepancies Only
 
LOV Oracle
Demand Type
 
LOV Oracle
Sequence Matching Attribute
 
LOV Oracle
Process Date To
 
DateTime
Process Date From
 
DateTime
TP Location To
 
LOV Oracle
TP Location From
 
LOV Oracle
TP Code To
 
LOV Oracle
TP Code From
 
LOV Oracle
Internal Item To
 
LOV Oracle
Internal Item From
 
LOV Oracle
Customer Item To
 
LOV Oracle
Customer Item From
 
LOV Oracle
Request Date To
 
DateTime
Request Date From
 
DateTime
Issue Date To
 
DateTime
Issue Date From
 
DateTime
Ship to
 
LOV Oracle
Ship From
 
LOV Oracle
Customer Name To
 
LOV Oracle
Customer Name From
 
LOV Oracle
Schedule Reference Num
 
LOV Oracle
Schedule Type
 
LOV Oracle