[Home] [Help]
PACKAGE BODY: APPS.INV_LABEL_PVT6
Source
1 PACKAGE BODY INV_LABEL_PVT6 AS
2 /* $Header: INVLAP6B.pls 120.3 2005/08/25 19:23:59 satkumar ship $ */
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 IN VARCHAR2) iS
12 BEGIN
13 INV_LABEL.trace(p_message, 'LABEL_LOCATION');
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_subinventory_code VARCHAR2(10);
29 l_locator_id NUMBER;
30 l_organization_id NUMBER;
31
32 ---------------------------------------------------------------------------------------------
33 -- Project: 'Custom Labels' (A 11i10+ Project) |
34 -- Author: Dinesh ([email protected]) |
35 -- Change Description: |
36 -- Following variables were added (as a part of 11i10+ 'Custom Labels' Project) |
37 -- to retrieve and hold the SQL Statement and it's result. |
38 ---------------------------------------------------------------------------------------------
39 l_sql_stmt VARCHAR2(4000);
40 l_sql_stmt_result VARCHAR2(4000) := NULL;
41 TYPE sql_stmt IS REF CURSOR;
42 c_sql_stmt sql_stmt;
43 l_custom_sql_ret_status VARCHAR2(1);
44 l_custom_sql_ret_msg VARCHAR2(2000);
45
46 -- Fix for bug: 4179593 Start
47 l_CustSqlWarnFlagSet BOOLEAN;
48 l_CustSqlErrFlagSet BOOLEAN;
49 l_CustSqlWarnMsg VARCHAR2(2000);
50 l_CustSqlErrMsg VARCHAR2(2000);
51 -- Fix for bug: 4179593 End
52
53 ------------------------End of this change for Custom Labels project code--------------------
54
55 CURSOR c_all_sub IS
56 SELECT secondary_inventory_name
57 FROM mtl_secondary_inventories
58 WHERE organization_id = l_organization_id;
59 CURSOR c_all_sub_loc IS
60 SELECT msi.secondary_inventory_name, milkfv.inventory_location_id
61 FROM mtl_secondary_inventories msi
62 ,mtl_item_locations milkfv
63 WHERE msi.organization_id = l_organization_id
64 AND msi.secondary_inventory_name = nvl(l_subinventory_code, msi.secondary_inventory_name)
65 AND milkfv.organization_id(+) = l_organization_id
66 AND milkfv.subinventory_code(+) = msi.secondary_inventory_name;
67
68 CURSOR c_location IS
69 SELECT
70 mp.organization_id organization_id
71 , mp.organization_code organization
72 , msi.secondary_inventory_name subinventory_code
73 , msi.description subinventory_description
74 , msi.status_id status_id
75 , mmsvl1.status_code subinventory_status
76 , msi.pick_uom_code subinventory_pick_uom
77 , msi.attribute_category subinv_attribute_category
78 , msi.attribute1 subinventory_attribute1
79 , msi.attribute2 subinventory_attribute2
80 , msi.attribute3 subinventory_attribute3
81 , msi.attribute4 subinventory_attribute4
82 , msi.attribute5 subinventory_attribute5
83 , msi.attribute6 subinventory_attribute6
84 , msi.attribute7 subinventory_attribute7
85 , msi.attribute8 subinventory_attribute8
86 , msi.attribute9 subinventory_attribute9
87 , msi.attribute10 subinventory_attribute10
88 , msi.attribute11 subinventory_attribute11
89 , msi.attribute11 subinventory_attribute12
90 , msi.attribute13 subinventory_attribute13
91 , msi.attribute14 subinventory_attribute14
92 , msi.attribute15 subinventory_attribute15
93 , msi.picking_order subinventory_pick_order
94 , msi.default_cost_group_id subinventory_def_cost_group_id
95 , ccg.cost_group subinventory_def_cost_group
96 , milkfv.inventory_location_id locator_id
97 , INV_PROJECT.GET_LOCSEGS(milkfv.inventory_location_id,milkfv.organization_id) locator
98 , milkfv.description locator_description
99 , milkfv.status_id locator_status_id
100 , mmsvl2.status_code locator_status
101 , milkfv.pick_uom_code locator_pick_uom
102 , milkfv.attribute_category locator_attribute_category
103 , milkfv.attribute1 locator_attribute1
104 , milkfv.attribute2 locator_attribute2
105 , milkfv.attribute3 locator_attribute3
106 , milkfv.attribute4 locator_attribute4
107 , milkfv.attribute5 locator_attribute5
108 , milkfv.attribute6 locator_attribute6
109 , milkfv.attribute7 locator_attribute7
110 , milkfv.attribute8 locator_attribute8
111 , milkfv.attribute9 locator_attribute9
112 , milkfv.attribute10 locator_attribute10
113 , milkfv.attribute11 locator_attribute11
114 , milkfv.attribute12 locator_attribute12
115 , milkfv.attribute13 locator_attribute13
116 , milkfv.attribute14 locator_attribute14
117 , milkfv.attribute15 locator_attribute15
118 , milkfv.project_id locator_project_id
119 , pap.name locator_project
120 , milkfv.task_id locator_task_id
121 , pat.task_name locator_task
122 , milkfv.picking_order locator_pick_order
123 , milkfv.max_weight locator_weight_capacity
124 , milkfv.location_weight_uom_code locator_weight_capacity_uom
125 , milkfv.max_cubic_area locator_volume_capacity
126 , milkfv.volume_uom_code locator_volume_capacity_uom
127 , milkfv.location_maximum_units locator_unit_capacity
128 , milkfv.alias locator_alias
129 FROM mtl_parameters mp
130 , mtl_secondary_inventories msi
131 , mtl_material_statuses_vl mmsvl1
132 , mtl_material_statuses_vl mmsvl2
133 , mtl_item_locations milkfv
134 , pa_tasks pat
135 , pa_projects_all pap
136 , cst_cost_groups ccg
137 WHERE mp.organization_id = l_organization_id
138 AND msi.secondary_inventory_name(+) = l_subinventory_code
139 AND msi.organization_id(+) = mp.organization_id
140 AND mmsvl1.status_id(+) = msi.status_id
141 AND mmsvl2.status_id(+) = milkfv.status_id
142 AND milkfv.inventory_location_id(+) = l_locator_id
143 AND milkfv.subinventory_code(+) = l_subinventory_code
144 AND milkfv.organization_id(+) = mp.organization_id
145 AND pap.project_id(+) = milkfv.project_id
146 AND pat.task_id(+) = milkfv.task_id
147 AND ccg.COST_GROUP_ID(+) = msi.DEFAULT_COST_GROUP_ID;
148
149
150 l_location_data LONG;
151
152 l_selected_fields INV_LABEL.label_field_variable_tbl_type;
153 l_selected_fields_count NUMBER;
154
155 l_content_rec_index NUMBER := 0;
156
157 l_label_format_id NUMBER := 0 ;
158 l_label_format VARCHAR2(100);
159 l_printer VARCHAR2(30);
160
161 l_api_name VARCHAR2(20) := 'get_variable_data';
162
163 l_return_status VARCHAR2(240);
164
165 l_error_message VARCHAR2(240);
166 l_msg_count NUMBER;
167 l_api_status VARCHAR2(240);
168 l_msg_data VARCHAR2(240);
169
170 l_print_all_mode NUMBER := 0;
171
172 i NUMBER;
173
174 l_label_index NUMBER;
175 l_label_request_id NUMBER;
176
177 --I cleanup, use l_prev_format_id to record the previous label format
178 l_prev_format_id NUMBER;
179
180 -- I cleanup, user l_prev_sub to record the previous subinventory
181 --so that get_printer is not called if the subinventory is the same
182 l_prev_sub VARCHAR2(30);
183
184 -- a list of columns that are selected for format
185 l_column_name_list LONG;
186 BEGIN
187 l_debug := INV_LABEL.l_debug;
188 IF (l_debug = 1) THEN
189 trace('**In PVT6: Location label**');
190 trace(' Business_flow: '||p_label_type_info.business_flow_code);
191 trace(' Transaction ID:'||p_transaction_id);
192 END IF;
193
194 -- Get Org, Sub, Loc
195 IF p_transaction_id IS NOT NULL THEN
196 l_organization_id := null;
197 l_subinventory_code := null;
198 l_locator_id :=null;
199 ELSE
200 -- On demand, get information from input_param
201 l_organization_id := p_input_param.organization_id;
202 l_subinventory_code := p_input_param.subinventory_code;
203 l_locator_id := p_input_param.locator_id;
204 END IF;
205
206 IF (l_debug = 1) THEN
207 trace(' Input Organization Id = '|| l_organization_id||
208 ',subinventory_code='|| l_subinventory_code||
209 ',Locator Id = '|| l_locator_id);
210 END IF;
211 IF (l_organization_id IS NULL) THEN
212 IF (l_debug = 1) THEN
213 trace(' Oranization IS NULL, cannot process ');
214 END IF;
215 RETURN;
216 END IF;
217
218 IF (l_debug = 1) THEN
219 trace('Getting the print all mode');
220 END IF;
221 -- If Sub or Loc is passed as NULL, it will print
222 -- all the sub or locator
223 -- If Sub or Loc is passed as -1, it will not print
224 -- all the sub or locator, it will just print
225 -- the org or sub.
226 -- l_print_all_mode =
227 -- 0: nothing to print
228 -- 1: print just the org
229 -- 2: print org and all the sub
230 -- 3: print just the org and sub
231 -- 4: print org, sub and all the locators
232 -- 5: print the given org, sub and locator
233 IF l_subinventory_code IS NULL THEN
234 -- Print just the Org
235 l_print_all_mode := 1;
236 ELSIF l_subinventory_code = '-1' THEN
237 -- Print all the sub
238 IF l_locator_id IS NULL THEN
239 -- just org and sub
240 l_print_all_mode := 2;
241 ELSE
242 l_print_all_mode := 4;
243 END IF;
244 ELSE
245 -- Giving a subinventory
246 IF l_locator_id IS NULL THEN
247 -- Print just org and sub
248 l_print_all_mode := 3;
249 ELSIF l_locator_id = -1 THEN
250 -- Finding all the locators for this sub
251 l_print_all_mode := 4;
252 ELSE
253 -- print given org, sub and loc
254 l_print_all_mode := 5;
255 END IF;
256 END IF;
257 IF (l_debug = 1) THEN
258 trace(' Got the l_print_all_mode = '|| l_print_all_mode);
259 --trace(' Getting selected fields ');
260 END IF;
261
262
263 IF (l_debug = 1) THEN
264 trace(' Getting org, sub, locator based on l_print_all_mode');
265 END IF;
266 IF l_print_all_mode = 0 THEN
267 -- Nothing to print
268 RETURN;
269 ELSIF l_print_all_mode = 1 THEN
270 -- set sub and loc as null
271 l_subinventory_code := null;
272 l_locator_id := null;
273 ELSIF l_print_all_mode = 2 THEN
274 -- print org and all the sub
275 l_subinventory_code := null;
276 OPEN c_all_sub;
277 FETCH c_all_sub INTO l_subinventory_code;
278 IF c_all_sub%NOTFOUND THEN
279 IF (l_debug = 1) THEN
280 trace(' No subinventory found for this org: '|| l_organization_id);
281 END IF;
282 l_subinventory_code := null;
283 l_locator_id := null;
284 CLOSE c_all_sub;
285 END IF;
286 ELSIF l_print_all_mode = 3 THEN
287 -- print org and just given sub
288 l_locator_id := null;
289 ELSIF l_print_all_mode = 4 THEN
290 -- Print org, all the locators for the given sub or all sub
291 IF l_subinventory_code = '-1' THEN
292 l_subinventory_code := null;
293 END IF;
294 l_locator_id := null;
295 OPEN c_all_sub_loc;
296 FETCH c_all_sub_loc INTO l_subinventory_code, l_locator_id;
297 IF c_all_sub_loc%NOTFOUND THEN
298 IF (l_debug = 1) THEN
299 trace(' No subinventory and locator found for this org: '|| l_organization_id || l_subinventory_code);
300 END IF;
301 --l_subinventory_code := null;
302 l_locator_id := null;
303 CLOSE c_all_sub_loc;
304 END IF;
305 ELSIF l_print_all_mode = 5 THEN
306 null;
307 END IF;
308
309 l_content_rec_index := 0;
310 l_location_data := '';
311 IF (l_debug = 1) THEN
312 trace('** in PVT6.get_variable_dataa ** , start ');
313 END IF;
314 l_printer := p_label_type_info.default_printer;
315
316 l_label_index := 1;
317 l_prev_format_id := -999; --p_label_type_info.default_format_id;--in R12
318 l_prev_sub := '####';
319
320 WHILE l_organization_id IS NOT NULL LOOP
321 l_content_rec_index := l_content_rec_index + 1;
322 IF (l_debug = 1) THEN
323 trace(' before FOR c_location org, sub, loc = '|| l_organization_id ||', '||l_subinventory_code || ', ' || l_locator_id);
324 END IF;
325 l_location_data := '';
326 FOR v_location IN c_location LOOP
327 IF (l_debug = 1) THEN
328 trace(' In Loop ' || l_content_rec_index ||' ^^^^^^^^New Label ^^^^^^^^^^^');
329 trace(' org, sub, loc = '|| l_organization_id ||', '||l_subinventory_code || ', ' || l_locator_id);
330 END IF;
331
332
333 --R12 : RFID compliance project
334 --Calling rules engine before calling to get printer
335
336 IF (l_debug = 1) THEN
337 trace('Apply Rules engine to get format '
338 ||',manual_format_id='||p_label_type_info.manual_format_id
339 ||',manual_format_name='||p_label_type_info.manual_format_name);
340 END IF;
341
342 /* insert a record into wms_label_requests entity to
343 call the label rules engine to get appropriate label */
344 INV_LABEL.GET_FORMAT_WITH_RULE
345 ( p_document_id =>p_label_type_info.label_type_id,
346 P_LABEL_FORMAT_ID => p_label_type_info.manual_format_id,
347 p_organization_id =>v_location.organization_id,
348 p_subinventory_code =>v_location.subinventory_code,
349 p_locator_id =>v_location.locator_id,
350 P_BUSINESS_FLOW_CODE => p_label_type_info.business_flow_code,
351 P_LAST_UPDATE_DATE =>sysdate,
352 P_LAST_UPDATED_BY =>FND_GLOBAL.user_id,
353 P_CREATION_DATE =>sysdate,
354 P_CREATED_BY =>FND_GLOBAL.user_id,
355 --P_PRINTER_NAME => l_printer,-- Removed in R12: 4396558
356 x_return_status =>l_return_status,
357 x_label_format_id =>l_label_format_id,
358 x_label_format =>l_label_format,
359 x_label_request_id =>l_label_request_id);
360
361 IF (l_debug = 1) THEN
362 trace('did apply label ' || l_label_format || ',' || l_label_format_id);
363 END IF;
364 IF l_return_status <> 'S' THEN
365 FND_MESSAGE.SET_NAME('WMS', 'WMS_LABL_RULE_ENGINE_FAILED');
366 FND_MSG_PUB.ADD;
367 l_label_format:= p_label_type_info.default_format_id;
368 l_label_format_id:= p_label_type_info.default_format_name;
369 END IF;
370 IF (l_debug = 1) THEN
371 trace('did apply label ' || l_label_format || ',' || l_label_format_id||',req_id '||l_label_request_id);
372
373 trace(' Getting printer, manual_printer='||p_label_type_info.manual_printer
374 ||',sub='||v_location.subinventory_code
375 ||',default printer='||p_label_type_info.default_printer);
376 END IF;
377
378 -- IF clause Added for Add format/printer for manual request
379 IF p_label_type_info.manual_printer IS NULL THEN
380 -- The p_label_type_info.manual_printer is the one passed from the manual page.
381 -- As per the design, if a printer is passed from the manual page, then we use that printer irrespective.
382
383 IF (v_location.subinventory_code IS NOT NULL) AND
384 (v_location.subinventory_code <> l_prev_sub) THEN
385 IF (l_debug = 1) THEN
386 trace('getting printer with sub '||v_location.subinventory_code);
387 END IF;
388 BEGIN
389 WSH_REPORT_PRINTERS_PVT.GET_PRINTER(
390 p_concurrent_program_id=>p_label_type_info.label_type_id,
391 p_user_id =>fnd_global.user_id,
392 p_responsibility_id =>fnd_global.resp_id,
393 p_application_id =>fnd_global.resp_appl_id,
394 p_organization_id =>v_location.organization_id,
395 p_zone =>v_location.subinventory_code,
396 p_format_id =>l_label_format_id, --added in r12 RFID
397 x_printer =>l_printer,
398 x_api_status =>l_api_status,
399 x_error_message =>l_error_message);
400 IF l_api_status <> 'S' THEN
401 IF (l_debug = 1) THEN
402 trace('Error in calling get_printer, set printer as default printer, err_msg:'||l_error_message);
403 END IF;
404 l_printer := p_label_type_info.default_printer;
405 END IF;
406
407 EXCEPTION
408 WHEN others THEN
409 l_printer := p_label_type_info.default_printer;
410 END;
411 l_prev_sub := v_location.subinventory_code;
412 END IF;
413 ELSE
414 IF (l_debug = 1) THEN
415 trace('Set printer as Manual Printer passed in:' || p_label_type_info.manual_printer );
416 END IF;
417 l_printer := p_label_type_info.manual_printer;
418 END IF;
419
420
421 IF p_label_type_info.manual_format_id IS NOT NULL THEN
422 l_label_format_id := p_label_type_info.manual_format_id;
423 l_label_format := p_label_type_info.manual_format_name;
424 IF (l_debug = 1) THEN
425 trace('Manual format passed in:'||l_label_format_id||','||l_label_format);
426 END IF;
427 END IF;
428 IF (l_label_format_id IS NOT NULL) THEN
429 -- Derive the fields for the format either passed in or derived via the rules engine.
430 IF l_label_format_id <> l_prev_format_id
431 THEN --l_prev_format_id initial value is -999
432 IF (l_debug = 1) THEN
433 trace(' Getting variables for new format ' || l_label_format);
434 END IF;
435 INV_LABEL.GET_VARIABLES_FOR_FORMAT(
436 x_variables => l_selected_fields
437 , x_variables_count => l_selected_fields_count
438 , p_format_id => l_label_format_id);
439
440 l_prev_format_id := l_label_format_id;
441
442 IF (l_selected_fields_count=0) OR (l_selected_fields.count =0 ) THEN
443 IF (l_debug = 1) THEN
444 trace('no fields defined for this format: ' || l_label_format|| ',' ||l_label_format_id);
445 END IF;
446 GOTO NextLabel;
447 END IF;
448 IF (l_debug = 1) THEN
449 trace(' Found selected_fields for format ' || l_label_format ||', num='|| l_selected_fields_count);
450 END IF;
451 END IF;
452 ELSE
453 IF (l_debug = 1) THEN
454 trace('No format exists for this label, goto nextlabel');
455 END IF;
456 GOTO NextLabel;
457 END IF;
458
459 /* variable header */
460 l_location_data := l_location_data || LABEL_B;
461 IF l_label_format <> nvl(p_label_type_info.default_format_name, '@@@') THEN
462 l_location_data := l_location_data || ' _FORMAT="' || nvl(p_label_type_info.manual_format_name, l_label_format) || '"';
463 END IF;
464 IF (l_printer IS NOT NULL) AND (l_printer <> nvl(p_label_type_info.default_printer,'###')) THEN
465 l_location_data := l_location_data || ' _PRINTERNAME="'||l_printer||'"';
466 END IF;
467
468 l_location_data := l_location_data || TAG_E;
469
470 IF (l_debug = 1) THEN
471 trace('Starting assign variables, ');
472 END IF;
473
474 l_column_name_list := 'Set variables for ';
475
476 /* Modified for Bug 4072474 -start*/
477 l_custom_sql_ret_status := FND_API.G_RET_STS_SUCCESS;
478 /* Modified for Bug 4072474 -End*/
479
480 -- Fix for bug: 4179593 Start
481 l_CustSqlWarnFlagSet := FALSE;
482 l_CustSqlErrFlagSet := FALSE;
483 l_CustSqlWarnMsg := NULL;
484 l_CustSqlErrMsg := NULL;
485 -- Fix for bug: 4179593 End
486
487 /* Loop for each selected fields, find the columns and write into the XML_content*/
488 FOR i IN 1..l_selected_fields.count LOOP
489 IF (l_debug = 1) THEN
490 l_column_name_list := l_column_name_list || ',' ||l_selected_fields(i).column_name;
491 END IF;
492
493 ---------------------------------------------------------------------------------------------
494 -- Project: 'Custom Labels' (A 11i10+ Project) |
495 -- Author: Dinesh ([email protected]) |
496 -- Change Description: |
497 -- The check (SQL_STMT <> NULL and COLUMN_NAME = NULL) implies that the field is a |
498 -- Custom SQL based field. Handle it appropriately. |
499 ---------------------------------------------------------------------------------------------
500 IF (l_selected_fields(i).SQL_STMT IS NOT NULL AND l_selected_fields(i).column_name = 'sql_stmt') THEN
501 IF (l_debug = 1) THEN
502 trace('Custom Labels Trace [INVLAP6B.pls]: ------------------------- REPORT BEGIN-------------------------------------');
503 trace('Custom Labels Trace [INVLAP6B.pls]: LABEL_FIELD_ID : ' || l_selected_fields(i).label_field_id);
504 trace('Custom Labels Trace [INVLAP6B.pls]: FIELD_VARIABLE_NAME : ' || l_selected_fields(i).variable_name);
505 trace('Custom Labels Trace [INVLAP6B.pls]: COLUMN_NAME : ' || l_selected_fields(i).column_name);
506 trace('Custom Labels Trace [INVLAP6B.pls]: SQL_STMT : ' || l_selected_fields(i).sql_stmt);
507 END IF;
508 l_sql_stmt := l_selected_fields(i).sql_stmt;
509 IF (l_debug = 1) THEN
510 trace('Custom Labels Trace [INVLAP6B.pls]: l_sql_stmt BEFORE REQUEST_ID Filter Concatenation: ' || l_sql_stmt);
511 END IF;
512 l_sql_stmt := l_sql_stmt || ' AND WLR.LABEL_REQUEST_ID = :REQUEST_ID';
513 IF (l_debug = 1) THEN
514 trace('Custom Labels Trace [INVLAP6B.pls]: l_sql_stmt AFTER REQUEST_ID Filter Concatenation: ' || l_sql_stmt);
515
516 END IF;
517 BEGIN
518 IF (l_debug = 1) THEN
519 trace('Custom Labels Trace [INVLAP6B.pls]: At Breadcrumb 1');
520 trace('Custom Labels Trace [INVLAP6B.pls]: LABEL_REQUEST_ID : ' || l_label_request_id);
521 END IF;
522 OPEN c_sql_stmt FOR l_sql_stmt using l_label_request_id;
523 LOOP
524 FETCH c_sql_stmt INTO l_sql_stmt_result;
525 EXIT WHEN c_sql_stmt%notfound OR c_sql_stmt%rowcount >=2;
526 END LOOP;
527
528 IF (c_sql_stmt%rowcount=1 AND l_sql_stmt_result IS NULL) THEN
529 x_return_status := FND_API.G_RET_STS_SUCCESS;
530 l_custom_sql_ret_status := INV_LABEL.G_WARNING;
531 fnd_message.set_name('WMS','WMS_CS_NULL_VALUE_RETURNED');
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_CustSqlWarnMsg := l_custom_sql_ret_msg;
537 l_CustSqlWarnFlagSet := TRUE;
538 -- Fix for bug: 4179593 End
539 IF (l_debug = 1) THEN
540 trace('Custom Labels Trace [INVLAP6B.pls]: At Breadcrumb 2');
541 trace('Custom Labels Trace [INVLAP6B.pls]: l_sql_stmt_result is: ' || l_sql_stmt_result);
542 trace('Custom Labels Trace [INVLAP6B.pls]: WARNING: NULL value returned by the custom SQL Query.');
543 trace('Custom Labels Trace [INVLAP6B.pls]: l_custom_sql_ret_status is set to : ' || l_custom_sql_ret_status );
544 END IF;
545 ELSIF c_sql_stmt%rowcount=0 THEN
546 IF (l_debug = 1) THEN
547 trace('Custom Labels Trace [INVLAP6B.pls]: At Breadcrumb 3');
548 trace('Custom Labels Trace [INVLAP6B.pls]: WARNING: No row returned by the Custom SQL query');
549 END IF;
550 x_return_status := FND_API.G_RET_STS_SUCCESS;
551 l_custom_sql_ret_status := INV_LABEL.G_WARNING;
552 fnd_message.set_name('WMS','WMS_CS_NO_DATA_FOUND');
553 fnd_msg_pub.ADD;
554 /* Replaced following statement for Bug 4207625: Anupam Jain*/
555 /*fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => l_custom_sql_ret_status);*/
556 -- Fix for bug: 4179593 Start
557 --fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => l_custom_sql_ret_msg);
558 l_custom_sql_ret_msg := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_last, p_encoded => fnd_api.g_false);
559 l_CustSqlWarnMsg := l_custom_sql_ret_msg;
560 l_CustSqlWarnFlagSet := TRUE;
561 -- Fix for bug: 4179593 End
562 ELSIF c_sql_stmt%rowcount>=2 THEN
563 IF (l_debug = 1) THEN
564 trace('Custom Labels Trace [INVLAP6B.pls]: At Breadcrumb 4');
565 trace('Custom Labels Trace [INVLAP6B.pls]: ERROR: Multiple values returned by the Custom SQL query');
566 END IF;
567 l_sql_stmt_result := NULL;
568 x_return_status := FND_API.G_RET_STS_SUCCESS;
569 l_custom_sql_ret_status := FND_API.G_RET_STS_ERROR;
570 fnd_message.set_name('WMS','WMS_CS_MULTIPLE_VALUES_RETURN');
571 fnd_msg_pub.ADD;
572 /* Replaced following statement for Bug 4207625: Anupam Jain*/
573 /*fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => l_custom_sql_ret_status);*/
574 -- Fix for bug: 4179593 Start
575 --fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => l_custom_sql_ret_msg);
576 l_custom_sql_ret_msg := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_last, p_encoded => fnd_api.g_false);
577 l_CustSqlErrMsg := l_custom_sql_ret_msg;
578 l_CustSqlErrFlagSet := TRUE;
579 -- Fix for bug: 4179593 End
580 END IF;
581 IF (c_sql_stmt%ISOPEN) THEN
582 CLOSE c_sql_stmt;
583 END IF;
584 EXCEPTION
585 WHEN OTHERS THEN
586 IF (c_sql_stmt%ISOPEN) THEN
587 CLOSE c_sql_stmt;
588 END IF;
589 IF (l_debug = 1) THEN
590 trace('Custom Labels Trace [INVLAP6B.pls]: At Breadcrumb 5');
591 trace('Custom Labels Trace [INVLAP6B.pls]: Unexpected Error has occured in GET_VARIABLES_DATA');
592 END IF;
593 x_return_status := FND_API.G_RET_STS_ERROR;
594 fnd_message.set_name('WMS','WMS_CS_WRONG_SQL_CONSTRUCT');
595 fnd_msg_pub.ADD;
596 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
597 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
598 END;
599 IF (l_debug = 1) THEN
600 trace('Custom Labels Trace [INVLAP6B.pls]: At Breadcrumb 6');
601 trace('Custom Labels Trace [INVLAP6B.pls]: Before assigning it to l_location_data');
602 END IF;
603 l_location_data := l_location_data
604 || variable_b
605 || l_selected_fields(i).variable_name
606 || '">'
607 || l_sql_stmt_result
608 || variable_e;
609 l_sql_stmt_result := NULL;
610 l_sql_stmt := NULL;
611 IF (l_debug = 1) THEN
612 trace('Custom Labels Trace [INVLAP6B.pls]: At Breadcrumb 7');
613 trace('Custom Labels Trace [INVLAP6B.pls]: After assigning it to l_location_data');
614 trace('Custom Labels Trace [INVLAP6B.pls]: --------------------------REPORT END-------------------------------------');
615 END IF;
616 ------------------------End of this change for Custom Labels project code--------------------
617 ELSIF LOWER(l_selected_fields(i).column_name) = 'current_date' THEN l_location_data := l_location_data || VARIABLE_B || l_selected_fields(i).variable_name || '">' || INV_LABEL.G_DATE || VARIABLE_E;
618 ELSIF LOWER(l_selected_fields(i).column_name) = 'current_time' THEN l_location_data := l_location_data || VARIABLE_B || l_selected_fields(i).variable_name || '">' || INV_LABEL.G_TIME || VARIABLE_E;
619 ELSIF LOWER(l_selected_fields(i).column_name) = 'request_user' THEN l_location_data := l_location_data || VARIABLE_B || l_selected_fields(i).variable_name || '">' || INV_LABEL.G_USER || VARIABLE_E;
620 ELSIF LOWER(l_selected_fields(i).column_name) = 'organization' THEN l_location_data := l_location_data || VARIABLE_B || l_selected_fields(i).variable_name || '">' || v_location.organization || VARIABLE_E;
621 ELSIF LOWER(l_selected_fields(i).column_name) = 'subinventory_code' THEN l_location_data := l_location_data || VARIABLE_B || l_selected_fields(i).variable_name || '">' || v_location.subinventory_code || VARIABLE_E;
622 ELSIF LOWER(l_selected_fields(i).column_name) = 'subinventory_description' THEN l_location_data := l_location_data || VARIABLE_B || l_selected_fields(i).variable_name || '">' || v_location.subinventory_description || VARIABLE_E;
623 ELSIF LOWER(l_selected_fields(i).column_name) = 'subinventory_status' THEN l_location_data := l_location_data || VARIABLE_B || l_selected_fields(i).variable_name || '">' || v_location.subinventory_status || VARIABLE_E;
624 ELSIF LOWER(l_selected_fields(i).column_name) = 'subinventory_pick_uom' THEN l_location_data := l_location_data || VARIABLE_B || l_selected_fields(i).variable_name || '">' || v_location.subinventory_pick_uom || VARIABLE_E;
625 ELSIF LOWER(l_selected_fields(i).column_name) = 'subinv_attribute_category' THEN l_location_data := l_location_data || VARIABLE_B || l_selected_fields(i).variable_name || '">' || v_location.subinv_attribute_category || VARIABLE_E;
626 ELSIF LOWER(l_selected_fields(i).column_name) = 'subinventory_attribute1' THEN l_location_data := l_location_data || VARIABLE_B || l_selected_fields(i).variable_name || '">' || v_location.subinventory_attribute1 || VARIABLE_E;
627 ELSIF LOWER(l_selected_fields(i).column_name) = 'subinventory_attribute2' THEN l_location_data := l_location_data || VARIABLE_B || l_selected_fields(i).variable_name || '">' || v_location.subinventory_attribute2 || VARIABLE_E;
628 ELSIF LOWER(l_selected_fields(i).column_name) = 'subinventory_attribute3' THEN l_location_data := l_location_data || VARIABLE_B || l_selected_fields(i).variable_name || '">' || v_location.subinventory_attribute3 || VARIABLE_E;
629 ELSIF LOWER(l_selected_fields(i).column_name) = 'subinventory_attribute4' THEN l_location_data := l_location_data || VARIABLE_B || l_selected_fields(i).variable_name || '">' || v_location.subinventory_attribute4 || VARIABLE_E;
630 ELSIF LOWER(l_selected_fields(i).column_name) = 'subinventory_attribute5' THEN l_location_data := l_location_data || VARIABLE_B || l_selected_fields(i).variable_name || '">' || v_location.subinventory_attribute5 || VARIABLE_E;
631 ELSIF LOWER(l_selected_fields(i).column_name) = 'subinventory_attribute6' THEN l_location_data := l_location_data || VARIABLE_B || l_selected_fields(i).variable_name || '">' || v_location.subinventory_attribute6 || VARIABLE_E;
632 ELSIF LOWER(l_selected_fields(i).column_name) = 'subinventory_attribute7' THEN l_location_data := l_location_data || VARIABLE_B || l_selected_fields(i).variable_name || '">' || v_location.subinventory_attribute7 || VARIABLE_E;
633 ELSIF LOWER(l_selected_fields(i).column_name) = 'subinventory_attribute8' THEN l_location_data := l_location_data || VARIABLE_B || l_selected_fields(i).variable_name || '">' || v_location.subinventory_attribute8 || VARIABLE_E;
634 ELSIF LOWER(l_selected_fields(i).column_name) = 'subinventory_attribute9' THEN l_location_data := l_location_data || VARIABLE_B || l_selected_fields(i).variable_name || '">' || v_location.subinventory_attribute9 || VARIABLE_E;
635 ELSIF LOWER(l_selected_fields(i).column_name) = 'subinventory_attribute10' THEN l_location_data := l_location_data || VARIABLE_B || l_selected_fields(i).variable_name || '">' || v_location.subinventory_attribute10 || VARIABLE_E;
636 ELSIF LOWER(l_selected_fields(i).column_name) = 'subinventory_attribute11' THEN l_location_data := l_location_data || VARIABLE_B || l_selected_fields(i).variable_name || '">' || v_location.subinventory_attribute11 || VARIABLE_E;
637 ELSIF LOWER(l_selected_fields(i).column_name) = 'subinventory_attribute12' THEN l_location_data := l_location_data || VARIABLE_B || l_selected_fields(i).variable_name || '">' || v_location.subinventory_attribute12 || VARIABLE_E;
638 ELSIF LOWER(l_selected_fields(i).column_name) = 'subinventory_attribute13' THEN l_location_data := l_location_data || VARIABLE_B || l_selected_fields(i).variable_name || '">' || v_location.subinventory_attribute13 || VARIABLE_E;
639 ELSIF LOWER(l_selected_fields(i).column_name) = 'subinventory_attribute14' THEN l_location_data := l_location_data || VARIABLE_B || l_selected_fields(i).variable_name || '">' || v_location.subinventory_attribute14 || VARIABLE_E;
640 ELSIF LOWER(l_selected_fields(i).column_name) = 'subinventory_attribute15' THEN l_location_data := l_location_data || VARIABLE_B || l_selected_fields(i).variable_name || '">' || v_location.subinventory_attribute15 || VARIABLE_E;
641 ELSIF LOWER(l_selected_fields(i).column_name) = 'subinventory_pick_order' THEN l_location_data := l_location_data || VARIABLE_B || l_selected_fields(i).variable_name || '">' || v_location.subinventory_pick_order || VARIABLE_E;
642 ELSIF LOWER(l_selected_fields(i).column_name) = 'subinventory_def_cost_group' THEN l_location_data := l_location_data || VARIABLE_B || l_selected_fields(i).variable_name || '">' || v_location.subinventory_def_cost_group || VARIABLE_E;
643 ELSIF LOWER(l_selected_fields(i).column_name) = 'locator' THEN l_location_data := l_location_data || VARIABLE_B || l_selected_fields(i).variable_name || '">' || v_location.locator || VARIABLE_E;
644 ELSIF LOWER(l_selected_fields(i).column_name) = 'locator_description' THEN l_location_data := l_location_data || VARIABLE_B || l_selected_fields(i).variable_name || '">' || v_location.locator_description || VARIABLE_E;
645 ELSIF LOWER(l_selected_fields(i).column_name) = 'locator_status' THEN l_location_data := l_location_data || VARIABLE_B || l_selected_fields(i).variable_name || '">' || v_location.locator_status || VARIABLE_E;
646 ELSIF LOWER(l_selected_fields(i).column_name) = 'locator_pick_uom' THEN l_location_data := l_location_data || VARIABLE_B || l_selected_fields(i).variable_name || '">' || v_location.locator_pick_uom || VARIABLE_E;
647 ELSIF LOWER(l_selected_fields(i).column_name) = 'locator_attribute_category' THEN l_location_data := l_location_data || VARIABLE_B || l_selected_fields(i).variable_name || '">' || v_location.locator_attribute_category || VARIABLE_E;
648 ELSIF LOWER(l_selected_fields(i).column_name) = 'locator_attribute1' THEN l_location_data := l_location_data || VARIABLE_B || l_selected_fields(i).variable_name || '">' || v_location.locator_attribute1 || VARIABLE_E;
649 ELSIF LOWER(l_selected_fields(i).column_name) = 'locator_attribute2' THEN l_location_data := l_location_data || VARIABLE_B || l_selected_fields(i).variable_name || '">' || v_location.locator_attribute2 || VARIABLE_E;
650 ELSIF LOWER(l_selected_fields(i).column_name) = 'locator_attribute3' THEN l_location_data := l_location_data || VARIABLE_B || l_selected_fields(i).variable_name || '">' || v_location.locator_attribute3 || VARIABLE_E;
651 ELSIF LOWER(l_selected_fields(i).column_name) = 'locator_attribute4' THEN l_location_data := l_location_data || VARIABLE_B || l_selected_fields(i).variable_name || '">' || v_location.locator_attribute4 || VARIABLE_E;
652 ELSIF LOWER(l_selected_fields(i).column_name) = 'locator_attribute5' THEN l_location_data := l_location_data || VARIABLE_B || l_selected_fields(i).variable_name || '">' || v_location.locator_attribute5 || VARIABLE_E;
653 ELSIF LOWER(l_selected_fields(i).column_name) = 'locator_attribute6' THEN l_location_data := l_location_data || VARIABLE_B || l_selected_fields(i).variable_name || '">' || v_location.locator_attribute6 || VARIABLE_E;
654 ELSIF LOWER(l_selected_fields(i).column_name) = 'locator_attribute7' THEN l_location_data := l_location_data || VARIABLE_B || l_selected_fields(i).variable_name || '">' || v_location.locator_attribute7 || VARIABLE_E;
655 ELSIF LOWER(l_selected_fields(i).column_name) = 'locator_attribute8' THEN l_location_data := l_location_data || VARIABLE_B || l_selected_fields(i).variable_name || '">' || v_location.locator_attribute8 || VARIABLE_E;
656 ELSIF LOWER(l_selected_fields(i).column_name) = 'locator_attribute9' THEN l_location_data := l_location_data || VARIABLE_B || l_selected_fields(i).variable_name || '">' || v_location.locator_attribute9 || VARIABLE_E;
657 ELSIF LOWER(l_selected_fields(i).column_name) = 'locator_attribute10' THEN l_location_data := l_location_data || VARIABLE_B || l_selected_fields(i).variable_name || '">' || v_location.locator_attribute10 || VARIABLE_E;
658 ELSIF LOWER(l_selected_fields(i).column_name) = 'locator_attribute11' THEN l_location_data := l_location_data || VARIABLE_B || l_selected_fields(i).variable_name || '">' || v_location.locator_attribute11 || VARIABLE_E;
659 ELSIF LOWER(l_selected_fields(i).column_name) = 'locator_attribute12' THEN l_location_data := l_location_data || VARIABLE_B || l_selected_fields(i).variable_name || '">' || v_location.locator_attribute12 || VARIABLE_E;
660 ELSIF LOWER(l_selected_fields(i).column_name) = 'locator_attribute13' THEN l_location_data := l_location_data || VARIABLE_B || l_selected_fields(i).variable_name || '">' || v_location.locator_attribute13 || VARIABLE_E;
661 ELSIF LOWER(l_selected_fields(i).column_name) = 'locator_attribute14' THEN l_location_data := l_location_data || VARIABLE_B || l_selected_fields(i).variable_name || '">' || v_location.locator_attribute14 || VARIABLE_E;
662 ELSIF LOWER(l_selected_fields(i).column_name) = 'locator_attribute15' THEN l_location_data := l_location_data || VARIABLE_B || l_selected_fields(i).variable_name || '">' || v_location.locator_attribute15 || VARIABLE_E;
663 ELSIF LOWER(l_selected_fields(i).column_name) = 'locator_project' THEN l_location_data := l_location_data || VARIABLE_B || l_selected_fields(i).variable_name || '">' || v_location.locator_project || VARIABLE_E;
664 ELSIF LOWER(l_selected_fields(i).column_name) = 'locator_task' THEN l_location_data := l_location_data || VARIABLE_B || l_selected_fields(i).variable_name || '">' || v_location.locator_task || VARIABLE_E;
665 ELSIF LOWER(l_selected_fields(i).column_name) = 'locator_pick_order' THEN l_location_data := l_location_data || VARIABLE_B || l_selected_fields(i).variable_name || '">' || v_location.locator_pick_order || VARIABLE_E;
666 ELSIF LOWER(l_selected_fields(i).column_name) = 'locator_weight_capacity' THEN l_location_data := l_location_data || VARIABLE_B || l_selected_fields(i).variable_name || '">' || v_location.locator_weight_capacity || VARIABLE_E;
667 ELSIF LOWER(l_selected_fields(i).column_name) = 'locator_weight_capacity_uom' THEN l_location_data := l_location_data || VARIABLE_B || l_selected_fields(i).variable_name || '">' || v_location.locator_weight_capacity_uom || VARIABLE_E;
668 ELSIF LOWER(l_selected_fields(i).column_name) = 'locator_volume_capacity' THEN l_location_data := l_location_data || VARIABLE_B || l_selected_fields(i).variable_name || '">' || v_location.locator_volume_capacity || VARIABLE_E;
669 ELSIF LOWER(l_selected_fields(i).column_name) = 'locator_volume_capacity_uom' THEN l_location_data := l_location_data || VARIABLE_B || l_selected_fields(i).variable_name || '">' || v_location.locator_volume_capacity_uom || VARIABLE_E;
670 ELSIF LOWER(l_selected_fields(i).column_name) = 'locator_unit_capacity' THEN l_location_data := l_location_data || VARIABLE_B || l_selected_fields(i).variable_name || '">' || v_location.locator_unit_capacity || VARIABLE_E;
671 ELSIF LOWER(l_selected_fields(i).column_name) = 'locator_alias' THEN l_location_data := l_location_data || VARIABLE_B || l_selected_fields(i).variable_name || '">' || v_location.locator_alias || VARIABLE_E;
672 END IF;
673
674 END LOOP;
675 l_location_data := l_location_data || LABEL_E;
676 x_variable_content(l_label_index).label_content := l_location_data;
677 x_variable_content(l_label_index).label_request_id := l_label_request_id;
678
679 ------------------------Start of changes for Custom Labels project code------------------
680 -- Fix for bug: 4179593 Start
681 IF (l_CustSqlWarnFlagSet) THEN
682 l_custom_sql_ret_status := INV_LABEL.G_WARNING;
683 l_custom_sql_ret_msg := l_CustSqlWarnMsg;
684 END IF;
685
686 IF (l_CustSqlErrFlagSet) THEN
687 l_custom_sql_ret_status := FND_API.G_RET_STS_ERROR;
688 l_custom_sql_ret_msg := l_CustSqlErrMsg;
689 END IF;
690 -- Fix for bug: 4179593 End
691
692 x_variable_content(l_label_index).label_status := l_custom_sql_ret_status ;
693 x_variable_content(l_label_index).error_message := l_custom_sql_ret_msg;
694 ------------------------End of this changes for Custom Labels project code---------------
695
696 l_label_index := l_label_index + 1;
697 <<NextLabel>>
698 l_location_data := '';
699 l_label_request_id := null;
700
701 ------------------------Start of changes for Custom Labels project code------------------
702 l_custom_sql_ret_status := NULL;
703 l_custom_sql_ret_msg := NULL;
704 ------------------------End of this changes for Custom Labels project code---------------
705
706 IF(l_debug=1) THEN
707 trace(l_column_name_list);
708 trace('Finished writing one label');
709 END IF;
710 END LOOP;
711
712 --x_variable_content := x_variable_content || l_location_data;
713 IF (l_debug = 1) THEN
714 trace(' Getting the next sub/locator ');
715 END IF;
716 IF l_print_all_mode = 2 THEN
717 -- print org and all the sub
718 FETCH c_all_sub INTO l_subinventory_code;
719 IF c_all_sub%NOTFOUND THEN
720 l_organization_id := null;
721 l_subinventory_code := null;
722 l_locator_id := null;
723 CLOSE c_all_sub;
724 ELSE
725 IF (l_debug = 1) THEN
726 trace(' Found next sub'|| l_subinventory_code );
727 END IF;
728 END IF;
729 ELSIF l_print_all_mode = 4 THEN
730 -- Print org, all the locators for the given sub or all sub
731 FETCH c_all_sub_loc INTO l_subinventory_code, l_locator_id;
732 IF c_all_sub_loc%NOTFOUND THEN
733 IF (l_debug = 1) THEN
734 trace(' Done with org ' || l_organization_id);
735 END IF;
736 l_organization_id := null;
737 l_subinventory_code := null;
738 l_locator_id := null;
739 CLOSE c_all_sub_loc;
740 ELSE
741 IF (l_debug = 1) THEN
742 trace(' Found next sub, loc : '|| l_subinventory_code ||','|| l_locator_id);
743 END IF;
744 END IF;
745 ELSE
746 l_organization_id := null;
747 END IF;
748
749 END LOOP;
750 END get_variable_data;
751
752 PROCEDURE get_variable_data(
753 x_variable_content OUT NOCOPY LONG
754 , x_msg_count OUT NOCOPY NUMBER
755 , x_msg_data OUT NOCOPY VARCHAR2
756 , x_return_status OUT NOCOPY VARCHAR2
757 , p_label_type_info IN INV_LABEL.label_type_rec
758 , p_transaction_id IN NUMBER
759 , p_input_param IN MTL_MATERIAL_TRANSACTIONS_TEMP%ROWTYPE
760 , p_transaction_identifier IN NUMBER
761 ) IS
762 l_variable_data_tbl INV_LABEL.label_tbl_type;
763 BEGIN
764 get_variable_data(
765 x_variable_content => l_variable_data_tbl
766 , x_msg_count => x_msg_count
767 , x_msg_data => x_msg_data
768 , x_return_status => x_return_status
769 , p_label_type_info => p_label_type_info
770 , p_transaction_id => p_transaction_id
771 , p_input_param => p_input_param
772 , p_transaction_identifier=> p_transaction_identifier
773 );
774
775 x_variable_content := '';
776
777 FOR i IN 1..l_variable_data_tbl.count() LOOP
778 x_variable_content := x_variable_content || l_variable_data_tbl(i).label_content;
779 END LOOP;
780
781 END get_variable_data;
782
783
784 END INV_LABEL_PVT6;