[Home] [Help]
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.
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'
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: ;
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
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
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
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: --
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'))
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;
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
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
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'
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
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
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;
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: --
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
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;
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;
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: --
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
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
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)
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;
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
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
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.