VIEW: APPS.FA_HIERARCHY_BATCH_DETAILS_V

Source

View Text - Preformatted

SELECT decode(bd.attribute_name,'CATEGORY',1, 'LEASE_NUMBER',2, 'SERIAL_NUMBER',3, 'ASSET_KEY',4, 'DISTRIBUTION',5, 'LIFE_END_DATE',6) ,bd.batch_id ,bd.book_type_code ,bd.attribute_name ,fl1.meaning ,bd.asset_id ,ad.asset_number ,bd.attribute_old_id ,decode(bd.attribute_name,'CATEGORY',FA_CUA_ASSET_WB_APIS_PKG.get_category_name(to_number(bd.attribute_old_id)), 'LEASE_NUMBER',FA_CUA_ASSET_WB_APIS_PKG.get_lease_number(to_number(bd.attribute_old_id)), 'SERIAL_NUMBER',bd.attribute_old_id, 'ASSET_KEY',FA_CUA_ASSET_WB_APIS_PKG.get_asset_key_name(to_number(bd.attribute_old_id)), 'DISTRIBUTION',null, 'LIFE_END_DATE',bd.attribute_old_id) ,bd.attribute_new_id ,decode(bd.attribute_name,'CATEGORY',FA_CUA_ASSET_WB_APIS_PKG.get_category_name(to_number(bd.attribute_new_id)), 'LEASE_NUMBER',FA_CUA_ASSET_WB_APIS_PKG.get_lease_number(to_number(bd.attribute_new_id)), 'SERIAL_NUMBER',bd.attribute_new_id, 'ASSET_KEY',FA_CUA_ASSET_WB_APIS_PKG.get_asset_key_name(to_number(bd.attribute_new_id)), 'DISTRIBUTION',null, 'LIFE_END_DATE',bd.attribute_new_id) ,bd.derived_from_entity ,fl2.meaning||decode(bd.derived_from_entity,'NODE',':LEVEL-'||FA_CUA_ASSET_WB_APIS_PKG.get_node_level(bd.derived_from_entity_id) ,'NODE-P',':LEVEL-'||FA_CUA_ASSET_WB_APIS_PKG.get_node_level(bd.derived_from_entity_id) ,null) ,bd.derived_from_entity_id ,decode(bd.derived_from_entity, 'NODE',FA_CUA_ASSET_WB_APIS_PKG.get_node_name(bd.derived_from_entity_id), 'NODE-P',FA_CUA_ASSET_WB_APIS_PKG.get_node_name(bd.derived_from_entity_id), 'CATEGORY-LIFE',FA_CUA_ASSET_WB_APIS_PKG.get_category_name(bd.derived_from_entity_id), 'CATEGORY-LED',FA_CUA_ASSET_WB_APIS_PKG.get_category_name(bd.derived_from_entity_id), 'LEASE',FA_CUA_ASSET_WB_APIS_PKG.get_lease_number(bd.derived_from_entity_id), 'ASSET',FA_CUA_ASSET_WB_APIS_PKG.get_asset_number(bd.derived_from_entity_id), null ) ,bd.created_by ,bd.creation_date ,bd.last_updated_by ,bd.last_update_date ,bd.last_update_login ,bd.status_code ,fl.meaning ,bd.rejection_reason ,bd.apply_flag ,bd.effective_date ,bd.fa_period_name ,bd.concurrent_request_id ,bd.parent_hierarchy_id from fa_mass_update_batch_details bd ,fa_additions ad ,fa_lookups fl ,fa_lookups fl1 ,fa_lookups fl2 WHERE ad.asset_id = bd.asset_id and (bd.status_code = fl.lookup_code (+) AND fl.lookup_type (+) = 'IFA_HR_STATUS_CODE') and (bd.attribute_name = fl1.lookup_code (+) AND fl1.lookup_type (+) = 'IFA_HR_SRC_ATTRIBUTE_NAME') and (bd.derived_from_entity = fl2.lookup_code (+) AND fl2.lookup_type (+) = 'IFA_HR_SRC_ENTITY_NAME')

View Text - HTML Formatted

