Indented BOM & WIP SQL 's

Query to get Tree structure of the indented BOM along with Vendor for the Purchased items and Item type

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 ;



Query to Get WIP Job Details:

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_datereleased_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



Comments

  1. 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

Post a Comment

Popular posts from this blog

PO & Receipt Query

Supplier SQL query

Sales Order SQL