eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.IGS_AD_IMP_026 dependencies on IGS_PE_EIT

Line 2312: CURSOR chk_duplicate(cp_person_id igs_pe_eit.person_id%TYPE,

2308: || updations done outside the main loop
2309: */
2310:
2311:
2312: CURSOR chk_duplicate(cp_person_id igs_pe_eit.person_id%TYPE,
2313: cp_information_type igs_pe_eit.information_type%TYPE,
2314: cp_start_date igs_pe_eit.start_date%TYPE)
2315: IS
2316: SELECT rowid,ei.*

Line 2313: cp_information_type igs_pe_eit.information_type%TYPE,

2309: */
2310:
2311:
2312: CURSOR chk_duplicate(cp_person_id igs_pe_eit.person_id%TYPE,
2313: cp_information_type igs_pe_eit.information_type%TYPE,
2314: cp_start_date igs_pe_eit.start_date%TYPE)
2315: IS
2316: SELECT rowid,ei.*
2317: FROM igs_pe_eit ei

Line 2314: cp_start_date igs_pe_eit.start_date%TYPE)

2310:
2311:
2312: CURSOR chk_duplicate(cp_person_id igs_pe_eit.person_id%TYPE,
2313: cp_information_type igs_pe_eit.information_type%TYPE,
2314: cp_start_date igs_pe_eit.start_date%TYPE)
2315: IS
2316: SELECT rowid,ei.*
2317: FROM igs_pe_eit ei
2318: WHERE person_id = cp_person_id AND

Line 2317: FROM igs_pe_eit ei

2313: cp_information_type igs_pe_eit.information_type%TYPE,
2314: cp_start_date igs_pe_eit.start_date%TYPE)
2315: IS
2316: SELECT rowid,ei.*
2317: FROM igs_pe_eit ei
2318: WHERE person_id = cp_person_id AND
2319: UPPER(information_type) = UPPER(cp_information_type) AND
2320: TRUNC(start_date) = TRUNC(cp_start_date) ;
2321:

Line 2322: CURSOR eit_dtls(cp_ei_status_2 igs_pe_eit_int.status%TYPE,

2318: WHERE person_id = cp_person_id AND
2319: UPPER(information_type) = UPPER(cp_information_type) AND
2320: TRUNC(start_date) = TRUNC(cp_start_date) ;
2321:
2322: CURSOR eit_dtls(cp_ei_status_2 igs_pe_eit_int.status%TYPE,
2323: cp_interface_run_id igs_pe_eit_int.interface_run_id%TYPE,
2324: cp_information_type igs_pe_eit_int.information_type%TYPE) IS
2325:
2326: SELECT ei.*, i.person_id

Line 2323: cp_interface_run_id igs_pe_eit_int.interface_run_id%TYPE,

2319: UPPER(information_type) = UPPER(cp_information_type) AND
2320: TRUNC(start_date) = TRUNC(cp_start_date) ;
2321:
2322: CURSOR eit_dtls(cp_ei_status_2 igs_pe_eit_int.status%TYPE,
2323: cp_interface_run_id igs_pe_eit_int.interface_run_id%TYPE,
2324: cp_information_type igs_pe_eit_int.information_type%TYPE) IS
2325:
2326: SELECT ei.*, i.person_id
2327: FROM igs_pe_eit_int ei,

Line 2324: cp_information_type igs_pe_eit_int.information_type%TYPE) IS

2320: TRUNC(start_date) = TRUNC(cp_start_date) ;
2321:
2322: CURSOR eit_dtls(cp_ei_status_2 igs_pe_eit_int.status%TYPE,
2323: cp_interface_run_id igs_pe_eit_int.interface_run_id%TYPE,
2324: cp_information_type igs_pe_eit_int.information_type%TYPE) IS
2325:
2326: SELECT ei.*, i.person_id
2327: FROM igs_pe_eit_int ei,
2328: igs_ad_interface_all i

Line 2327: FROM igs_pe_eit_int ei,

2323: cp_interface_run_id igs_pe_eit_int.interface_run_id%TYPE,
2324: cp_information_type igs_pe_eit_int.information_type%TYPE) IS
2325:
2326: SELECT ei.*, i.person_id
2327: FROM igs_pe_eit_int ei,
2328: igs_ad_interface_all i
2329: WHERE ei.interface_id = i.interface_id
2330: AND ei.STATUS = cp_ei_status_2
2331: AND ei.interface_run_id = cp_interface_run_id

