ECC Discrete Manufacturing, Resources

Description
Categories: Enterprise Command Center
Imported from Enterprise Command Center
Description: This data set holds information about resources.
Dataset Key: wip-ospdetails2
Query Procedure: WIP_ECC_UTIL_PVT.GET_ECC_DATA_LOAD_INFO
Security Procedure: wip_ecc_datasecurity_pkg_pub.GetFilterAttributeValues

Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS

Contact us to schedule a demo or if you need help with the installation

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 ,
										 PO_RCV_LINK ,
                                         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_start_date_t ,
                                         resource_completion_date_t ,
                                         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 ,
                                         release_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 OPERATION_STATUS_CODE,
                                         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,autocharge_type
                                        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 ,
                                         'fwkhp_sswafunc.gif' as PO_RCV_LINK,
                                        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 ,
                                        wor.start_date resource_start_date_t ,
                                        wor.completion_date resource_completion_date_t ,
                                        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 ,
                                        decode(bdr.utilization, NULL, 100, bdr.utilization * 100) utilization ,
                                        decode(bdr.efficiency, NULL, 100, bdr.efficiency * 100) 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.release_num release_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,
                                        wor.autocharge_type
                                        FROM wip_entities we,
                                        wip_discrete_jobs wdj,
                                        bom_resources br,
                                        wip_operations wo,
                                        wip_operation_resources wor,
                                        bom_department_resources      bdr,
                                        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   =