[Home] [Help]
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:
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
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:
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;
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);
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:
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:
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
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
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
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;
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;
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)
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)
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;
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;
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 <>
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);
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:
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:
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;
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
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;
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;
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)
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)
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;
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;
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 <>
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);
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:
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);
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);
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:
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:
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
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,
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);
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:
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:
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;
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;
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);
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:
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:
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
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);
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:
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:
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;
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;
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;
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,
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);
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:
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:
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 ;
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,
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);
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:
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:
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;
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,
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);
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:
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:
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;
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;
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);
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;
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;
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;
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;
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);
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:
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:
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
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
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;
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);
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:
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:
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;
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,
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,
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;
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,
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,
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);
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:
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;
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: ------------------------------------------------------------------------------+
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:
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;
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;
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;
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;
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;
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:
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);
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:
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;
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);
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:
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;
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);
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: