[Home] [Help]
47: l_sch_str_dt DATE := NULL;
48:
49: BEGIN
50:
51: pnp_debug_pkg.log(l_desc ||' (+)');
52:
53: l_info := ' validating input ';
54: pnp_debug_pkg.log(l_info);
55:
50:
51: pnp_debug_pkg.log(l_desc ||' (+)');
52:
53: l_info := ' validating input ';
54: pnp_debug_pkg.log(l_info);
55:
56: IF p_start_date IS NULL OR
57: p_end_date IS NULL OR
58: p_sch_day IS NULL OR
65: raise bad_input_exception;
66: END IF;
67:
68: l_info := ' initializing variables ';
69: pnp_debug_pkg.log(l_info);
70:
71: SELECT cal_start
72: INTO l_cal_yr_st_dt
73: FROM PN_LEASES_ALL
108: l_current_end_date := add_months(l_current_start_date, l_freq_num) - 1;
109: END IF;
110:
111: l_info := ' getting amount for schedule start: '|| l_current_start_date;
112: pnp_debug_pkg.log(l_info);
113:
114: IF p_term_freq = 'OT' THEN
115:
116: l_current_amount := p_amount;
152: IF p_term_freq = 'OT' THEN l_current_end_date := p_end_date + 1; END IF;
153:
154: END LOOP;
155:
156: pnp_debug_pkg.log(l_desc ||' (-)');
157:
158: EXCEPTION
159: WHEN OTHERS THEN
160: pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
156: pnp_debug_pkg.log(l_desc ||' (-)');
157:
158: EXCEPTION
159: WHEN OTHERS THEN
160: pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
161: raise;
162:
163: END create_virtual_schedules;
164:
219: l_desc VARCHAR2(100) := 'pn_retro_adjustment_pkg.get_current_schedules';
220:
221: BEGIN
222:
223: pnp_debug_pkg.log(l_desc ||' (+)');
224:
225: l_info := ' fetching original items data ';
226: pnp_debug_pkg.log(l_info);
227:
222:
223: pnp_debug_pkg.log(l_desc ||' (+)');
224:
225: l_info := ' fetching original items data ';
226: pnp_debug_pkg.log(l_info);
227:
228: l_count_orig := l_orig_item_tbl.COUNT;
229:
230: FOR orig_data_rec IN fetch_original_items LOOP
236: l_count_orig := l_count_orig + 1;
237: END LOOP;
238:
239: l_info := ' fetching adjustment items data ';
240: pnp_debug_pkg.log(l_info);
241:
242: l_count_adj := l_adj_item_tbl.COUNT;
243:
244: FOR adj_data_rec IN fetch_adj_items LOOP
248: l_count_adj := l_count_adj + 1;
249: END LOOP;
250:
251: l_info := ' merging the two tables ';
252: pnp_debug_pkg.log(l_info);
253:
254: IF l_adj_item_tbl.COUNT = 0 THEN
255:
256: -- if no adjustments, then return l_orig_item_tbl
268: l_info := ' (orig = adj): inserting the current item into result table '||
269: ' orig_item date: '||l_orig_item_tbl(i).schedule_date ||
270: ' adj_item date: '||l_adj_item_tbl(l_count_adj).schedule_date;
271:
272: pnp_debug_pkg.log(l_info);
273:
274: x_sched_tbl(l_count_summ).item_id := l_orig_item_tbl(i).item_id;
275: x_sched_tbl(l_count_summ).schedule_id := l_orig_item_tbl(i).schedule_id;
276: x_sched_tbl(l_count_summ).schedule_date := l_orig_item_tbl(i).schedule_date;
292: l_info := ' (orig < adj): inserting the current item into result table '||
293: ' orig_item date: '||l_orig_item_tbl(i).schedule_date ||
294: ' adj_item date: '||l_adj_item_tbl(l_count_adj).schedule_date;
295:
296: pnp_debug_pkg.log(l_info);
297:
298: x_sched_tbl(l_count_summ).item_id := l_orig_item_tbl(i).item_id;
299: x_sched_tbl(l_count_summ).schedule_id := l_orig_item_tbl(i).schedule_id;
300: x_sched_tbl(l_count_summ).schedule_date := l_orig_item_tbl(i).schedule_date;
305: ELSE
306:
307: l_info := ' (orig > adj): looping through other table until a greater date is found ';
308:
309: pnp_debug_pkg.log(l_info);
310:
311: WHILE (l_count_adj <= l_adj_item_tbl.COUNT - 1) AND
312: (l_orig_item_tbl(i).schedule_date >
313: l_adj_item_tbl(l_count_adj).schedule_date)
316: l_info := ' inserting the current item into result table '||
317: ' orig_item date: '||l_orig_item_tbl(i).schedule_date ||
318: ' adj_item date: '||l_adj_item_tbl(l_count_adj).schedule_date;
319:
320: pnp_debug_pkg.log(l_info);
321:
322: IF (l_count_summ <> 0 AND
323: x_sched_tbl(l_count_summ - 1).schedule_date <>
324: l_adj_item_tbl(l_count_adj).schedule_date)
335:
336: l_info := ' finished finding lesser adj dates, now inserting current orig '||
337: ' item into result table orig_item date: '|| l_orig_item_tbl(i).schedule_date;
338:
339: pnp_debug_pkg.log(l_info);
340:
341: x_sched_tbl(l_count_summ).item_id := l_orig_item_tbl(i).item_id;
342: x_sched_tbl(l_count_summ).schedule_id := l_orig_item_tbl(i).schedule_id;
343: x_sched_tbl(l_count_summ).schedule_date := l_orig_item_tbl(i).schedule_date;
364:
365: END LOOP;
366:
367: l_info := ' merging the leftover items from l_orig_item_tbl ';
368: pnp_debug_pkg.log(l_info);
369:
370: IF l_count_orig IS NOT NULL THEN
371: FOR i IN l_count_orig .. l_orig_item_tbl.COUNT - 1 LOOP
372: x_sched_tbl(l_count_summ).item_id := l_orig_item_tbl(i).item_id;
378: END LOOP;
379: END IF;
380:
381: l_info := ' merging the leftover items from l_adj_item_tbl ';
382: pnp_debug_pkg.log(l_info);
383:
384: FOR i IN l_count_adj .. l_adj_item_tbl.COUNT - 1 LOOP
385: IF (l_count_summ <> 0 AND
386: x_sched_tbl(l_count_summ - 1).schedule_date <>
394: END IF;
395: END LOOP;
396: END IF;
397:
398: pnp_debug_pkg.log(l_desc ||' (-)');
399:
400: EXCEPTION
401: WHEN OTHERS THEN
402: pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
398: pnp_debug_pkg.log(l_desc ||' (-)');
399:
400: EXCEPTION
401: WHEN OTHERS THEN
402: pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
403: raise;
404:
405: END get_current_schedules;
406:
430: l_desc VARCHAR2(100) := 'pn_retro_adjustment_pkg.merge_schedules';
431:
432: BEGIN
433:
434: pnp_debug_pkg.log(l_desc ||' (+)');
435:
436: l_info := ' initializing counters ';
437: pnp_debug_pkg.log(l_info);
438:
433:
434: pnp_debug_pkg.log(l_desc ||' (+)');
435:
436: l_info := ' initializing counters ';
437: pnp_debug_pkg.log(l_info);
438:
439: l_count_curnt := null;
440: l_count_virtl := 0;
441: l_count_merge := 0;
440: l_count_virtl := 0;
441: l_count_merge := 0;
442:
443: l_info := ' merging the two tables ';
444: pnp_debug_pkg.log(l_info);
445:
446: FOR i IN 0 .. p_current_sched.COUNT - 1 LOOP
447:
448: IF p_current_sched(i).schedule_date = p_virtual_sched(l_count_virtl).schedule_date THEN
450: l_info := ' (curnt = virtl): inserting item into result table '||
451: ' curnt_item date: '||p_current_sched(i).schedule_date ||
452: ' virtl_item date: '||p_virtual_sched(l_count_virtl).schedule_date;
453:
454: pnp_debug_pkg.log(l_info);
455:
456: x_sched_tbl(l_count_merge).item_id := p_current_sched(i).item_id;
457: x_sched_tbl(l_count_merge).adj_summ_id := p_current_sched(i).adj_summ_id;
458: x_sched_tbl(l_count_merge).schedule_id := p_current_sched(i).schedule_id;
477: l_info := ' (curnt < virtl): inserting item into result table '||
478: ' curnt_item date: '||p_current_sched(i).schedule_date ||
479: ' virtl_item date: '||p_virtual_sched(l_count_virtl).schedule_date;
480:
481: pnp_debug_pkg.log(l_info);
482:
483: x_sched_tbl(l_count_merge).item_id := p_current_sched(i).item_id;
484: x_sched_tbl(l_count_merge).adj_summ_id := p_current_sched(i).adj_summ_id;
485: x_sched_tbl(l_count_merge).schedule_id := p_current_sched(i).schedule_id;
491: ELSE
492:
493: l_info := ' (curnt > virtl): looping through other table until a greater date is found ';
494:
495: pnp_debug_pkg.log(l_info);
496:
497: WHILE (l_count_virtl <= p_virtual_sched.COUNT - 1) AND
498: (p_current_sched(i).schedule_date >
499: p_virtual_sched(l_count_virtl).schedule_date)
502: l_info := ' inserting into result table '||
503: ' curnt_item date: '||p_current_sched(i).schedule_date ||
504: ' virtl_item date: '||p_virtual_sched(l_count_virtl).schedule_date;
505:
506: pnp_debug_pkg.log(l_info);
507:
508: IF (l_count_merge <> 0 AND
509: x_sched_tbl(l_count_merge - 1).schedule_date <>
510: p_virtual_sched(l_count_virtl).schedule_date)
523:
524: l_info := ' finished finding lesser adj dates, now inserting current '||
525: ' item into result table curnt_item date:'||p_current_sched(i).schedule_date;
526:
527: pnp_debug_pkg.log(l_info);
528:
529: x_sched_tbl(l_count_merge).item_id := p_current_sched(i).item_id;
530: x_sched_tbl(l_count_merge).adj_summ_id := p_current_sched(i).adj_summ_id;
531: x_sched_tbl(l_count_merge).schedule_id := p_current_sched(i).schedule_id;
554:
555: END LOOP;
556:
557: l_info := ' merging the leftover items from p_current_sched ';
558: pnp_debug_pkg.log(l_info);
559:
560: IF l_count_curnt IS NOT NULL THEN
561: FOR i IN l_count_curnt .. p_current_sched.COUNT - 1 LOOP
562: x_sched_tbl(l_count_merge).item_id := p_current_sched(i).item_id;
569: END LOOP;
570: END IF;
571:
572: l_info := ' merging the leftover items from p_virtual_sched ';
573: pnp_debug_pkg.log(l_info);
574:
575: FOR i IN l_count_virtl .. p_virtual_sched.COUNT - 1 LOOP
576: IF (l_count_merge <> 0 AND
577: x_sched_tbl(l_count_merge - 1).schedule_date <>
587: l_count_merge := l_count_merge + 1;
588: END IF;
589: END LOOP;
590:
591: pnp_debug_pkg.log(l_desc ||' (-)');
592:
593: EXCEPTION
594: WHEN OTHERS THEN
595: pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
591: pnp_debug_pkg.log(l_desc ||' (-)');
592:
593: EXCEPTION
594: WHEN OTHERS THEN
595: pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
596: raise;
597:
598: END merge_schedules;
599:
623: l_desc VARCHAR2(100) := 'pn_retro_adjustment_pkg.find_start_end_dates';
624:
625: BEGIN
626:
627: pnp_debug_pkg.log(l_desc ||' (+)');
628:
629: IF p_term_freq = 'MON' THEN
630:
631: x_end_date := last_day(p_schedule_dt);
641: x_end_date := add_months(x_start_date, l_freq_num) - 1;
642:
643: END IF;
644:
645: pnp_debug_pkg.log(l_desc ||' (-)');
646:
647: EXCEPTION
648: WHEN OTHERS THEN
649: pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
645: pnp_debug_pkg.log(l_desc ||' (-)');
646:
647: EXCEPTION
648: WHEN OTHERS THEN
649: pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
650: raise;
651:
652: END find_start_end_dates;
653:
711: l_desc VARCHAR2(100) := 'pn_retro_adjustment_pkg.create_adjustment_tables';
712:
713: BEGIN
714:
715: pnp_debug_pkg.log(l_desc ||' (+)');
716:
717: l_info := ' looping through the schedule table ';
718: pnp_debug_pkg.log(l_info);
719:
714:
715: pnp_debug_pkg.log(l_desc ||' (+)');
716:
717: l_info := ' looping through the schedule table ';
718: pnp_debug_pkg.log(l_info);
719:
720: l_last_appr_dt := nvl(p_last_appr_dt, TO_DATE('01/01/0001','DD/MM/YYYY'));
721:
722: FOR i IN 0 .. p_sched_table.COUNT - 1 LOOP
806: IF x_adj_table(i).start_date IS NULL OR x_adj_table(i).end_date IS NULL THEN
807:
808: l_info := ' now figuring out start and end dates for schedule date:'||
809: x_adj_table(i).schedule_date;
810: pnp_debug_pkg.log(l_info);
811:
812: find_start_end_dates(
813: p_term_freq => p_term_freq,
814: p_term_start_dt => p_term_start_dt,
824:
825: END LOOP;
826: /* AMTNEW CHANGES - END */
827:
828: pnp_debug_pkg.log(l_desc ||' (-)');
829:
830: EXCEPTION
831: WHEN OTHERS THEN
832: pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
828: pnp_debug_pkg.log(l_desc ||' (-)');
829:
830: EXCEPTION
831: WHEN OTHERS THEN
832: pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
833: raise;
834:
835: END create_adjustment_tables;
836:
882: l_desc VARCHAR2(100) := 'pn_retro_adjustment_pkg.calculate_adjustment_details';
883:
884: BEGIN
885:
886: pnp_debug_pkg.log(l_desc ||' (+)');
887:
888: l_info := ' initializing counters ';
889: pnp_debug_pkg.log(l_info);
890:
885:
886: pnp_debug_pkg.log(l_desc ||' (+)');
887:
888: l_info := ' initializing counters ';
889: pnp_debug_pkg.log(l_info);
890:
891: l_count_new_itm := 0;
892: l_count_upd_itm := 0;
893: l_count_del_itm := 0;
900:
901: IF p_adj_table(i).adj_summ_id IS NOT NULL THEN
902:
903: l_info := ' fetching items for adj summary id: '||p_adj_table(i).adj_summ_id;
904: pnp_debug_pkg.log(l_info);
905:
906: FOR items_rec IN get_item_details (p_adj_table(i).adj_summ_id) LOOP
907:
908: l_exist_draft_adj := TRUE;
944: END IF;
945:
946: END LOOP;
947:
948: pnp_debug_pkg.log(l_desc ||' (-)');
949:
950: EXCEPTION
951: WHEN OTHERS THEN
952: pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
948: pnp_debug_pkg.log(l_desc ||' (-)');
949:
950: EXCEPTION
951: WHEN OTHERS THEN
952: pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
953: raise;
954:
955: END calculate_adjustment_details;
956:
985: l_desc VARCHAR2(100) := 'pn_retro_adjustment_pkg.prepare_new_items_from_adj';
986:
987: BEGIN
988:
989: pnp_debug_pkg.log(l_desc ||' (+)');
990:
991: l_info := ' fetching system option values ';
992: pnp_debug_pkg.log(l_info);
993:
988:
989: pnp_debug_pkg.log(l_desc ||' (+)');
990:
991: l_info := ' fetching system option values ';
992: pnp_debug_pkg.log(l_info);
993:
994: IF pn_mo_cache_utils.get_profile_value('PN_CONSOLIDATE_ADJ_ITEMS', p_org_id) = 'Y' THEN
995: l_consolidate := TRUE;
996: ELSE
1057: END IF;
1058:
1059: p_item_adj_tbl := l_item_dtl_tbl;
1060:
1061: pnp_debug_pkg.log(l_desc ||' (-)');
1062:
1063: EXCEPTION
1064: WHEN OTHERS THEN
1065: pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
1061: pnp_debug_pkg.log(l_desc ||' (-)');
1062:
1063: EXCEPTION
1064: WHEN OTHERS THEN
1065: pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
1066: raise;
1067:
1068: END prepare_new_items_from_adj;
1069:
1112: l_org_id NUMBER;
1113:
1114: BEGIN
1115:
1116: pnp_debug_pkg.log(l_desc ||' (+)');
1117:
1118: l_info:= ' finding draft schedules for date: '||p_schedule_date;
1119: pnp_debug_pkg.log(l_info);
1120:
1115:
1116: pnp_debug_pkg.log(l_desc ||' (+)');
1117:
1118: l_info:= ' finding draft schedules for date: '||p_schedule_date;
1119: pnp_debug_pkg.log(l_info);
1120:
1121: FOR org_id_rec IN org_id_cur LOOP
1122: l_org_id := org_id_rec.org_id;
1123: END LOOP;
1129: l_found := TRUE;
1130: p_schedule_id := schedule_rec.payment_schedule_id;
1131:
1132: l_info := ' getting draft schedule id: '||p_schedule_id;
1133: pnp_debug_pkg.log(l_info);
1134:
1135: ELSE -- if multiple schedules find
1136:
1137: l_info := ' checking multiple schedules for : '||p_schedule_date;
1134:
1135: ELSE -- if multiple schedules find
1136:
1137: l_info := ' checking multiple schedules for : '||p_schedule_date;
1138: pnp_debug_pkg.log(l_info);
1139:
1140: FOR get_id_rec IN check_if_sch_belong_to_term(schedule_rec.payment_schedule_id) LOOP
1141: p_schedule_id := schedule_rec.payment_schedule_id;
1142: END LOOP;
1146: END LOOP;
1147:
1148: IF NOT l_found THEN
1149: l_info:= ' inserting a new draft schedule for date: '||p_schedule_date;
1150: pnp_debug_pkg.log(l_info);
1151:
1152: pnt_payment_schedules_pkg.insert_row(
1153: x_context => null,
1154: x_rowid => l_rowid,
1187: );
1188:
1189: END IF;
1190:
1191: pnp_debug_pkg.log(l_desc ||' (-)');
1192:
1193: EXCEPTION
1194: WHEN OTHERS THEN
1195: pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
1191: pnp_debug_pkg.log(l_desc ||' (-)');
1192:
1193: EXCEPTION
1194: WHEN OTHERS THEN
1195: pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
1196: raise;
1197:
1198: END find_schedule;
1199:
1217: l_desc VARCHAR2(100) := 'pn_retro_adjustment_pkg.get_schedule_id_for_new_items';
1218:
1219: BEGIN
1220:
1221: pnp_debug_pkg.log(l_desc ||' (+)');
1222:
1223: l_info := ' starting loop ';
1224: pnp_debug_pkg.log(l_info);
1225:
1220:
1221: pnp_debug_pkg.log(l_desc ||' (+)');
1222:
1223: l_info := ' starting loop ';
1224: pnp_debug_pkg.log(l_info);
1225:
1226: FOR i IN 0 .. p_sched_tbl.COUNT - 1 LOOP
1227:
1228: l_info := ' finding schedule for '||p_sched_tbl(i).schedule_date ;
1225:
1226: FOR i IN 0 .. p_sched_tbl.COUNT - 1 LOOP
1227:
1228: l_info := ' finding schedule for '||p_sched_tbl(i).schedule_date ;
1229: pnp_debug_pkg.log(l_info);
1230:
1231: find_schedule(
1232: p_lease_id => p_lease_id,
1233: p_lease_change_id => p_lease_change_id,
1237: );
1238:
1239: END LOOP;
1240:
1241: pnp_debug_pkg.log(l_desc ||' (-)');
1242:
1243: EXCEPTION
1244: WHEN OTHERS THEN
1245: pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
1241: pnp_debug_pkg.log(l_desc ||' (-)');
1242:
1243: EXCEPTION
1244: WHEN OTHERS THEN
1245: pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
1246: raise;
1247:
1248: END get_schedule_id_for_new_items;
1249:
1266: l_desc VARCHAR2(100) := 'pn_retro_adjustment_pkg.remove_item_reference';
1267:
1268: BEGIN
1269:
1270: pnp_debug_pkg.log(l_desc ||' (+)');
1271:
1272: l_info := ' preparing for bulk update ';
1273: pnp_debug_pkg.log(l_info);
1274:
1269:
1270: pnp_debug_pkg.log(l_desc ||' (+)');
1271:
1272: l_info := ' preparing for bulk update ';
1273: pnp_debug_pkg.log(l_info);
1274:
1275: FOR i IN 0 .. p_item_tbl.COUNT - 1 LOOP
1276: l_payment_id_tbl(i) := p_item_tbl(i).item_id;
1277: END LOOP;
1276: l_payment_id_tbl(i) := p_item_tbl(i).item_id;
1277: END LOOP;
1278:
1279: l_info := ' performing bulk update ';
1280: pnp_debug_pkg.log(l_info);
1281:
1282: FORALL i IN 0 .. l_payment_id_tbl.COUNT - 1
1283: UPDATE pn_adjustment_details
1284: SET payment_item_id = null,
1286: last_updated_by = fnd_global.user_id,
1287: last_update_login = fnd_global.login_id
1288: WHERE payment_item_id = l_payment_id_tbl(i);
1289:
1290: pnp_debug_pkg.log(l_desc ||' (-)');
1291:
1292: EXCEPTION
1293: WHEN OTHERS THEN
1294: pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
1290: pnp_debug_pkg.log(l_desc ||' (-)');
1291:
1292: EXCEPTION
1293: WHEN OTHERS THEN
1294: pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
1295: raise;
1296:
1297: END remove_item_reference;
1298:
1357: l_org_id NUMBER;
1358:
1359: BEGIN
1360:
1361: pnp_debug_pkg.log(l_desc ||' (+)');
1362:
1363: l_info := ' updating items ';
1364: pnp_debug_pkg.log(l_info);
1365:
1360:
1361: pnp_debug_pkg.log(l_desc ||' (+)');
1362:
1363: l_info := ' updating items ';
1364: pnp_debug_pkg.log(l_info);
1365:
1366: FOR i IN 0 .. p_upd_item_tbl.COUNT - 1 LOOP
1367: l_act_amt_tbl(i) := p_upd_item_tbl(i).amount;
1368: l_payment_id_tbl(i) := p_upd_item_tbl(i).item_id;
1373: END IF;
1374: END LOOP;
1375:
1376: l_info := ' preparing for bulk update ';
1377: pnp_debug_pkg.log(l_info);
1378:
1379: FOR term_rec IN get_term_data LOOP
1380:
1381: l_currency_code := term_rec.currency_code;
1396: last_update_login = fnd_global.login_id
1397: WHERE payment_item_id = l_payment_id_tbl(i);
1398:
1399: l_info := ' deleting items ';
1400: pnp_debug_pkg.log(l_info);
1401:
1402: l_payment_id_tbl.delete;
1403:
1404: FOR i IN 0 .. p_del_item_tbl.COUNT - 1 LOOP
1405: l_payment_id_tbl(i) := p_del_item_tbl(i).item_id;
1406: END LOOP;
1407:
1408: l_info := ' preparing for bulk delete ';
1409: pnp_debug_pkg.log(l_info);
1410:
1411: FORALL i IN 0 .. l_payment_id_tbl.COUNT - 1
1412: DELETE pn_payment_items
1413: WHERE payment_item_id = l_payment_id_tbl(i);
1412: DELETE pn_payment_items
1413: WHERE payment_item_id = l_payment_id_tbl(i);
1414:
1415: l_info := ' creating items ';
1416: pnp_debug_pkg.log(l_info);
1417:
1418: l_payment_id_tbl.delete;
1419: l_act_amt_tbl.delete;
1420: l_est_amt_tbl.delete;
1420: l_est_amt_tbl.delete;
1421: l_has_est_amt := FALSE;
1422:
1423: l_info := ' fetching term details ';
1424: pnp_debug_pkg.log(l_info);
1425:
1426: FOR term_rec IN get_term_data LOOP
1427:
1428: l_vendor_id := term_rec.vendor_id;
1443: EXIT;
1444: END LOOP;
1445:
1446: l_info := ' preparing for bulk insert ';
1447: pnp_debug_pkg.log(l_info);
1448:
1449: FOR i IN 0 .. p_new_item_tbl.COUNT - 1 LOOP
1450: l_sched_id_tbl(i) := p_new_item_tbl(i).schedule_id;
1451: l_act_amt_tbl(i) := p_new_item_tbl(i).amount;
1525:
1526: -- NOTE: l_new_itm_id_tbl is populated starting from (1), not (0) --
1527:
1528: l_info := ' updating p_new_item_tbl with newly inserted item id ';
1529: pnp_debug_pkg.log(l_info);
1530:
1531: FOR i IN 0 .. p_new_item_tbl.COUNT - 1 LOOP
1532: p_new_item_tbl(i).item_id := l_new_itm_id_tbl(i + 1);
1533: END LOOP;
1531: FOR i IN 0 .. p_new_item_tbl.COUNT - 1 LOOP
1532: p_new_item_tbl(i).item_id := l_new_itm_id_tbl(i + 1);
1533: END LOOP;
1534:
1535: pnp_debug_pkg.log(l_desc ||' (-)');
1536:
1537: EXCEPTION
1538: WHEN OTHERS THEN
1539: pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
1535: pnp_debug_pkg.log(l_desc ||' (-)');
1536:
1537: EXCEPTION
1538: WHEN OTHERS THEN
1539: pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
1540: raise;
1541:
1542: END process_items;
1543:
1560: l_desc VARCHAR2(100) := 'pn_retro_adjustment_pkg.populate_new_item_id';
1561:
1562: BEGIN
1563:
1564: pnp_debug_pkg.log(l_desc ||' (+)');
1565:
1566: l_info := ' starting loop ';
1567: pnp_debug_pkg.log(l_info);
1568:
1563:
1564: pnp_debug_pkg.log(l_desc ||' (+)');
1565:
1566: l_info := ' starting loop ';
1567: pnp_debug_pkg.log(l_info);
1568:
1569: FOR i IN 0 .. p_adj_tbl.COUNT - 1 LOOP
1570:
1571: IF p_adj_tbl(i).item_id IS NULL THEN
1571: IF p_adj_tbl(i).item_id IS NULL THEN
1572:
1573: l_info := ' looping through adjustment table for start date '||p_adj_tbl(i).start_date||
1574: ' and end date '||p_adj_tbl(i).end_date;
1575: pnp_debug_pkg.log(l_info);
1576:
1577: FOR j IN l_mark .. p_new_item_tbl.COUNT - 1 LOOP
1578:
1579: IF p_new_item_tbl(j).start_date <= p_adj_tbl(i).start_date AND
1581: THEN
1582:
1583: l_info := ' found item match with start date '||p_new_item_tbl(j).start_date||
1584: ' and end date '||p_new_item_tbl(j).end_date;
1585: pnp_debug_pkg.log(l_info);
1586:
1587: p_adj_tbl(i).item_id := p_new_item_tbl(j).item_id;
1588: l_mark := j;
1589: exit;
1594: END IF;
1595:
1596: END LOOP;
1597:
1598: pnp_debug_pkg.log(l_desc ||' (-)');
1599:
1600: EXCEPTION
1601: WHEN OTHERS THEN
1602: pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
1598: pnp_debug_pkg.log(l_desc ||' (-)');
1599:
1600: EXCEPTION
1601: WHEN OTHERS THEN
1602: pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
1603: raise;
1604:
1605: END populate_new_item_id;
1606:
1633: l_desc VARCHAR2(100) := 'pn_retro_adjustment_pkg.create_adjustment_entries';
1634:
1635: BEGIN
1636:
1637: pnp_debug_pkg.log(l_desc ||' (+)');
1638:
1639: l_info := ' preparing adjustment data ';
1640: pnp_debug_pkg.log(l_info);
1641:
1636:
1637: pnp_debug_pkg.log(l_desc ||' (+)');
1638:
1639: l_info := ' preparing adjustment data ';
1640: pnp_debug_pkg.log(l_info);
1641:
1642: IF pn_mo_cache_utils.get_profile_value('PN_USE_SYSDATE_FOR_ADJ', p_org_id) = 'Y' AND
1643: pn_mo_cache_utils.get_profile_value('PN_CONSOLIDATE_ADJ_ITEMS', p_org_id) = 'Y' THEN
1644: l_consolidate := TRUE;
1651: IF p_adj_table(i).adj_summ_id IS NULL THEN
1652:
1653: l_info := ' inserting into adjustment summary table for schedule date:'||
1654: p_adj_table(i).schedule_date;
1655: pnp_debug_pkg.log(l_info);
1656:
1657: INSERT INTO pn_adjustment_summaries (
1658: adjustment_summary_id,
1659: adj_schedule_date,
1685: IF p_adj_table(i).start_date IS NULL OR p_adj_table(i).end_date IS NULL THEN
1686:
1687: l_info := ' figuring out start and end dates for schedule date:'||
1688: p_adj_table(i).schedule_date;
1689: pnp_debug_pkg.log(l_info);
1690:
1691: find_start_end_dates(
1692: p_term_freq => p_term_freq,
1693: p_term_start_dt => p_term_start_dt,
1703:
1704: END IF;
1705:
1706: l_info := ' finding system options to determine group num ';
1707: pnp_debug_pkg.log(l_info);
1708:
1709: IF NOT l_consolidate THEN
1710: l_group_num := l_group_num + 1;
1711: END IF;
1711: END IF;
1712:
1713: l_info := ' inserting new adjustment for schedule date:'||
1714: p_adj_table(i).schedule_date;
1715: pnp_debug_pkg.log(l_info);
1716:
1717: INSERT INTO pn_adjustment_details (
1718: adjustment_detail_id,
1719: term_history_id,
1747: IF p_adj_table(i).adj_summ_id IS NOT NULL THEN
1748:
1749: l_info := ' updating adjustment summary id:'||
1750: p_adj_table(i).adj_summ_id;
1751: pnp_debug_pkg.log(l_info);
1752:
1753: UPDATE pn_adjustment_summaries
1754: SET sum_adj_amount = sum_adj_amount + p_adj_table(i).amount,
1755: last_update_date = SYSDATE,
1760: END IF;
1761:
1762: END LOOP;
1763:
1764: pnp_debug_pkg.log(l_desc ||' (-)');
1765:
1766: EXCEPTION
1767: WHEN OTHERS THEN
1768: pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
1764: pnp_debug_pkg.log(l_desc ||' (-)');
1765:
1766: EXCEPTION
1767: WHEN OTHERS THEN
1768: pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
1769: raise;
1770:
1771: END create_adjustment_entries;
1772:
1800:
1801: l_found BOOLEAN;
1802:
1803: BEGIN
1804: pnp_debug_pkg.log('cleanup_schedules (+)');
1805:
1806: FOR schedules_draft_rec IN schedules_draft_cur LOOP
1807:
1808: l_found := FALSE;
1812: END LOOP;
1813:
1814: IF(l_found = FALSE) THEN
1815:
1816: pnp_debug_pkg.log('Deleting schedule id ........'||schedules_draft_rec.payment_schedule_id);
1817:
1818: DELETE pn_payment_schedules_all
1819: WHERE payment_schedule_id = schedules_draft_rec.payment_schedule_id;
1820:
1821: END IF;
1822:
1823: END LOOP;
1824:
1825: pnp_debug_pkg.log('cleanup_schedules (-)');
1826:
1827: END cleanup_schedules;
1828:
1829: ------------------------------------------------------------------------------+
1880: l_org_id NUMBER;
1881:
1882: BEGIN
1883:
1884: pnp_debug_pkg.log(l_desc ||' (+)');
1885:
1886: l_info := ' looping through original items table ';
1887: pnp_debug_pkg.log(l_info);
1888:
1883:
1884: pnp_debug_pkg.log(l_desc ||' (+)');
1885:
1886: l_info := ' looping through original items table ';
1887: pnp_debug_pkg.log(l_info);
1888:
1889: FOR i IN 0 .. p_orig_item_tbl.COUNT - 1 LOOP
1890: l_sched_id_tbl(i) := p_orig_item_tbl(i).schedule_id;
1891: l_sched_dt_tbl(i) := p_orig_item_tbl(i).schedule_date;
1891: l_sched_dt_tbl(i) := p_orig_item_tbl(i).schedule_date;
1892: END LOOP;
1893:
1894: l_info := ' looping through adjustment items table ';
1895: pnp_debug_pkg.log(l_info);
1896:
1897: FOR i IN 0 .. p_adj_item_tbl.COUNT - 1 LOOP
1898:
1899: l_found := FALSE;
1913:
1914: END LOOP;
1915:
1916: l_info := ' looping through schedule id table ';
1917: pnp_debug_pkg.log(l_info);
1918:
1919: FOR i IN 0 .. l_sched_id_tbl.COUNT - 1 LOOP
1920:
1921: l_found := FALSE;
1920:
1921: l_found := FALSE;
1922:
1923: l_info := ' looping through schedule id table ';
1924: pnp_debug_pkg.log(l_info);
1925:
1926: FOR items_rec IN find_cash_items(l_sched_id_tbl(i)) LOOP
1927: l_found := TRUE;
1928: l_num_item := items_rec.num_cash;
1931: IF l_found AND l_num_item = 0 THEN
1932:
1933: l_info := ' inserting $0 cash item onto schedule id: '||l_sched_id_tbl(i);
1934:
1935: pnp_debug_pkg.log(l_info);
1936:
1937: FOR term_rec IN get_term_data LOOP
1938:
1939: l_vendor_id := term_rec.vendor_id;
2009:
2010: ELSIF NOT l_found THEN
2011:
2012: l_info := ' deleting schedule id : '||l_sched_id_tbl(i);
2013: pnp_debug_pkg.log(l_info);
2014:
2015: DELETE pn_payment_schedules_all
2016: WHERE payment_schedule_id = l_sched_id_tbl(i);
2017:
2017:
2018: END IF;
2019: END LOOP;
2020:
2021: pnp_debug_pkg.log(l_desc ||' (-)');
2022:
2023: EXCEPTION
2024: WHEN OTHERS THEN
2025: pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
2021: pnp_debug_pkg.log(l_desc ||' (-)');
2022:
2023: EXCEPTION
2024: WHEN OTHERS THEN
2025: pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
2026: raise;
2027:
2028: END cleanup_schedules;
2029:
2057: l_amount NUMBER;
2058:
2059: BEGIN
2060:
2061: pnp_debug_pkg.log(l_desc ||' (+)');
2062:
2063: FOR amt_rec IN get_total_adj_amt LOOP
2064: l_amount := amt_rec.total_adj_amount;
2065: END LOOP;
2071: last_update_login = fnd_global.login_id,
2072: last_updated_by = fnd_global.user_id
2073: WHERE term_history_id = p_term_hist_id;
2074:
2075: pnp_debug_pkg.log(l_desc ||' (-)');
2076:
2077: EXCEPTION
2078: WHEN OTHERS THEN
2079: pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
2075: pnp_debug_pkg.log(l_desc ||' (-)');
2076:
2077: EXCEPTION
2078: WHEN OTHERS THEN
2079: pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
2080: raise;
2081:
2082: END update_terms_history;
2083:
2139: l_org_id NUMBER;
2140:
2141: BEGIN
2142:
2143: pnp_debug_pkg.log(l_desc ||' (+)');
2144:
2145: FOR org_id_rec IN org_id_cur LOOP
2146: l_org_id := org_id_rec.org_id;
2147: END LOOP;
2270: );
2271:
2272: END IF;
2273:
2274: pnp_debug_pkg.log(l_desc ||' (-)');
2275:
2276: EXCEPTION
2277: WHEN OTHERS THEN
2278: pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
2274: pnp_debug_pkg.log(l_desc ||' (-)');
2275:
2276: EXCEPTION
2277: WHEN OTHERS THEN
2278: pnp_debug_pkg.log(l_desc || ': Error while ' || l_info);
2279: raise;
2280:
2281: END create_retro_adjustments;
2282: