BOM Resource Where Used Report GUI

select flex.item_number       Item,
                   lu2.meaning          Eng_Routing,
                   r.resource_code          Res2,
                   flex.Description         Des,
                   b.Process_revision       Rev,
                   rtg.alternate_routing_designator Alternate,
                   os.operation_seq_num     Op,
                   d.department_code        Department,
                   to_char(os.effectivity_date,'DD-MON-RR HH24:MI:SS')  Effective_Date,
                   to_char(os.disable_date,'DD-MON-RR HH24:MI:SS')  Disable_Date,
                   ors.resource_seq_num     seq_num,
                   round(ors.assigned_units, :P_QTY_PRECISION)  Units,
                   substrb(lu1.meaning, 1, 5)       Basis,
                   round(ors.usage_rate_or_amount, :P_QTY_PRECISION)  Usage_Rate,
            round(ors.usage_rate_or_amount_inverse, :P_QTY_PRECISION) Usage_Rate_Inverse
            from
            MTL_ITEM_FLEXFIELDS FLEX,
            BOM_RESOURCES R,
            mtl_rtg_item_revisions b,
            BOM_OPERATIONAL_ROUTINGS RTG,
            BOM_OPERATION_SEQUENCES OS,
            BOM_DEPARTMENTS D,
            BOM_OPERATION_RESOURCES ORS,
            MFG_LOOKUPS LU1,
            MFG_LOOKUPS LU2,
            CST_ACTIVITIES ACT
            where
            R.RESOURCE_ID = ORS.RESOURCE_ID AND
            ORS.OPERATION_SEQUENCE_ID = OS.OPERATION_SEQUENCE_ID AND
            OS.DEPARTMENT_ID = D.DEPARTMENT_ID AND
            OS.ROUTING_SEQUENCE_ID = RTG.COMMON_ROUTING_SEQUENCE_ID AND
            os.effectivity_date <= :P_EFFECTIVE_DATE and
            nvl(os.disable_date, :P_EFFECTIVE_DATE +1 ) > :P_EFFECTIVE_DATE and
            RTG.ASSEMBLY_ITEM_ID = FLEX.ITEM_ID AND
            RTG.ORGANIZATION_ID = FLEX.ORGANIZATION_ID AND
            ORS.ACTIVITY_ID = ACT.ACTIVITY_ID(+) AND
            (LU1.LOOKUP_TYPE = 'CST_BASIS' AND LU1.LOOKUP_CODE = ORS.BASIS_TYPE) AND
            (lu2.lookup_type='BOM_NO_YES' and lu2.lookup_code = rtg.routing_type) and
            r.organization_id = :P_ORG_ID and
            ((:P_ENG_BILL_FLAG='BOM' and rtg.routing_type=1)
              OR
              (:P_ENG_BILL_FLAG='ENG')) and
            b.inventory_item_id = nvl(rtg.common_assembly_item_id, rtg.assembly_item_id) and
            b.process_revision = (select max(b2.process_revision)
                    from mtl_rtg_item_revisions b2
                    where b2.inventory_item_id = nvl(rtg.common_assembly_item_id, rtg.assembly_item_id) and
                    b2.organization_id = :P_ORG_ID and
                    b2.effectivity_date = (select max(b1.effectivity_date)
                        from mtl_rtg_item_revisions b1
                        where b1.inventory_item_id = nvl(rtg.common_assembly_item_id, rtg.assembly_item_id) and
                        b1.organization_id = :P_ORG_ID and
                        b1.effectivity_date <= :P_EFFECTIVE_DATE)
                    ) and
            b.organization_id = r.organization_id
             and r.resource_code=:Res
            order by flex.padded_item_number, os.operation_seq_num, ors.resource_seq_num ,rtg.alternate_routing_designator