FA Asset Distributions (Oracle Fusion SQL)

Enterprise Resource Planning (ERP) Oracle Fixed Assets (FA-Fusion-SQL's)
1

This FA Asset Distributions query displays current distribution details including account, location, and assigned employee for assets, helping users to monitor asset allocation and track responsibility efficiently.

#sqlquery

SELECT
  --SQL4Fusion (An Orbit Analytics Project)
  --Displays current asset distribution details including accounts, locations, and assigned employees.
  MAX(FDH.ASSET_ID) ASSET_ID,
  MAX(FDH.DISTRIBUTION_ID) DISTRIBUTION_ID,
  MAX(FBC.BOOK_TYPE_CODE) BOOK_TYPE_CODE,
  MAX(FDH.BOOK_TYPE_CODE) BOOK_TYPE_DISTRIBUTION_CODE,
  SUM(FDH.UNITS_ASSIGNED) UNITS_ASSIGNED,
  MAX(FDH.DATE_EFFECTIVE) DATE_EFFECTIVE,
  MAX(
    FND_FLEX_EXT.GET_SEGS(
      'GL',
      'GL#',
      GCC.CHART_OF_ACCOUNTS_ID,
      GCC.CODE_COMBINATION_ID
    )
  ) ACCOUNT,
  MAX(FL.SEGMENT1 || '.' || FL.SEGMENT2 || '.' || FL.SEGMENT3) LOCATION,
  MAX(PU.USERNAME) EMPLOYEE_NAME,
  MAX(PAPF.PERSON_NUMBER) EMPLOYEE_NUMBER
FROM
  FA_DISTRIBUTION_HISTORY FDH,
  FA_BOOK_CONTROLS FBC,
  FA_LOCATIONS FL,
  GL_CODE_COMBINATIONS GCC,
  PER_USERS PU,
  PER_ALL_PEOPLE_F PAPF
WHERE
  1 = 1
  AND PAPF.PERSON_ID = PU.PERSON_ID
  AND FDH.ASSIGNED_TO = PU.PERSON_ID
  AND GCC.CODE_COMBINATION_ID = FDH.CODE_COMBINATION_ID
  AND FDH.LOCATION_ID = FL.LOCATION_ID
  AND FDH.DATE_INEFFECTIVE IS NULL
  AND FDH.TRANSACTION_HEADER_ID_OUT IS NULL
  AND FDH.BOOK_TYPE_CODE = FBC.DISTRIBUTION_SOURCE_BOOK
GROUP BY
  ASSET_ID