[Home] [Help]
1: PACKAGE BODY IEX_PROMISES_BATCH_PUB as
2: /* $Header: iexpyrbb.pls 120.22.12020000.4 2012/10/19 17:30:20 sunagesh ship $ */
3:
4: PG_DEBUG NUMBER; -- := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
5:
6: G_APP_ID CONSTANT NUMBER := 695;
7: G_PKG_NAME CONSTANT VARCHAR2(30):= 'IEX_PROMISES_BATCH_PUB';
8: G_FILE_NAME CONSTANT VARCHAR2(12) := 'iexpyrbb.pls';
24: if G_REQUEST_ID <> -1 then
25: fnd_file.put_line(FND_FILE.LOG, p_msg);
26: end if;
27: */
28: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
29: iex_debug_pub.LogMessage(p_msg);
30: END IF;
31: END;
32:
39: l_result1 DATE;
40: l_callback_days NUMBER;
41: vSQL varchar2(500);
42: BEGIN
43: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
44: LogMessage(G_PKG_NAME || '.Get_Calback_Date: start');
45: END IF;
46:
47: l_callback_days := to_number(nvl(fnd_profile.value('IEX_PTP_CALLBACK_DAYS'), '0'));
44: LogMessage(G_PKG_NAME || '.Get_Calback_Date: start');
45: END IF;
46:
47: l_callback_days := to_number(nvl(fnd_profile.value('IEX_PTP_CALLBACK_DAYS'), '0'));
48: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
49: LogMessage(G_PKG_NAME || '.Get_Calback_Date: callback days from profile = ' || l_callback_days);
50: END IF;
51: if l_callback_days < 0 then
52: l_callback_days := 0;
51: if l_callback_days < 0 then
52: l_callback_days := 0;
53: end if;
54:
55: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
56: LogMessage(G_PKG_NAME || '.Get_Calback_Date: before cursor execute');
57: END IF;
58: vSQL := 'SELECT TO_NUMBER(TO_CHAR(:b + :a, ''D'')) FROM DUAL';
59:
61: vSQL
62: INTO l_result
63: using p_promise_date, l_callback_days;
64:
65: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
66: LogMessage(G_PKG_NAME || '.Get_Calback_Date: after cursor execute');
67: END IF;
68:
69: -- If Weekend => Monday
69: -- If Weekend => Monday
70: -- 6 => Firday
71: -- 1 => Sunday
72:
73: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
74: LogMessage(G_PKG_NAME || '.Get_Calback_Date: l_result = ' || l_result);
75: END IF;
76: if (l_result = 7) then
77: l_callback_days := l_callback_days + 2;
77: l_callback_days := l_callback_days + 2;
78: elsif (l_result = 1) then
79: l_callback_days := l_callback_days + 1;
80: end if;
81: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
82: LogMessage(G_PKG_NAME || '.Get_Calback_Date: l_callback_days = ' || l_callback_days);
83: END IF;
84:
85: vSQL := 'SELECT :b + :a FROM DUAL';
92: if trunc(sysdate) > trunc(x_callback_date) then
93: x_callback_date := sysdate;
94: end if;
95:
96: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
97: LogMessage(G_PKG_NAME || '.Get_CALLback_Date: x_callback_date = ' || x_callback_date);
98: END IF;
99:
100: EXCEPTION
98: END IF;
99:
100: EXCEPTION
101: WHEN OTHERS THEN
102: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
103: LogMessage(G_PKG_NAME || '.Get_CALLback_Date: in other execption');
104: END IF;
105: x_callback_date := sysdate;
106: -- Begin - Andre Araujo - 09/30/2004- Remove obsolete logging
152: PRD.STATUS = 'OPEN'
153: ORDER BY PRD.PROMISE_DETAIL_ID;
154:
155: BEGIN
156: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
157: iex_debug_pub.LogMessage(G_PKG_NAME || '.CLOSE_PROMISES: this procedure has been obsoleted - no actions have beed done.');
158: END IF;
159: X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
160:
164: We do not need to close or reopen promises if delinquency is closed or reopened - all this will be done by PROB.
165: We are obsoleting status CLOSED.
166:
167:
168: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
169: LogMessage('*************************');
170: LogMessage(G_PKG_NAME || '.' || l_api_name || ':Start');
171: END IF;
172:
172:
173: -- Standard start of API savepoint
174: SAVEPOINT CLOSE_PROMISES_PVT;
175:
176: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
177: LogMessage(G_PKG_NAME || '.' || l_api_name || ':Savepoint is established');
178: END IF;
179: -- Standard call to check for call compatibility
180: IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
189: -- Initialize API return status to success
190: l_return_status := FND_API.G_RET_STS_SUCCESS;
191:
192: -- START OF BODY OF API
193: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
194: LogMessage(G_PKG_NAME || '.' || l_api_name || ':Start of body');
195: END IF;
196:
197: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
193: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
194: LogMessage(G_PKG_NAME || '.' || l_api_name || ':Start of body');
195: END IF;
196:
197: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
198: LogMessage(G_PKG_NAME || '.' || l_api_name || ':Processing closed invoices');
199: END IF;
200: -- run thru table of payments and close promises
201: FOR i IN 1..p_payments_tbl.COUNT LOOP
198: LogMessage(G_PKG_NAME || '.' || l_api_name || ':Processing closed invoices');
199: END IF;
200: -- run thru table of payments and close promises
201: FOR i IN 1..p_payments_tbl.COUNT LOOP
202: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
203: LogMessage(G_PKG_NAME || '.' || l_api_name || ':record #' || i);
204: LogMessage(G_PKG_NAME || '.' || l_api_name || ':payment_schedule_id = ' || p_payments_tbl(i));
205: END IF;
206:
205: END IF;
206:
207: -- get open promises for the invoice
208: OPEN get_cl_pro_crs(P_PAYMENT_SCHEDULE_ID => p_payments_tbl(i));
209: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
210: LogMessage(G_PKG_NAME || '.' || l_api_name || ':get_cl_pro_crs cursor is opened');
211: END IF;
212:
213: LOOP
213: LOOP
214: FETCH get_cl_pro_crs INTO l_promise_detail_id;
215: EXIT WHEN get_cl_pro_crs%NOTFOUND;
216:
217: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
218: LogMessage(G_PKG_NAME || '.' || l_api_name || ':Promise found!');
219: LogMessage(G_PKG_NAME || '.' || l_api_name || ':l_promise_detail_id = ' || l_promise_detail_id);
220: END IF;
221:
218: LogMessage(G_PKG_NAME || '.' || l_api_name || ':Promise found!');
219: LogMessage(G_PKG_NAME || '.' || l_api_name || ':l_promise_detail_id = ' || l_promise_detail_id);
220: END IF;
221:
222: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
223: LogMessage(G_PKG_NAME || '.' || l_api_name || ':going to update promise ' || l_promise_detail_id || ' with status CLOSED');
224: END IF;
225: UPDATE iex_promise_details
226: SET STATUS = 'CLOSED',
228: last_updated_by = G_USER_ID
229: WHERE promise_detail_id = l_promise_detail_id;
230:
231: if (sql%notfound) then
232: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
233: LogMessage(G_PKG_NAME || '.' || l_api_name || ':update failed');
234: END IF;
235: -- Begin - Andre Araujo - 09/30/2004- Remove obsolete logging
236: /*
240: -- P_MESSAGE => 'Failed to update iex_promise_details with STATUS = CLOSED for promise_detail_id = ' || l_promise_detail_id);
241:
242: -- End - Andre Araujo - 09/30/2004- Remove obsolete logging
243: else
244: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
245: LogMessage(G_PKG_NAME || '.' || l_api_name || ':update successfull');
246: END IF;
247: end if;
248: END LOOP;
246: END IF;
247: end if;
248: END LOOP;
249: CLOSE get_cl_pro_crs;
250: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
251: LogMessage(G_PKG_NAME || '.' || l_api_name || ':get_cl_pro_crs cursor is closed');
252: END IF;
253: END LOOP;
254:
251: LogMessage(G_PKG_NAME || '.' || l_api_name || ':get_cl_pro_crs cursor is closed');
252: END IF;
253: END LOOP;
254:
255: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
256: LogMessage(G_PKG_NAME || '.' || l_api_name || ':End of body');
257: END IF;
258: -- END OF BODY OF API
259:
280: -- P_Procedure_name => G_PKG_NAME || '.' || l_api_name,
281: -- P_MESSAGE => 'Failed to close promises.' );
282: -- End - Andre Araujo - 09/30/2004- Remove obsolete logging
283:
284: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
285: LogMessage(G_PKG_NAME || '.' || l_api_name || ': In G_EXC_ERROR exception. Failed to close promises');
286: END IF;
287: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
288: ROLLBACK TO CLOSE_PROMISES_PVT;
293: -- P_Concurrent_Request_ID => FND_GLOBAL.CONC_REQUEST_ID,
294: -- P_Procedure_name => G_PKG_NAME || '.' || l_api_name,
295: -- P_MESSAGE => 'Failed to close promises.' );
296: -- End - Andre Araujo - 09/30/2004- Remove obsolete logging
297: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
298: LogMessage(G_PKG_NAME || '.' || l_api_name || ': In G_EXC_UNEXPECTED_ERROR exception. Failed to close promises');
299: END IF;
300: WHEN OTHERS THEN
301: ROLLBACK TO CLOSE_PROMISES_PVT;
309: -- P_Concurrent_Request_ID => FND_GLOBAL.CONC_REQUEST_ID,
310: -- P_Procedure_name => G_PKG_NAME || '.' || l_api_name,
311: -- P_MESSAGE => 'Failed to close promises.' );
312: -- End - Andre Araujo - 09/30/2004- Remove obsolete logging
313: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
314: LogMessage(G_PKG_NAME || '.' || l_api_name || ': In OTHERS exception. Failed to close promises');
315: END IF;
316: */
317:
354: L_BROKEN_PROMISE_IDS_TBL ids_table;
355:
356: BEGIN
357:
358: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
359: iex_debug_pub.LogMessage(G_PKG_NAME || '.CLOSE_PROMISES: this procedure has been obsoleted - no actions have beed done.');
360: END IF;
361: X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
362:
365: reverse payments from promises automatically.
366: We do not need to close or reopen promises if delinquency is closed or reopened - all this will be done by PROB.
367: We are obsoleting status CLOSED.
368:
369: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
370: iex_debug_pub.LogMessage(G_PKG_NAME || '.CLOSE_PROMISES: start');
371: END IF;
372: -- Standard start of API savepoint
373: SAVEPOINT CLOSE_PROMISES_PVT;
371: END IF;
372: -- Standard start of API savepoint
373: SAVEPOINT CLOSE_PROMISES_PVT;
374:
375: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
376: iex_debug_pub.LogMessage(G_PKG_NAME || '.CLOSE_PROMISES: Savepoint is established');
377: END IF;
378: -- Standard call to check for call compatibility
379: IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
388: -- Initialize API return status to success
389: l_return_status := FND_API.G_RET_STS_SUCCESS;
390:
391: -- START OF BODY OF API
392: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
393: iex_debug_pub.LogMessage(G_PKG_NAME || '.CLOSE_PROMISES: Start of body');
394: END IF;
395:
396: -- run thru table of payments and close promises
394: END IF;
395:
396: -- run thru table of payments and close promises
397: l_del_count := P_DELINQ_TBL.count;
398: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
399: iex_debug_pub.LogMessage(G_PKG_NAME || '.CLOSE_PROMISES: count of passed table of delinquencies = ' || l_del_count);
400: END IF;
401:
402: vSQL := 'SELECT ' ||
423: 'PRD.CNSLD_INVOICE_ID IS NULL and ' ||
424: 'PRD.STATUS IN (''OPEN'', ''PENDING'', ''BROKEN'') ' ||
425: 'ORDER BY PROMISE_DETAIL_ID';
426:
427: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
428: iex_debug_pub.LogMessage(G_PKG_NAME || '.CLOSE_PROMISES: processing promises...');
429: END IF;
430: FOR i in 1..l_del_count LOOP
431: open del_cur for vSQL
429: END IF;
430: FOR i in 1..l_del_count LOOP
431: open del_cur for vSQL
432: using p_delinq_tbl(i), p_delinq_tbl(i);
433: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
434: iex_debug_pub.LogMessage(G_PKG_NAME || '.CLOSE_PROMISES: record #' || i);
435: iex_debug_pub.LogMessage(G_PKG_NAME || '.CLOSE_PROMISES: delinquency: ' || p_delinq_tbl(i));
436: END IF;
437:
438: LOOP
439: fetch del_cur into l_promise_id, l_status, l_type;
440: exit when del_cur%NOTFOUND;
441:
442: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
443: iex_debug_pub.LogMessage(G_PKG_NAME || '.CLOSE_PROMISES: found promise with id: ' || l_promise_id);
444: iex_debug_pub.LogMessage(G_PKG_NAME || '.CLOSE_PROMISES: promise status: ' || l_status);
445: iex_debug_pub.LogMessage(G_PKG_NAME || '.CLOSE_PROMISES: promise type: ' || l_type);
446: END IF;
447:
448: if l_status = 'BROKEN' then
449: k := k + 1;
450: l_broken_promise_ids_tbl(k) := l_promise_id;
451: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
452: iex_debug_pub.LogMessage(G_PKG_NAME || '.CLOSE_PROMISES: added to broken promise table');
453: END IF;
454: else
455: j := j + 1;
453: END IF;
454: else
455: j := j + 1;
456: l_promise_ids_tbl(j) := l_promise_id;
457: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
458: iex_debug_pub.LogMessage(G_PKG_NAME || '.CLOSE_PROMISES: added to open/pending promise table');
459: END IF;
460: end if;
461: END LOOP;
462: END LOOP;
463:
464: l_cl_prd_count := l_promise_ids_tbl.count;
465: if l_cl_prd_count > 0 then
466: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
467: iex_debug_pub.LogMessage(G_PKG_NAME || '.CLOSE_PROMISES: updating open/pending promises...');
468: END IF;
469: FORALL n in 1..l_cl_prd_count
470: UPDATE iex_promise_details
475: end if;
476:
477: l_cl_prd_count := l_broken_promise_ids_tbl.count;
478: if l_cl_prd_count > 0 then
479: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
480: iex_debug_pub.LogMessage(G_PKG_NAME || '.CLOSE_PROMISES: updating broken promises...');
481: END IF;
482: FORALL n in 1..l_cl_prd_count
483: UPDATE iex_promise_details
487: last_updated_by = G_USER_ID
488: WHERE promise_detail_id = l_broken_promise_ids_tbl(n);
489: end if;
490:
491: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
492: iex_debug_pub.LogMessage(G_PKG_NAME || '.CLOSE_PROMISES: End of body');
493: END IF;
494: -- END OF BODY OF API
495:
506: p_data => x_msg_data);
507:
508: EXCEPTION
509: WHEN FND_API.G_EXC_ERROR THEN
510: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
511: iex_debug_pub.LogMessage(G_PKG_NAME || '.CLOSE_PROMISES: in FND_API.G_EXC_ERROR execption');
512: END IF;
513: ROLLBACK TO CLOSE_PROMISES_PVT;
514: x_return_status := FND_API.G_RET_STS_ERROR;
519: -- P_Procedure_name => 'IEX_PROMISES_BATCH_PUB.CLOSE_PROMISES',
520: -- P_MESSAGE => 'Failed to close promises.' );
521: -- End - Andre Araujo - 09/30/2004- Remove obsolete logging
522: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
523: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
524: iex_debug_pub.LogMessage(G_PKG_NAME || '.CLOSE_PROMISES: in FND_API.G_EXC_UNEXPECTED_ERROR execption');
525: END IF;
526: ROLLBACK TO CLOSE_PROMISES_PVT;
527: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
532: -- P_Procedure_name => 'IEX_PROMISES_BATCH_PUB.CLOSE_PROMISES',
533: -- P_MESSAGE => 'Failed to close promises.' );
534: -- End - Andre Araujo - 09/30/2004- Remove obsolete logging
535: WHEN OTHERS THEN
536: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
537: iex_debug_pub.LogMessage(G_PKG_NAME || '.CLOSE_PROMISES: in OTHERS execption');
538: END IF;
539: ROLLBACK TO CLOSE_PROMISES_PVT;
540: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
765: -- End bug#5874874 gnramasa 25-Apr-07
766: */
767: --End bug 6053792 gnramasa 17-May-2007
768: BEGIN
769: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
770: LogMessage('$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$');
771: LogMessage(G_PKG_NAME || '.' || l_api_name || ':Start');
772: END IF;
773:
774:
775: -- Standard start of API savepoint
776: SAVEPOINT PROCESS_ALL_PROMISES_PVT;
777:
778: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
779: LogMessage(G_PKG_NAME || '.' || l_api_name || ':Savepoint is established');
780: END IF;
781: -- Standard call to check for call compatibility
782: IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
791: -- Initialize API return status to success
792: l_return_status := FND_API.G_RET_STS_SUCCESS;
793:
794: -- START OF BODY OF API
795: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
796: LogMessage(G_PKG_NAME || '.' || l_api_name || ':Start of body');
797: END IF;
798:
799: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
795: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
796: LogMessage(G_PKG_NAME || '.' || l_api_name || ':Start of body');
797: END IF;
798:
799: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
800: LogMessage('********************************************');
801: LogMessage(G_PKG_NAME || '.' || l_api_name || ':Calling PROCESS_REVERSALS for AR ');
802: END IF;
803: PROCESS_REVERSALS(
809: X_MSG_COUNT => l_msg_count,
810: X_MSG_DATA => l_msg_data,
811: P_TYPE => 'AR');
812:
813: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
814: LogMessage('********************************************');
815: LogMessage(G_PKG_NAME || '.' || l_api_name || ':Calling PROCESS_REVERSALS for OKL ');
816: END IF;
817: PROCESS_REVERSALS(
823: X_MSG_COUNT => l_msg_count,
824: X_MSG_DATA => l_msg_data,
825: P_TYPE => 'OKL');
826:
827: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
828: LogMessage('********************************************');
829: LogMessage(G_PKG_NAME || '.' || l_api_name || ':Processing promises on AR invoices...');
830: END IF;
831: PROCESS_PROMISES(
837: X_MSG_COUNT => l_msg_count,
838: X_MSG_DATA => l_msg_data,
839: P_TYPE => 'INV');
840:
841: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
842: LogMessage('********************************************');
843: LogMessage(G_PKG_NAME || '.' || l_api_name || ':Processing promises on AR account...');
844: END IF;
845: PROCESS_PROMISES(
851: X_MSG_COUNT => l_msg_count,
852: X_MSG_DATA => l_msg_data,
853: P_TYPE => 'ACC');
854:
855: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
856: LogMessage('********************************************');
857: LogMessage(G_PKG_NAME || '.' || l_api_name || ':Processing promises on OKL contracts...');
858: END IF;
859: PROCESS_PROMISES(
865: X_MSG_COUNT => l_msg_count,
866: X_MSG_DATA => l_msg_data,
867: P_TYPE => 'CNTR');
868:
869: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
870: LogMessage(G_PKG_NAME || '.' || l_api_name || ':End of body');
871: END IF;
872: -- END OF BODY OF API
873:
873:
874: -- Standard check of p_commit.
875: IF FND_API.To_Boolean( p_commit ) THEN
876: COMMIT WORK;
877: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
878: LogMessage(G_PKG_NAME || '.' || l_api_name || ':Commited work');
879: END IF;
880: END IF;
881:
887: p_data => x_msg_data);
888:
889: -- Start bug#5874874 gnramasa 25-Apr-07
890: BEGIN
891: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
892: LogMessage(G_PKG_NAME || '.' || l_api_name || 'Started Updating IEX_DLN_UWQ_SUMMARY');
893: END IF;
894: -- Bug #6251572 bibeura 24-OCT-2007 modified the following Update statement
895: FOR I IN UPDATE_IEX_SUMMARY
905: END LOOP;
906:
907: COMMIT;
908:
909: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
910: LogMessage(G_PKG_NAME || '.' || l_api_name || 'Finished Updating IEX_DLN_UWQ_SUMMARY');
911: END IF;
912: EXCEPTION WHEN OTHERS THEN
913: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
909: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
910: LogMessage(G_PKG_NAME || '.' || l_api_name || 'Finished Updating IEX_DLN_UWQ_SUMMARY');
911: END IF;
912: EXCEPTION WHEN OTHERS THEN
913: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
914: LogMessage(G_PKG_NAME || '.' || l_api_name || 'Error Occurred while updating IEX_DLN_UWQ_SUMMARY ' || SQLERRM );
915: END IF;
916: END;
917:
915: END IF;
916: END;
917:
918: BEGIN
919: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
920: LogMessage(G_PKG_NAME || '.' || l_api_name || 'Started Updating IEX_DLN_UWQ_SUMMARY ACTIVE COLUMN');
921: END IF;
922: -- Bug #6251572 bibeura 24-OCT-2007 modified the following Update statement
923: FOR I IN UPDATE_IEX_ACTIVE_PRO
931: END LOOP;
932:
933: COMMIT;
934:
935: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
936: LogMessage(G_PKG_NAME || '.' || l_api_name || 'Finished Updating IEX_DLN_UWQ_SUMMARY ACTIVE COLUMN');
937: END IF;
938: EXCEPTION WHEN OTHERS THEN
939: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
935: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
936: LogMessage(G_PKG_NAME || '.' || l_api_name || 'Finished Updating IEX_DLN_UWQ_SUMMARY ACTIVE COLUMN');
937: END IF;
938: EXCEPTION WHEN OTHERS THEN
939: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
940: LogMessage(G_PKG_NAME || '.' || l_api_name || 'Error Occurred while updating IEX_DLN_UWQ_SUMMARY ACTIVE' || SQLERRM );
941: END IF;
942: END;
943:
941: END IF;
942: END;
943:
944: BEGIN
945: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
946: LogMessage(G_PKG_NAME || '.' || l_api_name || 'Started Updating IEX_DLN_UWQ_SUMMARY COMPLETED COLUMN');
947: END IF;
948: -- Bug #6251572 bibeura 24-OCT-2007 modified the following Update statement
949: FOR I IN UPDATE_IEX_COMP_PRO
957: END LOOP;
958:
959: COMMIT;
960:
961: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
962: LogMessage(G_PKG_NAME || '.' || l_api_name || 'Finished Updating IEX_DLN_UWQ_SUMMARY COMPLETED COLUMN');
963: END IF;
964: EXCEPTION WHEN OTHERS THEN
965: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
961: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
962: LogMessage(G_PKG_NAME || '.' || l_api_name || 'Finished Updating IEX_DLN_UWQ_SUMMARY COMPLETED COLUMN');
963: END IF;
964: EXCEPTION WHEN OTHERS THEN
965: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
966: LogMessage(G_PKG_NAME || '.' || l_api_name ||
967: 'Error Occurred while updating IEX_DLN_UWQ_SUMMARY COMPLETED COLUMN' || SQLERRM );
968: END IF;
969: END;
968: END IF;
969: END;
970:
971: BEGIN
972: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
973: LogMessage(G_PKG_NAME || '.' || l_api_name || 'Started Updating IEX_DLN_UWQ_SUMMARY PENDING COLUMN');
974: END IF;
975: -- Bug #6251572 bibeura 24-OCT-2007 modified the following Update statement
976: FOR I IN UPDATE_IEX_PEND_PRO
984: END LOOP;
985:
986: COMMIT;
987:
988: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
989: LogMessage(G_PKG_NAME || '.' || l_api_name || ' Finished Updating IEX_DLN_UWQ_SUMMARY PENDING COLUMN');
990: END IF;
991: EXCEPTION WHEN OTHERS THEN
992: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
988: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
989: LogMessage(G_PKG_NAME || '.' || l_api_name || ' Finished Updating IEX_DLN_UWQ_SUMMARY PENDING COLUMN');
990: END IF;
991: EXCEPTION WHEN OTHERS THEN
992: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
993: LogMessage(G_PKG_NAME || '.' || l_api_name ||
994: 'Error Occurred while updating IEX_DLN_UWQ_SUMMARY PENDING COLUMN ' || SQLERRM );
995: END IF;
996: END;
999:
1000:
1001: --start
1002: BEGIN
1003: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1004: LogMessage(G_PKG_NAME || '.' || l_api_name || 'Started Updating IEX_DELINQUENCY_ALL STAGED_DUNNING_LEVEL COLUMN');
1005: END IF;
1006:
1007: /*
1012: , p_stage_number => l_stage_number);
1013: update iex_delinquencies_all
1014: set staged_dunning_level = l_stage_number
1015: where delinquency_id = I.delinquency_id;
1016: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1017: LogMessage(G_PKG_NAME || '.' || l_api_name || 'Updated the staged dunning level to ' || l_stage_number || ' for delinquency id: ' || I.delinquency_id);
1018: end if;
1019:
1020: END LOOP;
1024:
1025: update iex_delinquencies_all
1026: set staged_dunning_level = 0
1027: where delinquency_id = J.delinquency_id;
1028: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1029: LogMessage(G_PKG_NAME || '.' || l_api_name || 'Updated the staged dunning level to 0 for delinquency id: ' || J.delinquency_id);
1030: end if;
1031:
1032: END LOOP;
1034:
1035: FOR I IN UPDATE_PRO_DEL
1036: LOOP
1037:
1038: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1039: LogMessage(G_PKG_NAME || '.' || l_api_name || ': Calling IEX_PROMISES_PUB.update_del_stage_level');
1040: END IF;
1041: IEX_PROMISES_PUB.update_del_stage_level (
1042: p_promise_id => I.promise_detail_id,
1043: X_RETURN_STATUS => l_return_status,
1044: X_MSG_COUNT => l_msg_count,
1045: X_MSG_DATA => l_msg_data);
1046:
1047: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1048: LogMessage(G_PKG_NAME || '.' || l_api_name || ': After call to IEX_PROMISES_PUB.update_del_stage_level');
1049: LogMessage(G_PKG_NAME || '.' || l_api_name || ': Status = ' || l_return_status);
1050: END IF;
1051:
1050: END IF;
1051:
1052: -- check for errors
1053: IF l_return_status<>FND_API.G_RET_STS_SUCCESS THEN
1054: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1055: LogMessage(G_PKG_NAME || '.' || l_api_name || ': IEX_PROMISES_PUB.update_del_stage_level failed');
1056: END IF;
1057: RAISE FND_API.G_EXC_ERROR;
1058: END IF;
1060: END LOOP;
1061:
1062: COMMIT;
1063:
1064: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1065: LogMessage(G_PKG_NAME || '.' || l_api_name || ' Finished Updating IEX_DELINQUENCY_ALL STAGED_DUNNING_LEVEL COLUMN');
1066: END IF;
1067: EXCEPTION WHEN OTHERS THEN
1068: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1064: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1065: LogMessage(G_PKG_NAME || '.' || l_api_name || ' Finished Updating IEX_DELINQUENCY_ALL STAGED_DUNNING_LEVEL COLUMN');
1066: END IF;
1067: EXCEPTION WHEN OTHERS THEN
1068: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1069: LogMessage(G_PKG_NAME || '.' || l_api_name ||
1070: 'Error Occurred while updating IEX_DELINQUENCY_ALL STAGED_DUNNING_LEVEL COLUMN ' || SQLERRM );
1071: END IF;
1072: END;
1084: P_Procedure_name => G_PKG_NAME || '.' || l_api_name,
1085: P_MESSAGE => 'Failed to process all promises');
1086: */
1087: -- End - Andre Araujo - 09/30/2004- Remove obsolete logging
1088: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1089: LogMessage(G_PKG_NAME || '.' || l_api_name || ': In G_EXC_ERROR exception. Failed to process all promises');
1090: END IF;
1091: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1092: ROLLBACK TO PROCESS_ALL_PROMISES_PVT;
1099: P_Procedure_name => G_PKG_NAME || '.' || l_api_name,
1100: P_MESSAGE => 'Failed to process all promises');
1101: */
1102: -- End - Andre Araujo - 09/30/2004- Remove obsolete logging
1103: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1104: LogMessage(G_PKG_NAME || '.' || l_api_name || ': In G_EXC_UNEXPECTED_ERROR exception. Failed to process all promises');
1105: END IF;
1106: WHEN OTHERS THEN
1107: ROLLBACK TO PROCESS_ALL_PROMISES_PVT;
1117: P_Procedure_name => G_PKG_NAME || '.' || l_api_name,
1118: P_MESSAGE => 'Failed to process all promises');
1119: */
1120: -- End - Andre Araujo - 09/30/2004- Remove obsolete logging
1121: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1122: LogMessage(G_PKG_NAME || '.' || l_api_name || ': In OTHERS exception. Failed to process all promises');
1123: END IF;
1124: END;
1125:
1158: l_callback_date DATE;
1159: l_new_remaining_amount NUMBER;
1160:
1161: BEGIN
1162: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1163: LogMessage(G_PKG_NAME || '.' || l_api_name || ':Start');
1164: END IF;
1165:
1166:
1167:
1168: -- Standard start of API savepoint
1169: SAVEPOINT PROCESS_REVERSALS_PVT;
1170:
1171: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1172: LogMessage(G_PKG_NAME || '.' || l_api_name || ':Savepoint is established');
1173: END IF;
1174: -- Standard call to check for call compatibility
1175: IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1184: -- Initialize API return status to success
1185: l_return_status := FND_API.G_RET_STS_SUCCESS;
1186:
1187: -- START OF BODY OF API
1188: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1189: LogMessage(G_PKG_NAME || '.' || l_api_name || ':Start of body');
1190: END IF;
1191:
1192: if P_TYPE = 'AR' then
1189: LogMessage(G_PKG_NAME || '.' || l_api_name || ':Start of body');
1190: END IF;
1191:
1192: if P_TYPE = 'AR' then
1193: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1194: LogMessage(G_PKG_NAME || '.' || l_api_name || ':Searching for applications that still applied to promises but reversed in AR ');
1195: END IF;
1196: -- search for all applications that still applied to promises but reversed in AR
1197: vSQL := 'SELECT ' ||
1219: 'prd.org_id = raa.org_id ' || --Added for bug 7237026 barathsr 31-Dec-2008
1220: 'ORDER BY raa.receivable_application_id';
1221:
1222: else
1223: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1224: LogMessage(G_PKG_NAME || '.' || l_api_name || ':Searching for applications that still applied to promises but reversed in OKL ');
1225: END IF;
1226: -- search for all applications that still applied to promises but reversed in OKL
1227: /* replaced the statement just below to fix a perf bug 4930383
1295: l_appl_tbl(i).receivable_application_id := l_receivable_application_id;
1296:
1297:
1298:
1299: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1300: LogMessage(G_PKG_NAME || '.' || l_api_name || '------------------------');
1301: LogMessage(G_PKG_NAME || '.' || l_api_name || ':Reversed record ' || i);
1302: LogMessage(G_PKG_NAME || '.' || l_api_name || ':promise_detail_id = ' || l_appl_tbl(i).promise_detail_id);
1303: LogMessage(G_PKG_NAME || '.' || l_api_name || ':promise_date = ' || l_appl_tbl(i).promise_date);
1309: END IF;
1310: END LOOP;
1311:
1312: nCount := l_appl_tbl.count;
1313: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1314: LogMessage(G_PKG_NAME || '.' || l_api_name || ':Total count of found reversed applications = ' || nCount);
1315: END IF;
1316: if nCount > 0 then
1317:
1315: END IF;
1316: if nCount > 0 then
1317:
1318: FOR i in 1..nCount LOOP
1319: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1320: LogMessage(G_PKG_NAME || '.' || l_api_name || '------------------------');
1321: LogMessage(G_PKG_NAME || '.' || l_api_name || ':Reversing record ' || i);
1322: END IF;
1323:
1320: LogMessage(G_PKG_NAME || '.' || l_api_name || '------------------------');
1321: LogMessage(G_PKG_NAME || '.' || l_api_name || ':Reversing record ' || i);
1322: END IF;
1323:
1324: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1325: LogMessage(G_PKG_NAME || '.' || l_api_name || ':Analizing what status to set for the promise...');
1326: LogMessage(G_PKG_NAME || '.' || l_api_name || ':current promise status ' || l_appl_tbl(i).status);
1327: END IF;
1328: if l_appl_tbl(i).status = 'FULFILLED' then -- it can effect only to FULFILLED records
1326: LogMessage(G_PKG_NAME || '.' || l_api_name || ':current promise status ' || l_appl_tbl(i).status);
1327: END IF;
1328: if l_appl_tbl(i).status = 'FULFILLED' then -- it can effect only to FULFILLED records
1329: l_new_status := 'COLLECTABLE';
1330: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1331: LogMessage(G_PKG_NAME || '.' || l_api_name || ':the promise status after reversing will be ' || l_new_status);
1332: END IF;
1333: else
1334: l_new_status := null;
1331: LogMessage(G_PKG_NAME || '.' || l_api_name || ':the promise status after reversing will be ' || l_new_status);
1332: END IF;
1333: else
1334: l_new_status := null;
1335: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1336: LogMessage(G_PKG_NAME || '.' || l_api_name || ':will leave this status');
1337: END IF;
1338: end if;
1339:
1336: LogMessage(G_PKG_NAME || '.' || l_api_name || ':will leave this status');
1337: END IF;
1338: end if;
1339:
1340: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1341: LogMessage(G_PKG_NAME || '.' || l_api_name || ':updating records in IEX_prd_appl_xref with reversed_flag = Y...');
1342: END IF;
1343:
1344: update IEX_prd_appl_xref
1351: receivable_application_id = l_appl_tbl(i).receivable_application_id and
1352: promise_detail_id = l_appl_tbl(i).promise_detail_id;
1353:
1354: if (sql%notfound) then
1355: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1356: LogMessage(G_PKG_NAME || '.' || l_api_name || ':update failed');
1357: END IF;
1358: -- Begin - Andre Araujo - 09/30/2004- Remove obsolete logging
1359: /*
1363: P_MESSAGE => 'Failed to update record in IEX_prd_appl_xref for promise_detail_id = ' || l_appl_tbl(i).promise_detail_id);
1364: */
1365: -- End - Andre Araujo - 09/30/2004- Remove obsolete logging
1366: else
1367: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1368: LogMessage(G_PKG_NAME || '.' || l_api_name || ':update successfull');
1369: END IF;
1370: end if;
1371:
1369: END IF;
1370: end if;
1371:
1372: l_new_remaining_amount := l_appl_tbl(i).amount_due_remaining + l_appl_tbl(i).amount_applied;
1373: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1374: LogMessage(G_PKG_NAME || '.' || l_api_name || ':updating record in IEX_PROMISE_DETAILS with:');
1375: LogMessage(G_PKG_NAME || '.' || l_api_name || ':amount_due_remaining = ' || l_new_remaining_amount);
1376: END IF;
1377:
1375: LogMessage(G_PKG_NAME || '.' || l_api_name || ':amount_due_remaining = ' || l_new_remaining_amount);
1376: END IF;
1377:
1378: if l_new_status is not null then
1379: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1380: LogMessage(G_PKG_NAME || '.' || l_api_name || ':status = ' || l_new_status);
1381: END IF;
1382: update IEX_PROMISE_DETAILS
1383: set status = l_new_status,
1393: where promise_detail_id = l_appl_tbl(i).promise_detail_id;
1394: end if;
1395:
1396: if (sql%notfound) then
1397: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1398: LogMessage(G_PKG_NAME || '.' || l_api_name || ':update failed');
1399: END IF;
1400: -- Begin - Andre Araujo - 09/30/2004- Remove obsolete logging
1401: /*
1405: P_MESSAGE => 'Failed to update record in IEX_PROMISE_DETAILS for promise_detail_id = ' || l_appl_tbl(i).promise_detail_id);
1406: */
1407: -- End - Andre Araujo - 09/30/2004- Remove obsolete logging
1408: else
1409: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1410: LogMessage(G_PKG_NAME || '.' || l_api_name || ':update successfull');
1411: END IF;
1412:
1413: /*
1411: END IF;
1412:
1413: /*
1414: --start
1415: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1416: iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Calling IEX_PROMISES_PUB.update_del_stage_level');
1417: END IF;
1418: IEX_PROMISES_PUB.update_del_stage_level (
1419: p_promise_id => l_appl_tbl(i).promise_detail_id,
1420: X_RETURN_STATUS => l_return_status,
1421: X_MSG_COUNT => l_msg_count,
1422: X_MSG_DATA => l_msg_data);
1423:
1424: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1425: iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': After call to IEX_PROMISES_PUB.update_del_stage_level');
1426: iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Status = ' || L_RETURN_STATUS);
1427: END IF;
1428:
1427: END IF;
1428:
1429: -- check for errors
1430: IF l_return_status<>FND_API.G_RET_STS_SUCCESS THEN
1431: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1432: iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': IEX_PROMISES_PUB.update_del_stage_level failed');
1433: END IF;
1434: RAISE FND_API.G_EXC_ERROR;
1435: END IF;
1438: end if;
1439:
1440: END LOOP;
1441: else
1442: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1443: LogMessage(G_PKG_NAME || '.' || l_api_name || ':no reversed applications found');
1444: END IF;
1445: end if;
1446:
1443: LogMessage(G_PKG_NAME || '.' || l_api_name || ':no reversed applications found');
1444: END IF;
1445: end if;
1446:
1447: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1448: LogMessage(G_PKG_NAME || '.' || l_api_name || ':End of body');
1449: END IF;
1450: -- END OF BODY OF API
1451:
1451:
1452: -- Standard check of p_commit.
1453: IF FND_API.To_Boolean( p_commit ) THEN
1454: COMMIT WORK;
1455: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1456: LogMessage(G_PKG_NAME || '.' || l_api_name || ':Commited work');
1457: END IF;
1458: END IF;
1459:
1477: P_Procedure_name => G_PKG_NAME || '.' || l_api_name,
1478: P_MESSAGE => 'Failed to reverse promise applications');
1479: */
1480: -- End - Andre Araujo - 09/30/2004- Remove obsolete logging
1481: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1482: LogMessage(G_PKG_NAME || '.' || l_api_name || ': In G_EXC_ERROR exception. Failed to reverse promise applications');
1483: END IF;
1484: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1485: ROLLBACK TO PROCESS_REVERSALS_PVT;
1492: P_Procedure_name => G_PKG_NAME || '.' || l_api_name,
1493: P_MESSAGE => 'Failed to reverse promise applications');
1494: */
1495: -- End - Andre Araujo - 09/30/2004- Remove obsolete logging
1496: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1497: LogMessage(G_PKG_NAME || '.' || l_api_name || ': In G_EXC_UNEXPECTED_ERROR exception. Failed to reverse promise applications');
1498: END IF;
1499: WHEN OTHERS THEN
1500: ROLLBACK TO PROCESS_REVERSALS_PVT;
1509: P_Procedure_name => G_PKG_NAME || '.' || l_api_name,
1510: P_MESSAGE => 'Failed to reverse promise applications');
1511: */
1512: -- End - Andre Araujo - 09/30/2004- Remove obsolete logging
1513: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1514: LogMessage(G_PKG_NAME || '.' || l_api_name || ': In OTHERS exception. Failed to reverse promise applications');
1515: END IF;
1516: END;
1517:
1552: l_CUST_ACCOUNT_ID NUMBER;
1553: l_CONTRACT_ID NUMBER;
1554:
1555: BEGIN
1556: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1557: LogMessage(G_PKG_NAME || '.' || l_api_name || ':Start');
1558: END IF;
1559:
1560:
1560:
1561: -- Standard start of API savepoint
1562: SAVEPOINT PROCESS_PROMISES_PVT;
1563:
1564: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1565: LogMessage(G_PKG_NAME || '.' || l_api_name || ':Savepoint is established');
1566: END IF;
1567: -- Standard call to check for call compatibility
1568: IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1577: -- Initialize API return status to success
1578: l_return_status := FND_API.G_RET_STS_SUCCESS;
1579:
1580: -- START OF BODY OF API
1581: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1582: LogMessage(G_PKG_NAME || '.' || l_api_name || ':Start of body');
1583: END IF;
1584:
1585: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1581: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1582: LogMessage(G_PKG_NAME || '.' || l_api_name || ':Start of body');
1583: END IF;
1584:
1585: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1586: LogMessage(G_PKG_NAME || '.' || l_api_name || ':searching for all available valid promises...');
1587: END IF;
1588:
1589: if P_TYPE = 'INV' then -- processing all promises on invoices
1633: l_pro_tbl(y).AMOUNT_DUE_REMAINING := l_AMOUNT_DUE_REMAINING;
1634: l_pro_tbl(y).DELINQUENCY_ID := l_DELINQUENCY_ID;
1635: l_pro_tbl(y).PAYMENT_SCHEDULE_ID := l_PAYMENT_SCHEDULE_ID;
1636:
1637: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1638: LogMessage(G_PKG_NAME || '.' || l_api_name || '------------------------');
1639: LogMessage(G_PKG_NAME || '.' || l_api_name || ':found promise ' || y);
1640: LogMessage(G_PKG_NAME || '.' || l_api_name || ':PROMISE_DETAIL_ID = ' || l_pro_tbl(y).PROMISE_DETAIL_ID);
1641: END IF;
1642:
1643: END LOOP;
1644:
1645: nCount := l_pro_tbl.count;
1646: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1647: LogMessage(G_PKG_NAME || '.' || l_api_name || ':Total count of found promises = ' || nCount);
1648: END IF;
1649:
1650: if nCount > 0 then
1647: LogMessage(G_PKG_NAME || '.' || l_api_name || ':Total count of found promises = ' || nCount);
1648: END IF;
1649:
1650: if nCount > 0 then
1651: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1652: LogMessage(G_PKG_NAME || '.' || l_api_name || ':Doing FIFO promise applications...');
1653: END IF;
1654: APPLY_PROMISES_FIFO(
1655: P_API_VERSION => 1.0,
1661: X_MSG_DATA => l_msg_data,
1662: P_PROMISES_TBL => l_pro_tbl,
1663: P_TYPE => 'INV');
1664: else
1665: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1666: LogMessage(G_PKG_NAME || '.' || l_api_name || ':no promises found - do not call FIFO');
1667: END IF;
1668: end if;
1669:
1666: LogMessage(G_PKG_NAME || '.' || l_api_name || ':no promises found - do not call FIFO');
1667: END IF;
1668: end if;
1669:
1670: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1671: LogMessage(G_PKG_NAME || '.' || l_api_name || ':Updating all COLLECTABLE and PENDING promises for delinquencies that have status CURRENT to status FULFILLED ...');
1672: END IF;
1673:
1674: UPDATE iex_promise_details
1687: del.status = 'CURRENT' and
1688: aps.amount_due_remaining = 0);--added for Bug 6446848 08-Dec-2008 barathsr
1689:
1690:
1691: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1692: LogMessage(G_PKG_NAME || '.' || l_api_name || ': ' || sql%rowcount || ' rows is updated');
1693: END IF;
1694:
1695: elsif P_TYPE = 'ACC' then -- processing all promises on account
1737: l_pro_tbl(y).PROMISE_AMOUNT := l_PROMISE_AMOUNT;
1738: l_pro_tbl(y).AMOUNT_DUE_REMAINING := l_AMOUNT_DUE_REMAINING;
1739: l_pro_tbl(y).CUST_ACCOUNT_ID := l_CUST_ACCOUNT_ID;
1740:
1741: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1742: LogMessage(G_PKG_NAME || '.' || l_api_name || '------------------------');
1743: LogMessage(G_PKG_NAME || '.' || l_api_name || ':found promise ' || y);
1744: LogMessage(G_PKG_NAME || '.' || l_api_name || ':PROMISE_DETAIL_ID = ' || l_pro_tbl(y).PROMISE_DETAIL_ID);
1745: END IF;
1746:
1747: END LOOP;
1748:
1749: nCount := l_pro_tbl.count;
1750: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1751: LogMessage(G_PKG_NAME || '.' || l_api_name || ':Total count of found promises = ' || nCount);
1752: END IF;
1753:
1754: if nCount > 0 then
1751: LogMessage(G_PKG_NAME || '.' || l_api_name || ':Total count of found promises = ' || nCount);
1752: END IF;
1753:
1754: if nCount > 0 then
1755: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1756: LogMessage(G_PKG_NAME || '.' || l_api_name || ':Doing FIFO promise applications...');
1757: END IF;
1758: APPLY_PROMISES_FIFO(
1759: P_API_VERSION => 1.0,
1765: X_MSG_DATA => l_msg_data,
1766: P_PROMISES_TBL => l_pro_tbl,
1767: P_TYPE => 'ACC');
1768: else
1769: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1770: LogMessage(G_PKG_NAME || '.' || l_api_name || ':no promises found - do not call FIFO');
1771: END IF;
1772: end if;
1773:
1815: l_pro_tbl(y).AMOUNT_DUE_REMAINING := l_AMOUNT_DUE_REMAINING;
1816: l_pro_tbl(y).CONTRACT_ID := l_CONTRACT_ID;
1817:
1818:
1819: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1820: LogMessage(G_PKG_NAME || '.' || l_api_name || '------------------------');
1821: LogMessage(G_PKG_NAME || '.' || l_api_name || ':found promise ' || y);
1822: LogMessage(G_PKG_NAME || '.' || l_api_name || ':PROMISE_DETAIL_ID = ' || l_pro_tbl(y).PROMISE_DETAIL_ID);
1823: END IF;
1824:
1825: END LOOP;
1826:
1827: nCount := l_pro_tbl.count;
1828: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1829: LogMessage(G_PKG_NAME || '.' || l_api_name || ':Total count of found promises = ' || nCount);
1830: END IF;
1831:
1832: if nCount > 0 then
1829: LogMessage(G_PKG_NAME || '.' || l_api_name || ':Total count of found promises = ' || nCount);
1830: END IF;
1831:
1832: if nCount > 0 then
1833: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1834: LogMessage(G_PKG_NAME || '.' || l_api_name || ':Doing FIFO promise applications...');
1835: END IF;
1836: APPLY_PROMISES_FIFO(
1837: P_API_VERSION => 1.0,
1843: X_MSG_DATA => l_msg_data,
1844: P_PROMISES_TBL => l_pro_tbl,
1845: P_TYPE => 'CNTR');
1846: else
1847: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1848: LogMessage(G_PKG_NAME || '.' || l_api_name || ':no promises found - do not call FIFO');
1849: END IF;
1850: end if;
1851:
1850: end if;
1851:
1852: end if;
1853:
1854: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1855: LogMessage(G_PKG_NAME || '.' || l_api_name || ':End of body');
1856: END IF;
1857: -- END OF BODY OF API
1858:
1858:
1859: -- Standard check of p_commit.
1860: IF FND_API.To_Boolean( p_commit ) THEN
1861: COMMIT WORK;
1862: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1863: LogMessage(G_PKG_NAME || '.' || l_api_name || ':Commited work');
1864: END IF;
1865: END IF;
1866:
1883: P_Procedure_name => G_PKG_NAME || '.' || l_api_name,
1884: P_MESSAGE => 'Failed to process promises');
1885: */
1886: -- End - Andre Araujo - 09/30/2004- Remove obsolete logging
1887: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1888: LogMessage(G_PKG_NAME || '.' || l_api_name || ': In G_EXC_ERROR exception. Failed to process promises');
1889: END IF;
1890: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1891: ROLLBACK TO PROCESS_PROMISES_PVT;
1898: P_Procedure_name => G_PKG_NAME || '.' || l_api_name,
1899: P_MESSAGE => 'Failed to process promises');
1900: */
1901: -- End - Andre Araujo - 09/30/2004- Remove obsolete logging
1902: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1903: LogMessage(G_PKG_NAME || '.' || l_api_name || ': In G_EXC_UNEXPECTED_ERROR exception. Failed to process promises');
1904: END IF;
1905: WHEN OTHERS THEN
1906: ROLLBACK TO PROCESS_PROMISES_PVT;
1916: P_Procedure_name => G_PKG_NAME || '.' || l_api_name,
1917: P_MESSAGE => 'Failed to process promises');
1918: */
1919: -- End - Andre Araujo - 09/30/2004- Remove obsolete logging
1920: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1921: LogMessage(G_PKG_NAME || '.' || l_api_name || ': In OTHERS exception. Failed to process promises');
1922: END IF;
1923: END;
1924:
1969: l_applied_appl_count NUMBER;
1970:
1971:
1972: BEGIN
1973: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1974: LogMessage(G_PKG_NAME || '.' || l_api_name || ':Start');
1975: END IF;
1976:
1977: -- Standard start of API savepoint
1976:
1977: -- Standard start of API savepoint
1978: SAVEPOINT APPLY_PROMISES_FIFO_PVT;
1979:
1980: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1981: LogMessage(G_PKG_NAME || '.' || l_api_name || ':Savepoint is established');
1982: END IF;
1983: -- Standard call to check for call compatibility
1984: IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1993: -- Initialize API return status to success
1994: l_return_status := FND_API.G_RET_STS_SUCCESS;
1995:
1996: -- START OF BODY OF API
1997: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1998: LogMessage(G_PKG_NAME || '.' || l_api_name || ':Start of body');
1999: END IF;
2000:
2001: nCount := P_PROMISES_TBL.count;
1998: LogMessage(G_PKG_NAME || '.' || l_api_name || ':Start of body');
1999: END IF;
2000:
2001: nCount := P_PROMISES_TBL.count;
2002: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2003: LogMessage(G_PKG_NAME || '.' || l_api_name || ':Total count of passed promises = ' || nCount);
2004: END IF;
2005:
2006: FOR i in 1..nCount LOOP
2003: LogMessage(G_PKG_NAME || '.' || l_api_name || ':Total count of passed promises = ' || nCount);
2004: END IF;
2005:
2006: FOR i in 1..nCount LOOP
2007: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2008: LogMessage(G_PKG_NAME || '.' || l_api_name || ':---------------------------');
2009: LogMessage(G_PKG_NAME || '.' || l_api_name || ':Promise ' || i || ' details:');
2010: LogMessage(G_PKG_NAME || '.' || l_api_name || ':PROMISE_DETAIL_ID = ' || P_PROMISES_TBL(i).PROMISE_DETAIL_ID);
2011: LogMessage(G_PKG_NAME || '.' || l_api_name || ':CREATION_DATE = ' || P_PROMISES_TBL(i).CREATION_DATE);
2016: LogMessage(G_PKG_NAME || '.' || l_api_name || ':STATE = ' || P_PROMISES_TBL(i).STATE);
2017: END IF;
2018:
2019: if P_TYPE = 'INV' then
2020: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2021: LogMessage(G_PKG_NAME || '.' || l_api_name || ':DELINQUENCY_ID = ' || P_PROMISES_TBL(i).DELINQUENCY_ID);
2022: LogMessage(G_PKG_NAME || '.' || l_api_name || ':PAYMENT_SCHEDULE_ID = ' || P_PROMISES_TBL(i).PAYMENT_SCHEDULE_ID);
2023: END IF;
2024: -- start bug 3635087 gnramasa 10/07/07
2106: WHERE PAYMENT_SCHEDULE_ID = P_PROMISES_TBL(i).PAYMENT_SCHEDULE_ID;
2107:
2108: IF l_adjustment_count = 0 THEN
2109: vSQL := vSQL_pay_only;
2110: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2111: LogMessage(G_PKG_NAME || '.' || l_api_name || 'No adjustment exist for this invoice, vSQL := vSQL_pay_only' );
2112: END IF;
2113: ELSE
2114: vSQL := vSQL_pay_adj;
2111: LogMessage(G_PKG_NAME || '.' || l_api_name || 'No adjustment exist for this invoice, vSQL := vSQL_pay_only' );
2112: END IF;
2113: ELSE
2114: vSQL := vSQL_pay_adj;
2115: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2116: LogMessage(G_PKG_NAME || '.' || l_api_name || 'Adjustments exist for this invoice, vSQL := vSQL_pay_adj' );
2117: END IF;
2118: END IF;
2119:
2117: END IF;
2118: END IF;
2119:
2120: elsif P_TYPE = 'ACC' then
2121: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2122: LogMessage(G_PKG_NAME || '.' || l_api_name || ':CUST_ACCOUNT_ID = ' || P_PROMISES_TBL(i).CUST_ACCOUNT_ID);
2123: END IF;
2124: vSQL := 'select ' ||
2125: 'raa.receivable_application_id, ' ||
2149: 'group by raa.receivable_application_id, raa.amount_applied, raa.apply_date ' ||
2150: 'order by raa.receivable_application_id';
2151:
2152: elsif P_TYPE = 'CNTR' then
2153: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2154: LogMessage(G_PKG_NAME || '.' || l_api_name || ':CONTRACT_ID = ' || P_PROMISES_TBL(i).CONTRACT_ID);
2155: END IF;
2156: /* replaced the statement just below to fix a perf bug 4930383
2157: vSQL := 'select ' ||
2206: ' order by ARAPP.receivable_application_id ';
2207:
2208: end if;
2209:
2210: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2211: LogMessage(G_PKG_NAME || '.' || l_api_name || ':Searching for applications to apply to the promise ' || P_PROMISES_TBL(i).PROMISE_DETAIL_ID);
2212: END IF;
2213:
2214: if P_TYPE = 'INV' then
2264: l_appl_tbl(y).ar_adjusted_amount := l_adjusted_amount;
2265: l_appl_tbl(y).ar_adj_remaining_amount := l_adj_remaining_amount;
2266: l_appl_tbl(y).ar_adjusted_date := l_adjusted_date;
2267:
2268: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2269: IF l_appl_tbl(y).receivable_application_id IS NOT NULL THEN
2270: LogMessage(G_PKG_NAME || '.' || l_api_name || ':found receivable_application_id = ' || l_appl_tbl(y).receivable_application_id);
2271: LogMessage(G_PKG_NAME || '.' || l_api_name || ':ar_applied_amount = ' || l_appl_tbl(y).ar_applied_amount);
2272: LogMessage(G_PKG_NAME || '.' || l_api_name || ':ar_remaining_amount = ' || l_appl_tbl(y).ar_remaining_amount);
2282:
2283: END LOOP;
2284:
2285: nCount1 := l_appl_tbl.count;
2286: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2287: LogMessage(G_PKG_NAME || '.' || l_api_name || ':Total found ' || nCount1 || ' available applications');
2288: END IF;
2289:
2290: if nCount1 > 0 then -- do applications
2287: LogMessage(G_PKG_NAME || '.' || l_api_name || ':Total found ' || nCount1 || ' available applications');
2288: END IF;
2289:
2290: if nCount1 > 0 then -- do applications
2291: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2292: LogMessage(G_PKG_NAME || '.' || l_api_name || ':applying...');
2293: END IF;
2294:
2295: FOR y in 1..nCount1 LOOP
2293: END IF;
2294:
2295: FOR y in 1..nCount1 LOOP
2296:
2297: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2298: LogMessage(G_PKG_NAME || '.' || l_api_name || ':processing application ' || y || ' Details:');
2299: LogMessage(G_PKG_NAME || '.' || l_api_name || ':receivable_application_id = ' || l_appl_tbl(y).receivable_application_id);
2300: LogMessage(G_PKG_NAME || '.' || l_api_name || ':ar_applied_amount = ' || l_appl_tbl(y).ar_applied_amount);
2301: LogMessage(G_PKG_NAME || '.' || l_api_name || ':ar_remaining_amount = ' || l_appl_tbl(y).ar_remaining_amount);
2306: LogMessage(G_PKG_NAME || '.' || l_api_name || ':ar_adjusted_date = ' || l_appl_tbl(y).ar_adjusted_date);
2307: LogMessage(G_PKG_NAME || '.' || l_api_name || ':pro_applied_amount = ' || l_appl_tbl(y).pro_applied_amount);
2308: END IF;
2309:
2310: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2311: LogMessage(G_PKG_NAME || '.' || l_api_name || ':comparing application remaining amount = ' || l_appl_tbl(y).ar_remaining_amount);
2312: LogMessage(G_PKG_NAME || '.' || l_api_name || ':comparing adjustment remaining amount = ' || l_appl_tbl(y).ar_adj_remaining_amount);
2313: LogMessage(G_PKG_NAME || '.' || l_api_name || ':and promise remaining amount = ' || P_PROMISES_TBL(i).AMOUNT_DUE_REMAINING);
2314: END IF;
2326: end if;
2327: end if;
2328:
2329: P_PROMISES_TBL(i).AMOUNT_DUE_REMAINING := P_PROMISES_TBL(i).AMOUNT_DUE_REMAINING - l_appl_tbl(y).pro_applied_amount;
2330: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2331: LogMessage(G_PKG_NAME || '.' || l_api_name || ':we will apply amount = ' || l_appl_tbl(y).pro_applied_amount);
2332: LogMessage(G_PKG_NAME || '.' || l_api_name || ':promise remaining amount after this application = ' || P_PROMISES_TBL(i).AMOUNT_DUE_REMAINING);
2333: END IF;
2334:
2333: END IF;
2334:
2335: l_applied_appl_count := y;
2336: if P_PROMISES_TBL(i).AMOUNT_DUE_REMAINING = 0 then -- we are done appliyng to the promise
2337: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2338: LogMessage(G_PKG_NAME || '.' || l_api_name || ':promise fulfilled by amount - exiting loop');
2339: END IF;
2340: exit;
2341: else -- we are not done yet. process next application
2338: LogMessage(G_PKG_NAME || '.' || l_api_name || ':promise fulfilled by amount - exiting loop');
2339: END IF;
2340: exit;
2341: else -- we are not done yet. process next application
2342: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2343: LogMessage(G_PKG_NAME || '.' || l_api_name || ':promise still not fulfilled by amount - process next application');
2344: END IF;
2345: end if;
2346:
2346:
2347: END LOOP;
2348:
2349: if P_PROMISES_TBL(i).AMOUNT_DUE_REMAINING > 0 then
2350: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2351: LogMessage(G_PKG_NAME || '.' || l_api_name || ':no more available applications');
2352: END IF;
2353: end if;
2354:
2352: END IF;
2353: end if;
2354:
2355: l_callback_date := null;
2356: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2357: LogMessage(G_PKG_NAME || '.' || l_api_name || ':figuring out promise status and state ...');
2358: END IF;
2359:
2360: if trunc(sysdate) > trunc(P_PROMISES_TBL(i).PROMISE_DATE) then /* the promise is in the past */
2357: LogMessage(G_PKG_NAME || '.' || l_api_name || ':figuring out promise status and state ...');
2358: END IF;
2359:
2360: if trunc(sysdate) > trunc(P_PROMISES_TBL(i).PROMISE_DATE) then /* the promise is in the past */
2361: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2362: LogMessage(G_PKG_NAME || '.' || l_api_name || ':the promise is in the past');
2363: END IF;
2364:
2365: if P_PROMISES_TBL(i).AMOUNT_DUE_REMAINING = 0 then -- promise is fulfilled by amount
2362: LogMessage(G_PKG_NAME || '.' || l_api_name || ':the promise is in the past');
2363: END IF;
2364:
2365: if P_PROMISES_TBL(i).AMOUNT_DUE_REMAINING = 0 then -- promise is fulfilled by amount
2366: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2367: LogMessage(G_PKG_NAME || '.' || l_api_name || ':the promise is fulfilled by amount - setting status FILFILLED');
2368: END IF;
2369: l_status := 'FULFILLED';
2370:
2370:
2371: if (trunc(l_appl_tbl(l_applied_appl_count).ar_apply_date) > trunc(P_PROMISES_TBL(i).PROMISE_DATE))
2372: or (trunc(l_appl_tbl(l_applied_appl_count).ar_adjusted_date) > trunc(P_PROMISES_TBL(i).PROMISE_DATE))
2373: then -- payments are late
2374: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2375: LogMessage(G_PKG_NAME || '.' || l_api_name || ':payments are late - setting state to BROKEN_PROMISE');
2376: END IF;
2377: l_state := 'BROKEN_PROMISE';
2378: else -- payment on time
2375: LogMessage(G_PKG_NAME || '.' || l_api_name || ':payments are late - setting state to BROKEN_PROMISE');
2376: END IF;
2377: l_state := 'BROKEN_PROMISE';
2378: else -- payment on time
2379: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2380: LogMessage(G_PKG_NAME || '.' || l_api_name || ':payments are on time - setting state PROMISE');
2381: END IF;
2382: l_state := 'PROMISE';
2383: end if;
2382: l_state := 'PROMISE';
2383: end if;
2384:
2385: else -- promise is not fulfilled by amount
2386: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2387: LogMessage(G_PKG_NAME || '.' || l_api_name || ':the promise is not fulfilled by amount - leaving status COLLECTABLE');
2388: END IF;
2389: l_status := 'COLLECTABLE';
2390:
2388: END IF;
2389: l_status := 'COLLECTABLE';
2390:
2391: if P_PROMISES_TBL(i).STATE = 'PROMISE' then
2392: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2393: LogMessage(G_PKG_NAME || '.' || l_api_name || ':the promise state is PROMISE - setting state to BROKEN_PROMISE');
2394: END IF;
2395: l_state := 'BROKEN_PROMISE';
2396: Get_Callback_Date(p_promise_date => P_PROMISES_TBL(i).PROMISE_DATE, x_callback_date => l_callback_date);
2394: END IF;
2395: l_state := 'BROKEN_PROMISE';
2396: Get_Callback_Date(p_promise_date => P_PROMISES_TBL(i).PROMISE_DATE, x_callback_date => l_callback_date);
2397: elsif P_PROMISES_TBL(i).STATE = 'BROKEN_PROMISE' then
2398: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2399: LogMessage(G_PKG_NAME || '.' || l_api_name || ':the promise state is already BROKEN_PROMISE - leave it BROKEN_PROMISE');
2400: END IF;
2401: l_state := 'BROKEN_PROMISE';
2402: end if;
2403: end if;
2404:
2405: else /* promise is in the future */
2406:
2407: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2408: LogMessage(G_PKG_NAME || '.' || l_api_name || ':the promise is in the future - leaving state PROMISE');
2409: END IF;
2410: l_state := 'PROMISE';
2411: if P_PROMISES_TBL(i).AMOUNT_DUE_REMAINING = 0 then -- promise is fulfilled by amount
2408: LogMessage(G_PKG_NAME || '.' || l_api_name || ':the promise is in the future - leaving state PROMISE');
2409: END IF;
2410: l_state := 'PROMISE';
2411: if P_PROMISES_TBL(i).AMOUNT_DUE_REMAINING = 0 then -- promise is fulfilled by amount
2412: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2413: LogMessage(G_PKG_NAME || '.' || l_api_name || ':the promise is fulfilled by amount - setting status to FULFILLED');
2414: END IF;
2415: l_status := 'FULFILLED';
2416: else
2413: LogMessage(G_PKG_NAME || '.' || l_api_name || ':the promise is fulfilled by amount - setting status to FULFILLED');
2414: END IF;
2415: l_status := 'FULFILLED';
2416: else
2417: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2418: LogMessage(G_PKG_NAME || '.' || l_api_name || ':the promise not fulfilled by amount - leaving status COLLECTABLE');
2419: END IF;
2420: l_status := 'COLLECTABLE';
2421: end if;
2420: l_status := 'COLLECTABLE';
2421: end if;
2422:
2423: end if;
2424: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2425: LogMessage(G_PKG_NAME || '.' || l_api_name || '......................');
2426: LogMessage(G_PKG_NAME || '.' || l_api_name || ':will set promise status to ' || l_status);
2427: LogMessage(G_PKG_NAME || '.' || l_api_name || ':will set promise state to ' || l_state);
2428: LogMessage(G_PKG_NAME || '.' || l_api_name || ':Updating iex_promise_details with promise_detail_id = ' || P_PROMISES_TBL(i).PROMISE_DETAIL_ID || ' set:');
2449: WHERE promise_detail_id = P_PROMISES_TBL(i).PROMISE_DETAIL_ID;
2450: end if;
2451:
2452: if (sql%notfound) then
2453: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2454: LogMessage(G_PKG_NAME || '.' || l_api_name || ':Failed to update iex_promise_details');
2455: END IF;
2456: -- Begin - Andre Araujo - 09/30/2004- Remove obsolete logging
2457: /*
2461: P_MESSAGE => 'Failed to update iex_promise_details');
2462: */
2463: -- End - Andre Araujo - 09/30/2004- Remove obsolete logging
2464: else
2465: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2466: LogMessage(G_PKG_NAME || '.' || l_api_name || ':update successfull');
2467: END IF;
2468: end if;
2469:
2466: LogMessage(G_PKG_NAME || '.' || l_api_name || ':update successfull');
2467: END IF;
2468: end if;
2469:
2470: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2471: LogMessage(G_PKG_NAME || '.' || l_api_name || ':Inserting into iex_prd_appl_xref values:');
2472: END IF;
2473:
2474: FOR x in 1..l_applied_appl_count LOOP
2471: LogMessage(G_PKG_NAME || '.' || l_api_name || ':Inserting into iex_prd_appl_xref values:');
2472: END IF;
2473:
2474: FOR x in 1..l_applied_appl_count LOOP
2475: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2476: LogMessage(G_PKG_NAME || '.' || l_api_name || ':record ' || x);
2477: LogMessage(G_PKG_NAME || '.' || l_api_name || ':PROMISE_DETAIL_ID = ' || P_PROMISES_TBL(i).PROMISE_DETAIL_ID);
2478: LogMessage(G_PKG_NAME || '.' || l_api_name || ':RECEIVABLE_APPLICATION_ID ' || l_appl_tbl(x).receivable_application_id);
2479: LogMessage(G_PKG_NAME || '.' || l_api_name || ':AMOUNT_APPLIED ' || l_appl_tbl(x).pro_applied_amount);
2521: END LOOP;
2522: -- End bug 3635087 gnramasa 10/07/07
2523: -- reopen strategy for just got broken promise
2524: if l_callback_date is not null then
2525: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2526: LogMessage(G_PKG_NAME || '.' || l_api_name || ': reopen strategy for promise ' || P_PROMISES_TBL(i).PROMISE_DETAIL_ID);
2527: END IF;
2528: IEX_PROMISES_PUB.SET_STRATEGY(P_PROMISE_ID => P_PROMISES_TBL(i).PROMISE_DETAIL_ID,
2529: P_STATUS => 'OPEN');
2529: P_STATUS => 'OPEN');
2530: end if;
2531:
2532: else -- nothing to apply
2533: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2534: LogMessage(G_PKG_NAME || '.' || l_api_name || ':nothing to apply');
2535: LogMessage(G_PKG_NAME || '.' || l_api_name || ':leaving promise status as it is - COLLECTABLE');
2536: LogMessage(G_PKG_NAME || '.' || l_api_name || ':figuring out promise state ...');
2537: END IF;
2536: LogMessage(G_PKG_NAME || '.' || l_api_name || ':figuring out promise state ...');
2537: END IF;
2538:
2539: if P_PROMISES_TBL(i).STATE = 'PROMISE' then
2540: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2541: LogMessage(G_PKG_NAME || '.' || l_api_name || ':the promise state is still PROMISE');
2542: END IF;
2543:
2544: if trunc(sysdate) > trunc(P_PROMISES_TBL(i).PROMISE_DATE) then /* the promise is in the past */
2541: LogMessage(G_PKG_NAME || '.' || l_api_name || ':the promise state is still PROMISE');
2542: END IF;
2543:
2544: if trunc(sysdate) > trunc(P_PROMISES_TBL(i).PROMISE_DATE) then /* the promise is in the past */
2545: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2546: LogMessage(G_PKG_NAME || '.' || l_api_name || ':the promise is in the past - setting state to BROKEN_PROMISE');
2547: END IF;
2548: l_state := 'BROKEN_PROMISE';
2549: Get_Callback_Date(p_promise_date => P_PROMISES_TBL(i).PROMISE_DATE, x_callback_date => l_callback_date);
2564: WHERE promise_detail_id = P_PROMISES_TBL(i).PROMISE_DETAIL_ID;
2565: end if;
2566:
2567: if (sql%notfound) then
2568: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2569: LogMessage(G_PKG_NAME || '.' || l_api_name || ':Failed to update iex_promise_details with STATUS = BROKEN for promise_detail_id = ' || P_PROMISES_TBL(i).PROMISE_DETAIL_ID);
2570: END IF;
2571: -- Begin - Andre Araujo - 09/30/2004- Remove obsolete logging
2572: /*
2576: P_MESSAGE => 'Failed to update iex_promise_details with STATUS = BROKEN for promise_detail_id = ' || P_PROMISES_TBL(i).PROMISE_DETAIL_ID);
2577: */
2578: -- End - Andre Araujo - 09/30/2004- Remove obsolete logging
2579: else
2580: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2581: LogMessage(G_PKG_NAME || '.' || l_api_name || ':update successfull');
2582: END IF;
2583:
2584: -- reopen strategy for just got broken promise
2582: END IF;
2583:
2584: -- reopen strategy for just got broken promise
2585: if l_callback_date is not null then
2586: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2587: LogMessage(G_PKG_NAME || '.' || l_api_name || ': reopen strategy for promise ' || P_PROMISES_TBL(i).PROMISE_DETAIL_ID);
2588: END IF;
2589: IEX_PROMISES_PUB.SET_STRATEGY(P_PROMISE_ID => P_PROMISES_TBL(i).PROMISE_DETAIL_ID,
2590: P_STATUS => 'OPEN');
2590: P_STATUS => 'OPEN');
2591: end if;
2592: end if;
2593: else /* promise is in the future */
2594: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2595: LogMessage(G_PKG_NAME || '.' || l_api_name || ':the promise is in the future - leaving the promise state PROMISE');
2596: END IF;
2597: end if;
2598: elsif P_PROMISES_TBL(i).STATE = 'BROKEN_PROMISE' then
2595: LogMessage(G_PKG_NAME || '.' || l_api_name || ':the promise is in the future - leaving the promise state PROMISE');
2596: END IF;
2597: end if;
2598: elsif P_PROMISES_TBL(i).STATE = 'BROKEN_PROMISE' then
2599: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2600: LogMessage(G_PKG_NAME || '.' || l_api_name || ':the promise state is already BROKEN_PROMISE - nothing to change.');
2601: END IF;
2602: end if;
2603: end if;
2602: end if;
2603: end if;
2604: END LOOP;
2605:
2606: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2607: LogMessage(G_PKG_NAME || '.' || l_api_name || ':End of body');
2608: END IF;
2609: -- END OF BODY OF API
2610:
2610:
2611: -- Standard check of p_commit.
2612: IF FND_API.To_Boolean( p_commit ) THEN
2613: COMMIT WORK;
2614: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2615: LogMessage(G_PKG_NAME || '.' || l_api_name || ':Commited work');
2616: END IF;
2617: END IF;
2618:
2636: P_Procedure_name => G_PKG_NAME || '.' || l_api_name,
2637: P_MESSAGE => 'Failed to do FIFO promise applications');
2638: */
2639: -- End - Andre Araujo - 09/30/2004- Remove obsolete logging
2640: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2641: LogMessage(G_PKG_NAME || '.' || l_api_name || ': In G_EXC_ERROR exception. Failed to do FIFO promise applications');
2642: END IF;
2643: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2644: ROLLBACK TO APPLY_PROMISES_FIFO_PVT;
2651: P_Procedure_name => G_PKG_NAME || '.' || l_api_name,
2652: P_MESSAGE => 'Failed to do FIFO promise applications');
2653: */
2654: -- End - Andre Araujo - 09/30/2004- Remove obsolete logging
2655: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2656: LogMessage(G_PKG_NAME || '.' || l_api_name || ': In G_EXC_UNEXPECTED_ERROR exception. Failed to do FIFO promise applications');
2657: END IF;
2658: WHEN OTHERS THEN
2659: ROLLBACK TO APPLY_PROMISES_FIFO_PVT;
2669: P_Procedure_name => G_PKG_NAME || '.' || l_api_name,
2670: P_MESSAGE => 'Failed to do FIFO promise applications');
2671: */
2672: -- End - Andre Araujo - 09/30/2004- Remove obsolete logging
2673: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2674: LogMessage(G_PKG_NAME || '.' || l_api_name || ': In OTHERS exception. Failed to do FIFO promise applications');
2675: END IF;
2676: END;
2677:
2879: IF FND_API.to_Boolean( p_init_msg_list ) THEN
2880: FND_MSG_PUB.initialize;
2881: END IF;
2882:
2883: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2884: LogMessage( 'Start time: ' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
2885: END IF;
2886:
2887: -- Initialize API return status to SUCCESS
2887: -- Initialize API return status to SUCCESS
2888: x_return_status := FND_API.G_RET_STS_SUCCESS;
2889:
2890: -- START OF BODY OF API
2891: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2892: LogMessage( 'Start of ' || l_api_name || ' body');
2893: END IF;
2894:
2895: Open C_GET_PROS;
2892: LogMessage( 'Start of ' || l_api_name || ' body');
2893: END IF;
2894:
2895: Open C_GET_PROS;
2896: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2897: LogMessage( 'OPEN C_GET_PROS');
2898: END IF;
2899: LOOP
2900:
2907: l_delinquency_id;
2908:
2909: EXIT WHEN C_GET_PROS%NOTFOUND;
2910:
2911: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2912: LogMessage( 'Found promise');
2913: LogMessage( 'promise_detail_id =' || l_promise_detail_id );
2914: LogMessage( 'party_id =' || l_party_id );
2915: LogMessage( 'resource_id =' || l_resource_id );
2916: LogMessage( 'l_cust_account_id =' || l_cust_account_id );
2917: END IF;
2918:
2919: If ( l_resource_id is null ) Then
2920: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2921: LogMessage( 'No Resource_ID');
2922: END IF;
2923: -- Begin - Andre Araujo - 09/30/2004- Remove obsolete logging
2924: /*
2979: end if;
2980:
2981: /* end kasreeni 01/20/2006 Create task reference for Party_id, ACCOUNT and bill to */
2982:
2983: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2984: LogMessage( 'Calling JTF_TASKS_PUB.CREATE_TASK...');
2985: END IF;
2986:
2987: JTF_TASKS_PUB.CREATE_TASK(
3013: x_task_id => l_task_id );
3014:
3015: -- check for errors
3016: IF l_return_status<>FND_API.G_RET_STS_SUCCESS THEN
3017: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3018: LogMessage( 'Call JTF_TASKS_PUB.CREATE_TASK failed');
3019: LogMessage(' JTF_TASKS_PUB,CREATE_TASK failed l_msg_count '|| l_msg_count);
3020: LogMessage(' JTF_TASKS_PUB.CREATE_TASK failed reason l_msg_data '|| l_msg_data);
3021:
3029: */
3030: -- End - Andre Araujo - 09/30/2004- Remove obsolete logging
3031: exit;
3032: ELSE
3033: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3034: LogMessage( 'Call JTF_TASKS_PUB.CREATE_TASK succeeded. Task_ID=' || l_task_id);
3035: END IF;
3036:
3037: END IF;
3043: last_updated_by = G_USER_ID
3044: WHERE promise_detail_id = l_promise_detail_id;
3045:
3046: if (sql%notfound) then
3047: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3048: LogMessage( 'update failed');
3049: END IF;
3050: -- Begin - Andre Araujo - 09/30/2004- Remove obsolete logging
3051: /*
3055: P_MESSAGE => 'Failed to update iex_promise_details for promise_detail_id = ' || l_promise_detail_id);
3056: */
3057: -- End - Andre Araujo - 09/30/2004- Remove obsolete logging
3058: else
3059: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3060: LogMessage( 'update successfull');
3061: END IF;
3062: end if;
3063:
3064: end if;
3065:
3066: end loop; -- end of CURSOR loop
3067:
3068: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3069: LogMessage( 'Close C_GET_PROS');
3070: END IF;
3071: Close C_GET_PROS;
3072:
3069: LogMessage( 'Close C_GET_PROS');
3070: END IF;
3071: Close C_GET_PROS;
3072:
3073: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3074: LogMessage( 'End of ' || l_api_name || ' body');
3075: END IF;
3076: -- END OF BODY OF API
3077:
3074: LogMessage( 'End of ' || l_api_name || ' body');
3075: END IF;
3076: -- END OF BODY OF API
3077:
3078: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3079: LogMessage( 'End time:' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
3080: END IF;
3081:
3082: -- Standard check of p_commit.
3084: COMMIT WORK;
3085: END IF;
3086:
3087: --Begin bug 7317666 21-Nov-2008 barathsr
3088: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3089: LogMessage( 'Cancelling the callback tasks correpsonding to fulfilled promises...');
3090: END IF;
3091: FOR rec1 IN c_invalid_tasks LOOP
3092: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3088: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3089: LogMessage( 'Cancelling the callback tasks correpsonding to fulfilled promises...');
3090: END IF;
3091: FOR rec1 IN c_invalid_tasks LOOP
3092: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3093: LogMessage( 'Cancelling the callback task: '||rec1.task_id);
3094: END IF;
3095: l_obj_version_number:=rec1.object_version_number;
3096: JTF_TASKS_PUB.UPDATE_TASK(
3105: x_msg_data => l_msg_data);
3106:
3107: END LOOP;
3108:
3109: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3110: LogMessage( 'Completed Cancelling the callback tasks correpsonding to fulfilled promises...');
3111: END IF;
3112:
3113: IF FND_API.To_Boolean( p_commit ) THEN
3205:
3206: -- Standard start of API savepoint
3207: SAVEPOINT REOPEN_PROMISES_PVT;
3208:
3209: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3210: LogMessage(G_PKG_NAME || '.REOPEN_PROMISES: Savepoint is established');
3211: END IF;
3212: -- Standard call to check for call compatibility
3213: IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
3222: -- Initialize API return status to success
3223: l_return_status := FND_API.G_RET_STS_SUCCESS;
3224:
3225: -- START OF BODY OF API
3226: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3227: LogMessage(G_PKG_NAME || '.REOPEN_PROMISES: Start of ' || l_api_name || ' body');
3228: END IF;
3229:
3230: l_del_count := p_dels_tbl.count;
3227: LogMessage(G_PKG_NAME || '.REOPEN_PROMISES: Start of ' || l_api_name || ' body');
3228: END IF;
3229:
3230: l_del_count := p_dels_tbl.count;
3231: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3232: LogMessage(G_PKG_NAME || '.REOPEN_PROMISES: delinquencies count: ' || l_del_count);
3233: END IF;
3234: if l_del_count > 0 then
3235:
3243: 'STATUS = ''CLOSED'' ' ||
3244: 'ORDER BY PROMISE_DETAIL_ID';
3245:
3246: FOR i in 1..l_del_count LOOP
3247: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3248: LogMessage(G_PKG_NAME || '.REOPEN_PROMISES: pulling closed promises for delinquency: ' || p_dels_tbl(i));
3249: END IF;
3250: open promises_cur for vSQL
3251: using p_dels_tbl(i);
3253: LOOP
3254: fetch promises_cur into l_promise_id, l_promise_date;
3255: exit when promises_cur%NOTFOUND;
3256:
3257: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3258: LogMessage(G_PKG_NAME || '.REOPEN_PROMISES: found promise with id: ' || l_promise_id);
3259: LogMessage(G_PKG_NAME || '.REOPEN_PROMISES: promise date: ' || l_promise_date);
3260: END IF;
3261:
3261:
3262: if trunc(sysdate) > trunc(l_promise_date) then
3263: Get_Callback_Date(p_promise_date => l_promise_date, x_callback_date => l_callback_date);
3264:
3265: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3266: LogMessage(G_PKG_NAME || '.REOPEN_PROMISES: updating promise ' || l_promise_id || ' with status BROKEN');
3267: LogMessage(G_PKG_NAME || '.REOPEN_PROMISES: callback date ' || l_callback_date);
3268: END IF;
3269: UPDATE iex_promise_details
3273: last_update_date = sysdate,
3274: last_updated_by = G_USER_ID
3275: WHERE promise_detail_id = l_promise_id;
3276: else
3277: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3278: LogMessage(G_PKG_NAME || '.REOPEN_PROMISES: updating promise ' || l_promise_id || ' with status OPEN');
3279: END IF;
3280: UPDATE iex_promise_details
3281: SET STATUS = 'OPEN',
3284: WHERE promise_detail_id = l_promise_id;
3285: end if;
3286:
3287: if (sql%notfound) then
3288: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3289: LogMessage(G_PKG_NAME || '.REOPEN_PROMISES: update of promise ' || l_promise_id || ' failed');
3290: END IF;
3291: -- Begin - Andre Araujo - 09/30/2004- Remove obsolete logging
3292: --IEX_CONC_REQUEST_MSG_PKG.Log_Error(
3294: -- P_Procedure_name => G_PKG_NAME || '.' || l_api_name,
3295: -- P_MESSAGE => 'Failed to update iex_promise_details for promise_detail_id = ' || l_promise_id);
3296: -- End - Andre Araujo - 09/30/2004- Remove obsolete logging
3297: else
3298: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3299: LogMessage(G_PKG_NAME || '.REOPEN_PROMISES: update of promise ' || l_promise_id || ' succeeded');
3300: END IF;
3301: end if;
3302: END LOOP;
3300: END IF;
3301: end if;
3302: END LOOP;
3303: END LOOP;
3304: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3305: LogMessage(G_PKG_NAME || '.REOPEN_PROMISES: done processing all delinquencies');
3306: END IF;
3307: end if;
3308:
3305: LogMessage(G_PKG_NAME || '.REOPEN_PROMISES: done processing all delinquencies');
3306: END IF;
3307: end if;
3308:
3309: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3310: LogMessage(G_PKG_NAME || '.REOPEN_PROMISES: End of ' || l_api_name || ' body');
3311: END IF;
3312: -- END OF BODY OF API
3313:
3313:
3314: -- Standard check of p_commit.
3315: IF FND_API.To_Boolean( p_commit ) THEN
3316: COMMIT WORK;
3317: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3318: LogMessage(G_PKG_NAME || '.REOPEN_PROMISES: Commited');
3319: END IF;
3320: END IF;
3321:
3362: -- End - Andre Araujo - 09/30/2004- Remove obsolete logging
3363: */
3364: END;
3365: begin
3366: PG_DEBUG := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
3367: G_LOGIN_ID := FND_GLOBAL.Conc_Login_Id;
3368: G_PROGRAM_ID := FND_GLOBAL.Conc_Program_Id;
3369: G_USER_ID := FND_GLOBAL.User_Id;
3370: G_REQUEST_ID := FND_GLOBAL.Conc_Request_Id;