[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
COMPANY_NAME
INTO N
FROM
FA_SYSTEM_CONTROLS;
SELECT
( NVL(A.PROCEEDS_OF_SALE
,0) - NVL(A.COST_OF_REMOVAL
,0) ) SALE
FROM
FA_RETIREMENTS A,
JAI_FA_AST_BLOCK_DTLS B,
JAI_FA_AST_BLOCKS C
WHERE A.ASSET_ID = B.ASSET_ID
AND A.STATUS = 'PROCESSED'
AND A.ASSET_ID = ASSET_ID_v
AND B.BLOCK_ID = TO_CHAR(BLOCK_ID1)
AND C.BOOK_TYPE_CODE = BOOK_NAME
AND A.DATE_RETIRED BETWEEN C.START_DATE
AND C.YEAR_ENDED
AND C.START_DATE BETWEEN START_DATE
AND END_DATE_N;
SELECT
YEAR_END
FROM
JAI_FA_AST_YEARS
WHERE YEAR_START = START_DATE;
SELECT
A.BLOCK_ID
FROM
JAI_FA_AST_BLOCKS A,
JAI_FA_AST_BLOCK_DTLS B,
FA_RETIREMENTS C
WHERE A.BLOCK_ID = B.BLOCK_ID
AND B.ASSET_ID = C.ASSET_ID
GROUP BY
A.BLOCK_ID;
SELECT
TYPE,
RATE
FROM
JAI_FA_AST_BLOCKS
WHERE TO_CHAR(BLOCK_ID) = TO_CHAR(P_BLOCK_ID)
AND BOOK_TYPE_CODE = BOOK_NAME;
SELECT
BLOCK_ID
FROM
JAI_FA_AST_BLOCKS
WHERE TYPE = P_TYPE
AND RATE = P_RATE
AND BOOK_TYPE_CODE = BOOK_NAME
AND START_DATE BETWEEN START_DATE
AND END_DATE_N;
SELECT
DISTINCT
( NVL(A.PROCEEDS_OF_SALE
,0) - NVL(A.COST_OF_REMOVAL
,0) ) SALE
FROM
FA_RETIREMENTS A,
JAI_FA_AST_BLOCK_DTLS B,
JAI_FA_AST_BLOCKS C
WHERE A.ASSET_ID = B.ASSET_ID
AND B.BLOCK_ID = P_BLOCK_ID
AND C.TYPE = P_TYPE
AND C.RATE = P_RATE
AND A.DATE_RETIRED between START_DATE
AND END_DATE_N;
SELECT
TYPE,
RATE
FROM
JAI_FA_AST_BLOCKS
WHERE BLOCK_ID = BLOCK_ID
AND BOOK_TYPE_CODE = BOOK_NAME;
SELECT
BLOCK_ID
FROM
JAI_FA_AST_BLOCKS
WHERE TYPE = TYPE1
AND RATE = RATE
AND START_DATE < START_DATE;
SELECT
NVL(SUM(FAR.PROCEEDS_OF_SALE - FAR.COST_OF_REMOVAL)
,0) SALE
FROM
JAI_FA_AST_BLOCK_DTLS JBA,
FA_ADDITIONS FA,
JAI_FA_AST_BLOCKS JABLOA,
FA_RETIREMENTS FAR
WHERE FA.OWNED_LEASED = CP_OWNED_LEASED
AND FA.ASSET_ID = JBA.ASSET_ID
AND JBA.BLOCK_ID = JABLOA.BLOCK_ID
AND FAR.BOOK_TYPE_CODE = JABLOA.BOOK_TYPE_CODE
AND JABLOA.BOOK_TYPE_CODE = BOOK_NAME
AND JABLOA.TYPE = TYPE1
AND JABLOA.RATE = RATE
AND JBA.ASSET_ID = FAR.ASSET_ID
AND FAR.STATUS = CP_STATUS
AND FAR.DATE_RETIRED between START_DATE
AND END_DATE_N;
SELECT
'1'
FROM
JAI_FA_AST_BLOCK_DTLS JBA,
FA_BOOKS FAB,
JAI_FA_AST_BLOCKS JABOA
WHERE JBA.ASSET_ID = FAB.ASSET_ID
AND ( ( JBA.ASSET_TYPE = LV_CAPITALIZED
AND FAB.CAPITALIZE_FLAG = LV_FLAG )
OR JBA.ASSET_TYPE = LV_EXPENSED )
AND JBA.DATE_OF_ACQUISITION BETWEEN JABOA.START_DATE
AND NVL(JABOA.YEAR_ENDED
,END_DATE_N)
AND FAB.DATE_INEFFECTIVE IS NULL
AND FAB.TRANSACTION_HEADER_ID_OUT IS NULL
AND FAB.PERIOD_COUNTER_FULLY_RETIRED IS NULL
AND JBA.BLOCK_ID = JABOA.BLOCK_ID
AND JABOA.BOOK_TYPE_CODE = FAB.BOOK_TYPE_CODE
AND JABOA.BOOK_TYPE_CODE = BOOK_NAME
AND JABOA.TYPE = (
SELECT
TYPE
FROM
JAI_FA_AST_BLOCKS
WHERE BLOCK_ID = BLOCK_ID_v )
AND ( JABOA.START_DATE <= NVL(START_DATE
,JABOA.START_DATE)
OR JABOA.START_DATE BETWEEN NVL(START_DATE
,JABOA.START_DATE)
AND NVL(END_DATE_N
,JABOA.YEAR_ENDED) );
SELECT
OPENING_WDV
FROM
JAI_FA_AST_BLOCKS JABOA
WHERE JABOA.BOOK_TYPE_CODE = BOOK_NAME
AND JABOA.TYPE = (
SELECT
TYPE
FROM
JAI_FA_AST_BLOCKS
WHERE BLOCK_ID = CP_BLOCK_ID )
ORDER BY
START_DATE ASC;
SELECT
A.ASSET_ID,
A.COST,
A.TRANSACTION_HEADER_ID_IN,
A.DATE_EFFECTIVE
FROM
FA_BOOKS A,
JAI_FA_AST_BLOCKS B,
JAI_FA_AST_BLOCK_DTLS C,
JAI_FA_AST_PERIOD_RATES D
WHERE A.ASSET_ID = C.ASSET_ID
AND ( ( C.ASSET_TYPE = LV_CAPITALIZED
AND A.CAPITALIZE_FLAG = LV_FLAG )
OR C.ASSET_TYPE = LV_EXPENSED )
AND B.BLOCK_ID = BLOCK_ID_v
AND B.BLOCK_ID = C.BLOCK_ID
AND A.BOOK_TYPE_CODE = B.BOOK_TYPE_CODE
AND A.BOOK_TYPE_CODE = BOOK_NAME
AND C.DATE_OF_ACQUISITION between D.START_DATE
AND D.END_DATE
AND B.START_DATE between NVL(START_DATE_T,B.START_DATE)
AND NVL(END_DATE_N ,B.YEAR_ENDED)
AND D.START_DATE >= ADD_MONTHS(START_DATE_T,6)
AND D.SLNO = 2
AND A.DATE_INEFFECTIVE IS null
AND A.TRANSACTION_HEADER_ID_OUT IS null;
SELECT
NVL(SUM(C.PROCEEDS_OF_SALE - C.COST_OF_REMOVAL)
,0) SALE
FROM
JAI_FA_AST_BLOCKS A,
FA_ADDITIONS B,
FA_RETIREMENTS C,
JAI_FA_AST_PERIOD_RATES D
WHERE B.CONTEXT = P_CONTEXT_VALUE
AND TO_CHAR(A.BLOCK_ID) = B.ATTRIBUTE2
AND A.BOOK_TYPE_CODE = BOOK_NAME
AND B.OWNED_LEASED = 'OWNED'
AND B.ASSET_TYPE IN ( 'CAPITALIZED' , 'EXPENSED' )
AND TO_DATE(B.ATTRIBUTE1
,'DD-MON-RRRR') between D.START_DATE
AND D.END_DATE
AND D.START_DATE < ADD_MONTHS(START_DATE_T
,6)
AND D.SLNO = 1
AND B.ASSET_ID = C.ASSET_ID
AND C.STATUS = 'PROCESSED'
AND A.TYPE = P_BLOCK_TYPE
AND A.BLOCK_ID = BLOCK_ID_V
AND C.DATE_RETIRED BETWEEN START_DATE_T
AND END_DATE_N;
SELECT
NVL(SUM(C.PROCEEDS_OF_SALE - C.COST_OF_REMOVAL)
,0) SALE
FROM
JAI_FA_AST_BLOCKS A,
FA_ADDITIONS B,
FA_RETIREMENTS C,
JAI_FA_AST_PERIOD_RATES D
WHERE B.CONTEXT = P_CONTEXT_VALUE
AND TO_CHAR(A.BLOCK_ID) = B.ATTRIBUTE2
AND A.BOOK_TYPE_CODE = BOOK_NAME
AND B.OWNED_LEASED = 'OWNED'
AND B.ASSET_TYPE IN ( 'CAPITALIZED' , 'EXPENSED' )
AND TO_DATE(B.ATTRIBUTE1
,'DD-MON-RRRR') between D.START_DATE
AND D.END_DATE
AND D.START_DATE >= ADD_MONTHS(START_DATE_T
,6)
AND D.SLNO = 2
AND B.ASSET_ID = C.ASSET_ID
AND C.STATUS = 'PROCESSED'
AND A.TYPE = P_BLOCK_TYPE
AND A.BLOCK_ID = BLOCK_ID_V
AND C.DATE_RETIRED BETWEEN START_DATE_T
AND END_DATE_N;
SELECT
count(*)
FROM
JAI_FA_DEP_BLOCKS
WHERE TO_CHAR(BLOCK_ID) = TO_CHAR(P_BLOCK_ID)
AND SLNO = - 1;
SELECT
MAX(BLOCK_HISTORY_ID)
INTO V_BLOCK_HISTORY_ID
FROM
JAI_FA_AST_BLOCK_H;
INSERT INTO JAI_FA_AST_BLOCK_H
(BLOCK_HISTORY_ID
,BLOCK_ID
,OPENING_WDV
,CLOSING_WDV
,OPENING_WDV_ADJ
,DEPN_ADJ
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,LAST_UPDATED_BY)
VALUES (V_BLOCK_HISTORY_ID
,P_BLOCK_ID
,P_OPENING_WDV
,P_CLOSING_BALANCE
,P_OPENING_WDV_ADJ
,P_DEPN_ADJ
,SYSDATE
,UID
,SYSDATE
,UID
,UID);
UPDATE
JAI_FA_AST_BLOCKS
SET
OPENING_WDV = NVL(P_OPENING_WDV
,0) + NVL(P_OPENING_WDV_ADJ
,0)
WHERE TO_CHAR(BLOCK_ID) = TO_CHAR(P_BLOCK_ID);
,'updated JAI_FA_AST_BLOCKS...in DEPRECIATION1..')*/NULL;
,'No of rows updated in JAI_FA_AST_BLOCKS.....' || SQL%ROWCOUNT)*/NULL;
INSERT INTO JAI_FA_DEP_BLOCKS
(BLOCK_DEPN_ID
,BLOCK_ID
,SLNO
,DEPN_OF_ASSETS
,UNPLANNED_DEPN
,YEAR_ENDED
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,LAST_UPDATED_BY)
VALUES (JAI_FA_DEP_BLOCKS_S.NEXTVAL
,P_BLOCK_ID
,-1
,NULL
,P_DEPN_ADJ
,P_YEAR_END
,SYSDATE
,UID
,SYSDATE
,UID
,UID);
,'inserted into JAI_FA_DEP_BLOCKS...DEPRECIATION1')*/NULL;
UPDATE
JAI_FA_DEP_BLOCKS
SET
UNPLANNED_DEPN = P_DEPN_ADJ
WHERE TO_CHAR(BLOCK_ID) = TO_CHAR(P_BLOCK_ID)
AND SLNO = - 1;
,'no.of rows updated JAI_FA_DEP_BLOCKS...DEPRECIATION1' || SQL%ROWCOUNT)*/NULL;
SELECT
NVL(DEPN_OF_ASSETS
,0)
FROM
JAI_FA_DEP_BLOCKS
WHERE UNPLANNED_DEPN = ASSET_ID
AND TO_CHAR(BLOCK_ID) = TO_CHAR(BLOCK_ID1);
SELECT
DISTINCT
A.ASSET_ID,
B.BLOCK_ID,
B.OPENING_WDV,
B.RATE BLOCK_RATE,
C.SLNO,
C.START_DATE,
C.END_DATE,
C.RATE PERIOD_RATE,
C.EXEMPT_UPTO,
A.DATE_OF_ACQUISITION AQUISATION_DATE,
D.DATE_PLACED_IN_SERVICE,
E.CURRENT_UNITS
FROM
JAI_FA_AST_BLOCK_DTLS A,
JAI_FA_AST_BLOCKS B,
JAI_FA_AST_PERIOD_RATES C,
FA_BOOKS D,
FA_ADDITIONS E
WHERE E.OWNED_LEASED = LV_OWNED
AND E.ASSET_ID = A.ASSET_ID
AND NVL(A.ASSET_ID
,0) = NVL(D.ASSET_ID
,0)
AND D.DATE_PLACED_IN_SERVICE between C.START_DATE
AND C.END_DATE
AND B.START_DATE >= P_YEAR_START
AND B.START_DATE <= P_YEAR_END
AND C.YEAR_START = P_YEAR_START
AND C.YEAR_END = P_YEAR_END
AND A.BLOCK_ID = B.BLOCK_ID
AND B.BOOK_TYPE_CODE = P_BOOK_NAME
AND D.DATE_INEFFECTIVE is null
AND D.TRANSACTION_HEADER_ID_OUT is null
AND ( ( A.ASSET_TYPE = LV_CAPITALIZED
AND D.CAPITALIZE_FLAG = LV_FLAG )
OR A.ASSET_TYPE = LV_EXPENSED )
ORDER BY
A.ASSET_ID;
SELECT
count(*)
FROM
JAI_FA_AST_BLOCK_DTLS A,
JAI_FA_AST_BLOCKS B,
JAI_FA_AST_PERIOD_RATES C,
FA_BOOKS D,
FA_ADDITIONS E
WHERE E.OWNED_LEASED = 'OWNED'
AND E.ASSET_ID = A.ASSET_ID
AND NVL(A.ASSET_ID
,0) = NVL(D.ASSET_ID
,0)
AND D.DATE_PLACED_IN_SERVICE between C.START_DATE
AND C.END_DATE
AND B.START_DATE >= P_YEAR_START
AND B.START_DATE <= P_YEAR_END
AND C.YEAR_START = P_YEAR_START
AND C.YEAR_END = P_YEAR_END
AND A.BLOCK_ID = B.BLOCK_ID
AND B.BLOCK_ID = P_BLOCK_ID
AND B.BOOK_TYPE_CODE = P_BOOK_NAME
AND D.BOOK_TYPE_CODE = B.BOOK_TYPE_CODE
AND NVL(B.CLOSING_WDV
,0) <> 0
AND B.YEAR_ENDED is NOT null
AND D.DATE_INEFFECTIVE is null
AND D.TRANSACTION_HEADER_ID_OUT is null
AND ( ( A.ASSET_TYPE = LV_CAPITALIZED
AND D.CAPITALIZE_FLAG = LV_FLAG )
OR A.ASSET_TYPE = LV_EXPENSED )
ORDER BY
A.ASSET_ID;
SELECT
A.BLOCK_ID,
NVL(SUM(C.PROCEEDS_OF_SALE)
,0) SALE
FROM
JAI_FA_AST_BLOCKS A,
JAI_FA_AST_BLOCK_DTLS B,
FA_RETIREMENTS C
WHERE A.BLOCK_ID = B.BLOCK_ID
AND A.BOOK_TYPE_CODE = P_BOOK_NAME
AND B.ASSET_ID = C.ASSET_ID
AND C.DATE_RETIRED between P_YEAR_START
AND P_YEAR_END
GROUP BY
A.BLOCK_ID;
SELECT
TYPE,
RATE
FROM
JAI_FA_AST_BLOCKS
WHERE TO_CHAR(BLOCK_ID) = TO_CHAR(P_BLOCK_ID)
AND BOOK_TYPE_CODE = P_BOOK_NAME;
SELECT
BLOCK_ID,
OPENING_WDV,
RATE,
TYPE,
OPENING_WDV_ADJ,
DEPN_ADJ
FROM
JAI_FA_AST_BLOCKS
WHERE TYPE = P_TYPE
AND RATE = P_RATE
AND BOOK_TYPE_CODE = P_BOOK_NAME
AND START_DATE BETWEEN P_YEAR_START
AND P_YEAR_END;
SELECT
DISTINCT
SUM(NVL(A.PROCEEDS_OF_SALE
,0)) SALE
FROM
FA_RETIREMENTS A,
JAI_FA_AST_BLOCK_DTLS B,
JAI_FA_AST_BLOCKS C
WHERE A.ASSET_ID = B.ASSET_ID
AND B.BLOCK_ID = P_BLOCK_ID
AND B.BLOCK_ID = C.BLOCK_ID
AND A.BOOK_TYPE_CODE = P_BOOK_NAME
AND A.DATE_RETIRED between P_YEAR_START
AND P_YEAR_END
ORDER BY
B.BLOCK_ID;
SELECT
SUM(A.ORIGINAL_COST) COSTING,
A.ASSET_ID,
A.DATE_PLACED_IN_SERVICE
FROM
FA_BOOKS A,
JAI_FA_AST_BLOCK_DTLS B,
FA_ADDITIONS C
WHERE A.ASSET_ID = P_ASSET_ID
AND A.ASSET_ID = B.ASSET_ID
AND A.DATE_INEFFECTIVE is null
AND A.TRANSACTION_HEADER_ID_OUT is null
AND ( ( B.ASSET_TYPE = LV_CAPITALIZED
AND A.CAPITALIZE_FLAG = LV_FLAG )
OR B.ASSET_TYPE = LV_EXPENSED )
AND B.BLOCK_ID = P_BLOCK_ID
AND A.ASSET_ID = C.ASSET_ID
AND A.BOOK_TYPE_CODE = P_BOOK_NAME
AND A.DATE_PLACED_IN_SERVICE between P_START_DATE
AND P_END_DATE
AND ( A.ORIGINAL_COST / C.CURRENT_UNITS ) <= NVL(P_EXEMPT_UPTO
,0)
GROUP BY
A.ASSET_ID,
A.DATE_PLACED_IN_SERVICE;
SELECT
SUM(A.ORIGINAL_COST) COSTING,
A.ASSET_ID,
A.DATE_PLACED_IN_SERVICE
FROM
FA_BOOKS A,
JAI_FA_AST_BLOCK_DTLS B,
FA_ADDITIONS C
WHERE A.ASSET_ID = P_ASSET_ID
AND A.ASSET_ID = B.ASSET_ID
AND A.ASSET_ID = B.ASSET_ID
AND A.DATE_INEFFECTIVE is null
AND A.TRANSACTION_HEADER_ID_OUT is null
AND ( ( B.ASSET_TYPE = LV_CAPITALIZED
AND A.CAPITALIZE_FLAG = LV_FLAG )
OR B.ASSET_TYPE = LV_EXPENSED )
AND B.BLOCK_ID = P_BLOCK_ID
AND A.BOOK_TYPE_CODE = P_BOOK_NAME
AND A.DATE_PLACED_IN_SERVICE between P_START_DATE
AND P_END_DATE
AND ( A.ORIGINAL_COST / C.CURRENT_UNITS ) > NVL(P_EXEMPT_UPTO
,0)
GROUP BY
A.ASSET_ID,
A.DATE_PLACED_IN_SERVICE;
SELECT
SUM(DEPN_OF_ASSETS) ASSETS,
SUM(TOTAL_BALANCE) BALANCE,
SUM(SLNO) COST,
SUM(FULL_EXEMPT) COST_FULL,
BLOCK_ID
FROM
JAI_FA_DEP_BLOCKS_T
GROUP BY
BLOCK_ID;
SELECT
TYPE,
RATE,
OPENING_WDV,
OPENING_WDV_ADJ,
DEPN_ADJ
FROM
JAI_FA_AST_BLOCKS
WHERE BLOCK_ID = P_BLOCK_ID
AND BOOK_TYPE_CODE = P_BOOK_NAME;
SELECT
SUM(EXEMPT_AMOUNT) L_AMOUNT,
BLOCK_ID
FROM
JAI_FA_EXEMPTIONS
WHERE BLOCK_ID = P_BLOCK_ID
GROUP BY
BLOCK_ID;
SELECT
A.UNITS,
B.BLOCK_ID
FROM
FA_RETIREMENTS A,
JAI_FA_AST_BLOCK_DTLS B
WHERE A.DATE_RETIRED <= P_YEAR_END
AND A.ASSET_ID = B.ASSET_ID
AND A.STATUS = 'PROCESSED'
AND A.ASSET_ID = P_ASSET_ID
AND B.BLOCK_ID = TO_CHAR(P_BLOCK_ID);
SELECT
DISTINCT
H.UNITS,
H.ASSET_ID
FROM
FA_ASSET_HISTORY H,
JAI_FA_AST_BLOCK_DTLS A
WHERE TRANSACTION_HEADER_ID_IN IN (
SELECT
MIN(TRANSACTION_HEADER_ID_IN)
FROM
FA_ASSET_HISTORY
GROUP BY
ASSET_ID )
AND A.ASSET_ID = H.ASSET_ID
AND A.BLOCK_ID = P_BLOCK_ID
AND A.ASSET_ID = P_ASSET_ID;
SELECT
BLOCK_ID
FROM
JAI_FA_AST_BLOCKS
WHERE TO_CHAR(BLOCK_ID) NOT IN (
SELECT
BLOCK_ID
FROM
JAI_FA_AST_BLOCK_DTLS )
AND START_DATE >= P_YEAR_START
AND START_DATE <= P_YEAR_END;
SELECT
DISTINCT
B.OPENING_WDV,
B.OPENING_WDV_ADJ,
B.DEPN_ADJ,
B.RATE BLOCK_RATE,
B.BLOCK_ID,
B.TYPE
FROM
JAI_FA_AST_BLOCKS B,
JAI_FA_AST_PERIOD_RATES C
WHERE B.START_DATE >= P_YEAR_START
AND B.START_DATE <= P_YEAR_END
AND C.YEAR_START >= P_YEAR_START
AND C.YEAR_END <= P_YEAR_END
AND B.BOOK_TYPE_CODE = P_BOOK_NAME
AND B.BLOCK_ID = P_BLOCK_ID;
SELECT
count(*)
FROM
JAI_FA_AST_BLOCKS
WHERE CLOSING_WDV is not null
AND YEAR_ENDED is not null
AND BLOCK_ID = P_BLOCK_ID
AND BOOK_TYPE_CODE = P_BOOK_NAME
AND START_DATE >= P_YEAR_START
AND START_DATE <= P_YEAR_END;
SELECT
COUNT(*)
FROM
FA_RETIREMENTS
WHERE DATE_RETIRED BETWEEN P_YEAR_START
AND P_YEAR_END;
SELECT
count(*)
FROM
FA_RETIREMENTS A,
JAI_FA_AST_BLOCK_DTLS B,
JAI_FA_AST_BLOCKS C
WHERE A.DATE_RETIRED BETWEEN P_YEAR_START
AND P_YEAR_END
AND A.ASSET_ID = B.ASSET_ID
AND B.BLOCK_ID = C.BLOCK_ID
AND C.START_DATE between P_YEAR_START
AND P_YEAR_END;
SELECT
ASSET_ID
FROM
JAI_FA_AST_BLOCK_DTLS
WHERE BLOCK_ID = P_BLOCK_ID;
SELECT
RATE,
TYPE
FROM
JAI_FA_AST_BLOCKS
WHERE START_DATE BETWEEN P_YEAR_START
AND P_YEAR_END
AND OPENING_WDV_ADJ is not null
OR DEPN_ADJ is not null
AND BOOK_TYPE_CODE = P_BOOK_NAME
AND TO_CHAR(BLOCK_ID) not in (
SELECT
BLOCK_ID
FROM
JAI_FA_AST_BLOCK_DTLS );
SELECT
RATE,
TYPE
FROM
JAI_FA_AST_BLOCKS
WHERE START_DATE BETWEEN P_YEAR_START
AND P_YEAR_END
AND BOOK_TYPE_CODE = P_BOOK_NAME
AND CLOSING_WDV > 0
AND TO_CHAR(BLOCK_ID) in (
SELECT
BLOCK_ID
FROM
JAI_FA_AST_BLOCK_DTLS );
SELECT
BLOCK_ID,
OPENING_WDV,
CLOSING_WDV,
RATE,
START_DATE
FROM
JAI_FA_AST_BLOCKS
WHERE RATE = P_RATE
AND TYPE = P_TYPE
AND START_DATE > P_YEAR_END
AND BOOK_TYPE_CODE = P_BOOK_NAME;
SELECT
YEAR_END
FROM
JAI_FA_AST_YEARS
WHERE YEAR_START = P_START_DATE;
SELECT
count(*)
FROM
JAI_FA_DEP_BLOCKS
WHERE SLNO > 0
AND BLOCK_ID = P_BLOCK_ID
AND SLNO = P_SLNO
AND UNPLANNED_DEPN = P_ASSET_ID;
SELECT
UNPLANNED_DEPN
FROM
JAI_FA_DEP_BLOCKS
WHERE BLOCK_ID = P_BLOCK_ID
AND SLNO = - 1;
UPDATE
JAI_FA_AST_BLOCKS
SET
CLOSING_WDV = NVL(V_CLOSING_BALANCE
,0) - NVL(BLOCK_ID_NEW.DEPN_ADJ
,0)
,YEAR_ENDED = P_YEAR_END
WHERE BLOCK_ID = BLOCK_ID_NEW.BLOCK_ID;
SELECT
JAI_FA_AST_BLOCKS_S.NEXTVAL
INTO V_BLOCK_ID1
FROM
DUAL;
INSERT INTO JAI_FA_AST_BLOCKS
(BLOCK_ID
,TYPE
,RATE
,BOOK_TYPE_CODE
,OPENING_WDV
,START_DATE
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,LAST_UPDATED_BY)
VALUES (V_BLOCK_ID1
,BLOCK_ID_NEW.TYPE
,BLOCK_ID_NEW.RATE
,P_BOOK_NAME
,NVL(V_CLOSING_BALANCE
,0) - NVL(BLOCK_ID_NEW.DEPN_ADJ
,0)
,P_YEAR_END + 1
,SYSDATE
,UID
,SYSDATE
,UID
,UID);
INSERT INTO JAI_FA_DEP_BLOCKS
(BLOCK_DEPN_ID
,BLOCK_ID
,SLNO
,DEPN_OF_ASSETS
,YEAR_ENDED
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,LAST_UPDATED_BY)
VALUES (JAI_FA_DEP_BLOCKS_S.NEXTVAL
,BLOCK_ID_NEW.BLOCK_ID
,0
,V_DEPRECIATION
,P_YEAR_END
,SYSDATE
,UID
,SYSDATE
,UID
,UID);
UPDATE
JAI_FA_DEP_BLOCKS
SET
DEPN_OF_ASSETS = V_DEPRECIATION
WHERE BLOCK_ID = BLOCK_ID_NEW.BLOCK_ID
AND SLNO = 0;
UPDATE
JAI_FA_AST_BLOCKS
SET
OPENING_WDV = NVL(V_CLOSING_BALANCE
,0) - NVL(V_UNPLANNED_DEPN
,0)
WHERE START_DATE = P_YEAR_END + 1
AND TYPE = BLOCK_ID_NEW.TYPE
AND RATE = BLOCK_ID_NEW.RATE;
UPDATE
JAI_FA_AST_BLOCKS
SET
CLOSING_WDV = NVL(V_CLOSING_BALANCE
,0) - NVL(V_UNPLANNED_DEPN
,0)
,YEAR_ENDED = P_YEAR_END
WHERE BLOCK_ID = BLOCK_ID_NEW.BLOCK_ID;
UPDATE
JAI_FA_DEP_BLOCKS
SET
UNPLANNED_DEPN = BLOCK_ID_NEW.DEPN_ADJ
WHERE BLOCK_ID = BLOCK_ID_NEW.BLOCK_ID
AND SLNO = 0;
SELECT
DISTINCT
A.ASSET_ID,
B.BLOCK_ID,
B.OPENING_WDV,
B.RATE BLOCK_RATE,
C.SLNO,
C.START_DATE,
C.END_DATE,
C.RATE PERIOD_RATE,
C.EXEMPT_UPTO,
NVL(E.DATE_OF_ACQUISITION
,B.START_DATE) AQUISATION_DATE,
D.DATE_PLACED_IN_SERVICE,
A.CURRENT_UNITS
FROM
FA_ADDITIONS A,
JAI_FA_AST_BLOCKS B,
JAI_FA_AST_PERIOD_RATES C,
FA_BOOKS D,
JAI_FA_AST_BLOCK_DTLS E
WHERE A.OWNED_LEASED = LV_OWNED_LEASED
AND ( ( A.ASSET_TYPE = LV_CAPITALIZED
AND D.CAPITALIZE_FLAG = LV_FLAG )
OR A.ASSET_TYPE = LV_EXPENSED )
AND A.ASSET_ID = D.ASSET_ID
AND A.ASSET_ID = E.ASSET_ID
AND E.DATE_OF_ACQUISITION BETWEEN C.START_DATE
AND C.END_DATE
AND B.START_DATE >= P_YEAR_START
AND B.START_DATE <= P_YEAR_END
AND C.YEAR_START = P_YEAR_START
AND C.YEAR_END = P_YEAR_END
AND E.BLOCK_ID = B.BLOCK_ID
AND B.BOOK_TYPE_CODE = P_BOOK_NAME
AND D.DATE_INEFFECTIVE IS null
AND D.TRANSACTION_HEADER_ID_OUT IS null;
SELECT
BLOCK_ID,
TYPE
FROM
JAI_FA_AST_BLOCKS
WHERE BLOCK_ID NOT IN (
SELECT
BLOCK_ID
FROM
JAI_FA_AST_BLOCK_DTLS
WHERE DATE_OF_ACQUISITION BETWEEN P_YEAR_START
AND P_YEAR_END )
AND START_DATE >= P_YEAR_START
AND START_DATE <= P_YEAR_END
AND BOOK_TYPE_CODE = P_BOOK_NAME;
SELECT
DISTINCT
B.OPENING_WDV,
B.OPENING_WDV_ADJ,
B.DEPN_ADJ,
B.RATE BLOCK_RATE,
B.BLOCK_ID,
B.TYPE
FROM
JAI_FA_AST_BLOCKS B,
JAI_FA_AST_PERIOD_RATES C
WHERE B.START_DATE >= P_YEAR_START
AND B.START_DATE <= P_YEAR_END
AND C.YEAR_START >= P_YEAR_START
AND C.YEAR_END <= P_YEAR_END
AND B.BOOK_TYPE_CODE = P_BOOK_NAME
AND B.BLOCK_ID = P_BLOCK_ID;
SELECT
NVL(SUM(A.PROCEEDS_OF_SALE - A.COST_OF_REMOVAL)
,0) SALE
FROM
FA_RETIREMENTS A,
JAI_FA_AST_BLOCK_DTLS B,
JAI_FA_AST_BLOCKS C,
FA_BOOKS D
WHERE A.ASSET_ID = B.ASSET_ID
AND ( ( B.ASSET_TYPE = LV_CAPITALIZED
AND D.CAPITALIZE_FLAG = LV_FLAG )
OR B.ASSET_TYPE = LV_EXPENSED )
AND D.ASSET_ID = B.ASSET_ID
AND C.BLOCK_ID = B.BLOCK_ID
AND A.STATUS = LV_STATUS
AND C.TYPE = P_TYPE
AND A.DATE_RETIRED BETWEEN P_YEAR_START
AND P_YEAR_END
AND A.RETIREMENT_ID = D.RETIREMENT_ID
AND D.BOOK_TYPE_CODE = C.BOOK_TYPE_CODE
AND C.BOOK_TYPE_CODE = P_BOOK_NAME;
SELECT
NVL(SUM(C.PROCEEDS_OF_SALE - C.COST_OF_REMOVAL)
,0) SALE
FROM
JAI_FA_AST_BLOCKS A,
JAI_FA_AST_BLOCK_DTLS B,
FA_RETIREMENTS C
WHERE A.BLOCK_ID = B.BLOCK_ID
AND A.BOOK_TYPE_CODE = P_BOOK_NAME
AND B.ASSET_ID = C.ASSET_ID
AND C.STATUS = LV_STATUS
AND A.TYPE = P_BLOCK_TYPE
AND C.DATE_RETIRED BETWEEN P_YEAR_START
AND P_YEAR_END;
SELECT
DISTINCT
SUM(NVL(A.PROCEEDS_OF_SALE
,0)) SALE
FROM
FA_RETIREMENTS A,
JAI_FA_AST_BLOCK_DTLS B,
JAI_FA_AST_BLOCKS C
WHERE A.ASSET_ID = B.ASSET_ID
AND B.ASSET_TYPE IN ( LV_CAPITALIZED , LV_EXPENSED )
AND A.STATUS = LV_STATUS
AND B.BLOCK_ID = P_BLOCK_ID
AND B.BLOCK_ID = C.BLOCK_ID
AND A.BOOK_TYPE_CODE = P_BOOK_NAME
AND A.DATE_RETIRED BETWEEN P_YEAR_START
AND P_YEAR_END
ORDER BY
B.BLOCK_ID;
SELECT
A.COST COSTING,
A.ASSET_ID,
A.DATE_PLACED_IN_SERVICE
FROM
FA_BOOKS A,
FA_ADDITIONS B,
JAI_FA_AST_BLOCK_DTLS C
WHERE A.ASSET_ID = P_ASSET_ID
AND A.ASSET_ID = C.ASSET_ID
AND ( ( B.ASSET_TYPE = LV_CAPITALIZED
AND A.CAPITALIZE_FLAG = LV_FLAG )
OR B.ASSET_TYPE = LV_EXPENSED )
AND A.ASSET_ID = B.ASSET_ID
AND A.TRANSACTION_HEADER_ID_IN = CP_TRANSACTION_HEADER_ID_IN
AND C.BLOCK_ID = TO_CHAR(P_BLOCK_ID)
AND A.BOOK_TYPE_CODE = P_BOOK_NAME
AND C.DATE_OF_ACQUISITION BETWEEN P_START_DATE
AND P_END_DATE
AND ( A.COST / B.CURRENT_UNITS ) <= NVL(P_EXEMPT_UPTO
,0);
SELECT
A.COST COSTING,
A.ASSET_ID,
A.DATE_PLACED_IN_SERVICE
FROM
FA_BOOKS A,
FA_ADDITIONS B,
JAI_FA_AST_BLOCK_DTLS C
WHERE A.ASSET_ID = P_ASSET_ID
AND A.ASSET_ID = C.ASSET_ID
AND ( ( B.ASSET_TYPE = LV_CAPITALIZED
AND A.CAPITALIZE_FLAG = LV_FLAG )
OR B.ASSET_TYPE = LV_EXPENSED )
AND A.ASSET_ID = B.ASSET_ID
AND A.TRANSACTION_HEADER_ID_IN = CP_TRANSACTION_HEADER_ID_IN
AND C.BLOCK_ID = TO_CHAR(P_BLOCK_ID)
AND A.BOOK_TYPE_CODE = P_BOOK_NAME
AND C.DATE_OF_ACQUISITION BETWEEN P_START_DATE
AND P_END_DATE
AND ( A.COST / B.CURRENT_UNITS ) > NVL(P_EXEMPT_UPTO
,0);
SELECT
BLOCK_ID BLOCKID,
SUM(DEPN_OF_ASSETS) ASSETS,
SUM(TOTAL_BALANCE) BALANCE,
SUM(SLNO) COST,
SUM(FULL_EXEMPT) COST_FULL,
BLOCK_ID
FROM
JAI_FA_DEP_BLOCKS_T
GROUP BY
BLOCK_ID;
SELECT
TYPE,
RATE,
OPENING_WDV,
OPENING_WDV_ADJ,
DEPN_ADJ
FROM
JAI_FA_AST_BLOCKS
WHERE BLOCK_ID = P_BLOCK_ID
AND BOOK_TYPE_CODE = P_BOOK_NAME
AND START_DATE >= P_YEAR_START
AND START_DATE <= P_YEAR_END;
SELECT
SUM(EXEMPT_AMOUNT) L_AMOUNT,
BLOCK_ID
FROM
JAI_FA_EXEMPTIONS
WHERE BLOCK_ID = P_BLOCK_ID
GROUP BY
BLOCK_ID;
SELECT
A.UNITS,
TO_NUMBER(B.BLOCK_ID)
FROM
FA_RETIREMENTS A,
JAI_FA_AST_BLOCK_DTLS B
WHERE A.DATE_RETIRED <= P_YEAR_END
AND A.ASSET_ID = B.ASSET_ID
AND A.STATUS = LV_STATUS
AND A.ASSET_ID = P_ASSET_ID
AND B.BLOCK_ID = P_BLOCK_ID;
SELECT
DISTINCT
H.UNITS,
H.ASSET_ID
FROM
FA_ASSET_HISTORY H,
JAI_FA_AST_BLOCK_DTLS A
WHERE H.TRANSACTION_HEADER_ID_IN IN (
SELECT
MIN(TRANSACTION_HEADER_ID_IN)
FROM
FA_ASSET_HISTORY
GROUP BY
ASSET_ID )
AND A.ASSET_ID = H.ASSET_ID
AND A.BLOCK_ID = P_BLOCK_ID
AND A.ASSET_ID = P_ASSET_ID;
SELECT
count(*)
FROM
FA_ADDITIONS A,
JAI_FA_AST_BLOCKS B,
JAI_FA_AST_PERIOD_RATES C,
FA_BOOKS D,
JAI_FA_AST_BLOCK_DTLS E
WHERE A.OWNED_LEASED = LV_OWNED_LEASED
AND ( ( A.ASSET_TYPE = LV_CAPITALIZED
AND D.CAPITALIZE_FLAG = LV_FLAG )
OR A.ASSET_TYPE = LV_EXPENSED )
AND NVL(A.ASSET_ID
,0) = NVL(D.ASSET_ID
,0)
AND NVL(A.ASSET_ID
,0) = E.ASSET_ID
AND E.DATE_OF_ACQUISITION BETWEEN C.START_DATE
AND C.END_DATE
AND B.START_DATE >= P_YEAR_START
AND B.START_DATE <= P_YEAR_END
AND C.YEAR_START = P_YEAR_START
AND C.YEAR_END = P_YEAR_END
AND E.BLOCK_ID = B.BLOCK_ID
AND B.BLOCK_ID = P_BLOCK_ID
AND B.BOOK_TYPE_CODE = P_BOOK_NAME
AND D.BOOK_TYPE_CODE = B.BOOK_TYPE_CODE
AND B.CLOSING_WDV IS NOT NULL
AND B.YEAR_ENDED IS NOT NULL
AND D.DATE_INEFFECTIVE IS NULL
AND D.TRANSACTION_HEADER_ID_OUT IS NULL
ORDER BY
1;
SELECT
count(*)
FROM
JAI_FA_AST_BLOCKS
WHERE CLOSING_WDV IS not null
AND YEAR_ENDED IS not null
AND BLOCK_ID = P_BLOCK_ID
AND BOOK_TYPE_CODE = P_BOOK_NAME
AND START_DATE >= P_YEAR_START
AND START_DATE <= P_YEAR_END;
SELECT
count(*)
FROM
FA_RETIREMENTS
WHERE DATE_RETIRED BETWEEN P_YEAR_START
AND P_YEAR_END;
SELECT
COUNT(*)
FROM
FA_RETIREMENTS A,
JAI_FA_AST_BLOCK_DTLS B,
JAI_FA_AST_BLOCKS C
WHERE A.DATE_RETIRED BETWEEN P_YEAR_START
AND P_YEAR_END
AND A.ASSET_ID = B.ASSET_ID
AND B.BLOCK_ID = C.BLOCK_ID
AND C.START_DATE BETWEEN P_YEAR_START
AND P_YEAR_END;
SELECT
ASSET_ID
FROM
JAI_FA_AST_BLOCK_DTLS
WHERE BLOCK_ID = P_BLOCK_ID;
SELECT
RATE,
TYPE
FROM
JAI_FA_AST_BLOCKS
WHERE START_DATE BETWEEN P_YEAR_START
AND P_YEAR_END
AND NVL(OPENING_WDV_ADJ
,DEPN_ADJ) IS NOT NULL
AND BOOK_TYPE_CODE = P_BOOK_NAME;
SELECT
JABOA.RATE,
JABOA.TYPE
FROM
JAI_FA_AST_BLOCKS JABOA,
JAI_FA_AST_BLOCK_DTLS JBA
WHERE JABOA.START_DATE BETWEEN P_YEAR_START
AND P_YEAR_END
AND JABOA.BOOK_TYPE_CODE = P_BOOK_NAME
AND JABOA.CLOSING_WDV > 0
AND JABOA.BLOCK_ID = JBA.BLOCK_ID;
SELECT
BLOCK_ID,
OPENING_WDV,
CLOSING_WDV,
RATE,
START_DATE
FROM
JAI_FA_AST_BLOCKS
WHERE RATE = P_RATE
AND TYPE = P_TYPE
AND START_DATE > P_YEAR_END
AND BOOK_TYPE_CODE = P_BOOK_NAME;
SELECT
YEAR_END
FROM
JAI_FA_AST_YEARS
WHERE YEAR_START = P_START_DATE;
SELECT
COUNT(*)
FROM
JAI_FA_DEP_BLOCKS
WHERE SLNO > 0
AND BLOCK_ID = P_BLOCK_ID
AND SLNO = P_SLNO
AND UNPLANNED_DEPN = P_ASSET_ID;
SELECT
UNPLANNED_DEPN
FROM
JAI_FA_DEP_BLOCKS
WHERE BLOCK_ID = P_BLOCK_ID
AND SLNO = - 1;
SELECT
COUNT(*)
FROM
JAI_FA_DEP_BLOCKS
WHERE BLOCK_ID = P_BLOCK_ID
AND SLNO = 0;
UPDATE
JAI_FA_AST_BLOCKS
SET
CLOSING_WDV = NVL(V_CLOSING_BALANCE
,0) - NVL(CLOSING.DEPN_ADJ
,0)
,YEAR_ENDED = P_YEAR_END
WHERE BLOCK_ID = CLOSING.BLOCK_ID;
SELECT
JAI_FA_AST_BLOCKS_S.NEXTVAL
INTO V_BLOCK_ID1
FROM
DUAL;
INSERT INTO JAI_FA_AST_BLOCKS
(BLOCK_ID
,TYPE
,RATE
,BOOK_TYPE_CODE
,OPENING_WDV
,START_DATE
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,LAST_UPDATED_BY)
VALUES (V_BLOCK_ID1
,CLOSING.TYPE
,CLOSING.BLOCK_RATE
,P_BOOK_NAME
,NVL(V_CLOSING_BALANCE
,0) - NVL(CLOSING.DEPN_ADJ
,0)
,P_YEAR_END + 1
,SYSDATE
,UID
,SYSDATE
,UID
,UID);
,'2.11 INSERT INTO JAI_FA_AST_BLOCKS')*/NULL;
INSERT INTO JAI_FA_DEP_BLOCKS
(BLOCK_DEPN_ID
,BLOCK_ID
,SLNO
,DEPN_OF_ASSETS
,YEAR_ENDED
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,LAST_UPDATED_BY
,UNPLANNED_DEPN)
VALUES (JAI_FA_DEP_BLOCKS_S.NEXTVAL
,CLOSING.BLOCK_ID
,0
,V_DEPRECIATION
,P_YEAR_END
,SYSDATE
,UID
,SYSDATE
,UID
,UID
,CLOSING.DEPN_ADJ);
,'UPDATE INTO JAI_FA_DEP_BLOCKS')*/NULL;
UPDATE
JAI_FA_DEP_BLOCKS
SET
DEPN_OF_ASSETS = V_DEPRECIATION
WHERE BLOCK_ID = CLOSING.BLOCK_ID
AND SLNO = 0;
,'2.12 update JAI_FA_DEP_BLOCKS ')*/NULL;
UPDATE
JAI_FA_AST_BLOCKS
SET
OPENING_WDV = NVL(V_CLOSING_BALANCE
,0) - NVL(V_UNPLANNED_DEPN
,0)
WHERE START_DATE = P_YEAR_END + 1
AND TYPE = CLOSING.TYPE
AND BOOK_TYPE_CODE = P_BOOK_NAME;
,'2.13 Update opening wdv JAI_FA_AST_BLOCKS -> ' || SQL%ROWCOUNT)*/NULL;
UPDATE
JAI_FA_AST_BLOCKS
SET
CLOSING_WDV = NVL(V_CLOSING_BALANCE
,0) - NVL(V_UNPLANNED_DEPN
,0)
,YEAR_ENDED = P_YEAR_END
WHERE BLOCK_ID = CLOSING.BLOCK_ID;
,'2.14 Update closing wdv JAI_FA_AST_BLOCKS -> ' || SQL%ROWCOUNT)*/NULL;
UPDATE
JAI_FA_DEP_BLOCKS
SET
UNPLANNED_DEPN = CLOSING.DEPN_ADJ
WHERE BLOCK_ID = CLOSING.BLOCK_ID
AND SLNO = 0;
,'3.3 inserting INTO JAI_FA_DEP_BLOCKS, v_block_id -> ' || V_BLOCK_ID || ', v_depn_amount -> ' || NVL(V_DEPN_AMOUNT
,0))*/NULL;
INSERT INTO JAI_FA_DEP_BLOCKS
(BLOCK_DEPN_ID
,BLOCK_ID
,SLNO
,DEPN_OF_ASSETS
,YEAR_ENDED
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,LAST_UPDATED_BY
,UNPLANNED_DEPN)
VALUES (JAI_FA_DEP_BLOCKS_S.NEXTVAL
,V_BLOCK_ID
,V_SERIAL_NUM1
,NVL(V_DEPN_AMOUNT
,0)
,P_YEAR_END
,SYSDATE
,UID
,SYSDATE
,UID
,UID
,V_ASSET_ID);
,'3.81 Before insert into JAI_FA_DEP_BLOCKS_T block_id ' || V_BLOCK_ID || ' , ' || 'slno' || ROUND(NVL(V_COST
,0)
,2) || ' , ' || 'depn_of_assets ' || ROUND(NVL(V_MORE_AMOUNT
,0)
,2) || ' , ' || 'year_ended ' || P_YEAR_END || ' , ' || 'full_exempt ' || NVL(V_MORE_AMOUNT1
,0) || ' , ' || 'total_balance ' || ROUND(NVL(V_CLOSING_BALANCE
,0)
,2) || ' , ' || 'unplanned_depn ' || V_SLNO || ' , ' || 'asset_id ' || V_ASSET_ID)*/NULL;
INSERT INTO JAI_FA_DEP_BLOCKS_T
(BLOCK_ID
,SLNO
,DEPN_OF_ASSETS
,YEAR_ENDED
,FULL_EXEMPT
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,LAST_UPDATED_BY
,TOTAL_BALANCE
,UNPLANNED_DEPN
,ASSET_ID)
VALUES (V_BLOCK_ID
,ROUND(NVL(V_COST
,0)
,2)
,ROUND(NVL(V_MORE_AMOUNT
,0)
,2)
,P_YEAR_END
,NVL(V_MORE_AMOUNT1
,0)
,SYSDATE
,UID
,SYSDATE
,UID
,UID
,ROUND(NVL(V_CLOSING_BALANCE
,0)
,2)
,V_SLNO
,V_ASSET_ID);
,'3.82 After insert into JAI_FA_DEP_BLOCKS_T')*/NULL;
INSERT INTO JAI_FA_DEP_BLOCKS
(BLOCK_DEPN_ID
,BLOCK_ID
,SLNO
,DEPN_OF_ASSETS
,YEAR_ENDED
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,LAST_UPDATED_BY
,UNPLANNED_DEPN)
VALUES (JAI_FA_DEP_BLOCKS_S.NEXTVAL
,V_BLOCK_ID
,V_SERIAL_NUM1
,NVL(V_DEPN_AMOUNT
,0)
,P_YEAR_END
,SYSDATE
,UID
,SYSDATE
,UID
,UID
,V_ASSET_ID);
INSERT INTO JAI_FA_EXEMPTIONS
(BLOCK_ID
,EXEMPT_AMOUNT
,FA_EXEMPTION_ID
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,OBJECT_VERSION_NUMBER)
VALUES (V_BLOCK_ID
,ROUND(NVL(V_COST_LESS
,0)
,2)
,JAI_FA_EXEMPTIONS_S.NEXTVAL
,FND_GLOBAL.USER_ID
,SYSDATE
,FND_GLOBAL.USER_ID
,SYSDATE
,FND_GLOBAL.LOGIN_ID
,NULL);
,'4.2 Inserted into JAI_FA_EXEMPTIONS, v_cost_less -> ' || V_COST_LESS || ', v_depn_slno -> ' || V_DEPN_SLNO)*/NULL;
,'4.3 Inserted into JAI_FA_DEP_BLOCKS, V_block_id -> ' || V_BLOCK_ID || ', v_cost_less -> ' || NVL(V_COST_LESS
,0) || ', v_slno -> ' || V_SLNO)*/NULL;
INSERT INTO JAI_FA_DEP_BLOCKS
(BLOCK_DEPN_ID
,BLOCK_ID
,SLNO
,DEPN_OF_ASSETS
,FULL_EXEMPT
,YEAR_ENDED
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,LAST_UPDATED_BY
,UNPLANNED_DEPN)
VALUES (JAI_FA_DEP_BLOCKS_S.NEXTVAL
,V_BLOCK_ID
,V_SLNO
,NVL(V_COST_LESS
,0)
,NVL(V_COST_LESS
,0)
,P_YEAR_END
,SYSDATE
,UID
,SYSDATE
,UID
,UID
,V_ASSET_ID);
SELECT
'1'
FROM
JAI_FA_AST_BLOCK_DTLS JBA,
FA_BOOKS FAB,
JAI_FA_AST_BLOCKS JABOA
WHERE JBA.ASSET_ID = FAB.ASSET_ID
AND ( ( JBA.ASSET_TYPE = 'CAPITALIZED'
AND FAB.CAPITALIZE_FLAG = 'YES' )
OR JBA.ASSET_TYPE = 'EXPENSED' )
AND JBA.DATE_OF_ACQUISITION BETWEEN JABOA.START_DATE
AND NVL(JABOA.YEAR_ENDED
,P_YEAR_END)
AND FAB.DATE_INEFFECTIVE IS NULL
AND FAB.TRANSACTION_HEADER_ID_OUT IS NULL
AND FAB.PERIOD_COUNTER_FULLY_RETIRED IS NULL
AND JBA.BLOCK_ID = JABOA.BLOCK_ID
AND JABOA.BOOK_TYPE_CODE = FAB.BOOK_TYPE_CODE
AND JABOA.BOOK_TYPE_CODE = P_BOOK_NAME
AND JABOA.TYPE = (
SELECT
TYPE
FROM
JAI_FA_AST_BLOCKS
WHERE BLOCK_ID = CP_BLOCK_ID )
AND ( JABOA.START_DATE <= NVL(P_YEAR_START
,JABOA.START_DATE)
OR JABOA.START_DATE BETWEEN NVL(P_YEAR_START
,JABOA.START_DATE)
AND NVL(P_YEAR_END
,JABOA.YEAR_ENDED) );
SELECT
OPENING_WDV
FROM
JAI_FA_AST_BLOCKS JABOA
WHERE JABOA.BOOK_TYPE_CODE = P_BOOK_NAME
AND JABOA.TYPE = (
SELECT
TYPE
FROM
JAI_FA_AST_BLOCKS
WHERE BLOCK_ID = CP_BLOCK_ID )
ORDER BY
START_DATE ASC;
UPDATE
JAI_FA_AST_BLOCKS
SET
CLOSING_WDV = NVL(V_CLOSING_BALANCE
,0)
,YEAR_ENDED = P_YEAR_END
WHERE BLOCK_ID = TEMP_REC.BLOCK_ID;
,'5.7 After update of JAI_FA_AST_BLOCKS, cnt -> ' || SQL%ROWCOUNT)*/NULL;
INSERT INTO JAI_FA_DEP_BLOCKS
(BLOCK_DEPN_ID
,BLOCK_ID
,SLNO
,DEPN_OF_ASSETS
,YEAR_ENDED
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,LAST_UPDATED_BY
,UNPLANNED_DEPN)
VALUES (JAI_FA_DEP_BLOCKS_S.NEXTVAL
,TEMP_REC.BLOCK_ID
,0
,NVL(V_AMOUNT1
,0)
,P_YEAR_END
,SYSDATE
,UID
,SYSDATE
,UID
,UID
,V_DEPN_ADJ);
SELECT
JAI_FA_AST_BLOCKS_S.NEXTVAL
INTO V_BLOCK_ID
FROM
DUAL;
INSERT INTO JAI_FA_AST_BLOCKS
(BLOCK_ID
,TYPE
,RATE
,OPENING_WDV
,START_DATE
,BOOK_TYPE_CODE
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,LAST_UPDATED_BY)
VALUES (V_BLOCK_ID
,V_TYPE
,V_BLOCK_RATE
,NVL(V_CLOSING_BALANCE
,0)
,P_YEAR_END + 1
,P_BOOK_NAME
,SYSDATE
,UID
,SYSDATE
,UID
,UID);
UPDATE
JAI_FA_AST_BLOCKS
SET
CLOSING_WDV = NVL(V_CLOSING_BALANCE
,0)
,YEAR_ENDED = P_YEAR_END
WHERE BLOCK_ID = TEMP_REC.BLOCK_ID;
,'5.9 After update of JAI_FA_AST_BLOCKS, cnt -> ' || SQL%ROWCOUNT)*/NULL;
UPDATE
JAI_FA_DEP_BLOCKS
SET
DEPN_OF_ASSETS = NVL(V_AMOUNT1
,0)
WHERE BLOCK_ID = TEMP_REC.BLOCK_ID
AND SLNO = 0;
,'5.10 After update of JAI_FA_DEP_BLOCKS, cnt -> ' || SQL%ROWCOUNT)*/NULL;
UPDATE
JAI_FA_AST_BLOCKS
SET
OPENING_WDV = NVL(V_CLOSING_BALANCE
,0)
WHERE START_DATE = P_YEAR_END + 1
AND TYPE = V_TYPE
AND BOOK_TYPE_CODE = P_BOOK_NAME;
,'5.11 After update of JAI_FA_AST_BLOCKS, cnt -> ' || SQL%ROWCOUNT)*/NULL;
UPDATE
JAI_FA_DEP_BLOCKS
SET
UNPLANNED_DEPN = V_DEPN_ADJ
WHERE BLOCK_ID = TEMP_REC.BLOCK_ID
AND SLNO = 0;
,'5.12 After update of JAI_FA_DEP_BLOCKS, cnt -> ' || SQL%ROWCOUNT)*/NULL;
UPDATE
JAI_FA_AST_BLOCKS
SET
CLOSING_WDV = 0
,YEAR_ENDED = P_YEAR_END
,CAPITAL_GAINS = ABS(V_CLOSING)
WHERE BLOCK_ID = TEMP_REC.BLOCK_ID;
,'6.1 After update of JAI_FA_AST_BLOCKS, cnt -> ' || SQL%ROWCOUNT)*/NULL;
,'6.2 insert into JAI_FA_AST_BLOCKS, temp_rec.block_id -> ' || TEMP_REC.BLOCK_ID)*/NULL;
INSERT INTO JAI_FA_DEP_BLOCKS
(BLOCK_DEPN_ID
,BLOCK_ID
,SLNO
,DEPN_OF_ASSETS
,YEAR_ENDED
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,LAST_UPDATED_BY
,UNPLANNED_DEPN)
VALUES (JAI_FA_DEP_BLOCKS_S.NEXTVAL
,TEMP_REC.BLOCK_ID
,0
,0
,P_YEAR_END
,SYSDATE
,UID
,SYSDATE
,UID
,UID
,V_DEPN_ADJ);
UPDATE
JAI_FA_DEP_BLOCKS
SET
DEPN_OF_ASSETS = 0
WHERE BLOCK_ID = TEMP_REC.BLOCK_ID;
,'6.3 After update of JAI_FA_DEP_BLOCKS, cnt -> ' || SQL%ROWCOUNT)*/NULL;
UPDATE
JAI_FA_AST_BLOCKS
SET
OPENING_WDV = 0
WHERE START_DATE = P_YEAR_END + 1
AND BOOK_TYPE_CODE = P_BOOK_NAME
AND TYPE = V_TYPE;
,'6.4 After update of JAI_FA_AST_BLOCKS, cnt -> ' || SQL%ROWCOUNT)*/NULL;
UPDATE
JAI_FA_AST_BLOCKS
SET
CLOSING_WDV = 0
,YEAR_ENDED = P_YEAR_END
,CAPITAL_GAINS = NULL
,CAPITAL_LOSS = NULL
WHERE BLOCK_ID = TEMP_REC.BLOCK_ID;
,'6.5 After update of JAI_FA_AST_BLOCKS, cnt -> ' || SQL%ROWCOUNT)*/NULL;
INSERT INTO JAI_FA_DEP_BLOCKS
(BLOCK_DEPN_ID
,BLOCK_ID
,SLNO
,DEPN_OF_ASSETS
,YEAR_ENDED
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,LAST_UPDATED_BY
,UNPLANNED_DEPN)
VALUES (JAI_FA_DEP_BLOCKS_S.NEXTVAL
,TEMP_REC.BLOCK_ID
,0
,0
,P_YEAR_END
,SYSDATE
,UID
,SYSDATE
,UID
,UID
,V_DEPN_ADJ);
,'6.6 After insert into JAI_FA_DEP_BLOCKS')*/NULL;
UPDATE
JAI_FA_DEP_BLOCKS
SET
DEPN_OF_ASSETS = 0
WHERE BLOCK_ID = TEMP_REC.BLOCK_ID;
,'6.7 After update of JAI_FA_DEP_BLOCKS, cnt -> ' || SQL%ROWCOUNT)*/NULL;
UPDATE
JAI_FA_AST_BLOCKS
SET
OPENING_WDV = 0
WHERE START_DATE = P_YEAR_END + 1
AND BOOK_TYPE_CODE = P_BOOK_NAME
AND TYPE = V_TYPE;
,'6.8 After update of JAI_FA_AST_BLOCKS, cnt -> ' || SQL%ROWCOUNT)*/NULL;
UPDATE
JAI_FA_AST_BLOCKS
SET
CLOSING_WDV = 0
,YEAR_ENDED = P_YEAR_END
,CAPITAL_GAINS = NULL
,CAPITAL_LOSS = V_CLOSING
WHERE BLOCK_ID = TEMP_REC.BLOCK_ID;
,'7.1 After update of JAI_FA_AST_BLOCKS, cnt -> ' || SQL%ROWCOUNT)*/NULL;
UPDATE
JAI_FA_DEP_BLOCKS
SET
DEPN_OF_ASSETS = 0
WHERE BLOCK_ID = TEMP_REC.BLOCK_ID;
,'7.2 After update of JAI_FA_DEP_BLOCKS, cnt -> ' || SQL%ROWCOUNT)*/NULL;
UPDATE
JAI_FA_AST_BLOCKS
SET
OPENING_WDV = 0
WHERE START_DATE = P_YEAR_END + 1
AND BOOK_TYPE_CODE = P_BOOK_NAME
AND TYPE = V_TYPE;
,'7.3 After update of JAI_FA_AST_BLOCKS, cnt -> ' || SQL%ROWCOUNT)*/NULL;
UPDATE
JAI_FA_AST_BLOCKS
SET
OPENING_WDV_ADJ = NULL
,DEPN_ADJ = NULL
WHERE START_DATE BETWEEN P_YEAR_START
AND P_YEAR_END
AND OPENING_WDV_ADJ IS NOT NULL
OR DEPN_ADJ IS NOT NULL
AND BOOK_TYPE_CODE = P_BOOK_NAME;
,'8.1 After update of JAI_FA_AST_BLOCKS, cnt -> ' || SQL%ROWCOUNT)*/NULL;
DELETE FROM JAI_FA_EXEMPTIONS;
,'8.2 before commit and after delete of JAI_FA_EXEMPTIONS, cnt -> ' || SQL%ROWCOUNT)*/NULL;
SELECT
DISTINCT
A.MASS_ADDITION_ID,
B.BLOCK_ID,
B.OPENING_WDV,
B.RATE BLOCK_RATE,
C.SLNO,
C.START_DATE,
C.END_DATE,
C.RATE PERIOD_RATE,
C.EXEMPT_UPTO,
NVL(TO_DATE(A.ATTRIBUTE1
,'DD-MON-RR')
,B.START_DATE) AQUISATION_DATE,
D.DATE_PLACED_IN_SERVICE,
A.FIXED_ASSETS_UNITS
FROM
FA_MASS_ADDITIONS A,
JAI_FA_AST_BLOCKS B,
JAI_FA_AST_PERIOD_RATES C,
FA_BOOKS D
WHERE NVL(A.MASS_ADDITION_ID
,0) = NVL(D.ASSET_ID
,0)
AND TO_DATE(A.ATTRIBUTE1
,'DD-MON-RR') between C.START_DATE
AND C.END_DATE
AND B.START_DATE >= P_YEAR_START
AND B.START_DATE <= P_YEAR_END
AND C.YEAR_START = P_YEAR_START
AND C.YEAR_END = P_YEAR_END
AND A.ATTRIBUTE2 = TO_CHAR(B.BLOCK_ID)
AND B.BOOK_TYPE_CODE = P_BOOK_NAME
AND D.DATE_INEFFECTIVE is null
AND D.TRANSACTION_HEADER_ID_OUT is null
AND ( ( A.ASSET_TYPE = LV_CAPITALIZED
AND D.CAPITALIZE_FLAG = LV_FLAG )
OR A.ASSET_TYPE = LV_EXPENSED )
ORDER BY
1;
SELECT
count(*)
FROM
FA_MASS_ADDITIONS A,
JAI_FA_AST_BLOCKS B,
JAI_FA_AST_PERIOD_RATES C,
FA_BOOKS D
WHERE NVL(A.MASS_ADDITION_ID
,0) = NVL(D.ASSET_ID
,0)
AND TO_DATE(A.ATTRIBUTE1
,'DD-MON-RR') between C.START_DATE
AND C.END_DATE
AND B.START_DATE >= P_YEAR_START
AND B.START_DATE <= P_YEAR_END
AND C.YEAR_START = P_YEAR_START
AND C.YEAR_END = P_YEAR_END
AND A.ATTRIBUTE2 = TO_CHAR(B.BLOCK_ID)
AND B.BLOCK_ID = P_BLOCK_ID
AND B.BOOK_TYPE_CODE = P_BOOK_NAME
AND D.BOOK_TYPE_CODE = B.BOOK_TYPE_CODE
AND NVL(B.CLOSING_WDV
,0) <> 0
AND B.YEAR_ENDED is NOT null
AND D.DATE_INEFFECTIVE is null
AND D.TRANSACTION_HEADER_ID_OUT is null
AND ( ( A.ASSET_TYPE = LV_CAPITALIZED
AND D.CAPITALIZE_FLAG = LV_FLAG )
OR A.ASSET_TYPE = LV_EXPENSED )
ORDER BY
1;
SELECT
A.BLOCK_ID,
NVL(SUM(C.PROCEEDS_OF_SALE - C.COST_OF_REMOVAL)
,0) SALE
FROM
JAI_FA_AST_BLOCKS A,
FA_MASS_ADDITIONS B,
FA_RETIREMENTS C
WHERE A.BLOCK_ID = B.ATTRIBUTE2
AND A.BOOK_TYPE_CODE = P_BOOK_NAME
AND B.MASS_ADDITION_ID = C.ASSET_ID
AND A.BLOCK_ID = P_BLOCK_ID
AND C.DATE_RETIRED between P_YEAR_START
AND P_YEAR_END
GROUP BY
A.BLOCK_ID;
SELECT
DISTINCT
SUM(NVL(A.PROCEEDS_OF_SALE
,0)) SALE
FROM
FA_RETIREMENTS A,
FA_MASS_ADDITIONS B,
JAI_FA_AST_BLOCKS C
WHERE A.ASSET_ID = B.MASS_ADDITION_ID
AND B.ATTRIBUTE2 = P_BLOCK_ID
AND B.ATTRIBUTE2 = C.BLOCK_ID
AND A.BOOK_TYPE_CODE = P_BOOK_NAME
AND A.DATE_RETIRED between P_YEAR_START
AND P_YEAR_END
ORDER BY
BLOCK_ID;
SELECT
SUM(A.ORIGINAL_COST) COSTING,
A.ASSET_ID,
A.DATE_PLACED_IN_SERVICE
FROM
FA_BOOKS A,
FA_MASS_ADDITIONS B
WHERE A.ASSET_ID = P_ASSET_ID
AND A.ASSET_ID = B.MASS_ADDITION_ID
AND A.DATE_INEFFECTIVE is null
AND A.TRANSACTION_HEADER_ID_OUT is null
AND ( ( B.ASSET_TYPE = LV_CAPITALIZED
AND A.CAPITALIZE_FLAG = LV_FLAG )
OR B.ASSET_TYPE = LV_EXPENSED )
AND B.ATTRIBUTE2 = P_BLOCK_ID
AND A.BOOK_TYPE_CODE = P_BOOK_NAME
AND TO_DATE(B.ATTRIBUTE1
,'DD-MON-RR') between P_START_DATE
AND P_END_DATE
AND ( A.ORIGINAL_COST / B.FIXED_ASSETS_UNITS ) <= NVL(P_EXEMPT_UPTO
,0)
GROUP BY
A.ASSET_ID,
A.DATE_PLACED_IN_SERVICE;
SELECT
SUM(A.ORIGINAL_COST) COSTING,
A.ASSET_ID,
A.DATE_PLACED_IN_SERVICE
FROM
FA_BOOKS A,
FA_MASS_ADDITIONS B
WHERE A.ASSET_ID = P_ASSET_ID
AND A.ASSET_ID = B.MASS_ADDITION_ID
AND A.DATE_INEFFECTIVE is null
AND A.TRANSACTION_HEADER_ID_OUT is null
AND ( ( B.ASSET_TYPE = LV_CAPITALIZED
AND A.CAPITALIZE_FLAG = LV_FLAG )
OR B.ASSET_TYPE = LV_EXPENSED )
AND B.ATTRIBUTE2 = P_BLOCK_ID
AND A.BOOK_TYPE_CODE = P_BOOK_NAME
AND TO_DATE(B.ATTRIBUTE1
,'DD-MON-RR') between P_START_DATE
AND P_END_DATE
AND ( A.ORIGINAL_COST / B.FIXED_ASSETS_UNITS ) > NVL(P_EXEMPT_UPTO
,0)
GROUP BY
A.ASSET_ID,
A.DATE_PLACED_IN_SERVICE;
SELECT
BLOCK_ID BLOCKID,
SUM(DEPN_OF_ASSETS) ASSETS,
SUM(TOTAL_BALANCE) BALANCE,
SUM(SLNO) COST,
SUM(FULL_EXEMPT) COST_FULL,
BLOCK_ID
FROM
JAI_FA_DEP_BLOCKS_T
GROUP BY
BLOCK_ID;
SELECT
TYPE,
RATE,
OPENING_WDV,
OPENING_WDV_ADJ,
DEPN_ADJ
FROM
JAI_FA_AST_BLOCKS
WHERE BLOCK_ID = P_BLOCK_ID
AND BOOK_TYPE_CODE = P_BOOK_NAME
AND START_DATE >= P_YEAR_START
AND START_DATE <= P_YEAR_END;
SELECT
SUM(EXEMPT_AMOUNT) L_AMOUNT,
BLOCK_ID
FROM
JAI_FA_EXEMPTIONS
WHERE BLOCK_ID = P_BLOCK_ID
GROUP BY
BLOCK_ID;
SELECT
A.UNITS,
TO_NUMBER(B.ATTRIBUTE2)
FROM
FA_RETIREMENTS A,
FA_MASS_ADDITIONS B
WHERE A.DATE_RETIRED <= P_YEAR_END
AND A.ASSET_ID = B.MASS_ADDITION_ID
AND A.STATUS = 'PROCESSED'
AND A.ASSET_ID = P_ASSET_ID
AND B.ATTRIBUTE2 = P_BLOCK_ID;
SELECT
DISTINCT
H.UNITS,
H.ASSET_ID
FROM
FA_ASSET_HISTORY H,
FA_MASS_ADDITIONS A
WHERE TRANSACTION_HEADER_ID_IN IN (
SELECT
MIN(TRANSACTION_HEADER_ID_IN)
FROM
FA_ASSET_HISTORY
GROUP BY
ASSET_ID )
AND A.MASS_ADDITION_ID = H.ASSET_ID
AND A.ATTRIBUTE2 = P_BLOCK_ID
AND A.MASS_ADDITION_ID = P_ASSET_ID;
SELECT
BLOCK_ID
FROM
JAI_FA_AST_BLOCKS
WHERE BLOCK_ID NOT IN (
SELECT
TO_NUMBER(NVL(ATTRIBUTE2
,0))
FROM
FA_MASS_ADDITIONS )
AND START_DATE >= P_YEAR_START
AND START_DATE <= P_YEAR_END;
SELECT
DISTINCT
B.OPENING_WDV,
B.OPENING_WDV_ADJ,
B.DEPN_ADJ,
B.RATE BLOCK_RATE,
B.BLOCK_ID,
B.TYPE
FROM
JAI_FA_AST_BLOCKS B,
JAI_FA_AST_PERIOD_RATES C
WHERE B.START_DATE >= P_YEAR_START
AND B.START_DATE <= P_YEAR_END
AND C.YEAR_START >= P_YEAR_START
AND C.YEAR_END <= P_YEAR_END
AND B.BOOK_TYPE_CODE = P_BOOK_NAME
AND B.BLOCK_ID = P_BLOCK_ID;
SELECT
count(*)
FROM
JAI_FA_AST_BLOCKS
WHERE CLOSING_WDV is not null
AND YEAR_ENDED is not null
AND BLOCK_ID = P_BLOCK_ID
AND BOOK_TYPE_CODE = P_BOOK_NAME
AND START_DATE >= P_YEAR_START
AND START_DATE <= P_YEAR_END;
SELECT
COUNT(*)
FROM
FA_RETIREMENTS
WHERE DATE_RETIRED BETWEEN P_YEAR_START
AND P_YEAR_END;
SELECT
count(*)
FROM
FA_RETIREMENTS A,
FA_MASS_ADDITIONS B,
JAI_FA_AST_BLOCKS C
WHERE A.DATE_RETIRED BETWEEN P_YEAR_START
AND P_YEAR_END
AND A.ASSET_ID = B.MASS_ADDITION_ID
AND B.ATTRIBUTE2 = C.BLOCK_ID
AND C.START_DATE between P_YEAR_START
AND P_YEAR_END;
SELECT
MASS_ADDITION_ID
FROM
FA_MASS_ADDITIONS
WHERE ATTRIBUTE2 = P_BLOCK_ID;
SELECT
RATE,
TYPE
FROM
JAI_FA_AST_BLOCKS
WHERE START_DATE BETWEEN P_YEAR_START
AND P_YEAR_END
AND NVL(OPENING_WDV_ADJ
,DEPN_ADJ) is not null
AND BOOK_TYPE_CODE = P_BOOK_NAME;
SELECT
RATE,
TYPE
FROM
JAI_FA_AST_BLOCKS
WHERE START_DATE BETWEEN P_YEAR_START
AND P_YEAR_END
AND BOOK_TYPE_CODE = P_BOOK_NAME
AND CLOSING_WDV > 0
AND BLOCK_ID in (
SELECT
TO_NUMBER(ATTRIBUTE2)
FROM
FA_MASS_ADDITIONS );
SELECT
BLOCK_ID,
OPENING_WDV,
CLOSING_WDV,
RATE,
START_DATE
FROM
JAI_FA_AST_BLOCKS
WHERE RATE = P_RATE
AND TYPE = P_TYPE
AND START_DATE > P_YEAR_END
AND BOOK_TYPE_CODE = P_BOOK_NAME;
SELECT
YEAR_END
FROM
JAI_FA_AST_YEARS
WHERE YEAR_START = P_START_DATE;
SELECT
count(*)
FROM
JAI_FA_DEP_BLOCKS
WHERE SLNO > 0
AND BLOCK_ID = P_BLOCK_ID
AND SLNO = P_SLNO
AND UNPLANNED_DEPN = P_ASSET_ID;
SELECT
UNPLANNED_DEPN
FROM
JAI_FA_DEP_BLOCKS
WHERE BLOCK_ID = P_BLOCK_ID
AND SLNO = - 1;
SELECT
COUNT(*)
FROM
JAI_FA_DEP_BLOCKS
WHERE BLOCK_ID = P_BLOCK_ID
AND SLNO = 0;
UPDATE
JAI_FA_AST_BLOCKS
SET
CLOSING_WDV = NVL(V_CLOSING_BALANCE
,0) - NVL(CLOSING.DEPN_ADJ
,0)
,YEAR_ENDED = P_YEAR_END
WHERE BLOCK_ID = CLOSING.BLOCK_ID;
SELECT
JAI_FA_AST_BLOCKS_S.NEXTVAL
INTO V_BLOCK_ID1
FROM
DUAL;
INSERT INTO JAI_FA_AST_BLOCKS
(BLOCK_ID
,TYPE
,RATE
,BOOK_TYPE_CODE
,OPENING_WDV
,START_DATE
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,LAST_UPDATED_BY)
VALUES (V_BLOCK_ID1
,CLOSING.TYPE
,CLOSING.BLOCK_RATE
,P_BOOK_NAME
,NVL(V_CLOSING_BALANCE
,0) - NVL(CLOSING.DEPN_ADJ
,0)
,P_YEAR_END + 1
,SYSDATE
,UID
,SYSDATE
,UID
,UID);
INSERT INTO JAI_FA_DEP_BLOCKS
(BLOCK_DEPN_ID
,BLOCK_ID
,SLNO
,DEPN_OF_ASSETS
,YEAR_ENDED
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,LAST_UPDATED_BY
,UNPLANNED_DEPN)
VALUES (JAI_FA_DEP_BLOCKS_S.NEXTVAL
,CLOSING.BLOCK_ID
,0
,V_DEPRECIATION
,P_YEAR_END
,SYSDATE
,UID
,SYSDATE
,UID
,UID
,CLOSING.DEPN_ADJ);
UPDATE
JAI_FA_DEP_BLOCKS
SET
DEPN_OF_ASSETS = V_DEPRECIATION
WHERE BLOCK_ID = CLOSING.BLOCK_ID
AND SLNO = 0;
UPDATE
JAI_FA_AST_BLOCKS
SET
OPENING_WDV = NVL(V_CLOSING_BALANCE
,0) - NVL(V_UNPLANNED_DEPN
,0)
WHERE START_DATE = P_YEAR_END + 1
AND TYPE = CLOSING.TYPE
AND RATE = CLOSING.BLOCK_RATE;
UPDATE
JAI_FA_AST_BLOCKS
SET
CLOSING_WDV = NVL(V_CLOSING_BALANCE
,0) - NVL(V_UNPLANNED_DEPN
,0)
,YEAR_ENDED = P_YEAR_END
WHERE BLOCK_ID = CLOSING.BLOCK_ID;
UPDATE
JAI_FA_DEP_BLOCKS
SET
UNPLANNED_DEPN = CLOSING.DEPN_ADJ
WHERE BLOCK_ID = CLOSING.BLOCK_ID
AND SLNO = 0;
INSERT INTO JAI_FA_DEP_BLOCKS
(BLOCK_DEPN_ID
,BLOCK_ID
,SLNO
,DEPN_OF_ASSETS
,YEAR_ENDED
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,LAST_UPDATED_BY
,UNPLANNED_DEPN)
VALUES (JAI_FA_DEP_BLOCKS_S.NEXTVAL
,V_BLOCK_ID
,V_SERIAL_NUM1
,NVL(V_DEPN_AMOUNT
,0)
,P_YEAR_END
,SYSDATE
,UID
,SYSDATE
,UID
,UID
,V_ASSET_ID);
INSERT INTO JAI_FA_DEP_BLOCKS_T
(BLOCK_ID
,SLNO
,DEPN_OF_ASSETS
,YEAR_ENDED
,FULL_EXEMPT
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,LAST_UPDATED_BY
,TOTAL_BALANCE
,UNPLANNED_DEPN)
VALUES (V_BLOCK_ID
,ROUND(NVL(V_COST
,0)
,2)
,ROUND(NVL(V_MORE_AMOUNT
,0)
,2)
,P_YEAR_END
,NVL(V_MORE_AMOUNT1
,0)
,SYSDATE
,UID
,SYSDATE
,UID
,UID
,ROUND(NVL(V_CLOSING_BALANCE
,0)
,2)
,V_SLNO);
INSERT INTO JAI_FA_DEP_BLOCKS
(BLOCK_DEPN_ID
,BLOCK_ID
,SLNO
,DEPN_OF_ASSETS
,YEAR_ENDED
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,LAST_UPDATED_BY
,UNPLANNED_DEPN)
VALUES (JAI_FA_DEP_BLOCKS_S.NEXTVAL
,V_BLOCK_ID
,V_SERIAL_NUM1
,NVL(V_DEPN_AMOUNT
,0)
,P_YEAR_END
,SYSDATE
,UID
,SYSDATE
,UID
,UID
,V_ASSET_ID);
INSERT INTO JAI_FA_EXEMPTIONS
(BLOCK_ID
,EXEMPT_AMOUNT
,FA_EXEMPTION_ID
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,OBJECT_VERSION_NUMBER)
VALUES (V_BLOCK_ID
,ROUND(NVL(V_COST_LESS
,0)
,2)
,JAI_FA_EXEMPTIONS_S.NEXTVAL
,FND_GLOBAL.USER_ID
,SYSDATE
,FND_GLOBAL.USER_ID
,SYSDATE
,FND_GLOBAL.LOGIN_ID
,NULL);
INSERT INTO JAI_FA_DEP_BLOCKS
(BLOCK_DEPN_ID
,BLOCK_ID
,SLNO
,DEPN_OF_ASSETS
,FULL_EXEMPT
,YEAR_ENDED
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,LAST_UPDATED_BY
,UNPLANNED_DEPN)
VALUES (JAI_FA_DEP_BLOCKS_S.NEXTVAL
,V_BLOCK_ID
,V_SLNO
,NVL(V_COST_LESS
,0)
,NVL(V_COST_LESS
,0)
,P_YEAR_END
,SYSDATE
,UID
,SYSDATE
,UID
,UID
,V_ASSET_ID);
UPDATE
JAI_FA_AST_BLOCKS
SET
CLOSING_WDV = NVL(V_CLOSING_BALANCE
,0)
,YEAR_ENDED = P_YEAR_END
WHERE BLOCK_ID = TEMP_REC.BLOCK_ID;
INSERT INTO JAI_FA_DEP_BLOCKS
(BLOCK_DEPN_ID
,BLOCK_ID
,SLNO
,DEPN_OF_ASSETS
,YEAR_ENDED
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,LAST_UPDATED_BY
,UNPLANNED_DEPN)
VALUES (JAI_FA_DEP_BLOCKS_S.NEXTVAL
,TEMP_REC.BLOCK_ID
,0
,NVL(V_AMOUNT1
,0)
,P_YEAR_END
,SYSDATE
,UID
,SYSDATE
,UID
,UID
,V_DEPN_ADJ);
SELECT
JAI_FA_AST_BLOCKS_S.NEXTVAL
INTO V_BLOCK_ID
FROM
DUAL;
INSERT INTO JAI_FA_AST_BLOCKS
(BLOCK_ID
,TYPE
,RATE
,OPENING_WDV
,START_DATE
,BOOK_TYPE_CODE
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,LAST_UPDATED_BY)
VALUES (V_BLOCK_ID
,V_TYPE
,V_BLOCK_RATE
,NVL(V_CLOSING_BALANCE
,0)
,P_YEAR_END + 1
,P_BOOK_NAME
,SYSDATE
,UID
,SYSDATE
,UID
,UID);
UPDATE
JAI_FA_AST_BLOCKS
SET
CLOSING_WDV = NVL(V_CLOSING_BALANCE
,0)
,YEAR_ENDED = P_YEAR_END
WHERE BLOCK_ID = TEMP_REC.BLOCK_ID;
UPDATE
JAI_FA_DEP_BLOCKS
SET
DEPN_OF_ASSETS = NVL(V_AMOUNT1
,0)
WHERE BLOCK_ID = TEMP_REC.BLOCK_ID
AND SLNO = 0;
UPDATE
JAI_FA_AST_BLOCKS
SET
OPENING_WDV = NVL(V_CLOSING_BALANCE
,0)
WHERE START_DATE = P_YEAR_END + 1
AND RATE = V_BLOCK_RATE
AND TYPE = V_TYPE;
UPDATE
JAI_FA_DEP_BLOCKS
SET
UNPLANNED_DEPN = V_DEPN_ADJ
WHERE BLOCK_ID = TEMP_REC.BLOCK_ID
AND SLNO = 0;
UPDATE
JAI_FA_AST_BLOCKS
SET
CLOSING_WDV = 0
,YEAR_ENDED = P_YEAR_END
,CAPITAL_GAINS = ABS(V_CLOSING)
WHERE BLOCK_ID = TEMP_REC.BLOCK_ID;
INSERT INTO JAI_FA_DEP_BLOCKS
(BLOCK_DEPN_ID
,BLOCK_ID
,SLNO
,DEPN_OF_ASSETS
,YEAR_ENDED
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,LAST_UPDATED_BY
,UNPLANNED_DEPN)
VALUES (JAI_FA_DEP_BLOCKS_S.NEXTVAL
,TEMP_REC.BLOCK_ID
,0
,0
,P_YEAR_END
,SYSDATE
,UID
,SYSDATE
,UID
,UID
,V_DEPN_ADJ);
UPDATE
JAI_FA_DEP_BLOCKS
SET
DEPN_OF_ASSETS = 0
WHERE BLOCK_ID = TEMP_REC.BLOCK_ID;
UPDATE
JAI_FA_AST_BLOCKS
SET
OPENING_WDV = 0
WHERE START_DATE = P_YEAR_END + 1
AND RATE = V_BLOCK_RATE
AND TYPE = V_TYPE;
UPDATE
JAI_FA_AST_BLOCKS
SET
CLOSING_WDV = 0
,YEAR_ENDED = P_YEAR_END
,CAPITAL_GAINS = NULL
,CAPITAL_LOSS = NULL
WHERE BLOCK_ID = TEMP_REC.BLOCK_ID;
INSERT INTO JAI_FA_DEP_BLOCKS
(BLOCK_DEPN_ID
,BLOCK_ID
,SLNO
,DEPN_OF_ASSETS
,YEAR_ENDED
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,LAST_UPDATED_BY
,UNPLANNED_DEPN)
VALUES (JAI_FA_DEP_BLOCKS_S.NEXTVAL
,TEMP_REC.BLOCK_ID
,0
,0
,P_YEAR_END
,SYSDATE
,UID
,SYSDATE
,UID
,UID
,V_DEPN_ADJ);
UPDATE
JAI_FA_DEP_BLOCKS
SET
DEPN_OF_ASSETS = 0
WHERE BLOCK_ID = TEMP_REC.BLOCK_ID;
UPDATE
JAI_FA_AST_BLOCKS
SET
OPENING_WDV = 0
WHERE START_DATE = P_YEAR_END + 1
AND RATE = V_BLOCK_RATE
AND TYPE = V_TYPE;
UPDATE
JAI_FA_AST_BLOCKS
SET
CLOSING_WDV = 0
,YEAR_ENDED = P_YEAR_END
,CAPITAL_GAINS = NULL
,CAPITAL_LOSS = V_CLOSING
WHERE BLOCK_ID = TEMP_REC.BLOCK_ID;
UPDATE
JAI_FA_DEP_BLOCKS
SET
DEPN_OF_ASSETS = 0
WHERE BLOCK_ID = TEMP_REC.BLOCK_ID;
UPDATE
JAI_FA_AST_BLOCKS
SET
OPENING_WDV = 0
WHERE START_DATE = P_YEAR_END + 1
AND RATE = V_BLOCK_RATE
AND TYPE = V_TYPE;
UPDATE
JAI_FA_AST_BLOCKS
SET
OPENING_WDV = NVL(V_PREV_CLOSING_BALANCE
,ADJUST.OPENING_WDV)
WHERE START_DATE = ADJUST.START_DATE
AND BLOCK_ID = ADJUST.BLOCK_ID;
UPDATE
JAI_FA_DEP_BLOCKS
SET
DEPN_OF_ASSETS = V_DEPRECIATION
WHERE BLOCK_ID = ADJUST.BLOCK_ID
AND SLNO = 0;
UPDATE
JAI_FA_AST_BLOCKS
SET
CLOSING_WDV = NVL(V_CLOSING_BALANCE
,0)
,YEAR_ENDED = V_YEAR_END
WHERE BLOCK_ID = ADJUST.BLOCK_ID;
UPDATE
JAI_FA_AST_BLOCKS
SET
OPENING_WDV = NVL(V_PREV_CLOSING_BALANCE
,ADJUST.OPENING_WDV)
WHERE START_DATE = ADJUST.START_DATE
AND BLOCK_ID = ADJUST.BLOCK_ID;
UPDATE
JAI_FA_DEP_BLOCKS
SET
DEPN_OF_ASSETS = V_DEPRECIATION
WHERE BLOCK_ID = ADJUST.BLOCK_ID
AND SLNO = 0;
UPDATE
JAI_FA_AST_BLOCKS
SET
CLOSING_WDV = NVL(V_CLOSING_BALANCE
,0)
,YEAR_ENDED = V_YEAR_END
WHERE BLOCK_ID = ADJUST.BLOCK_ID;
UPDATE
JAI_FA_AST_BLOCKS
SET
OPENING_WDV_ADJ = NULL
,DEPN_ADJ = NULL
WHERE START_DATE BETWEEN P_YEAR_START
AND P_YEAR_END
AND OPENING_WDV_ADJ is not null
OR DEPN_ADJ is not null
AND BOOK_TYPE_CODE = P_BOOK_NAME;
DELETE FROM JAI_FA_EXEMPTIONS;
DELETE FROM JAI_FA_DEP_BLOCKS_T;
SELECT
NVL(SUM(SLNO)
,0) COST
FROM
JAI_FA_DEP_BLOCKS_T
WHERE ASSET_ID Not In (
SELECT
ASSET_ID
FROM
FA_RETIREMENTS
WHERE BOOK_TYPE_CODE = BOOK_NAME
AND STATUS = CP_STATUS
AND DATE_RETIRED between START_DATE
AND END_DATE_N )
AND BLOCK_ID = BLOCK_ID
GROUP BY
BLOCK_ID;
SELECT
'1'
FROM
JAI_FA_AST_BLOCK_DTLS JBA,
FA_BOOKS FAB,
JAI_FA_AST_BLOCKS JABOA
WHERE JBA.ASSET_ID = FAB.ASSET_ID
AND ( ( JBA.ASSET_TYPE = LV_CAPITALIZED
AND FAB.CAPITALIZE_FLAG = LV_FLAG )
OR JBA.ASSET_TYPE = LV_EXPENSED )
AND JBA.DATE_OF_ACQUISITION BETWEEN JABOA.START_DATE
AND NVL(JABOA.YEAR_ENDED
,P_YEAR_END)
AND FAB.DATE_INEFFECTIVE IS NULL
AND FAB.TRANSACTION_HEADER_ID_OUT IS NULL
AND FAB.PERIOD_COUNTER_FULLY_RETIRED IS NULL
AND JBA.BLOCK_ID = JABOA.BLOCK_ID
AND JABOA.BOOK_TYPE_CODE = FAB.BOOK_TYPE_CODE
AND JABOA.BOOK_TYPE_CODE = P_BOOK_NAME
AND JABOA.TYPE = (
SELECT
TYPE
FROM
JAI_FA_AST_BLOCKS
WHERE BLOCK_ID = P_BLOCK_ID )
AND ( JABOA.START_DATE <= NVL(P_YEAR_START
,JABOA.START_DATE)
OR JABOA.START_DATE BETWEEN NVL(P_YEAR_START
,JABOA.START_DATE)
AND NVL(P_YEAR_END
,JABOA.YEAR_ENDED) );
SELECT
OPENING_WDV
FROM
JAI_FA_AST_BLOCKS JABOA
WHERE JABOA.BOOK_TYPE_CODE = P_BOOK_NAME
AND JABOA.TYPE = (
SELECT
TYPE
FROM
JAI_FA_AST_BLOCKS
WHERE BLOCK_ID = CP_BLOCK_ID )
ORDER BY
START_DATE ASC;
SELECT
A.ASSET_ID,
A.COST,
A.TRANSACTION_HEADER_ID_IN,
A.DATE_EFFECTIVE
FROM
FA_BOOKS A,
JAI_FA_AST_BLOCKS B,
JAI_FA_AST_BLOCK_DTLS C,
JAI_FA_AST_PERIOD_RATES D
WHERE A.ASSET_ID = C.ASSET_ID
AND ( ( C.ASSET_TYPE = LV_CAPITALIZED
AND A.CAPITALIZE_FLAG = LV_FLAG )
OR C.ASSET_TYPE = LV_EXPENSED )
AND B.BLOCK_ID = P_BLOCK_ID
AND B.BLOCK_ID = C.BLOCK_ID
AND A.BOOK_TYPE_CODE = B.BOOK_TYPE_CODE
AND A.BOOK_TYPE_CODE = P_BOOK_NAME
AND C.DATE_OF_ACQUISITION between D.START_DATE
AND D.END_DATE
AND B.START_DATE between NVL(P_YEAR_START
,B.START_DATE)
AND NVL(P_YEAR_END
,B.YEAR_ENDED)
AND D.START_DATE >= ADD_MONTHS(P_YEAR_START
,6)
AND D.SLNO = 2
AND A.DATE_INEFFECTIVE is null
AND A.TRANSACTION_HEADER_ID_OUT is null;
SELECT
NVL(RATE
,0) RATE,
TYPE
FROM
JAI_FA_AST_BLOCKS
WHERE BLOCK_ID = P_BLOCK_ID;
SELECT
NVL(SUM(C.PROCEEDS_OF_SALE - C.COST_OF_REMOVAL)
,0) SALE
FROM
JAI_FA_AST_BLOCKS A,
FA_ADDITIONS B,
FA_RETIREMENTS C,
JAI_FA_AST_PERIOD_RATES D
WHERE B.CONTEXT = P_CONTEXT_VALUE
AND TO_CHAR(A.BLOCK_ID) = B.ATTRIBUTE2
AND A.BOOK_TYPE_CODE = P_BOOK_NAME
AND B.OWNED_LEASED = 'OWNED'
AND B.ASSET_TYPE IN ( 'CAPITALIZED' , 'EXPENSED' )
AND TO_DATE(B.ATTRIBUTE1
,'DD-MON-RRRR') between D.START_DATE
AND D.END_DATE
AND D.START_DATE < ADD_MONTHS(P_YEAR_START
,6)
AND D.SLNO = 1
AND B.ASSET_ID = C.ASSET_ID
AND C.STATUS = 'PROCESSED'
AND A.TYPE = P_BLOCK_TYPE
AND A.BLOCK_ID = P_BLOCK_ID
AND C.DATE_RETIRED BETWEEN P_YEAR_START
AND P_YEAR_END;
SELECT
NVL(SUM(C.PROCEEDS_OF_SALE - C.COST_OF_REMOVAL)
,0) SALE
FROM
JAI_FA_AST_BLOCKS A,
FA_ADDITIONS B,
FA_RETIREMENTS C,
JAI_FA_AST_PERIOD_RATES D
WHERE B.CONTEXT = P_CONTEXT_VALUE
AND TO_CHAR(A.BLOCK_ID) = B.ATTRIBUTE2
AND A.BOOK_TYPE_CODE = P_BOOK_NAME
AND B.OWNED_LEASED = 'OWNED'
AND B.ASSET_TYPE IN ( 'CAPITALIZED' , 'EXPENSED' )
AND TO_DATE(B.ATTRIBUTE1
,'DD-MON-RRRR') between D.START_DATE
AND D.END_DATE
AND D.START_DATE >= ADD_MONTHS(P_YEAR_START
,6)
AND D.SLNO = 2
AND B.ASSET_ID = C.ASSET_ID
AND C.STATUS = 'PROCESSED'
AND A.TYPE = P_BLOCK_TYPE
AND A.BLOCK_ID = P_BLOCK_ID
AND C.DATE_RETIRED BETWEEN P_YEAR_START
AND P_YEAR_END;
SELECT
NVL(SUM(SLNO)
,0) COST
FROM
JAI_FA_DEP_BLOCKS_T
WHERE BLOCK_ID = P_BLOCK_ID;
SELECT
NVL(OPENING_WDV
,0)
FROM
JAI_FA_AST_BLOCKS
WHERE BLOCK_ID = P_BLOCK_ID;
SELECT
NVL(OPENING_WDV_ADJ
,0)
FROM
JAI_FA_AST_BLOCKS
WHERE BLOCK_ID = P_BLOCK_ID;
SELECT
COUNT(*)
FROM
FA_ADDITIONS
WHERE LENGTH(ATTRIBUTE1) = 1
AND CONTEXT = P_CONTEXT_VALUE;
SELECT
count(*)
FROM
FA_ADDITIONS
WHERE ATTRIBUTE1 IS NOT NULL
AND LENGTH(ATTRIBUTE1) <> 0
AND CONTEXT = P_CONTEXT_VALUE;
UPDATE
FA_ADDITIONS_B
SET
ATTRIBUTE1 = NULL
WHERE LENGTH(ATTRIBUTE1) = 1
AND CONTEXT = P_CONTEXT_VALUE;
UPDATE
FA_ADDITIONS_B
SET
ATTRIBUTE1 = TO_CHAR(TO_DATE(ATTRIBUTE1
,'DD-MON-RRRR')
,'DD-MON-RRRR')
WHERE ATTRIBUTE1 IS NOT NULL
AND LENGTH(ATTRIBUTE1) <> 1
AND CONTEXT = P_CONTEXT_VALUE;
SELECT
CONTEXT
FROM
FA_ADDITIONS_B
WHERE ATTRIBUTE1 is NOT NULL
AND ATTRIBUTE2 IN (
SELECT
DISTINCT
TO_CHAR(BLOCK_ID)
FROM
JAI_FA_AST_BLOCKS );
UPDATE
FA_ADDITIONS_B
SET
CONTEXT = 'India B Of Assets'
WHERE ATTRIBUTE1 IS NOT NULL
AND ATTRIBUTE2 IN (
SELECT
DISTINCT
TO_CHAR(BLOCK_ID)
FROM
JAI_FA_AST_BLOCKS );
SELECT
CONCURRENT_PROGRAM_ID,
NVL(ENABLE_TRACE
,'N')
FROM
FND_CONCURRENT_REQUESTS
WHERE REQUEST_ID = P_REQUEST_ID;
SELECT
TRANSACTION_HEADER_ID_IN,
DATE_EFFECTIVE,
FAB.COST
FROM
FA_BOOKS FAB
WHERE FAB.TRANSACTION_HEADER_ID_OUT = CP_TRANSACTION_HEADER_ID_IN
AND TRUNC(FAB.DATE_INEFFECTIVE) = TRUNC(CP_DATE_EFFECTIVE)
AND FAB.BOOK_TYPE_CODE = CP_BOOK_NAME
AND FAB.ASSET_ID = CP_ASSET_ID
AND FAB.RETIREMENT_ID IS NOT NULL;
SELECT
FAB.TRANSACTION_HEADER_ID_IN,
FAB.DATE_EFFECTIVE,
FAB.COST
FROM
FA_BOOKS FAB,
JAI_FA_AST_BLOCK_DTLS JBA
WHERE FAB.ASSET_ID = JBA.ASSET_ID
AND JBA.BLOCK_ID = P_BLOCK_ID
AND JBA.ASSET_ID = P_ASSET_ID
AND FAB.TRANSACTION_HEADER_ID_OUT IS NULL
AND FAB.DATE_INEFFECTIVE IS NULL
AND FAB.BOOK_TYPE_CODE = P_BOOK_NAME;
SELECT
TRANSACTION_HEADER_ID_IN,
DATE_EFFECTIVE
FROM
FA_BOOKS FAB
WHERE FAB.TRANSACTION_HEADER_ID_OUT = CP_TRANSACTION_HEADER_ID_IN
AND TRUNC(FAB.DATE_INEFFECTIVE) = TRUNC(CP_DATE_EFFECTIVE)
AND FAB.BOOK_TYPE_CODE = CP_BOOK_NAME
AND FAB.ASSET_ID = CP_ASSET_ID
AND FAB.RETIREMENT_ID IS NOT NULL;
SELECT
TRANSACTION_HEADER_ID_IN,
DATE_EFFECTIVE,
FAB.COST
FROM
FA_BOOKS FAB
WHERE FAB.TRANSACTION_HEADER_ID_OUT = CP_TRANSACTION_HEADER_ID_IN
AND TRUNC(FAB.DATE_INEFFECTIVE) = TRUNC(CP_DATE_EFFECTIVE)
AND FAB.BOOK_TYPE_CODE = CP_BOOK_NAME
AND FAB.ASSET_ID = CP_ASSET_ID
AND FAB.RETIREMENT_ID IS NOT NULL;