[Home] [Help]
76: l_label VARCHAR2(4000);
77: l_debug_str VARCHAR2(4000);
78: l_enable_log VARCHAR2(1);
79: l_request_id NUMBER(10);
80: l_interface_run_id igs_ad_interface_all.interface_run_id%TYPE;
81:
82: --
83: -- dld ref 1. Pick up the records from the tables mentioned below :
84: --
81:
82: --
83: -- dld ref 1. Pick up the records from the tables mentioned below :
84: --
85: CURSOR hnr_cur (cp_interface_run_id igs_ad_interface_all.interface_run_id%TYPE) IS
86: SELECT mi.*, i.person_id
87: FROM igs_ad_acadhonor_int_all mi,igs_ad_interface_all i
88: WHERE mi.interface_run_id = cp_interface_run_id
89: AND mi.interface_id = i.interface_id
83: -- dld ref 1. Pick up the records from the tables mentioned below :
84: --
85: CURSOR hnr_cur (cp_interface_run_id igs_ad_interface_all.interface_run_id%TYPE) IS
86: SELECT mi.*, i.person_id
87: FROM igs_ad_acadhonor_int_all mi,igs_ad_interface_all i
88: WHERE mi.interface_run_id = cp_interface_run_id
89: AND mi.interface_id = i.interface_id
90: AND i.interface_run_id = cp_interface_run_id
91: AND mi.status = '2';
274: match_ind = cst_mi_val_19
275: WHERE mi.interface_run_id = l_interface_run_id
276: AND mi.status = cst_stat_val_2
277: AND EXISTS ( SELECT '1'
278: FROM igs_pe_acad_honors pe, igs_ad_interface_all ii
279: WHERE ii.interface_run_id = l_interface_run_id
280: AND ii.interface_id = mi.interface_id
281: AND ii.person_id = pe.person_id
282: AND pe.acad_honor_type = UPPER(mi.acad_honor_type)
311: WHERE mi.interface_run_id = l_interface_run_id
312: AND mi.match_ind IS NULL
313: AND mi.status = cst_stat_val_2
314: AND EXISTS ( SELECT '1'
315: FROM igs_pe_acad_honors pe, igs_ad_interface_all ii
316: WHERE ii.interface_run_id = l_interface_run_id
317: AND ii.interface_id = mi.interface_id
318: AND ii.person_id = pe.person_id
319: AND pe.acad_honor_type = UPPER(mi.acad_honor_type)
327: UPDATE igs_ad_acadhonor_int_all mi
328: SET status = cst_stat_val_3,
329: match_ind = cst_mi_val_20,
330: DUP_ACAD_HONOR_ID = (SELECT pe.acad_honor_id
331: FROM igs_pe_acad_honors pe, igs_ad_interface_all ii
332: WHERE mi.interface_run_id = l_interface_run_id
333: AND ii.interface_id = mi.interface_id
334: AND ii.person_id = pe.person_id
335: AND pe.acad_honor_type = UPPER(mi.acad_honor_type)
337: WHERE mi.interface_run_id = l_interface_run_id
338: AND mi.match_ind IS NULL
339: AND mi.status = cst_stat_val_2
340: AND EXISTS (SELECT '1'
341: FROM igs_pe_acad_honors pe, igs_ad_interface_all ii
342: WHERE ii.interface_run_id = l_interface_run_id
343: AND ii.interface_id = mi.interface_id
344: AND ii.person_id = pe.person_id
345: AND pe.acad_honor_type = UPPER(mi.acad_honor_type)
601: l_debug_str VARCHAR2(4000);
602: l_enable_log VARCHAR2(1);
603: l_request_id NUMBER(10);
604: l_rule VARCHAR2(1);
605: l_interface_run_id igs_ad_interface_all.interface_run_id%TYPE;
606:
607: CURSOR res_cur(cp_interface_run_id igs_ad_interface_all.interface_run_id%TYPE) IS
608: SELECT mi.*, i.person_id
609: FROM igs_pe_res_dtls_int mi,igs_ad_interface_all i
603: l_request_id NUMBER(10);
604: l_rule VARCHAR2(1);
605: l_interface_run_id igs_ad_interface_all.interface_run_id%TYPE;
606:
607: CURSOR res_cur(cp_interface_run_id igs_ad_interface_all.interface_run_id%TYPE) IS
608: SELECT mi.*, i.person_id
609: FROM igs_pe_res_dtls_int mi,igs_ad_interface_all i
610: WHERE mi.interface_run_id = cp_interface_run_id
611: AND mi.interface_id = i.interface_id
605: l_interface_run_id igs_ad_interface_all.interface_run_id%TYPE;
606:
607: CURSOR res_cur(cp_interface_run_id igs_ad_interface_all.interface_run_id%TYPE) IS
608: SELECT mi.*, i.person_id
609: FROM igs_pe_res_dtls_int mi,igs_ad_interface_all i
610: WHERE mi.interface_run_id = cp_interface_run_id
611: AND mi.interface_id = i.interface_id
612: AND i.interface_run_id = cp_interface_run_id
613: AND mi.status = '2';
948: match_ind = cst_mi_val_19
949: WHERE mi.interface_run_id = l_interface_run_id
950: AND mi.status = cst_stat_val_2
951: AND EXISTS ( SELECT '1'
952: FROM igs_pe_res_dtls_all pe, igs_ad_interface_all ii
953: WHERE ii.interface_run_id = l_interface_run_id
954: AND ii.interface_id = mi.interface_id
955: AND ii.person_id = pe.person_id
956: AND UPPER(mi.residency_class_cd) = pe.residency_class_cd
986: WHERE mi.interface_run_id = l_interface_run_id
987: AND mi.match_ind IS NULL
988: AND mi.status = cst_stat_val_2
989: AND EXISTS ( SELECT '1'
990: FROM igs_pe_res_dtls_all pe, igs_ad_interface_all ii
991: WHERE ii.interface_run_id = l_interface_run_id
992: AND ii.interface_id = mi.interface_id
993: AND ii.person_id = pe.person_id
994: AND pe.residency_class_cd = UPPER(mi.Residency_class_cd)
1027: WHERE mi.interface_run_id = l_interface_run_id
1028: AND mi.match_ind IS NULL
1029: AND mi.status = cst_stat_val_2
1030: AND EXISTS (SELECT '1'
1031: FROM igs_pe_res_dtls_all pe, igs_ad_interface_all ii
1032: WHERE ii.interface_run_id = l_interface_run_id
1033: AND ii.interface_id = mi.interface_id
1034: AND ii.person_id = pe.person_id
1035: AND UPPER(mi.residency_class_cd) = pe.residency_class_cd
1187: l_label VARCHAR2(4000);
1188: l_debug_str VARCHAR2(4000);
1189: l_enable_log VARCHAR2(1);
1190: l_request_id NUMBER(10);
1191: l_interface_run_id igs_ad_interface_all.interface_run_id%TYPE;
1192: l_rule igs_ad_source_cat.discrepancy_rule_cd%TYPE;
1193:
1194: --Pick up the records for processing from the Special Needs Disability Interface Table
1195: CURSOR disability_cur(cp_interface_run_id igs_ad_interface_all.interface_run_id%TYPE) IS
1191: l_interface_run_id igs_ad_interface_all.interface_run_id%TYPE;
1192: l_rule igs_ad_source_cat.discrepancy_rule_cd%TYPE;
1193:
1194: --Pick up the records for processing from the Special Needs Disability Interface Table
1195: CURSOR disability_cur(cp_interface_run_id igs_ad_interface_all.interface_run_id%TYPE) IS
1196: SELECT mi.*,i.person_id
1197: FROM igs_ad_disablty_int_all mi,igs_ad_interface_all i
1198: WHERE mi.interface_run_id = cp_interface_run_id
1199: AND i.interface_run_id = cp_interface_run_id
1193:
1194: --Pick up the records for processing from the Special Needs Disability Interface Table
1195: CURSOR disability_cur(cp_interface_run_id igs_ad_interface_all.interface_run_id%TYPE) IS
1196: SELECT mi.*,i.person_id
1197: FROM igs_ad_disablty_int_all mi,igs_ad_interface_all i
1198: WHERE mi.interface_run_id = cp_interface_run_id
1199: AND i.interface_run_id = cp_interface_run_id
1200: AND mi.interface_id = i.interface_id
1201: AND mi.status = '2';
1200: AND mi.interface_id = i.interface_id
1201: AND mi.status = '2';
1202:
1203: -- Pick up the records processed before the loop from the Disability Interface Table
1204: CURSOR sp_disability_cur(cp_interface_run_id igs_ad_interface_all.interface_run_id%TYPE) IS
1205: SELECT mi.*,i.person_id
1206: FROM igs_ad_disablty_int_all mi,igs_ad_interface_all i
1207: WHERE mi.interface_run_id = cp_interface_run_id
1208: AND i.interface_run_id = cp_interface_run_id
1202:
1203: -- Pick up the records processed before the loop from the Disability Interface Table
1204: CURSOR sp_disability_cur(cp_interface_run_id igs_ad_interface_all.interface_run_id%TYPE) IS
1205: SELECT mi.*,i.person_id
1206: FROM igs_ad_disablty_int_all mi,igs_ad_interface_all i
1207: WHERE mi.interface_run_id = cp_interface_run_id
1208: AND i.interface_run_id = cp_interface_run_id
1209: AND mi.interface_id = i.interface_id
1210: AND mi.status = '1'
1211: AND mi.match_ind IN (cst_mi_val_23,cst_mi_val_19);
1212:
1213: --Pick up the records for processing from the Special Needs Service Interface Table
1214: CURSOR sn_service_cur(cp_interface_disablty_id igs_ad_disablty_int.interface_disablty_id%TYPE,
1215: cp_interface_run_id igs_ad_interface_all.interface_run_id%TYPE ) IS
1216: SELECT ai.*
1217: FROM igs_pe_sn_srvce_int ai,
1218: igs_ad_disablty_int_all ad
1219: WHERE ai.interface_run_id = cp_interface_run_id AND
1223: ai.status = '2';
1224:
1225: --Pick up the records for processing from the Special Needs Contact Interface Table
1226: CURSOR sn_contact_cur(cp_interface_disablty_id igs_ad_disablty_int.interface_disablty_id%TYPE,
1227: cp_interface_run_id igs_ad_interface_all.interface_run_id%TYPE ) IS
1228: SELECT ai.*
1229: FROM igs_pe_sn_conct_int ai,
1230: igs_ad_disablty_int_all ad
1231: WHERE ai.interface_run_id = cp_interface_run_id AND
1313: SELECT birth_date
1314: FROM igs_pe_person_base_v
1315: WHERE person_id = cp_interviewer_id;
1316:
1317: CURSOR birth_dt_cur(p_person_id IGS_AD_INTERFACE.PERSON_ID%TYPE) IS
1318: SELECT birth_date
1319: FROM igs_pe_person_base_v
1320: WHERE person_id= p_person_id;
1321:
1318: SELECT birth_date
1319: FROM igs_pe_person_base_v
1320: WHERE person_id= p_person_id;
1321:
1322: l_birth_date IGS_AD_INTERFACE.BIRTH_DT%TYPE;
1323: l_person_id IGS_AD_INTERFACE.PERSON_ID%TYPE;
1324: l_count NUMBER(5);
1325: l_var VARCHAR2(2);
1326:
1319: FROM igs_pe_person_base_v
1320: WHERE person_id= p_person_id;
1321:
1322: l_birth_date IGS_AD_INTERFACE.BIRTH_DT%TYPE;
1323: l_person_id IGS_AD_INTERFACE.PERSON_ID%TYPE;
1324: l_count NUMBER(5);
1325: l_var VARCHAR2(2);
1326:
1327: validate_disablty_rec validate_disablty_cur%ROWTYPE;
1474:
1475: -- Start of Local Procedure validate_sn_service
1476: --
1477: PROCEDURE validate_sn_service(p_sn_service_rec sn_service_cur%ROWTYPE,
1478: p_person_id igs_ad_interface.person_id%type,
1479: l_success OUT NOCOPY VARCHAR2,
1480: l_error_code OUT NOCOPY VARCHAR2)
1481: IS
1482: /*
1495: SELECT birth_date
1496: FROM igs_pe_person_base_v
1497: WHERE person_id= cp_person_id;
1498:
1499: l_birth_date IGS_AD_INTERFACE.BIRTH_DT%TYPE;
1500: BEGIN
1501: --
1502: -- Special Service code
1503: --
1577: END validate_sn_service;
1578: -- Start of Local Procedure validate_sn_contact
1579: --
1580: PROCEDURE validate_sn_contact(p_sn_contact_rec sn_contact_cur%ROWTYPE,
1581: p_person_id igs_ad_interface.person_id%TYPE,
1582: l_success OUT NOCOPY VARCHAR2,
1583: l_error_code OUT NOCOPY VARCHAR2)
1584: IS
1585: /*
1595: SELECT birth_date
1596: FROM igs_pe_person_base_v
1597: WHERE person_id= cp_person_id;
1598:
1599: l_birth_date IGS_AD_INTERFACE.BIRTH_DT%TYPE;
1600: BEGIN
1601: IF p_sn_contact_rec.contact_date IS NOT NULL THEN
1602: OPEN birth_dt_cur(p_person_id);
1603: FETCH birth_dt_cur INTO l_birth_date;
1908:
1909: --Local Procedure to create a Special Need Service Record
1910: PROCEDURE create_sn_service(p_sn_service_rec sn_service_cur%ROWTYPE,
1911: p_disability_id igs_pe_sn_service.disability_id%TYPE,
1912: p_person_id igs_ad_interface.person_id%TYPE,
1913: p_status OUT NOCOPY VARCHAR2)
1914: AS
1915: /*
1916: || Created By : [email protected]
2014:
2015: --Local Procedure to create a Special Need Contact Record
2016: PROCEDURE create_sn_contact(p_sn_contact_rec sn_contact_cur%ROWTYPE,
2017: p_disability_id igs_pe_sn_contact.disability_id%TYPE,
2018: p_person_id igs_ad_interface.person_id%TYPE,
2019: p_status OUT NOCOPY VARCHAR2)
2020: AS
2021: /*
2022: || Created By : [email protected]
2118:
2119:
2120: PROCEDURE process_sn_service(p_interface_disability_id IN igs_ad_disablty_int.interface_disablty_id%TYPE,
2121: p_disability_id IN igs_pe_sn_service.disability_id%TYPE,
2122: p_person_id IN igs_ad_interface.person_id%TYPE,
2123: p_status OUT NOCOPY VARCHAR2)
2124: AS
2125: --------------------------------------------------------------------------
2126: -- Created By : pkpatel
2397:
2398:
2399: PROCEDURE process_sn_contact(p_interface_disability_id IN igs_ad_disablty_int.interface_disablty_id%TYPE,
2400: p_disability_id IN igs_pe_sn_contact.disability_id%TYPE,
2401: p_person_id IN igs_ad_interface.person_id%TYPE,
2402: p_status OUT NOCOPY VARCHAR2)
2403: AS
2404: --------------------------------------------------------------------------
2405: -- Created By : pkpatel
2689: -- with no discrepancy to status 1 and match_ind 23 case to reduce shared memory.
2690: --------------------------------------------------------------------------
2691:
2692:
2693: l_person_id IGS_AD_INTERFACE.PERSON_ID%TYPE;
2694: l_var VARCHAR2(2);
2695: l_success VARCHAR2(1);
2696: l_error_code VARCHAR2(100);
2697: l_contact_status VARCHAR2(1);
2736: UPDATE igs_ad_disablty_int_all mi
2737: SET status = cst_stat_val_1,
2738: match_ind = cst_mi_val_19,
2739: dup_disability_id = ( SELECT pe.igs_pe_pers_disablty_id
2740: FROM igs_pe_pers_disablty pe, igs_ad_interface_all ii
2741: WHERE ii.interface_id = mi.interface_id AND
2742: pe.disability_type = UPPER(mi.disability_type) AND
2743: ROWNUM = 1 AND
2744: ii.person_id = pe.person_id AND
2745: ((TRUNC(mi.start_date) = pe.start_date) OR (mi.start_date IS NULL AND pe.start_date IS NULL)))
2746: WHERE mi.interface_run_id = l_interface_run_id
2747: AND mi.status = cst_stat_val_2
2748: AND EXISTS ( SELECT '1'
2749: FROM igs_pe_pers_disablty pe, igs_ad_interface_all ii
2750: WHERE pe.disability_type = UPPER(mi.disability_type) AND
2751: ii.interface_id = mi.interface_id AND
2752: ii.person_id = pe.person_id AND
2753: ((TRUNC(mi.start_date) = pe.start_date) OR (mi.start_date IS NULL AND pe.start_date IS NULL))
2780: UPDATE igs_ad_disablty_int_all mi
2781: SET status = cst_stat_val_1,
2782: match_ind = cst_mi_val_23,
2783: dup_disability_id = ( SELECT pe.igs_pe_pers_disablty_id
2784: FROM igs_pe_pers_disablty pe, igs_ad_interface_all ii
2785: WHERE ii.person_id = pe.person_id
2786: AND ii.interface_id = mi.interface_id
2787: AND pe.disability_type = UPPER(mi.disability_type)
2788: AND ((TRUNC(mi.start_date) = pe.start_date) OR (mi.start_date IS NULL AND pe.start_date IS NULL)))
2789: WHERE mi.interface_run_id = l_interface_run_id
2790: AND mi.match_ind IS NULL
2791: AND mi.status = cst_stat_val_2
2792: AND EXISTS ( SELECT 1
2793: FROM igs_pe_pers_disablty pe, igs_AD_interface_all ii
2794: WHERE ii.person_id = pe.person_id
2795: AND ii.interface_id = mi.interface_id
2796: AND UPPER(mi.disability_type) = pe.disability_type
2797: AND NVL(mi.special_allow_cd, -99) = NVL(pe.special_allow_cd, -99) AND
2822: WHERE mi.interface_run_id = l_interface_run_id
2823: AND mi.match_ind IS NULL
2824: AND mi.status = cst_stat_val_2
2825: AND EXISTS (SELECT '1'
2826: FROM igs_pe_pers_disablty pe, igs_Ad_interface_all ii
2827: WHERE pe.disability_type = UPPER(mi.disability_type) AND
2828: ii.person_id = pe.person_id AND
2829: ii.interface_id = mi.interface_id AND
2830: ((TRUNC(mi.start_date) = pe.start_date) OR (mi.start_date IS NULL AND pe.start_date IS NULL))