[Home] [Help]
PACKAGE BODY: APPS.JTF_IH_PUB_PS
Source
1 PACKAGE BODY JTF_IH_PUB_PS AS
2 /* $Header: JTFIHPSB.pls 115.10 2000/02/29 17:58:35 pkm ship $ */
3 G_PKG_NAME CONSTANT VARCHAR2(30) := 'JTF_IH_PUB';
4
5 -- Jean Zhu add Utility Validate_StartEnd_Date
6 PROCEDURE Validate_StartEnd_Date
7 ( p_api_name IN VARCHAR2,
8 p_start_date_time IN DATE,
9 p_end_date_time IN DATE,
10 x_return_status IN OUT VARCHAR2
11 );
12
13 -- End Utilities Declaration
14 -- Begin Utilities Definition
15 PROCEDURE Validate_StartEnd_Date
16 ( p_api_name IN VARCHAR2,
17 p_start_date_time IN DATE,
18 p_end_date_time IN DATE,
19 x_return_status IN OUT VARCHAR2
20 )
21 IS
22 BEGIN
23 IF((p_start_date_time IS NOT NULL) AND (p_end_date_time IS NOT NULL) AND
24 (p_end_date_time - p_start_date_time < 0) )THEN
25 ----DBMS_OUTPUT.PUT_LINE('end_date is less than start_date in JTF_IH_PUB_PS.Validate_StartEnd_Date');
26 x_return_status := fnd_api.g_ret_sts_error;
27 jtf_ih_core_util_pvt.add_invalid_argument_msg(p_api_name, to_char(p_end_date_time),
28 'end_date_time');
29 END IF;
30 END Validate_StartEnd_Date;
31
32
33 PROCEDURE Validate_Interaction_Record
34 ( p_api_name IN VARCHAR2,
35 p_int_val_rec IN interaction_rec_type,
36 p_resp_appl_id IN NUMBER := NULL,
37 p_resp_id IN NUMBER := NULL,
38 x_return_status IN OUT VARCHAR2
39 );
40
41 -- End Utilities Declaration
42 -- Begin Utilities Definition
43
44 PROCEDURE Validate_Interaction_Record
45 ( p_api_name IN VARCHAR2,
46 p_int_val_rec IN interaction_rec_type,
47 p_resp_appl_id IN NUMBER := NULL,
48 p_resp_id IN NUMBER := NULL,
49 x_return_status IN OUT VARCHAR2
50 )
51 IS
52 l_count NUMBER := 0;
53 BEGIN
54 -- Initialize API return status to success
55 x_return_status := fnd_api.g_ret_sts_success;
56
57 IF ((p_int_val_rec.handler_id IS NOT NULL) AND (p_int_val_rec.handler_id <> fnd_api.g_miss_num)) THEN
58 BEGIN
59 SELECT count(*) into l_count
60 FROM fnd_application
61 WHERE application_id = p_int_val_rec.handler_id;
62 IF (l_count <= 0) THEN
63 x_return_status := fnd_api.g_ret_sts_error;
64 jtf_ih_core_util_pvt.add_invalid_argument_msg(p_api_name, to_char(p_int_val_rec.handler_id),
65 'Handler_id');
66 RETURN;
67 END IF;
68 END;
69 ELSE
70 x_return_status := fnd_api.g_ret_sts_error;
71 jtf_ih_core_util_pvt.add_invalid_argument_msg(p_api_name, to_char(p_int_val_rec.handler_id),
72 'handler_id');
73 RETURN;
74 END IF;
75 ----DBMS_OUTPUT.PUT_LINE('PAST Validate handler_id in JTF_IH_PUB_PS.Validate_Interaction_Record');
76
77 l_count := 0;
78 IF ((p_int_val_rec.party_id IS NOT NULL) AND (p_int_val_rec.party_id <> fnd_api.g_miss_num)) THEN
79 BEGIN
80 SELECT count(*) into l_count
81 FROM hz_parties
82 WHERE party_id = p_int_val_rec.party_id;
83 IF (l_count <= 0) THEN
84 x_return_status := fnd_api.g_ret_sts_error;
85 jtf_ih_core_util_pvt.add_invalid_argument_msg(p_api_name, to_char(p_int_val_rec.party_id),
86 'party_id');
87 RETURN;
88 END IF;
89 END;
90 ELSE
91 x_return_status := fnd_api.g_ret_sts_error;
92 jtf_ih_core_util_pvt.add_invalid_argument_msg(p_api_name, to_char(p_int_val_rec.party_id),
93 'party_id');
94 RETURN;
95 END IF;
96 ----DBMS_OUTPUT.PUT_LINE('PAST Validate party_id in JTF_IH_PUB_PS.Validate_Interaction_Record');
97
98 l_count := 0;
99 IF ((p_int_val_rec.resource_id IS NOT NULL) AND (p_int_val_rec.resource_id <> fnd_api.g_miss_num)) THEN
100 BEGIN
101 SELECT count(*) into l_count
102 FROM jtf_rs_resource_extns
103 WHERE resource_id = p_int_val_rec.resource_id;
104 IF (l_count <= 0) THEN
105 x_return_status := fnd_api.g_ret_sts_error;
106 jtf_ih_core_util_pvt.add_invalid_argument_msg(p_api_name, to_char(p_int_val_rec.resource_id),
107 'resource_id');
108 RETURN;
109 END IF;
110 END;
111 ELSE
112 x_return_status := fnd_api.g_ret_sts_error;
113 jtf_ih_core_util_pvt.add_invalid_argument_msg(p_api_name, to_char(p_int_val_rec.resource_id),
114 'resource_id');
115 RETURN;
116 END IF;
117 ----DBMS_OUTPUT.PUT_LINE('PAST Validate resource_id in JTF_IH_PUB_PS.Validate_Interaction_Record');
118
119 l_count := 0;
120 IF ((p_int_val_rec.outcome_id IS NOT NULL) AND (p_int_val_rec.outcome_id <> fnd_api.g_miss_num)) THEN
121 BEGIN
122 SELECT count(*) into l_count
123 FROM jtf_ih_outcomes_B
124 WHERE outcome_id = p_int_val_rec.outcome_id;
125 IF (l_count <= 0) THEN
126 x_return_status := fnd_api.g_ret_sts_error;
127 jtf_ih_core_util_pvt.add_invalid_argument_msg(p_api_name, to_char(p_int_val_rec.outcome_id),
128 'outcome_id');
129 RETURN;
130 END IF;
131
132 END;
133 END IF;
134 ----DBMS_OUTPUT.PUT_LINE('PAST Validate outcome_id in JTF_IH_PUB_PS.Validate_Interaction_Record');
135
136 l_count := 0;
137 IF ((p_int_val_rec.result_id IS NOT NULL) AND (p_int_val_rec.result_id <> fnd_api.g_miss_num)) THEN
138 BEGIN
139 SELECT count(*) into l_count
140 FROM jtf_ih_results_B
141 WHERE result_id = p_int_val_rec.result_id;
142 IF (l_count <= 0) THEN
143 x_return_status := fnd_api.g_ret_sts_error;
144 jtf_ih_core_util_pvt.add_invalid_argument_msg(p_api_name, to_char(p_int_val_rec.result_id),
145 'result_id');
146 RETURN;
147 END IF;
148
149 END;
150 END IF;
151 ----DBMS_OUTPUT.PUT_LINE('PAST Validate result_id in JTF_IH_PUB_PS.Validate_Interaction_Record');
152
153 l_count := 0;
154 IF ((p_int_val_rec.reason_id IS NOT NULL) AND (p_int_val_rec.reason_id <> fnd_api.g_miss_num)) THEN
155 BEGIN
156 SELECT count(*) into l_count
157 FROM jtf_ih_reasons_B
158 WHERE reason_id = p_int_val_rec.reason_id;
159 IF (l_count <= 0) THEN
160 x_return_status := fnd_api.g_ret_sts_error;
161 jtf_ih_core_util_pvt.add_invalid_argument_msg(p_api_name, to_char(p_int_val_rec.reason_id),
162 'reason_id');
163 RETURN;
164 END IF;
165 END;
166 END IF;
167 ----DBMS_OUTPUT.PUT_LINE('PAST Validate reason_id in JTF_IH_PUB_PS.Validate_Interaction_Record');
168
169 l_count := 0;
170 IF ((p_int_val_rec.script_id IS NOT NULL) AND (p_int_val_rec.script_id <> fnd_api.g_miss_num)) THEN
171 BEGIN
172 SELECT count(*) into l_count
173 FROM jtf_ih_scripts
174 WHERE script_id = p_int_val_rec.script_id;
175 IF (l_count <= 0) THEN
176 x_return_status := fnd_api.g_ret_sts_error;
177 jtf_ih_core_util_pvt.add_invalid_argument_msg(p_api_name, to_char(p_int_val_rec.script_id),
178 'Script_id');
179 RETURN;
180 END IF;
181 END;
182 END IF;
183 ----DBMS_OUTPUT.PUT_LINE('PAST Validate script_id in JTF_IH_PUB_PS.Validate_Interaction_Record');
184
185 -- Add by Jean Zhu to validate the source_code_id
186 l_count := 0;
187 IF ((p_int_val_rec.source_code_id IS NOT NULL) AND (p_int_val_rec.source_code_id <> fnd_api.g_miss_num)) THEN
188 BEGIN
189 SELECT count(*) into l_count
190 FROM ams_source_codes
191 WHERE source_code_id = p_int_val_rec.source_code_id;
192 IF (l_count <= 0) THEN
193 x_return_status := fnd_api.g_ret_sts_error;
194 jtf_ih_core_util_pvt.add_invalid_argument_msg(p_api_name, to_char(p_int_val_rec.source_code_id),
195 'source_code_id');
196 RETURN;
197 END IF;
198 END;
199 END IF;
200 ----DBMS_OUTPUT.PUT_LINE('PAST Validate source_code_id in JTF_IH_PUB_PS.Validate_Interaction_Record');
201
202 l_count := 0;
203 IF ((p_int_val_rec.parent_id IS NOT NULL) AND (p_int_val_rec.parent_id <> fnd_api.g_miss_num)) THEN
204 BEGIN
205 SELECT count(*) into l_count
206 FROM jtf_ih_interactions
207 WHERE interaction_id = p_int_val_rec.parent_id;
208 IF (l_count <= 0) THEN
209 x_return_status := fnd_api.g_ret_sts_error;
210 jtf_ih_core_util_pvt.add_invalid_argument_msg(p_api_name, to_char(p_int_val_rec.parent_id),
211 'interaction_id');
212 RETURN;
213 END IF;
214 END;
215 END IF;
216 ----DBMS_OUTPUT.PUT_LINE('PAST Validate parent_id in JTF_IH_PUB_PS.Validate_Interaction_Record');
217
218 -- Validate descriptive flexfield values
219 ----------------------------------------
220 IF ((p_int_val_rec.attribute1 <> fnd_api.g_miss_char) OR
221 (p_int_val_rec.attribute2 <> fnd_api.g_miss_char) OR
222 (p_int_val_rec.attribute3 <> fnd_api.g_miss_char) OR
223 (p_int_val_rec.attribute4 <> fnd_api.g_miss_char) OR
224 (p_int_val_rec.attribute5 <> fnd_api.g_miss_char) OR
225 (p_int_val_rec.attribute6 <> fnd_api.g_miss_char) OR
226 (p_int_val_rec.attribute7 <> fnd_api.g_miss_char) OR
227 (p_int_val_rec.attribute8 <> fnd_api.g_miss_char) OR
228 (p_int_val_rec.attribute9 <> fnd_api.g_miss_char) OR
229 (p_int_val_rec.attribute10 <> fnd_api.g_miss_char) OR
230 (p_int_val_rec.attribute11 <> fnd_api.g_miss_char) OR
231 (p_int_val_rec.attribute12 <> fnd_api.g_miss_char) OR
232 (p_int_val_rec.attribute13 <> fnd_api.g_miss_char) OR
233 (p_int_val_rec.attribute14 <> fnd_api.g_miss_char) OR
234 (p_int_val_rec.attribute15 <> fnd_api.g_miss_char) OR
235 (p_int_val_rec.attribute_category <> fnd_api.g_miss_char)) THEN
236 jtf_ih_core_util_pvt.validate_desc_flex
237 ( p_api_name => p_api_name,
238 p_desc_flex_name => 'JTF_IH',
239 p_column_name1 => 'ATTRIBUTE1',
240 p_column_name2 => 'ATTRIBUTE2',
241 p_column_name3 => 'ATTRIBUTE3',
242 p_column_name4 => 'ATTRIBUTE4',
243 p_column_name5 => 'ATTRIBUTE5',
244 p_column_name6 => 'ATTRIBUTE6',
245 p_column_name7 => 'ATTRIBUTE7',
246 p_column_name8 => 'ATTRIBUTE8',
247 p_column_name9 => 'ATTRIBUTE9',
248 p_column_name10 => 'ATTRIBUTE10',
249 p_column_name11 => 'ATTRIBUTE11',
250 p_column_name12 => 'ATTRIBUTE12',
251 p_column_name13 => 'ATTRIBUTE13',
252 p_column_name14 => 'ATTRIBUTE14',
253 p_column_name15 => 'ATTRIBUTE15',
254 p_column_value1 => p_int_val_rec.attribute1,
255 p_column_value2 => p_int_val_rec.attribute2,
256 p_column_value3 => p_int_val_rec.attribute3,
257 p_column_value4 => p_int_val_rec.attribute4,
258 p_column_value5 => p_int_val_rec.attribute5,
259 p_column_value6 => p_int_val_rec.attribute6,
260 p_column_value7 => p_int_val_rec.attribute7,
261 p_column_value8 => p_int_val_rec.attribute8,
262 p_column_value9 => p_int_val_rec.attribute9,
263 p_column_value10 => p_int_val_rec.attribute10,
264 p_column_value11 => p_int_val_rec.attribute11,
265 p_column_value12 => p_int_val_rec.attribute12,
266 p_column_value13 => p_int_val_rec.attribute13,
267 p_column_value14 => p_int_val_rec.attribute14,
268 p_column_value15 => p_int_val_rec.attribute15,
269 p_context_value => p_int_val_rec.attribute_category,
270 p_resp_appl_id => p_resp_appl_id,
271 p_resp_id => p_resp_id,
272 x_return_status => x_return_status);
273 IF (x_return_status <> fnd_api.g_ret_sts_success) THEN
274 RETURN;
275 END IF;
276 END IF;
277 ----DBMS_OUTPUT.PUT_LINE('PAST Validate flexfields in JTF_IH_PUB_PS.Validate_Interaction_Record');
278 END Validate_Interaction_Record;
279
280
281 PROCEDURE Default_Interaction_Record (x_interaction IN OUT interaction_rec_type);
282
283 -- End Utilities Declaration
284 -- Begin Utilities Definition
285 PROCEDURE Default_Interaction_Record (x_interaction IN OUT interaction_rec_type)
286 IS
287 BEGIN
288 if (x_interaction.handler_id = fnd_api.g_miss_num)then
289 x_interaction.handler_id :=0;
290 end if;
291 if (x_interaction.script_id = fnd_api.g_miss_num)then
292 x_interaction.script_id :=0;
293 end if;
294
295 if (x_interaction.result_id = fnd_api.g_miss_num)then
296 x_interaction.result_id :=0;
297 end if;
298
299 if (x_interaction.reason_id = fnd_api.g_miss_num)then
300 x_interaction.reason_id :=0;
301 end if;
302
303 if (x_interaction.resource_id = fnd_api.g_miss_num)then
304 x_interaction.resource_id :=0;
305 end if;
306
307 if (x_interaction.party_id = fnd_api.g_miss_num)then
308 x_interaction.party_id :=0;
309 end if;
310
311 if (x_interaction.object_id = fnd_api.g_miss_num)then
312 x_interaction.object_id :=0;
313 end if;
314 if (x_interaction.source_code_id = fnd_api.g_miss_num)then
315 x_interaction.source_code_id :=0;
316 end if;
317 END;
318
319 PROCEDURE Default_activity_table (x_activities IN OUT activity_tbl_type);
320
321 -- End Utilities Declaration
322 -- Begin Utilities Definition
323 PROCEDURE Default_activity_table (x_activities IN OUT activity_tbl_type)
324 IS
325 BEGIN
326
327 for idx in 1 .. x_activities.count loop
328 if (x_activities(idx).task_id = fnd_api.g_miss_num)then
329 x_activities(idx).task_id :=0;
330 end if;
331 if (x_activities(idx).doc_id = fnd_api.g_miss_num)then
332 x_activities(idx).doc_id :=0;
333 end if;
334
335 if (x_activities(idx).action_item_id = fnd_api.g_miss_num)then
336 x_activities(idx).action_item_id :=0;
337 end if;
338
339 if (x_activities(idx).outcome_id = fnd_api.g_miss_num)then
340 x_activities(idx).outcome_id :=0;
341 end if;
342
343 if (x_activities(idx).result_id = fnd_api.g_miss_num)then
344 x_activities(idx).result_id :=0;
345 end if;
346 if (x_activities(idx).reason_id = fnd_api.g_miss_num)then
347 x_activities(idx).reason_id :=0;
348 end if;
349 if (x_activities(idx).object_id = fnd_api.g_miss_num)then
350 x_activities(idx).object_id :=0;
351 end if;
352 if (x_activities(idx).source_code_id = fnd_api.g_miss_num)then
353 x_activities(idx).source_code_id:=0;
354 end if;
355
356 end loop;
357 END;
358
359
360 -- Jean Zhu add Utility Validate_Activity_Record
361
362 PROCEDURE Validate_Activity_Record
363 ( p_api_name IN VARCHAR2,
364 p_act_val_rec IN activity_rec_type,
365 p_resp_appl_id IN NUMBER := NULL,
366 p_resp_id IN NUMBER := NULL,
367 x_return_status IN OUT VARCHAR2
368 );
369
370 -- End Utilities Declaration
371 -- Begin Utilities Definition
372
373 PROCEDURE Validate_Activity_Record
374 ( p_api_name IN VARCHAR2,
375 p_act_val_rec IN activity_rec_type,
376 p_resp_appl_id IN NUMBER := NULL,
377 p_resp_id IN NUMBER := NULL,
378 x_return_status IN OUT VARCHAR2
379 )
380 IS
381 l_count NUMBER := 0;
382 BEGIN
383 -- Initialize API return status to success
384 x_return_status := fnd_api.g_ret_sts_success;
385
386 IF ((p_act_val_rec.interaction_id IS NOT NULL) AND (p_act_val_rec.interaction_id <> fnd_api.g_miss_num)) THEN
387 BEGIN
388 SELECT count(*) into l_count
389 FROM jtf_ih_interactions
390 WHERE interaction_id = p_act_val_rec.interaction_id;
391 IF (l_count <= 0) THEN
392 x_return_status := fnd_api.g_ret_sts_error;
393 jtf_ih_core_util_pvt.add_invalid_argument_msg(p_api_name, to_char(p_act_val_rec.interaction_id),
394 'interaction_id');
395 RETURN;
396 END IF;
397 END;
398 ELSE
399 x_return_status := fnd_api.g_ret_sts_error;
400 jtf_ih_core_util_pvt.add_invalid_argument_msg(p_api_name, to_char(p_act_val_rec.interaction_id),
401 'interaction_id');
402 RETURN;
403 END IF;
404 ----DBMS_OUTPUT.PUT_LINE('PAST Validate interaction_id in JTF_IH_PUB_PS.Validate_Activity_Record');
405
406 l_count := 0;
407 IF ((p_act_val_rec.action_item_id IS NOT NULL) AND (p_act_val_rec.action_item_id <> fnd_api.g_miss_num)) THEN
408 BEGIN
409 SELECT count(*) into l_count
410 FROM jtf_ih_action_items_b
411 WHERE action_item_id = p_act_val_rec.action_item_id;
412 IF (l_count <= 0) THEN
413 x_return_status := fnd_api.g_ret_sts_error;
414 jtf_ih_core_util_pvt.add_invalid_argument_msg(p_api_name, to_char(p_act_val_rec.action_item_id),
415 'action_item_id');
416 RETURN;
417 END IF;
418 END;
419 END IF;
420 ----DBMS_OUTPUT.PUT_LINE('PAST Validate action_item_id in JTF_IH_PUB_PS.Validate_Activity_Record');
421
422 l_count := 0;
423 IF ((p_act_val_rec.outcome_id IS NOT NULL) AND (p_act_val_rec.outcome_id <> fnd_api.g_miss_num)) THEN
424 BEGIN
425 SELECT count(*) into l_count
426 FROM jtf_ih_outcomes_B
427 WHERE outcome_id = p_act_val_rec.outcome_id;
428 IF (l_count <= 0) THEN
429 x_return_status := fnd_api.g_ret_sts_error;
430 jtf_ih_core_util_pvt.add_invalid_argument_msg(p_api_name, to_char(p_act_val_rec.outcome_id),
431 'outcome_id');
432 RETURN;
433 END IF;
434
435 END;
436 END IF;
437 ----DBMS_OUTPUT.PUT_LINE('PAST Validate outcome_id in JTF_IH_PUB_PS.Validate_Activity_Record');
438
439
440 l_count := 0;
441 IF ((p_act_val_rec.action_id IS NOT NULL) AND (p_act_val_rec.action_id <> fnd_api.g_miss_num)) THEN
442 BEGIN
443 SELECT count(*) into l_count
444 FROM jtf_ih_actions_b
445 WHERE action_id = p_act_val_rec.action_id;
446 IF (l_count <= 0) THEN
447 x_return_status := fnd_api.g_ret_sts_error;
448 jtf_ih_core_util_pvt.add_invalid_argument_msg(p_api_name, to_char(p_act_val_rec.action_id),
449 'action_id');
450 RETURN;
451 END IF;
452 END;
453 END IF;
454 ----DBMS_OUTPUT.PUT_LINE('PAST Validate action_id in JTF_IH_PUB_PS.Validate_Activity_Record');
455 l_count := 0;
456 IF ((p_act_val_rec.result_id IS NOT NULL) AND (p_act_val_rec.result_id <> fnd_api.g_miss_num)) THEN
457 BEGIN
458 SELECT count(*) into l_count
459 FROM jtf_ih_results_B
460 WHERE result_id = p_act_val_rec.result_id;
461 IF (l_count <= 0) THEN
462 x_return_status := fnd_api.g_ret_sts_error;
463 jtf_ih_core_util_pvt.add_invalid_argument_msg(p_api_name, to_char(p_act_val_rec.result_id),
464 'result_id');
465 RETURN;
466 END IF;
467
468 END;
469 END IF;
470 ----DBMS_OUTPUT.PUT_LINE('PAST Validate result_id in JTF_IH_PUB_PS.Validate_Activity_Record');
471
472 l_count := 0;
473 IF ((p_act_val_rec.reason_id IS NOT NULL) AND (p_act_val_rec.reason_id <> fnd_api.g_miss_num)) THEN
474 BEGIN
475 SELECT count(*) into l_count
476 FROM jtf_ih_reasons_B
477 WHERE reason_id = p_act_val_rec.reason_id;
478 IF (l_count <= 0) THEN
479 x_return_status := fnd_api.g_ret_sts_error;
480 jtf_ih_core_util_pvt.add_invalid_argument_msg(p_api_name, to_char(p_act_val_rec.reason_id),
481 'reason_id');
482 RETURN;
483 END IF;
484 END;
485 END IF;
486 ----DBMS_OUTPUT.PUT_LINE('PAST Validate reason_id in JTF_IH_PUB_PS.Validate_Activity_Record');
487
488
489 l_count := 0;
490 IF ((p_act_val_rec.source_code_id IS NOT NULL) AND (p_act_val_rec.source_code_id <> fnd_api.g_miss_num)) THEN
491 BEGIN
492 SELECT count(*) into l_count
493 FROM ams_source_codes
494 WHERE source_code_id = p_act_val_rec.source_code_id;
495 IF (l_count <= 0) THEN
496 x_return_status := fnd_api.g_ret_sts_error;
497 jtf_ih_core_util_pvt.add_invalid_argument_msg(p_api_name, to_char(p_act_val_rec.source_code_id),
498 'source_code_id');
499 RETURN;
500 END IF;
501 END;
502 END IF;
503 ----DBMS_OUTPUT.PUT_LINE('PAST Validate source_code_id in JTF_IH_PUB_PS.Validate_Activity_Record');
504
505 l_count := 0;
506 IF ((p_act_val_rec.media_id IS NOT NULL) AND (p_act_val_rec.media_id <> fnd_api.g_miss_num)) THEN
507 BEGIN
508 SELECT count(*) into l_count
509 FROM jtf_ih_media_items
510 WHERE media_id = p_act_val_rec.media_id;
511 IF (l_count <= 0) THEN
512 x_return_status := fnd_api.g_ret_sts_error;
513 jtf_ih_core_util_pvt.add_invalid_argument_msg(p_api_name, to_char(p_act_val_rec.media_id),
514 'media_id');
515 RETURN;
516 END IF;
517 END;
518 END IF;
519 ----DBMS_OUTPUT.PUT_LINE('PAST Validate media_id in JTF_IH_PUB_PS.Validate_Activity_Record');
520 END Validate_Activity_Record;
521
522
523 PROCEDURE Validate_Activity_table
524 ( p_api_name IN VARCHAR2,
525 p_int_val_tbl IN activity_tbl_type,
526 p_resp_appl_id IN NUMBER := NULL,
527 p_resp_id IN NUMBER := NULL,
528 x_return_status IN OUT VARCHAR2
529 );
530
531 -- End Utilities Declaration
532 -- Begin Utilities Definition
533 PROCEDURE Validate_Activity_table
534 ( p_api_name IN VARCHAR2,
535 p_int_val_tbl IN activity_tbl_type,
536 p_resp_appl_id IN NUMBER := NULL,
537 p_resp_id IN NUMBER := NULL,
538 x_return_status IN OUT VARCHAR2
539 )
540
541 IS
542 l_count NUMBER := 0;
543
544 BEGIN
545 -- Initialize API return status to success
546 x_return_status := fnd_api.g_ret_sts_success;
547
548 -- Modified to call Validate_Activity_Record
549 for idx in 1 .. p_int_val_tbl.count loop
550 Validate_Activity_Record(p_api_name, p_int_val_tbl(idx),p_resp_appl_id,p_resp_id,x_return_status);
551 IF (x_return_status <> fnd_api.g_ret_sts_success) THEN
552 ----DBMS_OUTPUT.PUT_LINE('Unsuccessful validation of a activity record in jtf_ih_pub.Validate_Activity_table');
553 RETURN;
554 END IF;
555 END loop;
556 END Validate_Activity_table;
557 PROCEDURE Validate_Media_Item
558 ( p_api_name IN VARCHAR2,
559 p_media_item_val IN media_rec_type,
560 p_resp_appl_id IN NUMBER := NULL,
561 p_resp_id IN NUMBER := NULL,
562 x_return_status IN OUT VARCHAR2
563 );
564
565 -- End Utilities Declaration
566 -- Begin Utilities Definition
567 PROCEDURE Validate_Media_Item
568 ( p_api_name IN VARCHAR2,
569 p_media_item_val IN media_rec_type,
570 p_resp_appl_id IN NUMBER := NULL,
571 p_resp_id IN NUMBER := NULL,
572 x_return_status IN OUT VARCHAR2
573 )
574
575 IS
576 l_count NUMBER := 0;
577 BEGIN
578 -- Initialize API return status to success
579 x_return_status := fnd_api.g_ret_sts_success;
580
581
582 IF ((p_media_item_val.source_id IS NOT NULL) AND (p_media_item_val.source_id <> fnd_api.g_miss_num)) THEN
583 SELECT count(*) into l_count
584 FROM jtf_ih_sources
585 WHERE source_id = p_media_item_val.source_id;
586 IF (l_count <= 0) THEN
587 x_return_status := fnd_api.g_ret_sts_error;
588 jtf_ih_core_util_pvt.add_invalid_argument_msg(p_api_name, to_char(p_media_item_val.source_id),
589 'Source_id');
590 RETURN;
591 END IF;
592 ELSE
593 x_return_status := fnd_api.g_ret_sts_error;
594 jtf_ih_core_util_pvt.add_invalid_argument_msg(p_api_name, to_char(p_media_item_val.source_id),
595 'Source_id');
596 RETURN;
597 END IF;
598
599 IF ((p_media_item_val.media_item_type IS NULL) OR (p_media_item_val.media_item_type = fnd_api.g_miss_char)) THEN
600 x_return_status := fnd_api.g_ret_sts_error;
601 jtf_ih_core_util_pvt.add_invalid_argument_msg(p_api_name, p_media_item_val.media_item_type,
602 'media_item_type');
603 RETURN;
604 END IF;
605
606 IF (x_return_status <> fnd_api.g_ret_sts_success) THEN
607 RETURN;
608 END IF;
609 END Validate_Media_Item;
610
611 PROCEDURE Default_Media_Item_Record (x_media IN OUT media_rec_type);
612
613 -- End Utilities Declaration
614 -- Begin Utilities Definition
615 PROCEDURE Default_Media_Item_Record (x_media IN OUT media_rec_type)
616 IS
617 BEGIN
618 if (x_media.source_id = fnd_api.g_miss_num)then
619 x_media.source_id :=0;
620 end if;
621 if (x_media.source_item_id = fnd_api.g_miss_num)then
622 x_media.source_item_id :=0;
623 end if;
624 END Default_Media_Item_Record;
625
626 -- Jean Zhu add Utility Validate_Mlcs_Record
627 PROCEDURE Validate_Mlcs_Record
628 ( p_api_name IN VARCHAR2,
629 p_media_lc_rec IN media_lc_rec_type,
630 p_resp_appl_id IN NUMBER := NULL,
631 p_resp_id IN NUMBER := NULL,
632 x_return_status IN OUT VARCHAR2
633 );
634
635 -- End Utilities Declaration
636 -- Begin Utilities Definition
637 PROCEDURE Validate_Mlcs_Record
638 ( p_api_name IN VARCHAR2,
639 p_media_lc_rec IN media_lc_rec_type,
640 p_resp_appl_id IN NUMBER := NULL,
641 p_resp_id IN NUMBER := NULL,
642 x_return_status IN OUT VARCHAR2
643 )
644
645 IS
646 l_count NUMBER := 0;
647 BEGIN
648 -- Initialize API return status to success
649 x_return_status := fnd_api.g_ret_sts_success;
650 l_count := 0;
651
652 IF ((p_media_lc_rec.milcs_type_id IS NULL) OR (p_media_lc_rec.milcs_type_id = fnd_api.g_miss_num)) THEN
653 x_return_status := fnd_api.g_ret_sts_error;
654 jtf_ih_core_util_pvt.add_invalid_argument_msg(p_api_name, to_char(p_media_lc_rec.milcs_type_id),
655 'milcs_type_id');
656 RETURN;
657 END IF;
658 IF ((p_media_lc_rec.handler_id IS NOT NULL) AND (p_media_lc_rec.handler_id <> fnd_api.g_miss_num)) THEN
659 SELECT count(*) into l_count
660 FROM fnd_application
661 WHERE application_id = p_media_lc_rec.handler_id;
662 IF (l_count <= 0) THEN
663 x_return_status := fnd_api.g_ret_sts_error;
664 jtf_ih_core_util_pvt.add_invalid_argument_msg(p_api_name, to_char(p_media_lc_rec.handler_id),
665 'handler_id');
666 RETURN;
667 END IF;
668 ELSE
669 x_return_status := fnd_api.g_ret_sts_error;
670 jtf_ih_core_util_pvt.add_invalid_argument_msg(p_api_name, to_char(p_media_lc_rec.handler_id),
671 'handler_id');
672 RETURN;
673 END IF;
674 l_count := 0;
675 IF ((p_media_lc_rec.media_id IS NOT NULL) AND (p_media_lc_rec.media_id <> fnd_api.g_miss_num)) THEN
676 SELECT count(*) into l_count
677 FROM jtf_ih_media_items
678 WHERE media_id = p_media_lc_rec.media_id;
679 IF (l_count <= 0) THEN
680 x_return_status := fnd_api.g_ret_sts_error;
681 jtf_ih_core_util_pvt.add_invalid_argument_msg(p_api_name, to_char(p_media_lc_rec.media_id),
682 'media_id');
683 RETURN;
684 END IF;
685 ELSE
686 x_return_status := fnd_api.g_ret_sts_error;
687 jtf_ih_core_util_pvt.add_invalid_argument_msg(p_api_name, to_char(p_media_lc_rec.handler_id),
688 'handler_id');
689 RETURN;
690 END IF;
691 IF (x_return_status <> fnd_api.g_ret_sts_success) THEN
692 RETURN;
693 END IF;
694 END Validate_Mlcs_Record;
695
696 PROCEDURE Validate_Mlcs_table
697 ( p_api_name IN VARCHAR2,
698 p_mlcs_val_tab IN mlcs_tbl_type,
699 p_resp_appl_id IN NUMBER := NULL,
700 p_resp_id IN NUMBER := NULL,
701 x_return_status IN OUT VARCHAR2
702 );
703
704 -- End Utilities Declaration
705 -- Begin Utilities Definition
706 PROCEDURE Validate_Mlcs_table
707 ( p_api_name IN VARCHAR2,
708 p_mlcs_val_tab IN mlcs_tbl_type,
709 p_resp_appl_id IN NUMBER := NULL,
710 p_resp_id IN NUMBER := NULL,
711 x_return_status IN OUT VARCHAR2
712 )
713
714 IS
715 l_count NUMBER := 0;
716 BEGIN
717 -- Initialize API return status to success
718 x_return_status := fnd_api.g_ret_sts_success;
719 l_count := 0;
720
721 for idx in 1 .. p_mlcs_val_tab.count loop
722 Validate_Mlcs_Record ( p_api_name, p_mlcs_val_tab(idx), p_resp_appl_id, p_resp_id, x_return_status);
723 END loop;
724
725 IF (x_return_status <> fnd_api.g_ret_sts_success) THEN
726 ----DBMS_OUTPUT.PUT_LINE('Unsuccessful validation of a media_lc record in jtf_ih_pub_PS.Validate_Mlcs_table');
727 RETURN;
728 END IF;
729 END Validate_Mlcs_table;
730
731 PROCEDURE Default_Mlcs_table (x_mlcs IN OUT mlcs_tbl_type);
732
733 -- End Utilities Declaration
734 -- Begin Utilities Definition
735 PROCEDURE Default_Mlcs_table (x_mlcs IN OUT mlcs_tbl_type)
736 IS
737 BEGIN
738 for idx in 1 .. x_mlcs.count loop
739 if (x_mlcs(idx).type_id = fnd_api.g_miss_num)then
740 x_mlcs(idx).type_id :=0;
741 end if;
742 if (x_mlcs(idx).handler_id = fnd_api.g_miss_num)then
743 x_mlcs(idx).handler_id :=0;
744 end if;
745 end loop;
746 END Default_Mlcs_table;
747 --
748 -- old version
749 --
750 PROCEDURE Create_MediaItem
751 (
752 p_api_version IN NUMBER,
753 p_init_msg_list IN VARCHAR2, --DEFAULT FND_API.G_FALSE,
754 p_commit IN VARCHAR2, --DEFAULT FND_API.G_FALSE,
755 p_resp_appl_id IN NUMBER DEFAULT NULL,
756 p_resp_id IN NUMBER DEFAULT NULL,
757 p_user_id IN NUMBER,
758 p_login_id IN NUMBER DEFAULT NULL,
759 x_return_status OUT VARCHAR2,
760 x_msg_count OUT NUMBER,
761 x_msg_data OUT VARCHAR2,
762 p_media IN media_rec_type,
763 p_mlcs IN mlcs_tbl_type
764 ) AS
765 l_api_name CONSTANT VARCHAR2(30) := 'Create_MediaItem';
766 l_api_version CONSTANT NUMBER := 1.0;
767 l_api_name_full CONSTANT VARCHAR2(61) := g_pkg_name||'.'||l_api_name;
768 l_return_status VARCHAR2(1);
769 l_int_val_rec media_rec_type := p_media;
770 l_milcs_id NUMBER;
771 l_mlcs mlcs_tbl_type := p_mlcs;
772
773 BEGIN
774 -- Standard start of API savepoint
775 SAVEPOINT create_media_pub;
776
777 -- Standard call to check for call compatibility
778 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version,
779 l_api_name, g_pkg_name) THEN
780 RAISE fnd_api.g_exc_unexpected_error;
781 END IF;
782
783 -- Initialize message list if p_init_msg_list is set to TRUE
784 IF fnd_api.to_boolean(p_init_msg_list) THEN
785 fnd_msg_pub.initialize;
786 END IF;
787
788 -- Initialize API return status to success
789 x_return_status := fnd_api.g_ret_sts_success;
790
791 --
792 -- Validate user and login session IDs
793 --
794 IF (p_user_id IS NULL) THEN
795 jtf_ih_core_util_pvt.add_null_parameter_msg(l_api_name_full, 'p_user_id');
796 RAISE fnd_api.g_exc_error;
797 ELSE
798 jtf_ih_core_util_pvt.validate_who_info
799 ( p_api_name => l_api_name_full,
800 p_parameter_name_usr => 'p_user_id',
801 p_parameter_name_log => 'p_login_id',
802 p_user_id => p_user_id,
803 p_login_id => p_login_id,
804 x_return_status => l_return_status );
805 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
806 RAISE fnd_api.g_exc_error;
807 END IF;
808 END IF;
809
810 Validate_Media_Item
811 ( p_api_name => l_api_name_full,
812 p_media_item_val => p_media,
813 p_resp_appl_id => p_resp_appl_id,
814 p_resp_id => p_resp_id,
815 x_return_status => l_return_status
816 );
817 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
818 RAISE fnd_api.g_exc_error;
819 END IF;
820 Default_Media_Item_Record(l_int_val_rec);
821 IF ((p_media.media_id IS NULL) OR (p_media.media_id = fnd_api.g_miss_num)) THEN
822 SELECT jtf_ih_media_items_s1.NEXTVAL INTO l_int_val_rec.media_id FROM dual;
823 END IF;
824
825 insert into jtf_ih_Media_Items
826 (
827 DURATION,
828 DIRECTION,
829 END_DATE_TIME,
830 SOURCE_ITEM_CREATE_DATE_TIME,
831 INTERACTION_PERFORMED,
832 SOURCE_ITEM_ID,
833 START_DATE_TIME,
834 MEDIA_ID,
835 SOURCE_ID,
836 MEDIA_ITEM_TYPE,
837 CREATED_BY,
838 CREATION_DATE,
839 LAST_UPDATED_BY,
840 LAST_UPDATE_DATE,
841 LAST_UPDATE_LOGIN,
842 MEDIA_ITEM_REF,
843 MEDIA_DATA
844 ) values (
845 l_int_val_rec.duration,
846 l_int_val_rec.direction,
847 l_int_val_rec.end_date_time,
848 l_int_val_rec.source_item_create_date_time,
849 l_int_val_rec.interaction_performed,
850 l_int_val_rec.source_item_id,
851 l_int_val_rec.start_date_time,
852 l_int_val_rec.media_id,
853 l_int_val_rec.source_id,
854 l_int_val_rec.media_item_type,
855 p_user_id,
856 SysDate,
857 p_user_id,
858 SysDate,
859 p_login_id,
860 l_int_val_rec.media_item_ref,
861 l_int_val_rec.media_data
862 );
863 --
864 Validate_Mlcs_table
865 ( p_api_name => l_api_name_full,
866 p_mlcs_val_tab => p_mlcs,
867 p_resp_appl_id => p_resp_appl_id,
868 p_resp_id => p_resp_id,
869 x_return_status => l_return_status
870 );
871 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
872 RAISE fnd_api.g_exc_error;
873 END IF;
874
875 Default_Mlcs_table(l_mlcs);
876 for idx in 1 .. p_mlcs.count loop
877 IF ((p_mlcs(idx).milcs_id IS NULL) OR (p_mlcs(idx).milcs_id = fnd_api.g_miss_num)) THEN
878 SELECT jtf_ih_media_item_lc_seg_s1.NEXTVAL INTO l_mlcs(idx).milcs_id FROM dual;
879 END IF;
880
881 insert into jtf_ih_media_item_lc_segs
882 (
883 START_DATE_TIME,
884 TYPE_TYPE,
885 TYPE_ID,
886 DURATION,
887 END_DATE_TIME,
888 MILCS_ID,
889 MILCS_TYPE_ID,
890 MEDIA_ID,
891 HANDLER_ID,
892 CREATED_BY,
893 CREATION_DATE,
894 LAST_UPDATED_BY,
895 LAST_UPDATE_DATE,
896 LAST_UPDATE_LOGIN
897 )
898 values
899 (
900 l_mlcs(idx).start_date_time,
901 l_mlcs(idx).type_type,
902 l_mlcs(idx).type_id,
903 l_mlcs(idx).duration,
904 l_mlcs(idx).end_date_time,
905 l_mlcs(idx).milcs_id,
906 l_mlcs(idx).milcs_type_id,
907 l_int_val_rec.media_id,
908 l_mlcs(idx).handler_id,
909 p_user_id,
910 Sysdate,
911 p_user_id,
912 Sysdate,
913 p_login_id
914 );
915 END loop;
916
917
918 -- Standard check of p_commit
919 IF fnd_api.to_boolean(p_commit) THEN
920 COMMIT WORK;
921 END IF;
922
923 -- Standard call to get message count and if count is 1, get message info
924 fnd_msg_pub.count_and_get
925 ( p_count => x_msg_count,
926 p_data => x_msg_data );
927 EXCEPTION
928 WHEN fnd_api.g_exc_error THEN
929 ROLLBACK TO create_media_pub;
930 x_return_status := fnd_api.g_ret_sts_error;
931 fnd_msg_pub.count_and_get
932 ( p_count => x_msg_count,
933 p_data => x_msg_data );
934 WHEN fnd_api.g_exc_unexpected_error THEN
935 ROLLBACK TO create_media_pub;
936 x_return_status := fnd_api.g_ret_sts_unexp_error;
937 fnd_msg_pub.count_and_get
938 ( p_count => x_msg_count,
939 p_data => x_msg_data );
940 WHEN OTHERS THEN
941 ROLLBACK TO create_media_pub;
942 x_return_status := fnd_api.g_ret_sts_unexp_error;
943 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
944 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
945 END IF;
946 fnd_msg_pub.count_and_get
947 ( p_count => x_msg_count,
948 p_data => x_msg_data );
949
950 END Create_MediaItem;
951
952 -- Jean Zhu split old version PROCEDURE Create_MediaItem() to
953 -- two PROCEDUREs Create_MediaItem() and Create_MediaLifecycle()
954
955 PROCEDURE Create_MediaItem
956 (
957 p_api_version IN NUMBER,
958 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
959 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
960 p_resp_appl_id IN NUMBER DEFAULT NULL,
961 p_resp_id IN NUMBER DEFAULT NULL,
962 p_user_id IN NUMBER,
963 p_login_id IN NUMBER DEFAULT NULL,
964 x_return_status OUT VARCHAR2,
965 x_msg_count OUT NUMBER,
966 x_msg_data OUT VARCHAR2,
967 p_media_rec IN media_rec_type,
968 x_media_id OUT NUMBER
969 )AS
970
971 l_api_name CONSTANT VARCHAR2(30) := 'Create_MediaItem';
972 l_api_version CONSTANT NUMBER := 1.0;
973 l_api_name_full CONSTANT VARCHAR2(61) := g_pkg_name||'.'||l_api_name;
974 l_return_status VARCHAR2(1);
975 l_media_id NUMBER := NULL;
976
977 BEGIN
978 -- Standard start of API savepoint
979 SAVEPOINT create_media_pub;
980
981 -- Standard call to check for call compatibility
982 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version,
983 l_api_name, g_pkg_name) THEN
984 RAISE fnd_api.g_exc_unexpected_error;
985 END IF;
986 ----DBMS_OUTPUT.PUT_LINE('PAST fnd_api.compatible_api_call in JTF_IH_PUB_PS.Create_MediaItem');
987
988 -- Initialize message list if p_init_msg_list is set to TRUE
989 IF fnd_api.to_boolean(p_init_msg_list) THEN
990 fnd_msg_pub.initialize;
991 END IF;
992
993 -- Initialize API return status to success
994 x_return_status := fnd_api.g_ret_sts_success;
995
996 --
997 -- Validate user and login session IDs
998 --
999 IF (p_user_id IS NULL) THEN
1000 jtf_ih_core_util_pvt.add_null_parameter_msg(l_api_name_full, 'p_user_id');
1001 RAISE fnd_api.g_exc_error;
1002 ELSE
1003 jtf_ih_core_util_pvt.validate_who_info
1004 ( p_api_name => l_api_name_full,
1005 p_parameter_name_usr => 'p_user_id',
1006 p_parameter_name_log => 'p_login_id',
1007 p_user_id => p_user_id,
1008 p_login_id => p_login_id,
1009 x_return_status => l_return_status );
1010 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
1011 RAISE fnd_api.g_exc_error;
1012 END IF;
1013 END IF;
1014 ----DBMS_OUTPUT.PUT_LINE('PAST jtf_ih_core_util_pvt.validate_who_info in JTF_IH_PUB_PS.Create_MediaItem');
1015
1016 Validate_Media_Item
1017 ( p_api_name => l_api_name_full,
1018 p_media_item_val => p_media_rec,
1019 p_resp_appl_id => p_resp_appl_id,
1020 p_resp_id => p_resp_id,
1021 x_return_status => l_return_status
1022 );
1023 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
1024 RAISE fnd_api.g_exc_error;
1025 END IF;
1026
1027 ----DBMS_OUTPUT.PUT_LINE('PAST Validate_Media_Item in JTF_IH_PUB_PS.Create_MediaItem');
1028
1029 SELECT jtf_ih_media_items_s1.NEXTVAL INTO l_media_id FROM dual;
1030
1031 ----DBMS_OUTPUT.PUT_LINE('PAST generate PK in JTF_IH_PUB_PS.Create_MediaItem');
1032 insert into jtf_ih_Media_Items
1033 (
1034 DURATION,
1035 DIRECTION,
1036 END_DATE_TIME,
1037 SOURCE_ITEM_CREATE_DATE_TIME,
1038 INTERACTION_PERFORMED,
1039 SOURCE_ITEM_ID,
1040 START_DATE_TIME,
1041 MEDIA_ID,
1042 SOURCE_ID,
1043 MEDIA_ITEM_TYPE,
1044 CREATED_BY,
1045 CREATION_DATE,
1046 LAST_UPDATED_BY,
1047 LAST_UPDATE_DATE,
1048 LAST_UPDATE_LOGIN,
1049 MEDIA_ITEM_REF,
1050 MEDIA_DATA
1051 ) values (
1052 p_media_rec.duration,
1053 p_media_rec.direction,
1054 p_media_rec.end_date_time,
1055 p_media_rec.source_item_create_date_time,
1056 p_media_rec.interaction_performed,
1057 p_media_rec.source_item_id,
1058 p_media_rec.start_date_time,
1059 l_media_id,
1060 p_media_rec.source_id,
1061 p_media_rec.media_item_type,
1062 p_user_id,
1063 SysDate,
1064 p_user_id,
1065 SysDate,
1066 p_login_id,
1067 p_media_rec.media_item_ref,
1068 p_media_rec.media_data
1069 );
1070 ----DBMS_OUTPUT.PUT_LINE('PAST Insert data in JTF_IH_PUB_PS.Create_MediaItem');
1071
1072 --
1073 -- Output
1074 -- --
1075 x_media_id := l_media_id;
1076
1077 -- Standard check of p_commit
1078 IF fnd_api.to_boolean(p_commit) THEN
1079 COMMIT WORK;
1080 END IF;
1081
1082 -- Standard call to get message count and if count is 1, get message info
1083 fnd_msg_pub.count_and_get
1084 ( p_count => x_msg_count,
1085 p_data => x_msg_data );
1086 EXCEPTION
1087 WHEN fnd_api.g_exc_error THEN
1088 ROLLBACK TO create_media_pub;
1089 x_return_status := fnd_api.g_ret_sts_error;
1090 fnd_msg_pub.count_and_get
1091 ( p_count => x_msg_count,
1092 p_data => x_msg_data );
1093 WHEN fnd_api.g_exc_unexpected_error THEN
1094 ROLLBACK TO create_media_pub;
1095 x_return_status := fnd_api.g_ret_sts_unexp_error;
1096 fnd_msg_pub.count_and_get
1097 ( p_count => x_msg_count,
1098 p_data => x_msg_data );
1099 WHEN OTHERS THEN
1100 ROLLBACK TO create_media_pub;
1101 x_return_status := fnd_api.g_ret_sts_unexp_error;
1102 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1103 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1104 END IF;
1105 fnd_msg_pub.count_and_get
1106 ( p_count => x_msg_count,
1107 p_data => x_msg_data );
1108
1109 END Create_MediaItem;
1110
1111
1112 PROCEDURE Create_MediaLifecycle
1113 (
1114 p_api_version IN NUMBER,
1115 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1116 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1117 p_resp_appl_id IN NUMBER DEFAULT NULL,
1118 p_resp_id IN NUMBER DEFAULT NULL,
1119 p_user_id IN NUMBER,
1120 p_login_id IN NUMBER DEFAULT NULL,
1121 x_return_status OUT VARCHAR2,
1122 x_msg_count OUT NUMBER,
1123 x_msg_data OUT VARCHAR2,
1124 p_media_lc_rec IN media_lc_rec_type
1125 )AS
1126
1127 l_api_name CONSTANT VARCHAR2(30) := 'Create_MediaLifecycle';
1128 l_api_version CONSTANT NUMBER := 1.0;
1129 l_api_name_full CONSTANT VARCHAR2(61) := g_pkg_name||'.'||l_api_name;
1130 l_return_status VARCHAR2(1);
1131 l_milcs_id NUMBER := NULL;
1132
1133 BEGIN
1134 -- Standard start of API savepoint
1135 SAVEPOINT create_media_lc_pub;
1136
1137 -- Standard call to check for call compatibility
1138 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version,
1139 l_api_name, g_pkg_name) THEN
1140 RAISE fnd_api.g_exc_unexpected_error;
1141 END IF;
1142 ----DBMS_OUTPUT.PUT_LINE('PAST fnd_api.compatible_api_call in JTF_IH_PUB_PS.Create_MediaLifecycle');
1143 -- Initialize message list if p_init_msg_list is set to TRUE
1144 IF fnd_api.to_boolean(p_init_msg_list) THEN
1145 fnd_msg_pub.initialize;
1146 END IF;
1147
1148 -- Initialize API return status to success
1149 x_return_status := fnd_api.g_ret_sts_success;
1150
1151 --
1152 -- Validate user and login session IDs
1153 --
1154 IF (p_user_id IS NULL) THEN
1155 jtf_ih_core_util_pvt.add_null_parameter_msg(l_api_name_full, 'p_user_id');
1156 RAISE fnd_api.g_exc_error;
1157 ELSE
1158 jtf_ih_core_util_pvt.validate_who_info
1159 ( p_api_name => l_api_name_full,
1160 p_parameter_name_usr => 'p_user_id',
1161 p_parameter_name_log => 'p_login_id',
1162 p_user_id => p_user_id,
1163 p_login_id => p_login_id,
1164 x_return_status => l_return_status );
1165 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
1166 RAISE fnd_api.g_exc_error;
1167 END IF;
1168 END IF;
1169 ----DBMS_OUTPUT.PUT_LINE('PAST jtf_ih_core_util_pvt.validate_who_info in JTF_IH_PUB_PS.Create_MediaLifecycle');
1170 Validate_Mlcs_Record
1171 ( p_api_name => l_api_name_full,
1172 p_media_lc_rec => p_media_lc_rec,
1173 p_resp_appl_id => p_resp_appl_id,
1174 p_resp_id => p_resp_id,
1175 x_return_status => l_return_status
1176 );
1177 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
1178 RAISE fnd_api.g_exc_error;
1179 END IF;
1180 ----DBMS_OUTPUT.PUT_LINE('PAST Validate_Mlcs_Record in JTF_IH_PUB_PS.Create_MediaLifecycle');
1181
1182
1183 SELECT jtf_ih_media_item_lc_seg_s1.NEXTVAL INTO l_milcs_id FROM dual;
1184 ----DBMS_OUTPUT.PUT_LINE('PAST generate PK in JTF_IH_PUB_PS.Create_MediaLifecycle');
1185
1186 insert into jtf_ih_media_item_lc_segs
1187 (
1188 START_DATE_TIME,
1189 TYPE_TYPE,
1190 TYPE_ID,
1191 DURATION,
1192 END_DATE_TIME,
1193 MILCS_ID,
1194 MILCS_TYPE_ID,
1195 MEDIA_ID,
1196 HANDLER_ID,
1197 RESOURCE_ID,
1198 CREATED_BY,
1199 CREATION_DATE,
1200 LAST_UPDATED_BY,
1201 LAST_UPDATE_DATE,
1202 LAST_UPDATE_LOGIN
1203 )
1204 values
1205 (
1206 p_media_lc_rec.start_date_time,
1207 p_media_lc_rec.type_type,
1208 p_media_lc_rec.type_id,
1209 p_media_lc_rec.duration,
1210 p_media_lc_rec.end_date_time,
1211 l_milcs_id,
1212 p_media_lc_rec.milcs_type_id,
1213 p_media_lc_rec.media_id,
1214 p_media_lc_rec.handler_id,
1215 p_media_lc_rec.resource_id,
1216 p_user_id,
1217 Sysdate,
1218 p_user_id,
1219 Sysdate,
1220 p_login_id
1221 );
1222 --DBMS_OUTPUT.PUT_LINE('PAST insert data in JTF_IH_PUB_PS.Create_MediaLifecycle');
1223
1224 -- Standard check of p_commit
1225 IF fnd_api.to_boolean(p_commit) THEN
1226 COMMIT WORK;
1227 END IF;
1228
1229 -- Standard call to get message count and if count is 1, get message info
1230 fnd_msg_pub.count_and_get
1231 ( p_count => x_msg_count,
1232 p_data => x_msg_data );
1233 EXCEPTION
1234 WHEN fnd_api.g_exc_error THEN
1235 ROLLBACK TO create_media_lc_pub;
1236 x_return_status := fnd_api.g_ret_sts_error;
1237 fnd_msg_pub.count_and_get
1238 ( p_count => x_msg_count,
1239 p_data => x_msg_data );
1240 WHEN fnd_api.g_exc_unexpected_error THEN
1241 ROLLBACK TO create_media_lc_pub;
1242 x_return_status := fnd_api.g_ret_sts_unexp_error;
1243 fnd_msg_pub.count_and_get
1244 ( p_count => x_msg_count,
1245 p_data => x_msg_data );
1246 WHEN OTHERS THEN
1247 ROLLBACK TO create_media_lc_pub;
1248 x_return_status := fnd_api.g_ret_sts_unexp_error;
1249 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1250 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1251 END IF;
1252 fnd_msg_pub.count_and_get
1253 ( p_count => x_msg_count,
1254 p_data => x_msg_data );
1255
1256 END Create_MediaLifecycle;
1257
1258 PROCEDURE Create_Interaction(
1259 p_api_version IN NUMBER,
1260 p_init_msg_list IN VARCHAR2, --DEFAULT FND_API.G_FALSE,
1261 p_commit IN VARCHAR2, --DEFAULT FND_API.G_FALSE,
1262 p_resp_appl_id IN NUMBER DEFAULT NULL,
1263 p_resp_id IN NUMBER DEFAULT NULL,
1264 p_user_id IN NUMBER,
1265 p_login_id IN NUMBER DEFAULT NULL,
1266 x_return_status OUT VARCHAR2,
1267 x_msg_count OUT NUMBER,
1268 x_msg_data OUT VARCHAR2,
1269 p_interaction_rec IN interaction_rec_type,
1270 p_activities IN activity_tbl_type
1271 ) IS
1272 l_api_name CONSTANT VARCHAR2(30) := 'Create_Interaction';
1273 l_api_version CONSTANT NUMBER := 1.0;
1274 l_api_name_full CONSTANT VARCHAR2(61) := g_pkg_name||'.'||l_api_name;
1275 l_return_status VARCHAR2(1);
1276 l_int_val_rec interaction_rec_type := p_interaction_rec;
1277 l_interaction_id NUMBER;
1278 l_activity_id NUMBER;
1279 l_activities activity_tbl_type := p_activities;
1280
1281 BEGIN
1282
1283 -- Standard start of API savepoint
1284 SAVEPOINT create_interaction_pub;
1285
1286 -- Standard call to check for call compatibility
1287 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version,
1288 l_api_name, g_pkg_name) THEN
1289 RAISE fnd_api.g_exc_unexpected_error;
1290 END IF;
1291 --DBMS_OUTPUT.PUT_LINE('PAST fnd_api.compatible_api_call in JTF_IH_PUB_PS.Create_Interaction');
1292
1293 -- Initialize message list if p_init_msg_list is set to TRUE
1294 IF fnd_api.to_boolean(p_init_msg_list) THEN
1295 fnd_msg_pub.initialize;
1296 END IF;
1297
1298 -- Initialize API return status to success
1299 x_return_status := fnd_api.g_ret_sts_success;
1300
1301 --
1302 -- Apply business-rule validation to all required and passed parameters
1303 --
1304 -- Validate user and login session IDs
1305 --
1306 IF (p_user_id IS NULL) THEN
1307 jtf_ih_core_util_pvt.add_null_parameter_msg(l_api_name_full, 'p_user_id');
1308 RAISE fnd_api.g_exc_error;
1309 ELSE
1310 jtf_ih_core_util_pvt.validate_who_info
1311 ( p_api_name => l_api_name_full,
1312 p_parameter_name_usr => 'p_user_id',
1313 p_parameter_name_log => 'p_login_id',
1314 p_user_id => p_user_id,
1315 p_login_id => p_login_id,
1316 x_return_status => l_return_status );
1317 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
1318 RAISE fnd_api.g_exc_error;
1319 END IF;
1320 END IF;
1321 --DBMS_OUTPUT.PUT_LINE('PAST jtf_ih_core_util_pvt.validate_who_info in JTF_IH_PUB_PS.Create_Interaction');
1322
1323 --
1324 -- Validate all non-missing attributes by calling the utility procedure.
1325 --
1326 Validate_Interaction_Record
1327 ( p_api_name => l_api_name_full,
1328 p_int_val_rec => p_interaction_rec,
1329 p_resp_appl_id => p_resp_appl_id,
1330 p_resp_id => p_resp_id,
1331 x_return_status => l_return_status
1332 );
1333 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
1334 RAISE fnd_api.g_exc_error;
1335 END IF;
1336 --DBMS_OUTPUT.PUT_LINE('PAST Validate_Interaction_Record in JTF_IH_PUB_PS.Create_Interaction');
1337 Default_Interaction_Record(l_int_val_rec);
1338 IF ((p_interaction_rec.interaction_id IS NULL) OR (p_interaction_rec.interaction_id = fnd_api.g_miss_num)) THEN
1339 SELECT jtf_ih_interactions_s1.NEXTVAL INTO l_int_val_rec.interaction_id FROM dual;
1340
1341 END IF;
1342
1343 INSERT INTO jtf_ih_Interactions
1344 (
1345 CREATED_BY,
1346 REFERENCE_FORM,
1347 CREATION_DATE,
1348 LAST_UPDATED_BY,
1349 DURATION,
1350 LAST_UPDATE_DATE,
1351 LAST_UPDATE_LOGIN,
1352 END_DATE_TIME,
1353 FOLLOW_UP_ACTION,
1354 NON_PRODUCTIVE_TIME_AMOUNT,
1355 RESULT_ID,
1356 REASON_ID,
1357 START_DATE_TIME,
1358 OUTCOME_ID,
1359 PREVIEW_TIME_AMOUNT,
1360 PRODUCTIVE_TIME_AMOUNT,
1361 HANDLER_ID,
1362 INTER_INTERACTION_DURATION,
1363 INTERACTION_ID,
1364 WRAP_UP_TIME_AMOUNT,
1365 SCRIPT_ID,
1366 PARTY_ID,
1367 RESOURCE_ID,
1368 OBJECT_ID,
1369 OBJECT_TYPE,
1370 SOURCE_CODE_ID,
1371 SOURCE_CODE,
1372 ATTRIBUTE1,
1373 ATTRIBUTE2,
1374 ATTRIBUTE3,
1375 ATTRIBUTE4,
1376 ATTRIBUTE5,
1377 ATTRIBUTE6,
1378 ATTRIBUTE7,
1379 ATTRIBUTE8,
1380 ATTRIBUTE9,
1381 ATTRIBUTE10,
1382 ATTRIBUTE11,
1383 ATTRIBUTE12,
1384 ATTRIBUTE13,
1385 ATTRIBUTE14,
1386 ATTRIBUTE15,
1387 ATTRIBUTE_CATEGORY,
1388 ACTIVE
1389 )
1390 VALUES
1391 (
1392 p_user_id,
1393 l_int_val_rec.reference_form,
1394 Sysdate,
1395 p_user_id,
1396 l_int_val_rec.duration,
1397 Sysdate,
1398 p_login_id,
1399 l_int_val_rec.end_date_time,
1400 l_int_val_rec.follow_up_action,
1401 l_int_val_rec.non_productive_time_amount,
1402 l_int_val_rec.result_id,
1403 l_int_val_rec.reason_id,
1404 l_int_val_rec.start_date_time,
1405 l_int_val_rec.outcome_id,
1406 l_int_val_rec.preview_time_amount,
1407 l_int_val_rec.productive_time_amount,
1408 l_int_val_rec.handler_id,
1409 l_int_val_rec.inter_interaction_duration,
1410 l_int_val_rec.interaction_id,
1411 l_int_val_rec.wrapup_time_amount,
1412 l_int_val_rec.script_id,
1413 l_int_val_rec.party_id,
1414 l_int_val_rec.resource_id,
1415 l_int_val_rec.object_id,
1416 l_int_val_rec.object_type,
1417 l_int_val_rec.source_code_id,
1418 l_int_val_rec.source_code,
1419 l_int_val_rec.attribute1,
1420 l_int_val_rec.attribute2,
1421 l_int_val_rec.attribute3,
1422 l_int_val_rec.attribute4,
1423 l_int_val_rec.attribute5,
1424 l_int_val_rec.attribute6,
1425 l_int_val_rec.attribute7,
1426 l_int_val_rec.attribute8,
1427 l_int_val_rec.attribute9,
1428 l_int_val_rec.attribute10,
1429 l_int_val_rec.attribute11,
1430 l_int_val_rec.attribute12,
1431 l_int_val_rec.attribute13,
1432 l_int_val_rec.attribute14,
1433 l_int_val_rec.attribute15,
1434 l_int_val_rec.attribute_category,
1435 'Y'
1436 );
1437 --DBMS_OUTPUT.PUT_LINE('PAST INSERT INTO jtf_ih_Interactions in JTF_IH_PUB_PS.Create_Interaction');
1438
1439 Validate_Activity_table
1440 (
1441 p_api_name => l_api_name_full,
1442 p_int_val_tbl => p_activities,
1443 p_resp_appl_id => p_resp_appl_id,
1444 p_resp_id => p_resp_id,
1445 x_return_status => l_return_status
1446 );
1447 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
1448 RAISE fnd_api.g_exc_error;
1449 END IF;
1450
1451 Default_activity_table(l_activities);
1452 for idx in 1 .. p_activities.count loop
1453 IF ((p_activities(idx).activity_id IS NULL) OR (p_activities(idx).activity_id = fnd_api.g_miss_num)) THEN
1454 SELECT jtf_ih_activities_s1.NEXTVAL INTO l_activities(idx).activity_id FROM dual;
1455 END IF;
1456 insert into jtf_ih_Activities
1457 (
1458 OBJECT_ID,
1459 OBJECT_TYPE,
1460 SOURCE_CODE_ID,
1461 SOURCE_CODE,
1462 DURATION,
1463 DESCRIPTION,
1464 DOC_ID,
1465 END_DATE_TIME,
1466 ACTIVITY_ID,
1467 RESULT_ID,
1468 REASON_ID,
1469 START_DATE_TIME,
1470 INTERACTION_ACTION_TYPE,
1471 MEDIA_ID,
1472 OUTCOME_ID,
1473 ACTION_ITEM_ID,
1474 INTERACTION_ID,
1475 TASK_ID,
1476 CREATION_DATE,
1477 CREATED_BY,
1478 LAST_UPDATED_BY,
1479 LAST_UPDATE_DATE,
1480 LAST_UPDATE_LOGIN,
1481 ACTION_ID,
1482 ACTIVE
1483 )
1484 values
1485 (
1486 l_activities(idx).object_id,
1487 l_activities(idx).object_type,
1488 l_activities(idx).source_code_id,
1489 l_activities(idx).source_code,
1490 l_activities(idx).duration,
1491 l_activities(idx).description,
1492 l_activities(idx).doc_id,
1493 l_activities(idx).end_date_time,
1494 l_activities(idx).activity_id,
1495 l_activities(idx).result_id,
1496 l_activities(idx).reason_id,
1497 l_activities(idx).start_date_time,
1498 l_activities(idx).interaction_action_type,
1499 l_activities(idx).media_id,
1500 l_activities(idx).outcome_id,
1501 l_activities(idx).action_item_id,
1502 l_int_val_rec.interaction_id,
1503 l_activities(idx).task_id,
1504 Sysdate,
1505 p_user_id,
1506 p_user_id,
1507 Sysdate,
1508 p_login_id,
1509 l_activities(idx).action_id,
1510 'Y'
1511 );
1512 END loop;
1513
1514 IF ((l_int_val_rec.parent_id IS NOT NULL) AND (l_int_val_rec.parent_id <> fnd_api.g_miss_num)) THEN
1515 insert into jtf_ih_interaction_inters
1516 (
1517 INTERACT_INTERACTION_ID,
1518 INTERACT_INTERACTION_IDRELATES,
1519 CREATED_BY,
1520 CREATION_DATE,
1521 LAST_UPDATED_BY,
1522 LAST_UPDATE_DATE,
1523 LAST_UPDATE_LOGIN
1524 )
1525 values
1526 (
1527 l_int_val_rec.interaction_id,
1528 l_int_val_rec.parent_id,
1529 p_user_id,
1530 Sysdate,
1531 p_user_id,
1532 Sysdate,
1533 p_user_id
1534 );
1535 END IF;
1536 --
1537 -- Set OUT value
1538 --
1539 --x_interaction_id := l_int_val_rec.interaction_id;
1540
1541 -- Standard check of p_commit
1542 IF fnd_api.to_boolean(p_commit) THEN
1543 COMMIT WORK;
1544 END IF;
1545
1546 -- Standard call to get message count and if count is 1, get message info
1547 fnd_msg_pub.count_and_get
1548 ( p_count => x_msg_count,
1549 p_data => x_msg_data );
1550 EXCEPTION
1551 WHEN fnd_api.g_exc_error THEN
1552 ROLLBACK TO create_interaction_pub;
1553 x_return_status := fnd_api.g_ret_sts_error;
1554 fnd_msg_pub.count_and_get
1555 ( p_count => x_msg_count,
1556 p_data => x_msg_data );
1557 WHEN fnd_api.g_exc_unexpected_error THEN
1558 ROLLBACK TO create_interaction_pub;
1559 x_return_status := fnd_api.g_ret_sts_unexp_error;
1560 fnd_msg_pub.count_and_get
1561 ( p_count => x_msg_count,
1562 p_data => x_msg_data );
1563 WHEN OTHERS THEN
1564 ROLLBACK TO create_interaction_pub;
1565 x_return_status := fnd_api.g_ret_sts_unexp_error;
1566 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1567 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1568 END IF;
1569 fnd_msg_pub.count_and_get
1570 ( p_count => x_msg_count,
1571 p_data => x_msg_data );
1572
1573 END Create_Interaction;
1574
1575
1576
1577 PROCEDURE Get_InteractionActivityCount
1578 (
1579 p_api_version IN NUMBER,
1580 p_init_msg_list IN VARCHAR2, --DEFAULT FND_API.G_FALSE,
1581 p_resp_appl_id IN NUMBER DEFAULT NULL,
1582 p_resp_id IN NUMBER DEFAULT NULL,
1583 p_user_id IN NUMBER,
1584 p_login_id IN NUMBER DEFAULT NULL,
1585 x_return_status OUT VARCHAR2,
1586 x_msg_count OUT NUMBER,
1587 x_msg_data OUT VARCHAR2,
1588 p_outcome_id IN NUMBER,
1589 p_result_id IN NUMBER,
1590 p_reason_id IN NUMBER,
1591 p_script_id IN NUMBER,
1592 p_media_id IN NUMBER,
1593 x_activity_count OUT NUMBER
1594 ) AS
1595 l_api_name CONSTANT VARCHAR2(30) := 'Get_InteractionActivityCount';
1596 l_api_version CONSTANT NUMBER := 1.1;
1597 l_api_name_full CONSTANT VARCHAR2(61) := g_pkg_name||'.'||l_api_name;
1598 l_return_status VARCHAR2(1);
1599
1600 actionCount NUMBER;
1601
1602 BEGIN
1603
1604 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version,
1605 l_api_name, g_pkg_name) THEN
1606 RAISE fnd_api.g_exc_unexpected_error;
1607 END IF;
1608
1609 -- Initialize message list if p_init_msg_list is set to TRUE
1610 IF fnd_api.to_boolean(p_init_msg_list) THEN
1611 fnd_msg_pub.initialize;
1612 END IF;
1613
1614 -- Initialize API return status to success
1615 x_return_status := fnd_api.g_ret_sts_success;
1616
1617 --
1618 -- Apply business-rule validation to all required and passed parameters
1619 --
1620 -- Validate user and login session IDs
1621 --
1622 IF (p_user_id IS NULL) THEN
1623 jtf_ih_core_util_pvt.add_null_parameter_msg(l_api_name_full, 'p_user_id');
1624 RAISE fnd_api.g_exc_error;
1625 ELSE
1626 jtf_ih_core_util_pvt.validate_who_info
1627 ( p_api_name => l_api_name_full,
1628 p_parameter_name_usr => 'p_user_id',
1629 p_parameter_name_log => 'p_login_id',
1630 p_user_id => p_user_id,
1631 p_login_id => p_login_id,
1632 x_return_status => l_return_status );
1633 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
1634 RAISE fnd_api.g_exc_error;
1635 END IF;
1636 END IF;
1637
1638 SELECT count(*) into actionCount
1639 FROM jtf_ih_Activities
1640 where outcome_id = p_outcome_id
1641 and result_id = p_result_id
1642 and reason_id = p_reason_id
1643 and media_id = p_media_id
1644 ;
1645 x_activity_count := actionCount;
1646 -- Standard call to get message count and if count is 1, get message info
1647 fnd_msg_pub.count_and_get
1648 ( p_count => x_msg_count,
1649 p_data => x_msg_data );
1650 EXCEPTION
1651 WHEN fnd_api.g_exc_error THEN
1652 x_return_status := fnd_api.g_ret_sts_error;
1653 fnd_msg_pub.count_and_get
1654 ( p_count => x_msg_count,
1655 p_data => x_msg_data );
1656 WHEN fnd_api.g_exc_unexpected_error THEN
1657 x_return_status := fnd_api.g_ret_sts_unexp_error;
1658 fnd_msg_pub.count_and_get
1659 ( p_count => x_msg_count,
1660 p_data => x_msg_data );
1661 WHEN OTHERS THEN
1662 x_return_status := fnd_api.g_ret_sts_unexp_error;
1663 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1664 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1665 END IF;
1666 fnd_msg_pub.count_and_get
1667 ( p_count => x_msg_count,
1668 p_data => x_msg_data );
1669
1670 END Get_InteractionActivityCount;
1671
1672 PROCEDURE Get_InteractionCount
1673 (
1674 p_api_version IN NUMBER,
1675 p_init_msg_list IN VARCHAR2, --DEFAULT FND_API.G_FALSE,
1676 p_resp_appl_id IN NUMBER DEFAULT NULL,
1677 p_resp_id IN NUMBER DEFAULT NULL,
1678 p_user_id IN NUMBER,
1679 p_login_id IN NUMBER DEFAULT NULL,
1680 x_return_status OUT VARCHAR2,
1681 x_msg_count OUT NUMBER,
1682 x_msg_data OUT VARCHAR2,
1683 p_outcome_id IN NUMBER,
1684 p_result_id IN NUMBER,
1685 p_reason_id IN NUMBER,
1686 p_attribute1 IN VARCHAR2 DEFAULT NULL,
1687 p_attribute2 IN VARCHAR2 DEFAULT NULL,
1688 p_attribute3 IN VARCHAR2 DEFAULT NULL,
1689 p_attribute4 IN VARCHAR2 DEFAULT NULL,
1690 p_attribute5 IN VARCHAR2 DEFAULT NULL,
1691 p_attribute6 IN VARCHAR2 DEFAULT NULL,
1692 p_attribute7 IN VARCHAR2 DEFAULT NULL,
1693 p_attribute8 IN VARCHAR2 DEFAULT NULL,
1694 p_attribute9 IN VARCHAR2 DEFAULT NULL,
1695 p_attribute10 IN VARCHAR2 DEFAULT NULL,
1696 p_attribute11 IN VARCHAR2 DEFAULT NULL,
1697 p_attribute12 IN VARCHAR2 DEFAULT NULL,
1698 p_attribute13 IN VARCHAR2 DEFAULT NULL,
1699 p_attribute14 IN VARCHAR2 DEFAULT NULL,
1700 p_attribute15 IN VARCHAR2 DEFAULT NULL,
1701 p_attribute_category IN VARCHAR2 DEFAULT NULL,
1702 x_interaction_count OUT NUMBER
1703 ) AS
1704 l_api_name CONSTANT VARCHAR2(30) := 'Get_InteractionCount';
1705 l_api_version CONSTANT NUMBER := 1.1;
1706 l_api_name_full CONSTANT VARCHAR2(61) := g_pkg_name||'.'||l_api_name;
1707 l_return_status VARCHAR2(1);
1708
1709 interactionCount NUMBER;
1710 BEGIN
1711
1712 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version,
1713 l_api_name, g_pkg_name) THEN
1714 RAISE fnd_api.g_exc_unexpected_error;
1715 END IF;
1716
1717 -- Initialize message list if p_init_msg_list is set to TRUE
1718 IF fnd_api.to_boolean(p_init_msg_list) THEN
1719 fnd_msg_pub.initialize;
1720 END IF;
1721
1722 -- Initialize API return status to success
1723 x_return_status := fnd_api.g_ret_sts_success;
1724
1725 --
1726 -- Apply business-rule validation to all required and passed parameters
1727 --
1728 -- Validate user and login session IDs
1729 --
1730 IF (p_user_id IS NULL) THEN
1731 jtf_ih_core_util_pvt.add_null_parameter_msg(l_api_name_full, 'p_user_id');
1732 RAISE fnd_api.g_exc_error;
1733 ELSE
1734 jtf_ih_core_util_pvt.validate_who_info
1735 ( p_api_name => l_api_name_full,
1736 p_parameter_name_usr => 'p_user_id',
1737 p_parameter_name_log => 'p_login_id',
1738 p_user_id => p_user_id,
1739 p_login_id => p_login_id,
1740 x_return_status => l_return_status );
1741 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
1742 RAISE fnd_api.g_exc_error;
1743 END IF;
1744 END IF;
1745
1746 SELECT count(*) into interactionCount
1747 FROM jtf_ih_Interactions
1748 where outcome_id = p_outcome_id
1749 and result_id = p_result_id
1750 and reason_id = p_reason_id
1751 and ((attribute1 = p_attribute1) or (p_attribute1 is NULL and attribute1 is NULL))
1752 and ((attribute2 = p_attribute2) or (p_attribute2 is NULL and attribute2 is NULL))
1753 and ((attribute3 = p_attribute3) or (p_attribute3 is NULL and attribute3 is NULL))
1754 and ((attribute4 = p_attribute4) or (p_attribute4 is NULL and attribute4 is NULL))
1755 and ((attribute5 = p_attribute5) or (p_attribute5 is NULL and attribute5 is NULL))
1756 and ((attribute6 = p_attribute6) or (p_attribute6 is NULL and attribute6 is NULL))
1757 and ((attribute7 = p_attribute7) or (p_attribute7 is NULL and attribute7 is NULL))
1758 and ((attribute8 = p_attribute8) or (p_attribute8 is NULL and attribute8 is NULL))
1759 and ((attribute9 = p_attribute9) or (p_attribute9 is NULL and attribute9 is NULL))
1760 and ((attribute10 = p_attribute10) or (p_attribute10 is NULL and attribute10 is NULL))
1761 and ((attribute11 = p_attribute11) or (p_attribute11 is NULL and attribute11 is NULL))
1762 and ((attribute12 = p_attribute12) or (p_attribute12 is NULL and attribute12 is NULL))
1763 and ((attribute13 = p_attribute13) or (p_attribute13 is NULL and attribute13 is NULL))
1764 and ((attribute14 = p_attribute14) or (p_attribute14 is NULL and attribute14 is NULL))
1765 and ((attribute15 = p_attribute15) or (p_attribute15 is NULL and attribute15 is NULL))
1766 and ((p_attribute_category = p_attribute_category) or (p_attribute_category is NULL and p_attribute_category is NULL))
1767 ;
1768 x_interaction_count := interactionCount;
1769 -- Standard call to get message count and if count is 1, get message info
1770 fnd_msg_pub.count_and_get
1771 ( p_count => x_msg_count,
1772 p_data => x_msg_data );
1773 EXCEPTION
1774 WHEN fnd_api.g_exc_error THEN
1775 x_return_status := fnd_api.g_ret_sts_error;
1776 fnd_msg_pub.count_and_get
1777 ( p_count => x_msg_count,
1778 p_data => x_msg_data );
1779 WHEN fnd_api.g_exc_unexpected_error THEN
1780 x_return_status := fnd_api.g_ret_sts_unexp_error;
1781 fnd_msg_pub.count_and_get
1782 ( p_count => x_msg_count,
1783 p_data => x_msg_data );
1784 WHEN OTHERS THEN
1785 x_return_status := fnd_api.g_ret_sts_unexp_error;
1786 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1787 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1788 END IF;
1789 fnd_msg_pub.count_and_get
1790 ( p_count => x_msg_count,
1791 p_data => x_msg_data );
1792
1793 END Get_InteractionCount;
1794
1795 PROCEDURE Open_Interaction -- created by Jean Zhu 01/11/2000
1796 (
1797 p_api_version IN NUMBER,
1798 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1799 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1800 p_resp_appl_id IN NUMBER DEFAULT NULL,
1801 p_resp_id IN NUMBER DEFAULT NULL,
1802 p_user_id IN NUMBER,
1803 p_login_id IN NUMBER DEFAULT NULL,
1804 x_return_status OUT VARCHAR2,
1805 x_msg_count OUT NUMBER,
1806 x_msg_data OUT VARCHAR2,
1807 p_interaction_rec IN INTERACTION_REC_TYPE,
1808 x_interaction_id OUT NUMBER
1809 )
1810 AS
1811 l_api_name CONSTANT VARCHAR2(30) := 'Open_Interaction';
1812 l_api_version CONSTANT NUMBER := 1.0;
1813 l_api_name_full CONSTANT VARCHAR2(61) := g_pkg_name||'.'||l_api_name;
1814 l_return_status VARCHAR2(1);
1815 l_interaction_id NUMBER;
1816 l_start_date_time DATE;
1817 l_active VARCHAR2(1) := 'Y';
1818 l_duration NUMBER := NULL;
1819 l_productive_time_amount NUMBER := NULL;
1820 BEGIN
1821 -- Standard start of API savepoint
1822 SAVEPOINT open_interaction_pub;
1823
1824 -- Standard call to check for call compatibility
1825 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version,
1826 l_api_name, g_pkg_name) THEN
1827 RAISE fnd_api.g_exc_unexpected_error;
1828 END IF;
1829 --DBMS_OUTPUT.PUT_LINE('PAST fnd_api.compatible_api_call in JTF_IH_PUB_PS.Open_Interaction');
1830
1831 -- Initialize message list if p_init_msg_list is set to TRUE
1832 IF fnd_api.to_boolean(p_init_msg_list) THEN
1833 fnd_msg_pub.initialize;
1834 END IF;
1835
1836 -- Initialize API return status to success
1837 x_return_status := fnd_api.g_ret_sts_success;
1838
1839 --
1840 -- Apply business-rule validation to all required and passed parameters
1841 --
1842 -- Validate user and login session IDs
1843 --
1844 IF (p_user_id IS NULL) THEN
1845 jtf_ih_core_util_pvt.add_null_parameter_msg(l_api_name_full, 'p_user_id');
1846 RAISE fnd_api.g_exc_error;
1847 ELSE
1848 jtf_ih_core_util_pvt.validate_who_info
1849 ( p_api_name => l_api_name_full,
1850 p_parameter_name_usr => 'p_user_id',
1851 p_parameter_name_log => 'p_login_id',
1852 p_user_id => p_user_id,
1853 p_login_id => p_login_id,
1854 x_return_status => l_return_status );
1855 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
1856 RAISE fnd_api.g_exc_error;
1857 END IF;
1858 END IF;
1859 --DBMS_OUTPUT.PUT_LINE('PAST jtf_ih_core_util_pvt.validate_who_info in JTF_IH_PUB_PS.Open_Interaction');
1860
1861 --
1862 -- Validate all non-missing attributes by calling the utility procedure.
1863 --
1864 Validate_Interaction_Record
1865 ( p_api_name => l_api_name_full,
1866 p_int_val_rec => p_interaction_rec,
1867 p_resp_appl_id => p_resp_appl_id,
1868 p_resp_id => p_resp_id,
1869 x_return_status => l_return_status
1870 );
1871 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
1872 RAISE fnd_api.g_exc_error;
1873 END IF;
1874 --DBMS_OUTPUT.PUT_LINE('PAST Validate_Interaction_Record in JTF_IH_PUB_PS.Open_Interaction');
1875
1876 -- assign the start_date_time
1877 IF(p_interaction_rec.start_date_time IS NOT NULL) THEN
1878 l_start_date_time := p_interaction_rec.start_date_time;
1879 ELSE
1880 l_start_date_time := SYSDATE;
1881 END IF;
1882
1883 -- assign the duration
1884 IF(p_interaction_rec.duration IS NOT NULL) THEN
1885 l_duration := p_interaction_rec.duration;
1886 ELSIF(p_interaction_rec.end_date_time IS NOT NULL) THEN
1887 --
1888 -- Validate start_date_time and end_date_time by calling the utility procedure.
1889 --
1890 Validate_StartEnd_Date
1891 ( p_api_name => l_api_name_full,
1892 p_start_date_time => l_start_date_time,
1893 p_end_date_time => p_interaction_rec.end_date_time,
1894 x_return_status => l_return_status
1895 );
1896 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
1897 RAISE fnd_api.g_exc_error;
1898 END IF;
1899 --DBMS_OUTPUT.PUT_LINE('PAST Validate_StartEnd_Date in JTF_IH_PUB_PS.Open_Interaction');
1900 l_duration := ROUND((p_interaction_rec.end_date_time - l_start_date_time)*24*60);
1901 END IF;
1902
1903 -- assign the productive_time_amount
1904 IF(p_interaction_rec.productive_time_amount IS NOT NULL) THEN
1905 l_productive_time_amount := p_interaction_rec.productive_time_amount;
1906 ELSIF(l_duration IS NOT NULL) THEN
1907 IF(p_interaction_rec.non_productive_time_amount IS NOT NULL) THEN
1908 l_productive_time_amount := l_duration - p_interaction_rec.non_productive_time_amount;
1909 IF(l_productive_time_amount < 0) THEN
1910 x_return_status := fnd_api.g_ret_sts_error;
1911 jtf_ih_core_util_pvt.add_invalid_argument_msg(l_api_name_full,
1912 to_char(p_interaction_rec.non_productive_time_amount),'non_productive_time_amount');
1913 RETURN;
1914 END IF;
1915 ELSE
1916 l_productive_time_amount := l_duration;
1917 END IF;
1918 END IF;
1919
1920 SELECT JTF_IH_INTERACTIONS_S1.NextVal into l_interaction_id FROM dual;
1921
1922 INSERT INTO jtf_ih_interactions
1923 (
1924 CREATED_BY,
1925 REFERENCE_FORM,
1926 CREATION_DATE,
1927 LAST_UPDATED_BY,
1928 DURATION,
1929 LAST_UPDATE_DATE,
1930 LAST_UPDATE_LOGIN,
1931 END_DATE_TIME,
1932 FOLLOW_UP_ACTION,
1933 NON_PRODUCTIVE_TIME_AMOUNT,
1934 RESULT_ID,
1935 REASON_ID,
1936 START_DATE_TIME,
1937 OUTCOME_ID,
1938 PREVIEW_TIME_AMOUNT,
1939 PRODUCTIVE_TIME_AMOUNT,
1940 HANDLER_ID,
1941 INTER_INTERACTION_DURATION,
1942 INTERACTION_ID,
1943 WRAP_UP_TIME_AMOUNT,
1944 SCRIPT_ID,
1945 PARTY_ID,
1946 RESOURCE_ID,
1947 OBJECT_ID,
1948 OBJECT_TYPE,
1949 SOURCE_CODE_ID,
1950 SOURCE_CODE,
1951 ATTRIBUTE1,
1952 ATTRIBUTE2,
1953 ATTRIBUTE3,
1954 ATTRIBUTE4,
1955 ATTRIBUTE5,
1956 ATTRIBUTE6,
1957 ATTRIBUTE7,
1958 ATTRIBUTE8,
1959 ATTRIBUTE9,
1960 ATTRIBUTE10,
1961 ATTRIBUTE11,
1962 ATTRIBUTE12,
1963 ATTRIBUTE13,
1964 ATTRIBUTE14,
1965 ATTRIBUTE15,
1966 ATTRIBUTE_CATEGORY,
1967 ACTIVE
1968 )
1969 VALUES
1970 (
1971 p_user_id,
1972 p_interaction_rec.reference_form,
1973 Sysdate,
1974 p_user_id,
1975 l_duration,
1976 Sysdate,
1977 p_login_id,
1978 p_interaction_rec.end_date_time,
1979 p_interaction_rec.follow_up_action,
1980 p_interaction_rec.non_productive_time_amount,
1981 p_interaction_rec.result_id,
1982 p_interaction_rec.reason_id,
1983 l_start_date_time,
1984 p_interaction_rec.outcome_id,
1985 p_interaction_rec.preview_time_amount,
1986 l_productive_time_amount,
1987 p_interaction_rec.handler_id,
1988 p_interaction_rec.inter_interaction_duration,
1989 l_interaction_id,
1990 p_interaction_rec.wrapup_time_amount,
1991 p_interaction_rec.script_id,
1992 p_interaction_rec.party_id,
1993 p_interaction_rec.resource_id,
1994 p_interaction_rec.object_id,
1995 p_interaction_rec.object_type,
1996 p_interaction_rec.source_code_id,
1997 p_interaction_rec.source_code,
1998 p_interaction_rec.attribute1,
1999 p_interaction_rec.attribute2,
2000 p_interaction_rec.attribute3,
2001 p_interaction_rec.attribute4,
2002 p_interaction_rec.attribute5,
2003 p_interaction_rec.attribute6,
2004 p_interaction_rec.attribute7,
2005 p_interaction_rec.attribute8,
2006 p_interaction_rec.attribute9,
2007 p_interaction_rec.attribute10,
2008 p_interaction_rec.attribute11,
2009 p_interaction_rec.attribute12,
2010 p_interaction_rec.attribute13,
2011 p_interaction_rec.attribute14,
2012 p_interaction_rec.attribute15,
2013 p_interaction_rec.attribute_category,
2014 l_active
2015 );
2016 --DBMS_OUTPUT.PUT_LINE('PAST INSERT INTO jtf_ih_Interactions in JTF_IH_PUB_PS.Open_Interaction');
2017
2018
2019 IF ((p_interaction_rec.parent_id IS NOT NULL) AND (p_interaction_rec.parent_id <> fnd_api.g_miss_num)) THEN
2020 INSERT INTO jtf_ih_interaction_inters
2021 (
2022 INTERACT_INTERACTION_ID,
2023 INTERACT_INTERACTION_IDRELATES,
2024 CREATED_BY,
2025 CREATION_DATE,
2026 LAST_UPDATED_BY,
2027 LAST_UPDATE_DATE,
2028 LAST_UPDATE_LOGIN
2029 )
2030 VALUES
2031 (
2032 l_interaction_id,
2033 p_interaction_rec.parent_id,
2034 p_user_id,
2035 Sysdate,
2036 p_user_id,
2037 Sysdate,
2038 p_user_id
2039 );
2040 END IF;
2041 --DBMS_OUTPUT.PUT_LINE('PAST INSERT INTO jtf_ih_Interaction_inters in JTF_IH_PUB_PS.Open_Interaction');
2042 --
2043 -- Set OUT value
2044 --
2045 x_interaction_id := l_interaction_id;
2046
2047 -- Standard check of p_commit
2048 IF fnd_api.to_boolean(p_commit) THEN
2049 COMMIT WORK;
2050 END IF;
2051
2052 -- Standard call to get message count and if count is 1, get message info
2053 fnd_msg_pub.count_and_get( p_count => x_msg_count, p_data => x_msg_data );
2054 EXCEPTION
2055 WHEN fnd_api.g_exc_error THEN
2056 ROLLBACK TO open_interaction_pub;
2057 x_return_status := fnd_api.g_ret_sts_error;
2058 fnd_msg_pub.count_and_get
2059 ( p_count => x_msg_count,
2060 p_data => x_msg_data );
2061 WHEN fnd_api.g_exc_unexpected_error THEN
2062 ROLLBACK TO open_interaction_pub;
2063 x_return_status := fnd_api.g_ret_sts_unexp_error;
2064 fnd_msg_pub.count_and_get
2065 ( p_count => x_msg_count,
2066 p_data => x_msg_data );
2067 WHEN OTHERS THEN
2068 ROLLBACK TO open_interaction_pub;
2069 x_return_status := fnd_api.g_ret_sts_unexp_error;
2070 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2071 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
2072 END IF;
2073 fnd_msg_pub.count_and_get
2074 ( p_count => x_msg_count,
2075 p_data => x_msg_data );
2076 END Open_Interaction;
2077
2078
2079 PROCEDURE Update_Interaction -- created by Jean Zhu 01/11/2000
2080 (
2081 p_api_version IN NUMBER,
2082 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2083 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2084 p_resp_appl_id IN NUMBER DEFAULT NULL,
2085 p_resp_id IN NUMBER DEFAULT NULL,
2086 p_user_id IN NUMBER,
2087 p_login_id IN NUMBER DEFAULT NULL,
2088 x_return_status OUT VARCHAR2,
2089 x_msg_count OUT NUMBER,
2090 x_msg_data OUT VARCHAR2,
2091 p_interaction_rec IN interaction_rec_type
2092 )
2093 AS
2094 l_api_name CONSTANT VARCHAR2(30) := 'Update_Interaction';
2095 l_api_version CONSTANT NUMBER := 1.0;
2096 l_api_name_full CONSTANT VARCHAR2(61) := g_pkg_name||'.'||l_api_name;
2097 l_return_status VARCHAR2(1);
2098 l_count NUMBER;
2099 l_start_date_time DATE;
2100 l_active VARCHAR2(1) := NULL;
2101 l_duration NUMBER := NULL;
2102 l_productive_time_amount NUMBER := NULL;
2103 BEGIN
2104 -- Standard start of API savepoint
2105 SAVEPOINT update_interaction_pub;
2106
2107 -- Standard call to check for call compatibility
2108 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version,
2109 l_api_name, g_pkg_name) THEN
2110 RAISE fnd_api.g_exc_unexpected_error;
2111 END IF;
2112 --DBMS_OUTPUT.PUT_LINE('PAST fnd_api.compatible_api_call in JTF_IH_PUB_PS.Update_Interaction');
2113
2114 -- Initialize message list if p_init_msg_list is set to TRUE
2115 IF fnd_api.to_boolean(p_init_msg_list) THEN
2116 fnd_msg_pub.initialize;
2117 END IF;
2118
2119 -- Initialize API return status to success
2120 x_return_status := fnd_api.g_ret_sts_success;
2121
2122 --
2123 -- Apply business-rule validation to all required and passed parameters
2124 --
2125 -- Validate user and login session IDs
2126 --
2127 IF (p_user_id IS NULL) THEN
2128 jtf_ih_core_util_pvt.add_null_parameter_msg(l_api_name_full, 'p_user_id');
2129 RAISE fnd_api.g_exc_error;
2130 ELSE
2131 jtf_ih_core_util_pvt.validate_who_info
2132 ( p_api_name => l_api_name_full,
2133 p_parameter_name_usr => 'p_user_id',
2134 p_parameter_name_log => 'p_login_id',
2135 p_user_id => p_user_id,
2136 p_login_id => p_login_id,
2137 x_return_status => l_return_status );
2138 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
2139 RAISE fnd_api.g_exc_error;
2140 END IF;
2141 END IF;
2142 --DBMS_OUTPUT.PUT_LINE('PAST jtf_ih_core_util_pvt.validate_who_info in JTF_IH_PUB_PS.Update_Interaction');
2143
2144 --
2145 -- Validate all non-missing attributes by calling the utility procedure.
2146 --
2147 Validate_Interaction_Record
2148 ( p_api_name => l_api_name_full,
2149 p_int_val_rec => p_interaction_rec,
2150 p_resp_appl_id => p_resp_appl_id,
2151 p_resp_id => p_resp_id,
2152 x_return_status => l_return_status
2153 );
2154 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
2155 RAISE fnd_api.g_exc_error;
2156 END IF;
2157 --DBMS_OUTPUT.PUT_LINE('PAST Validate_Interaction_Record in JTF_IH_PUB_PS.Update_Interaction');
2158
2159
2160 --
2161 -- Update table JTF_IH_INTERACTIONS
2162 --
2163 IF (p_interaction_rec.interaction_id IS NULL) THEN
2164 jtf_ih_core_util_pvt.add_null_parameter_msg(l_api_name_full, 'interaction_id');
2165 RAISE fnd_api.g_exc_error;
2166 ELSE
2167 l_count := 0;
2168 SELECT count(*) into l_count
2169 FROM jtf_ih_interactions
2170 WHERE interaction_id = p_interaction_rec.interaction_id;
2171 IF(l_count <> 1) THEN
2172 x_return_status := fnd_api.g_ret_sts_error;
2173 jtf_ih_core_util_pvt.add_invalid_argument_msg(l_api_name_full, to_char(p_interaction_rec.interaction_id),
2174 'interaction_id');
2175 RETURN;
2176 ELSE
2177 SELECT active into l_active
2178 FROM jtf_ih_interactions
2179 WHERE interaction_id = p_interaction_rec.interaction_id;
2180 IF(l_active <> 'N') THEN
2181 SELECT start_date_time into l_start_date_time FROM jtf_ih_interactions
2182 WHERE interaction_id = p_interaction_rec.interaction_id;
2183
2184 -- assign the duration
2185 IF(p_interaction_rec.duration IS NOT NULL) THEN
2186 l_duration := p_interaction_rec.duration;
2187 ELSIF(p_interaction_rec.end_date_time IS NOT NULL) THEN
2188 --
2189 -- Validate start_date_time and end_date_time by calling the utility procedure.
2190 --
2191 Validate_StartEnd_Date
2192 ( p_api_name => l_api_name_full,
2193 p_start_date_time => l_start_date_time,
2194 p_end_date_time => p_interaction_rec.end_date_time,
2195 x_return_status => l_return_status
2196 );
2197 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
2198 RAISE fnd_api.g_exc_error;
2199 END IF;
2200 --DBMS_OUTPUT.PUT_LINE('PAST Validate_StartEnd_Date in JTF_IH_PUB_PS.Open_Interaction');
2201 l_duration := ROUND((p_interaction_rec.end_date_time - l_start_date_time)*24*60);
2202 END IF;
2203
2204 -- assign the productive_time_amount
2205 IF(p_interaction_rec.productive_time_amount IS NOT NULL) THEN
2206 l_productive_time_amount := p_interaction_rec.productive_time_amount;
2207 ELSIF(l_duration IS NOT NULL) THEN
2208 IF(p_interaction_rec.non_productive_time_amount IS NOT NULL) THEN
2209 l_productive_time_amount := l_duration - p_interaction_rec.non_productive_time_amount;
2210 IF(l_productive_time_amount < 0) THEN
2211 x_return_status := fnd_api.g_ret_sts_error;
2212 jtf_ih_core_util_pvt.add_invalid_argument_msg(l_api_name_full,
2213 to_char(p_interaction_rec.non_productive_time_amount),'non_productive_time_amount');
2214 RETURN;
2215 END IF;
2216 ELSE
2217 l_productive_time_amount := l_duration;
2218 END IF;
2219 END IF;
2220
2221 IF(p_interaction_rec.reference_form <> fnd_api.g_miss_char) THEN
2222 UPDATE jtf_ih_interactions SET REFERENCE_FORM = p_interaction_rec.reference_form
2223 WHERE interaction_id = p_interaction_rec.interaction_id;
2224 END IF;
2225 IF(p_interaction_rec.follow_up_action <> fnd_api.g_miss_char) THEN
2226 UPDATE jtf_ih_interactions SET FOLLOW_UP_ACTION = p_interaction_rec.follow_up_action
2227 WHERE interaction_id = p_interaction_rec.interaction_id;
2228 END IF;
2229 IF(l_duration IS NOT NULL) THEN
2230 UPDATE jtf_ih_interactions SET DURATION = l_duration
2231 WHERE interaction_id = p_interaction_rec.interaction_id;
2232 END IF;
2233 IF(p_interaction_rec.end_date_time <> fnd_api.g_miss_date) THEN
2234 UPDATE jtf_ih_interactions SET END_DATE_TIME = p_interaction_rec.end_date_time
2235 WHERE interaction_id = p_interaction_rec.interaction_id;
2236 END IF;
2237 IF(p_interaction_rec.inter_interaction_duration <> fnd_api.g_miss_num) THEN
2238 UPDATE jtf_ih_interactions SET INTER_INTERACTION_DURATION = p_interaction_rec.inter_interaction_duration
2239 WHERE interaction_id = p_interaction_rec.interaction_id;
2240 END IF;
2241 IF(p_interaction_rec.non_productive_time_amount <> fnd_api.g_miss_num) THEN
2242 UPDATE jtf_ih_interactions SET NON_PRODUCTIVE_TIME_AMOUNT = p_interaction_rec.non_productive_time_amount
2243 WHERE interaction_id = p_interaction_rec.interaction_id;
2244 END IF;
2245 IF(p_interaction_rec.preview_time_amount <> fnd_api.g_miss_num) THEN
2246 UPDATE jtf_ih_interactions SET PREVIEW_TIME_AMOUNT = p_interaction_rec.preview_time_amount
2247 WHERE interaction_id = p_interaction_rec.interaction_id;
2248 END IF;
2249 IF(l_productive_time_amount IS NOT NULL) THEN
2250 UPDATE jtf_ih_interactions SET PRODUCTIVE_TIME_AMOUNT = l_productive_time_amount
2251 WHERE interaction_id = p_interaction_rec.interaction_id;
2252 END IF;
2253 IF(p_interaction_rec.wrapUp_time_amount <> fnd_api.g_miss_num) THEN
2254 UPDATE jtf_ih_interactions SET WRAP_UP_TIME_AMOUNT = p_interaction_rec.wrapUp_time_amount
2255 WHERE interaction_id = p_interaction_rec.interaction_id;
2256 END IF;
2257 IF(p_interaction_rec.handler_id <> fnd_api.g_miss_num) THEN
2258 UPDATE jtf_ih_interactions SET HANDLER_ID = p_interaction_rec.handler_id
2259 WHERE interaction_id = p_interaction_rec.interaction_id;
2260 END IF;
2261 IF(p_interaction_rec.script_id <> fnd_api.g_miss_num) THEN
2262 UPDATE jtf_ih_interactions SET SCRIPT_ID = p_interaction_rec.script_id
2263 WHERE interaction_id = p_interaction_rec.interaction_id;
2264 END IF;
2265 IF(p_interaction_rec.outcome_id <> fnd_api.g_miss_num) THEN
2266 UPDATE jtf_ih_interactions SET OUTCOME_ID = p_interaction_rec.outcome_id
2267 WHERE interaction_id = p_interaction_rec.interaction_id;
2268 END IF;
2269 IF(p_interaction_rec.result_id <> fnd_api.g_miss_num) THEN
2270 UPDATE jtf_ih_interactions SET RESULT_ID = p_interaction_rec.result_id
2271 WHERE interaction_id = p_interaction_rec.interaction_id;
2272 END IF;
2273 IF(p_interaction_rec.reason_id <> fnd_api.g_miss_num) THEN
2274 UPDATE jtf_ih_interactions SET REASON_ID = p_interaction_rec.reason_id
2275 WHERE interaction_id = p_interaction_rec.interaction_id;
2276 END IF;
2277 IF(p_interaction_rec.resource_id <> fnd_api.g_miss_num) THEN
2278 UPDATE jtf_ih_interactions SET RESOURCE_ID = p_interaction_rec.resource_id
2279 WHERE interaction_id = p_interaction_rec.interaction_id;
2280 END IF;
2281 IF(p_interaction_rec.object_id <> fnd_api.g_miss_num) THEN
2282 UPDATE jtf_ih_interactions SET OBJECT_ID = p_interaction_rec.object_id
2283 WHERE interaction_id = p_interaction_rec.interaction_id;
2284 END IF;
2285 IF(p_interaction_rec.object_type <> fnd_api.g_miss_char) THEN
2286 UPDATE jtf_ih_interactions SET OBJECT_TYPE = p_interaction_rec.object_type
2287 WHERE interaction_id = p_interaction_rec.interaction_id;
2288 END IF;
2289 IF(p_interaction_rec.source_code_id <> fnd_api.g_miss_num) THEN
2290 UPDATE jtf_ih_interactions SET SOURCE_CODE_ID = p_interaction_rec.source_code_id
2291 WHERE interaction_id = p_interaction_rec.interaction_id;
2292 END IF;
2293 IF(p_interaction_rec.source_code <> fnd_api.g_miss_char) THEN
2294 UPDATE jtf_ih_interactions SET SOURCE_CODE = p_interaction_rec.source_code
2295 WHERE interaction_id = p_interaction_rec.interaction_id;
2296 END IF;
2297 IF(p_interaction_rec.attribute1 <> fnd_api.g_miss_char) THEN
2298 UPDATE jtf_ih_interactions SET ATTRIBUTE1 = p_interaction_rec.attribute1
2299 WHERE interaction_id = p_interaction_rec.interaction_id;
2300 END IF;
2301 IF(p_interaction_rec.attribute2 <> fnd_api.g_miss_char) THEN
2302 UPDATE jtf_ih_interactions SET ATTRIBUTE2 = p_interaction_rec.attribute2
2303 WHERE interaction_id = p_interaction_rec.interaction_id;
2304 END IF;
2305 IF(p_interaction_rec.attribute3 <> fnd_api.g_miss_char) THEN
2306 UPDATE jtf_ih_interactions SET ATTRIBUTE3 = p_interaction_rec.attribute3
2307 WHERE interaction_id = p_interaction_rec.interaction_id;
2308 END IF;
2309 IF(p_interaction_rec.attribute4 <> fnd_api.g_miss_char) THEN
2310 UPDATE jtf_ih_interactions SET ATTRIBUTE4 = p_interaction_rec.attribute4
2311 WHERE interaction_id = p_interaction_rec.interaction_id;
2312 END IF;
2313 IF(p_interaction_rec.attribute5 <> fnd_api.g_miss_char) THEN
2314 UPDATE jtf_ih_interactions SET ATTRIBUTE5 = p_interaction_rec.attribute5
2315 WHERE interaction_id = p_interaction_rec.interaction_id;
2316 END IF;
2317 IF(p_interaction_rec.attribute6 <> fnd_api.g_miss_char) THEN
2318 UPDATE jtf_ih_interactions SET ATTRIBUTE6 = p_interaction_rec.attribute6
2319 WHERE interaction_id = p_interaction_rec.interaction_id;
2320 END IF;
2321 IF(p_interaction_rec.attribute7 <> fnd_api.g_miss_char) THEN
2322 UPDATE jtf_ih_interactions SET ATTRIBUTE7 = p_interaction_rec.attribute7
2323 WHERE interaction_id = p_interaction_rec.interaction_id;
2324 END IF;
2325 IF(p_interaction_rec.attribute8 <> fnd_api.g_miss_char) THEN
2326 UPDATE jtf_ih_interactions SET ATTRIBUTE8 = p_interaction_rec.attribute8
2327 WHERE interaction_id = p_interaction_rec.interaction_id;
2328 END IF;
2329 IF(p_interaction_rec.attribute9 <> fnd_api.g_miss_char) THEN
2330 UPDATE jtf_ih_interactions SET ATTRIBUTE9 = p_interaction_rec.attribute9
2331 WHERE interaction_id = p_interaction_rec.interaction_id;
2332 END IF;
2333 IF(p_interaction_rec.attribute10 <> fnd_api.g_miss_char) THEN
2334 UPDATE jtf_ih_interactions SET ATTRIBUTE10 = p_interaction_rec.attribute10
2335 WHERE interaction_id = p_interaction_rec.interaction_id;
2336 END IF;
2337 IF(p_interaction_rec.attribute11 <> fnd_api.g_miss_char) THEN
2338 UPDATE jtf_ih_interactions SET ATTRIBUTE11 = p_interaction_rec.attribute11
2339 WHERE interaction_id = p_interaction_rec.interaction_id;
2340 END IF;
2341 IF(p_interaction_rec.attribute12 <> fnd_api.g_miss_char) THEN
2342 UPDATE jtf_ih_interactions SET ATTRIBUTE12 = p_interaction_rec.attribute12
2343 WHERE interaction_id = p_interaction_rec.interaction_id;
2344 END IF;
2345 IF(p_interaction_rec.attribute13 <> fnd_api.g_miss_char) THEN
2346 UPDATE jtf_ih_interactions SET ATTRIBUTE13 = p_interaction_rec.attribute13
2347 WHERE interaction_id = p_interaction_rec.interaction_id;
2348 END IF;
2349 IF(p_interaction_rec.attribute14 <> fnd_api.g_miss_char) THEN
2350 UPDATE jtf_ih_interactions SET ATTRIBUTE14 = p_interaction_rec.attribute14
2351 WHERE interaction_id = p_interaction_rec.interaction_id;
2352 END IF;
2353 IF(p_interaction_rec.attribute15 <> fnd_api.g_miss_char) THEN
2354 UPDATE jtf_ih_interactions SET ATTRIBUTE15 = p_interaction_rec.attribute15
2355 WHERE interaction_id = p_interaction_rec.interaction_id;
2356 END IF;
2357 IF(p_interaction_rec.attribute_category <> fnd_api.g_miss_char) THEN
2358 UPDATE jtf_ih_interactions SET ATTRIBUTE_CATEGORY = p_interaction_rec.attribute_category
2359 WHERE interaction_id = p_interaction_rec.interaction_id;
2360 END IF;
2361 END IF;
2362 END IF;
2363 END IF;
2364 --DBMS_OUTPUT.PUT_LINE('PAST update table jtf_ih_interactions in JTF_IH_PUB_PS.Update_Interaction');
2365
2366 IF ((p_interaction_rec.parent_id IS NOT NULL) AND (p_interaction_rec.parent_id <> fnd_api.g_miss_num)) THEN
2367 l_count := 0;
2368 SELECT count(*) into l_count
2369 FROM jtf_ih_interaction_inters
2370 WHERE interact_interaction_id = p_interaction_rec.interaction_id and
2371 interact_interaction_idrelates = p_interaction_rec.parent_id;
2372 IF (l_count <= 0) THEN
2373 INSERT INTO jtf_ih_interaction_inters
2374 (
2375 INTERACT_INTERACTION_ID,
2376 INTERACT_INTERACTION_IDRELATES,
2377 CREATED_BY,
2378 CREATION_DATE,
2379 LAST_UPDATED_BY,
2380 LAST_UPDATE_DATE,
2381 LAST_UPDATE_LOGIN
2382 )
2383 VALUES
2384 (
2385 p_interaction_rec.interaction_id,
2386 p_interaction_rec.parent_id,
2387 p_user_id,
2388 Sysdate,
2389 p_user_id,
2390 Sysdate,
2391 p_user_id
2392 );
2393 END IF;
2394 END IF;
2395 --DBMS_OUTPUT.PUT_LINE('PAST INSERT INTO jtf_ih_Interaction_inters in JTF_IH_PUB_PS.Update_Interaction');
2396
2397 -- Standard check of p_commit
2398 IF fnd_api.to_boolean(p_commit) THEN
2399 COMMIT WORK;
2400 END IF;
2401
2402 -- Standard call to get message count and if count is 1, get message info
2403 fnd_msg_pub.count_and_get( p_count => x_msg_count, p_data => x_msg_data );
2404
2405 EXCEPTION
2406 WHEN fnd_api.g_exc_error THEN
2407 ROLLBACK TO update_interaction_pub;
2408 x_return_status := fnd_api.g_ret_sts_error;
2409 fnd_msg_pub.count_and_get
2410 ( p_count => x_msg_count,
2411 p_data => x_msg_data );
2412 WHEN fnd_api.g_exc_unexpected_error THEN
2413 ROLLBACK TO update_interaction_pub;
2414 x_return_status := fnd_api.g_ret_sts_unexp_error;
2415 fnd_msg_pub.count_and_get
2416 ( p_count => x_msg_count,
2417 p_data => x_msg_data );
2418 WHEN OTHERS THEN
2419 ROLLBACK TO update_interaction_pub;
2420 x_return_status := fnd_api.g_ret_sts_unexp_error;
2421 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2422 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
2423 END IF;
2424 fnd_msg_pub.count_and_get
2425 ( p_count => x_msg_count,
2426 p_data => x_msg_data );
2427 END Update_Interaction;
2428
2429 PROCEDURE Close_Interaction -- created by Jean Zhu 01/11/2000
2430 (
2431 p_api_version IN NUMBER,
2432 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2433 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2434 p_resp_appl_id IN NUMBER DEFAULT NULL,
2435 p_resp_id IN NUMBER DEFAULT NULL,
2436 p_user_id IN NUMBER,
2437 p_login_id IN NUMBER DEFAULT NULL,
2438 x_return_status OUT VARCHAR2,
2439 x_msg_count OUT NUMBER,
2440 x_msg_data OUT VARCHAR2,
2441 p_interaction_rec IN interaction_rec_type
2442 )
2443 AS
2444 l_api_name CONSTANT VARCHAR2(30) := 'Close_Interaction';
2445 l_api_version CONSTANT NUMBER := 1.0;
2446 l_api_name_full CONSTANT VARCHAR2(61) := g_pkg_name||'.'||l_api_name;
2447 l_return_status VARCHAR2(1);
2448 l_outcome_id NUMBER := NULL;
2449 l_end_date_time DATE := NULL;
2450 l_action_item_id NUMBER := NULL;
2451 CURSOR l_activity_id_c IS
2452 SELECT activity_id FROM jtf_ih_activities
2453 WHERE interaction_id = p_interaction_rec.interaction_id;
2454 BEGIN
2455 SAVEPOINT close_interaction_pub1;
2456
2457 -- Standard call to check for call compatibility
2458 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version,
2459 l_api_name, g_pkg_name) THEN
2460 RAISE fnd_api.g_exc_unexpected_error;
2461 END IF;
2462 --DBMS_OUTPUT.PUT_LINE('PAST fnd_api.compatible_api_call in JTF_IH_PUB_PS.Close_Interaction');
2463
2464 -- Initialize message list if p_init_msg_list is set to TRUE
2465 IF fnd_api.to_boolean(p_init_msg_list) THEN
2466 fnd_msg_pub.initialize;
2467 END IF;
2468
2469 -- Initialize API return status to success
2470 x_return_status := fnd_api.g_ret_sts_success;
2471
2472 --
2473 -- Apply business-rule validation to all required and passed parameters
2474 --
2475 -- Validate user and login session IDs
2476 --
2477 IF (p_user_id IS NULL) THEN
2478 jtf_ih_core_util_pvt.add_null_parameter_msg(l_api_name_full, 'p_user_id');
2479 RAISE fnd_api.g_exc_error;
2480 ELSE
2481 jtf_ih_core_util_pvt.validate_who_info
2482 ( p_api_name => l_api_name_full,
2483 p_parameter_name_usr => 'p_user_id',
2484 p_parameter_name_log => 'p_login_id',
2485 p_user_id => p_user_id,
2486 p_login_id => p_login_id,
2487 x_return_status => l_return_status );
2488 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
2489 RAISE fnd_api.g_exc_error;
2490 END IF;
2491 END IF;
2492 --DBMS_OUTPUT.PUT_LINE('PAST jtf_ih_core_util_pvt.validate_who_info in JTF_IH_PUB_PS.Close_Interaction');
2493
2494 --
2495 -- Update interaction
2496 --
2497 Update_Interaction
2498 ( p_api_version,
2499 p_init_msg_list,
2500 p_commit,
2501 p_resp_appl_id,
2502 p_resp_id,
2503 p_user_id,
2504 p_login_id,
2505 x_return_status,
2506 x_msg_count,
2507 x_msg_data,
2508 p_interaction_rec);
2509 --DBMS_OUTPUT.PUT_LINE('PAST Update_Interaction in JTF_IH_PUB_PS.Close_Interaction');
2510
2511 SELECT outcome_id into l_outcome_id
2512 FROM jtf_ih_interactions
2513 WHERE interaction_id = p_interaction_rec.interaction_id;
2514 IF (l_outcome_id IS NULL) THEN
2515 x_return_status := fnd_api.g_ret_sts_error;
2516 jtf_ih_core_util_pvt.add_invalid_argument_msg(l_api_name_full, to_char(p_interaction_rec.outcome_id),
2517 'outcome_id');
2518 RETURN;
2519 END IF;
2520
2521 SELECT end_date_time into l_end_date_time
2522 FROM jtf_ih_interactions
2523 WHERE interaction_id = p_interaction_rec.interaction_id;
2524 IF(l_end_date_time IS NULL) THEN
2525 l_end_date_time := SYSDATE;
2526 END IF;
2527 --
2528 -- Set active to 'N' for jtf_ih_interactions and related jtf_ih_activities
2529 --
2530 UPDATE jtf_ih_interactions SET ACTIVE = 'N',end_date_time =l_end_date_time
2531 WHERE interaction_id = p_interaction_rec.interaction_id;
2532
2533 FOR v_activity_id_c IN l_activity_id_c LOOP
2534 l_outcome_id := NULL;
2535 SELECT outcome_id into l_outcome_id
2536 FROM jtf_ih_activities
2537 WHERE activity_id = v_activity_id_c.activity_id;
2538 IF (l_outcome_id IS NULL) THEN
2539 x_return_status := fnd_api.g_ret_sts_error;
2540 jtf_ih_core_util_pvt.add_invalid_argument_msg(l_api_name_full, to_char(l_outcome_id),
2541 'outcome_id');
2542 RETURN;
2543 END IF;
2544
2545 l_action_item_id := NULL;
2546 SELECT action_item_id into l_action_item_id
2547 FROM jtf_ih_activities
2548 WHERE activity_id = v_activity_id_c.activity_id;
2549 IF (l_action_item_id IS NULL) THEN
2550 x_return_status := fnd_api.g_ret_sts_error;
2551 jtf_ih_core_util_pvt.add_invalid_argument_msg(l_api_name_full, to_char(l_action_item_id),
2552 'action_item_id');
2553 RETURN;
2554 END IF;
2555
2556 SELECT end_date_time into l_end_date_time
2557 FROM jtf_ih_activities
2558 WHERE activity_id = v_activity_id_c.activity_id;
2559 IF(l_end_date_time IS NULL) THEN
2560 l_end_date_time := SYSDATE;
2561 END IF;
2562 UPDATE jtf_ih_activities SET ACTIVE = 'N',end_date_time = l_end_date_time
2563 WHERE interaction_id = p_interaction_rec.interaction_id;
2564 END LOOP;
2565 --DBMS_OUTPUT.PUT_LINE('PAST Update ACTIVE in JTF_IH_PUB_PS.Close_Interaction');
2566
2567 -- Standard check of p_commit
2568 IF fnd_api.to_boolean(p_commit) THEN
2569 COMMIT WORK;
2570 END IF;
2571
2572 -- Standard call to get message count and if count is 1, get message info
2573 fnd_msg_pub.count_and_get( p_count => x_msg_count, p_data => x_msg_data );
2574 EXCEPTION
2575 WHEN fnd_api.g_exc_error THEN
2576 ROLLBACK TO close_interaction_pub1;
2577 x_return_status := fnd_api.g_ret_sts_error;
2578 fnd_msg_pub.count_and_get
2579 ( p_count => x_msg_count,
2580 p_data => x_msg_data );
2581 WHEN fnd_api.g_exc_unexpected_error THEN
2582 ROLLBACK TO close_interaction_pub1;
2583 x_return_status := fnd_api.g_ret_sts_unexp_error;
2584 fnd_msg_pub.count_and_get
2585 ( p_count => x_msg_count,
2586 p_data => x_msg_data );
2587 WHEN OTHERS THEN
2588 ROLLBACK TO close_interaction_pub1;
2589 x_return_status := fnd_api.g_ret_sts_unexp_error;
2590 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2591 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
2592 END IF;
2593 fnd_msg_pub.count_and_get
2594 ( p_count => x_msg_count,
2595 p_data => x_msg_data );
2596
2597 END Close_Interaction;
2598
2599 PROCEDURE Add_Activity -- created by Jean Zhu 01/11/2000
2600 (
2601 p_api_version IN NUMBER,
2602 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2603 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2604 p_resp_appl_id IN NUMBER DEFAULT NULL,
2605 p_resp_id IN NUMBER DEFAULT NULL,
2606 p_user_id IN NUMBER,
2607 p_login_id IN NUMBER DEFAULT NULL,
2608 x_return_status OUT VARCHAR2,
2609 x_msg_count OUT NUMBER,
2610 x_msg_data OUT VARCHAR2,
2611 p_activity_rec IN activity_rec_type,
2612 x_activity_id OUT NUMBER
2613 )
2614 AS
2615 l_api_name CONSTANT VARCHAR2(30) := 'Add_Activity';
2616 l_api_version CONSTANT NUMBER := 1.0;
2617 l_api_name_full CONSTANT VARCHAR2(61) := g_pkg_name||'.'||l_api_name;
2618 l_return_status VARCHAR2(1);
2619 l_activity_id NUMBER;
2620 l_duration NUMBER := NULL;
2621 l_start_date_time DATE;
2622 l_active VARCHAR2(1) := 'Y';
2623 BEGIN
2624 -- Standard start of API savepoint
2625 SAVEPOINT add_activity_pub;
2626
2627 -- Standard call to check for call compatibility
2628 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version,
2629 l_api_name, g_pkg_name) THEN
2630 RAISE fnd_api.g_exc_unexpected_error;
2631 END IF;
2632 --DBMS_OUTPUT.PUT_LINE('PAST fnd_api.compatible_api_call in JTF_IH_PUB_PS.Add_Activity');
2633
2634 -- Initialize message list if p_init_msg_list is set to TRUE
2635 IF fnd_api.to_boolean(p_init_msg_list) THEN
2636 fnd_msg_pub.initialize;
2637 END IF;
2638
2639 -- Initialize API return status to success
2640 x_return_status := fnd_api.g_ret_sts_success;
2641
2642 --
2643 -- Apply business-rule validation to all required and passed parameters
2644 --
2645 -- Validate user and login session IDs
2646 --
2647 IF (p_user_id IS NULL) THEN
2648 jtf_ih_core_util_pvt.add_null_parameter_msg(l_api_name_full, 'p_user_id');
2649 RAISE fnd_api.g_exc_error;
2650 ELSE
2651 jtf_ih_core_util_pvt.validate_who_info
2652 ( p_api_name => l_api_name_full,
2653 p_parameter_name_usr => 'p_user_id',
2654 p_parameter_name_log => 'p_login_id',
2655 p_user_id => p_user_id,
2656 p_login_id => p_login_id,
2657 x_return_status => l_return_status );
2658 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
2659 RAISE fnd_api.g_exc_error;
2660 END IF;
2661 END IF;
2662 --DBMS_OUTPUT.PUT_LINE('PAST jtf_ih_core_util_pvt.validate_who_info in JTF_IH_PUB_PS.Add_Activity');
2663
2664 --
2665 -- Validate all non-missing attributes by calling the utility procedure.
2666 --
2667 Validate_Activity_Record
2668 ( p_api_name => l_api_name_full,
2669 p_act_val_rec => p_activity_rec,
2670 p_resp_appl_id => p_resp_appl_id,
2671 p_resp_id => p_resp_id,
2672 x_return_status => l_return_status
2673 );
2674 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
2675 RAISE fnd_api.g_exc_error;
2676 END IF;
2677 --DBMS_OUTPUT.PUT_LINE('PAST Validate_Activity_Record in JTF_IH_PUB_PS.Add_Activity');
2678
2679
2680 IF(p_activity_rec.start_date_time IS NOT NULL) THEN
2681 l_start_date_time := p_activity_rec.start_date_time;
2682 ELSE
2683 l_start_date_time := SYSDATE;
2684 END IF;
2685
2686 IF(p_activity_rec.duration IS NOT NULL) THEN
2687 l_duration := p_activity_rec.duration;
2688 ELSIF(p_activity_rec.end_date_time IS NOT NULL) THEN
2689 --
2690 -- Validate start_date_time and end_date_time by calling the utility procedure.
2691 --
2692 Validate_StartEnd_Date
2693 ( p_api_name => l_api_name_full,
2694 p_start_date_time => l_start_date_time,
2695 p_end_date_time => p_activity_rec.end_date_time,
2696 x_return_status => l_return_status
2697 );
2698 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
2699 RAISE fnd_api.g_exc_error;
2700 END IF;
2701 --DBMS_OUTPUT.PUT_LINE('PAST Validate_StartEnd_Date in JTF_IH_PUB_PS.Add_Activity');
2702 l_duration := ROUND((p_activity_rec.end_date_time - l_start_date_time)*24*60);
2703 END IF;
2704
2705 SELECT JTF_IH_ACTIVITIES_S1.NextVal into l_activity_id FROM dual;
2706
2707 INSERT INTO jtf_ih_Activities
2708 (
2709 ACTIVITY_ID,
2710 OBJECT_ID,
2711 OBJECT_TYPE,
2712 SOURCE_CODE_ID,
2713 SOURCE_CODE,
2714 DURATION,
2715 DESCRIPTION,
2716 DOC_ID,
2717 DOC_REF,
2718 END_DATE_TIME,
2719 RESULT_ID,
2720 REASON_ID,
2721 START_DATE_TIME,
2722 ACTION_ID,
2723 INTERACTION_ACTION_TYPE,
2724 MEDIA_ID,
2725 OUTCOME_ID,
2726 ACTION_ITEM_ID,
2727 INTERACTION_ID,
2728 TASK_ID,
2729 CREATION_DATE,
2730 CREATED_BY,
2731 LAST_UPDATED_BY,
2732 LAST_UPDATE_DATE,
2733 LAST_UPDATE_LOGIN,
2734 ACTIVE
2735 )
2736 VALUES
2737 (
2738 l_activity_id,
2739 p_activity_rec.object_id,
2740 p_activity_rec.object_type,
2741 p_activity_rec.source_code_id,
2742 p_activity_rec.source_code,
2743 l_duration,
2744 p_activity_rec.description,
2745 p_activity_rec.doc_id,
2746 p_activity_rec.doc_ref,
2747 p_activity_rec.end_date_time,
2748 p_activity_rec.result_id,
2749 p_activity_rec.reason_id,
2750 l_start_date_time,
2751 p_activity_rec.action_id,
2752 p_activity_rec.interaction_action_type,
2753 p_activity_rec.media_id,
2754 p_activity_rec.outcome_id,
2755 p_activity_rec.action_item_id,
2756 p_activity_rec.interaction_id,
2757 p_activity_rec.task_id,
2758 Sysdate,
2759 p_user_id,
2760 p_user_id,
2761 Sysdate,
2762 p_login_id,
2763 l_active
2764 );
2765 --DBMS_OUTPUT.PUT_LINE('PAST INSERT INTO jtf_ih_activities in JTF_IH_PUB_PS.Add_Activity');
2766
2767 --
2768 -- Set OUT value
2769 --
2770 x_activity_id := l_activity_id;
2771
2772 -- Standard check of p_commit
2773 IF fnd_api.to_boolean(p_commit) THEN
2774 COMMIT WORK;
2775 END IF;
2776
2777 -- Standard call to get message count and if count is 1, get message info
2778 fnd_msg_pub.count_and_get( p_count => x_msg_count, p_data => x_msg_data );
2779 EXCEPTION
2780 WHEN fnd_api.g_exc_error THEN
2781 ROLLBACK TO add_activity_pub;
2782 x_return_status := fnd_api.g_ret_sts_error;
2783 fnd_msg_pub.count_and_get
2784 ( p_count => x_msg_count,
2785 p_data => x_msg_data );
2786 WHEN fnd_api.g_exc_unexpected_error THEN
2787 ROLLBACK TO add_activity_pub;
2788 x_return_status := fnd_api.g_ret_sts_unexp_error;
2789 fnd_msg_pub.count_and_get
2790 ( p_count => x_msg_count,
2791 p_data => x_msg_data );
2792 WHEN OTHERS THEN
2793 ROLLBACK TO add_activity_pub;
2794 x_return_status := fnd_api.g_ret_sts_unexp_error;
2795 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2796 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
2797 END IF;
2798 fnd_msg_pub.count_and_get
2799 ( p_count => x_msg_count,
2800 p_data => x_msg_data );
2801 END Add_Activity;
2802
2803 PROCEDURE Update_Activity -- created by Jean Zhu 01/11/2000
2804 (
2805 p_api_version IN NUMBER,
2806 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2807 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2808 p_resp_appl_id IN NUMBER DEFAULT NULL,
2809 p_resp_id IN NUMBER DEFAULT NULL,
2810 p_user_id IN NUMBER,
2811 p_login_id IN NUMBER DEFAULT NULL,
2812 x_return_status OUT VARCHAR2,
2813 x_msg_count OUT NUMBER,
2814 x_msg_data OUT VARCHAR2,
2815 p_activity_rec IN activity_rec_type
2816 )
2817 AS
2818 l_api_name CONSTANT VARCHAR2(30) := 'Update_Activity';
2819 l_api_version CONSTANT NUMBER := 1.0;
2820 l_api_name_full CONSTANT VARCHAR2(61) := g_pkg_name||'.'||l_api_name;
2821 l_return_status VARCHAR2(1);
2822 l_start_date_time DATE;
2823 l_duration NUMBER := NULL;
2824 l_count NUMBER := 0;
2825 l_active VARCHAR2(1) := NULL;
2826 BEGIN
2827 -- Standard start of API savepoint
2828 SAVEPOINT update_activity_pub;
2829
2830 -- Standard call to check for call compatibility
2831 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version,
2832 l_api_name, g_pkg_name) THEN
2833 RAISE fnd_api.g_exc_unexpected_error;
2834 END IF;
2835 --DBMS_OUTPUT.PUT_LINE('PAST fnd_api.compatible_api_call in JTF_IH_PUB_PS.Update_Activity');
2836
2837 -- Initialize message list if p_init_msg_list is set to TRUE
2838 IF fnd_api.to_boolean(p_init_msg_list) THEN
2839 fnd_msg_pub.initialize;
2840 END IF;
2841
2842 -- Initialize API return status to success
2843 x_return_status := fnd_api.g_ret_sts_success;
2844
2845 --
2846 -- Apply business-rule validation to all required and passed parameters
2847 --
2848 -- Validate user and login session IDs
2849 --
2850 IF (p_user_id IS NULL) THEN
2851 jtf_ih_core_util_pvt.add_null_parameter_msg(l_api_name_full, 'p_user_id');
2852 RAISE fnd_api.g_exc_error;
2853 ELSE
2854 jtf_ih_core_util_pvt.validate_who_info
2855 ( p_api_name => l_api_name_full,
2856 p_parameter_name_usr => 'p_user_id',
2857 p_parameter_name_log => 'p_login_id',
2858 p_user_id => p_user_id,
2859 p_login_id => p_login_id,
2860 x_return_status => l_return_status );
2861 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
2862 RAISE fnd_api.g_exc_error;
2863 END IF;
2864 END IF;
2865 --DBMS_OUTPUT.PUT_LINE('PAST jtf_ih_core_util_pvt.validate_who_info in JTF_IH_PUB_PS.Update_Activity');
2866
2867 --
2868 -- Validate all non-missing attributes by calling the utility procedure.
2869 --
2870 Validate_Activity_Record
2871 ( p_api_name => l_api_name_full,
2872 p_act_val_rec => p_activity_rec,
2873 p_resp_appl_id => p_resp_appl_id,
2874 p_resp_id => p_resp_id,
2875 x_return_status => l_return_status
2876 );
2877 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
2878 RAISE fnd_api.g_exc_error;
2879 END IF;
2880 --DBMS_OUTPUT.PUT_LINE('PAST Validate_Activity_Record in JTF_IH_PUB_PS.Update_Activity');
2881
2882 --
2883 -- Update table JTF_IH_INTERACTIONS
2884 --
2885 IF (p_activity_rec.activity_id IS NULL) THEN
2886 jtf_ih_core_util_pvt.add_null_parameter_msg(l_api_name_full, 'activity_id');
2887 RAISE fnd_api.g_exc_error;
2888 ELSE
2889 l_count := 0;
2890 SELECT count(*) into l_count
2891 FROM jtf_ih_activities
2892 WHERE activity_id = p_activity_rec.activity_id;
2893 IF (l_count <> 1) THEN
2894 x_return_status := fnd_api.g_ret_sts_error;
2895 jtf_ih_core_util_pvt.add_invalid_argument_msg(l_api_name_full, to_char(p_activity_rec.activity_id),
2896 'activity_id');
2897 RETURN;
2898 ELSE
2899 SELECT active into l_active FROM jtf_ih_activities
2900 WHERE activity_id = p_activity_rec.activity_id;
2901 IF(l_active <> 'N') THEN
2902 SELECT start_date_time into l_start_date_time FROM jtf_ih_activities
2903 WHERE activity_id = p_activity_rec.activity_id;
2904 IF(p_activity_rec.duration IS NOT NULL) THEN
2905 l_duration := p_activity_rec.duration;
2906 ELSIF(p_activity_rec.end_date_time IS NOT NULL) THEN
2907 --
2908 -- Validate start_date_time and end_date_time by calling the utility procedure.
2909 --
2910 Validate_StartEnd_Date
2911 ( p_api_name => l_api_name_full,
2912 p_start_date_time => l_start_date_time,
2913 p_end_date_time => p_activity_rec.end_date_time,
2914 x_return_status => l_return_status
2915 );
2916 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
2917 RAISE fnd_api.g_exc_error;
2918 END IF;
2919 --DBMS_OUTPUT.PUT_LINE('PAST Validate_StartEnd_Date in JTF_IH_PUB_PS.Update_Activity');
2920 l_duration := ROUND((p_activity_rec.end_date_time - p_activity_rec.start_date_time)*24*60);
2921 END IF;
2922 IF(l_duration IS NOT NULL) THEN
2923 UPDATE jtf_ih_activities SET DURATION = l_duration
2924 WHERE activity_id = p_activity_rec.activity_id;
2925 END IF;
2926 IF(p_activity_rec.end_date_time <> fnd_api.g_miss_date) THEN
2927 UPDATE jtf_ih_activities SET END_DATE_TIME = p_activity_rec.end_date_time
2928 WHERE activity_id = p_activity_rec.activity_id;
2929 END IF;
2930 IF(p_activity_rec.cust_account_id <> fnd_api.g_miss_num) THEN
2931 UPDATE jtf_ih_activities SET CUST_ACCOUNT_ID = p_activity_rec.cust_account_id
2932 WHERE activity_id = p_activity_rec.activity_id;
2933 END IF;
2934 IF(p_activity_rec.cust_org_id <> fnd_api.g_miss_num) THEN
2935 UPDATE jtf_ih_activities SET CUST_ORG_ID = p_activity_rec.cust_org_id
2936 WHERE activity_id = p_activity_rec.activity_id;
2937 END IF;
2938 IF(p_activity_rec.role <> fnd_api.g_miss_char) THEN
2939 UPDATE jtf_ih_activities SET ROLE = p_activity_rec.role
2940 WHERE activity_id = p_activity_rec.activity_id;
2941 END IF;
2942 IF(p_activity_rec.outcome_id <> fnd_api.g_miss_num) THEN
2943 UPDATE jtf_ih_activities SET OUTCOME_ID = p_activity_rec.outcome_id
2944 WHERE activity_id = p_activity_rec.activity_id;
2945 END IF;
2946 IF(p_activity_rec.result_id <> fnd_api.g_miss_num) THEN
2947 UPDATE jtf_ih_activities SET RESULT_ID = p_activity_rec.result_id
2948 WHERE activity_id = p_activity_rec.activity_id;
2949 END IF;
2950 IF(p_activity_rec.reason_id <> fnd_api.g_miss_num) THEN
2951 UPDATE jtf_ih_activities SET REASON_ID = p_activity_rec.reason_id
2952 WHERE activity_id = p_activity_rec.activity_id;
2953 END IF;
2954 IF(p_activity_rec.task_id <> fnd_api.g_miss_num) THEN
2955 UPDATE jtf_ih_activities SET TASK_ID = p_activity_rec.task_id
2956 WHERE activity_id = p_activity_rec.activity_id;
2957 END IF;
2958 IF(p_activity_rec.object_id <> fnd_api.g_miss_num) THEN
2959 UPDATE jtf_ih_activities SET OBJECT_ID = p_activity_rec.object_id
2960 WHERE activity_id = p_activity_rec.activity_id;
2961 END IF;
2962 IF(p_activity_rec.object_type <> fnd_api.g_miss_char) THEN
2963 UPDATE jtf_ih_activities SET OBJECT_TYPE = p_activity_rec.object_type
2964 WHERE activity_id = p_activity_rec.activity_id;
2965 END IF;
2966 IF(p_activity_rec.source_code_id <> fnd_api.g_miss_num) THEN
2967 UPDATE jtf_ih_activities SET SOURCE_CODE_ID = p_activity_rec.source_code_id
2968 WHERE activity_id = p_activity_rec.activity_id;
2969 END IF;
2970 IF(p_activity_rec.source_code <> fnd_api.g_miss_char) THEN
2971 UPDATE jtf_ih_activities SET SOURCE_CODE = p_activity_rec.source_code
2972 WHERE activity_id = p_activity_rec.activity_id;
2973 END IF;
2974 IF(p_activity_rec.doc_id <> fnd_api.g_miss_num) THEN
2975 UPDATE jtf_ih_activities SET DOC_ID = p_activity_rec.doc_id
2976 WHERE activity_id = p_activity_rec.activity_id;
2977 END IF;
2978 IF(p_activity_rec.doc_ref <> fnd_api.g_miss_char) THEN
2979 UPDATE jtf_ih_activities SET DOC_REF = p_activity_rec.doc_ref
2980 WHERE activity_id = p_activity_rec.activity_id;
2981 END IF;
2982 IF(p_activity_rec.media_id <> fnd_api.g_miss_num) THEN
2983 UPDATE jtf_ih_activities SET MEDIA_ID = p_activity_rec.media_id
2984 WHERE activity_id = p_activity_rec.activity_id;
2985 END IF;
2986 IF(p_activity_rec.action_item_id <> fnd_api.g_miss_num) THEN
2987 UPDATE jtf_ih_activities SET ACTION_ITEM_ID = p_activity_rec.action_item_id
2988 WHERE activity_id = p_activity_rec.activity_id;
2989 END IF;
2990 IF(p_activity_rec.interaction_id <> fnd_api.g_miss_num) THEN
2991 UPDATE jtf_ih_activities SET INTERACTION_ID = p_activity_rec.interaction_id
2992 WHERE activity_id = p_activity_rec.activity_id;
2993 END IF;
2994 IF(p_activity_rec.description <> fnd_api.g_miss_char) THEN
2995 UPDATE jtf_ih_activities SET DESCRIPTION = p_activity_rec.description
2996 WHERE activity_id = p_activity_rec.activity_id;
2997 END IF;
2998 IF(p_activity_rec.action_id <> fnd_api.g_miss_num) THEN
2999 UPDATE jtf_ih_activities SET ACTION_ID = p_activity_rec.action_id
3000 WHERE activity_id = p_activity_rec.activity_id;
3001 END IF;
3002 IF(p_activity_rec.interaction_action_type <> fnd_api.g_miss_char) THEN
3003 UPDATE jtf_ih_activities SET INTERACTION_ACTION_TYPE = p_activity_rec.interaction_action_type
3004 WHERE activity_id = p_activity_rec.activity_id;
3005 END IF;
3006 END IF;
3007 END IF;
3008 END IF;
3009 --DBMS_OUTPUT.PUT_LINE('PAST update table jtf_ih_activities in JTF_IH_PUB_PS.Update_Activity');
3010
3011 -- Standard check of p_commit
3012 IF fnd_api.to_boolean(p_commit) THEN
3013 COMMIT WORK;
3014 END IF;
3015
3016 -- Standard call to get message count and if count is 1, get message info
3017 fnd_msg_pub.count_and_get( p_count => x_msg_count, p_data => x_msg_data );
3018 EXCEPTION
3019 WHEN fnd_api.g_exc_error THEN
3020 ROLLBACK TO update_activity_pub;
3021 x_return_status := fnd_api.g_ret_sts_error;
3022 fnd_msg_pub.count_and_get
3023 ( p_count => x_msg_count,
3024 p_data => x_msg_data );
3025 WHEN fnd_api.g_exc_unexpected_error THEN
3026 ROLLBACK TO update_activity_pub;
3027 x_return_status := fnd_api.g_ret_sts_unexp_error;
3028 fnd_msg_pub.count_and_get
3029 ( p_count => x_msg_count,
3030 p_data => x_msg_data );
3031 WHEN OTHERS THEN
3032 ROLLBACK TO update_activity_pub;
3033 x_return_status := fnd_api.g_ret_sts_unexp_error;
3034 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
3035 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
3036 END IF;
3037 fnd_msg_pub.count_and_get
3038 ( p_count => x_msg_count,
3039 p_data => x_msg_data );
3040 END Update_Activity;
3041
3042 PROCEDURE Close_Interaction -- created by Jean Zhu 01/11/2000
3043 (
3044 p_api_version IN NUMBER,
3045 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
3046 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
3047 p_resp_appl_id IN NUMBER DEFAULT NULL,
3048 p_resp_id IN NUMBER DEFAULT NULL,
3049 p_user_id IN NUMBER,
3050 p_login_id IN NUMBER DEFAULT NULL,
3051 x_return_status OUT VARCHAR2,
3052 x_msg_count OUT NUMBER,
3053 x_msg_data OUT VARCHAR2,
3054 p_interaction_id IN NUMBER
3055 )
3056 AS
3057 l_api_name CONSTANT VARCHAR2(30) := 'Close_Interaction';
3058 l_api_version CONSTANT NUMBER := 1.0;
3059 l_api_name_full CONSTANT VARCHAR2(61) := g_pkg_name||'.'||l_api_name;
3060 l_return_status VARCHAR2(1);
3061 l_outcome_id NUMBER := NULL;
3062 l_end_date_time DATE := NULL;
3063 l_action_item_id NUMBER := NULL;
3064 CURSOR l_activity_id_c IS
3065 SELECT activity_id FROM jtf_ih_activities
3066 WHERE interaction_id = p_interaction_id;
3067 BEGIN
3068 SAVEPOINT close_interaction_pub2;
3069
3070 -- Standard call to check for call compatibility
3071 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version,
3072 l_api_name, g_pkg_name) THEN
3073 RAISE fnd_api.g_exc_unexpected_error;
3074 END IF;
3075 --DBMS_OUTPUT.PUT_LINE('PAST fnd_api.compatible_api_call in JTF_IH_PUB_PS.Close_Interaction_2');
3076
3077 -- Initialize message list if p_init_msg_list is set to TRUE
3078 IF fnd_api.to_boolean(p_init_msg_list) THEN
3079 fnd_msg_pub.initialize;
3080 END IF;
3081
3082 -- Initialize API return status to success
3083 x_return_status := fnd_api.g_ret_sts_success;
3084
3085 --
3086 -- Apply business-rule validation to all required and passed parameters
3087 --
3088 -- Validate user and login session IDs
3089 --
3090 IF (p_user_id IS NULL) THEN
3091 jtf_ih_core_util_pvt.add_null_parameter_msg(l_api_name_full, 'p_user_id');
3092 RAISE fnd_api.g_exc_error;
3093 ELSE
3094 jtf_ih_core_util_pvt.validate_who_info
3095 ( p_api_name => l_api_name_full,
3096 p_parameter_name_usr => 'p_user_id',
3097 p_parameter_name_log => 'p_login_id',
3098 p_user_id => p_user_id,
3099 p_login_id => p_login_id,
3100 x_return_status => l_return_status );
3101 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
3102 RAISE fnd_api.g_exc_error;
3103 END IF;
3104 END IF;
3105 --DBMS_OUTPUT.PUT_LINE('PAST jtf_ih_core_util_pvt.validate_who_info in JTF_IH_PUB_PS.Close_Interaction_2');
3106
3107 SELECT outcome_id into l_outcome_id
3108 FROM jtf_ih_interactions
3109 WHERE interaction_id = p_interaction_id;
3110 IF (l_outcome_id IS NULL) THEN
3111 x_return_status := fnd_api.g_ret_sts_error;
3112 jtf_ih_core_util_pvt.add_invalid_argument_msg(l_api_name_full, to_char(l_outcome_id),
3113 'outcome_id');
3114 RETURN;
3115 END IF;
3116
3117 SELECT end_date_time into l_end_date_time
3118 FROM jtf_ih_interactions
3119 WHERE interaction_id = p_interaction_id;
3120 IF(l_end_date_time IS NULL) THEN
3121 l_end_date_time := SYSDATE;
3122 END IF;
3123 --
3124 -- Set active to 'N' for jtf_ih_interactions and related jtf_ih_activities
3125 --
3126 UPDATE jtf_ih_interactions SET ACTIVE = 'N',end_date_time =l_end_date_time
3127 WHERE interaction_id = p_interaction_id;
3128
3129 FOR v_activity_id_c IN l_activity_id_c LOOP
3130 l_outcome_id := NULL;
3131 SELECT outcome_id into l_outcome_id
3132 FROM jtf_ih_activities
3133 WHERE activity_id = v_activity_id_c.activity_id;
3134 IF (l_outcome_id IS NULL) THEN
3135 x_return_status := fnd_api.g_ret_sts_error;
3136 jtf_ih_core_util_pvt.add_invalid_argument_msg(l_api_name_full, to_char(l_outcome_id),
3137 'outcome_id');
3138 RETURN;
3139 END IF;
3140
3141 l_action_item_id := NULL;
3142 SELECT action_item_id into l_action_item_id
3143 FROM jtf_ih_activities
3144 WHERE activity_id = v_activity_id_c.activity_id;
3145 IF (l_action_item_id IS NULL) THEN
3146 x_return_status := fnd_api.g_ret_sts_error;
3147 jtf_ih_core_util_pvt.add_invalid_argument_msg(l_api_name_full, to_char(l_action_item_id),
3148 'action_item_id');
3149 RETURN;
3150 END IF;
3151
3152 SELECT end_date_time into l_end_date_time
3153 FROM jtf_ih_activities
3154 WHERE activity_id = v_activity_id_c.activity_id;
3155 IF(l_end_date_time IS NULL) THEN
3156 l_end_date_time := SYSDATE;
3157 END IF;
3158 UPDATE jtf_ih_activities SET ACTIVE = 'N',end_date_time = l_end_date_time
3159 WHERE interaction_id = p_interaction_id;
3160 END LOOP;
3161 --DBMS_OUTPUT.PUT_LINE('PAST Update ACTIVE in JTF_IH_PUB_PS.Close_Interaction_2');
3162
3163
3164 -- Standard check of p_commit
3165 IF fnd_api.to_boolean(p_commit) THEN
3166 COMMIT WORK;
3167 END IF;
3168
3169 -- Standard call to get message count and if count is 1, get message info
3170 fnd_msg_pub.count_and_get( p_count => x_msg_count, p_data => x_msg_data );
3171 EXCEPTION
3172 WHEN fnd_api.g_exc_error THEN
3173 ROLLBACK TO close_interaction_pub2;
3174 x_return_status := fnd_api.g_ret_sts_error;
3175 fnd_msg_pub.count_and_get
3176 ( p_count => x_msg_count,
3177 p_data => x_msg_data );
3178 WHEN fnd_api.g_exc_unexpected_error THEN
3179 ROLLBACK TO close_interaction_pub2;
3180 x_return_status := fnd_api.g_ret_sts_unexp_error;
3181 fnd_msg_pub.count_and_get
3182 ( p_count => x_msg_count,
3183 p_data => x_msg_data );
3184 WHEN OTHERS THEN
3185 ROLLBACK TO close_interaction_pub2;
3186 x_return_status := fnd_api.g_ret_sts_unexp_error;
3187 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
3188 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
3189 END IF;
3190 fnd_msg_pub.count_and_get
3191 ( p_count => x_msg_count,
3192 p_data => x_msg_data );
3193
3194 END Close_Interaction;
3195
3196
3197 PROCEDURE Update_ActivityDuration -- created by Jean Zhu 01/11/2000
3198 (
3199 p_api_version IN NUMBER,
3200 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
3201 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
3202 p_resp_appl_id IN NUMBER DEFAULT NULL,
3203 p_resp_id IN NUMBER DEFAULT NULL,
3204 p_user_id IN NUMBER,
3205 p_login_id IN NUMBER DEFAULT NULL,
3206 x_return_status OUT VARCHAR2,
3207 x_msg_count OUT NUMBER,
3208 x_msg_data OUT VARCHAR2,
3209 p_activity_id IN NUMBER,
3210 p_end_date_time IN DATE,
3211 p_duration IN NUMBER
3212 )
3213 AS
3214 l_api_name CONSTANT VARCHAR2(30) := 'Update_ActivityDuration';
3215 l_api_version CONSTANT NUMBER := 1.0;
3216 l_api_name_full CONSTANT VARCHAR2(61) := g_pkg_name||'.'||l_api_name;
3217 l_return_status VARCHAR2(1);
3218 l_start_date_time DATE;
3219 BEGIN
3220
3221 -- Standard start of API savepoint
3222 SAVEPOINT update_activityDuration;
3223
3224 -- Standard call to check for call compatibility
3225 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version,
3226 l_api_name, g_pkg_name) THEN
3227 RAISE fnd_api.g_exc_unexpected_error;
3228 END IF;
3229 --DBMS_OUTPUT.PUT_LINE('PAST fnd_api.compatible_api_call in JTF_IH_PUB_PS.Update_ActivityDuration');
3230
3231 -- Initialize message list if p_init_msg_list is set to TRUE
3232 IF fnd_api.to_boolean(p_init_msg_list) THEN
3233 fnd_msg_pub.initialize;
3234 END IF;
3235
3236 -- Initialize API return status to success
3237 x_return_status := fnd_api.g_ret_sts_success;
3238
3239 --
3240 -- Apply business-rule validation to all required and passed parameters
3241 --
3242 -- Validate user and login session IDs
3243 --
3244 IF (p_user_id IS NULL) THEN
3245 jtf_ih_core_util_pvt.add_null_parameter_msg(l_api_name_full, 'p_user_id');
3246 RAISE fnd_api.g_exc_error;
3247 ELSE
3248 jtf_ih_core_util_pvt.validate_who_info
3249 ( p_api_name => l_api_name_full,
3250 p_parameter_name_usr => 'p_user_id',
3251 p_parameter_name_log => 'p_login_id',
3252 p_user_id => p_user_id,
3253 p_login_id => p_login_id,
3254 x_return_status => l_return_status );
3255 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
3256 RAISE fnd_api.g_exc_error;
3257 END IF;
3258 END IF;
3259 --DBMS_OUTPUT.PUT_LINE('PAST jtf_ih_core_util_pvt.validate_who_info in JTF_IH_PUB_PS.Update_ActivityDuration');
3260
3261 --
3262 -- Update table JTF_IH_INTERACTIONS
3263 --
3264 IF (p_activity_id IS NULL) THEN
3265 jtf_ih_core_util_pvt.add_null_parameter_msg(l_api_name_full, 'activity_id');
3266 RAISE fnd_api.g_exc_error;
3267 ELSIF(p_end_date_time IS NULL) THEN RETURN;
3268 ELSE
3269 SELECT start_date_time into l_start_date_time
3270 FROM jtf_ih_activities
3271 WHERE activity_id = p_activity_id;
3272 --
3273 -- Validate start_date_time and end_date_time by calling the utility procedure.
3274 --
3275 Validate_StartEnd_Date
3276 ( p_api_name => l_api_name_full,
3277 p_start_date_time => l_start_date_time,
3278 p_end_date_time => p_end_date_time,
3279 x_return_status => l_return_status
3280 );
3281 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
3282 RAISE fnd_api.g_exc_error;
3283 END IF;
3284 --DBMS_OUTPUT.PUT_LINE('PAST Validate_StartEnd_Date in JTF_IH_PUB_PS.Update_ActivityDuration');
3285
3286 UPDATE jtf_ih_activities SET END_DATE_TIME = p_end_date_time,
3287 DURATION = p_duration WHERE activity_id = p_activity_id;
3288
3289 --DBMS_OUTPUT.PUT_LINE('PAST update end_date_time and duration in JTF_IH_PUB_PS.Update_ActivityDuration');
3290 END IF;
3291
3292 -- Standard check of p_commit
3293 IF fnd_api.to_boolean(p_commit) THEN
3294 COMMIT WORK;
3295 END IF;
3296
3297 -- Standard call to get message count and if count is 1, get message info
3298 fnd_msg_pub.count_and_get( p_count => x_msg_count, p_data => x_msg_data );
3299 EXCEPTION
3300 WHEN fnd_api.g_exc_error THEN
3301 ROLLBACK TO update_activityDuration;
3302 x_return_status := fnd_api.g_ret_sts_error;
3303 fnd_msg_pub.count_and_get
3304 ( p_count => x_msg_count,
3305 p_data => x_msg_data );
3306 WHEN fnd_api.g_exc_unexpected_error THEN
3307 ROLLBACK TO update_activityDuration;
3308 x_return_status := fnd_api.g_ret_sts_unexp_error;
3309 fnd_msg_pub.count_and_get
3310 ( p_count => x_msg_count,
3311 p_data => x_msg_data );
3312 WHEN OTHERS THEN
3313 ROLLBACK TO update_activityDuration;
3314 x_return_status := fnd_api.g_ret_sts_unexp_error;
3315 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
3316 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
3317 END IF;
3318 fnd_msg_pub.count_and_get
3319 ( p_count => x_msg_count,
3320 p_data => x_msg_data );
3321 END Update_ActivityDuration;
3322
3323 PROCEDURE Open_MediaItem
3324 (
3325 p_api_version IN NUMBER,
3326 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
3327 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
3328 p_resp_appl_id IN NUMBER DEFAULT NULL,
3329 p_resp_id IN NUMBER DEFAULT NULL,
3330 p_user_id IN NUMBER,
3331 p_login_id IN NUMBER DEFAULT NULL,
3332 x_return_status OUT VARCHAR2,
3333 x_msg_count OUT NUMBER,
3334 x_msg_data OUT VARCHAR2,
3335 p_media_rec IN media_rec_type,
3336 x_media_id OUT NUMBER
3337 ) AS
3338 BEGIN
3339 NULL;
3340 END Open_MediaItem;
3341
3342 PROCEDURE Update_MediaItem
3343 (
3344 p_api_version IN NUMBER,
3345 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
3346 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
3347 p_resp_appl_id IN NUMBER DEFAULT NULL,
3348 p_resp_id IN NUMBER DEFAULT NULL,
3349 p_user_id IN NUMBER,
3350 p_login_id IN NUMBER DEFAULT NULL,
3351 x_return_status OUT VARCHAR2,
3352 x_msg_count OUT NUMBER,
3353 x_msg_data OUT VARCHAR2,
3354 p_media_rec IN media_rec_type
3355 ) AS
3356 BEGIN
3357 NULL;
3358 END Update_MediaItem;
3359
3360 PROCEDURE Close_MediaItem
3361 (
3362 p_api_version IN NUMBER,
3363 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
3364 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
3365 p_resp_appl_id IN NUMBER DEFAULT NULL,
3366 p_resp_id IN NUMBER DEFAULT NULL,
3367 p_user_id IN NUMBER,
3368 p_login_id IN NUMBER DEFAULT NULL,
3369 x_return_status OUT VARCHAR2,
3370 x_msg_count OUT NUMBER,
3371 x_msg_data OUT VARCHAR2,
3372 p_media_rec IN media_rec_type
3373 ) AS
3374 BEGIN
3375 NULL;
3376 END Close_MediaItem;
3377
3378 PROCEDURE Add_MediaLifecycle
3379 (
3380 p_api_version IN NUMBER,
3381 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
3382 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
3383 p_resp_appl_id IN NUMBER DEFAULT NULL,
3384 p_resp_id IN NUMBER DEFAULT NULL,
3385 p_user_id IN NUMBER,
3386 p_login_id IN NUMBER DEFAULT NULL,
3387 x_return_status OUT VARCHAR2,
3388 x_msg_count OUT NUMBER,
3389 x_msg_data OUT VARCHAR2,
3390 p_media_lc_rec IN media_lc_rec_type
3391 ) AS
3392 BEGIN
3393 NULL;
3394 END Add_MediaLifecycle;
3395
3396 PROCEDURE Update_MediaLifecycle
3397 (
3398 p_api_version IN NUMBER,
3399 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
3400 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
3401 p_resp_appl_id IN NUMBER DEFAULT NULL,
3402 p_resp_id IN NUMBER DEFAULT NULL,
3403 p_user_id IN NUMBER,
3404 p_login_id IN NUMBER DEFAULT NULL,
3405 x_return_status OUT VARCHAR2,
3406 x_msg_count OUT NUMBER,
3407 x_msg_data OUT VARCHAR2,
3408 p_media_lc_rec IN media_lc_rec_type
3409 ) AS
3410
3411 BEGIN
3412 NULL;
3413 END Update_MediaLifecycle;
3414
3415
3416
3417 FUNCTION INIT_INTERACTION_REC RETURN interaction_rec_type
3418 AS
3419
3420 l_interaction_rec_type interaction_rec_type;
3421
3422 BEGIN
3423
3424 return l_interaction_rec_type;
3425
3426 END INIT_INTERACTION_REC;
3427
3428 FUNCTION INIT_ACTIVITY_REC RETURN activity_rec_type
3429 AS
3430
3431 l_activity_rec_type activity_rec_type;
3432
3433 BEGIN
3434
3435 return l_activity_rec_type;
3436
3437 END INIT_ACTIVITY_REC;
3438
3439 END JTF_IH_PUB_PS;