[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
CR.CONCURRENT_PROGRAM_ID
INTO L_CONC_PROGRAM_ID
FROM
FND_CONCURRENT_REQUESTS CR
WHERE CR.PROGRAM_APPLICATION_ID = 7002
AND CR.REQUEST_ID = P_CONC_REQUEST_ID;
SELECT
CP.USER_CONCURRENT_PROGRAM_NAME
INTO L_REPORT_NAME
FROM
FND_CONCURRENT_PROGRAMS_VL CP
WHERE CP.CONCURRENT_PROGRAM_ID = L_CONC_PROGRAM_ID
AND CP.APPLICATION_ID = 7002;
SELECT
DECODE(PERIOD_CLOSE_DATE
,NULL
,'OPEN'
,'CLOSED')
INTO CP_OPEN_CLOSED
FROM
FA_DEPRN_PERIODS
WHERE BOOK_TYPE_CODE = P_BOOK
AND PERIOD_NAME = P_PERIOD2;
SELECT
PERIOD_COUNTER,
PERIOD_OPEN_DATE,
NVL(PERIOD_CLOSE_DATE
,SYSDATE),
FISCAL_YEAR
INTO L_PERIOD_PC,L_PERIOD_POD,L_PERIOD_PCD,L_PERIOD_FY
FROM
FA_DEPRN_PERIODS
WHERE BOOK_TYPE_CODE = P_BOOK
AND PERIOD_NAME = P_PERIOD1;
SELECT
PERIOD_COUNTER,
PERIOD_OPEN_DATE,
NVL(PERIOD_CLOSE_DATE
,SYSDATE),
FISCAL_YEAR
INTO L_PERIOD_PC,L_PERIOD_POD,L_PERIOD_PCD,L_PERIOD_FY
FROM
FA_DEPRN_PERIODS
WHERE BOOK_TYPE_CODE = P_BOOK
AND PERIOD_NAME = P_PERIOD2;
FUNCTION DO_INSERTFORMULA RETURN NUMBER IS
BEGIN
BEGIN
IF (P_REPORT_TYPE = 'RESERVE' OR P_REPORT_TYPE = 'REVAL RESERVE') THEN
INSERT_INFO;
END DO_INSERTFORMULA;
PROCEDURE INSERT_INFO IS
PRAGMA AUTONOMOUS_TRANSACTION;
SELECT
P1.PERIOD_COUNTER,
P1.PERIOD_OPEN_DATE,
NVL(P1.PERIOD_CLOSE_DATE
,SYSDATE),
P2.PERIOD_COUNTER,
NVL(P2.PERIOD_CLOSE_DATE
,SYSDATE),
BC.DISTRIBUTION_SOURCE_BOOK
INTO CP_PERIOD1_PC,CP_PERIOD1_POD,CP_PERIOD1_PCD,CP_PERIOD2_PC,CP_PERIOD2_PCD,CP_DISTRIBUTION_SOURCE_BOOK
FROM
FA_DEPRN_PERIODS P1,
FA_DEPRN_PERIODS P2,
FA_BOOK_CONTROLS BC
WHERE BC.BOOK_TYPE_CODE = P_BOOK
AND P1.BOOK_TYPE_CODE = P_BOOK
AND P1.PERIOD_NAME = P_PERIOD1
AND P2.BOOK_TYPE_CODE = P_BOOK
AND P2.PERIOD_NAME = P_PERIOD2;
DELETE FROM JE_HU_BALANCES_REPORT;
DELETE FROM FA_LOOKUPS_B
WHERE LOOKUP_TYPE = 'REPORT TYPE';
DELETE FROM FA_LOOKUPS_TL
WHERE LOOKUP_TYPE = 'REPORT TYPE';
INSERT INTO FA_LOOKUPS_B
(LOOKUP_TYPE
,LOOKUP_CODE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,ENABLED_FLAG)
VALUES ('REPORT TYPE'
,P_REPORT_TYPE
,1
,SYSDATE
,'Y');
INSERT INTO FA_LOOKUPS_TL
(LOOKUP_TYPE
,LOOKUP_CODE
,LANGUAGE
,SOURCE_LANG
,MEANING
,LAST_UPDATED_BY
,LAST_UPDATE_DATE)
SELECT
'REPORT TYPE',
P_REPORT_TYPE,
L.LANGUAGE_CODE,
USERENV('LANG'),
P_REPORT_TYPE,
1,
SYSDATE
FROM
FND_LANGUAGES L
WHERE L.INSTALLED_FLAG IN ( 'I' , 'B' );
END INSERT_INFO;
INSERT INTO JE_HU_BALANCES_REPORT
(CATEGORY_ID
,ASSET_ID
,DISTRIBUTION_CCID
,ADJUSTMENT_CCID
,CATEGORY_BOOKS_ACCOUNT
,SOURCE_TYPE_CODE
,AMOUNT)
SELECT
AH.CATEGORY_ID,
AH.ASSET_ID,
DH.CODE_COMBINATION_ID,
AJ.CODE_COMBINATION_ID,
null,
AJ.SOURCE_TYPE_CODE,
SUM(DECODE(AJ.DEBIT_CREDIT_FLAG
,CP_BALANCE_TYPE
,1
,-1) * AJ.ADJUSTMENT_AMOUNT)
FROM
FA_LOOKUPS RT,
FA_DISTRIBUTION_HISTORY DH,
FA_TRANSACTION_HEADERS TH,
FA_ASSET_HISTORY AH,
FA_ADJUSTMENTS AJ
WHERE RT.LOOKUP_TYPE = 'REPORT TYPE'
AND RT.LOOKUP_CODE = P_REPORT_TYPE
AND DH.BOOK_TYPE_CODE = CP_DISTRIBUTION_SOURCE_BOOK
AND AJ.ASSET_ID = DH.ASSET_ID
AND AJ.BOOK_TYPE_CODE = P_BOOK
AND AJ.DISTRIBUTION_ID = DH.DISTRIBUTION_ID
AND AJ.ADJUSTMENT_TYPE in ( P_REPORT_TYPE , DECODE(P_REPORT_TYPE
,'REVAL RESERVE'
,'REVAL AMORT') )
AND AJ.PERIOD_COUNTER_CREATED BETWEEN CP_PERIOD1_PC
AND CP_PERIOD2_PC
AND TH.TRANSACTION_HEADER_ID = AJ.TRANSACTION_HEADER_ID
AND AH.ASSET_ID = DH.ASSET_ID
AND ( ( AH.ASSET_TYPE <> 'EXPENSED'
AND P_REPORT_TYPE IN ( 'COST' , 'CIP COST' ) )
OR ( AH.ASSET_TYPE = 'CAPITALIZED'
AND P_REPORT_TYPE IN ( 'RESERVE' , 'REVAL RESERVE' ) ) )
AND TH.TRANSACTION_HEADER_ID BETWEEN AH.TRANSACTION_HEADER_ID_IN
AND NVL(AH.TRANSACTION_HEADER_ID_OUT - 1
,TH.TRANSACTION_HEADER_ID)
AND ( DECODE(RT.LOOKUP_CODE
,AJ.ADJUSTMENT_TYPE
,1
,0) * AJ.ADJUSTMENT_AMOUNT ) <> 0
GROUP BY
AH.CATEGORY_ID,
AH.ASSET_ID,
DH.CODE_COMBINATION_ID,
AJ.CODE_COMBINATION_ID,
AJ.SOURCE_TYPE_CODE;
INSERT INTO JE_HU_BALANCES_REPORT
(CATEGORY_ID
,ASSET_ID
,DISTRIBUTION_CCID
,ADJUSTMENT_CCID
,CATEGORY_BOOKS_ACCOUNT
,SOURCE_TYPE_CODE
,AMOUNT)
SELECT
CB.CATEGORY_ID,
DH.ASSET_ID,
DH.CODE_COMBINATION_ID,
null,
CB.DEPRN_RESERVE_ACCT,
'ADDITION',
SUM(DD.DEPRN_RESERVE)
FROM
FA_DISTRIBUTION_HISTORY DH,
FA_CATEGORY_BOOKS CB,
FA_ADDITIONS AD,
FA_DEPRN_DETAIL DD
WHERE NOT EXISTS (
SELECT
BR.ASSET_ID
FROM
JE_HU_BALANCES_REPORT BR
WHERE BR.ASSET_ID = DH.ASSET_ID
AND BR.DISTRIBUTION_CCID = DH.CODE_COMBINATION_ID
AND BR.SOURCE_TYPE_CODE = 'ADDITION' )
AND DD.BOOK_TYPE_CODE = P_BOOK
AND ( DD.PERIOD_COUNTER + 1 ) BETWEEN CP_PERIOD1_PC
AND CP_PERIOD2_PC
AND DD.DEPRN_SOURCE_CODE = 'B'
AND DD.ASSET_ID = DH.ASSET_ID
AND DD.DEPRN_RESERVE <> 0
AND DD.DISTRIBUTION_ID = DH.DISTRIBUTION_ID
AND DD.ASSET_ID = AD.ASSET_ID
AND DD.BOOK_TYPE_CODE = CB.BOOK_TYPE_CODE
AND AD.ASSET_CATEGORY_ID = CB.CATEGORY_ID
GROUP BY
CB.CATEGORY_ID,
DH.ASSET_ID,
DH.CODE_COMBINATION_ID,
CB.DEPRN_RESERVE_ACCT;
INSERT INTO JE_HU_BALANCES_REPORT
(CATEGORY_ID
,ASSET_ID
,DISTRIBUTION_CCID
,ADJUSTMENT_CCID
,CATEGORY_BOOKS_ACCOUNT
,SOURCE_TYPE_CODE
,AMOUNT)
SELECT
AH.CATEGORY_ID,
AH.ASSET_ID,
DH.CODE_COMBINATION_ID,
AJ.CODE_COMBINATION_ID,
null,
ME.RATE_SOURCE_RULE,
SUM(DECODE(AJ.DEBIT_CREDIT_FLAG
,CP_BALANCE_TYPE
,1
,-1) * AJ.ADJUSTMENT_AMOUNT)
FROM
FA_LOOKUPS RT,
FA_DISTRIBUTION_HISTORY DH,
FA_TRANSACTION_HEADERS TH,
FA_ASSET_HISTORY AH,
FA_ADJUSTMENTS AJ,
FA_METHODS ME,
FA_BOOKS BO,
FA_DEPRN_PERIODS DP
WHERE RT.LOOKUP_TYPE = 'REPORT TYPE'
AND RT.LOOKUP_CODE = P_REPORT_TYPE
AND DH.BOOK_TYPE_CODE = CP_DISTRIBUTION_SOURCE_BOOK
AND AJ.ASSET_ID = DH.ASSET_ID
AND AJ.BOOK_TYPE_CODE = P_BOOK
AND AJ.DISTRIBUTION_ID = DH.DISTRIBUTION_ID
AND AJ.ADJUSTMENT_TYPE in ( P_REPORT_TYPE , DECODE(P_REPORT_TYPE
,'REVAL RESERVE'
,'REVAL AMORT') )
AND AJ.PERIOD_COUNTER_CREATED BETWEEN CP_PERIOD1_PC
AND CP_PERIOD2_PC
AND AJ.SOURCE_TYPE_CODE = 'DEPRECIATION'
AND TH.TRANSACTION_HEADER_ID = AJ.TRANSACTION_HEADER_ID
AND AH.ASSET_ID = DH.ASSET_ID
AND ( ( AH.ASSET_TYPE <> 'EXPENSED'
AND P_REPORT_TYPE IN ( 'COST' , 'CIP COST' ) )
OR ( AH.ASSET_TYPE = 'CAPITALIZED'
AND P_REPORT_TYPE IN ( 'RESERVE' , 'REVAL RESERVE' ) ) )
AND TH.TRANSACTION_HEADER_ID BETWEEN AH.TRANSACTION_HEADER_ID_IN
AND NVL(AH.TRANSACTION_HEADER_ID_OUT - 1
,TH.TRANSACTION_HEADER_ID)
AND ( DECODE(RT.LOOKUP_CODE
,AJ.ADJUSTMENT_TYPE
,1
,0) * AJ.ADJUSTMENT_AMOUNT ) <> 0
AND DP.PERIOD_COUNTER = CP_PERIOD2_PC
AND DP.BOOK_TYPE_CODE = P_BOOK
AND NVL(DP.PERIOD_CLOSE_DATE
,SYSDATE) between BO.DATE_EFFECTIVE
AND NVL(BO.DATE_INEFFECTIVE
,SYSDATE)
AND BO.BOOK_TYPE_CODE = P_BOOK
AND BO.ASSET_ID = AJ.ASSET_ID
AND ME.METHOD_CODE = BO.DEPRN_METHOD_CODE
AND NVL(ME.LIFE_IN_MONTHS
,0) = NVL(BO.LIFE_IN_MONTHS
,0)
GROUP BY
AH.CATEGORY_ID,
AH.ASSET_ID,
DH.CODE_COMBINATION_ID,
AJ.CODE_COMBINATION_ID,
ME.RATE_SOURCE_RULE;
INSERT INTO JE_HU_BALANCES_REPORT
(CATEGORY_ID
,ASSET_ID
,DISTRIBUTION_CCID
,ADJUSTMENT_CCID
,CATEGORY_BOOKS_ACCOUNT
,SOURCE_TYPE_CODE
,AMOUNT)
SELECT
AH.CATEGORY_ID,
AH.ASSET_ID,
DH.CODE_COMBINATION_ID,
null,
CB.DEPRN_RESERVE_ACCT,
'STR UNPLANNED',
SUM(DECODE(AJ.DEBIT_CREDIT_FLAG
,CP_BALANCE_TYPE
,-1
,1) * AJ.ADJUSTMENT_AMOUNT)
FROM
FA_LOOKUPS RT,
FA_DISTRIBUTION_HISTORY DH,
FA_CATEGORY_BOOKS CB,
FA_TRANSACTION_HEADERS TH,
FA_ASSET_HISTORY AH,
FA_ADJUSTMENTS AJ,
FA_LOOKUPS LO,
FA_METHODS ME,
FA_BOOKS BO
WHERE RT.LOOKUP_TYPE = 'REPORT TYPE'
AND RT.LOOKUP_CODE = P_REPORT_TYPE
AND DH.BOOK_TYPE_CODE = CP_DISTRIBUTION_SOURCE_BOOK
AND AJ.ASSET_ID = DH.ASSET_ID
AND AJ.BOOK_TYPE_CODE = P_BOOK
AND AJ.DISTRIBUTION_ID = DH.DISTRIBUTION_ID
AND AJ.ADJUSTMENT_TYPE = 'EXPENSE'
AND AJ.PERIOD_COUNTER_ADJUSTED BETWEEN CP_PERIOD1_PC
AND CP_PERIOD2_PC
AND AJ.SOURCE_TYPE_CODE = 'DEPRECIATION'
AND TH.TRANSACTION_HEADER_ID = AJ.TRANSACTION_HEADER_ID
AND AH.ASSET_ID = DH.ASSET_ID
AND TH.TRANSACTION_HEADER_ID BETWEEN AH.TRANSACTION_HEADER_ID_IN
AND NVL(AH.TRANSACTION_HEADER_ID_OUT - 1
,TH.TRANSACTION_HEADER_ID)
AND TH.TRANSACTION_SUBTYPE = LO.LOOKUP_CODE
AND LO.LOOKUP_TYPE = 'UNPLANNED DEPRN'
AND CB.BOOK_TYPE_CODE = AJ.BOOK_TYPE_CODE
AND CB.CATEGORY_ID = AH.CATEGORY_ID
AND AJ.ASSET_ID = BO.ASSET_ID
AND AJ.BOOK_TYPE_CODE = BO.BOOK_TYPE_CODE
AND TH.TRANSACTION_HEADER_ID = BO.TRANSACTION_HEADER_ID_IN
AND ME.METHOD_CODE = BO.DEPRN_METHOD_CODE
AND ME.STL_METHOD_FLAG = 'YES'
AND NVL(ME.LIFE_IN_MONTHS
,0) = NVL(BO.LIFE_IN_MONTHS
,0)
GROUP BY
AH.CATEGORY_ID,
AH.ASSET_ID,
DH.CODE_COMBINATION_ID,
AJ.CODE_COMBINATION_ID,
CB.DEPRN_RESERVE_ACCT;
INSERT INTO JE_HU_BALANCES_REPORT
(CATEGORY_ID
,ASSET_ID
,DISTRIBUTION_CCID
,ADJUSTMENT_CCID
,CATEGORY_BOOKS_ACCOUNT
,SOURCE_TYPE_CODE
,AMOUNT)
SELECT
AH.CATEGORY_ID,
AH.ASSET_ID,
DH.CODE_COMBINATION_ID,
null,
DECODE(RT.LOOKUP_CODE
,'RESERVE'
,CB.DEPRN_RESERVE_ACCT
,'REVAL RESERVE'
,CB.REVAL_RESERVE_ACCT),
DECODE(DD.DEPRN_SOURCE_CODE
,'D'
,'DEPRECIATION'
,'ADDITION'),
SUM(DECODE(RT.LOOKUP_CODE
,'RESERVE'
,DD.DEPRN_AMOUNT
,'REVAL RESERVE'
,-DD.REVAL_AMORTIZATION))
FROM
FA_LOOKUPS RT,
FA_CATEGORY_BOOKS CB,
FA_DISTRIBUTION_HISTORY DH,
FA_ASSET_HISTORY AH,
FA_DEPRN_DETAIL DD,
FA_DEPRN_PERIODS DP
WHERE DH.BOOK_TYPE_CODE = CP_DISTRIBUTION_SOURCE_BOOK
AND AH.ASSET_ID = DH.ASSET_ID
AND AH.ASSET_TYPE = 'CAPITALIZED'
AND AH.DATE_EFFECTIVE < NVL(DH.DATE_INEFFECTIVE
,SYSDATE)
AND NVL(DH.DATE_INEFFECTIVE
,SYSDATE) <= NVL(AH.DATE_INEFFECTIVE
,SYSDATE)
AND CB.CATEGORY_ID = AH.CATEGORY_ID
AND CB.BOOK_TYPE_CODE = P_BOOK
AND ( ( DD.DEPRN_SOURCE_CODE = 'B'
AND ( DD.PERIOD_COUNTER + 1 ) < CP_PERIOD2_PC )
OR ( DD.DEPRN_SOURCE_CODE = 'D' ) )
AND DD.BOOK_TYPE_CODE || '' = P_BOOK
AND DD.ASSET_ID = DH.ASSET_ID
AND DD.DISTRIBUTION_ID = DH.DISTRIBUTION_ID
AND DD.PERIOD_COUNTER between CP_PERIOD1_PC
AND CP_PERIOD2_PC
AND DP.BOOK_TYPE_CODE = DD.BOOK_TYPE_CODE
AND DP.PERIOD_COUNTER = DD.PERIOD_COUNTER
AND DECODE(RT.LOOKUP_CODE
,'RESERVE'
,CB.DEPRN_RESERVE_ACCT
,'REVAL RESERVE'
,CB.REVAL_RESERVE_ACCT) is not null
AND DECODE(RT.LOOKUP_CODE
,'RESERVE'
,DD.DEPRN_AMOUNT
,'REVAL RESERVE'
,NVL(DD.REVAL_AMORTIZATION
,0)) <> 0
GROUP BY
AH.CATEGORY_ID,
AH.ASSET_ID,
DH.CODE_COMBINATION_ID,
DECODE(RT.LOOKUP_CODE
,'RESERVE'
,CB.DEPRN_RESERVE_ACCT
,'REVAL RESERVE'
,CB.REVAL_RESERVE_ACCT),
DD.DEPRN_SOURCE_CODE;
INSERT INTO JE_HU_BALANCES_REPORT
(CATEGORY_ID
,ASSET_ID
,DISTRIBUTION_CCID
,ADJUSTMENT_CCID
,CATEGORY_BOOKS_ACCOUNT
,SOURCE_TYPE_CODE
,AMOUNT)
SELECT
AH.CATEGORY_ID,
AH.ASSET_ID,
DH.CODE_COMBINATION_ID,
null,
DECODE(RT.LOOKUP_CODE
,'RESERVE'
,CB.DEPRN_RESERVE_ACCT
,'REVAL RESERVE'
,CB.REVAL_RESERVE_ACCT),
'FIRST',
SUM(DECODE(RT.LOOKUP_CODE
,'RESERVE'
,DD.DEPRN_AMOUNT
,'REVAL RESERVE'
,-DD.REVAL_AMORTIZATION))
FROM
FA_LOOKUPS RT,
FA_CATEGORY_BOOKS CB,
FA_DISTRIBUTION_HISTORY DH,
FA_ASSET_HISTORY AH,
FA_DEPRN_DETAIL DD,
FA_DEPRN_PERIODS DP,
FA_DEPRN_PERIODS DP1,
FA_BOOKS BO
WHERE DH.BOOK_TYPE_CODE = CP_DISTRIBUTION_SOURCE_BOOK
AND AH.ASSET_ID = DH.ASSET_ID
AND AH.ASSET_TYPE = 'CAPITALIZED'
AND AH.DATE_EFFECTIVE < NVL(DH.DATE_INEFFECTIVE
,SYSDATE)
AND NVL(DH.DATE_INEFFECTIVE
,SYSDATE) <= NVL(AH.DATE_INEFFECTIVE
,SYSDATE)
AND CB.CATEGORY_ID = AH.CATEGORY_ID
AND CB.BOOK_TYPE_CODE = P_BOOK
AND DD.DEPRN_SOURCE_CODE = 'D'
AND DD.BOOK_TYPE_CODE || '' = P_BOOK
AND DD.ASSET_ID = DH.ASSET_ID
AND DD.DISTRIBUTION_ID = DH.DISTRIBUTION_ID
AND DD.PERIOD_COUNTER between CP_PERIOD1_PC
AND CP_PERIOD2_PC
AND DP.BOOK_TYPE_CODE = DD.BOOK_TYPE_CODE
AND DP.PERIOD_COUNTER = DD.PERIOD_COUNTER
AND DECODE(RT.LOOKUP_CODE
,'RESERVE'
,CB.DEPRN_RESERVE_ACCT
,'REVAL RESERVE'
,CB.REVAL_RESERVE_ACCT) is not null
AND DECODE(RT.LOOKUP_CODE
,'RESERVE'
,DD.DEPRN_AMOUNT
,'REVAL RESERVE'
,NVL(DD.REVAL_AMORTIZATION
,0)) <> 0
AND DP1.PERIOD_COUNTER = CP_PERIOD2_PC
AND DP1.BOOK_TYPE_CODE = P_BOOK
AND NVL(DP1.PERIOD_CLOSE_DATE
,SYSDATE) between BO.DATE_EFFECTIVE
AND NVL(BO.DATE_INEFFECTIVE
,SYSDATE)
AND BO.BOOK_TYPE_CODE = P_BOOK
AND BO.ASSET_ID = AH.ASSET_ID
AND BO.PERIOD_COUNTER_FULLY_RESERVED = (
SELECT
MIN(PERIOD_COUNTER)
FROM
FA_DEPRN_DETAIL DD2
WHERE DD2.BOOK_TYPE_CODE = DD.BOOK_TYPE_CODE
AND DD2.ASSET_ID = DD.ASSET_ID
AND DD2.DEPRN_SOURCE_CODE = 'D' )
GROUP BY
AH.CATEGORY_ID,
AH.ASSET_ID,
DH.CODE_COMBINATION_ID,
DECODE(RT.LOOKUP_CODE
,'RESERVE'
,CB.DEPRN_RESERVE_ACCT
,'REVAL RESERVE'
,CB.REVAL_RESERVE_ACCT);
INSERT INTO JE_HU_BALANCES_REPORT
(CATEGORY_ID
,ASSET_ID
,DISTRIBUTION_CCID
,ADJUSTMENT_CCID
,CATEGORY_BOOKS_ACCOUNT
,SOURCE_TYPE_CODE
,AMOUNT)
SELECT
AH.CATEGORY_ID,
AH.ASSET_ID,
DH.CODE_COMBINATION_ID,
null,
DECODE(RT.LOOKUP_CODE
,'RESERVE'
,CB.DEPRN_RESERVE_ACCT
,'REVAL RESERVE'
,CB.REVAL_RESERVE_ACCT),
ME.RATE_SOURCE_RULE,
SUM(DECODE(RT.LOOKUP_CODE
,'RESERVE'
,DD.DEPRN_AMOUNT
,'REVAL RESERVE'
,-DD.REVAL_AMORTIZATION))
FROM
FA_LOOKUPS RT,
FA_CATEGORY_BOOKS CB,
FA_DISTRIBUTION_HISTORY DH,
FA_ASSET_HISTORY AH,
FA_DEPRN_DETAIL DD,
FA_DEPRN_PERIODS DP,
FA_DEPRN_PERIODS DP1,
FA_METHODS ME,
FA_BOOKS BO
WHERE DH.BOOK_TYPE_CODE = CP_DISTRIBUTION_SOURCE_BOOK
AND AH.ASSET_ID = DH.ASSET_ID
AND AH.ASSET_TYPE = 'CAPITALIZED'
AND AH.DATE_EFFECTIVE < NVL(DH.DATE_INEFFECTIVE
,SYSDATE)
AND NVL(DH.DATE_INEFFECTIVE
,SYSDATE) <= NVL(AH.DATE_INEFFECTIVE
,SYSDATE)
AND CB.CATEGORY_ID = AH.CATEGORY_ID
AND CB.BOOK_TYPE_CODE = P_BOOK
AND DD.DEPRN_SOURCE_CODE = 'D'
AND DD.BOOK_TYPE_CODE || '' = P_BOOK
AND DD.ASSET_ID = DH.ASSET_ID
AND DD.DISTRIBUTION_ID = DH.DISTRIBUTION_ID
AND DD.PERIOD_COUNTER between CP_PERIOD1_PC
AND CP_PERIOD2_PC
AND DP.BOOK_TYPE_CODE = DD.BOOK_TYPE_CODE
AND DP.PERIOD_COUNTER = DD.PERIOD_COUNTER
AND DECODE(RT.LOOKUP_CODE
,'RESERVE'
,CB.DEPRN_RESERVE_ACCT
,'REVAL RESERVE'
,CB.REVAL_RESERVE_ACCT) is not null
AND DECODE(RT.LOOKUP_CODE
,'RESERVE'
,DD.DEPRN_AMOUNT
,'REVAL RESERVE'
,NVL(DD.REVAL_AMORTIZATION
,0)) <> 0
AND DP1.PERIOD_COUNTER = CP_PERIOD2_PC
AND DP1.BOOK_TYPE_CODE = P_BOOK
AND NVL(DP1.PERIOD_CLOSE_DATE
,SYSDATE) between BO.DATE_EFFECTIVE
AND NVL(BO.DATE_INEFFECTIVE
,SYSDATE)
AND BO.BOOK_TYPE_CODE = P_BOOK
AND BO.ASSET_ID = AH.ASSET_ID
AND ME.METHOD_CODE = BO.DEPRN_METHOD_CODE
AND NVL(ME.LIFE_IN_MONTHS
,0) = NVL(BO.LIFE_IN_MONTHS
,0)
GROUP BY
AH.CATEGORY_ID,
AH.ASSET_ID,
ME.RATE_SOURCE_RULE,
DH.CODE_COMBINATION_ID,
DECODE(RT.LOOKUP_CODE
,'RESERVE'
,CB.DEPRN_RESERVE_ACCT
,'REVAL RESERVE'
,CB.REVAL_RESERVE_ACCT);
SELECT
FND_DATE.DATE_TO_CHARDT(SYSDATE)
INTO DATE1
FROM
DUAL;
INSERT INTO JE_HU_BALANCES_REPORT
(CATEGORY_ID
,ASSET_ID
,DISTRIBUTION_CCID
,ADJUSTMENT_CCID
,CATEGORY_BOOKS_ACCOUNT
,SOURCE_TYPE_CODE
,AMOUNT)
SELECT
AH.CATEGORY_ID,
AH.ASSET_ID,
DH.CODE_COMBINATION_ID,
null,
CB.DEPRN_RESERVE_ACCT,
'NON STR UNPLN',
SUM(DECODE(AJ.DEBIT_CREDIT_FLAG
,CP_BALANCE_TYPE
,-1
,1) * AJ.ADJUSTMENT_AMOUNT)
FROM
FA_LOOKUPS RT,
FA_DISTRIBUTION_HISTORY DH,
FA_CATEGORY_BOOKS CB,
FA_TRANSACTION_HEADERS TH,
FA_ASSET_HISTORY AH,
FA_ADJUSTMENTS AJ,
FA_LOOKUPS LO,
FA_METHODS ME,
FA_BOOKS BO
WHERE RT.LOOKUP_TYPE = 'REPORT TYPE'
AND RT.LOOKUP_CODE = P_REPORT_TYPE
AND DH.BOOK_TYPE_CODE = CP_DISTRIBUTION_SOURCE_BOOK
AND AJ.ASSET_ID = DH.ASSET_ID
AND AJ.BOOK_TYPE_CODE = P_BOOK
AND AJ.DISTRIBUTION_ID = DH.DISTRIBUTION_ID
AND AJ.ADJUSTMENT_TYPE = 'EXPENSE'
AND AJ.PERIOD_COUNTER_ADJUSTED BETWEEN CP_PERIOD1_PC
AND CP_PERIOD2_PC
AND AJ.SOURCE_TYPE_CODE = 'DEPRECIATION'
AND TH.TRANSACTION_HEADER_ID = AJ.TRANSACTION_HEADER_ID
AND AH.ASSET_ID = DH.ASSET_ID
AND TH.TRANSACTION_HEADER_ID BETWEEN AH.TRANSACTION_HEADER_ID_IN
AND NVL(AH.TRANSACTION_HEADER_ID_OUT - 1
,TH.TRANSACTION_HEADER_ID)
AND TH.TRANSACTION_SUBTYPE = LO.LOOKUP_CODE
AND LO.LOOKUP_TYPE = 'UNPLANNED DEPRN'
AND CB.BOOK_TYPE_CODE = AJ.BOOK_TYPE_CODE
AND CB.CATEGORY_ID = AH.CATEGORY_ID
AND AJ.ASSET_ID = BO.ASSET_ID
AND AJ.BOOK_TYPE_CODE = BO.BOOK_TYPE_CODE
AND TH.TRANSACTION_HEADER_ID = BO.TRANSACTION_HEADER_ID_IN
AND ME.METHOD_CODE = BO.DEPRN_METHOD_CODE
AND ME.STL_METHOD_FLAG = 'NO'
AND NVL(ME.LIFE_IN_MONTHS
,0) = NVL(BO.LIFE_IN_MONTHS
,0)
GROUP BY
AH.CATEGORY_ID,
AH.ASSET_ID,
DH.CODE_COMBINATION_ID,
AJ.CODE_COMBINATION_ID,
CB.DEPRN_RESERVE_ACCT;
INSERT INTO JE_HU_BALANCES_REPORT
(CATEGORY_ID
,ASSET_ID
,DISTRIBUTION_CCID
,ADJUSTMENT_CCID
,CATEGORY_BOOKS_ACCOUNT
,SOURCE_TYPE_CODE
,AMOUNT)
SELECT
AH.CATEGORY_ID,
AH.ASSET_ID,
DH.CODE_COMBINATION_ID,
null,
DECODE(RT.LOOKUP_CODE
,'RESERVE'
,CB.DEPRN_RESERVE_ACCT
,'REVAL RESERVE'
,CB.REVAL_RESERVE_ACCT),
'FIRST STR',
SUM(NVL(DD.DEPRN_ADJUSTMENT_AMOUNT
,0))
FROM
FA_LOOKUPS RT,
FA_CATEGORY_BOOKS CB,
FA_DISTRIBUTION_HISTORY DH,
FA_ASSET_HISTORY AH,
FA_DEPRN_DETAIL DD,
FA_DEPRN_PERIODS DP,
FA_BOOKS BO,
FA_METHODS ME,
FA_TRANSACTION_HEADERS TH
WHERE DH.BOOK_TYPE_CODE = CP_DISTRIBUTION_SOURCE_BOOK
AND AH.ASSET_ID = DH.ASSET_ID
AND AH.ASSET_TYPE = 'CAPITALIZED'
AND AH.DATE_EFFECTIVE < NVL(DH.DATE_INEFFECTIVE
,SYSDATE)
AND NVL(DH.DATE_INEFFECTIVE
,SYSDATE) <= NVL(AH.DATE_INEFFECTIVE
,SYSDATE)
AND CB.CATEGORY_ID = AH.CATEGORY_ID
AND CB.BOOK_TYPE_CODE = P_BOOK
AND DD.DEPRN_SOURCE_CODE = 'B'
AND DD.BOOK_TYPE_CODE || '' = P_BOOK
AND DD.ASSET_ID = DH.ASSET_ID
AND DD.DISTRIBUTION_ID = DH.DISTRIBUTION_ID
AND DD.PERIOD_COUNTER between CP_PERIOD1_PC - 1
AND CP_PERIOD2_PC
AND DP.BOOK_TYPE_CODE = DD.BOOK_TYPE_CODE
AND DP.PERIOD_COUNTER = DD.PERIOD_COUNTER
AND DECODE(RT.LOOKUP_CODE
,'RESERVE'
,CB.DEPRN_RESERVE_ACCT
,'REVAL RESERVE'
,CB.REVAL_RESERVE_ACCT) is not null
AND DD.DEPRN_ADJUSTMENT_AMOUNT is not null
AND DD.ASSET_ID = BO.ASSET_ID
AND DD.BOOK_TYPE_CODE = BO.BOOK_TYPE_CODE
AND BO.TRANSACTION_HEADER_ID_IN = TH.TRANSACTION_HEADER_ID
AND TH.TRANSACTION_TYPE_CODE = 'ADDITION'
AND ME.METHOD_CODE = BO.DEPRN_METHOD_CODE
AND ME.STL_METHOD_FLAG = 'YES'
AND NVL(ME.LIFE_IN_MONTHS
,0) = NVL(BO.LIFE_IN_MONTHS
,0)
GROUP BY
AH.CATEGORY_ID,
AH.ASSET_ID,
DH.CODE_COMBINATION_ID,
DECODE(RT.LOOKUP_CODE
,'RESERVE'
,CB.DEPRN_RESERVE_ACCT
,'REVAL RESERVE'
,CB.REVAL_RESERVE_ACCT),
DD.DEPRN_SOURCE_CODE;
INSERT INTO JE_HU_BALANCES_REPORT
(CATEGORY_ID
,ASSET_ID
,DISTRIBUTION_CCID
,ADJUSTMENT_CCID
,CATEGORY_BOOKS_ACCOUNT
,SOURCE_TYPE_CODE
,AMOUNT)
SELECT
AH.CATEGORY_ID,
AH.ASSET_ID,
DH.CODE_COMBINATION_ID,
null,
DECODE(RT.LOOKUP_CODE
,'RESERVE'
,CB.DEPRN_RESERVE_ACCT
,'REVAL RESERVE'
,CB.REVAL_RESERVE_ACCT),
'FIRST NON STR',
SUM(NVL(DD.DEPRN_ADJUSTMENT_AMOUNT
,0))
FROM
FA_LOOKUPS RT,
FA_CATEGORY_BOOKS CB,
FA_DISTRIBUTION_HISTORY DH,
FA_ASSET_HISTORY AH,
FA_DEPRN_DETAIL DD,
FA_DEPRN_PERIODS DP,
FA_BOOKS BO,
FA_METHODS ME,
FA_TRANSACTION_HEADERS TH
WHERE DH.BOOK_TYPE_CODE = CP_DISTRIBUTION_SOURCE_BOOK
AND AH.ASSET_ID = DH.ASSET_ID
AND AH.ASSET_TYPE = 'CAPITALIZED'
AND AH.DATE_EFFECTIVE < NVL(DH.DATE_INEFFECTIVE
,SYSDATE)
AND NVL(DH.DATE_INEFFECTIVE
,SYSDATE) <= NVL(AH.DATE_INEFFECTIVE
,SYSDATE)
AND CB.CATEGORY_ID = AH.CATEGORY_ID
AND CB.BOOK_TYPE_CODE = P_BOOK
AND DD.DEPRN_SOURCE_CODE = 'B'
AND DD.BOOK_TYPE_CODE || '' = P_BOOK
AND DD.ASSET_ID = DH.ASSET_ID
AND DD.DISTRIBUTION_ID = DH.DISTRIBUTION_ID
AND DD.PERIOD_COUNTER between CP_PERIOD1_PC - 1
AND CP_PERIOD2_PC
AND DP.BOOK_TYPE_CODE = DD.BOOK_TYPE_CODE
AND DP.PERIOD_COUNTER = DD.PERIOD_COUNTER
AND DECODE(RT.LOOKUP_CODE
,'RESERVE'
,CB.DEPRN_RESERVE_ACCT
,'REVAL RESERVE'
,CB.REVAL_RESERVE_ACCT) is not null
AND DD.DEPRN_ADJUSTMENT_AMOUNT is not null
AND DD.ASSET_ID = BO.ASSET_ID
AND DD.BOOK_TYPE_CODE = BO.BOOK_TYPE_CODE
AND BO.TRANSACTION_HEADER_ID_IN = TH.TRANSACTION_HEADER_ID
AND TH.TRANSACTION_TYPE_CODE = 'ADDITION'
AND ME.METHOD_CODE = BO.DEPRN_METHOD_CODE
AND ME.STL_METHOD_FLAG = 'NO'
AND NVL(ME.LIFE_IN_MONTHS
,0) = NVL(BO.LIFE_IN_MONTHS
,0)
GROUP BY
AH.CATEGORY_ID,
AH.ASSET_ID,
DH.CODE_COMBINATION_ID,
DECODE(RT.LOOKUP_CODE
,'RESERVE'
,CB.DEPRN_RESERVE_ACCT
,'REVAL RESERVE'
,CB.REVAL_RESERVE_ACCT),
DD.DEPRN_SOURCE_CODE;
SELECT PERIOD_CLOSE_DATE
INTO END_DATE
FROM FA_DEPRN_PERIODS
WHERE BOOK_TYPE_CODE = P_BOOK
AND PERIOD_NAME = P_PERIOD2;