[Home] [Help]
PACKAGE BODY: APPS.JA_AU_FA_BAL_CHG
Source
1 PACKAGE BODY JA_AU_FA_BAL_CHG AS
2 /* $Header: jaaufasb.pls 115.11 2003/12/17 12:31:03 rbasker ship $ */
3
4 /***********************************************************************
5 PROCEDURE - Schedule 32
6 DESCRIPTION - Processes data for the Schedule 32 report
7 ************************************************************************/
8 PROCEDURE Schedule32
9 (P_Book_type_code VARCHAR2,
10 P_From_Period VARCHAR2,
11 P_To_Period VARCHAR2,
12 P_Category_ID NUMBER ) IS
13 v_book_type_code varchar2(15) := (P_Book_Type_Code);
14 v_from_period varchar2(15) := (P_From_Period);
15 v_to_period varchar2(15) := (P_To_Period);
16 v_category_id integer := to_number(P_Category_id);
17 v_cat_temp integer := 1;
18 v_category varchar2(80);
19 v_from_date fa_deprn_periods.period_open_date%type;
20 v_from_counter fa_deprn_periods.period_counter%type;
21 v_from_cal_date fa_deprn_periods.calendar_period_open_date%type;
22 v_to_date fa_deprn_periods.period_close_date%type;
23 v_to_counter fa_deprn_periods.period_counter%type;
24 v_col1 fnd_id_flex_segments.application_column_name%type;
25 v_col2 fnd_id_flex_segments.application_column_name%type;
26
27 CURSOR C_Get_First_Category IS
28 SELECT B.Category_id
29 FROM FA_Categories_B B,
30 FA_Categories_TL T
31 WHERE B.category_id = T.category_id
32 AND T.language = userenv('LANG')
33 AND ROWNUM = 1;
34
35 CURSOR C_Get_Category (C_Category_ID NUMBER,
36 C_First_Category NUMBER ) IS
37 SELECT DECODE(v_Category_id,null,'ALL-ALL',DECODE(flex1.application_column_name,'SEGMENT7', C.SEGMENT7, 'SEGMENT6', C.SEGMENT6,
38 'SEGMENT5', C.SEGMENT5, 'SEGMENT4', C.SEGMENT4, 'SEGMENT3',
39 C.SEGMENT3, 'SEGMENT2', C.SEGMENT2, C.SEGMENT1) ||
40 decode(nvl(flex2.application_column_name, ' '), ' ', ' ', '-') ||
41 DECODE(nvl(flex2.application_column_name, ' '), 'SEGMENT7', C.SEGMENT7, 'SEGMENT6', C.SEGMENT6,
42 'SEGMENT5', C.SEGMENT5, 'SEGMENT4', C.SEGMENT4, 'SEGMENT3',
43 C.SEGMENT3, 'SEGMENT2', C.SEGMENT2, 'SEGMENT1', C.SEGMENT1,
44 ' '))
45 FROM fa_categories C,
46 fnd_id_flexs flexid,
47 fnd_id_flex_segments flex1,
48 fnd_id_flex_segments flex2
49 WHERE C.Category_id = nvl(C_category_id,C_first_category)
50 and flexid.application_id = 140
51 and flexid.id_flex_code = 'CAT#'
52 and flex1.id_flex_code = flexid.id_flex_code
53 and flex1.application_id = 140
54 and flex1.id_flex_code = 'CAT#'
55 and flex1.id_flex_num = 101
56 and flex1.application_column_name = 'SEGMENT1'
57 and flex1.enabled_flag = 'Y'
58 and flex2.id_flex_code (+) = flexid.id_flex_code
59 and flex2.application_id(+) = 140
60 and flex2.id_flex_code(+) = 'CAT#'
61 and flex2.id_flex_num(+) = 101
62 and flex2.application_column_name(+) = 'SEGMENT2'
63 and flex2.enabled_flag (+) = 'Y';
64
65 CURSOR C_Parameters (C_Book_type_code VARCHAR2,
66 C_From_Period VARCHAR2,
67 C_To_Period VARCHAR2 ) is
68 select dp1.period_open_date,
69 dp1.period_counter,
70 dp1.calendar_period_open_date,
71 nvl(dp2.period_close_date, sysdate),
72 dp2.period_counter,
73 flex1.application_column_name,
74 nvl(flex2.application_column_name, ' ')
75 from fa_deprn_periods dp1,
76 fa_deprn_periods dp2,
77 fnd_id_flexs flexid,
78 fnd_id_flex_segments flex1,
79 fnd_id_flex_segments flex2
80 where dp1.book_type_code = C_book_type_code
81 and dp1.period_name = C_from_period
82 and dp2.book_type_code = dp1.book_type_code
83 and dp2.period_name = C_to_period
84 and flexid.application_id = 140
85 and flexid.id_flex_code = 'CAT#'
86 and flex1.id_flex_code = flexid.id_flex_code
87 and flex1.application_id = 140
88 and flex1.id_flex_code = 'CAT#'
89 and flex1.id_flex_num = 101
90 and flex1.application_column_name = 'SEGMENT1'
91 and flex1.enabled_flag = 'Y'
92 and flex2.id_flex_code (+) = flexid.id_flex_code
93 and flex2.application_id(+) = 140
94 and flex2.id_flex_code(+) = 'CAT#'
95 and flex2.id_flex_num(+) = 101
96 and flex2.application_column_name(+) = 'SEGMENT2'
97 and flex2.enabled_flag (+) = 'Y';
98
99
100 begin
101 -- Clean up the temporary Table
102 delete from ja_au_srw_tax_deprn_tmp ;
103
104 -- DBMS_OUTPUT.PUT_LINE ('Starting');
105
106 -- Fetch the first category ID
107 OPEN C_Get_First_Category;
108 FETCH C_Get_First_Category INTO V_Cat_Temp;
109 CLOSE C_Get_First_Category;
110
111 -- Get the Parameters
112 OPEN C_Parameters ( C_Book_Type_Code => V_Book_Type_code,
113 C_From_Period => V_From_Period,
114 C_To_Period => V_To_Period );
115 FETCH C_Parameters INTO v_from_date,
116 v_from_counter,
117 v_from_cal_date,
118 v_to_date,
119 v_to_counter,
120 v_col1,
121 v_col2;
122 CLOSE C_Parameters;
123
124 -- DBMS_OUTPUT.PUT_LINE ('Found Parameters');
125
126 -- Now get the category details
127 OPEN C_Get_Category (V_Category_ID,V_cat_temp);
128 FETCH C_Get_Category INTO V_Category;
129 CLOSE C_Get_Category;
130
131
132
133 /* Select candidate records between selected periods */
134
135 insert into ja_au_srw_tax_deprn_tmp
136 ( asset_id,
137 asset_number,
138 asset_desc,
139 category_id,
140 category_number,
141 category_desc,
142 original_cost_start,
143 cost_start,
144 original_cost_end,
145 cost_end,
146 in_service,
147 deprn_rate,
148 deprn_basis_rule,
149 Created_by,
150 Creation_date,
151 last_update_date,
152 last_update_login,
153 last_updated_by
154 )
155 select a.asset_id,
156 a.asset_number,
157 a.description,
158 c.category_id,
159 decode(v_col1,'SEGMENT7', c.segment7, 'SEGMENT6', c.segment6,
160 'SEGMENT5', c.segment5, 'SEGMENT4', c.segment4, 'SEGMENT3',
161 c.segment3, 'SEGMENT2', c.segment2, c.segment1) ||
162 decode(v_col2,' ','','-') ||
163 decode(v_col2,'SEGMENT7', c.segment7, 'SEGMENT6', c.segment6,
164 'SEGMENT5', c.segment5, 'SEGMENT4', c.segment4, 'SEGMENT3',
165 c.segment3, 'SEGMENT2', c.segment2, 'SEGMENT1', c.segment1, ''),
166 c.description,
167 b1.original_cost,
168 b1.cost,
169 b2.original_cost,
170 b2.cost,
171 b2.date_placed_in_service,
172 decode(nvl(b2.adjusted_rate,0),
173 0, decode(b2.life_in_months, 0, 0, null, 0,
174 1/(b2.life_in_months/12)),
175 b2.adjusted_rate) * 100,
176 m.deprn_basis_rule,
177 uid,
178 sysdate,
179 sysdate ,
180 uid,
181 uid
182 from fa_books b1,
183 fa_books b2,
184 fa_additions a,
185 fa_categories c,
186 fa_methods m
187 where b1.asset_id = a.asset_id
188 and b1.book_type_code = v_book_type_code
189 and b1.date_effective =
190 (select min(bk.date_effective)
191 from fa_books bk
192 where bk.asset_id = a.asset_id
193 and bk.book_type_code = v_book_type_code
194 and nvl(bk.date_ineffective,sysdate+2) > v_from_date
195 and bk.date_effective <= v_to_date)
196 and nvl(b1.date_ineffective,sysdate+2) > v_from_date
197 and b2.asset_id = a.asset_id
198 and b2.book_type_code = v_book_type_code
199 and b2.date_effective <= v_to_date
200 and nvl(b2.date_ineffective, sysdate+2) > v_to_date
201 and m.method_code(+) = b2.deprn_method_code
202 and nvl(m.life_in_months(+),1) = nvl(b2.life_in_months,1)
203 and c.category_id = a.asset_category_id
204 and decode(v_col1, 'SEGMENT7', c.segment7, 'SEGMENT6', c.segment6,
205 'SEGMENT5', c.segment5, 'SEGMENT4', c.segment4,
206 'SEGMENT3', c.segment3, 'SEGMENT2', c.segment2, c.segment1) LIKE
207 decode(substr(v_category,1,(INSTR(v_category,'-')-1)),
208 'ALL', '%',
209 '', decode(v_category, '', '%', 'ALL', '%', v_category),
210 substr(v_category,1,(instr(v_category,'-')-1)))
211 and (v_col2 = ' ' or
212 decode(v_col2, 'SEGMENT7', c.segment7, 'SEGMENT6', c.segment6,
213 'SEGMENT5', c.segment5, 'SEGMENT4', c.segment4,
214 'SEGMENT3', c.segment3, 'SEGMENT2', c.segment2, c.segment1) LIKE
215 decode(nvl(INSTR(v_category, '-'),'0'), 0, '%',
216 decode(substr(v_category,(nvl(instr(v_category,'-'),0)+1)),
217 'ALL','%', '', '%',
218 substr(v_category,(nvl(instr(v_category,'-'),0)+1)))));
219
220 -- DBMS_OUTPUT.PUT_LINE ('Found '||to_char(SQL%ROWCOUNT)||' Candidate Records');
221
222 /* Select the maximum date retired prior to the end of the chosen interval */
223
224 update ja_au_srw_tax_deprn_tmp t
225 set date_retired =
226 (select max(r.date_retired)
227 from fa_transaction_headers th,
228 fa_retirements r
229 where th.asset_id = t.asset_id
230 and th.book_type_code = v_book_type_code
231 and th.date_effective <= v_to_date
232 and th.transaction_type_code = 'FULL RETIREMENT'
233 and not exists (select '1'
234 from fa_transaction_headers th2
235 where th2.asset_id = t.asset_id
236 and th2.book_type_code = v_book_type_code
237 and th2.date_effective <= v_to_date
238 and th2.transaction_header_id > th.transaction_header_id
239 and th2.transaction_type_code = 'REINSTATEMENT')
240 and r.transaction_header_id_in = th.transaction_header_id);
241
242 /* Delete records where the maximum date retired is less than the start of the
243 interval */
244
245 delete from ja_au_srw_tax_deprn_tmp t
246 where t.date_retired < v_from_cal_date;
247
248 /* Select the assets that were retired in the interval and calculate the net
249 book value */
250
251 update ja_au_srw_tax_deprn_tmp t
252 set (date_retired, net_book_value) =
253 (select max(r.date_retired), sum(nvl(r.nbv_retired,0))
254 from fa_transaction_headers th,
255 fa_retirements r
256 where th.asset_id = t.asset_id
257 and th.book_type_code = v_book_type_code
258 and th.date_effective between v_from_date and v_to_date
259 and th.transaction_type_code in ('PARTIAL RETIREMENT','FULL RETIREMENT')
260 and not exists (select '1'
261 from fa_transaction_headers th2
262 where th2.asset_id = t.asset_id
263 and th2.book_type_code = v_book_type_code
264 and th2.date_effective between v_from_date and v_to_date
265 and th2.transaction_header_id > th.transaction_header_id
266 and th2.transaction_type_code = 'REINSTATEMENT')
267 and r.transaction_header_id_in = th.transaction_header_id);
268
269 /* Select the depreciation reserve at the start of the interval */
270
271 update ja_au_srw_tax_deprn_tmp t
272 set deprn_rsrve_start =
273 (select deprn_reserve
274 from fa_deprn_summary ds
275 where ds.asset_id = t.asset_id
276 and ds.period_counter =
277 (select max(dp2.period_counter)
278 from fa_deprn_summary ds2,
279 fa_deprn_periods dp2
280 where ds2.asset_id = t.asset_id
281 and ds2.period_counter = dp2.period_counter
282 and ds2.book_type_code = v_book_type_code
283 and dp2.book_type_code = v_book_type_code
284 and dp2.period_counter < v_from_counter)
285 and ds.book_type_code = v_book_type_code);
286
287 /* Select the depreciation reserve at the end of the interval */
288
289 update ja_au_srw_tax_deprn_tmp t
290 set deprn_rsrve_end =
291 (select deprn_reserve
292 from fa_deprn_summary ds
293 where ds.asset_id = t.asset_id
294 and ds.period_counter =
295 (select max(dp2.period_counter)
296 from fa_deprn_summary ds2,
297 fa_deprn_periods dp2
298 where ds2.asset_id = t.asset_id
299 and ds2.period_counter = dp2.period_counter
300 and ds2.book_type_code = v_book_type_code
301 and dp2.book_type_code = v_book_type_code
302 and dp2.period_counter between v_from_counter and
303 v_to_counter)
304 and ds.book_type_code = v_book_type_code);
305
306 /* Calculate the depreciation over the interval */
307
308 update ja_au_srw_tax_deprn_tmp t
309 set deprn_amount =
310 (select sum(ds.deprn_amount)
311 from fa_deprn_summary ds,
312 fa_deprn_periods dp
313 where ds.asset_id = t.asset_id
314 and ds.period_counter = dp.period_counter
315 and ds.book_type_code = v_book_type_code
316 and dp.book_type_code = v_book_type_code
317 and dp.period_counter between v_from_counter and v_to_counter);
318
319 -- Select the assets that were entered in the system
320 -- between the given periods
321
322 update ja_au_srw_tax_deprn_tmp t
323 set addition_date =
324 (select th.date_effective
325 from fa_transaction_headers th
326 where th.asset_id = t.asset_id
327 and th.book_type_code = v_book_type_code
328 and th.transaction_type_code = 'ADDITION'
329 and th.date_effective between v_from_date and v_to_date);
330
331 -- Select the balancing charges applied to each asset
332 update ja_au_srw_tax_deprn_tmp t
333 set bal_chg_applied =
334 (select sum(nvl(ap.bal_chg_applied,0))
335 from ja_au_bal_chg_applied ap
336 where ap.asset_id = t.asset_id
337 and ap.book_type_code = v_book_type_code);
338
339 -- DBMS_OUTPUT.PUT_LINE ('JAAUFS32.sql Completed...');
340 end;
341
342 /***********************************************************************
343 PROCEDURE - JAAUFRET
344 DESCRIPTION - Stub Procedure to call the original retirements program
345 followed by the Calculcate Balancing charges program
346 PARAMETERS - List of Parameters
347 NAME IN/OUT RANGE OF VALUES
348 Book IN Book Type Code
349 Period IN Financial Period
350 CALLED - List of Calling Code
351 HISTORY - Created - 10 July 1997 SGoggin
352 Created Initial Release of Code
353 Modified -
354 ************************************************************************/
355 PROCEDURE JAAUFRET
356 (ERRBUF OUT NOCOPY VARCHAR2,
357 RETCODE OUT NOCOPY VARCHAR2,
358 BOOK VARCHAR2,
359 PERIOD VARCHAR2) IS
360 V_Req_id1 number;
361 V_Req_id2 number;
362 Dummy_Default Boolean Default FALSE;
363 phase varchar2(255);
364 status varchar2(255);
365 dev_phase varchar2(255);
366 dev_status varchar2(255);
367 message varchar2(255);
368
369 l_balancing_charge_flag VARCHAR2(1);
370
371 BEGIN
372
373 -- JA_AU_FA_BAL_CHG_FLAG profile controls execution of this program.
374 FND_PROFILE.GET('JA_AU_FA_BAL_CHG_FLAG',l_balancing_charge_flag);
375
376 l_balancing_charge_flag := nvl(l_balancing_charge_flag,'N'); -- bug603639
377
378 -- Bug 602113 must always submit FARET process
379 V_Req_id1 := FND_REQUEST.SUBMIT_REQUEST(
380 application => 'OFA',
381 program =>'JAAUFRET',
382 description => fnd_message.get_string('JA','JA_AU_FRET_CAL_GAN_LSS'),
383 argument1 => BOOK,
384 argument2 => PERIOD);
385
386 commit;
387 IF l_balancing_charge_flag ='Y' and
388 FND_CONCURRENT.WAIT_FOR_REQUEST ( V_Req_id1,10,0,phase,status,dev_phase,dev_status,message ) THEN
389 V_Req_id2 := FND_REQUEST.SUBMIT_REQUEST(
390 application => 'JA',
391 program =>'JAAUFCB',
392 description => fnd_message.get_string('JA','JA_AU_FRET_CAL_BAL_CHG'),
393 argument1 => BOOK,
394 argument2 => PERIOD);
395 END IF;
396
397 errbuf := 'Submitted processes ' ||to_char(V_Req_id1)||' and '||to_char( V_Req_id2);
398 END;
399
400
401
402 /***********************************************************************
403 PROCEDURE - Name
404 DESCRIPTION - Scans for asset retirements in the specified period and
405 Book, then creates a Balancing charge Record
406 PARAMETERS - List of Parameters
407 NAME IN/OUT RANGE OF VALUES
408 CALLED - List of Calling Code
409 FORM
410 REPORT
411 HISTORY - Created - 10 July 1997 SGoggin
412 Created Initial Release of Code
413 Modified -
414 ************************************************************************/
415 PROCEDURE Calc_Bal_Chg
416 (ERRBUF OUT NOCOPY VARCHAR2,
417 RETCODE OUT NOCOPY VARCHAR2,
418 BOOK VARCHAR2,
419 PERIOD VARCHAR2) IS
420
421 v_book_type_code varchar2(15) := (BOOK);
422 v_period_name varchar2(15) := (PERIOD);
423 v_recoupment number := 0;
424 v_bal_chg_applied number := 0;
425 v_bal_chg_id number(15);
426 v_message varchar2(80);
427 v_reinstatements number := 0;
428 v_retirements number := 0;
429 v_retirement_tot number := 0;
430
431 V_Log_Out number := 1;
432 V_out_out number := 2;
433 V_Bal_Charge_Enabled varchar2(10) := 'N';
434
435 -- Declare a cursor to extract retirement numbers which have
436 -- had retirements or reinstatements.
437
438 CURSOR C_RETIREMENTS is
439 Select distinct
440 r.retirement_id ,
441 r.asset_id ,
442 r.status ,
443 r.date_retired ,
444 nvl(r.gain_loss_amount,0) gain_loss_amount,
445 nvl(r.cost_retired,0) - nvl(r.nbv_retired,0) deprn_retired
446 from FA_DEPRN_PERIODS DP,
447 FA_TRANSACTION_HEADERS TH,
448 FA_RETIREMENTS R,
449 FA_ADDITIONS A,
450 FA_BOOKS B
451 where dp.period_name = (v_period_name)
452
453 and dp.book_type_code = v_book_type_code
454 and th.book_type_code = v_book_type_code
455 and th.transaction_date_entered >=dp.calendar_period_open_date
456 and th.transaction_date_entered <=nvl(dp.calendar_period_close_date,th.date_effective)
457 and th.transaction_type_code in ('PARTIAL RETIREMENT',
458 'FULL RETIREMENT',
459 'REINSTATEMENT')
460 and th.transaction_header_id =
461 decode(th.transaction_type_code,
462 'PARTIAL RETIREMENT', r.transaction_header_id_in,
463 'FULL RETIREMENT', r.transaction_header_id_in,
464 /* REINSTATEMENT */ r.transaction_header_id_out)
465 and r.asset_id = th.asset_id
466 and r.book_type_code = v_book_type_code
467 and a.asset_id = r.asset_id
468 and nvl(a.property_type_code,'xxxxxxxxx') <> 'DIV 10D'
469 /* Exclude Div 10D buildings */
470 and b.asset_id = r.asset_id
471 and b.date_ineffective is null
472 and nvl(b.depreciate_flag,'zzz') = 'YES';
473 /* Only depreciable assets */
474
475 CURSOR C_Bal_Chg_Retirements (C_Retirement_ID NUMBER )IS
476 select nvl(s.bal_chg_applied,0)
477 from ja_au_bal_chg_source s
478 where s.retirement_id = C_retirement_id;
479
480 CURSOR C_Bal_Chg_Enabled (C_Book_Type_code VARCHAR2) IS
481 SELECT BAL_CHARGE_ENABLED
482 FROM JA_AU_FA_BOOK_CONTROLS
483 WHERE Book_Type_code = C_Book_Type_code;
484
485 BEGIN
486
487 -- Check if this is a Balancing Charge Book
488 OPEN C_Bal_Chg_Enabled (V_Book_Type_code);
489 FETCH C_Bal_Chg_Enabled into V_Bal_Charge_Enabled;
490 IF C_Bal_Chg_Enabled%NOTFOUND or
491 V_Bal_Charge_Enabled = 'NO' THEN
492 close C_Bal_Chg_Enabled;
493 errbuf := 'Balancing charge is disabled for '||V_Book_type_code;
494 ELSE
495
496 close C_Bal_Chg_Enabled;
497 -- Send a message to the log file for this concurrent process;
498 FND_FILE.Put_Line (V_Log_Out,'Calculate Balancing Charges Starting...');
499 FND_FILE.Put_Line (V_Log_Out,'...BOOK is '||V_Book_Type_code||' Period is '||V_Period_name);
500
501 -- Loop for all retirements in the current period
502 FOR C_Retirements_REC IN C_Retirements LOOP
503 -- Send a status message to the log file.
504 FND_FILE.Put_Line (V_Log_Out,'Processing Retirement for Asset '||to_char(C_Retirements_REC.Asset_id));
505
506 -- Reset Data
507 V_Recoupment := 0;
508 V_Bal_Chg_Applied := 0;
509
510 -- The retirement should have a status of PROCESSED or
511 -- DELETED. If Deleted, it's a reinstatement, so there
512 -- will be no new recoupment of depreciation.
513 -- But a Processed retirement will have a recoupment
514 -- equal to the proportion of the Gain/Loss which is
515 -- depreciation retired and subsequently recouped.
516
517 if C_Retirements_REC.status <> 'DELETED'
518 AND C_Retirements_REC.Gain_loss_amount > 0 then
519
520 -- Recoupment amount is limited by the Deprn retired amount
521 if C_Retirements_REC.gain_loss_amount >= C_Retirements_REC.deprn_retired then
522 v_recoupment := C_Retirements_REC.deprn_retired;
523 else
524 v_recoupment := C_Retirements_REC.gain_loss_amount;
525 end if;
526 end if;
527 FND_FILE.Put_Line (V_Log_Out,'...Recoupment is '||to_char(v_recoupment));
528
529 -- Check if a Source Record for this retired asset already exists.
530 -- If it does this means that this must be a reinstatement.
531 -- The Source record needs to be deleted or updated.
532 OPEN C_Bal_Chg_Retirements (C_Retirements_REC.Retirement_ID);
533 FETCH C_Bal_Chg_Retirements INTO V_Bal_Chg_Applied;
534
535 IF C_Bal_Chg_Retirements%NOTFOUND AND
536 v_recoupment > 0 THEN
537
538 -- There is no pre existing balancing charge source record therefore create one
539 FND_FILE.Put_Line (V_Log_Out,'...Created New Balancing Charge Source of '||to_Char(v_recoupment));
540
541 select ja_au_bal_chg_source_s.nextval
542 into v_bal_chg_id
543 from sys.dual;
544
545
546 insert into JA_AU_BAL_CHG_SOURCE
547 ( bal_chg_id,
548 book_type_code,
549 asset_id,
550 retirement_id,
551 last_update_date,
552 last_updated_by,
553 created_by,
554 creation_date,
555 last_update_login,
556 bal_chg_amount,
557 bal_chg_applied,
558 date_retired,
559 bal_chg_status)
560 values
561 ( v_bal_chg_id,
562 v_book_type_code,
563 C_Retirements_REC.asset_id,
564 C_Retirements_REC.retirement_id,
565 sysdate,
566 uid,
567 uid,
568 sysdate,
569 uid,
570 v_recoupment,
571 0,
572 C_Retirements_REC.date_retired,
573 'N'); /* Not applied balance */
574
575 v_retirements := v_retirements + 1;
576 v_retirement_tot := v_retirement_tot +
577 v_recoupment;
578
579
580 ELSIF C_Bal_Chg_Retirements%FOUND THEN
581 -- There is already a source record
582 IF v_recoupment = 0 and
583 v_bal_chg_applied = 0 then
584 -- No balancing charge applications so delete it
585 FND_FILE.Put_Line (V_Log_Out,'...Deleted Balancing Charge Source ');
586
587 -- Remove the Records
588 DELETE FROM ja_au_bal_chg_source s
589 WHERE s.retirement_id = C_Retirements_REC.retirement_id;
590
591 -- Update the couter
592 v_reinstatements := v_reinstatements + 1;
593
594 ELSE
595 FND_FILE.Put_Line (V_Log_Out,'...Updated Balancing Charge Source to '||to_Char(v_recoupment));
596 -- The amount applied is > 0
597 -- Update the Source record for this reinstatement.
598 -- This should only ever be a full reversal of the
599 -- balance charge for a reinstatement. If it's not a
600 -- reinstatement then this program must have been run
601 -- twice, so the Source record is simply updated to
602 -- the same value.
603
604 update ja_au_bal_chg_source s
605 set bal_chg_amount = v_recoupment,
606 bal_chg_status =
607 decode(sign(v_recoupment - v_bal_chg_applied),
608 +1,
609 decode(v_bal_chg_applied, 0,
610 'N', /* Not applied balance charge */
611 'P'), /* Partially applied BC */
612 0, 'F', /* Fully applied */
613 -1, 'R'), /* Reversed balance charge */
614 last_update_date = sysdate,
615 last_updated_by = uid,
616 last_update_login = uid
617 where s.retirement_id = C_Retirements_REC.retirement_id;
618
619
620 if v_recoupment = 0 then
621 v_reinstatements := v_reinstatements + 1;
622 end if;
623 END IF;
624
625 END IF;
626 Close C_Bal_Chg_Retirements;
627
628
629 END LOOP;
630
631 FND_FILE.Put_Line (V_Log_Out,'Processing Complete.');
632
633 FND_FILE.Put_Line (V_out_Out,'Calculate Balancing Charges.');
634 FND_FILE.Put_Line (V_out_Out,null);
635 FND_FILE.Put_Line (V_out_Out,null);
636 FND_FILE.Put_Line (V_out_Out,null);
637
638 /* Commented By Sierra - 03/03/99 for Rel 11.5 Multi Radix Issue fixes */
639
640 /*
641 FND_FILE.Put_Line (V_out_Out,'Retirements: '||to_char(v_retirements, '9,999,990') ||
642 ' Balancing Charges generated. ' ||to_char(v_retirement_tot, '$9,999,999,990') ||
643 ' value.');
644 */
645
646 FND_FILE.Put_Line (V_out_Out,'Retirements: '||to_char(v_retirements, '9G999G990') ||
647 ' Balancing Charges generated. ' ||to_char(v_retirement_tot, '$9G999G999G990') ||
648 ' value.');
649 FND_FILE.Put_Line (V_out_Out,null);
650
651 /* Commented By Sierra - 03/03/99 for Rel 11.5 Multi Radix Issue fixes */
652
653 /*
654 FND_FILE.Put_Line (V_out_Out,'Reinstatements: '||to_char(v_reinstatements, '9,999,990') ||
655 ' Balancing Charges reversed.');
656 errbuf := 'Retirements: '||to_char(v_retirements, '9,999,990') ||
657 ' Balancing Charges generated. ' ||to_char(v_retirement_tot, '$9,999,999,990') ||
658 ' value.';
659 */
660
661 FND_FILE.Put_Line (V_out_Out,'Reinstatements: '||to_char(v_reinstatements, '9G999G990') ||
662 ' Balancing Charges reversed.');
663 errbuf := 'Retirements: '||to_char(v_retirements, '9G999G990') ||
664 ' Balancing Charges generated. ' ||to_char(v_retirement_tot, '$9G999G999G990') ||
665 ' value.';
666
667 END IF;
668 EXCEPTION when others then
669 FND_FILE.Put_Line (V_out_Out,'Error ' || to_char(sqlcode, '999999')||' '|| sqlerrm);
670
671 END;
672
673 END;
674