ECC Discrete Manufacturing, Resources

Description
Categories: Enterprise Command Center
Columns: Ecc Spec Id, Ecc Op Spec Id, Ecc Rsc Spec Id, Job, Job Name, Assembly, Assembly Description, Organization Id, Organization Code, Operation Code ...
Imported from Enterprise Command Center
Description: This data set holds information about the resources
Dataset Key: wip-ospdetails
Query Procedure: WIP_ECC_UTIL_PVT.GET_ECC_DATA_LOAD_INFO
Security Procedure: wip_ecc_datasecurity_pkg_pub.GetFilterAttributeValues
select
x.*
from
(
 SELECT * 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_PURGE_REPORT_TYPE','SYS_YES_NO','BOM_RESOURCE_SCHEDULE_TYPE','BOM_RESOURCE_TYPE','CST_BASIS','BOM_AUTOCHARGE_TYPE','WIP_JOB_STATUS')
                                          AND view_application_id = 700
                                          AND security_group_id   = 0
                                           ) SELECT /*+ leading(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 ,
										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 ,
										mp.organization_code organization_code ,
                                        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.replacement_group_num replacement_group_num,
										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.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 ,
                                        wip_osp.assembly_qty_pending pending_quantity ,
                                        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 2
                                        ELSE 1
                                        END osp_operation_flag,
										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 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_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 ,
                                        status_lkp.meaning job_status
                                        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,
                                        (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
 
                                        yeslk.meaning,
                                        yeslk.language,
                                        yeslk.lookup_code
                                        FROM lookup_details yeslk
                                        WHERE yeslk.lookup_type       = 'SYS_YES_NO'
                                        AND yeslk.view_application_id = 700
										AND yeslk.lookup_code         = '1'
                                        AND yeslk.security_group_id   = 0
                                        ) yeslk,
										(SELECT
 
                                        nonelk.meaning,
                                        nonelk.language,
                                        nonelk.lookup_code
                                        FROM lookup_details nonelk
                                        WHERE nonelk.lookup_type       = 'WIP_PURGE_REPORT_TYPE'
                                        AND nonelk.view_application_id = 700
                                        AND nonelk.lookup_code         = '4'
                                        AND nonelk.security_group_id   = 0
                                        ) nonelk,
										(SELECT
 
                                        nolk.meaning,
                                        nolk.language,
                                        nolk.lookup_code
                                        FROM lookup_details nolk
                                        WHERE nolk.lookup_type       = 'SYS_YES_NO'
                                        AND nolk.view_application_id = 700
										AND nolk.lookup_code         = '2'
                                        AND nolk.security_group_id   = 0
                                        ) nolk,
                                        hr_all_organization_units_tl haout,
                                        (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 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.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 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,
                                        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
                                        FROM wip_operation_resources wor
                                        WHERE wor.wip_entity_id                 = pd.wip_entity_id
                                        AND NVL(wor.repetitive_schedule_id, -1) = NVL(pd.wip_repetitive_schedule_id, -1)
                                        AND wor.operation_seq_num               = pd.wip_operation_seq_num
                                        AND wor.organization_id                 = pd.destination_organization_id
                                        AND wor.resource_seq_num                = pd.wip_resource_seq_num
                                        )) assembly_qty_pending,
										pd.quantity_ordered,
								        pd.quantity_delivered,
										pd.quantity_cancelled,
										pd.po_header_id ||'-'||pd.po_line_id rec_key,
                                        WIP_ECC_UTIL_PVT.get_primary_uom_code(we.organization_id,we.primary_item_id) assembly_primary_uom,
                                        pd.wip_entity_id wip_entity_id,
                                        pd.destination_organization_id organization_id,
                                        pd.wip_operation_seq_num operation_seq_num,
                                        pd.wip_resource_seq_num resource_seq_num,
                                        NULL req_number,
                                        ps.cancel_flag cancel_flag,
                                        pl.line_num line_number,
                                        po_v.vendor_name vendor_name,
                                       WIP_ECC_UTIL_PVT.get_concatenated_segments(pd.destination_organization_id,pl.item_id ) po_item_number
                                        FROM wip_entities we,
                                        wip_discrete_jobs wdj,
                                        po_distributions_all pd,
                                        po_line_locations_all ps,
                                        po_lines_all pl,
                                        po_headers_all ph,
                                        po_document_types_all_b pdt,
                                        ap_suppliers po_v
                                        WHERE pdt.document_type_code       = 'PO'
                                        AND ph.type_lookup_code            = 'STANDARD'
                                        AND ph.type_lookup_code            = pdt.document_subtype
                                        AND ph.org_id                      = pdt.org_id
                                        AND pd.po_header_id                = ph.po_header_id
                                        AND pd.po_line_id                  = pl.po_line_id
                                        AND pd.line_location_id            = ps.line_location_id
                                        AND pd.wip_entity_id               = we.wip_entity_id
                                        AND we.entity_type                IN (1,3)
                                        AND we.wip_entity_id               = wdj.wip_entity_id
                                        AND we.organization_id             = wdj.organization_id
                                        AND wdj.status_type               IN (1,3,4,5,6,7,12,14,15)
                                        AND ph.vendor_id                   = po_v.vendor_id(+)
                                        UNION ALL
                                        SELECT
                                        /*+ leading ( pd pl ) index ( pd PO_DISTRIBUTIONS_N8 ) use_nl(pd pl) */
                                        ph.segment1
                                        ||'-'
                                        ||pr.release_num,
                                        ph.type_lookup_code,
                                        pr.authorization_status,
                                        ps.need_by_date,
                                        ps.promised_date,
                                        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
                                        FROM wip_operation_resources wor
                                        WHERE wor.wip_entity_id                 = pd.wip_entity_id
                                        AND NVL(wor.repetitive_schedule_id, -1) = NVL(pd.wip_repetitive_schedule_id, -1)
                                        AND wor.operation_seq_num               = pd.wip_operation_seq_num
                                        AND wor.organization_id                 = pd.destination_organization_id
                                        AND wor.resource_seq_num                = pd.wip_resource_seq_num
                                        )),
										pd.quantity_ordered,
								        pd.quantity_delivered,
										pd.quantity_cancelled,
										pd.po_header_id ||'-'||pd.po_line_id|| '-'||pr.po_release_id  rec_key,
                                        WIP_ECC_UTIL_PVT.get_primary_uom_code(we.organization_id,we.primary_item_id),
                                        pd.wip_entity_id,
                                        pd.destination_organization_id,
                                        pd.wip_operation_seq_num,
                                        pd.wip_resource_seq_num,
                                        NULL,
                                        ps.cancel_flag,
                                        pl.line_num,
                                        po_v.vendor_name,
                                        WIP_ECC_UTIL_PVT.get_concatenated_segments(pd.destination_organization_id,pl.item_id )
                                        FROM wip_entities we,
                                        wip_discrete_jobs wdj,
                                        po_distributions_all pd,
                                        po_line_locations_all ps,
                                        po_lines_all pl,
                                        po_document_types_all_b pdt,
                                        po_headers_all ph,
                                        po_releases_all pr,
                                        ap_suppliers po_v
                                        WHERE pdt.document_type_code       = 'RELEASE'
                                        AND ph.type_lookup_code            = 'BLANKET'
                                        AND pr.release_type                = pdt.document_subtype
                                        AND pr.org_id                      = pdt.org_id
                                        AND pr.po_release_id               = ps.po_release_id
                                        AND pr.po_header_id                = ph.po_header_id
                                        AND pd.po_header_id                = ph.po_header_id
                                        AND pd.po_line_id                  = pl.po_line_id
                                        AND pd.line_location_id            = ps.line_location_id
                                        AND pd.wip_entity_id               = we.wip_entity_id
                                        AND we.entity_type                IN (1,3)
                                        AND we.wip_entity_id               = wdj.wip_entity_id
                                        AND we.organization_id             = wdj.organization_id
                                        AND wdj.status_type               IN (1,3,4,5,6,7,12,14,15)
                                        AND ph.vendor_id                   = po_v.vendor_id(+)
                                        UNION ALL
                                        SELECT
                                        /*+ leading (prl) index (prl PO_REQUISITION_LINES_N9) */
                                        NULL,
                                        'REQUISITION',
                                        prh.authorization_status,
                                        prl.need_by_date,
                                        to_date(NULL),
                                        wip_osp.converttoprimarymoveqty(prl.item_id, prl.destination_organization_id, prl.quantity, prl.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
                                        FROM wip_operation_resources wor
                                        WHERE wor.wip_entity_id                 = prl.wip_entity_id
                                        AND NVL(wor.repetitive_schedule_id, -1) = NVL(prl.wip_repetitive_schedule_id, -1)
                                        AND wor.operation_seq_num               = prl.wip_operation_seq_num
                                        AND wor.organization_id                 = prl.destination_organization_id
                                        AND wor.resource_seq_num                = prl.wip_resource_seq_num
                                        )),
										prl.quantity  quantity_ordered,
								        0 quantity_delivered,
										0 quantity_cancelled,
										prl.requisition_header_id ||'-'||prl.requisition_line_id rec_key,
                                        WIP_ECC_UTIL_PVT.get_primary_uom_code(we.organization_id,we.primary_item_id),
                                        prl.wip_entity_id,
                                        prl.destination_organization_id,
                                        prl.wip_operation_seq_num,
                                        prl.wip_resource_seq_num,
                                        prh.segment1,
                                        prl.cancel_flag ,
                                        prl.line_num,
                                        NVL(po_v.vendor_name,prl.suggested_vendor_name),
                                        WIP_ECC_UTIL_PVT.get_concatenated_segments(prl.destination_organization_id,prl.item_id )
                                        FROM wip_entities we,
                                        wip_discrete_jobs wdj,
                                        po_requisition_headers_all prh,
                                        po_requisition_lines_all prl,
                                        ap_suppliers po_v
                                        WHERE NOT EXISTS
                                        (SELECT 1
                                        FROM po_line_locations_all pll
                                        WHERE prl.line_location_id = pll.line_location_id
                                        )
                                        AND prl.requisition_header_id       = prh.requisition_header_id
                                        AND prl.wip_entity_id               = we.wip_entity_id
                                        AND we.entity_type                 IN (1,3)
                                        AND we.wip_entity_id                = wdj.wip_entity_id
                                        AND we.organization_id              = wdj.organization_id
                                        AND wdj.status_type                IN (1,3,4,5,6,7,12,14,15)
                                        AND prl.vendor_i