[Home] [Help]
PACKAGE BODY: APPS.IEX_WF_DEL_CUR_STATUS_NOTE_PUB
Source
1 PACKAGE BODY IEX_WF_DEL_CUR_STATUS_NOTE_PUB AS
2 /* $Header: iexwfcnb.pls 120.1 2006/05/30 21:18:49 scherkas noship $ */
3 /*
4 * This procedure needs to be called with an itemtype and workflow process
5 * which will launch workflow.
6 * This procedure is called to workflow to notify owner and manager
7 * if the delinquency is closed(Current)
8 */
9
10 G_PKG_NAME CONSTANT VARCHAR2(30):= 'IEX_WF_DEL_CUR_STATUS_NOTE_PUB';
11
12
13 -- WorkFlow Defaults
14 v_itemtype VARCHAR2(10) ;
15 v_itemkey VARCHAR2(30);
16 workflowprocess VARCHAR2(30);
17
18
19 l_type_id NUMBER;
20 l_owner_id NUMBER;
21 l_owner_name VARCHAR2(360);
22 l_mgr_id NUMBER;
23 l_mgr_name VARCHAR2(360);
24
25 -- Forward Declaration
26 PROCEDURE clear_table_values ;
27
28
29 -- PG_DEBUG NUMBER(2) := TO_NUMBER(NVL(FND_PROFILE.value('IEX_DEBUG_LEVEL'), '20'));
30 PG_DEBUG NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
31
32 PROCEDURE start_workflow
33 (
34 p_api_version IN NUMBER := 1.0,
35 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
36 p_commit IN VARCHAR2 := FND_API.G_FALSE,
37 p_delinquency_ids IN IEX_UTILITIES.t_del_id,
38 x_return_status OUT NOCOPY VARCHAR2,
39 x_msg_count OUT NOCOPY NUMBER,
40 x_msg_data OUT NOCOPY VARCHAR2
41 )
42 IS
43 p_delinquency_id Number ;
44 l_result VARCHAR2(10);
45
46 l_error_msg VARCHAR2(2000);
47 l_return_status VARCHAR2(20);
48 l_msg_count NUMBER;
49 l_msg_data VARCHAR2(2000);
50 l_api_name VARCHAR2(100) := 'START_WORKFLOW';
51 l_api_version_number CONSTANT NUMBER := 1.0;
52
53 v_del_notification_cur DEL_NOTIFICATION_CUR ;
54
55 v_lit_sql varchar2(2000) ;
56 v_wof_sql varchar2(2000) ;
57 v_rep_sql varchar2(2000) ;
58 v_ban_sql varchar2(2000) ;
59 v_end_sql varchar2(2000) ;
60
61 BEGIN
62 -- Standard Start of API savepoint
63 -- SAVEPOINT START_WORKFLOW;
64
65 -- Standard call to check for call compatibility.
66 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
67 p_api_version,
68 l_api_name,
69 G_PKG_NAME)
70 THEN
71 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
72 END IF;
73
74 -- Initialize message list if p_init_msg_list is set to TRUE.
75 IF FND_API.to_Boolean( p_init_msg_list )
76 THEN
77 FND_MSG_PUB.initialize;
78 END IF;
79
80
81 -- Initialize API return status to SUCCESS
82 x_return_status := FND_API.G_RET_STS_SUCCESS;
83
84 v_itemtype := 'IEXDELCN';
85 workflowprocess := 'DEL_STATUS_NOTICE';
86
87 -- Common SQL Attachment
88 v_end_sql := v_end_sql || ', iex_delinquencies_all d, JTF_RS_RESOURCE_EXTNS r ' ;
89 v_end_sql := v_end_sql || 'where d.delinquency_id = :p_delinquency_id ' ;
90 v_end_sql := v_end_sql || 'and d.delinquency_id = l.delinquency_id ' ;
91 v_end_sql := v_end_sql || 'and l.created_by = r.user_id ' ;
92
93
94 -- Litigation SQL Query
95 v_lit_sql := ' select l.created_by, l.litigation_id, r.SOURCE_NAME, ' ;
96 v_lit_sql := v_lit_sql || 'NVL(r.SOURCE_MGR_ID, l.created_by), NVL(r.SOURCE_MGR_NAME, r.SOURCE_NAME)' ;
97 v_lit_sql := v_lit_sql || ' from IEX_LITIGATIONS l ';
98
99 -- WriteOff SQL Query
100 v_wof_sql := ' select l.created_by, l.writeoff_id, r.SOURCE_NAME, ' ;
101 v_wof_sql := v_wof_sql || 'NVL(r.SOURCE_MGR_ID, l.created_by), NVL(r.SOURCE_MGR_NAME, r.SOURCE_NAME)' ;
102 v_wof_sql := v_wof_sql || ' from IEX_WRITEOFFS l ';
103
104 -- Bankruptcy SQL Query
105 v_ban_sql := ' select l.created_by, l.bankruptcy_id, r.SOURCE_NAME, ' ;
106 v_ban_sql := v_ban_sql || 'NVL(r.SOURCE_MGR_ID, l.created_by), NVL(r.SOURCE_MGR_NAME, r.SOURCE_NAME)' ;
107 v_ban_sql := v_ban_sql || ' from IEX_BANKRUPTCIES l ';
108
109 -- Repossession SQL Query
110 v_rep_sql := ' select l.created_by, l.repossession_id, r.SOURCE_NAME, ' ;
111 v_rep_sql := v_rep_sql || 'NVL(r.SOURCE_MGR_ID, l.created_by), NVL(r.SOURCE_MGR_NAME, r.SOURCE_NAME)' ;
112 v_rep_sql := v_rep_sql || ' from IEX_REPOSSESSIONS l ';
113
114 -- IF PG_DEBUG < 10 THEN
115 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
116 IEX_DEBUG_PUB.LOGMESSAGE('start_workflow: ' || 'Litigation SQL');
117 END IF;
118 -- IF PG_DEBUG < 10 THEN
119 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
120 IEX_DEBUG_PUB.LOGMESSAGE('start_workflow: ' || '--------------');
121 END IF;
122 -- IF PG_DEBUG < 10 THEN
123 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
124 IEX_DEBUG_PUB.LOGMESSAGE('start_workflow: ' || v_lit_sql || v_end_sql);
125 END IF;
126 -- IF PG_DEBUG < 10 THEN
127 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
128 IEX_DEBUG_PUB.LOGMESSAGE('start_workflow: ' || '--------------');
129 END IF;
130
131 -- IF PG_DEBUG < 10 THEN
132 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
133 IEX_DEBUG_PUB.LOGMESSAGE('start_workflow: ' || 'Repo SQL');
134 END IF;
135 -- IF PG_DEBUG < 10 THEN
136 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
137 IEX_DEBUG_PUB.LOGMESSAGE('start_workflow: ' || '--------------');
138 END IF;
139 -- IF PG_DEBUG < 10 THEN
140 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
141 IEX_DEBUG_PUB.LOGMESSAGE('start_workflow: ' || v_rep_sql || v_end_sql);
142 END IF;
143 -- IF PG_DEBUG < 10 THEN
144 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
145 IEX_DEBUG_PUB.LOGMESSAGE('start_workflow: ' || '--------------');
146 END IF;
147
148 -- IF PG_DEBUG < 10 THEN
149 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
150 IEX_DEBUG_PUB.LOGMESSAGE('start_workflow: ' || 'Bank SQL');
151 END IF;
152 -- IF PG_DEBUG < 10 THEN
153 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
154 IEX_DEBUG_PUB.LOGMESSAGE('start_workflow: ' || '--------------');
155 END IF;
156 -- IF PG_DEBUG < 10 THEN
157 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
158 IEX_DEBUG_PUB.LOGMESSAGE('start_workflow: ' || v_ban_sql || v_end_sql);
159 END IF;
160 -- IF PG_DEBUG < 10 THEN
161 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
162 IEX_DEBUG_PUB.LOGMESSAGE('start_workflow: ' || '--------------');
163 END IF;
164
165 -- IF PG_DEBUG < 10 THEN
166 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
167 IEX_DEBUG_PUB.LOGMESSAGE('start_workflow: ' || 'Woff SQL');
168 END IF;
169 -- IF PG_DEBUG < 10 THEN
170 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
171 IEX_DEBUG_PUB.LOGMESSAGE('start_workflow: ' || '--------------');
172 END IF;
173 -- IF PG_DEBUG < 10 THEN
174 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
175 IEX_DEBUG_PUB.LOGMESSAGE('start_workflow: ' || v_wof_sql || v_end_sql);
176 END IF;
177 -- IF PG_DEBUG < 10 THEN
178 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
179 IEX_DEBUG_PUB.LOGMESSAGE('start_workflow: ' || '--------------');
180 END IF;
181
182 -- INITIALIZING PL/SQL TABLES
183 p_wf_item_number_name(1) := 'DELINQUENCY_ID' ;
184 p_wf_item_number_name(p_wf_item_number_name.LAST + 1) := 'TYPE_ID' ;
185 p_wf_item_number_name(p_wf_item_number_name.LAST + 1) := 'OWNER_ID' ;
186 p_wf_item_number_name(p_wf_item_number_name.LAST + 1) := 'MANAGER_ID' ;
187
188
189 p_wf_item_text_name(1) := 'SUB_DEL_TYPE' ;
190 p_wf_item_text_name(p_wf_item_text_name.LAST + 1) := 'OWNER_NAME' ;
191 p_wf_item_text_name(p_wf_item_text_name.LAST + 1) := 'MANAGER_NAME' ;
192
193 -- IF PG_DEBUG < 10 THEN
194 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
195 IEX_DEBUG_PUB.LOGMESSAGE('start_workflow: ' || 'Delinquency Table Size ' || to_char(p_delinquency_ids.COUNT));
196 END IF;
197
198 -- Starting the WorkFlow
199
200 FOR cnt in p_delinquency_ids.FIRST..p_delinquency_ids.LAST
201 LOOP
202 p_wf_item_number_value(1) := p_delinquency_ids(cnt) ;
203 -- IF PG_DEBUG < 10 THEN
204 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
205 IEX_DEBUG_PUB.LOGMESSAGE('start_workflow: ' || ' ---------------------------------------------------');
206 END IF;
207 -- IF PG_DEBUG < 10 THEN
208 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
209 IEX_DEBUG_PUB.LOGMESSAGE('start_workflow: ' || ' Delinquency Id >> ' || to_char(p_delinquency_ids(cnt)));
210 END IF;
211
212 /* ___________________ LITIGATIONS WORKFLOW _____________________*/
213 BEGIN
214 OPEN v_del_notification_cur FOR v_lit_sql || v_end_sql
215 USING p_delinquency_ids(cnt) ;
216
217 FETCH v_del_notification_cur
218 INTO l_owner_id,
219 l_type_id,
220 l_owner_name,
221 l_mgr_id,
222 l_mgr_name ;
223 -- IF PG_DEBUG < 10 THEN
224 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
225 IEX_DEBUG_PUB.LOGMESSAGE('start_workflow: ' || ' Lit Data>> ' || to_char(l_owner_id) || ' >> ' || to_char(l_type_id) || ' >> '|| l_owner_name || ' >> ' || to_char(l_mgr_id) || ' >> ' || l_mgr_name );
226 END IF;
227
228 EXCEPTION
229 WHEN OTHERS THEN
230 -- IF PG_DEBUG < 10 THEN
231 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
232 IEX_DEBUG_PUB.LOGMESSAGE('start_workflow: ' || ' Liti >> ' || SQLCODE || ' >> ' || SQLERRM);
233 END IF;
234 END MAIN ;
235
236 IF v_del_notification_cur%FOUND THEN
237 -- IF PG_DEBUG < 10 THEN
238 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
239 IEX_DEBUG_PUB.LOGMESSAGE('start_workflow: ' || 'START LIT PROCESS Owner id '||l_owner_id);
240 END IF;
241 -- IF PG_DEBUG < 10 THEN
242 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
243 IEX_DEBUG_PUB.LOGMESSAGE('start_workflow: ' || 'START LIT PROCESS Type id '||l_type_id);
244 END IF;
245
246 select 'LIT_' || to_char(IEX_DEL_WF_S.Nextval)
247 INTO v_itemkey
248 from dual ;
249
250 -- Setting all Numeric Attributes.
251 p_wf_item_number_value(p_wf_item_number_value.LAST + 1) := l_type_id ;
252 p_wf_item_number_value(p_wf_item_number_value.LAST + 1) := l_owner_id ;
253 p_wf_item_number_value(p_wf_item_number_value.LAST + 1) := l_mgr_id ;
254
255
256 -- Setting all Text Attributes.
257 p_wf_item_text_value(1) := 'LITIGATION' ;
258 p_wf_item_text_value(p_wf_item_text_value.LAST + 1) := l_owner_name ;
259 p_wf_item_text_value(p_wf_item_text_value.LAST + 1) := l_mgr_name ;
260
261 -- IF PG_DEBUG < 10 THEN
262 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
263 IEX_DEBUG_PUB.LOGMESSAGE('start_workflow: ' || '************ LITIGATION ***************');
264 END IF;
265 -- IF PG_DEBUG < 10 THEN
266 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
267 IEX_DEBUG_PUB.LOGMESSAGE('start_workflow: ' || 'Num Name count >> ' || to_char(p_wf_item_number_name.count) || 'Num Value count >> ' || to_char(p_wf_item_number_value.count));
268 END IF;
269 -- IF PG_DEBUG < 10 THEN
270 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
271 IEX_DEBUG_PUB.LOGMESSAGE('start_workflow: ' || 'Text Name count >> ' || to_char(p_wf_item_text_name.count) || 'Text Value count >> ' || to_char(p_wf_item_text_value.count));
272 END IF;
273
274
275
276 SEND_NOTIFICATION(
277 v_itemtype ,
278 v_itemkey ,
279 p_wf_item_NUMBER_NAME ,
280 p_wf_item_NUMBER_VALUE ,
281 p_wf_item_TEXT_NAME ,
282 p_wf_item_TEXT_VALUE ,
283 l_return_status ,
284 l_result ) ;
285
286 -- IF PG_DEBUG < 10 THEN
287 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
288 IEX_DEBUG_PUB.LOGMESSAGE('start_workflow: ' || 'Lit Return Status '||v_ItemKey||' '||l_return_status);
289 END IF;
290
291
292 Clear_Table_Values ;
293
294 END IF ;
295 CLOSE v_del_notification_cur ;
296
297 /* ___________________ BANKRUPTCY WORKFLOW _____________________*/
298 BEGIN
299 OPEN v_del_notification_cur FOR v_ban_sql || v_end_sql
300 USING p_delinquency_ids(cnt) ;
301
302 FETCH v_del_notification_cur
303 INTO l_owner_id,
304 l_type_id,
305 l_owner_name,
306 l_mgr_id,
307 l_mgr_name ;
308 -- IF PG_DEBUG < 10 THEN
309 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
310 IEX_DEBUG_PUB.LOGMESSAGE('start_workflow: ' || ' BAN Data>> ' || to_char(l_owner_id) || ' >> ' || to_char(l_type_id) || ' >> '|| l_owner_name || ' >> ' || to_char(l_mgr_id) || ' >> ' || l_mgr_name );
311 END IF;
312
313 EXCEPTION
314 WHEN OTHERS THEN
315 -- IF PG_DEBUG < 10 THEN
316 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
317 IEX_DEBUG_PUB.LOGMESSAGE('start_workflow: ' || ' Bank >> ' || SQLCODE || ' >> ' || SQLERRM);
318 END IF;
319 END MAIN ;
320
321 IF v_del_notification_cur%FOUND THEN
322
323 -- IF PG_DEBUG < 10 THEN
324 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
325 IEX_DEBUG_PUB.LOGMESSAGE('start_workflow: ' || 'START BANK PROCESS Owner id '||l_owner_id);
326 END IF;
327 -- IF PG_DEBUG < 10 THEN
328 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
329 IEX_DEBUG_PUB.LOGMESSAGE('start_workflow: ' || 'START BANK PROCESS Type id '||l_type_id);
330 END IF;
331
332 select 'BAN_' || to_char(IEX_DEL_WF_S.Nextval)
333 INTO v_itemkey
334 from dual ;
335
336 -- Setting all Numeric Attributes.
337 p_wf_item_number_value(p_wf_item_number_value.LAST + 1) := l_type_id ;
338 p_wf_item_number_value(p_wf_item_number_value.LAST + 1) := l_owner_id ;
339 p_wf_item_number_value(p_wf_item_number_value.LAST + 1) := l_mgr_id ;
340
341 -- Setting all Text Attributes.
342 p_wf_item_text_value(1) := 'BANKRUPTCY' ;
343 p_wf_item_text_value(p_wf_item_text_value.LAST + 1) := l_owner_name ;
344 p_wf_item_text_value(p_wf_item_text_value.LAST + 1) := l_mgr_name ;
345
346 -- IF PG_DEBUG < 10 THEN
347 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
348 IEX_DEBUG_PUB.LOGMESSAGE('start_workflow: ' || '************ BANKRUPTCY ***************');
349 END IF;
350 -- IF PG_DEBUG < 10 THEN
351 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
352 IEX_DEBUG_PUB.LOGMESSAGE('start_workflow: ' || 'Num Name count >> ' || to_char(p_wf_item_number_name.count) || 'Num Value count >> ' || to_char(p_wf_item_number_value.count));
353 END IF;
354 -- IF PG_DEBUG < 10 THEN
355 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
356 IEX_DEBUG_PUB.LOGMESSAGE('start_workflow: ' || 'Text Name count >> ' || to_char(p_wf_item_text_name.count) || 'Text Value count >> ' || to_char(p_wf_item_text_value.count));
357 END IF;
358
359 SEND_NOTIFICATION(
360 v_itemtype ,
361 v_itemkey ,
362 p_wf_item_NUMBER_NAME ,
363 p_wf_item_NUMBER_VALUE ,
364 p_wf_item_TEXT_NAME ,
365 p_wf_item_TEXT_VALUE ,
366 l_return_status ,
367 l_result ) ;
368
369 --IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
373 Clear_Table_Values ;
370 --IEX_DEBUG_PUB.LOGMESSAGE('Bank Ret Status '||ItemKey||' '||l_return_status);
371 --END IF;
372
374 END IF ;
375 CLOSE v_del_notification_cur ;
376
377
378 /* ___________________ WRITE OFF WORKFLOW _____________________*/
379 BEGIN
380 OPEN v_del_notification_cur FOR v_wof_sql || v_end_sql
381 USING p_delinquency_ids(cnt) ;
382
383 FETCH v_del_notification_cur
384 INTO l_owner_id,
385 l_type_id,
386 l_owner_name,
387 l_mgr_id,
388 l_mgr_name ;
389 -- IF PG_DEBUG < 10 THEN
390 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
391 IEX_DEBUG_PUB.LOGMESSAGE('start_workflow: ' || ' Data>> ' || to_char(l_owner_id) || ' >> ' || to_char(l_type_id) || ' >> '|| l_owner_name || ' >> ' || to_char(l_mgr_id) || ' >> ' || l_mgr_name );
392 END IF;
393
394 EXCEPTION
395 WHEN OTHERS THEN
396 -- IF PG_DEBUG < 10 THEN
397 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
398 IEX_DEBUG_PUB.LOGMESSAGE('start_workflow: ' || ' Write Off >>' || SQLCODE || ' >> ' || SQLERRM);
399 END IF;
400 END MAIN ;
401
402 IF v_del_notification_cur%FOUND THEN
403
404 -- IF PG_DEBUG < 10 THEN
405 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
406 IEX_DEBUG_PUB.LOGMESSAGE('start_workflow: ' || 'START wof PROCESS Owner id '||l_owner_id);
407 END IF;
408 -- IF PG_DEBUG < 10 THEN
409 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
410 IEX_DEBUG_PUB.LOGMESSAGE('start_workflow: ' || 'START WOF PROCESS Type id '||l_type_id);
411 END IF;
412
413 select 'WRI_' || to_char(IEX_DEL_WF_S.Nextval)
414 INTO v_itemkey
415 from dual ;
416
417 -- Setting all Numeric Attributes.
418 p_wf_item_number_value(p_wf_item_number_value.LAST + 1) := l_type_id ;
419 p_wf_item_number_value(p_wf_item_number_value.LAST + 1) := l_owner_id ;
420 p_wf_item_number_value(p_wf_item_number_value.LAST + 1) := l_mgr_id ;
421
422 -- Setting all Text Attributes.
423 p_wf_item_text_value(1) := 'WRITEOFF' ;
424 p_wf_item_text_value(p_wf_item_text_value.LAST + 1) := l_owner_name ;
425 p_wf_item_text_value(p_wf_item_text_value.LAST + 1) := l_mgr_name ;
426
427 -- IF PG_DEBUG < 10 THEN
428 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
429 IEX_DEBUG_PUB.LOGMESSAGE('start_workflow: ' || '************ WRITE OFF ***************');
430 END IF;
431 -- IF PG_DEBUG < 10 THEN
432 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
433 IEX_DEBUG_PUB.LOGMESSAGE('start_workflow: ' || 'Num Name count >> ' || to_char(p_wf_item_number_name.count) || 'Num Value count >> ' || to_char(p_wf_item_number_value.count));
434 END IF;
435 -- IF PG_DEBUG < 10 THEN
436 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
437 IEX_DEBUG_PUB.LOGMESSAGE('start_workflow: ' || 'Text Name count >> ' || to_char(p_wf_item_text_name.count) || 'Text Value count >> ' || to_char(p_wf_item_text_value.count));
438 END IF;
439
440 SEND_NOTIFICATION(
441 v_itemtype ,
442 v_itemkey ,
443 p_wf_item_NUMBER_NAME ,
444 p_wf_item_NUMBER_VALUE ,
445 p_wf_item_TEXT_NAME ,
446 p_wf_item_TEXT_VALUE ,
447 l_return_status ,
448 l_result ) ;
449
450 --IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
451 --IEX_DEBUG_PUB.LOGMESSAGE('WRI Return Status '||ItemKey||' '||l_return_status);
452 --END IF;
453
454 Clear_Table_Values ;
455 END IF ;
456 CLOSE v_del_notification_cur ;
457
458 /* ___________________ REPOSSESSION WORKFLOW _____________________*/
459 BEGIN
460 OPEN v_del_notification_cur FOR v_rep_sql || v_end_sql
461 USING p_delinquency_ids(cnt) ;
462
463 FETCH v_del_notification_cur
464 INTO l_owner_id,
465 l_type_id,
466 l_owner_name,
467 l_mgr_id,
468 l_mgr_name ;
469 -- IF PG_DEBUG < 10 THEN
470 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
471 IEX_DEBUG_PUB.LOGMESSAGE('start_workflow: ' || ' Repo Data>> ' || to_char(l_owner_id) || ' >> ' || to_char(l_type_id) || ' >> '|| l_owner_name || ' >> ' || to_char(l_mgr_id) || ' >> ' || l_mgr_name );
472 END IF;
473
474 EXCEPTION
475 WHEN OTHERS THEN
476 -- IF PG_DEBUG < 10 THEN
477 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
478 IEX_DEBUG_PUB.LOGMESSAGE('start_workflow: ' || ' Repo >>' || SQLCODE || ' >> ' || SQLERRM);
479 END IF;
480 END MAIN ;
481
482 IF v_del_notification_cur%FOUND THEN
483
484 -- IF PG_DEBUG < 10 THEN
485 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
486 IEX_DEBUG_PUB.LOGMESSAGE('start_workflow: ' || 'START Repo PROCESS Owner id '||l_owner_id);
487 END IF;
488 -- IF PG_DEBUG < 10 THEN
489 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
493 select 'REP_' || to_char(IEX_DEL_WF_S.Nextval)
490 IEX_DEBUG_PUB.LOGMESSAGE('start_workflow: ' || 'START REPO PROCESS Type id '||l_type_id);
491 END IF;
492
494 INTO v_itemkey
495 from dual ;
496
497 -- Setting all Numeric Attributes.
498 p_wf_item_number_value(p_wf_item_number_value.LAST + 1) := l_type_id ;
499 p_wf_item_number_value(p_wf_item_number_value.LAST + 1) := l_owner_id ;
500 p_wf_item_number_value(p_wf_item_number_value.LAST + 1) := l_mgr_id ;
501
502 -- Setting all Text Attributes.
503 p_wf_item_text_value(1) := 'WRITEOFF' ;
504 p_wf_item_text_value(p_wf_item_text_value.LAST + 1) := l_owner_name ;
505 p_wf_item_text_value(p_wf_item_text_value.LAST + 1) := l_mgr_name ;
506
507 -- IF PG_DEBUG < 10 THEN
508 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
509 IEX_DEBUG_PUB.LOGMESSAGE('start_workflow: ' || '************ REPOSSESSION ***************');
510 END IF;
511 -- IF PG_DEBUG < 10 THEN
512 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
513 IEX_DEBUG_PUB.LOGMESSAGE('start_workflow: ' || 'Num Name count >> ' || to_char(p_wf_item_number_name.count) || 'Num Value count >> ' || to_char(p_wf_item_number_value.count));
514 END IF;
515 -- IF PG_DEBUG < 10 THEN
516 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
517 IEX_DEBUG_PUB.LOGMESSAGE('start_workflow: ' || 'Text Name count >> ' || to_char(p_wf_item_text_name.count) || 'Text Value count >> ' || to_char(p_wf_item_text_value.count));
518 END IF;
519
520 SEND_NOTIFICATION(
521 v_itemtype ,
522 v_itemkey ,
523 p_wf_item_NUMBER_NAME ,
524 p_wf_item_NUMBER_VALUE ,
525 p_wf_item_TEXT_NAME ,
526 p_wf_item_TEXT_VALUE ,
527 l_return_status ,
528 l_result ) ;
529
530 Clear_Table_Values ;
531 END IF ;
532 CLOSE v_del_notification_cur ;
533 END LOOP ;
534
535
536 -- Standard call to get message count and if count is 1, get message info.
537 FND_MSG_PUB.Count_And_Get
538 ( p_count => x_msg_count,
539 p_data => x_msg_data );
540
541 EXCEPTION
542 WHEN FND_API.G_EXC_ERROR THEN
543 as_utility_pvt.HANDLE_EXCEPTIONS(
544 P_API_NAME => L_API_NAME
545 ,P_PKG_NAME => G_PKG_NAME
546 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
547 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PUB
548 ,X_MSG_COUNT => X_MSG_COUNT
549 ,X_MSG_DATA => X_MSG_DATA
550 ,X_RETURN_STATUS => X_RETURN_STATUS);
551
552 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
553 as_utility_pvt.HANDLE_EXCEPTIONS(
554 P_API_NAME => L_API_NAME
555 ,P_PKG_NAME => G_PKG_NAME
556 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
557 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PUB
558 ,X_MSG_COUNT => X_MSG_COUNT
559 ,X_MSG_DATA => X_MSG_DATA
560 ,X_RETURN_STATUS => X_RETURN_STATUS);
561
562 WHEN OTHERS THEN
563 as_utility_pvt.HANDLE_EXCEPTIONS(
564 P_API_NAME => L_API_NAME
565 ,P_PKG_NAME => G_PKG_NAME
566 ,P_EXCEPTION_LEVEL => as_utility_pvt.G_EXC_OTHERS
567 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PUB
568 ,X_MSG_COUNT => X_MSG_COUNT
569 ,X_MSG_DATA => X_MSG_DATA
570 ,X_RETURN_STATUS => X_RETURN_STATUS);
571 ----------------------------------
572 END start_workflow;
573
574 /* ________________________________________________________________________
575
576 PROCEDURE SELECT_NOTICE
577 __________________________________________________________________________*/
578
579 ----------- procedure update_approval_status -----------------------------
580 PROCEDURE select_notice(itemtype IN varchar2,
581 itemkey IN varchar2,
582 actid IN number,
583 funcmode IN varchar2,
584 result OUT NOCOPY varchar2) is
585
586 l_responder VARCHAR2(100);
587 l_text_value VARCHAR2(2000);
588 l_status VARCHAR2(1);
589 l_resource_id NUMBER;
590 l_delinquency_id NUMBER;
591 l_api_name VARCHAR2(100) := 'select_notice';
592 l_errmsg_name VARCHAR2(30);
593 L_API_ERROR EXCEPTION;
594 BEGIN
595
596 if funcmode <> 'RUN' then
597 result := wf_engine.eng_null;
598 return;
599 end if;
600
601 l_resource_id := wf_engine.GetItemAttrText(
602 itemtype => itemtype,
603 itemkey => itemkey,
604 aname => 'RESOURCE_ID');
605
606
607 l_delinquency_id := wf_engine.GetItemAttrText(
608 itemtype => itemtype,
612 IF l_status = 'Y' THEN
609 itemkey => itemkey,
610 aname => 'DELINQUENCY_ID');
611
613 result := 'COMPLETE:'||'Y';
614 ELSE
615 result := 'COMPLETE:'||'N';
616 END IF;
617
618 EXCEPTION
619 WHEN L_API_ERROR then
620 WF_CORE.Raise(l_errmsg_name);
621 WHEN OTHERS THEN
622 WF_CORE.Context('IEX_WF_DEL_CUR_STATUS_NOTE_PUB', 'Select Notice',
623 itemtype, itemkey, actid, funcmode);
624 RAISE;
625 END select_notice;
626
627 PROCEDURE select_resource_info(
628 p_delinquency_id IN NUMBER) IS
629 BEGIN
630 null;
631 EXCEPTION
632 WHEN OTHERS THEN
633 WF_CORE.Context('IEX_WF_DEL_CUR_STATUS_NOTE_PUB', 'Select Notice' );
634 RAISE;
635 END select_resource_info;
636
637 /* ________________________________________________________________________
638
639 PROCEDURE SEND_NOTIFICATION
640 __________________________________________________________________________*/
641
642 PROCEDURE SEND_NOTIFICATION( p_itemtype varchar2 ,
643 p_itemkey varchar2 ,
644 p_wf_item_NUMBER_NAME wf_engine.NameTabTyp ,
645 p_wf_item_NUMBER_VALUE wf_engine.NumTabTyp ,
646 p_wf_item_TEXT_NAME wf_engine.NameTabTyp ,
647 p_wf_item_TEXT_VALUE wf_engine.TextTabTyp ,
648 l_return_status OUT NOCOPY varchar2 ,
649 l_result OUT NOCOPY varchar2 )
650 IS
651 BEGIN
652
653 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
654 IEX_DEBUG_PUB.LOGMESSAGE('Item Type >> ' || p_itemtype || ' Item Key >> ' || p_itemkey || ' Process >> ' || workflowprocess) ;
655 END IF;
656
657 wf_engine.createprocess (
658 itemtype => p_itemtype,
659 itemkey => p_itemkey,
660 process => workflowprocess);
661
662
663 WF_ENGINE.SetItemAttrNumberArray(
664 itemtype => p_itemtype,
665 itemkey => p_itemkey,
666 aname => p_wf_item_number_name,
667 avalue => p_wf_item_number_value);
668
669 WF_ENGINE.SetItemAttrTextArray(
670 itemtype => p_itemtype,
671 itemkey => p_itemkey,
672 aname => p_wf_item_text_name,
673 avalue => p_wf_item_text_value);
674
675 wf_engine.startprocess( itemtype => p_itemtype,
676 itemkey => p_itemkey);
677 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
678 IEX_DEBUG_PUB.LOGMESSAGE('Send Notification Before Item Status');
679 END IF;
680
681 wf_engine.ItemStatus( itemtype => p_ItemType,
682 itemkey => p_ItemKey,
683 status => l_return_status,
684 result => l_result);
685 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
686 IEX_DEBUG_PUB.LOGMESSAGE('Send Notification Status >> ' || l_return_status);
687 IEX_DEBUG_PUB.LOGMESSAGE('Result Status >> ' || l_result);
688 END IF;
689
690 EXCEPTION
691 WHEN OTHERS then
692 -- Raise the Error and Return Error Status Back.
693 -- Null for now
694 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
695 IEX_DEBUG_PUB.LOGMESSAGE(SQLCODE || ' >> ' || SQLERRM) ;
696 END IF;
697 END ;
698
699 /* ________________________________________________________________________
700
701 PROCEDURE CLEAR_TABLE_VALUES
702 __________________________________________________________________________*/
703 -- Helper Procedure to Clear the Workflow PL/SQL Tables.
704 PROCEDURE clear_table_values
705 IS
706 BEGIN
707 p_wf_item_NUMBER_VALUE.DELETE(2, p_wf_item_NUMBER_NAME.LAST) ;
708 p_wf_item_TEXT_VALUE.DELETE(p_wf_item_NUMBER_NAME.FIRST, p_wf_item_NUMBER_NAME.LAST) ;
709
710 l_owner_id := NULL ;
711 l_type_id := NULL ;
712 l_owner_name := NULL ;
713 l_mgr_id := NULL ;
714 l_mgr_name := NULL ;
715 END ;
716
717
718 /* ________________________________________________________________________
719
720 PROCEDURE MAIN
721
722 PROCEDURE MAIN
723 IS
724 ld_del_tbl IEX_UTILITIES.t_del_id ;
725 ld_api_version Number := 1.0 ;
726 ld_init_mesg_list Varchar2(1) := 'T' ;
727 ld_commit Varchar2(1) := 'F' ;
728 ld_validation_level Number := 100 ;
729
730 ld_return_status VARCHAR2(10) := 'S';
731 ld_msg_count NUMBER := 0 ;
732 ld_msg_data VARCHAR2(4000) default NULL;
733
734 Begin
735 -- Populate with new Values
736 SELECT DISTINCT DELINQUENCY_ID
737 BULK COLLECT INTO ld_del_tbl
738 From IEX_DEL_CHILDREN ;
739 start_workflow
740 ( ld_api_version,
741 ld_init_mesg_list,
742 ld_commit ,
743 ld_del_tbl ,
744 ld_return_status ,
745 ld_msg_count ,
746 ld_msg_data ) ;
747
748 EXCEPTION
749 WHEN OTHERS THEN
750 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
751 IEX_DEBUG_PUB.LOGMESSAGE(SQLCODE || ' >> ' || SQLERRM) ;
752 END IF;
753 END MAIN ;
754 __________________________________________________________________________*/
755
756 END IEX_WF_DEL_CUR_STATUS_NOTE_PUB;