WIP Resource Performance
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Resource Performance Report
Application: Work in Process
Source: Resource Performance Report (XML)
Short Name: WIPLBPER_XML
DB package: WIP_WIPLBPER_XMLP_PKG
Description: Resource Performance Report
Application: Work in Process
Source: Resource Performance Report (XML)
Short Name: WIPLBPER_XML
DB package: WIP_WIPLBPER_XMLP_PKG
Run
WIP Resource Performance and other Oracle EBS reports with Blitz Report™ on our demo environment
SELECT BD.DEPARTMENT_CODE Department , BR.RESOURCE_CODE Resource_code , NVL(BR.UNIT_OF_MEASURE,' ') UOM , WE.WIP_ENTITY_NAME Job , WL.LINE_CODE Line , WOP.OPERATION_SEQ_NUM Op_Seq , WOR.RESOURCE_SEQ_NUM Res_Seq , WOP.FIRST_UNIT_START_DATE First_Unit , WOP.LAST_UNIT_COMPLETION_DATE Last_Unit , round(WOP.QUANTITY_COMPLETED,:P_qty_precision) Assy_Units , WOP.QUANTITY_COMPLETED Assy_Units1 , MSI.PRIMARY_UOM_CODE Assy , round(WOR.USAGE_RATE_OR_AMOUNT,:P_qty_precision) Usage_Rate , WOR.USAGE_RATE_OR_AMOUNT Usage_Rate1 , WOR.BASIS_TYPE Basis_Type , ML.MEANING Basis , round(WOR.APPLIED_RESOURCE_UNITS,:P_qty_precision) Applied, --WIP_WIPLBPER_XMLP_PKG.c_eff_total_mainformula(:C_Applied_Total, :C_Std_Total) C_Eff_Total_Main, --WIP_WIPLBPER_XMLP_PKG.c_eff_flagformula(:C_Eff_Total_Main) C_Eff_Flag, WIP_WIPLBPER_XMLP_PKG.c_std_unitsformula(WOR.BASIS_TYPE, WOR.USAGE_RATE_OR_AMOUNT, WOP.QUANTITY_COMPLETED, round ( WOR.USAGE_RATE_OR_AMOUNT , :P_qty_precision )) C_Std_Units, WIP_WIPLBPER_XMLP_PKG.c_efficiencyformula(round ( WOR.APPLIED_RESOURCE_UNITS , :P_qty_precision ), WIP_WIPLBPER_XMLP_PKG.c_std_unitsformula(WOR.BASIS_TYPE, WOR.USAGE_RATE_OR_AMOUNT, WOP.QUANTITY_COMPLETED, round ( WOR.USAGE_RATE_OR_AMOUNT , :P_qty_precision ))) C_Efficiency FROM MFG_LOOKUPS ML , WIP_LINES WL , MTL_SYSTEM_ITEMS MSI , WIP_REPETITIVE_SCHEDULES WRS , WIP_ENTITIES WE , WIP_OPERATION_RESOURCES WOR , WIP_OPERATIONS WOP , BOM_RESOURCES BR , BOM_DEPARTMENT_RESOURCES BDR , BOM_DEPARTMENTS BD WHERE BD.ORGANIZATION_ID = :P_Organization_Id &C_Limit_Dept AND BDR.DEPARTMENT_ID = BD.DEPARTMENT_ID AND BR.RESOURCE_ID = BDR.RESOURCE_ID &C_Limit_Resources AND BD.DEPARTMENT_ID = NVL( WOR.DEPARTMENT_ID, WOP.DEPARTMENT_ID) AND WOP.ORGANIZATION_ID = :P_Organization_Id AND WOR.WIP_ENTITY_ID = WOP.WIP_ENTITY_ID AND WOR.ORGANIZATION_ID = :P_Organization_Id AND WOR.OPERATION_SEQ_NUM = WOP.OPERATION_SEQ_NUM AND NVL(WOR.REPETITIVE_SCHEDULE_ID,-1) = NVL(WOP.REPETITIVE_SCHEDULE_ID,-1) AND WOR.RESOURCE_ID + 0 = BDR.RESOURCE_ID AND (((round(WOP.QUANTITY_COMPLETED,:P_qty_precision) <> 0) AND (round(WOR.USAGE_RATE_OR_AMOUNT,:P_qty_precision) <> 0)) OR (round(WOR.APPLIED_RESOURCE_UNITS,:P_qty_precision) <> 0)) AND WE.WIP_ENTITY_ID = WOP.WIP_ENTITY_ID &C_Limit_Dates AND MSI.ORGANIZATION_ID = :P_Organization_Id AND MSI.INVENTORY_ITEM_ID = WE.PRIMARY_ITEM_ID AND WRS.ORGANIZATION_ID(+) = :P_Organization_Id AND WRS.REPETITIVE_SCHEDULE_ID(+) = NVL(WOP.REPETITIVE_SCHEDULE_ID,-1) AND WL.LINE_ID(+) = WRS.LINE_ID AND WL.ORGANIZATION_ID(+) = :P_Organization_Id AND ML.LOOKUP_TYPE = 'CST_BASIS_SHORT' AND ML.LOOKUP_CODE = WOR.BASIS_TYPE UNION ALL SELECT BD.DEPARTMENT_CODE Department , BR.RESOURCE_CODE Resource_code , NVL(BR.UNIT_OF_MEASURE,' ') UOM , WF.SCHEDULE_NUMBER Job , WL.LINE_CODE Line , BOS.OPERATION_SEQ_NUM Op_Seq , BOR.RESOURCE_SEQ_NUM Res_Seq , WF.SCHEDULED_START_DATE First_Unit , WF.SCHEDULED_COMPLETION_DATE Last_Unit , round(WF.QUANTITY_COMPLETED,:P_Qty_Precision) Assy_Units , WF.QUANTITY_COMPLETED Assy_Units1 , MSI.PRIMARY_UOM_CODE Assy , round(BOR.USAGE_RATE_OR_AMOUNT,:P_Qty_Precision) Usage_Rate , BOR.USAGE_RATE_OR_AMOUNT Usage_Rate1 , BOR.BASIS_TYPE Basis_Type , ML.MEANING Basis , to_number(NULL) Applied, --WIP_WIPLBPER_XMLP_PKG.c_eff_total_mainformula(:C_Applied_Total, :C_Std_Total) C_Eff_Total_Main, --WIP_WIPLBPER_XMLP_PKG.c_eff_flagformula(:C_Eff_Total_Main) C_Eff_Flag, WIP_WIPLBPER_XMLP_PKG.c_std_unitsformula(BOR.BASIS_TYPE, BOR.USAGE_RATE_OR_AMOUNT, WF.QUANTITY_COMPLETED, round(WF.QUANTITY_COMPLETED,:P_Qty_Precision)) C_Std_Units, WIP_WIPLBPER_XMLP_PKG.c_efficiencyformula(round ( to_number(NULL), :P_qty_precision ),WIP_WIPLBPER_XMLP_PKG.c_std_unitsformula(BOR.BASIS_TYPE, BOR.USAGE_RATE_OR_AMOUNT, WF.QUANTITY_COMPLETED, round(WF.QUANTITY_COMPLETED,:P_Qty_Precision))) C_Efficiency FROM MFG_LOOKUPS ML , WIP_LINES WL , MTL_SYSTEM_ITEMS MSI , WIP_FLOW_SCHEDULES WF , BOM_OPERATIONAL_ROUTINGS BROUT , BOM_OPERATION_RESOURCES BOR , BOM_OPERATION_SEQUENCES BOS , BOM_RESOURCES BR , BOM_DEPARTMENT_RESOURCES BDR , BOM_DEPARTMENTS BD WHERE BROUT.ORGANIZATION_ID = :P_Organization_Id AND WF.ORGANIZATION_ID = BROUT.ORGANIZATION_ID AND WF.PRIMARY_ITEM_ID = BROUT.ASSEMBLY_ITEM_ID AND nvl(WF.ALTERNATE_ROUTING_DESIGNATOR, chr(12)) = nvl(BROUT.ALTERNATE_ROUTING_DESIGNATOR, chr(12)) AND BROUT.COMMON_ROUTING_SEQUENCE_ID = BOS.ROUTING_SEQUENCE_ID AND BOR.OPERATION_SEQUENCE_ID = BOS.OPERATION_SEQUENCE_ID AND BOR.RESOURCE_ID = BDR.RESOURCE_ID AND BDR.DEPARTMENT_ID = BD.DEPARTMENT_ID AND BDR.RESOURCE_ID = BR.RESOURCE_ID AND (round(BOR.USAGE_RATE_OR_AMOUNT,:P_Qty_Precision) <> 0) AND MSI.ORGANIZATION_ID = :P_Organization_Id AND MSI.INVENTORY_ITEM_ID = WF.PRIMARY_ITEM_ID AND ML.LOOKUP_TYPE = 'CST_BASIS_SHORT' AND ML.LOOKUP_CODE = BOR.BASIS_TYPE AND WL.LINE_ID(+) = WF.LINE_ID AND WL.ORGANIZATION_ID(+) = :P_Organization_Id &C_Limit_Dept &C_Limit_Resources ORDER BY Department , Resource_Code , UOM , Job , Line , First_Unit , Last_Unit , Op_Seq , Res_Seq |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Resources From |
|
LOV Oracle | |
To |
|
LOV Oracle | |
Departments From |
|
LOV Oracle | |
To 2 |
|
LOV Oracle | |
Job/Schedule Active Dates From |
|
Date | |
To 3 |
|
Date | |
Minimum Efficiency |
|
Number | |
Maximum Efficiency |
|
Number |