1 PACKAGE BODY PER_PL_CON_INFO AS
2 /* $Header: peplcrlp.pkb 120.1 2006/09/13 10:58:39 mseshadr noship $ */
3
4 ---------------------------------------------------------------------------------------------------
5 -- CREATE_PL_CON_REL --
6 ---------------------------------------------------------------------------------------------------
7 PROCEDURE CREATE_PL_CON_REL(P_DATE_START DATE,
8 P_DATE_END DATE,
9 P_CONTACT_PERSON_ID NUMBER,
10 P_PERSON_ID NUMBER,
11 P_CONTACT_TYPE VARCHAR2,
12 P_DATE_OF_BIRTH DATE) IS
13 cursor cur_crl is select 1 from per_contact_relationships
14 where contact_person_id = p_contact_person_id
15 and person_id = p_person_id
16 and contact_type in ('01','11','21','30','31','32','33','40','41','50','60')
17 and P_CONTACT_TYPE in ('01','11','21','30','31','32','33','40','41','50','60')
18 and (date_start >= p_date_start
19 OR nvl(date_end,hr_general.end_of_time) <= nvl(p_date_end,hr_general.end_of_time));
20
21 cursor csr_dob_contact is
22 select date_of_birth from per_all_people_f where person_id = P_CONTACT_PERSON_ID;
23
24 nDummy_val number;
25 l_dob_contact per_all_people_f.date_of_birth%TYPE;
26 l_proc varchar2(17);
27 BEGIN
28 l_proc:='CREATE_PL_CON_REL';
29 /* Added for GSI Bug 5472781 */
30 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'PL') THEN
31 hr_utility.set_location('Leaving : '||l_proc,10);
32 return;
33 END IF;
34 open cur_crl;
35 fetch cur_crl into nDummy_val;
36 IF cur_crl%found then
37 Close cur_crl;
38 hr_utility.set_message(800,'HR_CON_REL_OVERLAP_PL');
39 hr_utility.raise_error;
40 End If;
41 Close cur_crl;
42
43 /* This contact is not already a part of the system */
44
45 if p_contact_person_id is null then
46 if P_DATE_OF_BIRTH is null and P_CONTACT_TYPE = '11' then
47 hr_utility.set_message(800,'HR_CON_DOB_REQD_PL');
48 hr_utility.raise_error;
49 end if;
50
51 else
52
53 /* This contact is already a part of the system. So person_type_id is not null */
54
55 if P_CONTACT_TYPE = '11' then
56 if P_DATE_OF_BIRTH is null then
57 hr_utility.set_message(800,'HR_CON_DOB_REQD_PL');
58 hr_utility.raise_error;
59 end if;
60
61 if P_DATE_OF_BIRTH = hr_api.g_date then
62 open csr_dob_contact;
63 fetch csr_dob_contact into l_dob_contact;
64 close csr_dob_contact;
65
66 if l_dob_contact is null then
67 hr_utility.set_message(800,'HR_CON_DOB_REQD_PL');
68 hr_utility.raise_error;
69 end if;
70
71 end if;
72 end if;
73 end if; -- End if of p_contact_person_id is null
74
75
76
77 END CREATE_PL_CON_REL;
78 ---------------------------------------------------------------------------------------------------
79 -- UPDATE_PL_CON_REL --
80 ---------------------------------------------------------------------------------------------------
81
82 PROCEDURE UPDATE_PL_CON_REL(P_CONTACT_RELATIONSHIP_ID NUMBER,
83 P_DATE_START DATE,
84 P_DATE_END DATE,
85 P_CONTACT_TYPE VARCHAR2) IS
86
87 cursor cur_crl is select 1 from per_contact_relationships
88 where contact_person_id in
89 (select contact_person_id from per_contact_relationships where contact_relationship_id = p_contact_relationship_id)
90 and person_id in (select person_id from per_contact_relationships where contact_relationship_id = p_contact_relationship_id)
91 and contact_relationship_id <> p_contact_relationship_id
92 and contact_type in ('01','11','21','30','31','32','33','40','41','50','60')
93 and P_CONTACT_TYPE in ('01','11','21','30','31','32','33','40','41','50','60')
94 and (date_start >= p_date_start
95 OR nvl(date_end,hr_general.end_of_time) <= nvl(p_date_end,hr_general.end_of_time));
96
97 cursor csr_dob_contact is
98 select date_of_birth from per_all_people_f where person_id in (select contact_person_id from
99 per_contact_relationships where contact_relationship_id = P_CONTACT_RELATIONSHIP_ID);
100
101 l_contact_dob per_all_people_f.date_of_birth%TYPE;
102 nDummy_val number;
103 l_proc varchar2(17);
104 BEGIN
105 l_proc:='UPDATE_PL_CON_REL';
106 /* Added for GSI Bug 5472781 */
107 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'PL') THEN
108 hr_utility.set_location('Leaving : '||l_proc,10);
109 return;
110 END IF;
111
112 open cur_crl;
113 fetch cur_crl into nDummy_val;
114 IF cur_crl%found then
115 Close cur_crl;
116 hr_utility.set_message(800,'HR_CON_REL_OVERLAP_PL');
117 hr_utility.raise_error;
118 End If;
119 Close cur_crl;
120
121 if P_CONTACT_TYPE = '11' then
122 open csr_dob_contact;
123 fetch csr_dob_contact into l_contact_dob;
124 close csr_dob_contact;
125
126 if l_contact_dob is null then
127 hr_utility.set_message(800,'HR_CON_DOB_REQD_PL');
128 hr_utility.raise_error;
129 end if;
130
131 end if;
132
133 END UPDATE_PL_CON_REL;
134
135 END PER_PL_CON_INFO;
[Home] [Help]