ENG Engineering Change Order Schedule

Description
Categories: BI Publisher
Imported from BI Publisher
Description: Engineering Change Order Schedule Report
Application: Engineering
Source: Engineering Change Order Schedule Report (XML)
Short Name: ENGRCNSR_XML
DB package: ENG_ENGRCNSR_XMLP_PKG

Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS

Contact us to schedule a demo or if you need help with the installation

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
Print Components
 
LOV Oracle
Engineering Change Order Type
 
LOV Oracle
Engineering Change Orders From
 
LOV Oracle
To
 
LOV Oracle
Effective Dates From
 
Date
To 2
 
Date
Revised Item Status
 
LOV Oracle
Display Option
 
LOV Oracle
Revised Item
 
Char
Revised Component
 
Char
Sort Criteria 1
 
LOV Oracle
2
 
LOV Oracle
3
 
LOV Oracle