APPS.IGS_AD_IMP_025 dependencies on IGS_PE_PRIVACY_INT

Line 1741: l_error_code igs_pe_privacy_int.error_code%TYPE;

1737: */
1738:
1739: IS
1740: l_rule VARCHAR2(1);
1741: l_error_code igs_pe_privacy_int.error_code%TYPE;
1742: l_status igs_pe_privacy_int.status%TYPE;
1743: l_default_date DATE;
1744: l_processed_records NUMBER(5) := 0;
1745:

Line 1742: l_status igs_pe_privacy_int.status%TYPE;

1738:
1739: IS
1740: l_rule VARCHAR2(1);
1741: l_error_code igs_pe_privacy_int.error_code%TYPE;
1742: l_status igs_pe_privacy_int.status%TYPE;
1743: l_default_date DATE;
1744: l_processed_records NUMBER(5) := 0;
1745:
1746: l_prog_label VARCHAR2(100);

Line 1760: FROM igs_pe_privacy_int ai,

1756: --Pick up the records for processing from the privacy details interface table.
1757: CURSOR privacy_cur(cp_interface_run_id igs_ad_interface_all.interface_run_id%TYPE)
1758: IS
1759: SELECT ai.*, i.person_id
1760: FROM igs_pe_privacy_int ai,
1761: igs_ad_interface_all i
1762: WHERE ai.interface_id = i.interface_id AND
1763: ai.status = '2' AND
1764: i.interface_run_id = cp_interface_run_id AND

Line 1797: l_error_code igs_pe_privacy_int.error_code%TYPE;

1793: */
1794: AS
1795: l_rowid VARCHAR2(25);
1796: l_privacy_level_id igs_pe_priv_level.privacy_level_id%TYPE;
1797: l_error_code igs_pe_privacy_int.error_code%TYPE;
1798: l_org_id NUMBER(15);
1799:
1800: BEGIN
1801: SAVEPOINT before_insert;

Line 1831: UPDATE igs_pe_privacy_int

1827: x_mode => 'R'
1828: );
1829: -- Update interface table for successful insertion.
1830: l_error_code := NULL;
1831: UPDATE igs_pe_privacy_int
1832: SET status = '1',
1833: error_code = l_error_code
1834: WHERE interface_privacy_id = p_priv_rec.interface_privacy_id;
1835: EXCEPTION

Line 1853: UPDATE igs_pe_privacy_int

1849: l_error_code := 'E228';
1850: ELSE
1851: l_error_code := 'E322'; -- Person privacy details record insertion failed.
1852: END IF;
1853: UPDATE igs_pe_privacy_int
1854: SET status = '3',
1855: error_code = l_error_code
1856: WHERE interface_privacy_id = p_priv_rec.interface_privacy_id;
1857:

Line 1915: UPDATE igs_pe_privacy_int

1911: x_mode => 'R'
1912: );
1913: l_error_code := NULL;
1914: l_status := '1';
1915: UPDATE igs_pe_privacy_int
1916: SET status = l_status,
1917: error_code = l_error_code,
1918: match_ind = '18' -- '18' Match occured and used import values
1919: WHERE interface_privacy_id = p_int_rec.interface_privacy_id;

Line 1939: UPDATE igs_pe_privacy_int

1935: l_error_code := 'E228';
1936: ELSE
1937: l_error_code := 'E014';
1938: END IF;
1939: UPDATE igs_pe_privacy_int
1940: SET status = '3',
1941: error_code = l_error_code
1942: WHERE interface_id = p_int_rec.interface_privacy_id;
1943: -- Call Log detail

Line 2010: l_error_code igs_pe_privacy_int.error_code%TYPE;

2006: R.STATUS ='A' AND
2007: R.RELATIONSHIP_CODE = cp_to_whom_code AND
2008: R.SUBJECT_ID = cp_person_id;
2009:
2010: l_error_code igs_pe_privacy_int.error_code%TYPE;
2011: l_rec VARCHAR2(1);
2012: l_exists NUMBER;
2013: chk_data_group_for_ins_rec chk_data_group_for_ins%ROWTYPE;
2014: chk_data_group_for_upd_rec chk_data_group_for_upd%ROWTYPE;

Line 2075: UPDATE igs_pe_privacy_int

2071: RETURN TRUE;
2072:
2073: EXCEPTION
2074: WHEN OTHERS THEN
2075: UPDATE igs_pe_privacy_int
2076: SET status = '3',
2077: error_code = l_error_code
2078: WHERE interface_privacy_id = p_priv_rec.interface_privacy_id;
2079:

Line 2117: UPDATE igs_pe_privacy_int phi

2113:
2114: -- 1. If the rule is E or I, and the match ind column is not null, update all the records to status 3 as they are invalids.
2115:
2116: IF ((l_rule='E') OR (l_rule='I')) THEN
2117: UPDATE igs_pe_privacy_int phi
2118: SET status = '3',
2119: error_code = 'E695'
2120: WHERE phi.status = '2' AND
2121: phi.interface_run_id = l_interface_run_id AND

Line 2128: UPDATE igs_pe_privacy_int phi

2124:
2125: -- 2 . If rule is E and the match ind is null, we update the interface table for all duplicate records with status 1 and match ind 19.
2126:
2127: IF (l_rule = 'E') THEN
2128: UPDATE igs_pe_privacy_int phi
2129: SET status = '1',
2130: match_ind = '19'
2131: WHERE phi.status = '2' AND
2132: phi.interface_run_id = l_interface_run_id AND

Line 2146: UPDATE igs_pe_privacy_int phi

2142:
2143: -- 3. If rule is R and the record status is 18,19,22,23 these records have been processed, but didn't get updated. Update them to 1
2144:
2145: IF (l_rule='R') THEN
2146: UPDATE igs_pe_privacy_int phi
2147: SET status = '1'
2148: WHERE phi.status = '2' AND
2149: phi.interface_run_id = l_interface_run_id AND
2150: phi.match_ind IN ('18','19','22','23');

Line 2156: UPDATE igs_pe_privacy_int phi

2152:
2153: -- 4. If rule is R and the match ind is not null and is neither 21 nor 25, update it to errored record.
2154:
2155: IF (l_rule = 'R') THEN
2156: UPDATE igs_pe_privacy_int phi
2157: SET status = '3', error_code = 'E695'
2158: WHERE phi.status = '2' AND
2159: phi.interface_run_id = l_interface_run_id AND
2160: (phi.match_ind IS NOT NULL AND phi.match_ind NOT IN ('21','25'));

Line 2166: UPDATE igs_pe_privacy_int phi

2162:
2163: -- 5. If rule = 'R' and there is no discprepency in duplicate records, update them to status 1 and match ind 23.
2164:
2165: IF (l_rule ='R') THEN
2166: UPDATE igs_pe_privacy_int phi
2167: SET status = '1', match_ind = '23'
2168: WHERE phi.status = '2' AND
2169: phi.interface_run_id = l_interface_run_id AND
2170: phi.match_ind IS NULL AND

Line 2189: UPDATE igs_pe_privacy_int phi

2185: -- 6. If rule is R and there are still some records, they are the ones for which there is some discrepency existing. Update them to status 3
2186: -- and value from the OSS table.
2187:
2188: IF (l_rule ='R') THEN
2189: UPDATE igs_pe_privacy_int phi
2190: SET status = 3,
2191: match_ind = 20,
2192: dup_privacy_level_id = (SELECT pi.privacy_level_id
2193: FROM igs_pe_priv_level pi, igs_ad_interface_all ai