[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 'X'
FROM igs_ad_code_classes
WHERE class = cp_class
AND code_id = cp_code_id
AND closed_ind = cp_closed_ind;
UPDATE igs_ad_interface_all
SET ERROR_CODE = l_error_code,
status = '3'
WHERE interface_id = p_person_rec.interface_id;
|| mmkumar 19-JUL-2005 party number impact , passed NULL for x_oss_org_unit_cd in uupdate_row
|| (reverse chronological order - newest change first)
*/
L_MISS_person_rec HZ_PARTY_V2PUB.person_rec_TYPE;
SELECT SI.*
FROM IGS_AD_STAT_INT_all SI, IGS_AD_INTERFACE_all I
WHERE si.interface_id = lnInterfaceID
AND I.INTERFACE_ID = SI.INTERFACE_ID
AND SI.STATUS = '2'
AND I.STATUS IN ('1','4'); --4035277, if address errors out, it sets status =4 for ad_interface
SELECT ai.*
FROM igs_ad_addr_int_all ai,
igs_ad_interface_all i
WHERE ai.interface_id = cp_interface_id
AND ai.status = '2'
AND i.interface_id = ai.interface_id
AND i.status IN ('1','4'); --4035277, if address errors out, it sets status =4 for ad_interface
SELECT api.*
FROM igs_ad_api_int_all api,
igs_ad_interface_all ai
WHERE api.interface_id = cp_interface_id
AND api.status = '2'
AND api.interface_id = ai.interface_id
AND ai.status IN ('1','4'); --4035277, if address errors out, it sets status =4 for ad_interface
SELECT format_mask
FROM igs_pe_person_id_typ
WHERE person_id_type = cp_person_id_type;
SELECT PERSON_ID_TYPE
FROM IGS_PE_PERSON_ID_TYP
WHERE PREFERRED_IND ='Y';
SELECT pehz.ROWID, pehz.*
FROM IGS_PE_HZ_PARTIES pehz
WHERE party_id = cp_person_id;
l_party_last_update_date DATE;
SAVEPOINT before_insert;
UPDATE IGS_AD_INTERFACE_ALL
SET ERROR_CODE = 'E201',
STATUS = '3'
WHERE INTERFACE_ID = P_Person_Rec.Interface_Id;
UPDATE IGS_AD_INTERFACE_ALL
SET ERROR_CODE = 'E202',
STATUS = '3'
WHERE INTERFACE_ID = P_Person_Rec.Interface_Id;
UPDATE IGS_AD_INTERFACE_ALL
SET ERROR_CODE = 'E203',
STATUS = '3'
WHERE INTERFACE_ID = P_Person_Rec.Interface_Id;
UPDATE IGS_AD_INTERFACE_all
SET STATUS = '3' ,
ERROR_CODE = 'E204'
WHERE INTERFACE_ID = P_person_rec.INTERFACE_ID;
UPDATE IGS_AD_INTERFACE_ALL
SET ERROR_CODE = 'E285', STATUS = '3'
WHERE INTERFACE_ID = P_Person_Rec.Interface_Id;
UPDATE IGS_AD_INTERFACE_ALL
SET ERROR_CODE = 'E268',
STATUS = '3'
WHERE INTERFACE_ID = P_Person_Rec.Interface_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_person_rec.surname,
X_MIDDLE_NAME => p_person_rec.middle_name,
X_GIVEN_NAMES=> p_person_rec.given_names,
X_SEX => p_person_rec.sex,
X_TITLE => p_person_rec.title,
X_STAFF_MEMBER_IND => NULL,
X_DECEASED_IND => 'N',
X_SUFFIX => p_person_rec.suffix,
X_PRE_NAME_ADJUNCT => p_person_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 => NULL,
X_PROOF_OF_INS => NVL(p_person_rec.proof_of_ins,'N'),
X_PROOF_OF_IMMU=> NVL(p_person_rec.proof_of_immu,'N'),
X_BIRTH_DT=>p_person_rec.birth_dt,
X_SALUTATION => NULL,
X_ORACLE_USERNAME => NULL,
X_PREFERRED_GIVEN_NAME=> l_preferred_given_name,
X_EMAIL_ADDR=> NULL,
X_LEVEL_OF_QUAL_ID => p_person_rec.level_of_qual_id,
X_MILITARY_SERVICE_REG=>NVL(p_person_rec.MILITARY_SERVICE_REG,'N'),
X_VETERAN=> NVL(p_person_rec.veteran,'VETERAN_NOT'), -- ssawhney 2203778, lookup_code
x_hz_parties_ovn => l_object_version_number,
X_attribute_CATEGORY => p_person_rec.attribute_category,
X_attribute1 => p_person_rec.attribute1,
X_attribute2 => p_person_rec.attribute2,
X_attribute3 => p_person_rec.attribute3,
X_attribute4 => p_person_rec.attribute4,
X_attribute5 => p_person_rec.attribute5,
X_attribute6 => p_person_rec.attribute6,
X_attribute7 => p_person_rec.attribute7,
X_attribute8 => p_person_rec.attribute8,
X_attribute9 => p_person_rec.attribute9,
X_attribute10 => p_person_rec.attribute10,
X_attribute11 => p_person_rec.attribute11,
X_attribute12 => p_person_rec.attribute12,
X_attribute13 => p_person_rec.attribute13,
X_attribute14 => p_person_rec.attribute14,
X_attribute15 => p_person_rec.attribute15,
X_attribute16 => p_person_rec.attribute16,
X_attribute17 => p_person_rec.attribute17,
X_attribute18 => p_person_rec.attribute18,
X_attribute19 => p_person_rec.attribute19,
X_attribute20 => p_person_rec.attribute20,
X_PERSON_ID_TYPE=> l_pref_altid_type,
X_API_PERSON_ID => l_pref_altid,
X_MODE => 'R',
X_attribute21 => p_person_rec.attribute21,
X_attribute22 => p_person_rec.attribute22,
X_attribute23 => p_person_rec.attribute23,
X_attribute24 => p_person_rec.attribute24
);
IGS_PE_HZ_PARTIES_PKG.UPDATE_ROW(
X_ROWID => tlinfo2.ROWID,
X_PARTY_ID => tlinfo2.party_id,
X_DECEASED_IND => tlinfo2.deceased_ind,
X_ARCHIVE_EXCLUSION_IND => tlinfo2.archive_exclusion_ind,
X_ARCHIVE_DT => tlinfo2.archive_dt,
X_PURGE_EXCLUSION_IND => tlinfo2.purge_exclusion_ind,
X_PURGE_DT => tlinfo2.purge_dt,
X_ORACLE_USERNAME => tlinfo2.oracle_username,
X_PROOF_OF_INS => tlinfo2.proof_of_ins,
X_PROOF_OF_IMMU => tlinfo2.proof_of_immu,
X_LEVEL_OF_QUAL => tlinfo2.level_of_qual,
X_MILITARY_SERVICE_REG => tlinfo2.military_service_reg,
X_VETERAN => tlinfo2.veteran,
X_INSTITUTION_CD => tlinfo2.institution_cd,
X_OI_LOCAL_INSTITUTION_IND => tlinfo2.oi_local_institution_ind,
X_OI_OS_IND => tlinfo2.oi_os_ind,
X_OI_GOVT_INSTITUTION_CD => tlinfo2.oi_govt_institution_cd,
X_OI_INST_CONTROL_TYPE => tlinfo2.oi_inst_control_type,
X_OI_INSTITUTION_TYPE => tlinfo2.oi_institution_type,
X_OI_INSTITUTION_STATUS => tlinfo2.oi_institution_status,
X_OU_START_DT => tlinfo2.ou_start_dt,
X_OU_END_DT => tlinfo2.ou_end_dt,
X_OU_MEMBER_TYPE => tlinfo2.ou_member_type,
X_OU_ORG_STATUS => tlinfo2.ou_org_status,
X_OU_ORG_TYPE => tlinfo2.ou_org_type,
X_INST_ORG_IND => tlinfo2.inst_org_ind,
X_FUND_AUTHORIZATION => tlinfo2.fund_authorization,
X_PE_INFO_VERIFY_TIME => tlinfo2.pe_info_verify_time,
X_birth_city => p_person_rec.birth_city,
X_birth_country => p_person_rec.birth_country,
x_oss_org_unit_cd => NULL, --mmkumar, party number impact
X_felony_convicted_flag => p_person_rec.felony_convicted_flag,
X_MODE => 'R'
);
ROLLBACK TO BEFORE_INSERT;
UPDATE IGS_AD_INTERFACE_all
SET status = '3',
error_code = 'E322'
WHERE interface_id = p_person_rec.interface_id;
UPDATE IGS_AD_INTERFACE_all
SET STATUS = '1',
ERROR_CODE = NULL --ssomani, added this 3/15/01
WHERE INTERFACE_ID = P_person_rec.INTERFACE_ID;
ROLLBACK TO BEFORE_INSERT;
UPDATE IGS_AD_INTERFACE_all
SET status = '3',
error_code = l_error_code
WHERE interface_id = p_person_rec.interface_id;
SAVEPOINT before_insert_stats;
SELECT BIRTH_DATE
FROM IGS_PE_PERSON_BASE_V WHERE
PERSON_ID =cp_person_id;
igs_pe_stat_pkg.insert_row(
X_ACTION=> 'INSERT',
X_ROWID=> l_rowid,
X_PERSON_ID => p_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
);
ROLLBACK TO before_insert_stats;
UPDATE IGS_AD_STAT_INT_ALL
SET STATUS = '3',
ERROR_CODE = 'E005'
WHERE INTERFACE_STAT_ID = stat_rec.INTERFACE_STAT_ID;
UPDATE igs_ad_interface_all
SET status = '4',
error_code = 'E005'
WHERE interface_id = p_person_rec.interface_id;
UPDATE IGS_AD_STAT_INT_ALL
SET STATUS = '1',
ERROR_CODE = NULL, --ssomani, added this 3/15/01
PERSON_ID = P_PERSON_ID
WHERE INTERFACE_STAT_ID = stat_rec.INTERFACE_STAT_ID;
ROLLBACK TO before_insert_stats;
UPDATE IGS_AD_STAT_INT_ALL
SET status = '3',
error_code = l_error_code
WHERE INTERFACE_STAT_ID = stat_rec.INTERFACE_STAT_ID;
UPDATE igs_ad_interface_all
SET status = '4',
error_code = 'E005'
WHERE interface_id = p_person_rec.interface_id;
ROLLBACK TO before_insert_stats;
UPDATE IGS_AD_STAT_INT_ALL
SET STATUS = '3',
ERROR_CODE = 'E005'
WHERE INTERFACE_STAT_ID = stat_rec.INTERFACE_STAT_ID;
UPDATE igs_ad_interface_all
SET status = '4',
error_code = 'E005'
WHERE interface_id = p_person_rec.interface_id;
SELECT site.location_id, site.party_site_id
FROM hz_locations loc, hz_party_sites site
WHERE site.party_id = cp_person_id
AND site.location_id = loc.location_id
AND UPPER(NVL(loc.address1,'X')) = UPPER(NVL(cp_addr_rec.addr_line_1,'X'))
AND UPPER(NVL(loc.address2,'X')) = UPPER(NVL(cp_addr_rec.addr_line_2,'X'))
AND UPPER(NVL(loc.address3,'X')) = UPPER(NVL(cp_addr_rec.addr_line_3,'X'))
AND UPPER(NVL(loc.address4,'X')) = UPPER(NVL(cp_addr_rec.addr_line_4,'X'))
AND UPPER(NVL(loc.city,'X')) = UPPER(NVL(cp_addr_rec.city,'X'))
AND UPPER(NVL(loc.state,'X')) = UPPER(NVL(cp_addr_rec.state,'X'))
AND loc.country = cp_addr_rec.country
AND UPPER(NVL(loc.county,'X')) = UPPER(NVL(cp_addr_rec.county,'X'))
AND UPPER(NVL(loc.province,'X')) = UPPER(NVL(cp_addr_rec.province,'X'));
UPDATE_ADDRESS(
P_ADDR_REC => ADDR_REC,
P_PERSON_ID => p_person_id,
P_LOCATION_ID => l_location_id,
p_party_site_id => l_party_site_id);
UPDATE igs_ad_interface_all
SET status = '4',
error_code = 'E006'
WHERE interface_id = p_person_rec.interface_id;
UPDATE igs_ad_api_int_all
SET status = l_status,
error_code = l_error_code
WHERE interface_api_id = api_rec.interface_api_id;
UPDATE igs_ad_interface_all
SET status = '4',
error_code = 'E007'
WHERE interface_id = p_person_rec.interface_id;
UPDATE igs_ad_api_int_all
SET status = '1',
error_code = null
WHERE interface_api_id = api_rec.interface_api_id;
PROCEDURE UPDATE_PERSON( p_person_rec IN IGS_AD_INTERFACE_DTL_DSCP_V%ROWTYPE,
P_ADDR_TYPE IN VARCHAR2,
P_PERSON_ID_TYPE IN VARCHAR2,
P_PERSON_ID IN IGS_PE_PERSON.PERSON_ID%TYPE) AS
/*
|| Created By : nsinha
|| Created On : 22-JUN-2001
|| Purpose : This procedure process the Application
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| skpandey 21-SEP-2005 Bug: 3663505
|| Description: Added ATTRIBUTES 21 TO 24 to store additional information
|| pkpatel 22-JUN-2001 Bug no.1834307 :For Modeling and Forecasting DLD
|| Modified the signature by changing the datatype of parameter from
|| igs_ad_interface_all%ROWTYPE to igs_ad_interface_dtl_dscp_v%ROWTYPE
|| nsinha 08-Apr-2002 Bug no.2028066: Added Cursor for Null handling Rule.
|| pkpatel 25-DEC-2002 Bug No: 2702536
|| Added commit after the processing each person record.
|| vrathi 25-jun-2003 Bug No:3019813
|| Added check to set error code and status if the person already exists in HR
|| asbala 12-APR-2004 3313276: Use lookup_type HZ_GENDER to validate Gender
|| mmkumar 19-JUL-2005 passed NULL for x_oss_org_unit_cd in add_row call to igs_pe_hz_parties_pkg,
|| (reverse chronological order - newest change first)
*/
l_miss_person_rec HZ_PARTY_V2PUB.person_rec_TYPE;
SELECT AI.*
FROM IGS_AD_ADDR_INT_ALL AI
WHERE (AI.INTERFACE_ID = cp_interface_id)
AND NVL(AI.STATUS, '2') = '2';
SELECT *
FROM IGS_AD_API_INT_ALL
WHERE INTERFACE_ID = cp_interface_id
AND NVL(STATUS, '2') = '2'; -- The mandatory data restriction for other person type is removed
SELECT
p.rowid row_id,
p.party_id person_id,
p.party_number person_number,
p.party_name person_name,
NULL staff_member_ind,
p.person_last_name surname,
p.person_first_name given_names,
p.person_middle_name middle_name,
p.person_name_suffix suffix,
p.person_pre_name_adjunct pre_name_adjunct,
p.person_title title,
p.email_address email_addr,
p.salutation,
p.known_as preferred_given_name,
pd.proof_of_ins,
pd.proof_of_immu,
pd.level_of_qual level_of_qual_id,
pd.military_service_reg,
pd.veteran,
DECODE(pp.date_of_death,NULL,NVL(pd.deceased_ind,'N'),'Y') deceased_ind,
pp.gender sex,
pp.date_of_death deceased_date,
pp.date_of_birth birth_dt,
pd.archive_exclusion_ind,
pd.archive_dt,
pd.purge_exclusion_ind,
pd.purge_dt,
pit.person_id_type,
pit.api_person_id,
pd.fund_authorization,
p.attribute_category,
p.attribute1,
p.attribute2,
p.attribute3,
p.attribute4,
p.attribute5,
p.attribute6,
p.attribute7,
p.attribute8,
p.attribute9,
p.attribute10,
p.attribute11,
p.attribute12,
p.attribute13,
p.attribute14,
p.attribute15,
p.attribute16,
p.attribute17,
p.attribute18,
p.attribute19,
p.attribute20,
p.attribute21,
p.attribute22,
p.attribute23,
p.attribute24,
pd.oracle_username ,
pd.birth_city,
pd.birth_country,
p.object_version_number,
p.status,
pd.felony_convicted_flag
FROM
hz_parties p,
igs_pe_hz_parties pd,
hz_person_profiles pp,
igs_pe_person_id_type_v pit
WHERE p.party_id = cp_person_id
AND p.party_id = pit.pe_person_id (+)
AND p.party_id = pd.party_id (+)
AND p.party_id = pp.party_id
AND SYSDATE BETWEEN pp.effective_start_date AND NVL(pp.effective_end_date,SYSDATE);
SELECT format_mask
FROM igs_pe_person_id_typ
WHERE person_id_type = cp_person_id_type;
SELECT PERSON_ID_TYPE
FROM IGS_PE_PERSON_ID_TYP
WHERE PREFERRED_IND ='Y';
SELECT pehz.ROWID, pehz.*
FROM IGS_PE_HZ_PARTIES pehz
WHERE party_id = cp_person_id;
SELECT last_update_date, ROWID
FROM hz_parties
WHERE party_id = cp_person_id;
l_Last_Update_Date DATE;
l_prog_label := 'igs.plsql.igs_ad_imp_002.update_person';
l_label := 'igs.plsql.igs_ad_imp_002.update_person.';
l_label := 'igs.plsql.igs_ad_imp_002.update_person.begin';
UPDATE IGS_AD_INTERFACE_ALL
SET ERROR_CODE = 'E201',
STATUS = '3'
WHERE INTERFACE_ID = P_Person_Rec.Interface_Id;
UPDATE IGS_AD_INTERFACE_ALL
SET ERROR_CODE = 'E202',
STATUS = '3'
WHERE INTERFACE_ID = P_Person_Rec.Interface_Id;
UPDATE IGS_AD_INTERFACE_ALL
SET ERROR_CODE = 'E203',
STATUS = '3'
WHERE INTERFACE_ID = P_Person_Rec.Interface_Id;
UPDATE IGS_AD_INTERFACE_ALL
SET ERROR_CODE = 'E285',
STATUS = '3'
WHERE INTERFACE_ID = P_Person_Rec.Interface_Id;
UPDATE IGS_AD_INTERFACE_ALL
SET ERROR_CODE = 'E268',
STATUS = '3'
WHERE INTERFACE_ID = P_Person_Rec.Interface_Id;
FETCH hz_parties_cur INTO l_last_update_date, l_rowid;
UPDATE IGS_AD_INTERFACE_ALL
SET ERROR_CODE = 'E019',
STATUS = '3'
WHERE INTERFACE_ID = P_Person_Rec.Interface_Id;
SAVEPOINT BEFORE_UPDATE;
IGS_PE_PERSON_PKG.UPDATE_ROW(
X_LAST_UPDATE_DATE => l_last_update_date,
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 => p_person_id,
X_PERSON_NUMBER => c_null_hdlg_per_rec.person_number,
X_SURNAME => NVL(p_person_rec.surname,c_null_hdlg_per_rec.surname),
X_MIDDLE_NAME => NVL(p_person_rec.middle_name,c_null_hdlg_per_rec.middle_name),
X_GIVEN_NAMES=> NVL(p_person_rec.given_names,c_null_hdlg_per_rec.given_names),
X_SEX => NVL(p_person_rec.sex,c_null_hdlg_per_rec.sex),
X_TITLE => NVL(p_person_rec.title,c_null_hdlg_per_rec.title),
X_STAFF_MEMBER_IND => c_null_hdlg_per_rec.staff_member_ind,
X_DECEASED_IND => c_null_hdlg_per_rec.deceased_ind,
X_SUFFIX => NVL(p_person_rec.suffix,c_null_hdlg_per_rec.suffix),
X_PRE_NAME_ADJUNCT => NVL(p_person_rec.pre_name_adjunct,c_null_hdlg_per_rec.pre_name_adjunct), ---here
X_ARCHIVE_EXCLUSION_IND => c_null_hdlg_per_rec.archive_exclusion_ind,
X_ARCHIVE_DT => c_null_hdlg_per_rec.archive_dt,
X_PURGE_EXCLUSION_IND => c_null_hdlg_per_rec.purge_exclusion_ind,
X_PURGE_DT => c_null_hdlg_per_rec.purge_dt,
X_DECEASED_DATE => c_null_hdlg_per_rec.deceased_date,
X_PROOF_OF_INS => NVL(p_person_rec.proof_of_ins,c_null_hdlg_per_rec.proof_of_ins),
X_PROOF_OF_IMMU=> NVL(p_person_rec.proof_of_immu,c_null_hdlg_per_rec.proof_of_immu),
X_BIRTH_DT => NVL(p_person_rec.birth_dt,c_null_hdlg_per_rec.birth_dt),
X_SALUTATION => c_null_hdlg_per_rec.salutation,
X_ORACLE_USERNAME => c_null_hdlg_per_rec.oracle_username,
X_PREFERRED_GIVEN_NAME=> NVL(l_preferred_given_name,c_null_hdlg_per_rec.preferred_given_name),
X_EMAIL_ADDR=> c_null_hdlg_per_rec.email_addr,
X_LEVEL_OF_QUAL_ID => NVL(p_person_rec.level_of_qual_id,c_null_hdlg_per_rec.level_of_qual_id),
X_MILITARY_SERVICE_REG=> NVL( p_person_rec.military_service_reg,c_null_hdlg_per_rec.military_service_reg),
X_VETERAN=> NVL(p_person_rec.veteran,'VETERAN_NOT'), --ssawhney 2203778 lookup_code
x_hz_parties_ovn => c_null_hdlg_per_rec.object_version_number,
X_attribute_CATEGORY => NVL( p_person_rec.attribute_category,c_null_hdlg_per_rec.attribute_category),
X_attribute1 => NVL(p_person_rec.attribute1,c_null_hdlg_per_rec.attribute1),
X_attribute2 => NVL(p_person_rec.attribute2,c_null_hdlg_per_rec.attribute2),
X_attribute3 => NVL(p_person_rec.attribute3,c_null_hdlg_per_rec.attribute3),
X_attribute4 => NVL(p_person_rec.attribute4,c_null_hdlg_per_rec.attribute4),
X_attribute5 => NVL(p_person_rec.attribute5,c_null_hdlg_per_rec.attribute5),
X_attribute6 => NVL(p_person_rec.attribute6,c_null_hdlg_per_rec.attribute6),
X_attribute7 => NVL(p_person_rec.attribute7,c_null_hdlg_per_rec.attribute7),
X_attribute8 => NVL(p_person_rec.attribute8,c_null_hdlg_per_rec.attribute8),
X_attribute9 => NVL(p_person_rec.attribute9,c_null_hdlg_per_rec.attribute9),
X_attribute10 => NVL(p_person_rec.attribute10,c_null_hdlg_per_rec.attribute10),
X_attribute11 => NVL(p_person_rec.attribute11,c_null_hdlg_per_rec.attribute11),
X_attribute12 => NVL(p_person_rec.attribute12,c_null_hdlg_per_rec.attribute12),
X_attribute13 => NVL(p_person_rec.attribute13,c_null_hdlg_per_rec.attribute13),
X_attribute14 => NVL(p_person_rec.attribute14,c_null_hdlg_per_rec.attribute14),
X_attribute15 => NVL(p_person_rec.attribute15,c_null_hdlg_per_rec.attribute15),
X_attribute16 => NVL(p_person_rec.attribute16,c_null_hdlg_per_rec.attribute16),
X_attribute17 => NVL(p_person_rec.attribute17,c_null_hdlg_per_rec.attribute17),
X_attribute18 => NVL(p_person_rec.attribute18,c_null_hdlg_per_rec.attribute18),
X_attribute19 => NVL(p_person_rec.attribute19,c_null_hdlg_per_rec.attribute19),
X_attribute20 => NVL(p_person_rec.attribute20,c_null_hdlg_per_rec.attribute20),
X_PERSON_ID_TYPE=> l_pref_altid_type,
X_API_PERSON_ID => l_pref_altid,
X_MODE => 'R',
X_attribute21 => NVL(p_person_rec.attribute21,c_null_hdlg_per_rec.attribute21),
X_attribute22 => NVL(p_person_rec.attribute22,c_null_hdlg_per_rec.attribute22),
X_attribute23 => NVL(p_person_rec.attribute23,c_null_hdlg_per_rec.attribute23),
X_attribute24 => NVL(p_person_rec.attribute24,c_null_hdlg_per_rec.attribute24)
);
ROLLBACK TO BEFORE_UPDATE;
UPDATE IGS_AD_INTERFACE_all
SET status = '3',
error_code = l_error_code
WHERE interface_id = p_person_rec.interface_id;
l_label := 'igs.plsql.igs_ad_imp_002.update_person.exception'||l_error_code;
l_debug_str := 'IGS_AD_IMP_002.Update_Person' || ' Exception from IGS_PE_PERSON_PKG ' ||
' Interface Id : ' || ( P_person_rec.INTERFACE_ID) ||
' Status : 3' || ' ErrorCode : '||l_error_code||' SQLERRM:' || SQLERRM;
ROLLBACK TO BEFORE_UPDATE;
UPDATE IGS_AD_INTERFACE_all
SET STATUS = '3',
ERROR_CODE = 'E014'
WHERE INTERFACE_ID = P_person_rec.INTERFACE_ID;
l_label := 'igs.plsql.igs_ad_imp_002.update_person.exception'||'E014';
l_debug_str := 'IGS_AD_IMP_002.Update_Person ' || 'Error from IGS_PE_PERSON_PKG. HzMesg : ' || l_msg_data ||
' Interface Id : ' || (P_person_rec.INTERFACE_ID) ||
' Status : 3' || ' ErrorCode : E014';
UPDATE IGS_AD_INTERFACE_all
SET STATUS = '1',
ERROR_CODE = NULL --ssomani, added this 3/15/01
WHERE INTERFACE_ID = P_person_rec.INTERFACE_ID;
SELECT site.location_id , site.party_site_id
FROM hz_locations loc, hz_party_sites site
WHERE site.party_id = cp_person_id
AND site.location_id = loc.location_id
AND UPPER(NVL(loc.address1,'X')) = UPPER(NVL(cp_addr_rec.addr_line_1,'X'))
AND UPPER(NVL(loc.address2,'X')) = UPPER(NVL(cp_addr_rec.addr_line_2,'X'))
AND UPPER(NVL(loc.address3,'X')) = UPPER(NVL(cp_addr_rec.addr_line_3,'X'))
AND UPPER(NVL(loc.address4,'X')) = UPPER(NVL(cp_addr_rec.addr_line_4,'X'))
AND UPPER(NVL(loc.city,'X')) = UPPER(NVL(cp_addr_rec.city,'X'))
AND UPPER(NVL(loc.state,'X')) = UPPER(NVL(cp_addr_rec.state,'X'))
AND loc.country = cp_addr_rec.country
AND UPPER(NVL(loc.county,'X')) = UPPER(NVL(cp_addr_rec.county,'X'))
AND UPPER(NVL(loc.province,'X')) = UPPER(NVL(cp_addr_rec.province,'X'));
l_label := 'igs.plsql.igs_ad_imp_002.update_person.duplicate_address_exists';
UPDATE_ADDRESS( P_ADDR_REC => ADDR_REC,
P_PERSON_ID => p_person_id,
P_LOCATION_ID => l_location_id,
p_party_site_id => l_party_site_id);
l_label := 'igs.plsql.igs_ad_imp_002.update_person.duplicate_address_doesnot_exist';
UPDATE igs_ad_api_int_all
SET status = l_status,
ERROR_CODE = l_error_code
WHERE interface_api_id = api_rec.interface_api_id;
UPDATE igs_ad_interface_all
SET status = '4',
ERROR_CODE = 'E007'
WHERE interface_id = p_person_rec.interface_id;
UPDATE igs_ad_api_int_all
SET status = '1',
ERROR_CODE = NULL
WHERE interface_api_id = api_rec.interface_api_id;
END UPDATE_PERSON;
|| asbala 12-nov-03 3227107: address changes - signature of igs_pe_person_addr_pkg.insert_row changed
*/
CURSOR source_type_cur(cp_source_type igs_pe_src_types_all.source_type%TYPE) Is
SELECT source_type_id
FROM igs_pe_src_types_all
WHERE source_type = cp_source_type;
SELECT format_mask, region_ind
FROM igs_pe_person_id_typ
WHERE person_id_type = cp_person_id_type
AND closed_ind = 'N';
SELECT 'X'
FROM hz_geographies
WHERE GEOGRAPHY_TYPE = cp_geography_type
AND geography_code = cp_geography_cd
AND country_code = cp_country_cd;
SELECT COUNT(*)
INTO lnDupExist
FROM IGS_PE_ALT_PERS_ID
WHERE PE_PERSON_ID = P_PERSON_ID
AND API_PERSON_ID = P_API_REC.ALTERNATE_ID
AND PERSON_ID_TYPE = P_API_REC.PERSON_ID_TYPE
AND TRUNC(START_DT) = l_start_dt;
SAVEPOINT before_insert;
Igs_Pe_Alt_Pers_Id_Pkg.insert_row(
X_ROWID => l_rowid,
X_PE_PERSON_ID => P_PERSON_ID,
X_API_PERSON_ID => p_api_rec.ALTERNATE_ID,
X_PERSON_ID_TYPE => p_api_rec.PERSON_ID_TYPE,
X_START_DT => l_start_dt,
X_END_DT => p_api_rec.end_dt,
x_attribute_category => p_api_rec.attribute_category,
x_attribute1 => p_api_rec.attribute1,
x_attribute2 => p_api_rec.attribute2,
x_attribute3 => p_api_rec.attribute3,
x_attribute4 => p_api_rec.attribute4,
x_attribute5 => p_api_rec.attribute5,
x_attribute6 => p_api_rec.attribute6,
x_attribute7 => p_api_rec.attribute7,
x_attribute8 => p_api_rec.attribute8,
x_attribute9 => p_api_rec.attribute9,
x_attribute10 => p_api_rec.attribute10,
x_attribute11 => p_api_rec.attribute11,
x_attribute12 => p_api_rec.attribute12,
x_attribute13 => p_api_rec.attribute13,
x_attribute14 => p_api_rec.attribute14,
x_attribute15 => p_api_rec.attribute15,
x_attribute16 => p_api_rec.attribute16,
x_attribute17 => p_api_rec.attribute17,
x_attribute18 => p_api_rec.attribute18,
x_attribute19 => p_api_rec.attribute19,
x_attribute20 => p_api_rec.attribute20,
x_region_cd => p_api_rec.region_cd,
X_MODE=>'R');
ROLLBACK TO before_insert;
SELECT api.rowid,api.*
FROM igs_pe_alt_pers_id api
WHERE pe_person_id = cp_person_id
AND api_person_id = cp_api_person_id
AND person_id_type = cp_person_id_type
AND TRUNC(start_dt) = cp_start_dt;
SAVEPOINT before_api_update;
igs_pe_alt_pers_id_pkg.update_row(
x_rowid =>c_null_hdlg_alt_pers_cur_rec.rowid,
x_pe_person_id =>c_null_hdlg_alt_pers_cur_rec.pe_person_id,
x_api_person_id =>c_null_hdlg_alt_pers_cur_rec.api_person_id,
x_person_id_type => c_null_hdlg_alt_pers_cur_rec.person_id_type,
x_start_dt => NVL(p_api_rec.start_dt,c_null_hdlg_alt_pers_cur_rec.start_dt),
x_end_dt => NVL(p_api_rec.end_dt,c_null_hdlg_alt_pers_cur_rec.end_dt),
x_mode => 'R',
X_ATTRIBUTE_CATEGORY =>NVL(p_api_rec.attribute_category ,c_null_hdlg_alt_pers_cur_rec.attribute_category),
X_ATTRIBUTE1 =>NVL(p_api_rec.attribute1 ,c_null_hdlg_alt_pers_cur_rec.attribute1),
X_ATTRIBUTE2 =>NVL(p_api_rec.attribute2 ,c_null_hdlg_alt_pers_cur_rec.attribute2),
X_ATTRIBUTE3 =>NVL(p_api_rec.attribute3 ,c_null_hdlg_alt_pers_cur_rec.attribute3),
X_ATTRIBUTE4 =>NVL(p_api_rec.attribute4 ,c_null_hdlg_alt_pers_cur_rec.attribute4),
X_ATTRIBUTE5 =>NVL(p_api_rec.attribute5 ,c_null_hdlg_alt_pers_cur_rec.attribute5),
X_ATTRIBUTE6 =>NVL(p_api_rec.attribute6 ,c_null_hdlg_alt_pers_cur_rec.attribute6),
X_ATTRIBUTE7 =>NVL(p_api_rec.attribute7 ,c_null_hdlg_alt_pers_cur_rec.attribute7),
X_ATTRIBUTE8 =>NVL(p_api_rec.attribute8 ,c_null_hdlg_alt_pers_cur_rec.attribute8),
X_ATTRIBUTE9 =>NVL(p_api_rec.attribute9 ,c_null_hdlg_alt_pers_cur_rec.attribute9),
X_ATTRIBUTE10 =>NVL(p_api_rec.attribute10 ,c_null_hdlg_alt_pers_cur_rec.attribute10),
X_ATTRIBUTE11 =>NVL(p_api_rec.attribute11 ,c_null_hdlg_alt_pers_cur_rec.attribute11),
X_ATTRIBUTE12 =>NVL(p_api_rec.attribute12 ,c_null_hdlg_alt_pers_cur_rec.attribute12),
X_ATTRIBUTE13 =>NVL(p_api_rec.attribute13 ,c_null_hdlg_alt_pers_cur_rec.attribute13),
X_ATTRIBUTE14 =>NVL(p_api_rec.attribute14 ,c_null_hdlg_alt_pers_cur_rec.attribute14),
X_ATTRIBUTE15 =>NVL(p_api_rec.attribute15 ,c_null_hdlg_alt_pers_cur_rec.attribute15),
X_ATTRIBUTE16 =>NVL(p_api_rec.attribute16 ,c_null_hdlg_alt_pers_cur_rec.attribute16),
X_ATTRIBUTE17 =>NVL(p_api_rec.attribute17 ,c_null_hdlg_alt_pers_cur_rec.attribute17),
X_ATTRIBUTE18 =>NVL(p_api_rec.attribute18 ,c_null_hdlg_alt_pers_cur_rec.attribute18),
X_ATTRIBUTE19 =>NVL(p_api_rec.attribute19 ,c_null_hdlg_alt_pers_cur_rec.attribute19),
X_ATTRIBUTE20 =>NVL(p_api_rec.attribute20 ,c_null_hdlg_alt_pers_cur_rec.attribute20),
X_REGION_CD =>NVL(p_api_rec.region_cd ,c_null_hdlg_alt_pers_cur_rec.region_cd));
ROLLBACK TO before_api_update;
l_debug_str := 'IGS_AD_IMP_002.create_api (UPDATE) ' ||'Interface Api Id : '
|| p_api_rec.interface_api_id ||'Status : 3'
|| 'ErrorCode : '||p_error_code||' SQLERRM:' ||SQLERRM;
l_last_update_date DATE;
SELECT 'X'
FROM HZ_PARTY_SITE_USES
WHERE party_site_id = cp_party_site_id
AND site_use_type = cp_site_use_code;
SELECT *
FROM IGS_AD_ADDRUSAGE_INT_all
WHERE interface_addr_id = cp_interface_addr_id
AND status = '2';
l_profile_last_update_date DATE;
l_site_use_last_update_date DATE;
UPDATE IGS_AD_ADDRUSAGE_INT_all
SET STATUS = '1',
ERROR_CODE = NULL
WHERE interface_addrusage_id = c_usage_rec.interface_addrusage_id;
p_action => 'INSERT',
p_rowid => l_rowid,
p_party_site_use_id => l_party_site_use_id,
p_party_site_id => l_party_site_id,
p_site_use_type => c_usage_rec.site_use_code,
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_ADDRUSAGE_INT_ALL
SET STATUS = '1',
ERROR_CODE = NULL
WHERE interface_addrusage_id = c_usage_rec.interface_addrusage_id;
UPDATE IGS_AD_ADDRUSAGE_INT_ALL
SET STATUS = '3',
ERROR_CODE = l_error_code
WHERE interface_addrusage_id = c_usage_rec.interface_addrusage_id;
UPDATE igs_ad_addr_int_all
SET status = '4',
error_code = 'E244'
WHERE interface_addr_id = l_interface_addr_id;
UPDATE IGS_AD_ADDRUSAGE_INT_ALL
SET STATUS = '3',
ERROR_CODE = 'E244'
WHERE interface_addrusage_id = l_interface_addrusage_id;
UPDATE igs_ad_addr_int_all
SET status = '4',
error_code = 'E244'
WHERE interface_addr_id = l_interface_addr_id;
/* Validate all columns before Inserting. */
validate_address( p_addr_rec => p_addr_rec,
p_person_id => p_person_id,
p_status => p_status,
p_error_code => p_error_code);
IGS_PE_PERSON_ADDR_PKG.Insert_Row(
P_ACTION => 'INSERT',
P_ROWID => l_RowId,
P_LOCATION_ID => l_location_Id,
P_START_DT => p_addr_rec.Start_date,
P_END_DT => p_addr_rec.End_Date,
P_COUNTRY => p_addr_rec.country,
P_ADDRESS_STYLE => NULL,
P_ADDR_LINE_1 => p_addr_rec.addr_line_1,
P_ADDR_LINE_2 => p_addr_rec.addr_line_2,
P_ADDR_LINE_3 => p_addr_rec.addr_line_3,
P_ADDR_LINE_4 => p_addr_rec.addr_line_4,
P_DATE_LAST_VERIFIED => p_addr_rec.Date_Last_Verified,
P_CORRESPONDENCE => p_addr_rec.CORRESPONDENCE_FLAG,
P_CITY => p_addr_rec.city,
P_STATE => p_addr_rec.state,
P_PROVINCE => p_addr_rec.province,
P_COUNTY => p_addr_rec.county,
P_POSTAL_CODE => p_addr_rec.postcode,
P_ADDRESS_LINES_PHONETIC => NULL,
P_DELIVERY_POINT_CODE => p_addr_rec.delivery_point_code,
P_OTHER_DETAILS_1 => p_addr_rec.other_details_1,
P_OTHER_DETAILS_2 => p_addr_rec.other_details_2,
P_OTHER_DETAILS_3 => p_addr_rec.other_details_3,
L_RETURN_STATUS => l_Return_Status,
L_MSG_DATA => l_Msg_Data,
P_PARTY_ID => P_PERSON_ID,
P_PARTY_SITE_ID => l_Party_Site_Id,
P_PARTY_TYPE => 'PERSON',
P_LAST_UPDATE_DATE => l_last_update_date,
p_party_site_ovn => l_party_site_ovn,
p_location_ovn => l_location_ovn,
p_status => 'A'
);
UPDATE IGS_AD_ADDR_INT_ALL
SET STATUS = '3',
ERROR_CODE = 'E006'
WHERE INTERFACE_ADDR_ID = p_ADDR_REC.INTERFACE_ADDR_ID;
UPDATE igs_ad_interface_all
SET status = '4',
error_code = 'E006'
WHERE interface_id = p_addr_rec.interface_id;
|| 'Error from IGS_PE_PERSON_ADDR_PKG.INSERT_ROW HzMesg : ' || l_msg_data
|| ' Interface Addr Id : ' || p_addr_rec.interface_addr_id
|| ' Status : 3' || ' ErrorCode : E006';
UPDATE IGS_AD_ADDR_INT_ALL
SET STATUS = '4',
ERROR_CODE = 'E073'
WHERE INTERFACE_ADDR_ID = p_ADDR_REC.INTERFACE_ADDR_ID;
|| 'Warning from IGS_PE_PERSON_ADDR_PKG.INSERT_ROW HzMesg : ' || l_msg_data
|| ' Interface Addr Id : ' || p_addr_rec.interface_addr_id
|| ' Status : 4' || ' ErrorCode : E073';
UPDATE IGS_AD_ADDR_INT_ALL
SET STATUS = '1',
ERROR_CODE = NULL --ssomani, added this 3/15/01
WHERE INTERFACE_ADDR_ID = P_ADDR_REC.INTERFACE_ADDR_ID;
UPDATE IGS_AD_ADDR_INT_ALL
SET STATUS = '3',
ERROR_CODE = p_error_code
WHERE INTERFACE_ADDR_ID = p_ADDR_REC.INTERFACE_ADDR_ID;
UPDATE igs_ad_interface_all
SET status = '4',
error_code = 'E006'
WHERE interface_id = p_addr_rec.interface_id;
UPDATE IGS_AD_ADDR_INT_ALL
SET STATUS = '3',
ERROR_CODE = 'E006'
WHERE INTERFACE_ADDR_ID = p_ADDR_REC.INTERFACE_ADDR_ID;
UPDATE igs_ad_interface_all
SET status = '4',
error_code = 'E006'
WHERE interface_id = p_addr_rec.interface_id;
|| 'Error from IGS_PE_PERSON_ADDR_PKG .INSERT_ROW :' || l_msg_data
|| ' Interface addr Id : ' || P_addr_REC.INTERFACE_addr_ID
|| ' Status : 3' || ' ErrorCode : E006';
PROCEDURE UPDATE_ADDRESS(
p_addr_rec IN IGS_AD_ADDR_INT_ALL%ROWTYPE,
p_person_id IN IGS_PE_PERSON.PERSON_ID%TYPE,
p_location_id IN hz_party_sites.location_id%TYPE,
p_party_site_id IN hz_party_sites.party_site_id%TYPE) AS
l_rowid VARCHAR2(25);
l_date hz_party_sites.last_update_date%TYPE;
SELECT *
FROM IGS_PE_PERSON_ADDR_V
WHERE location_id = cp_location_id AND
party_site_id = cp_party_site_id;
l_last_update_date DATE;
SELECT 'X'
FROM HZ_PARTY_SITE_USES
WHERE party_site_id = cp_party_site_id
AND site_use_type = cp_site_use_code;
SELECT *
FROM IGS_AD_ADDRUSAGE_INT_ALL
WHERE interface_addr_id = cp_interface_addr_id
AND status = '2';
l_profile_last_update_date DATE;
l_site_use_last_update_date DATE;
l_label := 'igs.plsql.igs_ad_imp_006.update_address.process_addrusage_begin';
UPDATE IGS_AD_ADDRUSAGE_INT_ALL
SET STATUS = '1',
ERROR_CODE = NULL
WHERE interface_addrusage_id = c_usage_rec.interface_addrusage_id;
p_action => 'INSERT',
p_rowid => l_rowid,
p_party_site_use_id => l_party_site_use_id,
p_party_site_id => l_party_site_id,
p_site_use_type => c_usage_rec.site_use_code,
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_ADDRUSAGE_INT_ALL
SET STATUS = '1',
ERROR_CODE = NULL
WHERE interface_addrusage_id = c_usage_rec.interface_addrusage_id;
UPDATE IGS_AD_ADDRUSAGE_INT_ALL
SET STATUS = '3',
ERROR_CODE = l_error_code
WHERE interface_addrusage_id = c_usage_rec.interface_addrusage_id;
UPDATE igs_ad_addr_int_all
SET status = '4',
error_code = 'E244'
WHERE interface_addr_id = l_interface_addr_id;
UPDATE IGS_AD_ADDRUSAGE_INT_ALL
SET STATUS = '3',
ERROR_CODE = 'E244'
WHERE interface_addrusage_id = l_interface_addrusage_id;
UPDATE igs_ad_addr_int_all
SET status = '4',
error_code = 'E244'
WHERE interface_addr_id = l_interface_addr_id;
l_prog_label := 'igs.plsql.igs_ad_imp_002.update_address';
l_label := 'igs.plsql.igs_ad_imp_002.update_address.';
l_label := 'igs.plsql.igs_ad_imp_002.Update_Address.begin';
l_debug_str := 'start of update_address';
IGS_PE_PERSON_ADDR_PKG.Update_Row(
P_ACTION => 'UPDATE',
P_ROWID => l_RowId,
P_LOCATION_ID => l_location_Id,
P_START_DT => nvl(p_addr_rec.Start_date,null_hand_addr_rec.start_dt),
P_END_DT => nvl(p_addr_rec.End_Date,null_hand_addr_rec.end_dt),
P_COUNTRY => p_addr_rec.country,
P_ADDRESS_STYLE => NULL,
P_ADDR_LINE_1 => nvl(p_addr_rec.addr_line_1,null_hand_addr_rec.addr_line_1),
P_ADDR_LINE_2 => nvl(p_addr_rec.addr_line_2,null_hand_addr_rec.addr_line_2),
P_ADDR_LINE_3 => nvl(p_addr_rec.addr_line_3,null_hand_addr_rec.addr_line_3),
P_ADDR_LINE_4 => nvl(p_addr_rec.addr_line_4,null_hand_addr_rec.addr_line_4),
P_DATE_LAST_VERIFIED => nvl(p_addr_rec.Date_Last_Verified,null_hand_addr_rec.date_last_verified),
P_CORRESPONDENCE => nvl(p_addr_rec.CORRESPONDENCE_FLAG,null_hand_addr_rec. CORRESPONDENCE_IND),
P_CITY => nvl(p_addr_rec.city,null_hand_addr_rec.city),
P_STATE => nvl(p_addr_rec.state,null_hand_addr_rec.state),
P_PROVINCE => nvl(p_addr_rec.province,null_hand_addr_rec.province),
P_COUNTY => nvl(p_addr_rec.county,null_hand_addr_rec.county),
P_POSTAL_CODE => nvl(p_addr_rec.postcode,null_hand_addr_rec.postal_code),
P_ADDRESS_LINES_PHONETIC => NULL,
P_DELIVERY_POINT_CODE => nvl(p_addr_rec.delivery_point_code,null_hand_addr_rec.delivery_point_code),
P_OTHER_DETAILS_1 => nvl(p_addr_rec.other_details_1,null_hand_addr_rec.other_details_1),
P_OTHER_DETAILS_2 => nvl(p_addr_rec.other_details_2,null_hand_addr_rec.other_details_2),
P_OTHER_DETAILS_3 => nvl(p_addr_rec.other_details_3,null_hand_addr_rec.other_details_3),
L_RETURN_STATUS => l_Return_Status,
L_MSG_DATA => l_Msg_Data,
P_PARTY_ID => P_PERSON_ID,
P_PARTY_SITE_ID => l_party_site_id,
P_PARTY_TYPE => 'PERSON',
P_LAST_UPDATE_DATE => l_date,
p_party_site_ovn => l_location_ovn,
p_location_ovn => l_party_site_ovn,
p_status => null_hand_addr_rec.status
);
UPDATE igs_ad_addr_int_all
SET status = '3', error_code = 'E014'
WHERE interface_addr_id = p_addr_rec.interface_addr_id;
UPDATE igs_ad_interface_all
SET status = '4', error_code = 'E014'
WHERE interface_id = p_addr_rec.interface_id;
l_label := 'igs.plsql.igs_ad_imp_002.update_address.exception'||p_error_code;
l_debug_str := 'IGS_AD_IMP_002.Update_Address ' || 'Error from IGS_PE_PERSON_ADDRESS_PKG : HzMesg'
|| l_msg_data || ' Interface Addr Id : '
|| (P_addr_REC.INTERFACE_ADDR_ID) ||' Status : 3' || ' ErrorCode : E014 ';
UPDATE igs_ad_addr_int_all
SET status = '4', error_code = 'E073'
WHERE interface_addr_id = p_addr_rec.interface_addr_id;
l_label := 'igs.plsql.igs_ad_imp_002.update_address.warning'||p_error_code;
l_debug_str := 'IGS_AD_IMP_002.Update_Address ' || 'Warning from IGS_PE_PERSON_ADDRESS_PKG : HzMesg'
|| l_msg_data || ' Interface Addr Id : '
|| (P_addr_REC.INTERFACE_ADDR_ID) ||' Status : 4' || ' ErrorCode : E073';
UPDATE igs_ad_addr_int_all
SET status = '1',
ERROR_CODE = NULL
WHERE interface_addr_id = p_addr_rec.interface_addr_id;
UPDATE igs_ad_addr_int_all
SET status = '3', error_code = p_error_code
WHERE interface_addr_id = p_addr_rec.interface_addr_id;
UPDATE igs_ad_interface_all
SET status = '4', error_code = 'E014'
WHERE interface_id = p_addr_rec.interface_id;
l_label := 'igs.plsql.igs_ad_imp_002.update_address.exception'||'E014';
l_debug_str := 'IGS_AD_IMP_002.Update_Address ' || 'Exception from IGS_PE_PERSON_ADDRESS_PKG : HzMesg '
|| l_msg_data || ' Interface Addr Id : '
|| (P_addr_REC.INTERFACE_ADDR_ID) || ' Status : 3' ||
' ErrorCode : '||p_error_code;
END UPDATE_ADDRESS;
|| Modified all SELECT Query into Cursors.
|| pkpatel 25-DEC-2002 Bug No: 2702536
|| Added the new duplicate checking process. This will happen for each record, instead of at batch level
|| asbala 23-SEP-2003 Bug 3130316, Duplicate Person Matching Performance Improvements
|| pkpatel 23-Feb-2006 Bug 4869740 (Modified the datatype in cursor c_get_global_var for cp_match_set_id)
|| (reverse chronological order - newest change first)
*/
l_lvcAction VARCHAR2(1);
SELECT partial_if_null,primary_addr_flag, exclude_inactive_ind
FROM igs_pe_match_sets_all
WHERE match_set_id = cp_match_set_id;
SELECT data_element, drop_if_null, partial_include, exact_include
FROM igs_pe_mtch_set_data_all
WHERE match_set_id = cp_match_set_id;
SELECT ai.*
FROM igs_ad_interface_dtl_dscp_v ai
WHERE status = '2'
AND batch_id = cp_d_batch_id
AND source_type_id = cp_d_source_type_id;
SELECT
p.person_last_name surname,
p.person_first_name given_names,
p.person_middle_name middle_name,
p.person_name_suffix suffix,
p.person_pre_name_adjunct pre_name_adjunct,
p.person_title title,
p.known_as preferred_given_name,
pd.level_of_qual level_of_qual_id,
pp.gender sex,
pp.date_of_birth birth_dt
FROM
hz_parties p,
igs_pe_hz_parties pd,
hz_person_profiles pp
WHERE p.party_id = cp_person_id
AND p.party_id = pd.party_id (+)
AND p.party_id = pp.party_id
AND SYSDATE BETWEEN pp.effective_start_date AND NVL(pp.effective_end_date,SYSDATE);
SELECT status
FROM IGS_AD_INTERFACE_all
WHERE interface_id = cp_interface_id;
SELECT value
FROM igs_pe_mtch_set_data md
WHERE match_set_id = cp_match_set_id
AND md.data_element =cp_type;
SELECT 'X'
FROM hz_parties p,
igs_pe_hz_parties pd,
hz_person_profiles pp,
igs_pe_person_id_type_v pit
WHERE p.party_id = cp_person_id
AND p.party_id = pit.pe_person_id (+)
AND p.party_id = pd.party_id (+)
AND p.party_id = pp.party_id
AND SYSDATE BETWEEN pp.effective_start_date AND NVL(pp.effective_end_date,SYSDATE)
AND NVL(p.person_last_name, '*') = NVL(c_person_rec.surname, '*')
AND NVL(p.person_first_name, '*') = NVL(c_person_rec.given_names, '*')
AND NVL(p.person_name_suffix, '*') = NVL(c_person_rec.suffix, '*')
AND NVL(pp.gender, '*') = NVL(c_person_rec.sex, '*')
AND NVL(p.person_title, '*') = NVL(c_person_rec.title, '*')
AND NVL(pp.date_of_birth, l_default_date) = NVL(c_person_rec.birth_dt, l_default_date)
AND NVL(pd.proof_of_ins, '*') = NVL(c_person_rec.proof_of_ins, '*')
AND NVL(pd.proof_of_immu, '*') = NVL(c_person_rec.proof_of_immu, '*')
AND NVL(pd.level_of_qual, -99) = NVL(c_person_rec.level_of_qual_id, -99)
AND NVL(pd.military_service_reg, '*') = NVL(c_person_rec.military_service_reg, '*')
AND NVL(pd.veteran, '*') = NVL(c_person_rec.veteran, '*')
AND NVL(p.known_as, '*') = NVL(c_person_rec.preferred_given_name, '*')
AND NVL(p.attribute_category, '*') = NVL(c_person_rec.attribute_category, '*')
AND NVL(p.person_middle_name,'*') = NVL(c_person_rec.middle_name,'*')
AND NVL(p.person_pre_name_adjunct,'*') = NVL(c_person_rec.pre_name_adjunct,'*')
AND NVL(p.attribute1, '*') = NVL(c_person_rec.attribute1, '*')
AND NVL(p.attribute2, '*') = NVL(c_person_rec.attribute2, '*')
AND NVL(p.attribute3, '*') = NVL(c_person_rec.attribute3, '*')
AND NVL(p.attribute4, '*') = NVL(c_person_rec.attribute4, '*')
AND NVL(p.attribute5, '*') = NVL(c_person_rec.attribute5, '*')
AND NVL(p.attribute6, '*') = NVL(c_person_rec.attribute6, '*')
AND NVL(p.attribute7, '*') = NVL(c_person_rec.attribute7, '*')
AND NVL(p.attribute8, '*') = NVL(c_person_rec.attribute8, '*')
AND NVL(p.attribute9, '*') = NVL(c_person_rec.attribute9, '*')
AND NVL(p.attribute10, '*') = NVL(c_person_rec.attribute10, '*')
AND NVL(p.attribute11, '*') = NVL(c_person_rec.attribute11, '*')
AND NVL(p.attribute12, '*') = NVL(c_person_rec.attribute12, '*')
AND NVL(p.attribute13, '*') = NVL(c_person_rec.attribute13, '*')
AND NVL(p.attribute14, '*') = NVL(c_person_rec.attribute14, '*')
AND NVL(p.attribute15, '*') = NVL(c_person_rec.attribute15, '*')
AND NVL(p.attribute16, '*') = NVL(c_person_rec.attribute16, '*')
AND NVL(p.attribute17, '*') = NVL(c_person_rec.attribute17, '*')
AND NVL(p.attribute18, '*') = NVL(c_person_rec.attribute18, '*')
AND NVL(p.attribute19, '*') = NVL(c_person_rec.attribute19, '*')
AND NVL(p.attribute20, '*') = NVL(c_person_rec.attribute20, '*')
AND NVL(p.attribute21, '*') = NVL(c_person_rec.attribute21, '*')
AND NVL(p.attribute22, '*') = NVL(c_person_rec.attribute22, '*')
AND NVL(p.attribute23, '*') = NVL(c_person_rec.attribute23, '*')
AND NVL(p.attribute24, '*') = NVL(c_person_rec.attribute24, '*')
AND NVL(pd.felony_convicted_flag, '*') = NVL(c_person_rec.felony_convicted_flag, '*')
AND NVL(pd.birth_city, '*') = NVL(c_person_rec.birth_city, '*')
AND NVL(pd.birth_country, '*') = NVL(c_person_rec.birth_country, '*')
AND NVL(pit.api_person_id, '*') = NVL(c_person_rec.pref_alternate_id, '*');
UPDATE igs_ad_interface_all
SET person_match_ind = cst_mi_val_19, --19 -Match exists and retained existing values
status = cst_stat_val_1,
ERROR_CODE = NULL --ssomani, added this 3/15/01
WHERE interface_id = person_rec.interface_id;
update_person
(p_person_rec=>person_rec,
p_addr_type=> NULL ,
p_person_id_type=> NULL ,
p_person_id=> l_person_id);
UPDATE igs_ad_interface_all
SET person_match_ind = cst_mi_val_18, --18 -Match occured and used import values
status = cst_stat_val_1,
ERROR_CODE = NULL
WHERE interface_id = person_rec.interface_id;
update_person (p_person_rec=>person_rec,
p_addr_type=> NULL ,
p_person_id_type=> NULL ,
p_person_id=>l_person_id);
UPDATE igs_ad_interface_all
SET person_match_ind = cst_mi_val_18, --18 -Match occured and used import values
status = cst_stat_val_1,
ERROR_CODE = NULL
WHERE interface_id = person_rec.interface_id;
UPDATE igs_ad_interface_all
SET person_match_ind = cst_mi_val_23, --ssomani corrected the status updation 3/15/01
status = cst_stat_val_1, --23 - Match to be reviewed, but there was no discrepancy
ERROR_CODE = NULL -- and so retaining the existing values
WHERE interface_id = person_rec.interface_id;
UPDATE igs_ad_interface_all
SET person_match_ind = cst_mi_val_20, --20 - Match To Be Reviewed For Discrepancy
status = cst_stat_val_3--ssomani corrected the status updation 3/15/01
WHERE interface_id = person_rec.interface_id;
UPDATE igs_ad_interface_all
SET status = cst_stat_val_3 -- Record must have been processed in a previous run
WHERE interface_id = person_rec.interface_id;
UPDATE igs_ad_interface_all
SET status = cst_stat_val_1 -- Record must have been processed in a previous run
WHERE interface_id = person_rec.interface_id;
UPDATE igs_ad_interface_all
SET person_match_ind = cst_mi_val_19,--19 -Match exists and retained existing values
status = cst_stat_val_1,
error_code = NULL
WHERE interface_id = person_rec.interface_id;
update_person
(p_person_rec=>person_rec,
p_addr_type=> NULL ,
p_person_id_type=> NULL ,
p_person_id=> l_person_id);
UPDATE igs_ad_interface_all
SET person_match_ind = cst_mi_val_18, --18 -Match occured and used import values
status = cst_stat_val_1,
ERROR_CODE = NULL
WHERE interface_id = person_rec.interface_id;
update_person
(p_person_rec=>person_rec,
p_addr_type=> NULL ,
p_person_id_type=> NULL ,
p_person_id=>l_person_id);
UPDATE igs_ad_interface_all
SET person_match_ind = cst_mi_val_18, --18 -Match occured and used import values
status = cst_stat_val_1,
ERROR_CODE = NULL
WHERE interface_id = person_rec.interface_id;
UPDATE igs_ad_interface_all
SET person_match_ind = cst_mi_val_20, --20 - Match To Be Reviewed For Discrepancy
status = cst_stat_val_3,
error_code = NULL
WHERE interface_id = person_rec.interface_id;
UPDATE igs_ad_interface_all
SET person_match_ind = cst_mi_val_23, --23 - Match to be reviewed, but there was no discrepancy
-- and so retaining the existing values
status = cst_stat_val_1,
error_code = NULL
WHERE interface_id = person_rec.interface_id;
UPDATE igs_ad_interface_all
SET status = '3' -- Record must have been processed in a previous run
WHERE interface_id = person_rec.interface_id;
UPDATE igs_ad_interface_all
SET status = '1' -- Record must have been processed in a previous run
WHERE interface_id = person_rec.interface_id;
UPDATE igs_ad_interface_all
SET person_id = person_rec.person_id
WHERE interface_id = person_rec.interface_id;
SELECT birth_date
FROM igs_pe_person_base_v
WHERE person_id = cp_person_id;
SELECT territory_short_name
FROM FND_TERRITORIES_VL
WHERE territory_code = UPPER(cp_territory_code);