1 package body IBY_SYS_PMT_PROFILES_PKG as
2 /* $Header: ibysyppb.pls 120.4 2005/11/05 01:46:10 syidner noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY VARCHAR2,
5 X_SYSTEM_PROFILE_CODE in VARCHAR2,
6 X_PAYMENT_TEXT_MESSAGE2 in VARCHAR2,
7 X_INACTIVE_DATE in DATE,
8 X_ACK_TRANSMIT_PROTOCOL_CODE in VARCHAR2,
9 X_ACK_SECURITY_PROTOCOL_CODE in VARCHAR2,
10 X_SEND_TO_FILE_FLAG in VARCHAR2,
11 X_PI_REGISTER_FORMAT in VARCHAR2,
12 X_ATTRIBUTE_CATEGORY in VARCHAR2,
13 X_ATTRIBUTE1 in VARCHAR2,
14 X_ATTRIBUTE2 in VARCHAR2,
15 X_ATTRIBUTE3 in VARCHAR2,
16 X_ATTRIBUTE4 in VARCHAR2,
17 X_ATTRIBUTE5 in VARCHAR2,
18 X_ATTRIBUTE6 in VARCHAR2,
19 X_ATTRIBUTE7 in VARCHAR2,
20 X_ATTRIBUTE8 in VARCHAR2,
21 X_ATTRIBUTE9 in VARCHAR2,
22 X_ATTRIBUTE10 in VARCHAR2,
23 X_ATTRIBUTE11 in VARCHAR2,
24 X_ATTRIBUTE12 in VARCHAR2,
25 X_ATTRIBUTE13 in VARCHAR2,
26 X_ATTRIBUTE14 in VARCHAR2,
27 X_ATTRIBUTE15 in VARCHAR2,
28 X_PERIODIC_SEQUENCE_NAME_2 in VARCHAR2,
29 X_PERIODIC_SEQUENCE_NAME_3 in VARCHAR2,
30 X_PROCESSING_TYPE in VARCHAR2,
31 X_MARK_COMPLETE_EVENT in VARCHAR2,
32 X_MANUAL_MARK_COMPLETE_FLAG in VARCHAR2,
33 X_POSITIVE_PAY_DELIVERY_FLAG in VARCHAR2,
34 X_TRANSMIT_INSTR_IMMED_FLAG in VARCHAR2,
35 X_SEEDED_FLAG in VARCHAR2,
36 X_AUTOMATIC_PI_REG_SUBMIT in VARCHAR2,
37 X_OBJECT_VERSION_NUMBER in NUMBER,
38 X_DECLARATION_OPTION in VARCHAR2,
39 X_DCL_ONLY_FOREIGN_CURR_PMT_FL in VARCHAR2,
40 X_DECLARATION_REPORT_FORMAT_CO in VARCHAR2,
41 X_DECLARATION_CURR_FX_RATE_TYP in VARCHAR2,
42 X_DECLARATION_CURRENCY_CODE in VARCHAR2,
43 X_DECLARATION_THRESHOLD_AMOUNT in NUMBER,
44 X_BANK_INSTRUCTION1_CODE in VARCHAR2,
45 X_BANK_INSTRUCTION2_CODE in VARCHAR2,
46 X_BANK_INSTRUCTION_DETAILS in VARCHAR2,
47 X_PAYMENT_TEXT_MESSAGE1 in VARCHAR2,
48 X_PRINT_INSTRUCTION_IMMED_FLAG in VARCHAR2,
49 X_DEFAULT_PRINTER in VARCHAR2,
50 X_DEFAULT_PAYMENT_DOCUMENT_ID in NUMBER,
51 X_PERIODIC_SEQUENCE_NAME_1 in VARCHAR2,
52 X_TRANSMIT_PROTOCOL_CODE in VARCHAR2,
53 X_PAYMENT_FORMAT_CODE in VARCHAR2,
54 X_POSITIVE_PAY_FORMAT_CODE in VARCHAR2,
55 X_PAY_FILE_LETTER_FORMAT_CODE in VARCHAR2,
56 X_BEPID in NUMBER,
57 X_SECURITY_PROTOCOL_CODE in VARCHAR2,
58 X_SYSTEM_PROFILE_NAME in VARCHAR2,
59 X_SYSTEM_PROFILE_DESCRIPTION in VARCHAR2,
60 X_CREATION_DATE in DATE,
61 X_CREATED_BY in NUMBER,
62 X_LAST_UPDATE_DATE in DATE,
63 X_LAST_UPDATED_BY in NUMBER,
64 X_LAST_UPDATE_LOGIN in NUMBER
65 ) is
66 cursor C is select ROWID from IBY_SYS_PMT_PROFILES_B
67 where SYSTEM_PROFILE_CODE = X_SYSTEM_PROFILE_CODE
68 ;
69 begin
70 insert into IBY_SYS_PMT_PROFILES_B (
71 PAYMENT_TEXT_MESSAGE2,
72 INACTIVE_DATE,
73 ACK_TRANSMIT_PROTOCOL_CODE,
74 ACK_SECURITY_PROTOCOL_CODE,
75 SEND_TO_FILE_FLAG,
76 PI_REGISTER_FORMAT,
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 PERIODIC_SEQUENCE_NAME_2,
94 PERIODIC_SEQUENCE_NAME_3,
95 PROCESSING_TYPE,
96 MARK_COMPLETE_EVENT,
97 MANUAL_MARK_COMPLETE_FLAG,
98 POSITIVE_PAY_DELIVERY_FLAG,
99 TRANSMIT_INSTR_IMMED_FLAG,
100 SEEDED_FLAG,
101 AUTOMATIC_PI_REG_SUBMIT,
102 OBJECT_VERSION_NUMBER,
103 DECLARATION_OPTION,
104 DCL_ONLY_FOREIGN_CURR_PMT_FLAG,
105 DECLARATION_REPORT_FORMAT_CODE,
106 DECLARATION_CURR_FX_RATE_TYPE,
107 DECLARATION_CURRENCY_CODE,
108 DECLARATION_THRESHOLD_AMOUNT,
109 BANK_INSTRUCTION1_CODE,
110 BANK_INSTRUCTION2_CODE,
111 BANK_INSTRUCTION_DETAILS,
112 PAYMENT_TEXT_MESSAGE1,
113 PRINT_INSTRUCTION_IMMED_FLAG,
114 DEFAULT_PRINTER,
115 DEFAULT_PAYMENT_DOCUMENT_ID,
116 PERIODIC_SEQUENCE_NAME_1,
117 TRANSMIT_PROTOCOL_CODE,
118 PAYMENT_FORMAT_CODE,
119 POSITIVE_PAY_FORMAT_CODE,
120 PAY_FILE_LETTER_FORMAT_CODE,
121 SYSTEM_PROFILE_CODE,
122 BEPID,
123 SECURITY_PROTOCOL_CODE,
124 CREATION_DATE,
125 CREATED_BY,
126 LAST_UPDATE_DATE,
127 LAST_UPDATED_BY,
128 LAST_UPDATE_LOGIN
129 ) values (
130 X_PAYMENT_TEXT_MESSAGE2,
131 X_INACTIVE_DATE,
132 X_ACK_TRANSMIT_PROTOCOL_CODE,
133 X_ACK_SECURITY_PROTOCOL_CODE,
134 X_SEND_TO_FILE_FLAG,
135 X_PI_REGISTER_FORMAT,
136 X_ATTRIBUTE_CATEGORY,
137 X_ATTRIBUTE1,
138 X_ATTRIBUTE2,
139 X_ATTRIBUTE3,
140 X_ATTRIBUTE4,
141 X_ATTRIBUTE5,
142 X_ATTRIBUTE6,
143 X_ATTRIBUTE7,
144 X_ATTRIBUTE8,
145 X_ATTRIBUTE9,
146 X_ATTRIBUTE10,
147 X_ATTRIBUTE11,
148 X_ATTRIBUTE12,
149 X_ATTRIBUTE13,
150 X_ATTRIBUTE14,
151 X_ATTRIBUTE15,
152 X_PERIODIC_SEQUENCE_NAME_2,
153 X_PERIODIC_SEQUENCE_NAME_3,
154 X_PROCESSING_TYPE,
155 X_MARK_COMPLETE_EVENT,
156 X_MANUAL_MARK_COMPLETE_FLAG,
157 X_POSITIVE_PAY_DELIVERY_FLAG,
158 X_TRANSMIT_INSTR_IMMED_FLAG,
159 X_SEEDED_FLAG,
160 X_AUTOMATIC_PI_REG_SUBMIT,
161 X_OBJECT_VERSION_NUMBER,
162 X_DECLARATION_OPTION,
163 X_DCL_ONLY_FOREIGN_CURR_PMT_FL,
164 X_DECLARATION_REPORT_FORMAT_CO,
165 X_DECLARATION_CURR_FX_RATE_TYP,
166 X_DECLARATION_CURRENCY_CODE,
167 X_DECLARATION_THRESHOLD_AMOUNT,
168 X_BANK_INSTRUCTION1_CODE,
169 X_BANK_INSTRUCTION2_CODE,
170 X_BANK_INSTRUCTION_DETAILS,
171 X_PAYMENT_TEXT_MESSAGE1,
172 X_PRINT_INSTRUCTION_IMMED_FLAG,
173 X_DEFAULT_PRINTER,
174 X_DEFAULT_PAYMENT_DOCUMENT_ID,
175 X_PERIODIC_SEQUENCE_NAME_1,
176 X_TRANSMIT_PROTOCOL_CODE,
177 X_PAYMENT_FORMAT_CODE,
178 X_POSITIVE_PAY_FORMAT_CODE,
179 X_PAY_FILE_LETTER_FORMAT_CODE,
180 X_SYSTEM_PROFILE_CODE,
181 X_BEPID,
182 X_SECURITY_PROTOCOL_CODE,
183 X_CREATION_DATE,
184 X_CREATED_BY,
185 X_LAST_UPDATE_DATE,
186 X_LAST_UPDATED_BY,
187 X_LAST_UPDATE_LOGIN
188 );
189
190 insert into IBY_SYS_PMT_PROFILES_TL (
191 LAST_UPDATE_DATE,
192 LAST_UPDATE_LOGIN,
193 OBJECT_VERSION_NUMBER,
194 SYSTEM_PROFILE_DESCRIPTION,
195 LAST_UPDATED_BY,
196 CREATION_DATE,
197 CREATED_BY,
198 SYSTEM_PROFILE_CODE,
199 SYSTEM_PROFILE_NAME,
200 LANGUAGE,
201 SOURCE_LANG
202 ) select
203 X_LAST_UPDATE_DATE,
204 X_LAST_UPDATE_LOGIN,
205 X_OBJECT_VERSION_NUMBER,
206 X_SYSTEM_PROFILE_DESCRIPTION,
207 X_LAST_UPDATED_BY,
208 X_CREATION_DATE,
209 X_CREATED_BY,
210 X_SYSTEM_PROFILE_CODE,
211 X_SYSTEM_PROFILE_NAME,
212 L.LANGUAGE_CODE,
213 userenv('LANG')
214 from FND_LANGUAGES L
215 where L.INSTALLED_FLAG in ('I', 'B')
216 and not exists
217 (select NULL
218 from IBY_SYS_PMT_PROFILES_TL T
219 where T.SYSTEM_PROFILE_CODE = X_SYSTEM_PROFILE_CODE
220 and T.LANGUAGE = L.LANGUAGE_CODE);
221
222 open c;
223 fetch c into X_ROWID;
224 if (c%notfound) then
225 close c;
226 raise no_data_found;
227 end if;
228 close c;
229
230 end INSERT_ROW;
231
232 procedure LOCK_ROW (
233 X_SYSTEM_PROFILE_CODE in VARCHAR2,
234 X_PAYMENT_TEXT_MESSAGE2 in VARCHAR2,
235 X_INACTIVE_DATE in DATE,
236 X_ACK_TRANSMIT_PROTOCOL_CODE in VARCHAR2,
237 X_ACK_SECURITY_PROTOCOL_CODE in VARCHAR2,
238 X_SEND_TO_FILE_FLAG in VARCHAR2,
239 X_PI_REGISTER_FORMAT in VARCHAR2,
240 X_ATTRIBUTE_CATEGORY in VARCHAR2,
241 X_ATTRIBUTE1 in VARCHAR2,
242 X_ATTRIBUTE2 in VARCHAR2,
243 X_ATTRIBUTE3 in VARCHAR2,
244 X_ATTRIBUTE4 in VARCHAR2,
245 X_ATTRIBUTE5 in VARCHAR2,
246 X_ATTRIBUTE6 in VARCHAR2,
247 X_ATTRIBUTE7 in VARCHAR2,
248 X_ATTRIBUTE8 in VARCHAR2,
249 X_ATTRIBUTE9 in VARCHAR2,
250 X_ATTRIBUTE10 in VARCHAR2,
251 X_ATTRIBUTE11 in VARCHAR2,
252 X_ATTRIBUTE12 in VARCHAR2,
253 X_ATTRIBUTE13 in VARCHAR2,
254 X_ATTRIBUTE14 in VARCHAR2,
255 X_ATTRIBUTE15 in VARCHAR2,
256 X_PERIODIC_SEQUENCE_NAME_2 in VARCHAR2,
257 X_PERIODIC_SEQUENCE_NAME_3 in VARCHAR2,
258 X_PROCESSING_TYPE in VARCHAR2,
259 X_MARK_COMPLETE_EVENT in VARCHAR2,
260 X_MANUAL_MARK_COMPLETE_FLAG in VARCHAR2,
261 X_POSITIVE_PAY_DELIVERY_FLAG in VARCHAR2,
262 X_TRANSMIT_INSTR_IMMED_FLAG in VARCHAR2,
263 X_SEEDED_FLAG in VARCHAR2,
264 X_AUTOMATIC_PI_REG_SUBMIT in VARCHAR2,
265 X_OBJECT_VERSION_NUMBER in NUMBER,
266 X_DECLARATION_OPTION in VARCHAR2,
267 X_DCL_ONLY_FOREIGN_CURR_PMT_FL in VARCHAR2,
268 X_DECLARATION_REPORT_FORMAT_CO in VARCHAR2,
269 X_DECLARATION_CURR_FX_RATE_TYP in VARCHAR2,
270 X_DECLARATION_CURRENCY_CODE in VARCHAR2,
271 X_DECLARATION_THRESHOLD_AMOUNT in NUMBER,
272 X_BANK_INSTRUCTION1_CODE in VARCHAR2,
273 X_BANK_INSTRUCTION2_CODE in VARCHAR2,
274 X_BANK_INSTRUCTION_DETAILS in VARCHAR2,
275 X_PAYMENT_TEXT_MESSAGE1 in VARCHAR2,
276 X_PRINT_INSTRUCTION_IMMED_FLAG in VARCHAR2,
277 X_DEFAULT_PRINTER in VARCHAR2,
278 X_DEFAULT_PAYMENT_DOCUMENT_ID in NUMBER,
279 X_PERIODIC_SEQUENCE_NAME_1 in VARCHAR2,
280 X_TRANSMIT_PROTOCOL_CODE in VARCHAR2,
281 X_PAYMENT_FORMAT_CODE in VARCHAR2,
282 X_POSITIVE_PAY_FORMAT_CODE in VARCHAR2,
283 X_PAY_FILE_LETTER_FORMAT_CODE in VARCHAR2,
284 X_BEPID in NUMBER,
285 X_SECURITY_PROTOCOL_CODE in VARCHAR2,
286 X_SYSTEM_PROFILE_NAME in VARCHAR2,
287 X_SYSTEM_PROFILE_DESCRIPTION in VARCHAR2
288 ) is
289 cursor c is select
290 PAYMENT_TEXT_MESSAGE2,
291 INACTIVE_DATE,
292 ACK_TRANSMIT_PROTOCOL_CODE,
293 ACK_SECURITY_PROTOCOL_CODE,
294 SEND_TO_FILE_FLAG,
295 PI_REGISTER_FORMAT,
296 ATTRIBUTE_CATEGORY,
297 ATTRIBUTE1,
298 ATTRIBUTE2,
299 ATTRIBUTE3,
300 ATTRIBUTE4,
301 ATTRIBUTE5,
302 ATTRIBUTE6,
303 ATTRIBUTE7,
304 ATTRIBUTE8,
305 ATTRIBUTE9,
306 ATTRIBUTE10,
307 ATTRIBUTE11,
308 ATTRIBUTE12,
309 ATTRIBUTE13,
310 ATTRIBUTE14,
311 ATTRIBUTE15,
312 PERIODIC_SEQUENCE_NAME_2,
313 PERIODIC_SEQUENCE_NAME_3,
314 PROCESSING_TYPE,
315 MARK_COMPLETE_EVENT,
316 MANUAL_MARK_COMPLETE_FLAG,
317 POSITIVE_PAY_DELIVERY_FLAG,
318 TRANSMIT_INSTR_IMMED_FLAG,
319 SEEDED_FLAG,
320 AUTOMATIC_PI_REG_SUBMIT,
321 OBJECT_VERSION_NUMBER,
322 DECLARATION_OPTION,
323 DCL_ONLY_FOREIGN_CURR_PMT_FLAG,
324 DECLARATION_REPORT_FORMAT_CODE,
325 DECLARATION_CURR_FX_RATE_TYPE,
326 DECLARATION_CURRENCY_CODE,
327 DECLARATION_THRESHOLD_AMOUNT,
328 BANK_INSTRUCTION1_CODE,
329 BANK_INSTRUCTION2_CODE,
330 BANK_INSTRUCTION_DETAILS,
331 PAYMENT_TEXT_MESSAGE1,
332 PRINT_INSTRUCTION_IMMED_FLAG,
333 DEFAULT_PRINTER,
334 DEFAULT_PAYMENT_DOCUMENT_ID,
335 PERIODIC_SEQUENCE_NAME_1,
336 TRANSMIT_PROTOCOL_CODE,
337 PAYMENT_FORMAT_CODE,
338 POSITIVE_PAY_FORMAT_CODE,
339 PAY_FILE_LETTER_FORMAT_CODE,
340 BEPID,
341 SECURITY_PROTOCOL_CODE
342 from IBY_SYS_PMT_PROFILES_B
343 where SYSTEM_PROFILE_CODE = X_SYSTEM_PROFILE_CODE
344 for update of SYSTEM_PROFILE_CODE nowait;
345 recinfo c%rowtype;
346
347 cursor c1 is select
348 SYSTEM_PROFILE_NAME,
349 SYSTEM_PROFILE_DESCRIPTION,
350 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
351 from IBY_SYS_PMT_PROFILES_TL
352 where SYSTEM_PROFILE_CODE = X_SYSTEM_PROFILE_CODE
353 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
354 for update of SYSTEM_PROFILE_CODE nowait;
355 begin
356 open c;
357 fetch c into recinfo;
358 if (c%notfound) then
359 close c;
360 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
361 app_exception.raise_exception;
362 end if;
363 close c;
364 if ( ((recinfo.PAYMENT_TEXT_MESSAGE2 = X_PAYMENT_TEXT_MESSAGE2)
365 OR ((recinfo.PAYMENT_TEXT_MESSAGE2 is null) AND (X_PAYMENT_TEXT_MESSAGE2 is null)))
366 AND ((recinfo.INACTIVE_DATE = X_INACTIVE_DATE)
367 OR ((recinfo.INACTIVE_DATE is null) AND (X_INACTIVE_DATE is null)))
368 AND ((recinfo.ACK_TRANSMIT_PROTOCOL_CODE = X_ACK_TRANSMIT_PROTOCOL_CODE)
369 OR ((recinfo.ACK_TRANSMIT_PROTOCOL_CODE is null) AND (X_ACK_TRANSMIT_PROTOCOL_CODE is null)))
370 AND ((recinfo.ACK_SECURITY_PROTOCOL_CODE = X_ACK_SECURITY_PROTOCOL_CODE)
371 OR ((recinfo.ACK_SECURITY_PROTOCOL_CODE is null) AND (X_ACK_SECURITY_PROTOCOL_CODE is null)))
372 AND (recinfo.SEND_TO_FILE_FLAG = X_SEND_TO_FILE_FLAG)
373 AND ((recinfo.PI_REGISTER_FORMAT = X_PI_REGISTER_FORMAT)
374 OR ((recinfo.PI_REGISTER_FORMAT is null) AND (X_PI_REGISTER_FORMAT is null)))
375 AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
376 OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
377 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
378 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
379 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
380 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
381 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
382 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
383 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
384 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
385 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
386 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
387 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
388 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
389 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
390 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
391 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
392 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
393 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
394 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
395 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
396 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
397 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
398 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
399 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
400 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
401 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
402 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
403 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
404 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
405 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
406 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
407 AND ((recinfo.PERIODIC_SEQUENCE_NAME_2 = X_PERIODIC_SEQUENCE_NAME_2)
408 OR ((recinfo.PERIODIC_SEQUENCE_NAME_2 is null) AND (X_PERIODIC_SEQUENCE_NAME_2 is null)))
409 AND ((recinfo.PERIODIC_SEQUENCE_NAME_3 = X_PERIODIC_SEQUENCE_NAME_3)
410 OR ((recinfo.PERIODIC_SEQUENCE_NAME_3 is null) AND (X_PERIODIC_SEQUENCE_NAME_3 is null)))
411 AND (recinfo.PROCESSING_TYPE = X_PROCESSING_TYPE)
412 AND (recinfo.MARK_COMPLETE_EVENT = X_MARK_COMPLETE_EVENT)
413 AND (recinfo.MANUAL_MARK_COMPLETE_FLAG = X_MANUAL_MARK_COMPLETE_FLAG)
414 AND (recinfo.POSITIVE_PAY_DELIVERY_FLAG = X_POSITIVE_PAY_DELIVERY_FLAG)
415 AND (recinfo.TRANSMIT_INSTR_IMMED_FLAG = X_TRANSMIT_INSTR_IMMED_FLAG)
416 AND ((recinfo.SEEDED_FLAG = X_SEEDED_FLAG)
417 OR ((recinfo.SEEDED_FLAG is null) AND (X_SEEDED_FLAG is null)))
418 AND (recinfo.AUTOMATIC_PI_REG_SUBMIT = X_AUTOMATIC_PI_REG_SUBMIT)
419 AND (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
420 AND ((recinfo.DECLARATION_OPTION = X_DECLARATION_OPTION)
421 OR ((recinfo.DECLARATION_OPTION is null) AND (X_DECLARATION_OPTION is null)))
425 OR ((recinfo.DECLARATION_REPORT_FORMAT_CODE is null) AND (X_DECLARATION_REPORT_FORMAT_CO is null)))
422 AND ((recinfo.DCL_ONLY_FOREIGN_CURR_PMT_FLAG = X_DCL_ONLY_FOREIGN_CURR_PMT_FL)
423 OR ((recinfo.DCL_ONLY_FOREIGN_CURR_PMT_FLAG is null) AND (X_DCL_ONLY_FOREIGN_CURR_PMT_FL is null)))
424 AND ((recinfo.DECLARATION_REPORT_FORMAT_CODE = X_DECLARATION_REPORT_FORMAT_CO)
426 AND ((recinfo.DECLARATION_CURR_FX_RATE_TYPE = X_DECLARATION_CURR_FX_RATE_TYP)
427 OR ((recinfo.DECLARATION_CURR_FX_RATE_TYPE is null) AND (X_DECLARATION_CURR_FX_RATE_TYP is null)))
428 AND ((recinfo.DECLARATION_CURRENCY_CODE = X_DECLARATION_CURRENCY_CODE)
429 OR ((recinfo.DECLARATION_CURRENCY_CODE is null) AND (X_DECLARATION_CURRENCY_CODE is null)))
430 AND ((recinfo.DECLARATION_THRESHOLD_AMOUNT = X_DECLARATION_THRESHOLD_AMOUNT)
431 OR ((recinfo.DECLARATION_THRESHOLD_AMOUNT is null) AND (X_DECLARATION_THRESHOLD_AMOUNT is null)))
432 AND ((recinfo.BANK_INSTRUCTION1_CODE = X_BANK_INSTRUCTION1_CODE)
433 OR ((recinfo.BANK_INSTRUCTION1_CODE is null) AND (X_BANK_INSTRUCTION1_CODE is null)))
434 AND ((recinfo.BANK_INSTRUCTION2_CODE = X_BANK_INSTRUCTION2_CODE)
435 OR ((recinfo.BANK_INSTRUCTION2_CODE is null) AND (X_BANK_INSTRUCTION2_CODE is null)))
436 AND ((recinfo.BANK_INSTRUCTION_DETAILS = X_BANK_INSTRUCTION_DETAILS)
437 OR ((recinfo.BANK_INSTRUCTION_DETAILS is null) AND (X_BANK_INSTRUCTION_DETAILS is null)))
438 AND ((recinfo.PAYMENT_TEXT_MESSAGE1 = X_PAYMENT_TEXT_MESSAGE1)
439 OR ((recinfo.PAYMENT_TEXT_MESSAGE1 is null) AND (X_PAYMENT_TEXT_MESSAGE1 is null)))
440 AND (recinfo.PRINT_INSTRUCTION_IMMED_FLAG = X_PRINT_INSTRUCTION_IMMED_FLAG)
441 AND ((recinfo.DEFAULT_PRINTER = X_DEFAULT_PRINTER)
442 OR ((recinfo.DEFAULT_PRINTER is null) AND (X_DEFAULT_PRINTER is null)))
443 AND ((recinfo.DEFAULT_PAYMENT_DOCUMENT_ID = X_DEFAULT_PAYMENT_DOCUMENT_ID)
444 OR ((recinfo.DEFAULT_PAYMENT_DOCUMENT_ID is null) AND (X_DEFAULT_PAYMENT_DOCUMENT_ID is null)))
445 AND ((recinfo.PERIODIC_SEQUENCE_NAME_1 = X_PERIODIC_SEQUENCE_NAME_1)
446 OR ((recinfo.PERIODIC_SEQUENCE_NAME_1 is null) AND (X_PERIODIC_SEQUENCE_NAME_1 is null)))
447 AND ((recinfo.TRANSMIT_PROTOCOL_CODE = X_TRANSMIT_PROTOCOL_CODE)
448 OR ((recinfo.TRANSMIT_PROTOCOL_CODE is null) AND (X_TRANSMIT_PROTOCOL_CODE is null)))
449 AND (recinfo.PAYMENT_FORMAT_CODE = X_PAYMENT_FORMAT_CODE)
450 AND ((recinfo.POSITIVE_PAY_FORMAT_CODE = X_POSITIVE_PAY_FORMAT_CODE)
451 OR ((recinfo.POSITIVE_PAY_FORMAT_CODE is null) AND (X_POSITIVE_PAY_FORMAT_CODE is null)))
452 AND ((recinfo.PAY_FILE_LETTER_FORMAT_CODE = X_PAY_FILE_LETTER_FORMAT_CODE)
453 OR ((recinfo.PAY_FILE_LETTER_FORMAT_CODE is null) AND (X_PAY_FILE_LETTER_FORMAT_CODE is null)))
454 AND ((recinfo.BEPID = X_BEPID)
455 OR ((recinfo.BEPID is null) AND (X_BEPID is null)))
456 AND ((recinfo.SECURITY_PROTOCOL_CODE = X_SECURITY_PROTOCOL_CODE)
457 OR ((recinfo.SECURITY_PROTOCOL_CODE is null) AND (X_SECURITY_PROTOCOL_CODE is null)))
458 ) then
459 null;
460 else
461 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
462 app_exception.raise_exception;
463 end if;
464
465 for tlinfo in c1 loop
466 if (tlinfo.BASELANG = 'Y') then
467 if ( (tlinfo.SYSTEM_PROFILE_NAME = X_SYSTEM_PROFILE_NAME)
468 AND ((tlinfo.SYSTEM_PROFILE_DESCRIPTION = X_SYSTEM_PROFILE_DESCRIPTION)
469 OR ((tlinfo.SYSTEM_PROFILE_DESCRIPTION is null) AND (X_SYSTEM_PROFILE_DESCRIPTION is null)))
470 ) then
471 null;
472 else
473 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
474 app_exception.raise_exception;
475 end if;
476 end if;
477 end loop;
478 return;
479 end LOCK_ROW;
480
481 procedure UPDATE_ROW (
482 X_SYSTEM_PROFILE_CODE in VARCHAR2,
483 X_PAYMENT_TEXT_MESSAGE2 in VARCHAR2,
484 X_INACTIVE_DATE in DATE,
485 X_ACK_TRANSMIT_PROTOCOL_CODE in VARCHAR2,
486 X_ACK_SECURITY_PROTOCOL_CODE in VARCHAR2,
487 X_SEND_TO_FILE_FLAG in VARCHAR2,
488 X_PI_REGISTER_FORMAT in VARCHAR2,
489 X_ATTRIBUTE_CATEGORY in VARCHAR2,
490 X_ATTRIBUTE1 in VARCHAR2,
491 X_ATTRIBUTE2 in VARCHAR2,
492 X_ATTRIBUTE3 in VARCHAR2,
493 X_ATTRIBUTE4 in VARCHAR2,
494 X_ATTRIBUTE5 in VARCHAR2,
495 X_ATTRIBUTE6 in VARCHAR2,
496 X_ATTRIBUTE7 in VARCHAR2,
497 X_ATTRIBUTE8 in VARCHAR2,
498 X_ATTRIBUTE9 in VARCHAR2,
499 X_ATTRIBUTE10 in VARCHAR2,
500 X_ATTRIBUTE11 in VARCHAR2,
501 X_ATTRIBUTE12 in VARCHAR2,
502 X_ATTRIBUTE13 in VARCHAR2,
503 X_ATTRIBUTE14 in VARCHAR2,
504 X_ATTRIBUTE15 in VARCHAR2,
505 X_PERIODIC_SEQUENCE_NAME_2 in VARCHAR2,
506 X_PERIODIC_SEQUENCE_NAME_3 in VARCHAR2,
507 X_PROCESSING_TYPE in VARCHAR2,
508 X_MARK_COMPLETE_EVENT in VARCHAR2,
509 X_MANUAL_MARK_COMPLETE_FLAG in VARCHAR2,
510 X_POSITIVE_PAY_DELIVERY_FLAG in VARCHAR2,
511 X_TRANSMIT_INSTR_IMMED_FLAG in VARCHAR2,
512 X_SEEDED_FLAG in VARCHAR2,
513 X_AUTOMATIC_PI_REG_SUBMIT in VARCHAR2,
514 X_OBJECT_VERSION_NUMBER in NUMBER,
515 X_DECLARATION_OPTION in VARCHAR2,
516 X_DCL_ONLY_FOREIGN_CURR_PMT_FL in VARCHAR2,
517 X_DECLARATION_REPORT_FORMAT_CO in VARCHAR2,
518 X_DECLARATION_CURR_FX_RATE_TYP in VARCHAR2,
519 X_DECLARATION_CURRENCY_CODE in VARCHAR2,
520 X_DECLARATION_THRESHOLD_AMOUNT in NUMBER,
521 X_BANK_INSTRUCTION1_CODE in VARCHAR2,
522 X_BANK_INSTRUCTION2_CODE in VARCHAR2,
523 X_BANK_INSTRUCTION_DETAILS in VARCHAR2,
524 X_PAYMENT_TEXT_MESSAGE1 in VARCHAR2,
525 X_PRINT_INSTRUCTION_IMMED_FLAG in VARCHAR2,
526 X_DEFAULT_PRINTER in VARCHAR2,
527 X_DEFAULT_PAYMENT_DOCUMENT_ID in NUMBER,
528 X_PERIODIC_SEQUENCE_NAME_1 in VARCHAR2,
529 X_TRANSMIT_PROTOCOL_CODE in VARCHAR2,
530 X_PAYMENT_FORMAT_CODE in VARCHAR2,
531 X_POSITIVE_PAY_FORMAT_CODE in VARCHAR2,
532 X_PAY_FILE_LETTER_FORMAT_CODE in VARCHAR2,
536 X_SYSTEM_PROFILE_DESCRIPTION in VARCHAR2,
533 X_BEPID in NUMBER,
534 X_SECURITY_PROTOCOL_CODE in VARCHAR2,
535 X_SYSTEM_PROFILE_NAME in VARCHAR2,
537 X_LAST_UPDATE_DATE in DATE,
538 X_LAST_UPDATED_BY in NUMBER,
539 X_LAST_UPDATE_LOGIN in NUMBER
540 ) is
541 begin
542 update IBY_SYS_PMT_PROFILES_B set
543 PAYMENT_TEXT_MESSAGE2 = X_PAYMENT_TEXT_MESSAGE2,
544 INACTIVE_DATE = X_INACTIVE_DATE,
545 ACK_TRANSMIT_PROTOCOL_CODE = X_ACK_TRANSMIT_PROTOCOL_CODE,
546 ACK_SECURITY_PROTOCOL_CODE = X_ACK_SECURITY_PROTOCOL_CODE,
547 SEND_TO_FILE_FLAG = X_SEND_TO_FILE_FLAG,
548 PI_REGISTER_FORMAT = X_PI_REGISTER_FORMAT,
549 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
550 ATTRIBUTE1 = X_ATTRIBUTE1,
551 ATTRIBUTE2 = X_ATTRIBUTE2,
552 ATTRIBUTE3 = X_ATTRIBUTE3,
553 ATTRIBUTE4 = X_ATTRIBUTE4,
554 ATTRIBUTE5 = X_ATTRIBUTE5,
555 ATTRIBUTE6 = X_ATTRIBUTE6,
556 ATTRIBUTE7 = X_ATTRIBUTE7,
557 ATTRIBUTE8 = X_ATTRIBUTE8,
558 ATTRIBUTE9 = X_ATTRIBUTE9,
559 ATTRIBUTE10 = X_ATTRIBUTE10,
560 ATTRIBUTE11 = X_ATTRIBUTE11,
561 ATTRIBUTE12 = X_ATTRIBUTE12,
562 ATTRIBUTE13 = X_ATTRIBUTE13,
563 ATTRIBUTE14 = X_ATTRIBUTE14,
564 ATTRIBUTE15 = X_ATTRIBUTE15,
565 PERIODIC_SEQUENCE_NAME_2 = X_PERIODIC_SEQUENCE_NAME_2,
566 PERIODIC_SEQUENCE_NAME_3 = X_PERIODIC_SEQUENCE_NAME_3,
567 PROCESSING_TYPE = X_PROCESSING_TYPE,
568 MARK_COMPLETE_EVENT = X_MARK_COMPLETE_EVENT,
569 MANUAL_MARK_COMPLETE_FLAG = X_MANUAL_MARK_COMPLETE_FLAG,
570 POSITIVE_PAY_DELIVERY_FLAG = X_POSITIVE_PAY_DELIVERY_FLAG,
571 TRANSMIT_INSTR_IMMED_FLAG = X_TRANSMIT_INSTR_IMMED_FLAG,
572 SEEDED_FLAG = X_SEEDED_FLAG,
573 AUTOMATIC_PI_REG_SUBMIT = X_AUTOMATIC_PI_REG_SUBMIT,
574 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
575 DECLARATION_OPTION = X_DECLARATION_OPTION,
576 DCL_ONLY_FOREIGN_CURR_PMT_FLAG = X_DCL_ONLY_FOREIGN_CURR_PMT_FL,
577 DECLARATION_REPORT_FORMAT_CODE = X_DECLARATION_REPORT_FORMAT_CO,
578 DECLARATION_CURR_FX_RATE_TYPE = X_DECLARATION_CURR_FX_RATE_TYP,
579 DECLARATION_CURRENCY_CODE = X_DECLARATION_CURRENCY_CODE,
580 DECLARATION_THRESHOLD_AMOUNT = X_DECLARATION_THRESHOLD_AMOUNT,
581 BANK_INSTRUCTION1_CODE = X_BANK_INSTRUCTION1_CODE,
582 BANK_INSTRUCTION2_CODE = X_BANK_INSTRUCTION2_CODE,
583 BANK_INSTRUCTION_DETAILS = X_BANK_INSTRUCTION_DETAILS,
584 PAYMENT_TEXT_MESSAGE1 = X_PAYMENT_TEXT_MESSAGE1,
585 PRINT_INSTRUCTION_IMMED_FLAG = X_PRINT_INSTRUCTION_IMMED_FLAG,
586 DEFAULT_PRINTER = X_DEFAULT_PRINTER,
587 DEFAULT_PAYMENT_DOCUMENT_ID = X_DEFAULT_PAYMENT_DOCUMENT_ID,
588 PERIODIC_SEQUENCE_NAME_1 = X_PERIODIC_SEQUENCE_NAME_1,
589 TRANSMIT_PROTOCOL_CODE = X_TRANSMIT_PROTOCOL_CODE,
590 PAYMENT_FORMAT_CODE = X_PAYMENT_FORMAT_CODE,
591 POSITIVE_PAY_FORMAT_CODE = X_POSITIVE_PAY_FORMAT_CODE,
592 PAY_FILE_LETTER_FORMAT_CODE = X_PAY_FILE_LETTER_FORMAT_CODE,
593 BEPID = X_BEPID,
594 SECURITY_PROTOCOL_CODE = X_SECURITY_PROTOCOL_CODE,
595 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
596 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
597 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
598 where SYSTEM_PROFILE_CODE = X_SYSTEM_PROFILE_CODE;
599
600 if (sql%notfound) then
601 raise no_data_found;
602 end if;
603
604 update IBY_SYS_PMT_PROFILES_TL set
605 SYSTEM_PROFILE_NAME = X_SYSTEM_PROFILE_NAME,
606 SYSTEM_PROFILE_DESCRIPTION = X_SYSTEM_PROFILE_DESCRIPTION,
607 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
608 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
609 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
610 SOURCE_LANG = userenv('LANG')
611 where SYSTEM_PROFILE_CODE = X_SYSTEM_PROFILE_CODE
612 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
613
614 if (sql%notfound) then
615 raise no_data_found;
616 end if;
617 end UPDATE_ROW;
618
619 procedure DELETE_ROW (
620 X_SYSTEM_PROFILE_CODE in VARCHAR2
621 ) is
622 begin
623 delete from IBY_SYS_PMT_PROFILES_TL
624 where SYSTEM_PROFILE_CODE = X_SYSTEM_PROFILE_CODE;
625
626 if (sql%notfound) then
627 raise no_data_found;
628 end if;
629
630 delete from IBY_SYS_PMT_PROFILES_B
631 where SYSTEM_PROFILE_CODE = X_SYSTEM_PROFILE_CODE;
632
633 if (sql%notfound) then
634 raise no_data_found;
635 end if;
636 end DELETE_ROW;
637
638 procedure ADD_LANGUAGE
639 is
640 begin
641 delete from IBY_SYS_PMT_PROFILES_TL T
642 where not exists
643 (select NULL
644 from IBY_SYS_PMT_PROFILES_B B
645 where B.SYSTEM_PROFILE_CODE = T.SYSTEM_PROFILE_CODE
646 );
647
648 update IBY_SYS_PMT_PROFILES_TL T set (
649 SYSTEM_PROFILE_NAME,
650 SYSTEM_PROFILE_DESCRIPTION
651 ) = (select
652 B.SYSTEM_PROFILE_NAME,
653 B.SYSTEM_PROFILE_DESCRIPTION
654 from IBY_SYS_PMT_PROFILES_TL B
655 where B.SYSTEM_PROFILE_CODE = T.SYSTEM_PROFILE_CODE
656 and B.LANGUAGE = T.SOURCE_LANG)
657 where (
658 T.SYSTEM_PROFILE_CODE,
659 T.LANGUAGE
660 ) in (select
661 SUBT.SYSTEM_PROFILE_CODE,
662 SUBT.LANGUAGE
663 from IBY_SYS_PMT_PROFILES_TL SUBB, IBY_SYS_PMT_PROFILES_TL SUBT
664 where SUBB.SYSTEM_PROFILE_CODE = SUBT.SYSTEM_PROFILE_CODE
665 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
666 and (SUBB.SYSTEM_PROFILE_NAME <> SUBT.SYSTEM_PROFILE_NAME
667 or SUBB.SYSTEM_PROFILE_DESCRIPTION <> SUBT.SYSTEM_PROFILE_DESCRIPTION
668 or (SUBB.SYSTEM_PROFILE_DESCRIPTION is null and SUBT.SYSTEM_PROFILE_DESCRIPTION is not null)
669 or (SUBB.SYSTEM_PROFILE_DESCRIPTION is not null and SUBT.SYSTEM_PROFILE_DESCRIPTION is null)
670 ));
671
672 insert into IBY_SYS_PMT_PROFILES_TL (
673 LAST_UPDATE_DATE,
674 LAST_UPDATE_LOGIN,
675 OBJECT_VERSION_NUMBER,
676 SYSTEM_PROFILE_DESCRIPTION,
677 LAST_UPDATED_BY,
678 CREATION_DATE,
679 CREATED_BY,
680 SYSTEM_PROFILE_CODE,
681 SYSTEM_PROFILE_NAME,
682 LANGUAGE,
683 SOURCE_LANG
684 ) select /*+ ORDERED */
685 B.LAST_UPDATE_DATE,
686 B.LAST_UPDATE_LOGIN,
687 B.OBJECT_VERSION_NUMBER,
688 B.SYSTEM_PROFILE_DESCRIPTION,
689 B.LAST_UPDATED_BY,
690 B.CREATION_DATE,
691 B.CREATED_BY,
692 B.SYSTEM_PROFILE_CODE,
693 B.SYSTEM_PROFILE_NAME,
694 L.LANGUAGE_CODE,
695 B.SOURCE_LANG
696 from IBY_SYS_PMT_PROFILES_TL B, FND_LANGUAGES L
697 where L.INSTALLED_FLAG in ('I', 'B')
698 and B.LANGUAGE = userenv('LANG')
699 and not exists
700 (select NULL
701 from IBY_SYS_PMT_PROFILES_TL T
702 where T.SYSTEM_PROFILE_CODE = B.SYSTEM_PROFILE_CODE
703 and T.LANGUAGE = L.LANGUAGE_CODE);
704 end ADD_LANGUAGE;
705
706 end IBY_SYS_PMT_PROFILES_PKG;
[Home] [Help]