[Home] [Help]
60: l_debug_str VARCHAR2(32000);
61: g_person_status VARCHAR2(6) := 'NEW';
62: g_legal_status VARCHAR2(6) := 'NEW';
63: g_nonimg_form_id NUMBER(15);
64: TYPE g_address_rec_type IS TABLE OF IGS_SV_ADDRESSES%ROWTYPE INDEX BY BINARY_INTEGER;
65: TYPE g_edu_rec_type IS TABLE OF IGS_SV_PRGMS_INFO%ROWTYPE INDEX BY BINARY_INTEGER;
66: TYPE g_dependent_rec_type IS TABLE OF IGS_SV_DEPDNT_INFO%ROWTYPE INDEX BY BINARY_INTEGER;
67: TYPE g_convictions_rec_type IS TABLE OF IGS_SV_CONVICTIONS%ROWTYPE INDEX BY BINARY_INTEGER;
68: TYPE g_empl_rec_type IS TABLE OF IGS_SV_EMPL_INFO%ROWTYPE INDEX BY BINARY_INTEGER;
1014: IS
1015: l_valid_addr BOOLEAN;
1016: l_count NUMBER;
1017:
1018: CURSOR c_f_addr_exists(cp_person_id igs_sv_addresses.person_id%TYPE, cp_party_site_id igs_sv_addresses.party_site_id%TYPE)
1019: IS
1020: SELECT 1
1021: FROM igs_pe_addr_v adr,
1022: igs_pe_partysiteuse_v usg
1026: AND adr.party_site_id = cp_party_site_id
1027: AND usg.site_use_type = g_f_addr_usage
1028: AND usg.active = 'A';
1029:
1030: CURSOR c_us_addr_exists(cp_person_id igs_sv_addresses.person_id%TYPE, cp_party_site_id igs_sv_addresses.party_site_id%TYPE)
1031: IS
1032: SELECT 1
1033: FROM igs_pe_addr_v adr,
1034: igs_pe_partysiteuse_v usg
1038: AND adr.party_site_id = cp_party_site_id
1039: AND usg.site_use_type = g_us_addr_usage
1040: AND usg.active = 'A';
1041:
1042: CURSOR c_soa_exists(cp_person_id igs_sv_addresses.person_id%TYPE, cp_party_site_id igs_sv_addresses.party_site_id%TYPE)
1043: IS
1044: SELECT 1
1045: FROM hz_locations loc,
1046: hz_party_sites ps,
2057: Date Created By : Oct 14, 2002
2058:
2059: Purpose : Validate data pertaining to the Foreign
2060: address information on the student.
2061: (IGS_SV_ADDRESSES).
2062:
2063: Remarks : Return result:
2064: 'S' - record found and validated
2065: 'E' - validation error
2239:
2240: Date Created By : Oct 14, 2002
2241:
2242: Purpose : Validate the US Address block information
2243: on the student. (IGS_SV_ADDRESSES)
2244:
2245: Remarks : Return result:
2246: 'S' - record found and validated
2247: 'E' - validation error
2419: Date Created By : Oct 14, 2002
2420:
2421: Purpose : Validate the information pertaining to the
2422: Activity Site Address for the student.
2423: (IGS_SV_ADDRESSES).
2424:
2425: Remarks : Return result:
2426: 'S' - record found and validated
2427: 'E' - validation error
2479: AND usg.site_use_type = g_site_addr_usage
2480: AND usg.status = 'A'
2481: UNION
2482: -- All institution sites assigned as to a person. here location id is selected which
2483: -- will be stored as party_site id in IGS_SV_ADDRESSES table
2484: SELECT 'A' action_type, -- Add site action type
2485: adr.location_id party_site_id, -- this will be passed to party_site_id of IGS_SV_ADDRESSES
2486: ADDRESS1 addr_line_1,
2487: ADDRESS2 addr_line_2,
2481: UNION
2482: -- All institution sites assigned as to a person. here location id is selected which
2483: -- will be stored as party_site id in IGS_SV_ADDRESSES table
2484: SELECT 'A' action_type, -- Add site action type
2485: adr.location_id party_site_id, -- this will be passed to party_site_id of IGS_SV_ADDRESSES
2486: ADDRESS1 addr_line_1,
2487: ADDRESS2 addr_line_2,
2488: ADDRESS3 addr_line_3,
2489: ADDRESS4 addr_line_4,
2533: and usg.party_site_id = ps.party_site_id
2534: and usg.site_use_type = g_site_addr_usage
2535: and usg.party_site_id in
2536: ( select ad.party_site_id
2537: from igs_sv_addresses ad,
2538: igs_sv_persons pr
2539: where ad.person_id = pr.person_id
2540: and ad.batch_id = pr.batch_id
2541: and pr.record_status <> 'E'
2565: and usg.location_id = loc.location_id
2566: and sysdate between usg.start_date and nvl(usg.end_date,sysdate+1)
2567: and usg.location_id in
2568: ( select adr1.location_id
2569: from igs_sv_addresses ad,
2570: HZ_LOCATIONS adr1,
2571: igs_sv_persons pr
2572: where ad.person_id = pr.person_id
2573: and ad.batch_id = pr.batch_id
2571: igs_sv_persons pr
2572: where ad.person_id = pr.person_id
2573: and ad.batch_id = pr.batch_id
2574: and pr.record_status <> 'E'
2575: and adr1.location_id = ad.party_site_id -- in the party_side_id field of igs_sv_addresses location_id is stored for activity sites
2576: and ad.person_id = p_person_rec.person_id and ad.address_type not in ('F','U')
2577: );
2578:
2579: CURSOR c_primary_site IS
6069:
6070: Date Created By : Oct 14, 2002
6071:
6072: Purpose : Get Site of activity information on the student
6073: (IGS_SV_ADDRESSES).
6074:
6075: Remarks :
6076:
6077: Change History
6079: ------------------------------------------------------------------------
6080:
6081: ******************************************************************/
6082: FUNCTION Get_Address_Info (
6083: p_data_rec IN OUT NOCOPY IGS_SV_ADDRESSES%ROWTYPE -- Data record
6084: ) RETURN VARCHAR2 -- 'S' Record found, 'N' - not found. 'U' - Unexpected error
6085: IS
6086:
6087: l_api_name CONSTANT VARCHAR2(25) := 'Get_Address_Info';
6100: stdnt_valid_flag ,
6101: primary_flag ,
6102: activity_site_cd ,
6103: remarks
6104: FROM igs_sv_addresses cr
6105: WHERE cr.person_id = p_data_rec.person_id
6106: AND cr.party_site_id = p_data_rec.party_site_id
6107: AND cr.batch_id IN
6108: ( SELECT max(prs.batch_id)
6105: WHERE cr.person_id = p_data_rec.person_id
6106: AND cr.party_site_id = p_data_rec.party_site_id
6107: AND cr.batch_id IN
6108: ( SELECT max(prs.batch_id)
6109: FROM igs_sv_addresses prs,
6110: igs_sv_persons pr
6111: WHERE prs.person_id = pr.person_id
6112: AND prs.batch_id = pr.batch_id
6113: AND pr.record_status <> 'E'
7036:
7037: Date Created By : Oct 14, 2002
7038:
7039: Purpose : Insert site of activity information.
7040: (IGS_SV_ADDRESSES).
7041:
7042: Remarks :
7043:
7044: Change History
7088: p_data_rec(l_count).person_id,
7089: l_action,
7090: 'SV_SOA',
7091: 'SEND',
7092: 'IGS_SV_ADDRESSES',
7093: p_data_rec(l_count).party_site_id);
7094: ELSE
7095: l_remarks := null;
7096: l_btch_id := p_data_rec(l_count).batch_id;
7099: p_data_rec(l_count).person_id,
7100: g_person_status,
7101: 'SV_US_ADDR',
7102: 'SEND',
7103: 'IGS_SV_ADDRESSES',
7104: p_data_rec(l_count).party_site_id);
7105: ELSE
7106: Insert_Summary_Info(l_btch_id,
7107: p_data_rec(l_count).person_id,
7107: p_data_rec(l_count).person_id,
7108: g_person_status,
7109: 'SV_F_ADDR',
7110: 'SEND',
7111: 'IGS_SV_ADDRESSES',
7112: p_data_rec(l_count).party_site_id);
7113: END IF;
7114: END IF;
7115:
7112: p_data_rec(l_count).party_site_id);
7113: END IF;
7114: END IF;
7115:
7116: INSERT INTO igs_sv_addresses (
7117: batch_id ,
7118: person_id ,
7119: party_site_id ,
7120: print_form ,
8757: l_us_addr_rec g_address_rec_type;
8758: l_count NUMBER(10);
8759: l_cur NUMBER(10);
8760: l_changes_found VARCHAR2(1) := 'N'; --No changes by default
8761: l_prev_us_addr_rec IGS_SV_ADDRESSES%ROWTYPE;
8762:
8763: l_site_addr_rec g_address_rec_type;
8764: l_prev_site_addr_rec IGS_SV_ADDRESSES%ROWTYPE;
8765:
8760: l_changes_found VARCHAR2(1) := 'N'; --No changes by default
8761: l_prev_us_addr_rec IGS_SV_ADDRESSES%ROWTYPE;
8762:
8763: l_site_addr_rec g_address_rec_type;
8764: l_prev_site_addr_rec IGS_SV_ADDRESSES%ROWTYPE;
8765:
8766:
8767:
8768: BEGIN
9155: l_status VARCHAR2(1);
9156:
9157: l_prev_bio_rec IGS_SV_BIO_INFO%ROWTYPE;
9158: l_prev_oth_rec IGS_SV_OTH_INFO%ROWTYPE;
9159: l_prev_f_addr_rec IGS_SV_ADDRESSES%ROWTYPE;
9160: l_prev_us_addr_rec IGS_SV_ADDRESSES%ROWTYPE;
9161:
9162:
9163: BEGIN
9156:
9157: l_prev_bio_rec IGS_SV_BIO_INFO%ROWTYPE;
9158: l_prev_oth_rec IGS_SV_OTH_INFO%ROWTYPE;
9159: l_prev_f_addr_rec IGS_SV_ADDRESSES%ROWTYPE;
9160: l_prev_us_addr_rec IGS_SV_ADDRESSES%ROWTYPE;
9161:
9162:
9163: BEGIN
9164: /* Debug */
11282: fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,NULL);
11283: END IF;
11284: -- Delete from all related tables
11285:
11286: DELETE FROM igs_sv_addresses WHERE batch_id = p_batch_id AND ( person_id = p_person_id OR p_person_id IS NULL );
11287:
11288: DELETE FROM igs_sv_bio_info WHERE batch_id = p_batch_id AND ( person_id = p_person_id OR p_person_id IS NULL ) ;
11289:
11290: DELETE FROM igs_sv_convictions WHERE batch_id = p_batch_id AND ( person_id = p_person_id OR p_person_id IS NULL ) ;