BOM Resource Where Used Report GUI
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Resource Where Used Report
Application: Bills of Material
Source: Resource Where Used Report GUI (XML)
Short Name: BOMRWURSG_XML
DB package: BOM_BOMRWURS_XMLP_PKG
Description: Resource Where Used Report
Application: Bills of Material
Source: Resource Where Used Report GUI (XML)
Short Name: BOMRWURSG_XML
DB package: BOM_BOMRWURS_XMLP_PKG
Run
BOM Resource Where Used Report GUI and other Oracle EBS reports with Blitz Report™ on our demo environment
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 |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Resource From |
|
LOV Oracle | |
To |
|
LOV Oracle | |
Effective Date |
|
DateTime |