ECC Discrete Completions, Assembly Completion Transactions

select
x.*
from
(
 SELECT * FROM (SELECT /*+ leading(mmt) index(mmt MTL_MATERIAL_TRANSACTIONS_N5) cardinality(mmt10) */
                                            mmt.transaction_id          ecc_spec_id,
                                            we.wip_entity_id,
                                            we.wip_entity_name          job_name,
                                            mp.organization_id,
                                            mp.organization_code,
                                            org_name_lkp.name organization_name,
                                            org_name_lkp.language language,
                                            entity_source_lkp.meaning entity_source,
                                            entity_name_lkp.meaning job_type,
                                            transaction_lkp.meaning transaction_meaning,
                                           'fwkhp_sswafunc.gif' as ITEM_LINK, /* view_item_lkp.meaning */
                                           'fwkhp_sswafunc.gif' as QUALITY_LINK, /* view_quality_lkp.meaning  quality_link */
                                            status_lkp.meaning    job_status,
                                            wdj.status_type job_status_flag,
                                            mmt.transaction_date    completion_date,
                                            mmt.inventory_item_id,
                                            msi.concatenated_segments   assembly,
                                            msi.description             assembly_description,
                                            msi.primary_uom_code        primary_uom_code,
                                            msi.fixed_lead_time         fixed_lead_time,
                                            msi.variable_lead_time      variable_lead_time,
                                            msi.planner_code            planner,
                                            CASE
                                                    WHEN mmt.transaction_type_id = 44 THEN
                                                        mmt.primary_quantity
                                                    ELSE
                                                        0
                                            END completed_quantity,
                                            CASE
                                                    WHEN mmt.transaction_type_id = 90 THEN
                                                        mmt.primary_quantity
                                                    ELSE
                                                        0
                                            END scrap_quantity,
                                            CASE
                                                    WHEN mmt.transaction_type_id = 17 THEN
                                                        ABS(mmt.primary_quantity)
                                                    ELSE
                                                        0
                                            END return_quantity,
                                            decode(msi.base_item_id, NULL, NULL, WIP_ECC_WC_PVT.get_concatenated_segments(msi.organization_id, msi.base_item_id)) base_model
                                            ,
                                            (
                                                SELECT
                                                    acat.concatenated_segments
                                                FROM
                                                    mtl_item_catalog_groups_b_kfv acat
                                                WHERE
                                                    acat.item_catalog_group_id = msi.item_catalog_group_id
                                            ) assembly_catalog_category,
                                            we.entity_type,
                                            abs(mmt.primary_quantity) primary_quantity,
                                            abs(mmt.transaction_quantity) transaction_quantity,
                                            mmt.transaction_uom,
                                            mmt.transaction_action_id,
                                            mmt.transaction_type_id,
                                            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,
                                            mtr.description             transaction_reason_desc,
                                            mtr.reason_name             transaction_reason,
                                            (
                                                SELECT
                                                    mtt.transaction_type_name
                                                FROM
                                                    mtl_transaction_types mtt
                                                WHERE
                                                    mtt.transaction_type_id = mmt.transaction_type_id
                                            ) transaction_type_name,
                                            (
                                                SELECT
                                                    schedule_group_name
                                                FROM
                                                    wip_schedule_groups
                                                WHERE
                                                    schedule_group_id = decode(we.entity_type, 4, wfs.schedule_group_id, wdj.schedule_group_id)
                                            ) schedule_group,
                                            decode(we.entity_type, 4, wfs.build_sequence, wdj.build_sequence) build_sequence,
                                            decode(we.entity_type, 4, wfs.alternate_bom_designator, wdj.alternate_bom_designator) alternate_bom_designator,
                                            decode(we.entity_type, 4, wfs.bom_revision, wdj.bom_revision) bom_revision,
                                            decode(we.entity_type, 4, wfs.bom_revision_date, wdj.bom_revision_date) bom_revision_date,
                                            decode(we.entity_type, 4, wfs.alternate_routing_designator, wdj.alternate_routing_designator) alternate_routing_designator,
                                            decode(we.entity_type, 4, wfs.routing_revision, wdj.routing_revision) routing_revision,
                                            decode(we.entity_type, 4, wfs.routing_revision_date, wdj.routing_revision_date) routing_revision_date,
                                            nvl(ppa.name, psn.project_name) project_name,
                                            nvl(ppa.segment1, psn.project_number) project_number,
                                            decode(we.entity_type, 4, wfs.end_item_unit_number, wdj.end_item_unit_number) end_item_unit_number,
                                            pat.task_name               task_name,
                                            pat.task_number             task_number,
                                            decode(we.entity_type, 4, wfs.demand_class, wdj.demand_class) demand_class
                                        FROM
                                            mtl_material_transactions   mmt,
                                            wip_entities                we,
                                            wip_discrete_jobs           wdj,
                                            mtl_transaction_reasons     mtr,
                                            (
                                                SELECT
                                                    *
                                                FROM
                                                    wip_flow_schedules
                                                WHERE
                                                    scheduled_flag = 2
                                            ) wfs,
                                            mtl_parameters              mp,
                                            pa_projects_all             ppa,
                                            pjm_seiban_numbers          psn,
                                            pa_tasks                    pat,
                                            mtl_system_items_vl         msi,
                                            mtl_item_locations_kfv      milk ,
                                            (
                                                SELECT
                                                    haout.name ,haout.language , haout.organization_id
                                                FROM
                                                    hr_all_organization_units_tl haout
                                            ) org_name_lkp,
                                            (
                                                SELECT
                                                    meaning ,language,lookup_code
                                                FROM
                                                    fnd_lookup_values
                                                WHERE
                                                    lookup_type = 'WIP_ENTITY'
                                                    AND view_application_id = 700
                                                    AND security_group_id = 0
                                            ) entity_source_lkp,
                                           (
                                                SELECT
                                                    meaning ,lookup_code
                                                FROM
                                                    fnd_lookup_values
                                                WHERE
                                                    lookup_type = 'WIP_ENTITY'
                                                    AND view_application_id = 700
                                                    AND security_group_id = 0
                                                    AND language = 'US'
                                            ) entity_name_lkp,
                                           (
                                                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
                                                    ml1.meaning,
                                                    ml1.lookup_code,
                                                    ml1.language
                                                FROM
                                                    fnd_lookup_values ml1
                                                WHERE
                                                    ml1.lookup_type = 'WIP_COMP_ECC_TXN_TYPE'
                                                    AND ml1.view_application_id = 700
                                                    AND ml1.security_group_id = 0
                                            ) transaction_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         = 2
                                             ) 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
                                            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.organization_id = msi.organization_id
                                            AND mmt.inventory_item_id = msi.inventory_item_id
                                            AND we.entity_type IN (
                                                1,
                                                3,
                                                4
                                            )
                                            AND mp.organization_id = mmt.organization_id
                                            AND we.wip_entity_id = wdj.wip_entity_id (+)
                                            AND mmt.locator_id = milk.inventory_location_id (+)
                                            AND we.wip_entity_id = wfs.wip_entity_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 mmt.source_project_id = ppa.project_id (+)
                                            AND mmt.source_project_id = psn.project_id (+)
                                            AND mmt.source_task_id = pat.task_id (+)
                                            AND mmt.transaction_type_id = transaction_lkp.lookup_code
                                            AND entity_name_lkp.lookup_code = to_char(we.entity_type)
                                            AND entity_source_lkp.lookup_code = to_char(we.entity_type)
                                            AND entity_source_lkp.language = org_name_lkp.language
                                            AND org_name_lkp.organization_id = mp.organization_id
                                            AND status_lkp.language = org_name_lkp.language
                                            AND status_lkp.lookup_code = to_char(decode(we.entity_type,4,12,wdj.status_type))
                                          /*  AND view_item_lkp.language = org_name_lkp.language
                                            AND view_quality_lkp.language = org_name_lkp.language */
 
 
                                    AND org_name_lkp.language IN ('US')) PIVOT (
                                MAX ( organization_name )
                            AS organization_name, MAX ( entity_source ) AS entity_source ,/* MAX( item_link ) AS item_link,MAX( quality_link) AS quality_link,*/ MAX( job_status ) AS job_status FOR language  IN ('US' "US"))
) x
where
2=2