SELECT MO.MO_ID,

       MO.ITEM,

       MO.QTYORDERED,

       MO.PLANNEDSTARTDATE,

       BR.MAXLOTSIZE

  FROM TEMP_MO MO, 

       (SELECT PRODUCED_ITEM_ID ITEM_ID,

               SITEID,

               MAX(DECODE(NVL(MAXLOTSIZE, 0), 0, 99999999, MAXLOTSIZE)) MAXLOTSIZE

          FROM IN_ITEMBOMROUTING,

               TEMP_MO MO

         WHERE SUBSTR(PRODUCED_ITEM_ID, 7, 3) IN ('', '', '', '', '')

            OR SUBSTR(PRODUCED_ITEM_ID, 9, 4) IN ('', '')

            OR PRODUCED_ITEM_ID IN (SELECT IIS.ITEM_ID FROM STG.IN_ITEM_SITE IIS WHERE SUBSTR(IIS.ITEM_ID, 7, 3) IN ('')  AND TRIM(IIS.PROC_TYPE) = 'E')

         GROUP BY PRODUCED_ITEM_ID, SITEID) BR 

 WHERE MO.LOC_ID = BR.SITEID

   AND BR.ITEM_ID = MO.ITEM

CREATE INDEX STG.IDX_TEMP_MO_DBA01 ON STG.TEMP_MO(LOC_ID,ITEM)

LOGGING

TABLESPACE WWFDATA

PCTFREE    10

INITRANS   2

MAXTRANS   255

STORAGE    (

            INITIAL          64K

            NEXT             1M

            MINEXTENTS       1

            MAXEXTENTS       UNLIMITED

            PCTINCREASE      0

            BUFFER_POOL      DEFAULT

            FLASH_CACHE      DEFAULT

            CELL_FLASH_CACHE DEFAULT

           )

NOPARALLEL;

CREATE INDEX STG.IDX_IN_ITEM_SITE_DBA01 ON STG.IN_ITEM_SITE(ITEM_ID,PROC_TYPE)

LOGGING

TABLESPACE WWFDATA

PCTFREE    10

INITRANS   2

MAXTRANS   255

STORAGE    (

            INITIAL          64K

            NEXT             1M

            MINEXTENTS       1

            MAXEXTENTS       UNLIMITED

            PCTINCREASE      0

            BUFFER_POOL      DEFAULT

            FLASH_CACHE      DEFAULT

            CELL_FLASH_CACHE DEFAULT

           )

NOPARALLEL;

CREATE INDEX STG.IDX_IN_ITEMBOMROUTING_DBA01 ON STG.IN_ITEMBOMROUTING(PRODUCED_ITEM_ID,SITEID)

LOGGING

TABLESPACE WWFDATA

PCTFREE    10

INITRANS   2

MAXTRANS   255

STORAGE    (

            INITIAL          64K

            NEXT             1M

            MINEXTENTS       1

            MAXEXTENTS       UNLIMITED

            PCTINCREASE      0

            BUFFER_POOL      DEFAULT

            FLASH_CACHE      DEFAULT

            CELL_FLASH_CACHE DEFAULT

           )

NOPARALLEL;