ECC Discrete Manufacturing, Components
Description
Categories: Enterprise Command Center
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
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
Run
ECC Discrete Manufacturing, Components and other Oracle EBS reports with Blitz Report™ on our demo environment
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, TO_CHAR (we.wip_entity_name ||':' ||wro.operation_seq_num ||':' ||msi2.concatenated_segments ) wo_op_comp, 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, wdj.project_id , wdj.task_id, 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, 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, (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 and milk2.organization_id = wro.organization_id ) component_supply_locator , TO_CHAR(wro.comments) component_comments , wro.date_required component_date_required , basis_type_lkp.meaning component_basis_type, 'fwkhp_sswafunc.gif' as /* item_reservation_lkp.meaning */ item_reservation_link, 'fwkhp_sswafunc.gif' as /* view_comp_link.meaning */ comp_link, 'fwkhp_sswafunc.gif' as /* 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 |
