[Home] [Help]
PACKAGE BODY: APPS.PN_RETRO_ADJUSTMENT_PKG
Source
1 PACKAGE BODY pn_retro_adjustment_pkg AS
2 /* $Header: PNRTADJB.pls 120.5 2006/04/05 22:21:33 appldev noship $ */
3
4 ------------------------------ DECLARATIONS ----------------------------------+
5
6 TYPE item_id_tbl_type IS TABLE OF pn_payment_items.payment_item_id%TYPE INDEX BY BINARY_INTEGER;
7 TYPE sched_id_tbl_type IS TABLE OF pn_payment_schedules.payment_schedule_id%TYPE INDEX BY BINARY_INTEGER;
8 TYPE amt_tbl_type IS TABLE OF pn_payment_items.actual_amount%TYPE INDEX BY BINARY_INTEGER;
9 TYPE date_tbl_type IS TABLE OF pn_payment_items.due_date%TYPE INDEX BY BINARY_INTEGER;
10
11 bad_input_exception EXCEPTION;
12
13 ------------------------------------------------------------------------------+
14 -- PROCEDURE : create_virtual_schedules
15 -- DESCRIPTION : given a start date, end date, schedule day, amount, freq
16 -- create virtual items with corresponding dates.
17 -- HISTORY :
18 -- 24-SEP-04 ftanudja o Created.
19 -- 10-AUG-05 piagrawa o Bug 4354810 - Added code to retrive the proper schedule
20 -- start date
21 -- 05-APR-06 piagrawa o Bug 4354810 - Added handling for terms with start date
22 -- equal to end date
23 ------------------------------------------------------------------------------+
24
25 PROCEDURE create_virtual_schedules(
26 p_start_date pn_payment_terms.start_date%TYPE,
27 p_end_date pn_payment_terms.end_date%TYPE,
28 p_sch_day pn_payment_terms.schedule_day%TYPE,
29 p_amount pn_payment_terms.actual_amount%TYPE,
30 p_term_freq pn_payment_terms.frequency_code%TYPE,
31 x_sched_tbl OUT NOCOPY payment_item_tbl_type
32 )
33 IS
34 l_current_end_date pn_payment_terms.end_date%TYPE;
35 l_current_start_date pn_payment_terms.start_date%TYPE;
36 l_current_amount pn_payment_terms.actual_amount%TYPE;
37 l_dummy_amount pn_payment_terms.actual_amount%TYPE;
38
39 l_count NUMBER;
40 l_freq_num NUMBER;
41
42 l_info VARCHAR2(100);
43 l_desc VARCHAR2(100) := 'pn_retro_adjustment_pkg.create_virtual_schedules';
44
45 BEGIN
46
47 pnp_debug_pkg.log(l_desc ||' (+)');
48
49 l_info := ' validating input ';
50 pnp_debug_pkg.log(l_info);
51
52 IF p_start_date IS NULL OR
53 p_end_date IS NULL OR
54 p_sch_day IS NULL OR
55 p_amount IS NULL OR
56 p_term_freq IS NULL OR
57 p_start_date > p_end_date OR
58 p_sch_day < 1 OR
59 p_sch_day > 28
60 THEN
61 raise bad_input_exception;
62 END IF;
63
64 l_info := ' initializing variables ';
65 pnp_debug_pkg.log(l_info);
66
67 l_current_start_date := p_start_date;
68 l_current_end_date := p_start_date;
69
70 -- special case for one time payments and terms with start date
71 -- equal to end date
72 IF p_term_freq = 'OT' OR (p_start_date = p_end_Date) THEN
73 l_current_end_date := p_start_date - 1;
74 END IF;
75
76 l_info := ' creating items ';
77
78 l_freq_num := pn_schedules_items.get_frequency(p_term_freq);
79
80 WHILE l_current_end_date < p_end_date LOOP
81
82 IF p_term_freq = 'MON' THEN
83 l_current_end_date := last_day(l_current_start_date);
84 ELSIF p_term_freq = 'OT' THEN
85 l_current_end_date := l_current_start_date;
86 ELSE
87 l_current_end_date := add_months(l_current_start_date, l_freq_num) - 1;
88 END IF;
89
90 l_info := ' getting amount for schedule start: '|| l_current_start_date;
91 pnp_debug_pkg.log(l_info);
92
93 IF p_term_freq = 'OT' THEN
94
95 l_current_amount := p_amount;
96
97 ELSE
98
99 IF p_term_freq IN ('MON') THEN
100
101 l_current_start_date := pn_schedules_items.First_Day(l_current_start_date);
102
103 END IF;
104
105 pn_schedules_items.get_amount(
106 p_sch_str_dt => l_current_start_date,
107 p_sch_end_dt => l_current_end_date,
108 p_trm_str_dt => p_start_date,
109 p_trm_end_dt => p_end_date,
110 p_act_amt => p_amount,
111 p_est_amt => null,
112 p_freq => l_freq_num,
113 p_cash_act_amt => l_current_amount,
114 p_cash_est_amt => l_dummy_amount);
115
116 END IF;
117
118 -- make sure end date does not exceed the term end date
119 -- NOTE: this has to be done AFTER calling pn_schedules_items.get_amount() !!
120 l_current_end_date := LEAST(l_current_end_date, p_end_date);
121
122 l_count := x_sched_tbl.COUNT;
123 x_sched_tbl(l_count).start_date := l_current_start_date;
124 x_sched_tbl(l_count).end_date := l_current_end_date;
125 x_sched_tbl(l_count).schedule_date := last_day(add_months(l_current_start_date, -1)) + p_sch_day;
126 x_sched_tbl(l_count).amount := l_current_amount;
127
128 l_current_start_date := l_current_end_date + 1;
129
130 -- for one time payments, set logic to terminate loop
131 IF p_term_freq = 'OT' THEN l_current_end_date := p_end_date + 1; END IF;
132
133 END LOOP;
134
135 pnp_debug_pkg.log(l_desc ||' (-)');
136
137 EXCEPTION
138 WHEN OTHERS THEN
139 pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
140 raise;
141
142 END create_virtual_schedules;
143
144 ------------------------------------------------------------------------------+
145 -- PROCEDURE : get_current_schedules
146 -- DESCRIPTION : given a payment term id, fetch all original and adjustment
147 -- items associated with it.
148 -- NOTES :
149 -- The program works as follows:
150 -- 1) Fetch all original items into PL/SQL table
151 -- 2) Fetch all adjustment items into PL/SQL table
152 -- 3) Start comparing the two tables with the following rules
153 -- a) If no adjustment exists, return the original items table. No merging
154 -- is required here.
155 -- b) If any of the two tables has 'run out', exit loop and just parse
156 -- the rest of the other table into the output table.
157 --
158 -- HISTORY :
159 -- 27-SEP-04 ftanudja o Created.
160 -- 15-JUL-05 SatyaDeep o Replaced base views with their _ALL tables
161 ------------------------------------------------------------------------------+
162 PROCEDURE get_current_schedules(
163 p_term_id pn_payment_terms.payment_term_id%TYPE,
164 x_sched_tbl OUT NOCOPY payment_item_tbl_type
165 )
166 IS
167 -- NOTE: important that the cursor is ordered by date
168 CURSOR fetch_original_items IS
169 SELECT item.payment_item_id,
170 item.actual_amount,
171 item.payment_schedule_id,
172 schedule.schedule_date,
173 schedule.payment_status_lookup_code
174 FROM pn_payment_items_all item,
175 pn_payment_schedules_all schedule
176 WHERE item.payment_term_id = p_term_id
177 AND item.payment_schedule_id = schedule.payment_schedule_id
178 AND item.payment_item_type_lookup_code = 'CASH'
179 AND item.last_adjustment_type_code IS NULL
180 ORDER BY schedule.schedule_date;
181
182 -- NOTE: important that the cursor is ordered by date
183 CURSOR fetch_adj_items IS
184 SELECT summary.adjustment_summary_id,
185 summary.adj_schedule_date,
186 summary.sum_adj_amount
187 FROM pn_adjustment_summaries summary
188 WHERE summary.payment_term_id = p_term_id
189 ORDER BY summary.adj_schedule_date;
190
191 l_orig_item_tbl payment_item_tbl_type;
192 l_adj_item_tbl payment_item_tbl_type;
193
194 l_count_adj NUMBER;
195 l_count_orig NUMBER;
196 l_count_summ NUMBER;
197 l_info VARCHAR2(300);
198 l_desc VARCHAR2(100) := 'pn_retro_adjustment_pkg.get_current_schedules';
199
200 BEGIN
201
202 pnp_debug_pkg.log(l_desc ||' (+)');
203
204 l_info := ' fetching original items data ';
205 pnp_debug_pkg.log(l_info);
206
207 l_count_orig := l_orig_item_tbl.COUNT;
208
209 FOR orig_data_rec IN fetch_original_items LOOP
210 l_orig_item_tbl(l_count_orig).item_id := orig_data_rec.payment_item_id;
211 l_orig_item_tbl(l_count_orig).amount := orig_data_rec.actual_amount;
212 l_orig_item_tbl(l_count_orig).schedule_id := orig_data_rec.payment_schedule_id;
213 l_orig_item_tbl(l_count_orig).schedule_date := orig_data_rec.schedule_date;
214 l_orig_item_tbl(l_count_orig).payment_status := orig_data_rec.payment_status_lookup_code;
215 l_count_orig := l_count_orig + 1;
216 END LOOP;
217
218 l_info := ' fetching adjustment items data ';
219 pnp_debug_pkg.log(l_info);
220
221 l_count_adj := l_adj_item_tbl.COUNT;
222
223 FOR adj_data_rec IN fetch_adj_items LOOP
224 l_adj_item_tbl(l_count_adj).adj_summ_id := adj_data_rec.adjustment_summary_id;
225 l_adj_item_tbl(l_count_adj).amount := adj_data_rec.sum_adj_amount;
226 l_adj_item_tbl(l_count_adj).schedule_date := adj_data_rec.adj_schedule_date;
227 l_count_adj := l_count_adj + 1;
228 END LOOP;
229
230 l_info := ' merging the two tables ';
231 pnp_debug_pkg.log(l_info);
232
233 IF l_adj_item_tbl.COUNT = 0 THEN
234
235 -- if no adjustments, then return l_orig_item_tbl
236 x_sched_tbl := l_orig_item_tbl;
237
238 ELSE
239 l_count_orig := null;
240 l_count_adj := 0;
241 l_count_summ := 0;
242
243 FOR i IN 0 .. l_orig_item_tbl.COUNT - 1 LOOP
244
245 IF l_orig_item_tbl(i).schedule_date = l_adj_item_tbl(l_count_adj).schedule_date THEN
246
247 l_info := ' (orig = adj): inserting the current item into result table '||
248 ' orig_item date: '||l_orig_item_tbl(i).schedule_date ||
249 ' adj_item date: '||l_adj_item_tbl(l_count_adj).schedule_date;
250
251 pnp_debug_pkg.log(l_info);
252
253 x_sched_tbl(l_count_summ).item_id := l_orig_item_tbl(i).item_id;
254 x_sched_tbl(l_count_summ).schedule_id := l_orig_item_tbl(i).schedule_id;
255 x_sched_tbl(l_count_summ).schedule_date := l_orig_item_tbl(i).schedule_date;
256 x_sched_tbl(l_count_summ).payment_status := l_orig_item_tbl(i).payment_status;
257 x_sched_tbl(l_count_summ).adj_summ_id := l_adj_item_tbl(l_count_adj).adj_summ_id;
258 x_sched_tbl(l_count_summ).amount := l_orig_item_tbl(i).amount +
259 l_adj_item_tbl(l_count_adj).amount;
260
261 l_count_summ := l_count_summ + 1;
262 l_count_adj := l_count_adj + 1;
263
264 IF l_count_adj = l_adj_item_tbl.COUNT THEN
265 l_count_orig := i + 1;
266 exit;
267 END IF;
268
269 ELSIF l_orig_item_tbl(i).schedule_date < l_adj_item_tbl(l_count_adj).schedule_date THEN
270
271 l_info := ' (orig < adj): inserting the current item into result table '||
272 ' orig_item date: '||l_orig_item_tbl(i).schedule_date ||
273 ' adj_item date: '||l_adj_item_tbl(l_count_adj).schedule_date;
274
275 pnp_debug_pkg.log(l_info);
276
277 x_sched_tbl(l_count_summ).item_id := l_orig_item_tbl(i).item_id;
278 x_sched_tbl(l_count_summ).schedule_id := l_orig_item_tbl(i).schedule_id;
279 x_sched_tbl(l_count_summ).schedule_date := l_orig_item_tbl(i).schedule_date;
280 x_sched_tbl(l_count_summ).payment_status := l_orig_item_tbl(i).payment_status;
281 x_sched_tbl(l_count_summ).amount := l_orig_item_tbl(i).amount;
282 l_count_summ := l_count_summ + 1;
283
284 ELSE
285
286 l_info := ' (orig > adj): looping through other table until a greater date is found ';
287
288 pnp_debug_pkg.log(l_info);
289
290 WHILE (l_count_adj <= l_adj_item_tbl.COUNT - 1) AND
291 (l_orig_item_tbl(i).schedule_date >
292 l_adj_item_tbl(l_count_adj).schedule_date)
293 LOOP
294
295 l_info := ' inserting the current item into result table '||
296 ' orig_item date: '||l_orig_item_tbl(i).schedule_date ||
297 ' adj_item date: '||l_adj_item_tbl(l_count_adj).schedule_date;
298
299 pnp_debug_pkg.log(l_info);
300
301 IF (l_count_summ <> 0 AND
302 x_sched_tbl(l_count_summ - 1).schedule_date <>
303 l_adj_item_tbl(l_count_adj).schedule_date)
304 OR l_count_summ = 0 THEN
305 x_sched_tbl(l_count_summ).adj_summ_id := l_adj_item_tbl(l_count_adj).adj_summ_id;
306 x_sched_tbl(l_count_summ).schedule_date := l_adj_item_tbl(l_count_adj).schedule_date;
307 x_sched_tbl(l_count_summ).amount := l_adj_item_tbl(l_count_adj).amount;
308 l_count_summ := l_count_summ + 1;
309 END IF;
310
311 l_count_adj := l_count_adj + 1;
312
313 END LOOP;
314
315 l_info := ' finished finding lesser adj dates, now inserting current orig '||
316 ' item into result table orig_item date: '|| l_orig_item_tbl(i).schedule_date;
317
318 pnp_debug_pkg.log(l_info);
319
320 x_sched_tbl(l_count_summ).item_id := l_orig_item_tbl(i).item_id;
321 x_sched_tbl(l_count_summ).schedule_id := l_orig_item_tbl(i).schedule_id;
322 x_sched_tbl(l_count_summ).schedule_date := l_orig_item_tbl(i).schedule_date;
323 x_sched_tbl(l_count_summ).payment_status := l_orig_item_tbl(i).payment_status;
324 x_sched_tbl(l_count_summ).amount := l_orig_item_tbl(i).amount;
325
326 IF (l_count_adj <= l_adj_item_tbl.COUNT - 1) AND
327 (l_orig_item_tbl(i).schedule_date = l_adj_item_tbl(l_count_adj).schedule_date)
328 THEN
329 x_sched_tbl(l_count_summ).adj_summ_id := l_adj_item_tbl(l_count_adj).adj_summ_id;
330 x_sched_tbl(l_count_summ).amount := x_sched_tbl(l_count_summ).amount +
331 l_adj_item_tbl(l_count_adj).amount;
332 l_count_adj := l_count_adj + 1;
333 END IF;
334
335 l_count_summ := l_count_summ + 1;
336
337 IF l_count_adj = l_adj_item_tbl.COUNT THEN
338 l_count_orig := i + 1;
339 exit;
340 END IF;
341
342 END IF;
343
344 END LOOP;
345
346 l_info := ' merging the leftover items from l_orig_item_tbl ';
347 pnp_debug_pkg.log(l_info);
348
349 IF l_count_orig IS NOT NULL THEN
350 FOR i IN l_count_orig .. l_orig_item_tbl.COUNT - 1 LOOP
351 x_sched_tbl(l_count_summ).item_id := l_orig_item_tbl(i).item_id;
352 x_sched_tbl(l_count_summ).schedule_id := l_orig_item_tbl(i).schedule_id;
353 x_sched_tbl(l_count_summ).schedule_date := l_orig_item_tbl(i).schedule_date;
354 x_sched_tbl(l_count_summ).amount := l_orig_item_tbl(i).amount;
355 x_sched_tbl(l_count_summ).payment_status := l_orig_item_tbl(i).payment_status;
356 l_count_summ := l_count_summ + 1;
357 END LOOP;
358 END IF;
359
360 l_info := ' merging the leftover items from l_adj_item_tbl ';
361 pnp_debug_pkg.log(l_info);
362
363 FOR i IN l_count_adj .. l_adj_item_tbl.COUNT - 1 LOOP
364 IF (l_count_summ <> 0 AND
365 x_sched_tbl(l_count_summ - 1).schedule_date <>
366 l_adj_item_tbl(i).schedule_date)
367 OR
368 l_count_summ = 0 THEN
369 x_sched_tbl(l_count_summ).adj_summ_id := l_adj_item_tbl(i).adj_summ_id;
370 x_sched_tbl(l_count_summ).schedule_date := l_adj_item_tbl(i).schedule_date;
371 x_sched_tbl(l_count_summ).amount := l_adj_item_tbl(i).amount;
372 l_count_summ := l_count_summ + 1;
373 END IF;
374 END LOOP;
375 END IF;
376
377 pnp_debug_pkg.log(l_desc ||' (-)');
378
379 EXCEPTION
380 WHEN OTHERS THEN
381 pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
382 raise;
383
384 END get_current_schedules;
385
386 ------------------------------------------------------------------------------+
387 -- PROCEDURE : merge_schedules
388 -- DESCRIPTION : given two schedules, current and virtual, merge the two
389 -- together
390 -- NOTE :
391 -- This uses 99% of the logic of get_current_schedules() to merge the tables
392 -- using dates. Only the updated fields are sometimes different. Here the
393 -- value of virtual items is put into the 'new_amount' column.
394 --
395 -- HISTORY :
396 -- 28-SEP-04 ftanudja o Created.
397 ------------------------------------------------------------------------------+
398 PROCEDURE merge_schedules(
399 p_current_sched payment_item_tbl_type,
400 p_virtual_sched payment_item_tbl_type,
401 x_sched_tbl OUT NOCOPY payment_item_tbl_type
402 )
403 IS
404 l_count_virtl NUMBER;
405 l_count_curnt NUMBER;
406 l_count_merge NUMBER;
407
408 l_info VARCHAR2(200);
409 l_desc VARCHAR2(100) := 'pn_retro_adjustment_pkg.merge_schedules';
410
411 BEGIN
412
413 pnp_debug_pkg.log(l_desc ||' (+)');
414
415 l_info := ' initializing counters ';
416 pnp_debug_pkg.log(l_info);
417
418 l_count_curnt := null;
419 l_count_virtl := 0;
420 l_count_merge := 0;
421
422 l_info := ' merging the two tables ';
423 pnp_debug_pkg.log(l_info);
424
425 FOR i IN 0 .. p_current_sched.COUNT - 1 LOOP
426
427 IF p_current_sched(i).schedule_date = p_virtual_sched(l_count_virtl).schedule_date THEN
428
429 l_info := ' (curnt = virtl): inserting item into result table '||
430 ' curnt_item date: '||p_current_sched(i).schedule_date ||
431 ' virtl_item date: '||p_virtual_sched(l_count_virtl).schedule_date;
432
433 pnp_debug_pkg.log(l_info);
434
435 x_sched_tbl(l_count_merge).item_id := p_current_sched(i).item_id;
436 x_sched_tbl(l_count_merge).adj_summ_id := p_current_sched(i).adj_summ_id;
437 x_sched_tbl(l_count_merge).schedule_id := p_current_sched(i).schedule_id;
438 x_sched_tbl(l_count_merge).schedule_date := p_current_sched(i).schedule_date;
439 x_sched_tbl(l_count_merge).payment_status := p_current_sched(i).payment_status;
440 x_sched_tbl(l_count_merge).amount := p_current_sched(i).amount;
441
442 x_sched_tbl(l_count_merge).new_amount := p_virtual_sched(l_count_virtl).amount;
443 x_sched_tbl(l_count_merge).start_date := p_virtual_sched(l_count_virtl).start_date;
444 x_sched_tbl(l_count_merge).end_date := p_virtual_sched(l_count_virtl).end_date;
445
446 l_count_merge := l_count_merge + 1;
447 l_count_virtl := l_count_virtl + 1;
448
449 IF l_count_virtl = p_virtual_sched.COUNT THEN
450 l_count_curnt := i + 1;
451 exit;
452 END IF;
453
454 ELSIF p_current_sched(i).schedule_date < p_virtual_sched(l_count_virtl).schedule_date THEN
455
456 l_info := ' (curnt < virtl): inserting item into result table '||
457 ' curnt_item date: '||p_current_sched(i).schedule_date ||
458 ' virtl_item date: '||p_virtual_sched(l_count_virtl).schedule_date;
459
460 pnp_debug_pkg.log(l_info);
461
462 x_sched_tbl(l_count_merge).item_id := p_current_sched(i).item_id;
463 x_sched_tbl(l_count_merge).adj_summ_id := p_current_sched(i).adj_summ_id;
464 x_sched_tbl(l_count_merge).schedule_id := p_current_sched(i).schedule_id;
465 x_sched_tbl(l_count_merge).schedule_date := p_current_sched(i).schedule_date;
466 x_sched_tbl(l_count_merge).payment_status := p_current_sched(i).payment_status;
467 x_sched_tbl(l_count_merge).amount := p_current_sched(i).amount;
468 l_count_merge := l_count_merge + 1;
469
470 ELSE
471
472 l_info := ' (curnt > virtl): looping through other table until a greater date is found ';
473
474 pnp_debug_pkg.log(l_info);
475
476 WHILE (l_count_virtl <= p_virtual_sched.COUNT - 1) AND
477 (p_current_sched(i).schedule_date >
478 p_virtual_sched(l_count_virtl).schedule_date)
479 LOOP
480
481 l_info := ' inserting into result table '||
482 ' curnt_item date: '||p_current_sched(i).schedule_date ||
483 ' virtl_item date: '||p_virtual_sched(l_count_virtl).schedule_date;
484
485 pnp_debug_pkg.log(l_info);
486
487 IF (l_count_merge <> 0 AND
488 x_sched_tbl(l_count_merge - 1).schedule_date <>
489 p_virtual_sched(l_count_virtl).schedule_date)
490 OR l_count_merge = 0 THEN
491 x_sched_tbl(l_count_merge).schedule_date := p_virtual_sched(l_count_virtl).schedule_date;
492 x_sched_tbl(l_count_merge).new_amount := p_virtual_sched(l_count_virtl).amount;
493 x_sched_tbl(l_count_merge).start_date := p_virtual_sched(l_count_virtl).start_date;
494 x_sched_tbl(l_count_merge).end_date := p_virtual_sched(l_count_virtl).end_date;
495
496 l_count_merge := l_count_merge + 1;
497 END IF;
498
499 l_count_virtl := l_count_virtl + 1;
500
501 END LOOP;
502
503 l_info := ' finished finding lesser adj dates, now inserting current '||
504 ' item into result table curnt_item date:'||p_current_sched(i).schedule_date;
505
506 pnp_debug_pkg.log(l_info);
507
508 x_sched_tbl(l_count_merge).item_id := p_current_sched(i).item_id;
509 x_sched_tbl(l_count_merge).adj_summ_id := p_current_sched(i).adj_summ_id;
510 x_sched_tbl(l_count_merge).schedule_id := p_current_sched(i).schedule_id;
511 x_sched_tbl(l_count_merge).schedule_date := p_current_sched(i).schedule_date;
512 x_sched_tbl(l_count_merge).payment_status := p_current_sched(i).payment_status;
513 x_sched_tbl(l_count_merge).amount := p_current_sched(i).amount;
514
515 IF (l_count_virtl <= p_virtual_sched.COUNT - 1) AND
516 (p_current_sched(i).schedule_date = p_virtual_sched(l_count_virtl).schedule_date)
517 THEN
518 x_sched_tbl(l_count_merge).new_amount := p_virtual_sched(l_count_virtl).amount;
519 x_sched_tbl(l_count_merge).start_date := p_virtual_sched(l_count_virtl).start_date;
520 x_sched_tbl(l_count_merge).end_date := p_virtual_sched(l_count_virtl).end_date;
521 l_count_virtl := l_count_virtl + 1;
522
523 END IF;
524
525 l_count_merge := l_count_merge + 1;
526
527 IF l_count_virtl = p_virtual_sched.COUNT THEN
528 l_count_curnt := i + 1;
529 exit;
530 END IF;
531
532 END IF;
533
534 END LOOP;
535
536 l_info := ' merging the leftover items from p_current_sched ';
537 pnp_debug_pkg.log(l_info);
538
539 IF l_count_curnt IS NOT NULL THEN
540 FOR i IN l_count_curnt .. p_current_sched.COUNT - 1 LOOP
541 x_sched_tbl(l_count_merge).item_id := p_current_sched(i).item_id;
542 x_sched_tbl(l_count_merge).adj_summ_id := p_current_sched(i).adj_summ_id;
543 x_sched_tbl(l_count_merge).schedule_id := p_current_sched(i).schedule_id;
544 x_sched_tbl(l_count_merge).schedule_date := p_current_sched(i).schedule_date;
545 x_sched_tbl(l_count_merge).amount := p_current_sched(i).amount;
546 x_sched_tbl(l_count_merge).payment_status := p_current_sched(i).payment_status;
547 l_count_merge := l_count_merge + 1;
548 END LOOP;
549 END IF;
550
551 l_info := ' merging the leftover items from p_virtual_sched ';
552 pnp_debug_pkg.log(l_info);
553
554 FOR i IN l_count_virtl .. p_virtual_sched.COUNT - 1 LOOP
555 IF (l_count_merge <> 0 AND
556 x_sched_tbl(l_count_merge - 1).schedule_date <>
557 p_virtual_sched(i).schedule_date)
558 OR
559 l_count_merge = 0 THEN
560
561 x_sched_tbl(l_count_merge).schedule_date := p_virtual_sched(i).schedule_date;
562 x_sched_tbl(l_count_merge).new_amount := p_virtual_sched(i).amount;
563 x_sched_tbl(l_count_merge).start_date := p_virtual_sched(i).start_date;
564 x_sched_tbl(l_count_merge).end_date := p_virtual_sched(i).end_date;
565
566 l_count_merge := l_count_merge + 1;
567 END IF;
568 END LOOP;
569
570 pnp_debug_pkg.log(l_desc ||' (-)');
571
572 EXCEPTION
573 WHEN OTHERS THEN
574 pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
575 raise;
576
577 END merge_schedules;
578
579 ------------------------------------------------------------------------------+
580 -- PROCEDURE : find_start_end_dates
581 -- DESCRIPTION : In case the start and end dates are not found from the pl/sql
582 -- table, determine using term information.
583 -- NOTE : This is usually called when a new adjustment is needed to
584 -- cancel out an approved item that is outside of the date range
585 -- due to retro adjustment.
586 -- HISTORY :
587 -- 08-OCT-04 ftanudja o Created.
588 -- 14-JAN-05 atuppad o removed least of p_term_end_dt for x_end_date
589 ------------------------------------------------------------------------------+
590 PROCEDURE find_start_end_dates(
591 p_term_freq pn_payment_terms.frequency_code%TYPE,
592 p_term_start_dt pn_payment_terms.start_date%TYPE,
593 p_term_end_dt pn_payment_terms.end_date%TYPE,
594 p_schedule_dt pn_payment_schedules.schedule_date%TYPE,
595 x_start_date OUT NOCOPY pn_payment_items.adj_start_date%TYPE,
596 x_end_date OUT NOCOPY pn_payment_items.adj_end_date%TYPE
597 )
598 IS
599 l_freq_num NUMBER;
600 l_start_day NUMBER;
601 l_info VARCHAR2(100);
602 l_desc VARCHAR2(100) := 'pn_retro_adjustment_pkg.find_start_end_dates';
603
604 BEGIN
605
606 pnp_debug_pkg.log(l_desc ||' (+)');
607
608 IF p_term_freq = 'MON' THEN
609
610 x_end_date := last_day(p_schedule_dt);
611 x_start_date := add_months(x_end_date, -1) + 1;
612 x_end_date := least(x_end_date, p_term_end_dt);
613
614 ELSIF p_term_freq = 'QTR' THEN
615
616 l_freq_num := pn_schedules_items.get_frequency(p_term_freq);
617 l_start_day := TO_NUMBER(TO_CHAR(p_term_start_dt,'DD'));
618
619 x_start_date := last_day(add_months(p_schedule_dt,- 1)) + l_start_day;
620 x_end_date := add_months(x_start_date, l_freq_num) - 1;
621
622 END IF;
623
624 pnp_debug_pkg.log(l_desc ||' (-)');
625
626 EXCEPTION
627 WHEN OTHERS THEN
628 pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
629 raise;
630
631 END find_start_end_dates;
632
633 ------------------------------------------------------------------------------+
634 -- PROCEDURE : create_adjustment_tables
635 -- DESCRIPTION : Given a table of schedules and items, determine what action
636 -- needs to be done. Group items into tables, depending on what
637 -- action needs to be done. All changes to original items go
638 -- to xxx_orig_table; all changes to adjustment items go to
639 -- xxx_adj_table.
640 --
641 -- Program logic as follows
642 --
643 -- IF virtual item exists, and current item doesn't exist THEN
644 -- IF schedule date > last_appr_sch_dt THEN
645 -- create new original item
646 -- ELSE
647 -- create new adjustment item
648 -- END IF
649 -- ELSIF virtual item doesn't exist, and current item exists THEN
650 -- IF payment status = 'DRAFT' THEN
651 -- delete original item
652 --- ELSE
653 -- update adjustment item
654 -- END IF
655 -- ELSIF virtual item exists, and current item exists, and they're not = THEN
656 -- IF payment status = 'DRAFT' THEN
657 -- update original item
658 -- ELSE
659 -- update adjustment item
660 -- END IF
661 -- END IF
662 --
663 -- HISTORY :
664 -- 29-SEP-04 ftanudja o Created.
665 -- 14-JAN-04 atuppad o for the records in x_adj_table, made sure that they
666 -- start_date and end_date.
667 ------------------------------------------------------------------------------+
668 PROCEDURE create_adjustment_tables (
669 p_sched_table payment_item_tbl_type,
670 p_last_appr_dt DATE,
671 p_term_freq pn_payment_terms.frequency_code%TYPE,
672 p_term_start_dt pn_payment_terms.start_date%TYPE,
673 p_term_end_dt pn_payment_terms.end_date%TYPE,
674 x_new_orig_table OUT NOCOPY payment_item_tbl_type,
675 x_upd_orig_table OUT NOCOPY payment_item_tbl_type,
676 x_del_orig_table OUT NOCOPY payment_item_tbl_type,
677 x_adj_table OUT NOCOPY payment_item_tbl_type
678 )
679 IS
680 l_count_new_orig NUMBER := 0;
681 l_count_upd_orig NUMBER := 0;
682 l_count_del_orig NUMBER := 0;
683 l_count_chg_adj NUMBER := 0;
684 l_last_appr_dt DATE;
685
686 l_start_date pn_adjustment_details.adj_start_date%TYPE;
687 l_end_date pn_adjustment_details.adj_end_date%TYPE;
688
689 l_info VARCHAR2(100);
690 l_desc VARCHAR2(100) := 'pn_retro_adjustment_pkg.create_adjustment_tables';
691
692 BEGIN
693
694 pnp_debug_pkg.log(l_desc ||' (+)');
695
696 l_info := ' looping through the schedule table ';
697 pnp_debug_pkg.log(l_info);
698
699 l_last_appr_dt := nvl(p_last_appr_dt, TO_DATE('01/01/0001','DD/MM/YYYY'));
700
701 FOR i IN 0 .. p_sched_table.COUNT - 1 LOOP
702
703 IF p_sched_table(i).new_amount IS NOT NULL AND
704 p_sched_table(i).amount IS NULL AND
705 p_sched_table(i).new_amount <> 0
706 THEN
707
708 IF p_sched_table(i).schedule_date > l_last_appr_dt THEN
709 x_new_orig_table(l_count_new_orig).amount := p_sched_table(i).new_amount;
710 x_new_orig_table(l_count_new_orig).schedule_date := p_sched_table(i).schedule_date;
711 x_new_orig_table(l_count_new_orig).trx_date := p_sched_table(i).schedule_date;
712 l_count_new_orig := l_count_new_orig + 1;
713
714 ELSE
715 x_adj_table(l_count_chg_adj).amount := p_sched_table(i).new_amount;
716 x_adj_table(l_count_chg_adj).schedule_date := p_sched_table(i).schedule_date;
717 x_adj_table(l_count_chg_adj).start_date := p_sched_table(i).start_date;
718 x_adj_table(l_count_chg_adj).end_date := p_sched_table(i).end_date;
719 l_count_chg_adj := l_count_chg_adj + 1;
720
721 END IF;
722
723 ELSIF p_sched_table(i).new_amount IS NULL AND
724 p_sched_table(i).amount IS NOT NULL
725 THEN
726
727 IF p_sched_table(i).payment_status = 'DRAFT' THEN
728
729 x_del_orig_table(l_count_del_orig).item_id := p_sched_table(i).item_id;
730 x_del_orig_table(l_count_del_orig).schedule_id := p_sched_table(i).schedule_id;
731 x_del_orig_table(l_count_del_orig).schedule_date := p_sched_table(i).schedule_date;
732 l_count_del_orig := l_count_del_orig + 1;
733
734 ELSIF p_sched_table(i).amount <> 0 THEN
735
736 x_adj_table(l_count_chg_adj).amount := - p_sched_table(i).amount;
737 x_adj_table(l_count_chg_adj).schedule_date := p_sched_table(i).schedule_date;
738 x_adj_table(l_count_chg_adj).start_date := p_sched_table(i).start_date;
739 x_adj_table(l_count_chg_adj).end_date := p_sched_table(i).end_date;
740 x_adj_table(l_count_chg_adj).adj_summ_id := p_sched_table(i).adj_summ_id;
741 l_count_chg_adj := l_count_chg_adj + 1;
742
743 END IF;
744
745 ELSIF p_sched_table(i).new_amount IS NOT NULL AND
746 p_sched_table(i).amount IS NOT NULL AND
747 p_sched_table(i).amount <> p_sched_table(i).new_amount
748 THEN
749
750 IF p_sched_table(i).payment_status = 'DRAFT' AND p_sched_table(i).new_amount <> 0 THEN
751
752 x_upd_orig_table(l_count_upd_orig).item_id := p_sched_table(i).item_id;
753 x_upd_orig_table(l_count_upd_orig).schedule_date := p_sched_table(i).schedule_date;
754 x_upd_orig_table(l_count_upd_orig).amount := p_sched_table(i).new_amount;
755
756 l_count_upd_orig := l_count_upd_orig + 1;
757
758 -- this case is almost never going to happen
759 ELSIF p_sched_table(i).payment_status = 'DRAFT' AND p_sched_table(i).new_amount = 0 THEN
760
761 x_del_orig_table(l_count_del_orig).item_id := p_sched_table(i).item_id;
762 x_del_orig_table(l_count_del_orig).schedule_id := p_sched_table(i).schedule_id;
763 x_del_orig_table(l_count_del_orig).schedule_date := p_sched_table(i).schedule_date;
764 l_count_del_orig := l_count_del_orig + 1;
765
766 ELSE
767
768 x_adj_table(l_count_chg_adj).amount := p_sched_table(i).new_amount -
769 p_sched_table(i).amount;
770 x_adj_table(l_count_chg_adj).schedule_date := p_sched_table(i).schedule_date;
771 x_adj_table(l_count_chg_adj).start_date := p_sched_table(i).start_date;
772 x_adj_table(l_count_chg_adj).end_date := p_sched_table(i).end_date;
773 x_adj_table(l_count_chg_adj).adj_summ_id := p_sched_table(i).adj_summ_id;
774 l_count_chg_adj := l_count_chg_adj + 1;
775
776 END IF;
777
778 END IF;
779
780 END LOOP;
781
782 /* AMTNEW CHANGES - START */
783 FOR i IN 0 .. x_adj_table.COUNT - 1 LOOP
784
785 IF x_adj_table(i).start_date IS NULL OR x_adj_table(i).end_date IS NULL THEN
786
787 l_info := ' now figuring out start and end dates for schedule date:'||
788 x_adj_table(i).schedule_date;
789 pnp_debug_pkg.log(l_info);
790
791 find_start_end_dates(
792 p_term_freq => p_term_freq,
793 p_term_start_dt => p_term_start_dt,
794 p_term_end_dt => p_term_end_dt,
795 p_schedule_dt => x_adj_table(i).schedule_date,
796 x_start_date => l_start_date,
797 x_end_date => l_end_date
798 );
799 x_adj_table(i).start_date := l_start_date;
800 x_adj_table(i).end_date := l_end_date;
801
802 END IF;
803
804 END LOOP;
805 /* AMTNEW CHANGES - END */
806
807 pnp_debug_pkg.log(l_desc ||' (-)');
808
809 EXCEPTION
810 WHEN OTHERS THEN
811 pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
812 raise;
813
814 END create_adjustment_tables;
815
816 ------------------------------------------------------------------------------+
817 -- PROCEDURE : calculate_adjustment_details
818 -- DESCRIPTION : given a table of adjustments, find all payment items that are
819 -- impacted and separate them into 3 tables: one for updation,
820 -- one for deletion, and one for creation.
821 -- HISTORY :
822 -- 29-SEP-04 ftanudja o Created.
823 -- 15-JUL-05 SatyaDeep o Replaced base views with their _ALL tables
824 ------------------------------------------------------------------------------+
825
826 PROCEDURE calculate_adjustment_details(
827 p_adj_table IN OUT NOCOPY payment_item_tbl_type,
828 p_new_itm_table OUT NOCOPY payment_item_tbl_type,
829 p_upd_itm_table OUT NOCOPY payment_item_tbl_type,
830 p_del_itm_table OUT NOCOPY payment_item_tbl_type
831 )
832 IS
833
834 CURSOR get_item_details (p_adj_summ_id pn_adjustment_summaries.adjustment_summary_id%TYPE)IS
835 SELECT item.payment_item_id,
836 item.actual_amount amount,
837 schedule.payment_schedule_id,
838 schedule.schedule_date
839 FROM pn_payment_items_all item,
840 pn_payment_schedules_all schedule
841 WHERE schedule.payment_schedule_id = item.payment_schedule_id
842 AND schedule.payment_status_lookup_code = 'DRAFT'
843 AND item.payment_item_id IN
844 (SELECT payment_item_id
845 FROM pn_adjustment_details
846 WHERE adjustment_summary_id = p_adj_summ_id);
847
848 l_items_table payment_item_tbl_type;
849
850 -- for table counters
851 l_count_new_itm NUMBER;
852 l_count_upd_itm NUMBER;
853 l_count_del_itm NUMBER;
854 l_count_items NUMBER;
855
856 l_sch_date DATE;
857 l_temp_amt NUMBER;
858 l_exist_draft_adj BOOLEAN;
859
860 l_info VARCHAR2(100);
861 l_desc VARCHAR2(100) := 'pn_retro_adjustment_pkg.calculate_adjustment_details';
862
863 BEGIN
864
865 pnp_debug_pkg.log(l_desc ||' (+)');
866
867 l_info := ' initializing counters ';
868 pnp_debug_pkg.log(l_info);
869
870 l_count_new_itm := 0;
871 l_count_upd_itm := 0;
872 l_count_del_itm := 0;
873 l_count_items := 0;
874
875 FOR i IN 0 .. p_adj_table.COUNT - 1 LOOP
876
877 l_items_table.delete;
878 l_exist_draft_adj := FALSE;
879
880 IF p_adj_table(i).adj_summ_id IS NOT NULL THEN
881
882 l_info := ' fetching items for adj summary id: '||p_adj_table(i).adj_summ_id;
883 pnp_debug_pkg.log(l_info);
884
885 FOR items_rec IN get_item_details (p_adj_table(i).adj_summ_id) LOOP
886
887 l_exist_draft_adj := TRUE;
888 l_temp_amt := items_rec.amount + p_adj_table(i).amount;
889
890 -- if new amount is zero, delete, else update
891 IF l_temp_amt = 0 THEN
892
893 p_del_itm_table(l_count_del_itm).item_id := items_rec.payment_item_id;
894 p_del_itm_table(l_count_del_itm).schedule_date := items_rec.schedule_date;
895 p_del_itm_table(l_count_del_itm).schedule_id := items_rec.payment_schedule_id;
896 l_count_del_itm := l_count_del_itm + 1;
897
898 ELSIF l_temp_amt <> 0 THEN
899
900 p_adj_table(i).item_id := items_rec.payment_item_id;
901 p_upd_itm_table(l_count_upd_itm).item_id := items_rec.payment_item_id;
902 p_upd_itm_table(l_count_upd_itm).amount := l_temp_amt;
903 l_count_upd_itm := l_count_upd_itm + 1;
904
905 END IF;
906
907 -- there should be only one draft item, or even if there are multiple,
908 -- only one should be changed
909 exit;
910
911 END LOOP;
912
913 END IF;
914
915 -- if nothing found, create new adjustment
916
917 IF NOT l_exist_draft_adj THEN
918 p_new_itm_table(l_count_new_itm).amount := p_adj_table(i).amount;
919 p_new_itm_table(l_count_new_itm).schedule_date := p_adj_table(i).schedule_date;
920 p_new_itm_table(l_count_new_itm).start_date := p_adj_table(i).start_date;
921 p_new_itm_table(l_count_new_itm).end_date := p_adj_table(i).end_date;
922 l_count_new_itm := l_count_new_itm + 1;
923 END IF;
924
925 END LOOP;
926
927 pnp_debug_pkg.log(l_desc ||' (-)');
928
929 EXCEPTION
930 WHEN OTHERS THEN
931 pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
932 raise;
933
934 END calculate_adjustment_details;
935
936
937 ------------------------------------------------------------------------------+
938 -- PROCEDURE : prepare_new_items_from_adj
939 -- DESCRIPTION : Given a table of adjustments data, creates a table of items
940 -- that needs to be created based on various system options
941 -- values.
942 -- HISTORY :
943 -- 05-OCT-04 ftanudja o Created.
944 -- 10-AUG-05 piagrawa o Bug#4284035 - Modified the signature to pass org id
945 ------------------------------------------------------------------------------+
946 PROCEDURE prepare_new_items_from_adj (
947 p_sch_day pn_payment_terms.schedule_day%TYPE,
948 p_item_adj_tbl IN OUT NOCOPY payment_item_tbl_type,
949 p_org_id NUMBER
950 )
951 IS
952 l_item_dtl_tbl payment_item_tbl_type;
953
954 -- for system option values
955 l_consolidate BOOLEAN;
956 l_use_crnt_month BOOLEAN;
957 l_trx_sysdate BOOLEAN;
958
959 l_sch_dt pn_payment_schedules.schedule_date%TYPE;
960 l_trx_dt pn_payment_items.due_date%TYPE;
961 l_total_amt pn_payment_items.actual_amount%TYPE;
962
963 l_info VARCHAR2(100);
964 l_desc VARCHAR2(100) := 'pn_retro_adjustment_pkg.prepare_new_items_from_adj';
965
966 BEGIN
967
968 pnp_debug_pkg.log(l_desc ||' (+)');
969
970 l_info := ' fetching system option values ';
971 pnp_debug_pkg.log(l_info);
972
973 IF pn_mo_cache_utils.get_profile_value('PN_CONSOLIDATE_ADJ_ITEMS', p_org_id) = 'Y' THEN
974 l_consolidate := TRUE;
975 ELSE
976 l_consolidate := FALSE;
977 END IF;
978
979 IF pn_mo_cache_utils.get_profile_value('PN_USE_SYSDATE_FOR_ADJ', p_org_id) = 'Y' THEN
980 l_use_crnt_month := TRUE;
981 ELSE
982 l_use_crnt_month := FALSE;
983 END IF;
984
985 IF pn_mo_cache_utils.get_profile_value('PN_USE_SYSDATE_AS_TRX_DATE', p_org_id) = 'Y' THEN
986 l_trx_sysdate := TRUE;
987 ELSE
988 l_trx_sysdate := FALSE;
989 END IF;
990
991 l_total_amt := 0;
992
993 FOR i IN 0 .. p_item_adj_tbl.COUNT - 1 LOOP
994
995 IF l_consolidate AND l_use_crnt_month THEN
996 l_total_amt := l_total_amt + p_item_adj_tbl(i).amount;
997
998 ELSIF NOT l_consolidate THEN
999
1000 IF l_use_crnt_month THEN
1001 l_sch_dt := last_day(add_months(TRUNC(SYSDATE), -1)) + p_sch_day;
1002
1003 ELSE
1004 l_sch_dt := p_item_adj_tbl(i).schedule_date;
1005 END IF;
1006
1007 IF l_trx_sysdate THEN
1008 l_trx_dt := TRUNC(SYSDATE);
1009 ELSE
1010 l_trx_dt := l_sch_dt;
1011 END IF;
1012
1013 l_item_dtl_tbl(i).schedule_date := l_sch_dt;
1014 l_item_dtl_tbl(i).trx_date := l_trx_dt;
1015 l_item_dtl_tbl(i).start_date := p_item_adj_tbl(i).start_date;
1016 l_item_dtl_tbl(i).end_date := p_item_adj_tbl(i).end_date;
1017 l_item_dtl_tbl(i).amount := p_item_adj_tbl(i).amount;
1018
1019 END IF;
1020
1021 END LOOP;
1022
1023 IF l_use_crnt_month AND l_consolidate AND l_total_amt <> 0 THEN
1024
1025 l_item_dtl_tbl(0).schedule_date := last_day(add_months(TRUNC(SYSDATE), -1)) + p_sch_day;
1026 l_item_dtl_tbl(0).start_date := p_item_adj_tbl(0).start_date;
1027 l_item_dtl_tbl(0).end_date := p_item_adj_tbl(p_item_adj_tbl.COUNT - 1).end_date;
1028 l_item_dtl_tbl(0).amount := l_total_amt;
1029
1030 IF l_trx_sysdate THEN
1031 l_item_dtl_tbl(0).trx_date := TRUNC(SYSDATE);
1032 ELSE
1033 l_item_dtl_tbl(0).trx_date := l_item_dtl_tbl(0).schedule_date;
1034 END IF;
1035
1036 END IF;
1037
1038 p_item_adj_tbl := l_item_dtl_tbl;
1039
1040 pnp_debug_pkg.log(l_desc ||' (-)');
1041
1042 EXCEPTION
1043 WHEN OTHERS THEN
1044 pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
1045 raise;
1046
1047 END prepare_new_items_from_adj;
1048
1049 ------------------------------------------------------------------------------+
1050 -- PROCEDURE : find_schedule
1051 -- DESCRIPTION : finds a draft schedule for a given schedule date for a lease
1052 -- OR creates a new draft schedule if none is found.
1053 -- HISTORY :
1054 -- 19-OCT-04 ftanudja o Created.
1055 -- 15-JUL-05 SatyaDeep o Replaced base views with their _ALL tables
1056 -- 10-AUG-05 piagrawa o Bug #4284035 - Passed org id of the lease to insert_row
1057 ------------------------------------------------------------------------------+
1058 PROCEDURE find_schedule (
1059 p_lease_id pn_leases.lease_id%TYPE,
1060 p_lease_change_id pn_lease_changes.lease_change_id%TYPE,
1061 p_term_id pn_payment_terms.payment_term_id%TYPE,
1062 p_schedule_date pn_payment_schedules.schedule_date%TYPE,
1063 p_schedule_id OUT NOCOPY pn_payment_schedules.payment_schedule_id%TYPE
1064 )
1065 IS
1066 CURSOR fetch_schedule IS
1067 SELECT payment_schedule_id
1068 FROM pn_payment_schedules_all
1069 WHERE schedule_date = p_schedule_date
1070 AND lease_id = p_lease_id
1071 AND payment_status_lookup_code = 'DRAFT';
1072
1073 CURSOR check_if_sch_belong_to_term(p_sch_id pn_payment_schedules.payment_schedule_id%TYPE) IS
1074 SELECT 'Y'
1075 FROM dual
1076 WHERE EXISTS (SELECT NULL
1077 FROM pn_payment_items_all
1078 WHERE payment_schedule_id = p_sch_id
1079 AND payment_term_id = p_term_id);
1080
1081 CURSOR org_id_cur IS
1082 SELECT org_id
1083 FROM pn_leases_all
1084 WHERE lease_id = p_lease_id;
1085
1086 l_rowid VARCHAR2(100);
1087 l_found BOOLEAN := FALSE;
1088
1089 l_info VARCHAR2(100);
1090 l_desc VARCHAR2(100) := 'pn_retro_adjustment_pkg.find_schedule';
1091 l_org_id NUMBER;
1092
1093 BEGIN
1094
1095 pnp_debug_pkg.log(l_desc ||' (+)');
1096
1097 l_info:= ' finding draft schedules for date: '||p_schedule_date;
1098 pnp_debug_pkg.log(l_info);
1099
1100 FOR org_id_rec IN org_id_cur LOOP
1101 l_org_id := org_id_rec.org_id;
1102 END LOOP;
1103
1104 FOR schedule_rec IN fetch_schedule LOOP
1105
1106 IF l_found = FALSE THEN
1107
1108 l_found := TRUE;
1109 p_schedule_id := schedule_rec.payment_schedule_id;
1110
1111 l_info := ' getting draft schedule id: '||p_schedule_id;
1112 pnp_debug_pkg.log(l_info);
1113
1114 ELSE -- if multiple schedules find
1115
1116 l_info := ' checking multiple schedules for : '||p_schedule_date;
1117 pnp_debug_pkg.log(l_info);
1118
1119 FOR get_id_rec IN check_if_sch_belong_to_term(schedule_rec.payment_schedule_id) LOOP
1120 p_schedule_id := schedule_rec.payment_schedule_id;
1121 END LOOP;
1122
1123 END IF;
1124
1125 END LOOP;
1126
1127 IF NOT l_found THEN
1128 l_info:= ' inserting a new draft schedule for date: '||p_schedule_date;
1129 pnp_debug_pkg.log(l_info);
1130
1131 pnt_payment_schedules_pkg.insert_row(
1132 x_context => null,
1133 x_rowid => l_rowid,
1134 x_payment_schedule_id => p_schedule_id,
1135 x_schedule_date => p_schedule_date,
1136 x_lease_change_id => p_lease_change_id,
1137 x_lease_id => p_lease_id,
1138 x_approved_by_user_id => null,
1139 x_transferred_by_user_ID => null,
1140 x_payment_status_lookup_code => 'DRAFT',
1141 x_approval_date => null,
1142 x_transfer_date => null,
1143 x_period_name => null,
1144 x_attribute_category => null,
1145 x_attribute1 => null,
1146 x_attribute2 => null,
1147 x_attribute3 => null,
1148 x_attribute4 => null,
1149 x_attribute5 => null,
1150 x_attribute6 => null,
1151 x_attribute7 => null,
1152 x_attribute8 => null,
1153 x_attribute9 => null,
1154 x_attribute10 => null,
1155 x_attribute11 => null,
1156 x_attribute12 => null,
1157 x_attribute13 => null,
1158 x_attribute14 => null,
1159 x_attribute15 => null,
1160 x_creation_date => SYSDATE,
1161 x_created_by => fnd_global.user_id,
1162 x_last_update_date => SYSDATE,
1163 x_last_updated_by => fnd_global.user_id,
1164 x_last_update_login => fnd_global.login_id,
1165 x_org_id => l_org_id
1166 );
1167
1168 END IF;
1169
1170 pnp_debug_pkg.log(l_desc ||' (-)');
1171
1172 EXCEPTION
1173 WHEN OTHERS THEN
1174 pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
1175 raise;
1176
1177 END find_schedule;
1178
1179 ------------------------------------------------------------------------------+
1180 -- PROCEDURE : get_schedule_id_for_new_items
1181 -- DESCRIPTION : calls pn_schedules_items.create_schedule for every schedule
1182 -- date in the table and returns the schedule id to the same
1183 -- table.
1184 -- NOTE : This is dependent on pn_schedules_items.create_schedule()
1185 -- HISTORY :
1186 -- 05-OCT-04 ftanudja o Created.
1187 ------------------------------------------------------------------------------+
1188 PROCEDURE get_schedule_id_for_new_items(
1189 p_lease_id pn_leases.lease_id%TYPE,
1190 p_term_id pn_payment_terms.payment_term_id%TYPE,
1191 p_lease_change_id pn_lease_changes.lease_change_id%TYPE,
1192 p_sched_tbl IN OUT NOCOPY payment_item_tbl_type
1193 )
1194 IS
1195 l_info VARCHAR2(100);
1196 l_desc VARCHAR2(100) := 'pn_retro_adjustment_pkg.get_schedule_id_for_new_items';
1197
1198 BEGIN
1199
1200 pnp_debug_pkg.log(l_desc ||' (+)');
1201
1202 l_info := ' starting loop ';
1203 pnp_debug_pkg.log(l_info);
1204
1205 FOR i IN 0 .. p_sched_tbl.COUNT - 1 LOOP
1206
1207 l_info := ' finding schedule for '||p_sched_tbl(i).schedule_date ;
1208 pnp_debug_pkg.log(l_info);
1209
1210 find_schedule(
1211 p_lease_id => p_lease_id,
1212 p_lease_change_id => p_lease_change_id,
1213 p_term_id => p_term_id,
1214 p_schedule_date => p_sched_tbl(i).schedule_date,
1215 p_schedule_id => p_sched_tbl(i).schedule_id
1216 );
1217
1218 END LOOP;
1219
1220 pnp_debug_pkg.log(l_desc ||' (-)');
1221
1222 EXCEPTION
1223 WHEN OTHERS THEN
1224 pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
1225 raise;
1226
1227 END get_schedule_id_for_new_items;
1228
1229 ------------------------------------------------------------------------------+
1230 -- PROCEDURE : remove_item_reference
1231 -- DESCRIPTION : takes a table containing payment id and remove all reference
1232 -- to it from the pn_adjustment_details table.
1233 -- HISTORY :
1234 -- 05-OCT-04 ftanudja o Created.
1235 ------------------------------------------------------------------------------+
1236
1237 PROCEDURE remove_item_reference (
1238 p_item_tbl payment_item_tbl_type
1239 )
1240 IS
1241
1242 l_payment_id_tbl item_id_tbl_type;
1243
1244 l_info VARCHAR2(100);
1245 l_desc VARCHAR2(100) := 'pn_retro_adjustment_pkg.remove_item_reference';
1246
1247 BEGIN
1248
1249 pnp_debug_pkg.log(l_desc ||' (+)');
1250
1251 l_info := ' preparing for bulk update ';
1252 pnp_debug_pkg.log(l_info);
1253
1254 FOR i IN 0 .. p_item_tbl.COUNT - 1 LOOP
1255 l_payment_id_tbl(i) := p_item_tbl(i).item_id;
1256 END LOOP;
1257
1258 l_info := ' performing bulk update ';
1259 pnp_debug_pkg.log(l_info);
1260
1261 FORALL i IN 0 .. l_payment_id_tbl.COUNT - 1
1262 UPDATE pn_adjustment_details
1263 SET payment_item_id = null,
1264 last_update_date = SYSDATE,
1265 last_updated_by = fnd_global.user_id,
1266 last_update_login = fnd_global.login_id
1267 WHERE payment_item_id = l_payment_id_tbl(i);
1268
1269 pnp_debug_pkg.log(l_desc ||' (-)');
1270
1271 EXCEPTION
1272 WHEN OTHERS THEN
1273 pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
1274 raise;
1275
1276 END remove_item_reference;
1277
1278 ------------------------------------------------------------------------------+
1279 -- PROCEDURE : process_items
1280 -- DESCRIPTION : Takes 3 tables for UPDATE, DELETE, and INSERT operation
1281 -- into the pn_payment_items table. Does BULK operations for
1282 -- efficiency.
1283 -- HISTORY :
1284 -- 05-OCT-04 ftanudja o Created.
1285 -- 15-JUL-05 SatyaDeep o Replaced base views with their _ALL tables
1286 ------------------------------------------------------------------------------+
1287 PROCEDURE process_items(
1288 p_term_id pn_payment_terms.payment_term_id%TYPE,
1289 p_adj_type_cd pn_payment_items.last_adjustment_type_code%TYPE,
1290 p_upd_item_tbl payment_item_tbl_type,
1291 p_del_item_tbl payment_item_tbl_type,
1292 p_new_item_tbl IN OUT NOCOPY payment_item_tbl_type
1293 )
1294 IS
1295
1296 CURSOR get_term_data IS
1297 SELECT vendor_id,
1298 vendor_site_id,
1299 customer_id,
1300 customer_site_use_id,
1301 cust_ship_site_id,
1302 set_of_books_id,
1303 currency_code,
1304 rate,
1305 estimated_amount,
1306 org_id
1307 FROM pn_payment_terms_all
1308 WHERE payment_term_id = p_term_id;
1309
1310 l_new_itm_id_tbl item_id_tbl_type;
1311 l_payment_id_tbl item_id_tbl_type;
1312 l_sched_id_tbl sched_id_tbl_type;
1313 l_act_amt_tbl amt_tbl_type;
1314 l_est_amt_tbl amt_tbl_type;
1315 l_trx_date_tbl date_tbl_type;
1316 l_start_date_tbl date_tbl_type;
1317 l_end_date_tbl date_tbl_type;
1318
1319 l_vendor_id pn_payment_terms.vendor_id%TYPE;
1320 l_vendor_site_id pn_payment_terms.vendor_site_id%TYPE;
1321 l_customer_id pn_payment_terms.customer_id%TYPE;
1322 l_customer_site_use_id pn_payment_terms.customer_site_use_id%TYPE;
1323 l_cust_ship_site_id pn_payment_terms.cust_ship_site_id%TYPE;
1324 l_set_of_books_id pn_payment_terms.set_of_books_id%TYPE;
1325 l_currency_code pn_payment_terms.currency_code%TYPE;
1326 l_rate pn_payment_terms.rate%TYPE;
1327
1328 l_precision NUMBER;
1329 l_ext_precision NUMBER;
1330 l_min_acct_unit NUMBER;
1331
1332 l_has_est_amt BOOLEAN;
1333
1334 l_info VARCHAR2(100);
1335 l_desc VARCHAR2(100) := 'pn_retro_adjustment_pkg.process_items';
1336 l_org_id NUMBER;
1337
1338 BEGIN
1339
1340 pnp_debug_pkg.log(l_desc ||' (+)');
1341
1342 l_info := ' updating items ';
1343 pnp_debug_pkg.log(l_info);
1344
1345 FOR i IN 0 .. p_upd_item_tbl.COUNT - 1 LOOP
1346 l_act_amt_tbl(i) := p_upd_item_tbl(i).amount;
1347 l_payment_id_tbl(i) := p_upd_item_tbl(i).item_id;
1348 IF l_has_est_amt THEN
1349 l_est_amt_tbl(i) := p_upd_item_tbl(i).amount;
1350 ELSE
1351 l_est_amt_tbl(i) := null;
1352 END IF;
1353 END LOOP;
1354
1355 l_info := ' preparing for bulk update ';
1356 pnp_debug_pkg.log(l_info);
1357
1358 FOR term_rec IN get_term_data LOOP
1359
1360 l_currency_code := term_rec.currency_code;
1361 fnd_currency.get_info( l_currency_code
1362 , l_precision
1363 , l_ext_precision
1364 , l_min_acct_unit);
1365
1366 EXIT;
1367 END LOOP;
1368
1369 FORALL i IN 0 .. l_payment_id_tbl.COUNT - 1
1370 UPDATE pn_payment_items_all
1371 SET actual_amount = ROUND(l_act_amt_tbl(i), l_precision),
1372 estimated_amount = ROUND(l_est_amt_tbl(i), l_precision),
1373 last_update_date = SYSDATE,
1374 last_updated_by = fnd_global.user_id,
1375 last_update_login = fnd_global.login_id
1376 WHERE payment_item_id = l_payment_id_tbl(i);
1377
1378 l_info := ' deleting items ';
1379 pnp_debug_pkg.log(l_info);
1380
1381 l_payment_id_tbl.delete;
1382
1383 FOR i IN 0 .. p_del_item_tbl.COUNT - 1 LOOP
1384 l_payment_id_tbl(i) := p_del_item_tbl(i).item_id;
1385 END LOOP;
1386
1387 l_info := ' preparing for bulk delete ';
1388 pnp_debug_pkg.log(l_info);
1389
1390 FORALL i IN 0 .. l_payment_id_tbl.COUNT - 1
1391 DELETE pn_payment_items
1392 WHERE payment_item_id = l_payment_id_tbl(i);
1393
1394 l_info := ' creating items ';
1395 pnp_debug_pkg.log(l_info);
1396
1397 l_payment_id_tbl.delete;
1398 l_act_amt_tbl.delete;
1399 l_est_amt_tbl.delete;
1400 l_has_est_amt := FALSE;
1401
1402 l_info := ' fetching term details ';
1403 pnp_debug_pkg.log(l_info);
1404
1405 FOR term_rec IN get_term_data LOOP
1406
1407 l_vendor_id := term_rec.vendor_id;
1408 l_vendor_site_id := term_rec.vendor_site_id;
1409 l_customer_id := term_rec.customer_id;
1410 l_customer_site_use_id := term_rec.customer_site_use_id;
1411 l_cust_ship_site_id := term_rec.cust_ship_site_id;
1412 l_set_of_books_id := term_rec.set_of_books_id;
1413 l_currency_code := term_rec.currency_code;
1414 l_rate := term_rec.rate;
1415 l_org_id := term_rec.org_id;
1416 IF term_rec.estimated_amount IS NOT NULL THEN
1417 l_has_est_amt := TRUE;
1418 END IF;
1419
1420 fnd_currency.get_info(l_currency_code, l_precision, l_ext_precision, l_min_acct_unit);
1421
1422 EXIT;
1423 END LOOP;
1424
1425 l_info := ' preparing for bulk insert ';
1426 pnp_debug_pkg.log(l_info);
1427
1428 FOR i IN 0 .. p_new_item_tbl.COUNT - 1 LOOP
1429 l_sched_id_tbl(i) := p_new_item_tbl(i).schedule_id;
1430 l_act_amt_tbl(i) := p_new_item_tbl(i).amount;
1431 l_trx_date_tbl(i) := p_new_item_tbl(i).trx_date;
1432 l_start_date_tbl(i) := p_new_item_tbl(i).start_date;
1433 l_end_date_tbl(i) := p_new_item_tbl(i).end_date;
1434
1435 IF l_has_est_amt THEN
1436 l_est_amt_tbl(i) := p_new_item_tbl(i).amount;
1437 ELSE
1438 l_est_amt_tbl(i) := null;
1439 END IF;
1440
1441 END LOOP;
1442
1443 FORALL i IN 0 .. l_sched_id_tbl.COUNT - 1
1444 INSERT INTO pn_payment_items_all
1445 (
1446 payment_item_id,
1447 last_update_date,
1448 last_updated_by,
1449 creation_date,
1450 created_by,
1451 last_update_login,
1452 actual_amount,
1453 estimated_amount,
1454 due_date,
1455 adj_start_date,
1456 adj_end_date,
1457 last_adjustment_type_code,
1458 payment_item_type_lookup_code,
1459 payment_term_id,
1460 payment_schedule_id,
1461 period_fraction,
1462 vendor_id,
1463 customer_id,
1464 vendor_site_id,
1465 customer_site_use_id,
1466 cust_ship_site_id,
1467 set_of_books_id,
1468 currency_code,
1469 export_currency_code,
1470 export_currency_amount,
1471 rate,
1472 org_id
1473 )
1474 VALUES
1475 (
1476 pn_payment_items_s.nextval,
1477 SYSDATE,
1478 fnd_global.user_id,
1479 SYSDATE,
1480 fnd_global.user_id,
1481 fnd_global.login_id,
1482 ROUND(l_act_amt_tbl(i), l_precision),
1483 ROUND(l_est_amt_tbl(i), l_precision),
1484 l_trx_date_tbl(i),
1485 l_start_date_tbl(i),
1486 l_end_date_tbl(i),
1487 p_adj_type_cd,
1488 'CASH',
1489 p_term_id,
1490 l_sched_id_tbl(i),
1491 1,
1492 l_vendor_id,
1493 l_customer_id,
1494 l_vendor_site_id,
1495 l_customer_site_use_id,
1496 l_cust_ship_site_id,
1497 l_set_of_books_id,
1498 l_currency_code,
1499 l_currency_code,
1500 null,
1501 l_rate,
1502 l_org_id
1503 ) RETURNING payment_item_id BULK COLLECT INTO l_new_itm_id_tbl;
1504
1505 -- NOTE: l_new_itm_id_tbl is populated starting from (1), not (0) --
1506
1507 l_info := ' updating p_new_item_tbl with newly inserted item id ';
1508 pnp_debug_pkg.log(l_info);
1509
1510 FOR i IN 0 .. p_new_item_tbl.COUNT - 1 LOOP
1511 p_new_item_tbl(i).item_id := l_new_itm_id_tbl(i + 1);
1512 END LOOP;
1513
1514 pnp_debug_pkg.log(l_desc ||' (-)');
1515
1516 EXCEPTION
1517 WHEN OTHERS THEN
1518 pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
1519 raise;
1520
1521 END process_items;
1522
1523 ------------------------------------------------------------------------------+
1524 -- PROCEDURE : populate_new_item_id
1525 -- DESCRIPTION : puts newly inserted payment item ID's into adjustment table
1526 -- by doing a simple bubble search
1527 -- NOTES : the procedure assumes both tables are ordered by dates
1528 -- HISTORY :
1529 -- 07-OCT-04 ftanudja o Created.
1530 ------------------------------------------------------------------------------+
1531 PROCEDURE populate_new_item_id(
1532 p_new_item_tbl payment_item_tbl_type,
1533 p_adj_tbl IN OUT NOCOPY payment_item_tbl_type
1534 )
1535 IS
1536 l_mark NUMBER := 0;
1537
1538 l_info VARCHAR2(100);
1539 l_desc VARCHAR2(100) := 'pn_retro_adjustment_pkg.populate_new_item_id';
1540
1541 BEGIN
1542
1543 pnp_debug_pkg.log(l_desc ||' (+)');
1544
1545 l_info := ' starting loop ';
1546 pnp_debug_pkg.log(l_info);
1547
1548 FOR i IN 0 .. p_adj_tbl.COUNT - 1 LOOP
1549
1550 IF p_adj_tbl(i).item_id IS NULL THEN
1551
1552 l_info := ' looping through adjustment table for start date '||p_adj_tbl(i).start_date||
1553 ' and end date '||p_adj_tbl(i).end_date;
1554 pnp_debug_pkg.log(l_info);
1555
1556 FOR j IN l_mark .. p_new_item_tbl.COUNT - 1 LOOP
1557
1558 IF p_new_item_tbl(j).start_date <= p_adj_tbl(i).start_date AND
1559 p_new_item_tbl(j).end_date >= p_adj_tbl(i).end_date
1560 THEN
1561
1562 l_info := ' found item match with start date '||p_new_item_tbl(j).start_date||
1563 ' and end date '||p_new_item_tbl(j).end_date;
1564 pnp_debug_pkg.log(l_info);
1565
1566 p_adj_tbl(i).item_id := p_new_item_tbl(j).item_id;
1567 l_mark := j;
1568 exit;
1569 END IF;
1570
1571 END LOOP;
1572
1573 END IF;
1574
1575 END LOOP;
1576
1577 pnp_debug_pkg.log(l_desc ||' (-)');
1578
1579 EXCEPTION
1580 WHEN OTHERS THEN
1581 pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
1582 raise;
1583
1584 END populate_new_item_id;
1585
1586 ------------------------------------------------------------------------------+
1587 -- PROCEDURE : create_adjustment_entries
1588 -- DESCRIPTION : creates a new batch of adjustment entries for the current
1589 -- term history
1590 -- HISTORY :
1591 -- 06-OCT-04 ftanudja o Created.
1592 -- 10-AUG-05 piagrawa o Bug#4284035 - Modified the signature to pass org id.
1593 ------------------------------------------------------------------------------+
1594 PROCEDURE create_adjustment_entries(
1595 p_term_id pn_payment_terms.payment_term_id%TYPE,
1596 p_term_freq pn_payment_terms.frequency_code%TYPE,
1597 p_term_start_dt pn_payment_terms.start_date%TYPE,
1598 p_term_end_dt pn_payment_terms.end_date%TYPE,
1599 p_term_hist_id pn_payment_terms_history.term_history_id%TYPE,
1600 p_adj_table payment_item_tbl_type,
1601 p_org_id NUMBER
1602 )
1603 IS
1604 l_start_date pn_adjustment_details.adj_start_date%TYPE;
1605 l_end_date pn_adjustment_details.adj_end_date%TYPE;
1606 l_adj_summ_id pn_adjustment_summaries.adjustment_summary_id%TYPE;
1607
1608 l_group_num NUMBER := 0;
1609 l_consolidate BOOLEAN;
1610
1611 l_info VARCHAR2(100);
1612 l_desc VARCHAR2(100) := 'pn_retro_adjustment_pkg.create_adjustment_entries';
1613
1614 BEGIN
1615
1616 pnp_debug_pkg.log(l_desc ||' (+)');
1617
1618 l_info := ' preparing adjustment data ';
1619 pnp_debug_pkg.log(l_info);
1620
1621 IF pn_mo_cache_utils.get_profile_value('PN_USE_SYSDATE_FOR_ADJ', p_org_id) = 'Y' AND
1622 pn_mo_cache_utils.get_profile_value('PN_CONSOLIDATE_ADJ_ITEMS', p_org_id) = 'Y' THEN
1623 l_consolidate := TRUE;
1624 ELSE
1625 l_consolidate := FALSE;
1626 END IF;
1627
1628 FOR i IN 0 .. p_adj_table.COUNT - 1 LOOP
1629
1630 IF p_adj_table(i).adj_summ_id IS NULL THEN
1631
1632 l_info := ' inserting into adjustment summary table for schedule date:'||
1633 p_adj_table(i).schedule_date;
1634 pnp_debug_pkg.log(l_info);
1635
1636 INSERT INTO pn_adjustment_summaries (
1637 adjustment_summary_id,
1638 adj_schedule_date,
1639 payment_term_id,
1640 sum_adj_amount,
1641 creation_date,
1642 created_by,
1643 last_update_date,
1644 last_updated_by,
1645 last_update_login
1646 ) VALUES (
1647 pn_adjustment_summaries_s.nextval,
1648 p_adj_table(i).schedule_date,
1649 p_term_id,
1650 p_adj_table(i).amount,
1651 SYSDATE,
1652 fnd_global.user_id,
1653 SYSDATE,
1654 fnd_global.user_id,
1655 fnd_global.login_id
1656 ) RETURNING adjustment_summary_id INTO l_adj_summ_id;
1657
1658 ELSE
1659
1660 l_adj_summ_id := p_adj_table(i).adj_summ_id;
1661
1662 END IF;
1663
1664 IF p_adj_table(i).start_date IS NULL OR p_adj_table(i).end_date IS NULL THEN
1665
1666 l_info := ' figuring out start and end dates for schedule date:'||
1667 p_adj_table(i).schedule_date;
1668 pnp_debug_pkg.log(l_info);
1669
1670 find_start_end_dates(
1671 p_term_freq => p_term_freq,
1672 p_term_start_dt => p_term_start_dt,
1673 p_term_end_dt => p_term_end_dt,
1674 p_schedule_dt => p_adj_table(i).schedule_date,
1675 x_start_date => l_start_date,
1676 x_end_date => l_end_date
1677 );
1678 ELSE
1679
1680 l_start_date := p_adj_table(i).start_date;
1681 l_end_date := p_adj_table(i).end_date;
1682
1683 END IF;
1684
1685 l_info := ' finding system options to determine group num ';
1686 pnp_debug_pkg.log(l_info);
1687
1688 IF NOT l_consolidate THEN
1689 l_group_num := l_group_num + 1;
1690 END IF;
1691
1692 l_info := ' inserting new adjustment for schedule date:'||
1693 p_adj_table(i).schedule_date;
1694 pnp_debug_pkg.log(l_info);
1695
1696 INSERT INTO pn_adjustment_details (
1697 adjustment_detail_id,
1698 term_history_id,
1699 adjustment_summary_id,
1700 payment_item_id,
1701 adj_start_date,
1702 adj_end_date,
1703 adjustment_amount,
1704 group_num,
1705 creation_date,
1706 created_by,
1707 last_update_date,
1708 last_updated_by,
1709 last_update_login
1710 ) VALUES (
1711 pn_adjustment_details_s.nextval,
1712 p_term_hist_id,
1713 l_adj_summ_id,
1714 p_adj_table(i).item_id,
1715 l_start_date,
1716 l_end_date,
1717 p_adj_table(i).amount,
1718 l_group_num,
1719 SYSDATE,
1720 fnd_global.user_id,
1721 SYSDATE,
1722 fnd_global.user_id,
1723 fnd_global.login_id
1724 );
1725
1726 IF p_adj_table(i).adj_summ_id IS NOT NULL THEN
1727
1728 l_info := ' updating adjustment summary id:'||
1729 p_adj_table(i).adj_summ_id;
1730 pnp_debug_pkg.log(l_info);
1731
1732 UPDATE pn_adjustment_summaries
1733 SET sum_adj_amount = sum_adj_amount + p_adj_table(i).amount,
1734 last_update_date = SYSDATE,
1735 last_updated_by = fnd_global.user_id,
1736 last_update_login = fnd_global.login_id
1737 WHERE adjustment_summary_id = p_adj_table(i).adj_summ_id;
1738
1739 END IF;
1740
1741 END LOOP;
1742
1743 pnp_debug_pkg.log(l_desc ||' (-)');
1744
1745 EXCEPTION
1746 WHEN OTHERS THEN
1747 pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
1748 raise;
1749
1750 END create_adjustment_entries;
1751
1752
1753 ------------------------------------------------------------------------------+
1754 -- PROCEDURE : cleanup_schedules
1755 -- DESCRIPTION : Given a lease id and lease end date it clean up schedules.
1756 -- Delete schedules with no items.Also deletes the draft schedules
1757 -- which lie outside the lease.
1758 -- PURPOSE :
1759 -- INVOKED FROM : schedules_items.mini_retro_contraction
1760 -- ARGUMENTS : p_lease_id
1761 -- HISTORY :
1762 -- 08-OCT-04 piagrawa o Bug 4354810 - Overloaded for mini -retro.
1763 -- 04-APR-06 piagrawa o Bug#5107134 - modified schedules_draft_cur to include
1764 -- status 'ON_HOLD'
1765 ------------------------------------------------------------------------------+
1766 PROCEDURE cleanup_schedules(p_lease_id pn_leases_all.lease_id%TYPE)
1767 IS
1768
1769 CURSOR schedules_draft_cur IS
1770 SELECT payment_schedule_id
1771 FROM pn_payment_schedules_all
1772 WHERE lease_id = p_lease_id
1773 AND payment_status_lookup_code IN ('DRAFT', 'ON_HOLD');
1774
1775 CURSOR find_payment_items_cur (p_sch_id pn_payment_schedules.payment_schedule_id%TYPE)IS
1776 SELECT payment_item_id
1777 FROM pn_payment_items_all item
1778 WHERE item.payment_schedule_id = p_sch_id;
1779
1780 l_found BOOLEAN;
1781
1782 BEGIN
1783 pnp_debug_pkg.log('cleanup_schedules (+)');
1784
1785 FOR schedules_draft_rec IN schedules_draft_cur LOOP
1786
1787 l_found := FALSE;
1788
1789 FOR find_payment_items_rec IN find_payment_items_cur(schedules_draft_rec.payment_schedule_id) LOOP
1790 l_found := TRUE;
1791 END LOOP;
1792
1793 IF(l_found = FALSE) THEN
1794
1795 pnp_debug_pkg.log('Deleting schedule id ........'||schedules_draft_rec.payment_schedule_id);
1796
1797 DELETE pn_payment_schedules_all
1798 WHERE payment_schedule_id = schedules_draft_rec.payment_schedule_id;
1799
1800 END IF;
1801
1802 END LOOP;
1803
1804 pnp_debug_pkg.log('cleanup_schedules (-)');
1805
1806 END cleanup_schedules;
1807
1808 ------------------------------------------------------------------------------+
1809 -- PROCEDURE : cleanup_schedules
1810 -- DESCRIPTION : Given a list of schedule id's, clean up schedules. Delete
1811 -- schedules with no items. Create 0 cash item for schedules
1812 -- with no cash items.
1813 -- HISTORY :
1814 -- 08-OCT-04 ftanudja o Created.
1815 -- 15-JUL-05 SatyaDeep o Replaced base views with their _ALL tables
1816 ------------------------------------------------------------------------------+
1817 PROCEDURE cleanup_schedules(
1818 p_term_id pn_payment_terms.payment_term_id%TYPE,
1819 p_orig_item_tbl payment_item_tbl_type,
1820 p_adj_item_tbl payment_item_tbl_type
1821 )
1822 IS
1823
1824 CURSOR find_cash_items (p_sch_id pn_payment_schedules.payment_schedule_id%TYPE)IS
1825 SELECT SUM(DECODE(item.payment_item_type_lookup_code, 'CASH', 1, 0)) num_cash
1826 FROM pn_payment_items_all item
1827 WHERE item.payment_schedule_id = p_sch_id;
1828
1829 CURSOR get_term_data IS
1830 SELECT vendor_id,
1831 vendor_site_id,
1832 customer_id,
1833 customer_site_use_id,
1834 cust_ship_site_id,
1835 set_of_books_id,
1836 currency_code,
1837 rate,
1838 estimated_amount,
1839 org_id
1840 FROM pn_payment_terms_all
1841 WHERE payment_term_id = p_term_id;
1842
1843 l_vendor_id pn_payment_terms.vendor_id%TYPE;
1844 l_vendor_site_id pn_payment_terms.vendor_site_id%TYPE;
1845 l_customer_id pn_payment_terms.customer_id%TYPE;
1846 l_customer_site_use_id pn_payment_terms.customer_site_use_id%TYPE;
1847 l_cust_ship_site_id pn_payment_terms.cust_ship_site_id%TYPE;
1848 l_set_of_books_id pn_payment_terms.set_of_books_id%TYPE;
1849 l_currency_code pn_payment_terms.currency_code%TYPE;
1850 l_rate pn_payment_terms.rate%TYPE;
1851
1852 l_sched_id_tbl sched_id_tbl_type;
1853 l_sched_dt_tbl date_tbl_type;
1854 l_found BOOLEAN;
1855 l_num_item NUMBER;
1856
1857 l_info VARCHAR2(100);
1858 l_desc VARCHAR2(100) := 'pn_retro_adjustment_pkg.cleanup_schedules';
1859 l_org_id NUMBER;
1860
1861 BEGIN
1862
1863 pnp_debug_pkg.log(l_desc ||' (+)');
1864
1865 l_info := ' looping through original items table ';
1866 pnp_debug_pkg.log(l_info);
1867
1868 FOR i IN 0 .. p_orig_item_tbl.COUNT - 1 LOOP
1869 l_sched_id_tbl(i) := p_orig_item_tbl(i).schedule_id;
1870 l_sched_dt_tbl(i) := p_orig_item_tbl(i).schedule_date;
1871 END LOOP;
1872
1873 l_info := ' looping through adjustment items table ';
1874 pnp_debug_pkg.log(l_info);
1875
1876 FOR i IN 0 .. p_adj_item_tbl.COUNT - 1 LOOP
1877
1878 l_found := FALSE;
1879
1880 FOR j IN 0 .. l_sched_id_tbl.COUNT - 1 LOOP
1881
1882 IF l_sched_id_tbl(j) = p_adj_item_tbl(i).schedule_id THEN
1883 l_found := TRUE;
1884 END IF;
1885
1886 END LOOP;
1887
1888 IF NOT l_found THEN
1889 l_sched_id_tbl(l_sched_id_tbl.COUNT) := p_adj_item_tbl(i).schedule_id;
1890 l_sched_dt_tbl(l_sched_dt_tbl.COUNT) := p_adj_item_tbl(i).schedule_date;
1891 END IF;
1892
1893 END LOOP;
1894
1895 l_info := ' looping through schedule id table ';
1896 pnp_debug_pkg.log(l_info);
1897
1898 FOR i IN 0 .. l_sched_id_tbl.COUNT - 1 LOOP
1899
1900 l_found := FALSE;
1901
1902 l_info := ' looping through schedule id table ';
1903 pnp_debug_pkg.log(l_info);
1904
1905 FOR items_rec IN find_cash_items(l_sched_id_tbl(i)) LOOP
1906 l_found := TRUE;
1907 l_num_item := items_rec.num_cash;
1908 END LOOP;
1909
1910 IF l_found AND l_num_item = 0 THEN
1911
1912 l_info := ' inserting $0 cash item onto schedule id: '||l_sched_id_tbl(i);
1913
1914 pnp_debug_pkg.log(l_info);
1915
1916 FOR term_rec IN get_term_data LOOP
1917
1918 l_vendor_id := term_rec.vendor_id;
1919 l_vendor_site_id := term_rec.vendor_site_id;
1920 l_customer_id := term_rec.customer_id;
1921 l_customer_site_use_id := term_rec.customer_site_use_id;
1922 l_cust_ship_site_id := term_rec.cust_ship_site_id;
1923 l_set_of_books_id := term_rec.set_of_books_id;
1924 l_currency_code := term_rec.currency_code;
1925 l_rate := term_rec.rate;
1926 l_org_id := term_rec.org_id;
1927 EXIT;
1928
1929 END LOOP;
1930
1931 INSERT INTO pn_payment_items_all
1932 (
1933 payment_item_id,
1934 last_update_date,
1935 last_updated_by,
1936 creation_date,
1937 created_by,
1938 last_update_login,
1939 actual_amount,
1940 estimated_amount,
1941 due_date,
1942 adj_start_date,
1943 adj_end_date,
1944 payment_item_type_lookup_code,
1945 payment_term_id,
1946 payment_schedule_id,
1947 period_fraction,
1948 vendor_id,
1949 customer_id,
1950 vendor_site_id,
1951 customer_site_use_id,
1952 cust_ship_site_id,
1953 set_of_books_id,
1954 currency_code,
1955 export_currency_code,
1956 export_currency_amount,
1957 rate,
1958 org_id
1959 ) VALUES
1960 (
1961 pn_payment_items_s.nextval,
1962 SYSDATE,
1963 fnd_global.user_id,
1964 SYSDATE,
1965 fnd_global.user_id,
1966 fnd_global.login_id,
1967 0,
1968 null,
1969 l_sched_dt_tbl(i),
1970 null,
1971 null,
1972 'CASH',
1973 p_term_id,
1974 l_sched_id_tbl(i),
1975 1,
1976 l_vendor_id,
1977 l_customer_id,
1978 l_vendor_site_id,
1979 l_customer_site_use_id,
1980 l_cust_ship_site_id,
1981 l_set_of_books_id,
1982 l_currency_code,
1983 l_currency_code,
1984 null,
1985 l_rate,
1986 l_org_id
1987 );
1988
1989 ELSIF NOT l_found THEN
1990
1991 l_info := ' deleting schedule id : '||l_sched_id_tbl(i);
1992 pnp_debug_pkg.log(l_info);
1993
1994 DELETE pn_payment_schedules_all
1995 WHERE payment_schedule_id = l_sched_id_tbl(i);
1996
1997 END IF;
1998 END LOOP;
1999
2000 pnp_debug_pkg.log(l_desc ||' (-)');
2001
2002 EXCEPTION
2003 WHEN OTHERS THEN
2004 pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
2005 raise;
2006
2007 END cleanup_schedules;
2008
2009 ------------------------------------------------------------------------------+
2010 -- PROCEDURE : update_terms_history
2011 -- DESCRIPTION : updates the term history table with the latest adjustment
2012 -- type code.
2013 -- HISTORY :
2014 -- 14-OCT-04 ftanudja o Created.
2015 -- 14-JAN-05 atuppad o Added code to update total_adj_amount
2016 ------------------------------------------------------------------------------+
2017 PROCEDURE update_terms_history(
2018 p_term_hist_id pn_payment_terms_history.term_history_id%TYPE,
2019 p_adj_type_cd pn_payment_items.last_adjustment_type_code%TYPE,
2020 p_lease_change_id pn_lease_changes.lease_change_id%TYPE,
2021 p_term_id pn_payment_terms.payment_term_id%TYPE
2022 )
2023 IS
2024
2025 -- Get total adj amount
2026 CURSOR get_total_adj_amt IS
2027 SELECT SUM(pad.adjustment_amount) total_adj_amount
2028 FROM pn_adjustment_details pad,
2029 pn_payment_terms_history pth
2030 WHERE pth.payment_term_id = p_term_id
2031 AND pth.lease_change_id = p_lease_change_id
2032 AND pad.term_history_id = pth.term_history_id;
2033
2034 l_info VARCHAR2(100);
2035 l_desc VARCHAR2(100) := 'pn_retro_adjustment_pkg.update_terms_history';
2036 l_amount NUMBER;
2037
2038 BEGIN
2039
2040 pnp_debug_pkg.log(l_desc ||' (+)');
2041
2042 FOR amt_rec IN get_total_adj_amt LOOP
2043 l_amount := amt_rec.total_adj_amount;
2044 END LOOP;
2045
2046 UPDATE pn_payment_terms_history
2047 SET adjustment_type_code = p_adj_type_cd,
2048 total_adj_amount = l_amount,
2049 last_update_date = SYSDATE,
2050 last_update_login = fnd_global.login_id,
2051 last_updated_by = fnd_global.user_id
2052 WHERE term_history_id = p_term_hist_id;
2053
2054 pnp_debug_pkg.log(l_desc ||' (-)');
2055
2056 EXCEPTION
2057 WHEN OTHERS THEN
2058 pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
2059 raise;
2060
2061 END update_terms_history;
2062
2063 ------------------------------------------------------------------------------+
2064 -- PROCEDURE : create_retro_adjustments
2065 -- DESCRIPTION : This is the main procedure being called for retro adjustment
2066 -- changes.
2067 -- HISTORY :
2068 -- 04-OCT-04 ftanudja o Created.
2069 -- 14-JAN-05 atuppad o Changed the signature of below proc
2070 -- - create_adjustment_tables
2071 -- - update_terms_history
2072 -- 15-JUL-05 SatyaDeep o Replaced base views with their _ALL tables
2073 -- 10-AUG-05 piagrawa o Bug #4284035 - Updated the calls to proc
2074 -- prepare_new_items_from_adj and create_adjustment_entries
2075 -- to pass org id.
2076 ------------------------------------------------------------------------------+
2077 PROCEDURE create_retro_adjustments(
2078 p_lease_id pn_payment_terms.lease_id%TYPE,
2079 p_lease_chg_id pn_lease_changes.lease_change_id%TYPE,
2080 p_term_id pn_payment_terms.payment_term_id%TYPE,
2081 p_term_start_dt pn_payment_terms.start_date%TYPE,
2082 p_term_end_dt pn_payment_terms.end_date%TYPE,
2083 p_term_sch_day pn_payment_terms.schedule_day%TYPE,
2084 p_term_act_amt pn_payment_terms.actual_amount%TYPE,
2085 p_term_freq pn_payment_terms.frequency_code%TYPE,
2086 p_term_hist_id pn_payment_terms_history.term_history_id%TYPE,
2087 p_adj_type_cd pn_payment_items.last_adjustment_type_code%TYPE
2088 )
2089 IS
2090 CURSOR get_last_appr_sched IS
2091 SELECT max(schedule_date) schedule_date
2092 FROM pn_payment_schedules_all
2093 WHERE lease_id = p_lease_id
2094 AND payment_status_lookup_code = 'APPROVED';
2095
2096 CURSOR org_id_cur IS
2097 SELECT org_id
2098 FROM pn_leases_all
2099 WHERE lease_id = p_lease_id;
2100
2101 l_virtual_sched payment_item_tbl_type;
2102 l_current_sched payment_item_tbl_type;
2103 l_merged_sched payment_item_tbl_type;
2104
2105 l_new_orig_table payment_item_tbl_type;
2106 l_upd_orig_table payment_item_tbl_type;
2107 l_del_orig_table payment_item_tbl_type;
2108
2109 l_new_itm_table payment_item_tbl_type;
2110 l_upd_itm_table payment_item_tbl_type;
2111 l_del_itm_table payment_item_tbl_type;
2112
2113 l_adj_table payment_item_tbl_type;
2114
2115 l_last_appr_dt DATE;
2116 l_info VARCHAR2(100);
2117 l_desc VARCHAR2(100) := 'pn_retro_adjustment_pkg.create_retro_adjustments';
2118 l_org_id NUMBER;
2119
2120 BEGIN
2121
2122 pnp_debug_pkg.log(l_desc ||' (+)');
2123
2124 FOR org_id_rec IN org_id_cur LOOP
2125 l_org_id := org_id_rec.org_id;
2126 END LOOP;
2127
2128 create_virtual_schedules (
2129 p_start_date => p_term_start_dt,
2130 p_end_date => p_term_end_dt,
2131 p_sch_day => p_term_sch_day,
2132 p_amount => p_term_act_amt,
2133 p_term_freq => p_term_freq,
2134 x_sched_tbl => l_virtual_sched
2135 );
2136
2137 get_current_schedules(
2138 p_term_id => p_term_id,
2139 x_sched_tbl => l_current_sched
2140 );
2141
2142 merge_schedules(
2143 p_current_sched => l_current_sched,
2144 p_virtual_sched => l_virtual_sched,
2145 x_sched_tbl => l_merged_sched
2146 );
2147
2148 -- get last approved schedule date
2149 FOR date_rec IN get_last_appr_sched LOOP
2150 l_last_appr_dt := date_rec.schedule_date;
2151 exit;
2152 END LOOP;
2153
2154 create_adjustment_tables(
2155 p_sched_table => l_merged_sched,
2156 p_last_appr_dt => l_last_appr_dt,
2157 p_term_freq => p_term_freq,
2158 p_term_start_dt => p_term_start_dt,
2159 p_term_end_dt => p_term_end_dt,
2160 x_new_orig_table => l_new_orig_table,
2161 x_upd_orig_table => l_upd_orig_table,
2162 x_del_orig_table => l_del_orig_table,
2163 x_adj_table => l_adj_table
2164 );
2165
2166 -- for new items, find schedule id
2167 get_schedule_id_for_new_items(
2168 p_lease_id => p_lease_id,
2169 p_term_id => p_term_id,
2170 p_lease_change_id => p_lease_chg_id,
2171 p_sched_tbl => l_new_orig_table
2172 );
2173
2174 -- process original items
2175 process_items(
2176 p_term_id => p_term_id,
2177 p_adj_type_cd => null,
2178 p_upd_item_tbl => l_upd_orig_table,
2179 p_del_item_tbl => l_del_orig_table,
2180 p_new_item_tbl => l_new_orig_table
2181 );
2182
2183 calculate_adjustment_details(
2184 p_adj_table => l_adj_table,
2185 p_new_itm_table => l_new_itm_table,
2186 p_upd_itm_table => l_upd_itm_table,
2187 p_del_itm_table => l_del_itm_table
2188 );
2189
2190 -- before deleting items, remove reference of items to be deleted
2191 -- from the adjustment table
2192 remove_item_reference(
2193 p_item_tbl => l_del_itm_table
2194 );
2195
2196 prepare_new_items_from_adj (
2197 p_sch_day => p_term_sch_day,
2198 p_item_adj_tbl => l_new_itm_table,
2199 p_org_id => l_org_id
2200 );
2201
2202 -- for new items, find schedule id
2203 get_schedule_id_for_new_items(
2204 p_lease_id => p_lease_id,
2205 p_term_id => p_term_id,
2206 p_lease_change_id => p_lease_chg_id,
2207 p_sched_tbl => l_new_itm_table
2208 );
2209
2210 -- process adjustment items
2211 process_items(
2212 p_term_id => p_term_id,
2213 p_adj_type_cd => p_adj_type_cd,
2214 p_upd_item_tbl => l_upd_itm_table,
2215 p_del_item_tbl => l_del_itm_table,
2216 p_new_item_tbl => l_new_itm_table
2217 );
2218
2219 populate_new_item_id(
2220 p_new_item_tbl => l_new_itm_table,
2221 p_adj_tbl => l_adj_table
2222 );
2223
2224 create_adjustment_entries(
2225 p_term_id => p_term_id,
2226 p_term_freq => p_term_freq,
2227 p_term_start_dt => p_term_start_dt,
2228 p_term_end_dt => p_term_end_dt,
2229 p_term_hist_id => p_term_hist_id,
2230 p_adj_table => l_adj_table,
2231 p_org_id => l_org_id
2232 );
2233
2234 -- clean up schedules of deleted items (original and adjustment)
2235 cleanup_schedules(
2236 p_term_id => p_term_id,
2237 p_orig_item_tbl => l_del_orig_table,
2238 p_adj_item_tbl => l_del_itm_table
2239 );
2240
2241 IF l_adj_table.COUNT > 0 THEN
2242
2243 update_terms_history(
2244 p_term_hist_id => p_term_hist_id,
2245 p_adj_type_cd => p_adj_type_cd,
2246 p_lease_change_id => p_lease_chg_id,
2247 p_term_id => p_term_id
2248 );
2249
2250 END IF;
2251
2252 pnp_debug_pkg.log(l_desc ||' (-)');
2253
2254 EXCEPTION
2255 WHEN OTHERS THEN
2256 pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
2257 raise;
2258
2259 END create_retro_adjustments;
2260
2261
2262 END pn_retro_adjustment_pkg;