WIP Resource Performance

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