[Home] [Help]
PACKAGE BODY: APPS.CSFW_TASKS_PUB
Source
1 PACKAGE BODY csfw_tasks_pub AS
2 /*$Header: csfwtaskb.pls 120.20.12010000.2 2008/11/18 10:13:45 htank ship $*/
3
4 -- Bug # 4570867
5 -- Added this function to check a task type is of DISPATCH rule or not
6 FUNCTION has_field_service_rule (p_task_type_id NUMBER)
7 RETURN BOOLEAN IS
8 CURSOR c_task_type IS
9 SELECT task_type_id
10 FROM jtf_task_types_b
11 WHERE rule = 'DISPATCH'
12 AND NVL (schedule_flag, 'N') = 'Y'
13 AND task_type_id = p_task_type_id;
14 BEGIN
15 FOR v_task_type IN c_task_type LOOP
16 RETURN TRUE;
17 END LOOP;
18 RETURN FALSE;
19 END has_field_service_rule;
20
21 PROCEDURE GET_FOLLOW_UP_TASK_DETAILS
22 ( p_task_id IN NUMBER
23 , x_error_id OUT NOCOPY NUMBER
24 , x_error OUT NOCOPY VARCHAR2
25 , x_task_name OUT NOCOPY varchar2
26 , x_status_id OUT NOCOPY number
27 , x_priority_id OUT NOCOPY number
28 , x_customer_name OUT NOCOPY varchar2
29 , x_request_number OUT NOCOPY varchar2
30 , x_planned_effort_uom OUT NOCOPY varchar2
31 )
32 IS
33 l_task_table_type jtf_tasks_pub.task_table_type;
34 l_total_retrieved number;
35 l_total_returned number;
36 l_return_status varchar2(1);
37 l_msg_count number;
38 l_msg_data varchar2(2000);
39 l_object_version_number number;
40
41 l_task_rec jtf_tasks_pub.task_rec;
42 l_sort_data jtf_tasks_pub.sort_rec ;
43 l_sort_table jtf_tasks_pub.sort_data ;
44
45
46 CURSOR c_version (v_task_id NUMBER)
47 IS
48 select object_version_number from jtf_tasks_b where task_id = v_task_id;
49
50
51 BEGIN
52
53 x_error_id := 0; --Assume success
54
55 --select the object version number
56 open c_version(p_task_id);
57 fetch c_version into l_object_version_number;
58 close c_version;
59 --select object_version_number into l_object_version_number from jtf_tasks_b where task_id = p_task_id;
60
61
62 l_sort_data.field_name := 'TASK_NUMBER'; -- this is redundant as we are passing the primary key
63 l_sort_table (1) := l_sort_data;
64
65 jtf_tasks_pub.query_task
66 (
67 p_api_version => 1.0,
68 p_task_id => p_task_id,
69 p_sort_data => l_sort_table,
70 p_start_pointer => 1,
71 p_rec_wanted => 1,
72 p_show_all => 'Y',
73 x_task_table => l_task_table_type ,
74 x_total_retrieved => l_total_retrieved,
75 x_total_returned => l_total_returned,
76 x_return_status => l_return_status,
77 x_msg_count => l_msg_count,
78 x_msg_data => l_msg_data,
79 x_object_version_number => l_object_version_number
80 );
81
82
83 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
84 /* API-call was successfull */
85 x_error_id := 0;
86 x_error := FND_API.G_RET_STS_SUCCESS;
87
88 -- get the record from table
89 l_task_rec := l_task_table_type(1);
90
91 -- Now that we have the Table
92 x_task_name := nvl(l_task_rec.task_name, '');
93 x_status_id := nvl(l_task_rec.task_status_id, 0);
94 x_priority_id := nvl(l_task_rec.task_priority_id, 0);
95 x_customer_name := nvl(l_task_rec.customer_name, '');
96 x_request_number := nvl(l_task_rec.obect_name, '');
97 x_planned_effort_uom := l_task_rec.planned_effort_uom;
98 ELSE
99 x_error_id := 1;
100 x_error := 'Unexpected Error while Calling the API' ;
101 END IF;
102
103 EXCEPTION
104 WHEN OTHERS
105 THEN
106 x_error_id := -1;
107 x_error := SQLERRM;
108
109 END GET_FOLLOW_UP_TASK_DETAILS;
110
111 PROCEDURE CREATE_FOLLOW_UP_TASK
112 ( p_task_id IN NUMBER
113 , p_task_name IN VARCHAR2
114 , p_status_id IN NUMBER
115 , p_priority_id IN NUMBER
116 , p_Planned_Start_date IN DATE
117 , p_Planned_End_date IN DATE
118 , p_planned_effort IN NUMBER
119 , p_planned_effort_uom IN VARCHAR2
120 , p_notes IN VARCHAR2
121 , x_error_id OUT NOCOPY NUMBER
122 , x_error OUT NOCOPY VARCHAR2
123 , x_follow_up_task_id OUT NOCOPY NUMBER
124 , p_note_type IN VARCHAR2
125 , p_note_status IN VARCHAR2
126 , p_attribute_1 IN VARCHAR2
127 , p_attribute_2 IN VARCHAR2
128 , p_attribute_3 IN VARCHAR2
129 , p_attribute_4 IN VARCHAR2
130 , p_attribute_5 IN VARCHAR2
131 , p_attribute_6 IN VARCHAR2
132 , p_attribute_7 IN VARCHAR2
133 , p_attribute_8 IN VARCHAR2
134 , p_attribute_9 IN VARCHAR2
135 , p_attribute_10 IN VARCHAR2
136 , p_attribute_11 IN VARCHAR2
137 , p_attribute_12 IN VARCHAR2
138 , p_attribute_13 IN VARCHAR2
139 , p_attribute_14 IN VARCHAR2
140 , p_attribute_15 IN VARCHAR2
141 , p_context IN VARCHAR2
142 ) IS
143
144 l_task_id number;
145 l_task_table_type jtf_tasks_pub.task_table_type;
146 l_num_rec number;
147 l_total_retrieved number;
148 l_total_returned number;
149 l_return_status varchar2(1);
150 l_msg_count number;
151 l_msg_data varchar2(2000);
152 l_object_version_number number;
153 l_data varchar2(255);
154 l_task_rec jtf_tasks_pub.task_rec;
155 l_sort_data jtf_tasks_pub.sort_rec ;
156 l_sort_table jtf_tasks_pub.sort_data ;
157 l_planned_start_date date;
158 l_planned_end_date date;
159 L_PLANNED_EFFORT number;
160 L_PLANNED_EFFORT_UOM varchar2(10);
161 l_msg_index_out number;
162 l_task_notes_rec jtf_tasks_pub.task_notes_rec;
163 l_task_notes_tbl jtf_tasks_pub.task_notes_tbl;
164 l_organization_id NUMBER;
165 l_note_type varchar2(30);
166 l_note_status varchar2(1);
167 l_temp varchar2(1);
168 l_access_hrs_id number;
169 l_tmp_task_owner number; -- bug # 4724278
170 l_tmp_task_owner_type varchar2(30); -- bug # 4724278
171
172 CURSOR c_resource_id (v_user_id NUMBER)
173 IS
174 select resource_id
175 from jtf_rs_resource_extns
176 where user_id = v_user_id;
177
178 CURSOR c_resource_type (v_resource_id NUMBER)
179 IS
180 select decode(category, 'EMPLOYEE', 'RS_EMPLOYEE',
181 'PARTNER', 'RS_PARTNER',
182 'SUPPLIER_CONTACT', 'RS_SUPPLIER_CONTACT',
183 'OTHER', 'RS_OTHER',
184 'PARTY', 'RS_PARTY',
185 'TBH', 'RS_TBH',
186 'VENUE', 'RS_VENUE', category)
187 from JTF_RS_RESOURCE_EXTNS
188 where resource_id = v_resource_id ;
189
190 cursor c_check_valid_resource (v_resource_id number, v_resource_type varchar2)
191 is
192 select count(*) from jtf_task_resources_vl
193 where resource_id = v_resource_id and resource_type = v_resource_type;
194
195 l_check_valid_resource number;
196
197 CURSOR FIND_SKILL (V_TASK_ID NUMBER) IS
198 SELECT
199 SKILL_TYPE_ID
200 , SKILL_ID
201 , SKILL_LEVEL_ID , DISABLED_FLAG
202 FROM CSF_REQUIRED_SKILLS_B
203 WHERE HAS_SKILL_TYPE = 'TASK'
204 AND HAS_SKILL_ID = V_TASK_ID;
205
206 R_FIND_SKILL FIND_SKILL%ROWTYPE;
207
208 CURSOR C_CST_ACCESS_HRS_EXISTS(v_task_id number) is select 'Y' from csf_access_hours_vl where task_id=v_task_id;
209 CURSOR C_CST_ACCESS_HRS(v_task_id number) IS select * from CSF_ACCESS_HOURS_VL where task_id = v_task_id;
210
211 R_CST_HRS C_CST_ACCESS_HRS%ROWTYPE;
212
213 CURSOR c_task_location (v_task_id NUMBER)
214 IS
215 Select address_id,
216 location_id
217 from jtf_tasks_b
218 where task_id = v_task_id ;
219
220 r_task_location c_task_location%ROWTYPE;
221
222 begin
223 l_task_id := p_task_id;
224 l_object_version_number := 1;
225 l_sort_data.field_name := 'TASK_NUMBER';
226 l_sort_table (1) := l_sort_data;
227
228 l_planned_start_date := p_Planned_Start_date;
229 l_planned_end_date := p_Planned_End_date;
230 L_PLANNED_EFFORT := p_planned_effort;
231 L_PLANNED_EFFORT_UOM := p_planned_effort_uom;
232
233
234
235 -- Lets Query The Task
236 jtf_tasks_pub.query_task
237 (
238 p_api_version => 1.0,
239 p_task_id => l_task_id,
240 p_sort_data => l_sort_table,
241 p_start_pointer => 1,
242 p_rec_wanted => 1,
243 p_show_all => 'Y',
244 x_task_table => l_task_table_type ,
245 x_total_retrieved => l_total_retrieved,
246 x_total_returned => l_total_returned,
247 x_return_status => l_return_status,
248 x_msg_count => l_msg_count,
249 x_msg_data => l_msg_data,
250 x_object_version_number => l_object_version_number
251 );
252
253 IF l_return_status = FND_API.G_RET_STS_SUCCESS
254 THEN
255 /* API-call was successfull */
256 x_error_id := 0;
257 x_error := FND_API.G_RET_STS_SUCCESS;
258 l_task_rec := l_task_table_type(1);
259
260 --notes
261 if p_notes <> '$$#@'
262 then
263 if p_note_type is null then
264 l_note_type := 'KB_FACT';
265 else
266 l_note_type := p_note_type;
267 end if;
268
269 if p_note_status is null then
270 l_note_status := 'I';
271 else
272 l_note_status := p_note_status;
273 end if;
274
275
276
277 FND_PROFILE.GET ( 'CS_INV_VALIDATION_ORG' , l_organization_id);
278 l_task_notes_rec.org_id := l_organization_id;
279 l_task_notes_rec.notes := p_notes;
280 l_task_notes_rec.note_status := l_note_status;
281 l_task_notes_rec.entered_by := FND_GLOBAL.user_id;
282 l_task_notes_rec.entered_date := sysdate;
283 l_task_notes_rec.note_type := l_note_type;
284 l_task_notes_tbl (1) := l_task_notes_rec;
285
286 else
287 l_task_notes_tbl := jtf_tasks_pub.g_miss_task_notes_tbl;
288 end if;
289
290 -- bug # 4724278
291 -- Use Task Manager profile for Task Owner details
292 -- l_tmp_task_owner := -1;
293 FND_PROFILE.GET('JTF_TASK_DEFAULT_OWNER', l_tmp_task_owner);
294 FND_PROFILE.GET('JTF_TASK_DEFAULT_OWNER_TYPE', l_tmp_task_owner_type);
295
296 open c_check_valid_resource (l_tmp_task_owner, l_tmp_task_owner_type);
297 fetch c_check_valid_resource into l_check_valid_resource;
298 close c_check_valid_resource;
299
300 IF l_tmp_task_owner IS NULL
301 or l_tmp_task_owner_type is null
302 or l_check_valid_resource is null
303 or l_check_valid_resource <> 1 THEN
304
305 -- resource id
306 open c_resource_id (FND_GLOBAL.USER_ID);
307 fetch c_resource_id into l_tmp_task_owner;
308 close c_resource_id;
309
310 -- resource type
311 open c_resource_type (l_tmp_task_owner);
312 fetch c_resource_type into l_tmp_task_owner_type;
313 close c_resource_type;
314 END IF;
315
316 -- Address/location
317 open c_task_location (l_task_id);
318 fetch c_task_location into r_task_location;
319 close c_task_location;
320
321
322 -- bug # 4570867
323 -- use csf_task_pub instead of jtf_task_pub
324 csf_tasks_pub.create_task (
325 p_api_version => 1.0,
326 p_commit => fnd_api.g_true,
327 p_task_name => p_task_name ,
328 p_task_type_name => l_task_rec.task_type,
329 p_task_type_id => l_task_rec.task_type_id,
330 p_description => null,
331 p_task_status_id => p_status_id,
332 p_task_priority_id => p_priority_id,
333 p_owner_type_code => l_tmp_task_owner_type, -- bug # 4724278
334 p_owner_id => l_tmp_task_owner, -- bug # 4724278
335 -- p_owner_territory_id => l_task_rec.owner_territory_id,
336 p_customer_number => l_task_rec.customer_number,
337 p_customer_id => l_task_rec.customer_id,
338 p_cust_account_number => l_task_rec.cust_account_number,
339 p_cust_account_id => l_task_rec.cust_account_id,
340 p_address_id => r_task_location.address_id,
341 p_location_id => r_task_location.location_id,
342 p_planned_start_date => l_planned_Start_Date,
343 p_planned_end_date => l_planned_End_Date,
344 p_source_object_type_code => l_task_rec.object_type_code,
345 p_source_object_id => l_task_rec.object_id,
346 p_source_object_name => l_task_rec.obect_name,
347 p_planned_effort => L_PLANNED_EFFORT,
348 p_planned_effort_uom => L_PLANNED_EFFORT_UOM,
349 p_percentage_complete => l_task_rec.percentage_complete,
350 p_reason_code => l_task_rec.reason_code,
351 p_private_flag => l_task_rec.private_flag,
352 p_publish_flag => l_task_rec.publish_flag,
353 p_multi_booked_flag => l_task_rec.multi_booked_flag,
354 p_milestone_flag => l_task_rec.milestone_flag,
355 p_holiday_flag => l_task_rec.holiday_flag,
356 p_workflow_process_id => l_task_rec.workflow_process_id,
357 p_notification_flag => l_task_rec.notification_flag,
358 p_notification_period => l_task_rec.notification_period,
359 p_notification_period_uom => l_task_rec.notification_period_uom,
360 p_alarm_start => l_task_rec.alarm_start,
361 p_alarm_start_uom => l_task_rec.alarm_start_uom,
362 p_alarm_on => l_task_rec.alarm_on,
363 p_alarm_count => l_task_rec.alarm_count,
364 p_alarm_interval => l_task_rec.alarm_interval,
368 x_return_status => l_return_status,
365 p_alarm_interval_uom => l_task_rec.alarm_interval_uom,
366 p_escalation_level => l_task_rec.escalation_level,
367 p_task_notes_tbl => l_task_notes_tbl ,
369 x_msg_count => l_msg_count,
370 x_msg_data => l_msg_data,
371 x_task_id => x_follow_up_task_id,
372 p_attribute1 => p_attribute_1,
373 p_attribute2 => p_attribute_2,
374 p_attribute3 => p_attribute_3,
375 p_attribute4 => p_attribute_4,
376 p_attribute5 => p_attribute_5,
377 p_attribute6 => p_attribute_6,
378 p_attribute7 => p_attribute_7,
379 p_attribute8 => p_attribute_8,
380 p_attribute9 => p_attribute_9,
381 p_attribute10 => p_attribute_10,
382 p_attribute11 => p_attribute_11,
383 p_attribute12 => p_attribute_12,
384 p_attribute13 => p_attribute_13,
385 p_attribute14 => p_attribute_14,
386 p_attribute15 => p_attribute_15,
387 p_attribute_category => p_context,
388 p_date_selected => l_task_rec.date_selected
389 );
390
391 IF l_return_status = FND_API.G_RET_STS_SUCCESS
392 THEN
393 /* API-call was successfull */
394 x_error_id := 0;
395 x_error := FND_API.G_RET_STS_SUCCESS;
396
397 --ADding up skills (Bug 3290577)
398
399 FOR R_FIND_SKILL IN FIND_SKILL (l_task_id)
400 LOOP
401 CSF_REQUIRED_SKILLS_PKG.CREATE_ROW
402 ( P_API_VERSION => 1.0
403 , P_INIT_MSG_LIST => FND_API.G_FALSE
404 , P_COMMIT => fnd_api.g_true
405 , P_VALIDATION_LEVEL => 100
406 , X_RETURN_STATUS => l_return_status
407 , X_MSG_COUNT => L_MSG_COUNT
408 , X_MSG_DATA => L_MSG_DATA
409 , P_TASK_ID => x_follow_up_task_id
410 , P_SKILL_TYPE_ID => R_FIND_SKILL.SKILL_TYPE_ID
411 , P_SKILL_ID => R_FIND_SKILL.SKILL_ID
412 , P_SKILL_LEVEL_ID => R_FIND_SKILL.SKILL_LEVEL_ID);
413
414
415 -- ERROR PROCESSING IF THERE ARE ANY ERRORS WHILE CALLING THE API
416
417 IF (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
418 x_error_id := 0;
419 x_error := FND_API.G_RET_STS_SUCCESS;
420 /*
421 lets update the row with the disable flag
422 This is a temporary fix as there is no API from CSF_SKILL
423 to update the row or set the DISABLE_FLAG while creating
424 To be removed once that is in. A bug is filed (# 3292940)
425 */
426 IF (R_FIND_SKILL.DISABLED_FLAG = 'Y') THEN
427 UPDATE CSF_REQUIRED_SKILLS_B
428 SET DISABLED_FLAG = 'Y'
432 AND SKILL_LEVEL_ID = R_FIND_SKILL.SKILL_LEVEL_ID;
429 WHERE SKILL_ID = R_FIND_SKILL.SKILL_ID
430 AND HAS_SKILL_ID = x_follow_up_task_id
431 AND SKILL_TYPE_ID = R_FIND_SKILL.SKILL_TYPE_ID
433
434 END IF;
435 commit work;
436 ELSE
437 FOR l_counter IN 1 .. l_msg_count
438 LOOP
439 fnd_msg_pub.get
440 ( p_msg_index => l_counter
441 , p_encoded => FND_API.G_FALSE
442 , p_data => l_data
443 , p_msg_index_out => l_msg_index_out
444 );
445 --dbms_output.put_line( 'Message: '||l_data );
446 END LOOP ;
447 x_error_id := 22;
448 x_error := l_data;
449 END IF;
450 END LOOP;
451
452 -- copy customer access hours
453 open C_CST_ACCESS_HRS_EXISTS(l_task_id);
454 fetch C_CST_ACCESS_HRS_EXISTS into l_temp;
455 if not C_CST_ACCESS_HRS_EXISTS%notfound then
456 -- copy access hours
457 FOR R_CST_HRS in C_CST_ACCESS_HRS(l_task_id)
458 LOOP
459 CSF_ACCESS_HOURS_PUB.CREATE_ACCESS_HOURS(
460 x_ACCESS_HOUR_ID => l_access_hrs_id,
461 p_API_VERSION => 1.0,
462 p_init_msg_list => 'F',
463 p_TASK_ID => x_follow_up_task_id,
464 p_ACCESS_HOUR_REQD => R_CST_HRS.ACCESSHOUR_REQUIRED,
465 p_AFTER_HOURS_FLAG => R_CST_HRS.AFTER_HOURS_FLAG,
466 p_MONDAY_FIRST_START => R_CST_HRS.MONDAY_FIRST_START,
467 p_MONDAY_FIRST_END => R_CST_HRS.MONDAY_FIRST_END,
468 p_MONDAY_SECOND_START => R_CST_HRS.MONDAY_SECOND_START,
469 p_MONDAY_SECOND_END => R_CST_HRS.MONDAY_SECOND_END,
470 p_TUESDAY_FIRST_START => R_CST_HRS.TUESDAY_FIRST_START,
471 p_TUESDAY_FIRST_END => R_CST_HRS.TUESDAY_FIRST_END,
472 p_TUESDAY_SECOND_START => R_CST_HRS.TUESDAY_SECOND_START,
473 p_TUESDAY_SECOND_END => R_CST_HRS.TUESDAY_SECOND_END,
474 p_WEDNESDAY_FIRST_START => R_CST_HRS.WEDNESDAY_FIRST_START,
475 p_WEDNESDAY_FIRST_END => R_CST_HRS.WEDNESDAY_FIRST_END,
476 p_WEDNESDAY_SECOND_START => R_CST_HRS.WEDNESDAY_SECOND_START,
477 p_WEDNESDAY_SECOND_END => R_CST_HRS.WEDNESDAY_SECOND_END,
478 p_THURSDAY_FIRST_START => R_CST_HRS.THURSDAY_FIRST_START,
479 p_THURSDAY_FIRST_END => R_CST_HRS.THURSDAY_FIRST_END,
480 p_THURSDAY_SECOND_START => R_CST_HRS.THURSDAY_SECOND_START,
481 p_THURSDAY_SECOND_END => R_CST_HRS.THURSDAY_SECOND_END,
482 p_FRIDAY_FIRST_START => R_CST_HRS.FRIDAY_FIRST_START,
483 p_FRIDAY_FIRST_END => R_CST_HRS.FRIDAY_FIRST_END,
484 p_FRIDAY_SECOND_START => R_CST_HRS.FRIDAY_SECOND_START,
485 p_FRIDAY_SECOND_END => R_CST_HRS.FRIDAY_SECOND_END,
486 p_SATURDAY_FIRST_START => R_CST_HRS.SATURDAY_FIRST_START,
487 p_SATURDAY_FIRST_END => R_CST_HRS.SATURDAY_FIRST_END,
488 p_SATURDAY_SECOND_START => R_CST_HRS.SATURDAY_SECOND_START,
489 p_SATURDAY_SECOND_END => R_CST_HRS.SATURDAY_SECOND_END,
490 p_SUNDAY_FIRST_START => R_CST_HRS.SUNDAY_FIRST_START,
491 p_SUNDAY_FIRST_END => R_CST_HRS.SUNDAY_FIRST_END,
492 p_SUNDAY_SECOND_START => R_CST_HRS.SUNDAY_SECOND_START,
493 p_SUNDAY_SECOND_END => R_CST_HRS.SUNDAY_SECOND_END,
494 p_DESCRIPTION => R_CST_HRS.DESCRIPTION,
495 px_object_version_number => l_object_version_number,
496 p_CREATED_BY => FND_GLOBAL.user_id,
497 p_CREATION_DATE => sysdate,
498 p_LAST_UPDATED_BY => FND_GLOBAL.user_id,
499 p_LAST_UPDATE_DATE => sysdate,
500 p_LAST_UPDATE_LOGIN => FND_GLOBAL.user_id,
501 x_return_status => l_return_status,
502 x_msg_data => l_msg_data,
503 x_msg_count => l_msg_count
504 );
505
506 IF (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
507 x_error_id := 0;
508 x_error := FND_API.G_RET_STS_SUCCESS;
509 ELSE
510 FOR l_counter IN 1 .. l_msg_count
511 LOOP
512 fnd_msg_pub.get
513 ( p_msg_index => l_counter
514 , p_encoded => FND_API.G_FALSE
515 , p_data => l_data
516 , p_msg_index_out => l_msg_index_out
517 );
521 END IF;
518 END LOOP ;
519 x_error_id := 22;
520 x_error := l_data;
522
523 END LOOP;
524 end if;
525 close C_CST_ACCESS_HRS_EXISTS;
526
527 ELSE
528 FOR l_counter IN 1 .. l_msg_count
529 LOOP
530 fnd_msg_pub.get
531 ( p_msg_index => l_counter
532 , p_encoded => FND_API.G_FALSE
533 , p_data => l_data
534 , p_msg_index_out => l_msg_index_out
535 );
536 --dbms_output.put_line( 'Message: '||l_data );
537 END LOOP ;
538 x_error_id := 2;
539 x_error := l_data;
540 x_follow_up_task_id := 0; -- no tasks
541 END IF;
542
543 ELSE
544 FOR l_counter IN 1 .. l_msg_count
545 LOOP
546 fnd_msg_pub.get
547 ( p_msg_index => l_counter
548 , p_encoded => FND_API.G_FALSE
549 , p_data => l_data
550 , p_msg_index_out => l_msg_index_out
551 );
552 --dbms_output.put_line( 'Message: '||l_data );
553 END LOOP ;
554 x_error_id := 3;
555 x_error := l_data;
556 x_follow_up_task_id := 0; -- no tasks
557 END IF;
558
559 EXCEPTION
560 WHEN OTHERS
561 THEN
562 x_error_id := -1;
563 x_error := p_notes||' ' ||SQLERRM;
564
565 END CREATE_FOLLOW_UP_TASK;
566
567
568 PROCEDURE CREATE_NEW_TASK
569 ( p_task_name IN VARCHAR2
570 , p_task_type_id IN NUMBER
571 , p_status_id IN NUMBER
572 , p_priority_id IN NUMBER
573 , p_assign_to_me IN VARCHAR2
574 , p_Planned_Start_date IN DATE
575 , p_planned_effort IN NUMBER
576 , p_planned_effort_uom IN VARCHAR2
577 , p_notes IN VARCHAR2
578 , p_source_object_id IN NUMBER
579 , x_error_id OUT NOCOPY NUMBER
580 , x_error OUT NOCOPY VARCHAR2
581 , x_new_task_id OUT NOCOPY NUMBER
582 , p_note_type IN VARCHAR2
583 , p_note_status IN VARCHAR2
584 , p_Planned_End_date IN DATE
585 , p_attribute_1 IN VARCHAR2
586 , p_attribute_2 IN VARCHAR2
587 , p_attribute_3 IN VARCHAR2
588 , p_attribute_4 IN VARCHAR2
589 , p_attribute_5 IN VARCHAR2
590 , p_attribute_6 IN VARCHAR2
591 , p_attribute_7 IN VARCHAR2
592 , p_attribute_8 IN VARCHAR2
593 , p_attribute_9 IN VARCHAR2
594 , p_attribute_10 IN VARCHAR2
595 , p_attribute_11 IN VARCHAR2
596 , p_attribute_12 IN VARCHAR2
597 , p_attribute_13 IN VARCHAR2
598 , p_attribute_14 IN VARCHAR2
599 , p_attribute_15 IN VARCHAR2
600 , p_context IN VARCHAR2
601 ) IS
602
603 l_task_type_name varchar2(30);
604 l_return_status varchar2(1);
605 l_msg_count number;
606 l_msg_data varchar2(2000);
607
608 l_data varchar2(255);
609 l_task_notes_rec jtf_tasks_pub.task_notes_rec;
610 l_task_notes_tbl jtf_tasks_pub.task_notes_tbl;
611 l_msg_index_out number;
612 l_location_id number;
613 l_address_id number;
614
615 l_resource_id number;
616 l_resource_type varchar2(30);
617 l_assign_by_id number;
618 l_scheduled_start_date DATE;
619 l_scheduled_end_date DATE;
620 l_incident_number VARCHAR2(64);
621 l_organization_id NUMBER;
622 l_note_type varchar2(30);
623 l_note_status varchar2(1);
624 -- bug # 5182676
625 l_customer_id number;
626
627 cursor c_customer_id (v_incident_id number)
628 is
629 select customer_id from cs_incidents_all where incident_id = v_incident_id;
630
631 CURSOR c_task_type (v_task_type_id NUMBER)
632 IS
633 Select name
634 from jtf_task_types_vl
635 where TASK_TYPE_ID = v_task_type_id;
636
637 CURSOR c_resource_id (v_user_id NUMBER)
638 IS
639 select resource_id
640 from jtf_rs_resource_extns
641 where user_id = v_user_id;
642
643 CURSOR c_resource_type (v_resource_id NUMBER)
644 IS
645 select decode(category, 'EMPLOYEE', 'RS_EMPLOYEE',
646 'PARTNER', 'RS_PARTNER',
647 'SUPPLIER_CONTACT', 'RS_SUPPLIER_CONTACT',
648 'OTHER', 'RS_OTHER',
649 'PARTY', 'RS_PARTY',
650 'TBH', 'RS_TBH',
651 'VENUE', 'RS_VENUE', category)
652 from JTF_RS_RESOURCE_EXTNS
653 where resource_id = v_resource_id ;
654
655 cursor c_check_valid_resource (v_resource_id number, v_resource_type varchar2)
656 is
657 select count(*) from jtf_task_resources_vl
658 where resource_id = v_resource_id and resource_type = v_resource_type;
659
660 l_check_valid_resource number;
661
662 CURSOR c_incident_number (v_incident_id NUMBER)
663 IS
664 Select incident_number,
665 incident_location_type,
666 incident_location_id
667 from cs_incidents_all
668 where incident_id = v_incident_id ;
669
673
670 r_incident_record c_incident_number%ROWTYPE;
671
672 BEGIN
674 -- get the task type name
675 open c_task_type(p_task_type_id);
676 fetch c_task_type into l_task_type_name;
677 close c_task_type;
678
679
680 --notes
681 if p_notes <> '$$#@'
682 then
683 if p_note_type is null then
684 l_note_type := 'KB_FACT';
685 else
686 l_note_type := p_note_type;
687 end if;
688
689 if p_note_status is null then
690 l_note_status := 'I';
691 else
692 l_note_status := p_note_status;
693 end if;
694
695 FND_PROFILE.GET ( 'CS_INV_VALIDATION_ORG' , l_organization_id);
696 l_task_notes_rec.org_id := l_organization_id;
697 l_task_notes_rec.notes := p_notes;
698 l_task_notes_rec.note_status := l_note_status;
699 l_task_notes_rec.entered_by := FND_GLOBAL.user_id;
700 l_task_notes_rec.entered_date := sysdate;
701 l_task_notes_rec.note_type := l_note_type;
702 l_task_notes_tbl (1) := l_task_notes_rec;
703 else
704 l_task_notes_tbl := jtf_tasks_pub.g_miss_task_notes_tbl;
705 end if;
706
707 -- bug # 4724278
708 -- Use Task Manager profile for Task Owner details
709 l_resource_id := -1;
710 FND_PROFILE.GET('JTF_TASK_DEFAULT_OWNER', l_resource_id);
711 FND_PROFILE.GET('JTF_TASK_DEFAULT_OWNER_TYPE', l_resource_type);
712
713 open c_check_valid_resource (l_resource_id, l_resource_type);
714 fetch c_check_valid_resource into l_check_valid_resource;
715 close c_check_valid_resource;
716
717 IF l_resource_id IS NULL
718 or l_resource_type is NULL
719 or l_check_valid_resource is NULL
720 or l_check_valid_resource <> 1 THEN
721
722 -- resource id
723 open c_resource_id (FND_GLOBAL.USER_ID);
724 fetch c_resource_id into l_resource_id;
725 close c_resource_id;
726
727 -- resource type
728 open c_resource_type (l_resource_id);
729 fetch c_resource_type into l_resource_type;
730 close c_resource_type;
731 END IF;
732
733 -- SR number
734 open c_incident_number (p_source_object_id);
735 fetch c_incident_number into r_incident_record;
736 close c_incident_number;
737 l_incident_number := r_incident_record.incident_number;
738
739 IF (r_incident_record.incident_location_type = 'HZ_LOCATION')THEN
740 l_location_id := r_incident_record.incident_location_id;
741 l_address_id := null;
742 ELSE
743 l_location_id := null;
744 l_address_id := r_incident_record.incident_location_id;
745 END IF;
746
747
748 -- bug # 5182676
749 l_customer_id := null;
750 open c_customer_id (p_source_object_id);
751 fetch c_customer_id into l_customer_id;
752 close c_customer_id;
753
754
755 -- Lets call the API
756 -- bug # 4570867
757 -- use csf_task_pub instead of jtf_task_pub
758 -- for DISPATCH type task
759
760 IF has_field_service_rule (p_task_type_id) THEN
761
762 csf_tasks_pub.create_task (
763 p_api_version => 1.0,
764 p_commit => fnd_api.g_true,
765 p_task_name => p_task_name,
766 p_task_type_name => l_task_type_name,
767 p_task_type_id => p_task_type_id,
768 p_task_status_id => p_status_id,
769 p_task_priority_id => p_priority_id,
770 p_owner_type_code => l_resource_type,
771 p_owner_id => l_resource_id,
772 p_assigned_by_id => l_assign_by_id,
773 p_planned_start_date => p_Planned_Start_date,
774 p_planned_end_date => p_Planned_End_date,
775 p_scheduled_start_date => l_scheduled_start_date,
776 p_scheduled_end_date => l_scheduled_end_date,
777 p_source_object_type_code => 'SR',
778 p_source_object_id => p_source_object_id,
779 p_customer_id => l_customer_id, -- bug # 5182676
780 p_address_id => l_address_id,
781 p_location_id => l_location_id,
782 p_source_object_name => l_incident_number,
783 p_planned_effort => p_planned_effort,
784 p_planned_effort_uom => p_planned_effort_uom,
785 p_task_notes_tbl => l_task_notes_tbl,
786 x_return_status => l_return_status,
787 x_msg_count => l_msg_count,
788 x_msg_data => l_msg_data,
789 x_task_id => x_new_task_id,
790 p_attribute1 => p_attribute_1,
791 p_attribute2 => p_attribute_2,
792 p_attribute3 => p_attribute_3,
793 p_attribute4 => p_attribute_4,
794 p_attribute5 => p_attribute_5,
795 p_attribute6 => p_attribute_6,
796 p_attribute7 => p_attribute_7,
797 p_attribute8 => p_attribute_8,
798 p_attribute9 => p_attribute_9,
799 p_attribute10 => p_attribute_10,
800 p_attribute11 => p_attribute_11,
801 p_attribute12 => p_attribute_12,
802 p_attribute13 => p_attribute_13,
803 p_attribute14 => p_attribute_14,
804 p_attribute15 => p_attribute_15,
805 p_attribute_category => p_context
806 );
807 ELSE
808 -- call JTF for non DISPATCH type task
812 p_task_name => p_task_name,
809 jtf_tasks_pub.create_task (
810 p_api_version => 1.0,
811 p_commit => fnd_api.g_true,
813 p_task_type_name => l_task_type_name,
814 p_task_type_id => p_task_type_id,
815 p_description => '',
816 p_task_status_name => null,
817 p_task_status_id => p_status_id,
818 p_task_priority_name => null,
819 p_task_priority_id => p_priority_id,
820 p_owner_type_name => Null,
821 p_owner_type_code => l_resource_type,
822 p_owner_id => l_resource_id,
823 p_owner_territory_id => null,
824 p_assigned_by_name => NULL,
825 p_assigned_by_id => l_assign_by_id,
826 p_customer_number => null,
827 p_customer_id => l_customer_id, -- bug # 5182676
828 p_cust_account_number => null,
829 p_cust_account_id => null,
830 p_address_id => l_address_id,
831 p_location_id => l_location_id,
832 p_planned_start_date => p_Planned_Start_date,
833 p_planned_end_date => p_Planned_End_date,
834 p_scheduled_start_date => l_scheduled_start_date,
835 p_scheduled_end_date => l_scheduled_end_date,
836 p_actual_start_date => NULL,
837 p_actual_end_date => NULL,
838 p_timezone_id => NULL,
839 p_timezone_name => NULL,
840 p_source_object_type_code => 'SR',
841 p_source_object_id => p_source_object_id,
842 p_source_object_name => l_incident_number,
843 p_duration => null,
844 p_duration_uom => null,
845 p_planned_effort => p_planned_effort,
846 p_planned_effort_uom => p_planned_effort_uom,
847 p_actual_effort => NULL,
848 p_actual_effort_uom => NULL,
849 p_percentage_complete => null,
850 p_reason_code => null,
851 p_private_flag => null,
852 p_publish_flag => null,
853 p_restrict_closure_flag => NULL,
854 p_multi_booked_flag => NULL,
855 p_milestone_flag => NULL,
856 p_holiday_flag => NULL,
857 p_billable_flag => NULL,
858 p_bound_mode_code => null,
859 p_soft_bound_flag => null,
860 p_workflow_process_id => NULL,
861 p_notification_flag => NULL,
862 p_notification_period => NULL,
863 p_notification_period_uom => NULL,
864 p_parent_task_number => null,
865 p_parent_task_id => NULL,
866 p_alarm_start => NULL,
867 p_alarm_start_uom => NULL,
868 p_alarm_on => NULL,
869 p_alarm_count => NULL,
870 p_alarm_interval => NULL,
871 p_alarm_interval_uom => NULL,
872 p_palm_flag => NULL,
873 p_wince_flag => NULL,
874 p_laptop_flag => NULL,
875 p_device1_flag => NULL,
876 p_device2_flag => NULL,
877 p_device3_flag => NULL,
878 p_costs => NULL,
879 p_currency_code => NULL,
880 p_escalation_level => NULL,
881 p_task_notes_tbl => l_task_notes_tbl,
882 x_return_status => l_return_status,
883 x_msg_count => l_msg_count,
884 x_msg_data => l_msg_data,
885 x_task_id => x_new_task_id,
886 p_attribute1 => p_attribute_1,
887 p_attribute2 => p_attribute_2,
888 p_attribute3 => p_attribute_3,
889 p_attribute4 => p_attribute_4,
890 p_attribute5 => p_attribute_5,
891 p_attribute6 => p_attribute_6,
892 p_attribute7 => p_attribute_7,
893 p_attribute8 => p_attribute_8,
894 p_attribute9 => p_attribute_9,
895 p_attribute10 => p_attribute_10,
896 p_attribute11 => p_attribute_11,
897 p_attribute12 => p_attribute_12,
898 p_attribute13 => p_attribute_13,
899 p_attribute14 => p_attribute_14,
900 p_attribute15 => p_attribute_15,
901 p_attribute_category => p_context,
902 p_date_selected => NULL,
903 p_category_id => null,
904 p_show_on_calendar => null,
905 p_owner_status_id => null,
906 p_template_id => null,
907 p_template_group_id => null
908 );
909 END IF;
910
911 IF l_return_status = FND_API.G_RET_STS_SUCCESS
912 THEN
913 /* API-call was successfull */
914 x_error_id := 0;
915 x_error := FND_API.G_RET_STS_SUCCESS;
916 ELSE
917 FOR l_counter IN 1 .. l_msg_count
918 LOOP
919 fnd_msg_pub.get
920 ( p_msg_index => l_counter
921 , p_encoded => FND_API.G_FALSE
922 , p_data => l_data
926 END LOOP ;
923 , p_msg_index_out => l_msg_index_out
924 );
925 --dbms_output.put_line( 'Message: '||l_data );
927 x_error_id := 2;
928 x_error := l_data;
929 x_new_task_id := 0; -- no tasks
930 END IF;
931
932
933 EXCEPTION
934 WHEN OTHERS
935 THEN
936 x_error_id := -1;
937 x_error := SQLERRM;
938
939
940 END CREATE_NEW_TASK;
941
942
943 PROCEDURE CREATE_NEW_SR
944 ( p_old_incident_id IN NUMBER
945 , p_incident_type_id IN NUMBER
946 , p_status_id IN NUMBER
947 , p_severity_id IN NUMBER
948 , p_summary IN VARCHAR2
949 , p_instance_id IN NUMBER
950 , p_inv_item_id IN NUMBER
951 , p_serial_number IN VARCHAR2
952 , p_notes IN VARCHAR2
953 , x_new_incident_id OUT NOCOPY NUMBER
954 , x_incident_number OUT NOCOPY VARCHAR2
955 , x_error_id OUT NOCOPY NUMBER
956 , x_error OUT NOCOPY VARCHAR2
957 , x_msg_count OUT NOCOPY NUMBER
958 , x_return_status OUT NOCOPY VARCHAR2
959 , p_contact_id IN NUMBER
960 , p_external_reference IN VARCHAR2
961 , p_prob_code IN VARCHAR2 -- Addition for inserting problem code
962 , p_cust_po_number IN varchar2 -- Bug 5059169
963 , p_attribute_1 IN VARCHAR2 -- Addition for insert DFF data with create SR
964 , p_attribute_2 IN VARCHAR2
965 , p_attribute_3 IN VARCHAR2
966 , p_attribute_4 IN VARCHAR2
967 , p_attribute_5 IN VARCHAR2
968 , p_attribute_6 IN VARCHAR2
969 , p_attribute_7 IN VARCHAR2
970 , p_attribute_8 IN VARCHAR2
971 , p_attribute_9 IN VARCHAR2
972 , p_attribute_10 IN VARCHAR2
973 , p_attribute_11 IN VARCHAR2
974 , p_attribute_12 IN VARCHAR2
975 , p_attribute_13 IN VARCHAR2
976 , p_attribute_14 IN VARCHAR2
977 , p_attribute_15 IN VARCHAR2
978 , p_context IN VARCHAR2
979 )IS
980
981
982 l_api_name CONSTANT VARCHAR2(30) := 'Create_new_SR';
983 l_api_version_number CONSTANT NUMBER := 1.0;
984 l_return_status_full VARCHAR2(1);
985 l_access_flag VARCHAR2(1);
986 l_sqlcode Number;
987 l_sqlerrm Varchar2(2000);
988 l_service_request_rec cs_servicerequest_pub.service_request_rec_type;
989 l_notes_table cs_servicerequest_pub.notes_table;
990 l_notes_rec cs_servicerequest_pub.notes_rec;
991 l_contacts_table cs_servicerequest_pub.contacts_table;
992 l_msg_index_out number;
993 l_task_id NUMBER;
994 l_index NUMBER := 0;
995 l_primary_contact VARCHAR2(1);
996 l_organization_id NUMBER;
997 x_interaction_id Number;
998 x_workflow_process_id Number;
999 x_individual_owner Number;
1000 x_group_owner Number;
1001 x_individual_type Varchar2(200);
1002 x_object_version_number Number;
1003 x_reciprocal_link_id Number;
1004 x_link_id Number;
1005 x_pm_conf_reqd Varchar2(1);
1006 X_Msg_Data VARCHAR2(2000);
1007 l_data VARCHAR2(255);
1008 X_MSG_INDEX_OUT NUMBER;
1009
1010 l_caller_type VARCHAR2(100);
1011 l_party_id NUMBER;
1012 l_install_site_id NUMBER;
1013 l_party_acc_id NUMBER;
1014 l_external_ref VARCHAR2(30);
1015 l_system_id NUMBER;
1016 l_bill_to_site_id NUMBER;
1017 l_ship_to_site_id NUMBER;
1018 l_bill_to_party_id NUMBER;
1019 l_ship_to_party_id NUMBER;
1020
1021 /* File Handling
1022 debug_handler utl_file.file_type;
1023 debug_handler1 utl_file.file_type;
1024 l_utl_dir varchar2(1000) := '/sqlcom/outbound/SRVDEVR9';
1025 */
1026
1027 l_party_type VARCHAR2(30);
1028 l_next_val number;
1029 l_context VARCHAR2(30);
1030
1031 CURSOR c_incident_record( v_incident_id number)
1032 IS
1033
1034 Select caller_type,
1035 customer_id ,
1036 install_site_id ,
1037 account_id ,
1038 external_reference,
1039 system_id ,
1040 bill_to_site_id ,
1041 ship_to_site_id ,
1042 bill_to_party_id ,
1043 ship_to_party_id ,
1044 incident_location_type,
1045 incident_location_id,
1046 -- bug # 5182686
1047 customer_email_id,
1048 customer_phone_id,
1049 category_set_id,
1050 category_id
1051 from cs_incidents_all
1052 where incident_id = v_incident_id;
1053
1054 r_incident_record c_incident_record%ROWTYPE;
1055
1056 -- bug # 5182686
1057 cursor c_hz_contact_points (v_party_id number, v_contact_type varchar2) is
1058 select contact_point_id
1059 from
1060 HZ_PARTY_CONTACT_POINTS_V
1061 where
1062 party_id = v_party_id
1063 and contact_point_type = v_contact_type
1064 and primary_flag = 'Y';
1065
1066 l_customer_phone_id number;
1067 l_customer_email_id number;
1068
1069 -- bug # 5182686 for item category
1070 cursor c_item_category (v_inv_item_id number, v_cat_set_id number, v_org_id number ) is
1071 select
1072 category_id
1073 from
1074 MTL_ITEM_CATEGORIES
1075 where
1076 INVENTORY_ITEM_ID = v_inv_item_id
1077 and organization_id = v_org_id
1078 and category_set_id = v_cat_set_id;
1079
1083 CURSOR c_party_type( v_contact_id number) IS
1080 l_category_set_id number;
1081 l_category_id number;
1082
1084 SELECT party_type FROM hz_parties WHERE party_id = v_contact_id;
1085
1086 CURSOR c_contact_points_next IS
1087 SELECT CS_HZ_SR_CONTACT_POINTS_S.NEXTVAL FROM DUAL;
1088
1089 cursor c_from_ib_instance(v_instance_id number) is
1090 SELECT
1091 LOCATION_ID,
1092 LOCATION_TYPE_CODE,
1093 OWNER_PARTY_ID,
1094 OWNER_PARTY_ACCOUNT_ID,
1095 SYSTEM_ID,
1096 EXTERNAL_REFERENCE
1097 from csi_item_instances
1098 where instance_id = v_instance_id;
1099
1100 r_from_ib_instance c_from_ib_instance%ROWTYPE;
1101
1102
1103
1104 cursor c_bill_to_ship_to (v_instance_id number, v_type varchar2) is
1105 SELECT
1106 c.party_id party_id,
1107 nvl(a.party_site_id , 0) party_site_id
1108 from hz_party_sites a, hz_party_site_uses b ,csi_i_parties c
1109 where a.party_id(+) = c.party_id
1110 and c.RELATIONSHIP_TYPE_CODE = v_type
1111 and c.instance_id = v_instance_id
1112 and b.party_site_id(+) = a.party_site_id
1113 and b.site_use_type(+) = v_type
1114 and b.status(+) = 'A'
1115 and trunc(SYSDATE) BETWEEN TRUNC(NVL(b.begin_date,SYSDATE)) and
1116 TRUNC(NVL(b.end_date,SYSDATE));
1117
1118 r_bill_to_ship_to c_bill_to_ship_to%ROWTYPE;
1119
1120
1121
1122 cursor c_install_site(v_party_id number, v_location_id number ) is
1123 select party_site_id
1124 from hz_party_sites
1125 where party_id = v_party_id
1126 and location_id = v_location_id;
1127
1128 BEGIN
1129 FND_PROFILE.GET ( 'CS_INV_VALIDATION_ORG' , l_organization_id);
1130 cs_servicerequest_pub.initialize_rec(l_service_request_rec);
1131 --keep on Selecting
1132
1133 IF (p_old_incident_id <> 0) THEN
1134 OPEN c_incident_record(p_old_incident_id);
1135 FETCH c_incident_record INTO r_incident_record;
1136 CLOSE c_incident_record;
1137
1138 l_caller_type := r_incident_record.caller_type;
1139 l_party_id := r_incident_record.customer_id;
1140 l_install_site_id := r_incident_record.install_site_id;
1141 l_party_acc_id := r_incident_record.account_id;
1142 l_external_ref := r_incident_record.external_reference;
1143 l_system_id := r_incident_record.system_id;
1144 l_bill_to_site_id := r_incident_record.bill_to_site_id;
1145 l_ship_to_site_id := r_incident_record.ship_to_site_id;
1146 l_bill_to_party_id := r_incident_record.bill_to_party_id;
1147 l_ship_to_party_id := r_incident_record.ship_to_party_id;
1148 l_service_request_rec.incident_location_type := r_incident_record.incident_location_type;
1149 l_service_request_rec.incident_location_id := r_incident_record.incident_location_id;
1150
1151 -- bug # 5182686
1152 l_service_request_rec.customer_phone_id := r_incident_record.customer_phone_id;
1153 l_service_request_rec.customer_email_id := r_incident_record.customer_email_id;
1154 l_service_request_rec.category_set_id := r_incident_record.category_set_id;
1155 l_service_request_rec.category_id := r_incident_record.category_id;
1156
1157 ELSE
1158 -- Some how we need to get the parameters
1159 -- we can find some from the instance id. If we do not have the instance id then we
1160 -- will have the incident id
1161 OPEN c_from_ib_instance(p_instance_id);
1162 FETCH c_from_ib_instance INTO r_from_ib_instance;
1163 CLOSE c_from_ib_instance;
1164
1165 l_caller_type := 'ORGANIZATION';
1166 l_party_id := r_from_ib_instance.OWNER_PARTY_ID;
1167 l_party_acc_id := r_from_ib_instance.OWNER_PARTY_ACCOUNT_ID;
1168 l_external_ref := r_from_ib_instance.EXTERNAL_REFERENCE;
1169 l_system_id := r_from_ib_instance.SYSTEM_ID;
1170
1171 /*
1172 Chenged for FS Asstets Flow
1173 IF (r_from_ib_instance.INSTALL_LOCATION_TYPE_CODE = 'HZ_PARTY_SITES') THEN
1174 l_install_site_id := r_from_ib_instance.INSTALL_LOCATION_ID;
1175 ELSIF (r_from_ib_instance.INSTALL_LOCATION_TYPE_CODE = 'HZ_LOCATIONS') THEN
1176 open c_install_site(l_party_id, r_from_ib_instance.INSTALL_LOCATION_ID);
1177 fetch c_install_site into l_install_site_id;
1178 close c_install_site;LOCATION_ID,
1179 LOCATION_TYPE_CODE,
1180
1181
1182 ELSE
1183 l_install_site_id := null;
1184 END IF;*/
1185
1186 IF (r_from_ib_instance.LOCATION_TYPE_CODE = 'HZ_PARTY_SITES') THEN
1187 l_install_site_id := r_from_ib_instance.LOCATION_ID;
1188 l_service_request_rec.incident_location_type := 'HZ_PARTY_SITE';
1189 l_service_request_rec.incident_location_id := r_from_ib_instance.LOCATION_ID;
1190 ELSIF (r_from_ib_instance.LOCATION_TYPE_CODE = 'HZ_LOCATIONS') THEN
1191 open c_install_site(l_party_id, r_from_ib_instance.LOCATION_ID);
1192 fetch c_install_site into l_install_site_id;
1193 close c_install_site;
1194 l_service_request_rec.incident_location_type := 'HZ_LOCATION';
1195 l_service_request_rec.incident_location_id := r_from_ib_instance.LOCATION_ID;
1196
1197 ELSE
1198 l_install_site_id := null;
1199 END IF;
1200
1201
1202 --lets validate the partyid and install locationid
1203 -- and if it fails then we will not pass the install site id
1204 IF (validate_install_site(l_install_site_id, l_party_id)<=0)then
1205 l_install_site_id := null;
1206 END IF;
1207
1208
1209 OPEN c_bill_to_ship_to(p_instance_id, 'SHIP_TO');
1210 FETCH c_bill_to_ship_to INTO r_bill_to_ship_to;
1211 CLOSE c_bill_to_ship_to;
1215 OPEN c_bill_to_ship_to(p_instance_id, 'BILL_TO');
1212 l_ship_to_site_id := r_bill_to_ship_to.party_site_id;
1213 l_ship_to_party_id := l_party_id;--r_bill_to_ship_to.PARTY_ID;
1214
1216 FETCH c_bill_to_ship_to INTO r_bill_to_ship_to;
1217 CLOSE c_bill_to_ship_to;
1218 l_bill_to_site_id := r_bill_to_ship_to.party_site_id;
1219 l_bill_to_party_id := l_party_id;--r_bill_to_ship_to.PARTY_ID;
1220
1221 --Check for 0
1222 IF (l_bill_to_site_id = 0 ) THEN
1223 l_bill_to_site_id := NULL;
1224 END IF;
1225
1226 IF (l_ship_to_site_id = 0 ) THEN
1227 l_ship_to_site_id := NULL;
1228 END IF;
1229
1230 END IF;
1231
1232 l_service_request_rec.type_id := p_incident_type_id;
1233 l_service_request_rec.status_id := p_status_id;
1234 l_service_request_rec.severity_id := p_severity_id;
1235 l_service_request_rec.summary := p_summary;
1236 l_service_request_rec.caller_type := l_caller_type;
1237 l_service_request_rec.customer_id := l_party_id;
1238
1239 -- bug # 5182686
1240 open c_hz_contact_points (l_party_id, 'PHONE');
1241 fetch c_hz_contact_points into l_customer_phone_id;
1242 close c_hz_contact_points;
1243
1244 if l_customer_phone_id is not null then
1245 l_service_request_rec.customer_phone_id := l_customer_phone_id;
1246 end if;
1247
1248 open c_hz_contact_points (l_party_id, 'EMAIL');
1249 fetch c_hz_contact_points into l_customer_email_id;
1250 close c_hz_contact_points;
1251
1252 if l_customer_email_id is not null then
1253 l_service_request_rec.customer_email_id := l_customer_email_id;
1254 end if;
1255
1256 IF (p_instance_id <> 0) THEN
1257 l_service_request_rec.customer_product_id := p_instance_id;
1258 END IF;
1259
1260 IF (p_inv_item_id <> 0) THEN
1261 l_service_request_rec.inventory_item_id := p_inv_item_id;
1262 -- bug # 5182686
1263 FND_PROFILE.GET ( 'CS_SR_DEFAULT_CATEGORY_SET' , l_category_set_id);
1264 open c_item_category (p_inv_item_id, l_category_set_id, l_organization_id);
1265 fetch c_item_category into l_category_id;
1266 close c_item_category;
1267 if l_category_id is not null then
1268 l_service_request_rec.category_id := l_category_id;
1269 l_service_request_rec.category_set_id := l_category_set_id;
1270 end if;
1271 END IF ;
1272
1273 l_service_request_rec.inventory_org_id := l_organization_id;
1274
1275 IF (p_serial_number <> '$$#@') THEN
1276 l_service_request_rec.current_serial_number := p_serial_number;
1277 END IF;
1278
1279 IF (p_external_reference <> '$$#@') THEN
1280 l_service_request_rec.external_reference := p_external_reference;
1281 ELSE
1282 l_service_request_rec.external_reference := l_external_ref;
1283 END IF;
1284
1285
1286
1287 -- bug # 5525903 Populate incident_date
1288 l_service_request_rec.request_date := sysdate;
1289 l_service_request_rec.incident_occurred_date := sysdate;
1290
1291 l_service_request_rec.exp_resolution_date := sysdate+2;
1292 l_service_request_rec.install_site_use_id := l_install_site_id;
1293 l_service_request_rec.account_id := l_party_acc_id;
1294 l_service_request_rec.sr_creation_channel := 'MOBILE'; -- Bug 3939638: Changing from `AUTOMATIC`
1295 l_service_request_rec.system_id := l_system_id;
1296
1297 l_service_request_rec.creation_program_code := 'FS-WIRELESS'; -- Bug 3939638: Changing from 'PMCON'
1298 l_service_request_rec.last_update_program_code := 'FS-WIRELESS'; -- Bug 3939638: Changing from 'PMCON'
1299 l_service_request_rec.program_id := fnd_global.conc_program_id;
1300 l_service_request_rec.program_application_id := fnd_global.prog_appl_id;
1301 l_service_request_rec.conc_request_id := fnd_global.conc_request_id;
1302 l_service_request_rec.program_login_id := fnd_global.conc_login_id;
1303
1304 --l_service_request_rec.bill_to_site_id := l_bill_to_site_id;
1305 l_service_request_rec.ship_to_site_id := l_ship_to_site_id;
1306 --l_service_request_rec.bill_to_party_id := l_bill_to_party_id;
1307 l_service_request_rec.ship_to_party_id := l_ship_to_party_id;
1308
1309 l_service_request_rec.owner_id := NULL;
1310 l_service_request_rec.time_zone_id := NULL;
1311 l_service_request_rec.verify_cp_flag := 'Y';
1312 l_service_request_rec.problem_code := p_prob_code;
1313 l_service_request_rec.cust_po_number := p_cust_po_number; -- Bug 5059169
1314
1315 -- Addition for insert DFF data with create SR
1316 l_service_request_rec.request_attribute_1 := p_attribute_1 ;
1317 l_service_request_rec.request_attribute_2 := p_attribute_2 ;
1318 l_service_request_rec.request_attribute_3 := p_attribute_3 ;
1319 l_service_request_rec.request_attribute_4 := p_attribute_4 ;
1320 l_service_request_rec.request_attribute_5 := p_attribute_5 ;
1321 l_service_request_rec.request_attribute_6 := p_attribute_6 ;
1322 l_service_request_rec.request_attribute_7 := p_attribute_7 ;
1323 l_service_request_rec.request_attribute_8 := p_attribute_8 ;
1324 l_service_request_rec.request_attribute_9 := p_attribute_9 ;
1325 l_service_request_rec.request_attribute_10 := p_attribute_10 ;
1329 l_service_request_rec.request_attribute_14 := p_attribute_14 ;
1326 l_service_request_rec.request_attribute_11 := p_attribute_11 ;
1327 l_service_request_rec.request_attribute_12 := p_attribute_12 ;
1328 l_service_request_rec.request_attribute_13 := p_attribute_13 ;
1330 l_service_request_rec.request_attribute_15 := p_attribute_15 ;
1331 l_service_request_rec.request_context := p_context ;
1332
1333
1334 --notes
1335 if p_notes <> '$$#@'
1336 then
1337 l_notes_rec.note := p_notes;
1338 l_notes_rec.note_type := 'KB_FACT';
1339 l_notes_table (1) := l_notes_rec;
1340 else
1341 l_notes_rec.note := FND_API.G_MISS_CHAR;
1342 l_notes_rec.note_type := FND_API.G_MISS_CHAR;
1343 l_notes_table (1) := l_notes_rec;
1344 end if;
1345
1346 --Contacts
1347 IF (p_contact_id <> 0) THEN
1348 open c_party_type(p_contact_id);
1349 fetch c_party_type into l_party_type;
1350 close c_party_type;
1351
1352 open c_contact_points_next;
1353 fetch c_contact_points_next into l_next_val;
1354 close c_contact_points_next;
1355
1356 l_contacts_table(1).SR_CONTACT_POINT_ID := l_next_val ;
1357 l_contacts_table(1).PRIMARY_FLAG := 'Y';
1358 l_contacts_table(1).PARTY_ID := p_contact_id;
1359 l_contacts_table(1).CONTACT_TYPE := l_party_type;
1360 END IF;
1361
1362 --CALLING API
1363 cs_servicerequest_pub.Create_ServiceRequest
1364 ( p_api_version => 3.0,
1365 p_init_msg_list => FND_API.G_FALSE,
1366 p_commit => FND_API.G_TRUE, -- COMMIT
1367 x_return_status => x_return_status,
1368 x_msg_count => x_msg_count,
1369 x_msg_data => x_msg_data,
1370 p_resp_appl_id => FND_GLOBAL.RESP_APPL_ID,
1371 p_resp_id => FND_GLOBAL.RESP_ID,
1372 p_user_id => fnd_global.user_id,
1373 p_login_id => fnd_global.conc_login_id,
1374 p_org_id => null,
1375 p_request_id => null,
1376 p_request_number => null,
1377 p_service_request_rec => l_service_request_rec,
1378 p_notes => l_notes_table,
1379 p_contacts => l_contacts_table,-- for time being it is null
1380 p_auto_assign => 'Y',
1381 x_request_id => x_new_incident_id,
1382 x_request_number => x_incident_number,
1383 x_interaction_id => x_interaction_id,
1384 x_workflow_process_id => x_workflow_process_id,
1385 x_individual_owner => x_individual_owner,
1386 x_group_owner => x_group_owner,
1387 x_individual_type => x_individual_type );
1388
1389
1390 -- open the file
1391 /*
1392 debug_handler := UTL_FILE.FOPEN(l_utl_dir, 'prats1.log','a');
1393
1394 if UTL_FILE.is_open(debug_handler) then
1395 UTL_FILE.PUT_LINE(debug_handler,'**************** STARTING AGAIN **********' );
1396 UTL_FILE.PUT_LINE(debug_handler,'Just called API' );
1397 UTL_FILE.PUT_LINE(debug_handler,'Return Status ' ||x_return_status );
1398 UTL_FILE.PUT_LINE(debug_handler,'x_error '|| x_error );
1399 UTL_FILE.PUT_LINE(debug_handler,'x_msg_data ' || x_msg_data);
1400 UTL_FILE.PUT_LINE(debug_handler,'x_msg_count ' || x_msg_count);
1401 -- UTL_FILE.PUT_LINE(debug_handler,'**************** FINISHED RUN **********' );
1402 -- UTL_FILE.FCLOSE(debug_handler);
1403 end if;
1404 */
1405 IF x_return_status = FND_API.G_RET_STS_SUCCESS
1406 THEN
1407 /* API-call was successfull */
1408 x_error_id := 0;
1409 x_error := NULL ;
1410 ELSE
1411 x_error_id := 2;
1412 FOR l_counter IN 1 .. x_msg_count
1413 LOOP
1414 fnd_msg_pub.get
1415 ( p_msg_index => l_counter
1416 , p_encoded => FND_API.G_FALSE
1417 , p_data => l_data
1418 , p_msg_index_out => l_msg_index_out
1419 );
1420 /*
1421 if UTL_FILE.is_open(debug_handler) then
1422 UTL_FILE.PUT_LINE(debug_handler,'------------- Came Here status <> S --------------' );
1423 UTL_FILE.PUT_LINE(debug_handler,' l_counter ' || l_counter);
1424 UTL_FILE.PUT_LINE(debug_handler,' l_data ' || l_data);
1425 -- UTL_FILE.PUT_LINE(debug_handler,'**************** FINISHED RUN **********' );
1426 -- UTL_FILE.FCLOSE(debug_handler);
1427 end if;
1428 */ x_msg_data := l_data;
1429 END LOOP ;
1430 x_error := x_msg_data;
1431 x_new_incident_id := 0; -- no tasks
1432 END IF;
1433 /*
1434 if UTL_FILE.is_open(debug_handler) then
1435 UTL_FILE.PUT_LINE(debug_handler,'------------- Trying to finish stuffs --------------' );
1436 UTL_FILE.PUT_LINE(debug_handler,' x_msg_data ' || x_msg_data);
1437 UTL_FILE.PUT_LINE(debug_handler,'**************** FINISHED RUN **********' );
1438 UTL_FILE.FCLOSE(debug_handler);
1439 end if;
1440 */
1441 -- debug
1442 x_error :='Error is ' || x_error|| ' - '||l_bill_to_site_id||' - '|| l_bill_to_party_id ||' - '|| l_ship_to_site_id ||' - '|| l_ship_to_party_id;
1443
1444
1445 EXCEPTION
1446 WHEN OTHERS
1447 THEN
1448 x_error_id := -1;
1449 x_error := SQLERRM;
1450
1451
1452 END CREATE_NEW_SR ;
1453
1454 FUNCTION GET_END_DATE (p_start_date date, p_uom_code varchar2, p_effort number)
1455 RETURN date IS
1456
1457 conversion_rate_in_day number;
1458
1459 cursor c_conversion (l_uom_code varchar2) is
1460 SELECT CONVERSION_RATE/24
1461 FROM MTL_UOM_CONVERSIONS
1465
1462 WHERE UOM_CLASS = 'Time'
1463 AND UOM_CODE = l_uom_code
1464 AND INVENTORY_ITEM_ID = 0;
1466
1467 BEGIN
1468
1469 open c_conversion(p_uom_code);
1470 fetch c_conversion into conversion_rate_in_day;
1471 close c_conversion;
1472
1473 return (p_start_date+(p_effort * conversion_rate_in_day) );
1474
1475
1476 END GET_END_DATE;
1477
1478
1479 FUNCTION validate_install_site
1480 (
1481 p_install_site_id IN NUMBER ,
1482 p_customer_id IN NUMBER
1483 ) RETURN NUMBER IS
1484 l_count number;
1485
1486 BEGIN
1487 l_count := 0;
1488 SELECT count(*)
1489 INTO l_count
1490 FROM Hz_Party_Sites s
1491 WHERE s.party_site_id = p_install_site_id
1492 AND s.status = 'A'
1493 -- Belongs to SR Customer
1494 AND ( s.party_id = p_customer_id
1495 -- or one of its relationships
1496 OR s.party_id IN (
1497 SELECT r.party_id
1498 FROM Hz_Relationships r
1499 WHERE r.object_id = p_customer_id
1500 AND r.status = 'A'
1501 AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(r.START_DATE, SYSDATE)) AND TRUNC(NVL(r.END_DATE, SYSDATE)) )
1502 -- or one of its Related parties
1503 OR s.party_id IN (
1504 SELECT sub.party_id
1505 FROM Hz_Parties p,
1506 Hz_Parties sub,
1507 Hz_Parties obj,
1508 Hz_Relationships r
1509 WHERE obj.party_id = p_customer_id
1510 AND sub.status = 'A'
1511 AND sub.party_type IN ('PERSON','ORGANIZATION')
1512 AND p.party_id = r.party_id
1513 AND r.object_id = obj.party_id
1514 AND r.subject_id = sub.party_id ));
1515
1516 return l_count;
1517
1518 EXCEPTION
1519 when no_data_found then
1520 return 0;
1521 when others then
1522 return 0;
1523
1524 END validate_install_site;
1525
1526
1527 -- Wrapper on update_task for updating task fled field
1528
1529 PROCEDURE UPDATE_TASK_FLEX
1530 (
1531 p_task_id IN NUMBER
1532 , p_attribute_1 IN VARCHAR2
1533 , p_attribute_2 IN VARCHAR2
1534 , p_attribute_3 IN VARCHAR2
1535 , p_attribute_4 IN VARCHAR2
1536 , p_attribute_5 IN VARCHAR2
1537 , p_attribute_6 IN VARCHAR2
1538 , p_attribute_7 IN VARCHAR2
1539 , p_attribute_8 IN VARCHAR2
1540 , p_attribute_9 IN VARCHAR2
1541 , p_attribute_10 IN VARCHAR2
1542 , p_attribute_11 IN VARCHAR2
1543 , p_attribute_12 IN VARCHAR2
1544 , p_attribute_13 IN VARCHAR2
1545 , p_attribute_14 IN VARCHAR2
1546 , p_attribute_15 IN VARCHAR2
1547 , p_context IN VARCHAR2
1548 , x_return_status OUT NOCOPY VARCHAR2
1549 , x_msg_count OUT NOCOPY NUMBER
1550 , x_error OUT NOCOPY VARCHAR2
1551 ) IS
1552
1553 l_msg_count number;
1554 l_msg_data varchar2(2000);
1555
1556 l_data varchar2(255);
1557 l_msg_index_out number;
1558 l_object_version_number number;
1559
1560
1561 CURSOR c_prez_data(v_task_id number)
1562 IS
1563 select object_version_number from jtf_tasks_b where task_id = v_task_id;
1564
1565
1566 BEGIN
1567
1568 open c_prez_data(p_task_id);
1569 fetch c_prez_data into l_object_version_number;
1570 close c_prez_data ;
1571
1572 -- Lets call the API
1573 -- bug # 4570867
1574 -- call csf_task_pub instead of jtf_task_pub
1575
1576 csf_tasks_pub.update_task (
1577 p_api_version => 1.0,
1578 p_init_msg_list => FND_API.G_FALSE,
1579 p_commit => FND_API.G_TRUE,
1580 p_object_version_number => l_object_version_number,
1581 p_task_id => p_task_id,
1582 x_return_status => x_return_status,
1583 x_msg_count => x_msg_count,
1584 x_msg_data => l_msg_data,
1585 p_attribute1 => p_attribute_1,
1586 p_attribute2 => p_attribute_2,
1587 p_attribute3 => p_attribute_3,
1588 p_attribute4 => p_attribute_4,
1589 p_attribute5 => p_attribute_5,
1590 p_attribute6 => p_attribute_6,
1591 p_attribute7 => p_attribute_7,
1592 p_attribute8 => p_attribute_8,
1593 p_attribute9 => p_attribute_9,
1594 p_attribute10 => p_attribute_10,
1595 p_attribute11 => p_attribute_11,
1596 p_attribute12 => p_attribute_12,
1597 p_attribute13 => p_attribute_13,
1598 p_attribute14 => p_attribute_14,
1599 p_attribute15 => p_attribute_15,
1600 p_attribute_category => p_context
1601 );
1602
1603 IF x_return_status = FND_API.G_RET_STS_SUCCESS
1604 THEN
1605 -- API-call was successfull
1606 commit;
1607 ELSE
1608 FOR l_counter IN 1 .. x_msg_count
1609 LOOP
1610 fnd_msg_pub.get
1611 ( p_msg_index => l_counter
1612 , p_encoded => FND_API.G_FALSE
1613 , p_data => l_data
1614 , p_msg_index_out => l_msg_index_out
1615 );
1616 --dbms_output.put_line( 'Message: '||l_data );
1617 END LOOP ;
1621
1618 x_error := l_data;
1619 END IF;
1620
1622 EXCEPTION
1623 WHEN OTHERS
1624 THEN
1625 x_error := SQLERRM;
1626
1627
1628 END UPDATE_TASK_FLEX;
1629
1630 /*
1631 Bug # 4922104
1632 Procedure added to update schedule start/end date and Planned Efforts
1633 */
1634 PROCEDURE UPDATE_SCH_DATE_TASK
1635 ( p_task_id IN NUMBER
1636 , p_scheduled_start_date IN DATE
1637 , p_scheduled_end_date IN DATE
1638 , p_planned_effort IN NUMBER
1639 , p_planned_effort_uom IN VARCHAR
1640 , p_allow_overlap IN VARCHAR
1641 , x_return_status OUT NOCOPY VARCHAR2
1642 , x_msg_count OUT NOCOPY NUMBER
1643 , x_error OUT NOCOPY VARCHAR2
1644 ) IS
1645
1646 l_object_version_number number;
1647 l_find_overlap varchar2(10);
1648 l_validation_level number;
1649 l_msg_count number;
1650
1651 CURSOR c_prez_data(v_task_id number) IS
1652 select object_version_number from jtf_tasks_b where task_id = v_task_id;
1653
1654 BEGIN
1655
1656 -- Fetch Object Version
1657 open c_prez_data(p_task_id);
1658 fetch c_prez_data into l_object_version_number;
1659 close c_prez_data ;
1660
1661 l_find_overlap := fnd_api.G_TRUE;
1662 l_validation_level := fnd_api.G_VALID_LEVEL_FULL;
1663
1664 if p_allow_overlap = 'Y' then
1665 l_find_overlap := fnd_api.G_FALSE;
1666 l_validation_level := fnd_api.G_VALID_LEVEL_NONE;
1667 end if;
1668
1669 savepoint before_sch_date;
1670 -- Call API
1671 CSF_TASKS_PUB.Update_Task
1672 ( p_api_version => 1.0
1673 , p_init_msg_list => fnd_api.g_false
1674 , p_commit => fnd_api.g_false
1675 , p_validation_level => l_validation_level
1676 , p_find_overlap => l_find_overlap
1677 , p_task_id => p_task_id
1678 , p_object_version_number => l_object_version_number
1679 , p_planned_start_date => fnd_api.g_miss_date
1680 , p_planned_end_date => fnd_api.g_miss_date
1681 , p_scheduled_start_date => p_scheduled_start_date
1682 , p_scheduled_end_date => p_scheduled_end_date
1683 , p_actual_start_date => fnd_api.g_miss_date
1684 , p_actual_end_date => fnd_api.g_miss_date
1685 , p_timezone_id => fnd_api.g_miss_num
1686 , p_source_object_type_code => fnd_api.g_miss_char
1687 , p_source_object_id => fnd_api.g_miss_num
1688 , p_source_object_name => fnd_api.g_miss_char
1689 , p_task_status_id => fnd_api.g_miss_num
1690 , p_task_type_id => fnd_api.g_miss_num
1691 , p_task_priority_id => fnd_api.g_miss_num
1692 , p_owner_type_code => fnd_api.g_miss_char
1693 , p_owner_id => fnd_api.g_miss_num
1694 , p_owner_territory_id => fnd_api.g_miss_num
1695 , p_assigned_by_id => fnd_api.g_miss_num
1696 , p_customer_id => fnd_api.g_miss_num
1697 , p_cust_account_id => fnd_api.g_miss_num
1698 , p_address_id => fnd_api.g_miss_num
1699 , p_task_name => fnd_api.g_miss_char
1700 , p_description => fnd_api.g_miss_char
1701 , p_duration => fnd_api.g_miss_num
1702 , p_duration_uom => fnd_api.g_miss_char
1703 , p_planned_effort => p_planned_effort
1704 , p_planned_effort_uom => p_planned_effort_uom
1705 , p_actual_effort => fnd_api.g_miss_num
1706 , p_actual_effort_uom => fnd_api.g_miss_char
1707 , p_percentage_complete => fnd_api.g_miss_num
1708 , p_reason_code => fnd_api.g_miss_char
1709 , p_private_flag => fnd_api.g_miss_char
1710 , p_publish_flag => fnd_api.g_miss_char
1711 , p_restrict_closure_flag => fnd_api.g_miss_char
1712 , p_attribute1 => fnd_api.g_miss_char
1713 , p_attribute2 => fnd_api.g_miss_char
1714 , p_attribute3 => fnd_api.g_miss_char
1715 , p_attribute4 => fnd_api.g_miss_char
1716 , p_attribute5 => fnd_api.g_miss_char
1717 , p_attribute6 => fnd_api.g_miss_char
1718 , p_attribute7 => fnd_api.g_miss_char
1719 , p_attribute8 => fnd_api.g_miss_char
1720 , p_attribute9 => fnd_api.g_miss_char
1721 , p_attribute10 => fnd_api.g_miss_char
1722 , p_attribute11 => fnd_api.g_miss_char
1723 , p_attribute12 => fnd_api.g_miss_char
1724 , p_attribute13 => fnd_api.g_miss_char
1725 , p_attribute14 => fnd_api.g_miss_char
1726 , p_attribute15 => fnd_api.g_miss_char
1727 , p_attribute_category => fnd_api.g_miss_char
1728 , p_date_selected => fnd_api.g_miss_char
1729 , p_category_id => fnd_api.g_miss_num
1730 , p_multi_booked_flag => fnd_api.g_miss_char
1731 , p_milestone_flag => fnd_api.g_miss_char
1732 , p_holiday_flag => fnd_api.g_miss_char
1733 , p_billable_flag => fnd_api.g_miss_char
1734 , p_bound_mode_code => fnd_api.g_miss_char
1738 , p_notification_period => fnd_api.g_miss_num
1735 , p_soft_bound_flag => fnd_api.g_miss_char
1736 , p_workflow_process_id => fnd_api.g_miss_num
1737 , p_notification_flag => fnd_api.g_miss_char
1739 , p_notification_period_uom => fnd_api.g_miss_char
1740 , p_parent_task_id => fnd_api.g_miss_num
1741 , p_alarm_start => fnd_api.g_miss_num
1742 , p_alarm_start_uom => fnd_api.g_miss_char
1743 , p_alarm_on => fnd_api.g_miss_char
1744 , p_alarm_count => fnd_api.g_miss_num
1745 , p_alarm_fired_count => fnd_api.g_miss_num
1746 , p_alarm_interval => fnd_api.g_miss_num
1747 , p_alarm_interval_uom => fnd_api.g_miss_char
1748 , p_palm_flag => fnd_api.g_miss_char
1749 , p_wince_flag => fnd_api.g_miss_char
1750 , p_laptop_flag => fnd_api.g_miss_char
1751 , p_device1_flag => fnd_api.g_miss_char
1752 , p_device2_flag => fnd_api.g_miss_char
1753 , p_device3_flag => fnd_api.g_miss_char
1754 , p_costs => fnd_api.g_miss_num
1755 , p_currency_code => fnd_api.g_miss_char
1756 , p_escalation_level => fnd_api.g_miss_char
1757 , x_return_status => x_return_status
1758 , x_msg_count => x_msg_count
1759 , x_msg_data => x_error
1760 );
1761
1762 if x_return_status = 'S' and x_msg_count > 0 then
1763 rollback to before_sch_date;
1764 else
1765 commit;
1766 end if;
1767
1768 -- bug 5351199
1769 fnd_msg_pub.get
1770 ( p_msg_index => x_msg_count
1771 , p_encoded => FND_API.G_FALSE
1772 , p_data => x_error
1773 , p_msg_index_out => l_msg_count
1774 );
1775
1776 EXCEPTION
1777 WHEN OTHERS
1778 THEN
1779 x_error := SQLERRM;
1780 END UPDATE_SCH_DATE_TASK;
1781
1782 END csfw_tasks_pub;
1783