[Home] [Help]
138: lk.security_group_id = cp_security_group_id;
139:
140: CURSOR Rel_person_exists_cur(p_rel_person_id IGS_AD_RELATIONS_INT.REL_PERSON_ID%TYPE) IS
141: SELECT 'Y'
142: FROM HZ_PARTIES
143: WHERE party_id = p_rel_person_id ;
144:
145: person_rec HZ_PARTY_V2PUB.PERSON_REC_TYPE;
146:
231: END IF;
232: END IF;
233:
234: -- If the Rel Person ID passed is valid only the relation is created and Person is not created
235: -- Cursor to check whether the Rel Person ID passed is a valid Party ID in HZ_PARTIES
236: OPEN Rel_person_exists_cur(NVL(p_relations_rec.rel_person_id,0));
237: FETCH Rel_person_exists_cur INTO l_rel_person_exists;
238: CLOSE Rel_person_exists_cur;
239: IF l_rel_person_exists <> 'Y' or l_rel_person_exists IS NULL THEN
351: X_EMAIL_ADDR => NULL,
352: X_LEVEL_OF_QUAL_ID => NULL,
353: X_MILITARY_SERVICE_REG =>'N',
354: X_VETERAN => 'VETERAN_NOT', -- ssawhney now a lookup code 2203778
355: X_HZ_PARTIES_OVN => l_object_version_number,
356: X_ATTRIBUTE_CATEGORY => NULL,
357: X_ATTRIBUTE1 => NULL,
358: X_ATTRIBUTE2 => NULL,
359: X_ATTRIBUTE3 => NULL,
1632: phone_extension
1633: FROM hz_contact_points
1634: WHERE owner_table_id = p_rel_person_id
1635: AND contact_point_type = p_contact_point_type
1636: AND owner_table_name = 'HZ_PARTIES'
1637: AND (( nvl(email_format,'NulL') = nvl(p_email_format,'NulL') AND
1638: UPPER(nvl(email_address,'NulL')) = UPPER(nvl(p_email_address,'NulL')) AND
1639: contact_point_type = 'EMAIL' )OR
1640: ( nvl(phone_line_type,'NulL') = nvl(p_phone_line_type,'NulL') AND
1717: --5. if the validation is successful then, insert this record into the table hz_contact_points.
1718: --items in the record p_contact_point_rec
1719: contact_point_rec.contact_point_type := p_rel_con_rec.contact_point_type;
1720: -- contact_point_rec.status := p_rel_con_rec.status; ssomani, these two statuses are different 15 March 2001
1721: contact_point_rec.owner_table_name := 'HZ_PARTIES';
1722: contact_point_rec.primary_flag := NVL(p_rel_con_rec.primary_flag,'N');
1723: contact_point_rec.content_source_type := 'USER_ENTERED';
1724: contact_point_rec.created_by_module := 'IGS';
1725: contact_point_rec.owner_table_id := p_rel_person_id ; --ssomani, added this 15 March 2001
1963: AND EXISTS ( SELECT '1'
1964: FROM hz_contact_points pe
1965: WHERE pe.owner_table_id = p_rel_person_id
1966: AND pe.contact_point_type = UPPER(mi.contact_point_type)
1967: AND pe.owner_table_name = 'HZ_PARTIES'
1968: AND (pe.contact_point_type = 'EMAIL' AND
1969: (NVL(pe.email_format,'NulL') = NVL(UPPER(mi.email_format),'NulL') AND
1970: UPPER(NVL(pe.email_address,'NulL')) = UPPER(NVL(mi.email_addrress,'NulL'))
1971: ) OR
2029: DUP_CONTACT_POINT_ID = (SELECT pe.contact_point_id
2030: FROM hz_contact_points pe
2031: WHERE pe.owner_table_id = p_rel_person_id
2032: AND pe.contact_point_type = UPPER(mi.contact_point_type)
2033: AND pe.owner_table_name = 'HZ_PARTIES'
2034: AND ((pe.contact_point_type = 'EMAIL' AND
2035: NVL(pe.email_format,'NulL') = NVL(UPPER(mi.email_format),'NulL') AND
2036: UPPER(NVL(pe.email_address,'NulL')) = UPPER(NVL(mi.email_addrress,'NulL'))
2037: )
2049: AND EXISTS (SELECT '1'
2050: FROM hz_contact_points pe
2051: WHERE pe.owner_table_id = p_rel_person_id
2052: AND pe.contact_point_type = UPPER(mi.contact_point_type)
2053: AND pe.owner_table_name = 'HZ_PARTIES'
2054: AND (pe.contact_point_type = 'EMAIL' AND
2055: (NVL(pe.email_format,'NulL') = NVL(UPPER(mi.email_format),'NulL') AND
2056: UPPER(NVL(pe.email_address,'NulL')) = UPPER(NVL(mi.email_addrress,'NulL'))
2057: )
2092: --items in the record p_contact_point_rec
2093: contact_point_rec.contact_point_type := relcon_rec.contact_point_type;
2094: contact_point_rec.contact_point_id := rel_cont_del_dup_rec.contact_point_id;
2095: -- contact_point_rec.status := relcon_rec.status; --ssomani, unrelated statuses 15 March 2001
2096: contact_point_rec.owner_table_name := 'HZ_PARTIES';
2097: contact_point_rec.primary_flag :=NVL((NVL(relcon_rec.primary_flag,rel_cont_del_dup_rec.primary_flag)),FND_API.G_MISS_CHAR);
2098:
2099: -- contact_point_rec.content_source_type := 'USER_ENTERED';
2100: -- contact_point_rec.created_by_module := 'IGS';
2184: --items in the record p_contact_point_rec
2185: contact_point_rec.contact_point_type := relcon_rec.contact_point_type;
2186: contact_point_rec.contact_point_id := rel_cont_del_dup_rec.contact_point_id;
2187: -- contact_point_rec.status := relcon_rec.status; --ssomani, unrelated statuses 15 March 2001
2188: contact_point_rec.owner_table_name := 'HZ_PARTIES';
2189: contact_point_rec.primary_flag := NVL((NVL(relcon_rec.primary_flag,rel_cont_del_dup_rec.primary_flag)),FND_API.G_MISS_CHAR);
2190:
2191: -- contact_point_rec.content_source_type := 'USER_ENTERED';
2192: -- contact_point_rec.created_by_module := 'IGS';
2356: SELECT 'X' a
2357: FROM Igs_Ad_Relacad_Int_all a
2358: WHERE Interface_Relacad_Id = cp_Interface_Relacad_Id
2359: AND NVL(p_start_date,p_end_date) IS NOT NULL
2360: AND EXISTS (SELECT 1 FROM hz_Education h1, hz_parties h2
2361: WHERE h1.party_id = P_Rel_Person_ID
2362: AND h2.party_number = p_institution_code
2363: AND h2.party_id = h1.school_party_id
2364: AND h1.start_date_attended IS NULL
2363: AND h2.party_id = h1.school_party_id
2364: AND h1.start_date_attended IS NULL
2365: AND h1.last_date_attended IS NULL
2366: )
2367: AND NOT EXISTS ( SELECT 1 FROM hz_Education h1, hz_parties h2
2368: WHERE h1.party_id = p_rel_person_id
2369: AND h2.party_number = p_institution_code
2370: AND h2.party_id = h1.school_party_id
2371: AND NVL(h1.start_date_attended,
2380: p_end_Date Igs_Ad_Relacad_Int.end_Date%TYPE
2381: ) IS
2382:
2383: SELECT 1 a
2384: FROM hz_Education h1, hz_parties h2
2385: WHERE h1.party_id = P_Rel_Person_ID
2386: AND h2.party_number = p_institution_code
2387: AND h2.party_id = h1.school_party_id
2388: AND NVL(h1.start_date_attended,
2427: -- Validate the INSTITUTION_CD.
2428: CURSOR Institution_C(cp_Institution_Cd Igs_Ad_Relacad_Int.Institution_Code%TYPE,
2429: cp_inst_status Igs_Or_Inst_Stat.S_Institution_Status%TYPE) IS
2430: SELECT 1
2431: FROM igs_pe_hz_parties pzp, hz_parties hp, Igs_Or_Inst_Stat stat
2432: WHERE pzp.party_id = hp.party_id AND
2433: hp.party_number = cp_institution_cd AND
2434: pzp.OI_INSTITUTION_STATUS = stat.Institution_Status AND
2435: pzp.inst_org_ind = 'I' AND
2448:
2449: l_Course_Rec c_degree_code%ROWTYPE;
2450:
2451:
2452: CURSOR birth_date_cur(cp_person_id hz_parties.party_id%TYPE)
2453: IS
2454: SELECT birth_date
2455: FROM igs_pe_person_base_v
2456: WHERE person_id = cp_person_id;
3211: END crt_rel_acad_his;
3212:
3213: PROCEDURE validate_relns_emp_dtls(
3214: P_RELEMP_REC IN IGS_AD_RELEMP_INT_ALL%ROWTYPE,
3215: p_rel_person_id IN hz_parties.party_id%TYPE,
3216: P_EMPLOYER_PARTY_ID IN OUT NOCOPY NUMBER,
3217: P_ERROR_CODE OUT NOCOPY VARCHAR2);
3218:
3219: PROCEDURE Crt_Relns_Emp_Dtls(
3354:
3355: END Crt_Relns_Emp_Dtls;
3356:
3357: PROCEDURE validate_relns_emp_dtls(P_RELEMP_REC IN IGS_AD_RELEMP_INT_ALL%ROWTYPE,
3358: P_REL_PERSON_ID IN hz_parties.party_id%TYPE,
3359: P_EMPLOYER_PARTY_ID IN OUT NOCOPY NUMBER,
3360: P_ERROR_CODE OUT NOCOPY VARCHAR2) AS
3361:
3362: CURSOR Validate_Occup_Title( p_occupational_title_code igs_ps_dic_occ_titls.occupational_title_code%TYPE ) IS
3365: WHERE occupational_title_code = p_occupational_title_code;
3366:
3367: CURSOR employer_party_number_cur(cp_employer_party_number igs_ad_emp_int_all.employer_party_number%TYPE) IS
3368: SELECT party_id
3369: FROM HZ_PARTIES
3370: WHERE party_type = 'ORGANIZATION' AND
3371: party_number = cp_employer_party_number AND
3372: status <> 'M';
3373:
4255: X_PRIMARY_CONTACT_ID => NULL,
4256: X_PERSONAL_INCOME => NULL,
4257: X_HEAD_OF_HOUSEHOLD_FLAG => NULL,
4258: X_CONTENT_SOURCE_NUMBER => NULL,
4259: X_HZ_PARTIES_OVN => l_object_version_number,
4260: X_attribute_category => stat_rec.attribute_category,
4261: X_attribute1 => stat_rec.attribute1 ,
4262: X_attribute2 => stat_rec.attribute2 ,
4263: X_attribute3 => stat_rec.attribute3 ,
4439: match_ind = '19'
4440: WHERE mi.interface_run_id = l_interface_run_id
4441: AND mi.status = '2'
4442: AND EXISTS ( SELECT '1'
4443: FROM hz_parties hp, igs_ad_interface_all ii
4444: WHERE ii.interface_run_id = l_interface_run_id
4445: AND ii.interface_id = mi.interface_id
4446: AND ii.person_id = hp.party_id);
4447: END IF;
4521: WHERE mi.interface_run_id = l_interface_run_id
4522: AND mi.match_ind IS NULL
4523: AND mi.status = '2'
4524: AND EXISTS (SELECT '1'
4525: FROM hz_parties hp,
4526: igs_ad_interface_all ii
4527: WHERE ii.interface_run_id = l_interface_run_id
4528: AND ii.interface_id = mi.interface_id
4529: AND ii.person_id = hp.party_id);
4612: p.object_version_number object_version_number
4613: FROM
4614: hz_person_profiles pp,
4615: igs_pe_stat_details sd,
4616: hz_parties p
4617: WHERE
4618: sd.person_id(+) = p.party_id AND
4619: pp.party_id = p.party_id AND
4620: SYSDATE BETWEEN pp.effective_start_date AND NVL(pp.effective_end_date, SYSDATE) AND
4677: x_personal_income => chk_dup_rec.personal_income,
4678: x_head_of_household_flag => chk_dup_rec.head_of_household_flag,
4679: x_content_source_number => chk_dup_rec.content_source_number,
4680: x_attribute_category => NVL(stat_rec.attribute_category,chk_dup_rec.attribute_category),
4681: x_hz_parties_ovn => chk_dup_rec.object_version_number,
4682: x_attribute1 => NVL(stat_rec.attribute1,chk_dup_rec.attribute1),
4683: x_attribute2 => NVL(stat_rec.attribute2,chk_dup_rec.attribute2),
4684: x_attribute3 => NVL(stat_rec.attribute3,chk_dup_rec.attribute3),
4685: x_attribute4 => NVL(stat_rec.attribute4,chk_dup_rec.attribute4),
4857: x_primary_contact_id => chk_dup_rec.primary_contact_id,
4858: x_personal_income => chk_dup_rec.personal_income,
4859: x_head_of_household_flag => chk_dup_rec.head_of_household_flag,
4860: x_content_source_number => chk_dup_rec.content_source_number,
4861: x_hz_parties_ovn => chk_dup_rec.object_version_number,
4862: x_attribute_category => NVL(stat_rec.attribute_category,chk_dup_rec.attribute_category),
4863: x_attribute1 => NVL(stat_rec.attribute1,chk_dup_rec.attribute1),
4864: x_attribute2 => NVL(stat_rec.attribute2,chk_dup_rec.attribute2),
4865: x_attribute3 => NVL(stat_rec.attribute3,chk_dup_rec.attribute3),