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