ENG Engineering Change Order Schedule

Description
Categories: BI Publisher, Manufacturing
Application: Engineering
Source: Engineering Change Order Schedule Report (XML)
Short Name: ENGRCNSR_XML
DB package: ENG_ENGRCNSR_XMLP_PKG
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
Parameter Name SQL text Validation
Trace Flag
 
Dynamic Precision Option
 
LOV Oracle
Delete ECO
 
LOV Oracle
ORG_ID
 
Number
3
 
LOV Oracle
2
 
LOV Oracle
Sort Criteria 1
 
LOV Oracle
Revised Component
 
Revised Item
 
Display Option
 
LOV Oracle
Revised Item Status
 
LOV Oracle
To
 
Date
Effective Dates From
 
Date
To
 
LOV Oracle
Engineering Change Orders From
 
LOV Oracle
Engineering Change Order Type
 
LOV Oracle
Print Components
 
LOV Oracle