VIEW: APPS.FA_HR_BATCH_DIST_OLD_V

Source

View Text - Preformatted

SELECT BD.BATCH_ID , BD.ATTRIBUTE_NAME , bd.ASSET_ID , BD.ATTRIBUTE_OLD_ID , BD.ATTRIBUTE_NEW_ID , FDH.DISTRIBUTION_ID , -1 , BD.BOOK_TYPE_CODE , FDH.UNITS_ASSIGNED , FDH.CODE_COMBINATION_ID , FA_CUA_ASSET_WB_APIS_pkg.get_account_code_name(FDH.CODE_COMBINATION_ID) , FDH.LOCATION_ID , FA_CUA_ASSET_WB_APIS_pkg.get_location_name(fdh.location_id) , FDH.ASSIGNED_TO , FE.EMPLOYEE_NUMBER , FE.NAME from FA_MASS_UPDATE_BATCH_DETAILS BD, FA_DISTRIBUTION_HISTORY FDH, FA_EMPLOYEES FE WHERE BD.status_code <> 'A' AND BD.ASSET_ID = FDH.ASSET_ID AND BD.ATTRIBUTE_NAME = 'DISTRIBUTION' AND FDH.DATE_INEFFECTIVE is NULL AND BD.BOOK_TYPE_CODE = FDH.BOOK_TYPE_CODE AND FDH.ASSIGNED_TO = FE.EMPLOYEE_ID(+) UNION SELECT BD.BATCH_ID , BD.ATTRIBUTE_NAME , bd.ASSET_ID , BD.ATTRIBUTE_OLD_ID , BD.ATTRIBUTE_NEW_ID , FDH.DISTRIBUTION_ID , -1 , BD.BOOK_TYPE_CODE , FDH.UNITS_ASSIGNED , FDH.CODE_COMBINATION_ID , FA_CUA_ASSET_WB_APIS_pkg.get_account_code_name(FDH.CODE_COMBINATION_ID) , FDH.LOCATION_ID , FA_CUA_ASSET_WB_APIS_pkg.get_location_name(fdh.location_id) , FDH.ASSIGNED_TO , FE.EMPLOYEE_NUMBER , FE.NAME from FA_MASS_UPDATE_BATCH_DETAILS BD, FA_DISTRIBUTION_HISTORY FDH, FA_EMPLOYEES FE where BD.status_code = 'A' AND BD.ASSET_ID = FDH.ASSET_ID AND BD.ATTRIBUTE_NAME = 'DISTRIBUTION' AND EXISTS ( select 'X' from fa_distribution_history dh where dh.asset_id = fdh.asset_id and dh.transaction_header_id_in = fdh.transaction_header_id_out and dh.date_ineffective is null ) AND BD.BOOK_TYPE_CODE = FDH.BOOK_TYPE_CODE AND FDH.ASSIGNED_TO = FE.EMPLOYEE_ID(+)

View Text - HTML Formatted

SELECT BD.BATCH_ID
, BD.ATTRIBUTE_NAME
, BD.ASSET_ID
, BD.ATTRIBUTE_OLD_ID
, BD.ATTRIBUTE_NEW_ID
, FDH.DISTRIBUTION_ID
, -1
, BD.BOOK_TYPE_CODE
, FDH.UNITS_ASSIGNED
, FDH.CODE_COMBINATION_ID
, FA_CUA_ASSET_WB_APIS_PKG.GET_ACCOUNT_CODE_NAME(FDH.CODE_COMBINATION_ID)
, FDH.LOCATION_ID
, FA_CUA_ASSET_WB_APIS_PKG.GET_LOCATION_NAME(FDH.LOCATION_ID)
, FDH.ASSIGNED_TO
, FE.EMPLOYEE_NUMBER
, FE.NAME
FROM FA_MASS_UPDATE_BATCH_DETAILS BD
, FA_DISTRIBUTION_HISTORY FDH
, FA_EMPLOYEES FE
WHERE BD.STATUS_CODE <> 'A'
AND BD.ASSET_ID = FDH.ASSET_ID
AND BD.ATTRIBUTE_NAME = 'DISTRIBUTION'
AND FDH.DATE_INEFFECTIVE IS NULL
AND BD.BOOK_TYPE_CODE = FDH.BOOK_TYPE_CODE
AND FDH.ASSIGNED_TO = FE.EMPLOYEE_ID(+) UNION SELECT BD.BATCH_ID
, BD.ATTRIBUTE_NAME
, BD.ASSET_ID
, BD.ATTRIBUTE_OLD_ID
, BD.ATTRIBUTE_NEW_ID
, FDH.DISTRIBUTION_ID
, -1
, BD.BOOK_TYPE_CODE
, FDH.UNITS_ASSIGNED
, FDH.CODE_COMBINATION_ID
, FA_CUA_ASSET_WB_APIS_PKG.GET_ACCOUNT_CODE_NAME(FDH.CODE_COMBINATION_ID)
, FDH.LOCATION_ID
, FA_CUA_ASSET_WB_APIS_PKG.GET_LOCATION_NAME(FDH.LOCATION_ID)
, FDH.ASSIGNED_TO
, FE.EMPLOYEE_NUMBER
, FE.NAME
FROM FA_MASS_UPDATE_BATCH_DETAILS BD
, FA_DISTRIBUTION_HISTORY FDH
, FA_EMPLOYEES FE
WHERE BD.STATUS_CODE = 'A'
AND BD.ASSET_ID = FDH.ASSET_ID
AND BD.ATTRIBUTE_NAME = 'DISTRIBUTION'
AND EXISTS ( SELECT 'X'
FROM FA_DISTRIBUTION_HISTORY DH
WHERE DH.ASSET_ID = FDH.ASSET_ID
AND DH.TRANSACTION_HEADER_ID_IN = FDH.TRANSACTION_HEADER_ID_OUT
AND DH.DATE_INEFFECTIVE IS NULL )
AND BD.BOOK_TYPE_CODE = FDH.BOOK_TYPE_CODE
AND FDH.ASSIGNED_TO = FE.EMPLOYEE_ID(+)