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