1 Package BODY CS_SRCONTACT_PKG AS
2 /* $Header: cssrcpb.pls 120.9 2006/11/08 18:53:35 spusegao noship $*/
3 TYPE NUM_TBL IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
4 --------------------------------------------------------------------------------
5 -- Procedure Name : check_duplicates
6 -- Parameters :
7 -- IN : p_mode it indicates SR API. it could be CREATE or
8 -- UPDATE
9 -- : p_incident_id Service Request Identifier
10 -- : p_new_contact_tbl SR Contact passed to SR API
11 -- : p_updated_cp_qry Partial where clause having updated
12 -- contact points
13 -- : p_updated_cp_bind An array of Contact point Ids that are
14 -- being updated by SR API call
15 -- OUT : x_return_status Error condition indicator
16 --
17 --
18 -- Description : This procedure gets the list of all contact records from
19 -- database that are not being updated in SR API call.
20 -- Then it checks for duplicate entries in p_new_contact_tbl
21 -- if no duplicate entries are found then it looks for
22 -- duplicates between input records and not updated contact
23 -- records. If a duplucate is found then x_return_status is
24 -- set to Error otherwise it is set to success.
25 -- A record is considered duplicate if it has same value of
26 -- contact type, party id, contact point type, contact point
27 -- id, party role code and overlapping start and end dates
28 --
29 -- Modification History:
30 -- Date Name Desc
31 -------- -------- --------------------------------------------------------------
32 -- 10/21/05 smisra Created
33 -- 10/25/05 smisrs Added a new parameter p_mode to check_duplicates and
34 -- executed the code to get not updated contacts only for
35 -- update SR API.
36 --------------------------------------------------------------------------------
37 PROCEDURE check_duplicates
38 ( p_mode IN VARCHAR2
39 , p_new_contact_tbl IN CS_SERVICEREQUEST_PVT.contacts_table
40 , p_updated_cp_qry IN VARCHAR2
41 , p_updated_cp_bind IN NUM_TBL
42 , p_incident_id IN NUMBER
43 , x_return_status OUT NOCOPY VARCHAR2
44 ) IS
45 l_not_updated_contacts CS_SERVICEREQUEST_PVT.contacts_table;
46 --
47 l_sql VARCHAR2(4000);
48 l_cur_hdl INT;
49 l_rows_processed BINARY_INTEGER;
50 --
51 l_index NUMBER;
52 l_dup_found NUMBER;
53 --
54 l_st_dt1 DATE;
55 l_st_dt2 DATE;
56 l_end_dt1 DATE;
57 l_end_dt2 DATE;
58 --
59 l_party_id NUMBER;
60 l_contact_point_id NUMBER;
61 l_contact_type VARCHAR2(30);
62 l_party_role_code VARCHAR2(30);
63 l_contact_point_type VARCHAR2(30);
64 l_end_date_active DATE;
65 l_start_date_active DATE;
66 l_dup_role CS_HZ_SR_CONTACT_POINTS.party_role_code % TYPE;
67 BEGIN
68 x_return_status := FND_API.G_RET_STS_SUCCESS;
69 -- For SR Create API, no need to look for existing contact record because
70 -- there will be none.
71 IF p_mode <> 'CREATE'
72 THEN
73 l_sql := 'SELECT contact_type, party_id, party_role_code, contact_point_id, contact_point_type, start_date_active, end_date_active
74 FROM cs_hz_sr_contact_points
75 WHERE incident_id = :incident_id
76 /* AND TRUNC(SYSDATE) BETWEEN NVL(start_date_active, TRUNC(SYSDATE))
77 AND NVL(end_date_active , TRUNC(SYSDATE)) */';
78 IF p_updated_cp_qry <> ':1'
79 THEN
80 l_sql := l_sql || ' AND sr_contact_point_id NOT IN ( '|| p_updated_cp_qry || ')';
81 END IF;
82
83 l_cur_hdl := dbms_sql.open_cursor;
84 DBMS_SQL.parse(l_cur_hdl, l_sql, DBMS_SQL.NATIVE);
85 --
86 DBMS_SQL.bind_variable(l_cur_hdl, ':incident_id' , p_incident_id);
87 IF p_updated_cp_qry <> ':1'
88 THEN
89 FOR indx in p_updated_cp_bind.FIRST..p_updated_cp_bind.LAST
90 LOOP
91 DBMS_SQL.bind_variable(l_cur_hdl, ':' ||to_char(indx), p_updated_cp_bind(indx));
92 END LOOP;
93 END IF;
94 DBMS_SQL.define_column(l_cur_hdl, 1, l_contact_type, 30);
95 DBMS_SQL.define_column(l_cur_hdl, 2, l_party_id);
96 DBMS_SQL.define_column(l_cur_hdl, 3, l_party_role_code, 30);
97 DBMS_SQL.define_column(l_cur_hdl, 4, l_contact_point_id);
98 DBMS_SQL.define_column(l_cur_hdl, 5, l_contact_point_type, 30);
99 DBMS_SQL.define_column(l_cur_hdl, 6, l_start_date_active);
100 DBMS_SQL.define_column(l_cur_hdl, 7, l_end_date_active);
101 l_rows_processed := DBMS_SQL.execute(l_cur_hdl);
102 l_index := 0;
103 LOOP
104 IF DBMS_SQL.fetch_rows(l_cur_hdl) > 0
105 THEN
106 l_index := l_index + 1;
107 DBMS_SQL.column_value(l_cur_hdl, 1, l_contact_type);
108 DBMS_SQL.column_value(l_cur_hdl, 2, l_party_id );
109 DBMS_SQL.column_value(l_cur_hdl, 3, l_party_role_code );
110 DBMS_SQL.column_value(l_cur_hdl, 4, l_contact_point_id );
111 DBMS_SQL.column_value(l_cur_hdl, 5, l_contact_point_type);
112 DBMS_SQL.column_value(l_cur_hdl, 6, l_start_date_active );
113 DBMS_SQL.column_value(l_cur_hdl, 7, l_end_date_active );
114 l_not_updated_contacts(l_index).party_id := l_party_id;
115 l_not_updated_contacts(l_index).contact_type := l_contact_type;
116 l_not_updated_contacts(l_index).party_role_code := l_party_role_code;
117 l_not_updated_contacts(l_index).end_date_active := l_end_date_active;
118 l_not_updated_contacts(l_index).contact_point_id := l_contact_point_id;
119 l_not_updated_contacts(l_index).start_date_active := l_start_date_active;
120 l_not_updated_contacts(l_index).contact_point_type := l_contact_point_type;
121 ELSE
122 EXIT;
123 END IF;
124 END LOOP;
125 DBMS_SQL.close_cursor(l_cur_hdl);
126 END IF;
127 --
128 --
129 --
130 l_dup_found := 0;
131 IF p_new_contact_tbl.COUNT > 0
132 THEN
133 FOR i in p_new_contact_tbl.FIRST..p_new_contact_tbl.LAST
134 LOOP
135
136 l_st_dt1 := NVL(p_new_contact_tbl(i).start_date_active , TRUNC(SYSDATE-36500));
137 l_end_dt1 := NVL(p_new_contact_tbl(i).end_date_active , TRUNC(SYSDATE+36500));
138 -- if you are not at the last record, look for this and next record for duplicates
139 IF i < p_new_contact_tbl.LAST
140 THEN
141 FOR j in i+1..p_new_contact_tbl.LAST
142 LOOP
143 l_st_dt2 := NVL(p_new_contact_tbl(j).start_date_active, TRUNC(SYSDATE-36500));
144 l_end_dt2 := NVL(p_new_contact_tbl(j).end_date_active , TRUNC(SYSDATE+36500));
145 IF p_new_contact_tbl(i).party_id = p_new_contact_tbl(j).party_id AND
146 p_new_contact_tbl(i).contact_type = p_new_contact_tbl(j).contact_type AND
147 p_new_contact_tbl(i).party_role_code = p_new_contact_tbl(j).party_role_code AND
148 NVL(p_new_contact_tbl(i).contact_point_id ,-99) = NVL(p_new_contact_tbl(j).contact_point_id ,-99) AND
149 NVL(p_new_contact_tbl(i).contact_point_type,'-') = NVL(p_new_contact_tbl(j).contact_point_type,'-') AND
150 ( l_st_dt1 BETWEEN l_st_dt2 AND l_end_dt2 OR
151 l_st_dt2 BETWEEN l_st_dt1 AND l_end_dt1
152 )
153 THEN
154 l_dup_found := 1;
155 l_dup_role := p_new_contact_tbl(i).party_role_code;
156 EXIT;
157 END IF;
158 END LOOP;
159 -- if duplicate contaqct is found among input records, no need to check any further
160 IF l_dup_found <> 0
161 THEN
162 EXIT;
163 END IF;
164 END IF; -- end of condition IF i < p_new_contact_tbl.LAST
165 -- Check if this record is same as any existing record that is not being updated.
166 IF l_not_updated_contacts.count > 0
167 THEN
168 FOR k in l_not_updated_contacts.FIRST..l_not_updated_contacts.LAST
169 LOOP
170 l_st_dt2 := NVL(l_not_updated_contacts(k).start_date_active, TRUNC(SYSDATE-36500));
171 l_end_dt2 := NVL(l_not_updated_contacts(k).end_date_active , TRUNC(SYSDATE+36500));
172 IF p_new_contact_tbl(i).party_id = l_not_updated_contacts(k).party_id AND
173 p_new_contact_tbl(i).contact_type = l_not_updated_contacts(k).contact_type AND
174 p_new_contact_tbl(i).party_role_code = l_not_updated_contacts(k).party_role_code AND
175 NVL(p_new_contact_tbl(i).contact_point_id ,-99) = NVL(l_not_updated_contacts(k).contact_point_id ,-99) AND
176 NVL(p_new_contact_tbl(i).contact_point_type,'-') = NVL(l_not_updated_contacts(k).contact_point_type,'-') AND
177 ( l_st_dt1 BETWEEN l_st_dt2 AND l_end_dt2 OR
178 l_st_dt2 BETWEEN l_st_dt1 AND l_end_dt1
179 )
180 THEN
181 l_dup_found := 1;
182 l_dup_role := p_new_contact_tbl(i).party_role_code;
183 EXIT;
184 END IF;
185 END LOOP;
186 -- if duplicate contaqct is found among input records, no need to check any further
187 IF l_dup_found <> 0
188 THEN
189 EXIT;
190 END IF;
191 END IF; --end of condition IF l_not_updated_contacts.count > 0
192 END LOOP;
193 IF l_dup_found <> 0
194 THEN
195 x_return_status := FND_API.g_ret_sts_error;
196 IF l_dup_role = 'CONTACT'
197 THEN
198 FND_MESSAGE.set_name ('CS', 'CS_SR_DUP_CONTACT_PARTY');
199 ELSE
200 FND_MESSAGE.set_name ('CS', 'CS_SR_DUP_ASSOC_PARTY');
201 END IF;
202 FND_MESSAGE.set_token ('API_NAME','CS_SRCONTACT_PKG.check_duplicates');
203 FND_MSG_PUB.ADD;
204 END IF;
205 END IF;
206 EXCEPTION
207 WHEN OTHERS
208 THEN
209 x_return_status := FND_API.g_ret_sts_error;
210 FND_MESSAGE.set_name ('CS', 'CS_API_SR_UNKNOWN_ERROR');
211 FND_MESSAGE.set_token('P_TEXT','CS_SRCONTACT_PKG.check_duplicates:'||'-'||SQLERRM);
212 FND_MSG_PUB.ADD;
213 END check_duplicates;
214 --------------------------------------------------------------------------------
215 -- Function Name : contact_role_count
216 -- Parameters :
217 -- IN : p_incident_id Service Request Identifier
218 -- Return Value : Number
219 --
220 --
221 -- Description : For a given service request, this function returns acitve
222 -- contact points having role as CONTACT
223 --
224 -- Modification History:
225 -- Date Name Desc
226 -------- -------- --------------------------------------------------------------
227 -- 10/06/05 smisra Created
228 --------------------------------------------------------------------------------
229 FUNCTION contact_role_count
230 ( p_incident_id IN NUMBER
231 ) RETURN NUMBER IS
232 l_dt DATE;
233 l_count NUMBER;
234 BEGIN
235 l_dt := TRUNC(SYSDATE);
236 SELECT COUNT(1)
237 INTO l_count
238 FROM cs_hz_sr_contact_points
239 WHERE incident_id = p_incident_id
240 AND party_role_code = 'CONTACT'
241 AND NVL(END_DATE_ACTIVE,sysdate) >= l_dt
242 ;
243 RETURN(l_count);
244 EXCEPTION
245 WHEN OTHERS
246 THEN
247 NULL;
248 END contact_role_count;
249 --------------------------------------------------------------------------------
250 -- Procedure Name :
251 -- Parameters :
252 -- IN :
253 -- OUT :
254 --
255 -- Description :
256 --
257 -- Modification History:
258 -- Date Name Desc
259 -------- -------- --------------------------------------------------------------
260 -- 04/27/05 smisra Created
261 -- 07/27/05 smisra removed 1 from sequence name
262 -- 08/17/05 smisra add primary_flag, old primary flag to insert statement.
263 --------------------------------------------------------------------------------
264 Procedure create_cp_audit
265 ( p_sr_contact_point_id IN NUMBER
266 , p_incident_id IN NUMBER
267 , p_new_cp_rec IN CS_SERVICEREQUEST_PVT.contacts_rec
268 , p_old_cp_rec IN CS_SERVICEREQUEST_PVT.contacts_rec
269 , p_cp_modified_by IN NUMBER
270 , p_cp_modified_on IN DATE
271 , x_return_status OUT NOCOPY VARCHAR2
272 , x_msg_count OUT NOCOPY NUMBER
273 , x_msg_data OUT NOCOPY VARCHAR2
274 ) IS
275 l_dt DATE;
276 BEGIN
277 l_dt := SYSDATE;
278 INSERT INTO CS_HZ_SR_CONTACT_PNTS_AUDIT
279 ( sr_contact_point_audit_id
280 , sr_contact_point_id
281 , incident_id
282 , party_id
283 , old_party_id
284 , contact_type
285 , old_contact_type
286 , party_role_code
287 , old_party_role_code
288 , contact_point_type
289 , old_contact_point_type
290 , contact_point_id
291 , old_contact_point_id
292 , start_date_active
293 , old_start_date_active
294 , end_date_active
295 , old_end_date_active
296 , primary_flag
297 , old_primary_flag
298 , creation_date
299 , last_update_date
300 , created_by
301 , last_updated_by
302 , last_update_login
303 , contact_point_modified_by
304 , contact_point_modified_on
305 )
306 VALUES
307 ( cs.cs_hz_sr_cont_pnts_audit_s.NEXTVAL
308 , p_sr_contact_point_id
309 , p_incident_id
310 , p_new_cp_rec.party_id
311 , p_old_cp_rec.party_id
312 , p_new_cp_rec.contact_type
313 , p_old_cp_rec.contact_type
314 , p_new_cp_rec.party_role_code
315 , p_old_cp_rec.party_role_code
316 , p_new_cp_rec.contact_point_type
317 , p_old_cp_rec.contact_point_type
318 , p_new_cp_rec.contact_point_id
319 , p_old_cp_rec.contact_point_id
320 , p_new_cp_rec.start_date_active
321 , p_old_cp_rec.start_date_active
322 , p_new_cp_rec.end_date_active
323 , p_old_cp_rec.end_date_active
324 , p_new_cp_rec.primary_flag
325 , p_old_cp_rec.primary_flag
326 , l_dt
327 , l_dt
328 , FND_GLOBAL.USER_ID
329 , FND_GLOBAL.USER_ID
330 , FND_GLOBAL.LOGIN_ID
331 , p_cp_modified_by
332 , p_cp_modified_on
333 );
334 END create_cp_audit;
335 --
336 --------------------------------------------------------------------------------
337 -- Procedure Name :
338 -- Parameters :
339 -- IN :
340 -- OUT :
341 --
342 -- Description :
343 --
344 -- Modification History:
345 -- Date Name Desc
346 -------- -------- --------------------------------------------------------------
347 -- 04/15/05 smisra Created
348 --------------------------------------------------------------------------------
349
350 --------------------------------------------------------------------------------
351 -- Procedure Name : validate_contact
352 -- Parameters :
353 -- IN : p_caller_type This is service request customer type.
354 -- It can be ORGANIZATION or PERSON
355 -- p_customer_id Service request customer id. User for
356 -- validation of contact party.
357 -- p_new_contact_rec This record contains contact record passed
358 -- to service request API
359 -- p_old_contact_rec This record containt value of contact
360 -- record being update. in case of insert
361 -- this record in NULL
362 -- OUT : x_return_status Indicates success or Error condition
363 -- encountered by procedure.
364 --
365 -- Description : This procedure takes old and new value of contact being
366 -- processed and validates it. Old value record is needed to
367 -- determine if a particular attribute is changed or not.
368 -- validation is performed on only changed attributes.
369 -- in case of insert, all attributes that are not null are
370 -- assumed to be changed attributes.
371 --
372 -- Modification History:
373 -- Date Name Desc
374 -------- ----------- -----------------------------------------------------------
375 -- 04/15/05 smisra Created
376 -- 08/10/05 smisra Added following validations
377 -- Associate party has to exist in hz_parties only. there may
378 -- not be any relationship with SR customer
379 -- Primary contact can not have end date value
380 -- 10/05/05 smisra Change Request: 4645490
381 -- Raise error is party_id, contact_type or party_role_code
382 -- is updated
383 -- 10/21/05 smisra Added a validation that primary contact can not be future
384 -- dated
385 --------------------------------------------------------------------------------
386 PROCEDURE validate_contact
387 ( p_caller_type IN VARCHAR2
388 , p_customer_id IN NUMBER
389 , p_new_contact_rec IN CS_SERVICEREQUEST_PVT.contacts_rec
390 , p_old_contact_rec IN CS_SERVICEREQUEST_PVT.contacts_rec
391 , x_return_status OUT NOCOPY VARCHAR2
392 ) IS
393 l_api_name_full VARCHAR2(61);
394 l_employee_name VARCHAR2(80);
395 l_party_type cs_hz_sr_contact_points.contact_type % TYPE;
396 l_status hz_parties.status % TYPE;
397 p_mode VARCHAR2(30);
398 l_today DATE;
399 BEGIN
400 l_api_name_full := 'CS_SRCONTACT_PKG.validate_contact';
401 x_return_status := FND_API.G_RET_STS_SUCCESS;
402 l_today := TRUNC(SYSDATE);
403 -- set p_mode variable.
404 -- This could be passed to this procedure too
405 IF p_old_contact_rec.party_id IS NULL
406 THEN
407 p_mode := 'CREATE';
408 ELSE
409 p_mode := 'UPDATE';
410 END IF;
411 --
412 -- Check for update of non-updatable columns
413 --
414 IF p_mode = 'UPDATE'
415 THEN
416 -- check contact type update
417 IF p_new_contact_rec.contact_type <> p_old_contact_rec.contact_type
418 THEN
419 FND_MESSAGE.set_name ('CS','CS_SR_CP_CONTACT_TYPE_UPD_NA');
420 FND_MESSAGE.set_token ('API_NAME','cs_srcontact_pkg.validate_contact');
421 FND_MSG_PUB.add_detail( p_associated_column1=>'CS_HZ_SR_CONTACT_POINTS.CONTACT_TYPE');
422 RAISE FND_API.G_EXC_ERROR;
423 END IF;
424 -- check party_id update
425 IF p_new_contact_rec.party_id <> p_old_contact_rec.party_id
426 THEN
427 FND_MESSAGE.set_name ('CS','CS_SR_CP_PARTY_ID_UPD_NA');
428 FND_MESSAGE.set_token ('API_NAME','cs_srcontact_pkg.validate_contact');
429 FND_MSG_PUB.add_detail( p_associated_column1=>'CS_HZ_SR_CONTACT_POINTS.PARTY_ID');
430 RAISE FND_API.G_EXC_ERROR;
431 END IF;
432 -- check party_role_code update
433 IF p_new_contact_rec.party_role_code <> p_old_contact_rec.party_role_code
434 THEN
435 FND_MESSAGE.set_name ('CS','CS_SR_CP_PARTY_ROLE_UPD_NA');
436 FND_MESSAGE.set_token ('API_NAME','cs_srcontact_pkg.validate_contact');
437 FND_MSG_PUB.add_detail( p_associated_column1=>'CS_HZ_SR_CONTACT_POINTS.PARTY_ROLE_CODE');
438 RAISE FND_API.G_EXC_ERROR;
439 END IF;
440 END IF; -- p_mode = 'UPDATE'
441 --
442 -- Validate Contact Type
443 --
444 IF p_new_contact_rec.contact_type IS NULL
445 THEN
446 CS_SERVICEREQUEST_UTIL.add_null_parameter_msg
447 ( p_token_an => l_api_name_full
448 , p_token_np => 'p_contacts.contact_type'
449 , p_table_name => 'CS_HZ_SR_CONTACT_POINTS'
450 , p_column_name => 'CONTACT_TYPE'
451 );
452 RAISE FND_API.G_EXC_ERROR;
453 ELSIF (p_new_contact_rec.contact_type <> NVL(p_old_contact_rec.contact_type,'xx'))
454 THEN
455 IF NVL(p_new_contact_rec.party_role_code,'x') <> 'CONTACT'
456 THEN
457 IF p_new_contact_rec.contact_type = 'ORGANIZATION' OR
458 p_new_contact_rec.contact_type = 'PARTY_RELATIONSHIP' OR
459 p_new_contact_rec.contact_type = 'PERSON' OR
460 p_new_contact_rec.contact_type = 'EMPLOYEE'
461 THEN
462 NULL;
463 ELSE
464 CS_ServiceRequest_UTIL.Add_Invalid_Argument_Msg
465 ( p_token_an => l_api_name_full
466 , p_token_v => p_new_contact_rec.contact_type
467 , p_token_p => 'p_contacts.contact_type'
468 , p_table_name => 'CS_HZ_SR_CONTACT_POINTS'
469 , p_column_name => 'CONTACT_TYPE'
470 );
471 RAISE FND_API.G_EXC_ERROR;
472 END IF;
473 ELSE -- party role code is CONTACT
474 IF (p_new_contact_rec.contact_type = 'EMPLOYEE' OR
475 p_new_contact_rec.contact_type = 'ORGANIZATION' OR
476 p_new_contact_rec.contact_type = 'PARTY_RELATIONSHIP' OR
477 (p_new_contact_rec.contact_type = 'PERSON' AND
478 p_caller_type = 'PERSON')
479 )
480 THEN
481 NULL;
482 -- contact type is valid. do nothing.
483 ELSE
484 CS_ServiceRequest_UTIL.Add_Invalid_Argument_Msg(
485 p_token_an => l_api_name_full,
486 p_token_v => p_new_contact_rec.contact_type,
487 p_token_p => 'p_contacts.contact_type' ,
488 p_table_name => 'CS_HZ_SR_CONTACT_POINTS',
489 p_column_name => 'CONTACT_TYPE');
490 RAISE FND_API.G_EXC_ERROR;
491 END IF;
492 END IF; -- party_role_code = contact
493 END IF; -- for change in contact type
494 --
495 -- Validate Contact Party
496 --
497 IF p_new_contact_rec.party_id IS NULL
498 THEN
499 CS_SERVICEREQUEST_UTIL.add_null_parameter_msg
500 ( p_token_an => l_api_name_full,
501 p_token_np => 'p_contacts.party_id',
502 p_table_name => 'CS_HZ_SR_CONTACT_POINTS',
503 p_column_name => 'PARTY_ID'
504 );
505 RAISE FND_API.G_EXC_ERROR;
506 ELSIF (p_new_contact_rec.contact_type <> NVL(p_old_contact_rec.contact_type , 'xx') OR
507 p_new_contact_rec.party_id <> NVL(p_old_contact_rec.party_id , -1 ) OR
508 p_new_contact_rec.party_role_code <> NVL(p_old_contact_rec.party_role_code, 'CONTACT'))
509 THEN
510 IF (p_new_contact_rec.contact_type = 'EMPLOYEE') THEN
511 CS_ServiceRequest_UTIL.Validate_Employee
512 ( p_api_name => l_api_name_full
513 , p_parameter_name => 'p_employee_id'
514 , p_employee_id => p_new_contact_rec.party_id
515 , p_org_id => NULL
516 , p_employee_name => l_employee_name
517 , x_return_status => x_return_status
518 );
519 ELSIF NVL(p_new_contact_rec.party_role_code,'CONTACT') <> 'CONTACT'
520 -- Else condition means contact type is either person, org or relationship
521 -- so if party role is CONTACT then do not validation relationship between
522 -- SR customer and contact party. contact party should merely exist in
523 -- hz_parties table
524 THEN
525 CS_SERVICEREQUEST_UTIL.get_party_details
526 ( p_party_id => p_new_contact_rec.party_id
527 , x_party_type => l_party_type
528 , x_status => l_status
529 , x_return_status => x_return_status
530 );
531 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
532 THEN
533 FND_MESSAGE.set_name('CS','CS_SR_ASSOC_PARTY_NE');
534 FND_MESSAGE.set_token('API_NAME','cs_srcontact_pkg.validate_contact');
535 FND_MESSAGE.set_token('PARTY_ID',p_new_contact_rec.party_id);
536 FND_MSG_PUB.add_detail
537 ( p_associated_column1=>'CS_HZ_SR_CONTACT_POINTS.PARTY_ID'
538 );
539 RAISE FND_API.G_EXC_ERROR;
540 END IF;
541 IF l_status <> 'A'
542 THEN
543 FND_MESSAGE.set_name('CS','CS_SR_ASSOC_PARTY_INACTIVE');
544 FND_MESSAGE.set_token('API_NAME','cs_srcontact_pkg.validate_contact');
545 FND_MESSAGE.set_token('PARTY_ID',p_new_contact_rec.party_id);
546 FND_MSG_PUB.add_detail
547 ( p_associated_column1=>'CS_HZ_SR_CONTACT_POINTS.PARTY_ID'
548 );
549 RAISE FND_API.G_EXC_ERROR;
550 END IF;
551 IF l_party_type <> p_new_contact_rec.contact_type
552 THEN
553 FND_MESSAGE.set_name('CS','CS_SR_ASSOC_PARTYTYPE_MISMATCH');
554 FND_MESSAGE.set_token('API_NAME','cs_srcontact_pkg.validate_contact');
555 FND_MESSAGE.set_token('CONTACT_TYPE',l_party_type);
556 FND_MSG_PUB.add_detail
557 ( p_associated_column1=>'CS_HZ_SR_CONTACT_POINTS.PARTY_ID'
558 );
559 RAISE FND_API.G_EXC_ERROR;
560 END IF;
561 ELSE
562 -- This part means party role is not contact and contact type is not employee
563 -- so validate contact party and SR customer using relationship between them
564 IF p_caller_type = 'ORGANIZATION'
565 THEN
566 CS_ServiceRequest_UTIL.Validate_Org_Relationship
567 ( p_api_name => l_api_name_full,
568 p_parameter_name => 'p_party_id',
569 p_customer_contact_id => p_new_contact_rec.party_id,
570 p_customer_id => p_customer_id,
571 p_org_id => NULL,
572 x_return_status => x_return_status) ;
573 ELSE
574 CS_ServiceRequest_UTIL.Validate_Person_Relationship
575 ( p_api_name => l_api_name_full,
576 p_parameter_name => 'p_party_id',
577 p_customer_contact_id => p_new_contact_rec.party_id,
578 p_customer_id => p_customer_id,
579 p_org_id => NULL,
580 x_return_status => x_return_status) ;
581 END IF;
582 END IF; -- for party role_code condition
583 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
584 THEN
585 RAISE FND_API.G_EXC_ERROR;
586 END IF;
587 END IF; -- for change in contact pe or party id
588 --
589 -- Validate Contact point Type
590 --
591 IF (p_new_contact_rec.contact_point_type IS NOT NULL AND
592 p_new_contact_rec.contact_point_type <>
593 NVL(p_old_contact_rec.contact_point_type,'-9'))
594 THEN
595 CS_ServiceRequest_UTIL.validate_contact_point_type
596 ( p_api_name => l_api_name_full
597 , p_parameter_name => 'p_contact_point_type'
598 , p_contact_point_type => p_new_contact_rec.contact_point_type
599 , x_return_status => x_return_status
600 );
601 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
602 RAISE FND_API.G_EXC_ERROR;
603 END IF;
604 END IF;
605 --
606 -- Validate Contact Point Id
607 --
608 IF (p_new_contact_rec.contact_point_id IS NOT NULL AND
609 (p_new_contact_rec.contact_point_id <> NVL(p_old_contact_rec.contact_point_id,-9) OR
610 NVL(p_new_contact_rec.contact_point_type,'-9') <>
611 NVL(p_old_contact_rec.contact_point_type,'-9') ))
612 THEN
613 IF p_new_contact_rec.contact_type = 'EMPLOYEE'
614 THEN
615 IF (p_new_contact_rec.contact_point_type = 'PHONE') THEN
616 CS_ServiceRequest_UTIL.Validate_Emp_Contact_Point_Id
617 ( p_api_name => l_api_name_full
618 , p_parameter_name => 'p_contact_point_id'
619 , p_employee_id => p_new_contact_rec.party_id
620 , p_contact_point_id => p_new_contact_rec.contact_point_id
621 , x_return_status => x_return_status );
622 ELSIF (p_new_contact_rec.contact_point_type <> 'EMAIL') THEN
623 -- Added this logic for bug#2626855, if the contact_type is
624 -- Employee and the contact_point_type is other than email or
625 -- phone then give error else success.
626 CS_SERVICEREQUEST_UTIL.add_invalid_argument_msg
627 ( p_token_an => l_api_name_full
628 , p_token_v => p_new_contact_rec.contact_point_type
629 , p_token_p => 'p_contact_point_type'
630 , p_table_name => 'CS_HZ_SR_CONTACT_POINTS'
631 , p_column_name => 'CONTACT_POINT_TYPE'
632 );
633 x_return_status := FND_API.G_RET_STS_ERROR;
634 END IF;
635 ELSE -- Contact type is either PERSON or PARTY_RELATIONSHIP
636 CS_ServiceRequest_UTIL.Validate_Per_Contact_Point_Id
637 ( p_api_name => l_api_name_full
638 , p_parameter_name => 'p_contact_point_id'
639 , p_contact_point_type => p_new_contact_rec.contact_point_type
640 , p_contact_point_id => p_new_contact_rec.contact_point_id
641 , p_party_id => p_new_contact_rec.party_id
642 , x_return_status => x_return_status
643 );
644 END IF;
645 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
646 THEN
647 RAISE FND_API.G_EXC_ERROR;
648 END IF;
649 END IF; -- validate contact point id
650 --
651 -- validate Party role
652 --
653 IF p_new_contact_rec.party_role_code IS NOT NULL AND
654 p_new_contact_rec.party_role_code <> NVL(p_old_contact_rec.party_role_code,'#')
655 THEN
656 CS_SERVICEREQUEST_UTIL.validate_party_role_code
657 ( p_new_contact_rec.party_role_code
658 , x_return_status
659 );
660 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
661 THEN
662 RAISE FND_API.G_EXC_ERROR;
663 END IF;
664 END IF;
665 -- validate primary flag and party role combination
666 IF p_new_contact_rec.primary_flag = 'Y' AND
667 p_new_contact_rec.party_role_code <> 'CONTACT'
668 THEN
669 FND_MESSAGE.set_name('CS','CS_SR_PRIMARY_CONTACT_ROLE');
670 FND_MESSAGE.set_token('API_NAME','cs_srcontact_pkg.validate_contact');
671 FND_MSG_PUB.add_detail(p_associated_column1=>'CS_HZ_SR_CONTACT_POINTS.PRIMARY_FLAG');
672 RAISE FND_API.G_EXC_ERROR;
673 END IF;
674 --
675 IF p_new_contact_rec.primary_flag = 'Y' AND
676 p_new_contact_rec.end_date_active IS NOT NULL
677 THEN
678 FND_MESSAGE.set_name('CS','CS_SR_PRIMARY_END_DATED');
679 FND_MESSAGE.set_token('API_NAME','cs_srcontact_pkg.validate_contact');
680 FND_MSG_PUB.add_detail(p_associated_column1=>'CS_HZ_SR_CONTACT_POINTS.PRIMARY_FLAG');
681 RAISE FND_API.G_EXC_ERROR;
682 END IF;
683 --
684 IF p_new_contact_rec.primary_flag = 'Y' AND
685 p_new_contact_rec.start_date_active > l_today
686 THEN
687 FND_MESSAGE.set_name('CS','CS_SR_PRIMARY_FUTURE_DATED');
688 FND_MESSAGE.set_token('API_NAME','cs_srcontact_pkg.validate_contact');
689 FND_MSG_PUB.add_detail(p_associated_column1=>'CS_HZ_SR_CONTACT_POINTS.PRIMARY_FLAG');
690 RAISE FND_API.G_EXC_ERROR;
691 END IF;
692 -- validate Start and End date
693 --
694 IF p_new_contact_rec.end_date_active IS NOT NULL AND
695 p_new_contact_rec.start_date_active IS NOT NULL AND
696 p_new_contact_rec.end_date_active < p_new_contact_rec.start_date_active
697 THEN
698 FND_MESSAGE.set_name('CS','CS_SR_CP_ENDDT_LT_STARTDT');
699 FND_MESSAGE.set_token('API_NAME','cs_srcontact_pkg.validate_contact');
700 FND_MESSAGE.set_token('END_DT',to_char(p_new_contact_rec.end_date_active));
701 FND_MESSAGE.set_token('START_DT',to_char(p_new_contact_rec.start_date_active));
702 FND_MSG_PUB.add_detail(p_associated_column1=>'CS_HZ_SR_CONTACT_POINTS.END_DATE_ACITVE');
703 RAISE FND_API.G_EXC_ERROR;
704 END IF;
705
706 EXCEPTION
707 WHEN FND_API.G_EXC_ERROR THEN
708 x_return_status := FND_API.G_RET_STS_ERROR;
709 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
710 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
711 WHEN OTHERS THEN
712 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
713 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
714 FND_MSG_PUB.Add_Exc_Msg('aa', l_api_name_full);
715 END IF;
716 END validate_contact;
717 --------------------------------------------------------------------------------
718 -- Procedure Name : process_g_miss
719 -- Parameters :
720 -- IN : p_mode This can be CREATE or UPDATE. If mode is
721 -- not equal to CREATE then corresponding
722 -- record in database is access for replacing
723 -- g_miss with exisitng values.
724 -- p_new_contact_rec This record contains contact record passed
725 -- to service request API
726 -- x_new_contact_rec p_new_contact_rec with g_miss values
727 -- replaced by either NULL or from database
728 -- p_old_contact_rec This record containt value of contact
729 -- record being update. in case of insert
730 -- this record in NULL
731 -- OUT : x_return_status Indicates success or Error condition
732 -- encountered by procedure.
733 --
734 -- Description : This procedure check new contact record and if any value if
735 -- missing then it is set to it's value in old contact record.
736 --
737 -- Modification History:
738 -- Date Name Desc
739 -- -------- -------- -----------------------------------------------------------
740 -- 04/15/05 smisra Created
741 -- 08/10/05 smisra Defaulted party role code to contact if it is NULL
742 -- 10/21/05 smisra Bug 4074184
743 -- prepared a list of contact points being updated and
744 -- partial where clause for updated contact points
745 -- Truncated active start and end dates
746 --
747 -- 11/03/06 spusegao Modified Process_GMISS procedure to CLOSE the cursor immediately after
748 -- FETCH to resolve the issue described in bug 5629281.
749 -- (ORA-06511: PL/SQL: cursor already open)
750 --
751 -- -----------------------------------------------------------------------------
752 PROCEDURE process_g_miss
753 ( p_mode IN VARCHAR2
754 , p_incident_id IN NUMBER
755 , p_new_contact_tbl IN CS_SERVICEREQUEST_PVT.contacts_table
756 , x_new_contact_tbl OUT NOCOPY CS_SERVICEREQUEST_PVT.contacts_table
757 , x_old_contact_tbl OUT NOCOPY CS_SERVICEREQUEST_PVT.contacts_table
758 , x_updated_cp_qry OUT NOCOPY VARCHAR2
759 , x_updated_cp_bind OUT NOCOPY NUM_TBL
760 , x_return_status OUT NOCOPY VARCHAR2
761 ) IS
762 l_incident_id CS_INCIDENTS_ALL_B.incident_id % type;
763 l_updated_cp_index NUMBER;
764 --
765 CURSOR c_sr_contact (p_sr_contact_point_id NUMBER) IS
766 SELECT
767 sr_contact_point_id
768 , party_id
769 , contact_point_id
770 , contact_point_type
771 , contact_type
772 , primary_flag
773 , party_role_code
774 , start_date_active
775 , end_date_active
776 , incident_id
777 FROM
778 cs_hz_sr_contact_points
779 WHERE sr_contact_point_id = p_sr_contact_point_id;
780 BEGIN
781 x_new_contact_tbl := p_new_contact_tbl;
782 x_return_status := FND_API.G_RET_STS_SUCCESS;
783 x_updated_cp_qry := ':1';
784 l_updated_cp_index := 1;
785 x_updated_cp_bind(l_updated_cp_index) := -1;
786 FOR i in x_new_contact_tbl.FIRST..x_new_contact_tbl.LAST LOOP
787 x_old_contact_tbl(i) := NULL;
788 IF x_new_contact_tbl(i).sr_contact_point_id IS NOT NULL AND
789 x_new_contact_tbl(i).sr_contact_point_id <> FND_API.G_MISS_NUM
790 -- so that in update mode, old record is accessed
791 -- if caller passed misspelled p_mode, no harm is done. only record
792 -- will be accessed and nothing will be found
793 THEN
794 OPEN c_sr_contact(x_new_contact_tbl(i).sr_contact_point_id);
795 FETCH c_sr_contact
796 INTO
797 x_old_contact_tbl(i).sr_contact_point_id
798 , x_old_contact_tbl(i).party_id
799 , x_old_contact_tbl(i).contact_point_id
800 , x_old_contact_tbl(i).contact_point_type
801 , x_old_contact_tbl(i).contact_type
802 , x_old_contact_tbl(i).primary_flag
803 , x_old_contact_tbl(i).party_role_code
804 , x_old_contact_tbl(i).start_date_active
805 , x_old_contact_tbl(i).end_date_active
806 , l_incident_id;
807
808 CLOSE c_sr_contact;
809
810 -- IF c_sr_contact%FOUND -- Coommented to resolve bug # 5629281.
811 IF x_old_contact_tbl(i).sr_contact_point_id IS NOT NULL
812 THEN
813 -- in case of insert, p_incident_id will be null
814 IF NVL(p_incident_id,-1) <> l_incident_id
815 THEN
816 FND_MESSAGE.set_name('CS', 'CS_SR_CP_DO_NOT_BELONGS');
817 FND_MESSAGE.set_token
818 ( 'CP_ID'
819 , TO_CHAR(x_new_contact_tbl(i).sr_contact_point_id)
820 );
821 FND_MESSAGE.set_token
822 ( 'INC_ID'
823 , TO_CHAR(p_incident_id)
824 );
825 FND_MSG_PUB.ADD_DETAIL(P_ASSOCIATED_COLUMN1=>'CS_HZ_SR_CONTACT_POINTS.SR_CONTACT_POINT_ID');
826 x_return_status := FND_API.G_RET_STS_ERROR;
827 EXIT;
828 END IF;
829 l_updated_cp_index := l_updated_cp_index + 1;
830 x_updated_cp_qry :=
831 x_updated_cp_qry || ', :' || to_char(l_updated_cp_index);
832 x_updated_cp_bind(l_updated_cp_index) := x_old_contact_tbl(i).sr_contact_point_id;
833 -- CLOSE c_sr_contact; -- Coommented to resolve bug # 5629281.
834 END IF;
835 END IF;
836 IF x_new_contact_tbl(i).party_id = FND_API.G_MISS_NUM
837 THEN
838 x_new_contact_tbl(i).party_id := x_old_contact_tbl(i).party_id;
839 END IF;
840 IF x_new_contact_tbl(i).contact_type = FND_API.G_MISS_CHAR
841 THEN
842 x_new_contact_tbl(i).contact_type := x_old_contact_tbl(i).contact_type;
843 END IF;
844 IF x_new_contact_tbl(i).primary_flag = FND_API.G_MISS_CHAR
845 THEN
846 x_new_contact_tbl(i).primary_flag := x_old_contact_tbl(i).primary_flag;
847 END IF;
848 IF x_new_contact_tbl(i).contact_point_id = FND_API.G_MISS_NUM
849 THEN
850 x_new_contact_tbl(i).contact_point_id := x_old_contact_tbl(i).contact_point_id;
851 END IF;
852 IF x_new_contact_tbl(i).contact_point_type = FND_API.G_MISS_CHAR
853 THEN
854 x_new_contact_tbl(i).contact_point_type := x_old_contact_tbl(i).contact_point_type;
855 END IF;
856 IF x_new_contact_tbl(i).sr_contact_point_id = FND_API.G_MISS_NUM
857 THEN
858 x_new_contact_tbl(i).sr_contact_point_id:= x_old_contact_tbl(i).sr_contact_point_id;
859 END IF;
860 IF x_new_contact_tbl(i).party_role_code = FND_API.G_MISS_CHAR
861 THEN
862 x_new_contact_tbl(i).party_role_code := x_old_contact_tbl(i).party_role_code;
863 END IF;
864 IF x_new_contact_tbl(i).party_role_code IS NULL
865 THEN
866 x_new_contact_tbl(i).party_role_code := 'CONTACT';
867 END IF;
868 IF x_new_contact_tbl(i).start_date_active = FND_API.G_MISS_DATE
869 THEN
870 x_new_contact_tbl(i).start_date_active := x_old_contact_tbl(i).start_date_active;
871 END IF;
872 IF x_new_contact_tbl(i).end_date_active = FND_API.G_MISS_DATE
873 THEN
874 x_new_contact_tbl(i).end_date_active := x_old_contact_tbl(i).end_date_active;
875 END IF;
876 x_new_contact_tbl(i).start_date_active := TRUNC(x_new_contact_tbl(i).start_date_active);
877 x_new_contact_tbl(i).end_date_active := TRUNC(x_new_contact_tbl(i).end_date_active);
878 END LOOP;
879 END process_g_miss;
880 --------------------------------------------------------------------------------
881 -- Procedure Name :
882 -- Parameters :
883 -- IN :
884 -- OUT :
885 --
886 -- Description :
887 --
888 -- Modification History:
889 -- Date Name Desc
890 -------- -------- --------------------------------------------------------------
891 -- 04/15/05 smisra Created
892 --------------------------------------------------------------------------------
893 FUNCTION new_primary
894 ( p_new_contact_tbl IN CS_SERVICEREQUEST_PVT.contacts_table
895 , p_old_contact_tbl IN CS_SERVICEREQUEST_PVT.contacts_table
896 , x_return_status OUT NOCOPY VARCHAR2
897 ) RETURN NUMBER IS
898 BEGIN
899 FOR loop_index in p_new_contact_tbl.FIRST..p_new_contact_tbl.LAST
900 LOOP
901 IF p_new_contact_tbl(loop_index).primary_flag = 'Y'
902 THEN
903 -- if new contact is called primary but old record does not call
904 -- it primary then there must be other primary contact in database
905 IF NVL(p_old_contact_tbl(loop_index).primary_flag,'N') <> 'Y'
906 THEN
907 RETURN 'Y';
908 END IF;
909 END IF;
910 END LOOP;
911 RETURN 'N';
912 END new_primary;
913 --
914 --------------------------------------------------------------------------------
915 -- Procedure Name :
916 -- Parameters :
917 -- IN :
918 -- OUT :
919 --
920 -- Description :
921 --
922 -- Modification History:
923 -- Date Name Desc
924 -------- -------- --------------------------------------------------------------
925 -- 04/15/05 smisra Created
926 -- 10/05/05 smisra Change Request : 4645490
927 -- Called create_cp_audit only if profile to audit contact
928 -- points is enabled.
929 -- Removed party_id, contact_type and party_role_code from
930 -- update statement as these attributes can not be updated.
931 --------------------------------------------------------------------------------
932 PROCEDURE create_update
933 ( p_incident_id IN NUMBER
934 , p_invocation_mode IN VARCHAR2
935 , p_sr_update_date IN DATE
936 , p_sr_updated_by IN VARCHAR2
937 , p_sr_update_login IN VARCHAR2
938 , p_contact_tbl IN CS_SERVICEREQUEST_PVT.contacts_table
939 , p_old_contact_tbl IN CS_SERVICEREQUEST_PVT.contacts_table
940 , x_return_status OUT NOCOPY VARCHAR2
941 ) IS
942 --
943 l_audit_id NUMBER;
944 l_msg_data VARCHAR2(2000);
945 l_msg_count NUMBER;
946 l_activity_code VARCHAR2(1);
947 l_sr_contact_point_id NUMBER;
948 l_sysdate DATE;
949 l_add_audit VARCHAR2(1);
950 l_audit_enabled fnd_profile_option_values.profile_option_value % TYPE;
951 --
952 BEGIN
953 l_audit_enabled := FND_PROFILE.value('CS_SR_CONT_PNT_AUDIT_ENABLED');
954 IF p_contact_tbl.COUNT = 0
955 THEN
956 x_return_status := FND_API.G_RET_STS_SUCCESS;
957 RETURN;
958 END IF;
959
960 l_sysdate := sysdate;
961 FOR loop_index in p_contact_tbl.FIRST..p_contact_tbl.LAST LOOP
962 l_add_audit := 'N';
963 IF p_old_contact_tbl(loop_index).sr_contact_point_id is NULL
964 THEN
965 SELECT cs_hz_sr_contact_points_s.NEXTVAL
966 INTO l_sr_contact_point_id
967 FROM DUAL;
968 l_activity_code := 'C';
969 --
970 l_add_audit := 'Y';
971 INSERT INTO cs_hz_sr_contact_points
972 ( sr_contact_point_id
973 , party_id
974 , incident_id
975 , contact_point_type
976 , contact_type
977 , contact_point_id
978 , primary_flag
979 , party_role_code
980 , start_date_active
981 , end_date_active
982 , creation_date
983 , created_by
984 , last_update_date
985 , last_updated_by
986 , last_update_login
987 , object_version_number)
988 VALUES
989 ( l_sr_contact_point_id
990 , p_contact_tbl(loop_index).party_id
991 , p_incident_id
992 , p_contact_tbl(loop_index).contact_point_type
993 , p_contact_tbl(loop_index).contact_type
994 , p_contact_tbl(loop_index).contact_point_id
995 , p_contact_tbl(loop_index).primary_flag
996 , NVL(p_contact_tbl(loop_index).party_role_code,'CONTACT')
997 , p_contact_tbl(loop_index).start_date_active
998 , p_contact_tbl(loop_index).end_date_active
999 , p_sr_update_date
1000 , p_sr_updated_by
1001 , p_sr_update_date
1002 , p_sr_updated_by
1003 , p_sr_update_login
1004 , 1 );
1005 -- Else statement does not compare party_id, contact_type nd party role because
1006 -- these can not be updated.
1007 ELSIF (NVL(p_contact_tbl(loop_index).contact_point_type,'xz') <>
1008 NVL(p_old_contact_tbl(loop_index).contact_point_type,'xz') OR
1009 NVL(p_contact_tbl(loop_index).contact_point_id ,-99) <>
1010 NVL(p_old_contact_tbl(loop_index).contact_point_id,-99) OR
1011 NVL(p_contact_tbl(loop_index).start_date_active,l_sysdate) <>
1012 NVL(p_old_contact_tbl(loop_index).start_date_active,l_sysdate) OR
1013 NVL(p_contact_tbl(loop_index).end_date_active,l_sysdate) <>
1014 NVL(p_old_contact_tbl(loop_index).end_date_active,l_sysdate) OR
1015 NVL(p_contact_tbl(loop_index).primary_flag,'N') <>
1016 NVL(p_old_contact_tbl(loop_index).primary_flag,'N') )
1017 THEN
1018 l_add_audit := 'Y';
1019 l_activity_code := 'U';
1020 l_sr_contact_point_id := p_contact_tbl(loop_index).sr_contact_point_id;
1021 -- update statement does not include party id, contact type and party role code because
1022 -- these attributes can not be updated.
1023 UPDATE cs_hz_sr_contact_points
1024 SET primary_flag = p_contact_tbl(loop_index).primary_flag
1025 , contact_point_id = p_contact_tbl(loop_index).contact_point_id
1026 , contact_point_type = p_contact_tbl(loop_index).contact_point_type
1027 , start_date_active = p_contact_tbl(loop_index).start_date_active
1028 , end_date_active = p_contact_tbl(loop_index).end_date_active
1029 , last_updated_by = p_sr_updated_by
1030 , last_update_date = p_sr_update_date
1031 , last_update_login = p_sr_update_login
1032 , object_version_number = object_version_number+1
1033 WHERE sr_contact_point_id = l_sr_contact_point_id;
1034 END IF;
1035 --- Create Child audit
1036 IF l_add_audit = 'Y'
1037 THEN
1038 CS_SR_CHILD_AUDIT_PKG.CS_SR_AUDIT_CHILD
1039 ( p_incident_id => p_incident_id
1040 , p_updated_entity_code => 'SR_CONTACT_POINT'
1041 , p_updated_entity_id => l_sr_contact_point_id
1042 , p_entity_update_date => p_sr_update_date
1043 , p_entity_activity_code => l_activity_code
1044 , x_audit_id => l_audit_id
1045 , x_return_status => x_return_status
1046 , x_msg_count => l_msg_count
1047 , x_msg_data => l_msg_data
1048 );
1049 IF l_audit_enabled = 'Y'
1050 THEN
1051 create_cp_audit
1052 ( p_sr_contact_point_id => l_sr_contact_point_id
1053 , p_incident_id => p_incident_id
1054 , p_new_cp_rec => p_contact_tbl(loop_index)
1055 , p_old_cp_rec => p_old_contact_tbl(loop_index)
1056 , p_cp_modified_by => p_sr_updated_by
1057 , p_cp_modified_on => p_sr_update_date
1058 , x_return_status => x_return_status
1059 , x_msg_count => l_msg_count
1060 , x_msg_data => l_msg_data
1061 );
1062 END IF;
1063 END IF;
1064 /*
1065 */
1066 END LOOP;
1067 END CREATE_UPDATE;
1068 -- -----------------------------------------------------------------------------
1069 -- Procedure Name :
1070 -- Parameters :
1071 -- IN :
1072 -- OUT :
1073 --
1074 -- Description :
1075 --
1076 -- Modification History:
1077 -- Date Name Desc
1078 -- -------- -------- -----------------------------------------------------------
1079 -- 04/15/05 smisra Created
1080 -- 10/06/05 smisra Added audit of contact point record if primary flag is
1081 -- set to N
1082 -- -----------------------------------------------------------------------------
1083 PROCEDURE reset_primary_flag
1084 ( p_incident_id IN NUMBER
1085 , p_sr_contact_point_id IN NUMBER
1086 , x_return_status OUT NOCOPY VARCHAR2
1087 ) IS
1088 l_new_cp_rec CS_SERVICEREQUEST_PVT.contacts_rec;
1089 l_old_cp_rec CS_SERVICEREQUEST_PVT.contacts_rec;
1090 l_msg_count NUMBER;
1091 l_msg_data VARCHAR2(4000);
1092 l_audit_enabled fnd_profile_option_values.profile_option_value % TYPE;
1093 BEGIN
1094 l_audit_enabled := FND_PROFILE.value('CS_SR_CONT_PNT_AUDIT_ENABLED');
1095 UPDATE cs_hz_sr_contact_points
1096 SET primary_flag = 'N'
1097 WHERE incident_id = p_incident_id
1098 AND primary_flag = 'Y'
1099 AND sr_contact_point_id <> NVL(p_sr_contact_point_id,-9)
1100 RETURNING
1101 sr_contact_point_id ,
1102 party_id ,
1103 contact_point_id ,
1104 primary_flag ,
1105 contact_point_type ,
1106 contact_type ,
1107 party_role_code ,
1108 start_date_active ,
1109 end_date_active
1110 INTO l_new_cp_rec
1111 ;
1112 IF SQL%FOUND AND
1113 l_audit_enabled = 'Y'
1114 THEN
1115 l_old_cp_rec := l_new_cp_rec;
1116 l_old_cp_rec.primary_flag := 'Y';
1117 create_cp_audit
1118 ( p_sr_contact_point_id => l_new_cp_rec.sr_contact_point_id
1119 , p_incident_id => p_incident_id
1120 , p_new_cp_rec => l_new_cp_rec
1121 , p_old_cp_rec => l_old_cp_rec
1122 , p_cp_modified_by => NULL
1123 , p_cp_modified_on => NULL
1124 , x_return_status => x_return_status
1125 , x_msg_count => l_msg_count
1126 , x_msg_data => l_msg_data
1127 );
1128 END IF;
1129 EXCEPTION
1130 WHEN OTHERS
1131 THEN
1132 NULL;
1133 END reset_primary_flag;
1134 -- -----------------------------------------------------------------------------
1135 -- Procedure Name :
1136 -- Parameters :
1137 -- IN :
1138 -- OUT :
1139 --
1140 -- Description :
1141 --
1142 -- Modification History:
1143 -- Date Name Desc
1144 -- -------- -------- -----------------------------------------------------------
1145 -- 04/15/05 smisra Created
1146 -- 10/06/05 smisra Modified this procedure so that primary contact checks are
1147 -- performed only if contact table has atleat one contact
1148 -- point record with role as CONTACT
1149 -- 10/21/05 smisra Called check_duplicates to fix bug 4074184
1150 -- 10/25/05 smisra Passed p_mode to check_duplicates procedure
1151 -- -----------------------------------------------------------------------------
1152 PROCEDURE process
1153 ( p_mode IN VARCHAR2
1154 , p_incident_id IN NUMBER
1155 , p_caller_type IN VARCHAR2
1156 , p_customer_id IN NUMBER
1157 , p_validation_mode IN NUMBER
1158 , p_contact_tbl IN CS_SERVICEREQUEST_PVT.contacts_table
1159 , x_contact_tbl OUT NOCOPY CS_SERVICEREQUEST_PVT.contacts_table
1160 , x_old_contact_tbl OUT NOCOPY CS_SERVICEREQUEST_PVT.contacts_table
1161 , x_primary_party_id OUT NOCOPY NUMBER
1162 , x_primary_contact_point_id OUT NOCOPY NUMBER
1163 , x_return_status OUT NOCOPY VARCHAR2
1164 ) IS
1165 --
1166 l_primary_found number;
1167 l_new_contact_tbl CS_SERVICEREQUEST_PVT.contacts_table;
1168 l_old_contact_tbl CS_SERVICEREQUEST_PVT.contacts_table;
1169 l_api_name_full VARCHAR2(61);
1170 l_existing_primary NUMBER;
1171 l_contact_party_role_found NUMBER := 0;
1172 l_updated_cp_qry VARCHAR2(4000);
1173 l_updated_cp_bind NUM_TBL;
1174
1175 CURSOR c_primary_count IS
1176 SELECT
1177 party_id
1178 , DECODE(contact_point_type,'PHONE',contact_point_id,NULL)
1179 FROM
1180 cs_hz_sr_contact_points
1181 WHERE incident_id = p_incident_id
1182 AND primary_flag = 'Y';
1183 l_sr_contact_point_id_pri NUMBER;
1184 l_today DATE;
1185 --
1186 BEGIN
1187 l_api_name_full := 'CS_SRCONTACT_PKG.process';
1188 x_return_Status := FND_API.G_RET_STS_SUCCESS;
1189 l_today := TRUNC(SYSDATE);
1190 l_primary_found := 0;
1191 IF p_contact_tbl.COUNT = 0
1192 THEN
1193 RETURN;
1194 END IF;
1195 process_g_miss
1196 ( p_mode => p_mode
1197 , p_incident_id => p_incident_id
1198 , p_new_contact_tbl => p_contact_tbl
1199 , x_new_contact_tbl => l_new_contact_tbl
1200 , x_old_contact_tbl => l_old_contact_tbl
1201 , x_updated_cp_qry => l_updated_cp_qry
1202 , x_updated_cp_bind => l_updated_cp_bind
1203 , x_return_status => x_return_status
1204 );
1205 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
1206 THEN
1207 RAISE FND_API.G_EXC_ERROR;
1208 END IF;
1209 --
1210 -- Check for primary if contacts are being passed
1211 --
1212 FOR loop_index in l_new_contact_tbl.FIRST..l_new_contact_tbl.LAST LOOP
1213 IF NVL(l_new_contact_tbl(loop_index).party_role_code,'CONTACT') = 'CONTACT' AND
1214 NVL(l_new_contact_tbl(loop_index).end_date_active,l_today) >= l_today
1215 THEN
1216 l_contact_party_role_found := l_contact_party_role_found + 1 ;
1217 END IF ;
1218
1219 IF l_new_contact_tbl(loop_index).primary_flag = 'Y'
1220 THEN
1221 l_primary_found := l_primary_found + 1;
1222 x_primary_party_id := l_new_contact_tbl(loop_index).party_id;
1223 l_sr_contact_point_id_pri := l_new_contact_tbl(loop_index).sr_contact_point_id;
1224 IF l_new_contact_tbl(loop_index).contact_point_type = 'PHONE'
1225 THEN
1226 x_primary_contact_point_id := l_new_contact_tbl(loop_index).contact_point_id;
1227 END IF;
1228 END IF;
1229 END LOOP;
1230
1231 -- if any contact party is found only then we need to check
1232 -- for single primary contact
1233 IF l_contact_party_role_found > 0
1234 THEN
1235 IF l_primary_found >= 2
1236 THEN
1237 CS_ServiceRequest_UTIL.Add_Invalid_Argument_Msg
1238 ( p_token_an => l_api_name_full
1239 , p_token_v => 'Y'
1240 , p_token_p => 'p_contacts.primary_flag'
1241 , p_table_name => 'CS_HZ_SR_CONTACT_POINTS'
1242 , p_column_name => 'PRIMARY_FLAG'
1243 );
1244 RAISE FND_API.G_EXC_ERROR;
1245 ELSIF l_primary_found = 0
1246 THEN
1247 l_existing_primary := 0;
1248 IF p_mode <> 'CREATE'
1249 THEN
1250 OPEN c_primary_count;
1251 FETCH c_primary_count
1252 INTO x_primary_party_id, x_primary_contact_point_id;
1253 IF c_primary_count % FOUND
1254 THEN
1255 l_existing_primary := 1;
1256 END IF;
1257 CLOSE c_primary_count;
1258 END IF;
1259 IF l_existing_primary = 0
1260 THEN
1261 CS_SERVICEREQUEST_UTIL.add_null_parameter_msg
1262 ( p_token_an => l_api_name_full
1263 , p_token_np => 'Primary Contact Information'
1264 );
1265 RAISE FND_API.G_EXC_ERROR;
1266 END IF;
1267 ELSE -- means primary contact count is exactly 1. in case of update mode
1268 -- existing primary cantact should be made non primary.
1269 IF p_mode <> 'CREATE'
1270 THEN
1271 IF l_sr_contact_point_id_pri = FND_API.G_MISS_NUM
1272 THEN
1273 l_sr_contact_point_id_pri := NULL;
1274 END IF;
1275 reset_primary_flag(p_incident_id, l_sr_contact_point_id_pri, x_return_status);
1276 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
1277 THEN
1278 RAISE FND_API.G_EXC_ERROR;
1279 END IF;
1280 END IF;
1281 END IF;
1282 END IF; -- l_contact_party_role_found
1283 --
1284 -- Now Validate contact records
1285 --
1286 IF p_validation_mode > FND_API.G_VALID_LEVEL_NONE
1287 THEN
1288 FOR loop_index in l_new_contact_tbl.FIRST..l_new_contact_tbl.LAST LOOP
1289 IF l_primary_found = 0 AND
1290 l_old_contact_tbl(loop_index).primary_flag = 'Y' AND -- if it is null, no execution
1291 NVL(l_new_contact_tbl(loop_index).primary_flag,'N') <> 'Y'
1292 THEN
1293 IF NVL(l_new_contact_tbl(loop_index).end_date_active, l_today) >= l_today OR
1294 contact_role_count(p_incident_id) > 1
1295 THEN
1296 FND_MESSAGE.set_name('CS', 'CS_SR_NO_PRIMARY_LEFT');
1297 FND_MESSAGE.set_token ('API_NAME','cs_srcontact_pkg.process');
1298 FND_MESSAGE.set_token
1299 ( 'INC_ID'
1300 , TO_CHAR(l_new_contact_tbl(loop_index).sr_contact_point_id)
1301 );
1302 FND_MSG_PUB.ADD_DETAIL(P_ASSOCIATED_COLUMN1=>'CS_HZ_SR_CONTACT_POINTS.PRIMARY_FLAG');
1303 RAISE FND_API.G_EXC_ERROR;
1304 END IF;
1305 END IF;
1306 validate_contact
1307 ( p_caller_type => p_caller_type
1308 , p_customer_id => p_customer_id
1309 , p_new_contact_rec => l_new_contact_tbl(loop_index)
1310 , p_old_contact_rec => l_old_contact_tbl(loop_index)
1311 , x_return_status => x_return_status
1312 );
1313 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
1314 THEN
1315 RAISE FND_API.G_EXC_ERROR;
1316 END IF;
1317 END LOOP;
1318 --
1319 -- Check for duplicate contact point Records
1320 --
1321 check_duplicates
1322 ( p_mode => p_mode
1323 , p_new_contact_tbl => l_new_contact_tbl
1324 , p_updated_cp_qry => l_updated_cp_qry
1325 , p_updated_cp_bind => l_updated_cp_bind
1326 , p_incident_id => p_incident_id
1327 , x_return_status => x_return_status
1328 );
1329 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
1330 THEN
1331 RAISE FND_API.G_EXC_ERROR;
1332 END IF;
1333 END IF; -- check for validation level
1334 x_contact_tbl := l_new_contact_tbl;
1335 x_old_contact_tbl := l_old_contact_tbl;
1336 EXCEPTION
1337 WHEN FND_API.G_EXC_ERROR THEN
1338 x_return_status := FND_API.G_RET_STS_ERROR;
1339 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1340 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1341 WHEN OTHERS THEN
1342 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1343 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1344 FND_MSG_PUB.Add_Exc_Msg('aa', l_api_name_full);
1345 END IF;
1346 END process;
1347
1348 --------------------------------------------------------------------------------
1349 -- Procedure Name : populate_cp_audit_rec
1350 -- Parameters :
1351 -- IN : p_sr_contact_point_id - Contact point identifier.
1352 -- OUT : x_cp_contact_rec This is a populated audit record.
1353 -- : x_return_status Indicates success or Error condition
1354 -- encountered by procedure.
1355 -- x_msg_count
1356 -- x_msg_data
1357 --
1358 -- Modification History:
1359 -- Date Name Desc
1360 -------- -------- --------------------------------------------------------------
1361 -- 11/23/2005 spusegao created
1362 --------------------------------------------------------------------------------
1363
1364 PROCEDURE Populate_CP_Audit_Rec
1365 (p_sr_contact_point_id IN NUMBER,
1366 x_sr_contact_rec OUT NOCOPY CS_SERVICEREQUEST_PVT.CONTACTS_REC,
1367 x_return_status OUT NOCOPY VARCHAR2,
1368 x_msg_count OUT NOCOPY NUMBER,
1369 x_msg_data OUT NOCOPY VARCHAR2) IS
1370
1371 --Local Variabled
1372
1373
1374 -- Get contact point details.
1375 CURSOR c_get_cp_details IS
1376 SELECT *
1377 FROM cs_hz_sr_contact_points
1378 WHERE sr_contact_point_id = p_sr_contact_point_id ;
1379
1380 BEGIN
1381 -- Get contact point details for the passed contact point identifier.
1382 FOR c_get_cp_details_rec IN c_get_cp_details
1383 LOOP
1384 x_sr_contact_rec.sr_contact_point_id := c_get_cp_details_rec.sr_contact_point_id;
1385 x_sr_contact_rec.party_id := c_get_cp_details_rec.party_id;
1386 x_sr_contact_rec.contact_point_id := c_get_cp_details_rec.contact_point_id;
1387 x_sr_contact_rec.primary_flag := c_get_cp_details_rec.primary_flag;
1388 x_sr_contact_rec.contact_point_type := c_get_cp_details_rec.contact_point_type;
1389 x_sr_contact_rec.contact_type := c_get_cp_details_rec.contact_type;
1390 x_sr_contact_rec.party_role_code := c_get_cp_details_rec.party_role_code;
1391 x_sr_contact_rec.start_date_active := c_get_cp_details_rec.start_date_active;
1392 x_sr_contact_rec.end_date_active := c_get_cp_details_rec.end_date_active;
1393 END LOOP;
1394
1395 EXCEPTION
1396 WHEN OTHERS THEN
1397 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1398 FND_MSG_PUB.Count_And_Get
1399 ( p_count => x_msg_count,
1400 p_data => x_msg_data);
1401 RAISE;
1402 END Populate_CP_Audit_Rec;
1403
1404 END;
[Home] [Help]