[Home] [Help]
SELECT cii.rowid row_id, cii.inventory_item_id, cii.serial_number, cii.instance_description as descriptive_text, cii.network_asset_flag network_asset_flag, mp.maint_organization_id current_organization_id, msi.concatenated_segments, msi.eam_item_type, msi.description asset_group_description, mck.concatenated_segments category_name, cii.asset_criticality_code asset_criticality, nvl(cii.maintainable_flag, 'N') maintainable_flag, msn_parent.inventory_item_id parent_item_id, msn_parent.serial_number parent_serial_number, msn_parent.gen_object_id parent_object_id, msn.gen_object_id, msn.CURRENT_STATUS, cii.instance_number, cii.last_vld_organization_id inv_organization_id, cii.instance_id, (SELECT count(*) FROM mtl_object_genealogy mog1 WHERE mog1.parent_object_id = msn.gen_object_id AND nvl(mog1.start_date_active, SYSDATE-1) <= SYSDATE AND nvl(mog1.end_date_active, SYSDATE+1) >= SYSDATE) as no_of_children FROM mtl_parameters mp, csi_item_instances cii, mtl_categories_kfv mck, mtl_system_items_b_kfv msi, mtl_object_genealogy mog, mtl_serial_numbers msn, mtl_serial_numbers msn_parent WHERE mp.organization_id = cii.last_vld_organization_id AND cii.last_vld_organization_id = msi.organization_id AND msi.inventory_item_id = cii.inventory_item_id AND msi.eam_item_type in (1,3) AND nvl(cii.active_start_date, SYSDATE-1) <= SYSDATE AND nvl(cii.active_end_date, SYSDATE+1) >= SYSDATE AND msi.serial_number_control_code <> 1 AND msi.serial_number_control_code <>1 AND cii.category_id = mck.category_id(+) AND cii.inventory_item_id = msn.inventory_item_id AND cii.serial_number = msn.serial_number AND msn.gen_object_id = mog.object_id(+) AND mog.parent_object_id = msn_parent.gen_object_id(+) AND mog.genealogy_type(+) = 5 AND SYSDATE >= nvl(mog.start_date_active(+), SYSDATE) AND SYSDATE <= nvl(mog.end_date_active(+), SYSDATE)
SELECT CII.ROWID ROW_ID
, CII.INVENTORY_ITEM_ID
, CII.SERIAL_NUMBER
, CII.INSTANCE_DESCRIPTION AS DESCRIPTIVE_TEXT
, CII.NETWORK_ASSET_FLAG NETWORK_ASSET_FLAG
, MP.MAINT_ORGANIZATION_ID CURRENT_ORGANIZATION_ID
, MSI.CONCATENATED_SEGMENTS
, MSI.EAM_ITEM_TYPE
, MSI.DESCRIPTION ASSET_GROUP_DESCRIPTION
, MCK.CONCATENATED_SEGMENTS CATEGORY_NAME
, CII.ASSET_CRITICALITY_CODE ASSET_CRITICALITY
, NVL(CII.MAINTAINABLE_FLAG
, 'N') MAINTAINABLE_FLAG
, MSN_PARENT.INVENTORY_ITEM_ID PARENT_ITEM_ID
, MSN_PARENT.SERIAL_NUMBER PARENT_SERIAL_NUMBER
, MSN_PARENT.GEN_OBJECT_ID PARENT_OBJECT_ID
, MSN.GEN_OBJECT_ID
, MSN.CURRENT_STATUS
, CII.INSTANCE_NUMBER
, CII.LAST_VLD_ORGANIZATION_ID INV_ORGANIZATION_ID
, CII.INSTANCE_ID
, (SELECT COUNT(*)
FROM MTL_OBJECT_GENEALOGY MOG1
WHERE MOG1.PARENT_OBJECT_ID = MSN.GEN_OBJECT_ID
AND NVL(MOG1.START_DATE_ACTIVE
, SYSDATE-1) <= SYSDATE
AND NVL(MOG1.END_DATE_ACTIVE
, SYSDATE+1) >= SYSDATE) AS NO_OF_CHILDREN
FROM MTL_PARAMETERS MP
, CSI_ITEM_INSTANCES CII
, MTL_CATEGORIES_KFV MCK
, MTL_SYSTEM_ITEMS_B_KFV MSI
, MTL_OBJECT_GENEALOGY MOG
, MTL_SERIAL_NUMBERS MSN
, MTL_SERIAL_NUMBERS MSN_PARENT
WHERE MP.ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID
AND CII.LAST_VLD_ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
AND MSI.EAM_ITEM_TYPE IN (1
, 3)
AND NVL(CII.ACTIVE_START_DATE
, SYSDATE-1) <= SYSDATE
AND NVL(CII.ACTIVE_END_DATE
, SYSDATE+1) >= SYSDATE
AND MSI.SERIAL_NUMBER_CONTROL_CODE <> 1
AND MSI.SERIAL_NUMBER_CONTROL_CODE <>1
AND CII.CATEGORY_ID = MCK.CATEGORY_ID(+)
AND CII.INVENTORY_ITEM_ID = MSN.INVENTORY_ITEM_ID
AND CII.SERIAL_NUMBER = MSN.SERIAL_NUMBER
AND MSN.GEN_OBJECT_ID = MOG.OBJECT_ID(+)
AND MOG.PARENT_OBJECT_ID = MSN_PARENT.GEN_OBJECT_ID(+)
AND MOG.GENEALOGY_TYPE(+) = 5
AND SYSDATE >= NVL(MOG.START_DATE_ACTIVE(+)
, SYSDATE)
AND SYSDATE <= NVL(MOG.END_DATE_ACTIVE(+)
, SYSDATE)
|
|
|
|