[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Cursor cur_get_lst_date is modified to get last_update_date from HZ_ORG_CONTACT_ROLES table
In Procedure process_contact_phones rel_party_id is passed as argument instead of cust_acct_role_id
In Cursor cur_get_oss_phones reference to cust_acct_role_id is removed and owner_table_id is used to pick the record
In igs_or_contacts_v.insert_row and igs_or_contacts_v.update_row all the attributes that references to customer are removed
In igs_or_phones_v.insert_row and igs_or_phones_v.update_row all the attributes that references to customer are removed
pkpatel 25-OCT-2002 Bug No: 2613704
Modified stat_type_id to stat_type_cd
pkpatel 3-JAn-2003 Bug 2730137
Added the validation for contact party id in process_institution_contacts
ssawhney V2API OVN changes...igs_or_contacts + igs_or_phones.
ssaleem 22-SEP-2003 The following changes were done for IGS.L
a) passed values for create method as 'CREATE_IMPORT'
b) In the WHEN OTHERS block, replaced FND_MESSAGE.GET with
FND_MESSAGE.PARSE_ENCODED
c) Removed the check for 'IGS_AD_EVAL_XST_NO_PROSPCT'
d) Error code 'E162' is replaced with 'E049'
c) FND_FILE.PUT_LINE method calls replaced with
logging mechanism using FND_LOG package
gmaheswa 27-Jan-2006 Bug:4938278 : process_institution_address: Raise Address Change event at after processing address data of all persons.
gmaheswa 22-Jun-06 Bug 5189180: in process_institution_address, if igs_pe_person_addr_pkg.insert_row returns return_status as 'W' then set erro code E022.
***************************************************************/
PROCEDURE process_institution_notes(
p_interface_id IN igs_or_inst_nts_int.interface_id%TYPE,
p_party_id IN hz_parties.party_id%TYPE,
p_party_number IN hz_parties.party_number%TYPE)
/***************************************************************
Created By : mesriniv
Date Created By : 2001/07/12
Purpose : This is the third part of
Import Institutions Package
Known Limitations,Enhancements or Remarks
Change History :
ENH Bug No : 1872994
ENH Desc : Modelling and Forcasting DLD- Institutions Build
Who When What
***************************************************************/
AS
l_insert_success BOOLEAN;
l_update_success BOOLEAN;
SELECT *
FROM igs_or_inst_nts_int
WHERE interface_id =cp_interface_id
AND status =cp_status;
SELECT org_notes.rowid,org_notes.*
FROM igs_or_org_notes org_notes
WHERE org_note_type = p_note_type
AND org_structure_id = cp_party_number
AND org_note_sequence = p_org_seq_num ;
SELECT 'Y'
FROM igs_or_org_note_type
WHERE org_notes_type = p_org_note_type
AND inst_flag=cp_inst_flag;
PROCEDURE update_int_notes(p_int_notes_rec igs_or_inst_nts_int%ROWTYPE,
p_err_cd igs_or_inst_nts_int.error_code%type)
AS
BEGIN
--Since there is no Table Handler Direct Update on Table
UPDATE igs_or_inst_nts_int
SET status = p_int_notes_rec.status ,
error_code = p_err_cd
WHERE interface_inst_notes_id = p_int_notes_rec.interface_inst_notes_id;
END update_int_notes;
l_insert_success:=FALSE;
l_update_success:=FALSE;
l_dml_operation:='INSERT';
igs_or_org_notes_pkg.insert_row(
x_rowid => l_row_id,
x_org_structure_id => LTRIM(RTRIM(l_party_number)),
x_org_structure_type => 'INSTITUTE',
x_org_note_sequence => l_org_note_seq,
x_org_note_type => int_notes_rec.org_note_type,
x_start_date => int_notes_rec.start_date,
x_end_date => int_notes_rec.end_date,
x_note_text => int_notes_rec.note_text,
x_mode => 'R'
);
l_insert_success:=TRUE;
update_int_notes(int_notes_rec,l_val_fail_err_code);
l_dml_operation:='UPDATE';
igs_or_org_notes_pkg.update_row(
x_rowid =>oss_notes_rec.rowid,
x_org_structure_id =>oss_notes_rec.org_structure_id,
x_org_structure_type =>'INSTITUTE',
x_org_note_sequence =>oss_notes_rec.org_note_sequence,
x_org_note_type =>oss_notes_rec.org_note_type,
x_start_date =>NVL(int_notes_rec.start_date,oss_notes_rec.start_date),
x_end_date =>NVL(int_notes_rec.end_date,oss_notes_rec.end_date),
x_note_text =>NVL(int_notes_rec.note_text,oss_notes_rec.note_text),
x_mode =>'R'
);
l_update_success:=TRUE;
update_int_notes(int_notes_rec,l_val_fail_err_code);
update_int_notes(int_notes_rec,NULL);
IF l_dml_operation='INSERT' THEN
IF l_val_fail_err_code IS NULL THEN
l_err_cd:='E019';
ELSIF l_dml_operation='UPDATE' THEN
IF l_val_fail_err_code IS NULL THEN
l_err_cd:='E020';
update_int_notes(int_notes_rec, l_err_cd);
vskumar 31-May-2006 Xbuild3 performance related fix. changed cursor select stmt cur_get_oss_contacts.
***************************************************************/
AS
l_contact_id hz_parties.party_id%TYPE;
l_last_update_date DATE;
l_party_last_update_date DATE;
l_org_cont_last_update_date DATE;
l_lst_update_date DATE;
l_cont_point_last_update_date DATE;
l_prel_last_update_date DATE;
l_rel_party_last_update_date DATE;
SELECT *
FROM igs_or_inst_con_int
WHERE interface_id = cp_interface_id
AND status = cp_status;
SELECT org_conts.attribute_category, org_conts.attribute10, org_conts.attribute11, org_conts.attribute12,
org_conts.attribute13, org_conts.attribute14, org_conts.attribute15, org_conts.attribute16,
org_conts.attribute17, org_conts.attribute18, org_conts.attribute19, org_conts.attribute20,
org_conts.attribute1, org_conts.attribute2, org_conts.attribute21, org_conts.attribute22,
org_conts.attribute23, org_conts.attribute24, org_conts.attribute3, org_conts.attribute4,
org_conts.attribute5, org_conts.attribute6, org_conts.attribute7, org_conts.attribute8,
org_conts.attribute9, org_conts.contact_number, TO_NUMBER(NULL) contact_id, org_conts.mail_stop,
org_conts.OBJECT_VERSION_NUMBER org_cont_ovn, org_conts.org_contact_id, TO_NUMBER(NULL) org_role_ovn,
org_conts.title, rel.last_update_login, rel.last_updated_by, rel.OBJECT_VERSION_NUMBER rel_ovn,
rel.PARTY_ID rel_party_id, rel.relationship_id, rel.status, rel.subject_id contact_party_id,
hz.OBJECT_VERSION_NUMBER rel_party_ovn
FROM
HZ_ORG_CONTACTS org_conts,
HZ_RELATIONSHIPS rel,
HZ_PARTIES hz
WHERE
org_conts.PARTY_RELATIONSHIP_ID = REL.RELATIONSHIP_ID
AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND REL.DIRECTIONAL_FLAG = 'F'
AND REL.RELATIONSHIP_TYPE ='CONTACT'
AND REL.OBJECT_ID = hz.PARTY_ID
AND REL.SUBJECT_ID = p_contact_party_id
AND REL.OBJECT_ID = cp_party_id;
SELECT email_address, contact_point_id, object_version_number
FROM hz_contact_points
WHERE owner_table_id = cp_rel_party_id
AND contact_point_type = cp_contact_point_type
AND owner_table_name = cp_owner_table_name;
SELECT 'X'
FROM fnd_lookup_values
WHERE lookup_type = cp_lookup_type
AND lookup_code = p_title
AND enabled_flag = cp_enabled_flag
AND view_application_id = 222
AND security_group_id = 0
AND language = userenv('LANG');
SELECT 'X'
FROM igs_pe_person_base_v
WHERE person_id = cp_person_id;
PROCEDURE update_int_contact(p_status igs_or_inst_con_int.status%TYPE,
p_interface_contacts_id igs_or_inst_con_int.interface_contacts_id%TYPE,
p_contact_party_id igs_or_inst_con_int.contact_party_id%TYPE,
p_err_code igs_or_inst_con_int.error_code%TYPE,
p_err_text igs_or_inst_con_int.error_text%TYPE
)
AS
BEGIN
--Since there is no TBH direct Updation on Table
UPDATE igs_or_inst_con_int
SET status = p_status ,
error_code = p_err_code,
error_text = p_err_text
WHERE interface_contacts_id =p_interface_contacts_id;
'igs.plsql.igs_or_inst_imp_003.update_int_contact.' || p_err_code,
FND_MESSAGE.GET,
NULL,NULL,NULL,NULL,NULL, IGS_OR_INST_IMP_001.G_REQUEST_ID);
END update_int_contact;
SELECT person_type_code
FROM igs_pe_typ_instances_all
WHERE person_id = cp_contact_party_id AND
SYSDATE BETWEEN start_date AND NVL(end_date,SYSDATE);
igs_pe_typ_instances_pkg.insert_row
(
x_rowid => l_type_rowid,
x_person_id => p_contact_party_id,
x_course_cd => NULL,
x_type_instance_id => l_instance_id,
x_person_type_code => p_person_type,
x_cc_version_number => NULL,
x_funnel_status => NULL,
x_admission_appl_number => NULL,
x_nominated_course_cd => NULL,
x_ncc_version_number => NULL,
x_sequence_number => NULL,
x_start_date => TRUNC(SYSDATE),
x_end_date => NULL,
x_create_method => 'CREATE_IMPORT',
x_ended_by => NULL,
x_end_method => NULL,
x_mode => 'R',
x_org_id => l_org_id,
x_emplmnt_category_code => NULL
);
update_int_contact(p_status,p_interface_contacts_id,p_contact_party_id,p_error_code,NULL);
l_insert_success BOOLEAN;
l_update_success BOOLEAN;
l_last_update_date DATE;
SELECT *
FROM igs_or_inst_cphn_int
WHERE interface_cont_id =cp_interface_cont_id
AND status =cp_status;
SELECT 'X'
FROM fnd_lookup_values
WHERE lookup_type = cp_lookup_type
AND lookup_code = p_type
AND enabled_flag = cp_enabled_flag
AND view_application_id = 222
AND security_group_id = 0
AND language = userenv('LANG');
SELECT phone_country_code
FROM fnd_territories_vl ter, hz_phone_country_codes hzc
WHERE ter.territory_code = hzc.territory_code
AND hzc.phone_country_code = p_country_code ;
PROCEDURE update_int_phones(p_int_phones_rec igs_or_inst_cphn_int%ROWTYPE,
p_err_cd igs_or_inst_cphn_int.error_code%TYPE,
p_err_text igs_or_inst_cphn_int.error_text%TYPE
)
AS
BEGIN
--Since there is no Table Handler Direct Update on Table
UPDATE igs_or_inst_cphn_int
SET status = p_int_phones_rec.status,
error_code = p_err_cd,
error_text = p_err_text
WHERE interface_inst_cont_phone_id = p_int_phones_rec.interface_inst_cont_phone_id;
'igs.plsql.igs_or_inst_imp_003.update_int_phones.' || p_err_cd,
FND_MESSAGE.GET,
NULL,NULL,NULL,NULL,NULL, IGS_OR_INST_IMP_001.G_REQUEST_ID);
END update_int_phones;
l_insert_success := FALSE;
l_update_success := FALSE;
l_last_update_date := NULL;
update_int_phones(int_phones_rec,l_val_fail_err_cd,NULL);
l_dml_operation:='INSERT';
igs_or_phones_pkg.insert_row(
X_Phone_Id => l_phone_id,
X_Last_Update_Date => NULL,
X_Last_Updated_By => NULL,
X_Creation_Date => NULL,
X_Created_By => NULL,
X_phone_number => int_phones_rec.Phone_Number,
X_status => 'A',
X_phone_type => int_phones_rec.TYPE,
X_Last_Update_Login => NULL,
X_Country_code => int_phones_rec.country_Code,
X_Area_Code => int_phones_rec.area_code,
X_Extension => int_phones_rec.extension,
X_Primary_Flag => 'N',
X_Orig_System_Reference => l_orig_sys_ref,
X_Attribute_Category => int_phones_rec.attribute_category,
X_Attribute1 => int_phones_rec.attribute1,
X_Attribute2 => int_phones_rec.attribute2,
X_Attribute3 => int_phones_rec.attribute3,
X_Attribute4 => int_phones_rec.attribute4,
X_Attribute5 => int_phones_rec.attribute5,
X_Attribute6 => int_phones_rec.attribute6,
X_Attribute7 => int_phones_rec.attribute7,
X_Attribute8 => int_phones_rec.attribute8,
X_Attribute9 => int_phones_rec.attribute9,
X_Attribute10 => int_phones_rec.attribute10,
X_Attribute11 => int_phones_rec.attribute11,
X_Attribute12 => int_phones_rec.attribute12,
X_Attribute13 => int_phones_rec.attribute13,
X_Attribute14 => int_phones_rec.attribute14,
X_Attribute15 => int_phones_rec.attribute15,
X_Attribute16 => int_phones_rec.attribute16,
X_Attribute17 => int_phones_rec.attribute17,
X_Attribute18 => int_phones_rec.attribute18,
X_Attribute19 => int_phones_rec.attribute19,
X_Attribute20 => int_phones_rec.attribute20,
x_party_id => p_rel_party_id ,
x_party_site_id => NULL,
x_msg_count => l_msg_cnt,
x_msg_data => l_msg_dt,
x_return_status => l_ret_status,
x_contact_point_ovn => l_ovn
);
update_int_phones(int_phones_rec,l_val_fail_err_cd,NULL);
FND_MESSAGE.SET_NAME('IGS','IGS_OR_PHONEINSERT_ERROR');
l_insert_success:=TRUE;
update_int_phones(int_phones_rec,l_val_fail_err_cd,NULL);
update_int_phones(int_phones_rec,NULL,NULL);
IF l_dml_operation='INSERT' THEN
int_phones_rec.status :='3';
update_int_phones(int_phones_rec,'E047',NULL);
ELSIF l_dml_operation='UPDATE' THEN
int_phones_rec.status :='3';
update_int_phones(int_phones_rec,'E048',NULL);
update_int_contact(int_cont_rec.status,int_cont_rec.interface_contacts_id,int_cont_rec.contact_party_id,l_val_fail_err_cd,NULL);
update_int_contact(int_cont_rec.status,int_cont_rec.interface_contacts_id,int_cont_rec.contact_party_id,l_val_fail_err_cd,NULL);
l_cont_dml_operation :='UPDATE';
igs_or_contacts_pkg.update_row(
x_last_name => NULL,
x_last_updated_by => oss_cont_rec.last_updated_by,
x_last_update_date => l_lst_update_date,
x_party_last_update_date => l_party_last_update_date,
x_org_cont_last_update_date => l_org_cont_last_update_date,
x_cont_point_last_update_date => l_cont_point_last_update_date,
x_prel_last_update_date => l_prel_last_update_date,
x_rel_party_last_update_date => l_rel_party_last_update_date,
x_status => oss_cont_rec.status,
x_contact_key => NULL,
x_first_name => NULL,
x_job_title => NULL,
x_last_update_login => oss_cont_rec.last_update_login,
x_mail_stop => NVL( int_cont_rec.mail_stop, oss_cont_rec.mail_stop),
x_title => NVL(int_cont_rec.title, oss_cont_rec.title),
x_attribute_category => NVL( int_cont_rec.attribute_category,oss_cont_rec.attribute_category),
x_attribute1 => NVL (int_cont_rec.attribute1,oss_cont_rec.attribute1),
x_attribute2 => NVL (int_cont_rec.attribute2,oss_cont_rec.attribute2),
x_attribute3 => NVL (int_cont_rec.attribute3,oss_cont_rec.attribute3),
x_attribute4 => NVL (int_cont_rec.attribute4,oss_cont_rec.attribute4),
x_attribute5 => NVL (int_cont_rec.attribute5,oss_cont_rec.attribute5),
x_attribute6 => NVL (int_cont_rec.attribute6,oss_cont_rec.attribute6),
x_attribute7 => NVL (int_cont_rec.attribute7,oss_cont_rec.attribute7),
x_attribute8 => NVL (int_cont_rec.attribute8,oss_cont_rec.attribute8),
x_attribute9 => NVL (int_cont_rec.attribute9,oss_cont_rec.attribute9),
x_attribute10 => NVL (int_cont_rec.attribute10,oss_cont_rec.attribute10),
x_attribute11 => NVL (int_cont_rec.attribute11,oss_cont_rec.attribute11),
x_attribute12 => NVL (int_cont_rec.attribute12,oss_cont_rec.attribute12),
x_attribute13 => NVL (int_cont_rec.attribute13,oss_cont_rec.attribute13),
x_attribute14 => NVL (int_cont_rec.attribute14,oss_cont_rec.attribute14),
x_attribute15 => NVL (int_cont_rec.attribute15,oss_cont_rec.attribute15),
x_attribute16 => NVL (int_cont_rec.attribute16,oss_cont_rec.attribute16),
x_attribute17 => NVL (int_cont_rec.attribute17,oss_cont_rec.attribute17),
x_attribute18 => NVL (int_cont_rec.attribute18,oss_cont_rec.attribute18),
x_attribute19 => NVL (int_cont_rec.attribute19,oss_cont_rec.attribute19),
x_attribute20 => NVL (int_cont_rec.attribute20,oss_cont_rec.attribute20),
x_attribute21 => oss_cont_rec.attribute21, --There are only 20 Attributes in Interface
x_attribute22 => oss_cont_rec.attribute22,
x_attribute23 => oss_cont_rec.attribute23,
x_attribute24 => oss_cont_rec.attribute24,
x_attribute25 => NULL,
x_email_address => NVL(int_cont_rec.email,l_email_address),
x_last_name_alt => NULL,
x_first_name_alt => NULL ,
x_contact_number => oss_cont_rec.contact_number,
x_party_id => p_party_id,
x_party_site_id => NULL ,
x_contact_party_id => oss_cont_rec.contact_party_id ,
x_org_contact_id => oss_cont_rec.org_contact_id,
x_contact_point_id => l_contact_point_id,
x_org_contact_role_id => l_org_contact_role_id,
x_party_relationship_id => oss_cont_rec.relationship_id ,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_rel_party_id => oss_cont_rec.rel_party_id,
P_ORG_ROLE_OVN => l_org_role_ovn,
P_REL_OVN => l_rel_ovn,
P_REL_PARTY_OVN => l_rel_party_ovn,
P_ORG_CONT_OVN => l_org_cont_ovn,
P_CONTACT_POINT_OVN => l_contact_point_ovn
);
Update_int_contact(int_cont_rec.status,int_cont_rec.interface_contacts_id,int_cont_rec.contact_party_id,l_val_fail_err_cd,NULL);
FND_MESSAGE.SET_NAME('IGS','IGS_OR_CONTUPDATE_ERROR');
update_int_contact(int_cont_rec.status,int_cont_rec.interface_contacts_id,int_cont_rec.contact_party_id,l_val_fail_err_cd,NULL);
l_cont_dml_operation := 'INSERT';
igs_or_contacts_pkg.insert_row
(
x_last_name => NULL,
x_orig_system_reference => int_cont_rec.reference,
x_status => 'A',
x_contact_key => NULL,
x_first_name => NULL,
x_job_title => NULL,
x_mail_stop => int_cont_rec.mail_stop,
x_title => int_cont_rec.title,
x_attribute_category => int_cont_rec.attribute_category,
x_attribute1 => int_cont_rec.attribute1,
x_attribute2 => int_cont_rec.attribute2,
x_attribute3 => int_cont_rec.attribute3,
x_attribute4 => int_cont_rec.attribute4,
x_attribute5 => int_cont_rec.attribute5,
x_attribute6 => int_cont_rec.attribute6,
x_attribute7 => int_cont_rec.attribute7,
x_attribute8 => int_cont_rec.attribute8,
x_attribute9 => int_cont_rec.attribute9,
x_attribute10 => int_cont_rec.attribute10,
x_attribute11 => int_cont_rec.attribute11,
x_attribute12 => int_cont_rec.attribute12,
x_attribute13 => int_cont_rec.attribute13,
x_attribute14 => int_cont_rec.attribute14,
x_attribute15 => int_cont_rec.attribute15,
x_attribute16 => int_cont_rec.attribute16,
x_attribute17 => int_cont_rec.attribute17,
x_attribute18 => int_cont_rec.attribute18,
x_attribute19 => int_cont_rec.attribute19,
x_attribute20 => int_cont_rec.attribute20,
x_attribute21 => NULL,
x_attribute22 => NULL,
x_attribute23 => NULL,
x_attribute24 => NULL,
x_attribute25 => NULL,
x_email_address => int_cont_rec.email,
x_last_name_alt => NULL,
x_first_name_alt => NULL ,
x_contact_number => l_contact_number,
x_party_id => p_party_id ,
x_party_site_id => NULL ,
x_contact_party_id => int_cont_rec.contact_party_id ,
x_org_contact_id => l_org_contact_id,
x_contact_point_id => l_contact_point_id,
x_org_contact_role_id => l_org_contact_role_id,
x_rel_party_id => l_rel_party_id,
x_created_by => NULL,
x_creation_date => NULL,
x_updated_by => NULL,
x_update_date => NULL,
x_last_update_login => NULL,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
P_ORG_ROLE_OVN => l_org_role_ovn,
P_REL_OVN => l_rel_ovn,
P_REL_PARTY_OVN => l_rel_party_ovn,
P_ORG_CONT_OVN => l_org_cont_ovn,
P_CONTACT_POINT_OVN => l_contact_point_ovn
);
Update_int_contact(int_cont_rec.status,int_cont_rec.interface_contacts_id,int_cont_rec.contact_party_id,l_val_fail_err_cd,NULL);
FND_MESSAGE.SET_NAME('IGS','IGS_OR_CONTINSERT_ERROR');
update_int_contact('1',int_cont_rec.interface_contacts_id,int_cont_rec.contact_party_id,NULL,NULL);
IF l_cont_dml_operation='UPDATE' THEN
update_int_contact('3',int_cont_rec.interface_contacts_id,int_cont_rec.contact_party_id,'E025',NULL);
IF l_cont_dml_operation='INSERT' THEN
update_int_contact('3',int_cont_rec.interface_contacts_id,int_cont_rec.contact_party_id,'E025',NULL);
l_mast_insert_success BOOLEAN;
l_det_insert_success BOOLEAN;
l_det_update_success BOOLEAN;
SELECT *
FROM igs_or_inst_stat_int
WHERE interface_id =cp_interface_id
AND status =cp_status ;
SELECT sdtl.rowid,sdtl.*
FROM igs_or_inst_sdtl_int sdtl
WHERE interface_inst_stat_id = p_int_stat_id
AND status = cp_status;
SELECT *
FROM igs_or_inst_stats
WHERE stat_type_cd =p_stat_type_cd
AND party_id =cp_party_id;
SELECT sdtl.rowid,sdtl.*
FROM igs_or_inst_stat_dtl sdtl
WHERE sdtl.inst_stat_id = p_int_stat_id
AND TO_CHAR(sdtl.year,'YYYY') = TO_CHAR(p_year,'YYYY');
SELECT 'X'
FROM igs_lookup_values lkv
WHERE lkv.lookup_code = p_stat_type_cd
AND lkv.lookup_type= cp_lookup_type
AND enabled_flag = cp_enabled_flag;
PROCEDURE update_int_stat(p_int_stat_rec igs_or_inst_stat_int%ROWTYPE)
AS
BEGIN
UPDATE igs_or_inst_stat_int
SET status = p_int_stat_rec.status,
error_code =p_int_stat_rec.error_code
WHERE interface_inst_stat_id =p_int_stat_rec.interface_inst_stat_id;
'igs.plsql.igs_or_inst_imp_003.update_int_stat.' || p_int_stat_rec.error_code,
FND_MESSAGE.GET,
NULL,NULL,NULL,NULL,NULL, IGS_OR_INST_IMP_001.G_REQUEST_ID);
END update_int_stat;
PROCEDURE update_int_stat_det(p_int_stat_det_rec cur_get_int_stat_det%ROWTYPE)
AS
BEGIN
UPDATE igs_or_inst_sdtl_int
SET status = p_int_stat_det_rec.status,
error_code = p_int_stat_det_rec.error_code
WHERE interface_inst_stat_dtl_id = p_int_stat_det_rec.interface_inst_stat_dtl_id;
'igs.plsql.igs_or_inst_imp_003.update_int_stat_det.' || p_int_stat_det_rec.error_code,
FND_MESSAGE.GET,
NULL,NULL,NULL,NULL,NULL, IGS_OR_INST_IMP_001.G_REQUEST_ID);
END update_int_stat_det;
IF l_det_dml_operation='INSERT' THEN
p_int_stat_det_rec.error_code := 'E030';
ELSIF l_det_dml_operation='UPDATE' THEN
p_int_stat_det_rec.error_code := 'E031';
update_int_stat_det(p_int_stat_det_rec);
update_int_stat(p_int_stat_master_rec);
l_mast_insert_success:=FALSE;
l_det_insert_success:=FALSE;
l_det_update_success:=FALSE;
l_mast_dml_operation:='INSERT';
igs_or_inst_stats_pkg.insert_row(
x_rowid =>l_row_id,
x_inst_stat_id =>l_inst_stat_id,
x_stat_type_cd =>int_stat_master_rec.stat_type_cd,
x_party_id =>p_party_id,
x_mode =>'R'
);
l_mast_insert_success:=TRUE;
update_int_stat(int_stat_master_rec);
l_det_insert_success:=FALSE;
l_det_dml_operation:='INSERT';
igs_or_inst_stat_dtl_pkg.insert_row(
x_rowid => l_det_row_id,
x_inst_stat_dtl_id => l_dtl_id,
x_inst_stat_id => l_inst_stat_id,
x_year => int_stat_det_rec.year ,
x_value => int_stat_det_rec.value,
x_mode => 'R' );
l_det_insert_success:=TRUE;
update_int_stat_det(int_stat_det_rec);
l_det_insert_success:=FALSE;
l_det_dml_operation:='INSERT';
igs_or_inst_stat_dtl_pkg.insert_row(
x_rowid => l_det_row_id,
x_inst_stat_dtl_id => l_dtl_id,
x_inst_stat_id => oss_stat_master_rec.inst_stat_id,
x_year => int_stat_det_rec.year ,
x_value => int_stat_det_rec.value
);
l_det_insert_success:=TRUE;
l_det_dml_operation:='UPDATE';
igs_or_inst_stat_dtl_pkg.update_row(
x_rowid => oss_stat_det_rec.rowid,
x_inst_stat_dtl_id => oss_stat_det_rec.inst_stat_dtl_id,
x_inst_stat_id => oss_stat_det_rec.inst_stat_id,
x_year => oss_stat_det_rec.year ,
x_value => int_stat_det_rec.value
);
l_det_update_success:=TRUE;
update_int_stat_det(int_stat_det_rec);
update_int_stat(int_stat_master_rec);
update_int_stat(int_stat_master_rec);
asbala 12-nov-03 3227107: address changes - signature of igs_pe_person_addr_pkg.insert_row changed
***************************************************************/
AS
l_addr_usage igs_or_adrusge_int.site_use_code%TYPE;
SELECT *
FROM igs_or_adr_int
WHERE interface_id =cp_interface_id
AND status = cp_status ;
SELECT COUNT(interface_addrusage_id)
FROM igs_or_adrusge_int
WHERE interface_addr_id = p_int_addr_id
AND status = cp_status;
SELECT UPPER(site_use_code)
FROM igs_or_adrusge_int
WHERE interface_addr_id = p_int_addr_id
AND status = cp_status;
SELECT party_site_id
FROM hz_party_sites hp,
hz_locations addr
WHERE hp.location_id = addr.location_id
AND hp.party_id = cp_party_id
AND NVL(addr.address1,' ') = NVL(p_int_addr_rec.addr_line_1,' ')
AND NVL(addr.address2,' ') = NVL(p_int_addr_rec.addr_line_2,' ')
AND NVL(addr.address3,' ') = NVL(p_int_addr_rec.addr_line_3,' ')
AND NVL(addr.address4,' ') = NVL(p_int_addr_rec.addr_line_4,' ')
AND NVL(addr.city,' ') = NVL(p_int_addr_rec.city,' ')
AND NVL(addr.state,' ') = NVL(p_int_addr_rec.state,' ')
AND NVL(addr.province,' ') = NVL(p_int_addr_rec.province,' ')
AND NVL(addr.county,' ') = NVL(p_int_addr_rec.county,' ')
AND NVL(addr.country,' ') = NVL(p_int_addr_rec.country,' ')
AND NVL(addr.postal_code,' ') = NVL(p_int_addr_rec.postcode,' ');
SELECT site_use_type
FROM hz_party_site_uses
WHERE party_site_id = cp_party_site_id
AND site_use_type = cp_addr_usage;
SELECT 'X'
FROM fnd_territories
WHERE territory_code= p_country;
PROCEDURE update_address_int(p_interface_id igs_or_adr_int.interface_id%TYPE,
p_addr_id igs_or_adr_int.interface_addr_id%TYPE,
p_status igs_or_adr_int.status%TYPE,
p_err_cd igs_or_adr_int.error_code%TYPE,
p_err_txt igs_or_adr_int.error_text%TYPE,
p_which_tab VARCHAR2)
AS
BEGIN
--Update the status of address record
IF p_which_tab IN ('adr','both')THEN
UPDATE igs_or_adr_int
SET status = p_status ,
error_code = p_err_cd ,
error_text = p_err_txt
WHERE interface_id = p_interface_id
AND interface_addr_id= p_addr_id ;
'igs.plsql.igs_or_inst_imp_003.update_address_int.' || p_err_cd,
FND_MESSAGE.GET,
NULL,NULL,NULL,NULL,NULL, IGS_OR_INST_IMP_001.G_REQUEST_ID);
UPDATE igs_or_adrusge_int
SET status = p_status , error_code = p_err_cd , error_text = p_err_txt
WHERE interface_addr_id= p_addr_id;
'igs.plsql.igs_or_inst_imp_003.update_address_int.' || p_err_cd,
FND_MESSAGE.GET,
NULL,NULL,NULL,NULL,NULL, IGS_OR_INST_IMP_001.G_REQUEST_ID);
END update_address_int;
PROCEDURE insert_addr_usage(p_interface_id igs_or_adr_int.interface_id%TYPE,
p_int_addr_id igs_or_adr_int.interface_addr_id%TYPE)
AS
--cursor added to validate if the site use code in interface table is present in fnd_lookups
CURSOR c_val_addr_usage(p_site_use_code igs_or_adrusge_int.site_use_code%TYPE,
cp_lookup_typ fnd_lookup_values.lookup_type%TYPE,
cp_enabled_flag fnd_lookup_values.enabled_flag%TYPE) IS
SELECT 'X'
FROM fnd_lookup_values
WHERE lookup_type= cp_lookup_typ
AND lookup_code = p_site_use_code
AND enabled_flag = cp_enabled_flag
AND view_application_id = 222
AND security_group_id = 0
AND language = userenv('LANG');
p_action => 'INSERT',
p_rowid => l_usage_row_id,
p_party_site_use_id => l_party_site_use_id,
p_party_site_id => l_party_site_id,
p_site_use_type => l_addr_usage,
p_status => 'A',
p_return_status => l_return_status,
p_msg_data => l_msg_data,
p_last_update_date => l_date,
p_site_use_last_update_date => l_date,
p_profile_last_update_date => l_date,
p_hz_party_site_use_ovn => l_object_version_number
);
FND_MESSAGE.SET_NAME('IGS','IGS_OR_USG_INSERT');
'igs.plsql.igs_or_inst_imp_003.insert_addr_usage.fail',
FND_MESSAGE.GET || '-' || l_msg_data,
NULL,NULL,NULL,NULL,NULL, IGS_OR_INST_IMP_001.G_REQUEST_ID);
update_address_int(p_interface_id,p_int_addr_id,'3', 'E057',l_msg_data,'both');
update_address_int(p_interface_id,p_int_addr_id,'3','E033',NULL,'both');
END insert_addr_usage;
update_address_int(p_interface_id,int_addr_rec.interface_addr_id,'3','E034',NULL,'both');
update_address_int(p_interface_id,int_addr_rec.interface_addr_id,'3','E035',NULL,'both');
l_addr_dml := 'INSERT';
igs_pe_person_addr_pkg.insert_row
(
p_action => 'R',
p_rowid => l_addr_row_id,
p_location_id => l_location_id,
p_start_dt => int_addr_rec.start_date,
p_end_dt => int_addr_rec.end_date,
p_country => int_addr_rec.country,
p_address_style => NULL,
p_addr_line_1 => int_addr_rec.addr_line_1 ,
p_addr_line_2 => int_addr_rec.addr_line_2,
p_addr_line_3 => int_addr_rec.addr_line_3 ,
p_addr_line_4 => int_addr_rec.addr_line_4 ,
p_date_last_verified => int_addr_rec.date_last_verified,
p_correspondence => int_addr_rec.correspondence_flag ,
p_city => int_addr_rec.city,
p_state => int_addr_rec.state,
p_province => int_addr_rec.province,
p_county => int_addr_rec.county ,
p_postal_code => int_addr_rec.postcode,
p_address_lines_phonetic => NULL,
p_delivery_point_code => int_addr_rec.delivery_point_code,
p_other_details_1 => int_addr_rec.other_details_1,
p_other_details_2 => int_addr_rec.other_details_2,
p_other_details_3 => int_addr_rec.other_details_3,
l_return_status => l_return_status,
l_msg_data => l_msg_data,
p_party_id => p_party_id,
p_party_site_id => l_party_site_id,
p_party_type => 'ORGANIZATION',
p_last_update_date => l_date,
p_party_site_ovn => l_party_site_ovn,
p_location_ovn => l_location_ovn,
p_status => 'A'
);
FND_MESSAGE.SET_NAME('IGS','IGS_OR_ADDR_NO_INSERT');
update_address_int(p_interface_id,int_addr_rec.interface_addr_id,'3','E036',l_msg_data,'adr');
update_address_int(p_interface_id,int_addr_rec.interface_addr_id,'4','E022',l_msg_data,'adr');
--Set Insert address as TRUE if inserted
l_addr_success:=TRUE;
l_usage_dml:='INSERT';
insert_addr_usage(int_addr_rec.interface_id,int_addr_rec.interface_addr_id);
update_address_int(p_interface_id,int_addr_rec.interface_addr_id,'3','E032',NULL,'adr');
l_usage_dml:='INSERT';
insert_addr_usage(int_addr_rec.interface_id,int_addr_rec.interface_addr_id);
update_address_int(p_interface_id,int_addr_rec.interface_addr_id,'1',NULL,NULL,'both');
IF l_addr_dml='INSERT' AND NOT l_addr_success THEN
--Error has occurred while inserting address
update_address_int(p_interface_id,int_addr_rec.interface_addr_id,'3','E036',NULL,'adr');
IF l_usage_dml='INSERT' AND NOT l_usage_success THEN
--Error has occurred while inserting address usage
update_address_int(p_interface_id,int_addr_rec.interface_addr_id,'3','E038',NULL,'adr_usg');