The following lines contain the word 'select', 'insert', 'update' or 'delete':
, p_updated_cp_qry IN VARCHAR2
, p_updated_cp_bind IN NUM_TBL
, p_incident_id IN NUMBER
, x_return_status OUT NOCOPY VARCHAR2
) IS
l_not_updated_contacts CS_SERVICEREQUEST_PVT.contacts_table;
l_sql := 'SELECT contact_type, party_id, party_role_code, contact_point_id, contact_point_type, start_date_active, end_date_active
FROM cs_hz_sr_contact_points
WHERE incident_id = :incident_id
/* AND TRUNC(SYSDATE) BETWEEN NVL(start_date_active, TRUNC(SYSDATE))
AND NVL(end_date_active , TRUNC(SYSDATE)) */';
IF p_updated_cp_qry <> ':1'
THEN
l_sql := l_sql || ' AND sr_contact_point_id NOT IN ( '|| p_updated_cp_qry || ')';
IF p_updated_cp_qry <> ':1'
THEN
FOR indx in p_updated_cp_bind.FIRST..p_updated_cp_bind.LAST
LOOP
DBMS_SQL.bind_variable(l_cur_hdl, ':' ||to_char(indx), p_updated_cp_bind(indx));
l_not_updated_contacts(l_index).party_id := l_party_id;
l_not_updated_contacts(l_index).contact_type := l_contact_type;
l_not_updated_contacts(l_index).party_role_code := l_party_role_code;
l_not_updated_contacts(l_index).end_date_active := l_end_date_active;
l_not_updated_contacts(l_index).contact_point_id := l_contact_point_id;
l_not_updated_contacts(l_index).start_date_active := l_start_date_active;
l_not_updated_contacts(l_index).contact_point_type := l_contact_point_type;
IF l_not_updated_contacts.count > 0
THEN
FOR k in l_not_updated_contacts.FIRST..l_not_updated_contacts.LAST
LOOP
l_st_dt2 := NVL(l_not_updated_contacts(k).start_date_active, TRUNC(SYSDATE-36500));
l_end_dt2 := NVL(l_not_updated_contacts(k).end_date_active , TRUNC(SYSDATE+36500));
IF p_new_contact_tbl(i).party_id = l_not_updated_contacts(k).party_id AND
p_new_contact_tbl(i).contact_type = l_not_updated_contacts(k).contact_type AND
p_new_contact_tbl(i).party_role_code = l_not_updated_contacts(k).party_role_code AND
NVL(p_new_contact_tbl(i).contact_point_id ,-99) = NVL(l_not_updated_contacts(k).contact_point_id ,-99) AND
NVL(p_new_contact_tbl(i).contact_point_type,'-') = NVL(l_not_updated_contacts(k).contact_point_type,'-') AND
( l_st_dt1 BETWEEN l_st_dt2 AND l_end_dt2 OR
l_st_dt2 BETWEEN l_st_dt1 AND l_end_dt1
)
THEN
l_dup_found := 1;
END IF; --end of condition IF l_not_updated_contacts.count > 0
SELECT COUNT(1)
INTO l_count
FROM cs_hz_sr_contact_points
WHERE incident_id = p_incident_id
AND party_role_code = 'CONTACT'
AND NVL(END_DATE_ACTIVE,sysdate) >= l_dt
;
INSERT INTO CS_HZ_SR_CONTACT_PNTS_AUDIT
( sr_contact_point_audit_id
, sr_contact_point_id
, incident_id
, party_id
, old_party_id
, contact_type
, old_contact_type
, party_role_code
, old_party_role_code
, contact_point_type
, old_contact_point_type
, contact_point_id
, old_contact_point_id
, start_date_active
, old_start_date_active
, end_date_active
, old_end_date_active
, primary_flag
, old_primary_flag
, creation_date
, last_update_date
, created_by
, last_updated_by
, last_update_login
, contact_point_modified_by
, contact_point_modified_on
)
VALUES
( cs.cs_hz_sr_cont_pnts_audit_s.NEXTVAL
, p_sr_contact_point_id
, p_incident_id
, p_new_cp_rec.party_id
, p_old_cp_rec.party_id
, p_new_cp_rec.contact_type
, p_old_cp_rec.contact_type
, p_new_cp_rec.party_role_code
, p_old_cp_rec.party_role_code
, p_new_cp_rec.contact_point_type
, p_old_cp_rec.contact_point_type
, p_new_cp_rec.contact_point_id
, p_old_cp_rec.contact_point_id
, p_new_cp_rec.start_date_active
, p_old_cp_rec.start_date_active
, p_new_cp_rec.end_date_active
, p_old_cp_rec.end_date_active
, p_new_cp_rec.primary_flag
, p_old_cp_rec.primary_flag
, l_dt
, l_dt
, FND_GLOBAL.USER_ID
, FND_GLOBAL.USER_ID
, FND_GLOBAL.LOGIN_ID
, p_cp_modified_by
, p_cp_modified_on
);
p_mode := 'UPDATE';
IF p_mode = 'UPDATE'
THEN
-- check contact type update
IF p_new_contact_rec.contact_type <> p_old_contact_rec.contact_type
THEN
FND_MESSAGE.set_name ('CS','CS_SR_CP_CONTACT_TYPE_UPD_NA');
END IF; -- p_mode = 'UPDATE'
, x_updated_cp_qry OUT NOCOPY VARCHAR2
, x_updated_cp_bind OUT NOCOPY NUM_TBL
, x_return_status OUT NOCOPY VARCHAR2
) IS
l_incident_id CS_INCIDENTS_ALL_B.incident_id % type;
l_updated_cp_index NUMBER;
SELECT
sr_contact_point_id
, party_id
, contact_point_id
, contact_point_type
, contact_type
, primary_flag
, party_role_code
, start_date_active
, end_date_active
, incident_id
FROM
cs_hz_sr_contact_points
WHERE sr_contact_point_id = p_sr_contact_point_id;
x_updated_cp_qry := ':1';
l_updated_cp_index := 1;
x_updated_cp_bind(l_updated_cp_index) := -1;
l_updated_cp_index := l_updated_cp_index + 1;
x_updated_cp_qry :=
x_updated_cp_qry || ', :' || to_char(l_updated_cp_index);
x_updated_cp_bind(l_updated_cp_index) := x_old_contact_tbl(i).sr_contact_point_id;
PROCEDURE create_update
( p_incident_id IN NUMBER
, p_invocation_mode IN VARCHAR2
, p_sr_update_date IN DATE
, p_sr_updated_by IN VARCHAR2
, p_sr_update_login IN VARCHAR2
, p_contact_tbl IN CS_SERVICEREQUEST_PVT.contacts_table
, p_old_contact_tbl IN CS_SERVICEREQUEST_PVT.contacts_table
, x_return_status OUT NOCOPY VARCHAR2
) IS
--
l_audit_id NUMBER;
SELECT cs_hz_sr_contact_points_s.NEXTVAL
INTO l_sr_contact_point_id
FROM DUAL;
INSERT INTO cs_hz_sr_contact_points
( sr_contact_point_id
, party_id
, incident_id
, contact_point_type
, contact_type
, contact_point_id
, primary_flag
, party_role_code
, start_date_active
, end_date_active
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, object_version_number)
VALUES
( l_sr_contact_point_id
, p_contact_tbl(loop_index).party_id
, p_incident_id
, p_contact_tbl(loop_index).contact_point_type
, p_contact_tbl(loop_index).contact_type
, p_contact_tbl(loop_index).contact_point_id
, p_contact_tbl(loop_index).primary_flag
, NVL(p_contact_tbl(loop_index).party_role_code,'CONTACT')
, p_contact_tbl(loop_index).start_date_active
, p_contact_tbl(loop_index).end_date_active
, p_sr_update_date
, p_sr_updated_by
, p_sr_update_date
, p_sr_updated_by
, p_sr_update_login
, 1 );
UPDATE cs_hz_sr_contact_points
SET primary_flag = p_contact_tbl(loop_index).primary_flag
, contact_point_id = p_contact_tbl(loop_index).contact_point_id
, contact_point_type = p_contact_tbl(loop_index).contact_point_type
, start_date_active = p_contact_tbl(loop_index).start_date_active
, end_date_active = p_contact_tbl(loop_index).end_date_active
, last_updated_by = p_sr_updated_by
, last_update_date = p_sr_update_date
, last_update_login = p_sr_update_login
, object_version_number = object_version_number+1
WHERE sr_contact_point_id = l_sr_contact_point_id;
, p_updated_entity_code => 'SR_CONTACT_POINT'
, p_updated_entity_id => l_sr_contact_point_id
, p_entity_update_date => p_sr_update_date
, p_entity_activity_code => l_activity_code
, x_audit_id => l_audit_id
, x_return_status => x_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
);
, p_cp_modified_by => p_sr_updated_by
, p_cp_modified_on => p_sr_update_date
, x_return_status => x_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
);
END CREATE_UPDATE;
UPDATE cs_hz_sr_contact_points
SET primary_flag = 'N'
WHERE incident_id = p_incident_id
AND primary_flag = 'Y'
AND sr_contact_point_id <> NVL(p_sr_contact_point_id,-9)
RETURNING
sr_contact_point_id ,
party_id ,
contact_point_id ,
primary_flag ,
contact_point_type ,
contact_type ,
party_role_code ,
start_date_active ,
end_date_active
INTO l_new_cp_rec
;
l_updated_cp_qry VARCHAR2(4000);
l_updated_cp_bind NUM_TBL;
SELECT
party_id
, DECODE(contact_point_type,'PHONE',contact_point_id,NULL)
FROM
cs_hz_sr_contact_points
WHERE incident_id = p_incident_id
AND primary_flag = 'Y';
, x_updated_cp_qry => l_updated_cp_qry
, x_updated_cp_bind => l_updated_cp_bind
, x_return_status => x_return_status
);
ELSE -- means primary contact count is exactly 1. in case of update mode
-- existing primary cantact should be made non primary.
IF p_mode <> 'CREATE'
THEN
IF l_sr_contact_point_id_pri = FND_API.G_MISS_NUM
THEN
l_sr_contact_point_id_pri := NULL;
, p_updated_cp_qry => l_updated_cp_qry
, p_updated_cp_bind => l_updated_cp_bind
, p_incident_id => p_incident_id
, x_return_status => x_return_status
);
select LAST_NAME,FIRST_NAME into l_last_name,l_first_name
from PER_ALL_PEOPLE_F where person_id = l_primary_contact_id;
select PERSON_LAST_NAME,PERSON_FIRST_NAME into l_last_name,l_first_name
from HZ_PARTIES where party_id = l_primary_contact_id;
select PERSON_LAST_NAME,PERSON_FIRST_NAME into l_last_name,l_first_name
from HZ_PARTIES p, HZ_RELATIONSHIPS r
where r.party_id = l_primary_contact_id
and r.subject_id = p.party_id
and r.subject_type = 'PERSON'
and r.subject_table_name = 'HZ_PARTIES'
and r.directional_flag = 'F';
SELECT *
FROM cs_hz_sr_contact_points
WHERE sr_contact_point_id = p_sr_contact_point_id ;