ECC Discrete Manufacturing, Outside Processing

Description
Categories: Enterprise Command Center
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
Run ECC Discrete Manufacturing, Outside Processing and other Oracle EBS reports with Blitz Report™ on our demo environment
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_queue+quantity_in_run inprogress_quantity,
                                         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,
									  buyer_name
										FROM (
										SELECT 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,
										 wip_osp.buyer_name,
                                        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,
										wip_osp1.buyer_name
                                        FROM
                                        (SELECT
                                        /*+ leading ( pd pl ) index ( pd PO_DISTRIBUTIONS_N8 )  use(pd pl) */
                                        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),