[Home] [Help]
PACKAGE BODY: APPS.IEX_PROMISES_BATCH_PUB
Source
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';
9 G_LOGIN_ID NUMBER; -- := FND_GLOBAL.Conc_Login_Id;
10 G_PROGRAM_ID NUMBER; -- := FND_GLOBAL.Conc_Program_Id;
11 G_USER_ID NUMBER; -- := FND_GLOBAL.User_Id;
12 G_REQUEST_ID NUMBER; -- := FND_GLOBAL.Conc_Request_Id;
13
14 G_TASK_REFERENCE_TAB JTF_TASKS_PUB.TASK_REFER_TBL;
15
16
17 /**********************
18 This procedure logging messages
19 ***********************/
20 Procedure LogMessage(p_msg in varchar2)
21 IS
22 BEGIN
23 /*
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
33 /**********************
34 This procedure calculate callback date
35 ***********************/
36 Procedure Get_Callback_Date(p_promise_date in date, x_callback_date OUT NOCOPY DATE)
37 IS
38 l_result NUMBER;
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'));
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;
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
60 Execute Immediate
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
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;
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';
86 Execute Immediate
87 vSQL
88 into l_result1 using p_promise_date, l_callback_days;
89
90 x_callback_date := l_result1;
91
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
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
107 /*
108 IEX_CONC_REQUEST_MSG_PKG.Log_Error(
109 P_Concurrent_Request_ID => FND_GLOBAL.CONC_REQUEST_ID,
110 P_Procedure_name => 'IEX_PROMISES_BATCH_PUB.GET_CALLBACK_DATE',
111 P_MESSAGE => 'Cannot Get Callback_Date. Assing sysdate.' );
112 */
113 -- End - Andre Araujo - 09/30/2004- Remove obsolete logging
114
115 END;
116
117 /**********************
118 This procedure closes all open promises for payment schedules that have been closed.
119 ***********************/
120 PROCEDURE CLOSE_PROMISES(
121 P_API_VERSION IN NUMBER,
122 P_INIT_MSG_LIST IN VARCHAR2,
123 P_COMMIT IN VARCHAR2,
124 P_VALIDATION_LEVEL IN NUMBER,
125 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
126 X_MSG_COUNT OUT NOCOPY NUMBER,
127 X_MSG_DATA OUT NOCOPY VARCHAR2,
128 p_payments_tbl IN IEX_PAYMENTS_BATCH_PUB.CL_INV_TBL_TYPE)
129 IS
130 l_api_name CONSTANT VARCHAR2(30) := 'CLOSE_PROMISES';
131 l_api_version CONSTANT NUMBER := 1.0;
132 l_return_status VARCHAR2(1);
133 l_msg_count NUMBER;
134 l_msg_data VARCHAR2(32767);
135
136 i number := 0;
137 k number := 0;
138 l_cr_id number;
139 l_promise_detail_id number;
140
141 CURSOR get_cl_pro_crs(P_PAYMENT_SCHEDULE_ID NUMBER)
142 IS
143 SELECT
144 PRD.PROMISE_DETAIL_ID
145 FROM
146 IEX_PROMISE_DETAILS PRD,
147 IEX_DELINQUENCIES_ALL DEL
148 WHERE
149 DEL.DELINQUENCY_ID = PRD.DELINQUENCY_ID AND
150 DEL.CUST_ACCOUNT_ID = PRD.CUST_ACCOUNT_ID AND
151 DEL.PAYMENT_SCHEDULE_ID = P_PAYMENT_SCHEDULE_ID AND
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
161 /*
162 Commented out whole procedure because PROB now can apply payments to promises or
163 reverse payments from promises automatically.
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
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
181 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
182 END IF;
183
184 -- Initialize message list if p_init_msg_list is set to TRUE
185 IF FND_API.To_Boolean(p_init_msg_list) THEN
186 FND_MSG_PUB.initialize;
187 END IF;
188
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
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
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
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
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',
227 last_update_date = sysdate,
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 /*
237 --IEX_CONC_REQUEST_MSG_PKG.Log_Error(
238 -- P_Concurrent_Request_ID => FND_GLOBAL.CONC_REQUEST_ID,
239 -- P_Procedure_name => G_PKG_NAME || '.' || l_api_name,
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;
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
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
260 -- Standard check of p_commit.
261 IF FND_API.To_Boolean( p_commit ) THEN
262 COMMIT WORK;
263 END IF;
264
265 x_return_status := l_return_status;
266 -- Standard call to get message count and if count is 1, get message info
267 FND_MSG_PUB.Count_And_Get(
268 p_encoded => FND_API.G_FALSE,
269 p_count => x_msg_count,
270 p_data => x_msg_data);
271
272 EXCEPTION
273 WHEN FND_API.G_EXC_ERROR THEN
274 ROLLBACK TO CLOSE_PROMISES_PVT;
275 x_return_status := FND_API.G_RET_STS_ERROR;
276 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
277 -- Begin - Andre Araujo - 09/30/2004- Remove obsolete logging
278 --IEX_CONC_REQUEST_MSG_PKG.Log_Error(
279 -- P_Concurrent_Request_ID => FND_GLOBAL.CONC_REQUEST_ID,
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;
289 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
290 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
291 -- Begin - Andre Araujo - 09/30/2004- Remove obsolete logging
292 --IEX_CONC_REQUEST_MSG_PKG.Log_Error(
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;
302 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
303 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
304 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
305 END IF;
306 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
307 -- Begin - Andre Araujo - 09/30/2004- Remove obsolete logging
308 --IEX_CONC_REQUEST_MSG_PKG.Log_Error(
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
318 END;
319
320 /**********************
321 This procedure closes all promises for delinquencies passed.
322 ***********************/
323 PROCEDURE CLOSE_PROMISES(
324 P_API_VERSION IN NUMBER,
325 P_INIT_MSG_LIST IN VARCHAR2,
326 P_COMMIT IN VARCHAR2,
327 P_VALIDATION_LEVEL IN NUMBER,
328 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
329 X_MSG_COUNT OUT NOCOPY NUMBER,
330 X_MSG_DATA OUT NOCOPY VARCHAR2,
331 P_DELINQ_TBL IN IEX_UTILITIES.t_del_id)
332 IS
333 l_api_name CONSTANT VARCHAR2(30) := 'CLOSE_PROMISES';
334 l_api_version CONSTANT NUMBER := 1.0;
335 l_return_status VARCHAR2(1);
336 l_msg_count NUMBER;
337 l_msg_data VARCHAR2(32767);
338
339 vSQL varchar2(1000);
340 i number := 0;
341 j number := 0;
342 k number := 0;
343 l_del_count number := 0;
344 l_promise_id number;
345 l_status varchar2(100);
346 l_type varchar2(100);
347 l_cl_prd_count number := 0;
348
349 Type refCur is Ref Cursor;
350 del_cur refCur;
351
352 type ids_table is table of number index by binary_integer;
353 L_PROMISE_IDS_TBL ids_table;
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
363 /*
364 Commented out whole procedure because PROB now can apply payments to promises or
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;
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
380 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
381 END IF;
382
383 -- Initialize message list if p_init_msg_list is set to TRUE
384 IF FND_API.To_Boolean(p_init_msg_list) THEN
385 FND_MSG_PUB.initialize;
386 END IF;
387
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
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 ' ||
403 'PROMISE_DETAIL_ID, status, ''Invoice'' ' ||
404 'FROM ' ||
405 'IEX_PROMISE_DETAILS ' ||
406 'WHERE ' ||
407 'DELINQUENCY_ID = :del and ' ||
408 'STATUS in (''OPEN'', ''PENDING'', ''BROKEN'') ' ||
409 'union ' ||
410 'SELECT ' ||
411 'PRD.PROMISE_DETAIL_ID, prd.status, ''Case'' ' ||
412 'FROM ' ||
413 'IEX_CASE_OBJECTS CAO, ' ||
414 'IEX_PROMISE_DETAILS PRD, ' ||
415 'IEX_DELINQUENCIES DEL ' ||
416 'WHERE ' ||
417 'DEL.DELINQUENCY_ID = :del AND ' ||
418 'DEL.CASE_ID IS NOT NULL AND ' ||
419 'DEL.CASE_ID = CAO.CAS_ID AND ' ||
420 'CAO.OBJECT_CODE = ''CONTRACTS'' AND ' ||
421 'CAO.OBJECT_ID = PRD.CONTRACT_ID AND ' ||
422 'PRD.DELINQUENCY_ID IS NULL AND ' ||
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
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;
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
471 SET STATUS = 'CLOSED',
472 last_update_date = sysdate,
473 last_updated_by = G_USER_ID
474 WHERE promise_detail_id = l_promise_ids_tbl(n);
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
484 SET UWQ_STATUS = 'COMPLETE',
485 UWQ_COMPLETE_DATE = sysdate,
486 last_update_date = sysdate,
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
496 -- Standard check of p_commit.
497 IF FND_API.To_Boolean( p_commit ) THEN
498 COMMIT WORK;
499 END IF;
500
501 x_return_status := l_return_status;
502 -- Standard call to get message count and if count is 1, get message info
503 FND_MSG_PUB.Count_And_Get(
504 p_encoded => FND_API.G_FALSE,
505 p_count => x_msg_count,
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;
515 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
516 -- Begin - Andre Araujo - 09/30/2004- Remove obsolete logging
517 --IEX_CONC_REQUEST_MSG_PKG.Log_Error(
518 -- P_Concurrent_Request_ID => FND_GLOBAL.CONC_REQUEST_ID,
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;
528 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
529 -- Begin - Andre Araujo - 09/30/2004- Remove obsolete logging
530 --IEX_CONC_REQUEST_MSG_PKG.Log_Error(
531 -- P_Concurrent_Request_ID => FND_GLOBAL.CONC_REQUEST_ID,
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;
541 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
542 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
543 END IF;
544 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
545 -- Begin - Andre Araujo - 09/30/2004- Remove obsolete logging
546 --IEX_CONC_REQUEST_MSG_PKG.Log_Error(
547 -- P_Concurrent_Request_ID => FND_GLOBAL.CONC_REQUEST_ID,
548 -- P_Procedure_name => 'IEX_PROMISES_BATCH_PUB.CLOSE_PROMISES',
549 -- P_MESSAGE => 'Failed to close promises.' );
550 -- End - Andre Araujo - 09/30/2004- Remove obsolete logging
551 */
552 END;
553
554 /**********************
555 This procedure is called from concurent meneger to start promises processing
556 ***********************/
557 PROCEDURE IEX_PROMISES_CONCUR(
558 ERRBUF OUT NOCOPY VARCHAR2,
559 RETCODE OUT NOCOPY VARCHAR2,
560 P_ORG_ID IN NUMBER DEFAULT NULL) --Added for MOAC
561 IS
562 l_msg_count number;
563 BEGIN
564 --Start MOAC
565 mo_global.init('IEX');
566 IF p_org_id IS NULL THEN
567 mo_global.set_policy_context('M',NULL);
568 ELSE
569 mo_global.set_policy_context('S',p_org_id);
570 END IF;
571 --End MOAC
572
573 fnd_file.put_line(FND_FILE.LOG, 'Running Promise Reconciliation concurrent program');
574 fnd_file.put_line(FND_FILE.LOG, 'Operating Unit: '|| nvl(mo_global.get_ou_name(mo_global.get_current_org_id), 'All'));
575
576 PROCESS_ALL_PROMISES(
577 P_API_VERSION => 1.0,
578 P_INIT_MSG_LIST => FND_API.G_TRUE,
579 P_COMMIT => FND_API.G_TRUE,
580 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
581 X_RETURN_STATUS => RETCODE,
582 X_MSG_COUNT => l_msg_count,
583 X_MSG_DATA => ERRBUF);
584 END;
585
586 /**********************
587 This procedure process all available promises
588 ***********************/
589 PROCEDURE PROCESS_ALL_PROMISES(
590 P_API_VERSION IN NUMBER,
591 P_INIT_MSG_LIST IN VARCHAR2,
592 P_COMMIT IN VARCHAR2,
593 P_VALIDATION_LEVEL IN NUMBER,
594 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
595 X_MSG_COUNT OUT NOCOPY NUMBER,
596 X_MSG_DATA OUT NOCOPY VARCHAR2)
597 IS
598 l_api_name CONSTANT VARCHAR2(30) := 'PROCESS_ALL_PROMISES';
599 l_api_version CONSTANT NUMBER := 1.0;
600 l_return_status VARCHAR2(1);
601 l_msg_count NUMBER;
602 l_msg_data VARCHAR2(32767);
603
604 --Begin bug 6053792 gnramasa 17-May-2007
605 --Should update IEX_DLN_UWQ_SUMMARY even when there are no broken promises with status COLLECTABLE
606 -- Bug #6251572 bibeura 24-OCT-2007 Modified Cursor definition
607 CURSOR UPDATE_IEX_SUMMARY
608 IS
609 SELECT sum(decode(a.status, 'COLLECTABLE', 1, 0) ) numb,
610 sum(decode(a.status, 'COLLECTABLE', a.amount_due_remaining, 0)) broken_amount,
611 sum(decode(a.status, 'COLLECTABLE', a.promise_amount, 0)) promise_amount,
612 d.party_cust_id party_cust_id,
613 a.cust_account_id cust_account_id,
614 d.customer_site_use_id customer_site_use_id
615 FROM iex_promise_details a,
616 iex_delinquencies d--_all d --Changed for bug 7237026 20-Jan-2009 barathsr
617 WHERE a.delinquency_id=d.delinquency_id
618 AND a.state = 'BROKEN_PROMISE'
619 AND a.status in ('COLLECTABLE','FULFILLED', 'CANCELLED')
620 AND a.org_id = d.org_id --Added for bug 7237026 31-Dec-2008 barathsr
621 AND EXISTS (SELECT 1 FROM iex_promise_details b
622 where TRUNC(b.last_update_date)=TRUNC(SYSDATE) AND a.cust_account_id = b.cust_account_id)
623 GROUP BY d.party_cust_id,
624 a.cust_account_id,
625 d.customer_site_use_id;
626
627 CURSOR UPDATE_IEX_ACTIVE_PRO
628 IS
629 SELECT sum(decode(pd.status, 'COLLECTABLE', 1, 0)) active_promises,
630 d.party_cust_id party_cust_id,
631 pd.cust_account_id cust_account_id,
632 d.customer_site_use_id customer_site_use_id
633 FROM iex_promise_details pd,
634 iex_delinquencies d--_all d --Changed for bug 7237026 20-Jan-2009 barathsr
635 WHERE pd.delinquency_id=d.delinquency_id
636 AND pd.state = 'BROKEN_PROMISE'
637 AND pd.status in ('COLLECTABLE','FULFILLED', 'CANCELLED')
638 AND pd.org_id =d.org_id --Added for bug 7237026 31-Dec-2008 barathsr
639 AND EXISTS (SELECT 1 FROM iex_promise_details b
640 where TRUNC(b.last_update_date)=TRUNC(SYSDATE) AND pd.cust_account_id = b.cust_account_id)
641 AND(pd.uwq_status IS NULL OR pd.uwq_status = 'ACTIVE'
642 OR(TRUNC(pd.uwq_active_date) <= TRUNC(sysdate)
643 AND pd.uwq_status = 'PENDING'))
644 GROUP BY d.party_cust_id,
645 pd.cust_account_id,
646 d.customer_site_use_id;
647
648 CURSOR UPDATE_IEX_COMP_PRO
649 IS
650 SELECT sum(decode(pd.status, 'COLLECTABLE', 1, 0)) complete_promises,
651 d.party_cust_id party_cust_id,
652 pd.cust_account_id cust_account_id,
653 d.customer_site_use_id customer_site_use_id
654 FROM iex_promise_details pd,
655 iex_delinquencies d--_all d --Changed for bug 7237026 20-Jan-2009 barathsr
656 WHERE pd.delinquency_id=d.delinquency_id
657 AND pd.state = 'BROKEN_PROMISE'
658 AND pd.status in ('COLLECTABLE','FULFILLED', 'CANCELLED')
659 AND pd.org_id = d.org_id --Added for bug 7237026 31-Dec-2008 barathsr
660 AND EXISTS (SELECT 1 FROM iex_promise_details b
661 where TRUNC(b.last_update_date)=TRUNC(SYSDATE) AND pd.cust_account_id = b.cust_account_id)
662 AND(pd.uwq_status = 'COMPLETE'
663 AND(TRUNC(pd.uwq_complete_date) +
664 fnd_profile.VALUE('IEX_UWQ_COMPLETION_DAYS') > TRUNC(sysdate)))
665 GROUP BY d.party_cust_id,
666 pd.cust_account_id,
667 d.customer_site_use_id;
668
669 CURSOR UPDATE_IEX_PEND_PRO
670 IS
671 SELECT sum(decode(pd.status, 'COLLECTABLE', 1, 0)) pending_promises,
672 d.party_cust_id party_cust_id,
673 pd.cust_account_id cust_account_id,
674 d.customer_site_use_id customer_site_use_id
675 FROM iex_promise_details pd,
676 iex_delinquencies d--_all d --Changed for bug 7237026 20-Jan-2009 barathsr
677 WHERE pd.delinquency_id=d.delinquency_id
678 AND pd.state = 'BROKEN_PROMISE'
679 AND pd.status in ('COLLECTABLE','FULFILLED', 'CANCELLED')
680 AND pd.org_id = d.org_id --Added for bug 7237026 31-Dec-2008 barathsr
681 AND EXISTS (SELECT 1 FROM iex_promise_details b
682 where TRUNC(b.last_update_date)=TRUNC(SYSDATE) AND pd.cust_account_id = b.cust_account_id)
683 AND (pd.uwq_status = 'PENDING'
684 AND(TRUNC(pd.uwq_active_date) > TRUNC(sysdate)))
685 GROUP BY d.party_cust_id,
686 pd.cust_account_id,
687 d.customer_site_use_id;
688
689 l_stage_number number;
690 /*
691 CURSOR UPDATE_NOT_FULLY_PRO_DEL
692 IS
693 SELECT pd.delinquency_id delinquency_id
694 from iex_promise_details pd,
695 iex_delinquencies d
696 where pd.delinquency_id = d.delinquency_id
697 AND pd.status = 'COLLECTABLE'
698 AND pd.state = 'PROMISE'
699 AND d.staged_dunning_level = 0
700 group by pd.delinquency_id
701 having sum(pd.promise_amount) < (select amount_due_remaining from ar_payment_schedules pay,
702 iex_delinquencies del
703 where pay.payment_schedule_id = del.payment_schedule_id
704 and del.delinquency_id = pd.delinquency_id);
705
706 CURSOR UPDATE_FULLY_PRO_DEL
707 IS
708 SELECT pd.delinquency_id delinquency_id
709 from iex_promise_details pd,
710 iex_delinquencies d
711 where pd.delinquency_id = d.delinquency_id
712 AND pd.status = 'COLLECTABLE'
713 AND pd.state = 'PROMISE'
714 AND d.staged_dunning_level <> 0
715 group by pd.delinquency_id
716 having sum(pd.promise_amount) >= (select amount_due_remaining from ar_payment_schedules pay,
717 iex_delinquencies del
718 where pay.payment_schedule_id = del.payment_schedule_id
719 and del.delinquency_id = pd.delinquency_id);
720 */
721 CURSOR UPDATE_PRO_DEL
722 IS
723 SELECT promise_detail_id
724 from iex_promise_details pd
725 where status = 'COLLECTABLE';
726 /*
727 -- Start bug#5874874 gnramasa 25-Apr-07
728
729 CURSOR UPDATE_IEX_SUMMARY IS
730 SELECT COUNT(CUST_ACCOUNT_ID) NUMB,
731 sum(AMOUNT_DUE_REMAINING) broken_amount,
732 sum(PROMISE_AMOUNT) promise_amount,
733 CUST_ACCOUNT_ID
734 FROM IEX_PROMISE_DETAILS
735 WHERE STATE = 'BROKEN_PROMISE'
736 AND STATUS = 'COLLECTABLE'
737 AND NVL(AMOUNT_DUE_REMAINING,0) > 0
738 GROUP BY CUST_ACCOUNT_ID;
739
740 CURSOR UPDATE_IEX_ACTIVE_PRO IS
741 SELECT count(cust_account_id)active_promises,cust_account_id
742 FROM iex_promise_details pd
743 WHERE pd.state = 'BROKEN_PROMISE'
744 AND(pd.uwq_status IS NULL OR pd.uwq_status = 'ACTIVE'
745 OR(TRUNC(pd.uwq_active_date) <= TRUNC(sysdate)
746 AND pd.uwq_status = 'PENDING'))
747 GROUP BY CUST_ACCOUNT_ID;
748
749 CURSOR UPDATE_IEX_COMP_PRO IS
750 SELECT count(cust_account_id)complete_promises,cust_account_id
751 FROM iex_promise_details pd
752 WHERE pd.state = 'BROKEN_PROMISE'
753 AND(pd.uwq_status = 'COMPLETE'
754 AND(TRUNC(pd.uwq_complete_date) +
755 fnd_profile.VALUE('IEX_UWQ_COMPLETION_DAYS') > TRUNC(sysdate)))
756 GROUP BY CUST_ACCOUNT_ID;
757
758 CURSOR UPDATE_IEX_PEND_PRO IS
759 SELECT count(cust_account_id)pending_promises,cust_account_id
760 FROM iex_promise_details pd
761 WHERE pd.state = 'BROKEN_PROMISE' AND(pd.uwq_status = 'PENDING'
762 AND(TRUNC(pd.uwq_active_date) > TRUNC(sysdate)))
763 GROUP BY CUST_ACCOUNT_ID;
764
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
783 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
784 END IF;
785
786 -- Initialize message list if p_init_msg_list is set to TRUE
787 IF FND_API.To_Boolean(p_init_msg_list) THEN
788 FND_MSG_PUB.initialize;
789 END IF;
790
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
800 LogMessage('********************************************');
801 LogMessage(G_PKG_NAME || '.' || l_api_name || ':Calling PROCESS_REVERSALS for AR ');
802 END IF;
803 PROCESS_REVERSALS(
804 P_API_VERSION => 1.0,
805 P_INIT_MSG_LIST => FND_API.G_TRUE,
806 P_COMMIT => FND_API.G_TRUE,
807 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
808 X_RETURN_STATUS => l_return_status,
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(
818 P_API_VERSION => 1.0,
819 P_INIT_MSG_LIST => FND_API.G_TRUE,
820 P_COMMIT => FND_API.G_TRUE,
821 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
822 X_RETURN_STATUS => l_return_status,
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(
832 P_API_VERSION => 1.0,
833 P_INIT_MSG_LIST => FND_API.G_TRUE,
834 P_COMMIT => FND_API.G_TRUE,
835 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
836 X_RETURN_STATUS => l_return_status,
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(
846 P_API_VERSION => 1.0,
847 P_INIT_MSG_LIST => FND_API.G_TRUE,
848 P_COMMIT => FND_API.G_TRUE,
849 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
850 X_RETURN_STATUS => l_return_status,
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(
860 P_API_VERSION => 1.0,
861 P_INIT_MSG_LIST => FND_API.G_TRUE,
862 P_COMMIT => FND_API.G_TRUE,
863 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
864 X_RETURN_STATUS => l_return_status,
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
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
882 x_return_status := l_return_status;
883 -- Standard call to get message count and if count is 1, get message info
884 FND_MSG_PUB.Count_And_Get(
885 p_encoded => FND_API.G_FALSE,
886 p_count => x_msg_count,
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
896 LOOP
897 UPDATE IEX_DLN_UWQ_SUMMARY
898 SET NUMBER_OF_PROMISES = I.NUMB,
899 BROKEN_PROMISE_AMOUNT = I.BROKEN_AMOUNT,
900 PROMISE_AMOUNT = I.PROMISE_AMOUNT,
901 LAST_UPDATE_DATE= SYSDATE
902 WHERE PARTY_ID = I.PARTY_CUST_ID
903 AND CUST_ACCOUNT_ID = NVL(I.CUST_ACCOUNT_ID,CUST_ACCOUNT_ID)
904 AND SITE_USE_ID = NVL(I.CUSTOMER_SITE_USE_ID,SITE_USE_ID);
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
914 LogMessage(G_PKG_NAME || '.' || l_api_name || 'Error Occurred while updating IEX_DLN_UWQ_SUMMARY ' || SQLERRM );
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
924 LOOP
925 UPDATE IEX_DLN_UWQ_SUMMARY
926 SET ACTIVE_PROMISES = I.ACTIVE_PROMISES,
927 LAST_UPDATE_DATE= SYSDATE
928 WHERE PARTY_ID = I.PARTY_CUST_ID
929 AND CUST_ACCOUNT_ID = NVL(I.CUST_ACCOUNT_ID,CUST_ACCOUNT_ID)
930 AND SITE_USE_ID = NVL(I.CUSTOMER_SITE_USE_ID,SITE_USE_ID);
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
940 LogMessage(G_PKG_NAME || '.' || l_api_name || 'Error Occurred while updating IEX_DLN_UWQ_SUMMARY ACTIVE' || SQLERRM );
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
950 LOOP
951 UPDATE IEX_DLN_UWQ_SUMMARY
952 SET COMPLETE_PROMISES = I.COMPLETE_PROMISES,
953 LAST_UPDATE_DATE= SYSDATE
954 WHERE PARTY_ID = I.PARTY_CUST_ID
955 AND CUST_ACCOUNT_ID = NVL(I.CUST_ACCOUNT_ID,CUST_ACCOUNT_ID)
956 AND SITE_USE_ID = NVL(I.CUSTOMER_SITE_USE_ID,SITE_USE_ID);
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
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;
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
977 LOOP
978 UPDATE IEX_DLN_UWQ_SUMMARY
979 SET PENDING_PROMISES = I.PENDING_PROMISES,
980 LAST_UPDATE_DATE = SYSDATE
981 WHERE PARTY_ID = I.PARTY_CUST_ID
982 AND CUST_ACCOUNT_ID = NVL(I.CUST_ACCOUNT_ID,CUST_ACCOUNT_ID)
983 AND SITE_USE_ID = NVL(I.CUSTOMER_SITE_USE_ID,SITE_USE_ID);
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
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;
997
998 -- End bug#5874874 gnramasa 25-Apr-07
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 /*
1008 FOR I IN UPDATE_NOT_FULLY_PRO_DEL
1009 LOOP
1010
1011 iex_utilities.MaxStageForanDelinquency (p_delinquency_id => I.delinquency_id
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;
1021
1022 FOR J IN UPDATE_FULLY_PRO_DEL
1023 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;
1033 */
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
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;
1059
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
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;
1073 --end
1074
1075 EXCEPTION
1076 WHEN FND_API.G_EXC_ERROR THEN
1077 ROLLBACK TO PROCESS_ALL_PROMISES_PVT;
1078 x_return_status := FND_API.G_RET_STS_ERROR;
1079 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1080 -- Begin - Andre Araujo - 09/30/2004- Remove obsolete logging
1081 /*
1082 IEX_CONC_REQUEST_MSG_PKG.Log_Error(
1083 P_Concurrent_Request_ID => FND_GLOBAL.CONC_REQUEST_ID,
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;
1093 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1094 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1095 -- Begin - Andre Araujo - 09/30/2004- Remove obsolete logging
1096 /*
1097 IEX_CONC_REQUEST_MSG_PKG.Log_Error(
1098 P_Concurrent_Request_ID => FND_GLOBAL.CONC_REQUEST_ID,
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;
1108 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1109 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)THEN
1110 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1111 END IF;
1112 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1113 -- Begin - Andre Araujo - 09/30/2004- Remove obsolete logging
1114 /*
1115 IEX_CONC_REQUEST_MSG_PKG.Log_Error(
1116 P_Concurrent_Request_ID => FND_GLOBAL.CONC_REQUEST_ID,
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
1126 /**********************
1127 This procedure unapply promise applications that have been reversed in AR
1128 ***********************/
1129 PROCEDURE PROCESS_REVERSALS(
1130 P_API_VERSION IN NUMBER,
1131 P_INIT_MSG_LIST IN VARCHAR2,
1132 P_COMMIT IN VARCHAR2,
1133 P_VALIDATION_LEVEL IN NUMBER,
1134 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1135 X_MSG_COUNT OUT NOCOPY NUMBER,
1136 X_MSG_DATA OUT NOCOPY VARCHAR2,
1137 P_TYPE IN VARCHAR2)
1138 IS
1139 l_api_name CONSTANT VARCHAR2(30) := 'PROCESS_REVERSALS';
1140 l_api_version CONSTANT NUMBER := 1.0;
1141 l_return_status VARCHAR2(1);
1142 l_msg_count NUMBER;
1143 l_msg_data VARCHAR2(32767);
1144 vSQL varchar2(10000);
1145 Type refCur is Ref Cursor;
1146 promises_cur refCur;
1147 l_appl_tbl IEX_PROMISES_BATCH_PUB.REVERSE_APPLS_TBL;
1148 i NUMBER;
1149 nCount NUMBER;
1150 l_promise_detail_id NUMBER;
1151 l_promise_date DATE;
1152 l_status VARCHAR2(30);
1153 l_promise_amount NUMBER;
1154 l_amount_due_remaining NUMBER;
1155 l_amount_applied NUMBER;
1156 l_receivable_application_id NUMBER;
1157 l_new_status VARCHAR2(30) := null;
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
1176 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1177 END IF;
1178
1179 -- Initialize message list if p_init_msg_list is set to TRUE
1180 IF FND_API.To_Boolean(p_init_msg_list) THEN
1181 FND_MSG_PUB.initialize;
1182 END IF;
1183
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
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 ' ||
1198 'prd.promise_detail_id, ' ||
1199 'prd.promise_date, ' ||
1200 'prd.status, ' ||
1201 'prd.promise_amount, ' ||
1202 'prd.amount_due_remaining, ' ||
1203 'pax.amount_applied, ' ||
1204 'raa.receivable_application_id ' ||
1205 'FROM ' ||
1206 'AR_RECEIVABLE_APPLICATIONS raa, ' ||
1207 'IEX_prd_appl_xref pax, ' ||
1208 'iex_promise_details prd ' ||
1209 'WHERE ' ||
1210 'raa.receivable_application_id = pax.receivable_application_id and ' ||
1211 'raa.status in (''APP'', ''ACC'') and ' ||
1212 'raa.amount_applied > 0 and ' ||
1213 'raa.reversal_gl_date is not null and ' ||
1214 'pax.reversed_flag is null and ' ||
1215 'pax.reversed_date is null and ' ||
1216 'pax.receivable_application_id is not null and ' ||
1217 'pax.promise_detail_id = prd.promise_detail_id and ' ||
1218 'prd.status in (''COLLECTABLE'', ''FULFILLED'') and ' ||
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
1228 vSQL := 'SELECT ' ||
1229 'prd.promise_detail_id, ' ||
1230 'prd.promise_date, ' ||
1231 'prd.status, ' ||
1232 'prd.promise_amount, ' ||
1233 'prd.amount_due_remaining, ' ||
1234 'pax.amount_applied, ' ||
1235 'raa.receivable_application_id ' ||
1236 'FROM ' ||
1237 'IEX_OKL_PAYMENTS_V raa, ' ||
1238 'IEX_prd_appl_xref pax, ' ||
1239 'iex_promise_details prd ' ||
1240 'WHERE ' ||
1241 'raa.receivable_application_id = pax.receivable_application_id and ' ||
1242 'raa.amount_applied > 0 and ' ||
1243 'raa.reversal_gl_date is not null and ' ||
1244 'pax.reversed_flag is null and ' ||
1245 'pax.reversed_date is null and ' ||
1246 'pax.promise_detail_id = prd.promise_detail_id and ' ||
1247 'prd.status in (''COLLECTABLE'', ''FULFILLED'') ' ||
1248 'ORDER BY raa.receivable_application_id';
1249 */
1250
1251 vSQL := 'SELECT ' ||
1252 'prd.promise_detail_id, ' ||
1253 'prd.promise_date, ' ||
1254 'prd.status, ' ||
1255 'prd.promise_amount, ' ||
1256 'prd.amount_due_remaining, ' ||
1257 'pax.amount_applied, ' ||
1258 'pax.receivable_application_id ' ||
1259 'FROM ' ||
1260 'IEX_prd_appl_xref pax, ' ||
1261 'iex_promise_details prd, ' ||
1262 'AR_SYSTEM_PARAMETERS asp ' ||--Added for bug 73237026 barathsr 31-Dec-2008
1263 'WHERE ' ||
1264 'pax.receivable_application_id IN (select receivable_application_id from IEX_OKL_PAYMENTS_V where ' ||
1265 'amount_applied > 0 and ' ||
1266 'reversal_gl_date is not null) and ' ||
1267 'pax.reversed_flag is null and ' ||
1268 'pax.reversed_date is null and ' ||
1269 'pax.receivable_application_id is not null and ' ||
1270 'pax.promise_detail_id = prd.promise_detail_id and ' ||
1271 'prd.status in (''COLLECTABLE'', ''FULFILLED'') and ' ||
1272 'prd.org_id = asp.org_id ' || --Added for bug 73237026 barathsr
1273 'ORDER BY pax.receivable_application_id';
1274 end if;
1275
1276 open promises_cur for vSQL ;
1277 i := 0;
1278 LOOP
1279 fetch promises_cur into
1280 l_promise_detail_id,
1281 l_promise_date,
1282 l_status,
1283 l_promise_amount,
1284 l_amount_due_remaining,
1285 l_amount_applied,
1286 l_receivable_application_id;
1287 exit when promises_cur%NOTFOUND;
1288 i := i+1;
1289 l_appl_tbl(i).promise_detail_id := l_promise_detail_id;
1290 l_appl_tbl(i).promise_date := l_promise_date;
1291 l_appl_tbl(i).status := l_status;
1292 l_appl_tbl(i).promise_amount := l_promise_amount;
1293 l_appl_tbl(i).amount_due_remaining := l_amount_due_remaining;
1294 l_appl_tbl(i).amount_applied := l_amount_applied;
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);
1304 LogMessage(G_PKG_NAME || '.' || l_api_name || ':status = ' || l_appl_tbl(i).status);
1305 LogMessage(G_PKG_NAME || '.' || l_api_name || ':promise_amount = ' || l_appl_tbl(i).promise_amount);
1306 LogMessage(G_PKG_NAME || '.' || l_api_name || ':amount_due_remaining = ' || l_appl_tbl(i).amount_due_remaining);
1307 LogMessage(G_PKG_NAME || '.' || l_api_name || ':amount_applied = ' || l_appl_tbl(i).amount_applied);
1308 LogMessage(G_PKG_NAME || '.' || l_api_name || ':receivable_application_id = ' || l_appl_tbl(i).receivable_application_id);
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
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
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
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;
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
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
1345 set reversed_flag = 'Y',
1346 reversed_date = sysdate,
1347 last_update_date = sysdate,
1348 last_updated_by = G_USER_ID,
1349 request_id = G_REQUEST_ID
1350 where
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 /*
1360 IEX_CONC_REQUEST_MSG_PKG.Log_Error(
1361 P_Concurrent_Request_ID => FND_GLOBAL.CONC_REQUEST_ID,
1362 P_Procedure_name => G_PKG_NAME || '.' || l_api_name,
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
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
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,
1384 amount_due_remaining = l_new_remaining_amount,
1385 last_update_date = sysdate,
1386 last_updated_by = G_USER_ID
1387 where promise_detail_id = l_appl_tbl(i).promise_detail_id;
1388 else
1389 update IEX_PROMISE_DETAILS
1390 set amount_due_remaining = l_new_remaining_amount,
1391 last_update_date = sysdate,
1392 last_updated_by = G_USER_ID
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 /*
1402 IEX_CONC_REQUEST_MSG_PKG.Log_Error(
1403 P_Concurrent_Request_ID => FND_GLOBAL.CONC_REQUEST_ID,
1404 P_Procedure_name => G_PKG_NAME || '.' || l_api_name,
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 /*
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
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;
1436 --end
1437 */
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
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
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
1460 x_return_status := l_return_status;
1461 -- Standard call to get message count and if count is 1, get message info
1462 FND_MSG_PUB.Count_And_Get(
1463 p_encoded => FND_API.G_FALSE,
1464 p_count => x_msg_count,
1465 p_data => x_msg_data);
1466
1467 EXCEPTION
1468
1469 WHEN FND_API.G_EXC_ERROR THEN
1470 ROLLBACK TO PROCESS_REVERSALS_PVT;
1471 x_return_status := FND_API.G_RET_STS_ERROR;
1472 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1473 -- Begin - Andre Araujo - 09/30/2004- Remove obsolete logging
1474 /*
1475 IEX_CONC_REQUEST_MSG_PKG.Log_Error(
1476 P_Concurrent_Request_ID => FND_GLOBAL.CONC_REQUEST_ID,
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;
1486 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1487 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1488 -- Begin - Andre Araujo - 09/30/2004- Remove obsolete logging
1489 /*
1490 IEX_CONC_REQUEST_MSG_PKG.Log_Error(
1491 P_Concurrent_Request_ID => FND_GLOBAL.CONC_REQUEST_ID,
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;
1501 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1502 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)THEN
1503 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1504 END IF;
1505 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1506 /*
1507 IEX_CONC_REQUEST_MSG_PKG.Log_Error(
1508 P_Concurrent_Request_ID => FND_GLOBAL.CONC_REQUEST_ID,
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
1518 /**********************
1519 This procedure processes promises
1520 ***********************/
1521 PROCEDURE PROCESS_PROMISES(
1522 P_API_VERSION IN NUMBER,
1523 P_INIT_MSG_LIST IN VARCHAR2,
1524 P_COMMIT IN VARCHAR2,
1525 P_VALIDATION_LEVEL IN NUMBER,
1526 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1527 X_MSG_COUNT OUT NOCOPY NUMBER,
1528 X_MSG_DATA OUT NOCOPY VARCHAR2,
1529 P_TYPE IN VARCHAR2)
1530 IS
1531 l_api_name CONSTANT VARCHAR2(30) := 'PROCESS_PROMISES';
1532 l_api_version CONSTANT NUMBER := 1.0;
1533 l_return_status VARCHAR2(1);
1534 l_msg_count NUMBER;
1535 l_msg_data VARCHAR2(32767);
1536 vSQL varchar2(10000);
1537 Type refCur is Ref Cursor;
1538 promise_cur refCur;
1539 y NUMBER;
1540 nCount NUMBER;
1541 l_pro_tbl IEX_PROMISES_BATCH_PUB.PROMISES_TBL;
1542
1543 l_PROMISE_DETAIL_ID NUMBER;
1544 l_CREATION_DATE DATE;
1545 l_PROMISE_DATE DATE;
1546 l_STATUS VARCHAR2(30);
1547 l_STATE VARCHAR2(30);
1548 l_PROMISE_AMOUNT NUMBER;
1549 l_AMOUNT_DUE_REMAINING NUMBER;
1550 l_DELINQUENCY_ID NUMBER;
1551 l_PAYMENT_SCHEDULE_ID NUMBER;
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
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
1569 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1570 END IF;
1571
1572 -- Initialize message list if p_init_msg_list is set to TRUE
1573 IF FND_API.To_Boolean(p_init_msg_list) THEN
1574 FND_MSG_PUB.initialize;
1575 END IF;
1576
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
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
1590
1591 vSQL := 'SELECT ' ||
1592 'PRD.promise_detail_id, ' ||
1593 'PRD.creation_date, ' ||
1594 'nvl(PRD.broken_on_date, PRD.promise_date), ' ||
1595 'PRD.status, ' ||
1596 'PRD.state, ' ||
1597 'PRD.promise_amount, ' ||
1598 'PRD.amount_due_remaining, ' ||
1599 'PRD.delinquency_id, ' ||
1600 'DEL.payment_schedule_id ' ||
1601 'FROM ' ||
1602 'iex_promise_details prd, ' ||
1603 'iex_delinquencies del ' ||
1604 'WHERE ' ||
1605 'prd.delinquency_id is not null and ' ||
1606 'del.delinquency_id = prd.delinquency_id and ' ||
1607 'prd.status = ''COLLECTABLE'' and ' ||
1608 'prd.org_id = del.org_id ' || --Added for bug 7237026 barathsr 31-Dec-2008
1609 'order by PRD.promise_date';
1610
1611 open promise_cur for vSQL;
1612 y := 0;
1613 LOOP
1614 fetch promise_cur into
1615 l_PROMISE_DETAIL_ID,
1616 l_CREATION_DATE,
1617 l_PROMISE_DATE,
1618 l_STATUS,
1619 l_STATE,
1620 l_PROMISE_AMOUNT,
1621 l_AMOUNT_DUE_REMAINING,
1622 l_DELINQUENCY_ID,
1623 l_PAYMENT_SCHEDULE_ID;
1624 exit when promise_cur%NOTFOUND;
1625
1626 y := y+1;
1627 l_pro_tbl(y).PROMISE_DETAIL_ID := l_PROMISE_DETAIL_ID;
1628 l_pro_tbl(y).CREATION_DATE := l_CREATION_DATE;
1629 l_pro_tbl(y).PROMISE_DATE := l_PROMISE_DATE;
1630 l_pro_tbl(y).STATUS := l_STATUS;
1631 l_pro_tbl(y).STATE := l_STATE;
1632 l_pro_tbl(y).PROMISE_AMOUNT := l_PROMISE_AMOUNT;
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
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,
1656 P_INIT_MSG_LIST => FND_API.G_TRUE,
1657 P_COMMIT => FND_API.G_TRUE,
1658 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
1659 X_RETURN_STATUS => l_return_status,
1660 X_MSG_COUNT => l_msg_count,
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
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
1675 SET STATUS = 'FULFILLED',
1676 last_update_date = sysdate,
1677 last_updated_by = G_USER_ID
1678 WHERE
1679 promise_detail_id in
1680 (select prd.promise_detail_id
1681 from iex_promise_details prd, iex_delinquencies del, ar_payment_schedules aps --added for Bug 6446848 08-Dec-2008 barathsr
1682 where prd.delinquency_id is not null and
1683 prd.delinquency_id = del.delinquency_id and
1684 prd.org_id = del.org_id and --Added for bug 7237026 barathsr 31-Dec-2008
1685 del.payment_schedule_id=aps.payment_schedule_id and --added for Bug 6446848 08-Dec-2008 barathsr
1686 prd.status in ('COLLECTABLE', 'PENDING') and
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
1696
1697 vSQL := 'SELECT ' ||
1698 'PRD.promise_detail_id pro, ' ||
1699 'PRD.creation_date, ' ||
1700 'nvl(PRD.broken_on_date, PRD.promise_date), ' ||
1701 'PRD.status, ' ||
1702 'PRD.state, ' ||
1703 'PRD.promise_amount, ' ||
1704 'PRD.amount_due_remaining, ' ||
1705 'PRD.cust_account_id ' ||
1706 'FROM ' ||
1707 'iex_promise_details prd,' ||
1708 'AR_SYSTEM_PARAMETERS asp ' || --Added for bug 7237026 barathsr 31-Dec-2008
1709 'WHERE ' ||
1710 'prd.delinquency_id is null and ' ||
1711 'prd.CNSLD_INVOICE_ID is null and ' ||
1712 'prd.CONTRACT_ID is null and ' ||
1713 'prd.status = ''COLLECTABLE'' and ' ||
1714 'prd.org_id = asp.org_id ' || --Added for bug 7237026 barathsr 31-Dec-2008
1715 'order by PRD.promise_date';
1716
1717 open promise_cur for vSQL;
1718 y := 0;
1719 LOOP
1720 fetch promise_cur into
1721 l_PROMISE_DETAIL_ID,
1722 l_CREATION_DATE,
1723 l_PROMISE_DATE,
1724 l_STATUS,
1725 l_STATE,
1726 l_PROMISE_AMOUNT,
1727 l_AMOUNT_DUE_REMAINING,
1728 l_CUST_ACCOUNT_ID;
1729 exit when promise_cur%NOTFOUND;
1730
1731 y := y+1;
1732 l_pro_tbl(y).PROMISE_DETAIL_ID := l_PROMISE_DETAIL_ID;
1733 l_pro_tbl(y).CREATION_DATE := l_CREATION_DATE;
1734 l_pro_tbl(y).PROMISE_DATE := l_PROMISE_DATE;
1735 l_pro_tbl(y).STATUS := l_STATUS;
1736 l_pro_tbl(y).STATE := l_STATE;
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
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,
1760 P_INIT_MSG_LIST => FND_API.G_TRUE,
1761 P_COMMIT => FND_API.G_TRUE,
1762 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
1763 X_RETURN_STATUS => l_return_status,
1764 X_MSG_COUNT => l_msg_count,
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
1774 elsif P_TYPE = 'CNTR' then -- processing all promises on contracts
1775
1776 vSQL := 'SELECT ' ||
1777 'PRD.promise_detail_id, ' ||
1778 'PRD.creation_date, ' ||
1779 'nvl(PRD.broken_on_date, PRD.promise_date), ' ||
1780 'PRD.status, ' ||
1781 'PRD.state, ' ||
1782 'PRD.promise_amount, ' ||
1783 'PRD.amount_due_remaining, ' ||
1784 'PRD.contract_id ' ||
1785 'FROM ' ||
1786 'iex_promise_details prd, ' ||
1787 'AR_SYSTEM_PARAMETERS asp ' || --Added for bug 7237026 barathsr 31-Dec-2008
1788 'WHERE ' ||
1789 'prd.contract_id is not null and ' ||
1790 'prd.status = ''COLLECTABLE'' and ' ||
1791 'prd.org_id = asp.org_id '||--Added for bug 7237026 barathsr 31-Dec-2008
1792 'order by PRD.promise_date';
1793
1794 open promise_cur for vSQL;
1795 y := 0;
1796 LOOP
1797 fetch promise_cur into
1798 l_PROMISE_DETAIL_ID,
1799 l_CREATION_DATE,
1800 l_PROMISE_DATE,
1801 l_STATUS,
1802 l_STATE,
1803 l_PROMISE_AMOUNT,
1804 l_AMOUNT_DUE_REMAINING,
1805 l_CONTRACT_ID;
1806 exit when promise_cur%NOTFOUND;
1807
1808 y := y+1;
1809 l_pro_tbl(y).PROMISE_DETAIL_ID := l_PROMISE_DETAIL_ID;
1810 l_pro_tbl(y).CREATION_DATE := l_CREATION_DATE;
1811 l_pro_tbl(y).PROMISE_DATE := l_PROMISE_DATE;
1812 l_pro_tbl(y).STATUS := l_STATUS;
1813 l_pro_tbl(y).STATE := l_STATE;
1814 l_pro_tbl(y).PROMISE_AMOUNT := l_PROMISE_AMOUNT;
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
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,
1838 P_INIT_MSG_LIST => FND_API.G_TRUE,
1839 P_COMMIT => FND_API.G_TRUE,
1840 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
1841 X_RETURN_STATUS => l_return_status,
1842 X_MSG_COUNT => l_msg_count,
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
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
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
1867 x_return_status := l_return_status;
1868 -- Standard call to get message count and if count is 1, get message info
1869 FND_MSG_PUB.Count_And_Get(
1870 p_encoded => FND_API.G_FALSE,
1871 p_count => x_msg_count,
1872 p_data => x_msg_data);
1873
1874 EXCEPTION
1875 WHEN FND_API.G_EXC_ERROR THEN
1876 ROLLBACK TO PROCESS_PROMISES_PVT;
1877 x_return_status := FND_API.G_RET_STS_ERROR;
1878 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1879 -- Begin - Andre Araujo - 09/30/2004- Remove obsolete logging
1880 /*
1881 IEX_CONC_REQUEST_MSG_PKG.Log_Error(
1882 P_Concurrent_Request_ID => FND_GLOBAL.CONC_REQUEST_ID,
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;
1892 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1893 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1894 -- Begin - Andre Araujo - 09/30/2004- Remove obsolete logging
1895 /*
1896 IEX_CONC_REQUEST_MSG_PKG.Log_Error(
1897 P_Concurrent_Request_ID => FND_GLOBAL.CONC_REQUEST_ID,
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;
1907 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1908 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)THEN
1909 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1910 END IF;
1911 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1912 -- Begin - Andre Araujo - 09/30/2004- Remove obsolete logging
1913 /*
1914 IEX_CONC_REQUEST_MSG_PKG.Log_Error(
1915 P_Concurrent_Request_ID => FND_GLOBAL.CONC_REQUEST_ID,
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
1925 /**********************
1926 This procedure implements FIFO application method for promises
1927 ***********************/
1928 PROCEDURE APPLY_PROMISES_FIFO(
1929 P_API_VERSION IN NUMBER,
1930 P_INIT_MSG_LIST IN VARCHAR2,
1931 P_COMMIT IN VARCHAR2,
1932 P_VALIDATION_LEVEL IN NUMBER,
1933 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1934 X_MSG_COUNT OUT NOCOPY NUMBER,
1935 X_MSG_DATA OUT NOCOPY VARCHAR2,
1936 P_PROMISES_TBL IN OUT NOCOPY IEX_PROMISES_BATCH_PUB.PROMISES_TBL,
1937 P_TYPE IN VARCHAR2)
1938 IS
1939 l_api_name CONSTANT VARCHAR2(30) := 'APPLY_PROMISES_FIFO';
1940 l_api_version CONSTANT NUMBER := 1.0;
1941 l_return_status VARCHAR2(1);
1942 l_msg_count NUMBER;
1943 l_msg_data VARCHAR2(32767);
1944 vSQL varchar2(10000);
1945 -- start bug 3635087 gnramasa 10/07/07
1946 vSQL_pay_only varchar2(10000);
1947 vSQL_pay_adj varchar2(10000);
1948 l_adjustment_count NUMBER := 0;
1949 l_adjustment_id NUMBER;
1950 l_adjusted_amount NUMBER;
1951 l_adjusted_date DATE;
1952 l_adj_remaining_amount NUMBER;
1953 -- End bug 3635087 gnramasa 10/07/07
1954 Type refCur is Ref Cursor;
1955 appl_cur refCur;
1956 l_appl_tbl IEX_PROMISES_BATCH_PUB.APPLS_TBL;
1957 i NUMBER;
1958 y NUMBER;
1959 x NUMBER;
1960 nCount NUMBER;
1961 nCount1 NUMBER;
1962 l_receivable_application_id NUMBER;
1963 l_ar_applied_amount NUMBER;
1964 l_ar_remaining_amount NUMBER;
1965 l_ar_apply_date DATE;
1966 l_callback_date DATE;
1967 l_status VARCHAR2(30);
1968 l_state VARCHAR2(30);
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
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
1985 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1986 END IF;
1987
1988 -- Initialize message list if p_init_msg_list is set to TRUE
1989 IF FND_API.To_Boolean(p_init_msg_list) THEN
1990 FND_MSG_PUB.initialize;
1991 END IF;
1992
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;
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
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);
2012 LogMessage(G_PKG_NAME || '.' || l_api_name || ':nvl(BROKEN_ON_DATE, PROMISE_DATE) = ' || P_PROMISES_TBL(i).PROMISE_DATE);
2013 LogMessage(G_PKG_NAME || '.' || l_api_name || ':PROMISE_AMOUNT = ' || P_PROMISES_TBL(i).PROMISE_AMOUNT);
2014 LogMessage(G_PKG_NAME || '.' || l_api_name || ':AMOUNT_DUE_REMAINING = ' || P_PROMISES_TBL(i).AMOUNT_DUE_REMAINING);
2015 LogMessage(G_PKG_NAME || '.' || l_api_name || ':STATUS = ' || P_PROMISES_TBL(i).STATUS);
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
2025 vSQL_pay_only := 'select ' ||
2026 'raa.receivable_application_id, ' ||
2027 'raa.amount_applied, ' ||
2028 'raa.apply_date, ' ||
2029 'raa.amount_applied - nvl(sum(pax.amount_applied), 0), ' ||
2030 'NULL, ' ||
2031 'NULL, ' ||
2032 'NULL, ' ||
2033 'NULL ' ||
2034 'from ' ||
2035 'AR_RECEIVABLE_APPLICATIONS raa, ' ||
2036 'IEX_prd_appl_xref pax ' ||
2037 'where ' ||
2038 '(trunc(raa.apply_date) between trunc(:PROMISE_CR_DATE) and trunc(sysdate)) and ' ||
2039 'raa.status = ''APP'' and ' ||
2040 'raa.amount_applied > 0 and ' ||
2041 'raa.reversal_gl_date is null and ' ||
2042 'raa.applied_payment_schedule_id = :PSA_ID and ' ||
2043 'raa.receivable_application_id = pax.receivable_application_id(+) and ' ||
2044 'raa.receivable_application_id not in ' ||
2045 '(select receivable_application_id ' ||
2046 'from IEX_prd_appl_xref where promise_detail_id = :PROMISE_ID and ' ||
2047 'REVERSED_FLAG is null and REVERSED_DATE is null and receivable_application_id is NOT NULL) ' ||
2048 'group by raa.receivable_application_id, raa.amount_applied, raa.apply_date ' ||
2049 'order by raa.receivable_application_id';
2050
2051 vSQL_pay_adj := 'SELECT ' ||
2052 'raa.receivable_application_id, ' ||
2053 'raa.amount_applied, ' ||
2054 'raa.apply_date, ' ||
2055 'raa.amount_applied -nvl(SUM(pax.amount_applied), 0), ' ||
2056 'NULL, ' ||
2057 'NULL, ' ||
2058 'NULL, ' ||
2059 'NULL ' ||
2060 'FROM ar_receivable_applications raa, ' ||
2061 'iex_prd_appl_xref pax ' ||
2062 'WHERE(TRUNC(raa.apply_date) BETWEEN TRUNC(:promise_cr_date) ' ||
2063 'AND TRUNC(sysdate)) ' ||
2064 'AND raa.status = ''APP'' ' ||
2065 'AND raa.amount_applied > 0 ' ||
2066 'AND raa.reversal_gl_date IS NULL ' ||
2067 'AND raa.applied_payment_schedule_id = :psa_id ' ||
2068 'AND raa.receivable_application_id = pax.receivable_application_id(+) ' ||
2069 'AND raa.receivable_application_id NOT IN ' ||
2070 '(SELECT receivable_application_id ' ||
2071 'FROM iex_prd_appl_xref ' ||
2072 'WHERE promise_detail_id = :promise_id ' ||
2073 'AND reversed_flag IS NULL ' ||
2074 'AND reversed_date IS NULL AND receivable_application_id is NOT NULL) ' ||
2075 'GROUP BY raa.receivable_application_id, ' ||
2076 'raa.amount_applied, ' ||
2077 'raa.apply_date ' ||
2078 'UNION ALL ' ||
2079 'SELECT NULL, ' ||
2080 'NULL, ' ||
2081 'NULL, ' ||
2082 'NULL, ' ||
2083 'ara.adjustment_id, ' ||
2084 '-ara.amount, ' ||
2085 'ara.apply_date, ' ||
2086 '-ara.amount -nvl(SUM(pax.amount_applied), 0) ' ||
2087 'FROM ar_adjustments ara, ' ||
2088 'iex_prd_appl_xref pax ' ||
2089 'WHERE(TRUNC(ara.apply_date) BETWEEN TRUNC(:promise_cr_date) ' ||
2090 'AND TRUNC(sysdate)) ' ||
2091 'AND ara.status = ''A'' ' ||
2092 'AND ara.amount < 0 ' ||
2093 'AND ara.payment_schedule_id = :psa_id ' ||
2094 'AND ara.adjustment_id = pax.adjustment_id(+) ' ||
2095 'AND ara.adjustment_id NOT IN ' ||
2096 '(SELECT adjustment_id ' ||
2097 'FROM iex_prd_appl_xref ' ||
2098 'WHERE promise_detail_id = :promise_id AND adjustment_id is NOT NULL)' ||
2099 'GROUP BY ara.adjustment_id, ' ||
2100 'ara.amount, ' ||
2101 'ara.apply_date';
2102
2103 SELECT count(adjustment_id)
2104 into l_adjustment_count
2105 FROM ar_adjustments
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;
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
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, ' ||
2126 'raa.amount_applied, ' ||
2127 'raa.apply_date, ' ||
2128 'raa.amount_applied - nvl(sum(pax.amount_applied), 0), ' ||
2129 'NULL, ' ||
2130 'NULL, ' ||
2131 'NULL, ' ||
2132 'NULL ' ||
2133 'from ' ||
2134 'AR_RECEIVABLE_APPLICATIONS raa, ' ||
2135 'IEX_prd_appl_xref pax, ' ||
2136 'AR_PAYMENT_SCHEDULES psa ' ||
2137 'where ' ||
2138 '(trunc(raa.apply_date) between trunc(:PROMISE_CR_DATE) and trunc(sysdate)) and ' ||
2139 'raa.status = ''ACC'' and ' ||
2140 'raa.amount_applied > 0 and ' ||
2141 'raa.reversal_gl_date is null and ' ||
2142 'raa.payment_schedule_id = psa.payment_schedule_id and ' ||
2143 'psa.customer_id = :CUSTOMER_ID and ' ||
2144 'raa.receivable_application_id = pax.receivable_application_id(+) and ' ||
2145 'raa.receivable_application_id not in ' ||
2146 '(select receivable_application_id ' ||
2147 'from IEX_prd_appl_xref where promise_detail_id = :PROMISE_ID and ' ||
2148 'REVERSED_FLAG is null and REVERSED_DATE is null and receivable_application_id is NOT NULL) ' ||
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 ' ||
2158 'raa.receivable_application_id, ' ||
2159 'raa.amount_applied, ' ||
2160 'raa.apply_date, ' ||
2161 'raa.amount_applied - nvl(sum(pax.amount_applied), 0) ' ||
2162 'from ' ||
2163 'IEX_OKL_PAYMENTS_V raa, ' ||
2164 'IEX_prd_appl_xref pax ' ||
2165 'where ' ||
2166 '(trunc(raa.apply_date) between trunc(:PROMISE_CR_DATE) and trunc(sysdate)) and ' ||
2167 'raa.amount_applied > 0 and ' ||
2168 'raa.reversal_gl_date is null and ' ||
2169 'raa.contract_id = :CONTRACT_ID and ' ||
2170 'raa.receivable_application_id = pax.receivable_application_id(+) and ' ||
2171 'raa.receivable_application_id not in ' ||
2172 '(select receivable_application_id ' ||
2173 'from IEX_prd_appl_xref where promise_detail_id = :PROMISE_ID and ' ||
2174 'REVERSED_FLAG is null and REVERSED_DATE is null) ' ||
2175 'group by raa.receivable_application_id, raa.amount_applied, raa.apply_date ' ||
2176 'order by raa.receivable_application_id';
2177 */
2178
2179 vSQL := ' Select '||
2180 ' ARAPP.RECEIVABLE_APPLICATION_ID, '||
2181 ' ARAPP.AMOUNT_APPLIED, '||
2182 ' ARAPP.APPLY_DATE, '||
2183 ' ARAPP.AMOUNT_APPLIED - nvl(sum(PAX.amount_applied), 0), '||
2184 ' NULL, ' ||
2185 ' NULL, ' ||
2186 ' NULL, ' ||
2187 ' NULL ' ||
2188 ' From ' ||
2189 ' OKL_CNSLD_AR_STRMS_B CNSLD, '||
2190 ' AR_RECEIVABLE_APPLICATIONS ARAPP, '||
2191 ' AR_PAYMENT_SCHEDULES PMTSCH, '||
2192 ' IEX_prd_appl_xref PAX '||
2193 ' Where '||
2194 ' CNSLD.khr_id = :CONTRACT_ID '||
2195 ' and CNSLD.receivables_invoice_id = PMTSCH.customer_trx_id '||
2196 ' and PMTSCH.class = ''INV'' '||
2197 ' and PMTSCH.payment_schedule_id = ARAPP.applied_payment_schedule_id '||
2198 ' and (trunc(ARAPP.apply_date) between trunc(:PROMISE_CR_DATE) and trunc(sysdate)) '||
2199 ' and ARAPP.amount_applied > 0 '||
2200 ' and ARAPP.reversal_gl_date is null '||
2201 ' and ARAPP.receivable_application_id = PAX.receivable_application_id(+) '||
2202 ' and ARAPP.receivable_application_id not in (select receivable_application_id from IEX_prd_appl_xref ' ||
2203 ' where promise_detail_id = :PROMISE_ID and REVERSED_FLAG is null and REVERSED_DATE is null ' ||
2204 ' and receivable_application_id is NOT NULL) '||
2205 ' group by ARAPP.receivable_application_id, ARAPP.amount_applied, ARAPP.apply_date '||
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
2215 IF l_adjustment_count = 0 THEN
2216 open appl_cur for vSQL using
2217 P_PROMISES_TBL(i).CREATION_DATE,
2218 P_PROMISES_TBL(i).PAYMENT_SCHEDULE_ID,
2219 P_PROMISES_TBL(i).PROMISE_DETAIL_ID;
2220 ELSE
2221 open appl_cur for vSQL using
2222 P_PROMISES_TBL(i).CREATION_DATE,
2223 P_PROMISES_TBL(i).PAYMENT_SCHEDULE_ID,
2224 P_PROMISES_TBL(i).PROMISE_DETAIL_ID,
2225 P_PROMISES_TBL(i).CREATION_DATE,
2226 P_PROMISES_TBL(i).PAYMENT_SCHEDULE_ID,
2227 P_PROMISES_TBL(i).PROMISE_DETAIL_ID;
2228 END IF;
2229
2230 elsif P_TYPE = 'ACC' then
2231 open appl_cur for vSQL using
2232 P_PROMISES_TBL(i).CREATION_DATE,
2233 P_PROMISES_TBL(i).CUST_ACCOUNT_ID,
2234 P_PROMISES_TBL(i).PROMISE_DETAIL_ID;
2235 elsif P_TYPE = 'CNTR' then
2236 open appl_cur for vSQL using
2237 P_PROMISES_TBL(i).CREATION_DATE,
2238 P_PROMISES_TBL(i).CONTRACT_ID,
2239 P_PROMISES_TBL(i).PROMISE_DETAIL_ID;
2240 end if;
2241
2242 y := 0;
2243 l_appl_tbl.delete;
2244 LOOP
2245
2246 fetch appl_cur into
2247 l_receivable_application_id,
2248 l_ar_applied_amount,
2249 l_ar_apply_date,
2250 l_ar_remaining_amount,
2251 l_adjustment_id,
2252 l_adjusted_amount,
2253 l_adjusted_date,
2254 l_adj_remaining_amount;
2255 exit when appl_cur%NOTFOUND;
2256
2257 if l_ar_remaining_amount > 0 or l_adj_remaining_amount > 0 then
2258 y := y+1;
2259 l_appl_tbl(y).receivable_application_id := l_receivable_application_id;
2260 l_appl_tbl(y).ar_applied_amount := l_ar_applied_amount;
2261 l_appl_tbl(y).ar_remaining_amount := l_ar_remaining_amount;
2262 l_appl_tbl(y).ar_apply_date := l_ar_apply_date;
2263 l_appl_tbl(y).adjustment_id := l_adjustment_id;
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);
2273 LogMessage(G_PKG_NAME || '.' || l_api_name || ':ar_apply_date = ' || l_appl_tbl(y).ar_apply_date);
2274 ELSE
2275 LogMessage(G_PKG_NAME || '.' || l_api_name || ':found adjustment_id = ' || l_appl_tbl(y).adjustment_id);
2276 LogMessage(G_PKG_NAME || '.' || l_api_name || ':ar_adjusted_amount = ' || l_appl_tbl(y).ar_adjusted_amount);
2277 LogMessage(G_PKG_NAME || '.' || l_api_name || ':ar_adj_remaining_amount = ' || l_appl_tbl(y).ar_adj_remaining_amount);
2278 LogMessage(G_PKG_NAME || '.' || l_api_name || ':ar_adjusted_date = ' || l_appl_tbl(y).ar_adjusted_date);
2279 END IF;
2280 END IF;
2281 end if;
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
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
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);
2302 LogMessage(G_PKG_NAME || '.' || l_api_name || ':ar_apply_date = ' || l_appl_tbl(y).ar_apply_date);
2303 LogMessage(G_PKG_NAME || '.' || l_api_name || ':adjustment_id = ' || l_appl_tbl(y).adjustment_id);
2304 LogMessage(G_PKG_NAME || '.' || l_api_name || ':ar_adjusted_amount = ' || l_appl_tbl(y).ar_adjusted_amount);
2305 LogMessage(G_PKG_NAME || '.' || l_api_name || ':ar_adj_remaining_amount = ' || l_appl_tbl(y).ar_adj_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;
2315 if l_appl_tbl(y).ar_remaining_amount > 0 THEN
2316 if l_appl_tbl(y).ar_remaining_amount > P_PROMISES_TBL(i).AMOUNT_DUE_REMAINING then
2317 l_appl_tbl(y).pro_applied_amount := P_PROMISES_TBL(i).AMOUNT_DUE_REMAINING;
2318 elsif l_appl_tbl(y).ar_remaining_amount <= P_PROMISES_TBL(i).AMOUNT_DUE_REMAINING then
2319 l_appl_tbl(y).pro_applied_amount := l_appl_tbl(y).ar_remaining_amount;
2320 end if;
2321 elsif l_appl_tbl(y).ar_adj_remaining_amount > 0 THEN
2322 if l_appl_tbl(y).ar_adj_remaining_amount > P_PROMISES_TBL(i).AMOUNT_DUE_REMAINING then
2323 l_appl_tbl(y).pro_applied_amount := P_PROMISES_TBL(i).AMOUNT_DUE_REMAINING;
2324 elsif l_appl_tbl(y).ar_adj_remaining_amount <= P_PROMISES_TBL(i).AMOUNT_DUE_REMAINING then
2325 l_appl_tbl(y).pro_applied_amount := l_appl_tbl(y).ar_adj_remaining_amount;
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
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
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
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
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 */
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
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
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
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;
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
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);
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
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
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;
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:');
2429 LogMessage(G_PKG_NAME || '.' || l_api_name || ':amount_due_remaining ' || P_PROMISES_TBL(i).AMOUNT_DUE_REMAINING);
2430 END IF;
2431
2432 if l_callback_date is not null then
2433 UPDATE iex_promise_details
2434 SET amount_due_remaining = P_PROMISES_TBL(i).AMOUNT_DUE_REMAINING,
2435 STATUS = l_status,
2436 STATE = l_state,
2437 CALLBACK_CREATED_YN = 'N',
2438 CALLBACK_DATE = l_callback_date,
2439 last_update_date = sysdate,
2440 last_updated_by = G_USER_ID
2441 WHERE promise_detail_id = P_PROMISES_TBL(i).PROMISE_DETAIL_ID;
2442 else
2443 UPDATE iex_promise_details
2444 SET amount_due_remaining = P_PROMISES_TBL(i).AMOUNT_DUE_REMAINING,
2445 STATUS = l_status,
2446 STATE = l_state,
2447 last_update_date = sysdate,
2448 last_updated_by = G_USER_ID
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 /*
2458 IEX_CONC_REQUEST_MSG_PKG.Log_Error(
2459 P_Concurrent_Request_ID => FND_GLOBAL.CONC_REQUEST_ID,
2460 P_Procedure_name => G_PKG_NAME || '.' || l_api_name,
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
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
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);
2480 LogMessage(G_PKG_NAME || '.' || l_api_name || ':ADJUSTMENT_ID = ' || l_appl_tbl(x).adjustment_id);
2481 LogMessage(G_PKG_NAME || '.' || l_api_name || ':AR_ADJUSTED_AMOUNT = ' || l_appl_tbl(x).ar_adjusted_amount);
2482 LogMessage(G_PKG_NAME || '.' || l_api_name || ':PRO_APPLIED_AMOUNT = ' || l_appl_tbl(x).pro_applied_amount);
2483 END IF;
2484
2485 INSERT INTO iex_prd_appl_xref
2486 (PRD_APPL_XREF_ID
2487 ,PROMISE_DETAIL_ID
2488 ,RECEIVABLE_APPLICATION_ID
2489 ,AMOUNT_APPLIED
2490 ,APPLY_DATE
2491 ,REVERSED_FLAG
2492 ,REVERSED_DATE
2493 ,LAST_UPDATE_DATE
2494 ,LAST_UPDATED_BY
2495 ,LAST_UPDATE_LOGIN
2496 ,CREATION_DATE
2497 ,CREATED_BY
2498 ,PROGRAM_ID
2499 ,OBJECT_VERSION_NUMBER
2500 ,SECURITY_GROUP_ID
2501 ,REQUEST_ID
2502 ,ADJUSTMENT_ID)
2503 VALUES (
2504 iex_prd_appl_xref_s.NEXTVAL
2505 ,P_PROMISES_TBL(i).PROMISE_DETAIL_ID
2506 ,l_appl_tbl(x).receivable_application_id
2507 ,l_appl_tbl(x).pro_applied_amount
2508 ,sysdate
2509 ,null
2510 ,null
2511 ,SYSDATE
2512 ,G_USER_ID
2513 ,G_LOGIN_ID
2514 ,SYSDATE
2515 ,G_USER_ID
2516 ,G_PROGRAM_ID
2517 ,1.0
2518 ,null
2519 ,G_REQUEST_ID
2520 ,l_appl_tbl(x).adjustment_id);
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');
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;
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 */
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);
2550
2551 if l_callback_date is not null then
2552 UPDATE iex_promise_details
2553 SET state = l_state,
2554 CALLBACK_CREATED_YN = 'N',
2555 CALLBACK_DATE = l_callback_date,
2556 last_update_date = sysdate,
2557 last_updated_by = G_USER_ID
2558 WHERE promise_detail_id = P_PROMISES_TBL(i).PROMISE_DETAIL_ID;
2559 else
2560 UPDATE iex_promise_details
2561 SET state = l_state,
2562 last_update_date = sysdate,
2563 last_updated_by = G_USER_ID
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 /*
2573 IEX_CONC_REQUEST_MSG_PKG.Log_Error(
2574 P_Concurrent_Request_ID => FND_GLOBAL.CONC_REQUEST_ID,
2575 P_Procedure_name => G_PKG_NAME || '.' || l_api_name,
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
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');
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
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;
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
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
2619 x_return_status := l_return_status;
2620 -- Standard call to get message count and if count is 1, get message info
2621 FND_MSG_PUB.Count_And_Get(
2622 p_encoded => FND_API.G_FALSE,
2623 p_count => x_msg_count,
2624 p_data => x_msg_data);
2625
2626 EXCEPTION
2627
2628 WHEN FND_API.G_EXC_ERROR THEN
2629 ROLLBACK TO APPLY_PROMISES_FIFO_PVT;
2630 x_return_status := FND_API.G_RET_STS_ERROR;
2631 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2632 -- Begin - Andre Araujo - 09/30/2004- Remove obsolete logging
2633 /*
2634 IEX_CONC_REQUEST_MSG_PKG.Log_Error(
2635 P_Concurrent_Request_ID => FND_GLOBAL.CONC_REQUEST_ID,
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;
2645 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2646 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2647 -- Begin - Andre Araujo - 09/30/2004- Remove obsolete logging
2648 /*
2649 IEX_CONC_REQUEST_MSG_PKG.Log_Error(
2650 P_Concurrent_Request_ID => FND_GLOBAL.CONC_REQUEST_ID,
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;
2660 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2661 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)THEN
2662 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
2663 END IF;
2664 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2665 -- Begin - Andre Araujo - 09/30/2004- Remove obsolete logging
2666 /*
2667 IEX_CONC_REQUEST_MSG_PKG.Log_Error(
2668 P_Concurrent_Request_ID => FND_GLOBAL.CONC_REQUEST_ID,
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
2678
2679 PROCEDURE Copy_Task_Ref_To_Tab( p_counter BINARY_INTEGER,
2680 p_object_type_code varchar2,
2681 p_object_id number ) IS
2682
2683 Cursor Get_Object_Type(l_object_type_code varchar2) IS
2684 select o.name,o.select_id,o.select_name,o.select_details,o.from_table,o.where_clause
2685 from jtf_objects_vl o,
2686 jtf_object_usages u
2687 where trunc(sysdate)
2688 between trunc(nvl(o.start_date_active, sysdate))
2689 and trunc(nvl(o.end_date_active, sysdate))
2690 and u.object_user_code = 'TASK'
2691 and u.object_code = o.object_code
2692 and o.object_code <> 'ESC'
2693 and o.object_code = l_object_type_code;
2694
2695 l_select_id VARCHAR2(200);
2696 l_select_name VARCHAR2(200);
2697 l_select_details VARCHAR2(2000);
2698 l_from_table VARCHAR2(200);
2699 l_where_clause VARCHAR2(2000);
2700 l_CursorID INTEGER;
2701 l_SelectStmt VARCHAR2(2500);
2702 l_Dummy INTEGER;
2703 l_object_name VARCHAR2(360);
2704
2705 l_object_type varchar2(80);
2706 l_object_details varchar2(2000);
2707 l_current_block varchar2(2000);
2708
2709 BEGIN
2710
2711 OPEN Get_Object_Type(p_object_type_code);
2712 FETCH Get_Object_Type INTO l_object_type,l_select_id,l_select_name,l_select_details,l_from_table,l_where_clause;
2713 if Get_Object_Type%FOUND then
2714
2715 l_CursorID := DBMS_SQL.OPEN_CURSOR;
2716
2717 l_SelectStmt := 'SELECT ' || l_select_name;
2718
2719 IF (l_select_details IS NOT NULL) THEN
2720 l_SelectStmt := l_SelectStmt || ',' || l_select_details;
2721 END IF;
2722
2723 l_SelectStmt := l_SelectStmt || ' FROM '|| l_from_table || ' WHERE ' || l_where_clause;
2724
2725 IF l_where_clause is not null THEN
2726 l_SelectStmt := l_SelectStmt || ' AND ' ;
2727 END IF;
2728
2729 l_SelectStmt := l_SelectStmt || l_select_id || ' = :source_object_id ';
2730
2731 DBMS_SQL.PARSE(l_CursorID, l_SelectStmt, 1 );
2732
2733 DBMS_SQL.BIND_VARIABLE(l_CursorID,':source_object_id',p_object_id);
2734
2735 DBMS_SQL.DEFINE_COLUMN(l_CursorID, 1 , l_object_name , 360 );
2736 IF (l_select_details IS NOT NULL) THEN
2737 DBMS_SQL.DEFINE_COLUMN(l_CursorID, 2 , l_object_details , 2000 );
2738 END IF;
2739
2740 l_Dummy := DBMS_SQL.EXECUTE(l_CursorID);
2741
2742 LOOP
2743
2744 IF DBMS_SQL.FETCH_ROWS(l_CursorID) = 0 THEN
2745
2746 EXIT;
2747
2748 END IF;
2749
2750 DBMS_SQL.COLUMN_VALUE(l_CursorID, 1 , l_object_name );
2751 IF (l_select_details IS NOT NULL) THEN
2752 DBMS_SQL.COLUMN_VALUE(l_CursorID, 2 , l_object_details );
2753 END IF;
2754
2755
2756 END LOOP;
2757
2758 DBMS_SQL.CLOSE_CURSOR(l_CursorID);
2759 end if;
2760 CLOSE Get_Object_Type;
2761
2762 --adding for 14738490
2763 l_object_name := substr(l_object_name,1,80);
2764 l_object_details:= substr(l_object_details,1,2000);
2765 LogMessage('l_object_details after truncating:::::'||l_object_name);
2766 LogMessage('l_object_details after truncating:::::'||l_object_details);
2767 --end
2768 G_TASK_REFERENCE_TAB(p_counter).object_type_code := p_object_type_code;
2769 G_TASK_REFERENCE_TAB(p_counter).object_type_name := l_object_type;
2770 G_TASK_REFERENCE_TAB(p_counter).object_name := l_object_name;
2771 G_TASK_REFERENCE_TAB(p_counter).object_id := p_object_id;
2772 G_TASK_REFERENCE_TAB(p_counter).object_details := l_object_details;
2773 G_TASK_REFERENCE_TAB(p_counter).reference_code := null;
2774 G_TASK_REFERENCE_TAB(p_counter).usage := null;
2775
2776
2777 END Copy_Task_Ref_To_Tab;
2778
2779
2780 /**********************
2781 This procedure processing promise callbacks
2782 ***********************/
2783 PROCEDURE PROCESS_PROMISE_CALLBACKS(
2784 p_api_version IN NUMBER,
2785 p_init_msg_list IN VARCHAR2,
2786 p_commit IN VARCHAR2,
2787 P_VALIDATION_LEVEL IN NUMBER,
2788 x_return_status OUT NOCOPY VARCHAR2,
2789 x_msg_count OUT NOCOPY NUMBER,
2790 x_msg_data OUT NOCOPY VARCHAR2)
2791 IS
2792 -- bug 10645014 PNAVEENK
2793 CURSOR C_GET_PROS IS
2794 SELECT
2795 pro.promise_detail_id,
2796 hca.party_id,
2797 pro.resource_id,
2798 pro.cust_account_id,
2799 idel.customer_site_use_id,
2800 idel.delinquency_id
2801 FROM IEX_PROMISE_DETAILS pro, HZ_CUST_ACCOUNTS hca, iex_delinquencies idel
2802 WHERE
2803 pro.cust_account_id = hca.cust_account_id
2804 AND idel.delinquency_id(+) = pro.delinquency_id
2805 AND pro.status = 'COLLECTABLE'
2806 AND pro.state = 'BROKEN_PROMISE'
2807 AND pro.CALLBACK_CREATED_YN = 'N'
2808 AND trunc(sysdate) >= trunc(pro.callback_date);
2809 -- bug 10645014
2810 l_api_name CONSTANT VARCHAR2(30) := 'PROCESS_PROMISE_CALLBACKS';
2811 l_api_version CONSTANT NUMBER := 1.0;
2812 l_return_status VARCHAR2(1);
2813 l_msg_count NUMBER;
2814 l_msg_data VARCHAR2(32767);
2815
2816 l_promise_detail_id NUMBER;
2817 l_task_id NUMBER;
2818 l_party_id NUMBER;
2819 l_resource_id NUMBER;
2820 l_task_name varchar2(80) ;
2821 l_task_type varchar2(30) ;
2822 l_task_status varchar2(30) ;
2823 l_description varchar2(4000);
2824 l_task_priority_name varchar2(30) ;
2825 l_task_priority_id number;
2826 l_owner_id number;
2827 l_owner varchar2(4000);
2828 l_owner_type_code varchar2(4000);
2829 l_customer_id number;
2830 l_cust_account_id number;
2831 l_address_id number;
2832 l_customer_site_use_id number;
2833 l_delinquency_id number;
2834 p_counter number;
2835 l_task_notes_tbl JTF_TASKS_PUB.TASK_NOTES_TBL;
2836 l_miss_task_assign_tbl JTF_TASKS_PUB.TASK_ASSIGN_TBL;
2837 l_miss_task_depends_tbl JTF_TASKS_PUB.TASK_DEPENDS_TBL;
2838 l_miss_task_rsrc_req_tbl JTF_TASKS_PUB.TASK_RSRC_REQ_TBL;
2839 l_miss_task_refer_tbl JTF_TASKS_PUB.TASK_REFER_TBL;
2840 l_miss_task_dates_tbl JTF_TASKS_PUB.TASK_DATES_TBL;
2841 l_miss_task_recur_rec JTF_TASKS_PUB.TASK_RECUR_REC;
2842 l_miss_task_contacts_tbl JTF_TASKS_PUB.TASK_CONTACTS_TBL;
2843
2844 --Begin bug 7317666 21-Nov-2008 barathsr
2845 cursor c_invalid_tasks is
2846 select tsk.task_id,
2847 tsk.object_version_number
2848 --,tsk.task_type_id,typ.name task_type, tsk.task_status_id,st.name,tsk.source_object_id
2849 from jtf_tasks_vl tsk,
2850 jtf_task_types_tl typ,
2851 jtf_task_statuses_vl st
2852 where tsk.source_object_type_code='IEX_PROMISE'
2853 and tsk.task_type_id=typ.task_type_id
2854 and typ.name='Callback'
2855 and tsk.task_status_id=st.task_status_id
2856 and nvl(st.closed_flag, 'N') <>'Y'
2857 and nvl(st.cancelled_flag, 'N')<>'Y'
2858 and nvl(st.completed_flag, 'N')<>'Y'
2859 and exists(select 1 from iex_promise_details prd where tsk.source_object_id = prd.promise_detail_id
2860 and prd.status<>'COLLECTABLE');
2861 l_obj_version_number number;
2862 --End bug 7317666 21-Nov-2008 barathsr
2863 --fix start for bug 14036760 by sunagesh on 08-05-12
2864 l_taskname varchar2(30);
2865 l_statusname varchar2(30); -- fix end for bug 14036760 on 08-05-12
2866
2867
2868 BEGIN
2869
2870 -- Standard Start of API savepoint
2871 SAVEPOINT PROCESS_PROMISE_CALLBACKS_PUB;
2872
2873 -- Standard call to check for call compatibility.
2874 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
2875 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2876 END IF;
2877
2878 -- Initialize message list if p_init_msg_list is set to TRUE.
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
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;
2896 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2897 LogMessage( 'OPEN C_GET_PROS');
2898 END IF;
2899 LOOP
2900
2901 Fetch C_GET_PROS into
2902 l_promise_detail_id,
2903 l_party_id,
2904 l_resource_id,
2905 l_cust_account_id,
2906 l_customer_site_use_id,
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 /*
2925 IEX_CONC_REQUEST_MSG_PKG.Log_Error(
2926 P_Concurrent_Request_ID => FND_GLOBAL.CONC_REQUEST_ID,
2927 P_Procedure_name => G_PKG_NAME || '.' || l_api_name,
2928 P_MESSAGE => 'No resource_ID for promise_detail_id = ' || l_promise_detail_id || '. Cannot create task.');
2929 */
2930 -- End - Andre Araujo - 09/30/2004- Remove obsolete logging
2931 else
2932
2933 --fix start for bug 14036760 by sunagesh on 08-05-12
2934 SELECT jvl.name into l_statusname
2935 FROM jtf_task_statuses_vl jvl , jtf_task_statuses_tl jtl
2936 WHERE jvl.task_status_id= jtl.task_status_id
2937 AND jtl.language='US'
2938 AND jtl.name = 'Open'
2939 AND trunc(NVL (jvl.end_date_active, SYSDATE)) >= trunc(SYSDATE)
2940 AND trunc(NVL (jvl.start_date_active, SYSDATE)) <= trunc(SYSDATE);
2941 LogMessage('Status name - '||l_statusname);
2942
2943 SELECT jvl.name into l_taskname
2944 FROM jtf_task_types_vl jvl , jtf_task_types_tl jtl
2945 WHERE jvl.task_type_id= jtl.task_type_id
2946 AND jtl.language='US'
2947 AND jtl.name = 'Callback'
2948 AND trunc(NVL (jvl.end_date_active, SYSDATE)) >= trunc(SYSDATE)
2949 AND trunc(NVL (jvl.start_date_active, SYSDATE)) <= trunc(SYSDATE);
2950 LogMessage('Task name - '||l_taskname);
2951 --fix end for bug 14036760 by sunagesh on 08-05-12
2952
2953
2954 --Bug4201040. Fix By LKKUMAR on 24-Jan-2006. Start.
2955 --l_task_name := 'Oracle Collections Callback for Broken Promise';
2956 l_task_name := 'Broken Promise Callback';
2957 -- l_task_status := 'Open'; -- commented for bug 14036760
2958 l_task_status := l_statusname; -- added for bug 14036760
2959 -- l_task_type := 'Callback'; -- commented for bug 14036760
2960 l_task_type := l_taskname; -- added for bug 14036760
2961 --l_description := 'Oracle Collections Callback for Broken Promise';
2962 l_description := 'Broken Promise Callback';
2963 --Bug4201040. Fix By LKKUMAR on 24-Jan-2006. End.
2964 l_owner_type_code := 'RS_EMPLOYEE';
2965 l_owner_id := l_resource_id;
2966 l_customer_id := l_party_id;
2967
2968 /* begin kasreeni 01/20/2006 Create task reference for Party_id, ACCOUNT and bill to */
2969
2970 G_TASK_REFERENCE_TAB := l_miss_task_refer_tbl;
2971 p_counter := 1;
2972 copy_Task_ref_to_Tab(p_counter, 'IEX_ACCOUNT', l_cust_account_id);
2973
2974 if (l_customer_site_use_id is not null) then
2975 p_counter := p_counter + 1;
2976 copy_Task_ref_to_Tab(p_counter, 'IEX_BILLTO', l_customer_site_use_id);
2977 p_counter := p_counter + 1;
2978 copy_Task_ref_to_Tab(p_counter, 'IEX_DELINQUENCY', l_delinquency_id);
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(
2988 p_api_version => p_api_version,
2989 p_init_msg_list => p_init_msg_list,
2990 p_commit => p_commit,
2991 p_task_name => l_task_name,
2992 p_task_type_name => l_task_type,
2993 p_task_status_name => l_task_status,
2994 p_owner_type_code => l_owner_type_code,
2995 p_owner_id => l_owner_id,
2996 p_description => l_description,
2997 p_customer_id => l_customer_id,
2998 P_CUST_ACCOUNT_ID => l_cust_account_id,
2999 P_SOURCE_OBJECT_TYPE_CODE => 'IEX_PROMISE',
3000 P_SOURCE_OBJECT_ID => l_promise_detail_id,
3001 P_SOURCE_OBJECT_NAME => l_promise_detail_id,
3002 p_task_assign_tbl => l_miss_task_assign_tbl,
3003 p_task_depends_tbl => l_miss_task_depends_tbl,
3004 p_task_rsrc_req_tbl => l_miss_task_rsrc_req_tbl,
3005 p_task_refer_tbl => G_TASK_REFERENCE_TAB,
3006 p_task_dates_tbl => l_miss_task_dates_tbl,
3007 p_task_notes_tbl => l_task_notes_tbl,
3008 p_task_recur_rec => l_miss_task_recur_rec,
3009 p_task_contacts_tbl => l_miss_task_contacts_tbl,
3010 x_return_status => l_return_status,
3011 x_msg_count => l_msg_count,
3012 x_msg_data => l_msg_data,
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
3022 END IF;
3023 -- Begin - Andre Araujo - 09/30/2004- Remove obsolete logging
3024 /*
3025 IEX_CONC_REQUEST_MSG_PKG.Log_Error(
3026 P_Concurrent_Request_ID => FND_GLOBAL.CONC_REQUEST_ID,
3027 P_Procedure_name => G_PKG_NAME || '.' || l_api_name,
3028 P_MESSAGE => 'Call JTF_TASKS_PUB.CREATE_TASK failed');
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;
3038
3039 -- update iex_promise_details table
3040 UPDATE iex_promise_details
3041 SET CALLBACK_CREATED_YN = 'Y',
3042 last_update_date = sysdate,
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 /*
3052 IEX_CONC_REQUEST_MSG_PKG.Log_Error(
3053 P_Concurrent_Request_ID => FND_GLOBAL.CONC_REQUEST_ID,
3054 P_Procedure_name => G_PKG_NAME || '.' || l_api_name,
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
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
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.
3083 IF FND_API.To_Boolean( p_commit ) THEN
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
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(
3097 P_API_VERSION => p_api_version,
3098 P_INIT_MSG_LIST => p_init_msg_list,
3099 P_COMMIT => p_commit,
3100 P_OBJECT_VERSION_NUMBER => l_obj_version_number,
3101 P_TASK_ID => rec1.task_id,
3102 P_TASK_STATUS_NAME => 'Cancelled',
3103 x_return_status => l_return_status,
3104 x_msg_count => l_msg_count,
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
3114
3115 COMMIT WORK;
3116 END IF;
3117 --End bug 7317666 21-Nov-2008 barathsr
3118
3119 x_return_status := l_return_status;
3120 -- Standard call to get message count and if count is 1, get message info
3121 FND_MSG_PUB.Count_And_Get(
3122 p_encoded => FND_API.G_FALSE,
3123 p_count => x_msg_count,
3124 p_data => x_msg_data);
3125
3126 EXCEPTION
3127 WHEN FND_API.G_EXC_ERROR THEN
3128 ROLLBACK TO PROCESS_PROMISE_CALLBACKS_PUB;
3129 x_return_status := FND_API.G_RET_STS_ERROR;
3130 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3131 -- Begin - Andre Araujo - 09/30/2004- Remove obsolete logging
3132 /*
3133 IEX_CONC_REQUEST_MSG_PKG.Log_Error(
3134 P_Concurrent_Request_ID => FND_GLOBAL.CONC_REQUEST_ID,
3135 P_Procedure_name => G_PKG_NAME || '.' || l_api_name,
3136 P_MESSAGE => 'Failed to process promise callbacks');
3137 */
3138 -- End - Andre Araujo - 09/30/2004- Remove obsolete logging
3139 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3140 ROLLBACK TO PROCESS_PROMISE_CALLBACKS_PUB;
3141 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3142 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3143 -- Begin - Andre Araujo - 09/30/2004- Remove obsolete logging
3144 /*
3145 IEX_CONC_REQUEST_MSG_PKG.Log_Error(
3146 P_Concurrent_Request_ID => FND_GLOBAL.CONC_REQUEST_ID,
3147 P_Procedure_name => G_PKG_NAME || '.' || l_api_name,
3148 P_MESSAGE => 'Failed to process promise callbacks');
3149 */
3150 -- End - Andre Araujo - 09/30/2004- Remove obsolete logging
3151 WHEN OTHERS THEN
3152 ROLLBACK TO PROCESS_PROMISE_CALLBACKS_PUB;
3153 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3154 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3155 THEN
3156 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
3157 END IF;
3158 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3159 -- Begin - Andre Araujo - 09/30/2004- Remove obsolete logging
3160 /*
3161 IEX_CONC_REQUEST_MSG_PKG.Log_Error(
3162 P_Concurrent_Request_ID => FND_GLOBAL.CONC_REQUEST_ID,
3163 P_Procedure_name => G_PKG_NAME || '.' || l_api_name,
3164 P_MESSAGE => 'Failed to process promise callbacks');
3165 */
3166 -- End - Andre Araujo - 09/30/2004- Remove obsolete logging
3167
3168 END;
3169
3170 /**********************
3171 This procedure reopens promises for delinquencies that have been reopened.
3172 ***********************/
3173 PROCEDURE REOPEN_PROMISES(
3174 P_API_VERSION IN NUMBER,
3175 P_INIT_MSG_LIST IN VARCHAR2,
3176 P_COMMIT IN VARCHAR2,
3177 P_VALIDATION_LEVEL IN NUMBER,
3178 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
3179 X_MSG_COUNT OUT NOCOPY NUMBER,
3180 X_MSG_DATA OUT NOCOPY VARCHAR2,
3181 p_dels_tbl IN DBMS_SQL.NUMBER_TABLE /*table of delinquency ids*/)
3182 IS
3183 l_api_name CONSTANT VARCHAR2(30) := 'REOPEN_PROMISES';
3184 l_api_version CONSTANT NUMBER := 1.0;
3185 l_return_status VARCHAR2(1);
3186 l_msg_count NUMBER;
3187 l_msg_data VARCHAR2(32767);
3188
3189 l_promise_id NUMBER;
3190 l_promise_date DATE;
3191 l_del_count NUMBER;
3192 vSQL varchar2(10000);
3193
3194 Type refCur is Ref Cursor;
3195 promises_cur refCur;
3196 l_callback_date DATE;
3197 BEGIN
3198 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
3199
3200 /*
3201 Commented out whole procedure because PROB now can apply payments to promises or
3202 reverse payments from promises automatically.
3203 We do not need to close or reopen promises if delinquency is closed or reopened - all this will be done by PROB.
3204 We are obsoleting status CLOSED.
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
3214 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3215 END IF;
3216
3217 -- Initialize message list if p_init_msg_list is set to TRUE
3218 IF FND_API.To_Boolean(p_init_msg_list) THEN
3219 FND_MSG_PUB.initialize;
3220 END IF;
3221
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;
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
3236 vSQL := 'SELECT ' ||
3237 'PROMISE_DETAIL_ID, PROMISE_DATE ' ||
3238 'FROM ' ||
3239 'IEX_PROMISE_DETAILS ' ||
3240 'WHERE ' ||
3241 'DELINQUENCY_ID is not null and ' ||
3242 'DELINQUENCY_ID = :del and ' ||
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);
3252
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
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
3270 SET STATUS = 'BROKEN',
3271 CALLBACK_CREATED_YN = 'N',
3272 CALLBACK_DATE = l_callback_date,
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',
3282 last_update_date = sysdate,
3283 last_updated_by = G_USER_ID
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(
3293 -- P_Concurrent_Request_ID => FND_GLOBAL.CONC_REQUEST_ID,
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;
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
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
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
3322 x_return_status := l_return_status;
3323 -- Standard call to get message count and if count is 1, get message info
3324 FND_MSG_PUB.Count_And_Get(
3325 p_encoded => FND_API.G_FALSE,
3326 p_count => x_msg_count,
3327 p_data => x_msg_data);
3328
3329 EXCEPTION
3330 WHEN FND_API.G_EXC_ERROR THEN
3331 ROLLBACK TO REOPEN_PROMISES_PVT;
3332 x_return_status := FND_API.G_RET_STS_ERROR;
3333 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3334 -- Begin - Andre Araujo - 09/30/2004- Remove obsolete logging
3335 --IEX_CONC_REQUEST_MSG_PKG.Log_Error(
3336 -- P_Concurrent_Request_ID => FND_GLOBAL.CONC_REQUEST_ID,
3337 -- P_Procedure_name => 'IEX_PROMISES_BATCH_PUB.REOPEN_PROMISES',
3338 -- P_MESSAGE => 'Failed to reopen promises.' );
3339 -- End - Andre Araujo - 09/30/2004- Remove obsolete logging
3340 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3341 ROLLBACK TO REOPEN_PROMISES_PVT;
3342 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3343 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3344 -- Begin - Andre Araujo - 09/30/2004- Remove obsolete logging
3345 --IEX_CONC_REQUEST_MSG_PKG.Log_Error(
3346 -- P_Concurrent_Request_ID => FND_GLOBAL.CONC_REQUEST_ID,
3347 -- P_Procedure_name => 'IEX_PROMISES_BATCH_PUB.REOPEN_PROMISES',
3348 -- P_MESSAGE => 'Failed to reopen promises.' );
3349 -- End - Andre Araujo - 09/30/2004- Remove obsolete logging
3350 WHEN OTHERS THEN
3351 ROLLBACK TO REOPEN_PROMISES_PVT;
3352 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3353 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3354 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
3355 END IF;
3356 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3357 -- Begin - Andre Araujo - 09/30/2004- Remove obsolete logging
3358 --IEX_CONC_REQUEST_MSG_PKG.Log_Error(
3359 -- P_Concurrent_Request_ID => FND_GLOBAL.CONC_REQUEST_ID,
3360 -- P_Procedure_name => 'IEX_PROMISES_BATCH_PUB.REOPEN_PROMISES',
3361 -- P_MESSAGE => 'Failed to reopen promises.' );
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;
3371 END;