ECC Discrete Completions, Assembly Serial Transactions

Description
Categories: Enterprise Command Center
Imported from Enterprise Command Center
Dataset Key: wip-com-serialtxns
Query Procedure: WIP_ECC_WC_PVT.GET_ECC_DATA_LOAD_INFO
Security Procedure: wip_ecc_datasecurity_pkg_pub.GetFilterAttributeValues
Run ECC Discrete Completions, Assembly Serial Transactions and other Oracle EBS reports with Blitz Report™ on our demo environment
select
x.*
from
(
SELECT * FROM (
                                SELECT
                                    st.*,
                                    serial_status_lkp.meaning    serial_status,
                                    entity_source_lkp.meaning    entity_source,
                                    serial_status_lkp.language   language
                                FROM
 
                                (
                                SELECT /*+ leading(mmt) index(mmt MTL_MATERIAL_TRANSACTIONS_N5) cardinality(mmt 10) use_nl(msn) index(msn MTL_SERIAL_NUMBERS_U1) */
                                    to_char(msn.gen_object_id) ecc_spec_id,
                                    mut.transaction_id           transaction_id,
                                    we.wip_entity_id,
                                    we.wip_entity_name           workorder,
                                    mmt.organization_id,
                                    mp.organization_code,
                                    msn.serial_number,
                                    msn.gen_object_id,
                                    msi.concatenated_segments    assembly,
                                    msi.description              assembly_description,
                                    mmt.transaction_date,
                                    mmt.subinventory_code,
                                    decode(mp.project_reference_enabled, NULL, milk.concatenated_segments, 2, milk.concatenated_segments,
                                           1, inv_project.get_pjm_locsegs(milk.concatenated_segments)) completion_locator,
                                    (
                                        SELECT
                                            mtt.transaction_type_name
                                        FROM
                                            mtl_transaction_types mtt
                                        WHERE
                                            mtt.transaction_type_id = mmt.transaction_type_id
                                    ) transaction_type_name,
                                    mtr.description              transaction_reason_desc,
                                    mtr.reason_name              transaction_reason,
                                    we.entity_type ,
                                    msn.current_status
                                FROM
                                    mtl_material_transactions   mmt,
                                    mtl_unit_transactions       mut,
                                    wip_entities                we,
                                    mtl_transaction_reasons     mtr,
                                    mtl_system_items_vl         msi,
                                    mtl_item_locations_kfv      milk,
                                    mtl_serial_numbers          msn,
                                    mtl_parameters              mp
 
                                WHERE
                                    mmt.transaction_source_type_id = 5
                                    AND mmt.transaction_action_id IN (
                                        31,
                                        32
                                    )
                                    AND mmt.transaction_source_id = we.wip_entity_id
                                    AND mmt.transaction_id = mut.transaction_id
                                    AND msi.lot_control_code = 1
                                    AND mmt.organization_id = msi.organization_id
                                    AND mmt.inventory_item_id = msi.inventory_item_id
                                    AND mmt.organization_id = mp.organization_id
                                    AND we.entity_type IN (
                                        1,
                                        3,
                                        4
                                    )
                                    AND mmt.locator_id = milk.inventory_location_id (+)
                                    AND mmt.transaction_date > TO_DATE('18-MAR-21 11:53:29','DD-MON-RR HH24:MI:SS')
                                    AND mmt.transaction_date <= TO_DATE('17-APR-21 11:53:29','DD-MON-RR HH24:MI:SS')
                                    AND mmt.reason_id = mtr.reason_id (+)
                                    AND mut.inventory_item_id = msn.inventory_item_id
                                    AND mut.serial_number = msn.serial_number
 
                                UNION
                                SELECT /*+ leading(mmt) index(mmt MTL_MATERIAL_TRANSACTIONS_N5) cardinality(mmt 10) use_nl(msn) index(msn MTL_SERIAL_NUMBERS_U1) INDEX(MTLN MTL_TRANSACTION_LOT_NUMBERS_N1)
*/
                                    to_char(msn.gen_object_id) ecc_spec_id,
                                    mut.transaction_id           transaction_id,
                                    we.wip_entity_id,
                                    we.wip_entity_name           workorder,
                                    mmt.organization_id,
                                    mp.organization_code,
                                    msn.serial_number,
                                    msn.gen_object_id,
                                    msi.concatenated_segments    assembly,
                                    msi.description              assembly_description,
                                    mmt.transaction_date,
                                    mmt.subinventory_code,
                                    decode(mp.project_reference_enabled, NULL, milk.concatenated_segments, 2, milk.concatenated_segments,
                                           1, inv_project.get_pjm_locsegs(milk.concatenated_segments)) completion_locator,
                                    (
                                        SELECT
                                            mtt.transaction_type_name
                                        FROM
                                            mtl_transaction_types mtt
                                        WHERE
                                            mtt.transaction_type_id = mmt.transaction_type_id
                                    ) transaction_type_name,
                                    mtr.description              transaction_reason_desc,
                                    mtr.reason_name              transaction_reason,
                                    we.entity_type,
                                    msn.current_status
                                FROM
                                    mtl_material_transactions     mmt,
                                    mtl_unit_transactions         mut,
                                    wip_entities                  we,
                                    mtl_transaction_reasons       mtr,
                                    mtl_system_items_vl           msi,
                                    mtl_item_locations_kfv        milk,
                                    mtl_serial_numbers            msn,
                                    mtl_parameters                mp,
                                    mtl_transaction_lot_numbers   mtln
 
                                WHERE
                                    mmt.transaction_source_type_id = 5
                                    AND mmt.transaction_action_id IN (
                                        31,
                                        32
                                    )
                                    AND mmt.transaction_source_id = we.wip_entity_id
                                    AND msi.lot_control_code <> 1
                                    AND mmt.transaction_id = mtln.transaction_id
                                    AND mtln.serial_transaction_id = mut.transaction_id
                                    AND mmt.organization_id = msi.organization_id
                                    AND mmt.inventory_item_id = msi.inventory_item_id
                                    AND mmt.organization_id = mp.organization_id
                                    AND we.entity_type IN (
                                        1,
                                        3,
                                        4
                                    )
                                    AND mmt.locator_id = milk.inventory_location_id (+)
                                    AND mmt.transaction_date > TO_DATE('18-MAR-21 11:53:29','DD-MON-RR HH24:MI:SS')
                                    AND mmt.transaction_date <= TO_DATE('17-APR-21 11:53:29','DD-MON-RR HH24:MI:SS')
                                    AND mmt.reason_id = mtr.reason_id (+)
                                    AND mut.inventory_item_id = msn.inventory_item_id
                                    AND mut.serial_number = msn.serial_number
                                ) st ,
                                (
                                    SELECT
                                        flv.meaning,
                                        lookup_code,
                                        language
                                    FROM
                                        fnd_lookup_values flv
                                    WHERE
                                        flv.lookup_type = 'SERIAL_NUM_STATUS'
                                ) serial_status_lkp,
                                (
                                    SELECT
                                        meaning,
                                        language,
                                        lookup_code
                                    FROM
                                        fnd_lookup_values
                                    WHERE
                                        lookup_type = 'WIP_ENTITY'
                                )  entity_source_lkp
 
                                WHERE
                                     entity_source_lkp.lookup_code = to_char(st.entity_type)
                                    AND entity_source_lkp.language = serial_status_lkp.language
                                    AND serial_status_lkp.lookup_code = st.current_status
                                    AND serial_status_lkp.language IN ('US')
 
                                ) PIVOT (
                                MAX ( serial_status )
                            AS serial_status, MAX ( entity_source ) AS entity_source FOR language  IN ('US' "US"))
) x
where
2=2
Blitz Report™