eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.JL_ZZ_RECEIV_INTERFACE

Source


1 PACKAGE BODY JL_ZZ_RECEIV_INTERFACE AS
2 /* $Header: jlzzorib.pls 120.21.12010000.4 2008/11/25 16:22:08 pakumare ship $ */
3 
4 /*----------------------------------------------------------------------------*
5  |   PRIVATE FUNCTIONS/PROCEDURES                                              |
6  *----------------------------------------------------------------------------*/
7 
8 PROCEDURE init_gdf (
9         p_line_rec     IN OUT NOCOPY OE_Invoice_PUB.OE_GDF_Rec_Type );
10 
11 /*----------------------------------------------------------------------------*
12  |   PUBLIC FUNCTIONS/PROCEDURES             |
13  *----------------------------------------------------------------------------*/
14 
15 /*----------------------------------------------------------------------------*
16  | PROCEDURE                                                                  |
17  |    copy_gdff                                    	      |
18  |                                                                            |
19  | DESCRIPTION                                                                |
20  |    For each row created in the current submission of OM Invoicing process, |
21  |    this procedure copies required global attribute columns to the interface|
22  |    table. 				                                      |
23  |									      |
24  | PARAMETERS                                                                 |
25  |   INPUT                                                 		      |
26  |      p_interface_line_rec    OE_Invoice_PUB.OE_GDF_Rec_Type                |
27  |                              Interface line record declared in OEXPINVS.pls|
28  |   OUTPUT                                                		      |
29  |      x_interface_line_rec    OE_Invoice_PUB.OE_GDF_Rec_Type                |
30  |                              Interface line record declared in OEXPINVS.pls|
31  |      x_error_buffer          VARCHAR2 -- Error Message  	              |
32  |      x_return_code         	NUMBER   -- Error Code.           	      |
33  |                                          0 - Success, 2 - Failure. 	      |
34  |                                                                            |
35  | HISTORY                                                                    |
36  |                                                                            |
37  |   24-JAN-2000 Harsh Takle      Created.                                    |
38  |   18-FEB-2000 Satyadeep Chandrashekar     Modified to split the SQL into   |
39  |                                           two - one for OM and one for     |
40  |                                           shipping (bug 1202351)           |
41  |    31-AUG-2000  Satyadeep Chandrashekar   Modified Bug 1395885             |
42  |    04-OCT-2008  Palaniyandi Kumaresan     Modified for Brazilian SPED      |
43  *----------------------------------------------------------------------------*/
44 PROCEDURE copy_gdff (
45         p_interface_line_rec IN     OE_Invoice_PUB.OE_GDF_Rec_Type,
46         x_interface_line_rec IN OUT NOCOPY OE_Invoice_PUB.OE_GDF_Rec_Type,
47         x_return_code        IN OUT NOCOPY NUMBER,
48 	x_error_buffer       IN OUT NOCOPY VARCHAR2) IS
49 
50   l_so_organization_id 	 NUMBER(15);
51   l_country_code 	 VARCHAR2(2);
52   l_tax_method           VARCHAR2(30);
53 
54   l_header_attr_category VARCHAR2(30);
55   l_line_attr_category   VARCHAR2(30);
56 
57   l_volume_qty            VARCHAR2(150);
58   l_volume_type           VARCHAR2(150);
59   l_volume_number         VARCHAR2(150);
60   l_vehicle_number        VARCHAR2(150);
61   l_gross_weight          NUMBER;
62   l_net_weight            NUMBER;
63   l_item_origin           VARCHAR2(150);
64   l_item_fiscal_type      VARCHAR2(150);
65   l_fsc_cls_code          VARCHAR2(30);
66   l_trx_cls_code          VARCHAR2(30);
67   l_inventory_item_id     NUMBER;
68   l_line_id               NUMBER;
69   l_order_line_id         NUMBER;
70   l_invoice_line_id       NUMBER;
71   l_op_fiscal_code        VARCHAR2(5);
72   l_fed_trib_situation    VARCHAR2(25);
73   l_sta_trib_situation    VARCHAR2(25);
74 
75   l_org_id                NUMBER;
76   l_jl                    VARCHAR2(2);
77 
78   /* Added for Brazilian SPED */
79    l_veh_plate_state_code VARCHAR2(5);
80    l_veh_antt_inscr       VARCHAR2(50);
81    l_tow_veh_plate_num    VARCHAR2(50);
82    l_tow_veh_plate_state_code VARCHAR2(5);
83    l_tow_veh_antt_inscr   VARCHAR2(50);
84    l_seal_number          VARCHAR2(150);
85   /*End*/
86 
87   CURSOR read_gdffs IS
88     SELECT ol.global_attribute1    op_fiscal_code,
89            ol.global_attribute2    freight_acc_expense,
90            ol.global_attribute3    insurance_acc_expense,
91            ol.global_attribute4    other_acc_expense,
92            ol.global_attribute5    item_line_fiscal_class,
93            ol.global_attribute6    item_line_trx_reason,
94            msi.global_attribute3   item_origin,
95            msi.global_attribute4   item_fiscal_type,
96            msi.global_attribute5   fed_trib_situation,
97            msi.global_attribute6   sta_trib_situation
98     FROM   oe_order_lines ol,
99            mtl_system_items msi
100     WHERE  line_id = l_line_id
101     AND    msi.inventory_item_id(+) = l_inventory_item_id
102     AND    msi.organization_id      = l_so_organization_id;
103 
104   read_gdffs_rec  read_gdffs%ROWTYPE;
105 
106 BEGIN
107 
108   x_return_code := 0;
109   x_error_buffer := NULL;
110   x_interface_line_rec := p_interface_line_rec;
111   --Following line is commented as a part of bug 2133665
112   --l_country_code := fnd_profile.value('JGZZ_COUNTRY_CODE');
113   --
114   l_country_code := SUBSTR(x_interface_line_rec.line_gdf_attr_category,4,2);
115 
116   l_jl := 'JL';
117   --
118   OE_DEBUG_PUB.ADD('JL-Country Code is ' || l_country_code);
119 
120   IF NVL(l_country_code,'$') IN ('BR','AR','CO') THEN
121 
122      l_tax_method := JL_ZZ_AR_TX_LIB_PKG.get_tax_method(l_org_id);
123      OE_DEBUG_PUB.ADD('JL-Tax Method is ' || l_tax_method);
124 
125      l_so_organization_id:= to_number(oe_profile.value('SO_ORGANIZATION_ID'));
126      OE_DEBUG_PUB.ADD('JL-So Organization id is ' || l_so_organization_id);
127 
128      IF l_country_code = 'BR' THEN
129         l_header_attr_category :=  'JL.BR.ARXTWMAI.Additional Info';
130         l_line_attr_category   :=  'JL.BR.ARXTWMAI.Additional Info';
131      ELSE
132       --  l_header_attr_category :=  'JL'||'.'||l_country_code||'.ARXTWMAI.HEADER';
133         l_header_attr_category :=  l_jl||'.'||l_country_code||'.ARXTWMAI.HEADER';
134       --  l_line_attr_category   := 'JL'||'.'||l_country_code||'.ARXTWMAI.LINES';
135        l_line_attr_category   := l_jl||'.'||l_country_code||'.ARXTWMAI.LINES';
136      END IF;
137 
138      l_line_id := p_interface_line_rec.interface_line_attribute6;
139      l_inventory_item_id := p_interface_line_rec.inventory_item_id;
140 
141      OE_DEBUG_PUB.ADD('JL-Line Id '|| l_line_id);
142      OE_DEBUG_PUB.ADD('JL-Inventory Item Id '|| l_inventory_item_id);
143      OE_DEBUG_PUB.ADD('JL-Line Type '|| p_interface_line_rec.line_type);
144 
145      l_invoice_line_id := NULL;
146      l_order_line_id := NULL;
147      IF l_tax_method = 'LTE' THEN
148 
149        --
150        -- Bug#5588076- init GDF for LTE
151        --
152        init_gdf(x_interface_line_rec);
153 
154        BEGIN
155          SELECT REFERENCE_CUSTOMER_TRX_LINE_ID,
156                 REFERENCE_LINE_ID
157          INTO   l_invoice_line_id,
158                 l_order_line_id
159          FROM   oe_order_lines
160          WHERE  line_id = l_line_id;
161        EXCEPTION
162          WHEN OTHERS THEN
163               l_invoice_line_id := NULL;
164               l_order_line_id := NULL;
165        END;
166 
167      END IF;
168 
169      OE_DEBUG_PUB.ADD('JL-Invoice Line Id '|| l_invoice_line_id);
170      OE_DEBUG_PUB.ADD('JL-Return Id '|| l_order_line_id);
171 
172      IF (l_invoice_line_id IS NULL AND
173          l_order_line_id IS NULL) THEN
174 
175         /* Copy Global DFF columns from OE tables */
176         IF l_country_code = 'BR' THEN
177 
178            OPEN read_gdffs;
179            LOOP
180 
181              FETCH read_gdffs INTO read_gdffs_rec;
182 
183              EXIT WHEN read_gdffs%NOTFOUND OR
184                        read_gdffs%NOTFOUND is NULL;
185 
186              IF l_tax_method = 'LTE' THEN
187                 l_fsc_cls_code := read_gdffs_rec.item_line_fiscal_class;
188                 l_trx_cls_code := read_gdffs_rec.item_line_trx_reason;
189              ELSE
190                 l_fsc_cls_code := NULL;
191                 l_trx_cls_code := NULL;
192              END IF;
193 
194              IF p_interface_line_rec.line_type = 'LINE' THEN
195 
196                 BEGIN
197                   OE_DEBUG_PUB.ADD('JL-Delivery Name '||
198                                 p_interface_line_rec.interface_line_attribute3);
199                   SELECT del.gross_weight,
200                          del.net_weight,
201                          del.global_attribute3,
202                          del.global_attribute1,
203                          del.global_attribute2,
204                          del.global_attribute4,
205                          del.global_attribute5,
206                          del.global_attribute6,
207                          del.global_attribute7,
208                          del.global_attribute8,
209                          del.global_attribute9
210                   INTO   l_gross_weight,
211                          l_net_weight,
212                          l_volume_qty,
213                          l_volume_type,
214                          l_volume_number,
215                          l_veh_plate_state_code,
216                          l_veh_antt_inscr,
217                          l_tow_veh_plate_num,
218                          l_tow_veh_plate_state_code,
219                          l_tow_veh_antt_inscr,
220                          l_seal_number
221                   FROM   wsh_new_deliveries del
222                   WHERE  del.name =
223                                  p_interface_line_rec.interface_line_attribute3;
224 
225                 EXCEPTION
226                   WHEN OTHERS THEN
227                        l_gross_weight := NULL;
228                        l_net_weight := NULL;
229                        l_volume_qty := NULL;
230                        l_volume_type := NULL;
231                        l_volume_number := NULL;
232                        l_veh_plate_state_code := NULL;
233                        l_veh_antt_inscr := NULL;
234                        l_tow_veh_plate_num := NULL;
235                        l_tow_veh_plate_state_code := NULL;
236                        l_tow_veh_antt_inscr := NULL;
237                        l_seal_number := NULL;
238                 END;
239 
240                 BEGIN
241                   SELECT t.vehicle_number
242                   INTO   l_vehicle_number
243                   FROM   wsh_new_deliveries del,
244                          wsh_delivery_legs dl,
245                          wsh_trip_stops pickup_stop,
246                          wsh_trips t
247                   WHERE  del.name =
248                                  p_interface_line_rec.interface_line_attribute3
249                   AND    del.delivery_id = dl.delivery_id
250                   AND    dl.pick_up_stop_id = pickup_stop.stop_id
251                   AND    pickup_stop.trip_id = t.trip_id
252                   AND    pickup_stop.actual_departure_date =
253                                (SELECT min(pickup_stop_x.actual_departure_date)
254                                 FROM  wsh_new_deliveries del_x,
255                                       wsh_delivery_legs dl_x,
256                                       wsh_trip_stops pickup_stop_x,
257                                       wsh_trips tx
258                                 WHERE del_x.delivery_id = del.delivery_id
259                                 AND   del_x.delivery_id = dl_x.delivery_id
260                                 AND   dl_x.pick_up_stop_id =
261                                                            pickup_stop_x.stop_id
262                                 AND   pickup_stop_x.trip_id = tx.trip_id
263                                 );
264                 EXCEPTION
265                   WHEN OTHERS THEN
266                        l_vehicle_number := NULL;
267                 END;
268 
269              END IF;
270 
271              x_interface_line_rec.header_gdf_attribute9 :=
272                                           read_gdffs_rec.freight_acc_expense;
273        	     x_interface_line_rec.header_gdf_attribute10 :=
274                                           read_gdffs_rec.insurance_acc_expense;
275        	     x_interface_line_rec.header_gdf_attribute11 :=
276                                           read_gdffs_rec.other_acc_expense;
277        	     x_interface_line_rec.header_gdf_attribute12 := l_vehicle_number;
278        	     x_interface_line_rec.header_gdf_attribute13 := l_volume_qty;
279        	     x_interface_line_rec.header_gdf_attribute14 := l_volume_type;
280        	     x_interface_line_rec.header_gdf_attribute15 := l_volume_number;
281        	     x_interface_line_rec.header_gdf_attribute16 := fnd_number.number_to_canonical(l_gross_weight);
282        	     x_interface_line_rec.header_gdf_attribute17 := fnd_number.number_to_canonical(l_net_weight);
283              x_interface_line_rec.header_gdf_attribute21 := l_veh_plate_state_code;
284              x_interface_line_rec.header_gdf_attribute22 := l_veh_antt_inscr;
285              x_interface_line_rec.header_gdf_attribute23 := l_tow_veh_plate_num;
286              x_interface_line_rec.header_gdf_attribute24 := l_tow_veh_plate_state_code;
287              x_interface_line_rec.header_gdf_attribute25 := l_tow_veh_antt_inscr;
288              x_interface_line_rec.header_gdf_attribute26 := l_seal_number;
289 
290              x_interface_line_rec.header_gdf_attr_category :=
291                                           l_header_attr_category;
292        	     x_interface_line_rec.line_gdf_attribute1 :=
293                                           read_gdffs_rec.op_fiscal_code;
294        	     x_interface_line_rec.line_gdf_attribute4 :=
295                                           read_gdffs_rec.item_origin;
296        	     x_interface_line_rec.line_gdf_attribute5 :=
297                                           read_gdffs_rec.item_fiscal_type;
298        	     x_interface_line_rec.line_gdf_attribute6 :=
299                                           read_gdffs_rec.fed_trib_situation;
300        	     x_interface_line_rec.line_gdf_attribute7 :=
301                                           read_gdffs_rec.sta_trib_situation;
302 
303        	     x_interface_line_rec.line_gdf_attribute2 := l_fsc_cls_code;
304        	     x_interface_line_rec.line_gdf_attribute3 := l_trx_cls_code;
305              x_interface_line_rec.line_gdf_attr_category :=
306                                           l_line_attr_category;
307 
308            END LOOP;
309 
310         ELSE -- Country code is AR or CO
311 
312            IF l_tax_method = 'LTE' THEN
313               l_fsc_cls_code := NULL;
314               l_trx_cls_code := NULL;
315               BEGIN
316                 select global_attribute5,
317                        global_attribute6
318                 into   l_fsc_cls_code,
319                        l_trx_cls_code
320                 from   oe_order_lines
321                 where  line_id = l_line_id;
322               EXCEPTION
323                 WHEN OTHERS THEN
324                      l_fsc_cls_code := NULL;
325                      l_trx_cls_code := NULL;
326               END;
327            END IF;
328 
329            IF p_interface_line_rec.line_type = 'LINE' THEN
330               x_interface_line_rec.line_gdf_attribute2 := l_fsc_cls_code;
331               x_interface_line_rec.line_gdf_attribute3 := l_trx_cls_code;
332 
333               x_interface_line_rec.line_gdf_attr_category :=
334                                           l_line_attr_category;
335            END IF;
336 
337         END IF;
338 
339      ELSE -- l_invoice_line_id or l_order_line_id is not null
340 
341         IF l_tax_method = 'LTE' THEN
342 
343            l_fsc_cls_code := NULL;
344            l_trx_cls_code := NULL;
345            IF l_invoice_line_id IS NOT NULL THEN
346 
347               -- RMA source is invoice
348 
349               BEGIN
350                 SELECT global_attribute2,
351                        global_attribute3
352                 INTO   l_fsc_cls_code,
353                        l_trx_cls_code
354                 FROM   ra_customer_trx_lines
355                 WHERE  customer_trx_line_id = l_invoice_line_id;
356               EXCEPTION
357                 WHEN OTHERS THEN
358                      l_fsc_cls_code := NULL;
359                      l_trx_cls_code := NULL;
360                      l_line_attr_category := NULL;
361               END;
362            ELSIF l_order_line_id IS NOT NULL THEN
363 
364               -- RMA source is Sales Order
365               BEGIN
366                 select global_attribute5,
367                        global_attribute6
368                 into   l_fsc_cls_code,
369                        l_trx_cls_code
370                 from   oe_order_lines
371                 where  line_id = l_order_line_id;
372               EXCEPTION
373                 WHEN OTHERS THEN
374                      l_fsc_cls_code := NULL;
375                      l_trx_cls_code := NULL;
376                      l_line_attr_category := NULL;
377               END;
378 
379            END IF;
380 
381            IF p_interface_line_rec.line_type = 'LINE' THEN
382               x_interface_line_rec.line_gdf_attribute2 := l_fsc_cls_code;
383               x_interface_line_rec.line_gdf_attribute3 := l_trx_cls_code;
384 
385               x_interface_line_rec.line_gdf_attr_category :=
386                                                            l_line_attr_category;
387            END IF;
388 
389         END IF; -- Tax method check
390 
391         IF l_country_code = 'BR' THEN
392 
393            IF l_invoice_line_id IS NOT NULL THEN
394              BEGIN
395                SELECT il.global_attribute1   op_fiscal_code,
396                       il.global_attribute4   item_origin,
397                       il.global_attribute5   item_fiscal_type,
398                       il.global_attribute6   fed_trib_situation,
399                       il.global_attribute7   sta_trib_situation
400                INTO   l_op_fiscal_code,
401                       l_item_origin,
402                       l_item_fiscal_type,
403                       l_fed_trib_situation,
404                       l_sta_trib_situation
405                FROM   ra_customer_trx_lines il
406                WHERE  il.customer_trx_line_id = l_invoice_line_id;
407 
408              EXCEPTION WHEN OTHERS THEN
409                l_op_fiscal_code := null;
410                l_item_origin := null;
411                l_item_fiscal_type := null;
412                l_fed_trib_situation := null;
413                l_sta_trib_situation := null;
414 
415              END;
416 
417            ELSIF l_order_line_id IS NOT NULL THEN
418 
419              l_so_organization_id :=
420                 to_number(oe_profile.value('SO_ORGANIZATION_ID'));
421 
422              BEGIN
423                SELECT ol.global_attribute1    op_fiscal_code,
424                       msi.global_attribute3   item_origin,
425                       msi.global_attribute4   item_fiscal_type,
426                       msi.global_attribute5   fed_trib_situation,
427                       msi.global_attribute6   sta_trib_situation
428                INTO   l_op_fiscal_code,
429                       l_item_origin,
430                       l_item_fiscal_type,
431                       l_fed_trib_situation,
432                       l_sta_trib_situation
433                FROM   oe_order_lines ol, mtl_system_items msi
434                WHERE  ol.line_id = l_order_line_id
435                AND    msi.inventory_item_id(+) =
436                         p_interface_line_rec.inventory_item_id
437                AND    msi.organization_id = l_so_organization_id;
438 
439              EXCEPTION WHEN OTHERS THEN
440                l_op_fiscal_code := null;
441                l_item_origin := null;
442                l_item_fiscal_type := null;
443                l_fed_trib_situation := null;
444                l_sta_trib_situation := null;
445 
446              END;
447 
448            END IF;
449 
450        	   x_interface_line_rec.line_gdf_attribute1 := l_op_fiscal_code;
451        	   x_interface_line_rec.line_gdf_attribute4 := l_item_origin;
452        	   x_interface_line_rec.line_gdf_attribute5 := l_item_fiscal_type;
453        	   x_interface_line_rec.line_gdf_attribute6 := l_fed_trib_situation;
454        	   x_interface_line_rec.line_gdf_attribute7 := l_sta_trib_situation;
455 
456            x_interface_line_rec.line_gdf_attr_category := l_line_attr_category;
457 
458         END IF; -- Country code BR for RMA
459 
460      END IF; -- l_invoice_line_id or l_order_line_id check
461 
462   END IF;  -- Country Code check
463 
464 EXCEPTION
465   WHEN OTHERS THEN
466        OE_DEBUG_PUB.ADD('JL-Exception Others');
467        x_error_buffer := SQLERRM;
468        x_return_code := 2;
469 
470 END copy_gdff;
471 
472 PROCEDURE copy_gdf (
473         x_interface_line_rec IN OUT NOCOPY OE_Invoice_PUB.OE_GDF_Rec_Type,
474         x_return_code        IN OUT NOCOPY NUMBER,
475 	x_error_buffer       IN OUT NOCOPY VARCHAR2) IS
476 
477   l_so_organization_id 	 NUMBER(15);
478   l_country_code 	 VARCHAR2(2);
479   l_tax_method           VARCHAR2(30);
480 
481   l_header_attr_category VARCHAR2(30);
482   l_line_attr_category   VARCHAR2(30);
483 
484   l_volume_qty            VARCHAR2(150);
485   l_volume_type           VARCHAR2(150);
486   l_volume_number         VARCHAR2(150);
487   l_vehicle_number        VARCHAR2(150);
488   l_gross_weight          NUMBER;
489   l_net_weight            NUMBER;
490   l_item_origin           VARCHAR2(150);
491   l_item_fiscal_type      VARCHAR2(150);
492   l_fsc_cls_code          VARCHAR2(30);
493   l_trx_cls_code          VARCHAR2(30);
494   l_inventory_item_id     NUMBER;
495   l_line_id               NUMBER;
496   l_order_line_id         NUMBER;
497   l_invoice_line_id       NUMBER;
498   l_op_fiscal_code        VARCHAR2(5);
499   l_fed_trib_situation    VARCHAR2(25);
500   l_sta_trib_situation    VARCHAR2(25);
501 
502   l_org_id                NUMBER;
503   l_jl                    VARCHAR2(2);
504 
505   /* Added for Brazilian SPED */
506   l_veh_plate_state_code VARCHAR2(5);
507   l_veh_antt_inscr       VARCHAR2(50);
508   l_tow_veh_plate_num    VARCHAR2(50);
509   l_tow_veh_plate_state_code VARCHAR2(5);
510   l_tow_veh_antt_inscr   VARCHAR2(50);
511   l_seal_number          VARCHAR2(150);
512   /*End*/
513 
514 
515   CURSOR read_gdffs IS
516     SELECT ol.global_attribute1    op_fiscal_code,
517            ol.global_attribute2    freight_acc_expense,
518            ol.global_attribute3    insurance_acc_expense,
519            ol.global_attribute4    other_acc_expense,
520            ol.global_attribute5    item_line_fiscal_class,
521            ol.global_attribute6    item_line_trx_reason,
522            msi.global_attribute3   item_origin,
523            msi.global_attribute4   item_fiscal_type,
524            msi.global_attribute5   fed_trib_situation,
525            msi.global_attribute6   sta_trib_situation
526     FROM   oe_order_lines ol,
527            mtl_system_items msi
528     WHERE  line_id = l_line_id
529     AND    msi.inventory_item_id(+) = l_inventory_item_id
530     AND    msi.organization_id      = l_so_organization_id;
531 
532   read_gdffs_rec  read_gdffs%ROWTYPE;
533 
534 BEGIN
535 
536   x_return_code := 0;
537   x_error_buffer := NULL;
538 -- Following line is commented for performance changes bug 1922093.
539 --  x_interface_line_rec := p_interface_line_rec;
540 
541   --Following line is commented as a part of bug 2133665
542   --l_country_code := fnd_profile.value('JGZZ_COUNTRY_CODE');
543   --
544   l_country_code := SUBSTR(x_interface_line_rec.line_gdf_attr_category,4,2);
545 
546   l_jl := 'JL';
547   --
548   OE_DEBUG_PUB.ADD('JL-Country Code is ' || l_country_code);
549 
550   IF NVL(l_country_code,'$') IN ('BR','AR','CO') THEN
551 
552      l_org_id := mo_global.get_current_org_id;
553      l_tax_method := JL_ZZ_AR_TX_LIB_PKG.get_tax_method(l_org_id);
554      OE_DEBUG_PUB.ADD('JL-Tax Method is ' || l_tax_method);
555 
556      l_so_organization_id:= to_number(oe_sys_parameters.value('MASTER_ORGANIZATION_ID',l_org_id));
557      OE_DEBUG_PUB.ADD('JL-So Organization id is ' || l_so_organization_id);
558 
559      IF l_country_code = 'BR' THEN
560         l_header_attr_category :=  'JL.BR.ARXTWMAI.Additional Info';
561         l_line_attr_category   :=  'JL.BR.ARXTWMAI.Additional Info';
562      ELSE
563        -- l_header_attr_category :=  'JL'||'.'||l_country_code||'.ARXTWMAI.HEADER';
564        -- l_line_attr_category   := 'JL'||'.'||l_country_code||'.ARXTWMAI.LINES';
565         l_header_attr_category :=  l_jl||'.'||l_country_code||'.ARXTWMAI.HEADER';
566         l_line_attr_category   := l_jl||'.'||l_country_code||'.ARXTWMAI.LINES';
567      END IF;
568 
569      l_line_id := x_interface_line_rec.interface_line_attribute6;
570      l_inventory_item_id := x_interface_line_rec.inventory_item_id;
571 
572      OE_DEBUG_PUB.ADD('JL-Line Id '|| l_line_id);
573      OE_DEBUG_PUB.ADD('JL-Inventory Item Id '|| l_inventory_item_id);
574      OE_DEBUG_PUB.ADD('JL-Line Type '|| x_interface_line_rec.line_type);
575 
576      l_invoice_line_id := NULL;
577      l_order_line_id := NULL;
578      IF l_tax_method = 'LTE' THEN
579 
580        --
581        -- Bug#5588076- init GDF for LTE
582        --
583        init_gdf(x_interface_line_rec);
584 
585        BEGIN
586          SELECT REFERENCE_CUSTOMER_TRX_LINE_ID,
587                 REFERENCE_LINE_ID
588          INTO   l_invoice_line_id,
589                 l_order_line_id
590          FROM   oe_order_lines
591          WHERE  line_id = l_line_id;
592        EXCEPTION
593          WHEN OTHERS THEN
594               l_invoice_line_id := NULL;
595               l_order_line_id := NULL;
596        END;
597 
598      END IF;
599 
600      OE_DEBUG_PUB.ADD('JL-Invoice Line Id '|| l_invoice_line_id);
601      OE_DEBUG_PUB.ADD('JL-Return Id '|| l_order_line_id);
602 
603      IF (l_invoice_line_id IS NULL AND
604          l_order_line_id IS NULL) THEN
605 
606         /* Copy Global DFF columns from OE tables */
607         IF l_country_code = 'BR' THEN
608 
609            OPEN read_gdffs;
610            LOOP
611 
612              FETCH read_gdffs INTO read_gdffs_rec;
613 
614              EXIT WHEN read_gdffs%NOTFOUND OR
615                        read_gdffs%NOTFOUND is NULL;
616 
617              IF l_tax_method = 'LTE' THEN
618                 l_fsc_cls_code := read_gdffs_rec.item_line_fiscal_class;
619                 l_trx_cls_code := read_gdffs_rec.item_line_trx_reason;
620              ELSE
621                 l_fsc_cls_code := NULL;
622                 l_trx_cls_code := NULL;
623              END IF;
624 
625              IF x_interface_line_rec.line_type = 'LINE' THEN
626 
627                 BEGIN
628                   OE_DEBUG_PUB.ADD('JL-Delivery Name '||
629                                 x_interface_line_rec.interface_line_attribute3);
630                   SELECT del.gross_weight,
631                          del.net_weight,
632                          del.global_attribute3,
633                          del.global_attribute1,
634                          del.global_attribute2,
635                          del.global_attribute4,
636                          del.global_attribute5,
637                          del.global_attribute6,
638                          del.global_attribute7,
639                          del.global_attribute8,
640                          del.global_attribute9
641                   INTO   l_gross_weight,
642                          l_net_weight,
643                          l_volume_qty,
644                          l_volume_type,
645                          l_volume_number,
646                          l_veh_plate_state_code,
647                          l_veh_antt_inscr,
648                          l_tow_veh_plate_num,
649                          l_tow_veh_plate_state_code,
650                          l_tow_veh_antt_inscr,
651                          l_seal_number
652                   FROM   wsh_new_deliveries del
653                   WHERE  del.name =
654                                  x_interface_line_rec.interface_line_attribute3;
655 
656                 EXCEPTION
657                   WHEN OTHERS THEN
658                        l_gross_weight := NULL;
659                        l_net_weight := NULL;
660                        l_volume_qty := NULL;
661                        l_volume_type := NULL;
662                        l_volume_number := NULL;
663                        l_veh_plate_state_code := NULL;
664                        l_veh_antt_inscr := NULL;
665                        l_tow_veh_plate_num := NULL;
666                        l_tow_veh_plate_state_code := NULL;
667                        l_tow_veh_antt_inscr := NULL;
668                        l_seal_number := NULL;
669                 END;
670 
671                 BEGIN
672                   SELECT t.vehicle_number
673                   INTO   l_vehicle_number
674                   FROM   wsh_new_deliveries del,
675                          wsh_delivery_legs dl,
676                          wsh_trip_stops pickup_stop,
677                          wsh_trips t
678                   WHERE  del.name =
679                                  x_interface_line_rec.interface_line_attribute3
680                   AND    del.delivery_id = dl.delivery_id
681                   AND    dl.pick_up_stop_id = pickup_stop.stop_id
682                   AND    pickup_stop.trip_id = t.trip_id
683                   AND    pickup_stop.actual_departure_date =
684                                (SELECT min(pickup_stop_x.actual_departure_date)
685                                 FROM  wsh_new_deliveries del_x,
686                                       wsh_delivery_legs dl_x,
687                                       wsh_trip_stops pickup_stop_x,
688                                       wsh_trips tx
689                                 WHERE del_x.delivery_id = del.delivery_id
690                                 AND   del_x.delivery_id = dl_x.delivery_id
691                                 AND   dl_x.pick_up_stop_id =
692                                                            pickup_stop_x.stop_id
693                                 AND   pickup_stop_x.trip_id = tx.trip_id
694                                 );
695                 EXCEPTION
696                   WHEN OTHERS THEN
697                        l_vehicle_number := NULL;
698                 END;
699 
700              END IF;
701 
702              x_interface_line_rec.header_gdf_attribute9 :=
703                                           read_gdffs_rec.freight_acc_expense;
704        	     x_interface_line_rec.header_gdf_attribute10 :=
705                                           read_gdffs_rec.insurance_acc_expense;
706        	     x_interface_line_rec.header_gdf_attribute11 :=
707                                           read_gdffs_rec.other_acc_expense;
708        	     x_interface_line_rec.header_gdf_attribute12 := l_vehicle_number;
709        	     x_interface_line_rec.header_gdf_attribute13 := l_volume_qty;
710        	     x_interface_line_rec.header_gdf_attribute14 := l_volume_type;
711        	     x_interface_line_rec.header_gdf_attribute15 := l_volume_number;
712        	     x_interface_line_rec.header_gdf_attribute16 := fnd_number.number_to_canonical(l_gross_weight);
713        	     x_interface_line_rec.header_gdf_attribute17 := fnd_number.number_to_canonical(l_net_weight);
714              x_interface_line_rec.header_gdf_attribute21 := l_veh_plate_state_code;
715              x_interface_line_rec.header_gdf_attribute22 := l_veh_antt_inscr;
716              x_interface_line_rec.header_gdf_attribute23 := l_tow_veh_plate_num;
717              x_interface_line_rec.header_gdf_attribute24 := l_tow_veh_plate_state_code;
718              x_interface_line_rec.header_gdf_attribute25 := l_tow_veh_antt_inscr;
719              x_interface_line_rec.header_gdf_attribute26 := l_seal_number;
720              x_interface_line_rec.header_gdf_attr_category :=
721                                           l_header_attr_category;
722 
723              x_interface_line_rec.line_gdf_attribute1 :=
724                                           read_gdffs_rec.op_fiscal_code;
725        	     x_interface_line_rec.line_gdf_attribute4 :=
726                                           read_gdffs_rec.item_origin;
727        	     x_interface_line_rec.line_gdf_attribute5 :=
728                                           read_gdffs_rec.item_fiscal_type;
729        	     x_interface_line_rec.line_gdf_attribute6 :=
730                                           read_gdffs_rec.fed_trib_situation;
731        	     x_interface_line_rec.line_gdf_attribute7 :=
732                                           read_gdffs_rec.sta_trib_situation;
733 
734        	     x_interface_line_rec.line_gdf_attribute2 := l_fsc_cls_code;
735        	     x_interface_line_rec.line_gdf_attribute3 := l_trx_cls_code;
736 
737              x_interface_line_rec.line_gdf_attr_category :=
738                                           l_line_attr_category;
739 
740            END LOOP;
741 
742         ELSE -- Country code is AR or CO
743 
744            IF l_tax_method = 'LTE' THEN
745               l_fsc_cls_code := NULL;
746               l_trx_cls_code := NULL;
747               BEGIN
748                 select global_attribute5,
749                        global_attribute6
750                 into   l_fsc_cls_code,
751                        l_trx_cls_code
752                 from   oe_order_lines
753                 where  line_id = l_line_id;
754               EXCEPTION
755                 WHEN OTHERS THEN
756                      l_fsc_cls_code := NULL;
757                      l_trx_cls_code := NULL;
758               END;
759            END IF;
760 
761            IF x_interface_line_rec.line_type = 'LINE' THEN
762               x_interface_line_rec.line_gdf_attribute2 := l_fsc_cls_code;
763               x_interface_line_rec.line_gdf_attribute3 := l_trx_cls_code;
764 
765               x_interface_line_rec.line_gdf_attr_category :=
766                                           l_line_attr_category;
767            END IF;
768 
769         END IF;
770 
771      ELSE -- l_invoice_line_id or l_order_line_id is not null
772 
773         IF l_tax_method = 'LTE' THEN
774 
775            l_fsc_cls_code := NULL;
776            l_trx_cls_code := NULL;
777            IF l_invoice_line_id IS NOT NULL THEN
778 
779               -- RMA source is invoice
780 
781               BEGIN
782                 SELECT global_attribute2,
783                        global_attribute3
784                 INTO   l_fsc_cls_code,
785                        l_trx_cls_code
786                 FROM   ra_customer_trx_lines
787                 WHERE  customer_trx_line_id = l_invoice_line_id;
788               EXCEPTION
789                 WHEN OTHERS THEN
790                      l_fsc_cls_code := NULL;
791                      l_trx_cls_code := NULL;
792                      l_line_attr_category := NULL;
793               END;
794            ELSIF l_order_line_id IS NOT NULL THEN
795 
796               -- RMA source is Sales Order
797               BEGIN
798                 select global_attribute5,
799                        global_attribute6
800                 into   l_fsc_cls_code,
801                        l_trx_cls_code
802                 from   oe_order_lines
803                 where  line_id = l_order_line_id;
804               EXCEPTION
805                 WHEN OTHERS THEN
806                      l_fsc_cls_code := NULL;
807                      l_trx_cls_code := NULL;
808                      l_line_attr_category := NULL;
809               END;
810 
811            END IF;
812 
813            IF x_interface_line_rec.line_type = 'LINE' THEN
814               x_interface_line_rec.line_gdf_attribute2 := l_fsc_cls_code;
815               x_interface_line_rec.line_gdf_attribute3 := l_trx_cls_code;
816 
817               x_interface_line_rec.line_gdf_attr_category :=
818                                                            l_line_attr_category;
819            END IF;
820 
821         END IF; -- Tax method check
822 
823         IF l_country_code = 'BR' THEN
824 
825            IF l_invoice_line_id IS NOT NULL THEN
826              BEGIN
827                SELECT il.global_attribute1   op_fiscal_code,
828                       il.global_attribute4   item_origin,
829                       il.global_attribute5   item_fiscal_type,
830                       il.global_attribute6   fed_trib_situation,
831                       il.global_attribute7   sta_trib_situation
832                INTO   l_op_fiscal_code,
833                       l_item_origin,
834                       l_item_fiscal_type,
835                       l_fed_trib_situation,
836                       l_sta_trib_situation
837                FROM   ra_customer_trx_lines il
838                WHERE  il.customer_trx_line_id = l_invoice_line_id;
839 
840              EXCEPTION WHEN OTHERS THEN
841                l_op_fiscal_code := null;
842                l_item_origin := null;
843                l_item_fiscal_type := null;
844                l_fed_trib_situation := null;
845                l_sta_trib_situation := null;
846 
847              END;
848 
849            ELSIF l_order_line_id IS NOT NULL THEN
850 
851              l_so_organization_id :=
852                 to_number(oe_profile.value('SO_ORGANIZATION_ID'));
853 
854              BEGIN
855                SELECT ol.global_attribute1    op_fiscal_code,
856                       msi.global_attribute3   item_origin,
857                       msi.global_attribute4   item_fiscal_type,
858                       msi.global_attribute5   fed_trib_situation,
859                       msi.global_attribute6   sta_trib_situation
860                INTO   l_op_fiscal_code,
861                       l_item_origin,
862                       l_item_fiscal_type,
863                       l_fed_trib_situation,
864                       l_sta_trib_situation
865                FROM   oe_order_lines ol, mtl_system_items msi
866                WHERE  ol.line_id = l_order_line_id
867                AND    msi.inventory_item_id(+) =
868                         x_interface_line_rec.inventory_item_id
869                AND    msi.organization_id = l_so_organization_id;
870 
871              EXCEPTION WHEN OTHERS THEN
872                l_op_fiscal_code := null;
873                l_item_origin := null;
874                l_item_fiscal_type := null;
875                l_fed_trib_situation := null;
876                l_sta_trib_situation := null;
877 
878              END;
879 
880            END IF;
881 
882        	   x_interface_line_rec.line_gdf_attribute1 := l_op_fiscal_code;
883        	   x_interface_line_rec.line_gdf_attribute4 := l_item_origin;
884        	   x_interface_line_rec.line_gdf_attribute5 := l_item_fiscal_type;
885        	   x_interface_line_rec.line_gdf_attribute6 := l_fed_trib_situation;
886        	   x_interface_line_rec.line_gdf_attribute7 := l_sta_trib_situation;
887 
888            x_interface_line_rec.line_gdf_attr_category := l_line_attr_category;
889 
890         END IF; -- Country code BR for RMA
891 
892      END IF; -- l_invoice_line_id or l_order_line_id check
893 
894   END IF;  -- Country Code check
895 
896 EXCEPTION
897   WHEN OTHERS THEN
898        OE_DEBUG_PUB.ADD('JL-Exception Others');
899        x_error_buffer := SQLERRM;
900        x_return_code := 2;
901 
902 END copy_gdf;
903 
904 /*----------------------------------------------------------------------------*
905  | PROCEDURE                                                                  |
906  |    default_gdff                           			      	      |
907  |                                                                            |
908  | DESCRIPTION                                                                |
909  |    When the item is entered on Sales Order Line, if inventory item id is   |
910  |    not null then this procedure will default global descriptive flexfield  |
911  |    values if they are null                                                 |
912  |									      |
913  | PARAMETERS                                                                 |
914  |   INPUT                                                 		      |
915  |      p_line_rec              oe_order_pub.line_rec_type Interface line     |
916  |                              record declared in OEXPOROS.pls               |
917  |   OUTPUT                                                		      |
918  |      x_line_rec              oe_order_pub.line_rec_type Interface line     |
919  |                              record declared in OEXPOROS.pls               |
920  |      x_error_buffer          VARCHAR2 -- Error Message  	              |
921  |      x_return_code         	NUMBER   -- Error Code.           	      |
922  |                                          0 - Success, 2 - Failure. 	      |
923  |                                                                            |
924  | HISTORY                                                                    |
925  |                                                                            |
926  |    24-JAN-2000 Harsh Takle      Created.                                   |
927  *----------------------------------------------------------------------------*/
928 PROCEDURE default_gdff
929      (p_line_rec     IN     oe_order_pub.line_rec_type,
930       x_line_rec        OUT NOCOPY oe_order_pub.line_rec_type,
931       x_return_code  IN OUT NOCOPY NUMBER,
932       x_error_buffer IN OUT NOCOPY VARCHAR2,
933       p_org_id       IN     NUMBER DEFAULT mo_global.get_current_org_id
934      ) IS
935 
936 
937   l_so_organization_id 	 NUMBER(15);
938   l_inventory_org_id     NUMBER(15);
939 
940   l_country_code 	 VARCHAR2(2);
941   l_tax_method           VARCHAR2(30);
942 
943   l_fcc_code      varchar2(30);
944   l_tran_nat      varchar2(30);
945   l_gdf_cat       VARCHAR2(30);
946 
947   l_org_id        NUMBER;
948   l_jl            VARCHAR2(2);
949 
950 BEGIN
951   l_fcc_code := NULL;
952   l_tran_nat := NULL;
953 
954   x_line_rec := p_line_rec;
955   x_return_code := 0;
956   x_error_buffer := NULL;
957 
958   --Bug fix 2367111
959   --l_country_code := fnd_profile.value('JGZZ_COUNTRY_CODE');
960   --l_country_code := JG_ZZ_SHARED_PKG.GET_COUNTRY(p_org_id, null);
961 
962   -- Bug#5090423
963   l_org_id := p_line_rec.org_id;
964   l_country_code := JG_ZZ_SHARED_PKG.GET_COUNTRY(l_org_id, null);
965 
966   l_jl := 'JL';
967 
968   IF NVL(l_country_code,'$') IN ('BR','AR','CO') THEN
969 
970      --l_gdf_cat := 'JL'||'.'||l_country_code||'.OEXOEORD.LINES';
971      l_gdf_cat := l_jl||'.'||l_country_code||'.OEXOEORD.LINES';
972 
973      l_tax_method := JL_ZZ_AR_TX_LIB_PKG.get_tax_method(l_org_id);
974 
975      l_so_organization_id:= to_number(oe_profile.value('SO_ORGANIZATION_ID'));
976 
977      --
978      -- Bug#4929759- use ship_from_org_id for Colombia
979      -- replace WHERE  mtl.organization_id = l_so_organization_id
980      -- with WHERE  mtl.organization_id = l_inventory_org_id
981      --
982      IF l_country_code = 'CO' THEN
983        l_inventory_org_id := NVL(p_line_rec.ship_from_org_id,
984                                  l_so_organization_id);
985      ELSE
986        -- AR, BR
987        l_inventory_org_id := l_so_organization_id;
988      END IF;
989 
990      IF p_line_rec.inventory_item_id IS NOT NULL THEN
991 
992        -- Bug#5090423: remove get fsc here
993 
994        BEGIN
995          SELECT substr(mtl.global_attribute2,1,30)
996          INTO   l_tran_nat
997          FROM   mtl_system_items mtl
998          WHERE  mtl.organization_id = l_inventory_org_id
999          AND    mtl.inventory_item_id = p_line_rec.inventory_item_id;
1000        EXCEPTION
1001          WHEN OTHERS THEN
1002               OE_DEBUG_PUB.ADD('EXCEPTION: No Value for Transaction Nature');
1003               l_tran_nat := NULL;
1004        END;
1005 
1006        --Bug#6019028: No transaction class for CO as org id is picked from ship_from_org_id
1007 
1008        IF l_tran_nat IS NULL AND l_country_code = 'CO' THEN
1009 
1010          BEGIN
1011            SELECT substr(mtl.global_attribute2,1,30)
1012            INTO   l_tran_nat
1013            FROM   mtl_system_items mtl
1014            WHERE  mtl.organization_id = l_so_organization_id
1015            AND    mtl.inventory_item_id = p_line_rec.inventory_item_id;
1016          EXCEPTION
1017            WHEN OTHERS THEN
1018               OE_DEBUG_PUB.ADD('EXCEPTION: No Value for Transaction Nature');
1019               l_tran_nat := NULL;
1020          END;
1021        END IF;
1022 
1023        -- Bug#5090423
1024        -- get default fiscal classification
1025        BEGIN
1026          SELECT fc.classification_code
1027           INTO l_fcc_code
1028           FROM zx_fc_product_fiscal_v   fc,
1029                mtl_item_categories      mic
1030           WHERE
1031               ((fc.country_code    = l_country_code
1032                  AND fc.country_code in ('AR', 'BR', 'CO'))
1033                 or
1034                 fc.country_code is NULL
1035                )
1036           AND mic.inventory_item_id = p_line_rec.inventory_item_id
1037           AND mic.organization_id   = l_inventory_org_id
1038           AND mic.category_id       = fc.category_id
1039           AND mic.category_set_id   = fc.category_set_id
1040        -- AND fc.structure_name     = 'Fiscal Classification'  -- Commented for Bug#7125709
1041           AND fc.structure_code     = 'FISCAL_CLASSIFICATION'  -- Added as a fix for Bug#7125709
1042           AND EXISTS
1043                  (SELECT 1
1044                    FROM  JL_ZZ_AR_TX_FSC_CLS_ALL
1045                    WHERE fiscal_classification_code = fc.classification_code
1046                      AND org_id = l_org_id
1047                      AND enabled_flag = 'Y')
1048 	  AND ROWNUM =1;
1049          EXCEPTION
1050            WHEN OTHERS THEN
1051                 OE_DEBUG_PUB.ADD('EXCEPTION: No Value for Fiscal Classification');
1052                 l_fcc_code := NULL;
1053 
1054        END;
1055 
1056        --Bug#6019028: No fiscal classification for CO as org id is picked from ship_from_org_id
1057 
1058        IF l_fcc_code IS NULL AND l_country_code = 'CO' THEN
1059 
1060         BEGIN
1061          SELECT fc.classification_code
1062           INTO l_fcc_code
1063           FROM zx_fc_product_fiscal_v   fc,
1064                mtl_item_categories      mic
1065           WHERE
1066               ((fc.country_code    = l_country_code
1067                  AND fc.country_code in ('AR', 'BR', 'CO'))
1068                 or
1069                 fc.country_code is NULL
1070                )
1071           AND mic.inventory_item_id = p_line_rec.inventory_item_id
1072           AND mic.organization_id   = l_so_organization_id
1073           AND mic.category_id       = fc.category_id
1074           AND mic.category_set_id   = fc.category_set_id
1075        -- AND fc.structure_name     = 'Fiscal Classification'  -- Commented for Bug#7125709
1076           AND fc.structure_code     = 'FISCAL_CLASSIFICATION'  -- Added as a fix for Bug#7125709
1077           AND EXISTS
1078                  (SELECT 1
1079                    FROM  JL_ZZ_AR_TX_FSC_CLS_ALL
1080                    WHERE fiscal_classification_code = fc.classification_code
1081                      AND org_id = l_org_id
1082                      AND enabled_flag = 'Y')
1083 	  AND ROWNUM =1;
1084         EXCEPTION
1085            WHEN OTHERS THEN
1086               OE_DEBUG_PUB.ADD('EXCEPTION: No Value for Fiscal Classification');
1087                 l_fcc_code := NULL;
1088         END;
1089        END IF;
1090 
1091      END IF;
1092 
1093      OE_DEBUG_PUB.ADD('Org ID: '|| to_char(l_org_id));
1094      OE_DEBUG_PUB.ADD('Tax Method: '||l_tax_method);
1095      OE_DEBUG_PUB.ADD('Inventory Item Id : '|| to_char(p_line_rec.inventory_item_id));
1096      OE_DEBUG_PUB.ADD('Ship From Inventory Org : '||
1097                        to_char(p_line_rec.ship_from_org_id));
1098 
1099      IF l_tax_method = 'LTE' THEN
1100         OE_DEBUG_PUB.ADD('Before Defaulting');
1101         OE_DEBUG_PUB.ADD('-----------------');
1102         OE_DEBUG_PUB.ADD('GA5: '|| x_line_rec.global_attribute5);
1103         OE_DEBUG_PUB.ADD('GA6: '|| x_line_rec.global_attribute6);
1104         OE_DEBUG_PUB.ADD('GA7: '|| x_line_rec.global_attribute7);
1105         OE_DEBUG_PUB.ADD('GA8: '|| x_line_rec.global_attribute8);
1106 
1107         x_line_rec.global_attribute7 :=
1108                                    nvl(x_line_rec.global_attribute7,l_fcc_code);
1109         x_line_rec.global_attribute8 :=
1110                                    nvl(x_line_rec.global_attribute8,l_tran_nat);
1111         x_line_rec.global_attribute5 :=
1112                                    nvl(x_line_rec.global_attribute5,l_fcc_code);
1113         x_line_rec.global_attribute6 :=
1114                                    nvl(x_line_rec.global_attribute6,l_tran_nat);
1115         x_line_rec.global_attribute_category :=
1116                                    nvl(x_line_rec.global_attribute_category,l_gdf_cat);
1117 
1118         OE_DEBUG_PUB.ADD('After Defaulting');
1119         OE_DEBUG_PUB.ADD('----------------');
1120         OE_DEBUG_PUB.ADD('Default from Inventory Org' ||
1121                           to_char(l_inventory_org_id));
1122         OE_DEBUG_PUB.ADD('GA5: '|| x_line_rec.global_attribute5);
1123         OE_DEBUG_PUB.ADD('GA6: '|| x_line_rec.global_attribute6);
1124         OE_DEBUG_PUB.ADD('GA7: '|| x_line_rec.global_attribute7);
1125         OE_DEBUG_PUB.ADD('GA8: '|| x_line_rec.global_attribute8);
1126      END IF;
1127 
1128   END IF;
1129 
1130 EXCEPTION
1131   WHEN OTHERS THEN
1132        x_error_buffer := SQLERRM;
1133        x_return_code := 2;
1134 
1135 END default_gdff;
1136 
1137 PROCEDURE default_gdf
1138      (x_line_rec     IN OUT NOCOPY OE_ORDER_PUB.LINE_REC_TYPE,
1139       x_return_code  IN OUT NOCOPY NUMBER,
1140       x_error_buffer IN OUT NOCOPY VARCHAR2,
1141       p_org_id       IN NUMBER DEFAULT mo_global.get_current_org_id
1142      ) IS
1143 
1144 
1145   l_so_organization_id   NUMBER(15);
1146   l_inventory_org_id     NUMBER(15);
1147 
1148   l_order_type_id        NUMBER(15);
1149   l_def_value            VARCHAR2(50);
1150   errcode1               NUMBER(15);
1151   l_country_code         VARCHAR2(2);
1152   l_tax_method           VARCHAR2(30);
1153 
1154   l_fcc_code             VARCHAR2(30);
1155   l_tran_nat             VARCHAR2(30);
1156   l_gdf_cat              VARCHAR2(30);
1157   l_item_orig            VARCHAR2(30);
1158   l_fed_trib             VARCHAR2(30);
1159   l_sta_trib             VARCHAR2(30);
1160 
1161   l_org_id               NUMBER;
1162   l_jl                   VARCHAR2(2);
1163 
1164 BEGIN
1165   l_fcc_code := NULL;
1166   l_tran_nat := NULL;
1167   l_item_orig := NULL;
1168   l_sta_trib := NULL;
1169   l_fed_trib := NULL;
1170 
1171   -- Following line commented for bug 1922093.
1172   --x_line_rec := p_line_rec;
1173   x_return_code := 0;
1174   x_error_buffer := NULL;
1175 
1176   --Bug fix 2367111 related to bug 2354736
1177   --l_country_code := fnd_profile.value('JGZZ_COUNTRY_CODE');
1178   --l_org_id := mo_global.get_current_org_id;
1179 
1180   l_org_id := x_line_rec.org_id;
1181   l_country_code := JG_ZZ_SHARED_PKG.GET_COUNTRY(l_org_id, null);
1182 
1183   l_jl := 'JL';
1184 
1185   IF NVL(l_country_code,'$') IN ('BR','AR','CO') THEN
1186 
1187      -- l_gdf_cat := 'JL'||'.'||l_country_code||'.OEXOEORD.LINES';
1188      l_gdf_cat := l_jl||'.'||l_country_code||'.OEXOEORD.LINES';
1189 
1190      l_tax_method := JL_ZZ_AR_TX_LIB_PKG.get_tax_method(l_org_id);
1191 
1192      l_so_organization_id:= to_number(oe_sys_parameters.value('MASTER_ORGANIZATION_ID',l_org_id));
1193 
1194      --
1195      -- Bug#4929759- use ship_from_org_id for Colombia
1196      -- replace WHERE  mtl.organization_id = l_so_organization_id
1197      -- with WHERE  mtl.organization_id = l_inventory_org_id
1198      --
1199      IF l_country_code = 'CO' THEN
1200        l_inventory_org_id := NVL(x_line_rec.ship_from_org_id,
1201                                  l_so_organization_id);
1202      ELSE
1203        -- AR, BR
1204        l_inventory_org_id := l_so_organization_id;
1205      END IF;
1206 
1207      IF x_line_rec.inventory_item_id IS NOT NULL THEN
1208 
1209         IF l_country_code = 'BR'  THEN
1210             -- Forward port change. Refer bug 5224022
1211 
1212            BEGIN
1213              SELECT substr(mtl.global_attribute1,1,30),
1214                 substr(mtl.global_attribute2,1,30),
1215                 substr(mtl.global_attribute3,1,30),
1216                 substr(mtl.global_attribute5,1,30),
1217                 substr(mtl.global_attribute6,1,30)
1218              INTO   l_fcc_code,
1219                     l_tran_nat,
1220                     l_item_orig,
1221                     l_fed_trib,
1222                     l_sta_trib
1223              FROM   mtl_system_items mtl
1224              WHERE  mtl.organization_id = l_inventory_org_id
1225              AND    mtl.inventory_item_id = x_line_rec.inventory_item_id;
1226            EXCEPTION
1227              WHEN OTHERS THEN
1228                   OE_DEBUG_PUB.ADD('EXCEPTION: No Value for Global Attributes');
1229                   l_tran_nat := NULL;
1230           END;
1231         ELSE
1232 
1233 	       -- Bug#5090423 : removed get fsc here
1234 
1235 	       BEGIN
1236 	         SELECT substr(mtl.global_attribute2,1,30)
1237 	         INTO   l_tran_nat
1238 	         FROM   mtl_system_items mtl
1239 	         WHERE  mtl.organization_id = l_inventory_org_id
1240 	         AND    mtl.inventory_item_id = x_line_rec.inventory_item_id;
1241 	       EXCEPTION
1242 	         WHEN OTHERS THEN
1243 	              OE_DEBUG_PUB.ADD('EXCEPTION: No Value for Global Attributes');
1244 	              l_tran_nat := NULL;
1245 	       END;
1246 		END IF;
1247        --Bug#6019028: No transaction class for CO as org id is picked from ship_from_org_id
1248 
1249        IF l_tran_nat IS NULL AND l_country_code = 'CO' THEN
1250 
1251          BEGIN
1252            SELECT substr(mtl.global_attribute2,1,30)
1253            INTO   l_tran_nat
1254            FROM   mtl_system_items mtl
1255            WHERE  mtl.organization_id = l_so_organization_id
1256            AND    mtl.inventory_item_id = x_line_rec.inventory_item_id;
1257          EXCEPTION
1258            WHEN OTHERS THEN
1259               OE_DEBUG_PUB.ADD('EXCEPTION: No Value for Transaction Nature');
1260               l_tran_nat := NULL;
1261          END;
1262        END IF;
1263 
1264        -- Bug#5090423
1265        -- get default fiscal classification
1266        BEGIN
1267          SELECT fc.classification_code
1268           INTO l_fcc_code
1269           FROM zx_fc_product_fiscal_v   fc,
1270                mtl_item_categories      mic
1271           WHERE
1272             ((fc.country_code    = l_country_code
1273                AND fc.country_code in ('AR', 'BR', 'CO'))
1274               or
1275               fc.country_code is NULL
1276              )
1277           AND mic.inventory_item_id = x_line_rec.inventory_item_id
1278           AND mic.organization_id   = l_inventory_org_id
1279           AND mic.category_id       = fc.category_id
1280           AND mic.category_set_id   = fc.category_set_id
1281        -- AND fc.structure_name     = 'Fiscal Classification'  -- Commented for Bug#7125709
1282           AND fc.structure_code     = 'FISCAL_CLASSIFICATION'  -- Added as a fix for Bug#7125709
1283 	  AND rownum = 1
1284           AND EXISTS
1285                  (SELECT 1
1286                    FROM  JL_ZZ_AR_TX_FSC_CLS_ALL
1287                    WHERE fiscal_classification_code = fc.classification_code
1288                      AND org_id = l_org_id
1289                      AND enabled_flag = 'Y');
1290          EXCEPTION
1291            WHEN OTHERS THEN
1292                 OE_DEBUG_PUB.ADD('EXCEPTION: No Value for Fiscal Classification');
1293                 l_fcc_code := NULL;
1294 
1295        END;
1296 
1297               --Bug#6019028: No fiscal classification for CO as org id is picked from ship_from_org_id
1298 
1299        IF l_fcc_code IS NULL AND l_country_code = 'CO' THEN
1300 
1301         BEGIN
1302          SELECT fc.classification_code
1303           INTO l_fcc_code
1304           FROM zx_fc_product_fiscal_v   fc,
1305                mtl_item_categories      mic
1306           WHERE
1307               ((fc.country_code    = l_country_code
1308                  AND fc.country_code in ('AR', 'BR', 'CO'))
1309                 or
1310                 fc.country_code is NULL
1311                )
1312           AND mic.inventory_item_id = x_line_rec.inventory_item_id
1313           AND mic.organization_id   = l_so_organization_id
1314           AND mic.category_id       = fc.category_id
1315           AND mic.category_set_id   = fc.category_set_id
1316        -- AND fc.structure_name     = 'Fiscal Classification'  -- Commented for Bug#7125709
1317           AND fc.structure_code     = 'FISCAL_CLASSIFICATION'  -- Added as a fix for Bug#7125709
1318           AND EXISTS
1319                  (SELECT 1
1320                    FROM  JL_ZZ_AR_TX_FSC_CLS_ALL
1321                    WHERE fiscal_classification_code = fc.classification_code
1322                      AND org_id = l_org_id
1323                      AND enabled_flag = 'Y')
1324 	  AND ROWNUM =1;
1325         EXCEPTION
1326            WHEN OTHERS THEN
1327               OE_DEBUG_PUB.ADD('EXCEPTION: No Value for Fiscal Classification');
1328                 l_fcc_code := NULL;
1329         END;
1330        END IF;
1331 
1332      END IF;
1333 
1334      -- BUG 3685144
1335 
1336        BEGIN
1337          SELECT order_type_id
1338          INTO   l_order_type_id
1339          FROM OE_ORDER_HEADERS_ALL
1340          WHERE header_id = x_line_rec.header_id;
1341 
1342        EXCEPTION
1343          WHEN OTHERS THEN
1344               OE_DEBUG_PUB.ADD('EXCEPTION: No Value for Order type Id ');
1345               l_order_type_id := NULL;
1346        END;
1347 
1348        JL_ZZ_OE_LIBRARY_1_PKG.get_global_attribute3 ( l_order_type_id, l_def_value, 1, errcode1 );
1349 
1350      OE_DEBUG_PUB.ADD('Org ID: '|| to_char(l_org_id));
1351      OE_DEBUG_PUB.ADD('Country Code: '|| l_country_code);
1352      OE_DEBUG_PUB.ADD('Tax Method: '||l_tax_method);
1353      OE_DEBUG_PUB.ADD('Inventory Item Id : '|| to_char(x_line_rec.inventory_item_id));
1354      OE_DEBUG_PUB.ADD('Ship From Inventory Org : '||
1355                        to_char(x_line_rec.ship_from_org_id));
1356 
1357      OE_DEBUG_PUB.ADD('Order Type Id : '|| l_order_type_id);
1358      IF l_tax_method = 'LTE' THEN
1359         OE_DEBUG_PUB.ADD('Before Defaulting');
1360         OE_DEBUG_PUB.ADD('-----------------');
1361         OE_DEBUG_PUB.ADD('GA1: '|| x_line_rec.global_attribute1);
1362         OE_DEBUG_PUB.ADD('GA5: '|| x_line_rec.global_attribute5);
1363         OE_DEBUG_PUB.ADD('GA6: '|| x_line_rec.global_attribute6);
1364         OE_DEBUG_PUB.ADD('GA7: '|| x_line_rec.global_attribute7);
1365         OE_DEBUG_PUB.ADD('GA8: '|| x_line_rec.global_attribute8);
1366 
1367         x_line_rec.global_attribute7 :=
1368                                    nvl(x_line_rec.global_attribute7,l_fcc_code);
1369         x_line_rec.global_attribute8 :=
1370                                    nvl(x_line_rec.global_attribute8,l_tran_nat);
1371         x_line_rec.global_attribute5 :=
1372                                    nvl(x_line_rec.global_attribute5,l_fcc_code);
1373         x_line_rec.global_attribute1 :=
1374                                    nvl(x_line_rec.global_attribute1,l_def_value);
1375         x_line_rec.global_attribute6 :=
1376                                    nvl(x_line_rec.global_attribute6,l_tran_nat);
1377 
1378         IF l_country_code = 'BR' THEN
1379             x_line_rec.global_attribute9 :=
1380                                        nvl(x_line_rec.global_attribute9,l_item_orig);
1381             x_line_rec.global_attribute10 :=
1382                                        nvl(x_line_rec.global_attribute10,l_fed_trib);
1383             x_line_rec.global_attribute11 :=
1384                                        nvl(x_line_rec.global_attribute11,l_sta_trib);
1385             x_line_rec.global_attribute12 :=
1386                                        nvl(x_line_rec.global_attribute12,l_item_orig);
1387             x_line_rec.global_attribute13 :=
1388                                        nvl(x_line_rec.global_attribute13,l_fed_trib);
1389             x_line_rec.global_attribute14 :=
1390                                        nvl(x_line_rec.global_attribute14,l_sta_trib);
1391         END IF;
1392 
1393 
1394         x_line_rec.global_attribute_category :=
1395                                    nvl(x_line_rec.global_attribute_category,l_gdf_cat);
1396 
1397         OE_DEBUG_PUB.ADD('After Defaulting');
1398         OE_DEBUG_PUB.ADD('----------------');
1399         OE_DEBUG_PUB.ADD('Default from Inventory Org' ||
1400                           to_char(l_inventory_org_id));
1401 
1402         OE_DEBUG_PUB.ADD('GA5: '|| x_line_rec.global_attribute5);
1403         OE_DEBUG_PUB.ADD('GA6: '|| x_line_rec.global_attribute6);
1404         OE_DEBUG_PUB.ADD('GA7: '|| x_line_rec.global_attribute7);
1405         OE_DEBUG_PUB.ADD('GA8: '|| x_line_rec.global_attribute8);
1406         OE_DEBUG_PUB.ADD('GDF attribute Cat: '|| x_line_rec.global_attribute_category);
1407 
1408      END IF;
1409 
1410   END IF;
1411 
1412 EXCEPTION
1413   WHEN OTHERS THEN
1414        x_error_buffer := SQLERRM;
1415        x_return_code := 2;
1416 
1417 END default_gdf;
1418 
1419 
1420 -- Bug#5588076- new procedure to init LTE GDFs
1421 /*----------------------------------------------------------------------------*
1422  | PROCEDURE                                                                  |
1423  |    init_gdf                                                                |
1424  |                                                                            |
1425  | DESCRIPTION                                                                |
1426  |    this procedure initializes global attribute columns used by LTE         |
1427  |                                                                            |
1428  | PARAMETERS                                                                 |
1429  |   INPUT                                                                    |
1430  |      p_line_rec              OE_Invoice_PUB.OE_GDF_Rec_Type                |
1431  |                              Interface line record in copy_gdff/copy_gdf   |
1432  |   OUTPUT                                                                   |
1433  |      p_line_rec              OE_Invoice_PUB.OE_GDF_Rec_Type                |
1434  |                              Interface line record in copy_gdff/copy_gdf   |
1435  |                                                                            |
1436  |                                                                            |
1437  | HISTORY                                                                    |
1438  |                                                                            |
1439  |   28-NOV-2006 Phong La       Created.                                      |
1440  *----------------------------------------------------------------------------*/
1441 
1442 PROCEDURE init_gdf (
1443         p_line_rec     IN OUT NOCOPY OE_Invoice_PUB.OE_GDF_Rec_Type )
1444 IS
1445 BEGIN
1446 
1447     p_line_rec.line_gdf_attribute8  := NULL;
1448     p_line_rec.line_gdf_attribute9  := NULL;
1449     p_line_rec.line_gdf_attribute10 := NULL;
1450     p_line_rec.line_gdf_attribute11 := NULL;
1451 
1452     /* comment out for now
1453        p_line_rec.line_gdf_attribute2  := NULL;
1454        p_line_rec.line_gdf_attribute3  := NULL;
1455        p_line_rec.line_gdf_attribute12 := NULL;
1456        p_line_rec.line_gdf_attribute13 := NULL;
1457        p_line_rec.line_gdf_attribute14 := NULL;
1458        p_line_rec.line_gdf_attribute19 := NULL;
1459        p_line_rec.line_gdf_attribute20 := NULL;
1460     */
1461 
1462 END init_gdf;
1463 
1464 END JL_ZZ_RECEIV_INTERFACE;