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