ENG Engineering Change Order Schedule

SELECT
                   C.CHANGE_NOTICE CHANGE_NOTICE10,
                   T.TYPE_NAME CHANGE_ORDER_TYPE,
                   C.REASON_CODE,
                   C.PRIORITY_CODE,
                   C.INITIATION_DATE,
                   F.ITEM_NUMBER,
                   F.DESCRIPTION,
                   B.ALTERNATE_BOM_DESIGNATOR,
                   R.NEW_ITEM_REVISION,
                   R.SCHEDULED_DATE,
                   R.NEW_ROUTING_REVISION,
                   R.FROM_CUM_QTY,
                   R.LOT_NUMBER,
                   DECODE(R.ECO_FOR_PRODUCTION,1,:P_YES,:P_NO) ECO_FOR_PRODUCTION,
                   WI1.WIP_ENTITY_NAME,
                   WI2.WIP_ENTITY_NAME,
                   L.MEANING,
               DECODE(R.IMPLEMENTATION_DATE, NULL, DECODE(R.CANCELLATION_DATE, NULL,
            (TRUNC(R.SCHEDULED_DATE) - TRUNC(SYSDATE)), NULL), NULL),
                   R.BILL_SEQUENCE_ID,
                   DECODE(:ORDER_BY1, 1, C.CHANGE_NOTICE, 2, F.PADDED_ITEM_NUMBER,
                         3, TO_CHAR(R.SCHEDULED_DATE,'YYYYMMDD')) O1,
                   DECODE(:ORDER_BY2, 1, C.CHANGE_NOTICE, 2, F.PADDED_ITEM_NUMBER,
                         3, TO_CHAR(R.SCHEDULED_DATE,'YYYYMMDD')) O2,
                   DECODE(:ORDER_BY3, 1, C.CHANGE_NOTICE, 2, F.PADDED_ITEM_NUMBER,
                         3, TO_CHAR(R.SCHEDULED_DATE,'YYYYMMDD')) O3,
                   R.REVISED_ITEM_SEQUENCE_ID REVISED_ITEM_SEQUENCE_ID10
            FROM   ENG_ENGINEERING_CHANGES C,
                   ENG_REVISED_ITEMS R,
                   MTL_ITEM_FLEXFIELDS F,
                  BOM_BILL_OF_MATERIALS B,
                   ENG_CHANGE_ORDER_TYPES_VL T,
                   WIP_ENTITIES WI1,
                   WIP_ENTITIES WI2,
                   MFG_LOOKUPS L
            WHERE  C.ORGANIZATION_ID = :ORG_ID
            AND    R.ORGANIZATION_ID = :ORG_ID
            AND    R.CHANGE_NOTICE = C.CHANGE_NOTICE
            AND    R.STATUS_TYPE = L.LOOKUP_CODE
            AND    L.LOOKUP_TYPE = 'ECG_ECN_STATUS'
            AND    R.BILL_SEQUENCE_ID = B.BILL_SEQUENCE_ID (+)
            AND    WI1.WIP_ENTITY_ID (+) = R.FROM_WIP_ENTITY_ID
            AND    WI1.ORGANIZATION_ID (+) = R.ORGANIZATION_ID
            AND    WI2.WIP_ENTITY_ID (+) = R.TO_WIP_ENTITY_ID
            AND    WI2.ORGANIZATION_ID  (+) = R.ORGANIZATION_ID
            AND    C.CHANGE_ORDER_TYPE_ID = T.CHANGE_ORDER_TYPE_ID
            AND    R.REVISED_ITEM_ID = F.ITEM_ID
            AND    F.ORGANIZATION_ID = :ORG_ID
            AND    C.CHANGE_NOTICE BETWEEN NVL(:ECN_FROM,'         0') AND
                         NVL(:ECN_TO, 'zzzzzzzzzz')
            AND    C.CHANGE_ORDER_TYPE_ID = NVL(:CHANGE_ORDER_TYPE_ID,
                           C.CHANGE_ORDER_TYPE_ID)
            AND    R.SCHEDULED_DATE BETWEEN
                        NVL(:C_DATE_FROM, TO_DATE('1901/01/01','YYYY/MM/DD')) AND
                        NVL(:C_DATE_TO, TO_DATE('4000/12/31','YYYY/MM/DD'))
            AND    R.STATUS_TYPE = NVL(:REVISED_ITEM_STATUS, R.STATUS_TYPE)
            AND    ((:HISTORY_CODE = 1
                     AND R.IMPLEMENTATION_DATE IS NULL
                     AND R.CANCELLATION_DATE IS NULL)
                   OR
                    (:HISTORY_CODE = 2
                     AND (R.IMPLEMENTATION_DATE IS NOT NULL
                         OR R.CANCELLATION_DATE IS NOT NULL))
                   OR (:HISTORY_CODE = 3))
            AND  ((:P_REVISED_ITEM_ID IS NULL AND :P_REVISED_COMPONENT_ITEM_ID IS NULL)
                    OR (:P_REVISED_ITEM_ID IS NOT NULL
                        AND :P_REVISED_COMPONENT_ITEM_ID IS NULL
                        AND R.REVISED_ITEM_ID = :P_REVISED_ITEM_ID)
                    OR  (:P_REVISED_COMPONENT_ITEM_ID IS NOT NULL
                        AND :P_REVISED_ITEM_ID IS NULL
                        AND EXISTS (SELECT NULL
                               FROM ENG_COMPONENT_VIEW V
                               WHERE V.CHANGE_NOTICE = C.CHANGE_NOTICE
                               AND   V.COMPONENT_ITEM_ID = :P_REVISED_COMPONENT_ITEM_ID
                               AND   V.REVISED_ITEM_SEQUENCE_ID =
                                     R.REVISED_ITEM_SEQUENCE_ID))
                         OR  (:P_REVISED_COMPONENT_ITEM_ID IS NOT NULL
                              AND :P_REVISED_ITEM_ID IS NOT NULL
                              AND  (R.REVISED_ITEM_ID = :P_REVISED_ITEM_ID
                                   OR EXISTS (SELECT NULL
                               FROM ENG_COMPONENT_VIEW V
                              WHERE V.CHANGE_NOTICE = C.CHANGE_NOTICE
                               AND   V.COMPONENT_ITEM_ID = :P_REVISED_COMPONENT_ITEM_ID
                               AND   V.REVISED_ITEM_SEQUENCE_ID =
                                     R.REVISED_ITEM_SEQUENCE_ID)))
            )
            ORDER BY 20, 21, 22