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