WIP Discrete Job Routing Sheet

Description
Categories: BI Publisher
Application: Work in Process
Source: Discrete Job Routing Sheet (XML)
Short Name: WIPDJRTG_XML
DB package: WIP_WIPDJRTG_XMLP_PKG
SELECT E.WIP_ENTITY_NAME Job,
             E.ORGANIZATION_ID C_ORGANIZATION_ID,
             E.WIP_ENTITY_ID WIP_ENTITY_ID10,
             D.DESCRIPTION DESCRIPTION,
             M.PRIMARY_UOM_CODE UOM,
             D.START_QUANTITY Start_Quantity,
            D.JOB_TYPE Job_Type,
             DECODE(M.INVENTORY_ITEM_ID, NULL, NULL,null) FLEXDATA,
             MSITL.DESCRIPTION Item_Desc,
             D.COMPLETION_SUBINVENTORY Subinventory,
            null CLOCFLEX,
            D.COMPLETION_LOCATOR_ID Compl_Loc_Id,
             D.BOM_REVISION BOM_Revision,
             D.BOM_REVISION_DATE BOM_REVISION_DATE,
             SUBSTR(TO_CHAR(D.BOM_REVISION_DATE,'HH24:MI'),1,5) BOM_TIME,
             D.SCHEDULED_START_DATE Scheduled_Start,
             SUBSTR(TO_CHAR(D.SCHEDULED_START_DATE,'HH24:MI'),1,5) START_TIME,
             LU.MEANING Status,
             D.ROUTING_REVISION Routing_Revision,
             D.ROUTING_REVISION_DATE ROUTING_REVISION_DATE,
             SUBSTR(TO_CHAR(D.ROUTING_REVISION_DATE,'HH24:MI'),1,5) ROUTING_TIME,
             D.SCHEDULED_COMPLETION_DATE Scheduled_Complete,
             DECODE(D.BOM_REFERENCE_ID,NULL,NULL,
                    null) C_Bill_Ref_Data,
                   SI2.DESCRIPTION Bill_Ref_Description,
             DECODE(D.ROUTING_REFERENCE_ID,NULL,NULL,
                    null) C_Routing_Ref_Data,
                   SI3.DESCRIPTION Rout_Ref_Description, SUBSTR(TO_CHAR(D.SCHEDULED_COMPLETION_DATE,'HH24:MI'),1,5) COMPLETION_TIME,
            SG.SCHEDULE_GROUP_NAME,
            D.BUILD_SEQUENCE,
            WL.LINE_CODE,
                DECODE(M.INVENTORY_ITEM_ID, NULL, NULL,fnd_flex_xml_publisher_apis.process_kff_combination_1('flexfield', 'INV', 'MSTK', 101, M.ORGANIZATION_ID, M.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE')) FLEXFIELD,
                DECODE(D.BOM_REFERENCE_ID,NULL,NULL,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_bill_reference', 'INV', 'MSTK', 101, SI3.ORGANIZATION_ID, SI3.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE')) C_Bill_Reference,
                DECODE(D.ROUTING_REFERENCE_ID,NULL,NULL,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_routing_reference', 'INV', 'MSTK', 101, SI2.ORGANIZATION_ID, SI2.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE')) C_Routing_Reference,
                DECODE(D.COMPLETION_LOCATOR_ID, NULL, NULL, fnd_flex_xml_publisher_apis.process_kff_combination_1('c_clocator', 'INV', 'MTLL', 101, L.ORGANIZATION_ID, L.INVENTORY_LOCATION_ID, 'ALL', 'Y', 'VALUE')) C_CLOCATOR
            FROM MTL_SYSTEM_ITEMS_B M,
             MTL_SYSTEM_ITEMS_TL MSITL,
            MTL_SYSTEM_ITEMS SI2,
            MTL_SYSTEM_ITEMS SI3,
             MFG_LOOKUPS LU,
            MTL_ITEM_LOCATIONS L,
            WIP_LINES WL,
            WIP_SCHEDULE_GROUPS SG,
             WIP_ENTITIES E,
             WIP_DISCRETE_JOBS D
            WHERE E.ORGANIZATION_ID = :P_ORGANIZATION_ID &NAME_RANGE &DATE_RANGE
            &C_SCHEDULE_GROUP
            AND SG.SCHEDULE_GROUP_ID &P_SG_OUTER = D.SCHEDULE_GROUP_ID
            AND SG.ORGANIZATION_ID &P_SG_OUTER = D.ORGANIZATION_ID
            AND WL.LINE_ID (+) = D.LINE_ID
            AND WL.ORGANIZATION_ID (+) = D.ORGANIZATION_ID
             AND L.INVENTORY_LOCATION_ID(+) = NVL(D.COMPLETION_LOCATOR_ID,'-1')
            AND L.ORGANIZATION_ID (+) = D.ORGANIZATION_ID
             AND D.WIP_ENTITY_ID = E.WIP_ENTITY_ID
             AND M.INVENTORY_ITEM_ID(+) = E.PRIMARY_ITEM_ID
             AND    MSITL.INVENTORY_ITEM_ID(+) = E.PRIMARY_ITEM_ID
             AND    MSITL.ORGANIZATION_ID(+) = :P_ORGANIZATION_ID
             AND    MSITL.LANGUAGE(+) = USERENV('LANG')
             AND LU.LOOKUP_TYPE = 'WIP_JOB_STATUS'
             AND LU.LOOKUP_CODE = D.STATUS_TYPE
             AND D.STATUS_TYPE IN (1,3,4,6)
             AND E.ENTITY_TYPE in (1,5)
             AND D.ORGANIZATION_ID = :P_ORGANIZATION_ID
             AND M.ORGANIZATION_ID(+) = :P_ORGANIZATION_ID
            AND    SI2.ORGANIZATION_ID (+) = :P_ORGANIZATION_ID
            AND    SI3.ORGANIZATION_ID (+) = :P_ORGANIZATION_ID
            AND    SI2.INVENTORY_ITEM_ID (+) = D.BOM_REFERENCE_ID
            AND    SI3.INVENTORY_ITEM_ID (+) = D.ROUTING_REFERENCE_ID
            ORDER BY &C_ORDER_BY
Parameter NameSQL textValidation
Jobs From
 
LOV Oracle
To
 
LOV Oracle
Scheduled Start Dates From
 
Date
To
 
Date
Schedule Group From
 
LOV Oracle
Schedule Group To
 
LOV Oracle
Sort By
 
LOV Oracle
Org_id
 
Number
Quantity precision
 
Number