[Home] [Help]
PACKAGE BODY: APPS.INV_LABEL_PVT15
Source
1 PACKAGE BODY inv_label_pvt15 AS
2 /* $Header: INVLA15B.pls 120.2 2006/05/08 22:46:36 rahugupt noship $ */
3 label_b CONSTANT VARCHAR2(50) := '<label';
4 label_e CONSTANT VARCHAR2(50) := '</label>' || fnd_global.local_chr(10);
5 variable_b CONSTANT VARCHAR2(50) := '<variable name= "';
6 variable_e CONSTANT VARCHAR2(50) := '</variable>' || fnd_global.local_chr(10);
7 tag_e CONSTANT VARCHAR2(50) := '>' || fnd_global.local_chr(10);
8 l_debug NUMBER;
9
10 PROCEDURE TRACE(p_message VARCHAR2) IS
11 BEGIN
12 inv_label.TRACE(p_message, 'INV_LABEL_15');
13 END TRACE;
14
15 PROCEDURE get_variable_data(
16 x_variable_content OUT NOCOPY inv_label.label_tbl_type
17 , x_msg_count OUT NOCOPY NUMBER
18 , x_msg_data OUT NOCOPY VARCHAR2
19 , x_return_status OUT NOCOPY VARCHAR2
20 , p_label_type_info IN inv_label.label_type_rec
21 , p_transaction_id IN NUMBER
22 , p_input_param IN mtl_material_transactions_temp%ROWTYPE
23 , p_transaction_identifier IN NUMBER
24 ) IS
25
26
27 -- l_lpn_id NUMBER;
28 l_content_lpn_id NUMBER;
29 l_content_item_data LONG;
30 l_selected_fields inv_label.label_field_variable_tbl_type;
31 l_selected_fields_count NUMBER;
32 l_content_rec_index NUMBER := 0;
33 l_label_format_id NUMBER := 0;
34 l_label_format VARCHAR2(100);
35 l_printer VARCHAR2(30);
36 l_printer_sub VARCHAR2(30) := NULL;
37 l_api_name VARCHAR2(20) := 'get_variable_data';
38 l_return_status VARCHAR2(240);
39 l_error_message VARCHAR2(240);
40 l_msg_count NUMBER;
41 l_api_status VARCHAR2(240);
42 l_msg_data VARCHAR2(240);
43
44 i NUMBER;
45 j NUMBER;
46 k NUMBER;
47
48 l_transaction_identifier NUMBER := 0;
49 l_label_index NUMBER;
50 l_label_request_id NUMBER;
51 --I cleanup, use l_prev_format_id to record the previous label format
52 l_prev_format_id NUMBER;
53 -- I cleanup, user l_prev_sub to record the previous subinventory
54 --so that get_printer is not called if the subinventory is the same
55 l_prev_sub VARCHAR2(30);
56 -- a list of columns that are selected for format
57 l_column_name_list LONG;
58 -- l_patch_level NUMBER;
59
60
61 l_label_status VARCHAR2(1);
62 l_label_err_msg VARCHAR2(1000);
63 l_is_epc_exist VARCHAR2(1) := 'N';
64
65 l_revision mtl_material_transactions.revision%TYPE;
66 l_organization mtl_parameters.organization_code%TYPE;
67 l_organization_id NUMBER;
68
69 l_item mtl_system_items_vl.concatenated_segments%TYPE;
70 l_item_id NUMBER;
71 l_locator_id NUMBER;
72
73 l_sample_id gmd_samples.sample_id%TYPE;
74 l_orgn_code mtl_parameters.organization_code%TYPE;
75 l_sample_no gmd_samples.sample_no%TYPE;
76 l_sample_desc gmd_samples.sample_desc%TYPE;
77 l_sample_qty gmd_samples.sample_qty%TYPE;
78 l_sample_uom gmd_samples.sample_qty_uom%TYPE;
79 l_retain_as gmd_samples.retain_as%TYPE;
80 l_priority gmd_samples.priority%TYPE;
81 l_source varchar2(100);
82 l_subinventory gmd_samples.subinventory%TYPE;
83 l_lot_expiry varchar2(100);
84 l_lot_manufacturing_dat varchar2(100);
85 l_sample_expirt varchar2(100);
86 l_lot_number gmd_samples.lot_number%TYPE;
87 l_parent_lot_number gmd_samples.parent_lot_number%TYPE;
88 l_qc_lab_orgn_code gmd_samples.qc_lab_orgn_code%TYPE;
89 l_sample_instant gmd_samples.instance_id%TYPE;
90
91 l_batch_no gme_batch_header.batch_no%TYPE;
92 l_formula_no fm_form_mst_b.formula_no%TYPE;
93 l_oprn_no gmd_operations.oprn_no%TYPE;
94 l_recipe_no gmd_recipes_b.recipe_no%TYPE;
95 l_routing_no gmd_routings_b.routing_no%TYPE;
96
97 l_item_description mtl_system_items_vl.description%type;
98 l_sample_disposition varchar2(100);
99 l_date_drawn varchar2(100);
100 l_sample_instance gmd_samples.sample_instance%type;
101 l_print_count NUMBER;
102 l_reprint VARCHAR2(10);
103
104 ---------------------------------------------------------------------------------------------
105 -- Project: 'Custom Labels' (A 11i10+ Project) |
106 ---------------------------------------------------------------------------------------------
107 l_sql_stmt VARCHAR2(4000);
108 l_sql_stmt_result VARCHAR2(4000) := NULL;
109 TYPE sql_stmt IS REF CURSOR;
110 c_sql_stmt sql_stmt;
111 l_custom_sql_ret_status VARCHAR2(1);
112 l_custom_sql_ret_msg VARCHAR2(2000);
113
114 -- Fix for bug: 4179593 Start
115 l_CustSqlWarnFlagSet BOOLEAN;
116 l_CustSqlErrFlagSet BOOLEAN;
117 l_CustSqlWarnMsg VARCHAR2(2000);
118 l_CustSqlErrMsg VARCHAR2(2000);
119 -- Fix for bug: 4179593 End
120
121 ------------------------End of this change for Custom Labels project code--------------------
122
123
124 /* main driving cursor */
125
126 CURSOR c_sample_details IS
127 SELECT
128 para.ORGANIZATION_CODE,
129 msi.Concatenated_segments,
130 gsmp.sample_id,
131 para.organization_code,
132 gsmp.sample_no,
133 gsmp.sample_desc,
134 gsmp.inventory_item_id,
135 gsmp.sample_qty,
136 gsmp.sample_qty_uom,
137 gsmp.retain_as,
138 gsmp.priority,
139 (select meaning from gem_lookups where lookup_type='GMD_QC_SOURCE' and lookup_code=gsmp.source) source,
140 gsmp.subinventory,
141 gsmp.parent_lot_number,
142 gsmp.lot_number,
143 FND_DATE.DATE_TO_DISPLAYDT(mln.expiration_date, FND_TIMEZONES.GET_SERVER_TIMEZONE_CODE) lot_expiry,
144 FND_DATE.DATE_TO_DISPLAYDT(mln.creation_date, FND_TIMEZONES.GET_SERVER_TIMEZONE_CODE) lot_manufacturing_date,
145 FND_DATE.DATE_TO_DISPLAYDT(gsmp.expiration_date, FND_TIMEZONES.GET_SERVER_TIMEZONE_CODE) sample_expirt,
146 para2.organization_code qc_lab_orgn_code,
147 gsmp.instance_id sample_instant,
148 batchheader.batch_no,
149 formulamst.formula_no,
150 operations.oprn_no,
151 recipes.recipe_no,
152 routings.routing_no ,
153 gsmp.organization_id,
154 msi.description item_description,
155 (select lkups.meaning from gem_lookups lkups, gmd_sample_spec_disp disp where disp.sample_id=gsmp.sample_id and lookup_type='GMD_QC_SAMPLE_DISP' and lookup_code=disp.disposition) sample_disposition,
156 FND_DATE.DATE_TO_DISPLAYDT(gsmp.date_drawn, FND_TIMEZONES.GET_SERVER_TIMEZONE_CODE) date_drawn,
157 gsmp.sample_instance
158
159 from gmd_samples gsmp, mtl_lot_numbers mln,
160 fm_form_mst_b formulamst , gme_batch_header batchheader ,
161 gmd_operations operations , gmd_recipes_b recipes ,
162 gmd_routings_b routings , mtl_parameters para,
163 mtl_system_items_vl msi, mtl_parameters para2
164 where
165 gsmp.sample_id = p_transaction_id AND
166 gsmp.inventory_item_id = mln.inventory_item_id(+) and
167 gsmp.organization_id = mln.organization_id (+)and
168 gsmp.organization_id = para.organization_id AND
169 gsmp.lot_number = mln.lot_number(+) and
170 gsmp.inventory_item_id = msi.inventory_item_id AND
171 gsmp.organization_id = msi.organization_id AND
172 gsmp.lab_organization_id = para2.organization_id AND
173 gsmp.batch_id = batchheader.batch_id(+) and
174 gsmp.formula_id = formulamst.formula_id(+) and
175 gsmp.oprn_id = operations.oprn_id(+) and
176 gsmp.recipe_id = recipes.recipe_id(+) and
177 gsmp.routing_id = routings.routing_id(+);
178
179
180 BEGIN
181 -- Initialize return status as success
182 x_return_status := fnd_api.g_ret_sts_success;
183 l_debug := inv_label.l_debug;
184 l_label_status := INV_LABEL.G_SUCCESS;
185
186 IF (l_debug = 1) THEN
187 TRACE('***** In PVT15: ***********');
188 TRACE(
189 ' Business_flow= '
190 || p_label_type_info.business_flow_code
191 || ', Transaction ID= '
192 || p_transaction_id
193 || ', Transaction Identifier= '
194 || p_transaction_identifier
195 || ', Label Type ID= '
196 || p_label_type_info.label_type_id
197 );
198 END IF;
199
200 inv_label.get_variables_for_format(
201 x_variables => l_selected_fields
202 , x_variables_count => l_selected_fields_count
203 , p_format_id => p_label_type_info.default_format_id
204 );
205
206 IF (l_selected_fields_count = 0)
207 OR (l_selected_fields.COUNT = 0) THEN
208 IF (l_debug = 1) THEN
209 TRACE(
210 'no fields defined for this format: '
211 || p_label_type_info.default_format_id
212 || ','
213 || p_label_type_info.default_format_name
214 );
215 END IF;
216 --return;
217 END IF;
218
219 IF (l_debug = 1) THEN
220 TRACE(
221 ' Found variable defined for this format, cont = '
222 || l_selected_fields_count
223 );
224 TRACE(' Getting Sample data...');
225 END IF;
226
227
228 IF p_transaction_id IS NOT NULL
229 THEN
230 -- txn driven
231 i := 1;
232 l_content_rec_index := 0;
233 l_content_item_data := '';
234
235 l_printer := p_label_type_info.default_printer;
236 l_label_index := 1;
237 l_prev_format_id := p_label_type_info.default_format_id;
238 l_prev_sub := '####';
239
240
241 IF (l_debug = 1) THEN
242 TRACE('** in PVT15.get_variable_data ** , start ' || l_label_index );
243 END IF;
244
245 OPEN c_sample_details ;
246
247 IF c_sample_details%NOTFOUND
248 THEN
249 IF (l_debug = 1) THEN
250 TRACE('No data found .. Sample details' );
251 close c_sample_details ;
252 Return;
253 END IF;
254 ELSE
255 IF (l_debug = 1) THEN
256 TRACE('Found data for Sample details ' );
257 END IF;
258 END IF;
259
260
261 FETCH c_sample_details into
262 l_organization ,
263 l_item,
264 l_sample_id,
265 l_orgn_code,
266 l_sample_no,
267 l_sample_desc,
268 l_item_id,
269 l_sample_qty,
270 l_sample_uom,
271 l_retain_as,
272 l_priority,
273 l_source,
274 l_subinventory,
275 l_parent_lot_number,
276 l_lot_number,
277 l_lot_expiry,
278 l_lot_manufacturing_dat,
279 l_sample_expirt,
280 l_qc_lab_orgn_code,
281 l_sample_instant,
282 l_batch_no,
283 l_formula_no,
284 l_oprn_no,
285 l_recipe_no,
286 l_routing_no,
287 l_organization_id,
288 l_item_description,
289 l_sample_disposition,
290 l_date_drawn,
291 l_sample_instance;
292
293
294 IF (l_debug = 1) THEN
295 TRACE(
296 'Organization= '
297 || l_organization
298 || ' ,Sample No= '
299 || l_sample_no
300 || ' ,Item= '
301 || l_item
302 );
303 END IF;
304
305 GMO_LABEL_MGMT_GRP.GET_PRINT_COUNT(
306 p_api_version => 1.0,
307 p_init_msg_list => FND_API.G_FALSE,
308 x_return_status => l_return_status,
309 x_msg_count => l_msg_count,
310 x_msg_data => l_msg_data,
311 P_WMS_BUSINESS_FLOW_CODE => p_label_type_info.business_flow_code,
312 P_LABEL_TYPE => p_label_type_info.label_type_id,
313 P_TRANSACTION_ID => p_transaction_id ,
314 P_TRANSACTION_TYPE => p_transaction_identifier,
315 x_print_count => l_print_count
316 );
317
318 if (l_print_count > 0) then
319 l_reprint := fnd_message.get_string('GMO', 'GMO_UTIL_YES');
320 else
321 l_reprint := fnd_message.get_string('GMO', 'GMO_UTIL_NO');
322 end if;
323
324 l_content_item_data := '';
325
326 IF (l_debug = 1) THEN
327 TRACE(
328 'Apply Rules engine for format, printer='
329 || l_printer
330 || ',manual_format_id= '
331 || p_label_type_info.manual_format_id
332 || ',manual_format_name= '
333 || p_label_type_info.manual_format_name
334 );
335 END IF;
336
337 -- insert a record into wms_label_requests entity to
338 -- call the label rules engine to get appropriate label
339
340 inv_label.get_format_with_rule(
341 p_document_id => p_label_type_info.label_type_id
342 , p_label_format_id => p_label_type_info.manual_format_id
343 , p_organization_id => l_organization_id
344 , p_inventory_item_id => l_item_id
345 , p_subinventory_code => l_subinventory
346 , p_locator_id => l_locator_id
347 , p_lpn_id => NULL
348 , p_lot_number => l_lot_number
349 , p_revision => l_revision
350 , p_serial_number => NULL
351 , p_business_flow_code => p_label_type_info.business_flow_code
352 , p_last_update_date => SYSDATE
353 , p_last_updated_by => fnd_global.user_id
354 , p_creation_date => SYSDATE
355 , p_created_by => fnd_global.user_id
356 , p_printer_name => l_printer
357 , x_return_status => l_return_status
358 , x_label_format_id => l_label_format_id
359 , x_label_format => l_label_format
360 , x_label_request_id => l_label_request_id
361 );
362
363 IF l_return_status <> 'S' THEN
364 fnd_message.set_name('WMS', 'WMS_LABL_RULE_ENGINE_FAILED');
365 fnd_msg_pub.ADD;
366 l_label_format := p_label_type_info.default_format_id;
367 l_label_format_id := p_label_type_info.default_format_name;
368 l_label_status := INV_LABEL.G_ERROR;
369 END IF;
370
371 IF (l_label_format_id IS NOT NULL) THEN
372 -- Derive the fields for the format either passed in or derived via the rules engine.
373 IF l_label_format_id <> NVL(l_prev_format_id, -999) THEN
374 IF (l_debug = 1) THEN
375 TRACE(' Getting variables for new format '|| l_label_format);
376 END IF;
377
378 inv_label.get_variables_for_format(
379 x_variables => l_selected_fields
380 , x_variables_count => l_selected_fields_count
381 , p_format_id => l_label_format_id
382 );
383 l_prev_format_id := l_label_format_id;
384
385 IF (l_selected_fields_count = 0)
386 OR (l_selected_fields.COUNT = 0) THEN
387 IF (l_debug = 1) THEN
388 TRACE(
389 'no fields defined for this format: '
390 || l_label_format
391 || ','
392 || l_label_format_id
393 );
394 END IF;
395
396 END IF;
397
398 IF (l_debug = 1) THEN
399 TRACE(
400 ' Found selected_fields for format '
401 || l_label_format
402 || ', num='
403 || l_selected_fields_count
404 );
405 END IF;
406 END IF;
407 ELSE
408 IF (l_debug = 1) THEN
409 TRACE('No format exists for this label');
410 END IF;
411 END IF;
412
413 -- variable header
414
415 l_content_item_data := l_content_item_data || label_b;
416
417 IF l_label_format <> NVL(p_label_type_info.default_format_name, '@@@') THEN
418 l_content_item_data := l_content_item_data
419 || ' _FORMAT="'
420 || NVL(
421 p_label_type_info.manual_format_name
422 , l_label_format
423 )
424 || '"';
425 END IF;
426
427 IF (l_printer IS NOT NULL)
428 AND (l_printer <> NVL(p_label_type_info.default_printer, '###')) THEN
429 l_content_item_data := l_content_item_data || ' _PRINTERNAME="' || l_printer || '"';
430 END IF;
431
432 l_content_item_data := l_content_item_data || tag_e;
433
434 IF (l_debug = 1) THEN
435 TRACE('Starting assign variables, ');
436 END IF;
437
438 l_column_name_list := 'Set variables for ';
439
440 -- CUSTOM SQL
441 l_custom_sql_ret_status := FND_API.G_RET_STS_SUCCESS;
442 l_CustSqlWarnFlagSet := FALSE;
443 l_CustSqlErrFlagSet := FALSE;
444 l_CustSqlWarnMsg := NULL;
445 l_CustSqlErrMsg := NULL;
446 -- CUSTOM SQL
447
448
449 -- Loop for each selected fields, find the columns and write into the XML_content
450
451 FOR i IN 1 .. l_selected_fields.COUNT LOOP
452
453 IF (l_debug = 1) THEN
454 l_column_name_list := l_column_name_list || ',' || l_selected_fields(i).column_name;
455 END IF;
456 ---------------------------------------------------------------------------------------------
457 -- Project: 'Custom Labels' (A 11i10+ Project) |
458 -- Author: Dinesh ([email protected]) |
459 -- Change Description: |
460 -- The check (SQL_STMT <> NULL and COLUMN_NAME = NULL) implies that the field is a |
461 -- Custom SQL based field. Handle it appropriately. |
462 ---------------------------------------------------------------------------------------------
463 IF (l_selected_fields(i).SQL_STMT IS NOT NULL AND l_selected_fields(i).column_name = 'sql_stmt') THEN
464 IF (l_debug = 1) THEN
465 trace('Custom Labels Trace [INVLA15B.pls]: ------------------------- REPORT BEGIN-------------------------------------');
466 trace('Custom Labels Trace [INVLA15B.pls]: LABEL_FIELD_ID : ' || l_selected_fields(i).label_field_id);
467 trace('Custom Labels Trace [INVLA15B.pls]: FIELD_VARIABLE_NAME : ' || l_selected_fields(i).variable_name);
468 trace('Custom Labels Trace [INVLA15B.pls]: COLUMN_NAME : ' || l_selected_fields(i).column_name);
469 trace('Custom Labels Trace [INVLA15B.pls]: SQL_STMT : ' || l_selected_fields(i).sql_stmt);
470 END IF;
471 l_sql_stmt := l_selected_fields(i).sql_stmt;
472 IF (l_debug = 1) THEN
473 trace('Custom Labels Trace [INVLA15B.pls]: l_sql_stmt BEFORE REQUEST_ID Filter Concatenation: ' || l_sql_stmt);
474 END IF;
475 l_sql_stmt := l_sql_stmt || ' AND WLR.LABEL_REQUEST_ID = :REQUEST_ID';
476 IF (l_debug = 1) THEN
477 trace('Custom Labels Trace [INVLA15B.pls]: l_sql_stmt AFTER REQUEST_ID Filter Concatenation: ' || l_sql_stmt);
478
479 END IF;
480 BEGIN
481 IF (l_debug = 1) THEN
482 trace('Custom Labels Trace [INVLA15B.pls]: At Breadcrumb 1');
483 trace('Custom Labels Trace [INVLA15B.pls]: LABEL_REQUEST_ID : ' || l_label_request_id);
484 END IF;
485 OPEN c_sql_stmt FOR l_sql_stmt using l_label_request_id;
486 LOOP
487 FETCH c_sql_stmt INTO l_sql_stmt_result;
488 EXIT WHEN c_sql_stmt%notfound OR c_sql_stmt%rowcount >=2;
489 END LOOP;
490
491 IF (c_sql_stmt%rowcount=1 AND l_sql_stmt_result IS NULL) THEN
492 x_return_status := FND_API.G_RET_STS_SUCCESS;
493 l_custom_sql_ret_status := INV_LABEL.G_WARNING;
494 fnd_message.set_name('WMS','WMS_CS_NULL_VALUE_RETURNED');
495 fnd_msg_pub.ADD;
496 -- Fix for bug: 4179593 Start
497 --fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => l_custom_sql_ret_msg);
498 l_custom_sql_ret_msg := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_last, p_encoded => fnd_api.g_false);
499 l_CustSqlWarnMsg := l_custom_sql_ret_msg;
500 l_CustSqlWarnFlagSet := TRUE;
501 -- Fix for bug: 4179593 End
502 IF (l_debug = 1) THEN
503 trace('Custom Labels Trace [INVLA15B.pls]: At Breadcrumb 2');
504 trace('Custom Labels Trace [INVLA15B.pls]: l_sql_stmt_result is: ' || l_sql_stmt_result);
505 trace('Custom Labels Trace [INVLA15B.pls]: WARNING: NULL value returned.');
506 trace('Custom Labels Trace [INVLA15B.pls]: l_custom_sql_ret_status is set to : ' || l_custom_sql_ret_status);
507 END IF;
508 ELSIF c_sql_stmt%rowcount=0 THEN
509 IF (l_debug = 1) THEN
510 trace('Custom Labels Trace [INVLA15B.pls]: At Breadcrumb 3');
511 trace('Custom Labels Trace [INVLA15B.pls]: WARNING: No row returned by the Custom SQL query');
512 END IF;
513 x_return_status := FND_API.G_RET_STS_SUCCESS;
514 l_custom_sql_ret_status := INV_LABEL.G_WARNING;
515 fnd_message.set_name('WMS','WMS_CS_NO_DATA_FOUND');
516 fnd_msg_pub.ADD;
517 -- Fix for bug: 4179593 Start
518 --fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => l_custom_sql_ret_msg);
519 l_custom_sql_ret_msg := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_last, p_encoded => fnd_api.g_false);
520 l_CustSqlWarnMsg := l_custom_sql_ret_msg;
521 l_CustSqlWarnFlagSet := TRUE;
522 -- Fix for bug: 4179593 End
523 ELSIF c_sql_stmt%rowcount>=2 THEN
524 IF (l_debug = 1) THEN
525 trace('Custom Labels Trace [INVLA15B.pls]: At Breadcrumb 4');
526 trace('Custom Labels Trace [INVLA15B.pls]: ERROR: Multiple values returned by the Custom SQL query');
527 END IF;
528 l_sql_stmt_result := NULL;
529 x_return_status := FND_API.G_RET_STS_SUCCESS;
530 l_custom_sql_ret_status := FND_API.G_RET_STS_ERROR;
531 fnd_message.set_name('WMS','WMS_CS_MULTIPLE_VALUES_RETURN');
532 fnd_msg_pub.ADD;
533 -- Fix for bug: 4179593 Start
534 --fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => l_custom_sql_ret_msg);
535 l_custom_sql_ret_msg := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_last, p_encoded => fnd_api.g_false);
536 l_CustSqlErrMsg := l_custom_sql_ret_msg;
537 l_CustSqlErrFlagSet := TRUE;
538 -- Fix for bug: 4179593 End
539 END IF;
540 IF (c_sql_stmt%ISOPEN) THEN
541 CLOSE c_sql_stmt;
542 END IF;
543 EXCEPTION
544 WHEN OTHERS THEN
545 IF (c_sql_stmt%ISOPEN) THEN
546 CLOSE c_sql_stmt;
547 END IF;
548 IF (l_debug = 1) THEN
549 trace('Custom Labels Trace [INVLA15B.pls]: At Breadcrumb 5');
550 trace('Custom Labels Trace [INVLA15B.pls]: Unexpected Error has occured in GET_VARIABLES_DATA');
551 END IF;
552 x_return_status := FND_API.G_RET_STS_ERROR;
553 fnd_message.set_name('WMS','WMS_CS_WRONG_SQL_CONSTRUCT');
554 fnd_msg_pub.ADD;
555 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
556 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
557 END;
558 IF (l_debug = 1) THEN
559 trace('Custom Labels Trace [INVLA15B.pls]: At Breadcrumb 6');
560 trace('Custom Labels Trace [INVLA15B.pls]: Before assigning it to l_content_item_data');
561 END IF;
562 l_content_item_data := l_content_item_data
563 || variable_b
564 || l_selected_fields(i).variable_name
565 || '">'
566 || l_sql_stmt_result
567 || variable_e;
568 l_sql_stmt_result := NULL;
569 l_sql_stmt := NULL;
570 IF (l_debug = 1) THEN
571 trace('Custom Labels Trace [INVLA15B.pls]: At Breadcrumb 7');
572 trace('Custom Labels Trace [INVLA15B.pls]: After assigning it to l_content_item_data');
573 trace('Custom Labels Trace [INVLAP3B.pls]: --------------------------REPORT END-------------------------------------');
574 END IF;
575 ------------------------End of this changes for Custom Labels project code--------------------
576 ELSIF LOWER(l_selected_fields(i).column_name) = 'batch_no' THEN
577 l_content_item_data := l_content_item_data
578 || variable_b
579 || l_selected_fields(i).variable_name
580 || '">'
581 || l_batch_no
582 || variable_e;
583 ELSIF LOWER(l_selected_fields(i).column_name) = 'sample_no' THEN
584 l_content_item_data := l_content_item_data
585 || variable_b
586 || l_selected_fields(i).variable_name
587 || '">'
588 || l_sample_no
589 || variable_e;
590 ELSIF LOWER(l_selected_fields(i).column_name) = 'sample_desc' THEN
591 l_content_item_data := l_content_item_data
592 || variable_b
593 || l_selected_fields(i).variable_name
594 || '">'
595 || l_sample_desc
596 || variable_e;
597 ELSIF LOWER(l_selected_fields(i).column_name) = 'orgn_code' THEN
598 l_content_item_data := l_content_item_data
599 || variable_b
600 || l_selected_fields(i).variable_name
601 || '">'
602 || l_organization
603 || variable_e;
604 ELSIF LOWER(l_selected_fields(i).column_name) = 'subinventory_code' THEN
605 l_content_item_data := l_content_item_data
606 || variable_b
607 || l_selected_fields(i).variable_name
608 || '">'
609 || l_subinventory
610 || variable_e;
611
612 ELSIF LOWER(l_selected_fields(i).column_name) = 'item' THEN
613 l_content_item_data := l_content_item_data
614 || variable_b
615 || l_selected_fields(i).variable_name
616 || '">'
617 || l_item
618 || variable_e;
619
620 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_number' THEN
621 l_content_item_data := l_content_item_data
622 || variable_b
623 || l_selected_fields(i).variable_name
624 || '">'
625 || l_lot_number
626 || variable_e;
627
628 ELSIF LOWER(l_selected_fields(i).column_name) = 'parent_lot_number' THEN
629 l_content_item_data := l_content_item_data
630 || variable_b
631 || l_selected_fields(i).variable_name
632 || '">'
633 || l_parent_lot_number
634 || variable_e;
635
636 ELSIF LOWER(l_selected_fields(i).column_name) = 'sample_qty' THEN
637 l_content_item_data := l_content_item_data
638 || variable_b
639 || l_selected_fields(i).variable_name
640 || '">'
641 || l_sample_qty
642 || variable_e;
643 ELSIF LOWER(l_selected_fields(i).column_name) = 'sample_uom' THEN
644 l_content_item_data := l_content_item_data
645 || variable_b
646 || l_selected_fields(i).variable_name
647 || '">'
648 || l_sample_uom
649 || variable_e;
650 ELSIF LOWER(l_selected_fields(i).column_name) = 'retain_as' THEN
651 l_content_item_data := l_content_item_data
652 || variable_b
653 || l_selected_fields(i).variable_name
654 || '">'
655 || l_retain_as
656 || variable_e;
657 ELSIF LOWER(l_selected_fields(i).column_name) = 'priority' THEN
658 l_content_item_data := l_content_item_data
659 || variable_b
660 || l_selected_fields(i).variable_name
661 || '">'
662 || l_priority
663 || variable_e;
664 ELSIF LOWER(l_selected_fields(i).column_name) = 'source' THEN
665 l_content_item_data := l_content_item_data
666 || variable_b
667 || l_selected_fields(i).variable_name
668 || '">'
669 || l_source
670 || variable_e;
671 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_expiry' THEN
672 l_content_item_data := l_content_item_data
673 || variable_b
674 || l_selected_fields(i).variable_name
675 || '">'
676 || l_lot_expiry
677 || variable_e;
678 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_manufacturing_date' THEN
679 l_content_item_data := l_content_item_data
680 || variable_b
681 || l_selected_fields(i).variable_name
682 || '">'
683 || l_lot_manufacturing_dat
684 || variable_e;
685 ELSIF LOWER(l_selected_fields(i).column_name) = 'sample_expiry' THEN
686 l_content_item_data := l_content_item_data
687 || variable_b
688 || l_selected_fields(i).variable_name
689 || '">'
690 || l_sample_expirt
691 || variable_e;
692 ELSIF LOWER(l_selected_fields(i).column_name) = 'qc_lab_orgn' THEN
693 l_content_item_data := l_content_item_data
694 || variable_b
695 || l_selected_fields(i).variable_name
696 || '">'
697 || l_qc_lab_orgn_code
698 || variable_e;
699 ELSIF LOWER(l_selected_fields(i).column_name) = 'sample_instant' THEN
700 l_content_item_data := l_content_item_data
701 || variable_b
702 || l_selected_fields(i).variable_name
703 || '">'
704 || l_sample_instant
705 || variable_e;
706 ELSIF LOWER(l_selected_fields(i).column_name) = 'formula_no' THEN
707 l_content_item_data := l_content_item_data
708 || variable_b
709 || l_selected_fields(i).variable_name
710 || '">'
711 || l_formula_no
712 || variable_e;
713 ELSIF LOWER(l_selected_fields(i).column_name) = 'oprn_no' THEN
714 l_content_item_data := l_content_item_data
715 || variable_b
716 || l_selected_fields(i).variable_name
717 || '">'
718 || l_oprn_no
719 || variable_e;
720 ELSIF LOWER(l_selected_fields(i).column_name) = 'recipe_no' THEN
721 l_content_item_data := l_content_item_data
722 || variable_b
723 || l_selected_fields(i).variable_name
724 || '">'
725 || l_recipe_no
726 || variable_e;
727 ELSIF LOWER(l_selected_fields(i).column_name) = 'routing_no' THEN
728 l_content_item_data := l_content_item_data
729 || variable_b
730 || l_selected_fields(i).variable_name
731 || '">'
732 || l_routing_no
733 || variable_e;
734
735 ELSIF LOWER(l_selected_fields(i).column_name) = 'sample_id' THEN
736 l_content_item_data := l_content_item_data
737 || variable_b
738 || l_selected_fields(i).variable_name
739 || '">'
740 || l_sample_id
741 || variable_e;
742
743 ELSIF LOWER(l_selected_fields(i).column_name) = 'item_desc' THEN
744 l_content_item_data := l_content_item_data
745 || variable_b
746 || l_selected_fields(i).variable_name
747 || '">'
748 || l_item_description
749 || variable_e;
750
751 ELSIF LOWER(l_selected_fields(i).column_name) = 'disposition' THEN
752 l_content_item_data := l_content_item_data
753 || variable_b
754 || l_selected_fields(i).variable_name
755 || '">'
756 || l_sample_disposition
757 || variable_e;
758
759 ELSIF LOWER(l_selected_fields(i).column_name) = 'date_drawn' THEN
760 l_content_item_data := l_content_item_data
761 || variable_b
762 || l_selected_fields(i).variable_name
763 || '">'
764 || l_date_drawn
765 || variable_e;
766
767 ELSIF LOWER(l_selected_fields(i).column_name) = 'sample_instance' THEN
768 l_content_item_data := l_content_item_data
769 || variable_b
770 || l_selected_fields(i).variable_name
771 || '">'
772 || l_sample_instance
773 || variable_e;
774
775 ELSIF LOWER(l_selected_fields(i).column_name) = 'print_count' THEN
776 l_content_item_data := l_content_item_data
777 || variable_b
778 || l_selected_fields(i).variable_name
779 || '">'
780 || l_print_count
781 || variable_e;
782
783 ELSIF LOWER(l_selected_fields(i).column_name) = 'reprint' THEN
784 l_content_item_data := l_content_item_data
785 || variable_b
786 || l_selected_fields(i).variable_name
787 || '">'
788 || l_reprint
789 || variable_e;
790
791
792 END IF;
793 END LOOP; -- l_selected_fields.COUNT
794
795 l_content_item_data := l_content_item_data || label_e;
796 x_variable_content(l_label_index).label_content := l_content_item_data;
797 x_variable_content(l_label_index).label_request_id := l_label_request_id;
798 x_variable_content(l_label_index).label_status := l_label_status;
799 x_variable_content(l_label_index).error_message := l_label_err_msg;
800
801
802 ------------------------Start of changes for Custom Labels project code------------------
803
804 -- Fix for bug: 4179593 Start
805 IF (l_CustSqlWarnFlagSet) THEN
806 l_custom_sql_ret_status := INV_LABEL.G_WARNING;
807 l_custom_sql_ret_msg := l_CustSqlWarnMsg;
808 END IF;
809
810 IF (l_CustSqlErrFlagSet) THEN
811 l_custom_sql_ret_status := FND_API.G_RET_STS_ERROR;
812 l_custom_sql_ret_msg := l_CustSqlErrMsg;
813 END IF;
814 -- Fix for bug: 4179593 End
815
816 -- We will concatenate the error message from Custom SQL and EPC code.
817 x_variable_content(l_label_index).error_message := l_custom_sql_ret_msg || ' ' || l_label_err_msg;
818 IF(l_CustSqlWarnFlagSet OR l_CustSqlErrFlagSet) THEN
819 x_variable_content(l_label_index).label_status := l_custom_sql_ret_status;
820 END IF;
821 ------------------------End of this changes for Custom Labels project code---------------
822
823
824 l_label_index := l_label_index + 1;
825 l_content_item_data := '';
826 l_label_request_id := NULL;
827
828 ------------------------Start of changes for Custom Labels project code------------------
829 l_custom_sql_ret_status := NULL;
830 l_custom_sql_ret_msg := NULL;
831 ------------------------End of this changes for Custom Labels project code---------------
832 IF (l_debug = 1) THEN
833 TRACE(l_column_name_list);
834 TRACE(' Finished writing item variables ');
835 END IF;
836
837 IF (l_debug = 1) THEN
838 TRACE('x_variable_content.count ' || x_variable_content.count);
839 end if;
840
841 close c_sample_details;
842 END IF;
843
844 END get_variable_data;
845
846 PROCEDURE get_variable_data(
847 x_variable_content OUT NOCOPY LONG
848 , x_msg_count OUT NOCOPY NUMBER
849 , x_msg_data OUT NOCOPY VARCHAR2
850 , x_return_status OUT NOCOPY VARCHAR2
851 , p_label_type_info IN inv_label.label_type_rec
852 , p_transaction_id IN NUMBER
853 , p_input_param IN mtl_material_transactions_temp%ROWTYPE
854 , p_transaction_identifier IN NUMBER
855 ) IS
856 l_variable_data_tbl inv_label.label_tbl_type;
857 BEGIN
858 get_variable_data(
859 x_variable_content => l_variable_data_tbl
860 , x_msg_count => x_msg_count
861 , x_msg_data => x_msg_data
862 , x_return_status => x_return_status
863 , p_label_type_info => p_label_type_info
864 , p_transaction_id => p_transaction_id
865 , p_input_param => p_input_param
866 , p_transaction_identifier => p_transaction_identifier
867 );
868 x_variable_content := '';
869
870 FOR i IN 1 .. l_variable_data_tbl.COUNT() LOOP
871 x_variable_content :=
872 x_variable_content || l_variable_data_tbl(i).label_content;
873 END LOOP;
874
875 END get_variable_data;
876 END inv_label_pvt15;