1 PACKAGE BODY ja_interface_val AS
2 /* $Header: jgjagdfb.pls 120.5 2005/07/17 18:25:08 ykonishi ship $ */
3 -----------------------------------------------------------------
4 -- Payables Business Rules --
5 -----------------------------------------------------------------
6 PROCEDURE ap_business_rules
7 (p_calling_program_name IN VARCHAR2,
8 p_set_of_books_id IN NUMBER,
9 p_invoice_date IN DATE,
10 p_parent_table IN VARCHAR2,
11 p_parent_id IN NUMBER,
12 p_default_last_updated_by IN NUMBER,
13 p_default_last_update_login IN NUMBER,
14 p_global_attribute_category IN VARCHAR2,
15 p_global_attribute1 IN VARCHAR2,
16 p_global_attribute2 IN VARCHAR2,
17 p_global_attribute3 IN VARCHAR2,
18 p_global_attribute4 IN VARCHAR2,
19 p_global_attribute5 IN VARCHAR2,
20 p_global_attribute6 IN VARCHAR2,
21 p_global_attribute7 IN VARCHAR2,
22 p_global_attribute8 IN VARCHAR2,
23 p_global_attribute9 IN VARCHAR2,
24 p_global_attribute10 IN VARCHAR2,
25 p_global_attribute11 IN VARCHAR2,
26 p_global_attribute12 IN VARCHAR2,
27 p_global_attribute13 IN VARCHAR2,
28 p_global_attribute14 IN VARCHAR2,
29 p_global_attribute15 IN VARCHAR2,
30 p_global_attribute16 IN VARCHAR2,
31 p_global_attribute17 IN VARCHAR2,
32 p_global_attribute18 IN VARCHAR2,
33 p_global_attribute19 IN VARCHAR2,
34 p_global_attribute20 IN VARCHAR2,
35 p_current_invoice_status OUT NOCOPY VARCHAR2,
36 p_calling_sequence IN VARCHAR2) IS
37
38 l_credit_exists VARCHAR2(1);
39 l_current_invoice_status VARCHAR2(1);
40 l_debug_loc VARCHAR2(100);
41 l_curr_calling_sequence VARCHAR2(2000);
42 l_debug_info VARCHAR2(100);
43 BEGIN
44 l_current_invoice_status := 'Y';
45 l_debug_loc := 'ap_business_rules';
46 -------------------------- DEBUG INFORMATION ------------------------------------------
47 l_curr_calling_sequence := 'ja_interface_val.'||l_debug_loc||'<-'||p_calling_sequence;
48 l_debug_info := 'Check Asia Pacific business rules';
49 ---------------------------------------------------------------------------------------
50
51 --------------------------------------------------------------------------------------
52 -- Global Flexfield Validation
53 --------------------------------------------------------------------------------------
54 -- You can add your own validation code for your global flexfields.
55 -- You should not include arguments(GLOBAL_ATTRIBUTE(n)) you do not validate
56 -- in your procedure.
57
58 -- Form Name: APXIISIM
59 --------------------------------------------------------------------------------------
60 -- Header Level Validation - Block Name: INVOICES_FOLDER
61 --------------------------------------------------------------------------------------
62 -- 1-1. JA.KR.APXIISIM.INVOICES_FOLDER
63 -- 1-2. JA.CN.APXIISIM.INVOICES_FOLDER
64 -- 1-3. JA.TH.APXIISIM.INVOICES_INTF
65 -- 1-4. JA.TW.APXIISIM.INVOICES_FOLDER
66 -- 1-5. JA.SG.APXIISIM.INVOICES_FOLDER
67 --------------------------------------------------------------------------------------
68 -- Line Level Validation - Block Name: INVOICE_LINES_FOLDER
69 --------------------------------------------------------------------------------------
70 -- 2-1. JA.KR.APXIISIM.LINES_FOLDER
71 -- 2-2. JA.CA.APXIISIM.LINES_FOLDER
72 --------------------------------------------------------------------------------------
73
74 --
75 -- 1-1. JA.KR.APXIISIM.INVOICES_FOLDER
76 --
77 IF (p_global_attribute_category = 'JA.KR.APXIISIM.INVOICES_FOLDER') THEN
78 ja_kr_apxiisim_invoices_folder(
79 p_parent_id,
80 p_default_last_updated_by,
81 p_default_last_update_login,
82 p_global_attribute13,
83 p_global_attribute14,
84 p_global_attribute15,
85 p_global_attribute16,
86 p_global_attribute17,
87 p_global_attribute18,
88 p_global_attribute19,
89 p_global_attribute20,
90 l_current_invoice_status,
91 p_calling_sequence);
92
93 --
94 -- 1-2. JA.CN.APXIISIM.INVOICES_FOLDER
95 --
96 ELSIF (p_global_attribute_category = 'JA.CN.APXIISIM.INVOICES_FOLDER') THEN
97 ja_cn_apxiisim_invoices_folder(
98 p_parent_id,
99 p_default_last_updated_by,
100 p_default_last_update_login,
101 p_global_attribute1,
102 p_global_attribute2,
103 p_global_attribute3,
104 p_global_attribute4,
105 p_global_attribute5,
106 p_global_attribute6,
107 p_global_attribute7,
108 p_global_attribute8,
109 p_global_attribute9,
110 p_global_attribute10,
111 p_global_attribute11,
112 p_global_attribute12,
113 p_global_attribute13,
114 p_global_attribute14,
115 p_global_attribute15,
116 p_global_attribute16,
117 p_global_attribute17,
118 p_global_attribute18,
119 p_global_attribute19,
120 p_global_attribute20,
121 l_current_invoice_status,
122 p_calling_sequence);
123
124 --
125 -- 1-3. JA.TH.APXIISIM.INVOICES_FOLDER
126 --
127 ELSIF (p_global_attribute_category = 'JA.TH.APXIISIM.INVOICES_INTF') THEN
128 ja_th_apxiisim_invoices_folder(
129 p_set_of_books_id,
130 p_parent_id,
131 p_default_last_updated_by,
132 p_default_last_update_login,
133 p_global_attribute1,
134 p_global_attribute2,
135 p_global_attribute3,
136 p_global_attribute4,
137 p_global_attribute5,
138 p_global_attribute6,
139 p_global_attribute7,
140 p_global_attribute8,
141 p_global_attribute9,
142 p_global_attribute10,
143 p_global_attribute11,
144 p_global_attribute12,
145 p_global_attribute13,
146 p_global_attribute14,
147 p_global_attribute15,
148 p_global_attribute16,
149 p_global_attribute17,
150 p_global_attribute18,
151 p_global_attribute19,
152 p_global_attribute20,
153 l_current_invoice_status,
154 p_calling_sequence);
155
156 --
157 -- 1-4. JA.TW.APXIISIM.INVOICES_FOLDER
158 --
159 ELSIF (p_global_attribute_category = 'JA.TW.APXIISIM.INVOICES_FOLDER') THEN
160 ja_tw_apxiisim_invoices_folder(
161 p_parent_id,
162 p_default_last_updated_by,
163 p_default_last_update_login,
164 p_global_attribute1,
165 p_global_attribute2,
166 p_global_attribute3,
167 p_global_attribute4,
168 p_global_attribute5,
169 p_global_attribute6,
170 p_global_attribute7,
171 p_global_attribute8,
172 p_global_attribute9,
173 p_global_attribute10,
174 p_global_attribute11,
175 p_global_attribute12,
176 p_global_attribute13,
177 p_global_attribute14,
178 p_global_attribute15,
179 p_global_attribute16,
180 p_global_attribute17,
181 p_global_attribute18,
182 p_global_attribute19,
183 p_global_attribute20,
184 l_current_invoice_status,
185 p_calling_sequence);
186
187 --
188 -- 1-5. JA.SG.APXIISIM.INVOICES_FOLDER
189 --
190 ELSIF (p_global_attribute_category = 'JA.SG.APXIISIM.INVOICES_FOLDER') THEN
191 ja_sg_apxiisim_invoices_folder(
192 p_parent_id,
193 p_default_last_updated_by,
194 p_default_last_update_login,
195 p_global_attribute1,
196 p_global_attribute2,
197 p_global_attribute3,
198 p_global_attribute4,
199 p_global_attribute5,
200 p_global_attribute6,
201 p_global_attribute7,
202 p_global_attribute8,
203 p_global_attribute9,
204 p_global_attribute10,
205 p_global_attribute11,
206 p_global_attribute12,
207 p_global_attribute13,
208 p_global_attribute14,
209 p_global_attribute15,
210 p_global_attribute16,
211 p_global_attribute17,
212 p_global_attribute18,
213 p_global_attribute19,
214 p_global_attribute20,
215 l_current_invoice_status,
216 p_calling_sequence);
217 --
218 -- 2-1. JA.KR.APXIISIM.LINES_FOLDER
219 --
220 ELSIF (p_global_attribute_category = 'JA.KR.APXIISIM.LINES_FOLDER') THEN
221 ja_kr_apxiisim_lines_folder(
222 p_parent_id,
223 p_default_last_updated_by,
224 p_default_last_update_login,
225 p_global_attribute1,
226 p_global_attribute2,
227 p_global_attribute9,
228 p_global_attribute10,
229 p_global_attribute11,
230 p_global_attribute12,
231 p_global_attribute13,
232 p_global_attribute14,
233 p_global_attribute15,
234 p_global_attribute16,
235 p_global_attribute17,
236 p_global_attribute18,
237 p_global_attribute19,
238 p_global_attribute20,
239 l_current_invoice_status,
240 p_calling_sequence);
241 --
242 -- 2-2. JA.CA.APXIISIM.LINES_FOLDER
243 --
244 ELSIF (p_global_attribute_category = 'JA.CA.APXIISIM.LINES_FOLDER') THEN
245 ja_ca_apxiisim_lines_folder (
246 p_set_of_books_id,
247 p_invoice_date,
248 p_parent_id,
249 p_default_last_updated_by,
250 p_default_last_update_login,
251 p_global_attribute1,
252 p_global_attribute4,
253 p_global_attribute5,
254 p_global_attribute6,
255 p_global_attribute7,
256 p_global_attribute8,
257 p_global_attribute9,
258 p_global_attribute10,
259 p_global_attribute11,
260 p_global_attribute12,
261 p_global_attribute13,
262 p_global_attribute14,
263 p_global_attribute15,
264 p_global_attribute16,
265 p_global_attribute17,
266 p_global_attribute18,
267 p_global_attribute19,
268 p_global_attribute20,
269 l_current_invoice_status,
270 p_calling_sequence);
271 END IF;
272
273 END ap_business_rules;
274
275 --------------------------------------------------------------------------------------
276 -- JA_KR_APXIISIM_INVOICES_FOLDER()
277 --------------------------------------------------------------------------------------
278 -- There is no global flexfield in R11i, so we don't provide any validation for
279 -- Invoice Gateway.
280 --------------------------------------------------------------------------------------
281 PROCEDURE ja_kr_apxiisim_invoices_folder(
282 p_parent_id IN NUMBER,
283 p_default_last_updated_by IN NUMBER,
284 p_default_last_update_login IN NUMBER,
285 p_global_attribute13 IN VARCHAR2,
286 p_global_attribute14 IN VARCHAR2,
287 p_global_attribute15 IN VARCHAR2,
288 p_global_attribute16 IN VARCHAR2,
289 p_global_attribute17 IN VARCHAR2,
290 p_global_attribute18 IN VARCHAR2,
291 p_global_attribute19 IN VARCHAR2,
292 p_global_attribute20 IN VARCHAR2,
293 p_current_invoice_status OUT NOCOPY VARCHAR2,
294 p_calling_sequence IN VARCHAR2) IS
295
296 BEGIN
297 null;
298 END ja_kr_apxiisim_invoices_folder;
299
300 --------------------------------------------------------------------------------------
301 -- JA_CN_APXIISIM_INVOICES_FOLDER()
302 --------------------------------------------------------------------------------------
303 -- There is no global flexfield in R11i, so we don't provide any validation for
304 -- Invoice Gateway.
305 --------------------------------------------------------------------------------------
306 PROCEDURE ja_cn_apxiisim_invoices_folder
307 (p_parent_id IN NUMBER,
308 p_default_last_updated_by IN NUMBER,
309 p_default_last_update_login IN NUMBER,
310 p_global_attribute1 IN VARCHAR2,
311 p_global_attribute2 IN VARCHAR2,
312 p_global_attribute3 IN VARCHAR2,
313 p_global_attribute4 IN VARCHAR2,
314 p_global_attribute5 IN VARCHAR2,
315 p_global_attribute6 IN VARCHAR2,
316 p_global_attribute7 IN VARCHAR2,
317 p_global_attribute8 IN VARCHAR2,
318 p_global_attribute9 IN VARCHAR2,
319 p_global_attribute10 IN VARCHAR2,
320 p_global_attribute11 IN VARCHAR2,
321 p_global_attribute12 IN VARCHAR2,
322 p_global_attribute13 IN VARCHAR2,
323 p_global_attribute14 IN VARCHAR2,
324 p_global_attribute15 IN VARCHAR2,
325 p_global_attribute16 IN VARCHAR2,
326 p_global_attribute17 IN VARCHAR2,
327 p_global_attribute18 IN VARCHAR2,
328 p_global_attribute19 IN VARCHAR2,
329 p_global_attribute20 IN VARCHAR2,
330 p_current_invoice_status OUT NOCOPY VARCHAR2,
331 p_calling_sequence IN VARCHAR2) IS
332
333 BEGIN
334 null;
335 END ja_cn_apxiisim_invoices_folder;
336
337 --------------------------------------------------------------------------------------
338 -- JA_TH_APXIISIM_INVOICES_FOLDER()
339 --------------------------------------------------------------------------------------
340 -- Following segments are defined for Thai Invoice Interface:
344 -- 1 Tax Invoice Number GLOBAL_ATTRIBUTE1 No
341 --------------------------------------------------------------------------------------
342 -- No. Name Column Value Set Required
343 -- --- -------------------------- ------------------ ----------------- ------------
345 -- 2 Tax Invoice Date GLOBAL_ATTRIBUTE2 FND_STANDARD_DATE No
346 -- 3 Supplier Tax Invoice Number GLOBAL_ATTRIBUTE3 No
347 -- 4 Tax Accounting Period GLOBAL_ATTRIBUTE4 JATH_AP_TAX_ACCT_PERIOD No
348 --------------------------------------------------------------------------------------
349 PROCEDURE ja_th_apxiisim_invoices_folder
350 (p_set_of_books_id IN NUMBER,
351 p_parent_id IN NUMBER,
352 p_default_last_updated_by IN NUMBER,
353 p_default_last_update_login IN NUMBER,
354 p_global_attribute1 IN VARCHAR2,
355 p_global_attribute2 IN VARCHAR2,
356 p_global_attribute3 IN VARCHAR2,
357 p_global_attribute4 IN VARCHAR2,
358 p_global_attribute5 IN VARCHAR2,
359 p_global_attribute6 IN VARCHAR2,
360 p_global_attribute7 IN VARCHAR2,
361 p_global_attribute8 IN VARCHAR2,
362 p_global_attribute9 IN VARCHAR2,
363 p_global_attribute10 IN VARCHAR2,
364 p_global_attribute11 IN VARCHAR2,
365 p_global_attribute12 IN VARCHAR2,
366 p_global_attribute13 IN VARCHAR2,
367 p_global_attribute14 IN VARCHAR2,
368 p_global_attribute15 IN VARCHAR2,
369 p_global_attribute16 IN VARCHAR2,
370 p_global_attribute17 IN VARCHAR2,
371 p_global_attribute18 IN VARCHAR2,
372 p_global_attribute19 IN VARCHAR2,
373 p_global_attribute20 IN VARCHAR2,
374 p_current_invoice_status OUT NOCOPY VARCHAR2,
375 p_calling_sequence IN VARCHAR2) IS
376
377 l_tax_acct_per_exists VARCHAR2(1);
378 l_tax_inv_date DATE;
379
380
381 BEGIN
382
383 IF (p_global_attribute2 IS NOT NULL) THEN
384
385 -- Check if Tax Invoice Date is of Standard Date Format
386
387 BEGIN
388 SELECT fnd_date.canonical_to_date(p_global_attribute2)
389 INTO l_tax_inv_date
390 FROM dual;
391 EXCEPTION
392 WHEN OTHERS THEN
393 jg_globe_flex_val_shared.insert_rejections('AP_INVOICES_INTERFACE',
394 p_parent_id,
395 'INVALID_GLOBAL_ATTR2',
396 p_default_last_updated_by,
397 p_default_last_update_login,
398 p_calling_sequence);
399 p_current_invoice_status := 'N';
400 END;
401
402 -- Supplier Tax Invoice Number and Tax Accounting Period fields are required
403 -- if Tax Invoice Date is entered
404
405 IF (p_global_attribute3 IS NULL) THEN
406 jg_globe_flex_val_shared.insert_rejections('AP_INVOICES_INTERFACE',
407 p_parent_id,
408 'INVALID_GLOBAL_ATTR3',
409 p_default_last_updated_by,
410 p_default_last_update_login,
411 p_calling_sequence);
412 p_current_invoice_status := 'N';
413 END IF;
414
415 IF (p_global_attribute4 IS NULL) THEN
416 jg_globe_flex_val_shared.insert_rejections('AP_INVOICES_INTERFACE',
417 p_parent_id,
418 'INVALID_GLOBAL_ATTR4',
419 p_default_last_updated_by,
420 p_default_last_update_login,
421 p_calling_sequence);
422 p_current_invoice_status := 'N';
423
424 END IF;
425
426 END IF;
427
428 IF (p_global_attribute2 is not null and p_global_attribute4 is not null) then
429
430 -- Check is Tax Accounting Period is an Open or Future Period
431
432 BEGIN
433 SELECT NULL
434 INTO l_tax_acct_per_exists
435 FROM dual
436 WHERE p_global_attribute4 in
437 (select period_name
438 from gl_period_statuses
439 where application_id=200
440 and set_of_books_id = p_set_of_books_id
441 and closing_status in ('O','F')
442 and adjustment_period_flag = 'N'
443 and end_date >= fnd_date.canonical_to_date(p_global_attribute2));
444 EXCEPTION
445 WHEN OTHERS THEN
446 jg_globe_flex_val_shared.insert_rejections('AP_INVOICES_INTERFACE',
447 p_parent_id,
448 'INVALID_GLOBAL_ATTR4',
449 p_default_last_updated_by,
450 p_default_last_update_login,
451 p_calling_sequence);
452 p_current_invoice_status := 'N';
453 END ;
454
455 END IF;
456
457
458
459 -- Global attributes 5 through 20 checked for rejection
460 IF ((p_global_attribute5 IS NOT NULL) OR
461 (p_global_attribute6 IS NOT NULL) OR
465 (p_global_attribute10 IS NOT NULL) OR
462 (p_global_attribute7 IS NOT NULL) OR
463 (p_global_attribute8 IS NOT NULL) OR
464 (p_global_attribute9 IS NOT NULL) OR
466 (p_global_attribute11 IS NOT NULL) OR
467 (p_global_attribute12 IS NOT NULL) OR
468 (p_global_attribute13 IS NOT NULL) OR
469 (p_global_attribute14 IS NOT NULL) OR
470 (p_global_attribute15 IS NOT NULL) OR
471 (p_global_attribute16 IS NOT NULL) OR
472 (p_global_attribute17 IS NOT NULL) OR
473 (p_global_attribute18 IS NOT NULL) OR
474 (p_global_attribute19 IS NOT NULL) OR
475 (p_global_attribute20 IS NOT NULL))
476 THEN
477 jg_globe_flex_val_shared.insert_rejections('AP_INVOICES_INTERFACE',
478 p_parent_id,
479 'GLOBAL_ATTR_VALUE_FOUND',
480 p_default_last_updated_by,
481 p_default_last_update_login,
482 p_calling_sequence);
483 p_current_invoice_status := 'N';
484 END IF;
485 END ja_th_apxiisim_invoices_folder;
486
487 --------------------------------------------------------------------------------------
488 -- JA_TW_APXIISIM_INVOICES_FOLDER()
489 --------------------------------------------------------------------------------------
490 -- Following segments are defined for Taiwanese Invoice Interface:
491 --------------------------------------------------------------------------------------
492 -- No. Name Column Value Set Required
493 -- --- -------------------------- ------------------ -------------------- ---------
494 -- 1 Invoice Format GLOBAL_ATTRIBUTE1 JA_TW_AP_GUI_FORMAT No
495 -- 2 Wine/Cigarette GLOBAL_ATTRIBUTE2 AP_SRS_YES_NO_OPT No
496 -- 3 Deductible Flag GLOBAL_ATTRIBUTE3 JATW_AP_DEDUCTIBLE No
497 --------------------------------------------------------------------------------------
498 PROCEDURE ja_tw_apxiisim_invoices_folder
499 (p_parent_id IN NUMBER,
500 p_default_last_updated_by IN NUMBER,
501 p_default_last_update_login IN NUMBER,
502 p_global_attribute1 IN VARCHAR2,
503 p_global_attribute2 IN VARCHAR2,
504 p_global_attribute3 IN VARCHAR2,
505 p_global_attribute4 IN VARCHAR2,
506 p_global_attribute5 IN VARCHAR2,
507 p_global_attribute6 IN VARCHAR2,
508 p_global_attribute7 IN VARCHAR2,
509 p_global_attribute8 IN VARCHAR2,
510 p_global_attribute9 IN VARCHAR2,
511 p_global_attribute10 IN VARCHAR2,
512 p_global_attribute11 IN VARCHAR2,
513 p_global_attribute12 IN VARCHAR2,
514 p_global_attribute13 IN VARCHAR2,
515 p_global_attribute14 IN VARCHAR2,
516 p_global_attribute15 IN VARCHAR2,
517 p_global_attribute16 IN VARCHAR2,
518 p_global_attribute17 IN VARCHAR2,
519 p_global_attribute18 IN VARCHAR2,
520 p_global_attribute19 IN VARCHAR2,
521 p_global_attribute20 IN VARCHAR2,
522 p_current_invoice_status OUT NOCOPY VARCHAR2,
523 p_calling_sequence IN VARCHAR2) IS
524
525 BEGIN
526 IF (p_global_attribute1 NOT IN ('21','22','23','24','25','26','27','28')) THEN
527 jg_globe_flex_val_shared.insert_rejections('AP_INVOICES_INTERFACE',
528 p_parent_id,
529 'INVALID_GLOBAL_ATTR1',
530 p_default_last_updated_by,
531 p_default_last_update_login,
532 p_calling_sequence);
533 p_current_invoice_status := 'N';
534 END IF;
535
536 IF (p_global_attribute2 NOT IN ('Y','N')) THEN
537 jg_globe_flex_val_shared.insert_rejections('AP_INVOICES_INTERFACE',
538 p_parent_id,
539 'INVALID_GLOBAL_ATTR2',
540 p_default_last_updated_by,
541 p_default_last_update_login,
542 p_calling_sequence);
543 p_current_invoice_status := 'N';
544 END IF;
545
546 IF (p_global_attribute3 NOT IN ('1','2')) THEN
547 jg_globe_flex_val_shared.insert_rejections('AP_INVOICES_INTERFACE',
548 p_parent_id,
549 'INVALID_GLOBAL_ATTR3',
550 p_default_last_updated_by,
551 p_default_last_update_login,
552 p_calling_sequence);
553 p_current_invoice_status := 'N';
554 END IF;
555
556 /*
557 DECLARE
558 X_temp varchar2(30);
559 BEGIN
560 X_temp := fnd_date.canonical_to_date(p_global_attribute4);
561 EXCEPTION
562 WHEN OTHERS THEN
563 jg_globe_flex_val_shared.insert_rejections('AP_INVOICES_INTERFACE',
564 p_parent_id,
565 'INVALID_GLOBAL_ATTR4',
566 p_default_last_updated_by,
567 p_default_last_update_login,
568 p_calling_sequence);
569 p_current_invoice_status := 'N';
570 END;
571 */
575 (p_global_attribute8 IS NOT NULL) OR
572 IF ((p_global_attribute5 IS NOT NULL) OR
573 (p_global_attribute6 IS NOT NULL) OR
574 (p_global_attribute7 IS NOT NULL) OR
576 (p_global_attribute9 IS NOT NULL) OR
577 (p_global_attribute10 IS NOT NULL) OR
578 (p_global_attribute11 IS NOT NULL) OR
579 (p_global_attribute12 IS NOT NULL) OR
580 (p_global_attribute13 IS NOT NULL) OR
581 (p_global_attribute14 IS NOT NULL) OR
582 (p_global_attribute15 IS NOT NULL) OR
583 (p_global_attribute16 IS NOT NULL) OR
584 (p_global_attribute17 IS NOT NULL) OR
585 (p_global_attribute18 IS NOT NULL) OR
586 (p_global_attribute19 IS NOT NULL) OR
587 (p_global_attribute20 IS NOT NULL))
588 THEN
589 jg_globe_flex_val_shared.insert_rejections('AP_INVOICES_INTERFACE',
590 p_parent_id,
591 'GLOBAL_ATTR_VALUE_FOUND',
592 p_default_last_updated_by,
593 p_default_last_update_login,
594 p_calling_sequence);
595 p_current_invoice_status := 'N';
596 END IF;
597 END ja_tw_apxiisim_invoices_folder;
598
599 --------------------------------------------------------------------------------------
600 -- JA_SG_APXIISIM_INVOICES_FOLDER()
601 --------------------------------------------------------------------------------------
602 -- Following segments are defined for Singapore Invoice Interface:
603 --------------------------------------------------------------------------------------
604 -- No. Name Column Value Set Required
605 -- --- -------------------------- ------------------ -------------------- ---------
606 -- 1 Supplier Exchange Rate GLOBAL_ATTRIBUTE1 FND_NUMBER15 No
607 --------------------------------------------------------------------------------------
608 PROCEDURE ja_sg_apxiisim_invoices_folder
609 (p_parent_id IN NUMBER,
610 p_default_last_updated_by IN NUMBER,
611 p_default_last_update_login IN NUMBER,
612 p_global_attribute1 IN VARCHAR2,
613 p_global_attribute2 IN VARCHAR2,
614 p_global_attribute3 IN VARCHAR2,
615 p_global_attribute4 IN VARCHAR2,
616 p_global_attribute5 IN VARCHAR2,
617 p_global_attribute6 IN VARCHAR2,
618 p_global_attribute7 IN VARCHAR2,
619 p_global_attribute8 IN VARCHAR2,
620 p_global_attribute9 IN VARCHAR2,
621 p_global_attribute10 IN VARCHAR2,
622 p_global_attribute11 IN VARCHAR2,
623 p_global_attribute12 IN VARCHAR2,
624 p_global_attribute13 IN VARCHAR2,
625 p_global_attribute14 IN VARCHAR2,
626 p_global_attribute15 IN VARCHAR2,
627 p_global_attribute16 IN VARCHAR2,
628 p_global_attribute17 IN VARCHAR2,
629 p_global_attribute18 IN VARCHAR2,
630 p_global_attribute19 IN VARCHAR2,
631 p_global_attribute20 IN VARCHAR2,
632 p_current_invoice_status OUT NOCOPY VARCHAR2,
633 p_calling_sequence IN VARCHAR2) IS
634
635 BEGIN
636 /* NONE - NUMBER(15),Numbers Only(0-9) */
637 IF (NOT jg_globe_flex_val_shared.check_format(p_global_attribute1,'N',15,'','N','N','N','','')) THEN
638 jg_globe_flex_val_shared.insert_rejections('AP_INVOICES_INTERFACE',
639 p_parent_id,
640 'INVALID_GLOBAL_ATTR1',
641 p_default_last_updated_by,
642 p_default_last_update_login,
643 p_calling_sequence);
644 p_current_invoice_status := 'N';
645 END IF;
646
647 IF ((p_global_attribute2 IS NOT NULL) OR
648 (p_global_attribute3 IS NOT NULL) OR
649 (p_global_attribute4 IS NOT NULL) OR
650 (p_global_attribute5 IS NOT NULL) OR
651 (p_global_attribute6 IS NOT NULL) OR
652 (p_global_attribute7 IS NOT NULL) OR
653 (p_global_attribute8 IS NOT NULL) OR
654 (p_global_attribute9 IS NOT NULL) OR
655 (p_global_attribute10 IS NOT NULL) OR
656 (p_global_attribute11 IS NOT NULL) OR
657 (p_global_attribute12 IS NOT NULL) OR
658 (p_global_attribute13 IS NOT NULL) OR
659 (p_global_attribute14 IS NOT NULL) OR
660 (p_global_attribute15 IS NOT NULL) OR
661 (p_global_attribute16 IS NOT NULL) OR
662 (p_global_attribute17 IS NOT NULL) OR
663 (p_global_attribute18 IS NOT NULL) OR
664 (p_global_attribute19 IS NOT NULL) OR
665 (p_global_attribute20 IS NOT NULL))
666 THEN
667 jg_globe_flex_val_shared.insert_rejections('AP_INVOICES_INTERFACE',
668 p_parent_id,
669 'GLOBAL_ATTR_VALUE_FOUND',
670 p_default_last_updated_by,
671 p_default_last_update_login,
672 p_calling_sequence);
673 p_current_invoice_status := 'N';
674 END IF;
675 END ja_sg_apxiisim_invoices_folder;
676
677 --------------------------------------------------------------------------------------
678 -- JA_CA_APXIISIM_LINES_FOLDER()
679 --------------------------------------------------------------------------------------
683 PROCEDURE ja_ca_apxiisim_lines_folder
680 -- There is no global flexfield in R11i, so we don't provide any validation for
681 -- Invoice Gateway.
682 --------------------------------------------------------------------------------------
684 (p_set_of_books_id IN NUMBER,
685 p_invoice_date IN DATE,
686 p_parent_id IN NUMBER,
687 p_default_last_updated_by IN NUMBER,
688 p_default_last_update_login IN NUMBER,
689 p_global_attribute1 IN VARCHAR2,
690 p_global_attribute4 IN VARCHAR2,
691 p_global_attribute5 IN VARCHAR2,
692 p_global_attribute6 IN VARCHAR2,
693 p_global_attribute7 IN VARCHAR2,
694 p_global_attribute8 IN VARCHAR2,
695 p_global_attribute9 IN VARCHAR2,
696 p_global_attribute10 IN VARCHAR2,
697 p_global_attribute11 IN VARCHAR2,
698 p_global_attribute12 IN VARCHAR2,
699 p_global_attribute13 IN VARCHAR2,
700 p_global_attribute14 IN VARCHAR2,
701 p_global_attribute15 IN VARCHAR2,
702 p_global_attribute16 IN VARCHAR2,
703 p_global_attribute17 IN VARCHAR2,
704 p_global_attribute18 IN VARCHAR2,
705 p_global_attribute19 IN VARCHAR2,
706 p_global_attribute20 IN VARCHAR2,
707 p_current_invoice_status OUT NOCOPY VARCHAR2,
708 p_calling_sequence IN VARCHAR2) IS
709
710 BEGIN
711 null;
712 END ja_ca_apxiisim_lines_folder;
713
714 --------------------------------------------------------------------------------------
715 -- JA_KR_APXIISIM_LINES_FOLDER()
716 --------------------------------------------------------------------------------------
717 -- There is no global flexfield in R11i, so we don't provide any validation for
718 -- Invoice Gateway.
719 --------------------------------------------------------------------------------------
720 PROCEDURE ja_kr_apxiisim_lines_folder
721 (p_parent_id IN NUMBER,
722 p_default_last_updated_by IN NUMBER,
723 p_default_last_update_login IN NUMBER,
724 p_global_attribute1 IN VARCHAR2,
725 p_global_attribute2 IN VARCHAR2,
726 p_global_attribute9 IN VARCHAR2,
727 p_global_attribute10 IN VARCHAR2,
728 p_global_attribute11 IN VARCHAR2,
729 p_global_attribute12 IN VARCHAR2,
730 p_global_attribute13 IN VARCHAR2,
731 p_global_attribute14 IN VARCHAR2,
732 p_global_attribute15 IN VARCHAR2,
733 p_global_attribute16 IN VARCHAR2,
734 p_global_attribute17 IN VARCHAR2,
735 p_global_attribute18 IN VARCHAR2,
736 p_global_attribute19 IN VARCHAR2,
737 p_global_attribute20 IN VARCHAR2,
738 p_current_invoice_status OUT NOCOPY VARCHAR2,
739 p_calling_sequence IN VARCHAR2) IS
740
741 BEGIN
742 null;
743 END ja_kr_apxiisim_lines_folder;
744
745 -----------------------------------------------------------------
746 -- Receivables Business Rules --
747 -----------------------------------------------------------------
748 PROCEDURE ar_business_rules
749 (p_calling_program_name IN VARCHAR2,
750 p_sob_id IN NUMBER,
751 p_row_id IN VARCHAR2,
752 p_customer_name IN VARCHAR2,
753 p_customer_number IN NUMBER,
754 p_jgzz_fiscal_code IN VARCHAR2,
755 p_generate_customer_number IN VARCHAR2,
756 p_orig_system_customer_ref IN VARCHAR2,
757 p_insert_update_flag IN VARCHAR2,
758 p_request_id IN NUMBER,
759 p_global_attribute_category IN VARCHAR2,
760 p_global_attribute1 IN VARCHAR2,
761 p_global_attribute2 IN VARCHAR2,
762 p_global_attribute3 IN VARCHAR2,
763 p_global_attribute4 IN VARCHAR2,
764 p_global_attribute5 IN VARCHAR2,
765 p_global_attribute6 IN VARCHAR2,
766 p_global_attribute7 IN VARCHAR2,
767 p_global_attribute8 IN VARCHAR2,
768 p_global_attribute9 IN VARCHAR2,
769 p_global_attribute10 IN VARCHAR2,
770 p_global_attribute11 IN VARCHAR2,
771 p_global_attribute12 IN VARCHAR2,
772 p_global_attribute13 IN VARCHAR2,
773 p_global_attribute14 IN VARCHAR2,
774 p_global_attribute15 IN VARCHAR2,
775 p_global_attribute16 IN VARCHAR2,
776 p_global_attribute17 IN VARCHAR2,
777 p_global_attribute18 IN VARCHAR2,
778 p_global_attribute19 IN VARCHAR2,
779 p_global_attribute20 IN VARCHAR2,
780 p_current_record_status OUT NOCOPY VARCHAR2) IS
781
782 BEGIN
783
784 p_current_record_status := 'S';
785
786 END ar_business_rules;
787
788 PROCEDURE ar_business_rules(
789 p_int_table_name IN VARCHAR2,
790 p_glob_attr_set1 IN JG_GLOBE_FLEX_VAL_SHARED.GdfRec,
791 p_glob_attr_set2 IN JG_GLOBE_FLEX_VAL_SHARED.GdfRec,
795
792 p_glob_attr_set3 IN JG_GLOBE_FLEX_VAL_SHARED.GdfRec,
793 p_misc_prod_arg IN JG_GLOBE_FLEX_VAL_SHARED.GenRec,
794 p_current_record_status OUT NOCOPY VARCHAR2) IS
796 l_current_record_status VARCHAR2(1);
797
798 l_ou_id NUMBER;
799
800 BEGIN
801 l_current_record_status := 'S';
802 fnd_profile.get('ORG_ID',l_ou_id);
803 IF jg_zz_shared_pkg.get_country(l_ou_id, NULL) = 'TW' THEN
804 IF p_int_table_name = 'CUSTOMER' THEN
805 ja_tw_arxcudci_customers(
806 p_glob_attr_set1
807 , p_glob_attr_set2
808 , p_glob_attr_set3
809 , p_misc_prod_arg
810 , l_current_record_status);
811 END IF;
812 END IF;
813
814 p_current_record_status := l_current_record_status;
815
816 END ar_business_rules;
817
818 PROCEDURE ja_tw_arxcudci_customers(
819 p_glob_attr_set1 IN JG_GLOBE_FLEX_VAL_SHARED.GdfRec,
820 p_glob_attr_set2 IN JG_GLOBE_FLEX_VAL_SHARED.GdfRec,
821 p_glob_attr_set3 IN JG_GLOBE_FLEX_VAL_SHARED.GdfRec,
822 p_misc_prod_arg IN JG_GLOBE_FLEX_VAL_SHARED.GenRec,
823 p_current_record_status OUT NOCOPY VARCHAR2) IS
824
825 l_record_status VARCHAR2(1);
826 l_rowid ROWID;
827 l_customer_name VARCHAR2(50);
828 l_taxpayer_id VARCHAR2(20);
829 l_orig_system_customer_ref VARCHAR2(240);
830 l_insert_update_flag VARCHAR(1);
831 l_request_id NUMBER(15);
832 l_tax_reg_num VARCHAR2(50);
833 l_customer_id NUMBER(15);
834
835 l_mesg_code VARCHAR2(50);
836 l_table_name VARCHAR2(30);
837
838 BEGIN
839 /* Bug 4497198 : Stub out package body due to build error in referencing to
840 ra_customers table
841
842 l_record_status := 'S';
843 l_rowid := p_misc_prod_arg.core_prod_arg2;
844 l_customer_name := p_misc_prod_arg.core_prod_arg3;
845 l_taxpayer_id := p_misc_prod_arg.core_prod_arg5;
846 l_orig_system_customer_ref := p_misc_prod_arg.core_prod_arg7;
847 l_insert_update_flag := p_misc_prod_arg.core_prod_arg8;
848 l_request_id := p_misc_prod_arg.core_prod_arg9;
849 l_tax_reg_num := p_misc_prod_arg.core_prod_arg10;
850
851 l_table_name := 'RA_CUSTOMERS_INTERFACE';
852
853 --
854 -- ** Index **
855 -- 0. Get Customer ID for Uniqueness Check
856 -- 1. Validate Taxpayer ID
857 -- 1-1. Numeric
858 -- 1-2. Length
859 -- 1-3. Uniqueness
860 -- 2. Validate Tax Registration Number
861 -- 2-1. Numeric`
862 -- 2-2. Length
863 -- 2-3. Uniqueness
864 --
865
866 --
867 -- Get Customer ID for Uniqueness Check
868 --
869 IF (l_insert_update_flag = 'U') THEN
870 BEGIN
871 SELECT
872 rc.customer_id
873 INTO
874 l_customer_id
875 FROM
876 ra_customers rc
877 WHERE
878 rc.orig_system_reference = l_orig_system_customer_ref;
879 EXCEPTION
880 WHEN NO_DATA_FOUND THEN
881 arp_standard.debug('No data found in RA_CUSTOMERS table for Update');
882 WHEN OTHERS THEN
883 arp_standard.debug('Exception in ja_interface_val.ja_tw_arxcudci_customers');
884 arp_standard.debug(SQLERRM);
885 END;
886 ELSE
887 l_customer_id := 0;
888 END IF;
889
890 --
891 -- ** Taxpayer ID Validation **
892 -- +---------------+------+---------------------------------------------+
893 -- | Validation | Code | Message Text(Summary) |
894 -- +---------------+------+---------------------------------------------+
895 -- |1. Numeric | k3 | Taxpayer ID should be numeric. |
896 -- |2. 8 digits | k4 | Taxpayer ID exceeds maximum length allowed. |
897 -- |3. Unique | k5 | Duplicate Taxpayer ID. |
898 -- +---------------+------+---------------------------------------------+
899
900 --
901 -- 1. Taxpayer ID - Numeric
902 --
903 IF jg_taxid_val_pkg.check_numeric(
904 l_taxpayer_id -- Taxpayer ID
905 ) <> 'TRUE'
906 THEN
907 l_mesg_code := 'k3,';
908 l_record_status := 'E';
909 END IF;
910
911 --
912 -- 2. Taxpayer ID - 8 digits
913 --
914 IF l_taxpayer_id IS NOT NULL THEN
915 IF jg_taxid_val_pkg.check_length(
916 'TW' -- Country Code
917 , 8 -- Maximum Length
918 , l_taxpayer_id -- Taxpayer ID
919 ) <> 'TRUE'
920 THEN
921 l_mesg_code := l_mesg_code || 'k4,';
922 l_record_status := 'E';
923 END IF;
924 END IF;
925
926 --
927 -- 3. Taxpayer ID - Unique
928 --
929 IF jg_taxid_val_pkg.check_uniqueness(
930 'TW' -- Country Code
931 , l_taxpayer_id -- Taxpayer ID
932 , l_customer_id -- Customer ID
933 ,'RACUST' -- Calling Program Name
934 , l_orig_system_customer_ref -- Unique Customer Identifier
935 , l_customer_name -- Customer Name
936 , l_request_id -- Request ID
937 ) <> 'TRUE'
941 END IF;
938 THEN
939 l_mesg_code := l_mesg_code || 'k5,';
940 l_record_status := 'E';
942
943 --
944 -- ** Tax Registration Num Validation **
945 -- +----------------------+---------------------------------------------+
946 -- | Validation | Code | Message Text(Summary) |
947 -- +---------------+------+---------------------------------------------+
948 -- |1. Numeric | t5 | Tax Reg. No. should be numeric. |
949 -- |2. 9 digits | t6 | Tax Reg. No. exceeds maximum length allowed.|
950 -- |3. Unique | t7 | Duplicate Tax Reg. No. |
951 -- +---------------+----------------------------------------------------+
952
953 --
954 -- 1. Tax Registration Number - Numeric
955 --
956 IF jg_taxid_val_pkg.check_numeric(
957 l_tax_reg_num -- Tax Registration Number
958 ) <> 'TRUE'
959 THEN
960 l_mesg_code := l_mesg_code || 't5';
961 l_record_status := 'E';
962 END IF;
963
964 --
965 -- 2. Tax Registration Number - 9 digits
966 --
967 IF l_tax_reg_num IS NOT NULL THEN
968 IF jg_taxid_val_pkg.check_length(
969 'TW' -- Country Code
970 , 9 -- Maximum Length
971 , l_tax_reg_num -- Tax Registration Number
972 ) <> 'TRUE'
973 THEN
974 l_mesg_code := l_mesg_code || 't6';
975 l_record_status := 'E';
976 END IF;
977 END IF;
978
979 --
980 -- 3. Tax Registration Number - Unique
981 --
982 IF jg_taxid_val_pkg.check_unique_tax_reg_Num(
983 'TW' -- Country Code
984 , l_tax_reg_num -- Tax Registrtion Number
985 , l_customer_id -- Customer ID
986 ,'RACUST' -- Calling Program Name
987 , l_orig_system_customer_ref -- Unique Customer Identifier
988 , l_customer_name -- Customer Name
989 , l_request_id -- Request ID
990 ) <> 'TRUE'
991 THEN
992 l_mesg_code := l_mesg_code || 't7';
993 l_record_status := 'E';
994 END IF;
995
996 --
997 -- Update Interface_Status of ra_customers_interface.
998 --
999
1000 IF l_record_status = 'E' THEN
1001 jg_globe_flex_val_shared.update_interface_status(
1002 l_rowid,
1003 l_table_name,
1004 l_mesg_code,
1005 'E');
1006 END IF;
1007
1008 --
1009 -- Return current status to ar_business_rules
1010 --
1011 p_current_record_status := l_record_status;
1012 */
1013 NULL;
1014 END ja_tw_arxcudci_customers;
1015
1016 END ja_interface_val;
[Home] [Help]