[Home] [Help]
72: --SAVEPOINT CLEAN_DEL_BUFFERS_PVT;
73: RETCODE := FND_API.G_RET_STS_SUCCESS;
74: -- IF PG_DEBUG < 10 THEN
75: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
76: IEX_DEBUG_PUB.logMessage('CLEAR_DEL_BUFFERS: ' || 'IEX_DEL_PUB: cleaning del buffers');
77: END IF;
78: --
79: -- Begin - 01/25/2005 - Andre Araujo - This will cause the temp tables space to blow up changing it...
80: --
90: Exception
91: When others then
92: -- IF PG_DEBUG < 10 THEN
93: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
94: IEX_DEBUG_PUB.logMessage('CLEAR_DEL_BUFFERS: ' || 'IEX_DEL_PUB: cleaning failed due to ' || sqlerrm);
95: END IF;
96: RETCODE := -1;
97: ERRBUF := sqlerrm;
98:
157: FND_MSG_PUB.initialize;
158: END IF;
159:
160: -- Debug Message
161: IEX_DEBUG_PUB.LogMessage(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,'PVT: ' || l_api_name || ' start');
162:
163: --
164: -- API body
165: --
165: --
166:
167: l_num_payments := p_payments_tbl.count;
168:
169: IEX_DEBUG_PUB.LogMessage(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Calling Process Inv Payments');
170: IEX_PROMISES_BATCH_PUB.CLOSE_PROMISES(P_API_VERSION => l_api_version,
171: P_INIT_MSG_LIST => FND_API.G_FALSE,
172: P_COMMIT => FND_API.G_TRUE,
173: P_VALIDATION_LEVEL => NULL,
175: X_MSG_COUNT => l_msg_count,
176: X_MSG_DATA => l_msg_data,
177: P_PAYMENTS_TBL => p_payments_tbl);
178:
179: IEX_DEBUG_PUB.LogMessage(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Process Inv Payments returns ' || l_return_status);
180:
181: IF l_return_status <> 'S' THEN
182: NULL;
183: -- log error
182: NULL;
183: -- log error
184: END IF;
185:
186: IEX_DEBUG_PUB.LogMessage(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'pay count is ' || l_num_payments);
187:
188: -- get all delinquency IDs to be closed
189: IF l_num_payments >= 1 THEN
190: FOR i in 1..l_num_payments LOOP
188: -- get all delinquency IDs to be closed
189: IF l_num_payments >= 1 THEN
190: FOR i in 1..l_num_payments LOOP
191:
192: IEX_DEBUG_PUB.LogMessage(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'before open del cursor');
193: select delinquency_id into l_del_id
194: from iex_delinquencies
195: where payment_schedule_id = p_payments_tbl(i);
196:
200: ELSE
201: NULL;
202: END IF;
203:
204: IEX_DEBUG_PUB.LogMessage(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'callling close dunnings');
205:
206: -- here we will call crystal's API to Close_Dunnings
207: IEX_DUNNING_PUB.Close_Dunning(p_api_version => l_api_version
208: ,p_init_msg_list => FND_API.G_FALSE
212: ,x_return_status => l_return_status
213: ,x_msg_count => l_msg_count
214: ,x_msg_data => l_msg_data);
215:
216: IEX_DEBUG_PUB.LogMessage(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Close dunning returns ' || l_return_status);
217:
218: IF l_return_status <> 'S' THEN
219: NULL;
220: -- log error
219: NULL;
220: -- log error
221: END IF;
222:
223: IEX_DEBUG_PUB.LogMessage(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'before update');
224:
225: -- now update the delinquencies table
226: nCount := l_del_tbl.count;
227:
234: WHERE DELINQUENCY_ID = l_del_tbl(j);
235:
236: end if;
237:
238: IEX_DEBUG_PUB.LogMessage(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'after update');
239: COMMIT;
240:
241: x_return_status := l_return_status;
242:
244: -- End of API body
245: --
246:
247: -- Debug Message
248: IEX_DEBUG_PUB.LogMessage(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,'PVT: ' || l_api_name || ' end');
249:
250: -- Standard call to get message count and if count is 1, get message info.
251: FND_MSG_PUB.Count_And_Get
252: ( p_count => x_msg_count,
326: l_request_id :=p_request_id;
327: l_del_create_count := p_del_create_count ;
328: l_del_update_count := p_del_update_count ;
329: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
330: IEX_DEBUG_PUB.logMessage( 'Start Raise Delinquency Event Concurrent program');
331: IEX_DEBUG_PUB.logMessage('Program Run Date:'||SYSDATE);
332: END IF;
333:
334:
327: l_del_create_count := p_del_create_count ;
328: l_del_update_count := p_del_update_count ;
329: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
330: IEX_DEBUG_PUB.logMessage( 'Start Raise Delinquency Event Concurrent program');
331: IEX_DEBUG_PUB.logMessage('Program Run Date:'||SYSDATE);
332: END IF;
333:
334:
335: select iex_del_wf_s.nextval INTO l_seq from dual;
334:
335: select iex_del_wf_s.nextval INTO l_seq from dual;
336: l_key := l_event_name ||'-'||l_request_id || '-'||l_seq;
337: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
338: IEX_DEBUG_PUB.logMessage(' EVENT KEY ' ||l_key );
339: IEX_DEBUG_PUB.logMessage(
340: ' request_id =' ||l_request_id
341: ||'No of Del Created = ' ||l_del_create_count
342: ||'No of Del Updated =' ||l_del_update_count
335: select iex_del_wf_s.nextval INTO l_seq from dual;
336: l_key := l_event_name ||'-'||l_request_id || '-'||l_seq;
337: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
338: IEX_DEBUG_PUB.logMessage(' EVENT KEY ' ||l_key );
339: IEX_DEBUG_PUB.logMessage(
340: ' request_id =' ||l_request_id
341: ||'No of Del Created = ' ||l_del_create_count
342: ||'No of Del Updated =' ||l_del_update_count
343: );
355: to_char(l_del_update_count),
356: l_parameter_list);
357:
358: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
359: IEX_DEBUG_PUB.logMessage(' Before Launching Event ');
360: END IF;
361:
362: wf_event.raise(p_event_name => l_event_name
363: ,p_event_key => l_key
367:
368: l_parameter_list.DELETE;
369:
370: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
371: IEX_DEBUG_PUB.logMessage( 'End Raise Delinquency Event program');
372: END IF;
373:
374: -- Standard call to get message count and if count is 1, get message info.
375: FND_MSG_PUB.Count_And_Get
379:
380: EXCEPTION
381: WHEN OTHERS THEN
382: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
383: IEX_DEBUG_PUB.logMessage('Raise Delinquency Event Concurrent program raised exception '
384: || sqlerrm);
385: END IF;
386: x_msg_count := 1 ;
387: x_msg_data := sqlerrm ;
546: l_enable_business_events := FND_PROFILE.VALUE('IEX_ENABLE_CUST_STATUS_EVENT') ;
547:
548: -- IF PG_DEBUG < 10 THEN
549: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
550: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || v_line);
551: END IF;
552: -- IF PG_DEBUG < 10 THEN
553: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
554: IEX_DEBUG_PUB.LOGMESSAGE('---------->> PROCEDURE MANAGE_DELINQUENCIES Start <<----------');
550: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || v_line);
551: END IF;
552: -- IF PG_DEBUG < 10 THEN
553: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
554: IEX_DEBUG_PUB.LOGMESSAGE('---------->> PROCEDURE MANAGE_DELINQUENCIES Start <<----------');
555: END IF;
556: -- IF PG_DEBUG < 10 THEN
557: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
558: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || v_line);
554: IEX_DEBUG_PUB.LOGMESSAGE('---------->> PROCEDURE MANAGE_DELINQUENCIES Start <<----------');
555: END IF;
556: -- IF PG_DEBUG < 10 THEN
557: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
558: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || v_line);
559: END IF;
560: -- IF PG_DEBUG < 10 THEN
561: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
562: IEX_DEBUG_PUB.logMessage('MANAGE_DELINQUENCIES: ' || 'Start time:' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
558: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || v_line);
559: END IF;
560: -- IF PG_DEBUG < 10 THEN
561: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
562: IEX_DEBUG_PUB.logMessage('MANAGE_DELINQUENCIES: ' || 'Start time:' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
563: END IF;
564:
565:
566: RETCODE := 0 ;
577:
578: Begin
579: -- IF PG_DEBUG < 10 THEN
580: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
581: IEX_DEBUG_PUB.logMessage('MANAGE_DELINQUENCIES: ' || 'finding bridge ' || p_request_id);
582: END IF;
583: SELECT score_object_code,
584: score_id
585: INTO v_object,
589: AND rownum = 1 ;
590:
591: -- IF PG_DEBUG < 10 THEN
592: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
593: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || v_line);
594: END IF;
595: -- IF PG_DEBUG < 10 THEN
596: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
597: IEX_DEBUG_PUB.LOGMESSAGE
593: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || v_line);
594: END IF;
595: -- IF PG_DEBUG < 10 THEN
596: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
597: IEX_DEBUG_PUB.LOGMESSAGE
598: ('MANAGE_DELINQUENCIES: ' || 'Object >> ' || v_object || ' Score Id >> ' || v_score);
599: END IF;
600: -- IF PG_DEBUG < 10 THEN
601: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
598: ('MANAGE_DELINQUENCIES: ' || 'Object >> ' || v_object || ' Score Id >> ' || v_score);
599: END IF;
600: -- IF PG_DEBUG < 10 THEN
601: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
602: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || v_line);
603: END IF;
604: Exception
605: WHEN NO_DATA_FOUND then
606: ERRBUF := ' No Data Found on IEX_DEL_BUFFERS Table for the Passed Request Id...> '
607: || to_char(p_Request_id) ;
608: RETCODE := 1;
609: -- IF PG_DEBUG < 10 THEN
610: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
611: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || ERRBUF);
612: END IF;
613: ROLLBACK TO del_sp;
614:
615: -- IF PG_DEBUG < 10 THEN
613: ROLLBACK TO del_sp;
614:
615: -- IF PG_DEBUG < 10 THEN
616: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
617: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || v_line);
618: END IF;
619: -- IF PG_DEBUG < 10 THEN
620: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
621: IEX_DEBUG_PUB.LOGMESSAGE
617: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || v_line);
618: END IF;
619: -- IF PG_DEBUG < 10 THEN
620: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
621: IEX_DEBUG_PUB.LOGMESSAGE
622: ('---------->> PROCEDURE MANAGE_DELINQUENCIES End (returned) <<--------');
623: END IF;
624: -- IF PG_DEBUG < 10 THEN
625: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
622: ('---------->> PROCEDURE MANAGE_DELINQUENCIES End (returned) <<--------');
623: END IF;
624: -- IF PG_DEBUG < 10 THEN
625: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
626: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || v_line);
627: END IF;
628: return ;
629: End ;
630:
629: End ;
630:
631: -- IF PG_DEBUG < 10 THEN
632: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
633: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || 'Score Id = ' || v_score || ' Object Code = ' || v_object);
634: END IF;
635:
636: Select --fnd_profile.value('ORG_ID'), --Commneted for MOAC
637: fnd_profile.value('USER_ID'),
649: where score_id = v_score ;
650:
651: -- IF PG_DEBUG < 10 THEN
652: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
653: IEX_DEBUG_PUB.LOGMESSAGE
654: ('MANAGE_DELINQUENCIES: ' || 'Org Id = ' || v_org_id || ' Score Range Count = ' || v_score_range);
655: END IF;
656: -- IF PG_DEBUG < 10 THEN
657: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
654: ('MANAGE_DELINQUENCIES: ' || 'Org Id = ' || v_org_id || ' Score Range Count = ' || v_score_range);
655: END IF;
656: -- IF PG_DEBUG < 10 THEN
657: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
658: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || 'Debug Level = ' || to_char(v_debug_level));
659: END IF;
660: -- IF PG_DEBUG < 10 THEN
661: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
662: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || v_line);
658: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || 'Debug Level = ' || to_char(v_debug_level));
659: END IF;
660: -- IF PG_DEBUG < 10 THEN
661: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
662: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || v_line);
663: END IF;
664:
665: -- Everything works only when Score Range is Defined.
666: If v_score_range > 0 then
721:
722: /* ================= Debug Message ====================*/
723: -- IF PG_DEBUG < 10 THEN
724: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
725: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || 'Row Count after Update Select ');
726: END IF;
727: -- IF PG_DEBUG < 10 THEN
728: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
729: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || v_line);
725: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || 'Row Count after Update Select ');
726: END IF;
727: -- IF PG_DEBUG < 10 THEN
728: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
729: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || v_line);
730: END IF;
731:
732: if v_debug_level <= 10 then
733: for i in 1..vt_del_id.count
733: for i in 1..vt_del_id.count
734: LOOP
735: -- IF PG_DEBUG < 10 THEN
736: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
737: IEX_DEBUG_PUB.LOGMESSAGE
738: ('MANAGE_DELINQUENCIES: ' || ' Pmt Schd Id = ' || to_char(vt_pmt_schd_id(i))||
739: ' Delinquency Id = ' || to_char(vt_del_id(i)) ||
740: ' Buf Status = ' || vt_buf_status(i) ||
741: 'Del Status = ' || vt_del_status(i));
743: END LOOP ;
744: End If ;
745: -- IF PG_DEBUG < 10 THEN
746: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
747: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || v_line);
748: END IF;
749: /*================= Debug Message ====================== */
750:
751: Exception
754: ' Error Msg ' || SQLERRM ;
755: RETCODE := -1;
756: -- IF PG_DEBUG < 10 THEN
757: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
758: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || ERRBUF);
759: END IF;
760: ROLLBACK TO del_sp;
761:
762: -- IF PG_DEBUG < 10 THEN
760: ROLLBACK TO del_sp;
761:
762: -- IF PG_DEBUG < 10 THEN
763: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
764: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || 'Deleting Buffer Table after RollBack due to Error');
765: END IF;
766:
767:
768: --
783: -- End - Andre Araujo - 09/30/2004- Remove obsolete logging
784:
785: -- IF PG_DEBUG < 10 THEN
786: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
787: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || 'Deletd Buffer Row Count >> '||to_char(SQL%ROWCOUNT));
788: END IF;
789: -- IF PG_DEBUG < 10 THEN
790: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
791: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || v_line);
787: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || 'Deletd Buffer Row Count >> '||to_char(SQL%ROWCOUNT));
788: END IF;
789: -- IF PG_DEBUG < 10 THEN
790: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
791: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || v_line);
792: END IF;
793: -- IF PG_DEBUG < 10 THEN
794: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
795: IEX_DEBUG_PUB.LOGMESSAGE
791: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || v_line);
792: END IF;
793: -- IF PG_DEBUG < 10 THEN
794: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
795: IEX_DEBUG_PUB.LOGMESSAGE
796: ('--->> PROCEDURE MANAGE_DELINQUENCIES End (returned) <<--------');
797: END IF;
798: -- IF PG_DEBUG < 10 THEN
799: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
796: ('--->> PROCEDURE MANAGE_DELINQUENCIES End (returned) <<--------');
797: END IF;
798: -- IF PG_DEBUG < 10 THEN
799: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
800: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || v_line);
801: END IF;
802:
803: Commit;
804: return;
822:
823: /* ================= Debug Message ================== */
824: -- IF PG_DEBUG < 10 THEN
825: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
826: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || v_line);
827: END IF;
828: if v_debug_level <= 10 then
829: FOR i in 1..vt_del_id.count
830: LOOP
829: FOR i in 1..vt_del_id.count
830: LOOP
831: -- IF PG_DEBUG < 10 THEN
832: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
833: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || 'Rows Updated for ' ||
834: to_char(vt_del_id(i)) || ' is ' ||
835: to_char(SQL%BULK_ROWCOUNT(i)));
836: END IF;
837: END LOOP ;
837: END LOOP ;
838: End If ;
839: -- IF PG_DEBUG < 10 THEN
840: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
841: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || v_line);
842: END IF;
843: /* ================= Debug Message =========================== */
844:
845: Exception
849: || SQLCODE || ' Error Mesg ' || SQLERRM ;
850: RETCODE := -1;
851: -- IF PG_DEBUG < 10 THEN
852: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
853: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || ERRBUF);
854: END IF;
855: ROLLBACK TO del_sp ;
856:
857: -- IF PG_DEBUG < 10 THEN
855: ROLLBACK TO del_sp ;
856:
857: -- IF PG_DEBUG < 10 THEN
858: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
859: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || 'Deleting Buffer Table after RollBack due to Error');
860: END IF;
861:
862: --
863: -- Begin - 01/24/2005 - Andre Araujo - This procedure uses a memory schema uses up all memory available for the session, changing it to chunks
874: --
875:
876: -- IF PG_DEBUG < 10 THEN
877: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
878: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || 'Deleted Buffer Row Count >> ' || to_char(SQL%ROWCOUNT));
879: END IF;
880: -- IF PG_DEBUG < 10 THEN
881: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
882: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || v_line);
878: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || 'Deleted Buffer Row Count >> ' || to_char(SQL%ROWCOUNT));
879: END IF;
880: -- IF PG_DEBUG < 10 THEN
881: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
882: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || v_line);
883: END IF;
884: -- IF PG_DEBUG < 10 THEN
885: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
886: IEX_DEBUG_PUB.LOGMESSAGE('--->> PROCEDURE MANAGE_DELINQUENCIES End (returned) <<--------');
882: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || v_line);
883: END IF;
884: -- IF PG_DEBUG < 10 THEN
885: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
886: IEX_DEBUG_PUB.LOGMESSAGE('--->> PROCEDURE MANAGE_DELINQUENCIES End (returned) <<--------');
887: END IF;
888: -- IF PG_DEBUG < 10 THEN
889: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
890: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || v_line);
886: IEX_DEBUG_PUB.LOGMESSAGE('--->> PROCEDURE MANAGE_DELINQUENCIES End (returned) <<--------');
887: END IF;
888: -- IF PG_DEBUG < 10 THEN
889: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
890: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || v_line);
891: END IF;
892:
893: Commit;
894: return;
901: Open dbg_test ;
902:
903: -- IF PG_DEBUG < 10 THEN
904: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
905: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || '--------- Insert Candidate Rows ----------');
906: END IF;
907: -- IF PG_DEBUG < 10 THEN
908: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
909: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || '----cust----pmt schd----status----');
905: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || '--------- Insert Candidate Rows ----------');
906: END IF;
907: -- IF PG_DEBUG < 10 THEN
908: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
909: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || '----cust----pmt schd----status----');
910: END IF;
911:
912: LOOP
913: FETCH dbg_test
920: EXIT WHEN dbg_test%NOTFOUND ;
921:
922: -- IF PG_DEBUG < 10 THEN
923: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
924: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || to_char(dbg_cust) || ' ---- ' ||
925: to_char(dbg_object_id) || ' ---- ' ||dbg_status);
926: END IF;
927: END LOOP ;
928:
1011:
1012: /* ================= Debug Message ==================== */
1013: -- IF PG_DEBUG < 10 THEN
1014: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1015: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || v_line);
1016: END IF;
1017: -- IF PG_DEBUG < 10 THEN
1018: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1019: IEX_DEBUG_PUB.LOGMESSAGE
1015: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || v_line);
1016: END IF;
1017: -- IF PG_DEBUG < 10 THEN
1018: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1019: IEX_DEBUG_PUB.LOGMESSAGE
1020: ('MANAGE_DELINQUENCIES: ' || 'Number of Rows Inserted --> ' || to_char(SQL%ROWCOUNT));
1021: END IF;
1022: -- IF PG_DEBUG < 10 THEN
1023: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1020: ('MANAGE_DELINQUENCIES: ' || 'Number of Rows Inserted --> ' || to_char(SQL%ROWCOUNT));
1021: END IF;
1022: -- IF PG_DEBUG < 10 THEN
1023: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1024: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || v_line);
1025: END IF;
1026: /* ================= Debug Message ==================== */
1027:
1028: Exception
1029: WHEN OTHERS then
1030: ERRBUF := 'INSERT - Error Code = ' || SQLCODE || ' Error Msg ' || SQLERRM ;
1031: -- IF PG_DEBUG < 10 THEN
1032: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1033: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || ERRBUF);
1034: END IF;
1035: RETCODE := -1 ;
1036: ROLLBACK TO del_sp ;
1037:
1036: ROLLBACK TO del_sp ;
1037:
1038: -- IF PG_DEBUG < 10 THEN
1039: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1040: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || 'Deleting Buffer Table after RollBack due to Error');
1041: END IF;
1042:
1043: --
1044: -- Begin - 01/24/2005 - Andre Araujo - This procedure uses a memory schema uses up all memory available for the session, changing it to chunks
1053: --
1054:
1055: -- IF PG_DEBUG < 10 THEN
1056: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1057: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || 'Deletd Buffer Row Count >> ' ||
1058: to_char(SQL%ROWCOUNT));
1059: END IF;
1060: Commit;
1061: return;
1076:
1077: begin
1078:
1079: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1080: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_CONTRACTS: ' || 'Starting Contract..... ');
1081: END IF;
1082:
1083: SELECT ico.object_id, ids.del_status, ico.delinquency_status
1084: BULK COLLECT INTO vt_contract_id, vt_buf_status, vt_del_status
1092: AND idb.request_id = p_request_id;
1093: null;
1094:
1095: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1096: IEX_DEBUG_PUB.LOGMESSAGE
1097: ('MANAGE_DELINQUENCIES: ' || 'CONTRACT - Row Count after Update Select ' || to_char(vt_contract_id.COUNT));
1098: END IF;
1099:
1100: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1097: ('MANAGE_DELINQUENCIES: ' || 'CONTRACT - Row Count after Update Select ' || to_char(vt_contract_id.COUNT));
1098: END IF;
1099:
1100: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1101: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || v_line);
1102: END IF;
1103:
1104: if v_debug_level < 11 then
1105: for i in 1..vt_del_id.count
1105: for i in 1..vt_del_id.count
1106: LOOP
1107:
1108: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1109: IEX_DEBUG_PUB.LOGMESSAGE
1110: ('MANAGE_DELINQUENCIES: ' || ' CONTRACT Id = ' || to_char(vt_contract_id(i))||
1111: ' Delinquency Id = ' || to_char(vt_del_id(i)) ||
1112: ' Buf Status = ' || vt_buf_status(i) ||
1113: 'Del Status = ' || vt_del_status(i));
1115: END LOOP ;
1116: End If ;
1117:
1118: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1119: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || v_line);
1120: END IF;
1121:
1122: EXCEPTION
1123: WHEN OTHERS THEN
1123: WHEN OTHERS THEN
1124: ERRBUF := 'CONTRACT - Matching Delinquencies Select -->' || SQLCODE || ' Error Msg ' || SQLERRM ;
1125:
1126: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1127: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || ERRBUF);
1128: END IF;
1129: RETCODE := -1 ;
1130:
1131: ROLLBACK TO del_sp ;
1130:
1131: ROLLBACK TO del_sp ;
1132:
1133: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1134: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || 'Deletd Buffer Row Count >> ' || to_char(SQL%ROWCOUNT));
1135: END IF;
1136:
1137: Commit;
1138: return;
1152: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Number Of Contracts Updated..>> '|| vt_contract_id.count) ;
1153: l_del_update_count := vt_del_id.count ;
1154:
1155: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1156: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || v_line);
1157: END IF;
1158: if v_debug_level < 11 then
1159: FOR i in 1..vt_del_id.count
1160: LOOP
1159: FOR i in 1..vt_del_id.count
1160: LOOP
1161:
1162: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1163: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || 'Rows Updated for ' || to_char(vt_del_id(i)) || ' is ' ||
1164: to_char(SQL%BULK_ROWCOUNT(i)));
1165: END IF;
1166: END LOOP ;
1167: End If ;
1166: END LOOP ;
1167: End If ;
1168:
1169: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1170: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || v_line);
1171: END IF;
1172:
1173: EXCEPTION
1174: WHEN OTHERS THEN
1174: WHEN OTHERS THEN
1175: ERRBUF := 'CONTRACT Updating... - Matching Delinquencies Update --> ' || SQLCODE || ' Error Msg ' || SQLERRM ;
1176:
1177: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1178: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || ERRBUF);
1179: END IF;
1180: RETCODE := -1;
1181: ROLLBACK TO del_sp;
1182:
1180: RETCODE := -1;
1181: ROLLBACK TO del_sp;
1182:
1183: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1184: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || 'Deleting Buffer Table after RollBack due to Error');
1185: END IF;
1186: CLEAR_BUFFERS2(p_request_id);
1187: Commit;
1188: return;
1195: BEGIN
1196:
1197: -- IF PG_DEBUG < 10 THEN
1198: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1199: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || 'Starting Cases ');
1200: END IF;
1201:
1202: -- Selects All the matching rows between buffer and Delinquencies table
1203: -- except when the statuses are same or when buffer status is PREDELINQUENT
1239: AND idb.request_id = p_request_id;
1240: /* ================= Debug Message ====================*/
1241:
1242: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1243: IEX_DEBUG_PUB.LOGMESSAGE
1244: -- ('MANAGE_DELINQUENCIES: ' || 'CASE - Row Count after Update Select ' || to_char(vt_del_id.COUNT));
1245: ('MANAGE_DELINQUENCIES: ' || 'CASE - Row Count after Update Select ' || to_char(vt_case_id.COUNT));
1246: END IF;
1247:
1245: ('MANAGE_DELINQUENCIES: ' || 'CASE - Row Count after Update Select ' || to_char(vt_case_id.COUNT));
1246: END IF;
1247:
1248: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1249: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || v_line);
1250: END IF;
1251:
1252: if v_debug_level < 11 then
1253: for i in 1..vt_del_id.count
1253: for i in 1..vt_del_id.count
1254: LOOP
1255:
1256: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1257: IEX_DEBUG_PUB.LOGMESSAGE
1258: ('MANAGE_DELINQUENCIES: ' || ' Case Id = ' || to_char(vt_case_id(i))||
1259: ' Delinquency Id = ' || to_char(vt_del_id(i)) ||
1260: ' Buf Status = ' || vt_buf_status(i) ||
1261: 'Del Status = ' || vt_del_status(i));
1263: END LOOP ;
1264: End If ;
1265:
1266: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1267: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || v_line);
1268: END IF;
1269: /*================= Debug Message ====================== */
1270:
1271: EXCEPTION
1272: WHEN OTHERS THEN
1273: ERRBUF := 'CASE - Matching Delinquencies Select -->' || SQLCODE || ' Error Msg ' || SQLERRM ;
1274:
1275: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1276: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || ERRBUF);
1277: END IF;
1278: RETCODE := -1 ;
1279:
1280: ROLLBACK TO del_sp ;
1292: --
1293:
1294: -- IF PG_DEBUG < 10 THEN
1295: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1296: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || 'Deletd Buffer Row Count >> ' || to_char(SQL%ROWCOUNT));
1297: END IF;
1298:
1299: -- Begin - Andre Araujo - 09/30/2004- Remove obsolete logging
1300: -- IEX_CONC_REQUEST_MSG_PKG.LOG_ERROR(p_request_id, 'MANAGE_DELINQUENCIES',ERRBUF) ;
1334:
1335: /* ================= Debug Message =========================*/
1336: -- IF PG_DEBUG < 10 THEN
1337: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1338: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || v_line);
1339: END IF;
1340: if v_debug_level < 11 then
1341: FOR i in 1..vt_del_id.count
1342: LOOP
1341: FOR i in 1..vt_del_id.count
1342: LOOP
1343: -- IF PG_DEBUG < 10 THEN
1344: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1345: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || 'Rows Updated for ' ||
1346: to_char(vt_del_id(i)) || ' is ' ||
1347: to_char(SQL%BULK_ROWCOUNT(i)));
1348: END IF;
1349: END LOOP ;
1349: END LOOP ;
1350: End If ;
1351: -- IF PG_DEBUG < 10 THEN
1352: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1353: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || v_line);
1354: END IF;
1355: /* ================= Debug Message =========================== */
1356:
1357: EXCEPTION
1359: ERRBUF := 'CASE - Matching Delinquencies Update --> ' ||
1360: SQLCODE || ' Error Msg ' || SQLERRM ;
1361: -- IF PG_DEBUG < 10 THEN
1362: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1363: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || ERRBUF);
1364: END IF;
1365: RETCODE := -1;
1366: ROLLBACK TO del_sp;
1367:
1366: ROLLBACK TO del_sp;
1367:
1368: -- IF PG_DEBUG < 10 THEN
1369: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1370: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || 'Deleting Buffer Table after RollBack due to Error');
1371: END IF;
1372:
1373: --
1374: -- Begin - 01/24/2005 - Andre Araujo - This procedure uses a memory schema uses up all memory available for the session, changing it to chunks
1468:
1469: -- ================= Debug Message ========================
1470: -- IF PG_DEBUG < 10 THEN
1471: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1472: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || v_line);
1473: END IF;
1474: -- IF PG_DEBUG < 10 THEN
1475: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1476: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || 'Number of Rows Inserted --> ' || to_char(SQL%ROWCOUNT));
1472: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || v_line);
1473: END IF;
1474: -- IF PG_DEBUG < 10 THEN
1475: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1476: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || 'Number of Rows Inserted --> ' || to_char(SQL%ROWCOUNT));
1477: END IF;
1478: IF PG_DeBUG < 10 THEN
1479: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1480: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || v_line);
1476: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || 'Number of Rows Inserted --> ' || to_char(SQL%ROWCOUNT));
1477: END IF;
1478: IF PG_DeBUG < 10 THEN
1479: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1480: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || v_line);
1481: END IF;
1482: END IF;
1483: -- ================= Debug Message ===========================
1484:
1486: WHEN OTHERS then
1487: ERRBUF := 'INSERT - Error Code = ' || SQLCODE || ' Error Msg ' || SQLERRM ;
1488: -- IF PG_DEBUG < 10 THEN
1489: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1490: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || ERRBUF);
1491: END IF;
1492: RETCODE := -1 ;
1493: ROLLBACK TO del_sp ;
1494:
1493: ROLLBACK TO del_sp ;
1494:
1495: -- IF PG_DEBUG < 10 THEN
1496: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1497: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || 'CASE INSERT >> Deleting Buffer Table after RollBack due to Error');
1498: END IF;
1499:
1500: --
1501: -- Begin - 01/24/2005 - Andre Araujo - This procedure uses a memory schema uses up all memory available for the session, changing it to chunks
1510: --
1511:
1512: -- IF PG_DEBUG < 10 THEN
1513: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1514: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || 'Deletd Buffer Row Count >> ' || to_char(SQL%ROWCOUNT));
1515: END IF;
1516: -- Begin - Andre Araujo - 09/30/2004- Remove obsolete logging
1517: --IEX_CONC_REQUEST_MSG_PKG.LOG_ERROR(p_request_id, 'MANAGE_DELINQUENCIES',ERRBUF) ;
1518: -- End - Andre Araujo - 09/30/2004- Remove obsolete logging
1528:
1529: -- ______________________ CALLING THE WORKFLOW __________________________
1530: -- IF PG_DEBUG < 10 THEN
1531: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1532: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || 'Calling the Work Flow........ ');
1533: END IF;
1534: -- IF PG_DEBUG < 10 THEN
1535: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1536: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || 'Delinquency Table Count After Mangement Process ' || to_char(vt_del_id.count));
1532: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || 'Calling the Work Flow........ ');
1533: END IF;
1534: -- IF PG_DEBUG < 10 THEN
1535: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1536: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || 'Delinquency Table Count After Mangement Process ' || to_char(vt_del_id.count));
1537: END IF;
1538:
1539: -- Filter out NOCOPY the delinquency ids that are not the Workflow Candidates.
1540: if vt_del_id.count > 0 then
1544: if vt_del_status(ct) IN (vf_delinquent, vf_predelinquent)
1545: AND (vt_buf_status(ct) = vf_current) THEN
1546: -- F PG_DEBUG < 10 THEN
1547: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1548: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || 'Del Id for Workflow >> ' || to_char(vt_del_id(ct)));
1549: END IF;
1550: if vt_wf_del_id.COUNT = 0 then
1551: vt_wf_del_id(1) := vt_del_id(ct) ;
1552: else
1558:
1559: -- ================= Debug Message ===========================
1560: -- IF PG_DEBUG < 10 THEN
1561: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1562: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || '------------------------------------------------------------');
1563: END IF;
1564: -- IF PG_DEBUG < 10 THEN
1565: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1566: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || 'Number of WorlFlow Candidate Rows --> ' || to_char(vt_wf_del_id.COUNT));
1562: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || '------------------------------------------------------------');
1563: END IF;
1564: -- IF PG_DEBUG < 10 THEN
1565: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1566: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || 'Number of WorlFlow Candidate Rows --> ' || to_char(vt_wf_del_id.COUNT));
1567: END IF;
1568:
1569: -- Launch the Workflow and Close Promises only when required
1570: if vt_wf_del_id.COUNT > 0 then
1572: FOR p in 1..vt_wf_del_id.COUNT
1573: LOOP
1574: -- IF PG_DEBUG < 10 THEN
1575: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1576: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || 'Delinquency Id --> ' || to_char(vt_wf_del_id(p)));
1577: END IF;
1578: END LOOP ;
1579: -- IF PG_DEBUG < 10 THEN
1580: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1577: END IF;
1578: END LOOP ;
1579: -- IF PG_DEBUG < 10 THEN
1580: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1581: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || '------------------------------------------------------------');
1582: END IF;
1583: /* ================= Debug Message =========================== */
1584:
1585:
1594: x_msg_data => l_msg_data);
1595:
1596: -- IF PG_DEBUG < 10 THEN
1597: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1598: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || 'WorlFlow Status --> ' || l_return_status);
1599: END IF;
1600:
1601: -- Close the Promises
1602: IEX_PROMISES_BATCH_PUB.CLOSE_PROMISES
1610: p_delinq_tbl => vt_wf_del_id );
1611:
1612: -- IF PG_DEBUG < 10 THEN
1613: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1614: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || 'Close Promises Status --> ' || l_return_status);
1615: END IF;
1616:
1617: End If ;
1618:
1617: End If ;
1618:
1619: -- IF PG_DEBUG < 10 THEN
1620: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1621: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || 'Deleting Buffer Table');
1622: END IF;
1623: --
1624: -- Begin - 01/24/2005 - Andre Araujo - This procedure uses a memory schema uses up all memory available for the session, changing it to chunks
1625: --
1629: -- End - 01/24/2005 - Andre Araujo - This procedure uses a memory schema uses up all memory available for the session, changing it to chunks
1630: --
1631: -- IF PG_DEBUG < 10 THEN
1632: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1633: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || 'Deleted Buffer Row Count >> ' || to_char(SQL%ROWCOUNT));
1634: END IF;
1635:
1636: if not isRefreshProgramsRunning then -- Added for bug#7133605 by schekuri on 17-Jun-2008 --Added by PNAVEENK
1637: --Bug5237039. Fix by LKKUMAR on 25-May-2006. Start
1636: if not isRefreshProgramsRunning then -- Added for bug#7133605 by schekuri on 17-Jun-2008 --Added by PNAVEENK
1637: --Bug5237039. Fix by LKKUMAR on 25-May-2006. Start
1638: FND_FILE.PUT_LINE(FND_FILE.LOG,'Starting to update ar_trx_bal_summary....'); --Added by PNAVEENK
1639: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1640: IEX_DEBUG_PUB.LOGMESSAGE('Starting to update ar_trx_bal_summary with reference_1 = 1...');
1641: END IF;
1642: -- start bug 10037776 sunagesh 01.08.11
1643: /* call refresh_summary_incr, only when the business events are enabled.
1644: i.e When business event is enabled, changes will get reflect in iex_dln_uwq_summary table and there is no need to run populate uwq summary cp
1652: FETCH c_cust_account_id_1 BULK COLLECT INTO
1653: l_cust_account_id_1 LIMIT G_BATCH_SIZE;
1654: IF l_cust_account_id_1.count = 0 THEN
1655: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1656: IEX_DEBUG_PUB.LOGMESSAGE('Exit after Updating ar_trx_bal_summary with reference_1 = 1...');
1657: END IF;
1658: CLOSE c_cust_account_id_1;
1659: EXIT;
1660: ELSE
1663: SET REFERENCE_1 = 1
1664: WHERE CUST_ACCOUNT_ID = l_cust_account_id_1(I);
1665:
1666: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1667: IEX_DEBUG_PUB.LOGMESSAGE(SQL%ROWCOUNT || ' Rows updated in ar_trx_bal_summary with reference_1 = 1');
1668: END IF;
1669: END IF;
1670: END LOOP;*/
1671: select count(we.name)
1683:
1684: EXCEPTION WHEN OTHERS THEN
1685: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1686: -- start bug 10037776 sunagesh 01.08.11
1687: -- IEX_DEBUG_PUB.LOGMESSAGE(SQLERRM || ' Error while updating ar_trx_bal_summary with reference_1 = 1');
1688: IEX_DEBUG_PUB.LOGMESSAGE(SQLERRM || ' Error while getting Active business events count');
1689: -- end bug 10037776 sunagesh 01.08.11
1690: END IF;
1691: END;
1684: EXCEPTION WHEN OTHERS THEN
1685: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1686: -- start bug 10037776 sunagesh 01.08.11
1687: -- IEX_DEBUG_PUB.LOGMESSAGE(SQLERRM || ' Error while updating ar_trx_bal_summary with reference_1 = 1');
1688: IEX_DEBUG_PUB.LOGMESSAGE(SQLERRM || ' Error while getting Active business events count');
1689: -- end bug 10037776 sunagesh 01.08.11
1690: END IF;
1691: END;
1692:
1690: END IF;
1691: END;
1692:
1693: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1694: IEX_DEBUG_PUB.LOGMESSAGE('Starting to update ar_trx_bal_summary with reference_1 = Null...');
1695: END IF;
1696: -- start bug 10037776 sunagesh 01.08.11
1697: IEX_DEBUG_PUB.LOGMESSAGE('Number of active business events: ' || l_noof_active_busi_events);
1698: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Number of active business events: ' || l_noof_active_busi_events) ;
1693: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1694: IEX_DEBUG_PUB.LOGMESSAGE('Starting to update ar_trx_bal_summary with reference_1 = Null...');
1695: END IF;
1696: -- start bug 10037776 sunagesh 01.08.11
1697: IEX_DEBUG_PUB.LOGMESSAGE('Number of active business events: ' || l_noof_active_busi_events);
1698: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Number of active business events: ' || l_noof_active_busi_events) ;
1699:
1700: if l_noof_active_busi_events > 0 then
1701:
1699:
1700: if l_noof_active_busi_events > 0 then
1701:
1702: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1703: IEX_DEBUG_PUB.LOGMESSAGE('Starting to update ar_trx_bal_summary with reference_1 = 1...');
1704: END IF;
1705: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Updating AR_TRX_BAL_SUMMARY table with reference_1 = 1...') ;
1706: BEGIN
1707: OPEN c_cust_account_id_1;
1709: FETCH c_cust_account_id_1 BULK COLLECT INTO
1710: l_cust_account_id_1 LIMIT G_BATCH_SIZE;
1711: IF l_cust_account_id_1.count = 0 THEN
1712: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1713: IEX_DEBUG_PUB.LOGMESSAGE('Exit after Updating ar_trx_bal_summary with reference_1 = 1...');
1714: END IF;
1715: CLOSE c_cust_account_id_1;
1716: EXIT;
1717: ELSE
1720: SET REFERENCE_1 = '1'
1721: WHERE CUST_ACCOUNT_ID = l_cust_account_id_1(I)
1722: AND ORG_ID = FND_PROFILE.VALUE('ORG_ID');
1723: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1724: IEX_DEBUG_PUB.LOGMESSAGE(SQL%ROWCOUNT || ' Rows updated in ar_trx_bal_summary with reference_1 = 1');
1725: END IF;
1726: END IF;
1727: END LOOP;
1728: EXCEPTION WHEN OTHERS THEN
1726: END IF;
1727: END LOOP;
1728: EXCEPTION WHEN OTHERS THEN
1729: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1730: IEX_DEBUG_PUB.LOGMESSAGE(SQLERRM || ' Error while updating ar_trx_bal_summary with reference_1 = 1');
1731: END IF;
1732: END;
1733:
1734: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1731: END IF;
1732: END;
1733:
1734: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1735: IEX_DEBUG_PUB.LOGMESSAGE('Starting to update ar_trx_bal_summary with reference_1 = Null...');
1736: END IF;
1737: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Updating AR_TRX_BAL_SUMMARY table with reference_1 = Null...') ;
1738: -- end bug 10037776 sunagesh 01.08.11
1739: BEGIN
1742: FETCH c_cust_account_id_n BULK COLLECT INTO
1743: l_cust_account_id_n LIMIT G_BATCH_SIZE;
1744: IF l_cust_account_id_n.count = 0 THEN
1745: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1746: IEX_DEBUG_PUB.LOGMESSAGE('Exit after Update ar_trx_bal_summary on complete with reference_1 = Null...');
1747: END IF;
1748: CLOSE c_cust_account_id_n;
1749: EXIT;
1750: ELSE
1753: SET REFERENCE_1 = Null
1754: WHERE CUST_ACCOUNT_ID = l_cust_account_id_n(I);
1755: FND_FILE.PUT_LINE(FND_FILE.LOG,SQL%ROWCOUNT || ' Rows updated in ar_trx_bal_summary with reference_1 = NULL');
1756: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1757: IEX_DEBUG_PUB.LOGMESSAGE(SQL%ROWCOUNT || 'Rows updated in ar_trx_bal_summary with reference_1 = Null');
1758: END IF;
1759: END IF;
1760: END LOOP;
1761: EXCEPTION WHEN OTHERS THEN
1759: END IF;
1760: END LOOP;
1761: EXCEPTION WHEN OTHERS THEN
1762: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1763: IEX_DEBUG_PUB.LOGMESSAGE(SQLERRM || ' Error while updating ar_trx_bal_summary with reference_1 = Null');
1764: END IF;
1765: END;
1766: --Bug5237039. Fix by LKKUMAR on 25-May-2006. End.
1767: -- end if; -- 6785378
1769: -- Start Bug 5874874 gnramasa 25-Apr-2007
1770:
1771: -- if vt_del_id.count > 0 then -- 6785378
1772: -- start bug 10037776 sunagesh 01.08.11
1773: IEX_DEBUG_PUB.LOGMESSAGE('Business events are enabled, so calling iex_uwq_pop_sum_tbl_pvt.refresh_summary_incr');
1774: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Business events are enabled, so calling iex_uwq_pop_sum_tbl_pvt.refresh_summary_incr') ;
1775: -- end bug 10037776 sunagesh 01.08.11
1776: iex_uwq_pop_sum_tbl_pvt.refresh_summary_incr
1777: (x_errbuf,
1835: End If ;
1836: End If ;
1837:
1838: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1839: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || '--------------------------------------------------------------');
1840: IEX_DEBUG_PUB.LOGMESSAGE('---------->> PROCEDURE MANAGE_DELINQUENCIES End <<----------');
1841: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || '--------------------------------------------------------------');
1842: IEX_DEBUG_PUB.logMessage('MANAGE_DELINQUENCIES: ' || 'End time:' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
1843: END IF;
1836: End If ;
1837:
1838: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1839: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || '--------------------------------------------------------------');
1840: IEX_DEBUG_PUB.LOGMESSAGE('---------->> PROCEDURE MANAGE_DELINQUENCIES End <<----------');
1841: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || '--------------------------------------------------------------');
1842: IEX_DEBUG_PUB.logMessage('MANAGE_DELINQUENCIES: ' || 'End time:' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
1843: END IF;
1844:
1837:
1838: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1839: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || '--------------------------------------------------------------');
1840: IEX_DEBUG_PUB.LOGMESSAGE('---------->> PROCEDURE MANAGE_DELINQUENCIES End <<----------');
1841: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || '--------------------------------------------------------------');
1842: IEX_DEBUG_PUB.logMessage('MANAGE_DELINQUENCIES: ' || 'End time:' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
1843: END IF;
1844:
1845: EXCEPTION
1838: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1839: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || '--------------------------------------------------------------');
1840: IEX_DEBUG_PUB.LOGMESSAGE('---------->> PROCEDURE MANAGE_DELINQUENCIES End <<----------');
1841: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || '--------------------------------------------------------------');
1842: IEX_DEBUG_PUB.logMessage('MANAGE_DELINQUENCIES: ' || 'End time:' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
1843: END IF;
1844:
1845: EXCEPTION
1846: WHEN Others then
1847: RETCODE := -1 ;
1848: ERRBUF := 'MANAGE_DELINQUENCIES >> WHEN OTHERS - ERROR - ' || SQLCODE || ' ' || SQLERRM ;
1849: -- IF PG_DEBUG < 10 THEN
1850: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1851: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || ERRBUF);
1852: END IF;
1853: ROLLBACK TO del_sp ;
1854:
1855: -- IF PG_DEBUG < 10 THEN
1853: ROLLBACK TO del_sp ;
1854:
1855: -- IF PG_DEBUG < 10 THEN
1856: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1857: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || 'Deleting Buffer Table after RollBack due to Error');
1858: END IF;
1859:
1860: --
1861: -- Begin - 01/24/2005 - Andre Araujo - This procedure uses a memory schema uses up all memory available for the session, changing it to chunks
1867: --
1868:
1869: -- IF PG_DEBUG < 10 THEN
1870: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1871: IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || 'Deletd Buffer Row Count >> ' || to_char(SQL%ROWCOUNT));
1872: END IF;
1873:
1874: Commit;
1875: END MANAGE_DELINQUENCIES;
1999: END LOOP ;
2000: --Begin Bug 6446848 08-Dec-2008 barathsr
2001: else
2002:
2003: IEX_DEBUG_PUB.LOGMESSAGE('In for Current invoice insertion....');
2004:
2005: select IEX_DELINQUENCIES_S.NEXTVAL
2006: into l_deln_id
2007: from dual;
2014: into l_org_id,l_cust_acct_id,l_cust_site_use_id,l_cust_trx_id --29/12
2015: from ar_payment_schedules_all
2016: where payment_schedule_id= p_object_id_tbl(i);
2017:
2018: IEX_DEBUG_PUB.LOGMESSAGE('Org_id--->'||l_org_id);
2019: IEX_DEBUG_PUB.LOGMESSAGE('Cust_account_id-->'||l_cust_acct_id);
2020: IEX_DEBUG_PUB.LOGMESSAGE('Customer_site_use_id-->'||l_cust_site_use_id);
2021: IEX_DEBUG_PUB.LOGMESSAGE('Customer_site_use_id-->'||l_cust_trx_id);
2022:
2015: from ar_payment_schedules_all
2016: where payment_schedule_id= p_object_id_tbl(i);
2017:
2018: IEX_DEBUG_PUB.LOGMESSAGE('Org_id--->'||l_org_id);
2019: IEX_DEBUG_PUB.LOGMESSAGE('Cust_account_id-->'||l_cust_acct_id);
2020: IEX_DEBUG_PUB.LOGMESSAGE('Customer_site_use_id-->'||l_cust_site_use_id);
2021: IEX_DEBUG_PUB.LOGMESSAGE('Customer_site_use_id-->'||l_cust_trx_id);
2022:
2023: INSERT INTO IEX_DELINQUENCIES_ALL
2016: where payment_schedule_id= p_object_id_tbl(i);
2017:
2018: IEX_DEBUG_PUB.LOGMESSAGE('Org_id--->'||l_org_id);
2019: IEX_DEBUG_PUB.LOGMESSAGE('Cust_account_id-->'||l_cust_acct_id);
2020: IEX_DEBUG_PUB.LOGMESSAGE('Customer_site_use_id-->'||l_cust_site_use_id);
2021: IEX_DEBUG_PUB.LOGMESSAGE('Customer_site_use_id-->'||l_cust_trx_id);
2022:
2023: INSERT INTO IEX_DELINQUENCIES_ALL
2024: (DELINQUENCY_ID ,
2017:
2018: IEX_DEBUG_PUB.LOGMESSAGE('Org_id--->'||l_org_id);
2019: IEX_DEBUG_PUB.LOGMESSAGE('Cust_account_id-->'||l_cust_acct_id);
2020: IEX_DEBUG_PUB.LOGMESSAGE('Customer_site_use_id-->'||l_cust_site_use_id);
2021: IEX_DEBUG_PUB.LOGMESSAGE('Customer_site_use_id-->'||l_cust_trx_id);
2022:
2023: INSERT INTO IEX_DELINQUENCIES_ALL
2024: (DELINQUENCY_ID ,
2025: LAST_UPDATE_DATE ,
2053: p_source_module,
2054: l_cust_site_use_id) ; --29/12
2055: exception
2056: when others then --29/12
2057: IEX_DEBUG_PUB.LOGMESSAGE('Error in CURRENT invoice selection/insertion activity.....');
2058: raise FND_API.G_EXC_UNEXPECTED_ERROR;
2059: end;--29/12
2060: end loop;
2061: end if;
2072: ROLLBACK TO IEX_IND_DEL ;
2073: END IF;
2074:
2075: -- Debug Message
2076: IEX_DEBUG_PUB.LogMessage('PUB: ' || l_api_name || ' end');
2077: IEX_DEBUG_PUB.LogMessage('End time:' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
2078: FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2079:
2080: EXCEPTION
2073: END IF;
2074:
2075: -- Debug Message
2076: IEX_DEBUG_PUB.LogMessage('PUB: ' || l_api_name || ' end');
2077: IEX_DEBUG_PUB.LogMessage('End time:' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
2078: FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2079:
2080: EXCEPTION
2081: WHEN FND_API.G_EXC_ERROR THEN
2079:
2080: EXCEPTION
2081: WHEN FND_API.G_EXC_ERROR THEN
2082: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2083: IEX_DEBUG_PUB.logMessage('IEX_DELINQUENCY_PUB: Create_Ind_Delinquency: Expected Error ' || sqlerrm);
2084: END IF;
2085: RAISE FND_API.G_EXC_ERROR;
2086: ROLLBACK TO IEX_IND_DEL;
2087:
2086: ROLLBACK TO IEX_IND_DEL;
2087:
2088: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2089: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2090: IEX_DEBUG_PUB.logMessage('IEX_DELINQUENCY_PUB: Create_Ind_Delinquency: Unexpected Error ' || sqlerrm);
2091: END IF;
2092: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2093: ROLLBACK TO IEX_IND_DEL;
2094:
2094:
2095: WHEN OTHERS THEN
2096: v_error_msg := SQLCODE || ' Error Msg ' || SQLERRM ;
2097: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2098: IEX_DEBUG_PUB.LOGMESSAGE(v_error_msg) ;
2099: END IF;
2100: ROLLBACK TO IEX_IND_DEL;
2101: -- Begin - Andre Araujo - 09/30/2004- Remove obsolete logging
2102: --IEX_CONC_REQUEST_MSG_PKG.LOG_ERROR(-9999, 'CREATE_IND_DELINQUENCIES', v_error_msg) ;
2143: RETCODE := 0;
2144:
2145: -- IF PG_DEBUG < 10 THEN
2146: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2147: iex_debug_pub.logMessage('IEX_DELINQUENCY_PUB.SCORE_DELINQUENCIES');
2148: END IF;
2149: -- IF PG_DEBUG < 10 THEN
2150: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2151: iex_debug_pub.logMessage('SCORE_DELINQUENCIES: ' || 'StartTime: ' || to_char(sysdate, 'DD-MON-YYYY HH:MI:SS'));
2147: iex_debug_pub.logMessage('IEX_DELINQUENCY_PUB.SCORE_DELINQUENCIES');
2148: END IF;
2149: -- IF PG_DEBUG < 10 THEN
2150: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2151: iex_debug_pub.logMessage('SCORE_DELINQUENCIES: ' || 'StartTime: ' || to_char(sysdate, 'DD-MON-YYYY HH:MI:SS'));
2152: END IF;
2153: -- get the score id for the engine
2154: Select score_id into l_score_id
2155: from iex_del_buffers
2157: rownum = 1;
2158:
2159: -- IF PG_DEBUG < 10 THEN
2160: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2161: iex_debug_pub.logMessage('SCORE_DELINQUENCIES: ' || 'ScoreID is ' || l_score_id);
2162: END IF;
2163: -- IF PG_DEBUG < 10 THEN
2164: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2165: iex_debug_pub.logMessage('SCORE_DELINQUENCIES: ' || 'bridge value is ' || p_request_id);
2161: iex_debug_pub.logMessage('SCORE_DELINQUENCIES: ' || 'ScoreID is ' || l_score_id);
2162: END IF;
2163: -- IF PG_DEBUG < 10 THEN
2164: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2165: iex_debug_pub.logMessage('SCORE_DELINQUENCIES: ' || 'bridge value is ' || p_request_id);
2166: END IF;
2167:
2168: -- now get the data
2169: open c_scores(p_request_id);
2179: for i in 1..nCount
2180: loop
2181: -- IF PG_DEBUG < 10 THEN
2182: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2183: iex_debug_pub.logMessage('SCORE_DELINQUENCIES: ' || v_score_objects(i));
2184: END IF;
2185: -- IF PG_DEBUG < 10 THEN
2186: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2187: iex_debug_pub.logMessage('SCORE_DELINQUENCIES: ' || v_score_values(i));
2183: iex_debug_pub.logMessage('SCORE_DELINQUENCIES: ' || v_score_objects(i));
2184: END IF;
2185: -- IF PG_DEBUG < 10 THEN
2186: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2187: iex_debug_pub.logMessage('SCORE_DELINQUENCIES: ' || v_score_values(i));
2188: END IF;
2189: end loop;
2190:
2191: -- IF PG_DEBUG < 10 THEN
2189: end loop;
2190:
2191: -- IF PG_DEBUG < 10 THEN
2192: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2193: iex_debug_pub.logMessage('SCORE_DELINQUENCIES: ' || 'Count is ' || nCount);
2194: END IF;
2195: FORALL r in 1..nCount
2196: UPDATE IEX_DELINQUENCIES_ALL
2197: SET SCORE_ID = l_score_id,
2212: WHERE request_id = p_request_id;
2213: */
2214: -- IF PG_DEBUG < 10 THEN
2215: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2216: IEX_DEBUG_PUB.logMessage('SCORE_DELINQUENCIES: ' || 'Deleting from buffers');
2217: END IF;
2218: --
2219: -- Begin - 01/24/2005 - Andre Araujo - This procedure uses a memory schema uses up all memory available for the session, changing it to chunks
2220: --
2226: --
2227:
2228: -- IF PG_DEBUG < 10 THEN
2229: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2230: iex_debug_pub.logMessage('IEX_DELINQUENCY_PUB.SCORE_DELINQUENCIES');
2231: END IF;
2232: -- IF PG_DEBUG < 10 THEN
2233: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2234: iex_debug_pub.logMessage('SCORE_DELINQUENCIES: ' || 'ENDTime: ' || to_char(sysdate, 'DD-MON-YYYY HH:MI:SS'));
2230: iex_debug_pub.logMessage('IEX_DELINQUENCY_PUB.SCORE_DELINQUENCIES');
2231: END IF;
2232: -- IF PG_DEBUG < 10 THEN
2233: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2234: iex_debug_pub.logMessage('SCORE_DELINQUENCIES: ' || 'ENDTime: ' || to_char(sysdate, 'DD-MON-YYYY HH:MI:SS'));
2235: END IF;
2236: Exception
2237: When NO_DATA_FOUND then
2238: -- IF PG_DEBUG < 10 THEN
2236: Exception
2237: When NO_DATA_FOUND then
2238: -- IF PG_DEBUG < 10 THEN
2239: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2240: iex_debug_pub.logMessage('SCORE_DELINQUENCIES: ' || 'no data found ' || sqlerrm);
2241: END IF;
2242: RETCODE := -1;
2243: ERRBUF := sqlerrm;
2244: When others then
2243: ERRBUF := sqlerrm;
2244: When others then
2245: -- IF PG_DEBUG < 10 THEN
2246: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2247: iex_debug_pub.logMessage('SCORE_DELINQUENCIES: ' || 'other error ' || sqlerrm);
2248: END IF;
2249: RETCODE := -1;
2250: ERRBUF := sqlerrm;
2251:
2293:
2294: begin
2295:
2296: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2297: iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': start');
2298: END IF;
2299:
2300: -- Standard start of API savepoint
2301: SAVEPOINT SHOW_IN_UWQ_PVT;
2317:
2318: -- validating uwq status
2319: l_validation_item := 'P_UWQ_STATUS';
2320: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2321: iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': new uwq status: ' || P_UWQ_STATUS);
2322: iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Validating ' || l_validation_item);
2323: END IF;
2324: if P_UWQ_STATUS is null then
2325: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2318: -- validating uwq status
2319: l_validation_item := 'P_UWQ_STATUS';
2320: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2321: iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': new uwq status: ' || P_UWQ_STATUS);
2322: iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Validating ' || l_validation_item);
2323: END IF;
2324: if P_UWQ_STATUS is null then
2325: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2326: iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': ' || l_validation_item || ' failed validation');
2322: iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Validating ' || l_validation_item);
2323: END IF;
2324: if P_UWQ_STATUS is null then
2325: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2326: iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': ' || l_validation_item || ' failed validation');
2327: END IF;
2328: FND_MESSAGE.SET_NAME('IEX','IEX_BAD_API_INPUT');
2329: FND_MESSAGE.SET_TOKEN('API_NAME', G_PKG_NAME || '.' || l_api_name);
2330: FND_MESSAGE.SET_TOKEN('API_PARAMETER', l_validation_item);
2334:
2335: -- validating table of promises
2336: l_validation_item := 'P_DELINQUENCY_ID_TBL';
2337: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2338: iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': count of P_DELINQUENCY_ID_TBL: ' || P_DELINQUENCY_ID_TBL.count);
2339: iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Validating ' || l_validation_item);
2340: END IF;
2341: if P_DELINQUENCY_ID_TBL.count = 0 then
2342: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2335: -- validating table of promises
2336: l_validation_item := 'P_DELINQUENCY_ID_TBL';
2337: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2338: iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': count of P_DELINQUENCY_ID_TBL: ' || P_DELINQUENCY_ID_TBL.count);
2339: iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Validating ' || l_validation_item);
2340: END IF;
2341: if P_DELINQUENCY_ID_TBL.count = 0 then
2342: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2343: iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': ' || l_validation_item || ' failed validation');
2339: iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Validating ' || l_validation_item);
2340: END IF;
2341: if P_DELINQUENCY_ID_TBL.count = 0 then
2342: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2343: iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': ' || l_validation_item || ' failed validation');
2344: END IF;
2345: FND_MESSAGE.SET_NAME('IEX','IEX_BAD_API_INPUT');
2346: FND_MESSAGE.SET_TOKEN('API_NAME', G_PKG_NAME || '.' || l_api_name);
2347: FND_MESSAGE.SET_TOKEN('API_PARAMETER', l_validation_item);
2351:
2352: -- validating p_days
2353: l_validation_item := 'P_NO_DAYS';
2354: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2355: iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': P_NO_DAYS: ' || P_NO_DAYS);
2356: iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Validating ' || l_validation_item);
2357: END IF;
2358: if P_NO_DAYS is not null and P_NO_DAYS < 0 then
2359: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2352: -- validating p_days
2353: l_validation_item := 'P_NO_DAYS';
2354: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2355: iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': P_NO_DAYS: ' || P_NO_DAYS);
2356: iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Validating ' || l_validation_item);
2357: END IF;
2358: if P_NO_DAYS is not null and P_NO_DAYS < 0 then
2359: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2360: iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': ' || l_validation_item || ' failed validation');
2356: iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Validating ' || l_validation_item);
2357: END IF;
2358: if P_NO_DAYS is not null and P_NO_DAYS < 0 then
2359: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2360: iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': ' || l_validation_item || ' failed validation');
2361: END IF;
2362: FND_MESSAGE.SET_NAME('IEX','IEX_BAD_API_INPUT');
2363: FND_MESSAGE.SET_TOKEN('API_NAME', G_PKG_NAME || '.' || l_api_name);
2364: FND_MESSAGE.SET_TOKEN('API_PARAMETER', l_validation_item);
2372: else
2373: l_days := P_NO_DAYS;
2374: end if;
2375: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2376: iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': number of days: ' || l_days);
2377: END IF;
2378: l_set_status_date := sysdate + l_days;
2379: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2380: iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': set status date: ' || l_set_status_date);
2376: iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': number of days: ' || l_days);
2377: END IF;
2378: l_set_status_date := sysdate + l_days;
2379: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2380: iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': set status date: ' || l_set_status_date);
2381: END IF;
2382:
2383: -- check for status
2384: if P_UWQ_STATUS = 'ACTIVE' then
2394:
2395: nCount := p_delinquency_id_tbl.count;
2396: if nCount > 0 then
2397: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2398: iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': l_uwq_active_date: ' || l_uwq_active_date);
2399: iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': l_uwq_complete_date: ' || l_uwq_complete_date);
2400: iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': updating promise details...');
2401: END IF;
2402: FORALL i in 1..nCount
2395: nCount := p_delinquency_id_tbl.count;
2396: if nCount > 0 then
2397: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2398: iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': l_uwq_active_date: ' || l_uwq_active_date);
2399: iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': l_uwq_complete_date: ' || l_uwq_complete_date);
2400: iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': updating promise details...');
2401: END IF;
2402: FORALL i in 1..nCount
2403: update iex_delinquencies_all
2396: if nCount > 0 then
2397: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2398: iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': l_uwq_active_date: ' || l_uwq_active_date);
2399: iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': l_uwq_complete_date: ' || l_uwq_complete_date);
2400: iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': updating promise details...');
2401: END IF;
2402: FORALL i in 1..nCount
2403: update iex_delinquencies_all
2404: set UWQ_STATUS = P_UWQ_STATUS,
2412: -- start of fix for bug 5874874 gnramasa 25-Apr-07
2413: OPEN c_get_level;
2414: FETCH c_get_level INTO l_level;
2415: CLOSE c_get_level;
2416: iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Strateg Level = ' || l_level);
2417:
2418: IF l_level = 'CUSTOMER' THEN
2419:
2420: FORALL i in 1..nCount
2545: where delinquency_id = p_delinquency_id_tbl(i));
2546:
2547: END IF;
2548:
2549: iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Updated ' || SQL%ROWCOUNT || ' rows in IEX_DLN_UWQ_SUMMARY');
2550: -- end of fix for bug 5874874 gnramasa 25-Apr-07
2551:
2552: else
2553: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2550: -- end of fix for bug 5874874 gnramasa 25-Apr-07
2551:
2552: else
2553: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2554: iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': nothing to update');
2555: END IF;
2556: end if;
2557:
2558: -- END OF BODY OF API
2609: RETCODE := 0 ;
2610:
2611: -- IF PG_DEBUG < 10 THEN
2612: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2613: IEX_DEBUG_PUB.LOGMESSAGE(v_line);
2614: IEX_DEBUG_PUB.LOGMESSAGE('---------->> PROCEDURE CLOSE_DUNNINGS Start <<----------');
2615: IEX_DEBUG_PUB.logMessage('CLOSE_DUNNINGS : ' || 'Start time:' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
2616: IEX_DEBUG_PUB.LOGMESSAGE(v_line);
2617: END IF;
2610:
2611: -- IF PG_DEBUG < 10 THEN
2612: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2613: IEX_DEBUG_PUB.LOGMESSAGE(v_line);
2614: IEX_DEBUG_PUB.LOGMESSAGE('---------->> PROCEDURE CLOSE_DUNNINGS Start <<----------');
2615: IEX_DEBUG_PUB.logMessage('CLOSE_DUNNINGS : ' || 'Start time:' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
2616: IEX_DEBUG_PUB.LOGMESSAGE(v_line);
2617: END IF;
2618:
2611: -- IF PG_DEBUG < 10 THEN
2612: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2613: IEX_DEBUG_PUB.LOGMESSAGE(v_line);
2614: IEX_DEBUG_PUB.LOGMESSAGE('---------->> PROCEDURE CLOSE_DUNNINGS Start <<----------');
2615: IEX_DEBUG_PUB.logMessage('CLOSE_DUNNINGS : ' || 'Start time:' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
2616: IEX_DEBUG_PUB.LOGMESSAGE(v_line);
2617: END IF;
2618:
2619: if dunning_level in ('ALL', 'DELINQUENCY') then
2612: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2613: IEX_DEBUG_PUB.LOGMESSAGE(v_line);
2614: IEX_DEBUG_PUB.LOGMESSAGE('---------->> PROCEDURE CLOSE_DUNNINGS Start <<----------');
2615: IEX_DEBUG_PUB.logMessage('CLOSE_DUNNINGS : ' || 'Start time:' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
2616: IEX_DEBUG_PUB.LOGMESSAGE(v_line);
2617: END IF;
2618:
2619: if dunning_level in ('ALL', 'DELINQUENCY') then
2620: Begin
2635: ERRBUF := 'CLOSE DUNNINGS << DELINQUENCY >> Error Code = '
2636: || SQLCODE || ' Error Msg ' || SQLERRM ;
2637: -- IF PG_DEBUG < 10 THEN
2638: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2639: IEX_DEBUG_PUB.LOGMESSAGE('CLOSE_DUNNINGS for Delinquency : ' || ERRBUF);
2640: END IF;
2641: RETCODE := -1 ;
2642: ROLLBACK TO close_dunn ;
2643:
2642: ROLLBACK TO close_dunn ;
2643:
2644: -- IF PG_DEBUG < 10 THEN
2645: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2646: IEX_DEBUG_PUB.LOGMESSAGE(v_line);
2647: IEX_DEBUG_PUB.LOGMESSAGE('---------->> PROCEDURE CLOSE_DUNNINGS End ERROR <<----------');
2648: IEX_DEBUG_PUB.logMessage('CLOSE_DUNNINGS : ' || 'End time:' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
2649: IEX_DEBUG_PUB.LOGMESSAGE(v_line);
2650: END IF;
2643:
2644: -- IF PG_DEBUG < 10 THEN
2645: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2646: IEX_DEBUG_PUB.LOGMESSAGE(v_line);
2647: IEX_DEBUG_PUB.LOGMESSAGE('---------->> PROCEDURE CLOSE_DUNNINGS End ERROR <<----------');
2648: IEX_DEBUG_PUB.logMessage('CLOSE_DUNNINGS : ' || 'End time:' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
2649: IEX_DEBUG_PUB.LOGMESSAGE(v_line);
2650: END IF;
2651: End ;
2644: -- IF PG_DEBUG < 10 THEN
2645: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2646: IEX_DEBUG_PUB.LOGMESSAGE(v_line);
2647: IEX_DEBUG_PUB.LOGMESSAGE('---------->> PROCEDURE CLOSE_DUNNINGS End ERROR <<----------');
2648: IEX_DEBUG_PUB.logMessage('CLOSE_DUNNINGS : ' || 'End time:' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
2649: IEX_DEBUG_PUB.LOGMESSAGE(v_line);
2650: END IF;
2651: End ;
2652: end if ;
2645: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2646: IEX_DEBUG_PUB.LOGMESSAGE(v_line);
2647: IEX_DEBUG_PUB.LOGMESSAGE('---------->> PROCEDURE CLOSE_DUNNINGS End ERROR <<----------');
2648: IEX_DEBUG_PUB.logMessage('CLOSE_DUNNINGS : ' || 'End time:' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
2649: IEX_DEBUG_PUB.LOGMESSAGE(v_line);
2650: END IF;
2651: End ;
2652: end if ;
2653:
2684: ERRBUF := 'CLOSE DUNNINGS << BILL_TO >> Error Code = '
2685: || SQLCODE || ' Error Msg ' || SQLERRM ;
2686: -- IF PG_DEBUG < 10 THEN
2687: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2688: IEX_DEBUG_PUB.LOGMESSAGE('CLOSE_DUNNINGS for Bill To : ' || ERRBUF);
2689: END IF;
2690: RETCODE := -1 ;
2691: ROLLBACK TO close_dunn ;
2692:
2691: ROLLBACK TO close_dunn ;
2692:
2693: -- IF PG_DEBUG < 10 THEN
2694: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2695: IEX_DEBUG_PUB.LOGMESSAGE(v_line);
2696: IEX_DEBUG_PUB.LOGMESSAGE('---------->> PROCEDURE CLOSE_DUNNINGS End ERROR <<----------');
2697: IEX_DEBUG_PUB.logMessage('CLOSE_DUNNINGS : ' || 'End time:' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
2698: IEX_DEBUG_PUB.LOGMESSAGE(v_line);
2699: END IF;
2692:
2693: -- IF PG_DEBUG < 10 THEN
2694: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2695: IEX_DEBUG_PUB.LOGMESSAGE(v_line);
2696: IEX_DEBUG_PUB.LOGMESSAGE('---------->> PROCEDURE CLOSE_DUNNINGS End ERROR <<----------');
2697: IEX_DEBUG_PUB.logMessage('CLOSE_DUNNINGS : ' || 'End time:' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
2698: IEX_DEBUG_PUB.LOGMESSAGE(v_line);
2699: END IF;
2700: End ;
2693: -- IF PG_DEBUG < 10 THEN
2694: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2695: IEX_DEBUG_PUB.LOGMESSAGE(v_line);
2696: IEX_DEBUG_PUB.LOGMESSAGE('---------->> PROCEDURE CLOSE_DUNNINGS End ERROR <<----------');
2697: IEX_DEBUG_PUB.logMessage('CLOSE_DUNNINGS : ' || 'End time:' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
2698: IEX_DEBUG_PUB.LOGMESSAGE(v_line);
2699: END IF;
2700: End ;
2701: end if ;
2694: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2695: IEX_DEBUG_PUB.LOGMESSAGE(v_line);
2696: IEX_DEBUG_PUB.LOGMESSAGE('---------->> PROCEDURE CLOSE_DUNNINGS End ERROR <<----------');
2697: IEX_DEBUG_PUB.logMessage('CLOSE_DUNNINGS : ' || 'End time:' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
2698: IEX_DEBUG_PUB.LOGMESSAGE(v_line);
2699: END IF;
2700: End ;
2701: end if ;
2702: -- clchang updated 04/18/2003 for BILL_TO level -- end
2723: ERRBUF := 'CLOSE DUNNINGS << ACCOUNT >> Error Code = '
2724: || SQLCODE || ' Error Msg ' || SQLERRM ;
2725: -- IF PG_DEBUG < 10 THEN
2726: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2727: IEX_DEBUG_PUB.LOGMESSAGE('CLOSE_DUNNINGS for Account : ' || ERRBUF);
2728: END IF;
2729: RETCODE := -1 ;
2730: ROLLBACK TO close_dunn ;
2731:
2730: ROLLBACK TO close_dunn ;
2731:
2732: -- IF PG_DEBUG < 10 THEN
2733: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2734: IEX_DEBUG_PUB.LOGMESSAGE(v_line);
2735: IEX_DEBUG_PUB.LOGMESSAGE('---------->> PROCEDURE CLOSE_DUNNINGS End ERROR <<----------');
2736: IEX_DEBUG_PUB.logMessage('CLOSE_DUNNINGS : ' || 'End time:' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
2737: IEX_DEBUG_PUB.LOGMESSAGE(v_line);
2738: END IF;
2731:
2732: -- IF PG_DEBUG < 10 THEN
2733: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2734: IEX_DEBUG_PUB.LOGMESSAGE(v_line);
2735: IEX_DEBUG_PUB.LOGMESSAGE('---------->> PROCEDURE CLOSE_DUNNINGS End ERROR <<----------');
2736: IEX_DEBUG_PUB.logMessage('CLOSE_DUNNINGS : ' || 'End time:' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
2737: IEX_DEBUG_PUB.LOGMESSAGE(v_line);
2738: END IF;
2739: End ;
2732: -- IF PG_DEBUG < 10 THEN
2733: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2734: IEX_DEBUG_PUB.LOGMESSAGE(v_line);
2735: IEX_DEBUG_PUB.LOGMESSAGE('---------->> PROCEDURE CLOSE_DUNNINGS End ERROR <<----------');
2736: IEX_DEBUG_PUB.logMessage('CLOSE_DUNNINGS : ' || 'End time:' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
2737: IEX_DEBUG_PUB.LOGMESSAGE(v_line);
2738: END IF;
2739: End ;
2740: end if ;
2733: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2734: IEX_DEBUG_PUB.LOGMESSAGE(v_line);
2735: IEX_DEBUG_PUB.LOGMESSAGE('---------->> PROCEDURE CLOSE_DUNNINGS End ERROR <<----------');
2736: IEX_DEBUG_PUB.logMessage('CLOSE_DUNNINGS : ' || 'End time:' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
2737: IEX_DEBUG_PUB.LOGMESSAGE(v_line);
2738: END IF;
2739: End ;
2740: end if ;
2741:
2777: ERRBUF := 'CLOSE DUNNINGS << CUSTOMER >> Error Code = '
2778: || SQLCODE || ' Error Msg ' || SQLERRM ;
2779: -- IF PG_DEBUG < 10 THEN
2780: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2781: IEX_DEBUG_PUB.LOGMESSAGE('CLOSE_DUNNINGS for Customer : ' || ERRBUF);
2782: END IF;
2783: RETCODE := -1 ;
2784: ROLLBACK TO close_dunn ;
2785:
2784: ROLLBACK TO close_dunn ;
2785:
2786: -- IF PG_DEBUG < 10 THEN
2787: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2788: IEX_DEBUG_PUB.LOGMESSAGE(v_line);
2789: IEX_DEBUG_PUB.LOGMESSAGE('---------->> PROCEDURE CLOSE_DUNNINGS End ERROR <<----------');
2790: IEX_DEBUG_PUB.logMessage('CLOSE_DUNNINGS : ' || 'End time:' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
2791: IEX_DEBUG_PUB.LOGMESSAGE(v_line);
2792: END IF;
2785:
2786: -- IF PG_DEBUG < 10 THEN
2787: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2788: IEX_DEBUG_PUB.LOGMESSAGE(v_line);
2789: IEX_DEBUG_PUB.LOGMESSAGE('---------->> PROCEDURE CLOSE_DUNNINGS End ERROR <<----------');
2790: IEX_DEBUG_PUB.logMessage('CLOSE_DUNNINGS : ' || 'End time:' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
2791: IEX_DEBUG_PUB.LOGMESSAGE(v_line);
2792: END IF;
2793: End ;
2786: -- IF PG_DEBUG < 10 THEN
2787: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2788: IEX_DEBUG_PUB.LOGMESSAGE(v_line);
2789: IEX_DEBUG_PUB.LOGMESSAGE('---------->> PROCEDURE CLOSE_DUNNINGS End ERROR <<----------');
2790: IEX_DEBUG_PUB.logMessage('CLOSE_DUNNINGS : ' || 'End time:' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
2791: IEX_DEBUG_PUB.LOGMESSAGE(v_line);
2792: END IF;
2793: End ;
2794: end if ;
2787: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2788: IEX_DEBUG_PUB.LOGMESSAGE(v_line);
2789: IEX_DEBUG_PUB.LOGMESSAGE('---------->> PROCEDURE CLOSE_DUNNINGS End ERROR <<----------');
2790: IEX_DEBUG_PUB.logMessage('CLOSE_DUNNINGS : ' || 'End time:' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
2791: IEX_DEBUG_PUB.LOGMESSAGE(v_line);
2792: END IF;
2793: End ;
2794: end if ;
2795:
2796: Commit;
2797:
2798: -- IF PG_DEBUG < 10 THEN
2799: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2800: IEX_DEBUG_PUB.LOGMESSAGE('CLOSE_DUNNINGS : ' || v_line);
2801: IEX_DEBUG_PUB.LOGMESSAGE('---------->> PROCEDURE CLOSE_DUNNINGS End NORMAL <<----------');
2802: IEX_DEBUG_PUB.LOGMESSAGE('CLOSE_DUNNINGS : ' || v_line);
2803: IEX_DEBUG_PUB.logMessage('CLOSE_DUNNINGS : ' || 'End time:' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
2804: END IF;
2797:
2798: -- IF PG_DEBUG < 10 THEN
2799: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2800: IEX_DEBUG_PUB.LOGMESSAGE('CLOSE_DUNNINGS : ' || v_line);
2801: IEX_DEBUG_PUB.LOGMESSAGE('---------->> PROCEDURE CLOSE_DUNNINGS End NORMAL <<----------');
2802: IEX_DEBUG_PUB.LOGMESSAGE('CLOSE_DUNNINGS : ' || v_line);
2803: IEX_DEBUG_PUB.logMessage('CLOSE_DUNNINGS : ' || 'End time:' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
2804: END IF;
2805:
2798: -- IF PG_DEBUG < 10 THEN
2799: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2800: IEX_DEBUG_PUB.LOGMESSAGE('CLOSE_DUNNINGS : ' || v_line);
2801: IEX_DEBUG_PUB.LOGMESSAGE('---------->> PROCEDURE CLOSE_DUNNINGS End NORMAL <<----------');
2802: IEX_DEBUG_PUB.LOGMESSAGE('CLOSE_DUNNINGS : ' || v_line);
2803: IEX_DEBUG_PUB.logMessage('CLOSE_DUNNINGS : ' || 'End time:' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
2804: END IF;
2805:
2806: Exception
2799: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2800: IEX_DEBUG_PUB.LOGMESSAGE('CLOSE_DUNNINGS : ' || v_line);
2801: IEX_DEBUG_PUB.LOGMESSAGE('---------->> PROCEDURE CLOSE_DUNNINGS End NORMAL <<----------');
2802: IEX_DEBUG_PUB.LOGMESSAGE('CLOSE_DUNNINGS : ' || v_line);
2803: IEX_DEBUG_PUB.logMessage('CLOSE_DUNNINGS : ' || 'End time:' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
2804: END IF;
2805:
2806: Exception
2807: WHEN OTHERS then
2807: WHEN OTHERS then
2808: ERRBUF := 'CLOSE DUNNINGS - Error Code = ' || SQLCODE || ' Error Msg ' || SQLERRM ;
2809: -- IF PG_DEBUG < 10 THEN
2810: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2811: IEX_DEBUG_PUB.LOGMESSAGE('CLOSE_DUNNINGS : ' || ERRBUF);
2812: END IF;
2813: RETCODE := -1 ;
2814: ROLLBACK TO close_dunn ;
2815:
2814: ROLLBACK TO close_dunn ;
2815:
2816: -- IF PG_DEBUG < 10 THEN
2817: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2818: IEX_DEBUG_PUB.LOGMESSAGE(v_line);
2819: IEX_DEBUG_PUB.LOGMESSAGE('---------->> PROCEDURE CLOSE_DUNNINGS End ERROR <<----------');
2820: IEX_DEBUG_PUB.logMessage('CLOSE_DUNNINGS : ' || 'End time:' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
2821: IEX_DEBUG_PUB.LOGMESSAGE(v_line);
2822: END IF;
2815:
2816: -- IF PG_DEBUG < 10 THEN
2817: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2818: IEX_DEBUG_PUB.LOGMESSAGE(v_line);
2819: IEX_DEBUG_PUB.LOGMESSAGE('---------->> PROCEDURE CLOSE_DUNNINGS End ERROR <<----------');
2820: IEX_DEBUG_PUB.logMessage('CLOSE_DUNNINGS : ' || 'End time:' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
2821: IEX_DEBUG_PUB.LOGMESSAGE(v_line);
2822: END IF;
2823: END ;
2816: -- IF PG_DEBUG < 10 THEN
2817: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2818: IEX_DEBUG_PUB.LOGMESSAGE(v_line);
2819: IEX_DEBUG_PUB.LOGMESSAGE('---------->> PROCEDURE CLOSE_DUNNINGS End ERROR <<----------');
2820: IEX_DEBUG_PUB.logMessage('CLOSE_DUNNINGS : ' || 'End time:' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
2821: IEX_DEBUG_PUB.LOGMESSAGE(v_line);
2822: END IF;
2823: END ;
2824:
2817: IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2818: IEX_DEBUG_PUB.LOGMESSAGE(v_line);
2819: IEX_DEBUG_PUB.LOGMESSAGE('---------->> PROCEDURE CLOSE_DUNNINGS End ERROR <<----------');
2820: IEX_DEBUG_PUB.logMessage('CLOSE_DUNNINGS : ' || 'End time:' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
2821: IEX_DEBUG_PUB.LOGMESSAGE(v_line);
2822: END IF;
2823: END ;
2824:
2825: --
2857: x boolean;
2858:
2859: Begin
2860:
2861: IEX_DEBUG_PUB.logMessage('IEX_DELINQUENCY_PUB: cleaning delinquency buffers +');
2862: FND_FILE.PUT_LINE(FND_FILE.LOG,'IEX_DELINQUENCY_PUB: cleaning delinquency buffers +');
2863:
2864: select count(1) into l_del_count
2865: from fnd_conc_req_summary_v
2867: program_short_name in ('IEXDLMGB', 'IEX_SCORE_OBJECTS') and
2868: phase_code in ('P', 'R')
2869: and status_code <> 'Q'; -- changed for bug 9251590
2870:
2871: IEX_DEBUG_PUB.logMessage('Running IEXDLMGB count = ' || l_del_count);
2872: FND_FILE.PUT_LINE(FND_FILE.LOG,'Running IEXDLMGB count = ' || l_del_count);
2873:
2874: if (((p_request = -1) and (l_del_count = 0)) OR ((p_request <> -1) and (l_del_count <= 2))) then
2875: if ((p_request = -1) and (l_del_count = 0)) then
2872: FND_FILE.PUT_LINE(FND_FILE.LOG,'Running IEXDLMGB count = ' || l_del_count);
2873:
2874: if (((p_request = -1) and (l_del_count = 0)) OR ((p_request <> -1) and (l_del_count <= 2))) then
2875: if ((p_request = -1) and (l_del_count = 0)) then
2876: IEX_DEBUG_PUB.logMessage('IEX: Clear Delinquency Buffers Table cp is running and IEX: Scoring Engine Harness cp is not running, so truncating IEX_DEL_BUFFERS table...');
2877: FND_FILE.PUT_LINE(FND_FILE.LOG,'IEX: Clear Delinquency Buffers Table cp is running and IEX: Scoring Engine Harness cp is not running, so truncating IEX_DEL_BUFFERS table...');
2878: else
2879: IEX_DEBUG_PUB.logMessage('This is the last running instance of IEXDLMGB - truncating IEX_DEL_BUFFERS table...');
2880: FND_FILE.PUT_LINE(FND_FILE.LOG,'This is the last running instance of IEXDLMGB - truncating IEX_DEL_BUFFERS table...');
2875: if ((p_request = -1) and (l_del_count = 0)) then
2876: IEX_DEBUG_PUB.logMessage('IEX: Clear Delinquency Buffers Table cp is running and IEX: Scoring Engine Harness cp is not running, so truncating IEX_DEL_BUFFERS table...');
2877: FND_FILE.PUT_LINE(FND_FILE.LOG,'IEX: Clear Delinquency Buffers Table cp is running and IEX: Scoring Engine Harness cp is not running, so truncating IEX_DEL_BUFFERS table...');
2878: else
2879: IEX_DEBUG_PUB.logMessage('This is the last running instance of IEXDLMGB - truncating IEX_DEL_BUFFERS table...');
2880: FND_FILE.PUT_LINE(FND_FILE.LOG,'This is the last running instance of IEXDLMGB - truncating IEX_DEL_BUFFERS table...');
2881: end if;
2882: --select OWNER || '.' || TABLE_NAME into l_truncate_table from sys.all_tables where table_name = 'IEX_DEL_BUFFERS';
2883: --EXECUTE IMMEDIATE 'truncate table ' || l_truncate_table;
2882: --select OWNER || '.' || TABLE_NAME into l_truncate_table from sys.all_tables where table_name = 'IEX_DEL_BUFFERS';
2883: --EXECUTE IMMEDIATE 'truncate table ' || l_truncate_table;
2884: x := fnd_installation.get_app_info ('FND',l_out_status,l_out_industry,l_out_oracle_schema);
2885:
2886: IEX_DEBUG_PUB.logMessage('IEX Schema:'||l_out_oracle_schema);
2887:
2888: IEX_DEBUG_PUB.LogMessage('Truncating IEX_DEL_BUFFERS');
2889:
2890: ad_ddl.do_ddl( l_out_oracle_schema,
2884: x := fnd_installation.get_app_info ('FND',l_out_status,l_out_industry,l_out_oracle_schema);
2885:
2886: IEX_DEBUG_PUB.logMessage('IEX Schema:'||l_out_oracle_schema);
2887:
2888: IEX_DEBUG_PUB.LogMessage('Truncating IEX_DEL_BUFFERS');
2889:
2890: ad_ddl.do_ddl( l_out_oracle_schema,
2891: 'IEX',
2892: AD_DDL.TRUNCATE_TABLE,
2892: AD_DDL.TRUNCATE_TABLE,
2893: 'TRUNCATE TABLE IEX_DEL_BUFFERS',
2894: 'IEX_DEL_BUFFERS' );
2895:
2896: IEX_DEBUG_PUB.LogMessage('Truncated IEX_DEL_BUFFERS');
2897: IEX_DEBUG_PUB.logMessage('Done');
2898: else
2899: IEX_DEBUG_PUB.logMessage('There are other running instances of IEXDLMGB - quiting');
2900: FND_FILE.PUT_LINE(FND_FILE.LOG,'There are other running instances of IEXDLMGB - quiting');
2893: 'TRUNCATE TABLE IEX_DEL_BUFFERS',
2894: 'IEX_DEL_BUFFERS' );
2895:
2896: IEX_DEBUG_PUB.LogMessage('Truncated IEX_DEL_BUFFERS');
2897: IEX_DEBUG_PUB.logMessage('Done');
2898: else
2899: IEX_DEBUG_PUB.logMessage('There are other running instances of IEXDLMGB - quiting');
2900: FND_FILE.PUT_LINE(FND_FILE.LOG,'There are other running instances of IEXDLMGB - quiting');
2901: end if;
2895:
2896: IEX_DEBUG_PUB.LogMessage('Truncated IEX_DEL_BUFFERS');
2897: IEX_DEBUG_PUB.logMessage('Done');
2898: else
2899: IEX_DEBUG_PUB.logMessage('There are other running instances of IEXDLMGB - quiting');
2900: FND_FILE.PUT_LINE(FND_FILE.LOG,'There are other running instances of IEXDLMGB - quiting');
2901: end if;
2902:
2903: IEX_DEBUG_PUB.logMessage('IEX_DELINQUENCY_PUB: cleaning delinquency buffers -');
2899: IEX_DEBUG_PUB.logMessage('There are other running instances of IEXDLMGB - quiting');
2900: FND_FILE.PUT_LINE(FND_FILE.LOG,'There are other running instances of IEXDLMGB - quiting');
2901: end if;
2902:
2903: IEX_DEBUG_PUB.logMessage('IEX_DELINQUENCY_PUB: cleaning delinquency buffers -');
2904: FND_FILE.PUT_LINE(FND_FILE.LOG,'IEX_DELINQUENCY_PUB: cleaning delinquency buffers -');
2905:
2906: Exception
2907: When others then
2904: FND_FILE.PUT_LINE(FND_FILE.LOG,'IEX_DELINQUENCY_PUB: cleaning delinquency buffers -');
2905:
2906: Exception
2907: When others then
2908: IEX_DEBUG_PUB.logMessage('IEX_DELINQUENCY_PUB: CLEAR_BUFFERS2: cleaning failed due to ' || sqlcode || ' ' || sqlerrm);
2909: FND_FILE.PUT_LINE(FND_FILE.LOG, 'IEX_DELINQUENCY_PUB: CLEAR_BUFFERS2: cleaning failed due to ' || sqlcode || ' ' || sqlerrm);
2910:
2911:
2912: End CLEAR_BUFFERS2;