1 PACKAGE BODY IBY_PAYMENT_FORMAT_VAL_PUB AS
2 /* $Header: ibyfvvsb.pls 120.18.12020000.2 2012/07/12 14:58:43 sgogula ship $ */
3 ----------------------------------------------------------------------------------------------------------
4
5 -- Declaring Global Variables
6
7 g_EXCEPTION NUMBER;
8 g_FAILURE NUMBER;
9 g_SUCCESS NUMBER;
10
11 ----------------------------------------------------------------------------------------------------------
12 /*
13
14 PROCEDURE : FVBLCCDP
15
16 Bulk Data CCDP Payment Format Report
17
18 */
19
20 PROCEDURE FVBLCCDP
21 (
22 p_validation_assign_id IN IBY_VAL_ASSIGNMENTS.validation_assignment_id%TYPE,
23 p_validation_set_code IN IBY_VALIDATION_SETS_VL.validation_set_code%TYPE,
24 p_instruction_id IN IBY_PAY_INSTRUCTIONS_ALL.payment_instruction_id%type,
25 p_is_online_val IN VARCHAR2,
26 x_result OUT NOCOPY NUMBER
27 )IS
28 -- Initialising Payment Record
29 l_instruction_rec IBY_VALIDATIONSETS_PUB.instructionRecType;
30 l_docErrorTab IBY_VALIDATIONSETS_PUB.docErrorTabType;
31 l_docErrorRec IBY_TRANSACTION_ERRORS%ROWTYPE;
32
33 -- Pick Up The Required Data From Instructions (IBY_PAY_INSTRUCTIONS_ALL) using payemnt_instruction_id
34 CURSOR pay_instr_data_csr(p_instruction_id IBY_PAY_INSTRUCTIONS_ALL.payment_instruction_id%TYPE) IS
35 SELECT org_id
36 FROM iby_pay_instructions_all
37 WHERE
38 payment_instruction_id = p_instruction_id;
39
40 -- Pick Up Required Data From Payments (IBY_PAYMENTS_ALL) using payment_instruction_id
41 -- Pick up Tax Payer Id/SSN from here -- Bug 5468203
42 CURSOR iby_pmt_csr(p_instruction_id IBY_PAY_INSTRUCTIONS_ALL.payment_instruction_id%TYPE) IS
43 SELECT pmts.payment_id,
44 pmts.payee_party_id,
45 pmts.payee_le_registration_num,
46 pmts.internal_bank_account_id,
47 pmts.int_bank_account_name,
48 pmts.int_bank_acct_agency_loc_code,
49 pmts.external_bank_account_id,
50 ext_ba.bank_account_name ext_bank_account_name,
51 iby_ext_bankacct_pub.Uncipher_Bank_Number (ext_ba.bank_account_num,
52 ext_ba.ba_num_sec_segment_id,
53 iby_utility_pvt.get_view_param('SYS_KEY'),
54 baek.subkey_cipher_text,
55 baes.segment_cipher_text,
56 baes.encoding_scheme,
57 ext_ba.ba_mask_setting,
58 ext_ba.ba_unmask_length) ext_bank_account_number,
59 pmts.ext_branch_number,
60 pmts.ext_bank_account_type
61 FROM iby_payments_all pmts,
62 iby_ext_bank_accounts ext_ba,
63 IBY_SYS_SECURITY_SUBKEYS baek,
64 IBY_SECURITY_SEGMENTS baes
65 WHERE
66 pmts.payment_instruction_id = p_instruction_id
67 AND pmts.payment_status = 'INSTRUCTION_CREATED'
68 AND pmts.external_bank_account_id = ext_ba.ext_bank_account_id(+)
69 AND ext_ba.ba_num_sec_segment_id = baes.sec_segment_id(+)
70 AND baes.sec_subkey_id = baek.sec_subkey_id(+);
71
72 -- Pick Up Required Data From Payable Documents (IBY_DOCS_PAYABLE_ALL) Using Payment Id From Payment Data
73 CURSOR iby_docs_csr(p_pmt_id IBY_DOCS_PAYABLE_ALL.payment_id%TYPE) IS
74 SELECT calling_app_doc_unique_ref2
75 FROM iby_docs_payable_all
76 WHERE
77 payment_id = p_pmt_id
78 AND
79 calling_app_id=200
80 AND
81 UPPER(payment_function) IN ('PAYABLES_DISB','EMPLOYEE_EXP')
82 AND document_status = 'PAYMENT_CREATED';
83
84
85
86 -- Declaring Record Types Of Various Cursors
87 l_pay_instr_rec pay_instr_data_csr%ROWTYPE;
88 l_iby_pmt_rec iby_pmt_csr%ROWTYPE;
89 l_iby_docs_rec iby_docs_csr%ROWTYPE;
90
91
92
93 l_format_name VARCHAR2(50);
94 l_error_message VARCHAR2(1000);
95 l_valid NUMBER;
96
97 BEGIN
98 l_format_name := 'FVBLCCDP';
99 x_result:=g_SUCCESS;
100
101 -- Initializing the payment record
102 IBY_VALIDATIONSETS_PUB.initInstructionData(p_instruction_id,l_instruction_rec);
103
104 l_docErrorRec.validation_set_code := p_validation_set_code;
105 l_docErrorRec.transaction_id := p_instruction_id;
106 l_docErrorRec.transaction_type := 'PAYMENT_INSTRUCTION';
107 l_docErrorRec.calling_app_doc_unique_ref1 := p_instruction_id;
108
109
110 OPEN pay_instr_data_csr(p_instruction_id); -- Opening Instruction Data Cursor
111 FETCH pay_instr_data_csr INTO l_pay_instr_rec; -- Getting Instruction Data
112
113
114 IF pay_instr_data_csr%FOUND THEN -- If Row Found Then Only Process Further
115
116 -- delete from FV_TP_TS_AMT_DATA to refresh data
117 delete from FV_TP_TS_AMT_DATA where payment_instruction_id = p_instruction_id;
118
119 -- Do validation of the Federal Identification Number
120 IBY_PAYMENT_FORMAT_VAL_PVT.FEDERAL_ID_NUMBER(
121 l_format_name,
122 p_instruction_id,
123 l_docErrorTab,
124 l_docErrorRec,
125 l_valid,
126 l_error_message);
127
128 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
129 x_result:=1;
130 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
131 x_result:=1;
132 RETURN;
133 END IF;
134
135 -- Do validation of Agency Id Abbreviation
136 IBY_PAYMENT_FORMAT_VAL_PVT.AGENCY_ID_ABBREVIATION(
137 l_format_name,
138 p_instruction_id,
139 l_docErrorTab,
140 l_docErrorRec,
141 l_valid,
142 l_error_message);
143
144 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
145 x_result:=1;
146 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
147 x_result:=1;
148 RETURN;
149 END IF;
150
151 -- Do IBY_PAYMENT_FORMAT_VAL_PVT.SUPPLIER_TYPE at Payment Instruction Level
152 -- Moved this to instruction level (Bug 5526640)
153 IBY_PAYMENT_FORMAT_VAL_PVT.SUPPLIER_TYPE(l_format_name,
154 p_instruction_id,
155 l_docErrorTab,
156 l_docErrorRec,
157 l_valid,
158 l_error_message);
159
160 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
161 x_result:=1;
162 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
163 x_result:=1;
164 RETURN;
165 END IF;
166
167 OPEN iby_pmt_csr(p_instruction_id); -- Opening Payment Data Cursor
168 LOOP -- Perform Validation For Each Of record in IBY_PAYMENTS_ALL
169
170 FETCH iby_pmt_csr INTO l_iby_pmt_rec; -- Getting Payment Data
171 EXIT WHEN iby_pmt_csr%NOTFOUND;
172
173 IBY_PAYMENT_FORMAT_VAL_PVT.RFC_ID(l_format_name,
174 l_iby_pmt_rec.payment_id,
175 l_docErrorTab,
176 l_docErrorRec,
177 l_valid,
178 l_error_message);
179
180 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
181 x_result:=1;
182 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
183 x_result:=1;
184 RETURN;
185 END IF;
186
187
188
189 OPEN iby_docs_csr(l_iby_pmt_rec.payment_id); -- Opening Payable Documents Data
190 LOOP -- Perform Validation For Each Of record in IBY_DOCS_PAYABLE_ALL
191
192 FETCH iby_docs_csr INTO l_iby_docs_rec; -- Getting Payable Documents Data
193 EXIT WHEN iby_docs_csr%NOTFOUND;
194
195 -- validate internal bank account parameters
196 IBY_PAYMENT_FORMAT_VAL_PVT.AGENCY_LOCATION_CODE(l_format_name,
197 l_iby_pmt_rec.int_bank_acct_agency_loc_code,
198 l_iby_pmt_rec.int_bank_account_name,
199 l_docErrorTab,
200 l_docErrorRec,
201 l_valid,
202 l_error_message);
203 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
204 x_result:=1;
205 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
206 x_result:=1;
207 RETURN;
208 END IF;
209
210
211 -- validate external bank account parameters
212 -- validate external bank account id
213 IBY_PAYMENT_FORMAT_VAL_PVT.EXTERNAL_BANK_ACCOUNT_ID(l_format_name,
214 l_iby_pmt_rec.external_bank_account_id,
215 l_docErrorTab,
216 l_docErrorRec,
217 l_valid,
218 l_error_message);
219 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
220 x_result:=1;
221 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
222 x_result:=1;
223 RETURN;
224 END IF;
225
226
227 IBY_PAYMENT_FORMAT_VAL_PVT.ACCOUNT_TYPE(l_format_name,
228 l_iby_pmt_rec.ext_bank_account_type,
229 l_iby_pmt_rec.ext_bank_account_name,
230 TO_NUMBER(l_iby_docs_rec.calling_app_doc_unique_ref2),
231 l_iby_pmt_rec.payment_id,
232 l_docErrorTab,
233 l_docErrorRec,
234 l_valid,
235 l_error_message);
236 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
237 x_result:=1;
238 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
239 x_result:=1;
240 RETURN;
241 END IF;
242
243
244 IBY_PAYMENT_FORMAT_VAL_PVT.DEPOSITER_ACC_NUM(l_format_name,
245 l_iby_pmt_rec.ext_bank_account_number,
246 l_docErrorTab,
247 l_docErrorRec,
248 l_valid,
249 l_error_message);
250 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
251 x_result:=1;
252 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
253 x_result:=1;
254 RETURN;
255 END IF;
256
257
258 IBY_PAYMENT_FORMAT_VAL_PVT.RTN_NUMBER(l_format_name,
259 l_iby_pmt_rec.ext_branch_number,
260 l_docErrorTab,
261 l_docErrorRec,
262 l_valid,
263 l_error_message);
264 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
265 x_result:=1;
266 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
267 x_result:=1;
268 RETURN;
269 END IF;
270
271
272
273 IBY_PAYMENT_FORMAT_VAL_PVT.PAY_ALONE_OPTION(l_format_name,
274 TO_NUMBER(l_iby_docs_rec.calling_app_doc_unique_ref2),
275 l_iby_pmt_rec.payment_id,
276 l_docErrorTab,
277 l_docErrorRec,
278 l_valid,
279 l_error_message);
280 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
281 x_result:=1;
282 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
283 x_result:=1;
284 RETURN;
285 END IF;
286
287
288 IBY_PAYMENT_FORMAT_VAL_PVT.TREASURY_SYMBOLS_PROCESS(l_format_name,
289 p_instruction_id,
290 l_iby_pmt_rec.payment_id,
291 TO_NUMBER(l_iby_docs_rec.calling_app_doc_unique_ref2),
292 l_docErrorTab,
293 l_docErrorRec,
294 l_valid,
295 l_error_message);
296
297 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
298 x_result:=1;
299 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
300 x_result:=1;
301 RETURN;
302 END IF;
303
304
305
306 IBY_PAYMENT_FORMAT_VAL_PVT.PAYEE_SSN(l_format_name,
307 l_iby_pmt_rec.payee_le_registration_num,
308 l_iby_pmt_rec.payee_party_id,
309 l_docErrorTab,
310 l_docErrorRec,
311 l_valid,
312 l_error_message);
313
314 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
315 x_result:=1;
316 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
317 x_result:=1;
318 RETURN;
319 END IF;
320
321
322
323
324 END LOOP;-- End Of Documents Cursor Loop
325 CLOSE iby_docs_csr;-- Closing Payable Documents Cursor
326
327 END LOOP;-- End Of Payments Cursor Loop
328 CLOSE iby_pmt_csr; -- Closing Payments Cursor
329
330 IBY_PAYMENT_FORMAT_VAL_PVT.MAX_TREASURY_SYMBOLS(l_format_name,
331 p_instruction_id,
332 l_docErrorTab,
333 l_docErrorRec,
334 l_valid,
335 l_error_message);
336
337 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
338 x_result:=1;
339 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
340 x_result:=1;
341 RETURN;
342 END IF;
343
344 IBY_PAYMENT_FORMAT_VAL_PVT.SCHEDULE_NUMBER(l_format_name,
345 p_instruction_id,
346 l_docErrorTab,
347 l_docErrorRec,
348 l_valid,
349 l_error_message);
350
351 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
352 x_result:=1;
353 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
354 x_result:=1;
355 RETURN;
356 END IF;
357
358
359
360 END IF; -- End of IF pay_instr_data_csr%FOUND
361 CLOSE pay_instr_data_csr; -- Closing Instruction Data Cursor
362
363 IBY_VALIDATIONSETS_PUB.insert_transaction_errors('N',l_docErrorTab);
364
365 EXCEPTION
366 WHEN OTHERS THEN
367 x_result := 1;
368 l_docErrorRec.transaction_error_id := null;
369 l_docErrorRec.error_code := 'UNEXPECTED_ERROR';
370 l_docErrorRec.error_message := SQLERRM;
371 IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(l_docErrorRec, l_docErrorTab);
372 iby_payment_format_val_pvt.log_error_messages(FND_LOG.LEVEL_STATEMENT, l_format_name, SQLERRM);
373 IBY_VALIDATIONSETS_PUB.insert_transaction_errors('N',l_docErrorTab);
374
375 END FVBLCCDP; -- End of Procedure FVBLCCDP
376
377 ----------------------------------------------------------------------------------------------------------
378 /*
379
380 PROCEDURE : FVBLNCR
381
382 Bulk Data NCR Payment Format Report
383
384 */
385
386 PROCEDURE FVBLNCR
387 (
388 p_validation_assign_id IN IBY_VAL_ASSIGNMENTS.validation_assignment_id%TYPE,
389 p_validation_set_code IN IBY_VALIDATION_SETS_VL.validation_set_code%TYPE,
390 p_instruction_id IN IBY_PAY_INSTRUCTIONS_ALL.payment_instruction_id%TYPE,
391 p_is_online_val IN VARCHAR2,
392 x_result OUT NOCOPY NUMBER
393 )IS
394
395 l_instruction_rec IBY_VALIDATIONSETS_PUB.instructionRecType;
396 l_docErrorTab IBY_VALIDATIONSETS_PUB.docErrorTabType;
397 l_docErrorRec IBY_TRANSACTION_ERRORS%ROWTYPE;
398
399 -- Pick Up The Required Data From Instructions (IBY_PAY_INSTRUCTIONS_ALL) using payemnt_instruction_id
400 CURSOR pay_instr_data_csr(p_instruction_id IBY_PAY_INSTRUCTIONS_ALL.payment_instruction_id%TYPE) IS
401 SELECT org_id,
402 payment_reason_code
403 FROM iby_pay_instructions_all
404 WHERE
405 payment_instruction_id = p_instruction_id;
406 -- Pick Up Required Data From Payments (IBY_PAYMENTS_ALL) using payment_instruction_id
407 CURSOR iby_pmt_csr(p_instruction_id IBY_PAY_INSTRUCTIONS_ALL.payment_instruction_id%TYPE) IS
408 SELECT payment_id,
409 payee_party_id,
410 payee_le_registration_num,
411 payment_instruction_id,
412 payment_amount,
413 internal_bank_account_id,
414 int_bank_account_name,
415 int_bank_acct_agency_loc_code
416 FROM iby_payments_all
417 WHERE payment_instruction_id = p_instruction_id
418 AND payment_status = 'INSTRUCTION_CREATED';
419
420 -- Pick Up Required Data From Payable Documents (IBY_DOCS_PAYABLE_ALL) Using Payment Id From Payment Data
421 CURSOR iby_docs_csr(p_pmt_id IBY_DOCS_PAYABLE_ALL.payment_id%TYPE) IS
422 SELECT calling_app_doc_unique_ref2
423 FROM iby_docs_payable_all
424 WHERE
425 payment_id = p_pmt_id
426 AND
427 calling_app_id=200
428 AND
429 UPPER(payment_function) IN ('PAYABLES_DISB','EMPLOYEE_EXP')
430 AND document_status = 'PAYMENT_CREATED';
431
432
433 -- Declaring Record Types Of Various Cursors
434 l_pay_instr_rec pay_instr_data_csr%ROWTYPE;
435 l_iby_pmt_rec iby_pmt_csr%ROWTYPE;
436 l_iby_docs_rec iby_docs_csr%ROWTYPE;
437 l_org_id NUMBER;
438
439 l_format_name VARCHAR2(50);
440 l_error_message VARCHAR2(1000);
441 l_valid NUMBER;
442 l_invoice_type IBY_DOCS_PAYABLE_ALL.DOCUMENT_TYPE%TYPE;
443
444
445 BEGIN
446 l_format_name := 'FVBLNCR';
447 x_result:=g_SUCCESS;
448
449 -- Initializing the payment record
450 IBY_VALIDATIONSETS_PUB.initInstructionData(p_instruction_id,l_instruction_rec);
451
452 l_docErrorRec.validation_set_code := p_validation_set_code;
453 l_docErrorRec.transaction_id := p_instruction_id;
454 l_docErrorRec.transaction_type := 'PAYMENT_INSTRUCTION';
455 l_docErrorRec.calling_app_doc_unique_ref1 := p_instruction_id;
456
457 OPEN pay_instr_data_csr(p_instruction_id); -- Opening Instruction Data Cursor
458 FETCH pay_instr_data_csr INTO l_pay_instr_rec; -- Getting Instruction Data
459
460 IF pay_instr_data_csr%FOUND THEN -- If Row Found Then Only Process Further
461
462 -- delete from FV_TP_TS_AMT_DATA to refresh data
463 delete from FV_TP_TS_AMT_DATA where payment_instruction_id = p_instruction_id;
464
465 l_org_id:=l_pay_instr_rec.org_id;-- Extracting Org_Id
466
467 -- Do validation of Agency Id Abbreviation
468 IBY_PAYMENT_FORMAT_VAL_PVT.AGENCY_ID_ABBREVIATION(
469 l_format_name,
470 p_instruction_id,
471 l_docErrorTab,
472 l_docErrorRec,
473 l_valid,
474 l_error_message);
475
476 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
477 x_result:=1;
478 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
479 x_result:=1;
480 RETURN;
481 END IF;
482
483
484 -- Do IBY_PAYMENT_FORMAT_VAL_PVT.SUPPLIER_TYPE at Payment Instruction Level
485 -- Moved this to instruction level (Bug 5526640)
486 IBY_PAYMENT_FORMAT_VAL_PVT.SUPPLIER_TYPE(l_format_name,
487 p_instruction_id,
488 l_docErrorTab,
489 l_docErrorRec,
490 l_valid,
491 l_error_message);
492
493 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
494 x_result:=1;
495 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
496 x_result:=1;
497 RETURN;
498 END IF;
499
500
501 OPEN iby_pmt_csr(p_instruction_id); -- Opening Payment Data Cursor
502 LOOP -- Perform Validation For Each Of record in IBY_PAYMENTS_ALL
503 FETCH iby_pmt_csr INTO l_iby_pmt_rec; -- Getting Payment Data
504 EXIT WHEN iby_pmt_csr%NOTFOUND;
505
506 IBY_PAYMENT_FORMAT_VAL_PVT.RFC_ID(l_format_name,
507 l_iby_pmt_rec.payment_id,
508 l_docErrorTab,
509 l_docErrorRec,
510 l_valid,
511 l_error_message);
512
513 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
514 x_result:=1;
515 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
516 x_result:=1;
517 RETURN;
518 END IF;
519
520 -- Do IBY_PAYMENT_FORMAT_VAL_PVT.MAX_PAYMENT_AMT at Payment Level
521 IBY_PAYMENT_FORMAT_VAL_PVT.MAX_PAYMENT_AMT(l_format_name,
522 l_iby_pmt_rec.payment_instruction_id,
523 l_iby_pmt_rec.payment_amount,
524 l_docErrorTab,
525 l_docErrorRec,
526 l_valid,
527 l_error_message);
528
529 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
530 x_result:=1;
531 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
532 x_result:=1;
533 RETURN;
534 END IF;
535
536
537
538 IBY_PAYMENT_FORMAT_VAL_PVT.AGENCY_ADDRESS(l_format_name,
539 l_org_id,
540 l_docErrorTab,
541 l_docErrorRec,
542 l_valid,
543 l_error_message);
544 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
545 x_result:=1;
546 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
547 x_result:=1;
548 RETURN;
549 END IF;
550
551
552 IBY_PAYMENT_FORMAT_VAL_PVT.PAYEE_ADDRESS(l_format_name,
553 l_iby_pmt_rec.payment_id,
554 l_docErrorTab,
555 l_docErrorRec,
556 l_valid,
557 l_error_message);
558 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
559 x_result:=1;
560 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
561 x_result:=1;
562 RETURN;
563 END IF;
564
565
566
567
568 OPEN iby_docs_csr(l_iby_pmt_rec.payment_id); -- Opening Payable Documents Data
569 LOOP -- Perform Validation For Each Of record in IBY_DOCS_PAYABLE_ALL
570 FETCH iby_docs_csr INTO l_iby_docs_rec; -- Getting Payable Documents Data
571 EXIT WHEN iby_docs_csr%NOTFOUND;
572
573 -- validate internal bank account parameters
574 IBY_PAYMENT_FORMAT_VAL_PVT.AGENCY_LOCATION_CODE(l_format_name,
575 l_iby_pmt_rec.int_bank_acct_agency_loc_code,
576 l_iby_pmt_rec.int_bank_account_name,
577 l_docErrorTab,
578 l_docErrorRec,
579 l_valid,
580 l_error_message);
581 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
582 x_result:=1;
583 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
584 x_result:=1;
585 RETURN;
586 END IF;
587
588 SELECT document_type
589 INTO l_invoice_type FROM IBY_DOCS_PAYABLE_ALL
590 WHERE calling_app_doc_unique_ref2 = l_iby_docs_rec.calling_app_doc_unique_ref2
591 AND payment_id = l_iby_pmt_rec.payment_id
592 AND calling_app_id=200
593 AND UPPER(payment_function) IN ('PAYABLES_DISB','EMPLOYEE_EXP');
594
595 -- Do Validation by IBY_PAYMENT_FORMAT_VAL_PVT.PAY_TAX_BENEFIT at Instruction Level
596 IF(l_invoice_type <> 'INTEREST') THEN
597 IBY_PAYMENT_FORMAT_VAL_PVT.PAY_TAX_BENEFIT(l_format_name,
598 l_iby_pmt_rec.payment_id,
599 to_number(l_iby_docs_rec.calling_app_doc_unique_ref2),
600 l_docErrorTab,
601 l_docErrorRec,
602 l_valid,
603 l_error_message);
604 END IF;
605 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
606 x_result:=1;
607 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
608 x_result:=1;
609 RETURN;
610 END IF;
611
612
613 IBY_PAYMENT_FORMAT_VAL_PVT.TREASURY_SYMBOLS_PROCESS(l_format_name,
614 p_instruction_id,
615 l_iby_pmt_rec.payment_id,
616 TO_NUMBER(l_iby_docs_rec.calling_app_doc_unique_ref2),
617 l_docErrorTab,
618 l_docErrorRec,
619 l_valid,
620 l_error_message);
621 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
622 x_result:=1;
623 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
624 x_result:=1;
625 RETURN;
626 END IF;
627
628
629
630 IBY_PAYMENT_FORMAT_VAL_PVT.PAYEE_SSN(l_format_name,
631 l_iby_pmt_rec.payee_le_registration_num,
632 l_iby_pmt_rec.payee_party_id,
633 l_docErrorTab,
634 l_docErrorRec,
635 l_valid,
636 l_error_message);
637 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
638 x_result:=1;
639 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
640 x_result:=1;
641 RETURN;
642 END IF;
643
644
645
646 END LOOP;-- End Of Documents Cursor Loop
647 CLOSE iby_docs_csr;-- Closing Payable Documents Cursor
648
649 END LOOP;-- End Of Payments Cursor Loop
650 CLOSE iby_pmt_csr; -- Closing Payments Cursor
651
652 IBY_PAYMENT_FORMAT_VAL_PVT.MAX_TREASURY_SYMBOLS(l_format_name,
653 p_instruction_id,
654 l_docErrorTab,
655 l_docErrorRec,
656 l_valid,
657 l_error_message);
658
659 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
660 x_result:=1;
661 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
662 x_result:=1;
663 RETURN;
664 END IF;
665
666 IBY_PAYMENT_FORMAT_VAL_PVT.SCHEDULE_NUMBER(l_format_name,
667 p_instruction_id,
668 l_docErrorTab,
669 l_docErrorRec,
670 l_valid,
671 l_error_message);
672
673 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
674 x_result:=1;
675 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
676 x_result:=1;
677 RETURN;
678 END IF;
679
680
681 END IF; -- End of IF pay_instr_data_csr%FOUND
682 CLOSE pay_instr_data_csr; -- Closing Instruction Data Cursor
683
684 IBY_VALIDATIONSETS_PUB.insert_transaction_errors('N',l_docErrorTab);
685
686 EXCEPTION
687 WHEN OTHERS THEN
688 x_result := 1;
689 l_docErrorRec.transaction_error_id := null;
690 l_docErrorRec.error_code := 'UNEXPECTED_ERROR';
691 l_docErrorRec.error_message := SQLERRM;
692 IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(l_docErrorRec, l_docErrorTab);
693 iby_payment_format_val_pvt.log_error_messages(FND_LOG.LEVEL_STATEMENT, l_format_name, SQLERRM);
694 IBY_VALIDATIONSETS_PUB.insert_transaction_errors('N',l_docErrorTab);
695
696
697
698 END FVBLNCR;-- End of Procedure FVBLNCR
699 ----------------------------------------------------------------------------------------------------------
700 /*
701
702 PROCEDURE : FVBLPPDP
703
704 Bulk Data PPDP Payment Format Report
705
706 */
707
708 PROCEDURE FVBLPPDP(
709 p_validation_assign_id IN IBY_VAL_ASSIGNMENTS.validation_assignment_id%TYPE,
710 p_validation_set_code IN IBY_VALIDATION_SETS_VL.validation_set_code%TYPE,
711 p_instruction_id IN IBY_PAY_INSTRUCTIONS_ALL.payment_instruction_id%TYPE,
712 p_is_online_val IN VARCHAR2,
713 x_result OUT NOCOPY NUMBER
714 )IS
715
716 l_instruction_rec IBY_VALIDATIONSETS_PUB.instructionRecType;
717 l_docErrorTab IBY_VALIDATIONSETS_PUB.docErrorTabType;
718 l_docErrorRec IBY_TRANSACTION_ERRORS%ROWTYPE;
719
720 -- Pick Up The Required Data From Instructions (IBY_PAY_INSTRUCTIONS_ALL) using payemnt_instruction_id
721 CURSOR pay_instr_data_csr(p_instruction_id IBY_PAY_INSTRUCTIONS_ALL.payment_instruction_id%TYPE) IS
722 SELECT org_id,
723 payment_reason_code
724 FROM iby_pay_instructions_all
725 WHERE
726 payment_instruction_id = p_instruction_id;
727
728 -- Pick Up Required Data From Payments (IBY_PAYMENTS_ALL) using payment_instruction_id
729 CURSOR iby_pmt_csr(p_instruction_id IBY_PAY_INSTRUCTIONS_ALL.payment_instruction_id%TYPE) IS
730 SELECT pmts.payment_id,
731 pmts.payment_instruction_id,
732 pmts.payee_party_id,
733 pmts.payee_le_registration_num,
734 pmts.payment_amount,
735 pmts.internal_bank_account_id,
736 pmts.int_bank_account_name,
737 pmts.int_bank_acct_agency_loc_code,
738 pmts.external_bank_account_id,
739 ext_ba.bank_account_name ext_bank_account_name,
740 iby_ext_bankacct_pub.Uncipher_Bank_Number (ext_ba.bank_account_num,
741 ext_ba.ba_num_sec_segment_id,
742 iby_utility_pvt.get_view_param('SYS_KEY'),
743 baek.subkey_cipher_text,
744 baes.segment_cipher_text,
745 baes.encoding_scheme,
746 ext_ba.ba_mask_setting,
747 ext_ba.ba_unmask_length) ext_bank_account_number,
748 pmts.ext_branch_number,
749 pmts.ext_bank_account_type
750 FROM iby_payments_all pmts,
751 iby_ext_bank_accounts ext_ba,
752 IBY_SYS_SECURITY_SUBKEYS baek,
753 IBY_SECURITY_SEGMENTS baes
754 WHERE
755 pmts.payment_instruction_id = p_instruction_id
756 AND pmts.payment_status = 'INSTRUCTION_CREATED'
757 AND pmts.external_bank_account_id = ext_ba.ext_bank_account_id(+)
758 AND ext_ba.ba_num_sec_segment_id = baes.sec_segment_id(+)
759 AND baes.sec_subkey_id = baek.sec_subkey_id(+);
760
761 -- Pick Up Required Data From Payable Documents (IBY_DOCS_PAYABLE_ALL) Using Payment Id From Payment Data
762 CURSOR iby_docs_csr(p_pmt_id IBY_DOCS_PAYABLE_ALL.payment_id%TYPE) IS
763 SELECT calling_app_doc_unique_ref2
764 FROM iby_docs_payable_all
765 WHERE
766 payment_id = p_pmt_id
767 AND
768 calling_app_id=200
769 AND
770 UPPER(payment_function) IN ('PAYABLES_DISB','EMPLOYEE_EXP')
771 AND document_status = 'PAYMENT_CREATED';
772
773
774
775 -- Declaring Record Types Of Various Cursors
776 l_pay_instr_rec pay_instr_data_csr%ROWTYPE;
777 l_iby_pmt_rec iby_pmt_csr%ROWTYPE;
778 l_iby_docs_rec iby_docs_csr%ROWTYPE;
779 l_format_name VARCHAR2(50);
780 l_error_message VARCHAR2(1000);
781 l_valid NUMBER;
782
783 BEGIN
784 l_format_name := 'FVBLPPDP';
785 x_result:=g_SUCCESS;
786
787 -- Initializing the payment record
788 IBY_VALIDATIONSETS_PUB.initInstructionData(p_instruction_id,l_instruction_rec);
789
790 l_docErrorRec.validation_set_code := p_validation_set_code;
791 l_docErrorRec.transaction_id := p_instruction_id;
792 l_docErrorRec.transaction_type := 'PAYMENT_INSTRUCTION';
793 l_docErrorRec.calling_app_doc_unique_ref1 := p_instruction_id;
794
795 OPEN pay_instr_data_csr(p_instruction_id); -- Opening Instruction Data Cursor
796 FETCH pay_instr_data_csr INTO l_pay_instr_rec; -- Getting Instruction Data
797
798 IF pay_instr_data_csr%FOUND THEN -- If Row Found Then Only Process Further
799
800 -- delete from FV_TP_TS_AMT_DATA to refresh data
801 delete from FV_TP_TS_AMT_DATA where payment_instruction_id = p_instruction_id;
802
803 -- Do validation of the Federal Identification Number
804 IBY_PAYMENT_FORMAT_VAL_PVT.FEDERAL_ID_NUMBER(
805 l_format_name,
806 p_instruction_id,
807 l_docErrorTab,
808 l_docErrorRec,
809 l_valid,
810 l_error_message);
811
812 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
813 x_result:=1;
814 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
815 x_result:=1;
816 RETURN;
817 END IF;
818
819 -- Do validation of Agency Id Abbreviation
820 IBY_PAYMENT_FORMAT_VAL_PVT.AGENCY_ID_ABBREVIATION(
821 l_format_name,
822 p_instruction_id,
823 l_docErrorTab,
824 l_docErrorRec,
825 l_valid,
826 l_error_message);
827
828 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
829 x_result:=1;
830 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
831 x_result:=1;
832 RETURN;
833 END IF;
834
835
836 -- Do IBY_PAYMENT_FORMAT_VAL_PVT.SUPPLIER_TYPE at Payment Instruction Level
837 -- Moved this to instruction level (Bug 5526640)
838 IBY_PAYMENT_FORMAT_VAL_PVT.SUPPLIER_TYPE(l_format_name,
839 p_instruction_id,
840 l_docErrorTab,
841 l_docErrorRec,
842 l_valid,
843 l_error_message);
844
845 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
846 x_result:=1;
847 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
848 x_result:=1;
849 RETURN;
850 END IF;
851
852
853 OPEN iby_pmt_csr(p_instruction_id); -- Opening Payment Data Cursor
854
855 LOOP -- Perform Validation For Each Of record in IBY_PAYMENTS_ALL
856 FETCH iby_pmt_csr INTO l_iby_pmt_rec; -- Getting Payment Data
857 EXIT WHEN iby_pmt_csr%NOTFOUND;
858
859 IBY_PAYMENT_FORMAT_VAL_PVT.RFC_ID(l_format_name,
860 l_iby_pmt_rec.payment_id,
861 l_docErrorTab,
862 l_docErrorRec,
863 l_valid,
864 l_error_message);
865
866 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
867 x_result:=1;
868 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
869 x_result:=1;
870 RETURN;
871 END IF;
872
873 -- Do IBY_PAYMENT_FORMAT_VAL_PVT.MAX_PAYMENT_AMT_2 at Payment Level
874 IBY_PAYMENT_FORMAT_VAL_PVT.MAX_PAYMENT_AMT_2(l_format_name,
875 l_iby_pmt_rec.payment_instruction_id,
876 l_iby_pmt_rec.payment_amount,
877 l_docErrorTab,
878 l_docErrorRec,
879 l_valid,
880 l_error_message);
881
882 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
883 x_result:=1;
884 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
885 x_result:=1;
886 RETURN;
887 END IF;
888
889
890 OPEN iby_docs_csr(l_iby_pmt_rec.payment_id); -- Opening Payable Documents Data
891 LOOP -- Perform Validation For Each Of record in IBY_DOCS_PAYABLE_ALL
892 FETCH iby_docs_csr INTO l_iby_docs_rec; -- Getting Payable Documents Data
893 EXIT WHEN iby_docs_csr%NOTFOUND;
894
895 -- validate internal bank account parameters
896 IBY_PAYMENT_FORMAT_VAL_PVT.AGENCY_LOCATION_CODE(l_format_name,
897 l_iby_pmt_rec.int_bank_acct_agency_loc_code,
898 l_iby_pmt_rec.int_bank_account_name,
899 l_docErrorTab,
900 l_docErrorRec,
901 l_valid,
902 l_error_message);
903
904 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
905 x_result:=1;
906 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
907 x_result:=1;
908 RETURN;
909 END IF;
910
911 -- validate external bank account parameters
912 -- validate external bank account id
913 IBY_PAYMENT_FORMAT_VAL_PVT.EXTERNAL_BANK_ACCOUNT_ID(l_format_name,
914 l_iby_pmt_rec.external_bank_account_id,
915 l_docErrorTab,
916 l_docErrorRec,
917 l_valid,
918 l_error_message);
919 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
920 x_result:=1;
921 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
922 x_result:=1;
923 RETURN;
924 END IF;
925
926
927 IBY_PAYMENT_FORMAT_VAL_PVT.ACCOUNT_TYPE(l_format_name,
928 l_iby_pmt_rec.ext_bank_account_type,
929 l_iby_pmt_rec.ext_bank_account_name,
930 TO_NUMBER(l_iby_docs_rec.calling_app_doc_unique_ref2),
931 l_iby_pmt_rec.payment_id,
932 l_docErrorTab,
933 l_docErrorRec,
934 l_valid,
935 l_error_message);
936 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
937 x_result:=1;
938 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
939 x_result:=1;
940 RETURN;
941 END IF;
942
943
944 IBY_PAYMENT_FORMAT_VAL_PVT.DEPOSITER_ACC_NUM(l_format_name,
945 l_iby_pmt_rec.ext_bank_account_number,
946 l_docErrorTab,
947 l_docErrorRec,
948 l_valid,
949 l_error_message);
950 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
951 x_result:=1;
952 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
953 x_result:=1;
954 RETURN;
955 END IF;
956
957
958 IBY_PAYMENT_FORMAT_VAL_PVT.RTN_NUMBER(l_format_name,
959 l_iby_pmt_rec.ext_branch_number,
960 l_docErrorTab,
961 l_docErrorRec,
962 l_valid,
963 l_error_message);
964 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
965 x_result:=1;
966 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
967 x_result:=1;
968 RETURN;
969 END IF;
970
971
972
973
974 IBY_PAYMENT_FORMAT_VAL_PVT.PAY_ALONE_OPTION(l_format_name,
975 TO_NUMBER(l_iby_docs_rec.calling_app_doc_unique_ref2),
976 l_iby_pmt_rec.payment_id,
977 l_docErrorTab,
978 l_docErrorRec,
979 l_valid,
980 l_error_message);
981 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
982 x_result:=1;
983 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
984 x_result:=1;
985 RETURN;
986 END IF;
987
988
989 IBY_PAYMENT_FORMAT_VAL_PVT.TREASURY_SYMBOLS_PROCESS(l_format_name,
990 p_instruction_id,
991 l_iby_pmt_rec.payment_id,
992 TO_NUMBER(l_iby_docs_rec.calling_app_doc_unique_ref2),
993 l_docErrorTab,
994 l_docErrorRec,
995 l_valid,
996 l_error_message);
997 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
998 x_result:=1;
999 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
1000 x_result:=1;
1001 RETURN;
1002 END IF;
1003
1004
1005 IBY_PAYMENT_FORMAT_VAL_PVT.PAYEE_SSN(l_format_name,
1006 l_iby_pmt_rec.payee_le_registration_num,
1007 l_iby_pmt_rec.payee_party_id,
1008 l_docErrorTab,
1009 l_docErrorRec,
1010 l_valid,
1011 l_error_message);
1012 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
1013 x_result:=1;
1014 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
1015 x_result:=1;
1016 RETURN;
1017 END IF;
1018
1019
1020 END LOOP;-- End Of Documents Cursor Loop
1021 CLOSE iby_docs_csr;-- Closing Payable Documents Cursor
1022
1023 END LOOP;-- End Of Payments Cursor Loop
1024 CLOSE iby_pmt_csr; -- Closing Payments Cursor
1025
1026 IBY_PAYMENT_FORMAT_VAL_PVT.MAX_TREASURY_SYMBOLS(l_format_name,
1027 p_instruction_id,
1028 l_docErrorTab,
1029 l_docErrorRec,
1030 l_valid,
1031 l_error_message);
1032
1033 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
1034 x_result:=1;
1035 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
1036 x_result:=1;
1037 RETURN;
1038 END IF;
1039
1040 IBY_PAYMENT_FORMAT_VAL_PVT.SCHEDULE_NUMBER(l_format_name,
1041 p_instruction_id,
1042 l_docErrorTab,
1043 l_docErrorRec,
1044 l_valid,
1045 l_error_message);
1046
1047 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
1048 x_result:=1;
1049 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
1050 x_result:=1;
1051 RETURN;
1052 END IF;
1053
1054 IBY_PAYMENT_FORMAT_VAL_PVT.MANDATORY_PPD_PPDP_REASON_CODE(l_format_name,
1055 l_pay_instr_rec.payment_reason_code,
1056 l_docErrorTab,
1057 l_docErrorRec,
1058 l_valid,
1059 l_error_message);
1060
1061 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
1062 x_result:=1;
1063 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
1064 x_result:=1;
1065 RETURN;
1066 END IF;
1067
1068 END IF; -- End of IF pay_instr_data_csr%FOUND
1069 CLOSE pay_instr_data_csr; -- Closing Instruction Data Cursor
1070
1071 IBY_VALIDATIONSETS_PUB.insert_transaction_errors('N',l_docErrorTab);
1072
1073 EXCEPTION
1074 WHEN OTHERS THEN
1075 x_result := 1;
1076 l_docErrorRec.transaction_error_id := null;
1077 l_docErrorRec.error_code := 'UNEXPECTED_ERROR';
1078 l_docErrorRec.error_message := SQLERRM;
1079 IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(l_docErrorRec, l_docErrorTab);
1080 iby_payment_format_val_pvt.log_error_messages(FND_LOG.LEVEL_STATEMENT, l_format_name, SQLERRM);
1081 IBY_VALIDATIONSETS_PUB.insert_transaction_errors('N',l_docErrorTab);
1082
1083
1084 END FVBLPPDP;-- End of Procedure FVBLPPDP
1085 ----------------------------------------------------------------------------------------------------------
1086 /*
1087
1088 PROCEDURE : FVBLSLTR
1089
1090 Bulk Data Salary Travel NCR Payment Format
1091
1092 */
1093
1094 PROCEDURE FVBLSLTR
1095 (
1096 p_validation_assign_id IN IBY_VAL_ASSIGNMENTS.validation_assignment_id%TYPE,
1097 p_validation_set_code IN IBY_VALIDATION_SETS_VL.validation_set_code%TYPE,
1098 p_instruction_id IN IBY_PAY_INSTRUCTIONS_ALL.payment_instruction_id%TYPE,
1099 p_is_online_val IN VARCHAR2,
1100 x_result OUT NOCOPY NUMBER
1101 )IS
1102
1103 l_instruction_rec IBY_VALIDATIONSETS_PUB.instructionRecType;
1104 l_docErrorTab IBY_VALIDATIONSETS_PUB.docErrorTabType;
1105 l_docErrorRec IBY_TRANSACTION_ERRORS%ROWTYPE;
1106
1107 -- Pick Up The Required Data From Instructions (IBY_PAY_INSTRUCTIONS_ALL) using payemnt_instruction_id
1108 CURSOR pay_instr_data_csr(p_instruction_id IBY_PAY_INSTRUCTIONS_ALL.payment_instruction_id%TYPE) IS
1109 SELECT org_id,payment_reason_code
1110 FROM iby_pay_instructions_all
1111 WHERE
1112 payment_instruction_id = p_instruction_id;
1113
1114 -- Pick Up Required Data From Payments (IBY_PAYMENTS_ALL) using payment_instruction_id
1115 CURSOR iby_pmt_csr(p_instruction_id IBY_PAY_INSTRUCTIONS_ALL.payment_instruction_id%TYPE) IS
1116 SELECT payment_id,
1117 payment_instruction_id,
1118 payee_party_id,
1119 payee_le_registration_num,
1120 payment_amount,
1121 internal_bank_account_id,
1122 int_bank_account_name,
1123 int_bank_acct_agency_loc_code
1124 FROM iby_payments_all
1125 WHERE
1126 payment_instruction_id = p_instruction_id
1127 AND payment_status = 'INSTRUCTION_CREATED';
1128 -- Pick Up Required Data From Payable Documents (IBY_DOCS_PAYABLE_ALL) Using Payment Id From Payment Data
1129 CURSOR iby_docs_csr(p_pmt_id IBY_DOCS_PAYABLE_ALL.payment_id%TYPE) IS
1130 SELECT calling_app_doc_unique_ref2
1131 FROM iby_docs_payable_all
1132 WHERE
1133 payment_id = p_pmt_id
1134 AND
1135 calling_app_id=200
1136 AND
1137 UPPER(payment_function) IN ('PAYABLES_DISB','EMPLOYEE_EXP')
1138 AND document_status = 'PAYMENT_CREATED';
1139
1140
1141
1142 -- Declaring Record Types Of Various Cursors
1143 l_pay_instr_rec pay_instr_data_csr%ROWTYPE;
1144 l_iby_pmt_rec iby_pmt_csr%ROWTYPE;
1145 l_iby_docs_rec iby_docs_csr%ROWTYPE;
1146
1147 l_org_id NUMBER;
1148 l_format_name VARCHAR2(50);
1149 l_error_message VARCHAR2(1000);
1150 l_valid NUMBER;
1151
1152 BEGIN
1153 l_format_name := 'FVBLSLTR';
1154 x_result:=g_SUCCESS;
1155
1156 -- Initializing the payment record
1157 IBY_VALIDATIONSETS_PUB.initInstructionData(p_instruction_id,l_instruction_rec);
1158
1159 l_docErrorRec.validation_set_code := p_validation_set_code;
1160 l_docErrorRec.transaction_id := p_instruction_id;
1161 l_docErrorRec.transaction_type := 'PAYMENT_INSTRUCTION';
1162 l_docErrorRec.calling_app_doc_unique_ref1 := p_instruction_id;
1163
1164 OPEN pay_instr_data_csr(p_instruction_id); -- Opening Instruction Data Cursor
1165 FETCH pay_instr_data_csr INTO l_pay_instr_rec; -- Getting Instruction Data
1166
1167 IF pay_instr_data_csr%FOUND THEN -- If Row Found Then Only Process Further
1168
1169 -- delete from FV_TP_TS_AMT_DATA to refresh data
1170 delete from FV_TP_TS_AMT_DATA where payment_instruction_id = p_instruction_id;
1171
1172 l_org_id:=l_pay_instr_rec.org_id;-- Extracting Org_ID
1173
1174 -- Do validation of Agency Id Abbreviation
1175 IBY_PAYMENT_FORMAT_VAL_PVT.AGENCY_ID_ABBREVIATION(
1176 l_format_name,
1177 p_instruction_id,
1178 l_docErrorTab,
1179 l_docErrorRec,
1180 l_valid,
1181 l_error_message);
1182
1183 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
1184 x_result:=1;
1185 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
1186 x_result:=1;
1187 RETURN;
1188 END IF;
1189
1190 -- Do Validation by IBY_PAYMENT_FORMAT_VAL_PVT.PAY_SALARY_TRAVEL at Instruction Level
1191 IBY_PAYMENT_FORMAT_VAL_PVT.PAY_SALARY_TRAVEL(l_format_name,
1192 l_pay_instr_rec.payment_reason_code,
1193 l_docErrorTab,
1194 l_docErrorRec,
1195 l_valid,
1196 l_error_message);
1197 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
1198 x_result:=1;
1199 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
1200 x_result:=1;
1201 RETURN;
1202 END IF;
1203
1204
1205 -- Do IBY_PAYMENT_FORMAT_VAL_PVT.SUPPLIER_TYPE at Payment Instruction Level
1206 -- Moved this to instruction level (Bug 5526640)
1207 IBY_PAYMENT_FORMAT_VAL_PVT.SUPPLIER_TYPE(l_format_name,
1208 p_instruction_id,
1209 l_docErrorTab,
1210 l_docErrorRec,
1211 l_valid,
1212 l_error_message);
1213
1214 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
1215 x_result:=1;
1216 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
1217 x_result:=1;
1218 RETURN;
1219 END IF;
1220
1221 OPEN iby_pmt_csr(p_instruction_id); -- Opening Payment Data Cursor
1222 LOOP -- Perform Validation For Each Of record in IBY_PAYMENTS_ALL
1223 FETCH iby_pmt_csr INTO l_iby_pmt_rec; -- Getting Payment Data
1224 EXIT WHEN iby_pmt_csr%NOTFOUND;
1225
1226 IBY_PAYMENT_FORMAT_VAL_PVT.RFC_ID(l_format_name,
1227 l_iby_pmt_rec.payment_id,
1228 l_docErrorTab,
1229 l_docErrorRec,
1230 l_valid,
1231 l_error_message);
1232
1233 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
1234 x_result:=1;
1235 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
1236 x_result:=1;
1237 RETURN;
1238 END IF;
1239
1240 IBY_PAYMENT_FORMAT_VAL_PVT.AGENCY_ADDRESS(l_format_name,
1241 l_org_id,
1242 l_docErrorTab,
1243 l_docErrorRec,
1244 l_valid,
1245 l_error_message);
1246
1247 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
1248 x_result:=1;
1249 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
1250 x_result:=1;
1251 RETURN;
1252 END IF;
1253
1254 -- Do IBY_PAYMENT_FORMAT_VAL_PVT.MAX_PAYMENT_AMT_2 at Payment Level
1255 IBY_PAYMENT_FORMAT_VAL_PVT.MAX_PAYMENT_AMT_2(l_format_name,
1256 l_iby_pmt_rec.payment_instruction_id,
1257 l_iby_pmt_rec.payment_amount,
1258 l_docErrorTab,
1259 l_docErrorRec,
1260 l_valid,
1261 l_error_message);
1262
1263 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
1264 x_result:=1;
1265 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
1266 x_result:=1;
1267 RETURN;
1268 END IF;
1269
1270
1271 OPEN iby_docs_csr(l_iby_pmt_rec.payment_id); -- Opening Payable Documents Data
1272 LOOP -- Perform Validation For Each Of record in IBY_DOCS_PAYABLE_ALL
1273 FETCH iby_docs_csr INTO l_iby_docs_rec; -- Getting Payable Documents Data
1274 EXIT WHEN iby_docs_csr%NOTFOUND;
1275
1276
1277 -- validate internal bank account parameters
1278 IBY_PAYMENT_FORMAT_VAL_PVT.AGENCY_LOCATION_CODE(l_format_name,
1279 l_iby_pmt_rec.int_bank_acct_agency_loc_code,
1280 l_iby_pmt_rec.int_bank_account_name,
1281 l_docErrorTab,
1282 l_docErrorRec,
1283 l_valid,
1284 l_error_message);
1285 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
1286 x_result:=1;
1287 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
1288 x_result:=1;
1289 RETURN;
1290 END IF;
1291
1292
1293 IBY_PAYMENT_FORMAT_VAL_PVT.TREASURY_SYMBOLS_PROCESS(l_format_name,
1294 p_instruction_id,
1295 l_iby_pmt_rec.payment_id,
1296 TO_NUMBER(l_iby_docs_rec.calling_app_doc_unique_ref2),
1297 l_docErrorTab,
1298 l_docErrorRec,
1299 l_valid,
1300 l_error_message);
1301 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
1302 x_result:=1;
1303 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
1304 x_result:=1;
1305 RETURN;
1306 END IF;
1307
1308
1309 IBY_PAYMENT_FORMAT_VAL_PVT.PAYEE_SSN(l_format_name,
1310 l_iby_pmt_rec.payee_le_registration_num,
1311 l_iby_pmt_rec.payee_party_id,
1312 l_docErrorTab,
1313 l_docErrorRec,
1314 l_valid,
1315 l_error_message);
1316 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
1317 x_result:=1;
1318 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
1319 x_result:=1;
1320 RETURN;
1321 END IF;
1322
1323
1324
1325 END LOOP;-- End Of Documents Cursor Loop
1326 CLOSE iby_docs_csr;-- Closing Payable Documents Cursor
1327
1328 END LOOP;-- End Of Payments Cursor Loop
1329 CLOSE iby_pmt_csr; -- Closing Payments Cursor
1330
1331 IBY_PAYMENT_FORMAT_VAL_PVT.MAX_TREASURY_SYMBOLS(l_format_name,
1332 p_instruction_id,
1333 l_docErrorTab,
1334 l_docErrorRec,
1335 l_valid,
1336 l_error_message);
1337
1338 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
1339 x_result:=1;
1340 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
1341 x_result:=1;
1342 RETURN;
1343 END IF;
1344
1345 IBY_PAYMENT_FORMAT_VAL_PVT.SCHEDULE_NUMBER(l_format_name,
1346 p_instruction_id,
1347 l_docErrorTab,
1348 l_docErrorRec,
1349 l_valid,
1350 l_error_message);
1351
1352 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
1353 x_result:=1;
1354 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
1355 x_result:=1;
1356 RETURN;
1357 END IF;
1358
1359
1360 END IF; -- End of IF pay_instr_data_csr%FOUND
1361 CLOSE pay_instr_data_csr; -- Closing Instruction Data Cursor
1362
1363 IBY_VALIDATIONSETS_PUB.insert_transaction_errors('N',l_docErrorTab);
1364
1365 EXCEPTION
1366 WHEN OTHERS THEN
1367
1368 x_result := 1;
1369 l_docErrorRec.transaction_error_id := null;
1370 l_docErrorRec.error_code := 'UNEXPECTED_ERROR';
1371 l_docErrorRec.error_message := SQLERRM;
1372 IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(l_docErrorRec, l_docErrorTab);
1373 iby_payment_format_val_pvt.log_error_messages(FND_LOG.LEVEL_STATEMENT, l_format_name, SQLERRM);
1374 IBY_VALIDATIONSETS_PUB.insert_transaction_errors('N',l_docErrorTab);
1375
1376
1377 END FVBLSLTR;-- End of Procedure FVBLSLTR
1378 ----------------------------------------------------------------------------------------------------------
1379 /*
1380
1381 PROCEDURE : FVTICTX
1382
1383 CTX ACH Vendor Payment Format Report
1384
1385 */
1386
1387 PROCEDURE FVTICTX
1388 (
1389 p_validation_assign_id IN IBY_VAL_ASSIGNMENTS.validation_assignment_id%TYPE,
1390 p_validation_set_code IN IBY_VALIDATION_SETS_VL.validation_set_code%TYPE,
1391 p_instruction_id IN IBY_PAY_INSTRUCTIONS_ALL.payment_instruction_id%TYPE,
1392 p_is_online_val IN VARCHAR2,
1393 x_result OUT NOCOPY NUMBER
1394 )IS
1395
1396 l_instruction_rec IBY_VALIDATIONSETS_PUB.instructionRecType;
1397 l_docErrorTab IBY_VALIDATIONSETS_PUB.docErrorTabType;
1398 l_docErrorRec IBY_TRANSACTION_ERRORS%ROWTYPE;
1399
1400 -- Pick Up The Required Data From Instructions (IBY_PAY_INSTRUCTIONS_ALL) using payemnt_instruction_id
1401 CURSOR pay_instr_data_csr(p_instruction_id IBY_PAY_INSTRUCTIONS_ALL.payment_instruction_id%TYPE) IS
1402 SELECT org_id
1403 FROM iby_pay_instructions_all
1404 WHERE
1405 payment_instruction_id = p_instruction_id;
1406
1407 -- Pick Up Required Data From Payments (IBY_PAYMENTS_ALL) using payment_instruction_id
1408 CURSOR iby_pmt_csr(p_instruction_id IBY_PAY_INSTRUCTIONS_ALL.payment_instruction_id%TYPE) IS
1409 SELECT pmts.payment_id,
1410 pmts.payment_instruction_id,
1411 pmts.payee_party_id,
1412 pmts.payee_le_registration_num,
1413 pmts.payment_amount,
1414 pmts.internal_bank_account_id,
1415 pmts.int_bank_account_name,
1416 pmts.int_bank_acct_agency_loc_code,
1417 pmts.external_bank_account_id,
1418 ext_ba.bank_account_name ext_bank_account_name,
1419 iby_ext_bankacct_pub.Uncipher_Bank_Number (ext_ba.bank_account_num,
1420 ext_ba.ba_num_sec_segment_id,
1421 iby_utility_pvt.get_view_param('SYS_KEY'),
1422 baek.subkey_cipher_text,
1423 baes.segment_cipher_text,
1424 baes.encoding_scheme,
1425 ext_ba.ba_mask_setting,
1426 ext_ba.ba_unmask_length) ext_bank_account_number,
1427 pmts.ext_branch_number,
1428 pmts.ext_bank_account_type
1429 FROM iby_payments_all pmts,
1430 iby_ext_bank_accounts ext_ba,
1431 IBY_SYS_SECURITY_SUBKEYS baek,
1432 IBY_SECURITY_SEGMENTS baes
1433 WHERE
1434 pmts.payment_instruction_id = p_instruction_id
1435 AND pmts.payment_status = 'INSTRUCTION_CREATED'
1436 AND pmts.external_bank_account_id = ext_ba.ext_bank_account_id(+)
1437 AND ext_ba.ba_num_sec_segment_id = baes.sec_segment_id(+)
1438 AND baes.sec_subkey_id = baek.sec_subkey_id(+);
1439
1440 -- Pick Up Required Data From Payable Documents (IBY_DOCS_PAYABLE_ALL) Using Payment Id From Payment Data
1441 CURSOR iby_docs_csr(p_pmt_id IBY_DOCS_PAYABLE_ALL.payment_id%TYPE) IS
1442 SELECT calling_app_doc_unique_ref2
1443 FROM iby_docs_payable_all
1444 WHERE
1445 payment_id = p_pmt_id
1446 AND
1447 calling_app_id=200
1448 AND
1449 UPPER(payment_function) IN ('PAYABLES_DISB','EMPLOYEE_EXP')
1450 AND document_status = 'PAYMENT_CREATED';
1451
1452
1453
1454 -- Declaring Record Types Of Various Cursors
1455 l_pay_instr_rec pay_instr_data_csr%ROWTYPE;
1456 l_iby_pmt_rec iby_pmt_csr%ROWTYPE;
1457 l_iby_docs_rec iby_docs_csr%ROWTYPE;
1458 l_org_id NUMBER;
1459
1460 l_format_name VARCHAR2(50);
1461 l_error_message VARCHAR2(1000);
1462 l_valid NUMBER;
1463
1464 BEGIN
1465 l_format_name := 'FVTICTX';
1466 x_result:=g_SUCCESS;
1467
1468 -- Initializing the payment record
1469 IBY_VALIDATIONSETS_PUB.initInstructionData(p_instruction_id,l_instruction_rec);
1470
1471 l_docErrorRec.validation_set_code := p_validation_set_code;
1472 l_docErrorRec.transaction_id := p_instruction_id;
1473 l_docErrorRec.transaction_type := 'PAYMENT_INSTRUCTION';
1474 l_docErrorRec.calling_app_doc_unique_ref1 := p_instruction_id;
1475
1476 OPEN pay_instr_data_csr(p_instruction_id); -- Opening Instruction Data Cursor
1477 FETCH pay_instr_data_csr INTO l_pay_instr_rec; -- Getting Instruction Data
1478
1479 IF pay_instr_data_csr%FOUND THEN -- If Row Found Then Only Process Further
1480
1481 -- delete from FV_TP_TS_AMT_DATA to refresh data
1482 delete from FV_TP_TS_AMT_DATA where payment_instruction_id = p_instruction_id;
1483
1484 l_org_id:=l_pay_instr_rec.org_id;-- Extracting Org_ID
1485
1486 -- Do validation of Agency Id Abbreviation
1487 IBY_PAYMENT_FORMAT_VAL_PVT.AGENCY_ID_ABBREVIATION(
1488 l_format_name,
1489 p_instruction_id,
1490 l_docErrorTab,
1491 l_docErrorRec,
1492 l_valid,
1493 l_error_message);
1494
1495 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
1496 x_result:=1;
1497 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
1498 x_result:=1;
1499 RETURN;
1500 END IF;
1501
1502
1503 -- Do IBY_PAYMENT_FORMAT_VAL_PVT.SUPPLIER_TYPE at Payment Instruction Level
1504 -- Moved this to instruction level (Bug 5526640)
1505 IBY_PAYMENT_FORMAT_VAL_PVT.SUPPLIER_TYPE(l_format_name,
1506 p_instruction_id,
1507 l_docErrorTab,
1508 l_docErrorRec,
1509 l_valid,
1510 l_error_message);
1511
1512 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
1513 x_result:=1;
1514 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
1515 x_result:=1;
1516 RETURN;
1517 END IF;
1518
1519 OPEN iby_pmt_csr(p_instruction_id); -- Opening Payment Data Cursor
1520 LOOP -- Perform Validation For Each Of record in IBY_PAYMENTS_ALL
1521 FETCH iby_pmt_csr INTO l_iby_pmt_rec; -- Getting Payment Data
1522 EXIT WHEN iby_pmt_csr%NOTFOUND;
1523
1524 IBY_PAYMENT_FORMAT_VAL_PVT.RFC_ID(l_format_name,
1525 l_iby_pmt_rec.payment_id,
1526 l_docErrorTab,
1527 l_docErrorRec,
1528 l_valid,
1529 l_error_message);
1530
1531 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
1532 x_result:=1;
1533 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
1534 x_result:=1;
1535 RETURN;
1536 END IF;
1537
1538 -- Do IBY_PAYMENT_FORMAT_VAL_PVT.MAX_PAYMENT_AMT at Payment Level
1539 IBY_PAYMENT_FORMAT_VAL_PVT.MAX_PAYMENT_AMT(l_format_name,
1540 l_iby_pmt_rec.payment_instruction_id,
1541 l_iby_pmt_rec.payment_amount,
1542 l_docErrorTab,
1543 l_docErrorRec,
1544 l_valid,
1545 l_error_message);
1546 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
1547 x_result:=1;
1548 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
1549 x_result:=1;
1550 RETURN;
1551 END IF;
1552
1553
1554 -- Do IBY_PAYMENT_FORMAT_VAL_PVT.AGENCY_ADDRESS
1555 IBY_PAYMENT_FORMAT_VAL_PVT.AGENCY_ADDRESS(l_format_name,
1556 l_org_id,
1557 l_docErrorTab,
1558 l_docErrorRec,
1559 l_valid,
1560 l_error_message);
1561
1562 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
1563 x_result:=1;
1564 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
1565 x_result:=1;
1566 RETURN;
1567 END IF;
1568
1569 OPEN iby_docs_csr(l_iby_pmt_rec.payment_id); -- Opening Payable Documents Data
1570 LOOP -- Perform Validation For Each Of record in IBY_DOCS_PAYABLE_ALL
1571 FETCH iby_docs_csr INTO l_iby_docs_rec; -- Getting Payable Documents Data
1572 EXIT WHEN iby_docs_csr%NOTFOUND;
1573
1574
1575 -- validate internal bank account parameters
1576 IBY_PAYMENT_FORMAT_VAL_PVT.AGENCY_LOCATION_CODE(l_format_name,
1577 l_iby_pmt_rec.int_bank_acct_agency_loc_code,
1578 l_iby_pmt_rec.int_bank_account_name,
1579 l_docErrorTab,
1580 l_docErrorRec,
1581 l_valid,
1582 l_error_message);
1583 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
1584 x_result:=1;
1585 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
1586 x_result:=1;
1587 RETURN;
1588 END IF;
1589
1590 -- validate external bank account parameters
1591 -- validate external bank account id
1592 IBY_PAYMENT_FORMAT_VAL_PVT.EXTERNAL_BANK_ACCOUNT_ID(l_format_name,
1593 l_iby_pmt_rec.external_bank_account_id,
1594 l_docErrorTab,
1595 l_docErrorRec,
1596 l_valid,
1597 l_error_message);
1598 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
1599 x_result:=1;
1600 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
1601 x_result:=1;
1602 RETURN;
1603 END IF;
1604
1605 IBY_PAYMENT_FORMAT_VAL_PVT.ACCOUNT_TYPE(l_format_name,
1606 l_iby_pmt_rec.ext_bank_account_type,
1607 l_iby_pmt_rec.ext_bank_account_name,
1608 TO_NUMBER(l_iby_docs_rec.calling_app_doc_unique_ref2),
1609 l_iby_pmt_rec.payment_id,
1610 l_docErrorTab,
1611 l_docErrorRec,
1612 l_valid,
1613 l_error_message);
1614 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
1615 x_result:=1;
1616 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
1617 x_result:=1;
1618 RETURN;
1619 END IF;
1620
1621
1622 IBY_PAYMENT_FORMAT_VAL_PVT.DEPOSITER_ACC_NUM(l_format_name,
1623 l_iby_pmt_rec.ext_bank_account_number,
1624 l_docErrorTab,
1625 l_docErrorRec,
1626 l_valid,
1627 l_error_message);
1628 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
1629 x_result:=1;
1630 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
1631 x_result:=1;
1632 RETURN;
1633 END IF;
1634
1635
1636 IBY_PAYMENT_FORMAT_VAL_PVT.RTN_NUMBER(l_format_name,
1637 l_iby_pmt_rec.ext_branch_number,
1638 l_docErrorTab,
1639 l_docErrorRec,
1640 l_valid,
1641 l_error_message);
1642 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
1643 x_result:=1;
1644 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
1645 x_result:=1;
1646 RETURN;
1647 END IF;
1648
1649 -- bug 8577262
1650 /*
1651 IBY_PAYMENT_FORMAT_VAL_PVT.PAY_ALONE_OPTION(l_format_name,
1652 TO_NUMBER(l_iby_docs_rec.calling_app_doc_unique_ref2),
1653 l_iby_pmt_rec.payment_id,
1654 l_docErrorTab,
1655 l_docErrorRec,
1656 l_valid,
1657 l_error_message);
1658 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
1659 x_result:=1;
1660 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
1661 x_result:=1;
1662 RETURN;
1663 END IF;
1664 */
1665
1666 IBY_PAYMENT_FORMAT_VAL_PVT.TREASURY_SYMBOLS_PROCESS(l_format_name,
1667 p_instruction_id,
1668 l_iby_pmt_rec.payment_id,
1669 TO_NUMBER(l_iby_docs_rec.calling_app_doc_unique_ref2),
1670 l_docErrorTab,
1671 l_docErrorRec,
1672 l_valid,
1673 l_error_message);
1674 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
1675 x_result:=1;
1676 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
1677 x_result:=1;
1678 RETURN;
1679 END IF;
1680
1681
1682 IBY_PAYMENT_FORMAT_VAL_PVT.PAYEE_SSN(l_format_name,
1683 l_iby_pmt_rec.payee_le_registration_num,
1684 l_iby_pmt_rec.payee_party_id,
1685 l_docErrorTab,
1686 l_docErrorRec,
1687 l_valid,
1688 l_error_message);
1689 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
1690 x_result:=1;
1691 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
1692 x_result:=1;
1693 RETURN;
1694 END IF;
1695
1696
1697
1698
1699 END LOOP;-- End Of Documents Cursor Loop
1700 CLOSE iby_docs_csr;-- Closing Payable Documents Cursor
1701
1702 END LOOP;-- End Of Payments Cursor Loop
1703 CLOSE iby_pmt_csr; -- Closing Payments Cursor
1704
1705 IBY_PAYMENT_FORMAT_VAL_PVT.SCHEDULE_NUMBER(l_format_name,
1706 p_instruction_id,
1707 l_docErrorTab,
1708 l_docErrorRec,
1709 l_valid,
1710 l_error_message);
1711
1712 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
1713 x_result:=1;
1714 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
1715 x_result:=1;
1716 RETURN;
1717 END IF;
1718
1719
1720 END IF; -- End of IF pay_instr_data_csr%FOUND
1721 CLOSE pay_instr_data_csr; -- Closing Instruction Data Cursor
1722
1723 IBY_VALIDATIONSETS_PUB.insert_transaction_errors('N',l_docErrorTab);
1724
1725 EXCEPTION
1726 WHEN OTHERS THEN
1727 x_result := 1;
1728 l_docErrorRec.transaction_error_id := null;
1729 l_docErrorRec.error_code := 'UNEXPECTED_ERROR';
1730 l_docErrorRec.error_message := SQLERRM;
1731 IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(l_docErrorRec, l_docErrorTab);
1732 iby_payment_format_val_pvt.log_error_messages(FND_LOG.LEVEL_STATEMENT, l_format_name, SQLERRM);
1733 IBY_VALIDATIONSETS_PUB.insert_transaction_errors('N',l_docErrorTab);
1734
1735
1736 END FVTICTX;-- End of Procedure FVTICTX
1737 ----------------------------------------------------------------------------------------------------------
1738 /*
1739
1740 PROCEDURE : FVTPCCD
1741
1742 ECS CCD Vendor Payment Format Report
1743
1744 */
1745
1746 PROCEDURE FVTPCCD
1747 (
1748 p_validation_assign_id IN IBY_VAL_ASSIGNMENTS.validation_assignment_id%TYPE,
1749 p_validation_set_code IN IBY_VALIDATION_SETS_VL.validation_set_code%TYPE,
1750 p_instruction_id IN IBY_PAY_INSTRUCTIONS_ALL.payment_instruction_id%TYPE,
1751 p_is_online_val IN VARCHAR2,
1752 x_result OUT NOCOPY NUMBER
1753 )IS
1754
1755 l_instruction_rec IBY_VALIDATIONSETS_PUB.instructionRecType;
1756 l_docErrorTab IBY_VALIDATIONSETS_PUB.docErrorTabType;
1757 l_docErrorRec IBY_TRANSACTION_ERRORS%ROWTYPE;
1758
1759 -- Pick Up The Required Data From Instructions (IBY_PAY_INSTRUCTIONS_ALL) using payemnt_instruction_id
1760 CURSOR pay_instr_data_csr(p_instruction_id IBY_PAY_INSTRUCTIONS_ALL.payment_instruction_id%TYPE) IS
1761 SELECT org_id
1762 FROM iby_pay_instructions_all
1763 WHERE
1764 payment_instruction_id = p_instruction_id;
1765
1766 -- Pick Up Required Data From Payments (IBY_PAYMENTS_ALL) using payment_instruction_id
1767 CURSOR iby_pmt_csr(p_instruction_id IBY_PAY_INSTRUCTIONS_ALL.payment_instruction_id%TYPE) IS
1768 SELECT pmts.payment_id,
1769 pmts.payment_instruction_id,
1770 pmts.payee_party_id,
1771 pmts.payee_le_registration_num,
1772 pmts.payment_amount,
1773 pmts.internal_bank_account_id,
1774 pmts.int_bank_account_name,
1775 pmts.int_bank_acct_agency_loc_code,
1776 pmts.external_bank_account_id,
1777 ext_ba.bank_account_name ext_bank_account_name,
1778 iby_ext_bankacct_pub.Uncipher_Bank_Number (ext_ba.bank_account_num,
1779 ext_ba.ba_num_sec_segment_id,
1780 iby_utility_pvt.get_view_param('SYS_KEY'),
1781 baek.subkey_cipher_text,
1782 baes.segment_cipher_text,
1783 baes.encoding_scheme,
1784 ext_ba.ba_mask_setting,
1785 ext_ba.ba_unmask_length) ext_bank_account_number,
1786 pmts.ext_branch_number,
1787 pmts.ext_bank_account_type
1788 FROM iby_payments_all pmts,
1789 iby_ext_bank_accounts ext_ba,
1790 IBY_SYS_SECURITY_SUBKEYS baek,
1791 IBY_SECURITY_SEGMENTS baes
1792 WHERE
1793 pmts.payment_instruction_id = p_instruction_id
1794 AND pmts.payment_status = 'INSTRUCTION_CREATED'
1795 AND pmts.external_bank_account_id = ext_ba.ext_bank_account_id(+)
1796 AND ext_ba.ba_num_sec_segment_id = baes.sec_segment_id(+)
1797 AND baes.sec_subkey_id = baek.sec_subkey_id(+);
1798
1799 -- Pick Up Required Data From Payable Documents (IBY_DOCS_PAYABLE_ALL) Using Payment Id From Payment Data
1800 CURSOR iby_docs_csr(p_pmt_id IBY_DOCS_PAYABLE_ALL.payment_id%TYPE) IS
1801 SELECT calling_app_doc_unique_ref2
1802 FROM iby_docs_payable_all
1803 WHERE
1804 payment_id = p_pmt_id
1805 AND
1806 calling_app_id=200
1807 AND
1808 UPPER(payment_function) IN ('PAYABLES_DISB','EMPLOYEE_EXP')
1809 AND document_status = 'PAYMENT_CREATED';
1810
1811
1812
1813 -- Declaring Record Types Of Various Cursors
1814 l_pay_instr_rec pay_instr_data_csr%ROWTYPE;
1815 l_iby_pmt_rec iby_pmt_csr%ROWTYPE;
1816 l_iby_docs_rec iby_docs_csr%ROWTYPE;
1817 l_org_id NUMBER;
1818
1819 l_format_name VARCHAR2(50);
1820 l_error_message VARCHAR2(1000);
1821 l_valid NUMBER;
1822
1823 BEGIN
1824 l_format_name := 'FVTPCCD';
1825 x_result:=g_SUCCESS;
1826
1827 -- Initializing the payment record
1828 IBY_VALIDATIONSETS_PUB.initInstructionData(p_instruction_id,l_instruction_rec);
1829
1830 l_docErrorRec.validation_set_code := p_validation_set_code;
1831 l_docErrorRec.transaction_id := p_instruction_id;
1832 l_docErrorRec.transaction_type := 'PAYMENT_INSTRUCTION';
1833 l_docErrorRec.calling_app_doc_unique_ref1 := p_instruction_id;
1834
1835 OPEN pay_instr_data_csr(p_instruction_id); -- Opening Instruction Data Cursor
1836 FETCH pay_instr_data_csr INTO l_pay_instr_rec; -- Getting Instruction Data
1837
1838 IF pay_instr_data_csr%FOUND THEN -- If Row Found Then Only Process Further
1839
1840 -- delete from FV_TP_TS_AMT_DATA to refresh data
1841 delete from FV_TP_TS_AMT_DATA where payment_instruction_id = p_instruction_id;
1842
1843 l_org_id:=l_pay_instr_rec.org_id;-- Extracting Org_ID
1844
1845 -- Do IBY_PAYMENT_FORMAT_VAL_PVT.SUPPLIER_TYPE at Payment Instruction Level
1846 -- Moved this to instruction level (Bug 5526640)
1847 IBY_PAYMENT_FORMAT_VAL_PVT.SUPPLIER_TYPE(l_format_name,
1848 p_instruction_id,
1849 l_docErrorTab,
1850 l_docErrorRec,
1851 l_valid,
1852 l_error_message);
1853
1854 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
1855 x_result:=1;
1856 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
1857 x_result:=1;
1858 RETURN;
1859 END IF;
1860
1861
1862 OPEN iby_pmt_csr(p_instruction_id); -- Opening Payment Data Cursor
1863 LOOP -- Perform Validation For Each Of record in IBY_PAYMENTS_ALL
1864 FETCH iby_pmt_csr INTO l_iby_pmt_rec; -- Getting Payment Data
1865 EXIT WHEN iby_pmt_csr%NOTFOUND;
1866
1867
1868 IBY_PAYMENT_FORMAT_VAL_PVT.RFC_ID(l_format_name,
1869 l_iby_pmt_rec.payment_id,
1870 l_docErrorTab,
1871 l_docErrorRec,
1872 l_valid,
1873 l_error_message);
1874
1875 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
1876 x_result:=1;
1877 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
1878 x_result:=1;
1879 RETURN;
1880 END IF;
1881
1882 -- Do IBY_PAYMENT_FORMAT_VAL_PVT.AGENCY_ADDRESS
1883 IBY_PAYMENT_FORMAT_VAL_PVT.AGENCY_ADDRESS(l_format_name,
1884 l_org_id,
1885 l_docErrorTab,
1886 l_docErrorRec,
1887 l_valid,
1888 l_error_message);
1889
1890 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
1891 x_result:=1;
1892 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
1893 x_result:=1;
1894 RETURN;
1895 END IF;
1896
1897
1898 OPEN iby_docs_csr(l_iby_pmt_rec.payment_id); -- Opening Payable Documents Data
1899 LOOP -- Perform Validation For Each Of record in IBY_DOCS_PAYABLE_ALL
1900 FETCH iby_docs_csr INTO l_iby_docs_rec; -- Getting Payable Documents Data
1901 EXIT WHEN iby_docs_csr%NOTFOUND;
1902
1903 -- validate internal bank account parameters
1904 IBY_PAYMENT_FORMAT_VAL_PVT.AGENCY_LOCATION_CODE(l_format_name,
1905 l_iby_pmt_rec.int_bank_acct_agency_loc_code,
1906 l_iby_pmt_rec.int_bank_account_name,
1907 l_docErrorTab,
1908 l_docErrorRec,
1909 l_valid,
1910 l_error_message);
1911 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
1912 x_result:=1;
1913 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
1914 x_result:=1;
1915 RETURN;
1916 END IF;
1917
1918 -- validate external bank account parameters
1919 -- validate external bank account id
1920 IBY_PAYMENT_FORMAT_VAL_PVT.EXTERNAL_BANK_ACCOUNT_ID(l_format_name,
1921 l_iby_pmt_rec.external_bank_account_id,
1922 l_docErrorTab,
1923 l_docErrorRec,
1924 l_valid,
1925 l_error_message);
1926 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
1927 x_result:=1;
1928 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
1929 x_result:=1;
1930 RETURN;
1931 END IF;
1932
1933
1934 IBY_PAYMENT_FORMAT_VAL_PVT.ACCOUNT_TYPE(l_format_name,
1935 l_iby_pmt_rec.ext_bank_account_type,
1936 l_iby_pmt_rec.ext_bank_account_name,
1937 TO_NUMBER(l_iby_docs_rec.calling_app_doc_unique_ref2),
1938 l_iby_pmt_rec.payment_id,
1939 l_docErrorTab,
1940 l_docErrorRec,
1941 l_valid,
1942 l_error_message);
1943 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
1944 x_result:=1;
1945 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
1946 x_result:=1;
1947 RETURN;
1948 END IF;
1949
1950
1951 IBY_PAYMENT_FORMAT_VAL_PVT.DEPOSITER_ACC_NUM(l_format_name,
1952 l_iby_pmt_rec.ext_bank_account_number,
1953 l_docErrorTab,
1954 l_docErrorRec,
1955 l_valid,
1956 l_error_message);
1957
1958 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
1959 x_result:=1;
1960 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
1961 x_result:=1;
1962 RETURN;
1963 END IF;
1964
1965 IBY_PAYMENT_FORMAT_VAL_PVT.RTN_NUMBER(l_format_name,
1966 l_iby_pmt_rec.ext_branch_number,
1967 l_docErrorTab,
1968 l_docErrorRec,
1969 l_valid,
1970 l_error_message);
1971
1972 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
1973 x_result:=1;
1974 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
1975 x_result:=1;
1976 RETURN;
1977 END IF;
1978
1979
1980 IBY_PAYMENT_FORMAT_VAL_PVT.PAY_ALONE_OPTION(l_format_name,
1981 TO_NUMBER(l_iby_docs_rec.calling_app_doc_unique_ref2),
1982 l_iby_pmt_rec.payment_id,
1983 l_docErrorTab,
1984 l_docErrorRec,
1985 l_valid,
1986 l_error_message);
1987 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
1988 x_result:=1;
1989 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
1990 x_result:=1;
1991 RETURN;
1992 END IF;
1993
1994
1995 IBY_PAYMENT_FORMAT_VAL_PVT.TREASURY_SYMBOLS_PROCESS(l_format_name,
1996 p_instruction_id,
1997 l_iby_pmt_rec.payment_id,
1998 TO_NUMBER(l_iby_docs_rec.calling_app_doc_unique_ref2),
1999 l_docErrorTab,
2000 l_docErrorRec,
2001 l_valid,
2002 l_error_message);
2003 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
2004 x_result:=1;
2005 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
2006 x_result:=1;
2007 RETURN;
2008 END IF;
2009
2010
2011
2012 IBY_PAYMENT_FORMAT_VAL_PVT.PAYEE_SSN(l_format_name,
2013 l_iby_pmt_rec.payee_le_registration_num,
2014 l_iby_pmt_rec.payee_party_id,
2015 l_docErrorTab,
2016 l_docErrorRec,
2017 l_valid,
2018 l_error_message);
2019 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
2020 x_result:=1;
2021 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
2022 x_result:=1;
2023 RETURN;
2024 END IF;
2025
2026
2027
2028
2029 END LOOP;-- End Of Documents Cursor Loop
2030 CLOSE iby_docs_csr;-- Closing Payable Documents Cursor
2031
2032 END LOOP;-- End Of Payments Cursor Loop
2033 CLOSE iby_pmt_csr; -- Closing Payments Cursor
2034
2035 IBY_PAYMENT_FORMAT_VAL_PVT.SCHEDULE_NUMBER(l_format_name,
2036 p_instruction_id,
2037 l_docErrorTab,
2038 l_docErrorRec,
2039 l_valid,
2040 l_error_message);
2041
2042 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
2043 x_result:=1;
2044 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
2045 x_result:=1;
2046 RETURN;
2047 END IF;
2048
2049
2050 END IF; -- End of IF pay_instr_data_csr%FOUND
2051 CLOSE pay_instr_data_csr; -- Closing Instruction Data Cursor
2052
2053 IBY_VALIDATIONSETS_PUB.insert_transaction_errors('N',l_docErrorTab);
2054
2055
2056 EXCEPTION
2057 WHEN OTHERS THEN
2058 x_result := 1;
2059 l_docErrorRec.transaction_error_id := null;
2060 l_docErrorRec.error_code := 'UNEXPECTED_ERROR';
2061 l_docErrorRec.error_message := SQLERRM;
2062 IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(l_docErrorRec, l_docErrorTab);
2063 iby_payment_format_val_pvt.log_error_messages(FND_LOG.LEVEL_STATEMENT, l_format_name, SQLERRM);
2064 IBY_VALIDATIONSETS_PUB.insert_transaction_errors('N',l_docErrorTab);
2065
2066
2067 END FVTPCCD;-- End of Procedure FVTPCCD
2068 ----------------------------------------------------------------------------------------------------------
2069 /*
2070
2071 PROCEDURE : FVTIACHP
2072
2073 ECS CCDP Vendor Payment Format Report
2074
2075 */
2076
2077 PROCEDURE FVTIACHP
2078 (
2079 p_validation_assign_id IN IBY_VAL_ASSIGNMENTS.validation_assignment_id%TYPE,
2080 p_validation_set_code IN IBY_VALIDATION_SETS_VL.validation_set_code%TYPE,
2081 p_instruction_id IN IBY_PAY_INSTRUCTIONS_ALL.payment_instruction_id%TYPE,
2082 p_is_online_val IN VARCHAR2,
2083 x_result OUT NOCOPY NUMBER
2084 )IS
2085
2086 l_instruction_rec IBY_VALIDATIONSETS_PUB.instructionRecType;
2087 l_docErrorTab IBY_VALIDATIONSETS_PUB.docErrorTabType;
2088 l_docErrorRec IBY_TRANSACTION_ERRORS%ROWTYPE;
2089
2090 -- Pick Up The Required Data From Instructions (IBY_PAY_INSTRUCTIONS_ALL) using payemnt_instruction_id
2091 CURSOR pay_instr_data_csr(p_instruction_id IBY_PAY_INSTRUCTIONS_ALL.payment_instruction_id%TYPE) IS
2092 SELECT org_id
2093 FROM iby_pay_instructions_all
2094 WHERE
2095 payment_instruction_id = p_instruction_id;
2096
2097 -- Pick Up Required Data From Payments (IBY_PAYMENTS_ALL) using payment_instruction_id
2098 CURSOR iby_pmt_csr(p_instruction_id IBY_PAY_INSTRUCTIONS_ALL.payment_instruction_id%TYPE) IS
2099 SELECT pmts.payment_id,
2100 pmts.payment_instruction_id,
2101 pmts.payee_party_id,
2102 pmts.payee_le_registration_num,
2103 pmts.payment_amount,
2104 pmts.internal_bank_account_id,
2105 pmts.int_bank_account_name,
2106 pmts.int_bank_acct_agency_loc_code,
2107 pmts.external_bank_account_id,
2108 ext_ba.bank_account_name ext_bank_account_name,
2109 iby_ext_bankacct_pub.Uncipher_Bank_Number (ext_ba.bank_account_num,
2110 ext_ba.ba_num_sec_segment_id,
2111 iby_utility_pvt.get_view_param('SYS_KEY'),
2112 baek.subkey_cipher_text,
2113 baes.segment_cipher_text,
2114 baes.encoding_scheme,
2115 ext_ba.ba_mask_setting,
2116 ext_ba.ba_unmask_length) ext_bank_account_number,
2117 pmts.ext_branch_number,
2118 pmts.ext_bank_account_type
2119 FROM iby_payments_all pmts,
2120 iby_ext_bank_accounts ext_ba,
2121 IBY_SYS_SECURITY_SUBKEYS baek,
2122 IBY_SECURITY_SEGMENTS baes
2123 WHERE
2124 pmts.payment_instruction_id = p_instruction_id
2125 AND pmts.payment_status = 'INSTRUCTION_CREATED'
2126 AND pmts.external_bank_account_id = ext_ba.ext_bank_account_id(+)
2127 AND ext_ba.ba_num_sec_segment_id = baes.sec_segment_id(+)
2128 AND baes.sec_subkey_id = baek.sec_subkey_id(+);
2129
2130 -- Pick Up Required Data From Payable Documents (IBY_DOCS_PAYABLE_ALL) Using Payment Id From Payment Data
2131 CURSOR iby_docs_csr(p_pmt_id IBY_DOCS_PAYABLE_ALL.payment_id%TYPE) IS
2132 SELECT calling_app_doc_unique_ref2
2133 FROM iby_docs_payable_all
2134 WHERE
2135 payment_id = p_pmt_id
2136 AND
2137 calling_app_id=200
2138 AND
2139 UPPER(payment_function) IN ('PAYABLES_DISB','EMPLOYEE_EXP')
2140 AND document_status = 'PAYMENT_CREATED';
2141
2142
2143 -- Declaring Record Types Of Various Cursors
2144 l_pay_instr_rec pay_instr_data_csr%ROWTYPE;
2145 l_iby_pmt_rec iby_pmt_csr%ROWTYPE;
2146 l_iby_docs_rec iby_docs_csr%ROWTYPE;
2147 l_org_id NUMBER;
2148
2149 l_format_name VARCHAR2(50);
2150 l_error_message VARCHAR2(1000);
2151 l_valid NUMBER;
2152
2153 BEGIN
2154 l_format_name := 'FVTIACHP';
2155 x_result:=g_SUCCESS;
2156
2157 -- Initializing the payment record
2158 IBY_VALIDATIONSETS_PUB.initInstructionData(p_instruction_id,l_instruction_rec);
2159
2160 l_docErrorRec.validation_set_code := p_validation_set_code;
2161 l_docErrorRec.transaction_id := p_instruction_id;
2162 l_docErrorRec.transaction_type := 'PAYMENT_INSTRUCTION';
2163 l_docErrorRec.calling_app_doc_unique_ref1 := p_instruction_id;
2164
2165 OPEN pay_instr_data_csr(p_instruction_id); -- Opening Instruction Data Cursor
2166 FETCH pay_instr_data_csr INTO l_pay_instr_rec; -- Getting Instruction Data
2167
2168 IF pay_instr_data_csr%FOUND THEN -- If Row Found Then Only Process Further
2169
2170 -- delete from FV_TP_TS_AMT_DATA to refresh data
2171 delete from FV_TP_TS_AMT_DATA where payment_instruction_id = p_instruction_id;
2172
2173 l_org_id:=l_pay_instr_rec.org_id;-- Extracting Org_ID
2174
2175
2176 -- Do IBY_PAYMENT_FORMAT_VAL_PVT.SUPPLIER_TYPE at Payment Instruction Level
2177 -- Moved this to instruction level (Bug 5526640)
2178 IBY_PAYMENT_FORMAT_VAL_PVT.SUPPLIER_TYPE(l_format_name,
2179 p_instruction_id,
2180 l_docErrorTab,
2181 l_docErrorRec,
2182 l_valid,
2183 l_error_message);
2184
2185 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
2186 x_result:=1;
2187 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
2188 x_result:=1;
2189 RETURN;
2190 END IF;
2191
2192
2193 OPEN iby_pmt_csr(p_instruction_id); -- Opening Payment Data Cursor
2194 LOOP -- Perform Validation For Each Of record in IBY_PAYMENTS_ALL
2195 FETCH iby_pmt_csr INTO l_iby_pmt_rec; -- Getting Payment Data
2196 EXIT WHEN iby_pmt_csr%NOTFOUND;
2197
2198 IBY_PAYMENT_FORMAT_VAL_PVT.RFC_ID(l_format_name,
2199 l_iby_pmt_rec.payment_id,
2200 l_docErrorTab,
2201 l_docErrorRec,
2202 l_valid,
2203 l_error_message);
2204
2205 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
2206 x_result:=1;
2207 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
2208 x_result:=1;
2209 RETURN;
2210 END IF;
2211
2212 -- Do IBY_PAYMENT_FORMAT_VAL_PVT.AGENCY_ADDRESS
2213 IBY_PAYMENT_FORMAT_VAL_PVT.AGENCY_ADDRESS(l_format_name,
2214 l_org_id,
2215 l_docErrorTab,
2216 l_docErrorRec,
2217 l_valid,
2218 l_error_message);
2219 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
2220 x_result:=1;
2221 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
2222 x_result:=1;
2223 RETURN;
2224 END IF;
2225
2226
2227
2228 OPEN iby_docs_csr(l_iby_pmt_rec.payment_id); -- Opening Payable Documents Data
2229 LOOP -- Perform Validation For Each Of record in IBY_DOCS_PAYABLE_ALL
2230 FETCH iby_docs_csr INTO l_iby_docs_rec; -- Getting Payable Documents Data
2231 EXIT WHEN iby_docs_csr%NOTFOUND;
2232
2233 -- validate internal bank account parameters
2234 IBY_PAYMENT_FORMAT_VAL_PVT.AGENCY_LOCATION_CODE(l_format_name,
2235 l_iby_pmt_rec.int_bank_acct_agency_loc_code,
2236 l_iby_pmt_rec.int_bank_account_name,
2237 l_docErrorTab,
2238 l_docErrorRec,
2239 l_valid,
2240 l_error_message);
2241 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
2242 x_result:=1;
2243 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
2244 x_result:=1;
2245 RETURN;
2246 END IF;
2247
2248 -- validate external bank account parameters
2249 -- validate external bank account id
2250 IBY_PAYMENT_FORMAT_VAL_PVT.EXTERNAL_BANK_ACCOUNT_ID(l_format_name,
2251 l_iby_pmt_rec.external_bank_account_id,
2252 l_docErrorTab,
2253 l_docErrorRec,
2254 l_valid,
2255 l_error_message);
2256 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
2257 x_result:=1;
2258 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
2259 x_result:=1;
2260 RETURN;
2261 END IF;
2262
2263
2264 IBY_PAYMENT_FORMAT_VAL_PVT.ACCOUNT_TYPE(l_format_name,
2265 l_iby_pmt_rec.ext_bank_account_type,
2266 l_iby_pmt_rec.ext_bank_account_name,
2267 TO_NUMBER(l_iby_docs_rec.calling_app_doc_unique_ref2),
2268 l_iby_pmt_rec.payment_id,
2269 l_docErrorTab,
2270 l_docErrorRec,
2271 l_valid,
2272 l_error_message);
2273 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
2274 x_result:=1;
2275 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
2276 x_result:=1;
2277 RETURN;
2278 END IF;
2279
2280 IBY_PAYMENT_FORMAT_VAL_PVT.DEPOSITER_ACC_NUM(l_format_name,
2281 l_iby_pmt_rec.ext_bank_account_number,
2282 l_docErrorTab,
2283 l_docErrorRec,
2284 l_valid,
2285 l_error_message);
2286 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
2287 x_result:=1;
2288 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
2289 x_result:=1;
2290 RETURN;
2291 END IF;
2292
2293
2294 IBY_PAYMENT_FORMAT_VAL_PVT.RTN_NUMBER(l_format_name,
2295 l_iby_pmt_rec.ext_branch_number,
2296 l_docErrorTab,
2297 l_docErrorRec,
2298 l_valid,
2299 l_error_message);
2300
2301 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
2302 x_result:=1;
2303 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
2304 x_result:=1;
2305 RETURN;
2306 END IF;
2307
2308
2309 IBY_PAYMENT_FORMAT_VAL_PVT.PAY_ALONE_OPTION(l_format_name,
2310 TO_NUMBER(l_iby_docs_rec.calling_app_doc_unique_ref2),
2311 l_iby_pmt_rec.payment_id,
2312 l_docErrorTab,
2313 l_docErrorRec,
2314 l_valid,
2315 l_error_message);
2316 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
2317 x_result:=1;
2318 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
2319 x_result:=1;
2320 RETURN;
2321 END IF;
2322
2323
2324 IBY_PAYMENT_FORMAT_VAL_PVT.TREASURY_SYMBOLS_PROCESS(l_format_name,
2325 p_instruction_id,
2326 l_iby_pmt_rec.payment_id,
2327 TO_NUMBER(l_iby_docs_rec.calling_app_doc_unique_ref2),
2328 l_docErrorTab,
2329 l_docErrorRec,
2330 l_valid,
2331 l_error_message);
2332 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
2333 x_result:=1;
2334 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
2335 x_result:=1;
2336 RETURN;
2337 END IF;
2338
2339
2340
2341 IBY_PAYMENT_FORMAT_VAL_PVT.PAYEE_SSN(l_format_name,
2342 l_iby_pmt_rec.payee_le_registration_num,
2343 l_iby_pmt_rec.payee_party_id,
2344 l_docErrorTab,
2345 l_docErrorRec,
2346 l_valid,
2347 l_error_message);
2348 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
2349 x_result:=1;
2350 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
2351 x_result:=1;
2352 RETURN;
2353 END IF;
2354
2355
2356
2357
2358 END LOOP;-- End Of Documents Cursor Loop
2359 CLOSE iby_docs_csr;-- Closing Payable Documents Cursor
2360
2361 END LOOP;-- End Of Payments Cursor Loop
2362 CLOSE iby_pmt_csr; -- Closing Payments Cursor
2363
2364 IBY_PAYMENT_FORMAT_VAL_PVT.SCHEDULE_NUMBER(l_format_name,
2365 p_instruction_id,
2366 l_docErrorTab,
2367 l_docErrorRec,
2368 l_valid,
2369 l_error_message);
2370
2371 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
2372 x_result:=1;
2373 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
2374 x_result:=1;
2375 RETURN;
2376 END IF;
2377
2378
2379 END IF; -- End of IF pay_instr_data_csr%FOUND
2380 CLOSE pay_instr_data_csr; -- Closing Instruction Data Cursor
2381
2382 IBY_VALIDATIONSETS_PUB.insert_transaction_errors('N',l_docErrorTab);
2383
2384 EXCEPTION
2385 WHEN OTHERS THEN
2386 x_result := 1;
2387 l_docErrorRec.transaction_error_id := null;
2388 l_docErrorRec.error_code := 'UNEXPECTED_ERROR';
2389 l_docErrorRec.error_message := SQLERRM;
2390 IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(l_docErrorRec, l_docErrorTab);
2391 iby_payment_format_val_pvt.log_error_messages(FND_LOG.LEVEL_STATEMENT, l_format_name, SQLERRM);
2392 IBY_VALIDATIONSETS_PUB.insert_transaction_errors('N',l_docErrorTab);
2393
2394
2395 END FVTIACHP;-- End of Procedure FVTIACHP
2396 ----------------------------------------------------------------------------------------------------------
2397 /*
2398
2399 PROCEDURE : FVTIACHB
2400
2401 ECS Check NCR Payment Format
2402
2403 */
2404
2405 PROCEDURE FVTIACHB
2406 (
2407 p_validation_assign_id IN IBY_VAL_ASSIGNMENTS.validation_assignment_id%TYPE,
2408 p_validation_set_code IN IBY_VALIDATION_SETS_VL.validation_set_code%TYPE,
2409 p_instruction_id IN IBY_PAY_INSTRUCTIONS_ALL.payment_instruction_id%TYPE,
2410 p_is_online_val IN VARCHAR2,
2411 x_result OUT NOCOPY NUMBER
2412 )IS
2413
2414 l_instruction_rec IBY_VALIDATIONSETS_PUB.instructionRecType;
2415 l_docErrorTab IBY_VALIDATIONSETS_PUB.docErrorTabType;
2416 l_docErrorRec IBY_TRANSACTION_ERRORS%ROWTYPE;
2417
2418 -- Pick Up The Required Data From Instructions (IBY_PAY_INSTRUCTIONS_ALL) using payemnt_instruction_id
2419 CURSOR pay_instr_data_csr(p_instruction_id IBY_PAY_INSTRUCTIONS_ALL.payment_instruction_id%TYPE) IS
2420 SELECT org_id,
2421 payment_reason_code
2422 FROM iby_pay_instructions_all
2423 WHERE
2424 payment_instruction_id = p_instruction_id;
2425
2426 -- Pick Up Required Data From Payments (IBY_PAYMENTS_ALL) using payment_instruction_id
2427 CURSOR iby_pmt_csr(p_instruction_id IBY_PAY_INSTRUCTIONS_ALL.payment_instruction_id%TYPE) IS
2428 SELECT payment_id,
2429 payment_instruction_id,
2430 payment_amount,
2431 internal_bank_account_id,
2432 int_bank_account_name,
2433 int_bank_acct_agency_loc_code
2434 FROM iby_payments_all
2435 WHERE payment_instruction_id = p_instruction_id
2436 AND payment_status = 'INSTRUCTION_CREATED';
2437
2438 -- Pick Up Required Data From Payable Documents (IBY_DOCS_PAYABLE_ALL) Using Payment Id From Payment Data
2439 CURSOR iby_docs_csr(p_pmt_id IBY_DOCS_PAYABLE_ALL.payment_id%TYPE) IS
2440 SELECT calling_app_doc_unique_ref2
2441 FROM iby_docs_payable_all
2442 WHERE
2443 payment_id = p_pmt_id
2444 AND
2445 calling_app_id=200
2446 AND
2447 UPPER(payment_function) IN ('PAYABLES_DISB','EMPLOYEE_EXP')
2448 AND document_status = 'PAYMENT_CREATED';
2449
2450
2451 -- Declaring Record Types Of Various Cursors
2452 l_pay_instr_rec pay_instr_data_csr%ROWTYPE;
2453 l_iby_pmt_rec iby_pmt_csr%ROWTYPE;
2454 l_iby_docs_rec iby_docs_csr%ROWTYPE;
2455 l_org_id NUMBER;
2456
2457 l_format_name VARCHAR2(50);
2458 l_error_message VARCHAR2(1000);
2459 l_valid NUMBER;
2460 l_invoice_type IBY_DOCS_PAYABLE_ALL.DOCUMENT_TYPE%TYPE;
2461
2462 BEGIN
2463 l_format_name := 'FVTIACHB';
2464 x_result:=g_SUCCESS;
2465
2466 -- Initializing the payment record
2467 IBY_VALIDATIONSETS_PUB.initInstructionData(p_instruction_id,l_instruction_rec);
2468
2469 l_docErrorRec.validation_set_code := p_validation_set_code;
2470 l_docErrorRec.transaction_id := p_instruction_id;
2471 l_docErrorRec.transaction_type := 'PAYMENT_INSTRUCTION';
2472 l_docErrorRec.calling_app_doc_unique_ref1 := p_instruction_id;
2473
2474 OPEN pay_instr_data_csr(p_instruction_id); -- Opening Instruction Data Cursor
2475 FETCH pay_instr_data_csr INTO l_pay_instr_rec; -- Getting Instruction Data
2476
2477 IF pay_instr_data_csr%FOUND THEN -- If Row Found Then Only Process Further
2478
2479 -- delete from FV_TP_TS_AMT_DATA to refresh data
2480 delete from FV_TP_TS_AMT_DATA where payment_instruction_id = p_instruction_id;
2481
2482 l_org_id:=l_pay_instr_rec.org_id;-- Extracting Org_ID
2483
2484
2485 -- Do IBY_PAYMENT_FORMAT_VAL_PVT.SUPPLIER_TYPE at Payment Instruction Level
2486 -- Moved this to instruction level (Bug 5526640)
2487 IBY_PAYMENT_FORMAT_VAL_PVT.SUPPLIER_TYPE(l_format_name,
2488 p_instruction_id,
2489 l_docErrorTab,
2490 l_docErrorRec,
2491 l_valid,
2492 l_error_message);
2493
2494 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
2495 x_result:=1;
2496 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
2497 x_result:=1;
2498 RETURN;
2499 END IF;
2500
2501 OPEN iby_pmt_csr(p_instruction_id); -- Opening Payment Data Cursor
2502 LOOP -- Perform Validation For Each Of record in IBY_PAYMENTS_ALL
2503 FETCH iby_pmt_csr INTO l_iby_pmt_rec; -- Getting Payment Data
2504 EXIT WHEN iby_pmt_csr%NOTFOUND;
2505
2506 IBY_PAYMENT_FORMAT_VAL_PVT.RFC_ID(l_format_name,
2507 l_iby_pmt_rec.payment_id,
2508 l_docErrorTab,
2509 l_docErrorRec,
2510 l_valid,
2511 l_error_message);
2512
2513 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
2514 x_result:=1;
2515 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
2516 x_result:=1;
2517 RETURN;
2518 END IF;
2519
2520 IBY_PAYMENT_FORMAT_VAL_PVT.MAX_PAYMENT_AMT(l_format_name,
2521 l_iby_pmt_rec.payment_instruction_id,
2522 l_iby_pmt_rec.payment_amount,
2523 l_docErrorTab,
2524 l_docErrorRec,
2525 l_valid,
2526 l_error_message);
2527 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
2528 x_result:=1;
2529 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
2530 x_result:=1;
2531 RETURN;
2532 END IF;
2533
2534
2535 -- Do IBY_PAYMENT_FORMAT_VAL_PVT.AGENCY_ADDRESS
2536 IBY_PAYMENT_FORMAT_VAL_PVT.AGENCY_ADDRESS(l_format_name,
2537 l_org_id,
2538 l_docErrorTab,
2539 l_docErrorRec,
2540 l_valid,
2541 l_error_message);
2542 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
2543 x_result:=1;
2544 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
2545 x_result:=1;
2546 RETURN;
2547 END IF;
2548
2549
2550 -- Do IBY_PAYMENT_FORMAT_VAL_PVT.PAYEE_ADDRESS
2551 IBY_PAYMENT_FORMAT_VAL_PVT.PAYEE_ADDRESS(l_format_name,
2552 l_iby_pmt_rec.payment_id,
2553 l_docErrorTab,
2554 l_docErrorRec,
2555 l_valid,
2556 l_error_message);
2557
2558 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
2559 x_result:=1;
2560 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
2561 x_result:=1;
2562 RETURN;
2563 END IF;
2564
2565
2566
2567 OPEN iby_docs_csr(l_iby_pmt_rec.payment_id); -- Opening Payable Documents Data
2568 LOOP -- Perform Validation For Each Of record in IBY_DOCS_PAYABLE_ALL
2569 FETCH iby_docs_csr INTO l_iby_docs_rec; -- Getting Payable Documents Data
2570 EXIT WHEN iby_docs_csr%NOTFOUND;
2571
2572 -- validate internal bank account parameters
2573 IBY_PAYMENT_FORMAT_VAL_PVT.AGENCY_LOCATION_CODE(l_format_name,
2574 l_iby_pmt_rec.int_bank_acct_agency_loc_code,
2575 l_iby_pmt_rec.int_bank_account_name,
2576 l_docErrorTab,
2577 l_docErrorRec,
2578 l_valid,
2579 l_error_message);
2580
2581 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
2582 x_result:=1;
2583 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
2584 x_result:=1;
2585 RETURN;
2586 END IF;
2587
2588
2589 SELECT document_type
2590 INTO l_invoice_type FROM IBY_DOCS_PAYABLE_ALL
2591 WHERE calling_app_doc_unique_ref2 = l_iby_docs_rec.calling_app_doc_unique_ref2
2592 AND payment_id = l_iby_pmt_rec.payment_id
2593 AND calling_app_id=200
2594 AND UPPER(payment_function) IN ('PAYABLES_DISB','EMPLOYEE_EXP');
2595
2596 -- Do Validation by IBY_PAYMENT_FORMAT_VAL_PVT.PAY_TAX_BENEFIT at Instruction Level
2597 IF(l_invoice_type <> 'INTEREST') THEN
2598 IBY_PAYMENT_FORMAT_VAL_PVT.PAY_TAX_BENEFIT(l_format_name,
2599 l_iby_pmt_rec.payment_id,
2600 to_number(l_iby_docs_rec.calling_app_doc_unique_ref2),
2601 l_docErrorTab,
2602 l_docErrorRec,
2603 l_valid,
2604 l_error_message);
2605 END IF;
2606 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
2607 x_result:=1;
2608 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
2609 x_result:=1;
2610 RETURN;
2611 END IF;
2612
2613 IBY_PAYMENT_FORMAT_VAL_PVT.TREASURY_SYMBOLS_PROCESS(l_format_name,
2614 p_instruction_id,
2615 l_iby_pmt_rec.payment_id,
2616 TO_NUMBER(l_iby_docs_rec.calling_app_doc_unique_ref2),
2617 l_docErrorTab,
2618 l_docErrorRec,
2619 l_valid,
2620 l_error_message);
2621
2622 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
2623 x_result:=1;
2624 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
2625 x_result:=1;
2626 RETURN;
2627 END IF;
2628
2629 END LOOP;-- End Of Documents Cursor Loop
2630 CLOSE iby_docs_csr;-- Closing Payable Documents Cursor
2631
2632 END LOOP;-- End Of Payments Cursor Loop
2633 CLOSE iby_pmt_csr; -- Closing Payments Cursor
2634
2635 IBY_PAYMENT_FORMAT_VAL_PVT.SCHEDULE_NUMBER(l_format_name,
2636 p_instruction_id,
2637 l_docErrorTab,
2638 l_docErrorRec,
2639 l_valid,
2640 l_error_message);
2641
2642 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
2643 x_result:=1;
2644 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
2645 x_result:=1;
2646 RETURN;
2647 END IF;
2648
2649
2650 END IF; -- End of IF pay_instr_data_csr%FOUND
2651 CLOSE pay_instr_data_csr; -- Closing Instruction Data Cursor
2652
2653 IBY_VALIDATIONSETS_PUB.insert_transaction_errors('N',l_docErrorTab);
2654
2655 EXCEPTION
2656 WHEN OTHERS THEN
2657 x_result := 1;
2658 l_docErrorRec.transaction_error_id := null;
2659 l_docErrorRec.error_code := 'UNEXPECTED_ERROR';
2660 l_docErrorRec.error_message := SQLERRM;
2661 IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(l_docErrorRec, l_docErrorTab);
2662 iby_payment_format_val_pvt.log_error_messages(FND_LOG.LEVEL_STATEMENT, l_format_name, SQLERRM);
2663 IBY_VALIDATIONSETS_PUB.insert_transaction_errors('N',l_docErrorTab);
2664
2665
2666 END FVTIACHB;-- End of Procedure FVTIACHB
2667 ----------------------------------------------------------------------------------------------------------
2668 /*
2669
2670 PROCEDURE : FVTPPPD
2671
2672 ECS PPD Vendor Payment Format
2673
2674 */
2675
2676 PROCEDURE FVTPPPD
2677 (
2678 p_validation_assign_id IN IBY_VAL_ASSIGNMENTS.validation_assignment_id%TYPE,
2679 p_validation_set_code IN IBY_VALIDATION_SETS_VL.validation_set_code%TYPE,
2680 p_instruction_id IN IBY_PAY_INSTRUCTIONS_ALL.payment_instruction_id%TYPE,
2681 p_is_online_val IN VARCHAR2,
2682 x_result OUT NOCOPY NUMBER
2683 )IS
2684
2685 l_instruction_rec IBY_VALIDATIONSETS_PUB.instructionRecType;
2686 l_docErrorTab IBY_VALIDATIONSETS_PUB.docErrorTabType;
2687 l_docErrorRec IBY_TRANSACTION_ERRORS%ROWTYPE;
2688
2689 -- Pick Up The Required Data From Instructions (IBY_PAY_INSTRUCTIONS_ALL) using payemnt_instruction_id
2690 CURSOR pay_instr_data_csr(p_instruction_id IBY_PAY_INSTRUCTIONS_ALL.payment_instruction_id%TYPE) IS
2691 SELECT org_id,
2692 payment_reason_code
2693 FROM iby_pay_instructions_all
2694 WHERE
2695 payment_instruction_id = p_instruction_id;
2696
2697 -- Pick Up Required Data From Payments (IBY_PAYMENTS_ALL) using payment_instruction_id
2698 CURSOR iby_pmt_csr(p_instruction_id IBY_PAY_INSTRUCTIONS_ALL.payment_instruction_id%TYPE) IS
2699 SELECT pmts.payment_id,
2700 pmts.payment_instruction_id,
2701 pmts.payee_party_id,
2702 pmts.payee_le_registration_num,
2703 pmts.payment_amount,
2704 pmts.internal_bank_account_id,
2705 pmts.int_bank_account_name,
2706 pmts.int_bank_acct_agency_loc_code,
2707 pmts.external_bank_account_id,
2708 ext_ba.bank_account_name ext_bank_account_name,
2709 iby_ext_bankacct_pub.Uncipher_Bank_Number (ext_ba.bank_account_num,
2710 ext_ba.ba_num_sec_segment_id,
2711 iby_utility_pvt.get_view_param('SYS_KEY'),
2712 baek.subkey_cipher_text,
2713 baes.segment_cipher_text,
2714 baes.encoding_scheme,
2715 ext_ba.ba_mask_setting,
2716 ext_ba.ba_unmask_length) ext_bank_account_number,
2717 pmts.ext_branch_number,
2718 pmts.ext_bank_account_type
2719 FROM iby_payments_all pmts,
2720 iby_ext_bank_accounts ext_ba,
2721 IBY_SYS_SECURITY_SUBKEYS baek,
2722 IBY_SECURITY_SEGMENTS baes
2723 WHERE
2724 pmts.payment_instruction_id = p_instruction_id
2725 AND pmts.payment_status = 'INSTRUCTION_CREATED'
2726 AND pmts.external_bank_account_id = ext_ba.ext_bank_account_id(+)
2727 AND ext_ba.ba_num_sec_segment_id = baes.sec_segment_id(+)
2728 AND baes.sec_subkey_id = baek.sec_subkey_id(+);
2729
2730 -- Pick Up Required Data From Payable Documents (IBY_DOCS_PAYABLE_ALL) Using Payment Id From Payment Data
2731 CURSOR iby_docs_csr(p_pmt_id IBY_DOCS_PAYABLE_ALL.payment_id%TYPE) IS
2732 SELECT calling_app_doc_unique_ref2
2733 FROM iby_docs_payable_all
2734 WHERE
2735 payment_id = p_pmt_id
2736 AND
2737 calling_app_id=200
2738 AND
2739 UPPER(payment_function) IN ('PAYABLES_DISB','EMPLOYEE_EXP')
2740 AND document_status = 'PAYMENT_CREATED';
2741
2742
2743
2744 -- Declaring Record Types Of Various Cursors
2745 l_pay_instr_rec pay_instr_data_csr%ROWTYPE;
2746 l_iby_pmt_rec iby_pmt_csr%ROWTYPE;
2747 l_iby_docs_rec iby_docs_csr%ROWTYPE;
2748 l_org_id NUMBER;
2749
2750 l_format_name VARCHAR2(50);
2751 l_error_message VARCHAR2(1000);
2752 l_valid NUMBER;
2753
2754 BEGIN
2755 l_format_name := 'FVTPPPD';
2756 x_result:=g_SUCCESS;
2757
2758 -- Initializing the payment record
2759 IBY_VALIDATIONSETS_PUB.initInstructionData(p_instruction_id,l_instruction_rec);
2760
2761 l_docErrorRec.validation_set_code := p_validation_set_code;
2762 l_docErrorRec.transaction_id := p_instruction_id;
2763 l_docErrorRec.transaction_type := 'PAYMENT_INSTRUCTION';
2764 l_docErrorRec.calling_app_doc_unique_ref1 := p_instruction_id;
2765
2766 OPEN pay_instr_data_csr(p_instruction_id); -- Opening Instruction Data Cursor
2767 FETCH pay_instr_data_csr INTO l_pay_instr_rec; -- Getting Instruction Data
2768
2769 IF pay_instr_data_csr%FOUND THEN -- If Row Found Then Only Process Further
2770
2771 -- delete from FV_TP_TS_AMT_DATA to refresh data
2772 delete from FV_TP_TS_AMT_DATA where payment_instruction_id = p_instruction_id;
2773
2774 l_org_id:=l_pay_instr_rec.org_id;-- Extracting Org_ID
2775
2776 -- Do IBY_PAYMENT_FORMAT_VAL_PVT.SUPPLIER_TYPE at Payment Instruction Level
2777 -- Moved this to instruction level (Bug 5526640)
2778 IBY_PAYMENT_FORMAT_VAL_PVT.SUPPLIER_TYPE(l_format_name,
2779 p_instruction_id,
2780 l_docErrorTab,
2781 l_docErrorRec,
2782 l_valid,
2783 l_error_message);
2784
2785 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
2786 x_result:=1;
2787 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
2788 x_result:=1;
2789 RETURN;
2790 END IF;
2791
2792 OPEN iby_pmt_csr(p_instruction_id); -- Opening Payment Data Cursor
2793 LOOP -- Perform Validation For Each Of record in IBY_PAYMENTS_ALL
2794 FETCH iby_pmt_csr INTO l_iby_pmt_rec; -- Getting Payment Data
2795 EXIT WHEN iby_pmt_csr%NOTFOUND;
2796
2797 IBY_PAYMENT_FORMAT_VAL_PVT.RFC_ID(l_format_name,
2798 l_iby_pmt_rec.payment_id,
2799 l_docErrorTab,
2800 l_docErrorRec,
2801 l_valid,
2802 l_error_message);
2803
2804 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
2805 x_result:=1;
2806 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
2807 x_result:=1;
2808 RETURN;
2809 END IF;
2810
2811 -- Do IBY_PAYMENT_FORMAT_VAL_PVT.AGENCY_ADDRESS
2812 IBY_PAYMENT_FORMAT_VAL_PVT.AGENCY_ADDRESS(l_format_name,
2813 l_org_id,
2814 l_docErrorTab,
2815 l_docErrorRec,
2816 l_valid,
2817 l_error_message);
2818 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
2819 x_result:=1;
2820 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
2821 x_result:=1;
2822 RETURN;
2823 END IF;
2824
2825
2826 -- Do IBY_PAYMENT_FORMAT_VAL_PVT.MAX_PAYMENT_AMT_2 at Payment Level
2827 IBY_PAYMENT_FORMAT_VAL_PVT.MAX_PAYMENT_AMT_2(l_format_name,
2828 l_iby_pmt_rec.payment_instruction_id,
2829 l_iby_pmt_rec.payment_amount,
2830 l_docErrorTab,
2831 l_docErrorRec,
2832 l_valid,
2833 l_error_message);
2834
2835 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
2836 x_result:=1;
2837 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
2838 x_result:=1;
2839 RETURN;
2840 END IF;
2841
2842
2843 OPEN iby_docs_csr(l_iby_pmt_rec.payment_id); -- Opening Payable Documents Data
2844 LOOP -- Perform Validation For Each Of record in IBY_DOCS_PAYABLE_ALL
2845 FETCH iby_docs_csr INTO l_iby_docs_rec; -- Getting Payable Documents Data
2846 EXIT WHEN iby_docs_csr%NOTFOUND;
2847
2848 -- validate internal bank account parameters
2849 IBY_PAYMENT_FORMAT_VAL_PVT.AGENCY_LOCATION_CODE(l_format_name,
2850 l_iby_pmt_rec.int_bank_acct_agency_loc_code,
2851 l_iby_pmt_rec.int_bank_account_name,
2852 l_docErrorTab,
2853 l_docErrorRec,
2854 l_valid,
2855 l_error_message);
2856
2857 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
2858 x_result:=1;
2859 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
2860 x_result:=1;
2861 RETURN;
2862 END IF;
2863
2864 -- validate external bank account parameters
2865 -- validate external bank account id
2866 IBY_PAYMENT_FORMAT_VAL_PVT.EXTERNAL_BANK_ACCOUNT_ID(l_format_name,
2867 l_iby_pmt_rec.external_bank_account_id,
2868 l_docErrorTab,
2869 l_docErrorRec,
2870 l_valid,
2871 l_error_message);
2872 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
2873 x_result:=1;
2874 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
2875 x_result:=1;
2876 RETURN;
2877 END IF;
2878
2879
2880 IBY_PAYMENT_FORMAT_VAL_PVT.ACCOUNT_TYPE(l_format_name,
2881 l_iby_pmt_rec.ext_bank_account_type,
2882 l_iby_pmt_rec.ext_bank_account_name,
2883 TO_NUMBER(l_iby_docs_rec.calling_app_doc_unique_ref2),
2884 l_iby_pmt_rec.payment_id,
2885 l_docErrorTab,
2886 l_docErrorRec,
2887 l_valid,
2888 l_error_message);
2889
2890 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
2891 x_result:=1;
2892 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
2893 x_result:=1;
2894 RETURN;
2895 END IF;
2896
2897
2898 IBY_PAYMENT_FORMAT_VAL_PVT.DEPOSITER_ACC_NUM(l_format_name,
2899 l_iby_pmt_rec.ext_bank_account_number,
2900 l_docErrorTab,
2901 l_docErrorRec,
2902 l_valid,
2903 l_error_message);
2904
2905 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
2906 x_result:=1;
2907 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
2908 x_result:=1;
2909 RETURN;
2910 END IF;
2911
2912
2913
2914 IBY_PAYMENT_FORMAT_VAL_PVT.RTN_NUMBER(l_format_name,
2915 l_iby_pmt_rec.ext_branch_number,
2916 l_docErrorTab,
2917 l_docErrorRec,
2918 l_valid,
2919 l_error_message);
2920
2921 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
2922 x_result:=1;
2923 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
2924 x_result:=1;
2925 RETURN;
2926 END IF;
2927
2928
2929 IBY_PAYMENT_FORMAT_VAL_PVT.PAY_ALONE_OPTION(l_format_name,
2930 TO_NUMBER(l_iby_docs_rec.calling_app_doc_unique_ref2),
2931 l_iby_pmt_rec.payment_id,
2932 l_docErrorTab,
2933 l_docErrorRec,
2934 l_valid,
2935 l_error_message);
2936 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
2937 x_result:=1;
2938 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
2939 x_result:=1;
2940 RETURN;
2941 END IF;
2942
2943
2944 IBY_PAYMENT_FORMAT_VAL_PVT.TREASURY_SYMBOLS_PROCESS(l_format_name,
2945 p_instruction_id,
2946 l_iby_pmt_rec.payment_id,
2947 TO_NUMBER(l_iby_docs_rec.calling_app_doc_unique_ref2),
2948 l_docErrorTab,
2949 l_docErrorRec,
2950 l_valid,
2951 l_error_message);
2952 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
2953 x_result:=1;
2954 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
2955 x_result:=1;
2956 RETURN;
2957 END IF;
2958
2959
2960
2961 IBY_PAYMENT_FORMAT_VAL_PVT.PAYEE_SSN(l_format_name,
2962 l_iby_pmt_rec.payee_le_registration_num,
2963 l_iby_pmt_rec.payee_party_id,
2964 l_docErrorTab,
2965 l_docErrorRec,
2966 l_valid,
2967 l_error_message);
2968 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
2969 x_result:=1;
2970 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
2971 x_result:=1;
2972 RETURN;
2973 END IF;
2974
2975
2976
2977
2978 END LOOP;-- End Of Documents Cursor Loop
2979 CLOSE iby_docs_csr;-- Closing Payable Documents Cursor
2980
2981 END LOOP;-- End Of Payments Cursor Loop
2982 CLOSE iby_pmt_csr; -- Closing Payments Cursor
2983
2984 IBY_PAYMENT_FORMAT_VAL_PVT.SCHEDULE_NUMBER(l_format_name,
2985 p_instruction_id,
2986 l_docErrorTab,
2987 l_docErrorRec,
2988 l_valid,
2989 l_error_message);
2990
2991 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
2992 x_result:=1;
2993 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
2994 x_result:=1;
2995 RETURN;
2996 END IF;
2997
2998 IBY_PAYMENT_FORMAT_VAL_PVT.MANDATORY_PPD_PPDP_REASON_CODE(l_format_name,
2999 l_pay_instr_rec.payment_reason_code,
3000 l_docErrorTab,
3001 l_docErrorRec,
3002 l_valid,
3003 l_error_message);
3004
3005 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
3006 x_result:=1;
3007 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
3008 x_result:=1;
3009 RETURN;
3010 END IF;
3011
3012
3013
3014 END IF; -- End of IF pay_instr_data_csr%FOUND
3015 CLOSE pay_instr_data_csr; -- Closing Instruction Data Cursor
3016
3017 IBY_VALIDATIONSETS_PUB.insert_transaction_errors('N',l_docErrorTab);
3018
3019 EXCEPTION
3020 WHEN OTHERS THEN
3021 x_result := 1;
3022 l_docErrorRec.transaction_error_id := null;
3023 l_docErrorRec.error_code := 'UNEXPECTED_ERROR';
3024 l_docErrorRec.error_message := SQLERRM;
3025 IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(l_docErrorRec, l_docErrorTab);
3026 iby_payment_format_val_pvt.log_error_messages(FND_LOG.LEVEL_STATEMENT, l_format_name, SQLERRM);
3027 IBY_VALIDATIONSETS_PUB.insert_transaction_errors('N',l_docErrorTab);
3028
3029
3030 END FVTPPPD;-- End of Procedure FVTPPPD
3031 ----------------------------------------------------------------------------------------------------------
3032 /*
3033
3034 PROCEDURE : FVTPPPDP
3035
3036 ECS PPDP Vendor Payment Format
3037
3038 */
3039
3040 PROCEDURE FVTPPPDP
3041 (
3042 p_validation_assign_id IN IBY_VAL_ASSIGNMENTS.validation_assignment_id%TYPE,
3043 p_validation_set_code IN IBY_VALIDATION_SETS_VL.validation_set_code%TYPE,
3044 p_instruction_id IN IBY_PAY_INSTRUCTIONS_ALL.payment_instruction_id%TYPE,
3045 p_is_online_val IN VARCHAR2,
3046 x_result OUT NOCOPY NUMBER
3047 )IS
3048
3049 l_instruction_rec IBY_VALIDATIONSETS_PUB.instructionRecType;
3050 l_docErrorTab IBY_VALIDATIONSETS_PUB.docErrorTabType;
3051 l_docErrorRec IBY_TRANSACTION_ERRORS%ROWTYPE;
3052
3053 -- Pick Up The Required Data From Instructions (IBY_PAY_INSTRUCTIONS_ALL) using payemnt_instruction_id
3054 CURSOR pay_instr_data_csr(p_instruction_id IBY_PAY_INSTRUCTIONS_ALL.payment_instruction_id%TYPE) IS
3055 SELECT org_id,
3056 payment_reason_code
3057 FROM iby_pay_instructions_all
3058 WHERE
3059 payment_instruction_id = p_instruction_id;
3060
3061 -- Pick Up Required Data From Payments (IBY_PAYMENTS_ALL) using payment_instruction_id
3062 CURSOR iby_pmt_csr(p_instruction_id IBY_PAY_INSTRUCTIONS_ALL.payment_instruction_id%TYPE) IS
3063 SELECT pmts.payment_id,
3064 pmts.payment_instruction_id,
3065 pmts.payee_party_id,
3066 pmts.payee_le_registration_num,
3067 pmts.payment_amount,
3068 pmts.internal_bank_account_id,
3069 pmts.int_bank_account_name,
3070 pmts.int_bank_acct_agency_loc_code,
3071 pmts.external_bank_account_id,
3072 ext_ba.bank_account_name ext_bank_account_name,
3073 iby_ext_bankacct_pub.Uncipher_Bank_Number (ext_ba.bank_account_num,
3074 ext_ba.ba_num_sec_segment_id,
3075 iby_utility_pvt.get_view_param('SYS_KEY'),
3076 baek.subkey_cipher_text,
3077 baes.segment_cipher_text,
3078 baes.encoding_scheme,
3079 ext_ba.ba_mask_setting,
3080 ext_ba.ba_unmask_length) ext_bank_account_number,
3081 pmts.ext_branch_number,
3082 pmts.ext_bank_account_type
3083 FROM iby_payments_all pmts,
3084 iby_ext_bank_accounts ext_ba,
3085 IBY_SYS_SECURITY_SUBKEYS baek,
3086 IBY_SECURITY_SEGMENTS baes
3087 WHERE
3088 pmts.payment_instruction_id = p_instruction_id
3089 AND pmts.payment_status = 'INSTRUCTION_CREATED'
3090 AND pmts.external_bank_account_id = ext_ba.ext_bank_account_id(+)
3091 AND ext_ba.ba_num_sec_segment_id = baes.sec_segment_id(+)
3092 AND baes.sec_subkey_id = baek.sec_subkey_id(+);
3093
3094 -- Pick Up Required Data From Payable Documents (IBY_DOCS_PAYABLE_ALL) Using Payment Id From Payment Data
3095 CURSOR iby_docs_csr(p_pmt_id IBY_DOCS_PAYABLE_ALL.payment_id%TYPE) IS
3096 SELECT calling_app_doc_unique_ref2
3097 FROM iby_docs_payable_all
3098 WHERE
3099 payment_id = p_pmt_id
3100 AND
3101 calling_app_id=200
3102 AND
3103 UPPER(payment_function) IN ('PAYABLES_DISB','EMPLOYEE_EXP')
3104 AND document_status = 'PAYMENT_CREATED';
3105
3106
3107
3108 -- Declaring Record Types Of Various Cursors
3109 l_pay_instr_rec pay_instr_data_csr%ROWTYPE;
3110 l_iby_pmt_rec iby_pmt_csr%ROWTYPE;
3111 l_iby_docs_rec iby_docs_csr%ROWTYPE;
3112 l_org_id NUMBER;
3113
3114 l_format_name VARCHAR2(50);
3115 l_error_message VARCHAR2(1000);
3116 l_valid NUMBER;
3117
3118 BEGIN
3119 l_format_name := 'FVTPPPDP';
3120 x_result:=g_SUCCESS;
3121
3122 -- Initializing the payment record
3123 IBY_VALIDATIONSETS_PUB.initInstructionData(p_instruction_id,l_instruction_rec);
3124
3125 l_docErrorRec.validation_set_code := p_validation_set_code;
3126 l_docErrorRec.transaction_id := p_instruction_id;
3127 l_docErrorRec.transaction_type := 'PAYMENT_INSTRUCTION';
3128 l_docErrorRec.calling_app_doc_unique_ref1 := p_instruction_id;
3129
3130 OPEN pay_instr_data_csr(p_instruction_id); -- Opening Instruction Data Cursor
3131 FETCH pay_instr_data_csr INTO l_pay_instr_rec; -- Getting Instruction Data
3132
3133 IF pay_instr_data_csr%FOUND THEN -- If Row Found Then Only Process Further
3134
3135 -- delete from FV_TP_TS_AMT_DATA to refresh data
3136 delete from FV_TP_TS_AMT_DATA where payment_instruction_id = p_instruction_id;
3137
3138 l_org_id:=l_pay_instr_rec.org_id;-- Extracting Org_ID
3139
3140
3141 -- Do IBY_PAYMENT_FORMAT_VAL_PVT.SUPPLIER_TYPE at Payment Instruction Level
3142 -- Moved this to instruction level (Bug 5526640)
3143 IBY_PAYMENT_FORMAT_VAL_PVT.SUPPLIER_TYPE(l_format_name,
3144 p_instruction_id,
3145 l_docErrorTab,
3146 l_docErrorRec,
3147 l_valid,
3148 l_error_message);
3149
3150 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
3151 x_result:=1;
3152 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
3153 x_result:=1;
3154 RETURN;
3155 END IF;
3156
3157
3158 OPEN iby_pmt_csr(p_instruction_id); -- Opening Payment Data Cursor
3159 LOOP -- Perform Validation For Each Of record in IBY_PAYMENTS_ALL
3160 FETCH iby_pmt_csr INTO l_iby_pmt_rec; -- Getting Payment Data
3161 EXIT WHEN iby_pmt_csr%NOTFOUND;
3162
3163
3164 IBY_PAYMENT_FORMAT_VAL_PVT.RFC_ID(l_format_name,
3165 l_iby_pmt_rec.payment_id,
3166 l_docErrorTab,
3167 l_docErrorRec,
3168 l_valid,
3169 l_error_message);
3170
3171 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
3172 x_result:=1;
3173 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
3174 x_result:=1;
3175 RETURN;
3176 END IF;
3177
3178 -- Do IBY_PAYMENT_FORMAT_VAL_PVT.AGENCY_ADDRESS
3179 IBY_PAYMENT_FORMAT_VAL_PVT.AGENCY_ADDRESS(l_format_name,
3180 l_org_id,
3181 l_docErrorTab,
3182 l_docErrorRec,
3183 l_valid,
3184 l_error_message);
3185 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
3186 x_result:=1;
3187 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
3188 x_result:=1;
3189 RETURN;
3190 END IF;
3191
3192 -- Do IBY_PAYMENT_FORMAT_VAL_PVT.MAX_PAYMENT_AMT_2 at Payment Level
3193 IBY_PAYMENT_FORMAT_VAL_PVT.MAX_PAYMENT_AMT_2(l_format_name,
3194 l_iby_pmt_rec.payment_instruction_id,
3195 l_iby_pmt_rec.payment_amount,
3196 l_docErrorTab,
3197 l_docErrorRec,
3198 l_valid,
3199 l_error_message);
3200 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
3201 x_result:=1;
3202 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
3203 x_result:=1;
3204 RETURN;
3205 END IF;
3206
3207
3208 OPEN iby_docs_csr(l_iby_pmt_rec.payment_id); -- Opening Payable Documents Data
3209 LOOP -- Perform Validation For Each Of record in IBY_DOCS_PAYABLE_ALL
3210 FETCH iby_docs_csr INTO l_iby_docs_rec; -- Getting Payable Documents Data
3211 EXIT WHEN iby_docs_csr%NOTFOUND;
3212
3213 -- validate internal bank account parameters
3214 IBY_PAYMENT_FORMAT_VAL_PVT.AGENCY_LOCATION_CODE(l_format_name,
3215 l_iby_pmt_rec.int_bank_acct_agency_loc_code,
3216 l_iby_pmt_rec.int_bank_account_name,
3217 l_docErrorTab,
3218 l_docErrorRec,
3219 l_valid,
3220 l_error_message);
3221
3222 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
3223 x_result:=1;
3224 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
3225 x_result:=1;
3226 RETURN;
3227 END IF;
3228
3229 -- validate external bank account parameters
3230 -- validate external bank account id
3231 IBY_PAYMENT_FORMAT_VAL_PVT.EXTERNAL_BANK_ACCOUNT_ID(l_format_name,
3232 l_iby_pmt_rec.external_bank_account_id,
3233 l_docErrorTab,
3234 l_docErrorRec,
3235 l_valid,
3236 l_error_message);
3237 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
3238 x_result:=1;
3239 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
3240 x_result:=1;
3241 RETURN;
3242 END IF;
3243
3244
3245 IBY_PAYMENT_FORMAT_VAL_PVT.ACCOUNT_TYPE(l_format_name,
3246 l_iby_pmt_rec.ext_bank_account_type,
3247 l_iby_pmt_rec.ext_bank_account_name,
3248 TO_NUMBER(l_iby_docs_rec.calling_app_doc_unique_ref2),
3249 l_iby_pmt_rec.payment_id,
3250 l_docErrorTab,
3251 l_docErrorRec,
3252 l_valid,
3253 l_error_message);
3254 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
3255 x_result:=1;
3256 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
3257 x_result:=1;
3258 RETURN;
3259 END IF;
3260
3261 IBY_PAYMENT_FORMAT_VAL_PVT.DEPOSITER_ACC_NUM(l_format_name,
3262 l_iby_pmt_rec.ext_bank_account_number,
3263 l_docErrorTab,
3264 l_docErrorRec,
3265 l_valid,
3266 l_error_message);
3267
3268 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
3269 x_result:=1;
3270 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
3271 x_result:=1;
3272 RETURN;
3273 END IF;
3274
3275 IBY_PAYMENT_FORMAT_VAL_PVT.RTN_NUMBER(l_format_name,
3276 l_iby_pmt_rec.ext_branch_number,
3277 l_docErrorTab,
3278 l_docErrorRec,
3279 l_valid,
3280 l_error_message);
3281
3282 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
3283 x_result:=1;
3284 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
3285 x_result:=1;
3286 RETURN;
3287 END IF;
3288
3289
3290
3291 IBY_PAYMENT_FORMAT_VAL_PVT.PAY_ALONE_OPTION(l_format_name,
3292 TO_NUMBER(l_iby_docs_rec.calling_app_doc_unique_ref2),
3293 l_iby_pmt_rec.payment_id,
3294 l_docErrorTab,
3295 l_docErrorRec,
3296 l_valid,
3297 l_error_message);
3298 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
3299 x_result:=1;
3300 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
3301 x_result:=1;
3302 RETURN;
3303 END IF;
3304
3305
3306 IBY_PAYMENT_FORMAT_VAL_PVT.TREASURY_SYMBOLS_PROCESS(l_format_name,
3307 p_instruction_id,
3308 l_iby_pmt_rec.payment_id,
3309 TO_NUMBER(l_iby_docs_rec.calling_app_doc_unique_ref2),
3310 l_docErrorTab,
3311 l_docErrorRec,
3312 l_valid,
3313 l_error_message);
3314 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
3315 x_result:=1;
3316 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
3317 x_result:=1;
3318 RETURN;
3319 END IF;
3320
3321
3322
3323 IBY_PAYMENT_FORMAT_VAL_PVT.PAYEE_SSN(l_format_name,
3324 l_iby_pmt_rec.payee_le_registration_num,
3325 l_iby_pmt_rec.payee_party_id,
3326 l_docErrorTab,
3327 l_docErrorRec,
3328 l_valid,
3329 l_error_message);
3330 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
3331 x_result:=1;
3332 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
3333 x_result:=1;
3334 RETURN;
3335 END IF;
3336
3337
3338
3339
3340 END LOOP;-- End Of Documents Cursor Loop
3341 CLOSE iby_docs_csr;-- Closing Payable Documents Cursor
3342
3343 END LOOP;-- End Of Payments Cursor Loop
3344 CLOSE iby_pmt_csr; -- Closing Payments Cursor
3345
3346 IBY_PAYMENT_FORMAT_VAL_PVT.SCHEDULE_NUMBER(l_format_name,
3347 p_instruction_id,
3348 l_docErrorTab,
3349 l_docErrorRec,
3350 l_valid,
3351 l_error_message);
3352
3353 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
3354 x_result:=1;
3355 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
3356 x_result:=1;
3357 RETURN;
3358 END IF;
3359
3360 IBY_PAYMENT_FORMAT_VAL_PVT.MANDATORY_PPD_PPDP_REASON_CODE(l_format_name,
3361 l_pay_instr_rec.payment_reason_code,
3362 l_docErrorTab,
3363 l_docErrorRec,
3364 l_valid,
3365 l_error_message);
3366
3367 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
3368 x_result:=1;
3369 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
3370 x_result:=1;
3371 RETURN;
3372 END IF;
3373
3374 END IF; -- End of IF pay_instr_data_csr%FOUND
3375 CLOSE pay_instr_data_csr; -- Closing Instruction Data Cursor
3376
3377 IBY_VALIDATIONSETS_PUB.insert_transaction_errors('N',l_docErrorTab);
3378
3379 EXCEPTION
3380 WHEN OTHERS THEN
3381 x_result := 1;
3382 l_docErrorRec.transaction_error_id := null;
3383 l_docErrorRec.error_code := 'UNEXPECTED_ERROR';
3384 l_docErrorRec.error_message := SQLERRM;
3385 IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(l_docErrorRec, l_docErrorTab);
3386 iby_payment_format_val_pvt.log_error_messages(FND_LOG.LEVEL_STATEMENT, l_format_name, SQLERRM);
3387 IBY_VALIDATIONSETS_PUB.insert_transaction_errors('N',l_docErrorTab);
3388
3389
3390 END FVTPPPDP;-- End of Procedure FVTPPPDP
3391 ----------------------------------------------------------------------------------------------------------
3392 /*
3393
3394 PROCEDURE : FVSPCCD
3395
3396 SPS CCD Vendor Payment Format
3397
3398 */
3399
3400 PROCEDURE FVSPCCD
3401 (
3402 p_validation_assign_id IN IBY_VAL_ASSIGNMENTS.validation_assignment_id%TYPE,
3403 p_validation_set_code IN IBY_VALIDATION_SETS_VL.validation_set_code%TYPE,
3404 p_instruction_id IN IBY_PAY_INSTRUCTIONS_ALL.payment_instruction_id%TYPE,
3405 p_is_online_val IN VARCHAR2,
3406 x_result OUT NOCOPY NUMBER
3407 )IS
3408
3409 l_instruction_rec IBY_VALIDATIONSETS_PUB.instructionRecType;
3410 l_docErrorTab IBY_VALIDATIONSETS_PUB.docErrorTabType;
3411 l_docErrorRec IBY_TRANSACTION_ERRORS%ROWTYPE;
3412
3413 -- Pick Up The Required Data From Instructions (IBY_PAY_INSTRUCTIONS_ALL) using payemnt_instruction_id
3414 CURSOR pay_instr_data_csr(p_instruction_id IBY_PAY_INSTRUCTIONS_ALL.payment_instruction_id%TYPE) IS
3415 SELECT org_id
3416 FROM iby_pay_instructions_all
3417 WHERE
3418 payment_instruction_id = p_instruction_id;
3419
3420 -- Pick Up Required Data From Payments (IBY_PAYMENTS_ALL) using payment_instruction_id
3421 CURSOR iby_pmt_csr(p_instruction_id IBY_PAY_INSTRUCTIONS_ALL.payment_instruction_id%TYPE) IS
3422 SELECT pmts.payment_id,
3423 pmts.payment_instruction_id,
3424 pmts.payee_party_id,
3425 pmts.payee_le_registration_num,
3426 pmts.payment_amount,
3427 pmts.internal_bank_account_id,
3428 pmts.int_bank_account_name,
3429 pmts.int_bank_acct_agency_loc_code,
3430 pmts.external_bank_account_id,
3431 ext_ba.bank_account_name ext_bank_account_name,
3432 iby_ext_bankacct_pub.Uncipher_Bank_Number (ext_ba.bank_account_num,
3433 ext_ba.ba_num_sec_segment_id,
3434 iby_utility_pvt.get_view_param('SYS_KEY'),
3435 baek.subkey_cipher_text,
3436 baes.segment_cipher_text,
3437 baes.encoding_scheme,
3438 ext_ba.ba_mask_setting,
3439 ext_ba.ba_unmask_length) ext_bank_account_number,
3440 pmts.ext_branch_number,
3441 pmts.ext_bank_account_type
3442 FROM iby_payments_all pmts,
3443 iby_ext_bank_accounts ext_ba,
3444 IBY_SYS_SECURITY_SUBKEYS baek,
3445 IBY_SECURITY_SEGMENTS baes
3446 WHERE
3447 pmts.payment_instruction_id = p_instruction_id
3448 AND pmts.payment_status = 'INSTRUCTION_CREATED'
3449 AND pmts.external_bank_account_id = ext_ba.ext_bank_account_id(+)
3450 AND ext_ba.ba_num_sec_segment_id = baes.sec_segment_id(+)
3451 AND baes.sec_subkey_id = baek.sec_subkey_id(+);
3452
3453 -- Pick Up Required Data From Payable Documents (IBY_DOCS_PAYABLE_ALL) Using Payment Id From Payment Data
3454 CURSOR iby_docs_csr(p_pmt_id IBY_DOCS_PAYABLE_ALL.payment_id%TYPE) IS
3455 SELECT calling_app_doc_unique_ref2
3456 FROM iby_docs_payable_all
3457 WHERE
3458 payment_id = p_pmt_id
3459 AND
3460 calling_app_id=200
3461 AND
3462 UPPER(payment_function) IN ('PAYABLES_DISB','EMPLOYEE_EXP')
3463 AND document_status = 'PAYMENT_CREATED';
3464
3465
3466
3467 -- Declaring Record Types Of Various Cursors
3468 l_pay_instr_rec pay_instr_data_csr%ROWTYPE;
3469 l_iby_pmt_rec iby_pmt_csr%ROWTYPE;
3470 l_iby_docs_rec iby_docs_csr%ROWTYPE;
3471 l_org_id NUMBER;
3472
3473 l_format_name VARCHAR2(50);
3474 l_error_message VARCHAR2(1000);
3475 l_valid NUMBER;
3476
3477 BEGIN
3478 l_format_name := 'FVSPCCD';
3479 x_result:=g_SUCCESS;
3480
3481 -- Initializing the payment record
3482 IBY_VALIDATIONSETS_PUB.initInstructionData(p_instruction_id,l_instruction_rec);
3483
3484 l_docErrorRec.validation_set_code := p_validation_set_code;
3485 l_docErrorRec.transaction_id := p_instruction_id;
3486 l_docErrorRec.transaction_type := 'PAYMENT_INSTRUCTION';
3487 l_docErrorRec.calling_app_doc_unique_ref1 := p_instruction_id;
3488
3489 OPEN pay_instr_data_csr(p_instruction_id); -- Opening Instruction Data Cursor
3490 FETCH pay_instr_data_csr INTO l_pay_instr_rec; -- Getting Instruction Data
3491
3492 IF pay_instr_data_csr%FOUND THEN -- If Row Found Then Only Process Further
3493
3494 -- delete from FV_TP_TS_AMT_DATA to refresh data
3495 delete from FV_TP_TS_AMT_DATA where payment_instruction_id = p_instruction_id;
3496
3497 l_org_id:=l_pay_instr_rec.org_id;-- Extracting Org_ID
3498
3499 -- Do IBY_PAYMENT_FORMAT_VAL_PVT.SUPPLIER_TYPE at Payment Instruction Level
3500 -- Moved this to instruction level (Bug 5526640)
3501 IBY_PAYMENT_FORMAT_VAL_PVT.SUPPLIER_TYPE(l_format_name,
3502 p_instruction_id,
3503 l_docErrorTab,
3504 l_docErrorRec,
3505 l_valid,
3506 l_error_message);
3507
3508 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
3509 x_result:=1;
3510 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
3511 x_result:=1;
3512 RETURN;
3513 END IF;
3514
3515 OPEN iby_pmt_csr(p_instruction_id); -- Opening Payment Data Cursor
3516 LOOP -- Perform Validation For Each Of record in IBY_PAYMENTS_ALL
3517 FETCH iby_pmt_csr INTO l_iby_pmt_rec; -- Getting Payment Data
3518 EXIT WHEN iby_pmt_csr%NOTFOUND;
3519
3520
3521 OPEN iby_docs_csr(l_iby_pmt_rec.payment_id); -- Opening Payable Documents Data
3522 LOOP -- Perform Validation For Each Of record in IBY_DOCS_PAYABLE_ALL
3523 FETCH iby_docs_csr INTO l_iby_docs_rec; -- Getting Payable Documents Data
3524 EXIT WHEN iby_docs_csr%NOTFOUND;
3525
3526 -- validate internal bank account parameters
3527 IBY_PAYMENT_FORMAT_VAL_PVT.AGENCY_LOCATION_CODE(l_format_name,
3528 l_iby_pmt_rec.int_bank_acct_agency_loc_code,
3529 l_iby_pmt_rec.int_bank_account_name,
3530 l_docErrorTab,
3531 l_docErrorRec,
3532 l_valid,
3533 l_error_message);
3534 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
3535 x_result:=1;
3536 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
3537 x_result:=1;
3538 RETURN;
3539 END IF;
3540
3541 -- validate external bank account parameters
3542 -- validate external bank account id
3543 IBY_PAYMENT_FORMAT_VAL_PVT.EXTERNAL_BANK_ACCOUNT_ID(l_format_name,
3544 l_iby_pmt_rec.external_bank_account_id,
3545 l_docErrorTab,
3546 l_docErrorRec,
3547 l_valid,
3548 l_error_message);
3549 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
3550 x_result:=1;
3551 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
3552 x_result:=1;
3553 RETURN;
3554 END IF;
3555
3556
3557 IBY_PAYMENT_FORMAT_VAL_PVT.ACCOUNT_TYPE(l_format_name,
3558 l_iby_pmt_rec.ext_bank_account_type,
3559 l_iby_pmt_rec.ext_bank_account_name,
3560 TO_NUMBER(l_iby_docs_rec.calling_app_doc_unique_ref2),
3561 l_iby_pmt_rec.payment_id,
3562 l_docErrorTab,
3563 l_docErrorRec,
3564 l_valid,
3565 l_error_message);
3566 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
3567 x_result:=1;
3568 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
3569 x_result:=1;
3570 RETURN;
3571 END IF;
3572
3573 IBY_PAYMENT_FORMAT_VAL_PVT.DEPOSITER_ACC_NUM(l_format_name,
3574 l_iby_pmt_rec.ext_bank_account_number,
3575 l_docErrorTab,
3576 l_docErrorRec,
3577 l_valid,
3578 l_error_message);
3579
3580 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
3581 x_result:=1;
3582 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
3583 x_result:=1;
3584 RETURN;
3585 END IF;
3586
3587 IBY_PAYMENT_FORMAT_VAL_PVT.RTN_NUMBER(l_format_name,
3588 l_iby_pmt_rec.ext_branch_number,
3589 l_docErrorTab,
3590 l_docErrorRec,
3591 l_valid,
3592 l_error_message);
3593
3594 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
3595 x_result:=1;
3596 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
3597 x_result:=1;
3598 RETURN;
3599 END IF;
3600
3601 IBY_PAYMENT_FORMAT_VAL_PVT.PAY_ALONE_OPTION(l_format_name,
3602 TO_NUMBER(l_iby_docs_rec.calling_app_doc_unique_ref2),
3603 l_iby_pmt_rec.payment_id,
3604 l_docErrorTab,
3605 l_docErrorRec,
3606 l_valid,
3607 l_error_message);
3608 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
3609 x_result:=1;
3610 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
3611 x_result:=1;
3612 RETURN;
3613 END IF;
3614
3615
3616 IBY_PAYMENT_FORMAT_VAL_PVT.TREASURY_SYMBOLS_PROCESS(l_format_name,
3617 p_instruction_id,
3618 l_iby_pmt_rec.payment_id,
3619 TO_NUMBER(l_iby_docs_rec.calling_app_doc_unique_ref2),
3620 l_docErrorTab,
3621 l_docErrorRec,
3622 l_valid,
3623 l_error_message);
3624 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
3625 x_result:=1;
3626 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
3627 x_result:=1;
3628 RETURN;
3629 END IF;
3630
3631 --IBY_PAYMENT_FORMAT_VAL_PVT.TAS_VALIDATION Is Directly Called From IBY_PAYMENT_FORMAT_VAL_PVT.TREASURY_SYMBOLS_PROCESS
3632
3633
3634
3635 IBY_PAYMENT_FORMAT_VAL_PVT.PAYEE_SSN(l_format_name,
3636 l_iby_pmt_rec.payee_le_registration_num,
3637 l_iby_pmt_rec.payee_party_id,
3638 l_docErrorTab,
3639 l_docErrorRec,
3640 l_valid,
3641 l_error_message);
3642 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
3643 x_result:=1;
3644 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
3645 x_result:=1;
3646 RETURN;
3647 END IF;
3648
3649
3650
3651
3652 END LOOP;-- End Of Documents Cursor Loop
3653 CLOSE iby_docs_csr;-- Closing Payable Documents Cursor
3654
3655 END LOOP;-- End Of Payments Cursor Loop
3656 CLOSE iby_pmt_csr; -- Closing Payments Cursor
3657
3658 IBY_PAYMENT_FORMAT_VAL_PVT.MAX_TREASURY_SYMBOLS(l_format_name,
3659 p_instruction_id,
3660 l_docErrorTab,
3661 l_docErrorRec,
3662 l_valid,
3663 l_error_message);
3664
3665 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
3666 x_result:=1;
3667 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
3668 x_result:=1;
3669 RETURN;
3670 END IF;
3671
3672 IBY_PAYMENT_FORMAT_VAL_PVT.SCHEDULE_NUMBER(l_format_name,
3673 p_instruction_id,
3674 l_docErrorTab,
3675 l_docErrorRec,
3676 l_valid,
3677 l_error_message);
3678
3679 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
3680 x_result:=1;
3681 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
3682 x_result:=1;
3683 RETURN;
3684 END IF;
3685
3686
3687
3688 END IF; -- End of IF pay_instr_data_csr%FOUND
3689 CLOSE pay_instr_data_csr; -- Closing Instruction Data Cursor
3690
3691 IBY_VALIDATIONSETS_PUB.insert_transaction_errors('N',l_docErrorTab);
3692
3693 EXCEPTION
3694 WHEN OTHERS THEN
3695 x_result := 1;
3696 l_docErrorRec.transaction_error_id := null;
3697 l_docErrorRec.error_code := 'UNEXPECTED_ERROR';
3698 l_docErrorRec.error_message := SQLERRM;
3699 IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(l_docErrorRec, l_docErrorTab);
3700 iby_payment_format_val_pvt.log_error_messages(FND_LOG.LEVEL_STATEMENT, l_format_name, SQLERRM);
3701 IBY_VALIDATIONSETS_PUB.insert_transaction_errors('N',l_docErrorTab);
3702
3703
3704 END FVSPCCD;-- End of Procedure FVSPCCD
3705 ----------------------------------------------------------------------------------------------------------
3706 /*
3707
3708 PROCEDURE : FVSPCCDP
3709
3710 SPS CCDP Vendor Payment Format
3711
3712 */
3713
3714 PROCEDURE FVSPCCDP
3715 (
3716 p_validation_assign_id IN IBY_VAL_ASSIGNMENTS.validation_assignment_id%TYPE,
3717 p_validation_set_code IN IBY_VALIDATION_SETS_VL.validation_set_code%TYPE,
3718 p_instruction_id IN IBY_PAY_INSTRUCTIONS_ALL.payment_instruction_id%TYPE,
3719 p_is_online_val IN VARCHAR2,
3720 x_result OUT NOCOPY NUMBER
3721 )IS
3722
3723 l_instruction_rec IBY_VALIDATIONSETS_PUB.instructionRecType;
3724 l_docErrorTab IBY_VALIDATIONSETS_PUB.docErrorTabType;
3725 l_docErrorRec IBY_TRANSACTION_ERRORS%ROWTYPE;
3726
3727 -- Pick Up The Required Data From Instructions (IBY_PAY_INSTRUCTIONS_ALL) using payemnt_instruction_id
3728 CURSOR pay_instr_data_csr(p_instruction_id IBY_PAY_INSTRUCTIONS_ALL.payment_instruction_id%TYPE) IS
3729 SELECT org_id
3730 FROM iby_pay_instructions_all
3731 WHERE
3732 payment_instruction_id = p_instruction_id;
3733
3734 -- Pick Up Required Data From Payments (IBY_PAYMENTS_ALL) using payment_instruction_id
3735 CURSOR iby_pmt_csr(p_instruction_id IBY_PAY_INSTRUCTIONS_ALL.payment_instruction_id%TYPE) IS
3736 SELECT pmts.payment_id,
3737 pmts.payment_instruction_id,
3738 pmts.payee_party_id,
3739 pmts.payee_le_registration_num,
3740 pmts.payment_amount,
3741 pmts.internal_bank_account_id,
3742 pmts.int_bank_account_name,
3743 pmts.int_bank_acct_agency_loc_code,
3744 pmts.external_bank_account_id,
3745 ext_ba.bank_account_name ext_bank_account_name,
3746 iby_ext_bankacct_pub.Uncipher_Bank_Number (ext_ba.bank_account_num,
3747 ext_ba.ba_num_sec_segment_id,
3748 iby_utility_pvt.get_view_param('SYS_KEY'),
3749 baek.subkey_cipher_text,
3750 baes.segment_cipher_text,
3751 baes.encoding_scheme,
3752 ext_ba.ba_mask_setting,
3753 ext_ba.ba_unmask_length) ext_bank_account_number,
3754 pmts.ext_branch_number,
3755 pmts.ext_bank_account_type
3756 FROM iby_payments_all pmts,
3757 iby_ext_bank_accounts ext_ba,
3758 IBY_SYS_SECURITY_SUBKEYS baek,
3759 IBY_SECURITY_SEGMENTS baes
3760 WHERE
3761 pmts.payment_instruction_id = p_instruction_id
3762 AND pmts.payment_status = 'INSTRUCTION_CREATED'
3763 AND pmts.external_bank_account_id = ext_ba.ext_bank_account_id(+)
3764 AND ext_ba.ba_num_sec_segment_id = baes.sec_segment_id(+)
3765 AND baes.sec_subkey_id = baek.sec_subkey_id(+);
3766
3767 -- Pick Up Required Data From Payable Documents (IBY_DOCS_PAYABLE_ALL) Using Payment Id From Payment Data
3768 CURSOR iby_docs_csr(p_pmt_id IBY_DOCS_PAYABLE_ALL.payment_id%TYPE) IS
3769 SELECT calling_app_doc_unique_ref2
3770 FROM iby_docs_payable_all
3771 WHERE
3772 payment_id = p_pmt_id
3773 AND
3774 calling_app_id=200
3775 AND
3776 UPPER(payment_function) IN ('PAYABLES_DISB','EMPLOYEE_EXP')
3777 AND document_status = 'PAYMENT_CREATED';
3778
3779
3780
3781 -- Declaring Record Types Of Various Cursors
3782 l_pay_instr_rec pay_instr_data_csr%ROWTYPE;
3783 l_iby_pmt_rec iby_pmt_csr%ROWTYPE;
3784 l_iby_docs_rec iby_docs_csr%ROWTYPE;
3785 l_org_id NUMBER;
3786
3787 l_format_name VARCHAR2(50);
3788 l_error_message VARCHAR2(1000);
3789 l_valid NUMBER;
3790
3791 BEGIN
3792 l_format_name := 'FVSPCCDP';
3793 x_result:=g_SUCCESS;
3794
3795 -- Initializing the payment record
3796 IBY_VALIDATIONSETS_PUB.initInstructionData(p_instruction_id,l_instruction_rec);
3797
3798 l_docErrorRec.validation_set_code := p_validation_set_code;
3799 l_docErrorRec.transaction_id := p_instruction_id;
3800 l_docErrorRec.transaction_type := 'PAYMENT_INSTRUCTION';
3801 l_docErrorRec.calling_app_doc_unique_ref1 := p_instruction_id;
3802
3803 OPEN pay_instr_data_csr(p_instruction_id); -- Opening Instruction Data Cursor
3804 FETCH pay_instr_data_csr INTO l_pay_instr_rec; -- Getting Instruction Data
3805
3806 IF pay_instr_data_csr%FOUND THEN -- If Row Found Then Only Process Further
3807
3808 -- delete from FV_TP_TS_AMT_DATA to refresh data
3809 delete from FV_TP_TS_AMT_DATA where payment_instruction_id = p_instruction_id;
3810
3811 l_org_id:=l_pay_instr_rec.org_id;-- Extracting Org_ID
3812
3813 -- Do IBY_PAYMENT_FORMAT_VAL_PVT.SUPPLIER_TYPE at Payment Instruction Level
3814 -- Moved this to instruction level (Bug 5526640)
3815 IBY_PAYMENT_FORMAT_VAL_PVT.SUPPLIER_TYPE(l_format_name,
3816 p_instruction_id,
3817 l_docErrorTab,
3818 l_docErrorRec,
3819 l_valid,
3820 l_error_message);
3821
3822 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
3823 x_result:=1;
3824 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
3825 x_result:=1;
3826 RETURN;
3827 END IF;
3828
3829 OPEN iby_pmt_csr(p_instruction_id); -- Opening Payment Data Cursor
3830 LOOP -- Perform Validation For Each Of record in IBY_PAYMENTS_ALL
3831 FETCH iby_pmt_csr INTO l_iby_pmt_rec; -- Getting Payment Data
3832 EXIT WHEN iby_pmt_csr%NOTFOUND;
3833
3834 OPEN iby_docs_csr(l_iby_pmt_rec.payment_id); -- Opening Payable Documents Data
3835 LOOP -- Perform Validation For Each Of record in IBY_DOCS_PAYABLE_ALL
3836 FETCH iby_docs_csr INTO l_iby_docs_rec; -- Getting Payable Documents Data
3837 EXIT WHEN iby_docs_csr%NOTFOUND;
3838
3839 -- validate internal bank account parameters
3840 IBY_PAYMENT_FORMAT_VAL_PVT.AGENCY_LOCATION_CODE(l_format_name,
3841 l_iby_pmt_rec.int_bank_acct_agency_loc_code,
3842 l_iby_pmt_rec.int_bank_account_name,
3843 l_docErrorTab,
3844 l_docErrorRec,
3845 l_valid,
3846 l_error_message);
3847 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
3848 x_result:=1;
3849 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
3850 x_result:=1;
3851 RETURN;
3852 END IF;
3853
3854
3855 -- validate external bank account parameters
3856 -- validate external bank account id
3857 IBY_PAYMENT_FORMAT_VAL_PVT.EXTERNAL_BANK_ACCOUNT_ID(l_format_name,
3858 l_iby_pmt_rec.external_bank_account_id,
3859 l_docErrorTab,
3860 l_docErrorRec,
3861 l_valid,
3862 l_error_message);
3863 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
3864 x_result:=1;
3865 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
3866 x_result:=1;
3867 RETURN;
3868 END IF;
3869
3870
3871 IBY_PAYMENT_FORMAT_VAL_PVT.ACCOUNT_TYPE(l_format_name,
3872 l_iby_pmt_rec.ext_bank_account_type,
3873 l_iby_pmt_rec.ext_bank_account_name,
3874 TO_NUMBER(l_iby_docs_rec.calling_app_doc_unique_ref2),
3875 l_iby_pmt_rec.payment_id,
3876 l_docErrorTab,
3877 l_docErrorRec,
3878 l_valid,
3879 l_error_message);
3880 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
3881 x_result:=1;
3882 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
3883 x_result:=1;
3884 RETURN;
3885 END IF;
3886
3887 IBY_PAYMENT_FORMAT_VAL_PVT.DEPOSITER_ACC_NUM(l_format_name,
3888 l_iby_pmt_rec.ext_bank_account_number,
3889 l_docErrorTab,
3890 l_docErrorRec,
3891 l_valid,
3892 l_error_message);
3893
3894 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
3895 x_result:=1;
3896 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
3897 x_result:=1;
3898 RETURN;
3899 END IF;
3900
3901 IBY_PAYMENT_FORMAT_VAL_PVT.RTN_NUMBER(l_format_name,
3902 l_iby_pmt_rec.ext_branch_number,
3903 l_docErrorTab,
3904 l_docErrorRec,
3905 l_valid,
3906 l_error_message);
3907
3908 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
3909 x_result:=1;
3910 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
3911 x_result:=1;
3912 RETURN;
3913 END IF;
3914
3915
3916 IBY_PAYMENT_FORMAT_VAL_PVT.PAY_ALONE_OPTION(l_format_name,
3917 TO_NUMBER(l_iby_docs_rec.calling_app_doc_unique_ref2),
3918 l_iby_pmt_rec.payment_id,
3919 l_docErrorTab,
3920 l_docErrorRec,
3921 l_valid,
3922 l_error_message);
3923 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
3924 x_result:=1;
3925 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
3926 x_result:=1;
3927 RETURN;
3928 END IF;
3929
3930 IBY_PAYMENT_FORMAT_VAL_PVT.TREASURY_SYMBOLS_PROCESS(l_format_name,
3931 p_instruction_id,
3932 l_iby_pmt_rec.payment_id,
3933 TO_NUMBER(l_iby_docs_rec.calling_app_doc_unique_ref2),
3934 l_docErrorTab,
3935 l_docErrorRec,
3936 l_valid,
3937 l_error_message);
3938 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
3939 x_result:=1;
3940 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
3941 x_result:=1;
3942 RETURN;
3943 END IF;
3944
3945 --IBY_PAYMENT_FORMAT_VAL_PVT.TAS_VALIDATION Is Directly Called From IBY_PAYMENT_FORMAT_VAL_PVT.TREASURY_SYMBOLS_PROCESS
3946
3947
3948
3949 IBY_PAYMENT_FORMAT_VAL_PVT.PAYEE_SSN(l_format_name,
3950 l_iby_pmt_rec.payee_le_registration_num,
3951 l_iby_pmt_rec.payee_party_id,
3952 l_docErrorTab,
3953 l_docErrorRec,
3954 l_valid,
3955 l_error_message);
3956
3957 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
3958 x_result:=1;
3959 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
3960 x_result:=1;
3961 RETURN;
3962 END IF;
3963
3964
3965
3966
3967 END LOOP;-- End Of Documents Cursor Loop
3968 CLOSE iby_docs_csr;-- Closing Payable Documents Cursor
3969
3970 END LOOP;-- End Of Payments Cursor Loop
3971 CLOSE iby_pmt_csr; -- Closing Payments Cursor
3972
3973 IBY_PAYMENT_FORMAT_VAL_PVT.MAX_TREASURY_SYMBOLS(l_format_name,
3974 p_instruction_id,
3975 l_docErrorTab,
3976 l_docErrorRec,
3977 l_valid,
3978 l_error_message);
3979
3980 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
3981 x_result:=1;
3982 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
3983 x_result:=1;
3984 RETURN;
3985 END IF;
3986
3987
3988 IBY_PAYMENT_FORMAT_VAL_PVT.SCHEDULE_NUMBER(l_format_name,
3989 p_instruction_id,
3990 l_docErrorTab,
3991 l_docErrorRec,
3992 l_valid,
3993 l_error_message);
3994
3995 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
3996 x_result:=1;
3997 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
3998 x_result:=1;
3999 RETURN;
4000 END IF;
4001
4002
4003 END IF; -- End of IF pay_instr_data_csr%FOUND
4004
4005
4006 CLOSE pay_instr_data_csr; -- Closing Instruction Data Cursor
4007
4008 IBY_VALIDATIONSETS_PUB.insert_transaction_errors('N',l_docErrorTab);
4009
4010 EXCEPTION
4011 WHEN OTHERS THEN
4012 x_result := 1;
4013 l_docErrorRec.transaction_error_id := null;
4014 l_docErrorRec.error_code := 'UNEXPECTED_ERROR';
4015 l_docErrorRec.error_message := SQLERRM;
4016 IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(l_docErrorRec, l_docErrorTab);
4017 iby_payment_format_val_pvt.log_error_messages(FND_LOG.LEVEL_STATEMENT, l_format_name, SQLERRM);
4018 IBY_VALIDATIONSETS_PUB.insert_transaction_errors('N',l_docErrorTab);
4019
4020
4021 END FVSPCCDP;-- End of Procedure FVSPCCDP
4022 ----------------------------------------------------------------------------------------------------------
4023 /*
4024
4025 PROCEDURE : FVSPNCR
4026
4027 SPS NCR Vendor Payment Format
4028
4029 */
4030
4031 PROCEDURE FVSPNCR
4032 (
4033 p_validation_assign_id IN IBY_VAL_ASSIGNMENTS.validation_assignment_id%TYPE,
4034 p_validation_set_code IN IBY_VALIDATION_SETS_VL.validation_set_code%TYPE,
4035 p_instruction_id IN IBY_PAY_INSTRUCTIONS_ALL.payment_instruction_id%TYPE,
4036 p_is_online_val IN VARCHAR2,
4037 x_result OUT NOCOPY NUMBER
4038 )IS
4039
4040 l_instruction_rec IBY_VALIDATIONSETS_PUB.instructionRecType;
4041 l_docErrorTab IBY_VALIDATIONSETS_PUB.docErrorTabType;
4042 l_docErrorRec IBY_TRANSACTION_ERRORS%ROWTYPE;
4043
4044 -- Pick Up The Required Data From Instructions (IBY_PAY_INSTRUCTIONS_ALL) using payemnt_instruction_id
4045 CURSOR pay_instr_data_csr(p_instruction_id IBY_PAY_INSTRUCTIONS_ALL.payment_instruction_id%TYPE) IS
4046 SELECT org_id,
4047 payment_reason_code
4048 FROM iby_pay_instructions_all
4049 WHERE
4050 payment_instruction_id = p_instruction_id;
4051
4052 -- Pick Up Required Data From Payments (IBY_PAYMENTS_ALL) using payment_instruction_id
4053 CURSOR iby_pmt_csr(p_instruction_id IBY_PAY_INSTRUCTIONS_ALL.payment_instruction_id%TYPE) IS
4054 SELECT payment_id,
4055 payment_instruction_id,
4056 payee_party_id,
4057 payee_le_registration_num,
4058 payment_amount,
4059 internal_bank_account_id,
4060 int_bank_account_name,
4061 int_bank_acct_agency_loc_code
4062 FROM iby_payments_all
4063 WHERE payment_instruction_id = p_instruction_id
4064 AND payment_status = 'INSTRUCTION_CREATED';
4065
4066 -- Pick Up Required Data From Payable Documents (IBY_DOCS_PAYABLE_ALL) Using Payment Id From Payment Data
4067 CURSOR iby_docs_csr(p_pmt_id IBY_DOCS_PAYABLE_ALL.payment_id%TYPE) IS
4068 SELECT calling_app_doc_unique_ref2
4069 FROM iby_docs_payable_all
4070 WHERE
4071 payment_id = p_pmt_id
4072 AND
4073 calling_app_id=200
4074 AND
4075 UPPER(payment_function) IN ('PAYABLES_DISB','EMPLOYEE_EXP')
4076 AND document_status = 'PAYMENT_CREATED';
4077
4078
4079
4080 -- Declaring Record Types Of Various Cursors
4081 l_pay_instr_rec pay_instr_data_csr%ROWTYPE;
4082 l_iby_pmt_rec iby_pmt_csr%ROWTYPE;
4083 l_iby_docs_rec iby_docs_csr%ROWTYPE;
4084 l_org_id NUMBER;
4085
4086 l_format_name VARCHAR2(50);
4087 l_error_message VARCHAR2(1000);
4088 l_valid NUMBER;
4089 l_invoice_type IBY_DOCS_PAYABLE_ALL.DOCUMENT_TYPE%TYPE;
4090
4091 BEGIN
4092 l_format_name := 'FVSPNCR';
4093 x_result:=g_SUCCESS;
4094
4095 -- Initializing the payment record
4096 IBY_VALIDATIONSETS_PUB.initInstructionData(p_instruction_id,l_instruction_rec);
4097
4098 l_docErrorRec.validation_set_code := p_validation_set_code;
4099 l_docErrorRec.transaction_id := p_instruction_id;
4100 l_docErrorRec.transaction_type := 'PAYMENT_INSTRUCTION';
4101 l_docErrorRec.calling_app_doc_unique_ref1 := p_instruction_id;
4102
4103 OPEN pay_instr_data_csr(p_instruction_id); -- Opening Instruction Data Cursor
4104 FETCH pay_instr_data_csr INTO l_pay_instr_rec; -- Getting Instruction Data
4105
4106 IF pay_instr_data_csr%FOUND THEN -- If Row Found Then Only Process Further
4107
4108 -- delete from FV_TP_TS_AMT_DATA to refresh data
4109 delete from FV_TP_TS_AMT_DATA where payment_instruction_id = p_instruction_id;
4110
4111 l_org_id:=l_pay_instr_rec.org_id;-- Extracting Org_ID
4112
4113 -- Do IBY_PAYMENT_FORMAT_VAL_PVT.SUPPLIER_TYPE at Payment Instruction Level
4114 -- Moved this to instruction level (Bug 5526640)
4115 IBY_PAYMENT_FORMAT_VAL_PVT.SUPPLIER_TYPE(l_format_name,
4116 p_instruction_id,
4117 l_docErrorTab,
4118 l_docErrorRec,
4119 l_valid,
4120 l_error_message);
4121
4122 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
4123 x_result:=1;
4124 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
4125 x_result:=1;
4126 RETURN;
4127 END IF;
4128
4129 OPEN iby_pmt_csr(p_instruction_id); -- Opening Payment Data Cursor
4130 LOOP -- Perform Validation For Each Of record in IBY_PAYMENTS_ALL
4131 FETCH iby_pmt_csr INTO l_iby_pmt_rec; -- Getting Payment Data
4132 EXIT WHEN iby_pmt_csr%NOTFOUND;
4133
4134 -- Do IBY_PAYMENT_FORMAT_VAL_PVT.MAX_PAYMENT_AMT at Payment Level
4135 IBY_PAYMENT_FORMAT_VAL_PVT.MAX_PAYMENT_AMT(l_format_name,
4136 l_iby_pmt_rec.payment_instruction_id,
4137 l_iby_pmt_rec.payment_amount,
4138 l_docErrorTab,
4139 l_docErrorRec,
4140 l_valid,
4141 l_error_message);
4142 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
4143 x_result:=1;
4144 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
4145 x_result:=1;
4146 RETURN;
4147 END IF;
4148
4149
4150 -- Do IBY_PAYMENT_FORMAT_VAL_PVT.PAYEE_ADDRESS
4151 IBY_PAYMENT_FORMAT_VAL_PVT.PAYEE_ADDRESS(l_format_name,
4152 l_iby_pmt_rec.payment_id,
4153 l_docErrorTab,
4154 l_docErrorRec,
4155 l_valid,
4156 l_error_message);
4157 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
4158 x_result:=1;
4159 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
4160 x_result:=1;
4161 RETURN;
4162 END IF;
4163
4164
4165
4166
4167 OPEN iby_docs_csr(l_iby_pmt_rec.payment_id); -- Opening Payable Documents Data
4168 LOOP -- Perform Validation For Each Of record in IBY_DOCS_PAYABLE_ALL
4169 FETCH iby_docs_csr INTO l_iby_docs_rec; -- Getting Payable Documents Data
4170 EXIT WHEN iby_docs_csr%NOTFOUND;
4171
4172
4173 -- validate internal bank account parameters
4174 IBY_PAYMENT_FORMAT_VAL_PVT.AGENCY_LOCATION_CODE(l_format_name,
4175 l_iby_pmt_rec.int_bank_acct_agency_loc_code,
4176 l_iby_pmt_rec.int_bank_account_name,
4177 l_docErrorTab,
4178 l_docErrorRec,
4179 l_valid,
4180 l_error_message);
4181 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
4182 x_result:=1;
4183 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
4184 x_result:=1;
4185 RETURN;
4186 END IF;
4187
4188
4189
4190
4191 SELECT document_type
4192 INTO l_invoice_type FROM IBY_DOCS_PAYABLE_ALL
4193 WHERE calling_app_doc_unique_ref2 = l_iby_docs_rec.calling_app_doc_unique_ref2
4194 AND payment_id = l_iby_pmt_rec.payment_id
4195 AND calling_app_id=200
4196 AND UPPER(payment_function) IN ('PAYABLES_DISB','EMPLOYEE_EXP');
4197
4198 -- Do Validation by IBY_PAYMENT_FORMAT_VAL_PVT.PAY_TAX_BENEFIT at Instruction Level
4199 IF(l_invoice_type <> 'INTEREST') THEN
4200 IBY_PAYMENT_FORMAT_VAL_PVT.PAY_TAX_BENEFIT(l_format_name,
4201 l_iby_pmt_rec.payment_id,
4202 to_number(l_iby_docs_rec.calling_app_doc_unique_ref2),
4203 l_docErrorTab,
4204 l_docErrorRec,
4205 l_valid,
4206 l_error_message);
4207 END IF;
4208 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
4209 x_result:=1;
4210 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
4211 x_result:=1;
4212 RETURN;
4213 END IF;
4214
4215 IBY_PAYMENT_FORMAT_VAL_PVT.TREASURY_SYMBOLS_PROCESS(l_format_name,
4216 p_instruction_id,
4217 l_iby_pmt_rec.payment_id,
4218 TO_NUMBER(l_iby_docs_rec.calling_app_doc_unique_ref2),
4219 l_docErrorTab,
4220 l_docErrorRec,
4221 l_valid,
4222 l_error_message);
4223 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
4224 x_result:=1;
4225 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
4226 x_result:=1;
4227 RETURN;
4228 END IF;
4229
4230 --IBY_PAYMENT_FORMAT_VAL_PVT.TAS_VALIDATION Is Directly Called From IBY_PAYMENT_FORMAT_VAL_PVT.TREASURY_SYMBOLS_PROCESS
4231
4232
4233 END LOOP;-- End Of Documents Cursor Loop
4234 CLOSE iby_docs_csr;-- Closing Payable Documents Cursor
4235
4236 END LOOP;-- End Of Payments Cursor Loop
4237 CLOSE iby_pmt_csr; -- Closing Payments Cursor
4238
4239
4240
4241 IBY_PAYMENT_FORMAT_VAL_PVT.MAX_TREASURY_SYMBOLS(l_format_name,
4242 p_instruction_id,
4243 l_docErrorTab,
4244 l_docErrorRec,
4245 l_valid,
4246 l_error_message);
4247
4248 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
4249 x_result:=1;
4250 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
4251 x_result:=1;
4252 RETURN;
4253 END IF;
4254
4255 IBY_PAYMENT_FORMAT_VAL_PVT.SCHEDULE_NUMBER(l_format_name,
4256 p_instruction_id,
4257 l_docErrorTab,
4258 l_docErrorRec,
4259 l_valid,
4260 l_error_message);
4261
4262 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
4263 x_result:=1;
4264 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
4265 x_result:=1;
4266 RETURN;
4267 END IF;
4268
4269 END IF; -- End of IF pay_instr_data_csr%FOUND
4270 CLOSE pay_instr_data_csr; -- Closing Instruction Data Cursor
4271
4272 IBY_VALIDATIONSETS_PUB.insert_transaction_errors('N',l_docErrorTab);
4273
4274 EXCEPTION
4275 WHEN OTHERS THEN
4276 x_result := 1;
4277 l_docErrorRec.transaction_error_id := null;
4278 l_docErrorRec.error_code := 'UNEXPECTED_ERROR';
4279 l_docErrorRec.error_message := SQLERRM;
4280 IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(l_docErrorRec, l_docErrorTab);
4281 iby_payment_format_val_pvt.log_error_messages(FND_LOG.LEVEL_STATEMENT, l_format_name, SQLERRM);
4282 IBY_VALIDATIONSETS_PUB.insert_transaction_errors('N',l_docErrorTab);
4283
4284
4285 END FVSPNCR;-- End of Procedure FVSPNCR
4286 ----------------------------------------------------------------------------------------------------------
4287 /*
4288
4289 PROCEDURE : FVSPPPD
4290
4291 SPS PPD Vendor Payment Format
4292
4293 */
4294
4295 PROCEDURE FVSPPPD
4296 (
4297 p_validation_assign_id IN IBY_VAL_ASSIGNMENTS.validation_assignment_id%TYPE,
4298 p_validation_set_code IN IBY_VALIDATION_SETS_VL.validation_set_code%TYPE,
4299 p_instruction_id IN IBY_PAY_INSTRUCTIONS_ALL.payment_instruction_id%TYPE,
4300 p_is_online_val IN VARCHAR2,
4301 x_result OUT NOCOPY NUMBER
4302 )IS
4303
4304 l_instruction_rec IBY_VALIDATIONSETS_PUB.instructionRecType;
4305 l_docErrorTab IBY_VALIDATIONSETS_PUB.docErrorTabType;
4306 l_docErrorRec IBY_TRANSACTION_ERRORS%ROWTYPE;
4307
4308 -- Pick Up The Required Data From Instructions (IBY_PAY_INSTRUCTIONS_ALL) using payemnt_instruction_id
4309 CURSOR pay_instr_data_csr(p_instruction_id IBY_PAY_INSTRUCTIONS_ALL.payment_instruction_id%TYPE) IS
4310 SELECT org_id,
4311 payment_reason_code
4312 FROM iby_pay_instructions_all
4313 WHERE
4314 payment_instruction_id = p_instruction_id;
4315
4316 -- Pick Up Required Data From Payments (IBY_PAYMENTS_ALL) using payment_instruction_id
4317 CURSOR iby_pmt_csr(p_instruction_id IBY_PAY_INSTRUCTIONS_ALL.payment_instruction_id%TYPE) IS
4318 SELECT pmts.payment_id,
4319 pmts.payment_instruction_id,
4320 pmts.payee_party_id,
4321 pmts.payee_le_registration_num,
4322 pmts.payment_amount,
4323 pmts.internal_bank_account_id,
4324 pmts.int_bank_account_name,
4325 pmts.int_bank_acct_agency_loc_code,
4326 pmts.external_bank_account_id,
4327 ext_ba.bank_account_name ext_bank_account_name,
4328 iby_ext_bankacct_pub.Uncipher_Bank_Number (ext_ba.bank_account_num,
4329 ext_ba.ba_num_sec_segment_id,
4330 iby_utility_pvt.get_view_param('SYS_KEY'),
4331 baek.subkey_cipher_text,
4332 baes.segment_cipher_text,
4333 baes.encoding_scheme,
4334 ext_ba.ba_mask_setting,
4335 ext_ba.ba_unmask_length) ext_bank_account_number,
4336 pmts.ext_branch_number,
4337 pmts.ext_bank_account_type
4338 FROM iby_payments_all pmts,
4339 iby_ext_bank_accounts ext_ba,
4340 IBY_SYS_SECURITY_SUBKEYS baek,
4341 IBY_SECURITY_SEGMENTS baes
4342 WHERE
4343 pmts.payment_instruction_id = p_instruction_id
4344 AND pmts.payment_status = 'INSTRUCTION_CREATED'
4345 AND pmts.external_bank_account_id = ext_ba.ext_bank_account_id(+)
4346 AND ext_ba.ba_num_sec_segment_id = baes.sec_segment_id(+)
4347 AND baes.sec_subkey_id = baek.sec_subkey_id(+);
4348
4349 -- Pick Up Required Data From Payable Documents (IBY_DOCS_PAYABLE_ALL) Using Payment Id From Payment Data
4350 CURSOR iby_docs_csr(p_pmt_id IBY_DOCS_PAYABLE_ALL.payment_id%TYPE) IS
4351 SELECT calling_app_doc_unique_ref2
4352 FROM iby_docs_payable_all
4353 WHERE
4354 payment_id = p_pmt_id
4355 AND
4356 calling_app_id=200
4357 AND
4358 UPPER(payment_function) IN ('PAYABLES_DISB','EMPLOYEE_EXP')
4359 AND document_status = 'PAYMENT_CREATED';
4360
4361
4362 -- Declaring Record Types Of Various Cursors
4363 l_pay_instr_rec pay_instr_data_csr%ROWTYPE;
4364 l_iby_pmt_rec iby_pmt_csr%ROWTYPE;
4365 l_iby_docs_rec iby_docs_csr%ROWTYPE;
4366
4367 l_org_id NUMBER;
4368
4369 l_format_name VARCHAR2(50);
4370 l_error_message VARCHAR2(1000);
4371 l_valid NUMBER;
4372
4373 BEGIN
4374 l_format_name := 'FVSPPPD';
4375 x_result:=g_SUCCESS;
4376 -- Initializing the payment record
4377 IBY_VALIDATIONSETS_PUB.initInstructionData(p_instruction_id,l_instruction_rec);
4378
4379 l_docErrorRec.validation_set_code := p_validation_set_code;
4380 l_docErrorRec.transaction_id := p_instruction_id;
4381 l_docErrorRec.transaction_type := 'PAYMENT_INSTRUCTION';
4382 l_docErrorRec.calling_app_doc_unique_ref1 := p_instruction_id;
4383
4384 OPEN pay_instr_data_csr(p_instruction_id); -- Opening Instruction Data Cursor
4385 FETCH pay_instr_data_csr INTO l_pay_instr_rec; -- Getting Instruction Data
4386
4387 IF pay_instr_data_csr%FOUND THEN -- If Row Found Then Only Process Further
4388
4389 -- delete from FV_TP_TS_AMT_DATA to refresh data
4390 delete from FV_TP_TS_AMT_DATA where payment_instruction_id = p_instruction_id;
4391
4392 l_org_id:=l_pay_instr_rec.org_id;-- Extracting Org_ID
4393
4394 -- Do IBY_PAYMENT_FORMAT_VAL_PVT.SUPPLIER_TYPE at Payment Instruction Level
4395 -- Moved this to instruction level (Bug 5526640)
4396 IBY_PAYMENT_FORMAT_VAL_PVT.SUPPLIER_TYPE(l_format_name,
4397 p_instruction_id,
4398 l_docErrorTab,
4399 l_docErrorRec,
4400 l_valid,
4401 l_error_message);
4402
4403 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
4404 x_result:=1;
4405 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
4406 x_result:=1;
4407 RETURN;
4408 END IF;
4409
4410 OPEN iby_pmt_csr(p_instruction_id); -- Opening Payment Data Cursor
4411 LOOP -- Perform Validation For Each Of record in IBY_PAYMENTS_ALL
4412 FETCH iby_pmt_csr INTO l_iby_pmt_rec; -- Getting Payment Data
4413 EXIT WHEN iby_pmt_csr%NOTFOUND;
4414
4415 -- Do IBY_PAYMENT_FORMAT_VAL_PVT.MAX_PAYMENT_AMT_2 at Payment Level
4416 IBY_PAYMENT_FORMAT_VAL_PVT.MAX_PAYMENT_AMT_2(l_format_name,
4417 l_iby_pmt_rec.payment_instruction_id,
4418 l_iby_pmt_rec.payment_amount,
4419 l_docErrorTab,
4420 l_docErrorRec,
4421 l_valid,
4422 l_error_message);
4423 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
4424 x_result:=1;
4425 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
4426 x_result:=1;
4427 RETURN;
4428 END IF;
4429
4430
4431 OPEN iby_docs_csr(l_iby_pmt_rec.payment_id); -- Opening Payable Documents Data
4432 LOOP -- Perform Validation For Each Of record in IBY_DOCS_PAYABLE_ALL
4433 FETCH iby_docs_csr INTO l_iby_docs_rec; -- Getting Payable Documents Data
4434 EXIT WHEN iby_docs_csr%NOTFOUND;
4435
4436 -- validate internal bank account parameters
4437 IBY_PAYMENT_FORMAT_VAL_PVT.AGENCY_LOCATION_CODE(l_format_name,
4438 l_iby_pmt_rec.int_bank_acct_agency_loc_code,
4439 l_iby_pmt_rec.int_bank_account_name,
4440 l_docErrorTab,
4441 l_docErrorRec,
4442 l_valid,
4443 l_error_message);
4444 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
4445 x_result:=1;
4446 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
4447 x_result:=1;
4448 RETURN;
4449 END IF;
4450
4451 -- validate external bank account parameters
4452 -- validate external bank account id
4453 IBY_PAYMENT_FORMAT_VAL_PVT.EXTERNAL_BANK_ACCOUNT_ID(l_format_name,
4454 l_iby_pmt_rec.external_bank_account_id,
4455 l_docErrorTab,
4456 l_docErrorRec,
4457 l_valid,
4458 l_error_message);
4459 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
4460 x_result:=1;
4461 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
4462 x_result:=1;
4463 RETURN;
4464 END IF;
4465
4466
4467 IBY_PAYMENT_FORMAT_VAL_PVT.ACCOUNT_TYPE(l_format_name,
4468 l_iby_pmt_rec.ext_bank_account_type,
4469 l_iby_pmt_rec.ext_bank_account_name,
4470 TO_NUMBER(l_iby_docs_rec.calling_app_doc_unique_ref2),
4471 l_iby_pmt_rec.payment_id,
4472 l_docErrorTab,
4473 l_docErrorRec,
4474 l_valid,
4475 l_error_message);
4476 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
4477 x_result:=1;
4478 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
4479 x_result:=1;
4480 RETURN;
4481 END IF;
4482
4483 IBY_PAYMENT_FORMAT_VAL_PVT.DEPOSITER_ACC_NUM(l_format_name,
4484 l_iby_pmt_rec.ext_bank_account_number,
4485 l_docErrorTab,
4486 l_docErrorRec,
4487 l_valid,
4488 l_error_message);
4489
4490 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
4491 x_result:=1;
4492 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
4493 x_result:=1;
4494 RETURN;
4495 END IF;
4496
4497 IBY_PAYMENT_FORMAT_VAL_PVT.RTN_NUMBER(l_format_name,
4498 l_iby_pmt_rec.ext_branch_number,
4499 l_docErrorTab,
4500 l_docErrorRec,
4501 l_valid,
4502 l_error_message);
4503
4504 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
4505 x_result:=1;
4506 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
4507 x_result:=1;
4508 RETURN;
4509 END IF;
4510
4511
4512 IBY_PAYMENT_FORMAT_VAL_PVT.PAY_ALONE_OPTION(l_format_name,
4513 TO_NUMBER(l_iby_docs_rec.calling_app_doc_unique_ref2),
4514 l_iby_pmt_rec.payment_id,
4515 l_docErrorTab,
4516 l_docErrorRec,
4517 l_valid,
4518 l_error_message);
4519 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
4520 x_result:=1;
4521 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
4522 x_result:=1;
4523 RETURN;
4524 END IF;
4525
4526 IBY_PAYMENT_FORMAT_VAL_PVT.TREASURY_SYMBOLS_PROCESS(l_format_name,
4527 p_instruction_id,
4528 l_iby_pmt_rec.payment_id,
4529 TO_NUMBER(l_iby_docs_rec.calling_app_doc_unique_ref2),
4530 l_docErrorTab,
4531 l_docErrorRec,
4532 l_valid,
4533 l_error_message);
4534 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
4535 x_result:=1;
4536 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
4537 x_result:=1;
4538 RETURN;
4539 END IF;
4540
4541 --IBY_PAYMENT_FORMAT_VAL_PVT.TAS_VALIDATION Is Directly Called From IBY_PAYMENT_FORMAT_VAL_PVT.TREASURY_SYMBOLS_PROCESS
4542
4543
4544
4545 IBY_PAYMENT_FORMAT_VAL_PVT.PAYEE_SSN(l_format_name,
4546 l_iby_pmt_rec.payee_le_registration_num,
4547 l_iby_pmt_rec.payee_party_id,
4548 l_docErrorTab,
4549 l_docErrorRec,
4550 l_valid,
4551 l_error_message);
4552 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
4553 x_result:=1;
4554 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
4555 x_result:=1;
4556 RETURN;
4557 END IF;
4558
4559
4560 END LOOP;-- End Of Documents Cursor Loop
4561 CLOSE iby_docs_csr;-- Closing Payable Documents Cursor
4562
4563 END LOOP;-- End Of Payments Cursor Loop
4564 CLOSE iby_pmt_csr; -- Closing Payments Cursor
4565
4566 IBY_PAYMENT_FORMAT_VAL_PVT.MAX_TREASURY_SYMBOLS(l_format_name,
4567 p_instruction_id,
4568 l_docErrorTab,
4569 l_docErrorRec,
4570 l_valid,
4571 l_error_message);
4572
4573 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
4574 x_result:=1;
4575 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
4576 x_result:=1;
4577 RETURN;
4578 END IF;
4579
4580 IBY_PAYMENT_FORMAT_VAL_PVT.SCHEDULE_NUMBER(l_format_name,
4581 p_instruction_id,
4582 l_docErrorTab,
4583 l_docErrorRec,
4584 l_valid,
4585 l_error_message);
4586
4587 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
4588 x_result:=1;
4589 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
4590 x_result:=1;
4591 RETURN;
4592 END IF;
4593
4594 IBY_PAYMENT_FORMAT_VAL_PVT.MANDATORY_PPD_PPDP_REASON_CODE(l_format_name,
4595 l_pay_instr_rec.payment_reason_code,
4596 l_docErrorTab,
4597 l_docErrorRec,
4598 l_valid,
4599 l_error_message);
4600
4601 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
4602 x_result:=1;
4603 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
4604 x_result:=1;
4605 RETURN;
4606 END IF;
4607
4608
4609 END IF; -- End of IF pay_instr_data_csr%FOUND
4610 CLOSE pay_instr_data_csr; -- Closing Instruction Data Cursor
4611
4612 IBY_VALIDATIONSETS_PUB.insert_transaction_errors('N',l_docErrorTab);
4613
4614 EXCEPTION
4615 WHEN OTHERS THEN
4616 x_result := 1;
4617 l_docErrorRec.transaction_error_id := null;
4618 l_docErrorRec.error_code := 'UNEXPECTED_ERROR';
4619 l_docErrorRec.error_message := SQLERRM;
4620 IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(l_docErrorRec, l_docErrorTab);
4621 iby_payment_format_val_pvt.log_error_messages(FND_LOG.LEVEL_STATEMENT, l_format_name, SQLERRM);
4622 IBY_VALIDATIONSETS_PUB.insert_transaction_errors('N',l_docErrorTab);
4623
4624
4625 END FVSPPPD;-- End of Procedure FVSPPPD
4626 ----------------------------------------------------------------------------------------------------------
4627 /*
4628
4629 PROCEDURE : FVSPPPDP
4630
4631 SPS PPDP Vendor Payment Format
4632
4633 */
4634
4635 PROCEDURE FVSPPPDP
4636 (
4637 p_validation_assign_id IN IBY_VAL_ASSIGNMENTS.validation_assignment_id%TYPE,
4638 p_validation_set_code IN IBY_VALIDATION_SETS_VL.validation_set_code%TYPE,
4639 p_instruction_id IN IBY_PAY_INSTRUCTIONS_ALL.payment_instruction_id%TYPE,
4640 p_is_online_val IN VARCHAR2,
4641 x_result OUT NOCOPY NUMBER
4642 )IS
4643
4644 l_instruction_rec IBY_VALIDATIONSETS_PUB.instructionRecType;
4645 l_docErrorTab IBY_VALIDATIONSETS_PUB.docErrorTabType;
4646 l_docErrorRec IBY_TRANSACTION_ERRORS%ROWTYPE;
4647
4648 -- Pick Up The Required Data From Instructions (IBY_PAY_INSTRUCTIONS_ALL) using payemnt_instruction_id
4649 CURSOR pay_instr_data_csr(p_instruction_id IBY_PAY_INSTRUCTIONS_ALL.payment_instruction_id%TYPE) IS
4650 SELECT org_id,
4651 payment_reason_code
4652 FROM iby_pay_instructions_all
4653 WHERE
4654 payment_instruction_id = p_instruction_id;
4655
4656 -- Pick Up Required Data From Payments (IBY_PAYMENTS_ALL) using payment_instruction_id
4657 CURSOR iby_pmt_csr(p_instruction_id IBY_PAY_INSTRUCTIONS_ALL.payment_instruction_id%TYPE) IS
4658 SELECT pmts.payment_id,
4659 pmts.payment_instruction_id,
4660 pmts.payee_party_id,
4661 pmts.payee_le_registration_num,
4662 pmts.payment_amount,
4663 pmts.internal_bank_account_id,
4664 pmts.int_bank_account_name,
4665 pmts.int_bank_acct_agency_loc_code,
4666 pmts.external_bank_account_id,
4667 ext_ba.bank_account_name ext_bank_account_name,
4668 iby_ext_bankacct_pub.Uncipher_Bank_Number (ext_ba.bank_account_num,
4669 ext_ba.ba_num_sec_segment_id,
4670 iby_utility_pvt.get_view_param('SYS_KEY'),
4671 baek.subkey_cipher_text,
4672 baes.segment_cipher_text,
4673 baes.encoding_scheme,
4674 ext_ba.ba_mask_setting,
4675 ext_ba.ba_unmask_length) ext_bank_account_number,
4676 pmts.ext_branch_number,
4677 pmts.ext_bank_account_type
4678 FROM iby_payments_all pmts,
4679 iby_ext_bank_accounts ext_ba,
4680 IBY_SYS_SECURITY_SUBKEYS baek,
4681 IBY_SECURITY_SEGMENTS baes
4682 WHERE
4683 pmts.payment_instruction_id = p_instruction_id
4684 AND pmts.payment_status = 'INSTRUCTION_CREATED'
4685 AND pmts.external_bank_account_id = ext_ba.ext_bank_account_id(+)
4686 AND ext_ba.ba_num_sec_segment_id = baes.sec_segment_id(+)
4687 AND baes.sec_subkey_id = baek.sec_subkey_id(+);
4688
4689 -- Pick Up Required Data From Payable Documents (IBY_DOCS_PAYABLE_ALL) Using Payment Id From Payment Data
4690 CURSOR iby_docs_csr(p_pmt_id IBY_DOCS_PAYABLE_ALL.payment_id%TYPE) IS
4691 SELECT calling_app_doc_unique_ref2
4692 FROM iby_docs_payable_all
4693 WHERE
4694 payment_id = p_pmt_id
4695 AND
4696 calling_app_id=200
4697 AND
4698 UPPER(payment_function) IN ('PAYABLES_DISB','EMPLOYEE_EXP')
4699 AND document_status = 'PAYMENT_CREATED';
4700
4701
4702 -- Declaring Record Types Of Various Cursors
4703 l_pay_instr_rec pay_instr_data_csr%ROWTYPE;
4704 l_iby_pmt_rec iby_pmt_csr%ROWTYPE;
4705 l_iby_docs_rec iby_docs_csr%ROWTYPE;
4706 l_org_id NUMBER;
4707
4708 l_format_name VARCHAR2(50);
4709 l_error_message VARCHAR2(1000);
4710 l_valid NUMBER;
4711
4712 BEGIN
4713 l_format_name := 'FVSPPPDP';
4714 x_result:=g_SUCCESS;
4715
4716 -- Initializing the payment record
4717 IBY_VALIDATIONSETS_PUB.initInstructionData(p_instruction_id,l_instruction_rec);
4718
4719 l_docErrorRec.validation_set_code := p_validation_set_code;
4720 l_docErrorRec.transaction_id := p_instruction_id;
4721 l_docErrorRec.transaction_type := 'PAYMENT_INSTRUCTION';
4722 l_docErrorRec.calling_app_doc_unique_ref1 := p_instruction_id;
4723
4724 OPEN pay_instr_data_csr(p_instruction_id); -- Opening Instruction Data Cursor
4725 FETCH pay_instr_data_csr INTO l_pay_instr_rec; -- Getting Instruction Data
4726
4727 IF pay_instr_data_csr%FOUND THEN -- If Row Found Then Only Process Further
4728
4729 -- delete from FV_TP_TS_AMT_DATA to refresh data
4730 delete from FV_TP_TS_AMT_DATA where payment_instruction_id = p_instruction_id;
4731
4732 l_org_id:=l_pay_instr_rec.org_id;-- Extracting Org_ID
4733
4734 -- Do IBY_PAYMENT_FORMAT_VAL_PVT.SUPPLIER_TYPE at Payment Instruction Level
4735 -- Moved this to instruction level (Bug 5526640)
4736 IBY_PAYMENT_FORMAT_VAL_PVT.SUPPLIER_TYPE(l_format_name,
4737 p_instruction_id,
4738 l_docErrorTab,
4739 l_docErrorRec,
4740 l_valid,
4741 l_error_message);
4742
4743 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
4744 x_result:=1;
4745 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
4746 x_result:=1;
4747 RETURN;
4748 END IF;
4749
4750 OPEN iby_pmt_csr(p_instruction_id); -- Opening Payment Data Cursor
4751 LOOP -- Perform Validation For Each Of record in IBY_PAYMENTS_ALL
4752 FETCH iby_pmt_csr INTO l_iby_pmt_rec; -- Getting Payment Data
4753 EXIT WHEN iby_pmt_csr%NOTFOUND;
4754
4755 -- Do IBY_PAYMENT_FORMAT_VAL_PVT.MAX_PAYMENT_AMT_2 at Payment Level
4756 IBY_PAYMENT_FORMAT_VAL_PVT.MAX_PAYMENT_AMT_2(l_format_name,
4757 l_iby_pmt_rec.payment_instruction_id,
4758 l_iby_pmt_rec.payment_amount,
4759 l_docErrorTab,
4760 l_docErrorRec,
4761 l_valid,
4762 l_error_message);
4763 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
4764 x_result:=1;
4765 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
4766 x_result:=1;
4767 RETURN;
4768 END IF;
4769
4770
4771 OPEN iby_docs_csr(l_iby_pmt_rec.payment_id); -- Opening Payable Documents Data
4772 LOOP -- Perform Validation For Each Of record in IBY_DOCS_PAYABLE_ALL
4773 FETCH iby_docs_csr INTO l_iby_docs_rec; -- Getting Payable Documents Data
4774 EXIT WHEN iby_docs_csr%NOTFOUND;
4775
4776 -- validate internal bank account parameters
4777 IBY_PAYMENT_FORMAT_VAL_PVT.AGENCY_LOCATION_CODE(l_format_name,
4778 l_iby_pmt_rec.int_bank_acct_agency_loc_code,
4779 l_iby_pmt_rec.int_bank_account_name,
4780 l_docErrorTab,
4781 l_docErrorRec,
4782 l_valid,
4783 l_error_message);
4784
4785 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
4786 x_result:=1;
4787 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
4788 x_result:=1;
4789 RETURN;
4790 END IF;
4791
4792 -- validate external bank account parameters
4793 -- validate external bank account id
4794 IBY_PAYMENT_FORMAT_VAL_PVT.EXTERNAL_BANK_ACCOUNT_ID(l_format_name,
4795 l_iby_pmt_rec.external_bank_account_id,
4796 l_docErrorTab,
4797 l_docErrorRec,
4798 l_valid,
4799 l_error_message);
4800 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
4801 x_result:=1;
4802 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
4803 x_result:=1;
4804 RETURN;
4805 END IF;
4806
4807
4808 IBY_PAYMENT_FORMAT_VAL_PVT.ACCOUNT_TYPE(l_format_name,
4809 l_iby_pmt_rec.ext_bank_account_type,
4810 l_iby_pmt_rec.ext_bank_account_name,
4811 TO_NUMBER(l_iby_docs_rec.calling_app_doc_unique_ref2),
4812 l_iby_pmt_rec.payment_id,
4813 l_docErrorTab,
4814 l_docErrorRec,
4815 l_valid,
4816 l_error_message);
4817 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
4818 x_result:=1;
4819 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
4820 x_result:=1;
4821 RETURN;
4822 END IF;
4823
4824 IBY_PAYMENT_FORMAT_VAL_PVT.DEPOSITER_ACC_NUM(l_format_name,
4825 l_iby_pmt_rec.ext_bank_account_number,
4826 l_docErrorTab,
4827 l_docErrorRec,
4828 l_valid,
4829 l_error_message);
4830
4831 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
4832 x_result:=1;
4833 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
4834 x_result:=1;
4835 RETURN;
4836 END IF;
4837
4838 IBY_PAYMENT_FORMAT_VAL_PVT.RTN_NUMBER(l_format_name,
4839 l_iby_pmt_rec.ext_branch_number,
4840 l_docErrorTab,
4841 l_docErrorRec,
4842 l_valid,
4843 l_error_message);
4844
4845 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
4846 x_result:=1;
4847 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
4848 x_result:=1;
4849 RETURN;
4850 END IF;
4851
4852
4853 IBY_PAYMENT_FORMAT_VAL_PVT.PAY_ALONE_OPTION(l_format_name,
4854 TO_NUMBER(l_iby_docs_rec.calling_app_doc_unique_ref2),
4855 l_iby_pmt_rec.payment_id,
4856 l_docErrorTab,
4857 l_docErrorRec,
4858 l_valid,
4859 l_error_message);
4860 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
4861 x_result:=1;
4862 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
4863 x_result:=1;
4864 RETURN;
4865 END IF;
4866
4867
4868 IBY_PAYMENT_FORMAT_VAL_PVT.TREASURY_SYMBOLS_PROCESS(l_format_name,
4869 p_instruction_id,
4870 l_iby_pmt_rec.payment_id,
4871 TO_NUMBER(l_iby_docs_rec.calling_app_doc_unique_ref2),
4872 l_docErrorTab,
4873 l_docErrorRec,
4874 l_valid,
4875 l_error_message);
4876 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
4877 x_result:=1;
4878 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
4879 x_result:=1;
4880 RETURN;
4881 END IF;
4882
4883 --IBY_PAYMENT_FORMAT_VAL_PVT.TAS_VALIDATION Is Directly Called From IBY_PAYMENT_FORMAT_VAL_PVT.TREASURY_SYMBOLS_PROCESS
4884
4885
4886
4887 IBY_PAYMENT_FORMAT_VAL_PVT.PAYEE_SSN(l_format_name,
4888 l_iby_pmt_rec.payee_le_registration_num,
4889 l_iby_pmt_rec.payee_party_id,
4890 l_docErrorTab,
4891 l_docErrorRec,
4892 l_valid,
4893 l_error_message);
4894 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
4895 x_result:=1;
4896 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
4897 x_result:=1;
4898 RETURN;
4899 END IF;
4900
4901
4902
4903 END LOOP;-- End Of Documents Cursor Loop
4904 CLOSE iby_docs_csr;-- Closing Payable Documents Cursor
4905
4906 END LOOP;-- End Of Payments Cursor Loop
4907 CLOSE iby_pmt_csr; -- Closing Payments Cursor
4908
4909 IBY_PAYMENT_FORMAT_VAL_PVT.MAX_TREASURY_SYMBOLS(l_format_name,
4910 p_instruction_id,
4911 l_docErrorTab,
4912 l_docErrorRec,
4913 l_valid,
4914 l_error_message);
4915
4916 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
4917 x_result:=1;
4918 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
4919 x_result:=1;
4920 RETURN;
4921 END IF;
4922
4923 IBY_PAYMENT_FORMAT_VAL_PVT.SCHEDULE_NUMBER(l_format_name,
4924 p_instruction_id,
4925 l_docErrorTab,
4926 l_docErrorRec,
4927 l_valid,
4928 l_error_message);
4929
4930 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
4931 x_result:=1;
4932 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
4933 x_result:=1;
4934 RETURN;
4935 END IF;
4936
4937 IBY_PAYMENT_FORMAT_VAL_PVT.MANDATORY_PPD_PPDP_REASON_CODE(l_format_name,
4938 l_pay_instr_rec.payment_reason_code,
4939 l_docErrorTab,
4940 l_docErrorRec,
4941 l_valid,
4942 l_error_message);
4943
4944 IF(l_valid=g_FAILURE) THEN -- If Validation Error Comes Set x_result and Catch Other Validation Erros
4945 x_result:=1;
4946 ELSIF(l_valid=g_EXCEPTION) THEN -- If Some Unexpected Error Comes Set x_result and Return.
4947 x_result:=1;
4948 RETURN;
4949 END IF;
4950
4951
4952 END IF; -- End of IF pay_instr_data_csr%FOUND
4953 CLOSE pay_instr_data_csr; -- Closing Instruction Data Cursor
4954
4955 IBY_VALIDATIONSETS_PUB.insert_transaction_errors('N',l_docErrorTab);
4956
4957 EXCEPTION
4958 WHEN OTHERS THEN
4959 x_result := 1;
4960 l_docErrorRec.transaction_error_id := null;
4961 l_docErrorRec.error_code := 'UNEXPECTED_ERROR';
4962 l_docErrorRec.error_message := SQLERRM;
4963 IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(l_docErrorRec, l_docErrorTab);
4964 iby_payment_format_val_pvt.log_error_messages(FND_LOG.LEVEL_STATEMENT, l_format_name, SQLERRM);
4965 IBY_VALIDATIONSETS_PUB.insert_transaction_errors('N',l_docErrorTab);
4966
4967
4968 END FVSPPPDP;-- End of Procedure FVSPPPDP
4969
4970
4971
4972
4973
4974 ----------------------------------------------------------------------------------------------------------
4975
4976 -- Initialising Global Variables
4977 BEGIN
4978 g_FAILURE := -1; -- Corresponds To g_ERROR OF IBY_PAYMENT_FORMAT_VAL_PVT Package.
4979 g_EXCEPTION := -2; -- Corresponds To g_FAILURE OF IBY_PAYMENT_FORMAT_VAL_PVT Package.
4980 g_SUCCESS := 0;
4981
4982 ----------------------------------------------------------------------------------------------------------
4983
4984 END IBY_PAYMENT_FORMAT_VAL_PUB ;
[Home] [Help]