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