Line 2343: l_dup_id igs_pe_eit.pe_eit_id%TYPE;

2339: l_error_code VARCHAR2(10);
2340: l_status VARCHAR2(10);
2341: l_dup_var BOOLEAN;
2342: eit_rec eit_dtls%ROWTYPE;
2343: l_dup_id igs_pe_eit.pe_eit_id%TYPE;
2344: l_processed_records NUMBER(5) := 0;
2345: l_eit_rec chk_duplicate%ROWTYPE;
2346:
2347: l_prog_label VARCHAR2(100);

Line 2411: UPDATE igs_pe_eit_int

2407: --ALL VALIDATIONS ARE OK
2408:
2409: l_error := NULL;
2410:
2411: UPDATE igs_pe_eit_int
2412: SET status = '1',
2413: error_code = l_error
2414: WHERE interface_eit_id = eit_rec.interface_eit_id;
2415:

Line 2425: UPDATE igs_pe_eit_int

2421: IF birth_dt_cur%ISOPEN THEN
2422: CLOSE birth_dt_cur;
2423: END IF;
2424:
2425: UPDATE igs_pe_eit_int
2426: SET status = '3',
2427: error_code = l_error
2428: WHERE interface_eit_id = eit_rec.interface_eit_id;
2429:

Line 2431: igs_ad_imp_001.logerrormessage(eit_rec.interface_eit_id,l_error,'IGS_PE_EIT_INT');

2427: error_code = l_error
2428: WHERE interface_eit_id = eit_rec.interface_eit_id;
2429:
2430: IF l_enable_log = 'Y' THEN
2431: igs_ad_imp_001.logerrormessage(eit_rec.interface_eit_id,l_error,'IGS_PE_EIT_INT');
2432: END IF;
2433:
2434: RETURN FALSE;
2435:

Line 2437: UPDATE igs_pe_eit_int

2433:
2434: RETURN FALSE;
2435:
2436: WHEN OTHERS THEN
2437: UPDATE igs_pe_eit_int
2438: SET status = '3',
2439: error_code = l_error
2440: WHERE interface_eit_id = eit_rec.interface_eit_id;
2441:

Line 2465: igs_ad_imp_001.logerrormessage(eit_rec.interface_eit_id,l_error,'IGS_PE_EIT_INT');

2461: NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
2462: END IF;
2463:
2464: IF l_enable_log = 'Y' THEN
2465: igs_ad_imp_001.logerrormessage(eit_rec.interface_eit_id,l_error,'IGS_PE_EIT_INT');
2466: END IF;
2467:
2468: RETURN FALSE;
2469: END Validate_Record;

Line 2479: l_eit_id igs_pe_eit.pe_eit_id%TYPE;

2475: AS
2476:
2477: l_rowid ROWID := NULL;
2478: l_error VARCHAR2(30);
2479: l_eit_id igs_pe_eit.pe_eit_id%TYPE;
2480: l_count NUMBER(5);
2481:
2482: CURSOR date_overlap(cp_eit_rec eit_dtls%ROWTYPE,
2483: cp_end_date VARCHAR2) IS

Line 2484: SELECT count(1) FROM IGS_PE_EIT

2480: l_count NUMBER(5);
2481:
2482: CURSOR date_overlap(cp_eit_rec eit_dtls%ROWTYPE,
2483: cp_end_date VARCHAR2) IS
2484: SELECT count(1) FROM IGS_PE_EIT
2485: WHERE person_id = cp_eit_rec.person_id
2486: AND INFORMATION_TYPE = cp_eit_rec.information_type
2487: 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))
2488: OR

Line 2504: UPDATE igs_pe_eit_int

2500:
2501: IF l_count > 0 THEN
2502:
2503: l_error := 'E571';
2504: UPDATE igs_pe_eit_int
2505: SET status = '3',
2506: error_code = l_error
2507: WHERE interface_eit_id = eit_rec.interface_eit_id;
2508:

Line 2512: igs_ad_imp_001.logerrormessage(eit_rec.interface_eit_id,l_error,'IGS_PE_EIT_INT');

