[Home] [Help]
PACKAGE BODY: APPS.PAY_PAYMENT_METHODS_PKG
Source
1 PACKAGE BODY PAY_PAYMENT_METHODS_PKG AS
2 /* $Header: pyppm01t.pkb 120.0 2005/05/29 07:40:52 appldev noship $ */
3 PROCEDURE INSERT_ROW(P_ROWID IN OUT NOCOPY VARCHAR2,
4 P_PERSONAL_PAYMENT_METHOD_ID IN OUT NOCOPY NUMBER,
5 P_EFFECTIVE_START_DATE DATE,
6 P_EFFECTIVE_END_DATE DATE,
7 P_BUSINESS_GROUP_ID NUMBER,
8 P_EXTERNAL_ACCOUNT_ID NUMBER,
9 P_ASSIGNMENT_ID NUMBER,
10 P_PERSON_ID NUMBER,
11 P_RUN_TYPE_ID NUMBER,
12 P_ORG_PAYMENT_METHOD_ID NUMBER,
13 P_AMOUNT NUMBER,
14 P_COMMENT_ID NUMBER,
15 P_PERCENTAGE NUMBER,
16 P_PRIORITY NUMBER,
17 P_PAYEE_TYPE VARCHAR2,
18 P_PAYEE_ID NUMBER,
19 P_ATTRIBUTE_CATEGORY VARCHAR2,
20 P_ATTRIBUTE1 VARCHAR2,
21 P_ATTRIBUTE2 VARCHAR2,
22 P_ATTRIBUTE3 VARCHAR2,
23 P_ATTRIBUTE4 VARCHAR2,
24 P_ATTRIBUTE5 VARCHAR2,
25 P_ATTRIBUTE6 VARCHAR2,
26 P_ATTRIBUTE7 VARCHAR2,
27 P_ATTRIBUTE8 VARCHAR2,
28 P_ATTRIBUTE9 VARCHAR2,
29 P_ATTRIBUTE10 VARCHAR2,
30 P_ATTRIBUTE11 VARCHAR2,
31 P_ATTRIBUTE12 VARCHAR2,
32 P_ATTRIBUTE13 VARCHAR2,
33 P_ATTRIBUTE14 VARCHAR2,
34 P_ATTRIBUTE15 VARCHAR2,
35 P_ATTRIBUTE16 VARCHAR2,
36 P_ATTRIBUTE17 VARCHAR2,
37 P_ATTRIBUTE18 VARCHAR2,
38 P_ATTRIBUTE19 VARCHAR2,
39 P_ATTRIBUTE20 VARCHAR2,
40 P_PRENOTE_DATE DATE,
41 /** sbilling **/
42 P_TERRITORY_CODE VARCHAR2,
43 P_PPM_INFORMATION_CATEGORY VARCHAR2,
44 P_PPM_INFORMATION1 VARCHAR2,
45 P_PPM_INFORMATION2 VARCHAR2,
46 P_PPM_INFORMATION3 VARCHAR2,
47 P_PPM_INFORMATION4 VARCHAR2,
48 P_PPM_INFORMATION5 VARCHAR2,
49 P_PPM_INFORMATION6 VARCHAR2,
50 P_PPM_INFORMATION7 VARCHAR2,
51 P_PPM_INFORMATION8 VARCHAR2,
52 P_PPM_INFORMATION9 VARCHAR2,
53 P_PPM_INFORMATION10 VARCHAR2,
54 P_PPM_INFORMATION11 VARCHAR2,
55 P_PPM_INFORMATION12 VARCHAR2,
56 P_PPM_INFORMATION13 VARCHAR2,
57 P_PPM_INFORMATION14 VARCHAR2,
58 P_PPM_INFORMATION15 VARCHAR2,
59 P_PPM_INFORMATION16 VARCHAR2,
60 P_PPM_INFORMATION17 VARCHAR2,
61 P_PPM_INFORMATION18 VARCHAR2,
62 P_PPM_INFORMATION19 VARCHAR2,
63 P_PPM_INFORMATION20 VARCHAR2,
64 P_PPM_INFORMATION21 VARCHAR2,
65 P_PPM_INFORMATION22 VARCHAR2,
66 P_PPM_INFORMATION23 VARCHAR2,
67 P_PPM_INFORMATION24 VARCHAR2,
68 P_PPM_INFORMATION25 VARCHAR2,
69 P_PPM_INFORMATION26 VARCHAR2,
70 P_PPM_INFORMATION27 VARCHAR2,
71 P_PPM_INFORMATION28 VARCHAR2,
72 P_PPM_INFORMATION29 VARCHAR2,
73 P_PPM_INFORMATION30 VARCHAR2
74 ) IS
75
76 CURSOR C IS
77 SELECT ROWID
78 FROM PAY_PERSONAL_PAYMENT_METHODS_F
79 WHERE PERSONAL_PAYMENT_METHOD_ID = P_PERSONAL_PAYMENT_METHOD_ID;
80
81 CURSOR C2 IS
82 SELECT pay_personal_payment_methods_s.nextval
83 FROM sys.dual;
84
85 BEGIN
86
87 OPEN C2;
88 FETCH C2 INTO P_PERSONAL_PAYMENT_METHOD_ID;
89 CLOSE C2;
90
91 INSERT INTO PAY_PERSONAL_PAYMENT_METHODS_F (PERSONAL_PAYMENT_METHOD_ID,
92 EFFECTIVE_START_DATE,
93 EFFECTIVE_END_DATE,
94 BUSINESS_GROUP_ID,
95 EXTERNAL_ACCOUNT_ID,
96 ASSIGNMENT_ID,
97 PERSON_ID,
98 RUN_TYPE_ID,
99 ORG_PAYMENT_METHOD_ID,
100 AMOUNT,
101 COMMENT_ID,
102 PERCENTAGE,
103 PRIORITY,
104 PAYEE_TYPE,
105 PAYEE_ID,
106 ATTRIBUTE_CATEGORY,
107 ATTRIBUTE1,
108 ATTRIBUTE2,
109 ATTRIBUTE3,
110 ATTRIBUTE4,
111 ATTRIBUTE5,
112 ATTRIBUTE6,
113 ATTRIBUTE7,
114 ATTRIBUTE8,
115 ATTRIBUTE9,
116 ATTRIBUTE10,
117 ATTRIBUTE11,
118 ATTRIBUTE12,
119 ATTRIBUTE13,
120 ATTRIBUTE14,
121 ATTRIBUTE15,
122 ATTRIBUTE16,
123 ATTRIBUTE17,
124 ATTRIBUTE18,
125 ATTRIBUTE19,
126 ATTRIBUTE20,
127 PPM_INFORMATION_CATEGORY,
128 PPM_INFORMATION1,
129 PPM_INFORMATION2,
130 PPM_INFORMATION3,
131 PPM_INFORMATION4,
132 PPM_INFORMATION5,
133 PPM_INFORMATION6,
134 PPM_INFORMATION7,
135 PPM_INFORMATION8,
136 PPM_INFORMATION9,
137 PPM_INFORMATION10,
138 PPM_INFORMATION11,
139 PPM_INFORMATION12,
140 PPM_INFORMATION13,
141 PPM_INFORMATION14,
142 PPM_INFORMATION15,
143 PPM_INFORMATION16,
144 PPM_INFORMATION17,
145 PPM_INFORMATION18,
146 PPM_INFORMATION19,
147 PPM_INFORMATION20,
148 PPM_INFORMATION21,
149 PPM_INFORMATION22,
150 PPM_INFORMATION23,
151 PPM_INFORMATION24,
152 PPM_INFORMATION25,
153 PPM_INFORMATION26,
154 PPM_INFORMATION27,
155 PPM_INFORMATION28,
156 PPM_INFORMATION29,
157 PPM_INFORMATION30)
158
159 VALUES(P_PERSONAL_PAYMENT_METHOD_ID,P_EFFECTIVE_START_DATE,
160 P_EFFECTIVE_END_DATE,P_BUSINESS_GROUP_ID,P_EXTERNAL_ACCOUNT_ID,
161 P_ASSIGNMENT_ID,P_PERSON_ID,P_RUN_TYPE_ID,P_ORG_PAYMENT_METHOD_ID,P_AMOUNT,P_COMMENT_ID,
162 P_PERCENTAGE,P_PRIORITY,P_PAYEE_TYPE,P_PAYEE_ID,
163 P_ATTRIBUTE_CATEGORY,P_ATTRIBUTE1,
164 P_ATTRIBUTE2,P_ATTRIBUTE3,P_ATTRIBUTE4, P_ATTRIBUTE5,P_ATTRIBUTE6,
165 P_ATTRIBUTE7,P_ATTRIBUTE8,P_ATTRIBUTE9,P_ATTRIBUTE10,P_ATTRIBUTE11,
166 P_ATTRIBUTE12,P_ATTRIBUTE13,P_ATTRIBUTE14,P_ATTRIBUTE15,P_ATTRIBUTE16,
167 P_ATTRIBUTE17,P_ATTRIBUTE18,P_ATTRIBUTE19,P_ATTRIBUTE20,
168 P_PPM_INFORMATION_CATEGORY,P_PPM_INFORMATION1,P_PPM_INFORMATION2,
169 P_PPM_INFORMATION3,P_PPM_INFORMATION4,P_PPM_INFORMATION5,P_PPM_INFORMATION6,
170 P_PPM_INFORMATION7,P_PPM_INFORMATION8,P_PPM_INFORMATION9,P_PPM_INFORMATION10,
171 P_PPM_INFORMATION11,P_PPM_INFORMATION12,P_PPM_INFORMATION13,P_PPM_INFORMATION14,
172 P_PPM_INFORMATION15,P_PPM_INFORMATION16,P_PPM_INFORMATION17,P_PPM_INFORMATION18,
173 P_PPM_INFORMATION19,P_PPM_INFORMATION20,P_PPM_INFORMATION21,P_PPM_INFORMATION22,
174 P_PPM_INFORMATION23,P_PPM_INFORMATION24,P_PPM_INFORMATION25,P_PPM_INFORMATION26,
175 P_PPM_INFORMATION27,P_PPM_INFORMATION28,P_PPM_INFORMATION29,P_PPM_INFORMATION30 );
176
177
178 OPEN C;
179 FETCH C INTO P_Rowid;
180 if (C%NOTFOUND) then
181 CLOSE C;
182 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
183 hr_utility.set_message_token('PROCEDURE','Insert_row');
184 hr_utility.set_message_token('STEP','1');
185 hr_utility.raise_error;
186 end if;
187 CLOSE C;
188 --
189 IF (P_EXTERNAL_ACCOUNT_ID IS NOT NULL) AND (P_PRENOTE_DATE IS NOT NULL) THEN
190 --
191 UPDATE PAY_EXTERNAL_ACCOUNTS
192 SET PRENOTE_DATE = P_PRENOTE_DATE
193 WHERE EXTERNAL_ACCOUNT_ID = P_EXTERNAL_ACCOUNT_ID;
194 --
195 END IF;
196
197 /** sbilling **/
198 -- always want to set territory code,
199 -- update api depends on territory code remaining unchanged
200 --
201 IF (P_EXTERNAL_ACCOUNT_ID IS NOT NULL) AND (P_TERRITORY_CODE IS NOT NULL) THEN
202 UPDATE PAY_EXTERNAL_ACCOUNTS
203 SET TERRITORY_CODE = P_TERRITORY_CODE
204 WHERE EXTERNAL_ACCOUNT_ID = P_EXTERNAL_ACCOUNT_ID;
205 END IF;
206
207 END INSERT_ROW;
208 --
209 PROCEDURE UPDATE_ROW(P_ROWID VARCHAR2,
210 P_PERSONAL_PAYMENT_METHOD_ID NUMBER,
211 P_EFFECTIVE_START_DATE DATE,
212 P_EFFECTIVE_END_DATE DATE,
213 P_BUSINESS_GROUP_ID NUMBER,
214 P_EXTERNAL_ACCOUNT_ID NUMBER,
215 P_ASSIGNMENT_ID NUMBER,
216 P_PERSON_ID NUMBER,
217 P_RUN_TYPE_ID NUMBER,
218 P_ORG_PAYMENT_METHOD_ID NUMBER,
219 P_AMOUNT NUMBER,
220 P_COMMENT_ID NUMBER,
221 P_PERCENTAGE NUMBER,
222 P_PRIORITY NUMBER,
223 P_PAYEE_TYPE VARCHAR2,
224 P_PAYEE_ID NUMBER,
225 P_ATTRIBUTE_CATEGORY VARCHAR2,
226 P_ATTRIBUTE1 VARCHAR2,
227 P_ATTRIBUTE2 VARCHAR2,
228 P_ATTRIBUTE3 VARCHAR2,
229 P_ATTRIBUTE4 VARCHAR2,
230 P_ATTRIBUTE5 VARCHAR2,
231 P_ATTRIBUTE6 VARCHAR2,
232 P_ATTRIBUTE7 VARCHAR2,
233 P_ATTRIBUTE8 VARCHAR2,
234 P_ATTRIBUTE9 VARCHAR2,
235 P_ATTRIBUTE10 VARCHAR2,
236 P_ATTRIBUTE11 VARCHAR2,
237 P_ATTRIBUTE12 VARCHAR2,
238 P_ATTRIBUTE13 VARCHAR2,
239 P_ATTRIBUTE14 VARCHAR2,
240 P_ATTRIBUTE15 VARCHAR2,
241 P_ATTRIBUTE16 VARCHAR2,
242 P_ATTRIBUTE17 VARCHAR2,
243 P_ATTRIBUTE18 VARCHAR2,
244 P_ATTRIBUTE19 VARCHAR2,
245 P_ATTRIBUTE20 VARCHAR2,
246 P_PRENOTE_DATE DATE,
247 /** sbilling **/
248 P_TERRITORY_CODE VARCHAR2,
249 P_PPM_INFORMATION_CATEGORY VARCHAR2,
250 P_PPM_INFORMATION1 VARCHAR2,
251 P_PPM_INFORMATION2 VARCHAR2,
252 P_PPM_INFORMATION3 VARCHAR2,
253 P_PPM_INFORMATION4 VARCHAR2,
254 P_PPM_INFORMATION5 VARCHAR2,
255 P_PPM_INFORMATION6 VARCHAR2,
256 P_PPM_INFORMATION7 VARCHAR2,
257 P_PPM_INFORMATION8 VARCHAR2,
258 P_PPM_INFORMATION9 VARCHAR2,
259 P_PPM_INFORMATION10 VARCHAR2,
260 P_PPM_INFORMATION11 VARCHAR2,
261 P_PPM_INFORMATION12 VARCHAR2,
262 P_PPM_INFORMATION13 VARCHAR2,
263 P_PPM_INFORMATION14 VARCHAR2,
264 P_PPM_INFORMATION15 VARCHAR2,
265 P_PPM_INFORMATION16 VARCHAR2,
266 P_PPM_INFORMATION17 VARCHAR2,
267 P_PPM_INFORMATION18 VARCHAR2,
268 P_PPM_INFORMATION19 VARCHAR2,
269 P_PPM_INFORMATION20 VARCHAR2,
270 P_PPM_INFORMATION21 VARCHAR2,
271 P_PPM_INFORMATION22 VARCHAR2,
272 P_PPM_INFORMATION23 VARCHAR2,
273 P_PPM_INFORMATION24 VARCHAR2,
274 P_PPM_INFORMATION25 VARCHAR2,
275 P_PPM_INFORMATION26 VARCHAR2,
276 P_PPM_INFORMATION27 VARCHAR2,
277 P_PPM_INFORMATION28 VARCHAR2,
278 P_PPM_INFORMATION29 VARCHAR2,
279 P_PPM_INFORMATION30 VARCHAR2
280 ) IS
281
282 BEGIN
283 UPDATE PAY_PERSONAL_PAYMENT_METHODS_F
284 SET PERSONAL_PAYMENT_METHOD_ID = P_PERSONAL_PAYMENT_METHOD_ID,
285 EFFECTIVE_START_DATE = P_EFFECTIVE_START_DATE,
286 EFFECTIVE_END_DATE = P_EFFECTIVE_END_DATE,
287 BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID,
288 EXTERNAL_ACCOUNT_ID = P_EXTERNAL_ACCOUNT_ID,
289 ASSIGNMENT_ID = P_ASSIGNMENT_ID,
290 PERSON_ID = P_PERSON_ID,
294 COMMENT_ID = P_COMMENT_ID,
291 RUN_TYPE_ID = P_RUN_TYPE_ID,
292 ORG_PAYMENT_METHOD_ID = P_ORG_PAYMENT_METHOD_ID,
293 AMOUNT = P_AMOUNT,
295 PERCENTAGE = P_PERCENTAGE,
296 PRIORITY = P_PRIORITY,
297 PAYEE_TYPE = P_PAYEE_TYPE,
298 PAYEE_ID = P_PAYEE_ID,
299 ATTRIBUTE_CATEGORY = P_ATTRIBUTE_CATEGORY,
300 ATTRIBUTE1 = P_ATTRIBUTE1,
301 ATTRIBUTE2 = P_ATTRIBUTE2,
302 ATTRIBUTE3 = P_ATTRIBUTE3,
303 ATTRIBUTE4 = P_ATTRIBUTE4,
304 ATTRIBUTE5 = P_ATTRIBUTE5,
305 ATTRIBUTE6 = P_ATTRIBUTE6,
306 ATTRIBUTE7 = P_ATTRIBUTE7,
307 ATTRIBUTE8 = P_ATTRIBUTE8,
308 ATTRIBUTE9 = P_ATTRIBUTE9,
309 ATTRIBUTE10 = P_ATTRIBUTE10,
310 ATTRIBUTE11 = P_ATTRIBUTE11,
311 ATTRIBUTE12 = P_ATTRIBUTE12,
312 ATTRIBUTE13 = P_ATTRIBUTE13,
313 ATTRIBUTE14 = P_ATTRIBUTE14,
314 ATTRIBUTE15 = P_ATTRIBUTE15,
315 ATTRIBUTE16 = P_ATTRIBUTE16,
316 ATTRIBUTE17 = P_ATTRIBUTE17,
317 ATTRIBUTE18 = P_ATTRIBUTE18,
318 ATTRIBUTE19 = P_ATTRIBUTE19,
319 ATTRIBUTE20 = P_ATTRIBUTE20,
320 PPM_INFORMATION_CATEGORY = P_PPM_INFORMATION_CATEGORY,
321 PPM_INFORMATION1 = P_PPM_INFORMATION1,
322 PPM_INFORMATION2 = P_PPM_INFORMATION2,
323 PPM_INFORMATION3 = P_PPM_INFORMATION3,
324 PPM_INFORMATION4 = P_PPM_INFORMATION4,
325 PPM_INFORMATION5 = P_PPM_INFORMATION5,
326 PPM_INFORMATION6 = P_PPM_INFORMATION6,
327 PPM_INFORMATION7 = P_PPM_INFORMATION7,
328 PPM_INFORMATION8 = P_PPM_INFORMATION8,
329 PPM_INFORMATION9 = P_PPM_INFORMATION9,
330 PPM_INFORMATION10 = P_PPM_INFORMATION10,
331 PPM_INFORMATION11 = P_PPM_INFORMATION11,
332 PPM_INFORMATION12 = P_PPM_INFORMATION12,
333 PPM_INFORMATION13 = P_PPM_INFORMATION13,
334 PPM_INFORMATION14 = P_PPM_INFORMATION14,
335 PPM_INFORMATION15 = P_PPM_INFORMATION15,
336 PPM_INFORMATION16 = P_PPM_INFORMATION16,
337 PPM_INFORMATION17 = P_PPM_INFORMATION17,
338 PPM_INFORMATION18 = P_PPM_INFORMATION18,
339 PPM_INFORMATION19 = P_PPM_INFORMATION19,
340 PPM_INFORMATION20 = P_PPM_INFORMATION20,
341 PPM_INFORMATION21 = P_PPM_INFORMATION21,
342 PPM_INFORMATION22 = P_PPM_INFORMATION22,
343 PPM_INFORMATION23 = P_PPM_INFORMATION23,
344 PPM_INFORMATION24 = P_PPM_INFORMATION24,
345 PPM_INFORMATION25 = P_PPM_INFORMATION25,
346 PPM_INFORMATION26 = P_PPM_INFORMATION26,
347 PPM_INFORMATION27 = P_PPM_INFORMATION27,
348 PPM_INFORMATION28 = P_PPM_INFORMATION28,
349 PPM_INFORMATION29 = P_PPM_INFORMATION29,
350 PPM_INFORMATION30 = P_PPM_INFORMATION30
351 WHERE ROWID = P_ROWID;
352 IF (P_EXTERNAL_ACCOUNT_ID IS NOT NULL) THEN
353 --
354 UPDATE PAY_EXTERNAL_ACCOUNTS
355 SET PRENOTE_DATE = P_PRENOTE_DATE
356 WHERE EXTERNAL_ACCOUNT_ID = P_EXTERNAL_ACCOUNT_ID;
357 --
358 END IF;
359
360 /** sbilling **/
361 -- always want to set territory code,
362 -- update api depends on territory code remaining unchanged
363 --
364 IF (P_EXTERNAL_ACCOUNT_ID IS NOT NULL) AND (P_TERRITORY_CODE IS NOT NULL) THEN
365 UPDATE PAY_EXTERNAL_ACCOUNTS
366 SET TERRITORY_CODE = P_TERRITORY_CODE
367 WHERE EXTERNAL_ACCOUNT_ID = P_EXTERNAL_ACCOUNT_ID;
368 END IF;
369
370 --
371 END UPDATE_ROW;
372
373 --
374 PROCEDURE DELETE_ROW(P_ROWID VARCHAR2,
375 P_PPM_ID NUMBER,
376 P_AFTER_THIS_DATE DATE,
377 P_PAYEE_TYPE VARCHAR2) IS
378 --
379 CURSOR el_ent_check IS
380 SELECT 'el_ents_exist'
381 FROM pay_element_entries_f
382 WHERE personal_payment_method_id = p_ppm_id
383 AND effective_end_date > p_after_this_date;
384 --
388 BEGIN
385 l_after_this_date varchar(20);
386 l_dummy varchar(15);
387 --
389 --
390 -- Check there are no prepayments belonging to the PPM:
391 -- - at all, if the delete is a ZAP;
392 -- - after the end date, if it's a date effective delete.
393 --
394 -- The kind of delete is worked out on the client, and the
395 -- date parameter passed in is set to start_of_time or session
396 -- date accordingly. Change the date passed in to a char first,
397 -- because that's what check_pp expects.
398 --
399 -- Also do a similar check that there are no orphaned element
400 -- entries, if this is a 3rd party PPM (i.e. the payee type is
401 -- not null). Note that element entries don't apply to non-3rd
402 -- party PPMs.
403 --
404 IF p_payee_type is not null then
405 open el_ent_check;
406 fetch el_ent_check into l_dummy;
407 --
408 IF el_ent_check%FOUND THEN
409 close el_ent_check;
410 hr_utility.set_message('801', 'HR_7790_PAY_DEL_ENTRIES');
411 hr_utility.raise_error;
412 ELSE
413 close el_ent_check;
414 END IF;
415 END IF;
416 --
417 l_after_this_date := fnd_date.date_to_canonical(p_after_this_date);
418 --
419 IF hr_payments.check_pp (to_char (P_PPM_ID), l_after_this_date) THEN
420 DELETE FROM PAY_PERSONAL_PAYMENT_METHODS_F
421 WHERE ROWID = P_ROWID;
422 ELSE
423 hr_utility.set_message('801', 'HR_6498_PAY_DEL_PREPAY');
424 hr_utility.raise_error;
425 END IF;
426 END DELETE_ROW;
427 --
428 PROCEDURE LOCK_ROW(P_ROWID VARCHAR2,
429 P_PERSONAL_PAYMENT_METHOD_ID NUMBER,
430 P_EFFECTIVE_START_DATE DATE,
431 P_EFFECTIVE_END_DATE DATE,
432 P_BUSINESS_GROUP_ID NUMBER,
433 P_EXTERNAL_ACCOUNT_ID NUMBER,
434 P_ASSIGNMENT_ID NUMBER,
435 P_PERSON_ID NUMBER,
436 P_RUN_TYPE_ID NUMBER,
437 P_ORG_PAYMENT_METHOD_ID NUMBER,
438 P_AMOUNT NUMBER,
439 P_COMMENT_ID NUMBER,
440 P_PERCENTAGE NUMBER,
441 P_PRIORITY NUMBER,
442 P_PAYEE_TYPE VARCHAR2,
443 P_PAYEE_ID NUMBER,
444 P_ATTRIBUTE_CATEGORY VARCHAR2,
445 P_ATTRIBUTE1 VARCHAR2,
446 P_ATTRIBUTE2 VARCHAR2,
447 P_ATTRIBUTE3 VARCHAR2,
448 P_ATTRIBUTE4 VARCHAR2,
449 P_ATTRIBUTE5 VARCHAR2,
450 P_ATTRIBUTE6 VARCHAR2,
451 P_ATTRIBUTE7 VARCHAR2,
452 P_ATTRIBUTE8 VARCHAR2,
453 P_ATTRIBUTE9 VARCHAR2,
454 P_ATTRIBUTE10 VARCHAR2,
455 P_ATTRIBUTE11 VARCHAR2,
456 P_ATTRIBUTE12 VARCHAR2,
457 P_ATTRIBUTE13 VARCHAR2,
458 P_ATTRIBUTE14 VARCHAR2,
459 P_ATTRIBUTE15 VARCHAR2,
460 P_ATTRIBUTE16 VARCHAR2,
461 P_ATTRIBUTE17 VARCHAR2,
462 P_ATTRIBUTE18 VARCHAR2,
463 P_ATTRIBUTE19 VARCHAR2,
464 P_ATTRIBUTE20 VARCHAR2,
465 P_PRENOTE_DATE DATE,
466 P_PPM_INFORMATION_CATEGORY VARCHAR2,
467 P_PPM_INFORMATION1 VARCHAR2,
468 P_PPM_INFORMATION2 VARCHAR2,
469 P_PPM_INFORMATION3 VARCHAR2,
470 P_PPM_INFORMATION4 VARCHAR2,
471 P_PPM_INFORMATION5 VARCHAR2,
472 P_PPM_INFORMATION6 VARCHAR2,
473 P_PPM_INFORMATION7 VARCHAR2,
474 P_PPM_INFORMATION8 VARCHAR2,
475 P_PPM_INFORMATION9 VARCHAR2,
476 P_PPM_INFORMATION10 VARCHAR2,
477 P_PPM_INFORMATION11 VARCHAR2,
478 P_PPM_INFORMATION12 VARCHAR2,
479 P_PPM_INFORMATION13 VARCHAR2,
480 P_PPM_INFORMATION14 VARCHAR2,
481 P_PPM_INFORMATION15 VARCHAR2,
482 P_PPM_INFORMATION16 VARCHAR2,
483 P_PPM_INFORMATION17 VARCHAR2,
484 P_PPM_INFORMATION18 VARCHAR2,
488 P_PPM_INFORMATION22 VARCHAR2,
485 P_PPM_INFORMATION19 VARCHAR2,
486 P_PPM_INFORMATION20 VARCHAR2,
487 P_PPM_INFORMATION21 VARCHAR2,
489 P_PPM_INFORMATION23 VARCHAR2,
490 P_PPM_INFORMATION24 VARCHAR2,
491 P_PPM_INFORMATION25 VARCHAR2,
492 P_PPM_INFORMATION26 VARCHAR2,
493 P_PPM_INFORMATION27 VARCHAR2,
494 P_PPM_INFORMATION28 VARCHAR2,
495 P_PPM_INFORMATION29 VARCHAR2,
496 P_PPM_INFORMATION30 VARCHAR2 ) IS
497 CURSOR C IS SELECT *
498 FROM PAY_PERSONAL_PAYMENT_METHODS_F
499 WHERE ROWID = P_ROWID
500 FOR UPDATE OF PERSONAL_PAYMENT_METHOD_ID NOWAIT;
501
502 CURSOR PRENOTE IS SELECT *
503 FROM PAY_EXTERNAL_ACCOUNTS
504 WHERE EXTERNAL_ACCOUNT_ID = P_EXTERNAL_ACCOUNT_ID
505 FOR UPDATE OF PRENOTE_DATE NOWAIT;
506
507 RECINFO C%ROWTYPE;
508 PNINFO PRENOTE%ROWTYPE;
509
510 BEGIN
511 OPEN C;
512 FETCH C INTO RECINFO;
513 CLOSE C;
514 recinfo.payee_type := rtrim(recinfo.payee_type);
515 recinfo.attribute_category := rtrim(recinfo.attribute_category);
516 recinfo.attribute1 := rtrim(recinfo.attribute1);
517 recinfo.attribute2 := rtrim(recinfo.attribute2);
518 recinfo.attribute3 := rtrim(recinfo.attribute3);
519 recinfo.attribute4 := rtrim(recinfo.attribute4);
520 recinfo.attribute5 := rtrim(recinfo.attribute5);
521 recinfo.attribute6 := rtrim(recinfo.attribute6);
522 recinfo.attribute7 := rtrim(recinfo.attribute7);
523 recinfo.attribute8 := rtrim(recinfo.attribute8);
524 recinfo.attribute9 := rtrim(recinfo.attribute9);
525 recinfo.attribute10 := rtrim(recinfo.attribute10);
526 recinfo.attribute11 := rtrim(recinfo.attribute11);
527 recinfo.attribute12 := rtrim(recinfo.attribute12);
528 recinfo.attribute13 := rtrim(recinfo.attribute13);
529 recinfo.attribute14 := rtrim(recinfo.attribute14);
530 recinfo.attribute15 := rtrim(recinfo.attribute15);
531 recinfo.attribute16 := rtrim(recinfo.attribute16);
532 recinfo.attribute17 := rtrim(recinfo.attribute17);
533 recinfo.attribute18 := rtrim(recinfo.attribute18);
534 recinfo.attribute19 := rtrim(recinfo.attribute19);
535 recinfo.attribute20 := rtrim(recinfo.attribute20);
536 recinfo.ppm_information_category := rtrim(recinfo.ppm_information_category);
537 recinfo.ppm_information1 := rtrim(recinfo.ppm_information1);
538 recinfo.ppm_information2 := rtrim(recinfo.ppm_information2);
539 recinfo.ppm_information3 := rtrim(recinfo.ppm_information3);
540 recinfo.ppm_information4 := rtrim(recinfo.ppm_information4);
541 recinfo.ppm_information5 := rtrim(recinfo.ppm_information5);
542 recinfo.ppm_information6 := rtrim(recinfo.ppm_information6);
543 recinfo.ppm_information7 := rtrim(recinfo.ppm_information7);
544 recinfo.ppm_information8 := rtrim(recinfo.ppm_information8);
545 recinfo.ppm_information9 := rtrim(recinfo.ppm_information9);
546 recinfo.ppm_information10 := rtrim(recinfo.ppm_information10);
547 recinfo.ppm_information11 := rtrim(recinfo.ppm_information11);
548 recinfo.ppm_information12 := rtrim(recinfo.ppm_information12);
549 recinfo.ppm_information13 := rtrim(recinfo.ppm_information13);
550 recinfo.ppm_information14 := rtrim(recinfo.ppm_information14);
551 recinfo.ppm_information15 := rtrim(recinfo.ppm_information15);
552 recinfo.ppm_information16 := rtrim(recinfo.ppm_information16);
553 recinfo.ppm_information17 := rtrim(recinfo.ppm_information17);
554 recinfo.ppm_information18 := rtrim(recinfo.ppm_information18);
555 recinfo.ppm_information19 := rtrim(recinfo.ppm_information19);
556 recinfo.ppm_information20 := rtrim(recinfo.ppm_information20);
557 recinfo.ppm_information21 := rtrim(recinfo.ppm_information21);
558 recinfo.ppm_information22 := rtrim(recinfo.ppm_information22);
559 recinfo.ppm_information23 := rtrim(recinfo.ppm_information23);
563 recinfo.ppm_information27 := rtrim(recinfo.ppm_information27);
560 recinfo.ppm_information24 := rtrim(recinfo.ppm_information24);
561 recinfo.ppm_information25 := rtrim(recinfo.ppm_information25);
562 recinfo.ppm_information26 := rtrim(recinfo.ppm_information26);
564 recinfo.ppm_information28 := rtrim(recinfo.ppm_information28);
565 recinfo.ppm_information29 := rtrim(recinfo.ppm_information29);
566 recinfo.ppm_information30 := rtrim(recinfo.ppm_information30);
567
568 IF (
569 ( (P_PERSONAL_PAYMENT_METHOD_ID = RECINFO.PERSONAL_PAYMENT_METHOD_ID)
570 OR ( (P_PERSONAL_PAYMENT_METHOD_ID IS NULL)
571 AND (RECINFO.PERSONAL_PAYMENT_METHOD_ID IS NULL) ) ) AND
572 ( (P_EFFECTIVE_START_DATE = RECINFO.EFFECTIVE_START_DATE)
573 OR ( (P_EFFECTIVE_START_DATE IS NULL)
574 AND (RECINFO.EFFECTIVE_START_DATE IS NULL) ) ) AND
575 ( (P_EFFECTIVE_END_DATE = RECINFO.EFFECTIVE_END_DATE)
576 OR ( (P_EFFECTIVE_END_DATE IS NULL)
577 AND (RECINFO.EFFECTIVE_END_DATE IS NULL) ) ) AND
578 ( (P_BUSINESS_GROUP_ID = RECINFO.BUSINESS_GROUP_ID)
579 OR ( (P_BUSINESS_GROUP_ID IS NULL)
580 AND (RECINFO.BUSINESS_GROUP_ID IS NULL) ) ) AND
584 ( (P_ASSIGNMENT_ID = RECINFO.ASSIGNMENT_ID)
581 ( (P_EXTERNAL_ACCOUNT_ID = RECINFO.EXTERNAL_ACCOUNT_ID)
582 OR ( (P_EXTERNAL_ACCOUNT_ID IS NULL)
583 AND (RECINFO.EXTERNAL_ACCOUNT_ID IS NULL) ) ) AND
585 OR ( (P_ASSIGNMENT_ID IS NULL)
586 AND (RECINFO.ASSIGNMENT_ID IS NULL) ) ) AND
587 ( (P_PERSON_ID = RECINFO.PERSON_ID)
588 OR ( (P_PERSON_ID IS NULL)
589 AND (RECINFO.PERSON_ID IS NULL) ) ) AND
590 ( (P_RUN_TYPE_ID = RECINFO.RUN_TYPE_ID)
591 OR ( (P_RUN_TYPE_ID IS NULL)
592 AND (RECINFO.RUN_TYPE_ID IS NULL) ) ) AND
593 ( (P_ORG_PAYMENT_METHOD_ID = RECINFO.ORG_PAYMENT_METHOD_ID)
594 OR ( (P_ORG_PAYMENT_METHOD_ID IS NULL)
595 AND (RECINFO.ORG_PAYMENT_METHOD_ID IS NULL) ) ) AND
596 ( (P_AMOUNT = RECINFO.AMOUNT)
597 OR ( (P_AMOUNT IS NULL)
598 AND (RECINFO.AMOUNT IS NULL) ) ) AND
599 ( (P_COMMENT_ID = RECINFO.COMMENT_ID)
600 OR ( (P_COMMENT_ID IS NULL)
601 AND (RECINFO.COMMENT_ID IS NULL) ) ) AND
602 ( (P_PERCENTAGE = RECINFO.PERCENTAGE)
603 OR ( (P_PERCENTAGE IS NULL)
604 AND (RECINFO.PERCENTAGE IS NULL) ) ) AND
605 ( (P_PRIORITY = RECINFO.PRIORITY)
606 OR ( (P_PRIORITY IS NULL)
607 AND (RECINFO.PRIORITY IS NULL) ) ) AND
608 ( (P_PAYEE_TYPE = RECINFO.PAYEE_TYPE)
609 OR ( (P_PAYEE_TYPE IS NULL)
610 AND (RECINFO.PAYEE_TYPE IS NULL) ) ) AND
611 ( (P_PAYEE_ID = RECINFO.PAYEE_ID)
612 OR ( (P_PAYEE_ID IS NULL)
613 AND (RECINFO.PAYEE_ID IS NULL) ) ) AND
614 ( (P_ATTRIBUTE_CATEGORY = RECINFO.ATTRIBUTE_CATEGORY)
615 OR ( (P_ATTRIBUTE_CATEGORY IS NULL)
616 AND (RECINFO.ATTRIBUTE_CATEGORY IS NULL) ) ) AND
617 ( (P_ATTRIBUTE1 = RECINFO.ATTRIBUTE1)
618 OR ( (P_ATTRIBUTE1 IS NULL)
619 AND (RECINFO.ATTRIBUTE1 IS NULL) ) ) AND
620 ( (P_ATTRIBUTE2 = RECINFO.ATTRIBUTE2)
621 OR ( (P_ATTRIBUTE2 IS NULL)
622 AND (RECINFO.ATTRIBUTE2 IS NULL) ) ) AND
623 ( (P_ATTRIBUTE3 = RECINFO.ATTRIBUTE3)
624 OR ( (P_ATTRIBUTE3 IS NULL)
625 AND (RECINFO.ATTRIBUTE3 IS NULL) ) ) AND
626 ( (P_ATTRIBUTE4 = RECINFO.ATTRIBUTE4)
627 OR ( (P_ATTRIBUTE4 IS NULL)
628 AND (RECINFO.ATTRIBUTE4 IS NULL) ) ) AND
629 ( (P_ATTRIBUTE5 = RECINFO.ATTRIBUTE5)
630 OR ( (P_ATTRIBUTE5 IS NULL)
631 AND (RECINFO.ATTRIBUTE5 IS NULL) ) ) AND
632 ( (P_ATTRIBUTE6 = RECINFO.ATTRIBUTE6)
633 OR ( (P_ATTRIBUTE6 IS NULL)
634 AND (RECINFO.ATTRIBUTE6 IS NULL) ) ) AND
635 ( (P_ATTRIBUTE7 = RECINFO.ATTRIBUTE7)
636 OR ( (P_ATTRIBUTE7 IS NULL)
637 AND (RECINFO.ATTRIBUTE7 IS NULL) ) ) AND
638 ( (P_ATTRIBUTE8 = RECINFO.ATTRIBUTE8)
639 OR ( (P_ATTRIBUTE8 IS NULL)
640 AND (RECINFO.ATTRIBUTE8 IS NULL) ) ) AND
641 ( (P_ATTRIBUTE9 = RECINFO.ATTRIBUTE9)
642 OR ( (P_ATTRIBUTE9 IS NULL)
643 AND (RECINFO.ATTRIBUTE9 IS NULL) ) ) AND
644 ( (P_ATTRIBUTE10 = RECINFO.ATTRIBUTE10)
645 OR ( (P_ATTRIBUTE10 IS NULL)
646 AND (RECINFO.ATTRIBUTE10 IS NULL) ) ) AND
647 ( (P_ATTRIBUTE11 = RECINFO.ATTRIBUTE11)
648 OR ( (P_ATTRIBUTE11 IS NULL)
649 AND (RECINFO.ATTRIBUTE11 IS NULL) ) ) AND
650 ( (P_ATTRIBUTE12 = RECINFO.ATTRIBUTE12)
651 OR ( (P_ATTRIBUTE12 IS NULL)
652 AND (RECINFO.ATTRIBUTE12 IS NULL) ) ) AND
653 ( (P_ATTRIBUTE13 = RECINFO.ATTRIBUTE13)
654 OR ( (P_ATTRIBUTE13 IS NULL)
655 AND (RECINFO.ATTRIBUTE13 IS NULL) ) ) AND
656 ( (P_ATTRIBUTE14 = RECINFO.ATTRIBUTE14)
657 OR ( (P_ATTRIBUTE14 IS NULL)
658 AND (RECINFO.ATTRIBUTE14 IS NULL) ) ) AND
659 ( (P_ATTRIBUTE15 = RECINFO.ATTRIBUTE15)
660 OR ( (P_ATTRIBUTE15 IS NULL)
661 AND (RECINFO.ATTRIBUTE15 IS NULL) ) ) AND
662 ( (P_ATTRIBUTE16 = RECINFO.ATTRIBUTE16)
663 OR ( (P_ATTRIBUTE16 IS NULL)
664 AND (RECINFO.ATTRIBUTE16 IS NULL) ) ) AND
665 ( (P_ATTRIBUTE17 = RECINFO.ATTRIBUTE17)
666 OR ( (P_ATTRIBUTE17 IS NULL)
667 AND (RECINFO.ATTRIBUTE17 IS NULL) ) ) AND
668 ( (P_ATTRIBUTE18 = RECINFO.ATTRIBUTE18)
669 OR ( (P_ATTRIBUTE18 IS NULL)
670 AND (RECINFO.ATTRIBUTE18 IS NULL) ) ) AND
671 ( (P_ATTRIBUTE19 = RECINFO.ATTRIBUTE19)
672 OR ( (P_ATTRIBUTE19 IS NULL)
673 AND (RECINFO.ATTRIBUTE19 IS NULL) ) ) AND
674 ( (P_ATTRIBUTE20 = RECINFO.ATTRIBUTE20)
675 OR ( (P_ATTRIBUTE20 IS NULL)
676 AND (RECINFO.ATTRIBUTE20 IS NULL) ) ) AND
677 ((recinfo.PPM_INFORMATION_CATEGORY = P_PPM_INFORMATION_CATEGORY)
678 OR ((recinfo.PPM_INFORMATION_CATEGORY is null) AND (P_PPM_INFORMATION_CATEGORY is null)))
679 AND ((recinfo.PPM_INFORMATION1 = P_PPM_INFORMATION1)
680 OR ((recinfo.PPM_INFORMATION1 is null) AND (P_PPM_INFORMATION1 is null)))
681 AND ((recinfo.PPM_INFORMATION2 = P_PPM_INFORMATION2)
682 OR ((recinfo.PPM_INFORMATION2 is null) AND (P_PPM_INFORMATION2 is null)))
683 AND ((recinfo.PPM_INFORMATION3 = P_PPM_INFORMATION3)
684 OR ((recinfo.PPM_INFORMATION3 is null) AND (P_PPM_INFORMATION3 is null)))
685 AND ((recinfo.PPM_INFORMATION4 = P_PPM_INFORMATION4)
686 OR ((recinfo.PPM_INFORMATION4 is null) AND (P_PPM_INFORMATION4 is null)))
687 AND ((recinfo.PPM_INFORMATION5 = P_PPM_INFORMATION5)
688 OR ((recinfo.PPM_INFORMATION5 is null) AND (P_PPM_INFORMATION5 is null)))
689 AND ((recinfo.PPM_INFORMATION6 = P_PPM_INFORMATION6)
693 AND ((recinfo.PPM_INFORMATION8 = P_PPM_INFORMATION8)
690 OR ((recinfo.PPM_INFORMATION6 is null) AND (P_PPM_INFORMATION6 is null)))
691 AND ((recinfo.PPM_INFORMATION7 = P_PPM_INFORMATION7)
692 OR ((recinfo.PPM_INFORMATION7 is null) AND (P_PPM_INFORMATION7 is null)))
694 OR ((recinfo.PPM_INFORMATION8 is null) AND (P_PPM_INFORMATION8 is null)))
695 AND ((recinfo.PPM_INFORMATION9 = P_PPM_INFORMATION9)
696 OR ((recinfo.PPM_INFORMATION9 is null) AND (P_PPM_INFORMATION9 is null)))
697 AND ((recinfo.PPM_INFORMATION10 = P_PPM_INFORMATION10)
698 OR ((recinfo.PPM_INFORMATION10 is null) AND (P_PPM_INFORMATION10 is null)))
699 AND ((recinfo.PPM_INFORMATION11 = P_PPM_INFORMATION11)
700 OR ((recinfo.PPM_INFORMATION11 is null) AND (P_PPM_INFORMATION11 is null)))
701 AND ((recinfo.PPM_INFORMATION12 = P_PPM_INFORMATION12)
702 OR ((recinfo.PPM_INFORMATION12 is null) AND (P_PPM_INFORMATION12 is null)))
703 AND ((recinfo.PPM_INFORMATION13 = P_PPM_INFORMATION13)
704 OR ((recinfo.PPM_INFORMATION13 is null) AND (P_PPM_INFORMATION13 is null)))
705 AND ((recinfo.PPM_INFORMATION14 = P_PPM_INFORMATION14)
706 OR ((recinfo.PPM_INFORMATION14 is null) AND (P_PPM_INFORMATION14 is null)))
707 AND ((recinfo.PPM_INFORMATION15 = P_PPM_INFORMATION15)
708 OR ((recinfo.PPM_INFORMATION15 is null) AND (P_PPM_INFORMATION15 is null)))
709 AND ((recinfo.PPM_INFORMATION16 = P_PPM_INFORMATION16)
710 OR ((recinfo.PPM_INFORMATION16 is null) AND (P_PPM_INFORMATION16 is null)))
711 AND ((recinfo.PPM_INFORMATION17 = P_PPM_INFORMATION17)
712 OR ((recinfo.PPM_INFORMATION17 is null) AND (P_PPM_INFORMATION17 is null)))
713 AND ((recinfo.PPM_INFORMATION18 = P_PPM_INFORMATION18)
714 OR ((recinfo.PPM_INFORMATION18 is null) AND (P_PPM_INFORMATION18 is null)))
715 AND ((recinfo.PPM_INFORMATION19 = P_PPM_INFORMATION19)
716 OR ((recinfo.PPM_INFORMATION19 is null) AND (P_PPM_INFORMATION19 is null)))
717 AND ((recinfo.PPM_INFORMATION20 = P_PPM_INFORMATION20)
718 OR ((recinfo.PPM_INFORMATION20 is null) AND (P_PPM_INFORMATION20 is null)))
719 AND ((recinfo.PPM_INFORMATION21 = P_PPM_INFORMATION21)
720 OR ((recinfo.PPM_INFORMATION21 is null) AND (P_PPM_INFORMATION21 is null)))
721 AND ((recinfo.PPM_INFORMATION22 = P_PPM_INFORMATION22)
722 OR ((recinfo.PPM_INFORMATION22 is null) AND (P_PPM_INFORMATION22 is null)))
723 AND ((recinfo.PPM_INFORMATION23 = P_PPM_INFORMATION23)
724 OR ((recinfo.PPM_INFORMATION23 is null) AND (P_PPM_INFORMATION23 is null)))
725 AND ((recinfo.PPM_INFORMATION24 = P_PPM_INFORMATION24)
726 OR ((recinfo.PPM_INFORMATION24 is null) AND (P_PPM_INFORMATION24 is null)))
727 AND ((recinfo.PPM_INFORMATION25 = P_PPM_INFORMATION25)
728 OR ((recinfo.PPM_INFORMATION25 is null) AND (P_PPM_INFORMATION25 is null)))
729 AND ((recinfo.PPM_INFORMATION26 = P_PPM_INFORMATION26)
730 OR ((recinfo.PPM_INFORMATION26 is null) AND (P_PPM_INFORMATION26 is null)))
731 AND ((recinfo.PPM_INFORMATION27 = P_PPM_INFORMATION27)
732 OR ((recinfo.PPM_INFORMATION27 is null) AND (P_PPM_INFORMATION27 is null)))
733 AND ((recinfo.PPM_INFORMATION28 = P_PPM_INFORMATION28)
734 OR ((recinfo.PPM_INFORMATION28 is null) AND (P_PPM_INFORMATION28 is null)))
735 AND ((recinfo.PPM_INFORMATION29 = P_PPM_INFORMATION29)
736 OR ((recinfo.PPM_INFORMATION29 is null) AND (P_PPM_INFORMATION29 is null)))
737 AND ((recinfo.PPM_INFORMATION30 = P_PPM_INFORMATION30)
738 OR ((recinfo.PPM_INFORMATION30 is null) AND (P_PPM_INFORMATION30 is null)))
739 ) THEN
740 --
741 -- Now take out the lock on the external_account record in
742 -- in case the prenote_date gets changed.
743 --
744 open prenote;
745 fetch prenote into pninfo;
746 if prenote%notfound then
747 --
748 -- no problem: pay method is not prenoted. Can return now.
749 --
750 close prenote;
751 return;
752 else
753 close prenote;
754 end if;
755 if ( (P_PRENOTE_DATE = PNINFO.PRENOTE_DATE)
756 OR ( (P_PRENOTE_DATE IS NULL)
757 AND (PNINFO.PRENOTE_DATE IS NULL) ) ) THEN
758 --
759 -- OK. Neither the pay method rec nor the external a/c rec has
760 -- changed, so we can return with success.
761 --
762 return;
763 else
764 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
765 APP_EXCEPTION.RAISE_EXCEPTION;
766 end if;
767 else
768 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
769 APP_EXCEPTION.RAISE_EXCEPTION;
770 end if;
771 END LOCK_ROW;
772 --
773 procedure check_asg_on_payroll (p_asg_id varchar2,
774 p_new_date varchar2) is
775 --
776 l_payroll_id number(9);
777 --
778 -- This procedure is called when changing session date, to make sure the
779 -- asg still has a payroll as of the new session date. If it hasn't, the
780 -- user is not meant to have access to the form, so raise an error and
781 -- refuse to allow the date change. #293382.
782 --
783 cursor payroll_on_new_date is
784 select payroll_id
785 from per_all_assignments_f
786 where assignment_id = to_number (p_asg_id)
787 and fnd_date.canonical_to_date(p_new_date) between effective_start_date
788 and effective_end_date;
789 --
790 begin
791 open payroll_on_new_date;
792 fetch payroll_on_new_date into l_payroll_id;
793 --
794 -- check for the case that the asg simply doesn't exist at the date
795 --
796 if payroll_on_new_date%notfound then
797 close payroll_on_new_date;
798 hr_utility.set_message('801', 'HR_51029_ASG_NO_PAYROLL');
802 --
799 hr_utility.raise_error;
800 else
801 close payroll_on_new_date;
803 -- the asg exists: now check for a null payroll
804 --
805 if l_payroll_id is null then
806 hr_utility.set_message('801', 'HR_51029_ASG_NO_PAYROLL');
807 hr_utility.raise_error;
808 end if;
809 end if;
810 end check_asg_on_payroll;
811 --
812 END PAY_PAYMENT_METHODS_PKG;