Indented BOM & WIP SQL 's
SELECT DISTINCT LPAD (' ', LEVEL * 2) || LEVEL ORDER_LEVEL
, LPAD (' ', LEVEL * 1) || msib.segment1 ASSEMBLY_ITEM
, LPAD (' ', LEVEL * 4) || msib2.segment1 AS COMPONENT_ITEM
, msib2.description COMPONENT_ITEM_DESCRIPTION
, bic.bill_sequence_id c, bom.bill_sequence_id b
, msib.description ASSEMBLY_DESCRIPTION
, msib.inventory_item_status_code ASSEMBLY_ITEM_STATUS
, decode (msib2.PLANNING_MAKE_BUY_CODE, 1, 'MAKE'
, 2, 'BUY'
,msib2.PLANNING_MAKE_BUY_CODE ) MAKE_BUY
, decode (msib2.item_type,'SUPPORT_ITEM','SUPPORT ITEM'
,'BULK_ITEM','BULK ITEM'
,'P','PURCHASED ITEM'
,'SA','SUBASSEMBLY'
,'PH','PHANTOM ITEM'
,msib2.item_type ) ITEM_TYPE
--, v.vendor_name,v.vendor_num
, msib2.inventory_item_status_code COMPONENT_ITEM_STATUS
, bic.COMPONENT_QUANTITY
, bic.ITEM_NUM
, bic.OPERATION_SEQ_NUM
, bic.PLANNING_FACTOR
, bic.COMPONENT_YIELD_FACTOR
, bic.effectivity_date,bic.disable_date
,(select distinct aps.vendor_name
from apps.PO_Approved_SUPPLIER_list asl,
apps.mtl_system_items_b msi,
apps.ap_suppliers aps
where msi.inventory_item_id = asl.item_id
and asl.vendor_id = aps.vendor_id
and msi.segment1 = msib2.segment1
and msi.organization_id = msib2.organization_id
--and aps.segment1 is not null
and rownum =1
) VENDOR_name
,(select distinct aps.segment1
from apps.PO_Approved_SUPPLIER_list asl,
apps.mtl_system_items_b msi,
apps.ap_suppliers aps
where msi.inventory_item_id = asl.item_id
and asl.vendor_id = aps.vendor_id
and msi.segment1 = msib2.segment1
and msi.organization_id = msib2.organization_id
--and aps.segment1 is not null
and rownum =1
) VENDOR_num
, SYS_CONNECT_BY_PATH (msib2.segment1, '/') PATH
FROM bom.bom_components_b bic
, bom.bom_structures_b bom
, inv.mtl_system_items_b msib
, inv.mtl_system_items_b msib2
, mtl_parameters mp
-- , (select aps.vendor_name,aps.segment1 vendor_num,
-- msi.organization_id,msi.inventory_item_id
-- from apps.PO_Approved_SUPPLIER_list asl,
-- apps.mtl_system_items_b msi,
-- apps.ap_suppliers aps
-- where msi.inventory_item_id = asl.item_id
-- and asl.vendor_id = aps.vendor_id
-- ) v
WHERE 1 = 1
AND bic.bill_sequence_id = bom.bill_sequence_id
--AND SYSDATE BETWEEN bic.effectivity_date AND Nvl(bic.disable_date, SYSDATE)
AND bom.assembly_item_id = msib.inventory_item_id
AND bom.organization_id = msib.organization_id
AND bic.component_item_id = msib2.inventory_item_id
AND bom.organization_id = msib2.organization_id
AND mp.organization_id = msib.organization_id
AND mp.organization_code = 'IND'
AND bom.alternate_bom_designator IS NULL
--and msib2.segment1 = '31678'
and msib2.item_type in( 'BULK_ITEM','SUPPORT_ITEM','P')
--and v.inventory_item_id = msib2.inventory_item_id
--and v.organization_id = msib2.organization_id
START WITH msib.segment1 = '34567W'
CONNECT BY NOCYCLE PRIOR bic.component_item_id = msib.inventory_item_id
ORDER BY PATH ;
SELECT wip.wip_entity_name WO_NUMBER,
msi.description,
wip.start_quantity qty,
msi.segment1 part_number,
(SELECT OPERATION_DESCRIPTION
FROM
(SELECT BOS.OPERATION_SEQ_NUM,
BOS.OPERATION_DESCRIPTION
FROM APPS.BOM_OPERATIONAL_ROUTINGS BOR,
APPS.BOM_OPERATION_SEQUENCES BOS
WHERE BOR.ROUTING_SEQUENCE_ID = BOS.ROUTING_SEQUENCE_ID
AND BOR.ORGANIZATION_ID = WIP.ORGANIZATION_ID
AND BOR.ASSEMBLY_ITEM_ID = WIP.PRIMARY_ITEM_ID
AND UPPER(BOS.OPERATION_DESCRIPTION) LIKE 'S/U%PRESS%'
ORDER BY BOS.OPERATION_SEQ_NUM
)
WHERE ROWNUM = 1
) SETUP_OP_DESC,
NVL(wip.date_released,wip.creation_date) released_creation_date,
wip.job_type_meaning wo_type,
wip.status_type_disp,
TO_CHAR(wip.scheduled_start_date,'DD-MON-YYYY') start_date,
TO_CHAR(wip.scheduled_completion_date,'DD-MON-YYYY') comp_date,
TO_CHAR(wip.date_released,'DD-MON-YYYY HH24:MI:SS') date_released,
TO_CHAR(wip.creation_date,'DD-MON-YYYY HH24:MI:SS') creation_date
FROM apps.wip_discrete_jobs_v wip,
apps.mtl_system_items_b msi
WHERE wip.organization_id = msi.organization_id
AND msi.inventory_item_id = wip.primary_item_id
This is very good. But, it contains disabled components and their sub-components. I can try to eliminate the by adding “and bic.disable_date is null” in the where clause. But, this only removes the components their sub-components remain. Like in an indented BOM, if you set the display to Current it removes the end dated or disable components and their sub-components. How do I achieve this.
ReplyDelete