ECC Discrete Manufacturing, Outside Processing

Description
Categories: Enterprise Command Center
Columns: Ecc Spec Id, Ecc Op Spec Id, Ecc Rsc Spec Id, Job, Job Name, Job Scheduled Start Date, Job Scheduled Completion Date, Job Actual Completion Date, Assembly, Assembly Description ...
Imported from Enterprise Command Center
Description: This data set holds information about the outside processing operations
Dataset Key: wip-ospdetails2
Query Procedure: WIP_ECC_UTIL_PVT.GET_ECC_DATA_LOAD_INFO
Security Procedure: wip_ecc_datasecurity_pkg_pub.GetFilterAttributeValues
select
x.*
from
(
 SELECT * FROM (   WITH lookup_details as (SELECT /*+ materialize */ meaning,
                                            lookup_code,
                                            language,
                                            lookup_type,
                                            security_group_id,
                                            view_application_id
                                          FROM fnd_lookup_values
                                          WHERE lookup_type      IN ('WIP_JOB_STATUS','WIP_OPERATION_STATUS','WIP_PO_NOT_CREATED','SYS_YES_NO','BOM_RESOURCE_SCHEDULE_TYPE','BOM_RESOURCE_TYPE','CST_BASIS','BOM_AUTOCHARGE_TYPE')
                                          AND view_application_id = 700
                                          AND security_group_id   = 0
                                          )  SELECT  Ecc_Spec_Id,
                                         ecc_op_spec_id,
                                         Ecc_rsc_Spec_Id,
                                        job,
                                         job_name ,
										 job_scheduled_start_date,
									     job_scheduled_completion_date,
									     job_actual_completion_date,
										 job_status,
										assembly ,
										assembly_description,
                                         organization_id ,
										 organization_code ,
                                         organization_name ,
										 operation_code,
										 projected_available_quantity,
										 Standard_rate,
                                         shortage_quantity,
										 replacement_group_num,
										 substitute_group_num,
										 activity,
                                         operation_sequence_number,
                                         department_description ,
                                         planned_start_date ,
                                         Planned_completion_date ,
                                         actual_start_date ,
                                         actual_completion_date ,
                                         quantity_in_queue ,
                                         quantity_in_run ,
                                         Quantity_in_to_move ,
                                         Scrap_quantity ,
                                         Rejected_Quantity ,
                                         Completed_quantity ,
                                         Operation_Description ,
                                         Operation_progress ,
                                         Department,
                                         resource_code ,
                                         resource_description ,
                                         resource_usage_rate ,
                                         resource_uom ,
                                         resource_required_units ,
                                         resource_applied_units ,
                                         clock_in_employee,
                                         language,
                                         resource_type ,
                                         resource_basis_type,
                                         resource_open_qty ,
                                         resource_assigned_units ,
                                         resource_start_date ,
                                         resource_completion_date ,
                                         resource_alternate ,
                                         resource_sequence_number ,
                                         scheduled_sequence_number ,
                                         Rsr_actual_start_date ,
                                         Rsr_actual_completion_date ,
                                         auto_charge_flag ,
                                         phantom_flag ,
                                         operation_resource_batch ,
                                         scheduled_flag ,
                                         charge_type ,
                                         utilization ,
                                         efficiency ,
                                         supplier ,
										 supplier_tbl ,
                                         purchase_order_status ,
                                         purchase_order_number ,
                                         requisition_number ,
                                         line_number ,
                                         cancel_flag ,
                                         need_by_date ,
                                         promise_date ,
										 delayed_receipts,
                                         pending_quantity ,
										quantity_ordered,
								        quantity_delivered,
										quantity_cancelled,
                                         po_line_uom ,
                                         line_item,
                                         osp_operation,
                                        starting_today,
                                         Completing_Today,
                                         starting_tomorrow,
                                          Completing_Tomorrow,
                                         starting_by,
                                         finishing_by,
										 CASE WHEN job_status_type IN (3,6) THEN operation_start_delay ELSE 0 END operation_start_delay,
										 CASE WHEN job_status_type IN (3,6) THEN operation_completion_delay ELSE 0 END operation_completion_delay,
                                         CASE WHEN job_status_type IN (3,6) THEN operation_delay_duration ELSE 0 END operation_delay_duration,
                                         CASE WHEN job_status_type IN (3,6) THEN operation_delayed ELSE 0 END operation_delayed,
										 operation_status,
                                         op_status_flag,
										 CASE WHEN op_status_flag =1 AND job_status_type = 3
									     THEN 1 ELSE 0 END upcoming,
										CASE WHEN op_status_flag =1  AND job_status_type = 3 AND operation_start_delay > 0    THEN 1   ELSE 0  END Start_delay_flag,
										CASE WHEN op_status_flag =1  AND job_status_type = 3 AND operation_start_delay > 0    THEN 'YES'   ELSE 'NO'   END Start_delay,
										CASE WHEN (op_status_flag IN (2,3))  AND job_status_type = 3 AND operation_completion_delay > 0    THEN 1   ELSE 0  END completion_delay_flag,
										CASE WHEN (op_status_flag IN (2,3))  AND job_status_type = 3 AND operation_completion_delay > 0    THEN 1   ELSE 0   END Completion_delay,
 
										 CASE WHEN op_status_flag = 2 THEN 'YES' ELSE 'NO' END Open,
										 CASE WHEN op_status_flag =1 AND job_status_type = 3
									     THEN 'YES' ELSE 'NO' END upcoming_code,
										CASE WHEN delayed_receipts =1 THEN 'YES' ELSE 'NO' END po_receipt_delay,
										CASE WHEN job_status_type IN (3,6) AND operation_delay_duration > 0
                                      THEN 0
									  WHEN job_status_type NOT IN (3,6)
                                        THEN 0
                                        ELSE 1
                                      END ontrack_flag,
                                      CASE WHEN job_status_type IN (3,6) AND operation_delay_duration > 0
									  THEN no_flag
									  WHEN job_status_type NOT IN (3,6)
									    THEN no_flag
                                        ELSE yes_flag
                                      END ontrack
										FROM (
										SELECT  /*+ leading(we wdj mp haout) use_nl(we wip_osp wdj mp haout wo wor bd bso) */ TO_CHAR (wdj.wip_entity_id
                                        ||'-'
                                        ||wo.operation_seq_num
                                        ||'-'
                                        ||br.resource_code
										||'-'
										||wor.replacement_group_num
                                        ||'-'
                                        ||wdj.organization_id
                                        ||'-'
                                        ||wip_osp.rec_key
                                        ||'-OSP') Ecc_Spec_Id,
                                        TO_CHAR (wdj.wip_entity_id
                                        ||'-'
                                        ||wo.operation_seq_num
                                        ||'-'
                                        ||wdj.organization_id
                                        ||'-OP') ecc_op_spec_id,
                                        (wdj.wip_entity_id
                                        ||'-'
                                        ||wo.operation_seq_num
                                        ||'-'
                                        ||br.resource_code
                                        ||'-'
                                        ||wdj.organization_id
                                        ||'-RSC') Ecc_rsc_Spec_Id,
                                        wo.wip_entity_id job,
                                        we.wip_entity_name job_name ,
										status_lkp.meaning job_status,
										wor.replacement_group_num,
										wdj.scheduled_start_date job_scheduled_start_date ,
                                        wdj.scheduled_completion_date job_scheduled_completion_date ,
										wdj.date_completed job_actual_completion_date ,
										WIP_ECC_UTIL_PVT.get_concatenated_segments(wdj.organization_id,wdj.primary_item_id) assembly,
                                         WIP_ECC_UTIL_PVT.get_description(wdj.organization_id,wdj.primary_item_id)  assembly_description,
										 wdj.organization_id organization_id ,
										wdj.status_type job_status_type,
										mp.organization_code organization_code ,
										yeslk.meaning yes_flag,
										nolk.meaning no_flag,
                                        haout.name organization_name ,
										bso.operation_code,
										0 projected_available_quantity,
										to_char(decode(wor.standard_rate_flag,1,yeslk.meaning,nolk.meaning)) Standard_rate,
                                        0 shortage_quantity,
										wor.substitute_group_num substitute_group_num,
										ca.activity activity,
                                        wo.operation_seq_num operation_sequence_number,
                                        bd.description department_description ,
                                        wo.first_unit_start_date planned_start_date ,
                                        wo.last_unit_completion_date Planned_completion_date ,
                                        wo.actual_start_date actual_start_date ,
                                        wo.actual_completion_date actual_completion_date ,
                                        NVL(wo.quantity_in_queue,0) quantity_in_queue ,
                                        NVL(wo.quantity_running,0) quantity_in_run ,
                                        NVL(wo.quantity_waiting_to_move,0) Quantity_in_to_move ,
                                        to_number(NVL(wo.quantity_scrapped,0)) Scrap_quantity ,
                                        to_number(NVL(wo.quantity_rejected,0)) Rejected_Quantity ,
                                        NVL(wo.quantity_completed,0) Completed_quantity ,
                                        wo.description Operation_Description ,
                                        wo.progress_percentage Operation_progress ,
                                        bd.department_code Department,
                                        br.resource_code resource_code ,
                                        br.description resource_description ,
                                        wor.usage_rate_or_amount resource_usage_rate ,
                                        wor.uom_code resource_uom ,
                                        ROUND(wor.usage_rate_or_amount*DECODE(wor.basis_type, 1,wdj.start_quantity-NVL(wo.cumulative_scrap_quantity,0),1),6) resource_required_units ,
                                        wor.applied_resource_units resource_applied_units ,
                                        (SELECT pap.full_name
                                        FROM per_all_people_f pap
                                        WHERE pap.person_id = wo.employee_id
                                        AND ROWNUM          = 1
                                        ) clock_in_employee,
                                        restype.language language,
                                        restype.meaning resource_type ,
                                        resbasis.meaning resource_basis_type,
                                        ROUND((wor.usage_rate_or_amount *DECODE(wor.basis_type,1, wdj.start_quantity-NVL(wo.cumulative_scrap_quantity,0),1))-wor.applied_resource_units,6) resource_open_qty ,
                                        ROUND(wor.assigned_units,6) resource_assigned_units ,
                                        wor.start_date resource_start_date ,
                                        wor.completion_date resource_completion_date ,
                                        null resource_alternate ,
                                        wor.resource_seq_num resource_sequence_number ,
                                        wor.schedule_seq_num scheduled_sequence_number ,
                                        wor.actual_start_date Rsr_actual_start_date ,
                                        wor.actual_completion_date Rsr_actual_completion_date ,
                                        TO_CHAR(DECODE(wor.autocharge_type,1,yeslk.meaning,nolk.meaning)) auto_charge_flag ,
                                        TO_CHAR(DECODE(wor.phantom_flag,1,yeslk.meaning,nolk.meaning)) phantom_flag ,
                                        wor.batch_id operation_resource_batch ,
                                        ressch.meaning scheduled_flag ,
                                        reschg.meaning charge_type ,
                                        NULL utilization ,
                                        NULL efficiency ,
                                        NVL(wip_osp.vendor_name,nonelk.meaning) supplier ,
										wip_osp.vendor_name supplier_tbl ,
                                        wip_osp.approval_status_code purchase_order_status ,
                                        wip_osp.po_number purchase_order_number ,
                                        wip_osp.req_number requisition_number ,
                                        wip_osp.line_number line_number ,
                                        DECODE(wip_osp.cancel_flag,'Y',1,2) cancel_flag ,
                                        wip_osp.old_need_by_date need_by_date ,
                                        wip_osp.old_promised_date promise_date ,
										CASE WHEN wip_osp.po_number IS NOT NULL THEN 
                                                                                           CASE WHEN (NVL(wip_osp.old_promised_date,wip_osp.old_need_by_date) - SYSDATE)  < 0
										            AND (NVL(wip_osp.old_promised_date,wip_osp.old_need_by_date) - SYSDATE)  < 0
													AND ((wip_osp.CANCEL_FLAG_PL IS NULL  OR wip_osp.CANCEL_FLAG_PL = 'N')
													AND (wip_osp.QUANTITY_RECEIVED  <(wip_osp.QUANTITY-wip_osp.QUANTITY_CANCELLED_PL))
													AND NVL(wip_osp.CLOSED_CODE,'OPEN')  <> 'FINALLY CLOSED')
										           THEN 1 ELSE 0 END ELSE 0 END delayed_receipts,
                                        wip_osp.assembly_qty_pending pending_quantity ,
										wip_osp.quantity_ordered,
								        wip_osp.quantity_delivered,
										wip_osp.quantity_cancelled,
                                        wip_osp.assembly_primary_uom po_line_uom ,
                                        wip_osp.osp_res_po_line_item line_item,
                                        CASE
                                        WHEN wip_osp.po_number IS NULL
                                        AND wip_osp.req_number IS NULL
                                        THEN nolk.meaning
                                        ELSE yeslk.meaning
                                        END osp_operation,
										CASE
                                        WHEN wip_osp.po_number IS NULL
                                        AND wip_osp.req_number IS NULL
                                        THEN nolk.lookup_code
                                        ELSE yeslk.lookup_code
                                        END osp_operation_flag,
                                        CASE WHEN TRUNC(wo.first_unit_start_date) = TRUNC(SYSDATE) THEN 1 ELSE 0 END starting_today,
                                        CASE WHEN TRUNC(wo.last_unit_completion_date) = TRUNC(SYSDATE) THEN 1 ELSE 0 END  Completing_Today,
                                        CASE WHEN TRUNC(wo.first_unit_start_date) = TRUNC(SYSDATE+1) THEN 1 ELSE 0 END starting_tomorrow,
                                        CASE WHEN TRUNC(wo.last_unit_completion_date) = TRUNC(SYSDATE+1) THEN 1 ELSE 0 END  Completing_Tomorrow,
                                        (wo.first_unit_start_date      -SYSDATE) starting_by,
                                        (wo.last_unit_completion_date - SYSDATE ) finishing_by,
										CASE
                                        WHEN wo.actual_start_date IS NOT NULL
                                        AND wo.actual_start_date   > wo.first_unit_start_date
                                        THEN CASE WHEN (wo.actual_start_date-wo.first_unit_start_date) > 0
										          THEN (wo.actual_start_date-wo.first_unit_start_date) ELSE 0 END
                                        WHEN wo.actual_start_date       IS NULL
                                        AND (wo.first_unit_start_date)   < sysdate
                                        AND NVL(wo.quantity_in_queue,0)  = 0
                                        AND NVL(wo.quantity_running,0)   = 0
                                        AND NVL(wo.quantity_completed,0) = 0
                                        THEN CASE WHEN (sysdate-wo.first_unit_start_date) > 0
										          THEN (sysdate-wo.first_unit_start_date) ELSE 0 END
                                        ELSE 0
                                        END operation_start_delay,
										CASE
                                        WHEN wo.actual_completion_date IS NOT NULL
                                        AND wo.actual_completion_date   > (wo.last_unit_completion_date)
                                        THEN CASE WHEN (wo.actual_completion_date -wo.last_unit_completion_date) > 0
										          THEN (wo.actual_completion_date -wo.last_unit_completion_date) ELSE 0 END
                                        WHEN wo.actual_completion_date    IS NULL 
                                        AND (wo.last_unit_completion_date) < sysdate
                                        AND (wo.quantity_in_queue          > 0
                                        OR wo.quantity_running             > 0)
                                        THEN CASE WHEN (sysdate-wo.last_unit_completion_date) > 0
										          THEN (sysdate-wo.last_unit_completion_date) ELSE 0 END
                                        ELSE 0
                                        END operation_completion_delay,
                                        CASE
                                          WHEN wo.actual_completion_date IS NOT NULL
                                          AND wo.actual_completion_date   > (wo.last_unit_completion_date )
                                          THEN CASE WHEN (wo.actual_completion_date - (wo.last_unit_completion_date )) > 0
										            THEN (wo.actual_completion_date - (wo.last_unit_completion_date ))  ELSE 0 END
                                          WHEN wo.actual_completion_date    IS NULL
                                          AND (wo.last_unit_completion_date ) < sysdate
                                          AND (wo.quantity_in_queue          > 0
                                          OR wo.quantity_running             > 0)
                                          THEN CASE WHEN (sysdate-(wo.last_unit_completion_date )) > 0
										            THEN (sysdate-(wo.last_unit_completion_date )) ELSE 0 END
                                          WHEN wo.actual_start_date IS NOT NULL
                                          AND wo.actual_start_date   > (wo.first_unit_start_date )
                                          THEN CASE WHEN (wo.actual_start_date-(wo.first_unit_start_date )) > 0
										            THEN (wo.actual_start_date-(wo.first_unit_start_date )) ELSE 0 END
                                          WHEN wo.actual_start_date       IS NULL
                                          AND (wo.first_unit_start_date )   < sysdate
                                          AND NVL(wo.quantity_in_queue,0)  = 0
                                          AND NVL(wo.quantity_running,0)   = 0
                                          AND NVL(wo.quantity_completed,0) = 0
                                          THEN CASE WHEN (sysdate-(wo.first_unit_start_date )) > 0
										       THEN (sysdate-(wo.first_unit_start_date )) ELSE 0 END
                                          ELSE 0
                                        END operation_delay_duration,
                                        CASE
                                          WHEN wo.actual_completion_date IS NOT NULL
                                          AND wo.actual_completion_date   > (wo.last_unit_completion_date )
                                          THEN CASE WHEN (wo.actual_completion_date - (wo.last_unit_completion_date )) > 0
										            THEN 1 ELSE 0 END
                                          WHEN wo.actual_completion_date    IS NULL
                                          AND (wo.last_unit_completion_date ) < sysdate
                                          AND (wo.quantity_in_queue          > 0
                                          OR wo.quantity_running             > 0)
                                          THEN CASE WHEN (sysdate-(wo.last_unit_completion_date )) > 0
										            THEN 1 ELSE 0 END
                                          WHEN wo.actual_start_date IS NOT NULL
                                          AND wo.actual_start_date   > (wo.first_unit_start_date )
                                          THEN CASE WHEN (wo.actual_start_date-(wo.first_unit_start_date )) > 0
										            THEN 1 ELSE 0 END
                                          WHEN wo.actual_start_date       IS NULL
                                          AND (wo.first_unit_start_date )   < sysdate
                                          AND NVL(wo.quantity_in_queue,0)  = 0
                                          AND NVL(wo.quantity_running,0)   = 0
                                          AND NVL(wo.quantity_completed,0) = 0
                                          THEN CASE WHEN (sysdate-(wo.first_unit_start_date )) > 0
										       THEN 1 ELSE 0 END
                                          ELSE 0
                                        END operation_delayed,
										CASE
                                        WHEN wo.quantity_completed >= (wdj.start_quantity-wo.cumulative_scrap_quantity) OR wdj.status_type in (4,5,7,12)
                                        THEN operation_com_lkp.meaning
                                        WHEN ((Wo.quantity_in_queue + wo.quantity_running > 0) OR (wo.date_last_moved is not null)  OR ( wo.quantity_completed > 0 ) ) AND wdj.status_type in (3,6)
                                        THEN operation_run_lkp.meaning
                                        ELSE operation_ns_lkp.meaning
                                        END operation_status,
                                        CASE
                                        WHEN wo.quantity_completed >= (wdj.start_quantity-wo.cumulative_scrap_quantity) OR wdj.status_type in (4,5,7,12)
                                        THEN operation_com_lkp.lookup_code
                                        WHEN ( ( wo.quantity_in_queue + wo.quantity_running > 0 ) OR ( wo.date_last_moved IS NOT NULL ) OR ( wo.quantity_completed > 0 ) ) AND wdj.status_type IN (3,6)
                                        THEN operation_run_lkp.lookup_code
                                        ELSE operation_ns_lkp.lookup_code
                                        END op_status_flag
                                        FROM wip_entities we,
                                        wip_discrete_jobs wdj,
                                        bom_resources br,
                                        wip_operations wo,
                                        wip_operation_resources wor,
										cst_activities ca,
										mtl_parameters mp,
                                        hr_all_organization_units_tl haout,
										(
                                        SELECT
 
                                          ml1.meaning,
                                          ml1.lookup_code,
                                          ml1.language
                                        FROM lookup_details ml1
                                        WHERE ml1.lookup_type       = 'WIP_JOB_STATUS'
                                        AND ml1.view_application_id = 700
                                        AND ml1.security_group_id   = 0
                                        ) status_lkp,
                                        (SELECT
 
                                        restype.meaning,
                                        restype.language,
                                        restype.lookup_code
                                        FROM lookup_details restype
                                        WHERE restype.lookup_type       = 'BOM_RESOURCE_TYPE'
                                        AND restype.view_application_id = 700
                                        AND restype.security_group_id   = 0
                                        )restype,
                                        (SELECT
 
                                        resbasis.meaning,
                                        resbasis.language,
                                        resbasis.lookup_code
                                        FROM lookup_details resbasis
                                        WHERE resbasis.lookup_type       = 'CST_BASIS'
                                        AND resbasis.view_application_id = 700
                                        AND resbasis.security_group_id   = 0
                                        )resbasis,
                                        (SELECT
 
                                        reschg.meaning,
                                        reschg.language ,
                                        reschg.lookup_code
                                        FROM lookup_details reschg
                                        WHERE reschg.lookup_type       = 'BOM_AUTOCHARGE_TYPE'
                                        AND reschg.view_application_id = 700
                                        AND reschg.security_group_id   = 0
                                        )reschg,
                                        (SELECT
 
                                        ressch.meaning,
                                        ressch.language,
                                        ressch.lookup_code
                                        FROM lookup_details ressch
                                        WHERE ressch.lookup_type       = 'BOM_RESOURCE_SCHEDULE_TYPE'
                                        AND ressch.view_application_id = 700
                                        AND ressch.security_group_id   = 0
                                        )ressch,
										(SELECT
 
                                        f.meaning,
                                        f.lookup_code,
                                        f.language
                                        FROM lookup_details f
                                        WHERE f.lookup_type       = 'WIP_OPERATION_STATUS'
                                        AND f.view_application_id = 700
                                        AND f.security_group_id   = 0
                                        AND f.lookup_code         = 1
                                        ) operation_ns_lkp,
                                        (SELECT
 
                                        f.meaning,
                                        f.lookup_code,
                                        f.language
                                        FROM lookup_details f
                                        WHERE f.lookup_type       = 'WIP_OPERATION_STATUS'
                                        AND f.view_application_id = 700
                                        AND f.security_group_id   = 0
                                        AND f.lookup_code         = 2
                                        ) operation_run_lkp,
                                        (SELECT
 
                                        f.meaning,
                                        f.lookup_code,
                                        f.language
                                        FROM lookup_details f
                                        WHERE f.lookup_type       = 'WIP_OPERATION_STATUS'
                                        AND f.view_application_id = 700
                                        AND f.security_group_id   = 0
                                        AND f.lookup_code         = 3
                                        ) operation_com_lkp,
                                        (SELECT wip_osp1.rec_key,
										wip_osp1.approval_status_code,
                                        wip_osp1.po_number,
                                        wip_osp1.req_number,
                                        wip_osp1.cancel_flag,
                                        wip_osp1.old_need_by_date,
                                        wip_osp1.old_promised_date,
										wip_osp1.CANCEL_FLAG_PL,
                                        wip_osp1.QUANTITY_RECEIVED,
                                        wip_osp1.QUANTITY,
                                        wip_osp1.QUANTITY_CANCELLED_PL,
                                        wip_osp1.CLOSED_CODE,
                                        wip_osp1.assembly_qty_pending,
										wip_osp1.quantity_ordered,
								        wip_osp1.quantity_delivered,
										wip_osp1.quantity_cancelled,
                                        wip_osp1.assembly_primary_uom,
                                        wip_osp1.organization_id,
                                        wip_osp1.wip_entity_id,
                                        wip_osp1.operation_seq_num,
                                        wip_osp1.vendor_name,
                                        wip_osp1.po_item_number osp_res_po_line_item,
                                        wip_osp1.line_number,
                                        wip_osp1.resource_seq_num
                                        FROM
                                        (SELECT
                                        /*+ leading(pd) index(pd PO_DISTRIBUTIONS_N8) */
                                        ph.segment1 po_number,
                                        ph.type_lookup_code po_req_type,
                                        ph.authorization_status approval_status_code,
                                        ps.need_by_date old_need_by_date,
                                        ps.promised_date old_promised_date,
										ps.CANCEL_FLAG CANCEL_FLAG_PL,
                                        ps.QUANTITY_RECEIVED QUANTITY_RECEIVED,
                                        ps.QUANTITY QUANTITY,
                                        ps.QUANTITY_CANCELLED QUANTITY_CANCELLED_PL,
                                        ps.CLOSED_CODE CLOSED_CODE,
                                        wip_osp.converttoprimarymoveqty(pl.item_id, pd.destination_organization_id, greatest(pd.quantity_ordered - NVL(pd.quantity_delivered, 0) - NVL(pd.quantity_cancelled, 0), 0), pl.unit_meas_lookup_code, WIP_ECC_UTIL_PVT.get_primary_uom_code(we.organization_id,we.primary_item_id),
                                        (SELECT wor.usage_rate_or_amount