ECC Discrete Manufacturing, Components

Description
Categories: Enterprise Command Center
Columns: Ecc Spec Id, Job, Inventory Item Id, Planned Start Date, Planned Completion Date, Job Name, Assembly, Assembly Description, Organization Id, Organization Code ...
Imported from Enterprise Command Center
Description: This data set holds information about work order components
Dataset Key: wip-compdetails
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_VIEW_ITEM','WIP_VIEW_ITEM_RESERVATION','BOM_BASIS_TYPE','WIP_JOB_STATUS','WIP_SUPPLY','WIP_SERIAL_YIELD')
                                          AND view_application_id = 700
                                          AND security_group_id   = 0
                                          ) SELECT src.*,
	                                     CASE WHEN status_type   IN (3,4,6) THEN
                                         CASE WHEN onhand_quantity_actual > open_quantity
                                           THEN 0
                                           ELSE open_quantity - (onhand_quantity_actual + reserved_non_inv_qty)
                                         END  END  shortage_quantity,
                                         (CASE WHEN status_type   IN (3,4,6) THEN
										   CASE WHEN onhand_quantity_actual > open_quantity
                                           THEN 0
                                           ELSE open_quantity - (onhand_quantity_actual + reserved_non_inv_qty)
                                         END END )*-1 shortage_quantity_tbl FROM (SELECT src.*,
                                           CASE WHEN status_type   IN (3,4,6) THEN
										   CASE WHEN (src.onhand_quantity - NVL(lag(open_qty_sum) over (partition BY inventory_item_id, organization_id order by component_date_required, job),0)) < 0
                                             THEN 0
                                             ELSE src.onhand_quantity - NVL(lag(open_qty_sum) over (partition BY inventory_item_id, organization_id order by component_date_required, job),0)
                                           END END onhand_quantity_actual
                                         FROM
                                         (select src.*,
                                       CASE WHEN status_type   IN (3,4,6)
                                            THEN NVL(src.required_quantity,0) - NVL(src.issued_quantity,0)
                                             END open_quantity,
                                             (SUM( CASE WHEN status_type   IN (3,4,6) THEN
                                               CASE
                                                WHEN NVL(src.required_quantity,0) - NVL(src.issued_quantity,0) < 0
                                                THEN 0
                                                ELSE NVL(src.required_quantity,0) - nvl(src.issued_quantity,0)
                                               END
                                             END ) over (partition BY src.inventory_item_id, src.organization_id order by src.component_date_required, src.rid)) open_qty_sum,
                                             NVL(WIP_ECC_UTIL_PVT.get_qty_onhand(organization_id,inventory_item_id,NULL,NULL),0)  onhand_quantity,
                                            NVL(WIP_ECC_UTIL_PVT.get_qty_onhand(organization_id,substitute_component_id,NULL,NULL),0) substitute_comp_onhand
                                      from (select * from (SELECT TO_CHAR (wdj.wip_entity_id
                                       ||'-'
                                       ||wro.operation_seq_num
                                       ||'-'
                                       ||wro.inventory_item_id
                                       ||'-'
                                       ||wdj.organization_id
                                       ||'-COMP') ecc_spec_id,
                                       wro.wip_entity_id JOB,
                                       wro.inventory_item_id,
									   wo.first_unit_start_date planned_start_date ,
                                       wo.last_unit_completion_date Planned_completion_date ,
                                       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 ,
                                       wdj.status_type,
                                       status_lkp.meaning job_status,
                                       wro.operation_seq_num operation_sequence_number,
                                       (SELECT bso.operation_code
                                       FROM bom_standard_operations bso,
                                         wip_operations wo
                                       WHERE wo.wip_entity_id        =wdj.wip_entity_id
                                       AND wo.operation_seq_num      = wro.operation_seq_num
                                       AND wo.standard_operation_id  = bso.standard_operation_id(+)
                                       AND NVL(bso.operation_type,1) = 1
                                       AND bso.line_id              IS NULL
                                       ) operation_code,
                                       msi2.concatenated_segments component,
                                       msi2.description component_description,
                                       msi2.primary_uom_code component_uom,
                                       NVL(
                                       (SELECT MAX(
                                         CASE
                                           WHEN autocharge_type IN(3,4)
                                           THEN 1
                                           ELSE 2
                                         END)
                                       FROM wip_operation_resources wor
                                       WHERE wro.wip_entity_id   = wor.wip_entity_id
                                       AND wro.operation_seq_num = wor.operation_seq_num
                                       AND wro.organization_id   = wor.organization_id
                                       ),2) osp_operation_flag,
                                       (SELECT m_cat.concatenated_segments
                                       FROM mtl_item_catalog_groups_b_kfv m_cat
                                       WHERE m_cat.item_catalog_group_id = msi2.item_catalog_group_id
                                       ) component_category,
                                       wro.quantity_per_assembly quantity_per_assembly ,
                                       NVL(wro.quantity_allocated,0) quantity_allocated,
                                       supply_type_lkp.meaning component_wip_supply_type ,
                                       haout.language Language,
                                       wro.supply_subinventory component_supply_subinventory ,
                                       (SELECT milk2.concatenated_segments
                                       FROM mtl_item_locations_kfv milk2
                                       WHERE milk2.inventory_location_id = wro.supply_locator_id
                                       ) component_supply_locator ,
                                       TO_CHAR(wro.comments) component_comments ,
                                       wro.date_required component_date_required ,
                                       basis_type_lkp.meaning component_basis_type,
									   item_reservation_lkp.meaning item_reservation_link,
									   view_comp_link.meaning comp_link,
									   view_sub_comp_link.meaning sub_comp_link,
                                       wro.required_quantity required_quantity ,
									   wip_ecc_util_pvt.get_resv_qty(wdj.organization_id,wro.wip_entity_id,wro.operation_seq_num ,wro.inventory_item_id) reserved_quantity,
                                       wip_ecc_util_pvt.get_resv_non_inv_qty(wdj.organization_id,wro.wip_entity_id,wro.operation_seq_num ,wro.inventory_item_id) reserved_non_inv_qty,
									   wro.quantity_issued issued_quantity ,
                                       bsc_1.substitute_components substitute_component,
                                       bsc_1.substitute_comp_uom substitute_component_uom,
                                       bsc_1.substitute_description substitute_description,
                                       bsc_1.inventory_item_id substitute_component_id,
                                       CASE
                                         WHEN wdj.status_type                                                                                                                     IN (3,4,6)
                                         AND ( (wro.quantity_per_assembly * (DECODE(wro.basis_type,2,1,NVL((wo.quantity_completed ), wdj.quantity_completed)))                    > wro.quantity_issued
                                         AND wro.wip_supply_type                                                                                                                  <> 1
                                         AND wro.quantity_per_assembly                                                                                                            >= 0)
                                         OR (wro.quantity_per_assembly * (DECODE(wro.basis_type,2,1,NVL((wo.quantity_running + wo.quantity_in_queue ), wdj.quantity_completed))) > wro.quantity_issued
                                         AND wro.wip_supply_type                                                                                                                   = 1
                                         AND wro.quantity_per_assembly                                                                                                            >= 0) )
                                         THEN 1
                                         ELSE 0
                                       END pending_material_issue,
                                       wo.scheduled_quantity ,
                                        wo.cumulative_scrap_quantity,
                                       wro.basis_type,
                                       wro.rowid rid
                                     FROM
	                                   wip_requirement_operations wro,
                                       wip_operations wo,
                                       wip_discrete_jobs wdj,
                                       wip_entities we,
                                       mtl_parameters mp,
                                       hr_all_organization_units_tl haout,
                                       mtl_system_items_vl msi2,
                                       (SELECT
                                         meaning,
                                         lookup_code,
                                         language
                                       FROM lookup_details supptype
                                       WHERE supptype.lookup_type       = 'WIP_SUPPLY'
                                       AND supptype.view_application_id = 700
                                       AND supptype.security_group_id   = 0
                                       ) supply_type_lkp,
                                       (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
                                         basistype.meaning,
                                         basistype.lookup_code,
                                         basistype.language
                                       FROM lookup_details basistype
                                       WHERE basistype.lookup_type       = 'BOM_BASIS_TYPE'
                                       AND basistype.view_application_id = 700
                                       AND basistype.security_group_id   = 0
                                       ) basis_type_lkp,
									   (SELECT
                                         basistype.meaning,
                                         basistype.lookup_code,
                                         basistype.language
                                       FROM lookup_details basistype
                                       WHERE basistype.lookup_type       = 'WIP_VIEW_ITEM_RESERVATION'
                                       AND basistype.view_application_id = 700
									   AND basistype.lookup_code         = '1'
                                       AND basistype.security_group_id   = 0
                                       ) item_reservation_lkp,
									   										(SELECT
                                        nonelk.meaning,
                                        nonelk.language,
                                        nonelk.lookup_code
                                        FROM lookup_details nonelk
                                        WHERE nonelk.lookup_type       = 'WIP_VIEW_ITEM'
                                        AND nonelk.view_application_id = 700
                                        AND nonelk.lookup_code         = '2'
                                        AND nonelk.security_group_id   = 0
                                        ) view_comp_link,
										(SELECT
                                        nonelk.meaning,
                                        nonelk.language,
                                        nonelk.lookup_code
                                        FROM lookup_details nonelk
                                        WHERE nonelk.lookup_type       = 'WIP_VIEW_ITEM'
                                        AND nonelk.view_application_id = 700
                                        AND nonelk.lookup_code         = '3'
                                        AND nonelk.security_group_id   = 0
                                        ) view_sub_comp_link,
                                       (SELECT DISTINCT wro2.component_sequence_id ,
                                         bsc_3.concatenated_segments substitute_components,
                                         bsc_3.description substitute_description,
                                         bsc_3.inventory_item_id,
                                         bsc_3.primary_uom_code substitute_comp_uom
                                       FROM wip_requirement_operations wro2,
                                         (SELECT bsc_2.component_sequence_id,
                                           msi7.inventory_item_id,
                                           msi7.organization_id,
                                           msi7.concatenated_segments concatenated_segments,
                                           msi7.description,
                                           msi7.primary_uom_code
                                         FROM bom_substitute_components bsc_2,
                                           mtl_system_items_kfv msi7
                                         WHERE bsc_2.substitute_component_id = msi7.inventory_item_id
                                         ) bsc_3
                                       WHERE wro2.component_sequence_id IS NOT NULL
                                       AND wro2.component_sequence_id    = bsc_3.component_sequence_id
                                       AND bsc_3.organization_id           = wro2.organization_id
                                       ) bsc_1
                                     WHERE we.wip_entity_id                                       = wdj.wip_entity_id
                                     AND wdj.wip_entity_id                                        = wro.wip_entity_id
                                     AND mp.organization_id                                       = we.organization_id
                                     AND haout.organization_id                                    = mp.organization_id
                                     AND wdj.wip_entity_id                                         = wo.wip_entity_id
                                     and wdj.organization_id = wo .organization_id
                                     AND wo.operation_seq_num(+) = wro.operation_seq_num
                                     AND wo.wip_entity_id(+) = wro.wip_entity_id
                                     AND wo.organization_id(+) = wro.organization_id
                                     AND msi2.inventory_item_id                                   = wro.inventory_item_id
                                     AND msi2.organization_id                                     = wro.organization_id
                                     AND wdj.status_type                                          = status_lkp.lookup_code(+)
                                     AND wro.component_sequence_id                                = bsc_1.component_sequence_id(+)
                                     AND wro.wip_supply_type                                      = supply_type_lkp.lookup_code(+)
                                     AND TO_CHAR(NVL(wro.basis_type,1))                           = basis_type_lkp.lookup_code(+)
                                     AND haout.language                                = NVL(basis_type_lkp.language,haout.language)
                                     AND haout.language                                = NVL(supply_type_lkp.language,haout.language)
                                     AND haout.language                                = NVL(status_lkp.language,haout.language)
									 AND haout.language = NVL(item_reservation_lkp.language,haout.language)
                                     AND haout.language = NVL(view_comp_link.language,haout.language)
                                     AND haout.language = NVL(view_sub_comp_link.language,haout.language)
                                     AND wdj.status_type                                         IN (1,3,4,5,6,7,12,14,15)
                                     AND we.entity_type                                          IN (1,3) 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(COMPONENT_WIP_SUPPLY_TYPE) AS 
		COMPONENT_WIP_SUPPLY_TYPE ,MAX(COMPONENT_BASIS_TYPE) AS COMPONENT_BASIS_TYPE,MAX(ORGANIZATION_NAME) AS ORGANIZATION_NAME,  MAX(JOB_STATUS) AS JOB_STATUS , MAX(ITEM_RESERVATION_LINK) AS ITEM_RESERVATION_LINK ,MAX(COMP_LINK) AS COMP_LINK , MAX(SUB_COMP_LINK) AS SUB_COMP_LINK   FOR LANGUAGE IN ('US' "US")) src )src) src) src
) x
where
2=2