[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
HZ_CONTACT_POINT_PUB TO HZ_CONTACT_POINT_V2PUB for create and update of
person language and contact points
ssawhney 27 may BUG - 2377751, error codes modified from E008 for contacts.
|| npalanis 9-may-2002 BUG - 2352725
|| Dupcontact point id value is set
|| and also bug - 2338473 for messages is changed.
|| npalanis 6-may-2002 Bug - 2352725
|| * The contact point type in the interface table is made not
|| null and check is added that the contact point type must be PHONE or
|| EMAIL.
|| * IF check is added to see that whether the contact point type
|| is PHONE or EMAIL before and the respective attributes are populated
|| before creating or updating contact points.
|| * In dup check cursor contact point type = 'PHONE' check is
|| made.
|| * Validate procedure is added to validate contact point type,
|| phone line type,phone country code , email format from fnd lookups.
|| * If check is added in validate proc to check that email address,
|| email format cannot be null when contact point type is 'EMAIl' and
|| phone number , phone line type cannot be null when contact point type is
|| 'PHONE'.
|| * The contact point type check and phone line type check in
|| create and update contact point proc is removed.
|| * Cursor C1 fetches records based on contact point ID =
|| igs_ad_contacts_int.interface_contacts_id it is changed to
|| contact point ID = l_contact_point_id from hz_contact_points.
|| ssawhney 15 nov Bug no.2103692:Person Interface DLD
|| prc_pe_citizenship code is removed from here and added to
|| IGS_AD_IMP_007.
|| gmaheswa 11 Nov 2003 Bug 3223043 HZ.K Impact Changes
*/
cst_mi_val_18 CONSTANT VARCHAR2(2) := '18';
SELECT ai.interface_contacts_id,
ai.interface_id ai_interface_id,
UPPER(ai.contact_point_type) contact_point_type,
ai.email_address,
UPPER(ai.email_format) email_format,
UPPER(ai.primary_flag) primary_flag,
UPPER(ai.phone_line_type) phone_line_type,
ai.phone_country_code,
ai.phone_area_code,
ai.phone_number,
ai.phone_extension,
ai.status ai_status,
ai.match_ind ai_match_ind,
ai.error_code ai_error_code,
ai.dup_contact_point_id,
ai.created_by,
ai.creation_date,
ai.last_updated_by,
ai.last_update_date,
ai.last_update_login,
ai.request_id,
ai.program_application_id,
ai.program_id,
ai.program_update_date,
i.interface_id i_interface_id,
i.person_id i_person_id,
i.match_ind i_match_ind
FROM igs_ad_contacts_int_all ai, igs_ad_interface_all i
WHERE ai.interface_run_id = cp_interface_run_id
AND i.interface_id = ai.interface_id
AND i.interface_run_id = cp_interface_run_id
AND ai.status = '2';
l_update_date1 DATE;
UPDATE igs_ad_contacts_int_all
SET error_code='E322',status='3'
WHERE interface_contacts_id=rec_pc.interface_contacts_id;
UPDATE igs_ad_contacts_int_all
SET status='1'
WHERE interface_contacts_id=rec_pc.interface_contacts_id;
UPDATE igs_ad_contacts_int_all
SET error_code='E322',status='3'
WHERE interface_contacts_id=rec_pc.interface_contacts_id;
SELECT 'X'
FROM HZ_PHONE_COUNTRY_CODES
WHERE PHONE_COUNTRY_CODE = p_phone_country_code;
UPDATE igs_ad_contacts_int_all
SET status = cst_stat_val_3,
error_code = cst_err_val_246,
match_ind = cst_mi_val_18
WHERE interface_contacts_id = c_pc_rec.interface_contacts_id;
UPDATE igs_ad_contacts_int_all
SET error_code = 'E450',
status = '3'
WHERE interface_contacts_id = c_pc_rec.interface_contacts_id;
UPDATE igs_ad_contacts_int_all
SET error_code = 'E250',
status = '3'
WHERE interface_contacts_id = c_pc_rec.interface_contacts_id;
UPDATE igs_ad_contacts_int_all
SET status = '3',
error_code = 'E247'
WHERE interface_contacts_id = c_pc_rec.interface_contacts_id;
UPDATE igs_ad_contacts_int_all
SET status = '3',
error_code = 'E173'
WHERE interface_contacts_id = c_pc_rec.interface_contacts_id;
UPDATE igs_ad_contacts_int_all
SET error_code = 'E251',
status = '3'
WHERE interface_contacts_id = c_pc_rec.interface_contacts_id;
UPDATE igs_ad_contacts_int_all
SET error_code = 'E248',
status = '3'
WHERE interface_contacts_id = c_pc_rec.interface_contacts_id;
UPDATE igs_ad_contacts_int_all
SET status = cst_stat_val_3,
ERROR_CODE = cst_err_val_695 -- Error code depicting incorrect combination
WHERE match_ind IS NOT NULL
AND interface_run_id = l_interface_run_id
AND status = cst_stat_val_2;
UPDATE igs_ad_contacts_int_all ai
SET status = cst_stat_val_1,
match_ind = cst_mi_val_19
WHERE ai.interface_run_id = l_interface_run_id
AND ai.status = cst_stat_val_2
AND EXISTS( SELECT '1'
FROM hz_contact_points pe, igs_ad_interface_all i
WHERE i.interface_run_id = l_interface_run_id
AND i.interface_id = ai.interface_id
AND pe.owner_table_id = i.person_id
AND UPPER(ai.contact_point_type) = pe.contact_point_type
AND pe.owner_table_name = 'HZ_PARTIES'
AND ((pe.email_format= UPPER(ai.email_format)
AND UPPER(pe.email_address) = UPPER(ai.email_address)
AND pe.contact_point_type='EMAIL')
OR (pe.phone_line_type = UPPER(ai.phone_line_type)
AND (pe.phone_country_code = ai.phone_country_code OR (pe.phone_country_code IS NULL AND ai.phone_country_code IS NULL))
AND (UPPER(pe.phone_Area_code) = UPPER(ai.phone_area_code) OR (pe.phone_Area_code IS NULL AND ai.phone_area_code IS NULL ) )
AND pe.phone_number=ai.phone_number
AND pe.contact_point_type='PHONE'))
);
UPDATE igs_ad_contacts_int_all
SET status = cst_stat_val_1
WHERE interface_run_id = l_interface_run_id
AND match_ind IN (cst_mi_val_18,cst_mi_val_19,cst_mi_val_22,cst_mi_val_23)
AND status=cst_stat_val_2;
UPDATE igs_ad_contacts_int_all
SET status = cst_stat_val_3,
ERROR_CODE = cst_err_val_695
WHERE interface_run_id = l_interface_run_id
AND (match_ind IS NOT NULL AND match_ind NOT IN (cst_mi_val_21,cst_mi_val_25))
AND status=cst_stat_val_2;
UPDATE igs_ad_contacts_int_all ai
SET status = cst_stat_val_1,
match_ind = cst_mi_val_23
WHERE ai.interface_run_id = l_interface_run_id
AND ai.match_ind IS NULL
AND ai.status = cst_stat_val_2
AND EXISTS ( SELECT '1'
FROM hz_contact_points pe, igs_ad_interface_all i
WHERE i.interface_run_id = l_interface_run_id
AND i.interface_id = ai.interface_id
AND pe.owner_table_id = i.person_id
AND UPPER(ai.contact_point_type) = pe.contact_point_type
AND pe.owner_table_name = 'HZ_PARTIES'
AND ((pe.email_format= UPPER(ai.email_format)
AND UPPER(pe.email_address) = UPPER(ai.email_address)
AND pe.contact_point_type='EMAIL')
OR (pe.phone_line_type = UPPER(ai.phone_line_type)
AND (pe.phone_country_code = ai.phone_country_code OR (pe.phone_country_code IS NULL AND ai.phone_country_code IS NULL))
AND (UPPER(pe.phone_Area_code) = UPPER(ai.phone_area_code) OR (pe.phone_Area_code IS NULL AND ai.phone_area_code IS NULL ) )
AND pe.phone_number=ai.phone_number
AND (pe.phone_extension = ai.phone_extension
OR (pe.phone_extension IS NULL AND ai.phone_extension IS NULL))
AND pe.contact_point_type='PHONE'))
AND pe.primary_flag = NVL(ai.primary_flag,'N')
AND pe.content_source_type = 'USER_ENTERED'
);
UPDATE igs_ad_contacts_int_all ai
SET status = cst_stat_val_3,
match_ind = cst_mi_val_20,
dup_contact_point_id = (SELECT contact_point_id
FROM hz_contact_points pe, igs_ad_interface_all i
WHERE i.interface_run_id = l_interface_run_id
AND i.interface_id = ai.interface_id
AND rownum = 1
AND pe.owner_table_id = i.person_id
AND UPPER(ai.contact_point_type) = pe.contact_point_type
AND pe.owner_table_name = 'HZ_PARTIES'
AND ((pe.email_format= UPPER(ai.email_format)
AND UPPER(pe.email_address) = UPPER(ai.email_address)
AND pe.contact_point_type='EMAIL')
OR (pe.phone_line_type = UPPER(ai.phone_line_type)
AND (pe.phone_country_code = ai.phone_country_code OR (pe.phone_country_code IS NULL AND ai.phone_country_code IS NULL))
AND (UPPER(pe.phone_Area_code) = UPPER(ai.phone_area_code) OR (pe.phone_Area_code IS NULL AND ai.phone_area_code IS NULL ) )
AND pe.phone_number=ai.phone_number
AND pe.contact_point_type='PHONE')))
WHERE ai.interface_run_id = l_interface_run_id
AND ai.match_ind IS NULL
AND ai.status = cst_stat_val_2
AND EXISTS (SELECT '1'
FROM hz_contact_points pe, igs_ad_interface_all i
WHERE i.interface_run_id = l_interface_run_id
AND i.interface_id = ai.interface_id
AND pe.owner_table_id = i.person_id
AND UPPER(ai.contact_point_type) = pe.contact_point_type
AND pe.owner_table_name = 'HZ_PARTIES'
AND ((pe.email_format= UPPER(ai.email_format)
AND UPPER(pe.email_address) = UPPER(ai.email_address)
AND pe.contact_point_type='EMAIL')
OR (pe.phone_line_type = UPPER(ai.phone_line_type)
AND (pe.phone_country_code = ai.phone_country_code OR (pe.phone_country_code IS NULL AND ai.phone_country_code IS NULL))
AND (UPPER(pe.phone_Area_code) = UPPER(ai.phone_area_code) OR (pe.phone_Area_code IS NULL AND ai.phone_area_code IS NULL ) )
AND pe.phone_number=ai.phone_number
AND pe.contact_point_type='PHONE')));
SELECT ROWID, hi.*
FROM hz_contact_points hi
WHERE hi.owner_table_id=p_owner_table_id
AND UPPER(hi.contact_point_type)=UPPER(p_contact_point_type)
AND UPPER(hi.owner_table_name)='HZ_PARTIES'
AND ((UPPER(hi.email_format)=UPPER(p_email_format)
AND UPPER(hi.email_address)=UPPER(p_email_address)
AND UPPER(hi.contact_point_type)='EMAIL')
OR (UPPER(hi.phone_line_type)=UPPER(p_phone_line_type)
AND (UPPER(hi.phone_country_code)=UPPER(p_phone_country_code) OR (hi.phone_country_code IS NULL AND p_phone_country_code IS NULL ) )
AND (UPPER(hi.phone_Area_code)=UPPER(p_phone_area_code) OR (hi.phone_Area_code IS NULL AND p_phone_area_code IS NULL ) )
AND UPPER(hi.phone_number)=UPPER(p_phone_number)
AND UPPER(hi.contact_point_type)='PHONE'));
l_last_update DATE;
SELECT object_version_number
INTO l_obj_ver
FROM hz_contact_points
WHERE contact_point_id = check_dup_contact_rec.contact_point_id;
HZ_CONTACT_POINT_V2PUB.update_contact_point(
p_init_msg_list => FND_API.G_FALSE,
p_contact_point_rec => p_contact_points_rec,
p_email_rec => p_email_rec ,
p_phone_rec => p_phone_rec,
p_object_version_number => l_obj_ver,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
UPDATE igs_ad_contacts_int_all
SET ERROR_CODE = 'E014',
status='3'
WHERE interface_contacts_id = rec_pc1.interface_contacts_id;
UPDATE igs_ad_contacts_int_all
SET status=cst_stat_val_1,
match_ind =cst_mi_val_18
WHERE interface_contacts_id = rec_pc1.interface_contacts_id;
UPDATE igs_ad_contacts_int_all
SET match_ind = cst_mi_val_18,
status = cst_stat_val_3
WHERE interface_contacts_id = rec_pc1.interface_contacts_id;
l_last_update DATE;
SELECT object_version_number
INTO l_obj_ver
FROM hz_contact_points
WHERE contact_point_id = l_contact_point_id;
HZ_CONTACT_POINT_V2PUB.update_contact_point(
p_init_msg_list => FND_API.G_FALSE,
p_contact_point_rec => p_contact_points_rec,
p_email_rec => p_email_rec ,
p_phone_rec => p_phone_rec,
p_object_version_number => l_obj_ver,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
UPDATE igs_ad_contacts_int_all
SET error_code = 'E014',
status='3'
WHERE interface_contacts_id = rec_pc1.interface_contacts_id;
UPDATE igs_ad_contacts_int_all
SET status = cst_stat_val_1,
match_ind = cst_mi_val_18
WHERE interface_contacts_id = rec_pc1.interface_contacts_id;
UPDATE igs_ad_contacts_int_all
SET status = '3'
WHERE interface_contacts_id = rec_pc1.interface_contacts_id;
UPDATE igs_ad_contacts_int_all
SET status = '3',error_code='E518'
WHERE interface_contacts_id = rec_pc1.interface_contacts_id;
SELECT hii.*, i.person_id
FROM igs_ad_language_int_all hii, igs_ad_interface_all i
WHERE hii.interface_run_id = cp_interface_run_id
AND i.interface_id = hii.interface_id
AND i.interface_run_id = cp_interface_run_id
AND hii.status = '2';
SELECT 'X'
FROM fnd_languages_vl
WHERE language_code = cp_language_code;
UPDATE igs_ad_language_int_all
SET status = '3',
error_code = l_error_code
WHERE interface_language_id = p_person_language_rec.interface_language_id;
l_last_update_date DATE;
p_action => 'INSERT',
P_LANGUAGE_NAME => p_person_language_rec.language_name,
p_DESCRIPTION => null,
p_PARTY_ID => p_person_language_rec.person_id,
p_native_language => p_person_language_rec.native_language,
p_primary_language_indicator => p_person_language_rec.primary_language_indicator,
P_READS_LEVEL => p_person_language_rec.reads_level,
P_SPEAKS_LEVEL => p_person_language_rec.speaks_level,
P_WRITES_LEVEL => p_person_language_rec.writes_level,
p_END_DATE => null,
p_status => p_person_language_rec.lang_status,
p_understand_level => p_person_language_rec.understands_level,
p_last_update_date => l_last_update_date,
p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
P_language_use_reference_id => l_language_use_reference_id,
p_language_ovn => l_object_version_number
);
UPDATE igs_ad_language_int_all
SET status = '3',
error_code = 'E322'
WHERE interface_language_id = p_person_language_rec.interface_language_id;
UPDATE igs_ad_language_int_all
SET status = '1',
error_code = NULL
WHERE interface_language_id = p_person_language_rec.interface_language_id;
UPDATE igs_ad_language_int_all
SET status = '3',
error_code = 'E322'
WHERE interface_language_id = p_person_language_rec.interface_language_id;
UPDATE igs_ad_language_int_all
SET status = cst_stat_val_3,
ERROR_CODE = cst_err_val_695 -- Error code depicting incorrect combination
WHERE match_ind IS NOT NULL
AND interface_run_id = l_interface_run_id
AND status = cst_stat_val_2;
UPDATE igs_ad_language_int_all ai
SET status = cst_stat_val_1,
match_ind = cst_mi_val_19
WHERE ai.interface_run_id = l_interface_run_id
AND ai.status = cst_stat_val_2
AND EXISTS( SELECT '1'
FROM hz_person_language pe, igs_ad_interface_all i
WHERE i.interface_run_id = l_interface_run_id
AND i.interface_id = ai.interface_id
AND pe.party_id = i.person_id
AND pe.language_name = UPPER(ai.language_name)
);
UPDATE igs_ad_language_int_all
SET status = cst_stat_val_1
WHERE interface_run_id = l_interface_run_id
AND match_ind IN (cst_mi_val_18,cst_mi_val_19,cst_mi_val_22,cst_mi_val_23)
AND status=cst_stat_val_2;
UPDATE igs_ad_language_int_all
SET status = cst_stat_val_3,
ERROR_CODE = cst_err_val_695
WHERE interface_run_id = l_interface_run_id
AND (match_ind IS NOT NULL AND match_ind NOT IN (cst_mi_val_21,cst_mi_val_25))
AND status=cst_stat_val_2;
UPDATE igs_ad_language_int_all mi
SET status = cst_stat_val_1,
match_ind = cst_mi_val_23
WHERE mi.interface_run_id = l_interface_run_id
AND mi.match_ind IS NULL
AND mi.status = cst_stat_val_2
AND EXISTS ( SELECT '1'
FROM hz_person_language pe, igs_ad_interface_all i
WHERE i.interface_run_id = l_interface_run_id
AND i.interface_id = mi.interface_id
AND pe.language_name = UPPER(mi.language_name)
AND pe.party_id = i.person_id
AND NVL(UPPER(pe.native_language),'N') = NVL(UPPER(mi.native_language),'N')
AND NVL(UPPER(pe.primary_language_indicator),'N') = NVL(UPPER(mi.primary_language_indicator),'N')
AND NVL(UPPER(pe.reads_level),'*!*') = NVL(UPPER(mi.reads_level),'*!*')
AND NVL(UPPER(pe.speaks_level),'*!*') = NVL(UPPER(mi.speaks_level),'*!*')
AND NVL(UPPER(pe.writes_level),'*!*') = NVL(UPPER(mi.writes_level),'*!*')
AND NVL(UPPER(pe.spoken_comprehension_level),'*!*') = NVL(UPPER(mi.understands_level),'*!*')
AND NVL(UPPER(pe.status),'*!*') = NVL(UPPER(mi.lang_status),'*!*')
);
UPDATE igs_ad_language_int_all mi
SET status = cst_stat_val_3,
match_ind = cst_mi_val_20
WHERE mi.interface_run_id = l_interface_run_id
AND mi.match_ind IS NULL
AND mi.status = cst_stat_val_2
AND EXISTS (SELECT '1'
FROM hz_person_language pe, igs_ad_interface_all i
WHERE i.interface_run_id = l_interface_run_id
AND i.interface_id = mi.interface_id
AND pe.party_id = i.person_id
AND pe.language_name = UPPER(mi.language_name));
SELECT rowid, hi.*
FROM hz_person_language hi
WHERE hi.party_id = p_person_id
AND hi.language_name = p_language_name;
p_action => 'UPDATE',
P_LANGUAGE_NAME => person_language_rec.language_name,
p_DESCRIPTION => NULL,
p_PARTY_ID => person_language_rec.person_id,
p_native_language => NVL(person_language_rec.native_language,check_dup_language_rec.native_language),
p_primary_language_indicator => NVL(person_language_rec.primary_language_indicator,check_dup_language_rec.primary_language_indicator),
P_READS_LEVEL => NVL(person_language_rec.reads_level,check_dup_language_rec.reads_level),
P_SPEAKS_LEVEL => NVL(person_language_rec.speaks_level,check_dup_language_rec.speaks_level),
P_WRITES_LEVEL => NVL(person_language_rec.writes_level,check_dup_language_rec.writes_level),
p_END_DATE => NULL,
p_status => NVL(person_language_rec.lang_status,check_dup_language_rec.status),
p_understand_level => NVL(person_language_rec.understands_level,check_dup_language_rec.spoken_comprehension_level),
p_last_update_date => person_language_rec.last_update_date,
p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
P_language_use_reference_id => check_dup_language_rec.language_use_reference_id,
p_language_ovn => check_dup_language_rec.object_version_number
);
UPDATE igs_ad_language_int_all
SET error_code = 'E014',
status='3'
WHERE interface_language_id = person_language_rec.interface_language_id;
UPDATE igs_ad_language_int_all
SET status=cst_stat_val_1,
match_ind = cst_mi_val_18
WHERE interface_language_id = person_language_rec.interface_language_id;
UPDATE igs_ad_language_int_all
SET match_ind = NULL,
status = cst_stat_val_3,
error_code = cst_err_val_014
WHERE interface_language_id = person_language_rec.interface_language_id;
p_action => 'UPDATE',
P_LANGUAGE_NAME => person_language_rec.language_name,
p_DESCRIPTION => NULL,
p_PARTY_ID => person_language_rec.person_id,
p_native_language => NVL(person_language_rec.native_language,check_dup_language_rec.native_language),
p_primary_language_indicator => NVL(person_language_rec.primary_language_indicator,check_dup_language_rec.primary_language_indicator),
P_READS_LEVEL => NVL(person_language_rec.reads_level,check_dup_language_rec.reads_level),
P_SPEAKS_LEVEL => NVL(person_language_rec.speaks_level,check_dup_language_rec.speaks_level),
P_WRITES_LEVEL => NVL(person_language_rec.writes_level,check_dup_language_rec.writes_level),
p_END_DATE => NULL,
p_status => NVL(person_language_rec.lang_status,check_dup_language_rec.status),
p_understand_level => NVL(person_language_rec.understands_level,check_dup_language_rec.spoken_comprehension_level),
p_last_update_date => person_language_rec.last_update_date,
p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
P_language_use_reference_id => check_dup_language_rec.language_use_reference_id,
p_language_ovn => check_dup_language_rec.object_version_number
);
UPDATE igs_ad_language_int_all
SET error_code = 'E014',
status='3'
WHERE interface_language_id = person_language_rec.interface_language_id;
UPDATE igs_ad_language_int_all
SET status=cst_stat_val_1,
match_ind = cst_mi_val_18
WHERE interface_language_id = person_language_rec.interface_language_id;
UPDATE igs_ad_language_int_all
SET match_ind = NULL,
status = cst_stat_val_3,
error_code = cst_err_val_014
WHERE interface_language_id = person_language_rec.interface_language_id;
|| Lookup code columns are made upper before inserting.
|| asbala 16-OCT-2003 Bug 3130316. Import Process Source Category Rule processing changes,
lookup caching related changes, and cursor parameterization.
*/
l_rule VARCHAR2(1);
SELECT ai.*, i.person_id
FROM igs_pe_ath_dtl_int ai,
igs_ad_interface_all i
WHERE ai.interface_run_id = cp_interface_run_id
AND i.interface_id = ai.interface_id
AND i.interface_run_id = cp_interface_run_id
AND ai.status = '2';
SELECT ROWID, ad.*
FROM igs_pe_athletic_dtl ad
WHERE person_id = cp_person_id;
igs_pe_athletic_dtl_pkg.insert_row (
x_rowid => l_rowid,
x_athletic_details_id => l_athletic_details_id,
x_person_id => p_ath_dtl_rec.person_id,
x_athletic_gpa => p_ath_dtl_rec.athletic_gpa,
x_eligibility_status_cd => p_ath_dtl_rec.eligibility_status_cd,
x_predict_elig_code => p_ath_dtl_rec.predict_elig_code,
x_tentative_adm_code => p_ath_dtl_rec.tentative_adm_code,
x_review_date => p_ath_dtl_rec.review_date,
x_comments => p_ath_dtl_rec.comments,
x_mode => 'R'
);
UPDATE igs_pe_ath_dtl_int
SET status = '1',
error_code = l_error_code
WHERE interface_athletic_dtls_id = p_ath_dtl_rec.interface_athletic_dtls_id;
l_error_code := 'E093'; -- Athletics Details Insertion Failed
UPDATE igs_pe_ath_dtl_int
SET status = '3',
error_code = l_error_code
WHERE interface_athletic_dtls_id = p_ath_dtl_rec.interface_athletic_dtls_id;
||' Exception from igs_pe_athletic_dtl_Pkg.Insert_Row '
|| ' INTERFACE_ATHLETIC_DTLS_ID : ' ||
(p_ath_dtl_rec.interface_athletic_dtls_id) ||
' Status : ' || '3' || ' ErrorCode : ' || l_error_code
||' SQLERRM:' || SQLERRM;
SELECT Birth_date
FROM IGS_PE_PERSON_BASE_V
WHERE person_id= p_person_id;
UPDATE igs_pe_ath_dtl_int
SET status = '3',
error_code = l_error_code
WHERE interface_athletic_dtls_id = p_ath_dtl_rec.interface_athletic_dtls_id;
UPDATE igs_pe_ath_dtl_int
SET status = cst_stat_val_3,
ERROR_CODE = cst_err_val_695 -- Error code depicting incorrect combination
WHERE match_ind IS NOT NULL
AND interface_run_id = l_interface_run_id
AND status = cst_stat_val_2;
UPDATE igs_pe_ath_dtl_int ai
SET status = cst_stat_val_1,
match_ind = cst_mi_val_19
WHERE ai.interface_run_id = l_interface_run_id
AND ai.status = cst_stat_val_2
AND EXISTS( SELECT '1'
FROM igs_pe_athletic_dtl pe, igs_ad_interface_all i
WHERE i.interface_id = ai.interface_id
AND i.interface_run_id = l_interface_run_id
AND pe.person_id = NVL(i.person_id, -99)
);
UPDATE igs_pe_ath_dtl_int
SET status = cst_stat_val_1
WHERE interface_run_id = l_interface_run_id
AND match_ind IN (cst_mi_val_18,cst_mi_val_19,cst_mi_val_22,cst_mi_val_23)
AND status=cst_stat_val_2;
UPDATE igs_pe_ath_dtl_int
SET status = cst_stat_val_3,
ERROR_CODE = cst_err_val_695
WHERE interface_run_id = l_interface_run_id
AND (match_ind IS NOT NULL AND match_ind NOT IN (cst_mi_val_21,cst_mi_val_25))
AND status=cst_stat_val_2;
UPDATE igs_pe_ath_dtl_int mi
SET status = cst_stat_val_1,
match_ind = cst_mi_val_23
WHERE mi.interface_run_id = l_interface_run_id
AND mi.match_ind IS NULL
AND mi.status = cst_stat_val_2
AND EXISTS ( SELECT '1'
FROM igs_pe_athletic_dtl pe, igs_ad_interface_all i
WHERE i.interface_id = mi.interface_id
AND i.interface_run_id = l_interface_run_id
AND pe.person_id = NVL(i.person_id, -99) AND
NVL(pe.athletic_gpa, -99) = NVL(mi.athletic_gpa,-99) AND
NVL(UPPER(pe.eligibility_status_cd), '~') = NVL(UPPER(mi.eligibility_status_cd),'~') AND
NVL(UPPER(pe.predict_elig_code), '~') = NVL(UPPER(mi.predict_elig_code),'~') AND
NVL(UPPER(pe.tentative_adm_code), '~') = NVL(UPPER(mi.tentative_adm_code),'~') AND
NVL(TRUNC(pe.review_date),l_default_date)= NVL(TRUNC(mi.review_date),l_default_date) AND
NVL(UPPER(pe.comments), '~') = NVL(UPPER(mi.comments), '~'));
UPDATE igs_pe_ath_dtl_int ai
SET status = cst_stat_val_3,
match_ind = cst_mi_val_18,
dup_athletic_details_id = (SELECT athletic_details_id
FROM igs_pe_athletic_dtl pe, igs_ad_interface_all i
WHERE i.interface_id = ai.interface_id
AND i.interface_run_id = l_interface_run_id
AND pe.person_id = NVL(i.person_id, -99))
WHERE ai.interface_run_id = l_interface_run_id
AND ai.match_ind IS NULL
AND ai.status = cst_stat_val_2
AND EXISTS (SELECT '1'
FROM igs_pe_athletic_dtl pe, igs_ad_interface_all i
WHERE i.interface_id = ai.interface_id
AND i.interface_run_id = l_interface_run_id
AND pe.person_id = NVL(i.person_id, -99));
igs_pe_athletic_dtl_pkg.update_row (
x_rowid => dup_chk_ath_dtl_rec.rowid,
x_athletic_details_id => dup_chk_ath_dtl_rec.athletic_details_id,
x_person_id => NVL(ath_dtl_rec.person_id,dup_chk_ath_dtl_rec.person_id),
x_athletic_gpa => NVL(ath_dtl_rec.athletic_gpa,dup_chk_ath_dtl_rec.athletic_gpa),
x_eligibility_status_cd => NVL(ath_dtl_rec.eligibility_status_cd,dup_chk_ath_dtl_rec.eligibility_status_cd),
x_predict_elig_code => NVL(ath_dtl_rec.predict_elig_code,dup_chk_ath_dtl_rec.predict_elig_code),
x_tentative_adm_code => NVL(ath_dtl_rec.tentative_adm_code,dup_chk_ath_dtl_rec.tentative_adm_code),
x_review_date => NVL(ath_dtl_rec.review_date,dup_chk_ath_dtl_rec.review_date),
x_comments => NVL(ath_dtl_rec.comments,dup_chk_ath_dtl_rec.comments),
x_mode => 'R'
);
UPDATE igs_pe_ath_dtl_int
SET status = l_status,
error_code = l_error_code,
match_ind = cst_mi_val_18 -- '18' Match occured and used import values
WHERE interface_athletic_dtls_id = ath_dtl_rec.interface_athletic_dtls_id;
l_error_code := 'E094'; -- Could not update Person Athletics Details
UPDATE igs_pe_ath_dtl_int
SET status = l_status,
error_code = l_error_code
WHERE interface_athletic_dtls_id = ath_dtl_rec.interface_athletic_dtls_id;
||' Exception from igs_pe_athletic_dtl_Pkg.Update_Row '
|| ' INTERFACE_ATHLETIC_DTLS_ID : ' ||
(ath_dtl_rec.interface_athletic_dtls_id) ||
' Status : ' || '3' || ' ErrorCode : ' ||
l_error_code ||' SQLERRM:' || SQLERRM;
igs_pe_athletic_dtl_pkg.update_row (
x_rowid => dup_chk_ath_dtl_rec.rowid,
x_athletic_details_id => dup_chk_ath_dtl_rec.athletic_details_id,
x_person_id => NVL(ath_dtl_rec.person_id,dup_chk_ath_dtl_rec.person_id),
x_athletic_gpa => NVL(ath_dtl_rec.athletic_gpa,dup_chk_ath_dtl_rec.athletic_gpa),
x_eligibility_status_cd => NVL(ath_dtl_rec.eligibility_status_cd,dup_chk_ath_dtl_rec.eligibility_status_cd),
x_predict_elig_code => NVL(ath_dtl_rec.predict_elig_code,dup_chk_ath_dtl_rec.predict_elig_code),
x_tentative_adm_code => NVL(ath_dtl_rec.tentative_adm_code,dup_chk_ath_dtl_rec.tentative_adm_code),
x_review_date => NVL(ath_dtl_rec.review_date,dup_chk_ath_dtl_rec.review_date),
x_comments => NVL(ath_dtl_rec.comments,dup_chk_ath_dtl_rec.comments),
x_mode => 'R'
);
UPDATE igs_pe_ath_dtl_int
SET status = l_status,
error_code = l_error_code,
match_ind = cst_mi_val_18
WHERE interface_athletic_dtls_id = ath_dtl_rec.interface_athletic_dtls_id;
l_error_code := 'E094'; -- Could not update Person Athletics Details
UPDATE igs_pe_ath_dtl_int
SET status = l_status,
error_code = l_error_code
WHERE interface_athletic_dtls_id = ath_dtl_rec.interface_athletic_dtls_id;
||' Exception from igs_pe_athletic_dtl_Pkg.Update_Row '
|| ' INTERFACE_ATHLETIC_DTLS_ID : ' ||
(ath_dtl_rec.interface_athletic_dtls_id) ||
' Status : ' || '3' || ' ErrorCode : ' ||
l_error_code ||' SQLERRM:' || SQLERRM;
SELECT ai.*, i.person_id
FROM igs_pe_ath_prg_int ai, igs_ad_interface_all i
WHERE ai.interface_run_id = cp_interface_run_id
AND i.interface_id = ai.interface_id
AND i.interface_run_id = cp_interface_run_id
AND ai.status = '2';
SELECT ROWID, ap.*
FROM igs_pe_athletic_prg ap
WHERE person_id = cp_person_id AND
UPPER(athletic_prg_code) = UPPER(cp_athletic_prg_code) AND
start_date = cp_start_date;
igs_pe_athletic_prg_pkg.insert_row (
x_rowid => l_rowid,
x_athletic_prg_id => l_athletic_prg_id,
x_person_id => p_ath_prg_rec.person_id,
x_athletic_prg_code => p_ath_prg_rec.athletic_prg_code,
x_rating => p_ath_prg_rec.rating,
x_start_date => p_ath_prg_rec.start_date,
x_end_date => p_ath_prg_rec.end_date,
x_recruited_ind => p_ath_prg_rec.recruited_ind,
x_participating_ind => p_ath_prg_rec.participating_ind,
x_last_update_dt => p_ath_prg_rec.last_update_date,
x_mode => 'R'
);
UPDATE igs_pe_ath_prg_int
SET status = '1',
error_code = l_error_code
WHERE interface_athletic_prg_id = p_ath_prg_rec.interface_athletic_prg_id;
l_error_code := 'E099'; -- Athletics Program Insertion Failed
UPDATE igs_pe_ath_prg_int
SET status = '3',
error_code = l_error_code
WHERE interface_athletic_prg_id = p_ath_prg_rec.interface_athletic_prg_id;
||' Exception from igs_pe_athletic_prg_Pkg.Insert_Row '
|| ' INTERFACE_ATHLETIC_PRG_ID : ' ||
(p_ath_prg_rec.interface_athletic_prg_id) ||
' Status : ' || '3' || ' ErrorCode : ' || l_error_code
||' SQLERRM:' || SQLERRM;
SELECT Birth_date
FROM IGS_PE_PERSON_BASE_V
WHERE person_id= p_person_id;
UPDATE igs_pe_ath_prg_int
SET status = '3',
error_code = l_error_code
WHERE interface_athletic_prg_id = p_ath_prg_rec.interface_athletic_prg_id;
UPDATE igs_pe_ath_prg_int
SET status = cst_stat_val_3,
ERROR_CODE = cst_err_val_695 -- Error code depicting incorrect combination
WHERE match_ind IS NOT NULL
AND interface_run_id = l_interface_run_id
AND status = cst_stat_val_2;
UPDATE igs_pe_ath_prg_int ai
SET status = cst_stat_val_1,
match_ind = cst_mi_val_19
WHERE ai.interface_run_id = l_interface_run_id
AND ai.status = cst_stat_val_2
AND EXISTS( SELECT '1'
FROM igs_pe_athletic_prg pe, igs_ad_interface_all i
WHERE i.interface_id = ai.interface_id
AND i.interface_run_id = l_interface_run_id
AND pe.person_id = NVL(i.person_id, -99) AND
pe.athletic_prg_code = UPPER(ai.athletic_prg_code) AND
pe.start_date = TRUNC(ai.start_date)
);
UPDATE igs_pe_ath_prg_int
SET status = cst_stat_val_1
WHERE interface_run_id = l_interface_run_id
AND match_ind IN (cst_mi_val_18,cst_mi_val_19,cst_mi_val_22,cst_mi_val_23)
AND status=cst_stat_val_2;
UPDATE igs_pe_ath_prg_int
SET status = cst_stat_val_3,
ERROR_CODE = cst_err_val_695
WHERE interface_run_id = l_interface_run_id
AND (match_ind IS NOT NULL AND match_ind NOT IN (cst_mi_val_21,cst_mi_val_25))
AND status=cst_stat_val_2;
UPDATE igs_pe_ath_prg_int ai
SET status = cst_stat_val_1,
match_ind = cst_mi_val_23
WHERE ai.interface_run_id = l_interface_run_id
AND ai.match_ind IS NULL
AND ai.status = cst_stat_val_2
AND EXISTS ( SELECT '1'
FROM igs_pe_athletic_prg pe, igs_ad_interface_all i
WHERE i.interface_id = ai.interface_id
AND i.interface_run_id = l_interface_run_id
AND pe.person_id = NVL(i.person_id, -99) AND
pe.athletic_prg_code = UPPER(ai.athletic_prg_code) AND
pe.start_date = TRUNC(ai.start_date) AND
NVL(UPPER(pe.rating), '~') = NVL(UPPER(ai.rating),'~') AND
NVL(pe.end_date, l_default_date ) = NVL(TRUNC(ai.end_date),l_default_date) AND
NVL(UPPER(pe.recruited_ind),'N') = NVL(UPPER(ai.recruited_ind),'N') AND
NVL(UPPER(pe.participating_ind),'N') = NVL(UPPER(ai.participating_ind),'N'));
UPDATE igs_pe_ath_prg_int ai
SET status = cst_stat_val_3,
match_ind = cst_mi_val_20,
dup_athletic_prg_id = (SELECT athletic_prg_id
FROM igs_pe_athletic_prg pe, igs_ad_interface_all i
WHERE i.interface_id = ai.interface_id
AND i.interface_run_id = l_interface_run_id
AND pe.person_id = NVL(i.person_id, -99) AND
pe.athletic_prg_code = UPPER(ai.athletic_prg_code) AND
pe.start_date = TRUNC(ai.start_date))
WHERE ai.interface_run_id = l_interface_run_id
AND ai.match_ind IS NULL
AND ai.status = cst_stat_val_2
AND EXISTS (SELECT '1'
FROM igs_pe_athletic_prg pe, igs_ad_interface_all i
WHERE i.interface_id = ai.interface_id
AND i.interface_run_id = l_interface_run_id
AND pe.person_id = NVL(i.person_id, -99) AND
pe.athletic_prg_code = UPPER(ai.athletic_prg_code) AND
pe.start_date = TRUNC(ai.start_date));
igs_pe_athletic_prg_pkg.update_row (
x_rowid => dup_chk_ath_prg_rec.rowid,
x_athletic_prg_id => dup_chk_ath_prg_rec.athletic_prg_id,
x_person_id => NVL(ath_prg_rec.person_id,dup_chk_ath_prg_rec.person_id),
x_athletic_prg_code => ath_prg_rec.athletic_prg_code,
x_rating => NVL(ath_prg_rec.rating,dup_chk_ath_prg_rec.rating),
x_start_date => ath_prg_rec.start_date,
x_end_date => NVL(ath_prg_rec.end_date,dup_chk_ath_prg_rec.end_date),
x_recruited_ind => ath_prg_rec.recruited_ind,
x_participating_ind => ath_prg_rec.participating_ind,
x_last_update_dt =>nvl(ath_prg_rec.last_update_date,dup_chk_ath_prg_rec.last_update_dt),
x_mode => 'R'
);
UPDATE igs_pe_ath_prg_int
SET status = l_status,
error_code = l_error_code,
match_ind = cst_mi_val_18 -- '18' Match occured and used import values
WHERE interface_athletic_prg_id = ath_prg_rec.interface_athletic_prg_id;
l_error_code := 'E100'; -- Could not update Person Athletics Program Details
UPDATE igs_pe_ath_prg_int
SET status = l_status,
error_code = l_error_code
WHERE interface_athletic_prg_id = ath_prg_rec.interface_athletic_prg_id;
||' Exception from igs_pe_athletic_prg_Pkg.Update_Row '
|| ' INTERFACE_ATHLETIC_PRG_ID : ' ||
(ath_prg_rec.interface_athletic_prg_id) ||
' Status : ' || '3' || ' ErrorCode : ' ||
l_error_code||' SQLERRM:' || SQLERRM;
igs_pe_athletic_prg_pkg.update_row (
x_rowid => dup_chk_ath_prg_rec.rowid,
x_athletic_prg_id => dup_chk_ath_prg_rec.athletic_prg_id,
x_person_id => NVL(ath_prg_rec.person_id,dup_chk_ath_prg_rec.person_id),
x_athletic_prg_code => ath_prg_rec.athletic_prg_code,
x_rating => NVL(ath_prg_rec.rating,dup_chk_ath_prg_rec.rating),
x_start_date => ath_prg_rec.start_date,
x_end_date => NVL(ath_prg_rec.end_date,dup_chk_ath_prg_rec.end_date),
x_recruited_ind => ath_prg_rec.recruited_ind,
x_participating_ind => ath_prg_rec.participating_ind,
x_last_update_dt => nvl(ath_prg_rec.last_update_date,dup_chk_ath_prg_rec.last_update_dt),
x_mode => 'R'
);
UPDATE igs_pe_ath_prg_int
SET status = l_status,
error_code = l_error_code,
match_ind = cst_mi_val_18
WHERE interface_athletic_prg_id = ath_prg_rec.interface_athletic_prg_id;
l_error_code := 'E100'; -- Could not update Person Athletics Program Details
UPDATE igs_pe_ath_prg_int
SET status = l_status,
error_code = l_error_code
WHERE interface_athletic_prg_id = ath_prg_rec.interface_athletic_prg_id;
||' Exception from igs_pe_athletic_prg_Pkg.Update_Row '
|| ' INTERFACE_ATHLETIC_PRG_ID : ' ||
(ath_prg_rec.interface_athletic_prg_id) ||
' Status : ' || '3' || ' ErrorCode : ' ||
l_error_code||' SQLERRM:' || SQLERRM;