[Home] [Help]
1559: sum(nvl(RET.NBV_RETIRED,0)) net_proceeds,
1560: sum((nvl(RET.NBV_RETIRED,0))*nvl(BK2.REDUCTION_RATE,0)) net_proceeds_to_reduce
1561: from FA_BOOKS BK1,
1562: FA_BOOKS BK2,
1563: FA_RETIREMENTS RET,
1564: FA_TRANSACTION_HEADERS TH
1565: where TH.TRANSACTION_HEADER_ID = BK1.TRANSACTION_HEADER_ID_OUT(+)
1566: and TH.TRANSACTION_HEADER_ID = BK2.TRANSACTION_HEADER_ID_IN
1567: and TH.TRANSACTION_HEADER_ID = RET.TRANSACTION_HEADER_ID_IN(+)
1580: select sum(BK2.COST - nvl(BK1.COST,0)) total_change_in_cost,
1581: sum(nvl(RET.NBV_RETIRED,0)) total_net_proceeds
1582: from FA_BOOKS BK1,
1583: FA_BOOKS BK2,
1584: FA_RETIREMENTS RET,
1585: FA_TRANSACTION_HEADERS TH
1586: where TH.TRANSACTION_HEADER_ID = BK1.TRANSACTION_HEADER_ID_OUT(+)
1587: and TH.TRANSACTION_HEADER_ID = BK2.TRANSACTION_HEADER_ID_IN
1588: and TH.TRANSACTION_HEADER_ID = RET.TRANSACTION_HEADER_ID_IN(+)
1607: sum((nvl(RET.NBV_RETIRED,0))*nvl(BK3.REDUCTION_RATE,0)) net_proceeds_to_reduce
1608: from FA_BOOKS BK1,
1609: FA_BOOKS BK2,
1610: FA_BOOKS BK3,
1611: FA_RETIREMENTS RET,
1612: FA_TRANSACTION_HEADERS TH
1613: where TH.TRANSACTION_HEADER_ID = BK1.TRANSACTION_HEADER_ID_OUT(+)
1614: and TH.TRANSACTION_HEADER_ID = BK2.TRANSACTION_HEADER_ID_IN
1615: and TH.MEMBER_TRANSACTION_HEADER_ID = BK3.TRANSACTION_HEADER_ID_IN
1636: sum(nvl(RET.NBV_RETIRED,0)) total_net_proceeds
1637: from FA_BOOKS BK1,
1638: FA_BOOKS BK2,
1639: FA_BOOKS BK3,
1640: FA_RETIREMENTS RET,
1641: FA_TRANSACTION_HEADERS TH
1642: where TH.TRANSACTION_HEADER_ID = BK1.TRANSACTION_HEADER_ID_OUT(+)
1643: and TH.TRANSACTION_HEADER_ID = BK2.TRANSACTION_HEADER_ID_IN
1644: and TH.MEMBER_TRANSACTION_HEADER_ID = BK3.TRANSACTION_HEADER_ID_IN
1853: nvl(RET.NBV_RETIRED,0) cur_net_proceeds,
1854: nvl(RET.NBV_RETIRED,0)*nvl(BK2.REDUCTION_RATE,0) cur_net_proceeds_to_reduce
1855: from FA_BOOKS BK1,
1856: FA_BOOKS BK2,
1857: FA_RETIREMENTS RET,
1858: FA_TRANSACTION_HEADERS TH
1859: where TH.TRANSACTION_HEADER_ID = BK1.TRANSACTION_HEADER_ID_OUT(+)
1860: and TH.TRANSACTION_HEADER_ID = BK2.TRANSACTION_HEADER_ID_IN
1861: and TH.TRANSACTION_HEADER_ID = RET.TRANSACTION_HEADER_ID_IN(+)
3889: SELECT fth.transaction_type_code
3890: , fth.transaction_header_id
3891: INTO l_trx_type
3892: , l_old_trx_id
3893: FROM fa_retirements fr
3894: , fa_books fb
3895: , fa_transaction_headers fth
3896: WHERE fr.transaction_header_id_out = p_trans_rec.transaction_header_id
3897: AND fr.transaction_header_id_in = fb.transaction_header_id_in
3913: IF l_trx_type = 'PARTIAL RETIREMENT' THEN
3914: BEGIN
3915: SELECT (fb.adjusted_cost * p_asset_fin_rec_new.cost) / fb.COST
3916: INTO px_new_adjusted_cost
3917: FROM fa_retirements fr
3918: , fa_books fb
3919: WHERE fr.transaction_header_id_out = p_trans_rec.transaction_header_id
3920: AND fr.transaction_header_id_in = fb.transaction_header_id_in
3921: AND fr.asset_id = fb.asset_id
4151: cursor C_LTD_PROCEEDS (
4152: p_period_end_date date
4153: ) is
4154: select nvl(sum(ret.nbv_retired),0) ldt_proceeds
4155: from FA_RETIREMENTS ret,
4156: FA_TRANSACTION_HEADERS th
4157: where th.asset_id = p_asset_id
4158: and th.book_type_code = p_book_type_code
4159: and ret.status in ('PROCESSED','PENDING')
4164: cursor GP_LTD_PROCEEDS (
4165: p_period_end_date date
4166: ) is
4167: select nvl(sum(ret.nbv_retired),0) ldt_proceeds
4168: from FA_RETIREMENTS ret,
4169: FA_TRANSACTION_HEADERS th
4170: where th.asset_id = p_asset_id
4171: and th.book_type_code = p_book_type_code
4172: and ret.status in ('PROCESSED','PENDING')
4205: p_fy_start_date date,
4206: p_period_end_date date
4207: ) is
4208: select nvl(sum(ret.nbv_retired),0) ytd_proceeds
4209: from FA_RETIREMENTS ret,
4210: FA_TRANSACTION_HEADERS th
4211: where th.asset_id = p_asset_id
4212: and th.book_type_code = p_book_type_code
4213: and ret.status in ('PROCESSED','PENDING')
4220: p_fy_start_date date,
4221: p_period_end_date date
4222: ) is
4223: select nvl(sum(ret.nbv_retired),0) ytd_proceeds
4224: from FA_RETIREMENTS ret,
4225: FA_TRANSACTION_HEADERS th
4226: where th.asset_id = p_asset_id
4227: and th.book_type_code = p_book_type_code
4228: and ret.status in ('PROCESSED','PENDING')
4431: cursor C_LTD_RETIRED_COST (
4432: p_period_end_date date
4433: ) is
4434: select nvl(sum(ret.cost_retired),0)
4435: from FA_RETIREMENTS ret,
4436: FA_TRANSACTION_HEADERS th
4437: where th.asset_id = p_asset_id
4438: and th.book_type_code = p_book_type_code
4439: and ret.status in ('PROCESSED','PENDING')
4445: cursor GP_LTD_RETIRED_COST (
4446: p_period_end_date date
4447: ) is
4448: select nvl(sum(ret.cost_retired),0)
4449: from FA_RETIREMENTS ret,
4450: FA_TRANSACTION_HEADERS th
4451: where th.asset_id = p_asset_id
4452: and th.book_type_code = p_book_type_code
4453: and ret.status in ('PROCESSED','PENDING')
4485: p_fy_start_date date,
4486: p_period_end_date date
4487: ) is
4488: select nvl(sum(ret.cost_retired),0)
4489: from FA_RETIREMENTS ret,
4490: FA_TRANSACTION_HEADERS th
4491: where th.asset_id = p_asset_id
4492: and th.book_type_code = p_book_type_code
4493: and ret.status in ('PROCESSED','PENDING')
4500: p_fy_start_date date,
4501: p_period_end_date date
4502: ) is
4503: select nvl(sum(ret.cost_retired),0)
4504: from FA_RETIREMENTS ret,
4505: FA_TRANSACTION_HEADERS th
4506: where th.asset_id = p_asset_id
4507: and th.book_type_code = p_book_type_code
4508: and ret.status in ('PROCESSED','PENDING')
4674:
4675: -- Get Proceeds of member asset
4676: cursor C_GET_MEM_PROCEEDS is
4677: select nvl(RET.NBV_RETIRED,0),recognize_gain_loss
4678: from FA_RETIREMENTS RET
4679: where RET.TRANSACTION_HEADER_ID_IN = p_member_transaction_header_id;
4680:
4681: -- MRC
4682: cursor C_GET_MEM_PROCEEDS_M is