1 PACKAGE FA_TRACK_MEMBER_PVT AUTHID CURRENT_USER as
2 /* $Header: FAVTRACKS.pls 120.17.12020000.2 2012/07/19 08:59:08 deemitta ship $ */
3
4 -- Declare for Tracking related records
5
6 TYPE track_member_struct is RECORD (
7 group_asset_id number(15) := NULL,
8 member_asset_id number(15) := NULL,
9 period_counter number(15) := NULL,
10 fiscal_year number(15) := NULL,
11 set_of_books_id number := NULL,
12 allocation_basis number := NULL,
13 total_allocation_basis number := NULL,
14 allocated_deprn_amount number := NULL,
15 allocated_bonus_amount number := NULL,
16 system_deprn_amount number := NULL,
17 system_bonus_amount number := NULL,
18 fully_reserved_flag varchar2(01) := NULL,
19 fully_retired_flag varchar2(01) := NULL,
20 override_flag varchar2(01) := NULL,
21 cost number := NULL,
22 adjusted_cost number := NULL,
23 eofy_adj_cost number := NULL,
24 recoverable_cost number := NULL,
25 salvage_value number := NULL,
26 adjusted_recoverable_cost number := NULL,
27 eofy_reserve number := NULL,
28 deprn_reserve number := NULL,
29 ytd_deprn number := NULL,
30 bonus_deprn_reserve number := NULL,
31 bonus_ytd_deprn number := NULL,
32 eofy_recoverable_cost number := NULL,
33 eop_recoverable_cost number := NULL,
34 eofy_salvage_value number := NULL,
35 eop_salvage_value number := NULL
36 , reserve_adjustment_amount number,
37 unplanned_deprn_amount number := NULL
38 );
39
40 TYPE track_member_eofy is RECORD (
41 group_asset_id number(15) := NULL,
42 member_asset_id number(15) := NULL,
43 fiscal_year number := NULL,
44 cost number := NULL,
45 salvage_value number := NULL,
46 recoverable_cost number := NULL,
47 adjusted_cost number := NULL,
48 eofy_reserve number := NULL,
49 set_of_books_id number := NULL
50 );
51
52 TYPE track_member_type IS TABLE OF track_member_struct INDEX BY BINARY_INTEGER;
53 TYPE track_member_eofy_type IS TABLE OF track_member_eofy INDEX By BINARY_INTEGER;
54
55 null_track_member_struct track_member_struct;
56 p_track_member_table track_member_type;
57 p_track_member_eofy_table track_member_eofy_type;
58 p_track_adj_mode boolean := FALSE;
59
60 p_track_member_table_for_raf track_member_type;
61 p_tracK_member_table_for_deprn track_member_type;
62
63 /* Added for bug 7231274 */
64 type track_index_struct is TABLE of NUMBER index by VARCHAR2(152);
65 p_track_mem_index_table track_index_struct;
66
67 --* Update Depreciable Basis controller
68 l_process_deprn_for_member varchar2(3) := 'YES';
69 l_processing_member_table varchar2(3) := 'NO';
70
71 --------------------------------------------------------------------------
72 --
73 -- Function: track_assets
74 --
75 -- Description:
76 --
77 -- Main logic to track individual asset level amounts.
78 --
79 -- Returns:
80 --
81 -- 0 - No error / 1 - Error
82 --
83 --------------------------------------------------------------------------
84 FUNCTION track_assets(P_book_type_code in varchar2,
85 P_group_asset_id in number,
86 P_period_counter in number,
87 P_fiscal_year in number,
88 P_loop_end_year in number default null,
89 P_loop_end_period in number default null,
90 P_group_deprn_basis in varchar2,
91 P_group_exclude_salvage in varchar2 default NULL,
92 P_group_bonus_rule in varchar2 default NULL,
93 P_group_deprn_amount in number default 0,
94 P_group_bonus_amount in number default 0,
95 P_tracking_method in varchar2 default null,
96 P_allocate_to_fully_ret_flag in varchar2 default null,
97 P_allocate_to_fully_rsv_flag in varchar2 default null,
98 P_excess_allocation_option in varchar2 default 'REDUCE',
99 P_depreciation_option in varchar2 default null,
100 P_member_rollup_flag in varchar2 default null,
101 P_subtraction_flag in varchar2 default NULL,
102 P_group_level_override in out nocopy varchar2,
103 P_update_override_status in boolean default true,
104 P_period_of_addition in varchar2 default NULL,
105 P_transaction_date_entered in date default null,
106 P_mode in varchar2 default NULL,
107 p_mrc_sob_type_code in varchar2 default 'N',
108 p_set_of_books_id in number,
109 X_new_deprn_amount out nocopy number,
110 X_new_bonus_amount out nocopy number, p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
111 return number;
112
113 --------------------------------------------------------------------------
114 --
115 -- Function: allocate
116 --
117 -- Description:
118 --
119 -- Calculate the allocated amount based on the parameters
120 --
121 -- Returns:
122 --
123 -- True - No error / False - error
124 --
125 --------------------------------------------------------------------------
126
127 FUNCTION allocate(P_book_type_code in varchar2,
128 P_group_asset_id in number,
129 P_period_counter in number,
130 P_fiscal_year in number,
131 P_group_deprn_basis in varchar2,
132 P_group_exclude_salvage in varchar2 default NULL,
133 P_group_bonus_rule in varchar2 default NULL,
134 P_group_deprn_amount in number default 0,
135 P_group_bonus_amount in number default 0,
136 P_allocate_to_fully_ret_flag in varchar2 default NULL,
137 P_allocate_to_fully_rsv_flag in varchar2 default NULL,
138 P_excess_allocation_option in varchar2 default 'REDUCE',
139 P_subtraction_flag in varchar2 default NULL,
140 P_group_level_override in out nocopy varchar2,
141 P_update_override_status in boolean default true,
142 P_mrc_sob_type_code in varchar2 default 'N',
143 P_set_of_books_id in number,
144 P_mode in varchar2 default NULL,
145 X_new_deprn_amount out nocopy number,
146 X_new_bonus_amount out nocopy number, p_log_level_rec IN FA_API_TYPES.log_level_rec_type)
147
148 return boolean;
149
150 ---------------------------------------------------------------------------
151 --
152 -- Function: check_group_amounts
153 --
154 -- Description:
155 --
156 -- This function is called when system needs to update
157 -- Group Level Amounts as a result of tracking logic.
158 -- If system cannot update the group level amounts
159 -- due to some reason, this function will return false.
160 --
161 --
162 -- Returns:
163 --
164 -- 0 - No error / 1 - error
165 --
166 ---------------------------------------------------------------------------
167
168 FUNCTION check_group_amounts(P_book_type_code in varchar2,
169 P_group_asset_id in number,
170 P_period_counter in number,
171 P_perd_deprn_exp in number,
172 P_year_deprn_exp in number,
173 P_recoverable_cost in number,
174 P_adj_rec_cost in number,
175 P_current_deprn_reserve in number,
176 P_nbv_threshold in number,
177 P_nbv_thresh_amount in number,
178 P_rec_cost_abs_value in number,
179 X_life_complete_flag out nocopy varchar2,
180 X_fully_reserved_flag out nocopy varchar2, p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
181 return number;
182
183 ---------------------------------------------------------------------------
184 --
185 -- Function: allocation_main
186 --
187 -- Description:
188 --
189 -- This function is called to allocate group level amount
190 -- to member assets. This is the main logic to allocate amounts
191 -- to members.
192 --
193 --
194 -- Returns:
195 --
196 -- True on successful retrieval. Otherwise False.
197 --
198 ----------------------------------------------------------------------------
199
200 FUNCTION allocation_main(P_book_type_code in varchar2,
201 P_group_asset_id in number,
202 P_member_asset_id in number,
203 P_period_counter in number,
204 P_fiscal_year in number,
205 P_group_bonus_rule in varchar2 default null,
206 P_group_deprn_amount in number,
207 P_group_bonus_amount in number default 0,
208 P_allocation_basis in number,
209 P_total_allocation_basis in number,
210 P_ytd_deprn in number,
211 P_bonus_ytd_deprn in number default 0,
212 P_track_member_in in track_member_struct default null_track_member_struct,
213 P_check_reserve_flag in Varchar2 default null,
214 P_subtraction_flag in varchar2 default null,
215 P_group_level_override in out nocopy varchar2,
216 P_update_override_status in boolean default true,
217 P_member_override_flag in varchar2 default null,
218 PX_difference_deprn_amount in out nocopy number,
219 PX_difference_bonus_amount in out nocopy number,
220 X_system_deprn_amount out nocopy number,
221 X_system_bonus_amount out nocopy number,
222 X_track_member_out out nocopy track_member_struct,
223 P_mrc_sob_type_code in varchar2 default 'N',
224 P_set_of_books_id in number,
225 P_mode in Varchar2,
226 P_rec_cost_for_odda in number default null,
227 P_sv_for_odda in number default null, p_log_level_rec IN FA_API_TYPES.log_level_rec_type)
228 return boolean;
229
230 ----------------------------------------------------------------------------
231 --
232 -- Function: update_depreciable_basis
233 --
234 -- Description:
235 --
236 -- This function is called to update Depreciable Basis in some cases.
237 -- The case when this function is called is that the group level depreciable
238 -- basis rule has group level check logic, such as 50% rules for CCA or India.
239 -- In these logic, system needs to check group level net amount for the specified
240 -- period to decide if 50% reduction is applied or not.
241 -- This cannot be checked at member level. So after group level depreciable basis
242 -- updated is done, Deprn Basis Rule function will call this function to update
243 -- member level depreciable basis.
244 -- to members.
245 --
246 --
247 ----------------------------------------------------------------------------
248
249 FUNCTION update_deprn_basis(p_group_rule_in in fa_std_types.fa_deprn_rule_in_struct,
250 p_apply_reduction_flag in varchar2 default NULL,
251 p_mode in varchar2 default NULL, p_log_level_rec IN FA_API_TYPES.log_level_rec_type)
252 return boolean;
253
254 ----------------------------------------------------------------------------
255 --
256 -- Function: ins_dd_adj
257 --
258 -- Description:
259 --
260 -- This function is called to insert allocated amount
261 -- into FA_ADJ or FA_DEPRN_DETAIL/SUMMARY in case
262 -- that Unplanned Depreciation is made.
263 --
264 --
265 -- Returns
266 -- True on successful retrieval. Otherwise False.
267 --
268 ----------------------------------------------------------------------------
269
270 FUNCTION ins_dd_adj(P_book_type_code in varchar2,
271 P_group_asset_id in number,
272 P_period_counter in number,
273 P_fiscal_year in number,
274 P_period_of_addition in varchar2 default NULL,
275 P_transaction_date_entered in date default null,
276 P_mrc_sob_type_code in varchar2 default 'N',
277 P_set_of_books_id in number,
278 P_mode in varchar2, p_log_level_rec IN FA_API_TYPES.log_level_rec_type)
279 return boolean;
280
281 ----------------------------------------------------------------------------
282 --
283 -- Function: populate previous rows
284 --
285 -- Description:
286 --
287 -- This function is called to prepare PL/SQL table to process
288 -- allocation under ADJUSTMENT mode
289 -- If PL/SQL table doesn't exist, this function will extend the
290 -- table with necessary values for member assets to be processed
291 -- at one period before the starting period made subsequently.
292 --
293 --
294 -- Returns
295 -- True on successful retrieval. Otherwise False.
296 --
297 ----------------------------------------------------------------------------
298
299 FUNCTION populate_previous_rows(p_book_type_code in varchar2,
300 p_group_asset_id in number,
301 p_period_counter in number,
302 p_fiscal_year in number,
303 p_transaction_header_id in number default null,
304 p_loop_end_year in number default null,
305 p_loop_end_period in number default null,
306 p_allocate_to_fully_ret_flag in varchar2,
307 p_allocate_to_fully_rsv_flag in varchar2,
308 p_mrc_sob_type_code in varchar2, -- default 'N'
309 p_set_of_books_id in number,
310 p_calling_fn in varchar2 default NULL, p_log_level_rec IN FA_API_TYPES.log_level_rec_type)
311 return boolean;
312
313 --+=====================================================================
314 -- Function: get_member_at_start
315 --
316 -- This function will be called from adjustment engine
317 -- to poulate the member assets at the time of running faxcde
318 -- Using transaction_date_entered passed from engine,
319 -- member assets are defined.
320 -- And populate the necessary info into FA_TRACK_MEMBER table.
321 --+=====================================================================
322
323 FUNCTION get_member_at_start(p_period_rec in FA_API_TYPES.period_rec_type,
324 p_trans_rec in FA_API_TYPES.trans_rec_type,
325 p_asset_hdr_rec in FA_API_TYPES.asset_hdr_rec_type,
326 p_asset_fin_rec in FA_API_TYPES.asset_fin_rec_type,
327 p_dpr_in in FA_STD_TYPES.dpr_struct,
328 p_mrc_sob_type_code in varchar2 default 'N', p_log_level_rec IN FA_API_TYPES.log_level_rec_type)
329 return boolean;
330
331 --+=====================================================================
332 -- Function: update_member_books
333 --
334 -- This function will be called from adjustment engine
335 -- to update FA_BOOKS for each member assets
336 -- Using stored adjusted_cost in FA_TRACK_MEMBERS,
337 -- FA_BOOKS will be updated.
338 --
339 --+=====================================================================
340
341 FUNCTION update_member_books(p_trans_rec in FA_API_TYPES.trans_rec_type,
342 p_asset_hdr_rec in FA_API_TYPES.asset_hdr_rec_type,
343 p_dpr_in in FA_STD_TYPES.dpr_struct,
344 p_mrc_sob_type_code in varchar2 default 'N', p_log_level_rec IN FA_API_TYPES.log_level_rec_type)
345 return boolean;
346
347 --+=====================================================================
348 -- Function: member_eofy_rsv
349 --
350 -- This function will be called to keep eofy_reserve for each member asset
351 -- during the loop of recalculation
352 --
353 --+=====================================================================
354
355 FUNCTION member_eofy_rsv(p_asset_hdr_rec in FA_API_TYPES.asset_hdr_rec_type,
356 p_dpr_in in FA_STD_TYPES.dpr_struct,
357 p_mrc_sob_type_code in varchar2 default 'N', p_log_level_rec IN FA_API_TYPES.log_level_rec_type)
358 return boolean;
359
360 --+=====================================================================
361 -- Function: populate_member_assets_table
362 --
363 -- This function will be called to extend member assets PL/SQL table
364 -- to process faxcde correctly.
365 -- When this function is called, allocation calculation will be
366 -- made from group DPIS to one period before when recalculation will start
367 --
368 --+=====================================================================
369
370 FUNCTION populate_member_assets_table(p_trans_rec in FA_API_TYPES.trans_rec_type default null, --Bug13805724
371 p_asset_hdr_rec in FA_API_TYPES.asset_hdr_rec_type,
372 p_asset_fin_rec_new in FA_API_TYPES.asset_fin_rec_type,
373 p_populate_for_recalc_period in varchar2 default 'N',
374 p_amort_start_date in date,
375 p_recalc_start_fy in number,
376 p_recalc_start_period_num in number,
377 p_no_allocation_for_last in varchar2 default 'Y',
378 p_mrc_sob_type_code in varchar2 default 'N', p_log_level_rec IN FA_API_TYPES.log_level_rec_type)
379 return boolean;
380
381 --+=====================================================================
382 -- Function: populate_member_reserve
383 --
384 -- This function will be called to query tracked reserve amount
385 -- for group reclassification.
386 -- This function is used to skip recalculation from DPIS to reclassification
387 -- date populating member level reserve amount tracked.
388 --
389 --+=====================================================================
390
391 FUNCTION populate_member_reserve(p_trans_rec in FA_API_TYPES.trans_rec_type,
392 p_asset_hdr_rec in FA_API_TYPES.asset_hdr_rec_type,
393 p_asset_fin_rec_new in FA_API_TYPES.asset_fin_rec_type,
394 p_mrc_sob_type_code in varchar2 default 'N',
395 x_deprn_reserve out nocopy number,
396 x_eofy_reserve out nocopy number, p_log_level_rec IN FA_API_TYPES.log_level_rec_type)
397 return boolean;
398
399 --+=====================================================================
400 -- Function: check_reduction_application
401 --
402 -- This function will be called to check if 50% rule is applied at group
403 -- level or not before calling deprn basis rule function for each member
404 --
405 --+=====================================================================
406
407 FUNCTION check_reduction_application(p_rule_name in varchar2,
408 p_group_asset_id in number,
409 p_book_type_code in varchar2,
410 p_period_counter in number,
411 p_group_deprn_basis in varchar2,
412 p_reduction_rate in number,
413 p_group_eofy_rec_cost in number,
414 p_group_eofy_salvage_value in number,
415 p_group_eofy_reserve in number,
416 p_mrc_sob_type_code in varchar2 default 'N',
417 p_set_of_books_id in number,
418 x_apply_reduction_flag out nocopy varchar2, p_log_level_rec IN FA_API_TYPES.log_level_rec_type)
419 return boolean;
420
421 --+=====================================================================
422 -- Function: display_debug_message
423 --
424 -- This function will be called to display debug message
425 --
426 --+=====================================================================
427
428 FUNCTION display_debug_message(fa_rule_in in fa_std_types.fa_deprn_rule_in_struct,
429 p_calling_fn in varchar2, p_log_level_rec IN FA_API_TYPES.log_level_rec_type)
430
431 return boolean;
432
433 --+=====================================================================
434 -- Function: display_debug_message2
435 --
436 -- This function will be called to display debug message
437 -- This is for p_track_member_table
438 --+=====================================================================
439
440 FUNCTION display_debug_message2(i in number,
441 p_calling_fn in varchar2, p_log_level_rec IN FA_API_TYPES.log_level_rec_type)
442
443 return boolean;
444
445 --+=====================================================================
446 -- Function: copy_member_table
447 --
448 -- This function will be called to backup the memory table
449 -- restore backuped memory table
450 --
451 --+=====================================================================
452
453 FUNCTION copy_member_table(p_backup_restore in varchar2,
454 p_current_fiscal_year in number default NULL,
455 p_current_period_num in number default NULL, p_log_level_rec IN FA_API_TYPES.log_level_rec_type)
456
457 return boolean;
458
459 --+=====================================================================
460 -- Function: create_update_books_summary
461 --
462 -- This function will be called to insert row into fa_books_summary if not exists
463 -- update fa_books_summary row if exists
464 --
465 --+=====================================================================
466
467 FUNCTION create_update_bs_table(p_trans_rec in FA_API_TYPES.trans_rec_type,
468 p_book_type_code in varchar2,
469 p_group_asset_id in varchar2,
470 p_mrc_sob_type_code in varchar2 default 'N',
471 p_sob_id in number, --Bug 8941132
472 p_calling_fn in varchar2, p_log_level_rec IN FA_API_TYPES.log_level_rec_type)
473
474 return boolean;
475
476 --+=====================================================================
477 -- Function: override_member_amount
478 --
479 -- This function will be called to override deprn amount of member assets
480 -- This is called only when populate_member_assets_table calls
481 --
482 --+=====================================================================
483
484 FUNCTION override_member_amount(p_book_type_code in varchar2,
485 p_member_asset_id in number,
486 p_fiscal_year in number,
487 p_period_num in number,
488 p_ytd_deprn in number,
489 p_bonus_ytd_deprn in number,
490 x_override_deprn_amount out nocopy number,
491 x_override_bonus_amount out nocopy number,
492 x_deprn_override_flag out nocopy varchar2,
493 p_calling_fn in varchar2,
494 p_mrc_sob_type_code in varchar2,
495 p_recoverable_cost in number default null,
496 p_salvage_value in number default null, p_log_level_rec IN FA_API_TYPES.log_level_rec_type)
497
498 return boolean;
499
500
501 END FA_TRACK_MEMBER_PVT;
[Home] [Help]