[Home] [Help]
PACKAGE BODY: APPS.CSL_PARTY_CONTACTS_ACC_PKG
Source
1 PACKAGE BODY CSL_PARTY_CONTACTS_ACC_PKG AS
2 /* $Header: cslpcacb.pls 120.1 2005/08/31 02:56:26 utekumal noship $ */
3
4 g_debug_level NUMBER; -- debug level
5
6 /**
7 *
8 */
9 PROCEDURE INSERT_CONTACT_POINT( p_contact_point_id IN NUMBER
10 , p_resource_id IN NUMBER )
11 IS
12 l_table_name CONSTANT VARCHAR2(30) := 'HZ_CONTACT_POINTS';
13 l_acc_table_name CONSTANT VARCHAR2(30) := 'CSL_HZ_CONTACT_POINTS_ACC';
14 l_pk1_name CONSTANT VARCHAR2(30) := 'CONTACT_POINT_ID';
15 l_publication_item_name CONSTANT JTM_HOOK_UTIL_PKG.t_publication_item_list :=
16 JTM_HOOK_UTIL_PKG.t_publication_item_list('CSL_HZ_CONTACT_POINTS');
17
18 BEGIN
19 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
20 jtm_message_log_pkg.Log_Msg
21 ( p_contact_point_id
22 , l_table_name
23 , 'Entering INSERT_CONTACT_POINT'
24 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
25 END IF;
26
27 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
28 jtm_message_log_pkg.Log_Msg
29 ( p_contact_point_id
30 , l_table_name
31 , 'Inserting ACC record for resource_id = '||p_resource_id
32 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
33 END IF;
34
35 JTM_HOOK_UTIL_PKG.Insert_Acc
36 ( P_PUBLICATION_ITEM_NAMES => l_publication_item_name
37 , P_ACC_TABLE_NAME => l_acc_table_name
38 , P_PK1_NAME => l_pk1_name
39 , P_PK1_NUM_VALUE => p_contact_point_id
40 , P_RESOURCE_ID => p_resource_id
41 );
42
43 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
44 jtm_message_log_pkg.Log_Msg
45 ( p_contact_point_id
46 , l_table_name
47 , 'Leaving INSERT_CONTACT_POINT'
48 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
49 END IF;
50 END INSERT_CONTACT_POINT;
51
52 /**
53 *
54 */
55 PROCEDURE DELETE_CONTACT_POINT( p_contact_point_id IN NUMBER
56 , p_resource_id IN NUMBER )
57 IS
58 l_table_name CONSTANT VARCHAR2(30) := 'HZ_CONTACT_POINTS';
59 l_acc_table_name CONSTANT VARCHAR2(30) := 'CSL_HZ_CONTACT_POINTS_ACC';
60 l_pk1_name CONSTANT VARCHAR2(30) := 'CONTACT_POINT_ID';
61 l_publication_item_name CONSTANT JTM_HOOK_UTIL_PKG.t_publication_item_list :=
62 JTM_HOOK_UTIL_PKG.t_publication_item_list('CSL_HZ_CONTACT_POINTS');
63 BEGIN
64 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
65 jtm_message_log_pkg.Log_Msg
66 ( p_contact_point_id
67 , l_table_name
68 , 'Entering DELETE_CONTACT_POINT'
69 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
70 END IF;
71
72 JTM_HOOK_UTIL_PKG.Delete_Acc
73 ( P_PUBLICATION_ITEM_NAMES => l_publication_item_name
74 , P_ACC_TABLE_NAME => l_acc_table_name
75 , P_PK1_NAME => l_pk1_name
76 , P_PK1_NUM_VALUE => p_contact_point_id
77 , P_RESOURCE_ID => p_resource_id
78 );
79
80 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
81 jtm_message_log_pkg.Log_Msg
82 ( p_contact_point_id
83 , l_table_name
84 , 'Leaving DELETE_CONTACT_POINT'
85 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
86 END IF;
87
88 END DELETE_CONTACT_POINT;
89
90 /**
91 *
92 */
93 PROCEDURE INSERT_HZ_RELATIONSHIP( p_party_id IN NUMBER
94 , p_resource_id IN NUMBER )
95 IS
96 l_table_name CONSTANT VARCHAR2(30) := 'HZ_RELATIONSHIPS';
97 l_acc_table_name CONSTANT VARCHAR2(30) := 'CSL_HZ_RELATIONSHIPS_ACC';
98 l_pk1_name CONSTANT VARCHAR2(30) := 'RELATIONSHIP_ID';
99 l_pk2_name CONSTANT VARCHAR2(30) := 'DIRECTIONAL_FLAG';
100 l_publication_item_name CONSTANT JTM_HOOK_UTIL_PKG.t_publication_item_list :=
101 JTM_HOOK_UTIL_PKG.t_publication_item_list('CSL_HZ_RELATIONSHIPS');
102
103
104 CURSOR c_relationship( b_party_id NUMBER )
105 IS
106 SELECT *
107 FROM HZ_RELATIONSHIPS
108 WHERE PARTY_ID = b_party_id
109 AND DIRECTIONAL_FLAG = 'F';
110
111 r_relationship c_relationship%ROWTYPE;
112
113 BEGIN
114 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
115 jtm_message_log_pkg.Log_Msg
116 ( p_party_id
117 , l_table_name
118 , 'Entering INSERT_HZ_RELATIONSHIP'
119 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
120 END IF;
121
122 OPEN c_relationship( p_party_id );
123 FETCH c_relationship INTO r_relationship;
124 IF c_relationship%FOUND THEN
125 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
126 jtm_message_log_pkg.Log_Msg
127 ( r_relationship.relationship_id
128 , l_table_name
129 , 'Inserting ACC record for resource_id = '||p_resource_id
130 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
131 END IF;
132
133 JTM_HOOK_UTIL_PKG.Insert_Acc
134 ( P_PUBLICATION_ITEM_NAMES => l_publication_item_name
135 , P_ACC_TABLE_NAME => l_acc_table_name
136 , P_PK1_NAME => l_pk1_name
137 , P_PK1_NUM_VALUE => r_relationship.relationship_id
138 , P_PK2_NAME => l_pk2_name
139 , p_PK2_CHAR_VALUE => 'F'
140 , P_RESOURCE_ID => p_resource_id
141 );
142
143 /*Call the party of this relation*/
144 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
145 jtm_message_log_pkg.Log_Msg
146 ( r_relationship.SUBJECT_ID
147 , l_table_name
148 , 'Calling the party for this relationship'
149 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
150 END IF;
151 CSL_HZ_PARTIES_ACC_PKG.INSERT_PARTY( r_relationship.SUBJECT_ID, p_resource_id );
152 ELSE
153 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
154 jtm_message_log_pkg.Log_Msg
155 ( p_party_id
156 , l_table_name
157 , 'Could not find Relationship record'
158 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
159 END IF;
160 END IF;
161
162 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
163 jtm_message_log_pkg.Log_Msg
164 ( p_party_id
165 , l_table_name
166 , 'Leaving INSERT_HZ_RELATIONSHIP'
167 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
168 END IF;
169 END INSERT_HZ_RELATIONSHIP;
170
171 /**
172 *
173 */
174 PROCEDURE DELETE_HZ_RELATIONSHIP( p_party_id IN NUMBER
175 , p_resource_id IN NUMBER )
176 IS
177 l_table_name CONSTANT VARCHAR2(30) := 'HZ_RELATIONSHIPS';
178 l_acc_table_name CONSTANT VARCHAR2(30) := 'CSL_HZ_RELATIONSHIPS_ACC';
179 l_pk1_name CONSTANT VARCHAR2(30) := 'RELATIONSHIP_ID';
180 l_pk2_name CONSTANT VARCHAR2(30) := 'DIRECTIONAL_FLAG';
181 l_publication_item_name CONSTANT JTM_HOOK_UTIL_PKG.t_publication_item_list :=
182 JTM_HOOK_UTIL_PKG.t_publication_item_list('CSL_HZ_RELATIONSHIPS');
183
184 CURSOR c_relationship( b_party_id NUMBER )
185 IS
186 SELECT *
187 FROM HZ_RELATIONSHIPS
188 WHERE PARTY_ID = b_party_id
189 AND DIRECTIONAL_FLAG = 'F';
190
191 r_relationship c_relationship%ROWTYPE;
192
193 BEGIN
194 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
195 jtm_message_log_pkg.Log_Msg
196 ( p_party_id
197 , l_table_name
198 , 'Entering DELETE_HZ_RELATIONSHIP'
199 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
200 END IF;
201
202 OPEN c_relationship( p_party_id );
203 FETCH c_relationship INTO r_relationship;
204 IF c_relationship%FOUND THEN
205
206 JTM_HOOK_UTIL_PKG.Delete_Acc
207 ( P_PUBLICATION_ITEM_NAMES => l_publication_item_name
208 , P_ACC_TABLE_NAME => l_acc_table_name
209 , P_PK1_NAME => l_pk1_name
210 , P_PK1_NUM_VALUE => p_party_id
211 , P_PK2_NAME => l_pk2_name
212 , P_PK2_CHAR_VALUE => 'F'
213 , P_RESOURCE_ID => p_resource_id
214 );
215
216 /*Delete the matching party*/
217 CSL_HZ_PARTIES_ACC_PKG.INSERT_PARTY( r_relationship.SUBJECT_ID, p_resource_id );
218 END IF;
219 CLOSE c_relationship;
220
221 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
222 jtm_message_log_pkg.Log_Msg
223 ( p_party_id
224 , l_table_name
225 , 'Leaving DELETE_HZ_RELATIONSHIP'
226 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
227 END IF;
228 END DELETE_HZ_RELATIONSHIP;
229
230 /**
231 *
232 */
233 PROCEDURE INSERT_CS_HZ_SR_CONTACTS( p_incident_id IN NUMBER
234 , p_resource_id IN NUMBER
235 , p_flow_type IN NUMBER )--DEFAULT CSL_CS_INCIDENTS_ALL_ACC_PKG.G_FLOW_NORMAL)
236 IS
237 CURSOR c_contacts( b_incident_id NUMBER
238 , b_resource_id NUMBER)
239 IS
240 SELECT CHS.*
241 FROM CS_HZ_SR_CONTACT_POINTS CHS
242 WHERE CHS.INCIDENT_ID = b_incident_id
243 AND CHS.SR_CONTACT_POINT_ID NOT IN (
244 SELECT CCA.SR_CONTACT_POINT_ID
245 FROM CSL_CS_HZ_SR_CONTACT_PTS_ACC CCA
246 WHERE CCA.RESOURCE_ID = b_resource_id
247 );
248
249 CURSOR c_obsolete_contacts( b_incident_id NUMBER
250 , b_resource_id NUMBER)
251 IS
252 SELECT CCA.*
253 FROM CSL_CS_HZ_SR_CONTACT_PTS_ACC CCA
254 WHERE CCA.RESOURCE_ID = b_resource_id
255 AND CCA.SR_CONTACT_POINT_ID NOT IN (
256 SELECT CHS.SR_CONTACT_POINT_ID
257 FROM CS_HZ_SR_CONTACT_POINTS CHS
258 --WHERE CHS.INCIDENT_ID = b_incident_id
259 );
260
261 /*Each procedure has its own set of constants*/
262 l_table_name CONSTANT VARCHAR2(30) := 'CS_HZ_SR_CONTACT_POINTS';
263 l_acc_table_name CONSTANT VARCHAR2(30) := 'CSL_CS_HZ_SR_CONTACT_PTS_ACC';
264 l_pk1_name CONSTANT VARCHAR2(30) := 'SR_CONTACT_POINT_ID';
265 l_publication_item_name CONSTANT JTM_HOOK_UTIL_PKG.t_publication_item_list :=
266 JTM_HOOK_UTIL_PKG.t_publication_item_list('CSL_CS_HZ_SR_CONTACT_PTS');
267 BEGIN
268 /*** get debug level ***/
269 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
270
271 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
272 jtm_message_log_pkg.Log_Msg
273 ( p_incident_id
274 , l_table_name
275 , 'Entering INSERT_CS_HZ_SR_CONTACTS'
276 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
277 END IF;
278
279 /*** Insert contact point ACC record ***/
280 FOR r_contact IN c_contacts( b_incident_id => p_incident_id, b_resource_id => p_resource_id ) LOOP
281 IF p_flow_type = CSL_CS_INCIDENTS_ALL_ACC_PKG.G_FLOW_NORMAL OR (
282 p_flow_type = CSL_CS_INCIDENTS_ALL_ACC_PKG.G_FLOW_HISTORY AND
283 r_contact.PRIMARY_FLAG = 'Y' ) THEN
284 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
285 jtm_message_log_pkg.Log_Msg
286 ( r_contact.SR_CONTACT_POINT_ID
287 , l_table_name
288 , 'Inserting ACC record for resource_id = '||p_resource_id
289 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
290 END IF;
291
292 JTM_HOOK_UTIL_PKG.Insert_Acc
293 ( P_PUBLICATION_ITEM_NAMES => l_publication_item_name
294 , P_ACC_TABLE_NAME => l_acc_table_name
295 , P_PK1_NAME => l_pk1_name
296 , P_PK1_NUM_VALUE => r_contact.SR_CONTACT_POINT_ID
297 , P_RESOURCE_ID => p_resource_id
298 );
299
300
301 /*Insert the contact point record*/
302 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
303 jtm_message_log_pkg.Log_Msg
304 ( r_contact.SR_CONTACT_POINT_ID
305 , l_table_name
306 , 'Calling Insert_Contact_point for contact'||r_contact.CONTACT_POINT_ID
307 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
308 END IF;
309 IF r_contact.CONTACT_POINT_ID IS NOT NULL THEN
310 /*Insert the contact point record*/
311 INSERT_CONTACT_POINT( r_contact.CONTACT_POINT_ID, p_resource_id );
312 END IF;
313
314 /*Get the matching record ( party / party relation / employee or organization */
315 IF r_contact.CONTACT_TYPE = 'PERSON' THEN
316 -- Call procedure for hz_parties
317 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
318 jtm_message_log_pkg.Log_Msg
319 ( r_contact.SR_CONTACT_POINT_ID
320 , l_table_name
321 , 'Contact is of type ''PERSON'''||fnd_global.local_chr(10)||
322 'Calling the CSL_HZ_PARTIES_ACC_PKG.INSERT_PARTY'
323 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
324 END IF;
325 CSL_HZ_PARTIES_ACC_PKG.INSERT_PARTY( r_contact.PARTY_ID , p_resource_id );
326 END IF; --PERSON
327
328 IF r_contact.CONTACT_TYPE = 'PARTY_RELATIONSHIP' THEN
329 -- Call procedure for hz_party_relation_ships
330 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
331 jtm_message_log_pkg.Log_Msg
332 ( r_contact.SR_CONTACT_POINT_ID
333 , l_table_name
334 , 'Contact is of type ''PARTY_RELATIONSHIP'''||fnd_global.local_chr(10)||
335 'Calling INSERT_HZ_RELATIONSHIP'
336 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
337 END IF;
338 INSERT_HZ_RELATIONSHIP( r_contact.PARTY_ID, p_resource_id );
339 END IF; --PARTY_RELATIONSHIP
340
341 IF r_contact.CONTACT_TYPE = 'EMPLOYEE' THEN
342 -- Call procedure for per_all_people_f
343 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
344 jtm_message_log_pkg.Log_Msg
345 ( r_contact.SR_CONTACT_POINT_ID
346 , l_table_name
347 , 'Contact is of type ''EMPLOYEE'''||fnd_global.local_chr(10)||
348 'Calling INSERT_PER_ALL_PEOPLE_F'
349 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
350 END IF;
351 INSERT_PER_ALL_PEOPLE_F( r_contact.PARTY_ID, p_resource_id );
352 END IF; --EMPLOYEE
353
354 IF r_contact.CONTACT_TYPE = 'ORGANIZATION' THEN
355 -- Call procedure for ...
356 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
357 jtm_message_log_pkg.Log_Msg
358 ( r_contact.SR_CONTACT_POINT_ID
359 , l_table_name
360 , 'Contact is of type ''ORGANIZATION'''||fnd_global.local_chr(10)||
361 'Call to be implemented'
362 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
363 END IF;
364 NULL;
365 END IF; --ORGANIZATION
366 END IF; --Flow check
367 END LOOP;
368
369 /*Delete all contacts in the acc table for this incident that are no longer valid*/
370 FOR r_obsolete IN c_obsolete_contacts( b_incident_id => p_incident_id, b_resource_id => p_resource_id ) LOOP
371 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
372 jtm_message_log_pkg.Log_Msg
373 ( r_obsolete.SR_CONTACT_POINT_ID
374 , l_table_name
375 , 'Deleting contact record '||r_obsolete.SR_CONTACT_POINT_ID||fnd_global.local_chr(10)||
376 'for resource '||p_resource_id
377 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
378 END IF;
379
380 JTM_HOOK_UTIL_PKG.Delete_Acc
381 ( P_PUBLICATION_ITEM_NAMES => l_publication_item_name
382 , P_ACC_TABLE_NAME => l_acc_table_name
383 , P_PK1_NAME => l_pk1_name
384 , P_PK1_NUM_VALUE => r_obsolete.SR_CONTACT_POINT_ID
385 , P_RESOURCE_ID => p_resource_id
386 );
387
388 END LOOP;
389
390 /*Done, all packages are called*/
391 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
392 jtm_message_log_pkg.Log_Msg
393 ( p_incident_id
394 , l_table_name
395 , 'Leaving INSERT_CS_HZ_SR_CONTACTS'
396 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
397 END IF;
398 END INSERT_CS_HZ_SR_CONTACTS;
399
400 /**
401 *
402 */
403 PROCEDURE DELETE_CS_HZ_SR_CONTACTS( p_incident_id IN NUMBER
404 , p_resource_id IN NUMBER
405 , p_flow_type IN NUMBER ) --DEFAULT CSL_CS_INCIDENTS_ALL_ACC_PKG.G_FLOW_NORMAL)
406 IS
407 l_table_name CONSTANT VARCHAR2(30) := 'CS_HZ_SR_CONTACT_POINTS';
408 l_acc_table_name CONSTANT VARCHAR2(30) := 'CSL_CS_HZ_SR_CONTACT_PTS_ACC';
409 l_pk1_name CONSTANT VARCHAR2(30) := 'SR_CONTACT_POINT_ID';
410 l_publication_item_name CONSTANT JTM_HOOK_UTIL_PKG.t_publication_item_list :=
411 JTM_HOOK_UTIL_PKG.t_publication_item_list('CSL_CS_HZ_SR_CONTACT_PTS');
412
413 CURSOR c_contacts( b_incident_id NUMBER
414 , b_resource_id NUMBER)
415 IS
416 SELECT CHS.*
417 FROM CS_HZ_SR_CONTACT_POINTS CHS
418 WHERE CHS.INCIDENT_ID = b_incident_id
419 AND CHS.SR_CONTACT_POINT_ID IN (
420 SELECT CCA.SR_CONTACT_POINT_ID
421 FROM CSL_CS_HZ_SR_CONTACT_PTS_ACC CCA
422 WHERE CCA.RESOURCE_ID = b_resource_id
423 );
424
425 BEGIN
426 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
427 jtm_message_log_pkg.Log_Msg
428 ( p_incident_id
429 , l_table_name
430 , 'Entering DELETE_CS_HZ_SR_CONTACTS'
431 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
432 END IF;
433
434 FOR r_contact IN c_contacts( b_incident_id => p_incident_id, b_resource_id => p_resource_id ) LOOP
435 IF p_flow_type = CSL_CS_INCIDENTS_ALL_ACC_PKG.G_FLOW_NORMAL OR (
436 p_flow_type = CSL_CS_INCIDENTS_ALL_ACC_PKG.G_FLOW_HISTORY AND
437 r_contact.PRIMARY_FLAG = 'Y' ) THEN
438
439 /*Delete the records from incident-contact mapping table*/
440 JTM_HOOK_UTIL_PKG.Delete_Acc
441 ( P_PUBLICATION_ITEM_NAMES => l_publication_item_name
442 , P_ACC_TABLE_NAME => l_acc_table_name
443 , P_PK1_NAME => l_pk1_name
444 , P_PK1_NUM_VALUE => r_contact.SR_CONTACT_POINT_ID
445 , P_RESOURCE_ID => p_resource_id
446 );
447
448 /*Delete the real contacts*/
449 DELETE_CONTACT_POINT( r_contact.CONTACT_POINT_ID, p_resource_id );
450
451 IF r_contact.CONTACT_TYPE = 'PERSON' THEN
452 CSL_HZ_PARTIES_ACC_PKG.DELETE_PARTY( r_contact.PARTY_ID , p_resource_id );
453 END IF; --PERSON
454
455 IF r_contact.CONTACT_TYPE = 'PARTY_RELATIONSHIP' THEN
456 DELETE_HZ_RELATIONSHIP( r_contact.PARTY_ID, p_resource_id );
457 END IF; --PARTY_RELATIONSHIP
458
459 IF r_contact.CONTACT_TYPE = 'EMPLOYEE' THEN
460 DELETE_PER_ALL_PEOPLE_F( r_contact.PARTY_ID, p_resource_id );
461 END IF; --EMPLOYEE
462
463 IF r_contact.CONTACT_TYPE = 'ORGANIZATION' THEN
464 NULL;
465 END IF; --ORGANIZATION
466 END IF; --p_flow_id
467 END LOOP;
468
469 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
470 jtm_message_log_pkg.Log_Msg
471 ( p_incident_id
472 , l_table_name
473 , 'Leaving DELETE_CS_HZ_SR_CONTACTS'
474 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
475 END IF;
476
477 END DELETE_CS_HZ_SR_CONTACTS;
478
479 /**
480 *
481 */
482 PROCEDURE INSERT_PER_ALL_PEOPLE_F( p_person_id IN NUMBER
483 , p_resource_id IN NUMBER )
484 IS
485
486 CURSOR c_per_all_people(b_person_id NUMBER) IS
487 SELECT EFFECTIVE_START_DATE , EFFECTIVE_END_DATE
488 FROM PER_ALL_PEOPLE_F
489 WHERE PERSON_ID = b_person_id;
490 l_table_name CONSTANT VARCHAR2(30) := 'PER_ALL_PEOPLE_F';
491 l_acc_table_name CONSTANT VARCHAR2(30) := 'JTM_PER_ALL_PEOPLE_F_ACC';
492 l_pk1_name CONSTANT VARCHAR2(30) := 'PERSON_ID';
493 l_pk2_name CONSTANT VARCHAR2(30) := 'EFFECTIVE_START_DATE';
494 l_pk3_name CONSTANT VARCHAR2(30) := 'EFFECTIVE_END_DATE';
495 l_publication_item_name CONSTANT JTM_HOOK_UTIL_PKG.t_publication_item_list :=
496 JTM_HOOK_UTIL_PKG.t_publication_item_list('PER_ALL_PEOPLE_F');
497
498 BEGIN
499 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
500 jtm_message_log_pkg.Log_Msg
501 ( p_person_id
502 , l_table_name
503 , 'Entering INSERT_PER_ALL_PEOPLE_F'
504 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
505 END IF;
506
507 FOR r_per_all_people IN c_per_all_people(b_person_id => p_person_id) LOOP
508 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
509 jtm_message_log_pkg.Log_Msg
510 ( p_person_id
511 , l_table_name
512 , 'Inserting ACC record for resource_id = '||p_resource_id
513 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
514 END IF;
515
516 JTM_HOOK_UTIL_PKG.Insert_Acc
517 ( P_PUBLICATION_ITEM_NAMES => l_publication_item_name
518 , P_ACC_TABLE_NAME => l_acc_table_name
519 , P_PK1_NAME => l_pk1_name
520 , P_PK1_NUM_VALUE => p_person_id
521 , P_PK2_NAME => l_pk2_name
522 , P_PK2_DATE_VALUE => r_per_all_people.EFFECTIVE_START_DATE
523 , P_PK3_NAME => l_pk3_name
524 , P_PK3_DATE_VALUE => r_per_all_people.EFFECTIVE_END_DATE
525 , P_RESOURCE_ID => p_resource_id
526 );
527 END LOOP;
528
529 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
530 jtm_message_log_pkg.Log_Msg
531 ( p_person_id
532 , l_table_name
533 , 'Leaving INSERT_PER_ALL_PEOPLE_F'
534 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
535 END IF;
536 END INSERT_PER_ALL_PEOPLE_F;
537
538 /**
539 *
540 */
541 PROCEDURE DELETE_PER_ALL_PEOPLE_F ( p_person_id IN NUMBER
542 , p_resource_id IN NUMBER )
543 IS
544 CURSOR c_per_all_people(b_person_id NUMBER) IS
545 SELECT EFFECTIVE_START_DATE , EFFECTIVE_END_DATE
546 FROM PER_ALL_PEOPLE_F
547 WHERE PERSON_ID = b_person_id;
548 l_table_name CONSTANT VARCHAR2(30) := 'PER_ALL_PEOPLE_F';
549 l_acc_table_name CONSTANT VARCHAR2(30) := 'JTM_PER_ALL_PEOPLE_F_ACC';
550 l_pk1_name CONSTANT VARCHAR2(30) := 'PERSON_ID';
551 l_pk2_name CONSTANT VARCHAR2(30) := 'EFFECTIVE_START_DATE';
552 l_pk3_name CONSTANT VARCHAR2(30) := 'EFFECTIVE_END_DATE';
553 l_publication_item_name CONSTANT JTM_HOOK_UTIL_PKG.t_publication_item_list :=
554 JTM_HOOK_UTIL_PKG.t_publication_item_list('PER_ALL_PEOPLE_F');
555
556 BEGIN
557 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
558 jtm_message_log_pkg.Log_Msg
559 ( p_person_id
560 , l_table_name
561 , 'Entering DELETE_PER_ALL_PEOPLE_F'
562 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
563 END IF;
564
565 FOR r_per_all_people IN c_per_all_people(b_person_id => p_person_id) LOOP
566 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
567 jtm_message_log_pkg.Log_Msg
568 ( p_person_id
569 , l_table_name
570 , 'Deleting ACC record for resource_id = '||p_resource_id
571 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
572 END IF;
573
574 JTM_HOOK_UTIL_PKG.Delete_Acc
575 ( P_PUBLICATION_ITEM_NAMES => l_publication_item_name
576 , P_ACC_TABLE_NAME => l_acc_table_name
577 , P_PK1_NAME => l_pk1_name
578 , P_PK1_NUM_VALUE => p_person_id
579 , P_PK2_NAME => l_pk2_name
580 , P_PK2_DATE_VALUE => r_per_all_people.EFFECTIVE_START_DATE
581 , P_PK3_NAME => l_pk3_name
582 , P_PK3_DATE_VALUE => r_per_all_people.EFFECTIVE_END_DATE
583 , P_RESOURCE_ID => p_resource_id
584 );
585 END LOOP;
586
587 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
588 jtm_message_log_pkg.Log_Msg
589 ( p_person_id
590 , l_table_name
591 , 'Leaving DELETE_PER_ALL_PEOPLE_F'
592 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
593 END IF;
594
595 END DELETE_PER_ALL_PEOPLE_F;
596
597 FUNCTION UPDATE_CONTACT_POINT_WFSUB( p_subscription_guid in raw
598 , p_event in out NOCOPY wf_event_t)
599 return varchar2
600 IS
601 l_table_name CONSTANT VARCHAR2(30) := 'HZ_CONTACT_POINTS';
602 l_acc_table_name CONSTANT VARCHAR2(30) := 'CSL_HZ_CONTACT_POINTS_ACC';
603 l_pk1_name CONSTANT VARCHAR2(30) := 'CONTACT_POINT_ID';
604 l_publication_item_name CONSTANT JTM_HOOK_UTIL_PKG.t_publication_item_list :=
605 JTM_HOOK_UTIL_PKG.t_publication_item_list('CSL_HZ_CONTACT_POINTS');
606 l_key varchar2(240) := p_event.GetEventKey();
607 l_org_id NUMBER;
608 l_user_id NUMBER;
609 l_resp_id NUMBER;
610 l_resp_appl_id NUMBER;
611 l_security_group_id NUMBER;
612 l_count NUMBER;
613 l_contact_point_id NUMBER;
614 l_tab_resource_id dbms_sql.Number_Table;
615 l_tab_access_id dbms_sql.Number_Table;
616 BEGIN
617 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
618
619
620 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
621 jtm_message_log_pkg.Log_Msg
622 ( l_contact_point_id
623 , l_table_name
624 , 'Entering UPDATE_CONTACT_POINT_WFSUB'
625 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
626 END IF;
627
628 l_org_id := p_event.GetValueForParameter('ORG_ID');
629 l_user_id := p_event.GetValueForParameter('USER_ID');
630 l_resp_id := p_event.GetValueForParameter('RESP_ID');
631 l_resp_appl_id := p_event.GetValueForParameter('RESP_APPL_ID');
632 l_security_group_id := p_event.GetValueForParameter('SECURITY_GROUP_ID');
633
634 fnd_global.apps_initialize (l_user_id, l_resp_id, l_resp_appl_id, l_security_group_id);
635
636 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
637 jtm_message_log_pkg.Log_Msg
638 ( l_contact_point_id
639 , l_table_name
640 , 'Get parameter for hz parameter P_CONTACT_POINT_REC.CONTACT_POINT_ID'
641 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
642 END IF;
643 --Bug 4496299
644 /*
645 l_contact_point_id := hz_param_pkg.ValueOfNumParameter (p_key => l_key,
646 p_parameter_name => 'P_CONTACT_POINT_REC.CONTACT_POINT_ID');
647 */
648
649 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
650 jtm_message_log_pkg.Log_Msg
651 ( l_contact_point_id
652 , l_table_name
653 , 'Retrieved parameter for hz parameter P_CONTACT_POINT_REC.CONTACT_POINT_ID ' || l_contact_point_id
654 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
655 END IF;
656
657 /*Is record valid ? assume so*/
658 JTM_HOOK_UTIL_PKG.Get_Resource_Acc_List
659 ( P_ACC_TABLE_NAME => l_acc_table_name
660 , P_PK1_NAME => l_pk1_name
661 , P_PK1_NUM_VALUE => l_contact_point_id
662 , L_TAB_RESOURCE_ID => l_tab_resource_id
663 , L_TAB_ACCESS_ID => l_tab_access_id
664 );
665
666 /*** re-send rec to all resources ***/
667 IF l_tab_resource_id.COUNT > 0 THEN
668 FOR i IN l_tab_resource_id.FIRST .. l_tab_resource_id.LAST LOOP
669 JTM_HOOK_UTIL_PKG.Update_Acc
670 ( P_PUBLICATION_ITEM_NAMES => l_publication_item_name
671 , P_ACC_TABLE_NAME => l_acc_table_name
672 , P_RESOURCE_ID => l_tab_resource_id(i)
673 , P_ACCESS_ID => l_tab_access_id(i)
674 );
675 END LOOP;
676 END IF;
677
678 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
679 jtm_message_log_pkg.Log_Msg
680 ( l_contact_point_id
681 , l_table_name
682 , 'Leaving UPDATE_CONTACT_POINT_WFSUB'
683 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
684 END IF;
685
686 RETURN 'SUCCESS';
687
688 EXCEPTION
689 WHEN OTHERS THEN
690 WF_CORE.CONTEXT('CSL_PARTY_CONTACTS_ACC_PKG', 'UPDATE_CONTACT_POINT_WFSUB', p_event.getEventName(), p_subscription_guid);
691 WF_EVENT.setErrorInfo(p_event, 'ERROR');
692 RETURN 'ERROR';
693 END UPDATE_CONTACT_POINT_WFSUB;
694
695 END CSL_PARTY_CONTACTS_ACC_PKG;