SELECT DECODE(BD.ATTRIBUTE_NAME
, 'CATEGORY'
, 1
, 'LEASE_NUMBER'
, 2
, 'SERIAL_NUMBER'
, 3
, 'ASSET_KEY'
, 4
, 'DISTRIBUTION'
, 5
, 'LIFE_END_DATE'
, 6)
, BD.BATCH_ID
, BD.BOOK_TYPE_CODE
, BD.ATTRIBUTE_NAME
, FL1.MEANING
, BD.ASSET_ID
, AD.ASSET_NUMBER
, BD.ATTRIBUTE_OLD_ID
, DECODE(BD.ATTRIBUTE_NAME
, 'CATEGORY'
, FA_CUA_ASSET_WB_APIS_PKG.GET_CATEGORY_NAME(TO_NUMBER(BD.ATTRIBUTE_OLD_ID))
, 'LEASE_NUMBER'
, FA_CUA_ASSET_WB_APIS_PKG.GET_LEASE_NUMBER(TO_NUMBER(BD.ATTRIBUTE_OLD_ID))
, 'SERIAL_NUMBER'
, BD.ATTRIBUTE_OLD_ID
, 'ASSET_KEY'
, FA_CUA_ASSET_WB_APIS_PKG.GET_ASSET_KEY_NAME(TO_NUMBER(BD.ATTRIBUTE_OLD_ID))
, 'DISTRIBUTION'
, NULL
, 'LIFE_END_DATE'
, BD.ATTRIBUTE_OLD_ID)
, BD.ATTRIBUTE_NEW_ID
, DECODE(BD.ATTRIBUTE_NAME
, 'CATEGORY'
, FA_CUA_ASSET_WB_APIS_PKG.GET_CATEGORY_NAME(TO_NUMBER(BD.ATTRIBUTE_NEW_ID))
, 'LEASE_NUMBER'
, FA_CUA_ASSET_WB_APIS_PKG.GET_LEASE_NUMBER(TO_NUMBER(BD.ATTRIBUTE_NEW_ID))
, 'SERIAL_NUMBER'
, BD.ATTRIBUTE_NEW_ID
, 'ASSET_KEY'
, FA_CUA_ASSET_WB_APIS_PKG.GET_ASSET_KEY_NAME(TO_NUMBER(BD.ATTRIBUTE_NEW_ID))
, 'DISTRIBUTION'
, NULL
, 'LIFE_END_DATE'
, BD.ATTRIBUTE_NEW_ID)
, BD.DERIVED_FROM_ENTITY
, FL2.MEANING||DECODE(BD.DERIVED_FROM_ENTITY
, 'NODE'
, ':LEVEL-'||FA_CUA_ASSET_WB_APIS_PKG.GET_NODE_LEVEL(BD.DERIVED_FROM_ENTITY_ID)
, 'NODE-P'
, ':LEVEL-'||FA_CUA_ASSET_WB_APIS_PKG.GET_NODE_LEVEL(BD.DERIVED_FROM_ENTITY_ID)
, NULL)
, BD.DERIVED_FROM_ENTITY_ID
, DECODE(BD.DERIVED_FROM_ENTITY
, 'NODE'
, FA_CUA_ASSET_WB_APIS_PKG.GET_NODE_NAME(BD.DERIVED_FROM_ENTITY_ID)
, 'NODE-P'
, FA_CUA_ASSET_WB_APIS_PKG.GET_NODE_NAME(BD.DERIVED_FROM_ENTITY_ID)
, 'CATEGORY-LIFE'
, FA_CUA_ASSET_WB_APIS_PKG.GET_CATEGORY_NAME(BD.DERIVED_FROM_ENTITY_ID)
, 'CATEGORY-LED'
, FA_CUA_ASSET_WB_APIS_PKG.GET_CATEGORY_NAME(BD.DERIVED_FROM_ENTITY_ID)
, 'LEASE'
, FA_CUA_ASSET_WB_APIS_PKG.GET_LEASE_NUMBER(BD.DERIVED_FROM_ENTITY_ID)
, 'ASSET'
, FA_CUA_ASSET_WB_APIS_PKG.GET_ASSET_NUMBER(BD.DERIVED_FROM_ENTITY_ID)
, NULL )
, BD.CREATED_BY
, BD.CREATION_DATE
, BD.LAST_UPDATED_BY
, BD.LAST_UPDATE_DATE
, BD.LAST_UPDATE_LOGIN
, BD.STATUS_CODE
, FL.MEANING
, BD.REJECTION_REASON
, BD.APPLY_FLAG
, BD.EFFECTIVE_DATE
, BD.FA_PERIOD_NAME
, BD.CONCURRENT_REQUEST_ID
, BD.PARENT_HIERARCHY_ID
FROM FA_MASS_UPDATE_BATCH_DETAILS BD
, FA_ADDITIONS AD
, FA_LOOKUPS FL
, FA_LOOKUPS FL1
, FA_LOOKUPS FL2
WHERE AD.ASSET_ID = BD.ASSET_ID
AND (BD.STATUS_CODE = FL.LOOKUP_CODE (+)
AND FL.LOOKUP_TYPE (+) = 'IFA_HR_STATUS_CODE')
AND (BD.ATTRIBUTE_NAME = FL1.LOOKUP_CODE (+)
AND FL1.LOOKUP_TYPE (+) = 'IFA_HR_SRC_ATTRIBUTE_NAME')
AND (BD.DERIVED_FROM_ENTITY = FL2.LOOKUP_CODE (+)
AND FL2.LOOKUP_TYPE (+) = 'IFA_HR_SRC_ENTITY_NAME')