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