[Home] [Help]
PACKAGE BODY: APPS.PER_FR_VALIDATE_DELETE_PKG
Source
1 PACKAGE BODY PER_FR_VALIDATE_DELETE_PKG AS
2 /* $Header: pefrorvd.pkb 115.4 2003/12/18 06:52:55 hwinsor noship $ */
3
4 procedure validate_delete(x_organization_id in number)
5 is
6
7 cursor csr_estab_comp is
8 SELECT '1'
9 FROM HR_ORGANIZATION_INFORMATION
10 WHERE ORG_INFORMATION_CONTEXT = 'FR_ESTAB_INFO'
11 AND TO_NUMBER(ORG_INFORMATION1) = x_organization_id;
12
13 cursor csr_estab_urssaf is
14
15 SELECT '1'
16 FROM HR_ORGANIZATION_INFORMATION
17 WHERE ORG_INFORMATION_CONTEXT = 'FR_ESTAB_URSSAF'
18 AND TO_NUMBER(ORG_INFORMATION1) = x_organization_id;
19
20 cursor csr_estab_assedic is
21 SELECT '1'
22 FROM HR_ORGANIZATION_INFORMATION
23 WHERE ORG_INFORMATION_CONTEXT = 'FR_ESTAB_ASSEDIC'
24 AND TO_NUMBER(ORG_INFORMATION1) = x_organization_id;
25
26 cursor csr_estab_pens is
27 SELECT '1'
28 FROM HR_ORGANIZATION_INFORMATION
29 WHERE ORG_INFORMATION_CONTEXT = 'FR_ESTAB_PE_PRVS'
30 AND TO_NUMBER(ORG_INFORMATION1) = x_organization_id;
31
32 cursor csr_estab_insu is
33 SELECT '1'
34 FROM HR_ORGANIZATION_INFORMATION
35 WHERE ORG_INFORMATION_CONTEXT = 'FR_ESTAB_INS_PRV'
36 AND TO_NUMBER(ORG_INFORMATION1) = x_organization_id;
37
38 cursor csr_comp_urssaf is
39 SELECT '1'
40 FROM HR_ORGANIZATION_INFORMATION
41 WHERE ORG_INFORMATION_CONTEXT = 'FR_COMP_INFO'
42 AND x_organization_id = to_number(ORG_INFORMATION3);
43
44 cursor csr_comp_dads_estab is
45 select 1
46 from hr_organization_information oi, hr_all_organization_units ou
47 where oi.organization_id = ou.organization_id
48 and oi.organization_id = x_organization_id
49 and oi.org_information_context = 'FR_ESTAB_INFO'
50 and to_number (oi.org_information1) =
51 (select ou.organization_id
52 from hr_all_organization_units ou, hr_organization_information oi
53 where to_number (oi.org_information4) = x_organization_id
54 and oi.organization_id = ou.organization_id
55 and oi.org_information_context = 'FR_COMP_INFO');
56
57 cursor csr_comp_heaq_estab is
58 select 1
59 from hr_organization_information oi, hr_all_organization_units ou
60 where oi.organization_id = ou.organization_id
61 and oi.organization_id = x_organization_id
62 and oi.org_information_context = 'FR_ESTAB_INFO'
63 and to_number (oi.org_information1) =
64 (select ou.organization_id
65 from hr_all_organization_units ou, hr_organization_information oi
66 where to_number (oi.org_information5) = x_organization_id
67 and oi.organization_id = ou.organization_id
68 and oi.org_information_context = 'FR_COMP_INFO');
69
70 cursor csr_comp_pens is
71 SELECT '1'
72 FROM HR_ORGANIZATION_INFORMATION
73 WHERE ORG_INFORMATION_CONTEXT (+) = 'FR_COMP_PE_PRVS'
74 AND x_organization_id = to_number(ORG_INFORMATION1);
75
76 cursor csr_comp_insu is
77 SELECT '1'
78 FROM HR_ORGANIZATION_INFORMATION
79 WHERE ORG_INFORMATION_CONTEXT (+) = 'FR_COMP_INS_PRVS'
80 AND x_organization_id = to_number(ORG_INFORMATION1);
81
82
83 cursor csr_employer is
84 select '1'
85 from per_collective_agreements_v
86 where employer_organization_id = x_organization_id;
87
88 cursor csr_barg_units is
89 select '1'
90 from per_collective_agreements_v
91 where bargaining_organization_id = x_organization_id;
92
93 cursor csr_cpam is
94 select '1'
95 from per_all_people_f pap
96 where pap.per_information_category = 'FR'
97 and to_number(pap.per_information11)
98 = x_organization_id;
99
100 cursor csr_assignments is
101 select '1'
102 from per_all_assignments_f paa
103 where paa.establishment_id = x_organization_id;
104
105 l_dummy varchar2(1);
106
107 BEGIN
108 hr_utility.set_location('pefrorrvd package entered', 1);
109
110 if hr_organization_units_pkg.get_org_class(X_Organization_Id, 'FR_SOCIETE') then
111 open csr_estab_comp;
112 fetch csr_estab_comp into l_dummy;
113 hr_utility.set_location('FR societe!!', 10);
114 if csr_estab_comp%found then
115 hr_utility.set_location('FR societe failure!!', 10);
116 hr_utility.set_message(800,'PER_74848_ORG_DEL_EST');
117 hr_utility.raise_error;
118 end if;
119 close csr_estab_comp;
120 end if;
121
122 if hr_organization_units_pkg.get_org_class(X_Organization_Id,'FR_CENTRE_URSSAF') then
123 hr_utility.set_location('FR centre urssaf!!', 10);
124 open csr_estab_urssaf;
125 fetch csr_estab_urssaf into l_dummy;
126 if csr_estab_urssaf%found then
127 hr_utility.set_location('FR centre urssaf failure!!', 10);
128 hr_utility.set_message(800,'PER_74848_ORG_DEL_EST');
129 hr_utility.raise_error;
130 end if;
131 close csr_estab_urssaf;
132
133 open csr_comp_urssaf;
134 fetch csr_comp_urssaf into l_dummy;
135 hr_utility.set_location('FR comp urssaf!!', 10);
136 if csr_comp_urssaf%found then
137 hr_utility.set_location('FR comp urssaf!!', 10);
138 hr_utility.set_message(800,'PER_74849_ORG_DEL_COM');
139 hr_utility.raise_error;
140 end if;
141 close csr_comp_urssaf;
142 end if;
143
144 if hr_organization_units_pkg.get_org_class(X_Organization_Id,'FR_CENTRE_ASSEDIC') then
145 hr_utility.set_location('FR centre assedic!!', 10);
146 open csr_estab_assedic;
147 fetch csr_estab_assedic into l_dummy;
148 if csr_estab_assedic%found then
149 hr_utility.set_location('FR centre assedic failure!!', 10);
150 hr_utility.set_message(800,'PER_74848_ORG_DEL_EST');
151 hr_utility.raise_error;
152 end if;
153 close csr_estab_assedic;
154 end if;
155
156 if hr_organization_units_pkg.get_org_class(X_Organization_Id,'FR_PENSION') then
157 hr_utility.set_location('FR pension!!', 9);
158 open csr_estab_pens;
159 hr_utility.set_location('FR pension!!', 10);
160 fetch csr_estab_pens into l_dummy;
161 if csr_estab_pens%found then
162 hr_utility.set_location('FR pension failure!!', 10);
163 hr_utility.set_message(800,'PER_74848_ORG_DEL_EST');
164 hr_utility.raise_error;
165 end if;
166 hr_utility.set_location('FR pension!!', 11);
167 close csr_estab_pens;
168 hr_utility.set_location('FR pension!!', 12);
169
170 open csr_comp_pens;
171 hr_utility.set_location('FR pension!!', 13);
172 fetch csr_comp_pens into l_dummy;
173 hr_utility.set_location('FR comp pens!!', 10);
174 if csr_comp_pens%found then
175 hr_utility.set_location('FR comp pens failure!!', 10);
176 hr_utility.set_message(800,'PER_74849_ORG_DEL_COM');
177 hr_utility.raise_error;
178 end if;
179 close csr_comp_pens;
180 end if;
181
182 if hr_organization_units_pkg.get_org_class(X_Organization_Id,'FR_ASSURANCE') then
183 hr_utility.set_location('FR assurance!!', 10);
184 open csr_estab_insu;
185 fetch csr_estab_insu into l_dummy;
186 if csr_estab_insu%found then
187 hr_utility.set_location('FR assurance failure!!', 10);
188 hr_utility.set_message(800,'PER_74848_ORG_DEL_EST');
189 hr_utility.raise_error;
190 end if;
191 close csr_estab_insu;
192
193 open csr_comp_insu;
194 fetch csr_comp_insu into l_dummy;
195 hr_utility.set_location('FR comp insu!!', 10);
196 if csr_comp_insu%found then
197 hr_utility.set_location('FR comp insu failure!!', 10);
198 hr_utility.set_message(800,'PER_74849_ORG_DEL_COM');
199 hr_utility.raise_error;
200 end if;
201 close csr_comp_insu;
202 end if;
203
204 if hr_organization_units_pkg.get_org_class(X_Organization_Id,'FR_ETABLISSEMENT') then
205 hr_utility.set_location('FR etablissement!!', 10);
206 open csr_comp_dads_estab;
207 fetch csr_comp_dads_estab into l_dummy;
208 if csr_comp_dads_estab%found then
209 hr_utility.set_location('FR etablissement failure!!', 10);
210 hr_utility.set_message(800,'PER_74849_ORG_DEL_COM');
211 hr_utility.raise_error;
212 end if;
213 close csr_comp_dads_estab;
214
215 open csr_comp_heaq_estab;
216 fetch csr_comp_heaq_estab into l_dummy;
217 hr_utility.set_location('FR comp heaq estab!!', 10);
218 if csr_comp_heaq_estab%found then
219 hr_utility.set_location('FR comp heaq estab failure!!', 10);
220 hr_utility.set_message(800,'PER_74849_ORG_DEL_COM');
221 hr_utility.raise_error;
222 end if;
223 close csr_comp_heaq_estab;
224
225 open csr_assignments;
226 fetch csr_assignments into l_dummy;
227 hr_utility.set_location('FR assignments!!', 10);
228 if csr_assignments%found then
229 hr_utility.set_location('FR assignments failure!!', 10);
230 hr_utility.set_message(800,'PER_74853_ORG_DEL_ASG');
231 hr_utility.raise_error;
232 end if;
233 close csr_assignments;
234 end if;
235
236 if hr_organization_units_pkg.get_org_class(X_Organization_Id,'FR_CPAM' )then
237 hr_utility.set_location('FR cpam!!', 10);
238 open csr_cpam;
239 fetch csr_cpam into l_dummy;
240 if csr_cpam%found then
241 hr_utility.set_location('FR cpam failure!!', 10);
242 hr_utility.set_message(800,'PER_74852_ORG_DEL_PER');
243 hr_utility.raise_error;
244 end if;
245 close csr_cpam;
246 end if;
247 hr_utility.set_location('end of pefrorvd package', 10);
248
249 end validate_delete;
250 --
251 END PER_FR_VALIDATE_DELETE_PKG;