[Home] [Help]
PACKAGE BODY: APPS.PA_LOOKUPS_PKG
Source
1 PACKAGE BODY pa_lookups_pkg AS
2 /* $Header: PAXSLKPB.pls 115.0 99/07/16 15:32:32 porting ship $ */
3 ------------------------------------------------------------------------------
4 PROCEDURE check_unique (x_return_status IN OUT NUMBER
5 , x_lookup_type IN VARCHAR2
6 , x_lookup_code IN VARCHAR2
7 , x_meaning IN VARCHAR2)
8 IS
9 x_dummy NUMBER;
10
11 BEGIN
12 x_return_status := 0;
13
14 select 1
15 into x_dummy
16 from sys.dual
17 where not exists
18 (select 1
19 from pa_lookups p
20 where p.lookup_type = x_lookup_type
21 and ((UPPER(p.lookup_code) = UPPER(x_lookup_code)) or
22 (UPPER(p.meaning) = UPPER(x_meaning)))
23 );
24
25 x_return_status := 0;
26
27 EXCEPTION
28 WHEN NO_DATA_FOUND then
29 x_return_status := 1;
30
31 WHEN OTHERS then
32 x_return_status := SQLCODE;
33
34 END check_unique;
35
36 -----------------------------------------------------------------------------
37 PROCEDURE check_references (x_return_status IN OUT NUMBER
38 , x_stage IN OUT NUMBER
39 , x_lookup_code IN VARCHAR2)
40 IS
41 x_dummy NUMBER;
42
43
44 BEGIN
45 x_return_status := 0;
46
47 if (x_stage = 10) then
48 -- Credit Type
49
50 Begin
51 select 1
52 into x_dummy
53 from sys.dual
54 where not exists
55 (select 1
56 from pa_credit_receivers cr
57 where cr.credit_type_code = x_lookup_code);
58
59 EXCEPTION
60 WHEN NO_DATA_FOUND then
61 x_return_status := 1;
62 x_stage := 11;
63
64 WHEN OTHERS then
65 x_return_status := SQLCODE;
66 End;
67 Return;
68
69 elsif (x_stage = 20) then
70 -- Unit Type
71
72 Begin
73 -- Unit Type, Expenditure Types
74 select 1
75 into x_dummy
76 from sys.dual
77 where not exists
78 (select 1
79 from pa_expenditure_types et
80 where et.unit_of_measure = x_lookup_code);
81
82 EXCEPTION
83 WHEN NO_DATA_FOUND then
84 x_return_status := 1;
85 x_stage := 21;
86 return;
87
88 WHEN OTHERS then
89 x_return_status := SQLCODE;
90 return;
91 End;
92
93 Begin
94 -- Unit Type, Bill Rates
95 select 1
96 into x_dummy
97 from sys.dual
98 where not exists
99 (select 1
100 from pa_bill_rates br
101 where br.bill_rate_unit = x_lookup_code);
102
103 EXCEPTION
104 WHEN NO_DATA_FOUND then
105 x_return_status := 1;
106 x_stage := 22;
107 return;
108
109 WHEN OTHERS then
110 x_return_status := SQLCODE;
111 return;
112 End;
113 Begin
114 -- Unit Type, Resources
115 select 1
116 into x_dummy
117 from sys.dual
118 where not exists
119 (select 1
120 from pa_resources r
121 where r.unit_of_measure = x_lookup_code);
122
123 EXCEPTION
124 WHEN NO_DATA_FOUND then
125 x_return_status := 1;
126 x_stage := 23;
127 return;
128
129 WHEN OTHERS then
130 x_return_status := SQLCODE;
131 return;
132 End;
133 Begin
134 -- Unit Type, Employee Bill Rates Overrides
135 select 1
136 into x_dummy
137 from sys.dual
138 where not exists
139 (select 1
140 from pa_emp_bill_rate_overrides eb
141 where eb.bill_rate_unit = x_lookup_code);
142
143 EXCEPTION
144 WHEN NO_DATA_FOUND then
145 x_return_status := 1;
146 x_stage := 24;
147 return;
148
149 WHEN OTHERS then
150 x_return_status := SQLCODE;
151 return;
152 End;
153 Begin
154 -- Unit Type, Job Bill Rate Overrides
155 select 1
156 into x_dummy
157 from sys.dual
158 where not exists
159 (select 1
160 from pa_job_bill_rate_overrides jb
161 where jb.bill_rate_unit = x_lookup_code);
162
163 EXCEPTION
164 WHEN NO_DATA_FOUND then
165 x_return_status := 1;
166 x_stage := 25;
167 return;
168
169 WHEN OTHERS then
170 x_return_status := SQLCODE;
171 return;
172 End;
173 Return;
174
175 elsif (x_stage = 30) then
176
177
178 -- Service Type
179
180 Begin
181 -- Service Type, Project Types
182 select 1
183 into x_dummy
184 from sys.dual
185 where not exists
186 (select 1
187 from pa_project_types pt
188 where pt.service_type_code = x_lookup_code);
189
190 EXCEPTION
194 return;
191 WHEN NO_DATA_FOUND then
192 x_return_status := 1;
193 x_stage := 31;
195
196 WHEN OTHERS then
197 x_return_status := SQLCODE;
198 return;
199 End;
200
201 Begin
202 -- Service Type, Tasks
203 select 1
204 into x_dummy
205 from sys.dual
206 where not exists
207 (select 1
208 from pa_tasks t
209 where t.service_type_code = x_lookup_code);
210
211 EXCEPTION
212 WHEN NO_DATA_FOUND then
213 x_return_status := 1;
214 x_stage := 32;
215 return;
216
217 WHEN OTHERS then
218 x_return_status := SQLCODE;
219 return;
220 End;
221 Return;
222
223 elsif (x_stage = 40) then
224
225 -- Revenue Category
226
227 Begin
228 -- Revenue Category, Expenditure Types
229 select 1
230 into x_dummy
231 from sys.dual
232 where not exists
233 (select 1
234 from pa_expenditure_types et
235 where et.revenue_category_code = x_lookup_code);
236
237 EXCEPTION
238 WHEN NO_DATA_FOUND then
239 x_return_status := 1;
240 x_stage := 41;
241 return;
242
243 WHEN OTHERS then
244 x_return_status := SQLCODE;
245 return;
246 End;
247 Begin
248 -- Revenue Category, Event Types
249 select 1
250 into x_dummy
251 from sys.dual
252 where not exists
253 (select 1
254 from pa_event_types et
255 where et.revenue_category_code = x_lookup_code);
256
257 EXCEPTION
258 WHEN NO_DATA_FOUND then
259 x_return_status := 1;
260 x_stage := 42;
261 return;
262
263 WHEN OTHERS then
264 x_return_status := SQLCODE;
265 return;
266 End;
267 Begin
268 -- Revenue Category, Resource Txn Attributes
269 select 1
270 into x_dummy
271 from sys.dual
272 where not exists
273 (select 1
274 from pa_resource_txn_attributes rt
275 where rt.revenue_category = x_lookup_code);
276
277 EXCEPTION
278 WHEN NO_DATA_FOUND then
279 x_return_status := 1;
280 x_stage := 43;
281 return;
282
283 WHEN OTHERS then
284 x_return_status := SQLCODE;
285 return;
286 End;
287 Return;
288
289 elsif (x_stage = 50) then
290 -- Project Statuses
291 Begin
292 select 1
293 into x_dummy
294 from sys.dual
295 where not exists
296 (select 1
297 from pa_projects p
298 where p.project_status_code = x_lookup_code);
299
300 EXCEPTION
301 WHEN NO_DATA_FOUND then
302 x_return_status := 1;
303 x_stage := 51;
304
305 WHEN OTHERS then
306 x_return_status := SQLCODE;
307 End;
308 Return;
309
310 elsif (x_stage = 60) then
311
312 -- Customer Project Relationship
313
314 Begin
315 select 1
316 into x_dummy
317 from sys.dual
318 where not exists
319 (select 1
320 from pa_project_customers pc
321 where pc.project_relationship_code = x_lookup_code);
322
323 EXCEPTION
324 WHEN NO_DATA_FOUND then
325 x_return_status := 1;
326 x_stage := 61;
327
328 WHEN OTHERS then
329 x_return_status := SQLCODE;
330 End;
331 Return;
332
333 elsif (x_stage = 70) then
334
335 -- Project Contact Type
336
337 Begin
338 select 1
339 into x_dummy
340 from sys.dual
341 where not exists
342 (select 1
343 from pa_project_contacts pc
344 where pc.project_contact_type_code = x_lookup_code);
345
346 EXCEPTION
347 WHEN NO_DATA_FOUND then
348 x_return_status := 1;
349 x_stage := 71;
350
351 WHEN OTHERS then
352 x_return_status := SQLCODE;
353 End;
354 Return;
355
356 elsif (x_stage = 80) then
357
358 -- Budget Change Reason
359
360 Begin
361 -- Budget Change Reason, Budget Lines
362 select 1
363 into x_dummy
364 from sys.dual
365 where not exists
366 (select 1
367 from pa_budget_lines bl
368 where bl.change_reason_code = x_lookup_code);
369
370 EXCEPTION
371 WHEN NO_DATA_FOUND then
372 x_return_status := 1;
373 x_stage := 81;
374 return;
375
376 WHEN OTHERS then
377 x_return_status := SQLCODE;
378 return;
379 End;
380 Begin
381 -- Budget Change Reason, Budget Versions
382 select 1
383 into x_dummy
384 from sys.dual
385 where not exists
386 (select 1
387 from pa_budget_versions bv
388 where bv.change_reason_code = x_lookup_code);
389
390 EXCEPTION
391 WHEN NO_DATA_FOUND then
392 x_return_status := 1;
393 x_stage := 82;
394 return;
395
396 WHEN OTHERS then
397 x_return_status := SQLCODE;
398 return;
399 End;
400 Return;
401
402 elsif (x_stage = 90) then
403 -- PM Product code Pa projects
404 Begin
405 select 1 into x_dummy
406 from sys.dual
407 where not exists
408 (select 1 from pa_projects where pm_product_code
409 = x_lookup_code );
410 EXCEPTION
411 WHEN NO_DATA_FOUND then
412 x_return_status := 1;
413 x_stage := 92;
414 return;
415
416 WHEN OTHERS then
417 x_return_status := SQLCODE;
418 return;
419 End;
420 -- PM Product code Pa budget versions
421 Begin
422 select 1 into x_dummy
423 from sys.dual
424 where not exists
425 (select 1 from pa_budget_versions where pm_product_code
426 = x_lookup_code );
427 EXCEPTION
428 WHEN NO_DATA_FOUND then
429 x_return_status := 1;
430 x_stage := 93;
431 return;
432
433 WHEN OTHERS then
434 x_return_status := SQLCODE;
435 return;
436 End;
437 Return;
438 end if;
439
440 END check_references;
441 --===========================================================================
442 END pa_lookups_pkg;