[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
PERIOD_CLOSE_DATE
INTO CP_END_DATE
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 = 'COST' OR P_REPORT_TYPE = 'CIP COST') THEN
INSERT_INFO;
END DO_INSERTFORMULA;
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,
XAL.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,
XLA_AE_LINES XAL,
XLA_DISTRIBUTION_LINKS XDL
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 ( ( AJ.SOURCE_TYPE_CODE <> 'RECLASS'
AND TH.TRANSACTION_HEADER_ID BETWEEN AH.TRANSACTION_HEADER_ID_IN
AND NVL(AH.TRANSACTION_HEADER_ID_OUT - 1
,TH.TRANSACTION_HEADER_ID) )
OR ( AJ.SOURCE_TYPE_CODE = 'RECLASS'
AND DECODE(AJ.DEBIT_CREDIT_FLAG
,'CR'
,AH.TRANSACTION_HEADER_ID_OUT
,'DR'
,AH.TRANSACTION_HEADER_ID_IN) = TH.TRANSACTION_HEADER_ID ) )
AND ( DECODE(RT.LOOKUP_CODE
,AJ.ADJUSTMENT_TYPE
,1
,0) * AJ.ADJUSTMENT_AMOUNT ) <> 0
AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 = AJ.TRANSACTION_HEADER_ID
AND XDL.SOURCE_DISTRIBUTION_ID_NUM_2 = AJ.ADJUSTMENT_LINE_ID
AND XDL.APPLICATION_ID = 140
AND XDL.EVENT_ID = TH.EVENT_ID
AND XAL.AE_HEADER_ID = XDL.AE_HEADER_ID
AND XAL.AE_LINE_NUM = XDL.AE_LINE_NUM
AND XAL.APPLICATION_ID = 140
AND XDL.SOURCE_DISTRIBUTION_TYPE = 'TRX'
GROUP BY
AH.CATEGORY_ID,
AH.ASSET_ID,
DH.CODE_COMBINATION_ID,
XAL.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,
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;
PROCEDURE INSERT_INFO IS
PRAGMA AUTONOMOUS_TRANSACTION;
SELECT
P1.PERIOD_COUNTER,
P1.PERIOD_OPEN_DATE,
NVL(P1.PERIOD_CLOSE_DATE
,BC.LAST_DEPRN_RUN_DATE),
P2.PERIOD_COUNTER,
NVL(P2.PERIOD_CLOSE_DATE
,BC.LAST_DEPRN_RUN_DATE),
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,
null,
DECODE(P_REPORT_TYPE
,'COST'
,CB.ASSET_COST_ACCT
,'CIP COST'
,CB.CIP_COST_ACCT
,'RESERVE'
,CB.DEPRN_RESERVE_ACCT
,'REVAL RESERVE'
,CB.REVAL_RESERVE_ACCT),
DECODE(P_REPORT_TYPE
,'RESERVE'
,DECODE(DD.DEPRN_SOURCE_CODE
,'D'
,CP_BEGIN_OR_END
,'ADDITION')
,'REVAL RESERVE'
,DECODE(DD.DEPRN_SOURCE_CODE
,'D'
,CP_BEGIN_OR_END
,'ADDITION')
,CP_BEGIN_OR_END),
DECODE(P_REPORT_TYPE
,'COST'
,DD.COST
,'CIP COST'
,DD.COST
,'RESERVE'
,DD.DEPRN_RESERVE
,'REVAL RESERVE'
,DD.REVAL_RESERVE)
FROM
FA_BOOKS BK,
FA_CATEGORY_BOOKS CB,
FA_ASSET_HISTORY AH,
FA_DEPRN_DETAIL DD,
FA_DISTRIBUTION_HISTORY DH
WHERE DH.BOOK_TYPE_CODE || '' = CP_DISTRIBUTION_SOURCE_BOOK
AND DECODE(DD.DEPRN_SOURCE_CODE
,'D'
,CP_PERIOD_POD
,CP_PERIOD_PCD) BETWEEN DH.DATE_EFFECTIVE
AND NVL(DH.DATE_INEFFECTIVE
,SYSDATE)
AND DD.ASSET_ID = DH.ASSET_ID + 0
AND DD.BOOK_TYPE_CODE = P_BOOK
AND DD.DISTRIBUTION_ID = DH.DISTRIBUTION_ID + 0
AND DD.PERIOD_COUNTER <= CP_PERIOD_PC
AND DECODE(CP_BEGIN_OR_END
,'BEGIN'
,DD.DEPRN_SOURCE_CODE
,'D') = DD.DEPRN_SOURCE_CODE
AND DD.PERIOD_COUNTER = (
SELECT
MAX(SUB_DD.PERIOD_COUNTER)
FROM
FA_DEPRN_DETAIL SUB_DD
WHERE SUB_DD.BOOK_TYPE_CODE = P_BOOK
AND SUB_DD.DISTRIBUTION_ID = DH.DISTRIBUTION_ID + 0
AND SUB_DD.PERIOD_COUNTER <= CP_PERIOD_PC )
AND AH.ASSET_ID = DH.ASSET_ID + 0
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 DECODE(DD.DEPRN_SOURCE_CODE
,'D'
,CP_PERIOD_POD
,CP_PERIOD_PCD) BETWEEN AH.DATE_EFFECTIVE
AND NVL(AH.DATE_INEFFECTIVE
,SYSDATE)
AND CB.CATEGORY_ID = AH.CATEGORY_ID
AND CB.BOOK_TYPE_CODE = P_BOOK
AND BK.BOOK_TYPE_CODE = P_BOOK
AND BK.ASSET_ID = DD.ASSET_ID
AND DECODE(DD.DEPRN_SOURCE_CODE
,'D'
,CP_PERIOD_POD
,CP_PERIOD_PCD) BETWEEN BK.DATE_EFFECTIVE
AND NVL(BK.DATE_INEFFECTIVE
,SYSDATE)
AND NVL(BK.PERIOD_COUNTER_FULLY_RETIRED
,CP_PERIOD_PC + 1) > CP_PERIOD1_PC - 1
AND DECODE(P_REPORT_TYPE
,'COST'
,DECODE(AH.ASSET_TYPE
,'CAPITALIZED'
,CB.ASSET_COST_ACCT
,NULL)
,'CIP COST'
,DECODE(AH.ASSET_TYPE
,'CIP'
,CB.CIP_COST_ACCT
,NULL)
,'RESERVE'
,CB.DEPRN_RESERVE_ACCT
,'REVAL RESERVE'
,CB.REVAL_RESERVE_ACCT) is not null;