[Home] [Help]
PACKAGE BODY: APPS.IBY_PAYMENT_FORMAT_VAL_PVT
Source
1 PACKAGE BODY IBY_PAYMENT_FORMAT_VAL_PVT AS
2 /* $Header: ibyfvvlb.pls 120.24.12020000.2 2012/11/02 11:29:11 jnallam ship $ */
3 -------------------------------------------------------------------------------------------------------------------
4
5 g_FAILURE NUMBER;
6 g_ERROR NUMBER;
7 g_SUCCESS NUMBER;
8 g_module_name VARCHAR2(100);
9 g_current_level NUMBER;
10
11
12 -------------------------------------------------------------------------------------------------------------------
13 /*
14
15 PROCEDURE : SCHEDULE_NUMBER
16
17 This procedure is responsible for validating Schedule Number
18
19 */
20
21 PROCEDURE SCHEDULE_NUMBER
22 (
23 p_format_name IN VARCHAR2,
24 p_pinstr_id IN NUMBER,
25 p_docErrorTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.docErrorTabType,
26 p_docErrorRec IN OUT NOCOPY IBY_TRANSACTION_ERRORS%ROWTYPE,
27 x_error_code OUT NOCOPY NUMBER,
28 x_error_mesg OUT NOCOPY VARCHAR2
29 ) IS
30
31 CURSOR sch_num_csr(p_pinstr_id IBY_PAY_INSTRUCTIONS_ALL.payment_instruction_id%TYPE)
32 IS
33 SELECT pay_admin_assigned_ref_code
34 FROM IBY_PAY_INSTRUCTIONS_ALL
35 WHERE payment_instruction_id = p_pinstr_id;
36
37
38 l_constant NUMBER := 1;
39 l_incre NUMBER := 1;
40 l_char VARCHAR2(1);
41 l_position NUMBER;
42 l_sch_num_rec sch_num_csr%ROWTYPE;
43 l_char_string VARCHAR2(40);
44 l_module_name VARCHAR2(200) := g_module_name || 'SCHEDULE_NUMBER';
45 l_message VARCHAR2(1000);
46
47
48 BEGIN
49 x_error_code := g_SUCCESS;
50 l_message := 'Validating schedule number, Parameters: p_format_name = ' || p_format_name || ', p_instruction_id = ' || p_pinstr_id ;
51 log_error_messages(FND_LOG.LEVEL_STATEMENT, l_module_name, l_message);
52
53
54 OPEN sch_num_csr(p_pinstr_id);
55 FETCH sch_num_csr into l_sch_num_rec;
56
57
58 IF (sch_num_csr%FOUND) AND (l_sch_num_rec.pay_admin_assigned_ref_code IS NOT NULL) THEN -- If pay_admin_assigned_ref_code is not null then only perform validation
59 -- Handling Of Schedule Number For these two formats --
60 IF p_format_name IN ('FVCONCTX','FVTICTX') THEN
61 -- Only alpha numeric characters are allowed.
62 l_char_string := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890';
63
64 FOR l_incre in 1..least(11, length(l_sch_num_rec.pay_admin_assigned_ref_code))
65 LOOP
66 l_position := INSTR(l_char_string,SUBSTR(upper(l_sch_num_rec.pay_admin_assigned_ref_code),l_incre,1)) ;
67 EXIT WHEN l_position=0;
68 END LOOP;
69
70 IF l_position=0 THEN
71 l_message := 'pay_admin_assigned_ref_code = ' || l_sch_num_rec.pay_admin_assigned_ref_code;
72 log_error_messages(FND_LOG.LEVEL_STATEMENT, l_module_name, l_message);
73 x_error_mesg := 'The Reference Assigned by Administrator number must contain only valid characters of "0-9" or "A-Z" ';
74 p_docErrorRec.transaction_error_id := null;
75 p_docErrorRec.error_code := 'INVALID_SCHEDULE_NUMBER';
76 p_docErrorRec.error_message := x_error_mesg;
77 x_error_code := g_ERROR;
78 IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(p_docErrorRec,p_docErrorTab);
79 log_error_messages(FND_LOG.LEVEL_STATEMENT, l_module_name, x_error_mesg);
80 RETURN;
81 END IF;
82
83 ELSE
84 FOR l_incre in 1..least(10, length(l_sch_num_rec.pay_admin_assigned_ref_code))
85 LOOP
86 l_position := INSTR(l_sch_num_rec.pay_admin_assigned_ref_code,'0',l_incre);
87 l_char := substr(l_sch_num_rec.pay_admin_assigned_ref_code,l_incre,l_constant);
88
89 IF (l_position = 1) AND (l_char = '0') THEN
90 l_message := 'pay_admin_assigned_ref_code = ' || l_sch_num_rec.pay_admin_assigned_ref_code;
91 log_error_messages(FND_LOG.LEVEL_STATEMENT, l_module_name, l_message);
92 x_error_mesg := 'The Reference Assigned by Administrator number must contain only valid characters of "0-9", "A-Z" or "-" and the first character must not be a zero.';
93 p_docErrorRec.transaction_error_id := null;
94 p_docErrorRec.error_code := 'INVALID_SCHEDULE_NUMBER';
95 p_docErrorRec.error_message := x_error_mesg;
96 x_error_code := g_ERROR;
97 IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(p_docErrorRec,p_docErrorTab);
98 log_error_messages(FND_LOG.LEVEL_STATEMENT, l_module_name, x_error_mesg);
99 RETURN;
100
101 END IF; -- if first position of schedulenum is zero
102
103 IF NOT ((UPPER(l_char) BETWEEN 'A' and 'Z') OR (l_char between '0' and '9') OR (l_char = '-')) THEN
104 l_message := 'pay_admin_assigned_ref_code = ' || l_sch_num_rec.pay_admin_assigned_ref_code;
105 log_error_messages(FND_LOG.LEVEL_STATEMENT, l_module_name, l_message);
106 x_error_mesg := 'The Reference Assigned by Administrator number must contain only valid characters of "0-9", "A-Z" or "-" and the first character must not be a zero.';
107 p_docErrorRec.transaction_error_id := null;
108 p_docErrorRec.error_code := 'INVALID_SCHEDULE_NUMBER';
109 p_docErrorRec.error_message := x_error_mesg;
110 x_error_code := g_ERROR;
111 IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(p_docErrorRec,p_docErrorTab);
112 log_error_messages(FND_LOG.LEVEL_STATEMENT, l_module_name, x_error_mesg);
113 RETURN;
114 END IF; -- schedulenum has characters other than A-Z 1-0 -
115
116 END LOOP;
117 END IF;
118
119 END IF; -- sch_num_csr%FOUND
120 CLOSE sch_num_csr;
121
122 EXCEPTION
123 WHEN OTHERS THEN
124 x_error_code := g_FAILURE;
125 x_error_mesg := SQLERRM;
126 log_error_messages(FND_LOG.LEVEL_STATEMENT, l_module_name, x_error_mesg);
127 p_docErrorRec.transaction_error_id := null;
128 p_docErrorRec.error_code := 'UNEXPECTED_ERROR';
129 p_docErrorRec.error_message := x_error_mesg;
130 IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(p_docErrorRec, p_docErrorTab);
131 IBY_VALIDATIONSETS_PUB.insert_transaction_errors('N',p_docErrorTab);
132
133 END SCHEDULE_NUMBER;
134
135 -------------------------------------------------------------------------------------------------------------------
136 /*
137
138 PROCEDURE : SUPPLIER_TYPE
139
140 This procedure is responsible for Validating Supplier Type .
141
142 If Format Name Is In ('FVTPCCD','FVTIACHP','FVSPCCD', 'FVSPCCDP', 'FVTICTX' ,'FVBLCCDP') ONLY NON-EMPLOYEES ARE ALLOWED.
143
144 If Format Name Is In ('FVTPPPD','FVTPPPDP','FVSPPPDP','FVSPPPD', 'FVBLPPDP','FVBLSLTR') ONLY EMPLYEES ARE ALLOWED
145 If Format Name Is In ('FVTIACHB','FVBLNCR','FVSPNCR') EITHER ALL EMPLOYEES OR ALL NON EMPLOYEES ARE ALLOWED.
146
147 */
148
149 PROCEDURE SUPPLIER_TYPE
150 (
151 p_format_name IN VARCHAR2,
152 p_instruction_id IN NUMBER,
153 p_docErrorTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.docErrorTabType,
154 p_docErrorRec IN OUT NOCOPY IBY_TRANSACTION_ERRORS%ROWTYPE,
155 x_error_code OUT NOCOPY NUMBER,
156 x_error_mesg OUT NOCOPY VARCHAR2
157 )IS
158
159 -- Get Inovice ID From IBY_DOCS_PAYABLE_ALL
160 -- Bug : 11740157 : Validate only active documents.
161 CURSOR all_invoices_csr(p_instruction_id NUMBER)
162 IS
163 SELECT calling_app_doc_unique_ref2
164 FROM IBY_DOCS_PAYABLE_ALL -- added all
165 WHERE upper(document_type)='STANDARD'
166 AND calling_app_id=200
167 AND UPPER(payment_function) IN ('PAYABLES_DISB','EMPLOYEE_EXP')
168 AND document_status = 'PAYMENT_CREATED'
169 AND payment_id in (select payment_id
170 from iby_payments_all
171 where payment_instruction_id = p_instruction_id);
172
173
174
175 -- Get Vendor Id From AP_INVOICES
176 CURSOR all_vendors_csr(p_invoice_id NUMBER)
177 IS
178 SELECT vendor_id, invoice_num
179 FROM AP_INVOICES
180 WHERE
181 invoice_id=p_invoice_id;
182
183 -- Get Vendor Type
184 CURSOR vendor_type_csr(p_vendor_id NUMBER)
185 IS
186 SELECT nvl(vendor_type_lookup_code, 'XXX') -- Bug 6398944
187 FROM AP_SUPPLIERS
188 WHERE vendor_id = p_vendor_id;
189
190
191 l_invoice_id NUMBER(15,0);
192 l_vendor_id NUMBER(15,0);
193 l_vendor_type VARCHAR2(30);
194 l_emp boolean; -- If Employee
195 l_non_emp boolean; -- If Other Than Employee
196 l_inv_str VARCHAR2(30);
197 l_module_name VARCHAR2(200) := g_module_name || 'SUPPLIER_TYPE';
198 l_message VARCHAR2(1000);
199 l_invoice_num ap_invoices_all.invoice_num%TYPE;
200
201
202 BEGIN
203 x_error_code := g_SUCCESS;
204 l_message := 'Validating Supplier Type, Parameters: p_format_name = ' || p_format_name || ', p_payment_instruction_id = ' || p_instruction_id ;
205 log_error_messages(FND_LOG.LEVEL_STATEMENT, l_module_name, l_message);
206
207 IF p_format_name IN ('FVTPCCD','FVTIACHP','FVSPCCD','FVSPCCDP', 'FVTICTX' ,'FVBLCCDP') THEN
208
209
210 OPEN all_invoices_csr(p_instruction_id); -- Opening all invocies cursor
211
212 LOOP -- Reading All Invoices
213
214 FETCH all_invoices_csr INTO l_inv_str; -- Getting Data
215 EXIT WHEN all_invoices_csr%NOTFOUND;
216 l_invoice_id := TO_NUMBER(l_inv_str);
217
218 l_message := 'Inside all_invoices_csr, l_invoice_id = ' || l_inv_str;
219
220 OPEN all_vendors_csr(l_invoice_id);
221 FETCH all_vendors_csr INTO l_vendor_id, l_invoice_num;
222 CLOSE all_vendors_csr;
223
224 OPEN vendor_type_csr(l_vendor_id);
225 FETCH vendor_type_csr INTO l_vendor_type;
226 CLOSE vendor_type_csr;
227 -- All Vendors Should Be Non-Employee
228 IF UPPER(l_vendor_type) = 'EMPLOYEE' THEN
229 l_message := 'invoice_id = ' || l_inv_str || ' vendor_id = ' || l_vendor_id || ' vendor_type = ' || l_vendor_type;
230 log_error_messages(FND_LOG.LEVEL_STATEMENT, l_module_name, l_message);
231 x_error_mesg := 'The vendor for invoice '|| l_invoice_num || ' cannot be of type EMPLOYEE.';
232 p_docErrorRec.transaction_error_id := null;
233 p_docErrorRec.error_code := 'INV_IS_EMPLOYEE';
234 p_docErrorRec.error_message := x_error_mesg;
235 x_error_code := g_ERROR;
236 IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(p_docErrorRec, p_docErrorTab);
237 log_error_messages(FND_LOG.LEVEL_STATEMENT, l_module_name, x_error_mesg);
238 END IF;
239
240
241 END LOOP; -- End of For Each Invoice Loop
242 CLOSE all_invoices_csr; -- Closing Invoices Cursor
243
244 ELSIF p_format_name IN ('FVTPPPD','FVTPPPDP','FVSPPPDP','FVSPPPD','FVBLPPDP','FVBLSLTR') THEN
245
246 OPEN all_invoices_csr(p_instruction_id); -- Opening All Invoice Cursor
247
248 LOOP -- Reading All Invoices
249
250 FETCH all_invoices_csr INTO l_inv_str;
251 EXIT WHEN all_invoices_csr%NOTFOUND;
252
253 l_invoice_id := TO_NUMBER(l_inv_str);
254
255 OPEN all_vendors_csr(l_invoice_id); -- Opeing Vendors Cursor
256 FETCH all_vendors_csr INTO l_vendor_id, l_invoice_num;
257 CLOSE all_vendors_csr;
258 OPEN vendor_type_csr(l_vendor_id);
259
260 FETCH vendor_type_csr INTO l_vendor_type;
261 CLOSE vendor_type_csr;
262
263 -- All Vendors Should Be Employee
264 IF UPPER(l_vendor_type) <> 'EMPLOYEE' THEN
265 l_message := 'invoice_id = ' || l_inv_str || ' vendor_id = ' || l_vendor_id || 'vendor_type = ' || l_vendor_type;
266 log_error_messages(FND_LOG.LEVEL_STATEMENT, l_module_name, l_message);
267 x_error_mesg := 'The vendor for invoice '|| l_invoice_num || ' must be of type EMPLOYEE.';
268 p_docErrorRec.transaction_error_id := null;
269 p_docErrorRec.error_code := 'INV_IS_NOT_EMPLOYEE';
270 p_docErrorRec.error_message := x_error_mesg;
271 x_error_code := g_ERROR;
272 IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(p_docErrorRec, p_docErrorTab);
273 log_error_messages(FND_LOG.LEVEL_STATEMENT, l_module_name, x_error_mesg);
274 END IF;
275
276
277 END LOOP; -- Ending For Each Invoice Loop
278 CLOSE all_invoices_csr; -- Closing Invoice Cursor
279
280 ELSIF p_format_name IN ('FVTIACHB','FVBLNCR','FVSPNCR') THEN
281
282 OPEN all_invoices_csr(p_instruction_id);
283 l_emp:=false;
284 l_non_emp:=false;
285
286 LOOP -- Reading All Invoices
287
288 FETCH all_invoices_csr INTO l_inv_str;
289 EXIT WHEN all_invoices_csr%NOTFOUND;
290
291 l_invoice_id := TO_NUMBER(l_inv_str);
292
293 OPEN all_vendors_csr(l_invoice_id);
294 FETCH all_vendors_csr INTO l_vendor_id, l_invoice_num;
295 CLOSE all_vendors_csr;
296
297 OPEN vendor_type_csr(l_vendor_id);
298 FETCH vendor_type_csr INTO l_vendor_type;
299 CLOSE vendor_type_csr;
300
301 IF UPPER(l_vendor_type) = 'EMPLOYEE' THEN
302 l_emp:=TRUE;
303 ELSE
304 l_non_emp:=TRUE;
305 END IF;
306 -- Vendor Type Cannot Be a Mix Of Employee And Non Employee. Either All Employees Or All Non-Employees.
307 IF (l_emp=true AND l_non_emp=TRUE) THEN
308 l_message := 'invoice_id = ' || l_inv_str || ' vendor_id = ' || l_vendor_id || ' vendor_type = ' || l_vendor_type;
309 log_error_messages(FND_LOG.LEVEL_STATEMENT, l_module_name, l_message);
310 x_error_mesg := 'All selected invoices must have one vendor type, either EMPLOYEE or NON-EMPLOYEE.';
311 p_docErrorRec.transaction_error_id := null;
312 p_docErrorRec.error_code := 'INV_IS_MIXED_VENDOR_TYPE';
313 p_docErrorRec.error_message := x_error_mesg;
314 x_error_code := g_ERROR;
315 IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(p_docErrorRec, p_docErrorTab);
316 log_error_messages(FND_LOG.LEVEL_STATEMENT, l_module_name, x_error_mesg);
317 CLOSE all_invoices_csr;
318 RETURN;
319 END IF;
320
321
322 END LOOP; -- Ending Of For Each Invoice Loop
323 CLOSE all_invoices_csr; -- Closing Invoice Cursor
324
325 END IF;
326
327
328 EXCEPTION
329 WHEN OTHERS THEN
330 x_error_code := g_FAILURE;
331 x_error_mesg := SQLERRM;
332 log_error_messages(FND_LOG.LEVEL_STATEMENT, l_module_name, x_error_mesg);
333 p_docErrorRec.transaction_error_id := null;
334 p_docErrorRec.error_code := 'UNEXPECTED_ERROR';
335 p_docErrorRec.error_message := x_error_mesg;
336 IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(p_docErrorRec, p_docErrorTab);
337 IBY_VALIDATIONSETS_PUB.insert_transaction_errors('N',p_docErrorTab);
338
339 END SUPPLIER_TYPE;
340
341 -------------------------------------------------------------------------------------------------------------------
342 /*
343
344 PROCEDURE : TREASURY_SYMBOLS_PROCESS
345
346 This procedure is responsible to insert the treasury symbol into the table FV_TP_TS_AMT_DATA along with the amount.
347 This table will later be used for the maximum treasury symbol validations.
348
349 */
350
351
352 PROCEDURE TREASURY_SYMBOLS_PROCESS
353 (
354 p_format_name IN VARCHAR2,
355 p_instruction_id IN NUMBER,
356 p_payment_id IN NUMBER,
357 p_invoice_id IN NUMBER,
358 p_docErrorTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.docErrorTabType,
359 p_docErrorRec IN OUT NOCOPY IBY_TRANSACTION_ERRORS%ROWTYPE,
360 x_error_code OUT NOCOPY NUMBER,
361 x_error_mesg OUT NOCOPY VARCHAR2
362 )IS
363
364
365 -- Get Distribution Code Combination Id And SOB
366 CURSOR dist_sob_csr(p_invoice_id NUMBER)
367 IS
368 SELECT apid.dist_code_combination_id,
369 apid.set_of_books_id,
370 apid.org_id,
371 apid.amount, -- added this for 5466103
372 glpv.chart_of_accounts_id
373 FROM ap_invoice_distributions_all apid,
374 gl_ledgers_public_v glpv
375 WHERE apid.invoice_id = p_invoice_id
376 AND apid.set_of_books_id = glpv.ledger_id;
377
378 -- Get Treasury Symbol For Given Fund Value And Ledger Id
379 CURSOR treasury_symbol_csr(p_fund_value VARCHAR2 , p_ledger_id NUMBER)
380 IS
381 SELECT fvfp.fund_value fund_value,
382 fvts.treasury_symbol treasury_symbol
383 FROM fv_fund_parameters fvfp ,
384 fv_treasury_symbols fvts
385 WHERE fvfp.treasury_symbol_id = fvts.treasury_symbol_id
386 AND
387 fvfp.set_of_books_id = fvts.set_of_books_id
388 AND
389 fvfp.fund_value = p_fund_value
390 AND
391 fvfp.set_of_books_id = p_ledger_id ;
392
393 /* comment this out since we need amounts at the distribution level, not header level -- bug 5466103*/
394 -- Get Payment Amount And Instruction id Corresponding To Invoice Id(Bug 14542468)
395 CURSOR payid_amt_csr(p_invoice_id VARCHAR2, p_payment_id number)
396 IS
397 SELECT ibydocpay.payment_amount
398 FROM iby_docs_payable_all ibydocpay,
399 iby_payments_all ibypmt
400 WHERE ibydocpay.payment_id = p_payment_id
401 AND ibydocpay.calling_app_doc_unique_ref2 = p_invoice_id
402 AND ibydocpay.payment_id=ibypmt.payment_id
403 AND ibydocpay.calling_app_id=200
404 AND UPPER(ibydocpay.document_type)='STANDARD'
405 AND UPPER(ibydocpay.payment_function) IN ('PAYABLES_DISB','EMPLOYEE_EXP');
406
407
408
409
410
411 l_dist_sob_rec dist_sob_csr%ROWTYPE;
412 l_tas_rec treasury_symbol_csr%ROWTYPE;
413 l_amt_id_rec payid_amt_csr%ROWTYPE;
414
415 l_dist_ccid AP_INVOICE_DISTRIBUTIONS.dist_code_combination_id%TYPE;
416 l_segment_column BOOLEAN;
417 apps_id NUMBER(10) := 101;
418 l_flex_code VARCHAR2(20) := 'GL#';
419 l_seg_name VARCHAR2(30);
420 l_fund_val VARCHAR2(100);
421 l_row_exist NUMBER;
422
423 l_valid NUMBER;
424 l_message VARCHAR2(1000);
425 l_module_name VARCHAR2(200) := g_module_name || 'TREASURY_SYMBOLS_PROCESS';
426
427 BEGIN
428 x_error_code := g_SUCCESS;
429
430 l_message := 'Running Treasury Symbols Process, Parameters: p_format_name = ' || p_format_name || ', p_invoice_id = ' || p_invoice_id || ' p_payment_id = ' || p_payment_id;
431 log_error_messages(FND_LOG.LEVEL_STATEMENT, l_module_name, l_message);
432
433
434 -- Get Distribution Code Combination Id For The Invoice Id
435
436 OPEN dist_sob_csr(p_invoice_id); -- Opening Distribution And SOB Cursor
437
438 LOOP
439
440
441 FETCH dist_sob_csr INTO l_dist_sob_rec; -- Fetching Data From Distribution And SOB Cursor
442 EXIT WHEN dist_sob_csr%NOTFOUND;
443
444 -- Finding The Balancing Segment For SOB
445 l_segment_column := FND_FLEX_APIS.GET_SEGMENT_COLUMN(apps_id,
446 l_flex_code,
447 l_dist_sob_rec.chart_of_accounts_id,
448 'GL_BALANCING',
449 l_seg_name);
450
451 IF l_segment_column THEN -- If Exists
452
453 EXECUTE IMMEDIATE 'Select ' || l_seg_name || ' from GL_CODE_COMBINATIONS where code_combination_id= :ccid' INTO l_fund_val USING l_dist_sob_rec.dist_code_combination_id;
454
455 OPEN treasury_symbol_csr(l_fund_val,l_dist_sob_rec.set_of_books_id);
456 FETCH treasury_symbol_csr INTO l_tas_rec;
457
458 IF treasury_symbol_csr%NOTFOUND THEN
459 x_error_mesg := 'Payment Format Program Aborts as the Funds are not associated with Treasury Symbols. Please associate the fund value ' || l_fund_val ||' to a Treasury Symbol in Federal Administrator.';
460 p_docErrorRec.transaction_error_id := null;
461 p_docErrorRec.error_code := 'NO_TREASURY_SYMBOL';
462 p_docErrorRec.error_message := x_error_mesg;
463 x_error_code := g_ERROR;
464 IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(p_docErrorRec, p_docErrorTab);
465 log_error_messages(FND_LOG.LEVEL_STATEMENT, l_module_name, x_error_mesg);
466
467 ELSE
468 l_valid := g_SUCCESS; -- initialize l_valid
469 IF p_format_name IN ('FVSPCCD','FVSPCCDP','FVSPNCR','FVSPPPD','FVSPPPDP', 'FVBLCCDP', 'FVBLPPDP') THEN
470
471
472 TAS_VALIDATION(p_format_name,
473 l_tas_rec.treasury_symbol,
474 p_docErrorTab ,
475 p_docErrorRec ,
476 l_valid,
477 x_error_mesg );
478
479 END IF; --add this end if, we want to do TAS validation for above formats, but we want
480 --to do the insert below for all formats
481
482 IF(l_valid = g_SUCCESS) THEN
483
484 /* -- Bug 5466103 */
485 -- Bug 14542468
486 OPEN payid_amt_csr(TO_CHAR(p_invoice_id), p_payment_id);
487 FETCH payid_amt_csr INTO l_amt_id_rec;
488 CLOSE payid_amt_csr;
489
490
491
492 SELECT count(*) INTO l_row_exist
493 FROM FV_TP_TS_AMT_DATA
494 WHERE
495 treasury_symbol=l_tas_rec.treasury_symbol
496 AND
497 payment_instruction_id= p_instruction_id;
498
499
500
501 IF l_row_exist=0 THEN
502
503 INSERT INTO
504 FV_TP_TS_AMT_DATA(treasury_symbol,
505 amount,
506 payment_instruction_id,
507 org_id,
508 set_of_books_id)
509 VALUES(l_tas_rec.treasury_symbol,
510 l_amt_id_rec.payment_amount, -- changed from l_dist_sob_rec.amount(Bug 14542468)
511 p_instruction_id,
512 l_dist_sob_rec.org_id,
513 l_dist_sob_rec.set_of_books_id);
514
515
516
517 ELSE
518 UPDATE
519 FV_TP_TS_AMT_DATA
520 SET
521 amount = amount + l_amt_id_rec.payment_amount -- changed from l_dist_sob_rec.amount(Bug 14542468)
522 WHERE
523 treasury_symbol=l_tas_rec.treasury_symbol
524 AND
525 payment_instruction_id= p_instruction_id;
526
527
528
529 END IF;
530 ELSIF(l_valid=g_ERROR) THEN
531 x_error_code := g_ERROR;
532 ELSIF(l_valid=g_FAILURE) THEN
533 x_error_code := g_FAILURE;
534 RETURN;
535
536 END IF;
537
538 CLOSE treasury_symbol_csr;
539 END IF;
540
541 -- END IF; comment this out and move it to the top
542
543 END IF; --End Of IF l_segment_column THEN
544
545
546 END LOOP; -- End Of Distribution Id and SOB Cursor Loop
547
548 CLOSE dist_sob_csr; -- Closing Distribution Id and SOB Cursor
549
550
551
552 EXCEPTION
553 WHEN OTHERS THEN
554 x_error_code := g_FAILURE;
555 x_error_mesg := SQLERRM;
556 log_error_messages(FND_LOG.LEVEL_STATEMENT, l_module_name, x_error_mesg);
557 p_docErrorRec.transaction_error_id := null;
558 p_docErrorRec.error_code := 'UNEXPECTED_ERROR';
559 p_docErrorRec.error_message := x_error_mesg;
560 IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(p_docErrorRec, p_docErrorTab);
561 IBY_VALIDATIONSETS_PUB.insert_transaction_errors('N',p_docErrorTab);
562
563 END TREASURY_SYMBOLS_PROCESS;
564
565 -------------------------------------------------------------------------------------------------------------------
566 /*
567
568 PROCEDURE : MAX_TREASURY_SYMBOLS
569
570 This procedure is responsible to validate for maximum number of treasury symbols in a payment batch.
571
572 */
573
574
575 PROCEDURE MAX_TREASURY_SYMBOLS
576 (
577 p_format_name IN VARCHAR2,
578 p_instruction_id IN NUMBER,
579 p_docErrorTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.docErrorTabType,
580 p_docErrorRec IN OUT NOCOPY IBY_TRANSACTION_ERRORS%ROWTYPE,
581 x_error_code OUT NOCOPY NUMBER,
582 x_error_mesg OUT NOCOPY VARCHAR2
583 )IS
584
585 l_lmt_blk_fmt_val VARCHAR2(10);
586 l_count_tas NUMBER;
587 l_message VARCHAR2(1000);
588 l_module_name VARCHAR2(200) := g_module_name || 'MAX_TREASURY_SYMBOLS';
589
590 BEGIN
591 x_error_code := g_SUCCESS;
592
593 l_message := 'Validating Max Treasury Symbols, Parameters: p_format_name = ' || p_format_name || ', p_instruction_id = ' || p_instruction_id ;
594 log_error_messages(FND_LOG.LEVEL_STATEMENT, l_module_name, l_message);
595
596
597 IF p_format_name IN ('FVBLNCR','FVBLSLTR','FVBLCCDP','FVBLPPDP','FVCOCCDP','FVCOPPDP') THEN
598
599 l_lmt_blk_fmt_val:=FND_PROFILE.VALUE('FV_BULK_FORMAT_LIMIT');
600
601 IF UPPER(l_lmt_blk_fmt_val)= 'Y' THEN
602
603 SELECT COUNT(*) INTO l_count_tas
604 FROM FV_TP_TS_AMT_DATA
605 WHERE payment_instruction_id = p_instruction_id;
606
607 IF l_count_tas > 10 THEN
608 x_error_mesg := 'Payment format aborts as it contains more than 10 Treasury symbols';
609 p_docErrorRec.transaction_error_id := null;
610 p_docErrorRec.error_code := 'TREASURY_SYMBOL_LIMIT';
611 p_docErrorRec.error_message := x_error_mesg;
612 x_error_code := g_ERROR;
613 IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(p_docErrorRec, p_docErrorTab);
614 log_error_messages(FND_LOG.LEVEL_STATEMENT, l_module_name, x_error_mesg);
615 END IF;
616
617 END IF;
618
619 ELSIF p_format_name IN('FVSPCCD','FVSPCCDP','FVSPNCR','FVSPPPD','FVSPPPDP') THEN
620
621 SELECT COUNT(*) INTO l_count_tas
622 FROM FV_TP_TS_AMT_DATA
623 WHERE payment_instruction_id = p_instruction_id;
624
625 IF l_count_tas > 10 THEN
626 x_error_mesg := 'Payment format aborts as it contains more than 10 Treasury symbols';
627 p_docErrorRec.transaction_error_id := null;
628 p_docErrorRec.error_code := 'TREASURY_SYMBOLS_LIMIT';
629 p_docErrorRec.error_message := x_error_mesg;
630 x_error_code := g_ERROR;
631 IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(p_docErrorRec, p_docErrorTab);
632 log_error_messages(FND_LOG.LEVEL_STATEMENT, l_module_name, x_error_mesg);
633 END IF;
634 END IF;
635
636
637 EXCEPTION
638 WHEN OTHERS THEN
639 x_error_code := g_FAILURE;
640 x_error_mesg := SQLERRM;
641 log_error_messages(FND_LOG.LEVEL_STATEMENT, l_module_name, x_error_mesg);
642 p_docErrorRec.transaction_error_id := null;
643 p_docErrorRec.error_code := 'UNEXPECTED_ERROR';
644 p_docErrorRec.error_message := x_error_mesg;
645 IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(p_docErrorRec, p_docErrorTab);
646 IBY_VALIDATIONSETS_PUB.insert_transaction_errors('N',p_docErrorTab);
647
648 END MAX_TREASURY_SYMBOLS;
649
650 -------------------------------------------------------------------------------------------------------------------
651 /*
652
653 PROCEDURE : AGENCY_ADDRESS
654
655 This procedure is respnosible for Validation of Agency Address.
656
657 Agency Address should not be NULL.
658
659 */
660
661
662 PROCEDURE AGENCY_ADDRESS
663 (
664 p_format_name IN VARCHAR2,
665 p_org_id IN NUMBER,
666 p_docErrorTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.docErrorTabType,
667 p_docErrorRec IN OUT NOCOPY IBY_TRANSACTION_ERRORS%ROWTYPE,
668 x_error_code OUT NOCOPY NUMBER,
669 x_error_mesg OUT NOCOPY VARCHAR2
670 )IS
671
672 CURSOR get_agency_address_csr(p_org_id NUMBER)
673 IS
674 SELECT address_line_1, address_line_2, town_or_city, region_2, postal_code
675 FROM fv_system_parameters_v
676 WHERE
677 ou_org_id=p_org_id;
678
679 l_agencyadd_rec get_agency_address_csr%ROWTYPE ;
680 l_message VARCHAR2(1000);
681 l_module_name VARCHAR2(200) := g_module_name || 'AGENCY_ADDRESS';
682
683 BEGIN
684 x_error_code := g_SUCCESS;
685
686 l_message := 'Validating Agency Address, Parameters: p_format_name = ' || p_format_name || ', p_org_id = ' || p_org_id ;
687 log_error_messages(FND_LOG.LEVEL_STATEMENT, l_module_name, l_message);
688
689 OPEN get_agency_address_csr(p_org_id);
690 FETCH get_agency_address_csr INTO l_agencyadd_rec;
691 CLOSE get_agency_address_csr;
692
693 -- If all are blank
694 IF (l_agencyadd_rec.address_line_1 IS NULL ) AND
695 (l_agencyadd_rec.address_line_2 IS NULL ) AND
696 (l_agencyadd_rec.town_or_city IS NULL ) AND
697 (l_agencyadd_rec.region_2 IS NULL ) AND
698 (l_agencyadd_rec.postal_code IS NULL )
699 THEN
700 x_error_mesg := 'Invalid address for Agency';
701 p_docErrorRec.transaction_error_id := null;
702 p_docErrorRec.error_code := 'INVALID_AGENCY_ADDRESS';
703 p_docErrorRec.error_message := x_error_mesg;
704 x_error_code := g_ERROR;
705 IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(p_docErrorRec, p_docErrorTab);
706 log_error_messages(FND_LOG.LEVEL_STATEMENT, l_module_name, x_error_mesg);
707 END IF;
708
709
710 EXCEPTION
711 WHEN OTHERS THEN
712 x_error_code := g_FAILURE;
713 x_error_mesg := SQLERRM;
714 log_error_messages(FND_LOG.LEVEL_STATEMENT, l_module_name, x_error_mesg);
715 p_docErrorRec.transaction_error_id := null;
716 p_docErrorRec.error_code := 'UNEXPECTED_ERROR';
717 p_docErrorRec.error_message := x_error_mesg;
718 IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(p_docErrorRec, p_docErrorTab);
719 IBY_VALIDATIONSETS_PUB.insert_transaction_errors('N',p_docErrorTab);
720
721 END AGENCY_ADDRESS;
722
723 -------------------------------------------------------------------------------------------------------------------
724 /*
725
726 PROCEDURE : PAYEE_ADDRESS
727
728 This procedure is responsible for Validation of Payee Address
729
730 Payee Address should not be NULL.
731
732 */
733
734
735 PROCEDURE PAYEE_ADDRESS
736 (
737 p_format_name IN VARCHAR2,
738 p_payment_id IN NUMBER,
739 p_docErrorTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.docErrorTabType,
740 p_docErrorRec IN OUT NOCOPY IBY_TRANSACTION_ERRORS%ROWTYPE,
741 x_error_code OUT NOCOPY NUMBER,
742 x_error_mesg OUT NOCOPY VARCHAR2
743 )IS
744
745 -- Bug : 11740157 : Validate only active payments.
746 CURSOR get_payee_address_csr(p_payment_id NUMBER)
747 IS
748 SELECT payee_address1, payee_address2, payee_address3, payee_address4, payee_city, payee_postal_code, payee_state
749 FROM iby_payments_all
750 WHERE
751 PAYMENT_ID=p_payment_id
752 AND payment_status = 'INSTRUCTION_CREATED' ;
753
754 l_payeeadd_rec get_payee_address_csr%ROWTYPE ;
755 l_message VARCHAR2(1000);
756 l_module_name VARCHAR2(200) := g_module_name || 'PAYEE_ADDRESS';
757
758 BEGIN
759 x_error_code := g_SUCCESS;
760
761 l_message := 'Validating Payee Address, Parameters: p_format_name = ' || p_format_name || ', p_payment_id = ' || p_payment_id ;
762 log_error_messages(FND_LOG.LEVEL_STATEMENT, l_module_name, l_message);
763
764 OPEN get_payee_address_csr(p_payment_id);
765 FETCH get_payee_address_csr INTO l_payeeadd_rec;
766 CLOSE get_payee_address_csr;
767
768 -- If Any Of The Address Field Is Blank
769 IF ((l_payeeadd_rec.payee_address1 IS NULL ) AND
770 (l_payeeadd_rec.payee_address2 IS NULL ) AND
771 (l_payeeadd_rec.payee_address3 IS NULL ) AND
772 (l_payeeadd_rec.payee_address4 IS NULL )) OR
773 (l_payeeadd_rec.payee_city IS NULL ) OR
774 (l_payeeadd_rec.payee_postal_code IS NULL ) OR
775 (l_payeeadd_rec.payee_state IS NULL )
776 THEN
777 x_error_mesg := 'Invalid address for Vendor';
778 p_docErrorRec.transaction_error_id := null;
779 p_docErrorRec.error_code := 'INVALID_PAYEE_ADDRESS';
780 p_docErrorRec.error_message := x_error_mesg;
781 x_error_code := g_ERROR;
782 IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(p_docErrorRec, p_docErrorTab);
783 log_error_messages(FND_LOG.LEVEL_STATEMENT, l_module_name, x_error_mesg);
784 END IF;
785
786
787 EXCEPTION
788 WHEN OTHERS THEN
789 x_error_code := g_FAILURE;
790 x_error_mesg := SQLERRM;
791 log_error_messages(FND_LOG.LEVEL_STATEMENT, l_module_name, x_error_mesg);
792 p_docErrorRec.transaction_error_id := null;
793 p_docErrorRec.error_code := 'UNEXPECTED_ERROR';
794 p_docErrorRec.error_message := x_error_mesg;
795 IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(p_docErrorRec, p_docErrorTab);
796 IBY_VALIDATIONSETS_PUB.insert_transaction_errors('N',p_docErrorTab);
797
798 END PAYEE_ADDRESS;
799
800 -------------------------------------------------------------------------------------------------------------------
801 /*
802
803 PROCEDURE : MAX_PAYMENT_AMT
804
805 This procedure is responsible for Validation of Payment Amount Exceeding the limit of 9,999,999.99
806
807 */
808
809
810 PROCEDURE MAX_PAYMENT_AMT
811 (
812 p_format_name IN VARCHAR2,
813 p_instruction_id IN NUMBER,
814 p_payment_amount IN NUMBER,
815 p_docErrorTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.docErrorTabType,
816 p_docErrorRec IN OUT NOCOPY IBY_TRANSACTION_ERRORS%ROWTYPE,
817 x_error_code OUT NOCOPY NUMBER,
818 x_error_mesg OUT NOCOPY VARCHAR2
819 )IS
820
821 l_message VARCHAR2(1000);
822 l_module_name VARCHAR2(200) := g_module_name || 'MAX_PAYMENT_AMT';
823
824 BEGIN
825 x_error_code := g_SUCCESS;
826
827 l_message := 'Validating Max Payment Amount, Parameters: p_format_name = ' || p_format_name || ', p_instruction_id = ' || p_instruction_id ;
828 log_error_messages(FND_LOG.LEVEL_STATEMENT, l_module_name, l_message);
829
830 IF p_payment_amount > 9999999.99 THEN
831 x_error_mesg := 'Payment Amount Exceeds the limit of $9,999,999.99';
832 p_docErrorRec.transaction_error_id := null;
833 p_docErrorRec.error_code := 'INVALID_BULK_NCR_CHK_PAY';
834 p_docErrorRec.error_message := x_error_mesg;
835 x_error_code := g_ERROR;
836 IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(p_docErrorRec, p_docErrorTab);
837 log_error_messages(FND_LOG.LEVEL_STATEMENT, l_module_name, x_error_mesg);
838 END IF;
839
840 EXCEPTION
841 WHEN OTHERS THEN
842 x_error_code := g_FAILURE;
843 x_error_mesg := SQLERRM;
844 log_error_messages(FND_LOG.LEVEL_STATEMENT, l_module_name, x_error_mesg);
845 p_docErrorRec.transaction_error_id := null;
846 p_docErrorRec.error_code := 'UNEXPECTED_ERROR';
847 p_docErrorRec.error_message := x_error_mesg;
848 IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(p_docErrorRec, p_docErrorTab);
849 IBY_VALIDATIONSETS_PUB.insert_transaction_errors('N',p_docErrorTab);
850
851 END MAX_PAYMENT_AMT;
852
853 -------------------------------------------------------------------------------------------------------------------
854 /*
855
856 PROCEDURE : MAX_PAYMENT_AMT_2
857
858 This procedure is responsible for Validation of Payment Amount Exceeding the limit of 999,999.99
859
860 */
861
862
863 PROCEDURE MAX_PAYMENT_AMT_2
864 (
865 p_format_name IN VARCHAR2,
866 p_instruction_id IN NUMBER,
867 p_payment_amount IN NUMBER,
868 p_docErrorTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.docErrorTabType,
869 p_docErrorRec IN OUT NOCOPY IBY_TRANSACTION_ERRORS%ROWTYPE,
870 x_error_code OUT NOCOPY NUMBER,
871 x_error_mesg OUT NOCOPY VARCHAR2
872 )IS
873
874 l_message VARCHAR2(1000);
875 l_module_name VARCHAR2(200) := g_module_name || 'MAX_PAYMENT_AMT_2';
876
877 BEGIN
878
879 x_error_code := g_SUCCESS;
880
881 l_message := 'Validating Max Payment Amount 2, Parameters: p_format_name = ' || p_format_name || ', p_instruction_id = ' || p_instruction_id ;
882 log_error_messages(FND_LOG.LEVEL_STATEMENT, l_module_name, l_message);
883
884 IF p_payment_amount > 999999.99 THEN
885 x_error_mesg := 'Payment Amount Exceeds the limit of $999,999.99';
886 p_docErrorRec.transaction_error_id := null;
887 p_docErrorRec.error_code := 'INVALID_INVOICE_AMOUNT';
888 p_docErrorRec.error_message := x_error_mesg;
889 x_error_code := g_ERROR;
890 IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(p_docErrorRec, p_docErrorTab);
891 log_error_messages(FND_LOG.LEVEL_STATEMENT, l_module_name, x_error_mesg);
892 END IF;
893
894
895 EXCEPTION
896 WHEN OTHERS THEN
897 x_error_code := g_FAILURE;
898 x_error_mesg := SQLERRM;
899 log_error_messages(FND_LOG.LEVEL_STATEMENT, l_module_name, x_error_mesg);
900 p_docErrorRec.transaction_error_id := null;
901 p_docErrorRec.error_code := 'UNEXPECTED_ERROR';
902 p_docErrorRec.error_message := x_error_mesg;
903 IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(p_docErrorRec, p_docErrorTab);
904 IBY_VALIDATIONSETS_PUB.insert_transaction_errors('N',p_docErrorTab);
905
906 END MAX_PAYMENT_AMT_2;
907
908 -------------------------------------------------------------------------------------------------------------------
909 /*
910
911 PROCEDURE : PAY_TAX_BENEFIT
912
913 This procedure is responsible such that Payments should pertain to Tax / Benefit only.
914
915 */
916
917
918 PROCEDURE PAY_TAX_BENEFIT
919 (
920 p_format_name IN VARCHAR2,
921 p_payment_id IN NUMBER,
922 p_invoice_id IN NUMBER,
923 p_docErrorTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.docErrorTabType,
924 p_docErrorRec IN OUT NOCOPY IBY_TRANSACTION_ERRORS%ROWTYPE,
925 x_error_code OUT NOCOPY NUMBER,
926 x_error_mesg OUT NOCOPY VARCHAR2
927 )IS
928
929 l_message VARCHAR2(1000);
930 l_payment_reason_code iby_docs_payable_all.payment_reason_code%TYPE;
931 l_vendor_type_lookup_code ap_suppliers.vendor_type_lookup_code%TYPE;
932 l_module_name VARCHAR2(200) := g_module_name || 'PAY_TAX_BENEFIT';
933
934 BEGIN
935 x_error_code := g_SUCCESS;
936
937 l_message := 'Validating Pay Tax Benefit, Parameters: p_format_name = ' || p_format_name || ', p_invoice_id = ' || p_invoice_id ;
938 log_error_messages(FND_LOG.LEVEL_STATEMENT, l_module_name, l_message);
939 -- Bug : 11740157 : Validate only active documents.
940 SELECT idpa.payment_reason_code,
941 asup.vendor_type_lookup_code
942 INTO l_payment_reason_code,
943 l_vendor_type_lookup_code
944 FROM IBY_DOCS_PAYABLE_ALL idpa, -- added all
945 ap_supplier_sites_all asst,
946 ap_suppliers asup
947 WHERE idpa.payment_id=p_payment_id
948 AND idpa.calling_app_doc_unique_ref2 = p_invoice_id
949 AND idpa.document_status = 'PAYMENT_CREATED'
950 AND idpa.calling_app_id=200
951 AND UPPER(idpa.payment_function) IN ('PAYABLES_DISB','EMPLOYEE_EXP')
952 AND idpa.supplier_site_id = asst.vendor_site_id
953 AND asst.vendor_id = asup.vendor_id ;
954
955 /* Validation rule: Bug 5457879: The validation set must look at the type of supplier. If the supplier is
956 a type of Employee then the only type of reason codes that the invoice can have is 'US_FV_B','US_FV_C',
957 'US_FV_D','US_FV_O','US_FV_R', 'US_FV_X'. For the Supplier type of Organization (Standard Supplier),
958 the reason code can only be 'US_FV_V'.
959 */
960
961 IF (l_vendor_type_lookup_code = 'EMPLOYEE') THEN
962 IF (l_payment_reason_code NOT IN ('US_FV_B','US_FV_C','US_FV_D','US_FV_O','US_FV_R', 'US_FV_X')) THEN
963 x_error_mesg := 'Payments to an Internal Employee can only have the following payment reason codes: SSA, VA, SSI, OPM, or RRB Benefit or Tax';
964 p_docErrorRec.transaction_error_id := null;
965 p_docErrorRec.error_code := 'INVALID_PAY_TAX_BENEFIT';
966 p_docErrorRec.error_message := x_error_mesg;
967 x_error_code := g_ERROR;
968 IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(p_docErrorRec, p_docErrorTab);
969 l_message := 'Invalid reason code for type of vendor, reason_code = ' || l_payment_reason_code || ', vendor_type_code = ' || l_vendor_type_lookup_code;
970 log_error_messages(FND_LOG.LEVEL_STATEMENT, l_module_name, l_message);
971 END IF;
972 ELSE -- vendor is of type organization
973 IF (l_payment_reason_code <> 'US_FV_V') THEN
974 x_error_mesg := 'Payments to a Standard Supplier can only have a payment reason code of ''Vendor Payment Sub-Type'' ';
975 p_docErrorRec.transaction_error_id := null;
976 p_docErrorRec.error_code := 'INVALID_PAY_TAX_BENEFIT';
977 p_docErrorRec.error_message := x_error_mesg;
978 x_error_code := g_ERROR;
979 IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(p_docErrorRec, p_docErrorTab);
980 l_message := 'Invalid reason code for type of vendor, reason_code = ' || l_payment_reason_code || ', vendor_type_code = ' || l_vendor_type_lookup_code;
981 log_error_messages(FND_LOG.LEVEL_STATEMENT, l_module_name, l_message);
982 END IF;
983 END IF;
984
985 EXCEPTION
986 WHEN OTHERS THEN
987 x_error_code := g_FAILURE;
988 x_error_mesg := SQLERRM;
989 log_error_messages(FND_LOG.LEVEL_STATEMENT, l_module_name, x_error_mesg);
990 p_docErrorRec.transaction_error_id := null;
991 p_docErrorRec.error_code := 'UNEXPECTED_ERROR';
992 p_docErrorRec.error_message := x_error_mesg;
993 IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(p_docErrorRec, p_docErrorTab);
994 IBY_VALIDATIONSETS_PUB.insert_transaction_errors('N',p_docErrorTab);
995
996
997 END PAY_TAX_BENEFIT;
998
999 -------------------------------------------------------------------------------------------------------------------
1000 /*
1001
1002 PROCEDURE : PAY_SALARY_TRAVEL
1003
1004 This procedure is responsible such that Payments should pertain to Salary / Travel only.
1005
1006 */
1007
1008
1009 PROCEDURE PAY_SALARY_TRAVEL
1010 (
1011 p_format_name IN VARCHAR2,
1012 p_reason_code IN VARCHAR2,
1013 p_docErrorTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.docErrorTabType,
1014 p_docErrorRec IN OUT NOCOPY IBY_TRANSACTION_ERRORS%ROWTYPE,
1015 x_error_code OUT NOCOPY NUMBER,
1016 x_error_mesg OUT NOCOPY VARCHAR2
1017 )IS
1018
1019 l_message VARCHAR2(1000);
1020 l_module_name VARCHAR2(200) := g_module_name || 'PAY_SALARY_TRAVEL';
1021
1022 BEGIN
1023 x_error_code := g_SUCCESS;
1024
1025 l_message := 'Validating Pay Salary Travel, Parameters: p_format_name = ' || p_format_name || ', p_reason_code = ' || p_reason_code ;
1026 log_error_messages(FND_LOG.LEVEL_STATEMENT, l_module_name, l_message);
1027
1028 IF p_reason_code IS NULL OR p_reason_code NOT IN ('US_FV_S','US_FV_T') THEN
1029 x_error_mesg := 'The Check Salary / Travel NCR payments can only be generated for Salary or Travel payments. The Reason Code must be related to Salary or Travel';
1030 p_docErrorRec.transaction_error_id := null;
1031 p_docErrorRec.error_code := 'INVALID_PAY_SALARY_TRAVEL';
1032 p_docErrorRec.error_message := x_error_mesg;
1033 x_error_code := g_ERROR;
1034 IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(p_docErrorRec, p_docErrorTab);
1035 log_error_messages(FND_LOG.LEVEL_STATEMENT, l_module_name, x_error_mesg);
1036 END IF;
1037
1038 EXCEPTION
1039 WHEN OTHERS THEN
1040 x_error_code := g_FAILURE;
1041 x_error_mesg := SQLERRM;
1042 log_error_messages(FND_LOG.LEVEL_STATEMENT, l_module_name, x_error_mesg);
1043 p_docErrorRec.transaction_error_id := null;
1044 p_docErrorRec.error_code := 'UNEXPECTED_ERROR';
1045 p_docErrorRec.error_message := x_error_mesg;
1046 IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(p_docErrorRec, p_docErrorTab);
1047 IBY_VALIDATIONSETS_PUB.insert_transaction_errors('N',p_docErrorTab);
1048
1049
1050 END PAY_SALARY_TRAVEL;
1051
1052 -------------------------------------------------------------------------------------------------------------------
1053 /*
1054
1055 PROCEDURE : PAYEE_SSN
1056
1057 This procedure is responsible for Validation of Payee Social Security Number.
1058
1059 Payee Social Security Number should not be NULL.
1060
1061 */
1062
1063
1064 PROCEDURE PAYEE_SSN
1065 (
1066 p_format_name IN VARCHAR2,
1067 p_ssn_tin IN VARCHAR2,
1068 p_payee_party_id IN VARCHAR2,
1069 p_docErrorTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.docErrorTabType,
1070 p_docErrorRec IN OUT NOCOPY IBY_TRANSACTION_ERRORS%ROWTYPE,
1071 x_error_code OUT NOCOPY NUMBER,
1072 x_error_mesg OUT NOCOPY VARCHAR2
1073 ) AS
1074
1075 l_party_name hz_parties.party_name%TYPE;
1076 l_message VARCHAR2(1000);
1077 l_module_name VARCHAR2(200) := g_module_name || 'PAYEE_SSN';
1078
1079 BEGIN
1080
1081 x_error_code := g_SUCCESS;
1082
1083 l_message := 'Validating Payee SSN, Parameters: p_format_name = ' || p_format_name || ', p_ssn_tin = ' || p_ssn_tin ;
1084 log_error_messages(FND_LOG.LEVEL_STATEMENT, l_module_name, l_message);
1085
1086 begin
1087 select party_name
1088 into l_party_name
1089 from hz_parties
1090 where party_id = p_payee_party_id;
1091 exception
1092 when others then
1093 l_party_name := null;
1094 end;
1095
1096
1097 IF p_ssn_tin IS NULL THEN
1098 x_error_mesg := 'SSN / TIN must be supplied for payee ' || l_party_name;
1099 p_docErrorRec.transaction_error_id := null;
1100 p_docErrorRec.error_code := 'NO_SSN_TIN';
1101 p_docErrorRec.error_message := x_error_mesg;
1102 x_error_code := g_ERROR;
1103 IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(p_docErrorRec, p_docErrorTab);
1104 log_error_messages(FND_LOG.LEVEL_STATEMENT, l_module_name, x_error_mesg);
1105 END IF;
1106
1107
1108 EXCEPTION
1109 WHEN OTHERS THEN
1110 x_error_code := g_FAILURE;
1111 x_error_mesg := SQLERRM;
1112 log_error_messages(FND_LOG.LEVEL_STATEMENT, l_module_name, x_error_mesg);
1113 p_docErrorRec.transaction_error_id := null;
1114 p_docErrorRec.error_code := 'UNEXPECTED_ERROR';
1115 p_docErrorRec.error_message := x_error_mesg;
1116 IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(p_docErrorRec, p_docErrorTab);
1117 IBY_VALIDATIONSETS_PUB.insert_transaction_errors('N',p_docErrorTab);
1118
1119 END PAYEE_SSN;
1120
1121 -------------------------------------------------------------------------------------------------------------------
1122 /*
1123
1124 PROCEDURE : TAS_VALIDATION
1125
1126 This procedure is responsible for validation of Treasury account symbol (TAS) in Payment record .
1127
1128 TAS should me of minimum 7 characters and can only be "0-9", "A-Z", ".", "(", ")", or "/"'.
1129
1130 */
1131
1132
1133 PROCEDURE TAS_VALIDATION
1134 (
1135 p_format_name IN VARCHAR2,
1136 p_treasury_symbol IN VARCHAR2,
1137 p_docErrorTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.docErrorTabType,
1138 p_docErrorRec IN OUT NOCOPY IBY_TRANSACTION_ERRORS%ROWTYPE,
1139 x_error_code OUT NOCOPY NUMBER,
1140 x_error_mesg OUT NOCOPY VARCHAR2
1141 )IS
1142
1143 l_length NUMBER;
1144 l_string VARCHAR2(100);
1145 l_char_string VARCHAR2(50);
1146 l_ans NUMBER;
1147 l_message VARCHAR2(1000);
1148 l_module_name VARCHAR2(200) := g_module_name || 'TAS_VALIDATION';
1149
1150
1151 BEGIN
1152 x_error_code := g_SUCCESS;
1153 l_string := UPPER(p_treasury_symbol);
1154 l_length := LENGTH(l_string);
1155
1156 l_message := 'TAS Validation, Parameters: p_format_name = ' || p_format_name || ', p_treasury_symbol = ' || p_treasury_symbol ;
1157 log_error_messages(FND_LOG.LEVEL_STATEMENT, l_module_name, l_message);
1158
1159
1160 IF l_length < 4 THEN
1161 x_error_mesg := 'The Treasury Symbol ' || p_treasury_symbol || ' must contain a minimum of 4 characters';
1162 p_docErrorRec.transaction_error_id := null;
1163 p_docErrorRec.error_code := 'TREASURY_SYMBOL_LENGTH';
1164 p_docErrorRec.error_message := x_error_mesg;
1165 x_error_code := g_ERROR;
1166 IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(p_docErrorRec, p_docErrorTab);
1167 log_error_messages(FND_LOG.LEVEL_STATEMENT, l_module_name, x_error_mesg);
1168 ELSE
1169 l_char_string := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890/.()';
1170
1171 FOR i in 1..l_length
1172 LOOP
1173 l_ans := INSTR(l_char_string,SUBSTR(l_string,i,1)) ;
1174 EXIT WHEN l_ans=0;
1175 END LOOP;
1176
1177 IF l_ans=0 THEN
1178 x_error_mesg := 'The Treasury Symbol ' || p_treasury_symbol || ' should only contain the following characters: "0-9", "A-Z", ".", "(", ")", or "/"';
1179 p_docErrorRec.transaction_error_id := null;
1180 p_docErrorRec.error_code := 'INVALID_TREASURY_SYMBOL';
1181 p_docErrorRec.error_message := x_error_mesg;
1182 x_error_code := g_ERROR;
1183 IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(p_docErrorRec, p_docErrorTab);
1184 log_error_messages(FND_LOG.LEVEL_STATEMENT, l_module_name, x_error_mesg);
1185 END IF;
1186
1187
1188 END IF;
1189
1190 EXCEPTION
1191 WHEN OTHERS THEN
1192 x_error_code := g_FAILURE;
1193 x_error_mesg := SQLERRM;
1194 log_error_messages(FND_LOG.LEVEL_STATEMENT, l_module_name, x_error_mesg);
1195 p_docErrorRec.transaction_error_id := null;
1196 p_docErrorRec.error_code := 'UNEXPECTED_ERROR';
1197 p_docErrorRec.error_message := x_error_mesg;
1198 IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(p_docErrorRec, p_docErrorTab);
1199 IBY_VALIDATIONSETS_PUB.insert_transaction_errors('N',p_docErrorTab);
1200
1201 END TAS_VALIDATION;
1202
1203 -------------------------------------------------------------------------------------------------------------------
1204 /*
1205
1206 PROCEDURE : AGENCY_LOCATION_CODE
1207
1208 This procedure is responisble for Validation of Agency Location Code.
1209
1210 Agency Location Code should not be NULL.
1211
1212 */
1213
1214
1215 PROCEDURE AGENCY_LOCATION_CODE
1216 (
1217 p_format_name IN VARCHAR2,
1218 p_agency_location_code IN ce_bank_accounts.agency_location_code%TYPE,
1219 p_bank_account_name IN ce_bank_accounts.bank_account_name%TYPE,
1220 p_docErrorTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.docErrorTabType,
1221 p_docErrorRec IN OUT NOCOPY IBY_TRANSACTION_ERRORS%ROWTYPE,
1222 x_error_code OUT NOCOPY NUMBER,
1223 x_error_mesg OUT NOCOPY VARCHAR2
1224 )IS
1225
1226 l_message VARCHAR2(1000);
1227 l_module_name VARCHAR2(200) := g_module_name || 'AGENCY_LOCATION_CODE';
1228
1229 BEGIN
1230 x_error_code := g_SUCCESS;
1231
1232 l_message := 'Validating Agency Location Code, Parameters: p_format_name = ' || p_format_name || ', p_agency_location_code = ' || p_agency_location_code ;
1233 log_error_messages(FND_LOG.LEVEL_STATEMENT, l_module_name, l_message);
1234
1235 IF p_agency_location_code IS NULL THEN -- Agency Location Code Should Not Be Empty.
1236 x_error_mesg := 'Agency Location Code captured in Bank Account Details window, is not defined for Bank Account ' || p_bank_account_name;
1237 x_error_mesg := x_error_mesg || '. Please correct the error, terminate this request and submit a new request';
1238 p_docErrorRec.transaction_error_id := null;
1239 p_docErrorRec.error_code := 'NO_AGENCY_LOCATION_CODE';
1240 p_docErrorRec.error_message := x_error_mesg;
1241 x_error_code := g_ERROR;
1242 IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(p_docErrorRec, p_docErrorTab);
1243 log_error_messages(FND_LOG.LEVEL_STATEMENT, l_module_name, x_error_mesg);
1244 END IF;
1245
1246
1247 EXCEPTION
1248 WHEN OTHERS THEN
1249 x_error_code := g_FAILURE;
1250 x_error_mesg := SQLERRM;
1251 log_error_messages(FND_LOG.LEVEL_STATEMENT, l_module_name, x_error_mesg);
1252 p_docErrorRec.transaction_error_id := null;
1253 p_docErrorRec.error_code := 'UNEXPECTED_ERROR';
1254 p_docErrorRec.error_message := x_error_mesg;
1255 IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(p_docErrorRec, p_docErrorTab);
1256 IBY_VALIDATIONSETS_PUB.insert_transaction_errors('N',p_docErrorTab);
1257
1258 END AGENCY_LOCATION_CODE;
1259
1260 -------------------------------------------------------------------------------------------------------------------
1261 /*
1262
1263 PROCEDURE : RTN_NUMBER
1264
1265 This procedure is responsible for validation of RTN Number.
1266
1267 It must a nine-digit numeric-only field. Prohibit fewer or more than nine characters, allow for only numeric characters,
1268 and prohibit the entry of all zeroes in this field. The ninth digit is the Check Digit which is validated using the Modulus formula.
1269
1270 */
1271
1272
1273 PROCEDURE RTN_NUMBER
1274 (
1275 p_format_name IN VARCHAR2,
1276 p_rtn_number IN VARCHAR2,
1277 p_docErrorTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.docErrorTabType,
1278 p_docErrorRec IN OUT NOCOPY IBY_TRANSACTION_ERRORS%ROWTYPE,
1279 x_error_code OUT NOCOPY NUMBER,
1280 x_error_mesg OUT NOCOPY VARCHAR2
1281 )IS
1282
1283 l_counter NUMBER;
1284 l_digit NUMBER;
1285 l_sub_total NUMBER;
1286 l_total NUMBER;
1287 l_last_digit NUMBER;
1288 l_correct_cdg NUMBER;
1289 l_target_cdg NUMBER;
1290 l_message VARCHAR2(1000);
1291 l_module_name VARCHAR2(200) := g_module_name || 'RTN_NUMBER';
1292 l_length NUMBER;
1293 l_char_string VARCHAR2(50);
1294 l_ans NUMBER;
1295
1296
1297 BEGIN
1298 x_error_code := g_SUCCESS;
1299
1300 l_message := 'Validating RTN Number, Parameters: p_format_name = ' || p_format_name || ', p_rtn_number = ' || p_rtn_number ;
1301 log_error_messages(FND_LOG.LEVEL_STATEMENT, l_module_name, l_message);
1302
1303
1304 if (p_rtn_number is null) then
1305 x_error_mesg := 'Invalid Routing Number';
1306 x_error_mesg := x_error_mesg || '. Please correct the error, terminate this request and submit a new request';
1307 p_docErrorRec.transaction_error_id := null;
1308 p_docErrorRec.error_code := 'INVALID_RTN';
1309 p_docErrorRec.error_message := x_error_mesg;
1310 x_error_code := g_ERROR;
1311 IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(p_docErrorRec, p_docErrorTab);
1312 log_error_messages(FND_LOG.LEVEL_STATEMENT, l_module_name, x_error_mesg);
1313 RETURN;
1314 end if;
1315
1316
1317
1318 l_sub_total := 0;
1319 l_total := 0;
1320 l_counter := 1;
1321 l_length := LENGTH(p_rtn_number);
1322
1323
1324 /*
1325 IF ((INSTR(TO_CHAR(TO_NUMBER(p_rtn_number)),'.')=0)
1326 AND
1327 (LENGTH(p_rtn_number)=9)
1328 AND
1329 (TO_NUMBER(p_rtn_number)>0)
1330 AND
1331 (INSTR(p_rtn_number,'.') = 0)) THEN
1332 */
1333 -- The above code is causing problems, you need to just check if that each digit is a number between 0 and 9
1334 -- replace with the following code
1335
1336 l_char_string := '0123456789';
1337
1338 FOR i in 1..l_length
1339 LOOP
1340 l_ans := INSTR(l_char_string,SUBSTR(p_rtn_number,i,1)) ;
1341 EXIT WHEN l_ans=0;
1342 END LOOP;
1343
1344
1345 IF ((l_length = 9) AND (l_ans <> 0)) THEN
1346 -- the value is 9 digit number value only now verify the check digit,
1347 -- which is the 9th digit.
1348
1349 FOR l_counter IN 1..8
1350 LOOP
1351 l_digit := SUBSTR(p_rtn_number, l_counter,1);
1352
1353 IF l_counter IN (1,4,7) THEN
1354 l_sub_total := TO_NUMBER(l_digit) * 3;
1355 ELSIF l_counter IN (2,5,8) THEN
1356 l_sub_total := TO_NUMBER(l_digit) * 7;
1357 ELSIF l_counter IN (3,6) THEN
1358 l_sub_total := TO_NUMBER(l_digit) * 1;
1359 END IF;
1360
1361 l_total := l_total + l_sub_total;
1362 l_sub_total := 0;
1363 END LOOP;
1364
1365 l_last_digit := TO_NUMBER(SUBSTR(TO_CHAR(l_total),LENGTH(TO_CHAR(l_total))));
1366
1367 l_correct_cdg := 10 - l_last_digit;
1368
1369 IF l_correct_cdg = 10 THEN
1370 l_correct_cdg := 0;
1371 END IF;
1372
1373 l_target_cdg := TO_NUMBER(SUBSTR(p_rtn_number,9));
1374
1375 IF l_correct_cdg = l_target_cdg THEN
1376 x_error_code := g_SUCCESS;
1377 ELSE
1378 x_error_mesg := 'Invalid Routing Number';
1379 p_docErrorRec.transaction_error_id := null;
1380 p_docErrorRec.error_code := 'INVALID_RTN';
1381 p_docErrorRec.error_message := x_error_mesg;
1382 x_error_code := g_ERROR;
1383 IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(p_docErrorRec, p_docErrorTab);
1384 log_error_messages(FND_LOG.LEVEL_STATEMENT, l_module_name, x_error_mesg);
1385 END IF;
1386
1387 ELSE
1388 -- failed, not a 9 digit numeric value
1389 x_error_mesg := 'Invalid Routing Number';
1390 p_docErrorRec.transaction_error_id := null;
1391 p_docErrorRec.error_code := 'INVALID_RTN';
1392 p_docErrorRec.error_message := x_error_mesg;
1393 x_error_code := g_ERROR;
1394 IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(p_docErrorRec, p_docErrorTab);
1395 log_error_messages(FND_LOG.LEVEL_STATEMENT, l_module_name, x_error_mesg);
1396 END IF;
1397
1398
1399 EXCEPTION
1400 WHEN OTHERS THEN
1401 x_error_code := g_FAILURE;
1402 x_error_mesg := SQLERRM;
1403 log_error_messages(FND_LOG.LEVEL_STATEMENT, l_module_name, x_error_mesg);
1404 p_docErrorRec.transaction_error_id := null;
1405 p_docErrorRec.error_code := 'UNEXPECTED_ERROR';
1406 p_docErrorRec.error_message := x_error_mesg;
1407 IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(p_docErrorRec, p_docErrorTab);
1408 IBY_VALIDATIONSETS_PUB.insert_transaction_errors('N',p_docErrorTab);
1409
1410 END RTN_NUMBER;
1411
1412 -------------------------------------------------------------------------------------------------------------------
1413 /*
1414
1415 PROCEDURE : PAY_ALONE_OPTION
1416
1417 This procedure is responsible for validation of Pay ALone.
1418
1419 "Pay Alone" for each invoice should not be 'NO' or NULL.
1420
1421
1422 */
1423
1424
1425 PROCEDURE PAY_ALONE_OPTION
1426 (
1427 p_format_name IN VARCHAR2,
1428 p_invoice_id IN NUMBER,
1429 p_payment_id IN NUMBER,
1430 p_docErrorTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.docErrorTabType,
1431 p_docErrorRec IN OUT NOCOPY IBY_TRANSACTION_ERRORS%ROWTYPE,
1432 x_error_code OUT NOCOPY NUMBER,
1433 x_error_mesg OUT NOCOPY VARCHAR2
1434 )IS
1435
1436 -- Bug : 11740157 : Validate only active documents.
1437 CURSOR get_pay_alone_flag_csr(p_invoice_id NUMBER, p_payment_id NUMBER)
1438 IS
1439 SELECT exclusive_payment_flag,
1440 calling_app_doc_ref_number
1441 FROM IBY_DOCS_PAYABLE_ALL
1442 WHERE calling_app_doc_unique_ref2 = p_invoice_id
1443 AND document_status = 'PAYMENT_CREATED'
1444 AND payment_id = p_payment_id
1445 AND calling_app_id=200
1446 AND UPPER(payment_function) IN ('PAYABLES_DISB','EMPLOYEE_EXP');
1447
1448 l_pay_alone_flag VARCHAR2(1);
1449 l_invoice_num ap_invoices_all.invoice_num%TYPE;
1450 l_message VARCHAR2(1000);
1451 l_module_name VARCHAR2(200) := g_module_name || 'PAY_ALONE_OPTION';
1452
1453 BEGIN
1454 x_error_code := g_SUCCESS;
1455
1456 l_message := 'Validating Pay Alone Option, Parameters: p_format_name = ' || p_format_name || ', p_invoice_id = ' || p_invoice_id ;
1457 log_error_messages(FND_LOG.LEVEL_STATEMENT, l_module_name, l_message);
1458
1459 OPEN get_pay_alone_flag_csr(p_invoice_id, p_payment_id);
1460 FETCH get_pay_alone_flag_csr into l_pay_alone_flag, l_invoice_num; -- Getting Pay ALone Flag From AP_INVOICES_ALL using invoice_id
1461 CLOSE get_pay_alone_flag_csr;
1462
1463 IF (l_pay_alone_flag IS NULL ) or ( UPPER(l_pay_alone_flag) ='N') THEN
1464 x_error_mesg := 'Invoices for this payment format must be have the Pay Alone flag checked on the invoice ' || l_invoice_num || '.';
1465 p_docErrorRec.transaction_error_id := null;
1466 p_docErrorRec.error_code := 'INVALID_PAY_ALONE_FLAG';
1467 p_docErrorRec.error_message := x_error_mesg;
1468 x_error_code := g_ERROR;
1469 IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(p_docErrorRec, p_docErrorTab);
1470 log_error_messages(FND_LOG.LEVEL_STATEMENT, l_module_name, x_error_mesg);
1471 END IF;
1472
1473 EXCEPTION
1474 WHEN OTHERS THEN
1475 x_error_code := g_FAILURE;
1476 x_error_mesg := SQLERRM;
1477 log_error_messages(FND_LOG.LEVEL_STATEMENT, l_module_name, x_error_mesg);
1478 p_docErrorRec.transaction_error_id := null;
1479 p_docErrorRec.error_code := 'UNEXPECTED_ERROR';
1480 p_docErrorRec.error_message := x_error_mesg;
1481 IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(p_docErrorRec, p_docErrorTab);
1482 IBY_VALIDATIONSETS_PUB.insert_transaction_errors('N',p_docErrorTab);
1483
1484 END PAY_ALONE_OPTION;
1485
1486 -------------------------------------------------------------------------------------------------------------------
1487 /*
1488
1489 PROCEDURE : DEPOSITER_ACC_NUM
1490
1491 This procedure is responsible for Validation of Depositor Account number.
1492
1493 Account number should not be NULL.
1494
1495 */
1496
1497 PROCEDURE DEPOSITER_ACC_NUM
1498 (
1499 p_format_name IN VARCHAR2,
1500 p_dep_account_no IN iby_ext_bank_accounts.bank_account_num%TYPE,
1501 p_docErrorTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.docErrorTabType,
1502 p_docErrorRec IN OUT NOCOPY IBY_TRANSACTION_ERRORS%ROWTYPE,
1503 x_error_code OUT NOCOPY NUMBER,
1504 x_error_mesg OUT NOCOPY VARCHAR2
1505 )IS
1506
1507 l_message VARCHAR2(1000);
1508 l_module_name VARCHAR2(200) := g_module_name || 'DEPOSITER_ACC_NUM';
1509 l_char_string VARCHAR2(50);
1510 l_length NUMBER;
1511 l_ans NUMBER;
1512 l_string VARCHAR2(100);
1513
1514 BEGIN
1515 x_error_code := g_SUCCESS;
1516
1517 l_message := 'Validating Depositor Account Number, Parameters: p_format_name = ' || p_format_name || ', p_dep_account_no = ' || p_dep_account_no ;
1518 log_error_messages(FND_LOG.LEVEL_STATEMENT, l_module_name, l_message);
1519
1520 IF p_dep_account_no IS NULL THEN --Account Number Should Not Be Empty
1521 x_error_mesg := 'Bank account number missing for vendor';
1522 x_error_mesg := x_error_mesg || '. Please correct the error, terminate this request and submit a new request';
1523 p_docErrorRec.transaction_error_id := null;
1524 p_docErrorRec.error_code := 'DEPOSITER_ACCOUNT_NO_MISSING';
1525 p_docErrorRec.error_message := x_error_mesg;
1526 x_error_code := g_ERROR;
1527 IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(p_docErrorRec, p_docErrorTab);
1528 log_error_messages(FND_LOG.LEVEL_STATEMENT, l_module_name, x_error_mesg);
1529 ELSE
1530 l_string := rtrim(p_dep_account_no);
1531 l_length := length(l_string);
1532 l_char_string := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890-';
1533
1534 FOR i in 1..l_length
1535 LOOP
1536 l_ans := INSTR(l_char_string,SUBSTR(l_string,i,1)) ;
1537 EXIT WHEN l_ans=0;
1538 END LOOP;
1539
1540 IF ((l_ans=0) OR (l_length > 17)) THEN
1541 x_error_mesg := 'The Depositor Account Number ' || p_dep_account_no || ' should be less or equal to 17 characters and should only contain the following characters: "0-9", "A-Z" or "-"';
1542 p_docErrorRec.transaction_error_id := null;
1543 p_docErrorRec.error_code := 'INVALID_DEPOSITER_ACCOUNT_NO';
1544 p_docErrorRec.error_message := x_error_mesg;
1545 x_error_code := g_ERROR;
1546 IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(p_docErrorRec, p_docErrorTab);
1547 log_error_messages(FND_LOG.LEVEL_STATEMENT, l_module_name, x_error_mesg);
1548 END IF;
1549
1550 END IF;
1551
1552 EXCEPTION
1553 WHEN OTHERS THEN
1554 x_error_code := g_FAILURE;
1555 x_error_mesg := SQLERRM;
1556 log_error_messages(FND_LOG.LEVEL_STATEMENT, l_module_name, x_error_mesg);
1557 p_docErrorRec.transaction_error_id := null;
1558 p_docErrorRec.error_code := 'UNEXPECTED_ERROR';
1559 p_docErrorRec.error_message := x_error_mesg;
1560 IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(p_docErrorRec, p_docErrorTab);
1561 IBY_VALIDATIONSETS_PUB.insert_transaction_errors('N',p_docErrorTab);
1562
1563
1564 END DEPOSITER_ACC_NUM;
1565
1566 -------------------------------------------------------------------------------------------------------------------
1567 /*
1568
1569 PROCEDURE : ACCOUNT_TYPE
1570
1571 This procedure is responsible for Validation of Account type.
1572
1573 Valid values for bank account type are "CHECKING" - Checking account; "SAVING" - Saving account.
1574
1575 */
1576
1577
1578 PROCEDURE ACCOUNT_TYPE
1579 (
1580 p_format_name IN VARCHAR2,
1581 p_bank_account_type IN iby_ext_bank_accounts.bank_account_type%TYPE,
1582 p_bank_account_name IN iby_ext_bank_accounts.bank_account_name%TYPE,
1583 p_invoice_id IN NUMBER,
1584 p_payment_id IN NUMBER,
1585 p_docErrorTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.docErrorTabType,
1586 p_docErrorRec IN OUT NOCOPY IBY_TRANSACTION_ERRORS%ROWTYPE,
1587 x_error_code OUT NOCOPY NUMBER,
1588 x_error_mesg OUT NOCOPY VARCHAR2
1589 )IS
1590
1591 l_message VARCHAR2(1000);
1592 l_module_name VARCHAR2(200) := g_module_name || 'ACCOUNT_TYPE';
1593 l_invoice_num ap_invoices_all.invoice_num%TYPE;
1594
1595 BEGIN
1596
1597 x_error_code := g_SUCCESS;
1598
1599 l_message := 'Validating Account Type, Parameters: p_format_name = ' || p_format_name || ', p_bank_account_type = ' || p_bank_account_type ;
1600 log_error_messages(FND_LOG.LEVEL_STATEMENT, l_module_name, l_message);
1601
1602 SELECT calling_app_doc_ref_number
1603 INTO l_invoice_num FROM IBY_DOCS_PAYABLE_ALL
1604 WHERE calling_app_doc_unique_ref2 = p_invoice_id
1605 AND payment_id = p_payment_id
1606 AND calling_app_id=200
1607 AND UPPER(payment_function) IN ('PAYABLES_DISB','EMPLOYEE_EXP');
1608
1609 -- Valid values for bank account type are "CHECKINGS" - Checking account; "SAVING" - Saving account.
1610 IF (p_bank_account_type IS NULL ) OR UPPER(p_bank_account_type) NOT IN ('CHECKING','SAVING') THEN
1611 x_error_mesg := 'For the invoice ' || l_invoice_num || ', the payee bank account ' || p_bank_account_name || ' must have a bank account type of either ''Checking'' or "Saving''';
1612 x_error_mesg := x_error_mesg || '. Please correct the error, terminate this request and submit a new request';
1613 p_docErrorRec.transaction_error_id := null;
1614 p_docErrorRec.error_code := 'INVALID_BANK_ACCOUNT_TYPE';
1615 p_docErrorRec.error_message := x_error_mesg;
1616 x_error_code := g_ERROR;
1617 IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(p_docErrorRec, p_docErrorTab);
1618 log_error_messages(FND_LOG.LEVEL_STATEMENT, l_module_name, x_error_mesg);
1619 END IF;
1620
1621
1622 EXCEPTION
1623 WHEN OTHERS THEN
1624 x_error_code := g_FAILURE;
1625 x_error_mesg := SQLERRM;
1626 log_error_messages(FND_LOG.LEVEL_STATEMENT, l_module_name, x_error_mesg);
1627 p_docErrorRec.transaction_error_id := null;
1628 p_docErrorRec.error_code := 'UNEXPECTED_ERROR';
1629 p_docErrorRec.error_message := x_error_mesg;
1630 IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(p_docErrorRec, p_docErrorTab);
1631 IBY_VALIDATIONSETS_PUB.insert_transaction_errors('N',p_docErrorTab);
1632
1633 END ACCOUNT_TYPE;
1634
1635 -------------------------------------------------------------------------------------------------------------------
1636 /*
1637
1638 PROCEDURE : RFC_ID
1639
1640 This procedure is responsible for validation of RFC_ID
1641
1642 RFC_ID should not be NULL.
1643
1644 */
1645
1646 PROCEDURE RFC_ID
1647 (
1648 p_format_name IN VARCHAR2,
1649 p_payment_id IN NUMBER,
1650 p_docErrorTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.docErrorTabType,
1651 p_docErrorRec IN OUT NOCOPY IBY_TRANSACTION_ERRORS%ROWTYPE,
1652 x_error_code OUT NOCOPY NUMBER,
1653 x_error_mesg OUT NOCOPY VARCHAR2
1654 )IS
1655
1656 l_message VARCHAR2(1000);
1657 l_module_name VARCHAR2(200) := g_module_name || 'RFC_ID';
1658 l_rfc_id iby_pay_instructions_all.rfc_identifier%TYPE;
1659 l_bank_account_id iby_pay_instructions_all.internal_bank_account_id%TYPE;
1660 l_bank_branch_id ce_bank_accounts.bank_branch_id%TYPE;
1661 l_bank_account_name ce_bank_accounts.bank_account_name%TYPE;
1662
1663
1664
1665
1666 BEGIN
1667 x_error_code := g_SUCCESS;
1668
1669 l_message := 'Validating RFC Id, Parameters: p_format_name = ' || p_format_name || ', p_payment_id = ' || p_payment_id;
1670 log_error_messages(FND_LOG.LEVEL_STATEMENT, l_module_name, l_message);
1671
1672 select hca.class_code,
1673 ipa.internal_bank_account_id,
1674 cba.bank_branch_id,
1675 cba.bank_account_name
1676 into l_rfc_id,
1677 l_bank_account_id,
1678 l_bank_branch_id,
1679 l_bank_account_name
1680 from iby_payments_all ipa,
1681 ce_bank_accounts cba,
1682 hz_code_assignments hca
1683 where ipa.payment_id = p_payment_id
1684 and ipa.internal_bank_account_id = cba.bank_account_id(+)
1685 and hca.owner_table_name(+) = 'HZ_PARTIES'
1686 and hca.class_category(+) = 'RFC_IDENTIFIER'
1687 and hca.owner_table_id(+) = cba.bank_branch_id;
1688
1689 IF l_rfc_id IS NULL THEN -- p_rfc_identifier should not be Empty
1690
1691 x_error_mesg := 'RFC Identifier is not defined on the bank branch for bank account ' || l_bank_account_name;
1692 l_message := x_error_mesg || ' l_bank_account_id = ' || l_bank_account_id || ', l_bank_branch_id = ' || l_bank_branch_id;
1693 p_docErrorRec.transaction_error_id := null;
1694 p_docErrorRec.error_code := 'INVALID_RFC_ID';
1695 p_docErrorRec.error_message := x_error_mesg;
1696 x_error_code := g_ERROR;
1697 IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(p_docErrorRec, p_docErrorTab);
1698 log_error_messages(FND_LOG.LEVEL_STATEMENT, l_module_name, l_message);
1699
1700 END IF;
1701
1702
1703 EXCEPTION
1704 WHEN OTHERS THEN
1705 x_error_code := g_FAILURE;
1706 x_error_mesg := SQLERRM;
1707 log_error_messages(FND_LOG.LEVEL_STATEMENT, l_module_name, x_error_mesg);
1708 p_docErrorRec.transaction_error_id := null;
1709 p_docErrorRec.error_code := 'UNEXPECTED_ERROR';
1710 p_docErrorRec.error_message := x_error_mesg;
1711 IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(p_docErrorRec, p_docErrorTab);
1712 IBY_VALIDATIONSETS_PUB.insert_transaction_errors('N',p_docErrorTab);
1713
1714 END RFC_ID;
1715
1716
1717
1718 -------------------------------------------------------------------------------------------------------------------
1719 ------------------------------------------------------------------------------------------------------------------
1720 /*
1721
1722 PROCEDURE : MANDATORY_PPD_PPDP_REASON_CODE
1723
1724 This procedure is responsible for validation of following formats
1725
1726 Bulk Data PPDP Payment Format Report 'FVBLPPDP'
1727
1728 ECS PPD Vendor Payment Format Program 'FVTPPPD'
1729
1730 ECS PPDP Vendor Payment Format Program 'FVTPPPDP'
1731
1732 SPS PPD Vendor Payment Format Program 'FVSPPPD'
1733
1734 SPS PPDP Vendor Payment Format Program 'FVSPPPDP'
1735
1736 to have a payment with a specified Federal payment reason.
1737
1738 */
1739 PROCEDURE MANDATORY_PPD_PPDP_REASON_CODE
1740 (
1741 p_format_name IN VARCHAR2,
1742 p_reason_code IN VARCHAR2,
1743 p_docErrorTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.docErrorTabType,
1744 p_docErrorRec IN OUT NOCOPY IBY_TRANSACTION_ERRORS%ROWTYPE,
1745 x_error_code OUT NOCOPY NUMBER,
1746 x_error_mesg OUT NOCOPY VARCHAR2
1747 )IS
1748
1749 l_message VARCHAR2(1000);
1750 l_module_name VARCHAR2(200) := g_module_name || 'MANDATORY_PPD_PPDP_REASON_CODE';
1751
1752 BEGIN
1753 x_error_code := g_SUCCESS;
1754
1755 l_message := 'Validating Reason Code Id, Parameters: p_format_name = ' || p_format_name || ', p_reason_code = ' || p_reason_code ;
1756 log_error_messages(FND_LOG.LEVEL_STATEMENT, l_module_name, l_message);
1757
1758
1759 IF (p_reason_code IS NULL) OR p_reason_code NOT IN('US_FV_A','US_FV_B','US_FV_C','US_FV_D',
1760 'US_FV_I','US_FV_M','US_FV_O','US_FV_R',
1761 'US_FV_S','US_FV_T','US_FV_X') THEN
1762 x_error_mesg := 'This payment format can must have a Federal payment reason defined for each payment. The following are valid payment reasons: Allotments,
1763 SSA Benefits, VA Benefits, VAINS, Miscellaneous PPD, OPM Benefits, RRB Benefits, Salary, Travel and Tax.';
1764 p_docErrorRec.transaction_error_id := null;
1765 p_docErrorRec.error_code := 'INVALID_PAYMENT_REASON';
1766 p_docErrorRec.error_message := x_error_mesg;
1767 x_error_code := g_ERROR;
1768 IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(p_docErrorRec, p_docErrorTab);
1769 log_error_messages(FND_LOG.LEVEL_STATEMENT, l_module_name, x_error_mesg);
1770
1771 END IF;
1772
1773 EXCEPTION
1774 WHEN OTHERS THEN
1775 x_error_code := g_FAILURE;
1776 x_error_mesg := SQLERRM;
1777 log_error_messages(FND_LOG.LEVEL_STATEMENT, l_module_name, x_error_mesg);
1778 p_docErrorRec.transaction_error_id := null;
1779 p_docErrorRec.error_code := 'UNEXPECTED_ERROR';
1780 p_docErrorRec.error_message := x_error_mesg;
1781 IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(p_docErrorRec, p_docErrorTab);
1782 IBY_VALIDATIONSETS_PUB.insert_transaction_errors('N',p_docErrorTab);
1783
1784 END MANDATORY_PPD_PPDP_REASON_CODE;
1785 -----------------------------------------------------------------------------------------------------------------
1786 /*
1787
1788 PROCEDURE : EXTERNAL_BANK_ACCOUNT_ID
1789
1790 This procedure is responsible for validation of EXTERNAL_BANK_ACCOUNT_ID
1791
1792 EXTERNAL_BANK_ACCOUNT_ID should not be NULL.
1793
1794 */
1795
1796
1797 PROCEDURE EXTERNAL_BANK_ACCOUNT_ID
1798 (
1799 p_format_name IN VARCHAR2,
1800 p_external_bank_account_id IN NUMBER,
1801 p_docErrorTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.docErrorTabType,
1802 p_docErrorRec IN OUT NOCOPY IBY_TRANSACTION_ERRORS%ROWTYPE,
1803 x_error_code OUT NOCOPY NUMBER,
1804 x_error_mesg OUT NOCOPY VARCHAR2
1805 )IS
1806
1807 l_message VARCHAR2(1000);
1808 l_module_name VARCHAR2(200) := g_module_name || 'EXTERNAL_BANK_ACCOUNT_ID';
1809
1810
1811
1812 BEGIN
1813 x_error_code := g_SUCCESS;
1814
1815 l_message := 'Validating External Bank Account Id, Parameters: p_format_name = ' || p_format_name || ', p_external_bank_account_id = ' || p_external_bank_account_id;
1816 log_error_messages(FND_LOG.LEVEL_STATEMENT, l_module_name, l_message);
1817
1818 IF p_external_bank_account_id IS NULL THEN -- p_external_bank_account_id should not be Empty
1819
1820 x_error_mesg := 'This is an electronic format and requires a remit-to bank account to be entered on the invoice.';
1821 x_error_mesg := x_error_mesg || '. Please correct the error, terminate this request and submit a new request';
1822 p_docErrorRec.transaction_error_id := null;
1823 p_docErrorRec.error_code := 'INVALID_EXTERNAL_BANK_ACCOUNT';
1824 p_docErrorRec.error_message := x_error_mesg;
1825 x_error_code := g_ERROR;
1826 IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(p_docErrorRec, p_docErrorTab);
1827 log_error_messages(FND_LOG.LEVEL_STATEMENT, l_module_name, x_error_mesg);
1828
1829 END IF;
1830
1831 EXCEPTION
1832 WHEN OTHERS THEN
1833 x_error_code := g_FAILURE;
1834 x_error_mesg := SQLERRM;
1835 log_error_messages(FND_LOG.LEVEL_STATEMENT, l_module_name, x_error_mesg);
1836 p_docErrorRec.transaction_error_id := null;
1837 p_docErrorRec.error_code := 'UNEXPECTED_ERROR';
1838 p_docErrorRec.error_message := x_error_mesg;
1839 IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(p_docErrorRec, p_docErrorTab);
1840 IBY_VALIDATIONSETS_PUB.insert_transaction_errors('N',p_docErrorTab);
1841
1842 END EXTERNAL_BANK_ACCOUNT_ID;
1843 -----------------------------------------------------------------------------------------------------------------
1844 PROCEDURE FEDERAL_ID_NUMBER
1845 (
1846 p_format_name IN VARCHAR2,
1847 p_pay_instruction_id IN NUMBER,
1848 p_docErrorTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.docErrorTabType,
1849 p_docErrorRec IN OUT NOCOPY IBY_TRANSACTION_ERRORS%ROWTYPE,
1850 x_error_code OUT NOCOPY NUMBER,
1851 x_error_mesg OUT NOCOPY VARCHAR2
1852 )IS
1853 l_message VARCHAR2(1000);
1854 l_module_name VARCHAR2(200) := g_module_name || 'FEDERAL_ID_NUMBER';
1855 l_org_id number;
1856 l_org_name hr_all_organization_units.name%TYPE;
1857 l_fed_employer_id_number fv_operating_units_all.fed_employer_id_number%TYPE;
1858
1859 BEGIN
1860 x_error_code := g_SUCCESS;
1861
1862 IF (p_format_name IN ('FVBLCCDP','FVBLPPDP')) THEN
1863 l_message := 'Validating Federal Id Number Id, Parameters: p_format_name = ' || p_format_name;
1864 log_error_messages(FND_LOG.LEVEL_STATEMENT, l_module_name, l_message);
1865
1866 select ipia.org_id, haou.name
1867 into l_org_id, l_org_name
1868 from iby_pay_instructions_all ipia,
1869 hr_all_organization_units haou
1870 where ipia.payment_instruction_id = p_pay_instruction_id
1871 and ipia.org_id = haou.organization_id;
1872
1873
1874 select fed_employer_id_number
1875 into l_fed_employer_id_number
1876 from fv_operating_units_all
1877 where org_id = l_org_id;
1878
1879 IF l_fed_employer_id_number IS NULL THEN -- l_fed_employer_id_number should not be null
1880
1881 x_error_mesg := 'The FederalEmployer ID Number(FEIN) must be defined on the Define Federal Options ' ||
1882 'window in Federal Administrator for the operating unit ' || l_org_name;
1883 p_docErrorRec.transaction_error_id := null;
1884 p_docErrorRec.error_code := 'INVALID_FEDERAL_ID_NUMBER';
1885 p_docErrorRec.error_message := x_error_mesg;
1886 x_error_code := g_ERROR;
1887 IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(p_docErrorRec, p_docErrorTab);
1888 log_error_messages(FND_LOG.LEVEL_STATEMENT, l_module_name, x_error_mesg);
1889
1890 END IF;
1891 END IF;
1892 EXCEPTION
1893 WHEN NO_DATA_FOUND THEN
1894 x_error_mesg := 'The FederalEmployer ID Number(FEIN) must be defined on the Define Federal Options ' ||
1895 'window in Federal Administrator for the operating unit ' || l_org_name;
1896 p_docErrorRec.transaction_error_id := null;
1897 p_docErrorRec.error_code := 'INVALID_FEDERAL_ID_NUMBER';
1898 p_docErrorRec.error_message := x_error_mesg;
1899 x_error_code := g_ERROR;
1900 IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(p_docErrorRec, p_docErrorTab);
1901 log_error_messages(FND_LOG.LEVEL_STATEMENT, l_module_name, x_error_mesg);
1902
1903 WHEN OTHERS THEN
1904 x_error_code := g_FAILURE;
1905 x_error_mesg := SQLERRM;
1906 log_error_messages(FND_LOG.LEVEL_STATEMENT, l_module_name, x_error_mesg);
1907 p_docErrorRec.transaction_error_id := null;
1908 p_docErrorRec.error_code := 'UNEXPECTED_ERROR';
1909 p_docErrorRec.error_message := x_error_mesg;
1910 IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(p_docErrorRec, p_docErrorTab);
1911 IBY_VALIDATIONSETS_PUB.insert_transaction_errors('N',p_docErrorTab);
1912
1913 END FEDERAL_ID_NUMBER;
1914
1915 ------------------------------------------------------------------------------------------------------------------
1916 PROCEDURE AGENCY_ID_ABBREVIATION
1917 (
1918 p_format_name IN VARCHAR2,
1919 p_instruction_id IN NUMBER,
1920 p_docErrorTab IN OUT NOCOPY IBY_VALIDATIONSETS_PUB.docErrorTabType,
1921 p_docErrorRec IN OUT NOCOPY IBY_TRANSACTION_ERRORS%ROWTYPE,
1922 x_error_code OUT NOCOPY NUMBER,
1923 x_error_mesg OUT NOCOPY VARCHAR2
1924 )IS
1925
1926 l_message VARCHAR2(1000);
1927 l_module_name VARCHAR2(200) := g_module_name || 'AGENCY_ID_ABBREVIATION';
1928
1929 BEGIN
1930 x_error_code := g_SUCCESS;
1931
1932 l_message := 'Validating Agency Id Abbreviation, Parameters: p_format_name = ' || p_format_name || ', p_instruction_id = ' || p_instruction_id ;
1933 log_error_messages(FND_LOG.LEVEL_STATEMENT, l_module_name, l_message);
1934
1935
1936 IF p_format_name IN ('FVBLNCR','FVBLSLTR','FVBLCCDP','FVBLPPDP','FVTICTX') THEN
1937
1938 IF FND_PROFILE.VALUE('FV_AGENCY_ID_ABBREVIATION') IS NULL THEN
1939
1940 x_error_mesg := 'Profile FV:FV_AGENCY_ID_ABBREVIATION must be defined.';
1941 p_docErrorRec.transaction_error_id := null;
1942 p_docErrorRec.error_code := 'AGENCY_ABBREV_UNDEFINED';
1943 p_docErrorRec.error_message := x_error_mesg;
1944 x_error_code := g_ERROR;
1945 IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(p_docErrorRec, p_docErrorTab);
1946 log_error_messages(FND_LOG.LEVEL_STATEMENT, l_module_name, x_error_mesg);
1947
1948 END IF;
1949
1950 END IF;
1951
1952
1953 EXCEPTION
1954 WHEN OTHERS THEN
1955 x_error_code := g_FAILURE;
1956 x_error_mesg := SQLERRM;
1957 log_error_messages(FND_LOG.LEVEL_STATEMENT, l_module_name, x_error_mesg);
1958 p_docErrorRec.transaction_error_id := null;
1959 p_docErrorRec.error_code := 'UNEXPECTED_ERROR';
1960 p_docErrorRec.error_message := x_error_mesg;
1961 IBY_VALIDATIONSETS_PUB.insertIntoErrorTable(p_docErrorRec, p_docErrorTab);
1962 IBY_VALIDATIONSETS_PUB.insert_transaction_errors('N',p_docErrorTab);
1963
1964 END AGENCY_ID_ABBREVIATION;
1965
1966
1967
1968
1969 -------------------------------------------------------------------------------------------------------------------
1970 /*
1971 * Write immediate validation messages to the common
1972 * application logs. Write deferred validation messages
1973 * to the concurrent manager log file.
1974 *
1975 * If FND_GLOBAL.conc_request_id is -1, it implies that
1976 * this method has not been invoked via the concurrent
1977 * manager (online validation case; write to apps log).
1978 */
1979
1980 PROCEDURE LOG_ERROR_MESSAGES
1981 (
1982 p_level IN NUMBER,
1983 p_module IN VARCHAR2,
1984 p_message IN VARCHAR2
1985 ) IS
1986
1987 BEGIN
1988
1989 IF (p_level >= g_current_level) THEN
1990 fnd_log.string (p_level, p_module, p_message);
1991 END IF;
1992
1993 -- log messages only if concurrent program
1994 IF (FND_GLOBAL.conc_request_id <> -1) THEN
1995 FND_FILE.PUT_LINE(FND_FILE.LOG, p_module || ': ' || p_message);
1996 END IF;
1997
1998 END LOG_ERROR_MESSAGES;
1999
2000
2001 -----------------------------------------------------------------------------------------------------------------
2002 BEGIN
2003 g_module_name := 'fv.plsql.IBY_PAYMENT_FORMAT_VAL_PVT.';
2004 g_ERROR := -1;
2005 g_FAILURE := -2;
2006 g_SUCCESS := 0;
2007 g_current_level := fnd_log.g_current_runtime_level;
2008
2009
2010 END IBY_PAYMENT_FORMAT_VAL_PVT;