[Home] [Help]
PACKAGE BODY: APPS.INV_LABEL_PVT11
Source
1 PACKAGE BODY inv_label_pvt11 AS
2 /* $Header: INVLA11B.pls 120.2 2006/05/08 22:39:38 rahugupt noship $ */
3
4 label_b CONSTANT VARCHAR2(50) := '<label';
5 label_e CONSTANT VARCHAR2(50) := '</label>' || fnd_global.local_chr(10);
6 variable_b CONSTANT VARCHAR2(50) := '<variable name= "';
7 variable_e CONSTANT VARCHAR2(50) := '</variable>' || fnd_global.local_chr(10);
8 tag_e CONSTANT VARCHAR2(50) := '>' || fnd_global.local_chr(10);
9 l_debug NUMBER;
10
11 PROCEDURE TRACE(p_message VARCHAR2) IS
12 BEGIN
13 inv_label.TRACE(p_message, 'LABEL_INV_13');
14 END TRACE;
15
16 PROCEDURE get_variable_data(
17 x_variable_content OUT NOCOPY inv_label.label_tbl_type
18 , x_msg_count OUT NOCOPY NUMBER
19 , x_msg_data OUT NOCOPY VARCHAR2
20 , x_return_status OUT NOCOPY VARCHAR2
21 , p_label_type_info IN inv_label.label_type_rec
22 , p_transaction_id IN NUMBER
23 , p_input_param IN mtl_material_transactions_temp%ROWTYPE
24 , p_transaction_identifier IN NUMBER
25 ) IS
26
27
28 -- l_lpn_id NUMBER;
29 l_content_lpn_id NUMBER;
30 l_content_item_data LONG;
31 l_selected_fields inv_label.label_field_variable_tbl_type;
32 l_selected_fields_count NUMBER;
33 l_content_rec_index NUMBER := 0;
34 l_label_format_id NUMBER := 0;
35 l_label_format VARCHAR2(100);
36 l_printer VARCHAR2(30);
37 l_printer_sub VARCHAR2(30) := NULL;
38 l_api_name VARCHAR2(20) := 'get_variable_data';
39 l_return_status VARCHAR2(240);
40 l_error_message VARCHAR2(240);
41 l_msg_count NUMBER;
42 l_api_status VARCHAR2(240);
43 l_msg_data VARCHAR2(240);
44
45 i NUMBER;
46 j NUMBER;
47 k NUMBER;
48
49 l_transaction_identifier NUMBER := 0;
50 l_label_index NUMBER;
51 l_label_request_id NUMBER;
52 l_prev_format_id NUMBER;
53 l_prev_sub VARCHAR2(30);
54 l_column_name_list LONG;
55
56
57 l_label_status VARCHAR2(1);
58 l_label_err_msg VARCHAR2(1000);
59 l_is_epc_exist VARCHAR2(1) := 'N';
60
61 l_batch_id gme_batch_header.batch_id%TYPE;
62 l_batch_no gme_batch_header.batch_no%TYPE;
63
64 l_organization mtl_parameters.organization_code%TYPE;
65
66 l_item mtl_system_items_vl.concatenated_segments%TYPE;
67 l_source_container_item mtl_system_items_vl.concatenated_segments%TYPE;
68 l_target_container_item mtl_system_items_vl.concatenated_segments%TYPE;
69 l_dispensed_container mtl_system_items_vl.concatenated_segments%TYPE;
70 l_material_container mtl_system_items_vl.concatenated_segments%TYPE;
71
72 l_quantity NUMBER := 0;
73 l_revision mtl_material_transactions.revision%TYPE;
74 l_item_description VARCHAR2(240) := NULL;
75 l_locator_id NUMBER;
76 l_quantity2 NUMBER;
77 l_reason_id NUMBER;
78 l_uom2 VARCHAR2(3);
79
80
81 l_locator mtl_item_locations_kfv.CONCATENATED_SEGMENTS%TYPE;
82 l_count NUMBER;
83
84 l_dispense_id gmo_material_dispenses.dispense_id%TYPE;
85 l_dispense_source gmo_material_dispenses.dispense_source%TYPE;
86 l_dispense_number gmo_material_dispenses.dispense_number%TYPE;
87 l_dispense_type gmo_material_dispenses.dispense_type%TYPE;
88 l_batchstep_no gme_batch_steps.batchstep_no%TYPE;
89 l_oprn_no gmd_operations.oprn_no%TYPE;
90 l_inventory_item_id gmo_material_dispenses.inventory_item_id%TYPE;
91 l_organization_id gmo_material_dispenses.organization_id%TYPE;
92 l_subinventory gmo_material_dispenses.subinventory_code%TYPE;
93 l_security_code gmo_material_dispenses.security_code%TYPE;
94 l_dispense_uom gmo_material_dispenses.dispense_uom%TYPE;
95 l_undispense_uom gmo_material_dispenses.dispense_uom%TYPE;
96 l_dispensed_date varchar2(100);
97 l_mode varchar2(100);
98 l_required_qty gmo_material_dispenses.required_qty%TYPE;
99 l_dispensed_qty gmo_material_dispenses.dispensed_qty%TYPE;
100
101 l_gross_required_weight gmo_material_dispenses.gross_required_weight%TYPE;
102 l_source_container_tare gmo_material_dispenses.source_container_tare%TYPE;
103 l_source_container_weight gmo_material_dispenses.source_container_weight%TYPE;
104 l_source_container_reweight gmo_material_dispenses.source_container_reweight%TYPE;
105 l_actual_gross_weight gmo_material_dispenses.actual_gross_weight%TYPE;
106 l_target_container_tare gmo_material_dispenses.target_container_tare%TYPE;
107 l_source_container_item_id gmo_material_dispenses.source_container_item_id%TYPE;
108 l_target_container_item_id gmo_material_dispenses.target_container_item_id%TYPE;
109
110 l_lot_number gmo_material_dispenses.lot_number%TYPE;
111 l_hazard_class po_hazard_classes.hazard_class%TYPE;
112
113 /*** undispense ****/
114 l_undispense_id gmo_material_undispenses.undispense_id%TYPE;
115 l_undispense_number gmo_material_undispenses.undispense_number%TYPE;
116 l_undispense_type gmo_material_undispenses.undispense_type%TYPE;
117 l_undispensed_date varchar2(100);
118 l_undispensed_qty gmo_material_undispenses.undispensed_qty%TYPE;
119 l_undispense_source gmo_material_dispenses.dispense_source%TYPE;
120
121
122 l_material_qty NUMBER;
123 l_negation_qty NUMBER;
124
125 l_print_count NUMBER;
126 l_reprint VARCHAR2(10);
127 l_parent_lot_number mtl_lot_numbers.parent_lot_number%TYPE;
128
129 l_dispensing_mode varchar2(100);
130 l_undispensing_mode varchar2(100);
131 l_batch_line varchar2(100);
132
133
134 ---------------------------------------------------------------------------------------------
135 -- Project: 'Custom Labels' (A 11i10+ Project) |
136 ---------------------------------------------------------------------------------------------
137 l_sql_stmt VARCHAR2(4000);
138 l_sql_stmt_result VARCHAR2(4000) := NULL;
139 TYPE sql_stmt IS REF CURSOR;
140 c_sql_stmt sql_stmt;
141 l_custom_sql_ret_status VARCHAR2(1);
142 l_custom_sql_ret_msg VARCHAR2(2000);
143
144 -- Fix for bug: 4179593 Start
145 l_CustSqlWarnFlagSet BOOLEAN;
146 l_CustSqlErrFlagSet BOOLEAN;
147 l_CustSqlWarnMsg VARCHAR2(2000);
148 l_CustSqlErrMsg VARCHAR2(2000);
149 -- Fix for bug: 4179593 End
150
151 ------------------------End of this change for Custom Labels project code--------------------
152
153
154 /* main driving cursor */
155
156 CURSOR c_dispense_details IS
157 SELECT
158 mp.ORGANIZATION_CODE,
159 d.dispense_id,
160 d.dispense_source,
161 d.dispense_number,
162 d.dispense_type,
163 bh.batch_no,
164 bs.batchstep_no,
165 gop.oprn_no,
166 msi.concatenated_segments dispense_item,
167 d.organization_id,
168 d.subinventory_code,
169 d.security_code,
170 d.dispense_uom,
171 FND_DATE.DATE_TO_DISPLAYDT(d.dispensed_date, FND_TIMEZONES.GET_SERVER_TIMEZONE_CODE) dispensed_date,
172 d.dispensing_mode,
173 d.required_qty,
174 d.dispensed_qty,
175 d.gross_required_weight,
176 d.source_container_tare,
177 d.source_container_weight,
178 d.source_container_reweight,
179 d.actual_gross_weight,
180 d.target_container_tare,
181 d.source_container_item_id,
182 d.target_container_item_id,
183 d.lot_number,
184 hzc.hazard_class,
185 d.inventory_item_id
186 from
187 gmo_material_dispenses d, gme_batch_header bh,
188 gme_batch_steps bs, mtl_parameters mp, gmd_operations gop,
189 mtl_system_items_vl msi, po_hazard_classes hzc
190 where d.dispense_id = p_transaction_id AND
191 d.batch_id = bh.batch_id AND
192 d.organization_id = mp.organization_id AND
193 d.batch_step_id = bs.batchstep_id(+) AND
194 d.inventory_item_id = msi.inventory_item_id AND
195 d.organization_id = msi.organization_id AND
196 bs.oprn_id = gop.oprn_id(+) AND
197 msi.hazard_class_id = hzc.hazard_class_id (+);
198
199 /* undispense cursor */
200 CURSOR c_undispense_details IS
201 SELECT
202 mp.ORGANIZATION_CODE,
203 d.dispense_id,
204 d.dispense_source,
205 und.undispense_number,
206 und.undispense_type,
207 bh.batch_no,
208 bs.batchstep_no,
209 gop.oprn_no,
210 msi.concatenated_segments dispense_item,
211 und.organization_id,
212 und.subinventory_code,
213 und.security_code,
214 und.dispense_uom,
215 FND_DATE.DATE_TO_DISPLAYDT(und.undispensed_date, FND_TIMEZONES.GET_SERVER_TIMEZONE_CODE) undispensed_date,
216 und.undispensing_mode,
217 d.required_qty,
218 und.dispensed_qty,
219 und.gross_required_weight,
220 und.source_container_tare,
221 und.source_container_weight,
222 und.source_container_reweight,
223 und.actual_gross_weight,
224 und.target_container_tare,
225 und.source_container_item_id,
226 und.target_container_item_id,
227 und.lot_number,
228 hzc.hazard_class,
229 und.undispense_id,
230 (nvl(und.undispensed_qty,0) + nvl(und.material_loss,0)) undispensed_qty,
231 und.inventory_item_id
232 from
233 gmo_material_dispenses d, gme_batch_header bh,
234 gme_batch_steps bs, mtl_parameters mp, gmd_operations gop,
235 mtl_system_items_vl msi, po_hazard_classes hzc , gmo_material_undispenses und
236 where und.undispense_id = p_transaction_id AND
237 und.dispense_id = d.dispense_id AND
238 und.batch_id = bh.batch_id AND
239 und.organization_id = mp.organization_id AND
240 und.batch_step_id = bs.batchstep_id(+) AND
241 und.inventory_item_id = msi.inventory_item_id AND
242 und.organization_id = msi.organization_id AND
243 bs.oprn_id = gop.oprn_id(+) AND
244 msi.hazard_class_id = hzc.hazard_class_id (+);
245
246
247 cursor c_get_mode_meaning is select meaning from fnd_lookups where lookup_type='GMO_DISPENSE_MODE' and lookup_code=l_mode;
248
249 BEGIN
250 -- Initialize return status as success
251 x_return_status := fnd_api.g_ret_sts_success;
252 l_debug := inv_label.l_debug;
253 l_label_status := INV_LABEL.G_SUCCESS;
254
255 IF (l_debug = 1) THEN
256 TRACE('***** In PVT11: (GMO Label) ***********');
257 TRACE(
258 ' Business_flow= '
259 || p_label_type_info.business_flow_code
260 || ', Transaction ID= '
261 || p_transaction_id
262 || ', Transaction Identifier= '
263 || p_transaction_identifier
264 || ', Label Type ID= '
265 || p_label_type_info.label_type_id
266 );
267 END IF;
268
269 inv_label.get_variables_for_format(
270 x_variables => l_selected_fields
271 , x_variables_count => l_selected_fields_count
272 , p_format_id => p_label_type_info.default_format_id
273 );
274
275 IF (l_selected_fields_count = 0)
276 OR (l_selected_fields.COUNT = 0) THEN
277 IF (l_debug = 1) THEN
278 TRACE(
279 'no fields defined for this format: '
280 || p_label_type_info.default_format_id
281 || ','
282 || p_label_type_info.default_format_name
283 );
284 END IF;
285 --return;
286 END IF;
287
288 IF (l_debug = 1) THEN
289 TRACE(
290 ' Found variable defined for this format, cont = '
291 || l_selected_fields_count
292 );
293 TRACE(' Getting GMO Dispense data...');
294 END IF;
295
296
297 IF p_transaction_id IS NOT NULL
298 THEN
299 -- txn driven
300 i := 1;
301 l_content_rec_index := 0;
302 l_content_item_data := '';
303
304 l_printer := p_label_type_info.default_printer;
305 l_label_index := 1;
306 l_prev_format_id := p_label_type_info.default_format_id;
307 l_prev_sub := '####';
308
309
310 IF (l_debug = 1) THEN
311 TRACE('** in GMO PVT11.get_variable_data ** , start ' || l_label_index );
312 END IF;
313
314 if (p_transaction_identifier = inv_label.TRX_ID_DIS)
315 THEN
316
317 OPEN c_dispense_details;
318
319 IF c_dispense_details%NOTFOUND
320 THEN
321 IF (l_debug = 1) THEN
322 TRACE('** Did NOT find and Dispense details ..' );
323 close c_dispense_details;
324 Return;
325 END IF;
326 ELSE
327 IF (l_debug = 1) THEN
328 TRACE('** Found data for Dispense details ' );
329 END IF;
330 END IF;
331
332
333
334 FETCH c_dispense_details into
335 l_organization ,
336 l_dispense_id,
337 l_dispense_source,
338 l_dispense_number,
339 l_dispense_type,
340 l_batch_no,
341 l_batchstep_no,
342 l_oprn_no,
343 l_item ,
344 l_organization_id,
345 l_subinventory,
346 l_security_code,
347 l_dispense_uom,
348 l_dispensed_date,
349 l_mode,
350 l_required_qty,
351 l_dispensed_qty,
352 l_gross_required_weight,
353 l_source_container_tare,
354 l_source_container_weight,
355 l_source_container_reweight,
356 l_actual_gross_weight,
357 l_target_container_tare,
358 l_source_container_item_id,
359 l_target_container_item_id,
360 l_lot_number,
361 l_hazard_class,
362 l_inventory_item_id;
363 close c_dispense_details;
364
365 ELSIF (p_transaction_identifier = inv_label.TRX_ID_UNDIS) -- UNDISPENSIN transaction
366 THEN
367 OPEN c_undispense_details;
368
369 IF c_undispense_details%NOTFOUND
370 THEN
371 IF (l_debug = 1) THEN
372 TRACE('Did NOT find and Dispense details ..' );
373 close c_undispense_details;
374 Return;
375 END IF;
376 ELSE
377 IF (l_debug = 1) THEN
378 TRACE('Found data for Dispense details' );
379 END IF;
380 END IF;
381
382 FETCH c_undispense_details into
383 l_organization ,
384 l_dispense_id,
385 l_undispense_source,
386 l_undispense_number,
387 l_undispense_type,
388 l_batch_no,
389 l_batchstep_no,
390 l_oprn_no,
391 l_item ,
392 l_organization_id,
393 l_subinventory,
394 l_security_code,
395 l_undispense_uom,
396 l_undispensed_date,
397 l_mode,
398 l_required_qty,
399 l_dispensed_qty,
400 l_gross_required_weight,
401 l_source_container_tare,
402 l_source_container_weight,
403 l_source_container_reweight,
404 l_actual_gross_weight,
405 l_target_container_tare,
406 l_source_container_item_id,
407 l_target_container_item_id,
408 l_lot_number,
409 l_hazard_class,
410 l_undispense_id ,
411 l_undispensed_qty,
412 l_inventory_item_id;
413
414 close c_undispense_details;
415 END IF;
416
417 /* source_container_item */
418 IF l_source_container_item_id IS NOT NULL
419 THEN
420 Select concatenated_segments
421 INTO l_source_container_item
422 from mtl_system_items_vl
423 where organization_id = l_organization_id AND
424 inventory_item_id = l_source_container_item_id;
425 END IF;
426
427 /* target_container_item */
428 IF l_target_container_item_id IS NOT NULL
429 THEN
430 Select concatenated_segments
431 INTO l_target_container_item
432 from mtl_system_items_vl
433 where organization_id = l_organization_id AND
434 inventory_item_id = l_target_container_item_id;
435 END IF;
436
437 if (l_batchstep_no is not null)
438 then
439 l_batch_line := l_batchstep_no || '-' || l_oprn_no;
440 end if;
441
442 /* dispensed container/material LOGIC */
443
444 IF ( p_label_type_info.label_type_id = 11)
445 -- Process Material label
446 THEN
447 if (p_transaction_identifier = inv_label.TRX_ID_DIS)
448 then
449
450 open c_get_mode_meaning;
451 fetch c_get_mode_meaning into l_dispensing_mode;
452 close c_get_mode_meaning;
453
454 IF (l_mode = 'FULL_CONTAINER')
455 THEN
456 l_actual_gross_weight := NULL;
457 l_material_container := NULL;
458
459 ELSIF (l_mode = 'TARGET_CONTAINER')
460 THEN
461 -- l_actual_gross_weight; use db value
462 l_material_container := l_source_container_item;
463 l_material_qty := l_source_container_reweight - l_source_container_tare;
464 l_dispensed_container := l_target_container_item;
465
466 ELSIF (l_mode = 'SOURCE_CONTAINER')
467 THEN
468 l_material_container := l_target_container_item;
469 l_material_qty := l_actual_gross_weight - l_target_container_tare;
470 l_dispensed_container := l_source_container_item;
471 END IF;
472
473 ELSIF (p_transaction_identifier = inv_label.TRX_ID_UNDIS)
474 THEN
475 open c_get_mode_meaning;
476 fetch c_get_mode_meaning into l_undispensing_mode;
477 close c_get_mode_meaning;
478
479 IF (l_mode = 'FULL_CONTAINER')
480 THEN
481 l_material_container := l_source_container_item;
482 l_dispensed_container := NULL;
483 l_actual_gross_weight := l_source_container_weight;
484 l_material_qty := l_source_container_weight - l_source_container_tare;
485
486 ELSIF (l_mode = 'TARGET_CONTAINER')
487 THEN
488 -- l_actual_gross_weight; use db value
489 l_material_container := l_target_container_item;
490 l_material_qty := l_actual_gross_weight - l_target_container_tare;
491 l_dispensed_container := l_source_container_item;
492
493 ELSIF (l_mode = 'SOURCE_CONTAINER')
494 THEN
495 NULL; -- this mode is not applicable for reverse dispense
496
497 END IF;
498
499 END IF;
500
501 ELSIF ( p_label_type_info.label_type_id = 12) -- dispense material label
502 THEN
503
504 if (p_transaction_identifier = inv_label.TRX_ID_DIS)
505 then
506 open c_get_mode_meaning;
507 fetch c_get_mode_meaning into l_dispensing_mode;
508 close c_get_mode_meaning;
509
510 IF (l_mode = 'TARGET_CONTAINER')
511 THEN
512 l_dispensed_container := l_target_container_item;
513
514 ELSIF (l_mode in ('SOURCE_CONTAINER', 'FULL_CONTAINER'))
515 THEN
516 -- l_actual_gross_weight; use db value
517 l_dispensed_container := l_source_container_item;
518 END IF;
519
520
521 /* dispense qty */
522 l_dispensed_qty := GMO_DISPENSE_PVT.GET_NET_DISP_DISPENSED_QTY (P_DISPENSE_ID => l_dispense_id);
523
524
525 ELSIF (p_transaction_identifier = inv_label.TRX_ID_UNDIS)
526 THEN
527 -- UNDISPENSIN transaction
528
529 open c_get_mode_meaning;
530 fetch c_get_mode_meaning into l_undispensing_mode;
531 close c_get_mode_meaning;
532
533
534 IF (l_mode = 'FULL_CONTAINER')
535 THEN
536 -- l_actual_gross_weight; use db value
537 l_dispensed_container := l_source_container_item;
538
539 ELSIF (l_mode = 'TARGET_CONTAINER')
540 THEN
541 l_dispensed_container := l_source_container_item;
542
543 ELSIF (l_mode = 'SOURCE_CONTAINER')
544 THEN
545 NULL; -- this mode is not applicable for reverse dispense
546
547 END IF;
548
549
550 END IF;
551
552 END IF;
553
554
555 IF (l_lot_number is not null)
556 THEN
557 SELECT parent_lot_number
558 into l_parent_lot_number
559 FROM mtl_lot_numbers
560 WHERE organization_id = l_organization_id
561 AND inventory_item_id = l_inventory_item_id
562 AND lot_number = l_lot_number;
563 END IF;
564
565
566 IF (l_debug = 1) THEN
567 TRACE(
568 'Organization= '
569 || l_organization
570 || ' ,Batch= '
571 || l_batch_no
572 || ' ,Item= '
573 || l_item
574 );
575 END IF;
576
577
578 GMO_LABEL_MGMT_GRP.GET_PRINT_COUNT(
579 p_api_version => 1.0,
580 p_init_msg_list => FND_API.G_FALSE,
581 x_return_status => l_return_status,
582 x_msg_count => l_msg_count,
583 x_msg_data => l_msg_data,
584 P_WMS_BUSINESS_FLOW_CODE => p_label_type_info.business_flow_code,
585 P_LABEL_TYPE => p_label_type_info.label_type_id,
586 P_TRANSACTION_ID => p_transaction_id ,
587 P_TRANSACTION_TYPE => p_transaction_identifier,
588 x_print_count => l_print_count
589 );
590
591 if (l_print_count > 0) then
592 l_reprint := fnd_message.get_string('GMO', 'GMO_UTIL_YES');
593 else
594 l_reprint := fnd_message.get_string('GMO', 'GMO_UTIL_NO');
595 end if;
596
597
598 l_content_item_data := '';
599
600 IF (l_debug = 1) THEN
601 TRACE(
602 'Apply Rules engine for format, printer='
603 || l_printer
604 || ',manual_format_id= '
605 || p_label_type_info.manual_format_id
606 || ',manual_format_name= '
607 || p_label_type_info.manual_format_name
608 );
609 END IF;
610
611 -- insert a record into wms_label_requests entity to
612 -- call the label rules engine to get appropriate label
613
614 inv_label.get_format_with_rule(
615 p_document_id => p_label_type_info.label_type_id
616 , p_label_format_id => p_label_type_info.manual_format_id
617 , p_organization_id => l_organization_id
618 , p_inventory_item_id => l_inventory_item_id
619 , p_subinventory_code => l_subinventory
620 , p_locator_id => l_locator_id
621 , p_lpn_id => NULL
622 , p_lot_number => l_lot_number
623 , p_revision => l_revision
624 , p_serial_number => NULL
625 , p_business_flow_code => p_label_type_info.business_flow_code
626 , p_last_update_date => SYSDATE
627 , p_last_updated_by => fnd_global.user_id
628 , p_creation_date => SYSDATE
629 , p_created_by => fnd_global.user_id
630 , p_printer_name => l_printer
631 , x_return_status => l_return_status
632 , x_label_format_id => l_label_format_id
633 , x_label_format => l_label_format
634 , x_label_request_id => l_label_request_id
635 );
636
637 IF l_return_status <> 'S' THEN
638 fnd_message.set_name('WMS', 'WMS_LABL_RULE_ENGINE_FAILED');
639 fnd_msg_pub.ADD;
640 l_label_format := p_label_type_info.default_format_id;
641 l_label_format_id := p_label_type_info.default_format_name;
642 l_label_status := INV_LABEL.G_ERROR;
643 END IF;
644
645 IF (l_label_format_id IS NOT NULL) THEN
646 -- Derive the fields for the format either passed in or derived via the rules engine.
647 IF l_label_format_id <> NVL(l_prev_format_id, -999) THEN
648 IF (l_debug = 1) THEN
649 TRACE(' Getting variables for new format '|| l_label_format);
650 END IF;
651
652 inv_label.get_variables_for_format(
653 x_variables => l_selected_fields
654 , x_variables_count => l_selected_fields_count
655 , p_format_id => l_label_format_id
656 );
657
658 l_prev_format_id := l_label_format_id;
659
660 IF (l_selected_fields_count = 0)
661 OR (l_selected_fields.COUNT = 0) THEN
662 IF (l_debug = 1) THEN
663 TRACE(
664 'no fields defined for this format: '
665 || l_label_format
666 || ','
667 || l_label_format_id
668 );
669 END IF;
670
671 END IF;
672
673 IF (l_debug = 1) THEN
674 TRACE(
675 ' Found selected_fields for format '
676 || l_label_format
677 || ', num='
678 || l_selected_fields_count
679 );
680 END IF;
681 END IF;
682 ELSE
683 IF (l_debug = 1) THEN
684 TRACE('No format exists for this label');
685 END IF;
686 END IF;
687
688 -- variable header
689
690 l_content_item_data := l_content_item_data || label_b;
691
692 IF l_label_format <> NVL(p_label_type_info.default_format_name, '@@@') THEN
693 l_content_item_data := l_content_item_data
694 || ' _FORMAT="'
695 || NVL(
696 p_label_type_info.manual_format_name
697 , l_label_format
698 )
699 || '"';
700 END IF;
701
702 IF (l_printer IS NOT NULL)
703 AND (l_printer <> NVL(p_label_type_info.default_printer, '###')) THEN
704 l_content_item_data := l_content_item_data || ' _PRINTERNAME="' || l_printer || '"';
705 END IF;
706
707 l_content_item_data := l_content_item_data || tag_e;
708
709 IF (l_debug = 1) THEN
710 TRACE('Starting assign variables, ');
711 END IF;
712
713 l_column_name_list := 'Set variables for ';
714
715 -- CUSTOM SQL
716 l_custom_sql_ret_status := FND_API.G_RET_STS_SUCCESS;
717 l_CustSqlWarnFlagSet := FALSE;
718 l_CustSqlErrFlagSet := FALSE;
719 l_CustSqlWarnMsg := NULL;
720 l_CustSqlErrMsg := NULL;
721 -- CUSTOM SQL
722
723
724 -- Loop for each selected fields, find the columns and write into the XML_content
725
726 FOR i IN 1 .. l_selected_fields.COUNT LOOP
727
728 IF (l_debug = 1) THEN
729 l_column_name_list := l_column_name_list || ',' || l_selected_fields(i).column_name;
730 END IF;
731 ---------------------------------------------------------------------------------------------
732 -- Project: 'Custom Labels' (A 11i10+ Project) |
733 -- Author: Dinesh ([email protected]) |
734 -- Change Description: |
735 -- The check (SQL_STMT <> NULL and COLUMN_NAME = NULL) implies that the field is a |
736 -- Custom SQL based field. Handle it appropriately. |
737 ---------------------------------------------------------------------------------------------
738 IF (l_selected_fields(i).SQL_STMT IS NOT NULL AND l_selected_fields(i).column_name = 'sql_stmt') THEN
739 IF (l_debug = 1) THEN
740 trace('Custom Labels Trace [INVLA11B.pls]: ------------------------- REPORT BEGIN-------------------------------------');
741 trace('Custom Labels Trace [INVLA11B.pls]: LABEL_FIELD_ID : ' || l_selected_fields(i).label_field_id);
742 trace('Custom Labels Trace [INVLA11B.pls]: FIELD_VARIABLE_NAME : ' || l_selected_fields(i).variable_name);
743 trace('Custom Labels Trace [INVLA11B.pls]: COLUMN_NAME : ' || l_selected_fields(i).column_name);
744 trace('Custom Labels Trace [INVLA11B.pls]: SQL_STMT : ' || l_selected_fields(i).sql_stmt);
745 END IF;
746 l_sql_stmt := l_selected_fields(i).sql_stmt;
747 IF (l_debug = 1) THEN
748 trace('Custom Labels Trace [INVLAP13B.pls]: l_sql_stmt BEFORE REQUEST_ID Filter Concatenation: ' || l_sql_stmt);
749 END IF;
750 l_sql_stmt := l_sql_stmt || ' AND WLR.LABEL_REQUEST_ID = :REQUEST_ID';
751 IF (l_debug = 1) THEN
752 trace('Custom Labels Trace [INVLAP13B.pls]: l_sql_stmt AFTER REQUEST_ID Filter Concatenation: ' || l_sql_stmt);
753
754 END IF;
755 BEGIN
756 IF (l_debug = 1) THEN
757 trace('Custom Labels Trace [INVLA11B.pls]: At Breadcrumb 1');
758 trace('Custom Labels Trace [INVLA11B.pls]: LABEL_REQUEST_ID : ' || l_label_request_id);
759 END IF;
760 OPEN c_sql_stmt FOR l_sql_stmt using l_label_request_id;
761 LOOP
762 FETCH c_sql_stmt INTO l_sql_stmt_result;
763 EXIT WHEN c_sql_stmt%notfound OR c_sql_stmt%rowcount >=2;
764 END LOOP;
765
766 IF (c_sql_stmt%rowcount=1 AND l_sql_stmt_result IS NULL) THEN
767 x_return_status := FND_API.G_RET_STS_SUCCESS;
768 l_custom_sql_ret_status := INV_LABEL.G_WARNING;
769 fnd_message.set_name('WMS','WMS_CS_NULL_VALUE_RETURNED');
770 fnd_msg_pub.ADD;
771 -- Fix for bug: 4179593 Start
772 --fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => l_custom_sql_ret_msg);
773 l_custom_sql_ret_msg := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_last, p_encoded => fnd_api.g_false);
774 l_CustSqlWarnMsg := l_custom_sql_ret_msg;
775 l_CustSqlWarnFlagSet := TRUE;
776 -- Fix for bug: 4179593 End
777 IF (l_debug = 1) THEN
778 trace('Custom Labels Trace [INVLA11B.pls]: At Breadcrumb 2');
779 trace('Custom Labels Trace [INVLA11B.pls]: l_sql_stmt_result is: ' || l_sql_stmt_result);
780 trace('Custom Labels Trace [INVLA11B.pls]: WARNING: NULL value returned.');
781 trace('Custom Labels Trace [INVLA11B.pls]: l_custom_sql_ret_status is set to : ' || l_custom_sql_ret_status);
782 END IF;
783 ELSIF c_sql_stmt%rowcount=0 THEN
784 IF (l_debug = 1) THEN
785 trace('Custom Labels Trace [INVLA11B.pls]: At Breadcrumb 3');
786 trace('Custom Labels Trace [INVLA11B.pls]: WARNING: No row returned by the Custom SQL query');
787 END IF;
788 x_return_status := FND_API.G_RET_STS_SUCCESS;
789 l_custom_sql_ret_status := INV_LABEL.G_WARNING;
790 fnd_message.set_name('WMS','WMS_CS_NO_DATA_FOUND');
791 fnd_msg_pub.ADD;
792 -- Fix for bug: 4179593 Start
793 --fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => l_custom_sql_ret_msg);
794 l_custom_sql_ret_msg := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_last, p_encoded => fnd_api.g_false);
795 l_CustSqlWarnMsg := l_custom_sql_ret_msg;
796 l_CustSqlWarnFlagSet := TRUE;
797 -- Fix for bug: 4179593 End
798 ELSIF c_sql_stmt%rowcount>=2 THEN
799 IF (l_debug = 1) THEN
800 trace('Custom Labels Trace [INVLA11B.pls]: At Breadcrumb 4');
801 trace('Custom Labels Trace [INVLA11B.pls]: ERROR: Multiple values returned by the Custom SQL query');
802 END IF;
803 l_sql_stmt_result := NULL;
804 x_return_status := FND_API.G_RET_STS_SUCCESS;
805 l_custom_sql_ret_status := FND_API.G_RET_STS_ERROR;
806 fnd_message.set_name('WMS','WMS_CS_MULTIPLE_VALUES_RETURN');
807 fnd_msg_pub.ADD;
808 -- Fix for bug: 4179593 Start
809 --fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => l_custom_sql_ret_msg);
810 l_custom_sql_ret_msg := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_last, p_encoded => fnd_api.g_false);
811 l_CustSqlErrMsg := l_custom_sql_ret_msg;
812 l_CustSqlErrFlagSet := TRUE;
813 -- Fix for bug: 4179593 End
814 END IF;
815 IF (c_sql_stmt%ISOPEN) THEN
816 CLOSE c_sql_stmt;
817 END IF;
818 EXCEPTION
819 WHEN OTHERS THEN
820 IF (c_sql_stmt%ISOPEN) THEN
821 CLOSE c_sql_stmt;
822 END IF;
823 IF (l_debug = 1) THEN
824 trace('Custom Labels Trace [INVLA11B.pls]: At Breadcrumb 5');
825 trace('Custom Labels Trace [INVLA11B.pls]: Unexpected Error has occured in GET_VARIABLES_DATA');
826 END IF;
827 x_return_status := FND_API.G_RET_STS_ERROR;
828 fnd_message.set_name('WMS','WMS_CS_WRONG_SQL_CONSTRUCT');
829 fnd_msg_pub.ADD;
830 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
831 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
832 END;
833 IF (l_debug = 1) THEN
834 trace('Custom Labels Trace [INVLA11B.pls]: At Breadcrumb 6');
835 trace('Custom Labels Trace [INVLA11B.pls]: Before assigning it to l_content_item_data');
836 END IF;
837 l_content_item_data := l_content_item_data
838 || variable_b
839 || l_selected_fields(i).variable_name
840 || '">'
841 || l_sql_stmt_result
842 || variable_e;
843 l_sql_stmt_result := NULL;
844 l_sql_stmt := NULL;
845 IF (l_debug = 1) THEN
846 trace('Custom Labels Trace [INVLA11B.pls]: At Breadcrumb 7');
847 trace('Custom Labels Trace [INVLA11B.pls]: After assigning it to l_content_item_data');
848 trace('Custom Labels Trace [INVLAP3B.pls]: --------------------------REPORT END-------------------------------------');
849 END IF;
850 ------------------------End of this changes for Custom Labels project code--------------------
851 ELSIF LOWER(l_selected_fields(i).column_name) = 'batch_no' THEN
852 l_content_item_data := l_content_item_data
853 || variable_b
854 || l_selected_fields(i).variable_name
855 || '">'
856 || l_batch_no
857 || variable_e;
858
859 ELSIF LOWER(l_selected_fields(i).column_name) = 'oprn_no' THEN
860 l_content_item_data := l_content_item_data
861 || variable_b
862 || l_selected_fields(i).variable_name
863 || '">'
864 || l_batch_line
865 || variable_e;
866
867 ELSIF LOWER(l_selected_fields(i).column_name) = 'dispensing_item' THEN
868 l_content_item_data := l_content_item_data
869 || variable_b
870 || l_selected_fields(i).variable_name
871 || '">'
872 || l_item
873 || variable_e;
874
875 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_number' THEN
876 l_content_item_data := l_content_item_data
877 || variable_b
878 || l_selected_fields(i).variable_name
879 || '">'
880 || l_lot_number
881 || variable_e;
882
883 ELSIF LOWER(l_selected_fields(i).column_name) = 'organization_code' THEN
884 l_content_item_data := l_content_item_data
885 || variable_b
886 || l_selected_fields(i).variable_name
887 || '">'
888 || l_organization
889 || variable_e;
890 ELSIF LOWER(l_selected_fields(i).column_name) = 'subinventory_code' THEN
891 l_content_item_data := l_content_item_data
892 || variable_b
893 || l_selected_fields(i).variable_name
894 || '">'
895 || l_subinventory
896 || variable_e;
897
898 ELSIF LOWER(l_selected_fields(i).column_name) = 'dispense_source' THEN
899 l_content_item_data := l_content_item_data
900 || variable_b
901 || l_selected_fields(i).variable_name
902 || '">'
903 || l_dispense_source
904 || variable_e;
905
906 ELSIF LOWER(l_selected_fields(i).column_name) = 'dispense_number' THEN
907 l_content_item_data := l_content_item_data
908 || variable_b
909 || l_selected_fields(i).variable_name
910 || '">'
911 || l_dispense_number
912 || variable_e;
913
914 ELSIF LOWER(l_selected_fields(i).column_name) = 'dispense_type' THEN
915 l_content_item_data := l_content_item_data
916 || variable_b
917 || l_selected_fields(i).variable_name
918 || '">'
919 || l_dispense_type
920 || variable_e;
921
922 ELSIF LOWER(l_selected_fields(i).column_name) = 'dispense_id' THEN
923 l_content_item_data := l_content_item_data
924 || variable_b
925 || l_selected_fields(i).variable_name
926 || '">'
927 || l_dispense_id
928 || variable_e;
929
930 ELSIF LOWER(l_selected_fields(i).column_name) = 'dispense_uom' THEN
931 l_content_item_data := l_content_item_data
932 || variable_b
933 || l_selected_fields(i).variable_name
934 || '">'
935 || l_dispense_uom
936 || variable_e;
937
938 ELSIF LOWER(l_selected_fields(i).column_name) = 'dispensed_date' THEN
939 l_content_item_data := l_content_item_data
940 || variable_b
941 || l_selected_fields(i).variable_name
942 || '">'
943 || l_dispensed_date
944 || variable_e;
945
946 ELSIF LOWER(l_selected_fields(i).column_name) = 'dispensing_mode' THEN
947 l_content_item_data := l_content_item_data
948 || variable_b
949 || l_selected_fields(i).variable_name
950 || '">'
951 || l_dispensing_mode
952 || variable_e;
953
954 ELSIF LOWER(l_selected_fields(i).column_name) = 'required_qty' THEN
955 l_content_item_data := l_content_item_data
956 || variable_b
957 || l_selected_fields(i).variable_name
958 || '">'
959 || l_required_Qty
960 || variable_e;
961
962 ELSIF LOWER(l_selected_fields(i).column_name) = 'dispensed_qty' THEN
963 l_content_item_data := l_content_item_data
964 || variable_b
965 || l_selected_fields(i).variable_name
966 || '">'
967 || l_dispensed_qty
968 || variable_e;
969
970
971 ELSIF LOWER(l_selected_fields(i).column_name) = 'gross_required_weight' THEN
972 l_content_item_data := l_content_item_data
973 || variable_b
974 || l_selected_fields(i).variable_name
975 || '">'
976 || l_gross_required_weight
977 || variable_e;
978
979 ELSIF LOWER(l_selected_fields(i).column_name) = 'source_container_tare' THEN
980 l_content_item_data := l_content_item_data
981 || variable_b
982 || l_selected_fields(i).variable_name
983 || '">'
984 || l_source_container_tare
985 || variable_e;
986
987 ELSIF LOWER(l_selected_fields(i).column_name) = 'source_container_weight' THEN
988 l_content_item_data := l_content_item_data
989 || variable_b
990 || l_selected_fields(i).variable_name
991 || '">'
992 || l_source_container_weight
993 || variable_e;
994
995 ELSIF LOWER(l_selected_fields(i).column_name) = 'source_container_reweight' THEN
996 l_content_item_data := l_content_item_data
997 || variable_b
998 || l_selected_fields(i).variable_name
999 || '">'
1000 || l_source_container_reweight
1001 || variable_e;
1002
1003 ELSIF LOWER(l_selected_fields(i).column_name) = 'actual_gross_weight' THEN
1004 l_content_item_data := l_content_item_data
1005 || variable_b
1006 || l_selected_fields(i).variable_name
1007 || '">'
1008 || l_actual_gross_weight
1009 || variable_e;
1010
1011 ELSIF LOWER(l_selected_fields(i).column_name) = 'target_container_tare' THEN
1012 l_content_item_data := l_content_item_data
1013 || variable_b
1014 || l_selected_fields(i).variable_name
1015 || '">'
1016 || l_target_Container_tare
1017 || variable_e;
1018
1019 ELSIF LOWER(l_selected_fields(i).column_name) = 'source_container_item' THEN
1020 l_content_item_data := l_content_item_data
1021 || variable_b
1022 || l_selected_fields(i).variable_name
1023 || '">'
1024 || l_source_container_item
1025 || variable_e;
1026
1027 ELSIF LOWER(l_selected_fields(i).column_name) = 'target_container_item' THEN
1028 l_content_item_data := l_content_item_data
1029 || variable_b
1030 || l_selected_fields(i).variable_name
1031 || '">'
1032 || l_target_container_item
1033 || variable_e;
1034
1035 ELSIF LOWER(l_selected_fields(i).column_name) = 'hazard_class' THEN
1036 l_content_item_data := l_content_item_data
1037 || variable_b
1038 || l_selected_fields(i).variable_name
1039 || '">'
1040 || l_hazard_class
1041 || variable_e;
1042 ELSIF LOWER(l_selected_fields(i).column_name) = 'dispensed_container' THEN
1043 l_content_item_data := l_content_item_data
1044 || variable_b
1045 || l_selected_fields(i).variable_name
1046 || '">'
1047 || l_dispensed_container
1048 || variable_e;
1049
1050 ELSIF LOWER(l_selected_fields(i).column_name) = 'dispense_container' THEN
1051 l_content_item_data := l_content_item_data
1052 || variable_b
1053 || l_selected_fields(i).variable_name
1054 || '">'
1055 || l_dispensed_container
1056 || variable_e;
1057
1058 ELSIF LOWER(l_selected_fields(i).column_name) = 'material_container' THEN
1059 l_content_item_data := l_content_item_data
1060 || variable_b
1061 || l_selected_fields(i).variable_name
1062 || '">'
1063 || l_material_container
1064 || variable_e;
1065 ELSIF LOWER(l_selected_fields(i).column_name) = 'material_quantity' THEN
1066 l_content_item_data := l_content_item_data
1067 || variable_b
1068 || l_selected_fields(i).variable_name
1069 || '">'
1070 || l_material_qty
1071 || variable_e;
1072
1073 ELSIF LOWER(l_selected_fields(i).column_name) = 'undispense_number' THEN
1074 l_content_item_data := l_content_item_data
1075 || variable_b
1076 || l_selected_fields(i).variable_name
1077 || '">'
1078 || l_undispense_number
1079 || variable_e;
1080 ELSIF LOWER(l_selected_fields(i).column_name) = 'undispense_type' THEN
1081 l_content_item_data := l_content_item_data
1082 || variable_b
1083 || l_selected_fields(i).variable_name
1084 || '">'
1085 || l_undispense_type
1086 || variable_e;
1087 ELSIF LOWER(l_selected_fields(i).column_name) = 'undispensed_date' THEN
1088 l_content_item_data := l_content_item_data
1089 || variable_b
1090 || l_selected_fields(i).variable_name
1091 || '">'
1092 || l_undispensed_date
1093 || variable_e;
1094 ELSIF LOWER(l_selected_fields(i).column_name) = 'undispensed_qty' THEN
1095 l_content_item_data := l_content_item_data
1096 || variable_b
1097 || l_selected_fields(i).variable_name
1098 || '">'
1099 || l_undispensed_qty
1100 || variable_e;
1101
1102 ELSIF LOWER(l_selected_fields(i).column_name) = 'undispensing_mode' THEN
1103 l_content_item_data := l_content_item_data
1104 || variable_b
1105 || l_selected_fields(i).variable_name
1106 || '">'
1107 || l_undispensing_mode
1108 || variable_e;
1109
1110 ELSIF LOWER(l_selected_fields(i).column_name) = 'undispense_uom' THEN
1111 l_content_item_data := l_content_item_data
1112 || variable_b
1113 || l_selected_fields(i).variable_name
1114 || '">'
1115 || l_undispense_uom
1116 || variable_e;
1117
1118 ELSIF LOWER(l_selected_fields(i).column_name) = 'undispense_id' THEN
1119 l_content_item_data := l_content_item_data
1120 || variable_b
1121 || l_selected_fields(i).variable_name
1122 || '">'
1123 || l_undispense_id
1124 || variable_e;
1125
1126 ELSIF LOWER(l_selected_fields(i).column_name) = 'undispense_source' THEN
1127 l_content_item_data := l_content_item_data
1128 || variable_b
1129 || l_selected_fields(i).variable_name
1130 || '">'
1131 || l_undispense_source
1132 || variable_e;
1133
1134 ELSIF LOWER(l_selected_fields(i).column_name) = 'parent_lot_number' THEN
1135 l_content_item_data := l_content_item_data
1136 || variable_b
1137 || l_selected_fields(i).variable_name
1138 || '">'
1139 || l_parent_lot_number
1140 || variable_e;
1141
1142 ELSIF LOWER(l_selected_fields(i).column_name) = 'print_count' THEN
1143 l_content_item_data := l_content_item_data
1144 || variable_b
1145 || l_selected_fields(i).variable_name
1146 || '">'
1147 || l_print_count
1148 || variable_e;
1149
1150 ELSIF LOWER(l_selected_fields(i).column_name) = 'reprint' THEN
1151 l_content_item_data := l_content_item_data
1152 || variable_b
1153 || l_selected_fields(i).variable_name
1154 || '">'
1155 || l_reprint
1156 || variable_e;
1157
1158 END IF;
1159 END LOOP; -- l_selected_fields.COUNT
1160
1161 l_content_item_data := l_content_item_data || label_e;
1162 x_variable_content(l_label_index).label_content := l_content_item_data;
1163 x_variable_content(l_label_index).label_request_id := l_label_request_id;
1164 x_variable_content(l_label_index).label_status := l_label_status;
1165 x_variable_content(l_label_index).error_message := l_label_err_msg;
1166
1167
1168 ------------------------Start of changes for Custom Labels project code------------------
1169
1170 -- Fix for bug: 4179593 Start
1171 IF (l_CustSqlWarnFlagSet) THEN
1172 l_custom_sql_ret_status := INV_LABEL.G_WARNING;
1173 l_custom_sql_ret_msg := l_CustSqlWarnMsg;
1174 END IF;
1175
1176 IF (l_CustSqlErrFlagSet) THEN
1177 l_custom_sql_ret_status := FND_API.G_RET_STS_ERROR;
1178 l_custom_sql_ret_msg := l_CustSqlErrMsg;
1179 END IF;
1180 -- Fix for bug: 4179593 End
1181
1182 -- We will concatenate the error message from Custom SQL and EPC code.
1183 x_variable_content(l_label_index).error_message := l_custom_sql_ret_msg || ' ' || l_label_err_msg;
1184 IF(l_CustSqlWarnFlagSet OR l_CustSqlErrFlagSet) THEN
1185 x_variable_content(l_label_index).label_status := l_custom_sql_ret_status;
1186 END IF;
1187 ------------------------End of this changes for Custom Labels project code---------------
1188
1189
1190 l_label_index := l_label_index + 1;
1191 l_content_item_data := '';
1192 l_label_request_id := NULL;
1193
1194 ------------------------Start of changes for Custom Labels project code------------------
1195 l_custom_sql_ret_status := NULL;
1196 l_custom_sql_ret_msg := NULL;
1197 ------------------------End of this changes for Custom Labels project code---------------
1198 IF (l_debug = 1) THEN
1199 TRACE(l_column_name_list);
1200 TRACE(' Finished writing item variables ');
1201 END IF;
1202 IF (l_debug = 1) THEN
1203 TRACE('x_variable_content.count ' || x_variable_content.count);
1204 end if;
1205 END IF;
1206
1207 END get_variable_data;
1208
1209 PROCEDURE get_variable_data(
1210 x_variable_content OUT NOCOPY LONG
1211 , x_msg_count OUT NOCOPY NUMBER
1212 , x_msg_data OUT NOCOPY VARCHAR2
1213 , x_return_status OUT NOCOPY VARCHAR2
1214 , p_label_type_info IN inv_label.label_type_rec
1215 , p_transaction_id IN NUMBER
1216 , p_input_param IN mtl_material_transactions_temp%ROWTYPE
1217 , p_transaction_identifier IN NUMBER
1218 ) IS
1219 l_variable_data_tbl inv_label.label_tbl_type;
1220 BEGIN
1221 get_variable_data(
1222 x_variable_content => l_variable_data_tbl
1223 , x_msg_count => x_msg_count
1224 , x_msg_data => x_msg_data
1225 , x_return_status => x_return_status
1226 , p_label_type_info => p_label_type_info
1227 , p_transaction_id => p_transaction_id
1228 , p_input_param => p_input_param
1229 , p_transaction_identifier => p_transaction_identifier
1230 );
1231 x_variable_content := '';
1232
1233 FOR i IN 1 .. l_variable_data_tbl.COUNT() LOOP
1234 x_variable_content :=
1235 x_variable_content || l_variable_data_tbl(i).label_content;
1236 END LOOP;
1237
1238 END get_variable_data;
1239 END inv_label_pvt11;