[Home] [Help]
74: l_enable_log VARCHAR2(1);
75: l_request_id NUMBER;
76:
77:
78: CURSOR c_pc(cp_interface_run_id igs_ad_interface_all.interface_run_id%TYPE) IS
79: SELECT ai.interface_contacts_id,
80: ai.interface_id ai_interface_id,
81: UPPER(ai.contact_point_type) contact_point_type,
82: ai.email_address,
102: ai.program_update_date,
103: i.interface_id i_interface_id,
104: i.person_id i_person_id,
105: i.match_ind i_match_ind
106: FROM igs_ad_contacts_int_all ai, igs_ad_interface_all i
107: WHERE ai.interface_run_id = cp_interface_run_id
108: AND i.interface_id = ai.interface_id
109: AND i.interface_run_id = cp_interface_run_id
110: AND ai.status = '2';
118: l_contact_point_id igs_ad_contacts_int.dup_contact_point_id%TYPE;
119: rec_pc c_pc%ROWTYPE;
120: l_processed_records NUMBER(5) := 0 ;
121: -- local variable to store the value of global variable igs_ad_imp_001.g_interface_run_id
122: l_interface_run_id igs_ad_interface_all.interface_run_id%TYPE;
123: PROCEDURE crt_prsn_contacts(rec_pc c_pc%ROWTYPE ,
124: error_code OUT NOCOPY VARCHAR2,
125: status OUT NOCOPY VARCHAR2) AS
126: l_update_date1 DATE;
471: match_ind = cst_mi_val_19
472: WHERE ai.interface_run_id = l_interface_run_id
473: AND ai.status = cst_stat_val_2
474: AND EXISTS( SELECT '1'
475: FROM hz_contact_points pe, igs_ad_interface_all i
476: WHERE i.interface_run_id = l_interface_run_id
477: AND i.interface_id = ai.interface_id
478: AND pe.owner_table_id = i.person_id
479: AND UPPER(ai.contact_point_type) = pe.contact_point_type
517: WHERE ai.interface_run_id = l_interface_run_id
518: AND ai.match_ind IS NULL
519: AND ai.status = cst_stat_val_2
520: AND EXISTS ( SELECT '1'
521: FROM hz_contact_points pe, igs_ad_interface_all i
522: WHERE i.interface_run_id = l_interface_run_id
523: AND i.interface_id = ai.interface_id
524: AND pe.owner_table_id = i.person_id
525: AND UPPER(ai.contact_point_type) = pe.contact_point_type
544: UPDATE igs_ad_contacts_int_all ai
545: SET status = cst_stat_val_3,
546: match_ind = cst_mi_val_20,
547: dup_contact_point_id = (SELECT contact_point_id
548: FROM hz_contact_points pe, igs_ad_interface_all i
549: WHERE i.interface_run_id = l_interface_run_id
550: AND i.interface_id = ai.interface_id
551: AND rownum = 1
552: AND pe.owner_table_id = i.person_id
563: WHERE ai.interface_run_id = l_interface_run_id
564: AND ai.match_ind IS NULL
565: AND ai.status = cst_stat_val_2
566: AND EXISTS (SELECT '1'
567: FROM hz_contact_points pe, igs_ad_interface_all i
568: WHERE i.interface_run_id = l_interface_run_id
569: AND i.interface_id = ai.interface_id
570: AND pe.owner_table_id = i.person_id
571: AND UPPER(ai.contact_point_type) = pe.contact_point_type
958: l_label VARCHAR2(100);
959: l_debug_str VARCHAR2(2000);
960: l_enable_log VARCHAR2(1);
961: l_request_id NUMBER;
962: l_interface_run_id igs_ad_interface_all.interface_run_id%TYPE;
963:
964: CURSOR person_language_cur(cp_interface_run_id igs_ad_interface_all.interface_run_id%TYPE) IS
965: SELECT hii.*, i.person_id
966: FROM igs_ad_language_int_all hii, igs_ad_interface_all i
960: l_enable_log VARCHAR2(1);
961: l_request_id NUMBER;
962: l_interface_run_id igs_ad_interface_all.interface_run_id%TYPE;
963:
964: CURSOR person_language_cur(cp_interface_run_id igs_ad_interface_all.interface_run_id%TYPE) IS
965: SELECT hii.*, i.person_id
966: FROM igs_ad_language_int_all hii, igs_ad_interface_all i
967: WHERE hii.interface_run_id = cp_interface_run_id
968: AND i.interface_id = hii.interface_id
962: l_interface_run_id igs_ad_interface_all.interface_run_id%TYPE;
963:
964: CURSOR person_language_cur(cp_interface_run_id igs_ad_interface_all.interface_run_id%TYPE) IS
965: SELECT hii.*, i.person_id
966: FROM igs_ad_language_int_all hii, igs_ad_interface_all i
967: WHERE hii.interface_run_id = cp_interface_run_id
968: AND i.interface_id = hii.interface_id
969: AND i.interface_run_id = cp_interface_run_id
970: AND hii.status = '2';
989: FROM fnd_languages_vl
990: WHERE language_code = cp_language_code;
991:
992: l_exists VARCHAR2(1);
993: l_error_code igs_ad_interface_all.ERROR_CODE%TYPE;
994: BEGIN
995: OPEN lang_name_cur(p_person_language_rec.language_name);
996: FETCH lang_name_cur INTO l_exists;
997: IF lang_name_cur%NOTFOUND THEN
1227: match_ind = cst_mi_val_19
1228: WHERE ai.interface_run_id = l_interface_run_id
1229: AND ai.status = cst_stat_val_2
1230: AND EXISTS( SELECT '1'
1231: FROM hz_person_language pe, igs_ad_interface_all i
1232: WHERE i.interface_run_id = l_interface_run_id
1233: AND i.interface_id = ai.interface_id
1234: AND pe.party_id = i.person_id
1235: AND pe.language_name = UPPER(ai.language_name)
1264: WHERE mi.interface_run_id = l_interface_run_id
1265: AND mi.match_ind IS NULL
1266: AND mi.status = cst_stat_val_2
1267: AND EXISTS ( SELECT '1'
1268: FROM hz_person_language pe, igs_ad_interface_all i
1269: WHERE i.interface_run_id = l_interface_run_id
1270: AND i.interface_id = mi.interface_id
1271: AND pe.language_name = UPPER(mi.language_name)
1272: AND pe.party_id = i.person_id
1288: WHERE mi.interface_run_id = l_interface_run_id
1289: AND mi.match_ind IS NULL
1290: AND mi.status = cst_stat_val_2
1291: AND EXISTS (SELECT '1'
1292: FROM hz_person_language pe, igs_ad_interface_all i
1293: WHERE i.interface_run_id = l_interface_run_id
1294: AND i.interface_id = mi.interface_id
1295: AND pe.party_id = i.person_id
1296: AND pe.language_name = UPPER(mi.language_name));
1614: l_debug_str VARCHAR2(4000);
1615: l_enable_log VARCHAR2(1);
1616: l_request_id NUMBER(10);
1617:
1618: l_interface_run_id igs_ad_interface_all.interface_run_id%TYPE;
1619:
1620:
1621: --Pick up the records for processing from the Athletic Details Interface Table
1622: CURSOR ath_dtl_cur(cp_interface_run_id igs_ad_interface_all.interface_run_id%TYPE) IS
1618: l_interface_run_id igs_ad_interface_all.interface_run_id%TYPE;
1619:
1620:
1621: --Pick up the records for processing from the Athletic Details Interface Table
1622: CURSOR ath_dtl_cur(cp_interface_run_id igs_ad_interface_all.interface_run_id%TYPE) IS
1623: SELECT ai.*, i.person_id
1624: FROM igs_pe_ath_dtl_int ai,
1625: igs_ad_interface_all i
1626: WHERE ai.interface_run_id = cp_interface_run_id
1621: --Pick up the records for processing from the Athletic Details Interface Table
1622: CURSOR ath_dtl_cur(cp_interface_run_id igs_ad_interface_all.interface_run_id%TYPE) IS
1623: SELECT ai.*, i.person_id
1624: FROM igs_pe_ath_dtl_int ai,
1625: igs_ad_interface_all i
1626: WHERE ai.interface_run_id = cp_interface_run_id
1627: AND i.interface_id = ai.interface_id
1628: AND i.interface_run_id = cp_interface_run_id
1629: AND ai.status = '2';
1880: match_ind = cst_mi_val_19
1881: WHERE ai.interface_run_id = l_interface_run_id
1882: AND ai.status = cst_stat_val_2
1883: AND EXISTS( SELECT '1'
1884: FROM igs_pe_athletic_dtl pe, igs_ad_interface_all i
1885: WHERE i.interface_id = ai.interface_id
1886: AND i.interface_run_id = l_interface_run_id
1887: AND pe.person_id = NVL(i.person_id, -99)
1888: );
1916: WHERE mi.interface_run_id = l_interface_run_id
1917: AND mi.match_ind IS NULL
1918: AND mi.status = cst_stat_val_2
1919: AND EXISTS ( SELECT '1'
1920: FROM igs_pe_athletic_dtl pe, igs_ad_interface_all i
1921: WHERE i.interface_id = mi.interface_id
1922: AND i.interface_run_id = l_interface_run_id
1923: AND pe.person_id = NVL(i.person_id, -99) AND
1924: NVL(pe.athletic_gpa, -99) = NVL(mi.athletic_gpa,-99) AND
1934: UPDATE igs_pe_ath_dtl_int ai
1935: SET status = cst_stat_val_3,
1936: match_ind = cst_mi_val_18,
1937: dup_athletic_details_id = (SELECT athletic_details_id
1938: FROM igs_pe_athletic_dtl pe, igs_ad_interface_all i
1939: WHERE i.interface_id = ai.interface_id
1940: AND i.interface_run_id = l_interface_run_id
1941: AND pe.person_id = NVL(i.person_id, -99))
1942: WHERE ai.interface_run_id = l_interface_run_id
1942: WHERE ai.interface_run_id = l_interface_run_id
1943: AND ai.match_ind IS NULL
1944: AND ai.status = cst_stat_val_2
1945: AND EXISTS (SELECT '1'
1946: FROM igs_pe_athletic_dtl pe, igs_ad_interface_all i
1947: WHERE i.interface_id = ai.interface_id
1948: AND i.interface_run_id = l_interface_run_id
1949: AND pe.person_id = NVL(i.person_id, -99));
1950: END IF;
2147: l_debug_str VARCHAR2(4000);
2148: l_enable_log VARCHAR2(1);
2149: l_request_id NUMBER(10);
2150:
2151: l_interface_run_id igs_ad_interface_all.interface_run_id%TYPE;
2152:
2153: --Pick up the records for processing from the Athletic Programs Interface Table
2154: CURSOR ath_prg_cur(cp_interface_run_id igs_ad_interface_all.interface_run_id%TYPE) IS
2155: SELECT ai.*, i.person_id
2150:
2151: l_interface_run_id igs_ad_interface_all.interface_run_id%TYPE;
2152:
2153: --Pick up the records for processing from the Athletic Programs Interface Table
2154: CURSOR ath_prg_cur(cp_interface_run_id igs_ad_interface_all.interface_run_id%TYPE) IS
2155: SELECT ai.*, i.person_id
2156: FROM igs_pe_ath_prg_int ai, igs_ad_interface_all i
2157: WHERE ai.interface_run_id = cp_interface_run_id
2158: AND i.interface_id = ai.interface_id
2152:
2153: --Pick up the records for processing from the Athletic Programs Interface Table
2154: CURSOR ath_prg_cur(cp_interface_run_id igs_ad_interface_all.interface_run_id%TYPE) IS
2155: SELECT ai.*, i.person_id
2156: FROM igs_pe_ath_prg_int ai, igs_ad_interface_all i
2157: WHERE ai.interface_run_id = cp_interface_run_id
2158: AND i.interface_id = ai.interface_id
2159: AND i.interface_run_id = cp_interface_run_id
2160: AND ai.status = '2';
2405: match_ind = cst_mi_val_19
2406: WHERE ai.interface_run_id = l_interface_run_id
2407: AND ai.status = cst_stat_val_2
2408: AND EXISTS( SELECT '1'
2409: FROM igs_pe_athletic_prg pe, igs_ad_interface_all i
2410: WHERE i.interface_id = ai.interface_id
2411: AND i.interface_run_id = l_interface_run_id
2412: AND pe.person_id = NVL(i.person_id, -99) AND
2413: pe.athletic_prg_code = UPPER(ai.athletic_prg_code) AND
2444: WHERE ai.interface_run_id = l_interface_run_id
2445: AND ai.match_ind IS NULL
2446: AND ai.status = cst_stat_val_2
2447: AND EXISTS ( SELECT '1'
2448: FROM igs_pe_athletic_prg pe, igs_ad_interface_all i
2449: WHERE i.interface_id = ai.interface_id
2450: AND i.interface_run_id = l_interface_run_id
2451: AND pe.person_id = NVL(i.person_id, -99) AND
2452: pe.athletic_prg_code = UPPER(ai.athletic_prg_code) AND
2463: UPDATE igs_pe_ath_prg_int ai
2464: SET status = cst_stat_val_3,
2465: match_ind = cst_mi_val_20,
2466: dup_athletic_prg_id = (SELECT athletic_prg_id
2467: FROM igs_pe_athletic_prg pe, igs_ad_interface_all i
2468: WHERE i.interface_id = ai.interface_id
2469: AND i.interface_run_id = l_interface_run_id
2470: AND pe.person_id = NVL(i.person_id, -99) AND
2471: pe.athletic_prg_code = UPPER(ai.athletic_prg_code) AND
2473: WHERE ai.interface_run_id = l_interface_run_id
2474: AND ai.match_ind IS NULL
2475: AND ai.status = cst_stat_val_2
2476: AND EXISTS (SELECT '1'
2477: FROM igs_pe_athletic_prg pe, igs_ad_interface_all i
2478: WHERE i.interface_id = ai.interface_id
2479: AND i.interface_run_id = l_interface_run_id
2480: AND pe.person_id = NVL(i.person_id, -99) AND
2481: pe.athletic_prg_code = UPPER(ai.athletic_prg_code) AND