ECC Discrete Completions, Component Transactions Copy link

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