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