Introduction
Every year around the end of March or April, UCI Health will request for the Annual Depreciation Projection. After running the process in KFS Stage, two CSV files are extracted and sent back for review to UCI Health.
Instructions
For the Annual Depreciation Projection - running after June we need to:
-- Update parameter SOURCE_FISCAL_YEAR parameter to the end year we are testing and run fiscalYearMakerJob to create Object Codes for the new year
-- Run resetAssetDepreciationForFiscalYearJob
-- Repeat the following steps from July to June:
1) Log in to https://delta.adcom.uci.edu/kfs/
2) Impersonate mplee
3) Go to Financials Parameter
4) Look up by name: DEPRECIATION_DATE
5) Edit the entry
6) Description can be set to 'Test' and Parameter Value to the last day of month starting with July (ex. '2020-07-31')
7) Submit
8) Go to Batch Schedule
9) Look up by name: assetDepreciationBatchJob
10) Run
11) Go back to step 3) and repeat for every month until June of next year.
-- Connect to KFS7STG on SQL Developer and extract the following two results set as CSV format and send via email to Alice Lui <aylui@hs.uci.edu> and others in the email request.
--Building CSV file, named Depreciation_Buildings_STG_YYYYMMDD.csv
SELECT A.CPTLAST_NBR,
E.CUSTODIAL_CD,
A.CPTLAST_TYP_CD,
T.CPTLAST_DEPRLF_LMT,
A.CPTLAST_TAG_NBR,
A.CPTLAST_DESC,
A.CPTLAST_CRT_DT,
A.CAMPUS_CD,
A.BLDG_CD,
A.FIN_OBJ_SUB_TYP_CD,
A.CPTLAST_TOTCST_AMT,
P.ACCT_CHARGE_AMT,
P.AST_ACUM_DEPR1_AMT,
A.CPTL_AST_IN_SRVC_DT,
A.CPTL_AST_DEPR_DT,
P.AST_PRD1_DEPR1_AMT,
P.AST_PRD2_DEPR1_AMT,
P.AST_PRD3_DEPR1_AMT,
P.AST_PRD4_DEPR1_AMT,
P.AST_PRD5_DEPR1_AMT,
P.AST_PRD6_DEPR1_AMT,
P.AST_PRD7_DEPR1_AMT,
P.AST_PRD8_DEPR1_AMT,
P.AST_PRD9_DEPR1_AMT,
P.AST_PRD10DEPR1_AMT,
P.AST_PRD11DEPR1_AMT,
P.AST_PRD12DEPR1_AMT,
AX.UC_ACCT_NBR,
AX.FIN_COA_CD AS ASSET_COA_CD,
A.CPTLAST_ACQ_TYP_CD,
A.AST_INVN_STAT_CD,
A.CPTLAST_LSTINVN_DT
FROM
CM_AST_PAYMENT_T P,
CM_CPTLAST_T A,
CM_CPTLAST_EXT_T E,
CM_ASSET_TYPE_T T,
UCI_CM_ASSET_CUSTODIAL_CD_T C,
CA_ACCOUNT_EXT_T AX
WHERE A.CPTLAST_NBR = E.CPTLAST_NBR
AND A.CPTLAST_NBR = P.CPTLAST_NBR
AND T.CPTLAST_TYP_CD = A.CPTLAST_TYP_CD
AND E.CUSTODIAL_CD = C.CUSTODIAL_CD
AND C.DEPR_EXP_FIN_COA_CD = AX.FIN_COA_CD
AND C.DEPR_EXP_ACCT_NBR = AX.ACCOUNT_NBR
ORDER BY A.CPTLAST_TYP_CD, A.CPTLAST_NBR;
-- Assets/Equipment CSV file, named Depreciation_Assets_STG_YYYYMMDD.csv
SELECT A.CPTLAST_NBR,
E.CUSTODIAL_CD,
A.CPTLAST_TYP_CD,
T.CPTLAST_DEPRLF_LMT,
A.CPTLAST_TAG_NBR,
A.CPTLAST_DESC,
A.CPTLAST_CRT_DT,
A.CAMPUS_CD,
A.BLDG_CD,
A.FIN_OBJ_SUB_TYP_CD,
A.CPTLAST_TOTCST_AMT,
P.ACCT_CHARGE_AMT,
P.AST_ACUM_DEPR1_AMT,
A.CPTL_AST_IN_SRVC_DT,
A.CPTL_AST_DEPR_DT,
P.AST_PRD1_DEPR1_AMT,
P.AST_PRD2_DEPR1_AMT,
P.AST_PRD3_DEPR1_AMT,
P.AST_PRD4_DEPR1_AMT,
P.AST_PRD5_DEPR1_AMT,
P.AST_PRD6_DEPR1_AMT,
P.AST_PRD7_DEPR1_AMT,
P.AST_PRD8_DEPR1_AMT,
P.AST_PRD9_DEPR1_AMT,
P.AST_PRD10DEPR1_AMT,
P.AST_PRD11DEPR1_AMT,
P.AST_PRD12DEPR1_AMT,
AX.UC_ACCT_NBR,
AX.FIN_COA_CD AS ASSET_COA_CD,
A.CPTLAST_ACQ_TYP_CD,
A.AST_INVN_STAT_CD,
A.CPTLAST_LSTINVN_DT
FROM
CM_AST_PAYMENT_T P,
CM_CPTLAST_T A,
CM_CPTLAST_EXT_T E,
CM_ASSET_TYPE_T T,
UCI_CM_ASSET_CUSTODIAL_CD_T C,
CA_ACCOUNT_EXT_T AX
WHERE A.CPTLAST_NBR = E.CPTLAST_NBR
AND A.CPTLAST_NBR = P.CPTLAST_NBR
AND T.CPTLAST_TYP_CD = A.CPTLAST_TYP_CD
AND E.CUSTODIAL_CD = C.CUSTODIAL_CD
AND C.DEPR_EXP_FIN_COA_CD = AX.FIN_COA_CD
AND C.DEPR_EXP_ACCT_NBR = AX.ACCOUNT_NBR
AND A.FIN_OBJ_SUB_TYP_CD IN ('TM','EM','FM','NM','SM')
AND P.FIN_COA_CD = 'MC'
ORDER BY A.CPTLAST_TYP_CD, A.CPTLAST_NBR;