ECC Discrete Completions, Resource Transactions

select
x.*
from
(
SELECT * FROM (SELECT /*+ leading(wt we) index(wt WIP_TRANSACTIONS_N2) cardinality(wt 10)  use_nl(we) */
                                         to_char (wt.transaction_id ) ecc_spec_id,
                                            wt.transaction_id,
                                        entity_source_lkp.meaning entity_source,
                                        entity_source_lkp.language language,
                                            we.entity_type,
                                            we.wip_entity_id,
                                            we.wip_entity_name,
											we.organization_id,
                                            WIP_ECC_WC_PVT.get_concatenated_segments(we.organization_id, we.primary_item_id) assembly,
                                            WIP_ECC_WC_PVT.get_description(we.organization_id, we.primary_item_id) assemblydesc,
                                            br.resource_code,
                                            br.description res_desc,
                                            br.unit_of_measure,
                                            resource_type_lkp.meaning resource_type,
                                          resource_basis_lkp.meaning resource_basis,
                                          charge_type_lkp.meaning charge_type,
                                            (
                                                SELECT
                                                    activity
                                                FROM
                                                    cst_activities ca
                                                WHERE
                                                    wt.activity_id = ca.activity_id
                                            ) activity,
                                            round(wt.usage_rate_or_amount, 6) usage_rate_or_amount,
                                            round(wt.transaction_quantity, 6) transaction_quantity,
                                            wt.transaction_uom,
                                            round(wt.primary_quantity, 6) primary_quantity,
                                            wt.primary_uom,
                                            wt.actual_resource_rate,
                                            wt.standard_resource_rate,
                                            wt.transaction_date
                                        FROM
                                            wip_transactions          wt,
                                            wip_entities              we,
                                            mtl_transaction_reasons   mtr,
                                            bom_resources             br ,
                                                (
                                                SELECT
                                                    meaning , language , lookup_code
                                                FROM
                                                    fnd_lookup_values
                                                WHERE
                                                    lookup_type = 'WIP_ENTITY'
                                            ) entity_source_lkp ,
                                            (
                                                SELECT
                                                    meaning , language , lookup_code
                                                FROM
                                                    fnd_lookup_values
                                                WHERE
                                                    lookup_type = 'BOM_RESOURCE_TYPE'
                                            ) resource_type_lkp,
                                            (
                                                SELECT
                                                    meaning , language , lookup_code
                                                FROM
                                                    fnd_lookup_values
                                                WHERE
                                                    lookup_type = 'CST_BASIS'
                                            ) resource_basis_lkp,
                                            (
                                                SELECT
                                                    meaning , language ,lookup_code
                                                FROM
                                                    fnd_lookup_values
                                                WHERE
                                                    lookup_type = 'BOM_AUTOCHARGE_TYPE'
                                            ) charge_type_lkp
                                        WHERE
                                            wt.wip_entity_id = we.wip_entity_id
                                            AND we.entity_type IN (
                                                1,
                                                3,
                                                4
                                            )
                                            AND wt.transaction_type = 1
                                            AND wt.transaction_date > TO_DATE('12-NOV-22 10:13:51','DD-MON-RR HH24:MI:SS')
                                            AND wt.transaction_date <= TO_DATE('12-DEC-22 10:13:51','DD-MON-RR HH24:MI:SS')
                                            AND wt.reason_id = mtr.reason_id (+)
                                            AND wt.resource_id = br.resource_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 = we.organization_id
                                                    AND mmt.transaction_source_id = we.wip_entity_id
                                                    AND ROWNUM = 1
                                            )
                                            AND  entity_source_lkp.lookup_code = to_char(we.entity_type)
                                            AND charge_type_lkp.lookup_code = to_char(nvl(wt.autocharge_type, br.autocharge_type))
                                            AND resource_basis_lkp.lookup_code = to_char(nvl(wt.basis_type, 1))
                                            AND resource_type_lkp.lookup_code = to_char(br.resource_type)
                                            AND charge_type_lkp.language = resource_basis_lkp.language
                                            AND resource_basis_lkp.language = resource_type_lkp.language
                                            AND charge_type_lkp.language = entity_source_lkp.language
                                            AND entity_source_lkp.language in ('US')
 
                                ) PIVOT (
                                MAX ( entity_source ) AS entity_source , MAX( resource_type ) AS resource_type, MAX( resource_basis ) AS resource_basis , MAX( charge_type ) AS charge_type  FOR language  IN ('US' "US"))
) x
where
2=2