[Home] [Help]
PACKAGE BODY: APPS.IGR_IN_JTF_INTERACTIONS_PKG
Source
1 PACKAGE BODY Igr_in_jtf_interactions_pkg AS
2 /* $Header: IGSRT05B.pls 120.0 2005/06/01 18:13:24 appldev noship $ */
3
4 PROCEDURE get_profile_values IS
5 /* This procedure populates all the profile variables in either parameters
6 or global variables, so that these values can be used again. Default values
7 of some items are also populated here*/
8 l_tmp_org_id NUMBER;
9 l_tmp_user_id VARCHAR2(100);
10 l_tmp_login_id VARCHAR2(100);
11 l_tmp_resource_id VARCHAR2(100);
12
13 CURSOR c_resource_id is
14 SELECT resource_id
15 FROM jtf_rs_resource_extns
16 WHERE user_id = Igr_in_jtf_interactions_pkg.g_user_id;
17
18 l_outcome VARCHAR2(100);
19 l_result VARCHAR2(100);
20 l_reason VARCHAR2(100);
21
22 CURSOR default_outcome is
23 SELECT outcome_id, short_description
24 FROM jtf_ih_outcomes_vl
25 WHERE outcome_code = l_outcome;
26
27 CURSOR default_result is
28 SELECT result_id, short_description
29 FROM jtf_ih_results_vl
30 WHERE result_code = l_result;
31
32 CURSOR default_reason is
33 SELECT reason_id, short_description
34 FROM jtf_ih_reasons_vl
35 WHERE reason_code = l_reason;
36
37 BEGIN
38 -- Get default outcome,result and reason for activities
39 fnd_profile.get('IGR_JTF_DEFAULT_OUTCOME', l_outcome);
40
41 -- Default Outcome for Interactions and Activities in Academic Recruiting
42 fnd_profile.get('IGR_JTF_DEFAULT_RESULT', l_result);
43 -- Default Result for Interactions and Activities in Academic Recruiting
44 fnd_profile.get('IGR_JTF_DEFAULT_REASON', l_reason);
45 -- Default Reason for Interactions and Activities in Academic Recruiting
46
47 fnd_profile.get('USER_ID', l_tmp_user_id );
48 l_tmp_login_id:= FND_GLOBAL.LOGIN_ID;
49
50
51 fnd_profile.get('IGR_JTF_DEFAULT_RESOURCE', l_tmp_resource_id);
52 -- Default resource ID for Interactions and Activities in Academic Recruiting
53 Igr_in_jtf_interactions_pkg.g_resource_id := IGS_GE_NUMBER.TO_NUM(l_tmp_resource_id);
54 Igr_in_jtf_interactions_pkg.g_login_id := IGS_GE_NUMBER.TO_NUM(l_tmp_login_id);
55 Igr_in_jtf_interactions_pkg.g_resp_appl_id := fnd_global.resp_appl_id;
56 Igr_in_jtf_interactions_pkg.g_resp_id := fnd_global.resp_id;
57 Igr_in_jtf_interactions_pkg.g_user_id := fnd_global.user_id;
58
59 OPEN default_outcome;
60 FETCH default_outcome INTO Igr_in_jtf_interactions_pkg.g_def_outcome_id, Igr_in_jtf_interactions_pkg.g_def_outcome;
61 CLOSE default_outcome;
62
63 OPEN default_result;
64 FETCH default_result INTO Igr_in_jtf_interactions_pkg.g_def_result_id, Igr_in_jtf_interactions_pkg.g_def_result;
65 CLOSE default_result;
66
67 OPEN default_reason;
68 FETCH default_reason INTO Igr_in_jtf_interactions_pkg.g_def_reason_id, Igr_in_jtf_interactions_pkg.g_def_reason;
69 CLOSE default_reason;
70
71 END get_profile_values;
72
73 PROCEDURE start_interaction (p_person_id IN igs_pe_person_base_v.person_id%TYPE,
74 p_ret_status OUT NOCOPY VARCHAR2,
75 p_msg_data OUT NOCOPY VARCHAR2,
76 p_msg_count OUT NOCOPY NUMBER,
77 p_int_id OUT NOCOPY NUMBER) IS
78 /* Procedure to start an interaction */
79
80 l_interaction_rec jtf_ih_pub.interaction_rec_type := jtf_ih_pub.init_interaction_rec;
81 x_int_id NUMBER;
82 l_user_id NUMBER;
83 l_msg_count NUMBER;
84 l_msg_data VARCHAR2(2000);
85 l_return_status VARCHAR2(1);
86 l_rec_count NUMBER;
87 l_msg_index_out NUMBER;
88
89 BEGIN
90
91 l_interaction_rec.party_id := p_person_id;
92 -- Populate all the profile variables
93 Igr_in_jtf_interactions_pkg.get_profile_values;
94
95 -- Get the application id and pass that in handler_id
96 l_interaction_rec.handler_id := Igr_in_jtf_interactions_pkg.g_resp_appl_id;
97 l_interaction_rec.resource_id := Igr_in_jtf_interactions_pkg.g_resource_id;
98 l_interaction_rec.outcome_id := Igr_in_jtf_interactions_pkg.g_def_outcome_id;
99 l_interaction_rec.start_date_time := SYSDATE;
100 l_interaction_rec.duration := 0;
101
102 JTF_IH_PUB.OPEN_INTERACTION(p_api_version => Igr_in_jtf_interactions_pkg.g_api_version,
103 p_init_msg_list => Igr_in_jtf_interactions_pkg.g_true,
104 p_commit => Igr_in_jtf_interactions_pkg.g_false,
105 p_resp_appl_id => Igr_in_jtf_interactions_pkg.g_resp_appl_id,
106 p_resp_id => Igr_in_jtf_interactions_pkg.g_resp_id,
107 p_user_id => Igr_in_jtf_interactions_pkg.g_user_id,
108 p_login_id => Igr_in_jtf_interactions_pkg.g_login_id,
109 x_return_status => p_ret_status,
110 x_msg_count => p_msg_count,
111 x_msg_data => p_msg_data,
112 p_interaction_rec => l_interaction_rec,
113 x_interaction_id => p_int_id );
114
115 IF p_ret_status IN ('E','U') THEN
116 IF p_msg_count > 1 THEN
117 FOR i IN 1..p_msg_count LOOP
118 p_msg_data := p_msg_data || ' '||fnd_msg_pub.get(p_encoded => fnd_api.g_false);
119 END LOOP;
120 p_msg_data := trim(p_msg_data);
121 END IF;
122 END IF;
123 EXCEPTION
124 WHEN OTHERS THEN
125 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXCEPTION');
126 IGS_GE_MSG_STACK.ADD;
127 APP_EXCEPTION.RAISE_EXCEPTION;
128
129 END start_interaction;
130
131 PROCEDURE start_int_and_act (
132 p_doc_ref IN VARCHAR2,
133 p_person_id IN igs_pe_person_base_v.person_id%TYPE,
134 p_sales_lead_id IN as_sales_leads.sales_lead_id%TYPE ,
135 p_item_id IN igr_i_a_pkgitm.package_item_id%TYPE ,
136 p_doc_id IN NUMBER,
137 p_action IN jtf_ih_actions_vl.action%TYPE,
138 p_action_id IN jtf_ih_actions_vl.action_id%TYPE,
139 p_action_item IN jtf_ih_action_items_vl.action_item%TYPE,
140 p_action_item_id IN jtf_ih_action_items_vl.action_item_id%TYPE,
141 p_ret_status OUT NOCOPY VARCHAR2,
142 p_msg_data OUT NOCOPY VARCHAR2,
143 p_msg_count OUT NOCOPY NUMBER ) IS
144 /* This procedure starts and interaction and starts an activity */
145
146
147 CURSOR c_person_number(p_person_id igs_pe_person_base_v.person_id%TYPE) IS
148 SELECT person_number
149 FROM igs_pe_person_base_v
150 WHERE person_id = p_person_id;
151
152 person_number_rec c_person_number%ROWTYPE;
153 p_int_id NUMBER;
154
155 BEGIN
156 OPEN c_person_number(p_person_id);
157 FETCH c_person_number into person_number_rec;
158 CLOSE c_person_number;
159 Igr_in_jtf_interactions_pkg.start_interaction(
160 p_person_id => p_person_id,
161 p_ret_status => p_ret_status,
162 p_msg_data => p_msg_data,
163 p_msg_count => p_msg_count,
164 p_int_id => p_int_id);
165 IF p_ret_status IN ('E','U') THEN
166 IF p_msg_count > 1 THEN
167 FOR i IN 1..p_msg_count LOOP
168 p_msg_data := p_msg_data || ' '||fnd_msg_pub.get(p_encoded => fnd_api.g_false);
169 END LOOP;
170 p_msg_data := trim(p_msg_data);
171 END IF;
172 ELSE
173 Igr_in_jtf_interactions_pkg.add_activity(
174 p_action => p_action,
175 p_action_id => p_action_id,
176 p_action_item => p_action_item,
177 p_action_item_id => p_action_item_id,
178 p_doc_source_object_name => IGS_GE_NUMBER.TO_CANN(p_sales_lead_id),
179 p_doc_ref => p_doc_ref,
180 p_doc_id => p_doc_id,
181 p_ret_status => p_ret_status,
182 p_msg_data => p_msg_data,
183 p_msg_count => p_msg_count,
184 p_int_id => p_int_id);
185 IF p_ret_status IN ('E','U') THEN
186 IF p_msg_count > 1 THEN
187 FOR i IN 1..p_msg_count LOOP
188 p_msg_data := p_msg_data || ' '||fnd_msg_pub.get(p_encoded => fnd_api.g_false);
189 END LOOP;
190 p_msg_data := trim(p_msg_data);
191 END IF;
192 ELSE
193 Igr_in_jtf_interactions_pkg.end_interaction(
194 p_ret_status => p_ret_status,
195 p_msg_data => p_msg_data,
196 p_msg_count => p_msg_count);
197
198
199 END IF;
200 END IF;
201
202 END start_int_and_act;
203
204 PROCEDURE add_activity(p_action IN VARCHAR2,
205 p_action_id IN NUMBER,
206 p_Action_item IN VARCHAR2,
207 p_Action_item_id IN NUMBER,
208 p_doc_source_object_name IN VARCHAR2,
209 p_doc_id IN NUMBER,
210 p_doc_ref IN VARCHAR2,
211 p_outcome_id IN NUMBER,
212 p_result_id IN NUMBER,
213 p_reason_id IN NUMBER,
214 p_cust_account_id IN NUMBER,
215 p_int_id IN NUMBER,
216 p_ret_status OUT NOCOPY VARCHAR2,
217 p_msg_data OUT NOCOPY VARCHAR2,
218 p_msg_count OUT NOCOPY NUMBER ) IS
219
220 CURSOR activity_action is
221 SELECT action_id
222 FROM jtf_ih_actions_vl
223 WHERE action = p_action;
224
225 CURSOR activity_action_item is
226 SELECT action_item_id
227 FROM jtf_ih_action_items_vl
228 WHERE action_item = p_action_item;
229
230 l_activity_rec jtf_ih_pub.activity_rec_type := jtf_ih_pub.init_activity_rec;
231 x_activity_id NUMBER;
232 l_msg_count NUMBER;
233 l_msg_data VARCHAR2(2000);
234 l_return_status VARCHAR2(1);
235 l_rec_count NUMBER;
236 l_msg_index_out NUMBER;
237
238 BEGIN
239
240 l_activity_rec.interaction_id := p_int_id;
241 l_activity_rec.outcome_id := Igr_in_jtf_interactions_pkg.g_def_outcome_id;
242 l_activity_rec.result_id := Igr_in_jtf_interactions_pkg.g_def_result_id;
243 l_activity_rec.reason_id := Igr_in_jtf_interactions_pkg.g_def_reason_id;
244 -- Get the action_id and action_item_id from jtf_ih_actions_vl and jtf_ih_Action_items_vl
245 IF p_action_id IS NULL THEN
246 OPEN activity_action;
247 FETCH activity_action into l_activity_rec.action_id;
248 CLOSE activity_action;
249 ELSE
250 l_activity_rec.action_id := p_action_id;
251 END IF;
252
253 IF p_action_item_id IS NULL THEN
254 open activity_action_item;
255 fetch activity_action_item into l_activity_rec.action_item_id;
256 close activity_action_item;
257 ELSE
258 l_activity_rec.action_item_id := p_action_item_id;
259 END IF;
260 l_activity_rec.start_date_time := SYSDATE;
261 l_activity_rec.end_date_time := SYSDATE;
262 l_activity_rec.doc_source_object_name := p_doc_source_object_name;
263 l_activity_rec.doc_id := p_doc_id;
264 IF p_outcome_id IS NOT NULL THEN
265 l_activity_rec.outcome_id := p_outcome_id;
266 END IF;
267 IF p_result_id is NOT NULL THEN
268 l_activity_rec.result_id := p_result_id;
269 END IF;
270 IF p_reason_id IS NOT NULL THEN
271 l_activity_rec.reason_id := p_reason_id;
272 END IF;
273 IF p_cust_account_id IS NOT NULL THEN
274 l_activity_rec.cust_account_id := p_cust_account_id;
275 END IF;
276
277 JTF_IH_PUB.add_activity(p_api_version => Igr_in_jtf_interactions_pkg.g_api_version,
278 p_init_msg_list => Igr_in_jtf_interactions_pkg.g_true,
279 p_commit => Igr_in_jtf_interactions_pkg.g_false,
280 p_resp_appl_id => Igr_in_jtf_interactions_pkg.g_resp_appl_id,
281 p_resp_id => Igr_in_jtf_interactions_pkg.g_resp_id,
282 p_user_id => Igr_in_jtf_interactions_pkg.g_user_id,
283 p_login_id => Igr_in_jtf_interactions_pkg.g_login_id,
284 x_return_status => p_ret_status,
285 x_msg_count => p_msg_count,
286 x_msg_data => p_msg_data,
287 p_activity_rec => l_activity_rec,
288 x_activity_id => x_activity_id);
289
290 IF p_ret_status IN ('E','U') THEN
291 IF p_msg_count > 1 THEN
292 FOR i IN 1..p_msg_count LOOP
293 p_msg_data := p_msg_data || ' '||fnd_msg_pub.get(p_encoded => fnd_api.g_false);
294 END LOOP;
295 p_msg_data := trim(p_msg_data);
296 END IF;
297 END IF;
298 EXCEPTION
299 WHEN OTHERS THEN
300 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXCEPTION');
301 IGS_GE_MSG_STACK.ADD;
302 APP_EXCEPTION.RAISE_EXCEPTION;
303 END add_activity;
304
305 PROCEDURE update_activity(p_activity_id IN NUMBER,
306 p_action_id IN VARCHAR2,
307 p_Action_item_id IN VARCHAR2,
308 p_doc_source_object_name IN VARCHAR2,
309 p_outcome_id IN NUMBER,
310 p_result_id IN NUMBER,
311 p_reason_id IN NUMBER,
312 p_ret_status OUT NOCOPY VARCHAR2,
313 p_msg_data OUT NOCOPY VARCHAR2,
314 p_msg_count OUT NOCOPY NUMBER ) IS
315
316 l_activity_rec jtf_ih_pub.activity_rec_type := jtf_ih_pub.init_activity_rec;
317 l_msg_count NUMBER;
318 l_msg_data VARCHAR2(2000);
319 l_return_status VARCHAR2(1);
320 l_rec_count NUMBER;
321 l_msg_index_out NUMBER;
322
323 BEGIN
324
325 IF Igr_in_jtf_interactions_pkg.g_int_id is null THEN
326 -- Activity cannot be updated since there is no active interaction
327 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXCEPTION');
328 IGS_GE_MSG_STACK.ADD;
329 APP_EXCEPTION.RAISE_EXCEPTION;
330 ELSE
331 l_activity_rec.interaction_id := Igr_in_jtf_interactions_pkg.g_int_id;
332 l_activity_rec.activity_id := p_activity_id;
333 l_activity_rec.action_id := p_action_id;
334 l_activity_rec.action_item_id := p_action_item_id;
335 l_activity_rec.end_date_time := SYSDATE;
336 l_activity_rec.doc_source_object_name := p_doc_source_object_name;
337 l_activity_rec.outcome_id := p_outcome_id;
338 l_activity_rec.result_id := p_result_id;
339 l_activity_rec.reason_id := p_reason_id;
340
341 JTF_IH_PUB.update_activity(p_api_version => Igr_in_jtf_interactions_pkg.g_api_version,
342 p_init_msg_list => Igr_in_jtf_interactions_pkg.g_true,
343 p_commit => Igr_in_jtf_interactions_pkg.g_false,
344 p_resp_appl_id => Igr_in_jtf_interactions_pkg.g_resp_appl_id,
345 p_resp_id => Igr_in_jtf_interactions_pkg.g_resp_id,
346 p_user_id => Igr_in_jtf_interactions_pkg.g_user_id,
347 p_login_id => Igr_in_jtf_interactions_pkg.g_login_id,
348 x_return_status => l_return_status,
349 x_msg_count => l_msg_count,
350 x_msg_data => l_msg_data,
351 p_activity_rec => l_activity_rec);
352
353 END IF;
354
355 EXCEPTION
356 WHEN OTHERS THEN
357 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXCEPTION');
358 IGS_GE_MSG_STACK.ADD;
359 APP_EXCEPTION.RAISE_EXCEPTION;
360 END update_activity;
361
362 PROCEDURE end_interaction (p_ret_status OUT NOCOPY VARCHAR2,
363 p_msg_data OUT NOCOPY VARCHAR2,
364 p_msg_count OUT NOCOPY NUMBER ) IS
365 /* This procedure ends the interaction */
366
367 l_interaction_rec jtf_ih_pub.interaction_rec_type := jtf_ih_pub.init_interaction_rec;
368 l_active VARCHAR2(1);
369 l_msg_count NUMBER;
370 l_msg_data VARCHAR2(2000);
371 l_return_status VARCHAR2(1);
372 l_rec_count NUMBER;
373 l_msg_index_out NUMBER;
374 l_outcome_id NUMBER;
375 l_reason_id NUMBER;
376 l_result_id NUMBER;
377
378 CURSOR int_active IS
379 SELECT active, outcome_id, reason_id, result_id
380 FROM jtf_ih_interactions
381 WHERE interaction_id = Igr_in_jtf_interactions_pkg.g_int_id;
382
383 BEGIN
384
385 IF Igr_in_jtf_interactions_pkg.g_int_id IS NOT NULL THEN
386 OPEN int_active;
387 FETCH int_active INTO l_active, l_outcome_id, l_reason_id, l_result_id;
388 CLOSE int_active;
389
390 -- If outcome, result and reason are set in the database from some form other than
391 -- CC form, check if value is updated in database and pass to API accordingly
392 IF l_active = 'Y' THEN
393 IF (l_outcome_id IS NOT NULL) and (l_outcome_id <> fnd_api.g_miss_num) THEN
394 l_interaction_rec.outcome_id := l_outcome_id;
395 ELSE
396 l_interaction_rec.outcome_id := Igr_in_jtf_interactions_pkg.g_def_outcome_id;
397 END IF;
398 IF (l_result_id IS NOT NULL) and (l_result_id <> fnd_api.g_miss_num) THEN
399 l_interaction_rec.result_id := l_result_id;
400 ELSE
401 l_interaction_rec.result_id := Igr_in_jtf_interactions_pkg.g_def_result_id;
402 END IF;
403 IF (l_reason_id IS NOT NULL) and (l_reason_id <> fnd_api.g_miss_num) THEN
404 l_interaction_rec.reason_id := l_reason_id;
405 ELSE
406 l_interaction_rec.reason_id := Igr_in_jtf_interactions_pkg.g_def_reason_id;
407 END IF;
408
409 l_interaction_rec.interaction_id := Igr_in_jtf_interactions_pkg.g_int_id;
410 l_interaction_rec.end_date_time := SYSDATE;
411
412 JTF_IH_PUB.CLOSE_INTERACTION(p_api_version => Igr_in_jtf_interactions_pkg.g_api_version,
413 p_init_msg_list => Igr_in_jtf_interactions_pkg.g_true,
414 p_commit => Igr_in_jtf_interactions_pkg.g_false,
415 p_resp_appl_id => Igr_in_jtf_interactions_pkg.g_resp_appl_id,
416 p_resp_id => Igr_in_jtf_interactions_pkg.g_resp_id,
417 p_user_id => Igr_in_jtf_interactions_pkg.g_user_id,
418 p_login_id => Igr_in_jtf_interactions_pkg.g_login_id,
419 x_return_status => l_return_status,
420 x_msg_count => l_msg_count,
421 x_msg_data => l_msg_data,
422 p_interaction_rec=> l_interaction_rec);
423
424 IF l_return_status <> Igr_in_jtf_interactions_pkg.g_ret_sts_success THEN
425 -- Activity cannot be updated since there is no active interaction
426 FND_MESSAGE.SET_NAME('IGS','IGS_AD_JTF_CLS_INT_FLD');
427 IGS_GE_MSG_STACK.ADD;
428 APP_EXCEPTION.RAISE_EXCEPTION;
429 END IF;
430 END IF;
431
432 -- Clear the variable value
433 Igr_in_jtf_interactions_pkg.g_int_id := NULL;
434
435 END IF;
436
437 EXCEPTION
438 WHEN OTHERS THEN
439 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXCEPTION');
440 IGS_GE_MSG_STACK.ADD;
441 APP_EXCEPTION.RAISE_EXCEPTION;
442 END end_interaction;
443
444 END Igr_in_jtf_interactions_pkg;