[Home] [Help]
PACKAGE BODY: APPS.AS_SALES_METH_WF
Source
1 PACKAGE BODY as_sales_meth_wf AS
2 /* $Header: asxsmtwb.pls 115.38 2003/12/22 13:22:00 sumahali ship $ */
3 g_pkg_name varchar2(100);
4 g_notes varchar2(4000);
5
6 x_task_details_tbl jtf_task_inst_templates_pub.task_details_tbl;
7 ------------------------------------------------------------------------------------------
8 ----------------------------- Private Portion --------------------------------------------
9 ------------------------------------------------------------------------------------------
10 -- We use the following private utility procedures
11 --
12 ------------------------------------------------------------------------------------------
13 PROCEDURE Add_Error_Message
14 (
15 p_api_name IN VARCHAR2,
16 p_error_msg IN VARCHAR2
17 );
18 PROCEDURE Print_Message
19 (
20 p_error_msg IN VARCHAR2
21 );
22 -- Utility procedure to get the last error message
23 PROCEDURE Get_Error_Message
24 (
25 x_msg_data OUT NOCOPY VARCHAR2
26 ) ;
27 --
28 -- Start of comments
29 -- API name : Add_Error_Message
30 -- Type : Private
31 --
32 PROCEDURE Add_Error_Message
33 (
34 p_api_name IN VARCHAR2,
35 p_error_msg IN VARCHAR2
36 ) IS
37 BEGIN
38 -- To Be Developed.
39 PRINT_MESSAGE('p_api_name = ' || p_api_name);
40 PRINT_MESSAGE('p_error_msg = ' || p_error_msg);
41 END Add_Error_Message;
42 PROCEDURE Print_Message
43 (
44 p_error_msg IN VARCHAR2
45 ) IS
46 BEGIN
47 NULL;
48 -- Uncomment the line below for debug messages.
49 --dbms_output.put_line('p_debug_msg = ' || p_error_msg);
50 END Print_Message;
51 PROCEDURE Get_Error_Message
52 (
53 x_msg_data OUT NOCOPY VARCHAR2
54 ) IS
55 l_count NUMBER := 0;
56 l_msg_index_out NUMBER := 0;
57 j NUMBER;
58 BEGIN
59 x_msg_data := NULL;
60 l_count := FND_MSG_PUB.Count_Msg;
61 IF l_count > 0 THEN
62 FND_MSG_PUB.Get(p_msg_index => l_count,
63 p_encoded => FND_API.G_FALSE,
64 p_data => x_msg_data,
65 p_msg_index_out => l_msg_index_out);
66 END IF;
67 END Get_Error_Message;
68 --------------------------------------------------------------------------------------------
69 /*
70 * This procedure needs to be called with an itemtype and workflow process
71 which'll launch workflow .Start Methodology will call workflow based on Meth_flag in methodology base table*/
72 PROCEDURE start_methodology (p_source_object_type_code IN VARCHAR2,
73 p_source_object_id IN NUMBER,
74 p_source_object_name IN VARCHAR2,
75 p_owner_id IN NUMBER,
76 p_owner_type_code IN VARCHAR2,
77 p_object_type_code IN VARCHAR2,
78 p_current_stage_id IN NUMBER,
79 p_next_stage_id IN NUMBER,
80 p_template_group_id IN VARCHAR2,
81 item_type IN VARCHAR2,
82 workflow_process IN VARCHAR2,
83 x_return_status OUT NOCOPY VARCHAR2 ,
84 x_msg_count OUT NOCOPY NUMBER,
85 x_msg_data OUT NOCOPY VARCHAR2,
86 x_warning_message OUT NOCOPY VARCHAR2 ) IS
87 l_template_group_id NUMBER;
88 l_sales_stage_id NUMBER;
89 l_methodology_id NUMBER;
90 l_count NUMBER;
91 l_meth_flag VARCHAR2(10);
92 CURSOR c_profile IS
93 SELECT b.profile_option_value
94 FROM fnd_profile_options a, fnd_profile_option_values b
95 WHERE a.profile_option_id = b.profile_option_id
96 AND a.profile_option_name = 'AS_SM_CREATE_TASKS'
97 AND b.application_id =279;
98 CURSOR c_resource IS
99 SELECT decode(category,'EMPLOYEE','RS_EMPLOYEE','PARTNER','RS_PARTNER','PARTY','RS_PARTY')
100 FROM jtf_rs_resource_extns
101 WHERE resource_id = p_owner_id ;
102 CURSOR c_meth IS
103 SELECT b.autocreatetask_flag,b.sales_methodology_id
104 FROM as_leads_all a,as_sales_methodology_b b
105 WHERE a.lead_id = p_source_object_id
106 AND a.sales_methodology_id = b.sales_methodology_id;
107 CURSOR c_stage IS
108 SELECT a.sales_stage_id,task_template_group_id
109 FROM as_sales_meth_stage_map a
110 WHERE a.sales_methodology_id= l_methodology_id
111 ORDER BY stage_sequence;
112 CURSOR c_task IS
113 SELECT count(a.task_id) FROM as_sales_meth_task_map a
114 WHERE a.object_type_code = p_source_object_type_code
115 AND a.object_id = p_source_object_id
116 AND a.sales_stage_id = l_sales_stage_id
117 AND a.sales_methodology_id = l_methodology_id;
118 l_profile_value VARCHAR2(50);
119 l_result VARCHAR2(10);
120 itemtype VARCHAR2(10) ;
121 itemkey VARCHAR2(30);
122 workflowprocess VARCHAR2(30);
123 test VARCHAR2(100);
124 l_error_msg VARCHAR2(2000);
125 l_return_status VARCHAR2(20);
126 l_msg_count NUMBER;
127 l_msg_data VARCHAR2(2000);
128 l_api_name VARCHAR2(100) := 'AS_SALES_METH_WF';
129 l_category VARCHAR2(100);
130 BEGIN
131 --------------------------------------------------------
132 IF p_template_group_id IS NULL THEN
133 l_error_msg:='Template group id must not be null';
134 fnd_message.set_name('AS','AS_INVALID_TEMPLATE_ID');
135 fnd_msg_pub.add;
136 RAISE fnd_api.g_exc_unexpected_error;
137 END IF;
138 IF p_owner_id IS NULL THEN
139 l_error_msg:='Owner id must not be null';
140 fnd_message.set_name('AS','AS_INVALID_OWNER_ID');
141 fnd_msg_pub.add;
142 RAISE fnd_api.g_exc_unexpected_error;
143 END IF;
144 /*IF p_owner_type_code IS NULL THEN
145 l_error_msg:='Owner Type Code must not be null';
146 fnd_msg_pub.add_exc_msg('AS_SALES_METH','START_METHODOLOGY',l_error_msg);
147 fnd_msg_pub.add;
148 RAISE fnd_api.g_exc_unexpected_error;
149 END IF;*/
150 IF p_source_object_id IS NULL THEN
151 l_error_msg:='Source Object id must not be null';
152 fnd_message.set_name('AS','AS_INVALID_OBJECT_ID');
153 fnd_msg_pub.add;
154 RAISE fnd_api.g_exc_unexpected_error;
155 END IF;
156 IF p_source_object_type_code IS NULL THEN
157 l_error_msg:='Source object type code must not be null';
158 PRINT_MESSAGE('p_error_msg = ' || l_error_msg);
159 fnd_message.set_name('AS','AS_INVALID_OBJECT_TYPE');
160 fnd_msg_pub.add;
161 RAISE fnd_api.g_exc_unexpected_error;
162 END IF;
163 IF p_source_object_name IS NULL THEN
164 l_error_msg:='Source object name must not be null';
165 PRINT_MESSAGE('p_error_msg = ' || l_error_msg);
166 fnd_message.set_name('AS','AS_INVALID_OBJECT_NAME');
167 fnd_msg_pub.add;
168 RAISE fnd_api.g_exc_unexpected_error;
169 END IF;
170 /*IF p_current_stage_id IS NULL THEN
171 l_error_msg:='Current Stage id must not be null';
172 print_message('p_error_msg = ' || l_error_msg);
173 fnd_msg_pub.add_exc_msg('AS_SALES_METH','START_METHODOLOGY',l_error_msg);
174 fnd_msg_pub.add;
175 RAISE fnd_api.g_exc_unexpected_error;
176 END IF;*/
177 IF p_next_stage_id IS NULL THEN
178 l_error_msg:='Next Stage id must not be null';
179 print_message('p_error_msg = ' || l_error_msg);
180 fnd_message.set_name('AS','AS_INVALID_NEXT_STAGE');
181 fnd_msg_pub.add;
182 RAISE fnd_api.g_exc_unexpected_error;
183 END IF;
184 ----------------------------------------------------------
185 OPEN c_profile;
186 FETCH c_profile INTO l_profile_value;
187 IF (c_profile%NOTFOUND) THEN
188 CLOSE c_profile;
189 l_error_msg:='Required Profile not found';
190 PRINT_MESSAGE('p_error_msg = ' || l_error_msg);
191 fnd_message.set_name('AS','AS_PROFILE_NOT_FOUND');
192 fnd_msg_pub.add;
193 RAISE fnd_api.g_exc_unexpected_error;
194 END IF;
195 CLOSE c_profile;
196 IF l_profile_value = 'Y' THEN
197 --------------------------------
198 OPEN c_resource;
199 FETCH c_resource INTO l_category;
200 PRINT_MESSAGE('category:'||l_category);
201 IF (c_resource%NOTFOUND ) THEN
202 CLOSE c_resource;
203 l_error_msg := 'Category for resource not found in jtf_rs_resource_extns table';
204 PRINT_MESSAGE('p_error_msg = ' || l_error_msg);
205 fnd_message.set_name('AS','AS_INVALID_RESOURCE');
206 fnd_msg_pub.add;
207 RAISE fnd_api.g_exc_unexpected_error;
208 END IF;
209 CLOSE c_resource;
210 -------------------------------
211 workflowprocess :=workflow_process;
212 itemtype := item_type;
213 print_message(test);
214 SELECT TO_CHAR(AS_SALES_METHODOLOGY_WF_S.NEXTVAL) INTO itemkey FROM dual;
215 IF (itemtype IS NOT NULL) AND (itemkey IS NOT NULL) THEN
216 wf_engine.createprocess ( itemtype => itemtype,
217 itemkey => itemkey,
218 process => workflowprocess);
219 wf_engine.setitemattrnumber( itemtype => itemtype,
220 itemkey => itemkey,
221 aname => 'OWNER_ID',
222 avalue => p_owner_id);
223 wf_engine.setitemattrtext( itemtype => itemtype,
224 itemkey => itemkey,
225 aname => 'OWNER_TYPE_CODE',
226 avalue => l_category);
227 wf_engine.setitemattrnumber( itemtype => itemtype,
228 itemkey => itemkey,
229 aname => 'SOURCE_OBJECT_ID',
230 avalue => p_source_object_id);
231 wf_engine.setitemattrtext( itemtype => itemtype,
232 itemkey => itemkey,
233 aname => 'SOURCE_OBJECT_TYPE_CODE',
234 avalue => p_source_object_type_code);
235 wf_engine.setitemattrtext( itemtype => itemtype,
236 itemkey => itemkey,
237 aname => 'SOURCE_OBJECT_NAME',
238 avalue => p_source_object_name);
239 wf_engine.setitemattrtext( itemtype => itemtype,
240 itemkey => itemkey,
241 aname => 'OBJECT_TYPE_CODE',
242 avalue => p_object_type_code);
243 wf_engine.setitemattrnumber( itemtype => itemtype,
244 itemkey => itemkey,
245 aname => 'CURRENT_STAGE_ID',
246 avalue => p_current_stage_id);
247 wf_engine.setitemattrnumber( itemtype => itemtype,
248 itemkey => itemkey,
249 aname => 'NEXT_STAGE_ID',
250 avalue => p_next_stage_id);
251 wf_engine.setitemattrtext( itemtype => itemtype,
252 itemkey => itemkey,
253 aname => 'TASK_TEMPLATE_GROUP_ID',
254 avalue => p_template_group_id);
255 --------------------------------------------------------------
256 OPEN c_meth;
257 LOOP
258 print_message('Inside meth cursor loop');
259 FETCH c_meth INTO l_meth_flag,l_methodology_id;
260 print_message('Inside methodology id'||l_methodology_id);
261 EXIT WHEN c_meth%NOTFOUND;
262 IF l_meth_flag = 'Y' THEN
263 print_message('Meth flag'||l_meth_flag);
264 OPEN c_stage;
265 LOOP
266 FETCH c_stage INTO l_sales_stage_id,l_template_group_id;
267 EXIT WHEN c_stage%NOTFOUND;
268 OPEN c_task;
269 LOOP
270 FETCH c_task INTO l_count;
271 EXIT WHEN c_task%NOTFOUND;
272 print_message('Inside Task cursor loop =='||l_count);
273 IF l_count >= 1 THEN
274 print_message('Inside task count>=1');
275 x_return_status := 'S' ;
276 ELSE
277 PRINT_MESSAGE('sales stage id in count=='||l_sales_stage_id);
278 PRINT_MESSAGE('Task template group id in count=='||l_template_group_id);
279 wf_engine.setitemattrnumber( itemtype => itemtype,
280 itemkey => itemkey,
281 aname => 'NEXT_STAGE_ID',
282 avalue => l_sales_stage_id);
283 wf_engine.setitemattrtext( itemtype => itemtype,
284 itemkey => itemkey,
285 aname => 'TASK_TEMPLATE_GROUP_ID',
286 avalue => l_template_group_id);
287 print_message('Inside task count<1');
288 wf_engine.startprocess( itemtype => itemtype,
289 itemkey => itemkey);
290 wf_engine.ItemStatus ( itemtype => ItemType,
291 itemkey => ItemKey,
292 status => l_return_status,
293 result => l_result);
294 print_message('Result after workflow=in count else='||l_result);
295 IF l_result = 'SUCCESS' AND l_return_status ='COMPLETE' THEN
296 x_return_status := 'S' ;
297 ELSE
298 x_return_status := 'F';
299 END IF ;
300 END IF;
301 END LOOP;
302 CLOSE c_task;
303 END LOOP;
304 CLOSE c_stage;
305 ELSE
306 ----------------------------------------------------------
307 print_message('Meth flag is not equal to yes');
308 wf_engine.startprocess( itemtype => itemtype,
309 itemkey => itemkey);
310 wf_engine.ItemStatus ( itemtype => ItemType,
311 itemkey => ItemKey,
312 status => l_return_status,
313 result => l_result);
314 IF l_result = 'SUCCESS' AND l_return_status ='COMPLETE' THEN
315 x_return_status := 'S' ;
316 ELSE
317 x_return_status := 'F';
318 END IF ;
319 END IF;
320 END LOOP;
321 CLOSE c_meth;
322 ELSE
323 l_error_msg:='Item Type OR Item Key IS not null';
324 fnd_message.set_name('AS','AS_INVALID_ITEMTYPE');
325 fnd_msg_pub.add;
326 RAISE fnd_api.g_exc_unexpected_error;
327 x_return_status := 'U';
328 END IF;
329 ---------------------------------------------------------------
330 ELSE
331 l_error_msg:='Invalid Profile value';
332 PRINT_MESSAGE('p_error_msg = ' || l_error_msg);
333 fnd_message.set_name('AS','AS_INVALID_PROFILE');
334 fnd_msg_pub.add;
335 RAISE fnd_api.g_exc_unexpected_error;
336 x_return_status := 'U';
337 END IF;
338 x_warning_message := wf_engine.getitemattrtext( itemtype => itemtype,
339 itemkey => itemkey,
340 aname => 'WARNING_MESSAGE');
341
342 print_message(' Item Status->'||' '||x_return_status||'item status result->'||' '||l_result);
343 EXCEPTION
344 ----------------------------------
345 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
346 --ROLLBACK TO Start_Request;
347 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
348 Add_Error_Message (l_api_name, l_Error_Msg);
349 -- Standard call to get message count and if count=1, get the message
350 FND_MSG_PUB.count_and_get(
351 p_encoded => FND_API.g_false,
352 p_count => x_msg_count,
353 p_data => x_msg_data
354 );
355 Get_Error_Message(l_msg_data);
356 WHEN FND_API.G_EXC_ERROR THEN
357 --ROLLBACK TO Start_Request;
358 x_return_status := FND_API.G_RET_STS_ERROR;
359 Add_Error_Message (l_api_name, l_Error_Msg);
360 -- Standard call to get message count and if count=1, get the message
361 FND_MSG_PUB.count_and_get(
362 p_encoded => FND_API.g_false,
363 p_count => x_msg_count,
364 p_data => x_msg_data
365 );
366 Get_Error_Message(l_msg_data);
367 WHEN OTHERS THEN
368 --ROLLBACK TO Start_Request;
369 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
370 Add_Error_Message (l_api_name, SQLERRM);
371 IF FND_MSG_PUB.Check_Msg_Level
372 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
373 THEN
374 FND_MSG_PUB.Add_Exc_Msg
375 (G_PKG_NAME, l_api_name,sqlerrm);
376 END IF;
377 -- Standard call to get message count and if count=1, get the message
378 FND_MSG_PUB.count_and_get(
379 p_encoded => FND_API.g_false,
380 p_count => x_msg_count,
381 p_data => x_msg_data
382 );
383 Get_Error_Message(l_msg_data);
384 ----------------------------------
385 END start_methodology;
389 *
386 /**************************************************************************
387 * PROCEDURE: check_task_for_current
388 * DESCRIPTION: This procedure checks for any mandatory tasks for current stage
390 * It returns 'N' if no tasks exist
391 * Otherwise 'it returns 'Y'
392 *
393 **********************************************************************/
394 PROCEDURE check_task_for_current ( itemtype IN VARCHAR2,
395 itemkey IN VARCHAR2,
396 actid IN NUMBER,
397 funcmode IN VARCHAR2,
398 result OUT NOCOPY VARCHAR2 ) IS
399 l_return_status VARCHAR2(10);
400 l_source_object_type_code VARCHAR2(100) ;
401 l_source_object_name VARCHAR2(100);
402 l_source_object_id NUMBER;
403 l_object_type_code VARCHAR2(100) ;
404 l_object_id NUMBER ;
405 l_task_id NUMBER;
406 l_task_name VARCHAR2(100);
407 l_task_number NUMBER;
408 l_meth_note_type VARCHAR2(100);
409 n NUMBER :=0;
410 CURSOR c_current_stage IS SELECT a.task_id FROM jtf_tasks_b a,as_sales_meth_task_map b,
411 jtf_task_statuses_b c
412 WHERE a.task_id = b.task_id
413 AND a.source_object_type_code = l_source_object_type_code
414 AND a.source_object_id = l_source_object_id
415 AND b.sales_stage_id = l_object_id
416 AND a.task_status_id = c.task_status_id
417 AND a.restrict_closure_flag ='Y'
418 AND (c.closed_flag IS NULL OR c.closed_flag <> 'Y');
419 c_current_stage_rec c_current_stage%ROWTYPE;
420 BEGIN
421 -- initializing return status
422 l_return_status := 'U';
423 print_message('current func mode'||funcmode);
424 IF funcmode = 'RUN' THEN
425 l_source_object_type_code := wf_engine.getitemattrtext(itemtype => itemtype,
426 itemkey => itemkey,
427 aname => 'SOURCE_OBJECT_TYPE_CODE');
428 l_source_object_id := wf_engine.getitemattrnumber(itemtype => itemtype,
429 itemkey => itemkey,
430 aname => 'SOURCE_OBJECT_ID');
431 l_source_object_name := wf_engine.getitemattrtext (itemtype => itemtype,
432 itemkey => itemkey,
433 aname => 'SOURCE_OBJECT_NAME');
434 l_object_type_code := wf_engine.getitemattrtext (itemtype => itemtype,
435 itemkey => itemkey,
436 aname => 'OBJECT_TYPE_CODE');
437 l_object_id := wf_engine.getitemattrnumber(itemtype => itemtype,
438 itemkey => itemkey,
439 aname => 'CURRENT_STAGE_ID');
440 print_message('--------------------------------------------------');
441 print_message('SOURCE_OBJECT_CODE ='||l_source_object_type_code);
442 print_message('SOURCE_OBJECT_ID ='||l_source_object_id);
443 print_message('SOURCE_OBJECT_NAME ='||l_source_object_name);
444 print_message('OBJECT_TYPE_CODE ='||l_object_type_code);
445 print_message('CURRENT_STAGE_ID ='||l_object_id);
446 FOR c_current_stage_rec IN c_current_stage
447 LOOP
448 n := n+1;
449 g_task_tab(n) := c_current_stage_rec.task_id;
450 l_return_status:='Y';
451 -- create note comes here
452 END LOOP;
453 l_meth_note_type := 'CURRENT_STAGE';
454 wf_engine.setitemattrtext ( itemtype => itemtype,
455 itemkey => itemkey,
456 aname => 'METH_NOTE_TYPE',
457 avalue => l_meth_note_type);
458 IF l_return_status ='Y' THEN
459 result:='COMPLETE:Y';
460 ELSE
461 result:='COMPLETE:N';
462 END IF;
463 print_message('result after current stage = ' ||result);
464 END IF;
465 EXCEPTION
466 WHEN OTHERS THEN
467 wf_core.context('SAL_MET3','Check Mandatory for Current',itemtype, itemkey, to_char(actid), funcmode);
468 RAISE;
469 END Check_task_for_current;
470 /**************************************************************************
471 * PROCEDURE: check_task_for_next
472 * DESCRIPTION: This procedure checks for any tasks created for next stage
473 *
474 * It returns 'N' if no tasks exist
475 * Otherwise it returns 'Y'
476 *
477 **********************************************************************/
478 PROCEDURE check_task_exist_for_next ( itemtype IN VARCHAR2,
479 itemkey IN VARCHAR2,
480 actid IN NUMBER,
481 funcmode IN VARCHAR2,
482 result OUT NOCOPY VARCHAR2 ) IS
483 l_source_object_type_code VARCHAR2(100) ;
484 l_source_object_id NUMBER ;
485 l_source_object_name VARCHAR2(100);
486 l_object_type_code VARCHAR2(100) ;
487 l_object_id NUMBER;
488 l_meth_note_type VARCHAR2(100);
489 l_task_id NUMBER;
490 l_task_name VARCHAR2(100);
491 l_task_number NUMBER;
492 l_name VARCHAR2(10);
493 l_return_status VARCHAR2(10);
494 l_long_task_id VARCHAR2(4000);
495 n NUMBER:= 0;
496 CURSOR c_next_stage IS SELECT a.task_id FROM as_sales_meth_task_map a
497 WHERE a.object_type_code = l_source_object_type_code
498 AND a.object_id = l_source_object_id
499 AND a.sales_stage_id = l_object_id ;
500 c_next_stage_rec c_next_stage%ROWTYPE;
501 BEGIN
502 l_return_status:='U';
503 IF funcmode = 'RUN' THEN
507 l_source_object_id:= wf_engine.getitemattrnumber( itemtype => itemtype,
504 l_source_object_type_code := wf_engine.getitemattrtext( itemtype => itemtype,
505 itemkey => itemkey,
506 aname => 'SOURCE_OBJECT_TYPE_CODE');
508 itemkey => itemkey,
509 aname => 'SOURCE_OBJECT_ID');
510 l_source_object_name:= wf_engine.getitemattrtext( itemtype => itemtype,
511 itemkey => itemkey,
512 aname => 'SOURCE_OBJECT_NAME');
513 l_object_type_code:= wf_engine.getitemattrtext( itemtype => itemtype,
514 itemkey => itemkey,
515 aname => 'OBJECT_TYPE_CODE');
516 l_object_id:=wf_engine.getitemattrnumber( itemtype => itemtype,
517 itemkey => itemkey,
518 aname => 'NEXT_STAGE_ID');
519 print_message('NEXT_STAGE_ID= '||l_object_id);
520 FOR c_next_stage_rec IN c_next_stage
521 LOOP
522 print_message('Task id in next stage = '||c_next_stage_rec.task_id);
523 n := n+1;
524 g_task_tab(n) := c_next_stage_rec.task_id;
525 l_return_status:='Y';
526 END LOOP;
527 l_meth_note_type := 'NEXT_STAGE';
528 wf_engine.setitemattrtext( itemtype => itemtype,
529 itemkey => itemkey,
530 aname => 'METH_NOTE_TYPE',
531 avalue => l_meth_note_type);
532 IF l_return_status = 'Y' AND nvl(fnd_profile.value('AS_SM_RECREATE_TASKS'), 'N') <> 'Y' THEN
533 result:='COMPLETE:Y';
534 ELSE
535 result:='COMPLETE:N';
536 print_message('result after next stage = ' ||result);
537 END IF;
538 g_task_tab:=empty_tbl;
539 END IF;
540 EXCEPTION
541 WHEN OTHERS THEN
542 wf_core.context('SAL_MET3','Check task exist for next',itemtype, itemkey, to_char(actid), funcmode);
543 RAISE;
544 END Check_task_exist_for_next;
545 /**************************************************************************
546 * PROCEDURE: create_tasks
547 * DESCRIPTION: This procedure create tasks from templates and creates * references for those tasks.
548 *
549 * It returns 'SUCCESS' if tasks are successfully created Otherwise it returns 'FAIL'
550 **********************************************************************/
551 PROCEDURE create_tasks ( itemtype IN VARCHAR2,
552 itemkey IN VARCHAR2,
553 actid IN NUMBER,
554 funcmode IN VARCHAR2,
555 result OUT NOCOPY VARCHAR2) IS
556 l_meth_note_type VARCHAR2(100);
557 l_return_status VARCHAR2(100);
558 l_ref_status VARCHAR2(100);
559 l_task_name VARCHAR2(10);
560 l_task_id NUMBER;
561 l_task_template_id NUMBER;
562 l_task_template_group_id NUMBER ;
563 l_task_template_group_name VARCHAR2(100) := NULL;
564 l_sales_methodology_id NUMBER;
565 l_owner_id NUMBER ;
566 l_owner_type_code VARCHAR2(100) ;
567 l_object_name VARCHAR2(100);
568 l_object_type_code VARCHAR2(50);
569 l_source_object_type_code VARCHAR2(30) ;
570 l_source_object_id NUMBER ;
571 l_source_object_name VARCHAR2(240) ;
572 l_workflow_process_id VARCHAR2(100);
573 l_msg_count NUMBER;
574 l_msg_data VARCHAR2(4000);
575 l_object_id NUMBER;
576 j NUMBER;
577 l_msg_index_out NUMBER;
578 l_error_msg VARCHAR2(4000);
579 l_api_name VARCHAR2(2000);
580 table_row NUMBER;
581 l_taask_id NUMBER;
582 CURSOR c_Methodology IS
583 SELECT sales_methodology_id from as_leads_all a
584 WHERE a.lead_id = l_source_object_id;
585 CURSOR c_task IS
586 SELECT template_id,template_group_id
587 FROM JTF_TASKS_B where task_id = l_taask_id;
588
589 -- Added for bug 2596419 start
590
591 CURSOR c_customer_info (c_lead_id NUMBER) IS
592 select customer_id, address_id
593 from as_leads_all
594 where lead_id = c_lead_id;
595
596 CURSOR c_primary_address(c_customer_id NUMBER) IS
597 select party_site_id
598 from hz_party_sites
599 where party_id = c_customer_id
600 and identifying_address_flag = 'Y';
601
602 CURSOR c_task_templates(c_task_template_group_id NUMBER) IS
603 select task_template_id
604 from jtf_task_templates_b
605 where task_group_id = c_task_template_group_id;
606
607 CURSOR c_contact_points ( c_customer_id NUMBER) IS
608 select contact_point_id
609 from hz_contact_points
610 where owner_table_name = 'HZ_PARTIES'
611 and owner_table_id = c_customer_id
612 and status = 'A'
613 and primary_flag = 'Y'
614 -- and contact_point_type in ('EMAIL', 'PHONE');
615 and contact_point_type = 'PHONE';
616
617
618 l_task_template_group_info jtf_task_inst_templates_pub.task_template_group_info;
619 l_task_templates_tbl jtf_task_inst_templates_pub.task_template_info_tbl;
620 l_task_contact_points_tbl jtf_task_inst_templates_pub.task_contact_points_tbl;
621
622
623 l_customer_id NUMBER;
624 l_address_id NUMBER;
625
626 K NUMBER := 1;
627
628 -- Added for bug 2596419 end
629
630 BEGIN
631 l_return_status := 'U';
632 IF funcmode = 'RUN' THEN
633 l_source_object_type_code := wf_engine.getitemattrtext( itemtype => itemtype,
634 itemkey => itemkey,
638 itemkey => itemkey,
635 aname => 'SOURCE_OBJECT_TYPE_CODE');
636 print_message('current source type code = '||l_source_object_type_code);
637 l_source_object_id := wf_engine.getitemattrnumber( itemtype => itemtype,
639 aname => 'SOURCE_OBJECT_ID');
640 l_object_type_code := wf_engine.getitemattrtext( itemtype => itemtype,
641 itemkey => itemkey,
642 aname => 'OBJECT_TYPE_CODE');
643 l_object_id :=wf_engine.getitemattrnumber ( itemtype => itemtype,
644 itemkey => itemkey,
645 aname => 'NEXT_STAGE_ID');
646 l_source_object_name := wf_engine.getitemattrtext ( itemtype => itemtype,
647 itemkey => itemkey,
648 aname => 'SOURCE_OBJECT_NAME');
649 l_task_template_group_id:=wf_engine.getitemattrtext ( itemtype => itemtype,
650 itemkey => itemkey,
651 aname => 'TASK_TEMPLATE_GROUP_ID');
652 l_owner_type_code := wf_engine.getitemattrtext( itemtype => itemtype,
653 itemkey => itemkey,
654 aname => 'OWNER_TYPE_CODE');
655 l_owner_id := wf_engine.getitemattrnumber( itemtype => itemtype,
656 itemkey => itemkey,
657 aname => 'OWNER_ID');
658 print_message('OWNER_ID = '||l_owner_id);
659 print_message('OWNER_TYPE_CODE = '||l_owner_type_code);
660 print_message('about to create tasks from templates');
661 SAVEPOINT AS_CREATE_TASK;
662 -------------------------------------------
663
664
665 -- XDING Change for bug 2596491 start
666 -- Call jtf_task_inst_templates_pub.create_task_from_template instead of
667 -- jtf_tasks_pub.create_task_from_template to create tasks with
668 -- opportunity customer context and primary contact points
669
670 IF l_source_object_id IS NULL THEN
671 l_error_msg:='Source Object id must not be null';
672 fnd_message.set_name('AS','AS_INVALID_OBJECT_ID');
673 fnd_msg_pub.add;
674 RAISE fnd_api.g_exc_unexpected_error;
675 END IF;
676
677 OPEN c_customer_info(l_source_object_id);
678 FETCH c_customer_info INTO l_customer_id, l_address_id;
679 CLOSE c_customer_info;
680
681 IF l_address_id IS NULL THEN
682 OPEN c_primary_address (l_customer_id);
683 FETCH c_primary_address INTO l_address_id;
684 CLOSE c_primary_address;
685 END IF;
686
687 l_task_template_group_info.task_template_group_id := l_task_template_group_id;
688 l_task_template_group_info.owner_type_code := l_owner_type_code;
689 l_task_template_group_info.owner_id := l_owner_id;
690 l_task_template_group_info.source_object_id := l_source_object_id;
691 l_task_template_group_info.source_object_name := l_source_object_name;
692 -- l_task_template_group_info.cust_account_id :=
693 l_task_template_group_info.customer_id := l_customer_id;
694 l_task_template_group_info.address_id := l_address_id;
695 l_task_template_group_info.date_selected := 'P';
696 l_task_template_group_info.show_on_calendar := 'Y';
697
698 FOR cntrec IN c_contact_points(l_customer_id) LOOP
699 FOR temprec IN c_task_templates (l_task_template_group_id) LOOP
700 l_task_contact_points_tbl(K).task_template_id := temprec.task_template_id;
701 l_task_contact_points_tbl(K).phone_id := cntrec.contact_point_id;
702 l_task_contact_points_tbl(K).primary_key := 'Y';
703 K := K + 1;
704 END LOOP;
705 END LOOP;
706
707 jtf_task_inst_templates_pub.create_task_from_template (
708 p_api_version => 1.0,
709 p_init_msg_list => fnd_api.g_false,
710 p_commit => fnd_api.g_false,
711 p_task_template_group_info => l_task_template_group_info,
712 p_task_templates_tbl => l_task_templates_tbl,
713 p_task_contact_points_tbl => l_task_contact_points_tbl,
714 x_return_status => l_return_status,
715 x_msg_count => l_msg_count,
716 x_msg_data => l_msg_data,
717 x_task_details_tbl => x_task_details_tbl
718 );
719
720
721
722 /*
723 jtf_tasks_pub.create_task_from_template ( p_api_version => 1.0,
724 p_init_msg_list => fnd_api.g_false,
725 p_commit => fnd_api.g_false,
726 p_task_template_group_id => l_task_template_group_id,
727 p_task_template_group_name => l_task_template_group_name,
728 p_owner_type_code => l_owner_type_code,
729 p_owner_id => l_owner_id,
730 p_source_object_id => l_source_object_id,
731 p_source_object_name => l_source_object_name,
732 p_planned_start_date => sysdate,
733 p_planned_end_date => sysdate,
734 x_return_status => l_return_status,
735 x_msg_count => l_msg_count,
736 x_msg_data => l_msg_data,
737 x_task_details_tbl => g_task_details_tbl );
738
739 */
740
741 -- Change for bug 2596491 end
742
743
744 print_message('status after tasks from templates = ' ||l_return_status);
745 IF l_return_status = 'S' THEN
746 ---------------------------
747 OPEN c_methodology;
748 FETCH c_methodology INTO l_sales_methodology_id;
752 wf_engine.setitemattrtext( itemtype => itemtype,
749 --dbms_output.put_line('Methodology_id = '||l_sales_methodology_id);
750 IF (c_methodology %NOTFOUND) THEN
751 g_notes:= fnd_message.get_string('AS','AS_INVALID_METH');
753 itemkey => itemkey,
754 aname => 'WARNING_MESSAGE',
755 avalue => g_notes);
756 l_error_msg:='Methodology not found for lead_id';
757 l_return_status:='U';
758 END IF;
759 CLOSE c_methodology;
760 ---------------------------
761 FOR table_row IN 1..x_task_details_tbl.count
762 LOOP
763 l_taask_id :=x_task_details_tbl(table_row).task_id;
764 ------------------------------------------------------
765 OPEN c_task;
766 FETCH c_task INTO l_task_template_id,l_task_template_group_id;
767 IF (c_task %NOTFOUND) THEN
768 CLOSE c_task;
769 --------------
770 g_notes:=fnd_message.get_string('AS','AS_INVALID_TEMPLATE_DTL');
771 wf_engine.setitemattrtext( itemtype => itemtype,
772 itemkey => itemkey,
773 aname => 'WARNING_MESSAGE',
774 avalue => g_notes);
775 l_error_msg:='Template and Template group are not found for Task_id';
776 l_return_status:='U';
777 EXIT;
778 --------------
779 END IF;
780 CLOSE c_task;
781 --------------------------------------------------
782 AS_SALES_METH_TASK_MAP_PVT.CREATE_SALES_METH_TASK_MAP ( P_API_VERSION => 1.0,
783 P_INIT_MSG_LIST => fnd_api.g_false,
784 P_COMMIT => fnd_api.g_false,
785 P_VALIDATE_LEVEL => fnd_api.g_valid_level_full,
786 P_SALES_STAGE_ID => l_object_id,
787 P_SALES_METHODOLOGY_ID => l_sales_methodology_id,
788 P_SOURCE_OBJECT_ID => l_source_object_id,
789 P_SOURCE_OBJECT_TYPE_CODE => l_source_object_type_code,
790 P_SOURCE_OBJECT_NAME => l_source_object_name,
791 P_TASK_ID => l_taask_id,
792 P_TASK_TEMPLATE_ID => l_task_template_id,
793 p_task_template_group_id => l_task_template_group_id,
794 X_RETURN_STATUS => l_ref_status,
795 X_MSG_COUNT => l_msg_count,
796 X_MSG_DATA => l_msg_data );
797 ---------------------------------------------------
798 print_message('Return status= '||l_ref_status);
799 IF (FND_MSG_PUB.count_msg >0) THEN
800 FOR j IN 1..FND_MSG_PUB.Count_msg
801 LOOP
802 fnd_msg_pub.get ( p_msg_index => j,
803 p_encoded => 'F',
804 p_data => l_msg_data,
805 p_msg_index_out => l_msg_index_out);
806 print_message(l_msg_data);
807 END LOOP;
808 END IF;
809 IF l_ref_status = 'S' THEN
810 NULL;
811 ELSE
812 g_notes:=fnd_message.get_string('AS','AS_CREATE_TASK_MAP_FAIL');
813 g_notes:=g_notes||l_msg_data;
814 wf_engine.setitemattrtext( itemtype => itemtype,
815 itemkey => itemkey,
816 aname => 'WARNING_MESSAGE',
817 avalue => g_notes);
818 --g_notes:='Create map api in AS_SALES_METH_WF.CREATE_TASK failed with the following error **** '||l_msg_data;
819 l_return_status:='U';
820 EXIT;
821 END IF;
822 END LOOP;
823 ---------------------------------------------------
824 ELSE
825 l_return_status := 'U';
826 ROLLBACK TO AS_CREATE_TASK;
827 IF (FND_MSG_PUB.count_msg >0) THEN
828 FOR j IN 1..FND_MSG_PUB.Count_msg
829 LOOP
830 fnd_msg_pub.get ( p_msg_index => j,
831 p_encoded => 'F',
832 p_data => l_msg_data,
833 p_msg_index_out => l_msg_index_out);
834 print_message(l_msg_data);
835 END LOOP;
836 END IF;
837 g_notes:=fnd_message.get_string('AS','AS_CREATE_TASK_FAIL');
838 g_notes:=g_notes||l_msg_data;
839 wf_engine.setitemattrtext( itemtype => itemtype,
840 itemkey => itemkey,
841 aname => 'WARNING_MESSAGE',
845 wf_engine.setitemattrtext( itemtype => itemtype,
842 avalue => g_notes);
843 END IF ;
844 l_meth_note_type := 'CREATE_TASKS';
846 itemkey => itemkey,
847 aname => 'METH_NOTE_TYPE',
848 avalue => l_meth_note_type);
849 IF l_return_status = 'S' THEN
850 print_message('Create task from template done');
851 result :='COMPLETE:SUCCESS';
852 ELSE
853 result := 'COMPLETE:FAIL';
854 END IF;
855 END IF;
856 EXCEPTION
857 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
858 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
859 Add_Error_Message (l_api_name, l_Error_Msg);
860 FND_MSG_PUB.count_and_get( p_encoded => FND_API.g_false,
861 p_count => l_msg_count,
862 p_data => l_msg_data );
863 Get_Error_Message(l_msg_data);
864 WHEN OTHERS THEN
865 wf_core.context('SAL_MET3','Create task from template',itemtype,itemkey,to_char(actid),funcmode);
866 RAISE;
867 END create_tasks;
868 -------------------------------------
869 /**************************************************************************
870 * PROCEDURE: check_duration
871 * DESCRIPTION: This procedure checks duration
872 *
873 *
874 **********************************************************************/
875 PROCEDURE check_duration ( itemtype IN varchar2,
876 itemkey IN VARCHAR2,
877 actid IN NUMBER,
878 funcmode IN VARCHAR2,
879 result OUT NOCOPY VARCHAR2)IS
880 table_row NUMBER;
881 l_task_details_tbl jtf_tasks_pub.task_details_tbl;
882 l_end_hours NUMBER;
883 l_start_hours NUMBER;
884 l_start_minutes NUMBER;
885 l_planned_end_date DATE;
886 l_planned_start_date DATE;
887 l_duration NUMBER;
888 l_duration_uom VARCHAR2(100);
889 l_current_task_id NUMBER;
890 l_end_date NUMBER;
891 l_update_status VARCHAR2(100);
892 l_msg_data VARCHAR2(4000);
893 l_msg_count NUMBER;
894 l_msg_index_out NUMBER;
895 l_return_status VARCHAR2(10);
896 l_meth_note_type VARCHAR2(100);
897 l_notes_flag VARCHAR2(10):=NULL;
898 CURSOR c_tasks IS
899 SELECT duration,duration_uom,object_version_number,task_id,description,planned_start_date
900 FROM jtf_tasks_vl
901 WHERE task_id = l_current_task_id;
902 c_tasks_rec c_tasks%ROWTYPE;
903 BEGIN
904 l_notes_flag:='NO';
905 --l_return_status := 'U';
906 IF funcmode = 'RUN' THEN
907 FOR table_row IN 1..x_task_details_tbl.count
908 LOOP
909 ----------------------
910 l_current_task_id := x_task_details_tbl(table_row).task_id;
911 ----------------------
912 OPEN c_tasks;
913 fetch c_tasks into c_tasks_rec;
914 IF (c_tasks%NOTFOUND) THEN
915 CLOSE c_tasks;
916 END IF;
917 l_duration := c_tasks_rec.duration;
918 l_duration_uom :=c_tasks_rec.duration_uom;
919 print_message('duration='||l_duration||' '||'duration_uom='||l_duration_uom);
920 IF l_duration IS NOT NULL AND l_duration_uom IS NOT NULL THEN
921 IF l_duration_uom = 'DAY' AND ABS(l_duration) >0 THEN
922 l_notes_flag:='NO';
923 l_planned_end_date:= SYSDATE+l_duration;
924 ELSIF l_duration_uom = 'WK' AND ABS(l_duration) >0 THEN
925 l_notes_flag:='NO';
926 l_planned_end_date := SYSDATE+(7*l_duration);
927 ELSIF l_duration_uom='HR' AND ABS(l_duration) >0 THEN
928 l_notes_flag:='NO';
929 l_planned_end_date :=SYSDATE+(l_duration/24);
930 ELSIF l_duration_uom='MIN' AND ABS(l_duration) >0 THEN
931 l_notes_flag:='NO';
932 l_planned_end_date :=SYSDATE+(l_duration/1440);
933 ELSIF l_duration_uom = 'MTH' AND ABS(l_duration) >0 THEN
934 l_notes_flag:='NO';
935 l_planned_end_date:= ADD_MONTHS(SYSDATE,l_duration);
936 ELSIF l_duration_uom = 'YR' AND ABS(l_duration) >0 THEN
937 l_notes_flag:='NO';
938 l_planned_end_date := ADD_MONTHS(SYSDATE, (l_duration*12));
939 ELSIF l_duration_uom = 'CN' AND ABS(l_duration) >0 THEN
940 l_notes_flag:='NO';
941 l_planned_end_date:=ADD_MONTHS(SYSDATE, (l_duration*100*12));
942 ELSE
943 l_planned_end_date :=SYSDATE;
944 l_notes_flag:='YES';
945 END IF;
946 ELSIF l_duration IS NULL AND l_duration_uom IS NOT NULL THEN
947 l_notes_flag:='YES';
948 l_planned_end_date :=SYSDATE;
949 ELSIF l_duration IS NOT NULL AND l_duration_uom IS NULL THEN
950 l_notes_flag:='YES';
951 l_planned_end_date :=SYSDATE;
952 ELSE
953 --l_notes_flag:='YES';
954 l_planned_end_date :=SYSDATE;
955 END IF;
956 ---------------------------------
957 l_planned_start_date:=SYSDATE;
958 --l_planned_end_date :=SYSDATE;
959 l_start_minutes:=TO_NUMBER(floor(to_char(l_planned_start_date,'mi')/15)*15) ;
960 l_start_hours:=TO_NUMBER(TO_CHAR(l_planned_start_date,'hh24'));
961 l_planned_start_date:=TRUNC(l_planned_start_date)+(l_start_hours/24)+(l_start_minutes/1440);
962 l_end_date:=FLOOR(TO_CHAR(l_planned_end_date,'mi')/15)*15 ;
966 print_message('modified start date'||to_char(l_planned_start_date,'dd-mon-yyyy hh24:mi'));
963 l_end_hours:=TO_NUMBER(TO_CHAR(l_planned_end_date,'hh24'));
964 l_planned_end_date:=TRUNC(l_planned_end_date)+(l_end_hours/24)+(l_end_date/1440);
965 print_message('modified end date'||to_char(l_planned_end_date,'dd-mon-yyyy hh24:mi'));
967 ---------------------------------
968 --------------------Update api------------------------------------
969 JTF_TASKS_PUB.update_task (
970 p_api_version => 1.0,
971 p_object_version_number => c_tasks_rec.object_version_number,
972 p_task_id => c_tasks_rec.task_id,
973 p_description => c_tasks_rec.description,
974 p_planned_start_date => l_planned_start_date,
975 p_planned_end_date => l_planned_end_date,
976 x_return_status => l_update_status,
977 x_msg_count => l_msg_count ,
978 x_msg_data => l_msg_data );
979 print_message('status after update task api='||l_update_status);
980 IF l_update_status = 'S' AND l_notes_flag ='NO'THEN
981 l_return_status:='S';
982 ELSIF l_update_status = 'S' AND l_notes_flag ='YES' THEN
983 l_return_status := 'U';
984 l_meth_note_type := 'CHECK_DURATION';
985 wf_engine.setitemattrtext( itemtype => itemtype,
986 itemkey => itemkey,
987 aname => 'METH_NOTE_TYPE',
988 avalue => l_meth_note_type);
989 g_notes:= fnd_message.get_string('AS','AS_INVALID_DURATION');
990 wf_engine.setitemattrtext( itemtype => itemtype,
991 itemkey => itemkey,
992 aname => 'WARNING_MESSAGE',
993 avalue => g_notes);
994 ELSE
995 g_notes:=fnd_message.get_string('AS','AS_DURATION_UPDATE_FAIL');
996 wf_engine.setitemattrtext( itemtype => itemtype,
997 itemkey => itemkey,
998 aname => 'WARNING_MESSAGE',
999 avalue => g_notes);
1000 l_return_status :='U';
1001 l_meth_note_type := 'CHECK_DURATION';
1002 wf_engine.setitemattrtext( itemtype => itemtype,
1003 itemkey => itemkey,
1004 aname => 'METH_NOTE_TYPE',
1005 avalue => l_meth_note_type);
1006 END IF;
1007 -----------------------------
1008 CLOSE c_tasks;
1009 END LOOP;
1010 print_message('return status = ' ||l_return_status);
1011 -----------------------------------------------------
1012 IF l_return_status = 'S' THEN
1013 result :='COMPLETE:SUCCESS';
1014 --dbms_output.put_line('in check duration'||result);
1015 ELSE
1016 result := 'COMPLETE:FAIL';
1017 --dbms_output.put_line('in check duration'||result);
1018 END IF;
1019 END IF ;
1020 EXCEPTION
1021 WHEN others THEN
1022 --
1023 wf_core.context('SAL_MET3','check duration',itemtype, itemkey, to_char(actid), funcmode);
1024 RAISE;
1025 END check_duration;
1026 PROCEDURE create_note_for_duration ( itemtype IN varchar2,
1027 itemkey IN VARCHAR2,
1028 actid IN NUMBER,
1029 funcmode IN VARCHAR2,
1030 result OUT NOCOPY VARCHAR2)IS
1031 l_meth_note_type VARCHAR2(100);
1032 l_api_version VARCHAR2(10) := 1.0;
1033 l_return_status VARCHAR2(10);
1034 l_note_id NUMBER;
1035 l_context_tab jtf_notes_pub.jtf_note_contexts_tbl_type;
1036 l_validation_level VARCHAR2(10);
1037 l_msg_count NUMBER;
1038 l_msg_list VARCHAR2(10);
1039 l_msg_data VARCHAR2(4000);
1040 l_source_object_code VARCHAR2(100);
1041 l_source_object_id NUMBER;
1042 l_notes VARCHAR2(4000);
1043 l_long_task_id VARCHAR2(4000):= NULL;
1044 n NUMBER :=0;
1045 l_column VARCHAR2(10):=',';
1046 l_org_id NUMBER;
1047 BEGIN
1048 --l_return_status := 'U';
1049 l_org_id :=fnd_profile.value('ORG_ID');
1050 IF funcmode = 'RUN' THEN
1051 l_source_object_code := wf_engine.getitemattrtext( itemtype => itemtype,
1052 itemkey => itemkey,
1053 aname => 'SOURCE_OBJECT_TYPE_CODE');
1054 print_message('current source type code=>'||l_source_object_code);
1055 l_source_object_id:= wf_engine.getitemattrnumber( itemtype => itemtype,
1056 itemkey => itemkey,
1057 aname => 'SOURCE_OBJECT_ID');
1058 l_meth_note_type := wf_engine.getitemattrtext( itemtype => itemtype,
1059 itemkey => itemkey,
1060 aname => 'METH_NOTE_TYPE');
1061 print_message('methodology note type = '||l_meth_note_type);
1062 IF l_meth_note_type = 'CHECK_DURATION' THEN
1063 jtf_notes_pub.Create_note ( p_parent_note_id => NULL,
1064 p_jtf_note_id => fnd_api.g_miss_num,
1065 p_api_version => l_api_version,
1066 p_init_msg_list => fnd_api.g_false,
1070 x_msg_count => l_msg_count,
1067 p_commit => fnd_api.g_false,
1068 p_validation_level => fnd_api.g_valid_level_full,
1069 x_return_status => l_return_status,
1071 x_msg_data => l_msg_data,
1072 p_org_id => l_org_id,
1073 p_source_object_id => l_source_object_id,
1074 p_source_object_code => l_source_object_code,
1075 p_notes => g_notes ,
1076 p_notes_detail => NULL,
1077 p_note_status => 'E',
1078 p_entered_by => fnd_global.user_id,
1079 p_entered_date => sysdate,
1080 x_jtf_note_id => l_note_id,
1081 p_last_update_date => sysdate,
1082 p_last_updated_by => fnd_global.user_id,
1083 p_creation_date => sysdate,
1084 p_created_by => fnd_global.user_id,
1085 p_last_update_login => fnd_global.login_id,
1086 p_context => NULL,
1087 p_note_type => 'AS_SYSTEM',
1088 p_jtf_note_contexts_tab => l_context_tab );
1089 print_message('Create note for duration Completed with = '||l_return_status);
1090 IF l_return_status ='S' then
1091 result:='COMPLETE:SUCCESS';
1092 ELSE
1093 result:='COMPLETE:SUCCESS';
1094 END IF;
1095 END IF;
1096 print_message('Note_id created = '||l_note_id);
1097 END IF;
1098 EXCEPTION
1099 WHEN others THEN
1100 wf_core.context('SAL_MET3','Create Note for Duration',itemtype, itemkey, to_char(actid), funcmode);
1101 RAISE;
1102 END create_note_for_duration;
1103 /**************************************************************************
1104 * PROCEDURE: create_note
1105 * DESCRIPTION: This procedure creates notes based on the previous node
1106 in the workflow.
1107 *
1108 *
1109 **********************************************************************/
1110 PROCEDURE create_note ( itemtype IN varchar2,
1111 itemkey IN VARCHAR2,
1112 actid IN NUMBER,
1113 funcmode IN VARCHAR2,
1114 result OUT NOCOPY VARCHAR2)IS
1115 l_meth_note_type VARCHAR2(100);
1116 l_api_version VARCHAR2(10) := 1.0;
1117 l_return_status VARCHAR2(10);
1118 l_note_id NUMBER;
1119 l_context_tab jtf_notes_pub.jtf_note_contexts_tbl_type;
1120 l_validation_level VARCHAR2(10);
1121 l_msg_count NUMBER;
1122 l_msg_list VARCHAR2(10);
1123 l_msg_data VARCHAR2(4000);
1124 l_source_object_code VARCHAR2(100);
1125 l_source_object_id NUMBER;
1126 l_long_task_id VARCHAR2(4000):= NULL;
1127 n NUMBER :=0;
1128 l_column VARCHAR2(10):=',';
1129 l_sql VARCHAR2(2000);
1130 TYPE c_task_name IS REF CURSOR;
1131 c_task_name_ref c_task_name;
1132 l_long_task_name VARCHAR2(2000);
1133 l_task_name VARCHAR2(2000);
1134 l_org_id NUMBER;
1135 BEGIN
1136 l_return_status := 'S';
1137 l_org_id:= fnd_profile.value('ORG_ID');
1138 IF funcmode = 'RUN' THEN
1139 l_source_object_code := wf_engine.getitemattrtext( itemtype => itemtype,
1140 itemkey => itemkey,
1141 aname => 'SOURCE_OBJECT_TYPE_CODE');
1142 print_message('current source type code=>'||l_source_object_code);
1143 l_source_object_id:= wf_engine.getitemattrnumber( itemtype => itemtype,
1144 itemkey => itemkey,
1145 aname => 'SOURCE_OBJECT_ID');
1146 l_meth_note_type := wf_engine.getitemattrtext( itemtype => itemtype,
1147 itemkey => itemkey,
1148 aname => 'METH_NOTE_TYPE');
1149 print_message('methodology note type = '||l_meth_note_type);
1150 IF l_meth_note_type = 'CURRENT_STAGE' THEN
1151 ---------------------------------
1152 FOR n IN 1 .. g_task_tab.count
1153 LOOP
1154 IF l_long_task_id IS NULL THEN
1155 l_long_task_id := g_task_tab(n);
1156 ELSE
1157 l_long_task_id := l_long_task_id ||l_column||g_task_tab(n);
1158 END IF;
1159 END LOOP;
1160 ---------------------------------
1161 l_sql:='SELECT task_name FROM jtf_tasks_vl WHERE task_id IN'||'('||l_long_task_id||')';
1162 OPEN c_task_name_ref FOR l_sql;
1163 LOOP
1164 FETCH c_task_name_ref INTO l_task_name;
1165 IF l_long_task_name IS NULL THEN
1166 l_long_task_name:=l_task_name;
1167 l_task_name:= NULL;
1168 ELSE
1169 IF l_task_name IS NOT NULL THEN
1170 l_long_task_name := l_long_task_name||l_column||' '||l_task_name;
1171 l_task_name:= NULL;
1172 END IF;
1173 END IF;
1174 EXIT WHEN c_task_name_ref%NOTFOUND;
1175 END LOOP;
1176 CLOSE c_task_name_ref ;
1177 print_message ('task_names are = '||l_long_task_name);
1181 END IF;
1178 print_message ('task_ids are = '||l_long_task_id);
1179 ------------------------
1180 g_notes := 'Mandatory Tasks for Previous Stage have not been Completed.Work flow will continue with the next Stage. '||'*'||' '||l_long_task_name||' '||'*';
1182 jtf_notes_pub.Create_note ( p_parent_note_id => NULL,
1183 p_jtf_note_id => fnd_api.g_miss_num,
1184 p_api_version => l_api_version,
1185 p_init_msg_list => fnd_api.g_false,
1186 p_commit => fnd_api.g_false,
1187 p_validation_level => fnd_api.g_valid_level_full,
1188 x_return_status => l_return_status,
1189 x_msg_count => l_msg_count,
1190 x_msg_data => l_msg_data,
1191 p_org_id => l_org_id,
1192 p_source_object_id => l_source_object_id,
1193 p_source_object_code => l_source_object_code,
1194 p_notes => g_notes ,
1195 p_notes_detail => NULL,
1196 p_note_status => 'E',
1197 p_entered_by => fnd_global.user_id,
1198 p_entered_date => sysdate,
1199 x_jtf_note_id => l_note_id,
1200 p_last_update_date => sysdate,
1201 p_last_updated_by => fnd_global.user_id,
1202 p_creation_date => sysdate,
1203 p_created_by => fnd_global.user_id,
1204 p_last_update_login => fnd_global.login_id,
1205 p_context => NULL,
1206 p_note_type => 'AS_SYSTEM',
1207 p_jtf_note_contexts_tab => l_context_tab );
1208 print_message('Create note Completed with = '||l_return_status);
1209 print_message('Note_id created = '||l_note_id);
1210 g_task_tab:=empty_tbl;
1211 END IF;
1212 EXCEPTION
1213 WHEN OTHERS THEN
1214 wf_core.context('SAL_MET3','Create note from ',itemtype,itemkey,to_char(actid),funcmode);
1215 RAISE;
1216 END create_note;
1217 ----------------------------------
1218 PROCEDURE create_note_for_tasks_failure ( itemtype IN varchar2,
1219 itemkey IN VARCHAR2,
1220 actid IN NUMBER,
1221 funcmode IN VARCHAR2,
1222 result OUT NOCOPY VARCHAR2)IS
1223 l_meth_note_type VARCHAR2(100);
1224 l_api_version VARCHAR2(10) := 1.0;
1225 l_return_status VARCHAR2(10);
1226 l_note_id NUMBER;
1227 l_context_tab jtf_notes_pub.jtf_note_contexts_tbl_type;
1228 l_validation_level VARCHAR2(10);
1229 l_msg_count NUMBER;
1230 l_msg_list VARCHAR2(10);
1231 l_msg_data VARCHAR2(4000);
1232 l_source_object_code VARCHAR2(100);
1233 l_source_object_id NUMBER;
1234 l_notes VARCHAR2(4000);
1235 l_long_task_id VARCHAR2(4000):= NULL;
1236 n NUMBER :=0;
1237 l_column VARCHAR2(10):=',';
1238 l_org_id NUMBER;
1239 BEGIN
1240 l_return_status := 'U';
1241 l_org_id :=fnd_profile.value('ORG_ID');
1242 IF funcmode = 'RUN' THEN
1243 l_source_object_code := wf_engine.getitemattrtext( itemtype => itemtype,
1244 itemkey => itemkey,
1245 aname => 'SOURCE_OBJECT_TYPE_CODE');
1246 print_message('current source type code=>'||l_source_object_code);
1247 l_source_object_id:= wf_engine.getitemattrnumber( itemtype => itemtype,
1248 itemkey => itemkey,
1249 aname => 'SOURCE_OBJECT_ID');
1250 l_meth_note_type := wf_engine.getitemattrtext( itemtype => itemtype,
1251 itemkey => itemkey,
1252 aname => 'METH_NOTE_TYPE');
1253 print_message('methodology note type = '||l_meth_note_type);
1254 IF l_meth_note_type = 'CREATE_TASKS' THEN
1255 jtf_notes_pub.Create_note ( p_parent_note_id => NULL,
1256 p_jtf_note_id => fnd_api.g_miss_num,
1257 p_api_version => l_api_version,
1258 p_init_msg_list => fnd_api.g_false,
1259 p_commit => fnd_api.g_false,
1260 p_validation_level => fnd_api.g_valid_level_full,
1261 x_return_status => l_return_status,
1262 x_msg_count => l_msg_count,
1263 x_msg_data => l_msg_data,
1264 p_org_id => l_org_id,
1265 p_source_object_id => l_source_object_id,
1266 p_source_object_code => l_source_object_code,
1267 p_notes => g_notes ,
1268 p_notes_detail => NULL,
1269 p_note_status => 'E',
1270 p_entered_by => fnd_global.user_id,
1271 p_entered_date => sysdate,
1272 x_jtf_note_id => l_note_id,
1273 p_last_update_date => sysdate,
1274 p_last_updated_by => fnd_global.user_id,
1275 p_creation_date => sysdate,
1276 p_created_by => fnd_global.user_id,
1277 p_last_update_login => fnd_global.login_id,
1278 p_context => NULL,
1279 p_note_type => 'AS_SYSTEM',
1280 p_jtf_note_contexts_tab => l_context_tab );
1281 print_message('Create note Completed with = '||l_return_status);
1282 END IF;
1283 print_message('Note_id created = '||l_note_id);
1284 END IF;
1285 EXCEPTION
1286 WHEN OTHERS THEN
1287 wf_core.context('SAL_MET3','Create note from ',itemtype,itemkey,to_char(actid),funcmode);
1288 RAISE;
1289 END create_note_for_tasks_failure;
1290 END as_sales_meth_wf;