[Home] [Help]
PACKAGE BODY: APPS.INV_LABEL_PVT9
Source
1 PACKAGE BODY inv_label_pvt9 AS
2 /* $Header: INVLAP9B.pls 120.8.12010000.2 2008/07/29 13:41:16 ptkumar ship $ */
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 -- Bug 2795525 : This mask is used to mask all date fields.
10 g_date_format_mask VARCHAR2(100) := inv_label.g_date_format_mask;
11 g_header_printed BOOLEAN := FALSE;
12 g_user_name fnd_user.user_name%TYPE := fnd_global.user_name;
13
14 PROCEDURE TRACE(p_message VARCHAR2) IS
15 BEGIN
16 IF (g_header_printed = FALSE) THEN
17 inv_label.TRACE('$Header: INVLAP9B.pls 120.8.12010000.2 2008/07/29 13:41:16 ptkumar ship $', g_pkg_name || ' - ' || 'LABEL_WIP_CONT');
18 g_header_printed := TRUE;
19 END IF;
20
21 inv_label.TRACE(g_user_name || ': ' || p_message, 'LABEL_WIP_CONT');
22 END TRACE;
23
24 PROCEDURE get_variable_data(
25 x_variable_content OUT NOCOPY inv_label.label_tbl_type
26 , x_msg_count OUT NOCOPY NUMBER
27 , x_msg_data OUT NOCOPY VARCHAR2
28 , x_return_status OUT NOCOPY VARCHAR2
29 , p_label_type_info IN inv_label.label_type_rec
30 , p_transaction_id IN NUMBER
31 , p_input_param IN mtl_material_transactions_temp%ROWTYPE
32 , p_transaction_identifier IN NUMBER
33 ) IS
34 l_api_name VARCHAR2(20) := 'get_variable_data';
35
36 ---------------------------------------------------------------------------------------------
37 -- Project: 'Custom Labels' (A 11i10+ Project) |
38 -- Author: Dinesh ([email protected]) |
39 -- Change Description: |
40 -- Following variables were added (as a part of 11i10+ 'Custom Labels' Project) |
41 -- to retrieve and hold the SQL Statement and it's result. |
42 ---------------------------------------------------------------------------------------------
43 l_sql_stmt VARCHAR2(4000);
44 l_sql_stmt_result VARCHAR2(4000) := NULL;
45 TYPE sql_stmt IS REF CURSOR;
46 c_sql_stmt sql_stmt;
47 l_custom_sql_ret_status VARCHAR2(1);
48 l_custom_sql_ret_msg VARCHAR2(2000);
49
50 -- Fix for bug: 4179593 Start
51 l_CustSqlWarnFlagSet BOOLEAN;
52 l_CustSqlErrFlagSet BOOLEAN;
53 l_CustSqlWarnMsg VARCHAR2(2000);
54 l_CustSqlErrMsg VARCHAR2(2000);
55 -- Fix for bug: 4179593 End
56
57 ------------------------End of this change for Custom Labels project code--------------------
58
59 l_organization_id NUMBER := NULL;
60 l_wip_entity_id NUMBER := NULL;
61
62 --Bug #6417575,Label Printing Support for WIP Move Transactions (12.1)
63 -- Created new cursor to fetch WIP Job Attributes based on wip_entity_id.
64 CURSOR wip_discrete_cur IS
65 SELECT
66 -- Discrete Job
67 wipent.wip_entity_name job_schedule_name
68 , wipent.description job_schedule_description
69 , msik3.concatenated_segments job_assembly
70 , msik3.description assembly_description
71 , mfglkp1.meaning job_schedule_type
72 , wipdj.class_code job_accounting_class
73 , wipdj.attribute_category job_attribute_category
74 , wipdj.attribute1 job_attribute1
75 , wipdj.attribute2 job_attribute2
76 , wipdj.attribute3 job_attribute3
77 , wipdj.attribute4 job_attribute4
78 , wipdj.attribute5 job_attribute5
79 , wipdj.attribute6 job_attribute6
80 , wipdj.attribute7 job_attribute7
81 , wipdj.attribute8 job_attribute8
82 , wipdj.attribute9 job_attribute9
83 , wipdj.attribute10 job_attribute10
84 , wipdj.attribute11 job_attribute11
85 , wipdj.attribute12 job_attribute12
86 , wipdj.attribute13 job_attribute13
87 , wipdj.attribute14 job_attribute14
88 , wipdj.attribute15 job_attribute15
89 , wipdj.bom_revision job_bom_revision
90 , inv_project.get_locsegs(mila.inventory_location_id, mila.organization_id) job_completion_locator
91 , wipdj.completion_subinventory job_completion_subinventory
92 , wipdj.demand_class job_demand_class
93 , TO_CHAR(wipdj.due_date, g_date_format_mask) job_due_date
94 , -- Added for Bug 2795525
95 mfglkp2.meaning job_job_type
96 , wipdj.net_quantity job_net_quantity
97 , wipdj.priority job_priority
98 , TO_CHAR(wipdj.date_released, g_date_format_mask) job_release_date
99 , -- Added for Bug 2795525
100 wipdj.routing_revision job_routing_revision
101 , TO_CHAR(wipdj.scheduled_completion_date, g_date_format_mask) job_schedule_completion_date
102 , -- Added for Bug 2795525
103 TO_CHAR(wipdj.scheduled_start_date, g_date_format_mask) job_start_date
104 , -- Added for Bug 2795525
105 wipdj.start_quantity job_start_quantity
106 , mfglkp3.meaning job_status_type
107 ,
108 -- Repetitive Schedule
109 NULL repet_sched_alternate_bom
110 , NULL repet_sched_alternate_routing
111 , NULL repet_sched_attribute_category
112 , NULL repet_sched_attribute1
113 , NULL repet_sched_attribute2
114 , NULL repet_sched_attribute3
115 , NULL repet_sched_attribute4
116 , NULL repet_sched_attribute5
117 , NULL repet_sched_attribute6
118 , NULL repet_sched_attribute7
119 , NULL repet_sched_attribute8
120 , NULL repet_sched_attribute9
121 , NULL repet_sched_attribute10
122 , NULL repet_sched_attribute11
123 , NULL repet_sched_attribute12
124 , NULL repet_sched_attribute13
125 , NULL repet_sched_attribute14
126 , NULL repet_sched_attribute15
127 , NULL repet_sched_bom_revision
128 , NULL repet_sched_daily_quantity
129 , NULL repet_sched_demand_class
130 , NULL repet_sched_description
131 , NULL repet_sched_firm_flag
132 , NULL repet_sched_first_comple_dt
133 , -- Added for Bug 2795525
134 NULL repet_sched_first_start_date
135 , -- Added for Bug 2795525
136 NULL repet_sched_last_complet_dt
137 , -- Added for Bug 2795525
138 NULL repet_sched_last_start_date
139 , -- Added for Bug 2795525
140 NULL repet_sched_processing_date
141 , -- Added for Bug 2795525
142 NULL repet_sched_release_date
143 , -- Added for Bug 2795525
144 NULL repet_sched_routing_revision
145 , NULL repet_sched_schedule_status
146 ,
147 -- Operations
148 NULL operations_attribute_category
149 , NULL operations_attribute1
150 , NULL operations_attribute2
151 , NULL operations_attribute3
152 , NULL operations_attribute4
153 , NULL operations_attribute5
154 , NULL operations_attribute6
155 , NULL operations_attribute7
156 , NULL operations_attribute8
157 , NULL operations_attribute9
158 , NULL operations_attribute10
159 , NULL operations_attribute11
160 , NULL operations_attribute12
161 , NULL operations_attribute13
162 , NULL operations_attribute14
163 , NULL operations_attribute15
164 , NULL operations_backflush_flag
165 , NULL operations_department
166 , NULL operations_description
167 , NULL operations_first_complet_dt
168 , -- Added for Bug 2795525
169 NULL operations_first_receipt_date
170 , -- Added for Bug 2795525
171 NULL operations_last_complet_dt
172 , -- Added for Bug 2795525
173 NULL operations_last_receipt_date
174 , -- Added for Bug 2795525
175 NULL operations_min_transfer_qty
176 , NULL operations_schedule_quantity
177 , NULL operations_standard_operation
178 , NULL operations_yield
179 , NULL operations_yield_enabled
180 ,
181 -- Requirements
182 NULL requirements_attribute_categ
183 , NULL requirements_attribute1
184 , NULL requirements_attribute2
185 , NULL requirements_attribute3
186 , NULL requirements_attribute4
187 , NULL requirements_attribute5
188 , NULL requirements_attribute6
189 , NULL requirements_attribute7
190 , NULL requirements_attribute8
191 , NULL requirements_attribute9
192 , NULL requirements_attribute10
193 , NULL requirements_attribute11
194 , NULL requirements_attribute12
195 , NULL requirements_attribute13
196 , NULL requirements_attribute14
197 , NULL requirements_attribute15
198 , NULL requirements_comments
199 , NULL requirements_date_required
200 , -- Added for Bug 2795525
201 NULL requirements_department_name
202 , NULL requirements_operation_seq
203 , NULL requirements_qty_per_assembly
204 , NULL requirements_required_quantity
205 , NULL requirements_supply_locator
206 , NULL requirements_supply_sub
207 , NULL requirements_wip_supply_type
208 , NULL requirements_item_name
209 , wipent.organization_id organization_id
210 , NULL inventory_item_id
211 , wipent.entity_type entity_type
212 ,
213 -- LPN Content
214 NULL lpn_id
215 , NULL lpn
216 , NULL parent_lpn_id
217 , NULL parent_lpn
218 , NULL volume
219 , NULL volume_uom
220 , NULL gross_weight
221 , NULL gross_weight_uom
222 , NULL tare_weight
223 , NULL tare_weight_uom
224 , NULL lpn_container_item_id
225 , NULL lpn_container_item
226 , mp.organization_code ORGANIZATION
227 , NULL item
228 , NULL item_description
229 , NULL revision
230 , NULL lot
231 , NULL lot_status
232 , NULL lot_expiration_date
233 , NULL quantity
234 , NULL uom
235 , NULL cost_group
236 , NULL item_hazard_class
237 , NULL item_attribute_category
238 , NULL item_attribute1
239 , NULL item_attribute2
240 , NULL item_attribute3
241 , NULL item_attribute4
242 , NULL item_attribute5
243 , NULL item_attribute6
244 , NULL item_attribute7
245 , NULL item_attribute8
246 , NULL item_attribute9
247 , NULL item_attribute10
248 , NULL item_attribute11
249 , NULL item_attribute12
250 , NULL item_attribute13
251 , NULL item_attribute14
252 , NULL item_attribute15
253 , NULL lpn_attribute_category
254 , NULL lpn_attribute1
255 , NULL lpn_attribute2
256 , NULL lpn_attribute3
257 , NULL lpn_attribute4
258 , NULL lpn_attribute5
259 , NULL lpn_attribute6
260 , NULL lpn_attribute7
261 , NULL lpn_attribute8
262 , NULL lpn_attribute9
263 , NULL lpn_attribute10
264 , NULL lpn_attribute11
265 , NULL lpn_attribute12
266 , NULL lpn_attribute13
267 , NULL lpn_attribute14
268 , NULL lpn_attribute15
269 , NULL lot_attribute_category
270 , NULL lot_c_attribute1
271 , NULL lot_c_attribute2
272 , NULL lot_c_attribute3
273 , NULL lot_c_attribute4
274 , NULL lot_c_attribute5
275 , NULL lot_c_attribute6
276 , NULL lot_c_attribute7
277 , NULL lot_c_attribute8
278 , NULL lot_c_attribute9
279 , NULL lot_c_attribute10
280 , NULL lot_c_attribute11
281 , NULL lot_c_attribute12
282 , NULL lot_c_attribute13
283 , NULL lot_c_attribute14
284 , NULL lot_c_attribute15
285 , NULL lot_c_attribute16
286 , NULL lot_c_attribute17
287 , NULL lot_c_attribute18
288 , NULL lot_c_attribute19
289 , NULL lot_c_attribute20
290 , NULL lot_d_attribute1 -- Added for Bug 2795525,
291 , NULL lot_d_attribute2 -- Added for Bug 2795525,
292 , NULL lot_d_attribute3 -- Added for Bug 2795525,
293 , NULL lot_d_attribute4 -- Added for Bug 2795525,
294 , NULL lot_d_attribute5 -- Added for Bug 2795525,
295 , NULL lot_d_attribute6 -- Added for Bug 2795525,
296 , NULL lot_d_attribute7 -- Added for Bug 2795525,
297 , NULL lot_d_attribute8 -- Added for Bug 2795525,
298 , NULL lot_d_attribute9 -- Added for Bug 2795525,
299 , NULL lot_d_attribute10 -- Added for Bug 2795525,
300 , NULL lot_n_attribute1
301 , NULL lot_n_attribute2
302 , NULL lot_n_attribute3
303 , NULL lot_n_attribute4
304 , NULL lot_n_attribute5
305 , NULL lot_n_attribute6
306 , NULL lot_n_attribute7
307 , NULL lot_n_attribute8
308 , NULL lot_n_attribute9
309 , NULL lot_n_attribute10
310 , NULL lot_country_of_origin
311 , NULL lot_grade_code
312 , NULL lot_origination_date -- Added for Bug 2795525,
313 , NULL lot_date_code
314 , NULL lot_change_date -- Added for Bug 2795525,
315 , NULL lot_age
316 , NULL lot_retest_date -- Added for Bug 2795525,
317 , NULL lot_maturity_date -- Added for Bug 2795525,
318 , NULL lot_item_size
319 , NULL lot_color
320 , NULL lot_volume
321 , NULL lot_volume_uom
322 , NULL lot_place_of_origin
323 , NULL lot_best_by_date -- Added for Bug 2795525,
324 , NULL lot_length
325 , NULL lot_length_uom
326 , NULL lot_recycled_cont
327 , NULL lot_thickness
328 , NULL lot_thickness_uom
329 , NULL lot_width
330 , NULL lot_width_uom
331 , NULL lot_curl
332 , NULL lot_vendor
333 , NULL subinventory_code
334 , NULL LOCATOR
335 FROM wip_entities wipent
336 , wip_discrete_jobs wipdj
337 , mfg_lookups mfglkp1
338 , mfg_lookups mfglkp2
339 , mfg_lookups mfglkp3
340 , mtl_system_items_vl msik3
341 , mtl_item_locations mila
342 , mtl_parameters mp
343 WHERE wipdj.wip_entity_id = wipent.wip_entity_id
344 AND wipdj.organization_id = wipent.organization_id
345 AND wipent.wip_entity_id = l_wip_entity_id
346 AND wipent.organization_id = l_organization_id
347 AND mp.organization_id = wipent.organization_id
348 AND mfglkp1.lookup_code(+) = wipent.entity_type
349 AND mfglkp1.lookup_type(+) = 'WIP_ENTITY'
350 AND mfglkp2.lookup_code(+) = wipdj.job_type
351 AND mfglkp2.lookup_type(+) = 'WIP_DISCRETE_JOB'
352 AND mfglkp3.lookup_code(+) = wipdj.status_type
353 AND mfglkp3.lookup_type(+) = 'WIP_JOB_STATUS'
354 AND msik3.inventory_item_id(+) = wipent.primary_item_id
355 AND msik3.organization_id(+) = wipent.organization_id
356 AND mila.inventory_location_id(+) = wipdj.completion_locator_id;
357
358 -- Bug 2829872
359 -- Cursor for Manufacturing Cross-Dock(37)
360 CURSOR wip_cross_dock_cur IS
361 SELECT mmtt.organization_id
362 , mmtt.demand_source_header_id
363 , mmtt.schedule_id
364 , mmtt.inventory_item_id
365 , mmtt.operation_seq_num
366 , NVL(mmtt.content_lpn_id, mmtt.lpn_id)
367 , mmtt.revision
368 , mtlt.lot_number
369 , NVL(mtlt.transaction_quantity, mmtt.transaction_quantity)
370 , mmtt.transaction_uom
371 , mmtt.cost_group_id
372 , mmtt.subinventory_code
373 , pp.NAME
374 , pt.task_name
375 FROM mtl_material_transactions_temp mmtt, mtl_transaction_lots_temp mtlt, pa_projects pp, pa_tasks pt
376 WHERE mtlt.transaction_temp_id(+) = mmtt.transaction_temp_id
377 AND mmtt.project_id = pp.project_id(+)
378 AND mmtt.task_id = pt.task_id(+)
379 AND mmtt.transaction_temp_id = p_transaction_id;
380
381 CURSOR wip_lines_cur IS
382 SELECT mtrl.organization_id
383 , mtrl.txn_source_id
384 , mtrl.reference_id
385 , mtrl.inventory_item_id
386 , mtrl.txn_source_line_id
387 , mmtt.transfer_lpn_id
388 , mmtt.revision
389 , mtlt.lot_number
390 -- Bug 2781198. labels fails for lot controlled component
391 , NVL(mtlt.transaction_quantity, mmtt.transaction_quantity)
392 , mmtt.transaction_uom
393 , mmtt.cost_group_id
394 , mmtt.subinventory_code
395 -- Bug 2673874 : joabraha
396 , pp.NAME
397 , pt.task_name
398 FROM mtl_txn_request_lines mtrl
399 , mtl_material_transactions_temp mmtt
400 , mtl_transaction_lots_temp mtlt
401 -- bug 3075322
402 -- , mtl_item_locations mil commented for bug 3776231
403 , wip_discrete_jobs wdj -- added for bug 3776231
404 -- Bug 2673874 : joabraha
405 , pa_projects pp
406 , pa_tasks pt
407 WHERE mtrl.line_id = mmtt.move_order_line_id
408 AND mtlt.transaction_temp_id(+) = mmtt.transaction_temp_id
409 /*commented for bug 3776231
410 -- start bug 3075322
411 AND mmtt.locator_id = mil.inventory_location_id(+)
412 AND pp.project_id(+) = mil.segment19
413 AND pt.task_id(+) = mil.segment20
414 -- end bug 3075322
415 Added the following for bug 3776231*/
416 AND mmtt.transaction_source_id = wdj.wip_entity_id
417 AND pp.project_id(+) = wdj.project_id
418 AND pt.task_id(+) = wdj.task_id
419 -- End of fix for 3776231
420 AND mmtt.transaction_temp_id = p_transaction_id;
421
422 CURSOR wip_drop_lines_cur IS
423 SELECT mtrl.organization_id
424 , mtrl.txn_source_id
425 , mtrl.reference_id
426 , mtrl.inventory_item_id
427 , mtrl.txn_source_line_id
428 , mmtt.transfer_lpn_id
429 , mmtt.revision
430 , mtlt.lot_number
431 -- Bug 2781198. labels fails for lot controlled component
432 , NVL(mtlt.transaction_quantity, mmtt.transaction_quantity)
433 , mmtt.transaction_uom
434 , mmtt.cost_group_id
435 , mmtt.transfer_subinventory
436 -- Bug 2673874 : joabraha
437 , pp.NAME
438 , pt.task_name
439 FROM mtl_txn_request_lines mtrl
440 , mtl_material_transactions_temp mmtt
441 , mtl_transaction_lots_temp mtlt
442 -- bug 3075322
443 -- , mtl_item_locations mil commented for bug 3776231
444 , wip_discrete_jobs wdj -- added for bug 3776231
445 -- Bug 2673874 : joabraha
446 , pa_projects pp
447 , pa_tasks pt
448 WHERE mtrl.line_id = mmtt.move_order_line_id
449 AND mtlt.transaction_temp_id(+) = mmtt.transaction_temp_id
450 /*commented for bug 3776231
451 -- start bug 3075322
452 AND mmtt.locator_id = mil.inventory_location_id(+)
453 AND pp.project_id(+) = mil.segment19
454 AND pt.task_id(+) = mil.segment20
455 -- end bug 3075322
456 Added the following for bug 3776231*/
457 AND mmtt.transaction_source_id = wdj.wip_entity_id
458 AND pp.project_id(+) = wdj.project_id
459 AND pt.task_id(+) = wdj.task_id
460 -- End of fix for 3776231
461 AND mmtt.transaction_temp_id = p_transaction_id;
462
463
464 l_repetitive_schedule_id NUMBER := NULL;
465 l_inventory_item_id NUMBER := NULL;
466 l_subinventory VARCHAR2(30) := NULL;
467 l_operation_seq_num NUMBER := NULL;
468 l_lpn_id NUMBER := NULL;
469 l_revision VARCHAR2(3) := NULL;
470 -- Increased lot size to 80 Char - Mercy Thomas - B4625329
471 l_lot_number VARCHAR2(80) := NULL;
472 l_cost_group_id NUMBER := NULL;
473 l_quantity NUMBER := NULL;
474 l_uom VARCHAR2(3) := NULL;
475 -- Bug 2660701 : joabraha
476 l_project_name VARCHAR2(240) := NULL;
477 l_task_name VARCHAR2(240) := NULL;
478
479 CURSOR wip_content_cur IS
480 SELECT
481 -- Discrete Job
482 wipent.wip_entity_name job_schedule_name
483 , wipent.description job_schedule_description
484 , msik3.concatenated_segments job_assembly
485 , msik3.description assembly_description
486 , mfglkp1.meaning job_schedule_type
487 , wipdj.class_code job_accounting_class
488 , wipdj.attribute_category job_attribute_category
489 , wipdj.attribute1 job_attribute1
490 , wipdj.attribute2 job_attribute2
491 , wipdj.attribute3 job_attribute3
492 , wipdj.attribute4 job_attribute4
493 , wipdj.attribute5 job_attribute5
494 , wipdj.attribute6 job_attribute6
495 , wipdj.attribute7 job_attribute7
496 , wipdj.attribute8 job_attribute8
497 , wipdj.attribute9 job_attribute9
498 , wipdj.attribute10 job_attribute10
499 , wipdj.attribute11 job_attribute11
500 , wipdj.attribute12 job_attribute12
501 , wipdj.attribute13 job_attribute13
502 , wipdj.attribute14 job_attribute14
503 , wipdj.attribute15 job_attribute15
504 , wipdj.bom_revision job_bom_revision
505 , inv_project.get_locsegs(mila.inventory_location_id, mila.organization_id) job_completion_locator
506 , wipdj.completion_subinventory job_completion_subinventory
507 , wipdj.demand_class job_demand_class
508 , TO_CHAR(wipdj.due_date, g_date_format_mask) job_due_date
509 , -- Added for Bug 2795525
510 mfglkp2.meaning job_job_type
511 , wipdj.net_quantity job_net_quantity
512 , wipdj.priority job_priority
513 , TO_CHAR(wipdj.date_released, g_date_format_mask) job_release_date
514 , -- Added for Bug 2795525
515 wipdj.routing_revision job_routing_revision
516 , TO_CHAR(wipdj.scheduled_completion_date, g_date_format_mask) job_schedule_completion_date
517 , -- Added for Bug 2795525
518 TO_CHAR(wipdj.scheduled_start_date, g_date_format_mask) job_start_date
519 , -- Added for Bug 2795525
520 wipdj.start_quantity job_start_quantity
521 , mfglkp3.meaning job_status_type
522 ,
523 -- Repetitive Schedule
524 wiprs.alternate_bom_designator repet_sched_alternate_bom
525 , wiprs.alternate_routing_designator repet_sched_alternate_routing
526 , wiprs.attribute_category repet_sched_attribute_category
527 , wiprs.attribute1 repet_sched_attribute1
528 , wiprs.attribute2 repet_sched_attribute2
529 , wiprs.attribute3 repet_sched_attribute3
530 , wiprs.attribute4 repet_sched_attribute4
531 , wiprs.attribute5 repet_sched_attribute5
532 , wiprs.attribute6 repet_sched_attribute6
533 , wiprs.attribute7 repet_sched_attribute7
534 , wiprs.attribute8 repet_sched_attribute8
535 , wiprs.attribute9 repet_sched_attribute9
536 , wiprs.attribute10 repet_sched_attribute10
537 , wiprs.attribute11 repet_sched_attribute11
538 , wiprs.attribute12 repet_sched_attribute12
539 , wiprs.attribute13 repet_sched_attribute13
540 , wiprs.attribute14 repet_sched_attribute14
541 , wiprs.attribute15 repet_sched_attribute15
542 , wiprs.bom_revision repet_sched_bom_revision
543 , wiprs.daily_production_rate repet_sched_daily_quantity
544 , wiprs.demand_class repet_sched_demand_class
545 , wiprs.description repet_sched_description
546 , wiprs.firm_planned_flag repet_sched_firm_flag
547 , TO_CHAR(wiprs.first_unit_completion_date, g_date_format_mask) repet_sched_first_comple_dt
548 , -- Added for Bug 2795525
549 TO_CHAR(wiprs.first_unit_start_date, g_date_format_mask) repet_sched_first_start_date
550 , -- Added for Bug 2795525
551 TO_CHAR(wiprs.last_unit_completion_date, g_date_format_mask) repet_sched_last_complet_dt
552 , -- Added for Bug 2795525
553 TO_CHAR(wiprs.last_unit_start_date, g_date_format_mask) repet_sched_last_start_date
554 , -- Added for Bug 2795525
555 TO_CHAR(wiprs.processing_work_days, g_date_format_mask) repet_sched_processing_date
556 , -- Added for Bug 2795525
557 TO_CHAR(wiprs.date_released, g_date_format_mask) repet_sched_release_date
558 , -- Added for Bug 2795525
559 wiprs.routing_revision repet_sched_routing_revision
560 , mfglkp4.meaning repet_sched_schedule_status
561 ,
562 -- Operations
563 wipops.attribute_category operations_attribute_category
564 , wipops.attribute1 operations_attribute1
565 , wipops.attribute2 operations_attribute2
566 , wipops.attribute3 operations_attribute3
567 , wipops.attribute4 operations_attribute4
568 , wipops.attribute5 operations_attribute5
569 , wipops.attribute6 operations_attribute6
570 , wipops.attribute7 operations_attribute7
571 , wipops.attribute8 operations_attribute8
572 , wipops.attribute9 operations_attribute9
573 , wipops.attribute10 operations_attribute10
574 , wipops.attribute11 operations_attribute11
575 , wipops.attribute12 operations_attribute12
576 , wipops.attribute13 operations_attribute13
577 , wipops.attribute14 operations_attribute14
578 , wipops.attribute15 operations_attribute15
579 , mfglkp5.meaning operations_backflush_flag
580 , bomdep1.department_code operations_department
581 , wipops.description operations_description
582 , TO_CHAR(wipops.first_unit_completion_date, g_date_format_mask) operations_first_complet_dt
583 , -- Added for Bug 2795525
584 TO_CHAR(wipops.first_unit_start_date, g_date_format_mask) operations_first_receipt_date
585 , -- Added for Bug 2795525
586 TO_CHAR(wipops.last_unit_completion_date, g_date_format_mask) operations_last_complet_dt
587 , -- Added for Bug 2795525
588 TO_CHAR(wipops.last_unit_start_date, g_date_format_mask) operations_last_receipt_date
589 , -- Added for Bug 2795525
590 wipops.minimum_transfer_quantity operations_min_transfer_qty
591 , wipops.scheduled_quantity operations_schedule_quantity
592 , wipops.standard_operation_id operations_standard_operation
593 , wipops.operation_yield operations_yield
594 , wipops.operation_yield_enabled operations_yield_enabled
595 ,
596 -- Requirements
597 wipreqops.attribute_category requirements_attribute_categ
598 , wipreqops.attribute1 requirements_attribute1
599 , wipreqops.attribute2 requirements_attribute2
600 , wipreqops.attribute3 requirements_attribute3
601 , wipreqops.attribute4 requirements_attribute4
602 , wipreqops.attribute5 requirements_attribute5
603 , wipreqops.attribute6 requirements_attribute6
604 , wipreqops.attribute7 requirements_attribute7
605 , wipreqops.attribute8 requirements_attribute8
606 , wipreqops.attribute9 requirements_attribute9
607 , wipreqops.attribute10 requirements_attribute10
608 , wipreqops.attribute11 requirements_attribute11
609 , wipreqops.attribute12 requirements_attribute12
610 , wipreqops.attribute13 requirements_attribute13
611 , wipreqops.attribute14 requirements_attribute14
612 , wipreqops.attribute15 requirements_attribute15
613 , wipreqops.comments requirements_comments
614 , TO_CHAR(wipreqops.date_required, g_date_format_mask) requirements_date_required
615 , -- Added for Bug 2795525
616 bomdep2.department_code requirements_department_name
617 , wipreqops.operation_seq_num requirements_operation_seq
618 , wipreqops.quantity_per_assembly requirements_qty_per_assembly
619 , wipreqops.required_quantity requirements_required_quantity
620 , inv_project.get_locsegs(milb.inventory_location_id, milb.organization_id) requirements_supply_locator
621 , wipreqops.supply_subinventory requirements_supply_sub
622 , mfglkp6.meaning requirements_wip_supply_type
623 , msik2.concatenated_segments requirements_item_name
624 , wipent.organization_id organization_id
625 , wipreqops.inventory_item_id inventory_item_id
626 , wipent.entity_type entity_type
627 ,
628 -- LPN Content
629 lpn.lpn_id lpn_id
630 , lpn.license_plate_number lpn
631 , plpn.lpn_id parent_lpn_id
632 , plpn.license_plate_number parent_lpn
633 , lpn.content_volume volume
634 , lpn.content_volume_uom_code volume_uom
635 , lpn.gross_weight gross_weight
636 , lpn.gross_weight_uom_code gross_weight_uom
637 , lpn.tare_weight tare_weight
638 , lpn.tare_weight_uom_code tare_weight_uom
639 , lpn.inventory_item_id lpn_container_item_id
640 , msik1.concatenated_segments lpn_container_item
641 , mp.organization_code ORGANIZATION
642 , msik2.concatenated_segments item
643 , msik2.description item_description
644 , NVL(wlc.revision, l_revision) revision
645 , NVL(wlc.lot_number, l_lot_number) lot
646 , mmsv1.status_code lot_status
647 , mln.expiration_date lot_expiration_date
648 , ABS(NVL(wlc.quantity, l_quantity)) quantity
649 , NVL(wlc.uom_code, l_uom) uom
650 , ccg.cost_group cost_group
651 , poh.hazard_class item_hazard_class
652 , msik2.attribute_category item_attribute_category
653 , msik2.attribute1 item_attribute1
654 , msik2.attribute2 item_attribute2
655 , msik2.attribute3 item_attribute3
656 , msik2.attribute4 item_attribute4
657 , msik2.attribute5 item_attribute5
658 , msik2.attribute6 item_attribute6
659 , msik2.attribute7 item_attribute7
660 , msik2.attribute8 item_attribute8
661 , msik2.attribute9 item_attribute9
662 , msik2.attribute10 item_attribute10
663 , msik2.attribute11 item_attribute11
664 , msik2.attribute12 item_attribute12
665 , msik2.attribute13 item_attribute13
666 , msik2.attribute14 item_attribute14
667 , msik2.attribute15 item_attribute15
668 , lpn.attribute_category lpn_attribute_category
669 , lpn.attribute1 lpn_attribute1
670 , lpn.attribute2 lpn_attribute2
671 , lpn.attribute3 lpn_attribute3
672 , lpn.attribute4 lpn_attribute4
673 , lpn.attribute5 lpn_attribute5
674 , lpn.attribute6 lpn_attribute6
675 , lpn.attribute7 lpn_attribute7
676 , lpn.attribute8 lpn_attribute8
677 , lpn.attribute9 lpn_attribute9
678 , lpn.attribute10 lpn_attribute10
679 , lpn.attribute11 lpn_attribute11
680 , lpn.attribute12 lpn_attribute12
681 , lpn.attribute13 lpn_attribute13
682 , lpn.attribute14 lpn_attribute14
683 , lpn.attribute15 lpn_attribute15
684 , mln.lot_attribute_category lot_attribute_category
685 , mln.c_attribute1 lot_c_attribute1
686 , mln.c_attribute2 lot_c_attribute2
687 , mln.c_attribute3 lot_c_attribute3
688 , mln.c_attribute4 lot_c_attribute4
689 , mln.c_attribute5 lot_c_attribute5
690 , mln.c_attribute6 lot_c_attribute6
691 , mln.c_attribute7 lot_c_attribute7
692 , mln.c_attribute8 lot_c_attribute8
693 , mln.c_attribute9 lot_c_attribute9
694 , mln.c_attribute10 lot_c_attribute10
695 , mln.c_attribute11 lot_c_attribute11
696 , mln.c_attribute12 lot_c_attribute12
697 , mln.c_attribute13 lot_c_attribute13
698 , mln.c_attribute14 lot_c_attribute14
699 , mln.c_attribute15 lot_c_attribute15
700 , mln.c_attribute16 lot_c_attribute16
701 , mln.c_attribute17 lot_c_attribute17
702 , mln.c_attribute18 lot_c_attribute18
703 , mln.c_attribute19 lot_c_attribute19
704 , mln.c_attribute20 lot_c_attribute20
705 , TO_CHAR(mln.d_attribute1, g_date_format_mask) lot_d_attribute1 -- Added for Bug 2795525,
706 , TO_CHAR(mln.d_attribute2, g_date_format_mask) lot_d_attribute2 -- Added for Bug 2795525,
707 , TO_CHAR(mln.d_attribute3, g_date_format_mask) lot_d_attribute3 -- Added for Bug 2795525,
708 , TO_CHAR(mln.d_attribute4, g_date_format_mask) lot_d_attribute4 -- Added for Bug 2795525,
709 , TO_CHAR(mln.d_attribute5, g_date_format_mask) lot_d_attribute5 -- Added for Bug 2795525,
710 , TO_CHAR(mln.d_attribute6, g_date_format_mask) lot_d_attribute6 -- Added for Bug 2795525,
711 , TO_CHAR(mln.d_attribute7, g_date_format_mask) lot_d_attribute7 -- Added for Bug 2795525,
712 , TO_CHAR(mln.d_attribute8, g_date_format_mask) lot_d_attribute8 -- Added for Bug 2795525,
713 , TO_CHAR(mln.d_attribute9, g_date_format_mask) lot_d_attribute9 -- Added for Bug 2795525,
714 , TO_CHAR(mln.d_attribute10, g_date_format_mask) lot_d_attribute10 -- Added for Bug 2795525,
715 , mln.n_attribute1 lot_n_attribute1
716 , mln.n_attribute2 lot_n_attribute2
717 , mln.n_attribute3 lot_n_attribute3
718 , mln.n_attribute4 lot_n_attribute4
719 , mln.n_attribute5 lot_n_attribute5
720 , mln.n_attribute6 lot_n_attribute6
721 , mln.n_attribute7 lot_n_attribute7
722 , mln.n_attribute8 lot_n_attribute8
723 , mln.n_attribute9 lot_n_attribute9
724 , mln.n_attribute10 lot_n_attribute10
725 , mln.territory_code lot_country_of_origin
726 , mln.grade_code lot_grade_code
727 , TO_CHAR(mln.origination_date, g_date_format_mask) lot_origination_date -- Added for Bug 2795525,
728 , mln.date_code lot_date_code
729 , TO_CHAR(mln.change_date, g_date_format_mask) lot_change_date -- Added for Bug 2795525,
730 , mln.age lot_age
731 , TO_CHAR(mln.retest_date, g_date_format_mask) lot_retest_date -- Added for Bug 2795525,
732 , TO_CHAR(mln.maturity_date, g_date_format_mask) lot_maturity_date -- Added for Bug 2795525,
733 , mln.item_size lot_item_size
734 , mln.color lot_color
735 , mln.volume lot_volume
736 , mln.volume_uom lot_volume_uom
737 , mln.place_of_origin lot_place_of_origin
738 , TO_CHAR(mln.best_by_date, g_date_format_mask) lot_best_by_date -- Added for Bug 2795525,
739 , mln.LENGTH lot_length
740 , mln.length_uom lot_length_uom
741 , mln.recycled_content lot_recycled_cont
742 , mln.thickness lot_thickness
743 , mln.thickness_uom lot_thickness_uom
744 , mln.width lot_width
745 , mln.width_uom lot_width_uom
746 , mln.curl_wrinkle_fold lot_curl
747 , mln.vendor_name lot_vendor
748 , lpn.subinventory_code subinventory_code
749 , inv_project.get_locsegs(milkfv.inventory_location_id, milkfv.organization_id) LOCATOR
750 FROM wip_entities wipent
751 , wip_discrete_jobs wipdj
752 , wip_repetitive_schedules wiprs
753 , wip_operations wipops
754 , wip_requirement_operations wipreqops
755 , mtl_item_locations mila
756 , mtl_item_locations milb
757 , mfg_lookups mfglkp1
758 , mfg_lookups mfglkp2
759 , mfg_lookups mfglkp3
760 , mfg_lookups mfglkp4
761 , mfg_lookups mfglkp5
762 , mfg_lookups mfglkp6
763 , bom_departments bomdep1
764 , bom_departments bomdep2
765 , wms_license_plate_numbers lpn
766 , wms_license_plate_numbers plpn
767 , mtl_system_items_vl msik1
768 , mtl_system_items_vl msik2
769 , mtl_system_items_vl msik3
770 , mtl_parameters mp
771 , wms_lpn_contents wlc
772 , mtl_lot_numbers mln
773 , cst_cost_groups ccg
774 , po_hazard_classes poh
775 , mtl_material_statuses_vl mmsv1
776 , mtl_item_locations milkfv
777 WHERE wipdj.wip_entity_id(+) = wipent.wip_entity_id
778 AND wipdj.organization_id(+) = wipent.organization_id
779 AND wiprs.wip_entity_id(+) = wipent.wip_entity_id
780 AND wiprs.organization_id(+) = wipent.organization_id
781 -- Bug 2781198. operation information missing for discrete jobs
782 -- should join to wipent
783 AND wipops.wip_entity_id(+) = wipent.wip_entity_id
784 AND wipops.organization_id(+) = wipent.organization_id
785 AND NVL(wipops.repetitive_schedule_id, -999) = NVL(wiprs.repetitive_schedule_id, NVL(wipops.repetitive_schedule_id, -999))
786 AND wipreqops.wip_entity_id = wipent.wip_entity_id
787 AND wipreqops.organization_id = wipent.organization_id
788 AND wipreqops.operation_seq_num = NVL(wipops.operation_seq_num, l_operation_seq_num)
789 AND NVL(wipreqops.repetitive_schedule_id, 999) = NVL(wipops.repetitive_schedule_id, NVL(wipreqops.repetitive_schedule_id, 999))
790 AND mfglkp1.lookup_code(+) = wipent.entity_type
791 AND mfglkp1.lookup_type(+) = 'WIP_ENTITY'
792 AND mfglkp2.lookup_code(+) = wipdj.job_type
793 AND mfglkp2.lookup_type(+) = 'WIP_DISCRETE_JOB'
794 AND mfglkp3.lookup_code(+) = wipdj.status_type
795 AND mfglkp3.lookup_type(+) = 'WIP_JOB_STATUS'
796 AND mfglkp4.lookup_code(+) = wiprs.status_type
797 AND mfglkp4.lookup_type(+) = 'WIP_JOB_STATUS'
798 AND mfglkp5.lookup_code(+) = wipops.backflush_flag
799 AND mfglkp5.lookup_type(+) = 'SYS_YES_NO'
800 AND mfglkp6.lookup_code(+) = wipreqops.wip_supply_type
801 AND mfglkp6.lookup_type(+) = 'WIP_SUPPLY'
802 AND mila.inventory_location_id(+) = wipdj.completion_locator_id
803 AND milb.inventory_location_id(+) = wipreqops.supply_locator_id
804 AND bomdep1.department_id(+) = wipops.department_id
805 AND bomdep2.department_id(+) = wipreqops.department_id
806 AND wipreqops.inventory_item_id = l_inventory_item_id
807 AND wipreqops.operation_seq_num = l_operation_seq_num
808 AND wipent.wip_entity_id = l_wip_entity_id
809 AND wipent.organization_id = l_organization_id
810 AND NVL(wiprs.repetitive_schedule_id, -99) = NVL(l_repetitive_schedule_id, NVL(wiprs.repetitive_schedule_id, -99))
811 AND lpn.lpn_id = l_lpn_id
812 AND lpn.parent_lpn_id = plpn.lpn_id(+)
813 AND wlc.parent_lpn_id(+) = l_lpn_id
814 AND wlc.inventory_item_id(+) = wipreqops.inventory_item_id
815 AND NVL(wlc.revision, NVL(l_revision, '$$$')) =
816 NVL(l_revision, NVL(wlc.revision, '$$$')) -- Bug 2440672 For Agilent -- Takes care of non-LPN Revision controlled items
817 AND NVL(wlc.lot_number, NVL(l_lot_number, '$$$')) =
818 NVL(l_lot_number, NVL(wlc.lot_number, '$$$')) -- Bug 2440672 For Agilent -- Takes care of non-LPN Lot controlled items
819 AND msik1.inventory_item_id(+) = lpn.inventory_item_id
820 AND msik1.organization_id(+) = lpn.organization_id
821 AND mp.organization_id = lpn.organization_id
822 AND milkfv.organization_id(+) = lpn.organization_id
823 AND milkfv.subinventory_code(+) = lpn.subinventory_code
824 AND milkfv.inventory_location_id(+) = lpn.locator_id
825 AND msik2.inventory_item_id(+) = NVL(wlc.inventory_item_id, l_inventory_item_id)
826 AND msik2.organization_id(+) = NVL(wlc.organization_id, l_organization_id)
827 AND mln.organization_id(+) = NVL(wlc.organization_id, l_organization_id)
828 AND mln.inventory_item_id(+) = NVL(wlc.inventory_item_id, l_inventory_item_id)
829 AND mln.lot_number(+) = NVL(wlc.lot_number, l_lot_number)
830 AND mmsv1.status_id(+) = mln.status_id
831 AND ccg.cost_group_id(+) = NVL(wlc.cost_group_id, l_cost_group_id)
832 AND poh.hazard_class_id(+) = msik2.hazard_class_id
833 AND msik3.inventory_item_id(+) = wipent.primary_item_id -- Bug 2440672 For Agilent -- Added Outer Join for non-Standard Jobs.
834 AND msik3.organization_id(+) = wipent.organization_id; -- Bug 2440672 For Agilent -- Added Outer Join for non-Standard Jobs.
835
836 l_selected_fields inv_label.label_field_variable_tbl_type;
837 l_selected_fields_count NUMBER;
838 l_label_format_id NUMBER := 0;
839 l_label_format VARCHAR2(100);
840 l_printer VARCHAR2(30);
841 l_content_item_data LONG;
842 l_content_rec_index NUMBER := 0;
843 l_return_status VARCHAR2(240);
844 l_error_message VARCHAR2(240);
845 l_msg_count NUMBER;
846 l_api_status VARCHAR2(240);
847 l_msg_data VARCHAR2(240);
848 i NUMBER;
849 l_id NUMBER;
850 l_label_index NUMBER;
851 l_label_request_id NUMBER;
852 --I cleanup, use l_prev_format_id to record the previous label format
853 l_prev_format_id NUMBER;
854 -- I cleanup, user l_prev_sub to record the previous subinventory
855 --so that get_printer is not called if the subinventory is the same
856 l_prev_sub VARCHAR2(30);
857 -- a list of columns that are selected for format
858 l_column_name_list LONG;
859 v_wip_content wip_content_cur%ROWTYPE;
860 BEGIN
861 l_debug := inv_label.l_debug;
862 -- Initialize return status as success
863 x_return_status := fnd_api.g_ret_sts_success;
864
865 IF (l_debug = 1) THEN
866 TRACE('**In PVT9: Wip Content label**');
867 TRACE(' Business_flow: ' || p_label_type_info.business_flow_code);
868 TRACE(' Transaction ID:' || p_transaction_id);
869 END IF;
870
871 -- Get
872 IF p_transaction_id IS NOT NULL THEN
873 -- txn driven
874 IF p_label_type_info.business_flow_code IN(28) THEN
875 -- WIP Pick Load
876 OPEN wip_lines_cur;
877
878 FETCH wip_lines_cur
879 INTO l_organization_id
880 , l_wip_entity_id
881 , l_repetitive_schedule_id
882 , l_inventory_item_id
883 , l_operation_seq_num
884 , l_lpn_id
885 , l_revision
886 , l_lot_number
887 , l_quantity
888 , l_uom
889 , l_cost_group_id
890 , l_subinventory
891 , l_project_name
892 , l_task_name;
893
894 IF wip_lines_cur%NOTFOUND THEN
895 IF (l_debug = 1) THEN
896 TRACE(' No record found for Wip Pick load , no WIP content label print');
897 END IF;
898
899 CLOSE wip_lines_cur;
900
901 RETURN;
902 END IF;
903 -- Bug 2781198 Label fails for lot controlled component
904 -- Should not close cursor here so that more lot will be retrieved later
905 --CLOSE wip_lines_cur;
906 ELSIF p_label_type_info.business_flow_code IN(29) THEN
907 -- WIP Pick Drop
908 OPEN wip_drop_lines_cur;
909
910 FETCH wip_drop_lines_cur
911 INTO l_organization_id
912 , l_wip_entity_id
913 , l_repetitive_schedule_id
914 , l_inventory_item_id
915 , l_operation_seq_num
916 , l_lpn_id
917 , l_revision
918 , l_lot_number
919 , l_quantity
920 , l_uom
921 , l_cost_group_id
922 , l_subinventory
923 , l_project_name
924 , l_task_name;
925
926 IF wip_drop_lines_cur%NOTFOUND THEN
927 IF (l_debug = 1) THEN
928 TRACE(' No record found for Wip Pick drop, no WIP content label print');
929 END IF;
930
931 CLOSE wip_drop_lines_cur;
932
933 RETURN;
934 END IF;
935 -- Bug 2781198 Label fails for lot controlled component
936 -- Should not close cursor here so that more lot will be retrieved later
937 --CLOSE wip_drop_lines_cur;
938 ELSIF p_label_type_info.business_flow_code = 37 THEN
939 -- Bug Number: 2829872
940 -- Manufacturing Cross Dock(37)
941 OPEN wip_cross_dock_cur;
942
943 FETCH wip_cross_dock_cur
944 INTO l_organization_id
945 , l_wip_entity_id
946 , l_repetitive_schedule_id
947 , l_inventory_item_id
948 , l_operation_seq_num
949 , l_lpn_id
950 , l_revision
951 , l_lot_number
952 , l_quantity
953 , l_uom
954 , l_cost_group_id
955 , l_subinventory
956 , l_project_name
957 , l_task_name;
958
959 IF wip_cross_dock_cur%NOTFOUND THEN
960 IF (l_debug = 1) THEN
961 TRACE(' No record found for Manufacturing cross dock, no WIP content label print');
962 END IF;
963
964 CLOSE wip_cross_dock_cur;
965
966 RETURN;
967 END IF;
968 ELSE
969 IF (l_debug = 1) THEN
970 TRACE(' Invalid business flow code ' || p_label_type_info.business_flow_code);
971 END IF;
972
973 RETURN;
974 END IF;
975 ELSE
976 -- On demand, get information from input_param
977 --hjogleka, Bug #6417575,Label Printing Support for WIP Move Transactions (12.1)
978 l_wip_entity_id := p_input_param.transaction_source_id;
979 l_organization_id := p_input_param.organization_id;
980 IF (l_debug = 1) THEN
981 TRACE('Manual mode, now available');
982 END IF;
983
984 END IF;
985
986 IF (l_debug = 1) THEN
987 TRACE(' Getting selected fields ');
988 END IF;
989
990 inv_label.get_variables_for_format(x_variables => l_selected_fields, x_variables_count => l_selected_fields_count
991 , p_format_id => p_label_type_info.default_format_id);
992
993 IF (l_selected_fields_count = 0)
994 OR(l_selected_fields.COUNT = 0) THEN
995 IF (l_debug = 1) THEN
996 TRACE('no fields defined for this format: ' || p_label_type_info.default_format_id || ',' || p_label_type_info.default_format_name);
997 END IF;
998 END IF;
999
1000 IF (l_debug = 1) THEN
1001 TRACE(' Found variable defined for this format, count = ' || l_selected_fields_count);
1002 END IF;
1003
1004 l_content_rec_index := 0;
1005
1006 IF (l_debug = 1) THEN
1007 TRACE('** in PVT9.get_variable_data ** , start ');
1008 END IF;
1009
1010 l_printer := p_label_type_info.default_printer;
1011 l_label_index := 1;
1012 l_prev_format_id := p_label_type_info.default_format_id;
1013 l_prev_sub := '####';
1014
1015 WHILE l_wip_entity_id IS NOT NULL LOOP
1016 l_content_item_data := '';
1017
1018 IF (l_debug = 1) THEN
1019 TRACE(
1020 ' New WIP Content label '
1021 || ' l_inventory_item_id='
1022 || l_inventory_item_id
1023 || ' l_operation_seq_num='
1024 || l_operation_seq_num
1025 || ' l_wip_entity_id='
1026 || l_wip_entity_id
1027 || ' l_organization_id='
1028 || l_organization_id
1029 || ' l_repetitive_schedule_id='
1030 || l_repetitive_schedule_id
1031 || ' l_lpn_id='
1032 || l_lpn_id
1033 );
1034 END IF;
1035
1036 --hjogleka, Bug #6417575,Label Printing Support for WIP Move Transactions (12.1)
1037 -- Choosing the cursor to fetch WIP Job Attributes based on transaction mode.
1038
1039 IF p_transaction_id IS NOT NULL THEN
1040 OPEN wip_content_cur;
1041 IF (l_debug = 1) THEN
1042 TRACE('Opening wip_content_cur');
1043 END IF;
1044 ELSE
1045 OPEN wip_discrete_cur;
1046 IF (l_debug = 1) THEN
1047 TRACE('Opening wip_discrete_cur');
1048 END IF;
1049
1050 -- Bug #6843199, 7031542, getting project name and task number
1051 -- for the job when the label is printed in manual mode.
1052 BEGIN
1053 SELECT pp.SEGMENT1
1054 , pt.TASK_NUMBER
1055 INTO l_project_name
1056 , l_task_name
1057 FROM wip_discrete_jobs wdj
1058 , pa_projects_all pp
1059 , pa_tasks pt
1060 WHERE wdj.wip_entity_id = l_wip_entity_id
1061 AND wdj.organization_id = l_organization_id
1062 AND pp.project_id(+) = wdj.project_id
1063 AND pt.task_id(+) = wdj.task_id;
1064
1065 EXCEPTION
1066 WHEN OTHERS THEN
1067 l_project_name := NULL;
1068 l_task_name := NULL;
1069 END;
1070 END IF;
1071
1072 --FOR v_wip_content IN wip_content_cur
1073 LOOP
1074
1075 IF p_transaction_id IS NOT NULL THEN
1076 FETCH wip_content_cur INTO v_wip_content;
1077 EXIT WHEN wip_content_cur%NOTFOUND;
1078 ELSE
1079 FETCH wip_discrete_cur INTO v_wip_content;
1080 EXIT WHEN wip_discrete_cur%NOTFOUND;
1081 END IF;
1082
1083 l_content_rec_index := l_content_rec_index + 1;
1084
1085 IF (l_debug = 1) THEN
1086 TRACE(' New Label ' || l_content_rec_index);
1087 END IF;
1088
1089
1090
1091 --R12 : RFID compliance project
1092 --Calling rules engine before calling to get printer
1093
1094 IF (l_debug = 1) THEN
1095 TRACE(
1096 'Apply Rules engine for format'
1097 || ',manual_format_id='
1098 || p_label_type_info.manual_format_id
1099 || ',manual_format_name='
1100 || p_label_type_info.manual_format_name
1101 );
1102 END IF;
1103
1104 /* insert a record into wms_label_requests entity to
1105 call the label rules engine to get appropriate label */
1106
1107 inv_label.get_format_with_rule(
1108 p_document_id => p_label_type_info.label_type_id
1109 , p_label_format_id => p_label_type_info.manual_format_id
1110 , p_organization_id => v_wip_content.organization_id
1111 , p_inventory_item_id => v_wip_content.inventory_item_id
1112 , p_lpn_id => l_lpn_id -- Bug 5509692.
1113 , p_last_update_date => SYSDATE
1114 , p_last_updated_by => fnd_global.user_id
1115 , p_creation_date => SYSDATE
1116 , p_created_by => fnd_global.user_id
1117 --, p_printer_name => l_printer-- Removed in R12: 4396558
1118 , p_business_flow_code => p_label_type_info.business_flow_code
1119 , x_return_status => l_return_status
1120 , x_label_format_id => l_label_format_id
1121 , x_label_format => l_label_format
1122 , x_label_request_id => l_label_request_id
1123 );
1124
1125 IF l_return_status <> 'S' THEN
1126 fnd_message.set_name('WMS', 'WMS_LABL_RULE_ENGINE_FAILED');
1127 fnd_msg_pub.ADD;
1128 l_label_format := p_label_type_info.default_format_id;
1129 l_label_format_id := p_label_type_info.default_format_name;
1130 END IF;
1131
1132 IF (l_debug = 1) THEN
1133 TRACE('did apply label ' || l_label_format || ',' || l_label_format_id || ',req_id ' || l_label_request_id);
1134 END IF;
1135
1136 IF p_label_type_info.manual_format_id IS NOT NULL THEN
1137 l_label_format_id := p_label_type_info.manual_format_id;
1138 l_label_format := p_label_type_info.manual_format_name;
1139
1140 IF (l_debug = 1) THEN
1141 TRACE('Manual format passed in:' || l_label_format_id || ',' || l_label_format);
1142 END IF;
1143 END IF;
1144
1145
1146 IF (l_debug = 1) THEN
1147 TRACE(
1148 ' Getting printer, manual_printer='
1149 || p_label_type_info.manual_printer
1150 || ',sub='
1151 || l_subinventory
1152 || ',default printer='
1153 || p_label_type_info.default_printer
1154 );
1155 END IF;
1156
1157 -- IF clause Added for Add format/printer for manual request
1158 IF p_label_type_info.manual_printer IS NULL THEN
1159 IF (l_subinventory IS NOT NULL)
1160 AND(l_subinventory <> l_prev_sub) THEN
1161 IF (l_debug = 1) THEN
1162 TRACE('getting printer with org, sub' || l_organization_id || ',' || l_subinventory);
1163 END IF;
1164
1165 BEGIN
1166 wsh_report_printers_pvt.get_printer(
1167 p_concurrent_program_id => p_label_type_info.label_type_id
1168 , p_user_id => fnd_global.user_id
1169 , p_responsibility_id => fnd_global.resp_id
1170 , p_application_id => fnd_global.resp_appl_id
1171 , p_organization_id => l_organization_id
1172 , p_format_id =>l_label_format_id --added in r12 RFID 4396558
1173 , p_zone => l_subinventory
1174 , x_printer => l_printer
1175 , x_api_status => l_api_status
1176 , x_error_message => l_error_message
1177 );
1178
1179 IF l_api_status <> 'S' THEN
1180 IF (l_debug = 1) THEN
1181 TRACE('Error in calling get_printer, set printer as default printer, err_msg:' || l_error_message);
1182 END IF;
1183
1184 l_printer := p_label_type_info.default_printer;
1185 END IF;
1186 EXCEPTION
1187 WHEN OTHERS THEN
1188 l_printer := p_label_type_info.default_printer;
1189 END;
1190
1191 l_prev_sub := l_subinventory;
1192
1193 END IF;
1194 ELSE
1195 IF (l_debug = 1) THEN
1196 TRACE('Set printer as Manual Printer passed in:' || p_label_type_info.manual_printer);
1197 END IF;
1198
1199 l_printer := p_label_type_info.manual_printer;
1200 END IF;
1201
1202
1203
1204
1205 IF (l_label_format_id IS NOT NULL) THEN
1206 -- Derive the fields for the format either passed in or derived via the rules engine.
1207 IF l_label_format_id <> NVL(l_prev_format_id, -999) THEN
1208 IF (l_debug = 1) THEN
1209 TRACE(' Getting variables for new format ' || l_label_format);
1210 END IF;
1211
1212 inv_label.get_variables_for_format(x_variables => l_selected_fields, x_variables_count => l_selected_fields_count
1213 , p_format_id => l_label_format_id);
1214 l_prev_format_id := l_label_format_id;
1215
1216 IF (l_selected_fields_count = 0)
1217 OR(l_selected_fields.COUNT = 0) THEN
1218 IF (l_debug = 1) THEN
1219 TRACE('no fields defined for this format: ' || l_label_format || ',' || l_label_format_id);
1220 END IF;
1221
1222 GOTO nextlabel;
1223 END IF;
1224
1225 IF (l_debug = 1) THEN
1226 TRACE(' Found selected_fields for format ' || l_label_format || ', num=' || l_selected_fields_count);
1227 END IF;
1228 END IF;
1229 ELSE
1230 IF (l_debug = 1) THEN
1231 TRACE('No format exists for this label, goto nextlabel');
1232 END IF;
1233
1234 GOTO nextlabel;
1235 END IF;
1236
1237 /* variable header */
1238 l_content_item_data := l_content_item_data || label_b;
1239
1240 IF l_label_format <> NVL(p_label_type_info.default_format_name, '@@@') THEN
1241 l_content_item_data := l_content_item_data || ' _FORMAT="' || NVL(p_label_type_info.manual_format_name, l_label_format) || '"';
1242 END IF;
1243
1244 IF (l_printer IS NOT NULL)
1245 AND(l_printer <> NVL(p_label_type_info.default_printer, '###')) THEN
1246 l_content_item_data := l_content_item_data || ' _PRINTERNAME="' || l_printer || '"';
1247 END IF;
1248
1249 l_content_item_data := l_content_item_data || tag_e;
1250
1251 IF (l_debug = 1) THEN
1252 TRACE('Starting assign variables, ');
1253 END IF;
1254
1255 l_column_name_list := 'Set variables for ';
1256
1257 /* Modified for Bug 4072474 -start*/
1258 l_custom_sql_ret_status := FND_API.G_RET_STS_SUCCESS;
1259 /* Modified for Bug 4072474 -End*/
1260
1261 -- Fix for bug: 4179593 Start
1262 l_CustSqlWarnFlagSet := FALSE;
1263 l_CustSqlErrFlagSet := FALSE;
1264 l_CustSqlWarnMsg := NULL;
1265 l_CustSqlErrMsg := NULL;
1266 -- Fix for bug: 4179593 End
1267
1268 /* Loop for each selected fields, find the columns and write into the XML_content*/
1269 FOR i IN 1 .. l_selected_fields.COUNT LOOP
1270 IF (l_debug = 1) THEN
1271 l_column_name_list := l_column_name_list || ',' || l_selected_fields(i).column_name;
1272 END IF;
1273
1274 ---------------------------------------------------------------------------------------------
1275 -- Project: 'Custom Labels' (A 11i10+ Project) |
1276 -- Author: Dinesh ([email protected]) |
1277 -- Change Description: |
1278 -- The check (SQL_STMT <> NULL and COLUMN_NAME = NULL) implies that the field is a |
1279 -- Custom SQL based field. Handle it appropriately. |
1280 ---------------------------------------------------------------------------------------------
1281 IF (l_selected_fields(i).SQL_STMT IS NOT NULL AND l_selected_fields(i).column_name = 'sql_stmt') THEN
1282 IF (l_debug = 1) THEN
1283 trace('Custom Labels Trace [INVLAP9B.pls]: ------------------------- REPORT BEGIN-------------------------------------');
1284 trace('Custom Labels Trace [INVLAP9B.pls]: LABEL_FIELD_ID : ' || l_selected_fields(i).label_field_id);
1285 trace('Custom Labels Trace [INVLAP9B.pls]: FIELD_VARIABLE_NAME : ' || l_selected_fields(i).variable_name);
1286 trace('Custom Labels Trace [INVLAP9B.pls]: COLUMN_NAME : ' || l_selected_fields(i).column_name);
1287 trace('Custom Labels Trace [INVLAP9B.pls]: SQL_STMT : ' || l_selected_fields(i).sql_stmt);
1288 END IF;
1289 l_sql_stmt := l_selected_fields(i).sql_stmt;
1290 IF (l_debug = 1) THEN
1291 trace('Custom Labels Trace [INVLAP9B.pls]: l_sql_stmt BEFORE REQUEST_ID Filter Concatenation: ' || l_sql_stmt);
1292 END IF;
1293 l_sql_stmt := l_sql_stmt || ' AND WLR.LABEL_REQUEST_ID = :REQUEST_ID';
1294 IF (l_debug = 1) THEN
1295 trace('Custom Labels Trace [INVLAP9B.pls]: l_sql_stmt AFTER REQUEST_ID Filter Concatenation: ' || l_sql_stmt);
1296
1297 END IF;
1298 BEGIN
1299 IF (l_debug = 1) THEN
1300 trace('Custom Labels Trace [INVLAP9B.pls]: At Breadcrumb 1');
1301 trace('Custom Labels Trace [INVLAP9B.pls]: LABEL_REQUEST_ID : ' || l_label_request_id);
1302 END IF;
1303 OPEN c_sql_stmt FOR l_sql_stmt using l_label_request_id;
1304 LOOP
1305 FETCH c_sql_stmt INTO l_sql_stmt_result;
1306 EXIT WHEN c_sql_stmt%notfound OR c_sql_stmt%rowcount >=2;
1307 END LOOP;
1308
1309 IF (c_sql_stmt%rowcount=1 AND l_sql_stmt_result IS NULL) THEN
1310 x_return_status := FND_API.G_RET_STS_SUCCESS;
1311 l_custom_sql_ret_status := INV_LABEL.G_WARNING;
1312
1313 fnd_message.set_name('WMS','WMS_CS_NULL_VALUE_RETURNED');
1314 fnd_msg_pub.ADD;
1315 -- Fix for bug: 4179593 Start
1316 --fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => l_custom_sql_ret_msg);
1317 l_custom_sql_ret_msg := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_last, p_encoded => fnd_api.g_false);
1318 l_CustSqlWarnMsg := l_custom_sql_ret_msg;
1319 l_CustSqlWarnFlagSet := TRUE;
1320 -- Fix for bug: 4179593 End
1321 IF (l_debug = 1) THEN
1322 trace('Custom Labels Trace [INVLAP9B.pls]: At Breadcrumb 2');
1323 trace('Custom Labels Trace [INVLAP9B.pls]: l_sql_stmt_result is: ' || l_sql_stmt_result);
1324 trace('Custom Labels Trace [INVLAP9B.pls]: WARNING: NULL value returned by the custom SQL Query.');
1325 trace('Custom Labels Trace [INVLAP9B.pls]: l_custom_sql_ret_status is set to : ' || l_custom_sql_ret_status );
1326 END IF;
1327 ELSIF c_sql_stmt%rowcount=0 THEN
1328 IF (l_debug = 1) THEN
1329 trace('Custom Labels Trace [INVLAP9B.pls]: At Breadcrumb 3');
1330 trace('Custom Labels Trace [INVLAP9B.pls]: WARNING: No row returned by the Custom SQL query');
1331 END IF;
1332 x_return_status := FND_API.G_RET_STS_SUCCESS;
1333 l_custom_sql_ret_status := INV_LABEL.G_WARNING;
1334
1335 fnd_message.set_name('WMS','WMS_CS_NO_DATA_FOUND');
1336 fnd_msg_pub.ADD;
1337 /* Replaced following statement for Bug 4207625: Anupam Jain*/
1338 /*fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => l_custom_sql_ret_status);*/
1339 -- Fix for bug: 4179593 Start
1340 --fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => l_custom_sql_ret_msg);
1341 l_custom_sql_ret_msg := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_last, p_encoded => fnd_api.g_false);
1342 l_CustSqlWarnMsg := l_custom_sql_ret_msg;
1343 l_CustSqlWarnFlagSet := TRUE;
1344 -- Fix for bug: 4179593 End
1345 ELSIF c_sql_stmt%rowcount>=2 THEN
1346 IF (l_debug = 1) THEN
1347 trace('Custom Labels Trace [INVLAP9B.pls]: At Breadcrumb 4');
1348 trace('Custom Labels Trace [INVLAP9B.pls]: ERROR: Multiple values returned by the Custom SQL query');
1349 END IF;
1350 l_sql_stmt_result := NULL;
1351 x_return_status := FND_API.G_RET_STS_SUCCESS;
1352 l_custom_sql_ret_status := FND_API.G_RET_STS_ERROR;
1353 fnd_message.set_name('WMS','WMS_CS_MULTIPLE_VALUES_RETURN');
1354 fnd_msg_pub.ADD;
1355 /* Replaced following statement for Bug 4207625: Anupam Jain*/
1356 /*fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => l_custom_sql_ret_status);*/
1357 -- Fix for bug: 4179593 Start
1358 --fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => l_custom_sql_ret_msg);
1359 l_custom_sql_ret_msg := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_last, p_encoded => fnd_api.g_false);
1360 l_CustSqlErrMsg := l_custom_sql_ret_msg;
1361 l_CustSqlErrFlagSet := TRUE;
1362 -- Fix for bug: 4179593 End
1363 END IF;
1364 IF (c_sql_stmt%ISOPEN) THEN
1365 CLOSE c_sql_stmt;
1366 END IF;
1367 EXCEPTION
1368 WHEN OTHERS THEN
1369 IF (c_sql_stmt%ISOPEN) THEN
1370 CLOSE c_sql_stmt;
1371 END IF;
1372 IF (l_debug = 1) THEN
1373 trace('Custom Labels Trace [INVLAP9B.pls]: At Breadcrumb 5');
1374 trace('Custom Labels Trace [INVLAP9B.pls]: Unexpected Error has occured in GET_VARIABLES_DATA');
1375 END IF;
1376 x_return_status := FND_API.G_RET_STS_ERROR;
1377 fnd_message.set_name('WMS','WMS_CS_WRONG_SQL_CONSTRUCT');
1378 fnd_msg_pub.ADD;
1379 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
1380 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1381 END;
1382 IF (l_debug = 1) THEN
1383 trace('Custom Labels Trace [INVLAP9B.pls]: At Breadcrumb 6');
1384 trace('Custom Labels Trace [INVLAP9B.pls]: Before assigning it to l_content_item_data');
1385 END IF;
1386 l_content_item_data := l_content_item_data
1387 || variable_b
1388 || l_selected_fields(i).variable_name
1389 || '">'
1390 || l_sql_stmt_result
1391 || variable_e;
1392 l_sql_stmt_result := NULL;
1393 l_sql_stmt := NULL;
1394 IF (l_debug = 1) THEN
1395 trace('Custom Labels Trace [INVLAP9B.pls]: At Breadcrumb 7');
1396 trace('Custom Labels Trace [INVLAP9B.pls]: After assigning it to l_content_item_data');
1397 trace('Custom Labels Trace [INVLAP9B.pls]: --------------------------REPORT END-------------------------------------');
1398 END IF;
1399 ------------------------End of this change for Custom Labels project code--------------------
1400 ELSIF LOWER(l_selected_fields(i).column_name) = 'current_date' THEN
1401 l_content_item_data :=
1402 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || inv_label.g_date || variable_e;
1403 ELSIF LOWER(l_selected_fields(i).column_name) = 'current_time' THEN
1404 l_content_item_data :=
1405 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || inv_label.g_time || variable_e;
1406 ELSIF LOWER(l_selected_fields(i).column_name) = 'request_user' THEN
1407 l_content_item_data :=
1408 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || inv_label.g_user || variable_e;
1409 ELSIF LOWER(l_selected_fields(i).column_name) = 'job_schedule_type' THEN
1410 l_content_item_data :=
1411 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.job_schedule_type
1412 || variable_e;
1413 ELSIF LOWER(l_selected_fields(i).column_name) = 'job_schedule_name' THEN
1414 l_content_item_data :=
1415 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.job_schedule_name
1416 || variable_e;
1417 ELSIF LOWER(l_selected_fields(i).column_name) = 'job_schedule_description' THEN
1418 l_content_item_data :=
1419 l_content_item_data
1420 || variable_b
1421 || l_selected_fields(i).variable_name
1422 || '">'
1423 || v_wip_content.job_schedule_description
1424 || variable_e;
1425 -- Bug 2112635. A data field for the Assembly of the Job on the WIP Contents Label.
1426 ELSIF LOWER(l_selected_fields(i).column_name) = 'job_assembly' THEN
1427 l_content_item_data :=
1428 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.job_assembly || variable_e;
1429 ELSIF LOWER(l_selected_fields(i).column_name) = 'assembly_description' THEN
1430 l_content_item_data :=
1431 l_content_item_data
1432 || variable_b
1433 || l_selected_fields(i).variable_name
1434 || '">'
1435 || v_wip_content.assembly_description
1436 || variable_e;
1437 /* Discrete Jobs */
1438 ELSIF LOWER(l_selected_fields(i).column_name) = 'job_accounting_class' THEN
1439 l_content_item_data :=
1440 l_content_item_data
1441 || variable_b
1442 || l_selected_fields(i).variable_name
1443 || '">'
1444 || v_wip_content.job_accounting_class
1445 || variable_e;
1446 ELSIF LOWER(l_selected_fields(i).column_name) = 'job_attribute_category' THEN
1447 l_content_item_data :=
1448 l_content_item_data
1449 || variable_b
1450 || l_selected_fields(i).variable_name
1451 || '">'
1452 || v_wip_content.job_attribute_category
1453 || variable_e;
1454 ELSIF LOWER(l_selected_fields(i).column_name) = 'job_attribute1' THEN
1455 l_content_item_data :=
1456 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.job_attribute1 || variable_e;
1457 ELSIF LOWER(l_selected_fields(i).column_name) = 'job_attribute2' THEN
1458 l_content_item_data :=
1459 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.job_attribute2 || variable_e;
1460 ELSIF LOWER(l_selected_fields(i).column_name) = 'job_attribute3' THEN
1461 l_content_item_data :=
1462 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.job_attribute3 || variable_e;
1463 ELSIF LOWER(l_selected_fields(i).column_name) = 'job_attribute4' THEN
1464 l_content_item_data :=
1465 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.job_attribute4 || variable_e;
1466 ELSIF LOWER(l_selected_fields(i).column_name) = 'job_attribute5' THEN
1467 l_content_item_data :=
1468 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.job_attribute5 || variable_e;
1469 ELSIF LOWER(l_selected_fields(i).column_name) = 'job_attribute6' THEN
1470 l_content_item_data :=
1471 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.job_attribute6 || variable_e;
1472 ELSIF LOWER(l_selected_fields(i).column_name) = 'job_attribute7' THEN
1473 l_content_item_data :=
1474 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.job_attribute7 || variable_e;
1475 ELSIF LOWER(l_selected_fields(i).column_name) = 'job_attribute8' THEN
1476 l_content_item_data :=
1477 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.job_attribute8 || variable_e;
1478 ELSIF LOWER(l_selected_fields(i).column_name) = 'job_attribute9' THEN
1479 l_content_item_data :=
1480 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.job_attribute9 || variable_e;
1481 ELSIF LOWER(l_selected_fields(i).column_name) = 'job_attribute10' THEN
1482 l_content_item_data :=
1483 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.job_attribute10
1484 || variable_e;
1485 ELSIF LOWER(l_selected_fields(i).column_name) = 'job_attribute11' THEN
1486 l_content_item_data :=
1487 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.job_attribute11
1488 || variable_e;
1489 ELSIF LOWER(l_selected_fields(i).column_name) = 'job_attribute12' THEN
1490 l_content_item_data :=
1491 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.job_attribute12
1492 || variable_e;
1493 ELSIF LOWER(l_selected_fields(i).column_name) = 'job_attribute13' THEN
1494 l_content_item_data :=
1495 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.job_attribute13
1496 || variable_e;
1497 ELSIF LOWER(l_selected_fields(i).column_name) = 'job_attribute14' THEN
1498 l_content_item_data :=
1499 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.job_attribute14
1500 || variable_e;
1501 ELSIF LOWER(l_selected_fields(i).column_name) = 'job_attribute15' THEN
1502 l_content_item_data :=
1503 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.job_attribute15
1504 || variable_e;
1505 ELSIF LOWER(l_selected_fields(i).column_name) = 'job_bom_revision' THEN
1506 l_content_item_data :=
1507 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.job_bom_revision
1508 || variable_e;
1509 ELSIF LOWER(l_selected_fields(i).column_name) = 'job_completion_locator' THEN
1510 l_content_item_data :=
1511 l_content_item_data
1512 || variable_b
1513 || l_selected_fields(i).variable_name
1514 || '">'
1515 || v_wip_content.job_completion_locator
1516 || variable_e;
1517 ELSIF LOWER(l_selected_fields(i).column_name) = 'job_completion_subinventory' THEN
1518 l_content_item_data :=
1519 l_content_item_data
1520 || variable_b
1521 || l_selected_fields(i).variable_name
1522 || '">'
1523 || v_wip_content.job_completion_subinventory
1524 || variable_e;
1525 ELSIF LOWER(l_selected_fields(i).column_name) = 'job_demand_class' THEN
1526 l_content_item_data :=
1527 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.job_demand_class
1528 || variable_e;
1529 ELSIF LOWER(l_selected_fields(i).column_name) = 'job_due_date' THEN
1530 l_content_item_data :=
1531 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.job_due_date || variable_e;
1532 ELSIF LOWER(l_selected_fields(i).column_name) = 'job_job_type' THEN
1533 l_content_item_data :=
1534 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.job_job_type || variable_e;
1535 ELSIF LOWER(l_selected_fields(i).column_name) = 'job_net_quantity' THEN
1536 l_content_item_data :=
1537 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.job_net_quantity
1538 || variable_e;
1539 ELSIF LOWER(l_selected_fields(i).column_name) = 'job_priority' THEN
1540 l_content_item_data :=
1541 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.job_priority || variable_e;
1542 ELSIF LOWER(l_selected_fields(i).column_name) = 'job_release_date' THEN
1543 l_content_item_data :=
1544 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.job_release_date
1545 || variable_e;
1546 ELSIF LOWER(l_selected_fields(i).column_name) = 'job_routing_revision' THEN
1547 l_content_item_data :=
1548 l_content_item_data
1549 || variable_b
1550 || l_selected_fields(i).variable_name
1551 || '">'
1552 || v_wip_content.job_routing_revision
1553 || variable_e;
1554 ELSIF LOWER(l_selected_fields(i).column_name) = 'job_schedule_completion_date' THEN
1555 l_content_item_data :=
1556 l_content_item_data
1557 || variable_b
1558 || l_selected_fields(i).variable_name
1559 || '">'
1560 || v_wip_content.job_schedule_completion_date
1561 || variable_e;
1562 ELSIF LOWER(l_selected_fields(i).column_name) = 'job_start_date' THEN
1563 l_content_item_data :=
1564 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.job_start_date || variable_e;
1565 ELSIF LOWER(l_selected_fields(i).column_name) = 'job_start_quantity' THEN
1566 l_content_item_data :=
1567 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.job_start_quantity
1568 || variable_e;
1569 ELSIF LOWER(l_selected_fields(i).column_name) = 'job_status_type' THEN
1570 l_content_item_data :=
1571 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.job_status_type
1572 || variable_e;
1573 /* Repetive Schedule */
1574 ELSIF LOWER(l_selected_fields(i).column_name) = 'repet_sched_alternate_bom' THEN
1575 l_content_item_data :=
1576 l_content_item_data
1577 || variable_b
1578 || l_selected_fields(i).variable_name
1579 || '">'
1580 || v_wip_content.repet_sched_alternate_bom
1581 || variable_e;
1582 ELSIF LOWER(l_selected_fields(i).column_name) = 'repet_sched_alternate_routing' THEN
1583 l_content_item_data :=
1584 l_content_item_data
1585 || variable_b
1586 || l_selected_fields(i).variable_name
1587 || '">'
1588 || v_wip_content.repet_sched_alternate_routing
1589 || variable_e;
1590 ELSIF LOWER(l_selected_fields(i).column_name) = 'repet_sched_attribute_category' THEN
1591 l_content_item_data :=
1592 l_content_item_data
1593 || variable_b
1594 || l_selected_fields(i).variable_name
1595 || '">'
1596 || v_wip_content.repet_sched_attribute_category
1597 || variable_e;
1598 ELSIF LOWER(l_selected_fields(i).column_name) = 'repet_sched_attribute1' THEN
1599 l_content_item_data :=
1600 l_content_item_data
1601 || variable_b
1602 || l_selected_fields(i).variable_name
1603 || '">'
1604 || v_wip_content.repet_sched_attribute1
1605 || variable_e;
1606 ELSIF LOWER(l_selected_fields(i).column_name) = 'repet_sched_attribute2' THEN
1607 l_content_item_data :=
1608 l_content_item_data
1609 || variable_b
1610 || l_selected_fields(i).variable_name
1611 || '">'
1612 || v_wip_content.repet_sched_attribute2
1613 || variable_e;
1614 ELSIF LOWER(l_selected_fields(i).column_name) = 'repet_sched_attribute3' THEN
1615 l_content_item_data :=
1616 l_content_item_data
1617 || variable_b
1618 || l_selected_fields(i).variable_name
1619 || '">'
1620 || v_wip_content.repet_sched_attribute3
1621 || variable_e;
1622 ELSIF LOWER(l_selected_fields(i).column_name) = 'repet_sched_attribute4' THEN
1623 l_content_item_data :=
1624 l_content_item_data
1625 || variable_b
1626 || l_selected_fields(i).variable_name
1627 || '">'
1628 || v_wip_content.repet_sched_attribute4
1629 || variable_e;
1630 ELSIF LOWER(l_selected_fields(i).column_name) = 'repet_sched_attribute5' THEN
1631 l_content_item_data :=
1632 l_content_item_data
1633 || variable_b
1634 || l_selected_fields(i).variable_name
1635 || '">'
1636 || v_wip_content.repet_sched_attribute5
1637 || variable_e;
1638 ELSIF LOWER(l_selected_fields(i).column_name) = 'repet_sched_attribute6' THEN
1639 l_content_item_data :=
1640 l_content_item_data
1641 || variable_b
1642 || l_selected_fields(i).variable_name
1643 || '">'
1644 || v_wip_content.repet_sched_attribute6
1645 || variable_e;
1646 ELSIF LOWER(l_selected_fields(i).column_name) = 'repet_sched_attribute7' THEN
1647 l_content_item_data :=
1648 l_content_item_data
1649 || variable_b
1650 || l_selected_fields(i).variable_name
1651 || '">'
1652 || v_wip_content.repet_sched_attribute7
1653 || variable_e;
1654 ELSIF LOWER(l_selected_fields(i).column_name) = 'repet_sched_attribute8' THEN
1655 l_content_item_data :=
1656 l_content_item_data
1657 || variable_b
1658 || l_selected_fields(i).variable_name
1659 || '">'
1660 || v_wip_content.repet_sched_attribute8
1661 || variable_e;
1662 ELSIF LOWER(l_selected_fields(i).column_name) = 'repet_sched_attribute9' THEN
1663 l_content_item_data :=
1664 l_content_item_data
1665 || variable_b
1666 || l_selected_fields(i).variable_name
1667 || '">'
1668 || v_wip_content.repet_sched_attribute9
1669 || variable_e;
1670 ELSIF LOWER(l_selected_fields(i).column_name) = 'repet_sched_attribute10' THEN
1671 l_content_item_data :=
1672 l_content_item_data
1673 || variable_b
1674 || l_selected_fields(i).variable_name
1675 || '">'
1676 || v_wip_content.repet_sched_attribute10
1677 || variable_e;
1678 ELSIF LOWER(l_selected_fields(i).column_name) = 'repet_sched_attribute11' THEN
1679 l_content_item_data :=
1680 l_content_item_data
1681 || variable_b
1682 || l_selected_fields(i).variable_name
1683 || '">'
1684 || v_wip_content.repet_sched_attribute11
1685 || variable_e;
1686 ELSIF LOWER(l_selected_fields(i).column_name) = 'repet_sched_attribute12' THEN
1687 l_content_item_data :=
1688 l_content_item_data
1689 || variable_b
1690 || l_selected_fields(i).variable_name
1691 || '">'
1692 || v_wip_content.repet_sched_attribute12
1693 || variable_e;
1694 ELSIF LOWER(l_selected_fields(i).column_name) = 'repet_sched_attribute13' THEN
1695 l_content_item_data :=
1696 l_content_item_data
1697 || variable_b
1698 || l_selected_fields(i).variable_name
1699 || '">'
1700 || v_wip_content.repet_sched_attribute13
1701 || variable_e;
1702 ELSIF LOWER(l_selected_fields(i).column_name) = 'repet_sched_attribute14' THEN
1703 l_content_item_data :=
1704 l_content_item_data
1705 || variable_b
1706 || l_selected_fields(i).variable_name
1707 || '">'
1708 || v_wip_content.repet_sched_attribute14
1709 || variable_e;
1710 ELSIF LOWER(l_selected_fields(i).column_name) = 'repet_sched_attribute15' THEN
1711 l_content_item_data :=
1712 l_content_item_data
1713 || variable_b
1714 || l_selected_fields(i).variable_name
1715 || '">'
1716 || v_wip_content.repet_sched_attribute15
1717 || variable_e;
1718 ELSIF LOWER(l_selected_fields(i).column_name) = 'repet_sched_bom_revision' THEN
1719 l_content_item_data :=
1720 l_content_item_data
1721 || variable_b
1722 || l_selected_fields(i).variable_name
1723 || '">'
1724 || v_wip_content.repet_sched_bom_revision
1725 || variable_e;
1726 ELSIF LOWER(l_selected_fields(i).column_name) = 'repet_sched_daily_quantity' THEN
1727 l_content_item_data :=
1728 l_content_item_data
1729 || variable_b
1730 || l_selected_fields(i).variable_name
1731 || '">'
1732 || v_wip_content.repet_sched_daily_quantity
1733 || variable_e;
1734 ELSIF LOWER(l_selected_fields(i).column_name) = 'repet_sched_demand_class' THEN
1735 l_content_item_data :=
1736 l_content_item_data
1737 || variable_b
1738 || l_selected_fields(i).variable_name
1739 || '">'
1740 || v_wip_content.repet_sched_demand_class
1741 || variable_e;
1742 ELSIF LOWER(l_selected_fields(i).column_name) = 'repet_sched_description' THEN
1743 l_content_item_data :=
1744 l_content_item_data
1745 || variable_b
1746 || l_selected_fields(i).variable_name
1747 || '">'
1748 || v_wip_content.repet_sched_description
1749 || variable_e;
1750 ELSIF LOWER(l_selected_fields(i).column_name) = 'repet_sched_firm_flag' THEN
1751 l_content_item_data :=
1752 l_content_item_data
1753 || variable_b
1754 || l_selected_fields(i).variable_name
1755 || '">'
1756 || v_wip_content.repet_sched_firm_flag
1757 || variable_e;
1758 ELSIF LOWER(l_selected_fields(i).column_name) = 'repet_sched_first_comple_dt' THEN
1759 l_content_item_data :=
1760 l_content_item_data
1761 || variable_b
1762 || l_selected_fields(i).variable_name
1763 || '">'
1764 || v_wip_content.repet_sched_first_comple_dt
1765 || variable_e;
1766 ELSIF LOWER(l_selected_fields(i).column_name) = 'repet_sched_first_start_date' THEN
1767 l_content_item_data :=
1768 l_content_item_data
1769 || variable_b
1770 || l_selected_fields(i).variable_name
1771 || '">'
1772 || v_wip_content.repet_sched_first_start_date
1773 || variable_e;
1774 ELSIF LOWER(l_selected_fields(i).column_name) = 'repet_sched_last_complet_dt' THEN
1775 l_content_item_data :=
1776 l_content_item_data
1777 || variable_b
1778 || l_selected_fields(i).variable_name
1779 || '">'
1780 || v_wip_content.repet_sched_last_complet_dt
1781 || variable_e;
1782 ELSIF LOWER(l_selected_fields(i).column_name) = 'repet_sched_last_start_date' THEN
1783 l_content_item_data :=
1784 l_content_item_data
1785 || variable_b
1786 || l_selected_fields(i).variable_name
1787 || '">'
1788 || v_wip_content.repet_sched_last_start_date
1789 || variable_e;
1790 ELSIF LOWER(l_selected_fields(i).column_name) = 'repet_sched_processing_date' THEN
1791 l_content_item_data :=
1792 l_content_item_data
1793 || variable_b
1794 || l_selected_fields(i).variable_name
1795 || '">'
1796 || v_wip_content.repet_sched_processing_date
1797 || variable_e;
1798 ELSIF LOWER(l_selected_fields(i).column_name) = 'repet_sched_release_date' THEN
1799 l_content_item_data :=
1800 l_content_item_data
1801 || variable_b
1802 || l_selected_fields(i).variable_name
1803 || '">'
1804 || v_wip_content.repet_sched_release_date
1805 || variable_e;
1806 ELSIF LOWER(l_selected_fields(i).column_name) = 'repet_sched_routing_revision' THEN
1807 l_content_item_data :=
1808 l_content_item_data
1809 || variable_b
1810 || l_selected_fields(i).variable_name
1811 || '">'
1812 || v_wip_content.repet_sched_routing_revision
1813 || variable_e;
1814 ELSIF LOWER(l_selected_fields(i).column_name) = 'repet_sched_schedule_status' THEN
1815 l_content_item_data :=
1816 l_content_item_data
1817 || variable_b
1818 || l_selected_fields(i).variable_name
1819 || '">'
1820 || v_wip_content.repet_sched_schedule_status
1821 || variable_e;
1822 /*Operations*/
1823 ELSIF LOWER(l_selected_fields(i).column_name) = 'operations_attribute_category' THEN
1824 l_content_item_data :=
1825 l_content_item_data
1826 || variable_b
1827 || l_selected_fields(i).variable_name
1828 || '">'
1829 || v_wip_content.operations_attribute_category
1830 || variable_e;
1831 ELSIF LOWER(l_selected_fields(i).column_name) = 'operations_attribute1' THEN
1832 l_content_item_data :=
1833 l_content_item_data
1834 || variable_b
1835 || l_selected_fields(i).variable_name
1836 || '">'
1837 || v_wip_content.operations_attribute1
1838 || variable_e;
1839 ELSIF LOWER(l_selected_fields(i).column_name) = 'operations_attribute2' THEN
1840 l_content_item_data :=
1841 l_content_item_data
1842 || variable_b
1843 || l_selected_fields(i).variable_name
1844 || '">'
1845 || v_wip_content.operations_attribute2
1846 || variable_e;
1847 ELSIF LOWER(l_selected_fields(i).column_name) = 'operations_attribute3' THEN
1848 l_content_item_data :=
1849 l_content_item_data
1850 || variable_b
1851 || l_selected_fields(i).variable_name
1852 || '">'
1853 || v_wip_content.operations_attribute3
1854 || variable_e;
1855 ELSIF LOWER(l_selected_fields(i).column_name) = 'operations_attribute4' THEN
1856 l_content_item_data :=
1857 l_content_item_data
1858 || variable_b
1859 || l_selected_fields(i).variable_name
1860 || '">'
1861 || v_wip_content.operations_attribute4
1862 || variable_e;
1863 ELSIF LOWER(l_selected_fields(i).column_name) = 'operations_attribute5' THEN
1864 l_content_item_data :=
1865 l_content_item_data
1866 || variable_b
1867 || l_selected_fields(i).variable_name
1868 || '">'
1869 || v_wip_content.operations_attribute5
1870 || variable_e;
1871 ELSIF LOWER(l_selected_fields(i).column_name) = 'operations_attribute6' THEN
1872 l_content_item_data :=
1873 l_content_item_data
1874 || variable_b
1875 || l_selected_fields(i).variable_name
1876 || '">'
1877 || v_wip_content.operations_attribute6
1878 || variable_e;
1879 ELSIF LOWER(l_selected_fields(i).column_name) = 'operations_attribute7' THEN
1880 l_content_item_data :=
1881 l_content_item_data
1882 || variable_b
1883 || l_selected_fields(i).variable_name
1884 || '">'
1885 || v_wip_content.operations_attribute7
1886 || variable_e;
1887 ELSIF LOWER(l_selected_fields(i).column_name) = 'operations_attribute8' THEN
1888 l_content_item_data :=
1889 l_content_item_data
1890 || variable_b
1891 || l_selected_fields(i).variable_name
1892 || '">'
1893 || v_wip_content.operations_attribute8
1894 || variable_e;
1895 ELSIF LOWER(l_selected_fields(i).column_name) = 'operations_attribute9' THEN
1896 l_content_item_data :=
1897 l_content_item_data
1898 || variable_b
1899 || l_selected_fields(i).variable_name
1900 || '">'
1901 || v_wip_content.operations_attribute9
1902 || variable_e;
1903 ELSIF LOWER(l_selected_fields(i).column_name) = 'operations_attribute10' THEN
1904 l_content_item_data :=
1905 l_content_item_data
1906 || variable_b
1907 || l_selected_fields(i).variable_name
1908 || '">'
1909 || v_wip_content.operations_attribute10
1910 || variable_e;
1911 ELSIF LOWER(l_selected_fields(i).column_name) = 'operations_attribute11' THEN
1912 l_content_item_data :=
1913 l_content_item_data
1914 || variable_b
1915 || l_selected_fields(i).variable_name
1916 || '">'
1917 || v_wip_content.operations_attribute11
1918 || variable_e;
1919 ELSIF LOWER(l_selected_fields(i).column_name) = 'operations_attribute12' THEN
1920 l_content_item_data :=
1921 l_content_item_data
1922 || variable_b
1923 || l_selected_fields(i).variable_name
1924 || '">'
1925 || v_wip_content.operations_attribute12
1926 || variable_e;
1927 ELSIF LOWER(l_selected_fields(i).column_name) = 'operations_attribute13' THEN
1928 l_content_item_data :=
1929 l_content_item_data
1930 || variable_b
1931 || l_selected_fields(i).variable_name
1932 || '">'
1933 || v_wip_content.operations_attribute13
1934 || variable_e;
1935 ELSIF LOWER(l_selected_fields(i).column_name) = 'operations_attribute14' THEN
1936 l_content_item_data :=
1937 l_content_item_data
1938 || variable_b
1939 || l_selected_fields(i).variable_name
1940 || '">'
1941 || v_wip_content.operations_attribute14
1942 || variable_e;
1943 ELSIF LOWER(l_selected_fields(i).column_name) = 'operations_attribute15' THEN
1944 l_content_item_data :=
1945 l_content_item_data
1946 || variable_b
1947 || l_selected_fields(i).variable_name
1948 || '">'
1949 || v_wip_content.operations_attribute15
1950 || variable_e;
1951 ELSIF LOWER(l_selected_fields(i).column_name) = 'operations_backflush_flag' THEN
1952 l_content_item_data :=
1953 l_content_item_data
1954 || variable_b
1955 || l_selected_fields(i).variable_name
1956 || '">'
1957 || v_wip_content.operations_backflush_flag
1958 || variable_e;
1959 ELSIF LOWER(l_selected_fields(i).column_name) = 'operations_department' THEN
1960 l_content_item_data :=
1961 l_content_item_data
1962 || variable_b
1963 || l_selected_fields(i).variable_name
1964 || '">'
1965 || v_wip_content.operations_department
1966 || variable_e;
1967 ELSIF LOWER(l_selected_fields(i).column_name) = 'operations_description' THEN
1968 l_content_item_data :=
1969 l_content_item_data
1970 || variable_b
1971 || l_selected_fields(i).variable_name
1972 || '">'
1973 || v_wip_content.operations_description
1974 || variable_e;
1975 ELSIF LOWER(l_selected_fields(i).column_name) = 'operations_first_complet_dt' THEN
1976 l_content_item_data :=
1977 l_content_item_data
1978 || variable_b
1979 || l_selected_fields(i).variable_name
1980 || '">'
1981 || v_wip_content.operations_first_complet_dt
1982 || variable_e;
1983 ELSIF LOWER(l_selected_fields(i).column_name) = 'operations_first_receipt_date' THEN
1984 l_content_item_data :=
1985 l_content_item_data
1986 || variable_b
1987 || l_selected_fields(i).variable_name
1988 || '">'
1989 || v_wip_content.operations_first_receipt_date
1990 || variable_e;
1991 ELSIF LOWER(l_selected_fields(i).column_name) = 'operations_last_complet_dt' THEN
1992 l_content_item_data :=
1993 l_content_item_data
1994 || variable_b
1995 || l_selected_fields(i).variable_name
1996 || '">'
1997 || v_wip_content.operations_last_complet_dt
1998 || variable_e;
1999 ELSIF LOWER(l_selected_fields(i).column_name) = 'operations_last_receipt_date' THEN
2000 l_content_item_data :=
2001 l_content_item_data
2002 || variable_b
2003 || l_selected_fields(i).variable_name
2004 || '">'
2005 || v_wip_content.operations_last_receipt_date
2006 || variable_e;
2007 ELSIF LOWER(l_selected_fields(i).column_name) = 'operations_min_transfer_qty' THEN
2008 l_content_item_data :=
2009 l_content_item_data
2010 || variable_b
2011 || l_selected_fields(i).variable_name
2012 || '">'
2013 || v_wip_content.operations_min_transfer_qty
2014 || variable_e;
2015 ELSIF LOWER(l_selected_fields(i).column_name) = 'operations_schedule_quantity' THEN
2016 l_content_item_data :=
2017 l_content_item_data
2018 || variable_b
2019 || l_selected_fields(i).variable_name
2020 || '">'
2021 || v_wip_content.operations_schedule_quantity
2022 || variable_e;
2023 ELSIF LOWER(l_selected_fields(i).column_name) = 'operations_standard_operation' THEN
2024 l_content_item_data :=
2025 l_content_item_data
2026 || variable_b
2027 || l_selected_fields(i).variable_name
2028 || '">'
2029 || v_wip_content.operations_standard_operation
2030 || variable_e;
2031 ELSIF LOWER(l_selected_fields(i).column_name) = 'operations_yield' THEN
2032 l_content_item_data :=
2033 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.operations_yield
2034 || variable_e;
2035 ELSIF LOWER(l_selected_fields(i).column_name) = 'operations_yield_enabled' THEN
2036 l_content_item_data :=
2037 l_content_item_data
2038 || variable_b
2039 || l_selected_fields(i).variable_name
2040 || '">'
2041 || v_wip_content.operations_yield_enabled
2042 || variable_e;
2043 /* Requirements */
2044 ELSIF LOWER(l_selected_fields(i).column_name) = 'requirements_attribute_categ' THEN
2045 l_content_item_data :=
2046 l_content_item_data
2047 || variable_b
2048 || l_selected_fields(i).variable_name
2049 || '">'
2050 || v_wip_content.requirements_attribute_categ
2051 || variable_e;
2052 ELSIF LOWER(l_selected_fields(i).column_name) = 'requirements_attribute1' THEN
2053 l_content_item_data :=
2054 l_content_item_data
2055 || variable_b
2056 || l_selected_fields(i).variable_name
2057 || '">'
2058 || v_wip_content.requirements_attribute1
2059 || variable_e;
2060 ELSIF LOWER(l_selected_fields(i).column_name) = 'requirements_attribute2' THEN
2061 l_content_item_data :=
2062 l_content_item_data
2063 || variable_b
2064 || l_selected_fields(i).variable_name
2065 || '">'
2066 || v_wip_content.requirements_attribute2
2067 || variable_e;
2068 ELSIF LOWER(l_selected_fields(i).column_name) = 'requirements_attribute3' THEN
2069 l_content_item_data :=
2070 l_content_item_data
2071 || variable_b
2072 || l_selected_fields(i).variable_name
2073 || '">'
2074 || v_wip_content.requirements_attribute3
2075 || variable_e;
2076 ELSIF LOWER(l_selected_fields(i).column_name) = 'requirements_attribute4' THEN
2077 l_content_item_data :=
2078 l_content_item_data
2079 || variable_b
2080 || l_selected_fields(i).variable_name
2081 || '">'
2082 || v_wip_content.requirements_attribute4
2083 || variable_e;
2084 ELSIF LOWER(l_selected_fields(i).column_name) = 'requirements_attribute5' THEN
2085 l_content_item_data :=
2086 l_content_item_data
2087 || variable_b
2088 || l_selected_fields(i).variable_name
2089 || '">'
2090 || v_wip_content.requirements_attribute5
2091 || variable_e;
2092 ELSIF LOWER(l_selected_fields(i).column_name) = 'requirements_attribute6' THEN
2093 l_content_item_data :=
2094 l_content_item_data
2095 || variable_b
2096 || l_selected_fields(i).variable_name
2097 || '">'
2098 || v_wip_content.requirements_attribute6
2099 || variable_e;
2100 ELSIF LOWER(l_selected_fields(i).column_name) = 'requirements_attribute7' THEN
2101 l_content_item_data :=
2102 l_content_item_data
2103 || variable_b
2104 || l_selected_fields(i).variable_name
2105 || '">'
2106 || v_wip_content.requirements_attribute7
2107 || variable_e;
2108 ELSIF LOWER(l_selected_fields(i).column_name) = 'requirements_attribute8' THEN
2109 l_content_item_data :=
2110 l_content_item_data
2111 || variable_b
2112 || l_selected_fields(i).variable_name
2113 || '">'
2114 || v_wip_content.requirements_attribute8
2115 || variable_e;
2116 ELSIF LOWER(l_selected_fields(i).column_name) = 'requirements_attribute9' THEN
2117 l_content_item_data :=
2118 l_content_item_data
2119 || variable_b
2120 || l_selected_fields(i).variable_name
2121 || '">'
2122 || v_wip_content.requirements_attribute9
2123 || variable_e;
2124 ELSIF LOWER(l_selected_fields(i).column_name) = 'requirements_attribute10' THEN
2125 l_content_item_data :=
2126 l_content_item_data
2127 || variable_b
2128 || l_selected_fields(i).variable_name
2129 || '">'
2130 || v_wip_content.requirements_attribute10
2131 || variable_e;
2132 ELSIF LOWER(l_selected_fields(i).column_name) = 'requirements_attribute11' THEN
2133 l_content_item_data :=
2134 l_content_item_data
2135 || variable_b
2136 || l_selected_fields(i).variable_name
2137 || '">'
2138 || v_wip_content.requirements_attribute11
2139 || variable_e;
2140 ELSIF LOWER(l_selected_fields(i).column_name) = 'requirements_attribute12' THEN
2141 l_content_item_data :=
2142 l_content_item_data
2143 || variable_b
2144 || l_selected_fields(i).variable_name
2145 || '">'
2146 || v_wip_content.requirements_attribute12
2147 || variable_e;
2148 ELSIF LOWER(l_selected_fields(i).column_name) = 'requirements_attribute13' THEN
2149 l_content_item_data :=
2150 l_content_item_data
2151 || variable_b
2152 || l_selected_fields(i).variable_name
2153 || '">'
2154 || v_wip_content.requirements_attribute13
2155 || variable_e;
2156 ELSIF LOWER(l_selected_fields(i).column_name) = 'requirements_attribute14' THEN
2157 l_content_item_data :=
2158 l_content_item_data
2159 || variable_b
2160 || l_selected_fields(i).variable_name
2161 || '">'
2162 || v_wip_content.requirements_attribute14
2163 || variable_e;
2164 ELSIF LOWER(l_selected_fields(i).column_name) = 'requirements_attribute15' THEN
2165 l_content_item_data :=
2166 l_content_item_data
2167 || variable_b
2168 || l_selected_fields(i).variable_name
2169 || '">'
2170 || v_wip_content.requirements_attribute15
2171 || variable_e;
2172 ELSIF LOWER(l_selected_fields(i).column_name) = 'requirements_comments' THEN
2173 l_content_item_data :=
2174 l_content_item_data
2175 || variable_b
2176 || l_selected_fields(i).variable_name
2177 || '">'
2178 || v_wip_content.requirements_comments
2179 || variable_e;
2180 ELSIF LOWER(l_selected_fields(i).column_name) = 'requirements_date_required' THEN
2181 l_content_item_data :=
2182 l_content_item_data
2183 || variable_b
2184 || l_selected_fields(i).variable_name
2185 || '">'
2186 || v_wip_content.requirements_date_required
2187 || variable_e;
2188 ELSIF LOWER(l_selected_fields(i).column_name) = 'requirements_department_name' THEN
2189 l_content_item_data :=
2190 l_content_item_data
2191 || variable_b
2192 || l_selected_fields(i).variable_name
2193 || '">'
2194 || v_wip_content.requirements_department_name
2195 || variable_e;
2196 ELSIF LOWER(l_selected_fields(i).column_name) = 'requirements_operation_seq' THEN
2197 l_content_item_data :=
2198 l_content_item_data
2199 || variable_b
2200 || l_selected_fields(i).variable_name
2201 || '">'
2202 || v_wip_content.requirements_operation_seq
2203 || variable_e;
2204 ELSIF LOWER(l_selected_fields(i).column_name) = 'requirements_qty_per_assembly' THEN
2205 l_content_item_data :=
2206 l_content_item_data
2207 || variable_b
2208 || l_selected_fields(i).variable_name
2209 || '">'
2210 || v_wip_content.requirements_qty_per_assembly
2211 || variable_e;
2212 ELSIF LOWER(l_selected_fields(i).column_name) = 'requirements_required_quantity' THEN
2213 l_content_item_data :=
2214 l_content_item_data
2215 || variable_b
2216 || l_selected_fields(i).variable_name
2217 || '">'
2218 || v_wip_content.requirements_required_quantity
2219 || variable_e;
2220 ELSIF LOWER(l_selected_fields(i).column_name) = 'requirements_supply_locator' THEN
2221 l_content_item_data :=
2222 l_content_item_data
2223 || variable_b
2224 || l_selected_fields(i).variable_name
2225 || '">'
2226 || v_wip_content.requirements_supply_locator
2227 || variable_e;
2228 ELSIF LOWER(l_selected_fields(i).column_name) = 'requirements_supply_sub' THEN
2229 l_content_item_data :=
2230 l_content_item_data
2231 || variable_b
2232 || l_selected_fields(i).variable_name
2233 || '">'
2234 || v_wip_content.requirements_supply_sub
2235 || variable_e;
2236 ELSIF LOWER(l_selected_fields(i).column_name) = 'requirements_wip_supply_type' THEN
2237 l_content_item_data :=
2238 l_content_item_data
2239 || variable_b
2240 || l_selected_fields(i).variable_name
2241 || '">'
2242 || v_wip_content.requirements_wip_supply_type
2243 || variable_e;
2244 ELSIF LOWER(l_selected_fields(i).column_name) = 'requirements_item_name' THEN
2245 l_content_item_data :=
2246 l_content_item_data
2247 || variable_b
2248 || l_selected_fields(i).variable_name
2249 || '">'
2250 || v_wip_content.requirements_item_name
2251 || variable_e;
2252 /* LPN Content*/
2253 ELSIF LOWER(l_selected_fields(i).column_name) = 'lpn' THEN
2254 l_content_item_data :=
2255 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.lpn || variable_e;
2256 ELSIF LOWER(l_selected_fields(i).column_name) = 'parent_lpn' THEN
2257 l_content_item_data :=
2258 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.parent_lpn || variable_e;
2259 ELSIF LOWER(l_selected_fields(i).column_name) = 'volume' THEN
2260 l_content_item_data :=
2261 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.volume || variable_e;
2262 ELSIF LOWER(l_selected_fields(i).column_name) = 'volume_uom' THEN
2263 l_content_item_data :=
2264 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.volume_uom || variable_e;
2265 ELSIF LOWER(l_selected_fields(i).column_name) = 'gross_weight' THEN
2266 l_content_item_data :=
2267 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.gross_weight || variable_e;
2268 ELSIF LOWER(l_selected_fields(i).column_name) = 'gross_weight_uom' THEN
2269 l_content_item_data :=
2270 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.gross_weight_uom
2271 || variable_e;
2272 ELSIF LOWER(l_selected_fields(i).column_name) = 'tare_weight' THEN
2273 l_content_item_data :=
2274 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.tare_weight || variable_e;
2275 ELSIF LOWER(l_selected_fields(i).column_name) = 'tare_weight_uom' THEN
2276 l_content_item_data :=
2277 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.tare_weight_uom
2278 || variable_e;
2279 ELSIF LOWER(l_selected_fields(i).column_name) = 'lpn_container_item' THEN
2280 l_content_item_data :=
2281 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.lpn_container_item
2282 || variable_e;
2283 ELSIF LOWER(l_selected_fields(i).column_name) = 'organization' THEN
2284 l_content_item_data :=
2285 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.ORGANIZATION || variable_e;
2286 ELSIF LOWER(l_selected_fields(i).column_name) = 'subinventory_code' THEN
2287 l_content_item_data :=
2288 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.subinventory_code
2289 || variable_e;
2290 ELSIF LOWER(l_selected_fields(i).column_name) = 'locator' THEN
2291 l_content_item_data :=
2292 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.LOCATOR || variable_e;
2293 ELSIF LOWER(l_selected_fields(i).column_name) = 'item' THEN
2294 l_content_item_data :=
2295 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.item || variable_e;
2296 ELSIF LOWER(l_selected_fields(i).column_name) = 'item_description' THEN
2297 l_content_item_data :=
2298 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.item_description
2299 || variable_e;
2300 ELSIF LOWER(l_selected_fields(i).column_name) = 'revision' THEN
2301 l_content_item_data :=
2302 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.revision || variable_e;
2303 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot' THEN
2304 l_content_item_data :=
2305 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.lot || variable_e;
2306 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_status' THEN
2307 l_content_item_data :=
2308 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.lot_status || variable_e;
2309 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_expiration_date' THEN
2310 l_content_item_data :=
2311 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.lot_expiration_date
2312 || variable_e;
2313 ELSIF LOWER(l_selected_fields(i).column_name) = 'quantity' THEN
2314 /* bug 7016426 */
2315 IF p_label_type_info.business_flow_code = 37 THEN
2316 -- in case x-dock dock qty does not consume the entire qty
2317 -- IN the lpn, it should only print the qty that is
2318 -- allocated TO the wip x-dock and not the entire left out
2319 -- qty IN the LPN, taking qty from corresponding MMTT
2320 -- instead FOR wip x-dock
2321
2322 l_content_item_data := l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || Abs(l_quantity) || variable_e;
2323 ELSE
2324 l_content_item_data :=l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.quantity || variable_e;
2325 END IF;
2326 /* bug 7016426 */
2327 ELSIF LOWER(l_selected_fields(i).column_name) = 'uom' THEN
2328 l_content_item_data :=
2329 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.uom || variable_e;
2330 ELSIF LOWER(l_selected_fields(i).column_name) = 'cost_group' THEN
2331 l_content_item_data :=
2332 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.cost_group || variable_e;
2333 ELSIF LOWER(l_selected_fields(i).column_name) = 'item_hazard_class' THEN
2334 l_content_item_data :=
2335 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.item_hazard_class
2336 || variable_e;
2337 ELSIF LOWER(l_selected_fields(i).column_name) = 'item_attribute_category' THEN
2338 l_content_item_data :=
2339 l_content_item_data
2340 || variable_b
2341 || l_selected_fields(i).variable_name
2342 || '">'
2343 || v_wip_content.item_attribute_category
2344 || variable_e;
2345 ELSIF LOWER(l_selected_fields(i).column_name) = 'item_attribute1' THEN
2346 l_content_item_data :=
2347 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.item_attribute1
2348 || variable_e;
2349 ELSIF LOWER(l_selected_fields(i).column_name) = 'item_attribute2' THEN
2350 l_content_item_data :=
2351 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.item_attribute2
2352 || variable_e;
2353 ELSIF LOWER(l_selected_fields(i).column_name) = 'item_attribute3' THEN
2354 l_content_item_data :=
2355 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.item_attribute3
2356 || variable_e;
2357 ELSIF LOWER(l_selected_fields(i).column_name) = 'item_attribute4' THEN
2358 l_content_item_data :=
2359 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.item_attribute4
2360 || variable_e;
2361 ELSIF LOWER(l_selected_fields(i).column_name) = 'item_attribute5' THEN
2362 l_content_item_data :=
2363 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.item_attribute5
2364 || variable_e;
2365 ELSIF LOWER(l_selected_fields(i).column_name) = 'item_attribute6' THEN
2366 l_content_item_data :=
2367 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.item_attribute6
2368 || variable_e;
2369 ELSIF LOWER(l_selected_fields(i).column_name) = 'item_attribute7' THEN
2370 l_content_item_data :=
2371 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.item_attribute7
2372 || variable_e;
2373 ELSIF LOWER(l_selected_fields(i).column_name) = 'item_attribute8' THEN
2374 l_content_item_data :=
2375 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.item_attribute8
2376 || variable_e;
2377 ELSIF LOWER(l_selected_fields(i).column_name) = 'item_attribute9' THEN
2378 l_content_item_data :=
2379 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.item_attribute9
2380 || variable_e;
2381 ELSIF LOWER(l_selected_fields(i).column_name) = 'item_attribute10' THEN
2382 l_content_item_data :=
2383 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.item_attribute10
2384 || variable_e;
2385 ELSIF LOWER(l_selected_fields(i).column_name) = 'item_attribute11' THEN
2386 l_content_item_data :=
2387 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.item_attribute11
2388 || variable_e;
2389 ELSIF LOWER(l_selected_fields(i).column_name) = 'item_attribute12' THEN
2390 l_content_item_data :=
2391 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.item_attribute12
2392 || variable_e;
2393 ELSIF LOWER(l_selected_fields(i).column_name) = 'item_attribute13' THEN
2394 l_content_item_data :=
2395 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.item_attribute13
2396 || variable_e;
2397 ELSIF LOWER(l_selected_fields(i).column_name) = 'item_attribute14' THEN
2398 l_content_item_data :=
2399 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.item_attribute14
2400 || variable_e;
2401 ELSIF LOWER(l_selected_fields(i).column_name) = 'item_attribute15' THEN
2402 l_content_item_data :=
2403 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.item_attribute15
2404 || variable_e;
2405 ELSIF LOWER(l_selected_fields(i).column_name) = 'lpn_attribute_category' THEN
2406 l_content_item_data :=
2407 l_content_item_data
2408 || variable_b
2409 || l_selected_fields(i).variable_name
2410 || '">'
2411 || v_wip_content.lpn_attribute_category
2412 || variable_e;
2413 ELSIF LOWER(l_selected_fields(i).column_name) = 'lpn_attribute1' THEN
2414 l_content_item_data :=
2415 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.lpn_attribute1 || variable_e;
2416 ELSIF LOWER(l_selected_fields(i).column_name) = 'lpn_attribute2' THEN
2417 l_content_item_data :=
2418 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.lpn_attribute2 || variable_e;
2419 ELSIF LOWER(l_selected_fields(i).column_name) = 'lpn_attribute3' THEN
2420 l_content_item_data :=
2421 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.lpn_attribute3 || variable_e;
2422 ELSIF LOWER(l_selected_fields(i).column_name) = 'lpn_attribute4' THEN
2423 l_content_item_data :=
2424 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.lpn_attribute4 || variable_e;
2425 ELSIF LOWER(l_selected_fields(i).column_name) = 'lpn_attribute5' THEN
2426 l_content_item_data :=
2427 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.lpn_attribute5 || variable_e;
2428 ELSIF LOWER(l_selected_fields(i).column_name) = 'lpn_attribute6' THEN
2429 l_content_item_data :=
2430 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.lpn_attribute6 || variable_e;
2431 ELSIF LOWER(l_selected_fields(i).column_name) = 'lpn_attribute7' THEN
2432 l_content_item_data :=
2433 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.lpn_attribute7 || variable_e;
2434 ELSIF LOWER(l_selected_fields(i).column_name) = 'lpn_attribute8' THEN
2435 l_content_item_data :=
2436 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.lpn_attribute8 || variable_e;
2437 ELSIF LOWER(l_selected_fields(i).column_name) = 'lpn_attribute9' THEN
2438 l_content_item_data :=
2439 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.lpn_attribute9 || variable_e;
2440 ELSIF LOWER(l_selected_fields(i).column_name) = 'lpn_attribute10' THEN
2441 l_content_item_data :=
2442 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.lpn_attribute10
2443 || variable_e;
2444 ELSIF LOWER(l_selected_fields(i).column_name) = 'lpn_attribute11' THEN
2445 l_content_item_data :=
2446 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.lpn_attribute11
2447 || variable_e;
2448 ELSIF LOWER(l_selected_fields(i).column_name) = 'lpn_attribute12' THEN
2449 l_content_item_data :=
2450 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.lpn_attribute12
2451 || variable_e;
2452 ELSIF LOWER(l_selected_fields(i).column_name) = 'lpn_attribute13' THEN
2453 l_content_item_data :=
2454 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.lpn_attribute13
2455 || variable_e;
2456 ELSIF LOWER(l_selected_fields(i).column_name) = 'lpn_attribute14' THEN
2457 l_content_item_data :=
2458 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.lpn_attribute14
2459 || variable_e;
2460 ELSIF LOWER(l_selected_fields(i).column_name) = 'lpn_attribute15' THEN
2461 l_content_item_data :=
2462 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.lpn_attribute15
2463 || variable_e;
2464 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_attribute_category' THEN
2465 l_content_item_data :=
2466 l_content_item_data
2467 || variable_b
2468 || l_selected_fields(i).variable_name
2469 || '">'
2470 || v_wip_content.lot_attribute_category
2471 || variable_e;
2472 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_c_attribute1' THEN
2473 l_content_item_data :=
2474 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.lot_c_attribute1
2475 || variable_e;
2476 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_c_attribute2' THEN
2477 l_content_item_data :=
2478 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.lot_c_attribute2
2479 || variable_e;
2480 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_c_attribute3' THEN
2481 l_content_item_data :=
2482 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.lot_c_attribute3
2483 || variable_e;
2484 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_c_attribute4' THEN
2485 l_content_item_data :=
2486 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.lot_c_attribute4
2487 || variable_e;
2488 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_c_attribute5' THEN
2489 l_content_item_data :=
2490 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.lot_c_attribute5
2491 || variable_e;
2492 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_c_attribute6' THEN
2493 l_content_item_data :=
2494 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.lot_c_attribute6
2495 || variable_e;
2496 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_c_attribute7' THEN
2497 l_content_item_data :=
2498 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.lot_c_attribute7
2499 || variable_e;
2500 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_c_attribute8' THEN
2501 l_content_item_data :=
2502 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.lot_c_attribute8
2503 || variable_e;
2504 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_c_attribute9' THEN
2505 l_content_item_data :=
2506 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.lot_c_attribute9
2507 || variable_e;
2508 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_c_attribute10' THEN
2509 l_content_item_data :=
2510 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.lot_c_attribute10
2511 || variable_e;
2512 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_c_attribute11' THEN
2513 l_content_item_data :=
2514 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.lot_c_attribute11
2515 || variable_e;
2516 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_c_attribute12' THEN
2517 l_content_item_data :=
2518 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.lot_c_attribute12
2519 || variable_e;
2520 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_c_attribute13' THEN
2521 l_content_item_data :=
2522 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.lot_c_attribute13
2523 || variable_e;
2524 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_c_attribute14' THEN
2525 l_content_item_data :=
2526 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.lot_c_attribute14
2527 || variable_e;
2528 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_c_attribute15' THEN
2529 l_content_item_data :=
2530 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.lot_c_attribute15
2531 || variable_e;
2532 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_c_attribute16' THEN
2533 l_content_item_data :=
2534 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.lot_c_attribute16
2535 || variable_e;
2536 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_c_attribute17' THEN
2537 l_content_item_data :=
2538 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.lot_c_attribute17
2539 || variable_e;
2540 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_c_attribute18' THEN
2541 l_content_item_data :=
2542 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.lot_c_attribute18
2543 || variable_e;
2544 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_c_attribute19' THEN
2545 l_content_item_data :=
2546 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.lot_c_attribute19
2547 || variable_e;
2548 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_c_attribute20' THEN
2549 l_content_item_data :=
2550 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.lot_c_attribute20
2551 || variable_e;
2552 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_d_attribute1' THEN
2553 l_content_item_data :=
2554 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.lot_d_attribute1
2555 || variable_e;
2556 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_d_attribute2' THEN
2557 l_content_item_data :=
2558 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.lot_d_attribute2
2559 || variable_e;
2560 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_d_attribute3' THEN
2561 l_content_item_data :=
2562 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.lot_d_attribute3
2563 || variable_e;
2564 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_d_attribute4' THEN
2565 l_content_item_data :=
2566 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.lot_d_attribute4
2567 || variable_e;
2568 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_d_attribute5' THEN
2569 l_content_item_data :=
2570 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.lot_d_attribute5
2571 || variable_e;
2572 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_d_attribute6' THEN
2573 l_content_item_data :=
2574 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.lot_d_attribute6
2575 || variable_e;
2576 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_d_attribute7' THEN
2577 l_content_item_data :=
2578 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.lot_d_attribute7
2579 || variable_e;
2580 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_d_attribute8' THEN
2581 l_content_item_data :=
2582 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.lot_d_attribute8
2583 || variable_e;
2584 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_d_attribute9' THEN
2585 l_content_item_data :=
2586 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.lot_d_attribute9
2587 || variable_e;
2588 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_d_attribute10' THEN
2589 l_content_item_data :=
2590 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.lot_d_attribute10
2591 || variable_e;
2592 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_n_attribute1' THEN
2593 l_content_item_data :=
2594 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.lot_n_attribute1
2595 || variable_e;
2596 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_n_attribute2' THEN
2597 l_content_item_data :=
2598 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.lot_n_attribute2
2599 || variable_e;
2600 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_n_attribute3' THEN
2601 l_content_item_data :=
2602 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.lot_n_attribute3
2603 || variable_e;
2604 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_n_attribute4' THEN
2605 l_content_item_data :=
2606 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.lot_n_attribute4
2607 || variable_e;
2608 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_n_attribute5' THEN
2609 l_content_item_data :=
2610 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.lot_n_attribute5
2611 || variable_e;
2612 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_n_attribute6' THEN
2613 l_content_item_data :=
2614 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.lot_n_attribute6
2615 || variable_e;
2616 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_n_attribute7' THEN
2617 l_content_item_data :=
2618 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.lot_n_attribute7
2619 || variable_e;
2620 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_n_attribute8' THEN
2621 l_content_item_data :=
2622 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.lot_n_attribute8
2623 || variable_e;
2624 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_n_attribute9' THEN
2625 l_content_item_data :=
2626 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.lot_n_attribute9
2627 || variable_e;
2628 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_n_attribute10' THEN
2629 l_content_item_data :=
2630 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.lot_n_attribute10
2631 || variable_e;
2632 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_country_of_origin' THEN
2633 l_content_item_data :=
2634 l_content_item_data
2635 || variable_b
2636 || l_selected_fields(i).variable_name
2637 || '">'
2638 || v_wip_content.lot_country_of_origin
2639 || variable_e;
2640 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_grade_code' THEN
2641 l_content_item_data :=
2642 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.lot_grade_code || variable_e;
2643 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_origination_date' THEN
2644 l_content_item_data :=
2645 l_content_item_data
2646 || variable_b
2647 || l_selected_fields(i).variable_name
2648 || '">'
2649 || v_wip_content.lot_origination_date
2650 || variable_e;
2651 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_date_code' THEN
2652 l_content_item_data :=
2653 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.lot_date_code || variable_e;
2654 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_change_date' THEN
2655 l_content_item_data :=
2656 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.lot_change_date
2657 || variable_e;
2658 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_age' THEN
2659 l_content_item_data :=
2660 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.lot_age || variable_e;
2661 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_retest_date' THEN
2662 l_content_item_data :=
2663 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.lot_retest_date
2664 || variable_e;
2665 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_maturity_date' THEN
2666 l_content_item_data :=
2667 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.lot_maturity_date
2668 || variable_e;
2669 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_item_size' THEN
2670 l_content_item_data :=
2671 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.lot_item_size || variable_e;
2672 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_color' THEN
2673 l_content_item_data :=
2674 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.lot_color || variable_e;
2675 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_volume' THEN
2676 l_content_item_data :=
2677 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.lot_volume || variable_e;
2678 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_volume_uom' THEN
2679 l_content_item_data :=
2680 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.lot_volume_uom || variable_e;
2681 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_place_of_origin' THEN
2682 l_content_item_data :=
2683 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.lot_place_of_origin
2684 || variable_e;
2685 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_best_by_date' THEN
2686 l_content_item_data :=
2687 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.lot_best_by_date
2688 || variable_e;
2689 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_length' THEN
2690 l_content_item_data :=
2691 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.lot_length || variable_e;
2692 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_length_uom' THEN
2693 l_content_item_data :=
2694 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.lot_length_uom || variable_e;
2695 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_recycled_cont' THEN
2696 l_content_item_data :=
2697 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.lot_recycled_cont
2698 || variable_e;
2699 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_thickness' THEN
2700 l_content_item_data :=
2701 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.lot_thickness || variable_e;
2702 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_thickness_uom' THEN
2703 l_content_item_data :=
2704 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.lot_thickness_uom
2705 || variable_e;
2706 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_width' THEN
2707 l_content_item_data :=
2708 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.lot_width || variable_e;
2709 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_width_uom' THEN
2710 l_content_item_data :=
2711 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.lot_width_uom || variable_e;
2712 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_curl' THEN
2713 l_content_item_data :=
2714 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.lot_curl || variable_e;
2715 ELSIF LOWER(l_selected_fields(i).column_name) = 'lot_vendor' THEN
2716 l_content_item_data :=
2717 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_content.lot_vendor || variable_e;
2718 ELSIF LOWER(l_selected_fields(i).column_name) = 'project' THEN
2719 l_content_item_data :=
2720 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || l_project_name || variable_e;
2721 ELSIF LOWER(l_selected_fields(i).column_name) = 'task' THEN
2722 l_content_item_data :=
2723 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || l_task_name || variable_e;
2724 END IF;
2725 END LOOP;
2726
2727 l_content_item_data := l_content_item_data || label_e;
2728 x_variable_content(l_label_index).label_content := l_content_item_data;
2729 x_variable_content(l_label_index).label_request_id := l_label_request_id;
2730
2731 ------------------------Start of changes for Custom Labels project code------------------
2732 -- Fix for bug: 4179593 Start
2733 IF (l_CustSqlWarnFlagSet) THEN
2734 l_custom_sql_ret_status := INV_LABEL.G_WARNING;
2735 l_custom_sql_ret_msg := l_CustSqlWarnMsg;
2736 END IF;
2737
2738 IF (l_CustSqlErrFlagSet) THEN
2739 l_custom_sql_ret_status := FND_API.G_RET_STS_ERROR;
2740 l_custom_sql_ret_msg := l_CustSqlErrMsg;
2741 END IF;
2742 -- Fix for bug: 4179593 End
2743
2744 x_variable_content(l_label_index).label_status := l_custom_sql_ret_status ;
2745 x_variable_content(l_label_index).error_message := l_custom_sql_ret_msg;
2746
2747 ------------------------End of this changes for Custom Labels project code---------------
2748
2749 l_label_index := l_label_index + 1;
2750
2751 <<nextlabel>>
2752 l_content_item_data := '';
2753 l_custom_sql_ret_status := NULL;
2754 l_custom_sql_ret_msg := NULL;
2755
2756 IF (l_debug = 1) THEN
2757 TRACE(l_column_name_list);
2758 TRACE(' Finished writing variables ');
2759 END IF;
2760 END LOOP;
2761
2762 IF p_transaction_id IS NOT NULL THEN
2763 CLOSE wip_content_cur;
2764 ELSE
2765 CLOSE wip_discrete_cur;
2766 END IF;
2767
2768 --x_variable_content := x_variable_content || l_content_item_data ;
2769
2770 IF p_label_type_info.business_flow_code IN(28) THEN
2771 FETCH wip_lines_cur
2772 INTO l_organization_id
2773 , l_wip_entity_id
2774 , l_repetitive_schedule_id
2775 , l_inventory_item_id
2776 , l_operation_seq_num
2777 , l_lpn_id
2778 , l_revision
2779 , l_lot_number
2780 , l_quantity
2781 , l_uom
2782 , l_cost_group_id
2783 , l_subinventory
2784 , l_project_name
2785 , l_task_name;
2786
2787 IF wip_lines_cur%NOTFOUND THEN
2788 IF (l_debug = 1) THEN
2789 TRACE(' No more record found for Wip Pick load, end of WIP content label');
2790 END IF;
2791
2792 CLOSE wip_lines_cur;
2793
2794 l_wip_entity_id := NULL;
2795 END IF;
2796 ELSIF p_label_type_info.business_flow_code IN(29) THEN
2797 FETCH wip_drop_lines_cur
2798 INTO l_organization_id
2799 , l_wip_entity_id
2800 , l_repetitive_schedule_id
2801 , l_inventory_item_id
2802 , l_operation_seq_num
2803 , l_lpn_id
2804 , l_revision
2805 , l_lot_number
2806 , l_quantity
2807 , l_uom
2808 , l_cost_group_id
2809 , l_subinventory
2810 , l_project_name
2811 , l_task_name;
2812
2813 IF wip_drop_lines_cur%NOTFOUND THEN
2814 IF (l_debug = 1) THEN
2815 TRACE(' No more record found for Wip Pick drop, end of WIP content label');
2816 END IF;
2817
2818 CLOSE wip_drop_lines_cur;
2819
2820 l_wip_entity_id := NULL;
2821 END IF;
2822 ELSIF p_label_type_info.business_flow_code = 37 THEN
2823 -- Bug Number: 2829872
2824 -- Manufacturing Cross Dock(37)
2825 FETCH wip_cross_dock_cur
2826 INTO l_organization_id
2827 , l_wip_entity_id
2828 , l_repetitive_schedule_id
2829 , l_inventory_item_id
2830 , l_operation_seq_num
2831 , l_lpn_id
2832 , l_revision
2833 , l_lot_number
2834 , l_quantity
2835 , l_uom
2836 , l_cost_group_id
2837 , l_subinventory
2838 , l_project_name
2839 , l_task_name;
2840
2841 IF wip_cross_dock_cur%NOTFOUND THEN
2842 IF (l_debug = 1) THEN
2843 TRACE(' No more record found for Manufacturing cross dock, end of WIP content label');
2844 END IF;
2845
2846 CLOSE wip_cross_dock_cur;
2847
2848 l_wip_entity_id := NULL;
2849 END IF;
2850 ELSE
2851 l_wip_entity_id := NULL;
2852 END IF;
2853 END LOOP;
2854 END get_variable_data;
2855
2856 PROCEDURE get_variable_data(
2857 x_variable_content OUT NOCOPY LONG
2858 , x_msg_count OUT NOCOPY NUMBER
2859 , x_msg_data OUT NOCOPY VARCHAR2
2860 , x_return_status OUT NOCOPY VARCHAR2
2861 , p_label_type_info IN inv_label.label_type_rec
2862 , p_transaction_id IN NUMBER
2863 , p_input_param IN mtl_material_transactions_temp%ROWTYPE
2864 , p_transaction_identifier IN NUMBER
2865 ) IS
2866 l_variable_data_tbl inv_label.label_tbl_type;
2867 BEGIN
2868 get_variable_data(
2869 x_variable_content => l_variable_data_tbl
2870 , x_msg_count => x_msg_count
2871 , x_msg_data => x_msg_data
2872 , x_return_status => x_return_status
2873 , p_label_type_info => p_label_type_info
2874 , p_transaction_id => p_transaction_id
2875 , p_input_param => p_input_param
2876 , p_transaction_identifier => p_transaction_identifier
2877 );
2878 x_variable_content := '';
2879
2880 FOR i IN 1 .. l_variable_data_tbl.COUNT() LOOP
2881 x_variable_content := x_variable_content || l_variable_data_tbl(i).label_content;
2882 END LOOP;
2883 END get_variable_data;
2884 END inv_label_pvt9;