[Home] [Help]
PACKAGE BODY: APPS.TASK_MGR
Source
1 PACKAGE BODY TASK_MGR AS
2 /* $Header: JTFATSKB.pls 120.2 2005/10/31 05:26:24 snellepa ship $ */
3
4 PROCEDURE create_task(API_VERSION IN NUMBER,
5 TASK_NAME IN VARCHAR2,
6 TASK_TYPE_ID IN NUMBER,
7 TASK_STATUS_ID IN NUMBER,
8 OWNER_TYPE_CODE IN VARCHAR2,
9 OWNER_ID IN NUMBER,
10 SOURCE_OBJECT_TYPE_CODE IN VARCHAR2,
11 PARTY_ID IN NUMBER,
12 X_MSG_DATA OUT NOCOPY VARCHAR2,
13 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
14 X_MSG_COUNT OUT NOCOPY NUMBER)
15 IS
16 x NUMBER;
17 l_msg_count NUMBER;
18 l_msg_data VARCHAR2(2000);
19
20
21 -- NOTE-related variables...
22 COMMENTS VARCHAR2(2000) := 'Initial Note';
23 -- l_return_status VARCHAR2(30);
24 l_note_id NUMBER;
25 l_notes VARCHAR2(4000) := 'Initial Note';
26 l_notes_detail VARCHAR2(32000) ;
27 l_owner_id NUMBER := 0;
28 l_status_fail EXCEPTION;
29 l_msg_index number;
30
31 BEGIN
32
33 --insert into mytemp2 values(API_VERSION, TASK_NAME, TASK_TYPE_ID,
34 --TASK_STATUS_ID, OWNER_TYPE_CODE, OWNER_ID, SOURCE_OBJECT_TYPE_CODE,
35 --PARTY_ID);
36
37 SELECT RESOURCE_ID
38 INTO l_owner_id
39 FROM JTF_RS_RESOURCE_EXTNS
40 WHERE CATEGORY = 'EMPLOYEE'
41 AND ROWNUM = 1;
42
43 -- DBMS_OUTPUT.PUT_LINE('OWNER_ID = '|| l_owner_id);
44
45 JTF_TASKS_PUB.CREATE_TASK
46 (
47 P_API_VERSION => 1.0,
48 P_INIT_MSG_LIST => FND_API.G_TRUE,
49 P_COMMIT => FND_API.G_FALSE,
50 P_TASK_NAME => 'Creation Of Approval Request',
51 P_TASK_TYPE_NAME => null,
52 P_TASK_TYPE_ID => 1,
53 P_DESCRIPTION => NULL,
54 P_TASK_STATUS_NAME => NULL,
55 P_TASK_STATUS_ID => 10,
56 P_TASK_PRIORITY_NAME => NULL,
57 P_TASK_PRIORITY_ID => 4,
58 P_OWNER_TYPE_CODE => 'RS_EMPLOYEE',
59 P_OWNER_ID => l_owner_id,
60 P_ASSIGNED_BY_NAME => NULL,
61 P_ASSIGNED_BY_ID => NULL,
62 P_CUSTOMER_NUMBER => NULL,
63 P_CUSTOMER_ID => PARTY_ID,
64 P_SOURCE_OBJECT_TYPE_CODE => 'ISUPPORT',
65 P_SOURCE_OBJECT_ID => PARTY_ID,
66 P_SOURCE_OBJECT_NAME => PARTY_ID,
67 X_RETURN_STATUS => x_return_status,
68 X_MSG_COUNT => x_msg_count,
69 X_MSG_DATA => x_msg_data ,
70 X_TASK_ID => x
71 );
72
73 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
74 RAISE l_status_fail;
75 END IF;
76
77 -- l_msg_data := FND_MSG_PUB.GET(1, 'F');
78
79 --DBMS_OUTPUT.PUT_LINE('RETURN_STATUS = '||l_rs);
80 --DBMS_OUTPUT.PUT_LINE('X_MSG_COUNT = '||l_msg_count);
81 --DBMS_OUTPUT.PUT_LINE('MSG_DATA = ' ||l_msg_data);
82 --DBMS_OUTPUT.PUT_LINE('TASK_ID = ' ||x);
83
84 --DBMS_OUTPUT.PUT_LINE('Creating a note...');
85
86 JTF_NOTES_PUB.CREATE_NOTE
87 (
88 p_parent_note_id => NULL,
89 p_api_version => 1,
90 p_init_msg_list => NULL,
91 p_commit => FND_API.G_FALSE,
92 p_validation_level => 0,
93 x_return_status => x_return_status,
94 x_msg_count => x_msg_count,
95 x_msg_data => x_msg_data,
96 x_jtf_note_id => l_note_id,
97 p_org_id => NULL,
98 p_source_object_id => x,
99 p_source_object_code => 'TASK',
100 p_notes => l_notes,
101 -- p_notes_detail => COMMENTS,
102 p_note_status => 'I',
103 p_entered_by => FND_GLOBAL.USER_ID,
104 p_entered_date => SYSDATE,
105 p_last_update_date => SYSDATE,
106 p_last_updated_by => FND_GLOBAL.USER_ID,
107 p_creation_date => SYSDATE,
108 p_created_by => FND_GLOBAL.USER_ID
109 );
110
111 --dbms_output.put_line('Api completed '||l_return_status||l_note_id);
112
113 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) then
114 RAISE l_status_fail;
115 END IF;
116
117 COMMIT;
118
119 EXCEPTION
120 when l_status_fail then
121 l_msg_index := 1;
122 x_msg_data := ' ';
123 l_msg_count := x_msg_count;
124
125 while l_msg_count > 0 loop
126 l_msg_data := fnd_msg_pub.get(l_msg_index, fnd_api.g_false);
127 x_msg_data := concat(x_msg_data, l_msg_data);
128 x_msg_data := concat(x_msg_data, '###');
129 l_msg_index := l_msg_index + 1;
130 l_msg_count := l_msg_count - 1;
131 end loop;
132 END create_task;
133
134
135 FUNCTION query_task(API_VERSION IN NUMBER DEFAULT 1.0,
136 START_POINTER IN NUMBER DEFAULT 1,
137 REC_WANTED IN NUMBER DEFAULT 1,
138 TASK_STATUS_ID IN NUMBER DEFAULT 10,
139 SHOW_ALL IN VARCHAR2 DEFAULT 'YES',
140 SORT_ORDER IN VARCHAR2 DEFAULT 'sortByAscendingDate')
141 RETURN CLOB IS
142
143 l_task_table JTF_TASKS_PUB.TASK_TABLE_TYPE;
144 l_total_retrieved NUMBER;
145 l_total_returned NUMBER;
146 l_rs VARCHAR2(1);
147 l_msg_count NUMBER;
148 l_msg_data VARCHAR2(2000);
149 l_version_num NUMBER;
150
151 l_sort_data JTF_TASKS_PUB.SORT_DATA;
152
153 recurs JTF_TASKS_PUB.TASK_RECUR_REC;
154 rsc JTF_TASKS_PUB.TASK_RSRC_REQ_TBL;
155 asn JTF_TASKS_PUB.TASK_ASSIGN_TBL;
156 notes JTF_TASKS_PUB.TASK_NOTES_TBL;
157
158 v_first INTEGER;
159 v_index INTEGER;
160
161 row_length INTEGER := 0;
162 query_clob CLOB;
163
164 result VARCHAR2(4000) := '';
165 user0 VARCHAR2(4000) := '';
166 user1 VARCHAR2(4000) := '';
167 user2 VARCHAR2(4000) := '';
168 user3 VARCHAR2(4000) := '';
169 user4 VARCHAR2(4000) := '';
170 user5 VARCHAR2(4000) := '';
171 user6 VARCHAR2(4000) := '';
172 user7 VARCHAR2(4000) := '';
173
174
175 BEGIN
176
177
178 IF( SORT_ORDER = 'sortByAscendingDate' ) THEN
179 l_sort_data(1).field_name := 'task_id';
180 l_sort_data(1).asc_dsc_flag := 'A';
181 l_sort_data(2).field_name := 'task_name';
182 l_sort_data(2).asc_dsc_flag := 'D';
183 ELSIF( SORT_ORDER = 'sortByDescendingDate' ) THEN
184 l_sort_data(1).field_name := 'task_id';
185 l_sort_data(1).asc_dsc_flag := 'D';
186 l_sort_data(2).field_name := 'task_name';
187 l_sort_data(2).asc_dsc_flag := 'D';
188 ELSIF( SORT_ORDER = 'sortByCompany' ) THEN
189 l_sort_data(1).field_name := 'task_id';
190 l_sort_data(1).asc_dsc_flag := 'A';
191 l_sort_data(2).field_name := 'task_name';
192 l_sort_data(2).asc_dsc_flag := 'D';
193 END IF;
194
195
196 JTF_TASKS_PUB.query_task(
197 P_API_VERSION => 1.0,
198 P_START_POINTER => 1,
199 P_REC_WANTED => 10,
200 P_SHOW_ALL => 'Y',
201 P_QUERY_OR_NEXT_CODE => 'Q',
202 P_OBJECT_TYPE_CODE => 'ISUPPORT',
203 P_TASK_STATUS_ID => TASK_STATUS_ID,
204 P_TASK_TYPE_ID => 1,
205 --P_SOURCE_OBJECT_ID => 'ISUPPORT',
206 --P_SOURCE_OBJECT_CODE => 'ISUPPORT',
207 --P_SOURCE_OBJECT_TYPE_CODE => 'ISUPPORT',
208 --P_SOURCE_OBJECT_ID => PARTY_ID,
209 --P_SOURCE_OBJECT_NAME => PARTY_ID,
210 P_SORT_DATA => l_sort_data,
211 X_TASK_TABLE => l_task_table,
212 X_TOTAL_RETRIEVED => l_total_retrieved,
213 X_TOTAL_RETURNED => l_total_returned,
214 X_RETURN_STATUS => l_rs,
215 X_MSG_COUNT => l_msg_count,
216 X_MSG_DATA => l_msg_data,
217 X_OBJECT_VERSION_NUMBER => l_version_num);
218
219 l_msg_data := FND_MSG_PUB.GET(1, 'F' );
220
221
222 --DBMS_OUTPUT.PUT_LINE('RETRIEVED = '||l_total_retrieved);
223 --DBMS_OUTPUT.PUT_LINE('RETURNED = '||l_total_returned);
224 --DBMS_OUTPUT.PUT_LINE('RETURN_STATUS = '||l_rs);
225 --DBMS_OUTPUT.PUT_LINE('X_MSG_COUNT = '||l_msg_count);
226 --DBMS_OUTPUT.PUT_LINE('MSG_DATA = ' ||l_msg_data);
227 --DBMS_OUTPUT.PUT_LINE('TASK_ID = ' ||l_version_num);
228
229 DBMS_LOB.CREATETEMPORARY(query_clob, TRUE, DBMS_LOB.SESSION);
230
231 IF (l_task_table.count > 0) THEN
232 v_first := l_task_table.first;
233 v_index := v_first;
234
235 LOOP
236
237 --DBMS_OUTPUT.PUT_LINE('Task Name: '||l_task_table(v_index).task_name);
238 --DBMS_OUTPUT.PUT_LINE('Id: '||to_char(l_task_table(v_index).task_id));
239 --DBMS_OUTPUT.PUT_LINE('Number: '||l_task_table(v_index).task_number);
240 --DBMS_OUTPUT.PUT_LINE('Acc#: '||l_task_table(v_index).cust_account_number);
241 --DBMS_OUTPUT.PUT_LINE('Cust Name: '|| l_task_table(v_index).customer_name);
242 --DBMS_OUTPUT.PUT_LINE('Status:'||l_task_table(v_index).task_status);
243
244 --taskID = queryTaskTokens.nextToken();
245 --partyID = queryTaskTokens.nextToken();
246 --companyID = queryTaskTokens.nextToken();
247 --regDate = queryTaskTokens.nextToken();
248 --userName = queryTaskTokens.nextToken();
249 --userCompany = queryTaskTokens.nextToken();
250 --accountID = queryTaskTokens.nextToken();
251
252 IF (result IS NULL ) THEN
253 result := NVL(TO_CHAR(l_task_table(v_index).task_id), 'EMPTY');
254 ELSE
255 result := result||
256 NVL(TO_CHAR(l_task_table(v_index).task_id), 'EMPTY');
257 END IF;
258
259 -- ***************************************
260 -- NOTE: customer_ID contains the partyID
261 -- ***************************************
262
263 result := result
264 ||'^'||NVL(l_task_table(v_index).customer_id, '-1')
265 -- ||'^'||NVL(TO_CHAR(l_task_table(v_index).task_id), 'EMPTY')
266 ||'^'||TO_CHAR(l_task_table(v_index).creation_date,'DD/MM/YYYY')
267 -- ||'^'||NVL(l_task_table(v_index).customer_name, 'EMPTY')
268 -- ||'^'||NVL(l_task_table(v_index).customer_name, 'EMPTY')
269 -- ||'^'||NVL(l_task_table(v_index).cust_account_number, 'EMPTY')
270 ||'^';
271
272 row_length := NVL(length(rtrim(result)), 0);
273 DBMS_LOB.WRITEAPPEND(query_clob, row_length, result);
274 result := '';
275
276 EXIT WHEN v_index = l_task_table.last;
277 v_index := l_task_table.next(v_index);
278
279 END LOOP;
280
281 ELSE
282 user0 := '12/10/99^John Smith0^Oracle0^Acct 120^';
283 user1 := '12/11/99^John Smith1^Oracle1^Acct 121^';
284 user2 := '12/12/99^John Smith2^Oracle2^Acct 122^';
285 user3 := '12/13/99^John Smith3^Oracle3^Acct 123^';
286 user4 := '12/14/99^John Smith4^Oracle4^Acct 124^';
287 user5 := '12/15/99^John Smith5^Oracle5^Acct 125^';
288 user6 := '12/16/99^John Smith6^Oracle6^Acct 126^';
289 user7 := '12/17/99^John Smith7^Oracle7^Acct 127^';
290
291 result := result||user0||user1||user2||user3||
292 user4||user5||user6||user7;
293
294 END IF;
295
296 return( query_clob );
297
298 END query_task;
299
300
301 PROCEDURE query_test(API_VERSION IN NUMBER DEFAULT 1.0,
302 START_POINTER IN NUMBER DEFAULT 1,
303 REC_WANTED IN NUMBER DEFAULT 1,
304 SHOW_ALL IN VARCHAR2 DEFAULT 'YES',
305 SORT_ORDER IN VARCHAR2 DEFAULT 'sortByAscendingDate')
306 IS
307 l_task_table JTF_TASKS_PUB.TASK_TABLE_TYPE;
308 l_total_retrieved NUMBER;
309 l_total_returned NUMBER;
310 l_rs VARCHAR2(1);
311 l_msg_count NUMBER;
312 l_msg_data VARCHAR2(2000);
313 l_version_num NUMBER;
314
315 l_sort_data JTF_TASKS_PUB.SORT_DATA;
316
317 recurs JTF_TASKS_PUB.TASK_RECUR_REC;
318 rsc JTF_TASKS_PUB.TASK_RSRC_REQ_TBL;
319 asn JTF_TASKS_PUB.TASK_ASSIGN_TBL;
320 notes JTF_TASKS_PUB.TASK_NOTES_TBL;
321
322 v_first integer;
323 v_index integer;
324
325 result VARCHAR2(4000) := '';
326 user0 VARCHAR2(4000) := '';
327 user1 VARCHAR2(4000) := '';
328 user2 VARCHAR2(4000) := '';
329 user3 VARCHAR2(4000) := '';
330 user4 VARCHAR2(4000) := '';
331 user5 VARCHAR2(4000) := '';
332 user6 VARCHAR2(4000) := '';
333 user7 VARCHAR2(4000) := '';
334
335
336 BEGIN
337
338 --DBMS_OUTPUT.PUT_LINE('Testing Task Query sort_order = '||sort_order);
339
340 IF( SORT_ORDER = 'sortByAscendingDate' ) THEN
341 l_sort_data(1).field_name := 'task_name';
342 l_sort_data(1).asc_dsc_flag := 'D';
343 l_sort_data(2).field_name := 'task_id';
344 l_sort_data(2).asc_dsc_flag := 'A';
345 ELSIF( SORT_ORDER = 'sortByDescendingDate' ) THEN
346 l_sort_data(1).field_name := 'task_name';
347 l_sort_data(1).asc_dsc_flag := 'D';
348 l_sort_data(2).field_name := 'task_id';
349 l_sort_data(2).asc_dsc_flag := 'D';
350 ELSIF( SORT_ORDER = 'sortByCompany' ) THEN
351 l_sort_data(1).field_name := 'task_name';
352 l_sort_data(1).asc_dsc_flag := 'D';
353 l_sort_data(2).field_name := 'task_id';
354 l_sort_data(2).asc_dsc_flag := 'A';
355 END IF;
356
357
358 JTF_TASKS_PUB.query_task(
359 P_API_VERSION => 1.0,
360 P_START_POINTER => 1,
361 P_REC_WANTED => 10,
362 P_SHOW_ALL => 'Y',
363 P_QUERY_OR_NEXT_CODE => 'Q',
364 --P_SOURCE_OBJECT_CODE => 'ISUPPORT',
365 P_SORT_DATA => l_sort_data,
366 X_TASK_TABLE => l_task_table,
367 X_TOTAL_RETRIEVED => l_total_retrieved,
368 X_TOTAL_RETURNED => l_total_returned,
369 X_RETURN_STATUS => l_rs,
370 X_MSG_COUNT => l_msg_count,
371 X_MSG_DATA => l_msg_data,
372 X_OBJECT_VERSION_NUMBER => l_version_num);
373
374 l_msg_data := FND_MSG_PUB.GET(1, 'F' );
375
376 --DBMS_OUTPUT.PUT_LINE('X_TSK_TBL.count= '||TO_CHAR(l_task_table.count));
377
381 --DBMS_OUTPUT.PUT_LINE('X_MSG_COUNT = '||l_msg_count);
378 --DBMS_OUTPUT.PUT_LINE('RETRIEVED = '||l_total_retrieved);
379 --DBMS_OUTPUT.PUT_LINE('RETURNED = '||l_total_returned);
380 --DBMS_OUTPUT.PUT_LINE('RETURN_STATUS = '||l_rs);
382 --DBMS_OUTPUT.PUT_LINE('MSG_DATA = ' ||l_msg_data);
383 --DBMS_OUTPUT.PUT_LINE('TASK_ID = ' ||l_version_num);
384
385 IF (l_task_table.count > 0) THEN
386 v_first := l_task_table.first;
387 v_index := v_first;
388
389 LOOP
390
391 --DBMS_OUTPUT.PUT_LINE('Task Name: '||l_task_table(v_index).task_name);
392 --DBMS_OUTPUT.PUT_LINE('Id: '||to_char(l_task_table(v_index).task_id));
393 --DBMS_OUTPUT.PUT_LINE('Number: '||l_task_table(v_index).task_number);
394 --DBMS_OUTPUT.PUT_LINE('Acct#: '||l_task_table(v_index).cust_account_number);
395
396 --DBMS_OUTPUT.PUT_LINE('Cust Name: '|| l_task_table(v_index).customer_name);
397 --DBMS_OUTPUT.PUT_LINE('Status:'||l_task_table(v_index).task_status);
398 --DBMS_OUTPUT.PUT_LINE('Creation:'||l_task_table(v_index).creation_date);
399
400 EXIT WHEN v_index = l_task_table.last;
401 v_index := l_task_table.next(v_index);
402
403 END LOOP;
404 result := result||'cccccccccccccccccccccccccccc';
405
406 ELSE
407 user0 := '12/10/99^John Smith0^Oracle0^Acct 120^';
408 user1 := '12/11/99^John Smith1^Oracle1^Acct 121^';
409 user2 := '12/12/99^John Smith2^Oracle2^Acct 122^';
410 user3 := '12/13/99^John Smith3^Oracle3^Acct 123^';
411 user4 := '12/14/99^John Smith4^Oracle4^Acct 124^';
412 user5 := '12/15/99^John Smith5^Oracle5^Acct 125^';
413 user6 := '12/16/99^John Smith6^Oracle6^Acct 126^';
414 user7 := '12/17/99^John Smith7^Oracle7^Acct 127^';
415
416 result := result||'dddddddddddddddddddddddddddd';
417 END IF;
418
419 result := result||user0||user1||user2||user3||
420 user4||user5||user6||user7;
421
422 END query_test;
423
424
425 --SQL> select task_status_id, name
426 -- 2 from jtf_task_statuses_vl;
427 --
428 --TASK_STATUS_ID NAME
429 ---------------- ------------------------------
430 -- 2 PLANNED
431 -- 3 ACCEPTED
432 -- 4 REJECTED
433 -- 5 WORKING
434 -- 6 INTERRUPTED
435 -- 7 CANCELLED
436 -- 8 COMPLETED
437 -- 9 CLOSED
438 -- 10 OPEN
439 -- 11 CLOSE
440 -- 12 NOT STARTED
441 -- 14 ASSIGNED
442 -- 1 IN PLANNING
443 -- 13 UNASSIGNED
444
445
446 PROCEDURE update_task(API_VERSION IN NUMBER DEFAULT 1.0,
447 OBJECT_VERSION_NUMBER IN NUMBER DEFAULT 1,
448 P_TASK_ID IN NUMBER,
449 COMMENTS IN VARCHAR2 DEFAULT NULL,
450 COMPLETION_STATUS IN VARCHAR2 DEFAULT 'COMPLETED',
451 X_MSG_DATA OUT NOCOPY VARCHAR2,
452 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
453 X_MSG_COUNT OUT NOCOPY NUMBER)
454 IS
455 l_task_table JTF_TASKS_PUB.TASK_TABLE_TYPE;
456 l_total_retrieved NUMBER;
457 l_total_returned NUMBER;
458 -- l_rs VARCHAR2(1);
459 l_msg_count NUMBER;
460 l_msg_data VARCHAR2(2000);
461 l_version_num NUMBER;
462 l_status_id NUMBER := 9;
463 x NUMBER;
464
465 l_status_fail EXCEPTION;
466 l_msg_index number;
467 l_sort_data JTF_TASKS_PUB.SORT_DATA;
468
469 -- note-related variables...
470
471 l_object_version_number NUMBER := 0;
472 l_jtf_note_id NUMBER := 0;
473 -- l_return_status VARCHAR2(30);
474 l_note_id NUMBER;
475 l_notes VARCHAR2(4000) := '';
476 l_notes_detail VARCHAR2(32000);
477 l_owner_id NUMBER := 0;
478 -- context_tab JTF_NOTES_PUB.JTF_NOTE_CONTEXTS_TBL_TYPE;
479
480 BEGIN
481
482 l_status_id := 8;
483
484 SELECT OBJECT_VERSION_NUMBER
485 INTO l_object_version_number
486 FROM JTF_TASKS_B
487 WHERE TASK_ID = P_TASK_ID;
488
489 SELECT JTF_NOTE_ID
490 INTO l_note_id
491 FROM JTF_NOTES_B
492 WHERE SOURCE_OBJECT_ID = p_task_id
493 AND SOURCE_OBJECT_CODE = 'TASK'
494 AND ROWNUM = 1;
495
496 SELECT RESOURCE_ID
497 INTO l_owner_id
498 FROM JTF_RS_RESOURCE_EXTNS
499 WHERE CATEGORY = 'EMPLOYEE'
500 AND ROWNUM = 1;
501
502 -- DBMS_OUTPUT.PUT_LINE('Task '||p_task_id||' status = '||l_status_id||
503 -- ' with completion_status = '||completion_status );
504 -- DBMS_OUTPUT.PUT_LINE('l_note_id=' || l_note_id);
505
506
507 JTF_TASKS_PUB.update_task(
508 P_API_VERSION => 1.0,
509 P_OBJECT_VERSION_NUMBER => l_object_version_number,
510 P_TASK_STATUS_ID => l_status_id,
511 P_TASK_ID => P_TASK_ID,
512 P_COMMIT => FND_API.G_FALSE,
516
513 X_RETURN_STATUS => x_return_status,
514 X_MSG_COUNT => x_msg_count,
515 X_MSG_DATA => x_msg_data);
517 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
518 RAISE l_status_fail;
519 END IF;
520
521 -- l_msg_data := FND_MSG_PUB.GET(1, 'F' );
522
523 --DBMS_OUTPUT.PUT_LINE('RETURN_STATUS = '||l_rs);
524 --DBMS_OUTPUT.PUT_LINE('X_MSG_COUNT = '||l_msg_count);
525 --DBMS_OUTPUT.PUT_LINE('MSG_DATA = ' ||l_msg_data);
526
527
528 l_notes := COMMENTS;
529
530 JTF_NOTES_PUB.UPDATE_NOTE
531 ( p_api_version => 1,
532 p_init_msg_list => FND_API.G_FALSE,
533 p_commit => FND_API.G_FALSE,
534 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
535 x_return_status => x_return_status,
536 x_msg_count => x_msg_count,
537 x_msg_data => x_msg_data,
538 p_jtf_note_id => l_note_id,
539 p_entered_by => NULL,
540 p_last_updated_by => l_owner_id,
541 p_last_update_date => SYSDATE,
542 p_last_update_login => NULL,
543 p_notes => COMMENTS
544 );
545
546 -- DBMS_OUTPUT.PUT_LINE('RETURN_STATUS = '||x_return_status);
547 -- DBMS_OUTPUT.PUT_LINE('X_MSG_COUNT = '||x_msg_count);
548 -- DBMS_OUTPUT.PUT_LINE('MSG_DATA = ' ||x_msg_data);
549
550
551 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
552 RAISE l_status_fail;
553 END IF;
554
555 COMMIT;
556
557 EXCEPTION
558 when l_status_fail then
559 l_msg_index := 1;
560 x_msg_data := ' ';
561 l_msg_count := x_msg_count;
562
563 while l_msg_count > 0 loop
564 l_msg_data := fnd_msg_pub.get(l_msg_index, fnd_api.g_false);
565 x_msg_data := concat(x_msg_data, l_msg_data);
566 x_msg_data := concat(x_msg_data, '###');
567 l_msg_index := l_msg_index + 1;
568 l_msg_count := l_msg_count - 1;
569 end loop;
570
571 -- dbms_output.put_line('NOTE API COMPLETED: '|| l_return_status || l_note_id);
572
573 END update_task;
574
575
576 PROCEDURE update_task_without_note(API_VERSION IN NUMBER DEFAULT 1.0,
577 OBJECT_VERSION_NUMBER IN NUMBER DEFAULT 1,
578 P_TASK_ID IN NUMBER,
579 COMMENTS IN VARCHAR2 DEFAULT NULL,
580 COMPLETION_STATUS IN VARCHAR2 DEFAULT 'COMPLETED') IS
581
582 l_task_table JTF_TASKS_PUB.TASK_TABLE_TYPE;
583 l_total_retrieved NUMBER;
584 l_total_returned NUMBER;
585 l_rs VARCHAR2(1);
586 l_msg_count NUMBER;
587 l_msg_data VARCHAR2(2000);
588 l_version_num NUMBER;
589 l_status_id NUMBER := 9;
590 x NUMBER;
591
592 recurs JTF_TASKS_PUB.TASK_RECUR_REC;
593 rsc JTF_TASKS_PUB.TASK_RSRC_REQ_TBL;
594 asn JTF_TASKS_PUB.TASK_ASSIGN_TBL;
595 ass JTF_TASKS_PUB.TASK_ASSIGN_TBL;
596 notes JTF_TASKS_PUB.TASK_NOTES_TBL;
597
598 l_sort_data JTF_TASKS_PUB.SORT_DATA;
599
600 -- note-related variables...
601 x_return_status VARCHAR2(2000);
602 x_msg_count NUMBER;
603 x_msg_data VARCHAR2(2000);
604 x_cust_account_id NUMBER;
605 x_cust_account_number VARCHAR2(2000);
606 x_party_id NUMBER;
607 x_party_number VARCHAR2(2000);
608 x_profile_id NUMBER;
609
610
611 l_object_version_number NUMBER := 0;
612 l_return_status VARCHAR2(30);
613 l_note_id NUMBER;
614 l_notes VARCHAR2(4000) := '';
615 l_notes_detail VARCHAR2(32000);
616 context_tab JTF_NOTES_PUB.JTF_NOTE_CONTEXTS_TBL_TYPE;
617
618 BEGIN
619
620 IF( COMPLETION_STATUS = 'ACCEPTED' ) THEN
621 l_status_id := 3;
622 ELSIF( COMPLETION_STATUS = 'REJECTED' ) THEN
623 l_status_id := 4;
624 ELSIF( COMPLETION_STATUS = 'COMPLETED' ) THEN
625 l_status_id := 8;
626 ELSIF( COMPLETION_STATUS = 'CLOSED' ) THEN
627 l_status_id := 9;
628 ELSIF( COMPLETION_STATUS = 'UNASSIGNED' ) THEN
629 l_status_id := 13;
630 END IF;
631
632 SELECT JTF_TASKS_B.OBJECT_VERSION_NUMBER
633 INTO l_object_version_number
634 FROM JTF_TASKS_B
635 WHERE TASK_ID = P_TASK_ID;
636
637
638 --DBMS_OUTPUT.PUT_LINE('Task '||task_id||' status = '||l_status_id||
639 -- ' with completion_status = '||completion_status );
640
641 JTF_TASKS_PUB.update_task(
642 P_API_VERSION => 1.0,
643 P_OBJECT_VERSION_NUMBER => l_object_version_number,
644 P_TASK_STATUS_ID => l_status_id,
645 P_TASK_ID => P_TASK_ID,
649 X_MSG_COUNT => l_msg_count,
646 --P_SOURCE_OBJECT_TYPE_CODE => NULL,
647 --P_SOURCE_OBJECT_TYPE_CODE => 'ISUPPORT',
648 X_RETURN_STATUS => l_rs,
650 X_MSG_DATA => l_msg_data);
651
652 l_msg_data := FND_MSG_PUB.GET(1, 'F' );
653
654 --DBMS_OUTPUT.PUT_LINE('RETURN_STATUS = '||l_rs);
655 --DBMS_OUTPUT.PUT_LINE('X_MSG_COUNT = '||l_msg_count);
656 --DBMS_OUTPUT.PUT_LINE('MSG_DATA = ' ||l_msg_data);
657
658 END update_task_without_note;
659
660
661 PROCEDURE update_party_note(API_VERSION IN NUMBER DEFAULT 1.0,
662 OBJECT_VERSION_NUMBER IN NUMBER DEFAULT 1,
663 PARTY_ID IN NUMBER,
664 COMMENTS IN VARCHAR2) IS
665
666 CURSOR jtf_note_id_cursor IS
667 SELECT JTF_NOTE_ID
668 FROM JTF_NOTES_B
669 WHERE SOURCE_OBJECT_CODE = 'PARTY'
670 AND SOURCE_OBJECT_ID = PARTY_ID
671 ORDER BY 1;
672
673 l_msg_count NUMBER;
674 l_msg_data VARCHAR2(2000);
675
676 x_return_status VARCHAR2(2000);
677 x_msg_count NUMBER;
678 x_msg_data VARCHAR2(2000);
679 x_cust_account_id NUMBER;
680 x_cust_account_number VARCHAR2(2000);
681 x_party_id NUMBER;
682 x_party_number VARCHAR2(2000);
683 x_profile_id NUMBER;
684
685
686 l_jtf_note_id NUMBER := 0;
687 l_return_status VARCHAR2(30);
688 p_note_id NUMBER;
689 l_notes VARCHAR2(4000) := '';
690 l_notes_detail VARCHAR2(32000);
691 context_tab JTF_NOTES_PUB.JTF_NOTE_CONTEXTS_TBL_TYPE;
692
693 BEGIN
694
695 OPEN jtf_note_id_cursor;
696 FETCH jtf_note_id_cursor INTO p_note_id;
697
698 -- notes(1).org_id := 173 ;
699 -- notes(1).notes := COMMENTS;
700 -- notes(1).notes_detail := null;
701 -- notes(1).note_status := null ;
702 -- notes(1).entered_by := -1 ;
703 -- notes(1).entered_date := sysdate ;
704 -- notes(1).note_type := null ;
705 --
706 -- l_notes := COMMENTS;
707
708 WHILE jtf_note_id_cursor%FOUND
709 LOOP
710 --context_tab(1).note_context_id := 8715;
711 context_tab(1).note_context_type_id := PARTY_ID;
712 context_tab(1).note_context_type := 'PARTY';
713 context_tab(1).last_update_date := SYSDATE;
714 context_tab(1).last_updated_by := FND_GLOBAL.USER_ID;
715 context_tab(1).created_by := FND_GLOBAL.USER_ID;
716
717
718 JTF_NOTES_PUB.Update_note
719 ( p_api_version => 1,
720 p_init_msg_list => FND_API.G_FALSE,
721 --p_commit => FND_API.G_FALSE,
722 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
723 x_return_status => l_return_status,
724 x_msg_count => l_msg_count,
725 x_msg_data => l_msg_data,
726 p_jtf_note_id => p_note_id,
727 p_entered_by => NULL,
728 p_last_updated_by => 1000,
729 p_last_update_date => SYSDATE,
730 p_last_update_login => NULL,
731 p_notes => 'Updated thru API',
732 p_notes_detail => COMMENTS,
733 p_append_flag => FND_API.G_MISS_CHAR,
734 p_note_status => FND_API.G_MISS_CHAR,
735 p_note_type => FND_API.G_MISS_CHAR,
736 p_jtf_note_contexts_tab => context_tab
737 );
738
739 --dbms_output.put_line('Api completed '||l_return_status||p_note_id);
740
741 FETCH jtf_note_id_cursor INTO p_note_id;
742
743 END LOOP;
744
745 --l_msg_data := FND_MSG_PUB.GET(1, 'F' );
746 --DBMS_OUTPUT.PUT_LINE('RETURN_STATUS = '||l_rs);
747 --DBMS_OUTPUT.PUT_LINE('X_MSG_COUNT = '||l_msg_count);
748 --DBMS_OUTPUT.PUT_LINE('MSG_DATA = ' ||l_msg_data);
749
750 END UPDATE_PARTY_NOTE;
751
752
753 END TASK_MGR;