[Home] [Help]
PACKAGE BODY: APPS.IEX_DEL_SUB_PVT
Source
1 PACKAGE BODY IEX_DEL_SUB_PVT AS
2 /* $Header: iexpdlsb.pls 120.1 2006/05/30 17:27:03 scherkas noship $ */
3
4 l_api_version_number CONSTANT NUMBER := 1.0;
5
6 /* ------------------------------------------------------------------------------
7 PROCEDURE ADD_REC
8 ------------------------------------------------------------------------------ */
9 -- PG_DEBUG NUMBER(2) := TO_NUMBER(NVL(FND_PROFILE.value('IEX_DEBUG_LEVEL'), '20'));
10 PG_DEBUG NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
11
12 PROCEDURE Add_rec (p_api_version IN NUMBER ,
13 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
14 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
15 p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
16 x_return_status OUT NOCOPY VARCHAR2 ,
17 x_msg_count OUT NOCOPY NUMBER ,
18 x_msg_data OUT NOCOPY VARCHAR2 ,
19 p_source_module IN VARCHAR2 ,
20 p_id_tbl IN IEX_UTILITIES.t_numbers,
21 p_del_id IN Number ,
22 p_object_code IN Varchar2 ,
23 p_object_id IN IEX_DEL_ASSETS.object_id%TYPE )
24 IS
25
26 l_api_name varchar2(50) := 'Add_Rec';
27 nCount NUMBER;
28
29 BEGIN
30
31 -- Standard Start of API savepoint
32 SAVEPOINT Add_Rec;
33
34 -- Standard call to check for call compatibility.
35 IF NOT FND_API.Compatible_API_Call(l_api_version_number,
36 p_api_version,
37 l_api_name,
38 G_PKG_NAME)
39 THEN
40 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
41 END IF;
42
43 -- Initialize message list if p_init_msg_list is set to TRUE.
44 IF FND_API.to_Boolean(p_init_msg_list)
45 THEN
46 FND_MSG_PUB.initialize;
47 END IF;
48
49 -- Initialize API return status to SUCCESS
50 x_return_status := FND_API.G_RET_STS_SUCCESS;
51
52 --
53 -- Beginning of API body
54 --
55
56 nCount := p_id_tbl.Count;
57
58 -- Delinquency Assets Form
59 if p_source_module = 'IEXDLAST' then
60 FOR i IN 1..nCount
61 LOOP
62 BEGIN
63 Update IEX_DEL_ASSETS
64 SET ACTIVE_YN = 'Y'
65 where asset_id = p_id_tbl(i)
66 AND delinquency_id = p_del_id
67 AND object_id = p_object_id
68 AND object_code = p_object_code;
69
70 IF sql%NOTFOUND THEN
71 BEGIN
72 INSERT INTO IEX_DEL_ASSETS
73 (DEL_ASSET_ID ,
74 LAST_UPDATE_DATE ,
75 LAST_UPDATED_BY ,
76 LAST_UPDATE_LOGIN ,
77 CREATION_DATE ,
78 CREATED_BY ,
79 OBJECT_VERSION_NUMBER ,
80 ASSET_ID ,
81 OBJECT_CODE ,
82 OBJECT_ID ,
83 ACTIVE_YN ,
84 DELINQUENCY_ID)
85 VALUES
86 (IEX_DEL_ASSETS_S.NEXTVAL ,
87 sysdate ,
88 FND_GLOBAL.USER_ID,
89 FND_GLOBAL.LOGIN_ID,
90 sysdate ,
91 FND_GLOBAL.USER_ID,
92 1 ,
93 p_id_tbl(i),
94 p_object_code ,
95 p_object_id ,
96 'Y' ,
97 p_del_id);
98
99 EXCEPTION
100 WHEN Others then
101 -- Error Handling for Others
102 -- IF PG_DEBUG < 10 THEN
103 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
104 IEX_DEBUG_PUB.LOGMESSAGE
105 ('Add_rec: ' || '[ ' || p_source_module ||' ] - ADD Records Exception - INSERT >>
106 ' || SQLCODE || ' >> ' || SQLERRM);
107 END IF;
108 x_return_status := FND_API.G_RET_STS_ERROR;
109 RollBack to Add_Rec;
110 END;
111 END IF;
112
113 Exception
114 WHEN OTHERS then
115 -- Error Handling for Others
116 -- IF PG_DEBUG < 10 THEN
117 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
118 IEX_DEBUG_PUB.LOGMESSAGE
119 ('Add_rec: ' || '[ ' || p_source_module ||' ] - ADD Records Exception - UPDATE >>
120 ' || SQLCODE || ' >> ' || SQLERRM);
121 END IF;
122 x_return_status := FND_API.G_RET_STS_ERROR;
123 RollBack to Add_Rec ;
124 END;
125 END LOOP;
126
127 Elsif p_source_module = 'IEXWOCNT' then
128 FOR i IN 1..nCount
129 LOOP
130 BEGIN
131 Update IEX_WRITEOFF_CONTRACTS
132 SET ACTIVE_YN = 'Y'
133 where contract_id = p_id_tbl(i)
134 AND delinquency_id = p_del_id
135 AND object_id = p_object_id
136 AND object_code = p_object_code;
137
138 IF sql%NOTFOUND THEN
139
140 BEGIN
141 INSERT INTO IEX_WRITEOFF_CONTRACTS
142 (WRITEOFF_CONTRACT_ID ,
143 LAST_UPDATE_DATE ,
144 LAST_UPDATED_BY ,
145 LAST_UPDATE_LOGIN ,
146 CREATION_DATE ,
147 CREATED_BY ,
148 OBJECT_VERSION_NUMBER ,
149 CONTRACT_ID ,
150 OBJECT_CODE ,
151 OBJECT_ID ,
152 ACTIVE_YN ,
153 DELINQUENCY_ID)
154 VALUES
155 (IEX_WRITEOFF_CONTRACTS_S.NEXTVAL,
156 sysdate ,
157 FND_GLOBAL.USER_ID,
158 FND_GLOBAL.LOGIN_ID,
159 sysdate ,
160 FND_GLOBAL.USER_ID,
161 1 ,
162 p_id_tbl(i),
163 p_object_code ,
164 p_object_id ,
165 'Y' ,
166 p_del_id);
167 EXCEPTION
168 WHEN Others then
169 -- Error Handling for Others
170 -- IF PG_DEBUG < 10 THEN
171 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
172 IEX_DEBUG_PUB.LOGMESSAGE
173 ('Add_rec: ' || '[ ' || p_source_module ||' ] - ADD Records Exception - INSERT >>
174 ' || SQLCODE || ' >> ' || SQLERRM);
175 END IF;
176 x_return_status := FND_API.G_RET_STS_ERROR;
177 RollBack to Add_rec ;
178 END;
179 END IF;
180 Exception
181 WHEN OTHERS then
182 -- Error Handling for Others
183 -- IF PG_DEBUG < 10 THEN
184 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
185 IEX_DEBUG_PUB.LOGMESSAGE
186 ('Add_rec: ' || '[ ' || p_source_module ||' ] - ADD Records Exception - UPDATE >>
187 ' || SQLCODE || ' >> ' || SQLERRM);
188 END IF;
189 x_return_status := FND_API.G_RET_STS_ERROR;
190 RollBack to Add_rec ;
191 END;
192
193 END LOOP;
194
195 Elsif p_source_module = 'IEXWOINV' then
196 FOR i IN 1..nCount
197 LOOP
198 Begin
199 Update IEX_WRITEOFF_INVOICES
200 SET ACTIVE_YN = 'Y'
201 where lease_inv_line_id = p_id_tbl(i)
202 AND delinquency_id = p_del_id
203 AND object_id = p_object_id
204 AND object_code = p_object_code;
205
206 IF sql%NOTFOUND THEN
207
208 BEGIN
209 INSERT INTO IEX_WRITEOFF_INVOICES
210 (WRITEOFF_INVOICE_ID ,
211 LAST_UPDATE_DATE ,
212 LAST_UPDATED_BY ,
213 LAST_UPDATE_LOGIN ,
214 CREATION_DATE ,
215 CREATED_BY ,
216 OBJECT_VERSION_NUMBER ,
217 LEASE_INV_LINE_ID ,
218 OBJECT_CODE ,
219 OBJECT_ID ,
220 ACTIVE_YN ,
221 DELINQUENCY_ID)
222 VALUES
223 (IEX_WRITEOFF_INVOICES_S.NEXTVAL,
224 sysdate,
225 FND_GLOBAL.USER_ID,
226 FND_GLOBAL.LOGIN_ID,
227 sysdate,
228 FND_GLOBAL.USER_ID,
229 1,
230 p_id_tbl(i),
231 p_object_code,
232 p_object_id,
233 'Y',
234 p_del_id);
235 EXCEPTION
236 WHEN Others then
237 -- Error Handling for Others
238 -- IF PG_DEBUG < 10 THEN
239 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
240 IEX_DEBUG_PUB.LOGMESSAGE
241 ('Add_rec: ' || '[ ' || p_source_module ||' ] - ADD Records Exception - INSERT >>
242 ' || SQLCODE || ' >> ' || SQLERRM);
243 END IF;
244 x_return_status := FND_API.G_RET_STS_ERROR;
245 RollBack to Add_rec;
246 END;
247 END IF;
248
249 Exception
250 WHEN OTHERS then
251 -- Error Handling for Others
252 -- IF PG_DEBUG < 10 THEN
253 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
254 IEX_DEBUG_PUB.LOGMESSAGE
255 ('Add_rec: ' || '[ ' || p_source_module ||' ] - ADD Records Exception - INSERT >>
256 ' || SQLCODE || ' >> ' || SQLERRM);
257 END IF;
258 x_return_status := FND_API.G_RET_STS_ERROR;
259 RollBack to Add_rec;
260 END;
261
262 END LOOP;
263 END IF; -- p_object_type
264
265 -- Standard check for p_commit
266 IF FND_API.to_Boolean(p_commit)
267 THEN
268 COMMIT WORK;
269 ELSE
270 ROLLBACK TO ADD_REC ;
271 END IF;
272
273 -- Debug Message
274 IEX_DEBUG_PUB.LogMessage('PUB: ' || l_api_name || ' end');
275 IEX_DEBUG_PUB.LogMessage('End time:' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
276 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
277
278 EXCEPTION
279 WHEN FND_API.G_EXC_ERROR THEN
280 as_utility_pvt.HANDLE_EXCEPTIONS(
281 P_API_NAME => L_API_NAME
282 ,P_PKG_NAME => G_PKG_NAME
283 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
284 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PUB
285 ,X_MSG_COUNT => X_MSG_COUNT
286 ,X_MSG_DATA => X_MSG_DATA
287 ,X_RETURN_STATUS => X_RETURN_STATUS);
288
289 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
290 as_utility_pvt.HANDLE_EXCEPTIONS(
291 P_API_NAME => L_API_NAME
292 ,P_PKG_NAME => G_PKG_NAME
293 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
294 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PUB
295 ,X_MSG_COUNT => X_MSG_COUNT
296 ,X_MSG_DATA => X_MSG_DATA
297 ,X_RETURN_STATUS => X_RETURN_STATUS);
298
299 WHEN OTHERS THEN
300 as_utility_pvt.HANDLE_EXCEPTIONS(
301 P_API_NAME => L_API_NAME
302 ,P_PKG_NAME => G_PKG_NAME
303 ,P_EXCEPTION_LEVEL => as_utility_pvt.G_EXC_OTHERS
304 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PUB
305 ,X_MSG_COUNT => X_MSG_COUNT
306 ,X_MSG_DATA => X_MSG_DATA
307 ,X_RETURN_STATUS => X_RETURN_STATUS);
308
309 END Add_rec ;
310
311 /* ------------------------------------------------------------------------------
312 PROCEDURE REMOVE_REC
313 ------------------------------------------------------------------------------ */
314 PROCEDURE Remove_rec(p_api_version IN NUMBER,
315 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
316 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
317 p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
318 x_return_status OUT NOCOPY VARCHAR2,
319 x_msg_count OUT NOCOPY NUMBER,
320 x_msg_data OUT NOCOPY VARCHAR2,
321 p_source_module IN VARCHAR2,
322 p_id_tbl IN IEX_UTILITIES.t_numbers)
323 IS
324
325 l_api_name varchar2(50) := 'remove_Rec';
326 nCount NUMBER;
327
328 BEGIN
329
330 -- Standard Start of API savepoint
331 SAVEPOINT remove_Rec;
332
333 -- Standard call to check for call compatibility.
334 IF NOT FND_API.Compatible_API_Call(l_api_version_number,
335 p_api_version,
336 l_api_name,
337 G_PKG_NAME)
338 THEN
339 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
340 END IF;
341
342 -- Initialize message list if p_init_msg_list is set to TRUE.
343 IF FND_API.to_Boolean(p_init_msg_list)
344 THEN
345 FND_MSG_PUB.initialize;
346 END IF;
347
348 -- Initialize API return status to SUCCESS
349 x_return_status := FND_API.G_RET_STS_SUCCESS;
350
351 nCount := p_id_tbl.count ;
352
353 -- Delinquency Assets Form
354 If p_source_module = 'IEXDLAST' then
355 FORALL cnt in 1..nCount
356 UPDATE IEX_DEL_ASSETS
357 SET ACTIVE_YN = 'N'
358 WHERE DEL_ASSET_ID = p_id_tbl(cnt) ;
359
360 Elsif p_source_module = 'IEXWOCNT' then
361 FORALL cnt in 1..nCount
362 UPDATE IEX_WRITEOFF_CONTRACTS
363 SET ACTIVE_YN = 'N'
364 WHERE WRITEOFF_CONTRACT_ID = p_id_tbl(cnt) ;
365
366 Elsif p_source_module = 'IEXWOINV' then
367 FORALL cnt in 1..nCount
368 UPDATE IEX_WRITEOFF_INVOICES
369 SET ACTIVE_YN = 'N'
370 WHERE WRITEOFF_INVOICE_ID = p_id_tbl(cnt) ;
371
372 End If ;
373
374 -- Standard check for p_commit
375 IF FND_API.to_Boolean(p_commit)
376 THEN
377 COMMIT WORK;
378 ELSE
379 ROLLBACK TO REMOVE_REC ;
380 END IF;
381
382 -- Debug Message
383 IEX_DEBUG_PUB.LogMessage('PUB: ' || l_api_name || ' end');
384 IEX_DEBUG_PUB.LogMessage('End time:' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
385 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
386
387 EXCEPTION
388 WHEN FND_API.G_EXC_ERROR THEN
389 as_utility_pvt.HANDLE_EXCEPTIONS(
390 P_API_NAME => L_API_NAME
391 ,P_PKG_NAME => G_PKG_NAME
392 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
393 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PUB
394 ,X_MSG_COUNT => X_MSG_COUNT
395 ,X_MSG_DATA => X_MSG_DATA
396 ,X_RETURN_STATUS => X_RETURN_STATUS);
397
398 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
399 as_utility_pvt.HANDLE_EXCEPTIONS(
400 P_API_NAME => L_API_NAME
401 ,P_PKG_NAME => G_PKG_NAME
402 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
403 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PUB
404 ,X_MSG_COUNT => X_MSG_COUNT
405 ,X_MSG_DATA => X_MSG_DATA
406 ,X_RETURN_STATUS => X_RETURN_STATUS);
407
408 WHEN OTHERS THEN
409 -- IF PG_DEBUG < 10 THEN
410 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
411 IEX_DEBUG_PUB.LOGMESSAGE
412 ('Remove_rec: ' || '[ ' || p_source_module ||' ] - REMOVE Records Exception >> '
413 || SQLCODE || ' >> ' || SQLERRM);
414 END IF;
415 rollback to remove_rec ;
416 as_utility_pvt.HANDLE_EXCEPTIONS(
417 P_API_NAME => L_API_NAME
418 ,P_PKG_NAME => G_PKG_NAME
419 ,P_EXCEPTION_LEVEL => as_utility_pvt.G_EXC_OTHERS
420 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PUB
421 ,X_MSG_COUNT => X_MSG_COUNT
422 ,X_MSG_DATA => X_MSG_DATA
423 ,X_RETURN_STATUS => X_RETURN_STATUS);
424
425 END REMOVE_REC ;
426
427 /* ------------------------------------------------------------------------------
428 PROCEDURE ADD_ALL_REC
429 ------------------------------------------------------------------------------ */
430 PROCEDURE Add_All_rec(p_api_version IN NUMBER ,
431 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
432 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
433 p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
434 x_return_status OUT NOCOPY VARCHAR2 ,
435 x_msg_count OUT NOCOPY NUMBER ,
436 x_msg_data OUT NOCOPY VARCHAR2 ,
437 p_source_module IN VARCHAR2 ,
438 p_del_id IN Number ,
439 p_object_code IN Varchar2 ,
440 p_object_id IN Number )
441 IS
442 l_api_name varchar2(50) := 'add_all_Rec';
443 v_id_tbl IEX_UTILITIES.T_NUMBERS ;
444 BEGIN
445
446 -- Standard Start of API savepoint
447 SAVEPOINT add_all_Rec;
448
449 -- Standard call to check for call compatibility.
450 IF NOT FND_API.Compatible_API_Call(l_api_version_number,
451 p_api_version,
452 l_api_name,
453 G_PKG_NAME)
454 THEN
455 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
456 END IF;
457
458 -- Initialize message list if p_init_msg_list is set to TRUE.
459 IF FND_API.to_Boolean(p_init_msg_list)
460 THEN
461 FND_MSG_PUB.initialize;
462 END IF;
463
464 -- Initialize API return status to SUCCESS
465 x_return_status := FND_API.G_RET_STS_SUCCESS;
466
467 -- Delinquency Assets Form
468 If p_source_module = 'IEXDLAST' then
469 -- Bulk Collect all the Asset Ids and call add lines
470 select asset_id
471 BULK COLLECT INTO v_id_tbl
472 from iex_available_assets_v ;
473 /* MODIFY */
474 -- where delinquency_id = p_del_id ;
475 -- and joins to contracts view.
476 Elsif p_source_module = 'IEXWOCNT' then
477 -- Select id from Left Side Bali Spread for WriteOff Contract
478 --select contract_id
479 -- BULK COLLECT INTO v_id_tbl
480 --from iex_available_assets_v
481 --where delinquency_id = p_del_id ;
482 Null ;
483 Elsif p_source_module = 'IEXWOINV' then
484 -- Select id from Left Side Bali Spread for WriteOff Invoice
485 --select asset_id
486 -- BULK COLLECT INTO v_id_tbl
487 --from iex_available_assets_v
488 --where delinquency_id = p_del_id ;
489 Null ;
490
491 End If ;
492
493 Add_rec (p_api_version ,
494 p_init_msg_list ,
495 p_commit ,
496 p_validation_level ,
497 x_return_status ,
498 x_msg_count ,
499 x_msg_data ,
500 p_source_module ,
501 v_id_tbl ,
502 p_del_id ,
503 p_object_code ,
504 p_object_id );
505
506 -- Debug Message
507 IEX_DEBUG_PUB.LogMessage('PUB: ' || l_api_name || ' end');
508 IEX_DEBUG_PUB.LogMessage('End time:' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
509 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
510
511 EXCEPTION
512 WHEN FND_API.G_EXC_ERROR THEN
513 as_utility_pvt.HANDLE_EXCEPTIONS(
514 P_API_NAME => L_API_NAME
515 ,P_PKG_NAME => G_PKG_NAME
516 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
517 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PUB
518 ,X_MSG_COUNT => X_MSG_COUNT
519 ,X_MSG_DATA => X_MSG_DATA
520 ,X_RETURN_STATUS => X_RETURN_STATUS);
521
522 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
523 as_utility_pvt.HANDLE_EXCEPTIONS(
524 P_API_NAME => L_API_NAME
525 ,P_PKG_NAME => G_PKG_NAME
526 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
527 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PUB
528 ,X_MSG_COUNT => X_MSG_COUNT
529 ,X_MSG_DATA => X_MSG_DATA
530 ,X_RETURN_STATUS => X_RETURN_STATUS);
531
532 WHEN OTHERS THEN
533 -- IF PG_DEBUG < 10 THEN
534 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
535 IEX_DEBUG_PUB.LOGMESSAGE
536 ('Add_All_rec: ' || '[ ' || p_source_module ||' ] - ADD ALL Records Exception >> '
537 || SQLCODE || ' >> ' || SQLERRM);
538 END IF;
539 rollback to remove_rec ;
540 as_utility_pvt.HANDLE_EXCEPTIONS(
541 P_API_NAME => L_API_NAME
542 ,P_PKG_NAME => G_PKG_NAME
543 ,P_EXCEPTION_LEVEL => as_utility_pvt.G_EXC_OTHERS
544 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PUB
545 ,X_MSG_COUNT => X_MSG_COUNT
546 ,X_MSG_DATA => X_MSG_DATA
547 ,X_RETURN_STATUS => X_RETURN_STATUS);
548
549 END ADD_ALL_REC ;
550
551 /* ------------------------------------------------------------------------------
552 PROCEDURE REMOVE_ALL_REC
553 ------------------------------------------------------------------------------ */
554 PROCEDURE Remove_All_rec(
555 p_api_version IN NUMBER ,
556 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
557 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
558 p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
559 x_return_status OUT NOCOPY VARCHAR2 ,
560 x_msg_count OUT NOCOPY NUMBER ,
561 x_msg_data OUT NOCOPY VARCHAR2 ,
562 p_source_module IN VARCHAR2 ,
563 p_del_id IN Number )
564
565 IS
566 l_api_name varchar2(50) := 'remove_all_Rec';
567 BEGIN
568
569 -- Standard Start of API savepoint
570 SAVEPOINT remove_all_Rec;
571
572 -- Standard call to check for call compatibility.
573 IF NOT FND_API.Compatible_API_Call(l_api_version_number,
574 p_api_version,
575 l_api_name,
576 G_PKG_NAME)
577 THEN
578 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
579 END IF;
580
581 -- Initialize message list if p_init_msg_list is set to TRUE.
582 IF FND_API.to_Boolean(p_init_msg_list)
583 THEN
584 FND_MSG_PUB.initialize;
585 END IF;
586
587 -- Initialize API return status to SUCCESS
588 x_return_status := FND_API.G_RET_STS_SUCCESS;
589
590
591 If p_source_module = 'IEXDLAST' then
592 UPDATE IEX_DEL_ASSETS
593 SET ACTIVE_YN = 'N'
594 WHERE DELINQUENCY_ID = p_del_id ;
595
596 Elsif p_source_module = 'IEXWOCNT' then
597 UPDATE IEX_WRITEOFF_CONTRACTS
598 SET ACTIVE_YN = 'N'
599 WHERE DELINQUENCY_ID = p_del_id ;
600
601 Elsif p_source_module = 'IEXWOINV' then
602 UPDATE IEX_WRITEOFF_INVOICES
603 SET ACTIVE_YN = 'N'
604 WHERE DELINQUENCY_ID = p_del_id ;
605
606 End IF ;
607 -- Standard check for p_commit
608 IF FND_API.to_Boolean(p_commit)
609 THEN
610 COMMIT WORK;
611 ELSE
612 ROLLBACK TO REMOVE_ALL_REC ;
613 END IF;
614
615 -- Debug Message
616 IEX_DEBUG_PUB.LogMessage('PUB: ' || l_api_name || ' end');
617 IEX_DEBUG_PUB.LogMessage('End time:' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
618 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
619
620 EXCEPTION
621 WHEN FND_API.G_EXC_ERROR THEN
622 as_utility_pvt.HANDLE_EXCEPTIONS(
623 P_API_NAME => L_API_NAME
624 ,P_PKG_NAME => G_PKG_NAME
625 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
626 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PUB
627 ,X_MSG_COUNT => X_MSG_COUNT
628 ,X_MSG_DATA => X_MSG_DATA
629 ,X_RETURN_STATUS => X_RETURN_STATUS);
630
631 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
632 as_utility_pvt.HANDLE_EXCEPTIONS(
633 P_API_NAME => L_API_NAME
634 ,P_PKG_NAME => G_PKG_NAME
635 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
636 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PUB
637 ,X_MSG_COUNT => X_MSG_COUNT
638 ,X_MSG_DATA => X_MSG_DATA
639 ,X_RETURN_STATUS => X_RETURN_STATUS);
640
641 WHEN OTHERS THEN
642 -- IF PG_DEBUG < 10 THEN
643 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
644 IEX_DEBUG_PUB.LOGMESSAGE
645 ('Remove_All_rec: ' || '[ ' || p_source_module ||' ] - REMOVE ALL Records Exception >> '
646 || SQLCODE || ' >> ' || SQLERRM);
647 END IF;
648 rollback to remove_rec ;
649 as_utility_pvt.HANDLE_EXCEPTIONS(
650 P_API_NAME => L_API_NAME
651 ,P_PKG_NAME => G_PKG_NAME
652 ,P_EXCEPTION_LEVEL => as_utility_pvt.G_EXC_OTHERS
653 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PUB
654 ,X_MSG_COUNT => X_MSG_COUNT
655 ,X_MSG_DATA => X_MSG_DATA
656 ,X_RETURN_STATUS => X_RETURN_STATUS);
657
658
659 END REMOVE_ALL_REC ;
660
661
662 PROCEDURE Start_Workflow(
663 p_api_version IN NUMBER := 1.0,
664 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
665 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
666 p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
667 x_return_status OUT NOCOPY VARCHAR2 ,
668 x_msg_count OUT NOCOPY NUMBER ,
669 x_msg_data OUT NOCOPY VARCHAR2 ,
670 p_user_id IN NUMBER ,
671 p_asset_info IN VARCHAR2 ,
672 p_asset_addl_info IN Varchar2 ,
673 p_delinquency_id IN Number )
674 IS
675 l_result VARCHAR2(10);
676 itemtype VARCHAR2(10);
677 itemkey VARCHAR2(30);
678 workflowprocess VARCHAR2(30);
679
680 l_error_msg VARCHAR2(2000);
681 l_return_status VARCHAR2(20);
682 l_msg_count NUMBER;
683 l_msg_data VARCHAR2(2000);
684 l_api_name VARCHAR2(100) := 'START_WORKFLOW';
685 l_api_version_number CONSTANT NUMBER := 1;
686
687 l_manager_name varchar2(240) ;
688 l_manager_id Number ;
689 l_user_name Varchar2(100) ;
690
691 CURSOR manager_cur
692 IS
693 SELECT b.user_id, b.user_name
694 FROM JTF_RS_RESOURCE_EXTNS a ,
695 JTF_RS_RESOURCE_EXTNS b
696 WHERE b.source_id = a.source_mgr_id
697 AND a.user_id = p_user_id ;
698
699 CURSOR owner_cur
700 IS
701 SELECT user_name
702 FROM JTF_RS_RESOURCE_EXTNS
703 WHERE user_id = p_user_id ;
704
705 BEGIN
706 -- IF PG_DEBUG < 10 THEN
707 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
708 IEX_DEBUG_PUB.LOGMESSAGE('Start_Workflow: ' || ' ');
709 END IF;
710 -- IF PG_DEBUG < 10 THEN
711 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
712 IEX_DEBUG_PUB.LOGMESSAGE('Start_Workflow: ' || '************ Start Workflow Message Log Start **********');
713 END IF;
714
715 -- IF PG_DEBUG < 10 THEN
716 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
717 IEX_DEBUG_PUB.LOGMESSAGE('Start_Workflow: ' || 'Addl Notification Info >> ' || p_asset_addl_info);
718 END IF;
719 -- IF PG_DEBUG < 10 THEN
720 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
721 IEX_DEBUG_PUB.LOGMESSAGE('Start_Workflow: ' || 'Passed User Id >> ' || Nvl(to_char(p_user_id), 'NULL'));
722 END IF;
723
724 -- Standard Start of API savepoint
725 SAVEPOINT DEL_ASSET;
726
727 -- Standard call to check for call compatibility.
728 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
729 p_api_version,
730 l_api_name,
731 G_PKG_NAME)
732 THEN
733 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
734 END IF;
735
736 -- IF PG_DEBUG < 10 THEN
737 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
738 IEX_DEBUG_PUB.LOGMESSAGE('Start_Workflow: ' || 'Stage 1');
739 END IF;
740
741 -- Initialize message list if p_init_msg_list is set to TRUE.
742 IF FND_API.to_Boolean( p_init_msg_list )
743 THEN
744 FND_MSG_PUB.initialize;
745 END IF;
746
747 -- Debug Message
748 IEX_DEBUG_PUB.LogMessage('Public API: ' || l_api_name || ' start');
749
750 IEX_DEBUG_PUB.LogMessage('Start time: ' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
751 -- IF PG_DEBUG < 10 THEN
752 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
753 IEX_DEBUG_PUB.LOGMESSAGE('Start_Workflow: ' || 'Stage 2');
754 END IF;
755 -- Initialize API return status to SUCCESS
756 x_return_status := FND_API.G_RET_STS_SUCCESS;
757
758 itemtype := 'IEXDLAST';
759 workflowprocess := 'DELINQUENCY_ASSET';
760
761 select IEX_DEL_WF_S.NEXTVAL
762 Into Itemkey
763 from dual ;
764
765 BEGIN
766
767 OPEN Owner_cur ;
768 FETCH owner_Cur
769 INTO l_user_name ;
770
771 CLOSE owner_cur ;
772
773 OPEN Manager_Cur ;
774
775 FETCH Manager_Cur
776 INTO l_manager_id,
777 l_manager_name ;
778
779 CLOSE Manager_Cur ;
780
781 if l_manager_id is NULL then
782 -- IF PG_DEBUG < 10 THEN
783 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
784 IEX_DEBUG_PUB.LOGMESSAGE('Start_Workflow: ' || 'Setting Manager Id With Owner Id');
785 END IF;
786 l_manager_id := p_user_id ;
787 l_manager_name := l_user_name ;
788 else
789 -- IF PG_DEBUG < 10 THEN
790 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
791 IEX_DEBUG_PUB.LOGMESSAGE('Start_Workflow: ' || 'Manager Id Not Null >> ' || NVL(l_manager_id, 'NULL'));
792 END IF;
793 End IF ;
794 -- IF PG_DEBUG < 10 THEN
795 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
796 IEX_DEBUG_PUB.LOGMESSAGE('Start_Workflow: ' || 'Owner Id ' || to_char(p_user_id) || ' Owner Name ' || l_user_name);
797 END IF;
798 -- IF PG_DEBUG < 10 THEN
799 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
800 IEX_DEBUG_PUB.LOGMESSAGE('Start_Workflow: ' || 'Manager Id ' || to_char(l_manager_id) || ' Manager Name ' || l_manager_name);
801 END IF;
802 Exception
803 WHEN OTHERS THEN
804 -- IF PG_DEBUG < 10 THEN
805 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
806 IEX_DEBUG_PUB.LOGMESSAGE
807 ('Start_Workflow: ' || 'Getting Manager Information - '
808 || SQLCODE || ' >> ' || SQLERRM);
809 END IF;
810 rollback to DEL_ASSET ;
811
812 x_return_status := 'F';
813 commit;
814 End ;
815
816 -- IF PG_DEBUG < 10 THEN
817 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
818 IEX_DEBUG_PUB.LOGMESSAGE('Start_Workflow: ' || 'Before Create Process');
819 END IF;
820 -- IF PG_DEBUG < 10 THEN
821 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
822 IEX_DEBUG_PUB.LOGMESSAGE('Start_Workflow: ' || 'Item Type ' || itemtype || ' Item Key ' || itemkey || ' process ' || workflowprocess);
823 END IF;
824
825 wf_engine.createprocess (
826 itemtype => itemtype,
827 itemkey => itemkey,
828 process => workflowprocess);
829 --DBMS_OUTPUT.PUT_LINE('CREATE PROCESS, itemkey = ' || itemkey);
830
831 -- User Id
832 wf_engine.setitemattrnumber(
833 itemtype => itemtype,
834 itemkey => itemkey,
835 aname => 'OWNER_ID',
836 avalue => p_user_id);
837 -- Manager Name
838 wf_engine.setitemattrtext(
839 itemtype => itemtype,
840 itemkey => itemkey,
841 aname => 'OWNER_NAME',
842 avalue => l_user_name);
843
844
845 -- Manager Id
846 wf_engine.setitemattrnumber(
847 itemtype => itemtype,
848 itemkey => itemkey,
849 aname => 'MANAGER_ID',
850 avalue => l_manager_id);
851 -- Manager Name
852 wf_engine.setitemattrtext(
853 itemtype => itemtype,
854 itemkey => itemkey,
855 aname => 'MANAGER_NAME',
856 avalue => l_manager_name);
857 -- IF PG_DEBUG < 10 THEN
858 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
859 IEX_DEBUG_PUB.LOGMESSAGE('Start_Workflow: ' || 'Stage 6');
860 END IF;
861
862 -- Passed asset Information from Form
863 wf_engine.setitemattrtext(
864 itemtype => itemtype,
865 itemkey => itemkey,
866 aname => 'ASSET_INFO',
867 avalue => P_asset_info);
868
869 -- Passed Additional asset Information from Form
870 wf_engine.setitemattrtext(
871 itemtype => itemtype,
872 itemkey => itemkey,
873 aname => 'ASSET_ADDL_INFO',
874 avalue => p_asset_addl_info);
875
876 wf_engine.startprocess(
877 itemtype => itemtype,
878 itemkey => itemkey);
879 --DBMS_OUTPUT.PUT_LINE('START PROCESS');
880 --DBMS_OUTPUT.PUT_LINE('ITEMKEY '||itemkey);
881
882 wf_engine.ItemStatus( itemtype => ItemType,
883 itemkey => ItemKey,
884 status => l_return_status,
885 result => l_result);
886
887 -- IF PG_DEBUG < 10 THEN
888 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
889 IEX_DEBUG_PUB.LOGMESSAGE('Start_Workflow: ' || 'Return Status >> ' || l_return_status);
890 END IF;
891
892 if l_return_status = 'COMPLETE' OR l_return_status = 'ACTIVE' THEN
893 x_return_status := 'S';
894 commit;
895 else
896 x_return_status := 'F';
897 end if;
898 --DBMS_OUTPUT.PUT_LINE('GET ITEM STATUS = ' || l_return_status);
899 --DBMS_OUTPUT.PUT_LINE('GET ITEM result = ' || l_result);
900
901 -- Debug Message
902 IEX_DEBUG_PUB.LogMessage('PUB: ' || l_api_name || ' end');
903 IEX_DEBUG_PUB.LogMessage('End time:'
904 || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
905
906 -- Standard call to get message count and if count is 1, get message info.
907 FND_MSG_PUB.Count_And_Get
908 ( p_count => x_msg_count,
909 p_data => x_msg_data
910 );
911
912 -- IF PG_DEBUG < 10 THEN
913 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
914 IEX_DEBUG_PUB.LOGMESSAGE('Start_Workflow: ' || '************ Start Workflow Message Log End **********');
915 END IF;
916
917 EXCEPTION
918 WHEN FND_API.G_EXC_ERROR THEN
919 as_utility_pvt.HANDLE_EXCEPTIONS(
920 P_API_NAME => L_API_NAME
921 ,P_PKG_NAME => G_PKG_NAME
922 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
923 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PUB
924 ,X_MSG_COUNT => X_MSG_COUNT
925 ,X_MSG_DATA => X_MSG_DATA
926 ,X_RETURN_STATUS => X_RETURN_STATUS);
927
928 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
929 as_utility_pvt.HANDLE_EXCEPTIONS(
930 P_API_NAME => L_API_NAME
931 ,P_PKG_NAME => G_PKG_NAME
932 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
933 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PUB
934 ,X_MSG_COUNT => X_MSG_COUNT
935 ,X_MSG_DATA => X_MSG_DATA
936 ,X_RETURN_STATUS => X_RETURN_STATUS);
937
938 WHEN OTHERS THEN
939 as_utility_pvt.HANDLE_EXCEPTIONS(
940 P_API_NAME => L_API_NAME
941 ,P_PKG_NAME => G_PKG_NAME
942 ,P_EXCEPTION_LEVEL => as_utility_pvt.G_EXC_OTHERS
943 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PUB
944 ,X_MSG_COUNT => X_MSG_COUNT
945 ,X_MSG_DATA => X_MSG_DATA
946 ,X_RETURN_STATUS => X_RETURN_STATUS);
947 ----------------------------------
948 END start_workflow;
949 END IEX_DEL_SUB_PVT ;