1 PACKAGE BODY WMS_PACKING_WORKBENCH_PVT AS
2 /* $Header: WMSPACVB.pls 120.18.12020000.4 2012/08/09 06:23:00 zzyan ship $ */
3
4 -- Global constant holding the package name
5 g_pkg_name CONSTANT VARCHAR2(30) := 'WMS_PACKING_WORKBENCH_PVT';
6 g_pkg_version CONSTANT VARCHAR2(100) := '$Header: WMSPACVB.pls 120.18.12020000.4 2012/08/09 06:23:00 zzyan ship $';
7
8 -- Lot Serial Attributes
9 g_lot_ser_attr INV_LOT_SEL_ATTR.lot_sel_attributes_tbl_type;
10 G_DATE_MASK VARCHAR2(100) := 'YYYY/MM/DD';
11
12 g_kit_count_current_comp NUMBER :=0 ;
13
14 /*==========================
15 Private Procedure
16 =========================*/
17 PROCEDURE trace(p_message IN VARCHAR2,
18 p_level IN NUMBER DEFAULT 1 ) IS
19 BEGIN
20 INV_LOG_UTIL.trace(p_message, 'PackingWB', p_level);
21 END trace;
22
23
24 /********************************************
25 Procedure to insert into WMS_PACKING_MATERIAL_GTEMP
26 *******************************************/
27 PROCEDURE insert_material_rec(
28 p_material_rec IN WMS_PACKING_MATERIAL_GTEMP%ROWTYPE) IS
29
30 BEGIN
31 INSERT INTO WMS_PACKING_MATERIAL_GTEMP(
32 MOVE_ORDER_HEADER_ID
33 , MOVE_ORDER_LINE_ID
34 , REFERENCE
35 , REFERENCE_ID
36 , TXN_SOURCE_ID
37 , DELIVERY_DETAIL_ID
38 , ORGANIZATION_ID
39 , ORGANIZATION_CODE
40 , SUBINVENTORY
41 , LOCATOR_ID
42 , LOCATOR
43 , PROJECT_ID
44 , PROJECT
45 , TASK_ID
46 , TASK_NUMBER
47 , TASK_NAME
48 , INVENTORY_ITEM_ID
49 , ITEM
50 , ITEM_DESCRIPTION
51 , LPN_ID
52 , LPN
53 , PARENT_LPN_ID
54 , PARENT_LPN
55 , OUTERMOST_LPN_ID
56 , OUTERMOST_LPN
57 , REVISION
58 , UOM
59 , LOT_NUMBER
60 , QUANTITY
61 , DELIVERY_ID
62 , DELIVERY
63 , DELIVERY_COMPLETED
64 , TRIP_ID
65 , TRIP
66 , CARRIER_ID
67 , CARRIER
68 , ORDER_HEADER_ID
69 , ORDER_NUMBER
70 , ORDER_LINE_ID
71 , ORDER_LINE_NUM
72 , PACKING_INSTRUCTION
73 , CUSTOMER_ID
74 , CUSTOMER_NUMBER
75 , CUSTOMER_NAME
76 , SHIP_TO_LOCATION_ID
77 , SHIP_TO_LOCATION
78 , RECEIPT_NUM
79 , DOCUMENT_TYPE
80 , DOCUMENT_ID
81 , DOCUMENT_NUMBER
82 , DOCUMENT_LINE_ID
83 , DOCUMENT_LINE_NUM
84 , VENDOR_ID
85 , SOURCE_ORG_ID
86 , TRADING_PARTNER
87 , RECEIVING_LOCATION_ID
88 , RECEIVING_LOCATION
89 , PTO_FLAG
90 , SELECTED_FLAG
91 , SHIP_SET_ID
92 , SHIP_SET
93 --INVCONV KKILLAMS
94 , SECONDARY_UOM_CODE
95 , SECONDARY_QUANTITY
96 , GRADE_CODE
97 --INVCONV KKILLAMS
98 )
99 VALUES(
100 p_material_rec.MOVE_ORDER_HEADER_ID
101 , p_material_rec.MOVE_ORDER_LINE_ID
102 , p_material_rec.REFERENCE
103 , p_material_rec.REFERENCE_ID
104 , p_material_rec.TXN_SOURCE_ID
105 , p_material_rec.DELIVERY_DETAIL_ID
106 , p_material_rec.ORGANIZATION_ID
107 , p_material_rec.ORGANIZATION_CODE
108 , p_material_rec.SUBINVENTORY
109 , p_material_rec.LOCATOR_ID
110 , p_material_rec.LOCATOR
111 , p_material_rec.PROJECT_ID
112 , p_material_rec.PROJECT
113 , p_material_rec.TASK_ID
114 , p_material_rec.TASK_NUMBER
115 , p_material_rec.TASK_NAME
116 , p_material_rec.INVENTORY_ITEM_ID
117 , p_material_rec.ITEM
118 , p_material_rec.ITEM_DESCRIPTION
119 , p_material_rec.LPN_ID
120 , p_material_rec.LPN
121 , p_material_rec.PARENT_LPN_ID
122 , p_material_rec.PARENT_LPN
123 , p_material_rec.OUTERMOST_LPN_ID
124 , p_material_rec.OUTERMOST_LPN
125 , p_material_rec.REVISION
126 , p_material_rec.UOM
127 , p_material_rec.LOT_NUMBER
128 , p_material_rec.QUANTITY
129 , p_material_rec.DELIVERY_ID
130 , p_material_rec.DELIVERY
131 , p_material_rec.DELIVERY_COMPLETED
132 , p_material_rec.TRIP_ID
133 , p_material_rec.TRIP
134 , p_material_rec.CARRIER_ID
135 , p_material_rec.CARRIER
136 , p_material_rec.ORDER_HEADER_ID
137 , p_material_rec.ORDER_NUMBER
138 , p_material_rec.ORDER_LINE_ID
139 , p_material_rec.ORDER_LINE_NUM
140 , p_material_rec.PACKING_INSTRUCTION
141 , p_material_rec.CUSTOMER_ID
142 , p_material_rec.CUSTOMER_NUMBER
143 , p_material_rec.CUSTOMER_NAME
144 , p_material_rec.SHIP_TO_LOCATION_ID
145 , p_material_rec.SHIP_TO_LOCATION
146 , p_material_rec.RECEIPT_NUM
147 , p_material_rec.DOCUMENT_TYPE
148 , p_material_rec.DOCUMENT_ID
149 , p_material_rec.DOCUMENT_NUMBER
150 , p_material_rec.DOCUMENT_LINE_ID
151 , p_material_rec.DOCUMENT_LINE_NUM
152 , p_material_rec.VENDOR_ID
153 , p_material_rec.SOURCE_ORG_ID
154 , p_material_rec.TRADING_PARTNER
155 , p_material_rec.RECEIVING_LOCATION_ID
156 , p_material_rec.RECEIVING_LOCATION
157 , p_material_rec.PTO_FLAG
158 , nvl(p_material_rec.SELECTED_FLAG,'N')
159 , p_material_rec.SHIP_SET_ID
160 , p_material_rec.SHIP_SET
161 --INVCONV kkillams
162 , p_material_rec.SECONDARY_UOM_CODE
163 , p_material_rec.SECONDARY_QUANTITY
164 , p_material_rec.GRADE_CODE
165 --INVCONV kkillams
166 );
167
168 EXCEPTION
169 WHEN others THEN
170 IF l_debug = 1 THEN
171 trace('Error in insert_material_rec()');
172 trace('ERROR CODE = ' || SQLCODE);
173 trace('ERROR MESSAGE = ' || SQLERRM);
174 END IF;
175 END insert_material_rec;
176
177 PROCEDURE check_against_rcv
178 ( p_mol_rec IN WMS_PACKING_MATERIAL_GTEMP%ROWTYPE
179 , p_document_type IN VARCHAR2 DEFAULT NULL
180 , p_document_id IN NUMBER DEFAULT NULL
181 , p_document_line_id IN NUMBER DEFAULT NULL
182 , p_receipt_num IN VARCHAR2 DEFAULT NULL
183 , p_partner_id IN NUMBER DEFAULT NULL
184 , p_partner_type IN NUMBER DEFAULT NULL
185 , p_rcv_location_id IN NUMBER DEFAULT NULL
186 , x_valid OUT nocopy VARCHAR2
187 , x_unique OUT nocopy VARCHAR2
188 , x_receipt_num OUT nocopy varchar2
189 , x_rcv_location_id OUT nocopy NUMBER
190 , x_vendor_id OUT nocopy NUMBER
191 , x_from_org_id OUT nocopy NUMBER
192 --14274513
193 , x_quantity OUT nocopy NUMBER
194 , x_secondary_quantity OUT nocopy NUMBER
195 ) IS
196 l_cursor NUMBER;
197 l_last_error_pos NUMBER;
198 l_temp_str VARCHAR2(100);
199 l_query_sql VARCHAR2(10000);
200 l_select_str VARCHAR2(2000);
201 l_from_str VARCHAR2(2000);
202 l_where_str VARCHAR2(2000);
203
204 l_receipt_num VARCHAR2(30);
205 l_location_id NUMBER;
206 l_vendor_id NUMBER;
207 l_from_organization_id NUMBER;
208
209 l_document_unique NUMBER;
210 l_prev_location_id NUMBER;
211 l_location_exists NUMBER;
212 l_location_unique NUMBER;
213 l_prev_receipt VARCHAR2(30);
214 l_receipt_exists NUMBER;
215 l_receipt_unique NUMBER;
216 l_prev_partner_id NUMBER;
217 l_partner_exists NUMBER;
218 l_partner_unique NUMBER;
219 l_rcv_transaction_id NUMBER;
220
221 --14274513
222 l_receipt_qty NUMBER;
223 l_receipt_secondary_qty NUMBER;
224 l_uom VARCHAR2(30);
225
226 p_n NUMBER;
227 p_v VARCHAR2(256);
228
229 l_return NUMBER;
230
231 l_progress VARCHAR2(10);
232 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
233 BEGIN
234 --14274513
235 x_quantity := 0;
236 x_secondary_quantity := 0;
237 IF (l_debug = 1) THEN
238 trace('Entering check_against_rcv...');
239 trace(' p_document_type => '||p_document_type);
240 trace(' p_mol_rec.reference => '||p_mol_rec.reference );
241 trace(' p_document_id => '||p_document_id);
242 trace(' p_mol_rec.reference_id => '||p_mol_rec.reference_id);
243 END IF;
244
245 x_valid := 'N';
246 x_unique := 'Y';
247
248 IF ((p_mol_rec.reference IS NOT NULL)
249 AND
250 ((p_document_type = 'PO'
251 AND p_mol_rec.reference <>'PO_LINE_LOCATION_ID')
252 OR (p_document_type IN ('ASN','INTSHIP','REQ')
253 AND p_mol_rec.reference <>'SHIPMENT_LINE_ID')
254 OR (p_document_type IN ('ASN','INTSHIP')
255 AND p_mol_rec.reference = 'SHIPMENT_LINE_ID'
256 AND p_document_line_id IS NOT NULL
257 AND p_mol_rec.reference_id <> p_document_line_id)
258 OR (p_document_type = 'RMA'
259 AND p_mol_rec.reference <> 'ORDER_LINE_ID')
260 )) THEN
261 x_valid := 'N';
262 IF (l_debug = 1) THEN
263 trace('Sanity test failed. Skip this record');
264 END IF;
265 RETURN;
266 END IF;
267
268 IF (p_document_type IS NULL AND
269 p_document_id IS NULL AND
270 p_document_line_id IS NULL AND
271 p_receipt_num IS NULL AND
272 p_partner_id IS NULL AND
273 p_partner_type IS NULL AND
274 p_rcv_location_id IS NULL) THEN
275 x_valid := 'Y';
276 IF (l_debug = 1) THEN
277 trace('No futher validation is necessary. This record is OK');
278 END IF;
279 RETURN;
280 END IF;
281
282 l_select_str := 'SELECT DISTINCT';
283 l_select_str := l_select_str || ' rsh.receipt_num,';
284 l_select_str := l_select_str || ' rs.location_id,';
285 l_select_str := l_select_str || ' rsh.vendor_id,';
286 l_select_str := l_select_str || ' rsl.from_organization_id,';
287 l_select_str := l_select_str || ' rs.rcv_transaction_id';
288
289 --14274513
290 l_select_str := l_select_str || ',';
291 l_select_str := l_select_str || ' rs.quantity, ';
292 l_select_str := l_select_str || ' rs.secondary_quantity,';
293 l_select_str := l_select_str || ' rs.unit_of_measure ';
294
295 l_from_str := ' FROM rcv_supply rs,';
296 l_from_str := l_from_str || ' rcv_transactions rt,';
297 l_from_str := l_from_str || ' rcv_shipment_lines rsl,';
298 l_from_str := l_from_str || ' rcv_shipment_headers rsh';
299
300 l_where_str := ' WHERE rs.supply_source_id = rt.transaction_id';
301 l_where_str := l_where_str || ' AND rs.to_organization_id = :org_id';
302 l_where_str := l_where_str || ' AND rs.supply_type_code = ''RECEIVING''';
303
304 l_where_str := l_where_str || ' AND rs.item_id = :inventory_item_id';
305 l_where_str := l_where_str || ' AND nvl(rs.item_revision,''$@$'') = nvl(:revision,nvl(rs.item_revision,''$@$''))';
306 l_where_str := l_where_str || ' AND nvl(rt.project_id, -999) = nvl(:project_id, -999)';
307 l_where_str := l_where_str || ' AND nvl(rt.task_id, -999) = nvl(:task_id, -999)';
308 l_where_str := l_where_str || ' AND rs.shipment_line_id = rsl.shipment_line_id';
309 l_where_str := l_where_str || ' AND rsl.shipment_header_id = rsh.shipment_header_id';
310
311 IF (p_mol_rec.subinventory IS NOT NULL) THEN
312 l_where_str := l_where_str || ' AND nvl(rt.subinventory, ''&*&'') = :subinventory_code';
313 ELSE
314 l_where_str := l_where_str || ' AND nvl(rt.subinventory, ''&*&'') = ''&*&''';
315 END IF;
316
317 IF (p_mol_rec.locator_id IS NOT NULL) THEN
318 l_where_str := l_where_str || ' AND nvl(rt.locator_id, -999) = :locator_id';
319 ELSE
320 l_where_str := l_where_str || ' AND nvl(rt.locator_id, -999) = -999';
321 END IF;
322
323 IF (p_mol_rec.lpn_id IS NOT NULL) THEN
324 l_where_str := l_where_str || ' AND nvl(rs.lpn_id, -999) = :lpn_id';
325 ELSE
326 l_where_str := l_where_str || ' AND nvl(rs.lpn_id, -999) = -999';
327 END IF;
328
329 IF (p_document_type = 'PO' OR p_mol_rec.reference = 'PO_LINE_LOCATION_ID') THEN
330 l_where_str := l_where_str||' AND rs.po_line_id IS NOT NULL';
331
332 IF (p_mol_rec.reference_id IS NOT NULL) THEN
333 l_where_str := l_where_str ||' AND rsl.po_line_location_id = :reference_id';
334 END IF;
335
336 IF (p_document_id IS NOT NULL) THEN
337 l_where_str := l_where_str || ' AND rs.po_header_id = :document_id';
338 IF p_document_line_id IS NOT NULL THEN
339 l_where_str := l_where_str || ' AND rsl.po_line_id = :document_line_id';
340 END IF;
341 END IF;
342
343 --14274513
344 trace('p_rcpt number:' || p_receipt_num);
345 IF (p_receipt_num IS NOT NULL) THEN
346 l_where_str := l_where_str || ' AND rsh.receipt_num = :p_receipt_num ';
347 l_where_str := l_where_str || ' AND rt.UOM_CODE = :p_uom_code ';
348 END IF;
349
350 ELSIF (p_document_type IN ('ASN', 'INTSHIP') OR p_mol_rec.reference = 'SHIPMENT_LINE_ID') THEN
351 IF (p_mol_rec.reference_id IS NOT NULL) THEN
352 l_where_str := l_where_str || ' AND rsl.shipment_line_id = :reference_id';
353 END IF;
354
355 IF (p_document_type = 'ASN') THEN
356 l_where_str := l_where_str || ' AND rsh.asn_type in (''ASN'', ''ASBN'')';
357 ELSE
358 l_where_str := l_where_str || ' AND nvl(rsh.asn_type,''NOT ASN'') not in (''ASN'', ''ASBN'')';
359 l_where_str := l_where_str || ' AND rsh.receipt_source_code IN';
360 l_where_str := l_where_str || ' (''INTERNAL ORDER'',''INVENTORY'')';
361 l_where_str := l_where_str || ' AND rsh.ship_to_org_id = :org_id';
362 END IF;
363
364 IF (p_document_id IS NOT NULL) THEN
365 l_where_str := l_where_str || ' AND rsl.shipment_header_id = :document_id';
366 IF p_document_line_id IS NOT NULL THEN
367 l_where_str := l_where_str || ' AND rsl.shipment_line_id = :document_line_id';
368 END IF;
369 END IF;
370
371 --14274513
372 trace('p_rcpt number:' || p_receipt_num);
373 IF (p_receipt_num IS NOT NULL) THEN
374 l_where_str := l_where_str || ' AND rsh.receipt_num = :p_receipt_num ';
375 l_where_str := l_where_str || ' AND rt.UOM_CODE = :p_uom_code ';
376 END IF;
377
378 ELSIF (p_document_type = 'REQ' OR p_mol_rec.reference = 'SHIPMENT_LINE_ID') THEN
379 IF (p_mol_rec.reference_id IS NOT NULL) THEN
380 l_where_str := l_where_str || ' AND rsl.shipment_line_id = :reference_id';
381 END IF;
382
383 IF (p_document_id IS NOT NULL) THEN
384 l_where_str := l_where_str || ' AND rs.req_header_id = :document_id';
385 IF (p_document_line_id IS NOT NULL) THEN
386 l_where_str := l_where_str || ' AND rs.req_line_id = :document_line_id';
387 END IF;
388 END IF;
389 ELSIF (p_document_type = 'RMA' OR p_mol_rec.reference = 'OE_ORDER_LINE_ID') THEN
390 l_where_str := l_where_str||' AND rs.oe_order_header_id IS NOT NULL';
391
392 IF (p_mol_rec.reference_id IS NOT NULL) THEN
393 l_where_str := l_where_str||' AND rsl.oe_order_line_id = :reference_id';
394 END IF;
395
396 IF p_document_id IS NOT NULL THEN
397 l_where_str := l_where_str||' AND rsl.oe_order_header_id = :document_id';
398 IF p_document_line_id IS NOT NULL THEN
399 l_where_str := l_where_str||' AND rsl.oe_order_line_id = :document_line_id';
400 END IF;
401 END IF;
402 END IF;
403
404 l_query_sql := l_select_str || l_from_str || l_where_str ;
405
406 p_n :=1;
407 WHILE p_n <=length(l_query_sql) LOOP
408 p_v := Substr( l_query_sql,p_n,255);
409 IF (l_debug = 1) THEN
410 trace(p_v);
411 END IF;
412 p_n := p_n +255;
413 END LOOP;
414
415 l_cursor := dbms_sql.open_cursor;
416
417 BEGIN
418 dbms_sql.parse(l_cursor,l_query_sql,dbms_sql.v7);
419 EXCEPTION
420 WHEN OTHERS THEN
421 l_last_error_pos := dbms_sql.last_error_position();
422 l_temp_str := Substr(l_query_sql, l_last_error_pos-5, 50);
423 IF l_debug = 1 THEN
424 trace('Error in parse sql statement, at '||l_temp_str);
425 END IF;
426 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
427 END;
428
429 IF l_debug =1 THEN
430 trace('Binding variables');
431 END IF;
432
433 l_progress := '005';
434 dbms_sql.bind_variable(l_cursor, ':org_id', p_mol_rec.organization_id);
435 l_progress := '010';
436 dbms_sql.bind_variable(l_cursor, ':inventory_item_id', p_mol_rec.inventory_item_id);
437 l_progress := '011';
438 dbms_sql.bind_variable(l_cursor, ':revision', p_mol_rec.revision);
439 l_progress := '012';
440 dbms_sql.bind_variable(l_cursor, ':project_id', p_mol_rec.project_id);
441 l_progress := '013';
442 dbms_sql.bind_variable(l_cursor, ':task_id', p_mol_rec.task_id);
443 l_progress := '014';
444
445 IF p_mol_rec.subinventory IS NOT NULL THEN
446 dbms_sql.bind_variable(l_cursor, ':subinventory_code', p_mol_rec.subinventory);
447 END IF;
448 l_progress := '015';
449 IF p_mol_rec.locator_id IS NOT NULL THEN
450 dbms_sql.bind_variable(l_cursor, ':locator_id', p_mol_rec.locator_id);
451 END IF;
452 l_progress := '016';
453
454 IF p_mol_rec.lpn_id IS NOT NULL THEN
455 dbms_sql.bind_variable(l_cursor, ':lpn_id', p_mol_rec.lpn_id);
456 END IF;
457
458 l_progress := '017';
459 IF p_document_id IS NOT NULL THEN
460 dbms_sql.bind_variable(l_cursor, ':document_id', p_document_id);
461 END IF;
462 l_progress := '018';
463
464 IF p_document_line_id IS NOT NULL THEN
465 dbms_sql.bind_variable(l_cursor, ':document_line_id', p_document_line_id);
466 END IF;
467 l_progress := '019';
468
469 IF p_mol_rec.reference_id IS NOT NULL THEN
470 dbms_sql.bind_variable(l_cursor, ':reference_id',p_mol_rec.reference_id);
471 END IF;
472
473 --14274513
474 IF p_receipt_num IS NOT NULL and p_document_type IN ('PO', 'ASN', 'INTSHIP') THEN
475 dbms_sql.bind_variable(l_cursor, ':p_receipt_num', p_receipt_num);
476 dbms_sql.bind_variable(l_cursor, ':p_uom_code', p_mol_rec.uom);
477 END IF;
478
479 l_progress := '020';
480
481
482 dbms_sql.define_column(l_cursor, 1, l_receipt_num, 30);
483 l_progress := '020.5';
484 dbms_sql.define_column(l_cursor, 2, l_location_id);
485 dbms_sql.define_column(l_cursor, 3, l_vendor_id);
486 dbms_sql.define_column(l_cursor, 4, l_from_organization_id);
487 dbms_sql.define_column(l_cursor, 5, l_rcv_transaction_id);
488
489 --14274513
490 dbms_sql.define_column(l_cursor, 6, l_receipt_qty);
491 dbms_sql.define_column(l_cursor, 7, l_receipt_secondary_qty);
492 dbms_sql.define_column(l_cursor, 8, l_uom, 30);
493
494 l_progress := '021';
495
496 IF (l_debug = 1) THEN
497 trace('Excute query');
498 END IF;
499 l_return := dbms_sql.execute(l_cursor);
500 IF (l_debug = 1) THEN
501 trace('Executed query');
502 END IF;
503 l_progress := '022';
504
505 l_location_exists := 0;
506 l_location_unique := 1;
507 l_receipt_exists := 0;
508 l_receipt_unique := 1;
509 l_partner_exists := 0;
510 l_partner_unique := 1;
511
512 LOOP
513 IF DBMS_SQL.FETCH_ROWS(l_cursor) = 0 THEN
514 EXIT;
515 END IF;
516
517 x_valid := 'Y';
518
519 dbms_sql.column_value(l_cursor, 1, l_receipt_num);
520 dbms_sql.column_value(l_cursor, 2, l_location_id);
521 dbms_sql.column_value(l_cursor, 3, l_vendor_id);
522 dbms_sql.column_value(l_cursor, 4, l_from_organization_id);
523 dbms_sql.column_value(l_cursor, 5, l_rcv_transaction_id);
524
525 --14274513
526 dbms_sql.column_value(l_cursor, 6, l_receipt_qty);
527 dbms_sql.column_value(l_cursor, 7, l_receipt_secondary_qty);
528 dbms_sql.column_value(l_cursor, 8, l_uom);
529
530 IF (l_debug = 1) THEN
531 trace('rcv_transaction_id:'||l_rcv_transaction_id||
532 ' receipt_num:'||l_receipt_num||
533 ' location_id:'||l_location_id||
534 ' vendor_id:'||l_vendor_id||
535 ' from_org_id:'||l_from_organization_id||
536 -- 14274513
537 ' receipt_qty:' || l_receipt_qty ||
538 ' receipt_secondary_qty:' || l_receipt_secondary_qty ||
539 ' uom:' || l_uom);
540 END IF;
541
542 --14274513
543 -- don't need to do a UOM translate here
544 x_quantity := x_quantity + l_receipt_qty;
545 x_secondary_quantity := x_secondary_quantity + l_receipt_secondary_qty;
546
547 -- Doc validations
548 IF ((p_document_type IS NOT NULL AND p_mol_rec.reference IS NULL) OR
549 (p_document_id IS NOT NULL AND p_mol_rec.reference_id IS NULL)) THEN
550 x_unique := 'N';
551 END IF;
552
553 --RCV Location
554 IF p_rcv_location_id IS NOT NULL THEN
555 IF l_location_id = p_rcv_location_id THEN
556 l_location_exists := 1;
557 END IF;
558 END IF;
559
560 IF l_prev_location_id IS NOT NULL THEN
561 IF l_prev_location_id <> l_location_id THEN
562 l_location_unique := 0;
563 END IF;
564 ELSE
565 l_location_unique := 1;
566 l_prev_location_id := l_location_id;
567 END IF;
568
569 --Receipt
570 IF p_receipt_num IS NOT NULL THEN
571 IF l_receipt_num = p_receipt_num THEN
572 l_receipt_exists := 1;
573 END IF;
574 END IF;
575
576 IF l_prev_receipt IS NOT NULL THEN
577 IF l_prev_receipt <> l_receipt_num THEN
578 l_receipt_unique := 0;
579 END IF;
580 ELSE
581 l_receipt_unique := 1;
582 l_prev_receipt := l_receipt_num;
583 END IF;
584
585 --Parnter
586 IF p_partner_type IS NOT NULL THEN
587 IF p_partner_type = 1 THEN
588 IF p_partner_id = l_vendor_id THEN
589 l_partner_exists := 1;
590 END IF;
591 ELSIF p_partner_type = 2 THEN
592 IF p_partner_id = l_from_organization_id THEN
593 l_partner_exists := 1;
594 END IF;
595 END IF;
596 END IF;
597
598 IF p_partner_type = 1 THEN
599 IF l_prev_partner_id IS NOT NULL THEN
600 IF l_prev_partner_id <> Nvl(l_vendor_id,-1) THEN
601 l_partner_unique := 0;
602 END IF;
603 ELSE
604 l_partner_unique := 1;
605 l_prev_partner_id := l_vendor_id;
606 END IF;
607 ELSIF p_partner_type = 2 THEN
608 IF l_prev_partner_id IS NOT NULL THEN
609 IF l_prev_partner_id <> Nvl(l_from_organization_id,-1) THEN
610 l_partner_unique := 0;
611 END IF;
612 ELSE
613 l_partner_unique := 1;
614 l_prev_partner_id := l_from_organization_id;
615 END IF;
616 END IF;
617
618
619
620 END LOOP;
621
622 dbms_sql.close_cursor(l_cursor);
623
624 IF (l_debug = 1) THEN
625 trace('l_location_exists:'||l_location_exists||
626 ' l_location_unique:'||l_location_unique||
627 ' l_receipt_exists:'||l_receipt_exists||
628 ' l_receipt_unique:'||l_receipt_unique||
629 ' l_partner_exists:'||l_partner_exists||
630 ' l_partner_unique:'||l_partner_unique);
631 END IF;
632
633 --If user has entered location, receipt, partner as query
634 --critieria, then if no results is found for these critieria
635 --then this MOL must be skipped
636 IF ((p_rcv_location_id IS NOT NULL AND l_location_exists <> 1) OR
637 (p_receipt_num IS NOT NULL AND l_receipt_exists <> 1) OR
638 (p_partner_type IS NOT NULL AND l_partner_exists <> 1)) THEN
639 x_valid := 'N';
640 RETURN;
641 END IF;
642
643 IF l_receipt_unique = 1 THEN
644 --If this MOL corresponds to only 1 receipt, then stamped
645 --the receipt in the GTMP record.
646 x_receipt_num := l_prev_receipt;
647 ELSE
648 --If this MOL corresponds to more than 1 receipt, then
649 --leave the receipt as null in the GTMP table. Moreover
650 --if the user has use receipt as a query critiria, prompt
651 --a warning message that this MOL is mixed
652 x_receipt_num := NULL;
653
654 IF p_receipt_num IS NOT NULL THEN
655 x_unique := 'N';
656 END IF;
657 END IF;
658
659 IF l_location_unique = 1 THEN
660 x_rcv_location_id := l_prev_location_id;
661 ELSE
662 x_rcv_location_id := NULL;
663
664 IF p_rcv_location_id IS NOT NULL THEN
665 x_unique := 'N';
666 END IF;
667 END IF;
668
669 IF l_partner_unique = 1 THEN
670 IF p_partner_type = 1 THEN
671 x_vendor_id := l_prev_partner_id;
672 ELSIF p_partner_type = 2 THEN
673 x_from_org_id := l_prev_partner_id;
674 END IF;
675 ELSE
676
677 x_vendor_id := NULL;
678 x_from_org_id := NULL;
679
680 IF p_partner_type IS NOT NULL THEN
681 x_unique := 'N';
682 END IF;
683 END IF;
684
685 IF (l_debug = 1) THEN
686 trace('x_valid:'||x_valid||' x_unique:'||x_unique);
687 trace('x_receipt_num:'||x_receipt_num||
688 ' x_rcv_location_id:'||x_rcv_location_id||
689 ' x_vendor_id:'||x_vendor_id||
690 ' x_from_org_id:'||x_from_org_id
691 );
692 END IF;
693
694 EXCEPTION
695 WHEN others THEN
696 IF l_debug = 1 THEN
697 trace('Error in query_inbound_material(), progress='||l_progress);
698 trace('ERROR CODE = ' || SQLCODE);
699 trace('ERROR MESSAGE = ' || SQLERRM);
700 END IF;
701 dbms_sql.close_cursor(l_cursor);
702
703 x_valid := 'N';
704 END check_against_rcv;
705
706 /********************************************
707 Procedure to query inbound eligible material
708 *******************************************/
709 PROCEDURE query_inbound_material(
710 x_return_status OUT NOCOPY VARCHAR2
711 , p_organization_id IN NUMBER
712 , p_organization_code IN VARCHAR2
713 , p_subinventory_code IN VARCHAR2 DEFAULT NULL
714 , p_locator_id IN NUMBER DEFAULT NULL
715 , p_locator IN VARCHAR2 DEFAULT NULL
716 , p_inventory_item_id IN NUMBER DEFAULT NULL
717 , p_item IN VARCHAR2 DEFAULT NULL
718 , p_from_lpn_id IN NUMBER DEFAULT NULL
719 , p_project_id IN NUMBER DEFAULT NULL
720 , p_project IN VARCHAR2 DEFAULT NULL
721 , p_task_id IN NUMBER DEFAULT NULL
722 , p_task IN VARCHAR2 DEFAULT NULL
723 , p_document_type IN VARCHAR2 DEFAULT NULL
724 , p_document_id IN NUMBER DEFAULT NULL
725 , p_document_number IN VARCHAR2 DEFAULT NULL
726 , p_document_line_id IN NUMBER DEFAULT NULL
727 , p_document_line_num IN VARCHAR2 DEFAULT NULL--CLM Changes, Line number to be alphanumeric
728 , p_receipt_number IN VARCHAR2 DEFAULT NULL
729 , p_partner_id IN NUMBER DEFAULT NULL
730 , p_partner_type IN NUMBER DEFAULT NULL
731 , p_partner_name IN VARCHAR2 DEFAULT NULL
732 , p_rcv_location_id IN NUMBER DEFAULT NULL
733 , p_rcv_location IN VARCHAR2 DEFAULT NULL
734 , p_is_pjm_enabled_org IN VARCHAR2 DEFAULT 'N'
735 , x_source_unique OUT nocopy VARCHAR2 --R12
736
737 ) IS
738
739
740 CURSOR get_proj_task_rec IS
741 select distinct inventory_item_id
742 from wms_packing_material_gtemp
743 where lpn_id is null --for loose item
744 and project_id is not NULL; --taks id can be loose
745
746 l_get_proj_task_rec get_proj_task_rec%ROWTYPE;
747
748 -- Bug 3802897, after partial quantity is delivered,
749 -- The move order line is still open and quantity remains the same
750 -- The quantity_delivered will be populated with the delivered qty
751 -- The actual available quantity is mol.quantity-mol.quantity_delivered
752
753 -- Changed the select to mol.quantity-nvl(mol.quantity_delivered,0)
754 -- And retrict line with such quantity > 0
755 l_select_str VARCHAR2(2000) :=
756 'SELECT mol.header_id mol_header_id, mol.line_id mol_line_id, mol.reference, mol.reference_id, mol.txn_source_id, '||
757 'mol.organization_id organization_id, :org_code organization_code, nvl(lpn.subinventory_code,mol.from_subinventory_code) subinventory, '||
758 'nvl(lpn.locator_id,mol.from_locator_id) locator_id, :locator locator, mol.project_id project_id, :project project, mol.task_id task_id, :task task, '||
759 'mol.inventory_item_id inventory_item_id, :item item, mol.lpn_id lpn_id, mol.revision revision, mol.uom_code uom, mol.quantity-nvl(mol.quantity_delivered,0) quantity, mol.lot_number, '||
760 'mol.secondary_quantity -NVL(mol.secondary_quantity_delivered,0) secondary_quantity, mol.secondary_uom_code, mol.grade_code'; --INCONV kkillams
761
762 l_from_str VARCHAR2(2000) :=
763 ' FROM mtl_txn_request_lines mol, mtl_txn_request_headers moh, wms_license_plate_numbers lpn ';
764
765 -- Bug 3802897
766 -- Make sure the available quantity is greater than 0
767 l_where_str VARCHAR2(2000) :=
768 'WHERE moh.header_id = mol.header_id AND moh.move_order_type = 6 '||
769 'AND mol.line_status <> 5 AND (mol.quantity-nvl(mol.quantity_delivered,0))>0 '||
770 'AND lpn.lpn_id(+) = mol.lpn_id AND lpn.organization_id(+) = mol.organization_id '||
771 'AND (mol.lpn_id is null or (mol.lpn_id is not null and lpn.lpn_context = 3)) '||
772 'AND nvl(mol.wms_process_flag,1) <> 2 AND mol.organization_id = :org_id AND mol.inventory_item_id = nvl(:inventory_item_id, mol.inventory_item_id) '||
773 'AND nvl(mol.project_id, -9999) = nvl(:project_id, nvl(mol.project_id, -9999)) AND nvl(mol.task_id, -9999) = nvl(:task_id, nvl(mol.task_id, -9999)) '||
774 'AND ((mol.lpn_id IS NULL) OR '||
775 ' (mol.lpn_id IS NOT NULL AND (NOT exists (select 1 from wms_dispatched_tasks wdt where wdt.transfer_lpn_id = mol.lpn_id)) '||
776 ' AND (NOT exists (select 1 from wms_dispatched_tasks wdt, mtl_material_transactions_temp mmtt where wdt.transaction_temp_id = mmtt.transaction_temp_id and mmtt.lpn_id = mol.lpn_id)))) ';
777
778 l_sub_where_str VARCHAR2(100) :=
779 ' AND (nvl(lpn.subinventory_code,mol.from_subinventory_code)=:subinventory) ';
780
781 l_loc_where_str VARCHAR2(100) :=
782 ' AND (nvl(lpn.locator_id,mol.from_locator_id)=:locator_id) ';
783
784 l_fromlpn_where_str VARCHAR2(200) :=
785 ' AND (lpn.lpn_id = :from_lpn_id OR (lpn.outermost_lpn_id = :from_lpn_id and lpn.lpn_id <> lpn.outermost_lpn_id) OR (lpn.parent_lpn_id = :from_lpn_id and lpn.lpn_id <> lpn.parent_lpn_id))';
786
787 l_query_sql VARCHAR2(10000);
788
789 p_n NUMBER;
790 p_v VARCHAR2(256);
791
792 l_rs_exists BOOLEAN := false;
793 l_rsh_exists BOOLEAN := false;
794 l_rsl_exists BOOLEAN := false;
795 l_rt_exists BOOLEAN := false;
796
797 l_cursor NUMBER;
798 l_last_error_pos NUMBER;
799 l_temp_str VARCHAR2(100);
800 l_return NUMBER;
801
802 l_mol_header_id NUMBER;
803 l_mol_line_id NUMBER;
804 l_reference VARCHAR2(20);
805 l_reference_id NUMBER;
806 l_txn_source_id NUMBER;
807 l_organization_id NUMBER;
808 l_organization_code VARCHAR2(3);
809 l_subinventory VARCHAR2(10);
810 l_locator_id NUMBER;
811 l_locator VARCHAR2(204);
812 l_project_id NUMBER;
813 l_project VARCHAR2(30);
814 l_task_id NUMBER;
815 l_task VARCHAR2(30);
816 l_inventory_item_id NUMBER;
817 l_item VARCHAR2(40);
818 l_lpn_id NUMBER;
819 l_revision VARCHAR2(3);
820 l_uom VARCHAR2(3);
821 l_qty NUMBER;
822 --INVCONV kkillams
823 l_sec_uom VARCHAR2(3);
824 l_sec_qty NUMBER;
825 l_grade VARCHAR2(150);
826 --END INVCONV kkillams
827 l_lot VARCHAR2(30);
828 l_rcv_location_id NUMBER;
829 l_rcv_location VARCHAR2(60);
830 l_vendor_id NUMBER;
831 l_src_org_id NUMBER;
832 l_partner_name VARCHAR2(240);
833 l_doc_type VARCHAR2(10);
834 l_doc_num_id NUMBER;
835 l_document_number VARCHAR2(50);
836 l_receipt_num VARCHAR2(30);
837 l_doc_line_id NUMBER;
838 l_doc_line_num VARCHAR2(10);--CLM Changes, Line number to be alphanumeric
839
840 l_material_rec WMS_PACKING_MATERIAL_GTEMP%ROWTYPE;
841 l_null_material WMS_PACKING_MATERIAL_GTEMP%ROWTYPE;
842 l_rec_count NUMBER;
843 l_exists NUMBER;
844
845 l_shipment_header_id NUMBER;
846 l_shipment_line_id NUMBER;
847 l_req_line_id NUMBER;
848 l_shipment_num VARCHAR2(30);
849 l_asn_type VARCHAR2(25);
850 l_receipt_source_code VARCHAR2(25);
851 l_ship_to_org_id NUMBER;
852 l_line_num NUMBER;
853
854 l_progress VARCHAR2(20);
855 l_item_cnt NUMBER;
856 l_msg_count NUMBER;
857 l_msg_data VARCHAR2(1000);
858 l_proc_msg VARCHAR2(1000);
859
860 l_valid VARCHAR2(1);
861 l_unique VARCHAR2(1);
862 l_receipt_used NUMBER;
863 l_doc_line_used NUMBER;
864 l_doc_num_used NUMBER;
865
866 --14274513
867 l_rcpt_qty NUMBER;
868 l_rcpt_secondary_qty NUMBER;
869
870 BEGIN
871 l_progress := '000';
872 IF l_debug = 1 THEN
873 trace('In WMS Packing Workbench, package header ='|| g_pkg_version);
874 trace('Query Inbound eligible material with parameters :');
875 trace(' p_organization_id='||p_organization_id||', p_subinventory_code='||p_subinventory_code||', p_locator_id='||p_locator_id||', p_locator='||p_locator);
876 trace(' p_inventory_item_id='||p_inventory_item_id||', p_from_lpn_id='||p_from_lpn_id||', p_project_id='||p_project_id||', p_task_id='||p_task_id);
877 trace(' p_document_type='||p_document_type);
878 trace(' p_document_id='||p_document_id||', p_document_number='||p_document_number);
879 trace(' p_document_line_id='||p_document_line_id||', p_document_line_num='||p_document_line_num);
880 trace(' p_receipt_number='||p_receipt_number||', p_partner_id='||p_partner_id||', p_partner_type='||p_partner_type);
881 trace(' p_rcv_location_id='||p_rcv_location_id||',p_rcv_location='||p_rcv_location);
882
883 trace(' p_is_pjm_enabled_org ='|| p_is_pjm_enabled_org);
884 END IF;
885
886 x_return_status := fnd_api.G_RET_STS_SUCCESS;
887
888 /* Step 1, Build Dynamic SQL statement for the query */
889 IF p_subinventory_code IS NOT NULL THEN
890 l_where_str := l_where_str || l_sub_where_str;
891 END IF;
892 IF p_locator_id IS NOT NULL THEN
893 l_where_str := l_where_str || l_loc_where_str;
894 END IF;
895 l_progress := '001';
896 IF p_from_lpn_id IS NOT NULL THEN
897 l_where_str := l_where_str || l_fromlpn_where_str;
898 END IF;
899 l_progress := '002';
900
901 --R12
902
903 l_select_str := l_select_str||', NULL rcv_location_id';
904 l_select_str := l_select_str||', NULL rcv_location';
905 l_select_str := l_select_str||', NULL vendor_id';
906 l_select_str := l_select_str||', NULL src_org_id';
907 l_select_str := l_select_str||', NULL parnter_name';
908 l_select_str := l_select_str||', NULL doc_type';
909 l_select_str := l_select_str||', NULL doc_num_id';
910 l_select_str := l_select_str||', NULL document_number';
911 l_select_str := l_select_str||', NULL receipt_num';
912 l_select_str := l_select_str||', NULL doc_line_id';
913 l_select_str := l_select_str||', NULL doc_line_num';
914
915 l_receipt_used := 0;
916 l_doc_line_used := 0;
917 l_doc_num_used := 0;
918
919 IF (p_subinventory_code IS NULL
920 AND p_locator_id IS NULL
921 AND p_inventory_item_id IS NULL
922 AND p_from_lpn_id IS NULL
923 AND p_project_id IS NULL
924 AND p_task_id IS NULL) THEN
925 IF (p_receipt_number IS NOT NULL) THEN
926 l_receipt_used := 1;
927 l_where_str := l_where_str||' AND mol.inventory_item_id';
928 l_where_str := l_where_str||' IN (SELECT rsl.item_id';
929 l_where_str := l_where_str||' FROM rcv_shipment_lines rsl,rcv_shipment_headers rsh';
930 l_where_str := l_where_str||' WHERE rsh.receipt_num = :receipt_num';
931 l_where_str := l_where_str||' AND rsh.shipment_header_id = rsl.shipment_header_id)';
932 ELSIF (p_document_type = 'PO') THEN
933 IF (p_document_id IS NOT NULL AND p_document_line_id IS NOT NULL) THEN
934 l_doc_line_used := 1;
935 l_doc_num_used := 1;
936 l_where_str := l_where_str||' AND mol.inventory_item_id';
937 l_where_str := l_where_str||' IN (SELECT rs.item_id';
938 l_where_str := l_where_str||' FROM rcv_supply rs';
939 l_where_str := l_where_str||' WHERE rs.po_line_id = :doc_line_id';
940 l_where_str := l_where_str||' AND rs.po_header_id = :doc_num_id';
941 l_where_str := l_where_str||' AND rs.to_organization_id = :org_id)';
942 ELSIF p_document_id IS NOT NULL THEN
943 l_doc_num_used := 1;
944 l_where_str := l_where_str||' AND mol.inventory_item_id';
945 l_where_str := l_where_str||' IN (SELECT rs.item_id';
946 l_where_str := l_where_str||' FROM rcv_supply rs';
947 l_where_str := l_where_str||' WHERE rs.po_header_id = :doc_num_id';
948 l_where_str := l_where_str||' AND rs.to_organization_id = :org_id)';
949 ELSE
950 l_where_str := l_where_str||' AND mol.inventory_item_id';
951 l_where_str := l_where_str||' IN (SELECT rs.item_id';
952 l_where_str := l_where_str||' FROM rcv_supply rs';
953 l_where_str := l_where_str||' WHERE rs.to_organization_id = :org_id';
954 l_where_str := l_where_str||' AND rs.po_line_id IS NOT NULL)';
955 END IF;
956 ELSIF (p_document_type = 'REQ') THEN
957 IF (p_document_id IS NOT NULL AND p_document_line_id IS NOT NULL) THEN
958 l_doc_line_used := 1;
959 l_doc_num_used := 1;
960 l_where_str := l_where_str||' AND mol.inventory_item_id';
961 l_where_str := l_where_str||' IN (SELECT rs.item_id';
962 l_where_str := l_where_str||' FROM rcv_supply rs';
963 l_where_str := l_where_str||' WHERE rs.req_line_id = :doc_line_id';
964 l_where_str := l_where_str||' AND rs.req_header_id = :doc_num_id';
965 l_where_str := l_where_str||' AND rs.to_organization_id = :org_id)';
966 ELSIF p_document_id IS NOT NULL THEN
967 l_doc_num_used := 1;
968 l_where_str := l_where_str||' AND mol.inventory_item_id';
969 l_where_str := l_where_str||' IN (SELECT rs.item_id';
970 l_where_str := l_where_str||' FROM rcv_supply rs';
971 l_where_str := l_where_str||' WHERE rs.req_header_id = :doc_num_id';
972 l_where_str := l_where_str||' AND rs.to_organization_id = :org_id)';
973 ELSE
974 l_where_str := l_where_str||' AND mol.inventory_item_id';
975 l_where_str := l_where_str||' IN (SELECT rs.item_id';
976 l_where_str := l_where_str||' FROM rcv_supply rs';
977 l_where_str := l_where_str||' WHERE rs.req_line_id IS NOT NULL';
978 l_where_str := l_where_str||' AND rs.to_organization_id = :org_id)';
979 END IF;
980 ELSIF (p_document_type IN ('ASN','INTSHIP')) THEN
981 IF (p_document_id IS NOT NULL AND p_document_line_id IS NOT NULL) THEN
982 l_doc_line_used := 1;
983 l_doc_num_used := 1;
984 l_where_str := l_where_str||' AND mol.inventory_item_id';
985 l_where_str := l_where_str||' IN (SELECT rs.item_id';
986 l_where_str := l_where_str||' FROM rcv_supply rs';
987 l_where_str := l_where_str||' WHERE rs.shipment_line_id = :doc_line_id';
988 l_where_str := l_where_str||' WHERE rs.shipment_header_id = :doc_num_id';
989 l_where_str := l_where_str||' AND rs.to_organization_id = :org_id)';
990 ELSIF p_document_id IS NOT NULL THEN
991 l_doc_num_used := 1;
992 l_where_str := l_where_str||' AND mol.inventory_item_id';
993 l_where_str := l_where_str||' IN (SELECT rs.item_id';
994 l_where_str := l_where_str||' FROM rcv_supply rs';
995 l_where_str := l_where_str||' WHERE rs.shipment_header_id = :doc_num_id';
996 l_where_str := l_where_str||' AND rs.to_organization_id = :org_id)';
997 ELSE
998 IF (p_document_type = 'ASN') THEN
999 l_where_str := l_where_str||' AND mol.inventory_item_id';
1000 l_where_str := l_where_str||' IN (SELECT rs.item_id';
1001 l_where_str := l_where_str||' FROM rcv_supply rs,rcv_shipment_headers rsh';
1002 l_where_str := l_where_str||' WHERE rsh.asn_type IN (''ASN'',''ASBN'')';
1003 l_where_str := l_where_str||' AND rsh.shipment_num is not null';
1004 l_where_str := l_where_str||' AND rs.shipment_header_id = rsh.shipment_header_id';
1005 l_where_str := l_where_str||' AND rs.to_organization_id = :org_id)';
1006 ELSE
1007 l_where_str := l_where_str||' AND mol.inventory_item_id';
1008 l_where_str := l_where_str||' IN (SELECT rs.item_id';
1009 l_where_str := l_where_str||' FROM rcv_supply rs,rcv_shipment_headers rsh';
1010 l_where_str := l_where_str||' WHERE rsh.asn_type NOT IN (''ASN'',''ASBN'')';
1011 l_where_str := l_where_str||' AND rsh.shipment_num is not null';
1012 l_where_str := l_where_str||' AND rs.to_organization_id = :org_id';
1013 l_where_str := l_where_str||' AND rs.shipment_header_id = rsh.shipment_header_id';
1014 l_where_str := l_where_str||' AND rsh.receipt_source_code IN (''INTERNAL ORDER'',''INVENTORY''))';
1015 END IF;
1016 END IF;
1017 ELSIF (p_document_type = 'RMA') THEN
1018 IF (p_document_id IS NOT NULL AND p_document_line_id IS NOT NULL) THEN
1019 l_doc_line_used := 1;
1020 l_doc_num_used := 1;
1021 l_where_str := l_where_str||' AND mol.inventory_item_id';
1022 l_where_str := l_where_str||' IN (SELECT rs.item_id';
1023 l_where_str := l_where_str||' FROM rcv_supply rs';
1024 l_where_str := l_where_str||' WHERE rs.oe_order_line_id = :doc_line_id';
1025 l_where_str := l_where_str||' AND rs.oe_order_header_id = :doc_num_id';
1026 l_where_str := l_where_str||' AND rs.to_organization_id = :org_id)';
1027 ELSIF p_document_line_id IS NOT NULL THEN
1028 l_doc_num_used := 1;
1029 l_where_str := l_where_str||' AND mol.inventory_item_id';
1030 l_where_str := l_where_str||' IN (SELECT rs.item_id';
1031 l_where_str := l_where_str||' FROM rcv_supply rs';
1032 l_where_str := l_where_str||' WHERE rs.oe_order_header_id = :doc_num_id';
1033 l_where_str := l_where_str||' AND rs.to_organization_id = :org_id)';
1034 ELSE
1035 l_where_str := l_where_str||' AND mol.inventory_item_id';
1036 l_where_str := l_where_str||' IN (SELECT rs.item_id';
1037 l_where_str := l_where_str||' FROM rcv_supply rs';
1038 l_where_str := l_where_str||' WHERE rs.oe_order_line_id IS NOT NULL';
1039 l_where_str := l_where_str||' AND rs.to_organization_id = :org_id)';
1040 END IF;
1041 END IF;
1042 END IF;
1043
1044 l_progress := '020';
1045 l_query_sql := l_select_str || l_from_str || l_where_str ;
1046
1047 p_n :=1;
1048 WHILE p_n <=length(l_query_sql) LOOP
1049 p_v := Substr( l_query_sql,p_n,255);
1050 trace(p_v);
1051 p_n := p_n +255;
1052 END LOOP;
1053
1054
1055 /* Step 2, Build Cusor, Bind variables */
1056 IF l_debug =1 THEN
1057 trace('Finished building SQL, start build cursor');
1058 END IF;
1059 l_cursor := dbms_sql.open_cursor;
1060 l_progress := '030';
1061 BEGIN
1062 dbms_sql.parse(l_cursor,l_query_sql,dbms_sql.v7);
1063 EXCEPTION
1064 WHEN OTHERS THEN
1065 l_last_error_pos := dbms_sql.last_error_position();
1066 l_temp_str := Substr(l_query_sql, l_last_error_pos-5, 50);
1067 IF l_debug = 1 THEN
1068 trace('Error in parse sql statement, at '||l_temp_str);
1069 END IF;
1070 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1071 END;
1072
1073 l_progress := '040';
1074 IF l_debug =1 THEN
1075 trace('Binding variables');
1076 END IF;
1077 -- Bind variables
1078 dbms_sql.bind_variable(l_cursor, ':org_id', p_organization_id);
1079 dbms_sql.bind_variable(l_cursor, ':org_code', p_organization_code);
1080 dbms_sql.bind_variable(l_cursor, ':inventory_item_id', p_inventory_item_id);
1081 dbms_sql.bind_variable(l_cursor, ':item', p_item);
1082 dbms_sql.bind_variable(l_cursor, ':project_id', p_project_id);
1083 dbms_sql.bind_variable(l_cursor, ':project', p_project);
1084 dbms_sql.bind_variable(l_cursor, ':task_id', p_task_id);
1085 dbms_sql.bind_variable(l_cursor, ':task', p_task);
1086 l_progress := '050';
1087 IF p_subinventory_code IS NOT NULL THEN
1088 dbms_sql.bind_variable(l_cursor, ':subinventory', p_subinventory_code);
1089 END IF;
1090 IF p_locator_id IS NOT NULL THEN
1091 dbms_sql.bind_variable(l_cursor, ':locator_id', p_locator_id);
1092 END IF;
1093 dbms_sql.bind_variable(l_cursor, ':locator', p_locator);
1094 IF p_from_lpn_id IS NOT NULL THEN
1095 dbms_sql.bind_variable(l_cursor, ':from_lpn_id', p_from_lpn_id);
1096 END IF;
1097
1098 IF p_document_id IS NOT NULL AND l_doc_num_used = 1 THEN
1099 dbms_sql.bind_variable(l_cursor, ':doc_num_id', p_document_id);
1100 END IF;
1101
1102 IF p_document_line_id IS NOT NULL AND l_doc_line_used = 1 THEN
1103 dbms_sql.bind_variable(l_cursor, ':doc_line_id', p_document_line_id);
1104 END IF;
1105
1106 IF (p_receipt_number IS NOT NULL AND l_receipt_used = 1) THEN
1107 dbms_sql.bind_variable(l_cursor, ':receipt_num', p_receipt_number);
1108 END IF;
1109
1110 l_progress := '060';
1111
1112 /* Step 3. Execute the query */
1113 IF l_debug = 1 THEN
1114 trace('Execute the query');
1115 END IF;
1116 --Define output parameters
1117 dbms_sql.define_column(l_cursor, 1, l_mol_header_id);
1118 dbms_sql.define_column(l_cursor, 2, l_mol_line_id);
1119 dbms_sql.define_column(l_cursor, 3, l_reference,240);
1120 dbms_sql.define_column(l_cursor, 4, l_reference_id);
1121 dbms_sql.define_column(l_cursor, 5, l_txn_source_id);
1122 dbms_sql.define_column(l_cursor, 6, l_organization_id);
1123 dbms_sql.define_column(l_cursor, 7, l_organization_code,3);
1124 dbms_sql.define_column(l_cursor, 8, l_subinventory,10);
1125 dbms_sql.define_column(l_cursor, 9, l_locator_id);
1126 dbms_sql.define_column(l_cursor, 10,l_locator,204);
1127 dbms_sql.define_column(l_cursor, 11, l_project_id);
1128 dbms_sql.define_column(l_cursor, 12, l_project,30);
1129 dbms_sql.define_column(l_cursor, 13, l_task_id);
1130 dbms_sql.define_column(l_cursor, 14, l_task,30);
1131 dbms_sql.define_column(l_cursor, 15, l_inventory_item_id);
1132 dbms_sql.define_column(l_cursor, 16, l_item,40);
1133 dbms_sql.define_column(l_cursor, 17, l_lpn_id);
1134 dbms_sql.define_column(l_cursor, 18, l_revision,3);
1135 dbms_sql.define_column(l_cursor, 19, l_uom,3);
1136 dbms_sql.define_column(l_cursor, 20, l_qty);
1137 dbms_sql.define_column(l_cursor, 21, l_lot, 30);
1138 --INVCONV kkillams
1139 dbms_sql.define_column(l_cursor, 22, l_sec_qty);
1140 dbms_sql.define_column(l_cursor, 23, l_sec_uom,3);
1141 dbms_sql.define_column(l_cursor, 24, l_grade,150);
1142 --END INVCONV kkillams
1143 dbms_sql.define_column(l_cursor, 25, l_rcv_location_id);
1144 dbms_sql.define_column(l_cursor, 26, l_rcv_location,60);
1145 dbms_sql.define_column(l_cursor, 27, l_vendor_id);
1146 dbms_sql.define_column(l_cursor, 28, l_src_org_id);
1147 dbms_sql.define_column(l_cursor, 29, l_partner_name,240);
1148 dbms_sql.define_column(l_cursor, 30, l_doc_type,20);
1149 dbms_sql.define_column(l_cursor, 31, l_doc_num_id);
1150 dbms_sql.define_column(l_cursor, 32, l_document_number,50);
1151 dbms_sql.define_column(l_cursor, 33, l_receipt_num,30);
1152 dbms_sql.define_column(l_cursor, 34, l_doc_line_id);
1153 dbms_sql.define_column(l_cursor, 35, l_doc_line_num,10);--CLM Changes, Line number to be alphanumeric
1154
1155
1156 l_progress := '070';
1157 l_return := dbms_sql.execute(l_cursor);
1158 l_progress := '080';
1159 IF l_debug = 1 THEN
1160 trace('Executed query');
1161 END IF;
1162
1163 delete from wms_packing_material_gtemp;
1164 --delete from wms_packing_material_temp;
1165
1166 l_rec_count := 0;
1167 LOOP
1168 -- Fetch the rows into the buffer, and also check for the exit
1169 -- condition from the loop.
1170 IF DBMS_SQL.FETCH_ROWS(l_cursor) = 0 THEN
1171 EXIT;
1172 END IF;
1173 l_material_rec := l_null_material;
1174 -- Retrieve the rows from the buffer into temp variables.
1175 dbms_sql.column_value(l_cursor, 1, l_material_rec.move_order_header_id);
1176 dbms_sql.column_value(l_cursor, 2, l_material_rec.move_order_line_id);
1177 dbms_sql.column_value(l_cursor, 3, l_material_rec.reference);
1178 dbms_sql.column_value(l_cursor, 4, l_material_rec.reference_id);
1179 dbms_sql.column_value(l_cursor, 5, l_material_rec.txn_source_id);
1180 dbms_sql.column_value(l_cursor, 6, l_material_rec.organization_id);
1181 dbms_sql.column_value(l_cursor, 7, l_material_rec.organization_code);
1182 dbms_sql.column_value(l_cursor, 8, l_material_rec.subinventory);
1183 dbms_sql.column_value(l_cursor, 9, l_material_rec.locator_id);
1184 dbms_sql.column_value(l_cursor, 10,l_material_rec.locator);
1185 dbms_sql.column_value(l_cursor, 11, l_material_rec.project_id);
1186 dbms_sql.column_value(l_cursor, 12, l_material_rec.project);
1187 dbms_sql.column_value(l_cursor, 13, l_material_rec.task_id);
1188 dbms_sql.column_value(l_cursor, 14, l_material_rec.task_number);
1189 dbms_sql.column_value(l_cursor, 15, l_material_rec.inventory_item_id);
1190 dbms_sql.column_value(l_cursor, 16, l_material_rec.item);
1191 dbms_sql.column_value(l_cursor, 17, l_material_rec.lpn_id);
1192 dbms_sql.column_value(l_cursor, 18, l_material_rec.revision);
1193 dbms_sql.column_value(l_cursor, 19, l_material_rec.uom);
1194 dbms_sql.column_value(l_cursor, 20, l_material_rec.quantity);
1195 dbms_sql.column_value(l_cursor, 21, l_material_rec.lot_number);
1196 --INVCONV kkillams
1197 dbms_sql.column_value(l_cursor, 22, l_material_rec.secondary_quantity);
1198 dbms_sql.column_value(l_cursor, 23, l_material_rec.secondary_uom_code);
1199 dbms_sql.column_value(l_cursor, 24, l_material_rec.grade_code);
1200 --END INVCONV kkillams
1201 dbms_sql.column_value(l_cursor, 25, l_material_rec.receiving_location_id);
1202 dbms_sql.column_value(l_cursor, 26, l_material_rec.receiving_location);
1203 dbms_sql.column_value(l_cursor, 27, l_material_rec.vendor_id);
1204 dbms_sql.column_value(l_cursor, 28, l_material_rec.source_org_id);
1205 dbms_sql.column_value(l_cursor, 29, l_material_rec.trading_partner);
1206 dbms_sql.column_value(l_cursor, 30, l_material_rec.document_type);
1207 dbms_sql.column_value(l_cursor, 31, l_material_rec.document_id);
1208 dbms_sql.column_value(l_cursor, 32, l_material_rec.document_number);
1209 dbms_sql.column_value(l_cursor, 33, l_material_rec.receipt_num);
1210 dbms_sql.column_value(l_cursor, 34, l_material_rec.document_line_id);
1211 dbms_sql.column_value(l_cursor, 35, l_material_rec.document_line_num);
1212
1213 l_rec_count := l_rec_count + 1;
1214
1215 trace('Calling check_against_rcv');
1216 trace(' l_material_rec.line_id => '|| l_material_rec.move_order_line_id);
1217 trace(' l_material_rec.inventory_item_id => '|| l_material_rec.inventory_item_id);
1218 trace(' l_material_rec.revision => '|| l_material_rec.revision);
1219 trace(' l_material_rec.lot_number => '|| l_material_rec.lot_number);
1220
1221 check_against_rcv
1222 (p_mol_rec => l_material_rec
1223 , p_document_type => p_document_type
1224 , p_document_id => p_document_id
1225 , p_document_line_id => p_document_line_id
1226 , p_receipt_num => p_receipt_number
1227 , p_partner_id => p_partner_id
1228 , p_partner_type => p_partner_type
1229 , p_rcv_location_id => p_rcv_location_id
1230 , x_valid => l_valid
1231 , x_unique => l_unique
1232 , x_receipt_num => l_material_rec.receipt_num
1233 , x_rcv_location_id => l_material_rec.receiving_location_id
1234 , x_vendor_id => l_material_rec.vendor_id
1235 , x_from_org_id => l_material_rec.source_org_id
1236 --14274513
1237 , x_quantity => l_rcpt_qty
1238 , x_secondary_quantity => l_rcpt_secondary_qty);
1239
1240 IF (l_valid = 'N') THEN
1241 GOTO nextmolrec;
1242 END IF;
1243
1244 IF (x_source_unique IS NULL OR x_source_unique = 'Y') THEN
1245 x_source_unique := l_unique;
1246 END IF;
1247
1248 trace(' receipt_num:'||l_material_rec.receipt_num||
1249 ' receiving_location_id:'||l_material_rec.receiving_location_id||
1250 ' vendor_id:'||l_material_rec.vendor_id||
1251 ' source_org_id:'||l_material_rec.source_org_id);
1252
1253
1254 -- Derive column values
1255 l_progress := '090-'||l_rec_count;
1256
1257 -- LPN, Parent LPN, Outermost LPN
1258 IF l_material_rec.lpn_id IS NOT NULL THEN
1259 BEGIN
1260 SELECT lpn.license_plate_number, lpn.parent_lpn_id, pLpn.license_plate_number,
1261 lpn.outermost_lpn_id, oLpn.license_plate_number
1262 INTO l_material_rec.lpn,
1263 l_material_rec.parent_lpn_id,
1264 l_material_rec.parent_lpn,
1265 l_material_rec.outermost_lpn_id, l_material_rec.outermost_lpn
1266 FROM wms_license_plate_numbers lpn, wms_license_plate_numbers pLpn, wms_license_plate_numbers oLpn
1267 WHERE lpn.lpn_id = l_material_rec.lpn_id
1268 AND pLpn.lpn_id(+) = lpn.parent_lpn_id
1269 AND oLpn.lpn_id(+) = lpn.outermost_lpn_id;
1270 EXCEPTION
1271 WHEN no_data_found THEN
1272 IF l_debug = 1 THEN
1273 trace(' can not find lpn for lpn_id '|| l_material_rec.lpn_id);
1274 END IF;
1275 l_material_rec.lpn := null;
1276 l_material_rec.parent_lpn_id := null;
1277 l_material_rec.parent_lpn := null;
1278 l_material_rec.outermost_lpn_id := null;
1279 l_material_rec.outermost_lpn := null;
1280 END;
1281 END IF;
1282 l_progress := '091-'||l_rec_count;
1283 -- Locator
1284 IF l_material_rec.locator IS NULL AND
1285 (l_material_rec.locator_id IS NOT NULL AND l_material_rec.locator_id NOT IN (-1,0)) THEN
1286 BEGIN
1287 SELECT
1288 inv_project.get_locsegs(l_material_rec.locator_id,l_material_rec.organization_id) /*bug344642 concatenated_segments*/ INTO l_material_rec.locator
1289 FROM mtl_item_locations_kfv
1290 WHERE organization_id = l_material_rec.organization_id
1291 AND subinventory_code = l_material_rec.subinventory
1292 AND inventory_location_id = l_material_rec.locator_id;
1293 EXCEPTION
1294 WHEN no_data_found THEN
1295 IF l_debug = 1 THEN
1296 trace(' can not find locator name for loc_id '|| l_material_rec.locator_id||',mol _id ='||l_material_rec.move_order_line_id);
1297 END IF;
1298 l_material_rec.locator := null;
1299 END;
1300 END IF;
1301 l_progress := '092-'||l_rec_count;
1302 -- Project
1303 IF l_material_rec.project IS NULL AND l_material_rec.project_id IS NOT NULL THEN
1304 BEGIN
1305 SELECT name INTO l_material_rec.project
1306 FROM pa_projects WHERE project_id = l_material_rec.project_id;
1307 EXCEPTION
1308 WHEN no_data_found THEN
1309 IF l_debug = 1 THEN
1310 trace(' can not find project name for project_id '|| l_material_rec.project_id);
1311 END IF;
1312 l_material_rec.project := null;
1313 END;
1314 END IF;
1315 l_progress := '093-'||l_rec_count;
1316 -- Task
1317 IF l_material_rec.task_id IS NOT NULL THEN
1318 BEGIN
1319 SELECT task_number,task_name INTO l_material_rec.task_number, l_material_rec.task_name
1320 FROM pa_tasks
1321 WHERE project_id = l_material_rec.project_id
1322 AND task_id = l_material_rec.task_id;
1323 EXCEPTION
1324 WHEN no_data_found THEN
1325 IF l_debug = 1 THEN
1326 trace(' can not find task name for task_id '|| l_material_rec.task_id);
1327 END IF;
1328 l_material_rec.task_number := null;
1329 l_material_rec.task_name := null;
1330 END;
1331 END IF;
1332 l_progress := '094-'||l_rec_count;
1333 -- Item
1334 IF l_material_rec.inventory_item_id IS NOT NULL THEN
1335 BEGIN
1336 SELECT concatenated_segments,description INTO l_material_rec.item, l_material_rec.item_description
1337 FROM mtl_system_items_kfv
1338 WHERE organization_id = l_material_rec.organization_id
1339 AND inventory_item_id = l_material_rec.inventory_item_id;
1340 EXCEPTION
1341 WHEN no_data_found THEN
1342 IF l_debug = 1 THEN
1343 trace(' can not find item for item_id '|| l_material_rec.inventory_item_id);
1344 END IF;
1345 l_material_rec.item := null;
1346 l_material_rec.item_description := null;
1347 END;
1348 END IF;
1349
1350 l_progress := '095-'||l_rec_count;
1351 -- Receiving Location
1352 IF l_material_rec.receiving_location_id IS NOT NULL THEN
1353 BEGIN
1354 SELECT hrl.location_code
1355 INTO l_material_rec.receiving_location
1356 FROM hr_locations_all hrl
1357 WHERE hrl.location_id = l_material_rec.receiving_location_id;
1358 EXCEPTION
1359 WHEN no_data_found THEN
1360 IF l_debug = 1 THEN
1361 trace('Unable to retrieve location_code from location_id');
1362 END IF;
1363 l_material_rec.receiving_location_id := null;
1364 l_material_rec.receiving_location := null;
1365 END;
1366 END IF;
1367
1368 l_progress := '096-'||l_rec_count;
1369 -- Decide document type
1370 IF l_material_rec.reference = 'ORDER_LINE_ID' THEN
1371 l_material_rec.document_type := 'RMA';
1372 ELSIF l_material_rec.reference = 'PO_LINE_LOCATION_ID' THEN
1373 l_material_rec.document_type := 'PO';
1374 ELSIF l_material_rec.reference = 'SHIPMENT_LINE_ID' THEN
1375 IF l_material_rec.reference_id IS NOT NULL THEN
1376 BEGIN
1377 SELECT rsh.shipment_header_id, rsl.shipment_line_id, rsl.requisition_line_id, rsh.shipment_num, rsh.asn_type, rsh.receipt_source_code, rsh.ship_to_org_id, rsl.line_num, rsh.receipt_num, rsh.vendor_id, rsl.from_organization_id
1378 INTO l_shipment_header_id, l_shipment_line_id, l_req_line_id, l_shipment_num, l_asn_type, l_receipt_source_code, l_ship_to_org_id, l_line_num, l_receipt_num, l_vendor_id, l_src_org_id
1379 FROM rcv_shipment_lines rsl, rcv_shipment_headers rsh
1380 WHERE rsh.shipment_header_id = rsl.shipment_header_id
1381 AND rsl.shipment_line_id = l_material_rec.reference_id;
1382 EXCEPTION
1383 WHEN no_data_found THEN
1384 IF l_debug = 1 THEN
1385 trace('No data found in getting shipment line of '||l_material_rec.reference_id);
1386 END IF;
1387 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1388 END;
1389
1390 IF l_req_line_id IS NOT NULL THEN
1391 l_material_rec.document_type := 'REQ';
1392 ELSE
1393 IF l_shipment_num IS NOT NULL AND l_asn_type IN ('ASN','ASBN') THEN
1394 l_material_rec.document_type := 'ASN';
1395 ELSIF l_shipment_num IS NOT NULL THEN
1396 IF (l_receipt_source_code IN ('INTERNAL ORDER','INVENTORY')) AND
1397 (l_ship_to_org_id = l_material_rec.organization_id) THEN
1398 l_material_rec.document_type := 'INTSHIP';
1399 ELSE
1400 IF l_debug = 1 THEN
1401 trace('Can not decide document type');
1402 END IF;
1403 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1404 END IF;
1405 ELSE
1406 IF l_debug = 1 THEN
1407 trace('Can not decide document type');
1408 END IF;
1409 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1410 END IF;
1411 END IF;
1412 ELSE
1413 l_material_rec.document_type := 'MIXED';
1414 END IF;--END IF l_material_rec.reference_id IS NOT NULL THEN
1415 ELSE
1416 l_material_rec.document_type := 'MIXED';
1417 END IF;
1418
1419 l_progress := '097-'||l_rec_count;
1420 --trace('Document type is '||l_material_rec.document_type);
1421 -- obtain document information
1422 IF l_material_rec.document_type IN ('ASN', 'INTSHIP') THEN
1423 -- Document Number
1424 IF l_material_rec.document_number IS NOT NULL THEN
1425 IF (l_shipment_num IS NOT NULL) AND (l_shipment_num <> l_material_rec.document_number) THEN
1426 IF l_debug = 1 THEN
1427 trace('l_shipment_num '||l_shipment_num ||' not equal to l_material_rec.document_number '||l_material_rec.document_number);
1428 END IF;
1429 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1430 END IF;
1431 ELSE
1432 -- Need to derive document number
1433 IF l_shipment_num IS NOT NULL THEN
1434 l_material_rec.document_id := l_shipment_header_id;
1435 l_material_rec.document_number := l_shipment_num;
1436 ELSE
1437 IF (l_material_rec.reference_id IS NOT NULL) THEN
1438 BEGIN
1439 SELECT rsh.shipment_header_id, rsh.shipment_num, rsh.receipt_num,rsh.vendor_id
1440 ,rsl.shipment_line_id, rsl.line_num,rsl.from_organization_id
1441 INTO l_material_rec.document_id, l_material_rec.document_number, l_receipt_num, l_vendor_id,l_shipment_line_id, l_line_num, l_src_org_id
1442 FROM rcv_shipment_lines rsl, rcv_shipment_headers rsh
1443 WHERE rsh.shipment_header_id = rsl.shipment_header_id
1444 AND rsl.shipment_line_id = l_material_rec.reference_id;
1445 EXCEPTION
1446 WHEN no_data_found THEN
1447 IF l_debug = 1 THEN
1448 trace('Can not derive document number for type ASN/INTSHIP and reference_id = '||l_material_rec.reference_id);
1449 END IF;
1450 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1451 END;
1452 END IF;
1453 END IF;
1454 END IF;
1455
1456 -- Document Line
1457 IF l_material_rec.document_line_num IS NOT NULL THEN
1458 IF (l_line_num IS NOT NULL) AND (l_line_num <> l_material_rec.document_line_num) THEN
1459 IF l_debug = 1 THEN
1460 trace('l_line_num '||l_line_num ||' not equal to l_material_rec.document_line_num '||l_material_rec.document_line_num);
1461 END IF;
1462 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1463 END IF;
1464 ELSE
1465 -- Need to derive document number
1466 IF l_line_num IS NOT NULL THEN
1467 l_material_rec.document_line_id := l_shipment_line_id;
1468 l_material_rec.document_line_num := l_line_num;
1469 ELSE
1470 IF (l_material_rec.reference_id IS NOT NULL) THEN
1471 BEGIN
1472 SELECT rsl.shipment_line_id, rsl.line_num, rsl.from_organization_id
1473 INTO l_material_rec.document_line_id, l_material_rec.document_line_num, l_src_org_id
1474 FROM rcv_shipment_lines rsl
1475 WHERE rsl.shipment_line_id = l_material_rec.reference_id;
1476 EXCEPTION
1477 WHEN no_data_found THEN
1478 IF l_debug = 1 THEN
1479 trace('Can not derive document line for type ASN/INTSHIP and reference_id = '||l_material_rec.reference_id);
1480 END IF;
1481 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1482 END;
1483 END IF;
1484 END IF;
1485 END IF;
1486
1487 -- Trading Partner: Vendor or Source Org
1488 IF l_material_rec.trading_partner IS NULL THEN
1489 IF l_material_rec.document_type = 'ASN' THEN
1490 -- ASN, get vendor
1491 IF l_material_rec.vendor_id IS NULL THEN
1492 IF l_vendor_id IS NOT NULL THEN
1493 l_material_rec.vendor_id := l_vendor_id;
1494 ELSE
1495 IF (l_material_rec.reference_id IS NOT NULL) THEN
1496 BEGIN
1497 SELECT rsh.vendor_id INTO l_material_rec.vendor_id
1498 FROM rcv_shipment_lines rsl, rcv_shipment_headers rsh
1499 WHERE rsh.shipment_header_id = rsl.shipment_header_id
1500 AND rsl.shipment_line_id = l_material_rec.reference_id;
1501 EXCEPTION
1502 WHEN no_data_found THEN
1503 IF l_debug = 1 THEN
1504 trace('Can not derive vendor for type ASN and reference_id = '||l_material_rec.reference_id);
1505 END IF;
1506 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1507 END;
1508 END IF;
1509 END IF;
1510 END IF;
1511
1512 IF l_material_rec.vendor_id IS NOT NULL THEN
1513 BEGIN
1514 SELECT vendor_name INTO l_material_rec.trading_partner
1515 FROM po_vendors
1516 WHERE vendor_id = l_material_rec.vendor_id;
1517 EXCEPTION
1518 WHEN no_data_found THEN
1519 IF l_debug = 1 THEN
1520 trace('Can not derive vendor name for type ASN and vendor_id = '||l_material_rec.vendor_id);
1521 END IF;
1522 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1523 END;
1524 END IF;
1525 ELSE
1526 -- INTSHIP, get source_org
1527 IF l_material_rec.source_org_id IS NULL THEN
1528 IF l_src_org_id IS NOT NULL THEN
1529 l_material_rec.source_org_id := l_src_org_id;
1530 ELSE
1531 IF (l_material_rec.reference_id IS NOT NULL) THEN
1532 BEGIN
1533 SELECT rsl.from_organization_id INTO l_material_rec.source_org_id
1534 FROM rcv_shipment_lines rsl
1535 WHERE rsl.shipment_line_id = l_material_rec.reference_id;
1536 EXCEPTION
1537 WHEN no_data_found THEN
1538 IF l_debug = 1 THEN
1539 trace('Can not derive src_org_id for type INTSHIP and reference_id = '||l_material_rec.reference_id);
1540 END IF;
1541 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1542 END;
1543 END IF;
1544 END IF;
1545 END IF;
1546
1547 IF l_material_rec.source_org_id IS NOT NULL THEN
1548 BEGIN
1549 SELECT organization_code ||'-'||organization_name
1550 INTO l_material_rec.trading_partner
1551 FROM org_organization_definitions
1552 WHERE organization_id = l_material_rec.source_org_id;
1553 EXCEPTION
1554 WHEN no_data_found THEN
1555 IF l_debug = 1 THEN
1556 trace('Can not derive src_org name for type INSTHIP and org_id = '||l_material_rec.source_org_id);
1557 END IF;
1558 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1559 END;
1560 END IF;
1561 END IF;
1562 END IF;
1563
1564 ELSIF l_material_rec.document_type = 'PO' THEN
1565 -- Document Number
1566 --IF l_debug = 1 THEN
1567 -- trace('1 reference_id ='|| l_material_rec.reference_id);
1568 --END IF;
1569 IF l_material_rec.document_number IS NULL THEN
1570 IF (l_material_rec.reference_id IS NOT NULL) THEN
1571 BEGIN
1572 SELECT poh.po_header_id, poh.segment1, pol.po_line_id, pol.line_num
1573 INTO l_material_rec.document_id, l_material_rec.document_number
1574 , l_material_rec.document_line_id, l_material_rec.document_line_num
1575 FROM po_headers_trx_v poh, po_lines_trx_v pol, po_line_locations_trx_v poll--CLM Changes, using CLM views instead of base tables
1576 WHERE poll.line_location_id = l_material_rec.reference_id
1577 AND poh.po_header_id = poll.po_header_id
1578 AND pol.po_line_id = poll.po_line_id;
1579 EXCEPTION
1580 WHEN no_data_found THEN
1581 IF l_debug = 1 THEN
1582 trace('Can not derive document number for type PO and reference_id = '||l_material_rec.reference_id);
1583 END IF;
1584 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1585 END;
1586 END IF;
1587 END IF;
1588
1589 -- Document Line
1590 IF l_material_rec.document_line_num IS NULL THEN
1591 IF (l_material_rec.reference_id IS NOT NULL) THEN
1592 BEGIN
1593 SELECT pol.po_line_id, pol.line_num
1594 INTO l_material_rec.document_line_id, l_material_rec.document_line_num
1595 FROM po_lines_all pol, po_line_locations_all poll
1596 WHERE poll.line_location_id = l_material_rec.reference_id
1597 AND pol.po_line_id = poll.po_line_id;
1598 EXCEPTION
1599 WHEN no_data_found THEN
1600 IF l_debug = 1 THEN
1601 trace('Can not derive document line for type PO and reference_id = '||l_material_rec.reference_id);
1602 END IF;
1603 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1604 END;
1605 END IF;
1606 END IF;
1607
1608 -- Receipt Number/Vendor
1609 IF l_material_rec.vendor_id IS NOT NULL THEN
1610 BEGIN
1611 SELECT vendor_name INTO l_material_rec.trading_partner
1612 FROM po_vendors
1613 WHERE vendor_id = l_material_rec.vendor_id;
1614 EXCEPTION
1615 WHEN no_data_found THEN
1616 IF l_debug = 1 THEN
1617 trace('Can not derive vendor name for type PO and vendor_id = '||l_material_rec.vendor_id);
1618 END IF;
1619 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1620 END;
1621 END IF;
1622
1623 --14274513
1624 IF p_receipt_number IS NOT NULL and l_material_rec.lpn_id is null THEN
1625 l_material_rec.quantity := l_rcpt_qty;
1626 l_material_rec.secondary_quantity := l_rcpt_secondary_qty;
1627 l_material_rec.receipt_num := p_receipt_number;
1628 l_unique:='Y'; -- don't popup the warning msg
1629 END IF;
1630
1631 ELSIF l_material_rec.document_type = 'REQ' THEN
1632 -- Document Number
1633 IF l_material_rec.document_number IS NULL THEN
1634 IF (l_material_rec.reference_id IS NOT NULL) THEN
1635 BEGIN
1636 SELECT prh.requisition_header_id, prh.segment1, prl.requisition_line_id, prl.line_num
1637 INTO l_material_rec.document_id, l_material_rec.document_number
1638 , l_material_rec.document_line_id, l_material_rec.document_line_num
1639 FROM po_requisition_headers_all prh, po_requisition_lines_all prl, rcv_shipment_lines rsl
1640 WHERE rsl.shipment_line_id = l_material_rec.reference_id
1641 AND prh.requisition_header_id = prl.requisition_header_id
1642 AND prl.requisition_line_id = rsl.requisition_line_id;
1643 EXCEPTION
1644 WHEN no_data_found THEN
1645 IF l_debug = 1 THEN
1646 trace('Can not derive document number for type REQ and reference_id = '||l_material_rec.reference_id);
1647 END IF;
1648 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1649 END;
1650 END IF;
1651 END IF;
1652
1653 -- Document Line
1654 IF l_material_rec.document_line_num IS NULL THEN
1655 IF (l_material_rec.reference_id IS NOT NULL) THEN
1656 BEGIN
1657 SELECT prl.requisition_line_id, prl.line_num
1658 INTO l_material_rec.document_line_id, l_material_rec.document_line_num
1659 FROM po_requisition_lines_all prl, rcv_shipment_lines rsl
1660 WHERE rsl.shipment_line_id = l_material_rec.reference_id
1661 AND prl.requisition_line_id = rsl.requisition_line_id;
1662 EXCEPTION
1663 WHEN no_data_found THEN
1664 IF l_debug = 1 THEN
1665 trace('Can not derive document line for type REQ and reference_id = '||l_material_rec.reference_id);
1666 END IF;
1667 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1668 END;
1669 END IF;
1670 END IF;
1671
1672 -- Receipt Number/Vendor
1673 IF l_material_rec.source_org_id IS NOT NULL THEN
1674 BEGIN
1675 SELECT organization_code ||'-'||organization_name
1676 INTO l_material_rec.trading_partner
1677 FROM org_organization_definitions
1678 WHERE organization_id = l_material_rec.source_org_id;
1679 EXCEPTION
1680 WHEN no_data_found THEN
1681 IF l_debug = 1 THEN
1682 trace('Can not derive src_org name for type INSTHIP and org_id = '||l_material_rec.source_org_id);
1683 END IF;
1684 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1685 END;
1686 END IF;
1687 ELSIF l_material_rec.document_type = 'RMA' THEN
1688 -- Document Number
1689 IF l_material_rec.document_number IS NULL THEN
1690 IF (l_material_rec.reference_id IS NOT NULL) THEN
1691 BEGIN
1692 SELECT oeoh.header_id, to_char(oeoh.order_number), oeol.line_id, oeol.line_number
1693 INTO l_material_rec.document_id, l_material_rec.document_number
1694 , l_material_rec.document_line_id, l_material_rec.document_line_num
1695 FROM oe_order_headers_all oeoh, oe_order_lines_all oeol
1696 WHERE oeol.line_id = l_material_rec.reference_id
1697 AND oeoh.header_id = oeol.header_id;
1698 EXCEPTION
1699 WHEN no_data_found THEN
1700 IF l_debug = 1 THEN
1701 trace('Can not derive document number for type RMA and reference_id='||l_material_rec.reference_id);
1702 END IF;
1703 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1704 END;
1705 END IF;
1706 END IF;
1707
1708 -- Document Line
1709 IF l_material_rec.document_line_num IS NULL THEN
1710 IF (l_material_rec.reference_id IS NOT NULL) THEN
1711 BEGIN
1712 SELECT oeol.line_id, oeol.line_number
1713 INTO l_material_rec.document_line_id, l_material_rec.document_line_num
1714 FROM oe_order_lines_all oeol
1715 WHERE oeol.line_id = l_material_rec.reference_id;
1716 EXCEPTION
1717 WHEN no_data_found THEN
1718 IF l_debug = 1 THEN
1719 trace('Can not derive document line for type RMA and reference_id = '||l_material_rec.reference_id);
1720 END IF;
1721 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1722 END;
1723 END IF;
1724 END IF;
1725
1726 -- Receipt Number/Vendor
1727 IF l_material_rec.vendor_id IS NOT NULL THEN
1728 BEGIN
1729 SELECT vendor_name INTO l_material_rec.trading_partner
1730 FROM po_vendors
1731 WHERE vendor_id = l_material_rec.vendor_id;
1732 EXCEPTION
1733 WHEN no_data_found THEN
1734 IF l_debug = 1 THEN
1735 trace('Can not derive vendor name for type RMA and vendor_id = '||l_material_rec.vendor_id);
1736 END IF;
1737 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1738 END;
1739 END IF;
1740 END IF;
1741
1742 l_progress := '100-'||l_rec_count;
1743 -- Insert into the global temp table for eligible material
1744 insert_material_rec(l_material_rec);
1745 <<nextmolrec>>
1746 NULL;
1747 END LOOP;
1748
1749 dbms_sql.close_cursor(l_cursor);
1750
1751 IF l_debug = 1 THEN
1752 trace('Found eligible material records '||l_rec_count);
1753 END IF;
1754
1755
1756 --For patch set J, we will NOT be supporting the PJM transaction for loose
1757 --item if the query retrieved does not contain unique record with
1758 --item_id, project_id, task id combination. The reason is that we do not have
1759 --any logic as to which line to process FROM the list OF eligible
1760 --material in case we have multiple lines for same LOOSE item with
1761 -- different project and task
1762
1763 IF p_is_pjm_enabled_org = 'Y' THEN
1764
1765 IF l_debug = 1 THEN
1766 trace('Inside p_is_pjm_enabled_org condition');
1767 END IF;
1768
1769 OPEN get_proj_task_rec;--GET ALL distinct records for loose ITEMS with project
1770 LOOP
1771 FETCH get_proj_task_rec INTO l_get_proj_task_rec;
1772 IF get_proj_task_rec%notfound THEN
1773 CLOSE get_proj_task_rec;
1774 EXIT;
1775 END IF;
1776
1777 select count(1) INTO l_item_cnt
1778 FROM (select distinct project_id, task_id
1779 from wms_packing_material_gtemp
1780 WHERE lpn_id is NULL --loose items only
1781 and inventory_item_id = l_get_proj_task_rec.inventory_item_id
1782 AND project_id IS NOT NULL) wpmg;
1783
1784 IF l_debug = 1 THEN
1785 trace('There are '||l_item_cnt||' item records with project/TASK');
1786 END IF;
1787
1788 IF l_item_cnt > 1 THEN
1789
1790 IF l_debug = 1 THEN
1791 trace('Do not know which one to pick: Return');
1792 END IF;
1793
1794 CLOSE get_proj_task_rec;
1795 RAISE fnd_api.g_exc_error;
1796
1797 END IF;
1798
1799 END LOOP;
1800
1801 END IF;--p_is_pjm_enabled_org
1802
1803 /*
1804 if fnd_global.user_id = 1005653 THEN --SATKUMAR
1805 insert into wms_packing_material_temp value (select * from wms_packing_material_gtemp);
1806 commit;
1807 end if;
1808 */
1809
1810 EXCEPTION
1811 WHEN fnd_api.g_exc_error THEN
1812 x_return_status := fnd_api.G_RET_STS_ERROR;
1813 IF l_debug = 1 THEN
1814 trace('User defined: Show message: x_return_status :'||x_return_status);
1815 END IF;
1816
1817 WHEN others THEN
1818 IF l_debug = 1 THEN
1819 trace('Error in query_inbound_material(), progress='||l_progress);
1820 trace('ERROR CODE = ' || SQLCODE);
1821 trace('ERROR MESSAGE = ' || SQLERRM);
1822 END IF;
1823 dbms_sql.close_cursor(l_cursor);
1824
1825 x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR;
1826
1827 END query_inbound_material;
1828
1829 /************************************************
1830 * Get a hash value for a given string
1831 * This function is to solve hash collision issue
1832 ************************************************/
1833 FUNCTION get_column_hash_value (p_input_string VARCHAR2)
1834 RETURN NUMBER IS
1835 l_return_hash_value NUMBER;
1836 l_orig_hash_value NUMBER;
1837 l_hash_base NUMBER := 1;
1838 l_hash_size NUMBER := 256;
1839 BEGIN
1840 l_orig_hash_value := dbms_utility.get_hash_value
1841 (
1842 name => p_input_string
1843 ,base => l_hash_base
1844 ,hash_size => l_hash_size
1845 );
1846
1847 IF g_lot_ser_attr.exists(l_orig_hash_value) AND
1848 g_lot_ser_attr(l_orig_hash_value).column_name = p_input_string THEN
1849
1850 l_return_hash_value := l_orig_hash_value;
1851 ELSIF g_lot_ser_attr.exists(l_orig_hash_value) THEN
1852 -- hash collision
1853 LOOP
1854 l_orig_hash_value := l_orig_hash_value + 1;
1855
1856 IF l_orig_hash_value > l_hash_size THEN
1857 -- Don't need to check hash overflow here because the hash range
1858 -- for sure is greater than the number of columns.
1859 l_orig_hash_value := l_hash_base;
1860 END IF;
1861
1862 IF g_lot_ser_attr.exists(l_orig_hash_value) AND
1863 g_lot_ser_attr(l_orig_hash_value).column_name = p_input_string THEN
1864 EXIT;
1865 ELSIF NOT g_lot_ser_attr.exists(l_orig_hash_value) THEN
1866 EXIT;
1867 END IF;
1868 END LOOP;
1869
1870 l_return_hash_value := l_orig_hash_value;
1871
1872 ELSE
1873 l_return_hash_value := l_orig_hash_value;
1874 END IF;
1875
1876 RETURN l_return_hash_value;
1877
1878 END get_column_hash_value;
1879
1880 /*********************************************
1881 * Get default lot/serial attribute
1882 * This is used for inbound for new lot/serial
1883 *********************************************/
1884 PROCEDURE get_lot_ser_default_attribute(
1885 p_organization_id IN NUMBER
1886 , p_inventory_item_id IN NUMBER
1887 , p_lot_serial IN VARCHAR2
1888 , p_lot_or_serial IN VARCHAR2
1889 ) IS
1890 l_table_name VARCHAR2(30);
1891 l_flex_name VARCHAR2(30);
1892
1893 l_attr_list INV_LOT_SEL_ATTR.lot_sel_attributes_tbl_type;
1894 l_null_attr INV_LOT_SEL_ATTR.lot_sel_attributes_tbl_type;
1895 l_context_code varchar2(30);
1896 l_count number;
1897 l_return_status VARCHAR2(1);
1898 l_msg_count NUMBER;
1899 l_msg_data VARCHAR2(2000);
1900 l_hash_value NUMBER;
1901 l_context_column VARCHAR2(50);
1902 BEGIN
1903 IF l_debug = 1 THEN
1904 trace('In get_lot_ser_default_attribute ');
1905 trace(' p_inventory_item_id='||p_inventory_item_id||',p_lot_serial='||p_lot_serial||',p_lot_or_serial='||p_lot_or_serial);
1906 END IF;
1907 IF p_lot_or_serial = 'LOT' THEN
1908 l_table_name := 'MTL_LOT_NUMBERS';
1909 l_flex_name := 'Lot Attributes';
1910 l_context_column := 'LOT_ATTRIBUTE_CATEGORY';
1911 ELSIF p_lot_or_serial = 'SERIAL' THEN
1912 l_table_name := 'MTL_SERIAL_NUMBERS';
1913 l_flex_name := 'Serial Attributes';
1914 l_context_column := 'SERIAL_ATTRIBUTE_CATEGORY';
1915 END IF;
1916
1917 g_lot_ser_attr.delete;
1918
1919 INV_LOT_SEL_ATTR.get_context_code(
1920 context_value => l_context_code
1921 , org_id => p_organization_id
1922 , item_id => p_inventory_item_id
1923 , flex_name => l_flex_name);
1924 IF l_debug = 1 THEN
1925 trace(' Got context_code = '||l_context_code);
1926 END IF;
1927 INV_LOT_SEL_ATTR.get_default(
1928 x_attributes_default => l_attr_list
1929 , x_attributes_default_count => l_count
1930 , x_return_status => l_return_status
1931 , x_msg_count => l_msg_count
1932 , x_msg_data => l_msg_data
1933 , p_table_name => l_table_name
1934 , p_attributes_name => l_flex_name
1935 , p_inventory_item_id => p_inventory_item_id
1936 , p_organization_id => p_organization_id
1937 --, p_lot_serial_number => p_lot_serial
1938 , p_lot_serial_number => null
1939 , p_attributes => l_null_attr);
1940
1941 IF l_return_status <> 'S' THEN
1942 IF l_debug = 1 THEN
1943 trace('Error in INV_LOT_SEL_ATTR.get_default,msg_data='||l_msg_data);
1944 END IF;
1945 RETURN ;
1946 END IF;
1947 FOR i IN 1..l_attr_list.count Loop
1948 g_lot_ser_attr(get_column_hash_value(l_attr_list(i).COLUMN_NAME)) := l_attr_list(i);
1949 END LOOP;
1950
1951 g_lot_ser_attr(get_column_hash_value(l_context_column)).COLUMN_VALUE := l_context_code;
1952
1953 EXCEPTION
1954 WHEN others THEN
1955 IF l_debug = 1 THEN
1956 trace('Error in get_lot_ser_default_attribute');
1957 trace('ERROR Code ='||SQLCODE);
1958 trace('ERROR Message='||SQLERRM);
1959 END IF;
1960 END get_lot_ser_default_attribute;
1961
1962 /***************************************
1963 * Function to get default values
1964 * for a given column
1965 **************************************/
1966 FUNCTION get_column_default_value(p_column_name IN VARCHAR2)
1967 RETURN VARCHAR2 IS
1968 l_hash_value NUMBER;
1969 BEGIN
1970 l_hash_value := get_column_hash_value(p_column_name);
1971 IF g_lot_ser_attr.exists(l_hash_value) THEN
1972 RETURN g_lot_ser_attr(l_hash_value).column_value;
1973 ELSE
1974 RETURN NULL;
1975 END IF;
1976 END get_column_default_value;
1977 /********************************************
1978 Procedure to query outbound eligible material
1979 *******************************************/
1980 PROCEDURE query_outbound_material(
1981 x_return_status OUT NOCOPY VARCHAR2
1982 , p_organization_id IN NUMBER
1983 , p_organization_code IN VARCHAR2
1984 , p_subinventory_code IN VARCHAR2 DEFAULT NULL
1985 , p_locator_id IN VARCHAR2 DEFAULT NULL
1986 , p_locator IN VARCHAR2 DEFAULT NULL
1987 , p_inventory_item_id IN NUMBER DEFAULT NULL
1988 , p_item IN VARCHAR2 DEFAULT NULL
1989 , p_from_lpn_id IN NUMBER DEFAULT NULL
1990 , p_project_id IN NUMBER DEFAULT NULL
1991 , p_project IN VARCHAR2 DEFAULT NULL
1992 , p_task_id IN NUMBER DEFAULT NULL
1993 , p_task IN VARCHAR2 DEFAULT NULL
1994 , p_delivery_id IN NUMBER DEFAULT NULL
1995 , p_delivery IN VARCHAR2 DEFAULT NULL
1996 , p_order_header_id IN NUMBER DEFAULT NULL
1997 , p_order_number IN VARCHAR2 DEFAULT NULL
1998 , p_order_type IN VARCHAR2 DEFAULT NULL
1999 , p_carrier_id IN NUMBER DEFAULT NULL
2000 , p_carrier IN VARCHAR2 DEFAULT NULL
2001 , p_trip_id IN NUMBER DEFAULT NULL
2002 , p_trip IN VARCHAR2 DEFAULT NULL
2003 , p_delivery_state IN VARCHAR2 DEFAULT NULL
2004 , p_customer_id IN NUMBER DEFAULT NULL
2005 , p_customer IN VARCHAR2 DEFAULT NULL
2006 ) IS
2007
2008 -- Bug 4237771, performance
2009 -- Use different delivery cursor to use index as much as possible
2010
2011 -- Use this cursor when p_delivery_is is not null
2012 CURSOR l_delivery_cur_del IS
2013 SELECT wnd.delivery_id, wnd.name,
2014 nvl(p_delivery_state, wms_consolidation_pub.is_delivery_consolidated(wnd.delivery_id, p_organization_id, p_subinventory_code, p_locator_id))
2015 FROM wsh_new_deliveries_ob_grp_v wnd
2016 WHERE wnd.organization_id = p_organization_id
2017 AND wnd.delivery_id = p_delivery_id
2018 AND ((p_trip_id IS NULL) OR
2019 (p_trip_id IS NOT NULL AND wnd.delivery_id IN
2020 (select wdl.delivery_id from wsh_delivery_legs_ob_grp_v wdl, wsh_trip_stops_ob_grp_v wts
2021 where wdl.pick_up_stop_id = wts.stop_id
2022 and wts.trip_id = p_trip_id)))
2023 AND ((p_delivery_state IS NULL) OR
2024 (p_delivery_state IS NOT NULL AND
2025 wms_consolidation_pub.is_delivery_consolidated(wnd.delivery_id, p_organization_id, p_subinventory_code, p_locator_id) = p_delivery_state));
2026
2027 -- Use this cursor when p_delivery_is is null but p_trip_id is not null
2028 CURSOR l_delivery_cur_trip IS
2029 SELECT wnd.delivery_id, wnd.name,
2030 nvl(p_delivery_state, wms_consolidation_pub.is_delivery_consolidated(wnd.delivery_id, p_organization_id, p_subinventory_code, p_locator_id))
2031 FROM wsh_new_deliveries_ob_grp_v wnd
2032 WHERE wnd.organization_id = p_organization_id
2033 AND wnd.delivery_id IN
2034 (select wdl.delivery_id from wsh_delivery_legs_ob_grp_v wdl, wsh_trip_stops_ob_grp_v wts
2035 where wdl.pick_up_stop_id = wts.stop_id
2036 and wts.trip_id = p_trip_id)
2037 AND ((p_delivery_state IS NULL) OR
2038 (p_delivery_state IS NOT NULL AND
2039 wms_consolidation_pub.is_delivery_consolidated(wnd.delivery_id, p_organization_id, p_subinventory_code, p_locator_id) = p_delivery_state));
2040
2041 -- Use this cursor when both p_delivery_id and p_trip_id are null
2042 -- Then have to use this expensive cursor
2043 CURSOR l_delivery_cur_exp IS
2044 SELECT wnd.delivery_id, wnd.name,
2045 nvl(p_delivery_state, wms_consolidation_pub.is_delivery_consolidated(wnd.delivery_id, p_organization_id, p_subinventory_code, p_locator_id))
2046 FROM wsh_new_deliveries_ob_grp_v wnd
2047 WHERE wnd.organization_id = p_organization_id
2048 AND ((p_delivery_state IS NULL) OR
2049 (p_delivery_state IS NOT NULL AND
2050 wms_consolidation_pub.is_delivery_consolidated(wnd.delivery_id, p_organization_id, p_subinventory_code, p_locator_id) = p_delivery_state));
2051
2052 CURSOR l_wdd_cur(pl_delivery_id NUMBER) IS
2053 SELECT wdd1.organization_id
2054 ,wdd1.subinventory
2055 ,wdd1.locator_id
2056 ,wdd1.project_id
2057 ,wdd1.task_id
2058 ,wdd1.inventory_item_id
2059 ,wdd1.revision
2060 ,wdd1.lot_number
2061 ,wdd1.requested_quantity_uom uom
2062 --,sum(wdd1.requested_quantity) quantity
2063 ,sum(wdd1.picked_quantity) quantity --Changes for 10281724
2064 ,wdd1.requested_quantity_uom2 uom2 --INVCONV KKILLAMS
2065 --,sum(wdd1.requested_quantity2) quantity2 --INVCONV KKILLAMS
2066 ,sum(wdd1.picked_quantity2) quantity2 --Changes for 10281724
2067 ,wdd2.lpn_id
2068 ,wda.delivery_id
2069 -- Bug 5121507, Get carrier in the order of Trip->Delivery->Delivery Detail
2070 --,nvl(wdd1.carrier_id, wnd.carrier_id) carrier_id
2071 ,nvl(wt.carrier_id, nvl(wnd.carrier_id, wdd1.carrier_id)) carrier_id
2072 ,wdd1.source_header_id
2073 ,wdd1.source_header_number
2074 ,wdd1.source_line_id
2075 ,wdd1.source_line_number
2076 ,nvl(wdd1.customer_id, wnd.customer_id)
2077 ,wdd1.ship_to_location_id
2078 ,wdd1.ship_set_id
2079 ,wdd1.top_model_line_id
2080 FROM wsh_delivery_details_ob_grp_v wdd1, wsh_delivery_details_ob_grp_v wdd2
2081 ,wsh_delivery_assignments_v wda, wsh_new_deliveries_ob_grp_v wnd
2082 -- Bug 5121507
2083 , wsh_delivery_legs wdl
2084 , wsh_trip_stops wts
2085 , wsh_trips wt
2086 WHERE wda.delivery_detail_id = wdd1.delivery_detail_id
2087 AND wda.parent_delivery_detail_id = wdd2.delivery_detail_id
2088 AND wnd.delivery_id (+) = wda.delivery_id
2089 AND wdd1.released_status = 'Y'
2090 AND wdd2.lpn_id IS NOT NULL
2091 AND wdd2.released_status = 'X' -- For LPN reuse ER : 6845650
2092 AND wdd2.lpn_id IN
2093 (select lpn_id from wms_license_plate_numbers
2094 where organization_id = p_organization_id
2095 and lpn_context = 11)
2096 -- restriction from find window
2097 AND wdd1.organization_id = p_organization_id
2098 AND wdd1.subinventory = nvl(p_subinventory_code, wdd1.subinventory)
2099 AND nvl(wdd1.locator_id, -999) = nvl(p_locator_id, nvl(wdd1.locator_id, -999))
2100 AND ((wdd2.lpn_id = nvl(p_from_lpn_id, wdd2.lpn_id)) OR
2101 (wdd2.lpn_id IN (select lpn_id from wms_license_plate_numbers where outermost_lpn_id = p_from_lpn_id)))
2102 AND wdd1.inventory_item_id = nvl(p_inventory_item_id, wdd1.inventory_item_id )
2103 AND nvl(wdd1.project_id, -9999) = nvl(p_project_id,nvl(wdd1.project_id, -9999))
2104 AND nvl(wdd1.task_id, -9999) = nvl(p_task_id,nvl(wdd1.task_id, -9999))
2105 AND ((pl_delivery_id IS NULL) OR
2106 (pl_delivery_id IS NOT NULL AND wda.delivery_id = pl_delivery_id))
2107 AND wdd1.source_header_number = nvl(p_order_number, wdd1.source_header_number)
2108 AND wdd1.source_header_type_name = nvl(p_order_type, wdd1.source_header_type_name)
2109 -- Bug 5121507
2110 --AND nvl(nvl(wdd1.carrier_id, wnd.carrier_id), -9999) = nvl(p_carrier_id, nvl(nvl(wdd1.carrier_id, wnd.carrier_id), -9999))
2111 AND nvl(nvl(wt.carrier_id, nvl(wnd.carrier_id,wdd1.carrier_id)), -9999) = nvl(p_carrier_id, nvl(nvl(wt.carrier_id, nvl(wnd.carrier_id,wdd1.carrier_id)), -9999))
2112 AND wdd1.customer_id = nvl(p_customer_id, wdd1.customer_id)
2113 -- Bug 5121507
2114 AND wnd.delivery_id = wdl.delivery_id(+)
2115 AND wdl.pick_up_stop_id = wts.stop_id (+)
2116 AND wts.trip_id = wt.trip_id (+)
2117 GROUP BY wdd1.organization_id
2118 ,wdd1.subinventory
2119 ,wdd1.locator_id
2120 ,wdd1.project_id
2121 ,wdd1.task_id
2122 ,wdd1.inventory_item_id
2123 ,wdd1.revision
2124 ,wdd1.lot_number
2125 ,wdd1.requested_quantity_uom
2126 ,wdd1.requested_quantity_uom2 --INVCONV KKILLAMS
2127 ,wdd2.lpn_id
2128 ,wda.delivery_id
2129 -- Bug 5121507
2130 --,nvl(wdd1.carrier_id, wnd.carrier_id)
2131 ,nvl(wt.carrier_id, nvl(wnd.carrier_id,wdd1.carrier_id))
2132 ,wdd1.source_header_id
2133 ,wdd1.source_header_number
2134 ,wdd1.source_line_id
2135 ,wdd1.source_line_number
2136 ,nvl(wdd1.customer_id, wnd.customer_id)
2137 ,wdd1.ship_to_location_id
2138 ,wdd1.ship_set_id
2139 ,wdd1.top_model_line_id;
2140
2141 l_progress VARCHAR2(10);
2142
2143 l_material_rec WMS_PACKING_MATERIAL_GTEMP%ROWTYPE;
2144 l_null_material WMS_PACKING_MATERIAL_GTEMP%ROWTYPE;
2145 l_del_count NUMBER;
2146 l_rec_count NUMBER;
2147
2148 l_delivery_id NUMBER;
2149 l_delivery VARCHAR2(30);
2150 l_delivery_state VARCHAR2(1);
2151 l_delivery_req BOOLEAN;
2152 l_top_model_line_id NUMBER;
2153 BEGIN
2154 l_progress := '000';
2155
2156 IF l_debug = 1 THEN
2157 trace('In WMS Packing Workbench, package header ='|| g_pkg_version);
2158 trace('Query outbound eligible material with parameters :');
2159 trace(' p_organization_id='||p_organization_id||', p_organization_code='||p_organization_code);
2160 trace(' p_subinventory_code='||p_subinventory_code);
2161 trace(' p_locator_id='||p_locator_id||', p_locator='||p_locator);
2162 trace(' p_inventory_item_id='||p_inventory_item_id||',p_item='||p_item);
2163 trace(' p_from_lpn_id='||p_from_lpn_id);
2164 trace(' p_project_id='||p_project_id||', p_project='||p_project);
2165 trace(' p_task_id='||p_task_id||',p_task='||p_task);
2166 trace(' p_delivery_id='||p_delivery_id||', p_delivery='||p_delivery);
2167 trace(' p_order_header_id='||p_order_header_id);
2168 trace(' p_order_number='||p_order_number||', p_order_type='||p_order_type);
2169 trace(' p_carrier_id='||p_carrier_id||', p_carrier='||p_carrier);
2170 trace(' p_trip_id='||p_trip_id||', p_trip='||p_trip);
2171 trace(' p_delivery_state='||p_delivery_state);
2172 trace(' p_customer_id='||p_customer_id||', p_customer='||p_customer);
2173 END IF;
2174
2175 x_return_status := fnd_api.G_RET_STS_SUCCESS;
2176 l_del_count := 0;
2177 l_rec_count := 0;
2178
2179 delete from wms_packing_material_gtemp;
2180 --delete from wms_packing_material_temp;
2181
2182 l_progress := '010';
2183 IF (p_delivery_id IS NOT NULL) OR (p_trip_id IS NOT NULL) OR (p_delivery_state IS NOT NULL) THEN
2184 IF l_debug = 1 THEN
2185 trace(' Delivery requirement specified');
2186 END IF;
2187 l_delivery_req := true;
2188
2189 -- Bug 4237771, open l_delivery_cur_* conditionally to improve performance
2190 -- If either p_delivery_id or p_trip_is is not null, then it can use unique index on wnd.delivery_id
2191 -- Otherwise, have to use a more expensive cursor
2192 IF (p_delivery_id IS NOT NULL) THEN
2193 OPEN l_delivery_cur_del;
2194 ELSIF (p_trip_id IS NOT NULL) THEN
2195 OPEN l_delivery_cur_trip;
2196 ELSE
2197 OPEN l_delivery_cur_exp;
2198 END IF;
2199 ELSE
2200 l_delivery_req := false;
2201 l_delivery_id := null;
2202 END IF;
2203
2204 l_progress := '020';
2205
2206 LOOP -- Loop for delivery
2207 l_delivery_id := null;
2208 l_delivery := null;
2209 IF l_delivery_req THEN
2210 l_progress := '030'||'-'||l_del_count;
2211 -- Bug 4237771
2212 IF l_delivery_cur_del%ISOPEN THEN
2213 FETCH l_delivery_cur_del INTO
2214 l_delivery_id, l_delivery, l_delivery_state;
2215 IF l_delivery_cur_del%NOTFOUND THEN
2216 --trace('No more delivery found in l_delivery_cur_del');
2217 EXIT;
2218 END IF;
2219 ELSIF l_delivery_cur_trip%ISOPEN THEN
2220 FETCH l_delivery_cur_trip INTO
2221 l_delivery_id, l_delivery, l_delivery_state;
2222 IF l_delivery_cur_trip%NOTFOUND THEN
2223 --trace('No more delivery found in l_delivery_cur_trip');
2224 EXIT;
2225 END IF;
2226 ELSIF l_delivery_cur_exp%ISOPEN THEN
2227 FETCH l_delivery_cur_exp INTO
2228 l_delivery_id, l_delivery, l_delivery_state;
2229 IF l_delivery_cur_exp%NOTFOUND THEN
2230 --trace('No more delivery found in l_delivery_cur_exp');
2231 EXIT;
2232 END IF;
2233 END IF;
2234
2235 l_del_count := l_del_count + 1;
2236 END IF;
2237
2238 OPEN l_wdd_cur(l_delivery_id);
2239 LOOP
2240 l_material_rec := l_null_material;
2241 FETCH l_wdd_cur INTO
2242 l_material_rec.organization_id
2243 ,l_material_rec.subinventory
2244 ,l_material_rec.locator_id
2245 ,l_material_rec.project_id
2246 ,l_material_rec.task_id
2247 ,l_material_rec.inventory_item_id
2248 ,l_material_rec.revision
2249 ,l_material_rec.lot_number
2250 ,l_material_rec.uom
2251 ,l_material_rec.quantity
2252 ,l_material_rec.secondary_uom_code --invconv kkillams
2253 ,l_material_rec.secondary_quantity --invconv kkillams
2254 ,l_material_rec.lpn_id
2255 ,l_material_rec.delivery_id
2256 ,l_material_rec.carrier_id
2257 ,l_material_rec.order_header_id
2258 ,l_material_rec.order_number
2259 ,l_material_rec.order_line_id
2260 ,l_material_rec.order_line_num
2261 ,l_material_rec.customer_id
2262 ,l_material_rec.ship_to_location_id
2263 ,l_material_rec.ship_set_id
2264 ,l_top_model_line_id;
2265
2266 IF l_wdd_cur%NOTFOUND THEN
2267 --trace('no more WDD found for delivery '||l_delivery_id);
2268 EXIT;
2269 END IF;
2270
2271
2272 /* Comment out the following debug to reduce logging
2273 IF l_debug = 1 THEN
2274 trace(' Value of l_material_rec.order_line_id :'|| l_material_rec.order_line_id);
2275 trace(' Value of order_line_num :'|| l_material_rec.order_line_num);
2276 trace(' Value of l_material_rec.order_header_id :'||l_material_rec.order_header_id);
2277
2278 trace(' Value of l_material_rec.order_number :'||l_material_rec.order_number);
2279
2280 END IF;
2281 */
2282
2283
2284
2285
2286
2287 l_rec_count := l_rec_count +1;
2288 l_progress := '040'||'-'||l_rec_count;
2289 -- Derive column values
2290 -- Organization_code
2291 l_material_rec.organization_code := p_organization_code;
2292 l_progress := '041'||'-'||l_rec_count;
2293 -- Locator
2294 IF p_locator IS NOT NULL THEN
2295 l_material_rec.locator := p_locator;
2296 ELSIF l_material_rec.locator_id IS NOT NULL THEN
2297 BEGIN
2298 SELECT
2299 inv_project.get_locsegs(l_material_rec.locator_id,l_material_rec.organization_id) /*bug344642 concatenated_segments*/ INTO l_material_rec.locator
2300 FROM mtl_item_locations_kfv
2301 WHERE organization_id = l_material_rec.organization_id
2302 AND subinventory_code = l_material_rec.subinventory
2303 AND inventory_location_id = l_material_rec.locator_id;
2304 EXCEPTION
2305 WHEN NO_DATA_FOUND THEN
2306 IF l_debug = 1 THEN
2307 trace(' can not find locator name for loc_id '|| l_material_rec.locator_id);
2308 END IF;
2309 l_material_rec.locator := null;
2310 END;
2311 END IF;
2312 l_progress := '043'||'-'||l_rec_count;
2313 -- Project
2314 IF p_project IS NOT NULL THEN
2315 l_material_rec.project := p_project;
2316 ELSIF l_material_rec.project_id IS NOT NULL THEN
2317 BEGIN
2318 SELECT name INTO l_material_rec.project
2319 FROM pa_projects WHERE project_id = l_material_rec.project_id;
2320 EXCEPTION
2321 WHEN NO_DATA_FOUND THEN
2322 IF l_debug = 1 THEN
2323 trace(' can not find project name for project_id '|| l_material_rec.project_id);
2324 END IF;
2325 l_material_rec.project := null;
2326 END;
2327 END IF;
2328 l_progress := '045'||'-'||l_rec_count;
2329 -- Task
2330 IF l_material_rec.task_id IS NOT NULL THEN
2331 BEGIN
2332 SELECT task_number,task_name INTO l_material_rec.task_number, l_material_rec.task_name
2333 FROM pa_tasks
2334 WHERE project_id = l_material_rec.project_id
2335 AND task_id = l_material_rec.task_id;
2336 EXCEPTION
2337 WHEN NO_DATA_FOUND THEN
2338 IF l_debug = 1 THEN
2339 trace(' can not find task name for task_id '|| l_material_rec.task_id);
2340 END IF;
2341 l_material_rec.task_number := null;
2342 l_material_rec.task_name := null;
2343 END;
2344 END IF;
2345 l_progress := '047'||'-'||l_rec_count;
2346 -- Item
2347 IF p_item IS NOT NULL THEN
2348 l_material_rec.item := p_item;
2349 ELSIF l_material_rec.inventory_item_id IS NOT NULL THEN
2350 BEGIN
2351 SELECT concatenated_segments,description INTO l_material_rec.item, l_material_rec.item_description
2352 FROM mtl_system_items_kfv
2353 WHERE organization_id = l_material_rec.organization_id
2354 AND inventory_item_id = l_material_rec.inventory_item_id;
2355 EXCEPTION
2356 WHEN NO_DATA_FOUND THEN
2357 IF l_debug = 1 THEN
2358 trace(' can not find item for item_id '|| l_material_rec.inventory_item_id);
2359 END IF;
2360 l_material_rec.item := null;
2361 l_material_rec.item_description := null;
2362 END;
2363 END IF;
2364 l_progress := '049'||'-'||l_rec_count;
2365 -- LPN, Parent LPN, Outermost LPN
2366 IF l_material_rec.lpn_id IS NOT NULL THEN
2367 BEGIN
2368 SELECT lpn.license_plate_number, lpn.parent_lpn_id, pLpn.license_plate_number,
2369 lpn.outermost_lpn_id, oLpn.license_plate_number
2370 INTO l_material_rec.lpn, l_material_rec.parent_lpn_id, l_material_rec.parent_lpn,
2371 l_material_rec.outermost_lpn_id, l_material_rec.outermost_lpn
2372 FROM wms_license_plate_numbers lpn, wms_license_plate_numbers pLpn, wms_license_plate_numbers oLpn
2373 WHERE lpn.lpn_id = l_material_rec.lpn_id
2374 AND pLpn.lpn_id(+) = lpn.parent_lpn_id
2375 AND oLpn.lpn_id(+) = lpn.outermost_lpn_id;
2376 EXCEPTION
2377 WHEN NO_DATA_FOUND THEN
2378 IF l_debug = 1 THEN
2379 trace(' can not find lpn for lpn_id '|| l_material_rec.lpn_id);
2380 END IF;
2381 l_material_rec.lpn := null;
2382 l_material_rec.parent_lpn_id := null;
2383 l_material_rec.parent_lpn := null;
2384 l_material_rec.outermost_lpn_id := null;
2385 l_material_rec.outermost_lpn := null;
2386 END;
2387 END IF;
2388
2389 l_progress := '0411'||'-'||l_rec_count;
2390 -- Delivery
2391 IF l_delivery IS NOT NULL THEN
2392 l_material_rec.delivery := l_delivery;
2393 ELSIF l_material_rec.delivery_id IS NOT NULL THEN
2394 BEGIN
2395 SELECT name INTO l_material_rec.delivery
2396 FROM wsh_new_deliveries
2397 WHERE delivery_id = l_material_rec.delivery_id;
2398 EXCEPTION
2399 WHEN NO_DATA_FOUND THEN
2400 IF l_debug = 1 THEN
2401 trace(' can not find delivery name for delivery_id '||l_material_rec.delivery_id);
2402 END IF;
2403 l_material_rec.delivery := null;
2404 END;
2405 END IF;
2406
2407 l_progress := '0413'||'-'||l_rec_count;
2408
2409 -- Delivery State
2410 IF l_delivery_state IS NOT NULL THEN
2411 l_material_rec.delivery_completed := l_delivery_state;
2412 ELSIF l_material_rec.delivery_id IS NOT NULL THEN
2413 l_material_rec.delivery_completed := wms_consolidation_pub.is_delivery_consolidated(l_material_rec.delivery_id, p_organization_id, p_subinventory_code, p_locator_id);
2414 END IF;
2415
2416 l_progress := '0415'||'-'||l_rec_count;
2417 -- Trip
2418 IF p_trip IS NOT NULL THEN
2419 l_material_rec.trip := p_trip;
2420 ELSIF l_material_rec.delivery_id IS NOT NULL THEN
2421 IF l_debug = 1 THEN
2422 trace('delivery_id='||l_material_rec.delivery_id);
2423 END IF;
2424 BEGIN
2425 SELECT t.trip INTO l_material_rec.trip
2426 FROM(
2427 SELECT distinct wt.name trip
2428 FROM wsh_delivery_legs wdl, wsh_trip_stops wts, wsh_trips wt
2429 WHERE wdl.delivery_id = l_material_rec.delivery_id
2430 AND wts.stop_id = wdl.pick_up_stop_id
2431 AND wt.trip_id = wts.trip_id) t
2432 WHERE rownum <2;
2433 EXCEPTION
2434 WHEN no_data_found THEN
2435 /* Comment out the following debug to reduce logging
2436 IF l_debug = 1 THEN
2437 trace(' can not find trip name for delivery_id '||l_material_rec.delivery_id);
2438 END IF;
2439 */
2440 l_material_rec.trip := null;
2441 END;
2442 END IF;
2443
2444 l_progress := '0417'||'-'||l_rec_count;
2445
2446 -- Carrier
2447 IF p_carrier IS NOT NULL THEN
2448 l_material_rec.carrier := p_carrier;
2449 ELSIF l_material_rec.carrier_id IS NOT NULL THEN
2450 BEGIN
2451 SELECT carrier_name INTO l_material_rec.carrier
2452 FROM wsh_carriers_v
2453 WHERE carrier_id = l_material_rec.carrier_id;
2454 EXCEPTION
2455 WHEN NO_DATA_FOUND THEN
2456 IF l_debug = 1 THEN
2457 trace(' can not find carrier name for carrier_id '||l_material_rec.carrier_id);
2458 END IF;
2459 l_material_rec.carrier := null;
2460 END;
2461 END IF;
2462
2463 l_progress := '0419'||'-'||l_rec_count;
2464 -- Packing Instruction
2465 IF l_material_rec.order_line_id IS NOT NULL AND l_material_rec.order_header_id IS NOT NULL THEN
2466 BEGIN
2467 SELECT nvl(oeol.packing_instructions, oeoh.packing_instructions)
2468 INTO l_material_rec.packing_instruction
2469 FROM oe_order_headers_all oeoh, oe_order_lines_all oeol
2470 WHERE oeoh.header_id = oeol.header_id
2471 AND oeol.line_id = l_material_rec.order_line_id;
2472 EXCEPTION
2473 WHEN NO_DATA_FOUND THEN
2474 IF l_debug = 1 THEN
2475 trace(' can not find packing instruction for order_line_id '||l_material_rec.order_line_id);
2476 END IF;
2477 l_material_rec.packing_instruction := null;
2478 END;
2479 END IF;
2480
2481 l_progress := '0421'||'-'||l_rec_count;
2482 -- Customer
2483 -- Bug4579790
2484 IF l_material_rec.customer_id IS NOT NULL THEN
2485 BEGIN
2486 -- Bug 5363505
2487 -- the following query will return more than one row
2488 -- if there are more than one accounts for the party
2489 -- No need to join to hz_cust_accounts table
2490
2491 SELECT party.party_number, party.party_name
2492 INTO l_material_rec.customer_number, l_material_rec.customer_name
2493 FROM hz_parties party --, hz_cust_accounts cust_acct
2494 WHERE party.party_id = l_material_rec.customer_id;
2495 --AND cust_acct.party_id = party.party_id;
2496 EXCEPTION
2497 WHEN OTHERS THEN
2498 IF l_debug = 1 THEN
2499 trace(' can not find customer for customer_id '||l_material_rec.customer_id);
2500 END IF;
2501 l_material_rec.customer_number := null;
2502 l_material_rec.customer_name := null;
2503 END;
2504 END IF;
2505
2506 l_progress := '0423'||'-'||l_rec_count;
2507 -- Ship To Location
2508 IF l_material_rec.ship_to_location_id IS NOT NULL THEN
2509 --IF internal location IO
2510 BEGIN
2511 SELECT location_code INTO l_material_rec.ship_to_location
2512 FROM hr_locations_all
2513 WHERE location_id = l_material_rec.ship_to_location_id;
2514 EXCEPTION
2515 WHEN NO_DATA_FOUND THEN
2516 l_material_rec.ship_to_location := null;
2517 END;
2518
2519 BEGIN
2520 SELECT nvl(city, address1)||':'||to_char(location_id)
2521 INTO l_material_rec.ship_to_location
2522 FROM hz_locations
2523 WHERE location_id = l_material_rec.ship_to_location_id;
2524 EXCEPTION
2525 WHEN NO_DATA_FOUND THEN
2526 IF l_debug = 1 THEN
2527 trace(' can not find location for external location_id '||l_material_rec.ship_to_location_id);
2528 END IF;
2529 l_material_rec.ship_to_location := null;
2530 END;
2531 END IF;
2532
2533 -- PTO Flag
2534 IF l_top_model_line_id IS NOT NULL
2535 THEN
2536 BEGIN
2537 SELECT 'Y' INTO l_material_rec.pto_flag
2538 FROM dual
2539 WHERE exists (
2540 select 1 from oe_order_lines_all oel, oe_order_lines_all oel1
2541 where oel.inventory_item_id = l_material_rec.inventory_item_id
2542 and oel.top_model_line_id = l_top_model_line_id
2543 and oel1. inventory_item_id = oel.inventory_item_id
2544 and oel1.top_model_line_id = oel.top_model_line_id
2545 and (((oel.shippable_flag = 'Y' or oel.line_id = oel.top_model_line_id)
2546 AND (oel.ato_line_id <> oel.TOP_MODEL_LINE_ID OR oel.ato_line_id IS NULL))
2547 OR (oel1.ato_line_id is not null and oel1.line_id = oel1.top_model_line_id))
2548 );
2549 EXCEPTION
2550 WHEN no_data_found THEN
2551 l_material_rec.pto_flag := 'N';
2552 END;
2553 END IF;
2554
2555 -- Ship Set
2556 IF l_material_rec.ship_set_id IS NOT NULL THEN
2557 BEGIN
2558 SELECT set_name
2559 INTO l_material_rec.ship_set
2560 FROM oe_sets
2561 WHERE set_id = l_material_rec.ship_set_id;
2562 EXCEPTION
2563 WHEN no_data_found THEN
2564 IF l_debug =1 THEN
2565 trace(' can not find ship_set_name for ship_set_id '||l_material_rec.ship_set_id);
2566 END IF;
2567 l_material_rec.ship_set := null;
2568 END;
2569 END IF;
2570
2571 -- Insert into the global temp table for eligible material
2572 insert_material_rec(l_material_rec);
2573
2574 END LOOP; -- End WDD Loop
2575 CLOSE l_wdd_cur;
2576
2577 IF NOT l_delivery_req THEN
2578 EXIT;
2579 END IF;
2580
2581 END LOOP; -- End delivery loop
2582 IF l_debug = 1 THEN
2583 trace('Found total '||l_rec_count||' WDD records for total '||l_del_count||' deliveries');
2584 END IF;
2585
2586 IF l_delivery_cur_del%ISOPEN THEN
2587 CLOSE l_delivery_cur_del;
2588 END IF;
2589
2590 IF l_delivery_cur_trip%ISOPEN THEN
2591 CLOSE l_delivery_cur_trip;
2592 END IF;
2593
2594 IF l_delivery_cur_exp%ISOPEN THEN
2595 CLOSE l_delivery_cur_exp;
2596 END IF;
2597
2598 --insert into wms_packing_material_temp value (select * from wms_packing_material_gtemp);
2599 --commit;
2600
2601
2602 EXCEPTION
2603 WHEN others THEN
2604 IF l_debug = 1 THEN
2605 trace('Error in query_outbound_material(), l_progress='||l_progress);
2606 trace('ERROR CODE = ' || SQLCODE);
2607 trace('ERROR MESSAGE = ' || SQLERRM);
2608 END IF;
2609 x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR;
2610 END query_outbound_material;
2611
2612
2613 FUNCTION get_kit_list(
2614 p_organization_id IN NUMBER
2615 ,p_item_id IN NUMBER
2616 ,p_existing_kit IN VARCHAR2 DEFAULT 'N') RETURN kit_tbl_type IS
2617
2618 l_kit_list kit_tbl_type;
2619 l_kit_rec kit_rec_type;
2620 l_count NUMBER;
2621
2622 CURSOR kit_exist_cur IS
2623 SELECT distinct kit_item_id, top_model_line_id, 'Y','N' identified_flag
2624 /*is_kit_identified(kit_item_id) identified_flag*/
2625 FROM wms_packing_kitting_gtemp
2626 WHERE nvl(completed_flag,'N') <> 'Y'
2627 ORDER BY identified_flag desc;
2628
2629 --in the cursor kit_new_cu We did not use join with WPKG table
2630 -- since we need to know whether new scanned item IS UNIQUE across ALL the possible
2631 --kits IN the list OF eligible matrl, rather we will do programatically
2632 CURSOR kit_new_cur IS
2633 SELECT oel.INVENTORY_ITEM_id, oel.top_model_line_id, 'N', 'N'
2634 FROM oe_order_lines_all oel
2635 WHERE oel.line_id = oel.top_model_line_id
2636 AND oel.ato_line_id IS NULL
2637 AND oel.top_model_line_id in (
2638 select oel1.top_model_line_id
2639 from oe_order_lines_all oel1,oe_order_lines_all oel2
2640 where oel1.inventory_item_id = p_item_id
2641 and oel2. inventory_item_id = oel1.inventory_item_id
2642 and oel2.top_model_line_id = oel1.top_model_line_id
2643 AND oel1.line_id = oel2.line_id --bug 3458361
2644 and (((oel1.shippable_flag = 'Y') AND (oel1.line_id <> oel1.TOP_MODEL_LINE_ID) and (oel1.ato_line_id is null))
2645 OR (oel2.ato_line_id is not null and oel2.line_id = oel2.top_model_line_id))
2646 )
2647 AND exists (
2648 select 1 from WMS_PACKING_MATERIAL_GTEMP wpmg, oe_order_lines_all oel1
2649 where WPMG.order_header_id = oel1.header_id
2650 AND wpmg.order_line_id = oel1.line_id
2651 AND wpmg.inventory_item_id = p_item_id
2652 AND wpmg.inventory_item_id = oel1.inventory_item_id
2653 AND oel.top_model_line_id = oel1.top_model_line_id
2654 AND oel.header_id = oel1.header_id);
2655
2656
2657 l_item_in_existing_kit NUMBER;
2658
2659
2660 BEGIN
2661 l_kit_list.DELETE;
2662 l_count := 0;
2663
2664 -- Get existing kit
2665 OPEN kit_exist_cur;
2666 LOOP
2667 FETCH kit_exist_cur INTO l_kit_rec;
2668 IF kit_exist_cur%notfound THEN
2669 CLOSE kit_exist_cur;
2670 EXIT;
2671 END IF;
2672 l_count := l_count + 1;
2673 l_kit_list(l_count):= l_kit_rec;
2674 END LOOP;
2675 IF l_debug = 1 THEN
2676 trace('Found '||l_kit_list.COUNT||' existing kit');
2677 END IF;
2678
2679
2680 IF Nvl(p_existing_kit,'N') = 'N' THEN
2681 -- Get new kit
2682
2683 g_kit_count_current_comp := 0;
2684
2685 OPEN kit_new_cur;
2686 LOOP
2687 FETCH kit_new_cur INTO l_kit_rec;
2688 IF kit_new_cur%notfound THEN
2689 CLOSE kit_new_cur;
2690 EXIT;
2691
2692 END IF;
2693
2694 l_item_in_existing_kit := 0;
2695
2696 BEGIN
2697 select 1 INTO l_item_in_existing_kit FROM dual
2698 WHERE exists
2699 (SELECT 1 from WMS_PACKING_KITTING_GTEMP WPKG
2700 where WPKG.top_model_line_id = l_kit_rec.top_model_line_id);
2701
2702 EXCEPTION
2703 WHEN no_data_found THEN
2704 l_item_in_existing_kit := 0;
2705 WHEN too_many_rows THEN
2706 l_item_in_existing_kit := 1;
2707 END;
2708
2709 IF l_debug = 1 THEN
2710 trace('Current Item is in the list of existing kits (1:YES)'||l_item_in_existing_kit);
2711 END IF;
2712
2713 --Add only new kits retrieved by query into the
2714 --wpkg table, We did not use join with WPKG table
2715 --IN the CURSOR since we need to know whether new
2716 --scanned item IS UNIQUE across ALL the possible
2717 --kits IN the list OF eligible matrl
2718
2719 IF l_item_in_existing_kit <> 1 then
2720 l_count := l_count+1;
2721 l_kit_list(l_count):=l_kit_rec;
2722 END IF;
2723
2724 g_kit_count_current_comp := g_kit_count_current_comp +1;
2725
2726 END LOOP;
2727
2728
2729 END IF;
2730
2731
2732 IF l_debug = 1 THEN
2733 trace('Total '||g_kit_count_current_comp||' kits for CURRENT item');
2734 trace('Found total unique '||l_kit_list.COUNT||' kits');
2735 END IF;
2736 RETURN l_kit_list;
2737 EXCEPTION
2738 WHEN others THEN
2739 IF l_debug = 1 THEN
2740 trace('ERROR CODE = ' || SQLCODE);
2741 trace('ERROR MESSAGE = ' || SQLERRM);
2742 END IF;
2743 RAISE fnd_api.G_EXC_UNEXPECTED_ERROR;
2744
2745 END get_kit_list;
2746
2747 PROCEDURE insert_kit_info(
2748 p_kit_item_id IN NUMBER
2749 , p_component_item_id IN NUMBER
2750 , p_top_model_line_id IN NUMBER
2751 , p_packed_qty IN NUMBER
2752 , p_disp_packed_qty IN VARCHAR2
2753 ) IS
2754
2755 CURSOR kit_component_cur IS
2756 SELECT msi.concatenated_segments ITEM,
2757 msi.inventory_item_id ITEM_ID,
2758 --round(oel.ordered_quantity/oel1.ordered_quantity) bom_qty,
2759 oel.ordered_quantity ORD_QTY,
2760 OEL.top_model_LINE_ID
2761 FROM oe_order_lines_all oel, mtl_system_items_kfv msi, oe_order_lines_all oel1
2762 WHERE oel.inventory_item_id = msi.inventory_item_id
2763 AND oel.ship_from_org_id = msi.organization_id
2764 AND oel1.inventory_item_id = msi.inventory_item_id
2765 AND oel1.ship_from_org_id = msi.organization_id
2766 AND oel.top_model_line_id = p_top_model_line_id
2767 AND oel1.top_model_line_id = oel.top_model_line_id
2768 AND oel1.line_id = oel.line_id --bug 3458361
2769 AND (((oel.shippable_flag = 'Y' or oel.line_id = oel.top_model_line_id)
2770 AND (oel.ato_line_id <> oel.TOP_MODEL_LINE_ID OR oel.ato_line_id IS NULL))
2771 OR (oel1.ato_line_id is not null and oel1.line_id = oel1.top_model_line_id))
2772 ORDER BY oel.top_model_line_id,oel.shippable_flag;
2773
2774 l_kit_rec wms_packing_kitting_gtemp%ROWTYPE;
2775 l_kit_item_id NUMBER;
2776 l_kit_oqty NUMBER;
2777 BEGIN
2778 IF l_debug = 1 THEN
2779 trace('In insert_kit_info, p_kit_item_id='||p_kit_item_id||',p_component_item_id='||p_component_item_id);
2780 trace(' p_top_model_line_id='||p_top_model_line_id||',p_packed_qty='||p_packed_qty||',p_disp_packed_qty='||p_disp_packed_qty);
2781 END IF;
2782 OPEN kit_component_cur;
2783 -- get kit information
2784 FETCH kit_component_cur INTO
2785 l_kit_rec.item, l_kit_rec.kit_item_id
2786 , l_kit_rec.order_qty
2787 ,l_kit_rec.top_model_line_id;
2788 IF kit_component_cur%NOTFOUND THEN
2789
2790 IF l_debug = 1 THEN
2791 trace('No Kit info found for top_model_line_id '||p_top_model_line_id);
2792 END IF;
2793 CLOSE kit_component_cur;
2794 RETURN;
2795 END IF;
2796 l_kit_rec.component_item_id := null;
2797 l_kit_rec.packed_qty := null;
2798 l_kit_rec.packed_qty_disp := null;
2799 l_kit_rec.completed_flag := 'N';
2800
2801 INSERT INTO wms_packing_kitting_gtemp
2802 ( ITEM
2803 , kit_item_id
2804 , component_item_id
2805 , top_model_line_id
2806 , BOM_QTY
2807 , ORDER_QTY
2808 , PACKED_QTY
2809 , packed_qty_disp
2810 , completed_flag) VALUES
2811 (l_kit_rec.item
2812 ,l_kit_rec.kit_item_id
2813 ,l_kit_rec.component_item_id
2814 ,l_kit_rec.top_model_line_id
2815 ,1
2816 ,l_kit_rec.ORDER_QTY
2817 ,l_kit_rec.PACKED_QTY
2818 ,l_kit_rec.packed_qty_disp
2819 ,l_kit_rec.completed_flag);
2820
2821 l_kit_oqty := l_kit_rec.ORDER_QTY;
2822
2823 IF l_debug = 1 THEN
2824 trace('Inserted kit info for kit_item_id '||l_kit_rec.kit_item_id);
2825 END IF;
2826 l_kit_item_id := l_kit_rec.kit_item_id;
2827 -- Loop to insert component information
2828 LOOP
2829 FETCH kit_component_cur INTO
2830 l_kit_rec.item, l_kit_rec.component_item_id
2831 , l_kit_rec.order_qty
2832 ,l_kit_rec.top_model_line_id;
2833 IF kit_component_cur%NOTFOUND THEN
2834 IF l_debug = 1 THEN
2835 trace('No more component info found for top_model_line_id '||p_top_model_line_id);
2836 END IF;
2837 CLOSE kit_component_cur;
2838 EXIT;
2839 END IF;
2840 l_kit_rec.kit_item_id := l_kit_item_id;
2841 IF l_kit_rec.component_item_id = p_component_item_id THEN
2842 --condiiton "p_packed_qty
2843 -- is notl NULL" is added so that while inserting mutiple
2844 -- new kits for the a scanned components, we do not
2845 -- UPDATE the qty multiple times, we insert NULL qty for
2846 --ALL component under multiple kit AND THEN UPDATE based
2847 -- ON packed qty
2848 IF p_packed_qty IS NOT NULL THEN
2849 l_kit_rec.packed_qty := p_packed_qty;
2850 END IF;
2851 l_kit_rec.packed_qty_disp := p_disp_packed_qty;
2852 ELSE
2853 l_kit_rec.packed_qty := null;
2854 l_kit_rec.packed_qty_disp := null;
2855 END IF;
2856 l_kit_rec.completed_flag := 'N';
2857
2858
2859 INSERT INTO wms_packing_kitting_gtemp
2860 ( ITEM
2861 , kit_item_id
2862 , component_item_id
2863 , top_model_line_id
2864 , BOM_QTY
2865 , ORDER_QTY
2866 , PACKED_QTY
2867 , packed_qty_disp
2868 , completed_flag) VALUES
2869 (l_kit_rec.item
2870 ,l_kit_rec.kit_item_id
2871 ,l_kit_rec.component_item_id
2872 ,l_kit_rec.top_model_line_id
2873 ,(l_kit_rec.order_qty/l_kit_oqty) -- Cmp BOM = Cmp_Order_Qty/Kit_Order_Qty
2874 ,l_kit_rec.ORDER_QTY
2875 ,l_kit_rec.PACKED_QTY
2876 ,l_kit_rec.packed_qty_disp
2877 ,l_kit_rec.completed_flag);
2878
2879 IF l_debug = 1 THEN
2880 trace('Inserted component info for component_item_id '||l_kit_rec.component_item_id);
2881 END IF;
2882 END LOOP;
2883 END insert_kit_info;
2884
2885 FUNCTION get_kit_component_list
2886 (p_kit_item_id IN NUMBER
2887 ,p_top_model_line_id IN NUMBER
2888 ,p_exclude_item_id IN NUMBER) RETURN kit_component_tbl_type IS
2889
2890 CURSOR component_cur IS
2891 SELECT kit_item_id
2892 ,component_item_id
2893 ,packed_qty
2894 ,packed_qty_disp
2895 FROM wms_packing_kitting_gtemp
2896 WHERE kit_item_id = p_kit_item_id
2897 AND top_model_line_id = p_top_model_line_id
2898 AND component_item_id IS NOT NULL
2899 AND component_item_id <> p_exclude_item_id;
2900
2901 l_comp_rec kit_component_rec_type;
2902 l_comp_tbl kit_component_tbl_type;
2903 l_rec_count NUMBER := 0;
2904 BEGIN
2905 FOR l_comp_rec IN component_cur LOOP
2906 l_rec_count := l_rec_count + 1;
2907 l_comp_tbl(l_rec_count) := l_comp_rec;
2908 END LOOP;
2909 IF l_debug = 1 THEN
2910 trace('Got '||l_comp_tbl.count||' component for kit '||p_kit_item_id||' top_model '|| p_top_model_line_id
2911 || ', exclude_item '|| p_exclude_item_id);
2912 END IF;
2913 RETURN l_comp_tbl;
2914 END get_kit_component_list;
2915
2916
2917 /*==========================
2918 Public Procedure
2919 =========================*/
2920
2921 /*********************************
2922 Procedure to query the eligible material for pack/split/unpack transactions
2923 For inbound, it queries move order lines
2924 For outbound, it queries delivery detail lines
2925 After it finds results, it populates global temp table
2926 WMS_PACKING_MATERIAL_GTEMP to display on the spreadtable on packing workbench form
2927
2928 Input Parameter:
2929 p_source_id: 1=>Inbound, 2=>Outbound
2930
2931 The following input parameters applies for both inbound and outbound
2932 p_organization_id: Organization
2933 p_subinventory_code: Subinventory
2934 p_locator_id: ID for Locator
2935 p_inventory_item_id: ID for Item
2936 p_from_lpn_id: ID for From LPN
2937 p_project_id: ID for Project
2938 p_task_id: ID for Task
2939
2940 The following parameters applies for inbound
2941 p_document_type: 'ASN', 'INTSHIP', 'PO', 'REQ', 'RMA'
2942 p_document_id: ID for inbound document
2943 p_document_line_id: ID for inbound document line
2944 p_receipt_number: Receipt number
2945 p_partner_id: it can be vendor_id or internal org_id
2946 p_partner_type: 1=> Vendor, 2=> Internal Organization
2947 p_rcv_location_id: ID for receiving location
2948
2949 The following parameters applies for outbound
2950 p_delivery_id: ID for delivery
2951 p_order_header_id: ID for sales order header
2952 p_carrier_id: ID for carrier
2953 p_trip_id: ID for Trip
2954 p_delivery_state: 'Y'=> Deliveries that are completed packed
2955 'N"=> Deliveries that are not completed packed
2956 NULL=> all deliveries
2957 p_customer_id: ID for customer
2958 *********************************/
2959
2960 PROCEDURE query_eligible_material(
2961 x_return_status OUT NOCOPY VARCHAR2
2962 , p_source_id IN NUMBER
2963 , p_organization_id IN NUMBER
2964 , p_organization_code IN VARCHAR2
2965 , p_subinventory_code IN VARCHAR2 DEFAULT NULL
2966 , p_locator_id IN NUMBER DEFAULT NULL
2967 , p_locator IN VARCHAR2 DEFAULT NULL
2968 , p_inventory_item_id IN NUMBER DEFAULT NULL
2969 , p_item IN VARCHAR2 DEFAULT NULL
2970 , p_from_lpn_id IN NUMBER DEFAULT NULL
2971 , p_project_id IN NUMBER DEFAULT NULL
2972 , p_project IN VARCHAR2 DEFAULT NULL
2973 , p_task_id IN NUMBER DEFAULT NULL
2974 , p_task IN VARCHAR2 DEFAULT NULL
2975 , p_document_type IN VARCHAR2 DEFAULT NULL
2976 , p_document_id IN NUMBER DEFAULT NULL
2977 , p_document_number IN VARCHAR2 DEFAULT NULL
2978 , p_document_line_id IN NUMBER DEFAULT NULL
2979 , p_document_line_num IN VARCHAR2 DEFAULT NULL--CLM Changes, Line number to be alphanumeric
2980 , p_receipt_number IN VARCHAR2 DEFAULT NULL
2981 , p_partner_id IN NUMBER DEFAULT NULL
2982 , p_partner_type IN NUMBER DEFAULT NULL
2983 , p_partner_name IN VARCHAR2 DEFAULT NULL
2984 , p_rcv_location_id IN NUMBER DEFAULT NULL
2985 , p_rcv_location IN VARCHAR2 DEFAULT NULL
2986 , p_delivery_id IN NUMBER DEFAULT NULL
2987 , p_delivery IN VARCHAR2 DEFAULT NULL
2988 , p_order_header_id IN NUMBER DEFAULT NULL
2989 , p_order_number IN VARCHAR2 DEFAULT NULL
2990 , p_order_type IN VARCHAR2 DEFAULT NULL
2991 , p_carrier_id IN NUMBER DEFAULT NULL
2992 , p_carrier IN VARCHAR2 DEFAULT NULL
2993 , p_trip_id IN NUMBER DEFAULT NULL
2994 , p_trip IN VARCHAR2 DEFAULT NULL
2995 , p_delivery_state IN VARCHAR2 DEFAULT NULL
2996 , p_customer_id IN NUMBER DEFAULT NULL
2997 , p_customer IN VARCHAR2 DEFAULT NULL
2998 , p_is_pjm_enabled_org IN VARCHAR2 DEFAULT 'N'
2999 , x_source_unique OUT nocopy VARCHAR2
3000 ) IS
3001
3002 l_return_status VARCHAR2(1);
3003
3004 BEGIN
3005 x_return_status := FND_API.G_RET_STS_SUCCESS;
3006 l_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3007
3008 IF p_source_id = 1 THEN
3009 -- Inbound
3010 query_inbound_material(
3011 x_return_status => l_return_status
3012 ,p_organization_id => p_organization_id
3013 ,p_organization_code => p_organization_code
3014 ,p_subinventory_code => p_subinventory_code
3015 ,p_locator_id => p_locator_id
3016 ,p_locator => p_locator
3017 ,p_inventory_item_id => p_inventory_item_id
3018 ,p_item => p_item
3019 ,p_from_lpn_id => p_from_lpn_id
3020 ,p_project_id => p_project_id
3021 ,p_project => p_project
3022 ,p_task_id => p_task_id
3023 ,p_task => p_task
3024 ,p_document_type => p_document_type
3025 ,p_document_id => p_document_id
3026 ,p_document_number => p_document_number
3027 ,p_document_line_id => p_document_line_id
3028 ,p_document_line_num => p_document_line_num
3029 ,p_receipt_number => p_receipt_number
3030 ,p_partner_id => p_partner_id
3031 ,p_partner_type => p_partner_type
3032 ,p_partner_name => p_partner_name
3033 ,p_rcv_location_id => p_rcv_location_id
3034 ,p_rcv_location => p_rcv_location
3035 ,p_is_pjm_enabled_org => p_is_pjm_enabled_org
3036 ,x_source_unique => x_source_unique );
3037 ELSIF p_source_id = 2 THEN
3038 -- Outbound
3039 query_outbound_material(
3040 x_return_status => l_return_status
3041 ,p_organization_id => p_organization_id
3042 ,p_organization_code => p_organization_code
3043 ,p_subinventory_code => p_subinventory_code
3044 ,p_locator_id => p_locator_id
3045 ,p_locator => p_locator
3046 ,p_inventory_item_id => p_inventory_item_id
3047 ,p_item => p_item
3048 ,p_from_lpn_id => p_from_lpn_id
3049 ,p_project_id => p_project_id
3050 ,p_project => p_project
3051 ,p_task_id => p_task_id
3052 ,p_task => p_task
3053 ,p_delivery_id => p_delivery_id
3054 ,p_delivery => p_delivery
3055 ,p_order_header_id => p_order_header_id
3056 ,p_order_number => p_order_number
3057 ,p_order_type => p_order_type
3058 ,p_carrier_id => p_carrier_id
3059 ,p_carrier => p_carrier
3060 ,p_trip_id => p_trip_id
3061 ,p_trip => p_trip
3062 ,p_delivery_state => p_delivery_state
3063 ,p_customer_id => p_customer_id
3064 ,p_customer => p_customer);
3065 END IF;
3066
3067 x_return_status := l_return_status;
3068
3069 END query_eligible_material;
3070
3071
3072 /*******************************************
3073 * Procedure to create MMTT/MTLT/MSNT record
3074 * For a pack/split/unpack transaction
3075 *******************************************/
3076 PROCEDURE create_txn(
3077 x_return_status OUT NOCOPY VARCHAR2
3078 , x_proc_msg OUT NOCOPY VARCHAR2
3079 , p_source IN NUMBER
3080 , p_pack_process IN NUMBER
3081 , p_organization_id IN NUMBER
3082 , p_inventory_item_id IN NUMBER
3083 , p_primary_uom IN VARCHAR2
3084 , p_revision IN VARCHAR2
3085 , p_lot_number IN VARCHAR2
3086 , p_lot_expiration_date IN DATE
3087 , p_fm_serial_number IN VARCHAR2
3088 , p_to_serial_number IN VARCHAR2
3089 , p_from_lpn_id IN NUMBER
3090 , p_content_lpn_id IN NUMBER
3091 , p_to_lpn_id IN NUMBER
3092 , p_subinventory_code IN VARCHAR2
3093 , p_locator_id IN NUMBER
3094 , p_to_subinventory IN VARCHAR2
3095 , p_to_locator_id IN NUMBER
3096 , p_project_id IN NUMBER
3097 , p_task_id IN NUMBER
3098 , p_transaction_qty IN NUMBER
3099 , p_transaction_uom IN VARCHAR2
3100 , p_primary_qty IN NUMBER
3101 , p_secondary_qty IN NUMBER
3102 , p_secondary_uom IN VARCHAR2
3103 , p_transaction_header_id IN NUMBER
3104 , p_transaction_temp_id IN NUMBER
3105 , x_transaction_header_id OUT NOCOPY NUMBER
3106 , x_transaction_temp_id OUT NOCOPY NUMBER
3107 , x_serial_transaction_temp_id OUT NOCOPY NUMBER
3108 , p_grade_code IN VARCHAR2 --INVCONV kkillams
3109 ) IS
3110
3111 /*CURSOR inb_cur IS
3112 SELECT wpmg.move_order_line_id
3113 , wpmg.txn_source_id
3114 , wpmg.project_id
3115 , wpmg.task_id
3116 , inv_convert.inv_um_convert(wpmg.inventory_item_id,null,least(mol.quantity,wpmg.quantity), mol.uom_code,p_primary_uom,null,null)
3117 , least(mol.quantity,wpmg.quantity)
3118 , mol.uom_code
3119 --, decode(wpmg.uom, p_transaction_uom, 0, 1) uom_match
3120 , least(mol.secondary_quantity,wpmg.secondary_quantity)
3121 , mol.secondary_uom_code --INVCONV kkillams
3122 , wpmg.grade_code --INVCONV kkillams
3123 , mol.lot_number
3124 , mol.inspection_status
3125 FROM wms_packing_material_gtemp wpmg, mtl_txn_request_lines mol
3126 WHERE wpmg.move_order_line_id = mol.line_id
3127 AND wpmg.organization_id = p_organization_id
3128 AND nvl(wpmg.subinventory,'#$%') = nvl(p_subinventory_code, nvl(wpmg.subinventory,'#$%'))
3129 AND nvl(wpmg.locator_id, -9999) = nvl(p_locator_id, nvl(wpmg.locator_id, -9999))
3130 AND wpmg.inventory_item_id = p_inventory_item_id
3131 AND ((p_revision IS NULL) OR
3132 (p_revision IS NOT NULL and wpmg.revision = p_revision))
3133 AND ((wpmg.lot_number IS NULL) OR
3134 (wpmg.lot_number IS NOT NULL and wpmg.lot_number = p_lot_number))
3135 AND ((p_from_lpn_id IS NULL AND wpmg.lpn_id IS NULL) OR
3136 (p_from_lpn_id IS NOT NULL and wpmg.lpn_id = p_from_lpn_id))
3137 AND ((p_project_id = -1 and p_task_id = -1) OR
3138 (wpmg.project_id IS NULL and p_project_id IS NULL and
3139 wpmg.task_id IS NULL and p_task_id IS NULL) OR
3140 (wpmg.project_id = p_project_id AND wpmg.task_id = p_task_id))
3141 AND ((mol.wms_process_flag = 2 and wpmg.selected_flag='Y') OR
3142 (mol.wms_process_flag <> 2))
3143 order by decode(wpmg.uom, p_transaction_uom, 0, 1) asc, mol.creation_date asc;*/
3144
3145 -- modified this cursor for bug 14269755
3146
3147 CURSOR inb_cur IS
3148 SELECT wpmg.move_order_line_id
3149 , wpmg.txn_source_id
3150 , wpmg.project_id
3151 , wpmg.task_id
3152 , inv_convert.inv_um_convert(wpmg.inventory_item_id,null,least(mol.quantity,wpmg.quantity), mol.uom_code,p_primary_uom,null,null)
3153 , least(mol.quantity,wpmg.quantity)
3154 , mol.uom_code
3155 --, decode(wpmg.uom, p_transaction_uom, 0, 1) uom_match
3156 , least(mol.secondary_quantity,wpmg.secondary_quantity)
3157 , mol.secondary_uom_code --INVCONV kkillams
3158 , wpmg.grade_code --INVCONV kkillams
3159 , mol.lot_number
3160 , mol.inspection_status
3161 FROM wms_packing_material_gtemp wpmg, mtl_txn_request_lines mol
3162 WHERE wpmg.move_order_line_id = mol.line_id
3163 AND wpmg.organization_id = p_organization_id
3164 AND nvl(wpmg.subinventory,'#$%') = nvl(p_subinventory_code, nvl(wpmg.subinventory,'#$%'))
3165 AND nvl(wpmg.locator_id, -9999) = nvl(p_locator_id, nvl(wpmg.locator_id, -9999))
3166 AND wpmg.inventory_item_id = p_inventory_item_id
3167 AND ((p_revision IS NULL) OR
3168 (p_revision IS NOT NULL and wpmg.revision = p_revision))
3169 AND ((wpmg.lot_number IS NULL) OR
3170 (wpmg.lot_number IS NOT NULL and wpmg.lot_number = p_lot_number))
3171 AND ((p_from_lpn_id IS NULL AND wpmg.lpn_id IS NULL) OR
3172 (p_from_lpn_id IS NOT NULL and wpmg.lpn_id = p_from_lpn_id))
3173 AND ((p_project_id = -1 and p_task_id = -1) OR
3174 (wpmg.project_id IS NULL and p_project_id IS NULL and
3175 wpmg.task_id IS NULL and p_task_id IS NULL) OR
3176 (wpmg.project_id = p_project_id AND wpmg.task_id = p_task_id))
3177 AND ((mol.wms_process_flag = 2 and wpmg.selected_flag='Y') OR
3178 (mol.wms_process_flag <> 2))
3179 AND EXISTS (SELECT 1 FROM rcv_serials_supply rss, rcv_supply rs
3180 WHERE rss.serial_num = p_fm_serial_number AND (rss.supply_type_code = 'SHIPMENT' OR (rss.supply_type_code = 'RECEIVING' AND rss.transaction_id = rs.rcv_transaction_id))
3181 AND rss.shipment_line_id = rs.shipment_line_id
3182 AND ((mol.lpn_id IS NULL AND rs.lpn_id IS NULL) OR rs.lpn_id = mol.lpn_id)
3183 AND ((rs.shipment_line_id = mol.reference_id AND mol.reference='SHIPMENT_LINE_ID')
3184 OR
3185 (rs.po_line_location_id = mol.reference_id AND mol.reference='PO_LINE_LOCATION_ID')
3186 OR
3187 (rs.oe_order_line_id = mol.reference_id AND mol.reference='ORDER_LINE_ID')))
3188 UNION ALL
3189
3190 SELECT wpmg.move_order_line_id
3191 , wpmg.txn_source_id
3192 , wpmg.project_id
3193 , wpmg.task_id
3194 , inv_convert.inv_um_convert(wpmg.inventory_item_id,null,least(mol.quantity,wpmg.quantity), mol.uom_code,p_primary_uom,null,null)
3195 , least(mol.quantity,wpmg.quantity)
3196 , mol.uom_code
3197 --, decode(wpmg.uom, p_transaction_uom, 0, 1) uom_match
3198 , least(mol.secondary_quantity,wpmg.secondary_quantity)
3199 , mol.secondary_uom_code --INVCONV kkillams
3200 , wpmg.grade_code --INVCONV kkillams
3201 , mol.lot_number
3202 , mol.inspection_status
3203 FROM wms_packing_material_gtemp wpmg, mtl_txn_request_lines mol
3204 WHERE wpmg.move_order_line_id = mol.line_id
3205 AND wpmg.organization_id = p_organization_id
3206 AND nvl(wpmg.subinventory,'#$%') = nvl(p_subinventory_code, nvl(wpmg.subinventory,'#$%'))
3207 AND nvl(wpmg.locator_id, -9999) = nvl(p_locator_id, nvl(wpmg.locator_id, -9999))
3208 AND wpmg.inventory_item_id = p_inventory_item_id
3209 AND ((p_revision IS NULL) OR
3210 (p_revision IS NOT NULL and wpmg.revision = p_revision))
3211 AND ((wpmg.lot_number IS NULL) OR
3212 (wpmg.lot_number IS NOT NULL and wpmg.lot_number = p_lot_number))
3213 AND ((p_from_lpn_id IS NULL AND wpmg.lpn_id IS NULL) OR
3214 (p_from_lpn_id IS NOT NULL and wpmg.lpn_id = p_from_lpn_id))
3215 AND ((p_project_id = -1 and p_task_id = -1) OR
3216 (wpmg.project_id IS NULL and p_project_id IS NULL and
3217 wpmg.task_id IS NULL and p_task_id IS NULL) OR
3218 (wpmg.project_id = p_project_id AND wpmg.task_id = p_task_id))
3219 AND ((mol.wms_process_flag = 2 and wpmg.selected_flag='Y') OR
3220 (mol.wms_process_flag <> 2))
3221
3222 AND NOT EXISTS (SELECT 1 FROM rcv_serials_supply rss, rcv_supply rs
3223 WHERE rss.serial_num = p_fm_serial_number AND rss.shipment_line_id = rs.shipment_line_id
3224 AND rs.item_id=wpmg.inventory_item_id AND rs.to_organization_id=wpmg.organization_id)
3225
3226 AND NOT EXISTS (SELECT 1 FROM rcv_serials_supply rss, rcv_supply rs
3227 WHERE (rss.supply_type_code = 'SHIPMENT' OR (rss.supply_type_code = 'RECEIVING' AND rss.transaction_id = rs.rcv_transaction_id))
3228 AND rss.shipment_line_id = rs.shipment_line_id
3229 AND ((mol.lpn_id IS NULL AND rs.lpn_id IS NULL) OR rs.lpn_id = mol.lpn_id)
3230 AND ((rs.shipment_line_id = mol.reference_id AND mol.reference='SHIPMENT_LINE_ID')
3231 OR
3232 (rs.po_line_location_id = mol.reference_id AND mol.reference='PO_LINE_LOCATION_ID')
3233 OR
3234 (rs.oe_order_line_id = mol.reference_id AND mol.reference='ORDER_LINE_ID')));
3235
3236 --order by decode(wpmg.uom, p_transaction_uom, 0, 1) asc, mol.creation_date asc;
3237
3238 --Bug 6028098
3239 CURSOR get_gtemp IS
3240 SELECT * FROM wms_packing_material_gtemp
3241 WHERE inventory_item_id = p_inventory_item_id
3242 AND nvl(lot_number, '#$%') = nvl(p_lot_number, nvl(lot_number, '#$%'))
3243 AND nvl(revision, '#') = nvl(p_revision,nvl(revision, '#'))
3244 AND subinventory = p_subinventory_code
3245 AND locator_id = p_locator_id
3246 AND lpn_id = p_from_lpn_id;
3247
3248 l_sum_qty NUMBER := 0; --Bug 6028098
3249 l_process_qty NUMBER := 0; --Bug 6028098
3250 l_update_qty NUMBER := 0; --Bug 6028098
3251
3252 l_from_sub VARCHAR2(30);
3253 l_from_loc_id NUMBER;
3254 l_to_sub VARCHAR2(30);
3255 l_to_loc_id NUMBER;
3256
3257 l_txn_action_id NUMBER;
3258 l_txn_type_id NUMBER;
3259 l_txn_hdr_id NUMBER;
3260 l_txn_tmp_id NUMBER;
3261 l_ser_txn_id NUMBER;
3262
3263 l_insert NUMBER;
3264 l_proc_msg VARCHAR2(1000);
3265 l_return_status VARCHAR2(1);
3266 l_msg_count NUMBER;
3267 l_msg_data VARCHAR2(1000);
3268 i NUMBER;
3269 l_new_tolocator_id NUMBER;
3270
3271 l_mmtt_exists NUMBER;
3272 l_cur_rec mmtt_mtlt_rec_type;
3273
3274 l_mol_line_id NUMBER;
3275 l_mol_uom VARCHAR2(3);
3276 l_txn_src_id NUMBER;
3277 l_mol_project_id NUMBER;
3278 l_mol_task_id NUMBER;
3279 l_mol_prim_qty NUMBER;
3280 l_mol_txn_qty NUMBER;
3281 l_available_qty NUMBER;
3282 l_mol_sec_qty NUMBER; --INVCONV kkillams
3283 l_mol_sec_uom VARCHAR2(3); --INVCONV kkillams
3284 l_mol_grade_code VARCHAR2(150); --INVCONV kkillams
3285 l_mol_inspection_status NUMBER;
3286 l_mol_lot_number VARCHAR2(80);
3287 l_ser_inspection_status NUMBER;
3288 l_ser_lot_number VARCHAR2(80);
3289
3290 l_mol_list move_order_tbl_type;
3291 l_mol_count NUMBER;
3292
3293 l_new_lot NUMBER;
3294 l_new_serial NUMBER;
3295
3296 l_row_count NUMBER;
3297 l_current_status NUMBER := 1;
3298
3299 BEGIN
3300 fnd_msg_pub.Initialize;
3301 IF l_debug = 1 THEN
3302 trace('In Create TXN :');
3303 trace(' p_source='||p_source||', p_pack_process='||p_pack_process);
3304 trace(' p_organization_id='||p_organization_id);
3305 trace(' p_inventory_item_id='||p_inventory_item_id);
3306 trace(' p_primary_uom='||p_primary_uom||', p_revision='||p_revision);
3307 trace(' p_lot_number='||p_lot_number||', p_lot_expiration_date='||p_lot_expiration_date);
3308 trace(' p_fm_serial_number='||p_fm_serial_number||', p_to_serial_number='||p_to_serial_number);
3309 trace(' p_from_lpn_id='||p_from_lpn_id||', p_content_lpn_id='||p_content_lpn_id);
3310 trace(' p_to_lpn_id='||p_to_lpn_id);
3311 trace(' p_subinventory_code='||p_subinventory_code||',p_locator_id='||p_locator_id);
3312 trace(' p_to_subinventory='||p_to_subinventory||',p_to_locator_id='||p_to_locator_id);
3313 trace(' p_transaction_qty='||p_transaction_qty||',p_transaction_uom='||p_transaction_uom);
3314 trace(' p_primary_qty='||p_primary_qty);
3315 trace(' p_secondary_qty='||p_secondary_qty||',p_secondary_uom='||p_secondary_uom);
3316 trace(' p_transaction_header_id='||p_transaction_header_id||',p_transaction_temp_id='||p_transaction_temp_id);
3317 trace(' p_project_id='||p_project_id||', p_task_id='||p_task_id);
3318 trace(' p_grade_code='||p_grade_code);
3319 END IF;
3320
3321 x_return_status := fnd_api.G_RET_STS_SUCCESS;
3322
3323 l_new_tolocator_id := p_to_locator_id;
3324 -- Here only those records should be present to process for which there is
3325 -- UNIQUE combination OF projec/task for the item, The restriction
3326 -- IS made sure in the find window itself
3327
3328 --find the default locator if there is project/Task
3329 -- If not then create the new logical locator
3330
3331 IF (Nvl(p_project_id,-1)<>-1) THEN -- Added for bug 7577646
3332
3333 IF l_debug = 1 THEN
3334 trace('Getting/Creating logical locator for the project');
3335 END IF;
3336
3337 pjm_project_locator.get_defaultprojectlocator
3338 ( p_organization_id
3339 , l_new_tolocator_id
3340 , p_project_id
3341 , p_task_id
3342 , l_new_tolocator_id);
3343
3344 IF l_debug = 1 THEN
3345 trace('Existing/New to_locator_id for project='||l_new_tolocator_id);
3346 END IF;
3347
3348 END IF;
3349
3350
3351 IF p_source =1 THEN
3352 --Inbound
3353 IF l_debug = 1 THEN
3354 trace('Create txn record for Inbound');
3355 END IF;
3356 -- If exists pening MMTT, get previous MMTT information
3357 l_mmtt_exists := 0;
3358 IF p_transaction_header_id IS NOT NULL and p_transaction_temp_id IS NOT NULL THEN
3359 l_cur_rec := l_null_rec;
3360 BEGIN
3361 SELECT 1, mmtt.move_order_line_id, mmtt.inventory_item_id, mmtt.revision
3362 , mmtt.transaction_quantity, mmtt.transaction_uom, mtlt.lot_number, mtlt.serial_transaction_temp_id
3363 , mmtt.secondary_transaction_quantity, mmtt.secondary_uom_code --INVCONV kkillams
3364 INTO l_mmtt_exists, l_cur_rec.move_order_line_id, l_cur_rec.inventory_item_id
3365 , l_cur_rec.revision, l_cur_rec.transaction_quantity, l_cur_rec.transaction_uom
3366 , l_cur_rec.lot_number, l_cur_rec.serial_transaction_temp_id
3367 , l_cur_rec.secondary_transaction_quantity, l_cur_rec.secondary_uom_code --INVCONV kkillams
3368 FROM mtl_material_transactions_temp mmtt, mtl_transaction_lots_temp mtlt
3369 WHERE mtlt.transaction_temp_id(+) = mmtt.transaction_temp_id
3370 AND mmtt.transaction_header_id = p_transaction_header_id
3371 AND mmtt.transaction_temp_id = p_transaction_temp_id
3372 AND mmtt.inventory_item_id <> -1
3373 AND mmtt.content_lpn_id IS NULL;
3374 EXCEPTION
3375 WHEN no_data_found THEN
3376 l_mmtt_exists:=0;
3377 END;
3378 END IF;
3379
3380 IF l_debug = 1 THEN
3381 trace('l_mmtt_exists='||l_mmtt_exists);
3382 END IF;
3383
3384 IF p_pack_process = 1 THEN
3385 l_txn_action_id := 50;
3386 l_txn_type_id := 87; -- Container pack
3387 ELSIF p_pack_process = 2 THEN
3388 l_txn_action_id := 52;
3389 l_txn_type_id := 89; -- Container split
3390 ELSIF p_pack_process = 3 THEN
3391 l_txn_action_id := 51;
3392 l_txn_type_id := 88; -- Container Unpack
3393 ELSE
3394 fnd_message.set_name('INV','INV_INT_TRXACTCODE');
3395 fnd_msg_pub.add;
3396 raise fnd_api.g_exc_error;
3397 END IF;
3398
3399 IF l_debug = 1 THEN
3400 trace('trx action='||l_txn_action_id||',trx type='||l_txn_type_id);
3401 END IF;
3402
3403 IF p_content_lpn_id IS NOT NULL THEN
3404 -- Content is LPN, create new set of transactions and commit
3405
3406
3407 -- If there is existing MMTT for loose item, submit the transactions
3408 IF l_mmtt_exists = 1 THEN
3409 SAVEPOINT BEFORE_TM;
3410 IF l_debug = 1 THEN
3411 trace('Set savepoint BEFORE_TM, Calling rcv TM for previous MMTT txns, header_id='||p_transaction_header_id);
3412 END IF;
3413 WMS_RCV_PUP_PVT.pack_unpack_split
3414 ( p_header_id => p_transaction_header_id
3415 ,x_return_status => l_return_status
3416 ,x_msg_count => l_msg_count
3417 ,x_msg_data => l_msg_data
3418 );
3419 END IF;
3420 IF l_return_status <> 'S' THEN
3421 IF l_debug = 1 THEN
3422 trace('Error in process transaction for hdr_id='||p_transaction_header_id||',l_msg_data='||l_msg_data);
3423 END IF;
3424 raise fnd_api.g_exc_error;
3425 ELSE
3426 IF l_debug = 1 THEN
3427 trace('transaction processed successfully, hdr_id ='||p_transaction_header_id);
3428 END IF;
3429 END IF;
3430
3431 IF l_debug = 1 THEN
3432 trace('Creating MMTT for Content LPN ID '||p_content_lpn_id);
3433 END IF;
3434 l_insert := inv_trx_util_pub.insert_line_trx(
3435 p_trx_hdr_id => null
3436 ,p_item_id => null
3437 ,p_org_id => p_organization_id
3438 ,p_trx_action_id => l_txn_action_id
3439 ,p_trx_type_id => l_txn_type_id
3440 ,p_trx_src_type_id => 13
3441 ,p_trx_qty => 0
3442 ,p_pri_qty => 0
3443 ,p_uom => nvl(p_transaction_uom, ' ')
3444 ,p_subinv_code => p_subinventory_code
3445 ,p_tosubinv_code => p_to_subinventory
3446 ,p_locator_id => p_locator_id
3447 ,p_tolocator_id => l_new_tolocator_id
3448 ,p_from_lpn_id => p_from_lpn_id
3449 ,p_cnt_lpn_id => p_content_lpn_id
3450 ,p_xfr_lpn_id => p_to_lpn_id
3451 ,p_posting_flag => 'N' -- Set this so that locator capacity calculation will not consider this
3452 ,p_move_order_line_id => null
3453 ,p_process_flag => 'N' -- Set process_flag to 'N' so that INV TM will not process this MMTT
3454 ,p_user_id => fnd_global.user_id
3455 ,x_trx_tmp_id => l_txn_tmp_id
3456 ,x_proc_msg => l_proc_msg
3457 ,p_secondary_trx_qty => CASE WHEN p_secondary_uom IS NOT NULL THEN 0 ELSE NULL END --INVCONV kkillams
3458 ,p_secondary_uom => nvl(p_secondary_uom, ' ') --INVCONV kkillams
3459 );
3460 IF l_debug = 1 THEN
3461 trace('done with inserting , l_insert ='||l_insert);
3462 END IF;
3463 IF l_insert <> 0 THEN
3464 IF l_debug = 1 THEN
3465 trace('Error when inserting MMTT for content lpn ID '|| p_content_lpn_id|| 'err is '||l_proc_msg);
3466 END IF;
3467 x_proc_msg := l_proc_msg;
3468 raise fnd_api.g_exc_error;
3469 END IF;
3470
3471 IF l_debug = 1 THEN
3472 trace('MMTT inserted, tmp_id='||l_txn_tmp_id);
3473 END IF;
3474 l_txn_hdr_id := l_txn_tmp_id;
3475
3476 SAVEPOINT BEFORE_TM;
3477 IF l_debug = 1 THEN
3478 trace('Set savepoint BEFORE_TM, Calling API to process the transaction');
3479 END IF;
3480 WMS_RCV_PUP_PVT.pack_unpack_split
3481 (p_transaction_temp_id => l_txn_tmp_id
3482 ,p_header_id => l_txn_hdr_id
3483 ,x_return_status => l_return_status
3484 ,x_msg_count => l_msg_count
3485 ,x_msg_data => l_msg_data
3486 );
3487
3488 IF l_return_status <> 'S' THEN
3489 IF l_debug = 1 THEN
3490 trace('Error in process transaction for hdr_id='||l_txn_hdr_id||',tmp_id='||l_txn_tmp_id ||',l_msg_data='||l_msg_data);
3491 END IF;
3492 raise fnd_api.g_exc_error;
3493 ELSE
3494 IF l_debug = 1 THEN
3495 trace('transaction processed successfully, txn_temp_id ='||l_txn_tmp_id);
3496 END IF;
3497 x_return_status := 'S';
3498 x_transaction_header_id := l_txn_hdr_id;
3499 x_transaction_temp_id := l_txn_tmp_id;
3500 x_serial_transaction_temp_id := null;
3501
3502 -- Delete record from eligible material temp table
3503 delete from wms_packing_material_gtemp where outermost_lpn_id = p_content_lpn_id;
3504 --commit;
3505 END IF;
3506
3507 ELSIF p_inventory_item_id IS NOT NULL THEN
3508 -- Content is item
3509
3510 -- If there are previous transactions for different item,
3511 -- submit the previous txns
3512 IF l_debug = 1 THEN
3513 trace('Content is item,mmtt_exists='||l_mmtt_exists||',cur_rec.item='||l_cur_rec.inventory_item_id);
3514 END IF;
3515
3516
3517 IF l_mmtt_exists = 1 AND l_cur_rec.inventory_item_id <> p_inventory_item_id THEN
3518 SAVEPOINT BEFORE_TM;
3519 IF l_debug = 1 THEN
3520 trace('Set savepoint BEFORE_TM, Calling WMS_RCV_PUP_PVT.pack_unpack_split API for p_transaction_header_id='||p_transaction_header_id);
3521 END IF;
3522 WMS_RCV_PUP_PVT.pack_unpack_split
3523 ( p_header_id => p_transaction_header_id
3524 ,x_return_status => l_return_status
3525 ,x_msg_count => l_msg_count
3526 ,x_msg_data => l_msg_data
3527 );
3528
3529 IF l_return_status <> 'S' THEN
3530 IF l_debug = 1 THEN
3531 trace('Error in process transaction for hdr_id='||p_transaction_header_id||',l_msg_data='||l_msg_data);
3532 END IF;
3533 raise fnd_api.g_exc_error;
3534 ELSE
3535 IF l_debug = 1 THEN
3536 trace('transaction processed successfully, hdr_id='||p_transaction_header_id);
3537 END IF;
3538 l_mmtt_exists := 0;
3539 END IF;
3540 END IF;
3541
3542 -- Get the list of move order line IDs that will satify the transaction quantity
3543 l_mol_list.delete;
3544 l_available_qty := 0;
3545 l_mol_count := 0;
3546
3547 begin
3548 select current_status
3549 , lot_number
3550 , inspection_status
3551 INTO l_current_status
3552 , l_ser_lot_number
3553 , l_ser_inspection_status
3554 from mtl_serial_numbers a
3555 where serial_number = p_fm_serial_number
3556 and current_organization_id = p_organization_id
3557 and inventory_item_id = p_inventory_item_id
3558 and rownum<2;
3559
3560 exception
3561 when no_data_found then
3562 l_current_status := 1;
3563 l_ser_lot_number := NULL;
3564 l_ser_inspection_status := NULL;
3565 when others then
3566 null;
3567 end;
3568
3569 trace('ser_num:'||p_fm_serial_number||
3570 ' lot_num:'||l_ser_lot_number||
3571 ' inspect_status:'||l_ser_inspection_status);
3572
3573 OPEN inb_cur;
3574 FETCH inb_cur
3575 INTO l_mol_line_id
3576 , l_txn_src_id
3577 , l_mol_project_id
3578 , l_mol_task_id
3579 , l_mol_prim_qty
3580 , l_mol_txn_qty
3581 , l_mol_uom
3582 , l_mol_sec_qty
3583 , l_mol_sec_uom
3584 , l_mol_grade_code --INVCONV kkillams
3585 , l_mol_lot_number
3586 , l_mol_inspection_status;
3587 IF inb_cur%NOTFOUND THEN
3588 fnd_message.set_name('WMS','WMS_NO_ELIGIBLE_MATERIAL');
3589 fnd_msg_pub.add;
3590 raise fnd_api.g_exc_error;
3591 END IF;
3592
3593 LOOP
3594 IF inb_cur%NOTFOUND THEN
3595 CLOSE inb_cur;
3596 EXIT;
3597 END IF;
3598 IF l_debug =1 THEN
3599 trace('in mol loop, l_mol_line_id='||l_mol_line_id||',l_txn_src_id='||l_txn_src_id);
3600 END IF;
3601 IF p_fm_serial_number IS NULL THEN
3602 l_available_qty := l_available_qty + l_mol_prim_qty;
3603 ELSE
3604 --R12
3605 IF (l_current_status = 1 OR
3606 (Nvl(l_mol_lot_number,'@@@') = Nvl(l_ser_lot_number,'@@@') AND
3607 Nvl(l_mol_inspection_status,-1) = Nvl(nullif(l_ser_inspection_status,-9999),-1))) THEN
3608 l_available_qty := l_available_qty + l_mol_prim_qty;
3609 ELSE
3610 l_available_qty := 0;
3611 END IF;
3612 END IF;
3613
3614 IF l_debug = 1 THEN
3615 trace('l_available_qty='||l_available_qty||',p_primary_qty='||p_primary_qty);
3616 trace('l_mol_txn_qty='||l_mol_txn_qty||',l_mol_prim_qty='||l_mol_prim_qty);
3617 END IF;
3618 IF l_available_qty > 0 THEN
3619 l_mol_count := l_mol_count + 1;
3620 l_mol_list(l_mol_count).move_order_line_id := l_mol_line_id;
3621 l_mol_list(l_mol_count).transaction_uom := l_mol_uom;
3622 l_mol_list(l_mol_count).secondary_uom_code := l_mol_sec_uom; --INCONV kkillams
3623 l_mol_list(l_mol_count).grade_code := l_mol_grade_code; --INCONV kkillams
3624 IF l_available_qty < p_primary_qty THEN
3625 -- this move order line will be used for the transaction
3626 -- The quantity is the quantity on the move order line
3627 l_mol_list(l_mol_count).transaction_quantity := l_mol_txn_qty;
3628 l_mol_list(l_mol_count).primary_quantity := l_mol_prim_qty;
3629 l_mol_list(l_mol_count).secondary_transaction_quantity := l_mol_sec_qty; --INCONV kkillams
3630 ELSIF l_available_qty >= p_primary_qty THEN
3631 -- Last move order line needed for the transaction
3632 -- the quantity may be partial of the quantity on the move order line
3633 -- which is the mol.qty less the extra qty between the available qty and p_primary_qty
3634 l_mol_list(l_mol_count).primary_quantity := l_mol_prim_qty - (l_available_qty-p_primary_qty);
3635 l_mol_list(l_mol_count).transaction_quantity :=
3636 inv_convert.inv_um_convert(p_inventory_item_id,null,l_mol_list(l_mol_count).primary_quantity,p_primary_uom,l_mol_uom,null,null);
3637 l_mol_list(l_mol_count).SECONDARY_TRANSACTION_QUANTITY :=
3638 inv_convert.inv_um_convert(p_inventory_item_id,null,l_mol_list(l_mol_count).primary_quantity,p_primary_uom,l_mol_sec_uom,null,null);--INCONV kkillams
3639 CLOSE inb_cur;
3640 EXIT;
3641 END IF;
3642 IF l_debug = 1 THEN
3643 trace('txn_qty='||l_mol_list(l_mol_count).transaction_quantity ||',prim_qty='||l_mol_list(l_mol_count).primary_quantity);
3644 END IF;
3645 END IF;
3646
3647
3648 FETCH inb_cur
3649 INTO l_mol_line_id, l_txn_src_id, l_mol_project_id, l_mol_task_id,
3650 l_mol_prim_qty, l_mol_txn_qty, l_mol_uom
3651 , l_mol_sec_qty, l_mol_sec_uom , l_mol_grade_code
3652 , l_mol_lot_number, l_mol_inspection_status; --INVCONV kkillams
3653 END LOOP;
3654 IF l_available_qty < p_primary_qty THEN
3655 l_mol_list := l_null_mol_list;
3656 fnd_message.set_name('WMS','WMS_NO_ELIGIBLE_MATERIAL');
3657 fnd_msg_pub.add;
3658 raise fnd_api.g_exc_error;
3659 END IF;
3660 IF l_debug = 1 THEN
3661 trace('Found move order lines to fulfill transactions, number of records:'||l_mol_list.count);
3662 END IF;
3663 -- Create/Upadate MMTT for each move order line
3664 l_txn_hdr_id := p_transaction_header_id;
3665 FOR i IN 1..l_mol_list.count LOOP
3666 -- When no mmtt exists, or item changes
3667 IF l_debug = 1 THEN
3668 trace('l_cur_rec.move_order_line_id='||l_cur_rec.move_order_line_id);
3669 END IF;
3670 IF l_mmtt_exists = 0 OR NOT (
3671 l_mol_list(i).move_order_line_id = l_cur_rec.move_order_line_id AND
3672 p_inventory_item_id = l_cur_rec.inventory_item_id AND
3673 p_transaction_uom = l_cur_rec.transaction_uom AND
3674 nvl(p_revision, '#$%') = nvl(l_cur_rec.revision, '#$%') AND
3675 nvl(p_lot_number, '#$%') = nvl(l_cur_rec.lot_number, '#$%')) THEN
3676
3677 IF l_debug = 1 THEN
3678 trace('Calling inv_trx_util_pub.insert_line_trx() to insert MMTT with ');
3679 trace(' p_trx_hdr_id => '||l_txn_hdr_id);
3680 trace(' p_item_id => '||p_inventory_item_id);
3681 trace(' p_revision => '||p_revision);
3682 trace(' p_org_id => '||p_organization_id);
3683 trace(' p_trx_action_id => '||l_txn_action_id);
3684 trace(' p_trx_type_id => '||l_txn_type_id);
3685 trace(' p_trx_src_type_id => 13');
3686 trace(' p_trx_qty => '||l_mol_list(i).transaction_quantity);
3687 trace(' p_pri_qty => '||l_mol_list(i).primary_quantity);
3688 trace(' p_uom => '||l_mol_list(i).transaction_uom);
3689 trace(' p_subinv_code => '||p_subinventory_code);
3690 trace(' p_tosubinv_code => '||p_to_subinventory);
3691 trace(' p_locator_id => '||p_locator_id);
3692 trace(' p_tolocator_id => '||l_new_tolocator_id);
3693 trace(' p_from_lpn_id => '||p_from_lpn_id);
3694 trace(' p_xfr_lpn_id => '||p_to_lpn_id);
3695 trace(' p_posting_flag => N');
3696 trace(' p_move_order_line_id => '||l_mol_list(i).move_order_line_id);
3697 trace(' p_user_id => '||fnd_global.user_id);
3698 trace(' p_secondary_trx_qty => '||l_mol_list(i).secondary_transaction_quantity);
3699 trace(' p_secondary_uom => '||l_mol_list(i).secondary_uom_code);
3700 END IF;
3701 -- Create new MMTT
3702 l_insert := inv_trx_util_pub.insert_line_trx(
3703 p_trx_hdr_id => l_txn_hdr_id
3704 ,p_item_id => p_inventory_item_id
3705 ,p_revision => p_revision
3706 ,p_org_id => p_organization_id
3707 ,p_trx_action_id => l_txn_action_id
3708 ,p_trx_type_id => l_txn_type_id
3709 ,p_trx_src_type_id => 13
3710 ,p_trx_qty => l_mol_list(i).transaction_quantity
3711 ,p_pri_qty => l_mol_list(i).primary_quantity
3712 ,p_uom => l_mol_list(i).transaction_uom
3713 ,p_secondary_trx_qty => l_mol_list(i).secondary_transaction_quantity --INVCONV kkillams
3714 ,p_secondary_uom => l_mol_list(i).secondary_uom_code --INVCONV kkillams
3715 ,p_subinv_code => p_subinventory_code
3716 ,p_tosubinv_code => p_to_subinventory
3717 ,p_locator_id => p_locator_id
3718 ,p_tolocator_id => l_new_tolocator_id --p_to_locator_id
3719 ,p_from_lpn_id => p_from_lpn_id
3720 ,p_xfr_lpn_id => p_to_lpn_id
3721 ,p_posting_flag => 'N' -- Set this so that locator capacity calculation will not consider this
3722 ,p_process_flag => 'N' -- Set process_flag to 'N' so that INV TM will not process this MMTT record
3723 ,p_move_order_line_id => l_mol_list(i).move_order_line_id
3724 ,p_user_id => fnd_global.user_id
3725 ,x_trx_tmp_id => l_txn_tmp_id
3726 ,x_proc_msg => l_proc_msg
3727 );
3728
3729 IF l_debug = 1 THEN
3730 trace('done with inserting , l_insert ='||l_insert||',mol='||l_mol_list(i).move_order_line_id);
3731 END IF;
3732 IF l_insert <> 0 THEN
3733 IF l_debug = 1 THEN
3734 trace('Error when inserting MMTT for move order line id:'||l_mol_list(i).move_order_line_id || 'err is '||l_proc_msg);
3735 END IF;
3736 x_proc_msg := l_proc_msg;
3737 raise fnd_api.g_exc_error;
3738 END IF;
3739
3740 IF l_txn_hdr_id IS NULL THEN
3741 l_txn_hdr_id := l_txn_tmp_id;
3742 END IF;
3743 IF l_debug = 1 THEN
3744 trace('MMTT inserted, tmp_id='||l_txn_tmp_id||', hdr_id='||l_txn_hdr_id);
3745 END IF;
3746
3747 IF p_lot_number IS NOT NULL THEN
3748 -- Create MTLT
3749 -- Check to see whether it's a new lot
3750 -- Get default attribute if it is a new lot
3751 l_new_lot := 0;
3752 BEGIN
3753 SELECT 1 INTO l_new_lot
3754 FROM mtl_lot_numbers
3755 WHERE organization_id = p_organization_id
3756 AND inventory_item_id = p_inventory_item_id
3757 AND lot_number = p_lot_number;
3758 EXCEPTION
3759 WHEN no_data_found THEN
3760 l_new_lot := 0;
3761 IF l_debug = 1 THEN
3762 trace('It is a new lot number');
3763 END IF;
3764 END;
3765
3766 IF l_new_lot = 0 THEN
3767 -- Get default lot attributes
3768 g_lot_ser_attr.delete;
3769 get_lot_ser_default_attribute(
3770 p_organization_id => p_organization_id
3771 , p_inventory_item_id => p_inventory_item_id
3772 , p_lot_serial => p_lot_number
3773 , p_lot_or_serial => 'LOT');
3774 IF l_debug = 1 THEN
3775 trace('Got lot default attr, no.of rec '||g_lot_ser_attr.count);
3776 END IF;
3777 END IF;
3778 -- Insert MTLT record
3779
3780 IF g_lot_ser_attr.count = 0 THEN
3781 -- No lot attribute
3782 IF l_debug = 1 THEN
3783 trace('Calling insert_lot_trx with ');
3784 trace(' p_trx_tmp_id => '||l_txn_tmp_id);
3785 trace(' p_lot_number => '||p_lot_number);
3786 trace(' p_exp_date => '|| p_lot_expiration_date);
3787 trace(' p_trx_qty => '|| l_mol_list(i).transaction_quantity);
3788 trace(' p_pri_qty => '||l_mol_list(i).primary_quantity);
3789 trace(' No lot attributes are passed in');
3790 END IF;
3791 l_insert := inv_trx_util_pub.insert_lot_trx(
3792 p_trx_tmp_id => l_txn_tmp_id
3793 , p_user_id => fnd_global.user_id
3794 , p_lot_number => p_lot_number
3795 , p_exp_date => p_lot_expiration_date
3796 , p_trx_qty => l_mol_list(i).transaction_quantity
3797 , p_pri_qty => l_mol_list(i).primary_quantity
3798 , p_secondary_qty => l_mol_list(i).secondary_transaction_quantity --INVCONV kkillams
3799 , p_grade_code => p_grade_code --INVCONV kkillams
3800 , x_ser_trx_id => l_ser_txn_id
3801 , x_proc_msg => l_proc_msg
3802 );
3803 IF l_insert <> 0 THEN
3804 IF l_debug = 1 THEN
3805 trace('Error when inserting MTLT for lot:'||p_lot_number||',l_proc_msg='||l_proc_msg);
3806 END IF;
3807 x_proc_msg := l_proc_msg;
3808 raise fnd_api.g_exc_error;
3809 END IF;
3810 IF l_debug = 1 THEN
3811 trace('MTLT record inserted for lot(no attr):'||p_lot_number||',ser_txn_id='||l_ser_txn_id);
3812 END IF;
3813 ELSE
3814 -- With lot attribute
3815 IF l_debug = 1 THEN
3816 trace('Calling insert_lot_trx with ');
3817 trace(' p_trx_tmp_id => '||l_txn_tmp_id);
3818 trace(' p_lot_number => '||p_lot_number);
3819 trace(' p_exp_date => '|| p_lot_expiration_date);
3820 trace(' p_trx_qty => '|| l_mol_list(i).transaction_quantity);
3821 trace(' p_pri_qty => '||l_mol_list(i).primary_quantity);
3822 trace(' Lot attributes are passed in');
3823 END IF;
3824 l_insert := inv_trx_util_pub.insert_lot_trx(
3825 p_trx_tmp_id => l_txn_tmp_id
3826 , p_user_id => fnd_global.user_id
3827 , p_lot_number => p_lot_number
3828 , p_exp_date => p_lot_expiration_date
3829 , p_trx_qty => l_mol_list(i).transaction_quantity
3830 , p_pri_qty => l_mol_list(i).primary_quantity
3831 , p_secondary_qty => l_mol_list(i).secondary_transaction_quantity --INVCONV kkillams
3832 , p_grade_code => l_mol_list(i).grade_code --INVCONV kkillams
3833 , x_ser_trx_id => l_ser_txn_id
3834 , x_proc_msg => l_proc_msg
3835 , p_age =>to_number(get_column_default_value('AGE'))
3836 , p_best_by_date =>to_date(get_column_default_value('BEST_BY_DATE'),G_DATE_MASK)
3837 , p_change_date =>to_date(get_column_default_value('CHANGE_DATE'),G_DATE_MASK)
3838 , p_color =>get_column_default_value('COLOR')
3839 , p_curl_wrinkle_fold =>get_column_default_value('CURL_WRINKLE_FOLD')
3840 , p_date_code =>get_column_default_value('DATE_CODE')
3841 , p_description =>get_column_default_value('DESCRIPTION')
3842 , p_item_size =>to_number(get_column_default_value('ITEM_SIZE'))
3843 , p_length =>to_number(get_column_default_value('LENGTH'))
3844 , p_length_uom =>get_column_default_value('LENGTH_UOM')
3845 , p_maturity_date =>to_date(get_column_default_value('MATURITY_DATE'),G_DATE_MASK)
3846 , p_origination_date =>to_date(get_column_default_value('ORIGINATION_DATE'),G_DATE_MASK)
3847 , p_place_of_origin =>get_column_default_value('PLACE_OF_ORIGIN')
3848 , p_recycled_content =>to_number(get_column_default_value('RECYCLED_CONTENT'))
3849 , p_retest_date =>to_date(get_column_default_value('RETEST_DATE'),G_DATE_MASK)
3850 , p_supplier_lot_number =>get_column_default_value('SUPPLIER_LOT_NUMBER')
3851 , p_territory_code =>get_column_default_value('TERRITORY_CODE')
3852 , p_thickness =>to_number(get_column_default_value('THICKNESS'))
3853 , p_thickness_uom =>get_column_default_value('THICKNESS_UOM')
3854 , p_vendor_id =>get_column_default_value('VENDOR_ID')
3855 , p_volume =>to_number(get_column_default_value('VOLUME'))
3856 , p_volume_uom =>get_column_default_value('VOLUME_UOM')
3857 , p_width =>to_number(get_column_default_value('WIDTH'))
3858 , p_width_uom =>to_number(get_column_default_value('WIDTH_UOM'))
3859 , p_lot_attribute_category=>get_column_default_value('LOT_ATTRIBUTE_CATEGORY')
3860 , p_c_attribute1 =>get_column_default_value('C_ATTRIBUTE1')
3861 , p_c_attribute2 =>get_column_default_value('C_ATTRIBUTE2')
3862 , p_c_attribute3 =>get_column_default_value('C_ATTRIBUTE3')
3863 , p_c_attribute4 =>get_column_default_value('C_ATTRIBUTE4')
3864 , p_c_attribute5 =>get_column_default_value('C_ATTRIBUTE5')
3865 , p_c_attribute6 =>get_column_default_value('C_ATTRIBUTE6')
3866 , p_c_attribute7 =>get_column_default_value('C_ATTRIBUTE7')
3867 , p_c_attribute8 =>get_column_default_value('C_ATTRIBUTE8')
3868 , p_c_attribute9 =>get_column_default_value('C_ATTRIBUTE9')
3869 , p_c_attribute10 =>get_column_default_value('C_ATTRIBUTE10')
3870 , p_c_attribute11 =>get_column_default_value('C_ATTRIBUTE11')
3871 , p_c_attribute12 =>get_column_default_value('C_ATTRIBUTE12')
3872 , p_c_attribute13 =>get_column_default_value('C_ATTRIBUTE13')
3873 , p_c_attribute14 =>get_column_default_value('C_ATTRIBUTE14')
3874 , p_c_attribute15 =>get_column_default_value('C_ATTRIBUTE15')
3875 , p_c_attribute16 =>get_column_default_value('C_ATTRIBUTE16')
3876 , p_c_attribute17 =>get_column_default_value('C_ATTRIBUTE17')
3877 , p_c_attribute18 =>get_column_default_value('C_ATTRIBUTE18')
3878 , p_c_attribute19 =>get_column_default_value('C_ATTRIBUTE19')
3879 , p_c_attribute20 =>get_column_default_value('C_ATTRIBUTE20')
3880 , p_d_attribute1 =>to_date(get_column_default_value('D_ATTRIBUTE1'),'YYYY/MM/DD:HH24:MI:SS')
3881 , p_d_attribute2 =>to_date(get_column_default_value('D_ATTRIBUTE2'),'YYYY/MM/DD:HH24:MI:SS')
3882 , p_d_attribute3 =>to_date(get_column_default_value('D_ATTRIBUTE3'),'YYYY/MM/DD:HH24:MI:SS')
3883 , p_d_attribute4 =>to_date(get_column_default_value('D_ATTRIBUTE4'),'YYYY/MM/DD:HH24:MI:SS')
3884 , p_d_attribute5 =>to_date(get_column_default_value('D_ATTRIBUTE5'),'YYYY/MM/DD:HH24:MI:SS')
3885 , p_d_attribute6 =>to_date(get_column_default_value('D_ATTRIBUTE6'),'YYYY/MM/DD:HH24:MI:SS')
3886 , p_d_attribute7 =>to_date(get_column_default_value('D_ATTRIBUTE7'),'YYYY/MM/DD:HH24:MI:SS')
3887 , p_d_attribute8 =>to_date(get_column_default_value('D_ATTRIBUTE8'),'YYYY/MM/DD:HH24:MI:SS')
3888 , p_d_attribute9 =>to_date(get_column_default_value('D_ATTRIBUTE9'),'YYYY/MM/DD:HH24:MI:SS')
3889 , p_d_attribute10 =>to_date(get_column_default_value('D_ATTRIBUTE10'),G_DATE_MASK)
3890 , p_n_attribute1 =>to_number(get_column_default_value('N_ATTRIBUTE1'))
3891 , p_n_attribute2 =>to_number(get_column_default_value('N_ATTRIBUTE2'))
3892 , p_n_attribute3 =>to_number(get_column_default_value('N_ATTRIBUTE3'))
3893 , p_n_attribute4 =>to_number(get_column_default_value('N_ATTRIBUTE4'))
3894 , p_n_attribute5 =>to_number(get_column_default_value('N_ATTRIBUTE5'))
3895 , p_n_attribute6 =>to_number(get_column_default_value('N_ATTRIBUTE6'))
3896 , p_n_attribute7 =>to_number(get_column_default_value('N_ATTRIBUTE7'))
3897 , p_n_attribute8 =>to_number(get_column_default_value('N_ATTRIBUTE8'))
3898 , p_n_attribute9 =>to_number(get_column_default_value('N_ATTRIBUTE9'))
3899 , p_n_attribute10 =>to_number(get_column_default_value('N_ATTRIBUTE10'))
3900 );
3901 IF l_insert <> 0 THEN
3902 IF l_debug = 1 THEN
3903 trace('Error when inserting MTLT for lot:'||p_lot_number||',l_proc_msg='||l_proc_msg);
3904 END IF;
3905 x_proc_msg := l_proc_msg;
3906 raise fnd_api.g_exc_error;
3907 END IF;
3908 IF l_debug = 1 THEN
3909 trace('MTLT record inserted for lot(with attr):'||p_lot_number||',ser_txn_id='||l_ser_txn_id);
3910 END IF;
3911 END IF;
3912
3913
3914 END IF;
3915
3916 IF p_fm_serial_number IS NOT NULL THEN
3917 -- Create MSNT
3918 -- Check to see whether it's a new serial
3919 -- Get default attribute if it is a new serial
3920 l_new_serial := 0;
3921 BEGIN
3922 SELECT 1 INTO l_new_serial
3923 FROM mtl_serial_numbers
3924 WHERE current_organization_id = p_organization_id
3925 AND inventory_item_id = p_inventory_item_id
3926 AND nvl(lot_number, '#$%') = nvl(p_lot_number, nvl(lot_number, '#$%'))
3927 AND serial_number = p_fm_serial_number;
3928 EXCEPTION
3929 WHEN no_data_found THEN
3930 l_new_serial := 0;
3931 END;
3932
3933 IF l_new_serial = 0 THEN
3934 IF l_debug = 1 THEN
3935 trace('New Serial number, get default attributes');
3936 END IF;
3937 -- Get default serial attributes
3938 g_lot_ser_attr.delete;
3939 get_lot_ser_default_attribute(
3940 p_organization_id => p_organization_id
3941 , p_inventory_item_id => p_inventory_item_id
3942 , p_lot_serial => p_fm_serial_number
3943 , p_lot_or_serial => 'SERIAL');
3944 IF l_debug = 1 THEN
3945 trace('Got serial default attr, no.of rec '||g_lot_ser_attr.count);
3946 END IF;
3947 END IF;
3948 -- Insert MSNT record
3949 IF g_lot_ser_attr.count = 0 THEN
3950 -- No serial attributes
3951 l_insert := inv_trx_util_pub.insert_ser_trx(
3952 p_trx_tmp_id => nvl(l_ser_txn_id,l_txn_tmp_id)
3953 , p_user_id => fnd_global.user_id
3954 , p_fm_ser_num => p_fm_serial_number
3955 , p_to_ser_num => p_fm_serial_number
3956 , x_proc_msg => l_proc_msg
3957 );
3958 IF l_insert <> 0 THEN
3959 IF l_debug = 1 THEN
3960 trace('Error when inserting MSNT for serial(no attr):'||p_fm_serial_number||',l_proc_msg='||l_proc_msg);
3961 END IF;
3962 x_proc_msg := l_proc_msg;
3963 raise fnd_api.g_exc_error;
3964 END IF;
3965 IF l_debug = 1 THEN
3966 trace('MSNT record inserted for serial(no attr):'||p_fm_serial_number||',ser_txn_id='||nvl(l_ser_txn_id,l_txn_tmp_id));
3967 END IF;
3968 ELSE
3969 -- Has serial attributes
3970 l_insert := inv_trx_util_pub.insert_ser_trx(
3971 p_trx_tmp_id => nvl(l_ser_txn_id,l_txn_tmp_id)
3972 , p_user_id => fnd_global.user_id
3973 , p_fm_ser_num => p_fm_serial_number
3974 , p_to_ser_num => p_fm_serial_number
3975 , x_proc_msg => l_proc_msg
3976 , p_time_since_new =>to_number(get_column_default_value('TIME_SINCE_NEW'))
3977 , p_cycles_since_new =>to_number(get_column_default_value('CYCLES_SINCE_NEW'))
3978 , p_time_since_overhaul =>to_number(get_column_default_value('TIME_SINCE_OVERHAUL'))
3979 , p_cycles_since_overhaul=>to_number(get_column_default_value('CYCLES_SINCE_OVERHAUL'))
3980 , p_time_since_repair =>to_number(get_column_default_value('TIME_SINCE_REPAIR'))
3981 , p_cycles_since_repair =>to_number(get_column_default_value('CYCLES_SINCE_REPAIR'))
3982 , p_time_since_visit =>to_number(get_column_default_value('TIME_SINCE_VISIT'))
3983 , p_cycles_since_visit =>to_number(get_column_default_value('CYCLES_SINCE_VISIT'))
3984 , p_time_since_mark =>to_number(get_column_default_value('TIME_SINCE_MARK'))
3985 , p_cycles_since_mark =>to_number(get_column_default_value('CYCLES_SINCE_MARK'))
3986 , p_number_of_repairs =>to_number(get_column_default_value('NUMBER_OF_REPAIRS'))
3987 , p_territory_code =>to_number(get_column_default_value('TERRITORY_CODE'))
3988 , p_orgination_date =>to_date(get_column_default_value('ORIGINATION_DATE'),G_DATE_MASK)
3989 , p_serial_attribute_category =>get_column_default_value('SERIAL_ATTRIBUTE_CATEGORY')
3990 , p_c_attribute1 =>get_column_default_value('C_ATTRIBUTE1')
3991 , p_c_attribute2 =>get_column_default_value('C_ATTRIBUTE2')
3992 , p_c_attribute3 =>get_column_default_value('C_ATTRIBUTE3')
3993 , p_c_attribute4 =>get_column_default_value('C_ATTRIBUTE4')
3994 , p_c_attribute5 =>get_column_default_value('C_ATTRIBUTE5')
3995 , p_c_attribute6 =>get_column_default_value('C_ATTRIBUTE6')
3996 , p_c_attribute7 =>get_column_default_value('C_ATTRIBUTE7')
3997 , p_c_attribute8 =>get_column_default_value('C_ATTRIBUTE8')
3998 , p_c_attribute9 =>get_column_default_value('C_ATTRIBUTE9')
3999 , p_c_attribute10 =>get_column_default_value('C_ATTRIBUTE10')
4000 , p_c_attribute11 =>get_column_default_value('C_ATTRIBUTE11')
4001 , p_c_attribute12 =>get_column_default_value('C_ATTRIBUTE12')
4002 , p_c_attribute13 =>get_column_default_value('C_ATTRIBUTE13')
4003 , p_c_attribute14 =>get_column_default_value('C_ATTRIBUTE14')
4004 , p_c_attribute15 =>get_column_default_value('C_ATTRIBUTE15')
4005 , p_c_attribute16 =>get_column_default_value('C_ATTRIBUTE16')
4006 , p_c_attribute17 =>get_column_default_value('C_ATTRIBUTE17')
4007 , p_c_attribute18 =>get_column_default_value('C_ATTRIBUTE18')
4008 , p_c_attribute19 =>get_column_default_value('C_ATTRIBUTE19')
4009 , p_c_attribute20 =>get_column_default_value('C_ATTRIBUTE20')
4010 , p_d_attribute1 =>to_date(get_column_default_value('D_ATTRIBUTE1'),'YYYY/MM/DD:HH24:MI:SS')
4011 , p_d_attribute2 =>to_date(get_column_default_value('D_ATTRIBUTE2'),'YYYY/MM/DD:HH24:MI:SS')
4012 , p_d_attribute3 =>to_date(get_column_default_value('D_ATTRIBUTE3'),'YYYY/MM/DD:HH24:MI:SS')
4013 , p_d_attribute4 =>to_date(get_column_default_value('D_ATTRIBUTE4'),'YYYY/MM/DD:HH24:MI:SS')
4014 , p_d_attribute5 =>to_date(get_column_default_value('D_ATTRIBUTE5'),'YYYY/MM/DD:HH24:MI:SS')
4015 , p_d_attribute6 =>to_date(get_column_default_value('D_ATTRIBUTE6'),'YYYY/MM/DD:HH24:MI:SS')
4016 , p_d_attribute7 =>to_date(get_column_default_value('D_ATTRIBUTE7'),'YYYY/MM/DD:HH24:MI:SS')
4017 , p_d_attribute8 =>to_date(get_column_default_value('D_ATTRIBUTE8'),'YYYY/MM/DD:HH24:MI:SS')
4018 , p_d_attribute9 =>to_date(get_column_default_value('D_ATTRIBUTE9'),'YYYY/MM/DD:HH24:MI:SS')
4019 , p_d_attribute10 =>to_date(get_column_default_value('D_ATTRIBUTE10'),'YYYY/MM/DD:HH24:MI:SS')
4020 , p_n_attribute1 =>to_number(get_column_default_value('N_ATTRIBUTE1'))
4021 , p_n_attribute2 =>to_number(get_column_default_value('N_ATTRIBUTE2'))
4022 , p_n_attribute3 =>to_number(get_column_default_value('N_ATTRIBUTE3'))
4023 , p_n_attribute4 =>to_number(get_column_default_value('N_ATTRIBUTE4'))
4024 , p_n_attribute5 =>to_number(get_column_default_value('N_ATTRIBUTE5'))
4025 , p_n_attribute6 =>to_number(get_column_default_value('N_ATTRIBUTE6'))
4026 , p_n_attribute7 =>to_number(get_column_default_value('N_ATTRIBUTE7'))
4027 , p_n_attribute8 =>to_number(get_column_default_value('N_ATTRIBUTE8'))
4028 , p_n_attribute9 =>to_number(get_column_default_value('N_ATTRIBUTE9'))
4029 , p_n_attribute10 =>to_number(get_column_default_value('N_ATTRIBUTE10'))
4030 );
4031 IF l_insert <> 0 THEN
4032 IF l_debug = 1 THEN
4033 trace('Error when inserting MSNT for serial:'||p_fm_serial_number||',l_proc_msg='||l_proc_msg);
4034 END IF;
4035 x_proc_msg := l_proc_msg;
4036 raise fnd_api.g_exc_error;
4037 END IF;
4038 trace('MSNT record inserted for serial(with attr):'||p_fm_serial_number||',ser_txn_id='||nvl(l_ser_txn_id,l_txn_tmp_id));
4039
4040 END IF;
4041 x_serial_transaction_temp_id := nvl(l_ser_txn_id,l_txn_tmp_id);
4042 -- Mark Serial Number
4043 BEGIN
4044 UPDATE mtl_serial_numbers
4045 SET GROUP_MARK_ID = l_txn_tmp_id
4046 WHERE current_organization_id = p_organization_id
4047 AND inventory_item_id = p_inventory_item_id
4048 --AND nvl(lot_number, '#$%') = nvl(p_lot_number, nvl(lot_number, '#$%'))
4049 AND serial_number = p_fm_serial_number;
4050 IF l_debug = 1 THEN
4051 trace(SQL%ROWCOUNT||' records updated for serial number '||p_fm_serial_number||' for group_mark_id as '||l_txn_tmp_id);
4052 END IF;
4053 EXCEPTION
4054 WHEN others THEN
4055 IF l_debug = 1 THEN
4056 trace('Error when update MSN with group_mark_id='||l_txn_tmp_id||',SN='||p_fm_serial_number);
4057 END IF;
4058 raise fnd_api.g_exc_error;
4059 END;
4060
4061 END IF;
4062 -- Mark WMS_PROCESS_FLAG for the move order line
4063 BEGIN
4064 UPDATE mtl_txn_request_lines
4065 SET WMS_PROCESS_FLAG = 2
4066 WHERE line_id = l_mol_list(i).move_order_line_id;
4067 EXCEPTION
4068 WHEN others THEN
4069 IF l_debug = 1 THEN
4070 trace('Error when updating wms_process_flag for mo line:'||l_mol_list(i).move_order_line_id);
4071 END IF;
4072 raise fnd_api.g_exc_error;
4073 END;
4074
4075 x_transaction_header_id := l_txn_hdr_id;
4076 x_transaction_temp_id := l_txn_tmp_id;
4077 ELSE
4078 -- Update existing MMTT
4079 BEGIN
4080 UPDATE mtl_material_transactions_temp
4081 SET transaction_quantity = transaction_quantity + l_mol_list(i).transaction_quantity,
4082 primary_quantity = primary_quantity + l_mol_list(i).primary_quantity,
4083 secondary_transaction_quantity = CASE WHEN l_mol_list(i).secondary_transaction_quantity IS NOT NULL
4084 THEN l_mol_list(i).secondary_transaction_quantity + secondary_transaction_quantity
4085 ELSE secondary_transaction_quantity
4086 END --INVCONV kkillams
4087 WHERE transaction_temp_id = p_transaction_temp_id;
4088 EXCEPTION
4089 WHEN others THEN
4090 IF l_debug = 1 THEN
4091 trace('Error when updating MMTT rec,tmp_id='||p_transaction_temp_id);
4092 END IF;
4093 raise fnd_api.g_exc_error;
4094 END;
4095 IF l_debug = 1 THEN
4096 trace('MMTT updated for tmp_id '||p_transaction_temp_id);
4097 END IF;
4098
4099 -- Update MTLT
4100 IF p_lot_number IS NOT NULL THEN
4101 BEGIN
4102 UPDATE mtl_transaction_lots_temp
4103 SET transaction_quantity = transaction_quantity + l_mol_list(i).transaction_quantity,
4104 primary_quantity = primary_quantity + l_mol_list(i).primary_quantity,
4105 secondary_quantity = CASE WHEN l_mol_list(i).secondary_transaction_quantity IS NOT NULL
4106 THEN l_mol_list(i).secondary_transaction_quantity + secondary_quantity
4107 ELSE secondary_quantity
4108 END --INVCONV kkillams
4109 WHERE transaction_temp_id = p_transaction_temp_id
4110 AND lot_number = p_lot_number;
4111 EXCEPTION
4112 WHEN others THEN
4113 IF l_debug = 1 THEN
4114 trace('Error when updating MTLT rec,tmp_id='||p_transaction_temp_id);
4115 END IF;
4116 raise fnd_api.g_exc_error;
4117 END;
4118 IF l_debug = 1 THEN
4119 trace('MTLT updated for tmp_id '||p_transaction_temp_id);
4120 END IF;
4121 END IF;
4122
4123 -- Create MSNT record
4124 IF p_fm_serial_number IS NOT NULL THEN
4125 -- Create MSNT
4126 -- Check to see whether it's a new serial
4127 -- Get default attribute if it is a new serial
4128 l_new_serial := 0;
4129 BEGIN
4130 SELECT 1 INTO l_new_serial
4131 FROM mtl_serial_numbers
4132 WHERE current_organization_id = p_organization_id
4133 AND inventory_item_id = p_inventory_item_id
4134 AND nvl(lot_number, '#$%') = nvl(p_lot_number, nvl(lot_number, '#$%'))
4135 AND serial_number = p_fm_serial_number;
4136 EXCEPTION
4137 WHEN no_data_found THEN
4138 l_new_serial := 0;
4139 END;
4140
4141 IF l_new_serial = 0 THEN
4142 IF l_debug = 1 THEN
4143 trace('New serial number, getting attributes');
4144 END IF;
4145
4146 -- Get default serial attributes
4147 g_lot_ser_attr.delete;
4148 get_lot_ser_default_attribute(
4149 p_organization_id => p_organization_id
4150 , p_inventory_item_id => p_inventory_item_id
4151 , p_lot_serial => p_fm_serial_number
4152 , p_lot_or_serial => 'SERIAL');
4153 IF l_debug = 1 THEN
4154 trace('Got serial default attr, no.of rec '||g_lot_ser_attr.count);
4155 END IF;
4156 END IF;
4157 -- Insert MSNT record
4158 IF g_lot_ser_attr.count = 0 THEN
4159 -- No serial attributes
4160 l_insert := inv_trx_util_pub.insert_ser_trx(
4161 p_trx_tmp_id => nvl(l_cur_rec.serial_transaction_temp_id, p_transaction_temp_id)
4162 , p_user_id => fnd_global.user_id
4163 , p_fm_ser_num => p_fm_serial_number
4164 , p_to_ser_num => p_fm_serial_number
4165 , x_proc_msg => l_proc_msg
4166 );
4167 IF l_insert <> 0 THEN
4168 IF l_debug = 1 THEN
4169 trace('Error when inserting MSNT for serial(no attr):'||p_fm_serial_number||',l_proc_msg='||l_proc_msg);
4170 END IF;
4171 x_proc_msg := l_proc_msg;
4172 raise fnd_api.g_exc_error;
4173 END IF;
4174 IF l_debug = 1 THEN
4175 trace('MSNT record inserted for serial(no attr):'||p_fm_serial_number||',ser_txn_id='||nvl(l_ser_txn_id,l_txn_tmp_id));
4176 END IF;
4177 ELSE
4178 -- Has serial attributes
4179 l_insert := inv_trx_util_pub.insert_ser_trx(
4180 p_trx_tmp_id => nvl(l_cur_rec.serial_transaction_temp_id, p_transaction_temp_id)
4181 , p_user_id => fnd_global.user_id
4182 , p_fm_ser_num => p_fm_serial_number
4183 , p_to_ser_num => p_fm_serial_number
4184 , x_proc_msg => l_proc_msg
4185 , p_time_since_new =>to_number(get_column_default_value('TIME_SINCE_NEW'))
4186 , p_cycles_since_new =>to_number(get_column_default_value('CYCLES_SINCE_NEW'))
4187 , p_time_since_overhaul =>to_number(get_column_default_value('TIME_SINCE_OVERHAUL'))
4188 , p_cycles_since_overhaul=>to_number(get_column_default_value('CYCLES_SINCE_OVERHAUL'))
4189 , p_time_since_repair =>to_number(get_column_default_value('TIME_SINCE_REPAIR'))
4190 , p_cycles_since_repair =>to_number(get_column_default_value('CYCLES_SINCE_REPAIR'))
4191 , p_time_since_visit =>to_number(get_column_default_value('TIME_SINCE_VISIT'))
4192 , p_cycles_since_visit =>to_number(get_column_default_value('CYCLES_SINCE_VISIT'))
4193 , p_time_since_mark =>to_number(get_column_default_value('TIME_SINCE_MARK'))
4194 , p_cycles_since_mark =>to_number(get_column_default_value('CYCLES_SINCE_MARK'))
4195 , p_number_of_repairs =>to_number(get_column_default_value('NUMBER_OF_REPAIRS'))
4196 , p_territory_code =>to_number(get_column_default_value('TERRITORY_CODE'))
4197 , p_orgination_date =>to_date(get_column_default_value('ORIGINATION_DATE'),G_DATE_MASK)
4198 , p_serial_attribute_category =>get_column_default_value('SERIAL_ATTRIBUTE_CATEGORY')
4199 , p_c_attribute1 =>get_column_default_value('C_ATTRIBUTE1')
4200 , p_c_attribute2 =>get_column_default_value('C_ATTRIBUTE2')
4201 , p_c_attribute3 =>get_column_default_value('C_ATTRIBUTE3')
4202 , p_c_attribute4 =>get_column_default_value('C_ATTRIBUTE4')
4203 , p_c_attribute5 =>get_column_default_value('C_ATTRIBUTE5')
4204 , p_c_attribute6 =>get_column_default_value('C_ATTRIBUTE6')
4205 , p_c_attribute7 =>get_column_default_value('C_ATTRIBUTE7')
4206 , p_c_attribute8 =>get_column_default_value('C_ATTRIBUTE8')
4207 , p_c_attribute9 =>get_column_default_value('C_ATTRIBUTE9')
4208 , p_c_attribute10 =>get_column_default_value('C_ATTRIBUTE10')
4209 , p_c_attribute11 =>get_column_default_value('C_ATTRIBUTE11')
4210 , p_c_attribute12 =>get_column_default_value('C_ATTRIBUTE12')
4211 , p_c_attribute13 =>get_column_default_value('C_ATTRIBUTE13')
4212 , p_c_attribute14 =>get_column_default_value('C_ATTRIBUTE14')
4213 , p_c_attribute15 =>get_column_default_value('C_ATTRIBUTE15')
4214 , p_c_attribute16 =>get_column_default_value('C_ATTRIBUTE16')
4215 , p_c_attribute17 =>get_column_default_value('C_ATTRIBUTE17')
4216 , p_c_attribute18 =>get_column_default_value('C_ATTRIBUTE18')
4217 , p_c_attribute19 =>get_column_default_value('C_ATTRIBUTE19')
4218 , p_c_attribute20 =>get_column_default_value('C_ATTRIBUTE20')
4219 , p_d_attribute1 =>to_date(get_column_default_value('D_ATTRIBUTE1'),'YYYY/MM/DD:HH24:MI:SS')
4220 , p_d_attribute2 =>to_date(get_column_default_value('D_ATTRIBUTE2'),'YYYY/MM/DD:HH24:MI:SS')
4221 , p_d_attribute3 =>to_date(get_column_default_value('D_ATTRIBUTE3'),'YYYY/MM/DD:HH24:MI:SS')
4222 , p_d_attribute4 =>to_date(get_column_default_value('D_ATTRIBUTE4'),'YYYY/MM/DD:HH24:MI:SS')
4223 , p_d_attribute5 =>to_date(get_column_default_value('D_ATTRIBUTE5'),'YYYY/MM/DD:HH24:MI:SS')
4224 , p_d_attribute6 =>to_date(get_column_default_value('D_ATTRIBUTE6'),'YYYY/MM/DD:HH24:MI:SS')
4225 , p_d_attribute7 =>to_date(get_column_default_value('D_ATTRIBUTE7'),'YYYY/MM/DD:HH24:MI:SS')
4226 , p_d_attribute8 =>to_date(get_column_default_value('D_ATTRIBUTE8'),'YYYY/MM/DD:HH24:MI:SS')
4227 , p_d_attribute9 =>to_date(get_column_default_value('D_ATTRIBUTE9'),'YYYY/MM/DD:HH24:MI:SS')
4228 , p_d_attribute10 =>to_date(get_column_default_value('D_ATTRIBUTE10'),'YYYY/MM/DD:HH24:MI:SS')
4229 , p_n_attribute1 =>to_number(get_column_default_value('N_ATTRIBUTE1'))
4230 , p_n_attribute2 =>to_number(get_column_default_value('N_ATTRIBUTE2'))
4231 , p_n_attribute3 =>to_number(get_column_default_value('N_ATTRIBUTE3'))
4232 , p_n_attribute4 =>to_number(get_column_default_value('N_ATTRIBUTE4'))
4233 , p_n_attribute5 =>to_number(get_column_default_value('N_ATTRIBUTE5'))
4234 , p_n_attribute6 =>to_number(get_column_default_value('N_ATTRIBUTE6'))
4235 , p_n_attribute7 =>to_number(get_column_default_value('N_ATTRIBUTE7'))
4236 , p_n_attribute8 =>to_number(get_column_default_value('N_ATTRIBUTE8'))
4237 , p_n_attribute9 =>to_number(get_column_default_value('N_ATTRIBUTE9'))
4238 , p_n_attribute10 =>to_number(get_column_default_value('N_ATTRIBUTE10'))
4239 );
4240 IF l_insert <> 0 THEN
4241 IF l_debug = 1 THEN
4242 trace('Error when inserting MSNT for serial(with attr):'||p_fm_serial_number||',l_proc_msg='||l_proc_msg);
4243 END IF;
4244 x_proc_msg := l_proc_msg;
4245 raise fnd_api.g_exc_error;
4246 END IF;
4247 trace('MSNT record inserted for serial(with attr):'||p_fm_serial_number||',ser_txn_id='||nvl(l_ser_txn_id,l_txn_tmp_id));
4248 END IF;
4249 /* l_insert := inv_trx_util_pub.insert_ser_trx(
4250 p_trx_tmp_id => nvl(l_cur_rec.serial_transaction_temp_id, p_transaction_temp_id)
4251 , p_user_id => fnd_global.user_id
4252 , p_fm_ser_num => p_fm_serial_number
4253 , p_to_ser_num => p_fm_serial_number
4254 , x_proc_msg => l_proc_msg
4255 );
4256 IF l_insert <> 0 THEN
4257 IF l_debug = 1 THEN
4258 trace('Error when inserting MSNT for serial:'||p_fm_serial_number||',l_proc_msg='||l_proc_msg);
4259 END IF;
4260 x_proc_msg := l_proc_msg;
4261 raise fnd_api.g_exc_error;
4262 END IF;
4263 IF l_debug = 1 THEN
4264 trace('MSNT record inserted for serial:'||p_fm_serial_number||',ser_txn_id='||
4265 nvl(l_cur_rec.serial_transaction_temp_id, p_transaction_temp_id));
4266 END IF;*/
4267 x_serial_transaction_temp_id := nvl(l_cur_rec.serial_transaction_temp_id, p_transaction_temp_id);
4268 -- Mark Serial Number
4269 BEGIN
4270 UPDATE mtl_serial_numbers
4271 SET GROUP_MARK_ID = p_transaction_temp_id
4272 WHERE current_organization_id = p_organization_id
4273 AND inventory_item_id = p_inventory_item_id
4274 --AND nvl(lot_number, '#$%') = nvl(p_lot_number, nvl(lot_number, '#$%'))
4275 AND serial_number = p_fm_serial_number;
4276 IF l_debug = 1 THEN
4277 trace(SQL%ROWCOUNT||' records updated for serial number '||p_fm_serial_number||' for group_mark_id as '||p_transaction_temp_id);
4278 END IF;
4279
4280 EXCEPTION
4281 WHEN others THEN
4282 IF l_debug = 1 THEN
4283 trace('Error when update MSN with group_mark_id='||l_txn_tmp_id||',SN='||p_fm_serial_number);
4284 END IF;
4285 raise fnd_api.g_exc_error;
4286 END;
4287
4288 END IF; -- END SN is not null
4289 x_transaction_header_id := p_transaction_header_id;
4290 x_transaction_temp_id := p_transaction_temp_id;
4291
4292 END IF; -- End Create new MMTT or update MMTT
4293
4294 -- Update WMS_PACKING_MATERIAL_GTEMP
4295 IF l_debug = 1 THEN
4296 trace('Updating wpmg to decrease qty '||inv_convert.inv_um_convert(
4297 p_inventory_item_id,null,l_mol_list(i).transaction_quantity,l_mol_list(i).transaction_uom,'Ea',null,null));
4298 END IF;
4299 BEGIN
4300 UPDATE wms_packing_material_gtemp
4301 SET selected_flag = 'Y',
4302 quantity = quantity - inv_convert.inv_um_convert(
4303 inventory_item_id,null,l_mol_list(i).transaction_quantity,l_mol_list(i).transaction_uom,uom,null,null),
4304 secondary_quantity = CASE WHEN l_mol_list(i).secondary_transaction_quantity IS NOT NULL
4305 THEN secondary_quantity - l_mol_list(i).secondary_transaction_quantity
4306 ELSE secondary_quantity
4307 END --INVCONV kkillams
4308 WHERE move_order_line_id = l_mol_list(i).move_order_line_id;
4309 EXCEPTION
4310 WHEN others THEN
4311 IF l_debug = 1 THEN
4312 trace('Error when updating wms_packing_material_gtemp for mol:'||l_mol_list(i).move_order_line_id);
4313 END IF;
4314 raise fnd_api.g_exc_error;
4315 END;
4316
4317 END LOOP; -- Move order lines loop
4318
4319 ELSE
4320 IF l_debug = 1 THEN
4321 trace('Content has to be either lpn or item');
4322 END IF;
4323 raise fnd_api.g_exc_error;
4324 END IF;
4325
4326
4327 ELSIF p_source = 2 THEN
4328 --Outbound
4329 -- Logic to create/update MMTT/MTLT/MSNT is
4330 -- For vanilla item, already create new MMTT
4331 -- For rev or lot or serial item, try to update previous MMTT
4332 -- but always create new MTLT/MSNT
4333
4334 IF l_debug = 1 THEN
4335 trace('Create txn record for Outbound');
4336 END IF;
4337 -- Check whether MMTT/MTLT exists for the same item/lot
4338 l_mmtt_exists := 0;
4339 IF p_transaction_header_id IS NOT NULL and p_transaction_temp_id IS NOT NULL and
4340 p_revision IS NULL and (p_lot_number IS NOT NULL or p_fm_serial_number IS NOT NULL) THEN
4341 BEGIN
4342 SELECT 1
4343 INTO l_mmtt_exists
4344 FROM mtl_material_transactions_temp mmtt
4345 WHERE mmtt.transaction_header_id = p_transaction_header_id
4346 AND mmtt.transaction_temp_id = p_transaction_temp_id
4347 AND mmtt.content_lpn_id IS NULL
4348 AND mmtt.inventory_item_id = p_inventory_item_id
4349 AND mmtt.transaction_uom = p_transaction_uom
4350 AND mmtt.lpn_id = p_from_lpn_id
4351 AND nvl(mmtt.secondary_uom_code, '@#$') = nvl(p_secondary_uom,nvl(mmtt.secondary_uom_code, '@#$'));
4352 EXCEPTION
4353 WHEN no_data_found THEN
4354 l_mmtt_exists:=0;
4355 END;
4356 END IF;
4357
4358 IF l_debug = 1 THEN
4359 trace('l_mmtt_exists='||l_mmtt_exists);
4360 END IF;
4361
4362 -- For outbound split transaction without move
4363 -- Transfer Sub/loc should be null
4364 l_to_sub := p_to_subinventory;
4365 l_to_loc_id := l_new_tolocator_id; --p_to_locator_id
4366 IF p_subinventory_code = p_to_subinventory AND
4367 p_locator_id = l_new_tolocator_id THEN
4368 -- No move transaction
4369 IF p_pack_process = 1 THEN
4370 l_txn_action_id := 50;
4371 l_txn_type_id := 87;
4372 ELSIF p_pack_process = 2 THEN
4373 l_txn_action_id := 52;
4374 l_txn_type_id := 89;
4375 l_to_sub := null;
4376 l_to_loc_id := null;
4377 ELSIF p_pack_process = 3 THEN
4378 l_txn_action_id := 51;
4379 l_txn_type_id := 88;
4380 ELSE
4381 fnd_message.set_name('INV','INV_INT_TRXACTCODE');
4382 fnd_msg_pub.add;
4383 raise fnd_api.g_exc_error;
4384 END IF;
4385 ELSE
4386 -- There is move happens
4387 -- Use subinventory transfer
4388 l_txn_action_id := 2;
4389 l_txn_type_id := 2;
4390 END IF;
4391
4392 IF l_debug = 1 THEN
4393 trace('trx action='||l_txn_action_id||',trx type='||l_txn_type_id);
4394 END IF;
4395
4396 IF p_transaction_header_id IS NULL THEN
4397 SELECT mtl_material_transactions_s.NEXTVAL
4398 INTO l_txn_hdr_id
4399 FROM dual;
4400 ELSE
4401 l_txn_hdr_id := p_transaction_header_id;
4402 END IF;
4403 x_transaction_header_id := l_txn_hdr_id;
4404
4405 IF p_content_lpn_id IS NOT NULL THEN
4406 -- Content is LPN
4407 -- Create new MMTT
4408 IF l_debug = 1 THEN
4409 trace('Calling inv_trx_util_pub.insert_line_trx() to insert MMTT with ');
4410 trace(' p_trx_hdr_id => '||l_txn_hdr_id);
4411 trace(' p_item_id => null');
4412 trace(' p_org_id => '||p_organization_id);
4413 trace(' p_trx_action_id => '||l_txn_action_id);
4414 trace(' p_trx_type_id => '||l_txn_type_id);
4415 trace(' p_trx_src_type_id => 13');
4416 trace(' p_trx_qty => 0');
4417 trace(' p_pri_qty => 0');
4418 trace(' p_uom => '||nvl(p_transaction_uom, ' '));
4419 trace(' p_subinv_code => '||p_subinventory_code);
4420 trace(' p_tosubinv_code => '||l_to_sub);
4421 trace(' p_locator_id => '||p_locator_id);
4422 trace(' p_tolocator_id => '||l_to_loc_id);
4423 trace(' p_from_lpn_id => '||p_from_lpn_id);
4424 trace(' p_cnt_lpn_id => '||p_content_lpn_id);
4425 trace(' p_xfr_lpn_id => '||p_to_lpn_id);
4426 trace(' p_user_id => '||fnd_global.user_id);
4427 END IF;
4428
4429 l_insert := inv_trx_util_pub.insert_line_trx(
4430 p_trx_hdr_id => l_txn_hdr_id
4431 ,p_item_id => null
4432 ,p_org_id => p_organization_id
4433 ,p_trx_action_id => l_txn_action_id
4434 ,p_trx_type_id => l_txn_type_id
4435 ,p_trx_src_type_id => 13
4436 ,p_trx_qty => 0
4437 ,p_pri_qty => 0
4438 ,p_uom => nvl(p_transaction_uom, ' ')
4439 ,p_secondary_trx_qty => CASE WHEN p_secondary_uom IS NOT NULL THEN 0 ELSE NULL END --INVCONV kkillams
4440 ,p_secondary_uom => nvl(p_secondary_uom, ' ') --INVCONV kkillams
4441 ,p_subinv_code => p_subinventory_code
4442 ,p_tosubinv_code => l_to_sub
4443 ,p_xfr_org_id => p_organization_id
4444 ,p_locator_id => p_locator_id
4445 ,p_tolocator_id => l_to_loc_id
4446 ,p_from_lpn_id => p_from_lpn_id
4447 ,p_cnt_lpn_id => p_content_lpn_id
4448 ,p_xfr_lpn_id => p_to_lpn_id
4449 ,p_user_id => fnd_global.user_id
4450 ,x_trx_tmp_id => l_txn_tmp_id
4451 ,x_proc_msg => l_proc_msg);
4452
4453 IF l_debug = 1 THEN
4454 trace('done with inserting , l_insert ='||l_insert);
4455 END IF;
4456 IF l_insert <> 0 THEN
4457 IF l_debug = 1 THEN
4458 trace('Error when inserting MMTT for content lpn ID:'||p_content_lpn_id|| 'err is '||l_proc_msg);
4459 END IF;
4460 x_proc_msg := l_proc_msg;
4461 raise fnd_api.g_exc_error;
4462 END IF;
4463
4464 IF l_debug = 1 THEN
4465 trace('MMTT inserted, hdr_id='||l_txn_hdr_id|| ',tmp_id='||l_txn_tmp_id);
4466 END IF;
4467
4468 -- Update wms_packing_material_gtemp
4469 BEGIN
4470 IF p_from_lpn_id IS NOT NULL THEN
4471 -- Split, Unpack
4472 UPDATE wms_packing_material_gtemp
4473 SET selected_flag = 'D' -- Deleted
4474 WHERE (lpn_id = p_content_lpn_id AND parent_lpn_id = p_from_lpn_id) OR (parent_lpn_id = p_content_lpn_id AND parent_lpn_id <> outermost_lpn_id);
4475 ELSE
4476 -- Pack
4477 UPDATE wms_packing_material_gtemp
4478 SET selected_flag = 'D'
4479 WHERE outermost_lpn_id = p_content_lpn_id;
4480 END IF;
4481 EXCEPTION
4482 WHEN others THEN
4483 IF l_debug = 1 THEN
4484 trace('Error when deleting from wms_packing_material_gtemp for content lpn '||p_content_lpn_id);
4485 END IF;
4486 x_proc_msg := l_proc_msg;
4487 raise fnd_api.g_exc_error;
4488 END;
4489
4490 ELSIF p_inventory_item_id IS NOT NULL THEN
4491 -- Content is Item
4492 -- Check whether need to create new MMTT or update existing MMTT
4493
4494 IF l_mmtt_exists = 0 THEN
4495 -- Create new MMTT
4496
4497 IF l_debug = 1 THEN
4498 trace('Calling inv_trx_util_pub.insert_line_trx() to insert MMTT with ');
4499 trace(' p_trx_hdr_id => '||l_txn_hdr_id);
4500 trace(' p_item_id => '||p_inventory_item_id);
4501 trace(' p_revision => '||p_revision);
4502 trace(' p_org_id => '||p_organization_id);
4503 trace(' p_trx_action_id => '||l_txn_action_id);
4504 trace(' p_trx_type_id => '||l_txn_type_id);
4505 trace(' p_trx_src_type_id => 13');
4506 trace(' p_trx_qty => '||p_transaction_qty);
4507 trace(' p_pri_qty => '||p_primary_qty);
4508 trace(' p_uom => '||p_transaction_uom);
4509 trace(' p_subinv_code => '||p_subinventory_code);
4510 trace(' p_tosubinv_code => '||l_to_sub);
4511 trace(' p_locator_id => '||p_locator_id);
4512 trace(' p_tolocator_id => '||l_to_loc_id);
4513 trace(' p_from_lpn_id => '||p_from_lpn_id);
4514 trace(' p_xfr_lpn_id => '||p_to_lpn_id);
4515 trace(' p_secondary_trx_qty => '||p_secondary_qty);
4516 trace(' p_secondary_uom => '||p_secondary_uom);
4517 trace(' p_user_id => '||fnd_global.user_id);
4518 trace(' p_grade_code => '||p_grade_code);
4519 END IF;
4520 l_insert := inv_trx_util_pub.insert_line_trx(
4521 p_trx_hdr_id => l_txn_hdr_id
4522 ,p_item_id => p_inventory_item_id
4523 ,p_revision => p_revision
4524 ,p_org_id => p_organization_id
4525 ,p_trx_action_id => l_txn_action_id
4526 ,p_trx_type_id => l_txn_type_id
4527 ,p_trx_src_type_id => 13
4528 ,p_trx_qty => p_transaction_qty
4529 ,p_pri_qty => p_primary_qty
4530 ,p_uom => p_transaction_uom
4531 ,p_subinv_code => p_subinventory_code
4532 ,p_tosubinv_code => l_to_sub
4533 ,p_xfr_org_id => p_organization_id
4534 ,p_locator_id => p_locator_id
4535 ,p_tolocator_id => l_to_loc_id
4536 ,p_from_lpn_id => p_from_lpn_id
4537 ,p_xfr_lpn_id => p_to_lpn_id
4538 ,p_user_id => fnd_global.user_id
4539 ,p_secondary_trx_qty => p_secondary_qty
4540 ,p_secondary_uom => p_secondary_uom
4541 ,x_trx_tmp_id => l_txn_tmp_id
4542 ,x_proc_msg => l_proc_msg);
4543
4544 IF l_debug = 1 THEN
4545 trace('done with inserting , l_insert ='||l_insert);
4546 END IF;
4547 IF l_insert <> 0 THEN
4548 IF l_debug = 1 THEN
4549 trace('Error when inserting MMTT for item id:'||p_inventory_item_id|| 'err is '||l_proc_msg);
4550 END IF;
4551 x_proc_msg := l_proc_msg;
4552 raise fnd_api.g_exc_error;
4553 END IF;
4554
4555 IF l_debug = 1 THEN
4556 trace('MMTT inserted, tmp_id='||l_txn_tmp_id);
4557 END IF;
4558
4559 l_ser_txn_id := l_txn_tmp_id;
4560 ELSE
4561 -- Update existing MMTT
4562 BEGIN
4563 UPDATE mtl_material_transactions_temp
4564 SET transaction_quantity = transaction_quantity + p_transaction_qty,
4565 primary_quantity = primary_quantity + p_primary_qty,
4566 secondary_transaction_quantity =
4567 decode(secondary_transaction_quantity, NULL, NULL, secondary_transaction_quantity+p_secondary_qty)
4568 WHERE transaction_temp_id = p_transaction_temp_id;
4569 EXCEPTION
4570 WHEN others THEN
4571 IF l_debug = 1 THEN
4572 trace('Error when updating MMTT rec,tmp_id='||p_transaction_temp_id);
4573 END IF;
4574 raise fnd_api.g_exc_error;
4575 END;
4576 IF l_debug = 1 THEN
4577 trace('MMTT updated for tmp_id '||p_transaction_temp_id);
4578 END IF;
4579 l_txn_tmp_id := p_transaction_temp_id;
4580 l_ser_txn_id := p_transaction_temp_id;
4581 END IF;
4582
4583 IF p_lot_number IS NOT NULL THEN
4584 -- Create MTLT
4585 -- Insert MTLT record
4586 l_insert := inv_trx_util_pub.insert_lot_trx(
4587 p_trx_tmp_id => l_txn_tmp_id
4588 , p_user_id => fnd_global.user_id
4589 , p_lot_number => p_lot_number
4590 , p_trx_qty => p_transaction_qty
4591 , p_pri_qty => p_primary_qty
4592 , p_secondary_qty => p_secondary_qty
4593 , p_secondary_uom => p_secondary_uom
4594 , p_grade_code => p_grade_code --INVCONV kkillams
4595 , x_ser_trx_id => l_ser_txn_id
4596 , x_proc_msg => l_proc_msg
4597 );
4598 IF l_insert <> 0 THEN
4599 IF l_debug = 1 THEN
4600 trace('Error when inserting MTLT for lot:'||p_lot_number||',l_proc_msg='||l_proc_msg);
4601 END IF;
4602 x_proc_msg := l_proc_msg;
4603 raise fnd_api.g_exc_error;
4604 END IF;
4605 IF l_debug = 1 THEN
4606 trace('MTLT record inserted for lot:'||p_lot_number||',ser_txn_id='||l_ser_txn_id);
4607 END IF;
4608
4609 END IF; -- End if for Lot
4610
4611 IF p_fm_serial_number IS NOT NULL THEN
4612 -- Create MSNT
4613 -- Insert MSNT record
4614 l_insert := inv_trx_util_pub.insert_ser_trx(
4615 p_trx_tmp_id => l_ser_txn_id
4616 , p_user_id => fnd_global.user_id
4617 , p_fm_ser_num => p_fm_serial_number
4618 , p_to_ser_num => nvl(p_to_serial_number, p_fm_serial_number)
4619 , x_proc_msg => l_proc_msg
4620 );
4621 IF l_insert <> 0 THEN
4622 IF l_debug = 1 THEN
4623 trace('Error when inserting MSNT for fm_serial:'||p_fm_serial_number||',to_serial:'||p_to_serial_number||',l_proc_msg='||l_proc_msg);
4624 END IF;
4625 x_proc_msg := l_proc_msg;
4626 raise fnd_api.g_exc_error;
4627 END IF;
4628 IF l_debug = 1 THEN
4629 trace('MSNT record inserted for fm_serial:'||p_fm_serial_number||',to_serial:'||p_to_serial_number||',ser_txn_id='||l_ser_txn_id);
4630 END IF;
4631
4632 x_serial_transaction_temp_id := l_ser_txn_id;
4633 END IF; -- End if of SN
4634
4635 --Start Bug 6028098
4636 BEGIN
4637 SELECT sum(quantity)
4638 INTO l_sum_qty
4639 FROM wms_packing_material_gtemp
4640 WHERE inventory_item_id = p_inventory_item_id
4641 AND nvl(lot_number, '#$%') = nvl(p_lot_number, nvl(lot_number, '#$%'))
4642 AND nvl(revision, '#') = nvl(p_revision,nvl(revision, '#'))
4643 AND subinventory = p_subinventory_code
4644 AND locator_id = p_locator_id
4645 AND lpn_id = p_from_lpn_id;
4646 EXCEPTION WHEN OTHERS THEN l_sum_qty := 0;
4647 END;
4648
4649 -- Get the converted qty based on UOM.
4650 l_process_qty := inv_convert.inv_um_convert(p_inventory_item_id,null,p_transaction_qty,p_transaction_uom,p_primary_uom,null,null);
4651
4652 IF l_debug = 1 THEN
4653 trace('l_sum_qty: ' || l_sum_qty);
4654 trace('l_process_qty: ' || l_process_qty);
4655 END IF;
4656
4657
4658 -- If the summed qty = process_qty, then no loop needs to be done.
4659 -- Simply perform an update for all records for that part and set the qty to 0.
4660 IF l_process_qty = l_sum_qty THEN
4661 UPDATE wms_packing_material_gtemp
4662 SET selected_flag = 'Y'
4663 ,quantity = 0
4664 WHERE inventory_item_id = p_inventory_item_id
4665 AND nvl(lot_number, '#$%') = nvl(p_lot_number, nvl(lot_number, '#$%'))
4666 AND nvl(revision, '#') = nvl(p_revision,nvl(revision, '#'))
4667 AND subinventory = p_subinventory_code
4668 AND locator_id = p_locator_id
4669 AND lpn_id = p_from_lpn_id;
4670
4671 ELSE -- quantities are not the same, so you must loop and update the records you can.
4672
4673 -- Loop thru records for that part and reduce qty by correct amount.
4674 FOR c1 IN get_gtemp LOOP
4675
4676 IF l_process_qty <= 0 THEN
4677 exit;
4678 END IF;
4679
4680 IF l_debug = 1 THEN
4681 trace('c1.quantity ' || c1.quantity);
4682 trace('l_process_qty: ' || l_process_qty);
4683 END IF;
4684
4685 -- Calculate the qty to deduct from the current line.
4686 IF c1.quantity <= l_process_qty THEN
4687 l_update_qty := c1.quantity;
4688 l_process_qty := l_process_qty - c1.quantity;
4689 ELSE
4690 l_update_qty := l_process_qty;
4691 l_process_qty := 0;
4692 END IF;
4693
4694 IF l_debug = 1 THEN
4695 trace('l_update_qty ' || l_update_qty);
4696 END IF;
4697
4698 UPDATE wms_packing_material_gtemp
4699 SET selected_flag = 'Y'
4700 ,quantity = quantity - l_update_qty
4701 WHERE inventory_item_id = p_inventory_item_id
4702 AND nvl(lot_number, '#$%') = nvl(p_lot_number, nvl(lot_number, '#$%'))
4703 AND nvl(revision, '#') = nvl(p_revision,nvl(revision, '#'))
4704 AND subinventory = p_subinventory_code
4705 AND locator_id = p_locator_id
4706 AND lpn_id = p_from_lpn_id
4707 AND order_line_id = c1.order_line_id; -- Modified for bug 7606031
4708
4709 EXIT WHEN l_process_qty <= 0;
4710 END LOOP;
4711
4712 END IF;
4713 --End Bug 6028098
4714
4715 --Start Commented out Bug 6028098
4716 /*
4717 -- Update wms_packing_material_gtemp
4718 BEGIN
4719 UPDATE wms_packing_material_gtemp
4720 SET selected_flag = 'Y'
4721 ,quantity = quantity - inv_convert.inv_um_convert(
4722 inventory_item_id,null,p_transaction_qty,p_transaction_uom,uom,null,null)
4723 WHERE inventory_item_id = p_inventory_item_id
4724 AND nvl(lot_number, '#$%') = nvl(p_lot_number, nvl(lot_number, '#$%'))
4725 AND nvl(revision, '#') = nvl(p_revision,nvl(revision, '#'))
4726 AND subinventory = p_subinventory_code
4727 AND locator_id = p_locator_id
4728 AND lpn_id = p_from_lpn_id
4729 AND rownum<2;
4730 l_row_count := SQL%ROWCOUNT;
4731 IF l_row_count > 1 THEN
4732 IF l_debug = 1 THEN
4733 trace('Error when updating wms_packing_material_gtemp for item, only one record should be updated');
4734 END IF;
4735 fnd_message.set_name('INV','INV_FAILED');
4736 fnd_msg_pub.add;
4737 raise fnd_api.g_exc_error;
4738 ELSE
4739 IF l_debug = 1 THEN
4740 trace('wms_packing_material_gtemp updated, row_count='||l_row_count);
4741 END IF;
4742 --END IF;
4743 EXCEPTION
4744 WHEN others THEN
4745 IF l_debug = 1 THEN
4746 trace('Error when updating wms_packing_material_gtemp for item '||p_inventory_item_id);
4747 END IF;
4748 fnd_message.set_name('INV','INV_FAILED');
4749 fnd_msg_pub.add;
4750 raise fnd_api.g_exc_error;
4751 END;
4752 */
4753 --End Commented out Bug 6028098
4754
4755 -- No need to mark group_mark_id on MSN
4756 -- Because material in outbound already has group_mark_id stamped
4757 -- From stage transfer txn
4758 -- TM does not clear group_mark_id for stage transfer txn
4759
4760 END IF; -- End if of content is lpn or item
4761
4762 x_transaction_header_id := l_txn_hdr_id;
4763 x_transaction_temp_id := l_txn_tmp_id;
4764
4765 END IF; -- End if of Inbound or Outbound
4766 EXCEPTION
4767 WHEN others THEN
4768 IF l_debug = 1 THEN
4769 trace('Other errors in create_txn');
4770 trace('ERROR Code ='||SQLCODE);
4771 trace('ERROR Message='||SQLERRM);
4772 END IF;
4773 x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR;
4774 fnd_msg_pub.count_and_get(p_count => l_msg_count, p_data => l_msg_data, p_encoded => 'F');
4775 IF (l_msg_count = 1) THEN
4776 x_proc_msg := x_proc_msg || l_msg_data;
4777 ELSIF (l_msg_count > 1) THEN
4778 FOR i IN 1 .. l_msg_count LOOP
4779 l_msg_data := fnd_msg_pub.get(i, 'F');
4780 x_proc_msg := x_proc_msg || l_msg_data;
4781 END LOOP;
4782 END IF;
4783 END create_txn;
4784
4785
4786 /*******************************************
4787 * Procedure to delete MMTT/MTLT/MSNT record
4788 * For a pack/split/unpack transaction
4789 * This is used when user choose to do a UNDO
4790 *******************************************/
4791 PROCEDURE delete_txn(
4792 x_return_status OUT NOCOPY VARCHAR2
4793 , x_msg_count OUT NOCOPY NUMBER
4794 , x_msg_data OUT NOCOPY VARCHAR2
4795 , p_transaction_header_id IN NUMBER
4796 , p_transaction_temp_id IN NUMBER
4797 , p_lot_number IN VARCHAR2
4798 , p_serial_number IN VARCHAR2
4799 , p_quantity IN NUMBER DEFAULT NULL
4800 , p_uom IN VARCHAR2 DEFAULT NULL
4801 ) IS
4802 l_txn_hdr_id NUMBER;
4803 l_txn_tmp_id NUMBER;
4804 l_ser_tmp_id NUMBER;
4805 l_row_count NUMBER;
4806 l_mmtt_qty NUMBER;
4807 l_mtlt_qty NUMBER;
4808 l_mtlt_row_id ROWID;
4809 l_msnt_row_id ROWID;
4810 l_cont_lpn_id NUMBER;
4811 l_item_id NUMBER;
4812 l_txn_uom VARCHAR2(3);
4813 l_sec_uom VARCHAR2(3); --INCONV kkillams
4814 l_progress VARCHAR2(20);
4815
4816 BEGIN
4817 x_return_status := fnd_api.G_RET_STS_SUCCESS;
4818 IF l_debug = 1 THEN
4819 trace('In wms_packing_workbench_pvt.delete_txn');
4820 trace(', p_transaction_header_id = '||p_transaction_header_id||',p_transaction_temp_id='||p_transaction_temp_id);
4821 trace(', p_lot_number='||p_lot_number||', p_serial_number='||p_serial_number);
4822 END IF;
4823
4824 l_progress := '000';
4825 BEGIN
4826 SELECT content_lpn_id, inventory_item_id, primary_quantity, transaction_uom, secondary_uom_code
4827 INTO l_cont_lpn_id, l_item_id, l_mmtt_qty, l_txn_uom
4828 , l_sec_uom --INCONV kkillams
4829 FROM mtl_material_transactions_temp
4830 WHERE transaction_header_id = p_transaction_header_id
4831 AND transaction_temp_id = p_transaction_temp_id;
4832 IF l_debug = 1 THEN
4833 trace('Found MMTT, contLPNID='||l_cont_lpn_id||', l_item_id='||l_item_id||',l_mmtt_qty='||l_mmtt_qty);
4834 END IF;
4835
4836 l_progress := '003';
4837 IF p_lot_number IS NOT NULL AND p_serial_number IS NOT NULL THEN
4838 -- Lot and Serial
4839 SELECT mtlt_row_id, mtlt_qty, msnt_row_id
4840 INTO l_mtlt_row_id, l_mtlt_qty, l_msnt_row_id
4841 FROM
4842 (SELECT mtlt.rowid mtlt_row_id, mtlt.primary_quantity mtlt_qty, msnt.rowid msnt_row_id
4843 FROM mtl_transaction_lots_temp mtlt, mtl_serial_numbers_temp msnt
4844 WHERE msnt.transaction_temp_id = mtlt.serial_transaction_temp_id
4845 AND mtlt.transaction_temp_id = p_transaction_temp_id
4846 AND mtlt.lot_number = p_lot_number
4847 AND msnt.fm_serial_number = p_serial_number
4848 order by msnt.creation_date desc) t
4849 WHERE rownum < 2;
4850 IF l_debug = 1 THEN
4851 trace('Lot and Serial, l_mtlt_row_id='||l_mtlt_row_id||',l_mtlt_qty='||l_mtlt_qty||',l_msnt_row_id='||l_msnt_row_id);
4852 END IF;
4853 l_progress := '005';
4854 ELSIF p_lot_number IS NOT NULL THEN
4855 -- Lot Only
4856 SELECT t.mtlt_row_id, t.mtlt_qty
4857 INTO l_mtlt_row_id, l_mtlt_qty
4858 FROM
4859 (SELECT rowid mtlt_row_id, primary_quantity mtlt_qty
4860 FROM mtl_transaction_lots_temp
4861 WHERE transaction_temp_id = p_transaction_temp_id
4862 AND lot_number = p_lot_number
4863 AND primary_quantity = p_quantity
4864 order by creation_date desc) t
4865 WHERE rownum < 2;
4866 l_msnt_row_id := null;
4867 IF l_debug = 1 THEN
4868 trace('Lot only, l_mtlt_row_id='||l_mtlt_row_id||',l_mtlt_qty='||l_mtlt_qty);
4869 END IF;
4870 l_progress := '007';
4871 ELSIF p_serial_number IS NOT NULL THEN
4872 -- Serial Only
4873 SELECT t.msnt_row_id
4874 INTO l_msnt_row_id
4875 FROM
4876 (SELECT rowid msnt_row_id
4877 FROM mtl_serial_numbers_temp
4878 WHERE transaction_temp_id = p_transaction_temp_id
4879 AND fm_serial_number = p_serial_number
4880 order by creation_date desc) t
4881 WHERE rownum < 2;
4882 l_mtlt_row_id := null;
4883 l_mtlt_qty := null;
4884 IF l_debug = 1 THEN
4885 trace('Serial only, msnt_row_id='||l_msnt_row_id);
4886 END IF;
4887 l_progress := '009';
4888 ELSE
4889 -- No Lot , No serial
4890 null;
4891 END IF;
4892
4893 EXCEPTION
4894 WHEN others THEN
4895 IF l_debug = 1 THEN
4896 trace('Error getting txn information for txn_temp_id '||p_transaction_temp_id);
4897 END IF;
4898 END;
4899
4900 IF (l_cont_lpn_id IS NOT NULL) OR
4901 (p_lot_number IS NULL AND p_serial_number IS NULL) THEN
4902 IF l_debug = 1 THEN
4903 IF l_debug = 1 THEN
4904 trace('MMTT is for LPN or, Lot and serial number is null, deleting MMTT');
4905 END IF;
4906 END IF;
4907 DELETE mtl_material_transactions_temp
4908 WHERE transaction_temp_id = p_transaction_temp_id;
4909 l_row_count := SQL%ROWCOUNT;
4910 IF l_debug = 1 THEN
4911 trace(l_row_count||' rows of MMTT deleted with tmp_id '||p_transaction_temp_id);
4912 END IF;
4913 l_progress := '011';
4914 IF l_row_count <> 1 THEN
4915 RAISE fnd_api.g_exc_error;
4916 END IF;
4917 ELSE
4918
4919 IF p_serial_number IS NOT NULL THEN
4920 IF l_debug = 1 THEN
4921 trace('Deleting MSNT');
4922 END IF;
4923 DELETE mtl_serial_numbers_temp
4924 WHERE rowid = l_msnt_row_id;
4925 l_row_count := SQL%ROWCOUNT;
4926 IF l_debug = 1 THEN
4927 trace(l_row_count||' rows of MSNT deleted with row_id '||l_msnt_row_id);
4928 END IF;
4929 l_progress := '013';
4930
4931 IF l_row_count <> 1 THEN
4932 RAISE fnd_api.g_exc_error;
4933 END IF;
4934 END IF;
4935
4936 IF p_lot_number IS NOT NULL THEN
4937 IF l_mtlt_qty = abs(p_quantity) THEN
4938 -- Delete MTLT
4939 DELETE mtl_transaction_lots_temp
4940 WHERE rowid = l_mtlt_row_id;
4941 l_row_count := SQL%ROWCOUNT;
4942 IF l_debug = 1 THEN
4943 trace(l_row_count||' rows of MTLT deleted with row_id '||l_mtlt_row_id);
4944 END IF;
4945 l_progress := '015';
4946
4947 IF l_row_count <> 1 THEN
4948 RAISE fnd_api.g_exc_error;
4949 END IF;
4950 ELSIF l_mtlt_qty > abs(p_quantity) THEN
4951 -- Update MTLT
4952 UPDATE mtl_transaction_lots_temp
4953 SET primary_quantity = primary_quantity - abs(p_quantity)
4954 ,transaction_quantity = inv_convert.inv_um_convert(
4955 l_item_id,null,primary_quantity - abs(p_quantity),p_uom,l_txn_uom,null,null)
4956 WHERE rowid = l_mtlt_row_id;
4957 l_row_count := SQL%ROWCOUNT;
4958 IF l_debug = 1 THEN
4959 trace(l_row_count||' rows of MTLT updated with row_id '||l_mtlt_row_id);
4960 END IF;
4961 l_progress := '017';
4962
4963 IF l_row_count <> 1 THEN
4964 RAISE fnd_api.g_exc_error;
4965 END IF;
4966 ELSE
4967 IF l_debug = 1 THEN
4968 trace('mtlt quantity can not be less than p_quantity');
4969 END IF;
4970 RAISE fnd_api.g_exc_error;
4971 END IF;
4972 END IF;
4973
4974 -- MMTT
4975 IF l_mmtt_qty = abs(p_quantity) THEN
4976 -- Delete MMTT
4977 DELETE mtl_material_transactions_temp
4978 WHERE transaction_temp_id = p_transaction_temp_id;
4979 l_row_count := SQL%ROWCOUNT;
4980 IF l_debug = 1 THEN
4981 trace(l_row_count||' rows of MMTT deleted with tmp_id '||p_transaction_temp_id);
4982 END IF;
4983 l_progress := '019';
4984
4985 IF l_row_count <> 1 THEN
4986 RAISE fnd_api.g_exc_error;
4987 END IF;
4988 ELSIF l_mmtt_qty > abs(p_quantity) THEN
4989 UPDATE mtl_material_transactions_temp
4990 SET primary_quantity = primary_quantity - abs(p_quantity)
4991 ,transaction_quantity = inv_convert.inv_um_convert(
4992 l_item_id,null,primary_quantity - abs(p_quantity),p_uom,l_txn_uom,null,null)
4993 --INVCONV kkillams
4994 ,secondary_transaction_quantity = CASE WHEN secondary_uom_code IS NOT NULL THEN
4995 inv_convert.inv_um_convert(l_item_id,
4996 null,
4997 primary_quantity - abs(p_quantity),
4998 p_uom,
4999 l_sec_uom,null,null)
5000 ELSE NULL END
5001 WHERE transaction_temp_id = p_transaction_temp_id;
5002 l_row_count := SQL%ROWCOUNT;
5003 IF l_debug = 1 THEN
5004 trace(l_row_count||' rows of MMTT updated with tmp_id '||p_transaction_temp_id);
5005 END IF;
5006 l_progress := '021';
5007 IF l_row_count <> 1 THEN
5008 RAISE fnd_api.g_exc_error;
5009 END IF;
5010 ELSE
5011 IF l_debug = 1 THEN
5012 trace('mmtt quantity can not be less than p_quantity');
5013 END IF;
5014 RAISE fnd_api.g_exc_error;
5015 END IF;
5016
5017 END IF;
5018
5019 EXCEPTION
5020 WHEN others THEN
5021 x_return_status := fnd_api.G_RET_STS_ERROR;
5022 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
5023 IF l_debug = 1 THEN
5024 trace('Error in delete_txn(), progress='||l_progress);
5025 trace('ERROR Code ='||SQLCODE);
5026 trace('ERROR Message='||SQLERRM);
5027 END IF;
5028 END delete_txn;
5029
5030 /*******************************************
5031 * Procedure to call transaction manager
5032 * to process the MMTT records
5033 * This is used when user close a LPN
5034 *******************************************/
5035 PROCEDURE process_txn(
5036 p_source IN NUMBER
5037 , p_trx_hdr_id IN NUMBER
5038 , x_return_status OUT NOCOPY VARCHAR2
5039 , x_proc_msg OUT NOCOPY VARCHAR2) IS
5040
5041 l_return NUMBER;
5042 l_proc_msg VARCHAR2(1000) := null;
5043 l_return_status VARCHAR2(1);
5044 l_msg_data VARCHAR2(1000);
5045 l_msg_count NUMBER;
5046
5047 BEGIN
5048 IF l_debug = 1 THEN
5049 trace('In process txn, p_source='||p_source||',p_hdr_id='||p_trx_hdr_id);
5050 END IF;
5051
5052 IF p_source = 1 THEN
5053 -- Inbound
5054 IF l_debug = 1 THEN
5055 trace('Calling WMS_RCV_PUP_PVT.pack_unpack_split for trx_hdr_id '||p_trx_hdr_id);
5056 END IF;
5057 WMS_RCV_PUP_PVT.pack_unpack_split
5058 ( p_header_id => p_trx_hdr_id
5059 ,x_return_status => l_return_status
5060 ,x_msg_count => l_msg_count
5061 ,x_msg_data => l_msg_data
5062 );
5063 IF l_debug = 1 THEN
5064 trace('Called WMS_RCV_PUP_PVT.pack_unpack_split API, return_status='||l_return_status||',msg_count='||l_msg_count||',msg_data='||l_msg_data);
5065 END IF;
5066 IF l_return_status <> 'S' THEN
5067 raise fnd_api.g_exc_error;
5068 ELSE
5069 x_return_status := fnd_api.G_RET_STS_SUCCESS;
5070 x_proc_msg := NULL;
5071 END IF;
5072 ELSE
5073 -- Outbound
5074 IF l_debug = 1 THEN
5075 trace('Calling INV_LPN_TRX_PUB.PROCESS_LPN_TRX for trx_hdr_id '||p_trx_hdr_id);
5076 END IF;
5077
5078 l_return := INV_LPN_TRX_PUB.PROCESS_LPN_TRX(
5079 p_trx_hdr_id => p_trx_hdr_id,
5080 x_proc_msg => l_proc_msg,
5081 p_proc_mode => 1 --Online Mode
5082 );
5083 IF l_debug = 1 THEN
5084 trace('called INV_LPN_TRX_PUB.PROCESS_LPN_TRX , l_return='||l_return||',l_proc_msg='||l_proc_msg);
5085 END IF;
5086 IF l_return = 0 THEN
5087 x_return_status := fnd_api.G_RET_STS_SUCCESS;
5088 x_proc_msg := null;
5089 ELSE
5090 x_return_status := fnd_api.G_RET_STS_ERROR;
5091 x_proc_msg := l_proc_msg;
5092 END IF;
5093
5094 END IF;
5095
5096 EXCEPTION
5097 WHEN others THEN
5098 x_return_status := fnd_api.G_RET_STS_ERROR;
5099 fnd_msg_pub.count_and_get(p_count => l_msg_count, p_data => l_msg_data, p_encoded => 'F');
5100 IF (l_msg_count = 1) THEN
5101 x_proc_msg := l_msg_data;
5102 ELSIF (l_msg_count > 1) THEN
5103 FOR i IN 1 .. l_msg_count LOOP
5104 l_msg_data := fnd_msg_pub.get(i, 'F');
5105 x_proc_msg := x_proc_msg || l_msg_data;
5106 END LOOP;
5107 END IF;
5108 IF l_debug = 1 THEN
5109 trace('Error in process_txn');
5110 trace('ERROR Code ='||SQLCODE);
5111 trace('ERROR Message='||SQLERRM);
5112 END IF;
5113 END process_txn;
5114
5115 /*******************************
5116 * Firm Delivery API *
5117 *******************************/
5118 PROCEDURE firm_delivery(
5119 p_delivery_id IN NUMBER
5120 , x_return_status OUT NOCOPY VARCHAR2
5121 , x_proc_msg OUT NOCOPY VARCHAR2) IS
5122
5123 l_action_prms WSH_INTERFACE_EXT_GRP.del_action_parameters_rectype;
5124 l_delivery_id_tab wsh_util_core.id_tab_type;
5125 l_delivery_out_rec WSH_INTERFACE_EXT_GRP.Del_Action_Out_Rec_Type;
5126
5127 l_return_status VARCHAR2(1);
5128 l_msg_count NUMBER;
5129 l_msg_data VARCHAR2(2000);
5130
5131 BEGIN
5132 IF l_debug = 1 THEN
5133 trace('Calling WSH_INTERFACE_EXT_GRP.Delivery_Action() for delivery_id '||p_delivery_id);
5134 END IF;
5135 l_action_prms.caller := 'WMS_DLMG';
5136 l_action_prms.event := WSH_INTERFACE_EXT_GRP.G_START_OF_PACKING;
5137 l_action_prms.action_code := 'ADJUST-PLANNED-FLAG';
5138
5139 l_delivery_id_tab(1) := p_delivery_id;
5140
5141 WSH_INTERFACE_EXT_GRP.Delivery_Action
5142 (p_api_version_number => 1.0,
5143 p_init_msg_list => fnd_api.g_false,
5144 p_commit => fnd_api.g_false,
5145 p_action_prms => l_action_prms,
5146 p_delivery_id_tab => l_delivery_id_tab,
5147 x_delivery_out_rec => l_delivery_out_rec,
5148 x_return_status => l_return_status,
5149 x_msg_count => l_msg_count,
5150 x_msg_data => l_msg_data);
5151
5152 IF l_debug = 1 THEN
5153 trace('Called WSH_INTERFACE_EXT_GRP.Delivery_Action, return_status ='||l_return_status);
5154 trace('l_msg_data='||l_msg_data);
5155 END IF;
5156 x_return_status := l_return_status;
5157 IF l_return_status <> 'S' THEN
5158 fnd_msg_pub.count_and_get(p_count => l_msg_count, p_data => l_msg_data, p_encoded => 'F');
5159 IF (l_msg_count = 1) THEN
5160 x_proc_msg := l_msg_data;
5161 ELSIF (l_msg_count > 1) THEN
5162 FOR i IN 1 .. l_msg_count LOOP
5163 l_msg_data := fnd_msg_pub.get(i, 'F');
5164 x_proc_msg := x_proc_msg || l_msg_data;
5165 END LOOP;
5166 END IF;
5167 IF l_debug = 1 THEN
5168 trace('WSH_INTERFACE_EXT_GRP.Delivery_Action failed, proc_msg = '||x_proc_msg);
5169 END IF;
5170 END IF;
5171
5172
5173 EXCEPTION
5174 WHEN others THEN
5175 IF l_debug = 1 THEN
5176 trace('Error when in firm_delivery for delivery_id='||p_delivery_id);
5177 trace('ERROR Code ='||SQLCODE);
5178 trace('ERROR Message='||SQLERRM);
5179 END IF;
5180 END firm_delivery;
5181
5182
5183 PROCEDURE update_kit_model_info
5184 ( p_kit_item_id IN NUMBER
5185 , p_component_item_id IN NUMBER
5186 , p_top_model_line_id IN NUMBER
5187 ) IS
5188
5189 CURSOR c_existing_kits_for_component IS
5190 SELECT DISTINCT top_model_line_id, kit_item_id
5191 FROM wms_packing_kitting_gtemp
5192 WHERE component_item_id = p_component_item_id;
5193
5194 l_existing_kits_for_component c_existing_kits_for_component%ROWTYPE;
5195 l_kit_packed_qty NUMBER :=0;
5196 l_kit_order_qty NUMBER;
5197 l_completed_flag VARCHAR2(1);
5198
5199
5200 BEGIN
5201
5202 IF p_kit_item_id IS NOT NULL AND p_top_model_line_id IS NOT NULL THEN
5203
5204 -- Check whehther kit is completed and update packed_qty of the Kit .
5205
5206 BEGIN
5207 SELECT min(floor(decode(packed_qty_disp, '*',0,NULL,0,to_number(packed_qty_disp))/bom_qty)) kit_qty
5208 INTO l_kit_packed_qty
5209 FROM wms_packing_kitting_gtemp
5210 WHERE kit_item_id = p_kit_item_id
5211 AND top_model_line_id = p_top_model_line_id
5212 AND component_item_id IS NOT NULL;
5213 EXCEPTION
5214 WHEN no_data_found THEN
5215 l_kit_packed_qty := 0;
5216 END;
5217 IF l_debug = 1 THEN
5218 trace('Got kit_packed_qty = '||l_kit_packed_qty);
5219 END IF;
5220
5221 -- Get Order Qty of the kit
5222 BEGIN
5223 SELECT order_qty INTO l_kit_order_qty
5224 FROM wms_packing_kitting_gtemp
5225 WHERE kit_item_id = p_kit_item_id
5226 AND top_model_line_id = p_top_model_line_id
5227 AND component_item_id IS NULL;
5228 EXCEPTION
5229 WHEN no_data_found THEN
5230 l_kit_order_qty := 0;
5231 END;
5232
5233 -- Update packed_qty for the Kit
5234 IF l_kit_order_qty = l_kit_packed_qty THEN
5235 l_completed_flag := 'Y';
5236 ELSE
5237 l_completed_flag := 'N';
5238 END IF;
5239
5240 UPDATE wms_packing_kitting_gtemp
5241 SET packed_qty = l_kit_packed_qty
5242 , packed_qty_disp = decode(l_kit_packed_qty,0,NULL,to_char(l_kit_packed_qty))
5243 , completed_flag = l_completed_flag
5244 WHERE kit_item_id = p_kit_item_id
5245 AND top_model_line_id = p_top_model_line_id
5246 AND component_item_id IS NULL;
5247
5248 IF l_debug = 1 THEN
5249 trace(SQL%ROWCOUNT||' rows of wms_packing_kitting_gtemp updated');
5250 END IF;
5251
5252 UPDATE wms_packing_kitting_gtemp
5253 SET completed_flag = l_completed_flag
5254 WHERE kit_item_id = p_kit_item_id
5255 AND top_model_line_id = p_top_model_line_id;
5256 IF l_debug = 1 THEN
5257 trace(SQL%ROWCOUNT||' rows of wms_packing_kitting_gtemp updated for completed_flag of '||l_completed_flag);
5258 END IF;
5259
5260
5261 ELSE
5262 --called from the get_kitting_info at the end of finding
5263 --multiple kits to update the kits info, after updating the qty
5264 --field FOR common item across all kits, if all order qty has been packed
5265
5266 IF l_debug = 1 THEN
5267 trace('Updating Kit qty information after updating the common kit-component qty');
5268 END IF;
5269
5270 -- Get new kit
5271 OPEN c_existing_kits_for_component;
5272 LOOP
5273 FETCH c_existing_kits_for_component INTO l_existing_kits_for_component;
5274
5275 IF c_existing_kits_for_component%notfound THEN
5276 CLOSE c_existing_kits_for_component;
5277 EXIT;
5278 END IF;
5279
5280
5281 -- Check whehther kit is completed and update packed_qty of the Kit .
5282 l_kit_packed_qty := 0;
5283 BEGIN
5284 SELECT min(floor(decode(packed_qty_disp, '*',0,NULL,0,to_number(packed_qty_disp))/bom_qty)) kit_qty
5285 INTO l_kit_packed_qty
5286 FROM wms_packing_kitting_gtemp
5287 WHERE kit_item_id = l_existing_kits_for_component.kit_item_id
5288 AND top_model_line_id = l_existing_kits_for_component.top_model_line_id
5289 AND component_item_id IS NOT NULL;
5290 EXCEPTION
5291 WHEN no_data_found THEN
5292 l_kit_packed_qty := 0;
5293 END;
5294 IF l_debug = 1 THEN
5295 trace('Got kit_packed_qty = '||l_kit_packed_qty);
5296 END IF;
5297
5298 -- Get Order Qty of the kit
5299 BEGIN
5300 SELECT order_qty INTO l_kit_order_qty
5301 FROM wms_packing_kitting_gtemp
5302 WHERE kit_item_id = l_existing_kits_for_component.kit_item_id
5303 AND top_model_line_id = l_existing_kits_for_component.top_model_line_id
5304 AND component_item_id IS NULL;
5305 EXCEPTION
5306 WHEN no_data_found THEN
5307 l_kit_order_qty := 0;
5308 END;
5309
5310 -- Update packed_qty for the Kit
5311 IF l_kit_order_qty = l_kit_packed_qty THEN
5312 l_completed_flag := 'Y';
5313 ELSE
5314 l_completed_flag := 'N';
5315 END IF;
5316
5317 UPDATE wms_packing_kitting_gtemp
5318 SET packed_qty = l_kit_packed_qty
5319 , packed_qty_disp = decode(l_kit_packed_qty,0,NULL,to_char(l_kit_packed_qty))
5320 , completed_flag = l_completed_flag
5321 WHERE kit_item_id = l_existing_kits_for_component.kit_item_id
5322 AND top_model_line_id = l_existing_kits_for_component.top_model_line_id
5323 AND component_item_id IS NULL;
5324
5325 IF l_debug = 1 THEN
5326 trace(SQL%ROWCOUNT||' rows of wms_packing_kitting_gtemp updated');
5327 END IF;
5328
5329 UPDATE wms_packing_kitting_gtemp
5330 SET completed_flag = l_completed_flag
5331 WHERE kit_item_id = l_existing_kits_for_component.kit_item_id
5332 AND top_model_line_id = l_existing_kits_for_component.top_model_line_id;
5333
5334 IF l_debug = 1 THEN
5335 trace(SQL%ROWCOUNT||' rows of wms_packing_kitting_gtemp updated for completed_flag of '||l_completed_flag);
5336 END IF;
5337
5338 END LOOP;
5339 END IF;
5340
5341 END update_kit_model_info;
5342
5343
5344
5345 /* **************************************************
5346 * Update the kit temp table
5347 * p_packed_qty: Given packed_qty
5348 When p_action = 'A'(Add): Add the p_packed_qty to existing p_packed_qty
5349 p_action = 'U'(Update): Update the gtemp.packed_qty as p_packed_qty
5350 p_disp_packed_qty:
5351 When is '*', update gtemp.packed_qty_disp as '*'
5352 When is NULL, update gtemp.packed_qty_disp as NULL
5353 When not null and not '*', update gtemp.packed_qty_disp as gtemp.packed_qty
5354 ****************************************************/
5355 PROCEDURE update_kit_info
5356 ( p_kit_item_id IN NUMBER
5357 , p_component_item_id IN NUMBER
5358 , p_top_model_line_id IN NUMBER
5359 , p_packed_qty IN NUMBER DEFAULT NULL
5360 , p_disp_packed_qty IN VARCHAR2 DEFAULT NULL
5361 , p_action IN VARCHAR2
5362 ) IS
5363 l_packed_qty NUMBER;
5364 l_packed_qty_disp VARCHAR2(200);
5365
5366 CURSOR c_update_QTY_common_comp IS
5367 SELECT packed_qty,order_qty,kit_item_id,component_item_id,top_model_line_id FROM wms_packing_kitting_gtemp
5368 WHERE component_item_id = p_component_item_id
5369 AND ((packed_qty <> order_qty AND packed_qty IS NOT NULL) OR
5370 packed_qty IS NULL);
5371
5372 /*
5373 --for debug only
5374 CURSOR c_debug_cur IS
5375 SELECT
5376 packed_qty,order_qty,kit_item_id,component_item_id,top_model_line_id,packed_qty_disp
5377 FROM wms_packing_kitting_gtemp;
5378
5379 l_debug_cur C_debug_cur%ROWTYPE;
5380 */
5381
5382 l_update_qty_common_comp c_update_qty_common_comp%ROWTYPE;
5383 l_surplus_qty NUMBER;
5384 l_remaining_qty_to_pack NUMBER;
5385 l_total_row_cnt NUMBER;
5386
5387
5388 BEGIN
5389 IF l_debug = 1 THEN
5390 trace('In update_kit_info');
5391 trace(' p_kit_item_id = '||p_kit_item_id||', p_component_item_id='||p_component_item_id);
5392 trace(' p_top_model_line_id='||p_top_model_line_id);
5393 trace(' p_packed_qty ='||p_packed_qty||', p_disp_packed_qty='||p_disp_packed_qty);
5394 trace(' p_action='||p_action);
5395 END IF;
5396
5397 IF p_kit_item_id IS NOT NULL AND p_component_item_id IS NOT NULL then
5398 UPDATE wms_packing_kitting_gtemp
5399 SET packed_qty = least(order_qty,decode(p_action, 'A', nvl(packed_qty,0) + p_packed_qty, p_packed_qty))
5400 , packed_qty_disp = Decode(p_disp_packed_qty, '*', '*', NULL, NULL,
5401 to_char(least(order_qty,decode(p_action, 'A', nvl(packed_qty,0) + p_packed_qty, p_packed_qty))))
5402 WHERE kit_item_id = p_kit_item_id
5403 AND component_item_id = p_component_item_id
5404 AND top_model_line_id = p_top_model_line_id;
5405
5406
5407 IF l_debug = 1 THEN
5408 trace('updated kit '|| p_kit_item_id||' and component '||p_component_item_id||' top_model_line_id='||p_top_model_line_id);
5409
5410 trace('NUMBER OF ROWS UPDATE :'||SQL%rowcount);
5411 END IF;
5412
5413
5414 /*
5415
5416 --FOR DEBUG ONLY
5417
5418 OPEN c_debug_cur;
5419 LOOP
5420 FETCH c_debug_cur INTO l_debug_cur;
5421
5422 IF c_debug_cur%notfound THEN
5423 CLOSE c_debug_cur;
5424 EXIT;
5425 END IF;
5426
5427
5428 IF l_debug = 1 THEN
5429 trace('---------------------******************--------------');
5430 trace('kit_item_id :'||l_debug_cur.kit_item_id);
5431 trace('component_item_id :'|| l_debug_cur.component_item_id);
5432 trace('top_model_line_id :'||l_debug_cur.top_model_line_id);
5433 trace('packed_qty :'||l_debug_cur.packed_qty);
5434 trace('order_qty :'||l_debug_cur.order_qty);
5435 trace('packed_qty_disp :'||l_debug_cur.packed_qty_disp);
5436 END IF;
5437
5438 END LOOP;
5439
5440 --for debug ONLY
5441 */
5442
5443
5444 ELSE
5445
5446 IF l_debug = 1 THEN
5447 trace('Updating qty in WPKG recursively');
5448 END IF;
5449
5450 l_surplus_qty := 0;
5451 l_remaining_qty_to_pack:= p_packed_qty;
5452
5453 OPEN c_update_QTY_common_comp;
5454 LOOP
5455 FETCH c_update_QTY_common_comp INTO l_update_qty_common_comp;
5456
5457 IF c_update_QTY_common_comp%notfound THEN
5458 CLOSE c_update_qty_common_comp;
5459 EXIT;
5460 END IF;
5461
5462 IF l_debug = 1 THEN
5463 trace('Inside the loop to update the qty recursively in WPKG');
5464 END IF;
5465
5466 IF l_update_qty_common_comp.packed_qty IS NULL THEN
5467 l_update_qty_common_comp.packed_qty := 0;
5468 END IF;
5469
5470 l_surplus_qty := (l_remaining_qty_to_pack + l_update_qty_common_comp.packed_qty) - l_update_qty_common_comp.order_qty;
5471
5472 IF l_debug = 1 THEN
5473 trace('l_remaining_qty_to_pack :' ||l_remaining_qty_to_pack);
5474 trace('l_update_qty_common_comp.packed_qt :' ||l_update_qty_common_comp.packed_qty);
5475 trace('l_update_qty_common_comp.order_qty :'|| l_update_qty_common_comp.order_qty);
5476 trace('l_surplus_qty :'||l_surplus_qty);
5477
5478 END IF;
5479
5480
5481 IF l_surplus_qty <= 0 THEN
5482
5483 UPDATE wms_packing_kitting_gtemp
5484 SET packed_qty = (l_remaining_qty_to_pack+l_update_qty_common_comp.packed_qty)
5485 --, packed_qty_disp = '*'
5486 WHERE kit_item_id = l_update_qty_common_comp.kit_item_id
5487 AND component_item_id = l_update_qty_common_comp.component_item_id
5488 AND top_model_line_id = l_update_qty_common_comp.top_model_line_id;
5489
5490 IF l_debug = 1 THEN
5491 trace('Final remaining qty after consuming p_packed_qty : 0');
5492 END IF;
5493
5494 CLOSE c_update_qty_common_comp;
5495 EXIT;--this record finally consumed remaining, exit
5496
5497 ELSIF l_surplus_qty > 0 THEN
5498 --this record can not consume complete p_packed_qty
5499 --Update this record with enough qty to fulfill this order_qty
5500
5501 l_remaining_qty_to_pack := l_remaining_qty_to_pack -
5502 (l_update_qty_common_comp.order_qty - l_update_qty_common_comp.packed_qty);
5503
5504 UPDATE wms_packing_kitting_gtemp
5505 SET packed_qty = l_update_qty_common_comp.order_qty
5506 --, packed_qty_disp = '*'
5507 WHERE kit_item_id = l_update_qty_common_comp.kit_item_id
5508 AND component_item_id = l_update_qty_common_comp.component_item_id
5509 AND top_model_line_id = l_update_qty_common_comp.top_model_line_id;
5510
5511 IF l_debug = 1 THEN
5512 trace('Remaining qty after consuming p_packed_qty :'||l_remaining_qty_to_pack);
5513 END IF;
5514
5515 END IF;
5516
5517 END LOOP;
5518
5519 UPDATE wms_packing_kitting_gtemp
5520 SET packed_qty_disp = '*'
5521 WHERE component_item_id = p_component_item_id;
5522
5523 END IF;
5524
5525
5526 --Now update kit model information
5527 update_kit_model_info
5528 ( p_kit_item_id => p_kit_item_id
5529 , p_component_item_id => p_component_item_id
5530 , p_top_model_line_id => p_top_model_line_id);
5531
5532
5533 END update_kit_info;
5534
5535
5536
5537
5538 PROCEDURE get_kitting_info(
5539 x_return_status OUT NOCOPY VARCHAR2
5540 , x_msg_data OUT NOCOPY VARCHAR2
5541 , x_msg_count OUT NOCOPY VARCHAR2
5542 , p_organization_id IN NUMBER
5543 ,p_inventory_item_id IN NUMBER
5544 ,p_quantity IN NUMBER) IS
5545
5546 /*OPEN ISSUES
5547
5548 --what if the TO-LPN already has some content, do they become
5549 --part OF the kit TO be packed -- Not Yet
5550
5551 --what if the user scans the quantity for common item greater than all
5552 --exhausted-qty of component IN the current kit list. where to
5553 --save this extra quantity, which might be needed later FOR new
5554 --added kit -- Just show the order qty
5555
5556 */
5557
5558
5559 CURSOR c_update_disp_qty_common_comp IS
5560 SELECT packed_qty,kit_item_id,component_item_id,top_model_line_id FROM wms_packing_kitting_gtemp
5561 WHERE component_item_id = p_inventory_item_id
5562 AND packed_qty = order_qty
5563 AND packed_qty IS NOT NULL;
5564
5565
5566
5567 l_kit_list kit_tbl_type;
5568 l_other_kit_list kit_tbl_type;
5569 l_kit_component_list kit_component_tbl_type;
5570
5571 l_update_disp_qty_common_comp c_update_disp_qty_common_comp%ROWTYPE;
5572
5573 l_new_inserted_kit_cnt NUMBER := 0;
5574 l_item_unique_existing_kit NUMBER;
5575 l_common_qty_filled NUMBER := 0;
5576
5577
5578 BEGIN
5579 IF l_debug = 1 THEN
5580 trace('In get_kitting_info, p_org_id='||p_organization_id||',p_item_id='||p_inventory_item_id||',p_qty='||p_quantity);
5581 END IF;
5582 x_return_status := fnd_api.G_RET_STS_SUCCESS;
5583 -- First get the list of kits that the item belongs to
5584 l_kit_list := get_kit_list(p_organization_id, p_inventory_item_id, 'N');
5585
5586 l_new_inserted_kit_cnt := 0;
5587
5588 IF l_kit_list.COUNT = 1 THEN
5589 -- Only one kit
5590 IF l_debug = 1 THEN
5591 trace('Item belongs to only one kit');
5592 END IF;
5593 IF l_kit_list(1).exist_flag = 'N' THEN
5594 -- New kit
5595 insert_kit_info
5596 ( p_kit_item_id =>l_kit_list(1).kit_item_id
5597 , p_component_item_id =>p_inventory_item_id
5598 , p_top_model_line_id => l_kit_list(1).top_model_line_id
5599 , p_packed_qty => p_quantity
5600 , p_disp_packed_qty => p_quantity
5601 );
5602 IF l_debug = 1 THEN
5603 trace('Kit 1 is a new kit, inserted information for kit ID '||l_kit_list(1).kit_item_id);
5604 END IF;
5605 ELSE
5606 -- Existing kit
5607 -- Update this kit info
5608 update_kit_info
5609 ( p_kit_item_id =>l_kit_list(1).kit_item_id
5610 , p_component_item_id =>p_inventory_item_id
5611 , p_top_model_line_id => l_kit_list(1).top_model_line_id
5612 , p_packed_qty => p_quantity
5613 , p_disp_packed_qty => to_char(p_quantity)
5614 , p_action => 'A' -- Add
5615 );
5616 IF l_debug = 1 THEN
5617 trace('Kit 1 exists already, updated kit information for kit ID '||l_kit_list(1).kit_item_id);
5618 END IF;
5619 -- Get other items in the kit
5620 l_kit_component_list.DELETE;
5621 l_kit_component_list := get_kit_component_list
5622 (p_kit_item_id => l_kit_list(1).kit_item_id
5623 ,p_top_model_line_id => l_kit_list(1).top_model_line_id
5624 ,p_exclude_item_id => p_inventory_item_id
5625 );
5626 IF l_debug = 1 THEN
5627 trace('Got other items in the existing kit, no. of items='||l_kit_component_list.COUNT);
5628 END IF;
5629 FOR i IN 1..l_kit_component_list.COUNT LOOP
5630 IF l_kit_component_list(i).packed_qty IS NOT NULL AND
5631 l_kit_component_list(i).packed_qty_disp='*' THEN
5632 -- Update disp_pack_qty as p_packed_qty
5633 update_kit_info
5634 (p_kit_item_id => l_kit_list(1).kit_item_id
5635 , p_component_item_id =>
5636 l_kit_component_list(i).component_item_id
5637 , p_top_model_line_id => l_kit_list(1).top_model_line_id
5638 , p_packed_qty => l_kit_component_list(i).packed_qty
5639 , p_disp_packed_qty => to_char(l_kit_component_list(i).packed_qty)
5640 , p_action => 'U'); -- Update
5641 IF l_debug = 1 THEN
5642 trace('updated kit info for item '|| l_kit_component_list(i).component_item_id
5643 ||' in kit '||l_kit_list(1).kit_item_id);
5644 trace('get other unidentified kits for this item');
5645 END IF;
5646 l_other_kit_list.DELETE;
5647 l_other_kit_list := get_kit_list(p_organization_id,
5648 l_kit_component_list(i).component_item_id, 'Y');
5649 FOR j IN 1..l_other_kit_list.COUNT LOOP
5650 IF l_other_kit_list(j).kit_item_id <>l_kit_list(1).kit_item_id AND
5651 l_other_kit_list(j).identified_flag = 'N' THEN
5652
5653 update_kit_info
5654 (p_kit_item_id => l_other_kit_list(j).kit_item_id
5655 , p_component_item_id =>l_kit_component_list(i).component_item_id
5656 , p_top_model_line_id => l_kit_list(j).top_model_line_id
5657 , p_packed_qty => NULL
5658 , p_disp_packed_qty => NULL
5659 , p_action => 'U'); -- Update
5660 IF l_debug = 1 THEN
5661 trace('Updated the packedQty and dispQty as NULL for kit '||l_other_kit_list(j).kit_item_id||', component '||l_kit_component_list(i).component_item_id);
5662 END IF;
5663 END IF; -- End if to update kit info
5664 END LOOP; -- End loop for other kits
5665 END IF; -- End if for unidentified component
5666 END LOOP; -- End loop for all components in the kits
5667
5668 END IF; -- End if new kit or existing kit
5669
5670 ELSE
5671
5672 -- Item belongs to more than one kits
5673
5674
5675
5676 --l_item_unique_existing_kit is USED only for existing records,
5677 -- before scan of current item
5678 l_item_unique_existing_kit := is_item_unique_existing_kit(p_inventory_item_id);
5679
5680 IF l_debug = 1 THEN
5681 trace('Items scanned so far belong to multiple kits');
5682 --TO find out whether the newly scanned item is unique
5683 -- across the kits; l_new_kit_count = 1 will tell that.
5684 trace('Is last scanned item unique across all kits for scanned items,(1: Yes) Value -> '||g_kit_count_current_comp);
5685 trace('number of kits this item belongs to in existing kit-list :'||l_item_unique_existing_kit);
5686
5687 END IF;
5688
5689
5690
5691 FOR i IN 1..l_kit_list.COUNT LOOP --loop is needed to
5692 -- INSERT multiple NEW kits, but after UPDATE just exit
5693
5694 IF l_debug = 1 THEN
5695 trace('Found kits, LOOP '||i);
5696 END IF;
5697 IF l_kit_list(i).exist_flag = 'Y' THEN
5698 -- Existing kit
5699
5700 --there is no Concept of identified flag anymore!!!!
5701
5702 --see whether the item is unique in the
5703 --existing set of kits,THEN UPDATE qty OF
5704 --that UNIQUE item
5705 IF l_item_unique_existing_kit = 1 THEN
5706
5707 IF g_kit_count_current_comp > 1 THEN
5708 --means item is unique among the existing ones BUT
5709 --it braught at least one more NEW Kit
5710 --Do not update qty here, show display here
5711 --At the time of New Kit qty will be updated recursively
5712
5713 IF l_debug = 1 THEN
5714 trace(' Kit '||i||' is non-identified existing kit FOR UNIQUE item, just add to the packed qty');
5715 END IF;
5716
5717 update_kit_info
5718 (p_kit_item_id=>l_kit_list(i).kit_item_id
5719 ,p_component_item_id=> p_inventory_item_id
5720 ,p_top_model_line_id => l_kit_list(i).top_model_line_id
5721 ,p_packed_qty=> 0
5722 ,p_disp_packed_qty => '*'
5723 ,p_action => 'A'); -- Add
5724
5725
5726 ELSE
5727 --means unique kit in the existing kit, component did not
5728 --bring ANY NEW kit
5729
5730 IF l_debug = 1 THEN
5731 trace(' Kit '||i||' is non-identified existing kit FOR UNIQUE item, just add to the packed qty');
5732 END IF;
5733
5734 update_kit_info
5735 (p_kit_item_id=>l_kit_list(i).kit_item_id
5736 ,p_component_item_id=> p_inventory_item_id
5737 ,p_top_model_line_id => l_kit_list(i).top_model_line_id
5738 ,p_packed_qty=>p_quantity
5739 ,p_disp_packed_qty => to_char(p_quantity)
5740 ,p_action => 'A'); -- Add
5741
5742 END IF;
5743
5744
5745 --Do not exit Here since it has to update the correct
5746 --kit, It might not be the first one. in the API for
5747 --unmatching kits it will NOT get updated
5748
5749
5750
5751 ELSIF l_item_unique_existing_kit > 1 THEN --item belongs
5752 --TO more than one existing kit
5753
5754
5755 IF i = l_kit_list.COUNT() THEN
5756
5757 IF l_debug = 1 THEN
5758 trace(' Kit '||i||' is last non-unique component IN existing kit, modify packing qty recursively');
5759 END IF;
5760
5761 -- last record in the list of existing kits
5762 -- Add the quantity
5763 update_kit_info
5764 (p_kit_item_id=>NULL--l_kit_list(i).kit_item_id
5765 ,p_component_item_id=> p_inventory_item_id
5766 ,p_top_model_line_id => NULL--l_kit_list(i).top_model_line_id
5767 ,p_packed_qty=> p_quantity
5768 ,p_disp_packed_qty => '*'
5769 ,p_action => 'A');
5770
5771 ELSE
5772
5773 --Just update the qty to 0 and display qty fld with *
5774
5775 IF l_debug = 1 THEN
5776 trace(' Kit '||i||' is non-unique item in existing kit, just add 0 tO the packed qty,set disp_packed_qty as *');
5777 END IF;
5778
5779 -- Not identified kit
5780 -- Add to packed_qty, but disp_packed_qty is *
5781 update_kit_info
5782 (p_kit_item_id=>l_kit_list(i).kit_item_id
5783 ,p_component_item_id=> p_inventory_item_id
5784 ,p_top_model_line_id => l_kit_list(i).top_model_line_id
5785 ,p_packed_qty=> 0
5786 ,p_disp_packed_qty => '*'
5787 ,p_action => 'A');
5788
5789 END IF;
5790
5791
5792 END IF; -- is_item_unique in existing kit
5793
5794
5795 ELSE --MEANS l_kit_list(i).exist_flag = 'N'
5796 -- New kit
5797
5798 IF g_kit_count_current_comp= 1 THEN
5799 -- From (exist_flag = 'N') and (g_kit_count_current_comp = 1)
5800 -- we can infer that new scanned item braught only one NEW unique kit
5801 -- And this Kit is NOT part of existing kit-list
5802
5803 IF l_debug = 1 THEN
5804 trace(' Kit '||i||' is new unique kit for the item, insert_kit_info with packed_qty ');
5805 END IF;
5806 insert_kit_info
5807 (p_kit_item_id =>l_kit_list(i).kit_item_id
5808 , p_component_item_id =>p_inventory_item_id
5809 , p_top_model_line_id => l_kit_list(i).top_model_line_id
5810 , p_packed_qty => p_quantity
5811 , p_disp_packed_qty => p_quantity);
5812
5813
5814 EXIT;--unique, hence exit
5815
5816 ELSE -- new scanned item braught more than one
5817 --kits, Be Careful: NOT all of them might be
5818 --NEW Kit, some kits might belongs to existing kit-list
5819 IF l_debug = 1 THEN
5820 trace(' Kit '||i||' is new kit, insert_kit_info with packed_qty and *');
5821 END IF;
5822
5823 insert_kit_info
5824 (p_kit_item_id =>l_kit_list(i).kit_item_id
5825 , p_component_item_id =>p_inventory_item_id
5826 , p_top_model_line_id => l_kit_list(i).top_model_line_id
5827 , p_packed_qty => NULL
5828 , p_disp_packed_qty => '*'
5829 );
5830
5831 -- in the Kit_list() we have total number of
5832 -- kits in the order of Old and New Kits.
5833 -- Keep inserting NULL in the qty for the component Item
5834 -- for all new unique kits and
5835 -- when we are at the end of last new kit
5836 -- UPDATE the quantity for this Item.
5837
5838 IF i = l_kit_list.COUNT THEN
5839 IF l_debug = 1 THEN
5840 trace(' updating packing qty for the item across kits');
5841 END IF;
5842
5843 -- update the quantity in this call take care of
5844 --updating qty for same component across
5845 --different kits till the CURRENT p_quantity
5846 --gets exhausted
5847 update_kit_info
5848 (p_kit_item_id=>NULL--l_kit_list(i).kit_item_id
5849 ,p_component_item_id=> p_inventory_item_id
5850 ,p_top_model_line_id => NULL--l_kit_list(i).top_model_line_id
5851 ,p_packed_qty=> p_quantity
5852 ,p_disp_packed_qty => '*'
5853 ,p_action => 'A');
5854
5855 END IF;
5856
5857 END IF;
5858
5859 END IF; -- End if of existing kit or not
5860
5861 END LOOP; -- end loop of kit list
5862
5863
5864 --if all the ordered qty across all eligible kits for the scanned items (kit-component) has been
5865 --packed to fulfill the order for all eligible kits in the list of
5866 --eligible material then update the qty = order qty for
5867 --the kit component
5868
5869 IF is_item_unique_existing_kit(p_inventory_item_id) > 1 THEN
5870
5871 IF l_debug = 1 THEN
5872 trace('inside updating total qty for common item');
5873
5874 END IF;
5875
5876
5877 /*
5878 --FOR DEBUG ONLY
5879 BEGIN
5880 SELECT SUM(packed_qty), SUM(order_qty) INTO l_pack_comp_qty_total,l_ord_comp_qty_total
5881 FROM wms_packing_kitting_gtemp
5882 WHERE component_item_id = p_inventory_item_id
5883 GROUP BY component_item_id;
5884 EXCEPTION
5885 WHEN no_data_found THEN
5886 l_pack_comp_qty_total := 0;
5887 END;
5888
5889 IF l_debug = 1 THEN
5890 trace('l_pack_comp_qty_total '|| l_pack_comp_qty_total||' and l_ord_comp_qty_tota :'||l_ord_comp_qty_total);
5891
5892 END IF;
5893 --FOR DEBUG ONLY
5894 */
5895
5896
5897 BEGIN
5898 SELECT 1 INTO l_common_qty_filled FROM dual WHERE exists
5899 (SELECT 1
5900 FROM wms_packing_kitting_gtemp
5901 WHERE component_item_id = p_inventory_item_id
5902 AND ((packed_qty <> order_qty AND packed_qty IS NOT
5903 NULL) OR (packed_qty IS NULL) ));
5904
5905 EXCEPTION
5906 WHEN no_data_found THEN
5907 l_common_qty_filled := 0;--requirement of qty for the
5908 --compenent across ALL kits have been fulfilled
5909 WHEN too_many_rows THEN
5910 l_common_qty_filled := 1;
5911 END;
5912
5913
5914 IF l_common_qty_filled = 0 THEN
5915
5916 --update disp qty for the current common item across all kits
5917 OPEN c_update_disp_qty_common_comp;
5918 LOOP
5919 FETCH c_update_disp_qty_common_comp INTO l_update_disp_qty_common_comp;
5920
5921 IF c_update_disp_qty_common_comp%notfound THEN
5922 CLOSE c_update_disp_qty_common_comp;
5923 EXIT;
5924 END IF;
5925
5926 UPDATE wms_packing_kitting_gtemp
5927 SET packed_qty_disp = l_update_disp_qty_common_comp.packed_qty
5928 WHERE kit_item_id = l_update_disp_qty_common_comp.kit_item_id
5929 AND component_item_id = l_update_disp_qty_common_comp.component_item_id
5930 AND top_model_line_id = l_update_disp_qty_common_comp.top_model_line_id;
5931
5932
5933 END LOOP;
5934
5935 --Now update kit model information
5936 update_kit_model_info
5937 ( p_kit_item_id => NULL
5938 , p_component_item_id => p_inventory_item_id
5939 , p_top_model_line_id => NULL);
5940
5941 END IF;
5942
5943
5944 END IF; --the item is a common component across kits
5945
5946
5947 END IF; -- end if of only one kit or multiple kits
5948
5949 EXCEPTION
5950 WHEN others THEN
5951 IF l_debug = 1 THEN
5952 trace('Unexpected Error in get_kitting_info');
5953 trace('ERROR Code ='||SQLCODE);
5954 trace('ERROR Message='||SQLERRM);
5955 END IF;
5956
5957 END get_kitting_info;
5958
5959
5960 FUNCTION is_kit_identified(p_kit_id IN NUMBER) RETURN VARCHAR2 IS
5961 l_exist NUMBER := 0;
5962 BEGIN
5963 BEGIN
5964 SELECT 1 INTO l_exist
5965 FROM dual
5966 WHERE exists(
5967 SELECT 1 FROM wms_packing_kitting_gtemp
5968 WHERE kit_item_id = p_kit_id
5969 AND component_item_id IS NOT NULL
5970 AND packed_qty IS NOT NULL
5971 AND packed_qty_disp = '*');
5972 EXCEPTION
5973 WHEN no_data_found THEN
5974 l_exist := 0;
5975 END;
5976 IF l_exist = 0 THEN
5977 RETURN 'Y';
5978 ELSE
5979 RETURN 'N';
5980 END IF;
5981
5982 END is_kit_identified;
5983
5984
5985 FUNCTION is_item_unique_existing_kit(p_component_id IN NUMBER) RETURN NUMBER IS
5986 l_cnt NUMBER := 0;
5987 BEGIN
5988
5989 BEGIN
5990 SELECT COUNT(1) INTO l_cnt FROM wms_packing_kitting_gtemp
5991 WHERE component_item_id = p_component_id;
5992 EXCEPTION
5993 WHEN no_data_found THEN
5994 l_cnt := 0; --is not there, unique
5995 END;
5996
5997 RETURN l_cnt;
5998
5999 END is_item_unique_existing_kit;
6000
6001
6002
6003 /**********************************
6004 * Procedure to set savepoint
6005 * This is called from the form/library
6006 * The savepoint can be set currently are
6007 * PACK_START
6008 * BEFORE_TM
6009 * Return value:
6010 * 0 Success
6011 * -1 Failed to set savepoint
6012 **************************************/
6013 PROCEDURE issue_savepoint(p_savepoint VARCHAR2) IS
6014 BEGIN
6015 IF p_savepoint = 'PACK_START' THEN
6016 SAVEPOINT PACK_START;
6017 ELSIF p_savepoint = 'BEFORE_TM' THEN
6018 SAVEPOINT BEFORE_TM;
6019 ELSE
6020 IF l_debug = 1 THEN
6021 trace('Wrong name for p_savepoint '||p_savepoint);
6022 END IF;
6023 END IF;
6024 IF l_debug = 1 THEN
6025 trace('Set savepoint '||p_savepoint);
6026 END IF;
6027 EXCEPTION
6028 WHEN others THEN
6029 IF l_debug = 1 THEN
6030 trace('Error when setting savepoint '||p_savepoint||' in issue_savepoint');
6031 trace('ERROR Code ='||SQLCODE);
6032 trace('ERROR Message='||SQLERRM);
6033 END IF;
6034 END issue_savepoint;
6035
6036 /**********************************
6037 * Procedure to issue rollback to savepoint
6038 * This is called from the form/library
6039 * The savepoint can be rollback currently are
6040 * PACK_START
6041 * BEFORE_TM
6042 * NULL : Rollback everything
6043 **************************************/
6044 PROCEDURE issue_rollback(p_savepoint VARCHAR2) IS
6045 BEGIN
6046 IF p_savepoint IS NULL THEN
6047 ROLLBACK;
6048 ELSIF p_savepoint = 'PACK_START' THEN
6049 ROLLBACK TO PACK_START;
6050 ELSIF p_savepoint = 'BEFORE_TM' THEN
6051 ROLLBACK TO BEFORE_TM;
6052 ELSE
6053 IF l_debug = 1 THEN
6054 trace('Wrong name for p_savepoint '||p_savepoint||' in issue_rollback');
6055 END IF;
6056 END IF;
6057 IF l_debug = 1 THEN
6058 trace('Rollback '||p_savepoint);
6059 END IF;
6060 EXCEPTION
6061 WHEN others THEN
6062 IF l_debug = 1 THEN
6063 trace('Error in issue rollback for '||p_savepoint);
6064 trace('ERROR Code ='||SQLCODE);
6065 trace('ERROR Message='||SQLERRM);
6066 END IF;
6067 END issue_rollback;
6068
6069 /**********************************
6070 * Procedure to issue commit
6071 * This is called from the form/library
6072 **************************************/
6073 PROCEDURE issue_commit IS
6074 BEGIN
6075 commit;
6076 END issue_commit;
6077 END WMS_PACKING_WORKBENCH_PVT;
[Home] [Help]