1 PACKAGE BODY JL_INTERFACE_VAL AS
2 /* $Header: jgjlgdfb.pls 120.13 2006/03/31 20:30:34 amohiudd ship $ */
3
4 --PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('TAX_DEBUG_FLAG'), 'N');
5 -- Bugfix# 3259701
6 --PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
7
8 PROCEDURE ap_business_rules
9 (p_calling_program_name IN VARCHAR2,
10 p_set_of_books_id IN NUMBER,
11 p_invoice_date IN DATE,
12 p_parent_table IN VARCHAR2,
13 p_parent_id IN NUMBER,
14 p_default_last_updated_by IN NUMBER,
15 p_default_last_update_login IN NUMBER,
16 p_line_type_lookup_code IN VARCHAR2,
17 p_global_attribute_category IN VARCHAR2,
18 p_global_attribute1 IN VARCHAR2,
19 p_global_attribute2 IN VARCHAR2,
20 p_global_attribute3 IN VARCHAR2,
21 p_global_attribute4 IN VARCHAR2,
22 p_global_attribute5 IN VARCHAR2,
23 p_global_attribute6 IN VARCHAR2,
24 p_global_attribute7 IN VARCHAR2,
25 p_global_attribute8 IN VARCHAR2,
26 p_global_attribute9 IN VARCHAR2,
27 p_global_attribute10 IN VARCHAR2,
28 p_global_attribute11 IN VARCHAR2,
29 p_global_attribute12 IN VARCHAR2,
30 p_global_attribute13 IN VARCHAR2,
31 p_global_attribute14 IN VARCHAR2,
32 p_global_attribute15 IN VARCHAR2,
33 p_global_attribute16 IN VARCHAR2,
34 p_global_attribute17 IN VARCHAR2,
35 p_global_attribute18 IN VARCHAR2,
36 p_global_attribute19 IN VARCHAR2,
37 p_global_attribute20 IN VARCHAR2,
38 p_current_invoice_status OUT NOCOPY VARCHAR2,
39 p_calling_sequence IN VARCHAR2) IS
40
41 l_credit_exists VARCHAR2(1);
42 l_current_invoice_status VARCHAR2(1); -- := 'Y';
43
44 l_debug_loc VARCHAR2(30); -- := 'check_ap_business_rules';
45 l_curr_calling_sequence VARCHAR2(2000);
46 l_debug_info VARCHAR2(100);
47 l_country_code VARCHAR2(10);
48
49 l_ou_id NUMBER;
50
51 BEGIN
52 l_current_invoice_status := 'Y';
53 l_debug_loc := 'check_ap_business_rules';
54 -------------------------- DEBUG INFORMATION ------------------------------
55 l_curr_calling_sequence := 'jl_interface_val.'||l_debug_loc||'<-'||p_calling_sequence;
56 l_debug_info := 'ap business rules';
57 ---------------------------------------------------------------------------
58
59 ------------------------------------------------------------------------------
60 -- Global Flexfield Validation
61
62 ------------------------------------------------------------------------------
63 -- You can add your own validation code for your global flexfields.
64
65 -- You should not include arguments(GLOBAL_ATTRIBUTE(n)) you do not validate
66 -- in your procedure.
67
68 -- Form Name: APXIISIM
69 ------------------------------------------------------------------------------
70 -- Header Level Validation - Block Name: INVOICES_FOLDER
71
72 ------------------------------------------------------------------------------
73 -- 1-27.JL.AR.APXIISIM.INVOICES_FOLDER
74 -- 1-27a.JL.CO.APXIISIM.INVOICES_FOLDER -- Bug 3233307
75 -- 1-28.JL.BR.APXIISIM.INVOICES_FOLDER
76 -- 1-29.JL.CL.APXIISIM.INVOICES_FOLDER
77 ------------------------------------------------------------------------------
78 -- Line Level Validation - Block Name: INVOICE_LINES_FOLDER
79
80 ------------------------------------------------------------------------------
81 -- 2-5. JL.AR.APXIISIM.LINES_FOLDER
82 -- 2-6. JL.CO.APXIIFIX.LINES_FOLDER
83 -- 2-7. JL.BR.APXIISIM.LINES_FOLDER
84
85 ------------------------------------------------------------------------------
86
87 IF (p_global_attribute_category = 'JL.AR.APXIISIM.INVOICES_FOLDER') THEN
88
89 jl_ar_apxiisim_invoices_folder
90 (p_parent_id ,
91 p_default_last_updated_by,
92 p_default_last_update_login,
93 p_global_attribute1,
94 p_global_attribute2,
95 p_global_attribute3,
96 p_global_attribute4,
97 p_global_attribute5,
98 p_global_attribute6,
99 p_global_attribute7,
100 p_global_attribute8,
101 p_global_attribute9,
102 p_global_attribute10,
103 p_global_attribute11,
104 p_global_attribute12,
105 p_global_attribute13,
106 p_global_attribute14,
107 p_global_attribute15,
108 p_global_attribute16,
109 p_global_attribute17,
110 p_global_attribute18,
111 p_global_attribute19,
112 p_global_attribute20,
113 l_current_invoice_status,
114 p_calling_sequence);
115
116 ELSIF (p_global_attribute_category ='JL.AR.APXIISIM.LINES_FOLDER') THEN
117 jl_ar_apxiisim_lines_folder
118 (p_parent_id,
119 p_default_last_updated_by,
120 p_default_last_update_login,
121 p_global_attribute1,
122 p_global_attribute2,
123 p_global_attribute3,
124 p_global_attribute4,
125 p_global_attribute5,
126 p_global_attribute6,
127 p_global_attribute7,
128 p_global_attribute8,
129 p_global_attribute9,
130 p_global_attribute10,
131 p_global_attribute11,
132 p_global_attribute12,
133 p_global_attribute13,
134 p_global_attribute14,
135 p_global_attribute15,
136 p_global_attribute16,
137 p_global_attribute17,
138 p_global_attribute18,
139 p_global_attribute19,
140 p_global_attribute20,
141 l_current_invoice_status,
142 p_calling_sequence);
143
144 -- Bug 3233307 JL.CO.APXIISIM.INVOICES_FOLDER
145 ELSIF (p_global_attribute_category = 'JL.CO.APXIISIM.INVOICES_FOLDER') THEN
146
147 jl_co_apxiisim_invoices_folder
148 (p_parent_id ,
149 p_default_last_updated_by,
150 p_default_last_update_login,
151 p_global_attribute1,
152 p_global_attribute2,
153 p_global_attribute3,
154 p_global_attribute4,
155 p_global_attribute5,
156 p_global_attribute6,
157 p_global_attribute7,
158 p_global_attribute8,
159 p_global_attribute9,
160 p_global_attribute10,
161 p_global_attribute11,
162 p_global_attribute12,
163 p_global_attribute13,
164 p_global_attribute14,
165 p_global_attribute15,
166 p_global_attribute16,
167 p_global_attribute17,
168 p_global_attribute18,
169 p_global_attribute19,
170 p_global_attribute20,
171 l_current_invoice_status,
172 p_calling_sequence);
173
174 --
175 -- 2-5. JL.CO.APXIISIM.LINES_FOLDER
176 --
177
178 ELSIF (p_global_attribute_category ='JL.CO.APXIISIM.LINES_FOLDER') THEN
179
180 jl_co_apxiisim_lines_folder
181 (p_parent_id,
182 p_default_last_updated_by,
183 p_default_last_update_login,
184 p_global_attribute1,
185 p_global_attribute2,
186 p_global_attribute3,
187 p_global_attribute4,
188 p_global_attribute5,
189 p_global_attribute6,
190 p_global_attribute7,
191 p_global_attribute8,
192 p_global_attribute9,
193 p_global_attribute10,
194 p_global_attribute11,
195 p_global_attribute12,
196 p_global_attribute13,
197 p_global_attribute14,
198 p_global_attribute15,
199 p_global_attribute16,
200 p_global_attribute17,
201 p_global_attribute18,
202 p_global_attribute19,
203 p_global_attribute20,
204 l_current_invoice_status,
205 p_calling_sequence);
206
207 --
208 -- 1-28. JL.BR.APXIISIM.INVOICES_FOLDER
209 --
210
211 ELSIF (p_global_attribute_category = 'JL.BR.APXIISIM.INVOICES_FOLDER') THEN
212
213 jl_br_apxiisim_invoices_folder
214 (p_parent_id ,
215 p_default_last_updated_by,
216 p_default_last_update_login,
217 p_global_attribute1,
218 p_global_attribute2,
219 p_global_attribute3,
220 p_global_attribute4,
221 p_global_attribute5,
222 p_global_attribute6,
223 p_global_attribute7,
224 p_global_attribute8,
225 p_global_attribute9,
226 p_global_attribute10,
227 p_global_attribute11,
228 p_global_attribute12,
229 p_global_attribute13,
230 p_global_attribute14,
231 p_global_attribute15,
232 p_global_attribute16,
233 p_global_attribute17,
234 p_global_attribute18,
235 p_global_attribute19,
236 p_global_attribute20,
237 l_current_invoice_status,
238 p_calling_sequence);
239
240 --
241 -- 2-7. JL.BR.APXIISIM.LINES_FOLDER
242 --
243
244 ELSIF (p_global_attribute_category = 'JL.BR.APXIISIM.LINES_FOLDER') THEN
245
246 jl_br_apxiisim_lines_folder
247 (p_parent_id ,
248 p_line_type_lookup_code,
249 p_default_last_updated_by,
250 p_default_last_update_login,
251 p_global_attribute1,
252 p_global_attribute2,
253 p_global_attribute3,
254 p_global_attribute4,
255 p_global_attribute5,
256 p_global_attribute6,
257 p_global_attribute7,
258 p_global_attribute8,
259 p_global_attribute9,
260 p_global_attribute10,
261 p_global_attribute11,
262 p_global_attribute12,
263 p_global_attribute13,
264 p_global_attribute14,
265 p_global_attribute15,
266 p_global_attribute16,
267 p_global_attribute17,
268 p_global_attribute18,
269 p_global_attribute19,
270 p_global_attribute20,
271 l_current_invoice_status,
272 p_calling_sequence);
273
274 -- togeorge 11/18/1999
275 -- Bug# 1074309
276 jl_br_apxiisim_val_cfo_code
277 (p_parent_id ,
278 p_line_type_lookup_code,
279 p_default_last_updated_by,
280 p_default_last_update_login,
281 l_current_invoice_status,
282 p_calling_sequence);
283
284
285 --
286 -- 1-29. JL.CL.APXIISIM.INVOICES_FOLDER
287 --
288
289 ELSIF (p_global_attribute_category = 'JL.CL.APXIISIM.INVOICES_FOLDER') THEN
290
291 jl_cl_apxiisim_invoices_folder
292 (p_parent_id ,
293 p_default_last_updated_by,
294 p_default_last_update_login,
295 p_global_attribute1,
296 p_global_attribute2,
297 p_global_attribute3,
298 p_global_attribute4,
299 p_global_attribute5,
300 p_global_attribute6,
301 p_global_attribute7,
302 p_global_attribute8,
303 p_global_attribute9,
304 p_global_attribute10,
305 p_global_attribute11,
306 p_global_attribute12,
307 p_global_attribute13,
308 p_global_attribute14,
309 p_global_attribute15,
310 p_global_attribute16,
311 p_global_attribute17,
312 p_global_attribute18,
313 p_global_attribute19,
314 p_global_attribute20,
315 l_current_invoice_status,
316 p_calling_sequence);
317
318 END IF;
319
320 p_current_invoice_status := l_current_invoice_status;
321
322 /***************************************************************
323 -- Call to check for multiple balancing segments
324 ***************************************************************/
325 ------------------------
326 -- Get the Country Code
327 ------------------------
328 --Bug 2354736
329 --fnd_profile.get('ORG_ID',l_ou_id);
330 --Bug 4499004
331 --commented out above line and getting org id from fnd concurrents
332 SELECT org_id into l_ou_id FROM fnd_concurrent_requests
333 WHERE request_id = fnd_global.conc_request_id ;
334
335 fnd_request.set_org_id(l_ou_id);
336 l_country_code := jg_zz_shared_pkg.get_country(l_ou_id, NULL,null);
337
338 -------------------------------------------------------
339 -- Execute the Colombia Balancing Segament Validation
340 -------------------------------------------------------
341 IF (l_country_code = 'CO') THEN
342
343 Declare
344 invo_id number;
345 l_liability_post_lookup_code AP_SYSTEM_PARAMETERS.liability_post_lookup_code%TYPE;
346 validate_error varchar2(200);
347 l_cursor NUMBER;
348 l_sqlstmt VARCHAR2(1000);
349 l_ignore NUMBER;
350 Begin
351 SELECT invoice_id
352 INTO invo_id
353 FROM ap_invoice_lines_interface
354 WHERE invoice_line_id = P_parent_id;
355
356 ----------------------------------------------------------------------------------------
357 -- Get Set of Books and Auto-offsets Option info
358 ----------------------------------------------------------------------------------------
359
360 SELECT nvl(liability_post_lookup_code, 'NONE')
361 INTO l_liability_post_lookup_code
362 FROM ap_system_parameters;
363
364
365 IF (l_Liability_Post_Lookup_Code = 'BALANCING_SEGMENT') AND
366 (Ap_Extended_Withholding_Pkg.Ap_Extended_Withholding_Active) THEN
367
368 Begin
369 ------------------------------------------
370 -- Dynamic Call
371 ------------------------------------------
372 -- Create the SQL statement
373 l_cursor := dbms_sql.open_cursor;
374 l_sqlstmt := 'BEGIN :validate_error := ' ||
375 'JL_ZZ_AP_WITHHOLDING_PKG.Validate_Mult_BS_GateWay(:invo_id); END;';
376
377 -- Parse the SQL statement
378 dbms_sql.parse (l_cursor, l_sqlstmt, dbms_sql.native);
379
380 -- Define the variables
381 dbms_sql.bind_variable (l_cursor, 'validate_error', validate_error,200);
382 dbms_sql.bind_variable (l_cursor, 'invo_id', invo_id);
383
384 -- Execute the SQL statement
385 l_ignore := dbms_sql.execute (l_cursor);
386
387 -- Get the return value (success)
388 dbms_sql.variable_value (l_cursor, 'validate_error', validate_error);
389
390 -- Close the cursor
391 dbms_sql.close_cursor (l_cursor);
392
393 EXCEPTION
394 WHEN others THEN
395 IF (dbms_sql.is_open(l_cursor)) THEN
396 dbms_sql.close_cursor(l_cursor);
397 END IF;
398 End;
399 -- Validate Mul BS by distribution lines.
400 IF (validate_error = 'Error') THEN
401 jg_globe_flex_val_shared.insert_rejections('AP_INVOICES_INTERFACE',
402 invo_id,
403 'MULTIPLE BAL SEG FOUND',
404 p_default_last_updated_by,
405 p_default_last_update_login,
406 p_calling_sequence);
407 p_current_invoice_status := 'N';
408 END IF;
409 END IF;
410 Exception
411 WHEN OTHERS THEN
412 null;
413 End; -- Pl Block
414 END IF;
415
416 EXCEPTION
417 WHEN OTHERS THEN
418 IF (SQLCODE <> -20001) THEN
419 FND_MESSAGE.SET_NAME('SQLAP', 'AP_DEBUG');
420 FND_MESSAGE.SET_TOKEN('ERROR', 'SQLERRM');
421 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
422 FND_MESSAGE.SET_TOKEN('PARAMETERS',
423 'Set Of Books Id = '||to_char(p_set_of_books_id)
424 ||', Parent Table = '||p_parent_table
425 ||', Parent Id = '||to_char(p_parent_id)
426 ||', Last Updated By = '||to_char(p_default_last_updated_by)
427 ||', Last Update Login = '||to_char(p_default_last_update_login)
428 ||', Line Type Lookup Code = '||p_line_type_lookup_code
429 ||', Global Attribute Category = '||p_global_attribute_category
430 ||', Global Attribute1 = '||p_global_attribute1
431 ||', Global Attribute2 = '||p_global_attribute2
432 ||', Global Attribute3 = '||p_global_attribute3
433 ||', Global Attribute4 = '||p_global_attribute4
434 ||', Global Attribute5 = '||p_global_attribute5
435 ||', Global Attribute6 = '||p_global_attribute6
436 ||', Global Attribute7 = '||p_global_attribute7
437 ||', Global Attribute8 = '||p_global_attribute8
438 ||', Global Attribute9 = '||p_global_attribute9
439 ||', Global Attribute10 = '||p_global_attribute10
440 ||', Global Attribute11 = '||p_global_attribute11
441 ||', Global Attribute12 = '||p_global_attribute12
442 ||', Global Attribute13 = '||p_global_attribute13
443 ||', Global Attribute14 = '||p_global_attribute14
444 ||', Global Attribute15 = '||p_global_attribute15
445 ||', Global Attribute16 = '||p_global_attribute16
446 ||', Global Attribute17 = '||p_global_attribute17
447 ||', Global Attribute18 = '||p_global_attribute18
448 ||', Global Attribute19 = '||p_global_attribute19
449 ||', Global Attribute20 = '||p_global_attribute20);
450
451 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
452 END IF;
453 APP_EXCEPTION.RAISE_EXCEPTION;
454
455 END ap_business_rules;
456
457 -------------------------------------------------------------------------------
458 -- Following segments are defined for Argentina Invoice Interface
459 -------------------------------------------------------------------------------
460 -- No. Name Column Value Set Req.
461 -- --- ------------------- ------------------ -------------------------- ------
462 -- 1 Legal Transaction GLOBAL_ATTRIBUTE11 JLAR_AP_LEGAL_TAX_CATEGORY Yes
463 -- Category
464 -- 2 Transaction letter GLOBAL_ATTRIBUTE12 JLAR_DOCUMENT_LETTER Yes
465 -- 3 Tax Authority GlOBAL ATTRIBUTE13 JLAR_AP_DGI_CODE Yes
466 -- Transaction Type
467 -- 4 Customs Code GLOBAL_ATTRIBUTE14 15 charcters No
468 -- 5 Customs Issue Date GLOBAL_ATTRIBUTE15 FND_STANDARD_DATE No
469 -- 6 Customs Issue Number GLOBAL_ATTRIBUTE16 FND_NUMBER No
470 -- 7 Tax inclusive GLOBAL_ATTRIBUTE17 AP_SRS_YES_NO_MAND Yes
471 -- with Note
472 --------------------------------------------------------------------------------
473 --
474 -- This procedure validate the information in the GA 11,12,13,15,16,17
475 -- in the invoice header for Argentina.
476 --
477
478 PROCEDURE jl_ar_apxiisim_invoices_folder
479 (p_parent_id IN NUMBER,
480 p_default_last_updated_by IN NUMBER,
481 p_default_last_update_login IN NUMBER,
482 p_global_attribute1 IN VARCHAR2,
483 p_global_attribute2 IN VARCHAR2,
484 p_global_attribute3 IN VARCHAR2,
485 p_global_attribute4 IN VARCHAR2,
486 p_global_attribute5 IN VARCHAR2,
487 p_global_attribute6 IN VARCHAR2,
488 p_global_attribute7 IN VARCHAR2,
489 p_global_attribute8 IN VARCHAR2,
490 p_global_attribute9 IN VARCHAR2,
491 p_global_attribute10 IN VARCHAR2,
492 p_global_attribute11 IN VARCHAR2,
493 p_global_attribute12 IN VARCHAR2,
494 p_global_attribute13 IN VARCHAR2,
495 p_global_attribute14 IN VARCHAR2,
496 p_global_attribute15 IN VARCHAR2,
497 p_global_attribute16 IN VARCHAR2,
498 p_global_attribute17 IN VARCHAR2,
499 p_global_attribute18 IN VARCHAR2,
500 p_global_attribute19 IN VARCHAR2,
501 p_global_attribute20 IN VARCHAR2,
502 p_current_invoice_status OUT NOCOPY VARCHAR2,
503 p_calling_sequence IN VARCHAR2) IS
504
505 value_exists VARCHAR2(1);
506 Length_Date Number;
507 -- Bug 2729151
508 l_global_attribute15 VARCHAR2(15);
509
510 BEGIN
511
512 -- Validation for Legal Transaction Category
513
514 IF (p_global_attribute11 IS NOT NULL) THEN
515 BEGIN
516 SELECT 'X'
517 INTO value_exists
518 FROM fnd_lookups
519 WHERE lookup_type = 'JLAR_LEGAL_TRX_CATEGORY'
520 AND lookup_code = p_global_attribute11
521 AND nvl(start_date_active,sysdate) <= sysdate
522 AND nvl(end_date_active,sysdate) >= sysdate
523 AND enabled_flag = 'Y';
524 EXCEPTION
525 WHEN OTHERS THEN
526 jg_globe_flex_val_shared.insert_rejections('AP_INVOICES_INTERFACE',
527 p_parent_id,
528 'INVALID_GLOBAL_ATTR11',
529 p_default_last_updated_by,
530 p_default_last_update_login,
531 p_calling_sequence);
532 p_current_invoice_status := 'N';
533
534 END;
535 ELSE -- The Global Attribute11 is Required
536 jg_globe_flex_val_shared.insert_rejections('AP_INVOICES_INTERFACE',
537 p_parent_id,
538 'INVALID_GLOBAL_ATTR11',
539 p_default_last_updated_by,
540 p_default_last_update_login,
541 p_calling_sequence);
542 p_current_invoice_status := 'N';
543
544 END IF; -- p_global_attribute11 is not null
545
546 -- Validation for Transaction Letter
547
548 IF (p_global_attribute12 IS NOT NULL) THEN
549 BEGIN
550 SELECT 'X'
551 INTO value_exists
552 FROM fnd_lookups
553 WHERE lookup_type = 'JLAR_DOCUMENT_LETTER'
554 AND lookup_code = p_global_attribute12
555 AND nvl(start_date_active,sysdate) <= sysdate
556 AND nvl(end_date_active,sysdate) >= sysdate
557 AND enabled_flag = 'Y' ;
558 EXCEPTION
559 WHEN OTHERS THEN
560 jg_globe_flex_val_shared.insert_rejections('AP_INVOICES_INTERFACE',
561 p_parent_id,
562 'INVALID_GLOBAL_ATTR12',
563 p_default_last_updated_by,
564 p_default_last_update_login,
565 p_calling_sequence);
566 p_current_invoice_status := 'N';
567
568 END;
569 ELSE -- The Global Attribute12 is Required
570 jg_globe_flex_val_shared.insert_rejections('AP_INVOICES_INTERFACE',
571 p_parent_id,
572 'INVALID_GLOBAL_ATTR12',
573 p_default_last_updated_by,
574 p_default_last_update_login,
575 p_calling_sequence);
576 p_current_invoice_status := 'N';
577
578 END IF; -- p_global_attribute12 is not null
579
580 -- Tax Authority Transaction Type
581 IF (p_global_attribute13 IS NOT NULL) THEN -- Tax Authority Transaction Type
582 BEGIN
583 SELECT 'X'
584 INTO value_exists
585 FROM jl_ar_ap_trx_dgi_codes
586 WHERE trx_category = p_global_attribute11
587 and trx_letter = p_global_attribute12;
588 EXCEPTION
589 WHEN OTHERS THEN
590 jg_globe_flex_val_shared.insert_rejections('AP_INVOICES_INTERFACE',
591 p_parent_id,
592 'INVALID_GLOBAL_ATTR13',
593 p_default_last_updated_by,
594 p_default_last_update_login,
595 p_calling_sequence);
596
597 p_current_invoice_status := 'N';
598 END;
599 ELSE -- The Global Attribute13 is Required
600 jg_globe_flex_val_shared.insert_rejections('AP_INVOICES_INTERFACE',
601 p_parent_id,
602 'INVALID_GLOBAL_ATTR13',
603 p_default_last_updated_by,
604 p_default_last_update_login,
605 p_calling_sequence);
606 p_current_invoice_status := 'N';
607
608 END IF; -- p_global_attribute13 is not null
609
610 --********************************************************
611 -- It doesn't have any validation for Customs Code p_global_attribute 14
612 --
613 --**********************************************************
614
615 -- Customs Issue Date
616 IF (p_global_attribute15 IS NOT NULL) THEN
617 Length_Date := length (p_global_attribute15);
618
619 --Bug 2729151
620 l_global_attribute15 := to_char(fnd_date.canonical_to_date(p_global_attribute15));
621 Length_Date := length (l_global_attribute15);
622
623 IF (Length_Date = 9) THEN
624 IF NOT (jg_globe_flex_val_shared.Check_Format (l_global_attribute15,'D',9,'','','','','','')) THEN
625
626 jg_globe_flex_val_shared.insert_rejections('AP_INVOICES_INTERFACE',
627 p_parent_id,
628 'INVALID_GLOBAL_ATTR15',
629 p_default_last_updated_by,
630 p_default_last_update_login,
631 p_calling_sequence);
632
633 p_current_invoice_status := 'N';
634
635 END IF; -- Check_Format 9
636 ELSIF (Length_Date = 11) THEN
637
638 IF NOT(jg_globe_flex_val_shared.Check_Format (l_global_attribute15,'D', 11,'','','' ,'','','')) THEN
639
640 jg_globe_flex_val_shared.insert_rejections('AP_INVOICES_INTERFACE',
641 p_parent_id,
642 'INVALID_GLOBAL_ATTR15',
643 p_default_last_updated_by,
644 p_default_last_update_login,
645 p_calling_sequence);
646
647 p_current_invoice_status := 'N';
648
649 END IF; -- Check_Format 11
650 ELSE -- No Date Format.
651 jg_globe_flex_val_shared.insert_rejections('AP_INVOICES_INTERFACE',
652 p_parent_id,
653 'INVALID_GLOBAL_ATTR15',
654 p_default_last_updated_by,
655 p_default_last_update_login,
656 p_calling_sequence);
657
658 p_current_invoice_status := 'N';
659
660 END IF; -- Check_Format 11
661 END IF; -- Validate The Format Global Attribute15
662
663 -- Customs Issue Number
664 IF (p_global_attribute16 IS NOT NULL) THEN
665 -- Bug 2729151
666 -- Changed the 3rd paramter of check_format from '0' to '15'
667
668 IF NOT(jg_globe_flex_val_shared.Check_Format (p_global_attribute16,'C',15,'','', '','','','')) THEN
669
670 jg_globe_flex_val_shared.insert_rejections('AP_INVOICES_INTERFACE',
671 p_parent_id,
672 'INVALID_GLOBAL_ATTR16',
673 p_default_last_updated_by,
674 p_default_last_update_login,
675 p_calling_sequence);
676 p_current_invoice_status := 'N';
677
678 END IF; -- Check_Format Number
679 END IF; -- Validate Global Attribute 16
680
681 -- Tax Inclusive with Note
682 IF (p_global_attribute17 IS NOT NULL) THEN
683
684 BEGIN
685 SELECT 'X'
686 INTO value_exists
687 FROM fnd_lookups
688 WHERE lookup_type = 'YES_NO'
689 AND lookup_code = p_global_attribute17
690 AND nvl(start_date_active,sysdate) <= sysdate
691 AND nvl(end_date_active,sysdate) >= sysdate
692 AND enabled_flag = 'Y' ;
693 EXCEPTION
694 WHEN OTHERS THEN
695
696 jg_globe_flex_val_shared.insert_rejections('AP_INVOICES_INTERFACE',
697 p_parent_id,
698 'INVALID_GLOBAL_ATTR17',
699 p_default_last_updated_by,
700 p_default_last_update_login,
701 p_calling_sequence);
702 p_current_invoice_status := 'N';
703
704 END;
705 ELSE -- The Global Attribute17 is Required
706 jg_globe_flex_val_shared.insert_rejections('AP_INVOICES_INTERFACE',
707 p_parent_id,
708 'INVALID_GLOBAL_ATTR17',
709 p_default_last_updated_by,
710 p_default_last_update_login,
711 p_calling_sequence);
712 p_current_invoice_status := 'N';
713
714 END IF; -- p_global_attribute17 is not null
715
716 -- Validate the rest of the Global Attributes be NULL
717
718 IF ((p_global_attribute1 IS NOT NULL) OR
719 (p_global_attribute2 IS NOT NULL) OR
720 (p_global_attribute3 IS NOT NULL) OR
721 (p_global_attribute4 IS NOT NULL) OR
722 (p_global_attribute5 IS NOT NULL) OR
723 (p_global_attribute6 IS NOT NULL) OR
724 (p_global_attribute7 IS NOT NULL) OR
725 (p_global_attribute8 IS NOT NULL) OR
726 (p_global_attribute9 IS NOT NULL) OR
727 --(p_global_attribute10 IS NOT NULL) OR
728 (p_global_attribute18 IS NOT NULL) --OR
729 --(p_global_attribute19 IS NOT NULL) OR
730 --(p_global_attribute20 IS NOT NULL)
731 ) THEN
732 jg_globe_flex_val_shared.insert_rejections('AP_INVOICES_INTERFACE',
733 p_parent_id,
734 'GLOBAL_ATTR_VALUE_FOUND',
735 p_default_last_updated_by,
736 p_default_last_update_login,
737 p_calling_sequence);
738 p_current_invoice_status := 'N';
739 END IF;
740
741 ----------------------------------------------------------------------------------------
742 -- Call to check for multiple balancing segments
743 -- Get Set of Books and Auto-offsets Option info
744 ----------------------------------------------------------------------------------------
745 Declare
746 l_liability_post_lookup_code AP_SYSTEM_PARAMETERS.liability_post_lookup_code%TYPE;
747 validate_error varchar2(200);
748 l_cursor NUMBER;
749 l_sqlstmt VARCHAR2(1000);
750 l_ignore NUMBER;
751
752 Begin
753 SELECT nvl(liability_post_lookup_code, 'NONE')
754 INTO l_liability_post_lookup_code
755 FROM ap_system_parameters;
756
757 IF (l_Liability_Post_Lookup_Code = 'BALANCING_SEGMENT') AND
758 (Ap_Extended_Withholding_Pkg.Ap_Extended_Withholding_Active) THEN
759
760 Begin
761 ------------------------------------------
762 -- Dynamic Call
763 ------------------------------------------
764 -- Create the SQL statement
765 l_cursor := dbms_sql.open_cursor;
766 l_sqlstmt := 'BEGIN :validate_error := ' ||
767 'JL_ZZ_AP_WITHHOLDING_PKG.Validate_Mult_BS_GateWay(:p_parent_id); END;';
768
769 -- Parse the SQL statement
770 dbms_sql.parse (l_cursor, l_sqlstmt, dbms_sql.native);
771
772 -- Define the variables
773 dbms_sql.bind_variable (l_cursor, 'validate_error', validate_error,200);
774 dbms_sql.bind_variable (l_cursor, 'p_parent_id', p_parent_id);
775
776 -- Execute the SQL statement
777 l_ignore := dbms_sql.execute (l_cursor);
778
779 -- Get the return value (success)
780 dbms_sql.variable_value (l_cursor, 'validate_error', validate_error);
781
782 -- Close the cursor
783 dbms_sql.close_cursor (l_cursor);
784
785 EXCEPTION
786 WHEN others THEN
787 IF (dbms_sql.is_open(l_cursor)) THEN
788 dbms_sql.close_cursor(l_cursor);
789 END IF;
790 End;
791 -- Validate Mul BS by distribution lines.
792 IF (validate_error = 'Error') THEN
793 jg_globe_flex_val_shared.insert_rejections('AP_INVOICES_INTERFACE',
794 p_parent_id,
795 'MULTIPLE BAL SEG FOUND',
796 p_default_last_updated_by,
797 p_default_last_update_login,
798 p_calling_sequence);
799 p_current_invoice_status := 'N';
800 END IF;
801 END IF;
802 Exception
803 WHEN OTHERS THEN
804 null;
805 End; -- Pl Block
806
807 END jl_ar_apxiisim_invoices_folder;
808
809 -------------------------------------------------------------------------------
810 -- Following segments are defined for Argentina Invoice Lines Interface
811 -------------------------------------------------------------------------------
812 -- No. Name Column Value Set Required
813
814 -- --- ------------------- ------------------ ------------------------ -------
815 -- 1 Ship to Location GLOBAL_ATTRIBUTE3 JLZZ_AP_SHIP_TO_LOCATION Yes
816
817 -- 2 Tax Inclusive Amount GLOBAL_ATTRIBUTE4 FND_NUMBER No
818 -------------------------------------------------------------------------------
819
820 -- This procedure validate the information in the GA 3,4
821 -- in the invoice Line for Argentina.
822 --
823
824 PROCEDURE jl_ar_apxiisim_lines_folder
825 (p_parent_id IN NUMBER,
826 p_default_last_updated_by IN NUMBER,
827 p_default_last_update_login IN NUMBER,
828 p_global_attribute1 IN VARCHAR2,
829 p_global_attribute2 IN VARCHAR2,
830 p_global_attribute3 IN VARCHAR2,
831 p_global_attribute4 IN VARCHAR2,
832 p_global_attribute5 IN VARCHAR2,
833 p_global_attribute6 IN VARCHAR2,
834 p_global_attribute7 IN VARCHAR2,
835 p_global_attribute8 IN VARCHAR2,
836 p_global_attribute9 IN VARCHAR2,
837 p_global_attribute10 IN VARCHAR2,
838 p_global_attribute11 IN VARCHAR2,
839 p_global_attribute12 IN VARCHAR2,
840 p_global_attribute13 IN VARCHAR2,
841 p_global_attribute14 IN VARCHAR2,
842 p_global_attribute15 IN VARCHAR2,
843 p_global_attribute16 IN VARCHAR2,
844 p_global_attribute17 IN VARCHAR2,
845 p_global_attribute18 IN VARCHAR2,
846 p_global_attribute19 IN VARCHAR2,
847 p_global_attribute20 IN VARCHAR2,
848 p_current_invoice_status OUT NOCOPY VARCHAR2,
849 p_calling_sequence IN VARCHAR2) IS
850
851 value_exists VARCHAR2(1);
852
853 BEGIN
854 -- Ship to Location
855 IF (p_global_attribute3 IS NOT NULL) THEN
856 BEGIN
857 SELECT 'X'
858 INTO value_exists
859 FROM HR_Locations_all
860 WHERE Location_id = p_global_attribute3
861 AND sysdate < nvl(inactive_date, sysdate+1); -- Bug 3463869
862 EXCEPTION
863
864 WHEN OTHERS THEN
865 jg_globe_flex_val_shared.insert_rejections('AP_INVOICE_LINES_INTERFACE',
866 p_parent_id,
867 'INVALID_GLOBAL_ATTR3',
868 p_default_last_updated_by,
869 p_default_last_update_login,
870 p_calling_sequence);
871 p_current_invoice_status := 'N';
872
873 END;
874 ELSE -- The Global Attribute3 is Required
875
876 jg_globe_flex_val_shared.insert_rejections('AP_INVOICE_LINES_INTERFACE',
877 p_parent_id,
878 'INVALID_GLOBAL_ATTR3',
879 p_default_last_updated_by,
880 p_default_last_update_login,
881 p_calling_sequence);
882 p_current_invoice_status := 'N';
883 END IF; -- p_global_attribute3 is not null
884
885 -- Tax Inclusive Amount
886 IF (p_global_attribute4 IS NOT NULL) THEN
887 IF NOT (jg_globe_flex_val_shared.Check_Format (p_global_attribute4,'N',0, '','','','','','')) THEN
888 jg_globe_flex_val_shared.insert_rejections('AP_INVOICE_LINES_INTERFACE',
889 p_parent_id,
890 'INVALID_GLOBAL_ATTR4',
891 p_default_last_updated_by,
892 p_default_last_update_login,
893 p_calling_sequence);
894 p_current_invoice_status := 'N';
895
896 END IF; -- Check_Format Number
897 END IF; -- Validate Global Attribute 4
898
899 -- Validate the rest of the Global Attributes be NULL
900
901 IF ((p_global_attribute1 IS NOT NULL) OR
902 (p_global_attribute2 IS NOT NULL) OR
903 (p_global_attribute5 IS NOT NULL) OR
904 (p_global_attribute6 IS NOT NULL) OR
905 (p_global_attribute7 IS NOT NULL) OR
906 (p_global_attribute8 IS NOT NULL) OR
907 (p_global_attribute9 IS NOT NULL) OR
908 (p_global_attribute10 IS NOT NULL) OR
909 (p_global_attribute11 IS NOT NULL) OR
910 (p_global_attribute12 IS NOT NULL) OR
911 (p_global_attribute13 IS NOT NULL) OR
912 (p_global_attribute14 IS NOT NULL) OR
913 (p_global_attribute15 IS NOT NULL) OR
914 (p_global_attribute16 IS NOT NULL) OR
915 (p_global_attribute17 IS NOT NULL) OR
916 (p_global_attribute18 IS NOT NULL) OR
917 (p_global_attribute19 IS NOT NULL) OR
918 (p_global_attribute20 IS NOT NULL))
919 THEN
920 jg_globe_flex_val_shared.insert_rejections('AP_INVOICE_LINES_INTERFACE',
921 p_parent_id,
922 'GLOBAL_ATTR_VALUE_FOUND',
923 p_default_last_updated_by,
924 p_default_last_update_login,
925 p_calling_sequence);
926 p_current_invoice_status := 'N';
927 END IF;
928 END jl_ar_apxiisim_lines_folder;
929
930 -- Bug 3233307
931 -----------------------------------------------------------------------------
932 -- Following segments are defined for Colombia Invoice Interface
933 ------------------------------------------------------------------------------
934 -- No. Name Column Value Set Required
935 -- --- --------------- ----------------- -------------------------- ---------
936 -- 1 Ship to Location GLOBAL_ATTRIBUTE18 JLZZ_AP_SHIP_TO_LOCATION Yes
937
938 ------------------------------------------------------------------------------
939 --
940 -- This procedure validate the information in the GA 18
941 -- in the invoice Header for Colombia.
942
943 PROCEDURE jl_co_apxiisim_invoices_folder
944 (p_parent_id IN NUMBER,
945 p_default_last_updated_by IN NUMBER,
946 p_default_last_update_login IN NUMBER,
947 p_global_attribute1 IN VARCHAR2,
948 p_global_attribute2 IN VARCHAR2,
949 p_global_attribute3 IN VARCHAR2,
950 p_global_attribute4 IN VARCHAR2,
951 p_global_attribute5 IN VARCHAR2,
952 p_global_attribute6 IN VARCHAR2,
953 p_global_attribute7 IN VARCHAR2,
954 p_global_attribute8 IN VARCHAR2,
955 p_global_attribute9 IN VARCHAR2,
956 p_global_attribute10 IN VARCHAR2,
957 p_global_attribute11 IN VARCHAR2,
958 p_global_attribute12 IN VARCHAR2,
959 p_global_attribute13 IN VARCHAR2,
960 p_global_attribute14 IN VARCHAR2,
961 p_global_attribute15 IN VARCHAR2,
962 p_global_attribute16 IN VARCHAR2,
963 p_global_attribute17 IN VARCHAR2,
964 p_global_attribute18 IN VARCHAR2,
965 p_global_attribute19 IN VARCHAR2,
966 p_global_attribute20 IN VARCHAR2,
967 p_current_invoice_status OUT NOCOPY VARCHAR2,
968 p_calling_sequence IN VARCHAR2) IS
969
970 value_exists VARCHAR2(1);
971
972 BEGIN
973
974 -- Validation for Ship To Location
975
976 IF (p_global_attribute18 IS NOT NULL) THEN
977 BEGIN
978 SELECT 'X'
979 INTO value_exists
980 FROM HR_Locations_all
981 WHERE Location_id = p_global_attribute18
982 AND sysdate < nvl(inactive_date, sysdate+1); -- Bug 3463869
983 EXCEPTION
984
985 WHEN OTHERS THEN
986 jg_globe_flex_val_shared.insert_rejections('AP_INVOICES_INTERFACE',
987 p_parent_id,
988 'INVALID_GLOBAL_ATTR18',
989 p_default_last_updated_by,
990 p_default_last_update_login,
991 p_calling_sequence);
992 p_current_invoice_status := 'N';
993
994 END;
995
996 END IF; -- p_global_attribute18 is null
997
998 IF ((p_global_attribute1 IS NOT NULL) OR
999 (p_global_attribute2 IS NOT NULL) OR
1000 (p_global_attribute3 IS NOT NULL) OR
1001 (p_global_attribute4 IS NOT NULL) OR
1002 (p_global_attribute5 IS NOT NULL) OR
1003 (p_global_attribute6 IS NOT NULL) OR
1004 (p_global_attribute7 IS NOT NULL) OR
1005 (p_global_attribute8 IS NOT NULL) OR
1006 (p_global_attribute9 IS NOT NULL) OR
1007 (p_global_attribute10 IS NOT NULL) OR
1008 (p_global_attribute11 IS NOT NULL) OR
1009 (p_global_attribute12 IS NOT NULL) OR
1010 (p_global_attribute13 IS NOT NULL) OR
1011 (p_global_attribute14 IS NOT NULL) OR
1012 (p_global_attribute15 IS NOT NULL) OR
1013 (p_global_attribute16 IS NOT NULL) OR
1014 (p_global_attribute17 IS NOT NULL) OR
1015 (p_global_attribute19 IS NOT NULL) OR
1016 (p_global_attribute20 IS NOT NULL))
1017 THEN
1018 jg_globe_flex_val_shared.insert_rejections('AP_INVOICES_INTERFACE',
1019 p_parent_id,
1020 'GLOBAL_ATTR_VALUE_FOUND',
1021 p_default_last_updated_by,
1022 p_default_last_update_login,
1023 p_calling_sequence);
1024 p_current_invoice_status := 'N';
1025
1026 END IF;
1027
1028 END jl_co_apxiisim_invoices_folder;
1029
1030 -- End of Inclusion for bug 3233307
1031
1032 -----------------------------------------------------------------------------
1033 -- Following segments are defined for Colombia Invoice Lines Interface
1034 ------------------------------------------------------------------------------
1035 -- No. Name Column Value Set Required
1036 -- --- --------------- ----------------- -------------------------- ---------
1037 -- 1 TaxPayer ID GLOBAL_ATTRIBUTE2 JLCO_AP_THIRDPARTY_ID No
1038 -- 2 Ship to Location GLOBAL_ATTRIBUTE3 JLZZ_AP_SHIP_TO_LOCATION Yes
1039
1040 ------------------------------------------------------------------------------
1041 --
1042 -- This procedure validate the information in the GA 2,3
1043 -- in the invoice Line for Colombia.
1044 --
1045
1046 PROCEDURE jl_co_apxiisim_lines_folder
1047 (p_parent_id IN NUMBER,
1048 p_default_last_updated_by IN NUMBER,
1049 p_default_last_update_login IN NUMBER,
1050 p_global_attribute1 IN VARCHAR2,
1051 p_global_attribute2 IN VARCHAR2,
1052 p_global_attribute3 IN VARCHAR2,
1053 p_global_attribute4 IN VARCHAR2,
1054 p_global_attribute5 IN VARCHAR2,
1055 p_global_attribute6 IN VARCHAR2,
1056 p_global_attribute7 IN VARCHAR2,
1057 p_global_attribute8 IN VARCHAR2,
1058 p_global_attribute9 IN VARCHAR2,
1059 p_global_attribute10 IN VARCHAR2,
1060 p_global_attribute11 IN VARCHAR2,
1061 p_global_attribute12 IN VARCHAR2,
1062 p_global_attribute13 IN VARCHAR2,
1063 p_global_attribute14 IN VARCHAR2,
1064 p_global_attribute15 IN VARCHAR2,
1065 p_global_attribute16 IN VARCHAR2,
1066 p_global_attribute17 IN VARCHAR2,
1067 p_global_attribute18 IN VARCHAR2,
1068 p_global_attribute19 IN VARCHAR2,
1069 p_global_attribute20 IN VARCHAR2,
1070 p_current_invoice_status OUT NOCOPY VARCHAR2,
1071 p_calling_sequence IN VARCHAR2) IS
1072
1073 value_exists VARCHAR2(1);
1074
1075
1076 BEGIN
1077 -- Taxpayer Id
1078 IF (p_global_attribute2 IS NOT NULL) THEN
1079 -- The validation is with the vendor num (segment1)
1080 BEGIN
1081 SELECT 'X'
1082 INTO value_exists
1083 FROM PO_Vendors
1084
1085 WHERE segment1 = p_global_attribute2;
1086 EXCEPTION
1087
1088 WHEN OTHERS THEN
1089
1090 jg_globe_flex_val_shared.insert_rejections('AP_INVOICE_LINES_INTERFACE',
1091 p_parent_id,
1092 'INVALID_GLOBAL_ATTR2',
1093 p_default_last_updated_by,
1094 p_default_last_update_login,
1095 p_calling_sequence);
1096
1097 p_current_invoice_status := 'N';
1098
1099 END;
1100 END IF; -- p_global_attribute2 is not null
1101
1102
1103 -- Ship to Location
1104 IF (p_global_attribute3 IS NOT NULL) THEN
1105 BEGIN
1106 SELECT 'X'
1107 INTO value_exists
1108 FROM HR_Locations_all
1109 WHERE Location_id = p_global_attribute3
1110 AND sysdate < nvl(inactive_date, sysdate+1); -- Bug 3463869
1111 EXCEPTION
1112
1113 WHEN OTHERS THEN
1114
1115 jg_globe_flex_val_shared.insert_rejections('AP_INVOICE_LINES_INTERFACE',
1116 p_parent_id,
1117 'INVALID_GLOBAL_ATTR3',
1118 p_default_last_updated_by,
1119 p_default_last_update_login,
1120 p_calling_sequence);
1121 p_current_invoice_status := 'N';
1122
1123 END;
1124 ELSE -- The Global Attribute3 is Required
1125 jg_globe_flex_val_shared.insert_rejections('AP_INVOICE_LINES_INTERFACE',
1126 p_parent_id,
1127 'INVALID_GLOBAL_ATTR3',
1128 p_default_last_updated_by,
1129 p_default_last_update_login,
1130 p_calling_sequence);
1131 p_current_invoice_status := 'N';
1132
1133 END IF; -- p_global_attribute3 is not null
1134
1135
1136 IF ((p_global_attribute1 IS NOT NULL) OR
1137 (p_global_attribute4 IS NOT NULL) OR
1138 (p_global_attribute5 IS NOT NULL) OR
1139 (p_global_attribute6 IS NOT NULL) OR
1140 (p_global_attribute7 IS NOT NULL) OR
1141 (p_global_attribute8 IS NOT NULL) OR
1142 (p_global_attribute9 IS NOT NULL) OR
1143 (p_global_attribute10 IS NOT NULL) OR
1144 (p_global_attribute11 IS NOT NULL) OR
1145 (p_global_attribute12 IS NOT NULL) OR
1146 (p_global_attribute13 IS NOT NULL) OR
1147 (p_global_attribute14 IS NOT NULL) OR
1148 (p_global_attribute15 IS NOT NULL) OR
1149 (p_global_attribute16 IS NOT NULL) OR
1150 (p_global_attribute17 IS NOT NULL) OR
1151 (p_global_attribute18 IS NOT NULL) OR
1152 (p_global_attribute19 IS NOT NULL) OR
1153 (p_global_attribute20 IS NOT NULL))
1154 THEN
1155 jg_globe_flex_val_shared.insert_rejections('AP_INVOICE_LINES_INTERFACE',
1156 p_parent_id,
1157 'GLOBAL_ATTR_VALUE_FOUND',
1158 p_default_last_updated_by,
1159 p_default_last_update_login,
1160 p_calling_sequence);
1161 p_current_invoice_status := 'N';
1162
1163 END IF;
1164
1165 END jl_co_apxiisim_lines_folder;
1166
1167 -- Parameters to ar_business_rules changed for TCA model, so have to modify the
1168 --parameters passed to the procedures within ar_business_rules
1169 /*
1170 PROCEDURE ar_business_rules
1171 (p_calling_program_name IN VARCHAR2,
1172 p_sob_id IN NUMBER,
1173 p_row_id IN VARCHAR2,
1174 p_customer_name IN VARCHAR2,
1175 p_customer_number IN NUMBER,
1176 p_jgzz_fiscal_code IN VARCHAR2,
1177 p_generate_customer_number IN VARCHAR2,
1178 p_orig_system_customer_ref IN VARCHAR2,
1179 p_insert_update_flag IN VARCHAR2,
1180 p_request_id IN NUMBER,
1181 p_global_attribute_category IN VARCHAR2,
1182 p_global_attribute1 IN VARCHAR2,
1183 p_global_attribute2 IN VARCHAR2,
1184 p_global_attribute3 IN VARCHAR2,
1185 p_global_attribute4 IN VARCHAR2,
1186 p_global_attribute5 IN VARCHAR2,
1187 p_global_attribute6 IN VARCHAR2,
1188 p_global_attribute7 IN VARCHAR2,
1189 p_global_attribute8 IN VARCHAR2,
1190 p_global_attribute9 IN VARCHAR2,
1191 p_global_attribute10 IN VARCHAR2,
1192 p_global_attribute11 IN VARCHAR2,
1193 p_global_attribute12 IN VARCHAR2,
1194 p_global_attribute13 IN VARCHAR2,
1195 p_global_attribute14 IN VARCHAR2,
1196 p_global_attribute15 IN VARCHAR2,
1197 p_global_attribute16 IN VARCHAR2,
1198 p_global_attribute17 IN VARCHAR2,
1199 p_global_attribute18 IN VARCHAR2,
1200 p_global_attribute19 IN VARCHAR2,
1201 p_global_attribute20 IN VARCHAR2,
1202 p_current_record_status OUT NOCOPY VARCHAR2) IS
1203
1204
1205 l_current_record_status VARCHAR2(1) := 'S';
1206
1207 BEGIN
1208
1209 ----------------------------- DEBUG INFORMATION ------------------------------
1210 IF PG_DEBUG = 'Y' THEN
1211 arp_util_tax.debug('ar_business_rules: ' || 'Check ar business rules');
1212 END IF;
1213 ------------------------------------------------------------------------------
1214
1215 ------------------------------------------------------------------------------
1216 -- Global Flexfield Validation
1217 ------------------------------------------------------------------------------
1218 -- You can add your own validation code for your global flexfields.
1219 --
1220 -- Form Name: ARXCUDCI
1221 ------------------------------------------------------------------------------
1222 -- Header Level Validation - Block Name: CUST
1223 ------------------------------------------------------------------------------
1224 -- 1-1. JL.CL.ARXCUDCI.CUSTOMERS
1225 -- 1-2. JL.CO.ARXCUDCI.CUSTOMERS
1226 -- 1-3. JL.AR.ARXCUDCI.CUSTOMERS
1227 -------------------------------------------------------------------------------
1228
1229 -- 1-1. JL.CL.ARXCUDCI.RA_CUSTOMERS
1230 --
1231
1232 IF (p_global_attribute_category = 'JL.CL.ARXCUDCI.CUSTOMERS') THEN
1233 jl_cl_arxcudci_customers(
1234 p_calling_program_name,
1235 p_sob_id,
1236 p_row_id,
1237 p_customer_name,
1238 p_customer_number,
1239 p_jgzz_fiscal_code,
1240 p_generate_customer_number,
1241 p_orig_system_customer_ref,
1242 p_insert_update_flag,
1243 p_request_id,
1244 p_global_attribute_category,
1245 p_global_attribute1,
1246 p_global_attribute2,
1247 p_global_attribute3,
1248 p_global_attribute4,
1249 p_global_attribute5,
1250 p_global_attribute6,
1251 p_global_attribute7,
1252 p_global_attribute8,
1253 p_global_attribute9,
1254 p_global_attribute10,
1255 p_global_attribute11,
1256 p_global_attribute12,
1257 p_global_attribute13,
1258 p_global_attribute14,
1259 p_global_attribute15,
1260 p_global_attribute16,
1261 p_global_attribute17,
1262 p_global_attribute18,
1263 p_global_attribute19,
1264 p_global_attribute20,
1265 l_current_record_status);
1266
1267 --
1268 -- 1-2. JL.CO.ARXCUDCI.RA_CUSTOMERS
1269 --
1270 ELSIF (p_global_attribute_category = 'JL.CO.ARXCUDCI.CUSTOMERS') THEN
1271 jl_co_arxcudci_customers(
1272 p_calling_program_name,
1273 p_sob_id,
1274 p_row_id,
1275 p_customer_name,
1276 p_customer_number,
1277 p_jgzz_fiscal_code,
1278 p_generate_customer_number,
1279 p_orig_system_customer_ref,
1280 p_insert_update_flag,
1281 p_request_id,
1282 p_global_attribute_category,
1283 p_global_attribute1,
1284 p_global_attribute2,
1285 p_global_attribute3,
1286 p_global_attribute4,
1287 p_global_attribute5,
1288 p_global_attribute6,
1289 p_global_attribute7,
1290 p_global_attribute8,
1291 p_global_attribute9,
1292 p_global_attribute10,
1293 p_global_attribute11,
1294 p_global_attribute12,
1295 p_global_attribute13,
1296 p_global_attribute14,
1297 p_global_attribute15,
1298 p_global_attribute16,
1299 p_global_attribute17,
1300 p_global_attribute18,
1301 p_global_attribute19,
1302 p_global_attribute20,
1303 l_current_record_status);
1304
1305 --
1306 -- 1-3. JL.AR.ARXCUDCI.RA_CUSTOMERS
1307 --
1308 ELSIF (p_global_attribute_category = 'JL.AR.ARXCUDCI.CUSTOMERS') THEN
1309 jl_ar_arxcudci_customers(
1310 p_calling_program_name,
1311 p_sob_id,
1312 p_row_id,
1313 p_customer_name,
1314 p_customer_number,
1315 p_jgzz_fiscal_code,
1316 p_generate_customer_number,
1317 p_orig_system_customer_ref,
1318 p_insert_update_flag,
1319 p_request_id,
1320 p_global_attribute_category,
1321 p_global_attribute1,
1322 p_global_attribute2,
1323 p_global_attribute3,
1324 p_global_attribute4,
1325 p_global_attribute5,
1326 p_global_attribute6,
1327 p_global_attribute7,
1328 p_global_attribute8,
1329 p_global_attribute9,
1330 p_global_attribute10,
1331 p_global_attribute11,
1332 p_global_attribute12,
1333 p_global_attribute13,
1334 p_global_attribute14,
1335 p_global_attribute15,
1336 p_global_attribute16,
1337 p_global_attribute17,
1338 p_global_attribute18,
1339 p_global_attribute19,
1340 p_global_attribute20,
1341 l_current_record_status);
1342 ELSIF (p_global_attribute_category = 'JL.BR.ARXCUDCI.Additional') THEN
1343 l_current_record_status := 'S';
1344 END IF;
1345 p_current_record_status := l_current_record_status;
1346 EXCEPTION
1347 WHEN OTHERS THEN
1348 IF PG_DEBUG = 'Y' THEN
1349 arp_util_tax.debug('Exception in JL_INTERFACE_VAL.AR_BUSINESS_RULES()');
1350 arp_util_tax.debug('ar_business_rules: ' || SQLERRM);
1351 ELSE
1352 NULL;
1353 END IF;
1354 END ar_business_rules;
1355
1356 */
1357
1358 -- Modification to the passing of parameters to ar_business_rules for TCA model
1359
1360 PROCEDURE ar_business_rules
1361 (p_int_table_name IN VARCHAR2,
1362 p_glob_attr_set1 IN jg_globe_flex_val_shared.GdfRec,
1363 p_glob_attr_set2 IN jg_globe_flex_val_shared.GdfRec,
1364 p_glob_attr_set3 IN jg_globe_flex_val_shared.GdfRec,
1365 p_misc_prod_arg IN jg_globe_flex_val_shared.GenRec,
1366 p_current_record_status OUT NOCOPY VARCHAR2) IS
1367
1368 l_current_record_status VARCHAR2(1); -- := 'S';
1369 l_ou_id NUMBER;
1370
1371 PG_DEBUG varchar2(1); -- := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
1372
1373 BEGIN
1374 l_current_record_status := 'S';
1375 PG_DEBUG := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
1376 ----------------------------- DEBUG INFORMATION ------------------------------
1377 IF PG_DEBUG = 'Y' THEN
1378 arp_util_tax.debug('ar_business_rules: ' || 'Check ar business rules');
1379 END IF;
1380 ------------------------------------------------------------------------------
1381
1382 -- Call to validate the address gdfs
1383
1384 IF p_int_table_name = 'CUSTOMER' THEN
1385
1386 --fnd_profile.get('ORG_ID',l_ou_id);
1387
1388 --Bug 4499004
1389 --commented out above line and getting org id from fnd concurrents
1390 SELECT org_id into l_ou_id FROM fnd_concurrent_requests
1391 WHERE request_id = fnd_global.conc_request_id ;
1392
1393 fnd_request.set_org_id(l_ou_id);
1394
1395 IF jg_zz_shared_pkg.get_country(l_ou_id, NULL,null) IN ('AR','BR','CO','CL') THEN --Bug 2354736
1396
1397 IF p_glob_attr_set1.global_attribute_category IN
1398 ('JL.AR.ARXCUDCI.CUSTOMERS' ,
1399 'JL.CL.ARXCUDCI.CUSTOMERS' ,
1400 'JL.CO.ARXCUDCI.CUSTOMERS') THEN
1401 jl_zz_arxcudci_cust_txid (p_glob_attr_set1,
1402 p_glob_attr_set2,
1403 p_glob_attr_set3,
1404 p_misc_prod_arg,
1405 l_current_record_status);
1406 END IF;
1407
1408 IF p_glob_attr_set2.global_attribute_category IN
1409 ('JL.AR.ARXCUDCI.Additional' ,
1410 'JL.BR.ARXCUDCI.Additional' ,
1411 'JL.CO.ARXCUDCI.Additional') THEN
1412 jl_zz_ar_tx_arxcudci_address (p_glob_attr_set1,
1413 p_glob_attr_set2,
1414 p_glob_attr_set3,
1415 p_misc_prod_arg,
1416 l_current_record_status);
1417 END IF;
1418
1419 END IF;
1420
1421 IF jg_zz_shared_pkg.get_country(l_ou_id, NULL) = 'BR' THEN --Bug 2354736
1422
1423 IF p_glob_attr_set2.global_attribute_category =
1424 'JL.BR.ARXCUDCI.Additional' THEN
1425 jl_br_arxcudci_additional (p_glob_attr_set1,
1426 p_glob_attr_set2,
1427 p_glob_attr_set3,
1428 p_misc_prod_arg,
1429 l_current_record_status);
1430 END IF;
1431
1432 END IF;
1433
1434 -- Call to validate the profile gdfs
1435
1436 ELSIF (p_int_table_name) = 'PROFILE' THEN
1437
1438 --fnd_profile.get('ORG_ID',l_ou_id);
1439 --Bug 4499004
1440 --commented out above line and getting org id from fnd concurrents
1441 SELECT org_id into l_ou_id FROM fnd_concurrent_requests
1442 WHERE request_id = fnd_global.conc_request_id ;
1443
1444 fnd_request.set_org_id(l_ou_id);
1445
1446 IF jg_zz_shared_pkg.get_country(l_ou_id, NULL) = 'BR' THEN
1447
1448 IF p_glob_attr_set2.global_attribute_category =
1449 'JL.BR.ARXCUDCI.Additional Info' THEN
1450 jl_br_customer_profiles (p_glob_attr_set1,
1451 p_glob_attr_set2,
1452 p_glob_attr_set3,
1453 p_misc_prod_arg,
1454 l_current_record_status);
1455 END IF;
1456
1457 END IF;
1458
1459 END IF;
1460
1461 p_current_record_status := l_current_record_status;
1462
1463 EXCEPTION
1464 WHEN OTHERS THEN
1465 IF PG_DEBUG = 'Y' THEN
1466 arp_util_tax.debug('Exception in JL_INTERFACE_VAL.AR_BUSINESS_RULES()');
1467 arp_util_tax.debug('ar_business_rules: ' || SQLERRM);
1468 ELSE
1469 NULL;
1470 END IF;
1471 END ar_business_rules;
1472
1473
1474 PROCEDURE jl_zz_arxcudci_cust_txid(
1475 p_glob_attr_set1 IN jg_globe_flex_val_shared.GdfRec,
1476 p_glob_attr_set2 IN jg_globe_flex_val_shared.GdfRec,
1477 p_glob_attr_set3 IN jg_globe_flex_val_shared.GdfRec,
1478 p_misc_prod_arg IN jg_globe_flex_val_shared.GenRec,
1479 p_record_status OUT NOCOPY VARCHAR2) IS
1480
1481 l_record_status VARCHAR2(1); -- := 'S';
1482
1483 BEGIN
1484 l_record_status := 'S';
1485
1486 IF p_glob_attr_set1.global_attribute_category =
1487 'JL.CL.ARXCUDCI.CUSTOMERS' THEN
1488 jl_cl_arxcudci_customers(p_glob_attr_set1,
1489 p_glob_attr_set2,
1490 p_glob_attr_set3,
1491 p_misc_prod_arg,
1492 l_record_status);
1493 ELSIF p_glob_attr_set1.global_attribute_category =
1494 'JL.CO.ARXCUDCI.CUSTOMERS' THEN
1495 jl_co_arxcudci_customers(p_glob_attr_set1,
1496 p_glob_attr_set2,
1497 p_glob_attr_set3,
1498 p_misc_prod_arg,
1499 l_record_status);
1500 ELSIF p_glob_attr_set1.global_attribute_category =
1501 'JL.AR.ARXCUDCI.CUSTOMERS' THEN
1502 jl_ar_arxcudci_customers(p_glob_attr_set1,
1503 p_glob_attr_set2,
1504 p_glob_attr_set3,
1505 p_misc_prod_arg,
1506 l_record_status);
1507 END IF;
1508
1509 p_record_status := l_record_status;
1510
1511 END jl_zz_arxcudci_cust_txid;
1512 -----------------------------------------------------------------------------------
1513 -- 1-1. JL_CL_ARXCUDCI_RA_CUSTOMERS()
1514 ----------------------------------------------------------------------------------
1515 -- Following segments are defined for Chile Customer Interface:
1516 ----------------------------------------------------------------------------------
1517 -- No. Name Column Value Set Req
1518 ----- --------------------------- ------------------ ----------------------- ----
1519 -- 1 Primary ID Type GLOBAL_ATTRIBUTE10 JLZZ_ORIGIN YES
1520 -- 2 Primary ID Validation Digit GLOBAL_ATTRIBUTE12 JLCL_TAXID_VAL_DIGIT
1521
1522 -----------------------------------------------------------------------------------
1523 PROCEDURE jl_cl_arxcudci_customers(
1524 p_glob_attr_set1 IN jg_globe_flex_val_shared.GdfRec,
1525 p_glob_attr_set2 IN jg_globe_flex_val_shared.GdfRec,
1526 p_glob_attr_set3 IN jg_globe_flex_val_shared.GdfRec,
1527 p_misc_prod_arg IN jg_globe_flex_val_shared.GenRec,
1528 p_record_status OUT NOCOPY VARCHAR2) IS
1529
1530 l_record_status VARCHAR2(1); -- := 'S';
1531 l_taxid_record_status VARCHAR2(1); -- := 'S';
1532 l_mesg_code VARCHAR2(50);
1533 l_taxid_mesg_code VARCHAR2(50);
1534 l_row_id ROWID; -- := p_misc_prod_arg.core_prod_arg2;
1535 BEGIN
1536 l_record_status := 'S';
1537 l_taxid_record_status := 'S';
1538 l_row_id := p_misc_prod_arg.core_prod_arg2;
1539 -- Checking for Domestic or Foreign Customer
1540
1541 IF (p_glob_attr_set1.global_attribute10 NOT IN
1542 ('DOMESTIC_ORIGIN','FOREIGN_ORIGIN')) OR
1543 (p_glob_attr_set1.global_attribute10 IS NULL) THEN
1544
1545 -- Return the record status and the error message code
1546 -- (j2 -Invalid Value in Global Attribute10) to update
1547 -- INTERFACE_STATUS field in RA_CUSTOMERS_INTERFACE
1548 -- with error code
1549
1550 l_mesg_code := 'j2,';
1551 l_record_status := 'E';
1552
1553 END IF;
1554
1555 IF (p_misc_prod_arg.core_prod_arg5 is not null) THEN
1556 IF (p_glob_attr_set1.global_attribute10 = 'DOMESTIC_ORIGIN' AND
1557 p_glob_attr_set1.global_attribute12 IS NULL) OR
1558 (p_glob_attr_set1.global_attribute12 IS NOT NULL AND
1559 ((lengthb(p_glob_attr_set1.global_attribute12)<>1) OR
1560 (p_glob_attr_set1.global_attribute12 not in
1561 ('0','1','2','3','4','5','6','7','8','9','K') ))) THEN
1562
1563 -- Return the record status and the error message code
1564 -- (j4 -Invalid Value in Global Attribute12) to update
1565 -- INTERFACE_STATUS field in RA_CUSTOMERS_INTERFACE
1566 -- with error code
1567
1568 -- Checking for global attribute12 has a value then check if it is
1569 -- between 0 - 9 or K.
1570 -- The length allowed for validation digit is 1
1571
1572 l_mesg_code := l_mesg_code||'j4,';
1573 l_record_status := 'E';
1574 END IF;
1575 END IF; -- End if Tax ID is not null
1576
1577 IF (p_glob_attr_set1.global_attribute1 IS NOT NULL) OR
1578 (p_glob_attr_set1.global_attribute2 IS NOT NULL) OR
1579 (p_glob_attr_set1.global_attribute3 IS NOT NULL) OR
1580 (p_glob_attr_set1.global_attribute4 IS NOT NULL) OR
1581 (p_glob_attr_set1.global_attribute5 IS NOT NULL) OR
1582 (p_glob_attr_set1.global_attribute6 IS NOT NULL) OR
1583 (p_glob_attr_set1.global_attribute7 IS NOT NULL) OR
1584 (p_glob_attr_set1.global_attribute8 IS NOT NULL) OR
1585 (p_glob_attr_set1.global_attribute9 IS NOT NULL) OR
1586 (p_glob_attr_set1.global_attribute11 IS NOT NULL) OR
1587 (p_glob_attr_set1.global_attribute13 IS NOT NULL) OR
1588 (p_glob_attr_set1.global_attribute14 IS NOT NULL) OR
1589 (p_glob_attr_set1.global_attribute15 IS NOT NULL) OR
1590 (p_glob_attr_set1.global_attribute16 IS NOT NULL) OR
1591 (p_glob_attr_set1.global_attribute17 IS NOT NULL) OR
1592 (p_glob_attr_set1.global_attribute18 IS NOT NULL) OR
1593 (p_glob_attr_set1.global_attribute19 IS NOT NULL) OR
1594 (p_glob_attr_set1.global_attribute20 IS NOT NULL) THEN
1595
1596 l_mesg_code := l_mesg_code||'i1,';
1597 l_record_status := 'E';
1598
1599 END IF;
1600
1601 IF (l_record_status = 'E') THEN
1602 jg_globe_flex_val_shared.update_interface_status (l_row_id,
1603 'RA_CUSTOMERS_INTERFACE',
1604 l_mesg_code,
1605 l_record_status);
1606 END IF;
1607
1608 -- Checking for Tax ID is not null
1609
1610 IF l_record_status = 'S' THEN
1611 IF (p_misc_prod_arg.core_prod_arg5 is not null) THEN
1612
1613 jl_zz_taxid_customers(
1614 'CL',
1615 -- 'RACUST',
1616 p_misc_prod_arg.core_prod_arg11,
1617 p_misc_prod_arg.core_prod_arg2,
1618 p_misc_prod_arg.core_prod_arg3,
1619 p_misc_prod_arg.core_prod_arg4,
1620 p_misc_prod_arg.core_prod_arg5,
1621 -- p_generate_customer_number,
1622 p_misc_prod_arg.core_prod_arg6,
1623 p_misc_prod_arg.core_prod_arg7,
1624 p_misc_prod_arg.core_prod_arg8,
1625 p_misc_prod_arg.core_prod_arg9,
1626 p_glob_attr_set1.global_attribute_category,
1627 NULL,
1628 p_glob_attr_set1.global_attribute10,
1629 p_glob_attr_set1.global_attribute12,
1630 l_taxid_mesg_code,
1631 l_taxid_record_status);
1632 END IF; -- End IF taxpayer ID is not null.
1633 END IF; -- End IF l_record_status = 'S'
1634
1635 IF (l_taxid_record_status = 'E') THEN
1636 jg_globe_flex_val_shared.update_interface_status (l_row_id,
1637 'RA_CUSTOMERS_INTERFACE',
1638 l_taxid_mesg_code,
1639 l_taxid_record_status);
1640 ELSIF (l_taxid_record_status = 'W') THEN
1641 jg_globe_flex_val_shared.update_interface_status (l_row_id,
1642 'RA_CUSTOMERS_INTERFACE',
1643 l_taxid_mesg_code,
1644 l_taxid_record_status);
1645 END IF;
1646
1647 IF l_record_status = 'E' OR l_taxid_record_status = 'E' THEN
1648 p_record_status := 'E';
1649 ELSE
1650 p_record_status := 'S';
1651 END IF;
1652
1653 END jl_cl_arxcudci_customers;
1654
1655 -------------------------------------------------------------------------------------
1656 -- 1-2. JL_CO_ARXCUDCI_RA_CUSTOMERS()
1657 -------------------------------------------------------------------------------------
1658 -- Following segments are defined for Colombia Customer Interface:
1659
1660 -------------------------------------------------------------------------------------
1661 -- No. Name Column Value Set Req.
1662 -- --- --------------------------- ------------------ -------------------- -----
1663 -- 1 Primary ID Type GLOBAL_ATTRIBUTE10 JL_CO_TAXID_TYPE YES
1664 -- 2 Primary ID Validation Digit GLOBAL_ATTRIBUTE12 JLZZ_TAXID_VAL_DIGIT
1665
1666 -------------------------------------------------------------------------------------
1667
1668 PROCEDURE jl_co_arxcudci_customers(
1669 p_glob_attr_set1 IN jg_globe_flex_val_shared.GdfRec,
1670 p_glob_attr_set2 IN jg_globe_flex_val_shared.GdfRec,
1671 p_glob_attr_set3 IN jg_globe_flex_val_shared.GdfRec,
1672 p_misc_prod_arg IN jg_globe_flex_val_shared.GenRec,
1673 p_record_status OUT NOCOPY VARCHAR2) IS
1674
1675 l_record_status VARCHAR2(1); -- := 'S';
1676 l_taxid_record_status VARCHAR2(1); -- := 'S';
1677 l_mesg_code VARCHAR2(50);
1678 l_taxid_mesg_code VARCHAR2(50);
1679 l_row_id ROWID; --:= p_misc_prod_arg.core_prod_arg2;
1680 BEGIN
1681 l_record_status := 'S';
1682 l_taxid_record_status := 'S';
1683 l_row_id := p_misc_prod_arg.core_prod_arg2;
1684
1685 -- Checking for Natural people, Foreign and Legal Entity
1686
1687 IF (p_glob_attr_set1.global_attribute10 NOT IN
1688 ('INDIVIDUAL','LEGAL_ENTITY','FOREIGN_ENTITY')) OR
1689 (p_glob_attr_set1.global_attribute10 IS NULL) THEN
1690
1691 -- Return the record status and the error message code
1692 -- (j2 -Invalid Value in Global Attribute10)
1693 -- to update INTERFACE_STATUS field in RA_CUSTOMERS_INTERFACE
1694 -- with error code
1695
1696 l_mesg_code := 'j2,';
1697 l_record_status := 'E';
1698
1699 END IF;
1700
1701 -- Checking for Legal and Global attribute12 is NULL
1702
1703 IF (p_misc_prod_arg.core_prod_arg5 is not null) THEN
1704 IF (p_glob_attr_set1.global_attribute10 = 'LEGAL_ENTITY' AND
1705 p_glob_attr_set1.global_attribute12 IS NULL) OR
1706 (p_glob_attr_set1.global_attribute12 IS NOT NULL AND
1707 ((lengthb(p_glob_attr_set1.global_attribute12)<>1) OR
1708 (p_glob_attr_set1.global_attribute12 not in
1709 ('0','1','2','3','4','5','6','7','8','9')))) THEN
1710
1711 -- Return the record status and the error message code
1712 -- (j4 -Invalid Value in Global Attribute12) to update
1713 -- INTERFACE_STATUS field in RA_CUSTOMERS_INTERFACE
1714 -- with error code
1715
1716 -- Checking for global attribute12 has a value then check if it is
1717 -- between 0 - 9 or K.
1718 -- The length allowed for validation digit is 1
1719
1720 l_mesg_code := l_mesg_code||'j4,';
1721 l_record_status := 'E';
1722 END IF;
1723 END IF;
1724
1725 IF (p_glob_attr_set1.global_attribute1 IS NOT NULL) OR
1726 (p_glob_attr_set1.global_attribute2 IS NOT NULL) OR
1727 (p_glob_attr_set1.global_attribute3 IS NOT NULL) OR
1728 (p_glob_attr_set1.global_attribute4 IS NOT NULL) OR
1729 (p_glob_attr_set1.global_attribute5 IS NOT NULL) OR
1730 (p_glob_attr_set1.global_attribute6 IS NOT NULL) OR
1731 (p_glob_attr_set1.global_attribute7 IS NOT NULL) OR
1732 (p_glob_attr_set1.global_attribute8 IS NOT NULL) OR
1733 (p_glob_attr_set1.global_attribute9 IS NOT NULL) OR
1734 (p_glob_attr_set1.global_attribute11 IS NOT NULL) OR
1735 (p_glob_attr_set1.global_attribute13 IS NOT NULL) OR
1736 (p_glob_attr_set1.global_attribute14 IS NOT NULL) OR
1737 (p_glob_attr_set1.global_attribute15 IS NOT NULL) OR
1738 (p_glob_attr_set1.global_attribute16 IS NOT NULL) OR
1739 (p_glob_attr_set1.global_attribute17 IS NOT NULL) OR
1740 (p_glob_attr_set1.global_attribute18 IS NOT NULL) OR
1741 (p_glob_attr_set1.global_attribute19 IS NOT NULL) OR
1742 (p_glob_attr_set1.global_attribute20 IS NOT NULL) THEN
1743
1744 l_mesg_code := l_mesg_code||'i1,';
1745 l_record_status := 'E';
1746 END IF;
1747
1748 IF (l_record_status = 'E') THEN
1749 jg_globe_flex_val_shared.update_interface_status (l_row_id,
1750 'RA_CUSTOMERS_INTERFACE',
1751 l_mesg_code,
1752 l_record_status);
1753 END IF;
1754
1755 IF l_record_status = 'S' THEN
1756 IF (p_misc_prod_arg.core_prod_arg5 is not null) THEN
1757 jl_zz_taxid_customers(
1758 'CO',
1759 --'RACUST',
1760 p_misc_prod_arg.core_prod_arg11,
1761 p_misc_prod_arg.core_prod_arg2 ,
1762 p_misc_prod_arg.core_prod_arg3 ,
1763 p_misc_prod_arg.core_prod_arg4 ,
1764 p_misc_prod_arg.core_prod_arg5 ,
1765 --p_generate_customer_number,
1766 p_misc_prod_arg.core_prod_arg6 ,
1767 p_misc_prod_arg.core_prod_arg7 ,
1768 p_misc_prod_arg.core_prod_arg8 ,
1769 p_misc_prod_arg.core_prod_arg9 ,
1770 p_glob_attr_set1.global_attribute_category,
1771 NULL,
1772 p_glob_attr_set1.global_attribute10,
1773 p_glob_attr_set1.global_attribute12,
1774 l_taxid_mesg_code,
1775 l_taxid_record_status);
1776 END IF; -- End IF taxpayer ID is not null.
1777 END IF; -- End IF l_record_status = 'S'
1778
1779
1780 IF (l_taxid_record_status = 'E') THEN
1781 jg_globe_flex_val_shared.update_interface_status (l_row_id,
1782 'RA_CUSTOMERS_INTERFACE',
1783 l_taxid_mesg_code,
1784 l_taxid_record_status);
1785
1786 ELSIF (l_taxid_record_status = 'W') THEN
1787 jg_globe_flex_val_shared.update_interface_status (l_row_id,
1788 'RA_CUSTOMERS_INTERFACE',
1789 l_taxid_mesg_code,
1790 l_taxid_record_status);
1791 END IF;
1792
1793 IF l_record_status = 'E' OR l_taxid_record_status = 'E' THEN
1794 p_record_status := 'E';
1795 ELSE
1796 p_record_status := 'S';
1797 END IF;
1798
1799
1800 END jl_co_arxcudci_customers;
1801
1802 -----------------------------------------------------------------------------------
1803 -- 1-3. JL.AR.ARXCUDCI.RA_CUSTOMERS()
1804 -----------------------------------------------------------------------------------
1805 -- Following segments are defined for Argentina Customer Interface:
1806
1807 ------------------------------------------------------------------------------------
1808 -- No. Name Column Value Set Req.
1809 -- --- --------------------------- ------------------ -------------------- ----
1810 -- 1 Origin GLOBAL_ATTRIBUTE9 JLZZ_ORIGIN YES
1811 -- 2 Primary ID Type GLOBAL_ATTRIBUTE10 JLAR_TAXID_TYPE YES
1812 -- 3 Primary ID Validation Digit GLOBAL_ATTRIBUTE12 JLZZ_TAXID_VAL_DIGIT
1813
1814 -------------------------------------------------------------------------------
1815 PROCEDURE jl_ar_arxcudci_customers(
1816 p_glob_attr_set1 IN jg_globe_flex_val_shared.GdfRec,
1817 p_glob_attr_set2 IN jg_globe_flex_val_shared.GdfRec,
1818 p_glob_attr_set3 IN jg_globe_flex_val_shared.GdfRec,
1819 p_misc_prod_arg IN jg_globe_flex_val_shared.GenRec,
1820 p_record_status OUT NOCOPY VARCHAR2) IS
1821
1822 l_lookup_code VARCHAR2(2);
1823 l_record_status VARCHAR2(1); --:= 'S';
1824 l_taxid_record_status VARCHAR2(1); --:= 'S';
1825 l_mesg_code VARCHAR2(50);
1826 l_taxid_mesg_code VARCHAR2(50);
1827 l_row_id ROWID; --:= p_misc_prod_arg.core_prod_arg2;
1828
1829 BEGIN
1830 l_record_status := 'S';
1831 l_taxid_record_status := 'S';
1832 l_row_id := p_misc_prod_arg.core_prod_arg2;
1833
1834 IF (p_glob_attr_set1.global_attribute9 NOT IN
1835 ('DOMESTIC_ORIGIN','FOREIGN_ORIGIN')) OR
1836 (p_glob_attr_set1.global_attribute9 IS NULL) THEN
1837
1838 -- Return the record status and the error message code
1839 -- (j1 -Invalid Value in Global Attribute9)
1840 -- to update INTERFACE_STATUS field in RA_CUSTOMERS_INTERFACE
1841 -- with error code
1842
1843 l_mesg_code := 'j1,';
1844 l_record_status := 'E';
1845 END IF;
1846
1847 -- Validate for p_global_attribute10
1848
1849 BEGIN
1850 SELECT 'x'
1851 INTO l_lookup_code
1852 FROM dual
1853 WHERE exists (select lookup_code
1854 from fnd_lookups
1855 where lookup_type = 'JLAR_TAXID_TYPE'
1856 and lookup_code = p_glob_attr_set1.global_attribute10);
1857 EXCEPTION
1858 WHEN NO_DATA_FOUND THEN
1859 -- Return the record status and the error message code
1860 -- (j2 -Invalid Value in Global Attribute10) to update
1861 -- INTERFACE_STATUS field in RA_CUSTOMERS_INTERFACE
1862 -- with error code
1863
1864 l_mesg_code := l_mesg_code||'j2,';
1865 l_record_status := 'E';
1866
1867 END; -- End Begin Validation for p_global_attribute10
1868
1869 -- Checking for '82' and '80'(Domestic CUIT and CUIL) and Global attribute12
1870 -- is NULL
1871
1872 IF (p_misc_prod_arg.core_prod_arg5 is not null) THEN
1873 IF (p_glob_attr_set1.global_attribute10 IN ('80','82') AND
1874 p_glob_attr_set1.global_attribute12 is NULL) OR
1875 (p_glob_attr_set1.global_attribute12 IS NOT NULL AND
1876 ((lengthb(p_glob_attr_set1.global_attribute12)<>1) OR
1877 (p_glob_attr_set1.global_attribute12 not in
1878 ('0','1','2','3','4','5','6','7','8','9')))) THEN
1879
1880 -- Return the record status and the error message code
1881 -- (j4 -Invalid Value in Global Attribute12) to update
1882 -- INTERFACE_STATUS field in RA_CUSTOMERS_INTERFACE
1883 -- with error code
1884
1885 l_mesg_code := l_mesg_code||'j4,';
1886 l_record_status := 'E';
1887
1888 END IF;
1889 END IF;
1890
1891 IF (p_glob_attr_set1.global_attribute1 IS NOT NULL) OR
1892 (p_glob_attr_set1.global_attribute2 IS NOT NULL) OR
1893 (p_glob_attr_set1.global_attribute3 IS NOT NULL) OR
1894 (p_glob_attr_set1.global_attribute4 IS NOT NULL) OR
1895 (p_glob_attr_set1.global_attribute5 IS NOT NULL) OR
1896 (p_glob_attr_set1.global_attribute6 IS NOT NULL) OR
1897 (p_glob_attr_set1.global_attribute7 IS NOT NULL) OR
1898 (p_glob_attr_set1.global_attribute8 IS NOT NULL) OR
1899 (p_glob_attr_set1.global_attribute11 IS NOT NULL) OR
1900 (p_glob_attr_set1.global_attribute13 IS NOT NULL) OR
1901 (p_glob_attr_set1.global_attribute14 IS NOT NULL) OR
1902 (p_glob_attr_set1.global_attribute15 IS NOT NULL) OR
1903 (p_glob_attr_set1.global_attribute16 IS NOT NULL) OR
1904 (p_glob_attr_set1.global_attribute17 IS NOT NULL) OR
1905 (p_glob_attr_set1.global_attribute18 IS NOT NULL) OR
1906 (p_glob_attr_set1.global_attribute19 IS NOT NULL) OR
1907 (p_glob_attr_set1.global_attribute20 IS NOT NULL) THEN
1908
1909 l_mesg_code := l_mesg_code||'i1,';
1910 l_record_status := 'E';
1911
1912 END IF;
1913
1914 IF (l_record_status = 'E') THEN
1915 jg_globe_flex_val_shared.update_interface_status (l_row_id,
1916 'RA_CUSTOMERS_INTERFACE',
1917 l_mesg_code,
1918 l_record_status);
1919 END IF;
1920
1921 IF l_record_status = 'S' THEN
1922 IF (p_misc_prod_arg.core_prod_arg5 is not null) THEN
1923 jl_zz_taxid_customers(
1924 'AR',
1925 --'RACUST',
1926 p_misc_prod_arg.core_prod_arg11,
1927 p_misc_prod_arg.core_prod_arg2,
1928 p_misc_prod_arg.core_prod_arg3,
1929 p_misc_prod_arg.core_prod_arg4,
1930 p_misc_prod_arg.core_prod_arg5,
1931 --p_generate_customer_number,
1932 p_misc_prod_arg.core_prod_arg6,
1933 p_misc_prod_arg.core_prod_arg7,
1934 p_misc_prod_arg.core_prod_arg8,
1935 p_misc_prod_arg.core_prod_arg9,
1936 p_glob_attr_set1.global_attribute_category,
1937 p_glob_attr_set1.global_attribute9,
1938 p_glob_attr_set1.global_attribute10,
1939 p_glob_attr_set1.global_attribute12,
1940 l_taxid_mesg_code,
1941 l_taxid_record_status);
1942 END IF; -- End IF taxpayer ID is not null.
1943 END IF; -- End IF l_record_status = 'S'
1944
1945 IF (l_taxid_record_status = 'E') THEN
1946 jg_globe_flex_val_shared.update_interface_status (l_row_id,
1947 'RA_CUSTOMERS_INTERFACE',
1948 l_taxid_mesg_code,
1949 l_taxid_record_status);
1950 ELSIF (l_taxid_record_status = 'W') THEN
1951 jg_globe_flex_val_shared.update_interface_status (l_row_id,
1952 'RA_CUSTOMERS_INTERFACE',
1953 l_taxid_mesg_code,
1954 l_taxid_record_status);
1955 END IF;
1956
1957 IF l_record_status = 'E' OR l_taxid_record_status = 'E' THEN
1958 p_record_status := 'E';
1959 ELSE
1960 p_record_status := 'S';
1961 END IF;
1962
1963 END jl_ar_arxcudci_customers;
1964
1965 ---------------------------------------------------------------------------
1966 -- JL_ZZ_TAXID_RA_CUSTOMERS():
1967 -- This procedure validates Bussines Rules for each country and
1968 -- call JG_TAXID_VAL_PKG package
1969 --------------------------------------------------------------------------
1970 PROCEDURE jl_zz_taxid_customers(
1971 p_country_code IN VARCHAR2,
1972 p_calling_program_name IN VARCHAR2,
1973 p_row_id IN VARCHAR2,
1974 p_customer_name IN VARCHAR2,
1975 p_customer_number IN VARCHAR2,
1976 p_jgzz_fiscal_code IN VARCHAR2,
1977 p_generate_customer_number IN VARCHAR2,
1978 p_orig_system_customer_ref IN VARCHAR2,
1979 p_insert_update_flag IN VARCHAR2,
1980 p_request_id IN NUMBER,
1981 p_global_attribute_category IN VARCHAR2,
1982 p_global_attribute9 IN VARCHAR2,
1983 p_global_attribute10 IN VARCHAR2,
1984 p_global_attribute12 IN VARCHAR2,
1985 p_taxid_mesg_code OUT NOCOPY VARCHAR2,
1986 p_taxid_record_status OUT NOCOPY VARCHAR2) IS
1987
1988 l_return_ar VARCHAR2(10):=NULL;
1989 l_return_ap VARCHAR2(10):=NULL;
1990 l_return_hr VARCHAR2(10):=NULL;
1991 l_return_bk VARCHAR2(10):=NULL;
1992 l_taxid_raise_error VARCHAR2(30);
1993 l_cus_sup_num VARCHAR2(30);
1994 l_num_digits NUMBER; -- Maximum digits allowed for each country
1995 l_copy VARCHAR2(1);
1996 l_customer_id NUMBER;
1997
1998 PG_DEBUG varchar2(1);
1999
2000 BEGIN
2001 PG_DEBUG := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
2002
2003 FND_PROFILE.GET('JLZZ_TAXID_RAISE_ERROR',l_taxid_raise_error);
2004 IF p_country_code = 'CL' THEN
2005 l_taxid_raise_error := NVL(l_taxid_raise_error,'VALIDATION_TYPE_WARN');
2006 l_num_digits := 12; -- Maximum digits allowed for Chile
2007 END IF; -- End IF p_country_code = 'CL'
2008
2009 IF p_country_code = 'CO' THEN
2010 l_taxid_raise_error := NVL(l_taxid_raise_error,'VALIDATION_TYPE_ERROR');
2011 l_num_digits := 14; -- Maximum digits allowed for Colombia
2012 END IF; -- End IF p_country_code = 'CO'
2013
2014 IF p_country_code = 'AR' THEN
2015 l_taxid_raise_error := NVL(l_taxid_raise_error,'VALIDATION_TYPE_ERROR');
2016 IF (p_global_attribute10 IN ('80','82')) THEN
2017 l_num_digits:=10;
2018 ELSIF (p_global_attribute10 = '96') THEN
2019 l_num_digits:=8;
2020 END IF;
2021 END IF; -- End IF p_country_code = 'AR'
2022
2023
2024 IF p_country_code IN ('CL','CO') THEN
2025 FND_PROFILE.GET('JLZZ_COPY_CUS_SUP_NUM',l_cus_sup_num);
2026 l_cus_sup_num := NVL(l_cus_sup_num,'Y');
2027 IF p_generate_customer_number='N' AND l_cus_sup_num='Y' THEN
2028 l_copy:='Y';
2029 ELSE
2030 l_copy:='N';
2031 END IF;
2032 END IF;
2033
2034 -- Validation for CHECK_NUMERIC AND CHECK_LENGTH
2035 IF (p_country_code IN ('CL','CO')) OR
2036 (p_country_code = 'AR' and (p_global_attribute9 = 'DOMESTIC_ORIGIN' AND p_global_attribute10 IN ('80','82','96')) OR
2037 (p_global_attribute9 = 'FOREIGN_ORIGIN' AND p_global_attribute10 = '80')) THEN
2038
2039 -- Check for Numeric
2040
2041 IF JG_TAXID_VAL_PKG.CHECK_NUMERIC(p_jgzz_fiscal_code) <> 'TRUE' THEN
2042
2043 -- Return the record status and the error message code
2044 -- k3 - Taxpayer ID should be numeric) to update
2045 -- INTERFACE_STATUS field in RA_CUSTOMERS_INTERFACE
2046 -- with error code
2047
2048 p_taxid_mesg_code := 'k3,';
2049 p_taxid_record_status := 'E';
2050 return;
2051
2052 END IF;
2053
2054 -- Check maximun digits allowed.
2055
2056 IF JG_TAXID_VAL_PKG.CHECK_LENGTH(
2057 p_country_code,
2058 l_num_digits,
2059 p_jgzz_fiscal_code
2060 ) <> 'TRUE' THEN
2061 -- Return the record status and the error message code
2062 -- (k4 - Taxpayer ID exceeds maximun digits allowed)
2063 -- to update INTERFACE_STATUS field in
2064 -- RA_CUSTOMERS_INTERFACE with error code
2065
2066 p_taxid_mesg_code := 'k4,';
2067 p_taxid_record_status := 'E';
2068 return;
2069
2070 END IF;
2071 END IF; --End for validation CHECK_NUMERIC and CHECK_LENGTH
2072
2073 -- Validation for CHECK_UNIQUENESS
2074
2075 -- Checking for Customer_id in RA_CUSTOMERS table for records marked for
2076 -- Update in RA_CUSTOMERS_INTERFACE
2077
2078 IF (p_insert_update_flag = 'U') THEN
2079
2080 BEGIN
2081
2082 SELECT rc.cust_account_id INTO l_customer_id
2083 FROM hz_cust_accounts rc
2084 WHERE rc.orig_system_reference = p_orig_system_customer_ref;
2085
2086 EXCEPTION
2087 WHEN NO_DATA_FOUND THEN
2088 IF PG_DEBUG = 'Y' THEN
2089 arp_util_tax.debug('jl_zz_taxid_customers: ' || 'No data found in hz_cust_accounts table for Update');
2090 ELSE
2091 NULL;
2092 END IF;
2093 WHEN OTHERS THEN
2094 IF PG_DEBUG = 'Y' THEN
2095 arp_util_tax.debug('Exception in JL_INTERFACE_VAL.JL_ZZ_TAXID_CUSTOMERS()');
2096 arp_util_tax.debug('jl_zz_taxid_customers: ' || SQLERRM);
2097 ELSE
2098 NULL;
2099 END IF;
2100 END;
2101
2102 ELSE
2103
2104 l_customer_id := 0;
2105
2106 END IF;
2107
2108
2109 IF (p_country_code IN ('CL','CO')) OR
2110 (p_country_code = 'AR' and p_global_attribute9='DOMESTIC_ORIGIN') THEN
2111 IF JG_TAXID_VAL_PKG.CHECK_UNIQUENESS(
2112 p_country_code,
2113 p_jgzz_fiscal_code,
2114 l_customer_id,
2115 --'RACUST',
2116 p_calling_program_name,
2117 p_orig_system_customer_ref,
2118 p_customer_name,
2119 p_request_id) <> 'TRUE' THEN
2120
2121 -- Return the record status and the error message code
2122 -- (k5 - Duplicate Tax ID) to update INTERFACE_STATUS
2123 -- field in RA_CUSTOMERS_INTERFACE with error code
2124
2125 p_taxid_mesg_code := 'k5,';
2126 p_taxid_record_status := 'E';
2127 return;
2128
2129 END IF;
2130 END IF; -- End validation for CHECK_UNIQUENESS
2131
2132 -- Validation for CROSS_VALIDATE
2133
2134 IF (p_country_code = 'CL' AND p_global_attribute10 = 'DOMESTIC_ORIGIN') OR
2135 (p_country_code = 'CO') OR
2136 (p_country_code = 'AR' and p_global_attribute9 = 'DOMESTIC_ORIGIN' AND p_global_attribute10 IN ('80','82','96')) THEN
2137
2138 -- Call procedure to cross validate if Customer exists
2139 -- as a Supplier with different Tax ID and Name
2140
2141 IF p_customer_name IS NOT NULL THEN
2142
2143 JG_TAXID_VAL_PKG.CHECK_CROSS_MODULE(
2144 p_country_code,
2145 p_customer_name,
2146 p_jgzz_fiscal_code,
2147 p_global_attribute9,
2148 p_global_attribute10,
2149 p_calling_program_name,
2150 l_return_ar,
2151 l_return_ap,
2152 l_return_hr,
2153 l_return_bk);
2154 IF (l_return_ap='k6') THEN
2155
2156 -- Return the record status and the warning message
2157 -- code (k6 -Tax ID used by different Supplier)
2158 -- to update INTERFACE_STATUS field in
2159 -- RA_CUSTOMERS_INTERFACE with error code
2160
2161 IF l_taxid_raise_error= 'VALIDATION_TYPE_ERROR' THEN
2162 p_taxid_mesg_code := 'k6,';
2163 p_taxid_record_status := 'E';
2164 return;
2165 ELSE
2166 p_taxid_mesg_code := p_taxid_mesg_code||'k6,';
2167 p_taxid_record_status := 'W';
2168 END IF;
2169 ELSIF (l_return_ap='k8') THEN
2170
2171 -- Return the record status and the warning message code
2172 -- (k8 - Customer exist as Supplier with different
2173 -- Tax id) to update WARNING_TEXT field in
2174 -- RA_CUSTOMERS_INTERFACE with error code
2175
2176 IF l_taxid_raise_error='VALIDATION_TYPE_ERROR' THEN
2177 p_taxid_mesg_code := 'k8,';
2178 p_taxid_record_status := 'E';
2179 return;
2180 ELSE
2181 p_taxid_mesg_code := p_taxid_mesg_code||'k8,';
2182 p_taxid_record_status := 'W';
2183 END IF;
2184 END IF;
2185
2186 IF (l_return_hr='k7') THEN
2187
2188 -- Return the record status and the warning message
2189 -- code (k7 -Tax ID used by different Company - Company
2190 -- with different name) to update WARNING_TEXT field in
2191 -- RA_CUSTOMERS_INTERFACE with error code
2192
2193 IF l_taxid_raise_error='VALIDATION_TYPE_ERROR' THEN
2194 p_taxid_mesg_code := 'k7,';
2195 p_taxid_record_status := 'E';
2196 return;
2197 ELSE
2198 p_taxid_mesg_code := p_taxid_mesg_code||'k7,';
2199 p_taxid_record_status := 'W';
2200 END IF;
2201 ELSIF (l_return_hr='k9') THEN
2202
2203 -- Return the record status and the warning message code
2204 -- (k9 - Customer exist as Company with
2205 -- different Tax ID) to update WARNING_TEXT field in
2206 -- RA_CUSTOMERS_INTERFACE with error code
2207
2208 IF l_taxid_raise_error='VALIDATION_TYPE_ERROR' THEN
2209 p_taxid_mesg_code := 'k9,';
2210 p_taxid_record_status := 'E';
2211 return;
2212 ELSE
2213 p_taxid_mesg_code := p_taxid_mesg_code||'k9,';
2214 p_taxid_record_status := 'W';
2215 END IF;
2216 END IF;
2217
2218 IF p_country_code = 'CO' THEN -- Validations for Banks.
2219 IF (l_return_bk='l1') THEN
2220
2221 -- Return the record status and the warning message
2222 -- code (l1 - Tax ID used by different Bank)
2223 -- to update WARNING_TEXT field in
2224 -- RA_CUSTOMERS_INTERFACE with error code
2225
2226 IF l_taxid_raise_error='VALIDATION_TYPE_ERROR' THEN
2227 p_taxid_mesg_code := 'l1,';
2228 p_taxid_record_status := 'E';
2229 return;
2230 ELSE
2231 p_taxid_mesg_code := p_taxid_mesg_code||'l1,';
2232 p_taxid_record_status := 'W';
2233 END IF;
2234 ELSIF (l_return_bk='l2') THEN
2235
2236 -- Return the record status and the warning message code
2237 -- (l2 - Customer exist as a Bank with different Tax ID
2238 -- or ID Type) to update WARNING_TEXT field in
2239 -- RA_CUSTOMERS_INTERFACE with error code
2240
2241 IF l_taxid_raise_error='VALIDATION_TYPE_ERROR' THEN
2242 p_taxid_mesg_code := 'l2,';
2243 p_taxid_record_status := 'E';
2244 return;
2245 ELSE
2246 p_taxid_mesg_code := p_taxid_mesg_code||'l2,';
2247 p_taxid_record_status := 'W';
2248 END IF;
2249 END IF;
2250 END IF; -- End IF p_country_code = 'CO' for Banks
2251 END IF; -- End of IF p_customer_name IS NOT NULL
2252 END IF; -- End for Cross Module Validate
2253
2254 -- Validation algorithm for verifying the validation digit
2255
2256 IF (p_country_code = 'CL' and p_global_attribute10 = 'DOMESTIC_ORIGIN') OR
2257 (p_country_code = 'CO' and p_global_attribute10 = 'LEGAL_ENTITY') OR
2258 ((p_country_code = 'AR' and (p_global_attribute9 = 'DOMESTIC_ORIGIN' AND p_global_attribute10 IN ('80','82')) OR
2259 (p_global_attribute9 = 'FOREIGN_ORIGIN' AND p_global_attribute10 = '80')))
2260 THEN
2261 IF JG_TAXID_VAL_PKG.CHECK_ALGORITHM(
2262 p_jgzz_fiscal_code,
2263 p_country_code,
2264 p_global_attribute12
2265 ) <> 'TRUE' THEN
2266 IF (l_taxid_raise_error = 'VALIDATION_TYPE_ERROR') THEN
2267
2268 -- Return the record status and the error message code
2269 -- (k0 - Validation Routine Failed)
2270 -- to update INTERFACE_STATUS field in
2271 -- RA_CUSTOMERS_INTERFACE with error code
2272
2273 p_taxid_mesg_code := 'k0,';
2274 p_taxid_record_status := 'E';
2275 return;
2276
2277 ELSE
2278
2279 -- The record can be processed. But a Warning message
2280 -- should appear in the Log File. Update field
2281 -- WARNING_TEXT in RA_CUSTOMERS_INTERFACE with
2282 -- error code k0 - Tax ID Validation Routine Failed
2283
2284 p_taxid_mesg_code := p_taxid_mesg_code||'k0,';
2285 p_taxid_record_status := 'W';
2286
2287 END IF;
2288 END IF;
2289 END IF; -- End Validation Algorithm
2290
2291 IF (p_country_code = 'CL' AND l_copy = 'Y' AND p_customer_number IS NULL) THEN
2292
2293 IF p_global_attribute12 IS NOT NULL THEN
2294 UPDATE ra_customers_interface
2295 SET customer_number = p_jgzz_fiscal_code||'-'||p_global_attribute12
2296 WHERE rowid = p_row_id;
2297 ELSE
2298 UPDATE ra_customers_interface
2299 SET customer_number = p_jgzz_fiscal_code
2300 WHERE rowid = p_row_id;
2301 END IF;
2302 END IF;
2303
2304 IF (p_country_code = 'CO' AND l_copy = 'Y' AND p_customer_number IS NULL) THEN
2305
2306 IF (p_global_attribute10 = 'LEGAL_ENTITY') AND (p_global_attribute12 IS NOT NULL) THEN
2307 UPDATE ra_customers_interface
2308 SET customer_number = p_jgzz_fiscal_code||'-'||p_global_attribute12
2309 WHERE rowid = p_row_id;
2310 ELSE
2311 UPDATE ra_customers_interface
2312 SET customer_number = p_jgzz_fiscal_code
2313 WHERE rowid = p_row_id;
2314 END IF;
2315
2316
2317 END IF;
2318
2319 IF p_taxid_record_status IS NULL THEN
2320
2321 IF PG_DEBUG = 'Y' THEN
2322 arp_util_tax.debug('jl_zz_taxid_customers: ' || 'In if record status is null p_taxid_record_status: '||p_taxid_record_status);
2323 END IF;
2324 p_taxid_record_status:='S';
2325
2326 END IF;
2327 END jl_zz_taxid_customers;
2328
2329 -------------------------------------------------------------------------------
2330 -- Following segments are defined for Brazilian Invoice Interface
2331 -------------------------------------------------------------------------------
2332 -- No. Name Column Value Set Required
2333 -- -- ------------------------------- --------------------- --------------------------- -------
2334 -- 1 Collection Document Association GLOBAL_ATTRIBUTE1 JLBR_AP_CHAR_ENABLE_BANK_COLL Yes
2335 -- 2 Operation Fiscal Code GLOBAL_ATTRIBUTE2 JLBR_AP_CFO_CODE2 No
2336 -- 3 Series GLOBAL_ATTRIBUTE3 JLBR_AP_CHAR_INVOICE_SERIES No
2337 -- 4 Class GLOBAL_ATTRIBUTE4 JLBR_AP_CHAR_INVOICE_CLASS No
2338 -- 5 ICMS Base Amount GLOBAL_ATTRIBUTE5 JLBR_PO_NUMBER_BASE_AMOUNT No
2339 -- 6 ICMS Name GLOBAL_ATTRIBUTE6 JLBR_PO_CHAR_ICMS_TAX_NAME No
2340 -- 7 ICMS Amount GLOBAL_ATTRIBUTE7 JLBR_PO_NUMBER_ICMS_TAX_AMOUNT No
2341 -- 8 IPI Amount GLOBAL_ATTRIBUTE8 JLBR_PO_NUMBER_IPI_TAX_AMOUNT No
2342 -- 9 Withholding Base Amount GLOBAL_ATTRIBUTE9 JLBR_AP_NUMBER_WHT_BASE_AMOUNT No
2343 --------------------------------------------------------------------------------
2344 -- This procedure validates the information in the GA 1,2,3,4,5,6,7,8,9,10
2345 -- in the invoice header for Brazil.
2346 --
2347
2348 PROCEDURE jl_br_apxiisim_invoices_folder
2349 (p_parent_id IN NUMBER,
2350 p_default_last_updated_by IN NUMBER,
2351 p_default_last_update_login IN NUMBER,
2352 p_global_attribute1 IN VARCHAR2,
2353 p_global_attribute2 IN VARCHAR2,
2354 p_global_attribute3 IN VARCHAR2,
2355 p_global_attribute4 IN VARCHAR2,
2356 p_global_attribute5 IN VARCHAR2,
2357 p_global_attribute6 IN VARCHAR2,
2358 p_global_attribute7 IN VARCHAR2,
2359 p_global_attribute8 IN VARCHAR2,
2360 p_global_attribute9 IN VARCHAR2,
2361 p_global_attribute10 IN VARCHAR2,
2362 p_global_attribute11 IN VARCHAR2,
2363 p_global_attribute12 IN VARCHAR2,
2364 p_global_attribute13 IN VARCHAR2,
2365 p_global_attribute14 IN VARCHAR2,
2366 p_global_attribute15 IN VARCHAR2,
2367 p_global_attribute16 IN VARCHAR2,
2368 p_global_attribute17 IN VARCHAR2,
2369 p_global_attribute18 IN VARCHAR2,
2370 p_global_attribute19 IN VARCHAR2,
2371 p_global_attribute20 IN VARCHAR2,
2372 p_current_invoice_status OUT NOCOPY VARCHAR2,
2373 p_calling_sequence IN VARCHAR2) IS
2374
2375 value_exists VARCHAR2(1);
2376 p_val_date date;
2377
2378 BEGIN
2379
2380 -- Validation for Collection Document Association Option
2381
2382 IF (p_global_attribute1 IS NOT NULL) THEN
2383 BEGIN
2384 SELECT 'X'
2385 INTO value_exists
2386 FROM fnd_lookups
2387 WHERE lookup_type = 'YES_NO'
2388 AND lookup_code = p_global_attribute1
2389 AND nvl(start_date_active,sysdate) <= sysdate
2390 AND nvl(end_date_active,sysdate) >= sysdate
2391 AND enabled_flag = 'Y';
2392 EXCEPTION
2393
2394 WHEN OTHERS THEN
2395 jg_globe_flex_val_shared.insert_rejections('AP_INVOICES_INTERFACE',
2396 p_parent_id,
2397 'INVALID_GLOBAL_ATTR1',
2398 p_default_last_updated_by,
2399 p_default_last_update_login,
2400 p_calling_sequence);
2401 p_current_invoice_status := 'N';
2402
2403 END;
2404 ELSE -- The Global Attribute1 is Required
2405 jg_globe_flex_val_shared.insert_rejections('AP_INVOICES_INTERFACE',
2406 p_parent_id,
2407 'INVALID_GLOBAL_ATTR1',
2408 p_default_last_updated_by,
2409 p_default_last_update_login,
2410 p_calling_sequence);
2411 p_current_invoice_status := 'N';
2412
2413 END IF; -- p_global_attribute1 is not null
2414
2415 -- Validation for Operation Fiscal Code
2416
2417 IF (p_global_attribute2 IS NOT NULL) THEN
2418 BEGIN
2419 SELECT 'X'
2420 INTO value_exists
2421 FROM jl_br_ap_operations
2422 WHERE cfo_code = p_global_attribute2;
2423 EXCEPTION
2424 WHEN OTHERS THEN
2425 jg_globe_flex_val_shared.insert_rejections('AP_INVOICES_INTERFACE',
2426 p_parent_id,
2427 'INVALID_GLOBAL_ATTR2',
2428 p_default_last_updated_by,
2429 p_default_last_update_login,
2430 p_calling_sequence);
2431 p_current_invoice_status := 'N';
2432 END;
2433 END IF; -- p_global_attribute2 is not null
2434
2435 -- Validation for Invoice Series
2436
2437 IF (p_global_attribute3 IS NOT NULL) THEN
2438 BEGIN
2439 SELECT 'X'
2440 INTO value_exists
2441 FROM fnd_lookups
2442 WHERE lookup_type = 'JLBR_INVOICE_SERIES'
2443 AND lookup_code = p_global_attribute3
2444 AND nvl(start_date_active,sysdate) <= sysdate
2445 AND nvl(end_date_active,sysdate) >= sysdate
2446 AND enabled_flag = 'Y';
2447 EXCEPTION
2448
2449 WHEN OTHERS THEN
2450 jg_globe_flex_val_shared.insert_rejections('AP_INVOICES_INTERFACE',
2451 p_parent_id,
2452 'INVALID_GLOBAL_ATTR3',
2453 p_default_last_updated_by,
2454 p_default_last_update_login,
2455 p_calling_sequence);
2456 p_current_invoice_status := 'N';
2457
2458 END;
2459 END IF; -- p_global_attribute3 is not null
2460
2461 -- Validation for Invoice Class
2462
2463 IF (p_global_attribute4 IS NOT NULL) THEN
2464 BEGIN
2465 SELECT 'X'
2466 INTO value_exists
2467 FROM fnd_lookups
2468 WHERE lookup_type = 'JLBR_INVOICE_CLASS'
2469 AND lookup_code = p_global_attribute4
2470 AND nvl(start_date_active,sysdate) <= sysdate
2471 AND nvl(end_date_active,sysdate) >= sysdate
2472 AND enabled_flag = 'Y';
2473 EXCEPTION
2474
2475 WHEN OTHERS THEN
2476 jg_globe_flex_val_shared.insert_rejections('AP_INVOICES_INTERFACE',
2477 p_parent_id,
2478 'INVALID_GLOBAL_ATTR4',
2479 p_default_last_updated_by,
2480 p_default_last_update_login,
2481 p_calling_sequence);
2482 p_current_invoice_status := 'N';
2483
2484 END;
2485 END IF; -- p_global_attribute4 is not null
2486
2487 -- Format for ICMS Base Amount
2488
2489 /* NUMBER(15,2),Numbers Only (0-9)*/
2490 IF (NOT jg_globe_flex_val_shared.check_format(p_global_attribute5,'N',15,2,'N','N','N','','')) THEN
2491 jg_globe_flex_val_shared.insert_rejections('AP_INVOICES_INTERFACE',
2492 p_parent_id,
2493 'INVALID_GLOBAL_ATTR5',
2494 p_default_last_updated_by,
2495 p_default_last_update_login,
2496 p_calling_sequence);
2497 p_current_invoice_status := 'N';
2498 END IF;
2499
2500 -- Validation for ICMS Name
2501
2502 -- Bug # 955006
2503 -- irani 10/15/99
2504
2505 IF (p_global_attribute6 IS NOT NULL) THEN
2506
2507 BEGIN
2508 SELECT invoice_date
2509 INTO p_val_date
2510 FROM ap_invoices_interface
2511 WHERE invoice_id = p_parent_id;
2512 EXCEPTION
2513 WHEN NO_DATA_FOUND THEN
2514 Null;
2515 END;
2516
2517 BEGIN
2518 SELECT 'X'
2519 INTO value_exists
2520 FROM ap_tax_codes
2521 WHERE tax_type = 'ICMS'
2522 AND name = p_global_attribute6
2523 AND nvl(start_date,p_val_date) <= p_val_date
2524 AND nvl(inactive_date,p_val_date+1) > p_val_date
2525 AND nvl(enabled_flag,'Y') = 'Y';
2526 EXCEPTION
2527
2528 WHEN OTHERS THEN
2529 jg_globe_flex_val_shared.insert_rejections('AP_INVOICES_INTERFACE',
2530 p_parent_id,
2531 'INVALID_GLOBAL_ATTR6',
2532 p_default_last_updated_by,
2533 p_default_last_update_login,
2534 p_calling_sequence);
2535 p_current_invoice_status := 'N';
2536
2537 END;
2538 END IF; -- p_global_attribute6 is not null
2539
2540 -- Format for ICMS Amount
2541
2542 /* NUMBER(15,2),Numbers Only (0-9)*/
2543 IF (NOT jg_globe_flex_val_shared.check_format(p_global_attribute7,'N',15,2,'N','N','N','','')) THEN
2544 jg_globe_flex_val_shared.insert_rejections('AP_INVOICES_INTERFACE',
2545 p_parent_id,
2546 'INVALID_GLOBAL_ATTR7',
2547 p_default_last_updated_by,
2548 p_default_last_update_login,
2549 p_calling_sequence);
2550 p_current_invoice_status := 'N';
2551 END IF;
2552
2553 -- Format for IPI Amount
2554
2555 /* NUMBER(15,2),Numbers Only (0-9)*/
2556 IF (NOT jg_globe_flex_val_shared.check_format(p_global_attribute8,'N',15,2,'N','N','N','','')) THEN
2557 jg_globe_flex_val_shared.insert_rejections('AP_INVOICES_INTERFACE',
2558 p_parent_id,
2559 'INVALID_GLOBAL_ATTR8',
2560 p_default_last_updated_by,
2561 p_default_last_update_login,
2562 p_calling_sequence);
2563 p_current_invoice_status := 'N';
2564 END IF;
2565
2566 -- Format for Withholding Base Amount
2567
2568 /* NUMBER(15,2)*/
2569 IF (NOT jg_globe_flex_val_shared.check_format(p_global_attribute9,'N',15,2,'Y','N','N','','')) THEN
2570 jg_globe_flex_val_shared.insert_rejections('AP_INVOICES_INTERFACE',
2571 p_parent_id,
2572 'INVALID_GLOBAL_ATTR9',
2573 p_default_last_updated_by,
2574 p_default_last_update_login,
2575 p_calling_sequence);
2576 p_current_invoice_status := 'N';
2577 END IF;
2578
2579 -- Validation for Consolidated Invoice Number (Should be NULL at this point)
2580
2581 IF (p_global_attribute10 IS NOT NULL) THEN
2582 jg_globe_flex_val_shared.insert_rejections('AP_INVOICES_INTERFACE',
2583 p_parent_id,
2584 'INVALID_GLOBAL_ATTR10',
2585 p_default_last_updated_by,
2586 p_default_last_update_login,
2587 p_calling_sequence);
2588 p_current_invoice_status := 'N';
2589
2590 END IF; -- p_global_attribute10 is not null
2591
2592
2593 -- Validate the rest of the Global Attributes be NULL
2594
2595 IF ((p_global_attribute11 IS NOT NULL) OR
2596 (p_global_attribute12 IS NOT NULL) OR
2597 (p_global_attribute13 IS NOT NULL) OR
2598 (p_global_attribute14 IS NOT NULL) OR
2599 (p_global_attribute15 IS NOT NULL) OR
2600 (p_global_attribute16 IS NOT NULL) OR
2601 (p_global_attribute17 IS NOT NULL) OR
2602 (p_global_attribute18 IS NOT NULL) OR
2603 (p_global_attribute19 IS NOT NULL) OR
2604 (p_global_attribute20 IS NOT NULL))
2605 THEN
2606 jg_globe_flex_val_shared.insert_rejections('AP_INVOICES_INTERFACE',
2607 p_parent_id,
2608 'GLOBAL_ATTR_VALUE_FOUND',
2609 p_default_last_updated_by,
2610 p_default_last_update_login,
2611 p_calling_sequence);
2612 p_current_invoice_status := 'N';
2613 END IF;
2614
2615 END jl_br_apxiisim_invoices_folder;
2616
2617 -------------------------------------------------------------------------------
2618 -- Following segments are defined for Brazilian Invoice Lines Interface
2619 -------------------------------------------------------------------------------
2620 -- No. Name Column Value Set Required
2621
2622 -- --- ------------------- ------------------ ------------------------ -------
2623 -- 1 Operation Fiscal Code GLOBAL_ATTRIBUTE1 JLBR_AP_CFO_CODE No
2624 --------------------------------------------------------------------------------
2625 -- This procedure validates the information in the GA 1
2626 -- in the invoice Line for Brazil.
2627 --
2628 PROCEDURE jl_br_apxiisim_lines_folder
2629 (p_parent_id IN NUMBER,
2630 p_line_type_lookup_code IN VARCHAR2,
2631 p_default_last_updated_by IN NUMBER,
2632 p_default_last_update_login IN NUMBER,
2633 p_global_attribute1 IN VARCHAR2,
2634 p_global_attribute2 IN VARCHAR2,
2635 p_global_attribute3 IN VARCHAR2,
2636 p_global_attribute4 IN VARCHAR2,
2637 p_global_attribute5 IN VARCHAR2,
2638 p_global_attribute6 IN VARCHAR2,
2639 p_global_attribute7 IN VARCHAR2,
2640 p_global_attribute8 IN VARCHAR2,
2641 p_global_attribute9 IN VARCHAR2,
2642 p_global_attribute10 IN VARCHAR2,
2643 p_global_attribute11 IN VARCHAR2,
2644 p_global_attribute12 IN VARCHAR2,
2645 p_global_attribute13 IN VARCHAR2,
2646 p_global_attribute14 IN VARCHAR2,
2647 p_global_attribute15 IN VARCHAR2,
2648 p_global_attribute16 IN VARCHAR2,
2649 p_global_attribute17 IN VARCHAR2,
2650 p_global_attribute18 IN VARCHAR2,
2651 p_global_attribute19 IN VARCHAR2,
2652 p_global_attribute20 IN VARCHAR2,
2653 p_current_invoice_status OUT NOCOPY VARCHAR2,
2654 p_calling_sequence IN VARCHAR2) IS
2655
2656 value_exists VARCHAR2(1);
2657
2658 BEGIN
2659 -- Validation for Operation Fiscal Code
2660
2661 IF (p_global_attribute1 IS NOT NULL) THEN
2662 BEGIN
2663 SELECT 'X'
2664 INTO value_exists
2665 FROM jl_br_ap_operations
2666 WHERE p_line_type_lookup_code = 'ITEM'
2667 AND CFO_CODE = p_global_attribute1;
2668 EXCEPTION
2669 WHEN OTHERS THEN
2670 jg_globe_flex_val_shared.insert_rejections('AP_INVOICE_LINES_INTERFACE',
2671 p_parent_id,
2672 'INVALID_GLOBAL_ATTR1',
2673 p_default_last_updated_by,
2674 p_default_last_update_login,
2675 p_calling_sequence);
2676 p_current_invoice_status := 'N';
2677 END;
2678 END IF; -- p_global_attribute1 is not null
2679
2680 -- Validate the rest of the Global Attributes be NULL
2681
2682 IF ((p_global_attribute2 IS NOT NULL) OR
2683 (p_global_attribute3 IS NOT NULL) OR
2684 (p_global_attribute4 IS NOT NULL) OR
2685 (p_global_attribute5 IS NOT NULL) OR
2686 (p_global_attribute6 IS NOT NULL) OR
2687 (p_global_attribute7 IS NOT NULL) OR
2688 (p_global_attribute8 IS NOT NULL) OR
2689 (p_global_attribute9 IS NOT NULL) OR
2690 (p_global_attribute10 IS NOT NULL) OR
2691 (p_global_attribute11 IS NOT NULL) OR
2692 (p_global_attribute12 IS NOT NULL) OR
2693 (p_global_attribute13 IS NOT NULL) OR
2694 (p_global_attribute14 IS NOT NULL) OR
2695 (p_global_attribute15 IS NOT NULL) OR
2696 (p_global_attribute16 IS NOT NULL) OR
2697 (p_global_attribute17 IS NOT NULL) OR
2698 (p_global_attribute18 IS NOT NULL) OR
2699 (p_global_attribute19 IS NOT NULL) OR
2700 (p_global_attribute20 IS NOT NULL))
2701 THEN
2702 jg_globe_flex_val_shared.insert_rejections('AP_INVOICE_LINES_INTERFACE',
2703 p_parent_id,
2704 'GLOBAL_ATTR_VALUE_FOUND',
2705 p_default_last_updated_by,
2706 p_default_last_update_login,
2707 p_calling_sequence);
2708 p_current_invoice_status := 'N';
2709 END IF;
2710 END jl_br_apxiisim_lines_folder;
2711
2712 -- togeorge 11/22/1999
2713 -- Bug# 1074309
2714 PROCEDURE jl_br_apxiisim_val_cfo_code
2715 (p_parent_id IN NUMBER,
2716 p_line_type_lookup_code IN VARCHAR2,
2717 p_default_last_updated_by IN NUMBER,
2718 p_default_last_update_login IN NUMBER,
2719 p_current_invoice_status OUT NOCOPY VARCHAR2,
2720 p_calling_sequence IN VARCHAR2) IS
2721
2722 l_invoice_id NUMBER;
2723 l_po_number VARCHAR2(20);
2724 l_cfo_code VARCHAR2(15);
2725 l_icms_tax_name VARCHAR2(15);
2726 l_icms_tax_amount VARCHAR2(15);
2727 l_exists NUMBER;
2728 BEGIN
2729 BEGIN
2730 SELECT invoice_id,po_number
2731 INTO l_invoice_id,l_po_number
2732 FROM ap_invoice_lines_interface
2733 WHERE invoice_line_id = p_parent_id;
2734 EXCEPTION
2735 WHEN OTHERS THEN
2736 null;
2737 END;
2738 IF l_po_number is null THEN --then it is a nomatch case
2739 BEGIN
2740 SELECT global_attribute2,global_attribute6,global_attribute7
2741 INTO l_cfo_code,l_icms_tax_name,l_icms_tax_amount
2742 FROM ap_invoices_interface
2743 WHERE invoice_id = l_invoice_id;
2744 EXCEPTION
2745 WHEN OTHERS THEN
2746 null;
2747 END;
2748 IF l_icms_tax_name IS NOT NULL OR l_icms_tax_amount IS NOT NULL THEN
2749 IF l_cfo_code IS NULL THEN
2750 BEGIN
2751 SELECT DISTINCT 1
2752 INTO l_exists
2753 FROM ap_interface_rejections
2754 WHERE parent_id=l_invoice_id
2755 AND parent_table = 'AP_INVOICES_INTERFACE'
2756 AND REJECT_LOOKUP_CODE = 'INVALID_GLOBAL_ATTR2';
2757 EXCEPTION
2758 WHEN NO_DATA_FOUND THEN
2759 jg_globe_flex_val_shared.insert_rejections('AP_INVOICES_INTERFACE',
2760 l_invoice_id,
2761 'INVALID_GLOBAL_ATTR2',
2762 p_default_last_updated_by,
2763 p_default_last_update_login,
2764 p_calling_sequence);
2765 END;
2766 p_current_invoice_status := 'N';
2767 END IF;
2768 END IF;
2769 END IF;
2770
2771 END jl_br_apxiisim_val_cfo_code;
2772
2773 --=========================================================================
2774 -- This procedure validates address attribute columns From Customer Interface
2775 -- table (RA_CUSTOMERS_INTERFACE) for specific Brazilian requirements.
2776 -- IF any validation fails, this procedure writes the error code
2777 -- in the interface_status column of RA_CUSTOMERS_INTERFACE
2778 --========================================================================
2779 Procedure jl_br_arxcudci_additional
2780 ( p_glob_attr_set1 IN jg_globe_flex_val_shared.GdfRec,
2781 p_glob_attr_set2 IN jg_globe_flex_val_shared.GdfRec,
2782 p_glob_attr_set3 IN jg_globe_flex_val_shared.GdfRec,
2783 p_misc_prod_arg IN jg_globe_flex_val_shared.GenRec,
2784 p_record_status OUT NOCOPY VARCHAR2)
2785 IS
2786 l_inscription_type varchar2(1);
2787 l_inscription_number varchar2(9);
2788 l_inscription_branch varchar2(4);
2789 l_inscription_digit varchar2(2);
2790 l_errbuf varchar2(30);
2791 l_retcode number;
2792 l_num_check number;
2793 l_error_code varchar2(50) DEFAULT NULL;
2794 l_row_id ROWID; -- := p_misc_prod_arg.core_prod_arg2;
2795
2796 BEGIN
2797 l_row_id := p_misc_prod_arg.core_prod_arg2;
2798
2799 /* Check inscription Number */
2800 IF p_glob_attr_set2.global_attribute2 is not null THEN
2801
2802 /* Get the inscription type code */
2803 l_inscription_type := substr(p_glob_attr_set2.global_attribute2,1,1);
2804 l_inscription_number := substr(p_glob_attr_set2.global_attribute3,1,9);
2805 l_inscription_branch := substr(p_glob_attr_set2.global_attribute4,1,4);
2806 l_inscription_digit := substr(p_glob_attr_set2.global_attribute5,1,2);
2807
2808 BEGIN
2809 select to_number(l_inscription_type)
2810 into l_num_check
2811 from dual;
2812 EXCEPTION WHEN INVALID_NUMBER OR VALUE_ERROR THEN
2813 l_error_code := l_error_code||'n3,';
2814 END;
2815 IF l_error_code is NULL THEN
2816 IF l_inscription_type = '1' OR
2817 l_inscription_type = '2' THEN
2818 BEGIN
2819 select to_number(l_inscription_number)
2820 into l_num_check
2821 from dual;
2822 EXCEPTION WHEN INVALID_NUMBER OR VALUE_ERROR THEN
2823 l_error_code := l_error_code||'n4,';
2824 END;
2825
2826 BEGIN
2827 select to_number(l_inscription_branch)
2828 into l_num_check
2829 from dual;
2830 EXCEPTION WHEN INVALID_NUMBER OR VALUE_ERROR THEN
2831 l_error_code := l_error_code ||'n5,';
2832 END;
2833
2834 BEGIN
2835 select to_number(l_inscription_digit)
2836 into l_num_check
2837 from dual;
2838 EXCEPTION WHEN INVALID_NUMBER OR VALUE_ERROR THEN
2839 l_error_code := l_error_code ||'n6,';
2840 END;
2841 END IF;
2842 END IF;
2843 IF (l_error_code is null) THEN
2844 jl_br_inscription_number.validate_inscription_number(
2845 l_inscription_type,
2846 l_inscription_number,
2847 l_inscription_branch,
2848 l_inscription_digit,
2849 l_errbuf,
2850 l_retcode);
2851
2852 IF l_retcode <> 0 THEN /* Validation of inscription number failed */
2853 IF l_errbuf = 'CGC_INSCRIPTION_NUMBER_ERR' OR
2854 l_errbuf = 'CPF_INSCRIPTION_NUMBER_ERR' THEN
2855 l_error_code := l_error_code ||'n6,';
2856 ELSIF l_errbuf = 'CPF_INSCRIPTION_BRANCH_ERR' THEN
2857 l_error_code := l_error_code ||'n5,';
2858 ELSIF l_errbuf = 'INSCRIPTION_TYPE_ERR' THEN
2859 l_error_code := l_error_code ||'n3,';
2860 END IF;
2861
2862 END IF;
2863
2864 END IF;
2865
2866 ELSE /* There is no inscription type */
2867 l_error_code := l_error_code ||'n3,';
2868 END IF;
2869
2870 --Call procedure to write the error codes to interface table
2871 IF l_error_code IS NULL THEN
2872 p_record_status := 'S';
2873 ELSE
2874 p_record_status := 'E';
2875 jg_globe_flex_val_shared.update_interface_status
2876 (l_row_id,
2877 'RA_CUSTOMERS_INTERFACE',
2878 l_error_code,
2879 p_record_status);
2880 END IF;
2881
2882 END;
2883
2884 --=========================================================================
2885 -- This procedure validates the customer profile attribute columns From
2886 -- Customer Interface table (RA_CUSTOMERS_INTERFACE) for specific
2887 -- Brazilian requirements. IF any validation fails, this procedure writes
2888 --the error code in the Interface status column of RA_CUSTOMER_PROFILES_
2889 --INTERFACE
2890 --========================================================================+
2891
2892 PROCEDURE jl_br_customer_profiles
2893 ( p_glob_attr_set1 IN jg_globe_flex_val_shared.GdfRec,
2894 p_glob_attr_set2 IN jg_globe_flex_val_shared.GdfRec,
2895 p_glob_attr_set3 IN jg_globe_flex_val_shared.GdfRec,
2896 p_misc_prod_arg IN jg_globe_flex_val_shared.GenRec,
2897 p_record_status OUT NOCOPY VARCHAR2)
2898 IS
2899
2900 dummy_code NUMBER;
2901 l_error_code varchar2(50); -- :='';
2902 l_row_id ROWID; -- := p_misc_prod_arg.core_prod_arg2;
2903
2904 BEGIN
2905 l_error_code :='';
2906 l_row_id := p_misc_prod_arg.core_prod_arg2;
2907
2908 --Check Remit_protest_instructions
2909 IF p_glob_attr_set2.global_attribute1 IS NOT NULL THEN
2910 BEGIN
2911 SELECT 1
2912 INTO dummy_code
2913 FROM fnd_lookups
2914 WHERE lookup_code=p_glob_attr_set2.global_attribute1
2915 AND lookup_type = 'YES_NO'
2916 AND NVL(START_DATE_ACTIVE,SYSDATE) <= SYSDATE
2917 AND NVL(END_DATE_ACTIVE,SYSDATE) >= SYSDATE
2918 AND ENABLED_FLAG = 'Y';
2919 EXCEPTION
2920 WHEN NO_DATA_FOUND THEN
2921 l_error_code := l_error_code||'r4,';
2922 END;
2923 ELSE
2924 l_error_code := l_error_code||'r4,';
2925 END IF;
2926
2927 --Check Remit interest instructions
2928 IF p_glob_attr_set2.global_attribute2 IS NOT NULL THEN
2929 BEGIN
2930 SELECT 1
2931 INTO dummy_code
2932 FROM fnd_lookups
2933 WHERE lookup_code=p_glob_attr_set2.global_attribute2
2934 AND lookup_type = 'YES_NO'
2935 AND NVL(START_DATE_ACTIVE,SYSDATE) <= SYSDATE
2936 AND NVL(END_DATE_ACTIVE,SYSDATE) >= SYSDATE
2937 AND ENABLED_FLAG = 'Y';
2938 EXCEPTION
2939 WHEN NO_DATA_FOUND THEN
2940 l_error_code :=l_error_code||'r5,';
2941 END;
2942 ELSE
2943 l_error_code := l_error_code||'r5,';
2944 END IF;
2945
2946
2947 IF l_error_code IS NULL THEN
2948 p_record_status := 'S';
2949 ELSE
2950 p_record_status := 'E';
2951 jg_globe_flex_val_shared.update_interface_status
2952 (l_row_id,
2953 'RA_CUSTOMER_PROFILES_INTERFACE',
2954 l_error_code,
2955 p_record_status);
2956 END IF;
2957 END;
2958
2959 --=========================================================================
2960 -- This procedure validates address attribute column From Customer Interface
2961 -- table (RA_CUSTOMERS_INTERFACE) for LTE requirements.
2962 -- IF any validation fails, this procedure writes the error code
2963 -- in the interface_status column of RA_CUSTOMERS_INTERFACE
2964 --========================================================================
2965 procedure jl_zz_ar_tx_arxcudci_address
2966 ( p_glob_attr_set1 IN jg_globe_flex_val_shared.GdfRec,
2967 p_glob_attr_set2 IN jg_globe_flex_val_shared.GdfRec,
2968 p_glob_attr_set3 IN jg_globe_flex_val_shared.GdfRec,
2969 p_misc_prod_arg IN jg_globe_flex_val_shared.GenRec,
2970 p_record_status OUT NOCOPY VARCHAR2) IS
2971
2972 l_error_code varchar2(50) DEFAULT NULL;
2973 l_row_id ROWID; -- := p_misc_prod_arg.core_prod_arg2;
2974 l_tax_method VARCHAR2(30);
2975 l_tax_rule_set VARCHAR2(30);
2976 l_dummy NUMBER;
2977
2978 BEGIN
2979 l_row_id := p_misc_prod_arg.core_prod_arg2;
2980 l_tax_method := NULL;
2981
2982 BEGIN
2983 -- Bug 3761529
2984 SELECT tax_method_code
2985 INTO l_tax_method
2986 FROM zx_product_options;
2987
2988 SELECT substr(global_attribute13,1,30)
2989 INTO l_tax_rule_set
2990 FROM ar_system_parameters;
2991 EXCEPTION
2992 WHEN OTHERS THEN
2993 l_tax_method := NULL;
2994 END;
2995
2996 IF l_tax_method = 'LTE' THEN
2997
2998 /* Check contributor Condition Class Value */
2999
3000 IF p_glob_attr_set2.global_attribute8 is not null THEN
3001
3002 l_dummy := 0;
3003 BEGIN
3004 SELECT count(*)
3005 INTO l_dummy
3006 FROM jl_zz_ar_tx_att_cls tac,
3007 jl_zz_ar_tx_categ tc
3008 WHERE tac.tax_attr_class_code = p_glob_attr_set2.global_attribute8
3009 AND tac.tax_attr_class_type = 'CONTRIBUTOR_CLASS'
3010 AND tac.enabled_flag = 'Y'
3011 AND tac.tax_category_id = tc.tax_category_id
3012 AND tc.tax_rule_set = l_tax_rule_set;
3013 EXCEPTION
3014 WHEN OTHERS THEN
3015 l_dummy := 0;
3016 END;
3017
3018 IF l_dummy = 0 THEN
3019 l_error_code := l_error_code ||'n9,';
3020 END IF;
3021
3022 ELSE
3023 l_error_code := l_error_code ||'n9,';
3024 END IF;
3025
3026 /* Value for 'Use Cust Site Profile' should be 'N'.
3027 Currently NULL value is interpreted as 'Y' by Latin Tax Engine to
3028 support existing records. New records in Customer Site must have
3029 a value of 'Y'/'N' for global_attribute9. If value is 'Y', Tax
3030 Engine evaluates JL_ZZ_AR_TX_CUS_CLS for applicability and evaluates
3031 JL_ZZ_AR_TX_ATT_CLS if value is 'N'.
3032 This change was implemented as part of Bugfix #1783986 */
3033
3034 IF NVL(p_glob_attr_set2.global_attribute9,'Y') <> 'N' THEN
3035 l_error_code := l_error_code || 'n0,';
3036 END IF;
3037
3038 END IF;
3039
3040 -- Call procedure to write the error codes to interface table
3041
3042 IF l_error_code IS NULL THEN
3043 p_record_status := 'S';
3044 ELSE
3045 p_record_status := 'E';
3046 jg_globe_flex_val_shared.update_interface_status (l_row_id,
3047 'RA_CUSTOMERS_INTERFACE',
3048 l_error_code,
3049 p_record_status);
3050 END IF;
3051
3052 END jl_zz_ar_tx_arxcudci_address;
3053
3054
3055 -------------------------------------------------------------------------------
3056 -- Following segments are defined for Chile Invoice Interface
3057 -------------------------------------------------------------------------------
3058 -- No. Name Column Value Set Req.
3059 -- --- ------------------- ------------------ -------------------------- ------
3060 -- 1 Document Type GLOBAL_ATTRIBUTE19 JLCL_AP_DOCUMENT_TYPE_IG No
3061 --------------------------------------------------------------------------------
3062 --
3063 -- This procedure validates the information in the GA19
3064 -- in the invoice header for Chile.
3065 --
3066
3067 PROCEDURE jl_cl_apxiisim_invoices_folder
3068 (p_parent_id IN NUMBER,
3069 p_default_last_updated_by IN NUMBER,
3070 p_default_last_update_login IN NUMBER,
3071 p_global_attribute1 IN VARCHAR2,
3072 p_global_attribute2 IN VARCHAR2,
3073 p_global_attribute3 IN VARCHAR2,
3074 p_global_attribute4 IN VARCHAR2,
3075 p_global_attribute5 IN VARCHAR2,
3076 p_global_attribute6 IN VARCHAR2,
3077 p_global_attribute7 IN VARCHAR2,
3078 p_global_attribute8 IN VARCHAR2,
3079 p_global_attribute9 IN VARCHAR2,
3080 p_global_attribute10 IN VARCHAR2,
3081 p_global_attribute11 IN VARCHAR2,
3082 p_global_attribute12 IN VARCHAR2,
3083 p_global_attribute13 IN VARCHAR2,
3084 p_global_attribute14 IN VARCHAR2,
3085 p_global_attribute15 IN VARCHAR2,
3086 p_global_attribute16 IN VARCHAR2,
3087 p_global_attribute17 IN VARCHAR2,
3088 p_global_attribute18 IN VARCHAR2,
3089 p_global_attribute19 IN VARCHAR2,
3090 p_global_attribute20 IN VARCHAR2,
3091 p_current_invoice_status OUT NOCOPY VARCHAR2,
3092 p_calling_sequence IN VARCHAR2) IS
3093
3094 value_exists VARCHAR2(1);
3095
3096 BEGIN
3097
3098 -- Validation for Document Type
3099
3100 IF (p_global_attribute19 IS NOT NULL) THEN
3101 BEGIN
3102 SELECT 'X'
3103 INTO value_exists
3104 FROM fnd_lookups
3105 WHERE lookup_type = 'JLCL_AP_DOCUMENT_TYPE'
3106 AND lookup_code = p_global_attribute19
3107 AND nvl(start_date_active,sysdate) <= sysdate
3108 AND nvl(end_date_active,sysdate) >= sysdate
3109 AND enabled_flag = 'Y';
3110 EXCEPTION
3111
3112 WHEN OTHERS THEN
3113 jg_globe_flex_val_shared.insert_rejections('AP_INVOICES_INTERFACE',
3114 p_parent_id,
3115 'INVALID_GLOBAL_ATTR19',
3116 p_default_last_updated_by,
3117 p_default_last_update_login,
3118 p_calling_sequence);
3119 p_current_invoice_status := 'N';
3120
3121 END;
3122 END IF; -- p_global_attribute19 is not null
3123
3124
3125 -- Validate the rest of the Global Attributes be NULL
3126
3127 IF ((p_global_attribute1 IS NOT NULL) OR
3128 (p_global_attribute2 IS NOT NULL) OR
3129 (p_global_attribute3 IS NOT NULL) OR
3130 (p_global_attribute4 IS NOT NULL) OR
3131 (p_global_attribute5 IS NOT NULL) OR
3132 (p_global_attribute6 IS NOT NULL) OR
3133 (p_global_attribute7 IS NOT NULL) OR
3134 (p_global_attribute8 IS NOT NULL) OR
3135 (p_global_attribute9 IS NOT NULL) OR
3136 (p_global_attribute10 IS NOT NULL) OR
3137 (p_global_attribute11 IS NOT NULL) OR
3138 (p_global_attribute12 IS NOT NULL) OR
3139 (p_global_attribute13 IS NOT NULL) OR
3140 (p_global_attribute14 IS NOT NULL) OR
3141 (p_global_attribute15 IS NOT NULL) OR
3142 (p_global_attribute16 IS NOT NULL) OR
3143 (p_global_attribute17 IS NOT NULL) OR
3144 (p_global_attribute18 IS NOT NULL) OR
3145 (p_global_attribute20 IS NOT NULL))
3146 THEN
3147 jg_globe_flex_val_shared.insert_rejections('AP_INVOICES_INTERFACE',
3148 p_parent_id,
3149 'GLOBAL_ATTR_VALUE_FOUND',
3150 p_default_last_updated_by,
3151 p_default_last_update_login,
3152 p_calling_sequence);
3153 p_current_invoice_status := 'N';
3154 END IF;
3155
3156 END jl_cl_apxiisim_invoices_folder;
3157
3158 END JL_INTERFACE_VAL;
[Home] [Help]