[Home] [Help]
PACKAGE BODY: APPS.INV_LABEL_PVT10
Source
1 PACKAGE BODY INV_LABEL_PVT10 AS
2 /* $Header: INVLA10B.pls 120.5.12020000.3 2012/07/17 06:04:19 yintang ship $ */
3
4 LABEL_B CONSTANT VARCHAR2(50) := '<label';
5 LABEL_E CONSTANT VARCHAR2(50) := '</label>'||fnd_global.local_chr(10);
6 VARIABLE_B CONSTANT VARCHAR2(50) := '<variable name= "';
7 VARIABLE_E CONSTANT VARCHAR2(50) := '</variable>'||fnd_global.local_chr(10);
8 TAG_E CONSTANT VARCHAR2(50) := '>'||fnd_global.local_chr(10);
9 l_debug number;
10
11 /* MMTT_TYPE : transaction is MTL_MATERIAL_TRANSACTIONS_TEMP ID */
12 /* MTI_TYPE : transaction is MTL_TRANSACTION_INTERFACE ID */
13 /* MTRL_TYPE : transaction is MTL_TXN_REQUEST_LINES ID */
14 /* WFS_TYPE : transaction is WIP_FLOW_SCHEDULES ID */
15
16 MMTT_TYPE CONSTANT NUMBER := 1;
17 MTI_TYPE CONSTANT NUMBER := 2;
18 MTRL_TYPE CONSTANT NUMBER := 3;
19 WFS_TYPE CONSTANT NUMBER := 4;
20
21 G_DATE_FORMAT_MASK VARCHAR2(20) := INV_LABEL.G_DATE_FORMAT_MASK;
22
23 TYPE output_rec is RECORD
24 (
25 datbuf VARCHAR2(240)
26 );
27
28 TYPE output_tbl_type IS TABLE OF output_rec INDEX BY BINARY_INTEGER;
29
30 PROCEDURE trace(p_message VARCHAR2) IS
31 BEGIN
32 INV_LABEL.trace(p_message, 'LABEL_FLOW_CONT');
33 END trace;
34
35 PROCEDURE get_data(
36 x_out_tbl IN OUT NOCOPY output_tbl_type,
37 p_wip_entity_id wip_flow_schedules.wip_entity_id%TYPE,
38 p_schedule_number wip_flow_schedules.schedule_number%TYPE,
39 p_inventory_item_id mtl_system_items.inventory_item_id%TYPE,
40 p_organization_id mtl_system_items.organization_id%TYPE,
41 p_subinventory_code wip_flow_schedules.completion_subinventory%TYPE,
42 p_locator_id wip_flow_schedules.completion_locator_id%TYPE
43 ) IS
44
45 i NUMBER;
46 l_wip_entity_id wip_flow_schedules.wip_entity_id%TYPE := NULL;
47
48 CURSOR item_curs IS
49 SELECT
50 NULL cost_group,
51 item.concatenated_segments,
52 item.description,
53 item.attribute1,
54 item.attribute10,
55 item.attribute11,
56 item.attribute12,
57 item.attribute13,
58 item.attribute14,
59 item.attribute15,
60 item.attribute2,
61 item.attribute3,
62 item.attribute4,
63 item.attribute5,
64 item.attribute6,
65 item.attribute7,
66 item.attribute8,
67 item.attribute9,
68 item.attribute_category,
69 item_poh. hazard_class,
70 item_mir.revision
71 FROM MTL_SYSTEM_ITEMS_KFV item,
72 PO_HAZARD_CLASSES item_poh,
73 MTL_ITEM_REVISIONS item_mir
74 WHERE item.organization_id = p_organization_id
75 AND item.inventory_item_id = p_inventory_item_id
76 AND item.hazard_class_id = item_poh.hazard_class_id (+)
77 AND item.organization_Id = item_mir.organization_id(+)
78 AND item.inventory_item_id = item_mir.inventory_item_id(+);
79
80 CURSOR flow_curs IS
81 SELECT
82 wflow.bom_revision,
83 Wflow.build_sequence,
84 wflow_loc.concatenated_segments completion_location,
85 NVL(p_subinventory_code ,wflow.completion_subinventory),
86 wflow.end_item_unit_number,
87 wflow.attribute1,
88 wflow.attribute10,
89 wflow.attribute11,
90 wflow.attribute12,
91 wflow.attribute13,
92 wflow.attribute14,
93 wflow.attribute15,
94 wflow.attribute2,
95 wflow.attribute3,
96 wflow.attribute4,
97 wflow.attribute5,
98 wflow.attribute6,
99 wflow.attribute7,
100 wflow.attribute8,
101 wflow.attribute9,
102 wflow.attribute_category,
103 Wflow.created_by,
104 to_char(wflow.creation_date,G_DATE_FORMAT_MASK),
105 to_char(wflow.last_update_date,G_DATE_FORMAT_MASK),
106 wflow.last_updated_by,
107 wflow.planned_quantity,
108 wflow.quantity_completed,
109 wflow.schedule_number,
110 to_char(wflow.scheduled_start_date,G_DATE_FORMAT_MASK),
111 wflow.status,
112 wflow_mkc.kanban_card_number,
113 wflow.material_account,
114 wflow.mps_net_quantity,
115 to_char(wflow.mps_scheduled_completion_date,G_DATE_FORMAT_MASK),
116 wflow.quantity_scrapped,
117 wflow.routing_revision,
118 to_char(wflow.scheduled_completion_date,G_DATE_FORMAT_MASK),
119 wflow_we.wip_entity_name,
120 Wflow_wl.line_code,
121 wflow.End_item_unit_number,
122 wflow.Current_line_operation
123 FROM WIP_FLOW_SCHEDULES wflow,
124 WIP_ENTITIES wflow_we,
125 MTL_ITEM_LOCATIONS_KFV wflow_loc,
126 MTL_KANBAN_CARDS wflow_mkc,
127 WIP_LINES wflow_wl
128 WHERE wflow.wip_entity_id = l_wip_entity_id
129 AND Wflow.wip_entity_id = wflow_we.wip_entity_id
130 AND NVL(p_locator_id,wflow.completion_locator_id) = wflow_loc.inventory_location_id(+)
131 AND Wflow.kanban_card_id = wflow_mkc.kanban_card_id(+)
132 AND Wflow.line_id = wflow_wl.line_id(+);
133
134 CURSOR flow_entity_curs IS
135 SELECT wip_entity_id
136 FROM WIP_FLOW_SCHEDULES
137 WHERE wip_entity_id = p_wip_entity_id ;
138
139
140 BEGIN
141 l_debug := INV_LABEL.l_debug;
142 IF (l_debug = 1) THEN
143 trace('**In get_data() **.');
144 trace(' p_schedule_number : ' || p_schedule_number);
145 trace(' p_wip_entity_Id : ' || p_wip_entity_id);
146 trace(' p_inventory_item_id : ' || p_inventory_item_id);
147 trace(' p_organization_id : ' || p_organization_id);
148 trace('** Get Item data .., ');
149 END IF;
150 OPEN item_curs;
151 FETCH item_curs INTO
152 x_out_tbl(1710).datbuf, x_out_tbl(1711).datbuf, x_out_tbl(1712).datbuf,
153 x_out_tbl(1713).datbuf, x_out_tbl(1714).datbuf, x_out_tbl(1715).datbuf,
154 x_out_tbl(1716).datbuf, x_out_tbl(1717).datbuf, x_out_tbl(1718).datbuf,
155 x_out_tbl(1719).datbuf, x_out_tbl(1720).datbuf, x_out_tbl(1721).datbuf,
156 x_out_tbl(1722).datbuf, x_out_tbl(1723).datbuf, x_out_tbl(1724).datbuf,
157 x_out_tbl(1725).datbuf, x_out_tbl(1726).datbuf, x_out_tbl(1727).datbuf,
158 x_out_tbl(1728).datbuf, x_out_tbl(1729).datbuf, x_out_tbl(1730).datbuf;
159
160 CLOSE item_curs;
161 /*
162 FOR i in 1710..1730 LOOP
163 IF (x_out_tbl.EXISTS(i) AND x_out_tbl(i).datbuf IS NOT NULL ) THEN
164 IF (l_debug = 1) THEN
165 trace('x_out_tbl('||i||')='|| x_out_tbl(i).datbuf );
166 END IF;
167 END IF;
168 END LOOP;
169 */
170 IF (l_debug = 1) THEN
171 trace('** Get WIP Flow Schedule entity ID .., ');
172 END IF;
173 OPEN flow_entity_curs;
174 FETCH flow_entity_curs INTO l_wip_entity_id;
175 CLOSE flow_entity_curs;
176 if (p_schedule_number IS NOT NULL) THEN
177 -- assign schedule number to x_out_tbl(2027)
178 x_out_tbl(2027).datbuf := p_schedule_number;
179
180 BEGIN
181 SELECT wip_entity_id INTO l_wip_entity_id
182 FROM WIP_FLOW_SCHEDULES
183 WHERE schedule_number = p_schedule_number ;
184
185 EXCEPTION
186 WHEN OTHERS THEN
187 IF (l_debug = 1) THEN
188 trace('No entry for WIP_FLOW_SCHEDULE schedule_number : ' || p_schedule_number);
189 END IF;
190
191 END;
192 END IF;
193
194 IF (l_wip_entity_id IS NOT NULL) THEN
195 IF (l_debug = 1) THEN
196 trace('** Retrieving WIP Flow Schedue date for entity ID : '|| l_wip_entity_id);
197 END IF;
198 OPEN flow_curs;
199 FETCH flow_curs INTO
200 x_out_tbl(2000).datbuf, x_out_tbl(2001).datbuf, x_out_tbl(2002).datbuf,
201 x_out_tbl(2003).datbuf, x_out_tbl(2004).datbuf, x_out_tbl(2005).datbuf,
202 x_out_tbl(2006).datbuf, x_out_tbl(2007).datbuf, x_out_tbl(2008).datbuf,
203 x_out_tbl(2009).datbuf, x_out_tbl(2010).datbuf, x_out_tbl(2011).datbuf,
204 x_out_tbl(2012).datbuf, x_out_tbl(2013).datbuf, x_out_tbl(2014).datbuf,
205 x_out_tbl(2015).datbuf, x_out_tbl(2016).datbuf, x_out_tbl(2017).datbuf,
206 x_out_tbl(2018).datbuf, x_out_tbl(2019).datbuf, x_out_tbl(2020).datbuf,
207 x_out_tbl(2021).datbuf, x_out_tbl(2022).datbuf, x_out_tbl(2023).datbuf,
208 x_out_tbl(2024).datbuf, x_out_tbl(2025).datbuf, x_out_tbl(2026).datbuf,
209 x_out_tbl(2027).datbuf, x_out_tbl(2028).datbuf, x_out_tbl(2029).datbuf,
210 x_out_tbl(2030).datbuf, x_out_tbl(2031).datbuf, x_out_tbl(2032).datbuf,
211 x_out_tbl(2033).datbuf, x_out_tbl(2034).datbuf, x_out_tbl(2035).datbuf,
212 x_out_tbl(2036).datbuf, x_out_tbl(2037).datbuf, x_out_tbl(2038).datbuf,
213 x_out_tbl(2039).datbuf, x_out_tbl(2040).datbuf;
214 CLOSE flow_curs;
215
216 /*
217 -- Wip Flow Schedule
218 FOR i in 2000..2040 LOOP
219 IF (x_out_tbl.EXISTS(i) AND x_out_tbl(i).datbuf IS NOT NULL ) THEN
220 IF (l_debug = 1) THEN
221 trace('x_out_tbl('||i||')='|| x_out_tbl(i).datbuf );
222 END IF;
223 END IF;
224 END LOOP;
225 */
226 END IF;
227
228 END get_data ;
229 /*=================================================================*/
230 PROCEDURE get_data_bom_bill_header(
231 x_out_tbl IN OUT NOCOPY output_tbl_type,
232 p_inventory_item_id bom_bill_of_materials.assembly_item_id%TYPE,
233 p_organization_id bom_bill_of_materials.organization_id%TYPE,
234 p_alternate_bom_designator bom_bill_of_materials.alternate_bom_designator%TYPE
235 )IS
236
237 CURSOR bom_header_curs IS
238 SELECT
239 --Bom_hdr.bill_sequence_id,
240 --Bom_hdr.assembly_item_id,
241 bom_hdr.attribute1,
242 bom_hdr.attribute2,
243 bom_hdr.attribute3,
244 bom_hdr.attribute4,
245 bom_hdr.attribute5,
246 bom_hdr.attribute6,
247 bom_hdr.attribute7,
248 bom_hdr.attribute8,
249 bom_hdr.attribute9,
250 bom_hdr.attribute10,
251 bom_hdr.attribute11,
252 bom_hdr.attribute12,
253 bom_hdr.attribute13,
254 bom_hdr.attribute14,
255 bom_hdr.attribute15,
256 bom_hdr.attribute_category,
257 bom_hdr_pp.name project_name,
258 bom_hdr_pt.task_name task_name,
259 Bom_hdr.specific_assembly_comment
260 FROM BOM_BILL_OF_MATERIALS bom_hdr,
261 PA_PROJECTS bom_hdr_pp, PA_TASKS bom_hdr_pt
262 WHERE bom_hdr.assembly_item_id = p_inventory_item_id
263 AND bom_hdr.organization_id = p_organization_id
264 AND nvl(bom_hdr.alternate_bom_designator, '@@@') = nvl(p_alternate_bom_designator, '@@@')
265 AND bom_hdr.project_id = bom_hdr_pp.project_id(+)
266 AND bom_hdr.task_id = bom_hdr_pt.task_id(+);
267
268 BEGIN
269 IF (l_debug = 1) THEN
270 trace('**In get_data_bom_bill_header() **');
271 END IF;
272 OPEN bom_header_curs;
273 FETCH bom_header_curs INTO
274 x_out_tbl(2075).datbuf, x_out_tbl(2076).datbuf, x_out_tbl(2077).datbuf,
275 x_out_tbl(2078).datbuf, x_out_tbl(2079).datbuf, x_out_tbl(2080).datbuf,
276 x_out_tbl(2081).datbuf, x_out_tbl(2082).datbuf, x_out_tbl(2083).datbuf,
277 x_out_tbl(2084).datbuf, x_out_tbl(2085).datbuf, x_out_tbl(2086).datbuf,
278 x_out_tbl(2087).datbuf, x_out_tbl(2088).datbuf, x_out_tbl(2089).datbuf,
279 x_out_tbl(2090).datbuf, x_out_tbl(2091).datbuf, x_out_tbl(2092).datbuf,
280 x_out_tbl(2093).datbuf;
281
282 CLOSE bom_header_curs;
283 /*
284 FOR i in 2075..2093 LOOP
285 IF (x_out_tbl.EXISTS(i) AND x_out_tbl(i).datbuf IS NOT NULL ) THEN
286 IF (l_debug = 1) THEN
287 trace('x_out_tbl('||i||')='|| x_out_tbl(i).datbuf );
288 END IF;
289 END IF;
290 END LOOP;
291 */
292
293 END get_data_bom_bill_header ;
294
295 /*=================================================================*/
296 PROCEDURE get_data_bom_routing(
297 x_out_tbl IN OUT NOCOPY output_tbl_type,
298 p_inventory_item_id bom_operational_routings.assembly_item_id%TYPE,
299 p_organization_id bom_operational_routings.organization_id%TYPE,
300 p_alternate_routing_designator bom_operational_routings.alternate_routing_designator%TYPE
301 )IS
302
303 CURSOR bom_routing_curs IS
304 SELECT
305 --bom_rte.routing_sequence_id,
306 --bom_rte.assembly_item_id,
307 bom_rte.attribute1,
308 bom_rte.attribute2,
309 bom_rte.attribute3,
310 bom_rte.attribute4,
311 bom_rte.attribute5,
312 bom_rte.attribute6,
313 bom_rte.attribute7,
314 bom_rte.attribute8,
315 bom_rte.attribute9,
316 bom_rte.attribute10,
317 bom_rte.attribute11,
318 bom_rte.attribute12,
319 bom_rte.attribute13,
320 bom_rte.attribute14,
321 bom_rte.attribute15,
322 bom_rte.attribute_category,
323 bom_rte.routing_comment,
324 bom_rte_wl.line_code line_code,
325 bom_rte.total_product_cycle_time,
326 bom_rte_pp.name project_name,
327 bom_rte_pt.task_name
328 FROM BOM_OPERATIONAL_ROUTINGS bom_rte,
329 PA_PROJECTS bom_rte_pp, PA_TASKS bom_rte_pt,
330 WIP_LINES bom_rte_wl
331 WHERE bom_rte.assembly_item_id = p_inventory_item_id
332 AND bom_rte.organization_id = p_organization_id
333 AND nvl(bom_rte.alternate_routing_designator, '@@@') = nvl(p_alternate_routing_designator, '@@@')
334 AND bom_rte.project_id = bom_rte_pp.project_id(+)
335 AND bom_rte.task_id = bom_rte_pt.task_id(+)
336 AND bom_rte.line_id = bom_rte_wl.line_id(+);
337
338 BEGIN
339 IF (l_debug = 1) THEN
340 trace('**In get_data_bom_routing() **');
341 END IF;
342 OPEN bom_routing_curs;
343 FETCH bom_routing_curs INTO
344 x_out_tbl(2094).datbuf, x_out_tbl(2095).datbuf, x_out_tbl(2096).datbuf,
345 x_out_tbl(2097).datbuf, x_out_tbl(2098).datbuf, x_out_tbl(2099).datbuf,
346 x_out_tbl(2100).datbuf, x_out_tbl(2101).datbuf, x_out_tbl(2102).datbuf,
347 x_out_tbl(2103).datbuf, x_out_tbl(2104).datbuf, x_out_tbl(2105).datbuf,
348 x_out_tbl(2106).datbuf, x_out_tbl(2107).datbuf, x_out_tbl(2108).datbuf,
349 x_out_tbl(2109).datbuf, x_out_tbl(2110).datbuf, x_out_tbl(2111).datbuf,
350 x_out_tbl(2112).datbuf, x_out_tbl(2113).datbuf, x_out_tbl(2114).datbuf;
351
352 CLOSE bom_routing_curs;
353 /*
354 FOR i in 2094..2114 LOOP
355 IF (x_out_tbl.EXISTS(i) AND x_out_tbl(i).datbuf IS NOT NULL ) THEN
356 IF (l_debug = 1) THEN
357 trace('x_out_tbl('||i||')='|| x_out_tbl(i).datbuf );
358 END IF;
359 END IF;
360 END LOOP;
361 */
362 END get_data_bom_routing ;
363
364 /*=================================================================*/
365 PROCEDURE get_data_kanban(
366 x_out_tbl IN OUT NOCOPY output_tbl_type,
367 p_kanban_card_id IN mtl_kanban_cards.kanban_card_id%TYPE
368 )IS
369
370 CURSOR kanban_curs IS
371 SELECT
372 kanban.attribute1,
373 kanban.attribute2,
374 kanban.attribute3,
375 kanban.attribute4,
376 Kanban.attribute5,
377 kanban.attribute6,
378 kanban.attribute7,
379 kanban.attribute8,
380 kanban.attribute9,
381 kanban.attribute10,
382 kanban.attribute11,
383 kanban.attribute12,
384 kanban.attribute13,
385 kanban.attribute14,
386 kanban.attribute15,
387 kanban.attribute_category,
388 kanban.card_status,
389 kanban.kanban_card_type,
390 kanban.created_by,
391 to_char(kanban.creation_date,G_DATE_FORMAT_MASK),
392 kanban.last_updated_by,
393 to_char(kanban.last_update_date,G_DATE_FORMAT_MASK),
394 kanban_loc.concatenated_segments locator,
395 kanban.pull_sequence_id,
396 kanban.kanban_size,
397 kanban_sloc.concatenated_segments source_locator,
398 kanban_sorg.organization_code source_organization_code,
399 kanban.source_subinventory,
400 kanban.Source_type,
401 pv.vendor_name supplier,
402 kanban.supply_status,
403 kanban.kanban_card_number
404 FROM MTL_KANBAN_CARDS kanban,
405 MTL_ITEM_LOCATIONS_KFV kanban_loc,
406 MTL_ITEM_LOCATIONS_KFV kanban_sloc,
407 MTL_PARAMETERS kanban_sorg,
408 MTL_PARAMETERS kanban_org,
409 PO_VENDORS pv
410 WHERE kanban.kanban_card_id = p_kanban_card_id
411 AND kanban.locator_id = kanban_loc.inventory_location_id(+)
412 AND kanban.source_locator_id = kanban_sloc.inventory_location_id(+)
413 AND kanban.source_organization_id = kanban_sorg.organization_id
414 AND kanban.organization_id = kanban_org.organization_id
415 AND kanban.supplier_id = pv.vendor_id(+);
416
417
418 BEGIN
419 IF (l_debug = 1) THEN
420 trace('**In get_data_kanban() **');
421 END IF;
422 OPEN kanban_curs;
423 FETCH kanban_curs INTO
424 x_out_tbl(1507).datbuf, x_out_tbl(1508).datbuf, x_out_tbl(1509).datbuf,
425 x_out_tbl(1510).datbuf, x_out_tbl(1511).datbuf, x_out_tbl(1512).datbuf,
426 x_out_tbl(1513).datbuf, x_out_tbl(1514).datbuf, x_out_tbl(1515).datbuf,
427 x_out_tbl(1516).datbuf, x_out_tbl(1517).datbuf, x_out_tbl(1518).datbuf,
428 x_out_tbl(1519).datbuf, x_out_tbl(1520).datbuf, x_out_tbl(1521).datbuf,
429 x_out_tbl(1522).datbuf, x_out_tbl(1523).datbuf, x_out_tbl(1524).datbuf,
430 x_out_tbl(1525).datbuf, x_out_tbl(1526).datbuf, x_out_tbl(1527).datbuf,
431 x_out_tbl(1528).datbuf, x_out_tbl(1529).datbuf, x_out_tbl(1530).datbuf,
432 x_out_tbl(1531).datbuf, x_out_tbl(1532).datbuf, x_out_tbl(1533).datbuf,
433 x_out_tbl(1534).datbuf, x_out_tbl(1535).datbuf, x_out_tbl(1536).datbuf,
434 x_out_tbl(1537).datbuf, x_out_tbl(1538).datbuf;
435
436 CLOSE kanban_curs;
437 /*
438 FOR i in 1507..1538 LOOP
439 IF (x_out_tbl.EXISTS(i) AND x_out_tbl(i).datbuf IS NOT NULL ) THEN
440 IF (l_debug = 1) THEN
441 trace('x_out_tbl('||i||')='|| x_out_tbl(i).datbuf );
442 END IF;
443 END IF;
444 END LOOP;
445 */
446 END get_data_kanban ;
447
448 /*=================================================================*/
449 PROCEDURE get_data_lot(
450 x_out_tbl IN OUT NOCOPY output_tbl_type,
451 p_lot_number mtl_lot_numbers.lot_number%TYPE
452 )IS
453
454 CURSOR lot_curs IS
455 SELECT
456 lot.lot_number,
457 lot.age,
458 to_char(lot.best_by_date,G_DATE_FORMAT_MASK),
459 lot.c_attribute1,
460 lot.c_attribute10,
461 lot.c_attribute11,
462 lot.c_attribute12,
463 lot.c_attribute13,
464 lot.c_attribute14,
465 lot.c_attribute15,
466 lot.c_attribute16,
467 lot.c_attribute17,
468 lot.c_attribute18,
469 lot.c_attribute19,
470 lot.c_attribute2,
471 lot.c_attribute20,
472 lot.c_attribute3,
473 lot.c_attribute4,
474 lot.c_attribute5,
475 lot.c_attribute6,
476 lot.c_attribute7,
477 lot.c_attribute8,
478 lot.c_attribute9,
479 lot.attribute_category,
480 to_char(lot.change_date,G_DATE_FORMAT_MASK),
481 lot.color,
482 lot.d_attribute1,
483 lot.d_attribute10,
484 lot.d_attribute2,
485 lot.d_attribute3,
486 lot.d_attribute4,
487 lot.d_attribute5,
488 lot.d_attribute6,
489 lot.d_attribute7,
490 lot.d_attribute8,
491 lot.d_attribute9,
492 lot.date_code,
493 lot.grade_code,
494 lot.item_size,
495 lot.length,
496 lot.length_uom,
497 to_char(lot.maturity_date,G_DATE_FORMAT_MASK),
498 lot.n_attribute1,
499 lot.n_attribute10,
500 lot.n_attribute2,
501 lot.n_attribute3,
502 lot.n_attribute4,
503 lot.n_attribute5,
504 lot.n_attribute6,
505 lot.n_attribute7,
506 lot.n_attribute8,
507 lot.n_attribute9,
508 to_char(lot.origination_date,G_DATE_FORMAT_MASK),
509 lot.place_of_origin,
510 lot.recycled_content,
511 to_char(lot.retest_date,G_DATE_FORMAT_MASK),
512 lot.thickness,
513 lot.thickness_uom,
514 lot.vendor_name supplier,
515 lot.supplier_lot_number,
516 lot.volume,
517 lot.volume_uom,
518 lot.width,
519 lot.width_uom,
520 to_char(lot.expiration_date,G_DATE_FORMAT_MASK),
521 lot_status.status_code
522 FROM MTL_LOT_NUMBERS lot,
523 MTL_MATERIAL_STATUSES_VL lot_status
524 WHERE lot.lot_number = p_lot_number
525 AND lot.status_id = lot_status.status_id (+);
526
527 BEGIN
528 IF (l_debug = 1) THEN
529 trace('**In get_data_lot() **');
530 END IF;
531 OPEN lot_curs;
532 FETCH lot_curs INTO
533 x_out_tbl(1539).datbuf, x_out_tbl(1540).datbuf, x_out_tbl(1541).datbuf,
534 x_out_tbl(1542).datbuf, x_out_tbl(1543).datbuf, x_out_tbl(1544).datbuf,
535 x_out_tbl(1545).datbuf, x_out_tbl(1546).datbuf, x_out_tbl(1547).datbuf,
536 x_out_tbl(1548).datbuf, x_out_tbl(1549).datbuf, x_out_tbl(1550).datbuf,
537 x_out_tbl(1551).datbuf, x_out_tbl(1552).datbuf, x_out_tbl(1553).datbuf,
538 x_out_tbl(1554).datbuf, x_out_tbl(1555).datbuf, x_out_tbl(1556).datbuf,
539 x_out_tbl(1557).datbuf, x_out_tbl(1558).datbuf, x_out_tbl(1559).datbuf,
540 x_out_tbl(1560).datbuf, x_out_tbl(1561).datbuf, x_out_tbl(1562).datbuf,
541 x_out_tbl(1563).datbuf, x_out_tbl(1564).datbuf,
542 x_out_tbl(1567).datbuf, x_out_tbl(1568).datbuf,
543 x_out_tbl(1569).datbuf, x_out_tbl(1570).datbuf, x_out_tbl(1571).datbuf,
544 x_out_tbl(1572).datbuf, x_out_tbl(1573).datbuf, x_out_tbl(1574).datbuf,
545 x_out_tbl(1575).datbuf, x_out_tbl(1576).datbuf, x_out_tbl(1577).datbuf,
546 x_out_tbl(1578).datbuf, x_out_tbl(1579).datbuf,
547 x_out_tbl(1581).datbuf, x_out_tbl(1582).datbuf, x_out_tbl(1583).datbuf,
548 x_out_tbl(1584).datbuf, x_out_tbl(1585).datbuf, x_out_tbl(1586).datbuf,
549 x_out_tbl(1587).datbuf, x_out_tbl(1588).datbuf, x_out_tbl(1589).datbuf,
550 x_out_tbl(1590).datbuf, x_out_tbl(1591).datbuf, x_out_tbl(1592).datbuf,
551 x_out_tbl(1593).datbuf, x_out_tbl(1594).datbuf, x_out_tbl(1595).datbuf,
552 x_out_tbl(1596).datbuf, x_out_tbl(1597).datbuf, x_out_tbl(1598).datbuf,
553 x_out_tbl(1599).datbuf, x_out_tbl(1600).datbuf, x_out_tbl(1601).datbuf,
554 x_out_tbl(1602).datbuf, x_out_tbl(1603).datbuf, x_out_tbl(1604).datbuf,
555 x_out_tbl(1605).datbuf, x_out_tbl(1606).datbuf, x_out_tbl(1607).datbuf;
556
557 IF lot_curs%NOTFOUND THEN
558 trace('New Lot, just populate lot number');
559 x_out_tbl(1539).datbuf := p_lot_number;
560 END IF;
561 CLOSE lot_curs;
562
563 /*
564 FOR i in 1539..1607 LOOP
565 IF (x_out_tbl.EXISTS(i) AND x_out_tbl(i).datbuf IS NOT NULL ) THEN
566 IF (l_debug = 1) THEN
567 trace('x_out_tbl('||i||')='|| x_out_tbl(i).datbuf );
568 END IF;
569 END IF;
570 END LOOP;
571 */
572 END get_data_lot ;
573
574 /*=================================================================*/
575 PROCEDURE get_data_serial(
576 x_out_tbl IN OUT NOCOPY output_tbl_type,
577 p_inventory_item_id mtl_serial_numbers.inventory_item_id%TYPE,
578 p_serial_number mtl_serial_numbers.serial_number%TYPE
579 )IS
580
581 CURSOR serial_curs IS
582 SELECT
583 serial.c_attribute1,
584 serial.c_attribute2,
585 serial.c_attribute3,
586 serial.c_attribute4,
587 serial.c_attribute5,
588 serial.c_attribute6,
589 serial.c_attribute7,
590 serial.c_attribute8,
591 serial.c_attribute9,
592 serial.c_attribute10,
593 serial.c_attribute11,
594 serial.c_attribute12,
595 serial.c_attribute13,
596 serial.c_attribute14,
597 serial.c_attribute15,
598 serial.c_attribute16,
599 serial.c_attribute17,
600 serial.c_attribute18,
601 serial.c_attribute19,
602 serial.c_attribute20,
603 serial.attribute_category,
604 to_date(serial.completion_date,G_DATE_FORMAT_MASK),
605 serial.cycles_since_mark,
606 serial.cycles_since_new,
607 serial.cycles_since_overhaul,
608 serial.cycles_since_repair,
609 serial.cycles_since_visit,
610 serial.d_attribute1,
611 serial.d_attribute10,
612 serial.d_attribute2,
613 serial.d_attribute3,
614 serial.d_attribute4,
615 serial.d_attribute5,
616 serial.d_attribute6,
617 serial.d_attribute7,
618 serial.d_attribute8,
619 serial.d_attribute9,
620 serial.fixed_asset_tag,
621 to_char(serial.initialization_date,G_DATE_FORMAT_MASK),
622 serial.n_attribute1,
623 serial.n_attribute2,
624 serial.n_attribute3,
625 serial.n_attribute4,
626 serial.n_attribute5,
627 serial.n_attribute6,
628 serial.n_attribute7,
629 serial.n_attribute8,
630 serial.n_attribute9,
631 serial.n_attribute10,
632 serial.number_of_repairs,
633 to_char(serial.origination_date,G_DATE_FORMAT_MASK),
634 serial.time_since_mark,
635 serial.time_since_new,
636 serial.time_since_overhaul,
637 serial.time_since_repair,
638 serial.time_since_visit,
639 serial.vendor_serial_number,
640 serial.serial_number,
641 serial_status.status_code
642 FROM MTL_SERIAL_NUMBERS serial,
643 MTL_MATERIAL_STATUSES_VL serial_status
644 WHERE serial.inventory_item_id = p_inventory_item_id
645 AND serial.serial_number = p_serial_number
646 AND serial.status_id = serial_status.status_id (+);
647
648 BEGIN
649 IF (l_debug = 1) THEN
650 trace('**In get_data_serial() **');
651 END IF;
652 OPEN serial_curs;
653 FETCH serial_curs INTO
654 x_out_tbl(1609).datbuf, x_out_tbl(1610).datbuf, x_out_tbl(1611).datbuf,
655 x_out_tbl(1612).datbuf, x_out_tbl(1613).datbuf, x_out_tbl(1614).datbuf,
656 x_out_tbl(1615).datbuf, x_out_tbl(1616).datbuf, x_out_tbl(1617).datbuf,
657 x_out_tbl(1618).datbuf, x_out_tbl(1619).datbuf, x_out_tbl(1620).datbuf,
658 x_out_tbl(1621).datbuf,
659 x_out_tbl(1622).datbuf, x_out_tbl(1623).datbuf, x_out_tbl(1624).datbuf,
660 x_out_tbl(1625).datbuf, x_out_tbl(1626).datbuf, x_out_tbl(1627).datbuf,
661 x_out_tbl(1628).datbuf, x_out_tbl(1629).datbuf, x_out_tbl(1630).datbuf,
662 x_out_tbl(1631).datbuf, x_out_tbl(1632).datbuf, x_out_tbl(1633).datbuf,
663 x_out_tbl(1634).datbuf, x_out_tbl(1635).datbuf, x_out_tbl(1636).datbuf,
664 x_out_tbl(1637).datbuf, x_out_tbl(1638).datbuf, x_out_tbl(1639).datbuf,
665 x_out_tbl(1640).datbuf, x_out_tbl(1641).datbuf, x_out_tbl(1642).datbuf,
666 x_out_tbl(1643).datbuf, x_out_tbl(1644).datbuf, x_out_tbl(1645).datbuf,
667 x_out_tbl(1646).datbuf, x_out_tbl(1647).datbuf, x_out_tbl(1648).datbuf,
668 x_out_tbl(1649).datbuf, x_out_tbl(1650).datbuf,
669 x_out_tbl(1651).datbuf, x_out_tbl(1652).datbuf,
670 x_out_tbl(1653).datbuf, x_out_tbl(1654).datbuf, x_out_tbl(1655).datbuf,
671 x_out_tbl(1656).datbuf, x_out_tbl(1657).datbuf, x_out_tbl(1658).datbuf,
672 x_out_tbl(1659).datbuf, x_out_tbl(1660).datbuf, x_out_tbl(1661).datbuf,
673 x_out_tbl(1662).datbuf, x_out_tbl(1663).datbuf, x_out_tbl(1664).datbuf,
674 x_out_tbl(1665).datbuf, x_out_tbl(1666).datbuf, x_out_tbl(1667).datbuf;
675
676
677 CLOSE serial_curs;
678 /*
679 FOR i in 1609..1667 LOOP
680 IF (x_out_tbl.EXISTS(i) AND x_out_tbl(i).datbuf IS NOT NULL ) THEN
681 IF (l_debug = 1) THEN
682 trace('x_out_tbl('||i||')='|| x_out_tbl(i).datbuf );
683 END IF;
684 END IF;
685 END LOOP;
686 */
687
688 END get_data_serial ;
689
690 /*=================================================================*/
691 PROCEDURE get_data_LPN(
692 x_out_tbl IN OUT NOCOPY output_tbl_type,
693 p_lpn_id wms_license_plate_numbers.lpn_id%TYPE,
694 p_revision wms_lpn_contents.revision%TYPE,
695 p_lot_number wms_lpn_contents.lot_number%TYPE,
696 p_serial_number wms_lpn_contents.serial_number%TYPE,
697 p_inventory_item_id wms_lpn_contents.inventory_item_id%TYPE
698 )IS
699
700 CURSOR lpn_curs IS
701 SELECT
702 lpn.license_plate_number license_plate_number,
703 lpn_msik1.concatenated_segments lpn_container_item,
704 lpn.attribute1,
705 lpn.attribute2,
706 lpn.attribute3,
707 lpn.attribute4,
708 lpn.attribute5,
709 lpn.attribute6,
710 lpn.attribute7,
711 lpn.attribute8,
712 lpn.attribute9,
713 lpn.attribute10,
714 lpn.attribute11,
715 lpn.attribute12,
716 lpn.attribute13,
717 lpn.attribute14,
718 lpn.attribute15,
719 lpn.attribute_category,
720 lpn.gross_weight gross_weight,
721 lpn.gross_weight_uom_code gross_weight_uom,
722 0 number_of_total,
723 lpn.tare_weight tare_weight,
724 lpn.tare_weight_uom_code tare_weight_uom,
725 0 total_of_total,
726 lpn.content_volume volume,
727 lpn.content_volume_uom_code volume_uom,
728 lpn_mp.organization_code organization,
729 lpn_msik2.concatenated_segments item,
730 lpn_msik2.description item_description,
731 lpn_wlc.revision revision,
732 lpn_wlc.lot_number lot,
733 NVL(lpn_wlc.serial_number, p_serial_number) serial_number,
734 decode(p_serial_number, NULL, lpn_wlc.quantity,
735 decode(lpn_wlc.serial_summary_entry, 1, 1, lpn_wlc.quantity)) quantity,
736 lpn_wlc.uom_code
737 FROM wms_license_plate_numbers lpn,
738 wms_license_plate_numbers lpn_pLpn,
739 mtl_system_items_kfv lpn_msik1,
740 mtl_system_items_kfv lpn_msik2,
741 mtl_parameters lpn_mp,
742 wms_lpn_contents lpn_wlc
743 WHERE lpn.lpn_id = p_lpn_id
744 AND lpn.parent_lpn_id = lpn_pLpn.lpn_id(+)
745 AND lpn_wlc.parent_lpn_id(+) = p_lpn_id
746 AND nvl(lpn_wlc.revision, '$$$') = nvl(p_revision, nvl(lpn_wlc.revision, '$$$'))
747 AND nvl(lpn_wlc.lot_number, '$$$') = nvl(p_lot_number, nvl(lpn_wlc.lot_number, '$$$'))
748 --AND nvl(lpn_wlc.serial_number,'$$$') = nvl(p_serial_number,nvl(lpn_wlc.serial_number,'$$$'))
749 AND lpn_wlc.inventory_item_id = p_inventory_item_id
750 AND lpn_msik1.inventory_item_id (+) = lpn.inventory_item_id
751 AND lpn_msik1.organization_id (+) = lpn.organization_id
752 AND lpn_mp.organization_id = lpn.organization_id
753 AND lpn_msik2.inventory_item_id(+) = lpn_wlc.inventory_item_id
754 AND lpn_msik2.organization_id(+) = lpn_wlc.organization_id;
755
756
757 BEGIN
758 IF (l_debug = 1) THEN
759 trace('**In get_data_LPN() **');
760 END IF;
761 OPEN lpn_curs;
762 FETCH lpn_curs INTO
763 x_out_tbl(2041).datbuf, x_out_tbl(2042).datbuf, x_out_tbl(2043).datbuf,
764 x_out_tbl(2044).datbuf, x_out_tbl(2045).datbuf, x_out_tbl(2046).datbuf,
765 x_out_tbl(2047).datbuf, x_out_tbl(2048).datbuf, x_out_tbl(2049).datbuf,
766 x_out_tbl(2050).datbuf, x_out_tbl(2051).datbuf, x_out_tbl(2052).datbuf,
767 x_out_tbl(2053).datbuf, x_out_tbl(2054).datbuf, x_out_tbl(2055).datbuf,
768 x_out_tbl(2056).datbuf, x_out_tbl(2057).datbuf, x_out_tbl(2058).datbuf,
769 x_out_tbl(2059).datbuf, x_out_tbl(2060).datbuf, x_out_tbl(2061).datbuf,
770 x_out_tbl(2062).datbuf, x_out_tbl(2063).datbuf, x_out_tbl(2064).datbuf,
771 x_out_tbl(2065).datbuf, x_out_tbl(2066).datbuf, x_out_tbl(2067).datbuf,
772 x_out_tbl(2068).datbuf, x_out_tbl(2069).datbuf, x_out_tbl(2070).datbuf,
773 x_out_tbl(2071).datbuf, x_out_tbl(2072).datbuf, x_out_tbl(2073).datbuf,
774 x_out_tbl(2074).datbuf;
775
776
777 /*
778 CLOSE lpn_curs;
779 FOR i in 2041..2074 LOOP
780 IF (x_out_tbl.EXISTS(i) AND x_out_tbl(i).datbuf IS NOT NULL ) THEN
781 IF (l_debug = 1) THEN
782 trace('x_out_tbl('||i||')='|| x_out_tbl(i).datbuf );
783 END IF;
784 END IF;
785 END LOOP;
786 */
787
788 END get_data_LPN ;
789
790 /*=================================================================*/
791 PROCEDURE get_data_sale_header(
792 x_out_tbl IN OUT NOCOPY output_tbl_type,
793 p_header_id oe_order_headers_all.header_id%TYPE ,
794 p_line_id oe_order_lines_all.line_id%TYPE
795
796 )IS
797
798 --
799 -- Modification Start for Bug # - 4418524
800 --
801 -- As part of TCA related changes ra_customers, ra_contacts views are
802 -- obsoleted in R12. The columns fetched from these views are fetched
803 -- from "HZ_PARTIES", "HZ_CUST_ACCOUNTS", "HZ_CUST_ACCOUNT_ROLES",
804 -- "HZ_CUST_ACCOUNTS", "HZ_RELATIONSHIPS".
805 --
806 -- Following declarations are commented.
807 --
808 --l_customer_id ra_customers.customer_id%TYPE;
809 --l_party_id ra_customers.party_id%TYPE;
810 --l_party_number ra_customers.party_number%TYPE;
811 --l_customer_name ra_customers.customer_name%TYPE;
812 --l_invoice_customer_id ra_customers.customer_id%TYPE;
813 --l_deliver_customer_id ra_customers.customer_id%TYPE;
814 --l_ship_to_customer_id ra_customers.customer_id%TYPE;
815 --
816 -- Following declarations are added to replace the above commented
817 -- declarations
818 --
819 l_customer_id hz_cust_accounts.cust_account_id%TYPE;
820 l_party_id hz_parties.party_id%TYPE;
821 l_party_number hz_parties.party_number%TYPE;
822 l_customer_name hz_parties.party_name%TYPE;
823 l_invoice_customer_id hz_cust_accounts.cust_account_id%TYPE;
824 l_deliver_customer_id hz_cust_accounts.cust_account_id%TYPE;
825 l_ship_to_customer_id hz_cust_accounts.cust_account_id%TYPE;
826 --
827 -- Modification End for Bug # - 4418524
828 --
829
830
831 l_sold_from_org_id oe_order_headers_all.sold_from_org_id%TYPE;
832 l_sold_to_org_id oe_order_headers_all.sold_to_org_id%TYPE;
833 l_ship_from_org_id oe_order_headers_all.ship_from_org_id%TYPE;
834 l_ship_to_org_id oe_order_headers_all.ship_to_org_id%TYPE;
835 l_invoice_to_org_id oe_order_headers_all.invoice_to_org_id%TYPE;
836 l_deliver_to_org_id oe_order_headers_all.deliver_to_org_id%TYPE;
837 l_organization_code mtl_parameters.organization_code%TYPE;
838 l_location_id hz_locations.location_id%TYPE;
839 l_location_name hz_cust_site_uses_all.location%TYPE;
840 l_organization_name org_organization_definitions.organization_name%TYPE;
841 l_customer_number hz_cust_accounts.account_number%TYPE;
842 l_sold_from_location_id hz_locations.location_id%TYPE;
843 l_sold_to_location_id hz_locations.location_id%TYPE;
844 l_ship_from_location_id hz_locations.location_id%TYPE;
845 l_ship_to_location_id hz_locations.location_id%TYPE;
846 l_invoice_to_location_id hz_locations.location_id%TYPE;
847 l_deliver_to_location_id hz_locations.location_id%TYPE;
848 l_ship_from_organization_code mtl_parameters.organization_code%TYPE;
849 l_ship_to_organization_code mtl_parameters.organization_code%TYPE;
850 l_sold_from_organization_code mtl_parameters.organization_code%TYPE;
851 l_sold_to_organization_code mtl_parameters.organization_code%TYPE;
852 l_invoice_to_organization_code mtl_parameters.organization_code%TYPE;
853 l_deliver_to_organization_code mtl_parameters.organization_code%TYPE;
854 l_address5 hz_locations.address4%TYPE;
855 l_header_id oe_order_headers_all.header_id%TYPE := p_header_id;
856
857
858 CURSOR oe_header_curs IS
859 SELECT
860 ohead_mp.organization_code,
861 to_char(Ohead.booked_date,G_DATE_FORMAT_MASK),
862 Ohead.credit_card_holder_name,
863 Ohead.credit_card_number,
864 to_char(Ohead.expiration_date,G_DATE_FORMAT_MASK),
865 ohead.attribute1,
866 ohead.attribute10,
867 ohead.attribute11,
868 ohead.attribute12,
869 ohead.attribute13,
870 ohead.attribute14,
871 ohead.attribute15,
872 ohead.attribute2,
873 ohead.attribute3,
874 ohead.attribute4,
875 ohead.attribute5,
876 ohead.attribute6,
877 ohead.attribute7,
878 ohead.attribute8,
879 ohead.attribute9,
880 ohead.global_attribute_category,
881 ohead.global_attribute1,
882 ohead.global_attribute10,
883 ohead.global_attribute11,
884 ohead.global_attribute12,
885 ohead.global_attribute13,
886 ohead.global_attribute14,
887 ohead.global_attribute15,
888 ohead.global_attribute16,
889 ohead.global_attribute17,
890 ohead.global_attribute18,
891 ohead.global_attribute19,
892 ohead.global_attribute2,
893 ohead.global_attribute20,
894 ohead.global_attribute3,
895 ohead.global_attribute4,
896 ohead.global_attribute5,
897 ohead.global_attribute6,
898 ohead.global_attribute7,
899 ohead.global_attribute8,
900 ohead.global_attribute9,
901 Ohead.order_number,
902 Ohead.return_reason_code,
903 to_char(Ohead.ordered_date,G_DATE_FORMAT_MASK),
904 ohead_rcs.customer_name,
905 ohead_rcs.person_first_name,
906 ohead_rcs.person_last_name,
907 ohead_rcs.person_middle_name,
908 ohead_rcs.customer_type,
909 ohead_rcs.customer_id,
910 ohead_rcs.party_id,
911 ohead_rcs.party_number,
912 ohead.sold_from_org_id,
913 ohead.sold_to_org_id,
914 ohead.ship_to_org_id,
915 ohead.ship_from_org_id,
916 ohead.invoice_to_org_id,
917 ohead.deliver_to_org_id
918 FROM OE_ORDER_HEADERS_ALL ohead
919 , MTL_PARAMETERS ohead_mp
920 --
921 -- Modification Start for Bug # - 4418524
922 --
923 -- As part of TCA related changes ra_customers, ra_contacts views are
924 -- obsoleted in R12. The columns fetched from these views are fetched
925 -- from hz_parties and hz_cust_accounts.
926 --
927 -- Following table alias are commented
928 --, ra_customers ohead_rcs
929 --
930 -- Following Queries are added to replace the above commented
931 -- views
932 --
933 , ( SELECT CUST_ACCT.cust_account_id customer_id,
934 PARTY.party_id party_id,
935 PARTY.party_number party_number,
936 SUBSTRB(PARTY.party_name,1,50) customer_name,
937 PARTY.person_first_name person_first_name,
938 PARTY.person_middle_name person_middle_name,
939 PARTY.person_last_name person_last_name,
940 CUST_ACCT.customer_type customer_type
941 FROM hz_parties PARTY
942 , hz_cust_accounts CUST_ACCT
943 WHERE CUST_ACCT.party_id = PARTY.party_id
944 ) ohead_rcs
945 --
946 -- Modification End for Bug # - 4418524
947 --
948 WHERE ohead.header_id = l_header_id
949 AND ohead.org_id = ohead_mp.organization_id
950 AND ohead.sold_to_org_id = ohead_rcs.customer_id(+);
951
952 -- ======================
953 -- OE lines Cursor
954 -- ======================
955 CURSOR oe_lines_curs IS
956 SELECT
957 oline.booked_flag,
958 oline.cancelled_flag,
959 oline.component_code,
960 oline.cust_po_number,
961 to_char(oline.earliest_acceptable_date,G_DATE_FORMAT_MASK),
962 to_char(oline.explosion_date, G_DATE_FORMAT_MASK),
963 oline.freight_carrier_code,
964 to_char(oline.latest_acceptable_date, G_DATE_FORMAT_MASK),
965 Oline.open_flag,
966 to_char(oline.actual_shipment_date, G_DATE_FORMAT_MASK),
967 oline.created_by,
968 oline.last_updated_by,
969 to_char(oline.last_update_date, G_DATE_FORMAT_MASK),
970 oline.attribute1,
971 oline.attribute2,
972 oline.attribute3,
973 oline.attribute4,
974 oline.attribute5,
975 oline.attribute6,
976 oline.attribute7,
977 oline.attribute8,
978 oline.attribute9,
979 oline.attribute10,
980 oline.attribute11,
981 oline.attribute12,
982 oline.attribute13,
983 oline.attribute14,
984 oline.attribute15,
985 oline.return_attribute1,
986 oline.return_attribute2,
987 oline.return_attribute3,
988 oline.return_attribute4,
989 oline.return_attribute5,
990 oline.return_attribute6,
991 oline.return_attribute7,
992 oline.return_attribute8,
993 oline.return_attribute9,
994 oline.return_attribute10,
995 oline.return_attribute11,
996 oline.return_attribute12,
997 oline.return_attribute13,
998 oline.return_attribute14,
999 oline.return_attribute15,
1000 oline.return_context,
1001 oline.context,
1002 to_char(oline.creation_date, G_DATE_FORMAT_MASK),
1003 oline.fulfilled_quantity,
1004 oline.ordered_item,
1005 oline.line_number,
1006 oline.ordered_quantity,
1007 to_char(oline.promise_date, G_DATE_FORMAT_MASK),
1008 oline.order_quantity_uom,
1009 to_char(oline.request_date, G_DATE_FORMAT_MASK),
1010 to_char(oline.schedule_ship_date, G_DATE_FORMAT_MASK),
1011 oline.shipped_quantity,
1012 oline.shipping_quantity,
1013 oline.shipping_quantity_uom,
1014 oline.over_ship_reason_code,
1015 oline.packing_instructions,
1016 pp.name project_name,
1017 ras.name salesreps_name,
1018 to_char(oline.schedule_arrival_date, G_DATE_FORMAT_MASK),
1019 oe_sets.set_name ship_set_name,
1020 osmv.meaning shipping_method_name,
1021 ar_lookups.meaning tax_exempt_reason,
1022 oline.tax_code,
1023 oline.tax_exempt_flag,
1024 oline.tax_exempt_number,
1025 oline.tax_rate,
1026 oline.shipment_number,
1027 oline.shipping_instructions,
1028 --rcship.last_name,
1029 LTRIM(rcship.last_name ||decode(rcship.first_name,NULL,NULL,','||rcship.first_name)) ship_to_contact_name,
1030 LTRIM(isc.last_name || decode(isc.first_name,NULL,NULL,','|| isc.first_name)) intmed_ship_to_contact_name,
1031 LTRIM(invc.last_name || decode(invc.first_name,NULL,NULL,','|| invc.first_name)) invoice_to_contact_name,
1032 LTRIM(dcontact.last_name || decode(dcontact.first_name,NULL,NULL,','|| dcontact.first_name)) deliver_to_contact_name,
1033 oline_rc.customer_name,
1034 oline_rc.person_first_name,
1035 oline_rc.person_last_name,
1036 oline_rc.person_middle_name,
1037 oline_rc.customer_type,
1038 oline_rc.customer_id,
1039 oline_rc.party_id,
1040 oline_rc.party_number,
1041 oline.sold_from_org_id,
1042 oline.sold_to_org_id,
1043 oline.ship_from_org_id,
1044 oline.ship_to_org_id,
1045 oline.invoice_to_org_id,
1046 oline.deliver_to_org_id,
1047 shiptoc.customer_id ship_to_customer_id,
1048 dcontact.customer_id delivery_customer_id,
1049 invc.customer_id invoice_customer_id,
1050 oline.header_id
1051 -- ship_from_org.organization_code
1052 FROM OE_ORDER_LINES_ALL oline,
1053 OE_SHIP_METHODS_V osmv
1054 --
1055 -- Modification Start for Bug # - 4418524
1056 --
1057 -- As part of TCA related changes ra_customers, ra_contacts views are
1058 -- obsoleted in R12. The columns fetched from these views are fetched
1059 -- from "HZ_PARTIES", "HZ_CUST_ACCOUNTS", "HZ_CUST_ACCOUNT_ROLES",
1060 -- "HZ_CUST_ACCOUNTS", "HZ_RELATIONSHIPS".
1061 --
1062 -- Following six table alias are commented
1063 --, ra_customers oline_rc
1064 --, ra_contacts rcship
1065 --, ra_contacts dcontact
1066 --, ra_contacts isc
1067 --, ra_contacts invc
1068 --, ra_contacts shiptoc
1069 --
1070 -- Following 4 Queries are added to replace the above commented
1071 -- views
1072 --
1073 , ( SELECT CUST_ACCT.cust_account_id customer_id,
1074 PARTY.party_id party_id,
1075 PARTY.party_number party_number,
1076 SUBSTRB(PARTY.party_name,1,50) customer_name,
1077 PARTY.person_first_name person_first_name,
1078 PARTY.person_middle_name person_middle_name,
1079 PARTY.person_last_name person_last_name,
1080 CUST_ACCT.customer_type customer_type
1081 FROM hz_parties PARTY
1082 , hz_cust_accounts CUST_ACCT
1083 WHERE CUST_ACCT.party_id = PARTY.party_id
1084 ) oline_rc
1085 , ( SELECT ACCT_ROLE.cust_account_role_id contact_id,
1086 ACCT_ROLE.cust_account_id customer_id,
1087 SUBSTRB(PARTY.person_last_name,1,50) last_name,
1088 SUBSTRB(PARTY.person_first_name,1,40) first_name
1089 FROM hz_cust_account_roles ACCT_ROLE,
1090 hz_parties PARTY,
1091 hz_relationships REL,
1092 hz_cust_accounts ROLE_ACCT
1093 WHERE
1094 ACCT_ROLE.party_id = REL.party_id
1095 AND ACCT_ROLE.role_type = 'CONTACT'
1096 AND REL.subject_id = PARTY.party_id
1097 AND REL.subject_table_name = 'HZ_PARTIES'
1098 AND REL.object_table_name = 'HZ_PARTIES'
1099 AND ACCT_ROLE.cust_account_id = ROLE_ACCT.cust_account_id
1100 AND ROLE_ACCT.party_id = REL.object_id
1101 ) rcship
1102 , ( SELECT ACCT_ROLE.cust_account_role_id contact_id,
1103 ACCT_ROLE.cust_account_id customer_id,
1104 SUBSTRB(PARTY.person_last_name,1,50) last_name,
1105 SUBSTRB(PARTY.person_first_name,1,40) first_name
1106 FROM hz_cust_account_roles ACCT_ROLE,
1107 hz_parties PARTY,
1108 hz_relationships REL,
1109 hz_cust_accounts ROLE_ACCT
1110 WHERE
1111 ACCT_ROLE.party_id = REL.party_id
1112 AND ACCT_ROLE.role_type = 'CONTACT'
1113 AND REL.subject_id = PARTY.party_id
1114 AND REL.subject_table_name = 'HZ_PARTIES'
1115 AND REL.object_table_name = 'HZ_PARTIES'
1116 AND ACCT_ROLE.cust_account_id = ROLE_ACCT.cust_account_id
1117 AND ROLE_ACCT.party_id = REL.object_id
1118 ) dcontact
1119 , ( SELECT ACCT_ROLE.cust_account_role_id contact_id,
1120 ACCT_ROLE.cust_account_id customer_id,
1121 SUBSTRB(PARTY.person_last_name,1,50) last_name,
1122 SUBSTRB(PARTY.person_first_name,1,40) first_name
1123 FROM hz_cust_account_roles ACCT_ROLE,
1124 hz_parties PARTY,
1125 hz_relationships REL,
1126 hz_cust_accounts ROLE_ACCT
1127 WHERE
1128 ACCT_ROLE.party_id = REL.party_id
1129 AND ACCT_ROLE.role_type = 'CONTACT'
1130 AND REL.subject_id = PARTY.party_id
1131 AND REL.subject_table_name = 'HZ_PARTIES'
1132 AND REL.object_table_name = 'HZ_PARTIES'
1133 AND ACCT_ROLE.cust_account_id = ROLE_ACCT.cust_account_id
1134 AND ROLE_ACCT.party_id = REL.object_id
1135 ) isc
1136 , ( SELECT ACCT_ROLE.cust_account_role_id contact_id,
1137 ACCT_ROLE.cust_account_id customer_id,
1138 SUBSTRB(PARTY.person_last_name,1,50) last_name,
1139 SUBSTRB(PARTY.person_first_name,1,40) first_name
1140 FROM hz_cust_account_roles ACCT_ROLE,
1141 hz_parties PARTY,
1142 hz_relationships REL,
1143 hz_cust_accounts ROLE_ACCT
1144 WHERE
1145 ACCT_ROLE.party_id = REL.party_id
1146 AND ACCT_ROLE.role_type = 'CONTACT'
1147 AND REL.subject_id = PARTY.party_id
1148 AND REL.subject_table_name = 'HZ_PARTIES'
1149 AND REL.object_table_name = 'HZ_PARTIES'
1150 AND ACCT_ROLE.cust_account_id = ROLE_ACCT.cust_account_id
1151 AND ROLE_ACCT.party_id = REL.object_id
1152 ) invc
1153 , ( SELECT ACCT_ROLE.cust_account_role_id contact_id,
1154 ACCT_ROLE.cust_account_id customer_id,
1155 SUBSTRB(PARTY.person_last_name,1,50) last_name,
1156 SUBSTRB(PARTY.person_first_name,1,40) first_name
1157 FROM hz_cust_account_roles ACCT_ROLE,
1158 hz_parties PARTY,
1159 hz_relationships REL,
1160 hz_cust_accounts ROLE_ACCT
1161 WHERE
1162 ACCT_ROLE.party_id = REL.party_id
1163 AND ACCT_ROLE.role_type = 'CONTACT'
1164 AND REL.subject_id = PARTY.party_id
1165 AND REL.subject_table_name = 'HZ_PARTIES'
1166 AND REL.object_table_name = 'HZ_PARTIES'
1167 AND ACCT_ROLE.cust_account_id = ROLE_ACCT.cust_account_id
1168 AND ROLE_ACCT.party_id = REL.object_id
1169 ) shiptoc,
1170 --
1171 -- Modification End for Bug # - 4418524
1172 --
1173 PA_PROJECTS pp,
1174 --ORG_FREIGHT_VL ofv,
1175 RA_SALESREPS ras,
1176 OE_SETS,
1177 AR_LOOKUPS,
1178 MTL_PARAMETERS ship_from_org
1179 WHERE oline.line_id = p_line_id
1180 AND oline.sold_to_org_id = oline_rc.customer_id(+)
1181 AND oline.deliver_to_contact_id = dcontact.contact_id(+)
1182 AND oline.ship_to_contact_id = shiptoc.contact_id(+)
1183 AND oline.invoice_to_contact_id = invc.contact_id(+)
1184 AND oline.intmed_ship_to_contact_id = isc.contact_id(+)
1185 AND oline.salesrep_id = ras.salesrep_id(+)
1186 AND oline.ship_set_id = oe_sets.set_id(+)
1187 AND oline.ship_to_contact_id = rcship.contact_id(+)
1188 AND oline.shipping_method_code = osmv.lookup_code(+)
1189 AND oline.tax_exempt_reason_code = ar_lookups.lookup_code(+)
1190 and ar_lookups.lookup_type(+) = 'TAX_REASON'
1191 AND oline.project_id =pp.project_id(+)
1192 AND oline.ship_from_org_id = ship_from_org.organization_id(+);
1193 --AND oline.freight_carrier_code = ofv.freight_code(+)
1194
1195 -- ===================================================
1196
1197 CURSOR loc_curs (c_location_id NUMBER) IS
1198 SELECT
1199 loc.address_line_1,
1200 loc.address_line_2,
1201 loc.address_line_3,
1202 decode(LOC.CITY,null, null, LOC.CITY|| ', ')
1203 ||decode(LOC.state, null, null, LOC.state || ', ') ||
1204 decode(LOC.postal_code,null, null, LOC.postal_code || ', ') ||
1205 decode(LOC.country, null, null, LOC.country) address_line_4 ,
1206 -- loc.address_line_4 address_line_5,
1207 loc.country,
1208 loc.postal_code,
1209 loc.county,
1210 loc.state,
1211 loc.province,
1212 loc.city ,
1213 loc.telephone_number_1
1214 --loc.description
1215 FROM (
1216 SELECT loc.location_id location_id,
1217 loc.address_line_1 address_line_1,
1218 loc.address_line_2 address_line_2,loc.address_line_3 address_line_3,
1219 loc.loc_information13 address_line_4,
1220 loc.town_or_city city,loc.postal_code postal_code,
1221 loc.region_2 state,loc.region_1 county,
1222 loc.country country,loc.region_3 province,
1223 loc.location_code location_code,loc.description description,
1224 loc.telephone_number_1
1225 FROM hr_locations_all loc
1226 UNION ALL
1227 SELECT hz.location_id location_id,
1228 hz.address1 address_line_1,
1229 hz.address2 address_line_2,hz.address3 address_line_3,
1230 hz.address4 address_line_4,
1231 hz.city city,hz.postal_code postal_code,
1232 hz.state state,hz.county county,
1233 hz.country country,hz.province province,
1234 hz.description location_code, hz.description description,
1235 NULL telephone_number_1
1236 FROM hz_locations hz
1237 ) LOC
1238 WHERE location_id = c_location_id;
1239
1240 CURSOR customer_site(c_site_use_code VARCHAR2,
1241 c_site_use_id NUMBER, c_customer_id NUMBER) IS
1242 SELECT /*+ INDEX(ACCT_SITE,HZ_CUST_ACCT_SITES_N2) */
1243 party_site.location_id,
1244 site.location location_code,
1245 org.organization_code,
1246 org.organization_name
1247 --cust_acct.account_number customer_Number,
1248 --cust_acct.customer_type,
1249 --party.party_name customer_name
1250 --site.site_use_code,
1251 --site.site_use_id,
1252 --site.org_Id organization_id,
1253 --cust_acct.cust_account_id customer_id
1254 FROM
1255 HZ_CUST_ACCT_SITES_ALL ACCT_SITE,
1256 HZ_PARTY_SITES PARTY_SITE,
1257 HZ_CUST_SITE_USES_ALL SITE,
1258 HZ_PARTIES PARTY,
1259 HZ_CUST_ACCOUNTS CUST_ACCT,
1260 ORG_ORGANIZATION_DEFINITIONS ORG
1261 WHERE SITE.ORG_ID = ORG.ORGANIZATION_ID
1262 AND SITE.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID
1263 AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
1264 and acct_site.status='A'
1265 AND ACCT_SITE.CUST_ACCOUNT_ID=CUST_ACCT.CUST_ACCOUNT_ID
1266 AND CUST_ACCT.PARTY_ID=PARTY.PARTY_ID
1267 AND CUST_ACCT.status='A'
1268 AND site.status='A'
1269 AND SITE.SITE_USE_CODE = c_site_use_code
1270 AND CUST_ACCT.CUST_ACCOUNT_ID = c_customer_id
1271 AND SITE.SITE_USE_ID = c_site_use_id;
1272
1273 -- ==================================================
1274
1275 CURSOR from_org_curs (c_organization_id NUMBER) IS
1276 SELECT hou.location_id,
1277 mp.organization_code
1278 -- hou.name organization_name,
1279 -- hou.organization_id
1280 FROM hr_organization_units hou,
1281 mtl_parameters mp,
1282 hr_organization_information hoi1
1283 WHERE hou.ORGANIZATION_ID = mp.ORGANIZATION_ID
1284 AND hou.ORGANIZATION_ID = hoi1.ORGANIZATION_id
1285 AND hoi1.ORG_INFORMATION1= 'INV'
1286 AND hoi1.ORG_INFORMATION2 = 'Y'
1287 AND mp.organization_id = c_organization_id;
1288
1289
1290
1291 BEGIN
1292 IF (l_debug = 1) THEN
1293 trace('**In get_data_sale_header() **');
1294 trace('p_header_id : ' || p_header_id);
1295 trace('p_line_id : ' || p_line_id);
1296 END IF;
1297
1298
1299
1300 IF (p_line_id IS NOT NULL) THEN
1301
1302 /* ================================== */
1303 /* Retrieve data for sale order line */
1304 /* ================================== */
1305 IF (l_debug = 1) THEN
1306 trace('**Retrieve Sale Line data ... **');
1307 END IF;
1308 OPEN oe_lines_curs;
1309 FETCH oe_lines_curs INTO
1310 x_out_tbl(1876).datbuf, x_out_tbl(1877).datbuf, x_out_tbl(1878).datbuf,
1311 x_out_tbl(1879).datbuf, x_out_tbl(1880).datbuf, x_out_tbl(1881).datbuf,
1312 x_out_tbl(1882).datbuf, x_out_tbl(1883).datbuf, x_out_tbl(1884).datbuf,
1313 x_out_tbl(1885).datbuf, x_out_tbl(1886).datbuf, x_out_tbl(1887).datbuf,
1314 x_out_tbl(1888).datbuf, x_out_tbl(1889).datbuf, x_out_tbl(1890).datbuf,
1315 x_out_tbl(1891).datbuf, x_out_tbl(1892).datbuf, x_out_tbl(1893).datbuf,
1316 x_out_tbl(1894).datbuf, x_out_tbl(1895).datbuf, x_out_tbl(1896).datbuf,
1317 x_out_tbl(1897).datbuf, x_out_tbl(1898).datbuf, x_out_tbl(1899).datbuf,
1318 x_out_tbl(1900).datbuf, x_out_tbl(1901).datbuf, x_out_tbl(1902).datbuf,
1319 x_out_tbl(1903).datbuf, x_out_tbl(1904).datbuf, x_out_tbl(1905).datbuf,
1320 x_out_tbl(1906).datbuf, x_out_tbl(1907).datbuf, x_out_tbl(1908).datbuf,
1321 x_out_tbl(1909).datbuf, x_out_tbl(1910).datbuf, x_out_tbl(1911).datbuf,
1322 x_out_tbl(1912).datbuf, x_out_tbl(1913).datbuf, x_out_tbl(1914).datbuf,
1323 x_out_tbl(1915).datbuf, x_out_tbl(1916).datbuf, x_out_tbl(1917).datbuf,
1324 x_out_tbl(1918).datbuf, x_out_tbl(1919).datbuf, x_out_tbl(1920).datbuf,
1325 x_out_tbl(1921).datbuf, x_out_tbl(1922).datbuf, x_out_tbl(1923).datbuf,
1326 x_out_tbl(1924).datbuf, x_out_tbl(1925).datbuf, x_out_tbl(1926).datbuf,
1327 x_out_tbl(1927).datbuf, x_out_tbl(1928).datbuf, x_out_tbl(1929).datbuf,
1328 x_out_tbl(1930).datbuf, x_out_tbl(1931).datbuf, x_out_tbl(1932).datbuf,
1329 x_out_tbl(1933).datbuf, x_out_tbl(1934).datbuf, x_out_tbl(1935).datbuf,
1330 x_out_tbl(1936).datbuf, x_out_tbl(1937).datbuf, x_out_tbl(1938).datbuf,
1331 x_out_tbl(1939).datbuf, x_out_tbl(1940).datbuf, x_out_tbl(1941).datbuf,
1332 x_out_tbl(1942).datbuf, x_out_tbl(1943).datbuf, x_out_tbl(1944).datbuf,
1333 x_out_tbl(1945).datbuf, x_out_tbl(1946).datbuf, x_out_tbl(1947).datbuf,
1334 x_out_tbl(1948).datbuf, x_out_tbl(1949).datbuf, x_out_tbl(1950).datbuf,
1335 x_out_tbl(1951).datbuf, x_out_tbl(1952).datbuf, x_out_tbl(1953).datbuf,
1336 x_out_tbl(1954).datbuf, x_out_tbl(1955).datbuf,
1337 l_customer_id, l_party_id, l_party_number,
1338 l_sold_from_org_id, l_sold_to_org_id, l_ship_from_org_id,
1339 l_ship_to_org_id,l_invoice_to_org_id, l_deliver_to_org_id,
1340 l_ship_to_customer_id,l_deliver_customer_id, l_invoice_customer_id,
1341 l_header_id;
1342
1343 CLOSE oe_lines_curs;
1344 -- trace('l_sold_from_org_id : ' || l_sold_from_org_id);
1345 -- trace('l_sold_to_org_id : ' || l_sold_to_org_id);
1346 -- trace('l_ship_to_org_id : ' || l_ship_to_org_id);
1347 -- trace('l_ship_from_org_id : ' || l_ship_from_org_id);
1348 -- trace('l_invoice_to_org_id : ' || l_invoice_to_org_id);
1349 -- trace('l_deliver_to_org_id : ' || l_deliver_to_org_id);
1350 -- trace('l_ship_to_customer_id : ' || l_ship_to_customer_id);
1351 -- trace('l_invoice_customer_id : ' || l_invoice_customer_id);
1352 -- trace('l_deliver_customer_id : ' || l_deliver_customer_id);
1353
1354 -- ship to organization_code
1355 OPEN customer_site('SHIP_TO', l_ship_to_org_id, l_ship_to_customer_id);
1356
1357 FETCH customer_site INTO l_ship_to_location_id, l_location_name,
1358 l_ship_to_organization_code, l_organization_name;
1359 CLOSE customer_site;
1360 --trace('l_ship_to_location_id : ' || l_ship_to_location_id ||
1361 -- ' l_ship_to_organization_code : ' || l_ship_to_organization_code);
1362
1363 -- sold to organization_code
1364 OPEN customer_site('BILL_TO', l_invoice_to_org_id, l_customer_id);
1365
1366 FETCH customer_site INTO l_sold_to_location_id, l_location_name,
1367 l_sold_to_organization_code, l_organization_name;
1368 CLOSE customer_site;
1369
1370 -- invoice to organization_code
1371 OPEN customer_site('BILL_TO', l_invoice_to_org_id, l_invoice_customer_id);
1372
1373 FETCH customer_site INTO l_invoice_to_location_id, l_location_name,
1374 l_invoice_to_organization_code, l_organization_name;
1375 CLOSE customer_site;
1376
1377 -- deliver to organization_code
1378 OPEN customer_site('DELIVER_TO',l_deliver_to_org_id, l_deliver_customer_id);
1379
1380 FETCH customer_site INTO l_deliver_to_location_id, l_location_name,
1381 l_deliver_to_organization_code, l_organization_name;
1382 CLOSE customer_site;
1383
1384 -- sold from organization_code
1385 OPEN from_org_curs(l_sold_from_org_id);
1386 FETCH from_org_curs INTO l_sold_from_location_id,
1387 l_sold_from_organization_code;
1388 CLOSE from_org_curs;
1389
1390 -- ship from organization_code
1391 OPEN from_org_curs(l_ship_from_org_id);
1392 FETCH from_org_curs INTO l_ship_from_location_id,
1393 l_ship_from_organization_code;
1394 CLOSE from_org_curs;
1395 --trace('l_ship_from_location_id : ' || l_ship_from_location_id ||
1396 -- ' l_ship_from_organization_code : ' || l_ship_from_organization_code);
1397
1398 x_out_tbl(1956).datbuf := l_sold_from_organization_code;
1399 x_out_tbl(1957).datbuf := l_sold_to_organization_code;
1400 x_out_tbl(1958).datbuf := l_deliver_to_organization_code;
1401 x_out_tbl(1959).datbuf := l_ship_to_organization_code;
1402 x_out_tbl(1960).datbuf := l_invoice_to_organization_code;
1403 x_out_tbl(1961).datbuf := l_ship_from_organization_code;
1404
1405
1406 OPEN loc_curs(l_ship_to_location_id);
1407 FETCH loc_curs INTO
1408 x_out_tbl(1962).datbuf, x_out_tbl(1963).datbuf, x_out_tbl(1964).datbuf,
1409 x_out_tbl(1965).datbuf, x_out_tbl(1966).datbuf, x_out_tbl(1967).datbuf,
1410 x_out_tbl(1968).datbuf, x_out_tbl(1969).datbuf, x_out_tbl(1970).datbuf,
1411 x_out_tbl(1971).datbuf, x_out_tbl(1972).datbuf;
1412 CLOSE loc_curs;
1413
1414 OPEN loc_curs(l_ship_from_location_id);
1415 FETCH loc_curs INTO
1416 x_out_tbl(1973).datbuf, x_out_tbl(1974).datbuf, x_out_tbl(1975).datbuf,
1417 x_out_tbl(1976).datbuf, x_out_tbl(1977).datbuf, x_out_tbl(1978).datbuf,
1418 x_out_tbl(1979).datbuf, x_out_tbl(1980).datbuf, x_out_tbl(1981).datbuf,
1419 x_out_tbl(1982).datbuf, x_out_tbl(1983).datbuf;
1420 CLOSE loc_curs;
1421 /*
1422 FOR i in 1876..1983 LOOP
1423 IF (x_out_tbl.EXISTS(i) AND x_out_tbl(i).datbuf IS NOT NULL ) THEN
1424 IF (l_debug = 1) THEN
1425 trace('x_out_tbl('||i||')='|| x_out_tbl(i).datbuf );
1426 END IF;
1427 END IF;
1428 END LOOP;
1429 */
1430 END IF; -- p_line_id not null
1431
1432 IF (l_debug = 1) THEN
1433 trace('**Retrieve Sale Header data ... **');
1434 trace('Header ID : ' || l_header_id);
1435 END IF;
1436 OPEN oe_header_curs;
1437 FETCH oe_header_curs INTO
1438 x_out_tbl(1731).datbuf, x_out_tbl(1732).datbuf, x_out_tbl(1733).datbuf,
1439 x_out_tbl(1734).datbuf, x_out_tbl(1735).datbuf, x_out_tbl(1736).datbuf,
1440 x_out_tbl(1737).datbuf, x_out_tbl(1738).datbuf, x_out_tbl(1739).datbuf,
1441 x_out_tbl(1740).datbuf, x_out_tbl(1741).datbuf, x_out_tbl(1742).datbuf,
1442 x_out_tbl(1743).datbuf, x_out_tbl(1744).datbuf, x_out_tbl(1745).datbuf,
1443 x_out_tbl(1746).datbuf, x_out_tbl(1747).datbuf, x_out_tbl(1748).datbuf,
1444 x_out_tbl(1749).datbuf, x_out_tbl(1750).datbuf, x_out_tbl(1751).datbuf,
1445 x_out_tbl(1752).datbuf, x_out_tbl(1753).datbuf, x_out_tbl(1754).datbuf,
1446 x_out_tbl(1755).datbuf, x_out_tbl(1756).datbuf, x_out_tbl(1757).datbuf,
1447 x_out_tbl(1758).datbuf, x_out_tbl(1759).datbuf, x_out_tbl(1760).datbuf,
1448 x_out_tbl(1761).datbuf, x_out_tbl(1762).datbuf, x_out_tbl(1763).datbuf,
1449 x_out_tbl(1764).datbuf, x_out_tbl(1765).datbuf, x_out_tbl(1766).datbuf,
1450 x_out_tbl(1767).datbuf, x_out_tbl(1768).datbuf, x_out_tbl(1769).datbuf,
1451 x_out_tbl(1770).datbuf, x_out_tbl(1771).datbuf, x_out_tbl(1772).datbuf,
1452 x_out_tbl(1773).datbuf, x_out_tbl(1774).datbuf, x_out_tbl(1775).datbuf,
1453 x_out_tbl(1776).datbuf, x_out_tbl(1777).datbuf, x_out_tbl(1778).datbuf,
1454 x_out_tbl(1779).datbuf, l_customer_id, l_party_id, l_party_number,
1455 l_sold_from_org_id, l_sold_to_org_id, l_ship_to_org_id,
1456 l_ship_from_org_id,l_invoice_to_org_id, l_deliver_to_org_id;
1457
1458 CLOSE oe_header_curs;
1459
1460 OPEN customer_site('SHIP_TO', l_ship_to_org_id, l_customer_id);
1461
1462 FETCH customer_site INTO l_location_id, l_location_name,
1463 l_organization_code, l_organization_name;
1464 CLOSE customer_site;
1465
1466 OPEN loc_curs(l_location_id);
1467 FETCH loc_curs INTO
1468 x_out_tbl(1780).datbuf, x_out_tbl(1781).datbuf, x_out_tbl(1782).datbuf,
1469 x_out_tbl(1783).datbuf, x_out_tbl(1785).datbuf,
1470 x_out_tbl(1786).datbuf, x_out_tbl(1787).datbuf, x_out_tbl(1788).datbuf,
1471 x_out_tbl(1789).datbuf, x_out_tbl(1790).datbuf, x_out_tbl(1791).datbuf;
1472 CLOSE loc_curs;
1473 /*
1474 FOR i in 1731..1789 LOOP
1475 IF (x_out_tbl.EXISTS(i) AND x_out_tbl(i).datbuf IS NOT NULL ) THEN
1476 IF (l_debug = 1) THEN
1477 trace('x_out_tbl('||i||')='|| x_out_tbl(i).datbuf );
1478 END IF;
1479 END IF;
1480 END LOOP;
1481 */
1482 END get_data_sale_header ;
1483
1484 /*=================================================================*/
1485 PROCEDURE get_data_sale_line(
1486 x_out_tbl IN OUT NOCOPY output_tbl_type,
1487 p_line_id oe_order_lines_all.line_id%TYPE
1488 )IS
1489
1490 l_header_id oe_order_headers_all.header_id%TYPE;
1491
1492 CURSOR hr_location_curs (c_location_id NUMBER) IS
1493 SELECT loc.location_id location_id,
1494 loc.address_line_1 address_line_1,
1495 loc.address_line_2 address_line_2,loc.address_line_3 address_line_3,
1496 loc.loc_information13 address_line_4,
1497 loc.town_or_city city,loc.postal_code postal_code,
1498 loc.region_2 state,loc.region_1 county,
1499 loc.country country,loc.region_3 province,
1500 loc.location_code location_code,loc.description location_description
1501 FROM hr_locations_all loc
1502 WHERE loc.location_id = c_location_id
1503 UNION ALL
1504 SELECT hz.location_id location_id,
1505 hz.address1 address_line_1,
1506 hz.address2 address_line_2,hz.address3 address_line_3,
1507 hz.address4 address_line_4,
1508 hz.city city,hz.postal_code postal_code,
1509 hz.state state,hz.county county,
1510 hz.country country,hz.province province,
1511 hz.description location_code, hz.description location_description
1512 FROM hz_locations hz
1513 WHERE hz.location_id = c_location_id;
1514
1515 CURSOR oe_lines_curs IS
1516 SELECT
1517 oline.booked_flag,
1518 oline.cancelled_flag,
1519 oline.component_code,
1520 oline.cust_po_number,
1521 to_char(oline.earliest_acceptable_date, G_DATE_FORMAT_MASK),
1522 to_char(oline.explosion_date, G_DATE_FORMAT_MASK),
1523 oline.freight_carrier_code,
1524 to_char(oline.latest_acceptable_date, G_DATE_FORMAT_MASK),
1525 Oline.open_flag,
1526 to_char(oline.actual_shipment_date, G_DATE_FORMAT_MASK),
1527 oline.created_by,
1528 oline.last_updated_by,
1529 to_char(oline.last_update_date, G_DATE_FORMAT_MASK),
1530 oline.attribute1,
1531 oline.attribute2,
1532 oline.attribute3,
1533 oline.attribute4,
1534 oline.attribute5,
1535 oline.attribute6,
1536 oline.attribute7,
1537 oline.attribute8,
1538 oline.attribute9,
1539 oline.attribute10,
1540 oline.attribute11,
1541 oline.attribute12,
1542 oline.attribute13,
1543 oline.attribute14,
1544 oline.attribute15,
1545 oline.return_attribute1,
1546 oline.return_attribute2,
1547 oline.return_attribute3,
1548 oline.return_attribute4,
1549 oline.return_attribute5,
1550 oline.return_attribute6,
1551 oline.return_attribute7,
1552 oline.return_attribute8,
1553 oline.return_attribute9,
1554 oline.return_attribute10,
1555 oline.return_attribute11,
1556 oline.return_attribute12,
1557 oline.return_attribute13,
1558 oline.return_attribute14,
1559 oline.return_attribute15,
1560 oline.return_context,
1561 oline.context,
1562 to_char(oline.creation_date, G_DATE_FORMAT_MASK),
1563 oline.fulfilled_quantity,
1564 oline.ordered_item,
1565 oline.line_number,
1566 oline.ordered_quantity,
1567 to_char(oline.promise_date, G_DATE_FORMAT_MASK),
1568 oline.order_quantity_uom,
1569 to_char(oline.request_date, G_DATE_FORMAT_MASK),
1570 to_char(oline.schedule_ship_date, G_DATE_FORMAT_MASK),
1571 oline.shipped_quantity,
1572 oline.shipping_quantity,
1573 oline.shipping_quantity_uom,
1574 oline.over_ship_reason_code,
1575 oline.packing_instructions,
1576 pp.name project_name,
1577 ras.name salesreps_name,
1578 to_char(oline.schedule_arrival_date, G_DATE_FORMAT_MASK),
1579 oe_sets.set_name ship_set_name,
1580 osmv.meaning shipping_method_name,
1581 ar_lookups.meaning tax_exempt_reason,
1582 oline.tax_code,
1583 oline.tax_exempt_flag,
1584 oline.tax_exempt_number,
1585 oline.tax_rate,
1586 oline.shipment_number,
1587 oline.shipping_instructions,
1588 --rcship.last_name,
1589 LTRIM(rcship.last_name ||decode(rcship.first_name,NULL,NULL,','||rcship.first_name)) ship_to_contact_name,
1590 LTRIM(isc.last_name || decode(isc.first_name,NULL,NULL,','|| isc.first_name)) intmed_ship_to_contact_name,
1591 LTRIM(invc.last_name || decode(invc.first_name,NULL,NULL,','|| invc.first_name)) invoice_to_contact_name,
1592 LTRIM(dcontact.last_name || decode(dcontact.first_name,NULL,NULL,','|| dcontact.first_name)) deliver_to_contact_name,
1593 oline.sold_from_org_id,
1594 oline.sold_to_org_id,
1595 oline.deliver_to_org_id,
1596 oline.ship_to_org_id,
1597 oline.invoice_to_org_id,
1598 ship_from_org.organization_code,
1599 oline.header_id
1600 FROM OE_ORDER_LINES_ALL oline,
1601 OE_SHIP_METHODS_V osmv
1602 --
1603 -- Modification Start for Bug # - 4418524
1604 --
1605 -- As part of TCA related changes ra_customers, ra_contacts views are
1606 -- obsoleted in R12. The columns fetched from these views are fetched
1607 -- from "HZ_PARTIES", "HZ_CUST_ACCOUNTS", "HZ_CUST_ACCOUNT_ROLES",
1608 -- "HZ_CUST_ACCOUNTS", "HZ_RELATIONSHIPS".
1609 --
1610 -- Following six table alias are commented
1611 --, ra_contacts rcship
1612 --, ra_contacts dcontact
1613 --, ra_contacts isc
1614 --, ra_contacts invc
1615 --
1616 -- Following 4 Queries are added to replace the above commented
1617 -- views
1618 --
1619 , ( SELECT ACCT_ROLE.cust_account_role_id contact_id,
1620 ACCT_ROLE.cust_account_id customer_id,
1621 SUBSTRB(PARTY.person_last_name,1,50) last_name,
1622 SUBSTRB(PARTY.person_first_name,1,40) first_name
1623 FROM hz_cust_account_roles ACCT_ROLE,
1624 hz_parties PARTY,
1625 hz_relationships REL,
1626 hz_cust_accounts ROLE_ACCT
1627 WHERE
1628 ACCT_ROLE.party_id = REL.party_id
1629 AND ACCT_ROLE.role_type = 'CONTACT'
1630 AND REL.subject_id = PARTY.party_id
1631 AND REL.subject_table_name = 'HZ_PARTIES'
1632 AND REL.object_table_name = 'HZ_PARTIES'
1633 AND ACCT_ROLE.cust_account_id = ROLE_ACCT.cust_account_id
1634 AND ROLE_ACCT.party_id = REL.object_id
1635 ) rcship
1636 , ( SELECT ACCT_ROLE.cust_account_role_id contact_id,
1637 ACCT_ROLE.cust_account_id customer_id,
1638 SUBSTRB(PARTY.person_last_name,1,50) last_name,
1639 SUBSTRB(PARTY.person_first_name,1,40) first_name
1640 FROM hz_cust_account_roles ACCT_ROLE,
1641 hz_parties PARTY,
1642 hz_relationships REL,
1643 hz_cust_accounts ROLE_ACCT
1644 WHERE
1645 ACCT_ROLE.party_id = REL.party_id
1646 AND ACCT_ROLE.role_type = 'CONTACT'
1647 AND REL.subject_id = PARTY.party_id
1648 AND REL.subject_table_name = 'HZ_PARTIES'
1649 AND REL.object_table_name = 'HZ_PARTIES'
1650 AND ACCT_ROLE.cust_account_id = ROLE_ACCT.cust_account_id
1651 AND ROLE_ACCT.party_id = REL.object_id
1652 ) dcontact
1653 , ( SELECT ACCT_ROLE.cust_account_role_id contact_id,
1654 ACCT_ROLE.cust_account_id customer_id,
1655 SUBSTRB(PARTY.person_last_name,1,50) last_name,
1656 SUBSTRB(PARTY.person_first_name,1,40) first_name
1657 FROM hz_cust_account_roles ACCT_ROLE,
1658 hz_parties PARTY,
1659 hz_relationships REL,
1660 hz_cust_accounts ROLE_ACCT
1661 WHERE
1662 ACCT_ROLE.party_id = REL.party_id
1663 AND ACCT_ROLE.role_type = 'CONTACT'
1664 AND REL.subject_id = PARTY.party_id
1665 AND REL.subject_table_name = 'HZ_PARTIES'
1666 AND REL.object_table_name = 'HZ_PARTIES'
1667 AND ACCT_ROLE.cust_account_id = ROLE_ACCT.cust_account_id
1668 AND ROLE_ACCT.party_id = REL.object_id
1669 ) isc
1670 , ( SELECT ACCT_ROLE.cust_account_role_id contact_id,
1671 ACCT_ROLE.cust_account_id customer_id,
1672 SUBSTRB(PARTY.person_last_name,1,50) last_name,
1673 SUBSTRB(PARTY.person_first_name,1,40) first_name
1674 FROM hz_cust_account_roles ACCT_ROLE,
1675 hz_parties PARTY,
1676 hz_relationships REL,
1677 hz_cust_accounts ROLE_ACCT
1678 WHERE
1679 ACCT_ROLE.party_id = REL.party_id
1680 AND ACCT_ROLE.role_type = 'CONTACT'
1681 AND REL.subject_id = PARTY.party_id
1682 AND REL.subject_table_name = 'HZ_PARTIES'
1683 AND REL.object_table_name = 'HZ_PARTIES'
1684 AND ACCT_ROLE.cust_account_id = ROLE_ACCT.cust_account_id
1685 AND ROLE_ACCT.party_id = REL.object_id
1686 ) invc,
1687 PA_PROJECTS pp,
1688 --ORG_FREIGHT_VL ofv,
1689 RA_SALESREPS ras,
1690 OE_SETS,
1691 AR_LOOKUPS,
1692 MTL_PARAMETERS ship_from_org
1693 WHERE oline.line_id = p_line_id
1694 AND oline.deliver_to_contact_id = dcontact.contact_id(+)
1695 AND oline.intmed_ship_to_contact_id = isc.contact_id(+)
1696 AND oline.salesrep_id = ras.salesrep_id(+)
1697 AND oline.ship_set_id = oe_sets.set_id(+)
1698 AND oline.ship_to_contact_id = rcship.contact_id(+)
1699 AND oline.shipping_method_code = osmv.lookup_code(+)
1700 AND oline.tax_exempt_reason_code = ar_lookups.lookup_code(+)
1701 and ar_lookups.lookup_type(+) = 'TAX_REASON'
1702 AND oline.invoice_to_contact_id = invc.contact_id(+)
1703 AND oline.project_id =pp.project_id(+)
1704 AND oline.ship_from_org_id = ship_from_org.organization_id(+);
1705 --AND oline.freight_carrier_code = ofv.freight_code(+)
1706
1707
1708
1709 BEGIN
1710 IF (l_debug = 1) THEN
1711 trace('**In get_data_sale_line() **');
1712 END IF;
1713 OPEN oe_lines_curs;
1714 FETCH oe_lines_curs INTO
1715 x_out_tbl(1876).datbuf, x_out_tbl(1877).datbuf, x_out_tbl(1878).datbuf,
1716 x_out_tbl(1879).datbuf, x_out_tbl(1880).datbuf, x_out_tbl(1881).datbuf,
1717 x_out_tbl(1882).datbuf, x_out_tbl(1883).datbuf, x_out_tbl(1884).datbuf,
1718 x_out_tbl(1885).datbuf, x_out_tbl(1886).datbuf, x_out_tbl(1887).datbuf,
1719 x_out_tbl(1888).datbuf, x_out_tbl(1889).datbuf, x_out_tbl(1890).datbuf,
1720 x_out_tbl(1891).datbuf, x_out_tbl(1892).datbuf, x_out_tbl(1893).datbuf,
1721 x_out_tbl(1894).datbuf, x_out_tbl(1895).datbuf, x_out_tbl(1896).datbuf,
1722 x_out_tbl(1897).datbuf, x_out_tbl(1898).datbuf, x_out_tbl(1899).datbuf,
1723 x_out_tbl(1900).datbuf, x_out_tbl(1901).datbuf, x_out_tbl(1902).datbuf,
1724 x_out_tbl(1903).datbuf, x_out_tbl(1904).datbuf, x_out_tbl(1905).datbuf,
1725 x_out_tbl(1906).datbuf, x_out_tbl(1907).datbuf, x_out_tbl(1908).datbuf,
1726 x_out_tbl(1909).datbuf, x_out_tbl(1910).datbuf, x_out_tbl(1911).datbuf,
1727 x_out_tbl(1912).datbuf, x_out_tbl(1913).datbuf, x_out_tbl(1914).datbuf,
1728 x_out_tbl(1915).datbuf, x_out_tbl(1916).datbuf, x_out_tbl(1917).datbuf,
1729 x_out_tbl(1918).datbuf, x_out_tbl(1919).datbuf, x_out_tbl(1920).datbuf,
1730 x_out_tbl(1921).datbuf, x_out_tbl(1922).datbuf, x_out_tbl(1923).datbuf,
1731 x_out_tbl(1924).datbuf, x_out_tbl(1925).datbuf, x_out_tbl(1926).datbuf,
1732 x_out_tbl(1927).datbuf, x_out_tbl(1928).datbuf, x_out_tbl(1929).datbuf,
1733 x_out_tbl(1930).datbuf, x_out_tbl(1931).datbuf, x_out_tbl(1932).datbuf,
1734 x_out_tbl(1933).datbuf, x_out_tbl(1934).datbuf, x_out_tbl(1935).datbuf,
1735 x_out_tbl(1936).datbuf, x_out_tbl(1937).datbuf, x_out_tbl(1938).datbuf,
1736 x_out_tbl(1939).datbuf, x_out_tbl(1940).datbuf, x_out_tbl(1941).datbuf,
1737 x_out_tbl(1942).datbuf, x_out_tbl(1943).datbuf, x_out_tbl(1944).datbuf,
1738 x_out_tbl(1945).datbuf, x_out_tbl(1946).datbuf, x_out_tbl(1947).datbuf,
1739 x_out_tbl(1948).datbuf, x_out_tbl(1949).datbuf, x_out_tbl(1950).datbuf,
1740 x_out_tbl(1951).datbuf, x_out_tbl(1952).datbuf, x_out_tbl(1953).datbuf,
1741 x_out_tbl(1954).datbuf, x_out_tbl(1955).datbuf, x_out_tbl(1956).datbuf,
1742 l_header_id;
1743
1744 CLOSE oe_lines_curs;
1745
1746 /*
1747 FOR i in 1876..1955 LOOP
1748 IF (x_out_tbl.EXISTS(i) AND x_out_tbl(i).datbuf IS NOT NULL ) THEN
1749 IF (l_debug = 1) THEN
1750 trace('x_out_tbl('||i||')='|| x_out_tbl(i).datbuf );
1751 END IF;
1752 END IF;
1753 END LOOP;
1754 */
1755
1756 END get_data_sale_line ;
1757
1758 /*=================================================================*
1759 * Main Procedure *
1760 *=================================================================*
1761 */
1762 PROCEDURE get_variable_data(
1763 x_variable_content OUT NOCOPY INV_LABEL.label_tbl_type
1764 ,x_msg_count OUT NOCOPY NUMBER
1765 ,x_msg_data OUT NOCOPY VARCHAR2
1766 ,x_return_status OUT NOCOPY VARCHAR2
1767 ,p_label_type_info IN INV_LABEL.label_type_rec
1768 ,p_transaction_id IN NUMBER
1769 ,p_input_param IN MTL_MATERIAL_TRANSACTIONS_TEMP%ROWTYPE
1770 ,p_transaction_identifier IN NUMBER
1771 ) IS
1772
1773 l_api_name VARCHAR2(20) := 'get_variable_data';
1774 SERIAL_EXCEPTION EXCEPTION;
1775 NO_FLOW_DATA_FOUND_X EXCEPTION;
1776 NO_LABEL_FORMAT_FOUND_X EXCEPTION;
1777
1778 l_transaction_id MTL_MATERIAL_TRANSACTIONS_TEMP.TRANSACTION_TEMP_ID%TYPE;
1779
1780 TYPE inptype is RECORD
1781 (
1782 inventory_item_id mtl_material_transactions_temp.inventory_item_id%TYPE,
1783 organization_id mtl_material_transactions_temp.organization_id%TYPE,
1784 subinventory_code mtl_material_transactions_temp.subinventory_code%TYPE,
1785 locator_id mtl_material_transactions_temp.locator_id%TYPE,
1786 lot_number mtl_material_transactions_temp.lot_number%TYPE,
1787 serial_number mtl_material_transactions_temp.serial_number%TYPE,
1788 serial_number_start mtl_serial_numbers.serial_number%TYPE,
1789 serial_number_end mtl_serial_numbers.serial_number%TYPE,
1790 cost_group_id mtl_material_transactions_temp.cost_group_id%TYPE,
1791 project_id mtl_material_transactions_temp.project_id%TYPE,
1792 task_id mtl_material_transactions_temp.task_id%TYPE,
1793 quantity mtl_material_transactions_temp.transaction_quantity%TYPE,
1794 uom mtl_material_transactions_temp.transaction_uom%TYPE,
1795 revision mtl_material_transactions_temp.revision%TYPE,
1796 alternate_bom_designator mtl_material_transactions_temp.alternate_bom_designator%TYPE,
1797 alternate_routing_designator mtl_material_transactions_temp.alternate_routing_designator%TYPE,
1798 sale_header_id mtl_material_transactions_temp.demand_source_header_id%TYPE,
1799 sale_line_id mtl_material_transactions_temp.demand_source_line%TYPE,
1800 kanban_card_id mtl_material_transactions_temp.kanban_card_id%TYPE,
1801 lpn_id mtl_material_transactions_temp.lpn_id%TYPE,
1802 wip_entity_id mtl_material_transactions_temp.transaction_source_id%TYPE,
1803 schedule_number wip_flow_schedules.schedule_number%TYPE,
1804 lot_control_code mtl_system_items.lot_control_code%TYPE,
1805 serial_number_control_code mtl_system_items.serial_number_control_code%TYPE,
1806 transaction_id mtl_material_transactions_temp.transaction_temp_id%TYPE
1807 );
1808
1809
1810 --TYPE flow_input_tbl_type IS TABLE OF inptype INDEX BY BINARY_INTEGER;
1811 TYPE flow_input_tbl_type IS TABLE OF inptype INDEX BY BINARY_INTEGER;
1812
1813 l_in_tbl flow_input_tbl_type;
1814 l_prev_in_tbl inptype;
1815 l_out_tbl output_tbl_type;
1816 l_counter INTEGER;
1817 l_in_rec inptype;
1818 l_serial_numbers_table inv_label.serial_tab_type;
1819 l_transaction_identifier NUMBER;
1820 l_serial_not_found BOOLEAN;
1821
1822 ---------------------------------------------------------------------------------------------
1823 -- Project: 'Custom Labels' (A 11i10+ Project) |
1824 -- Author: Dinesh ([email protected]) |
1825 -- Change Description: |
1826 -- Following variables were added (as a part of 11i10+ 'Custom Labels' Project) |
1827 -- to retrieve and hold the SQL Statement and it's result. |
1828 ---------------------------------------------------------------------------------------------
1829 l_sql_stmt VARCHAR2(4000);
1830 l_sql_stmt_result VARCHAR2(4000) := NULL;
1831 TYPE sql_stmt IS REF CURSOR;
1832 c_sql_stmt sql_stmt;
1833 l_custom_sql_ret_status VARCHAR2(1);
1834 l_custom_sql_ret_msg VARCHAR2(2000);
1835
1836 -- Fix for bug: 4179593 Start
1837 l_CustSqlWarnFlagSet BOOLEAN;
1838 l_CustSqlErrFlagSet BOOLEAN;
1839 l_CustSqlWarnMsg VARCHAR2(2000);
1840 l_CustSqlErrMsg VARCHAR2(2000);
1841 -- Fix for bug: 4179593 End
1842
1843 ------------------------End of this change for Custom Labels project code--------------------
1844
1845 -- Driving cursor
1846 CURSOR flow_complete_mmtt_curs IS
1847 SELECT mmtt.inventory_item_id,
1848 mmtt.organization_id,
1849 NVL(mmtt.subinventory_code,
1850 wfs.completion_subinventory) subinventory_code,
1851 NVL(mmtt.locator_id, wfs.completion_locator_id) locator_id,
1852 NVL(mtlt.lot_number,mmtt.lot_number) lot_number ,
1853 mmtt.serial_number,
1854 NULL serial_number_start,
1855 NULL serial_number_end,
1856 mmtt.cost_group_id ,
1857 NVL(mmtt.project_id , wfs.project_id) project_id ,
1858 NVL(mmtt.task_id , wfs.task_id) task_id ,
1859 mmtt.transaction_quantity quantity,
1860 mmtt.transaction_uom uom,
1861 mmtt.revision revision,
1862 NVL(mmtt.alternate_bom_designator,
1863 wfs.alternate_bom_designator) alternate_bom_designator,
1864 NVL(mmtt.alternate_routing_designator,
1865 wfs.alternate_routing_designator) alternate_routing_designator,
1866 NVL(mmtt.demand_source_header_id,
1867 wfs.demand_source_header_id) sale_header_id,
1868 NVL(mmtt.demand_source_line, wfs.demand_source_line) sale_line_id,
1869 NVL(mmtt.kanban_card_id,wfs.kanban_card_id) kanban_card_id,
1870 NVL(NVL(mmtt.transfer_lpn_id, mmtt.content_lpn_id), mmtt.lpn_id) lpn_id,
1871 mmtt.transaction_source_id wip_entity_id,
1872 NVL(mmtt.schedule_number, wfs.schedule_number) schedule_number,
1873 msi.lot_control_code,
1874 msi.serial_number_control_code serial_number_control_code,
1875 mmtt.transaction_temp_id transaction_id
1876 FROM MTL_MATERIAL_TRANSACTIONS_TEMP mmtt,
1877 MTL_TRANSACTION_LOTS_TEMP mtlt,
1878 MTL_SYSTEM_ITEMS msi,
1879 WIP_ENTITIES WE,
1880 WIP_FLOW_SCHEDULES wfs
1881 WHERE mmtt.transaction_temp_id = l_transaction_id
1882 AND mmtt.transaction_temp_id = mtlt.transaction_temp_id(+)
1883 AND mmtt.organization_id = msi.organization_id
1884 AND mmtt.inventory_item_id = msi.inventory_item_id
1885 AND mmtt.transaction_source_id = wfs.wip_entity_id(+)
1886 AND mmtt.transaction_source_id = we.wip_entity_id(+)
1887 AND we.entity_type(+) = 4; -- Flow
1888 -- Bug 2904142 Add next where clause
1889 /*AND mmtt.inventory_item_id IS NOT NULL
1890 AND mmtt.content_lpn_id IS NULL;*/ -- Modified for the bug # 5740354
1891
1892 -- Bug 2904142, add a new cusor to query the exploded MMTT line
1893 CURSOR flow_complete_mmtt_lpn_curs IS
1894 SELECT mmtt.inventory_item_id,
1895 mmtt.organization_id,
1896 NVL(mmtt.subinventory_code,
1897 wfs.completion_subinventory) subinventory_code,
1898 NVL(mmtt.locator_id, wfs.completion_locator_id) locator_id,
1899 NVL(mtlt.lot_number,mmtt.lot_number) lot_number ,
1900 mmtt.serial_number,
1901 NULL serial_number_start,
1902 NULL serial_number_end,
1903 mmtt.cost_group_id ,
1904 NVL(mmtt.project_id , wfs.project_id) project_id ,
1905 NVL(mmtt.task_id , wfs.task_id) task_id ,
1906 mmtt.transaction_quantity quantity,
1907 mmtt.transaction_uom uom,
1908 mmtt.revision revision,
1909 NVL(mmtt.alternate_bom_designator,
1910 wfs.alternate_bom_designator) alternate_bom_designator,
1911 NVL(mmtt.alternate_routing_designator,
1912 wfs.alternate_routing_designator) alternate_routing_designator,
1913 NVL(mmtt.demand_source_header_id,
1914 wfs.demand_source_header_id) sale_header_id,
1915 NVL(mmtt.demand_source_line, wfs.demand_source_line) sale_line_id,
1916 NVL(mmtt.kanban_card_id,wfs.kanban_card_id) kanban_card_id,
1917 NVL(NVL(mmtt.transfer_lpn_id, mmtt.content_lpn_id), mmtt.lpn_id) lpn_id,
1918 mmtt.transaction_source_id wip_entity_id,
1919 NVL(mmtt.schedule_number, wfs.schedule_number) schedule_number,
1920 msi.lot_control_code,
1921 msi.serial_number_control_code serial_number_control_code,
1922 mmtt.transaction_temp_id transaction_id
1923 FROM MTL_MATERIAL_TRANSACTIONS_TEMP mmtt,
1924 MTL_TRANSACTION_LOTS_TEMP mtlt,
1925 MTL_SYSTEM_ITEMS msi,
1926 WIP_ENTITIES WE,
1927 WIP_FLOW_SCHEDULES wfs,
1928 MTL_MATERIAL_TRANSACTIONS_TEMP mmtt_orig
1929 WHERE mmtt.transaction_temp_id = mtlt.transaction_temp_id(+)
1930 AND mmtt.organization_id = msi.organization_id
1931 AND mmtt.inventory_item_id = msi.inventory_item_id
1932 AND mmtt.transaction_source_id = wfs.wip_entity_id(+)
1933 AND mmtt.transaction_source_id = we.wip_entity_id(+)
1934 AND we.entity_type(+) = 4
1935 AND mmtt.transaction_header_id = mmtt_orig.transaction_header_id
1936 AND mmtt.transaction_temp_id <>mmtt_orig.transaction_temp_id
1937 AND mmtt_orig.content_lpn_id IS NOT NULL
1938 AND mmtt_orig.transaction_temp_id = l_transaction_id;
1939
1940 -- Driving cursor
1941 CURSOR flow_complete_mti_curs IS
1942 SELECT mti.inventory_item_id,
1943 mti.organization_id,
1944 NVL(mti.subinventory_code,
1945 wfs.completion_subinventory) subinventory_code,
1946 NVL(mti.locator_id, wfs.completion_locator_id) locator_id,
1947 -- mti.source_lot_number lot_number, -- Commented for Bug 2894995 : joabraha
1948 mtli.lot_number lot_number, -- Added for Bug 2894995 : joabraha
1949 NULL serial_number,
1950 NULL serial_number_start,
1951 NULL serial_number_end,
1952 mti.cost_group_id,
1953 NVL(mti.project_id , wfs.project_id) project_id ,
1954 NVL(mti.task_id , wfs.task_id) task_id ,
1955 mti.transaction_quantity quantity,
1956 mti.transaction_uom uom,
1957 mti.revision revision,
1958 NVL(mti.alternate_bom_designator,
1959 wfs.alternate_bom_designator) alternate_bom_designator,
1960 NVL(mti.alternate_routing_designator,
1961 wfs.alternate_routing_designator) alternate_routing_designator,
1962 NVL(mti.demand_source_header_id,
1963 wfs.demand_source_header_id) sale_header_id,
1964 NVL(mti.demand_source_line, wfs.demand_source_line) sale_line_id,
1965 NVL(mti.kanban_card_id,wfs.kanban_card_id) kanban_card_id,
1966 mti.transfer_lpn_id lpn_id ,
1967 mti.transaction_source_id wip_entity_id,
1968 NVL(mti.schedule_number, wfs.schedule_number) schedule_number,
1969 msi.lot_control_code,
1970 msi.serial_number_control_code,
1971 mti.transaction_interface_id transaction_id
1972 FROM MTL_TRANSACTIONS_INTERFACE mti,
1973 -- MTL_TRANSACTION_LOTS_INTERFACE mtli, -- Bug 2576424 : joabraha
1974 -- Bug 2904877, change back to using MTLI,
1975 -- this depends on WIP's fix on bug 2904857
1976 MTL_TRANSACTION_LOTS_INTERFACE mtli,
1977 --MTL_TRANSACTION_LOTS_TEMP mtlt,
1978 MTL_SYSTEM_ITEMS msi,
1979 WIP_FLOW_SCHEDULES wfs
1980 WHERE mti.transaction_interface_id = l_transaction_id
1981 -- AND mti.transaction_interface_id = mtli.transaction_interface_id(+) -- Bug 2576424 : joabraha
1982 -- AND mti.transaction_interface_id = mtlt.transaction_temp_id(+) -- Bug 2576424 : joabraha
1983 -- Bug 2904877, change back to using MTLI,
1984 -- this depends on WIP's fix on bug 2904857
1985 AND mti.transaction_interface_id = mtli.transaction_interface_id(+)
1986 AND mti.organization_id = msi.organization_id
1987 AND mti.inventory_item_id = msi.inventory_item_id
1988 AND mti.transaction_source_id = wfs.wip_entity_id(+);
1989 --AND mti.wip_entity_type = 4; -- Flow /* Commented out as part of Bug# 3560377 */
1990
1991
1992 -- Driving cursor
1993 CURSOR flow_complete_mtrl_curs IS
1994 SELECT mtrl.inventory_item_id,
1995 mtrl.organization_id,
1996 NVL(mmtt.subinventory_code, mtrl.to_subinventory_code) subinventory_code,
1997 mtrl.to_locator_id locator_id,
1998 NVL(mmtt.lot_number,mtrl.lot_number) lot_number,
1999 NULL serial_number,
2000 NVL(mtrl.serial_number_start,'@@') serial_number_start,
2001 NVL(mtrl.serial_number_end,'@@') serial_number_end,
2002 mtrl.to_cost_group_id cost_group_id,
2003 mtrl.project_id ,
2004 mtrl.task_id ,
2005 mtrl.quantity quantity,
2006 mtrl.uom_code uom,
2007 mtrl.revision revision,
2008 mmtt.alternate_bom_designator alternate_bom_designator,
2009 mmtt.alternate_routing_designator alternate_routing_designator,
2010 mmtt.demand_source_header_id sale_header_id,
2011 NVL(mmtt.demand_source_line, mtrl.txn_source_line_id) sale_line_id,
2012 mmtt.kanban_card_id kanban_card_id,
2013 mtrl.lpn_id lpn_id ,
2014 mtrl.txn_source_id wip_entity_id,
2015 mmtt.schedule_number schedule_number,
2016 msi.lot_control_code,
2017 msi.serial_number_control_code,
2018 mmtt.transaction_temp_id transaction_id
2019 FROM MTL_TXN_REQUEST_LINES mtrl,
2020 MTL_MATERIAL_TRANSACTIONS_TEMP mmtt,
2021 MTL_TRANSACTION_LOTS_TEMP mtlt,
2022 MTL_SYSTEM_ITEMS msi,
2023 WIP_ENTITIES WE
2024 WHERE mtrl.line_id = l_transaction_id
2025 AND mtrl.line_id = mmtt.move_order_line_id(+)
2026 AND mmtt.transaction_temp_id = mtlt.transaction_temp_id(+)
2027 AND mtrl.organization_id = msi.organization_id
2028 AND mtrl.inventory_item_id = msi.inventory_item_id
2029 AND mtrl.txn_source_id = we.wip_entity_id(+)
2030 AND we.entity_type(+) = 4;
2031
2032 -- Driving cursor
2033 CURSOR flow_schedule_mmtt_curs IS
2034 SELECT wfs.primary_item_id inventory_item_id,
2035 wfs.organization_id organization_id,
2036 NVL(mmtt.subinventory_code, wfs.completion_subinventory) subinventory_code,
2037 NVL(mmtt.locator_id, wfs.completion_locator_id) locator_id,
2038 NULL lot_number ,
2039 NULL serial_number,
2040 NULL serial_number_start,
2041 NULL serial_number_end,
2042 mmtt.cost_group_id cost_group_id,
2043 NVL(mmtt.project_id, wfs.project_id) project_id ,
2044 NVL(mmtt.task_id, wfs.task_id) task_id ,
2045 NVL(mmtt.transaction_quantity, wfs.quantity_completed) quantity,
2046 mmtt.transaction_uom uom,
2047 mmtt.revision revision,
2048 NVL(mmtt.alternate_bom_designator,wfs.alternate_bom_designator) alternate_bom_designator,
2049 NVL(mmtt.alternate_routing_designator, wfs.alternate_routing_designator) alternate_routing_designator,
2050 NVL(mmtt.demand_source_header_id,
2051 wfs.demand_source_header_id ) sale_header_id,
2052 NVL(mmtt.demand_source_line,
2053 wfs.demand_source_line) sale_line_id,
2054 NVL(mmtt.kanban_card_id,wfs.kanban_card_id) kanban_card_id,
2055 NVL(NVL(mmtt.transfer_lpn_id, mmtt.content_lpn_id), mmtt.lpn_id) lpn_id,
2056 wfs.wip_entity_id wip_entity_id,
2057 wfs.schedule_number schedule_number,
2058 msi.lot_control_code,
2059 msi.serial_number_control_code,
2060 mmtt.transaction_temp_id transaction_id
2061 FROM WIP_FLOW_SCHEDULES wfs,
2062 MTL_MATERIAL_TRANSACTIONS_TEMP mmtt,
2063 MTL_TRANSACTION_LOTS_TEMP mtlt,
2064 MTL_SYSTEM_ITEMS msi,
2065 WIP_ENTITIES WE
2066 WHERE mmtt.transaction_source_type_id = 5
2067 AND mmtt.transaction_action_id = 31
2068 AND mmtt.organization_id = wfs.organization_id
2069 AND mmtt.inventory_item_id = wfs.primary_item_id
2070 AND mmtt.transaction_source_id = wfs.wip_entity_id
2071 AND mmtt.transaction_temp_id = mtlt.transaction_temp_id(+)
2072 AND wfs.organization_id = msi.organization_id
2073 AND wfs.primary_item_id = msi.inventory_item_id
2074 AND wfs.wip_entity_id = we.wip_entity_id
2075 AND we.entity_type = 4 -- Flow
2076 AND wfs.wip_entity_id = l_transaction_id;
2077
2078 -- Driving cursor
2079 CURSOR flow_schedule_mmt_curs IS
2080 SELECT wfs.primary_item_id inventory_item_id,
2081 wfs.organization_id organization_id,
2082 NVL(mmt.subinventory_code, wfs.completion_subinventory) subinventory_code,
2083 NVL(mmt.locator_id, wfs.completion_locator_id) locator_id,
2084 mtln.lot_number lot_number ,
2085 NULL serial_number,
2086 NULL serial_number_start,
2087 NULL serial_number_end,
2088 mmt.cost_group_id cost_group_id,
2089 NVL(mmt.project_id, wfs.project_id) project_id ,
2090 NVL(mmt.task_id, wfs.task_id) task_id ,
2091 NVL(mmt.transaction_quantity, wfs.quantity_completed) quantity,
2092 mmt.transaction_uom uom,
2093 mmt.revision revision,
2094 wfs.alternate_bom_designator alternate_bom_designator,
2095 wfs.alternate_routing_designator alternate_routing_designator,
2096 wfs.demand_source_header_id sale_header_id,
2097 wfs.demand_source_line sale_line_id,
2098 wfs.kanban_card_id kanban_card_id,
2099 NVL(NVL(mmt.transfer_lpn_id, mmt.content_lpn_id), mmt.lpn_id) lpn_id ,
2100 wfs.wip_entity_id wip_entity_id,
2101 wfs.schedule_number schedule_number,
2102 msi.lot_control_code,
2103 msi.serial_number_control_code,
2104 mmt.transaction_id transaction_id
2105 FROM wip_flow_schedules wfs,
2106 mtl_material_transactions mmt,
2107 mtl_transaction_lot_numbers mtln,
2108 wip_entities we,
2109 mtl_system_items msi
2110 WHERE mmt.transaction_source_type_id = 5
2111 AND mmt.transaction_action_id = 31
2112 AND mmt.organization_id = wfs.organization_id
2113 AND mmt.inventory_item_id = wfs.primary_item_id
2114 AND mmt.transaction_source_id = wfs.wip_entity_id
2115 AND wfs.organization_id = msi.organization_id
2116 AND wfs.primary_item_id = msi.inventory_item_id
2117 AND we.wip_entity_id = wfs.wip_entity_id
2118 AND we.entity_type = 4
2119 AND mmt.transaction_id = mtln.transaction_id(+)
2120 AND wfs.wip_entity_id = l_transaction_id;
2121
2122 -- Bug 2728468 Adhoc printing enabled for schedules that is not completed yet.
2123 -- Added new cursor of flow_schedule_wfs_curs to retrieve schedule information
2124 -- from WIP_FLOW_SCHEDULES
2125 CURSOR flow_schedule_wfs_curs IS
2126 SELECT wfs.primary_item_id inventory_item_id,
2127 wfs.organization_id organization_id,
2128 wfs.completion_subinventory subinventory_code,
2129 wfs.completion_locator_id locator_id,
2130 NULL lot_number ,
2131 NULL serial_number,
2132 NULL serial_number_start,
2133 NULL serial_number_end,
2134 NULL cost_group_id,
2135 wfs.project_id project_id ,
2136 wfs.task_id task_id ,
2137 wfs.quantity_completed quantity,
2138 msi.primary_uom_code uom,
2139 NULL revision,
2140 wfs.alternate_bom_designator alternate_bom_designator,
2141 wfs.alternate_routing_designator alternate_routing_designator,
2142 wfs.demand_source_header_id sale_header_id,
2143 wfs.demand_source_line sale_line_id,
2144 wfs.kanban_card_id kanban_card_id,
2145 NULL lpn_id ,
2146 wfs.wip_entity_id wip_entity_id,
2147 wfs.schedule_number schedule_number,
2148 msi.lot_control_code,
2149 msi.serial_number_control_code,
2150 NULL transaction_id
2151 FROM wip_flow_schedules wfs,
2152 wip_entities we,
2153 mtl_system_items msi
2154 WHERE wfs.organization_id = msi.organization_id
2155 AND wfs.primary_item_id = msi.inventory_item_id
2156 AND we.wip_entity_id = wfs.wip_entity_id
2157 AND we.entity_type = 4
2158 AND wfs.wip_entity_id = l_transaction_id;
2159
2160 /* The following cursor has been modified for the bug# 5475495
2161 * The cursor will be opened only for LPN- Flow/WorkOrderLess completion Txn's
2162 * ie., transaction_identifier will be 1(i.e., MMTT_TYPE in this PACKAGE)
2163 *
2164 * Currently the cursor is fetching data using mtl_serial_numbers_temp table.
2165 * whereas the data will present only on mtl_serial_numbers table.
2166 * hence, modified the code to retrieve the serial numbers based on lpn_id in
2167 * mtl_material_transactions_temp and lpn_id in mtl_serial_numbers.
2168
2169 -- Bug 2882958, added parameter p_lot_number to restrict on lot
2170 CURSOR mmtt_serial_curs(p_lot_number VARCHAR2) IS
2171 -- Serial Control
2172 SELECT msn.serial_number
2173 FROM mtl_material_transactions_temp mmtt,
2174 mtl_serial_numbers_temp msnt, mtl_serial_numbers msn
2175 where mmtt.transaction_temp_id = msnt.transaction_temp_id
2176 and msnt.fm_serial_number <= msn.serial_number AND
2177 msnt.to_serial_number >= msn.serial_number
2178 and mmtt.organization_id = msn.current_organization_id
2179 and mmtt.inventory_item_id = msn.inventory_item_id
2180 and mmtt.transaction_temp_id = l_transaction_id
2181 UNION
2182 -- Lot and Serial Control
2183 SELECT msn.serial_number
2184 FROM mtl_material_transactions_temp mmtt, mtl_transaction_lots_temp mtlt,
2185 mtl_serial_numbers_temp msnt, mtl_serial_numbers msn
2186 where mmtt.transaction_temp_id = mtlt.transaction_temp_id
2187 and mtlt.serial_transaction_temp_id = msnt.transaction_temp_id
2188 and msnt.fm_serial_number <= msn.serial_number AND
2189 msnt.to_serial_number >= msn.serial_number
2190 and mmtt.organization_id = msn.current_organization_id
2191 and mmtt.inventory_item_id = msn.inventory_item_id
2192 and mmtt.transaction_temp_id = l_transaction_id
2193 -- Bug 2882958, added parameter p_lot_number to restrict on lot
2194 and mtlt.lot_number = p_lot_number; */
2195
2196 CURSOR mmtt_serial_curs(p_lot_number VARCHAR2) IS
2197 SELECT msn.serial_number
2198 FROM mtl_material_transactions_temp mmtt,
2199 mtl_serial_numbers msn
2200 where mmtt.transaction_temp_id = l_transaction_id
2201 and (mmtt.lpn_id = msn.lpn_id
2202 or mmtt.content_lpn_id = msn.lpn_id) -- Modified for the bug # 5740354
2203 and mmtt.inventory_item_id = msn.inventory_item_id
2204 UNION
2205 SELECT msn.serial_number
2206 FROM mtl_material_transactions_temp mmtt,
2207 mtl_serial_numbers msn
2208 where mmtt.transaction_temp_id = l_transaction_id
2209 and (mmtt.lpn_id = msn.lpn_id
2210 or mmtt.content_lpn_id = msn.lpn_id) -- Modified for the bug # 5740354
2211 and mmtt.inventory_item_id = msn.inventory_item_id
2212 and msn.lot_number = p_lot_number;
2213
2214
2215 -- Bug 2882958, added parameter p_lot_number to restrict on lot
2216 CURSOR mti_serial_curs(p_lot_number VARCHAR2) IS
2217 -- Serial Control
2218 SELECT msn.serial_number
2219 FROM mtl_transactions_interface mti,
2220 mtl_serial_numbers_interface msni, mtl_serial_numbers msn
2221 where mti.transaction_interface_id =msni.transaction_interface_id
2222 and msni.fm_serial_number <= msn.serial_number AND
2223 msni.to_serial_number >= msn.serial_number
2224 and mti.organization_id = msn.current_organization_id
2225 and mti.inventory_item_id = msn.inventory_item_id
2226 and mti.transaction_interface_id = l_transaction_id
2227 UNION
2228 -- Lot and Serial Control
2229 SELECT msn.serial_number
2230 FROM mtl_transactions_interface mti, mtl_transaction_lots_interface mtli,
2231 mtl_serial_numbers_interface msni, mtl_serial_numbers msn
2232 where mti.transaction_interface_id = mtli.transaction_interface_id
2233 and mtli.serial_transaction_temp_id = msni.transaction_interface_id
2234 and msni.fm_serial_number <= msn.serial_number AND
2235 msni.to_serial_number >= msn.serial_number
2236 and mti.organization_id = msn.current_organization_id
2237 and mti.inventory_item_id = msn.inventory_item_id
2238 and mti.transaction_interface_id = l_transaction_id
2239 -- Bug 2882958, added parameter p_lot_number to restrict on lot
2240 and mtli.lot_number = p_lot_number;
2241
2242 CURSOR serial_curs IS
2243 select serial_number
2244 FROM mtl_serial_numbers
2245 WHERE current_organization_id = l_in_rec.organization_id
2246 AND inventory_item_id = l_in_rec.inventory_item_id
2247 AND current_subinventory_code = l_in_rec.subinventory_code
2248 AND NVL(revision, '@@@@') = NVL(l_in_rec.revision,'@@@@')
2249 AND NVL(lot_number, '@@@@') = NVL(l_in_rec.lot_number, '@@@@')
2250 AND last_transaction_id = l_transaction_id;
2251
2252 l_organization_id NUMBER := null;
2253 l_wip_entity_id NUMBER := null;
2254 l_inventory_item_id NUMBER := null;
2255 l_operation_seq_num NUMBER := null;
2256 l_lpn_id NUMBER := null;
2257 l_revision VARCHAR2(3) := null;
2258 -- Increased lot size to 80 Char - Mercy Thomas - B4625329
2259 l_lot_number VARCHAR2(80) := null;
2260 l_cost_group_id NUMBER := null;
2261 l_quantity NUMBER := null;
2262 l_uom VARCHAR2(3) := null;
2263 l_subinventory_code mtl_material_transactions_temp.subinventory_code%TYPE;
2264 l_locator_id NUMBER := null; -- Added for bug13781065
2265
2266 l_selected_fields INV_LABEL.label_field_variable_tbl_type;
2267 l_selected_fields_count NUMBER;
2268
2269 l_label_format_id NUMBER := 0 ;
2270 l_label_format VARCHAR2(100);
2271 l_printer VARCHAR2(30);
2272 l_field_id NUMBER := 0 ;
2273
2274 l_prev_label_format_id NUMBER :=0;
2275
2276 l_content_item_data LONG;
2277 l_content_rec_index NUMBER := 0;
2278
2279 l_return_status VARCHAR2(240);
2280 l_error_message VARCHAR2(240);
2281 l_msg_count NUMBER;
2282 l_api_status VARCHAR2(240);
2283 l_msg_data VARCHAR2(240);
2284 i NUMBER;
2285 j NUMBER;
2286 l_field_value VARCHAR2(240);
2287
2288 l_id number;
2289 l_label_index NUMBER := 1;
2290 l_label_request_id NUMBER;
2291 -- I cleanup, user l_prev_sub to record the previous subinventory
2292 --so that get_printer is not called if the subinventory is the same
2293 l_prev_sub VARCHAR2(30);
2294
2295 BEGIN
2296 l_debug := INV_LABEL.l_debug;
2297
2298 -- Initialize return status as success
2299 x_return_status := FND_API.G_RET_STS_SUCCESS;
2300 IF (l_debug = 1) THEN
2301 trace('**In PVT10: Flow Content label**');
2302 trace(' Business_flow: '||p_label_type_info.business_flow_code);
2303 trace(' Transaction ID:'||p_transaction_id);
2304 trace(' Transaction Identifier:'||p_transaction_identifier);
2305 END IF;
2306
2307 -- ==========================================================
2308 -- Validate Input Parameters
2309 -- ==========================================================
2310
2311 IF (p_transaction_id IS NULL) AND (p_input_param.transaction_temp_id IS NULL)
2312 THEN
2313 IF (l_debug = 1) THEN
2314 trace('Neither p_transaction_id nor p_input_param.transaction_temp_id is passed . ');
2315 END IF;
2316 RAISE FND_API.G_EXC_ERROR;
2317 END IF;
2318 l_counter := 0;
2319 IF p_transaction_identifier IS NULL THEN
2320 l_transaction_identifier := 0;
2321 ELSE
2322 l_transaction_identifier := p_transaction_identifier;
2323 END IF;
2324
2325 l_transaction_id := p_transaction_id;
2326 -- ====================================================
2327 -- Manual Printing is for specific wip_entity_id
2328 -- ====================================================
2329 IF (p_input_param.transaction_temp_id IS NOT NULL) THEN
2330 l_transaction_identifier := WFS_TYPE;
2331 l_transaction_id := p_input_param.transaction_temp_id;
2332 END IF;
2333
2334 IF (l_transaction_identifier = MMTT_TYPE ) THEN
2335 OPEN flow_complete_mmtt_curs;
2336 LOOP
2337 FETCH flow_complete_mmtt_curs INTO l_in_rec;
2338 EXIT WHEN flow_complete_mmtt_curs%NOTFOUND;
2339 l_counter := l_counter + 1;
2340 l_transaction_id := l_in_rec.transaction_id;
2341 IF (l_in_rec.serial_number_control_code in (2,5,6) ) THEN
2342 -- The item is serial control
2343 -- initialize serial_number to check for error
2344 l_serial_not_found := TRUE;
2345 FOR serial_rec IN mmtt_serial_curs(l_in_rec.lot_number) LOOP
2346 l_in_tbl(l_counter) := l_in_rec;
2347 l_in_tbl(l_counter).serial_number := serial_rec.serial_number;
2348 l_counter := l_counter + 1;
2349 l_serial_not_found := FALSE;
2350 END LOOP;
2351
2352 IF l_serial_not_found THEN
2353 IF (l_debug = 1) THEN
2354 trace('Item is serial number control. No serial Number found !!!');
2355 END IF;
2356 FND_MESSAGE.SET_NAME('INV', 'WMS_SERIAL_FOUND');
2357 FND_MSG_PUB.ADD;
2358 RAISE SERIAL_EXCEPTION;
2359 -- Bug 2882958
2360 ELSE
2361 -- Reduce l_count to avoid extra increment
2362 l_counter := l_counter - 1;
2363 END IF;
2364
2365 ELSE
2366 l_in_tbl(l_counter) := l_in_rec;
2367 END IF;
2368 END LOOP;
2369 CLOSE flow_complete_mmtt_curs;
2370 -- Bug 2904142, For Flow completion with putaway drop (35),
2371 -- The MMTT record is only has content_lpn_id populated and
2372 -- the detail item, lot, serial information should be retrieved
2373 -- from the new MMTT/MTLT/MSNT that will be exploded from the original MMTT line
2374 IF (l_counter = 0) THEN
2375 OPEN flow_complete_mmtt_lpn_curs;
2376 LOOP
2377 FETCH flow_complete_mmtt_lpn_curs INTO l_in_rec;
2378 EXIT WHEN flow_complete_mmtt_lpn_curs%NOTFOUND;
2379 l_counter := l_counter + 1;
2380 l_transaction_id := l_in_rec.transaction_id;
2381 IF (l_in_rec.serial_number_control_code in (2,5,6) ) THEN
2382 -- The item is serial control
2383 -- initialize serial_number to check for error
2384 l_serial_not_found := TRUE;
2385 FOR serial_rec IN mmtt_serial_curs(l_in_rec.lot_number) LOOP
2386 l_in_tbl(l_counter) := l_in_rec;
2387 l_in_tbl(l_counter).serial_number := serial_rec.serial_number;
2388 l_counter := l_counter + 1;
2389 l_serial_not_found := FALSE;
2390 END LOOP;
2391
2392 IF l_serial_not_found THEN
2393 trace('Item is serial number control. No serial Number found !!!');
2394 FND_MESSAGE.SET_NAME('INV', 'WMS_SERIAL_FOUND');
2395 FND_MSG_PUB.ADD;
2396 RAISE SERIAL_EXCEPTION;
2397 -- Bug 2882958
2398 ELSE
2399 -- Reduce l_count to avoid extra increment
2400 l_counter := l_counter - 1;
2401 END IF;
2402 ELSE
2403 l_in_tbl(l_counter) := l_in_rec;
2404 END IF;
2405 END LOOP;
2406 CLOSE flow_complete_mmtt_lpn_curs;
2407 END IF;
2408
2409 IF (l_counter = 0) THEN
2410 IF (l_debug = 1) THEN
2411 trace(' No material found for Transaction ID:'||p_transaction_id);
2412 END IF;
2413 RAISE NO_FLOW_DATA_FOUND_X;
2414 END IF ;
2415
2416 ELSIF (l_transaction_identifier = MTI_TYPE ) THEN
2417 OPEN flow_complete_mti_curs;
2418 LOOP
2419 FETCH flow_complete_mti_curs INTO l_in_rec;
2420 EXIT WHEN flow_complete_mti_curs%NOTFOUND;
2421 l_counter := l_counter + 1;
2422 l_transaction_id := l_in_rec.transaction_id;
2423 IF (l_in_rec.serial_number_control_code in (2,5,6) ) THEN
2424 -- The item is serial control
2425 -- initialize serial_number to check for error
2426 l_serial_not_found := TRUE;
2427 -- Bug 2882958, add lot_number to cursor mti_serial_curs
2428 FOR serial_rec IN mti_serial_curs(l_in_rec.lot_number) LOOP
2429 l_in_tbl(l_counter) := l_in_rec;
2430 l_in_tbl(l_counter).serial_number := serial_rec.serial_number;
2431 l_counter := l_counter + 1;
2432 l_serial_not_found := FALSE;
2433 END LOOP;
2434
2435 IF l_serial_not_found THEN
2436 IF (l_debug = 1) THEN
2437 trace('Item is serial number control. No serial Number found !!!');
2438 END IF;
2439 FND_MESSAGE.SET_NAME('INV', 'WMS_SERIAL_FOUND');
2440 FND_MSG_PUB.ADD;
2441 RAISE SERIAL_EXCEPTION;
2442 -- Bug 2882958
2443 ELSE
2444 -- Reduce l_count to avoid extra increment
2445 l_counter := l_counter - 1;
2446 END IF;
2447 ELSE
2448 l_in_tbl(l_counter) := l_in_rec;
2449 END IF;
2450 END LOOP;
2451 CLOSE flow_complete_mti_curs;
2452
2453 IF (l_counter = 0) THEN
2454 IF (l_debug = 1) THEN
2455 trace(' No material found for Interface Transaction ID:'|| p_transaction_id);
2456 END IF;
2457 RAISE NO_FLOW_DATA_FOUND_X;
2458 END IF ;
2459
2460 ELSIF (l_transaction_identifier = MTRL_TYPE ) THEN
2461
2462 OPEN flow_complete_mtrl_curs;
2463 LOOP
2464 FETCH flow_complete_mtrl_curs INTO l_in_rec;
2465 EXIT WHEN flow_complete_mtrl_curs%NOTFOUND;
2466 l_counter := l_counter + 1;
2467 l_transaction_id := l_in_rec.transaction_id;
2468 IF (l_in_rec.serial_number_control_code in (2,5,6) ) THEN
2469 -- The item is serial control
2470 IF (l_debug = 1) THEN
2471 trace(' Before call to GET_SERIALS_BETWEEN_RANGE');
2472 END IF;
2473 IF (l_in_rec.serial_number_start) <> (l_in_rec.serial_number_end) THEN
2474 INV_LABEL.GET_NUMBER_BETWEEN_RANGE(
2475 fm_x_number => l_in_rec.serial_number_start
2476 ,to_x_number => l_in_rec.serial_number_end
2477 ,x_return_status => l_return_status
2478 ,x_number_table => l_serial_numbers_table);
2479 IF l_return_status <> 'S' THEN
2480 FND_MESSAGE.SET_NAME('WMS', 'WMS_GET_SER_CUR_FAILED');
2481 FND_MSG_PUB.ADD;
2482 RAISE SERIAL_EXCEPTION;
2483 END IF;
2484
2485 FOR j IN 1..l_serial_numbers_table.count LOOP
2486 l_in_tbl(l_counter) := l_in_rec;
2487 l_in_tbl(l_counter).serial_number := l_serial_numbers_table(j);
2488 l_counter := l_counter + 1;
2489
2490 END LOOP;
2491 IF ( l_serial_numbers_table.count = 0) THEN
2492 IF (l_debug = 1) THEN
2493 trace('Item is serial number control. No serial Number found !!! ');
2494 END IF;
2495 FND_MESSAGE.SET_NAME('INV', 'WMS_SERIAL_FOUND');
2496 FND_MSG_PUB.ADD;
2497 RAISE SERIAL_EXCEPTION;
2498
2499 END IF;
2500
2501 END IF;
2502 ELSE
2503 l_in_tbl(l_counter) := l_in_rec;
2504 END IF;
2505 END LOOP;
2506 CLOSE flow_complete_mtrl_curs;
2507
2508 IF (l_counter = 0) THEN
2509 IF (l_debug = 1) THEN
2510 trace(' No material found for Move Order Line ID:'||p_transaction_id);
2511 END IF;
2512 RAISE NO_FLOW_DATA_FOUND_X;
2513 END IF ;
2514
2515 ELSIF (l_transaction_identifier = WFS_TYPE ) THEN
2516 -- Bug 2728468 Adhoc printing enabled for schedules that is not completed yet.
2517 -- Remove the following query from MMTT because from adhoc printing does not
2518 -- look at schedules that is being processed.
2519 /*OPEN flow_schedule_mmtt_curs;
2520 LOOP
2521 FETCH flow_schedule_mmtt_curs INTO l_in_rec;
2522 EXIT WHEN flow_schedule_mmtt_curs%NOTFOUND;
2523 l_counter := l_counter + 1;
2524 l_transaction_id := l_in_rec.transaction_id;
2525 l_in_tbl(l_counter) := l_in_rec;
2526 IF (l_in_rec.serial_number_control_code in (2,5,6) ) THEN
2527 -- The item is serial control
2528 -- initialize serial_number to check for error
2529 l_serial_not_found := TRUE;
2530 FOR serial_rec IN serial_curs LOOP
2531 l_in_tbl(l_counter) := l_in_rec;
2532 l_in_tbl(l_counter).serial_number := serial_rec.serial_number;
2533 l_counter := l_counter + 1;
2534 l_serial_not_found := FALSE;
2535
2536 END LOOP;
2537
2538 IF l_serial_not_found THEN
2539 IF (l_debug = 1) THEN
2540 trace('Item is serial number control. No serial Number found ');
2541 END IF;
2542 FND_MESSAGE.SET_NAME('INV', 'WMS_SERIAL_FOUND');
2543 FND_MSG_PUB.ADD;
2544 -- RAISE SERIAL_EXCEPTION;
2545 END IF;
2546 ELSE
2547 l_in_tbl(l_counter) := l_in_rec;
2548 END IF;
2549 END LOOP;
2550 CLOSE flow_schedule_mmtt_curs;*/
2551 OPEN flow_schedule_mmt_curs;
2552 -- Attempt to retrieve lot/serial from mmt
2553 LOOP
2554 FETCH flow_schedule_mmt_curs INTO l_in_rec;
2555 EXIT WHEN flow_schedule_mmt_curs%NOTFOUND;
2556 l_counter := l_counter + 1;
2557 l_transaction_id := l_in_rec.transaction_id;
2558 l_in_tbl(l_counter) := l_in_rec;
2559 IF (l_in_rec.serial_number_control_code in (2,5,6) ) THEN
2560 -- The item is serial control
2561 -- initialize serial_number to check for error
2562 l_serial_not_found := TRUE;
2563 FOR serial_rec IN serial_curs LOOP
2564 l_in_tbl(l_counter) := l_in_rec;
2565 l_in_tbl(l_counter).serial_number := serial_rec.serial_number;
2566 l_counter := l_counter + 1;
2567 l_serial_not_found := FALSE;
2568
2569 END LOOP;
2570
2571 IF l_serial_not_found THEN
2572 IF (l_debug = 1) THEN
2573 trace('Item is serial number control. No serial Number found ');
2574 END IF;
2575 FND_MESSAGE.SET_NAME('INV', 'WMS_SERIAL_FOUND');
2576 FND_MSG_PUB.ADD;
2577 -- RAISE SERIAL_EXCEPTION;
2578 END IF;
2579 ELSE
2580 l_in_tbl(l_counter) := l_in_rec;
2581 END IF;
2582 END LOOP;
2583 CLOSE flow_schedule_mmt_curs;
2584
2585 -- Bug 2728468 Adhoc printing enabled for schedules that is not completed yet.
2586 -- Added new cursor of flow_schedule_wfs_curs to retrieve schedule information
2587 -- from WIP_FLOW_SCHEDULES
2588 IF (l_in_rec.transaction_id IS NULL) THEN
2589 l_counter := 0;
2590 -- When no MMT record for the schedule, query directly from WFS
2591 OPEN flow_schedule_wfs_curs;
2592 LOOP
2593 FETCH flow_schedule_wfs_curs INTO l_in_rec;
2594 EXIT WHEN flow_schedule_wfs_curs%NOTFOUND;
2595 l_counter := l_counter + 1;
2596 l_transaction_id := l_in_rec.transaction_id;
2597 l_in_tbl(l_counter) := l_in_rec;
2598 END LOOP;
2599 CLOSE flow_schedule_wfs_curs;
2600
2601 END IF;
2602
2603 IF (l_counter = 0) THEN
2604 IF (l_debug = 1) THEN
2605 trace(' No material found for Wip Flow Schedule ID:'||p_transaction_id);
2606 END IF;
2607 RAISE NO_FLOW_DATA_FOUND_X;
2608 END IF ;
2609
2610 ELSE
2611 IF (l_debug = 1) THEN
2612 trace(' Invalid transaction_identifier passed '||p_transaction_identifier);
2613 END IF;
2614 RAISE FND_API.G_EXC_ERROR;
2615 END IF;
2616
2617 IF (l_debug = 1) THEN
2618 trace(' Getting default format selected fields ');
2619 END IF;
2620 INV_LABEL.GET_VARIABLES_FOR_FORMAT(
2621 x_variables => l_selected_fields
2622 ,x_variables_count => l_selected_fields_count
2623 ,p_format_id => p_label_type_info.default_format_id);
2624
2625 IF (l_selected_fields_count=0) OR (l_selected_fields.count =0 ) THEN
2626 IF (l_debug = 1) THEN
2627 trace('no fields defined for this format: ' || p_label_type_info.default_format_id || ',' || p_label_type_info.default_format_name);
2628 END IF;
2629 RAISE NO_LABEL_FORMAT_FOUND_X;
2630 END IF;
2631
2632 IF (l_debug = 1) THEN
2633 trace(' Found format ID and name : ' || p_label_type_info.default_format_id || p_label_type_info.default_format_name);
2634 END IF;
2635
2636 IF (l_debug = 1) THEN
2637 trace(' Found variable defined for this format, cont = ' || l_selected_fields_count);
2638 END IF;
2639
2640 l_content_rec_index := 0;
2641 IF (l_debug = 1) THEN
2642 trace('** in PVT10.get_variable_data ** , start ');
2643 END IF;
2644 l_prev_label_format_id := p_label_type_info.default_format_id;
2645 l_printer := p_label_type_info.default_printer;
2646
2647 --
2648 -- Initialize l_prev_in_tbl
2649 --
2650 l_prev_in_tbl.inventory_item_id := -9999;
2651 l_prev_in_tbl.subinventory_code := '@';
2652 l_prev_in_tbl.lot_number := -9999;
2653 l_prev_in_tbl.serial_number := -9999;
2654 l_prev_in_tbl.revision := '@@@';
2655 l_prev_in_tbl.alternate_bom_designator := '@@@';
2656 l_prev_in_tbl.alternate_routing_designator := '@@@';
2657 l_prev_in_tbl.sale_header_id := -9999;
2658 l_prev_in_tbl.sale_line_id := -9999;
2659 l_prev_in_tbl.kanban_card_id := -9999;
2660 l_prev_in_tbl.lpn_id := -9999;
2661 l_prev_in_tbl.wip_entity_id := -9999;
2662 l_prev_in_tbl.lot_controL_code := -9999;
2663 l_prev_in_tbl.serial_number_control_code := -9999;
2664
2665 l_prev_sub := '####';
2666
2667 FOR i in 1..l_in_tbl.COUNT LOOP
2668 l_content_item_data := '';
2669 IF (l_debug = 1) THEN
2670 trace(' New Flow Content label : in_tbl.COUNT = ' || l_in_tbl.COUNT);
2671 trace(' l_inventory_item_id='|| l_in_tbl(i).inventory_item_id ||
2672 ' l_organization_id='|| l_in_tbl(i).organization_id||
2673 ' l_subinventory_code='||l_in_tbl(i).subinventory_code||
2674 ' l_locator_id ='||l_in_tbl(i).locator_id);
2675 trace(' l_lot_number ='||l_in_tbl(i).lot_number||
2676 ' l_serial_number ='||l_in_tbl(i).serial_number ||
2677 ' l_serial_number_start ='|| l_in_tbl(i).serial_number_start||
2678 ' l_serial_number_end ='||l_in_tbl(i).serial_number_end);
2679 trace(' l_uom ='||l_in_tbl(i).uom||
2680 ' l_revision ='||l_in_tbl(i).revision||
2681 ' l_sale_header_id ='||l_in_tbl(i).sale_header_id||
2682 ' l_sale_line_id ='||l_in_tbl(i).sale_line_id);
2683 trace(' l_kanban_card_id ='||l_in_tbl(i).kanban_card_id ||
2684 ' l_lpn_id =' ||l_in_tbl(i).lpn_id||
2685 ' l_wip_entity_id=' || l_in_tbl(i).wip_entity_id||
2686 ' l_schedule_number='|| l_in_tbl(i).schedule_number);
2687 trace(' l_lot_control_code ='||l_in_tbl(i).lot_control_code||
2688 ' l_serial_number_control_code ='|| l_in_tbl(i).serial_number_control_code||
2689 ' l_transaction_id ='|| l_in_tbl(i).transaction_id);
2690 END IF;
2691 -- Added for bug13781065 Start
2692 l_subinventory_code := l_in_tbl(i).subinventory_code;
2693 l_locator_id := l_in_tbl(i).locator_id;
2694 l_organization_id := l_in_tbl(i).organization_id;
2695 -- End bug13781065
2696 l_lpn_id := l_in_tbl(i).lpn_id; -- Added for bug 14299696: Populate lpn_id into WMS_LABEL_REQUESTS then Custom fields of flow contentd label can use it.
2697
2698 IF (l_in_tbl(i).inventory_item_id) <> (l_prev_in_tbl.inventory_item_id) THEN
2699 get_data(l_out_tbl, l_in_tbl(i).wip_entity_id,
2700 l_in_tbl(i).schedule_number,
2701 l_in_tbl(i).inventory_item_id,
2702 l_in_tbl(i).organization_id,
2703 l_in_tbl(i).subinventory_code,
2704 l_in_tbl(i).locator_id);
2705
2706 get_data_bom_bill_header(l_out_tbl,
2707 l_in_tbl(i).inventory_item_id,
2708 l_in_tbl(i).organization_id,
2709 l_in_tbl(i).alternate_bom_designator);
2710
2711 get_data_bom_routing(l_out_tbl,
2712 l_in_tbl(i).inventory_item_id,
2713 l_in_tbl(i).organization_id,
2714 l_in_tbl(i).alternate_routing_designator);
2715 END IF;
2716
2717
2718 IF (l_in_tbl(i).kanban_card_id IS NOT NULL) AND
2719 (l_in_tbl(i).kanban_card_id <> l_prev_in_tbl.kanban_card_id) THEN
2720 get_data_kanban(l_out_tbl, l_in_tbl(i).kanban_card_id);
2721
2722 END IF;
2723
2724 IF (l_in_tbl(i).lpn_id IS NOT NULL) AND
2725 (l_in_tbl(i).lpn_id <> l_prev_in_tbl.lpn_id) THEN
2726 get_data_LPN(l_out_tbl, l_in_tbl(i).lpn_id,
2727 l_in_tbl(i).revision,
2728 l_in_tbl(i).lot_number,
2729 l_in_tbl(i).serial_number,
2730 l_in_tbl(i).inventory_item_id
2731 );
2732 END IF;
2733
2734 IF (l_in_tbl(i).lot_number IS NOT NULL) AND
2735 (l_in_tbl(i).lot_number <> l_prev_in_tbl.lot_number) THEN
2736 get_data_lot(l_out_tbl, l_in_tbl(i).lot_number);
2737 END IF;
2738
2739
2740 IF (l_in_tbl(i).serial_number IS NOT NULL) AND
2741 (l_in_tbl(i).serial_number <> l_prev_in_tbl.serial_number) THEN
2742 get_data_serial(l_out_tbl,
2743 l_in_tbl(i).inventory_item_id,
2744 l_in_tbl(i).serial_number);
2745 END IF;
2746
2747
2748 IF ((l_in_tbl(i).sale_header_id IS NOT NULL) AND
2749 (l_in_tbl(i).sale_header_id <> l_prev_in_tbl.sale_header_id)) OR
2750 ((l_in_tbl(i).sale_line_id IS NOT NULL) AND
2751 (l_in_tbl(i).sale_line_id <> l_prev_in_tbl.sale_line_id)) THEN
2752 get_data_sale_header(l_out_tbl, l_in_tbl(i).sale_header_id,
2753 l_in_tbl(i).sale_line_id);
2754 END IF;
2755
2756
2757 -- =====================================
2758 -- Save the current input record
2759 -- =====================================
2760 l_prev_in_tbl := l_in_tbl(i);
2761
2762 IF (l_debug = 1) THEN
2763 trace(' ^^^^^^^^^^^^^^^^^New LAbel^^^^^^^^^^^^^^^^^');
2764
2765
2766 --R12 : RFID compliance project
2767 --Calling rules engine before calling to get printer
2768
2769 --IF (l_debug = 1) THEN -- Committed this line to fix bug 14204271
2770 trace('Apply Rules engine for format, printer=' || l_printer ||',manual_format_id='||p_label_type_info.manual_format_id ||',manual_format_name='||p_label_type_info.manual_format_name);
2771 END IF;
2772
2773
2774 INV_LABEL.get_format_with_rule
2775 (
2776 p_document_id => p_label_type_info.label_type_id,
2777 P_LABEL_FORMAT_ID => p_label_type_info.manual_format_id,
2778 p_organization_id => l_in_tbl(i).organization_id,
2779 p_inventory_item_id => l_in_tbl(i).inventory_item_id,
2780 P_LAST_UPDATE_DATE => sysdate,
2781 P_LAST_UPDATED_BY => FND_GLOBAL.user_id,
2782 P_CREATION_DATE => sysdate,
2783 P_CREATED_BY => FND_GLOBAL.user_id,
2784 --P_PRINTER_NAME => l_printer,-- Removed in R12: 4396558
2785 P_SUBINVENTORY_CODE => l_subinventory_code, --Added for bug13781065 Start
2786 P_LOCATOR_ID => l_locator_id, --End bug13781065
2787 P_LPN_ID => l_lpn_id,-- Added for bug 14299696: Populate lpn_id into WMS_LABEL_REQUESTS then Custom fields of flow contentd label can use it.
2788 P_BUSINESS_FLOW_CODE => p_label_type_info.business_flow_code,
2789 x_return_status => l_return_status,
2790 x_label_format_id => l_label_format_id,
2791 x_label_format => l_label_format,
2792 x_label_request_id => l_label_request_id);
2793
2794 IF l_return_status <> 'S' THEN
2795 FND_MESSAGE.SET_NAME('WMS', 'WMS_LABL_RULE_ENGINE_FAILED');
2796 FND_MSG_PUB.ADD;
2797 l_label_format:= p_label_type_info.default_format_id;
2798 l_label_format_id:= p_label_type_info.default_format_name;
2799 END IF;
2800
2801 IF (l_debug = 1) THEN
2802 trace('did apply label ' || l_label_format || ',' || l_label_format_id||',req_id '||l_label_request_id);
2803 END IF;
2804
2805
2806 IF (l_debug = 1) THEN -- Added to fix bug 14204271
2807 trace(' Getting printer, manual_printer='||p_label_type_info.manual_printer ||',sub='||l_subinventory_code ||',default printer='||p_label_type_info.default_printer);
2808 END IF;
2809
2810 IF p_label_type_info.manual_printer IS NULL THEN
2811 -- The p_label_type_info.manual_printer is the one passed from the manual page.
2812 -- As per the design, if a printer is passed from the manual page, then we use that printer irrespective.
2813 IF (l_subinventory_code IS NOT NULL) AND (l_subinventory_code <> l_prev_sub)THEN
2814 IF (l_debug = 1) THEN
2815 trace('getting printer with sub '||l_subinventory_code);
2816 END IF;
2817 BEGIN
2818 WSH_REPORT_PRINTERS_PVT.get_printer
2819 (
2820 p_concurrent_program_id=>p_label_type_info.label_type_id,
2821 p_user_id =>fnd_global.user_id,
2822 p_responsibility_id =>fnd_global.resp_id,
2823 p_application_id =>fnd_global.resp_appl_id,
2824 p_organization_id =>l_organization_id,
2825 p_zone =>l_subinventory_code,
2826 p_format_id =>l_label_format_id, --added in r12 RFID 4396558
2827 x_printer =>l_printer,
2828 x_api_status =>l_api_status,
2829 x_error_message =>l_error_message);
2830 IF l_api_status <> 'S' THEN
2831 IF (l_debug = 1) THEN
2832 trace('Error in calling get_printer, set printer as default printer, err_msg:'||l_error_message);
2833 END IF;
2834 l_printer := p_label_type_info.default_printer;
2835 END IF;
2836 EXCEPTION
2837 WHEN others THEN
2838 l_printer := p_label_type_info.default_printer;
2839 END;
2840 l_prev_sub := l_subinventory_code;
2841 END IF;
2842 ELSE
2843 IF (l_debug = 1) THEN
2844 trace('Set printer as Manual Printer passed in:' || p_label_type_info.manual_printer );
2845 END IF;
2846 l_printer := p_label_type_info.manual_printer;
2847 END IF;
2848
2849
2850
2851 IF p_label_type_info.manual_format_id IS NOT NULL THEN
2852 l_label_format_id := p_label_type_info.manual_format_id;
2853 l_label_format := p_label_type_info.manual_format_name;
2854 IF (l_debug = 1) THEN
2855 trace('Manual format passed in:'||l_label_format_id||','||l_label_format);
2856 END IF;
2857 END IF;
2858 IF (l_label_format_id IS NOT NULL) THEN
2859 -- Derive the fields for the format either passed in or derived via the rules engine.
2860 IF l_label_format_id <> nvl(l_prev_label_format_id, -999) THEN
2861 IF (l_debug = 1) THEN
2862 trace(' Getting variables for new format ' || l_label_format);
2863 END IF;
2864 INV_LABEL.GET_VARIABLES_FOR_FORMAT(
2865 x_variables => l_selected_fields
2866 , x_variables_count => l_selected_fields_count
2867 , p_format_id => l_label_format_id);
2868
2869 l_prev_label_format_id := l_label_format_id;
2870
2871 IF (l_selected_fields_count=0) OR (l_selected_fields.count =0 ) THEN
2872 IF (l_debug = 1) THEN
2873 trace('no fields defined for this format: ' || l_label_format|| ',' ||l_label_format_id);
2874 END IF;
2875 GOTO NextLabel;
2876 END IF;
2877 IF (l_debug = 1) THEN
2878 trace(' Found selected_fields for format ' || l_label_format ||', num='|| l_selected_fields_count);
2879 END IF;
2880 END IF;
2881 ELSE
2882 IF (l_debug = 1) THEN
2883 trace('No format exists for this label, goto nextlabel');
2884 END IF;
2885 GOTO NextLabel;
2886 END IF;
2887
2888 /* variable header */
2889 l_content_item_data := l_content_item_data || LABEL_B;
2890 IF l_label_format <> nvl(p_label_type_info.default_format_name, '@@@') THEN
2891 l_content_item_data := l_content_item_data || ' _FORMAT="' || nvl(p_label_type_info.manual_format_name, l_label_format) || '"';
2892 END IF;
2893 IF (l_printer IS NOT NULL) AND (l_printer <> nvl(p_label_type_info.default_printer,'###')) THEN
2894 l_content_item_data := l_content_item_data || ' _PRINTERNAME="'||l_printer||'"';
2895 END IF;
2896
2897 l_content_item_data := l_content_item_data || TAG_E;
2898 IF (l_debug = 1) THEN
2899 trace('Starting assign variables, ');
2900 END IF;
2901
2902 /* Modified for Bug 4072474 -start*/
2903 l_custom_sql_ret_status := FND_API.G_RET_STS_SUCCESS;
2904 /* Modified for Bug 4072474 -End*/
2905
2906 -- Fix for bug: 4179593 Start
2907 l_CustSqlWarnFlagSet := FALSE;
2908 l_CustSqlErrFlagSet := FALSE;
2909 l_CustSqlWarnMsg := NULL;
2910 l_CustSqlErrMsg := NULL;
2911 -- Fix for bug: 4179593 End
2912
2913 -- Loop for each selected fields, find the columns and write into the XML_content
2914 FOR i IN 1..l_selected_fields.count LOOP
2915 l_field_id := l_selected_fields(i).label_field_id;
2916 -- trace(' -- In selected_fields loop , column_name ='||
2917 -- l_selected_fields(i).column_name);
2918
2919 ---------------------------------------------------------------------------------------------
2920 -- Project: 'Custom Labels' (A 11i10+ Project) |
2921 -- Author: Dinesh ([email protected]) |
2922 -- Change Description: |
2923 -- The check (SQL_STMT <> NULL and COLUMN_NAME = NULL) implies that the field is a |
2924 -- Custom SQL based field. Handle it appropriately. |
2925 ---------------------------------------------------------------------------------------------
2926 IF (l_selected_fields(i).SQL_STMT IS NOT NULL AND l_selected_fields(i).column_name = 'sql_stmt') THEN
2927 IF (l_debug = 1) THEN
2928 trace('Custom Labels Trace [INVLA10B.pls]: ------------------------- REPORT BEGIN-------------------------------------');
2929 trace('Custom Labels Trace [INVLA10B.pls]: LABEL_FIELD_ID : ' || l_selected_fields(i).label_field_id);
2930 trace('Custom Labels Trace [INVLA10B.pls]: FIELD_VARIABLE_NAME : ' || l_selected_fields(i).variable_name);
2931 trace('Custom Labels Trace [INVLA10B.pls]: COLUMN_NAME : ' || l_selected_fields(i).column_name);
2932 trace('Custom Labels Trace [INVLA10B.pls]: SQL_STMT : ' || l_selected_fields(i).sql_stmt);
2933 END IF;
2934 l_sql_stmt := l_selected_fields(i).sql_stmt;
2935 IF (l_debug = 1) THEN
2936 trace('Custom Labels Trace [INVLA10B.pls]: l_sql_stmt BEFORE REQUEST_ID Filter Concatenation: ' || l_sql_stmt);
2937 END IF;
2938 l_sql_stmt := l_sql_stmt || ' AND WLR.LABEL_REQUEST_ID = :REQUEST_ID';
2939 IF (l_debug = 1) THEN
2940 trace('Custom Labels Trace [INVLA10B.pls]: l_sql_stmt AFTER REQUEST_ID Filter Concatenation: ' || l_sql_stmt);
2941 END IF;
2942 BEGIN
2943 IF (l_debug = 1) THEN
2944 trace('Custom Labels Trace [INVLA10B.pls]: At Breadcrumb 1');
2945 trace('Custom Labels Trace [INVLA10B.pls]: LABEL_REQUEST_ID : ' || l_label_request_id);
2946 END IF;
2947 OPEN c_sql_stmt FOR l_sql_stmt using l_label_request_id;
2948 LOOP
2949 FETCH c_sql_stmt INTO l_sql_stmt_result;
2950 EXIT WHEN c_sql_stmt%notfound OR c_sql_stmt%rowcount >=2;
2951 END LOOP;
2952
2953 IF (c_sql_stmt%rowcount=1 AND l_sql_stmt_result IS NULL) THEN
2954 x_return_status := FND_API.G_RET_STS_SUCCESS;
2955 l_custom_sql_ret_status := INV_LABEL.G_WARNING;
2956 fnd_message.set_name('WMS','WMS_CS_NULL_VALUE_RETURNED');
2957 fnd_msg_pub.ADD;
2958 -- Fix for bug: 4179593 Start
2959 --fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => l_custom_sql_ret_msg);
2960 l_custom_sql_ret_msg := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_last, p_encoded => fnd_api.g_false);
2961 l_CustSqlWarnMsg := l_custom_sql_ret_msg;
2962 l_CustSqlWarnFlagSet := TRUE;
2963 -- Fix for bug: 4179593 End
2964
2965 IF (l_debug = 1) THEN
2966 trace('Custom Labels Trace [INVLA10B.pls]: At Breadcrumb 2');
2967 trace('Custom Labels Trace [INVLA10B.pls]: l_sql_stmt_result is: ' || l_sql_stmt_result);
2968 trace('Custom Labels Trace [INVLA10B.pls]: WARNING: NULL value returned by the custom SQL Query.');
2969 trace('Custom Labels Trace [INVLA10B.pls]: l_custom_sql_ret_status is set to : ' || l_custom_sql_ret_status );
2970 END IF;
2971 ELSIF c_sql_stmt%rowcount=0 THEN
2972 IF (l_debug = 1) THEN
2973 trace('Custom Labels Trace [INVLA10B.pls]: At Breadcrumb 3');
2974 trace('Custom Labels Trace [INVLA10B.pls]: WARNING: No row returned by the Custom SQL query');
2975 END IF;
2976 x_return_status := FND_API.G_RET_STS_SUCCESS;
2977 l_custom_sql_ret_status := INV_LABEL.G_WARNING;
2978 fnd_message.set_name('WMS','WMS_CS_NO_DATA_FOUND');
2979 fnd_msg_pub.ADD;
2980 /* Replaced following statement for Bug 4207625: Anupam Jain*/
2981 /*fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => l_custom_sql_ret_status);*/
2982 -- Fix for bug: 4179593 Start
2983 --fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => l_custom_sql_ret_msg);
2984 l_custom_sql_ret_msg := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_last, p_encoded => fnd_api.g_false);
2985 l_CustSqlWarnMsg := l_custom_sql_ret_msg;
2986 l_CustSqlWarnFlagSet := TRUE;
2987 -- Fix for bug: 4179593 End
2988 ELSIF c_sql_stmt%rowcount>=2 THEN
2989 IF (l_debug = 1) THEN
2990 trace('Custom Labels Trace [INVLA10B.pls]: At Breadcrumb 4');
2991 trace('Custom Labels Trace [INVLA10B.pls]: ERROR: Multiple values returned by the Custom SQL query');
2992 END IF;
2993 l_sql_stmt_result := NULL;
2994 x_return_status := FND_API.G_RET_STS_SUCCESS;
2995 l_custom_sql_ret_status := FND_API.G_RET_STS_ERROR;
2996 fnd_message.set_name('WMS','WMS_CS_MULTIPLE_VALUES_RETURN');
2997 fnd_msg_pub.ADD;
2998 /* Replaced following statement for Bug 4207625: Anupam Jain*/
2999 /*fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => l_custom_sql_ret_status);*/
3000 -- Fix for bug: 4179593 Start
3001 --fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => l_custom_sql_ret_msg);
3002 l_custom_sql_ret_msg := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_last, p_encoded => fnd_api.g_false);
3003 l_CustSqlErrMsg := l_custom_sql_ret_msg;
3004 l_CustSqlErrFlagSet := TRUE;
3005 -- Fix for bug: 4179593 End
3006 END IF;
3007 IF (c_sql_stmt%ISOPEN) THEN
3008 CLOSE c_sql_stmt;
3009 END IF;
3010 EXCEPTION
3011 WHEN OTHERS THEN
3012 IF (c_sql_stmt%ISOPEN) THEN
3013 CLOSE c_sql_stmt;
3014 END IF;
3015 IF (l_debug = 1) THEN
3016 trace('Custom Labels Trace [INVLA10B.pls]: At Breadcrumb 5');
3017 trace('Custom Labels Trace [INVLA10B.pls]: Unexpected Error has occured in GET_VARIABLES_DATA');
3018 END IF;
3019 x_return_status := FND_API.G_RET_STS_ERROR;
3020 fnd_message.set_name('WMS','WMS_CS_WRONG_SQL_CONSTRUCT');
3021 fnd_msg_pub.ADD;
3022 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
3023 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3024 END;
3025 IF (l_debug = 1) THEN
3026 trace('Custom Labels Trace [INVLA10B.pls]: At Breadcrumb 6');
3027 trace('Custom Labels Trace [INVLA10B.pls]: Before assigning it to l_content_item_data');
3028 END IF;
3029 l_content_item_data := l_content_item_data
3030 || variable_b
3031 || l_selected_fields(i).variable_name
3032 || '">'
3033 || l_sql_stmt_result
3034 || variable_e;
3035 l_sql_stmt_result := NULL;
3036 l_sql_stmt := NULL;
3037 IF (l_debug = 1) THEN
3038 trace('Custom Labels Trace [INVLA10B.pls]: At Breadcrumb 7');
3039 trace('Custom Labels Trace [INVLA10B.pls]: After assigning it to l_content_item_data');
3040 trace('Custom Labels Trace [INVLA10B.pls]: --------------------------REPORT END-------------------------------------');
3041 END IF;
3042 ------------------------End of this change for Custom Labels project code--------------------
3043 ELSIF LOWER(l_selected_fields(i).column_name) = 'current_date' THEN
3044 l_content_item_data := l_content_item_data || VARIABLE_B ||
3045 l_selected_fields(i).variable_name ||
3046 '">' || INV_LABEL.G_DATE || VARIABLE_E;
3047 ELSIF LOWER(l_selected_fields(i).column_name) = 'current_time' THEN
3048 l_content_item_data := l_content_item_data || VARIABLE_B ||
3049 l_selected_fields(i).variable_name ||
3050 '">' || INV_LABEL.G_TIME || VARIABLE_E;
3051 ELSIF LOWER(l_selected_fields(i).column_name) = 'request_user' THEN
3052 l_content_item_data := l_content_item_data || VARIABLE_B ||
3053 l_selected_fields(i).variable_name ||
3054 '">' || INV_LABEL.G_USER || VARIABLE_E;
3055 ELSE
3056 l_field_value := '';
3057 --trace(' Finished writing variables ');
3058 IF (l_out_tbl.EXISTS(l_field_id) ) THEN
3059 l_field_value := l_out_tbl(l_field_id).datbuf;
3060 END IF;
3061
3062 l_content_item_data := l_content_item_data || VARIABLE_B ||
3063 l_selected_fields(i).variable_name || '">' ||
3064 l_field_value ||
3065 VARIABLE_E;
3066 END IF;
3067
3068 --trace(' Finished writing variables ');
3069 END LOOP;
3070 l_content_item_data := l_content_item_data || LABEL_E;
3071 x_variable_content(l_label_index).label_content := l_content_item_data ;
3072 x_variable_content(l_label_index).label_request_id := l_label_request_id;
3073
3074 ------------------------Start of changes for Custom Labels project code------------------
3075
3076 -- Fix for bug: 4179593 Start
3077 IF (l_CustSqlWarnFlagSet) THEN
3078 l_custom_sql_ret_status := INV_LABEL.G_WARNING;
3079 l_custom_sql_ret_msg := l_CustSqlWarnMsg;
3080 END IF;
3081
3082 IF (l_CustSqlErrFlagSet) THEN
3083 l_custom_sql_ret_status := FND_API.G_RET_STS_ERROR;
3084 l_custom_sql_ret_msg := l_CustSqlErrMsg;
3085 END IF;
3086 -- Fix for bug: 4179593 End
3087
3088 x_variable_content(l_label_index).label_status := l_custom_sql_ret_status ;
3089 x_variable_content(l_label_index).error_message := l_custom_sql_ret_msg;
3090 ------------------------End of this changes for Custom Labels project code---------------
3091
3092 IF (l_debug = 1) THEN
3093 trace('LENGTH : ' || length(x_variable_content(l_label_index).label_content));
3094 END IF;
3095 l_label_index := l_label_index + 1;
3096 <<NextLabel>>
3097 l_content_item_data := '';
3098 l_label_request_id := null;
3099
3100 ------------------------Start of changes for Custom Labels project code------------------
3101 l_custom_sql_ret_status := NULL;
3102 l_custom_sql_ret_msg := NULL;
3103 ------------------------End of this changes for Custom Labels project code---------------
3104
3105 END LOOP;
3106
3107 EXCEPTION
3108 WHEN FND_API.G_EXC_ERROR THEN
3109 x_return_status := FND_API.G_RET_STS_ERROR;
3110
3111 WHEN SERIAL_EXCEPTION THEN
3112 x_return_status := FND_API.G_RET_STS_ERROR;
3113
3114 WHEN NO_FLOW_DATA_FOUND_X THEN
3115 x_return_status := FND_API.G_RET_STS_ERROR;
3116
3117 WHEN NO_LABEL_FORMAT_FOUND_X THEN
3118 FND_MESSAGE.SET_NAME('WMS', 'WMS_LABL_NO_LABEL_CREATED');
3119 FND_MSG_PUB.ADD;
3120 x_return_status := FND_API.G_RET_STS_ERROR;
3121
3122
3123
3124 END get_variable_data;
3125
3126 END INV_LABEL_PVT10;