[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
|| npalanis 22-APR-2003 BUG:2832980 location_id is not selected in form_dup where clause because it is not used any where
|| pkpatel 22-JUN-2001 Bug no.2702536
|| Added the parameters p_match_ind, p_person_id, p_addr_type and p_person_id_type to IGS_AD_IMP_FIND_DUP_PERSONS
|| And implemented the new record level duplicate check.
|| gmuralid 4 -DEC-2002 Change by gmuralid, removed reference to table igs_ad_intl_int,
|| igs_pe_fund_dep_int.Included references to igs_pe_visa_int,
|| igs_pe_vst_hist_int,igs_pe_passport_int,igs_pe_eit_int
|| As a part of BUG 2599109, SEVIS Build
||
|| ssawhney 21-may-2002 Bug 2381539, %imp_p% procedures, incorrect ref to variable x_lvcExactSelectClause in case of partial match
|| because of which it was not going into match at all.
|| pkpatel 30-MAY-2002 Bug 2377580, parameters were missing in the call to Igs_Pe_Identify_Dups.form_dup_whereclause
|| npalanis 5-Jun-2002 Bug 2397849 , The function for match indicator 15 ,16 and 17 are handled.
|| pkpatel 10-OCT-2002 Bug No: 2603065
|| Increased the size of variable x_lvcExactSelectClause and x_lvcPartialSelectClause from 500 to 2000
|| ssawhney 22-oct-2002 SWS104, Bug 2630860. Introduced validations from making STATISTICS not mandatory.
|| modified for ACADHONORS.SWS104 obsoleted IGS_AD_REFS_INT
|| sjalasut 31st oct SWS105 ad_collact table obsoleted
|| sjalasut Jan 20, 2003 changed the references of IGS_AD_INQ_CHAR_INT to IGS_RC_I_CHAR_INT for RCT101 Build. bug 2664699
|| rrengara 14 Feb 2003 changes for RCT Build. Removed the obsolete table names and replaced the new table names
|| ssawhney 24-feb-2003 REF CUR dup_matches_cur was not being closed for exact match of DUP_MATCHES_P and PP
|| vrathi 26-Jun-2003 Bug 3001974 Added specific messages in duplicate check fiunctions. + sswhney - valriable lenght increased
for whereclause execution.
|| asbala 23-SEP-2003 Bug 3130316, Duplicate Person Matching Performance Improvements
|| gmaheswa 9-OCT-2003 Bug 3146324, Match Criteria sets Enhancement
*/
-- constants to replace match indicator values for sql performance tuning
cst_mi_val_11 CONSTANT VARCHAR2(2) := '11';
|| Increased the size of variable x_lvcExactSelectClause and x_lvcPartialSelectClause from 500 to 2000
|| pkpatel 22-JUN-2001 Bug no.2702536
|| Added the parameters p_match_ind, p_person_id
|| pkpatel 4-MAY-2003 Bug 3004858 (PKM Issue to use bind variable)
|| (reverse chronological order - newest change first)
*/
CURSOR imp_person_cur(cp_d_interface_id igs_ad_imp_matches_p_v.interface_id%TYPE) IS
SELECT *
FROM igs_ad_imp_matches_p_v
WHERE interface_id = cp_d_interface_id;
x_lvcExactSelectClause VARCHAR2(32000);
x_lvcPartialSelectClause VARCHAR2(32000);
x_select_clause => x_lvcExactSelectClause);
IF x_lvcExactSelectClause <> 'PARTIAL_MATCH' THEN
-- Open the Dynamic Cursor with the select statement returned for Exact Match
l_cursor_id := dbms_sql.open_cursor;
dbms_sql.parse(l_cursor_id, x_lvcExactSelectClause, dbms_sql.native);
Igs_Ad_Imp_Near_Mtch_Pkg.insert_row
(x_rowid =>l_rowid,
x_org_id => lnOrg_ID,
x_near_mtch_id=>l_pk,
x_interface_id=>p_d_interface_id,
x_person_id=>l_person_id,
x_match_ind=>'E',
x_action=>'D',
x_addr_type=> p_c_addr_type,
x_person_id_type=>p_c_person_id_type,
x_match_set_id=>p_d_match_set_id,
x_mode =>'I',
x_Party_Site_ID => NULL);
/*If the dynamic Query returns only one row, then Update the
igs_ad_interface_table */
IF x_match_cnt = 1 THEN /* Only One Match is Found Match_Ind 12 is - Match to a Single Person*/
UPDATE igs_ad_interface
SET match_ind = cst_mi_val_12,
person_id = l_person_id
WHERE interface_id = imp_person_rec.interface_id;
ELSIF x_match_cnt > 1 THEN -- if more than one duplicate is found then update match_ind to 13
-- 13 - Match to Multiple Persons
UPDATE igs_ad_interface
SET match_ind = cst_mi_val_13,
ERROR_CODE = cst_err_val_2,
STATUS = cst_stat_val_3
WHERE interface_id = imp_person_rec.interface_id;
x_select_clause => x_lvcPartialSelectClause);
IF x_lvcPartialSelectClause IS NOT NULL THEN
-- Execute the Dynamic SQL
l_cursor_id1 := dbms_sql.open_cursor;
dbms_sql.parse(l_cursor_id1, x_lvcPartialSelectClause, dbms_sql.native);
Igs_Ad_Imp_Near_Mtch_Pkg.insert_row
(x_rowid =>l_rowid,
x_org_id => lnOrg_ID,
x_near_mtch_id=>l_pk,
x_interface_id=>p_d_interface_id,
x_person_id=>l_person_id,
x_match_ind=>'P',
x_action=>'D',
x_addr_type=> p_c_addr_type,
x_person_id_type=>p_c_person_id_type,
x_match_set_id=>p_d_match_set_id,
x_mode =>'R',
x_Party_Site_ID => NULL);
UPDATE igs_ad_interface
SET match_ind = cst_mi_val_11
WHERE interface_id = imp_person_rec.interface_id;
UPDATE igs_ad_interface
SET match_ind = cst_mi_val_14,
ERROR_CODE = cst_err_val_3,
STATUS = cst_stat_val_3
WHERE interface_id = imp_person_rec.interface_id;
UPDATE igs_ad_interface
SET status = cst_stat_val_3,
match_ind = cst_mi_val_24,
ERROR_CODE = cst_err_val_1
WHERE interface_id = p_d_interface_id;
|| Increased the size of variable x_lvcExactSelectClause and x_lvcPartialSelectClause from 500 to 2000
|| pkpatel 22-JUN-2001 Bug no.2702536
|| Added the parameters p_match_ind, p_person_id
|| pkpatel 4-MAY-2003 Bug 3004858 (PKM Issue to use bind variable)
|| (reverse chronological order - newest change first)
*/
l_prog_label VARCHAR2(100);
SELECT *
FROM igs_ad_imp_matches_pp_v
WHERE interface_id = cp_d_interface_id AND
( person_id_type = cp_c_person_id_type OR person_id_type IS NULL );
x_lvcExactSelectClause VARCHAR2(32000);
x_lvcPartialSelectClause VARCHAR2(32000);
x_select_clause => x_lvcExactSelectClause,
x_api_person_id =>imp_person_rec.alternate_id,
x_person_id_type =>imp_person_rec.person_id_type
);
IF x_lvcExactSelectClause <> 'PARTIAL_MATCH' THEN
l_cursor_id := dbms_sql.open_cursor;
dbms_sql.parse(l_cursor_id, x_lvcExactSelectClause, dbms_sql.native);
/* Insert into igs_ad_imp_near_match, all the duplicate records */
Igs_Ad_Imp_Near_Mtch_Pkg.insert_row
(x_rowid =>l_rowid,
x_Org_ID => lnOrg_ID,
x_near_mtch_id=>l_pk,
x_interface_id=>p_d_interface_id,
x_person_id=> l_person_id,
x_match_ind=>'E',
x_action=>'D',
x_addr_type=>p_c_addr_type,
x_person_id_type=>p_c_person_id_type,
x_match_set_id=>p_d_match_set_id,
x_mode =>'R',
x_Party_Site_ID => NULL);
/*If the dynamic Query returns only one row, then Update the igs_ad_interface_table */
IF x_match_cnt = 1 THEN /* Only One Match is Found */
UPDATE igs_ad_interface
SET match_ind = cst_mi_val_12,
person_id = l_person_id
WHERE interface_id = imp_person_rec.interface_id;
UPDATE igs_ad_interface
SET match_ind = cst_mi_val_13,
error_code = cst_err_val_2,
STATUS = cst_stat_val_3
WHERE interface_id = imp_person_rec.interface_id;
x_select_clause => x_lvcPartialSelectClause);
IF x_lvcPartialSelectClause IS NOT NULL THEN
/* Exceute the Partial Select Clause */
l_cursor_id1 := dbms_sql.open_cursor;
dbms_sql.parse(l_cursor_id1, x_lvcPartialSelectClause, dbms_sql.native);
/* Insert into igs_ad_imp_near_match_int, all the duplicate records */
Igs_Ad_Imp_Near_Mtch_Pkg.insert_row
(x_rowid =>l_rowid,
x_Org_ID => lnOrg_ID,
x_near_mtch_id=>l_pk,
x_interface_id=>p_d_interface_id,
x_person_id=> l_person_id,
x_match_ind=>'P',
x_action=>'D',
x_addr_type=>p_c_addr_type,
x_person_id_type=>p_c_person_id_type,
x_match_set_id=>p_d_match_set_id,
x_mode =>'R',
x_party_Site_ID => NULL);
UPDATE igs_ad_interface
SET match_ind = cst_mi_val_11
WHERE interface_id = imp_person_rec.interface_id;
UPDATE igs_ad_interface
SET match_ind = cst_mi_val_14,
ERROR_CODE = cst_err_val_3,
STATUS = cst_stat_val_3
WHERE interface_id = imp_person_rec.interface_id;
UPDATE igs_ad_interface
SET status = cst_stat_val_3,
match_ind = cst_mi_val_24,
error_code = cst_err_val_1
WHERE interface_id = p_d_interface_id;
|| Increased the size of variable x_lvcExactSelectClause and x_lvcPartialSelectClause from 500 to 2000
|| pkpatel 22-JUN-2001 Bug no.2702536
|| Added the parameters p_match_ind, p_person_id
|| pkpatel 4-MAY-2003 Bug 3004858 (PKM Issue to use bind variable)
*/
l_prog_label VARCHAR2(100);
SELECT *
FROM igs_ad_imp_matches_pa_v
WHERE interface_id = cp_d_interface_id
AND
( addr_type = cp_c_addr_type OR addr_type IS NULL OR Igs_Pe_Identify_Dups.g_primary_addr_flag = 'Y');
SELECT PS.party_site_id
FROM hz_party_sites PS,hz_party_site_uses PSU
WHERE PS.party_site_id = PSU.party_site_id AND
PS.party_id = cp_person_id AND
PSU.site_use_type = cp_addr_type;
SELECT PS.party_site_id
FROM hz_party_sites PS
WHERE PS.party_id = cp_person_id AND
PS.identifying_address_flag = 'Y';
x_lvcExactSelectClause VARCHAR2(32000);
x_lvcPartialSelectClause VARCHAR2(32000);
x_select_clause => x_lvcExactSelectClause,
x_addr_type =>imp_person_rec.addr_type,
x_city =>imp_person_rec.city,
x_state =>imp_person_rec.state,
x_province =>imp_person_rec.province,
x_county =>imp_person_rec.county,
x_country =>imp_person_rec.country,
x_postcode =>imp_person_rec.postcode
);
IF x_lvcExactSelectClause <> 'PARTIAL_MATCH' THEN
l_cursor_id := dbms_sql.open_cursor;
dbms_sql.parse(l_cursor_id, x_lvcExactSelectClause, dbms_sql.native);
/* Insert into igs_ad_imp_near_match, all the duplicate records */
Igs_Ad_Imp_Near_Mtch_Pkg.insert_row
(x_rowid =>l_rowid,
x_Org_ID => lnOrg_ID,
x_near_mtch_id=>l_pk,
x_interface_id=>p_d_interface_id,
x_person_id=>l_person_id,
x_match_ind=>'E',
x_action=>'D',
x_addr_type=>p_c_addr_type,
x_person_id_type=>p_c_person_id_type,
x_match_set_id=>p_d_match_set_id,
x_mode =>'R',
x_party_SITE_ID => lnParty_Site_ID);
/*If the dynamic Query returns only one row, then Update the igs_ad_interface_table */
IF x_match_cnt = 1 THEN /* Only One Match is Found */
UPDATE igs_ad_interface
SET match_ind = cst_mi_val_12,
person_id = l_person_id
WHERE interface_id = imp_person_rec.interface_id;
UPDATE igs_ad_interface
SET match_ind = cst_mi_val_13,
error_code = cst_err_val_2,
STATUS = cst_stat_val_3
WHERE interface_id = imp_person_rec.interface_id;
x_select_clause => x_lvcPartialSelectClause, -- bug Bug 2381539, it was being taken as exactselect
x_addr_type =>imp_person_rec.addr_type,
x_addr_line_1 =>imp_person_rec.addr_line_1,
x_addr_line_2 =>imp_person_rec.addr_line_2,
x_addr_line_3 =>imp_person_rec.addr_line_3,
x_addr_line_4 =>imp_person_rec.addr_line_4,
x_city =>imp_person_rec.city,
x_state =>imp_person_rec.state,
x_province =>imp_person_rec.province,
x_county =>imp_person_rec.county,
x_country =>imp_person_rec.country,
x_postcode =>imp_person_rec.postcode
);
IF x_lvcPartialSelectClause IS NOT NULL THEN
/* Execute the Partial Select Clause */
l_cursor_id1 := dbms_sql.open_cursor;
dbms_sql.parse(l_cursor_id1, x_lvcPartialSelectClause, dbms_sql.native);
/* Insert into igs_ad_imp_near_match_int, all the duplicate records */
Igs_Ad_Imp_Near_Mtch_Pkg.insert_row
(x_rowid =>l_rowid,
x_Org_ID => lnOrg_ID,
x_near_mtch_id=>l_pk,
x_interface_id=>p_d_interface_id,
x_person_id=>l_person_id,
x_match_ind=>'P',
x_action=>'D',
x_addr_type=>p_c_addr_type,
x_person_id_type=>p_c_person_id_type,
x_match_set_id=>p_d_match_set_id,
x_mode =>'R',
x_party_Site_ID => lnParty_Site_ID);
UPDATE igs_ad_interface
SET match_ind = cst_mi_val_11
WHERE interface_id = imp_person_rec.interface_id;
UPDATE igs_ad_interface
SET match_ind = '14',
ERROR_CODE = 'E003',
STATUS = '3'
WHERE interface_id = imp_person_rec.interface_id;
UPDATE igs_ad_interface
SET status = '3',
match_ind = '24',
error_code = 'E001'
WHERE interface_id = p_d_interface_id;
|| Increased the size of variable x_lvcExactSelectClause and x_lvcPartialSelectClause from 500 to 2000
|| pkpatel 22-JUN-2001 Bug no.2702536
|| Added the parameters p_match_ind, p_person_id
|| pkpatel 4-MAY-2003 Bug 3004858 (PKM Issue to use bind variable)
|| gmaheswa 24-March-2006 Bug 4218763 Modified imp_person_cur to condsider Igs_Pe_Identify_Dups.g_primary_addr_flag = 'Y' condition.
*/
l_prog_label VARCHAR2(100);
SELECT *
FROM igs_ad_imp_matches_ppa_v
WHERE interface_id = cp_d_interface_id
AND (addr_type = cp_c_addr_type OR addr_type IS NULL OR Igs_Pe_Identify_Dups.g_primary_addr_flag = 'Y')
AND (person_id_type = cp_c_person_id_type OR person_id_type IS NULL);
SELECT PS.party_site_id
FROM hz_party_sites PS,hz_party_site_uses PSU
WHERE PS.party_site_id = PSU.party_site_id AND
PS.party_id = cp_person_id AND
PSU.site_use_type = cp_addr_type;
SELECT PS.party_site_id
FROM hz_party_sites PS
WHERE PS.party_id = cp_person_id AND
PS.identifying_address_flag = 'Y';
x_lvcExactSelectClause VARCHAR2(32000);
x_lvcPartialSelectClause VARCHAR2(32000);
x_select_clause =>x_lvcExactSelectClause,
x_addr_type =>imp_person_rec.addr_type,
x_city =>imp_person_rec.city,
x_state =>imp_person_rec.state,
x_province =>imp_person_rec.province,
x_county =>imp_person_rec.county,
x_country =>imp_person_rec.country,
x_postcode =>imp_person_rec.postcode
);
IF x_lvcExactSelectClause <> 'PARTIAL_MATCH' THEN
l_cursor_id := dbms_sql.open_cursor;
dbms_sql.parse(l_cursor_id, x_lvcExactSelectClause, dbms_sql.native);
/* Insert into igs_ad_imp_near_match, all the duplicate records */
Igs_Ad_Imp_Near_Mtch_Pkg.insert_row
(x_rowid =>l_rowid,
x_Org_ID => lnOrg_ID,
x_near_mtch_id=>l_pk,
x_interface_id=>p_d_interface_id,
x_person_id=>l_person_id,
x_match_ind=>'E',
x_action=>'D',
x_addr_type=>p_c_addr_type,
x_person_id_type=>p_c_person_id_type,
x_match_set_id=>p_d_match_set_id,
x_mode =>'R',
x_party_Site_ID => lnParty_Site_ID);
/*If the dynamic Query returns only one row, then Update the igs_ad_interface_table */
IF x_match_cnt = 1 THEN /* Only One Match is Found */
UPDATE igs_ad_interface
SET match_ind = cst_mi_val_12,
person_id = l_person_id
WHERE interface_id = imp_person_rec.interface_id;
UPDATE igs_ad_interface
SET match_ind = '13',
ERROR_CODE = 'E002',
STATUS = '3'
WHERE interface_id = imp_person_rec.interface_id;
x_select_clause =>x_lvcPartialSelectClause, -- bug Bug 2381539, it was being taken as exactselect
x_addr_type =>imp_person_rec.addr_type,
x_addr_line_1 =>imp_person_rec.addr_line_1,
x_addr_line_2 =>imp_person_rec.addr_line_2,
x_addr_line_3 =>imp_person_rec.addr_line_3,
x_addr_line_4 =>imp_person_rec.addr_line_4,
x_city =>imp_person_rec.city,
x_state =>imp_person_rec.state,
x_province =>imp_person_rec.province,
x_county =>imp_person_rec.county,
x_country =>imp_person_rec.country,
x_postcode =>imp_person_rec.postcode
);
IF x_lvcPartialSelectClause IS NOT NULL THEN
/* Exceute the Partial Select Clause */
l_cursor_id1 := dbms_sql.open_cursor;
dbms_sql.parse(l_cursor_id1, x_lvcPartialSelectClause, dbms_sql.native);
Igs_Ad_Imp_Near_Mtch_Pkg.insert_row
(x_rowid =>l_rowid,
x_Org_ID => lnOrg_ID,
x_near_mtch_id=>l_pk,
x_interface_id=>p_d_interface_id,
x_person_id=>l_person_id,
x_match_ind=>'P',
x_action=>'D',
x_addr_type=>p_c_addr_type,
x_person_id_type=>p_c_person_id_type,
x_match_set_id=>p_d_match_set_id,
x_mode =>'R',
x_party_Site_ID => lnParty_Site_ID );
UPDATE igs_ad_interface
SET match_ind = cst_mi_val_11
WHERE interface_id = imp_person_rec.interface_id;
UPDATE igs_ad_interface
SET match_ind = cst_mi_val_14,
ERROR_CODE = cst_err_val_3,
STATUS = cst_stat_val_3
WHERE interface_id = imp_person_rec.interface_id;
UPDATE igs_ad_interface
SET status = cst_stat_val_3,
match_ind = cst_mi_val_24,
error_code = cst_err_val_1
WHERE interface_id = p_d_interface_id;
|| Increased the size of variable x_lvcExactSelectClause and x_lvcPartialSelectClause from 500 to 2000
|| pkpatel 22-JUN-2001 Bug no.2702536
|| Added the parameters p_match_ind, p_person_id
|| pkpatel 22-JUN-2001 Bug no.2702536
|| Added the parameters p_match_ind, p_person_id, p_addr_type and p_person_id_type.
|| Implemented the new record level duplicate check.
|| asbala 23-SEP-2003 Bug 3130316, Duplicate Person Matching Performance Improvements
Calling the logic to DELETE from igs_ad_near_mtch for the interface ids at one shot.
*/
l_prog_label VARCHAR2(100);
SELECT md.exact_include
FROM igs_pe_mtch_set_data md
WHERE md.match_set_id = cp_d_match_set_id
AND md.data_element = cp_lookup_code
AND md.drop_if_null = cp_din;
SELECT 'X'
FROM igs_ad_stat_int
WHERE interface_id = cp_interface_id AND
status = cp_status;
UPDATE igs_ad_interface
SET status='3', ERROR_CODE='E177'
WHERE interface_id = p_interface_id;