[Home] [Help]
1430: sum(nvl(RET.NBV_RETIRED,0)) net_proceeds,
1431: sum((nvl(RET.NBV_RETIRED,0))*nvl(BK2.REDUCTION_RATE,0)) net_proceeds_to_reduce
1432: from FA_BOOKS BK1,
1433: FA_BOOKS BK2,
1434: FA_RETIREMENTS RET,
1435: FA_TRANSACTION_HEADERS TH
1436: where TH.TRANSACTION_HEADER_ID = BK1.TRANSACTION_HEADER_ID_OUT(+)
1437: and TH.TRANSACTION_HEADER_ID = BK2.TRANSACTION_HEADER_ID_IN
1438: and TH.TRANSACTION_HEADER_ID = RET.TRANSACTION_HEADER_ID_IN(+)
1450: select sum(BK2.COST - nvl(BK1.COST,0)+nvl(RET.COST_RETIRED,0)) total_change_in_cost,
1451: sum(nvl(RET.NBV_RETIRED,0)) total_net_proceeds
1452: from FA_BOOKS BK1,
1453: FA_BOOKS BK2,
1454: FA_RETIREMENTS RET,
1455: FA_TRANSACTION_HEADERS TH
1456: where TH.TRANSACTION_HEADER_ID = BK1.TRANSACTION_HEADER_ID_OUT(+)
1457: and TH.TRANSACTION_HEADER_ID = BK2.TRANSACTION_HEADER_ID_IN
1458: and TH.TRANSACTION_HEADER_ID = RET.TRANSACTION_HEADER_ID_IN(+)
1477: sum((nvl(RET.NBV_RETIRED,0))*nvl(BK3.REDUCTION_RATE,0)) net_proceeds_to_reduce
1478: from FA_BOOKS BK1,
1479: FA_BOOKS BK2,
1480: FA_BOOKS BK3,
1481: FA_RETIREMENTS RET,
1482: FA_TRANSACTION_HEADERS TH
1483: where TH.TRANSACTION_HEADER_ID = BK1.TRANSACTION_HEADER_ID_OUT(+)
1484: and TH.TRANSACTION_HEADER_ID = BK2.TRANSACTION_HEADER_ID_IN
1485: and TH.MEMBER_TRANSACTION_HEADER_ID = BK3.TRANSACTION_HEADER_ID_IN
1505: sum(nvl(RET.NBV_RETIRED,0)) total_net_proceeds
1506: from FA_BOOKS BK1,
1507: FA_BOOKS BK2,
1508: FA_BOOKS BK3,
1509: FA_RETIREMENTS RET,
1510: FA_TRANSACTION_HEADERS TH
1511: where TH.TRANSACTION_HEADER_ID = BK1.TRANSACTION_HEADER_ID_OUT(+)
1512: and TH.TRANSACTION_HEADER_ID = BK2.TRANSACTION_HEADER_ID_IN
1513: and TH.MEMBER_TRANSACTION_HEADER_ID = BK3.TRANSACTION_HEADER_ID_IN
1542: sum(nvl(RET.NBV_RETIRED,0)) net_proceeds,
1543: sum((nvl(RET.NBV_RETIRED,0))*nvl(BK2.REDUCTION_RATE,0)) net_proceeds_to_reduce
1544: from FA_BOOKS_MRC_V BK1,
1545: FA_BOOKS_MRC_V BK2,
1546: FA_RETIREMENTS_MRC_V RET,
1547: FA_TRANSACTION_HEADERS TH
1548: where TH.TRANSACTION_HEADER_ID = BK1.TRANSACTION_HEADER_ID_OUT(+)
1549: and TH.TRANSACTION_HEADER_ID = BK2.TRANSACTION_HEADER_ID_IN
1550: and TH.TRANSACTION_HEADER_ID = RET.TRANSACTION_HEADER_ID_IN(+)
1562: select sum(BK2.COST - nvl(BK1.COST,0)+nvl(RET.COST_RETIRED,0)) total_change_in_cost,
1563: sum(nvl(RET.NBV_RETIRED,0)) total_net_proceeds
1564: from FA_BOOKS_MRC_V BK1,
1565: FA_BOOKS_MRC_V BK2,
1566: FA_RETIREMENTS_MRC_V RET,
1567: FA_TRANSACTION_HEADERS TH
1568: where TH.TRANSACTION_HEADER_ID = BK1.TRANSACTION_HEADER_ID_OUT(+)
1569: and TH.TRANSACTION_HEADER_ID = BK2.TRANSACTION_HEADER_ID_IN
1570: and TH.TRANSACTION_HEADER_ID = RET.TRANSACTION_HEADER_ID_IN(+)
1589: sum((nvl(RET.NBV_RETIRED,0))*nvl(BK3.REDUCTION_RATE,0)) net_proceeds_to_reduce
1590: from FA_BOOKS_MRC_V BK1,
1591: FA_BOOKS_MRC_V BK2,
1592: FA_BOOKS_MRC_V BK3,
1593: FA_RETIREMENTS_MRC_V RET,
1594: FA_TRANSACTION_HEADERS TH
1595: where TH.TRANSACTION_HEADER_ID = BK1.TRANSACTION_HEADER_ID_OUT(+)
1596: and TH.TRANSACTION_HEADER_ID = BK2.TRANSACTION_HEADER_ID_IN
1597: and TH.MEMBER_TRANSACTION_HEADER_ID = BK3.TRANSACTION_HEADER_ID_IN
1617: sum(nvl(RET.NBV_RETIRED,0)) total_net_proceeds
1618: from FA_BOOKS_MRC_V BK1,
1619: FA_BOOKS_MRC_V BK2,
1620: FA_BOOKS_MRC_V BK3,
1621: FA_RETIREMENTS_MRC_V RET,
1622: FA_TRANSACTION_HEADERS TH
1623: where TH.TRANSACTION_HEADER_ID = BK1.TRANSACTION_HEADER_ID_OUT(+)
1624: and TH.TRANSACTION_HEADER_ID = BK2.TRANSACTION_HEADER_ID_IN
1625: and TH.MEMBER_TRANSACTION_HEADER_ID = BK3.TRANSACTION_HEADER_ID_IN
3723: cursor C_LTD_PROCEEDS (
3724: p_period_end_date date
3725: ) is
3726: select nvl(sum(ret.nbv_retired),0) ldt_proceeds
3727: from FA_RETIREMENTS ret,
3728: FA_TRANSACTION_HEADERS th
3729: where th.asset_id = p_asset_id
3730: and th.book_type_code = p_book_type_code
3731: and ret.status in ('PROCESSED','PENDING')
3736: cursor GP_LTD_PROCEEDS (
3737: p_period_end_date date
3738: ) is
3739: select nvl(sum(ret.nbv_retired),0) ldt_proceeds
3740: from FA_RETIREMENTS ret,
3741: FA_TRANSACTION_HEADERS th
3742: where th.asset_id = p_asset_id
3743: and th.book_type_code = p_book_type_code
3744: and ret.status in ('PROCESSED','PENDING')
3748: cursor C_LTD_PROCEEDS_M (
3749: p_period_end_date date
3750: ) is
3751: select nvl(sum(ret.nbv_retired),0) ldt_proceeds
3752: from FA_RETIREMENTS_MRC_V ret,
3753: FA_TRANSACTION_HEADERS th
3754: where th.asset_id = p_asset_id
3755: and th.book_type_code = p_book_type_code
3756: and ret.status in ('PROCESSED','PENDING')
3761: cursor GP_LTD_PROCEEDS_M (
3762: p_period_end_date date
3763: ) is
3764: select nvl(sum(ret.nbv_retired),0) ldt_proceeds
3765: from FA_RETIREMENTS_MRC_V ret,
3766: FA_TRANSACTION_HEADERS th
3767: where th.asset_id = p_asset_id
3768: and th.book_type_code = p_book_type_code
3769: and ret.status in ('PROCESSED','PENDING')
3775: p_fy_start_date date,
3776: p_period_end_date date
3777: ) is
3778: select nvl(sum(ret.nbv_retired),0) ytd_proceeds
3779: from FA_RETIREMENTS ret,
3780: FA_TRANSACTION_HEADERS th
3781: where th.asset_id = p_asset_id
3782: and th.book_type_code = p_book_type_code
3783: and ret.status in ('PROCESSED','PENDING')
3790: p_fy_start_date date,
3791: p_period_end_date date
3792: ) is
3793: select nvl(sum(ret.nbv_retired),0) ytd_proceeds
3794: from FA_RETIREMENTS ret,
3795: FA_TRANSACTION_HEADERS th
3796: where th.asset_id = p_asset_id
3797: and th.book_type_code = p_book_type_code
3798: and ret.status in ('PROCESSED','PENDING')
3804: p_fy_start_date date,
3805: p_period_end_date date
3806: ) is
3807: select nvl(sum(ret.nbv_retired),0) ytd_proceeds
3808: from FA_RETIREMENTS_MRC_V ret,
3809: FA_TRANSACTION_HEADERS th
3810: where th.asset_id = p_asset_id
3811: and th.book_type_code = p_book_type_code
3812: and ret.status in ('PROCESSED','PENDING')
3819: p_fy_start_date date,
3820: p_period_end_date date
3821: ) is
3822: select nvl(sum(ret.nbv_retired),0) ytd_proceeds
3823: from FA_RETIREMENTS_MRC_V ret,
3824: FA_TRANSACTION_HEADERS th
3825: where th.asset_id = p_asset_id
3826: and th.book_type_code = p_book_type_code
3827: and ret.status in ('PROCESSED','PENDING')
3998: cursor C_LTD_RETIRED_COST (
3999: p_period_end_date date
4000: ) is
4001: select nvl(sum(ret.cost_retired),0)
4002: from FA_RETIREMENTS ret,
4003: FA_TRANSACTION_HEADERS th
4004: where th.asset_id = p_asset_id
4005: and th.book_type_code = p_book_type_code
4006: and ret.status in ('PROCESSED','PENDING')
4012: cursor GP_LTD_RETIRED_COST (
4013: p_period_end_date date
4014: ) is
4015: select nvl(sum(ret.cost_retired),0)
4016: from FA_RETIREMENTS ret,
4017: FA_TRANSACTION_HEADERS th
4018: where th.asset_id = p_asset_id
4019: and th.book_type_code = p_book_type_code
4020: and ret.status in ('PROCESSED','PENDING')
4024: cursor C_LTD_RETIRED_COST_M (
4025: p_period_end_date date
4026: ) is
4027: select nvl(sum(ret.cost_retired),0)
4028: from FA_RETIREMENTS_MRC_V ret,
4029: FA_TRANSACTION_HEADERS th
4030: where th.asset_id = p_asset_id
4031: and th.book_type_code = p_book_type_code
4032: and ret.status in ('PROCESSED','PENDING')
4036: cursor GP_LTD_RETIRED_COST_M (
4037: p_period_end_date date
4038: ) is
4039: select nvl(sum(ret.cost_retired),0)
4040: from FA_RETIREMENTS_MRC_V ret,
4041: FA_TRANSACTION_HEADERS th
4042: where th.asset_id = p_asset_id
4043: and th.book_type_code = p_book_type_code
4044: and ret.status in ('PROCESSED','PENDING')
4050: p_fy_start_date date,
4051: p_period_end_date date
4052: ) is
4053: select nvl(sum(ret.cost_retired),0)
4054: from FA_RETIREMENTS ret,
4055: FA_TRANSACTION_HEADERS th
4056: where th.asset_id = p_asset_id
4057: and th.book_type_code = p_book_type_code
4058: and ret.status in ('PROCESSED','PENDING')
4065: p_fy_start_date date,
4066: p_period_end_date date
4067: ) is
4068: select nvl(sum(ret.cost_retired),0)
4069: from FA_RETIREMENTS ret,
4070: FA_TRANSACTION_HEADERS th
4071: where th.asset_id = p_asset_id
4072: and th.book_type_code = p_book_type_code
4073: and ret.status in ('PROCESSED','PENDING')
4079: p_fy_start_date date,
4080: p_period_end_date date
4081: ) is
4082: select nvl(sum(ret.cost_retired),0)
4083: from FA_RETIREMENTS_MRC_V ret,
4084: FA_TRANSACTION_HEADERS th
4085: where th.asset_id = p_asset_id
4086: and th.book_type_code = p_book_type_code
4087: and ret.status in ('PROCESSED','PENDING')
4093: p_fy_start_date date,
4094: p_period_end_date date
4095: ) is
4096: select nvl(sum(ret.cost_retired),0)
4097: from FA_RETIREMENTS_MRC_V ret,
4098: FA_TRANSACTION_HEADERS th
4099: where th.asset_id = p_asset_id
4100: and th.book_type_code = p_book_type_code
4101: and ret.status in ('PROCESSED','PENDING')
4237:
4238: -- Get Proceeds of member asset
4239: cursor C_GET_MEM_PROCEEDS is
4240: select nvl(RET.NBV_RETIRED,0)
4241: from FA_RETIREMENTS RET
4242: where RET.TRANSACTION_HEADER_ID_IN = p_member_transaction_header_id;
4243:
4244: -- MRC
4245: cursor C_GET_MEM_PROCEEDS_M is
4243:
4244: -- MRC
4245: cursor C_GET_MEM_PROCEEDS_M is
4246: select nvl(RET.NBV_RETIRED,0)
4247: from FA_RETIREMENTS_MRC_V RET
4248: where RET.TRANSACTION_HEADER_ID_IN = p_member_transaction_header_id;
4249:
4250: -- Get reduction_rate of member asset
4251: cursor C_GET_REDUCITON_RATE is