1 PACKAGE BODY PAY_RECORDED_REQUESTS_PKG AS
2 /* $Header: pyrecreq.pkb 115.5 2004/08/05 08:25:34 jford noship $ */
3
4 g_package varchar2(33) := ' pay_recorded_requests_pkg.'; -- Global package name
5 --
6
7 -- ----------------------------------------------------------------------------
8 -- Name: insert_recorded_request
9 --
10 -- Description:
11 -- This procedure controls the actual dml insert logic.
12 -- Other than validating teh process against a lookup, no further validation
13 -- occurs. Simply an insert in to the table occurs.
14 --
15 -- Prerequisites:
16 -- This is an internal private procedure which is called by the other
17 -- maintenance procedures held within this package.
18 --
19 -- In Parameters:
20 -- All column values to be inserted in to the table.
21 --
22 -- Post Success:
23 -- The specified row will be inserted into the schema.
24 --
25 -- Post Failure:
26 -- Lookup failure is handled explicitly, all other errors are propogated
27 -- using usual SQL behaviour.
28 -- ----------------------------------------------------------------------------
29
30
31 procedure insert_recorded_request( p_process in varchar2,
32 p_recorded_date in date,
33 p_attribute1 in varchar2 ,
34 p_attribute2 in varchar2 ,
35 p_attribute3 in varchar2 ,
36 p_attribute4 in varchar2 ,
37 p_attribute5 in varchar2 ,
38 p_attribute6 in varchar2 ,
39 p_attribute7 in varchar2 ,
40 p_attribute8 in varchar2 ,
41 p_attribute9 in varchar2 ,
42 p_attribute10 in varchar2 ,
43 p_attribute11 in varchar2 ,
44 p_attribute12 in varchar2 ,
45 p_attribute13 in varchar2 ,
46 p_attribute14 in varchar2 ,
47 p_attribute15 in varchar2 ,
48 p_attribute16 in varchar2 ,
49 p_attribute17 in varchar2 ,
50 p_attribute18 in varchar2 ,
51 p_attribute19 in varchar2 ,
52 p_attribute20 in varchar2 )
53 as
54 l_proc varchar2(72) := g_package||'insert_recorded_request';
55 --
56 BEGIN
57 --hr_utility.set_location('Entering:'||l_proc, 10);
58 --
59 -- Validate against hr_lookups,
60 -- to check request/process has been set up to use mechanism.
61 --
62 if hr_api.not_exists_in_hr_lookups
63 (p_effective_date => sysdate
64 ,p_lookup_type => 'PAY_RECORDED_REQUESTS'
65 ,p_lookup_code => p_process
66 )
67 then
68 hr_utility.set_location(' Leaving:'||l_proc, 20);
69 fnd_message.set_name('PAY', 'INVALID_LOOKUP_CODE');
70 fnd_message.set_token('LOOKUP_TYPE', 'PAY_RECORDED_REQUESTS');
71 fnd_message.set_token('VALUE', p_process);
72 fnd_message.raise_error;
73 end if;
74
75 INSERT INTO pay_recorded_requests(recorded_request_id,
76 recorded_date,
77 attribute_category,
78 attribute1 ,
79 attribute2 ,
80 attribute3 ,
81 attribute4 ,
82 attribute5 ,
83 attribute6 ,
84 attribute7 ,
85 attribute8 ,
86 attribute9 ,
87 attribute10,
88 attribute11,
89 attribute12,
90 attribute13,
91 attribute14,
92 attribute15,
93 attribute16,
94 attribute17,
95 attribute18,
96 attribute19,
97 attribute20 )
98 VALUES (pay_recorded_requests_s.NEXTVAL,
99 p_recorded_date,
100 p_process,
101 p_attribute1 ,
102 p_attribute2 ,
103 p_attribute3 ,
104 p_attribute4 ,
105 p_attribute5 ,
106 p_attribute6 ,
107 p_attribute7 ,
108 p_attribute8 ,
109 p_attribute9 ,
110 p_attribute10,
111 p_attribute11,
112 p_attribute12,
113 p_attribute13,
114 p_attribute14,
115 p_attribute15,
116 p_attribute16,
117 p_attribute17,
118 p_attribute18,
119 p_attribute19,
120 p_attribute20 );
121 --hr_utility.set_location(' Leaving:'||l_proc, 900);
122
123 END insert_recorded_request;
124
125 -- ----------------------------------------------------------------------------
126 -- Name: get_recorded_date
127 --
128 -- Description:
129 -- This procedure returns the date that has been recorded against the request
130 -- identified by the attributes.
131 -- If no record exists (no row in table) then a row is created and the default
132 -- hr_api.g_sot is returned.
133 --
134 -- Prerequisites:
135 -- This is a public procedure which allows code as part of the request to access
136 -- a single recorded date which may be required for future processing.
137 --
138 -- In Parameters:
139 -- All column values that identify the row explicitly, eg request type and parameter
140 -- values. The only out parameter is the current date stored against this row.
141 --
142 -- Post Success:
143 -- The specified row's recorded date will be returned.
144 --
145 -- Post Failure:
146 -- Errors are propogated using usual SQL behaviour.
147 -- ----------------------------------------------------------------------------
148
149 procedure get_recorded_date( p_process in varchar2,
150 p_recorded_date out nocopy date ,
151 p_attribute1 in varchar2 ,
152 p_attribute2 in varchar2 ,
153 p_attribute3 in varchar2 ,
154 p_attribute4 in varchar2 ,
155 p_attribute5 in varchar2 ,
156 p_attribute6 in varchar2 ,
157 p_attribute7 in varchar2 ,
158 p_attribute8 in varchar2 ,
159 p_attribute9 in varchar2 ,
160 p_attribute10 in varchar2 ,
161 p_attribute11 in varchar2 ,
162 p_attribute12 in varchar2 ,
163 p_attribute13 in varchar2 ,
164 p_attribute14 in varchar2 ,
165 p_attribute15 in varchar2 ,
166 p_attribute16 in varchar2 ,
167 p_attribute17 in varchar2 ,
168 p_attribute18 in varchar2 ,
169 p_attribute19 in varchar2 ,
170 p_attribute20 in varchar2 )
171 as
172
173 cursor csr_process_run IS
174
175 SELECT recorded_date
176 FROM pay_recorded_requests
177 WHERE attribute_category = p_process
178 and attribute1 = p_attribute1
179 and nvl(attribute2,'X') = nvl(p_attribute2,'X')
180 and nvl(attribute3,'X') = nvl(p_attribute3,'X')
181 and nvl(attribute4,'X') = nvl(p_attribute4,'X')
182 and nvl(attribute5,'X') = nvl(p_attribute5,'X')
183 and nvl(attribute6,'X') = nvl(p_attribute6,'X')
184 and nvl(attribute7,'X') = nvl(p_attribute7,'X')
185 and nvl(attribute8,'X') = nvl(p_attribute8,'X')
186 and nvl(attribute9,'X') = nvl(p_attribute9,'X')
187 and nvl(attribute10,'X') = nvl(p_attribute10,'X')
188 and nvl(attribute11,'X') = nvl(p_attribute11,'X')
189 and nvl(attribute12,'X') = nvl(p_attribute12,'X')
190 and nvl(attribute13,'X') = nvl(p_attribute13,'X')
191 and nvl(attribute14,'X') = nvl(p_attribute14,'X')
192 and nvl(attribute15,'X') = nvl(p_attribute15,'X')
193 and nvl(attribute16,'X') = nvl(p_attribute16,'X')
194 and nvl(attribute17,'X') = nvl(p_attribute17,'X')
195 and nvl(attribute18,'X') = nvl(p_attribute18,'X')
196 and nvl(attribute19,'X') = nvl(p_attribute19,'X')
197 and nvl(attribute20,'X') = nvl(p_attribute20,'X');
198
199
200 l_recorded_date DATE;
201 l_proc varchar2(72) := g_package||'get_recorded_date';
202 --
203 BEGIN
204 --hr_utility.set_location('Entering:'||l_proc, 10);
205
206 --See if we've already got an appropriate row
207 open csr_process_run;
208 fetch csr_process_run into l_recorded_date;
209
210 if (csr_process_run%NOTFOUND) then
211 --no row exists so create one
212 l_recorded_date := hr_api.g_sot;
213 insert_recorded_request( p_process ,
214 l_recorded_date,
215 p_attribute1,
216 p_attribute2,
217 p_attribute3,
218 p_attribute4,
219 p_attribute5,
220 p_attribute6,
221 p_attribute7,
222 p_attribute8,
223 p_attribute9,
224 p_attribute10,
225 p_attribute11,
226 p_attribute12,
227 p_attribute13,
228 p_attribute14,
229 p_attribute15,
230 p_attribute16,
231 p_attribute17,
232 p_attribute18,
233 p_attribute19 ,
234 p_attribute20);
235 end if;
236
237 close csr_process_run;
238
239 p_recorded_date := l_recorded_date;
240 --hr_utility.set_location('Leaving:'||l_proc, 900);
241
242 END get_recorded_date;
243
244 -- Variation of above procedure
245 -- pyccutl.pkb has function to get asg_act_status and this needs
246 -- to retrieve a date but without any dml because function is called
247 -- within a view. This is fine because when a true date needs to be
248 -- inserted, set_recorded_date can be called at a suitable juncture
249 --
250 procedure get_recorded_date_no_ins( p_process in varchar2,
251 p_recorded_date out nocopy date ,
252 p_attribute1 in varchar2 ,
253 p_attribute2 in varchar2 ,
254 p_attribute3 in varchar2 ,
255 p_attribute4 in varchar2 ,
256 p_attribute5 in varchar2 ,
257 p_attribute6 in varchar2 ,
258 p_attribute7 in varchar2 ,
259 p_attribute8 in varchar2 ,
260 p_attribute9 in varchar2 ,
261 p_attribute10 in varchar2 ,
262 p_attribute11 in varchar2 ,
263 p_attribute12 in varchar2 ,
264 p_attribute13 in varchar2 ,
265 p_attribute14 in varchar2 ,
266 p_attribute15 in varchar2 ,
267 p_attribute16 in varchar2 ,
268 p_attribute17 in varchar2 ,
269 p_attribute18 in varchar2 ,
270 p_attribute19 in varchar2 ,
271 p_attribute20 in varchar2 )
272 as
273
274 cursor csr_process_run IS
275
276 SELECT recorded_date
277 FROM pay_recorded_requests
278 WHERE attribute_category = p_process
279 and attribute1 = p_attribute1
280 and nvl(attribute2,'X') = nvl(p_attribute2,'X')
281 and nvl(attribute3,'X') = nvl(p_attribute3,'X')
282 and nvl(attribute4,'X') = nvl(p_attribute4,'X')
283 and nvl(attribute5,'X') = nvl(p_attribute5,'X')
284 and nvl(attribute6,'X') = nvl(p_attribute6,'X')
285 and nvl(attribute7,'X') = nvl(p_attribute7,'X')
286 and nvl(attribute8,'X') = nvl(p_attribute8,'X')
287 and nvl(attribute9,'X') = nvl(p_attribute9,'X')
288 and nvl(attribute10,'X') = nvl(p_attribute10,'X')
289 and nvl(attribute11,'X') = nvl(p_attribute11,'X')
290 and nvl(attribute12,'X') = nvl(p_attribute12,'X')
291 and nvl(attribute13,'X') = nvl(p_attribute13,'X')
292 and nvl(attribute14,'X') = nvl(p_attribute14,'X')
293 and nvl(attribute15,'X') = nvl(p_attribute15,'X')
294 and nvl(attribute16,'X') = nvl(p_attribute16,'X')
295 and nvl(attribute17,'X') = nvl(p_attribute17,'X')
296 and nvl(attribute18,'X') = nvl(p_attribute18,'X')
297 and nvl(attribute19,'X') = nvl(p_attribute19,'X')
298 and nvl(attribute20,'X') = nvl(p_attribute20,'X');
299
300
301 l_recorded_date DATE;
302 l_proc varchar2(72) := g_package||'get_recorded_date_no_ins';
303 --
304 BEGIN
305 --hr_utility.set_location('Entering:'||l_proc, 10);
306
307 --See if we've already got an appropriate row
308 open csr_process_run;
309 fetch csr_process_run into l_recorded_date;
310
311 if (csr_process_run%NOTFOUND) then
312 --no row exists, in this procedure we're not creating a row
313 l_recorded_date := hr_api.g_sot;
314 end if;
315
316 close csr_process_run;
317
318 p_recorded_date := l_recorded_date;
319 --hr_utility.set_location('Leaving:'||l_proc, 900);
320
321 END get_recorded_date_no_ins;
322
323 -- ----------------------------------------------------------------------------
324 -- Name: set_recorded_date
325 --
326 -- Description:
327 -- This procedure sets the recorded date against the request
328 -- identified by the attributes.
329 -- If no record exists (no row in table) then a row is created and this new date
330 -- is used.
331 --
332 -- Prerequisites:
333 -- This is a public procedure which allows code as part of the request to set
334 -- a single recorded date which may be required for future processing.
335 --
336 -- In Parameters:
337 -- All column values that identify the row explicitly, eg request type and parameter
338 -- values. Both the old date held for this row, and the new set date are returned.
339 --
340 -- Post Success:
341 -- The specified row's old and new recorded dates will be returned.
342 --
343 -- Post Failure:
344 -- Errors are propogated using usual SQL behaviour.
345 -- ----------------------------------------------------------------------------
346
347 procedure set_recorded_date( p_process in varchar2,
348 p_recorded_date in date,
349 p_recorded_date_o out nocopy date,
350 p_attribute1 in varchar2 ,
351 p_attribute2 in varchar2 ,
352 p_attribute3 in varchar2 ,
353 p_attribute4 in varchar2 ,
354 p_attribute5 in varchar2 ,
355 p_attribute6 in varchar2 ,
356 p_attribute7 in varchar2 ,
357 p_attribute8 in varchar2 ,
358 p_attribute9 in varchar2 ,
359 p_attribute10 in varchar2 ,
360 p_attribute11 in varchar2 ,
361 p_attribute12 in varchar2 ,
362 p_attribute13 in varchar2 ,
363 p_attribute14 in varchar2 ,
364 p_attribute15 in varchar2 ,
365 p_attribute16 in varchar2 ,
366 p_attribute17 in varchar2 ,
367 p_attribute18 in varchar2 ,
368 p_attribute19 in varchar2 ,
369 p_attribute20 in varchar2 )
370 as
371
372 cursor csr_process_run IS
373 SELECT recorded_date
374 FROM pay_recorded_requests
375 WHERE attribute_category = p_process
376 and attribute1 = p_attribute1
377 and nvl(attribute2,'X') = nvl(p_attribute2,'X')
378 and nvl(attribute3,'X') = nvl(p_attribute3,'X')
379 and nvl(attribute4,'X') = nvl(p_attribute4,'X')
380 and nvl(attribute5,'X') = nvl(p_attribute5,'X')
381 and nvl(attribute6,'X') = nvl(p_attribute6,'X')
382 and nvl(attribute7,'X') = nvl(p_attribute7,'X')
383 and nvl(attribute8,'X') = nvl(p_attribute8,'X')
384 and nvl(attribute9,'X') = nvl(p_attribute9,'X')
385 and nvl(attribute10,'X') = nvl(p_attribute10,'X')
386 and nvl(attribute11,'X') = nvl(p_attribute11,'X')
387 and nvl(attribute12,'X') = nvl(p_attribute12,'X')
388 and nvl(attribute13,'X') = nvl(p_attribute13,'X')
389 and nvl(attribute14,'X') = nvl(p_attribute14,'X')
390 and nvl(attribute15,'X') = nvl(p_attribute15,'X')
391 and nvl(attribute16,'X') = nvl(p_attribute16,'X')
392 and nvl(attribute17,'X') = nvl(p_attribute17,'X')
393 and nvl(attribute18,'X') = nvl(p_attribute18,'X')
394 and nvl(attribute19,'X') = nvl(p_attribute19,'X')
395 and nvl(attribute20,'X') = nvl(p_attribute20,'X')
396 for update of recorded_date;
397
398
399 l_recorded_date DATE;
400 l_proc varchar2(72) := g_package||'set_recorded_date';
401 --
402 BEGIN
403 --hr_utility.set_location('Entering:'||l_proc, 10);
404
405 --Get existing row
406 open csr_process_run;
407 fetch csr_process_run into l_recorded_date;
408
409 -- Return old date in case required by calling code
410 p_recorded_date_o := nvl(l_recorded_date,hr_api.g_sot);
411
412
413 IF (csr_process_run%NOTFOUND) then
414 --no row so make one
415 insert_recorded_request(
416 p_process,
417 p_recorded_date,
418 p_attribute1 ,
419 p_attribute2 ,
420 p_attribute3 ,
421 p_attribute4 ,
422 p_attribute5 ,
423 p_attribute6 ,
424 p_attribute7 ,
425 p_attribute8 ,
426 p_attribute9 ,
427 p_attribute10,
428 p_attribute11,
429 p_attribute12,
430 p_attribute13,
431 p_attribute14,
432 p_attribute15,
433 p_attribute16,
434 p_attribute17,
435 p_attribute18,
436 p_attribute19,
437 p_attribute20 );
438 ELSE
439 -- Update to store new date
440 UPDATE pay_recorded_requests
441 SET recorded_date = p_recorded_date
442 WHERE attribute_category = p_process
443 and attribute1 = p_attribute1
444 and nvl(attribute2,'X') = nvl(p_attribute2,'X')
445 and nvl(attribute3,'X') = nvl(p_attribute3,'X')
446 and nvl(attribute4,'X') = nvl(p_attribute4,'X')
447 and nvl(attribute5,'X') = nvl(p_attribute5,'X')
448 and nvl(attribute6,'X') = nvl(p_attribute6,'X')
449 and nvl(attribute7,'X') = nvl(p_attribute7,'X')
450 and nvl(attribute8,'X') = nvl(p_attribute8,'X')
451 and nvl(attribute9,'X') = nvl(p_attribute9,'X')
452 and nvl(attribute10,'X') = nvl(p_attribute10,'X')
453 and nvl(attribute11,'X') = nvl(p_attribute11,'X')
454 and nvl(attribute12,'X') = nvl(p_attribute12,'X')
455 and nvl(attribute13,'X') = nvl(p_attribute13,'X')
456 and nvl(attribute14,'X') = nvl(p_attribute14,'X')
457 and nvl(attribute15,'X') = nvl(p_attribute15,'X')
458 and nvl(attribute16,'X') = nvl(p_attribute16,'X')
459 and nvl(attribute17,'X') = nvl(p_attribute17,'X')
460 and nvl(attribute18,'X') = nvl(p_attribute18,'X')
461 and nvl(attribute19,'X') = nvl(p_attribute19,'X')
462 and nvl(attribute20,'X') = nvl(p_attribute20,'X');
463
464 END IF;
465
466 close csr_process_run;
467 --hr_utility.set_location('Leaving:'||l_proc, 900);
468
469 END set_recorded_date;
470
471
472 END PAY_RECORDED_REQUESTS_PKG;
[Home] [Help]