This FA Asset Transfer Reconciliation query displays asset transfer and unit adjustment transactions for assets, helping users reconcile movements across books and accounts.
#sqlquery
SELECT
--SQL4Fusion (An Orbit Analytics Project)
--This query describes the tracks asset transfer and unit adjustment transactions.
-- Asset Information
FAB.ASSET_NUMBER,
FAB.PROPERTY_TYPE_CODE PROPERTY_TYPE,
FAB.IN_USE_FLAG IN_USE,
FAB.NEW_USED NEW_OR_USED,
FAB.SERIAL_NUMBER,
FAB.TAG_NUMBER,
FAB.MANUFACTURER_NAME,
FAB.INVENTORIAL,
--Adjustment information
FA.ADJUSTMENT_TYPE,
FA.SOURCE_TYPE_CODE ASSET_TRANSACTION_TYPE,
--Transaction Information
FTH.TRANSACTION_NAME,
FTH.TRANSACTION_DATE_ENTERED,
FTH.DATE_EFFECTIVE,
FTH.BOOK_TYPE_CODE BOOK_TYPE,
DECODE(
FA.DEBIT_CREDIT_FLAG,
'CR', 'OUT',
'DR', 'IN'
) IN_OUT,
DECODE( FA.DEBIT_CREDIT_FLAG,'DR', 1,'CR', -1, 0) COST,
-- Ledger Information
GL.NAME LEDGER_NAME,
GL.DESCRIPTION LEDGER_DESCRIPTION,
GL.ALC_LEDGER_TYPE_CODE REPORTING_CURRENY_TYPE,
--GL Accounting Info
DECODE(
FAH.ASSET_TYPE,
'CIP', FCB.WIP_COST_ACCOUNT_CCID,
FCB.ASSET_COST_ACCOUNT_CCID
) ACCOUNT,
FDP.PERIOD_NAME,
GCC.SEGMENT1,
GCC.SEGMENT2,
GCC.SEGMENT3,
GCC.SEGMENT4,
GCC.SEGMENT5,
GCC.SEGMENT6,
GCC.SEGMENT7,
GCC.SEGMENT8,
GCC.SEGMENT9,
GCC.SEGMENT10,
FND_FLEX_EXT.GET_SEGS(
'GL',
'GL#',
GCC.CHART_OF_ACCOUNTS_ID,
GCC.CODE_COMBINATION_ID
) GL_ACCOUNT,
-- Amount Details
SUM(FA.ADJUSTMENT_AMOUNT) TRANSFER_COST,
SUM(FA.ANNUALIZED_ADJUSTMENT) ANNUALIZED_ADJUSTMENT
FROM
FA_ADDITIONS_B FAB,
FA_TRANSACTION_HEADERS FTH,
FA_ADJUSTMENTS FA,
FA_DISTRIBUTION_HISTORY FDH,
FA_CATEGORY_BOOKS FCB,
FA_ASSET_HISTORY FAH,
FA_BOOK_CONTROLS FBC,
FA_DEPRN_PERIODS FDP,
GL_CODE_COMBINATIONS GCC,
GL_LEDGERS GL
WHERE
1 = 1
AND FTH.TRANSACTION_TYPE_CODE IN ('TRANSFER', 'UNIT ADJUSTMENT')
AND FA.ADJUSTMENT_TYPE IN ('COST', 'CIP COST')
AND ( FDH.DISTRIBUTION_ID IN ( SELECT DH1.DISTRIBUTION_ID
FROM FA_DISTRIBUTION_HISTORY DH1
WHERE ( DH1.TRANSACTION_HEADER_ID_OUT = FTH.TRANSACTION_HEADER_ID
OR DH1.TRANSACTION_HEADER_ID_IN = FTH.TRANSACTION_HEADER_ID
)
AND 1 = ( SELECT COUNT(DISTINCT DH2.CODE_COMBINATION_ID)
FROM FA_DISTRIBUTION_HISTORY DH2
WHERE ( DH2.TRANSACTION_HEADER_ID_OUT = FTH.TRANSACTION_HEADER_ID
OR DH2.TRANSACTION_HEADER_ID_IN = FTH.TRANSACTION_HEADER_ID)
)
)
OR FTH.TRANSACTION_TYPE_CODE = 'UNIT ADJUSTMENT' )
AND FA.SOURCE_TYPE_CODE = 'TRANSFER'
AND FA.TRANSACTION_HEADER_ID = FTH.TRANSACTION_HEADER_ID
AND FA.ASSET_ID = FTH.ASSET_ID
AND FA.BOOK_TYPE_CODE = FTH.BOOK_TYPE_CODE
AND FBC.BOOK_TYPE_CODE = FTH.BOOK_TYPE_CODE
AND GL.LEDGER_ID = FBC.SET_OF_BOOKS_ID
AND FDH.DISTRIBUTION_ID = FA.DISTRIBUTION_ID
AND GCC.CODE_COMBINATION_ID = FDH.CODE_COMBINATION_ID
AND FAH.ASSET_ID = FTH.ASSET_ID
AND FCB.CATEGORY_ID = FAH.CATEGORY_ID
AND FCB.BOOK_TYPE_CODE = FTH.BOOK_TYPE_CODE
AND FAB.ASSET_ID = FAH.ASSET_ID
AND FA.PERIOD_COUNTER_ADJUSTED = FDP.PERIOD_COUNTER
AND FTH.BOOK_TYPE_CODE = FDP.BOOK_TYPE_CODE
AND SYSDATE BETWEEN FTH.DATE_EFFECTIVE AND NVL(FAH.DATE_INEFFECTIVE, SYSDATE)
GROUP BY
FAB.ASSET_NUMBER,
FAB.ASSET_ID,
FAB.PROPERTY_TYPE_CODE,
FAB.IN_USE_FLAG,
FAB.NEW_USED,
FAB.SERIAL_NUMBER,
FAB.TAG_NUMBER,
FAB.MANUFACTURER_NAME,
FAB.INVENTORIAL,
FTH.TRANSACTION_NAME ,
FTH.TRANSACTION_DATE_ENTERED ,
FTH.DATE_EFFECTIVE,
FTH.BOOK_TYPE_CODE,
FA.ADJUSTMENT_TYPE,
FA.SOURCE_TYPE_CODE,
DECODE(
FA.DEBIT_CREDIT_FLAG,
'CR', 'OUT',
'DR', 'IN'
),
DECODE(
FAH.ASSET_TYPE,
'CIP', FCB.WIP_COST_ACCOUNT_CCID,
FCB.ASSET_COST_ACCOUNT_CCID
),
FDP.PERIOD_NAME,
GCC.SEGMENT1,
GCC.SEGMENT2,
GCC.SEGMENT3,
GCC.SEGMENT4,
GCC.SEGMENT5,
GCC.SEGMENT6,
GCC.SEGMENT7,
GCC.SEGMENT8,
GCC.SEGMENT9,
GCC.SEGMENT10,
GL.NAME,
GL.DESCRIPTION,
GL.ALC_LEDGER_TYPE_CODE,
FND_FLEX_EXT.GET_SEGS(
'GL',
'GL#',
GCC.CHART_OF_ACCOUNTS_ID,
GCC.CODE_COMBINATION_ID
) ,
DECODE( FA.DEBIT_CREDIT_FLAG,'DR', 1,'CR', -1, 0)