ECC Genealogy and Trace, Work Orders

Description
Categories: Enterprise Command Center
Columns: Ecc Spec Id, Organization Code, Organization Id, Wip Entity Name, Inventory Item Id, Assembly, Description, Primary Uom Code, Transaction Id, Transaction Source Id ...
Imported from Enterprise Command Center
Dataset Key: gnt-wodetails
Query Procedure: WIP_ECC_GNT_PVT.GET_ECC_DATA_LOAD_INFO
Security Procedure: wip_ecc_datasecurity_pkg_pub.GetGntFilterAttributeValues
select
x.*
from
(
SELECT * FROM
                    (
                    SELECT /*+ leading(wo_txns) no_merge(wo_txns)  */  to_char(wo_txns.type||'-'||wo_txns.transaction_id||'-'||wo_txns.gen_object_id) ecc_spec_id,
                        mp.organization_code,
                        mp.organization_id,
                        wo_txns.wip_entity_name,
                        wo_txns.primary_item_id inventory_item_id,
                        status_lkp.meaning job_status,
                        nodetype_lkp.meaning node_type,
                        msiv.concatenated_segments assembly,
                        msiv.description,
                        msiv.primary_uom_code,
                        wo_txns.transaction_id,
                        wo_txns.transaction_source_id,
                        decode(wo_txns.type, 1, wo_txns.gen_object_id) lot_gen_obj_id,
                        decode(wo_txns.type, 2, wo_txns.gen_object_id) ser_gen_obj_id,
                        decode(wo_txns.type, 3, wo_txns.gen_object_id) res_wo_id,
                        wo_txns.transaction_date,
                        wo_txns.transaction_quantity,
                        wo_txns.transaction_uom,
                        wo_txns.revision,
                        mtt.transaction_type_name,
                        status_lkp.language       language,
                        decode(wo_txns.entity_type,3,1,wo_txns.entity_type) entity_type,
                        /* action_lkp.meaning */ 'QA' WO_QA_RESULTS,
                        CASE WHEN wo_txns.entity_type IN (1,3,5,8) THEN wdj.start_quantity
                             WHEN wo_txns.entity_type = 4 THEN wfs.planned_quantity
                             ELSE wrs.daily_production_rate * wrs.processing_work_days END start_quantity,
                        CASE WHEN wo_txns.entity_type IN (1,3,5,8) THEN wdj.quantity_completed
                             WHEN wo_txns.entity_type = 4 THEN wfs.quantity_completed
                             ELSE wrs.quantity_completed END quantity_completed,
                        CASE WHEN wo_txns.entity_type IN (1,3,5,8) THEN wdj.scheduled_start_date
                            WHEN wo_txns.entity_type = 4 THEN wfs.scheduled_start_date
                            ELSE wrs.first_unit_start_date END scheduled_start_date,
                        CASE WHEN wo_txns.entity_type IN (1,3,5,8) THEN wdj.scheduled_completion_date
                             WHEN wo_txns.entity_type = 4 THEN wfs.scheduled_completion_date
                             ELSE wrs.last_unit_completion_date END scheduled_completion_date,
                        CASE WHEN wo_txns.entity_type IN (1,3,5,8) THEN wdj.bom_revision
                             WHEN wo_txns.entity_type = 4 THEN wfs.bom_revision
                             ELSE wrs.bom_revision END bom_revision,
                        CASE WHEN wo_txns.entity_type IN (1,3,5,8) THEN wdj.routing_revision
                             WHEN wo_txns.entity_type = 4 THEN wfs.routing_revision
                             ELSE wrs.routing_revision END routing_revision,
                        CASE WHEN wo_txns.entity_type IN (1,3,5,8) THEN wdj.bom_revision_date
                             WHEN wo_txns.entity_type = 4 THEN wfs.bom_revision_date
                             ELSE wrs.bom_revision_date END bom_revision_date,
                        CASE WHEN wo_txns.entity_type IN (1,3,5,8) THEN wdj.routing_revision_date
                             WHEN wo_txns.entity_type = 4 THEN wfs.routing_revision_date
                             ELSE wrs.routing_revision_date END routing_revision_date,
                        CASE WHEN wo_txns.entity_type IN (1,3,5,8) THEN wdj.alternate_bom_designator
                            WHEN wo_txns.entity_type = 4 THEN wfs.alternate_bom_designator
                            ELSE wrs.alternate_bom_designator END alternate_bom_designator,
                        CASE WHEN wo_txns.entity_type IN (1,3,5,8) THEN wdj.alternate_routing_designator
                            WHEN wo_txns.entity_type = 4 THEN wfs.alternate_routing_designator
                            ELSE wrs.alternate_routing_designator END alternate_routing_designator,
                        (SELECT wl.line_code FROM wip_lines wl WHERE wl.line_id =
                                CASE WHEN wo_txns.entity_type IN (1,3,5,8) THEN wdj.line_id
                                     WHEN wo_txns.entity_type = 4 THEN wfs.line_id
                                     ELSE wrs.line_id END ) line_code,
                        CASE WHEN wo_txns.entity_type IN (1,3,5,8) THEN wdj.status_type
                             WHEN wo_txns.entity_type = 4 THEN decode(wfs.status, 2, 12, 3)
                             ELSE wrs.status_type END status_type
                    FROM ( SELECT /*+ leading(mmt) INDEX(mmt MTL_MATERIAL_TRANSACTIONS_N5)  */ 
                                mmt.transaction_source_id,
                                (select mln.gen_object_id
                                 from mtl_lot_numbers mln
                                 where mln.lot_number = mtln.lot_number
                                 and mln.inventory_item_id = mtln.inventory_item_id
                                 and mln.organization_id = mtln.organization_id) gen_object_id,
                                1 type,
                                mtln.transaction_date transaction_date,
                                abs(mtln.transaction_quantity) transaction_quantity,
                                mtln.transaction_id,
                                mmt.transaction_uom,
                                mmt.transaction_action_id,
                                mmt.transaction_type_id,
                                mtln.serial_transaction_id,
                                we.entity_type,
                                we.organization_id,
                                we.wip_entity_name,
                                mtln.inventory_item_id,
                                mmt.revision,
                                we.primary_item_id
                            FROM mtl_material_transactions     mmt,
                                mtl_transaction_lot_numbers   mtln,
                                wip_entities                  we
                            WHERE mmt.transaction_id = mtln.transaction_id
                                AND mmt.transaction_source_id = we.wip_entity_id
                                AND mmt.organization_id = we.organization_id
                                AND we.entity_type IN (1,2,3,4,5,8)
                                AND mmt.transaction_source_type_id = 5
                                AND mmt.transaction_action_id IN (30,31,32,40,41,42,43)
                                AND mmt.organization_id = nvl('',mmt.organization_id)
                                AND mmt.transaction_date >= TO_DATE('17-APR-20 11:47:47','DD-MON-RR HH24:MI:SS')
                                AND mmt.transaction_date <= TO_DATE('17-APR-21 11:47:47','DD-MON-RR HH24:MI:SS')
                            UNION ALL
                            SELECT /*+ leading(mmt) INDEX(mmt MTL_MATERIAL_TRANSACTIONS_N5)  */ 
                                mmt.transaction_source_id,
                                (select msn.gen_object_id from
                                 mtl_serial_numbers msn
                                 where msn.serial_number = mut.serial_number
                                 and msn.inventory_item_id = mut.inventory_item_id) gen_object_id,
                                2,
                                mut.transaction_date transaction_date,
                                1 transaction_quantity,
                                mut.transaction_id,
                                mmt.transaction_uom,
                                mmt.transaction_action_id,
                                mmt.transaction_type_id,
                                NULL,
                                we.entity_type,
                                we.organization_id,
                                we.wip_entity_name,
                                mut.inventory_item_id,
                                mmt.revision,
                                we.primary_item_id
                            FROM mtl_material_transactions   mmt,
                                mtl_unit_transactions       mut,
                                wip_entities                we
                            WHERE mmt.transaction_id = mut.transaction_id
                                AND mmt.transaction_source_id = we.wip_entity_id
                                AND mmt.organization_id = we.organization_id
                                AND we.entity_type IN (1,2,3,4,5,8)
                                AND mmt.organization_id = nvl('',mmt.organization_id)
                                AND mmt.transaction_source_type_id = 5
                                AND mmt.transaction_action_id IN (30,31,32,40,41,42,43)
                                AND mmt.transaction_date >= TO_DATE('17-APR-20 11:47:47','DD-MON-RR HH24:MI:SS')
                                AND mmt.transaction_date <= TO_DATE('17-APR-21 11:47:47','DD-MON-RR HH24:MI:SS')
                            UNION ALL
                            SELECT /*+ leading(mmt) INDEX(mmt MTL_MATERIAL_TRANSACTIONS_N5)  */ 
                                mmt.transaction_source_id,
                                NULL,
                                0,
                                NULL,
                                NULL,
                                mmt.transaction_id,
                                NULL,
                                NULL,
                                mmt.transaction_type_id,
                                NULL,
                                we.entity_type,
                                we.organization_id,
                                we.wip_entity_name,
                                mmt.inventory_item_id,
                                mmt.revision,
                                we.primary_item_id
                            FROM
                                mtl_material_transactions   mmt,
                                wip_entities                we
                            WHERE
                                mmt.transaction_source_id = we.wip_entity_id
                                AND mmt.organization_id = we.organization_id
                                AND mmt.organization_id = nvl('',mmt.organization_id)
                                AND mmt.transaction_source_type_id = 5
                                AND mmt.transaction_action_id NOT IN (30,31,32,40,41,42,43)
                                AND we.entity_type IN (1,2,3,4,5,8)
                                AND mmt.transaction_date >= TO_DATE('17-APR-20 11:47:47','DD-MON-RR HH24:MI:SS')
                                AND mmt.transaction_date <= TO_DATE('17-APR-21 11:47:47','DD-MON-RR HH24:MI:SS')
                            UNION ALL
                            SELECT /*+ leading(txn) INDEX(txn WSM_SPLIT_MERGE_TXNS_ECC_N1)  */ 
                                st.wip_entity_id,
                                rs.wip_entity_id,
                                3,
                                txn.transaction_date,
                                decode(txn.transaction_type_id, 1, rs.start_quantity, st.available_quantity),
                                txn.transaction_id,
                                msi.primary_uom_code,
                                decode(txn.transaction_type_id, 1, 40, 41),
                                decode(txn.transaction_type_id, 1, 55, 56),
                                NULL,
                                we.entity_type,
                                we.organization_id,
                                we.wip_entity_name,
                                st.primary_item_id,
                                st.bom_revision,
                                we.primary_item_id
                            FROM
                                wsm_sm_starting_jobs           st,
                                wsm_sm_resulting_jobs          rs,
                                wsm_split_merge_transactions   txn,
                                mtl_system_items               msi,
                                wip_entities                   we
                            WHERE
                                txn.transaction_id = st.transaction_id
                                AND txn.transaction_id = rs.transaction_id
                                AND st.wip_entity_id = we.wip_entity_id
                                AND txn.organization_id = we.organization_id
                                AND we.entity_type IN (5,8)
                                AND txn.transaction_type_id IN (1,2)
                                AND txn.transaction_date >= TO_DATE('17-APR-20 11:47:47','DD-MON-RR HH24:MI:SS')
                                AND txn.transaction_date <= TO_DATE('17-APR-21 11:47:47','DD-MON-RR HH24:MI:SS')
                                AND rs.primary_item_id = msi.inventory_item_id
                                AND txn.organization_id = msi.organization_id
                                AND txn.organization_id = nvl('',txn.organization_id)
                        ) wo_txns,
                        (SELECT ml1.meaning,ml1.lookup_code,ml1.language
                         FROM fnd_lookup_values ml1
                         WHERE ml1.lookup_type = 'WIP_JOB_STATUS'
                         AND ml1.view_application_id = 700
                         AND ml1.security_group_id = 0
                        ) status_lkp,
                       (select meaning , language , lookup_code
                        FROM fnd_lookup_values
                        WHERE lookup_type = 'WIP_GNT_ECC_NW_NODETYPE'
                       ) nodetype_lkp ,
					   /*
                       (select meaning , language
                        FROM fnd_lookup_values
                        WHERE lookup_type = 'WIP_GNT_ACTION_LINK_TYPE'
                        AND lookup_code = 4
                       )action_lkp , */
                       wip_discrete_jobs          wdj,
                       wip_flow_schedules         wfs,
                       wip_repetitive_schedules   wrs,
                       mtl_system_items_vl        msiv,
                       mtl_parameters             mp,
                       mtl_transaction_types      mtt
                       WHERE
                       wo_txns.transaction_source_id = wdj.wip_entity_id (+)
                       AND wo_txns.organization_id = wdj.organization_id (+)
                       AND wo_txns.transaction_source_id = wfs.wip_entity_id (+)
                       AND wo_txns.organization_id = wfs.organization_id (+)
                       AND wo_txns.transaction_source_id = wrs.wip_entity_id (+)
                       AND wo_txns.organization_id = wrs.organization_id (+)
                       AND wo_txns.organization_id = msiv.organization_id (+)
                       AND wo_txns.primary_item_id = msiv.inventory_item_id(+)
                       AND wo_txns.organization_id = mp.organization_id
                       AND wo_txns.transaction_type_id = mtt.transaction_type_id
                       AND status_lkp.lookup_code = to_char(decode(wo_txns.entity_type,2,wrs.status_type,4,decode(wfs.status,2,12,3),wdj.status_type))
                       AND nodetype_lkp.lookup_code = to_char(decode(wo_txns.entity_type,1,5,3,5,4,4,5,6,8,6,2,3,5))
                       AND nodetype_lkp.language = status_lkp.language
                       /* AND action_lkp.language = status_lkp.language */ AND status_lkp.language IN ('US') ) wo PIVOT (
                        MAX ( job_status ) AS job_status , MAX( node_type) AS node_type
						/* , MAX(WO_QA_RESULTS) AS WO_QA_RESULTS */
						FOR language  IN ('US' "US")) 
) x
where
2=2
Download
 
Blitz Report In Action
Blitz Report™

Blitz Report™ provides multiple benefits: