[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
|| Purpose : This public API is used to update and insert records to
|| Visa, Passport and Visit Histry tables in IGS
||
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| vredkar 14-Oct-2005 Bug#4654248,replaced generic duplicate/overlap
|| exists messages with component specific messages
******************************************************************************/
G_PKG_NAME CONSTANT VARCHAR2(30):='IGS_PE_VISAPASS_PUB';
IGS_PE_VISA_PKG.INSERT_ROW(
X_ROWID => l_rowid,
X_VISA_ID => x_visa_id,
X_PERSON_ID => l_visa_rec.person_id,
X_VISA_TYPE => l_visa_rec.VISA_TYPE ,
X_VISA_NUMBER => l_visa_rec.VISA_NUMBER,
X_VISA_ISSUE_DATE => l_visa_rec.VISA_ISSUE_DATE ,
X_VISA_EXPIRY_DATE => l_visa_rec.VISA_EXPIRY_DATE,
X_VISA_CATEGORY => NULL ,
X_VISA_ISSUING_POST => l_visa_rec.VISA_ISSUING_POST,
X_PASSPORT_ID => l_visa_rec.PASSPORT_ID,
X_AGENT_ORG_UNIT_CD => l_visa_rec.AGENT_ORG_UNIT_CD ,
X_AGENT_PERSON_ID => l_visa_rec.AGENT_PERSON_ID ,
X_AGENT_CONTACT_NAME => l_visa_rec.AGENT_CONTACT_NAME ,
X_ATTRIBUTE_CATEGORY => l_visa_rec.ATTRIBUTE_CATEGORY ,
X_ATTRIBUTE1 => l_visa_rec.ATTRIBUTE1 ,
X_ATTRIBUTE2 => l_visa_rec.ATTRIBUTE2 ,
X_ATTRIBUTE3 => l_visa_rec.ATTRIBUTE3 ,
X_ATTRIBUTE4 => l_visa_rec.ATTRIBUTE4 ,
X_ATTRIBUTE5 => l_visa_rec.ATTRIBUTE5 ,
X_ATTRIBUTE6 => l_visa_rec.ATTRIBUTE6 ,
X_ATTRIBUTE7 => l_visa_rec.ATTRIBUTE7 ,
X_ATTRIBUTE8 => l_visa_rec.ATTRIBUTE8 ,
X_ATTRIBUTE9 => l_visa_rec.ATTRIBUTE9 ,
X_ATTRIBUTE10 => l_visa_rec.ATTRIBUTE10 ,
X_ATTRIBUTE11 => l_visa_rec.ATTRIBUTE11 ,
X_ATTRIBUTE12 => l_visa_rec.ATTRIBUTE12 ,
X_ATTRIBUTE13 => l_visa_rec.ATTRIBUTE13 ,
X_ATTRIBUTE14 => l_visa_rec.ATTRIBUTE14 ,
X_ATTRIBUTE15 => l_visa_rec.ATTRIBUTE15 ,
X_ATTRIBUTE16 => l_visa_rec.ATTRIBUTE16 ,
X_ATTRIBUTE17 => l_visa_rec.ATTRIBUTE17 ,
X_ATTRIBUTE18 => l_visa_rec.ATTRIBUTE18 ,
X_ATTRIBUTE19 => l_visa_rec.ATTRIBUTE19 ,
X_ATTRIBUTE20 => l_visa_rec.ATTRIBUTE20 ,
x_visa_issuing_country => l_visa_rec.visa_issuing_country);
ELSIF l_message_name = 'FORM_RECORD_DELETED' THEN
x_return_status := FND_API.G_RET_STS_ERROR ;
PROCEDURE Update_Visa
( p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_visa_rec IN visa_rec_type
)
IS
CURSOR null_handlng_cur(cp_visa_rec IN visa_rec_type) IS
SELECT rowid,
visa_id,person_id,visa_type, visa_number, visa_issue_date, visa_expiry_date,visa_category,
visa_issuing_post,passport_id, agent_org_unit_cd, agent_person_id, agent_contact_name,
attribute_category,attribute1, attribute2, attribute3, attribute4, attribute5, attribute6,
attribute7, attribute8, attribute9, attribute10, attribute11, attribute12, attribute13,
attribute14, attribute15, attribute16, attribute17, attribute18, attribute19, attribute20,
visa_issuing_country
FROM IGS_PE_VISA
WHERE VISA_ID = cp_visa_rec.visa_id FOR UPDATE NOWAIT;
l_api_name CONSTANT VARCHAR2(30) := 'Update_Visa';
SAVEPOINT Update_Visa_PUB;
IGS_PE_VISA_PKG.UPDATE_ROW (
X_ROWID => dup_visa_rec.rowid,
X_VISA_ID => l_visa_rec.visa_id,
X_PERSON_ID => l_visa_rec.person_id,
X_VISA_TYPE => l_visa_rec.visa_type,
X_VISA_NUMBER => l_visa_rec.visa_number,
X_VISA_ISSUE_DATE => l_visa_rec.visa_issue_date,
X_VISA_EXPIRY_DATE => l_visa_rec.visa_expiry_date,
X_VISA_CATEGORY => NULL,
X_VISA_ISSUING_POST => l_visa_rec.visa_issuing_post,
X_PASSPORT_ID => l_visa_rec.passport_id,
X_AGENT_ORG_UNIT_CD => l_visa_rec.agent_org_unit_cd,
X_AGENT_PERSON_ID => l_visa_rec.agent_person_id,
X_AGENT_CONTACT_NAME => l_visa_rec.agent_contact_name,
X_ATTRIBUTE_CATEGORY => l_visa_rec.attribute_category,
X_ATTRIBUTE1 => l_visa_rec.attribute1,
X_ATTRIBUTE2 => l_visa_rec.attribute2,
X_ATTRIBUTE3 => l_visa_rec.attribute3,
X_ATTRIBUTE4 => l_visa_rec.attribute4,
X_ATTRIBUTE5 => l_visa_rec.attribute5,
X_ATTRIBUTE6 => l_visa_rec.attribute6,
X_ATTRIBUTE7 => l_visa_rec.attribute7,
X_ATTRIBUTE8 => l_visa_rec.attribute8,
X_ATTRIBUTE9 => l_visa_rec.attribute9,
X_ATTRIBUTE10 => l_visa_rec.attribute10,
X_ATTRIBUTE11 => l_visa_rec.attribute11,
X_ATTRIBUTE12 => l_visa_rec.attribute12,
X_ATTRIBUTE13 => l_visa_rec.attribute13,
X_ATTRIBUTE14 => l_visa_rec.attribute14,
X_ATTRIBUTE15 => l_visa_rec.attribute15,
X_ATTRIBUTE16 => l_visa_rec.attribute16,
X_ATTRIBUTE17 => l_visa_rec.attribute17,
X_ATTRIBUTE18 => l_visa_rec.attribute18,
X_ATTRIBUTE19 => l_visa_rec.attribute19,
X_ATTRIBUTE20 => l_visa_rec.attribute20,
X_visa_issuing_country => l_visa_rec.visa_issuing_country);
ROLLBACK TO Update_Visa_PUB;
ROLLBACK TO Update_Visa_PUB;
ROLLBACK TO Update_Visa_PUB;
ROLLBACK TO Update_Visa_PUB;
IF l_message_name IN('IGS_PE_VIS_ASOC_PASS_EXP','IGS_PE_VISA_DUP_EXISTS','IGS_PE_VISA_DATE_OVERLAP','IGS_EN_INV','IGS_PS_LGCY_MANDATORY', 'IGS_PE_VIPS_UPD_ERR','IGS_PE_VIPS_COL_NONUPD','FORM_RECORD_DELETED') THEN
x_return_status := FND_API.G_RET_STS_ERROR ;
END Update_Visa;
igs_pe_visit_histry_pkg.insert_row(
X_ROWID => l_rowid,
X_PORT_OF_ENTRY => l_visit_hstry_rec.port_of_entry,
X_CNTRY_ENTRY_FORM_NUM => l_visit_hstry_rec.cntry_entry_form_num ,
X_VISA_ID => l_visit_hstry_rec.visa_id ,
X_VISIT_START_DATE => l_visit_hstry_rec.visit_start_date ,
X_VISIT_END_DATE => l_visit_hstry_rec.visit_end_date ,
X_REMARKS => l_visit_hstry_rec.remarks,
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_MODE => 'R');
PROCEDURE Update_VisitHistry
( p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_visit_hstry_rec IN visit_hstry_rec_type
)
IS
CURSOR null_handlng_cur(cp_visit_rec IN visit_hstry_rec_type) IS
SELECT rowid,
port_of_entry, cntry_entry_form_num, visa_id, visit_start_date, visit_end_date, remarks
FROM IGS_PE_VISIT_HISTRY
WHERE port_of_entry = cp_visit_rec.port_of_entry AND
cntry_entry_form_num = cp_visit_rec.cntry_entry_form_num FOR UPDATE NOWAIT;
l_api_name CONSTANT VARCHAR2(30) := 'Update_VisitHistry';
SAVEPOINT Update_VisitHistry_PUB;
igs_pe_visit_histry_pkg.update_row(
X_ROWID => dup_visit_rec.rowid,
X_PORT_OF_ENTRY => l_visit_hstry_rec.port_of_entry,
X_CNTRY_ENTRY_FORM_NUM => l_visit_hstry_rec.cntry_entry_form_num,
X_VISA_ID => l_visit_hstry_rec.visa_id ,
X_VISIT_START_DATE => l_visit_hstry_rec.visit_start_date,
X_VISIT_END_DATE => l_visit_hstry_rec.visit_end_date,
X_REMARKS => l_visit_hstry_rec.remarks,
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);
ROLLBACK TO Update_VisitHistry_PUB;
ROLLBACK TO Update_VisitHistry_PUB;
ROLLBACK TO Update_VisitHistry_PUB;
ROLLBACK TO Update_VisitHistry_PUB;
END Update_VisitHistry;
IGS_PE_PASSPORT_PKG.INSERT_ROW(
X_ROWID => l_rowid,
X_PASSPORT_ID => x_passport_id ,
X_PERSON_ID => l_passport_rec.person_id,
X_PASSPORT_NUMBER => l_passport_rec.passport_number,
X_PASSPORT_EXPIRY_DATE => l_passport_rec.passport_expiry_date,
X_PASSPORT_CNTRY_CODE => l_passport_rec.passport_cntry_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_MODE => 'R'
);
ELSIF l_message_name = 'FORM_RECORD_DELETED' THEN
x_return_status := FND_API.G_RET_STS_ERROR ;
PROCEDURE Update_Passport
( p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_passport_rec IN passport_rec_type
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_Passport';
SELECT rowid,
PASSPORT_ID, PERSON_ID, PASSPORT_NUMBER, PASSPORT_EXPIRY_DATE, PASSPORT_CNTRY_CODE
FROM IGS_PE_PASSPORT
WHERE passport_id = cp_pass.passport_id FOR UPDATE NOWAIT;
SAVEPOINT Update_Passport_PUB;
igs_pe_passport_pkg.update_row(
X_ROWID => dup_pass_rec.rowid,
X_PASSPORT_ID => dup_pass_rec.passport_id,
X_PERSON_ID => l_passport_rec.person_id,
X_PASSPORT_NUMBER => l_passport_rec.passport_number,
X_PASSPORT_EXPIRY_DATE => l_passport_rec.passport_expiry_date,
X_PASSPORT_CNTRY_CODE => l_passport_rec.passport_cntry_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_MODE => 'R'
);
ROLLBACK TO Update_Passport_PUB;
ROLLBACK TO Update_Passport_PUB;
ROLLBACK TO Update_Passport_PUB;
ROLLBACK TO Update_Passport_PUB;
END Update_Passport;