2508:
2509: -- CALL LOG DETAIL
2510:
2511: IF l_enable_log = 'Y' THEN
2512: igs_ad_imp_001.logerrormessage(eit_rec.interface_eit_id,l_error,'IGS_PE_EIT_INT');
2513: END IF;
2514:
2515: ELSE
2516: igs_pe_eit_pkg.insert_row(

Line 2516: igs_pe_eit_pkg.insert_row(

2512: igs_ad_imp_001.logerrormessage(eit_rec.interface_eit_id,l_error,'IGS_PE_EIT_INT');
2513: END IF;
2514:
2515: ELSE
2516: igs_pe_eit_pkg.insert_row(
2517: X_ROWID => l_rowid,
2518: X_PE_EIT_ID => l_eit_id,
2519: X_PERSON_ID => eit_rec.person_id ,
2520: X_INFORMATION_TYPE => eit_rec.information_type ,

Line 2534: UPDATE igs_pe_eit_int

2530:
2531: -- IF SUCCESSFUL INSERT THEN
2532:
2533: l_error := NULL;
2534: UPDATE igs_pe_eit_int
2535: SET status = '1',
2536: error_code = l_error
2537: WHERE interface_eit_id = eit_rec.interface_eit_id;
2538: END IF;

Line 2543: UPDATE igs_pe_eit_int

2539:
2540: EXCEPTION
2541: WHEN OTHERS THEN
2542: l_error := 'E322';
2543: UPDATE igs_pe_eit_int
2544: SET status = '3',
2545: error_code = l_error
2546: WHERE interface_eit_id = eit_rec.interface_eit_id;
2547:

Line 2571: igs_ad_imp_001.logerrormessage(eit_rec.interface_eit_id,l_error,'IGS_PE_EIT_INT');

2567: NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
2568: END IF;
2569:
2570: IF l_enable_log = 'Y' THEN
2571: igs_ad_imp_001.logerrormessage(eit_rec.interface_eit_id,l_error,'IGS_PE_EIT_INT');
2572: END IF;
2573:
2574: END crt_pe_eit;
2575:

Line 2591: SELECT count(1) FROM IGS_PE_EIT

2587: l_count NUMBER(5);
2588:
2589: CURSOR date_overlap(cp_eit_rec eit_dtls%ROWTYPE,
2590: cp_end_date VARCHAR2 ) IS
2591: SELECT count(1) FROM IGS_PE_EIT
2592: WHERE person_id = cp_eit_rec.person_id
2593: AND information_type = cp_eit_rec.information_type
2594: AND start_date <> cp_eit_rec.start_date
2595: 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))

Line 2616: UPDATE igs_pe_eit_int

2612:
2613: p_error_code := l_error;
2614: p_status := '3';
2615:
2616: UPDATE igs_pe_eit_int
2617: SET status = '3',
2618: error_code = l_error
2619: WHERE interface_eit_id = eit_rec.interface_eit_id;
2620:

Line 2624: igs_ad_imp_001.logerrormessage(eit_rec.interface_eit_id,l_error,'IGS_PE_EIT_INT');

2620:
2621: -- CALL LOG DETAIL
2622:
2623: IF l_enable_log = 'Y' THEN
2624: igs_ad_imp_001.logerrormessage(eit_rec.interface_eit_id,l_error,'IGS_PE_EIT_INT');
2625: END IF;
2626:
2627: ELSE
2628:

Line 2629: -- MAKE CALL TO THE TBH i.e IGS_PE_EIT_PKG.UPDATE_ROW

2625: END IF;
2626:
2627: ELSE
2628:
2629: -- MAKE CALL TO THE TBH i.e IGS_PE_EIT_PKG.UPDATE_ROW
2630:
2631: igs_pe_eit_pkg.update_row(
2632: X_ROWID => dup_eit_rec.rowid,
2633: X_PE_EIT_ID => dup_eit_rec.pe_eit_id,

Line 2631: igs_pe_eit_pkg.update_row(

2627: ELSE
2628:
2629: -- MAKE CALL TO THE TBH i.e IGS_PE_EIT_PKG.UPDATE_ROW
2630:
2631: igs_pe_eit_pkg.update_row(
2632: X_ROWID => dup_eit_rec.rowid,
2633: X_PE_EIT_ID => dup_eit_rec.pe_eit_id,
2634: X_PERSON_ID => NVL(eit_rec.person_id,dup_eit_rec.person_id),
2635: X_INFORMATION_TYPE => NVL(eit_rec.information_type,dup_eit_rec.information_type),

Line 2654: UPDATE igs_pe_eit_int

2650: WHEN OTHERS THEN
2651: p_error_code := 'E014';
2652: p_status := '3';
2653:
2654: UPDATE igs_pe_eit_int
2655: SET status = '3',
2656: error_code = 'E014'
2657: WHERE interface_eit_id = eit_rec.interface_eit_id;
2658:

Line 2682: igs_ad_imp_001.logerrormessage(eit_rec.interface_eit_id,'E014','IGS_PE_EIT_INT');

2678: NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
2679: END IF;
2680:
2681: IF l_enable_log = 'Y' THEN
2682: igs_ad_imp_001.logerrormessage(eit_rec.interface_eit_id,'E014','IGS_PE_EIT_INT');
2683: END IF;
2684:
2685: END upd_pe_eit;
2686:

Line 2714: UPDATE igs_pe_eit_int

2710: l_rule :=igs_ad_imp_001.find_source_cat_rule(p_source_type_id,'PERSON_INTERNATIONAL_DETAILS');
2711:
2712: IF l_rule = 'E' OR l_rule = 'I' THEN
2713:
2714: UPDATE igs_pe_eit_int
2715: SET status='3',
2716: error_code = 'E695'
2717: WHERE interface_run_id=l_interface_run_id
2718: AND STATUS = '2'

Line 2724: UPDATE igs_pe_eit_int ei

2720: AND match_ind IS NOT NULL;
2721:
2722: IF l_rule = 'E' THEN
2723:
2724: UPDATE igs_pe_eit_int ei
2725: SET status='1',
2726: match_ind='19'
2727: WHERE interface_run_id=l_interface_run_id
2728: AND STATUS = '2'

Line 2731: FROM igs_pe_eit es,

2727: WHERE interface_run_id=l_interface_run_id
2728: AND STATUS = '2'
2729: AND UPPER(information_type) ='PE_INT_PERM_RES'
2730: AND EXISTS( SELECT es.rowid
2731: FROM igs_pe_eit es,
2732: igs_ad_interface_all ad
2733: WHERE ad.interface_id = ei.interface_id AND
2734: es.person_id = ad.person_id AND
2735: ad.interface_run_id = l_interface_run_id AND

Line 2742: UPDATE igs_pe_eit_int

2738: END IF;
2739:
2740: ELSIF l_rule = 'R' THEN
2741:
2742: UPDATE igs_pe_eit_int
2743: SET status = '1'
2744: WHERE interface_run_id=l_interface_run_id
2745: AND status = '2'
2746: AND UPPER(information_type) ='PE_INT_PERM_RES'

Line 2749: UPDATE igs_pe_eit_int

2745: AND status = '2'
2746: AND UPPER(information_type) ='PE_INT_PERM_RES'
2747: AND match_ind IN ('18','19','22','23');
2748:
2749: UPDATE igs_pe_eit_int
2750: SET status = '3',
2751: error_code = 'E695'
2752: WHERE interface_run_id=l_interface_run_id
2753: AND status = '2'

Line 2757: UPDATE igs_pe_eit_int ei

2753: AND status = '2'
2754: AND UPPER(information_type) ='PE_INT_PERM_RES'
2755: AND ( match_ind IS NOT NULL AND match_ind <> '21' AND match_ind <> '25');
2756:
2757: UPDATE igs_pe_eit_int ei
2758: SET status='1',
2759: match_ind = '23'
2760: WHERE interface_run_id=l_interface_run_id
2761: AND status = '2'

Line 2765: FROM igs_pe_eit es,

2761: AND status = '2'
2762: AND UPPER(information_type) ='PE_INT_PERM_RES'
2763: AND match_ind IS NULL
2764: AND EXISTS( SELECT es.rowid
2765: FROM igs_pe_eit es,
2766: igs_ad_interface_all ad
2767: WHERE ad.interface_id = ei.interface_id AND
2768: es.person_id = ad.person_id AND
2769: ad.interface_run_id = l_interface_run_id AND

Line 2779: UPDATE igs_pe_eit_int ei

2775: ((UPPER(es.pei_information4) = UPPER(ei.pei_information4)) OR ((es.pei_information4 IS NULL) AND (ei.pei_information4 IS NULL))) AND
2776: ((UPPER(es.pei_information5) = UPPER(ei.pei_information5)) OR ((es.pei_information5 IS NULL) AND (ei.pei_information5 IS NULL))) AND
2777: ((TRUNC(es.end_date) = TRUNC(ei.end_date)) OR ((es.end_date IS NULL) AND (ei.end_date IS NULL))));
2778:
2779: UPDATE igs_pe_eit_int ei
2780: SET status = '3',
2781: match_ind='20',
2782: dup_pe_eit_id = (SELECT pe_eit_id
2783: FROM igs_pe_eit es,

Line 2783: FROM igs_pe_eit es,

2779: UPDATE igs_pe_eit_int ei
2780: SET status = '3',
2781: match_ind='20',
2782: dup_pe_eit_id = (SELECT pe_eit_id
2783: FROM igs_pe_eit es,
2784: igs_ad_interface_all ad
2785: WHERE ad.interface_id = ei.interface_id AND
2786: es.person_id = ad.person_id AND
2787: ad.interface_run_id = l_interface_run_id AND

Line 2795: FROM igs_pe_eit es,

2791: status = '2' AND
2792: information_type ='PE_INT_PERM_RES' AND
2793: match_ind IS NULL AND
2794: EXISTS (SELECT es.rowid
2795: FROM igs_pe_eit es,
2796: igs_ad_interface_all ad
2797: WHERE ad.interface_id = ei.interface_id AND
2798: es.person_id = ad.person_id AND
2799: ad.interface_run_id = l_interface_run_id AND

Line 2837: UPDATE igs_pe_eit_int

2833: dup_eit_rec => l_eit_rec,
2834: p_error_code => l_error_code,
2835: p_status => l_status);
2836:
2837: UPDATE igs_pe_eit_int
2838: SET match_ind = '18', -- MATCH OCCURED AND USED IMPORTED VALUES
2839: status = l_status ,
2840: error_code = l_error_code
2841: WHERE interface_eit_id = eit_rec.interface_eit_id;

Line 2867: igs_ad_imp_001.logerrormessage(eit_rec.interface_eit_id,'E014','IGS_PE_EIT_INT');

2863: NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
2864: END IF;
2865:
2866: IF l_enable_log = 'Y' THEN
2867: igs_ad_imp_001.logerrormessage(eit_rec.interface_eit_id,'E014','IGS_PE_EIT_INT');
2868: END IF;
2869:
2870: UPDATE igs_pe_eit_int
2871: SET match_ind = '18',

Line 2870: UPDATE igs_pe_eit_int

2866: IF l_enable_log = 'Y' THEN
2867: igs_ad_imp_001.logerrormessage(eit_rec.interface_eit_id,'E014','IGS_PE_EIT_INT');
2868: END IF;
2869:
2870: UPDATE igs_pe_eit_int
2871: SET match_ind = '18',
2872: status = '3',
2873: error_code = 'E014'
2874: WHERE interface_eit_id = eit_rec.interface_eit_id;

Line 2886: UPDATE igs_pe_eit_int

2882: dup_eit_rec => l_eit_rec,
2883: p_error_code => l_error_code,
2884: p_status => l_status);
2885:
2886: UPDATE igs_pe_eit_int
2887: SET
2888: status = l_status ,
2889: error_code = l_error_code
2890: WHERE

Line 2917: igs_ad_imp_001.logerrormessage(eit_rec.interface_eit_id,'E014','IGS_PE_EIT_INT');

2913: NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
2914: END IF;
2915:
2916: IF l_enable_log = 'Y' THEN
2917: igs_ad_imp_001.logerrormessage(eit_rec.interface_eit_id,'E014','IGS_PE_EIT_INT');
2918: END IF;
2919:
2920: UPDATE igs_pe_eit_int
2921: SET status = '3',

Line 2920: UPDATE igs_pe_eit_int

2916: IF l_enable_log = 'Y' THEN
2917: igs_ad_imp_001.logerrormessage(eit_rec.interface_eit_id,'E014','IGS_PE_EIT_INT');
2918: END IF;
2919:
2920: UPDATE igs_pe_eit_int
2921: SET status = '3',
2922: error_code = 'E014'
2923: WHERE interface_eit_id = eit_rec.interface_eit_id;
2924: END;