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 > 0 THEN rework_flag ELSE accepted_flag END yield
                                     	FROM (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,
										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 ,
                                        we.wip_entity_id ,
                                        mog.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.meaning wip_serial_status ,
                                        3 wip_serial_status_flag ,
                                        wip_intraoperation_step.meaning assembly_serial_op_step ,
                                        inv_serial_status.meaning inventory_serial_status ,
                                        inv_serial_status.lookup_code inv_serial_status_code,
                                        inv_serial_status.language,
                                        'Completed' 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,
                                          rework_lkp.meaning rework_flag,
                                          accepted_lkp.meaning accepted_flag,
										  status_lkp.meaning job_status
                                        FROM mtl_serial_numbers msn,
                                        wip_entities we,
                                        mtl_system_items_vl msi8,
                                        mtl_object_genealogy mog,
                                        wip_discrete_jobs wdj,
                                        (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
                                        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 =4
                                        ) accepted_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 =3
                                        ) rework_lkp ,
                                        (SELECT
                                        ml4.lookup_code,
                                        ML4.LANGUAGE,
                                        ml4.meaning
                                        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,
                                        (SELECT
                                        ml8.meaning,
                                        ml8.lookup_code,
                                        ML8.LANGUAGE
                                        FROM lookup_details ml8
                                        WHERE ml8.lookup_type       = 'WIP_INTRAOPERATION_STEP'
                                        AND ml8.view_application_id = 700
                                        AND ml8.security_group_id   = 0
                                        ) wip_intraoperation_step,
                                        (SELECT
                                        ml6.meaning,
                                        ml6.lookup_code,
                                        ML6.LANGUAGE
                                        FROM lookup_details ml6
                                        WHERE ml6.lookup_type       = 'WIP_SERIAL_STATUS'
                                        AND ml6.view_application_id = 700
                                        AND ml6.lookup_code         = '3'
                                        AND ml6.security_group_id   = 0
                                        ) wip_serial_status
                                        WHERE msn.gen_object_id                                               = mog.parent_object_id
                                        AND we.gen_object_id                                                  = mog.object_id
                                        AND mog.object_type                                                   = 5
                                        AND mog.parent_object_type                                            = 2
                                        AND mog.end_date_active                                              IS NULL
                                        AND we.entity_type                                                   IN (1,3)
                                        AND wip_intraoperation_step.lookup_code(+)                            = msn.intraoperation_step_type
                                        AND inv_serial_status.lookup_code(+)                                  = msn.current_status
                                        AND status_lkp.language                                               = nvl(inv_serial_status.language, status_lkp.language)
                                        AND status_lkp.language                                               = nvl(wip_intraoperation_step.language, status_lkp.language)
                                        AND status_lkp.language                                               = nvl(wip_serial_status.language, status_lkp.language)
                                        AND status_lkp.language                                               = nvl(accepted_lkp.language, status_lkp.language)
                                        AND status_lkp.language                                               = nvl(rework_lkp.language, status_lkp.language)
                                        AND we.primary_item_id                                                = msn.inventory_item_id
                                       AND wdj.status_type                                                    = status_lkp.lookup_code (+)
                                        AND wdj.wip_entity_id                                                 = we.wip_entity_id
										AND wdj.organization_id                                               = msi8.organization_id(+)
                                        AND wdj.primary_item_id                                               = msi8.inventory_item_id(+)
                                        AND wdj.organization_id                                               = we.organization_id
                                        AND wdj.status_type                                                  IN (1,3,4,5,6,7,12,14,15)
                                        AND wdj.serialization_start_op                                       IS NOT NULL 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 )) )src WHERE SRC.LANGUAGE IN ('US') ) PIVOT ( MAX(WIP_SERIAL_STATUS) AS WIP_SERIAL_STATUS , 
		MAX(INVENTORY_SERIAL_STATUS) AS INVENTORY_SERIAL_STATUS , MAX(YIELD) AS YIELD , MAX ( ASSEMBLY_SERIAL_OP_STEP
    ) AS ASSEMBLY_SERIAL_OP_STEP, MAX ( JOB_STATUS ) AS JOB_STATUS FOR LANGUAGE IN ('US' "US"))
) x
where
2=2
Download
Blitz Report In Action
Blitz Report™

Blitz Report™ provides multiple benefits: