1 PACKAGE BODY JL_ZZ_AUTO_INVOICE as
2 /* $Header: jlzzraib.pls 120.20.12020000.2 2012/09/26 13:13:14 mbarrett ship $ */
3
4 /*----------------------------------------------------------------------------*
5 | PUBLIC FUNCTIONS/PROCEDURES |
6 *----------------------------------------------------------------------------*/
7
8 /*----------------------------------------------------------------------------*
9 | FUNCTION |
10 | validate_gdff |
11 | |
12 | DESCRIPTION |
13 | |
14 | PARAMETERS |
15 | INPUT |
16 | p_request_id Number -- Concurrent Request_id |
17 | |
18 | RETURNS |
19 | 0 Number -- Validation Fails, if there is any |
20 | exceptional case which is handled |
21 | in WHEN OTHERS |
22 | 1 Number -- Validation Succeeds |
23 | |
24 *----------------------------------------------------------------------------*/
25 --PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('TAX_DEBUG_FLAG'), 'N');
26 -- Bugfix# 3259701
27 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
28
29 FUNCTION validate_tax_attributes (p_interface_line_id IN NUMBER
30 , p_line_type IN VARCHAR2
31 , p_memo_line_id IN NUMBER
32 , p_inventory_item_id IN NUMBER
33 , p_product_fiscal_class IN VARCHAR2
34 , p_product_category IN VARCHAR2
35 , p_trx_business_category IN VARCHAR2
36 , p_line_attribute11 IN VARCHAR2
37 , p_line_attribute12 IN VARCHAR2
38 , p_address_id IN NUMBER
39 , p_warehouse_id IN NUMBER)
40 RETURN BOOLEAN IS
41
42 error_condition EXCEPTION;
43 l_dummy_code VARCHAR2(15);
44 l_delimiter zx_fc_types_b.delimiter%type;
45 l_trx_business_category VARCHAR2(30);
46 l_contributor_class VARCHAR2(30);
47 l_use_site_prof VARCHAR2(1);
48 l_count NUMBER;
49
50 BEGIN
51
52 IF p_line_type = 'LINE' THEN
53
54 IF p_warehouse_id IS NULL AND
55 p_inventory_item_id IS NOT NULL THEN
56 IF NOT JG_ZZ_AUTO_INVOICE.put_error_message ('JL'
57 ,'JL_ZZ_AR_TX_SHIP_FROM_ORG_REQ'
58 ,p_interface_line_id , p_warehouse_id) THEN
59 RAISE error_condition;
60 END IF;
61 ELSE
62 l_dummy_code := NULL;
63 BEGIN
64 SELECT 'Success'
65 INTO l_dummy_code
66 FROM hr_organization_units hou,
67 hr_locations_all hl
68 WHERE hou.organization_id = p_warehouse_id
69 AND hl.location_id = hou.location_id
70 AND hl.global_attribute1 IS NOT NULL;
71 EXCEPTION
72 WHEN NO_DATA_FOUND THEN
73 IF NOT JG_ZZ_AUTO_INVOICE.put_error_message ('JL'
74 ,'JL_ZZ_AR_TX_ORG_CLASS_INVALID'
75 ,p_interface_line_id ,p_warehouse_id) THEN
76 RAISE error_condition;
77 END IF;
78 END;
79 END IF;
80
81 l_contributor_class := NULL;
82 l_use_site_prof := NULL;
83 IF p_address_id IS NOT NULL THEN
84 BEGIN
85 SELECT global_attribute8, global_attribute9
86 INTO l_contributor_class, l_use_site_prof
87 FROM hz_cust_acct_sites
88 WHERE cust_acct_site_id = p_address_id;
89 EXCEPTION
90 WHEN NO_DATA_FOUND THEN
91 l_contributor_class := NULL;
92 END;
93
94 IF l_contributor_class IS NULL THEN
95 IF NOT JG_ZZ_AUTO_INVOICE.put_error_message ('JL'
96 ,'JL_ZZ_AR_TX_CONTRIB_CLASS_INV'
97 ,p_interface_line_id
98 ,p_address_id) THEN
99 RAISE error_condition;
100 END IF;
101 ELSE
102 l_count := 0;
103 BEGIN
104 IF NVL(l_use_site_prof,'N') = 'Y' THEN
105 SELECT count(*)
106 INTO l_count
107 FROM JL_ZZ_AR_TX_CUS_CLS
108 WHERE address_id = p_address_id
109 AND tax_attr_class_code = l_contributor_class;
110 ELSE
111 SELECT count(*)
112 INTO l_count
113 FROM JL_ZZ_AR_TX_ATT_CLS
114 WHERE tax_attr_class_type = 'CONTRIBUTOR_CLASS'
115 AND tax_attribute_type = 'CONTRIBUTOR_ATTRIBUTE'
116 AND tax_attr_class_code = l_contributor_class;
117 END IF;
118 EXCEPTION
119 WHEN OTHERS THEN
120 l_count := 0;
121 END;
122 END IF;
123
124 IF l_count = 0 THEN
125 IF NOT JG_ZZ_AUTO_INVOICE.put_error_message ('JL'
126 ,'JL_ZZ_AR_TX_CUS_SITE_PROF_REQ'
127 ,p_interface_line_id
128 ,p_address_id) THEN
129 RAISE error_condition;
130 END IF;
131 END IF;
132
133 END IF;
134
135 /* commented for Bug 3761529. Fiscal Classification Code (GA2) is migrated to Item
136 Category model as part of eBTax uptake. So, just validate the column to be a
137 NOT NULL for LTE and the actual validation of Product Fiscal Classification
138 needs to be done by Product or eBTax feature. */
139 /********* bug#8351227- ZX default_and_validate should have validated this
140 IF p_product_fiscal_class IS NOT NULL THEN
141 l_dummy_code := NULL;
142 BEGIN
143 SELECT 'Sucess'
144 INTO l_dummy_code
145 FROM fnd_lookups
146 WHERE lookup_code = p_product_fiscal_class
147 AND lookup_type = 'JLZZ_AR_TX_FISCAL_CLASS_CODE'
148 AND enabled_flag = 'Y'
149 AND sysdate between nvl(start_date_active,sysdate)
150 and nvl(end_date_active,sysdate);
151 EXCEPTION
152 WHEN NO_DATA_FOUND THEN
153 IF NOT JG_ZZ_AUTO_INVOICE.put_error_message ('JL'
154 ,'JL_ZZ_AR_TX_RAXTRX_FSC_CLS_INV'
155 ,p_interface_line_id , p_product_fiscal_class) THEN
156 RAISE error_condition;
157 END IF;
158 WHEN OTHERS THEN
159 RAISE error_condition;
160 END; -- p_product_fiscal_class
161 ELSE
162 IF NOT JG_ZZ_AUTO_INVOICE.put_error_message ('JL'
163 ,'JL_ZZ_AR_TX_RAXTRX_FSC_CLS_MND'
164 ,p_interface_line_id
165 ,p_product_fiscal_class) THEN
166 RAISE error_condition;
167 END IF;
168 END IF; -- p_product_fiscal_class IS NOT NULL
169 */
170
171 /* IF p_product_fiscal_class IS NULL AND
172 p_product_category IS NULL THEN
173 IF NOT JG_ZZ_AUTO_INVOICE.put_error_message ('JL'
174 ,'JL_ZZ_AR_TX_RAXTRX_FSC_CLS_MND'
175 ,p_interface_line_id
176 ,p_product_fiscal_class) THEN
177 RAISE error_condition;
178 END IF;
179 END IF;
180 */
181 /* bug#8351227- ZX default_and_validate should have validated this
182 IF p_trx_business_category IS NOT NULL THEN
183 l_dummy_code := NULL;
184 BEGIN
185
186 -- Transaction Business Category for LTE is prefixed with
187 -- Event Class Code and it has to be eliminated along with
188 -- the delimiter to validate the transaction condition class value.
189 SELECT delimiter
190 INTO l_delimiter
191 FROM zx_fc_types_b
192 WHERE classification_type_code ='TRX_BUSINESS_CATEGORY';
193
194 -- For example, the value for Transaction Business Category of LTE
195 -- will be something like 'INVOICE.INDUSTRIAL' and the assignment
196 -- below would extract only 'INDUSTRIAL' and validates with
197 -- Transaction Condition Class Code of LTE
198 l_trx_business_category := substr(p_trx_business_category,
199 instr(p_trx_business_category,l_delimiter,-1)+1);
200
201 SELECT 'Sucess'
202 INTO l_dummy_code
203 FROM fnd_lookups
204 WHERE lookup_code = l_trx_business_category
205 AND lookup_type = 'TRANSACTION_CLASS'
206 AND enabled_flag = 'Y'
207 AND sysdate BETWEEN nvl(start_date_active,sysdate)
208 AND nvl(end_date_active,sysdate);
209 EXCEPTION
210 WHEN NO_DATA_FOUND THEN
211 IF NOT JG_ZZ_AUTO_INVOICE.put_error_message ('JL'
212 ,'JL_ZZ_AR_TX_RAXTRX_TRX_NAT_INV'
213 ,p_interface_line_id
214 ,p_trx_business_category) THEN
215 RAISE error_condition;
216 END IF;
217 WHEN OTHERS THEN
218 RAISE error_condition;
219 END; -- p_line_attribute3
220 ELSE
221 IF NOT JG_ZZ_AUTO_INVOICE.put_error_message ('JL'
222 ,'JL_ZZ_AR_TX_RAXTRX_TRX_NAT_MND'
223 ,p_interface_line_id
224 ,p_trx_business_category) THEN
225 RAISE error_condition;
226 END IF;
227 END IF; -- p_trx_business_category IS NOT NULL
228 */
229
230 ELSIF p_line_type ='TAX' THEN
231 IF p_line_attribute11 IS NOT NULL THEN
232 l_dummy_code := NULL;
233 BEGIN
234 SELECT FND_NUMBER.canonical_to_number (p_line_attribute11)
235 INTO l_dummy_code
236 FROM sys.dual;
237 EXCEPTION
238 WHEN INVALID_NUMBER OR VALUE_ERROR THEN
239 IF NOT JG_ZZ_AUTO_INVOICE.put_error_message ('JL'
240 ,'JL_ZZ_AR_TX_RAXTRX_BS_AMT_INV'
241 ,p_interface_line_id
242 ,p_line_attribute11) THEN
243 RAISE error_condition;
244 END IF;
245 WHEN OTHERS THEN
246 RAISE error_condition;
247 END; -- p_attribute_11
248 END IF; -- line_attribute11 IS NOT NULL
249
250 IF p_line_attribute12 IS NOT NULL THEN
251 l_dummy_code := NULL;
252 BEGIN
253 SELECT FND_NUMBER.canonical_to_number (p_line_attribute12)
254 INTO l_dummy_code
255 FROM sys.dual;
256 EXCEPTION
257 WHEN INVALID_NUMBER OR VALUE_ERROR THEN
258 IF NOT JG_ZZ_AUTO_INVOICE.put_error_message ('JL'
259 ,'JL_ZZ_AR_TX_RAXTRX_BS_RATE_INV'
260 ,p_interface_line_id
261 ,p_line_attribute12) THEN
262 RAISE error_condition;
263 END IF;
264 WHEN OTHERS THEN
265 RAISE error_condition;
266 END;
267 END IF; -- line_attribute12 IS NOT NULL
268
269 END IF; -- line_type
270
271 RETURN TRUE;
272
273 EXCEPTION
274 WHEN OTHERS THEN
275 RETURN FALSE;
276
277 END validate_tax_attributes;
278
279 FUNCTION validate_interest_attributes (p_interface_line_id IN NUMBER
280 , p_line_type IN VARCHAR2
281 , p_header_attribute1 IN VARCHAR2
282 , p_header_attribute2 IN VARCHAR2
283 , p_header_attribute3 IN VARCHAR2
284 , p_header_attribute4 IN VARCHAR2
285 , p_header_attribute5 IN VARCHAR2
286 , p_header_attribute6 IN VARCHAR2
287 , p_header_attribute7 IN VARCHAR2)
288 RETURN BOOLEAN IS
289
290 dummy_code VARCHAR2 (15);
291 error_condition EXCEPTION;
292
293 BEGIN
294
295 ------------------------------------------------------------
296 -- header_attribute1 is the interest type --
297 -- Valid JLBR_INTEREST_PENALTY_TYPE. Not Mandatory. --
298 ------------------------------------------------------------
299 IF p_line_type = 'LINE' THEN
300
301 IF p_header_attribute1 IS NOT NULL THEN
302
303 BEGIN
304 SELECT 'Success'
305 INTO dummy_code
306 FROM fnd_lookups
307 WHERE lookup_code = p_header_attribute1
308 AND lookup_type = 'JLBR_INTEREST_PENALTY_TYPE'
309 AND enabled_flag = 'Y'
310 AND nvl(start_date_active,sysdate) <= sysdate
311 AND nvl(end_date_active,sysdate+1) >= sysdate;
312
313 EXCEPTION
314 WHEN NO_DATA_FOUND THEN
315 IF NOT JG_ZZ_AUTO_INVOICE.put_error_message ('JL'
316 , 'JL_BR_AR_RAXTRX_INT_TYPE_INV'
317 , p_interface_line_id
318 , p_header_attribute1) THEN
319 RAISE error_condition;
320 END IF;
321 WHEN OTHERS THEN
322 RAISE error_condition;
323 END; -- p_header_attribute1
324
325 END IF;
326
327 END IF; -- p_line_type = 'LINE'
328
329 ------------------------------------------------------------
330 -- header_attribute2 is the interest rate o amount for the--
331 -- invoice. --
332 -- If interest type = 'R' then interest rate between 0 and--
333 -- 100. --
334 -- If interest type = 'A' then interest amount >= 0. --
335 ------------------------------------------------------------
336 IF p_line_type = 'LINE' THEN
337
338 IF p_header_attribute1 = 'R' AND
339 p_header_attribute2 NOT BETWEEN 0 AND 100 THEN
340
341 IF NOT JG_ZZ_AUTO_INVOICE.put_error_message ('JL'
342 , 'JL_BR_AR_RAXTRX_INT_RATE_INV'
343 , p_interface_line_id
344 , p_header_attribute2) THEN
345 RAISE error_condition;
346 END IF;
347
348 END IF;
349
350 IF p_header_attribute1 = 'A' AND p_header_attribute2 < 0 THEN
351
352 IF NOT JG_ZZ_AUTO_INVOICE.put_error_message ('JL'
353 , 'JL_BR_AR_RAXTRX_AMNT_INV'
354 , p_interface_line_id
355 , p_header_attribute2) THEN
356 RAISE error_condition;
357 END IF;
358
359 END IF;
360
361 END IF; -- p_line_type = 'LINE'
362
363 ------------------------------------------------------------
364 -- header_attribute3 is the interest period days. --
365 -- Must be > 0. Mandatory --
366 ------------------------------------------------------------
367 IF p_line_type = 'LINE' THEN
368
369 IF p_header_attribute1 IS NOT NULL THEN
370
371 IF p_header_attribute3 IS NOT NULL THEN
372
373 BEGIN
374 SELECT 'Success'
375 INTO dummy_code
376 FROM dual
377 WHERE to_number (p_header_attribute3) >= 0;
378
379 EXCEPTION
380 WHEN NO_DATA_FOUND OR INVALID_NUMBER OR VALUE_ERROR THEN
381 IF NOT JG_ZZ_AUTO_INVOICE.put_error_message ('JL'
382 , 'JL_BR_AR_RAXTRX_INT_DAY_INV'
383 , p_interface_line_id
384 , p_header_attribute3) THEN
385 RAISE error_condition;
386 END IF;
387 WHEN OTHERS THEN
388 RAISE error_condition;
389 END; -- p_header_attribute3
390
391 ELSE
392 IF NOT JG_ZZ_AUTO_INVOICE.put_error_message ('JL'
393 , 'JL_BR_AR_RAXTRX_INT_DAY_INV'
394 , p_interface_line_id
395 , p_header_attribute3) THEN
396 RAISE error_condition;
397 END IF;
398
399 END IF;
400
401 END IF;
402
403 END IF; -- p_line_type = 'LINE'
404
405 ------------------------------------------------------------
406 -- header_attribute4 is the interest formula. --
407 -- Valid JLBR_INTEREST_FORMULA. Not Mandatory. --
408 ------------------------------------------------------------
409 IF p_line_type = 'LINE' THEN
410
411 IF p_header_attribute4 IS NOT NULL THEN
412
413 BEGIN
414 SELECT 'Success'
415 INTO dummy_code
416 FROM fnd_lookups
417 WHERE lookup_code = p_header_attribute4
418 AND lookup_type = 'JLBR_INTEREST_FORMULA'
419 AND enabled_flag = 'Y'
420 AND nvl(start_date_active,sysdate) <= sysdate
421 AND nvl(end_date_active,sysdate+1) >= sysdate;
422
423 EXCEPTION
424 WHEN NO_DATA_FOUND THEN
425 IF NOT JG_ZZ_AUTO_INVOICE.put_error_message ('JL'
426 , 'JL_BR_AR_RAXTRX_INT_FORM_INV'
427 , p_interface_line_id
428 , p_header_attribute4) THEN
429 RAISE error_condition;
430 END IF;
431 WHEN OTHERS THEN
432 RAISE error_condition;
433 END; -- p_header_attribute4
434
435 END IF;
436
437 END IF; -- p_line_type = 'LINE'
438
439 ------------------------------------------------------------
440 -- header_attribute5 is the grace days for the invoice --
441 -- Must be >= 0. Mandatory --
442 ------------------------------------------------------------
443 IF p_line_type = 'LINE' THEN
444
445 IF p_header_attribute5 IS NOT NULL THEN
446
447 BEGIN
448 SELECT 'Success'
449 INTO dummy_code
450 FROM dual
451 WHERE to_number (p_header_attribute5) >= 0;
452
453 EXCEPTION
454 WHEN NO_DATA_FOUND OR INVALID_NUMBER OR VALUE_ERROR THEN
455 IF NOT JG_ZZ_AUTO_INVOICE.put_error_message ('JL'
456 , 'JL_BR_AR_RAXTRX_INT_GRACE_INV'
457 , p_interface_line_id
458 , p_header_attribute5) THEN
459 RAISE error_condition;
460 END IF;
461 WHEN OTHERS THEN
462 RAISE error_condition;
463
464 END; -- p_header_attribute5
465
466 END IF;
467
468 END IF; -- p_line_type = 'LINE'
469
470
471 ------------------------------------------------------------
472 -- header_attribute6 is the interest formula. --
473 -- Valid JLBR_INTEREST_PENALTY_TYPE. Not Mandatory. --
474 ------------------------------------------------------------
475 IF p_line_type = 'LINE' THEN
476
477 IF p_header_attribute6 IS NOT NULL THEN
478
479 BEGIN
480 SELECT 'Success'
481 INTO dummy_code
482 FROM fnd_lookups
483 WHERE lookup_code = p_header_attribute6
484 AND lookup_type = 'JLBR_INTEREST_PENALTY_TYPE'
485 AND enabled_flag = 'Y'
486 AND nvl(start_date_active,sysdate) <= sysdate
487 AND nvl(end_date_active,sysdate+1) >= sysdate;
488
489 EXCEPTION
490 WHEN NO_DATA_FOUND THEN
491 IF NOT JG_ZZ_AUTO_INVOICE.put_error_message ('JL'
492 , 'JL_BR_AR_RAXTRX_PEN_TYPE_INV'
493 , p_interface_line_id
494 , p_header_attribute6) THEN
495 RAISE error_condition;
496 END IF;
497 WHEN OTHERS THEN
498 RAISE error_condition;
499
500 END; -- p_header_attribute6
501
502 END IF;
503
504 END IF; -- p_line_type = 'LINE'
505
506
507 ------------------------------------------------------------
508 -- header_attribute7 is the interest rate o amount for the--
509 -- invoice. --
510 -- If penalty type = 'R' then interest rate between 0 and --
511 -- 100. --
512 -- If penalty type = 'A' then interest amount >= 0. --
513 ------------------------------------------------------------
514 IF p_line_type = 'LINE' THEN
515
516 IF p_header_attribute6 = 'R' AND
517 p_header_attribute7 NOT BETWEEN 0 AND 100 THEN
518 IF NOT JG_ZZ_AUTO_INVOICE.put_error_message ('JL'
519 , 'JL_BR_AR_RAXTRX_PEN_RATE_INV'
520 , p_interface_line_id
521 , p_header_attribute7) THEN
522 RAISE error_condition;
523 END IF;
524
525 END IF;
526
527 IF p_header_attribute6 = 'A' AND p_header_attribute7 < 0 THEN
528 IF NOT JG_ZZ_AUTO_INVOICE.put_error_message ('JL'
529 , 'JL_BR_AR_RAXTRX_PEN_AMNT_INV'
530 , p_interface_line_id
531 , p_header_attribute7) THEN
532 RAISE error_condition;
533 END IF;
534
535 END IF;
536
537 END IF; -- p_line_type = 'LINE'
538
539 RETURN TRUE;
540
541 EXCEPTION
542 WHEN OTHERS THEN
543 RETURN FALSE;
544
545 END validate_interest_attributes;
546
547 FUNCTION validate_billing_attributes (p_interface_line_id IN NUMBER
548 , p_line_type IN VARCHAR2
549 , p_memo_line_id IN NUMBER
550 , p_inventory_item_id IN NUMBER
551 , p_header_attribute9 IN VARCHAR2
552 , p_header_attribute10 IN VARCHAR2
553 , p_header_attribute11 IN VARCHAR2
554 , p_header_attribute13 IN VARCHAR2
555 , p_header_attribute15 IN VARCHAR2
556 , p_header_attribute16 IN VARCHAR2
557 , p_header_attribute17 IN VARCHAR2
558 , p_line_attribute1 IN VARCHAR2
559 , p_line_attribute4 IN VARCHAR2
560 , p_line_attribute5 IN VARCHAR2
561 , p_line_attribute6 IN VARCHAR2
562 , p_line_attribute7 IN VARCHAR2)
563 RETURN BOOLEAN IS
564
565 dummy_code VARCHAR2 (15);
566 error_condition EXCEPTION;
567 x_return_context VARCHAR2(30);
568
569 BEGIN
570
571 BEGIN
572 SELECT return_context
573 INTO x_return_context
574 FROM oe_order_lines a, ra_interface_lines_gt b
575 WHERE a.line_id = b.interface_line_attribute6
576 AND b.interface_line_id = p_interface_line_id;
577
578 EXCEPTION WHEN OTHERS THEN
579 x_return_context := NULL;
580
581 END;
582
583 ------------------------------------------------------------
584 -- header_attribute9 is the freight accesory expense --
585 -- Numeric . No Mandatory --
586 ------------------------------------------------------------
587 IF p_line_type = 'LINE' THEN
588
589 BEGIN
590 -- Bug 9085547 Start
591 SELECT FND_NUMBER.canonical_to_number(nvl (p_header_attribute9, 999))
592 -- Bug 9085547 End
593 INTO dummy_code
594 FROM sys.dual;
595
596 EXCEPTION
597 WHEN INVALID_NUMBER OR VALUE_ERROR THEN
598 IF NOT JG_ZZ_AUTO_INVOICE.put_error_message ('JL'
599 , 'JL_BR_AR_RAXTRX_FRT_EXP_INV'
600 , p_interface_line_id
601 , p_header_attribute9) THEN
602 RAISE error_condition;
603 END IF;
604 WHEN OTHERS THEN
605 RAISE error_condition;
606 END; -- p_header_attribute9
607
608 END IF; -- p_line_type = 'LINE'
609
610
611 ------------------------------------------------------------
612 -- header_attribute10 is the Insurance accesory expense --
613 -- Numeric . No Mandatory --
614 ------------------------------------------------------------
615 IF p_line_type = 'LINE' THEN
616
617 BEGIN
618 -- Bug 9085547 Start
619 SELECT FND_NUMBER.canonical_to_number(nvl (p_header_attribute10, 999))
620 -- Bug 9085547 End
621 INTO dummy_code
622 FROM sys.dual;
623
624 EXCEPTION
625 WHEN INVALID_NUMBER OR VALUE_ERROR THEN
626 IF NOT JG_ZZ_AUTO_INVOICE.put_error_message ('JL'
627 , 'JL_BR_AR_RAXTRX_INS_EXP_INV'
628 , p_interface_line_id
629 , p_header_attribute10) THEN
630 RAISE error_condition;
631 END IF;
632 WHEN OTHERS THEN
633 RAISE error_condition;
634
635 END; -- p_header_attribute10
636
637 END IF; -- p_line_type = 'LINE'
638
639
640 ------------------------------------------------------------
641 -- header_attribute11 is the Other accessory expense --
642 -- Numeric . No Mandatory --
643 ------------------------------------------------------------
644 IF p_line_type = 'LINE' THEN
645
646 BEGIN
647 -- Bug 9085547 Start
648 SELECT FND_NUMBER.canonical_to_number(nvl (p_header_attribute11, 999))
649 -- Bug 9085547 End
650 INTO dummy_code
651 FROM sys.dual;
652
653 EXCEPTION
654 WHEN INVALID_NUMBER OR VALUE_ERROR THEN
655 IF NOT JG_ZZ_AUTO_INVOICE.put_error_message ('JL'
656 , 'JL_BR_AR_RAXTRX_OTH_EXP_INV'
657 , p_interface_line_id
658 , p_header_attribute11) THEN
659 RAISE error_condition;
660 END IF;
661 WHEN OTHERS THEN
662 RAISE error_condition;
663
664 END; --p_header_attribute11
665
666 END IF; -- p_line_type = 'LINE'
667
668
669 ------------------------------------------------------------
670 -- header_attribute13 is the Volume Quantity --
671 -- Numeric . No Mandatory --
672 ------------------------------------------------------------
673 IF p_line_type = 'LINE' THEN
674
675 BEGIN
676 -- Bug 9085547 Start
677 SELECT FND_NUMBER.canonical_to_number(nvl (p_header_attribute13, 999))
678 -- Bug 9085547 End
679 INTO dummy_code
680 FROM sys.dual;
681
682 EXCEPTION
683 WHEN INVALID_NUMBER OR VALUE_ERROR THEN
684 IF NOT JG_ZZ_AUTO_INVOICE.put_error_message ('JL'
685 , 'JL_BR_AR_RAXTRX_VOL_QTY_INV'
686 , p_interface_line_id
687 , p_header_attribute13) THEN
688 RAISE error_condition;
689 END IF;
690 WHEN OTHERS THEN
691 RAISE error_condition;
692 END; --p_header_attribute13
693
694 END IF; -- p_line_type = 'LINE'
695
696 ------------------------------------------------------------
697 -- header_attribute16 is the Total gross Weight --
698 -- Numeric . No Mandatory --
699 ------------------------------------------------------------
700 IF p_line_type = 'LINE' THEN
701
702 BEGIN
703 SELECT FND_NUMBER.canonical_to_number(nvl(p_header_attribute16, 999))
704 INTO dummy_code
705 FROM sys.dual;
706
707 EXCEPTION
708 WHEN INVALID_NUMBER OR VALUE_ERROR THEN
709 IF NOT JG_ZZ_AUTO_INVOICE.put_error_message ('JL'
710 , 'JL_BR_AR_RAXTRX_GRS_WGT_INV'
711 , p_interface_line_id
712 , p_header_attribute16) THEN
713 RAISE error_condition;
714 END IF;
715 WHEN OTHERS THEN
716 RAISE error_condition;
717
718 END; --p_header_attribute16
719
720 END IF; -- p_line_type = 'LINE'
721
722
723 ------------------------------------------------------------
724 -- header_attribute17 is the Total Net Weight --
725 -- Numeric . No Mandatory --
726 ------------------------------------------------------------
727 IF p_line_type = 'LINE' THEN
728
729 BEGIN
730 SELECT FND_NUMBER.canonical_to_number(nvl(p_header_attribute17, 999))
731 INTO dummy_code
732 FROM sys.dual;
733
734 EXCEPTION
735 WHEN INVALID_NUMBER OR VALUE_ERROR THEN
736 IF NOT JG_ZZ_AUTO_INVOICE.put_error_message ('JL'
737 , 'JL_BR_AR_RAXTRX_NET_WGT_INV'
738 , p_interface_line_id
739 , p_header_attribute17) THEN
740 RAISE error_condition;
741 END IF;
742 WHEN OTHERS THEN
743 RAISE error_condition;
744
745 END; --p_header_attribute17
746
747 END IF; -- p_line_type = 'LINE'
748
749
750 ------------------------------------------------------------
751 -- line_attribute1 is the operation fiscal code (CFO) --
752 -- Valid JL_AR_AP_OPERATIONS. Mandatory. --
753 ------------------------------------------------------------
754 IF p_line_type = 'LINE' THEN
755
756 IF p_line_attribute1 IS NOT NULL THEN
757
758 BEGIN
759 SELECT 'Success'
760 INTO dummy_code
761 FROM jl_br_ap_operations
762 WHERE cfo_code = p_line_attribute1;
763
764 EXCEPTION
765 WHEN NO_DATA_FOUND THEN
766 IF NOT JG_ZZ_AUTO_INVOICE.put_error_message ('JL'
767 , 'JL_BR_AR_RAXTRX_OP_FISC_INV'
768 , p_interface_line_id
769 , p_line_attribute1) THEN
770 RAISE error_condition;
771 END IF;
772 WHEN OTHERS THEN
773 RAISE error_condition;
774 END; -- p_line_attribute1
775 ELSE
776 IF x_return_context IS NULL THEN
777 IF NOT JG_ZZ_AUTO_INVOICE.put_error_message ('JL'
778 , 'JL_BR_AR_RAXTRX_OP_FISC_MND'
779 , p_interface_line_id
780 , p_line_attribute1) THEN
781 RAISE error_condition;
782 END IF;
783 ELSE
784 NULL;
785 END IF;
786
787 END IF;
788
789 END IF; -- p_line_type = 'LINE'
790
791
792 ------------------------------------------------------------
793 -- line_attribute4 is the item origin --
794 -- Mandatory if RA_INTERFACE_LINES.INVENTORY_ITEM_ID is --
795 -- not null or RA_INTERFACE_LINES.MEMO_LINE_ID is not null--
796 -- --
797 -- Valid ITEM_ORIGIN --
798 ------------------------------------------------------------
799 IF p_line_type = 'LINE' THEN
800
801 IF -- Bug 9864299 p_memo_line_id IS NOT NULL OR
802 p_inventory_item_id IS NOT NULL THEN
803
804 IF p_line_attribute4 IS NOT NULL THEN
805
806 BEGIN
807
808 /* Old Code Commented by Sierra on 03/29/99 for MLS */
809
810 -- SELECT 'Success'
811 -- INTO dummy_code
812 -- FROM jl_br_lookup_codes
813 -- WHERE lookup_code = p_line_attribute4
814 -- AND lookup_type = 'ITEM_ORIGIN'
815 -- AND nvl (inactive_date, sysdate + 1) > sysdate;
816
817 /* End of Old Code */
818
819 /* New Code for MLS by Sierra on 03/29/99 */
820 /* Lookup Type value modified by Sierra on 06/11/99 */
821
822 SELECT 'Success'
823 INTO dummy_code
824 FROM fnd_lookups
825 WHERE lookup_code = p_line_attribute4
826 AND lookup_type = 'JLBR_ITEM_ORIGIN'
827 AND nvl (end_date_active, sysdate + 1) > sysdate;
828
829 /* End of New Code */
830
831 EXCEPTION
832 WHEN NO_DATA_FOUND THEN
833 IF NOT JG_ZZ_AUTO_INVOICE.put_error_message ('JL'
834 , 'JL_BR_AR_RAXTRX_IT_ORIG_INV'
835 , p_interface_line_id
836 , p_line_attribute4) THEN
837 RAISE error_condition;
838 END IF;
839 WHEN OTHERS THEN
840 RAISE error_condition;
841
842 END; --p_line_attribute4
843 ELSE
844 IF x_return_context IS NULL THEN
845 IF NOT JG_ZZ_AUTO_INVOICE.put_error_message ('JL'
846 , 'JL_BR_AR_RAXTRX_IT_ORIG_MND'
847 , p_interface_line_id
848 , p_line_attribute4) THEN
849 RAISE error_condition;
850 END IF;
851 ELSE
852 NULL;
853 END IF;
854 END IF;
855
856 END IF;
857
858 END IF; -- p_line_type = 'LINE'
859
860
861 ------------------------------------------------------------
862 -- line_attribute5 is the Fiscal Type. --
863 -- Mandatory if RA_INTERFACE_LINES.INVENTORY_ITEM_ID is --
864 -- not null or RA_INTERFACE_LINES.MEMO_LINE_ID is not null--
865 -- --
866 -- Valid ITEM_FISCAL_TYPE --
867 ------------------------------------------------------------
868 IF p_line_type = 'LINE' THEN
869
870 IF -- Bug 9864299 p_memo_line_id IS NOT NULL OR
871 p_inventory_item_id IS NOT NULL THEN
872
873 IF p_line_attribute5 IS NOT NULL THEN
874
875 BEGIN
876
877 /* Old Code Commented By Sierra on 03/29/99 for MLS */
878
879 -- SELECT 'Success'
880 -- INTO dummy_code
881 -- FROM jl_br_lookup_codes
882 -- WHERE lookup_code = p_line_attribute5
883 -- AND lookup_type = 'ITEM_FISCAL_TYPE'
884 -- AND nvl (inactive_date, sysdate + 1) > sysdate;
885
886 /* End of Old Code */
887
888 /* New Code for MLS by Sierra on 03/29/99 */
889
890 SELECT 'Success'
891 INTO dummy_code
892 FROM fnd_lookups
893 WHERE lookup_code = p_line_attribute5
894 AND lookup_type = 'JLBR_ITEM_FISCAL_TYPE'
895 AND nvl (end_date_active, sysdate + 1) > sysdate;
896
897 /* End of New Code */
898
899 EXCEPTION
900 WHEN NO_DATA_FOUND THEN
901 IF NOT JG_ZZ_AUTO_INVOICE.put_error_message ('JL'
902 , 'JL_BR_AR_RAXTRX_IT_FISC_TP_INV'
903 , p_interface_line_id
904 , p_line_attribute5) THEN
905 RAISE error_condition;
906 END IF;
907 WHEN OTHERS THEN
908 RAISE error_condition;
909
910 END; -- p_line_attribute5
911 ELSE
912 IF x_return_context IS NULL THEN
913 IF NOT JG_ZZ_AUTO_INVOICE.put_error_message ('JL'
914 , 'JL_BR_AR_RAXTRX_IT_FISC_TP_MND'
915 , p_interface_line_id
916 , p_line_attribute5) THEN
917 RAISE error_condition;
918 END IF;
919 ELSE
920 NULL;
921 END IF;
922 END IF;
923
924 END IF;
925
926 END IF; -- p_line_type = 'LINE'
927
928
929 ------------------------------------------------------------
930 -- line_attribute6 is the Federal Tributary Situation --
931 -- Mandatory if RA_INTERFACE_LINES.INVENTORY_ITEM_ID is --
932 -- not null or RA_INTERFACE_LINES.MEMO_LINE_ID is not null--
933 -- --
934 -- Valid ITEM_FEDERAL_SITUATION --
935 ------------------------------------------------------------
936 IF p_line_type = 'LINE' THEN
937
938 IF -- Bug 9864299 p_memo_line_id IS NOT NULL OR
939 p_inventory_item_id IS NOT NULL THEN
940
941 IF p_line_attribute6 IS NOT NULL THEN
942
943 BEGIN
944
945 /* Old Code Commented by Sierra for MLS on 03/29/99 */
946
947 -- SELECT 'Success'
948 -- INTO dummy_code
949 -- FROM jl_br_lookup_codes
950 -- WHERE lookup_code = p_line_attribute6
951 -- AND lookup_type = 'ITEM_FEDERAL_SITUATION'
952 -- AND nvl (inactive_date, sysdate + 1) > sysdate;
953
954 /* End of Old Code */
955
956 /* New Code for MLS by Sierra on 03/29/99 */
957
958 SELECT 'Success'
959 INTO dummy_code
960 FROM fnd_lookups
961 WHERE lookup_code = p_line_attribute6
962 AND lookup_type = 'JLBR_ITEM_FEDERAL_SITUATION'
963 AND nvl (end_date_active, sysdate + 1) > sysdate;
964
965 /* End of New Code */
966
967 EXCEPTION
968 WHEN NO_DATA_FOUND THEN
969 IF NOT JG_ZZ_AUTO_INVOICE.put_error_message ('JL'
970 , 'JL_BR_AR_RAXTRX_FED_TR_SIT_INV'
971 , p_interface_line_id
972 , p_line_attribute6) THEN
973 RAISE error_condition;
974 END IF;
975 WHEN OTHERS THEN
976 RAISE error_condition;
977 END; -- p_line_attribute6
978 ELSE
979 IF x_return_context IS NULL THEN
980 IF NOT JG_ZZ_AUTO_INVOICE.put_error_message ('JL'
981 , 'JL_BR_AR_RAXTRX_FED_TR_SIT_MND'
982 , p_interface_line_id
983 , p_line_attribute5) THEN
984 RAISE error_condition;
985 END IF;
986 ELSE
987 NULL;
988 END IF;
989
990 END IF;
991
992 END IF;
993
994 END IF; -- p_line_type = 'LINE'
995
996
997 ------------------------------------------------------------
998 -- line_attribute7 is the State Tributary Situation --
999 -- Mandatory if RA_INTERFACE_LINES.INVENTORY_ITEM_ID is --
1000 -- not null or RA_INTERFACE_LINES.MEMO_LINE_ID is not null--
1001 -- --
1002 -- Valid ITEM_STATE_SITUATION --
1003 ------------------------------------------------------------
1004 IF p_line_type = 'LINE' THEN
1005
1006 IF -- Bug 9864299 p_memo_line_id IS NOT NULL OR
1007 p_inventory_item_id IS NOT NULL THEN
1008
1009 IF p_line_attribute7 IS NOT NULL THEN
1010
1011 BEGIN
1012
1013 /* Old Code Commented by Sierra for MLS on 03/29/99 */
1014
1015 -- SELECT 'Success'
1016 -- INTO dummy_code
1017 -- FROM jl_br_lookup_codes
1018 -- WHERE lookup_code = p_line_attribute7
1019 -- AND lookup_type = 'ITEM_STATE_SITUATION'
1020 -- AND nvl (inactive_date, sysdate + 1) > sysdate;
1021
1022 /* End of Old Code */
1023
1024 /* New Code for MLS by Sierra on 03/29/99 */
1025
1026 SELECT 'Success'
1027 INTO dummy_code
1028 FROM fnd_lookups
1029 WHERE lookup_code = p_line_attribute7
1030 AND lookup_type = 'JLBR_ITEM_STATE_SITUATION'
1031 AND nvl (end_date_active, sysdate + 1) > sysdate;
1032
1033 /* End of New Code */
1034
1035 EXCEPTION
1036 WHEN NO_DATA_FOUND THEN
1037 IF NOT JG_ZZ_AUTO_INVOICE.put_error_message ('JL'
1038 , 'JL_BR_AR_RAXTRX_STA_TR_SIT_INV'
1039 , p_interface_line_id
1040 , p_line_attribute7) THEN
1041 RAISE error_condition;
1042 END IF;
1043 WHEN OTHERS THEN
1044 RAISE error_condition;
1045
1046 END; -- p_line_attribute7
1047 ELSE
1048 IF x_return_context IS NULL THEN
1049 IF NOT JG_ZZ_AUTO_INVOICE.put_error_message ('JL'
1050 , 'JL_BR_AR_RAXTRX_STA_TR_SIT_MND'
1051 , p_interface_line_id
1052 , p_line_attribute5) THEN
1053 RAISE error_condition;
1054 END IF;
1055 ELSE
1056 NULL;
1057 END IF;
1058
1059 END IF; -- p_line_attribute7 is not null
1060
1061 END IF;
1062
1063 END IF; -- p_line_type = 'LINE'
1064
1065 RETURN TRUE;
1066
1067 EXCEPTION
1068 WHEN OTHERS THEN
1069 RETURN FALSE;
1070
1071 END validate_billing_attributes;
1072
1073 FUNCTION validate_gdff
1074 (p_request_id IN NUMBER,
1075 p_org_id IN NUMBER DEFAULT --Bugfix 2367111
1076 to_number(fnd_profile.value('ORG_ID'))
1077 ) RETURN NUMBER IS
1078
1079 return_code NUMBER (1);
1080 l_tax_method VARCHAR2(30);
1081 l_country_code VARCHAR2(2);
1082 l_org_id NUMBER;
1083 l_rowid ROWID;
1084 -- BUG 10184720
1085 l_tow_veh_plate_num2 JL_BR_CUSTOMER_TRX_EXTS.TOWING_VEH_PLATE_NUMBER%TYPE;
1086 l_tow_veh_plate_state_code2 JL_BR_CUSTOMER_TRX_EXTS.TOWING_VEH_PLATE_STATE_CODE%TYPE;
1087 l_tow_veh_plate_num3 JL_BR_CUSTOMER_TRX_EXTS.TOWING_VEH_PLATE_NUMBER%TYPE;
1088 l_tow_veh_plate_state_code3 JL_BR_CUSTOMER_TRX_EXTS.TOWING_VEH_PLATE_STATE_CODE%TYPE;
1089 l_tow_veh_plate_num4 JL_BR_CUSTOMER_TRX_EXTS.TOWING_VEH_PLATE_NUMBER%TYPE;
1090 l_tow_veh_plate_state_code4 JL_BR_CUSTOMER_TRX_EXTS.TOWING_VEH_PLATE_STATE_CODE%TYPE;
1091 l_tow_veh_plate_num5 JL_BR_CUSTOMER_TRX_EXTS.TOWING_VEH_PLATE_NUMBER%TYPE;
1092 l_tow_veh_plate_state_code5 JL_BR_CUSTOMER_TRX_EXTS.TOWING_VEH_PLATE_STATE_CODE%TYPE;
1093 l_count NUMBER;
1094 l_service_exec_date VARCHAR2(30); -- BUG 11825724
1095 l_so_org_id NUMBER;
1096 -- BUG 10184720
1097 --Modified for BrSPED Additional Towing Attributes
1098 CURSOR trx_lines_cursor (c_request_id NUMBER) IS
1099 SELECT interface_line_id
1100 , interface_line_context
1101 , interface_line_attribute3
1102 , interface_line_attribute6 -- BUG 11825724
1103 , cust_trx_type_id trx_type
1104 , trx_date
1105 , nvl(orig_system_ship_address_id , orig_system_bill_address_id)
1106 orig_system_address_id
1107 , line_type
1108 , memo_line_id
1109 , inventory_item_id
1110 , header_gdf_attribute1
1111 , header_gdf_attribute2
1112 , header_gdf_attribute3
1113 , header_gdf_attribute4
1114 , header_gdf_attribute5
1115 , header_gdf_attribute6
1116 , header_gdf_attribute7
1117 , header_gdf_attribute8
1118 , header_gdf_attribute9
1119 , header_gdf_attribute10
1120 , header_gdf_attribute11
1121 , header_gdf_attribute12
1122 , header_gdf_attribute13
1123 , header_gdf_attribute14
1124 , header_gdf_attribute15
1125 , header_gdf_attribute16
1126 , header_gdf_attribute17
1127 , line_gdf_attribute1
1128 , line_gdf_attribute2
1129 , line_gdf_attribute3
1130 -- nipatel commented out because the columns below will not be added to ra_interface_lines
1131 -- , product_fisc_classification
1132 -- , product_category
1133 -- , trx_business_category
1134 , line_gdf_attribute4
1135 , line_gdf_attribute5
1136 , line_gdf_attribute6
1137 , line_gdf_attribute7
1138 , line_gdf_attribute8
1139 , line_gdf_attribute9
1140 , line_gdf_attribute10
1141 , line_gdf_attribute11
1142 , line_gdf_attribute12
1143 , warehouse_id
1144 , batch_source_name
1145 , trx_number
1146 FROM ra_interface_lines_gt
1147 WHERE request_id = c_request_id
1148 ORDER BY trx_date;
1149
1150 ------------------------------------------------------------
1151 ----- Tax validation function.
1152 ----------------------------------------------------------
1153
1154
1155 ------------------------------------------------------------
1156 -- Interest validation function --
1157 ------------------------------------------------------------
1158
1159
1160 ------------------------------------------------------------
1161 -- Billing validation function --
1162 ------------------------------------------------------------
1163
1164 ------------------------------------------------------------
1165 -- Main function body. --
1166 ------------------------------------------------------------
1167
1168
1169 BEGIN
1170
1171 ------------------------------------------------------------
1172 -- Let's assume everything is OK --
1173 ------------------------------------------------------------
1174 --arp_standard.debug('JL_ZZ_AUTO_INVOICE.validate_gdff()+');
1175 IF PG_DEBUG = 'Y' THEN
1176 arp_util_tax.debug('JL_ZZ_AUTO_INVOICE.validate_gdff()+');
1177 END IF;
1178
1179 return_code := 1;
1180 --Bug Fix 2367111
1181 --l_country_code := FND_PROFILE.VALUE('JGZZ_COUNTRY_CODE');
1182 l_org_id := MO_GLOBAL.get_current_org_id;
1183 l_country_code := JG_ZZ_SHARED_PKG.GET_COUNTRY(l_org_id, null);
1184
1185 --arp_standard.debug('-- Country Code: '||l_country_code);
1186 --arp_standard.debug('-- Request Id: '||to_char(p_request_id));
1187 IF PG_DEBUG = 'Y' THEN
1188 arp_util_tax.debug('validate_gdff: ' || '-- Country Code: '||l_country_code);
1189 arp_util_tax.debug('validate_gdff: ' || '-- Request Id: '||to_char(p_request_id));
1190 END IF;
1191
1192 ------------------------------------------------------------
1193 -- Validate all the rows for this concurrent request --
1194 ------------------------------------------------------------
1195 FOR trx_line_record IN trx_lines_cursor (p_request_id)
1196 LOOP
1197
1198 IF l_country_code IN ('BR','AR','CO') THEN
1199
1200 l_tax_method := JL_ZZ_AR_TX_LIB_PKG.get_tax_method(l_org_id);
1201
1202 --arp_standard.debug('-- Tax Method: '||l_tax_method);
1203 IF PG_DEBUG = 'Y' THEN
1204 arp_util_tax.debug('validate_gdff: ' || '-- Tax Method: '||l_tax_method);
1205 END IF;
1206
1207 IF l_tax_method = 'LTE' THEN
1208
1209
1210
1211 IF NOT validate_tax_attributes (trx_line_record.interface_line_id
1212 , trx_line_record.line_type
1213 , trx_line_record.memo_line_id
1214 , trx_line_record.inventory_item_id
1215 , trx_line_record.line_gdf_attribute2
1216 , trx_line_record.line_gdf_attribute3
1217 , trx_line_record.line_gdf_attribute3
1218 , trx_line_record.line_gdf_attribute11
1219 , trx_line_record.line_gdf_attribute12
1220 , trx_line_record.orig_system_address_id
1221 , trx_line_record.warehouse_id) THEN
1222
1223 --arp_standard.debug('-- validate_tax_attributes routine failed');
1224 IF PG_DEBUG = 'Y' THEN
1225 arp_util_tax.debug('validate_gdff: ' || '-- validate_tax_attributes routine failed');
1226 END IF;
1227 return_code := 0;
1228 END IF; -- Validate tax
1229 END IF; -- l_tax_method check
1230 END IF; -- Tax method check
1231
1232 IF l_country_code = 'BR' THEN
1233 -- BUG 10184720
1234 -- Processing for Additional Towing Vehicle Attributes
1235 -- Start
1236 -------------------------------------------------------------------
1237 -- Logic to process Data coming from Order Management for --
1238 -- the Additional Towing Vehicle Attributes captured at Shipping --
1239 -- transaction GDF --
1240 -------------------------------------------------------------------
1241 IF trx_line_record.line_type = 'LINE' AND trx_line_record.interface_line_context = 'ORDER ENTRY' THEN
1242 /* Fetching RowID to be stored as reference in JL Interface
1243 Extension table */
1244 BEGIN
1245 SELECT ROWID INTO l_rowid FROM ra_interface_lines_all
1246 WHERE interface_line_id = trx_line_record.interface_line_id;
1247 EXCEPTION
1248 WHEN OTHERS THEN
1249 l_rowid := '999';
1250 END;
1251 fnd_file.put_line(fnd_file.log,'JL msg: RowID fetched');
1252
1253 /* Fetching Additional Towing Attributes from Shipping GDF using
1254 Unique delivery Name */
1255 BEGIN
1256 SELECT global_attribute12,
1257 global_attribute13,
1258 global_attribute14,
1259 global_attribute15,
1260 global_attribute16,
1261 global_attribute17,
1262 global_attribute18,
1263 global_attribute19
1264 INTO l_tow_veh_plate_num2,
1265 l_tow_veh_plate_state_code2,
1266 l_tow_veh_plate_num3,
1267 l_tow_veh_plate_state_code3,
1268 l_tow_veh_plate_num4,
1269 l_tow_veh_plate_state_code4,
1270 l_tow_veh_plate_num5,
1271 l_tow_veh_plate_state_code5
1272 FROM wsh_new_deliveries
1273 WHERE name = trx_line_record.interface_line_attribute3;
1274 EXCEPTION
1275 WHEN OTHERS THEN
1276 l_tow_veh_plate_num2 := NULL;
1277 l_tow_veh_plate_state_code2 := NULL;
1278 l_tow_veh_plate_num3 := NULL;
1279 l_tow_veh_plate_state_code3 := NULL;
1280 l_tow_veh_plate_num4 := NULL;
1281 l_tow_veh_plate_state_code4 := NULL;
1282 l_tow_veh_plate_num5 := NULL;
1283 l_tow_veh_plate_state_code5 := NULL;
1284 END;
1285
1286 -- BUG 11825724
1287 -- START
1288 l_so_org_id := to_number(oe_profile.value('SO_ORGANIZATION_ID'));
1289 BEGIN
1290 SELECT fnd_date.canonical_to_date(ol.global_attribute16) --service_execution_date
1291 INTO l_service_exec_date
1292 FROM oe_order_lines ol,
1293 mtl_system_items msi
1294 WHERE line_id = trx_line_record.interface_line_attribute6
1295 AND msi.inventory_item_id(+) = trx_line_record.inventory_item_id
1296 AND msi.organization_id = l_so_org_id;
1297 EXCEPTION
1298 WHEN OTHERS THEN
1299 l_service_exec_date := NULL;
1300 END;
1301
1302 -- BUG 11825724
1303 -- END
1304
1305 IF l_rowid IS NOT NULL THEN
1306 fnd_file.put_line(fnd_file.log,'JL msg: Row ID '||l_rowid);
1307
1308 -- Checking if Record already exists in JL Extension Table
1309 SELECT count(*) INTO l_count FROM JL_BR_INTERFACE_LINES_EXTS
1310 WHERE JL_BR_INTERFACE_LINK_ID = l_rowid;
1311 fnd_file.put_line(fnd_file.log,'JL msg: Count '||l_count);
1312 IF l_count = 0 THEN
1313 -- Inserting Data into JL Extension Table
1314 INSERT INTO JL_BR_INTERFACE_LINES_EXTS (JL_BR_INTERFACE_LINK_ID,
1315 HEADER_GDF_ATTRIBUTE31,
1316 HEADER_GDF_ATTRIBUTE32,
1317 HEADER_GDF_ATTRIBUTE33,
1318 HEADER_GDF_ATTRIBUTE34,
1319 HEADER_GDF_ATTRIBUTE35,
1320 HEADER_GDF_ATTRIBUTE36,
1321 HEADER_GDF_ATTRIBUTE37,
1322 HEADER_GDF_ATTRIBUTE38,
1323 HEADER_GDF_ATTRIBUTE39,--BUG 11825724
1324 LAST_UPDATE_DATE,
1325 LAST_UPDATED_BY,
1326 LAST_UPDATE_LOGIN,
1327 CREATION_DATE,
1328 CREATED_BY
1329 )
1330 VALUES (l_rowid,
1331 l_tow_veh_plate_num2,
1332 l_tow_veh_plate_state_code2,
1333 l_tow_veh_plate_num3,
1334 l_tow_veh_plate_state_code3,
1335 l_tow_veh_plate_num4,
1336 l_tow_veh_plate_state_code4,
1337 l_tow_veh_plate_num5,
1338 l_tow_veh_plate_state_code5,
1339 l_service_exec_date,--BUG 11825724
1340 SYSDATE,
1341 FND_GLOBAL.USER_ID,
1342 FND_GLOBAL.LOGIN_ID,
1343 SYSDATE,
1344 FND_GLOBAL.USER_ID
1345 );
1346 END IF; -- l_count check
1347 END IF; -- l_rowid check
1348 END IF; --main
1349 -- End
1350
1351 IF NOT validate_interest_attributes
1352 ( trx_line_record.interface_line_id
1353 , trx_line_record.line_type
1354 , trx_line_record.header_gdf_attribute1
1355 , trx_line_record.header_gdf_attribute2
1356 , trx_line_record.header_gdf_attribute3
1357 , trx_line_record.header_gdf_attribute4
1358 , trx_line_record.header_gdf_attribute5
1359 , trx_line_record.header_gdf_attribute6
1360 , trx_line_record.header_gdf_attribute7) THEN
1361
1362 --arp_standard.debug('-- validate_interest_attributes routine failed');
1363 IF PG_DEBUG = 'Y' THEN
1364 arp_util_tax.debug('validate_gdff: ' || '-- validate_interest_attributes routine failed');
1365 END IF;
1366
1367 return_code := 0;
1368 END IF; -- Validate interest
1369
1370 IF NOT validate_billing_attributes (trx_line_record.interface_line_id
1371 , trx_line_record.line_type
1372 , trx_line_record.memo_line_id
1373 , trx_line_record.inventory_item_id
1374 , trx_line_record.header_gdf_attribute9
1375 , trx_line_record.header_gdf_attribute10
1376 , trx_line_record.header_gdf_attribute11
1377 , trx_line_record.header_gdf_attribute13
1378 , trx_line_record.header_gdf_attribute15
1379 , trx_line_record.header_gdf_attribute16
1380 , trx_line_record.header_gdf_attribute17
1381 , trx_line_record.line_gdf_attribute1
1382 , trx_line_record.line_gdf_attribute4
1383 , trx_line_record.line_gdf_attribute5
1384 , trx_line_record.line_gdf_attribute6
1385 , trx_line_record.line_gdf_attribute7) THEN
1386
1387 --arp_standard.debug('-- validate_billing_attributes routine failed');
1388 IF PG_DEBUG = 'Y' THEN
1389 arp_util_tax.debug('validate_gdff: ' || '-- validate_billing_attributes routine failed');
1390 END IF;
1391
1392 return_code := 0;
1393 END IF; -- Validate billing
1394
1395 ELSIF l_country_code = 'AR' THEN
1396 ------------------------------------------------------------
1397 -- Validate all the rows for this concurrent request --
1398 ------------------------------------------------------------
1399 IF NOT JL_AR_DOC_NUMBERING_PKG.validate_interface_lines
1400 ( p_request_id
1401 , trx_line_record.interface_line_id
1402 , trx_line_record.trx_type
1403 , trx_line_record.inventory_item_id
1404 , trx_line_record.memo_line_id
1405 , trx_line_record.trx_date
1406 , trx_line_record.orig_system_address_id
1407 , trx_line_record.warehouse_id
1408 ) THEN
1409
1410 --arp_standard.debug('-- JL_AR_DOC_NUMBERING_PKG.'||'validate_interface_lines routine failed');
1411 IF PG_DEBUG = 'Y' THEN
1412 arp_util_tax.debug('validate_gdff: ' || '-- JL_AR_DOC_NUMBERING_PKG.'||'validate_interface_lines routine failed');
1413 END IF;
1414
1415 return_code := 0;
1416 END IF; -- Validate interface lines
1417 END IF;
1418
1419 END LOOP;
1420
1421 --arp_standard.debug('-- Return Code: '||to_char(return_code));
1422 --arp_standard.debug('JL_ZZ_AUTO_INVOICE.validate_gdff()-');
1423 IF PG_DEBUG = 'Y' THEN
1424 arp_util_tax.debug('validate_gdff: ' || '-- Return Code: '||to_char(return_code));
1425 arp_util_tax.debug('JL_ZZ_AUTO_INVOICE.validate_gdff()-');
1426 END IF;
1427
1428 RETURN return_code;
1429
1430 EXCEPTION
1431 WHEN OTHERS THEN
1432
1433 --arp_standard.debug('-- Return From Exception when others');
1434 --arp_standard.debug('-- Return Code: 0');
1435 --arp_standard.debug('JL_ZZ_AUTO_INVOICE.validate_gdff()-');
1436 IF PG_DEBUG = 'Y' THEN
1437 arp_util_tax.debug('validate_gdff: ' || '-- Return From Exception when others');
1438 arp_util_tax.debug('validate_gdff: ' || '-- Return Code: 0');
1439 arp_util_tax.debug('JL_ZZ_AUTO_INVOICE.validate_gdff()-');
1440 END IF;
1441
1442 RETURN 0;
1443
1444 END validate_gdff;
1445
1446 FUNCTION trx_num_upd
1447 (p_batch_source_id IN NUMBER,
1448 p_trx_number IN VARCHAR2,
1449 p_org_id IN NUMBER DEFAULT --Bugfix 2367111
1450 to_number(fnd_profile.value('ORG_ID'))
1451 ) RETURN VARCHAR2 IS
1452
1453 l_trx_number ra_customer_trx_all.trx_number%TYPE;
1454 l_country_code VARCHAR2(2);
1455 l_org_id NUMBER;
1456
1457 BEGIN
1458 --Bug Fix 2367111
1459 --l_country_code := FND_PROFILE.VALUE('JGZZ_COUNTRY_CODE');
1460 l_org_id := mo_global.get_current_org_id;
1461 l_country_code := JG_ZZ_SHARED_PKG.GET_COUNTRY(l_org_id, null);
1462
1463 IF l_country_code = 'AR' THEN
1464 l_trx_number := JL_AR_DOC_NUMBERING_PKG.trx_num_gen(p_batch_source_id,
1465 p_trx_number);
1466 END IF;
1467
1468 RETURN l_trx_number;
1469
1470 EXCEPTION
1471 WHEN OTHERS THEN
1472 RAISE;
1473 END trx_num_upd;
1474
1475
1476 FUNCTION jl_br_cm_upd_inv_status (p_request_id IN number) RETURN NUMBER is -- Added for bug 9183563
1477 l_status_code number := 1;
1478
1479 BEGIN
1480
1481 update ra_customer_trx
1482 set status_trx ='VD'
1483 where customer_trx_id in (select trx.previous_customer_trx_id
1484 from ra_customer_trx trx,
1485 ra_cust_trx_types trx_type
1486 where trx.cust_trx_type_id = trx_type.cust_trx_type_id
1487 and trx.request_id = p_request_id
1488 and trx_type.type = 'CM'
1489 and trx_type.default_status = 'VD'
1490 and trx.previous_customer_trx_id is not null);
1491
1492
1493 return l_status_code ;
1494
1495 EXCEPTION
1496 WHEN OTHERS THEN
1497 l_status_code := 0;
1498 fnd_file.put_line(fnd_file.log,'IN jl_br_cm_upd_inv_status :'||SQLCODE||'-'||SQLERRM);
1499 return l_status_code;
1500 END jl_br_cm_upd_inv_status;
1501
1502
1503 END JL_ZZ_AUTO_INVOICE;
[Home] [Help]