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