ECC Discrete Manufacturing, Serial Numbers

➜ Back to Library
Description
Categories: Enterprise Command Center
Columns: Ecc Spec Id, Job, Job Name, Organization Id, Osp Operation Flag, Assembly, Assembly Uom, Assembly Description, Assembly Catalog Grp, Assembly Base Model ...
Imported from Enterprise Command Center
Description: This data set holds information about serial-level details for work orders
Dataset Key: wip-serialdetails
Query Procedure: WIP_ECC_UTIL_PVT.GET_ECC_DATA_LOAD_INFO
Security Procedure: wip_ecc_datasecurity_pkg_pub.GetFilterAttributeValues 
select
x.*
from
(
 SELECT * FROM (  WITH lookup_details as (SELECT /*+ materialize */ meaning,
                                            lookup_code,
                                            language,
                                            lookup_type,
                                            security_group_id,
                                            view_application_id
                                          FROM fnd_lookup_values
                                          WHERE lookup_type      IN ('WIP_JOB_STATUS','WIP_SERIAL_STATUS','WIP_INTRAOPERATION_STEP','SERIAL_NUM_STATUS','WIP_SERIAL_YIELD')
                                          AND view_application_id = 700
                                          AND security_group_id   = 0
                                          ) SELECT
                                        /*+ leading(we) */
                                        TO_CHAR (wdj.wip_entity_id ||'-'||wdj.organization_id  ||'-'||msn.serial_number||'-SER') ecc_spec_id,
                                        wdj.wip_entity_id job,
                                        we.wip_entity_name job_name,
                                        status_lkp.meaning job_status,
										wdj.organization_id  organization_id,
										CASE WHEN (SELECT SUM(CASE
                                                          WHEN autocharge_type IN(3,4)
                                                          THEN 1
                                                          ELSE 0
                                                        END)
                                                     FROM wip_operation_resources wor
                                                     WHERE wdj.wip_entity_id   = wor.wip_entity_id
                                                     AND wdj.organization_id   = wor.organization_id
                                        ) > 0 THEN 1 ELSE 2 END  osp_operation_flag,
                                        WIP_ECC_UTIL_PVT.get_concatenated_segments(wdj.organization_id,wdj.primary_item_id) assembly ,
                                        WIP_ECC_UTIL_PVT.get_primary_uom_code(wdj.organization_id,wdj.primary_item_id) assembly_uom ,
                                        WIP_ECC_UTIL_PVT.get_description(wdj.organization_id,wdj.primary_item_id) assembly_description ,
									    (SELECT acat.concatenated_segments
                                        FROM mtl_item_catalog_groups_b_kfv acat
                                        WHERE acat.item_catalog_group_id = WIP_ECC_UTIL_PVT.get_item_catalog_group_id(wdj.organization_id,wdj.primary_item_id)
                                        ) assembly_catalog_grp,
										(SELECT msi6.concatenated_segments
                                        FROM mtl_system_items_vl msi6
                                        WHERE msi6.inventory_item_id = WIP_ECC_UTIL_PVT.get_base_item_id(wdj.organization_id,wdj.primary_item_id)
                                        AND msi6.organization_id     = wdj.organization_id
                                        ) assembly_base_model,
                                        msn.serial_number ,
                                        msn.wip_entity_id wip_entity_id ,
                                        NULL parent_object_id,
                                        msn.operation_seq_num operation_seq_num ,
                                        msn.intraoperation_step_type intraoperation_step_type ,
                                        msn.cycles_since_mark ,
                                        msn.cycles_since_new ,
                                        msn.cycles_since_overhaul ,
                                        msn.cycles_since_repair ,
                                        msn.cycles_since_visit ,
                                        msn.number_of_repairs ,
                                        msn.territory_code ,
                                        msn.time_since_mark ,
                                        msn.time_since_new ,
                                        msn.time_since_overhaul ,
                                        msn.time_since_repair ,
                                        msn.time_since_visit ,
                                        wip_serial_status_code.meaning wip_serial_status,
                                        wip_serial_status_code.lookup_code wip_serial_status_flag,
                                        assembly_serial_op_step.meaning assembly_serial_op_step,
                                        inv_serial_status_code.meaning inventory_serial_status,
                                        inv_serial_status_code.lookup_code inv_serial_status_code,
                                        inv_serial_status_code.language,
                                        CASE
                                            WHEN intraoperation_step_type IN (5)
                                            THEN 'Scrap'
                                            WHEN intraoperation_step_type IN (4)
                                            THEN 'Reject'
                                            ELSE 'In progress'
                                          END serial_type,
                                          (SELECT SUM(
                                            CASE
                                              WHEN fm_operation_seq_num        > to_operation_seq_num
                                              OR (fm_operation_seq_num         = to_operation_seq_num
                                              AND to_intraoperation_Step_type IN (1,2)
                                              AND fm_intraoperation_Step_type IN (3,4,5))
                                              THEN 1
                                              ELSE 0
                                            END )
                                          FROM wip_move_transactions wmt,
                                            wip_serial_move_transactions wsmt
                                          WHERE wmt.organization_id            = we.organization_id
                                          AND wmt.to_operation_seq_num         = msn.operation_seq_num
                                          AND wmt.to_intraoperation_Step_type  = msn.intraoperation_step_type
                                          AND wmt.to_intraoperation_Step_type IN (4,5)
                                          AND wsmt.transaction_id              = wmt.transaction_id
                                          AND wsmt.assembly_serial_number      = msn.serial_number
                                          AND wmt.wip_entity_id                = msn.wip_entity_id
                                          ) rework_quantity,
                                          CASE
                                            WHEN intraoperation_step_type IN (5)
                                            THEN scrap_lkp.meaning
                                            WHEN intraoperation_step_type IN (4)
                                            THEN reject_lkp.meaning
                                            ELSE NULL
                                          END yield
                                        FROM mtl_serial_numbers msn,
                                        wip_entities we,
                                        wip_discrete_jobs wdj,
                                        (SELECT
                                        ml2.meaning,
                                        ml2.language,
                                        to_number(ml2.lookup_code) LOOKUP_CODE
                                        FROM lookup_details ml2
                                        WHERE ml2.lookup_type       = 'WIP_SERIAL_STATUS'
                                        AND ml2.view_application_id = 700
                                        AND ml2.security_group_id   = 0
                                        ) wip_serial_status_code ,  (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
                                        ml3.meaning,
                                        ml3.language,
                                        ml3.lookup_code
                                        FROM lookup_details ml3
                                        WHERE ml3.lookup_type       = 'WIP_INTRAOPERATION_STEP'
                                        AND ml3.view_application_id = 700
                                        AND ml3.security_group_id   = 0
                                        ) assembly_serial_op_step ,
                                        (SELECT
                                        ml4.lookup_code,
                                        ML4.MEANING,
                                        ml4. language
                                        FROM lookup_details ml4
                                        WHERE ml4.lookup_type       = 'SERIAL_NUM_STATUS'
                                        AND ml4.view_application_id = 700
                                        AND ml4.security_group_id   = 0
                                        ) inv_serial_status_code  ,
                                        (SELECT
                                        ml4.lookup_code,
                                        ML4.MEANING,
                                        ml4. language
                                        FROM lookup_details ml4
                                        WHERE ml4.lookup_type       = 'WIP_SERIAL_YIELD'
                                        AND ml4.view_application_id = 700
                                        AND ml4.security_group_id   = 0
                                        AND ml4.lookup_code =1
                                        ) scrap_lkp,
                                        (SELECT
                                        ml4.lookup_code,
                                        ML4.MEANING,
                                        ml4. language
                                        FROM lookup_details ml4
                                        WHERE ml4.lookup_type       = 'WIP_SERIAL_YIELD'
                                        AND ml4.view_application_id = 700
                                        AND ml4.security_group_id   = 0
                                        AND ml4.lookup_code =2
                                        ) reject_lkp
                                        WHERE msn.wip_entity_id                                                    = we.wip_entity_id
                                        AND inv_serial_status_code.lookup_code(+)                                  = msn.current_status
                                        AND assembly_serial_op_step.lookup_code(+)                                 = msn.intraoperation_step_type
                                        AND wip_serial_status_code.lookup_code(+)                                  = DECODE(msn.operation_seq_num, NULL, 1, 2)
                                        AND wdj.status_type                                                        = status_lkp.lookup_code (+)
                                        AND status_lkp.language                                                    = nvl(inv_serial_status_code.language, status_lkp.language)
                                        AND status_lkp.language                                                    = nvl(assembly_serial_op_step.language, status_lkp.language)
                                        AND status_lkp.language                                                    = nvl(wip_serial_status_code.language, status_lkp.language)
                                        AND status_lkp.language                                                    = nvl(scrap_lkp.language, status_lkp.language)
                                        AND status_lkp.language                                                    = nvl(reject_lkp.language, status_lkp.language)
                                        AND wdj.wip_entity_id                                                      = we.wip_entity_id
 
                                        AND we.primary_item_id                                                     = msn.inventory_item_id
                                        AND we.entity_type                                                        IN (1,3)
                                        AND wdj.status_type                                                       IN (1,3,4,5,6,7,12,14,15)   AND (NVL(wdj.date_completed,wdj.scheduled_completion_date) >= SYSDATE - 30
                                          OR wdj.status_type  IN (3,6)
                                          OR (wdj.status_type  = 1
 
                                          AND wdj.scheduled_start_date  <= SYSDATE + 7 ))  UNION ALL  SELECT ecc_spec_id,
                                        job,
                                        job_name,
                                        job_status,
										organization_id,
										osp_operation_flag,
										assembly ,
                                        assembly_uom ,
                                        assembly_description ,
									    assembly_catalog_grp,
										assembly_base_model,
                                        serial_number ,
                                        wip_entity_id ,
                                        parent_object_id,
                                        operation_seq_num operation_seq_num ,
                                        intraoperation_step_type intraoperation_step_type ,
                                        cycles_since_mark ,
                                        cycles_since_new ,
                                        cycles_since_overhaul ,
                                        cycles_since_repair ,
                                        cycles_since_visit ,
                                        number_of_repairs ,
                                        territory_code ,
                                        time_since_mark ,
                                        time_since_new ,
                                        time_since_overhaul ,
                                        time_since_repair ,
                                        time_since_visit ,
                                        wip_serial_status ,
                                        wip_serial_status_flag ,
                                        assembly_serial_op_step ,
                                        inventory_serial_status ,
                                        inv_serial_status_code,
                                        language,
                                        serial_type,
                                        rework_quantity,
	                                    CASE WHEN rework_quantity >