INV Forecast rule listing

Description
Categories: BI Publisher, Logistics
Application: Inventory
Source: Forecast rule listing (XML)
Short Name: INVPRFRL_XML
DB package: INV_INVPRFRL_XMLP_PKG
SELECT 
 MFR.FORECAST_RULE_ID        RULE_ID, 
 MFR.FORECAST_RULE_NAME      RULE_NAME, 
 MFR.DESCRIPTION             RULE_DESC, 
 MFR.FORECAST_TYPE           FCST_TYPE_ID, 
 ML1.MEANING                 FCST_TYPE_NAM, 
 ML1.LOOKUP_CODE             RULE_TYPE_CD,
 MFR.PERIOD_TYPE             PERIOD_ID, 
 ML2.MEANING                 BUCKET_NAME, 
 ML2.LOOKUP_CODE             PERIOD_CD,
 ML3.MEANING                 INCL_SORD_YN, 
 ML4.MEANING                 INCL_IORG_YN,
 ML5.MEANING                 INCL_WIP_YN,
 ML6.MEANING                 INCL_MISC_YN, 
 ML7.MEANING                 INCL_TREND_YN,
 ML8.MEANING                 INCL_SEASON_YN, 
 MFR.MAXIMUM_PAST_PERIODS    MAX_PAST_PDS, 
 MFR.ALPHA_SMOOTHING_FACTOR  ALPHA_SMOOTH, 
 MFR.TREND_FACTOR            TREND_FACTOR, 
 MFR.SEASONALITY_FACTOR      SEASON_FACTOR, 
 MSI.PERIOD_NUM              SEASON_PD_NUM, 
 MSI.SEASONALITY_INDEX       SEASON_INDEX, 
 MSI.PERIOD_TYPE_CODE        SEASON_PERIOD_TYPE_CD
FROM 
 MTL_FORECAST_RULES          MFR, 
 MFG_LOOKUPS                 ML1, 
 MFG_LOOKUPS                 ML2, 
 MFG_LOOKUPS                 ML3, 
 MFG_LOOKUPS                 ML4, 
 MFG_LOOKUPS                 ML5, 
 MFG_LOOKUPS                 ML6, 
 MFG_LOOKUPS                 ML7, 
 MFG_LOOKUPS                 ML8,
 MTL_SEASONALITY_INDICES     MSI
WHERE 
 MFR.FORECAST_TYPE = DECODE        
     ( :P_FCST_TYPE_ID,'',MFR.FORECAST_TYPE,
                         :P_FCST_TYPE_ID)
AND 
 MFR.PERIOD_TYPE =    DECODE 
       (:P_BUCKET_TYPE_ID,'',MFR.PERIOD_TYPE,:P_BUCKET_TYPE_ID)
 AND 
  ( 
     (  :P_FCST_RULE_NAME_LO IS NULL and 
         :P_FCST_RULE_NAME_HI IS NULL )
     OR 
     (  :P_FCST_RULE_NAME_LO  IS NOT NULL and 
         :P_FCST_RULE_NAME_HI IS NULL  and 
         :P_FCST_RULE_NAME_LO = 
         MFR.FORECAST_RULE_NAME ) 
     OR 
      (  :P_FCST_RULE_NAME_LO IS NOT NULL and 
          :P_FCST_RULE_NAME_HI IS NOT NULL and 
     MFR.FORECAST_RULE_NAME  BETWEEN 
     :P_FCST_RULE_NAME_LO AND :P_FCST_RULE_NAME_HI ) 
    ) 
AND 
 MFR.FORECAST_RULE_ID = 
 MSI.FORECAST_RULE_ID (+)
AND  
 ML1.LOOKUP_TYPE    = 'MTL_FORECAST'
 AND 
 MFR.FORECAST_TYPE  = ML1.LOOKUP_CODE (+)
AND 
 ML2.LOOKUP_TYPE    = 'MRP_BUCKET_TYPE' 
 AND 
 MFR.PERIOD_TYPE    = ML2.LOOKUP_CODE (+)
AND 
 ML3.LOOKUP_TYPE    = 'SYS_YES_NO' 
 AND 
 MFR.INCLUDE_SALES_ORDERS =  ML3.LOOKUP_CODE (+)
AND 
 ML4.LOOKUP_TYPE    = 'SYS_YES_NO' 
 AND 
 MFR.INCLUDE_INTERORG_ISSUES = ML4.LOOKUP_CODE (+)
AND 
 ML5.LOOKUP_TYPE    = 'SYS_YES_NO' 
 AND 
 MFR.INCLUDE_WIP_ISSUES = ML5.LOOKUP_CODE (+)
AND 
 ML6.LOOKUP_TYPE    = 'SYS_YES_NO'
 AND 
 MFR.INCLUDE_MISCELLANEOUS_ISSUES = ML6.LOOKUP_CODE (+)
AND 
 ML7.LOOKUP_TYPE    = 'SYS_YES_NO'
 AND 
 MFR.INCLUDE_TREND_MODEL = ML7.LOOKUP_CODE (+) 
AND 
 ML8.LOOKUP_TYPE    = 'SYS_YES_NO' 
 AND 
 MFR.INCLUDE_SEASONALITY_MODEL = ML8.LOOKUP_CODE (+)
ORDER BY 
  MFR.FORECAST_RULE_NAME, 
  MSI.PERIOD_NUM
Parameter Name SQL text Validation
Organization
 
Number
Forecast Type
 
LOV Oracle
Bucket Type
 
LOV Oracle
To
 
LOV Oracle
Forecast Rules From
 
LOV Oracle
Ask a question