[Home] [Help]
212: nvl(P_mode,'DEPRECIATION') = 'GROUP ADJUSTMENT' then
213:
214: -- This is a case when this program is called from Group Adjustments.
215: -- So allocated amount will be inserted/updated into FA_ADJUSTMENTS or FA_DEPRN_SUMMARY/DETAIL
216: -- or FA_BOOKS following P_mode
217:
218: if not ins_dd_adj(P_book_type_Code => P_book_type_code,
219: P_group_asset_id => P_group_asset_id,
220: P_period_counter => h_period_counter,
404: nvl(ds.deprn_reserve,0) deprn_reserve,
405: nvl(ds.ytd_deprn,0) ytd_deprn,
406: nvl(ds.bonus_deprn_reserve,0) bonus_deprn_reserve,
407: nvl(ds.bonus_ytd_deprn,0) bonus_ytd_deprn
408: from fa_books bk,
409: fa_deprn_summary ds,
410: fa_additions_b ad
411: where bk.group_asset_id = h_group_asset_id
412: and bk.book_type_code = h_book_type_code
488: cursor ALL_MEMBERS_TOTAL is
489: select sum(bk.cost),
490: decode(h_excl_sv,'N',sum(bk.adjusted_cost),sum(bk.adjusted_cost+bk.salvage_value)),
491: sum(bk.recoverable_cost)
492: from fa_books bk,
493: fa_additions_b ad
494: where bk.group_asset_id = h_group_asset_id
495: and bk.date_ineffective is null
496: and bk.depreciate_flag = 'YES'
516: cursor MEMBERS_EX_RETIRED_TOTAL is
517: select sum(bk.cost),
518: decode(h_excl_sv,'N',sum(bk.adjusted_cost),sum(bk.adjusted_cost+bk.salvage_value)),
519: sum(bk.recoverable_cost)
520: from fa_books bk,
521: fa_additions_b ad
522: where bk.group_asset_id = h_group_asset_id
523: and bk.date_ineffective is null
524: and bk.period_counter_fully_retired is null
546: cursor MEMBERS_EX_RESERVED_TOTAL is
547: select sum(bk.cost),
548: decode(h_excl_sv,'N',sum(bk.adjusted_cost),sum(bk.adjusted_cost+bk.salvage_value)),
549: sum(bk.recoverable_cost)
550: from fa_books bk,
551: fa_additions_b ad
552: where bk.group_asset_id = h_group_asset_id
553: and bk.date_ineffective is null
554: and bk.period_counter_fully_reserved is null
576: cursor MEMBER_EX_BOTH_TOTAL is
577: select sum(bk.cost),
578: decode(h_excl_sv,'N',sum(bk.adjusted_cost),sum(bk.adjusted_cost+bk.salvage_value)),
579: sum(bk.recoverable_cost)
580: from fa_books bk,
581: fa_additions_b ad
582: where bk.group_asset_id = h_group_asset_id
583: and bk.date_ineffective is null
584: and bk.period_counter_fully_retired is null
629: ds.ytd_deprn,
630: ds.bonus_deprn_reserve,
631: ds.bonus_ytd_deprn
632: from fa_track_members tr,
633: fa_books bk,
634: fa_deprn_summary ds,
635: fa_deprn_periods dp,
636: fa_additions_b ad
637: where tr.group_asset_id = h_group_asset_id
693: --* Cursor for Group Adjusted Cost or Recoverable Cost
694: cursor GROUP_BASIS is
695: select decode(h_excl_sv,'N',bk.adjusted_cost,bk.adjusted_cost+bk.salvage_value) adjusted_cost,
696: bk.recoverable_cost recoverable_cost
697: from fa_books bk
698: where bk.book_type_code=h_book_type_code
699: and bk.asset_id = h_group_asset_id
700: and bk.date_ineffective is null;
701:
903: cursor c_get_basis_rule_name is
904: select db.rule_name
905: from fa_deprn_basis_rules db
906: , fa_methods mt
907: , fa_books bk
908: where db.deprn_basis_rule_id = mt.deprn_basis_rule_id
909: and mt.method_code = bk.deprn_method_code
910: and nvl(mt.life_in_months, -99) = nvl(bk.life_in_months, -99)
911: and bk.book_type_code = h_book_type_code
1087: h_total_cost := 0;
1088: h_total_adjusted_cost := 0;
1089: h_total_recoverable_cost := 0;
1090:
1091: -- issue query against fa_books_summary
1092: For i IN 1 .. p_track_member_table.COUNT LOOP
1093:
1094: if p_track_member_table(i).group_asset_id = h_group_asset_id and
1095: p_track_member_table(i).period_counter = h_period_counter and
1681: (mem.asset_id is not null) and -- ENERGY
1682: (nvl(l_track_member_out.allocated_deprn_amount, 0) <> 0) and -- ENERGY
1683: (nvl(P_mode,'DEPRECIATION') = 'UNPLANNED') then -- ENERGY
1684: -- ENERGY
1685: update fa_books -- ENERGY
1686: set adjusted_cost = adjusted_cost - nvl(l_track_member_out.allocated_deprn_amount, 0) -- ENERGY
1687: where transaction_header_id_out is null -- ENERGY
1688: and asset_id = mem.asset_id -- ENERGY
1689: and book_type_code = h_book_type_code; -- ENERGY
2979: l_unplanned_exp_tbl(l_count) := l_unplanned_exp_tbl(l_count) + l_unplanned_exp_mem_tbl(l_count);
2980: end loop;
2981:
2982: FORALL l_count IN 1..l_mem_asset_id_tbl.count
2983: UPDATE FA_BOOKS_SUMMARY
2984: SET DEPRN_AMOUNT = l_unplanned_exp_tbl(l_count) + DEPRN_AMOUNT,
2985: YTD_DEPRN = l_unplanned_exp_tbl(l_count) + YTD_DEPRN
2986: WHERE BOOK_TYPE_CODE = h_book_type_code
2987: AND PERIOD_COUNTER = h_period_counter
3945: temp.eofy_recoverable_cost temp_eofy_recoverable_cost,
3946: temp.eop_recoverable_cost temp_eop_recoverable_cost,
3947: temp.eofy_salvage_value temp_eofy_salvage_value,
3948: temp.eop_salvage_value temp_eop_salvage_value
3949: from fa_books bk,
3950: fa_deprn_summary ds,
3951: fa_track_members temp
3952: where bk.book_type_code = h_book_type_code
3953: and bk.group_asset_id = h_group_asset_id
4127: new_bk.salvage_type,
4128: decode(new_bk.salvage_type,'PCT',new_bk.percent_salvage_value),
4129: new_bk.deprn_limit_type,
4130: decode(new_bk.deprn_limit_type,'PCT',new_bk.allowed_deprn_limit)
4131: from fa_books new_bk,
4132: fa_books old_bk
4133: where new_bk.book_type_code = p_group_rule_in.book_type_code
4134: and new_bk.asset_id = h_member_asset_id
4135: and new_bk.transaction_header_id_in = h_adj_member_trans_header_id
4128: decode(new_bk.salvage_type,'PCT',new_bk.percent_salvage_value),
4129: new_bk.deprn_limit_type,
4130: decode(new_bk.deprn_limit_type,'PCT',new_bk.allowed_deprn_limit)
4131: from fa_books new_bk,
4132: fa_books old_bk
4133: where new_bk.book_type_code = p_group_rule_in.book_type_code
4134: and new_bk.asset_id = h_member_asset_id
4135: and new_bk.transaction_header_id_in = h_adj_member_trans_header_id
4136: and old_bk.book_type_code = new_bk.book_type_code
4319: new_bk.salvage_type,
4320: new_bk.percent_salvage_value,
4321: new_bk.deprn_limit_type,
4322: new_bk.allowed_deprn_limit
4323: from fa_books new_bk
4324: where new_bk.book_type_code = p_group_rule_in.book_type_code
4325: and new_bk.asset_id = h_member_asset_id
4326: and new_bk.transaction_header_id_in = h_adj_member_trans_header_id;
4327:
4340: and new_bk.set_of_books_id = h_set_of_books_id;
4341:
4342: cursor GET_EXCLUDE_FULLY_RSV_FLAG is
4343: select exclude_fully_rsv_flag
4344: from fa_books
4345: where book_type_code = h_book_type_code
4346: and asset_id = h_group_asset_id
4347: and date_ineffective is null;
4348:
4533: -- and nvl(h_transaction_type_code,'NULL') <> 'ADDITION'
4534: -- and nvl(h_transaction_type_code,'NULL') <> 'ADDITION/VOID' then
4535: and h_period_counter = h_trans_period_counter then -- This transaction is for this member
4536:
4537: -- Query old fa_books and new fa_books to calculate delta
4538: if p_group_rule_in.mrc_sob_type_code <> 'R' then
4539: open GET_DELTA;
4540: fetch GET_DELTA into h_adj_cost,h_adj_salvage_value,h_adj_adjusted_rec_cost,
4541: h_adj_salvage_type,h_adj_percent_salvage,
5023: end if; -- allocation type
5024:
5025: end loop;
5026:
5027: --* Following logic is prepared for exclude_salvage_value in FA_BOOKS is set.
5028: -- In this case, adjusted_cost of fully reserved should be removed from adjusted_cost
5029: -- of group asset. so need to maintain the memory table adjusted cost
5030: if nvl(h_exclude_fully_rsv_flag,'N') = 'Y' and
5031: nvl(p_group_rule_in.eofy_flag,'N') = 'Y' and
5242: rule_out => fa_rule_out, p_log_level_rec => p_log_level_rec)) then
5243: raise upd_deprn_err;
5244: end if;
5245:
5246: --* Update FA_BOOKS table
5247: if nvl(fa_rule_in.eofy_flag,'N') = 'Y' then
5248: h_prior_eofy_reserve := member.bk_eofy_reserve;
5249: h_new_eofy_reserve := fa_rule_in.current_total_rsv;
5250: h_eofy_adj_cost := member.adjusted_cost;
5259: h_prior_eofy_reserve := null;
5260: end if;
5261:
5262: if (p_log_level_rec.statement_level) then
5263: fa_debug_pkg.add(l_calling_fn, '++ update FA_BOOKS for ',member.member_asset_id, p_log_level_rec => p_log_level_rec);
5264: fa_debug_pkg.add(l_calling_fn, 'fa_rule_out.new_adjusted_cost ',fa_rule_out.new_adjusted_cost, p_log_level_rec => p_log_level_rec);
5265: fa_debug_pkg.add(l_calling_fn, 'h_eofy_adj_cost:h_eop_adj_cost:h_new_eofy_reserve ',
5266: h_eofy_adj_cost||':'||h_eop_adj_cost||':'||h_new_eofy_reserve, p_log_level_rec => p_log_level_rec);
5267: end if;
5265: fa_debug_pkg.add(l_calling_fn, 'h_eofy_adj_cost:h_eop_adj_cost:h_new_eofy_reserve ',
5266: h_eofy_adj_cost||':'||h_eop_adj_cost||':'||h_new_eofy_reserve, p_log_level_rec => p_log_level_rec);
5267: end if;
5268:
5269: update fa_books
5270: set adjusted_cost = fa_rule_out.new_adjusted_cost,
5271: eofy_adj_cost = h_eofy_adj_cost,
5272: eofy_reserve = h_new_eofy_reserve,
5273: eop_adj_cost = h_eop_adj_cost,
5448: rule_out => fa_rule_out, p_log_level_rec => p_log_level_rec)) then
5449: raise upd_deprn_err;
5450: end if;
5451:
5452: --* Update FA_BOOKS table
5453: if nvl(fa_rule_in.eofy_flag,'N') = 'Y' then
5454: h_new_eofy_reserve := fa_rule_in.current_total_rsv;
5455: h_eofy_adj_cost := member.adjusted_cost;
5456: h_prior_eofy_reserve := member.bk_eofy_reserve;
5465: h_prior_eofy_reserve := null;
5466: end if;
5467:
5468: if (p_log_level_rec.statement_level) then
5469: fa_debug_pkg.add(l_calling_fn, '++ update FA_BOOKS for ',member.member_asset_id, p_log_level_rec => p_log_level_rec);
5470: fa_debug_pkg.add(l_calling_fn, 'fa_rule_out.new_adjusted_cost ',fa_rule_out.new_adjusted_cost, p_log_level_rec => p_log_level_rec);
5471: fa_debug_pkg.add(l_calling_fn, 'h_eofy_adj_cost:h_eop_adj_cost:h_new_eofy_reserve ',
5472: h_eofy_adj_cost||':'||h_eop_adj_cost||':'||h_new_eofy_reserve, p_log_level_rec => p_log_level_rec);
5473: end if;
5660:
5661: end if;
5662:
5663: -- terminate the row
5664: fa_books_pkg.deactivate_row
5665: (X_asset_id => p_asset_hdr_rec.asset_id,
5666: X_book_type_code => p_asset_hdr_rec.book_type_code,
5667: X_transaction_header_id_out => p_transaction_header_id,
5668: X_date_ineffective => sysdate,
5681:
5682: x_asset_fin_rec.period_counter_life_complete := x_asset_fin_rec.period_counter_fully_reserved;
5683:
5684: -- insert the row
5685: fa_books_pkg.insert_row
5686: (X_Rowid => l_rowid,
5687: X_Book_Type_Code => p_asset_hdr_rec.book_type_code,
5688: X_Asset_Id => p_asset_hdr_rec.asset_id,
5689: X_Date_Placed_In_Service => x_asset_fin_rec.date_placed_in_service,
6060:
6061: -- cursor to get all members at the specified period
6062: cursor ALL_MEMBERS(h_date date) is
6063: select bk.asset_id
6064: from fa_books bk,
6065: fa_additions_b ad
6066: where bk.book_type_code = h_book_type_code
6067: and bk.group_asset_id = h_group_asset_id
6068: and bk.date_placed_in_service <= h_date
6104: decode(dp1.fiscal_year,h_fiscal_year,ds.ytd_deprn,0),
6105: ds.bonus_deprn_reserve,
6106: decode(dp1.fiscal_year,h_fiscal_year,ds.bonus_ytd_deprn,0),
6107: dp1.fiscal_year
6108: from fa_books bk,
6109: fa_deprn_periods dp,
6110: fa_deprn_periods dp1,
6111: fa_deprn_summary ds,
6112: fa_additions_b ad
6118: bk.transaction_header_id_out = h_last_trans_id or
6119: (bk.date_ineffective is null and
6120: bk.transaction_header_id_in <> nvl(p_transaction_header_id,-1) and
6121: not exists (select 'y'
6122: from fa_books bk1
6123: where bk1.book_type_code = bk.book_type_code
6124: and bk1.asset_id = bk.asset_id
6125: and bk1.transaction_header_id_out = nvl(p_transaction_header_id,-1))))
6126: and bk.depreciate_flag = decode(p_transaction_header_id,NULL,'YES',bk.depreciate_flag) -- added for bug 8584206
6208: ds.ytd_deprn,
6209: ds.bonus_deprn_reserve,
6210: ds.bonus_ytd_deprn,
6211: dp.fiscal_year
6212: from fa_books bk,
6213: fa_deprn_periods dp,
6214: fa_deprn_summary ds,
6215: fa_additions_b ad
6216: where dp.book_type_code = h_book_type_code
6274:
6275: cursor MEMBER_START_PERIOD is
6276: select bk.recoverable_cost,
6277: bk.salvage_value
6278: from fa_books bk,
6279: fa_deprn_periods dp
6280: where bk.book_type_code = h_book_type_code
6281: and bk.group_asset_id = h_member_asset_id
6282: and bk.date_effective <= nvl(dp.period_close_date,sysdate)
6509:
6510: if nvl(p_calling_fn,'OTHER') = 'TRACK_ASSETS' then -- Get group level info
6511: select deprn_method_code,life_in_months
6512: into h_method_code,h_life_in_months
6513: from fa_books
6514: where book_type_code = h_book_type_code
6515: and asset_id = h_group_asset_id
6516: and date_ineffective is null;
6517: end if;
6630: h_deprn_reserve, h_ytd_deprn,
6631: h_bonus_deprn_reserve, h_bonus_ytd_deprn, h_eop_fiscal_year;
6632: if ALL_MEMBER_FOR_ADDITION%NOTFOUND then
6633: if (p_log_level_rec.statement_level) then
6634: fa_debug_pkg.add(l_calling_fn, 'No rows in FA_BOOKS of member asset', h_member_asset_id, p_log_level_rec => p_log_level_rec);
6635: end if;
6636: else
6637: i := i + 1;
6638: p_track_member_table(i).group_asset_id := h_group_asset_id;
6900: h_deprn_reserve, h_ytd_deprn,
6901: h_bonus_deprn_reserve, h_bonus_ytd_deprn, h_eop_fiscal_year;
6902: if ALL_MEMBER_FOR_ADDITION_MRC%NOTFOUND then
6903: if (p_log_level_rec.statement_level) then
6904: fa_debug_pkg.add(l_calling_fn, 'No rows in FA_BOOKS(Reporting Book) of member asset', h_member_asset_id);
6905: end if;
6906: i := i + 1;
6907: p_track_member_table(i).group_asset_id := h_group_asset_id;
6908: p_track_member_table(i).member_asset_id := h_member_asset_id;
7176: BK_OUT.DEPRN_LIMIT_TYPE old_limit_type,
7177: BK_IN.ALLOWED_DEPRN_LIMIT new_deprn_limit,
7178: BK_IN.ALLOWED_DEPRN_LIMIT_AMOUNT new_deprn_limit_amount,
7179: BK_IN.DEPRECIATE_FLAG depreciate_flag
7180: from FA_BOOKS BK_IN,
7181: FA_BOOKS BK_OUT
7182: where BK_IN.book_type_code = h_book_type_code
7183: and BK_IN.asset_id = h_member_asset_id
7184: and BK_IN.transaction_header_id_in = p_transaction_header_id
7177: BK_IN.ALLOWED_DEPRN_LIMIT new_deprn_limit,
7178: BK_IN.ALLOWED_DEPRN_LIMIT_AMOUNT new_deprn_limit_amount,
7179: BK_IN.DEPRECIATE_FLAG depreciate_flag
7180: from FA_BOOKS BK_IN,
7181: FA_BOOKS BK_OUT
7182: where BK_IN.book_type_code = h_book_type_code
7183: and BK_IN.asset_id = h_member_asset_id
7184: and BK_IN.transaction_header_id_in = p_transaction_header_id
7185: and BK_OUT.book_type_code(+) = BK_IN.book_type_code
7207:
7208: -- cursor to get all members at the specified period
7209: cursor ALL_MEMBERS_AT_AMORT(p_fiscal_year number,p_period_num number) is
7210: select distinct bk.asset_id member_asset_id, ad.asset_number
7211: from fa_books bk,
7212: fa_additions_b ad
7213: where bk.book_type_code = h_book_type_code
7214: and bk.group_asset_id = h_group_asset_id
7215: and bk.depreciate_flag = 'YES'
7256: order by ad.asset_number asc;
7257:
7258: cursor RECLASS_TRANS_CHECK(p_member_asset_id number,p_thid number) is
7259: select BK_IN.group_asset_id
7260: from fa_books BK_IN
7261: where BK_IN.book_type_code = h_book_type_code
7262: and BK_IN.asset_id = p_member_asset_id
7263: and BK_IN.transaction_header_id_in = p_thid;
7264:
7391: raise get_member_at_start_err;
7392: end if;
7393: end if;
7394:
7395: -- Query member assets from FA_BOOKS at the time of transction_date_entered
7396:
7397: i := 0;
7398: if p_mrc_sob_type_code <> 'R' then
7399:
7901: --+=====================================================================
7902: -- Function: update_member_books
7903: --
7904: -- This function will be called from adjustment engine
7905: -- to update FA_BOOKS for each member assets
7906: -- Using stored adjusted_cost in FA_TRACK_MEMBERS,
7907: -- FA_BOOKS will be updated.
7908: --
7909: --+=====================================================================
7903: --
7904: -- This function will be called from adjustment engine
7905: -- to update FA_BOOKS for each member assets
7906: -- Using stored adjusted_cost in FA_TRACK_MEMBERS,
7907: -- FA_BOOKS will be updated.
7908: --
7909: --+=====================================================================
7910:
7911: FUNCTION update_member_books(p_trans_rec in FA_API_TYPES.trans_rec_type,
7957: -- cursor to query start period condition of all members belonged to the specified group
7958: cursor ALL_MEMBERS is
7959: select bk.asset_id,
7960: bk.group_asset_id
7961: from fa_books bk
7962: where bk.book_type_code = h_book_type_code
7963: and bk.group_asset_id = h_group_asset_id
7964: and bk.date_ineffective is null
7965: and bk.depreciate_flag = 'YES'
8023: if (p_log_level_rec.statement_level) then
8024: fa_debug_pkg.add(l_calling_fn, 'h_set_of_books_id:period_counter', h_set_of_books_id||':'||h_period_counter, p_log_level_rec => p_log_level_rec);
8025: end if;
8026:
8027: -- Query member assets from FA_BOOKS with current date
8028: if p_mrc_sob_type_code <> 'R' then
8029:
8030: -- Loop for all member populated above
8031: For update_member in ALL_MEMBERS loop
8067: fa_debug_pkg.add('fa_track_member_pvt', 'adjusted cost', h_adjusted_cost, p_log_level_rec => p_log_level_rec);
8068: fa_debug_pkg.add('fa_track_member_pvt', 'eofy reserve', h_eofy_reserve, p_log_level_rec => p_log_level_rec);
8069: end if;
8070:
8071: -- Update FA_BOOKS table
8072: Update FA_BOOKS set adjusted_cost = h_adjusted_cost,
8073: eofy_reserve = h_eofy_reserve,
8074: last_update_date = sysdate,
8075: last_updated_by = -1
8068: fa_debug_pkg.add('fa_track_member_pvt', 'eofy reserve', h_eofy_reserve, p_log_level_rec => p_log_level_rec);
8069: end if;
8070:
8071: -- Update FA_BOOKS table
8072: Update FA_BOOKS set adjusted_cost = h_adjusted_cost,
8073: eofy_reserve = h_eofy_reserve,
8074: last_update_date = sysdate,
8075: last_updated_by = -1
8076: where book_type_code = h_book_type_code
8078: and group_asset_id = h_group_asset_id
8079: and date_ineffective is null;
8080: /* Bug 6929073 - The following update statement added for half year basis rule with 50% reduction rule.
8081: when the member asset is added in second half then its adjusted_cost should be 50%.
8082: But its being populated as full value in 'Book' row of fa_ds. in Fa_books also its populated with full value.
8083: But the above update statement is updating the correct value, if tracking method is allocate.
8084: The following update statement written similar to above. */
8085: Update fa_deprn_summary set adjusted_cost = h_adjusted_cost
8086: where book_type_code = h_book_type_code
8090: where deprn_source_code = 'DEPRN'
8091: and asset_id = h_member_asset_id
8092: and book_type_code = h_book_type_code)
8093: and exists (select 'reduction rate'
8094: from fa_books
8095: where asset_id = h_group_asset_id
8096: and book_type_code = h_book_type_code
8097: and transaction_header_id_out is null
8098: and nvl(reduction_rate,0) <> 0
8147: fa_debug_pkg.add('fa_track_member_pvt', 'adjusted cost', h_adjusted_cost, p_log_level_rec => p_log_level_rec);
8148: fa_debug_pkg.add('fa_track_member_pvt', 'eofy reserve', h_eofy_reserve, p_log_level_rec => p_log_level_rec);
8149: end if;
8150:
8151: -- Update FA_BOOKS table
8152: Update FA_MC_BOOKS set adjusted_cost = h_adjusted_cost,
8153: eofy_reserve = h_eofy_reserve,
8154: last_update_date = sysdate,
8155: last_updated_by = -1
8160: and set_of_books_id = h_set_of_books_id;
8161:
8162: /* Bug 6929073 - The following update statement added for half year basis rule with 50% reduction rule.
8163: when the member asset is added in second half then its adjusted_cost should be 50%.
8164: But its being populated as full value. in Fa_books also its populated with full value.
8165: But the above update statement is updating the correct value, if tracking method is allocate.
8166: The following update statement written similar to above. */
8167:
8168: Update fa_mc_deprn_summary set adjusted_cost = h_adjusted_cost
8576: cursor GET_MEMBER_ASSETS(p_fiscal_year number,p_period_num number) is
8577: select distinct BK.ASSET_ID, AD.asset_number,
8578: bk_max.transaction_header_id_in as max_trx_id_in_this_group,
8579: bk_other_max.transaction_header_id_in as max_trx_id_in_other_group
8580: from FA_BOOKS BK,
8581: FA_ADDITIONS_B AD,
8582: (
8583: select distinct bk_in.asset_id, bk_in.book_type_code,
8584: first_value(bk_in.transaction_header_id_in)
8583: select distinct bk_in.asset_id, bk_in.book_type_code,
8584: first_value(bk_in.transaction_header_id_in)
8585: over (partition by bk_in.asset_id, bk_in.book_type_code
8586: order by bk_in.transaction_header_id_in desc nulls last) as transaction_header_id_in
8587: from fa_books bk_in,
8588: FA_TRANSACTION_HEADERS TH1,
8589: FA_CALENDAR_PERIODS DP1,
8590: FA_FISCAL_YEAR FY
8591: where TH1.book_type_code = BK_in.book_type_code
8605: select distinct bk_in.asset_id, bk_in.book_type_code,
8606: first_value(bk_in.transaction_header_id_in)
8607: over (partition by bk_in.asset_id, bk_in.book_type_code
8608: order by bk_in.transaction_header_id_in desc nulls last) as transaction_header_id_in
8609: from fa_books bk_in,
8610: FA_TRANSACTION_HEADERS TH1,
8611: FA_CALENDAR_PERIODS DP1,
8612: FA_FISCAL_YEAR FY
8613: where TH1.book_type_code = BK_in.book_type_code
8623: and nvl(bk_in.group_asset_id,-1) <> h_group_asset_id
8624: and (bk_in.asset_id, bk_in.book_type_code) in
8625: (
8626: select distinct bk_in2.asset_id, bk_in2.book_type_code
8627: from fa_books bk_in2,
8628: FA_TRANSACTION_HEADERS TH2,
8629: FA_CALENDAR_PERIODS DP2,
8630: FA_FISCAL_YEAR FY2
8631: where TH2.book_type_code = BK_in2.book_type_code
8743: select bk.allocate_to_fully_ret_flag,
8744: bk.allocate_to_fully_rsv_flag,
8745: bk.period_counter_fully_retired,
8746: bk.period_counter_fully_reserved
8747: from fa_books bk
8748: where bk.book_type_code = h_book_type_code
8749: and bk.asset_id = p_asset_id
8750: and bk.date_ineffective is null;
8751:
8791: BK_IN.DEPRECIATE_FLAG depreciate_flag,
8792: BK_IN.group_asset_id group_asset_id,
8793: BK_IN.period_counter_fully_retired period_counter_fully_retired,
8794: bk_in.adjusted_cost -nvl(bk_out.adjusted_cost, 0) delta_adjusted_cost -- Bug 8484007
8795: from FA_BOOKS BK_IN,
8796: FA_BOOKS BK_OUT
8797: where BK_IN.book_type_code = h_book_type_code
8798: and BK_IN.group_asset_id = h_group_asset_id
8799: and BK_IN.asset_id = h_member_asset_id
8792: BK_IN.group_asset_id group_asset_id,
8793: BK_IN.period_counter_fully_retired period_counter_fully_retired,
8794: bk_in.adjusted_cost -nvl(bk_out.adjusted_cost, 0) delta_adjusted_cost -- Bug 8484007
8795: from FA_BOOKS BK_IN,
8796: FA_BOOKS BK_OUT
8797: where BK_IN.book_type_code = h_book_type_code
8798: and BK_IN.group_asset_id = h_group_asset_id
8799: and BK_IN.asset_id = h_member_asset_id
8800: and BK_IN.transaction_header_id_in = p_transaction_header_id
8908: and adj.book_type_code = h_book_type_code
8909: and adj.period_counter_adjusted = p_period_counter
8910: and set_of_books_id = h_set_of_books_id;
8911:
8912: --* cursor to populate Group Assets record from FA_BOOKS_SUMMARY
8913: cursor GET_GROUP_INFO(p_period_counter number) is
8914: select FISCAL_YEAR,
8915: PERIOD_NUM,
8916: CALENDAR_PERIOD_OPEN_DATE,
8934: -- EOFY_SALVAGE_VALULE,
8935: EOFY_RESERVE,
8936: SYSTEM_DEPRN_AMOUNT,
8937: SYSTEM_BONUS_DEPRN_AMOUNT
8938: from FA_BOOKS_SUMMARY
8939: where book_type_code = h_book_type_code
8940: and asset_id = h_group_asset_id
8941: and period_counter = p_period_counter;
8942:
8973:
8974: cursor GET_GROUP_SYSTEM_INFO(p_fiscal_year number, p_period_counter_end number) is
8975: select sum(nvl(SYSTEM_DEPRN_AMOUNT,0)),
8976: sum(nvl(SYSTEM_BONUS_DEPRN_AMOUNT,0))
8977: from FA_BOOKS_SUMMARY
8978: where book_type_code = h_book_type_code
8979: and asset_id = h_group_asset_id
8980: and fiscal_year = p_fiscal_year
8981: and period_counter <= p_period_counter_end;
8993: --* cursor to populate Group Assets record (Temporary)
8994: cursor GET_GROUP_SALVAGE(p_fiscal_year number, p_period_num number) is
8995: select SALVAGE_VALUE,
8996: RECOVERABLE_COST
8997: from FA_BOOKS_SUMMARY
8998: where book_type_code = h_book_type_code
8999: and asset_id = h_group_asset_id
9000: and fiscal_year = p_fiscal_year
9001: and period_num = p_period_num;
9009: and fiscal_year = p_fiscal_year
9010: and period_num = p_period_num
9011: and set_of_books_id = h_set_of_books_id;
9012:
9013: -- cursor to query fa_books_summary for this member/period
9014: cursor GET_PRV_ROW_BS is
9015: select COST,
9016: SALVAGE_VALUE,
9017: RECOVERABLE_COST,
9021: BONUS_DEPRN_RESERVE,
9022: YTD_DEPRN,
9023: BONUS_YTD_DEPRN,
9024: EOFY_RESERVE
9025: from FA_BOOKS_SUMMARY
9026: where book_type_code = h_book_type_code
9027: and group_asset_id = h_group_asset_id
9028: and period_counter = h_processing_period_counter -1
9029: and asset_id = h_member_asset_id;
9048:
9049: --* Get new group asset id
9050: cursor GET_NEW_GROUP(p_member_asset_id number, p_transaction_header_id number) is
9051: select BK_IN.group_asset_id group_asset_id
9052: from FA_BOOKS BK_IN,
9053: FA_BOOKS BK_OUT
9054: where BK_IN.book_type_code = h_book_type_code
9055: and BK_IN.asset_id = h_member_asset_id
9056: and BK_IN.transaction_header_id_in = p_transaction_header_id
9049: --* Get new group asset id
9050: cursor GET_NEW_GROUP(p_member_asset_id number, p_transaction_header_id number) is
9051: select BK_IN.group_asset_id group_asset_id
9052: from FA_BOOKS BK_IN,
9053: FA_BOOKS BK_OUT
9054: where BK_IN.book_type_code = h_book_type_code
9055: and BK_IN.asset_id = h_member_asset_id
9056: and BK_IN.transaction_header_id_in = p_transaction_header_id
9057: and BK_OUT.book_type_code(+) = BK_IN.book_type_code
9104: fa_debug_pkg.add(l_calling_fn, 'l_member_asset_id', l_member_asset_id, p_log_level_rec => p_log_level_rec);
9105: end if;
9106: end if;
9107:
9108: -- Query Group Asset DPIS' period counter and amort date's period counter from FA_BOOKS
9109: select deprn_calendar, fiscal_year_name
9110: into h_deprn_calendar, h_fiscal_year_name
9111: from fa_book_controls
9112: where book_type_code = h_book_type_code;
9187: and period_close_date is null;
9188:
9189: select exclude_fully_rsv_flag,recognize_gain_loss
9190: into h_exclude_fully_rsv_flag,h_group_recognize_gain_loss
9191: from fa_books
9192: where book_type_code = h_book_type_code
9193: and asset_id = h_group_asset_id
9194: and date_ineffective is null;
9195:
9304: if (p_log_level_rec.statement_level) then
9305: if (h_find_flag_1) then
9306: fa_debug_pkg.add(l_calling_fn, 'Memory Table has data for this asset', '+++', p_log_level_rec => p_log_level_rec);
9307: else
9308: fa_debug_pkg.add(l_calling_fn, 'Need to query fa_books_summary table due to no data in'||
9309: ' memory table','+++', p_log_level_rec => p_log_level_rec);
9310: end if;
9311: end if;
9312:
9743: h_bonus_ytd_deprn,
9744: h_eofy_reserve;
9745: if GET_PRV_ROW_BS%NOTFOUND then
9746: if (p_log_level_rec.statement_level) then
9747: fa_debug_pkg.add(l_calling_fn, 'No data in FA_BOOKS_SUMMARY', '***');
9748: end if;
9749: else
9750: if (p_log_level_rec.statement_level) then
9751: fa_debug_pkg.add(l_calling_fn, 'Data in FA_BOOKS_SUMMARY', 'FOUND', p_log_level_rec => p_log_level_rec);
9747: fa_debug_pkg.add(l_calling_fn, 'No data in FA_BOOKS_SUMMARY', '***');
9748: end if;
9749: else
9750: if (p_log_level_rec.statement_level) then
9751: fa_debug_pkg.add(l_calling_fn, 'Data in FA_BOOKS_SUMMARY', 'FOUND', p_log_level_rec => p_log_level_rec);
9752: end if;
9753: if h_processing_period_num = 1 then
9754: h_ytd_deprn := 0;
9755: h_bonus_ytd_deprn := 0;
10193: h_bonus_ytd_deprn,
10194: h_eofy_reserve;
10195: if GET_PRV_ROW_BS_MRC%NOTFOUND then
10196: if (p_log_level_rec.statement_level) then
10197: fa_debug_pkg.add(l_calling_fn, 'No data in FA_BOOKS_SUMMARY', '***');
10198: end if;
10199: else
10200: if (p_log_level_rec.statement_level) then
10201: fa_debug_pkg.add(l_calling_fn, 'Data in FA_BOOKS_SUMMARY', 'FOUND', p_log_level_rec => p_log_level_rec);
10197: fa_debug_pkg.add(l_calling_fn, 'No data in FA_BOOKS_SUMMARY', '***');
10198: end if;
10199: else
10200: if (p_log_level_rec.statement_level) then
10201: fa_debug_pkg.add(l_calling_fn, 'Data in FA_BOOKS_SUMMARY', 'FOUND', p_log_level_rec => p_log_level_rec);
10202: end if;
10203: if h_processing_period_num = 1 then
10204: h_ytd_deprn := 0;
10205: h_bonus_ytd_deprn := 0;
10552: end if;
10553:
10554: End Loop; -- (For i IN 1.. h_length_of_loop)
10555:
10556: --* Following logic is prepared for exclude_salvage_value in FA_BOOKS is set.
10557: -- In this case, adjusted_cost of fully reserved should be removed from adjusted_cost
10558: -- of group asset. so need to maintain the memory table adjusted cost
10559: if nvl(h_exclude_fully_rsv_flag,'N') = 'Y' and
10560: nvl(fa_cache_pkg.fazccmt_record.deprn_basis_rule,'COST') = 'NBV' then
10862: fetch FA_ADJ_RESERVE into h_adj_reserve;
10863: close FA_ADJ_RESERVE;
10864:
10865: select eofy_reserve into h_eofy_reserve
10866: from fa_books
10867: where book_type_code = h_book_type_code
10868: and asset_id = h_member_asset_id
10869: and date_ineffective is null;
10870:
11312:
11313: --+=====================================================================
11314: -- Function: create_update_books_summary
11315: --
11316: -- This function will be called to insert row into fa_books_summary if not exists
11317: -- update fa_books_summary row if exists
11318: -- Used merge statement for bug 7195989
11319: --+=====================================================================
11320:
11313: --+=====================================================================
11314: -- Function: create_update_books_summary
11315: --
11316: -- This function will be called to insert row into fa_books_summary if not exists
11317: -- update fa_books_summary row if exists
11318: -- Used merge statement for bug 7195989
11319: --+=====================================================================
11320:
11321: FUNCTION create_update_bs_table(p_trans_rec in FA_API_TYPES.trans_rec_type,
11472: );
11473:
11474: else
11475: forall j in 1..l_tbl_member_asset_id.COUNT
11476: MERGE INTO FA_BOOKS_SUMMARY BS
11477: USING (
11478: SELECT
11479: l_tbl_group_asset_id(j) as group_asset_id,
11480: l_tbl_member_asset_id(j) as member_asset_id,