[Home] [Help]
PACKAGE BODY: APPS.IEX_PROMISES_BATCH_PUB
Source
1 PACKAGE BODY IEX_PROMISES_BATCH_PUB as
2 /* $Header: iexpyrbb.pls 120.13.12010000.6 2009/01/21 06:18:45 barathsr 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 -- Start bug#5874874 gnramasa 25-Apr-07
690
691 CURSOR UPDATE_IEX_SUMMARY IS
692 SELECT COUNT(CUST_ACCOUNT_ID) NUMB,
693 sum(AMOUNT_DUE_REMAINING) broken_amount,
694 sum(PROMISE_AMOUNT) promise_amount,
695 CUST_ACCOUNT_ID
696 FROM IEX_PROMISE_DETAILS
697 WHERE STATE = 'BROKEN_PROMISE'
698 AND STATUS = 'COLLECTABLE'
699 AND NVL(AMOUNT_DUE_REMAINING,0) > 0
700 GROUP BY CUST_ACCOUNT_ID;
701
702 CURSOR UPDATE_IEX_ACTIVE_PRO IS
703 SELECT count(cust_account_id)active_promises,cust_account_id
704 FROM iex_promise_details pd
705 WHERE pd.state = 'BROKEN_PROMISE'
706 AND(pd.uwq_status IS NULL OR pd.uwq_status = 'ACTIVE'
707 OR(TRUNC(pd.uwq_active_date) <= TRUNC(sysdate)
708 AND pd.uwq_status = 'PENDING'))
709 GROUP BY CUST_ACCOUNT_ID;
710
711 CURSOR UPDATE_IEX_COMP_PRO IS
712 SELECT count(cust_account_id)complete_promises,cust_account_id
713 FROM iex_promise_details pd
714 WHERE pd.state = 'BROKEN_PROMISE'
715 AND(pd.uwq_status = 'COMPLETE'
716 AND(TRUNC(pd.uwq_complete_date) +
717 fnd_profile.VALUE('IEX_UWQ_COMPLETION_DAYS') > TRUNC(sysdate)))
718 GROUP BY CUST_ACCOUNT_ID;
719
720 CURSOR UPDATE_IEX_PEND_PRO IS
721 SELECT count(cust_account_id)pending_promises,cust_account_id
722 FROM iex_promise_details pd
723 WHERE pd.state = 'BROKEN_PROMISE' AND(pd.uwq_status = 'PENDING'
724 AND(TRUNC(pd.uwq_active_date) > TRUNC(sysdate)))
725 GROUP BY CUST_ACCOUNT_ID;
726
727 -- End bug#5874874 gnramasa 25-Apr-07
728 */
729 --End bug 6053792 gnramasa 17-May-2007
730 BEGIN
731 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
732 LogMessage('$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$');
733 LogMessage(G_PKG_NAME || '.' || l_api_name || ':Start');
734 END IF;
735
736
737 -- Standard start of API savepoint
738 SAVEPOINT PROCESS_ALL_PROMISES_PVT;
739
740 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
741 LogMessage(G_PKG_NAME || '.' || l_api_name || ':Savepoint is established');
742 END IF;
743 -- Standard call to check for call compatibility
744 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
745 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
746 END IF;
747
748 -- Initialize message list if p_init_msg_list is set to TRUE
749 IF FND_API.To_Boolean(p_init_msg_list) THEN
750 FND_MSG_PUB.initialize;
751 END IF;
752
753 -- Initialize API return status to success
754 l_return_status := FND_API.G_RET_STS_SUCCESS;
755
756 -- START OF BODY OF API
757 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
758 LogMessage(G_PKG_NAME || '.' || l_api_name || ':Start of body');
759 END IF;
760
761 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
762 LogMessage('********************************************');
763 LogMessage(G_PKG_NAME || '.' || l_api_name || ':Calling PROCESS_REVERSALS for AR ');
764 END IF;
765 PROCESS_REVERSALS(
766 P_API_VERSION => 1.0,
767 P_INIT_MSG_LIST => FND_API.G_TRUE,
768 P_COMMIT => FND_API.G_TRUE,
769 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
770 X_RETURN_STATUS => l_return_status,
771 X_MSG_COUNT => l_msg_count,
772 X_MSG_DATA => l_msg_data,
773 P_TYPE => 'AR');
774
775 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
776 LogMessage('********************************************');
777 LogMessage(G_PKG_NAME || '.' || l_api_name || ':Calling PROCESS_REVERSALS for OKL ');
778 END IF;
779 PROCESS_REVERSALS(
780 P_API_VERSION => 1.0,
781 P_INIT_MSG_LIST => FND_API.G_TRUE,
782 P_COMMIT => FND_API.G_TRUE,
783 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
784 X_RETURN_STATUS => l_return_status,
785 X_MSG_COUNT => l_msg_count,
786 X_MSG_DATA => l_msg_data,
787 P_TYPE => 'OKL');
788
789 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
790 LogMessage('********************************************');
791 LogMessage(G_PKG_NAME || '.' || l_api_name || ':Processing promises on AR invoices...');
792 END IF;
793 PROCESS_PROMISES(
794 P_API_VERSION => 1.0,
795 P_INIT_MSG_LIST => FND_API.G_TRUE,
796 P_COMMIT => FND_API.G_TRUE,
797 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
798 X_RETURN_STATUS => l_return_status,
799 X_MSG_COUNT => l_msg_count,
800 X_MSG_DATA => l_msg_data,
801 P_TYPE => 'INV');
802
803 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
804 LogMessage('********************************************');
805 LogMessage(G_PKG_NAME || '.' || l_api_name || ':Processing promises on AR account...');
806 END IF;
807 PROCESS_PROMISES(
808 P_API_VERSION => 1.0,
809 P_INIT_MSG_LIST => FND_API.G_TRUE,
810 P_COMMIT => FND_API.G_TRUE,
811 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
812 X_RETURN_STATUS => l_return_status,
813 X_MSG_COUNT => l_msg_count,
814 X_MSG_DATA => l_msg_data,
815 P_TYPE => 'ACC');
816
817 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
818 LogMessage('********************************************');
819 LogMessage(G_PKG_NAME || '.' || l_api_name || ':Processing promises on OKL contracts...');
820 END IF;
821 PROCESS_PROMISES(
822 P_API_VERSION => 1.0,
823 P_INIT_MSG_LIST => FND_API.G_TRUE,
824 P_COMMIT => FND_API.G_TRUE,
825 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
826 X_RETURN_STATUS => l_return_status,
827 X_MSG_COUNT => l_msg_count,
828 X_MSG_DATA => l_msg_data,
829 P_TYPE => 'CNTR');
830
831 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
832 LogMessage(G_PKG_NAME || '.' || l_api_name || ':End of body');
833 END IF;
834 -- END OF BODY OF API
835
836 -- Standard check of p_commit.
837 IF FND_API.To_Boolean( p_commit ) THEN
838 COMMIT WORK;
839 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
840 LogMessage(G_PKG_NAME || '.' || l_api_name || ':Commited work');
841 END IF;
842 END IF;
843
844 x_return_status := l_return_status;
845 -- Standard call to get message count and if count is 1, get message info
846 FND_MSG_PUB.Count_And_Get(
847 p_encoded => FND_API.G_FALSE,
848 p_count => x_msg_count,
849 p_data => x_msg_data);
850
851 -- Start bug#5874874 gnramasa 25-Apr-07
852 BEGIN
853 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
854 LogMessage(G_PKG_NAME || '.' || l_api_name || 'Started Updating IEX_DLN_UWQ_SUMMARY');
855 END IF;
856 -- Bug #6251572 bibeura 24-OCT-2007 modified the following Update statement
857 FOR I IN UPDATE_IEX_SUMMARY
858 LOOP
859 UPDATE IEX_DLN_UWQ_SUMMARY
860 SET NUMBER_OF_PROMISES = I.NUMB,
861 BROKEN_PROMISE_AMOUNT = I.BROKEN_AMOUNT,
862 PROMISE_AMOUNT = I.PROMISE_AMOUNT,
863 LAST_UPDATE_DATE= SYSDATE
864 WHERE PARTY_ID = I.PARTY_CUST_ID
865 AND CUST_ACCOUNT_ID = NVL(I.CUST_ACCOUNT_ID,CUST_ACCOUNT_ID)
866 AND SITE_USE_ID = NVL(I.CUSTOMER_SITE_USE_ID,SITE_USE_ID);
867 END LOOP;
868
869 COMMIT;
870
871 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
872 LogMessage(G_PKG_NAME || '.' || l_api_name || 'Finished Updating IEX_DLN_UWQ_SUMMARY');
873 END IF;
874 EXCEPTION WHEN OTHERS THEN
875 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
876 LogMessage(G_PKG_NAME || '.' || l_api_name || 'Error Occurred while updating IEX_DLN_UWQ_SUMMARY ' || SQLERRM );
877 END IF;
878 END;
879
880 BEGIN
881 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
882 LogMessage(G_PKG_NAME || '.' || l_api_name || 'Started Updating IEX_DLN_UWQ_SUMMARY ACTIVE COLUMN');
883 END IF;
884 -- Bug #6251572 bibeura 24-OCT-2007 modified the following Update statement
885 FOR I IN UPDATE_IEX_ACTIVE_PRO
886 LOOP
887 UPDATE IEX_DLN_UWQ_SUMMARY
888 SET ACTIVE_PROMISES = I.ACTIVE_PROMISES,
889 LAST_UPDATE_DATE= SYSDATE
890 WHERE PARTY_ID = I.PARTY_CUST_ID
891 AND CUST_ACCOUNT_ID = NVL(I.CUST_ACCOUNT_ID,CUST_ACCOUNT_ID)
892 AND SITE_USE_ID = NVL(I.CUSTOMER_SITE_USE_ID,SITE_USE_ID);
893 END LOOP;
894
895 COMMIT;
896
897 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
898 LogMessage(G_PKG_NAME || '.' || l_api_name || 'Finished Updating IEX_DLN_UWQ_SUMMARY ACTIVE COLUMN');
899 END IF;
900 EXCEPTION WHEN OTHERS THEN
901 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
902 LogMessage(G_PKG_NAME || '.' || l_api_name || 'Error Occurred while updating IEX_DLN_UWQ_SUMMARY ACTIVE' || SQLERRM );
903 END IF;
904 END;
905
906 BEGIN
907 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
908 LogMessage(G_PKG_NAME || '.' || l_api_name || 'Started Updating IEX_DLN_UWQ_SUMMARY COMPLETED COLUMN');
909 END IF;
910 -- Bug #6251572 bibeura 24-OCT-2007 modified the following Update statement
911 FOR I IN UPDATE_IEX_COMP_PRO
912 LOOP
913 UPDATE IEX_DLN_UWQ_SUMMARY
914 SET COMPLETE_PROMISES = I.COMPLETE_PROMISES,
915 LAST_UPDATE_DATE= SYSDATE
916 WHERE PARTY_ID = I.PARTY_CUST_ID
917 AND CUST_ACCOUNT_ID = NVL(I.CUST_ACCOUNT_ID,CUST_ACCOUNT_ID)
918 AND SITE_USE_ID = NVL(I.CUSTOMER_SITE_USE_ID,SITE_USE_ID);
919 END LOOP;
920
921 COMMIT;
922
923 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
924 LogMessage(G_PKG_NAME || '.' || l_api_name || 'Finished Updating IEX_DLN_UWQ_SUMMARY COMPLETED COLUMN');
925 END IF;
926 EXCEPTION WHEN OTHERS THEN
927 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
928 LogMessage(G_PKG_NAME || '.' || l_api_name ||
929 'Error Occurred while updating IEX_DLN_UWQ_SUMMARY COMPLETED COLUMN' || SQLERRM );
930 END IF;
931 END;
932
933 BEGIN
934 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
935 LogMessage(G_PKG_NAME || '.' || l_api_name || 'Started Updating IEX_DLN_UWQ_SUMMARY PENDING COLUMN');
936 END IF;
937 -- Bug #6251572 bibeura 24-OCT-2007 modified the following Update statement
938 FOR I IN UPDATE_IEX_PEND_PRO
939 LOOP
940 UPDATE IEX_DLN_UWQ_SUMMARY
941 SET PENDING_PROMISES = I.PENDING_PROMISES,
942 LAST_UPDATE_DATE = SYSDATE
943 WHERE PARTY_ID = I.PARTY_CUST_ID
944 AND CUST_ACCOUNT_ID = NVL(I.CUST_ACCOUNT_ID,CUST_ACCOUNT_ID)
945 AND SITE_USE_ID = NVL(I.CUSTOMER_SITE_USE_ID,SITE_USE_ID);
946 END LOOP;
947
948 COMMIT;
949
950 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
951 LogMessage(G_PKG_NAME || '.' || l_api_name || ' Finished Updating IEX_DLN_UWQ_SUMMARY PENDING COLUMN');
952 END IF;
953 EXCEPTION WHEN OTHERS THEN
954 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
955 LogMessage(G_PKG_NAME || '.' || l_api_name ||
956 'Error Occurred while updating IEX_DLN_UWQ_SUMMARY PENDING COLUMN ' || SQLERRM );
957 END IF;
958 END;
959
960 -- End bug#5874874 gnramasa 25-Apr-07
961 EXCEPTION
962 WHEN FND_API.G_EXC_ERROR THEN
963 ROLLBACK TO PROCESS_ALL_PROMISES_PVT;
964 x_return_status := FND_API.G_RET_STS_ERROR;
965 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
966 -- Begin - Andre Araujo - 09/30/2004- Remove obsolete logging
967 /*
968 IEX_CONC_REQUEST_MSG_PKG.Log_Error(
969 P_Concurrent_Request_ID => FND_GLOBAL.CONC_REQUEST_ID,
970 P_Procedure_name => G_PKG_NAME || '.' || l_api_name,
971 P_MESSAGE => 'Failed to process all promises');
972 */
973 -- End - Andre Araujo - 09/30/2004- Remove obsolete logging
974 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
975 LogMessage(G_PKG_NAME || '.' || l_api_name || ': In G_EXC_ERROR exception. Failed to process all promises');
976 END IF;
977 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
978 ROLLBACK TO PROCESS_ALL_PROMISES_PVT;
979 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
980 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
981 -- Begin - Andre Araujo - 09/30/2004- Remove obsolete logging
982 /*
983 IEX_CONC_REQUEST_MSG_PKG.Log_Error(
984 P_Concurrent_Request_ID => FND_GLOBAL.CONC_REQUEST_ID,
985 P_Procedure_name => G_PKG_NAME || '.' || l_api_name,
986 P_MESSAGE => 'Failed to process all promises');
987 */
988 -- End - Andre Araujo - 09/30/2004- Remove obsolete logging
989 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
990 LogMessage(G_PKG_NAME || '.' || l_api_name || ': In G_EXC_UNEXPECTED_ERROR exception. Failed to process all promises');
991 END IF;
992 WHEN OTHERS THEN
993 ROLLBACK TO PROCESS_ALL_PROMISES_PVT;
994 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
995 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)THEN
996 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
997 END IF;
998 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
999 -- Begin - Andre Araujo - 09/30/2004- Remove obsolete logging
1000 /*
1001 IEX_CONC_REQUEST_MSG_PKG.Log_Error(
1002 P_Concurrent_Request_ID => FND_GLOBAL.CONC_REQUEST_ID,
1003 P_Procedure_name => G_PKG_NAME || '.' || l_api_name,
1004 P_MESSAGE => 'Failed to process all promises');
1005 */
1006 -- End - Andre Araujo - 09/30/2004- Remove obsolete logging
1007 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1008 LogMessage(G_PKG_NAME || '.' || l_api_name || ': In OTHERS exception. Failed to process all promises');
1009 END IF;
1010 END;
1011
1012 /**********************
1013 This procedure unapply promise applications that have been reversed in AR
1014 ***********************/
1015 PROCEDURE PROCESS_REVERSALS(
1016 P_API_VERSION IN NUMBER,
1017 P_INIT_MSG_LIST IN VARCHAR2,
1018 P_COMMIT IN VARCHAR2,
1019 P_VALIDATION_LEVEL IN NUMBER,
1020 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1021 X_MSG_COUNT OUT NOCOPY NUMBER,
1022 X_MSG_DATA OUT NOCOPY VARCHAR2,
1023 P_TYPE IN VARCHAR2)
1024 IS
1025 l_api_name CONSTANT VARCHAR2(30) := 'PROCESS_REVERSALS';
1026 l_api_version CONSTANT NUMBER := 1.0;
1027 l_return_status VARCHAR2(1);
1028 l_msg_count NUMBER;
1029 l_msg_data VARCHAR2(32767);
1030 vSQL varchar2(10000);
1031 Type refCur is Ref Cursor;
1032 promises_cur refCur;
1033 l_appl_tbl IEX_PROMISES_BATCH_PUB.REVERSE_APPLS_TBL;
1034 i NUMBER;
1035 nCount NUMBER;
1036 l_promise_detail_id NUMBER;
1037 l_promise_date DATE;
1038 l_status VARCHAR2(30);
1039 l_promise_amount NUMBER;
1040 l_amount_due_remaining NUMBER;
1041 l_amount_applied NUMBER;
1042 l_receivable_application_id NUMBER;
1043 l_new_status VARCHAR2(30) := null;
1044 l_callback_date DATE;
1045 l_new_remaining_amount NUMBER;
1046
1047 BEGIN
1048 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1049 LogMessage(G_PKG_NAME || '.' || l_api_name || ':Start');
1050 END IF;
1051
1052
1053
1054 -- Standard start of API savepoint
1055 SAVEPOINT PROCESS_REVERSALS_PVT;
1056
1057 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1058 LogMessage(G_PKG_NAME || '.' || l_api_name || ':Savepoint is established');
1059 END IF;
1060 -- Standard call to check for call compatibility
1061 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1062 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1063 END IF;
1064
1065 -- Initialize message list if p_init_msg_list is set to TRUE
1066 IF FND_API.To_Boolean(p_init_msg_list) THEN
1067 FND_MSG_PUB.initialize;
1068 END IF;
1069
1070 -- Initialize API return status to success
1071 l_return_status := FND_API.G_RET_STS_SUCCESS;
1072
1073 -- START OF BODY OF API
1074 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1075 LogMessage(G_PKG_NAME || '.' || l_api_name || ':Start of body');
1076 END IF;
1077
1078 if P_TYPE = 'AR' then
1079 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1080 LogMessage(G_PKG_NAME || '.' || l_api_name || ':Searching for applications that still applied to promises but reversed in AR ');
1081 END IF;
1082 -- search for all applications that still applied to promises but reversed in AR
1083 vSQL := 'SELECT ' ||
1084 'prd.promise_detail_id, ' ||
1085 'prd.promise_date, ' ||
1086 'prd.status, ' ||
1087 'prd.promise_amount, ' ||
1088 'prd.amount_due_remaining, ' ||
1089 'pax.amount_applied, ' ||
1090 'raa.receivable_application_id ' ||
1091 'FROM ' ||
1092 'AR_RECEIVABLE_APPLICATIONS raa, ' ||
1093 'IEX_prd_appl_xref pax, ' ||
1094 'iex_promise_details prd ' ||
1095 'WHERE ' ||
1096 'raa.receivable_application_id = pax.receivable_application_id and ' ||
1097 'raa.status in (''APP'', ''ACC'') and ' ||
1098 'raa.amount_applied > 0 and ' ||
1099 'raa.reversal_gl_date is not null and ' ||
1100 'pax.reversed_flag is null and ' ||
1101 'pax.reversed_date is null and ' ||
1102 'pax.receivable_application_id is not null and ' ||
1103 'pax.promise_detail_id = prd.promise_detail_id and ' ||
1104 'prd.status in (''COLLECTABLE'', ''FULFILLED'') and ' ||
1105 'prd.org_id = raa.org_id ' || --Added for bug 7237026 barathsr 31-Dec-2008
1106 'ORDER BY raa.receivable_application_id';
1107
1108 else
1109 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1110 LogMessage(G_PKG_NAME || '.' || l_api_name || ':Searching for applications that still applied to promises but reversed in OKL ');
1111 END IF;
1112 -- search for all applications that still applied to promises but reversed in OKL
1113 /* replaced the statement just below to fix a perf bug 4930383
1114 vSQL := 'SELECT ' ||
1115 'prd.promise_detail_id, ' ||
1116 'prd.promise_date, ' ||
1117 'prd.status, ' ||
1118 'prd.promise_amount, ' ||
1119 'prd.amount_due_remaining, ' ||
1120 'pax.amount_applied, ' ||
1121 'raa.receivable_application_id ' ||
1122 'FROM ' ||
1123 'IEX_OKL_PAYMENTS_V raa, ' ||
1124 'IEX_prd_appl_xref pax, ' ||
1125 'iex_promise_details prd ' ||
1126 'WHERE ' ||
1127 'raa.receivable_application_id = pax.receivable_application_id and ' ||
1128 'raa.amount_applied > 0 and ' ||
1129 'raa.reversal_gl_date is not null and ' ||
1130 'pax.reversed_flag is null and ' ||
1131 'pax.reversed_date is null and ' ||
1132 'pax.promise_detail_id = prd.promise_detail_id and ' ||
1133 'prd.status in (''COLLECTABLE'', ''FULFILLED'') ' ||
1134 'ORDER BY raa.receivable_application_id';
1135 */
1136
1137 vSQL := 'SELECT ' ||
1138 'prd.promise_detail_id, ' ||
1139 'prd.promise_date, ' ||
1140 'prd.status, ' ||
1141 'prd.promise_amount, ' ||
1142 'prd.amount_due_remaining, ' ||
1143 'pax.amount_applied, ' ||
1144 'pax.receivable_application_id ' ||
1145 'FROM ' ||
1146 'IEX_prd_appl_xref pax, ' ||
1147 'iex_promise_details prd, ' ||
1148 'AR_SYSTEM_PARAMETERS asp ' ||--Added for bug 73237026 barathsr 31-Dec-2008
1149 'WHERE ' ||
1150 'pax.receivable_application_id IN (select receivable_application_id from IEX_OKL_PAYMENTS_V where ' ||
1151 'amount_applied > 0 and ' ||
1152 'reversal_gl_date is not null) and ' ||
1153 'pax.reversed_flag is null and ' ||
1154 'pax.reversed_date is null and ' ||
1155 'pax.receivable_application_id is not null and ' ||
1156 'pax.promise_detail_id = prd.promise_detail_id and ' ||
1157 'prd.status in (''COLLECTABLE'', ''FULFILLED'') and ' ||
1158 'prd.org_id = asp.org_id ' || --Added for bug 73237026 barathsr
1159 'ORDER BY pax.receivable_application_id';
1160 end if;
1161
1162 open promises_cur for vSQL ;
1163 i := 0;
1164 LOOP
1165 fetch promises_cur into
1166 l_promise_detail_id,
1167 l_promise_date,
1168 l_status,
1169 l_promise_amount,
1170 l_amount_due_remaining,
1171 l_amount_applied,
1172 l_receivable_application_id;
1173 exit when promises_cur%NOTFOUND;
1174 i := i+1;
1175 l_appl_tbl(i).promise_detail_id := l_promise_detail_id;
1176 l_appl_tbl(i).promise_date := l_promise_date;
1177 l_appl_tbl(i).status := l_status;
1178 l_appl_tbl(i).promise_amount := l_promise_amount;
1179 l_appl_tbl(i).amount_due_remaining := l_amount_due_remaining;
1180 l_appl_tbl(i).amount_applied := l_amount_applied;
1181 l_appl_tbl(i).receivable_application_id := l_receivable_application_id;
1182
1183
1184
1185 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1186 LogMessage(G_PKG_NAME || '.' || l_api_name || '------------------------');
1187 LogMessage(G_PKG_NAME || '.' || l_api_name || ':Reversed record ' || i);
1188 LogMessage(G_PKG_NAME || '.' || l_api_name || ':promise_detail_id = ' || l_appl_tbl(i).promise_detail_id);
1189 LogMessage(G_PKG_NAME || '.' || l_api_name || ':promise_date = ' || l_appl_tbl(i).promise_date);
1190 LogMessage(G_PKG_NAME || '.' || l_api_name || ':status = ' || l_appl_tbl(i).status);
1191 LogMessage(G_PKG_NAME || '.' || l_api_name || ':promise_amount = ' || l_appl_tbl(i).promise_amount);
1192 LogMessage(G_PKG_NAME || '.' || l_api_name || ':amount_due_remaining = ' || l_appl_tbl(i).amount_due_remaining);
1193 LogMessage(G_PKG_NAME || '.' || l_api_name || ':amount_applied = ' || l_appl_tbl(i).amount_applied);
1194 LogMessage(G_PKG_NAME || '.' || l_api_name || ':receivable_application_id = ' || l_appl_tbl(i).receivable_application_id);
1195 END IF;
1196 END LOOP;
1197
1198 nCount := l_appl_tbl.count;
1199 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1200 LogMessage(G_PKG_NAME || '.' || l_api_name || ':Total count of found reversed applications = ' || nCount);
1201 END IF;
1202 if nCount > 0 then
1203
1204 FOR i in 1..nCount LOOP
1205 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1206 LogMessage(G_PKG_NAME || '.' || l_api_name || '------------------------');
1207 LogMessage(G_PKG_NAME || '.' || l_api_name || ':Reversing record ' || i);
1208 END IF;
1209
1210 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1211 LogMessage(G_PKG_NAME || '.' || l_api_name || ':Analizing what status to set for the promise...');
1212 LogMessage(G_PKG_NAME || '.' || l_api_name || ':current promise status ' || l_appl_tbl(i).status);
1213 END IF;
1214 if l_appl_tbl(i).status = 'FULFILLED' then -- it can effect only to FULFILLED records
1215 l_new_status := 'COLLECTABLE';
1216 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1217 LogMessage(G_PKG_NAME || '.' || l_api_name || ':the promise status after reversing will be ' || l_new_status);
1218 END IF;
1219 else
1220 l_new_status := null;
1221 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1222 LogMessage(G_PKG_NAME || '.' || l_api_name || ':will leave this status');
1223 END IF;
1224 end if;
1225
1226 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1227 LogMessage(G_PKG_NAME || '.' || l_api_name || ':updating records in IEX_prd_appl_xref with reversed_flag = Y...');
1228 END IF;
1229
1230 update IEX_prd_appl_xref
1231 set reversed_flag = 'Y',
1232 reversed_date = sysdate,
1233 last_update_date = sysdate,
1234 last_updated_by = G_USER_ID,
1235 request_id = G_REQUEST_ID
1236 where
1237 receivable_application_id = l_appl_tbl(i).receivable_application_id and
1238 promise_detail_id = l_appl_tbl(i).promise_detail_id;
1239
1240 if (sql%notfound) then
1241 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1242 LogMessage(G_PKG_NAME || '.' || l_api_name || ':update failed');
1243 END IF;
1244 -- Begin - Andre Araujo - 09/30/2004- Remove obsolete logging
1245 /*
1246 IEX_CONC_REQUEST_MSG_PKG.Log_Error(
1247 P_Concurrent_Request_ID => FND_GLOBAL.CONC_REQUEST_ID,
1248 P_Procedure_name => G_PKG_NAME || '.' || l_api_name,
1249 P_MESSAGE => 'Failed to update record in IEX_prd_appl_xref for promise_detail_id = ' || l_appl_tbl(i).promise_detail_id);
1250 */
1251 -- End - Andre Araujo - 09/30/2004- Remove obsolete logging
1252 else
1253 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1254 LogMessage(G_PKG_NAME || '.' || l_api_name || ':update successfull');
1255 END IF;
1256 end if;
1257
1258 l_new_remaining_amount := l_appl_tbl(i).amount_due_remaining + l_appl_tbl(i).amount_applied;
1259 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1260 LogMessage(G_PKG_NAME || '.' || l_api_name || ':updating record in IEX_PROMISE_DETAILS with:');
1261 LogMessage(G_PKG_NAME || '.' || l_api_name || ':amount_due_remaining = ' || l_new_remaining_amount);
1262 END IF;
1263
1264 if l_new_status is not null then
1265 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1266 LogMessage(G_PKG_NAME || '.' || l_api_name || ':status = ' || l_new_status);
1267 END IF;
1268 update IEX_PROMISE_DETAILS
1269 set status = l_new_status,
1270 amount_due_remaining = l_new_remaining_amount,
1271 last_update_date = sysdate,
1272 last_updated_by = G_USER_ID
1273 where promise_detail_id = l_appl_tbl(i).promise_detail_id;
1274 else
1275 update IEX_PROMISE_DETAILS
1276 set amount_due_remaining = l_new_remaining_amount,
1277 last_update_date = sysdate,
1278 last_updated_by = G_USER_ID
1279 where promise_detail_id = l_appl_tbl(i).promise_detail_id;
1280 end if;
1281
1282 if (sql%notfound) then
1283 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1284 LogMessage(G_PKG_NAME || '.' || l_api_name || ':update failed');
1285 END IF;
1286 -- Begin - Andre Araujo - 09/30/2004- Remove obsolete logging
1287 /*
1288 IEX_CONC_REQUEST_MSG_PKG.Log_Error(
1289 P_Concurrent_Request_ID => FND_GLOBAL.CONC_REQUEST_ID,
1290 P_Procedure_name => G_PKG_NAME || '.' || l_api_name,
1291 P_MESSAGE => 'Failed to update record in IEX_PROMISE_DETAILS for promise_detail_id = ' || l_appl_tbl(i).promise_detail_id);
1292 */
1293 -- End - Andre Araujo - 09/30/2004- Remove obsolete logging
1294 else
1295 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1296 LogMessage(G_PKG_NAME || '.' || l_api_name || ':update successfull');
1297 END IF;
1298 end if;
1299
1300 END LOOP;
1301 else
1302 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1303 LogMessage(G_PKG_NAME || '.' || l_api_name || ':no reversed applications found');
1304 END IF;
1305 end if;
1306
1307 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1308 LogMessage(G_PKG_NAME || '.' || l_api_name || ':End of body');
1309 END IF;
1310 -- END OF BODY OF API
1311
1312 -- Standard check of p_commit.
1313 IF FND_API.To_Boolean( p_commit ) THEN
1314 COMMIT WORK;
1315 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1316 LogMessage(G_PKG_NAME || '.' || l_api_name || ':Commited work');
1317 END IF;
1318 END IF;
1319
1320 x_return_status := l_return_status;
1321 -- Standard call to get message count and if count is 1, get message info
1322 FND_MSG_PUB.Count_And_Get(
1323 p_encoded => FND_API.G_FALSE,
1324 p_count => x_msg_count,
1325 p_data => x_msg_data);
1326
1327 EXCEPTION
1328
1329 WHEN FND_API.G_EXC_ERROR THEN
1330 ROLLBACK TO PROCESS_REVERSALS_PVT;
1331 x_return_status := FND_API.G_RET_STS_ERROR;
1332 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1333 -- Begin - Andre Araujo - 09/30/2004- Remove obsolete logging
1334 /*
1335 IEX_CONC_REQUEST_MSG_PKG.Log_Error(
1336 P_Concurrent_Request_ID => FND_GLOBAL.CONC_REQUEST_ID,
1337 P_Procedure_name => G_PKG_NAME || '.' || l_api_name,
1338 P_MESSAGE => 'Failed to reverse promise applications');
1339 */
1340 -- End - Andre Araujo - 09/30/2004- Remove obsolete logging
1341 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1342 LogMessage(G_PKG_NAME || '.' || l_api_name || ': In G_EXC_ERROR exception. Failed to reverse promise applications');
1343 END IF;
1344 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1345 ROLLBACK TO PROCESS_REVERSALS_PVT;
1346 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1347 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1348 -- Begin - Andre Araujo - 09/30/2004- Remove obsolete logging
1349 /*
1350 IEX_CONC_REQUEST_MSG_PKG.Log_Error(
1351 P_Concurrent_Request_ID => FND_GLOBAL.CONC_REQUEST_ID,
1352 P_Procedure_name => G_PKG_NAME || '.' || l_api_name,
1353 P_MESSAGE => 'Failed to reverse promise applications');
1354 */
1355 -- End - Andre Araujo - 09/30/2004- Remove obsolete logging
1356 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1357 LogMessage(G_PKG_NAME || '.' || l_api_name || ': In G_EXC_UNEXPECTED_ERROR exception. Failed to reverse promise applications');
1358 END IF;
1359 WHEN OTHERS THEN
1360 ROLLBACK TO PROCESS_REVERSALS_PVT;
1361 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1362 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)THEN
1363 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1364 END IF;
1365 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1366 /*
1367 IEX_CONC_REQUEST_MSG_PKG.Log_Error(
1368 P_Concurrent_Request_ID => FND_GLOBAL.CONC_REQUEST_ID,
1369 P_Procedure_name => G_PKG_NAME || '.' || l_api_name,
1370 P_MESSAGE => 'Failed to reverse promise applications');
1371 */
1372 -- End - Andre Araujo - 09/30/2004- Remove obsolete logging
1373 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1374 LogMessage(G_PKG_NAME || '.' || l_api_name || ': In OTHERS exception. Failed to reverse promise applications');
1375 END IF;
1376 END;
1377
1378 /**********************
1379 This procedure processes promises
1380 ***********************/
1381 PROCEDURE PROCESS_PROMISES(
1382 P_API_VERSION IN NUMBER,
1383 P_INIT_MSG_LIST IN VARCHAR2,
1384 P_COMMIT IN VARCHAR2,
1385 P_VALIDATION_LEVEL IN NUMBER,
1386 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1387 X_MSG_COUNT OUT NOCOPY NUMBER,
1388 X_MSG_DATA OUT NOCOPY VARCHAR2,
1389 P_TYPE IN VARCHAR2)
1390 IS
1391 l_api_name CONSTANT VARCHAR2(30) := 'PROCESS_PROMISES';
1392 l_api_version CONSTANT NUMBER := 1.0;
1393 l_return_status VARCHAR2(1);
1394 l_msg_count NUMBER;
1395 l_msg_data VARCHAR2(32767);
1396 vSQL varchar2(10000);
1397 Type refCur is Ref Cursor;
1398 promise_cur refCur;
1399 y NUMBER;
1400 nCount NUMBER;
1401 l_pro_tbl IEX_PROMISES_BATCH_PUB.PROMISES_TBL;
1402
1403 l_PROMISE_DETAIL_ID NUMBER;
1404 l_CREATION_DATE DATE;
1405 l_PROMISE_DATE DATE;
1406 l_STATUS VARCHAR2(30);
1407 l_STATE VARCHAR2(30);
1408 l_PROMISE_AMOUNT NUMBER;
1409 l_AMOUNT_DUE_REMAINING NUMBER;
1410 l_DELINQUENCY_ID NUMBER;
1411 l_PAYMENT_SCHEDULE_ID NUMBER;
1412 l_CUST_ACCOUNT_ID NUMBER;
1413 l_CONTRACT_ID NUMBER;
1414
1415 BEGIN
1416 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1417 LogMessage(G_PKG_NAME || '.' || l_api_name || ':Start');
1418 END IF;
1419
1420
1421 -- Standard start of API savepoint
1422 SAVEPOINT PROCESS_PROMISES_PVT;
1423
1424 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1425 LogMessage(G_PKG_NAME || '.' || l_api_name || ':Savepoint is established');
1426 END IF;
1427 -- Standard call to check for call compatibility
1428 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1429 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1430 END IF;
1431
1432 -- Initialize message list if p_init_msg_list is set to TRUE
1433 IF FND_API.To_Boolean(p_init_msg_list) THEN
1434 FND_MSG_PUB.initialize;
1435 END IF;
1436
1437 -- Initialize API return status to success
1438 l_return_status := FND_API.G_RET_STS_SUCCESS;
1439
1440 -- START OF BODY OF API
1441 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1442 LogMessage(G_PKG_NAME || '.' || l_api_name || ':Start of body');
1443 END IF;
1444
1445 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1446 LogMessage(G_PKG_NAME || '.' || l_api_name || ':searching for all available valid promises...');
1447 END IF;
1448
1449 if P_TYPE = 'INV' then -- processing all promises on invoices
1450
1451 vSQL := 'SELECT ' ||
1452 'PRD.promise_detail_id, ' ||
1453 'PRD.creation_date, ' ||
1454 'nvl(PRD.broken_on_date, PRD.promise_date), ' ||
1455 'PRD.status, ' ||
1456 'PRD.state, ' ||
1457 'PRD.promise_amount, ' ||
1458 'PRD.amount_due_remaining, ' ||
1459 'PRD.delinquency_id, ' ||
1460 'DEL.payment_schedule_id ' ||
1461 'FROM ' ||
1462 'iex_promise_details prd, ' ||
1463 'iex_delinquencies del ' ||
1464 'WHERE ' ||
1465 'prd.delinquency_id is not null and ' ||
1466 'del.delinquency_id = prd.delinquency_id and ' ||
1467 'prd.status = ''COLLECTABLE'' and ' ||
1468 'prd.org_id = del.org_id ' || --Added for bug 7237026 barathsr 31-Dec-2008
1469 'order by PRD.promise_date';
1470
1471 open promise_cur for vSQL;
1472 y := 0;
1473 LOOP
1474 fetch promise_cur into
1475 l_PROMISE_DETAIL_ID,
1476 l_CREATION_DATE,
1477 l_PROMISE_DATE,
1478 l_STATUS,
1479 l_STATE,
1480 l_PROMISE_AMOUNT,
1481 l_AMOUNT_DUE_REMAINING,
1482 l_DELINQUENCY_ID,
1483 l_PAYMENT_SCHEDULE_ID;
1484 exit when promise_cur%NOTFOUND;
1485
1486 y := y+1;
1487 l_pro_tbl(y).PROMISE_DETAIL_ID := l_PROMISE_DETAIL_ID;
1488 l_pro_tbl(y).CREATION_DATE := l_CREATION_DATE;
1489 l_pro_tbl(y).PROMISE_DATE := l_PROMISE_DATE;
1490 l_pro_tbl(y).STATUS := l_STATUS;
1491 l_pro_tbl(y).STATE := l_STATE;
1492 l_pro_tbl(y).PROMISE_AMOUNT := l_PROMISE_AMOUNT;
1493 l_pro_tbl(y).AMOUNT_DUE_REMAINING := l_AMOUNT_DUE_REMAINING;
1494 l_pro_tbl(y).DELINQUENCY_ID := l_DELINQUENCY_ID;
1495 l_pro_tbl(y).PAYMENT_SCHEDULE_ID := l_PAYMENT_SCHEDULE_ID;
1496
1497 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1498 LogMessage(G_PKG_NAME || '.' || l_api_name || '------------------------');
1499 LogMessage(G_PKG_NAME || '.' || l_api_name || ':found promise ' || y);
1500 LogMessage(G_PKG_NAME || '.' || l_api_name || ':PROMISE_DETAIL_ID = ' || l_pro_tbl(y).PROMISE_DETAIL_ID);
1501 END IF;
1502
1503 END LOOP;
1504
1505 nCount := l_pro_tbl.count;
1506 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1507 LogMessage(G_PKG_NAME || '.' || l_api_name || ':Total count of found promises = ' || nCount);
1508 END IF;
1509
1510 if nCount > 0 then
1511 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1512 LogMessage(G_PKG_NAME || '.' || l_api_name || ':Doing FIFO promise applications...');
1513 END IF;
1514 APPLY_PROMISES_FIFO(
1515 P_API_VERSION => 1.0,
1516 P_INIT_MSG_LIST => FND_API.G_TRUE,
1517 P_COMMIT => FND_API.G_TRUE,
1518 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
1519 X_RETURN_STATUS => l_return_status,
1520 X_MSG_COUNT => l_msg_count,
1521 X_MSG_DATA => l_msg_data,
1522 P_PROMISES_TBL => l_pro_tbl,
1523 P_TYPE => 'INV');
1524 else
1525 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1526 LogMessage(G_PKG_NAME || '.' || l_api_name || ':no promises found - do not call FIFO');
1527 END IF;
1528 end if;
1529
1530 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1531 LogMessage(G_PKG_NAME || '.' || l_api_name || ':Updating all COLLECTABLE and PENDING promises for delinquencies that have status CURRENT to status FULFILLED ...');
1532 END IF;
1533
1534 UPDATE iex_promise_details
1535 SET STATUS = 'FULFILLED',
1536 last_update_date = sysdate,
1537 last_updated_by = G_USER_ID
1538 WHERE
1539 promise_detail_id in
1540 (select prd.promise_detail_id
1541 from iex_promise_details prd, iex_delinquencies del, ar_payment_schedules aps --added for Bug 6446848 08-Dec-2008 barathsr
1542 where prd.delinquency_id is not null and
1543 prd.delinquency_id = del.delinquency_id and
1544 prd.org_id = del.org_id and --Added for bug 7237026 barathsr 31-Dec-2008
1545 del.payment_schedule_id=aps.payment_schedule_id and --added for Bug 6446848 08-Dec-2008 barathsr
1546 prd.status in ('COLLECTABLE', 'PENDING') and
1547 del.status = 'CURRENT' and
1548 aps.amount_due_remaining = 0);--added for Bug 6446848 08-Dec-2008 barathsr
1549
1550
1551 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1552 LogMessage(G_PKG_NAME || '.' || l_api_name || ': ' || sql%rowcount || ' rows is updated');
1553 END IF;
1554
1555 elsif P_TYPE = 'ACC' then -- processing all promises on account
1556
1557 vSQL := 'SELECT ' ||
1558 'PRD.promise_detail_id pro, ' ||
1559 'PRD.creation_date, ' ||
1560 'nvl(PRD.broken_on_date, PRD.promise_date), ' ||
1561 'PRD.status, ' ||
1562 'PRD.state, ' ||
1563 'PRD.promise_amount, ' ||
1564 'PRD.amount_due_remaining, ' ||
1565 'PRD.cust_account_id ' ||
1566 'FROM ' ||
1567 'iex_promise_details prd,' ||
1568 'AR_SYSTEM_PARAMETERS asp ' || --Added for bug 7237026 barathsr 31-Dec-2008
1569 'WHERE ' ||
1570 'prd.delinquency_id is null and ' ||
1571 'prd.CNSLD_INVOICE_ID is null and ' ||
1572 'prd.CONTRACT_ID is null and ' ||
1573 'prd.status = ''COLLECTABLE'' and ' ||
1574 'prd.org_id = asp.org_id ' || --Added for bug 7237026 barathsr 31-Dec-2008
1575 'order by PRD.promise_date';
1576
1577 open promise_cur for vSQL;
1578 y := 0;
1579 LOOP
1580 fetch promise_cur into
1581 l_PROMISE_DETAIL_ID,
1582 l_CREATION_DATE,
1583 l_PROMISE_DATE,
1584 l_STATUS,
1585 l_STATE,
1586 l_PROMISE_AMOUNT,
1587 l_AMOUNT_DUE_REMAINING,
1588 l_CUST_ACCOUNT_ID;
1589 exit when promise_cur%NOTFOUND;
1590
1591 y := y+1;
1592 l_pro_tbl(y).PROMISE_DETAIL_ID := l_PROMISE_DETAIL_ID;
1593 l_pro_tbl(y).CREATION_DATE := l_CREATION_DATE;
1594 l_pro_tbl(y).PROMISE_DATE := l_PROMISE_DATE;
1595 l_pro_tbl(y).STATUS := l_STATUS;
1596 l_pro_tbl(y).STATE := l_STATE;
1597 l_pro_tbl(y).PROMISE_AMOUNT := l_PROMISE_AMOUNT;
1598 l_pro_tbl(y).AMOUNT_DUE_REMAINING := l_AMOUNT_DUE_REMAINING;
1599 l_pro_tbl(y).CUST_ACCOUNT_ID := l_CUST_ACCOUNT_ID;
1600
1601 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1602 LogMessage(G_PKG_NAME || '.' || l_api_name || '------------------------');
1603 LogMessage(G_PKG_NAME || '.' || l_api_name || ':found promise ' || y);
1604 LogMessage(G_PKG_NAME || '.' || l_api_name || ':PROMISE_DETAIL_ID = ' || l_pro_tbl(y).PROMISE_DETAIL_ID);
1605 END IF;
1606
1607 END LOOP;
1608
1609 nCount := l_pro_tbl.count;
1610 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1611 LogMessage(G_PKG_NAME || '.' || l_api_name || ':Total count of found promises = ' || nCount);
1612 END IF;
1613
1614 if nCount > 0 then
1615 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1616 LogMessage(G_PKG_NAME || '.' || l_api_name || ':Doing FIFO promise applications...');
1617 END IF;
1618 APPLY_PROMISES_FIFO(
1619 P_API_VERSION => 1.0,
1620 P_INIT_MSG_LIST => FND_API.G_TRUE,
1621 P_COMMIT => FND_API.G_TRUE,
1622 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
1623 X_RETURN_STATUS => l_return_status,
1624 X_MSG_COUNT => l_msg_count,
1625 X_MSG_DATA => l_msg_data,
1626 P_PROMISES_TBL => l_pro_tbl,
1627 P_TYPE => 'ACC');
1628 else
1629 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1630 LogMessage(G_PKG_NAME || '.' || l_api_name || ':no promises found - do not call FIFO');
1631 END IF;
1632 end if;
1633
1634 elsif P_TYPE = 'CNTR' then -- processing all promises on contracts
1635
1636 vSQL := 'SELECT ' ||
1637 'PRD.promise_detail_id, ' ||
1638 'PRD.creation_date, ' ||
1639 'nvl(PRD.broken_on_date, PRD.promise_date), ' ||
1640 'PRD.status, ' ||
1641 'PRD.state, ' ||
1642 'PRD.promise_amount, ' ||
1643 'PRD.amount_due_remaining, ' ||
1644 'PRD.contract_id ' ||
1645 'FROM ' ||
1646 'iex_promise_details prd, ' ||
1647 'AR_SYSTEM_PARAMETERS asp ' || --Added for bug 7237026 barathsr 31-Dec-2008
1648 'WHERE ' ||
1649 'prd.contract_id is not null and ' ||
1650 'prd.status = ''COLLECTABLE'' and ' ||
1651 'prd.org_id = asp.org_id '||--Added for bug 7237026 barathsr 31-Dec-2008
1652 'order by PRD.promise_date';
1653
1654 open promise_cur for vSQL;
1655 y := 0;
1656 LOOP
1657 fetch promise_cur into
1658 l_PROMISE_DETAIL_ID,
1659 l_CREATION_DATE,
1660 l_PROMISE_DATE,
1661 l_STATUS,
1662 l_STATE,
1663 l_PROMISE_AMOUNT,
1664 l_AMOUNT_DUE_REMAINING,
1665 l_CONTRACT_ID;
1666 exit when promise_cur%NOTFOUND;
1667
1668 y := y+1;
1669 l_pro_tbl(y).PROMISE_DETAIL_ID := l_PROMISE_DETAIL_ID;
1670 l_pro_tbl(y).CREATION_DATE := l_CREATION_DATE;
1671 l_pro_tbl(y).PROMISE_DATE := l_PROMISE_DATE;
1672 l_pro_tbl(y).STATUS := l_STATUS;
1673 l_pro_tbl(y).STATE := l_STATE;
1674 l_pro_tbl(y).PROMISE_AMOUNT := l_PROMISE_AMOUNT;
1675 l_pro_tbl(y).AMOUNT_DUE_REMAINING := l_AMOUNT_DUE_REMAINING;
1676 l_pro_tbl(y).CONTRACT_ID := l_CONTRACT_ID;
1677
1678
1679 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1680 LogMessage(G_PKG_NAME || '.' || l_api_name || '------------------------');
1681 LogMessage(G_PKG_NAME || '.' || l_api_name || ':found promise ' || y);
1682 LogMessage(G_PKG_NAME || '.' || l_api_name || ':PROMISE_DETAIL_ID = ' || l_pro_tbl(y).PROMISE_DETAIL_ID);
1683 END IF;
1684
1685 END LOOP;
1686
1687 nCount := l_pro_tbl.count;
1688 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1689 LogMessage(G_PKG_NAME || '.' || l_api_name || ':Total count of found promises = ' || nCount);
1690 END IF;
1691
1692 if nCount > 0 then
1693 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1694 LogMessage(G_PKG_NAME || '.' || l_api_name || ':Doing FIFO promise applications...');
1695 END IF;
1696 APPLY_PROMISES_FIFO(
1697 P_API_VERSION => 1.0,
1698 P_INIT_MSG_LIST => FND_API.G_TRUE,
1699 P_COMMIT => FND_API.G_TRUE,
1700 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
1701 X_RETURN_STATUS => l_return_status,
1702 X_MSG_COUNT => l_msg_count,
1703 X_MSG_DATA => l_msg_data,
1704 P_PROMISES_TBL => l_pro_tbl,
1705 P_TYPE => 'CNTR');
1706 else
1707 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1708 LogMessage(G_PKG_NAME || '.' || l_api_name || ':no promises found - do not call FIFO');
1709 END IF;
1710 end if;
1711
1712 end if;
1713
1714 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1715 LogMessage(G_PKG_NAME || '.' || l_api_name || ':End of body');
1716 END IF;
1717 -- END OF BODY OF API
1718
1719 -- Standard check of p_commit.
1720 IF FND_API.To_Boolean( p_commit ) THEN
1721 COMMIT WORK;
1722 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1723 LogMessage(G_PKG_NAME || '.' || l_api_name || ':Commited work');
1724 END IF;
1725 END IF;
1726
1727 x_return_status := l_return_status;
1728 -- Standard call to get message count and if count is 1, get message info
1729 FND_MSG_PUB.Count_And_Get(
1730 p_encoded => FND_API.G_FALSE,
1731 p_count => x_msg_count,
1732 p_data => x_msg_data);
1733
1734 EXCEPTION
1735 WHEN FND_API.G_EXC_ERROR THEN
1736 ROLLBACK TO PROCESS_PROMISES_PVT;
1737 x_return_status := FND_API.G_RET_STS_ERROR;
1738 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1739 -- Begin - Andre Araujo - 09/30/2004- Remove obsolete logging
1740 /*
1741 IEX_CONC_REQUEST_MSG_PKG.Log_Error(
1742 P_Concurrent_Request_ID => FND_GLOBAL.CONC_REQUEST_ID,
1743 P_Procedure_name => G_PKG_NAME || '.' || l_api_name,
1744 P_MESSAGE => 'Failed to process promises');
1745 */
1746 -- End - Andre Araujo - 09/30/2004- Remove obsolete logging
1747 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1748 LogMessage(G_PKG_NAME || '.' || l_api_name || ': In G_EXC_ERROR exception. Failed to process promises');
1749 END IF;
1750 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1751 ROLLBACK TO PROCESS_PROMISES_PVT;
1752 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1753 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1754 -- Begin - Andre Araujo - 09/30/2004- Remove obsolete logging
1755 /*
1756 IEX_CONC_REQUEST_MSG_PKG.Log_Error(
1757 P_Concurrent_Request_ID => FND_GLOBAL.CONC_REQUEST_ID,
1758 P_Procedure_name => G_PKG_NAME || '.' || l_api_name,
1759 P_MESSAGE => 'Failed to process promises');
1760 */
1761 -- End - Andre Araujo - 09/30/2004- Remove obsolete logging
1762 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1763 LogMessage(G_PKG_NAME || '.' || l_api_name || ': In G_EXC_UNEXPECTED_ERROR exception. Failed to process promises');
1764 END IF;
1765 WHEN OTHERS THEN
1766 ROLLBACK TO PROCESS_PROMISES_PVT;
1767 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1768 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)THEN
1769 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1770 END IF;
1771 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1772 -- Begin - Andre Araujo - 09/30/2004- Remove obsolete logging
1773 /*
1774 IEX_CONC_REQUEST_MSG_PKG.Log_Error(
1775 P_Concurrent_Request_ID => FND_GLOBAL.CONC_REQUEST_ID,
1776 P_Procedure_name => G_PKG_NAME || '.' || l_api_name,
1777 P_MESSAGE => 'Failed to process promises');
1778 */
1779 -- End - Andre Araujo - 09/30/2004- Remove obsolete logging
1780 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1781 LogMessage(G_PKG_NAME || '.' || l_api_name || ': In OTHERS exception. Failed to process promises');
1782 END IF;
1783 END;
1784
1785 /**********************
1786 This procedure implements FIFO application method for promises
1787 ***********************/
1788 PROCEDURE APPLY_PROMISES_FIFO(
1789 P_API_VERSION IN NUMBER,
1790 P_INIT_MSG_LIST IN VARCHAR2,
1791 P_COMMIT IN VARCHAR2,
1792 P_VALIDATION_LEVEL IN NUMBER,
1793 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1794 X_MSG_COUNT OUT NOCOPY NUMBER,
1795 X_MSG_DATA OUT NOCOPY VARCHAR2,
1796 P_PROMISES_TBL IN OUT NOCOPY IEX_PROMISES_BATCH_PUB.PROMISES_TBL,
1797 P_TYPE IN VARCHAR2)
1798 IS
1799 l_api_name CONSTANT VARCHAR2(30) := 'APPLY_PROMISES_FIFO';
1800 l_api_version CONSTANT NUMBER := 1.0;
1801 l_return_status VARCHAR2(1);
1802 l_msg_count NUMBER;
1803 l_msg_data VARCHAR2(32767);
1804 vSQL varchar2(10000);
1805 -- start bug 3635087 gnramasa 10/07/07
1806 vSQL_pay_only varchar2(10000);
1807 vSQL_pay_adj varchar2(10000);
1808 l_adjustment_count NUMBER := 0;
1809 l_adjustment_id NUMBER;
1810 l_adjusted_amount NUMBER;
1811 l_adjusted_date DATE;
1812 l_adj_remaining_amount NUMBER;
1813 -- End bug 3635087 gnramasa 10/07/07
1814 Type refCur is Ref Cursor;
1815 appl_cur refCur;
1816 l_appl_tbl IEX_PROMISES_BATCH_PUB.APPLS_TBL;
1817 i NUMBER;
1818 y NUMBER;
1819 x NUMBER;
1820 nCount NUMBER;
1821 nCount1 NUMBER;
1822 l_receivable_application_id NUMBER;
1823 l_ar_applied_amount NUMBER;
1824 l_ar_remaining_amount NUMBER;
1825 l_ar_apply_date DATE;
1826 l_callback_date DATE;
1827 l_status VARCHAR2(30);
1828 l_state VARCHAR2(30);
1829 l_applied_appl_count NUMBER;
1830
1831
1832 BEGIN
1833 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1834 LogMessage(G_PKG_NAME || '.' || l_api_name || ':Start');
1835 END IF;
1836
1837 -- Standard start of API savepoint
1838 SAVEPOINT APPLY_PROMISES_FIFO_PVT;
1839
1840 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1841 LogMessage(G_PKG_NAME || '.' || l_api_name || ':Savepoint is established');
1842 END IF;
1843 -- Standard call to check for call compatibility
1844 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1845 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1846 END IF;
1847
1848 -- Initialize message list if p_init_msg_list is set to TRUE
1849 IF FND_API.To_Boolean(p_init_msg_list) THEN
1850 FND_MSG_PUB.initialize;
1851 END IF;
1852
1853 -- Initialize API return status to success
1854 l_return_status := FND_API.G_RET_STS_SUCCESS;
1855
1856 -- START OF BODY OF API
1857 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1858 LogMessage(G_PKG_NAME || '.' || l_api_name || ':Start of body');
1859 END IF;
1860
1861 nCount := P_PROMISES_TBL.count;
1862 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1863 LogMessage(G_PKG_NAME || '.' || l_api_name || ':Total count of passed promises = ' || nCount);
1864 END IF;
1865
1866 FOR i in 1..nCount LOOP
1867 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1868 LogMessage(G_PKG_NAME || '.' || l_api_name || ':---------------------------');
1869 LogMessage(G_PKG_NAME || '.' || l_api_name || ':Promise ' || i || ' details:');
1870 LogMessage(G_PKG_NAME || '.' || l_api_name || ':PROMISE_DETAIL_ID = ' || P_PROMISES_TBL(i).PROMISE_DETAIL_ID);
1871 LogMessage(G_PKG_NAME || '.' || l_api_name || ':CREATION_DATE = ' || P_PROMISES_TBL(i).CREATION_DATE);
1872 LogMessage(G_PKG_NAME || '.' || l_api_name || ':nvl(BROKEN_ON_DATE, PROMISE_DATE) = ' || P_PROMISES_TBL(i).PROMISE_DATE);
1873 LogMessage(G_PKG_NAME || '.' || l_api_name || ':PROMISE_AMOUNT = ' || P_PROMISES_TBL(i).PROMISE_AMOUNT);
1874 LogMessage(G_PKG_NAME || '.' || l_api_name || ':AMOUNT_DUE_REMAINING = ' || P_PROMISES_TBL(i).AMOUNT_DUE_REMAINING);
1875 LogMessage(G_PKG_NAME || '.' || l_api_name || ':STATUS = ' || P_PROMISES_TBL(i).STATUS);
1876 LogMessage(G_PKG_NAME || '.' || l_api_name || ':STATE = ' || P_PROMISES_TBL(i).STATE);
1877 END IF;
1878
1879 if P_TYPE = 'INV' then
1880 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1881 LogMessage(G_PKG_NAME || '.' || l_api_name || ':DELINQUENCY_ID = ' || P_PROMISES_TBL(i).DELINQUENCY_ID);
1882 LogMessage(G_PKG_NAME || '.' || l_api_name || ':PAYMENT_SCHEDULE_ID = ' || P_PROMISES_TBL(i).PAYMENT_SCHEDULE_ID);
1883 END IF;
1884 -- start bug 3635087 gnramasa 10/07/07
1885 vSQL_pay_only := 'select ' ||
1886 'raa.receivable_application_id, ' ||
1887 'raa.amount_applied, ' ||
1888 'raa.apply_date, ' ||
1889 'raa.amount_applied - nvl(sum(pax.amount_applied), 0), ' ||
1890 'NULL, ' ||
1891 'NULL, ' ||
1892 'NULL, ' ||
1893 'NULL ' ||
1894 'from ' ||
1895 'AR_RECEIVABLE_APPLICATIONS raa, ' ||
1896 'IEX_prd_appl_xref pax ' ||
1897 'where ' ||
1898 '(trunc(raa.apply_date) between trunc(:PROMISE_CR_DATE) and trunc(sysdate)) and ' ||
1899 'raa.status = ''APP'' and ' ||
1900 'raa.amount_applied > 0 and ' ||
1901 'raa.reversal_gl_date is null and ' ||
1902 'raa.applied_payment_schedule_id = :PSA_ID and ' ||
1903 'raa.receivable_application_id = pax.receivable_application_id(+) and ' ||
1904 'raa.receivable_application_id not in ' ||
1905 '(select receivable_application_id ' ||
1906 'from IEX_prd_appl_xref where promise_detail_id = :PROMISE_ID and ' ||
1907 'REVERSED_FLAG is null and REVERSED_DATE is null and receivable_application_id is NOT NULL) ' ||
1908 'group by raa.receivable_application_id, raa.amount_applied, raa.apply_date ' ||
1909 'order by raa.receivable_application_id';
1910
1911 vSQL_pay_adj := 'SELECT ' ||
1912 'raa.receivable_application_id, ' ||
1913 'raa.amount_applied, ' ||
1914 'raa.apply_date, ' ||
1915 'raa.amount_applied -nvl(SUM(pax.amount_applied), 0), ' ||
1916 'NULL, ' ||
1917 'NULL, ' ||
1918 'NULL, ' ||
1919 'NULL ' ||
1920 'FROM ar_receivable_applications raa, ' ||
1921 'iex_prd_appl_xref pax ' ||
1922 'WHERE(TRUNC(raa.apply_date) BETWEEN TRUNC(:promise_cr_date) ' ||
1923 'AND TRUNC(sysdate)) ' ||
1924 'AND raa.status = ''APP'' ' ||
1925 'AND raa.amount_applied > 0 ' ||
1926 'AND raa.reversal_gl_date IS NULL ' ||
1927 'AND raa.applied_payment_schedule_id = :psa_id ' ||
1928 'AND raa.receivable_application_id = pax.receivable_application_id(+) ' ||
1929 'AND raa.receivable_application_id NOT IN ' ||
1930 '(SELECT receivable_application_id ' ||
1931 'FROM iex_prd_appl_xref ' ||
1932 'WHERE promise_detail_id = :promise_id ' ||
1933 'AND reversed_flag IS NULL ' ||
1934 'AND reversed_date IS NULL AND receivable_application_id is NOT NULL) ' ||
1935 'GROUP BY raa.receivable_application_id, ' ||
1936 'raa.amount_applied, ' ||
1937 'raa.apply_date ' ||
1938 'UNION ALL ' ||
1939 'SELECT NULL, ' ||
1940 'NULL, ' ||
1941 'NULL, ' ||
1942 'NULL, ' ||
1943 'ara.adjustment_id, ' ||
1944 '-ara.amount, ' ||
1945 'ara.apply_date, ' ||
1946 '-ara.amount -nvl(SUM(pax.amount_applied), 0) ' ||
1947 'FROM ar_adjustments ara, ' ||
1948 'iex_prd_appl_xref pax ' ||
1949 'WHERE(TRUNC(ara.apply_date) BETWEEN TRUNC(:promise_cr_date) ' ||
1950 'AND TRUNC(sysdate)) ' ||
1951 'AND ara.status = ''A'' ' ||
1952 'AND ara.amount < 0 ' ||
1953 'AND ara.payment_schedule_id = :psa_id ' ||
1954 'AND ara.adjustment_id = pax.adjustment_id(+) ' ||
1955 'AND ara.adjustment_id NOT IN ' ||
1956 '(SELECT adjustment_id ' ||
1957 'FROM iex_prd_appl_xref ' ||
1958 'WHERE promise_detail_id = :promise_id AND adjustment_id is NOT NULL)' ||
1959 'GROUP BY ara.adjustment_id, ' ||
1960 'ara.amount, ' ||
1961 'ara.apply_date';
1962
1963 SELECT count(adjustment_id)
1964 into l_adjustment_count
1965 FROM ar_adjustments
1966 WHERE PAYMENT_SCHEDULE_ID = P_PROMISES_TBL(i).PAYMENT_SCHEDULE_ID;
1967
1968 IF l_adjustment_count = 0 THEN
1969 vSQL := vSQL_pay_only;
1970 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1971 LogMessage(G_PKG_NAME || '.' || l_api_name || 'No adjustment exist for this invoice, vSQL := vSQL_pay_only' );
1972 END IF;
1973 ELSE
1974 vSQL := vSQL_pay_adj;
1975 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1976 LogMessage(G_PKG_NAME || '.' || l_api_name || 'Adjustments exist for this invoice, vSQL := vSQL_pay_adj' );
1977 END IF;
1978 END IF;
1979
1980 elsif P_TYPE = 'ACC' then
1981 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1982 LogMessage(G_PKG_NAME || '.' || l_api_name || ':CUST_ACCOUNT_ID = ' || P_PROMISES_TBL(i).CUST_ACCOUNT_ID);
1983 END IF;
1984 vSQL := 'select ' ||
1985 'raa.receivable_application_id, ' ||
1986 'raa.amount_applied, ' ||
1987 'raa.apply_date, ' ||
1988 'raa.amount_applied - nvl(sum(pax.amount_applied), 0), ' ||
1989 'NULL, ' ||
1990 'NULL, ' ||
1991 'NULL, ' ||
1992 'NULL ' ||
1993 'from ' ||
1994 'AR_RECEIVABLE_APPLICATIONS raa, ' ||
1995 'IEX_prd_appl_xref pax, ' ||
1996 'AR_PAYMENT_SCHEDULES psa ' ||
1997 'where ' ||
1998 '(trunc(raa.apply_date) between trunc(:PROMISE_CR_DATE) and trunc(sysdate)) and ' ||
1999 'raa.status = ''ACC'' and ' ||
2000 'raa.amount_applied > 0 and ' ||
2001 'raa.reversal_gl_date is null and ' ||
2002 'raa.payment_schedule_id = psa.payment_schedule_id and ' ||
2003 'psa.customer_id = :CUSTOMER_ID and ' ||
2004 'raa.receivable_application_id = pax.receivable_application_id(+) and ' ||
2005 'raa.receivable_application_id not in ' ||
2006 '(select receivable_application_id ' ||
2007 'from IEX_prd_appl_xref where promise_detail_id = :PROMISE_ID and ' ||
2008 'REVERSED_FLAG is null and REVERSED_DATE is null and receivable_application_id is NOT NULL) ' ||
2009 'group by raa.receivable_application_id, raa.amount_applied, raa.apply_date ' ||
2010 'order by raa.receivable_application_id';
2011
2012 elsif P_TYPE = 'CNTR' then
2013 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2014 LogMessage(G_PKG_NAME || '.' || l_api_name || ':CONTRACT_ID = ' || P_PROMISES_TBL(i).CONTRACT_ID);
2015 END IF;
2016 /* replaced the statement just below to fix a perf bug 4930383
2017 vSQL := 'select ' ||
2018 'raa.receivable_application_id, ' ||
2019 'raa.amount_applied, ' ||
2020 'raa.apply_date, ' ||
2021 'raa.amount_applied - nvl(sum(pax.amount_applied), 0) ' ||
2022 'from ' ||
2023 'IEX_OKL_PAYMENTS_V raa, ' ||
2024 'IEX_prd_appl_xref pax ' ||
2025 'where ' ||
2026 '(trunc(raa.apply_date) between trunc(:PROMISE_CR_DATE) and trunc(sysdate)) and ' ||
2027 'raa.amount_applied > 0 and ' ||
2028 'raa.reversal_gl_date is null and ' ||
2029 'raa.contract_id = :CONTRACT_ID and ' ||
2030 'raa.receivable_application_id = pax.receivable_application_id(+) and ' ||
2031 'raa.receivable_application_id not in ' ||
2032 '(select receivable_application_id ' ||
2033 'from IEX_prd_appl_xref where promise_detail_id = :PROMISE_ID and ' ||
2034 'REVERSED_FLAG is null and REVERSED_DATE is null) ' ||
2035 'group by raa.receivable_application_id, raa.amount_applied, raa.apply_date ' ||
2036 'order by raa.receivable_application_id';
2037 */
2038
2039 vSQL := ' Select '||
2040 ' ARAPP.RECEIVABLE_APPLICATION_ID, '||
2041 ' ARAPP.AMOUNT_APPLIED, '||
2042 ' ARAPP.APPLY_DATE, '||
2043 ' ARAPP.AMOUNT_APPLIED - nvl(sum(PAX.amount_applied), 0), '||
2044 ' NULL, ' ||
2045 ' NULL, ' ||
2046 ' NULL, ' ||
2047 ' NULL ' ||
2048 ' From ' ||
2049 ' OKL_CNSLD_AR_STRMS_B CNSLD, '||
2050 ' AR_RECEIVABLE_APPLICATIONS ARAPP, '||
2051 ' AR_PAYMENT_SCHEDULES PMTSCH, '||
2052 ' IEX_prd_appl_xref PAX '||
2053 ' Where '||
2054 ' CNSLD.khr_id = :CONTRACT_ID '||
2055 ' and CNSLD.receivables_invoice_id = PMTSCH.customer_trx_id '||
2056 ' and PMTSCH.class = ''INV'' '||
2057 ' and PMTSCH.payment_schedule_id = ARAPP.applied_payment_schedule_id '||
2058 ' and (trunc(ARAPP.apply_date) between trunc(:PROMISE_CR_DATE) and trunc(sysdate)) '||
2059 ' and ARAPP.amount_applied > 0 '||
2060 ' and ARAPP.reversal_gl_date is null '||
2061 ' and ARAPP.receivable_application_id = PAX.receivable_application_id(+) '||
2062 ' and ARAPP.receivable_application_id not in (select receivable_application_id from IEX_prd_appl_xref ' ||
2063 ' where promise_detail_id = :PROMISE_ID and REVERSED_FLAG is null and REVERSED_DATE is null ' ||
2064 ' and receivable_application_id is NOT NULL) '||
2065 ' group by ARAPP.receivable_application_id, ARAPP.amount_applied, ARAPP.apply_date '||
2066 ' order by ARAPP.receivable_application_id ';
2067
2068 end if;
2069
2070 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2071 LogMessage(G_PKG_NAME || '.' || l_api_name || ':Searching for applications to apply to the promise ' || P_PROMISES_TBL(i).PROMISE_DETAIL_ID);
2072 END IF;
2073
2074 if P_TYPE = 'INV' then
2075 IF l_adjustment_count = 0 THEN
2076 open appl_cur for vSQL using
2077 P_PROMISES_TBL(i).CREATION_DATE,
2078 P_PROMISES_TBL(i).PAYMENT_SCHEDULE_ID,
2079 P_PROMISES_TBL(i).PROMISE_DETAIL_ID;
2080 ELSE
2081 open appl_cur for vSQL using
2082 P_PROMISES_TBL(i).CREATION_DATE,
2083 P_PROMISES_TBL(i).PAYMENT_SCHEDULE_ID,
2084 P_PROMISES_TBL(i).PROMISE_DETAIL_ID,
2085 P_PROMISES_TBL(i).CREATION_DATE,
2086 P_PROMISES_TBL(i).PAYMENT_SCHEDULE_ID,
2087 P_PROMISES_TBL(i).PROMISE_DETAIL_ID;
2088 END IF;
2089
2090 elsif P_TYPE = 'ACC' then
2091 open appl_cur for vSQL using
2092 P_PROMISES_TBL(i).CREATION_DATE,
2093 P_PROMISES_TBL(i).CUST_ACCOUNT_ID,
2094 P_PROMISES_TBL(i).PROMISE_DETAIL_ID;
2095 elsif P_TYPE = 'CNTR' then
2096 open appl_cur for vSQL using
2097 P_PROMISES_TBL(i).CREATION_DATE,
2098 P_PROMISES_TBL(i).CONTRACT_ID,
2099 P_PROMISES_TBL(i).PROMISE_DETAIL_ID;
2100 end if;
2101
2102 y := 0;
2103 l_appl_tbl.delete;
2104 LOOP
2105
2106 fetch appl_cur into
2107 l_receivable_application_id,
2108 l_ar_applied_amount,
2109 l_ar_apply_date,
2110 l_ar_remaining_amount,
2111 l_adjustment_id,
2112 l_adjusted_amount,
2113 l_adjusted_date,
2114 l_adj_remaining_amount;
2115 exit when appl_cur%NOTFOUND;
2116
2117 if l_ar_remaining_amount > 0 or l_adj_remaining_amount > 0 then
2118 y := y+1;
2119 l_appl_tbl(y).receivable_application_id := l_receivable_application_id;
2120 l_appl_tbl(y).ar_applied_amount := l_ar_applied_amount;
2121 l_appl_tbl(y).ar_remaining_amount := l_ar_remaining_amount;
2122 l_appl_tbl(y).ar_apply_date := l_ar_apply_date;
2123 l_appl_tbl(y).adjustment_id := l_adjustment_id;
2124 l_appl_tbl(y).ar_adjusted_amount := l_adjusted_amount;
2125 l_appl_tbl(y).ar_adj_remaining_amount := l_adj_remaining_amount;
2126 l_appl_tbl(y).ar_adjusted_date := l_adjusted_date;
2127
2128 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2129 IF l_appl_tbl(y).receivable_application_id IS NOT NULL THEN
2130 LogMessage(G_PKG_NAME || '.' || l_api_name || ':found receivable_application_id = ' || l_appl_tbl(y).receivable_application_id);
2131 LogMessage(G_PKG_NAME || '.' || l_api_name || ':ar_applied_amount = ' || l_appl_tbl(y).ar_applied_amount);
2132 LogMessage(G_PKG_NAME || '.' || l_api_name || ':ar_remaining_amount = ' || l_appl_tbl(y).ar_remaining_amount);
2133 LogMessage(G_PKG_NAME || '.' || l_api_name || ':ar_apply_date = ' || l_appl_tbl(y).ar_apply_date);
2134 ELSE
2135 LogMessage(G_PKG_NAME || '.' || l_api_name || ':found adjustment_id = ' || l_appl_tbl(y).adjustment_id);
2136 LogMessage(G_PKG_NAME || '.' || l_api_name || ':ar_adjusted_amount = ' || l_appl_tbl(y).ar_adjusted_amount);
2137 LogMessage(G_PKG_NAME || '.' || l_api_name || ':ar_adj_remaining_amount = ' || l_appl_tbl(y).ar_adj_remaining_amount);
2138 LogMessage(G_PKG_NAME || '.' || l_api_name || ':ar_adjusted_date = ' || l_appl_tbl(y).ar_adjusted_date);
2139 END IF;
2140 END IF;
2141 end if;
2142
2143 END LOOP;
2144
2145 nCount1 := l_appl_tbl.count;
2146 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2147 LogMessage(G_PKG_NAME || '.' || l_api_name || ':Total found ' || nCount1 || ' available applications');
2148 END IF;
2149
2150 if nCount1 > 0 then -- do applications
2151 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2152 LogMessage(G_PKG_NAME || '.' || l_api_name || ':applying...');
2153 END IF;
2154
2155 FOR y in 1..nCount1 LOOP
2156
2157 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2158 LogMessage(G_PKG_NAME || '.' || l_api_name || ':processing application ' || y || ' Details:');
2159 LogMessage(G_PKG_NAME || '.' || l_api_name || ':receivable_application_id = ' || l_appl_tbl(y).receivable_application_id);
2160 LogMessage(G_PKG_NAME || '.' || l_api_name || ':ar_applied_amount = ' || l_appl_tbl(y).ar_applied_amount);
2161 LogMessage(G_PKG_NAME || '.' || l_api_name || ':ar_remaining_amount = ' || l_appl_tbl(y).ar_remaining_amount);
2162 LogMessage(G_PKG_NAME || '.' || l_api_name || ':ar_apply_date = ' || l_appl_tbl(y).ar_apply_date);
2163 LogMessage(G_PKG_NAME || '.' || l_api_name || ':adjustment_id = ' || l_appl_tbl(y).adjustment_id);
2164 LogMessage(G_PKG_NAME || '.' || l_api_name || ':ar_adjusted_amount = ' || l_appl_tbl(y).ar_adjusted_amount);
2165 LogMessage(G_PKG_NAME || '.' || l_api_name || ':ar_adj_remaining_amount = ' || l_appl_tbl(y).ar_adj_remaining_amount);
2166 LogMessage(G_PKG_NAME || '.' || l_api_name || ':ar_adjusted_date = ' || l_appl_tbl(y).ar_adjusted_date);
2167 LogMessage(G_PKG_NAME || '.' || l_api_name || ':pro_applied_amount = ' || l_appl_tbl(y).pro_applied_amount);
2168 END IF;
2169
2170 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2171 LogMessage(G_PKG_NAME || '.' || l_api_name || ':comparing application remaining amount = ' || l_appl_tbl(y).ar_remaining_amount);
2172 LogMessage(G_PKG_NAME || '.' || l_api_name || ':comparing adjustment remaining amount = ' || l_appl_tbl(y).ar_adj_remaining_amount);
2173 LogMessage(G_PKG_NAME || '.' || l_api_name || ':and promise remaining amount = ' || P_PROMISES_TBL(i).AMOUNT_DUE_REMAINING);
2174 END IF;
2175 if l_appl_tbl(y).ar_remaining_amount > 0 THEN
2176 if l_appl_tbl(y).ar_remaining_amount > P_PROMISES_TBL(i).AMOUNT_DUE_REMAINING then
2177 l_appl_tbl(y).pro_applied_amount := P_PROMISES_TBL(i).AMOUNT_DUE_REMAINING;
2178 elsif l_appl_tbl(y).ar_remaining_amount <= P_PROMISES_TBL(i).AMOUNT_DUE_REMAINING then
2179 l_appl_tbl(y).pro_applied_amount := l_appl_tbl(y).ar_remaining_amount;
2180 end if;
2181 elsif l_appl_tbl(y).ar_adj_remaining_amount > 0 THEN
2182 if l_appl_tbl(y).ar_adj_remaining_amount > P_PROMISES_TBL(i).AMOUNT_DUE_REMAINING then
2183 l_appl_tbl(y).pro_applied_amount := P_PROMISES_TBL(i).AMOUNT_DUE_REMAINING;
2184 elsif l_appl_tbl(y).ar_adj_remaining_amount <= P_PROMISES_TBL(i).AMOUNT_DUE_REMAINING then
2185 l_appl_tbl(y).pro_applied_amount := l_appl_tbl(y).ar_adj_remaining_amount;
2186 end if;
2187 end if;
2188
2189 P_PROMISES_TBL(i).AMOUNT_DUE_REMAINING := P_PROMISES_TBL(i).AMOUNT_DUE_REMAINING - l_appl_tbl(y).pro_applied_amount;
2190 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2191 LogMessage(G_PKG_NAME || '.' || l_api_name || ':we will apply amount = ' || l_appl_tbl(y).pro_applied_amount);
2192 LogMessage(G_PKG_NAME || '.' || l_api_name || ':promise remaining amount after this application = ' || P_PROMISES_TBL(i).AMOUNT_DUE_REMAINING);
2193 END IF;
2194
2195 l_applied_appl_count := y;
2196 if P_PROMISES_TBL(i).AMOUNT_DUE_REMAINING = 0 then -- we are done appliyng to the promise
2197 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2198 LogMessage(G_PKG_NAME || '.' || l_api_name || ':promise fulfilled by amount - exiting loop');
2199 END IF;
2200 exit;
2201 else -- we are not done yet. process next application
2202 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2203 LogMessage(G_PKG_NAME || '.' || l_api_name || ':promise still not fulfilled by amount - process next application');
2204 END IF;
2205 end if;
2206
2207 END LOOP;
2208
2209 if P_PROMISES_TBL(i).AMOUNT_DUE_REMAINING > 0 then
2210 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2211 LogMessage(G_PKG_NAME || '.' || l_api_name || ':no more available applications');
2212 END IF;
2213 end if;
2214
2215 l_callback_date := null;
2216 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2217 LogMessage(G_PKG_NAME || '.' || l_api_name || ':figuring out promise status and state ...');
2218 END IF;
2219
2220 if trunc(sysdate) > trunc(P_PROMISES_TBL(i).PROMISE_DATE) then /* the promise is in the past */
2221 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2222 LogMessage(G_PKG_NAME || '.' || l_api_name || ':the promise is in the past');
2223 END IF;
2224
2225 if P_PROMISES_TBL(i).AMOUNT_DUE_REMAINING = 0 then -- promise is fulfilled by amount
2226 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2227 LogMessage(G_PKG_NAME || '.' || l_api_name || ':the promise is fulfilled by amount - setting status FILFILLED');
2228 END IF;
2229 l_status := 'FULFILLED';
2230
2231 if (trunc(l_appl_tbl(l_applied_appl_count).ar_apply_date) > trunc(P_PROMISES_TBL(i).PROMISE_DATE))
2232 or (trunc(l_appl_tbl(l_applied_appl_count).ar_adjusted_date) > trunc(P_PROMISES_TBL(i).PROMISE_DATE))
2233 then -- payments are late
2234 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2235 LogMessage(G_PKG_NAME || '.' || l_api_name || ':payments are late - setting state to BROKEN_PROMISE');
2236 END IF;
2237 l_state := 'BROKEN_PROMISE';
2238 else -- payment on time
2239 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2240 LogMessage(G_PKG_NAME || '.' || l_api_name || ':payments are on time - setting state PROMISE');
2241 END IF;
2242 l_state := 'PROMISE';
2243 end if;
2244
2245 else -- promise is not fulfilled by amount
2246 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2247 LogMessage(G_PKG_NAME || '.' || l_api_name || ':the promise is not fulfilled by amount - leaving status COLLECTABLE');
2248 END IF;
2249 l_status := 'COLLECTABLE';
2250
2251 if P_PROMISES_TBL(i).STATE = 'PROMISE' then
2252 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2253 LogMessage(G_PKG_NAME || '.' || l_api_name || ':the promise state is PROMISE - setting state to BROKEN_PROMISE');
2254 END IF;
2255 l_state := 'BROKEN_PROMISE';
2256 Get_Callback_Date(p_promise_date => P_PROMISES_TBL(i).PROMISE_DATE, x_callback_date => l_callback_date);
2257 elsif P_PROMISES_TBL(i).STATE = 'BROKEN_PROMISE' then
2258 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2259 LogMessage(G_PKG_NAME || '.' || l_api_name || ':the promise state is already BROKEN_PROMISE - leave it BROKEN_PROMISE');
2260 END IF;
2261 l_state := 'BROKEN_PROMISE';
2262 end if;
2263 end if;
2264
2265 else /* promise is in the future */
2266
2267 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2268 LogMessage(G_PKG_NAME || '.' || l_api_name || ':the promise is in the future - leaving state PROMISE');
2269 END IF;
2270 l_state := 'PROMISE';
2271 if P_PROMISES_TBL(i).AMOUNT_DUE_REMAINING = 0 then -- promise is fulfilled by amount
2272 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2273 LogMessage(G_PKG_NAME || '.' || l_api_name || ':the promise is fulfilled by amount - setting status to FULFILLED');
2274 END IF;
2275 l_status := 'FULFILLED';
2276 else
2277 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2278 LogMessage(G_PKG_NAME || '.' || l_api_name || ':the promise not fulfilled by amount - leaving status COLLECTABLE');
2279 END IF;
2280 l_status := 'COLLECTABLE';
2281 end if;
2282
2283 end if;
2284 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2285 LogMessage(G_PKG_NAME || '.' || l_api_name || '......................');
2286 LogMessage(G_PKG_NAME || '.' || l_api_name || ':will set promise status to ' || l_status);
2287 LogMessage(G_PKG_NAME || '.' || l_api_name || ':will set promise state to ' || l_state);
2288 LogMessage(G_PKG_NAME || '.' || l_api_name || ':Updating iex_promise_details with promise_detail_id = ' || P_PROMISES_TBL(i).PROMISE_DETAIL_ID || ' set:');
2289 LogMessage(G_PKG_NAME || '.' || l_api_name || ':amount_due_remaining ' || P_PROMISES_TBL(i).AMOUNT_DUE_REMAINING);
2290 END IF;
2291
2292 if l_callback_date is not null then
2293 UPDATE iex_promise_details
2294 SET amount_due_remaining = P_PROMISES_TBL(i).AMOUNT_DUE_REMAINING,
2295 STATUS = l_status,
2296 STATE = l_state,
2297 CALLBACK_CREATED_YN = 'N',
2298 CALLBACK_DATE = l_callback_date,
2299 last_update_date = sysdate,
2300 last_updated_by = G_USER_ID
2301 WHERE promise_detail_id = P_PROMISES_TBL(i).PROMISE_DETAIL_ID;
2302 else
2303 UPDATE iex_promise_details
2304 SET amount_due_remaining = P_PROMISES_TBL(i).AMOUNT_DUE_REMAINING,
2305 STATUS = l_status,
2306 STATE = l_state,
2307 last_update_date = sysdate,
2308 last_updated_by = G_USER_ID
2309 WHERE promise_detail_id = P_PROMISES_TBL(i).PROMISE_DETAIL_ID;
2310 end if;
2311
2312 if (sql%notfound) then
2313 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2314 LogMessage(G_PKG_NAME || '.' || l_api_name || ':Failed to update iex_promise_details');
2315 END IF;
2316 -- Begin - Andre Araujo - 09/30/2004- Remove obsolete logging
2317 /*
2318 IEX_CONC_REQUEST_MSG_PKG.Log_Error(
2319 P_Concurrent_Request_ID => FND_GLOBAL.CONC_REQUEST_ID,
2320 P_Procedure_name => G_PKG_NAME || '.' || l_api_name,
2321 P_MESSAGE => 'Failed to update iex_promise_details');
2322 */
2323 -- End - Andre Araujo - 09/30/2004- Remove obsolete logging
2324 else
2325 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2326 LogMessage(G_PKG_NAME || '.' || l_api_name || ':update successfull');
2327 END IF;
2328 end if;
2329
2330 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2331 LogMessage(G_PKG_NAME || '.' || l_api_name || ':Inserting into iex_prd_appl_xref values:');
2332 END IF;
2333
2334 FOR x in 1..l_applied_appl_count LOOP
2335 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2336 LogMessage(G_PKG_NAME || '.' || l_api_name || ':record ' || x);
2337 LogMessage(G_PKG_NAME || '.' || l_api_name || ':PROMISE_DETAIL_ID = ' || P_PROMISES_TBL(i).PROMISE_DETAIL_ID);
2338 LogMessage(G_PKG_NAME || '.' || l_api_name || ':RECEIVABLE_APPLICATION_ID ' || l_appl_tbl(x).receivable_application_id);
2339 LogMessage(G_PKG_NAME || '.' || l_api_name || ':AMOUNT_APPLIED ' || l_appl_tbl(x).pro_applied_amount);
2340 LogMessage(G_PKG_NAME || '.' || l_api_name || ':ADJUSTMENT_ID = ' || l_appl_tbl(x).adjustment_id);
2341 LogMessage(G_PKG_NAME || '.' || l_api_name || ':AR_ADJUSTED_AMOUNT = ' || l_appl_tbl(x).ar_adjusted_amount);
2342 LogMessage(G_PKG_NAME || '.' || l_api_name || ':PRO_APPLIED_AMOUNT = ' || l_appl_tbl(x).pro_applied_amount);
2343 END IF;
2344
2345 INSERT INTO iex_prd_appl_xref
2346 (PRD_APPL_XREF_ID
2347 ,PROMISE_DETAIL_ID
2348 ,RECEIVABLE_APPLICATION_ID
2349 ,AMOUNT_APPLIED
2350 ,APPLY_DATE
2351 ,REVERSED_FLAG
2352 ,REVERSED_DATE
2353 ,LAST_UPDATE_DATE
2354 ,LAST_UPDATED_BY
2355 ,LAST_UPDATE_LOGIN
2356 ,CREATION_DATE
2357 ,CREATED_BY
2358 ,PROGRAM_ID
2359 ,OBJECT_VERSION_NUMBER
2360 ,SECURITY_GROUP_ID
2361 ,REQUEST_ID
2362 ,ADJUSTMENT_ID)
2363 VALUES (
2364 iex_prd_appl_xref_s.NEXTVAL
2365 ,P_PROMISES_TBL(i).PROMISE_DETAIL_ID
2366 ,l_appl_tbl(x).receivable_application_id
2367 ,l_appl_tbl(x).pro_applied_amount
2368 ,sysdate
2369 ,null
2370 ,null
2371 ,SYSDATE
2372 ,G_USER_ID
2373 ,G_LOGIN_ID
2374 ,SYSDATE
2375 ,G_USER_ID
2376 ,G_PROGRAM_ID
2377 ,1.0
2378 ,null
2379 ,G_REQUEST_ID
2380 ,l_appl_tbl(x).adjustment_id);
2381 END LOOP;
2382 -- End bug 3635087 gnramasa 10/07/07
2383 -- reopen strategy for just got broken promise
2384 if l_callback_date is not null then
2385 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2386 LogMessage(G_PKG_NAME || '.' || l_api_name || ': reopen strategy for promise ' || P_PROMISES_TBL(i).PROMISE_DETAIL_ID);
2387 END IF;
2388 IEX_PROMISES_PUB.SET_STRATEGY(P_PROMISE_ID => P_PROMISES_TBL(i).PROMISE_DETAIL_ID,
2389 P_STATUS => 'OPEN');
2390 end if;
2391
2392 else -- nothing to apply
2393 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2394 LogMessage(G_PKG_NAME || '.' || l_api_name || ':nothing to apply');
2395 LogMessage(G_PKG_NAME || '.' || l_api_name || ':leaving promise status as it is - COLLECTABLE');
2396 LogMessage(G_PKG_NAME || '.' || l_api_name || ':figuring out promise state ...');
2397 END IF;
2398
2399 if P_PROMISES_TBL(i).STATE = 'PROMISE' then
2400 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2401 LogMessage(G_PKG_NAME || '.' || l_api_name || ':the promise state is still PROMISE');
2402 END IF;
2403
2404 if trunc(sysdate) > trunc(P_PROMISES_TBL(i).PROMISE_DATE) then /* the promise is in the past */
2405 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2406 LogMessage(G_PKG_NAME || '.' || l_api_name || ':the promise is in the past - setting state to BROKEN_PROMISE');
2407 END IF;
2408 l_state := 'BROKEN_PROMISE';
2409 Get_Callback_Date(p_promise_date => P_PROMISES_TBL(i).PROMISE_DATE, x_callback_date => l_callback_date);
2410
2411 if l_callback_date is not null then
2412 UPDATE iex_promise_details
2413 SET state = l_state,
2414 CALLBACK_CREATED_YN = 'N',
2415 CALLBACK_DATE = l_callback_date,
2416 last_update_date = sysdate,
2417 last_updated_by = G_USER_ID
2418 WHERE promise_detail_id = P_PROMISES_TBL(i).PROMISE_DETAIL_ID;
2419 else
2420 UPDATE iex_promise_details
2421 SET state = l_state,
2422 last_update_date = sysdate,
2423 last_updated_by = G_USER_ID
2424 WHERE promise_detail_id = P_PROMISES_TBL(i).PROMISE_DETAIL_ID;
2425 end if;
2426
2427 if (sql%notfound) then
2428 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2429 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);
2430 END IF;
2431 -- Begin - Andre Araujo - 09/30/2004- Remove obsolete logging
2432 /*
2433 IEX_CONC_REQUEST_MSG_PKG.Log_Error(
2434 P_Concurrent_Request_ID => FND_GLOBAL.CONC_REQUEST_ID,
2435 P_Procedure_name => G_PKG_NAME || '.' || l_api_name,
2436 P_MESSAGE => 'Failed to update iex_promise_details with STATUS = BROKEN for promise_detail_id = ' || P_PROMISES_TBL(i).PROMISE_DETAIL_ID);
2437 */
2438 -- End - Andre Araujo - 09/30/2004- Remove obsolete logging
2439 else
2440 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2441 LogMessage(G_PKG_NAME || '.' || l_api_name || ':update successfull');
2442 END IF;
2443
2444 -- reopen strategy for just got broken promise
2445 if l_callback_date is not null then
2446 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2447 LogMessage(G_PKG_NAME || '.' || l_api_name || ': reopen strategy for promise ' || P_PROMISES_TBL(i).PROMISE_DETAIL_ID);
2448 END IF;
2449 IEX_PROMISES_PUB.SET_STRATEGY(P_PROMISE_ID => P_PROMISES_TBL(i).PROMISE_DETAIL_ID,
2450 P_STATUS => 'OPEN');
2451 end if;
2452 end if;
2453 else /* promise is in the future */
2454 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2455 LogMessage(G_PKG_NAME || '.' || l_api_name || ':the promise is in the future - leaving the promise state PROMISE');
2456 END IF;
2457 end if;
2458 elsif P_PROMISES_TBL(i).STATE = 'BROKEN_PROMISE' then
2459 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2460 LogMessage(G_PKG_NAME || '.' || l_api_name || ':the promise state is already BROKEN_PROMISE - nothing to change.');
2461 END IF;
2462 end if;
2463 end if;
2464 END LOOP;
2465
2466 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2467 LogMessage(G_PKG_NAME || '.' || l_api_name || ':End of body');
2468 END IF;
2469 -- END OF BODY OF API
2470
2471 -- Standard check of p_commit.
2472 IF FND_API.To_Boolean( p_commit ) THEN
2473 COMMIT WORK;
2474 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2475 LogMessage(G_PKG_NAME || '.' || l_api_name || ':Commited work');
2476 END IF;
2477 END IF;
2478
2479 x_return_status := l_return_status;
2480 -- Standard call to get message count and if count is 1, get message info
2481 FND_MSG_PUB.Count_And_Get(
2482 p_encoded => FND_API.G_FALSE,
2483 p_count => x_msg_count,
2484 p_data => x_msg_data);
2485
2486 EXCEPTION
2487
2488 WHEN FND_API.G_EXC_ERROR THEN
2489 ROLLBACK TO APPLY_PROMISES_FIFO_PVT;
2490 x_return_status := FND_API.G_RET_STS_ERROR;
2491 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2492 -- Begin - Andre Araujo - 09/30/2004- Remove obsolete logging
2493 /*
2494 IEX_CONC_REQUEST_MSG_PKG.Log_Error(
2495 P_Concurrent_Request_ID => FND_GLOBAL.CONC_REQUEST_ID,
2496 P_Procedure_name => G_PKG_NAME || '.' || l_api_name,
2497 P_MESSAGE => 'Failed to do FIFO promise applications');
2498 */
2499 -- End - Andre Araujo - 09/30/2004- Remove obsolete logging
2500 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2501 LogMessage(G_PKG_NAME || '.' || l_api_name || ': In G_EXC_ERROR exception. Failed to do FIFO promise applications');
2502 END IF;
2503 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2504 ROLLBACK TO APPLY_PROMISES_FIFO_PVT;
2505 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2506 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2507 -- Begin - Andre Araujo - 09/30/2004- Remove obsolete logging
2508 /*
2509 IEX_CONC_REQUEST_MSG_PKG.Log_Error(
2510 P_Concurrent_Request_ID => FND_GLOBAL.CONC_REQUEST_ID,
2511 P_Procedure_name => G_PKG_NAME || '.' || l_api_name,
2512 P_MESSAGE => 'Failed to do FIFO promise applications');
2513 */
2514 -- End - Andre Araujo - 09/30/2004- Remove obsolete logging
2515 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2516 LogMessage(G_PKG_NAME || '.' || l_api_name || ': In G_EXC_UNEXPECTED_ERROR exception. Failed to do FIFO promise applications');
2517 END IF;
2518 WHEN OTHERS THEN
2519 ROLLBACK TO APPLY_PROMISES_FIFO_PVT;
2520 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2521 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)THEN
2522 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
2523 END IF;
2524 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2525 -- Begin - Andre Araujo - 09/30/2004- Remove obsolete logging
2526 /*
2527 IEX_CONC_REQUEST_MSG_PKG.Log_Error(
2528 P_Concurrent_Request_ID => FND_GLOBAL.CONC_REQUEST_ID,
2529 P_Procedure_name => G_PKG_NAME || '.' || l_api_name,
2530 P_MESSAGE => 'Failed to do FIFO promise applications');
2531 */
2532 -- End - Andre Araujo - 09/30/2004- Remove obsolete logging
2533 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2534 LogMessage(G_PKG_NAME || '.' || l_api_name || ': In OTHERS exception. Failed to do FIFO promise applications');
2535 END IF;
2536 END;
2537
2538
2539 PROCEDURE Copy_Task_Ref_To_Tab( p_counter BINARY_INTEGER,
2540 p_object_type_code varchar2,
2541 p_object_id number ) IS
2542
2543 Cursor Get_Object_Type(l_object_type_code varchar2) IS
2544 select o.name,o.select_id,o.select_name,o.select_details,o.from_table,o.where_clause
2545 from jtf_objects_vl o,
2546 jtf_object_usages u
2547 where trunc(sysdate)
2548 between trunc(nvl(o.start_date_active, sysdate))
2549 and trunc(nvl(o.end_date_active, sysdate))
2550 and u.object_user_code = 'TASK'
2551 and u.object_code = o.object_code
2552 and o.object_code <> 'ESC'
2553 and o.object_code = l_object_type_code;
2554
2555 l_select_id VARCHAR2(200);
2556 l_select_name VARCHAR2(200);
2557 l_select_details VARCHAR2(2000);
2558 l_from_table VARCHAR2(200);
2559 l_where_clause VARCHAR2(2000);
2560 l_CursorID INTEGER;
2561 l_SelectStmt VARCHAR2(2500);
2562 l_Dummy INTEGER;
2563 l_object_name VARCHAR2(360);
2564
2565 l_object_type varchar2(80);
2566 l_object_details varchar2(2000);
2567 l_current_block varchar2(2000);
2568
2569 BEGIN
2570
2571 OPEN Get_Object_Type(p_object_type_code);
2572 FETCH Get_Object_Type INTO l_object_type,l_select_id,l_select_name,l_select_details,l_from_table,l_where_clause;
2573 if Get_Object_Type%FOUND then
2574
2575 l_CursorID := DBMS_SQL.OPEN_CURSOR;
2576
2577 l_SelectStmt := 'SELECT ' || l_select_name;
2578
2579 IF (l_select_details IS NOT NULL) THEN
2580 l_SelectStmt := l_SelectStmt || ',' || l_select_details;
2581 END IF;
2582
2583 l_SelectStmt := l_SelectStmt || ' FROM '|| l_from_table || ' WHERE ' || l_where_clause;
2584
2585 IF l_where_clause is not null THEN
2586 l_SelectStmt := l_SelectStmt || ' AND ' ;
2587 END IF;
2588
2589 l_SelectStmt := l_SelectStmt || l_select_id || ' = :source_object_id ';
2590
2591 DBMS_SQL.PARSE(l_CursorID, l_SelectStmt, 1 );
2592
2593 DBMS_SQL.BIND_VARIABLE(l_CursorID,':source_object_id',p_object_id);
2594
2595 DBMS_SQL.DEFINE_COLUMN(l_CursorID, 1 , l_object_name , 360 );
2596 IF (l_select_details IS NOT NULL) THEN
2597 DBMS_SQL.DEFINE_COLUMN(l_CursorID, 2 , l_object_details , 2000 );
2598 END IF;
2599
2600 l_Dummy := DBMS_SQL.EXECUTE(l_CursorID);
2601
2602 LOOP
2603
2604 IF DBMS_SQL.FETCH_ROWS(l_CursorID) = 0 THEN
2605
2606 EXIT;
2607
2608 END IF;
2609
2610 DBMS_SQL.COLUMN_VALUE(l_CursorID, 1 , l_object_name );
2611 IF (l_select_details IS NOT NULL) THEN
2612 DBMS_SQL.COLUMN_VALUE(l_CursorID, 2 , l_object_details );
2613 END IF;
2614
2615
2616 END LOOP;
2617
2618 DBMS_SQL.CLOSE_CURSOR(l_CursorID);
2619 end if;
2620 CLOSE Get_Object_Type;
2621
2622
2623 G_TASK_REFERENCE_TAB(p_counter).object_type_code := p_object_type_code;
2624 G_TASK_REFERENCE_TAB(p_counter).object_type_name := l_object_type;
2625 G_TASK_REFERENCE_TAB(p_counter).object_name := l_object_name;
2626 G_TASK_REFERENCE_TAB(p_counter).object_id := p_object_id;
2627 G_TASK_REFERENCE_TAB(p_counter).object_details := l_object_details;
2628 G_TASK_REFERENCE_TAB(p_counter).reference_code := null;
2629 G_TASK_REFERENCE_TAB(p_counter).usage := null;
2630
2631
2632 END Copy_Task_Ref_To_Tab;
2633
2634
2635 /**********************
2636 This procedure processing promise callbacks
2637 ***********************/
2638 PROCEDURE PROCESS_PROMISE_CALLBACKS(
2639 p_api_version IN NUMBER,
2640 p_init_msg_list IN VARCHAR2,
2641 p_commit IN VARCHAR2,
2642 P_VALIDATION_LEVEL IN NUMBER,
2643 x_return_status OUT NOCOPY VARCHAR2,
2644 x_msg_count OUT NOCOPY NUMBER,
2645 x_msg_data OUT NOCOPY VARCHAR2)
2646 IS
2647 CURSOR C_GET_PROS IS
2648 SELECT
2649 pro.promise_detail_id,
2650 hca.party_id,
2651 pro.resource_id,
2652 pro.cust_account_id,
2653 idel.customer_site_use_id,
2654 idel.delinquency_id
2655 FROM IEX_PROMISE_DETAILS pro, HZ_CUST_ACCOUNTS hca, iex_delinquencies_all idel
2656 WHERE
2657 pro.cust_account_id = hca.cust_account_id
2658 AND idel.delinquency_id(+) = pro.delinquency_id
2659 AND pro.state = 'BROKEN_PROMISE'
2660 AND pro.CALLBACK_CREATED_YN = 'N'
2661 AND trunc(sysdate) >= trunc(pro.callback_date);
2662
2663 l_api_name CONSTANT VARCHAR2(30) := 'PROCESS_PROMISE_CALLBACKS';
2664 l_api_version CONSTANT NUMBER := 1.0;
2665 l_return_status VARCHAR2(1);
2666 l_msg_count NUMBER;
2667 l_msg_data VARCHAR2(32767);
2668
2669 l_promise_detail_id NUMBER;
2670 l_task_id NUMBER;
2671 l_party_id NUMBER;
2672 l_resource_id NUMBER;
2673 l_task_name varchar2(80) ;
2674 l_task_type varchar2(30) ;
2675 l_task_status varchar2(30) ;
2676 l_description varchar2(4000);
2677 l_task_priority_name varchar2(30) ;
2678 l_task_priority_id number;
2679 l_owner_id number;
2680 l_owner varchar2(4000);
2681 l_owner_type_code varchar2(4000);
2682 l_customer_id number;
2683 l_cust_account_id number;
2684 l_address_id number;
2685 l_customer_site_use_id number;
2686 l_delinquency_id number;
2687 p_counter number;
2688 l_task_notes_tbl JTF_TASKS_PUB.TASK_NOTES_TBL;
2689 l_miss_task_assign_tbl JTF_TASKS_PUB.TASK_ASSIGN_TBL;
2690 l_miss_task_depends_tbl JTF_TASKS_PUB.TASK_DEPENDS_TBL;
2691 l_miss_task_rsrc_req_tbl JTF_TASKS_PUB.TASK_RSRC_REQ_TBL;
2692 l_miss_task_refer_tbl JTF_TASKS_PUB.TASK_REFER_TBL;
2693 l_miss_task_dates_tbl JTF_TASKS_PUB.TASK_DATES_TBL;
2694 l_miss_task_recur_rec JTF_TASKS_PUB.TASK_RECUR_REC;
2695 l_miss_task_contacts_tbl JTF_TASKS_PUB.TASK_CONTACTS_TBL;
2696
2697 --Begin bug 7317666 21-Nov-2008 barathsr
2698 cursor c_invalid_tasks is
2699 select tsk.task_id,
2700 tsk.object_version_number
2701 --,tsk.task_type_id,typ.name task_type, tsk.task_status_id,st.name,tsk.source_object_id
2702 from jtf_tasks_vl tsk,
2703 jtf_task_types_tl typ,
2704 jtf_task_statuses_vl st
2705 where tsk.source_object_type_code='IEX_PROMISE'
2706 and tsk.task_type_id=typ.task_type_id
2707 and typ.name='Callback'
2708 and tsk.task_status_id=st.task_status_id
2709 and nvl(st.closed_flag, 'N') <>'Y'
2710 and nvl(st.cancelled_flag, 'N')<>'Y'
2711 and nvl(st.completed_flag, 'N')<>'Y'
2712 and exists(select 1 from iex_promise_details prd where tsk.source_object_id = prd.promise_detail_id
2713 and prd.status<>'COLLECTABLE');
2714 l_obj_version_number number;
2715 --End bug 7317666 21-Nov-2008 barathsr
2716
2717
2718 BEGIN
2719
2720 -- Standard Start of API savepoint
2721 SAVEPOINT PROCESS_PROMISE_CALLBACKS_PUB;
2722
2723 -- Standard call to check for call compatibility.
2724 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
2725 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2726 END IF;
2727
2728 -- Initialize message list if p_init_msg_list is set to TRUE.
2729 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2730 FND_MSG_PUB.initialize;
2731 END IF;
2732
2733 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2734 LogMessage( 'Start time: ' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
2735 END IF;
2736
2737 -- Initialize API return status to SUCCESS
2738 x_return_status := FND_API.G_RET_STS_SUCCESS;
2739
2740 -- START OF BODY OF API
2741 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2742 LogMessage( 'Start of ' || l_api_name || ' body');
2743 END IF;
2744
2745 Open C_GET_PROS;
2746 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2747 LogMessage( 'OPEN C_GET_PROS');
2748 END IF;
2749 LOOP
2750
2751 Fetch C_GET_PROS into
2752 l_promise_detail_id,
2753 l_party_id,
2754 l_resource_id,
2755 l_cust_account_id,
2756 l_customer_site_use_id,
2757 l_delinquency_id;
2758
2759 EXIT WHEN C_GET_PROS%NOTFOUND;
2760
2761 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2762 LogMessage( 'Found promise');
2763 LogMessage( 'promise_detail_id =' || l_promise_detail_id );
2764 LogMessage( 'party_id =' || l_party_id );
2765 LogMessage( 'resource_id =' || l_resource_id );
2766 LogMessage( 'l_cust_account_id =' || l_cust_account_id );
2767 END IF;
2768
2769 If ( l_resource_id is null ) Then
2770 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2771 LogMessage( 'No Resource_ID');
2772 END IF;
2773 -- Begin - Andre Araujo - 09/30/2004- Remove obsolete logging
2774 /*
2775 IEX_CONC_REQUEST_MSG_PKG.Log_Error(
2776 P_Concurrent_Request_ID => FND_GLOBAL.CONC_REQUEST_ID,
2777 P_Procedure_name => G_PKG_NAME || '.' || l_api_name,
2778 P_MESSAGE => 'No resource_ID for promise_detail_id = ' || l_promise_detail_id || '. Cannot create task.');
2779 */
2780 -- End - Andre Araujo - 09/30/2004- Remove obsolete logging
2781 else
2782 --Bug4201040. Fix By LKKUMAR on 24-Jan-2006. Start.
2783 --l_task_name := 'Oracle Collections Callback for Broken Promise';
2784 l_task_name := 'Broken Promise Callback';
2785 l_task_status := 'Open';
2786 l_task_type := 'Callback';
2787 --l_description := 'Oracle Collections Callback for Broken Promise';
2788 l_description := 'Broken Promise Callback';
2789 --Bug4201040. Fix By LKKUMAR on 24-Jan-2006. End.
2790 l_owner_type_code := 'RS_EMPLOYEE';
2791 l_owner_id := l_resource_id;
2792 l_customer_id := l_party_id;
2793
2794 /* begin kasreeni 01/20/2006 Create task reference for Party_id, ACCOUNT and bill to */
2795
2796 G_TASK_REFERENCE_TAB := l_miss_task_refer_tbl;
2797 p_counter := 1;
2798 copy_Task_ref_to_Tab(p_counter, 'IEX_ACCOUNT', l_cust_account_id);
2799
2800 if (l_customer_site_use_id is not null) then
2801 p_counter := p_counter + 1;
2802 copy_Task_ref_to_Tab(p_counter, 'IEX_BILLTO', l_customer_site_use_id);
2803 p_counter := p_counter + 1;
2804 copy_Task_ref_to_Tab(p_counter, 'IEX_DELINQUENCY', l_delinquency_id);
2805 end if;
2806
2807 /* end kasreeni 01/20/2006 Create task reference for Party_id, ACCOUNT and bill to */
2808
2809 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2810 LogMessage( 'Calling JTF_TASKS_PUB.CREATE_TASK...');
2811 END IF;
2812
2813 JTF_TASKS_PUB.CREATE_TASK(
2814 p_api_version => p_api_version,
2815 p_init_msg_list => p_init_msg_list,
2816 p_commit => p_commit,
2817 p_task_name => l_task_name,
2818 p_task_type_name => l_task_type,
2819 p_task_status_name => l_task_status,
2820 p_owner_type_code => l_owner_type_code,
2821 p_owner_id => l_owner_id,
2822 p_description => l_description,
2823 p_customer_id => l_customer_id,
2824 P_CUST_ACCOUNT_ID => l_cust_account_id,
2825 P_SOURCE_OBJECT_TYPE_CODE => 'IEX_PROMISE',
2826 P_SOURCE_OBJECT_ID => l_promise_detail_id,
2827 P_SOURCE_OBJECT_NAME => l_promise_detail_id,
2828 p_task_assign_tbl => l_miss_task_assign_tbl,
2829 p_task_depends_tbl => l_miss_task_depends_tbl,
2830 p_task_rsrc_req_tbl => l_miss_task_rsrc_req_tbl,
2831 p_task_refer_tbl => G_TASK_REFERENCE_TAB,
2832 p_task_dates_tbl => l_miss_task_dates_tbl,
2833 p_task_notes_tbl => l_task_notes_tbl,
2834 p_task_recur_rec => l_miss_task_recur_rec,
2835 p_task_contacts_tbl => l_miss_task_contacts_tbl,
2836 x_return_status => l_return_status,
2837 x_msg_count => l_msg_count,
2838 x_msg_data => l_msg_data,
2839 x_task_id => l_task_id );
2840
2841 -- check for errors
2842 IF l_return_status<>FND_API.G_RET_STS_SUCCESS THEN
2843 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2844 LogMessage( 'Call JTF_TASKS_PUB.CREATE_TASK failed');
2845 END IF;
2846 -- Begin - Andre Araujo - 09/30/2004- Remove obsolete logging
2847 /*
2848 IEX_CONC_REQUEST_MSG_PKG.Log_Error(
2849 P_Concurrent_Request_ID => FND_GLOBAL.CONC_REQUEST_ID,
2850 P_Procedure_name => G_PKG_NAME || '.' || l_api_name,
2851 P_MESSAGE => 'Call JTF_TASKS_PUB.CREATE_TASK failed');
2852 */
2853 -- End - Andre Araujo - 09/30/2004- Remove obsolete logging
2854 exit;
2855 ELSE
2856 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2857 LogMessage( 'Call JTF_TASKS_PUB.CREATE_TASK succeeded. Task_ID=' || l_task_id);
2858 END IF;
2859
2860 END IF;
2861
2862 -- update iex_promise_details table
2863 UPDATE iex_promise_details
2864 SET CALLBACK_CREATED_YN = 'Y',
2865 last_update_date = sysdate,
2866 last_updated_by = G_USER_ID
2867 WHERE promise_detail_id = l_promise_detail_id;
2868
2869 if (sql%notfound) then
2870 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2871 LogMessage( 'update failed');
2872 END IF;
2873 -- Begin - Andre Araujo - 09/30/2004- Remove obsolete logging
2874 /*
2875 IEX_CONC_REQUEST_MSG_PKG.Log_Error(
2876 P_Concurrent_Request_ID => FND_GLOBAL.CONC_REQUEST_ID,
2877 P_Procedure_name => G_PKG_NAME || '.' || l_api_name,
2878 P_MESSAGE => 'Failed to update iex_promise_details for promise_detail_id = ' || l_promise_detail_id);
2879 */
2880 -- End - Andre Araujo - 09/30/2004- Remove obsolete logging
2881 else
2882 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2883 LogMessage( 'update successfull');
2884 END IF;
2885 end if;
2886
2887 end if;
2888
2889 end loop; -- end of CURSOR loop
2890
2891 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2892 LogMessage( 'Close C_GET_PROS');
2893 END IF;
2894 Close C_GET_PROS;
2895
2896 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2897 LogMessage( 'End of ' || l_api_name || ' body');
2898 END IF;
2899 -- END OF BODY OF API
2900
2901 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2902 LogMessage( 'End time:' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
2903 END IF;
2904
2905 -- Standard check of p_commit.
2906 IF FND_API.To_Boolean( p_commit ) THEN
2907 COMMIT WORK;
2908 END IF;
2909
2910 --Begin bug 7317666 21-Nov-2008 barathsr
2911 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2912 LogMessage( 'Cancelling the callback tasks correpsonding to fulfilled promises...');
2913 END IF;
2914 FOR rec1 IN c_invalid_tasks LOOP
2915 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2916 LogMessage( 'Cancelling the callback task: '||rec1.task_id);
2917 END IF;
2918 l_obj_version_number:=rec1.object_version_number;
2919 JTF_TASKS_PUB.UPDATE_TASK(
2920 P_API_VERSION => p_api_version,
2921 P_INIT_MSG_LIST => p_init_msg_list,
2922 P_COMMIT => p_commit,
2923 P_OBJECT_VERSION_NUMBER => l_obj_version_number,
2924 P_TASK_ID => rec1.task_id,
2925 P_TASK_STATUS_NAME => 'Cancelled',
2926 x_return_status => l_return_status,
2927 x_msg_count => l_msg_count,
2928 x_msg_data => l_msg_data);
2929
2930 END LOOP;
2931
2932 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2933 LogMessage( 'Completed Cancelling the callback tasks correpsonding to fulfilled promises...');
2934 END IF;
2935
2936 IF FND_API.To_Boolean( p_commit ) THEN
2937
2938 COMMIT WORK;
2939 END IF;
2940 --End bug 7317666 21-Nov-2008 barathsr
2941
2942 x_return_status := l_return_status;
2943 -- Standard call to get message count and if count is 1, get message info
2944 FND_MSG_PUB.Count_And_Get(
2945 p_encoded => FND_API.G_FALSE,
2946 p_count => x_msg_count,
2947 p_data => x_msg_data);
2948
2949 EXCEPTION
2950 WHEN FND_API.G_EXC_ERROR THEN
2951 ROLLBACK TO PROCESS_PROMISE_CALLBACKS_PUB;
2952 x_return_status := FND_API.G_RET_STS_ERROR;
2953 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2954 -- Begin - Andre Araujo - 09/30/2004- Remove obsolete logging
2955 /*
2956 IEX_CONC_REQUEST_MSG_PKG.Log_Error(
2957 P_Concurrent_Request_ID => FND_GLOBAL.CONC_REQUEST_ID,
2958 P_Procedure_name => G_PKG_NAME || '.' || l_api_name,
2959 P_MESSAGE => 'Failed to process promise callbacks');
2960 */
2961 -- End - Andre Araujo - 09/30/2004- Remove obsolete logging
2962 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2963 ROLLBACK TO PROCESS_PROMISE_CALLBACKS_PUB;
2964 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2965 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2966 -- Begin - Andre Araujo - 09/30/2004- Remove obsolete logging
2967 /*
2968 IEX_CONC_REQUEST_MSG_PKG.Log_Error(
2969 P_Concurrent_Request_ID => FND_GLOBAL.CONC_REQUEST_ID,
2970 P_Procedure_name => G_PKG_NAME || '.' || l_api_name,
2971 P_MESSAGE => 'Failed to process promise callbacks');
2972 */
2973 -- End - Andre Araujo - 09/30/2004- Remove obsolete logging
2974 WHEN OTHERS THEN
2975 ROLLBACK TO PROCESS_PROMISE_CALLBACKS_PUB;
2976 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2977 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2978 THEN
2979 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
2980 END IF;
2981 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2982 -- Begin - Andre Araujo - 09/30/2004- Remove obsolete logging
2983 /*
2984 IEX_CONC_REQUEST_MSG_PKG.Log_Error(
2985 P_Concurrent_Request_ID => FND_GLOBAL.CONC_REQUEST_ID,
2986 P_Procedure_name => G_PKG_NAME || '.' || l_api_name,
2987 P_MESSAGE => 'Failed to process promise callbacks');
2988 */
2989 -- End - Andre Araujo - 09/30/2004- Remove obsolete logging
2990
2991 END;
2992
2993 /**********************
2994 This procedure reopens promises for delinquencies that have been reopened.
2995 ***********************/
2996 PROCEDURE REOPEN_PROMISES(
2997 P_API_VERSION IN NUMBER,
2998 P_INIT_MSG_LIST IN VARCHAR2,
2999 P_COMMIT IN VARCHAR2,
3000 P_VALIDATION_LEVEL IN NUMBER,
3001 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
3002 X_MSG_COUNT OUT NOCOPY NUMBER,
3003 X_MSG_DATA OUT NOCOPY VARCHAR2,
3004 p_dels_tbl IN DBMS_SQL.NUMBER_TABLE /*table of delinquency ids*/)
3005 IS
3006 l_api_name CONSTANT VARCHAR2(30) := 'REOPEN_PROMISES';
3007 l_api_version CONSTANT NUMBER := 1.0;
3008 l_return_status VARCHAR2(1);
3009 l_msg_count NUMBER;
3010 l_msg_data VARCHAR2(32767);
3011
3012 l_promise_id NUMBER;
3013 l_promise_date DATE;
3014 l_del_count NUMBER;
3015 vSQL varchar2(10000);
3016
3017 Type refCur is Ref Cursor;
3018 promises_cur refCur;
3019 l_callback_date DATE;
3020 BEGIN
3021 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
3022
3023 /*
3024 Commented out whole procedure because PROB now can apply payments to promises or
3025 reverse payments from promises automatically.
3026 We do not need to close or reopen promises if delinquency is closed or reopened - all this will be done by PROB.
3027 We are obsoleting status CLOSED.
3028
3029 -- Standard start of API savepoint
3030 SAVEPOINT REOPEN_PROMISES_PVT;
3031
3032 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3033 LogMessage(G_PKG_NAME || '.REOPEN_PROMISES: Savepoint is established');
3034 END IF;
3035 -- Standard call to check for call compatibility
3036 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
3037 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3038 END IF;
3039
3040 -- Initialize message list if p_init_msg_list is set to TRUE
3041 IF FND_API.To_Boolean(p_init_msg_list) THEN
3042 FND_MSG_PUB.initialize;
3043 END IF;
3044
3045 -- Initialize API return status to success
3046 l_return_status := FND_API.G_RET_STS_SUCCESS;
3047
3048 -- START OF BODY OF API
3049 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3050 LogMessage(G_PKG_NAME || '.REOPEN_PROMISES: Start of ' || l_api_name || ' body');
3051 END IF;
3052
3053 l_del_count := p_dels_tbl.count;
3054 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3055 LogMessage(G_PKG_NAME || '.REOPEN_PROMISES: delinquencies count: ' || l_del_count);
3056 END IF;
3057 if l_del_count > 0 then
3058
3059 vSQL := 'SELECT ' ||
3060 'PROMISE_DETAIL_ID, PROMISE_DATE ' ||
3061 'FROM ' ||
3062 'IEX_PROMISE_DETAILS ' ||
3063 'WHERE ' ||
3064 'DELINQUENCY_ID is not null and ' ||
3065 'DELINQUENCY_ID = :del and ' ||
3066 'STATUS = ''CLOSED'' ' ||
3067 'ORDER BY PROMISE_DETAIL_ID';
3068
3069 FOR i in 1..l_del_count LOOP
3070 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3071 LogMessage(G_PKG_NAME || '.REOPEN_PROMISES: pulling closed promises for delinquency: ' || p_dels_tbl(i));
3072 END IF;
3073 open promises_cur for vSQL
3074 using p_dels_tbl(i);
3075
3076 LOOP
3077 fetch promises_cur into l_promise_id, l_promise_date;
3078 exit when promises_cur%NOTFOUND;
3079
3080 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3081 LogMessage(G_PKG_NAME || '.REOPEN_PROMISES: found promise with id: ' || l_promise_id);
3082 LogMessage(G_PKG_NAME || '.REOPEN_PROMISES: promise date: ' || l_promise_date);
3083 END IF;
3084
3085 if trunc(sysdate) > trunc(l_promise_date) then
3086 Get_Callback_Date(p_promise_date => l_promise_date, x_callback_date => l_callback_date);
3087
3088 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3089 LogMessage(G_PKG_NAME || '.REOPEN_PROMISES: updating promise ' || l_promise_id || ' with status BROKEN');
3090 LogMessage(G_PKG_NAME || '.REOPEN_PROMISES: callback date ' || l_callback_date);
3091 END IF;
3092 UPDATE iex_promise_details
3093 SET STATUS = 'BROKEN',
3094 CALLBACK_CREATED_YN = 'N',
3095 CALLBACK_DATE = l_callback_date,
3096 last_update_date = sysdate,
3097 last_updated_by = G_USER_ID
3098 WHERE promise_detail_id = l_promise_id;
3099 else
3100 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3101 LogMessage(G_PKG_NAME || '.REOPEN_PROMISES: updating promise ' || l_promise_id || ' with status OPEN');
3102 END IF;
3103 UPDATE iex_promise_details
3104 SET STATUS = 'OPEN',
3105 last_update_date = sysdate,
3106 last_updated_by = G_USER_ID
3107 WHERE promise_detail_id = l_promise_id;
3108 end if;
3109
3110 if (sql%notfound) then
3111 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3112 LogMessage(G_PKG_NAME || '.REOPEN_PROMISES: update of promise ' || l_promise_id || ' failed');
3113 END IF;
3114 -- Begin - Andre Araujo - 09/30/2004- Remove obsolete logging
3115 --IEX_CONC_REQUEST_MSG_PKG.Log_Error(
3116 -- P_Concurrent_Request_ID => FND_GLOBAL.CONC_REQUEST_ID,
3117 -- P_Procedure_name => G_PKG_NAME || '.' || l_api_name,
3118 -- P_MESSAGE => 'Failed to update iex_promise_details for promise_detail_id = ' || l_promise_id);
3119 -- End - Andre Araujo - 09/30/2004- Remove obsolete logging
3120 else
3121 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3122 LogMessage(G_PKG_NAME || '.REOPEN_PROMISES: update of promise ' || l_promise_id || ' succeeded');
3123 END IF;
3124 end if;
3125 END LOOP;
3126 END LOOP;
3127 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3128 LogMessage(G_PKG_NAME || '.REOPEN_PROMISES: done processing all delinquencies');
3129 END IF;
3130 end if;
3131
3132 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3133 LogMessage(G_PKG_NAME || '.REOPEN_PROMISES: End of ' || l_api_name || ' body');
3134 END IF;
3135 -- END OF BODY OF API
3136
3137 -- Standard check of p_commit.
3138 IF FND_API.To_Boolean( p_commit ) THEN
3139 COMMIT WORK;
3140 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3141 LogMessage(G_PKG_NAME || '.REOPEN_PROMISES: Commited');
3142 END IF;
3143 END IF;
3144
3145 x_return_status := l_return_status;
3146 -- Standard call to get message count and if count is 1, get message info
3147 FND_MSG_PUB.Count_And_Get(
3148 p_encoded => FND_API.G_FALSE,
3149 p_count => x_msg_count,
3150 p_data => x_msg_data);
3151
3152 EXCEPTION
3153 WHEN FND_API.G_EXC_ERROR THEN
3154 ROLLBACK TO REOPEN_PROMISES_PVT;
3155 x_return_status := FND_API.G_RET_STS_ERROR;
3156 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3157 -- Begin - Andre Araujo - 09/30/2004- Remove obsolete logging
3158 --IEX_CONC_REQUEST_MSG_PKG.Log_Error(
3159 -- P_Concurrent_Request_ID => FND_GLOBAL.CONC_REQUEST_ID,
3160 -- P_Procedure_name => 'IEX_PROMISES_BATCH_PUB.REOPEN_PROMISES',
3161 -- P_MESSAGE => 'Failed to reopen promises.' );
3162 -- End - Andre Araujo - 09/30/2004- Remove obsolete logging
3163 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3164 ROLLBACK TO REOPEN_PROMISES_PVT;
3165 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3166 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3167 -- Begin - Andre Araujo - 09/30/2004- Remove obsolete logging
3168 --IEX_CONC_REQUEST_MSG_PKG.Log_Error(
3169 -- P_Concurrent_Request_ID => FND_GLOBAL.CONC_REQUEST_ID,
3170 -- P_Procedure_name => 'IEX_PROMISES_BATCH_PUB.REOPEN_PROMISES',
3171 -- P_MESSAGE => 'Failed to reopen promises.' );
3172 -- End - Andre Araujo - 09/30/2004- Remove obsolete logging
3173 WHEN OTHERS THEN
3174 ROLLBACK TO REOPEN_PROMISES_PVT;
3175 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3176 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3177 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
3178 END IF;
3179 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3180 -- Begin - Andre Araujo - 09/30/2004- Remove obsolete logging
3181 --IEX_CONC_REQUEST_MSG_PKG.Log_Error(
3182 -- P_Concurrent_Request_ID => FND_GLOBAL.CONC_REQUEST_ID,
3183 -- P_Procedure_name => 'IEX_PROMISES_BATCH_PUB.REOPEN_PROMISES',
3184 -- P_MESSAGE => 'Failed to reopen promises.' );
3185 -- End - Andre Araujo - 09/30/2004- Remove obsolete logging
3186 */
3187 END;
3188 begin
3189 PG_DEBUG := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
3190 G_LOGIN_ID := FND_GLOBAL.Conc_Login_Id;
3191 G_PROGRAM_ID := FND_GLOBAL.Conc_Program_Id;
3192 G_USER_ID := FND_GLOBAL.User_Id;
3193 G_REQUEST_ID := FND_GLOBAL.Conc_Request_Id;
3194 END;