VIEW: APPS.BOM_STRUCTURE_SYNC_V

Source

View Text - Preformatted

SELECT b.assembly_item_id as ASSEMBLY_ITEM_ID, b.organization_id as ORGANIZATION_ID , b.sort_order as SORT_ORDER, b.parent_sort_order as PARENT_SORT_ORDER, b.plan_level as PLAN_LEVEL, b.explode_group_id as EXPLODE_GROUP_ID, b.top_item_id as TOP_ITEM_ID, b.top_bill_sequence_id as TOP_BILL_SEQUENCE_ID, b.bill_sequence_id as BILL_SEQUENCE_ID, b.component_item_id as COMPONENT_ITEM_ID, b.component_sequence_id as COMPONENT_SEQUENCE_ID, b.component_quantity as COMPONENT_QUANTITY, b.effectivity_date as EFFECTIVITY_DATE, b.disable_date as DISABLE_DATE, b.COMP_BILL_SEQ_ID as COMP_BILL_SEQ_ID, i1.concatenated_segments as ASSEMBLY_ITEM_NAME, i1.description as ASSEMBLY_ITEM_DESCRIPTION, i1.CUSTOMER_ORDER_ENABLED_FLAG as ASSEM_CUSTORDERENABLED_FLAG, i1.CUSTOMER_ORDER_FLAG as ASSEM_CUSTORDER_FLAG, i1.effectivity_control as ASSEM_EFFECTIVITY_CONTROL, i1.bom_item_type as ASSEMBLY_BOM_ITEM_TYPE, i1.PRIMARY_UOM_CODE as ASSEM_PRIMARY_UOM, i2.concatenated_segments as COMPONENT_ITEM_NAME, i2.description as COMPONENT_ITEM_DESCRIPTION, i2.CUSTOMER_ORDER_ENABLED_FLAG as COMP_CUSTORDERENABLED_FLAG, i2.CUSTOMER_ORDER_FLAG as COMP_CUSORDER_FLAG, i2.effectivity_control as COMP_EFFECTIVITY_CONTROL, i2.bom_item_type as COMP_BOM_ITEM_TYPE, i2.PRIMARY_UOM_CODE as COMP_PRIMARY_UOM, HAOUT_OU.name as OPERATING_UNIT_NAME, iv.organization_code as ORGANIZATION_CODE, iv.organization_code as ORGANIZATION_NAME, HAOUT_OU.organization_id as OPERATING_UNIT_ID FROM bom_explosions b, mtl_system_items_vl i1, mtl_system_items_vl i2, HR_ALL_ORGANIZATION_UNITS_TL HAOUT_OU, mtl_parameters iv, HR_ORGANIZATION_INFORMATION HOI WHERE nvl(b.assembly_item_id, b.component_item_id) = i1.inventory_item_id AND b.organization_id = i1.organization_id AND b.component_item_id = i2.inventory_item_id AND b.organization_id = i2.organization_id AND decode(plan_level, 0, sysdate, b.implementation_date) is not null AND nvl(b.disable_date, sysdate+1) >= sysdate AND b.organization_id = iv.organization_id AND iv.organization_id = hoi.organization_id AND UPPER((HOI.ORG_INFORMATION_CONTEXT||'')) ='ACCOUNTING INFORMATION' AND HAOUT_OU.ORGANIZATION_ID = DECODE(ORG_INFORMATION_CONTEXT, 'Accounting Information', TO_NUMBER(ORG_INFORMATION3), TO_NUMBER(NULL)) AND HAOUT_OU.LANGUAGE = UserEnv('LANG')

View Text - HTML Formatted

