SELECT DISTINCT FAB.ASSET_NUMBER,
FAT.DESCRIPTION ASSET_DESCRIPTION,
FAB.MANUFACTURER_NAME,
FASI.VENDOR_NAME,
FASI.PO_NUMBER,
FASI.REQUESTER,
FAB.ATTRIBUTE_CATEGORY_CODE CATEGORY_DESCRIPTION,
FB.ORIGINAL_COST,
FAC.COST CURRENT_COST,
FB.DATE_PLACED_IN_SERVICE,
FM.LIFE_IN_MONTHS / 12 LIFE_IN_YEARS,
FAB.TAG_NUMBER,
DECODE(
FAB.IN_USE_FLAG, 'YES', 'IN USE',
'DISPOSED'
) ASSETS_CONDITION,
FAL.NUMBER_OF_UNITS,
FAL.LOCATION,
FAB.ATTRIBUTE2 STORAGE_BUILDING_ROOM,
FAB.SERIAL_NUMBER,
FAD.DEPRECIATION_RESERVATION,
FAC.COST - NVL(FAD.DEPRECIATION_RESERVATION, 0) NET_BOOK_VALUE,
FAH.ASSET_TYPE,
(
SELECT
DISTINCT SEGMENT1
FROM
GL_CODE_COMBINATIONS
WHERE
CODE_COMBINATION_ID = FAL.CODE_COMBINATION_ID
) COMPANY_CODE,
(
SELECT
DISTINCT SEGMENT2
FROM
GL_CODE_COMBINATIONS
WHERE
CODE_COMBINATION_ID = FAL.CODE_COMBINATION_ID
) COST_CENTER,
(
SELECT
SEGMENT3
FROM
GL_CODE_COMBINATIONS
WHERE
CODE_COMBINATION_ID = (
SELECT
FCB.ASSET_COST_ACCOUNT_CCID
FROM
FA_CATEGORY_BOOKS FCB,
FA_ADDITIONS_B FA,
FA_CATEGORIES_B FC
WHERE
FA.ASSET_CATEGORY_ID = FC.CATEGORY_ID
AND FC.CATEGORY_ID = FCB.CATEGORY_ID
AND FA.ASSET_ID = FAB.ASSET_ID
)
) ASSET_ACCOUNT,
(
CASE WHEN (
SELECT
PERIOD_COUNTER
FROM
FA_DEPRN_PERIODS
WHERE
PERIOD_NAME = : P_ACCOUNTING_PERIOD
AND BOOK_TYPE_CODE = FB.BOOK_TYPE_CODE
) IN (
SELECT
FDSS1.PERIOD_COUNTER
FROM
FA_DEPRN_SUMMARY FDSS1,
FA_DEPRN_PERIODS FDP1
WHERE
FDSS1.DEPRN_SOURCE_CODE = 'DEPRN'
AND FDSS1.ASSET_ID = FB.ASSET_ID
AND FDSS1.BOOK_TYPE_CODE = FB.BOOK_TYPE_CODE
AND FDSS1.PERIOD_COUNTER = FDP1.PERIOD_COUNTER
AND FDP1.BOOK_TYPE_CODE = FB.BOOK_TYPE_CODE
) THEN NVL(
(
SELECT
FDSS.YTD_DEPRN
FROM
FA_DEPRN_SUMMARY FDSS
WHERE
FDSS.DEPRN_SOURCE_CODE = 'DEPRN'
AND FDSS.ASSET_ID = FB.ASSET_ID
AND FDSS.PERIOD_COUNTER = (
SELECT
FDSS1.PERIOD_COUNTER
FROM
FA_DEPRN_SUMMARY FDSS1,
FA_DEPRN_PERIODS FDP1
WHERE
FDSS1.DEPRN_SOURCE_CODE = 'DEPRN'
AND FDSS1.ASSET_ID = FB.ASSET_ID
AND FDSS1.BOOK_TYPE_CODE = FB.BOOK_TYPE_CODE
AND FDSS1.PERIOD_COUNTER = FDP1.PERIOD_COUNTER
AND FDP1.BOOK_TYPE_CODE = FB.BOOK_TYPE_CODE
AND FDP1.PERIOD_NAME = : P_ACCOUNTING_PERIOD
)
AND FDSS.BOOK_TYPE_CODE = FB.BOOK_TYPE_CODE
),
0
) WHEN (
SELECT
PERIOD_COUNTER
FROM
FA_DEPRN_PERIODS
WHERE
PERIOD_NAME = : P_ACCOUNTING_PERIOD
AND BOOK_TYPE_CODE = FB.BOOK_TYPE_CODE
) > (
SELECT
MAX(FDSS1.PERIOD_COUNTER)
FROM
FA_DEPRN_SUMMARY FDSS1,
FA_DEPRN_PERIODS FDP1
WHERE
FDSS1.DEPRN_SOURCE_CODE = 'DEPRN'
AND FDSS1.ASSET_ID = FB.ASSET_ID
AND FDSS1.BOOK_TYPE_CODE = FB.BOOK_TYPE_CODE
AND FDSS1.PERIOD_COUNTER = FDP1.PERIOD_COUNTER
AND FDP1.BOOK_TYPE_CODE = FB.BOOK_TYPE_CODE
) THEN NVL(
(
SELECT
FDSS.YTD_DEPRN
FROM
FA_DEPRN_SUMMARY FDSS
WHERE
FDSS.DEPRN_SOURCE_CODE = 'DEPRN'
AND FDSS.ASSET_ID = FB.ASSET_ID
AND FDSS.PERIOD_COUNTER = (
SELECT
MAX(FDSS1.PERIOD_COUNTER)
FROM
FA_DEPRN_SUMMARY FDSS1,
FA_DEPRN_PERIODS FDP1
WHERE
FDSS1.DEPRN_SOURCE_CODE = 'DEPRN'
AND FDSS1.ASSET_ID = FB.ASSET_ID
AND FDSS1.BOOK_TYPE_CODE = FB.BOOK_TYPE_CODE
AND FDSS1.PERIOD_COUNTER = FDP1.PERIOD_COUNTER
AND FDP1.BOOK_TYPE_CODE = FB.BOOK_TYPE_CODE
AND SUBSTR(FDP1.PERIOD_NAME,-2, 2) = SUBSTR(
(: P_ACCOUNTING_PERIOD),
-2,
2
)
)
AND FDSS.BOOK_TYPE_CODE = FB.BOOK_TYPE_CODE
),
0
) ELSE 0 END
) YTD1,
(
SELECT
FDS.DEPRN_AMOUNT
FROM
FA_DEPRN_PERIODS FDP,
FA_DEPRN_SUMMARY FDS
WHERE
1 = 1
AND FDS.ASSET_ID = FAB.ASSET_ID
AND FDP.BOOK_TYPE_CODE = FDS.BOOK_TYPE_CODE
AND FB.BOOK_TYPE_CODE = FDS.BOOK_TYPE_CODE
AND FDP.PERIOD_COUNTER = FDS.PERIOD_COUNTER
AND FDS.DEPRN_SOURCE_CODE = 'DEPRN'
AND FDS.PERIOD_COUNTER = (
SELECT
FDP1.PERIOD_COUNTER
FROM
FA_DEPRN_PERIODS FDP1
WHERE
FDP1.PERIOD_NAME = : P_ACCOUNTING_PERIOD
AND FDS.BOOK_TYPE_CODE = FDP1.BOOK_TYPE_CODE
)
) "CURRENT_DEPRECIATION",
(
SELECT
SUM(FDS.DEPRN_AMOUNT)
FROM
FA_DEPRN_PERIODS FDP,
FA_DEPRN_SUMMARY FDS
WHERE
1 = 1
AND FDS.ASSET_ID = FAB.ASSET_ID
AND FDP.BOOK_TYPE_CODE = FDS.BOOK_TYPE_CODE
AND FB.BOOK_TYPE_CODE = FDS.BOOK_TYPE_CODE
AND FDP.PERIOD_COUNTER = FDS.PERIOD_COUNTER
AND FDS.DEPRN_SOURCE_CODE = 'DEPRN'
AND FDS.PERIOD_COUNTER IN (
SELECT
FDP1.PERIOD_COUNTER
FROM
FA_DEPRN_PERIODS FDP1
WHERE
FDP1.PERIOD_NAME IN (
SELECT
PERIOD_NAME
FROM
GL_PERIODS
WHERE
(PERIOD_YEAR, QUARTER_NUM) = (
SELECT
GP1.PERIOD_YEAR,
GP1.QUARTER_NUM
FROM
GL_PERIODS GP1
WHERE
GP1.PERIOD_NAME = : P_ACCOUNTING_PERIOD
)
AND ADJUSTMENT_PERIOD_FLAG <> 'Y'
AND PERIOD_NUM <= (
SELECT
PERIOD_NUM
FROM
GL_PERIODS
WHERE
PERIOD_NAME = : P_ACCOUNTING_PERIOD
)
)
AND FDS.BOOK_TYPE_CODE = FDP1.BOOK_TYPE_CODE
)
) "QTD_DEPRECIATION",
(
SELECT
DISTINCT GCC11.SEGMENT1 || '-' || GCC11.SEGMENT2 || '-' || GCC11.SEGMENT3 || '-' || GCC11.SEGMENT4 || '-' || GCC11.SEGMENT5 || '-' || GCC11.SEGMENT6 || '-' || GCC11.SEGMENT7 DEPRN_EXP_ACCOUNT
FROM
GL_CODE_COMBINATIONS GCC11,
FA_DISTRIBUTION_HISTORY FDH2
WHERE
1 = 1
AND FAB.ASSET_ID = FDH2.ASSET_ID
AND FDH2.DATE_INEFFECTIVE IS NULL
AND FDH2.BOOK_TYPE_CODE = FB.BOOK_TYPE_CODE
AND GCC11.CODE_COMBINATION_ID = FDH2.CODE_COMBINATION_ID
) DEPRN_EXP_ACCOUNT
FROM
FA_ADDITIONS_B FAB,
FA_ADDITIONS_TL FAT,
(
SELECT
FAB.ASSET_ID,
(
SELECT
COST
FROM
(
SELECT
FB.COST
FROM
FA_TRANSACTION_HEADERS FTH,
FA_BOOKS FB,
(
SELECT
MIN(CALENDAR_PERIOD_CLOSE_DATE) TO_PERIOD_CLOSE_DATE
FROM
FA_DEPRN_PERIODS
WHERE
PERIOD_NAME =: P_ACCOUNTING_PERIOD
) FAP
WHERE
1 = 1
AND FTH.ASSET_ID = FAB.ASSET_ID
AND FTH.TRANSACTION_TYPE_CODE IN (
'ADDITION', 'REINSTATEMENT', 'FULL RETIREMENT',
'ADDITION/VOID', 'ADJUSTMENT', 'CIP ADDITION',
'CIP REVERSE', 'CIP ADJUSTMENT'
)
AND FTH.TRANSACTION_DATE_ENTERED <= FAP.TO_PERIOD_CLOSE_DATE
AND FTH.TRANSACTION_HEADER_ID = FB.TRANSACTION_HEADER_ID_IN
ORDER BY
FTH.TRANSACTION_HEADER_ID DESC
)
WHERE
ROWNUM = 1
) COST
FROM
FA_ADDITIONS_B FAB
) FAC, --COST
(
SELECT
FB.*
FROM
FA_BOOKS FB
WHERE
FB.TRANSACTION_HEADER_ID_IN =(
SELECT
MAX(TRANSACTION_HEADER_ID_IN)
FROM
FA_BOOKS FB1
WHERE
FB1.ASSET_ID = FB.ASSET_ID
AND FB1.BOOK_TYPE_CODE = FB.BOOK_TYPE_CODE
)
AND FB.DATE_INEFFECTIVE IS NULL
) FB, --METHOD
FA_METHODS FM,
(
SELECT
ASSET_ID,
LISTAGG(VENDOR_NAME, ', ') WITHIN GROUP (
ORDER BY
PO_NUMBER
) VENDOR_NAME,
LISTAGG(PO_NUMBER, ', ') WITHIN GROUP (
ORDER BY
PO_NUMBER
) PO_NUMBER,
LISTAGG(REQUESTER, ', ') WITHIN GROUP (
ORDER BY
PO_NUMBER
) REQUESTER
FROM
(
SELECT
DISTINCT FAI.ASSET_ID,
PSV.VENDOR_NAME,
FAI.PO_NUMBER,
PPNF.DISPLAY_NAME REQUESTER
FROM
FA_ASSET_INVOICES FAI,
AP_INVOICE_DISTRIBUTIONS_ALL AIDA,
PO_DISTRIBUTIONS_ALL PDA,
PER_PERSON_NAMES_F PPNF,
POZ_SUPPLIERS_V PSV
WHERE
1 = 1
AND FAI.INVOICE_LINE_TYPE(+) = 'ITEM'
AND AIDA.INVOICE_DISTRIBUTION_ID(+) = FAI.INVOICE_DISTRIBUTION_ID
AND PDA.PO_DISTRIBUTION_ID(+) = AIDA.PO_DISTRIBUTION_ID
AND PPNF.PERSON_ID(+) = PDA.DELIVER_TO_PERSON_ID
AND PPNF.NAME_TYPE(+) = 'GLOBAL'
AND SYSDATE BETWEEN PPNF.EFFECTIVE_START_DATE(+)
AND PPNF.EFFECTIVE_END_DATE(+)
AND PSV.VENDOR_ID(+) = FAI.PO_VENDOR_ID
)
GROUP BY
ASSET_ID
) FASI, --FIXED ASSET SOURCE INFO
(
SELECT
ASSET_ID,
LISTAGG(NUMBER_OF_UNITS, ', ') WITHIN GROUP (
ORDER BY
LOCATION
) NUMBER_OF_UNITS,
LISTAGG(LOCATION, ', ') WITHIN GROUP (
ORDER BY
LOCATION
) LOCATION,
CODE_COMBINATION_ID
FROM
(
SELECT
FDH.ASSET_ID,
GCC.CODE_COMBINATION_ID,
SUM(FDH.UNITS_ASSIGNED) NUMBER_OF_UNITS,
FALO.SEGMENT1 || '.' || FALO.SEGMENT2 || '.' || FALO.SEGMENT3 || '.' || FALO.SEGMENT4 LOCATION
FROM
FA_DISTRIBUTION_HISTORY FDH,
FA_LOCATIONS FALO,
GL_CODE_COMBINATIONS GCC
WHERE
1 = 1
AND NVL (
FDH.DATE_INEFFECTIVE(+),
SYSDATE + 1
) > SYSDATE
AND FDH.TRANSACTION_HEADER_ID_OUT IS NULL
AND FALO.LOCATION_ID = FDH.LOCATION_ID
AND GCC.CODE_COMBINATION_ID = FDH.CODE_COMBINATION_ID
AND GCC.SEGMENT1 IN (: P_COMPANY_CODE)
GROUP BY
FDH.ASSET_ID,
FALO.SEGMENT1,
FALO.SEGMENT2,
FALO.SEGMENT3,
FALO.SEGMENT4,
GCC.CODE_COMBINATION_ID
)
GROUP BY
ASSET_ID,
CODE_COMBINATION_ID
) FAL, --FIXED ASSET LOCATIONS
(
SELECT
FDS.ASSET_ID,
FDS.DEPRN_RESERVE DEPRECIATION_RESERVATION
FROM
FA_DEPRN_SUMMARY FDS,
(
SELECT
FDS.ASSET_ID,
MAX(FDS.DEPRN_RUN_DATE) MAX_DEPRN_RUN_DATE
FROM
FA_DEPRN_SUMMARY FDS,
(
SELECT
MIN(PERIOD_COUNTER) TO_PERIOD_COUNTER
FROM
FA_DEPRN_PERIODS
WHERE
PERIOD_NAME =: P_ACCOUNTING_PERIOD
) FTP --TO PERIOD
WHERE
FDS.PERIOD_COUNTER <= FTP.TO_PERIOD_COUNTER
GROUP BY
FDS.ASSET_ID
ORDER BY
FDS.ASSET_ID
) FMDR --MAX DEPRECIATION RUN
WHERE
1 = 1
AND FDS.ASSET_ID = FMDR.ASSET_ID
AND FDS.DEPRN_RUN_DATE = FMDR.MAX_DEPRN_RUN_DATE
) FAD, --DEPRECIATION
FA_TRANSACTION_HEADERS FTH,
FA_DEPRN_PERIODS FDP,
FA_ASSET_HISTORY FAH
WHERE
1 = 1
AND FAT.ASSET_ID = FAB.ASSET_ID
AND FAT.LANGUAGE = USERENV('LANG')
AND FAC.ASSET_ID(+)= FAB.ASSET_ID
AND FB.ASSET_ID(+)= FAB.ASSET_ID
AND FM.METHOD_ID(+)= FB.METHOD_ID
and FASI.ASSET_ID(+) = FAB.ASSET_ID
AND FAL.ASSET_ID(+) = FAB.ASSET_ID
AND FAD.ASSET_ID(+) = FAB.ASSET_ID
AND FAH.ASSET_ID(+) = FAB.ASSET_ID
AND FAH.BOOK_TYPE_CODE(+)= FB.BOOK_TYPE_CODE
AND FAH.DATE_INEFFECTIVE is null
AND FTH.ASSET_ID = FAB.ASSET_ID
AND FTH.TRANSACTION_TYPE_CODE IN ('ADDITION')
AND FTH.BOOK_TYPE_CODE = FDP.BOOK_TYPE_CODE
AND FTH.DATE_EFFECTIVE BETWEEN FDP.PERIOD_OPEN_DATE AND NVL(FDP.PERIOD_CLOSE_DATE, SYSDATE)
-- Asset addition period should be less than Accounting_period parameter
AND FDP.PERIOD_OPEN_DATE <= (
SELECT
MIN(PERIOD_OPEN_DATE) TO_PERIOD_CLOSE_DATE
FROM
FA_DEPRN_PERIODS
WHERE
PERIOD_NAME =: P_ACCOUNTING_PERIOD
)
ORDER BY
FAB.ASSET_NUMBER
