FA Mass Additions Status Details (Oracle Fusion SQL)

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

This FA Mass Additions Status Report query displays financial and status details of assets going through the mass addition process before capitalization, helping users monitor asset intake and ensure accurate cost assignment.

#sqlquery

SELECT
-- SQL4Fusion (An Orbit Analytics Project)
-- This query describes Track pre-capitalization asset details and costing in mass additions.
  --Asset Info
  FMA.ASSET_NUMBER,
  DECODE (
     FMA.ASSET_TYPE,
     'CIP',
     FCB.WIP_COST_ACCOUNT_CCID,
     FCB.ASSET_COST_ACCOUNT_CCID
   ) ASSET_ACCOUNT,
  FMA.DESCRIPTION ASSET_DESCRIPTION,
  FMA.ASSET_TYPE,
  FMA.PROPERTY_TYPE_CODE ASSET_TYPE_CODE,
  -- Mass Addition 
  FMA.INVOICE_NUMBER,
  FMA.VENDOR_NUMBER,
  FMA.PO_NUMBER,
  FMA.QUEUE_NAME,
  FMA.BOOK_TYPE_CODE BOOK_TYPE,
  FMA.PAYABLES_BATCH_NAME,
  -- Vendor Info
  PSV.VENDOR_NAME SUPPLIER_NAME,
  PSV.SEGMENT1 SUPPLIER_NUMBER,
  -- GL Info
  DECODE (
     FAD.DEPRN_EXPENSE_CCID, NULL, NULL,
     GCC.SEGMENT1
   ) GL_NUMBER,
   GCC.SEGMENT1 || '-' || GCC.SEGMENT2 || '-' || GCC.SEGMENT3 || '-' || GCC.SEGMENT4 || '-' || GCC.SEGMENT5 GENERAL_LEDGER_NUMBER,
  --Amount Info
  FMA.SUM_UNITS,
  FMA.FIXED_ASSETS_UNITS,
  (
    SELECT
      SUM (UNITS)
    FROM
      FA_MASSADD_DISTRIBUTIONS
    WHERE
      MASS_ADDITION_ID = FMA.MASS_ADDITION_ID
  ) SUM_OF_UNITS,
  ((FMA.FIXED_ASSETS_COST * FAD.UNITS)) COST
FROM
  FA_MASSADD_DISTRIBUTIONS FAD,
  FA_ADDITIONS_B FAB,
  FA_MASS_ADDITIONS FMA,
  FA_CATEGORY_BOOKS FCB,
  GL_CODE_COMBINATIONS GCC,
  POZ_SUPPLIERS_V PSV
WHERE
  1 = 1
  AND FAD.MASS_ADDITION_ID = FMA.MASS_ADDITION_ID(+)
  AND FAB.ASSET_ID = FMA.ASSET_ID
  AND FCB.CATEGORY_ID(+) = FMA.ASSET_CATEGORY_ID
  AND FCB.BOOK_TYPE_CODE(+) = FMA.BOOK_TYPE_CODE
  AND GCC.CODE_COMBINATION_ID(+) = FAD.DEPRN_EXPENSE_CCID
  AND PSV.VENDOR_ID(+) = FMA.PO_VENDOR_ID
ORDER BY  FMA.ASSET_NUMBER desc