ECC Discrete Completions, Component Transactions

➜ Back to Library
Description
Categories: Enterprise Command Center
Imported from Enterprise Command Center
Dataset Key: wip-com-componenttxns
Query Procedure: WIP_ECC_WC_PVT.GET_ECC_DATA_LOAD_INFO
Security Procedure: wip_ecc_datasecurity_pkg_pub.GetFilterAttributeValues
Run ECC Discrete Completions, Component Transactions and other Oracle EBS reports with Blitz Report™ on our demo environment
select
x.*
from
(
SELECT * FROM (SELECT /*+ leading(mmt1) index(mmt1 MTL_MATERIAL_TRANSACTIONS_N5) cardinality(mmt1 10) use_nl(we) */
                                            to_char(mmt1.transaction_id) ecc_spec_id,
                                            mmt1.transaction_id,
                                            entity_source_lkp.meaning entity_source,
                                            entity_source_lkp.language,
                                           'fwkhp_sswafunc.gif' as ITEM_LINK, /* view_item_lkp.meaning  item_link,*/
                                           'fwkhp_sswafunc.gif' as  QUALITY_LINK,/* view_quality_lkp.meaning  quality_link, */
                                            we.entity_type,
                                            we.wip_entity_id,
                                            we.wip_entity_name,
                                            WIP_ECC_WC_PVT.get_concatenated_segments(mmt1.organization_id, we.primary_item_id) assembly,
                                            WIP_ECC_WC_PVT.get_description(mmt1.organization_id, we.primary_item_id) assemblydesc,
                                            mmt1.operation_seq_num,
                                            WIP_ECC_WC_PVT.get_concatenated_segments(mmt1.organization_id, mmt1.inventory_item_id) component,
                                            WIP_ECC_WC_PVT.get_description(mmt1.organization_id, mmt1.inventory_item_id) compdesc,
                                            (SELECT acat.concatenated_segments FROM mtl_item_catalog_groups_b_kfv acat
                                                WHERE acat.item_catalog_group_id = WIP_ECC_WC_PVT.get_item_catalog_group_id(mmt1.organization_id, mmt1.inventory_item_id)
                                            ) component_category,
                                            WIP_ECC_WC_PVT.get_primary_uom_code(mmt1.organization_id, mmt1.inventory_item_id) comppriuom,
                                            abs(mmt1.primary_quantity) primary_quantity,
                                            abs(mmt1.transaction_quantity) transaction_quantity,
                                            mmt1.transaction_uom,
                                            mmt1.transaction_action_id,
                                            mmt1.transaction_type_id,
                                            mmt1.transaction_date,
                                            (
                                                SELECT
                                                    mtt.transaction_type_name
                                                FROM
                                                    mtl_transaction_types mtt
                                                WHERE
                                                    mtt.transaction_type_id = mmt1.transaction_type_id
                                            ) transaction_type_name,
                                            mmt1.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,
                                            mtr.description   transaction_reason_desc,
                                            mtr.reason_name   transaction_reason,
                                            mp.organization_id,
                                            mp.organization_code,
                                            mmt1.transaction_source_id
                                        FROM
                                            mtl_material_transactions   mmt1,
                                            wip_entities                we,
                                            mtl_transaction_reasons     mtr,
                                            mtl_parameters              mp,
                                            mtl_item_locations_kfv      milk ,
                                                (
                                                SELECT
                                                    meaning , lookup_code, language
                                                FROM
                                                    fnd_lookup_values
                                                WHERE
                                                    lookup_type = 'WIP_ENTITY'
                                            ) entity_source_lkp
                                           /* (
                                                  SELECT
                                                    ml1.meaning,
                                                    ml1.lookup_code,
                                                    ml1.language
                                                  FROM fnd_lookup_values ml1
                                                  WHERE ml1.lookup_type       = 'WIP_VIEW_QUALITY_NC'
                                                  AND ml1.view_application_id = 700
                                                  AND ml1.security_group_id   = 0
                                                  AND ml1.lookup_code         = 3
                                             ) view_quality_lkp,
                                             (
                                                  SELECT
                                                    ml1.meaning,
                                                    ml1.lookup_code,
                                                    ml1.language
                                                  FROM fnd_lookup_values ml1
                                                  WHERE ml1.lookup_type       = 'WIP_VIEW_ITEM'
                                                  AND ml1.view_application_id = 700
                                                  AND ml1.security_group_id   = 0
                                                  AND ml1.lookup_code         = 1
                                             ) view_item_lkp
											 */
                                        WHERE
                                            mmt1.transaction_source_type_id = 5
                                            AND mmt1.transaction_action_id IN (
                                                1,
                                                27,
                                                33,
                                                34
                                            )
                                            AND mmt1.transaction_source_id = we.wip_entity_id
                                            AND we.entity_type IN (
                                                1,
                                                3,
                                                4
                                            )
                                            AND mmt1.transaction_date > TO_DATE('12-NOV-22 10:13:51','DD-MON-RR HH24:MI:SS')
                                            AND mmt1.transaction_date <= TO_DATE('12-DEC-22 10:13:51','DD-MON-RR HH24:MI:SS')
                                            AND mmt1.organization_id = mp.organization_id
                                            AND mmt1.locator_id = milk.inventory_location_id (+)
                                            AND mmt1.reason_id = mtr.reason_id (+)
                                            AND EXISTS (
                                                SELECT
                                                    1
                                                FROM
                                                    mtl_material_transactions mmt
                                                WHERE
                                                    mmt.transaction_source_type_id = 5
                                                    AND mmt.transaction_action_id IN (
                                                        31,
                                                        32
                                                    )
                                                    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.organization_id = mmt1.organization_id
                                                    AND mmt.transaction_source_id = mmt1.transaction_source_id
                                                    AND ROWNUM = 1
                                            )
                                            AND entity_source_lkp.lookup_code = to_char(we.entity_type)
                                          /*  AND view_item_lkp.language = entity_source_lkp.language
                                            AND view_quality_lkp.language = entity_source_lkp.language */
                                            AND entity_source_lkp.language in ('US')
 
                                ) PIVOT (
                                MAX ( entity_source ) AS entity_source /* MAX( item_link ) AS item_link , MAX( quality_link ) AS quality_link */ FOR language  IN ('US' "US"))
) x
where
2=2
Download
Related Blitz Reports
ECC Discrete Completions, Assembly Completion Transactions ECC Discrete Manufacturing, Serial Numbers ECC Discrete Completions, Assembly Serial Transactions ECC Discrete Manufacturing, Work Orders GL Account Analysis (Distributions) ECC Discrete Manufacturing, Components GL Account Analysis CAC Purchase Price Variance INV Material Transactions
Blitz Report™

Blitz Report™ provides multiple benefits: