[Home] [Help]
PACKAGE BODY: APPS.GML_RCV_COMMON_APIS
Source
1 PACKAGE BODY GML_RCV_COMMON_APIS AS
2 /* $Header: GMLRCVAB.pls 120.0 2005/05/25 16:53:16 appldev noship $*/
3
4 -- Global constant holding the package name
5 g_pkg_name CONSTANT VARCHAR2(30) := 'GML_RCV_COMMON_APIS';
6
7
8 PROCEDURE insert_mtlt(p_mtlt_rec mtl_transaction_lots_temp%ROWTYPE) IS
9 BEGIN
10 INSERT INTO mtl_transaction_lots_temp
11 (
12 transaction_temp_id
13 , last_update_date
14 , last_updated_by
15 , creation_date
16 , created_by
17 , last_update_login
18 , request_id
19 , program_application_id
20 , program_id
21 , program_update_date
22 , transaction_quantity
23 , primary_quantity
24 , lot_number
25 , lot_expiration_date
26 , ERROR_CODE
27 , serial_transaction_temp_id
28 , group_header_id
29 , put_away_rule_id
30 , pick_rule_id
31 , description
32 , vendor_id
33 , supplier_lot_number
34 , territory_code
35 , --country_of_origin,
36 origination_date
37 , date_code
38 , grade_code
39 , change_date
40 , maturity_date
41 , status_id
42 , retest_date
43 , age
44 , item_size
45 , color
46 , volume
47 , volume_uom
48 , place_of_origin
49 , --kill_date,
50 best_by_date
51 , LENGTH
52 , length_uom
53 , recycled_content
54 , thickness
55 , thickness_uom
56 , width
57 , width_uom
58 , curl_wrinkle_fold
59 --- Added the following 5 comlumns for OPM
60 , sublot_num
61 , reason_code
62 , SECONDARY_QUANTITY
63 , SECONDARY_UNIT_OF_MEASURE
64 , qc_grade
65 , lot_attribute_category
66 , c_attribute1
67 , c_attribute2
68 , c_attribute3
69 , c_attribute4
70 , c_attribute5
71 , c_attribute6
72 , c_attribute7
73 , c_attribute8
74 , c_attribute9
75 , c_attribute10
76 , c_attribute11
77 , c_attribute12
78 , c_attribute13
79 , c_attribute14
80 , c_attribute15
81 , c_attribute16
82 , c_attribute17
83 , c_attribute18
84 , c_attribute19
85 , c_attribute20
86 , d_attribute1
87 , d_attribute2
88 , d_attribute3
89 , d_attribute4
90 , d_attribute5
91 , d_attribute6
92 , d_attribute7
93 , d_attribute8
94 , d_attribute9
95 , d_attribute10
96 , n_attribute1
97 , n_attribute2
98 , n_attribute3
99 , n_attribute4
100 , n_attribute5
101 , n_attribute6
102 , n_attribute7
103 , n_attribute8
104 , n_attribute9
105 , n_attribute10
106 , vendor_name
107 )
108 VALUES (
109 p_mtlt_rec.transaction_temp_id
110 , p_mtlt_rec.last_update_date
111 , p_mtlt_rec.last_updated_by
112 , p_mtlt_rec.creation_date
113 , p_mtlt_rec.created_by
114 , p_mtlt_rec.last_update_login
115 , p_mtlt_rec.request_id
116 , p_mtlt_rec.program_application_id
117 , p_mtlt_rec.program_id
118 , p_mtlt_rec.program_update_date
119 , p_mtlt_rec.transaction_quantity
120 , p_mtlt_rec.primary_quantity
121 , p_mtlt_rec.lot_number
122 , p_mtlt_rec.lot_expiration_date
123 , p_mtlt_rec.ERROR_CODE
124 , p_mtlt_rec.serial_transaction_temp_id
125 , p_mtlt_rec.group_header_id
126 , p_mtlt_rec.put_away_rule_id
127 , p_mtlt_rec.pick_rule_id
128 , p_mtlt_rec.description
129 , p_mtlt_rec.vendor_id
130 , p_mtlt_rec.supplier_lot_number
131 , p_mtlt_rec.territory_code
132 , --p_mtlt_rec.country_of_origin,
133 p_mtlt_rec.origination_date
134 , p_mtlt_rec.date_code
135 , p_mtlt_rec.grade_code
136 , p_mtlt_rec.change_date
137 , p_mtlt_rec.maturity_date
138 , p_mtlt_rec.status_id
139 , p_mtlt_rec.retest_date
140 , p_mtlt_rec.age
141 , p_mtlt_rec.item_size
142 , p_mtlt_rec.color
143 , p_mtlt_rec.volume
144 , p_mtlt_rec.volume_uom
145 , p_mtlt_rec.place_of_origin
146 , --p_mtlt_rec.kill_date,
147 p_mtlt_rec.best_by_date
148 , p_mtlt_rec.LENGTH
149 , p_mtlt_rec.length_uom
150 , p_mtlt_rec.recycled_content
151 , p_mtlt_rec.thickness
152 , p_mtlt_rec.thickness_uom
153 , p_mtlt_rec.width
154 , p_mtlt_rec.width_uom
155 , p_mtlt_rec.curl_wrinkle_fold
156 --- Added the following 5 comlumns for OPM
157 , p_mtlt_rec.sublot_num
158 , p_mtlt_rec.reason_code
159 , p_mtlt_rec.SECONDARY_QUANTITY
160 , p_mtlt_rec.SECONDARY_UNIT_OF_MEASURE
161 , p_mtlt_rec.qc_grade
162 , p_mtlt_rec.lot_attribute_category
163 , p_mtlt_rec.c_attribute1
164 , p_mtlt_rec.c_attribute2
165 , p_mtlt_rec.c_attribute3
166 , p_mtlt_rec.c_attribute4
167 , p_mtlt_rec.c_attribute5
168 , p_mtlt_rec.c_attribute6
169 , p_mtlt_rec.c_attribute7
170 , p_mtlt_rec.c_attribute8
171 , p_mtlt_rec.c_attribute9
172 , p_mtlt_rec.c_attribute10
173 , p_mtlt_rec.c_attribute11
174 , p_mtlt_rec.c_attribute12
175 , p_mtlt_rec.c_attribute13
176 , p_mtlt_rec.c_attribute14
177 , p_mtlt_rec.c_attribute15
178 , p_mtlt_rec.c_attribute16
179 , p_mtlt_rec.c_attribute17
180 , p_mtlt_rec.c_attribute18
181 , p_mtlt_rec.c_attribute19
182 , p_mtlt_rec.c_attribute20
183 , p_mtlt_rec.d_attribute1
184 , p_mtlt_rec.d_attribute2
185 , p_mtlt_rec.d_attribute3
186 , p_mtlt_rec.d_attribute4
187 , p_mtlt_rec.d_attribute5
188 , p_mtlt_rec.d_attribute6
189 , p_mtlt_rec.d_attribute7
190 , p_mtlt_rec.d_attribute8
191 , p_mtlt_rec.d_attribute9
192 , p_mtlt_rec.d_attribute10
193 , p_mtlt_rec.n_attribute1
194 , p_mtlt_rec.n_attribute2
195 , p_mtlt_rec.n_attribute3
196 , p_mtlt_rec.n_attribute4
197 , p_mtlt_rec.n_attribute5
198 , p_mtlt_rec.n_attribute6
199 , p_mtlt_rec.n_attribute7
200 , p_mtlt_rec.n_attribute8
201 , p_mtlt_rec.n_attribute9
202 , p_mtlt_rec.n_attribute10
203 , p_mtlt_rec.vendor_name
204 );
205 END insert_mtlt;
206
207 FUNCTION break_lots_only(p_original_tid IN mtl_transaction_lots_temp.transaction_temp_id%TYPE, p_new_transactions_tb IN trans_rec_tb_tp)
208 RETURN BOOLEAN IS
209 CURSOR c_lots IS
210 SELECT ROWID
211 , transaction_temp_id
212 , last_update_date
213 , last_updated_by
214 , creation_date
215 , created_by
216 , last_update_login
217 , request_id
218 , program_application_id
219 , program_id
220 , program_update_date
221 , transaction_quantity
222 , primary_quantity
223 , lot_number
224 , lot_expiration_date
225 , ERROR_CODE
226 , serial_transaction_temp_id
227 , group_header_id
228 , put_away_rule_id
229 , pick_rule_id
230 , description
231 , vendor_id
232 , supplier_lot_number
233 , territory_code
234 , origination_date
235 , date_code
236 , grade_code
237 , change_date
238 , maturity_date
239 , status_id
240 , retest_date
241 , age
242 , item_size
243 , color
244 , volume
245 , volume_uom
246 , place_of_origin
247 , best_by_date
248 , LENGTH
249 , length_uom
250 , recycled_content
251 , thickness
252 , thickness_uom
253 , width
254 , width_uom
255 , curl_wrinkle_fold
256 --- Added the following 5 comlumns for OPM
257 , sublot_num
258 , reason_code
259 , SECONDARY_QUANTITY
260 , SECONDARY_UNIT_OF_MEASURE
261 , qc_grade
262 , lot_attribute_category
263 , c_attribute1
264 , c_attribute2
265 , c_attribute3
266 , c_attribute4
267 , c_attribute5
268 , c_attribute6
269 , c_attribute7
270 , c_attribute8
271 , c_attribute9
272 , c_attribute10
273 , c_attribute11
274 , c_attribute12
275 , c_attribute13
276 , c_attribute14
277 , c_attribute15
278 , c_attribute16
279 , c_attribute17
280 , c_attribute18
281 , c_attribute19
282 , c_attribute20
283 , d_attribute1
284 , d_attribute2
285 , d_attribute3
286 , d_attribute4
287 , d_attribute5
288 , d_attribute6
289 , d_attribute7
290 , d_attribute8
291 , d_attribute9
292 , d_attribute10
293 , n_attribute1
294 , n_attribute2
295 , n_attribute3
296 , n_attribute4
297 , n_attribute5
298 , n_attribute6
299 , n_attribute7
300 , n_attribute8
301 , n_attribute9
302 , n_attribute10
303 , vendor_name
304 FROM mtl_transaction_lots_temp
305 WHERE transaction_temp_id = p_original_tid
306 ORDER BY DECODE(
307 inv_rcv_common_apis.g_order_lots_by
308 , inv_rcv_common_apis.g_order_lots_by_exp_date, lot_expiration_date
309 , inv_rcv_common_apis.g_order_lots_by_creation_date, creation_date
310 , lot_expiration_date
311 );
312
313 --Changed the order by for bug 2422193
314 --ORDER BY lot_expiration_date,creation_date;
318 l_new_primary_quantity NUMBER; -- the quanity user wants to split
315
316 l_mtlt_rec mtl_transaction_lots_temp%ROWTYPE;
317 l_new_transaction_temp_id mtl_transaction_lots_temp.transaction_temp_id%TYPE;
319 l_transaction_temp_id mtl_transaction_lots_temp.transaction_temp_id%TYPE;
320 l_primary_quantity NUMBER; -- the primary qty for lot
321 l_secondary_quantity NUMBER; -- the primary qty for lot
322 l_transaction_quantity NUMBER;
323 l_lot_number mtl_transaction_lots_temp.lot_number%TYPE;
324 l_sublot_number mtl_transaction_lots_temp.sublot_num%TYPE;
325 l_item_no VARCHAR2(40);
326 l_unit_of_measure VARCHAR2(100);
327
328 --BUG 2673970
329 l_rowid ROWID;
330 BEGIN
331 FOR i IN 1 .. p_new_transactions_tb.COUNT LOOP -- Loop through all the transaction lines need to be splitted
332 l_new_transaction_temp_id := p_new_transactions_tb(i).transaction_id;
333 l_new_primary_quantity := p_new_transactions_tb(i).primary_quantity;
334 l_item_no := p_new_transactions_tb(i).item_no;
335 l_unit_of_measure := p_new_transactions_tb(i).unit_of_measure;
336 OPEN c_lots;
337
338 LOOP -- Loop through all the lot record for this transaction
339
340 --BUG 2673970
341 FETCH c_lots INTO l_rowid
342 , l_mtlt_rec.transaction_temp_id
343 , l_mtlt_rec.last_update_date
344 , l_mtlt_rec.last_updated_by
345 , l_mtlt_rec.creation_date
346 , l_mtlt_rec.created_by
347 , l_mtlt_rec.last_update_login
348 , l_mtlt_rec.request_id
349 , l_mtlt_rec.program_application_id
350 , l_mtlt_rec.program_id
351 , l_mtlt_rec.program_update_date
352 , l_mtlt_rec.transaction_quantity
353 , l_mtlt_rec.primary_quantity
354 , l_mtlt_rec.lot_number
355 , l_mtlt_rec.lot_expiration_date
356 , l_mtlt_rec.ERROR_CODE
357 , l_mtlt_rec.serial_transaction_temp_id
358 , l_mtlt_rec.group_header_id
359 , l_mtlt_rec.put_away_rule_id
360 , l_mtlt_rec.pick_rule_id
361 , l_mtlt_rec.description
362 , l_mtlt_rec.vendor_id
363 , l_mtlt_rec.supplier_lot_number
364 , l_mtlt_rec.territory_code
365 , l_mtlt_rec.origination_date
366 , l_mtlt_rec.date_code
367 , l_mtlt_rec.grade_code
368 , l_mtlt_rec.change_date
369 , l_mtlt_rec.maturity_date
370 , l_mtlt_rec.status_id
371 , l_mtlt_rec.retest_date
372 , l_mtlt_rec.age
373 , l_mtlt_rec.item_size
374 , l_mtlt_rec.color
375 , l_mtlt_rec.volume
376 , l_mtlt_rec.volume_uom
377 , l_mtlt_rec.place_of_origin
378 , l_mtlt_rec.best_by_date
379 , l_mtlt_rec.LENGTH
380 , l_mtlt_rec.length_uom
381 , l_mtlt_rec.recycled_content
382 , l_mtlt_rec.thickness
383 , l_mtlt_rec.thickness_uom
384 , l_mtlt_rec.width
385 , l_mtlt_rec.width_uom
386 , l_mtlt_rec.curl_wrinkle_fold
387 --- Added the following 5 comlumns for OPM
388 , l_mtlt_rec.sublot_num
389 , l_mtlt_rec.reason_code
390 , l_mtlt_rec.SECONDARY_QUANTITY
391 , l_mtlt_rec.SECONDARY_UNIT_OF_MEASURE
392 , l_mtlt_rec.qc_grade
393 , l_mtlt_rec.lot_attribute_category
394 , l_mtlt_rec.c_attribute1
395 , l_mtlt_rec.c_attribute2
396 , l_mtlt_rec.c_attribute3
397 , l_mtlt_rec.c_attribute4
398 , l_mtlt_rec.c_attribute5
399 , l_mtlt_rec.c_attribute6
400 , l_mtlt_rec.c_attribute7
401 , l_mtlt_rec.c_attribute8
402 , l_mtlt_rec.c_attribute9
403 , l_mtlt_rec.c_attribute10
404 , l_mtlt_rec.c_attribute11
405 , l_mtlt_rec.c_attribute12
406 , l_mtlt_rec.c_attribute13
407 , l_mtlt_rec.c_attribute14
408 , l_mtlt_rec.c_attribute15
409 , l_mtlt_rec.c_attribute16
410 , l_mtlt_rec.c_attribute17
411 , l_mtlt_rec.c_attribute18
412 , l_mtlt_rec.c_attribute19
413 , l_mtlt_rec.c_attribute20
414 , l_mtlt_rec.d_attribute1
415 , l_mtlt_rec.d_attribute2
416 , l_mtlt_rec.d_attribute3
417 , l_mtlt_rec.d_attribute4
418 , l_mtlt_rec.d_attribute5
419 , l_mtlt_rec.d_attribute6
420 , l_mtlt_rec.d_attribute7
421 , l_mtlt_rec.d_attribute8
422 , l_mtlt_rec.d_attribute9
423 , l_mtlt_rec.d_attribute10
424 , l_mtlt_rec.n_attribute1
425 , l_mtlt_rec.n_attribute2
426 , l_mtlt_rec.n_attribute3
427 , l_mtlt_rec.n_attribute4
428 , l_mtlt_rec.n_attribute5
429 , l_mtlt_rec.n_attribute6
430 , l_mtlt_rec.n_attribute7
431 , l_mtlt_rec.n_attribute8
432 , l_mtlt_rec.n_attribute9
433 , l_mtlt_rec.n_attribute10
434 , l_mtlt_rec.vendor_name;
435 EXIT WHEN c_lots%NOTFOUND;
436
437 l_primary_quantity := l_mtlt_rec.primary_quantity; -- initial qty for this lot
438 l_transaction_temp_id := l_mtlt_rec.transaction_temp_id; -- initial txn_int_id for this lot
439 l_lot_number := l_mtlt_rec.lot_number;
440 l_sublot_number := l_mtlt_rec.sublot_num;
441 l_transaction_quantity := l_mtlt_rec.transaction_quantity;
442
443 IF (l_primary_quantity > l_new_primary_quantity) -- new quantity detailed completely
444 -- and there is remaining lot qty
445 THEN
446 l_mtlt_rec.transaction_temp_id := l_new_transaction_temp_id;
450 IF l_mtlt_rec.secondary_unit_of_measure IS NOT NULL THEN
447 l_mtlt_rec.primary_quantity := l_new_primary_quantity;
448 l_mtlt_rec.transaction_quantity := l_transaction_quantity * l_new_primary_quantity / l_primary_quantity;
449
451 --- Calculate secondary qty
452 GML_MOBILE_RECEIPT.Calculate_Secondary_Qty(
453 p_item_no => l_item_no,
454 p_unit_of_measure => l_unit_of_measure,
455 p_quantity => l_mtlt_rec.transaction_quantity,
456 p_lot_no =>l_lot_number,
457 p_sublot_no =>l_sublot_number,
458 p_secondary_unit_of_measure => l_mtlt_rec.secondary_unit_of_measure,
459 x_secondary_quantity => l_mtlt_rec.secondary_quantity);
460 END IF;
461
462
463
464 insert_mtlt(l_mtlt_rec); -- insert one line with new quantity and new txn_id
465
466 l_primary_quantity := l_primary_quantity - l_new_primary_quantity;
467 l_transaction_quantity := l_transaction_quantity - l_mtlt_rec.transaction_quantity;
468
469 -- Update the existing lot rec with reduced quantity
470
471
472 IF l_mtlt_rec.secondary_unit_of_measure IS NOT NULL THEN
473 --- Calculate secondary qty
474 GML_MOBILE_RECEIPT.Calculate_Secondary_Qty(
475 p_item_no => l_item_no,
476 p_unit_of_measure => l_unit_of_measure,
477 p_quantity => l_transaction_quantity,
478 p_lot_no =>l_lot_number,
479 p_sublot_no =>l_sublot_number,
480 p_secondary_unit_of_measure => l_mtlt_rec.secondary_unit_of_measure,
481 x_secondary_quantity => l_secondary_quantity);
482 END IF;
483
484 IF l_sublot_number IS NULL OR l_sublot_number = '' THEN
485 UPDATE mtl_transaction_lots_temp
486 SET primary_quantity = l_primary_quantity
487 , transaction_quantity = l_transaction_quantity
488 , secondary_quantity = l_secondary_quantity
489 WHERE transaction_temp_id = l_transaction_temp_id
490 AND lot_number = l_lot_number
491 AND ROWID = l_rowid;
492 ELSE
493 UPDATE mtl_transaction_lots_temp
494 SET primary_quantity = l_primary_quantity
495 , transaction_quantity = l_transaction_quantity
496 , secondary_quantity = l_secondary_quantity
497 WHERE transaction_temp_id = l_transaction_temp_id
498 AND lot_number = l_lot_number
499 AND sublot_num = l_sublot_number
500 AND ROWID = l_rowid;
501 END IF;
502
503 EXIT; -- exit lot loop
504
505 ELSIF(l_primary_quantity < l_new_primary_quantity) THEN
506 -- new quantity is partially detailed
507 -- lot qty is exhausted
508 -- need to continue lot loop in this case
509
510
511 IF l_sublot_number IS NULL OR l_sublot_number = '' THEN
512 -- Update the lot rec with new transaction interface ID
513 UPDATE mtl_transaction_lots_temp
514 SET transaction_temp_id = l_new_transaction_temp_id
515 WHERE transaction_temp_id = l_transaction_temp_id
516 AND lot_number = l_lot_number
517 AND ROWID = l_rowid;
518 ELSE
519 UPDATE mtl_transaction_lots_temp
520 SET transaction_temp_id = l_new_transaction_temp_id
521 WHERE transaction_temp_id = l_transaction_temp_id
522 AND lot_number = l_lot_number
523 AND sublot_num = l_sublot_number
524 AND ROWID = l_rowid;
525 END IF;
526
527 -- reduce the new qty
528 l_new_primary_quantity := l_new_primary_quantity - l_primary_quantity;
529 ELSIF(l_primary_quantity = l_new_primary_quantity) THEN
530 -- exact match
531
532 IF l_sublot_number IS NULL OR l_sublot_number = '' THEN
533 -- Update the lot rec with new transaction interface ID
534 UPDATE mtl_transaction_lots_temp
535 SET transaction_temp_id = l_new_transaction_temp_id
536 WHERE transaction_temp_id = l_transaction_temp_id
537 AND lot_number = l_lot_number
538 AND ROWID = l_rowid;
539
540 ELSE
541 UPDATE mtl_transaction_lots_temp
542 SET transaction_temp_id = l_new_transaction_temp_id
543 WHERE transaction_temp_id = l_transaction_temp_id
544 AND lot_number = l_lot_number
545 AND sublot_num = l_sublot_number
546 AND ROWID = l_rowid;
547 END IF;
548
549 EXIT; -- exit lot loop
550 END IF;
551 END LOOP; -- end lot loop
552
553 CLOSE c_lots;
554 END LOOP; -- end transaction line loop
555
556 RETURN TRUE;
557 EXCEPTION
558 WHEN OTHERS THEN
559 IF c_lots%ISOPEN THEN
560 CLOSE c_lots;
561 END IF;
562
563 RAISE;
564 END break_lots_only;
565
566
567 PROCEDURE BREAK(
568 p_original_tid IN mtl_transaction_lots_temp.transaction_temp_id%TYPE
569 , p_new_transactions_tb IN trans_rec_tb_tp
570 , p_lot_control_code IN NUMBER
571 , p_serial_control_code IN NUMBER
572 ) IS
573 BEGIN
574
575 IF break_lots_only(p_original_tid, p_new_transactions_tb) THEN
576 NULL;
577 END IF;
578
582 IF break_lots_only(p_original_tid, p_new_transactions_tb) THEN
579 /** The following is not needed as OPM does not support serials
580 IF (p_lot_control_code = 2
581 AND p_serial_control_code IN(1)) THEN
583 NULL;
584 END IF;
585 --serials not lots
586 -- Toshiba Fixes for RMA
587 ELSIF(p_lot_control_code = 1
588 AND p_serial_control_code NOT IN(1)) THEN
589 IF break_serials_only(p_original_tid, p_new_transactions_tb) THEN
590 NULL;
591 END IF;
592 --both lot and serial
593 ELSIF(p_lot_control_code = 2
594 AND p_serial_control_code NOT IN(1)) THEN
595 IF break_lots_serials(p_original_tid, p_new_transactions_tb) THEN
596 NULL;
597 END IF;
598 END IF;
599 */
600 END BREAK;
601
602 END GML_RCV_COMMON_APIS;