[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
ihps.START_DATE START_DT, --hps.START_DATE_ACTIVE START_DT,
hps.party_site_id,
ihps.END_DATE END_DT, -- hps.END_DATE_ACTIVE END_DT,
hl.COUNTRY COUNTRY_CD,
hl.ADDRESS_STYLE,
hl.ADDRESS1 ADDR_LINE_1,
hl.ADDRESS2 ADDR_LINE_2,
hl.ADDRESS3 ADDR_LINE_3,
hl.ADDRESS4 ADDR_LINE_4,
hps.identifying_address_flag CORRESPONDENCE,
hl.CITY,
hl.STATE,
hl.PROVINCE,
hl.COUNTY,
hl.POSTAL_CODE,
hl.address_lines_phonetic,
hl.delivery_point_code,
hps.status
FROM
HZ_LOCATIONS hl,
hz_party_sites hps,
hz_party_site_uses hpsu,
igs_pe_hz_pty_sites ihps
WHERE
hl.location_id = hps.location_id AND
hps.party_id = cp_person_id AND
hps.party_site_id = hpsu.party_site_id AND
hpsu.site_use_type = cp_site_use_type AND
hps.party_site_id = ihps.party_site_id(+) AND
hps.status= 'A' AND
hpsu.status= 'A' AND
hps.identifying_address_flag = 'Y';
SELECT
ihps.START_DATE START_DT, --hps.START_DATE_ACTIVE START_DT,
hps.party_site_id,
ihps.END_DATE END_DT, -- hps.END_DATE_ACTIVE END_DT,
hl.COUNTRY COUNTRY_CD,
hl.ADDRESS_STYLE,
hl.ADDRESS1 ADDR_LINE_1,
hl.ADDRESS2 ADDR_LINE_2,
hl.ADDRESS3 ADDR_LINE_3,
hl.ADDRESS4 ADDR_LINE_4,
hps.identifying_address_flag CORRESPONDENCE,
hl.CITY,
hl.STATE,
hl.PROVINCE,
hl.COUNTY,
hl.POSTAL_CODE,
hl.address_lines_phonetic,
hl.delivery_point_code,
hps.status
FROM
HZ_LOCATIONS hl,
hz_party_sites hps,
hz_party_site_uses hpsu,
igs_pe_hz_pty_sites ihps
WHERE
hl.location_id = hps.location_id AND
hps.party_id = cp_person_id AND
hps.party_site_id = hpsu.party_site_id AND
hpsu.site_use_type = cp_site_use_type AND
hps.party_site_id = ihps.party_site_id(+) AND
hps.status= 'A' AND
hpsu.status= 'A' AND
SYSDATE BETWEEN ihps.START_DATE AND NVL(ihps.END_DATE, SYSDATE)
ORDER BY ihps.START_DATE DESC;
SELECT TO_NUMBER(nvl((SYSDATE-birth_date)/365,0))
FROM igs_pe_person_base_v
WHERE person_id = cp_person_id;
l_last_update_date DATE;
l_site_last_update_date DATE;
l_profile_last_update_date DATE;
IGS_PE_PERSON_ADDR_PKG.INSERT_ROW(
p_action =>'INSERT',
p_rowid => l_rowid,
p_location_id => l_location_id,
p_start_dt => rec_student_addr.START_DT,
p_end_dt => rec_student_addr.END_DT,
p_country => rec_student_addr.COUNTRY_CD,
p_address_style => rec_student_addr.ADDRESS_STYLE,
p_addr_line_1 => rec_student_addr.ADDR_LINE_1,
p_addr_line_2 => rec_student_addr.ADDR_LINE_2,
p_addr_line_3 => rec_student_addr.ADDR_LINE_3,
p_addr_line_4 => rec_student_addr.ADDR_LINE_4,
p_date_last_verified => NULL,
p_correspondence => rec_student_addr.CORRESPONDENCE,
p_city => rec_student_addr.CITY,
p_state => rec_student_addr.STATE,
p_province => rec_student_addr.PROVINCE,
p_county => rec_student_addr.COUNTY,
p_postal_code => rec_student_addr.POSTAL_CODE,
p_address_lines_phonetic => rec_student_addr.address_lines_phonetic,
p_delivery_point_code => rec_student_addr.delivery_point_code,
p_other_details_1 => NULL,
p_other_details_2 => NULL,
p_other_details_3 => NULL,
l_return_status => l_return_status ,
l_msg_data => l_msg_data,
p_party_id => p_object_id,
p_party_site_id => l_party_site_id,
p_party_type => 'PERSON',
p_last_update_date => l_last_update_date ,
p_party_site_ovn => l_party_site_ovn,
p_location_ovn => l_location_ovn,
p_status => rec_student_addr.status
);
l_last_update_date := NULL;
p_action => 'INSERT',
p_rowid => l_rowid,
p_party_site_use_id => l_party_site_use_id,
p_party_site_id => l_party_site_id,
p_site_use_type => l_profile_addr_usage,
p_return_status => l_return_status,
p_msg_data => l_msg_data,
p_last_update_date => l_last_update_date,
p_site_use_last_update_date => l_site_last_update_date,
p_profile_last_update_date => l_profile_last_update_date,
p_status => 'A',
P_HZ_PARTY_SITE_USE_OVN => l_object_version_number
);
Description: : Call api (INSERT_ROW/ADD_ROW) only when p_caller is not 'ORGANIZATION'; and pass 'F' for x_directional_flag parameter in ADD_ROW
PROCEDURE CREATUPDATE_PARTY_RELATIONSHIP(
p_action IN VARCHAR2 ,
p_subject_id IN NUMBER ,
p_object_id IN NUMBER ,
p_party_relationship_type IN VARCHAR2 ,
p_relationship_code IN VARCHAR2,
p_comments IN VARCHAR2 ,
p_start_date IN DATE,
p_end_date IN DATE,
p_last_update_date IN OUT NOCOPY DATE ,
p_return_status OUT NOCOPY VARCHAR2 ,
p_msg_count OUT NOCOPY NUMBER ,
p_msg_data OUT NOCOPY VARCHAR2 ,
p_party_relationship_id IN OUT NOCOPY VARCHAR2 ,
p_party_id OUT NOCOPY NUMBER ,
p_party_number OUT NOCOPY VARCHAR2,
p_caller IN VARCHAR2 ,
P_Object_Version_Number IN OUT NOCOPY NUMBER,
P_Primary IN VARCHAR2 ,
P_Secondary IN VARCHAR2,
P_Joint_Salutation IN VARCHAR2,
P_Next_To_Kin IN VARCHAR2 ,
P_Rep_Faculty IN VARCHAR2,
P_Rep_Staff IN VARCHAR2,
P_Rep_Student IN VARCHAR2,
P_Rep_Alumni IN VARCHAR2,
p_directional_flag IN VARCHAR2,
p_emergency_contact_flag IN VARCHAR2
) AS
CURSOR get_party_rel_type_cur(cp_relationship_type hz_relationship_types.relationship_type%type) IS
SELECT 'X'
FROM hz_relationship_types
WHERE RELATIONSHIP_TYPE = cp_relationship_type AND
SUBJECT_TYPE= 'PERSON' AND
OBJECT_TYPE='PERSON';
SELECT birth_date
FROM IGS_PE_PERSON_BASE_V
WHERE person_id = p_subject_id;
IF p_action = 'INSERT' THEN
prel_rec.subject_id := p_subject_id;
select party_type into prel_rec.object_type from hz_parties where party_id = p_object_id;
SELECT hz_party_number_s.nextval INTO p_party_rec.party_number FROM dual;
IGS_PE_HZ_REL_PKG.INSERT_ROW(
x_rowid =>l_rowid ,
x_relationship_id =>p_party_relationship_id,
x_directional_flag =>'F',
x_primary =>p_primary,
x_secondary =>p_secondary,
x_joint_salutation =>p_joint_salutation,
x_next_to_kin =>p_next_to_kin,
x_rep_faculty =>p_rep_faculty,
x_rep_staff =>p_rep_staff,
x_rep_student =>p_rep_student,
x_rep_alumni =>p_rep_alumni,
x_emergency_contact_flag =>p_emergency_contact_flag);
ELSIF p_action = 'UPDATE' THEN
-- get the object_version_number , new methodology for locking in V2 apis
-- this cursor may return 2 records with the V2 APIs structure and HZpatchset C
-- hence fetch only one record, both the records will have the same last update date and
-- object version number
-- kumma. V2 API logic has been modified, and if a field which had data is made NULL
-- then we have to explicitly pass it as G_MISS_CHAR.
-- cross checked with API coding standards also. bug number 2314209
IF p_comments IS NULL THEN
prel_rec.comments := FND_API.G_MISS_CHAR;
HZ_RELATIONSHIP_V2PUB.update_relationship (
p_init_msg_list => null,
p_relationship_rec => prel_rec,
p_object_version_number => p_object_version_number,
p_party_object_version_number => x_party_object_version_number ,
x_return_status => p_return_status,
x_msg_count => p_msg_count,
x_msg_data => p_msg_data );
END creatupdate_party_relationship ;