[Home] [Help]
PACKAGE BODY: APPS.IBY_UPG_PPP_PKG
Source
1 package body IBY_UPG_PPP_PKG as
2 /* $Header: ibypppub.pls 120.12.12020000.2 2012/12/05 07:43:03 sgogula ship $ */
3
4 procedure INSERT_ROW (
5 X_ROWID in out nocopy VARCHAR2,
6 X_SYSTEM_PROFILE_CODE in VARCHAR2,
7 X_REMIT_PMT_DETAILS_LENGTH_LIM in NUMBER,
8 X_REMIT_REMITTANCE_ADVICE_FORM in VARCHAR2,
9 X_REMIT_SRA_OVERRIDE_PAYEE_FLA in VARCHAR2,
10 X_REMIT_ADVICE_DELIVERY_METHOD in VARCHAR2,
11 X_REMIT_AUTOMATIC_SRA_SUBMIT in VARCHAR2,
12 X_OBJECT_VERSION_NUMBER in NUMBER,
13 X_PMT_PAYMENT_DETAILS_FORMULA in VARCHAR2,
14 X_INST_GROUP_BY_LEGAL_ENTITY in VARCHAR2,
15 X_INST_GROUP_BY_BILL_PAYABLE in VARCHAR2,
16 X_INST_GROUP_BY_MAX_INSTRUCTIO in VARCHAR2,
17 X_INST_GROUP_BY_ORGANIZATION in VARCHAR2,
18 X_INST_GROUP_BY_PAYMENT_FUNCTI in VARCHAR2,
19 X_INST_GROUP_BY_PAYMENT_REASON in VARCHAR2,
20 X_INST_GROUP_BY_RFC in VARCHAR2,
21 X_INST_MAX_PAYMENTS_PER_INSTR in NUMBER,
22 X_INST_MAX_AMOUNT_PER_INSTR_VA in NUMBER,
23 X_INST_MAX_AMOUNT_PER_INSTR_CU in VARCHAR2,
24 X_INST_MAX_AMOUNT_FX_RATE_TYPE in VARCHAR2,
25 X_INST_SORT_OPTION_1 in VARCHAR2,
26 X_INST_SORT_ORDER_1 in VARCHAR2,
27 X_INST_SORT_OPTION_2 in VARCHAR2,
28 X_POSITIVE_PAY_DELIVERY_FLAG in VARCHAR2,
29 X_TRANSMIT_INSTR_IMMED_FLAG in VARCHAR2,
30 X_AUTOMATIC_PI_REG_SUBMIT in VARCHAR2,
31 X_PMT_GROUP_BY_DELIVERY_CHANNE in VARCHAR2,
32 X_PMT_GROUP_BY_REMITTANCE_MES in VARCHAR2,
33 X_PMT_GROUP_BY_MAX_DOCUMENTS in VARCHAR2,
34 X_PMT_GROUP_BY_UNIQUE_REMIT_ID in VARCHAR2,
35 X_PMT_GROUP_BY_SETTLE_PRIORITY in VARCHAR2,
36 X_PMT_GROUP_BY_PAYMENT_REASON in VARCHAR2,
37 X_PMT_GROUP_BY_DUE_DATE_FLAG in VARCHAR2,
38 X_PMT_MAX_DOCUMENTS_PER_PAYMEN in NUMBER,
39 X_PMT_PAYMENT_DETAILS_LENGTH_L in NUMBER,
40 X_PMT_GROUP_BY_PAYMENT_DETAILS in VARCHAR2,
41 X_PMT_GROUP_BY_BANK_CHARGE_BEA in VARCHAR2,
42 X_INST_SORT_ORDER_2 in VARCHAR2,
43 X_INST_SORT_OPTION_3 in VARCHAR2,
44 X_INST_SORT_ORDER_3 in VARCHAR2,
45 X_REMIT_DOCUMENT_COUNT_LIMIT in NUMBER,
46 X_REMIT_ALLOW_MULTIPLE_COPY_FL in VARCHAR2,
47 X_INST_GROUP_BY_PAYMENT_DATE in VARCHAR2,
48 X_INST_GROUP_BY_PAYMENT_CURREN in VARCHAR2,
49 X_INST_GROUP_BY_INT_BANK_ACCT in VARCHAR2,
50 X_INST_GROUP_BY_MAX_PAYMENTS in VARCHAR2,
51 X_INST_GROUP_BY_PAY_SERVICE_RE in VARCHAR2,
52 X_DCL_ONLY_FOREIGN_CURR_PMT_FL in VARCHAR2,
53 X_DECLARATION_REPORT_FORMAT_CO in VARCHAR2,
54 X_DECLARATION_CURR_FX_RATE_TYP in VARCHAR2,
55 X_DECLARATION_CURRENCY_CODE in VARCHAR2,
56 X_DECLARATION_THRESHOLD_AMOUNT in NUMBER,
57 X_BANK_INSTRUCTION1_CODE in VARCHAR2,
58 X_BANK_INSTRUCTION2_CODE in VARCHAR2,
59 X_BANK_INSTRUCTION_DETAILS in VARCHAR2,
60 X_PAYMENT_TEXT_MESSAGE1 in VARCHAR2,
61 X_PAYMENT_TEXT_MESSAGE2 in VARCHAR2,
62 X_ACK_TRANSMIT_PROTOCOL_CODE in VARCHAR2,
63 X_ACK_SECURITY_PROTOCOL_CODE in VARCHAR2,
64 X_SEND_TO_FILE_FLAG in VARCHAR2,
65 X_PI_REGISTER_FORMAT in VARCHAR2,
66 X_BEP_ACCOUNT_ID in NUMBER,
67 X_APPLICABLE_PMT_METHOD in VARCHAR2,
68 X_APPLICABLE_CURRENCY in VARCHAR2,
69 X_APPLICABLE_PAYER_ORG in VARCHAR2,
70 X_APPLICABLE_INT_BANK_ACCOUNT in VARCHAR2,
71 X_BEP_SUFFIX in VARCHAR2,
72 X_SECURITY_PROTOCOL_CODE in VARCHAR2,
73 X_TRANSMIT_PROTOCOL_CODE in VARCHAR2,
74 X_PAYMENT_FORMAT_CODE in VARCHAR2,
75 X_POSITIVE_PAY_FORMAT_CODE in VARCHAR2,
76 X_PAY_FILE_LETTER_FORMAT_CODE in VARCHAR2,
77 X_PRINT_INSTRUCTION_IMMED_FLAG in VARCHAR2,
78 X_PERIODIC_SEQUENCE_NAME_1 in VARCHAR2,
79 X_PERIODIC_SEQUENCE_NAME_2 in VARCHAR2,
80 X_PERIODIC_SEQUENCE_NAME_3 in VARCHAR2,
81 X_PROCESSING_TYPE in VARCHAR2,
82 X_MARK_COMPLETE_EVENT in VARCHAR2,
83 X_MANUAL_MARK_COMPLETE_FLAG in VARCHAR2,
84 X_DECLARATION_OPTION in VARCHAR2,
85 X_SYSTEM_PROFILE_NAME in VARCHAR2,
86 X_SYSTEM_PROFILE_DESCRIPTION in VARCHAR2,
87 X_CREATION_DATE in DATE,
88 X_CREATED_BY in NUMBER,
89 X_LAST_UPDATE_DATE in DATE,
90 X_LAST_UPDATED_BY in NUMBER,
91 X_LAST_UPDATE_LOGIN in NUMBER,
92 X_ELECTRONIC_PROCESSING_CHNNL in VARCHAR2,
93 X_LOGICAL_GROUPING_MODE in VARCHAR2,
94 X_BATCH_BOOKING_FLAG in VARCHAR2,
95 X_INST_GROUP_BY_PAYMENT_METHOD in VARCHAR2,
96 X_INACTIVE_DATE in DATE
97 ) is
98 cursor C is select ROWID from IBY_UPG_PPP_B
99 where SYSTEM_PROFILE_CODE = X_SYSTEM_PROFILE_CODE
100 ;
101 begin
102 insert into IBY_UPG_PPP_B (
103 REMIT_PMT_DETAILS_LENGTH_LIM,
104 REMIT_REMITTANCE_ADVICE_FORMAT,
105 REMIT_SRA_OVERRIDE_PAYEE_FLAG,
106 REMIT_ADVICE_DELIVERY_METHOD,
107 REMIT_AUTOMATIC_SRA_SUBMIT,
108 OBJECT_VERSION_NUMBER,
109 PMT_PAYMENT_DETAILS_FORMULA,
110 INST_GROUP_BY_LEGAL_ENTITY,
111 INST_GROUP_BY_BILL_PAYABLE,
112 INST_GROUP_BY_MAX_INSTRUCTION,
113 INST_GROUP_BY_ORGANIZATION,
114 INST_GROUP_BY_PAYMENT_FUNCTION,
115 INST_GROUP_BY_PAYMENT_REASON,
116 INST_GROUP_BY_RFC,
117 INST_MAX_PAYMENTS_PER_INSTR,
118 INST_MAX_AMOUNT_PER_INSTR_VAL,
119 INST_MAX_AMOUNT_PER_INSTR_CURR,
120 INST_MAX_AMOUNT_FX_RATE_TYPE,
121 INST_SORT_OPTION_1,
122 INST_SORT_ORDER_1,
123 INST_SORT_OPTION_2,
124 POSITIVE_PAY_DELIVERY_FLAG,
125 TRANSMIT_INSTR_IMMED_FLAG,
126 AUTOMATIC_PI_REG_SUBMIT,
127 PMT_GROUP_BY_DELIVERY_CHANNEL,
128 PMT_GROUP_BY_REMITTANCE_MES,
129 PMT_GROUP_BY_MAX_DOCUMENTS,
130 PMT_GROUP_BY_UNIQUE_REMIT_ID,
131 PMT_GROUP_BY_SETTLE_PRIORITY,
132 PMT_GROUP_BY_PAYMENT_REASON,
133 PMT_GROUP_BY_DUE_DATE_FLAG,
134 PMT_MAX_DOCUMENTS_PER_PAYMENT,
135 PMT_PAYMENT_DETAILS_LENGTH_LIM,
136 PMT_GROUP_BY_PAYMENT_DETAILS,
137 PMT_GROUP_BY_BANK_CHARGE_BEAR,
138 INST_SORT_ORDER_2,
139 INST_SORT_OPTION_3,
140 INST_SORT_ORDER_3,
141 REMIT_DOCUMENT_COUNT_LIMIT,
142 REMIT_ALLOW_MULTIPLE_COPY_FLAG,
143 INST_GROUP_BY_PAYMENT_DATE,
144 INST_GROUP_BY_PAYMENT_CURRENCY,
145 INST_GROUP_BY_INT_BANK_ACCT,
146 INST_GROUP_BY_MAX_PAYMENTS,
147 INST_GROUP_BY_PAY_SERVICE_REQ,
148 DCL_ONLY_FOREIGN_CURR_PMT_FLAG,
149 DECLARATION_REPORT_FORMAT_CODE,
150 DECLARATION_CURR_FX_RATE_TYPE,
151 DECLARATION_CURRENCY_CODE,
152 DECLARATION_THRESHOLD_AMOUNT,
153 BANK_INSTRUCTION1_CODE,
154 BANK_INSTRUCTION2_CODE,
155 BANK_INSTRUCTION_DETAILS,
156 PAYMENT_TEXT_MESSAGE1,
157 PAYMENT_TEXT_MESSAGE2,
158 ACK_TRANSMIT_PROTOCOL_CODE,
159 ACK_SECURITY_PROTOCOL_CODE,
160 SEND_TO_FILE_FLAG,
161 PI_REGISTER_FORMAT,
162 BEP_ACCOUNT_ID,
163 APPLICABLE_PMT_METHOD,
164 APPLICABLE_CURRENCY,
165 APPLICABLE_PAYER_ORG,
166 APPLICABLE_INT_BANK_ACCOUNT,
167 SYSTEM_PROFILE_CODE,
168 BEP_SUFFIX,
169 SECURITY_PROTOCOL_CODE,
170 TRANSMIT_PROTOCOL_CODE,
171 PAYMENT_FORMAT_CODE,
172 POSITIVE_PAY_FORMAT_CODE,
173 PAY_FILE_LETTER_FORMAT_CODE,
174 PRINT_INSTRUCTION_IMMED_FLAG,
175 PERIODIC_SEQUENCE_NAME_1,
176 PERIODIC_SEQUENCE_NAME_2,
177 PERIODIC_SEQUENCE_NAME_3,
178 PROCESSING_TYPE,
179 MARK_COMPLETE_EVENT,
180 MANUAL_MARK_COMPLETE_FLAG,
181 DECLARATION_OPTION,
182 CREATION_DATE,
183 CREATED_BY,
184 LAST_UPDATE_DATE,
185 LAST_UPDATED_BY,
186 LAST_UPDATE_LOGIN,
187 ELECTRONIC_PROCESSING_CHANNEL,
188 LOGICAL_GROUPING_MODE,
189 BATCH_BOOKING_FLAG,
190 INST_GROUP_BY_PAYMENT_METHOD,
191 INACTIVE_DATE
192 ) values (
193 X_REMIT_PMT_DETAILS_LENGTH_LIM,
194 X_REMIT_REMITTANCE_ADVICE_FORM,
195 X_REMIT_SRA_OVERRIDE_PAYEE_FLA,
196 X_REMIT_ADVICE_DELIVERY_METHOD,
197 X_REMIT_AUTOMATIC_SRA_SUBMIT,
198 X_OBJECT_VERSION_NUMBER,
199 X_PMT_PAYMENT_DETAILS_FORMULA,
200 X_INST_GROUP_BY_LEGAL_ENTITY,
201 X_INST_GROUP_BY_BILL_PAYABLE,
202 X_INST_GROUP_BY_MAX_INSTRUCTIO,
203 X_INST_GROUP_BY_ORGANIZATION,
204 X_INST_GROUP_BY_PAYMENT_FUNCTI,
205 X_INST_GROUP_BY_PAYMENT_REASON,
206 X_INST_GROUP_BY_RFC,
207 X_INST_MAX_PAYMENTS_PER_INSTR,
208 X_INST_MAX_AMOUNT_PER_INSTR_VA,
209 X_INST_MAX_AMOUNT_PER_INSTR_CU,
210 X_INST_MAX_AMOUNT_FX_RATE_TYPE,
211 X_INST_SORT_OPTION_1,
212 X_INST_SORT_ORDER_1,
213 X_INST_SORT_OPTION_2,
214 X_POSITIVE_PAY_DELIVERY_FLAG,
215 X_TRANSMIT_INSTR_IMMED_FLAG,
216 X_AUTOMATIC_PI_REG_SUBMIT,
217 X_PMT_GROUP_BY_DELIVERY_CHANNE,
218 X_PMT_GROUP_BY_REMITTANCE_MES,
219 X_PMT_GROUP_BY_MAX_DOCUMENTS,
220 X_PMT_GROUP_BY_UNIQUE_REMIT_ID,
221 X_PMT_GROUP_BY_SETTLE_PRIORITY,
222 X_PMT_GROUP_BY_PAYMENT_REASON,
223 X_PMT_GROUP_BY_DUE_DATE_FLAG,
224 X_PMT_MAX_DOCUMENTS_PER_PAYMEN,
225 X_PMT_PAYMENT_DETAILS_LENGTH_L,
226 X_PMT_GROUP_BY_PAYMENT_DETAILS,
227 X_PMT_GROUP_BY_BANK_CHARGE_BEA,
228 X_INST_SORT_ORDER_2,
229 X_INST_SORT_OPTION_3,
230 X_INST_SORT_ORDER_3,
231 X_REMIT_DOCUMENT_COUNT_LIMIT,
232 X_REMIT_ALLOW_MULTIPLE_COPY_FL,
233 X_INST_GROUP_BY_PAYMENT_DATE,
234 X_INST_GROUP_BY_PAYMENT_CURREN,
235 X_INST_GROUP_BY_INT_BANK_ACCT,
236 X_INST_GROUP_BY_MAX_PAYMENTS,
237 X_INST_GROUP_BY_PAY_SERVICE_RE,
238 X_DCL_ONLY_FOREIGN_CURR_PMT_FL,
239 X_DECLARATION_REPORT_FORMAT_CO,
240 X_DECLARATION_CURR_FX_RATE_TYP,
241 X_DECLARATION_CURRENCY_CODE,
242 X_DECLARATION_THRESHOLD_AMOUNT,
243 X_BANK_INSTRUCTION1_CODE,
244 X_BANK_INSTRUCTION2_CODE,
245 X_BANK_INSTRUCTION_DETAILS,
246 X_PAYMENT_TEXT_MESSAGE1,
247 X_PAYMENT_TEXT_MESSAGE2,
248 X_ACK_TRANSMIT_PROTOCOL_CODE,
249 X_ACK_SECURITY_PROTOCOL_CODE,
250 X_SEND_TO_FILE_FLAG,
251 X_PI_REGISTER_FORMAT,
252 X_BEP_ACCOUNT_ID,
253 X_APPLICABLE_PMT_METHOD,
254 X_APPLICABLE_CURRENCY,
255 X_APPLICABLE_PAYER_ORG,
256 X_APPLICABLE_INT_BANK_ACCOUNT,
257 X_SYSTEM_PROFILE_CODE,
258 X_BEP_SUFFIX,
259 X_SECURITY_PROTOCOL_CODE,
260 X_TRANSMIT_PROTOCOL_CODE,
261 X_PAYMENT_FORMAT_CODE,
262 X_POSITIVE_PAY_FORMAT_CODE,
263 X_PAY_FILE_LETTER_FORMAT_CODE,
264 X_PRINT_INSTRUCTION_IMMED_FLAG,
265 X_PERIODIC_SEQUENCE_NAME_1,
266 X_PERIODIC_SEQUENCE_NAME_2,
267 X_PERIODIC_SEQUENCE_NAME_3,
268 X_PROCESSING_TYPE,
269 X_MARK_COMPLETE_EVENT,
270 X_MANUAL_MARK_COMPLETE_FLAG,
271 X_DECLARATION_OPTION,
272 X_CREATION_DATE,
273 X_CREATED_BY,
274 X_LAST_UPDATE_DATE,
275 X_LAST_UPDATED_BY,
276 X_LAST_UPDATE_LOGIN,
277 X_ELECTRONIC_PROCESSING_CHNNL,
278 X_LOGICAL_GROUPING_MODE,
279 X_BATCH_BOOKING_FLAG,
280 X_INST_GROUP_BY_PAYMENT_METHOD,
281 X_INACTIVE_DATE
282 );
283
284 insert into IBY_UPG_PPP_TL (
285 SYSTEM_PROFILE_CODE,
286 SYSTEM_PROFILE_NAME,
287 SYSTEM_PROFILE_DESCRIPTION,
288 CREATED_BY,
289 CREATION_DATE,
290 LAST_UPDATED_BY,
291 LAST_UPDATE_DATE,
292 LAST_UPDATE_LOGIN,
293 OBJECT_VERSION_NUMBER,
294 LANGUAGE,
295 SOURCE_LANG
296 ) select
297 X_SYSTEM_PROFILE_CODE,
298 X_SYSTEM_PROFILE_NAME,
299 X_SYSTEM_PROFILE_DESCRIPTION,
300 X_CREATED_BY,
301 X_CREATION_DATE,
302 X_LAST_UPDATED_BY,
303 X_LAST_UPDATE_DATE,
304 X_LAST_UPDATE_LOGIN,
305 X_OBJECT_VERSION_NUMBER,
306 L.LANGUAGE_CODE,
307 userenv('LANG')
308 from FND_LANGUAGES L
309 where L.INSTALLED_FLAG in ('I', 'B')
310 and not exists
311 (select NULL
312 from IBY_UPG_PPP_TL T
313 where T.SYSTEM_PROFILE_CODE = X_SYSTEM_PROFILE_CODE
314 and T.LANGUAGE = L.LANGUAGE_CODE);
315
316 open c;
317 fetch c into X_ROWID;
318 if (c%notfound) then
319 close c;
320 raise no_data_found;
321 end if;
322 close c;
323 -- New for Online Patching
324 /* AD_ZD_SEED.PREPARE('iby_sys_pmt_profiles_b');
325 AD_ZD_SEED.PREPARE('iby_sys_pmt_profiles_tl');
326 AD_ZD_SEED.PREPARE('iby_acct_pmt_profiles_b');
327 AD_ZD_SEED.PREPARE('iby_acct_pmt_profiles_tl');
328 AD_ZD_SEED.PREPARE('iby_applicable_pmt_profs');
329 AD_ZD_SEED.PREPARE('iby_pmt_creation_rules');
330 AD_ZD_SEED.PREPARE('iby_instr_creation_rules');
331 AD_ZD_SEED.PREPARE('iby_remit_advice_setup'); */
332
333 set_sys_user_profiles();
334
335 end INSERT_ROW;
336
337 procedure LOCK_ROW (
338 X_SYSTEM_PROFILE_CODE in VARCHAR2,
339 X_REMIT_PMT_DETAILS_LENGTH_LIM in NUMBER,
340 X_REMIT_REMITTANCE_ADVICE_FORM in VARCHAR2,
341 X_REMIT_SRA_OVERRIDE_PAYEE_FLA in VARCHAR2,
342 X_REMIT_ADVICE_DELIVERY_METHOD in VARCHAR2,
343 X_REMIT_AUTOMATIC_SRA_SUBMIT in VARCHAR2,
344 X_OBJECT_VERSION_NUMBER in NUMBER,
345 X_PMT_PAYMENT_DETAILS_FORMULA in VARCHAR2,
346 X_INST_GROUP_BY_LEGAL_ENTITY in VARCHAR2,
347 X_INST_GROUP_BY_BILL_PAYABLE in VARCHAR2,
348 X_INST_GROUP_BY_MAX_INSTRUCTIO in VARCHAR2,
349 X_INST_GROUP_BY_ORGANIZATION in VARCHAR2,
350 X_INST_GROUP_BY_PAYMENT_FUNCTI in VARCHAR2,
351 X_INST_GROUP_BY_PAYMENT_REASON in VARCHAR2,
352 X_INST_GROUP_BY_RFC in VARCHAR2,
353 X_INST_MAX_PAYMENTS_PER_INSTR in NUMBER,
354 X_INST_MAX_AMOUNT_PER_INSTR_VA in NUMBER,
355 X_INST_MAX_AMOUNT_PER_INSTR_CU in VARCHAR2,
356 X_INST_MAX_AMOUNT_FX_RATE_TYPE in VARCHAR2,
357 X_INST_SORT_OPTION_1 in VARCHAR2,
358 X_INST_SORT_ORDER_1 in VARCHAR2,
359 X_INST_SORT_OPTION_2 in VARCHAR2,
360 X_POSITIVE_PAY_DELIVERY_FLAG in VARCHAR2,
361 X_TRANSMIT_INSTR_IMMED_FLAG in VARCHAR2,
362 X_AUTOMATIC_PI_REG_SUBMIT in VARCHAR2,
363 X_PMT_GROUP_BY_DELIVERY_CHANNE in VARCHAR2,
364 X_PMT_GROUP_BY_REMITTANCE_MES in VARCHAR2,
365 X_PMT_GROUP_BY_MAX_DOCUMENTS in VARCHAR2,
366 X_PMT_GROUP_BY_UNIQUE_REMIT_ID in VARCHAR2,
367 X_PMT_GROUP_BY_SETTLE_PRIORITY in VARCHAR2,
368 X_PMT_GROUP_BY_PAYMENT_REASON in VARCHAR2,
369 X_PMT_GROUP_BY_DUE_DATE_FLAG in VARCHAR2,
370 X_PMT_MAX_DOCUMENTS_PER_PAYMEN in NUMBER,
371 X_PMT_PAYMENT_DETAILS_LENGTH_L in NUMBER,
372 X_PMT_GROUP_BY_PAYMENT_DETAILS in VARCHAR2,
373 X_PMT_GROUP_BY_BANK_CHARGE_BEA in VARCHAR2,
374 X_INST_SORT_ORDER_2 in VARCHAR2,
375 X_INST_SORT_OPTION_3 in VARCHAR2,
376 X_INST_SORT_ORDER_3 in VARCHAR2,
377 X_REMIT_DOCUMENT_COUNT_LIMIT in NUMBER,
378 X_REMIT_ALLOW_MULTIPLE_COPY_FL in VARCHAR2,
379 X_INST_GROUP_BY_PAYMENT_DATE in VARCHAR2,
380 X_INST_GROUP_BY_PAYMENT_CURREN in VARCHAR2,
381 X_INST_GROUP_BY_INT_BANK_ACCT in VARCHAR2,
382 X_INST_GROUP_BY_MAX_PAYMENTS in VARCHAR2,
383 X_INST_GROUP_BY_PAY_SERVICE_RE in VARCHAR2,
384 X_DCL_ONLY_FOREIGN_CURR_PMT_FL in VARCHAR2,
385 X_DECLARATION_REPORT_FORMAT_CO in VARCHAR2,
386 X_DECLARATION_CURR_FX_RATE_TYP in VARCHAR2,
387 X_DECLARATION_CURRENCY_CODE in VARCHAR2,
388 X_DECLARATION_THRESHOLD_AMOUNT in NUMBER,
389 X_BANK_INSTRUCTION1_CODE in VARCHAR2,
390 X_BANK_INSTRUCTION2_CODE in VARCHAR2,
391 X_BANK_INSTRUCTION_DETAILS in VARCHAR2,
392 X_PAYMENT_TEXT_MESSAGE1 in VARCHAR2,
393 X_PAYMENT_TEXT_MESSAGE2 in VARCHAR2,
394 X_ACK_TRANSMIT_PROTOCOL_CODE in VARCHAR2,
395 X_ACK_SECURITY_PROTOCOL_CODE in VARCHAR2,
396 X_SEND_TO_FILE_FLAG in VARCHAR2,
397 X_PI_REGISTER_FORMAT in VARCHAR2,
398 X_BEP_ACCOUNT_ID in NUMBER,
399 X_APPLICABLE_PMT_METHOD in VARCHAR2,
400 X_APPLICABLE_CURRENCY in VARCHAR2,
401 X_APPLICABLE_PAYER_ORG in VARCHAR2,
402 X_APPLICABLE_INT_BANK_ACCOUNT in VARCHAR2,
403 X_BEP_SUFFIX in VARCHAR2,
404 X_SECURITY_PROTOCOL_CODE in VARCHAR2,
405 X_TRANSMIT_PROTOCOL_CODE in VARCHAR2,
406 X_PAYMENT_FORMAT_CODE in VARCHAR2,
407 X_POSITIVE_PAY_FORMAT_CODE in VARCHAR2,
408 X_PAY_FILE_LETTER_FORMAT_CODE in VARCHAR2,
409 X_PRINT_INSTRUCTION_IMMED_FLAG in VARCHAR2,
410 X_PERIODIC_SEQUENCE_NAME_1 in VARCHAR2,
411 X_PERIODIC_SEQUENCE_NAME_2 in VARCHAR2,
412 X_PERIODIC_SEQUENCE_NAME_3 in VARCHAR2,
413 X_PROCESSING_TYPE in VARCHAR2,
414 X_MARK_COMPLETE_EVENT in VARCHAR2,
415 X_MANUAL_MARK_COMPLETE_FLAG in VARCHAR2,
416 X_DECLARATION_OPTION in VARCHAR2,
417 X_SYSTEM_PROFILE_NAME in VARCHAR2,
418 X_SYSTEM_PROFILE_DESCRIPTION in VARCHAR2,
419 X_ELECTRONIC_PROCESSING_CHNNL in VARCHAR2,
420 X_LOGICAL_GROUPING_MODE in VARCHAR2,
421 X_BATCH_BOOKING_FLAG in VARCHAR2,
422 X_INST_GROUP_BY_PAYMENT_METHOD in VARCHAR2
423 ) is
424 cursor c is select
425 REMIT_PMT_DETAILS_LENGTH_LIM,
426 REMIT_REMITTANCE_ADVICE_FORMAT,
427 REMIT_SRA_OVERRIDE_PAYEE_FLAG,
428 REMIT_ADVICE_DELIVERY_METHOD,
429 REMIT_AUTOMATIC_SRA_SUBMIT,
430 OBJECT_VERSION_NUMBER,
431 PMT_PAYMENT_DETAILS_FORMULA,
432 INST_GROUP_BY_LEGAL_ENTITY,
433 INST_GROUP_BY_BILL_PAYABLE,
434 INST_GROUP_BY_MAX_INSTRUCTION,
435 INST_GROUP_BY_ORGANIZATION,
436 INST_GROUP_BY_PAYMENT_FUNCTION,
437 INST_GROUP_BY_PAYMENT_REASON,
438 INST_GROUP_BY_RFC,
439 INST_MAX_PAYMENTS_PER_INSTR,
440 INST_MAX_AMOUNT_PER_INSTR_VAL,
441 INST_MAX_AMOUNT_PER_INSTR_CURR,
442 INST_MAX_AMOUNT_FX_RATE_TYPE,
443 INST_SORT_OPTION_1,
444 INST_SORT_ORDER_1,
445 INST_SORT_OPTION_2,
446 POSITIVE_PAY_DELIVERY_FLAG,
447 TRANSMIT_INSTR_IMMED_FLAG,
448 AUTOMATIC_PI_REG_SUBMIT,
449 PMT_GROUP_BY_DELIVERY_CHANNEL,
450 PMT_GROUP_BY_REMITTANCE_MES,
451 PMT_GROUP_BY_MAX_DOCUMENTS,
452 PMT_GROUP_BY_UNIQUE_REMIT_ID,
453 PMT_GROUP_BY_SETTLE_PRIORITY,
454 PMT_GROUP_BY_PAYMENT_REASON,
455 PMT_GROUP_BY_DUE_DATE_FLAG,
456 PMT_MAX_DOCUMENTS_PER_PAYMENT,
457 PMT_PAYMENT_DETAILS_LENGTH_LIM,
458 PMT_GROUP_BY_PAYMENT_DETAILS,
459 PMT_GROUP_BY_BANK_CHARGE_BEAR,
460 INST_SORT_ORDER_2,
461 INST_SORT_OPTION_3,
462 INST_SORT_ORDER_3,
463 REMIT_DOCUMENT_COUNT_LIMIT,
464 REMIT_ALLOW_MULTIPLE_COPY_FLAG,
465 INST_GROUP_BY_PAYMENT_DATE,
466 INST_GROUP_BY_PAYMENT_CURRENCY,
467 INST_GROUP_BY_INT_BANK_ACCT,
468 INST_GROUP_BY_MAX_PAYMENTS,
469 INST_GROUP_BY_PAY_SERVICE_REQ,
470 DCL_ONLY_FOREIGN_CURR_PMT_FLAG,
471 DECLARATION_REPORT_FORMAT_CODE,
472 DECLARATION_CURR_FX_RATE_TYPE,
473 DECLARATION_CURRENCY_CODE,
474 DECLARATION_THRESHOLD_AMOUNT,
475 BANK_INSTRUCTION1_CODE,
476 BANK_INSTRUCTION2_CODE,
477 BANK_INSTRUCTION_DETAILS,
478 PAYMENT_TEXT_MESSAGE1,
479 PAYMENT_TEXT_MESSAGE2,
480 ACK_TRANSMIT_PROTOCOL_CODE,
481 ACK_SECURITY_PROTOCOL_CODE,
482 SEND_TO_FILE_FLAG,
483 PI_REGISTER_FORMAT,
484 BEP_ACCOUNT_ID,
485 APPLICABLE_PMT_METHOD,
486 APPLICABLE_CURRENCY,
487 APPLICABLE_PAYER_ORG,
488 APPLICABLE_INT_BANK_ACCOUNT,
489 BEP_SUFFIX,
490 SECURITY_PROTOCOL_CODE,
491 TRANSMIT_PROTOCOL_CODE,
492 PAYMENT_FORMAT_CODE,
493 POSITIVE_PAY_FORMAT_CODE,
494 PAY_FILE_LETTER_FORMAT_CODE,
495 PRINT_INSTRUCTION_IMMED_FLAG,
496 PERIODIC_SEQUENCE_NAME_1,
497 PERIODIC_SEQUENCE_NAME_2,
498 PERIODIC_SEQUENCE_NAME_3,
499 PROCESSING_TYPE,
500 MARK_COMPLETE_EVENT,
501 MANUAL_MARK_COMPLETE_FLAG,
502 DECLARATION_OPTION,
503 ELECTRONIC_PROCESSING_CHANNEL,
504 LOGICAL_GROUPING_MODE,
505 BATCH_BOOKING_FLAG,
506 INST_GROUP_BY_PAYMENT_METHOD
507 from IBY_UPG_PPP_B
508 where SYSTEM_PROFILE_CODE = X_SYSTEM_PROFILE_CODE
509 for update of SYSTEM_PROFILE_CODE nowait;
510 recinfo c%rowtype;
511
512 cursor c1 is select
513 SYSTEM_PROFILE_NAME,
514 SYSTEM_PROFILE_DESCRIPTION,
515 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
516 from IBY_UPG_PPP_TL
517 where SYSTEM_PROFILE_CODE = X_SYSTEM_PROFILE_CODE
518 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
519 for update of SYSTEM_PROFILE_CODE nowait;
520 begin
521 open c;
522 fetch c into recinfo;
523 if (c%notfound) then
524 close c;
525 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
526 app_exception.raise_exception;
527 end if;
528 close c;
529 if ( ((recinfo.REMIT_PMT_DETAILS_LENGTH_LIM = X_REMIT_PMT_DETAILS_LENGTH_LIM)
530 OR ((recinfo.REMIT_PMT_DETAILS_LENGTH_LIM is null) AND (X_REMIT_PMT_DETAILS_LENGTH_LIM is null)))
531 AND ((recinfo.REMIT_REMITTANCE_ADVICE_FORMAT = X_REMIT_REMITTANCE_ADVICE_FORM)
532 OR ((recinfo.REMIT_REMITTANCE_ADVICE_FORMAT is null) AND (X_REMIT_REMITTANCE_ADVICE_FORM is null)))
533 AND (recinfo.REMIT_SRA_OVERRIDE_PAYEE_FLAG = X_REMIT_SRA_OVERRIDE_PAYEE_FLA)
534 AND ((recinfo.REMIT_ADVICE_DELIVERY_METHOD = X_REMIT_ADVICE_DELIVERY_METHOD)
535 OR ((recinfo.REMIT_ADVICE_DELIVERY_METHOD is null) AND (X_REMIT_ADVICE_DELIVERY_METHOD is null)))
536 AND (recinfo.REMIT_AUTOMATIC_SRA_SUBMIT = X_REMIT_AUTOMATIC_SRA_SUBMIT)
537 AND (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
538 AND ((recinfo.PMT_PAYMENT_DETAILS_FORMULA = X_PMT_PAYMENT_DETAILS_FORMULA)
539 OR ((recinfo.PMT_PAYMENT_DETAILS_FORMULA is null) AND (X_PMT_PAYMENT_DETAILS_FORMULA is null)))
540 AND (recinfo.INST_GROUP_BY_LEGAL_ENTITY = X_INST_GROUP_BY_LEGAL_ENTITY)
541 AND (recinfo.INST_GROUP_BY_BILL_PAYABLE = X_INST_GROUP_BY_BILL_PAYABLE)
542 AND (recinfo.INST_GROUP_BY_MAX_INSTRUCTION = X_INST_GROUP_BY_MAX_INSTRUCTIO)
543 AND (recinfo.INST_GROUP_BY_ORGANIZATION = X_INST_GROUP_BY_ORGANIZATION)
544 AND (recinfo.INST_GROUP_BY_PAYMENT_FUNCTION = X_INST_GROUP_BY_PAYMENT_FUNCTI)
545 AND (recinfo.INST_GROUP_BY_PAYMENT_REASON = X_INST_GROUP_BY_PAYMENT_REASON)
546 AND (recinfo.INST_GROUP_BY_RFC = X_INST_GROUP_BY_RFC)
547 AND ((recinfo.INST_MAX_PAYMENTS_PER_INSTR = X_INST_MAX_PAYMENTS_PER_INSTR)
548 OR ((recinfo.INST_MAX_PAYMENTS_PER_INSTR is null) AND (X_INST_MAX_PAYMENTS_PER_INSTR is null)))
549 AND ((recinfo.INST_MAX_AMOUNT_PER_INSTR_VAL = X_INST_MAX_AMOUNT_PER_INSTR_VA)
550 OR ((recinfo.INST_MAX_AMOUNT_PER_INSTR_VAL is null) AND (X_INST_MAX_AMOUNT_PER_INSTR_VA is null)))
551 AND ((recinfo.INST_MAX_AMOUNT_PER_INSTR_CURR = X_INST_MAX_AMOUNT_PER_INSTR_CU)
552 OR ((recinfo.INST_MAX_AMOUNT_PER_INSTR_CURR is null) AND (X_INST_MAX_AMOUNT_PER_INSTR_CU is null)))
553 AND ((recinfo.INST_MAX_AMOUNT_FX_RATE_TYPE = X_INST_MAX_AMOUNT_FX_RATE_TYPE)
554 OR ((recinfo.INST_MAX_AMOUNT_FX_RATE_TYPE is null) AND (X_INST_MAX_AMOUNT_FX_RATE_TYPE is null)))
555 AND ((recinfo.INST_SORT_OPTION_1 = X_INST_SORT_OPTION_1)
556 OR ((recinfo.INST_SORT_OPTION_1 is null) AND (X_INST_SORT_OPTION_1 is null)))
557 AND ((recinfo.INST_SORT_ORDER_1 = X_INST_SORT_ORDER_1)
558 OR ((recinfo.INST_SORT_ORDER_1 is null) AND (X_INST_SORT_ORDER_1 is null)))
559 AND ((recinfo.INST_SORT_OPTION_2 = X_INST_SORT_OPTION_2)
560 OR ((recinfo.INST_SORT_OPTION_2 is null) AND (X_INST_SORT_OPTION_2 is null)))
561 AND (recinfo.POSITIVE_PAY_DELIVERY_FLAG = X_POSITIVE_PAY_DELIVERY_FLAG)
562 AND (recinfo.TRANSMIT_INSTR_IMMED_FLAG = X_TRANSMIT_INSTR_IMMED_FLAG)
563 AND (recinfo.AUTOMATIC_PI_REG_SUBMIT = X_AUTOMATIC_PI_REG_SUBMIT)
564 AND (recinfo.PMT_GROUP_BY_DELIVERY_CHANNEL = X_PMT_GROUP_BY_DELIVERY_CHANNE)
565 AND (recinfo.PMT_GROUP_BY_REMITTANCE_MES = X_PMT_GROUP_BY_REMITTANCE_MES)
566 AND (recinfo.PMT_GROUP_BY_MAX_DOCUMENTS = X_PMT_GROUP_BY_MAX_DOCUMENTS)
567 AND (recinfo.PMT_GROUP_BY_UNIQUE_REMIT_ID = X_PMT_GROUP_BY_UNIQUE_REMIT_ID)
568 AND (recinfo.PMT_GROUP_BY_SETTLE_PRIORITY = X_PMT_GROUP_BY_SETTLE_PRIORITY)
569 AND (recinfo.PMT_GROUP_BY_PAYMENT_REASON = X_PMT_GROUP_BY_PAYMENT_REASON)
570 AND (recinfo.PMT_GROUP_BY_DUE_DATE_FLAG = X_PMT_GROUP_BY_DUE_DATE_FLAG)
571 AND ((recinfo.PMT_MAX_DOCUMENTS_PER_PAYMENT = X_PMT_MAX_DOCUMENTS_PER_PAYMEN)
572 OR ((recinfo.PMT_MAX_DOCUMENTS_PER_PAYMENT is null) AND (X_PMT_MAX_DOCUMENTS_PER_PAYMEN is null)))
573 AND ((recinfo.PMT_PAYMENT_DETAILS_LENGTH_LIM = X_PMT_PAYMENT_DETAILS_LENGTH_L)
574 OR ((recinfo.PMT_PAYMENT_DETAILS_LENGTH_LIM is null) AND (X_PMT_PAYMENT_DETAILS_LENGTH_L is null)))
575 AND (recinfo.PMT_GROUP_BY_PAYMENT_DETAILS = X_PMT_GROUP_BY_PAYMENT_DETAILS)
576 AND (recinfo.PMT_GROUP_BY_BANK_CHARGE_BEAR = X_PMT_GROUP_BY_BANK_CHARGE_BEA)
577 AND ((recinfo.INST_SORT_ORDER_2 = X_INST_SORT_ORDER_2)
578 OR ((recinfo.INST_SORT_ORDER_2 is null) AND (X_INST_SORT_ORDER_2 is null)))
579 AND ((recinfo.INST_SORT_OPTION_3 = X_INST_SORT_OPTION_3)
580 OR ((recinfo.INST_SORT_OPTION_3 is null) AND (X_INST_SORT_OPTION_3 is null)))
581 AND ((recinfo.INST_SORT_ORDER_3 = X_INST_SORT_ORDER_3)
582 OR ((recinfo.INST_SORT_ORDER_3 is null) AND (X_INST_SORT_ORDER_3 is null)))
583 AND ((recinfo.REMIT_DOCUMENT_COUNT_LIMIT = X_REMIT_DOCUMENT_COUNT_LIMIT)
584 OR ((recinfo.REMIT_DOCUMENT_COUNT_LIMIT is null) AND (X_REMIT_DOCUMENT_COUNT_LIMIT is null)))
585 AND ((recinfo.REMIT_ALLOW_MULTIPLE_COPY_FLAG = X_REMIT_ALLOW_MULTIPLE_COPY_FL)
586 OR ((recinfo.REMIT_ALLOW_MULTIPLE_COPY_FLAG is null) AND (X_REMIT_ALLOW_MULTIPLE_COPY_FL is null)))
587 AND (recinfo.INST_GROUP_BY_PAYMENT_DATE = X_INST_GROUP_BY_PAYMENT_DATE)
588 AND (recinfo.INST_GROUP_BY_PAYMENT_CURRENCY = X_INST_GROUP_BY_PAYMENT_CURREN)
589 AND (recinfo.INST_GROUP_BY_INT_BANK_ACCT = X_INST_GROUP_BY_INT_BANK_ACCT)
590 AND (recinfo.INST_GROUP_BY_MAX_PAYMENTS = X_INST_GROUP_BY_MAX_PAYMENTS)
591 AND (recinfo.INST_GROUP_BY_PAY_SERVICE_REQ = X_INST_GROUP_BY_PAY_SERVICE_RE)
592 AND ((recinfo.DCL_ONLY_FOREIGN_CURR_PMT_FLAG = X_DCL_ONLY_FOREIGN_CURR_PMT_FL)
593 OR ((recinfo.DCL_ONLY_FOREIGN_CURR_PMT_FLAG is null) AND (X_DCL_ONLY_FOREIGN_CURR_PMT_FL is null)))
594 AND ((recinfo.DECLARATION_REPORT_FORMAT_CODE = X_DECLARATION_REPORT_FORMAT_CO)
595 OR ((recinfo.DECLARATION_REPORT_FORMAT_CODE is null) AND (X_DECLARATION_REPORT_FORMAT_CO is null)))
596 AND ((recinfo.DECLARATION_CURR_FX_RATE_TYPE = X_DECLARATION_CURR_FX_RATE_TYP)
597 OR ((recinfo.DECLARATION_CURR_FX_RATE_TYPE is null) AND (X_DECLARATION_CURR_FX_RATE_TYP is null)))
598 AND ((recinfo.DECLARATION_CURRENCY_CODE = X_DECLARATION_CURRENCY_CODE)
599 OR ((recinfo.DECLARATION_CURRENCY_CODE is null) AND (X_DECLARATION_CURRENCY_CODE is null)))
600 AND ((recinfo.DECLARATION_THRESHOLD_AMOUNT = X_DECLARATION_THRESHOLD_AMOUNT)
601 OR ((recinfo.DECLARATION_THRESHOLD_AMOUNT is null) AND (X_DECLARATION_THRESHOLD_AMOUNT is null)))
602 AND ((recinfo.BANK_INSTRUCTION1_CODE = X_BANK_INSTRUCTION1_CODE)
603 OR ((recinfo.BANK_INSTRUCTION1_CODE is null) AND (X_BANK_INSTRUCTION1_CODE is null)))
604 AND ((recinfo.BANK_INSTRUCTION2_CODE = X_BANK_INSTRUCTION2_CODE)
605 OR ((recinfo.BANK_INSTRUCTION2_CODE is null) AND (X_BANK_INSTRUCTION2_CODE is null)))
606 AND ((recinfo.BANK_INSTRUCTION_DETAILS = X_BANK_INSTRUCTION_DETAILS)
607 OR ((recinfo.BANK_INSTRUCTION_DETAILS is null) AND (X_BANK_INSTRUCTION_DETAILS is null)))
608 AND ((recinfo.PAYMENT_TEXT_MESSAGE1 = X_PAYMENT_TEXT_MESSAGE1)
609 OR ((recinfo.PAYMENT_TEXT_MESSAGE1 is null) AND (X_PAYMENT_TEXT_MESSAGE1 is null)))
610 AND ((recinfo.PAYMENT_TEXT_MESSAGE2 = X_PAYMENT_TEXT_MESSAGE2)
611 OR ((recinfo.PAYMENT_TEXT_MESSAGE2 is null) AND (X_PAYMENT_TEXT_MESSAGE2 is null)))
612 AND ((recinfo.ACK_TRANSMIT_PROTOCOL_CODE = X_ACK_TRANSMIT_PROTOCOL_CODE)
613 OR ((recinfo.ACK_TRANSMIT_PROTOCOL_CODE is null) AND (X_ACK_TRANSMIT_PROTOCOL_CODE is null)))
614 AND ((recinfo.ACK_SECURITY_PROTOCOL_CODE = X_ACK_SECURITY_PROTOCOL_CODE)
615 OR ((recinfo.ACK_SECURITY_PROTOCOL_CODE is null) AND (X_ACK_SECURITY_PROTOCOL_CODE is null)))
616 AND (recinfo.SEND_TO_FILE_FLAG = X_SEND_TO_FILE_FLAG)
617 AND ((recinfo.PI_REGISTER_FORMAT = X_PI_REGISTER_FORMAT)
618 OR ((recinfo.PI_REGISTER_FORMAT is null) AND (X_PI_REGISTER_FORMAT is null)))
619 AND ((recinfo.BEP_ACCOUNT_ID = X_BEP_ACCOUNT_ID)
620 OR ((recinfo.BEP_ACCOUNT_ID is null) AND (X_BEP_ACCOUNT_ID is null)))
621 AND ((recinfo.APPLICABLE_PMT_METHOD = X_APPLICABLE_PMT_METHOD)
622 OR ((recinfo.APPLICABLE_PMT_METHOD is null) AND (X_APPLICABLE_PMT_METHOD is null)))
623 AND ((recinfo.APPLICABLE_CURRENCY = X_APPLICABLE_CURRENCY)
624 OR ((recinfo.APPLICABLE_CURRENCY is null) AND (X_APPLICABLE_CURRENCY is null)))
625 AND ((recinfo.APPLICABLE_PAYER_ORG = X_APPLICABLE_PAYER_ORG)
626 OR ((recinfo.APPLICABLE_PAYER_ORG is null) AND (X_APPLICABLE_PAYER_ORG is null)))
627 AND ((recinfo.APPLICABLE_INT_BANK_ACCOUNT = X_APPLICABLE_INT_BANK_ACCOUNT)
628 OR ((recinfo.APPLICABLE_INT_BANK_ACCOUNT is null) AND (X_APPLICABLE_INT_BANK_ACCOUNT is null)))
629 AND ((recinfo.BEP_SUFFIX = X_BEP_SUFFIX)
630 OR ((recinfo.BEP_SUFFIX is null) AND (X_BEP_SUFFIX is null)))
631 AND ((recinfo.SECURITY_PROTOCOL_CODE = X_SECURITY_PROTOCOL_CODE)
632 OR ((recinfo.SECURITY_PROTOCOL_CODE is null) AND (X_SECURITY_PROTOCOL_CODE is null)))
633 AND ((recinfo.TRANSMIT_PROTOCOL_CODE = X_TRANSMIT_PROTOCOL_CODE)
634 OR ((recinfo.TRANSMIT_PROTOCOL_CODE is null) AND (X_TRANSMIT_PROTOCOL_CODE is null)))
635 AND (recinfo.PAYMENT_FORMAT_CODE = X_PAYMENT_FORMAT_CODE)
636 AND ((recinfo.POSITIVE_PAY_FORMAT_CODE = X_POSITIVE_PAY_FORMAT_CODE)
637 OR ((recinfo.POSITIVE_PAY_FORMAT_CODE is null) AND (X_POSITIVE_PAY_FORMAT_CODE is null)))
638 AND ((recinfo.PAY_FILE_LETTER_FORMAT_CODE = X_PAY_FILE_LETTER_FORMAT_CODE)
639 OR ((recinfo.PAY_FILE_LETTER_FORMAT_CODE is null) AND (X_PAY_FILE_LETTER_FORMAT_CODE is null)))
640 AND (recinfo.PRINT_INSTRUCTION_IMMED_FLAG = X_PRINT_INSTRUCTION_IMMED_FLAG)
641 AND ((recinfo.PERIODIC_SEQUENCE_NAME_1 = X_PERIODIC_SEQUENCE_NAME_1)
642 OR ((recinfo.PERIODIC_SEQUENCE_NAME_1 is null) AND (X_PERIODIC_SEQUENCE_NAME_1 is null)))
643 AND ((recinfo.PERIODIC_SEQUENCE_NAME_2 = X_PERIODIC_SEQUENCE_NAME_2)
644 OR ((recinfo.PERIODIC_SEQUENCE_NAME_2 is null) AND (X_PERIODIC_SEQUENCE_NAME_2 is null)))
645 AND ((recinfo.PERIODIC_SEQUENCE_NAME_3 = X_PERIODIC_SEQUENCE_NAME_3)
646 OR ((recinfo.PERIODIC_SEQUENCE_NAME_3 is null) AND (X_PERIODIC_SEQUENCE_NAME_3 is null)))
647 AND (recinfo.PROCESSING_TYPE = X_PROCESSING_TYPE)
648 AND (recinfo.MARK_COMPLETE_EVENT = X_MARK_COMPLETE_EVENT)
649 AND (recinfo.MANUAL_MARK_COMPLETE_FLAG = X_MANUAL_MARK_COMPLETE_FLAG)
650 AND ((recinfo.DECLARATION_OPTION = X_DECLARATION_OPTION)
651 OR ((recinfo.DECLARATION_OPTION is null) AND (X_DECLARATION_OPTION is null)))
652 AND ((recinfo.ELECTRONIC_PROCESSING_CHANNEL = X_ELECTRONIC_PROCESSING_CHNNL)
653 OR ((recinfo.ELECTRONIC_PROCESSING_CHANNEL is null) AND (X_ELECTRONIC_PROCESSING_CHNNL is null)))
654 AND ((recinfo.LOGICAL_GROUPING_MODE = X_LOGICAL_GROUPING_MODE)
655 OR ((recinfo.LOGICAL_GROUPING_MODE is null) AND (X_LOGICAL_GROUPING_MODE is null)))
656 AND ((recinfo.BATCH_BOOKING_FLAG = X_BATCH_BOOKING_FLAG)
657 OR ((recinfo.BATCH_BOOKING_FLAG is null) AND (X_BATCH_BOOKING_FLAG is null)))
658 AND ((recinfo.INST_GROUP_BY_PAYMENT_METHOD = X_INST_GROUP_BY_PAYMENT_METHOD)
659 OR ((recinfo.INST_GROUP_BY_PAYMENT_METHOD is null) AND (X_INST_GROUP_BY_PAYMENT_METHOD is null)))
660 ) then
661 null;
662 else
663 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
664 app_exception.raise_exception;
665 end if;
666
667 for tlinfo in c1 loop
668 if (tlinfo.BASELANG = 'Y') then
669 if ( (tlinfo.SYSTEM_PROFILE_NAME = X_SYSTEM_PROFILE_NAME)
670 AND ((tlinfo.SYSTEM_PROFILE_DESCRIPTION = X_SYSTEM_PROFILE_DESCRIPTION)
671 OR ((tlinfo.SYSTEM_PROFILE_DESCRIPTION is null) AND (X_SYSTEM_PROFILE_DESCRIPTION is null)))
672 ) then
673 null;
674 else
675 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
676 app_exception.raise_exception;
677 end if;
678 end if;
679 end loop;
680 return;
681 end LOCK_ROW;
682
683 procedure UPDATE_ROW (
684 X_SYSTEM_PROFILE_CODE in VARCHAR2,
685 X_REMIT_PMT_DETAILS_LENGTH_LIM in NUMBER,
686 X_REMIT_REMITTANCE_ADVICE_FORM in VARCHAR2,
687 X_REMIT_SRA_OVERRIDE_PAYEE_FLA in VARCHAR2,
688 X_REMIT_ADVICE_DELIVERY_METHOD in VARCHAR2,
689 X_REMIT_AUTOMATIC_SRA_SUBMIT in VARCHAR2,
690 X_OBJECT_VERSION_NUMBER in NUMBER,
691 X_PMT_PAYMENT_DETAILS_FORMULA in VARCHAR2,
692 X_INST_GROUP_BY_LEGAL_ENTITY in VARCHAR2,
693 X_INST_GROUP_BY_BILL_PAYABLE in VARCHAR2,
694 X_INST_GROUP_BY_MAX_INSTRUCTIO in VARCHAR2,
695 X_INST_GROUP_BY_ORGANIZATION in VARCHAR2,
696 X_INST_GROUP_BY_PAYMENT_FUNCTI in VARCHAR2,
697 X_INST_GROUP_BY_PAYMENT_REASON in VARCHAR2,
698 X_INST_GROUP_BY_RFC in VARCHAR2,
699 X_INST_MAX_PAYMENTS_PER_INSTR in NUMBER,
700 X_INST_MAX_AMOUNT_PER_INSTR_VA in NUMBER,
701 X_INST_MAX_AMOUNT_PER_INSTR_CU in VARCHAR2,
702 X_INST_MAX_AMOUNT_FX_RATE_TYPE in VARCHAR2,
703 X_INST_SORT_OPTION_1 in VARCHAR2,
704 X_INST_SORT_ORDER_1 in VARCHAR2,
705 X_INST_SORT_OPTION_2 in VARCHAR2,
706 X_POSITIVE_PAY_DELIVERY_FLAG in VARCHAR2,
707 X_TRANSMIT_INSTR_IMMED_FLAG in VARCHAR2,
708 X_AUTOMATIC_PI_REG_SUBMIT in VARCHAR2,
709 X_PMT_GROUP_BY_DELIVERY_CHANNE in VARCHAR2,
710 X_PMT_GROUP_BY_REMITTANCE_MES in VARCHAR2,
711 X_PMT_GROUP_BY_MAX_DOCUMENTS in VARCHAR2,
712 X_PMT_GROUP_BY_UNIQUE_REMIT_ID in VARCHAR2,
713 X_PMT_GROUP_BY_SETTLE_PRIORITY in VARCHAR2,
714 X_PMT_GROUP_BY_PAYMENT_REASON in VARCHAR2,
715 X_PMT_GROUP_BY_DUE_DATE_FLAG in VARCHAR2,
716 X_PMT_MAX_DOCUMENTS_PER_PAYMEN in NUMBER,
717 X_PMT_PAYMENT_DETAILS_LENGTH_L in NUMBER,
718 X_PMT_GROUP_BY_PAYMENT_DETAILS in VARCHAR2,
719 X_PMT_GROUP_BY_BANK_CHARGE_BEA in VARCHAR2,
720 X_INST_SORT_ORDER_2 in VARCHAR2,
721 X_INST_SORT_OPTION_3 in VARCHAR2,
722 X_INST_SORT_ORDER_3 in VARCHAR2,
723 X_REMIT_DOCUMENT_COUNT_LIMIT in NUMBER,
724 X_REMIT_ALLOW_MULTIPLE_COPY_FL in VARCHAR2,
725 X_INST_GROUP_BY_PAYMENT_DATE in VARCHAR2,
726 X_INST_GROUP_BY_PAYMENT_CURREN in VARCHAR2,
727 X_INST_GROUP_BY_INT_BANK_ACCT in VARCHAR2,
728 X_INST_GROUP_BY_MAX_PAYMENTS in VARCHAR2,
729 X_INST_GROUP_BY_PAY_SERVICE_RE in VARCHAR2,
730 X_DCL_ONLY_FOREIGN_CURR_PMT_FL in VARCHAR2,
731 X_DECLARATION_REPORT_FORMAT_CO in VARCHAR2,
732 X_DECLARATION_CURR_FX_RATE_TYP in VARCHAR2,
733 X_DECLARATION_CURRENCY_CODE in VARCHAR2,
734 X_DECLARATION_THRESHOLD_AMOUNT in NUMBER,
735 X_BANK_INSTRUCTION1_CODE in VARCHAR2,
736 X_BANK_INSTRUCTION2_CODE in VARCHAR2,
737 X_BANK_INSTRUCTION_DETAILS in VARCHAR2,
738 X_PAYMENT_TEXT_MESSAGE1 in VARCHAR2,
739 X_PAYMENT_TEXT_MESSAGE2 in VARCHAR2,
740 X_ACK_TRANSMIT_PROTOCOL_CODE in VARCHAR2,
741 X_ACK_SECURITY_PROTOCOL_CODE in VARCHAR2,
742 X_SEND_TO_FILE_FLAG in VARCHAR2,
743 X_PI_REGISTER_FORMAT in VARCHAR2,
744 X_BEP_ACCOUNT_ID in NUMBER,
745 X_APPLICABLE_PMT_METHOD in VARCHAR2,
746 X_APPLICABLE_CURRENCY in VARCHAR2,
747 X_APPLICABLE_PAYER_ORG in VARCHAR2,
748 X_APPLICABLE_INT_BANK_ACCOUNT in VARCHAR2,
749 X_BEP_SUFFIX in VARCHAR2,
750 X_SECURITY_PROTOCOL_CODE in VARCHAR2,
751 X_TRANSMIT_PROTOCOL_CODE in VARCHAR2,
752 X_PAYMENT_FORMAT_CODE in VARCHAR2,
753 X_POSITIVE_PAY_FORMAT_CODE in VARCHAR2,
754 X_PAY_FILE_LETTER_FORMAT_CODE in VARCHAR2,
755 X_PRINT_INSTRUCTION_IMMED_FLAG in VARCHAR2,
756 X_PERIODIC_SEQUENCE_NAME_1 in VARCHAR2,
757 X_PERIODIC_SEQUENCE_NAME_2 in VARCHAR2,
758 X_PERIODIC_SEQUENCE_NAME_3 in VARCHAR2,
759 X_PROCESSING_TYPE in VARCHAR2,
760 X_MARK_COMPLETE_EVENT in VARCHAR2,
761 X_MANUAL_MARK_COMPLETE_FLAG in VARCHAR2,
762 X_DECLARATION_OPTION in VARCHAR2,
763 X_SYSTEM_PROFILE_NAME in VARCHAR2,
764 X_SYSTEM_PROFILE_DESCRIPTION in VARCHAR2,
765 X_LAST_UPDATE_DATE in DATE,
766 X_LAST_UPDATED_BY in NUMBER,
767 X_LAST_UPDATE_LOGIN in NUMBER,
768 X_ELECTRONIC_PROCESSING_CHNNL in VARCHAR2,
769 X_LOGICAL_GROUPING_MODE in VARCHAR2,
770 X_BATCH_BOOKING_FLAG in VARCHAR2,
771 X_INST_GROUP_BY_PAYMENT_METHOD in VARCHAR2,
772 X_INACTIVE_DATE in DATE
773 ) is
774
775
776 l_jenl_eft_reporting_limit NUMBER;
777 l_system_profile_code IBY_SYS_PMT_PROFILES_B.SYSTEM_PROFILE_CODE%TYPE;
778
779 TYPE t_sys_profile_code IS TABLE OF
780 IBY_SYS_PMT_PROFILES_B.system_profile_code%TYPE
781 INDEX BY BINARY_INTEGER;
782
783 l_sys_profile_code_tab t_sys_profile_code;
784 l_sys_profile_code_tab_cnt number;
785
786
787
788 cursor get_sys_prof_code(X_SYSTEM_PROFILE_CODE IBY_SYS_PMT_PROFILES_B.system_profile_code%TYPE, l_jenl_eft_reporting_limit NUMBER)
789 IS
790 select system_profile_code
791 from
792 (SELECT DISTINCT(decode(sfgdf.org_id,
793 NULL,ppp.system_profile_code || '_' ||
794 to_char(cf.check_format_id),
795 ppp.system_profile_code || '_' || to_char
796 (cf.check_format_id) || '_' || sfgdf.org_id))
797 system_profile_code
798 FROM
799 ap_check_formats cf,
800 ap_check_stocks_all cs,
801 ce_upg_bank_accounts cba,
802 ap_payment_programs pp,
803 jg_zz_pay_format_info gdf,
804 (SELECT(
805 CASE
806 WHEN sfa.jgzz_attribute_category =
807 'SE.JESEPBAI' THEN sfa.jgzz_system_info5
808 END) AS
809 payment_text_message1,
810 (
811 CASE
812 WHEN sfa.jgzz_attribute_category =
813 'SE.JESEPBAI' THEN sfa.jgzz_system_info3
814 END) AS
815 payment_text_message2,
816 (
817 CASE
818 WHEN sfa.jgzz_attribute_category =
819 'NO.JENOPTGN' THEN
820 sfa.jgzz_system_info20
821 WHEN sfa.jgzz_attribute_category =
822 'SE.JESEPBAI' THEN sfa.jgzz_system_info10
823 WHEN sfa.jgzz_attribute_category =
824 'SE.JESEPBSI' THEN sfa.jgzz_system_info8
825 WHEN sfa.jgzz_attribute_category =
826 'SE.JESEPBUT' THEN sfa.jgzz_system_info8
827 WHEN sfa.jgzz_attribute_category =
828 'SE.JESEPPOI' THEN sfa.jgzz_system_info11
829 WHEN sfa.jgzz_attribute_category =
830 'SE.JESEPPOU' THEN sfa.jgzz_system_info10
831 END) AS
832 outbound_pmt_file_directory,
833 (
834 CASE
835 WHEN sfa.jgzz_attribute_category =
836 'DE.JEDEDEFI' THEN decode
837 (sfa.jgzz_system_info3, 'Y',
838 'DECLARE_THROUGH_BANK', 'N',
839 'NO_DECLARATIONS',
840 sfa.jgzz_system_info3)
841 WHEN sfa.jgzz_attribute_category =
842 'NL.JENLFFGN' THEN decode(
843 l_jenl_eft_reporting_limit,
844 NULL,
845 'NO_DECLARATIONS',
846 'DECLARE_THROUGH_BANK')
847 END)
848 AS
849 declaration_option,
850 (
851 CASE
852 WHEN sfa.jgzz_attribute_category =
853 'DE.JEDEDEFI' THEN to_number
854 (sfa.jgzz_system_info5)
855 WHEN sfa.jgzz_attribute_category =
856 'NL.JENLFFGN' THEN
857 l_jenl_eft_reporting_limit
858 END)
859 AS
860 declaration_threshold_amount,
861 (
862 CASE
863 WHEN sfa.jgzz_attribute_category =
864 'DE.JEDEDEFI'
865 AND sfa.jgzz_system_info5 IS NOT NULL
866 THEN 'EUR'
867 WHEN sfa.jgzz_attribute_category =
868 'NL.JENLFFGN'
869 AND
870 l_jenl_eft_reporting_limit
871 IS
872 NOT NULL THEN
873 'NLG'
874 END)
875 AS
876 declaration_currency_code,
877 (
878 CASE
879 WHEN sfa.jgzz_attribute_category =
880 'NL.JENLFFGN' THEN sfa.jgzz_system_info13
881 WHEN sfa.jgzz_attribute_category =
882 'DE.JEDEDEFI' THEN decode
883 (sfa.jgzz_system_info3, 'Y', 'Corporate')
884 END) AS
885 declaration_curr_fx_rate_type,
886 sfa.jgzz_attribute_category,
887 SUBSTR(sfa.jgzz_attribute_category,
888 LENGTH(sfa.jgzz_country_code) + 2,
889 LENGTH(sfa.jgzz_attribute_category))
890 format_code,
891 sfa.org_id
892 FROM jg_zz_sys_formats_all_b sfa)
893 sfgdf,
894 iby_formats_b ipf,
895 iby_upg_ppp_b ppp,
896 iby_bepinfo bep,
897 iby_bepkeys KEY
898 WHERE cf.format_payments_program_id =
899 pp.program_id
900 AND cf.check_format_id =
901 cs.check_format_id(+)
902 AND cs.bank_account_id = cba.source_pk_id
903 (+)
904 AND cba.source_application_id(+) = 200
905 AND cf.check_format_id =
906 gdf.check_format_id(+)
907 AND pp.program_name =
908 ipf.reference_format_code
909 AND ipf.format_type_code =
910 'OUTBOUND_PAYMENT_INSTRUCTION'
911 AND pp.program_name = sfgdf.format_code
912 (+)
913 AND ppp.payment_format_code =
914 ipf.format_code
915 AND ppp.bep_suffix = bep.suffix(+)
916 AND bep.bepid = KEY.bepid(+)
917 AND ppp.system_profile_code =
918 X_SYSTEM_PROFILE_CODE
919
920 UNION ALL
921
922 SELECT distinct(ppb.system_profile_code) system_profile_code
923 FROM iby_formats_b ipf,
924 iby_upg_ppp_tl ppp,
925 iby_upg_ppp_b ppb,
926 iby_acct_pmt_profiles_b app,
927 iby_bepinfo bep,
928 iby_bepkeys key1
929 WHERE(ipf.reference_format_code IS NULL OR(ipf.reference_format_code IS NOT NULL
930 AND ipf.reference_format_code NOT IN
931 (SELECT pp.program_name
932 FROM ap_check_formats cf, ap_payment_programs pp
933 WHERE cf.format_payments_program_id = pp.program_id)))
934 AND ppb.payment_format_code = ipf.format_code
935 AND ipf.format_type_code = 'OUTBOUND_PAYMENT_INSTRUCTION'
936 AND ppp.system_profile_code = app.system_profile_code
937 AND ppp.system_profile_code = ppb.system_profile_code
938 AND ppb.bep_suffix = bep.suffix(+)
939 AND app.bep_account_id = key1.bep_account_id(+)
940 AND ppp.system_profile_code = X_SYSTEM_PROFILE_CODE) SYSTEM_PROF_CODE_TABLE;
941
942
943 BEGIN
944
945 BEGIN
946 SELECT TO_NUMBER(x.profile_option_value)
947 INTO l_jenl_eft_reporting_limit
948 FROM ( SELECT o.profile_option_name, v.profile_option_value,
949 row_number() OVER (partition by o.profile_option_name
950 order by count(*) desc) order_rank
951 FROM fnd_profile_options o,
952 fnd_profile_option_values v
953 WHERE o.profile_option_name = UPPER('JENL_REPORTING_THRESHOLD')
954 AND v.application_id = o.application_id
955 AND v.profile_option_id = o.profile_option_id
956 GROUP BY o.profile_option_name, v.profile_option_value) x
957 WHERE x.order_rank = 1;
958 EXCEPTION
959 WHEN others THEN NULL;
960 END;
961
962
963
964 -- kp_msg('The value for l_jenl_eft_reporting_limit: '|| l_jenl_eft_reporting_limit);
965
966 -- kp_msg('The value for x_system_profile_code'|| X_SYSTEM_PROFILE_CODE||'*******');
967
968 open get_sys_prof_code(X_SYSTEM_PROFILE_CODE,l_jenl_eft_reporting_limit);
969 FETCH get_sys_prof_code BULK COLLECT INTO l_sys_profile_code_tab;
970 l_sys_profile_code_tab_cnt := get_sys_prof_code%rowcount;
971 CLOSE get_sys_prof_code;
972
973
974 update IBY_UPG_PPP_B set
975 REMIT_PMT_DETAILS_LENGTH_LIM = X_REMIT_PMT_DETAILS_LENGTH_LIM,
976 REMIT_REMITTANCE_ADVICE_FORMAT = X_REMIT_REMITTANCE_ADVICE_FORM,
977 REMIT_SRA_OVERRIDE_PAYEE_FLAG = X_REMIT_SRA_OVERRIDE_PAYEE_FLA,
978 REMIT_ADVICE_DELIVERY_METHOD = X_REMIT_ADVICE_DELIVERY_METHOD,
979 REMIT_AUTOMATIC_SRA_SUBMIT = X_REMIT_AUTOMATIC_SRA_SUBMIT,
980 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
981 PMT_PAYMENT_DETAILS_FORMULA = X_PMT_PAYMENT_DETAILS_FORMULA,
982 INST_GROUP_BY_LEGAL_ENTITY = X_INST_GROUP_BY_LEGAL_ENTITY,
983 INST_GROUP_BY_BILL_PAYABLE = X_INST_GROUP_BY_BILL_PAYABLE,
984 INST_GROUP_BY_MAX_INSTRUCTION = X_INST_GROUP_BY_MAX_INSTRUCTIO,
985 INST_GROUP_BY_ORGANIZATION = X_INST_GROUP_BY_ORGANIZATION,
986 INST_GROUP_BY_PAYMENT_FUNCTION = X_INST_GROUP_BY_PAYMENT_FUNCTI,
987 INST_GROUP_BY_PAYMENT_REASON = X_INST_GROUP_BY_PAYMENT_REASON,
988 INST_GROUP_BY_RFC = X_INST_GROUP_BY_RFC,
989 INST_MAX_PAYMENTS_PER_INSTR = X_INST_MAX_PAYMENTS_PER_INSTR,
990 INST_MAX_AMOUNT_PER_INSTR_VAL = X_INST_MAX_AMOUNT_PER_INSTR_VA,
991 INST_MAX_AMOUNT_PER_INSTR_CURR = X_INST_MAX_AMOUNT_PER_INSTR_CU,
992 INST_MAX_AMOUNT_FX_RATE_TYPE = X_INST_MAX_AMOUNT_FX_RATE_TYPE,
993 INST_SORT_OPTION_1 = X_INST_SORT_OPTION_1,
994 INST_SORT_ORDER_1 = X_INST_SORT_ORDER_1,
995 INST_SORT_OPTION_2 = X_INST_SORT_OPTION_2,
996 POSITIVE_PAY_DELIVERY_FLAG = X_POSITIVE_PAY_DELIVERY_FLAG,
997 TRANSMIT_INSTR_IMMED_FLAG = X_TRANSMIT_INSTR_IMMED_FLAG,
998 AUTOMATIC_PI_REG_SUBMIT = X_AUTOMATIC_PI_REG_SUBMIT,
999 PMT_GROUP_BY_DELIVERY_CHANNEL = X_PMT_GROUP_BY_DELIVERY_CHANNE,
1000 PMT_GROUP_BY_REMITTANCE_MES = X_PMT_GROUP_BY_REMITTANCE_MES,
1001 PMT_GROUP_BY_MAX_DOCUMENTS = X_PMT_GROUP_BY_MAX_DOCUMENTS,
1002 PMT_GROUP_BY_UNIQUE_REMIT_ID = X_PMT_GROUP_BY_UNIQUE_REMIT_ID,
1003 PMT_GROUP_BY_SETTLE_PRIORITY = X_PMT_GROUP_BY_SETTLE_PRIORITY,
1004 PMT_GROUP_BY_PAYMENT_REASON = X_PMT_GROUP_BY_PAYMENT_REASON,
1005 PMT_GROUP_BY_DUE_DATE_FLAG = X_PMT_GROUP_BY_DUE_DATE_FLAG,
1006 PMT_MAX_DOCUMENTS_PER_PAYMENT = X_PMT_MAX_DOCUMENTS_PER_PAYMEN,
1007 PMT_PAYMENT_DETAILS_LENGTH_LIM = X_PMT_PAYMENT_DETAILS_LENGTH_L,
1008 PMT_GROUP_BY_PAYMENT_DETAILS = X_PMT_GROUP_BY_PAYMENT_DETAILS,
1009 PMT_GROUP_BY_BANK_CHARGE_BEAR = X_PMT_GROUP_BY_BANK_CHARGE_BEA,
1010 INST_SORT_ORDER_2 = X_INST_SORT_ORDER_2,
1011 INST_SORT_OPTION_3 = X_INST_SORT_OPTION_3,
1012 INST_SORT_ORDER_3 = X_INST_SORT_ORDER_3,
1013 REMIT_DOCUMENT_COUNT_LIMIT = X_REMIT_DOCUMENT_COUNT_LIMIT,
1014 REMIT_ALLOW_MULTIPLE_COPY_FLAG = X_REMIT_ALLOW_MULTIPLE_COPY_FL,
1015 INST_GROUP_BY_PAYMENT_DATE = X_INST_GROUP_BY_PAYMENT_DATE,
1016 INST_GROUP_BY_PAYMENT_CURRENCY = X_INST_GROUP_BY_PAYMENT_CURREN,
1017 INST_GROUP_BY_INT_BANK_ACCT = X_INST_GROUP_BY_INT_BANK_ACCT,
1018 INST_GROUP_BY_MAX_PAYMENTS = X_INST_GROUP_BY_MAX_PAYMENTS,
1019 INST_GROUP_BY_PAY_SERVICE_REQ = X_INST_GROUP_BY_PAY_SERVICE_RE,
1020 DCL_ONLY_FOREIGN_CURR_PMT_FLAG = X_DCL_ONLY_FOREIGN_CURR_PMT_FL,
1021 DECLARATION_REPORT_FORMAT_CODE = X_DECLARATION_REPORT_FORMAT_CO,
1022 DECLARATION_CURR_FX_RATE_TYPE = X_DECLARATION_CURR_FX_RATE_TYP,
1023 DECLARATION_CURRENCY_CODE = X_DECLARATION_CURRENCY_CODE,
1024 DECLARATION_THRESHOLD_AMOUNT = X_DECLARATION_THRESHOLD_AMOUNT,
1025 BANK_INSTRUCTION1_CODE = X_BANK_INSTRUCTION1_CODE,
1026 BANK_INSTRUCTION2_CODE = X_BANK_INSTRUCTION2_CODE,
1027 BANK_INSTRUCTION_DETAILS = X_BANK_INSTRUCTION_DETAILS,
1028 PAYMENT_TEXT_MESSAGE1 = X_PAYMENT_TEXT_MESSAGE1,
1029 PAYMENT_TEXT_MESSAGE2 = X_PAYMENT_TEXT_MESSAGE2,
1030 ACK_TRANSMIT_PROTOCOL_CODE = X_ACK_TRANSMIT_PROTOCOL_CODE,
1031 ACK_SECURITY_PROTOCOL_CODE = X_ACK_SECURITY_PROTOCOL_CODE,
1032 SEND_TO_FILE_FLAG = X_SEND_TO_FILE_FLAG,
1033 PI_REGISTER_FORMAT = X_PI_REGISTER_FORMAT,
1034 BEP_ACCOUNT_ID = X_BEP_ACCOUNT_ID,
1035 APPLICABLE_PMT_METHOD = X_APPLICABLE_PMT_METHOD,
1036 APPLICABLE_CURRENCY = X_APPLICABLE_CURRENCY,
1037 APPLICABLE_PAYER_ORG = X_APPLICABLE_PAYER_ORG,
1038 APPLICABLE_INT_BANK_ACCOUNT = X_APPLICABLE_INT_BANK_ACCOUNT,
1039 BEP_SUFFIX = X_BEP_SUFFIX,
1040 SECURITY_PROTOCOL_CODE = X_SECURITY_PROTOCOL_CODE,
1041 TRANSMIT_PROTOCOL_CODE = X_TRANSMIT_PROTOCOL_CODE,
1042 PAYMENT_FORMAT_CODE = X_PAYMENT_FORMAT_CODE,
1043 POSITIVE_PAY_FORMAT_CODE = X_POSITIVE_PAY_FORMAT_CODE,
1044 PAY_FILE_LETTER_FORMAT_CODE = X_PAY_FILE_LETTER_FORMAT_CODE,
1045 PRINT_INSTRUCTION_IMMED_FLAG = X_PRINT_INSTRUCTION_IMMED_FLAG,
1046 PERIODIC_SEQUENCE_NAME_1 = X_PERIODIC_SEQUENCE_NAME_1,
1047 PERIODIC_SEQUENCE_NAME_2 = X_PERIODIC_SEQUENCE_NAME_2,
1048 PERIODIC_SEQUENCE_NAME_3 = X_PERIODIC_SEQUENCE_NAME_3,
1049 PROCESSING_TYPE = X_PROCESSING_TYPE,
1050 MARK_COMPLETE_EVENT = X_MARK_COMPLETE_EVENT,
1051 MANUAL_MARK_COMPLETE_FLAG = X_MANUAL_MARK_COMPLETE_FLAG,
1052 DECLARATION_OPTION = X_DECLARATION_OPTION,
1053 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
1054 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
1055 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
1056 ELECTRONIC_PROCESSING_CHANNEL = X_ELECTRONIC_PROCESSING_CHNNL,
1057 LOGICAL_GROUPING_MODE = X_LOGICAL_GROUPING_MODE,
1058 BATCH_BOOKING_FLAG = X_BATCH_BOOKING_FLAG,
1059 INST_GROUP_BY_PAYMENT_METHOD = X_INST_GROUP_BY_PAYMENT_METHOD,
1060 INACTIVE_DATE = X_INACTIVE_DATE
1061
1062 where SYSTEM_PROFILE_CODE = X_SYSTEM_PROFILE_CODE;
1063
1064 if (sql%notfound) then
1065 raise no_data_found;
1066 end if;
1067
1068 -- Uncomment the below code for debugging..
1069 -- kp_msg('The updation of iby_upg_ppp_b tables has been done successfully');
1070 -- CODE TO UPDATE BASE TABLES
1071
1072 /* Added IF Conditions for fixing Bug# 8691900 */
1073 IF(l_sys_profile_code_tab_cnt > 0) THEN
1074 FOR i IN l_sys_profile_code_tab.FIRST .. l_sys_profile_code_tab.LAST LOOP
1075
1076 --kp_msg('Inside the loop for i=' || i|| '; value of sys_prof_code =' ||l_sys_profile_code_tab(i));
1077
1078 update iby_sys_pmt_profiles_b set
1079 payment_format_code = X_PAYMENT_FORMAT_CODE,
1080 print_instruction_immed_flag = X_PRINT_INSTRUCTION_IMMED_FLAG,
1081 processing_type = X_PROCESSING_TYPE,
1082 mark_complete_event = X_MARK_COMPLETE_EVENT,
1083 manual_mark_complete_flag = X_MANUAL_MARK_COMPLETE_FLAG,
1084 positive_pay_delivery_flag= X_POSITIVE_PAY_DELIVERY_FLAG,
1085 object_version_number= X_OBJECT_VERSION_NUMBER,
1086 transmit_instr_immed_flag= X_TRANSMIT_INSTR_IMMED_FLAG,
1087 automatic_pi_reg_submit = X_AUTOMATIC_PI_REG_SUBMIT,
1088 send_to_file_flag = X_SEND_TO_FILE_FLAG,
1089 dcl_only_foreign_curr_pmt_flag = X_DCL_ONLY_FOREIGN_CURR_PMT_FL,
1090 security_protocol_code = X_SECURITY_PROTOCOL_CODE,
1091 transmit_protocol_code = X_TRANSMIT_PROTOCOL_CODE,
1092 positive_pay_format_code = X_POSITIVE_PAY_FORMAT_CODE,
1093 pay_file_letter_format_code = X_PAY_FILE_LETTER_FORMAT_CODE,
1094 periodic_sequence_name_1 = X_PERIODIC_SEQUENCE_NAME_1,
1095 periodic_sequence_name_2= X_PERIODIC_SEQUENCE_NAME_2,
1096 periodic_sequence_name_3= X_PERIODIC_SEQUENCE_NAME_3,
1097 declaration_option= X_DECLARATION_OPTION,
1098 declaration_report_format_code =X_DECLARATION_REPORT_FORMAT_CO,
1099 declaration_curr_fx_rate_type = X_DECLARATION_CURR_FX_RATE_TYP,
1100 declaration_currency_code = X_DECLARATION_CURRENCY_CODE,
1101 declaration_threshold_amount = X_DECLARATION_THRESHOLD_AMOUNT,
1102 bank_instruction1_code= X_BANK_INSTRUCTION1_CODE,
1103 bank_instruction2_code= X_BANK_INSTRUCTION2_CODE,
1104 bank_instruction_details = X_BANK_INSTRUCTION_DETAILS,
1105 payment_text_message1= X_PAYMENT_TEXT_MESSAGE1,
1106 payment_text_message2=X_PAYMENT_TEXT_MESSAGE2,
1107 ack_transmit_protocol_code = X_ACK_TRANSMIT_PROTOCOL_CODE,
1108 ack_security_protocol_code= X_ACK_SECURITY_PROTOCOL_CODE,
1109 pi_register_format= X_PI_REGISTER_FORMAT,
1110 electronic_processing_channel= X_ELECTRONIC_PROCESSING_CHNNL,
1111 logical_grouping_mode=X_LOGICAL_GROUPING_MODE,
1112 BATCH_BOOKING_FLAG = X_BATCH_BOOKING_FLAG,
1113 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
1114 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
1115 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
1116 INACTIVE_DATE = X_INACTIVE_DATE
1117 where SYSTEM_PROFILE_CODE = l_sys_profile_code_tab(i);
1118
1119
1120 /* Commented the code below for fixing Bug# 8691900 */
1121 /*if (sql%notfound) then
1122 raise no_data_found;
1123 end if;*/
1124
1125 -- kp_msg('The updation of iby_sys_pmt_profiles_b tables has been done successfully');
1126
1127 update IBY_PMT_CREATION_RULES set
1128 group_by_delivery_channel = X_PMT_GROUP_BY_DELIVERY_CHANNE,
1129 group_by_remittance_message = X_PMT_GROUP_BY_REMITTANCE_MES,
1130 group_by_max_documents_flag = X_PMT_GROUP_BY_MAX_DOCUMENTS,
1131 group_by_unique_remit_id_flag = X_PMT_GROUP_BY_UNIQUE_REMIT_ID,
1132 group_by_settle_priority_flag = X_PMT_GROUP_BY_SETTLE_PRIORITY,
1133 group_by_payment_reason = X_PMT_GROUP_BY_PAYMENT_REASON,
1134 group_by_due_date_flag = X_PMT_GROUP_BY_DUE_DATE_FLAG,
1135 max_documents_per_payment = X_PMT_MAX_DOCUMENTS_PER_PAYMEN,
1136 payment_details_length_limit = X_PMT_PAYMENT_DETAILS_LENGTH_L,
1137 payment_details_formula = X_PMT_PAYMENT_DETAILS_FORMULA,
1138 group_by_payment_details_flag = X_PMT_GROUP_BY_PAYMENT_DETAILS,
1139 group_by_bank_charge_bearer = X_PMT_GROUP_BY_BANK_CHARGE_BEA,
1140 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
1141 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
1142 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
1143 where system_profile_code = l_sys_profile_code_tab(i);
1144
1145 /* Commented the code below for fixing Bug# 8691900 */
1146 /*if (sql%notfound) then
1147 raise no_data_found;
1148 end if;*/
1149
1150 update iby_acct_pmt_profiles_b set
1151 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
1152 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
1153 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
1154 BEP_ACCOUNT_ID = X_BEP_ACCOUNT_ID
1155 where SYSTEM_PROFILE_CODE = l_sys_profile_code_tab(i) ;
1156
1157 /* Commented the code below for fixing Bug# 8691900 */
1158 /*if (sql%notfound) then
1159 raise no_data_found;
1160 end if;*/
1161
1162 update iby_instr_creation_rules set
1163 group_by_payment_date= X_INST_GROUP_BY_PAYMENT_DATE,
1164 group_by_payment_currency = X_INST_GROUP_BY_PAYMENT_CURREN,
1165 group_by_internal_bank_account = X_INST_GROUP_BY_INT_BANK_ACCT,
1166 group_by_max_payments_flag = X_INST_GROUP_BY_MAX_PAYMENTS,
1167 group_by_pay_service_request = X_INST_GROUP_BY_PAY_SERVICE_RE,
1168 group_by_legal_entity = X_INST_GROUP_BY_LEGAL_ENTITY,
1169 group_by_bill_payable = X_INST_GROUP_BY_BILL_PAYABLE,
1170 group_by_organization = X_INST_GROUP_BY_ORGANIZATION,
1171 group_by_max_instruction_flag = X_INST_GROUP_BY_MAX_INSTRUCTIO,
1172 group_by_payment_function = X_INST_GROUP_BY_PAYMENT_FUNCTI,
1173 group_by_payment_reason = X_INST_GROUP_BY_PAYMENT_REASON,
1174 group_by_rfc= X_INST_GROUP_BY_RFC,
1175 max_payments_per_instruction = X_INST_MAX_PAYMENTS_PER_INSTR,
1176 max_amount_per_instr_value = X_INST_MAX_AMOUNT_PER_INSTR_VA,
1177 max_amount_per_instr_curr_code= X_INST_MAX_AMOUNT_PER_INSTR_CU,
1178 max_amount_fx_rate_type= X_INST_MAX_AMOUNT_FX_RATE_TYPE,
1179 SORT_OPTION_1 = X_INST_SORT_OPTION_1,
1180 SORT_ORDER_1 = X_INST_SORT_ORDER_1,
1181 SORT_OPTION_2 = X_INST_SORT_OPTION_2,
1182 SORT_ORDER_2 = X_INST_SORT_ORDER_2,
1183 SORT_OPTION_3 = X_INST_SORT_OPTION_3,
1184 SORT_ORDER_3 = X_INST_SORT_ORDER_3,
1185 group_by_payment_method = X_INST_GROUP_BY_PAYMENT_METHOD,
1186 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
1187 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
1188 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
1189 where SYSTEM_PROFILE_CODE = l_sys_profile_code_tab(i) ;
1190
1191 /* Commented the code below for fixing Bug# 8691900 */
1192 /*if (sql%notfound) then
1193 raise no_data_found;
1194 end if;*/
1195
1196 -- kp_msg('The updation of iby_instr_creation_rules tables has been done successfully');
1197
1198 update iby_remit_advice_setup set
1199 document_count_limit = X_REMIT_DOCUMENT_COUNT_LIMIT,
1200 allow_multiple_copy_flag = X_REMIT_ALLOW_MULTIPLE_COPY_FL,
1201 remittance_advice_format_code = X_REMIT_REMITTANCE_ADVICE_FORM,
1202 sra_override_payee_flag = X_REMIT_SRA_OVERRIDE_PAYEE_FLA,
1203 REMIT_ADVICE_DELIVERY_METHOD = X_REMIT_ADVICE_DELIVERY_METHOD,
1204 automatic_sra_submit_flag = X_REMIT_AUTOMATIC_SRA_SUBMIT,
1205 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
1206 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
1207 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
1208 where system_profile_code = l_sys_profile_code_tab(i);
1209
1210 /* Commented the code below for fixing Bug# 8691900 */
1211 /*if (sql%notfound) then
1212 raise no_data_found;
1213 end if;*/
1214
1215 END LOOP;
1216 END IF;
1217
1218 update IBY_UPG_PPP_TL set
1219 SYSTEM_PROFILE_NAME = X_SYSTEM_PROFILE_NAME,
1220 SYSTEM_PROFILE_DESCRIPTION = X_SYSTEM_PROFILE_DESCRIPTION,
1221 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
1222 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
1223 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
1224 SOURCE_LANG = userenv('LANG')
1225 where SYSTEM_PROFILE_CODE = X_SYSTEM_PROFILE_CODE
1226 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
1227
1228 if (sql%notfound) then
1229 raise no_data_found;
1230 end if;
1231
1232 update IBY_UPG_PPP_TL set
1233 SYSTEM_PROFILE_NAME = X_SYSTEM_PROFILE_NAME,
1234 SYSTEM_PROFILE_DESCRIPTION = X_SYSTEM_PROFILE_DESCRIPTION,
1235 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
1236 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
1237 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
1238 SOURCE_LANG = userenv('LANG')
1239 where SYSTEM_PROFILE_CODE = X_SYSTEM_PROFILE_CODE
1240 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
1241
1242 if (sql%notfound) then
1243 raise no_data_found;
1244 end if;
1245
1246 update IBY_SYS_PMT_PROFILES_TL set
1247 SYSTEM_PROFILE_NAME = X_SYSTEM_PROFILE_NAME,
1248 SYSTEM_PROFILE_DESCRIPTION = X_SYSTEM_PROFILE_DESCRIPTION,
1249 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
1250 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
1251 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
1252 SOURCE_LANG = userenv('LANG')
1253 where SYSTEM_PROFILE_CODE = X_SYSTEM_PROFILE_CODE
1254 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
1255
1256 end UPDATE_ROW;
1257
1258 procedure DELETE_ROW (
1259 X_SYSTEM_PROFILE_CODE in VARCHAR2
1260 ) is
1261 begin
1262 delete from IBY_UPG_PPP_TL
1263 where SYSTEM_PROFILE_CODE = X_SYSTEM_PROFILE_CODE;
1264
1265 /* Commented the code below for fixing Bug# 8691900 */
1266 /*if (sql%notfound) then
1267 raise no_data_found;
1268 end if;*/
1269
1270 delete from IBY_UPG_PPP_B
1271 where SYSTEM_PROFILE_CODE = X_SYSTEM_PROFILE_CODE;
1272
1273 /* Commented the code below for fixing Bug# 8691900 */
1274 /*if (sql%notfound) then
1275 raise no_data_found;
1276 end if;*/
1277
1278 end DELETE_ROW;
1279
1280 procedure ADD_LANGUAGE
1281 is
1282 begin
1283 delete from IBY_UPG_PPP_TL T
1284 where not exists
1285 (select NULL
1286 from IBY_UPG_PPP_B B
1287 where B.SYSTEM_PROFILE_CODE = T.SYSTEM_PROFILE_CODE
1288 );
1289
1290 update IBY_UPG_PPP_TL T set (
1291 SYSTEM_PROFILE_NAME,
1292 SYSTEM_PROFILE_DESCRIPTION
1293 ) = (select
1294 B.SYSTEM_PROFILE_NAME,
1295 B.SYSTEM_PROFILE_DESCRIPTION
1296 from IBY_UPG_PPP_TL B
1297 where B.SYSTEM_PROFILE_CODE = T.SYSTEM_PROFILE_CODE
1298 and B.LANGUAGE = T.SOURCE_LANG)
1299 where (
1300 T.SYSTEM_PROFILE_CODE,
1301 T.LANGUAGE
1302 ) in (select
1303 SUBT.SYSTEM_PROFILE_CODE,
1304 SUBT.LANGUAGE
1305 from IBY_UPG_PPP_TL SUBB, IBY_UPG_PPP_TL SUBT
1306 where SUBB.SYSTEM_PROFILE_CODE = SUBT.SYSTEM_PROFILE_CODE
1307 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
1308 and (SUBB.SYSTEM_PROFILE_NAME <> SUBT.SYSTEM_PROFILE_NAME
1309 or SUBB.SYSTEM_PROFILE_DESCRIPTION <> SUBT.SYSTEM_PROFILE_DESCRIPTION
1310 or (SUBB.SYSTEM_PROFILE_DESCRIPTION is null and SUBT.SYSTEM_PROFILE_DESCRIPTION is not null)
1311 or (SUBB.SYSTEM_PROFILE_DESCRIPTION is not null and SUBT.SYSTEM_PROFILE_DESCRIPTION is null)
1312 ));
1313
1314 insert into IBY_UPG_PPP_TL (
1315 SYSTEM_PROFILE_CODE,
1316 SYSTEM_PROFILE_NAME,
1317 SYSTEM_PROFILE_DESCRIPTION,
1318 CREATED_BY,
1319 CREATION_DATE,
1320 LAST_UPDATED_BY,
1321 LAST_UPDATE_DATE,
1322 LAST_UPDATE_LOGIN,
1323 OBJECT_VERSION_NUMBER,
1324 LANGUAGE,
1325 SOURCE_LANG
1326 ) select /*+ ORDERED */
1327 B.SYSTEM_PROFILE_CODE,
1328 B.SYSTEM_PROFILE_NAME,
1329 B.SYSTEM_PROFILE_DESCRIPTION,
1330 B.CREATED_BY,
1331 B.CREATION_DATE,
1332 B.LAST_UPDATED_BY,
1333 B.LAST_UPDATE_DATE,
1334 B.LAST_UPDATE_LOGIN,
1335 B.OBJECT_VERSION_NUMBER,
1336 L.LANGUAGE_CODE,
1337 B.SOURCE_LANG
1338 from IBY_UPG_PPP_TL B, FND_LANGUAGES L
1339 where L.INSTALLED_FLAG in ('I', 'B')
1340 and B.LANGUAGE = userenv('LANG')
1341 and not exists
1342 (select NULL
1343 from IBY_UPG_PPP_TL T
1344 where T.SYSTEM_PROFILE_CODE = B.SYSTEM_PROFILE_CODE
1345 and T.LANGUAGE = L.LANGUAGE_CODE);
1346 end ADD_LANGUAGE;
1347
1348 procedure LOAD_ROW (
1349 X_SYSTEM_PROFILE_CODE in VARCHAR2,
1350 X_BEP_SUFFIX in VARCHAR2,
1351 X_SECURITY_PROTOCOL_CODE in VARCHAR2,
1352 X_TRANSMIT_PROTOCOL_CODE in VARCHAR2,
1353 X_PAYMENT_FORMAT_CODE in VARCHAR2,
1354 X_POSITIVE_PAY_FORMAT_CODE in VARCHAR2,
1355 X_PAY_FILE_LETTER_FORMAT_CODE in VARCHAR2,
1356 X_PRINT_INSTRUCTION_IMMED_FLAG in VARCHAR2,
1357 X_PERIODIC_SEQUENCE_NAME_1 in VARCHAR2,
1358 X_PERIODIC_SEQUENCE_NAME_2 in VARCHAR2,
1359 X_PERIODIC_SEQUENCE_NAME_3 in VARCHAR2,
1360 X_PROCESSING_TYPE in VARCHAR2,
1361 X_MARK_COMPLETE_EVENT in VARCHAR2,
1362 X_MANUAL_MARK_COMPLETE_FLAG in VARCHAR2,
1363 X_POSITIVE_PAY_DELIVERY_FLAG in VARCHAR2,
1364 X_TRANSMIT_INSTR_IMMED_FLAG in VARCHAR2,
1365 X_AUTOMATIC_PI_REG_SUBMIT in VARCHAR2,
1366 X_DECLARATION_OPTION in VARCHAR2,
1367 X_DCL_ONLY_FOREIGN_CURR_PMT_FL in VARCHAR2,
1368 X_DECLARATION_REPORT_FORMAT_CO in VARCHAR2,
1369 X_DECLARATION_CURR_FX_RATE_TYP in VARCHAR2,
1370 X_DECLARATION_CURRENCY_CODE in VARCHAR2,
1371 X_DECLARATION_THRESHOLD_AMOUNT in NUMBER,
1372 X_BANK_INSTRUCTION1_CODE in VARCHAR2,
1373 X_BANK_INSTRUCTION2_CODE in VARCHAR2,
1374 X_BANK_INSTRUCTION_DETAILS in VARCHAR2,
1375 X_PAYMENT_TEXT_MESSAGE1 in VARCHAR2,
1376 X_PAYMENT_TEXT_MESSAGE2 in VARCHAR2,
1377 X_ACK_TRANSMIT_PROTOCOL_CODE in VARCHAR2,
1378 X_ACK_SECURITY_PROTOCOL_CODE in VARCHAR2,
1379 X_SEND_TO_FILE_FLAG in VARCHAR2,
1380 X_PI_REGISTER_FORMAT in VARCHAR2,
1381 X_BEP_ACCOUNT_ID in NUMBER,
1382 X_APPLICABLE_PMT_METHOD in VARCHAR2,
1383 X_APPLICABLE_CURRENCY in VARCHAR2,
1384 X_APPLICABLE_PAYER_ORG in VARCHAR2,
1385 X_APPLICABLE_INT_BANK_ACCOUNT in VARCHAR2,
1386 X_PMT_GROUP_BY_PAYMENT_DETAILS in VARCHAR2,
1387 X_PMT_GROUP_BY_BANK_CHARGE_BEA in VARCHAR2,
1388 X_PMT_GROUP_BY_DELIVERY_CHANNE in VARCHAR2,
1389 X_PMT_GROUP_BY_REMITTANCE_MES in VARCHAR2,
1390 X_PMT_GROUP_BY_MAX_DOCUMENTS in VARCHAR2,
1391 X_PMT_GROUP_BY_UNIQUE_REMIT_ID in VARCHAR2,
1392 X_PMT_GROUP_BY_SETTLE_PRIORITY in VARCHAR2,
1393 X_PMT_GROUP_BY_PAYMENT_REASON in VARCHAR2,
1394 X_PMT_GROUP_BY_DUE_DATE_FLAG in VARCHAR2,
1395 X_PMT_MAX_DOCUMENTS_PER_PAYMEN in NUMBER,
1396 X_PMT_PAYMENT_DETAILS_LENGTH_L in NUMBER,
1397 X_PMT_PAYMENT_DETAILS_FORMULA in VARCHAR2,
1398 X_INST_GROUP_BY_PAYMENT_DATE in VARCHAR2,
1399 X_INST_GROUP_BY_PAYMENT_CURREN in VARCHAR2,
1400 X_INST_GROUP_BY_INT_BANK_ACCT in VARCHAR2,
1401 X_INST_GROUP_BY_MAX_PAYMENTS in VARCHAR2,
1402 X_INST_GROUP_BY_PAY_SERVICE_RE in VARCHAR2,
1403 X_INST_GROUP_BY_LEGAL_ENTITY in VARCHAR2,
1404 X_INST_GROUP_BY_BILL_PAYABLE in VARCHAR2,
1405 X_INST_GROUP_BY_MAX_INSTRUCTIO in VARCHAR2,
1406 X_INST_GROUP_BY_ORGANIZATION in VARCHAR2,
1407 X_INST_GROUP_BY_PAYMENT_FUNCTI in VARCHAR2,
1408 X_INST_GROUP_BY_PAYMENT_REASON in VARCHAR2,
1409 X_INST_GROUP_BY_RFC in VARCHAR2,
1410 X_INST_MAX_PAYMENTS_PER_INSTR in NUMBER,
1411 X_INST_MAX_AMOUNT_PER_INSTR_VA in NUMBER,
1412 X_INST_MAX_AMOUNT_PER_INSTR_CU in VARCHAR2,
1413 X_INST_MAX_AMOUNT_FX_RATE_TYPE in VARCHAR2,
1414 X_INST_SORT_OPTION_1 in VARCHAR2,
1415 X_INST_SORT_ORDER_1 in VARCHAR2,
1416 X_INST_SORT_OPTION_2 in VARCHAR2,
1417 X_INST_SORT_ORDER_2 in VARCHAR2,
1418 X_INST_SORT_OPTION_3 in VARCHAR2,
1419 X_INST_SORT_ORDER_3 in VARCHAR2,
1420 X_REMIT_DOCUMENT_COUNT_LIMIT in NUMBER,
1421 X_REMIT_ALLOW_MULTIPLE_COPY_FL in VARCHAR2,
1422 X_REMIT_PMT_DETAILS_LENGTH_LIM in NUMBER,
1423 X_REMIT_REMITTANCE_ADVICE_FORM in VARCHAR2,
1424 X_REMIT_SRA_OVERRIDE_PAYEE_FLA in VARCHAR2,
1425 X_REMIT_ADVICE_DELIVERY_METHOD in VARCHAR2,
1426 X_REMIT_AUTOMATIC_SRA_SUBMIT in VARCHAR2,
1427 X_SYSTEM_PROFILE_NAME in VARCHAR2,
1428 X_SYSTEM_PROFILE_DESCRIPTION in VARCHAR2,
1429 X_CREATION_DATE in DATE,
1430 X_CREATED_BY in NUMBER,
1431 X_LAST_UPDATE_DATE in DATE,
1432 X_LAST_UPDATED_BY in NUMBER,
1433 X_LAST_UPDATE_LOGIN in NUMBER,
1434 X_OBJECT_VERSION_NUMBER in NUMBER,
1435 X_ELECTRONIC_PROCESSING_CHNNL in VARCHAR2,
1436 X_LOGICAL_GROUPING_MODE in VARCHAR2,
1437 X_BATCH_BOOKING_FLAG in VARCHAR2,
1438 X_INST_GROUP_BY_PAYMENT_METHOD in VARCHAR2,
1439 X_INACTIVE_DATE in DATE
1440 )
1441 is
1442 row_id VARCHAR2(200);
1443 begin
1444
1445 UPDATE_ROW (
1446 X_SYSTEM_PROFILE_CODE,
1447 X_REMIT_PMT_DETAILS_LENGTH_LIM,
1448 X_REMIT_REMITTANCE_ADVICE_FORM,
1449 X_REMIT_SRA_OVERRIDE_PAYEE_FLA,
1450 X_REMIT_ADVICE_DELIVERY_METHOD,
1451 X_REMIT_AUTOMATIC_SRA_SUBMIT,
1452 X_OBJECT_VERSION_NUMBER,
1453 X_PMT_PAYMENT_DETAILS_FORMULA,
1454 X_INST_GROUP_BY_LEGAL_ENTITY,
1455 X_INST_GROUP_BY_BILL_PAYABLE,
1456 X_INST_GROUP_BY_MAX_INSTRUCTIO,
1457 X_INST_GROUP_BY_ORGANIZATION,
1458 X_INST_GROUP_BY_PAYMENT_FUNCTI,
1459 X_INST_GROUP_BY_PAYMENT_REASON,
1460 X_INST_GROUP_BY_RFC,
1461 X_INST_MAX_PAYMENTS_PER_INSTR,
1462 X_INST_MAX_AMOUNT_PER_INSTR_VA,
1463 X_INST_MAX_AMOUNT_PER_INSTR_CU,
1464 X_INST_MAX_AMOUNT_FX_RATE_TYPE,
1465 X_INST_SORT_OPTION_1,
1466 X_INST_SORT_ORDER_1,
1467 X_INST_SORT_OPTION_2,
1468 X_POSITIVE_PAY_DELIVERY_FLAG,
1469 X_TRANSMIT_INSTR_IMMED_FLAG,
1470 X_AUTOMATIC_PI_REG_SUBMIT,
1471 X_PMT_GROUP_BY_DELIVERY_CHANNE,
1472 X_PMT_GROUP_BY_REMITTANCE_MES,
1473 X_PMT_GROUP_BY_MAX_DOCUMENTS,
1474 X_PMT_GROUP_BY_UNIQUE_REMIT_ID,
1475 X_PMT_GROUP_BY_SETTLE_PRIORITY,
1476 X_PMT_GROUP_BY_PAYMENT_REASON,
1477 X_PMT_GROUP_BY_DUE_DATE_FLAG,
1478 X_PMT_MAX_DOCUMENTS_PER_PAYMEN,
1479 X_PMT_PAYMENT_DETAILS_LENGTH_L,
1480 X_PMT_GROUP_BY_PAYMENT_DETAILS,
1481 X_PMT_GROUP_BY_BANK_CHARGE_BEA,
1482 X_INST_SORT_ORDER_2,
1483 X_INST_SORT_OPTION_3,
1484 X_INST_SORT_ORDER_3,
1485 X_REMIT_DOCUMENT_COUNT_LIMIT,
1486 X_REMIT_ALLOW_MULTIPLE_COPY_FL,
1487 X_INST_GROUP_BY_PAYMENT_DATE,
1488 X_INST_GROUP_BY_PAYMENT_CURREN,
1489 X_INST_GROUP_BY_INT_BANK_ACCT,
1490 X_INST_GROUP_BY_MAX_PAYMENTS,
1491 X_INST_GROUP_BY_PAY_SERVICE_RE,
1492 X_DCL_ONLY_FOREIGN_CURR_PMT_FL,
1493 X_DECLARATION_REPORT_FORMAT_CO,
1494 X_DECLARATION_CURR_FX_RATE_TYP,
1495 X_DECLARATION_CURRENCY_CODE,
1496 X_DECLARATION_THRESHOLD_AMOUNT,
1497 X_BANK_INSTRUCTION1_CODE,
1498 X_BANK_INSTRUCTION2_CODE,
1499 X_BANK_INSTRUCTION_DETAILS,
1500 X_PAYMENT_TEXT_MESSAGE1,
1501 X_PAYMENT_TEXT_MESSAGE2,
1502 X_ACK_TRANSMIT_PROTOCOL_CODE,
1503 X_ACK_SECURITY_PROTOCOL_CODE,
1504 X_SEND_TO_FILE_FLAG,
1505 X_PI_REGISTER_FORMAT,
1506 X_BEP_ACCOUNT_ID,
1507 X_APPLICABLE_PMT_METHOD,
1508 X_APPLICABLE_CURRENCY,
1509 X_APPLICABLE_PAYER_ORG,
1510 X_APPLICABLE_INT_BANK_ACCOUNT,
1511 X_BEP_SUFFIX,
1512 X_SECURITY_PROTOCOL_CODE,
1513 X_TRANSMIT_PROTOCOL_CODE,
1514 X_PAYMENT_FORMAT_CODE,
1515 X_POSITIVE_PAY_FORMAT_CODE,
1516 X_PAY_FILE_LETTER_FORMAT_CODE,
1517 X_PRINT_INSTRUCTION_IMMED_FLAG,
1518 X_PERIODIC_SEQUENCE_NAME_1,
1519 X_PERIODIC_SEQUENCE_NAME_2,
1520 X_PERIODIC_SEQUENCE_NAME_3,
1521 X_PROCESSING_TYPE,
1522 X_MARK_COMPLETE_EVENT,
1523 X_MANUAL_MARK_COMPLETE_FLAG,
1524 X_DECLARATION_OPTION,
1525 X_SYSTEM_PROFILE_NAME,
1526 X_SYSTEM_PROFILE_DESCRIPTION,
1527 X_LAST_UPDATE_DATE,
1528 X_LAST_UPDATED_BY,
1529 X_LAST_UPDATE_LOGIN,
1530 X_ELECTRONIC_PROCESSING_CHNNL,
1531 X_LOGICAL_GROUPING_MODE,
1532 X_BATCH_BOOKING_FLAG,
1533 X_INST_GROUP_BY_PAYMENT_METHOD,
1534 X_INACTIVE_DATE);
1535
1536 exception
1537 when no_data_found then
1538 INSERT_ROW (
1539 row_id,
1540 X_SYSTEM_PROFILE_CODE,
1541 X_REMIT_PMT_DETAILS_LENGTH_LIM,
1542 X_REMIT_REMITTANCE_ADVICE_FORM,
1543 X_REMIT_SRA_OVERRIDE_PAYEE_FLA,
1544 X_REMIT_ADVICE_DELIVERY_METHOD,
1545 X_REMIT_AUTOMATIC_SRA_SUBMIT,
1546 X_OBJECT_VERSION_NUMBER,
1547 X_PMT_PAYMENT_DETAILS_FORMULA,
1548 X_INST_GROUP_BY_LEGAL_ENTITY,
1549 X_INST_GROUP_BY_BILL_PAYABLE,
1550 X_INST_GROUP_BY_MAX_INSTRUCTIO,
1551 X_INST_GROUP_BY_ORGANIZATION,
1552 X_INST_GROUP_BY_PAYMENT_FUNCTI,
1553 X_INST_GROUP_BY_PAYMENT_REASON,
1554 X_INST_GROUP_BY_RFC,
1555 X_INST_MAX_PAYMENTS_PER_INSTR,
1556 X_INST_MAX_AMOUNT_PER_INSTR_VA,
1557 X_INST_MAX_AMOUNT_PER_INSTR_CU,
1558 X_INST_MAX_AMOUNT_FX_RATE_TYPE,
1559 X_INST_SORT_OPTION_1,
1560 X_INST_SORT_ORDER_1,
1561 X_INST_SORT_OPTION_2,
1562 X_POSITIVE_PAY_DELIVERY_FLAG,
1563 X_TRANSMIT_INSTR_IMMED_FLAG,
1564 X_AUTOMATIC_PI_REG_SUBMIT,
1565 X_PMT_GROUP_BY_DELIVERY_CHANNE,
1566 X_PMT_GROUP_BY_REMITTANCE_MES,
1567 X_PMT_GROUP_BY_MAX_DOCUMENTS,
1568 X_PMT_GROUP_BY_UNIQUE_REMIT_ID,
1569 X_PMT_GROUP_BY_SETTLE_PRIORITY,
1570 X_PMT_GROUP_BY_PAYMENT_REASON,
1571 X_PMT_GROUP_BY_DUE_DATE_FLAG,
1572 X_PMT_MAX_DOCUMENTS_PER_PAYMEN,
1573 X_PMT_PAYMENT_DETAILS_LENGTH_L,
1574 X_PMT_GROUP_BY_PAYMENT_DETAILS,
1575 X_PMT_GROUP_BY_BANK_CHARGE_BEA,
1576 X_INST_SORT_ORDER_2,
1577 X_INST_SORT_OPTION_3,
1578 X_INST_SORT_ORDER_3,
1579 X_REMIT_DOCUMENT_COUNT_LIMIT,
1580 X_REMIT_ALLOW_MULTIPLE_COPY_FL,
1581 X_INST_GROUP_BY_PAYMENT_DATE,
1582 X_INST_GROUP_BY_PAYMENT_CURREN,
1583 X_INST_GROUP_BY_INT_BANK_ACCT,
1584 X_INST_GROUP_BY_MAX_PAYMENTS,
1585 X_INST_GROUP_BY_PAY_SERVICE_RE,
1586 X_DCL_ONLY_FOREIGN_CURR_PMT_FL,
1587 X_DECLARATION_REPORT_FORMAT_CO,
1588 X_DECLARATION_CURR_FX_RATE_TYP,
1589 X_DECLARATION_CURRENCY_CODE,
1590 X_DECLARATION_THRESHOLD_AMOUNT,
1591 X_BANK_INSTRUCTION1_CODE,
1592 X_BANK_INSTRUCTION2_CODE,
1593 X_BANK_INSTRUCTION_DETAILS,
1594 X_PAYMENT_TEXT_MESSAGE1,
1595 X_PAYMENT_TEXT_MESSAGE2,
1596 X_ACK_TRANSMIT_PROTOCOL_CODE,
1597 X_ACK_SECURITY_PROTOCOL_CODE,
1598 X_SEND_TO_FILE_FLAG,
1599 X_PI_REGISTER_FORMAT,
1600 X_BEP_ACCOUNT_ID,
1601 X_APPLICABLE_PMT_METHOD,
1602 X_APPLICABLE_CURRENCY,
1603 X_APPLICABLE_PAYER_ORG,
1604 X_APPLICABLE_INT_BANK_ACCOUNT,
1605 X_BEP_SUFFIX,
1606 X_SECURITY_PROTOCOL_CODE,
1607 X_TRANSMIT_PROTOCOL_CODE,
1608 X_PAYMENT_FORMAT_CODE,
1609 X_POSITIVE_PAY_FORMAT_CODE,
1610 X_PAY_FILE_LETTER_FORMAT_CODE,
1611 X_PRINT_INSTRUCTION_IMMED_FLAG,
1612 X_PERIODIC_SEQUENCE_NAME_1,
1613 X_PERIODIC_SEQUENCE_NAME_2,
1614 X_PERIODIC_SEQUENCE_NAME_3,
1615 X_PROCESSING_TYPE,
1616 X_MARK_COMPLETE_EVENT,
1617 X_MANUAL_MARK_COMPLETE_FLAG,
1618 X_DECLARATION_OPTION,
1619 X_SYSTEM_PROFILE_NAME,
1620 X_SYSTEM_PROFILE_DESCRIPTION,
1621 X_CREATION_DATE,
1622 X_CREATED_BY,
1623 X_LAST_UPDATE_DATE,
1624 X_LAST_UPDATED_BY,
1625 X_LAST_UPDATE_LOGIN,
1626 X_ELECTRONIC_PROCESSING_CHNNL,
1627 X_LOGICAL_GROUPING_MODE,
1628 X_BATCH_BOOKING_FLAG,
1629 X_INST_GROUP_BY_PAYMENT_METHOD,
1630 X_INACTIVE_DATE);
1631
1632 end LOAD_ROW;
1633
1634 procedure TRANSLATE_ROW (
1635 X_SYSTEM_PROFILE_CODE in VARCHAR2,
1636 X_SYSTEM_PROFILE_NAME in VARCHAR2,
1637 X_SYSTEM_PROFILE_DESCRIPTION in VARCHAR2,
1638 X_LAST_UPDATE_DATE in DATE,
1639 X_LAST_UPDATED_BY in NUMBER,
1640 X_LAST_UPDATE_LOGIN in NUMBER)
1641 is
1642 begin
1643 update IBY_UPG_PPP_TL set
1644 SYSTEM_PROFILE_NAME = X_SYSTEM_PROFILE_NAME,
1645 SYSTEM_PROFILE_DESCRIPTION = X_SYSTEM_PROFILE_DESCRIPTION,
1646 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
1647 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
1648 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
1649 SOURCE_LANG = userenv('LANG')
1650 where SYSTEM_PROFILE_CODE = X_SYSTEM_PROFILE_CODE
1651 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
1652
1653 if (sql%notfound) then
1654 raise no_data_found;
1655 end if;
1656
1657 end TRANSLATE_ROW;
1658
1659 PROCEDURE set_sys_user_profiles IS
1660 -- l_user_id NUMBER(15);
1661 -- l_login_id NUMBER(15);
1662 -- l_sysdate DATE;
1663 l_max_app NUMBER := 0;
1664 l_jenl_eft_dir_value fnd_profile_option_values.profile_option_value%TYPE;
1665 l_xxno_signet_file_value fnd_profile_option_values.profile_option_value%TYPE;
1666 l_ece_out_file_value fnd_profile_option_values.profile_option_value%TYPE;
1667 l_jenl_eft_reporting_limit NUMBER;
1668 l_jenl_pmt_separation fnd_profile_option_values.profile_option_value%TYPE;
1669
1670 l_remit_advice_format_code VARCHAR2(50) := 'IBY_PAY_REMIT_ADV';
1671
1672 BEGIN
1673 -- l_user_id := NVL(fnd_global.user_id, 1);
1674 -- l_login_id := NVL(fnd_global.login_id, 1);
1675 -- l_sysdate := sysdate;
1676
1677 ---------------------------------------------------------------------------
1678 -- Step 1: Get max value of applicable_pmt_prof_id for rerun cases --
1679 ---------------------------------------------------------------------------
1680 BEGIN
1681 SELECT NVL(MAX(applicable_pmt_prof_id),0)
1682 INTO l_max_app
1683 FROM iby_applicable_pmt_profs app
1684 WHERE applicable_pmt_prof_id < 10000;
1685 EXCEPTION
1686 WHEN no_data_found THEN
1687 l_max_app := 0;
1688 END;
1689
1690 ---------------------------------------------------------------------------
1691 -- Step 2: Get most frequent values for Outbound directory profile --
1692 -- options to be upgraded --
1693 ---------------------------------------------------------------------------
1694 BEGIN
1695 SELECT x.profile_option_value
1696 INTO l_jenl_eft_dir_value
1697 FROM ( SELECT o.profile_option_name, v.profile_option_value,
1698 row_number() OVER (partition by o.profile_option_name
1699 order by count(*) desc) order_rank
1700 FROM fnd_profile_options o,
1701 fnd_profile_option_values v
1702 WHERE o.profile_option_name = UPPER('JENL_EFT_DIR')
1703 AND v.application_id = o.application_id
1704 AND v.profile_option_id = o.profile_option_id
1705 GROUP BY o.profile_option_name, v.profile_option_value) x
1706 WHERE x.order_rank = 1;
1707 EXCEPTION
1708 WHEN no_data_found THEN NULL;
1709 END;
1710
1711 BEGIN
1712 SELECT x.profile_option_value
1713 INTO l_xxno_signet_file_value
1714 FROM ( SELECT o.profile_option_name, v.profile_option_value,
1715 row_number() OVER (partition by o.profile_option_name
1716 order by count(*) desc) order_rank
1717 FROM fnd_profile_options o,
1718 fnd_profile_option_values v
1719 WHERE o.profile_option_name = UPPER('XXNO_SIGNET_CONFIG_FILE')
1720 AND v.application_id = o.application_id
1721 AND v.profile_option_id = o.profile_option_id
1722 GROUP BY o.profile_option_name, v.profile_option_value) x
1723 WHERE x.order_rank = 1;
1724 EXCEPTION
1725 WHEN no_data_found THEN NULL;
1726 END;
1727
1728 BEGIN
1729 SELECT x.profile_option_value
1730 INTO l_ece_out_file_value
1731 FROM ( SELECT o.profile_option_name, v.profile_option_value,
1732 row_number() OVER (partition by o.profile_option_name
1733 order by count(*) desc) order_rank
1734 FROM fnd_profile_options o,
1735 fnd_profile_option_values v
1736 WHERE o.profile_option_name = UPPER('ECE_OUT_FILE_PATH')
1737 AND v.application_id = o.application_id
1738 AND v.profile_option_id = o.profile_option_id
1739 GROUP BY o.profile_option_name, v.profile_option_value) x
1740 WHERE x.order_rank = 1;
1741 EXCEPTION
1742 WHEN no_data_found THEN NULL;
1743 END;
1744
1745 ---------------------------------------------------------------------------
1746 -- Step 3: Get most frequent value for NL threshold amt --
1747 -- options to be upgraded --
1748 ---------------------------------------------------------------------------
1749 BEGIN
1750 SELECT TO_NUMBER(x.profile_option_value)
1751 INTO l_jenl_eft_reporting_limit
1752 FROM ( SELECT o.profile_option_name, v.profile_option_value,
1753 row_number() OVER (partition by o.profile_option_name
1754 order by count(*) desc) order_rank
1755 FROM fnd_profile_options o,
1756 fnd_profile_option_values v
1757 WHERE o.profile_option_name = UPPER('JENL_REPORTING_THRESHOLD')
1758 AND v.application_id = o.application_id
1759 AND v.profile_option_id = o.profile_option_id
1760 GROUP BY o.profile_option_name, v.profile_option_value) x
1761 WHERE x.order_rank = 1;
1762 EXCEPTION
1763 WHEN others THEN NULL;
1764 END;
1765
1766
1767 ---------------------------------------------------------------------------
1768 -- Step 4: Get most frequent value for NL payment separation --
1769 -- options to be upgraded --
1770 ---------------------------------------------------------------------------
1771 BEGIN
1772 SELECT x.profile_option_value
1773 INTO l_jenl_pmt_separation
1774 FROM ( SELECT o.profile_option_name, v.profile_option_value,
1775 row_number() OVER (partition by o.profile_option_name
1776 order by count(*) desc) order_rank
1777 FROM fnd_profile_options o,
1778 fnd_profile_option_values v
1779 WHERE o.profile_option_name = UPPER('JENL_PAYMENT_SEPARATION')
1780 AND v.application_id = o.application_id
1781 AND v.profile_option_id = o.profile_option_id
1782 GROUP BY o.profile_option_name, v.profile_option_value) x
1783 WHERE x.order_rank = 1;
1784 EXCEPTION
1785 WHEN others THEN NULL;
1786 END;
1787
1788 ---------------------------------------------------------------------------
1789 -- Step 5: Create PPPs. Both seeded and user entered --
1790 ---------------------------------------------------------------------------
1791 BEGIN
1792 INSERT ALL
1793 WHEN create_header = 1 THEN
1794 INTO iby_sys_pmt_profiles_b
1795 (
1796 system_profile_code,
1797 payment_format_code,
1798 print_instruction_immed_flag,
1799 processing_type,
1800 mark_complete_event,
1801 manual_mark_complete_flag,
1802 positive_pay_delivery_flag,
1803 created_by,
1804 creation_date,
1805 last_updated_by,
1806 last_update_date,
1807 last_update_login,
1808 object_version_number,
1809 transmit_instr_immed_flag,
1810 automatic_pi_reg_submit,
1811 send_to_file_flag,
1812 dcl_only_foreign_curr_pmt_flag,
1813 seeded_flag,
1814 attribute_category,
1815 attribute1,
1816 attribute2,
1817 attribute3,
1818 attribute4,
1819 attribute5,
1820 attribute6,
1821 attribute7,
1822 attribute8,
1823 attribute9,
1824 attribute10,
1825 attribute11,
1826 attribute12,
1827 attribute13,
1828 attribute14,
1829 attribute15,
1830 bepid,
1831 security_protocol_code,
1832 transmit_protocol_code,
1833 positive_pay_format_code,
1834 pay_file_letter_format_code,
1835 default_printer,
1836 default_payment_document_id,
1837 periodic_sequence_name_1,
1838 periodic_sequence_name_2,
1839 periodic_sequence_name_3,
1840 declaration_option,
1841 declaration_report_format_code,
1842 declaration_curr_fx_rate_type,
1843 declaration_currency_code,
1844 declaration_threshold_amount,
1845 bank_instruction1_code,
1846 bank_instruction2_code,
1847 bank_instruction_details,
1848 payment_text_message1,
1849 payment_text_message2,
1850 inactive_date,
1851 ack_transmit_protocol_code,
1852 ack_security_protocol_code,
1853 pi_register_format,
1854 outbound_pmt_file_directory,
1855 electronic_processing_channel,
1856 logical_grouping_mode,
1857 batch_booking_flag,
1858 inactive_date
1859 ) VALUES
1860 (
1861 check_format_id, -- system_profile_code
1862 payment_format_code,
1863 print_instruction_immed_flag,
1864 processing_type,
1865 mark_complete_event,
1866 manual_mark_complete_flag,
1867 positive_pay_delivery_flag,
1868 created_by, -- created_by
1869 creation_date, -- creation_date
1870 last_updated_by, -- last_updated_by
1871 last_update_date, -- last_update_date
1872 last_update_login, -- last_update_login
1873 object_version_number, -- object_version_number
1874 transmit_instr_immed_flag,
1875 automatic_pi_reg_submit,
1876 send_to_file_flag,
1877 dcl_only_foreign_curr_pmt_flag,
1878 seeded_flag,
1879 attribute_category,
1880 attribute1,
1881 attribute2,
1882 attribute3,
1883 attribute4,
1884 attribute5,
1885 attribute6,
1886 attribute7,
1887 attribute8,
1888 attribute9,
1889 attribute10,
1890 attribute11,
1891 attribute12,
1892 attribute13,
1893 attribute14,
1894 attribute15,
1895 bepid,
1896 security_protocol_code,
1897 transmit_protocol_code,
1898 positive_pay_format_code,
1899 pay_file_letter_format_code,
1900 NULL, -- default_printer
1901 payment_document_id, -- default_payment_document_id
1902 periodic_sequence_name_1,
1903 periodic_sequence_name_2,
1904 periodic_sequence_name_3,
1905 declaration_option,
1906 declaration_report_format_code,
1907 declaration_curr_fx_rate_type,
1908 declaration_currency_code,
1909 declaration_threshold_amount,
1910 bank_instruction1_code,
1911 bank_instruction2_code,
1912 bank_instruction_details,
1913 payment_text_message1,
1914 payment_text_message2,
1915 NULL, -- inactive_date
1916 ack_transmit_protocol_code,
1917 ack_security_protocol_code,
1918 pi_register_format,
1919 outbound_pmt_file_directory,
1920 electronic_processing_channel,
1921 logical_grouping_mode,
1922 batch_booking_flag,
1923 inactive_date
1924 )
1925 WHEN create_bepacct=1 THEN
1926 INTO iby_acct_pmt_profiles_b
1927 (
1928 payment_profile_id,
1929 system_profile_code,
1930 created_by,
1931 creation_date,
1932 last_updated_by,
1933 last_update_date,
1934 last_update_login,
1935 object_version_number,
1936 bep_account_id,
1937 security_configuration_id,
1938 transmit_configuration_id,
1939 reset_value_1,
1940 last_used_number_1,
1941 reset_request_1,
1942 reset_value_2,
1943 last_used_number_2,
1944 reset_request_2,
1945 reset_value_3,
1946 last_used_number_3,
1947 reset_request_3,
1948 inactive_date,
1949 ack_sec_config_id,
1950 ack_transmit_config_id
1951 ) VALUES
1952 (
1953 iby_acct_pmt_profiles_b_s.NEXTVAL,
1954 check_format_id, -- system_profile_code
1955 created_by, -- created_by
1956 creation_date, -- creation_date
1957 last_updated_by, -- last_updated_by
1958 last_update_date, -- last_update_date
1959 last_update_login, -- last_update_login
1960 object_version_number, -- object_version_number
1961 bep_account_id,
1962 NULL, -- security_configuration_id,
1963 NULL, -- transmit_configuration_id,
1964 NULL, -- reset_value_1
1965 NULL, -- last_used_number_1
1966 NULL, -- reset_request_1
1967 NULL, -- reset_value_2
1968 NULL, -- last_used_number_2
1969 NULL, -- reset_request_2
1970 NULL, -- reset_value_3
1971 NULL, -- last_used_number_3
1972 NULL, -- reset_request_3
1973 NULL, -- inactive_date
1974 NULL, -- ack_sec_config_id
1975 NULL -- ack_transmit_config_id
1976 )
1977 -- applicable pmt method
1978 WHEN create_header = 1 THEN
1979 INTO iby_applicable_pmt_profs
1980 (
1981 applicable_pmt_prof_id,
1982 system_profile_code,
1983 applicable_type_code,
1984 seeded_flag,
1985 applicable_value_from,
1986 applicable_value_to,
1987 created_by,
1988 creation_date,
1989 last_updated_by,
1990 last_update_date,
1991 last_update_login,
1992 object_version_number
1993 ) VALUES
1994 (
1995 (row_num*4)+l_max_app,
1996 check_format_id, -- system_profile_code
1997 'PAYMENT_METHOD',
1998 seeded_flag,
1999 NULL,
2000 Decode(iby_format_code, 'IBY_PAY_EFT_FOREIGN_DE', applicable_pmt_method, NVL(check_pmt_method_lookup_code, applicable_pmt_method)),
2001 created_by, -- created_by
2002 creation_date, -- creation_date
2003 last_updated_by, -- last_updated_by
2004 last_update_date, -- last_update_date
2005 last_update_login, -- last_update_login
2006 object_version_number -- object_version_number
2007 )
2008 -- applicable currency
2009 WHEN create_header = 1 THEN
2010 INTO iby_applicable_pmt_profs
2011 (
2012 applicable_pmt_prof_id,
2013 system_profile_code,
2014 applicable_type_code,
2015 seeded_flag,
2016 applicable_value_from,
2017 applicable_value_to,
2018 created_by,
2019 creation_date,
2020 last_updated_by,
2021 last_update_date,
2022 last_update_login,
2023 object_version_number
2024 ) VALUES
2025 (
2026 (row_num*4+1)+l_max_app,
2027 check_format_id, -- system_profile_code
2028 'CURRENCY_CODE',
2029 seeded_flag,
2030 NULL,
2031 DECODE(check_multi_currency_flag,
2032 'Y', NULL,
2033 NVL(check_currency_code, applicable_currency)),
2034 created_by, -- created_by
2035 creation_date, -- creation_date
2036 last_updated_by, -- last_updated_by
2037 last_update_date, -- last_update_date
2038 last_update_login, -- last_update_login
2039 object_version_number -- object_version_number
2040 )
2041 -- applicable organizations
2042 WHEN create_header = 1 THEN
2043 INTO iby_applicable_pmt_profs
2044 (
2045 applicable_pmt_prof_id,
2046 system_profile_code,
2047 applicable_type_code,
2048 seeded_flag,
2049 applicable_value_from,
2050 applicable_value_to,
2051 created_by,
2052 creation_date,
2053 last_updated_by,
2054 last_update_date,
2055 last_update_login,
2056 object_version_number
2057 ) VALUES
2058 (
2059 (row_num*4+2)+l_max_app,
2060 check_format_id, -- system_profile_code
2061 'PAYER_ORG',
2062 seeded_flag,
2063 DECODE(applicable_payer_org, NULL, NULL, 'OPERATING_UNIT'),
2064 applicable_payer_org,
2065 created_by, -- created_by
2066 creation_date, -- creation_date
2067 last_updated_by, -- last_updated_by
2068 last_update_date, -- last_update_date
2069 last_update_login, -- last_update_login
2070 object_version_number -- object_version_number
2071 )
2072 -- applicable internal bank account
2073 WHEN create_intba = 1 THEN
2074 INTO iby_applicable_pmt_profs
2075 (
2076 applicable_pmt_prof_id,
2077 system_profile_code,
2078 applicable_type_code,
2079 seeded_flag,
2080 applicable_value_from,
2081 applicable_value_to,
2082 created_by,
2083 creation_date,
2084 last_updated_by,
2085 last_update_date,
2086 last_update_login,
2087 object_version_number
2088 ) VALUES
2089 (
2090 (row_num*4+3)+l_max_app,
2091 check_format_id, -- system_profile_code
2092 'INTERNAL_BANK_ACCOUNT',
2093 seeded_flag,
2094 NULL,
2095 applicable_int_bank_account,
2096 created_by, -- created_by
2097 creation_date, -- creation_date
2098 last_updated_by, -- last_updated_by
2099 last_update_date, -- last_update_date
2100 last_update_login, -- last_update_login
2101 object_version_number -- object_version_number
2102 )
2103 WHEN create_header = 1 THEN
2104 INTO iby_pmt_creation_rules
2105 (
2106 system_profile_code,
2107 created_by,
2108 creation_date,
2109 last_updated_by,
2110 last_update_date,
2111 last_update_login,
2112 object_version_number,
2113 group_by_payment_details_flag,
2114 group_by_bank_charge_bearer,
2115 group_by_delivery_channel,
2116 group_by_remittance_message,
2117 group_by_max_documents_flag,
2118 group_by_unique_remit_id_flag,
2119 group_by_settle_priority_flag,
2120 group_by_payment_reason,
2121 group_by_due_date_flag,
2122 max_documents_per_payment,
2123 payment_details_length_limit,
2124 payment_details_formula
2125 ) VALUES
2126 (
2127 check_format_id, -- system_profile_code
2128 created_by, -- created_by
2129 creation_date, -- creation_date
2130 last_updated_by, -- last_updated_by
2131 last_update_date, -- last_update_date
2132 last_update_login, -- last_update_login
2133 object_version_number, -- object_version_number
2134 p_grp_by_payment_details_flag,
2135 p_grp_by_bank_charge_bearer,
2136 p_grp_by_delivery_channel,
2137 p_grp_by_remittance_message,
2138 p_grp_by_max_documents_flag,
2139 p_grp_by_unique_remit_id_flag,
2140 p_grp_by_settle_priority_flag,
2141 p_grp_by_payment_reason,
2142 DECODE(ap_program_name,
2143 NULL, p_grp_by_due_date_flag,
2144 check_grp_by_due_date),
2145 p_max_documents_per_payment,
2146 p_payment_details_length_limit,
2147 p_payment_details_formula
2148 )
2149 WHEN create_header = 1 THEN
2150 INTO iby_instr_creation_rules
2151 (
2152 system_profile_code,
2153 created_by,
2154 creation_date,
2155 last_updated_by,
2156 last_update_date,
2157 last_update_login,
2158 object_version_number,
2159 group_by_payment_date,
2160 group_by_payment_currency,
2161 group_by_internal_bank_account,
2162 group_by_max_payments_flag,
2163 group_by_pay_service_request,
2164 group_by_legal_entity,
2165 group_by_bill_payable,
2166 group_by_organization,
2167 group_by_max_instruction_flag,
2168 group_by_payment_function,
2169 group_by_payment_reason,
2170 group_by_rfc,
2171 max_payments_per_instruction,
2172 max_amount_per_instr_value,
2173 max_amount_per_instr_curr_code,
2174 max_amount_fx_rate_type,
2175 sort_option_1,
2176 sort_order_1,
2177 sort_option_2,
2178 sort_order_2,
2179 sort_option_3,
2180 sort_order_3,
2181 group_by_payment_method
2182 ) VALUES
2183 (
2184 check_format_id, -- system_profile_code
2185 created_by, -- created_by
2186 creation_date, -- creation_date
2187 last_updated_by, -- last_updated_by
2188 last_update_date, -- last_update_date
2189 last_update_login, -- last_update_login
2190 object_version_number, -- object_version_number
2191 i_grp_by_payment_date,
2192 i_grp_by_payment_currency,
2193 i_grp_by_internal_bank_account,
2194 i_grp_by_max_payments_flag,
2195 i_grp_by_pay_service_request,
2196 i_grp_by_legal_entity,
2197 i_grp_by_bill_payable,
2198 i_grp_by_organization,
2199 i_grp_by_max_instruction_flag,
2200 i_grp_by_payment_function,
2201 i_grp_by_payment_reason,
2202 i_grp_by_rfc,
2203 i_max_payments_per_instruction,
2204 i_max_amt_per_instr_value,
2205 i_max_amt_per_instr_curr_code,
2206 i_max_amt_fx_rate_type,
2207 i_sort_option_1,
2208 i_sort_order_1,
2209 i_sort_option_2,
2210 i_sort_order_2,
2211 i_sort_option_3,
2212 i_sort_order_3,
2213 i_grp_by_payment_method
2214 )
2215 WHEN create_header = 1 THEN
2216 INTO iby_remit_advice_setup
2217 (
2218 system_profile_code,
2219 document_count_limit,
2220 allow_multiple_copy_flag,
2221 seeded_flag,
2222 payment_details_length_limit,
2223 created_by,
2224 creation_date,
2225 last_updated_by,
2226 last_update_date,
2227 last_update_login,
2228 object_version_number,
2229 remittance_advice_format_code,
2230 sra_override_payee_flag,
2231 remit_advice_delivery_method,
2232 automatic_sra_submit_flag
2233 ) VALUES
2234 (
2235 check_format_id, -- system_profile_code
2236 document_count_limit,
2237 allow_multiple_copy_flag,
2238 seeded_flag,
2239 payment_details_length_limit,
2240 created_by, -- created_by
2241 creation_date, -- creation_date
2242 last_updated_by, -- last_updated_by
2243 last_update_date, -- last_update_date
2244 last_update_login, -- last_update_login
2245 object_version_number, -- object_version_number
2246 remit_advice_format_code,
2247 sra_override_payee_flag,
2248 remit_advice_delivery_method,
2249 automatic_sra_submit_flag
2250 )
2251 SELECT
2252 i.check_format_id,
2253 i.invoices_per_stub,
2254 i.check_pmt_method_lookup_code,
2255 i.check_currency_code,
2256 i.check_multi_currency_flag,
2257 i.check_zero_amounts_only,
2258 i.check_print_check_stub,
2259 i.check_grp_by_due_date,
2260 i.bank_file_character_set,
2261 i.transmission_flag,
2262 i.ap_program_name,
2263 i.friendly_name,
2264 i.iby_format_code,
2265 i.attribute_category,
2266 i.attribute1,
2267 i.attribute2,
2268 i.attribute3,
2269 i.attribute4,
2270 i.attribute5,
2271 i.attribute6,
2272 i.attribute7,
2273 i.attribute8,
2274 i.attribute9,
2275 i.attribute10,
2276 i.attribute11,
2277 i.attribute12,
2278 i.attribute13,
2279 i.attribute14,
2280 i.attribute15,
2281 i.creation_date,
2282 i.created_by,
2283 i.last_update_date,
2284 i.last_updated_by,
2285 i.last_update_login,
2286 1 object_version_number,
2287 i.payment_document_id,
2288 i.seeded_flag,
2289 i.system_profile_code,
2290 i.payment_format_code,
2291 i.print_instruction_immed_flag,
2292 i.processing_type,
2293 i.mark_complete_event,
2294 i.manual_mark_complete_flag,
2295 i.positive_pay_delivery_flag,
2296 i.transmit_instr_immed_flag,
2297 i.automatic_pi_reg_submit,
2298 i.send_to_file_flag,
2299 i.dcl_only_foreign_curr_pmt_flag,
2300 i.bep_suffix,
2301 i.bepid bepid,
2302 i.security_protocol_code,
2303 i.transmit_protocol_code,
2304 i.positive_pay_format_code,
2305 i.pay_file_letter_format_code,
2306 i.periodic_sequence_name_1,
2307 i.periodic_sequence_name_2,
2308 i.periodic_sequence_name_3,
2309 i.declaration_option,
2310 i.declaration_report_format_code,
2311 i.declaration_curr_fx_rate_type,
2312 i.declaration_currency_code,
2313 i.declaration_threshold_amount,
2314 i.bank_instruction1_code,
2315 i.bank_instruction2_code,
2316 i.bank_instruction_details,
2317 i.payment_text_message1,
2318 i.payment_text_message2,
2319 i.ack_transmit_protocol_code,
2320 i.ack_security_protocol_code,
2321 i.pi_register_format,
2322 i.bep_account_id,
2323 i.applicable_pmt_method,
2324 i.applicable_currency,
2325 i.applicable_payer_org,
2326 i.applicable_int_bank_account,
2327 i.p_grp_by_payment_details_flag,
2328 i.p_grp_by_bank_charge_bearer,
2329 i.p_grp_by_delivery_channel,
2330 i.p_grp_by_remittance_message,
2331 i.p_grp_by_max_documents_flag,
2332 i.p_grp_by_unique_remit_id_flag,
2333 i.p_grp_by_settle_priority_flag,
2334 i.p_grp_by_payment_reason,
2335 i.p_grp_by_due_date_flag,
2336 i.p_max_documents_per_payment,
2337 i.p_payment_details_length_limit,
2338 i.p_payment_details_formula,
2339 i.i_grp_by_payment_date,
2340 i.i_grp_by_payment_currency,
2341 i.i_grp_by_internal_bank_account,
2342 i.i_grp_by_max_payments_flag,
2343 i.i_grp_by_pay_service_request,
2344 i.i_grp_by_legal_entity,
2345 i.i_grp_by_bill_payable,
2346 i.i_grp_by_organization,
2347 i.i_grp_by_max_instruction_flag,
2348 i.i_grp_by_payment_function,
2349 i.i_grp_by_payment_reason,
2350 i.i_grp_by_rfc,
2351 i.i_max_payments_per_instruction,
2352 i.i_max_amt_per_instr_value,
2353 i.i_max_amt_per_instr_curr_code,
2354 i.i_max_amt_fx_rate_type,
2355 i.i_sort_option_1,
2356 i.i_sort_order_1,
2357 i.i_sort_option_2,
2358 i.i_sort_order_2,
2359 i.i_sort_option_3,
2360 i.i_sort_order_3,
2361 i.i_grp_by_payment_method,
2362 i.document_count_limit,
2363 i.allow_multiple_copy_flag,
2364 i.payment_details_length_limit,
2365 i.sra_override_payee_flag,
2366 i.remit_advice_delivery_method,
2367 i.automatic_sra_submit_flag,
2368 i.remit_advice_format_code,
2369 i.create_header,
2370 i.create_bepacct,
2371 i.create_intba,
2372 (CASE
2373 WHEN i.ap_program_name IN ('JENLFDOM','JENLFFGN','JENLPPSX') THEN
2374 l_jenl_eft_dir_value
2375 WHEN i.ap_program_name IN ('JENOPBDR','JENOPDDG','JENOPTGN') THEN
2376 NVL(i.outbound_pmt_file_directory, l_xxno_signet_file_value)
2377 WHEN i.ap_program_name IN ('JEATPSRA','JEATREFD','JEATIEFT','JEATPPF1',
2378 'JEATPPF2','JEATPPF3','JEATPPF4','JEATPPF5',
2379 'JEDKEIGO','JEDKEUGO','JEDKEUNI') THEN
2380 l_ece_out_file_value
2381 ELSE
2382 i.outbound_pmt_file_directory
2383 END) AS outbound_pmt_file_directory,
2384 i.electronic_processing_channel,
2385 i.logical_grouping_mode,
2386 i.batch_booking_flag,
2387 i.inactive_date,
2388 rownum row_num
2389 FROM
2390 ( SELECT
2391 DECODE(sfgdf.org_id,
2392 NULL, ppp.system_profile_code||'_'||to_char(cf.check_format_id),
2393 ppp.system_profile_code||'_'||to_char(cf.check_format_id)||
2394 '_'||sfgdf.org_id) check_format_id,
2395 cf.invoices_per_stub,
2396 cf.payment_method_lookup_code check_pmt_method_lookup_code,
2397 cf.currency_code check_currency_code,
2398 NVL(cf.multi_currency_flag, 'N') check_multi_currency_flag,
2399 NVL(cf.zero_amounts_only, 'N') check_zero_amounts_only,
2400 NVL(cf.print_check_stub, 'N') check_print_check_stub,
2401 NVL(cf.group_by_due_date, 'N') check_grp_by_due_date,
2402 cf.bank_file_character_set, -- used for Japanese payment programs as per etrm
2403 NVL(NVL(cf.transmissions_flag, pp.transmissions_enabled), 'N') transmission_flag,
2404 pp.program_name ap_program_name,
2405 pp.friendly_name,
2406 ipf.format_code iby_format_code,
2407 cf.attribute_category,
2408 cf.attribute1,
2409 cf.attribute2,
2410 cf.attribute3,
2411 cf.attribute4,
2412 cf.attribute5,
2413 cf.attribute6,
2414 cf.attribute7,
2415 cf.attribute8,
2416 cf.attribute9,
2417 cf.attribute10,
2418 cf.attribute11,
2419 cf.attribute12,
2420 cf.attribute13,
2421 cf.attribute14,
2422 cf.attribute15,
2423 NVL(cf.creation_date, cf.last_update_date) creation_date,
2424 NVL(cf.created_by, cf.last_updated_by) created_by,
2425 cf.last_update_date,
2426 cf.last_updated_by,
2427 cf.last_update_login,
2428 (CASE
2429 WHEN ppp.processing_type = 'PRINTED' THEN
2430 (SELECT cs.check_stock_id
2431 FROM ap_check_stocks_all cs
2432 WHERE (cs.inactive_date IS NULL OR
2433 TRUNC(cs.inactive_date) >= TRUNC(sysdate))
2434 AND cs.check_format_id = cf.check_format_id
2435 AND ROWNUM=1)
2436 END) AS payment_document_id,
2437 DECODE(cf.created_by,
2438 1, DECODE(cf.last_updated_by, 1, 'Y', 'N'),
2439 'N') seeded_flag,
2440 ppp.inactive_date,
2441 ppp.system_profile_code system_profile_code,
2442 ppp.payment_format_code payment_format_code,
2443 ppp.print_instruction_immed_flag print_instruction_immed_flag,
2444 ppp.processing_type processing_type,
2445 ppp.mark_complete_event mark_complete_event,
2446 ppp.manual_mark_complete_flag manual_mark_complete_flag,
2447 ppp.positive_pay_delivery_flag positive_pay_delivery_flag,
2448 ppp.transmit_instr_immed_flag transmit_instr_immed_flag,
2449 ppp.automatic_pi_reg_submit automatic_pi_reg_submit,
2450 ppp.send_to_file_flag send_to_file_flag,
2451 ppp.dcl_only_foreign_curr_pmt_flag dcl_only_foreign_curr_pmt_flag,
2452 ppp.bep_suffix,
2453 bep.bepid bepid,
2454 ppp.security_protocol_code security_protocol_code,
2455 ppp.transmit_protocol_code transmit_protocol_code,
2456 ppp.positive_pay_format_code positive_pay_format_code,
2457 ppp.pay_file_letter_format_code pay_file_letter_format_code,
2458 ppp.periodic_sequence_name_1 periodic_sequence_name_1,
2459 ppp.periodic_sequence_name_2 periodic_sequence_name_2,
2460 ppp.periodic_sequence_name_3 periodic_sequence_name_3,
2461 NVL(sfgdf.declaration_option, ppp.declaration_option) declaration_option,
2462 ppp.declaration_report_format_code declaration_report_format_code,
2463 NVL(sfgdf.declaration_curr_fx_rate_type,
2464 ppp.declaration_curr_fx_rate_type) declaration_curr_fx_rate_type,
2465 NVL(sfgdf.declaration_currency_code,
2466 ppp.declaration_currency_code) declaration_currency_code,
2467 NVL(TO_NUMBER(sfgdf.declaration_threshold_amount),
2468 ppp.declaration_threshold_amount) declaration_threshold_amount,
2469 (CASE
2470 WHEN gdf.jgzz_attribute_category = 'FI.LMP' THEN
2471 (SELECT bi.bank_instruction_code
2472 FROM iby_bank_instructions_vl bi
2473 WHERE bi.format_value = gdf.jgzz_format_info6
2474 AND bi.territory_code = 'FI')
2475 ELSE
2476 ppp.bank_instruction1_code
2477 END) AS bank_instruction1_code,
2478 ppp.bank_instruction2_code bank_instruction2_code,
2479 ppp.bank_instruction_details bank_instruction_details,
2480 NVL(sfgdf.payment_text_message1, ppp.payment_text_message1) payment_text_message1,
2481 NVL(sfgdf.payment_text_message2, ppp.payment_text_message2) payment_text_message2,
2482 ppp.ack_transmit_protocol_code ack_transmit_protocol_code,
2483 ppp.ack_security_protocol_code ack_security_protocol_code,
2484 ppp.pi_register_format pi_register_format,
2485 NVL(key.bep_account_id, ppp.bep_account_id) bep_account_id,
2486 ppp.applicable_pmt_method,
2487 ppp.applicable_currency,
2488 NVL(TO_CHAR(sfgdf.org_id), ppp.applicable_payer_org) applicable_payer_org,
2489 NVL(TO_CHAR(cba.ce_bank_account_id), ppp.applicable_int_bank_account) applicable_int_bank_account,
2490 (CASE
2491 WHEN (ipf.reference_format_code = 'JENLFDOM' OR
2492 ipf.reference_format_code = 'JENLFFGN') AND
2493 NVL(l_jenl_pmt_separation, 'N') = 'N' THEN 'N'
2494 ELSE ppp.pmt_group_by_payment_details
2495 END) AS p_grp_by_payment_details_flag,
2496 ppp.pmt_group_by_bank_charge_bear p_grp_by_bank_charge_bearer,
2497 ppp.pmt_group_by_delivery_channel p_grp_by_delivery_channel,
2498 ppp.pmt_group_by_remittance_mes p_grp_by_remittance_message,
2499 ppp.pmt_group_by_max_documents p_grp_by_max_documents_flag,
2500 ppp.pmt_group_by_unique_remit_id p_grp_by_unique_remit_id_flag,
2501 ppp.pmt_group_by_settle_priority p_grp_by_settle_priority_flag,
2502 ppp.pmt_group_by_payment_reason p_grp_by_payment_reason,
2503 ppp.pmt_group_by_due_date_flag p_grp_by_due_date_flag,
2504 ppp.pmt_max_documents_per_payment p_max_documents_per_payment,
2505 (CASE
2506 WHEN (ipf.reference_format_code = 'JENLFDOM' OR
2507 ipf.reference_format_code = 'JENLFFGN') AND
2508 NVL(l_jenl_pmt_separation, 'N') = 'N' THEN NULL
2509 ELSE ppp.pmt_payment_details_length_lim
2510 END) AS p_payment_details_length_limit,
2511 (CASE
2512 WHEN (ipf.reference_format_code = 'JENLFDOM' OR
2513 ipf.reference_format_code = 'JENLFFGN') AND
2514 NVL(l_jenl_pmt_separation, 'N') = 'N' THEN NULL
2515 ELSE ppp.pmt_payment_details_formula
2516 END) AS p_payment_details_formula,
2517 ppp.inst_group_by_payment_date i_grp_by_payment_date,
2518 ppp.inst_group_by_payment_currency i_grp_by_payment_currency,
2519 ppp.inst_group_by_int_bank_acct i_grp_by_internal_bank_account,
2520 ppp.inst_group_by_max_payments i_grp_by_max_payments_flag,
2521 ppp.inst_group_by_pay_service_req i_grp_by_pay_service_request,
2522 ppp.inst_group_by_legal_entity i_grp_by_legal_entity,
2523 ppp.inst_group_by_bill_payable i_grp_by_bill_payable,
2524 ppp.inst_group_by_organization i_grp_by_organization,
2525 ppp.inst_group_by_max_instruction i_grp_by_max_instruction_flag,
2526 ppp.inst_group_by_payment_function i_grp_by_payment_function,
2527 ppp.inst_group_by_payment_reason i_grp_by_payment_reason,
2528 ppp.inst_group_by_rfc i_grp_by_rfc,
2529 ppp.inst_max_payments_per_instr i_max_payments_per_instruction,
2530 ppp.inst_max_amount_per_instr_val i_max_amt_per_instr_value,
2531 ppp.inst_max_amount_per_instr_curr i_max_amt_per_instr_curr_code,
2532 ppp.inst_max_amount_fx_rate_type i_max_amt_fx_rate_type,
2533 ppp.inst_sort_option_1 i_sort_option_1,
2534 ppp.inst_sort_order_1 i_sort_order_1,
2535 ppp.inst_sort_option_2 i_sort_option_2,
2536 ppp.inst_sort_order_2 i_sort_order_2,
2537 ppp.inst_sort_option_3 i_sort_option_3,
2538 ppp.inst_sort_order_3 i_sort_order_3,
2539 ppp.inst_group_by_payment_method i_grp_by_payment_method,
2540 ppp.remit_document_count_limit document_count_limit,
2541 ppp.remit_allow_multiple_copy_flag allow_multiple_copy_flag,
2542 ppp.remit_pmt_details_length_lim payment_details_length_limit,
2543 ppp.remit_sra_override_payee_flag sra_override_payee_flag,
2544 ppp.remit_advice_delivery_method remit_advice_delivery_method,
2545 ppp.remit_automatic_sra_submit automatic_sra_submit_flag,
2546 DECODE(NVL(cf.separate_remittance_advice, 'N'),
2547 'Y', NVL(ppp.remit_remittance_advice_format,
2548 l_remit_advice_format_code),
2549 NULL) remit_advice_format_code,
2550 sfgdf.outbound_pmt_file_directory,
2551 ppp.electronic_processing_channel,
2552 ppp.logical_grouping_mode,
2553 ppp.batch_booking_flag,
2554 rank() over (partition by cf.check_format_id, sfgdf.org_id
2555 order by key.bep_account_id,cs.bank_account_id,cs.check_stock_id) create_header,
2556 rank() over (partition by cf.check_format_id, sfgdf.org_id, key.bep_account_id
2557 order by cs.check_stock_id) create_bepacct,
2558 rank() over (partition by cf.check_format_id, sfgdf.org_id, cs.bank_account_id
2559 order by cs.check_stock_id, key.bep_account_id) create_intba
2560 FROM
2561 ap_check_formats cf,
2562 ap_check_stocks_all cs,
2563 ce_upg_bank_accounts cba,
2564 ap_payment_programs pp,
2565 jg_zz_pay_format_info gdf,
2566 (SELECT (CASE
2567 WHEN sfa.jgzz_attribute_category = 'SE.JESEPBAI' THEN
2568 sfa.jgzz_system_info5
2569 END) AS payment_text_message1,
2570 (CASE
2571 WHEN sfa.jgzz_attribute_category = 'SE.JESEPBAI' THEN
2572 sfa.jgzz_system_info3
2573 END) AS payment_text_message2,
2574 (CASE
2575 WHEN sfa.jgzz_attribute_category = 'NO.JENOPTGN' THEN
2576 sfa.jgzz_system_info20
2577 WHEN sfa.jgzz_attribute_category = 'SE.JESEPBAI' THEN
2578 sfa.jgzz_system_info10
2579 WHEN sfa.jgzz_attribute_category = 'SE.JESEPBSI' THEN
2580 sfa.jgzz_system_info8
2581 WHEN sfa.jgzz_attribute_category = 'SE.JESEPBUT' THEN
2582 sfa.jgzz_system_info8
2583 WHEN sfa.jgzz_attribute_category = 'SE.JESEPPOI' THEN
2584 sfa.jgzz_system_info11
2585 WHEN sfa.jgzz_attribute_category = 'SE.JESEPPOU' THEN
2586 sfa.jgzz_system_info10
2587 END) AS outbound_pmt_file_directory,
2588 (CASE
2589 WHEN sfa.jgzz_attribute_category = 'DE.JEDEDEFI' THEN
2590 DECODE(sfa.jgzz_system_info3,
2591 'Y', 'DECLARE_THROUGH_BANK',
2592 'N', 'NO_DECLARATIONS',
2593 sfa.jgzz_system_info3)
2594 WHEN sfa.jgzz_attribute_category = 'NL.JENLFFGN' THEN
2595 DECODE(l_jenl_eft_reporting_limit, NULL, 'NO_DECLARATIONS',
2596 'DECLARE_THROUGH_BANK')
2597 END) AS declaration_option,
2598 (CASE
2599 WHEN sfa.jgzz_attribute_category = 'DE.JEDEDEFI' THEN
2600 TO_NUMBER(sfa.jgzz_system_info5)
2601 WHEN sfa.jgzz_attribute_category = 'NL.JENLFFGN' THEN
2602 l_jenl_eft_reporting_limit
2603 END) AS declaration_threshold_amount,
2604 (CASE
2605 WHEN sfa.jgzz_attribute_category = 'DE.JEDEDEFI' AND
2606 sfa.jgzz_system_info5 IS NOT NULL THEN 'EUR'
2607 WHEN sfa.jgzz_attribute_category = 'NL.JENLFFGN' AND
2608 l_jenl_eft_reporting_limit IS NOT NULL THEN
2609 'NLG'
2610 END) AS declaration_currency_code,
2611 (CASE
2612 WHEN sfa.jgzz_attribute_category = 'NL.JENLFFGN' THEN
2613 sfa.jgzz_system_info13
2614 WHEN sfa.jgzz_attribute_category = 'DE.JEDEDEFI' THEN
2615 DECODE(sfa.jgzz_system_info3,
2616 'Y', 'Corporate')
2617 END) AS declaration_curr_fx_rate_type,
2618 sfa.jgzz_attribute_category,
2619 SUBSTR(sfa.jgzz_attribute_category,
2620 LENGTH(sfa.jgzz_country_code)+2,
2621 LENGTH(sfa.jgzz_attribute_category)) format_code,
2622 sfa.org_id
2623 FROM jg_zz_sys_formats_all_b sfa) sfgdf,
2624 iby_formats_b ipf,
2625 iby_upg_ppp_b ppp,
2626 iby_bepinfo bep,
2627 iby_bepkeys key
2628 WHERE cf.format_payments_program_id = pp.program_id
2629 AND cf.check_format_id = cs.check_format_id(+)
2630 AND cs.bank_account_id = cba.source_pk_id(+)
2631 AND cba.source_application_id(+) = 200
2632 AND cf.check_format_id = gdf.check_format_id(+)
2633 AND pp.program_name = ipf.reference_format_code
2634 AND ipf.format_type_code = 'OUTBOUND_PAYMENT_INSTRUCTION'
2635 AND pp.program_name = sfgdf.format_code(+)
2636 AND ppp.payment_format_code = ipf.format_code
2637 AND ppp.bep_suffix = bep.suffix(+)
2638 AND bep.bepid = key.bepid(+)
2639 UNION ALL
2640 SELECT
2641 ppp.system_profile_code check_format_id,
2642 NULL invoices_per_stub,
2643 NULL check_pmt_method_lookup_code,
2644 NULL check_currency_code,
2645 NULL check_multi_currency_flag,
2646 NULL check_zero_amounts_only,
2647 NULL check_print_check_stub,
2648 NULL check_grp_by_due_date,
2649 NULL bank_file_character_set,
2650 NULL transmission_flag,
2651 NULL ap_program_name,
2652 NULL friendly_name,
2653 ipf.format_code iby_format_code,
2654 NULL attribute_category,
2655 NULL attribute1,
2656 NULL attribute2,
2657 NULL attribute3,
2658 NULL attribute4,
2659 NULL attribute5,
2660 NULL attribute6,
2661 NULL attribute7,
2662 NULL attribute8,
2663 NULL attribute9,
2664 NULL attribute10,
2665 NULL attribute11,
2666 NULL attribute12,
2667 NULL attribute13,
2668 NULL attribute14,
2669 NULL attribute15,
2670 ppp.creation_date,
2671 ppp.created_by,
2672 ppp.last_update_date,
2673 ppp.last_updated_by,
2674 ppp.last_update_login,
2675 NULL payment_document_id,
2676 'Y' seeded_flag,
2677 ppp.inactive_date,
2678 ppp.system_profile_code system_profile_code,
2679 ppp.payment_format_code payment_format_code,
2680 ppp.print_instruction_immed_flag print_instruction_immed_flag,
2681 ppp.processing_type processing_type,
2682 ppp.mark_complete_event mark_complete_event,
2683 ppp.manual_mark_complete_flag manual_mark_complete_flag,
2684 ppp.positive_pay_delivery_flag positive_pay_delivery_flag,
2685 ppp.transmit_instr_immed_flag transmit_instr_immed_flag,
2686 ppp.automatic_pi_reg_submit automatic_pi_reg_submit,
2687 ppp.send_to_file_flag send_to_file_flag,
2688 ppp.dcl_only_foreign_curr_pmt_flag dcl_only_foreign_curr_pmt_flag,
2689 ppp.bep_suffix,
2690 bep.bepid bepid,
2691 ppp.security_protocol_code security_protocol_code,
2692 ppp.transmit_protocol_code transmit_protocol_code,
2693 ppp.positive_pay_format_code positive_pay_format_code,
2694 ppp.pay_file_letter_format_code pay_file_letter_format_code,
2695 ppp.periodic_sequence_name_1 periodic_sequence_name_1,
2696 ppp.periodic_sequence_name_2 periodic_sequence_name_2,
2697 ppp.periodic_sequence_name_3 periodic_sequence_name_3,
2698 ppp.declaration_option declaration_option,
2699 ppp.declaration_report_format_code declaration_report_format_code,
2700 ppp.declaration_curr_fx_rate_type declaration_curr_fx_rate_type,
2701 ppp.declaration_currency_code declaration_currency_code,
2702 ppp.declaration_threshold_amount declaration_threshold_amount,
2703 ppp.bank_instruction1_code bank_instruction1_code,
2704 ppp.bank_instruction2_code bank_instruction2_code,
2705 ppp.bank_instruction_details bank_instruction_details,
2706 ppp.payment_text_message1 payment_text_message1,
2707 ppp.payment_text_message2 payment_text_message2,
2708 ppp.ack_transmit_protocol_code ack_transmit_protocol_code,
2709 ppp.ack_security_protocol_code ack_security_protocol_code,
2710 ppp.pi_register_format pi_register_format,
2711 NVL(key.bep_account_id, ppp.bep_account_id) bep_account_id,
2712 ppp.applicable_pmt_method,
2713 ppp.applicable_currency,
2714 ppp.applicable_payer_org,
2715 ppp.applicable_int_bank_account,
2716 ppp.pmt_group_by_payment_details p_grp_by_payment_details_flag,
2717 ppp.pmt_group_by_bank_charge_bear p_grp_by_bank_charge_bearer,
2718 ppp.pmt_group_by_delivery_channel p_grp_by_delivery_channel,
2719 ppp.pmt_group_by_remittance_mes p_grp_by_remittance_message,
2720 ppp.pmt_group_by_max_documents p_grp_by_max_documents_flag,
2721 ppp.pmt_group_by_unique_remit_id p_grp_by_unique_remit_id_flag,
2722 ppp.pmt_group_by_settle_priority p_grp_by_settle_priority_flag,
2723 ppp.pmt_group_by_payment_reason p_grp_by_payment_reason,
2724 ppp.pmt_group_by_due_date_flag p_grp_by_due_date_flag,
2725 ppp.pmt_max_documents_per_payment p_max_documents_per_payment,
2726 ppp.pmt_payment_details_length_lim p_payment_details_length_limit,
2727 ppp.pmt_payment_details_formula p_payment_details_formula,
2728 ppp.inst_group_by_payment_date i_grp_by_payment_date,
2729 ppp.inst_group_by_payment_currency i_grp_by_payment_currency,
2730 ppp.inst_group_by_int_bank_acct i_grp_by_internal_bank_account,
2731 ppp.inst_group_by_max_payments i_grp_by_max_payments_flag,
2732 ppp.inst_group_by_pay_service_req i_grp_by_pay_service_request,
2733 ppp.inst_group_by_legal_entity i_grp_by_legal_entity,
2734 ppp.inst_group_by_bill_payable i_grp_by_bill_payable,
2735 ppp.inst_group_by_organization i_grp_by_organization,
2736 ppp.inst_group_by_max_instruction i_grp_by_max_instruction_flag,
2737 ppp.inst_group_by_payment_function i_grp_by_payment_function,
2738 ppp.inst_group_by_payment_reason i_grp_by_payment_reason,
2739 ppp.inst_group_by_rfc i_grp_by_rfc,
2740 ppp.inst_max_payments_per_instr i_max_payments_per_instruction,
2741 ppp.inst_max_amount_per_instr_val i_max_amt_per_instr_value,
2742 ppp.inst_max_amount_per_instr_curr i_max_amt_per_instr_curr_code,
2743 ppp.inst_max_amount_fx_rate_type i_max_amt_fx_rate_type,
2744 ppp.inst_sort_option_1 i_sort_option_1,
2745 ppp.inst_sort_order_1 i_sort_order_1,
2746 ppp.inst_sort_option_2 i_sort_option_2,
2747 ppp.inst_sort_order_2 i_sort_order_2,
2748 ppp.inst_sort_option_3 i_sort_option_3,
2749 ppp.inst_sort_order_3 i_sort_order_3,
2750 ppp.inst_group_by_payment_method i_grp_by_payment_method,
2751 ppp.remit_document_count_limit document_count_limit,
2752 ppp.remit_allow_multiple_copy_flag allow_multiple_copy_flag,
2753 ppp.remit_pmt_details_length_lim payment_details_length_limit,
2754 ppp.remit_sra_override_payee_flag sra_override_payee_flag,
2755 ppp.remit_advice_delivery_method remit_advice_delivery_method,
2756 ppp.remit_automatic_sra_submit automatic_sra_submit_flag,
2757 ppp.remit_remittance_advice_format remit_advice_format_code,
2758 null outbound_pmt_file_directory,
2759 ppp.electronic_processing_channel,
2760 ppp.logical_grouping_mode,
2761 ppp.batch_booking_flag,
2762 rank() over (partition by ppp.system_profile_code
2763 order by key.bep_account_id) create_header,
2764 1 create_bepacct,
2765 rank() over (partition by ppp.system_profile_code
2766 order by key.bep_account_id) create_intba
2767 FROM
2768 iby_formats_b ipf,
2769 iby_upg_ppp_b ppp,
2770 iby_bepinfo bep,
2771 iby_bepkeys key
2772 WHERE (ipf.reference_format_code IS NULL OR
2773 (ipf.reference_format_code IS NOT NULL AND
2774 ipf.reference_format_code NOT IN (SELECT pp.program_name
2775 FROM ap_check_formats cf,
2776 ap_payment_programs pp
2777 WHERE cf.format_payments_program_id = pp.program_id))
2778 )
2779 AND ppp.payment_format_code = ipf.format_code
2780 AND ipf.format_type_code = 'OUTBOUND_PAYMENT_INSTRUCTION'
2781 AND ppp.bep_suffix = bep.suffix(+)
2782 AND bep.bepid = key.bepid(+)) i
2783 WHERE i.check_format_id NOT IN (SELECT spp.system_profile_code
2784 FROM iby_sys_pmt_profiles_b spp);
2785 EXCEPTION
2786 WHEN others THEN NULL;
2787 END;
2788
2789 ---------------------------------------------------------------------------
2790 -- Step 4: Insert into NLS tables for system and payment profiles --
2791 ---------------------------------------------------------------------------
2792 BEGIN
2793 INSERT ALL
2794 WHEN create_header = 1 THEN
2795 INTO iby_sys_pmt_profiles_tl
2796 (
2797 system_profile_code,
2798 system_profile_name,
2799 language,
2800 source_lang,
2801 created_by,
2802 creation_date,
2803 last_updated_by,
2804 last_update_date,
2805 last_update_login,
2806 object_version_number,
2807 system_profile_description
2808 ) VALUES
2809 (
2810 system_profile_code, -- system_profile_code
2811 system_profile_name,
2812 language_code,
2813 'US',
2814 created_by, -- created_by
2815 creation_date, -- creation_date
2816 last_updated_by, -- last_updated_by
2817 last_update_date, -- last_update_date
2818 last_update_login, -- last_update_login
2819 object_version_number, -- object_version_number
2820 system_profile_description
2821 )
2822 WHEN 1 = 1 THEN
2823 INTO iby_acct_pmt_profiles_tl
2824 (
2825 payment_profile_id,
2826 payment_profile_name,
2827 language,
2828 source_lang,
2829 created_by,
2830 creation_date,
2831 last_updated_by,
2832 last_update_date,
2833 last_update_login,
2834 object_version_number
2835 ) VALUES
2836 (
2837 payment_profile_id,
2838 payment_profile_name,
2839 language_code,
2840 'US',
2841 created_by, -- created_by
2842 creation_date, -- creation_date
2843 last_updated_by, -- last_updated_by
2844 last_update_date, -- last_update_date
2845 last_update_login, -- last_update_login
2846 object_version_number -- object_version_number
2847 )
2848 SELECT
2849 i.system_profile_code,
2850 i.system_profile_name,
2851 i.system_profile_description,
2852 SUBSTRB(i.system_profile_name||' '||i.bep_name||' '||i.key, 1, 100) payment_profile_name,
2853 i.language_code,
2854 i.bep_name,
2855 i.payment_profile_id,
2856 i.key,
2857 i.creation_date,
2858 i.created_by,
2859 i.last_update_date,
2860 i.last_updated_by,
2861 i.last_update_login,
2862 1 object_version_number,
2863 i.create_header
2864 FROM (
2865 SELECT
2866 DECODE(sfgdf.org_id,
2867 NULL, ppp.system_profile_code||'_'||to_char(cf.check_format_id),
2868 ppp.system_profile_code||'_'||to_char(cf.check_format_id)||
2869 '_'||sfgdf.org_id) system_profile_code,
2870 /* Modified the script to change the names of the PPPs which are
2871 created with the formats (Stub After Payment). Hard coded the
2872 format codes as these are the only formats which are duplicated
2873 for providing the flexiblity to the customers
2874 Bug Number: 7532799*/
2875 DECODE(sfgdf.org_id,
2876 NULL, cf.name|| Decode(ppb.system_profile_code, 'IBY_PAY_EFT_German_General', ' General Payments',
2877 'IBY_PAY_EFT_German_EU_Standard', ' EU Standard Payments',
2878 'IBY_PAY_EFT_German_EUE', ' EUE Payments',
2879 '')
2880 || Decode(ipf.format_code, 'IBY_PAY_CHK_DE_A', '(Stub after Payment)',
2881 'IBY_PAY_CHK_FORM_FEED_1A', '(Stub after Payment)',
2882 'IBY_PAY_CHK_FORM_FEED_2A', '(Stub after Payment)',
2883 'IBY_PAY_CHK_FR_A', '(Stub after Payment)',
2884 'IBY_PAY_CHK_LASER_A', '(Stub after Payment)',
2885 'IBY_PAY_CHK_STANDARD_1A', '(Stub after Payment)',
2886 'IBY_PAY_CHK_STANDARD_2A', '(Stub after Payment)',
2887 ''),
2888 cf.name|| Decode(ppb.system_profile_code, 'IBY_PAY_EFT_German_General', ' General Payments',
2889 'IBY_PAY_EFT_German_EU_Standard', ' EU Standard Payments',
2890 'IBY_PAY_EFT_German_EUE', ' EUE Payments',
2891 '')
2892 ||' - '||ou.name
2893 || Decode(ipf.format_code, 'IBY_PAY_CHK_DE_A', '(Stub after Payment)',
2894 'IBY_PAY_CHK_FORM_FEED_1A', '(Stub after Payment)',
2895 'IBY_PAY_CHK_FORM_FEED_2A', '(Stub after Payment)',
2896 'IBY_PAY_CHK_FR_A', '(Stub after Payment)',
2897 'IBY_PAY_CHK_LASER_A', '(Stub after Payment)',
2898 'IBY_PAY_CHK_STANDARD_1A', '(Stub after Payment)',
2899 'IBY_PAY_CHK_STANDARD_2A', '(Stub after Payment)',
2900 '')
2901 ) system_profile_name,
2902 pp.friendly_name system_profile_description,
2903 ppp.language language_code,
2904 bep.name bep_name,
2905 app.payment_profile_id,
2906 key.key,
2907 NVL(cf.creation_date, cf.last_update_date) creation_date,
2908 NVL(cf.created_by, cf.last_updated_by) created_by,
2909 cf.last_update_date,
2910 cf.last_updated_by,
2911 cf.last_update_login,
2912 rank() over (partition by app.system_profile_code
2913 order by app.payment_profile_id) create_header
2914 FROM
2915 ap_check_formats cf,
2916 ap_payment_programs pp,
2917 iby_formats_b ipf,
2918 iby_upg_ppp_tl ppp,
2919 iby_upg_ppp_b ppb,
2920 iby_acct_pmt_profiles_b app,
2921 iby_bepinfo bep,
2922 iby_bepkeys key,
2923 (SELECT SUBSTR(sfa.jgzz_attribute_category,
2924 LENGTH(sfa.jgzz_country_code)+2,
2925 LENGTH(sfa.jgzz_attribute_category)) format_code,
2926 sfa.org_id
2927 FROM jg_zz_sys_formats_all_b sfa) sfgdf,
2928 hr_organization_units ou
2929 WHERE cf.format_payments_program_id = pp.program_id
2930 AND pp.program_name = ipf.reference_format_code
2931 AND ipf.format_type_code = 'OUTBOUND_PAYMENT_INSTRUCTION'
2932 AND pp.program_name = sfgdf.format_code(+)
2933 AND sfgdf.org_id = ou.organization_id(+)
2934 AND DECODE(sfgdf.org_id,
2935 NULL, ppp.system_profile_code||'_'||to_char(cf.check_format_id),
2936 ppp.system_profile_code||'_'||to_char(cf.check_format_id)||
2937 '_'||sfgdf.org_id) = app.system_profile_code
2938 AND ppb.payment_format_code = ipf.format_code
2939 AND ppp.system_profile_code = ppb.system_profile_code
2940 AND ppb.bep_suffix = bep.suffix(+)
2941 AND app.bep_account_id = key.bep_account_id(+)
2942 UNION ALL
2943 SELECT
2944 ppp.system_profile_code system_profile_code,
2945 ppp.system_profile_name system_profile_name,
2946 ppp.system_profile_description system_profile_description,
2947 ppp.language language_code,
2948 bep.name bep_name,
2949 app.payment_profile_id,
2950 key.key,
2951 ppp.creation_date,
2952 ppp.created_by,
2953 ppp.last_update_date,
2954 ppp.last_updated_by,
2955 ppp.last_update_login,
2956 rank() over (partition by app.system_profile_code
2957 order by app.payment_profile_id) create_header
2958 FROM
2959 iby_formats_b ipf,
2960 iby_upg_ppp_tl ppp,
2961 iby_upg_ppp_b ppb,
2962 iby_acct_pmt_profiles_b app,
2963 iby_bepinfo bep,
2964 iby_bepkeys key
2965 WHERE (ipf.reference_format_code IS NULL OR
2966 (ipf.reference_format_code IS NOT NULL AND
2967 ipf.reference_format_code NOT IN (SELECT pp.program_name
2968 FROM ap_check_formats cf,
2969 ap_payment_programs pp
2970 WHERE cf.format_payments_program_id = pp.program_id))
2971 )
2972 AND ppb.payment_format_code = ipf.format_code
2973 AND ipf.format_type_code = 'OUTBOUND_PAYMENT_INSTRUCTION'
2974 AND ppp.system_profile_code = app.system_profile_code
2975 AND ppp.system_profile_code = ppb.system_profile_code
2976 AND ppb.bep_suffix = bep.suffix(+)
2977 AND app.bep_account_id = key.bep_account_id(+)) i
2978 WHERE i.system_profile_code NOT IN (SELECT spp.system_profile_code
2979 FROM iby_sys_pmt_profiles_tl spp);
2980 EXCEPTION
2981 WHEN others THEN NULL;
2982
2983 END;
2984
2985 ---------------------------------------------------------------------------
2986 -- Step 5: Insert DK payment categories as applicable pmt methods for ECE
2987 -- PPPs.
2988 ---------------------------------------------------------------------------
2989 BEGIN
2990 SELECT NVL(MAX(applicable_pmt_prof_id),0)
2991 INTO l_max_app
2992 FROM iby_applicable_pmt_profs app
2993 WHERE applicable_pmt_prof_id < 10000;
2994 EXCEPTION
2995 WHEN no_data_found THEN
2996 l_max_app := 0;
2997 END;
2998
2999 BEGIN
3000 INSERT INTO iby_applicable_pmt_profs
3001 (
3002 applicable_pmt_prof_id,
3003 system_profile_code,
3004 applicable_type_code,
3005 seeded_flag,
3006 applicable_value_from,
3007 applicable_value_to,
3008 created_by,
3009 creation_date,
3010 last_updated_by,
3011 last_update_date,
3012 last_update_login,
3013 object_version_number
3014 )
3015 SELECT
3016 (rownum)+l_max_app,
3017 apm.system_profile_code,
3018 'PAYMENT_METHOD',
3019 apm.seeded_flag,
3020 NULL,
3021 pm.payment_method_code,
3022 apm.created_by,
3023 apm.creation_date,
3024 apm.last_updated_by,
3025 apm.last_update_date,
3026 apm.last_update_login,
3027 apm.object_version_number
3028 FROM iby_applicable_pmt_profs apm,
3029 iby_sys_pmt_profiles_b ppp,
3030 iby_payment_methods_b pm
3031 WHERE ppp.system_profile_code = apm.system_profile_code
3032 AND ppp.payment_format_code = 'IBY_PAY_ECE'
3033 AND apm.applicable_type_code = 'PAYMENT_METHOD'
3034 AND pm.payment_method_code like 'DK%'
3035 AND NOT EXISTS (SELECT a.applicable_pmt_prof_id
3036 FROM iby_applicable_pmt_profs a
3037 WHERE a.system_profile_code = ppp.system_profile_code
3038 AND a.applicable_type_code = 'PAYMENT_METHOD'
3039 AND a.applicable_value_to = pm.payment_method_code);
3040 EXCEPTION
3041 WHEN others THEN NULL;
3042
3043 END;
3044
3045
3046 END set_sys_user_profiles;
3047
3048 end IBY_UPG_PPP_PKG;