ECC Discrete Completions, Assembly Serial Transactions

➜ Back to Library
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

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
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           job_name,
                                    mmt.organization_id,
                                    mp.organization_code,
                                    msn.serial_number,
                                    msn.gen_object_id,
                                    to_char(msn.serial_number
											|| ' ('
											|| msi.concatenated_segments
											|| ', '
											|| mp.organization_code
											|| ')') SERIAL_ITEM_ORG,
                                    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('12-NOV-22 10:13:51','DD-MON-RR HH24:MI:SS')
                                    AND mmt.transaction_date <= TO_DATE('12-DEC-22 10:13:51','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           job_name,
                                    mmt.organization_id,
                                    mp.organization_code,
                                    msn.serial_number,
                                    msn.gen_object_id,
									to_char(msn.serial_number
											|| ' ('
											|| msi.concatenated_segments
											|| ', '
											|| mp.organization_code
											|| ')') SERIAL_ITEM_ORG,
                                    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('12-NOV-22 10:13:51','DD-MON-RR HH24:MI:SS')
                                    AND mmt.transaction_date <= TO_DATE('12-DEC-22 10:13:51','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
Download
 
Related Blitz Reports
INV Transaction Register INV Lot Transaction Register INV Material Transactions INV Serial number transaction register ECC Discrete Completions, Assembly Completion Transactions ECC Discrete Manufacturing, Serial Numbers ECC Genealogy and Trace, Process Batch ECC Process Genealogy and Trace, Process Batch ECC Genealogy and Trace, Work Orders
Blitz Report™

Blitz Report™ provides multiple benefits: