1 Package BODY CS_SRCONTACT_PKG AS
2 /* $Header: cssrcpb.pls 120.9.12010000.4 2010/05/14 22:20:58 siahmed ship $*/
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 --Added by Lakshmi for 12.1.2 project,Added another 'AND' condition ,
675 --To throw an error is a primary contact is endated and Profile 'CS_SR_CONTACT_MANDATORY' is set to Yes.
676 IF p_new_contact_rec.primary_flag = 'Y' AND
677 p_new_contact_rec.end_date_active IS NOT NULL AND
678 FND_PROFILE.value('CS_SR_CONTACT_MANDATORY') = 'Y'
679 THEN
680 FND_MESSAGE.set_name('CS','CS_SR_PRIMARY_END_DATED');
681 FND_MESSAGE.set_token('API_NAME','cs_srcontact_pkg.validate_contact');
682 FND_MSG_PUB.add_detail(p_associated_column1=>'CS_HZ_SR_CONTACT_POINTS.PRIMARY_FLAG');
683 RAISE FND_API.G_EXC_ERROR;
684 END IF;
685 --
686 IF p_new_contact_rec.primary_flag = 'Y' AND
687 p_new_contact_rec.start_date_active > l_today
688 THEN
689 FND_MESSAGE.set_name('CS','CS_SR_PRIMARY_FUTURE_DATED');
690 FND_MESSAGE.set_token('API_NAME','cs_srcontact_pkg.validate_contact');
691 FND_MSG_PUB.add_detail(p_associated_column1=>'CS_HZ_SR_CONTACT_POINTS.PRIMARY_FLAG');
692 RAISE FND_API.G_EXC_ERROR;
693 END IF;
694 -- validate Start and End date
695 --
696 IF p_new_contact_rec.end_date_active IS NOT NULL AND
697 p_new_contact_rec.start_date_active IS NOT NULL AND
698 p_new_contact_rec.end_date_active < p_new_contact_rec.start_date_active
699 THEN
700 FND_MESSAGE.set_name('CS','CS_SR_CP_ENDDT_LT_STARTDT');
701 FND_MESSAGE.set_token('API_NAME','cs_srcontact_pkg.validate_contact');
702 FND_MESSAGE.set_token('END_DT',to_char(p_new_contact_rec.end_date_active));
703 FND_MESSAGE.set_token('START_DT',to_char(p_new_contact_rec.start_date_active));
704 FND_MSG_PUB.add_detail(p_associated_column1=>'CS_HZ_SR_CONTACT_POINTS.END_DATE_ACITVE');
705 RAISE FND_API.G_EXC_ERROR;
706 END IF;
707
708 EXCEPTION
709 WHEN FND_API.G_EXC_ERROR THEN
710 x_return_status := FND_API.G_RET_STS_ERROR;
711 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
712 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
713 WHEN OTHERS THEN
714 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
715 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
716 FND_MSG_PUB.Add_Exc_Msg('aa', l_api_name_full);
717 END IF;
718 END validate_contact;
719 --------------------------------------------------------------------------------
720 -- Procedure Name : process_g_miss
721 -- Parameters :
722 -- IN : p_mode This can be CREATE or UPDATE. If mode is
723 -- not equal to CREATE then corresponding
724 -- record in database is access for replacing
725 -- g_miss with exisitng values.
726 -- p_new_contact_rec This record contains contact record passed
727 -- to service request API
728 -- x_new_contact_rec p_new_contact_rec with g_miss values
729 -- replaced by either NULL or from database
730 -- p_old_contact_rec This record containt value of contact
731 -- record being update. in case of insert
732 -- this record in NULL
733 -- OUT : x_return_status Indicates success or Error condition
734 -- encountered by procedure.
735 --
736 -- Description : This procedure check new contact record and if any value if
737 -- missing then it is set to it's value in old contact record.
738 --
739 -- Modification History:
740 -- Date Name Desc
741 -- -------- -------- -----------------------------------------------------------
742 -- 04/15/05 smisra Created
743 -- 08/10/05 smisra Defaulted party role code to contact if it is NULL
744 -- 10/21/05 smisra Bug 4074184
745 -- prepared a list of contact points being updated and
746 -- partial where clause for updated contact points
747 -- Truncated active start and end dates
748 --
749 -- 11/03/06 spusegao Modified Process_GMISS procedure to CLOSE the cursor immediately after
750 -- FETCH to resolve the issue described in bug 5629281.
751 -- (ORA-06511: PL/SQL: cursor already open)
752 --
753 -- -----------------------------------------------------------------------------
754 PROCEDURE process_g_miss
755 ( p_mode IN VARCHAR2
756 , p_incident_id IN NUMBER
757 , p_new_contact_tbl IN CS_SERVICEREQUEST_PVT.contacts_table
758 , x_new_contact_tbl OUT NOCOPY CS_SERVICEREQUEST_PVT.contacts_table
759 , x_old_contact_tbl OUT NOCOPY CS_SERVICEREQUEST_PVT.contacts_table
760 , x_updated_cp_qry OUT NOCOPY VARCHAR2
761 , x_updated_cp_bind OUT NOCOPY NUM_TBL
762 , x_return_status OUT NOCOPY VARCHAR2
763 ) IS
764 l_incident_id CS_INCIDENTS_ALL_B.incident_id % type;
765 l_updated_cp_index NUMBER;
766 --
767 CURSOR c_sr_contact (p_sr_contact_point_id NUMBER) IS
768 SELECT
769 sr_contact_point_id
770 , party_id
771 , contact_point_id
772 , contact_point_type
773 , contact_type
774 , primary_flag
775 , party_role_code
776 , start_date_active
777 , end_date_active
778 , incident_id
779 FROM
780 cs_hz_sr_contact_points
781 WHERE sr_contact_point_id = p_sr_contact_point_id;
782 BEGIN
783 x_new_contact_tbl := p_new_contact_tbl;
784 x_return_status := FND_API.G_RET_STS_SUCCESS;
785 x_updated_cp_qry := ':1';
786 l_updated_cp_index := 1;
787 x_updated_cp_bind(l_updated_cp_index) := -1;
788 FOR i in x_new_contact_tbl.FIRST..x_new_contact_tbl.LAST LOOP
789 x_old_contact_tbl(i) := NULL;
790 IF x_new_contact_tbl(i).sr_contact_point_id IS NOT NULL AND
791 x_new_contact_tbl(i).sr_contact_point_id <> FND_API.G_MISS_NUM
792 -- so that in update mode, old record is accessed
793 -- if caller passed misspelled p_mode, no harm is done. only record
794 -- will be accessed and nothing will be found
795 THEN
796 OPEN c_sr_contact(x_new_contact_tbl(i).sr_contact_point_id);
797 FETCH c_sr_contact
798 INTO
799 x_old_contact_tbl(i).sr_contact_point_id
800 , x_old_contact_tbl(i).party_id
801 , x_old_contact_tbl(i).contact_point_id
802 , x_old_contact_tbl(i).contact_point_type
803 , x_old_contact_tbl(i).contact_type
804 , x_old_contact_tbl(i).primary_flag
805 , x_old_contact_tbl(i).party_role_code
806 , x_old_contact_tbl(i).start_date_active
807 , x_old_contact_tbl(i).end_date_active
808 , l_incident_id;
809
810 CLOSE c_sr_contact;
811
812 -- IF c_sr_contact%FOUND -- Coommented to resolve bug # 5629281.
813 IF x_old_contact_tbl(i).sr_contact_point_id IS NOT NULL
814 THEN
815 -- in case of insert, p_incident_id will be null
816 IF NVL(p_incident_id,-1) <> l_incident_id
817 THEN
818 FND_MESSAGE.set_name('CS', 'CS_SR_CP_DO_NOT_BELONGS');
819 FND_MESSAGE.set_token
820 ( 'CP_ID'
821 , TO_CHAR(x_new_contact_tbl(i).sr_contact_point_id)
822 );
823 FND_MESSAGE.set_token
824 ( 'INC_ID'
825 , TO_CHAR(p_incident_id)
826 );
827 FND_MSG_PUB.ADD_DETAIL(P_ASSOCIATED_COLUMN1=>'CS_HZ_SR_CONTACT_POINTS.SR_CONTACT_POINT_ID');
828 x_return_status := FND_API.G_RET_STS_ERROR;
829 EXIT;
830 END IF;
831 l_updated_cp_index := l_updated_cp_index + 1;
832 x_updated_cp_qry :=
833 x_updated_cp_qry || ', :' || to_char(l_updated_cp_index);
834 x_updated_cp_bind(l_updated_cp_index) := x_old_contact_tbl(i).sr_contact_point_id;
835 -- CLOSE c_sr_contact; -- Coommented to resolve bug # 5629281.
836 END IF;
837 END IF;
838 IF x_new_contact_tbl(i).party_id = FND_API.G_MISS_NUM
839 THEN
840 x_new_contact_tbl(i).party_id := x_old_contact_tbl(i).party_id;
841 END IF;
842 IF x_new_contact_tbl(i).contact_type = FND_API.G_MISS_CHAR
843 THEN
844 x_new_contact_tbl(i).contact_type := x_old_contact_tbl(i).contact_type;
845 END IF;
846 IF x_new_contact_tbl(i).primary_flag = FND_API.G_MISS_CHAR
847 THEN
848 x_new_contact_tbl(i).primary_flag := x_old_contact_tbl(i).primary_flag;
849 END IF;
850 IF x_new_contact_tbl(i).contact_point_id = FND_API.G_MISS_NUM
851 THEN
852 x_new_contact_tbl(i).contact_point_id := x_old_contact_tbl(i).contact_point_id;
853 END IF;
854 IF x_new_contact_tbl(i).contact_point_type = FND_API.G_MISS_CHAR
855 THEN
856 x_new_contact_tbl(i).contact_point_type := x_old_contact_tbl(i).contact_point_type;
857 END IF;
858 IF x_new_contact_tbl(i).sr_contact_point_id = FND_API.G_MISS_NUM
859 THEN
860 x_new_contact_tbl(i).sr_contact_point_id:= x_old_contact_tbl(i).sr_contact_point_id;
861 END IF;
862 IF x_new_contact_tbl(i).party_role_code = FND_API.G_MISS_CHAR
863 THEN
864 x_new_contact_tbl(i).party_role_code := x_old_contact_tbl(i).party_role_code;
865 END IF;
866 IF x_new_contact_tbl(i).party_role_code IS NULL
867 THEN
868 x_new_contact_tbl(i).party_role_code := 'CONTACT';
869 END IF;
870 IF x_new_contact_tbl(i).start_date_active = FND_API.G_MISS_DATE
871 THEN
872 x_new_contact_tbl(i).start_date_active := x_old_contact_tbl(i).start_date_active;
873 END IF;
874 IF x_new_contact_tbl(i).end_date_active = FND_API.G_MISS_DATE
875 THEN
876 x_new_contact_tbl(i).end_date_active := x_old_contact_tbl(i).end_date_active;
877 END IF;
878 x_new_contact_tbl(i).start_date_active := TRUNC(x_new_contact_tbl(i).start_date_active);
879 x_new_contact_tbl(i).end_date_active := TRUNC(x_new_contact_tbl(i).end_date_active);
880 END LOOP;
881 END process_g_miss;
882 --------------------------------------------------------------------------------
883 -- Procedure Name :
884 -- Parameters :
885 -- IN :
886 -- OUT :
887 --
888 -- Description :
889 --
890 -- Modification History:
891 -- Date Name Desc
892 -------- -------- --------------------------------------------------------------
893 -- 04/15/05 smisra Created
894 --------------------------------------------------------------------------------
895 FUNCTION new_primary
896 ( p_new_contact_tbl IN CS_SERVICEREQUEST_PVT.contacts_table
897 , p_old_contact_tbl IN CS_SERVICEREQUEST_PVT.contacts_table
898 , x_return_status OUT NOCOPY VARCHAR2
899 ) RETURN NUMBER IS
900 BEGIN
901 FOR loop_index in p_new_contact_tbl.FIRST..p_new_contact_tbl.LAST
902 LOOP
903 IF p_new_contact_tbl(loop_index).primary_flag = 'Y'
904 THEN
905 -- if new contact is called primary but old record does not call
906 -- it primary then there must be other primary contact in database
907 IF NVL(p_old_contact_tbl(loop_index).primary_flag,'N') <> 'Y'
908 THEN
909 RETURN 'Y';
910 END IF;
911 END IF;
912 END LOOP;
913 RETURN 'N';
914 END new_primary;
915 --
916 --------------------------------------------------------------------------------
917 -- Procedure Name :
918 -- Parameters :
919 -- IN :
920 -- OUT :
921 --
922 -- Description :
923 --
924 -- Modification History:
925 -- Date Name Desc
926 -------- -------- --------------------------------------------------------------
927 -- 04/15/05 smisra Created
928 -- 10/05/05 smisra Change Request : 4645490
929 -- Called create_cp_audit only if profile to audit contact
930 -- points is enabled.
931 -- Removed party_id, contact_type and party_role_code from
932 -- update statement as these attributes can not be updated.
933 --------------------------------------------------------------------------------
934 PROCEDURE create_update
935 ( p_incident_id IN NUMBER
936 , p_invocation_mode IN VARCHAR2
937 , p_sr_update_date IN DATE
938 , p_sr_updated_by IN VARCHAR2
939 , p_sr_update_login IN VARCHAR2
940 , p_contact_tbl IN CS_SERVICEREQUEST_PVT.contacts_table
941 , p_old_contact_tbl IN CS_SERVICEREQUEST_PVT.contacts_table
942 , x_return_status OUT NOCOPY VARCHAR2
943 ) IS
944 --
945 l_audit_id NUMBER;
946 l_msg_data VARCHAR2(2000);
947 l_msg_count NUMBER;
948 l_activity_code VARCHAR2(1);
949 l_sr_contact_point_id NUMBER;
950 l_sysdate DATE;
951 l_add_audit VARCHAR2(1);
952 l_audit_enabled fnd_profile_option_values.profile_option_value % TYPE;
953 --
954 BEGIN
955 --siahmed added to disable auditing if invocation_mode is set to replay
956 --before there was no if condition it was just the following commented line
957 --l_audit_enabled := FND_PROFILE.value('CS_SR_CONT_PNT_AUDIT_ENABLED');
958 IF (p_invocation_mode = 'REPLAY' ) THEN
959 l_audit_enabled := 'N';
960 ELSE
961 l_audit_enabled := FND_PROFILE.value('CS_SR_CONT_PNT_AUDIT_ENABLED');
962 END IF;
963 --end of change siahmed
964
965 IF p_contact_tbl.COUNT = 0
966 THEN
967 x_return_status := FND_API.G_RET_STS_SUCCESS;
968 RETURN;
969 END IF;
970
971 l_sysdate := sysdate;
972 FOR loop_index in p_contact_tbl.FIRST..p_contact_tbl.LAST LOOP
973 l_add_audit := 'N';
974 IF p_old_contact_tbl(loop_index).sr_contact_point_id is NULL
975 THEN
976 SELECT cs_hz_sr_contact_points_s.NEXTVAL
977 INTO l_sr_contact_point_id
978 FROM DUAL;
979 l_activity_code := 'C';
980 --
981 l_add_audit := 'Y';
982 INSERT INTO cs_hz_sr_contact_points
983 ( sr_contact_point_id
984 , party_id
985 , incident_id
986 , contact_point_type
987 , contact_type
988 , contact_point_id
989 , primary_flag
990 , party_role_code
991 , start_date_active
992 , end_date_active
993 , creation_date
994 , created_by
995 , last_update_date
996 , last_updated_by
997 , last_update_login
998 , object_version_number)
999 VALUES
1000 ( l_sr_contact_point_id
1001 , p_contact_tbl(loop_index).party_id
1002 , p_incident_id
1003 , p_contact_tbl(loop_index).contact_point_type
1004 , p_contact_tbl(loop_index).contact_type
1005 , p_contact_tbl(loop_index).contact_point_id
1006 , p_contact_tbl(loop_index).primary_flag
1007 , NVL(p_contact_tbl(loop_index).party_role_code,'CONTACT')
1008 , p_contact_tbl(loop_index).start_date_active
1009 , p_contact_tbl(loop_index).end_date_active
1010 , p_sr_update_date
1011 , p_sr_updated_by
1012 , p_sr_update_date
1013 , p_sr_updated_by
1014 , p_sr_update_login
1015 , 1 );
1016 -- Else statement does not compare party_id, contact_type nd party role because
1017 -- these can not be updated.
1018 ELSIF (NVL(p_contact_tbl(loop_index).contact_point_type,'xz') <>
1019 NVL(p_old_contact_tbl(loop_index).contact_point_type,'xz') OR
1020 NVL(p_contact_tbl(loop_index).contact_point_id ,-99) <>
1021 NVL(p_old_contact_tbl(loop_index).contact_point_id,-99) OR
1022 NVL(p_contact_tbl(loop_index).start_date_active,l_sysdate) <>
1023 NVL(p_old_contact_tbl(loop_index).start_date_active,l_sysdate) OR
1024 NVL(p_contact_tbl(loop_index).end_date_active,l_sysdate) <>
1025 NVL(p_old_contact_tbl(loop_index).end_date_active,l_sysdate) OR
1026 NVL(p_contact_tbl(loop_index).primary_flag,'N') <>
1027 NVL(p_old_contact_tbl(loop_index).primary_flag,'N') )
1028 THEN
1029 l_add_audit := 'Y';
1030 l_activity_code := 'U';
1031 l_sr_contact_point_id := p_contact_tbl(loop_index).sr_contact_point_id;
1032 -- update statement does not include party id, contact type and party role code because
1033 -- these attributes can not be updated.
1034 UPDATE cs_hz_sr_contact_points
1035 SET primary_flag = p_contact_tbl(loop_index).primary_flag
1036 , contact_point_id = p_contact_tbl(loop_index).contact_point_id
1037 , contact_point_type = p_contact_tbl(loop_index).contact_point_type
1038 , start_date_active = p_contact_tbl(loop_index).start_date_active
1039 , end_date_active = p_contact_tbl(loop_index).end_date_active
1040 , last_updated_by = p_sr_updated_by
1041 , last_update_date = p_sr_update_date
1042 , last_update_login = p_sr_update_login
1043 , object_version_number = object_version_number+1
1044 WHERE sr_contact_point_id = l_sr_contact_point_id;
1045 END IF;
1046 --- Create Child audit
1047 IF l_add_audit = 'Y'
1048 THEN
1049 CS_SR_CHILD_AUDIT_PKG.CS_SR_AUDIT_CHILD
1050 ( p_incident_id => p_incident_id
1051 , p_updated_entity_code => 'SR_CONTACT_POINT'
1052 , p_updated_entity_id => l_sr_contact_point_id
1053 , p_entity_update_date => p_sr_update_date
1054 , p_entity_activity_code => l_activity_code
1055 , x_audit_id => l_audit_id
1056 , x_return_status => x_return_status
1057 , x_msg_count => l_msg_count
1058 , x_msg_data => l_msg_data
1059 );
1060 IF l_audit_enabled = 'Y'
1061 THEN
1062 create_cp_audit
1063 ( p_sr_contact_point_id => l_sr_contact_point_id
1064 , p_incident_id => p_incident_id
1065 , p_new_cp_rec => p_contact_tbl(loop_index)
1066 , p_old_cp_rec => p_old_contact_tbl(loop_index)
1067 , p_cp_modified_by => p_sr_updated_by
1068 , p_cp_modified_on => p_sr_update_date
1069 , x_return_status => x_return_status
1070 , x_msg_count => l_msg_count
1071 , x_msg_data => l_msg_data
1072 );
1073 END IF;
1074 END IF;
1075 /*
1076 */
1077 END LOOP;
1078 END CREATE_UPDATE;
1079 -- -----------------------------------------------------------------------------
1080 -- Procedure Name :
1081 -- Parameters :
1082 -- IN :
1083 -- OUT :
1084 --
1085 -- Description :
1086 --
1087 -- Modification History:
1088 -- Date Name Desc
1089 -- -------- -------- -----------------------------------------------------------
1090 -- 04/15/05 smisra Created
1091 -- 10/06/05 smisra Added audit of contact point record if primary flag is
1092 -- set to N
1093 -- -----------------------------------------------------------------------------
1094 PROCEDURE reset_primary_flag
1095 ( p_incident_id IN NUMBER
1096 , p_sr_contact_point_id IN NUMBER
1097 , x_return_status OUT NOCOPY VARCHAR2
1098 ) IS
1099 l_new_cp_rec CS_SERVICEREQUEST_PVT.contacts_rec;
1100 l_old_cp_rec CS_SERVICEREQUEST_PVT.contacts_rec;
1101 l_msg_count NUMBER;
1102 l_msg_data VARCHAR2(4000);
1103 l_audit_enabled fnd_profile_option_values.profile_option_value % TYPE;
1104 BEGIN
1105 l_audit_enabled := FND_PROFILE.value('CS_SR_CONT_PNT_AUDIT_ENABLED');
1106 UPDATE cs_hz_sr_contact_points
1107 SET primary_flag = 'N'
1108 WHERE incident_id = p_incident_id
1109 AND primary_flag = 'Y'
1110 AND sr_contact_point_id <> NVL(p_sr_contact_point_id,-9)
1111 RETURNING
1112 sr_contact_point_id ,
1113 party_id ,
1114 contact_point_id ,
1115 primary_flag ,
1116 contact_point_type ,
1117 contact_type ,
1118 party_role_code ,
1119 start_date_active ,
1120 end_date_active
1121 INTO l_new_cp_rec
1122 ;
1123 IF SQL%FOUND AND
1124 l_audit_enabled = 'Y'
1125 THEN
1126 l_old_cp_rec := l_new_cp_rec;
1127 l_old_cp_rec.primary_flag := 'Y';
1128 create_cp_audit
1129 ( p_sr_contact_point_id => l_new_cp_rec.sr_contact_point_id
1130 , p_incident_id => p_incident_id
1131 , p_new_cp_rec => l_new_cp_rec
1132 , p_old_cp_rec => l_old_cp_rec
1133 , p_cp_modified_by => NULL
1134 , p_cp_modified_on => NULL
1135 , x_return_status => x_return_status
1136 , x_msg_count => l_msg_count
1137 , x_msg_data => l_msg_data
1138 );
1139 END IF;
1140 EXCEPTION
1141 WHEN OTHERS
1142 THEN
1143 NULL;
1144 END reset_primary_flag;
1145 -- -----------------------------------------------------------------------------
1146 -- Procedure Name :
1147 -- Parameters :
1148 -- IN :
1149 -- OUT :
1150 --
1151 -- Description :
1152 --
1153 -- Modification History:
1154 -- Date Name Desc
1155 -- -------- -------- -----------------------------------------------------------
1156 -- 04/15/05 smisra Created
1157 -- 10/06/05 smisra Modified this procedure so that primary contact checks are
1158 -- performed only if contact table has atleat one contact
1159 -- point record with role as CONTACT
1160 -- 10/21/05 smisra Called check_duplicates to fix bug 4074184
1161 -- 10/25/05 smisra Passed p_mode to check_duplicates procedure
1162 -- -----------------------------------------------------------------------------
1163 PROCEDURE process
1164 ( p_mode IN VARCHAR2
1165 , p_incident_id IN NUMBER
1166 , p_caller_type IN VARCHAR2
1167 , p_customer_id IN NUMBER
1168 , p_validation_mode IN NUMBER
1169 , p_contact_tbl IN CS_SERVICEREQUEST_PVT.contacts_table
1170 , x_contact_tbl OUT NOCOPY CS_SERVICEREQUEST_PVT.contacts_table
1171 , x_old_contact_tbl OUT NOCOPY CS_SERVICEREQUEST_PVT.contacts_table
1172 , x_primary_party_id OUT NOCOPY NUMBER
1173 , x_primary_contact_point_id OUT NOCOPY NUMBER
1174 , x_return_status OUT NOCOPY VARCHAR2
1175 ) IS
1176 --
1177 l_primary_found number;
1178 l_new_contact_tbl CS_SERVICEREQUEST_PVT.contacts_table;
1179 l_old_contact_tbl CS_SERVICEREQUEST_PVT.contacts_table;
1180 l_api_name_full VARCHAR2(61);
1181 l_existing_primary NUMBER;
1182 l_contact_party_role_found NUMBER := 0;
1183 l_updated_cp_qry VARCHAR2(4000);
1184 l_updated_cp_bind NUM_TBL;
1185
1186 --Start Change ,Added by Lakshmi,for 12.1.2 project
1187 l_primary_contact_id NUMBER := 0;
1188 l_counter NUMBER := 0;
1189 l_primary_contact_type VARCHAR2(30) := null;
1190 l_last_name varchar2(50) := null;
1191 l_first_name varchar2(50) := null;
1192 l_inact_prm_contact varchar2(5) := null;
1193 l_prm_contact_mand varchar2(5) := null;
1194 --End of change
1195
1196 CURSOR c_primary_count IS
1197 SELECT
1198 party_id
1199 , DECODE(contact_point_type,'PHONE',contact_point_id,NULL)
1200 FROM
1201 cs_hz_sr_contact_points
1202 WHERE incident_id = p_incident_id
1203 AND primary_flag = 'Y';
1204 l_sr_contact_point_id_pri NUMBER;
1205 l_today DATE;
1206 --
1207 BEGIN
1208 l_api_name_full := 'CS_SRCONTACT_PKG.process';
1209 x_return_Status := FND_API.G_RET_STS_SUCCESS;
1210 l_today := TRUNC(SYSDATE);
1211 l_primary_found := 0;
1212 IF p_contact_tbl.COUNT = 0
1213 THEN
1214 RETURN;
1215 END IF;
1216 process_g_miss
1217 ( p_mode => p_mode
1218 , p_incident_id => p_incident_id
1219 , p_new_contact_tbl => p_contact_tbl
1220 , x_new_contact_tbl => l_new_contact_tbl
1221 , x_old_contact_tbl => l_old_contact_tbl
1222 , x_updated_cp_qry => l_updated_cp_qry
1223 , x_updated_cp_bind => l_updated_cp_bind
1224 , x_return_status => x_return_status
1225 );
1226 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
1227 THEN
1228 RAISE FND_API.G_EXC_ERROR;
1229 END IF;
1230 --
1231 -- Check for primary if contacts are being passed
1232 --
1233 FOR loop_index in l_new_contact_tbl.FIRST..l_new_contact_tbl.LAST LOOP
1234 IF NVL(l_new_contact_tbl(loop_index).party_role_code,'CONTACT') = 'CONTACT' AND
1235 NVL(l_new_contact_tbl(loop_index).end_date_active,l_today) >= l_today
1236 THEN
1237 l_contact_party_role_found := l_contact_party_role_found + 1 ;
1238 END IF ;
1239
1240 IF l_new_contact_tbl(loop_index).primary_flag = 'Y'
1241 THEN
1242 l_primary_found := l_primary_found + 1;
1243 x_primary_party_id := l_new_contact_tbl(loop_index).party_id;
1244 l_sr_contact_point_id_pri := l_new_contact_tbl(loop_index).sr_contact_point_id;
1245 IF l_new_contact_tbl(loop_index).contact_point_type = 'PHONE'
1246 THEN
1247 x_primary_contact_point_id := l_new_contact_tbl(loop_index).contact_point_id;
1248 END IF;
1249 END IF;
1250 END LOOP;
1251
1252 -- if any contact party is found only then we need to check
1253 -- for single primary contact
1254 IF l_contact_party_role_found > 0
1255 THEN
1256 IF l_primary_found >= 2
1257 THEN
1258 CS_ServiceRequest_UTIL.Add_Invalid_Argument_Msg
1259 ( p_token_an => l_api_name_full
1260 , p_token_v => 'Y'
1261 , p_token_p => 'p_contacts.primary_flag'
1262 , p_table_name => 'CS_HZ_SR_CONTACT_POINTS'
1263 , p_column_name => 'PRIMARY_FLAG'
1264 );
1265 RAISE FND_API.G_EXC_ERROR;
1266 ELSIF l_primary_found = 0
1267 THEN
1268 l_existing_primary := 0;
1269 IF p_mode <> 'CREATE'
1270 THEN
1271 OPEN c_primary_count;
1272 FETCH c_primary_count
1273 INTO x_primary_party_id, x_primary_contact_point_id;
1274 IF c_primary_count % FOUND
1275 THEN
1276 l_existing_primary := 1;
1277 END IF;
1278 CLOSE c_primary_count;
1279 END IF;
1280 IF l_existing_primary = 0
1281 THEN
1282 CS_SERVICEREQUEST_UTIL.add_null_parameter_msg
1283 ( p_token_an => l_api_name_full
1284 , p_token_np => 'Primary Contact Information'
1285 );
1286 RAISE FND_API.G_EXC_ERROR;
1287 END IF;
1288 ELSE -- means primary contact count is exactly 1. in case of update mode
1289 -- existing primary cantact should be made non primary.
1290 IF p_mode <> 'CREATE'
1291 THEN
1292 IF l_sr_contact_point_id_pri = FND_API.G_MISS_NUM
1293 THEN
1294 l_sr_contact_point_id_pri := NULL;
1295 END IF;
1296 reset_primary_flag(p_incident_id, l_sr_contact_point_id_pri, x_return_status);
1297 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
1298 THEN
1299 RAISE FND_API.G_EXC_ERROR;
1300 END IF;
1301 END IF;
1302 END IF;
1303 END IF; -- l_contact_party_role_found
1304 --
1305 -- Now Validate contact records
1306 --
1307 IF p_validation_mode > FND_API.G_VALID_LEVEL_NONE
1308 THEN
1309 FOR loop_index in l_new_contact_tbl.FIRST..l_new_contact_tbl.LAST LOOP
1310 -- Commented for 12.1.2 project ,end-dating contact points,Lakshmi
1311 /* IF l_primary_found = 0 AND
1312 l_old_contact_tbl(loop_index).primary_flag = 'Y' AND -- if it is null, no execution
1313 NVL(l_new_contact_tbl(loop_index).primary_flag,'N') <> 'Y'
1314 THEN
1315 IF NVL(l_new_contact_tbl(loop_index).end_date_active, l_today) >= l_today OR
1316 contact_role_count(p_incident_id) > 1
1317 THEN
1318 FND_MESSAGE.set_name('CS', 'CS_SR_NO_PRIMARY_LEFT');
1319 FND_MESSAGE.set_token ('API_NAME','cs_srcontact_pkg.process');
1320 FND_MESSAGE.set_token
1321 ( 'INC_ID'
1322 , TO_CHAR(l_new_contact_tbl(loop_index).sr_contact_point_id)
1323 );
1324 FND_MSG_PUB.ADD_DETAIL(P_ASSOCIATED_COLUMN1=>'CS_HZ_SR_CONTACT_POINTS.PRIMARY_FLAG');
1325 RAISE FND_API.G_EXC_ERROR;
1326 END IF;
1327 END IF;*/
1328 -- Comment ends for 12.1.2 project ,end-dating contact points
1329 validate_contact
1330 ( p_caller_type => p_caller_type
1331 , p_customer_id => p_customer_id
1332 , p_new_contact_rec => l_new_contact_tbl(loop_index)
1333 , p_old_contact_rec => l_old_contact_tbl(loop_index)
1334 , x_return_status => x_return_status
1335 );
1336 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
1337 THEN
1338 RAISE FND_API.G_EXC_ERROR;
1339 END IF;
1340 END LOOP;
1341 --
1342 -- Check for duplicate contact point Records
1343 --
1344 check_duplicates
1345 ( p_mode => p_mode
1346 , p_new_contact_tbl => l_new_contact_tbl
1347 , p_updated_cp_qry => l_updated_cp_qry
1348 , p_updated_cp_bind => l_updated_cp_bind
1349 , p_incident_id => p_incident_id
1350 , x_return_status => x_return_status
1351 );
1352 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
1353 THEN
1354 RAISE FND_API.G_EXC_ERROR;
1355 END IF;
1356 --Lakshmi - 12.1.2 Nov Project -Change starts for End Dating Contact
1357 IF l_new_contact_tbl.count = 1 -- IF THERE ONLY ONE CONTACT
1358 THEN
1359 IF NVL(l_new_contact_tbl(1).party_role_code,'CONTACT') = 'CONTACT'
1360 AND l_new_contact_tbl(1).end_date_active IS NOT NULL
1361 AND FND_PROFILE.value('CS_SR_CONTACT_MANDATORY') = 'Y'
1362 THEN
1363 l_primary_contact_type := l_new_contact_tbl(1).CONTACT_TYPE;
1364 l_primary_contact_id := l_new_contact_tbl(1).PARTY_ID;
1365 l_prm_contact_mand := 'Y';
1366
1367 END IF; --Profile value check,If profile is 'N' continue with inactivating
1368 ELSE -- else contact_tbl >1
1369 FOR loop_index in l_new_contact_tbl.FIRST..l_new_contact_tbl.LAST LOOP
1370 IF NVL(l_new_contact_tbl(loop_index).party_role_code,'CONTACT') = 'CONTACT' AND
1371 l_new_contact_tbl(loop_index).end_date_active IS NOT NULL
1372 THEN
1373 l_counter := l_counter+1;
1374 IF l_new_contact_tbl(loop_index).PRIMARY_FLAG = 'Y'
1375 THEN
1376 l_primary_contact_id := l_new_contact_tbl(loop_index).PARTY_ID;
1377 l_primary_contact_type := l_new_contact_tbl(loop_index).CONTACT_TYPE;
1378 END IF;
1379 END IF;
1380 END lOOP;
1381 IF l_counter = l_new_contact_tbl.count
1382 AND FND_PROFILE.value('CS_SR_CONTACT_MANDATORY') = 'Y'
1383 THEN
1384 l_inact_prm_contact := 'Y';
1385 ELSIF l_counter <> l_new_contact_tbl.count
1386 AND l_primary_contact_id <> 0
1387 THEN
1388 l_prm_contact_mand := 'Y';
1389 END IF;
1390
1391 END IF; -- END OF else of contact >1
1392 IF l_primary_contact_id <> 0
1393 THEN
1394 IF l_primary_contact_type = 'EMPLOYEE'
1395 THEN
1396 select LAST_NAME,FIRST_NAME into l_last_name,l_first_name
1397 from PER_ALL_PEOPLE_F where person_id = l_primary_contact_id;
1398 ELSIF l_primary_contact_type = 'PERSON'
1399 THEN
1400 select PERSON_LAST_NAME,PERSON_FIRST_NAME into l_last_name,l_first_name
1401 from HZ_PARTIES where party_id = l_primary_contact_id;
1402 ELSIF l_primary_contact_type = 'PARTY_RELATIONSHIP'
1403 THEN
1404 select PERSON_LAST_NAME,PERSON_FIRST_NAME into l_last_name,l_first_name
1405 from HZ_PARTIES p, HZ_RELATIONSHIPS r
1406 where r.party_id = l_primary_contact_id
1407 and r.subject_id = p.party_id
1408 and r.subject_type = 'PERSON'
1409 and r.subject_table_name = 'HZ_PARTIES'
1410 and r.directional_flag = 'F';
1411 END IF;
1412 IF l_prm_contact_mand = 'Y'
1413 THEN
1414 FND_MESSAGE.set_name ('CS','CS_SR_CONTACT_POINT_MANDATORY');
1415 ELSIF l_inact_prm_contact = 'Y'
1416 THEN
1417 FND_MESSAGE.set_name ('CS','CS_SR_INACT_PRIMARY_CONTACT');
1418 END IF;
1419
1420
1421 FND_MESSAGE.set_token ('CONTACT_LAST_NAME',l_last_name);
1422 FND_MESSAGE.set_token ('CONTACT_FIRST_NAME',l_first_name);
1423 FND_MSG_PUB.add;
1424 RAISE FND_API.G_EXC_ERROR;
1425 END IF;
1426
1427 --End of changes for 12.1.2 -End Dating Contact by Lakshmi
1428 END IF; -- check for validation level
1429 x_contact_tbl := l_new_contact_tbl;
1430 x_old_contact_tbl := l_old_contact_tbl;
1431 EXCEPTION
1432 WHEN FND_API.G_EXC_ERROR THEN
1433 x_return_status := FND_API.G_RET_STS_ERROR;
1434 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1435 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1436 WHEN OTHERS THEN
1437 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1438 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1439 FND_MSG_PUB.Add_Exc_Msg('aa', l_api_name_full);
1440 END IF;
1441 END process;
1442
1443 --------------------------------------------------------------------------------
1444 -- Procedure Name : populate_cp_audit_rec
1445 -- Parameters :
1446 -- IN : p_sr_contact_point_id - Contact point identifier.
1447 -- OUT : x_cp_contact_rec This is a populated audit record.
1448 -- : x_return_status Indicates success or Error condition
1449 -- encountered by procedure.
1450 -- x_msg_count
1451 -- x_msg_data
1452 --
1453 -- Modification History:
1454 -- Date Name Desc
1455 -------- -------- --------------------------------------------------------------
1456 -- 11/23/2005 spusegao created
1457 --------------------------------------------------------------------------------
1458
1459 PROCEDURE Populate_CP_Audit_Rec
1460 (p_sr_contact_point_id IN NUMBER,
1461 x_sr_contact_rec OUT NOCOPY CS_SERVICEREQUEST_PVT.CONTACTS_REC,
1462 x_return_status OUT NOCOPY VARCHAR2,
1463 x_msg_count OUT NOCOPY NUMBER,
1464 x_msg_data OUT NOCOPY VARCHAR2) IS
1465
1466 --Local Variabled
1467
1468
1469 -- Get contact point details.
1470 CURSOR c_get_cp_details IS
1471 SELECT *
1472 FROM cs_hz_sr_contact_points
1473 WHERE sr_contact_point_id = p_sr_contact_point_id ;
1474
1475 BEGIN
1476 -- Get contact point details for the passed contact point identifier.
1477 FOR c_get_cp_details_rec IN c_get_cp_details
1478 LOOP
1479 x_sr_contact_rec.sr_contact_point_id := c_get_cp_details_rec.sr_contact_point_id;
1480 x_sr_contact_rec.party_id := c_get_cp_details_rec.party_id;
1481 x_sr_contact_rec.contact_point_id := c_get_cp_details_rec.contact_point_id;
1482 x_sr_contact_rec.primary_flag := c_get_cp_details_rec.primary_flag;
1483 x_sr_contact_rec.contact_point_type := c_get_cp_details_rec.contact_point_type;
1484 x_sr_contact_rec.contact_type := c_get_cp_details_rec.contact_type;
1485 x_sr_contact_rec.party_role_code := c_get_cp_details_rec.party_role_code;
1486 x_sr_contact_rec.start_date_active := c_get_cp_details_rec.start_date_active;
1487 x_sr_contact_rec.end_date_active := c_get_cp_details_rec.end_date_active;
1488 END LOOP;
1489
1490 EXCEPTION
1491 WHEN OTHERS THEN
1492 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1493 FND_MSG_PUB.Count_And_Get
1494 ( p_count => x_msg_count,
1495 p_data => x_msg_data);
1496 RAISE;
1497 END Populate_CP_Audit_Rec;
1498
1499 END;
[Home] [Help]