[Home] [Help]
PACKAGE BODY: APPS.PAY_PAYMENT_TYPES_PKG
Source
1 PACKAGE BODY PAY_PAYMENT_TYPES_PKG as
2 /* $Header: pypyt01t.pkb 120.0.12010000.2 2008/08/06 08:14:49 ubhat ship $ */
3 g_dummy number(1);
4
5 PROCEDURE Is_Unique(X_Rowid VARCHAR2,X_Payment_Type_Name VARCHAR2,X_Territory_Code VARCHAR2) IS
6 result varchar2(255);
7 Begin
8 SELECT NULL INTO result
9 FROM PAY_PAYMENT_TYPES
10 WHERE UPPER(payment_type_name) = UPPER(X_Payment_Type_Name)
11 AND UPPER(Territory_Code) = UPPER(X_Territory_Code)
12 AND (Rowid <> X_Rowid OR X_Rowid is NULL);
13 IF (SQL%FOUND) THEN
14 hr_utility.set_message(801,'HR_6714_PAYM_ALREADY_EXISTS');
15 hr_utility.raise_error;
16 END IF;
17 EXCEPTION
18 when NO_DATA_FOUND then
19 null;
20 END Is_Unique;
21 --
22 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
23 X_Payment_Type_Id IN OUT NOCOPY NUMBER,
24 X_Territory_Code VARCHAR2,
25 X_Currency_Code VARCHAR2,
26 X_Category VARCHAR2,
27 X_Payment_Type_Name VARCHAR2,
28 -- --
29 X_Base_Payment_Type_Name VARCHAR2,
30 -- --
31 X_Allow_As_Default VARCHAR2,
32 X_Description VARCHAR2,
33 X_Pre_Validation_Required VARCHAR2,
34 X_Procedure_Name VARCHAR2,
35 X_Validation_Days NUMBER,
36 X_Validation_Value VARCHAR2
37 ) IS
38 CURSOR C IS SELECT rowid FROM PAY_PAYMENT_TYPES
39
40 WHERE payment_type_id = X_Payment_Type_Id;
41 --
42 l_max_id pay_payment_types.payment_type_id%type;
43 BEGIN
44 --
45 Is_Unique(X_Rowid,X_Payment_Type_Name,X_Territory_Code);
46 SELECT Pay_Payment_Types_s.nextval
47 INTO X_Payment_Type_Id
48 FROM dual;
49
50 /* Defensive coding to prevent duplicate primary keys.
51 We've seen issues where the sequence on payment_type has been
52 reset and we end up selecting a sequence value which already
53 exists on the table. */
54
55 SELECT nvl(max(payment_type_id),0)
56 INTO l_max_id
57 FROM pay_payment_types;
58
59 WHILE X_Payment_Type_Id <= l_max_id LOOP
60 SELECT Pay_Payment_Types_s.nextval
61 INTO X_Payment_Type_Id
62 FROM dual;
63 END LOOP;
64
65 INSERT INTO PAY_PAYMENT_TYPES(
66 payment_type_id,
67 territory_code,
68 currency_code,
69 category,
70 payment_type_name,
71 allow_as_default,
72 description,
73 pre_validation_required,
74 procedure_name,
75 validation_days,
76 validation_value
77 ) VALUES (
78 X_Payment_Type_Id,
79 X_Territory_Code,
80 X_Currency_Code,
81 X_Category,
82 --X_Payment_Type_Name,
83 -- --
84 X_Base_Payment_Type_Name,
85 -- --
86 X_Allow_As_Default,
87 X_Description,
88 X_Pre_Validation_Required,
89 X_Procedure_Name,
90 X_Validation_Days,
91 X_Validation_Value
92 );
93 --
94 -- **************************************************************************
95 -- insert into MLS table (TL)
96 --
97 insert into PAY_PAYMENT_TYPES_TL (
98 PAYMENT_TYPE_ID,
99 PAYMENT_TYPE_NAME,
100 DESCRIPTION,
101 LAST_UPDATE_DATE,
102 CREATION_DATE,
103 LANGUAGE,
104 SOURCE_LANG
105 ) select
106 X_PAYMENT_TYPE_ID,
107 X_PAYMENT_TYPE_NAME,
108 X_DESCRIPTION,
109 sysdate,
110 sysdate,
111 L.LANGUAGE_CODE,
112 userenv('LANG')
113 from FND_LANGUAGES L
114 where L.INSTALLED_FLAG in ('I', 'B')
115 and not exists
116 (select NULL
117 from PAY_PAYMENT_TYPES_TL T
118 where T.PAYMENT_TYPE_ID = X_PAYMENT_TYPE_ID
119 and T.LANGUAGE = L.LANGUAGE_CODE);
120 --
121 --
122 -- *******************************************************************************
123 --
124 OPEN C;
125 FETCH C INTO X_Rowid;
126 if (C%NOTFOUND) then
127 CLOSE C;
128 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
129 hr_utility.set_message_token('PROCEDURE','Insert_Row');
130 hr_utility.set_message_token('STEP','1');
131 hr_utility.raise_error;
132 end if;
133 CLOSE C;
134 END Insert_Row;
135
136 procedure validate_translation(payment_type_id IN NUMBER,
137 language IN VARCHAR2,
138 payment_type_name IN VARCHAR2,
139 description IN VARCHAR2) IS
140 /*
141
142 This procedure is used to ensure uniqueness of translated payment type names.
143 It fails if a payment type translation is already present in
144 the table for a given language. Otherwise, no action is performed.
145
146 Two cursors are required, in case the user does not commit the base table
147 record before opening the MLS widget and entering a translation.
148
149 */
150
151 --
152 -- This cursor is used if there isn't a valid payment_type_id
153 -- In this case, we know that the record hasn't yet made it into the
154 -- database, so no existing translated payment type name should have
155 -- the same name.
156 --
157
158 cursor c_translation_exists(p_language IN VARCHAR2,
159 p_payment_type_name IN VARCHAR2) IS
160 SELECT 1
161 FROM pay_payment_types_tl
162 WHERE language = p_language
163 AND upper(payment_type_name) = upper(p_payment_type_name);
164
165 --
166 -- The second cursor implements the validation we actually require,
167 -- but this will only work if the record exists in the db already,
168 -- and we have a primary key id.
169 --
170
171 cursor c_trans_check(p_language IN VARCHAR2,
172 p_payment_type_name IN VARCHAR2,
173 p_payment_type_id IN NUMBER) IS
174 SELECT 1
175 FROM pay_payment_types_tl ptt,
176 pay_payment_types pty
177 WHERE upper(ptt.payment_type_name)=upper(p_payment_type_name)
178 AND ptt.payment_type_id = pty.payment_type_id
179 AND ptt.language = p_language
180 AND pty.payment_type_id <> p_payment_type_id;
181
182 l_package_name VARCHAR2(80) := 'PAY_PAYMENT_TYPES_PKG.VALIDATE_TRANSLATION';
183
184 BEGIN
185
186 hr_utility.set_location (l_package_name,10);
187
188 IF (payment_type_id IS NOT NULL) THEN
189 -- We know this record is in the database, and can use
190 -- full validation
191 OPEN c_trans_check(language, payment_type_name,payment_type_id);
192 hr_utility.set_location (l_package_name,20);
193 FETCH c_trans_check INTO g_dummy;
194
195 IF c_trans_check%NOTFOUND THEN
196 hr_utility.set_location (l_package_name,30);
197 CLOSE c_trans_check;
198 ELSE
199 hr_utility.set_location (l_package_name,40);
200 CLOSE c_trans_check;
201 fnd_message.set_name('PAY','HR_TRANSLATION_EXISTS');
202 fnd_message.raise_error;
203 END IF;
204 ELSE
205 OPEN c_translation_exists(language, payment_type_name);
206 hr_utility.set_location (l_package_name,50);
207 FETCH c_translation_exists INTO g_dummy;
208
209 IF c_translation_exists%NOTFOUND THEN
210 hr_utility.set_location (l_package_name,60);
211 CLOSE c_translation_exists;
212 ELSE
213 hr_utility.set_location (l_package_name,70);
214 CLOSE c_translation_exists;
215 fnd_message.set_name('PAY','HR_TRANSLATION_EXISTS');
216 fnd_message.raise_error;
217 END IF;
218 END IF;
219 hr_utility.set_location ('Leaving:'||l_package_name,80);
220
221 END validate_translation;
222
223 --
224 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
225 X_Payment_Type_Id NUMBER,
226 X_Territory_Code VARCHAR2,
227 X_Currency_Code VARCHAR2,
228 X_Category VARCHAR2,
229 --X_Payment_Type_Name VARCHAR2,
230 -- --
231 X_Base_Payment_Type_Name VARCHAR2,
232 -- --
233 X_Allow_As_Default VARCHAR2,
234 X_Description VARCHAR2,
235 X_Pre_Validation_Required VARCHAR2,
236 X_Procedure_Name VARCHAR2,
237 X_Validation_Days NUMBER,
238 X_Validation_Value VARCHAR2
239 ) IS
240 CURSOR C IS
241 SELECT *
242 FROM PAY_PAYMENT_TYPES
243 WHERE rowid = X_Rowid
244 FOR UPDATE of Payment_Type_Id NOWAIT;
245 Recinfo C%ROWTYPE;
246 --
247 -- ***************************************************************************
248 -- cursor for MLS
249 --
250 cursor csr_payment_type_tl is select
251 PAYMENT_TYPE_NAME,
252 DESCRIPTION,
253 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
254 from PAY_PAYMENT_TYPES_TL
255 where PAYMENT_TYPE_ID = X_PAYMENT_TYPE_ID
256 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
257 for update of PAYMENT_TYPE_ID nowait;
258 --
259 -- ***************************************************************************
260 --
261 l_mls_count NUMBER :=0;
262 --
263 BEGIN
264 OPEN C;
265 FETCH C INTO Recinfo;
266 if (C%NOTFOUND) then
267 CLOSE C;
268 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
269 hr_utility.set_message_token('PROCEDURE','Lock_Row');
270 hr_utility.set_message_token('STEP','1');
271 hr_utility.raise_error;
272 end if;
273 CLOSE C;
274 --
275 /** sbilling **/
276 -- removed explicit lock of _TL table,
277 -- the MLS strategy requires that the base table is locked before update of the
278 -- _TL table can take place,
279 -- which implies it is not necessary to lock both tables.
280 -- ***************************************************************************
281 -- code for MLS
282 --
283 -- for tlinfo in csr_payment_type_tl LOOP
284 -- l_mls_count := l_mls_count+1;
285 -- if (tlinfo.BASELANG = 'Y') then
286 -- if ((tlinfo.PAYMENT_TYPE_NAME = X_PAYMENT_TYPE_NAME)
287 -- AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
288 -- OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
289 -- ) then
290 -- null;
291 -- else
292 -- fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
293 -- app_exception.raise_exception;
294 -- end if;
295 -- end if;
296 -- end loop;
297 ----
298 --if (l_mls_count=0) then -- Trap system errors
299 -- hr_utility.set_message (801,'HR_6153_ALL_PROCEDURE_FAIL');
300 -- hr_utility.set_message_token ('PROCEDURE','PAY_PAYMENT_TYPES_PKG.LOCK_TL_ROW');
301 --end if;
302 --
303 -- ***************************************************************************
304 --
305 recinfo.territory_code := rtrim(recinfo.territory_code);
306 recinfo.currency_code := rtrim(recinfo.currency_code);
307 recinfo.category := rtrim(recinfo.category);
308 recinfo.payment_type_name := rtrim(recinfo.payment_type_name);
309 recinfo.allow_as_default := rtrim(recinfo.allow_as_default);
310 recinfo.description := rtrim(recinfo.description);
311 recinfo.pre_validation_required := rtrim(recinfo.pre_validation_required);
312 recinfo.procedure_name := rtrim(recinfo.procedure_name);
313 recinfo.validation_value := rtrim(recinfo.validation_value); --
314 if (
315 ( (Recinfo.payment_type_id = X_Payment_Type_Id)
316 OR ( (Recinfo.payment_type_id IS NULL)
317 AND (X_Payment_Type_Id IS NULL)))
318 AND ( (Recinfo.territory_code = X_Territory_Code)
319 OR ( (Recinfo.territory_code IS NULL)
320 AND (X_Territory_Code IS NULL)))
321 AND ( (Recinfo.currency_code = X_Currency_Code)
322 OR ( (Recinfo.currency_code IS NULL)
323 AND (X_Currency_Code IS NULL)))
324 AND ( (Recinfo.category = X_Category)
325 OR ( (Recinfo.category IS NULL)
326 AND (X_Category IS NULL)))
327 -- AND ( (Recinfo.payment_type_name = X_Payment_Type_Name)
328 -- OR ( (Recinfo.payment_type_name IS NULL)
329 -- AND (X_Payment_Type_Name IS NULL)))
330 -- --
331 AND ( (Recinfo.payment_type_name = X_Base_Payment_Type_Name)
332 OR ( (Recinfo.payment_type_name IS NULL)
333 AND (X_Base_Payment_Type_Name IS NULL)))
334 -- --
335 AND ( (Recinfo.allow_as_default = X_Allow_As_Default)
336 OR ( (Recinfo.allow_as_default IS NULL)
337 AND (X_Allow_As_Default IS NULL)))
338 AND ( (Recinfo.description = X_Description)
339 OR ( (Recinfo.description IS NULL)
340 AND (X_Description IS NULL)))
341 AND ( (Recinfo.pre_validation_required = X_Pre_Validation_Required)
342 OR ( (Recinfo.pre_validation_required IS NULL)
343 AND (X_Pre_Validation_Required IS NULL)))
344 AND ( (Recinfo.procedure_name = X_Procedure_Name)
345 OR ( (Recinfo.procedure_name IS NULL)
346 AND (X_Procedure_Name IS NULL)))
347 AND ( (Recinfo.validation_days = X_Validation_Days)
348 OR ( (Recinfo.validation_days IS NULL)
349 AND (X_Validation_Days IS NULL)))
350 AND ( (Recinfo.validation_value = X_Validation_Value)
354 return;
351 OR ( (Recinfo.validation_value IS NULL)
352 AND (X_Validation_Value IS NULL)))
353 ) then
355 else
356 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
357 APP_EXCEPTION.RAISE_EXCEPTION;
358 end if;
359 END Lock_Row;
360
361 PROCEDURE Update_Row(X_Rowid VARCHAR2,
362 X_Payment_Type_Id NUMBER,
363 X_Territory_Code VARCHAR2,
364 X_Currency_Code VARCHAR2,
365 X_Category VARCHAR2,
366 X_Payment_Type_Name VARCHAR2,
367 X_Allow_As_Default VARCHAR2,
368 X_Description VARCHAR2,
369 X_Pre_Validation_Required VARCHAR2,
370 X_Procedure_Name VARCHAR2,
371 X_Validation_Days NUMBER,
372 X_Validation_Value VARCHAR2,
373 X_Base_Payment_Type_Name VARCHAR2
374 ) IS
375 BEGIN
376 --
377 Is_Unique(X_Rowid,X_Payment_Type_Name,X_Territory_Code);
378 UPDATE PAY_PAYMENT_TYPES
379 SET
380
381 payment_type_id = X_Payment_Type_Id,
382 territory_code = X_Territory_Code,
383 currency_code = X_Currency_Code,
384 category = X_Category,
385 -- --
386 --payment_type_name = X_Payment_Type_Name,
387 -- --
388 -- -- for bug # 2511059
389 payment_type_name = X_Base_Payment_Type_Name,
390 -- --
391 allow_as_default = X_Allow_As_Default,
392 description = X_Description,
393 pre_validation_required = X_Pre_Validation_Required,
394 procedure_name = X_Procedure_Name,
395 validation_days = X_Validation_Days,
396 validation_value = X_Validation_Value
397 WHERE rowid = X_rowid;
398
399 if (SQL%NOTFOUND) then
400 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
401 hr_utility.set_message_token('PROCEDURE','Update_Row');
402 hr_utility.set_message_token('STEP','1');
403 hr_utility.raise_error;
404 end if;
405 --
406 -- ****************************************************************************************
407 --
408 -- update MLS table (TL)
409 --
410 update PAY_PAYMENT_TYPES_TL
411 set PAYMENT_TYPE_NAME = X_PAYMENT_TYPE_NAME,
412 DESCRIPTION = X_DESCRIPTION,
413 LAST_UPDATE_DATE = sysdate,
414 SOURCE_LANG = userenv('LANG')
415 where PAYMENT_TYPE_ID = X_PAYMENT_TYPE_ID
416 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
417 --
418 if (sql%notfound) then -- trap system errors during update
419 hr_utility.set_message (801,'HR_6153_ALL_PROCEDURE_FAIL');
420 hr_utility.set_message_token ('PROCEDURE','PAY_PAYMENT_TYPES_PKG.UPDATE_TL_ROW');
421 end if;
422 --
423 -- ***************************************************************************************
424 --
425 END Update_Row;
426
427 PROCEDURE Delete_Row(X_payment_type_id NUMBER, X_Rowid VARCHAR2) IS
428 BEGIN
429 DELETE FROM PAY_PAYMENT_TYPES
430 WHERE rowid = X_Rowid;
431
432 if (SQL%NOTFOUND) then
433 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
434 hr_utility.set_message_token('PROCEDURE','Delete_Row');
435 hr_utility.set_message_token('STEP','1');
436 hr_utility.raise_error;
437 end if;
438 --
439 -- ********************************************************************************
440 --
441 -- delete from MLS table (TL)
442 --
443 delete from PAY_PAYMENT_TYPES_TL
444 where PAYMENT_TYPE_ID = X_PAYMENT_TYPE_ID;
445 --
446 if sql%notfound then -- trap system errors during deletion
447 hr_utility.set_message (801,'HR_6153_ALL_PROCEDURE_FAIL');
448 hr_utility.set_message_token ('PROCEDURE','PAY_PAYMENT_TYPES_PKG.DELETE_TL_ROW');
449 end if;
450 --
451 -- ********************************************************************************
452 --
453 END Delete_Row;
454
455 ----------------------------------------------------------------------------------
456 procedure ADD_LANGUAGE
457 is
458 begin
459 delete from PAY_PAYMENT_TYPES_TL T
460 where not exists
461 (select NULL
462 from PAY_PAYMENT_TYPES B
463 where B.PAYMENT_TYPE_ID = T.PAYMENT_TYPE_ID
464 );
465
466 update PAY_PAYMENT_TYPES_TL T set (
467 PAYMENT_TYPE_NAME,
468 DESCRIPTION
469 ) = (select
470 B.PAYMENT_TYPE_NAME,
471 B.DESCRIPTION
472 from PAY_PAYMENT_TYPES_TL B
473 where B.PAYMENT_TYPE_ID = T.PAYMENT_TYPE_ID
474 and B.LANGUAGE = T.SOURCE_LANG)
475 where (
476 T.PAYMENT_TYPE_ID,
477 T.LANGUAGE
478 ) in (select
479 SUBT.PAYMENT_TYPE_ID,
480 SUBT.LANGUAGE
481 from PAY_PAYMENT_TYPES_TL SUBB, PAY_PAYMENT_TYPES_TL SUBT
482 where SUBB.PAYMENT_TYPE_ID = SUBT.PAYMENT_TYPE_ID
486 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
483 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
484 and (SUBB.PAYMENT_TYPE_NAME <> SUBT.PAYMENT_TYPE_NAME
485 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
487 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
488 ));
489
490 insert into PAY_PAYMENT_TYPES_TL (
491 PAYMENT_TYPE_ID,
492 PAYMENT_TYPE_NAME,
493 DESCRIPTION,
494 LAST_UPDATE_DATE,
495 LAST_UPDATED_BY,
496 LAST_UPDATE_LOGIN,
497 CREATED_BY,
498 CREATION_DATE,
499 LANGUAGE,
500 SOURCE_LANG
501 ) select
502 B.PAYMENT_TYPE_ID,
503 B.PAYMENT_TYPE_NAME,
504 B.DESCRIPTION,
505 B.LAST_UPDATE_DATE,
506 B.LAST_UPDATED_BY,
507 B.LAST_UPDATE_LOGIN,
508 B.CREATED_BY,
509 B.CREATION_DATE,
510 L.LANGUAGE_CODE,
511 B.SOURCE_LANG
512 from PAY_PAYMENT_TYPES_TL B, FND_LANGUAGES L
513 where L.INSTALLED_FLAG in ('I', 'B')
514 and B.LANGUAGE = userenv('LANG')
515 and not exists
516 (select NULL
517 from PAY_PAYMENT_TYPES_TL T
518 where T.PAYMENT_TYPE_ID = B.PAYMENT_TYPE_ID
519 and T.LANGUAGE = L.LANGUAGE_CODE);
520 end ADD_LANGUAGE;
521 ------------------------------------------------------------------------------
522 procedure unique_chk(x_payment_type_name in VARCHAR2,x_territory_code in VARCHAR2)
523 is
524 result varchar2(255);
525 Begin
526 SELECT count(*) INTO result
527 FROM PAY_PAYMENT_TYPES
528 WHERE UPPER(payment_type_name) = UPPER(X_Payment_Type_Name)
529 AND UPPER(territory_code) = UPPER(x_territory_code);
530 --
531 IF (result>1) THEN
532 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
533 hr_utility.set_message_token('PROCEDURE','PAY_PAYMENT_TYPES_PKG.UNIQUE_CHK');
534 hr_utility.set_message_token('STEP','1');
535 hr_utility.raise_error;
536 END IF;
537 EXCEPTION
538 when NO_DATA_FOUND then
539 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
540 hr_utility.set_message_token('PROCEDURE','PAY_PAYMENT_TYPES_PKG.UNIQUE_CHK');
541 hr_utility.set_message_token('STEP','1');
542 hr_utility.raise_error;
543 end unique_chk;
544 --
545 procedure TRANSLATE_ROW(x_b_payment_type_name in VARCHAR2,
546 x_territory_code in VARCHAR2,
547 x_payment_type_name in VARCHAR2,
548 x_owner in VARCHAR2,
549 x_description in VARCHAR2)
550 is
551 begin
552 -- unique_chk(x_b_payment_type_name,x_territory_code);
553 --
554 UPDATE pay_payment_types_tl
555 SET description = nvl(x_description,description),
556 payment_type_name = nvl(x_payment_type_name,payment_type_name),
557 last_update_date = SYSDATE,
558 last_updated_by = decode(x_owner,'SEED',1,0),
559 last_update_login = 0,
560 source_lang = userenv('LANG')
561 WHERE userenv('LANG') IN (language,source_lang)
562 AND payment_type_id IN
563 (SELECT PPT.PAYMENT_TYPE_ID
564 FROM pay_payment_types ppt
565 WHERE nvl(upper(x_territory_code),'~null~') = nvl(upper(ppt.territory_code),'~null~')
566 AND nvl(upper(x_b_payment_type_name),'~null~') = nvl(upper(ppt.payment_type_name),'~null~'));
567 --
568 if (sql%notfound) then -- trap system errors during update
569 -- hr_utility.set_message (801,'HR_6153_ALL_PROCEDURE_FAIL');
570 -- hr_utility.set_message_token ('PROCEDURE','PAY_PAYMENT_TYPES_PKG.TRANSLATE_ROW');
571 -- hr_utility.set_message_token('STEP','1');
572 -- hr_utility.raise_error;
573 null;
574 end if;
575 end TRANSLATE_ROW;
576 ------------------------------------------------------------------------------
577 procedure LOAD_ROW(x_b_payment_type_name in VARCHAR2,
578 x_territory_code in VARCHAR2,
579 x_currency_code in VARCHAR2,
580 x_category in VARCHAR2,
581 x_allow_as_default in VARCHAR2,
582 x_pre_validation_required in VARCHAR2,
583 x_procedure_name in VARCHAR2,
584 x_validation_days in NUMBER,
585 x_validation_value in VARCHAR2,
586 x_payment_type_name in VARCHAR2,
587 x_owner in VARCHAR2,
588 x_description in VARCHAR2)
589 is
590 X_PAYMENT_TYPE_ID NUMBER(9);
591 CURSOR C IS SELECT PAYMENT_TYPE_ID FROM PAY_PAYMENT_TYPES
592 WHERE payment_type_id = X_PAYMENT_TYPE_ID;
593 begin
594 -- unique_chk(x_b_payment_type_name,x_territory_code);
595 --
596 UPDATE pay_payment_types
597 SET description = nvl(x_description,description),
598 -- payment_type_name = nvl(x_payment_type_name,payment_type_name),
599 last_update_date = SYSDATE,
600 last_updated_by = decode(x_owner,'SEED',1,0),
601 last_update_login = 0,
602 currency_code = nvl(x_currency_code,currency_code),
603 category = x_category,
604 allow_as_default =nvl(x_allow_as_default,allow_as_default),
605 pre_validation_required = nvl(x_pre_validation_required,pre_validation_required),
606 procedure_name = nvl(x_procedure_name,procedure_name),
607 validation_days = nvl(x_validation_days,validation_days),
608 validation_value = nvl(x_validation_value,validation_value),
609 territory_code = nvl(x_territory_code,territory_code)
610 WHERE nvl(upper(x_territory_code),'~null~') = nvl(upper(territory_code),'~null~')
611 AND nvl(upper(x_b_payment_type_name),'~null~') = nvl(upper(payment_type_name),'~null~');
612 --
613 -- exception
614 -- when NO_DATA_FOUND then
615 if (SQL%rowcount = 0) then
616 SELECT pay_payment_types_s.nextval
617 INTO X_PAYMENT_TYPE_ID
618 FROM dual;
619 INSERT INTO pay_payment_types(
620 PAYMENT_TYPE_ID,
621 TERRITORY_CODE,
622 CURRENCY_CODE,
623 CATEGORY,
624 PAYMENT_TYPE_NAME,
625 ALLOW_AS_DEFAULT,
626 DESCRIPTION,
627 PRE_VALIDATION_REQUIRED,
628 PROCEDURE_NAME,
629 VALIDATION_DAYS,
630 VALIDATION_VALUE,
631 last_update_date,
632 last_updated_by,
633 last_update_login,
634 created_by,
635 creation_date
636 )VALUES(
637 X_PAYMENT_TYPE_ID,
638 X_TERRITORY_CODE,
639 X_CURRENCY_CODE,
640 X_CATEGORY,
641 X_B_PAYMENT_TYPE_NAME,
642 X_ALLOW_AS_DEFAULT,
643 X_DESCRIPTION,
644 X_PRE_VALIDATION_REQUIRED,
645 X_PROCEDURE_NAME,
646 X_VALIDATION_DAYS,
647 X_VALIDATION_VALUE,
648 SYSDATE,
649 decode(x_owner,'SEED',1,0),
650 0,
651 decode(x_owner,'SEED',1,0),
652 SYSDATE
653 );
654 INSERT INTO pay_payment_types_tl(
655 PAYMENT_TYPE_ID,
656 PAYMENT_TYPE_NAME,
657 DESCRIPTION,
658 LANGUAGE,
659 SOURCE_LANG,
660 LAST_UPDATE_DATE,
661 LAST_UPDATED_BY,
662 LAST_UPDATE_LOGIN,
663 CREATED_BY,
664 CREATION_DATE
665 ) select
666 X_PAYMENT_TYPE_ID,
667 X_PAYMENT_TYPE_NAME,
668 X_DESCRIPTION,
669 L.LANGUAGE_CODE,
670 userenv('LANG'),
671 SYSDATE,
672 decode(x_owner,'SEED',1,0),
673 0,
674 decode(x_owner,'SEED',1,0),
675 SYSDATE
676 from FND_LANGUAGES L
677 where L.INSTALLED_FLAG in ('I', 'B')
678 and not exists
679 (select NULL
680 from pay_payment_types_tl T
681 where T.PAYMENT_TYPE_ID = X_PAYMENT_TYPE_ID
682 and T.LANGUAGE = L.LANGUAGE_CODE);
683 OPEN C;
684 FETCH C INTO X_PAYMENT_TYPE_ID;
685 if (C%NOTFOUND) then
686 -- CLOSE C;
687 -- hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
688 -- hr_utility.set_message_token('PROCEDURE','Insert_Row');
689 -- hr_utility.set_message_token('STEP','1');
690 -- hr_utility.raise_error;
691 null;
692 end if;
693 CLOSE C;
694 -- Bug # 6124985.
695 -- Added else part to update the pay_payment_types_tl table if the above
696 -- update is successful.
697 else
698 UPDATE pay_payment_types_tl
699 SET description = nvl(x_description,description),
700 payment_type_name = nvl(x_payment_type_name,payment_type_name),
701 last_update_date = SYSDATE,
702 last_updated_by = decode(x_owner,'SEED',1,0),
703 last_update_login = 0,
704 source_lang = userenv('LANG')
705 WHERE userenv('LANG') IN (language,source_lang)
706 AND payment_type_id IN
707 (SELECT PPT.PAYMENT_TYPE_ID
708 FROM pay_payment_types ppt
709 WHERE nvl(upper(x_territory_code),'~null~') = nvl(upper(ppt.territory_code),'~null~')
710 AND nvl(upper(x_b_payment_type_name),'~null~') = nvl(upper(ppt.payment_type_name),'~null~'));
711 end if;
712 end LOAD_ROW;
713 ------------------------------------------------------------------------------
714 END PAY_PAYMENT_TYPES_PKG;