*******************
*Fixed Asset Books*
*******************
select
BOOK_TYPE_CODE Name,
BOOK_TYPE_NAME Description,
BOOK_CLASS,
MASS_COPY_SOURCE_BOOK Associated_Corp_Book,
b.NAME Ledger,
DEPRN_CALENDAR,
FISCAL_YEAR_NAME,
PRORATE_CALENDAR,
CURRENT_FISCAL_YEAR,
DEPRN_ALLOCATION_CODE Divide_Depr,
DEPR_FIRST_YEAR_RET_FLAG Depr_If_Ret_in_First_year,
AMORTIZE_FLAG Allow_Amort_Changes,
ALLOW_COST_SIGN_CHANGE_FLAG Allow_Cost_sing_Change,
ALLOW_IMPAIRMENT_FLAG,
GL_POSTING_ALLOWED_FLAG Allow_Ledger_Posting,
ALLOW_PHYSICAL_INVENTORY_FLAG,
ALLOW_LEASES_FLAG,
USE_NBV_THRESHOLD_FLAG,
ADDITIONS_APPROVAL_FLAG,
ADJUSTMENTS_APPROVAL_FLAG,
TRANSFERS_APPROVAL_FLAG,
RETIREMENTS_APPROVAL_FLAG,
CAPITAL_GAIN_THRESHOLD Capital_Gain_Threshold_Months,
DATE_INEFFECTIVE,
ROUND_ANNUAL_DEPRECIATION,
c.CONCATENATED_SEGMENTS Account_Details,
NBV_RETIRED_GAIN_ACCT,
NBV_RETIRED_LOSS_ACCT,
PROCEEDS_OF_SALE_GAIN_ACCT,
PROCEEDS_OF_SALE_LOSS_ACCT
PROCEEDS_OF_SALE_CLEARING_ACCT,
COST_OF_REMOVAL_GAIN_ACCT,
COST_OF_REMOVAL_LOSS_ACCT,
COST_OF_REMOVAL_CLEARING_ACCT,
DEFERRED_DEPRN_EXPENSE_ACCT,
DEFERRED_DEPRN_RESERVE_ACCT,
DEFAULT_DPIS_TO_INV_DATE_FLAG Use_Payables_Inv_Dt_as_DPIS,
ALLOW_UNALLOCATED_LINES_FLAG,
SUPPRESS_INV_AUTO_MERGE_FLAG
AP_CLEARING_NA_CHECK_FLAG,
AP_CLEARING_BSV_CHECK_FLAG,
AP_CLEARING_CC_CHECK_FLAG,
CAP_THRESHOLD_TYPE,
CAP_THRESHOLD_AMOUNT,
LOW_VALUE_THRESHOLD_AMOUNT
from
FA_BOOK_CONTROLS a,
GL_LEdgers b,
GL_CODE_COMBINATIONS C
where
a.SET_OF_BOOKS_ID = B.LEDGER_ID
and a.FLEXBUILDER_DEFAULTS_CCID = c.CODE_COMBINATION_ID (+)
*************************
*Fixed Asset Cagtegories*
*************************
select
a.segment1 major_category,
a.segment2 minor_category,
b.description,
a.CATEGORY_TYPE,
a.owned_Leased,
a.PROPERTY_TYPE_CODE,
PROPERTY_1245_1250_CODE property_class,
a.CAPITALIZE_FLAG,
a.enabled_flag,
a.INTANGIBLE_ASSET_FLAG,
a.INVENTORIAL,
a.START_DATE_ACTIVE,
a.END_DATE_ACTIVE
from
FA_CATEGORIES_B A,
FA_CATEGORIES_TL B
where a.category_id = b.category_id
and b.LANGUAGE = 'US'
******************************
*Fixed Asset Cagtegories Book*
******************************
select
a.segment1 major_category,
a.segment2 minor_category,
b.BOOK_TYPE_CODE,
b.DEFAULT_EXPENSE_CCID_FLAG,
c.CONCATENATED_SEGMENTS Asset_Cost_Account,
d.CONCATENATED_SEGMENTS Asset_Clearing_Account,
e.CONCATENATED_SEGMENTS Asset_WIP_Cost_Account,
f.CONCATENATED_SEGMENTS Asset_WIP_Clearing_Account,
g.CONCATENATED_SEGMENTS Asset_Depr_Exp_Account,
h.CONCATENATED_SEGMENTS Asset_Depr_Res_Account,
i.CONCATENATED_SEGMENTS Asset_Bon_Depr_Exp_Account,
j.CONCATENATED_SEGMENTS Asset_Bon_Depr_Res_Account,
k.CONCATENATED_SEGMENTS Asset_Reval_Amort_Account,
l.CONCATENATED_SEGMENTS Asset_Reval_Res_Account,
m.CONCATENATED_SEGMENTS Asset_Unplann_exp_Account,
n.CONCATENATED_SEGMENTS Asset_Alt_Cost_Account,
o.CONCATENATED_SEGMENTS Asset_Write_off_Account,
p.CONCATENATED_SEGMENTS Asset_Imp_Exp_Account,
q.CONCATENATED_SEGMENTS Asset_Imp_Res_Account,
r.CONCATENATED_SEGMENTS Asset_Cap_Adj_Account,
s.CONCATENATED_SEGMENTS Asset_Gen_Fund_Account,
u.CONCATENATED_SEGMENTS Asset_Reval_Loss_Account,
v.CONCATENATED_SEGMENTS Asset_COST_WRITE_OFF_Account,
w.CONCATENATED_SEGMENTS Asset_LEASE_LIABILITY_Account,
x.CONCATENATED_SEGMENTS Asset_LEASE_INT_EXPENSE_Account,
y.CONCATENATED_SEGMENTS Asset_LEASE_EXPENSE_Account,
z.CONCATENATED_SEGMENTS Asset_LEASE_GAIN_LOSS_Account,
aa.CONCATENATED_SEGMENTS Asset_LEASE_CLEARING_Account,
ab.CONCATENATED_SEGMENTS Asset_REVALUED_COST_Account,
ac.CONCATENATED_SEGMENTS Asset_ACKLOG_DEPRN_RSV_Account,
t.CONCATENATED_SEGMENTS Asset_ANN_INT_EXP_Account,
ad.CONCATENATED_SEGMENTS Asset_ANN_INT_REV_Account
from
FA_CATEGORIES_B A,
FA_CATEGORY_BOOKS B,
GL_CODE_COMBINATIONS C,
GL_CODE_COMBINATIONS D,
GL_CODE_COMBINATIONS E,
GL_CODE_COMBINATIONS F,
GL_CODE_COMBINATIONS G,
GL_CODE_COMBINATIONS H,
GL_CODE_COMBINATIONS I,
GL_CODE_COMBINATIONS J,
GL_CODE_COMBINATIONS k,
GL_CODE_COMBINATIONS l,
GL_CODE_COMBINATIONS m,
GL_CODE_COMBINATIONS n,
GL_CODE_COMBINATIONS o,
GL_CODE_COMBINATIONS p,
GL_CODE_COMBINATIONS q,
GL_CODE_COMBINATIONS r,
GL_CODE_COMBINATIONS s,
GL_CODE_COMBINATIONS t,
GL_CODE_COMBINATIONS u,
GL_CODE_COMBINATIONS v,
GL_CODE_COMBINATIONS w,
GL_CODE_COMBINATIONS x,
GL_CODE_COMBINATIONS y,
GL_CODE_COMBINATIONS z,
GL_CODE_COMBINATIONS aa,
GL_CODE_COMBINATIONS ab,
GL_CODE_COMBINATIONS ac,
GL_CODE_COMBINATIONS ad
Where a.category_id = b.category_id
and b.ASSET_COST_ACCOUNT_CCID = c.CODE_COMBINATION_ID (+)
and b.ASSET_CLEARING_ACCOUNT_CCID = D.CODE_COMBINATION_ID (+)
and b.WIP_COST_ACCOUNT_CCID = E.CODE_COMBINATION_ID (+)
and b.WIP_CLEARING_ACCOUNT_CCID = f.CODE_COMBINATION_ID (+)
and b.DEPRN_EXPENSE_ACCOUNT_CCID = g.CODE_COMBINATION_ID (+)
and b.RESERVE_ACCOUNT_CCID = h.CODE_COMBINATION_ID (+)
and b.BONUS_EXPENSE_ACCOUNT_CCID = i.CODE_COMBINATION_ID (+)
and b.BONUS_RESERVE_ACCT_CCID = j.CODE_COMBINATION_ID (+)
and b.REVAL_AMORT_ACCOUNT_CCID = k.CODE_COMBINATION_ID (+)
and b.REVAL_RESERVE_ACCOUNT_CCID = l.CODE_COMBINATION_ID (+)
and b.UNPLAN_EXPENSE_ACCOUNT_CCID = m.CODE_COMBINATION_ID (+)
and b.ALT_COST_ACCOUNT_CCID= n.CODE_COMBINATION_ID (+)
and b.WRITE_OFF_ACCOUNT_CCID= o.CODE_COMBINATION_ID (+)
and b.IMPAIR_EXPENSE_ACCOUNT_CCID= p.CODE_COMBINATION_ID (+)
and b.IMPAIR_RESERVE_ACCOUNT_CCID= q.CODE_COMBINATION_ID (+)
and b.CAPITAL_ADJ_ACCOUNT_CCID= r.CODE_COMBINATION_ID (+)
and b.GENERAL_FUND_ACCOUNT_CCID= s.CODE_COMBINATION_ID (+)
and b.REVAL_LOSS_ACCOUNT_CCID = u.CODE_COMBINATION_ID (+)
and b.COST_WRITE_OFF_ACCOUNT_CCID = v.CODE_COMBINATION_ID (+)
and b.LEASE_LIABILITY_ACCOUNT_CCID = w.CODE_COMBINATION_ID (+)
and b.LEASE_INT_EXPENSE_ACCOUNT_CCID= x.CODE_COMBINATION_ID (+)
and b.LEASE_EXPENSE_ACCOUNT_CCID= y.CODE_COMBINATION_ID (+)
and b.LEASE_GAIN_LOSS_ACCOUNT_CCID= z.CODE_COMBINATION_ID (+)
and b.LEASE_CLEARING_ACCOUNT_CCID= aa.CODE_COMBINATION_ID (+)
and b.REVALUED_COST_ACCOUNT_CCID= ab.CODE_COMBINATION_ID (+)
and b.BACKLOG_DEPRN_RSV_ACCT_CCID= ac.CODE_COMBINATION_ID (+)
and b.ANNUITY_INT_EXP_ACCOUNT_CCID = t.CODE_COMBINATION_ID (+)
and b.ANNUITY_INT_REV_ACCOUNT_CCID = ad.CODE_COMBINATION_ID (+)
***************************************
*Fixed Asset Cagtegories Book Defaults*
***************************************
select
a.segment1 major_category,
a.segment2 minor_category,
b.BOOK_TYPE_CODE,
to_char(b.START_DPIS,'MM/DD/YYYY') as START_DPIS,
to_char(b.END_DPIS,'MM/DD/YYYY') as END_DPIS,
b.DEPRECIATE_FLAG,
--b.METHOD_ID,
c.name DEPR_METHOD,
c.LIFE_IN_MONTHS,
b.DEPRN_LIMIT_TYPE,
b.ALLOWED_DEPRN_LIMIT,
b.BONUS_RULE_ID,
b.CONVENTION_TYPE_ID,
d.PRORATE_CONVENTION_CODE PRORATE_CONV_TYPE,
b.RETIREMENT_CONVENTION_TYPE_ID,
e.PRORATE_CONVENTION_CODE RETIRE_CONV_TYPE,
b.PERCENT_SALVAGE_VALUE,
b.FLAT_RATE_ID,
b.CEILING_TYPE_ID,
b.SPECIAL_DEPRN_LIMIT_AMOUNT,
b.CAPITAL_GAIN_THRESHOLD,
b.PRICE_INDEX_ID,
b.MASS_PROPERTY_FLAG,
b.SUBCOMPONENT_LIFE_RULE,
b.MINIMUM_LIFE_IN_MONTHS,
b.CAP_THRESHOLD_AMOUNT,
b.LOW_VALUE_THRESHOLD_AMOUNT,
b.LVA_CONVENTION_TYPE_ID,
b.LVA_METHOD_ID,
b.RECOGNIZE_GAIN_LOSS,
b.TERMINAL_GAIN_LOSS,
b.RECAPTURE_RESERVE_FLAG,
b.LIMIT_PROCEEDS_FLAG,
b.TRACKING_METHOD,
b.GROUP_ASSET_ID
from
FA_CATEGORIES_B A,
FA_CATEGORY_BOOK_DEFAULTS B,
FA_METHODS C,
FA_CONVENTION_TYPES D,
FA_CONVENTION_TYPES E
Where
a.category_id = b.category_id
and b.METHOD_ID = c.METHOD_ID
and b.CONVENTION_TYPE_ID = d.CONVENTION_TYPE_ID
and b.RETIREMENT_CONVENTION_TYPE_ID = e.CONVENTION_TYPE_ID
order by 3,1,2
***********************
Fixed Asset Details
**********************
SELECT
FAB.BOOK_TYPE_CODE ASSET_BOOK,
FA.ASSET_NUMBER,
FAT.DESCRIPTION ASSET_DESCRIPTION,
FA.TAG_NUMBER,
FA.ASSET_TYPE,
FAC.SEGMENT1 MAJOR_CATEGORY,
FAC.SEGMENT2 MINOR_CATEGORY,
TO_CHAR(FAB.DATE_PLACED_IN_SERVICE, 'MM/DD/YYYY') DATE_PLACED_IN_SERVICE,
FAB.COST,
GL.NAME LEDGER,
FAL.SEGMENT1 || '.' || FAL.SEGMENT2 ||'.'||FAL.SEGMENT3||'.'||FAL.SEGMENT4 AS ASSET_LOCATION,
TO_CHAR(FAR.DATE_RETIRED, 'MM/DD/YYYY') DATE_RETIRED,
GCC.SEGMENT1 LEGAL_ENTITY,
GCC.SEGMENT2 COST_CENTER,
GCC.SEGMENT3 ACCOUNT
FROM
FA_ADDITIONS_B FA,
FA_ADDITIONS_TL FAT,
FA_BOOKS FAB,
FA_CATEGORIES_B FAC,
FA_BOOK_CONTROLS FBC,
GL_LEDGERS GL,
FA_DISTRIBUTION_HISTORY FDH,
FA_LOCATIONS FAL,
FA_RETIREMENTS FAR,
GL_CODE_COMBINATIONS GCC
WHERE
FA.ASSET_CATEGORY_ID = FAC.CATEGORY_ID
AND FBC.SET_OF_BOOKS_ID = GL.LEDGER_ID
AND FBC.BOOK_TYPE_CODE = FAB.BOOK_TYPE_CODE
AND FA.ASSET_ID = FAB.ASSET_ID
AND FA.ASSET_ID = FAR.ASSET_ID (+)
AND FA.ASSET_ID = FAT.ASSET_ID
AND FA.ASSET_ID = FDH.ASSET_ID
AND FDH.DATE_INEFFECTIVE IS NULL
AND FAB.DATE_INEFFECTIVE IS NULL
AND FDH.LOCATION_ID = FAL.LOCATION_ID
AND GCC.CODE_COMBINATION_ID = FDH.CODE_COMBINATION_ID
AND FAT.LANGUAGE = 'US'
AND (TO_CHAR(DATE_RETIRED, 'MM/DD/YYYY') IS NULL OR DATE_RETIRED >= TO_DATE('11/30/2024', 'MM/DD/YYYY'))
AND TO_CHAR(FAB.DATE_PLACED_IN_SERVICE, 'MM/DD/YYYY') <= '12/31/2024'