[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| KUMMA 15-jul-2002 Bug 2446067: in call to CREATE_ORGANIZATION and UPDATE_ORGANIZATION ,
| converted the NEW_INSTITUTION_CD to upper.
| In call to IGS_PE_HZ_PARTIES_PKG.INSERT_ROW if the local_ind and os_ind are null
| then put the default value of 'N'. In call to IGS_PE_HZ_PARTIES_PKG.UPDATE_ROW if
| local_ind and os_ind are null then substitute them with database older values
| OI_LOCAL_INSTITUTION_IND and OI_OS_IND
| pkpatel 31-JUL-2002 Bug No: 2461744
| Removed the UPPER check for INSTITUTION_CD
| pkpatel 25-OCT-2002 Bug No: 2613704
| Replaced column inst_priority_code_id with inst_priority_cd in igs_pe_hz_parties_pkg
| npalanis 27-OCT-2002 Bug No: 2613704
| Modified create_alternate_id
| pkpatel 2-DEC-2002 Bug No: 2599109
| Added column birth_city, birth_country in the call to TBH igs_pe_hz_parties_pkg
| ssawhney 30-APR-2003 V2API - OVN implementation , create/update_institution procs modified
| vrathi 28-MAY-2003 Bug No: 2961982 Replaced update_row with add_row
| ssaleem 25-SEP-2003 IGS.L patch the following changes are made
| 1. Logging mechanism introduced, FND_FILE.PUT_LINE replaced with methods
| in FND_LOG package
| 2. Cursors that used variables in the SELECT statements were replaced with
| cursor parameters. Respective changes have been made in the places where
| the cursors are opened
| 3. In the import process, it is made sure that NULL values does not replace
| existing values in the table. NULL check has been added while calling
| IGS_OR_GEN_012_PKG.UPDATE_ORGANIZATION and igs_pe_hz_parties_pkg.ADD_row
| mmkumar 18-Jul-2005 modified calls to igs_pe_hz_parties insert_row and add row
*/
PROCEDURE create_institution (
p_inst_rec IN IGS_OR_INST_INT%ROWTYPE,
p_instcode OUT NOCOPY VARCHAR2,
p_errind OUT NOCOPY VARCHAR2,
p_error_code OUT NOCOPY VARCHAR2,
p_error_text OUT NOCOPY VARCHAR2)
AS
/*************************************************************
Created By :samaresh
Date Created By : 17-JUL-2001
Purpose : This Procedure creates a New Institution
Know limitations, enhancements or remarks
Change History
Who When What
SMVK 05-Feb-2002 Added Fund Authorization to
IGS_PE_HZ_PARTIES_PKG.INSERT_ROW calls
as per enhancement bug no.2191470.
kumma 14-JUN-2002 Uncommented the call to message IGS_OR_INST_CRT_FAIL,
IGS_OR_AUTOGEN_FAIL, 2410165
kumma 26-JUN-2002 Passed NULL for values INSITUTION_CD AND OU_START_DT
Inside call to IGS_PE_HZ_PARTIES_PKG.INSERT_ROW AND UPDATE_ROW, Bug 2425349
kumma 28-jun-2002 set the error indicator to 'Y' inside exception handling code
kumma 15-JUL-2002 In call to CREATE_ORGANIZATION converted the NEW_INSTITUTION_CD to upper.
In call to IGS_PE_HZ_PARTIES_PKG.INSERT_ROW if the local_ind and os_ind are null
then put the default value of 'N'. Bug 2446067
pkpatel 31-JUL-2002 Bug No: 2461744
Removed the UPPER check for INSTITUTION_CD
pkpatel 25-OCT-2002 Bug No: 2613704
Replaced column inst_priority_code_id with inst_priority_cd in igs_pe_hz_parties_pkg
ssawhney 30-APR-2003 V2API - OVN implementation.
skpandey 27-SEP-2005 Bug: 3663505
Description: Added ATTRIBUTES 21 TO 24 to store additional information in IGS_OR_GEN_012_PKG call
***************************************************************/
l_return_status VARCHAR2(1);
SELECT party_number
FROM hz_parties
WHERE party_id = cp_party_id;
IGS_PE_HZ_PARTIES_PKG.INSERT_ROW (
x_rowid => l_rowid,
x_mode => 'R',
x_party_id => l_party_id,
x_deceased_ind => NULL,
x_archive_exclusion_ind => NULL,
x_archive_dt => NULL,
x_purge_exclusion_ind => NULL,
x_purge_dt => NULL,
x_oracle_username => NULL,
x_proof_of_ins => NULL,
x_proof_of_immu => NULL,
x_level_of_qual => NULL,
x_military_service_reg => NULL,
x_veteran => NULL,
x_institution_cd => NULL,
x_oi_local_institution_ind => NVL(p_inst_rec.LOCAL_INSTITUTION_IND,'N'),
x_oi_os_ind => NVL(p_inst_rec.OS_IND,'N'),
x_oi_govt_institution_cd => p_inst_rec.GOVT_INSTITUTION_CD,
x_oi_inst_control_type => p_inst_rec.INST_CONTROL_TYPE,
x_oi_institution_type => p_inst_rec.INSTITUTION_TYPE,
x_oi_institution_status => p_inst_rec.INSTITUTION_STATUS,
x_ou_start_dt => NULL,
x_ou_end_dt => NULL,
x_ou_member_type => NULL,
x_ou_org_status => NULL,
x_ou_org_type => NULL,
x_inst_org_ind => 'I',
x_inst_priority_cd => p_inst_rec.INST_PRIORITY_CD,
x_inst_eps_code => p_inst_rec.EPS_CODE,
x_inst_phone_country_code => p_inst_rec.PHONE_COUNTRY,
x_inst_phone_area_code => p_inst_rec.PHONE_AREA,
x_inst_phone_number => p_inst_rec.PHONE_NUMBER,
x_adv_studies_classes => p_inst_rec.ADV_STUDIES_CLASSES,
x_honors_classes => p_inst_rec.HONORS_CLASSES,
x_class_size => p_inst_rec.CLASS_SIZE,
x_sec_school_location_id => p_inst_rec.SEC_SCHOOL_LOCATION_ID,
x_percent_plan_higher_edu => p_inst_rec.PERCENT_PLAN_HIGHER_EDU,
x_fund_authorization => NULL,
x_birth_city => NULL,
x_birth_country => NULL,
x_oss_org_unit_cd => p_instcode --mmkumar, party number change
);
IGS_OR_CWLK_PKG.INSERT_ROW (
x_rowid => l_rowid,
x_crosswalk_id => l_crswalk_id,
x_institution_code => p_inst_code,
x_institution_name => p_inst_name
);
SELECT 'Y'
FROM IGS_OR_CWLK_DTL
WHERE ALT_ID_TYPE = cp_data_src AND
ALT_ID_VALUE = cp_data_val;
IGS_OR_CWLK_DTL_PKG.INSERT_ROW (
x_rowid => l_rowid,
x_crosswalk_dtl_id => l_crswalkdtl_id,
x_crosswalk_id => p_crwlkid,
x_alt_id_type => p_datasrc,
x_alt_id_value => p_dataval,
x_mode => 'R');
Modified the logic to insert the alternate id. It will create a new alternate ID
if there is no ACTIVE alternate id present.
kumma 16-jul-2002 changed the cursor c_partyid to put upper on cp_instcd,2446067
pkpatel 31-JUL-2002 Bug No: 2461744
Removed the UPPER check for INSTITUTION_CD
npalanis 27-OCT-2002 Bug No: 2613704
Added the parameter p_error_code and added the check for overlap of alternate ID
***************************************************************/
CURSOR c_exists (cp_alt_id_type igs_or_org_alt_ids.org_alternate_id_type%TYPE,
cp_alt_id_val VARCHAR2,
cp_instcd igs_or_org_alt_ids.org_structure_id%TYPE,
cp_structure_type igs_or_org_alt_ids.org_structure_type%TYPE,
cp_end_date VARCHAR2) IS
SELECT org_alternate_id
FROM igs_or_org_alt_ids
WHERE org_alternate_id_type = cp_alt_id_type
AND org_structure_id = cp_instcd
AND org_structure_type = cp_structure_type
AND ( SYSDATE BETWEEN start_date AND NVL(end_date,TO_DATE(cp_end_date,'YYYY/MM/DD')) );
SELECT hp.party_id
FROM HZ_PARTIES hp, igs_pe_hz_parties ihp
WHERE ihp.oss_org_unit_cd = cp_instcd and
ihp.party_id = hp.party_id;
IGS_OR_ORG_ALT_IDS_PKG.INSERT_ROW(
x_rowid => l_rowid,
x_org_structure_id => p_instcd,
x_org_structure_type => 'INSTITUTE',
x_org_alternate_id_type => p_altidtype,
x_org_alternate_id => p_altidval,
x_start_date => SYSDATE,
x_end_date => NULL,
x_mode => 'R');
PROCEDURE update_institution(
p_instcd IN VARCHAR2,
p_instrec IN IGS_OR_INST_INT%ROWTYPE,
p_errind OUT NOCOPY VARCHAR2,
p_error_code OUT NOCOPY VARCHAR2,
p_error_text OUT NOCOPY VARCHAR2 )
AS
/*************************************************************
Created By :samaresh
Date Created By : 17-JUL-2001
Purpose : This Procedure Updates an Institution Record
Know limitations, enhancements or remarks
Change History
Who When What
SMVK 05-Feb-2002 Added Fund Authorization to
IGS_PE_HZ_PARTIES_PKG.UPDATE_ROW calls
as per enhancement bug no.2191470.
kumma 14-JUN-2002 Uncommented the call to message IGS_OR_INST_UPD_FAIL, 2410165
kumma 15-JUL-2002 In call to UPDATE_ORGANIZATION ,
converted the INSTITUTION_CD to upper.
In call to IGS_PE_HZ_PARTIES_PKG.UPDATE_ROW if the local_ind and os_ind are null
then substituted them witholder values of database. Bug 2446067
pkpatel 31-JUL-2002 Bug No: 2461744
Removed the UPPER check for INSTITUTION_CD
pkpatel 25-OCT-2002 Bug No: 2613704
Replaced column inst_priority_code_id with inst_priority_cd in igs_pe_hz_parties_pkg
ssawhney 30-APR-2003 V2API - OVN implementation.
vrathi 28-MAY-2003 Bug No: 2961982 Replaced update_row with add_row
ssaleem 26-SEP-2003 NULL check has been added while calling
IGS_OR_GEN_012_PKG.UPDATE_ORGANIZATION and igs_pe_hz_parties_pkg.ADD_row,
it is made sure that NULL values does not replace
existing values in the table.
skpandey 27-SEP-2005 Bug: 3663505
Description: Added ATTRIBUTES 21 TO 24 to store additional information in IGS_OR_GEN_012_PKG call
***************************************************************/
CURSOR c_partyid (cp_instcd VARCHAR2) IS
SELECT hp.*
FROM HZ_PARTIES HP, igs_pe_hz_parties ihp
WHERE ihp.oss_org_unit_cd = cp_instcd and
hp.party_id = ihp.party_id;
SELECT rowid ,PP.*
FROM IGS_PE_HZ_PARTIES PP
WHERE party_id = cp_partyid;
IGS_OR_GEN_012_PKG.UPDATE_ORGANIZATION (
p_party_id => l_party_rec.party_id,
p_institution_cd => p_instcd,
p_name => NVL(p_instrec.NAME,l_party_rec.party_name),
p_status => l_party_rec.status,
p_last_update => l_party_rec.last_update_date,
p_attribute_category => NVL(p_instrec.ATTRIBUTE_CATEGORY, l_party_rec.attribute_category),
p_attribute1 => NVL(p_instrec.ATTRIBUTE1,l_party_rec.attribute1),
p_attribute2 => NVL(p_instrec.ATTRIBUTE2,l_party_rec.attribute2),
p_attribute3 => NVL(p_instrec.ATTRIBUTE3,l_party_rec.attribute3),
p_attribute4 => NVL(p_instrec.ATTRIBUTE4,l_party_rec.attribute4),
p_attribute5 => NVL(p_instrec.ATTRIBUTE5,l_party_rec.attribute5),
p_attribute6 => NVL(p_instrec.ATTRIBUTE6,l_party_rec.attribute6),
p_attribute7 => NVL(p_instrec.ATTRIBUTE7,l_party_rec.attribute7),
p_attribute8 => NVL(p_instrec.ATTRIBUTE8,l_party_rec.attribute8),
p_attribute9 => NVL(p_instrec.ATTRIBUTE9,l_party_rec.attribute9),
p_attribute10 => NVL(p_instrec.ATTRIBUTE10,l_party_rec.attribute10),
p_attribute11 => NVL(p_instrec.ATTRIBUTE11,l_party_rec.attribute11),
p_attribute12 => NVL(p_instrec.ATTRIBUTE12,l_party_rec.attribute12),
p_attribute13 => NVL(p_instrec.ATTRIBUTE13,l_party_rec.attribute13),
p_attribute14 => NVL(p_instrec.ATTRIBUTE14,l_party_rec.attribute14),
p_attribute15 => NVL(p_instrec.ATTRIBUTE15,l_party_rec.attribute15),
p_attribute16 => NVL(p_instrec.ATTRIBUTE16,l_party_rec.attribute16),
p_attribute17 => NVL(p_instrec.ATTRIBUTE17,l_party_rec.attribute17),
p_attribute18 => NVL(p_instrec.ATTRIBUTE18,l_party_rec.attribute18),
p_attribute19 => NVL(p_instrec.ATTRIBUTE19,l_party_rec.attribute19),
p_attribute20 => NVL(p_instrec.ATTRIBUTE20,l_party_rec.attribute20),
p_return_status => l_return_status,
p_msg_data => l_msg_data,
p_object_version_number => l_ovn,
p_attribute21 => NVL(p_instrec.ATTRIBUTE21,l_party_rec.attribute21),
p_attribute22 => NVL(p_instrec.ATTRIBUTE22,l_party_rec.attribute22),
p_attribute23 => NVL(p_instrec.ATTRIBUTE23,l_party_rec.attribute23),
p_attribute24 => NVL(p_instrec.ATTRIBUTE24,l_party_rec.attribute24)
);
'igs.plsql.igs_or_inst_imp_002.update_institution.updatefail',
FND_MESSAGE.Get || '-' || l_msg_data ,NULL,NULL,NULL,NULL,NULL,IGS_OR_INST_IMP_001.g_request_id);
FND_MESSAGE.Set_Token('NAME','IMP_OR_INSTITUTION.Update Institution');
'igs.plsql.igs_or_inst_imp_002.update_institution.addrowfail',
FND_MESSAGE.Get||'-'||SQLERRM,NULL,NULL,NULL,NULL,NULL,IGS_OR_INST_IMP_001.g_request_id);
END update_institution;
PROCEDURE update_crosswalk_master (
p_cwlkid IN NUMBER,
p_instcd IN VARCHAR2,
p_errind OUT NOCOPY VARCHAR2)
AS
/*************************************************************
Created By :samaresh
Date Created By : 17-JUL-2001
Purpose : This Procedure Updates a record in the Crosswalk Master
with the Institution Code
Know limitations, enhancements or remarks
Change History
Who When What
kumma 16-JUL-2002 changed the institution_cd to upper,2446067
pkpatel 31-JUL-2002 Bug No: 2461744
Removed the UPPER check for INSTITUTION_CD
***************************************************************/
CURSOR c_getrow (cp_cwlkid NUMBER) IS
SELECT rowid ,ORC.*
FROM IGS_OR_CWLK ORC
WHERE crosswalk_id = cp_cwlkid;
IGS_OR_CWLK_PKG.update_row (
x_rowid => l_getrow_rec.rowid,
x_crosswalk_id => p_cwlkid,
x_institution_code => p_instcd,
x_institution_name => l_getrow_rec.institution_name,
x_mode => 'R' );
FND_MESSAGE.Set_Token('NAME','IMP_OR_INSTITUTION.Update Crosswalk Master');
'igs.plsql.igs_or_inst_imp_002.update_crosswalk_master.others',
FND_MESSAGE.Get||'-'||SQLERRM,NULL,NULL,NULL,NULL,NULL,IGS_OR_INST_IMP_001.g_request_id);
END update_crosswalk_master;