[Home] [Help]
50: ,0) - NVL(A.COST_OF_REMOVAL
51: ,0) ) SALE
52: FROM
53: FA_RETIREMENTS A,
54: JAI_FA_AST_BLOCK_DTLS B,
55: JAI_FA_AST_BLOCKS C
56: WHERE A.ASSET_ID = B.ASSET_ID
57: AND A.STATUS = 'PROCESSED'
58: AND A.ASSET_ID = ASSET_ID_v
106: SELECT
107: A.BLOCK_ID
108: FROM
109: JAI_FA_AST_BLOCKS A,
110: JAI_FA_AST_BLOCK_DTLS B,
111: FA_RETIREMENTS C
112: WHERE A.BLOCK_ID = B.BLOCK_ID
113: AND B.ASSET_ID = C.ASSET_ID
114: GROUP BY
138: ,0) - NVL(A.COST_OF_REMOVAL
139: ,0) ) SALE
140: FROM
141: FA_RETIREMENTS A,
142: JAI_FA_AST_BLOCK_DTLS B,
143: JAI_FA_AST_BLOCKS C
144: WHERE A.ASSET_ID = B.ASSET_ID
145: AND B.BLOCK_ID = P_BLOCK_ID
146: AND C.TYPE = P_TYPE
196: SELECT
197: NVL(SUM(FAR.PROCEEDS_OF_SALE - FAR.COST_OF_REMOVAL)
198: ,0) SALE
199: FROM
200: JAI_FA_AST_BLOCK_DTLS JBA,
201: FA_ADDITIONS FA,
202: JAI_FA_AST_BLOCKS JABLOA,
203: FA_RETIREMENTS FAR
204: WHERE FA.OWNED_LEASED = CP_OWNED_LEASED
237: ,TYPE1 IN VARCHAR2
238: ,OP_TOT IN NUMBER
239: ,COS_TOT IN NUMBER
240: ,RATE IN NUMBER) RETURN NUMBER IS
241: LV_CAPITALIZED JAI_FA_AST_BLOCK_DTLS.ASSET_TYPE%TYPE;
242: LV_FLAG FA_BOOKS.CAPITALIZE_FLAG%TYPE;
243: LV_EXPENSED JAI_FA_AST_BLOCK_DTLS.ASSET_TYPE%TYPE;
244: START_DATE_T date;
245: CURSOR CUR_GET_VALID_ASSETS IS
239: ,COS_TOT IN NUMBER
240: ,RATE IN NUMBER) RETURN NUMBER IS
241: LV_CAPITALIZED JAI_FA_AST_BLOCK_DTLS.ASSET_TYPE%TYPE;
242: LV_FLAG FA_BOOKS.CAPITALIZE_FLAG%TYPE;
243: LV_EXPENSED JAI_FA_AST_BLOCK_DTLS.ASSET_TYPE%TYPE;
244: START_DATE_T date;
245: CURSOR CUR_GET_VALID_ASSETS IS
246: SELECT
247: '1'
245: CURSOR CUR_GET_VALID_ASSETS IS
246: SELECT
247: '1'
248: FROM
249: JAI_FA_AST_BLOCK_DTLS JBA,
250: FA_BOOKS FAB,
251: JAI_FA_AST_BLOCKS JABOA
252: WHERE JBA.ASSET_ID = FAB.ASSET_ID
253: AND ( ( JBA.ASSET_TYPE = LV_CAPITALIZED
298: A.DATE_EFFECTIVE
299: FROM
300: FA_BOOKS A,
301: JAI_FA_AST_BLOCKS B,
302: JAI_FA_AST_BLOCK_DTLS C,
303: JAI_FA_AST_PERIOD_RATES D
304: WHERE A.ASSET_ID = C.ASSET_ID
305: AND ( ( C.ASSET_TYPE = LV_CAPITALIZED
306: AND A.CAPITALIZE_FLAG = LV_FLAG )
589:
590: PROCEDURE RETIRE(P_YEAR_START IN DATE
591: ,P_YEAR_END IN DATE
592: ,P_BOOK_NAME IN VARCHAR2) IS
593: LV_CAPITALIZED JAI_FA_AST_BLOCK_DTLS.ASSET_TYPE%TYPE;
594: LV_FLAG FA_BOOKS.CAPITALIZE_FLAG%TYPE;
595: LV_EXPENSED JAI_FA_AST_BLOCK_DTLS.ASSET_TYPE%TYPE;
596: LV_OWNED VARCHAR2(10);
597: K NUMBER;
591: ,P_YEAR_END IN DATE
592: ,P_BOOK_NAME IN VARCHAR2) IS
593: LV_CAPITALIZED JAI_FA_AST_BLOCK_DTLS.ASSET_TYPE%TYPE;
594: LV_FLAG FA_BOOKS.CAPITALIZE_FLAG%TYPE;
595: LV_EXPENSED JAI_FA_AST_BLOCK_DTLS.ASSET_TYPE%TYPE;
596: LV_OWNED VARCHAR2(10);
597: K NUMBER;
598: I NUMBER := 0;
599: V_COUNT1 NUMBER;
668: A.DATE_OF_ACQUISITION AQUISATION_DATE,
669: D.DATE_PLACED_IN_SERVICE,
670: E.CURRENT_UNITS
671: FROM
672: JAI_FA_AST_BLOCK_DTLS A,
673: JAI_FA_AST_BLOCKS B,
674: JAI_FA_AST_PERIOD_RATES C,
675: FA_BOOKS D,
676: FA_ADDITIONS E
697: CURSOR COUNT_CUR(P_BLOCK_ID IN NUMBER) IS
698: SELECT
699: count(*)
700: FROM
701: JAI_FA_AST_BLOCK_DTLS A,
702: JAI_FA_AST_BLOCKS B,
703: JAI_FA_AST_PERIOD_RATES C,
704: FA_BOOKS D,
705: FA_ADDITIONS E
734: NVL(SUM(C.PROCEEDS_OF_SALE)
735: ,0) SALE
736: FROM
737: JAI_FA_AST_BLOCKS A,
738: JAI_FA_AST_BLOCK_DTLS B,
739: FA_RETIREMENTS C
740: WHERE A.BLOCK_ID = B.BLOCK_ID
741: AND A.BOOK_TYPE_CODE = P_BOOK_NAME
742: AND B.ASSET_ID = C.ASSET_ID
773: SUM(NVL(A.PROCEEDS_OF_SALE
774: ,0)) SALE
775: FROM
776: FA_RETIREMENTS A,
777: JAI_FA_AST_BLOCK_DTLS B,
778: JAI_FA_AST_BLOCKS C
779: WHERE A.ASSET_ID = B.ASSET_ID
780: AND B.BLOCK_ID = P_BLOCK_ID
781: AND B.BLOCK_ID = C.BLOCK_ID
790: A.ASSET_ID,
791: A.DATE_PLACED_IN_SERVICE
792: FROM
793: FA_BOOKS A,
794: JAI_FA_AST_BLOCK_DTLS B,
795: FA_ADDITIONS C
796: WHERE A.ASSET_ID = P_ASSET_ID
797: AND A.ASSET_ID = B.ASSET_ID
798: AND A.DATE_INEFFECTIVE is null
816: A.ASSET_ID,
817: A.DATE_PLACED_IN_SERVICE
818: FROM
819: FA_BOOKS A,
820: JAI_FA_AST_BLOCK_DTLS B,
821: FA_ADDITIONS C
822: WHERE A.ASSET_ID = P_ASSET_ID
823: AND A.ASSET_ID = B.ASSET_ID
824: AND A.ASSET_ID = B.ASSET_ID
872: A.UNITS,
873: B.BLOCK_ID
874: FROM
875: FA_RETIREMENTS A,
876: JAI_FA_AST_BLOCK_DTLS B
877: WHERE A.DATE_RETIRED <= P_YEAR_END
878: AND A.ASSET_ID = B.ASSET_ID
879: AND A.STATUS = 'PROCESSED'
880: AND A.ASSET_ID = P_ASSET_ID
885: H.UNITS,
886: H.ASSET_ID
887: FROM
888: FA_ASSET_HISTORY H,
889: JAI_FA_AST_BLOCK_DTLS A
890: WHERE TRANSACTION_HEADER_ID_IN IN (
891: SELECT
892: MIN(TRANSACTION_HEADER_ID_IN)
893: FROM
905: WHERE TO_CHAR(BLOCK_ID) NOT IN (
906: SELECT
907: BLOCK_ID
908: FROM
909: JAI_FA_AST_BLOCK_DTLS )
910: AND START_DATE >= P_YEAR_START
911: AND START_DATE <= P_YEAR_END;
912: CURSOR CLOSING_BALANCE_CUR(P_BLOCK_ID IN NUMBER) IS
913: SELECT
949: SELECT
950: count(*)
951: FROM
952: FA_RETIREMENTS A,
953: JAI_FA_AST_BLOCK_DTLS B,
954: JAI_FA_AST_BLOCKS C
955: WHERE A.DATE_RETIRED BETWEEN P_YEAR_START
956: AND P_YEAR_END
957: AND A.ASSET_ID = B.ASSET_ID
961: CURSOR ASSET_ID_CURSOR(P_BLOCK_ID IN NUMBER) IS
962: SELECT
963: ASSET_ID
964: FROM
965: JAI_FA_AST_BLOCK_DTLS
966: WHERE BLOCK_ID = P_BLOCK_ID;
967: CURSOR OPENING_WDV_ADJ_CUR IS
968: SELECT
969: RATE,
978: AND TO_CHAR(BLOCK_ID) not in (
979: SELECT
980: BLOCK_ID
981: FROM
982: JAI_FA_AST_BLOCK_DTLS );
983: CURSOR ASSET_ADD_PR_CUR IS
984: SELECT
985: RATE,
986: TYPE
993: AND TO_CHAR(BLOCK_ID) in (
994: SELECT
995: BLOCK_ID
996: FROM
997: JAI_FA_AST_BLOCK_DTLS );
998: CURSOR ADJUST_OPEN_CUR(P_RATE IN NUMBER,P_TYPE IN VARCHAR2) IS
999: SELECT
1000: BLOCK_ID,
1001: OPENING_WDV,
1167:
1168: PROCEDURE RUN_DEP(P_YEAR_START IN DATE
1169: ,P_YEAR_END IN DATE
1170: ,P_BOOK_NAME IN VARCHAR2) IS
1171: LV_CAPITALIZED JAI_FA_AST_BLOCK_DTLS.ASSET_TYPE%TYPE;
1172: LV_FLAG FA_BOOKS.CAPITALIZE_FLAG%TYPE;
1173: LV_EXPENSED JAI_FA_AST_BLOCK_DTLS.ASSET_TYPE%TYPE;
1174: LV_OWNED_LEASED FA_ADDITIONS.OWNED_LEASED%TYPE;
1175: LV_STATUS FA_RETIREMENTS.STATUS%TYPE;
1169: ,P_YEAR_END IN DATE
1170: ,P_BOOK_NAME IN VARCHAR2) IS
1171: LV_CAPITALIZED JAI_FA_AST_BLOCK_DTLS.ASSET_TYPE%TYPE;
1172: LV_FLAG FA_BOOKS.CAPITALIZE_FLAG%TYPE;
1173: LV_EXPENSED JAI_FA_AST_BLOCK_DTLS.ASSET_TYPE%TYPE;
1174: LV_OWNED_LEASED FA_ADDITIONS.OWNED_LEASED%TYPE;
1175: LV_STATUS FA_RETIREMENTS.STATUS%TYPE;
1176: V_C NUMBER := 0;
1177: K NUMBER := 0;
1259: FA_ADDITIONS A,
1260: JAI_FA_AST_BLOCKS B,
1261: JAI_FA_AST_PERIOD_RATES C,
1262: FA_BOOKS D,
1263: JAI_FA_AST_BLOCK_DTLS E
1264: WHERE A.OWNED_LEASED = LV_OWNED_LEASED
1265: AND ( ( A.ASSET_TYPE = LV_CAPITALIZED
1266: AND D.CAPITALIZE_FLAG = LV_FLAG )
1267: OR A.ASSET_TYPE = LV_EXPENSED )
1286: WHERE BLOCK_ID NOT IN (
1287: SELECT
1288: BLOCK_ID
1289: FROM
1290: JAI_FA_AST_BLOCK_DTLS
1291: WHERE DATE_OF_ACQUISITION BETWEEN P_YEAR_START
1292: AND P_YEAR_END )
1293: AND START_DATE >= P_YEAR_START
1294: AND START_DATE <= P_YEAR_END
1316: NVL(SUM(A.PROCEEDS_OF_SALE - A.COST_OF_REMOVAL)
1317: ,0) SALE
1318: FROM
1319: FA_RETIREMENTS A,
1320: JAI_FA_AST_BLOCK_DTLS B,
1321: JAI_FA_AST_BLOCKS C,
1322: FA_BOOKS D
1323: WHERE A.ASSET_ID = B.ASSET_ID
1324: AND ( ( B.ASSET_TYPE = LV_CAPITALIZED
1338: NVL(SUM(C.PROCEEDS_OF_SALE - C.COST_OF_REMOVAL)
1339: ,0) SALE
1340: FROM
1341: JAI_FA_AST_BLOCKS A,
1342: JAI_FA_AST_BLOCK_DTLS B,
1343: FA_RETIREMENTS C
1344: WHERE A.BLOCK_ID = B.BLOCK_ID
1345: AND A.BOOK_TYPE_CODE = P_BOOK_NAME
1346: AND B.ASSET_ID = C.ASSET_ID
1354: SUM(NVL(A.PROCEEDS_OF_SALE
1355: ,0)) SALE
1356: FROM
1357: FA_RETIREMENTS A,
1358: JAI_FA_AST_BLOCK_DTLS B,
1359: JAI_FA_AST_BLOCKS C
1360: WHERE A.ASSET_ID = B.ASSET_ID
1361: AND B.ASSET_TYPE IN ( LV_CAPITALIZED , LV_EXPENSED )
1362: AND A.STATUS = LV_STATUS
1374: A.DATE_PLACED_IN_SERVICE
1375: FROM
1376: FA_BOOKS A,
1377: FA_ADDITIONS B,
1378: JAI_FA_AST_BLOCK_DTLS C
1379: WHERE A.ASSET_ID = P_ASSET_ID
1380: AND A.ASSET_ID = C.ASSET_ID
1381: AND ( ( B.ASSET_TYPE = LV_CAPITALIZED
1382: AND A.CAPITALIZE_FLAG = LV_FLAG )
1396: A.DATE_PLACED_IN_SERVICE
1397: FROM
1398: FA_BOOKS A,
1399: FA_ADDITIONS B,
1400: JAI_FA_AST_BLOCK_DTLS C
1401: WHERE A.ASSET_ID = P_ASSET_ID
1402: AND A.ASSET_ID = C.ASSET_ID
1403: AND ( ( B.ASSET_TYPE = LV_CAPITALIZED
1404: AND A.CAPITALIZE_FLAG = LV_FLAG )
1451: A.UNITS,
1452: TO_NUMBER(B.BLOCK_ID)
1453: FROM
1454: FA_RETIREMENTS A,
1455: JAI_FA_AST_BLOCK_DTLS B
1456: WHERE A.DATE_RETIRED <= P_YEAR_END
1457: AND A.ASSET_ID = B.ASSET_ID
1458: AND A.STATUS = LV_STATUS
1459: AND A.ASSET_ID = P_ASSET_ID
1464: H.UNITS,
1465: H.ASSET_ID
1466: FROM
1467: FA_ASSET_HISTORY H,
1468: JAI_FA_AST_BLOCK_DTLS A
1469: WHERE H.TRANSACTION_HEADER_ID_IN IN (
1470: SELECT
1471: MIN(TRANSACTION_HEADER_ID_IN)
1472: FROM
1483: FA_ADDITIONS A,
1484: JAI_FA_AST_BLOCKS B,
1485: JAI_FA_AST_PERIOD_RATES C,
1486: FA_BOOKS D,
1487: JAI_FA_AST_BLOCK_DTLS E
1488: WHERE A.OWNED_LEASED = LV_OWNED_LEASED
1489: AND ( ( A.ASSET_TYPE = LV_CAPITALIZED
1490: AND D.CAPITALIZE_FLAG = LV_FLAG )
1491: OR A.ASSET_TYPE = LV_EXPENSED )
1532: SELECT
1533: COUNT(*)
1534: FROM
1535: FA_RETIREMENTS A,
1536: JAI_FA_AST_BLOCK_DTLS B,
1537: JAI_FA_AST_BLOCKS C
1538: WHERE A.DATE_RETIRED BETWEEN P_YEAR_START
1539: AND P_YEAR_END
1540: AND A.ASSET_ID = B.ASSET_ID
1544: CURSOR ASSET_ID_CURSOR(P_BLOCK_ID IN NUMBER) IS
1545: SELECT
1546: ASSET_ID
1547: FROM
1548: JAI_FA_AST_BLOCK_DTLS
1549: WHERE BLOCK_ID = P_BLOCK_ID;
1550: CURSOR OPENING_WDV_ADJ_CUR IS
1551: SELECT
1552: RATE,
1563: JABOA.RATE,
1564: JABOA.TYPE
1565: FROM
1566: JAI_FA_AST_BLOCKS JABOA,
1567: JAI_FA_AST_BLOCK_DTLS JBA
1568: WHERE JABOA.START_DATE BETWEEN P_YEAR_START
1569: AND P_YEAR_END
1570: AND JABOA.BOOK_TYPE_CODE = P_BOOK_NAME
1571: AND JABOA.CLOSING_WDV > 0
2272: CURSOR CUR_GET_VALID_ASSETS(CP_BLOCK_ID IN JAI_FA_AST_BLOCKS.BLOCK_ID%TYPE) IS
2273: SELECT
2274: '1'
2275: FROM
2276: JAI_FA_AST_BLOCK_DTLS JBA,
2277: FA_BOOKS FAB,
2278: JAI_FA_AST_BLOCKS JABOA
2279: WHERE JBA.ASSET_ID = FAB.ASSET_ID
2280: AND ( ( JBA.ASSET_TYPE = 'CAPITALIZED'
2621:
2622: PROCEDURE RUN_DEP_MASS(P_YEAR_START IN DATE
2623: ,P_YEAR_END IN DATE
2624: ,P_BOOK_NAME IN VARCHAR2) IS
2625: LV_CAPITALIZED JAI_FA_AST_BLOCK_DTLS.ASSET_TYPE%TYPE;
2626: LV_FLAG FA_BOOKS.CAPITALIZE_FLAG%TYPE;
2627: LV_EXPENSED JAI_FA_AST_BLOCK_DTLS.ASSET_TYPE%TYPE;
2628: K NUMBER := 0;
2629: I NUMBER := 0;
2623: ,P_YEAR_END IN DATE
2624: ,P_BOOK_NAME IN VARCHAR2) IS
2625: LV_CAPITALIZED JAI_FA_AST_BLOCK_DTLS.ASSET_TYPE%TYPE;
2626: LV_FLAG FA_BOOKS.CAPITALIZE_FLAG%TYPE;
2627: LV_EXPENSED JAI_FA_AST_BLOCK_DTLS.ASSET_TYPE%TYPE;
2628: K NUMBER := 0;
2629: I NUMBER := 0;
2630: V_COUNT1 NUMBER;
2631: V_COUNTER NUMBER;
3896: ,P_YEAR_START IN JAI_FA_AST_YEARS.YEAR_START%TYPE
3897: ,P_YEAR_END IN JAI_FA_AST_YEARS.YEAR_END%TYPE
3898: ,P_BLOCK_ID IN JAI_FA_AST_BLOCKS.BLOCK_ID%TYPE
3899: ,P_NO_ASSETS OUT NOCOPY VARCHAR2) RETURN NUMBER IS
3900: LV_CAPITALIZED JAI_FA_AST_BLOCK_DTLS.ASSET_TYPE%TYPE;
3901: LV_FLAG FA_BOOKS.CAPITALIZE_FLAG%TYPE;
3902: LV_EXPENSED JAI_FA_AST_BLOCK_DTLS.ASSET_TYPE%TYPE;
3903: LV_STATUS FA_RETIREMENTS.STATUS%TYPE;
3904: CURSOR CUR_GET_VALID_ASSETS IS
3898: ,P_BLOCK_ID IN JAI_FA_AST_BLOCKS.BLOCK_ID%TYPE
3899: ,P_NO_ASSETS OUT NOCOPY VARCHAR2) RETURN NUMBER IS
3900: LV_CAPITALIZED JAI_FA_AST_BLOCK_DTLS.ASSET_TYPE%TYPE;
3901: LV_FLAG FA_BOOKS.CAPITALIZE_FLAG%TYPE;
3902: LV_EXPENSED JAI_FA_AST_BLOCK_DTLS.ASSET_TYPE%TYPE;
3903: LV_STATUS FA_RETIREMENTS.STATUS%TYPE;
3904: CURSOR CUR_GET_VALID_ASSETS IS
3905: SELECT
3906: '1'
3904: CURSOR CUR_GET_VALID_ASSETS IS
3905: SELECT
3906: '1'
3907: FROM
3908: JAI_FA_AST_BLOCK_DTLS JBA,
3909: FA_BOOKS FAB,
3910: JAI_FA_AST_BLOCKS JABOA
3911: WHERE JBA.ASSET_ID = FAB.ASSET_ID
3912: AND ( ( JBA.ASSET_TYPE = LV_CAPITALIZED
3957: A.DATE_EFFECTIVE
3958: FROM
3959: FA_BOOKS A,
3960: JAI_FA_AST_BLOCKS B,
3961: JAI_FA_AST_BLOCK_DTLS C,
3962: JAI_FA_AST_PERIOD_RATES D
3963: WHERE A.ASSET_ID = C.ASSET_ID
3964: AND ( ( C.ASSET_TYPE = LV_CAPITALIZED
3965: AND A.CAPITALIZE_FLAG = LV_FLAG )
4367: FAB.DATE_EFFECTIVE,
4368: FAB.COST
4369: FROM
4370: FA_BOOKS FAB,
4371: JAI_FA_AST_BLOCK_DTLS JBA
4372: WHERE FAB.ASSET_ID = JBA.ASSET_ID
4373: AND JBA.BLOCK_ID = P_BLOCK_ID
4374: AND JBA.ASSET_ID = P_ASSET_ID
4375: AND FAB.TRANSACTION_HEADER_ID_OUT IS NULL