SELECT B.ASSEMBLY_ITEM_ID AS ASSEMBLY_ITEM_ID
, B.ORGANIZATION_ID AS ORGANIZATION_ID
, B.SORT_ORDER AS SORT_ORDER
, B.PARENT_SORT_ORDER AS PARENT_SORT_ORDER
, B.PLAN_LEVEL AS PLAN_LEVEL
, B.EXPLODE_GROUP_ID AS EXPLODE_GROUP_ID
, B.TOP_ITEM_ID AS TOP_ITEM_ID
, B.TOP_BILL_SEQUENCE_ID AS TOP_BILL_SEQUENCE_ID
, B.BILL_SEQUENCE_ID AS BILL_SEQUENCE_ID
, B.COMPONENT_ITEM_ID AS COMPONENT_ITEM_ID
, B.COMPONENT_SEQUENCE_ID AS COMPONENT_SEQUENCE_ID
, B.COMPONENT_QUANTITY AS COMPONENT_QUANTITY
, B.EFFECTIVITY_DATE AS EFFECTIVITY_DATE
, B.DISABLE_DATE AS DISABLE_DATE
, B.COMP_BILL_SEQ_ID AS COMP_BILL_SEQ_ID
, I1.CONCATENATED_SEGMENTS AS ASSEMBLY_ITEM_NAME
, I1.DESCRIPTION AS ASSEMBLY_ITEM_DESCRIPTION
, I1.CUSTOMER_ORDER_ENABLED_FLAG AS ASSEM_CUSTORDERENABLED_FLAG
, I1.CUSTOMER_ORDER_FLAG AS ASSEM_CUSTORDER_FLAG
, I1.EFFECTIVITY_CONTROL AS ASSEM_EFFECTIVITY_CONTROL
, I1.BOM_ITEM_TYPE AS ASSEMBLY_BOM_ITEM_TYPE
, I1.PRIMARY_UOM_CODE AS ASSEM_PRIMARY_UOM
, I2.CONCATENATED_SEGMENTS AS COMPONENT_ITEM_NAME
, I2.DESCRIPTION AS COMPONENT_ITEM_DESCRIPTION
, I2.CUSTOMER_ORDER_ENABLED_FLAG AS COMP_CUSTORDERENABLED_FLAG
, I2.CUSTOMER_ORDER_FLAG AS COMP_CUSORDER_FLAG
, I2.EFFECTIVITY_CONTROL AS COMP_EFFECTIVITY_CONTROL
, I2.BOM_ITEM_TYPE AS COMP_BOM_ITEM_TYPE
, I2.PRIMARY_UOM_CODE AS COMP_PRIMARY_UOM
, HAOUT_OU.NAME AS OPERATING_UNIT_NAME
, IV.ORGANIZATION_CODE AS ORGANIZATION_CODE
, IV.ORGANIZATION_CODE AS ORGANIZATION_NAME
, HAOUT_OU.ORGANIZATION_ID AS OPERATING_UNIT_ID
FROM BOM_EXPLOSIONS B
, MTL_SYSTEM_ITEMS_VL I1
, MTL_SYSTEM_ITEMS_VL I2
, HR_ALL_ORGANIZATION_UNITS_TL HAOUT_OU
, MTL_PARAMETERS IV
, HR_ORGANIZATION_INFORMATION HOI
WHERE NVL(B.ASSEMBLY_ITEM_ID
, B.COMPONENT_ITEM_ID) = I1.INVENTORY_ITEM_ID
AND B.ORGANIZATION_ID = I1.ORGANIZATION_ID
AND B.COMPONENT_ITEM_ID = I2.INVENTORY_ITEM_ID
AND B.ORGANIZATION_ID = I2.ORGANIZATION_ID
AND DECODE(PLAN_LEVEL
, 0
, SYSDATE
, B.IMPLEMENTATION_DATE) IS NOT NULL
AND NVL(B.DISABLE_DATE
, SYSDATE+1) >= SYSDATE
AND B.ORGANIZATION_ID = IV.ORGANIZATION_ID
AND IV.ORGANIZATION_ID = HOI.ORGANIZATION_ID
AND UPPER((HOI.ORG_INFORMATION_CONTEXT||'')) ='ACCOUNTING INFORMATION'
AND HAOUT_OU.ORGANIZATION_ID = DECODE(ORG_INFORMATION_CONTEXT
, 'ACCOUNTING INFORMATION'
, TO_NUMBER(ORG_INFORMATION3)
, TO_NUMBER(NULL))
AND HAOUT_OU.LANGUAGE = USERENV('LANG')