[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
||kumma 03-MAY-2003 2941138, Modified dynamic query variable l_select_clause inside function validate_record for sqlbind bug of PKM_ISSUE
|| ssaleem 7-OCT-2003 Bug : 3130316
|| Validations done for individual records inside the main loop are removed
|| Instead they are done for of bulk records before the start of main loop
|| Logging is modified to include logging mechanism
|| ssaleem 25 Aug 2004 Moving the validate_record function in visa, passport and visit histry outside to the package level
|| Added new procedures validate_visa_pub,validate_passport_pub and visit histry pub that will be called by the Visa, Passport and Visit Histry Public APIs.
|| Changes as part of Bug # 3847525
|| vredkar 14-Oct-2005 Bug#4654248,replaced generic duplicate/overlap
|| exists messages with component specific messages
|| skpandey 3_FEB-2006 Bug: 4937960
|| Description: Change call from GET_WHERE_CLAUSE to GET_WHERE_CLAUSE_API as a part of Literal fix
*/
CURSOR visa_dtls(cp_interface_run_id igs_pe_visa_int.interface_run_id%TYPE) IS
SELECT vi.*, i.person_id
FROM igs_pe_visa_int vi,
igs_ad_interface_all i
WHERE vi.interface_id = i.interface_id
AND vi.STATUS = '2'
AND vi.interface_run_id = cp_interface_run_id
AND i.interface_run_id = cp_interface_run_id;
SELECT vh.*, i.person_id,pev.visa_id,pev.visa_issue_date issue_date,pev.visa_expiry_date expiry_date
FROM igs_pe_vst_hist_int vh,
igs_ad_interface_all i,
igs_pe_visa_int vi,
igs_pe_visa pev
WHERE vh.interface_visa_id = vi.interface_visa_id
AND vi.interface_id = i.interface_id
AND pev.person_id = i.person_id
AND vh.STATUS = cp_vh_status_2
AND vi.status = cp_vi_status_1
AND vh.interface_run_id = cp_interface_run_id
AND pev.visa_type = UPPER(vi.visa_type)
AND pev.visa_issue_date = TRUNC(vi.visa_issue_date);
SELECT pi.*, i.person_id
FROM igs_pe_passport_int pi,
igs_ad_interface_all i
WHERE pi.interface_id = i.interface_id
AND pi.STATUS = '2'
AND pi.interface_run_id = cp_interface_run_id
AND i.interface_run_id = cp_interface_run_id;
SELECT birth_date birth_dt
FROM igs_pe_person_base_v
WHERE person_id = cp_person_id;
l_select_clause VARCHAR2(2000):=
' SELECT ou1.org_unit_cd FROM igs_or_unit ou1,igs_or_status org_status WHERE org_status.s_org_status = ''ACTIVE''
AND org_status.org_status = ou1.org_status AND ou1.org_unit_cd = :agent_org_unit_cd';
SELECT person_id
FROM igs_pe_person_base_v
WHERE person_id = visa_rec.AGENT_PERSON_ID ;
SELECT passport_number
FROM igs_pe_passport p
WHERE p.person_id = visa_rec.person_id AND
p.passport_id = visa_rec.passport_id;
SELECT 'X'
FROM igs_lookup_values
WHERE lookup_type = cp_lookup_type AND
lookup_code = cp_visa_issuing_post AND
tag = cp_visa_issuing_country AND
enabled_flag = cp_enabled_flag;
l_select_clause := l_select_clause||' AND '||l_where_clause;
OPEN org_unit_cur FOR l_select_clause USING visa_rec.agent_org_unit_cd, l_func_name;
OPEN org_unit_cur FOR l_select_clause USING visa_rec.agent_org_unit_cd;
UPDATE igs_pe_visa_int
SET status = '1',
error_code = l_error
WHERE interface_visa_id = visa_rec.interface_visa_id;
UPDATE igs_pe_visa_int
SET status = '4',
error_code = l_error
WHERE interface_visa_id = visa_rec.interface_visa_id;
UPDATE igs_pe_visa_int
SET status = '3',
error_code = l_error
WHERE interface_visa_id = visa_rec.interface_visa_id;
UPDATE igs_pe_visa_int
SET status = '3',
error_code = l_error
WHERE interface_visa_id = visa_rec.interface_visa_id;
|| insert row
|| pkpatel 24-FEB-2003 Bug : 2783882
|| Modified the code for implementing the overlap chack from TBH
||
|| ssaleem 7-OCT-2003 Bug : 3130316
|| Validations done for individual records inside the main loop are removed
|| Instead they are done for of bulk records before the start of main loop
||
|| ssaleem 25 Aug 2004 Moving the validate_record function in prc_pe_visa procedure outside the package level
|| Added a new procedure that will be called by the Visa Public API.
|| Changes as part of Bug # 3847525
*/
CURSOR chk_duplicate(cp_person_id igs_pe_visa.person_id%TYPE,
cp_visa_type igs_pe_visa.visa_type%TYPE ,
cp_visa_issue_date igs_pe_visa.visa_issue_date%TYPE) IS
SELECT rowid,vi.*
FROM IGS_PE_VISA vi
WHERE person_id = cp_person_id AND
visa_type = cp_visa_type AND
visa_issue_date = cp_visa_issue_date; -- end_date IS NULL check removed
SAVEPOINT before_insert;
IGS_PE_VISA_PKG.INSERT_ROW(
X_ROWID => l_rowid,
X_VISA_ID => l_visaid,
X_PERSON_ID => visa_rec.person_id,
X_VISA_TYPE => visa_rec.VISA_TYPE ,
X_VISA_NUMBER => visa_rec.VISA_NUMBER,
X_VISA_ISSUE_DATE => visa_rec.VISA_ISSUE_DATE ,
X_VISA_EXPIRY_DATE => visa_rec.VISA_EXPIRY_DATE,
X_VISA_CATEGORY => visa_rec.VISA_CATEGORY ,
X_VISA_ISSUING_POST => visa_rec.VISA_ISSUING_POST,
X_PASSPORT_ID => visa_rec.PASSPORT_ID,
X_AGENT_ORG_UNIT_CD => visa_rec.AGENT_ORG_UNIT_CD ,
X_AGENT_PERSON_ID => visa_rec.AGENT_PERSON_ID ,
X_AGENT_CONTACT_NAME => visa_rec.AGENT_CONTACT_NAME ,
X_ATTRIBUTE_CATEGORY => visa_rec.ATTRIBUTE_CATEGORY ,
X_ATTRIBUTE1 => visa_rec.ATTRIBUTE1 ,
X_ATTRIBUTE2 => visa_rec.ATTRIBUTE2 ,
X_ATTRIBUTE3 => visa_rec.ATTRIBUTE3 ,
X_ATTRIBUTE4 => visa_rec.ATTRIBUTE4 ,
X_ATTRIBUTE5 => visa_rec.ATTRIBUTE5 ,
X_ATTRIBUTE6 => visa_rec.ATTRIBUTE6 ,
X_ATTRIBUTE7 => visa_rec.ATTRIBUTE7 ,
X_ATTRIBUTE8 => visa_rec.ATTRIBUTE8 ,
X_ATTRIBUTE9 => visa_rec.ATTRIBUTE9 ,
X_ATTRIBUTE10 => visa_rec.ATTRIBUTE10 ,
X_ATTRIBUTE11 => visa_rec.ATTRIBUTE11 ,
X_ATTRIBUTE12 => visa_rec.ATTRIBUTE12 ,
X_ATTRIBUTE13 => visa_rec.ATTRIBUTE13 ,
X_ATTRIBUTE14 => visa_rec.ATTRIBUTE14 ,
X_ATTRIBUTE15 => visa_rec.ATTRIBUTE15 ,
X_ATTRIBUTE16 => visa_rec.ATTRIBUTE16 ,
X_ATTRIBUTE17 => visa_rec.ATTRIBUTE17 ,
X_ATTRIBUTE18 => visa_rec.ATTRIBUTE18 ,
X_ATTRIBUTE19 => visa_rec.ATTRIBUTE19 ,
X_ATTRIBUTE20 => visa_rec.ATTRIBUTE20 ,
x_visa_issuing_country => visa_rec.visa_issuing_country,
X_MODE => 'R');
UPDATE igs_pe_visa_int
SET status = '1',
error_code = NULL
WHERE interface_visa_id = visa_rec.interface_visa_id;
ROLLBACK TO before_insert;
UPDATE igs_pe_visa_int
SET status = '3',
error_code = l_error
WHERE interface_visa_id = visa_rec.interface_visa_id;
SELECT 'X'
FROM igs_pe_visit_histry_v
WHERE person_id = cp_visa_rec.person_id AND
visa_type = cp_visa_rec.visa_type AND
visa_issue_date = cp_visa_rec.visa_issue_date AND
visit_end_date NOT BETWEEN cp_visa_rec.visa_issue_date AND (cp_visa_rec.visa_expiry_date+30);
SAVEPOINT before_update;
UPDATE igs_pe_visa_int
SET status = '3',
error_code = 'E559'
WHERE interface_visa_id = visa_rec.interface_visa_id;
IGS_PE_VISA_PKG.UPDATE_ROW (
X_ROWID => dup_visa_rec.rowid,
X_VISA_ID => dup_visa_rec.visa_id,
X_PERSON_ID => NVL(visa_rec.person_id,dup_visa_rec.person_id),
X_VISA_TYPE => NVL(visa_rec.visa_type,dup_visa_rec.visa_type),
X_VISA_NUMBER => NVL(visa_rec.visa_number,dup_visa_rec.VISA_NUMBER),
X_VISA_ISSUE_DATE => NVL(visa_rec.VISA_ISSUE_DATE,dup_visa_rec.VISA_ISSUE_DATE),
X_VISA_EXPIRY_DATE => NVL(visa_rec.VISA_EXPIRY_DATE,dup_visa_rec.VISA_EXPIRY_DATE),
X_VISA_CATEGORY => NVL(visa_rec.VISA_CATEGORY,dup_visa_rec.VISA_CATEGORY),
X_VISA_ISSUING_POST => NVL(visa_rec.VISA_ISSUING_POST,dup_visa_rec.VISA_ISSUING_POST),
X_PASSPORT_ID => NVL(visa_rec.PASSPORT_ID,dup_visa_rec.PASSPORT_ID),
X_AGENT_ORG_UNIT_CD => NVL(visa_rec.AGENT_ORG_UNIT_CD,dup_visa_rec.AGENT_ORG_UNIT_CD),
X_AGENT_PERSON_ID => NVL(visa_rec.AGENT_PERSON_ID,dup_visa_rec.AGENT_PERSON_ID) ,
X_AGENT_CONTACT_NAME => NVL(visa_rec.AGENT_CONTACT_NAME,dup_visa_rec.AGENT_CONTACT_NAME) ,
X_ATTRIBUTE_CATEGORY => NVL(visa_rec.attribute_category,dup_visa_rec.attribute_category) ,
X_ATTRIBUTE1 => NVL(visa_rec.attribute1, dup_visa_rec.attribute1) ,
X_ATTRIBUTE2 => NVL(visa_rec.attribute2, dup_visa_rec.attribute2) ,
X_ATTRIBUTE3 => NVL(visa_rec.attribute3, dup_visa_rec.attribute3) ,
X_ATTRIBUTE4 => NVL(visa_rec.attribute4, dup_visa_rec.attribute4) ,
X_ATTRIBUTE5 => NVL(visa_rec.attribute5, dup_visa_rec.attribute5) ,
X_ATTRIBUTE6 => NVL(visa_rec.attribute6, dup_visa_rec.attribute6) ,
X_ATTRIBUTE7 => NVL(visa_rec.attribute7, dup_visa_rec.attribute7) ,
X_ATTRIBUTE8 => NVL(visa_rec.attribute8, dup_visa_rec.attribute8) ,
X_ATTRIBUTE9 => NVL(visa_rec.attribute9, dup_visa_rec.attribute9) ,
X_ATTRIBUTE10 => NVL(visa_rec.attribute10,dup_visa_rec.attribute10) ,
X_ATTRIBUTE11 => NVL(visa_rec.attribute11,dup_visa_rec.attribute11) ,
X_ATTRIBUTE12 => NVL(visa_rec.attribute12,dup_visa_rec.attribute12) ,
X_ATTRIBUTE13 => NVL(visa_rec.attribute13,dup_visa_rec.attribute13) ,
X_ATTRIBUTE14 => NVL(visa_rec.attribute14,dup_visa_rec.attribute14) ,
X_ATTRIBUTE15 => NVL(visa_rec.attribute15,dup_visa_rec.attribute15) ,
X_ATTRIBUTE16 => NVL(visa_rec.attribute16,dup_visa_rec.attribute16) ,
X_ATTRIBUTE17 => NVL(visa_rec.attribute17,dup_visa_rec.attribute17) ,
X_ATTRIBUTE18 => NVL(visa_rec.attribute18,dup_visa_rec.attribute18) ,
X_ATTRIBUTE19 => NVL(visa_rec.attribute19,dup_visa_rec.attribute19) ,
X_ATTRIBUTE20 => NVL(visa_rec.attribute20,dup_visa_rec.attribute20) ,
X_visa_issuing_country => NVL(visa_rec.visa_issuing_country,dup_visa_rec.visa_issuing_country) ,
X_MODE => 'R');
UPDATE igs_pe_visa_int
SET status = '1',
error_code = NULL,
match_ind = '18'
WHERE interface_visa_id = visa_rec.interface_visa_id;
ROLLBACK TO before_update;
UPDATE igs_pe_visa_int
SET status = '3',
error_code = 'E558'
WHERE interface_visa_id = visa_rec.interface_visa_id;
UPDATE igs_pe_visa_int
SET status = '3',
error_code = 'E287'
WHERE interface_visa_id = visa_rec.interface_visa_id;
UPDATE igs_pe_visa_int
SET status = '3',
error_code = 'E014'
WHERE interface_visa_id = visa_rec.interface_visa_id;
UPDATE igs_pe_visa_int
SET status='3',
error_code = 'E695'
WHERE
interface_run_id=l_interface_run_id
AND STATUS = '2'
AND match_ind IS NOT NULL;
UPDATE igs_pe_visa_int vi
SET status='1', match_ind='19'
WHERE interface_run_id=l_interface_run_id
AND STATUS = '2'
AND EXISTS( SELECT vs.rowid
FROM igs_pe_visa vs,
igs_ad_interface_all ad
WHERE ad.interface_id = vi.interface_id AND
ad.interface_run_id = l_interface_run_id AND
vs.person_id = ad.person_id AND
vs.visa_type = UPPER(vi.visa_type) AND
vs.visa_issue_date = TRUNC(vi.visa_issue_date));
UPDATE igs_pe_visa_int
SET status = '1'
WHERE
interface_run_id=l_interface_run_id
AND status = '2'
AND match_ind IN ('18','19','22','23');
UPDATE igs_pe_visa_int
SET status = '3',
error_code = 'E695'
WHERE
interface_run_id=l_interface_run_id
AND status = '2'
AND ( match_ind IS NOT NULL AND match_ind <> '21' AND match_ind <> '25');
UPDATE igs_pe_visa_int vi
SET status='1',
match_ind = '23'
WHERE
interface_run_id=l_interface_run_id
AND status = '2'
AND match_ind IS NULL
AND EXISTS( SELECT vs.rowid
FROM igs_pe_visa vs,
igs_ad_interface_all ad
WHERE ad.interface_id = vi.interface_id AND
ad.interface_run_id = l_interface_run_id AND
vs.visa_type = UPPER(vi.visa_type) AND
UPPER(vs.visa_number) = UPPER(vi.visa_number) AND
vs.person_id = ad.person_id AND
((UPPER(vs.agent_org_unit_cd)= UPPER(vi.agent_org_unit_cd)) OR ((vs.agent_org_unit_cd IS NULL) AND (vi.agent_org_unit_cd IS NULL))) AND
((vs.agent_person_id = vi.agent_person_id) OR ((vs.agent_person_id IS NULL) AND (vi.agent_person_id IS NULL))) AND
((UPPER(vs.agent_contact_name) = UPPER(vi.agent_contact_name)) OR ((vs.agent_contact_name IS NULL) AND (vi.agent_contact_name IS NULL))) AND
vs.visa_issue_date = TRUNC(vi.visa_issue_date) AND
TRUNC(vs.visa_expiry_date) = TRUNC(vi.visa_expiry_date) AND
((vs.passport_id = vi.passport_id) OR ((vs.passport_id IS NULL) AND (vi.passport_id IS NULL))) AND
((UPPER(vs.visa_issuing_post) = UPPER(vi.visa_issuing_post)) OR ((vs.visa_issuing_post IS NULL) AND (vi.visa_issuing_post IS NULL))) AND
((UPPER(vs.visa_category) = UPPER(vi.visa_category)) OR ((vs.visa_category IS NULL) AND ( vi.visa_category is NULL))) AND
((UPPER(vs.attribute_category) = UPPER(vi.attribute_category)) OR ((vs.attribute_category IS NULL) AND (vi.attribute_category IS NULL))) AND
((UPPER(vs.attribute1) = UPPER(vi.attribute1)) OR ((vs.attribute1 IS NULL) AND (vi.attribute1 IS NULL))) AND
((UPPER(vs.attribute2) = UPPER(vi.attribute2)) OR ((vs.attribute2 IS NULL) AND (vi.attribute2 IS NULL))) AND
((UPPER(vs.attribute3) = UPPER(vi.attribute3)) OR ((vs.attribute3 IS NULL) AND (vi.attribute3 IS NULL))) AND
((UPPER(vs.attribute4) = UPPER(vi.attribute4)) OR ((vs.attribute4 IS NULL) AND (vi.attribute4 IS NULL))) AND
((UPPER(vs.attribute5) = UPPER(vi.attribute5)) OR ((vs.attribute5 IS NULL) AND (vi.attribute5 IS NULL))) AND
((UPPER(vs.attribute6) = UPPER(vi.attribute6)) OR ((vs.attribute6 IS NULL) AND (vi.attribute6 IS NULL))) AND
((UPPER(vs.attribute7) = UPPER(vi.attribute7)) OR ((vs.attribute7 IS NULL) AND (vi.attribute7 IS NULL))) AND
((UPPER(vs.attribute8) = UPPER(vi.attribute8)) OR ((vs.attribute8 IS NULL) AND (vi.attribute8 IS NULL))) AND
((UPPER(vs.attribute9) = UPPER(vi.attribute9)) OR ((vs.attribute9 IS NULL) AND (vi.attribute9 IS NULL))) AND
((UPPER(vs.attribute10) = UPPER(vi.attribute10)) OR ((vs.attribute10 IS NULL) AND (vi.attribute10 IS NULL))) AND
((UPPER(vs.attribute11) = UPPER(vi.attribute11)) OR ((vs.attribute11 IS NULL) AND (vi.attribute11 IS NULL))) AND
((UPPER(vs.attribute12) = UPPER(vi.attribute12)) OR ((vs.attribute12 IS NULL) AND (vi.attribute12 IS NULL))) AND
((UPPER(vs.attribute13) = UPPER(vi.attribute13)) OR ((vs.attribute13 IS NULL) AND (vi.attribute13 IS NULL))) AND
((UPPER(vs.attribute14) = UPPER(vi.attribute14)) OR ((vs.attribute14 IS NULL) AND (vi.attribute14 IS NULL))) AND
((UPPER(vs.attribute15) = UPPER(vi.attribute15)) OR ((vs.attribute15 IS NULL) AND (vi.attribute15 IS NULL))) AND
((UPPER(vs.attribute16) = UPPER(vi.attribute16)) OR ((vs.attribute16 IS NULL) AND (vi.attribute16 IS NULL))) AND
((UPPER(vs.attribute17) = UPPER(vi.attribute17)) OR ((vs.attribute17 IS NULL) AND (vi.attribute17 IS NULL))) AND
((UPPER(vs.attribute18) = UPPER(vi.attribute18)) OR ((vs.attribute18 IS NULL) AND (vi.attribute18 IS NULL))) AND
((UPPER(vs.attribute19) = UPPER(vi.attribute19)) OR ((vs.attribute19 IS NULL) AND (vi.attribute19 IS NULL))) AND
((UPPER(vs.attribute20) = UPPER(vi.attribute20)) OR ((vs.attribute20 IS NULL) AND (vi.attribute20 IS NULL))) AND
((UPPER(vs.visa_issuing_country) = UPPER(vi.visa_issuing_country)) OR ((vs.visa_issuing_country IS NULL) AND (vi.visa_issuing_country IS NULL))));
UPDATE igs_pe_visa_int vi
SET status = '3',
match_ind='20',
dup_visa_id = (SELECT visa_id
FROM igs_pe_visa vs,
igs_ad_interface_all ad
WHERE ad.interface_id = vi.interface_id AND
ad.interface_run_id = l_interface_run_id AND
vs.person_id = ad.person_id AND
vs.visa_type = UPPER(vi.visa_type) AND
vs.visa_issue_date = TRUNC(vi.visa_issue_date) )
WHERE
interface_run_id=l_interface_run_id AND
status = '2' AND
match_ind IS NULL AND
EXISTS (SELECT vs.rowid
FROM igs_pe_visa vs,
igs_ad_interface_all ad
WHERE ad.interface_id = vi.interface_id AND
ad.interface_run_id = l_interface_run_id AND
vs.person_id = ad.person_id AND
vs.visa_type = UPPER(vi.visa_type) AND
vs.visa_issue_date = TRUNC(vi.visa_issue_date));
UPDATE igs_pe_visa_int
SET status = '4',
error_code = l_error_code
WHERE interface_visa_id = visa_rec.interface_visa_id;
SELECT BIRTH_DATE Birth_dt
FROM IGS_PE_PERSON_BASE_V
WHERE person_id = cp_person_id;
UPDATE igs_pe_passport_int
SET status = '1',
error_code = p_err_code
WHERE interface_passport_id = pass_rec.interface_passport_id;
UPDATE igs_pe_passport_int
SET status = '3',
error_code = p_err_code
WHERE interface_passport_id = pass_rec.interface_passport_id;
UPDATE igs_pe_passport_int
SET status = '3',
error_code = p_err_code
WHERE interface_passport_id = pass_rec.interface_passport_id;
SELECT rowid, pi.*
FROM igs_pe_passport pi
WHERE person_id = cp_person_id AND
UPPER(passport_number) = UPPER(cp_passport_number) AND
passport_cntry_code = UPPER(cp_passport_cntry_code);
IGS_PE_PASSPORT_PKG.INSERT_ROW(
X_ROWID => l_rowid,
X_PASSPORT_ID => l_pass_id ,
X_PERSON_ID => pass_rec.person_id,
X_PASSPORT_NUMBER => pass_rec.passport_number,
X_PASSPORT_EXPIRY_DATE => pass_rec.passport_expiry_date,
X_PASSPORT_CNTRY_CODE => pass_rec.passport_cntry_code ,
X_ATTRIBUTE_CATEGORY => pass_rec.attribute_category ,
X_ATTRIBUTE1 => pass_rec.attribute1 ,
X_ATTRIBUTE2 => pass_rec.attribute2 ,
X_ATTRIBUTE3 => pass_rec.attribute3 ,
X_ATTRIBUTE4 => pass_rec.attribute4 ,
X_ATTRIBUTE5 => pass_rec.attribute5 ,
X_ATTRIBUTE6 => pass_rec.attribute6 ,
X_ATTRIBUTE7 => pass_rec.attribute7 ,
X_ATTRIBUTE8 => pass_rec.attribute8 ,
X_ATTRIBUTE9 => pass_rec.attribute9 ,
X_ATTRIBUTE10 => pass_rec.attribute10 ,
X_ATTRIBUTE11 => pass_rec.attribute11 ,
X_ATTRIBUTE12 => pass_rec.attribute12 ,
X_ATTRIBUTE13 => pass_rec.attribute13 ,
X_ATTRIBUTE14 => pass_rec.attribute14 ,
X_ATTRIBUTE15 => pass_rec.attribute15 ,
X_ATTRIBUTE16 => pass_rec.attribute16 ,
X_ATTRIBUTE17 => pass_rec.attribute17 ,
X_ATTRIBUTE18 => pass_rec.attribute18 ,
X_ATTRIBUTE19 => pass_rec.attribute19 ,
X_ATTRIBUTE20 => pass_rec.attribute20 ,
X_MODE => 'R'
);
UPDATE igs_pe_passport_int
SET status = '1',
error_code = l_error
WHERE interface_passport_id = pass_rec.interface_passport_id;
UPDATE igs_pe_passport_int
SET status = '3',
error_code = l_error
WHERE interface_passport_id = pass_rec.interface_passport_id;
igs_pe_passport_pkg.update_row(
X_ROWID => dup_pass_rec.rowid,
X_PASSPORT_ID => dup_pass_rec.passport_id,
X_PERSON_ID => NVL(pass_rec.person_id,dup_pass_rec.person_id),
X_PASSPORT_NUMBER => NVL(pass_rec.passport_number,dup_pass_rec.passport_number),
X_PASSPORT_EXPIRY_DATE => NVL(pass_rec.passport_expiry_date,dup_pass_rec.passport_expiry_date),
X_PASSPORT_CNTRY_CODE => NVL(pass_rec.passport_cntry_code,dup_pass_rec.passport_cntry_code),
X_ATTRIBUTE_CATEGORY => NVL(pass_rec.attribute_category,dup_pass_rec.attribute_category) ,
X_ATTRIBUTE1 => NVL(pass_rec.attribute1,dup_pass_rec.attribute1),
X_ATTRIBUTE2 => NVL(pass_rec.attribute2,dup_pass_rec.attribute2),
X_ATTRIBUTE3 => NVL(pass_rec.attribute3,dup_pass_rec.attribute3),
X_ATTRIBUTE4 => NVL(pass_rec.attribute4,dup_pass_rec.attribute4),
X_ATTRIBUTE5 => NVL(pass_rec.attribute5,dup_pass_rec.attribute5),
X_ATTRIBUTE6 => NVL(pass_rec.attribute6,dup_pass_rec.attribute6),
X_ATTRIBUTE7 => NVL(pass_rec.attribute7,dup_pass_rec.attribute7),
X_ATTRIBUTE8 => NVL(pass_rec.attribute8,dup_pass_rec.attribute8),
X_ATTRIBUTE9 => NVL(pass_rec.attribute9,dup_pass_rec.attribute9),
X_ATTRIBUTE10 => NVL(pass_rec.attribute10,dup_pass_rec.attribute10),
X_ATTRIBUTE11 => NVL(pass_rec.attribute11,dup_pass_rec.attribute11),
X_ATTRIBUTE12 => NVL(pass_rec.attribute12,dup_pass_rec.attribute12),
X_ATTRIBUTE13 => NVL(pass_rec.attribute13,dup_pass_rec.attribute13),
X_ATTRIBUTE14 => NVL(pass_rec.attribute14,dup_pass_rec.attribute14),
X_ATTRIBUTE15 => NVL(pass_rec.attribute15,dup_pass_rec.attribute15),
X_ATTRIBUTE16 => NVL(pass_rec.attribute16,dup_pass_rec.attribute16),
X_ATTRIBUTE17 => NVL(pass_rec.attribute17,dup_pass_rec.attribute17),
X_ATTRIBUTE18 => NVL(pass_rec.attribute18,dup_pass_rec.attribute18),
X_ATTRIBUTE19 => NVL(pass_rec.attribute19,dup_pass_rec.attribute19),
X_ATTRIBUTE20 => NVL(pass_rec.attribute20,dup_pass_rec.attribute20),
X_MODE => 'R'
);
UPDATE igs_pe_passport_int
SET status = p_status,
error_code = p_error_code
WHERE interface_passport_id = pass_rec.interface_passport_id;
UPDATE igs_pe_passport_int
SET status='3',
error_code = 'E695'
WHERE
interface_run_id=l_interface_run_id
AND STATUS = '2'
AND match_ind IS NOT NULL;
UPDATE igs_pe_passport_int pi
SET status='1',
match_ind='19'
WHERE
interface_run_id=l_interface_run_id
AND STATUS = '2'
AND EXISTS( SELECT ps.rowid
FROM igs_pe_passport ps,
igs_ad_interface_all ad
WHERE ad.interface_id = pi.interface_id AND
ad.interface_run_id = l_interface_run_id AND
ps.person_id = ad.person_id AND
ps.passport_cntry_code = UPPER(pi.passport_cntry_code) AND
UPPER(ps.passport_number) = UPPER(pi.passport_number));
UPDATE igs_pe_passport_int
SET status = '1'
WHERE
interface_run_id=l_interface_run_id
AND status = '2'
AND match_ind IN ('18','19','22','23');
UPDATE igs_pe_passport_int
SET status = '3',
error_code = 'E695'
WHERE
interface_run_id=l_interface_run_id
AND status = '2'
AND ( match_ind IS NOT NULL AND match_ind <> '21' AND match_ind <> '25');
UPDATE igs_pe_passport_int pi
SET status='1',
match_ind = '23'
WHERE
interface_run_id=l_interface_run_id
AND status = '2'
AND match_ind IS NULL
AND EXISTS( SELECT ps.rowid
FROM igs_pe_passport ps,
igs_ad_interface_all ad
WHERE ad.interface_id = pi.interface_id AND
ad.interface_run_id = l_interface_run_id AND
ps.person_id = ad.person_id AND
ps.passport_cntry_code = UPPER(pi.passport_cntry_code) AND
UPPER(ps.passport_number) = UPPER(pi.passport_number) AND
TRUNC(ps.passport_expiry_date) = TRUNC(pi.passport_expiry_date) AND
((ps.attribute_category = pi.attribute_category) OR ((ps.attribute_category IS NULL) AND (pi.attribute_category IS NULL))) AND
((ps.attribute1 = pi.attribute1) OR ((ps.attribute1 IS NULL) AND (pi.attribute1 IS NULL))) AND
((ps.attribute2 = pi.attribute2) OR ((ps.attribute2 IS NULL) AND (pi.attribute2 IS NULL))) AND
((ps.attribute3 = pi.attribute3) OR ((ps.attribute3 IS NULL) AND (pi.attribute3 IS NULL))) AND
((ps.attribute4 = pi.attribute4) OR ((ps.attribute4 IS NULL) AND (pi.attribute4 IS NULL))) AND
((ps.attribute5 = pi.attribute5) OR ((ps.attribute5 IS NULL) AND (pi.attribute5 IS NULL))) AND
((ps.attribute6 = pi.attribute6) OR ((ps.attribute6 IS NULL) AND (pi.attribute6 IS NULL))) AND
((ps.attribute7 = pi.attribute7) OR ((ps.attribute7 IS NULL) AND (pi.attribute7 IS NULL))) AND
((ps.attribute8 = pi.attribute8) OR ((ps.attribute8 IS NULL) AND (pi.attribute8 IS NULL))) AND
((ps.attribute9 = pi.attribute9) OR ((ps.attribute9 IS NULL) AND (pi.attribute9 IS NULL))) AND
((ps.attribute10 = pi.attribute10) OR ((ps.attribute10 IS NULL) AND (pi.attribute10 IS NULL))) AND
((ps.attribute11 = pi.attribute11) OR ((ps.attribute11 IS NULL) AND (pi.attribute11 IS NULL))) AND
((ps.attribute12 = pi.attribute12) OR ((ps.attribute12 IS NULL) AND (pi.attribute12 IS NULL))) AND
((ps.attribute13 = pi.attribute13) OR ((ps.attribute13 IS NULL) AND (pi.attribute13 IS NULL))) AND
((ps.attribute14 = pi.attribute14) OR ((ps.attribute14 IS NULL) AND (pi.attribute14 IS NULL))) AND
((ps.attribute15 = pi.attribute15) OR ((ps.attribute15 IS NULL) AND (pi.attribute15 IS NULL))) AND
((ps.attribute16 = pi.attribute16) OR ((ps.attribute16 IS NULL) AND (pi.attribute16 IS NULL))) AND
((ps.attribute17 = pi.attribute17) OR ((ps.attribute17 IS NULL) AND (pi.attribute17 IS NULL))) AND
((ps.attribute18 = pi.attribute18) OR ((ps.attribute18 IS NULL) AND (pi.attribute18 IS NULL))) AND
((ps.attribute19 = pi.attribute19) OR ((ps.attribute19 IS NULL) AND (pi.attribute19 IS NULL))) AND
((ps.attribute20 = pi.attribute20) OR ((ps.attribute20 IS NULL) AND (pi.attribute20 IS NULL))));
UPDATE igs_pe_passport_int pi
SET status = '3',
match_ind='20',
dup_passport_id = (SELECT passport_id
FROM igs_pe_passport ps,
igs_ad_interface_all ad
WHERE ad.interface_id = pi.interface_id AND
ad.interface_run_id = l_interface_run_id AND
ps.person_id = ad.person_id AND
ps.passport_cntry_code = UPPER(pi.passport_cntry_code) AND
UPPER(ps.passport_number) = UPPER(pi.passport_number))
WHERE interface_run_id=l_interface_run_id AND
status = '2' AND
match_ind IS NULL AND
EXISTS (SELECT ps.rowid
FROM igs_pe_passport ps,
igs_ad_interface_all ad
WHERE ad.interface_id = pi.interface_id AND
ps.person_id = ad.person_id AND
ad.interface_run_id = l_interface_run_id AND
ps.passport_cntry_code = UPPER(pi.passport_cntry_code) AND
UPPER(ps.passport_number) = UPPER(pi.passport_number));
UPDATE igs_pe_passport_int
SET match_ind = '18', -- MATCH OCCURED AND USED IMPORTED VALUES
status = l_status ,
error_code = l_error_code
WHERE interface_passport_id= pass_rec.interface_passport_id;
UPDATE igs_pe_passport_int
SET match_ind = '18',
status = '3',
error_code = 'E014'
WHERE interface_passport_id= pass_rec.interface_passport_id;
UPDATE igs_pe_passport_int
SET status = l_status ,
error_code = l_error_code
WHERE interface_passport_id= pass_rec.interface_passport_id;
UPDATE igs_pe_passport_int
SET status = '3',
error_code = 'E014'
WHERE interface_passport_id= pass_rec.interface_passport_id;
SELECT BIRTH_DATE Birth_dt
FROM IGS_PE_PERSON_BASE_V
WHERE
person_id = cp_person_id;
SELECT 'Y' FROM IGS_PE_VISA
WHERE person_id = cp_person_id AND
visa_id = cp_visa_id AND
cp_visit_start_date BETWEEN visa_issue_date AND visa_expiry_date;
UPDATE igs_pe_vst_hist_int
SET status = '1',
error_code = p_err_code
WHERE interface_visit_histry_id = visit_rec.interface_visit_histry_id;
UPDATE igs_pe_vst_hist_int
SET status = '3',
error_code = p_err_code
WHERE interface_visit_histry_id = visit_rec.interface_visit_histry_id;
UPDATE igs_pe_vst_hist_int
SET status = '3',
error_code = p_err_code
WHERE interface_visit_histry_id = visit_rec.interface_visit_histry_id;
SELECT person_id,visa_issue_date issue_date,visa_expiry_date expiry_date
FROM igs_pe_visa
WHERE visa_id = cp_visa_id;
SELECT rowid,vh.*
FROM igs_pe_visit_histry vh
WHERE port_of_entry = cp_port_of_entry AND
cntry_entry_form_num = cp_cntry_entry_form_num;
SAVEPOINT before_insert;
igs_pe_visit_histry_pkg.insert_row(
X_ROWID => l_rowid,
X_PORT_OF_ENTRY => visit_rec.port_of_entry,
X_CNTRY_ENTRY_FORM_NUM => visit_rec.cntry_entry_form_num ,
X_VISA_ID => visit_rec.visa_id ,
X_VISIT_START_DATE => visit_rec.visit_start_date ,
X_VISIT_END_DATE => visit_rec.visit_end_date ,
X_REMARKS => visit_rec.remarks ,
X_ATTRIBUTE_CATEGORY => visit_rec.attribute_category ,
X_ATTRIBUTE1 => visit_rec.attribute1 ,
X_ATTRIBUTE2 => visit_rec.attribute2 ,
X_ATTRIBUTE3 => visit_rec.attribute3 ,
X_ATTRIBUTE4 => visit_rec.attribute4 ,
X_ATTRIBUTE5 => visit_rec.attribute5 ,
X_ATTRIBUTE6 => visit_rec.attribute6 ,
X_ATTRIBUTE7 => visit_rec.attribute7 ,
X_ATTRIBUTE8 => visit_rec.attribute8 ,
X_ATTRIBUTE9 => visit_rec.attribute9 ,
X_ATTRIBUTE10 => visit_rec.attribute10 ,
X_ATTRIBUTE11 => visit_rec.attribute11 ,
X_ATTRIBUTE12 => visit_rec.attribute12 ,
X_ATTRIBUTE13 => visit_rec.attribute13 ,
X_ATTRIBUTE14 => visit_rec.attribute14 ,
X_ATTRIBUTE15 => visit_rec.attribute15 ,
X_ATTRIBUTE16 => visit_rec.attribute16 ,
X_ATTRIBUTE17 => visit_rec.attribute17 ,
X_ATTRIBUTE18 => visit_rec.attribute18 ,
X_ATTRIBUTE19 => visit_rec.attribute19 ,
X_ATTRIBUTE20 => visit_rec.attribute20 ,
X_MODE => 'R');
UPDATE igs_pe_vst_hist_int
SET status = '1',
error_code = l_error
WHERE interface_visit_histry_id = visit_rec.interface_visit_histry_id;
ROLLBACK TO before_insert;
UPDATE igs_pe_vst_hist_int
SET status = '3',
error_code = l_error
WHERE interface_visit_histry_id = visit_rec.interface_visit_histry_id;
SAVEPOINT before_update;
igs_pe_visit_histry_pkg.update_row(
X_ROWID => dup_visit_rec.rowid,
X_PORT_OF_ENTRY => NVL(visit_rec.port_of_entry,dup_visit_rec.port_of_entry),
X_CNTRY_ENTRY_FORM_NUM => NVL(visit_rec.cntry_entry_form_num,dup_visit_rec.cntry_entry_form_num),
X_VISA_ID => NVL(visit_rec.visa_id ,dup_visit_rec.visa_id),
X_VISIT_START_DATE => NVL(visit_rec.visit_start_date,dup_visit_rec.visit_start_date),
X_VISIT_END_DATE => l_visit_end_date,
X_REMARKS => NVL(visit_rec.remarks,dup_visit_rec.remarks),
X_ATTRIBUTE_CATEGORY => NVL(visit_rec.attribute_category,dup_visit_rec.attribute_category),
X_ATTRIBUTE1 => NVL(visit_rec.attribute1,dup_visit_rec.attribute1),
X_ATTRIBUTE2 => NVL(visit_rec.attribute2,dup_visit_rec.attribute2),
X_ATTRIBUTE3 => NVL(visit_rec.attribute3,dup_visit_rec.attribute3),
X_ATTRIBUTE4 => NVL(visit_rec.attribute4,dup_visit_rec.attribute4),
X_ATTRIBUTE5 => NVL(visit_rec.attribute5,dup_visit_rec.attribute5),
X_ATTRIBUTE6 => NVL(visit_rec.attribute6,dup_visit_rec.attribute6),
X_ATTRIBUTE7 => NVL(visit_rec.attribute7,dup_visit_rec.attribute7),
X_ATTRIBUTE8 => NVL(visit_rec.attribute8,dup_visit_rec.attribute8),
X_ATTRIBUTE9 => NVL(visit_rec.attribute9,dup_visit_rec.attribute9),
X_ATTRIBUTE10 => NVL(visit_rec.attribute10,dup_visit_rec.attribute10),
X_ATTRIBUTE11 => NVL(visit_rec.attribute11,dup_visit_rec.attribute11),
X_ATTRIBUTE12 => NVL(visit_rec.attribute12,dup_visit_rec.attribute12),
X_ATTRIBUTE13 => NVL(visit_rec.attribute13,dup_visit_rec.attribute13),
X_ATTRIBUTE14 => NVL(visit_rec.attribute14,dup_visit_rec.attribute14),
X_ATTRIBUTE15 => NVL(visit_rec.attribute15,dup_visit_rec.attribute15),
X_ATTRIBUTE16 => NVL(visit_rec.attribute16,dup_visit_rec.attribute16),
X_ATTRIBUTE17 => NVL(visit_rec.attribute17,dup_visit_rec.attribute17),
X_ATTRIBUTE18 => NVL(visit_rec.attribute18,dup_visit_rec.attribute18),
X_ATTRIBUTE19 => NVL(visit_rec.attribute19,dup_visit_rec.attribute19),
X_ATTRIBUTE20 => NVL(visit_rec.attribute20,dup_visit_rec.attribute20),
X_MODE => 'R');
ROLLBACK TO before_update;
UPDATE igs_pe_vst_hist_int
SET status = '3',
error_code = 'E014'
WHERE interface_visit_histry_id = visit_rec.interface_visit_histry_id;
UPDATE igs_pe_vst_hist_int
SET status = '3',
error_code = 'E014'
WHERE interface_visit_histry_id = visit_rec.interface_visit_histry_id;
UPDATE igs_pe_vst_hist_int
SET status='3',
error_code = 'E695'
WHERE
interface_run_id=l_interface_run_id
AND STATUS = '2'
AND match_ind IS NOT NULL;
UPDATE igs_pe_vst_hist_int vh
SET status='1',
match_ind='19'
WHERE interface_run_id=l_interface_run_id
AND STATUS = '2'
AND EXISTS( SELECT vs.rowid
FROM igs_pe_visit_histry vs
WHERE vs.port_of_entry = UPPER(vh.port_of_entry) AND
UPPER(vs.cntry_entry_form_num) = UPPER(vh.cntry_entry_form_num))
AND EXISTS( SELECT vi.rowid
FROM igs_pe_visa_int vi
WHERE vi.interface_visa_id = vh.interface_visa_id AND
vi.status = '1');
UPDATE igs_pe_vst_hist_int vh
SET status = '1'
WHERE interface_run_id=l_interface_run_id
AND status = '2'
AND match_ind IN ('18','19','22','23')
AND EXISTS( SELECT vi.rowid
FROM igs_pe_visa_int vi
WHERE vi.interface_visa_id = vh.interface_visa_id AND
vi.status = '1');
UPDATE igs_pe_vst_hist_int vh
SET status = '3',
error_code = 'E695'
WHERE interface_run_id=l_interface_run_id
AND status = '2'
AND ( match_ind IS NOT NULL AND match_ind <> '21' AND match_ind <> '25')
AND EXISTS( SELECT vi.rowid
FROM igs_pe_visa_int vi
WHERE vi.interface_visa_id = vh.interface_visa_id AND
vi.status = '1');
UPDATE igs_pe_vst_hist_int vh
SET status='1',
match_ind = '23'
WHERE interface_run_id=l_interface_run_id
AND status = '2'
AND match_ind IS NULL
AND EXISTS( SELECT vi.rowid
FROM igs_pe_visa_int vi
WHERE vi.interface_visa_id = vh.interface_visa_id AND
vi.status = '1')
AND EXISTS( SELECT vs.rowid
FROM igs_pe_visit_histry vs ,
igs_pe_visa pev
WHERE vs.visa_id = pev.visa_id AND
vs.port_of_entry = UPPER(vh.port_of_entry) AND
UPPER(vs.cntry_entry_form_num) = UPPER(vh.cntry_entry_form_num) AND
TRUNC(vs.visit_start_date) = TRUNC(vh.visit_start_date) AND
((TRUNC(vs.visit_end_date) = TRUNC(vh.visit_end_date)) OR ((vs.visit_end_date IS NULL) AND (vh.visit_end_date IS NULL))) AND
((UPPER(vs.remarks) = UPPER(vh.remarks)) OR ((vs.remarks IS NULL) AND (vh.remarks IS NULL))) AND
((vs.attribute_category = vh.attribute_category) OR ((vs.attribute_category IS NULL) AND (vh.attribute_category IS NULL))) AND
((vs.attribute1 = vh.attribute1) OR ((vs.attribute1 IS NULL) AND (vh.attribute1 IS NULL))) AND
((vs.attribute2 = vh.attribute2) OR ((vs.attribute2 IS NULL) AND (vh.attribute2 IS NULL))) AND
((vs.attribute3 = vh.attribute3) OR ((vs.attribute3 IS NULL) AND (vh.attribute3 IS NULL))) AND
((vs.attribute4 = vh.attribute4) OR ((vs.attribute4 IS NULL) AND (vh.attribute4 IS NULL))) AND
((vs.attribute5 = vh.attribute5) OR ((vs.attribute5 IS NULL) AND (vh.attribute5 IS NULL))) AND
((vs.attribute6 = vh.attribute6) OR ((vs.attribute6 IS NULL) AND (vh.attribute6 IS NULL))) AND
((vs.attribute7 = vh.attribute7) OR ((vs.attribute7 IS NULL) AND (vh.attribute7 IS NULL))) AND
((vs.attribute8 = vh.attribute8) OR ((vs.attribute8 IS NULL) AND (vh.attribute8 IS NULL))) AND
((vs.attribute9 = vh.attribute9) OR ((vs.attribute9 IS NULL) AND (vh.attribute9 IS NULL))) AND
((vs.attribute10 = vh.attribute10) OR ((vs.attribute10 IS NULL) AND (vh.attribute10 IS NULL))) AND
((vs.attribute11 = vh.attribute11) OR ((vs.attribute11 IS NULL) AND (vh.attribute11 IS NULL))) AND
((vs.attribute12 = vh.attribute12) OR ((vs.attribute12 IS NULL) AND (vh.attribute12 IS NULL))) AND
((vs.attribute13 = vh.attribute13) OR ((vs.attribute13 IS NULL) AND (vh.attribute13 IS NULL))) AND
((vs.attribute14 = vh.attribute14) OR ((vs.attribute14 IS NULL) AND (vh.attribute14 IS NULL))) AND
((vs.attribute15 = vh.attribute15) OR ((vs.attribute15 IS NULL) AND (vh.attribute15 IS NULL))) AND
((vs.attribute16 = vh.attribute16) OR ((vs.attribute16 IS NULL) AND (vh.attribute16 IS NULL))) AND
((vs.attribute17 = vh.attribute17) OR ((vs.attribute17 IS NULL) AND (vh.attribute17 IS NULL))) AND
((vs.attribute18 = vh.attribute18) OR ((vs.attribute18 IS NULL) AND (vh.attribute18 IS NULL))) AND
((vs.attribute19 = vh.attribute19) OR ((vs.attribute19 IS NULL) AND (vh.attribute19 IS NULL))) AND
((vs.attribute20 = vh.attribute20) OR ((vs.attribute20 IS NULL) AND (vh.attribute20 IS NULL))));
UPDATE igs_pe_vst_hist_int vh
SET status = '3',
match_ind='20',
(dup_port_of_entry,dup_cntry_entry_form_num) = (SELECT port_of_entry,cntry_entry_form_num
FROM igs_pe_visit_histry vs
WHERE vs.port_of_entry = UPPER(vh.port_of_entry) AND
UPPER(vs.cntry_entry_form_num) = UPPER(vh.cntry_entry_form_num))
WHERE interface_run_id=l_interface_run_id AND
status = '2' AND
EXISTS( SELECT vsi.rowid
FROM igs_pe_visa_int vsi
WHERE vsi.interface_visa_id = vh.interface_visa_id AND
vsi.status = '1') AND
match_ind IS NULL AND
EXISTS (SELECT rowid
FROM igs_pe_visit_histry
WHERE port_of_entry = UPPER(vh.port_of_entry) AND
UPPER(cntry_entry_form_num) = UPPER(vh.cntry_entry_form_num) );
UPDATE igs_pe_vst_hist_int
SET match_ind = '18', -- MATCH OCCURED AND USED IMPORTED VALUES
status = l_status ,
error_code = l_error_code
WHERE interface_visit_histry_id = visit_rec.interface_visit_histry_id;
UPDATE igs_pe_vst_hist_int
SET match_ind = '18',
status = '3',
error_code = 'E014'
WHERE interface_visit_histry_id = visit_rec.interface_visit_histry_id;
UPDATE igs_pe_vst_hist_int
SET status = l_status ,
error_code = l_error_code
WHERE interface_visit_histry_id = visit_rec.interface_visit_histry_id;
UPDATE igs_pe_vst_hist_int
SET status = '3',
error_code = 'E014'
WHERE interface_visit_histry_id = visit_rec.interface_visit_histry_id;
SELECT rowid,ei.*
FROM igs_pe_eit ei
WHERE person_id = cp_person_id AND
UPPER(information_type) = UPPER(cp_information_type) AND
TRUNC(start_date) = TRUNC(cp_start_date) ;
SELECT ei.*, i.person_id
FROM igs_pe_eit_int ei,
igs_ad_interface_all i
WHERE ei.interface_id = i.interface_id
AND ei.STATUS = cp_ei_status_2
AND ei.interface_run_id = cp_interface_run_id
AND i.interface_run_id = cp_interface_run_id
AND ei.information_type =cp_information_type;
SELECT BIRTH_DATE Birth_dt
FROM IGS_PE_PERSON_BASE_V
WHERE
person_id = cp_person_id;
UPDATE igs_pe_eit_int
SET status = '1',
error_code = l_error
WHERE interface_eit_id = eit_rec.interface_eit_id;
UPDATE igs_pe_eit_int
SET status = '3',
error_code = l_error
WHERE interface_eit_id = eit_rec.interface_eit_id;
UPDATE igs_pe_eit_int
SET status = '3',
error_code = l_error
WHERE interface_eit_id = eit_rec.interface_eit_id;
SELECT count(1) FROM IGS_PE_EIT
WHERE person_id = cp_eit_rec.person_id
AND INFORMATION_TYPE = cp_eit_rec.information_type
AND (NVL(cp_eit_rec.end_date,IGS_GE_DATE.igsdate(cp_end_date)) BETWEEN START_DATE AND NVL(END_DATE,IGS_GE_DATE.igsdate(cp_end_date))
OR
cp_eit_rec.start_date BETWEEN START_DATE AND NVL(END_DATE,IGS_GE_DATE.igsdate(cp_end_date))
OR
( cp_eit_rec.start_date < START_DATE AND
NVL(end_date,IGS_GE_DATE.igsdate(cp_end_date))< NVL(cp_eit_rec.end_date,IGS_GE_DATE.igsdate(cp_end_date)) ) );
UPDATE igs_pe_eit_int
SET status = '3',
error_code = l_error
WHERE interface_eit_id = eit_rec.interface_eit_id;
igs_pe_eit_pkg.insert_row(
X_ROWID => l_rowid,
X_PE_EIT_ID => l_eit_id,
X_PERSON_ID => eit_rec.person_id ,
X_INFORMATION_TYPE => eit_rec.information_type ,
X_PEI_INFORMATION1 => eit_rec.pei_information1 ,
X_PEI_INFORMATION2 => eit_rec.pei_information2 ,
X_PEI_INFORMATION3 => eit_rec.pei_information3 ,
X_PEI_INFORMATION4 => eit_rec.pei_information4 ,
X_PEI_INFORMATION5 => eit_rec.pei_information5 ,
X_START_DATE => eit_rec.start_date ,
X_END_DATE => eit_rec.end_date ,
X_MODE => 'R'
);
UPDATE igs_pe_eit_int
SET status = '1',
error_code = l_error
WHERE interface_eit_id = eit_rec.interface_eit_id;
UPDATE igs_pe_eit_int
SET status = '3',
error_code = l_error
WHERE interface_eit_id = eit_rec.interface_eit_id;
SELECT count(1) FROM IGS_PE_EIT
WHERE person_id = cp_eit_rec.person_id
AND information_type = cp_eit_rec.information_type
AND start_date <> cp_eit_rec.start_date
AND (NVL(cp_eit_rec.end_date,IGS_GE_DATE.igsdate(cp_end_date)) BETWEEN START_DATE AND NVL(END_DATE,IGS_GE_DATE.igsdate(cp_end_date))
OR
cp_eit_rec.start_date BETWEEN START_DATE AND NVL(END_DATE,IGS_GE_DATE.igsdate(cp_end_date))
OR
( cp_eit_rec.start_date < START_DATE AND
NVL(end_date,IGS_GE_DATE.igsdate(cp_end_date))< NVL(cp_eit_rec.end_date,IGS_GE_DATE.igsdate(cp_end_date)) ) );
UPDATE igs_pe_eit_int
SET status = '3',
error_code = l_error
WHERE interface_eit_id = eit_rec.interface_eit_id;
igs_pe_eit_pkg.update_row(
X_ROWID => dup_eit_rec.rowid,
X_PE_EIT_ID => dup_eit_rec.pe_eit_id,
X_PERSON_ID => NVL(eit_rec.person_id,dup_eit_rec.person_id),
X_INFORMATION_TYPE => NVL(eit_rec.information_type,dup_eit_rec.information_type),
X_PEI_INFORMATION1 => NVL(eit_rec.pei_information1,dup_eit_rec.pei_information1) ,
X_PEI_INFORMATION2 => NVL(eit_rec.pei_information2,dup_eit_rec.pei_information2) ,
X_PEI_INFORMATION3 => NVL(eit_rec.pei_information3,dup_eit_rec.pei_information3) ,
X_PEI_INFORMATION4 => NVL(eit_rec.pei_information4,dup_eit_rec.pei_information4) ,
X_PEI_INFORMATION5 => NVL(eit_rec.pei_information5,dup_eit_rec.pei_information5) ,
X_START_DATE => NVL(eit_rec.start_date,dup_eit_rec.start_date),
X_END_DATE => NVL(eit_rec.end_date,dup_eit_rec.end_date),
X_MODE => 'R');
UPDATE igs_pe_eit_int
SET status = '3',
error_code = 'E014'
WHERE interface_eit_id = eit_rec.interface_eit_id;
UPDATE igs_pe_eit_int
SET status='3',
error_code = 'E695'
WHERE interface_run_id=l_interface_run_id
AND STATUS = '2'
AND UPPER(information_type) ='PE_INT_PERM_RES'
AND match_ind IS NOT NULL;
UPDATE igs_pe_eit_int ei
SET status='1',
match_ind='19'
WHERE interface_run_id=l_interface_run_id
AND STATUS = '2'
AND UPPER(information_type) ='PE_INT_PERM_RES'
AND EXISTS( SELECT es.rowid
FROM igs_pe_eit es,
igs_ad_interface_all ad
WHERE ad.interface_id = ei.interface_id AND
es.person_id = ad.person_id AND
ad.interface_run_id = l_interface_run_id AND
es.information_type = UPPER(ei.information_type) AND
es.start_date = TRUNC(ei.start_date));
UPDATE igs_pe_eit_int
SET status = '1'
WHERE interface_run_id=l_interface_run_id
AND status = '2'
AND UPPER(information_type) ='PE_INT_PERM_RES'
AND match_ind IN ('18','19','22','23');
UPDATE igs_pe_eit_int
SET status = '3',
error_code = 'E695'
WHERE interface_run_id=l_interface_run_id
AND status = '2'
AND UPPER(information_type) ='PE_INT_PERM_RES'
AND ( match_ind IS NOT NULL AND match_ind <> '21' AND match_ind <> '25');
UPDATE igs_pe_eit_int ei
SET status='1',
match_ind = '23'
WHERE interface_run_id=l_interface_run_id
AND status = '2'
AND UPPER(information_type) ='PE_INT_PERM_RES'
AND match_ind IS NULL
AND EXISTS( SELECT es.rowid
FROM igs_pe_eit es,
igs_ad_interface_all ad
WHERE ad.interface_id = ei.interface_id AND
es.person_id = ad.person_id AND
ad.interface_run_id = l_interface_run_id AND
es.information_type = UPPER(ei.information_type) AND
TRUNC(es.start_date) = TRUNC(ei.start_date) AND
((UPPER(es.pei_information1) = UPPER(ei.pei_information1)) OR ((es.pei_information1 IS NULL) AND (ei.pei_information1 IS NULL))) AND
((UPPER(es.pei_information2) = UPPER(ei.pei_information2)) OR ((es.pei_information2 IS NULL) AND (ei.pei_information2 IS NULL))) AND
((UPPER(es.pei_information3) = UPPER(ei.pei_information3)) OR ((es.pei_information3 IS NULL) AND (ei.pei_information3 IS NULL))) AND
((UPPER(es.pei_information4) = UPPER(ei.pei_information4)) OR ((es.pei_information4 IS NULL) AND (ei.pei_information4 IS NULL))) AND
((UPPER(es.pei_information5) = UPPER(ei.pei_information5)) OR ((es.pei_information5 IS NULL) AND (ei.pei_information5 IS NULL))) AND
((TRUNC(es.end_date) = TRUNC(ei.end_date)) OR ((es.end_date IS NULL) AND (ei.end_date IS NULL))));
UPDATE igs_pe_eit_int ei
SET status = '3',
match_ind='20',
dup_pe_eit_id = (SELECT pe_eit_id
FROM igs_pe_eit es,
igs_ad_interface_all ad
WHERE ad.interface_id = ei.interface_id AND
es.person_id = ad.person_id AND
ad.interface_run_id = l_interface_run_id AND
es.information_type = UPPER(ei.information_type) AND
es.start_date = TRUNC(ei.start_date) )
WHERE interface_run_id=l_interface_run_id AND
status = '2' AND
information_type ='PE_INT_PERM_RES' AND
match_ind IS NULL AND
EXISTS (SELECT es.rowid
FROM igs_pe_eit es,
igs_ad_interface_all ad
WHERE ad.interface_id = ei.interface_id AND
es.person_id = ad.person_id AND
ad.interface_run_id = l_interface_run_id AND
es.information_type = UPPER(ei.information_type) AND
es.start_date = TRUNC(ei.start_date) );
UPDATE igs_pe_eit_int
SET match_ind = '18', -- MATCH OCCURED AND USED IMPORTED VALUES
status = l_status ,
error_code = l_error_code
WHERE interface_eit_id = eit_rec.interface_eit_id;
UPDATE igs_pe_eit_int
SET match_ind = '18',
status = '3',
error_code = 'E014'
WHERE interface_eit_id = eit_rec.interface_eit_id;
UPDATE igs_pe_eit_int
SET
status = l_status ,
error_code = l_error_code
WHERE
interface_eit_id = eit_rec.interface_eit_id;
UPDATE igs_pe_eit_int
SET status = '3',
error_code = 'E014'
WHERE interface_eit_id = eit_rec.interface_eit_id;
|| Added the parameter p_party_site_id in update address.
|| Modified for performance.
|| gmaheswa 27-Jan-2006 Bug: 4938278: Call IGS_PE_WF_GEN. ADDR_BULK_SYNCHRONIZATION to raise bulk
|| address change notification after process address records of all persons.
|| (reverse chronological order - newest change first)
*/
lnDupExist NUMBER;
SELECT ai.*, i.person_id
FROM igs_ad_addr_int_all ai, igs_ad_interface_all i
WHERE ai.status = '2' AND
i.interface_run_id = cp_interface_run_id AND
ai.interface_id = i.interface_id AND
i.status = '1';
SELECT hz_party_sites.rowid,hz_party_sites.*
FROM hz_locations, hz_party_sites
WHERE hz_party_sites.party_id = cp_addr_rec.person_id
AND hz_party_sites.location_id = hz_locations.location_id
AND UPPER(NVL(hz_locations.address1,cp_x_value)) = UPPER(NVL(cp_addr_rec.addr_line_1,cp_x_value))
AND UPPER(NVL(hz_locations.address2,cp_x_value)) = UPPER(NVL(cp_addr_rec.addr_line_2,cp_x_value))
AND UPPER(NVL(hz_locations.address3,cp_x_value)) = UPPER(NVL(cp_addr_rec.addr_line_3,cp_x_value))
AND UPPER(NVL(hz_locations.address4,cp_x_value)) = UPPER(NVL(cp_addr_rec.addr_line_4,cp_x_value))
AND UPPER(NVL(hz_locations.city,cp_x_value)) = UPPER(NVL(cp_addr_rec.city,cp_x_value))
AND UPPER(NVL(hz_locations.state,cp_x_value)) = UPPER(NVL(cp_addr_rec.state,cp_x_value))
AND hz_locations.country = cp_addr_rec.country
AND UPPER(NVL(hz_locations.county,cp_x_value)) = UPPER(NVL(cp_addr_rec.county,cp_x_value))
AND UPPER(NVL(hz_locations.province,cp_x_value)) = UPPER(NVL(cp_addr_rec.province,cp_x_value));
UPDATE igs_ad_addr_int_all
SET status='3',
error_code = 'E695'
WHERE interface_run_id=l_interface_run_id
AND STATUS = '2'
AND match_ind IS NOT NULL;
UPDATE igs_ad_addr_int_all ai
SET status='1',
match_ind='19'
WHERE interface_run_id=l_interface_run_id
AND STATUS = '2'
AND EXISTS( SELECT hs.rowid
FROM hz_party_sites hs,
igs_ad_interface_all ad,
hz_locations hl
WHERE ad.interface_id = ai.interface_id AND
hs.party_id = ad.person_id AND
hs.location_id = hl.location_id AND
UPPER(NVL(hl.address1,'X')) = UPPER(NVL(ai.addr_line_1,'X')) AND
UPPER(NVL(hl.address2,'X')) = UPPER(NVL(ai.addr_line_2,'X')) AND
UPPER(NVL(hl.address3,'X')) = UPPER(NVL(ai.addr_line_3,'X')) AND
UPPER(NVL(hl.address4,'X')) = UPPER(NVL(ai.addr_line_4,'X')) AND
UPPER(NVL(hl.city,'X')) = UPPER(NVL(ai.city,'X')) AND
UPPER(NVL(hl.state,'X')) = UPPER(NVL(ai.state,'X')) AND
hl.country = UPPER(ai.country) AND
UPPER(NVL(hl.county,'X')) = UPPER(NVL(ai.county,'X')) AND
UPPER(NVL(hl.province,'X')) = UPPER(NVL(ai.province,'X')));
UPDATE igs_ad_addr_int_all
SET status = '1'
WHERE interface_run_id=l_interface_run_id
AND status = '2'
AND match_ind IN ('18','19','22','23');
UPDATE igs_ad_addr_int_all
SET status = '3',
error_code = 'E695'
WHERE interface_run_id=l_interface_run_id
AND status = '2'
AND ( match_ind IS NOT NULL AND match_ind <> '21' AND match_ind <> '25');
UPDATE igs_ad_addr_int_all ai
SET status='1',
match_ind = '23'
WHERE interface_run_id=l_interface_run_id
AND status = '2'
AND match_ind IS NULL
AND EXISTS( SELECT hs.rowid
FROM hz_locations hl,
hz_party_sites hs,
igs_ad_interface_all ad
WHERE ad.interface_id = ai.interface_id AND
hs.party_id = ad.person_id AND
hs.location_id = hl.location_id AND
NVL(UPPER(hl.address1), 'X') = NVL(UPPER(ai.addr_line_1), 'X') AND
NVL(UPPER(hl.address2), 'X') = NVL(UPPER(ai.addr_line_2), 'X') AND
NVL(UPPER(hl.address3), 'X') = NVL(UPPER(ai.addr_line_3), 'X') AND
NVL(UPPER(hl.address4), 'X') = NVL(UPPER(ai.addr_line_4), 'X') AND
NVL(UPPER(hl.city), 'X') = NVL(UPPER(ai.city), 'X') AND
NVL(UPPER(hl.state), 'X') = NVL(UPPER(ai.state), 'X') AND
NVL(UPPER(hl.province), 'X') = NVL(UPPER(ai.province), 'X') AND
NVL(UPPER(hl.county), 'X') = NVL(UPPER(ai.county), 'X') AND
hl.country = UPPER(ai.country) AND
NVL(UPPER(hl.postal_code), 'X') = NVL(UPPER(ai.postcode), 'X'));
UPDATE igs_ad_addr_int_all ai
SET status = '3',
match_ind='20',
dup_party_site_id = (SELECT hs.party_site_id
FROM hz_party_sites hs,
igs_ad_interface_all ad,
hz_locations hl
WHERE ad.interface_id = ai.interface_id AND
ROWNUM = 1 AND
hs.party_id = ad.person_id AND
hs.location_id = hl.location_id AND
UPPER(NVL(hl.address1,'X')) = UPPER(NVL(ai.addr_line_1,'X')) AND
UPPER(NVL(hl.address2,'X')) = UPPER(NVL(ai.addr_line_2,'X')) AND
UPPER(NVL(hl.address3,'X')) = UPPER(NVL(ai.addr_line_3,'X')) AND
UPPER(NVL(hl.address4,'X')) = UPPER(NVL(ai.addr_line_4,'X')) AND
UPPER(NVL(hl.city,'X')) = UPPER(NVL(ai.city,'X')) AND
UPPER(NVL(hl.state,'X')) = UPPER(NVL(ai.state,'X')) AND
hl.country = UPPER(ai.country) AND
UPPER(NVL(hl.county,'X')) = UPPER(NVL(ai.county,'X')) AND
UPPER(NVL(hl.province,'X')) = UPPER(NVL(ai.province,'X')))
WHERE interface_run_id=l_interface_run_id AND
status = '2' AND
match_ind IS NULL AND
EXISTS (SELECT hs.rowid
FROM hz_party_sites hs,
igs_ad_interface_all ad,
hz_locations hl
WHERE ad.interface_id = ai.interface_id AND
hs.party_id = ad.person_id AND
hs.location_id = hl.location_id AND
UPPER(NVL(hl.address1,'X')) = UPPER(NVL(ai.addr_line_1,'X')) AND
UPPER(NVL(hl.address2,'X')) = UPPER(NVL(ai.addr_line_2,'X')) AND
UPPER(NVL(hl.address3,'X')) = UPPER(NVL(ai.addr_line_3,'X')) AND
UPPER(NVL(hl.address4,'X')) = UPPER(NVL(ai.addr_line_4,'X')) AND
UPPER(NVL(hl.city,'X')) = UPPER(NVL(ai.city,'X')) AND
UPPER(NVL(hl.state,'X')) = UPPER(NVL(ai.state,'X')) AND
hl.country = UPPER(ai.country) AND
UPPER(NVL(hl.county,'X')) = UPPER(NVL(ai.county,'X')) AND
UPPER(NVL(hl.province,'X')) = UPPER(NVL(ai.province,'X')) );
l_addr_rec1.last_updated_by := addr_rec.last_updated_by;
l_addr_rec1.last_update_date := addr_rec.last_update_date;
l_addr_rec1.last_update_login := addr_rec.last_update_login;
l_addr_rec1.program_update_date := addr_rec.program_update_date;
Igs_Ad_Imp_002.update_address(p_addr_rec => l_addr_rec1,
p_person_id => addr_rec.person_id,
p_location_id => l_addr_rec.location_id,
p_party_site_id => l_addr_rec.party_site_id );
Igs_Ad_Imp_002.update_address( p_addr_rec => l_addr_rec1,
p_person_id => addr_rec.person_id,
p_location_id => l_addr_rec.location_id,
p_party_site_id => l_addr_rec.party_site_id);