[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Included code to update igs_ad_reladdr_int.status after successful import
npalanis 6-JAN-2003 Bug : 2734697
code added to commit after import of every
100 records .New variable l_processed_records added
rrengara 16-DEC-2002 Bug 2693734, 2696082, 2694051, 2692214 fixes
gmuralid 26-NOV-2002 BUG 2466674 -- V2API uptake
Changed reference of HZ_CONTACT_POINT_PUB
TO HZ_CONTACT_POINT_V2PUB for create
and update of contact points
gmuralid 27-NOV-2002 BUG 2676422 -- commented created_by_module := 'IGS' in update
call of contact point
pkpatel 23-DEC-2002 Bug No: 2722027
Moved the code of Person special needs to IGSAD89B.pls
gmaheswa 10-NOV-2003 Bug 3223043 HZ.K Impact changes
pkpatel 11-DEC-2003 Bug 3311720 (Added the Date validations in Relation Address processing)
gmaheswa 15-DEC-2003 Bug 3316838 Removed code related to date overlap under same employer or employer party number.
asbala 10-MAR-2004 Bug 3484532 (Removed the check for person already exists in HR. All check will happen from TCA)
asbala 15-APR-2004 3349171: Incorrect usage of fnd_lookup_values view
akadam.in 21-SEP-2004 Academic History LOV Build
skpandey 21-OCT-2004 Bug: 4691121
Description: Sync the changes made in version 115.124 from 115.123 which was mistakenly overridden by 115.125 version
pkpatel 17-Jan-2006 Bug 4937960 (R12: SWS Performance repository violation deliverables)
gmaheswa 27-Jan-2006 Bug: 4938278: crt_rel_adr: Call IGS_PE_WF_GEN. ADDR_BULK_SYNCHRONIZATION to raise bulk
|| address change notification after process address records of all relationships.
|| gmaheswa 22-Jun-06 Bug 5189180: modified CREATE_ADDRESS,Update_address to log error code E073, if address created with warning.
*/
PROCEDURE Prc_Pe_Relns (
p_batch_id IN NUMBER,
p_source_type_id IN NUMBER )
AS
l_prog_label VARCHAR2(100);
SELECT mi.*,i.person_id
FROM igs_ad_relations_int_all mi, igs_ad_interface_all i
WHERE mi.interface_run_id = cp_interface_run_id
AND mi.interface_id = i.interface_id
AND i.interface_run_id = cp_interface_run_id
AND mi.status = '2';
SELECT include_ind
FROM igs_ad_source_cat
WHERE CATEGORY_NAME = p_cat_name
AND SOURCE_TYPE_ID = p_src_typ_id;
SELECT count(*)
FROM HZ_RELATIONSHIPS
WHERE subject_id = cp_person_Id
AND object_id = cp_rel_person_id
AND RELATIONSHIP_TYPE = cp_relationship_type
AND RELATIONSHIP_CODE = cp_relationship_code
AND ( SYSDATE BETWEEN START_DATE AND END_DATE )
AND STATUS = 'A';
SELECT argument3
FROM FND_CONCURRENT_REQUESTS
WHERE request_id = p_request_id;
|| asbala 12-nov-03 3227107: address changes - signature of igs_pe_person_addr_pkg.insert_row and update_row changed
|| asbala 15-APR-2004 3349171: Incorrect usage of fnd_lookup_values view
|| (reverse chronological order - newest change first)
*/
--1. Validate the RELATIONSHIP_TYPE and RELATIONSHIP_CODE -- ssawhney PE CCR 2203778
-- OSS will not allow creation of relations between org and person through import process at this moment.
CURSOR Relationship_Type_C(cp_lookup_type fnd_lookup_values.lookup_type%TYPE,
cp_subject_type hz_relationship_types.subject_type%TYPE,
cp_object_type hz_relationship_types.object_type%TYPE,
cp_appl_id fnd_lookup_values.view_application_id%TYPE,
cp_relationship_code P_Relations_Rec.Relationship_code%TYPE,
cp_relationship_type P_Relations_Rec.Relationship_type%TYPE,
cp_security_group_id fnd_lookup_values.security_group_id%TYPE) IS
SELECT COUNT(1)
FROM FND_LOOKUP_VALUES lk, hz_relationship_types hz
WHERE hz.forward_rel_code= cp_relationship_code AND
hz.relationship_type = cp_relationship_type AND
lk.LOOKUP_TYPE = cp_lookup_type AND
lk.lookup_code = hz.forward_rel_code AND
hz.subject_type = cp_subject_type AND
hz.object_type= cp_object_type AND
hz.STATUS='A' AND
lk.ENABLED_FLAG='Y' AND
lk.VIEW_APPLICATION_ID = cp_appl_id AND
lk.language = USERENV('LANG') AND
lk.security_group_id = cp_security_group_id;
SELECT 'Y'
FROM HZ_PARTIES
WHERE party_id = p_rel_person_id ;
l_last_update_date DATE;
UPDATE IGS_AD_RELATIONS_INT_ALL
SET ERROR_CODE = 'E171',
STATUS = l_Status
WHERE INTERFACE_RELATIONS_ID = P_Relations_Rec.Interface_Relations_Id;
UPDATE IGS_AD_RELATIONS_INT_ALL
SET ERROR_CODE = 'E239',
STATUS = l_Status
WHERE INTERFACE_RELATIONS_ID = P_Relations_Rec.Interface_Relations_Id;
UPDATE igs_ad_relations_int_all
SET ERROR_CODE = 'E240',
STATUS = l_Status
WHERE INTERFACE_RELATIONS_ID = P_Relations_Rec.Interface_Relations_Id;
UPDATE igs_ad_relations_int_all
SET STATUS = '3',ERROR_CODE = 'E204'
WHERE INTERFACE_RELATIONS_ID = P_Relations_Rec.INTERFACE_RELATIONS_ID;
UPDATE igs_ad_relations_int_all
SET ERROR_CODE = 'E201',
STATUS = l_Status
WHERE INTERFACE_RELATIONS_ID = P_Relations_Rec.Interface_Relations_Id;
UPDATE igs_ad_relations_int_all
SET ERROR_CODE = 'E202',
STATUS = l_Status
WHERE INTERFACE_RELATIONS_ID = P_Relations_Rec.Interface_Relations_Id;
UPDATE igs_ad_relations_int_all
SET ERROR_CODE = l_err_cd,
STATUS = l_Status
WHERE INTERFACE_RELATIONS_ID = P_Relations_Rec.Interface_Relations_Id;
UPDATE igs_ad_relations_int_all
SET ERROR_CODE = l_err_cd,
STATUS = l_Status
WHERE INTERFACE_RELATIONS_ID = P_Relations_Rec.Interface_Relations_Id;
IGS_PE_PERSON_PKG.INSERT_ROW(
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data,
X_RETURN_STATUS => l_return_status,
X_ROWID => l_rowid,
X_PERSON_ID => l_person_id,
X_PERSON_NUMBER => l_person_number,
X_SURNAME => p_relations_rec.surname,
X_MIDDLE_NAME => NULL,
X_GIVEN_NAMES => p_relations_rec.given_names,
X_SEX => p_relations_rec.sex,
X_TITLE => p_relations_rec.title,
X_STAFF_MEMBER_IND => NULL,
X_DECEASED_IND => P_Relations_Rec.deceased,
X_SUFFIX => NULL,
X_PRE_NAME_ADJUNCT => p_relations_rec.pre_name_adjunct,
X_ARCHIVE_EXCLUSION_IND => 'N',
X_ARCHIVE_DT => NULL,
X_PURGE_EXCLUSION_IND => 'N',
X_PURGE_DT => NULL,
X_DECEASED_DATE => p_relations_rec.deceased_date,
X_PROOF_OF_INS => 'N',
X_PROOF_OF_IMMU => 'N',
X_BIRTH_DT =>p_relations_rec.birth_dt,
X_SALUTATION => NULL,
X_ORACLE_USERNAME => NULL,
X_PREFERRED_GIVEN_NAME => P_Relations_Rec.preferred_given_name,
X_EMAIL_ADDR => NULL,
X_LEVEL_OF_QUAL_ID => NULL,
X_MILITARY_SERVICE_REG =>'N',
X_VETERAN => 'VETERAN_NOT', -- ssawhney now a lookup code 2203778
X_HZ_PARTIES_OVN => l_object_version_number,
X_ATTRIBUTE_CATEGORY => NULL,
X_ATTRIBUTE1 => NULL,
X_ATTRIBUTE2 => NULL,
X_ATTRIBUTE3 => NULL,
X_ATTRIBUTE4 => NULL,
X_ATTRIBUTE5 => NULL,
X_ATTRIBUTE6 => NULL,
X_ATTRIBUTE7 => NULL,
X_ATTRIBUTE8 => NULL,
X_ATTRIBUTE9 => NULL,
X_ATTRIBUTE10 => NULL,
X_ATTRIBUTE11 => NULL,
X_ATTRIBUTE12 => NULL,
X_ATTRIBUTE13 => NULL,
X_ATTRIBUTE14 => NULL,
X_ATTRIBUTE15 => NULL,
X_ATTRIBUTE16 => NULL,
X_ATTRIBUTE17 => NULL,
X_ATTRIBUTE18 => NULL,
X_ATTRIBUTE19 => NULL,
X_ATTRIBUTE20 => NULL,
X_PERSON_ID_TYPE => NULL,
X_API_PERSON_ID => NULL,
X_MODE => 'R',
X_ATTRIBUTE21 => NULL,
X_ATTRIBUTE22 => NULL,
X_ATTRIBUTE23 => NULL,
X_ATTRIBUTE24 => NULL
);
UPDATE igs_ad_relations_int_all
SET ERROR_CODE = 'E452',
status = l_status
WHERE interface_relations_id = P_Relations_Rec.Interface_Relations_Id;
UPDATE igs_ad_relations_int_all
SET ERROR_CODE = 'E289',
STATUS = l_Status
WHERE INTERFACE_RELATIONS_ID = P_Relations_Rec.Interface_Relations_Id;
SAVEPOINT before_creatupdate;
igs_pe_relationships_pkg.CREATUPDATE_PARTY_RELATIONSHIP(
p_action => 'INSERT',
p_subject_id => P_Relations_Rec.Person_Id,
p_object_id => l_person_id ,
p_party_relationship_type => P_Relations_Rec.RELATIONSHIP_TYPE,
p_relationship_code => P_Relations_Rec.RELATIONSHIP_CODE,
p_comments => null,
p_start_date => sysdate,
p_end_date => null,
p_last_update_date => l_last_update_date ,
p_return_status => l_return_status ,
p_msg_count => l_msg_count, -- this is coded wrong, it should have been a number
p_msg_data => l_msg_data,
p_party_relationship_id => l_party_relationship_id,
p_party_id => l_party_id,
p_party_number => l_party_number,
p_object_version_number => l_object_verson_number) ;
UPDATE igs_ad_relations_int_all
SET ERROR_CODE = 'E172',
STATUS = '3'
WHERE INTERFACE_RELATIONS_ID = P_Relations_Rec.Interface_Relations_Id;
UPDATE igs_ad_relations_int_all
SET rel_person_id = l_person_id,
status = '1',
ERROR_CODE = NULL
WHERE INTERFACE_RELATIONS_ID = P_Relations_Rec.Interface_Relations_Id;
ROLLBACK TO before_creatupdate;
UPDATE igs_ad_relations_int_all
SET ERROR_CODE = 'E290',
STATUS = '3'
WHERE INTERFACE_RELATIONS_ID = P_Relations_Rec.Interface_Relations_Id;
UPDATE igs_ad_relations_int_all
SET ERROR_CODE = 'E518',
STATUS = l_Status
WHERE INTERFACE_RELATIONS_ID = P_Relations_Rec.Interface_Relations_Id;
UPDATE igs_ad_relations_int_all
SET status = '1',
error_code = NULL
WHERE interface_relations_id = relations_rec.interface_relations_id;
UPDATE igs_ad_relations_int_all
SET status = '3', error_code = 'E518'
WHERE interface_relations_id = relations_rec.interface_relations_id;
SELECT *
FROM igs_ad_reladdr_int_all
WHERE status = '2'
AND interface_relations_id = cp_interface_relations_id;
SELECT *
FROM IGS_PE_ADDR_V
WHERE UPPER(addr_line_1) = UPPER(p_addr_line_1)
AND UPPER(nvl(addr_line_2,'NulL')) = UPPER(nvl(p_addr_line_2,'NulL'))
AND UPPER(nvl(addr_line_3,'NulL')) = UPPER(nvl(p_addr_line_3,'NulL'))
AND UPPER(nvl(addr_line_4,'NulL')) = UPPER(nvl(p_addr_line_4,'NulL'))
AND country_cd = p_country
AND UPPER(nvl(county,'NulL')) = UPPER(nvl(p_county,'NulL'))
AND UPPER(nvl(province,'NulL')) = UPPER(nvl(p_province,'NulL'))
AND UPPER(nvl(city,'NulL')) = UPPER(nvl(p_city,'NulL'))
AND UPPER(nvl(state,'NulL')) = UPPER(nvl(p_state,'NulL'))
AND person_id = P_Rel_Person_Id;
SELECT site_use_type,party_site_use_id,last_update_date
FROM HZ_PARTY_SITE_USES
WHERE PARTY_SITE_ID = p_party_site_id
AND site_use_type = p_site_use_code;
l_last_update_date DATE;
l_site_use_last_update_date DATE;
l_profile_last_update_date DATE;
PROCEDURE Update_Addr(addr_rec addr_c%ROWTYPE , reladdr_rec reladdr_rec_c%ROWTYPE , p_rel_person_id NUMBER) AS
--Check to see if for this address these two site usages exist.
CURSOR site_c (p_party_site_id igs_pe_addr_v.party_site_id%TYPE,
p_site_use_code igs_ad_reladdr_int.site_use_code_1%TYPE ) IS
SELECT site_use_type,party_site_use_id,last_update_date
FROM hz_party_site_uses
WHERE party_site_id = p_party_site_id AND
site_use_type = p_site_use_code;
l_last_update_date DATE;
l_site_use_last_update_date DATE;
l_profile_last_update_date DATE;
l_site_use_last_update_date := NULL;
l_profile_last_update_date := NULL;
IGS_PE_PERSON_ADDR_PKG.UPDATE_ROW(
p_action => NULL,
p_rowid => l_row_id,
p_location_id => l_location_id ,
p_start_dt => nvl(reladdr_rec.start_dt,addr_rec.start_dt) ,
p_end_dt => nvl(reladdr_rec.end_dt,addr_rec.end_dt) ,
p_country => nvl(reladdr_rec.country,addr_rec.country),
p_address_style => addr_rec.address_style,
p_addr_line_1 => nvl(reladdr_rec.addr_line_1,addr_rec.addr_line_1),
p_addr_line_2 => nvl(reladdr_rec.addr_line_2,addr_rec.addr_line_2),
p_addr_line_3 => nvl(reladdr_rec.addr_line_3,addr_rec.addr_line_3),
p_addr_line_4 => nvl(reladdr_rec.addr_line_4,addr_rec.addr_line_4),
p_date_last_verified => addr_rec.date_last_verified,
p_correspondence => nvl(reladdr_rec.correspondence,addr_rec.correspondence),
p_city => nvl(reladdr_rec.city,addr_rec.city),
p_state => nvl(reladdr_rec.state,addr_rec.state),
p_province => nvl(reladdr_rec.province,addr_rec.province),
p_county => nvl(reladdr_rec.county,addr_rec.county),
p_postal_code => nvl(reladdr_rec.postal_code,addr_rec.postal_code),
p_address_lines_phonetic => addr_rec.address_lines_phonetic,
p_delivery_point_code => addr_rec.delivery_point_code,
p_other_details_1 => nvl(reladdr_rec.other_details_1,addr_rec.other_details_1),
p_other_details_2 => nvl(reladdr_rec.other_details_2,addr_rec.other_details_2),
p_other_details_3 => nvl(reladdr_rec.other_details_3,addr_rec.other_details_3),
l_return_status => l_return_status ,
l_msg_data => l_msg_data ,
p_party_id => P_Rel_Person_Id,
p_party_site_id => l_party_site_id,
p_party_type => NULL,
p_last_update_date => l_last_update_date,
p_party_site_ovn => l_party_site_ovn,
p_location_ovn => l_location_ovn,
p_status => addr_rec.status
);
l_label := 'igs.plsql.igs_ad_imp_008.update_addr.exception '||'E014';
UPDATE Igs_Ad_RelAddr_Int_all
SET Error_Code = 'E014',
Status = '3'
WHERE interface_reladdr_id = reladdr_rec.interface_reladdr_id;
l_label := 'igs.plsql.igs_ad_imp_008.update_addr.warning '||'E073';
UPDATE Igs_Ad_RelAddr_Int_all
SET Error_Code = 'E073',
Status = '4'
WHERE interface_reladdr_id = reladdr_rec.interface_reladdr_id;
p_action => 'INSERT',
p_rowid => l_row_id,
p_party_site_use_id => l_party_site_use_id,
p_party_site_id => l_party_site_id,
p_site_use_type => reladdr_rec.site_use_code_1,
p_status => 'A',
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_use_last_update_date,
P_profile_last_update_date => l_profile_last_update_date,
p_hz_party_site_use_ovn => l_object_version_number
);
l_label := 'igs.plsql.igs_ad_imp_008.update_addr.exception1 '||'E244';
UPDATE Igs_Ad_RelAddr_Int_all
SET Error_Code = 'E244',
Status = '3'
WHERE INTERFACE_RELADDR_ID = reladdr_rec.INTERFACE_RELADDR_ID;
p_action => 'INSERT',
p_rowid => l_row_id,
p_party_site_use_id => l_party_site_use_id,
p_party_site_id => l_party_site_id,
p_site_use_type => reladdr_rec.site_use_code_2,
p_status => 'A',
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_use_last_update_date,
P_profile_last_update_date => l_profile_last_update_date,
p_hz_party_site_use_ovn => l_object_version_number
);
l_label := 'igs.plsql.igs_ad_imp_008.update_addr.exception2 '||'E244';
UPDATE Igs_Ad_RelAddr_Int_all
SET Error_Code = 'E244',
Status = l_Status
WHERE INTERFACE_RELADDR_ID = reladdr_rec.INTERFACE_RELADDR_ID;
UPDATE igs_ad_reladdr_int_all
SET error_code = NULL,
match_ind = '18',
status = '1'
WHERE interface_reladdr_id = reladdr_rec.interface_reladdr_id;
l_label := 'igs.plsql.igs_ad_imp_008.crt_rel_adr.update_addr_exception'||'E014';
UPDATE Igs_Ad_RelAddr_Int_all
SET Error_Code = 'E014',
Status = '3'
WHERE Interface_Reladdr_Id = reladdr_rec.Interface_Reladdr_Id;
END Update_Addr;
SELECT territory_short_name
FROM fnd_territories_vl
WHERE territory_code = p_territory_code;
SELECT birth_date
FROM igs_pe_person_base_v
WHERE person_id = cp_person_id;
UPDATE igs_ad_reladdr_int_all
SET error_code = 'E211', status = '3'
WHERE interface_reladdr_id = reladdr_rec.interface_reladdr_id;
UPDATE igs_ad_reladdr_int_all
SET error_code = 'E211', status = '3'
WHERE interface_reladdr_id = reladdr_rec.interface_reladdr_id;
UPDATE igs_ad_reladdr_int_all
SET error_code = 'E449', status = '3'
WHERE interface_reladdr_id = reladdr_rec.interface_reladdr_id;
UPDATE igs_ad_reladdr_int_all
SET error_code = 'E209', status = '3'
WHERE interface_reladdr_id = reladdr_rec.interface_reladdr_id;
UPDATE igs_ad_reladdr_int_all
SET error_code = 'E213', status = '3'
WHERE interface_reladdr_id = reladdr_rec.interface_reladdr_id;
UPDATE igs_ad_reladdr_int_all
SET error_code = 'E407', status = '3'
WHERE interface_reladdr_id = reladdr_rec.interface_reladdr_id;
UPDATE igs_ad_reladdr_int_all
SET error_code = 'E406', status = '3'
WHERE interface_reladdr_id = reladdr_rec.interface_reladdr_id;
UPDATE igs_ad_reladdr_int_all
SET error_code = 'E222', status = '3'
WHERE interface_reladdr_id = reladdr_rec.interface_reladdr_id;
UPDATE Igs_Ad_RelAddr_Int_all
SET status = '3',
ERROR_CODE = 'E695' -- Error code depicting incorrect combination
WHERE match_ind IS NOT NULL
AND status = '2'
AND interface_relations_id = p_interface_relations_id;
UPDATE Igs_Ad_RelAddr_Int_all mi
SET status = '1',
match_ind = '19'
WHERE mi.interface_relations_id = p_interface_relations_id
AND mi.status = '2'
AND EXISTS ( SELECT 1
FROM hz_locations pe, hz_party_sites ps
WHERE ps.party_id = P_Rel_Person_Id
AND ps.location_id = pe.location_id
AND UPPER(pe.address1) = UPPER(mi.addr_line_1)
AND UPPER(NVL(pe.address2,'NulL')) = UPPER(NVL(mi.addr_line_2,'NulL'))
AND UPPER(NVL(pe.address3,'NulL')) = UPPER(NVL(mi.addr_line_3,'NulL'))
AND UPPER(NVL(pe.address4,'NulL')) = UPPER(NVL(mi.addr_line_4,'NulL'))
AND pe.country = UPPER(mi.country)
AND UPPER(NVL(pe.county,'NulL')) = UPPER(NVL(mi.county,'NulL'))
AND UPPER(NVL(mi.province,'NulL')) = UPPER(NVL(pe.province,'NulL'))
AND UPPER(NVL(mi.city,'NulL')) = UPPER(NVL(pe.city,'NulL'))
AND UPPER(NVL(mi.state,'NulL')) = UPPER(NVL(pe.state,'NulL'))
);
UPDATE Igs_Ad_RelAddr_Int_all
SET status = '1'
WHERE interface_relations_id = p_interface_relations_id
AND match_ind IN ('18','19','22','23')
AND status = '2';
UPDATE Igs_Ad_RelAddr_Int_all
SET status = '3',
ERROR_CODE = 'E695'
WHERE interface_relations_id = p_interface_relations_id
AND status = 2
AND (match_ind IS NOT NULL AND match_ind NOT IN ('21','25'));
UPDATE Igs_Ad_RelAddr_Int_all mi
SET status = '1',
match_ind = '23'
WHERE mi.interface_relations_id = p_interface_relations_id
AND mi.match_ind IS NULL
AND mi.status = '2'
AND EXISTS ( SELECT 1
FROM igs_pe_addr_v pe
WHERE UPPER(mi.addr_line_1) = UPPER(pe.addr_line_1)
AND NVL(UPPER(mi.addr_line_2),'NulL') = NVL(UPPER(pe.addr_line_2),'NulL')
AND NVL(UPPER(mi.addr_line_3),'NulL') = NVL(UPPER(pe.addr_line_3),'NulL')
AND NVL(UPPER(mi.addr_line_4),'NulL') = NVL(UPPER(pe.addr_line_4),'NulL')
AND UPPER(mi.country) = UPPER(pe.country_cd)
AND NVL(UPPER(mi.county),'NulL') = NVL(UPPER(pe.county),'NulL')
AND NVL(UPPER(mi.province),'NulL') = NVL(UPPER(pe.province),'NulL')
AND NVL(UPPER(mi.city),'NulL') = NVL(UPPER(pe.city),'NulL')
AND NVL(UPPER(mi.state),'NulL') = NVL(UPPER(pe.state),'NulL')
AND NVL(TRUNC(mi.start_dt),IGS_GE_DATE.igsdate('4712/12/31')) = NVL(TRUNC(pe.start_dt),IGS_GE_DATE.igsdate('4712/12/31'))
AND NVL(TRUNC(mi.end_dt),IGS_GE_DATE.igsdate('4712/12/31')) = NVL(TRUNC(pe.end_dt),IGS_GE_DATE.igsdate('4712/12/31'))
AND NVL(UPPER(mi.correspondence),'N') = NVL(UPPER(pe.correspondence),'N')
AND NVL(UPPER(mi.postal_code),'NulL') = NVL(UPPER(pe.postal_code),'NulL')
AND NVL(UPPER(mi.other_details_1),'NulL') = NVL(UPPER(pe.other_details_1),'NulL')
AND NVL(UPPER(mi.other_details_2),'NulL') = NVL(UPPER(pe.other_details_2),'NulL')
AND NVL(UPPER(mi.other_details_3),'NulL') = NVL(UPPER(pe.other_details_3),'NulL')
AND pe.person_id = P_Rel_Person_Id
);
UPDATE Igs_Ad_RelAddr_Int_all mi
SET status = '3',
match_ind = '20'
WHERE mi.interface_relations_id = p_interface_relations_id
AND mi.match_ind IS NULL
AND mi.status = '2'
AND EXISTS (SELECT 1
FROM hz_locations pe, hz_party_sites ps
WHERE ps.party_id = P_Rel_Person_Id
AND ps.location_id = pe.location_id
AND UPPER(pe.address1) = UPPER(mi.addr_line_1)
AND UPPER(NVL(pe.address2,'NulL')) = UPPER(NVL(mi.addr_line_2,'NulL'))
AND UPPER(NVL(pe.address3,'NulL')) = UPPER(NVL(mi.addr_line_3,'NulL'))
AND UPPER(NVL(pe.address4,'NulL')) = UPPER(NVL(mi.addr_line_4,'NulL'))
AND pe.country = UPPER(mi.country)
AND UPPER(NVL(pe.county,'NulL')) = UPPER(NVL(mi.county,'NulL'))
AND UPPER(NVL(mi.province,'NulL')) = UPPER(NVL(pe.province,'NulL'))
AND UPPER(NVL(mi.city,'NulL')) = UPPER(NVL(pe.city,'NulL'))
AND UPPER(NVL(mi.state,'NulL')) = UPPER(NVL(pe.state,'NulL'))
);
l_last_update_date := addr_rec.last_update_date;
Update_Addr(addr_rec , reladdr_rec , p_rel_person_id);
Update_Addr(addr_rec , reladdr_rec , p_rel_person_id);
IGS_PE_PERSON_ADDR_PKG.INSERT_ROW (
p_action => NULL,
p_rowid => l_row_id,
p_location_id => l_location_id,
p_start_dt => reladdr_rec.start_dt,
p_end_dt => reladdr_rec.end_dt,
p_country => reladdr_rec.country,
p_address_style => NULL,
p_addr_line_1 => reladdr_rec.addr_line_1,
p_addr_line_2 => reladdr_rec.addr_line_2,
p_addr_line_3 => reladdr_rec.addr_line_3,
p_addr_line_4 => reladdr_rec.addr_line_4,
p_date_last_verified => NULL,
p_correspondence => NVL(reladdr_rec.correspondence,'N'),
p_city => reladdr_rec.city,
p_state => reladdr_rec.state,
p_province => reladdr_rec.province,
p_county => reladdr_rec.county,
p_postal_code => reladdr_rec.postal_code,
p_address_lines_phonetic => NULL,
p_delivery_point_code => NULL,
p_other_details_1 => reladdr_rec.other_details_1,
p_other_details_2 => reladdr_rec.other_details_2,
p_other_details_3 => reladdr_rec.other_details_3,
l_return_status => l_return_status,
l_msg_data => l_msg_data,
p_party_id => p_rel_person_id,
p_party_site_id => l_party_site_id,
p_party_type => NULL,
p_last_update_date => l_last_update_date,
p_party_site_ovn => l_party_site_ovn,
p_location_ovn => l_location_ovn,
p_status => 'A'
);
UPDATE Igs_Ad_RelAddr_Int_all
SET Error_Code = 'E322',
Status = '3'
WHERE INTERFACE_RELADDR_ID = reladdr_rec.INTERFACE_RELADDR_ID;
UPDATE Igs_Ad_RelAddr_Int_all
SET Error_Code = 'E073',
Status = '4'
WHERE INTERFACE_RELADDR_ID = reladdr_rec.INTERFACE_RELADDR_ID;
p_action => 'INSERT',
p_rowid => l_row_id,
p_party_site_use_id => l_party_site_use_id,
p_party_site_id => l_party_site_id,
p_site_use_type => reladdr_rec.site_use_code_1,
p_status => 'A',
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_use_last_update_date,
P_profile_last_update_date => l_profile_last_update_date,
p_hz_party_site_use_ovn => l_object_version_number
);
UPDATE Igs_Ad_RelAddr_Int_all
SET Error_Code = 'E322',
Status = '3'
WHERE INTERFACE_RELADDR_ID = reladdr_rec.INTERFACE_RELADDR_ID;
p_action => 'INSERT',
p_rowid => l_row_id,
p_party_site_use_id => l_party_site_use_id,
p_party_site_id => l_party_site_id,
p_site_use_type => reladdr_rec.site_use_code_2,
p_status => 'A',
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_use_last_update_date,
P_profile_last_update_date => l_profile_last_update_date,
p_hz_party_site_use_ovn => l_object_version_number
);
UPDATE Igs_Ad_RelAddr_Int_all
SET Error_Code = 'E244',
Status = '3'
WHERE INTERFACE_RELADDR_ID = reladdr_rec.INTERFACE_RELADDR_ID;
UPDATE igs_ad_reladdr_int_all
SET error_code = NULL,
status = '1'
WHERE interface_reladdr_id = reladdr_rec.interface_reladdr_id;
UPDATE Igs_Ad_RelAddr_Int_all
SET Error_Code = 'E518',
Status = '3'
WHERE Interface_Reladdr_Id = reladdr_rec.Interface_Reladdr_Id;
SELECT *
FROM igs_ad_rel_con_int_all
WHERE status = '2'
AND interface_relations_id = cp_interface_relations_id;
SELECT contact_point_id,
primary_flag ,
email_format ,
phone_country_code ,
phone_line_type ,
phone_area_code ,
phone_number ,
phone_extension
FROM hz_contact_points
WHERE owner_table_id = p_rel_person_id
AND contact_point_type = p_contact_point_type
AND owner_table_name = 'HZ_PARTIES'
AND (( nvl(email_format,'NulL') = nvl(p_email_format,'NulL') AND
UPPER(nvl(email_address,'NulL')) = UPPER(nvl(p_email_address,'NulL')) AND
contact_point_type = 'EMAIL' )OR
( nvl(phone_line_type,'NulL') = nvl(p_phone_line_type,'NulL') AND
nvl(phone_country_code,'NulL') = nvl(p_phone_country_code,'NulL') AND
UPPER(nvl(phone_area_code,'NulL')) = UPPER(nvl(p_phone_area_code,'NulL')) AND
nvl(phone_number,'NulL') = nvl(p_phone_number,'NulL') AND
contact_point_type = 'PHONE' ));
SELECT object_version_number
FROM hz_contact_points
WHERE contact_point_id = c_contact_point_id;
l_last_update_date DATE;
l_last_update_date DATE;
UPDATE igs_ad_rel_con_int_all
SET error_code = ln_Error_Code, --error code for the insert failure
status = '3'
WHERE interface_rel_con_id = p_rel_con_rec.interface_rel_con_id;
UPDATE igs_ad_rel_con_int_all
SET status = '1'
WHERE interface_rel_con_id = p_rel_con_rec.interface_rel_con_id;
SELECT 'X'
FROM HZ_PHONE_COUNTRY_CODES
WHERE PHONE_COUNTRY_CODE = p_phone_country_code;
UPDATE igs_ad_rel_con_int_all
SET status = '3',
error_code = 'E246'
WHERE interface_rel_con_id = relcon_rec.interface_rel_con_id;
UPDATE igs_ad_rel_con_int_all
SET error_code = 'E450',
status = '3'
WHERE interface_rel_con_id = relcon_rec.interface_rel_con_id;
UPDATE igs_ad_rel_con_int_all
SET error_code = 'E250',
status = '3'
WHERE interface_rel_con_id = relcon_rec.interface_rel_con_id;
UPDATE igs_ad_rel_con_int_all
SET status = '3',
error_code = 'E247'
WHERE interface_rel_con_id = relcon_rec.interface_rel_con_id;
UPDATE igs_ad_rel_con_int_all
SET status = '3',
error_code = 'E173'
WHERE interface_rel_con_id = relcon_rec.interface_rel_con_id;
UPDATE igs_ad_rel_con_int_all
SET error_code = 'E251',
status = '3'
WHERE interface_rel_con_id = relcon_rec.interface_rel_con_id;
UPDATE igs_ad_rel_con_int_all
SET error_code = 'E248',
status = '3'
WHERE interface_rel_con_id = relcon_rec.interface_rel_con_id;
UPDATE igs_ad_rel_con_int_all
SET status = '3',
ERROR_CODE = 'E695' -- Error code depicting incorrect combination
WHERE match_ind IS NOT NULL
AND status = '2'
AND interface_relations_id = p_interface_relations_id;
UPDATE igs_ad_rel_con_int_all mi
SET status = '1',
match_ind = '19'
WHERE mi.interface_relations_id = p_interface_relations_id
AND mi.status = '2'
AND EXISTS ( SELECT '1'
FROM hz_contact_points pe
WHERE pe.owner_table_id = p_rel_person_id
AND pe.contact_point_type = UPPER(mi.contact_point_type)
AND pe.owner_table_name = 'HZ_PARTIES'
AND (pe.contact_point_type = 'EMAIL' AND
(NVL(pe.email_format,'NulL') = NVL(UPPER(mi.email_format),'NulL') AND
UPPER(NVL(pe.email_address,'NulL')) = UPPER(NVL(mi.email_addrress,'NulL'))
) OR
( pe.contact_point_type = 'PHONE' AND
NVL(pe.phone_line_type,'NulL') = NVL(UPPER(mi.phone_line_type),'NulL') AND
NVL(pe.phone_country_code,'NulL') = NVL(UPPER(mi.phone_country_code),'NulL') AND
UPPER(NVL(pe.phone_area_code,'NulL')) = UPPER(NVL(mi.phone_area_code,'NulL')) AND
NVL(UPPER(pe.phone_number),'NulL') = NVL(UPPER(mi.phone_number),'NulL')
))
);
UPDATE igs_ad_rel_con_int_all
SET status = '1'
WHERE interface_relations_id = p_interface_relations_id
AND match_ind IN ('18','19','22','23')
AND status = '2';
UPDATE igs_ad_rel_con_int_all
SET status = '3',
ERROR_CODE = 'E695'
WHERE interface_relations_id = p_interface_relations_id
AND status = '2'
AND (match_ind IS NOT NULL AND match_ind NOT IN ('21','25'));
UPDATE igs_ad_rel_con_int_all mi
SET status = '1',
match_ind = '23'
WHERE mi.interface_relations_id = p_interface_relations_id
AND mi.match_ind IS NULL
AND mi.status = '2'
AND EXISTS ( SELECT 1
FROM hz_contact_points pe
WHERE pe.owner_table_id = p_rel_person_id
AND NVL(pe.primary_flag,'N') = NVL(UPPER(mi.primary_flag),'N')
AND NVL(UPPER(pe.phone_extension),'NulL') = NVL(UPPER(mi.phone_extension),'NulL')
AND pe.contact_point_type = UPPER(mi.contact_point_type)
AND NVL(pe.email_format,'NulL') = NVL(UPPER(mi.email_format),'NulL')
AND NVL(pe.phone_line_type,'NulL') = NVL(UPPER(mi.phone_line_type),'NulL')
AND NVL(pe.phone_country_code,'NulL') = NVL(UPPER(mi.phone_country_code),'NulL')
AND NVL(UPPER(pe.phone_area_code),'NulL') = NVL(UPPER(mi.phone_area_code),'NulL')
AND NVL(UPPER(pe.phone_number),'NulL') = NVL(UPPER(mi.phone_number),'NulL')
AND NVL(UPPER(pe.email_address),'NulL') = NVL(UPPER(mi.email_addrress),'NulL')
);
UPDATE igs_ad_rel_con_int_all mi
SET status = '3',
match_ind = '20',
DUP_CONTACT_POINT_ID = (SELECT pe.contact_point_id
FROM hz_contact_points pe
WHERE pe.owner_table_id = p_rel_person_id
AND pe.contact_point_type = UPPER(mi.contact_point_type)
AND pe.owner_table_name = 'HZ_PARTIES'
AND ((pe.contact_point_type = 'EMAIL' AND
NVL(pe.email_format,'NulL') = NVL(UPPER(mi.email_format),'NulL') AND
UPPER(NVL(pe.email_address,'NulL')) = UPPER(NVL(mi.email_addrress,'NulL'))
)
OR
(pe.contact_point_type = 'PHONE' AND
NVL(pe.phone_line_type,'NulL') = NVL(UPPER(mi.phone_line_type),'NulL') AND
NVL(pe.phone_country_code,'NulL') = NVL(UPPER(mi.phone_country_code),'NulL') AND
UPPER(NVL(pe.phone_area_code,'NulL')) = UPPER(NVL(mi.phone_area_code,'NulL')) AND
NVL(UPPER(pe.phone_number),'NulL') = NVL(UPPER(mi.phone_number),'NulL')
))
AND ROWNUM = 1)
WHERE mi.interface_relations_id = p_interface_relations_id
AND mi.match_ind IS NULL
AND mi.status = '2'
AND EXISTS (SELECT '1'
FROM hz_contact_points pe
WHERE pe.owner_table_id = p_rel_person_id
AND pe.contact_point_type = UPPER(mi.contact_point_type)
AND pe.owner_table_name = 'HZ_PARTIES'
AND (pe.contact_point_type = 'EMAIL' AND
(NVL(pe.email_format,'NulL') = NVL(UPPER(mi.email_format),'NulL') AND
UPPER(NVL(pe.email_address,'NulL')) = UPPER(NVL(mi.email_addrress,'NulL'))
)
OR
(pe.contact_point_type = 'PHONE' AND
NVL(pe.phone_line_type,'NulL') = NVL(UPPER(mi.phone_line_type),'NulL') AND
NVL(pe.phone_country_code,'NulL') = NVL(UPPER(mi.phone_country_code),'NulL') AND
UPPER(NVL(pe.phone_area_code,'NulL')) = UPPER(NVL(mi.phone_area_code,'NulL')) AND
NVL(UPPER(pe.phone_number),'NulL') = NVL(UPPER(mi.phone_number),'NulL')
))
);
HZ_CONTACT_POINT_V2PUB.update_contact_point(
p_init_msg_list => FND_API.G_FALSE,
p_contact_point_rec => contact_point_rec,
p_email_rec => email_rec ,
p_phone_rec => 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_rel_con_int_all
SET status = '1',
match_ind = '18'
WHERE interface_rel_con_id = relcon_rec.interface_rel_con_id;
UPDATE igs_ad_rel_con_int_all
SET status = '3',
error_code = 'E014'
WHERE interface_rel_con_id = relcon_rec.interface_rel_con_id;
HZ_CONTACT_POINT_V2PUB.update_contact_point(
p_init_msg_list => FND_API.G_FALSE,
p_contact_point_rec => contact_point_rec,
p_email_rec => email_rec ,
p_phone_rec => 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_rel_con_int_all
SET status = '3',
ERROR_CODE = 'E014'
WHERE interface_rel_con_id = relcon_rec.interface_rel_con_id;
UPDATE igs_ad_rel_con_int_all
SET status = '1',
ERROR_CODE = NULL,
match_ind = '18'
WHERE interface_rel_con_id = relcon_rec.interface_rel_con_id;
UPDATE igs_ad_rel_con_int_all
SET status = '3',
error_code = 'E518'
WHERE interface_rel_con_id = relcon_rec.interface_rel_con_id;
SELECT *
FROM Igs_Ad_Relacad_Int_all
WHERE Status = '2'
AND Interface_Relations_Id = cP_Interface_Relations_Id;
SELECT *
FROM IGS_AD_ACAD_HISTORY_V a
WHERE a.institution_code = P_Institution_Code
AND ((TRUNC(a.Start_Date) = TRUNC(P_Start_Date)) OR (a.start_date IS NULL AND P_Start_Date IS NULL))
AND ((TRUNC(a.end_Date) = TRUNC(p_end_date)) OR (a.end_date IS NULL AND p_end_date IS NULL))
AND a.person_id = P_Rel_Person_ID;
SELECT 'X' a
FROM Igs_Ad_Relacad_Int_all a
WHERE Interface_Relacad_Id = cp_Interface_Relacad_Id
AND NVL(p_start_date,p_end_date) IS NOT NULL
AND EXISTS (SELECT 1 FROM hz_Education h1, hz_parties h2
WHERE h1.party_id = P_Rel_Person_ID
AND h2.party_number = p_institution_code
AND h2.party_id = h1.school_party_id
AND h1.start_date_attended IS NULL
AND h1.last_date_attended IS NULL
)
AND NOT EXISTS ( SELECT 1 FROM hz_Education h1, hz_parties h2
WHERE h1.party_id = p_rel_person_id
AND h2.party_number = p_institution_code
AND h2.party_id = h1.school_party_id
AND NVL(h1.start_date_attended,
h1.last_date_attended) IS NOT NULL
);
SELECT 1 a
FROM hz_Education h1, hz_parties h2
WHERE h1.party_id = P_Rel_Person_ID
AND h2.party_number = p_institution_code
AND h2.party_id = h1.school_party_id
AND NVL(h1.start_date_attended,
h1.last_date_attended) IS NOT NULL;
SELECT ah.rowid, ah.*
FROM igs_ad_acad_history_v ah
WHERE person_id = P_Rel_Person_ID
AND institution_code = Relacad_Rec.institution_code
AND creation_date = ( SELECT min(creation_date) FROM igs_ad_acad_history_v
WHERE person_id = P_Rel_Person_ID
AND institution_code = Relacad_Rec.institution_code);
SELECT 1
FROM igs_pe_hz_parties pzp, hz_parties hp, Igs_Or_Inst_Stat stat
WHERE pzp.party_id = hp.party_id AND
hp.party_number = cp_institution_cd AND
pzp.OI_INSTITUTION_STATUS = stat.Institution_Status AND
pzp.inst_org_ind = 'I' AND
stat.S_Institution_Status = cp_inst_status;
SELECT dg.degree_cd
FROM igs_ps_degrees dg,
igs_ps_type_all ps
WHERE dg.degree_cd = p_degree
AND dg.closed_ind ='N'
AND dg.program_type = ps.course_type;
SELECT birth_date
FROM igs_pe_person_base_v
WHERE person_id = cp_person_id;
UPDATE Igs_Ad_Relacad_Int_all
SET Error_Code = 'E396',
Status = l_Status
WHERE Interface_Relacad_Id = Relacad_Rec.Interface_Relacad_Id;
UPDATE Igs_Ad_Relacad_Int_all
SET Error_Code = 'E401',
Status = l_Status
WHERE Interface_Relacad_Id = Relacad_Rec.Interface_Relacad_Id;
UPDATE Igs_Ad_Relacad_Int_all
SET Error_Code = 'E402',
Status = l_Status
WHERE Interface_Relacad_Id = Relacad_Rec.Interface_Relacad_Id;
UPDATE Igs_Ad_Relacad_Int_all
SET Error_Code = 'E403',
Status = l_Status
WHERE Interface_Relacad_Id = Relacad_Rec.Interface_Relacad_Id;
UPDATE Igs_Ad_Relacad_Int_all
SET Error_Code = 'E453',
Status = l_Status
WHERE Interface_Relacad_Id = Relacad_Rec.Interface_Relacad_Id;
UPDATE Igs_Ad_Relacad_Int_all
SET Error_Code = 'E448',
Status = l_Status
WHERE Interface_Relacad_Id = Relacad_Rec.Interface_Relacad_Id;
UPDATE Igs_Ad_Relacad_Int_all
SET Error_Code = 'E405',
Status = l_Status
WHERE Interface_Relacad_Id = Relacad_Rec.Interface_Relacad_Id;
UPDATE Igs_Ad_Relacad_Int_all
SET Error_Code = 'E406',
Status = l_Status
WHERE Interface_Relacad_Id = Relacad_Rec.Interface_Relacad_Id;
UPDATE Igs_Ad_Relacad_Int_all
SET Error_Code = 'E407',
Status = l_Status
WHERE Interface_Relacad_Id = Relacad_Rec.Interface_Relacad_Id;
UPDATE Igs_Ad_Relacad_Int_all
SET Error_Code = 'E408',
Status = l_Status
WHERE Interface_Relacad_Id = Relacad_Rec.Interface_Relacad_Id;
UPDATE Igs_Ad_Relacad_Int_all
SET Error_Code = 'E222',
Status = l_Status
WHERE Interface_Relacad_Id = Relacad_Rec.Interface_Relacad_Id;
UPDATE Igs_Ad_Relacad_Int_all
SET status = '3',
ERROR_CODE = 'E695' -- Error code depicting incorrect combination
WHERE match_ind IS NOT NULL
AND status = '2'
AND Interface_Relations_Id = P_Interface_Relations_Id;
UPDATE Igs_Ad_Relacad_Int_all mi
SET status = '1',
match_ind = '19'
WHERE mi.Interface_Relations_Id = P_Interface_Relations_Id
AND mi.status = '2'
AND EXISTS ( SELECT 1
FROM Igs_Ad_Acad_History_V pe
WHERE pe.Institution_Code = mi.institution_code
AND ((TRUNC(pe.Start_Date) = TRUNC(mi.Start_Date)) OR (pe.start_date IS NULL AND mi.Start_date IS NULL))
AND ((TRUNC(pe.end_Date) = TRUNC(mi.end_date)) OR (pe.end_date IS NULL AND mi.end_date IS NULL))
AND pe.person_id = P_Rel_Person_ID);
UPDATE Igs_Ad_Relacad_Int_all
SET status = '1'
WHERE Interface_Relations_Id = P_Interface_Relations_Id
AND match_ind IN ('18','19','22','23')
AND status = '2';
UPDATE Igs_Ad_Relacad_Int_all
SET status = '3',
ERROR_CODE = 'E695'
WHERE Interface_Relations_Id = P_Interface_Relations_Id
AND status = '2'
AND (match_ind IS NOT NULL AND match_ind NOT IN ('21','25'));
UPDATE Igs_Ad_Relacad_Int_all mi
SET status = '1',
match_ind = '23'
WHERE mi.Interface_Relations_Id = P_Interface_Relations_Id
AND mi.match_ind IS NULL
AND mi.status = '2'
AND EXISTS ( SELECT '1'
FROM Igs_Ad_Acad_History_V pe
WHERE pe.person_id = P_Rel_Person_ID
AND UPPER(pe.current_inst) = UPPER(mi.current_inst)
AND ( (TRUNC(pe.Start_Date) = TRUNC(mi.Start_Date)) OR (pe.start_date IS NULL AND mi.Start_date is null) )
AND ( (UPPER(pe.program_code) = UPPER(mi.program_code)) OR (pe.program_code IS NULL AND mi.program_code is null) )
AND ( (UPPER(pe.degree_attempted) = UPPER(mi.degree_attempted)) or (pe.degree_attempted is null and mi.degree_attempted is null) )
AND ( (UPPER(pe.degree_earned) = UPPER(mi.degree_earned)) OR (pe.degree_earned is null and mi.degree_earned is null) )
AND ( (UPPER(pe.comments) = UPPER(mi.comments)) or (pe.comments is null and mi.comments is null) )
AND ( (TRUNC(pe.planned_completion_date) = TRUNC(mi.plan_completion_date)) or (pe.planned_completion_date is null and mi.plan_completion_date is null) )
AND UPPER(pe.Institution_Code) = UPPER(mi.Institution_Code)
);
UPDATE Igs_Ad_Relacad_Int_all mi
SET status = '3',
match_ind = '20'
WHERE mi.Interface_Relations_Id = P_Interface_Relations_Id
AND mi.match_ind IS NULL
AND mi.status = '2'
AND EXISTS ( SELECT 1
FROM Igs_Ad_Acad_History_V pe
WHERE pe.Institution_Code = mi.institution_code
AND ((TRUNC(pe.Start_Date) = TRUNC(mi.Start_Date)) OR (pe.start_date IS NULL AND mi.Start_date IS NULL))
AND ((TRUNC(pe.end_Date) = TRUNC(mi.end_date)) OR (pe.end_date IS NULL AND mi.end_date IS NULL))
AND pe.person_id = P_Rel_Person_ID);
Igs_Ad_Acad_History_Pkg.update_row(
x_rowid => l_row_id,
x_recalc_total_cp_earned => l_Academic_His_Rec.recalc_total_cp_earned,
x_recalc_total_cp_attempted => l_Academic_His_Rec.recalc_total_cp_attempted,
x_recalc_total_unit_gp => l_Academic_His_Rec.recalc_total_unit_gp,
x_recalc_tot_gpa_units_attemp => l_Academic_His_Rec.recalc_total_gpa_units_attemp,
x_recalc_inst_gpa => l_Academic_His_Rec.recalc_inst_gpa,
x_recalc_grading_scale_id => l_Academic_His_Rec.recalc_grading_scale_id,
x_selfrep_total_cp_attempted => l_Academic_His_Rec.selfrep_total_cp_attempted,
x_selfrep_total_cp_earned => l_Academic_His_Rec.selfrep_total_cp_earned,
x_selfrep_total_unit_gp => l_Academic_His_Rec.selfrep_total_unit_gp,
x_selfrep_tot_gpa_uts_attemp => l_Academic_His_Rec.selfrep_total_gpa_units_attemp,
x_selfrep_inst_gpa => l_Academic_His_Rec.selfrep_inst_gpa,
x_selfrep_grading_scale_id => l_Academic_His_Rec.selfrep_grading_scale_id,
x_selfrep_weighted_gpa => l_Academic_His_Rec.selfrep_weighted_gpa,
x_selfrep_rank_in_class => l_Academic_His_Rec.selfrep_rank_in_class,
x_selfrep_weighed_rank => l_Academic_His_Rec.selfrep_weighed_rank,
x_selfrep_class_size => l_Academic_His_Rec.selfrep_class_size, -- x_hz_acad_hist_id => l_Academic_His_Rec.hz_acad_hist_id,
x_attribute_category => l_Academic_His_Rec.attribute_category,
x_attribute1 => l_Academic_His_Rec.attribute1,
x_attribute2 => l_Academic_His_Rec.attribute2,
x_attribute3 => l_Academic_His_Rec.attribute3,
x_attribute4 => l_Academic_His_Rec.attribute4,
x_attribute5 => l_Academic_His_Rec.attribute5,
x_attribute6 => l_Academic_His_Rec.attribute6,
x_attribute7 => l_Academic_His_Rec.attribute7,
x_attribute8 => l_Academic_His_Rec.attribute8,
x_attribute9 => l_Academic_His_Rec.attribute9,
x_attribute10 => l_Academic_His_Rec.attribute10,
x_attribute11 => l_Academic_His_Rec.attribute11,
x_attribute12 => l_Academic_His_Rec.attribute12,
x_attribute13 => l_Academic_His_Rec.attribute13,
x_attribute14 => l_Academic_His_Rec.attribute14,
x_attribute15 => l_Academic_His_Rec.attribute15,
x_attribute16 => l_Academic_His_Rec.attribute16,
x_attribute17 => l_Academic_His_Rec.attribute17,
x_attribute18 => l_Academic_His_Rec.attribute18,
x_attribute19 => l_Academic_His_Rec.attribute19,
x_attribute20 => l_Academic_His_Rec.attribute20,
x_type_of_school => l_Academic_His_Rec.type_of_school,
x_institution_code => NVL(Relacad_Rec.institution_code,l_Academic_His_Rec.institution_code),
x_education_id => l_education_id,
x_person_id => l_Academic_His_Rec.person_id,
x_current_inst => NVL(Relacad_Rec.current_inst,l_Academic_His_Rec.current_inst),
x_degree_attempted => NVL(Relacad_Rec.degree_attempted,l_Academic_His_Rec.degree_attempted),
x_program_code => NVL(Relacad_Rec.program_code,l_Academic_His_Rec.program_code ),
x_degree_earned => NVL(Relacad_Rec.degree_earned,l_Academic_His_Rec.degree_earned),
x_comments => NVL(Relacad_Rec.comments,l_Academic_His_Rec.comments),
x_start_date => NVL(Relacad_Rec.start_date,TRUNC(l_Academic_His_Rec.start_date)),
x_end_date => NVL(Relacad_Rec.end_date,TRUNC(l_Academic_His_Rec.end_date)),
x_planned_completion_date => NVL(Relacad_Rec.plan_completion_date,TRUNC(l_Academic_His_Rec.planned_completion_date)),
x_transcript_required => l_Academic_His_Rec.transcript_required,
x_object_version_number => l_Academic_His_Rec.object_version_number,
x_msg_data => l_msg_data,
x_return_status => l_return_status,
x_mode => 'R'
);
l_debug_str := 'Rule - Import, IGS_AD_IMP_008.crt_rel_acad_his Update Row failed'
|| 'Interface Relacad Id : '
|| (relacad_rec.interface_relacad_id)
|| 'Status : 3' || 'ErrorCode : E014 HzMesg: '||l_msg_data||' SQLERRM:' || SQLERRM;
UPDATE Igs_Ad_Relacad_Int_all
SET Error_Code = 'E014',
Status = '3'
WHERE Interface_Relacad_Id = Relacad_Rec.Interface_Relacad_Id;
UPDATE Igs_Ad_Relacad_Int_all
SET Error_Code = NULL,
Status = '1', Match_Ind = '18'
WHERE Interface_Relacad_Id = Relacad_Rec.Interface_Relacad_Id;
Igs_Ad_Acad_History_Pkg.update_row(
x_rowid => l_row_id,
x_recalc_total_cp_earned => l_Academic_His_Rec.recalc_total_cp_earned,
x_recalc_total_cp_attempted => l_Academic_His_Rec.recalc_total_cp_attempted,
x_recalc_total_unit_gp => l_Academic_His_Rec.recalc_total_unit_gp,
x_recalc_tot_gpa_units_attemp => l_Academic_His_Rec.recalc_total_gpa_units_attemp,
x_recalc_inst_gpa => l_Academic_His_Rec.recalc_inst_gpa,
x_recalc_grading_scale_id => l_Academic_His_Rec.recalc_grading_scale_id,
x_selfrep_total_cp_attempted => l_Academic_His_Rec.selfrep_total_cp_attempted,
x_selfrep_total_cp_earned => l_Academic_His_Rec.selfrep_total_cp_earned,
x_selfrep_total_unit_gp => l_Academic_His_Rec.selfrep_total_unit_gp,
x_selfrep_tot_gpa_uts_attemp => l_Academic_His_Rec.selfrep_total_gpa_units_attemp,
x_selfrep_inst_gpa => l_Academic_His_Rec.selfrep_inst_gpa,
x_selfrep_grading_scale_id => l_Academic_His_Rec.selfrep_grading_scale_id,
x_selfrep_weighted_gpa => l_Academic_His_Rec.selfrep_weighted_gpa,
x_selfrep_rank_in_class => l_Academic_His_Rec.selfrep_rank_in_class,
x_selfrep_weighed_rank => l_Academic_His_Rec.selfrep_weighed_rank,
x_selfrep_class_size => l_Academic_His_Rec.selfrep_class_size, -- x_hz_acad_hist_id => l_Academic_His_Rec.hz_acad_hist_id,
x_attribute_category => l_Academic_His_Rec.attribute_category,
x_attribute1 => l_Academic_His_Rec.attribute1,
x_attribute2 => l_Academic_His_Rec.attribute2,
x_attribute3 => l_Academic_His_Rec.attribute3,
x_attribute4 => l_Academic_His_Rec.attribute4,
x_attribute5 => l_Academic_His_Rec.attribute5,
x_attribute6 => l_Academic_His_Rec.attribute6,
x_attribute7 => l_Academic_His_Rec.attribute7,
x_attribute8 => l_Academic_His_Rec.attribute8,
x_attribute9 => l_Academic_His_Rec.attribute9,
x_attribute10 => l_Academic_His_Rec.attribute10,
x_attribute11 => l_Academic_His_Rec.attribute11,
x_attribute12 => l_Academic_His_Rec.attribute12,
x_attribute13 => l_Academic_His_Rec.attribute13,
x_attribute14 => l_Academic_His_Rec.attribute14,
x_attribute15 => l_Academic_His_Rec.attribute15,
x_attribute16 => l_Academic_His_Rec.attribute16,
x_attribute17 => l_Academic_His_Rec.attribute17,
x_attribute18 => l_Academic_His_Rec.attribute18,
x_attribute19 => l_Academic_His_Rec.attribute19,
x_attribute20 => l_Academic_His_Rec.attribute20,
x_type_of_school => l_Academic_His_Rec.type_of_school,
x_institution_code => NVL(Relacad_Rec.institution_code,l_Academic_His_Rec.institution_code),
x_education_id => l_education_id,
x_person_id => l_Academic_His_Rec.person_id,
x_current_inst => NVL(Relacad_Rec.current_inst,l_Academic_His_Rec.current_inst),
x_degree_attempted => NVL(Relacad_Rec.degree_attempted,l_Academic_His_Rec.degree_attempted),
x_program_code => NVL(Relacad_Rec.program_code,l_Academic_His_Rec.program_code ),
x_degree_earned => NVL(Relacad_Rec.degree_earned,l_Academic_His_Rec.degree_earned),
x_comments => NVL(Relacad_Rec.comments,l_Academic_His_Rec.comments),
x_start_date => NVL(Relacad_Rec.start_date,TRUNC(l_Academic_His_Rec.start_date)),
x_end_date => NVL(Relacad_Rec.end_date,TRUNC(l_Academic_His_Rec.end_date)),
x_planned_completion_date => NVL(Relacad_Rec.plan_completion_date,TRUNC(l_Academic_His_Rec.planned_completion_date)),
x_transcript_required => l_Academic_His_Rec.transcript_required,
x_object_version_number => l_Academic_His_Rec.object_version_number,
x_msg_data => l_msg_data,
x_return_status => l_return_status,
x_mode => 'R'
);
l_debug_str := 'Rule - Import, IGS_AD_IMP_008.crt_rel_acad_his Update Row failed'
|| 'Interface Relacad Id : '
|| (relacad_rec.interface_relacad_id)
|| 'Status : 3' || 'ErrorCode : E014 HzMesg: '||l_msg_data||' SQLERRM:' || SQLERRM;
UPDATE Igs_Ad_Relacad_Int_all
SET Error_Code = 'E014',
Status = '3'
WHERE Interface_Relacad_Id = Relacad_Rec.Interface_Relacad_Id;
UPDATE Igs_Ad_Relacad_Int_all
SET Error_Code = NULL,
Status = '1',
match_ind = '18'
WHERE Interface_Relacad_Id = Relacad_Rec.Interface_Relacad_Id;
Igs_Ad_Acad_History_Pkg.update_row(
x_rowid => l_row_id,
x_recalc_total_cp_earned => dup_cur_first_rec.recalc_total_cp_earned,
x_recalc_total_cp_attempted => dup_cur_first_rec.recalc_total_cp_attempted,
x_recalc_total_unit_gp => dup_cur_first_rec.recalc_total_unit_gp,
x_recalc_tot_gpa_units_attemp => dup_cur_first_rec.recalc_total_gpa_units_attemp,
x_recalc_inst_gpa => dup_cur_first_rec.recalc_inst_gpa,
x_recalc_grading_scale_id => dup_cur_first_rec.recalc_grading_scale_id,
x_selfrep_total_cp_attempted => dup_cur_first_rec.selfrep_total_cp_attempted,
x_selfrep_total_cp_earned => dup_cur_first_rec.selfrep_total_cp_earned,
x_selfrep_total_unit_gp => dup_cur_first_rec.selfrep_total_unit_gp,
x_selfrep_tot_gpa_uts_attemp => dup_cur_first_rec.selfrep_total_gpa_units_attemp,
x_selfrep_inst_gpa => dup_cur_first_rec.selfrep_inst_gpa,
x_selfrep_grading_scale_id => dup_cur_first_rec.selfrep_grading_scale_id,
x_selfrep_weighted_gpa => dup_cur_first_rec.selfrep_weighted_gpa,
x_selfrep_rank_in_class => dup_cur_first_rec.selfrep_rank_in_class,
x_selfrep_weighed_rank => dup_cur_first_rec.selfrep_weighed_rank,
x_selfrep_class_size => dup_cur_first_rec.selfrep_class_size, -- x_hz_acad_hist_id => l_Academic_His_Rec.hz_acad_hist_id,
x_attribute_category => dup_cur_first_rec.attribute_category,
x_attribute1 => dup_cur_first_rec.attribute1,
x_attribute2 => dup_cur_first_rec.attribute2,
x_attribute3 => dup_cur_first_rec.attribute3,
x_attribute4 => dup_cur_first_rec.attribute4,
x_attribute5 => dup_cur_first_rec.attribute5,
x_attribute6 => dup_cur_first_rec.attribute6,
x_attribute7 => dup_cur_first_rec.attribute7,
x_attribute8 => dup_cur_first_rec.attribute8,
x_attribute9 => dup_cur_first_rec.attribute9,
x_attribute10 => dup_cur_first_rec.attribute10,
x_attribute11 => dup_cur_first_rec.attribute11,
x_attribute12 => dup_cur_first_rec.attribute12,
x_attribute13 => dup_cur_first_rec.attribute13,
x_attribute14 => dup_cur_first_rec.attribute14,
x_attribute15 => dup_cur_first_rec.attribute15,
x_attribute16 => dup_cur_first_rec.attribute16,
x_attribute17 => dup_cur_first_rec.attribute17,
x_attribute18 => dup_cur_first_rec.attribute18,
x_attribute19 => dup_cur_first_rec.attribute19,
x_attribute20 => dup_cur_first_rec.attribute20,
x_type_of_school => dup_cur_first_rec.type_of_school,
x_institution_code => NVL(Relacad_Rec.institution_code,dup_cur_first_rec.institution_code),
x_education_id => l_education_id,
x_person_id => dup_cur_first_rec.person_id,
x_current_inst => NVL(Relacad_Rec.current_inst,dup_cur_first_rec.current_inst),
x_degree_attempted => NVL(Relacad_Rec.degree_attempted,dup_cur_first_rec.degree_attempted),
x_program_code => NVL(Relacad_Rec.program_code,dup_cur_first_rec.program_code ),
x_degree_earned => NVL(Relacad_Rec.degree_earned,dup_cur_first_rec.degree_earned),
x_comments => NVL(Relacad_Rec.comments,dup_cur_first_rec.comments),
x_start_date => NVL(Relacad_Rec.start_date,TRUNC(dup_cur_first_rec.start_date)),
x_end_date => NVL(Relacad_Rec.end_date,TRUNC(dup_cur_first_rec.end_date)),
x_planned_completion_date => NVL(Relacad_Rec.plan_completion_date,TRUNC(dup_cur_first_rec.planned_completion_date)),
x_transcript_required => dup_cur_first_rec.transcript_required,
x_object_version_number => dup_cur_first_rec.object_version_number,
x_msg_data => l_msg_data,
x_return_status => l_return_status,
x_mode => 'R'
);
l_debug_str := 'Rule - Import, IGS_AD_IMP_008.crt_rel_acad_his Update Row failed'
|| 'Interface Relacad Id : '
|| (relacad_rec.interface_relacad_id)
|| 'Status : 3' || 'ErrorCode : E014 HzMesg: '||l_msg_data||' SQLERRM:' || SQLERRM;
UPDATE Igs_Ad_Relacad_Int_all
SET Error_Code = 'E014',
Status = '3'
WHERE Interface_Relacad_Id = Relacad_Rec.Interface_Relacad_Id;
UPDATE Igs_Ad_Relacad_Int_all
SET Error_Code = NULL,
Status = '1',
match_ind = '18'
WHERE Interface_Relacad_Id = Relacad_Rec.Interface_Relacad_Id;
SELECT COUNT(*) INTO l_count
FROM IGS_AD_ACAD_HISTORY_V acad
WHERE acad.person_id = p_rel_person_id
AND acad.institution_code = RELACAD_REC.INSTITUTION_CODE;
UPDATE Igs_Ad_Relacad_Int_all
SET Status = '3', match_ind = '14'
WHERE Interface_Relacad_Id = Relacad_Rec.Interface_Relacad_Id;
UPDATE Igs_Ad_Relacad_Int_all
SET Status = '3', match_ind = '13'
WHERE Interface_Relacad_Id = Relacad_Rec.Interface_Relacad_Id;
Igs_Ad_Acad_History_Pkg.INSERT_ROW (
x_rowid => l_row_id,
x_education_id => l_education_id,
x_person_id => p_rel_person_id,
x_current_inst => relacad_rec.current_inst,
x_degree_attempted => relacad_rec.degree_attempted,
x_program_code => relacad_rec.program_code,
x_degree_earned => relacad_rec.degree_earned,
x_comments => relacad_rec.comments,
x_start_date => relacad_rec.start_date,
x_end_date => relacad_rec.end_date,
x_planned_completion_date => relacad_rec.plan_completion_date,
x_recalc_total_cp_attempted => NULL,
x_recalc_total_cp_earned => NULL,
x_recalc_total_unit_gp => NULL,
x_recalc_tot_gpa_units_attemp => NULL,
x_recalc_inst_gpa => NULL,
x_recalc_grading_scale_id => NULL,
x_selfrep_total_cp_attempted => NULL,
x_selfrep_total_cp_earned => NULL,
x_selfrep_total_unit_gp => NULL,
X_selfrep_tot_gpa_uts_attemp => NULL,
x_selfrep_inst_gpa => NULL,
x_selfrep_grading_scale_id => NULL,
x_selfrep_weighted_gpa => NULL,
x_selfrep_rank_in_class => NULL,
x_selfrep_weighed_rank => NULL,
x_type_of_school => NULL,
x_institution_code => relacad_rec.institution_code,
x_attribute_category => NULL,
x_attribute1 => NULL,
x_attribute2 => NULL,
x_attribute3 => NULL,
x_attribute4 => NULL,
x_attribute5 => NULL,
x_attribute6 => NULL,
x_attribute7 => NULL,
x_attribute8 => NULL,
x_attribute9 => NULL,
x_attribute10 => NULL,
x_attribute11 => NULL,
x_attribute12 => NULL,
x_attribute13 => NULL,
x_attribute14 => NULL,
x_attribute15 => NULL,
x_attribute16 => NULL,
x_attribute17 => NULL,
x_attribute18 => NULL,
x_attribute19 => NULL,
x_attribute20 => NULL,
x_selfrep_class_size => NULL,
x_transcript_required => NULL,
x_object_version_number => l_object_version_number,
x_msg_data => l_msg_data,
x_return_status => l_return_status,
x_mode => 'R'
) ;
l_debug_str := 'Rule - Import, IGS_AD_IMP_008.crt_rel_acad_his Insert Row failed'
|| 'Interface Relacad Id : '
|| (relacad_rec.interface_relacad_id)
|| 'Status : 3' || 'ErrorCode : E322 HzMesg: '||l_msg_data||' SQLERRM:' || SQLERRM;
UPDATE Igs_Ad_Relacad_Int_all
SET Error_Code = 'E322',
Status = '3'
WHERE Interface_Relacad_Id = Relacad_Rec.Interface_Relacad_Id;
UPDATE Igs_Ad_Relacad_Int_all
SET Error_Code = NULL,
Status = '1'
WHERE Interface_Relacad_Id = Relacad_Rec.Interface_Relacad_Id;
UPDATE igs_ad_relemp_int_all
SET status = '3', error_code = l_error_code
WHERE interface_relemp_id = p_relemp_rec.interface_relemp_id;
Igs_Ad_Emp_Dtl_Pkg.Insert_Row (
X_ROWID => l_RowId,
x_employment_history_id => l_Employment_History_Id,
x_PERSON_ID => p_person_id,
x_START_DT => P_RELEMP_REC.Start_Dt,
x_END_DT => P_RELEMP_REC.End_Dt,
x_TYPE_OF_EMPLOYMENT => P_RELEMP_REC.Type_Of_Employment,
x_FRACTION_OF_EMPLOYMENT => P_RELEMP_REC.Fraction_Of_Employment,
x_TENURE_OF_EMPLOYMENT => P_RELEMP_REC.Tenure_Of_Employment,
x_POSITION => P_RELEMP_REC.Position,
x_OCCUPATIONAL_TITLE_CODE => P_RELEMP_REC.OCCUPATIONAL_TITLE_CODE,
x_OCCUPATIONAL_TITLE => NULL, --P_RELEMP_REC.TITLE,
x_WEEKLY_WORK_HOURS => P_RELEMP_REC.WEEKLY_WORK_HOURS,
x_COMMENTS => P_RELEMP_REC.Comments,
x_EMPLOYER => P_RELEMP_REC.Employer,
x_EMPLOYED_BY_DIVISION_NAME => P_RELEMP_REC.Employed_by_division_name,
x_BRANCH => null,
x_MILITARY_RANK => null,
x_SERVED => null,
x_STATION => null,
x_CONTACT => p_RELEMP_REC.Contact, -- Bug : 2037512
x_msg_data => l_msg_data,
x_return_status => l_return_status,
x_object_version_number => l_object_version_number,
x_employed_by_party_id => l_employer_party_id,
x_reason_for_leaving => p_RELEMP_REC.Reason_for_leaving,
X_MODE => 'R' );
UPDATE igs_ad_relemp_int_all
SET status = '3', error_code = 'E322'
WHERE interface_relemp_id = p_relemp_rec.interface_relemp_id;
UPDATE igs_ad_relemp_int_all
SET status = '1', error_code = NULL
WHERE interface_relemp_id = p_relemp_rec.interface_relemp_id;
UPDATE igs_ad_relemp_int_all
SET status = '3', error_code = 'E518'
WHERE interface_relemp_id = p_relemp_rec.interface_relemp_id;
SELECT 'Y'
FROM igs_ps_dic_occ_titls
WHERE occupational_title_code = p_occupational_title_code;
SELECT party_id
FROM HZ_PARTIES
WHERE party_type = 'ORGANIZATION' AND
party_number = cp_employer_party_number AND
status <> 'M';
SELECT birth_date
FROM igs_pe_person_base_v
WHERE person_id = cp_person_id;
SELECT ri.*
FROM igs_ad_relemp_int_all ri
WHERE
INTERFACE_RELATIONS_ID = cP_INTERFACE_RELATIONS_ID AND
ri.STATUS = '2';
UPDATE igs_ad_relemp_int_all
SET status = '3',
ERROR_CODE = 'E695' -- Error code depicting incorrect combination
WHERE match_ind IS NOT NULL
AND status = '2'
AND interface_relations_id = p_interface_relations_id;
UPDATE igs_ad_relemp_int_all mi
SET status = '1',
match_ind = '19'
WHERE mi.interface_relations_id = p_interface_relations_id
AND mi.status = '2'
AND EXISTS ( SELECT 1
FROM IGS_AD_EMP_DTL pe
WHERE PERSON_ID = P_REL_PERSON_ID AND
((NVL(UPPER(pe.EMPLOYER),'*!') = NVL(UPPER(mi.employer),'*')) OR
(NVL(mi.employer_party_number,'*!') = NVL(pe.employed_by_party_number,'*'))) AND
NVL(TRUNC(pe.START_DT),TO_DATE('4712/12/31','YYYY/MM/DD')) = NVL(TRUNC(mi.start_dt),TO_DATE('4712/12/31','YYYY/MM/DD'))
);
UPDATE igs_ad_relemp_int_all
SET status = '1'
WHERE interface_relations_id = p_interface_relations_id
AND match_ind IN ('18','19','22','23')
AND status = '2';
UPDATE igs_ad_relemp_int_all
SET status = '3',
ERROR_CODE = 'E695'
WHERE interface_relations_id = p_interface_relations_id
AND status = '2'
AND (match_ind IS NOT NULL AND match_ind NOT IN ('21','25'));
UPDATE igs_ad_relemp_int_all mi
SET status = '1',
match_ind = '23'
WHERE mi.interface_relations_id = p_interface_relations_id
AND mi.match_ind IS NULL
AND mi.status = '2'
AND EXISTS ( SELECT '1'
FROM IGS_AD_EMP_DTL pe
WHERE pe.PERSON_ID = p_rel_person_id AND
((TRUNC(pe.START_DT) = TRUNC(mi.Start_Dt) )OR (pe.start_dt IS NULL AND mi.Start_Dt IS NULL ))AND
((TRUNC(pe.END_DT) = TRUNC(mi.End_Dt))OR ( pe.end_dt IS NULL AND mi.End_Dt IS NULL )) AND
((pe.TYPE_OF_EMPLOYMENT = UPPER(mi.Type_Of_Employment)) OR (pe.TYPE_OF_EMPLOYMENT IS NULL AND mi.Type_Of_Employment IS NULL )) AND
((pe.FRACTION_OF_EMPLOYMENT = mi.Fraction_Of_Employment) OR (pe.FRACTION_OF_EMPLOYMENT IS NULL AND mi.Fraction_Of_Employment IS NULL )) AND
((pe.TENURE_OF_EMPLOYMENT = UPPER(mi.Tenure_Of_Employment)) OR (pe.TENURE_OF_EMPLOYMENT IS NULL AND mi.Tenure_Of_Employment IS NULL ) ) AND
((UPPER(pe.POSITION) = UPPER(mi.Position)) OR (pe.POSITION IS NULL AND mi.Position IS NULL ) ) AND
((UPPER(pe.OCCUPATIONAL_TITLE_CODE) = UPPER(mi.Occupational_Title_code)) OR (pe.OCCUPATIONAL_TITLE_CODE IS NULL AND mi.Occupational_Title_code IS NULL ) )AND
((UPPER(pe.WEEKLY_WORK_HOURS) = UPPER(mi.Weekly_Work_Hours)) OR (pe.WEEKLY_WORK_HOURS IS NULL AND mi.Weekly_Work_Hours IS NULL))AND
((pe.COMMENTS = mi.Comments ) OR (pe.COMMENTS IS NULL AND mi.Comments IS NULL ) )AND
(((UPPER(pe.EMPLOYER) = UPPER(mi.Employer)) OR (pe.EMPLOYER IS NULL AND mi.Employer IS NULL ) )OR
((pe.EMPLOYED_BY_PARTY_NUMBER = mi.EMPLOYER_PARTY_NUMBER) OR ( pe.EMPLOYED_BY_PARTY_NUMBER IS NULL AND mi.EMPLOYER_PARTY_NUMBER IS NULL ))) AND
((UPPER(pe.EMPLOYED_BY_DIVISION_NAME) = UPPER(mi.Employed_By_Division_Name)) OR (pe.EMPLOYED_BY_DIVISION_NAME IS NULL AND mi.Employed_By_Division_Name IS NULL ))
);
UPDATE igs_ad_relemp_int_all mi
SET status = '3',
match_ind = '20',
dup_employment_history_id = (SELECT pe.employment_history_id
FROM IGS_AD_EMP_DTL pe
WHERE pe.person_id = P_REL_PERSON_ID AND
((NVL(UPPER(pe.EMPLOYER),'*!') = NVL(UPPER(mi.EMPLOYER),'*')) OR
(NVL(pe.EMPLOYED_BY_PARTY_NUMBER,'*!') = NVL(mi.EMPLOYER_PARTY_NUMBER,'*') ))AND
NVL(TRUNC(pe.START_DT),TO_DATE('4712/12/31','YYYY/MM/DD')) = NVL(TRUNC(mi.start_dt),TO_DATE('4712/12/31','YYYY/MM/DD'))
AND ROWNUM = 1)
WHERE mi.interface_relations_id = p_interface_relations_id
AND mi.match_ind IS NULL
AND mi.status = '2'
AND EXISTS (SELECT '1'
FROM IGS_AD_EMP_DTL pe
WHERE pe.PERSON_ID = P_REL_PERSON_ID AND
((NVL(UPPER(pe.EMPLOYER),'*!') = NVL(UPPER(mi.EMPLOYER),'*')) OR
(NVL(pe.EMPLOYED_BY_PARTY_NUMBER,'*!') = NVL(mi.EMPLOYER_PARTY_NUMBER,'*'))) AND
NVL(TRUNC(pe.START_DT),TO_DATE('4712/12/31','YYYY/MM/DD')) = NVL(TRUNC(mi.start_dt),TO_DATE('4712/12/31','YYYY/MM/DD'))
);
SELECT pe.*
FROM igs_ad_emp_dtl pe
WHERE pe.person_id = cp_person_id
AND (((NVL(UPPER(pe.employer),'*!')) = NVL(UPPER(cp_Relns_Emp_Dtls_Rec.employer),'*'))
OR (NVL(pe.employed_by_party_number,'*!') = NVL(cp_Relns_Emp_Dtls_Rec.employer_party_number,'*')))
AND NVL(TRUNC(pe.START_DT),TO_DATE('4712/12/31','YYYY/MM/DD')) = NVL(TRUNC(cp_Relns_Emp_Dtls_Rec.start_dt),TO_DATE('4712/12/31','YYYY/MM/DD'));
UPDATE igs_ad_relemp_int_all
SET status = '3', error_code = l_error_code
WHERE interface_relemp_id = relns_emp_dtls_rec.interface_relemp_id;
Igs_Ad_Emp_Dtl_Pkg.Update_Row (
x_rowid => chk_dup_rec.Row_Id,
x_employment_history_id => chk_dup_rec.Employment_History_Id,
x_person_id => NVL(p_rel_person_id,chk_dup_rec.person_id),
x_start_dt => NVL(relns_emp_dtls_rec.start_dt,chk_dup_rec.start_dt),
x_end_dt => NVL(relns_emp_dtls_rec.end_dt,chk_dup_rec.end_dt),
x_type_of_employment => NVL(relns_emp_dtls_rec.type_of_employment,chk_dup_rec.type_of_employment),
x_fraction_of_employment => NVL(relns_emp_dtls_rec.fraction_of_employment,chk_dup_rec.fraction_of_employment),
x_tenure_of_employment => NVL(relns_emp_dtls_rec.tenure_of_employment,chk_dup_rec.tenure_of_employment),
x_position => NVL(relns_emp_dtls_rec.position,chk_dup_rec.position),
x_occupational_title_code => NVL(relns_emp_dtls_rec.occupational_title_code,chk_dup_rec.occupational_title_code),
x_occupational_title => chk_dup_rec.occupational_title,
x_weekly_work_hours => NVL(relns_emp_dtls_rec.weekly_work_hours,chk_dup_rec.weekly_work_hours),
x_comments => NVL(relns_emp_dtls_rec.comments,chk_dup_rec.comments),
x_employer => NVL(relns_emp_dtls_rec.employer,chk_dup_rec.employer),
x_employed_by_division_name => NVL(relns_emp_dtls_rec.employed_by_division_name,chk_dup_rec.employed_by_division_name),
x_branch => NVL(relns_emp_dtls_rec.branch,chk_dup_rec.branch),
x_military_rank => NVL(relns_emp_dtls_rec.military_rank,chk_dup_rec.military_rank),
x_served => NVL(relns_emp_dtls_rec.served,chk_dup_rec.served),
x_station => NVL(relns_emp_dtls_rec.station,chk_dup_rec.station),
x_contact => NVL(relns_emp_dtls_rec.contact, chk_dup_rec.contact), -- bug : 2037512
x_msg_data => l_msg_data,
x_return_status => l_return_status,
x_object_version_number => chk_dup_rec.object_version_number,
x_employed_by_party_id => NVL(l_employer_party_id,chk_dup_rec.employed_by_party_id),
x_reason_for_leaving => NVL(relns_emp_dtls_rec.reason_for_leaving,chk_dup_rec.reason_for_leaving),
x_mode => 'R'
);
UPDATE igs_ad_relemp_int_all
SET error_code = 'E014', status = '3'
WHERE interface_relemp_id = relns_emp_dtls_rec.interface_relemp_id;
UPDATE igs_ad_relemp_int_all
SET status = '1', error_code = NULL, match_ind ='18'
WHERE interface_relemp_id = relns_emp_dtls_rec.interface_relemp_id;
UPDATE igs_ad_relemp_int_all
SET status = '3', error_code = l_error_code
WHERE interface_relemp_id = relns_emp_dtls_rec.interface_relemp_id;
Igs_Ad_Emp_Dtl_Pkg.Update_Row (
x_rowid => chk_dup_rec.Row_Id,
x_employment_history_id => chk_dup_rec.Employment_History_Id,
x_person_id => NVL(p_rel_person_id,chk_dup_rec.person_id),
x_start_dt => NVL(relns_emp_dtls_rec.start_dt,chk_dup_rec.start_dt),
x_end_dt => NVL(relns_emp_dtls_rec.end_dt,chk_dup_rec.end_dt),
x_type_of_employment => NVL(relns_emp_dtls_rec.type_of_employment,chk_dup_rec.type_of_employment),
x_fraction_of_employment => NVL(relns_emp_dtls_rec.fraction_of_employment,chk_dup_rec.fraction_of_employment),
x_tenure_of_employment => NVL(relns_emp_dtls_rec.tenure_of_employment,chk_dup_rec.tenure_of_employment),
x_position => NVL(relns_emp_dtls_rec.position,chk_dup_rec.position),
x_occupational_title_code => NVL(relns_emp_dtls_rec.occupational_title_code,chk_dup_rec.occupational_title_code),
x_occupational_title => chk_dup_rec.occupational_title,
x_weekly_work_hours => NVL(relns_emp_dtls_rec.weekly_work_hours,chk_dup_rec.weekly_work_hours),
x_comments => NVL(relns_emp_dtls_rec.comments,chk_dup_rec.comments),
x_employer => NVL(relns_emp_dtls_rec.employer,chk_dup_rec.employer),
x_employed_by_division_name => NVL(relns_emp_dtls_rec.employed_by_division_name,chk_dup_rec.employed_by_division_name),
x_branch => NVL(relns_emp_dtls_rec.branch,chk_dup_rec.branch),
x_military_rank => NVL(relns_emp_dtls_rec.military_rank,chk_dup_rec.military_rank),
x_served => NVL(relns_emp_dtls_rec.served,chk_dup_rec.served),
x_station => NVL(relns_emp_dtls_rec.station,chk_dup_rec.station),
x_contact => NVL(relns_emp_dtls_rec.contact, chk_dup_rec.contact), -- Bug : 2037512
x_msg_data => l_msg_data,
x_return_status => l_return_status,
x_object_version_number => chk_dup_rec.object_version_number,
x_employed_by_party_id => NVL(l_employer_party_id,chk_dup_rec.employed_by_party_id),
x_reason_for_leaving => NVL(relns_emp_dtls_rec.reason_for_leaving,chk_dup_rec.reason_for_leaving),
x_mode => 'R'
);
UPDATE igs_ad_relemp_int_all
SET error_code = 'E014', status = '3'
WHERE interface_relemp_id = relns_emp_dtls_rec.interface_relemp_id;
UPDATE igs_ad_relemp_int_all
SET error_code = NULL, status = '1', match_ind ='18'
WHERE interface_relemp_id = relns_emp_dtls_rec.interface_relemp_id;
SELECT mi.*, i.person_id pid
FROM igs_ad_stat_int_all mi, igs_ad_interface_all i
WHERE mi.interface_run_id = cp_interface_run_id
AND mi.interface_id = i.interface_id
AND i.interface_run_id = cp_interface_run_id
AND mi.status = '2'
AND i.status IN ('1','4');
l_party_last_update_date DATE;
UPDATE igs_ad_stat_int_all
SET error_code = 'E205',
status = '3'
WHERE interface_stat_id = p_per_stat.interface_stat_id;
UPDATE igs_ad_stat_int_all
SET error_code = 'E206',
status = '3'
WHERE interface_stat_id = p_per_stat.interface_stat_id;
SELECT BIRTH_DATE
FROM IGS_PE_PERSON_BASE_V WHERE PERSON_ID =p_person_id;
UPDATE igs_ad_stat_int_all
SET error_code = 'E277',
status = '3'
WHERE interface_stat_id = p_per_stat.interface_stat_id;
UPDATE igs_ad_stat_int_all
SET error_code = 'E207',
status = '3'
WHERE interface_stat_id = p_per_stat.interface_stat_id;
UPDATE IGS_AD_STAT_INT_ALL
SET status = '3',
error_code = 'E170'
WHERE interface_id = stat_rec.INTERFACE_ID;
l_party_last_update_date DATE;
igs_pe_stat_pkg.insert_row(
X_ACTION=> 'INSERT',
X_ROWID=> l_rowid,
X_PERSON_ID => stat_rec.pid, --stat_rec.person_id,
X_ETHNIC_ORIGIN_ID =>stat_rec.ethnic_origin,
X_MARITAL_STATUS => stat_rec.marital_status,
X_MARITAL_STAT_EFFECT_DT => stat_rec.marital_status_effective_date,
X_ANN_FAMILY_INCOME=> NULL,
X_NUMBER_IN_FAMILY=> NULL,
X_CONTENT_SOURCE_TYPE => 'USER_ENTERED',
X_INTERNAL_FLAG=> NULL,
X_PERSON_NUMBER => NULL,
X_EFFECTIVE_START_DATE => SYSDATE,
X_EFFECTIVE_END_DATE => NULL,
X_ETHNIC_ORIGIN => NULL,
X_RELIGION=> stat_rec.religion_cd,
X_NEXT_TO_KIN => NULL,
X_NEXT_TO_KIN_MEANING => NULL,
X_PLACE_OF_BIRTH => stat_rec.place_of_birth,
X_SOCIO_ECO_STATUS => NULL,
X_SOCIO_ECO_STATUS_DESC => NULL,
X_FURTHER_EDUCATION => NULL,
X_FURTHER_EDUCATION_DESC => NULL,
X_IN_STATE_TUITION=> NULL,
X_TUITION_ST_DATE=> NULL,
X_TUITION_END_DATE => NULL,
X_PERSON_INITIALS => NULL,
X_PRIMARY_CONTACT_ID => NULL,
X_PERSONAL_INCOME => NULL,
X_HEAD_OF_HOUSEHOLD_FLAG => NULL,
X_CONTENT_SOURCE_NUMBER => NULL,
X_HZ_PARTIES_OVN => l_object_version_number,
X_attribute_category => stat_rec.attribute_category,
X_attribute1 => stat_rec.attribute1 ,
X_attribute2 => stat_rec.attribute2 ,
X_attribute3 => stat_rec.attribute3 ,
X_attribute4 => stat_rec.attribute4 ,
X_attribute5 => stat_rec.attribute5 ,
X_attribute6 => stat_rec.attribute6 ,
X_attribute7 => stat_rec.attribute7 ,
X_attribute8 => stat_rec.attribute8 ,
X_attribute9 => stat_rec.attribute9 ,
X_attribute10 => stat_rec.attribute10 ,
X_attribute11 => stat_rec.attribute11 ,
X_attribute12 => stat_rec.attribute12 ,
X_attribute13 => stat_rec.attribute13 ,
X_attribute14 => stat_rec.attribute14 ,
X_attribute15 => stat_rec.attribute15 ,
X_attribute16 => stat_rec.attribute16 ,
X_attribute17 => stat_rec.attribute17 ,
X_attribute18 => stat_rec.attribute18 ,
X_attribute19 => stat_rec.attribute19 ,
X_attribute20 => stat_rec.attribute20 ,
X_GLOBAL_ATTRIBUTE_CATEGORY => NULL,
X_GLOBAL_ATTRIBUTE1 => NULL,
X_GLOBAL_ATTRIBUTE2 => NULL,
X_GLOBAL_ATTRIBUTE3 => NULL,
X_GLOBAL_ATTRIBUTE4 => NULL,
X_GLOBAL_ATTRIBUTE5 => NULL,
X_GLOBAL_ATTRIBUTE6 => NULL,
X_GLOBAL_ATTRIBUTE7 => NULL,
X_GLOBAL_ATTRIBUTE8 => NULL,
X_GLOBAL_ATTRIBUTE9 => NULL,
X_GLOBAL_ATTRIBUTE10 => NULL,
X_GLOBAL_ATTRIBUTE11 => NULL,
X_GLOBAL_ATTRIBUTE12 => NULL,
X_GLOBAL_ATTRIBUTE13 => NULL,
X_GLOBAL_ATTRIBUTE14 => NULL,
X_GLOBAL_ATTRIBUTE15 => NULL,
X_GLOBAL_ATTRIBUTE16 => NULL,
X_GLOBAL_ATTRIBUTE17 => NULL,
X_GLOBAL_ATTRIBUTE18 => NULL,
X_GLOBAL_ATTRIBUTE19 => NULL,
X_GLOBAL_ATTRIBUTE20 => NULL,
X_PARTY_LAST_UPDATE_DATE => L_party_last_update_date,
X_PERSON_PROFILE_ID=> l_person_profile_id,
X_MATR_CAL_TYPE => NULL,
X_MATR_SEQUENCE_NUMBER => NULL,
X_INIT_CAL_TYPE => NULL,
X_INIT_SEQUENCE_NUMBER => NULL,
X_RECENT_CAL_TYPE => NULL,
X_RECENT_SEQUENCE_NUMBER => NULL,
X_CATALOG_CAL_TYPE => NULL,
X_CATALOG_SEQUENCE_NUMBER => NULL,
Z_RETURN_STATUS => l_return_status,
Z_MSG_COUNT => l_msg_count,
Z_MSG_DATA => l_msg_data,
X_BIRTH_CNTRY_RESN_CODE => NULL --- prbhardw
);
UPDATE IGS_AD_STAT_INT_all
SET status = '3',
ERROR_CODE = 'E695' -- Error code depicting incorrect combination
WHERE match_ind IS NOT NULL
AND status = '2'
AND interface_run_id = l_interface_run_id;
UPDATE IGS_AD_STAT_INT_all mi
SET status = '1',
match_ind = '19'
WHERE mi.interface_run_id = l_interface_run_id
AND mi.status = '2'
AND EXISTS ( SELECT '1'
FROM hz_parties hp, igs_ad_interface_all ii
WHERE ii.interface_run_id = l_interface_run_id
AND ii.interface_id = mi.interface_id
AND ii.person_id = hp.party_id);
UPDATE IGS_AD_STAT_INT_all
SET status = '1'
WHERE interface_run_id = l_interface_run_id
AND match_ind IN ('18','19','22','23')
AND status = '2';
UPDATE IGS_AD_STAT_INT_all
SET status = '3',
ERROR_CODE = 'E695'
WHERE interface_run_id = l_interface_run_id
AND status = '2'
AND (match_ind IS NOT NULL AND match_ind NOT IN ('21','25'));
UPDATE IGS_AD_STAT_INT_all mi
SET status = '1',
match_ind = '23'
WHERE mi.interface_run_id = l_interface_run_id
AND mi.match_ind IS NULL
AND mi.status = '2'
AND EXISTS ( SELECT 1
FROM hz_person_profiles pp,
igs_pe_stat_details sd,
igs_ad_interface_all ii
WHERE ii.interface_run_id = l_interface_run_id AND
ii.interface_id = mi.interface_id AND
ii.person_id = pp.party_id AND
sd.person_id(+) = pp.party_id AND
SYSDATE BETWEEN pp.effective_start_date AND NVL(pp.effective_end_date, SYSDATE) AND
NVL(pp.declared_ethnicity,'*') = NVL(mi.ethnic_origin,'*') AND
NVL(pp.marital_status,'*') = NVL(UPPER(mi.marital_status),'*') AND
NVL(sd.religion_cd,'*') = NVL(mi.religion_cd,'*') AND
NVL(TRUNC(pp.marital_status_effective_date), l_default_date)
= NVL(TRUNC(mi.marital_status_effective_date),l_default_date)AND
NVL(UPPER(pp.place_of_birth),'*') = NVL(UPPER(mi.place_of_birth),'*') AND
NVL(UPPER(sd.attribute1),'*') = NVL(UPPER(mi.attribute1),'*') AND
NVL(UPPER(sd.attribute2),'*') = NVL(UPPER(mi.attribute2),'*') AND
NVL(UPPER(sd.attribute3),'*') = NVL(UPPER(mi.attribute3),'*') AND
NVL(UPPER(sd.attribute4),'*') = NVL(UPPER(mi.attribute4),'*') AND
NVL(UPPER(sd.attribute5),'*') = NVL(UPPER(mi.attribute5),'*') AND
NVL(UPPER(sd.attribute6),'*') = NVL(UPPER(mi.attribute6),'*') AND
NVL(UPPER(sd.attribute7),'*') = NVL(UPPER(mi.attribute7),'*') AND
NVL(UPPER(sd.attribute8),'*') = NVL(UPPER(mi.attribute8),'*') AND
NVL(UPPER(sd.attribute9),'*') = NVL(UPPER(mi.attribute9),'*') AND
NVL(UPPER(sd.attribute10),'*') = NVL(UPPER(mi.attribute10),'*') AND
NVL(UPPER(sd.attribute11),'*') = NVL(UPPER(mi.attribute11),'*') AND
NVL(UPPER(sd.attribute12),'*') = NVL(UPPER(mi.attribute12),'*') AND
NVL(UPPER(sd.attribute13),'*') = NVL(UPPER(mi.attribute13),'*') AND
NVL(UPPER(sd.attribute14),'*') = NVL(UPPER(mi.attribute14),'*') AND
NVL(UPPER(sd.attribute15),'*') = NVL(UPPER(mi.attribute15),'*') AND
NVL(UPPER(sd.attribute16),'*') = NVL(UPPER(mi.attribute16),'*') AND
NVL(UPPER(sd.attribute17),'*') = NVL(UPPER(mi.attribute17),'*') AND
NVL(UPPER(sd.attribute18),'*') = NVL(UPPER(mi.attribute18),'*') AND
NVL(UPPER(sd.attribute19),'*') = NVL(UPPER(mi.attribute19),'*') AND
NVL(UPPER(sd.attribute20),'*') = NVL(UPPER(mi.attribute20),'*') AND
NVL(UPPER(sd.attribute_category),'*') = NVL(UPPER(mi.attribute_category),'*')
);
UPDATE IGS_AD_STAT_INT_all mi
SET status = '3',
match_ind = '20'
WHERE mi.interface_run_id = l_interface_run_id
AND mi.match_ind IS NULL
AND mi.status = '2'
AND EXISTS (SELECT '1'
FROM hz_parties hp,
igs_ad_interface_all ii
WHERE ii.interface_run_id = l_interface_run_id
AND ii.interface_id = mi.interface_id
AND ii.person_id = hp.party_id);
SELECT
p.rowid row_id,
pp.person_profile_id,
p.party_id person_id,
p.party_number person_number,
pp.effective_start_date,
pp.effective_end_date,
pp.declared_ethnicity ethnic_origin_id,
pp.marital_status,
pp.marital_status_effective_date,
pp.internal_flag,
sd.religion_cd religion,
sd.next_to_kin,
pp.place_of_birth,
sd.socio_eco_cd socio_eco_status,
sd.further_education_cd further_education,
pp.household_size number_in_family,
pp.household_income ann_family_income,
sd.in_state_tuition,
sd.tuition_st_date,
sd.tuition_end_date,
sd.matr_cal_type,
sd.matr_sequence_number,
sd.init_cal_type,
sd.init_sequence_number,
sd.recent_cal_type,
sd.recent_sequence_number,
sd.catalog_cal_type,
sd.catalog_sequence_number,
sd.attribute_category attribute_category,
sd.attribute1 attribute1,
sd.attribute2 attribute2,
sd.attribute3 attribute3,
sd.attribute4 attribute4,
sd.attribute5 attribute5,
sd.attribute6 attribute6,
sd.attribute7 attribute7,
sd.attribute8 attribute8,
sd.attribute9 attribute9,
sd.attribute10 attribute10,
sd.attribute11 attribute11,
sd.attribute12 attribute12,
sd.attribute13 attribute13,
sd.attribute14 attribute14,
sd.attribute15 attribute15,
sd.attribute16 attribute16,
sd.attribute17 attribute17,
sd.attribute18 attribute18,
sd.attribute19 attribute19,
sd.attribute20 attribute20,
pp.global_attribute_category,
pp.global_attribute1,
pp.global_attribute2,
pp.global_attribute3,
pp.global_attribute4,
pp.global_attribute5,
pp.global_attribute6,
pp.global_attribute7,
pp.global_attribute8,
pp.global_attribute9,
pp.global_attribute10,
pp.global_attribute11,
pp.global_attribute12,
pp.global_attribute13,
pp.global_attribute14,
pp.global_attribute15,
pp.global_attribute16,
pp.global_attribute17,
pp.global_attribute18,
pp.global_attribute19,
pp.global_attribute20,
pp.person_initials,
pp.primary_contact_id,
pp.personal_income,
pp.head_of_household_flag,
pp.content_source_type,
pp.content_source_number,
p.object_version_number object_version_number
FROM
hz_person_profiles pp,
igs_pe_stat_details sd,
hz_parties p
WHERE
sd.person_id(+) = p.party_id AND
pp.party_id = p.party_id AND
SYSDATE BETWEEN pp.effective_start_date AND NVL(pp.effective_end_date, SYSDATE) AND
p.party_id = cp_person_id;
IGS_PE_STAT_PKG.Update_Row(
X_ACTION => 'UPDATE',
x_rowid => chk_dup_rec.row_id,
x_person_id => stat_rec.pid, -- stat_rec.person_id ,
x_ethnic_origin_id => NVL(stat_rec.ethnic_origin,chk_dup_rec.ethnic_origin_id), -- BUG ID: 2138382
x_marital_status => NVL(stat_rec.marital_status,chk_dup_rec.marital_status),
x_marital_stat_effect_dt => NVL(stat_rec.marital_status_effective_date,TRUNC(chk_dup_rec.marital_status_effective_date)),
x_ann_family_income => chk_dup_rec.ann_family_income,
x_number_in_family => chk_dup_rec.number_in_family,
x_content_source_type => 'USER_ENTERED',
x_internal_flag => chk_dup_rec.internal_flag,
x_person_number => chk_dup_rec.person_number,
x_effective_start_date => TRUNC(chk_dup_rec.effective_start_date),
x_effective_end_date => TRUNC(chk_dup_rec.effective_end_date),
x_ethnic_origin => NULL,
x_religion => NVL(stat_rec.religion_cd,chk_dup_rec.religion),
x_next_to_kin => chk_dup_rec.next_to_kin,
x_next_to_kin_meaning => NULL,
x_place_of_birth => NVL(stat_rec.place_of_birth,chk_dup_rec.place_of_birth),
x_socio_eco_status => chk_dup_rec.socio_eco_status,
x_socio_eco_status_desc => NULL,
x_further_education => chk_dup_rec.further_education,
x_further_education_desc => NULL,
x_in_state_tuition => chk_dup_rec.in_state_tuition,
x_tuition_st_date => TRUNC(chk_dup_rec.tuition_st_date),
x_tuition_end_date => TRUNC(chk_dup_rec.tuition_end_date),
x_person_initials => chk_dup_rec.person_initials,
x_primary_contact_id => chk_dup_rec.primary_contact_id,
x_personal_income => chk_dup_rec.personal_income,
x_head_of_household_flag => chk_dup_rec.head_of_household_flag,
x_content_source_number => chk_dup_rec.content_source_number,
x_attribute_category => NVL(stat_rec.attribute_category,chk_dup_rec.attribute_category),
x_hz_parties_ovn => chk_dup_rec.object_version_number,
x_attribute1 => NVL(stat_rec.attribute1,chk_dup_rec.attribute1),
x_attribute2 => NVL(stat_rec.attribute2,chk_dup_rec.attribute2),
x_attribute3 => NVL(stat_rec.attribute3,chk_dup_rec.attribute3),
x_attribute4 => NVL(stat_rec.attribute4,chk_dup_rec.attribute4),
x_attribute5 => NVL(stat_rec.attribute5,chk_dup_rec.attribute5),
x_attribute6 => NVL(stat_rec.attribute6,chk_dup_rec.attribute6),
x_attribute7 => NVL(stat_rec.attribute7,chk_dup_rec.attribute7),
x_attribute8 => NVL(stat_rec.attribute8,chk_dup_rec.attribute8),
x_attribute9 => NVL(stat_rec.attribute9,chk_dup_rec.attribute9),
x_attribute10 => NVL(stat_rec.attribute10,chk_dup_rec.attribute10),
x_attribute11 => NVL(stat_rec.attribute11,chk_dup_rec.attribute11),
x_attribute12 => NVL(stat_rec.attribute12,chk_dup_rec.attribute12),
x_attribute13 => NVL(stat_rec.attribute13,chk_dup_rec.attribute13),
x_attribute14 => NVL(stat_rec.attribute14,chk_dup_rec.attribute14),
x_attribute15 => NVL(stat_rec.attribute15,chk_dup_rec.attribute15),
x_attribute16 => NVL(stat_rec.attribute16,chk_dup_rec.attribute16),
x_attribute17 => NVL(stat_rec.attribute17,chk_dup_rec.attribute17),
x_attribute18 => NVL(stat_rec.attribute18,chk_dup_rec.attribute18),
x_attribute19 => NVL(stat_rec.attribute19,chk_dup_rec.attribute19),
x_attribute20 => NVL(stat_rec.attribute20,chk_dup_rec.attribute20),
x_global_attribute_category => chk_dup_rec.global_attribute_category,
x_global_attribute1 => chk_dup_rec.global_attribute1,
x_global_attribute2=> chk_dup_rec.global_attribute1,
x_global_attribute3=> chk_dup_rec.global_attribute1,
x_global_attribute4=> chk_dup_rec.global_attribute1,
x_global_attribute5=> chk_dup_rec.global_attribute1,
x_global_attribute6=> chk_dup_rec.global_attribute1,
x_global_attribute7=> chk_dup_rec.global_attribute1,
x_global_attribute8=> chk_dup_rec.global_attribute1,
x_global_attribute9=> chk_dup_rec.global_attribute1,
x_global_attribute10=> chk_dup_rec.global_attribute1,
x_global_attribute11=> chk_dup_rec.global_attribute1,
x_global_attribute12=> chk_dup_rec.global_attribute1,
x_global_attribute13=> chk_dup_rec.global_attribute1,
x_global_attribute14=> chk_dup_rec.global_attribute1,
x_global_attribute15=> chk_dup_rec.global_attribute1,
x_global_attribute16=> chk_dup_rec.global_attribute1,
x_global_attribute17=> chk_dup_rec.global_attribute1,
x_global_attribute18=> chk_dup_rec.global_attribute1,
x_global_attribute19=> chk_dup_rec.global_attribute1,
x_global_attribute20=> chk_dup_rec.global_attribute1,
x_party_last_update_date=> l_party_last_update_date,
x_person_profile_id => chk_dup_rec.person_profile_id,
x_matr_cal_type => chk_dup_rec.matr_cal_type,
x_matr_sequence_number => chk_dup_rec.matr_sequence_number,
x_init_cal_type => chk_dup_rec.init_cal_type,
x_init_sequence_number => chk_dup_rec.init_sequence_number,
x_recent_cal_type => chk_dup_rec.recent_cal_type,
x_recent_sequence_number => chk_dup_rec.recent_sequence_number,
x_catalog_cal_type => chk_dup_rec.catalog_cal_type,
x_catalog_sequence_number => chk_dup_rec.catalog_sequence_number,
z_return_status => l_return_status,
z_msg_count => l_msg_count,
z_msg_data => l_msg_data,
x_birth_cntry_resn_code => NULL --- prbhardw
);
UPDATE igs_ad_stat_int_all
SET status = '3',error_code = 'E014'
WHERE interface_id = stat_rec.interface_id
AND interface_stat_id = stat_rec.interface_stat_id;
UPDATE
igs_ad_stat_int_all
SET
error_code = NULL, --ssomani, added this 15 March 2001
status = '1', match_ind = '18'
WHERE
interface_id = stat_rec.interface_id
AND interface_stat_id = stat_rec.interface_stat_id;
UPDATE igs_ad_stat_int_all
SET status = '3',
error_code = 'E518'
WHERE interface_id = stat_rec.interface_id
AND interface_stat_id = stat_rec.interface_stat_id;
IGS_PE_STAT_PKG.Update_Row(
X_ACTION => 'UPDATE',
x_rowid => chk_dup_rec.row_id,
x_person_id => stat_rec.pid, --stat_rec.person_id,
x_ethnic_origin_id => NVL(stat_rec.ethnic_origin,chk_dup_rec.ethnic_origin_id), -- BUG ID : 2138382
x_marital_status => NVL(stat_rec.marital_status,chk_dup_rec.marital_status),
x_marital_stat_effect_dt => NVL(stat_rec.marital_status_effective_date,chk_dup_rec.marital_status_effective_date),
x_ann_family_income => chk_dup_rec.ann_family_income,
x_number_in_family => chk_dup_rec.number_in_family,
x_content_source_type => 'USER_ENTERED',
x_internal_flag => chk_dup_rec.internal_flag,
x_person_number => chk_dup_rec.person_number,
x_effective_start_date => TRUNC(chk_dup_rec.effective_start_date),
x_effective_end_date => TRUNC(chk_dup_rec.effective_end_date),
x_ethnic_origin => NULL,
x_religion => NVL(stat_rec.religion_cd,chk_dup_rec.religion),
x_next_to_kin => chk_dup_rec.next_to_kin,
x_next_to_kin_meaning => NULL,
x_place_of_birth => NVL(stat_rec.place_of_birth,chk_dup_rec.place_of_birth),
x_socio_eco_status => chk_dup_rec.socio_eco_status,
x_socio_eco_status_desc => NULL,
x_further_education => chk_dup_rec.further_education,
x_further_education_desc => NULL,
x_in_state_tuition => chk_dup_rec.in_state_tuition,
x_tuition_st_date => TRUNC(chk_dup_rec.tuition_st_date),
x_tuition_end_date => TRUNC(chk_dup_rec.tuition_end_date),
x_person_initials => chk_dup_rec.person_initials,
x_primary_contact_id => chk_dup_rec.primary_contact_id,
x_personal_income => chk_dup_rec.personal_income,
x_head_of_household_flag => chk_dup_rec.head_of_household_flag,
x_content_source_number => chk_dup_rec.content_source_number,
x_hz_parties_ovn => chk_dup_rec.object_version_number,
x_attribute_category => NVL(stat_rec.attribute_category,chk_dup_rec.attribute_category),
x_attribute1 => NVL(stat_rec.attribute1,chk_dup_rec.attribute1),
x_attribute2 => NVL(stat_rec.attribute2,chk_dup_rec.attribute2),
x_attribute3 => NVL(stat_rec.attribute3,chk_dup_rec.attribute3),
x_attribute4 => NVL(stat_rec.attribute4,chk_dup_rec.attribute4),
x_attribute5 => NVL(stat_rec.attribute5,chk_dup_rec.attribute5),
x_attribute6 => NVL(stat_rec.attribute6,chk_dup_rec.attribute6),
x_attribute7 => NVL(stat_rec.attribute7,chk_dup_rec.attribute7),
x_attribute8 => NVL(stat_rec.attribute8,chk_dup_rec.attribute8),
x_attribute9 => NVL(stat_rec.attribute9,chk_dup_rec.attribute9),
x_attribute10 => NVL(stat_rec.attribute10,chk_dup_rec.attribute10),
x_attribute11 => NVL(stat_rec.attribute11,chk_dup_rec.attribute11),
x_attribute12 => NVL(stat_rec.attribute12,chk_dup_rec.attribute12),
x_attribute13 => NVL(stat_rec.attribute13,chk_dup_rec.attribute13),
x_attribute14 => NVL(stat_rec.attribute14,chk_dup_rec.attribute14),
x_attribute15 => NVL(stat_rec.attribute15,chk_dup_rec.attribute15),
x_attribute16 => NVL(stat_rec.attribute16,chk_dup_rec.attribute16),
x_attribute17 => NVL(stat_rec.attribute17,chk_dup_rec.attribute17),
x_attribute18 => NVL(stat_rec.attribute18,chk_dup_rec.attribute18),
x_attribute19 => NVL(stat_rec.attribute19,chk_dup_rec.attribute19),
x_attribute20 => NVL(stat_rec.attribute20,chk_dup_rec.attribute20),
x_global_attribute_category => chk_dup_rec.global_attribute_category,
x_global_attribute1 => chk_dup_rec.global_attribute1,
x_global_attribute2=> chk_dup_rec.global_attribute1,
x_global_attribute3=> chk_dup_rec.global_attribute1,
x_global_attribute4=> chk_dup_rec.global_attribute1,
x_global_attribute5=> chk_dup_rec.global_attribute1,
x_global_attribute6=> chk_dup_rec.global_attribute1,
x_global_attribute7=> chk_dup_rec.global_attribute1,
x_global_attribute8=> chk_dup_rec.global_attribute1,
x_global_attribute9=> chk_dup_rec.global_attribute1,
x_global_attribute10=> chk_dup_rec.global_attribute1,
x_global_attribute11=> chk_dup_rec.global_attribute1,
x_global_attribute12=> chk_dup_rec.global_attribute1,
x_global_attribute13=> chk_dup_rec.global_attribute1,
x_global_attribute14=> chk_dup_rec.global_attribute1,
x_global_attribute15=> chk_dup_rec.global_attribute1,
x_global_attribute16=> chk_dup_rec.global_attribute1,
x_global_attribute17=> chk_dup_rec.global_attribute1,
x_global_attribute18=> chk_dup_rec.global_attribute1,
x_global_attribute19=> chk_dup_rec.global_attribute1,
x_global_attribute20=> chk_dup_rec.global_attribute1,
x_party_last_update_date=> l_party_last_update_date,
x_person_profile_id => chk_dup_rec.person_profile_id,
x_matr_cal_type => chk_dup_rec.matr_cal_type,
x_matr_sequence_number => chk_dup_rec.matr_sequence_number,
x_init_cal_type => chk_dup_rec.init_cal_type,
x_init_sequence_number => chk_dup_rec.init_sequence_number,
x_recent_cal_type => chk_dup_rec.recent_cal_type,
x_recent_sequence_number => chk_dup_rec.recent_sequence_number,
x_catalog_cal_type => chk_dup_rec.catalog_cal_type,
x_catalog_sequence_number => chk_dup_rec.catalog_sequence_number,
z_return_status => l_return_status,
z_msg_count => l_msg_count,
z_msg_data => l_msg_data,
x_birth_cntry_resn_code => NULL --- prbhardw
);
UPDATE igs_ad_stat_int_all
SET status = '3',
error_code = 'E014'
WHERE interface_id = stat_rec.interface_id
AND interface_stat_id = stat_rec.interface_stat_id;
UPDATE igs_ad_stat_int_all
SET status = '1', ERROR_CODE = NULL, match_ind = '18'
WHERE
interface_id = stat_rec.interface_id
AND interface_stat_id = stat_rec.interface_stat_id;
UPDATE
igs_ad_stat_int_all
SET
status = '3',
error_code = p_error_code
WHERE
interface_id = stat_rec.interface_id
AND interface_stat_id = stat_rec.interface_stat_id;
UPDATE igs_ad_stat_int_all
SET
status = '3',
error_code = 'E014'
WHERE
interface_id = stat_rec.interface_id
AND interface_stat_id = stat_rec.interface_stat_id;
UPDATE igs_ad_stat_int_all
SET status = l_status,error_code = l_error_code
WHERE interface_id = stat_rec.interface_id
AND interface_stat_id = stat_rec.interface_stat_id;
UPDATE igs_ad_stat_int_all
SET status = '3',error_code = 'E518'
WHERE interface_id = stat_rec.interface_id
AND interface_stat_id = stat_rec.interface_stat_id;
SELECT mi.*,i.person_id
FROM igs_pe_eit_int mi,
igs_ad_interface_all i
WHERE mi.information_type IN ('PE_STAT_RES_COUNTRY','PE_STAT_RES_STATE', 'PE_STAT_RES_STATUS')
AND mi.interface_run_id = cp_interface_run_id
AND mi.interface_id = i.interface_id
AND i.interface_run_id = cp_interface_run_id
AND mi.status = '2';
SELECT rowid, pe.*
FROM igs_pe_eit pe
WHERE person_id = biodem_rec.person_id AND
information_type = biodem_rec.information_type AND
start_date = TRUNC(biodem_rec.start_date);
|| Purpose : Person Stats - Local Insert Biodemo details proc
|| Known limitations, enhancements or remarks :
*/
AS
l_count NUMBER(3);
SELECT count(1) FROM IGS_PE_EIT
WHERE person_id = cp_person_id
AND INFORMATION_TYPE = cp_information_type
AND (
NVL(TRUNC(cp_end_date),IGS_GE_DATE.igsdate('4712/12/31')) BETWEEN START_DATE
AND NVL(END_DATE,IGS_GE_DATE.igsdate('4712/12/31')) OR
cp_start_date BETWEEN START_DATE AND NVL(END_DATE,IGS_GE_DATE.igsdate('4712/12/31')) OR
(cp_start_Date < START_DATE AND
NVL(end_date,IGS_GE_DATE.igsdate('4712/12/31'))< NVL(cp_end_date ,IGS_GE_DATE.igsdate('4712/12/31')) )
);
Igs_Pe_Eit_Pkg.insert_row (
x_rowid => l_rowid,
x_pe_eit_id => l_pe_eit_id,
x_person_id => biodem_rec.person_id,
x_information_type => biodem_rec.information_type,
x_pei_information1 => biodem_rec.pei_information1,
x_pei_information2 => biodem_rec.pei_information2,
x_pei_information3 => biodem_rec.pei_information3,
x_pei_information4 => biodem_rec.pei_information4,
x_pei_information5 => biodem_rec.pei_information5,
x_start_date => biodem_rec.start_date,
x_end_date => biodem_rec.end_date,
x_mode => 'R'
);
SELECT 'X' var
FROM fnd_territories_vl
WHERE territory_code = NVL(cp_territory_code,'0');
SELECT 'X'
FROM hz_geographies
WHERE GEOGRAPHY_TYPE = cp_geography_type
AND geography_code = NVL(cp_geography_cd, '0')
AND COUNTRY_CODE = cp_country_cd;
SELECT birth_date
FROM igs_pe_person_base_v
WHERE person_id = cp_person_id;
UPDATE igs_pe_eit_int
SET status = '3',
error_code = l_error
WHERE interface_eit_id = p_biodemo_rec.interface_eit_id;
UPDATE igs_pe_eit_int
SET status = '3',
ERROR_CODE = 'E695' -- Error code depicting incorrect combination
WHERE match_ind IS NOT NULL
AND status = '2'
AND interface_run_id = l_interface_run_id;
UPDATE igs_pe_eit_int mi
SET status = '1',
match_ind = '19'
WHERE mi.interface_run_id = l_interface_run_id
AND mi.status = '2'
AND EXISTS ( SELECT '1'
FROM igs_pe_eit pe, igs_ad_interface_all ii
WHERE ii.interface_run_id = l_interface_run_id
AND ii.interface_id = mi.interface_id
AND ii.person_id = pe.person_id
AND UPPER(mi.information_type) = UPPER(pe.information_type)
AND TRUNC(pe.start_date) = TRUNC(mi.start_date));
UPDATE igs_pe_eit_int
SET status = '1'
WHERE interface_run_id = l_interface_run_id
AND match_ind IN ('18','19','22','23')
AND status = '2';
UPDATE igs_pe_eit_int
SET status = '3',
ERROR_CODE = 'E695'
WHERE interface_run_id = l_interface_run_id
AND status = '2'
AND (match_ind IS NOT NULL AND match_ind NOT IN ('21','25'));
UPDATE igs_pe_eit_int mi
SET status = '1',
match_ind = '23'
WHERE mi.interface_run_id = l_interface_run_id
AND mi.match_ind IS NULL
AND mi.status = '2'
AND EXISTS ( SELECT '1'
FROM igs_pe_eit pe, igs_ad_interface_all ii
WHERE ii.interface_run_id = l_interface_run_id
AND ii.interface_id = mi.interface_id
AND ii.person_id = pe.person_id
AND UPPER(mi.information_type) = UPPER(pe.information_type)
AND TRUNC(pe.start_date) = TRUNC(mi.start_date)
AND NVL(UPPER(pe.pei_information1),'1') = NVL(UPPER(mi.pei_information1),'1')
AND NVL(UPPER(pe.pei_information2),'2') = NVL(UPPER(mi.pei_information2),'2')
AND NVL(UPPER(pe.pei_information3),'3') = NVL(UPPER(mi.pei_information3),'3')
AND NVL(UPPER(pe.pei_information4),'4') = NVL(UPPER(mi.pei_information4),'4')
AND NVL(UPPER(pe.pei_information5),'5') = NVL(UPPER(mi.pei_information5),'5')
AND NVL(TRUNC(pe.end_date),IGS_GE_DATE.IGSDATE('4712/12/01'))=
NVL(TRUNC(mi.end_date),IGS_GE_DATE.IGSDATE('4712/12/01'))
);
UPDATE igs_pe_eit_int mi
SET status = '3',
match_ind = '20',
DUP_PE_EIT_ID = (SELECT pe.pe_eit_id
FROM igs_pe_eit pe, igs_ad_interface_all ii
WHERE mi.interface_run_id = l_interface_run_id
AND ii.interface_id = mi.interface_id
AND ii.person_id = pe.person_id
AND UPPER(mi.information_type) = UPPER(pe.information_type)
AND TRUNC(pe.start_date) = TRUNC(mi.start_date))
WHERE mi.interface_run_id = l_interface_run_id
AND mi.match_ind IS NULL
AND mi.status = '2'
AND EXISTS (SELECT '1'
FROM igs_pe_eit pe, igs_ad_interface_all ii
WHERE ii.interface_run_id = l_interface_run_id
AND ii.interface_id = mi.interface_id
AND ii.person_id = pe.person_id
AND UPPER(mi.information_type) = UPPER(pe.information_type)
AND TRUNC(pe.start_date) = TRUNC(mi.start_date));
SELECT COUNT(1) FROM IGS_PE_EIT
WHERE person_id = cp_person_id
AND INFORMATION_TYPE = cp_information_type
AND start_date <> cp_start_date
AND (NVL(cp_end_date,IGS_GE_DATE.igsdate('4712/12/31')) BETWEEN START_DATE AND NVL(END_DATE,IGS_GE_DATE.igsdate('4712/12/31'))
OR
cp_start_date BETWEEN START_DATE AND NVL(END_DATE,IGS_GE_DATE.igsdate('4712/12/31'))
OR
( cp_start_date < START_DATE AND
NVL(end_date,IGS_GE_DATE.igsdate('4712/12/31'))< NVL(cp_end_date,IGS_GE_DATE.igsdate('4712/12/31')) ) );
UPDATE igs_pe_eit_int
SET status = '3',
error_code = 'E228'
WHERE interface_eit_id = biodem_rec.interface_eit_id;
igs_pe_eit_pkg.update_row (
x_rowid => dup_cur_rec.rowid,
x_pe_eit_id => dup_cur_rec.pe_eit_id,
x_person_id => dup_cur_rec.person_id,
x_information_type => dup_cur_rec.information_type,
x_pei_information1 => NVL(biodem_rec.pei_information1,dup_cur_rec.pei_information1),
x_pei_information2 => NVL(biodem_rec.pei_information2,dup_cur_rec.pei_information2),
x_pei_information3 => NVL(biodem_rec.pei_information3,dup_cur_rec.pei_information3),
x_pei_information4 => NVL(biodem_rec.pei_information4,dup_cur_rec.pei_information4),
x_pei_information5 => NVL(biodem_rec.pei_information5,dup_cur_rec.pei_information5),
x_start_date => dup_cur_rec.start_date,
x_end_date => NVL(biodem_rec.end_date,dup_cur_rec.end_date),
x_mode => 'R'
);
UPDATE igs_pe_eit_int
SET status = '1',
match_ind = '18'
WHERE interface_eit_id = biodem_rec.interface_eit_id;
UPDATE igs_pe_eit_int
SET status = '3',
error_code = 'E090'
WHERE interface_eit_id = biodem_rec.interface_eit_id;
SELECT count(1) FROM IGS_PE_EIT
WHERE person_id = cp_person_id
AND INFORMATION_TYPE = cp_information_type
AND start_date <> cp_start_date
AND (NVL(cp_end_date,IGS_GE_DATE.igsdate('4712/12/31')) BETWEEN START_DATE AND NVL(END_DATE,IGS_GE_DATE.igsdate('4712/12/31'))
OR
cp_start_date BETWEEN START_DATE AND NVL(END_DATE,IGS_GE_DATE.igsdate('4712/12/31'))
OR
( cp_start_date < START_DATE AND
NVL(end_date,IGS_GE_DATE.igsdate('4712/12/31'))< NVL(cp_end_date,IGS_GE_DATE.igsdate('4712/12/31')) ) );
UPDATE igs_pe_eit_int
SET status = '3',
error_code = 'E228'
WHERE interface_eit_id = biodem_rec.interface_eit_id;
igs_pe_eit_pkg.update_row (
x_rowid => dup_cur_rec.rowid,
x_pe_eit_id => dup_cur_rec.pe_eit_id,
x_person_id => dup_cur_rec.person_id,
x_information_type => dup_cur_rec.information_type,
x_pei_information1 => NVL(biodem_rec.pei_information1,dup_cur_rec.pei_information1),
x_pei_information2 => NVL(biodem_rec.pei_information2,dup_cur_rec.pei_information2),
x_pei_information3 => NVL(biodem_rec.pei_information3,dup_cur_rec.pei_information3),
x_pei_information4 => NVL(biodem_rec.pei_information4,dup_cur_rec.pei_information4),
x_pei_information5 => NVL(biodem_rec.pei_information5,dup_cur_rec.pei_information5),
x_start_date => dup_cur_rec.start_date,
x_end_date => NVL(biodem_rec.end_date,dup_cur_rec.end_date),
x_mode => 'R'
);
UPDATE igs_pe_eit_int
SET status = '1', error_code = NULL,match_ind = '18'
WHERE interface_eit_id = biodem_rec.interface_eit_id;
UPDATE igs_pe_eit_int
SET status = '3',
error_code = 'E089'
WHERE interface_eit_id = biodem_rec.interface_eit_id;
UPDATE igs_pe_eit_int
SET status = l_status,
error_code = l_error_code
WHERE interface_eit_id = biodem_rec.interface_eit_id;