[Home] [Help]
PACKAGE BODY: APPS.PER_OTA_PREDEL_VALIDATION
Source
1 PACKAGE BODY PER_OTA_PREDEL_VALIDATION as
2 /* $Header: peperota.pkb 115.3 2002/12/07 00:07:02 dhmulia ship $ */
3
4 PROCEDURE ota_predel_job_validation(p_job_id number) is
5 --
6 g_dummy_number number;
7 --l_sql_text VARCHAR2(2000);
8 l_status VARCHAR2(1);
9 l_industry VARCHAR2(1);
10 --l_oci_out VARCHAR2(1);
11 --l_sql_cursor NUMBER;
12 --l_rows_fetched NUMBER;
13
14
15 l_exists varchar2(1);
16 --
17
18 Cursor CSR_JOB IS
19 Select null
20 from ota_event_associations
21 where job_id = p_job_id;
22
23 begin
24 --
25 -- Check there are no values in ota_event_association
26 --
27 hr_utility.set_location('PER_OTA_PREDEL_VALIDATION.ota_predel_job_validation', 10);
28 --
29 --
30 --
31 -- is ota installed?
32 --
33 if (fnd_installation.get(appl_id => 810
34 ,dep_appl_id => 810
35 ,status => l_status
36 ,industry => l_industry))
37 then
38 --
39 -- If fully installed (l_status = 'I')
40 --
41 if l_status = 'I'
42 then
43 -- Dynamic SQL cursor to get round the problem of Table not existing.
44 -- Shouldn't be a problem after 10.6, but better safe than sorry.
45 -- This uses a similar method to OCI but Via PL/SQL instead.
46 --
47 --
48 begin
49 For job in CSR_JOB
50 LOOP
51 fnd_message.set_name('PAY','HR_7995_OTA_RECORD_EXISTS');
52 fnd_message.raise_error;
53
54 END LOOP;
55
56 end;
57 end if;
58 end if;
59 end ota_predel_job_validation;
60 --
61 --
62 PROCEDURE ota_predel_pos_validation(p_position_id number) is
63 --
64 l_exists varchar2(1);
65 l_status VARCHAR2(1);
66 l_industry VARCHAR2(1);
67
68 Cursor CSR_POS IS
69 Select null
70 from ota_event_associations
71 where position_id = p_position_id;
72
73 begin
74 --
75 -- Check there are no values in ota_event_association
76 --
77 --
78 hr_utility.set_location('PER_OTA_PREDEL_VALIDATION.ota_predel_pos_validation', 10);
79 --
80 --
81 -- is ota installed?
82 if (fnd_installation.get(appl_id => 810
83 ,dep_appl_id => 810
84 ,status => l_status
85 ,industry => l_industry))
86 then
87 --
88 -- If fully installed (l_status = 'I')
89 --
90 if l_status = 'I'
91 then
92 begin
93 For pos in CSR_POS
94 LOOP
95 fnd_message.set_name('PAY','HR_7995_OTA_RECORD_EXISTS');
96 fnd_message.raise_error;
97
98 END LOOP;
99 end;
100 end if;
101 end if;
102 end ota_predel_pos_validation;
103 --
104 --
105 PROCEDURE ota_predel_org_validation(p_organization_id number) is
106 --
107 l_exists varchar2(1);
108 l_status VARCHAR2(1);
109 l_industry VARCHAR2(1);
110
111
112 CURSOR CSR_ORG
113 IS
114 select null
115 from sys.dual
116 where exists(select null
117 from ota_events
118 where organization_id = p_organization_id)
119 or exists(select null
120 from ota_delegate_bookings
121 where organization_id = p_organization_id)
122 or exists(select null
123 from ota_event_associations
124 where organization_id = p_organization_id)
125 or exists( select null
126 from ota_finance_headers
127 where organization_id = p_organization_id)
128 or exists( select null from ota_activity_versions
129 where developer_organization_id = p_organization_id)
130 or exists( select null from ota_events
131 where training_center_id = p_organization_id);
132 -- or exists( select null from ota_notrng_histories
133 -- where organization_id = p_organization_id) ;
134 begin
135 --
136 -- Check there are no values in ota_delegate_bookings
137 -- ota_events
138 -- ota_event_associations
139 -- ota_finance_headers
140 -- ota_activity_versions
141 -- ota_notrng_histories
142 --
143 hr_utility.set_location('PER_OTA_PREDEL_VALIDATION.ota_predel_org_validation', 10);
144 --
145 --
146 -- is ota installed?
147 --
148 if (fnd_installation.get(appl_id => 810
149 ,dep_appl_id => 810
150 ,status => l_status
151 ,industry => l_industry))
152 then
153 --
154 -- If fully installed (l_status = 'I')
155 --
156 if l_status = 'I'
157 then
158 begin
159 For pos in CSR_ORG
160 LOOP
161 fnd_message.set_name('PAY','HR_7995_OTA_RECORD_EXISTS');
162 fnd_message.raise_error;
163
164 END LOOP;
165 end;
166 end if;
167 end if;
168 end ota_predel_org_validation;
169 --
170 --
171 PROCEDURE ota_predel_per_validation(p_person_id number) is
172 --
173 l_exists varchar2(1);
174 l_status VARCHAR2(1);
175 l_industry VARCHAR2(1);
176
177 CURSOR CSR_PER
178 IS
179 select null
180 from sys.dual
181 where exists(select null
182 from ota_activity_versions
183 where controlling_person_id = p_person_id)
184 or exists(select null
185 from ota_delegate_bookings
186 where delegate_person_id = p_person_id
187 or sponsor_person_id = p_person_id)
188 or exists(select null
189 from ota_events
190 where owner_id = p_person_id)
191 or exists(select null
192 from ota_notrng_histories
193 where person_id = p_person_id and
194 organization_id is not null);
195 begin
196 --
197 -- Check there are no values in ota_delegate_bookings
198 -- ota_activity_versions
199 -- ota_events
200 -- ota_notrng_histories
201 --
202 hr_utility.set_location('PER_OTA_PREDEL_VALIDATION.ota_predel_per_validation', 10);
203 --
204 -- is ota installed?
205 --
206 if (fnd_installation.get(appl_id => 810
207 ,dep_appl_id => 810
208 ,status => l_status
209 ,industry => l_industry))
210 then
211 --
212 -- If fully installed (l_status = 'I')
213 --
214 if l_status = 'I'
215 then
216 begin
217 For pos in CSR_PER
218 LOOP
219 fnd_message.set_name('PAY','HR_7995_OTA_RECORD_EXISTS');
220 fnd_message.raise_error;
221
222 END LOOP;
223 end;
224 end if;
225 end if;
226 end ota_predel_per_validation;
227 --
228 --
229 PROCEDURE ota_predel_asg_validation(p_assignment_id number) is
230 --
231 l_exists varchar2(1);
232 l_status VARCHAR2(1);
233 l_industry VARCHAR2(1);
234
235 Cursor CSR_ASG
236 IS
237 select null
238 from ota_delegate_bookings
239 where delegate_assignment_id = p_assignment_id
240 or sponsor_assignment_id = p_assignment_id ;
241
242
243 begin
244 --
245 -- Check there are no values in ota_delegate_bookings
246 --
247 hr_utility.set_location('PER_OTA_PREDEL_VALIDATION.ota_predel_asg_validation', 10);
248 --
249 --
250 -- is ota installed?
251 --
252 if (fnd_installation.get(appl_id => 810
253 ,dep_appl_id => 810
254 ,status => l_status
255 ,industry => l_industry))
256 then
257 --
258 -- If fully installed (l_status = 'I')
259 --
260 if l_status = 'I'
261 then
262 begin
263 For pos in CSR_ASG
264 LOOP
265 fnd_message.set_name('PAY','HR_7995_OTA_RECORD_EXISTS');
266 fnd_message.raise_error;
267
268 END LOOP;
269 end;
270 end if;
271 end if;
272 end ota_predel_asg_validation;
273 --
274 --
275 END PER_OTA_PREDEL_VALIDATION;