[Home] [Help]
PACKAGE BODY: APPS.INV_RCV_INTEGRATION_APIS
Source
1 PACKAGE BODY inv_rcv_integration_apis AS
2 /* $Header: INVRCVIB.pls 120.17.12020000.3 2012/08/23 11:54:05 raminoch ship $*/
3
4 g_pkg_name CONSTANT VARCHAR2(30) := 'INV_RCV_INTEGRATION_APIS';
5
6 TYPE mol_rec IS RECORD
7 (line_id NUMBER DEFAULT NULL
8 ,line_number NUMBER DEFAULT NULL
9 ,header_id NUMBER DEFAULT NULL
10 ,quantity NUMBER DEFAULT NULL
11 ,primary_quantity NUMBER DEFAULT NULL
12 ,quantity_delivered NUMBER DEFAULT NULL
13 ,quantity_detailed NUMBER DEFAULT NULL
14 ,uom_code VARCHAR2(3) DEFAULT NULL
15 ,inventory_item_id NUMBER DEFAULT NULL
16 ,organization_id NUMBER DEFAULT NULL
17 ,secondary_uom VARCHAR2(3) DEFAULT NULL
18 ,secondary_quantity NUMBER DEFAULT NULL
19 ,secondary_quantity_delivered NUMBER DEFAULT NULL --OPM Convergence
20 ,secondary_quantity_detailed NUMBER DEFAULT NULL --OPM Convergence
21 ,secondary_required_quantity NUMBER DEFAULT NULL --OPM Convergence
22 ,backorder_delivery_detail_id NUMBER DEFAULT NULL
23 ,crossdock_type NUMBER DEFAULT NULL
24 );
25
26 PROCEDURE print_debug(p_err_msg VARCHAR2, p_level NUMBER) IS
27 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
28 BEGIN
29 IF (l_debug = 1) THEN
30 inv_mobile_helper_functions.tracelog(p_err_msg => p_err_msg, p_module => g_pkg_name||'($Revision: 120.17.12020000.3 $)', p_level => p_level);
31 END IF;
32
33 END print_debug;
34
35
36 procedure insert_wlpni
37 (p_api_version IN NUMBER
38 , p_init_msg_lst IN VARCHAR2
39 , x_return_status OUT NOCOPY VARCHAR2
40 , x_msg_count OUT NOCOPY NUMBER
41 , x_msg_data OUT NOCOPY VARCHAR2
42 , p_ORGANIZATION_ID IN NUMBER
43 , p_LPN_ID IN NUMBER
44 , p_license_plate_number IN VARCHAR2
45 , p_LPN_GROUP_ID IN NUMBER
46 , p_PARENT_LPN_ID IN NUMBER
47 , p_PARENT_LICENSE_PLATE_NUMBER IN VARCHAR2
48 , p_REQUEST_ID IN NUMBER
49 , p_INVENTORY_ITEM_ID IN NUMBER
50 , p_REVISION IN VARCHAR2
51 , p_LOT_NUMBER IN VARCHAR2
52 , p_SERIAL_NUMBER IN VARCHAR2
53 , p_SUBINVENTORY_CODE IN VARCHAR2
54 , p_LOCATOR_ID IN NUMBER
55 , p_GROSS_WEIGHT_UOM_CODE IN VARCHAR2
56 , p_GROSS_WEIGHT IN NUMBER
57 , p_CONTENT_VOLUME_UOM_CODE IN VARCHAR2
58 , p_CONTENT_VOLUME IN NUMBER
59 , p_TARE_WEIGHT_UOM_CODE IN VARCHAR2
60 , p_TARE_WEIGHT IN NUMBER
61 , p_STATUS_ID IN NUMBER
62 , p_SEALED_STATUS IN NUMBER
63 , p_ATTRIBUTE_CATEGORY IN VARCHAR2
64 , p_ATTRIBUTE1 IN VARCHAR2
65 , p_ATTRIBUTE2 IN VARCHAR2
66 , p_ATTRIBUTE3 IN VARCHAR2
67 , p_ATTRIBUTE4 IN VARCHAR2
68 , p_ATTRIBUTE5 IN VARCHAR2
69 , p_ATTRIBUTE6 IN VARCHAR2
70 , p_ATTRIBUTE7 IN VARCHAR2
71 , p_ATTRIBUTE8 IN VARCHAR2
72 , p_ATTRIBUTE9 IN VARCHAR2
73 , p_ATTRIBUTE10 IN VARCHAR2
74 , p_ATTRIBUTE11 IN VARCHAR2
75 , p_ATTRIBUTE12 IN VARCHAR2
76 , p_ATTRIBUTE13 IN VARCHAR2
77 , p_ATTRIBUTE14 IN VARCHAR2
78 , p_ATTRIBUTE15 IN VARCHAR2
79 , p_COST_GROUP_ID IN NUMBER
80 , p_LPN_CONTEXT IN NUMBER
81 , p_LPN_REUSABILITY IN NUMBER
82 , p_OUTERMOST_LPN_ID IN NUMBER
83 , p_outermost_lpn IN VARCHAR2
84 , p_HOMOGENEOUS_CONTAINER IN NUMBER
85 , p_SOURCE_TYPE_ID IN NUMBER
86 , p_SOURCE_HEADER_ID IN NUMBER
87 , p_SOURCE_LINE_ID IN NUMBER
88 , p_SOURCE_LINE_DETAIL_ID IN NUMBER
89 , p_SOURCE_NAME IN VARCHAR2
90 ) IS
91 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
92 BEGIN
93 x_return_status := g_ret_sts_success;
94
95 IF (p_lpn_id IS NOT NULL OR p_license_plate_number IS NOT NULL) THEN
96 inv_rcv_integration_pvt.insert_wlpni
97 (x_return_status => x_return_status
98 , x_msg_count => x_msg_count
99 , x_msg_data => x_msg_data
100 , p_LPN_ID => p_LPN_ID
101 , p_license_plate_number => p_license_plate_number
102 , p_PARENT_LPN_ID => p_PARENT_LPN_ID
103 , p_PARENT_LICENSE_PLATE_NUMBER => p_PARENT_LICENSE_PLATE_NUMBER
104 , p_REQUEST_ID => p_REQUEST_ID
105 , p_INVENTORY_ITEM_ID => p_INVENTORY_ITEM_ID
106 , p_REVISION => p_REVISION
107 , p_LOT_NUMBER => p_LOT_NUMBER
108 , p_SERIAL_NUMBER => p_SERIAL_NUMBER
109 , p_ORGANIZATION_ID => p_ORGANIZATION_ID
110 , p_SUBINVENTORY_CODE => p_SUBINVENTORY_CODE
111 , p_LOCATOR_ID => p_LOCATOR_ID
112 , p_GROSS_WEIGHT_UOM_CODE => p_GROSS_WEIGHT_UOM_CODE
113 , p_GROSS_WEIGHT => p_GROSS_WEIGHT
114 , p_CONTENT_VOLUME_UOM_CODE => p_CONTENT_VOLUME_UOM_CODE
115 , p_CONTENT_VOLUME => p_CONTENT_VOLUME
116 , p_TARE_WEIGHT_UOM_CODE => p_TARE_WEIGHT_UOM_CODE
117 , p_TARE_WEIGHT => p_TARE_WEIGHT
118 , p_STATUS_ID => p_STATUS_ID
119 , p_SEALED_STATUS => p_SEALED_STATUS
120 , p_ATTRIBUTE_CATEGORY => p_ATTRIBUTE_CATEGORY
121 , p_ATTRIBUTE1 => p_ATTRIBUTE1
122 , p_ATTRIBUTE2 => p_ATTRIBUTE2
123 , p_ATTRIBUTE3 => p_ATTRIBUTE3
124 , p_ATTRIBUTE4 => p_ATTRIBUTE4
125 , p_ATTRIBUTE5 => p_ATTRIBUTE5
126 , p_ATTRIBUTE6 => p_ATTRIBUTE6
127 , p_ATTRIBUTE7 => p_ATTRIBUTE7
128 , p_ATTRIBUTE8 => p_ATTRIBUTE8
129 , p_ATTRIBUTE9 => p_ATTRIBUTE9
130 , p_ATTRIBUTE10 => p_ATTRIBUTE10
131 , p_ATTRIBUTE11 => p_ATTRIBUTE11
132 , p_ATTRIBUTE12 => p_ATTRIBUTE12
133 , p_ATTRIBUTE13 => p_ATTRIBUTE13
134 , p_ATTRIBUTE14 => p_ATTRIBUTE14
135 , p_ATTRIBUTE15 => p_ATTRIBUTE15
136 , p_COST_GROUP_ID => p_COST_GROUP_ID
137 , p_LPN_CONTEXT => p_LPN_CONTEXT
138 , p_LPN_REUSABILITY => p_LPN_REUSABILITY
139 , p_OUTERMOST_LPN_ID => p_OUTERMOST_LPN_ID
140 --, p_outermost_lpn => p_outermost_lpn
141 , p_HOMOGENEOUS_CONTAINER => p_HOMOGENEOUS_CONTAINER
142 , p_SOURCE_TYPE_ID => p_SOURCE_TYPE_ID
143 , p_SOURCE_HEADER_ID => p_SOURCE_HEADER_ID
144 , p_SOURCE_LINE_ID => p_SOURCE_LINE_ID
145 , p_SOURCE_LINE_DETAIL_ID => p_SOURCE_LINE_DETAIL_ID
146 , p_SOURCE_NAME => p_SOURCE_NAME
147 , p_LPN_GROUP_ID => p_LPN_GROUP_ID
148 );
149 ELSE
150 IF (l_debug = 1) THEN
151 print_debug('INSERT_WLPNI - WLPNI not inserted as both LPN and LPNID are NULL',1);
152 END IF;
153 END IF;
154
155 END insert_wlpni;
156
157
158 PROCEDURE insert_mtli (
159 p_api_version IN NUMBER
160 , p_init_msg_lst IN VARCHAR2
161 , x_return_status OUT NOCOPY VARCHAR2
162 , x_msg_count OUT NOCOPY NUMBER
163 , x_msg_data OUT NOCOPY VARCHAR2
164 , p_transaction_interface_id IN OUT NOCOPY NUMBER
165 , p_lot_number IN VARCHAR2
166 , p_transaction_quantity IN NUMBER
167 , p_primary_quantity IN NUMBER
168 , p_organization_id IN NUMBER
169 , p_inventory_item_id IN NUMBER
170 , p_expiration_date IN DATE
171 , p_status_id IN NUMBER
172 , x_serial_transaction_temp_id OUT NOCOPY NUMBER
173 , p_product_transaction_id IN OUT NOCOPY NUMBER
174 , p_product_code IN VARCHAR2
175 , p_att_exist IN VARCHAR2
176 , p_update_mln IN VARCHAR2
177 , p_description IN VARCHAR2
178 , p_vendor_name IN VARCHAR2
179 , p_supplier_lot_number IN VARCHAR2
180 , p_origination_date IN DATE
181 , p_date_code IN VARCHAR2
182 , p_grade_code IN VARCHAR2
183 , p_change_date IN DATE
184 , p_maturity_date IN DATE
185 , p_retest_date IN DATE
186 , p_age IN NUMBER
187 , p_item_size IN NUMBER
188 , p_color IN VARCHAR2
189 , p_volume IN NUMBER
190 , p_volume_uom IN VARCHAR2
191 , p_place_of_origin IN VARCHAR2
192 , p_best_by_date IN DATE
193 , p_length IN NUMBER
194 , p_length_uom IN VARCHAR2
195 , p_recycled_content IN NUMBER
196 , p_thickness IN NUMBER
197 , p_thickness_uom IN VARCHAR2
198 , p_width IN NUMBER
199 , p_width_uom IN VARCHAR2
200 , p_curl_wrinkle_fold IN VARCHAR2
201 , p_vendor_id IN NUMBER
202 , p_territory_code IN VARCHAR2
203 , p_lot_attribute_category IN VARCHAR2
204 , p_c_attribute1 IN VARCHAR2
205 , p_c_attribute2 IN VARCHAR2
206 , p_c_attribute3 IN VARCHAR2
207 , p_c_attribute4 IN VARCHAR2
208 , p_c_attribute5 IN VARCHAR2
209 , p_c_attribute6 IN VARCHAR2
210 , p_c_attribute7 IN VARCHAR2
211 , p_c_attribute8 IN VARCHAR2
212 , p_c_attribute9 IN VARCHAR2
213 , p_c_attribute10 IN VARCHAR2
214 , p_c_attribute11 IN VARCHAR2
215 , p_c_attribute12 IN VARCHAR2
216 , p_c_attribute13 IN VARCHAR2
217 , p_c_attribute14 IN VARCHAR2
218 , p_c_attribute15 IN VARCHAR2
219 , p_c_attribute16 IN VARCHAR2
220 , p_c_attribute17 IN VARCHAR2
221 , p_c_attribute18 IN VARCHAR2
222 , p_c_attribute19 IN VARCHAR2
223 , p_c_attribute20 IN VARCHAR2
224 , p_d_attribute1 IN DATE
225 , p_d_attribute2 IN DATE
226 , p_d_attribute3 IN DATE
227 , p_d_attribute4 IN DATE
228 , p_d_attribute5 IN DATE
229 , p_d_attribute6 IN DATE
230 , p_d_attribute7 IN DATE
231 , p_d_attribute8 IN DATE
232 , p_d_attribute9 IN DATE
233 , p_d_attribute10 IN DATE
234 , p_n_attribute1 IN NUMBER
235 , p_n_attribute2 IN NUMBER
236 , p_n_attribute3 IN NUMBER
237 , p_n_attribute4 IN NUMBER
238 , p_n_attribute5 IN NUMBER
239 , p_n_attribute6 IN NUMBER
240 , p_n_attribute7 IN NUMBER
241 , p_n_attribute8 IN NUMBER
242 , p_n_attribute9 IN NUMBER
243 , p_n_attribute10 IN NUMBER
244 , p_attribute_category IN VARCHAR2
245 , p_attribute1 IN VARCHAR2
246 , p_attribute2 IN VARCHAR2
247 , p_attribute3 IN VARCHAR2
248 , p_attribute4 IN VARCHAR2
249 , p_attribute5 IN VARCHAR2
250 , p_attribute6 IN VARCHAR2
251 , p_attribute7 IN VARCHAR2
252 , p_attribute8 IN VARCHAR2
253 , p_attribute9 IN VARCHAR2
254 , p_attribute10 IN VARCHAR2
255 , p_attribute11 IN VARCHAR2
256 , p_attribute12 IN VARCHAR2
257 , p_attribute13 IN VARCHAR2
258 , p_attribute14 IN VARCHAR2
259 , p_attribute15 IN VARCHAR2
260 , p_from_org_id IN NUMBER
261 , p_secondary_quantity IN NUMBER --OPM Convergence
262 , p_origination_type IN NUMBER--OPM Convergence
263 , p_expiration_action_code IN VARCHAR2--OPM Convergence
264 , p_expiration_action_date IN DATE-- OPM Convergence
265 , p_hold_date IN DATE--OPM Convergence
266 , p_parent_lot_number IN VARCHAR2 --OPM Convergence
267 , p_reasond_id IN NUMBER --OPM convergence
268 ) IS
269 CURSOR c_mln_attributes( v_lot_number VARCHAR2
270 , v_inventory_item_id NUMBER
271 , v_organization_id NUMBER) IS
272 SELECT lot_number
273 , expiration_date
274 , description
275 , vendor_name
276 , supplier_lot_number
277 , grade_code
278 , origination_date
279 , date_code
280 , status_id
281 , change_date
282 , age
283 , retest_date
284 , maturity_date
285 , item_size
286 , color
287 , volume
288 , volume_uom
289 , place_of_origin
290 , best_by_date
291 , LENGTH
292 , length_uom
293 , recycled_content
294 , thickness
295 , thickness_uom
296 , width
297 , width_uom
298 , curl_wrinkle_fold
299 , vendor_id
300 , territory_code
301 , lot_attribute_category
302 , c_attribute1
303 , c_attribute2
304 , c_attribute3
305 , c_attribute4
306 , c_attribute5
307 , c_attribute6
308 , c_attribute7
309 , c_attribute8
310 , c_attribute9
311 , c_attribute10
312 , c_attribute11
313 , c_attribute12
314 , c_attribute13
315 , c_attribute14
316 , c_attribute15
317 , c_attribute16
318 , c_attribute17
319 , c_attribute18
320 , c_attribute19
321 , c_attribute20
322 , d_attribute1
323 , d_attribute2
324 , d_attribute3
325 , d_attribute4
326 , d_attribute5
327 , d_attribute6
328 , d_attribute7
329 , d_attribute8
330 , d_attribute9
331 , d_attribute10
332 , n_attribute1
333 , n_attribute2
334 , n_attribute3
335 , n_attribute4
336 , n_attribute5
337 , n_attribute6
338 , n_attribute7
339 , n_attribute8
340 , n_attribute9
341 , n_attribute10
342 , attribute_category
343 , attribute1
344 , attribute2
345 , attribute3
346 , attribute4
347 , attribute5
348 , attribute6
349 , attribute7
350 , attribute8
351 , attribute9
352 , attribute10
353 , attribute11
354 , attribute12
355 , attribute13
356 , attribute14
357 , attribute15
358 , origination_type --OPM Convergence
359 , availability_type --OPM Convergence
360 , expiration_action_code --OPM Convergence
361 , expiration_action_date -- OPM Convergence
362 , hold_date --OPM Convergence
363 FROM mtl_lot_numbers
364 WHERE lot_number = Ltrim(Rtrim(v_lot_number))
365 AND inventory_item_id = v_inventory_item_id
366 AND organization_id = v_organization_id;
367
368 --Local Variables
369 l_transaction_interface_id NUMBER; --transaction_interface_id generated
370 l_serial_transaction_temp_id NUMBER; --serial_transaction_temp_id generated
371 l_product_transaction_id NUMBER; --product_transaction_id generated
372 l_lot_count NUMBER; --MTLI count for given lot and interface_id
373 l_lot_exists NUMBER;
374
375 l_lot_number mtl_lot_numbers.lot_number%type := p_lot_number;
376 l_expiration_date mtl_lot_numbers.expiration_date%type := p_expiration_date;
377 l_description mtl_lot_numbers.description%type := p_description;
378 l_vendor_name mtl_lot_numbers.vendor_name%type := p_vendor_name;
379 l_supplier_lot_number mtl_lot_numbers.supplier_lot_number%type := p_supplier_lot_number;
380 l_grade_code mtl_lot_numbers.grade_code%type := p_grade_code;
381 l_origination_date mtl_lot_numbers.origination_date%type := p_origination_date;
382 l_date_code mtl_lot_numbers.date_code%type := p_date_code;
383 l_status_id mtl_lot_numbers.status_id%type := p_status_id;
384 l_change_date mtl_lot_numbers.change_date%type := p_change_date;
385 l_age mtl_lot_numbers.age%type := p_age;
386 l_retest_date mtl_lot_numbers.retest_date%type := p_retest_date;
387 l_maturity_date mtl_lot_numbers.maturity_date%type := p_maturity_date;
388 l_item_size mtl_lot_numbers.item_size%type := p_item_size;
389 l_color mtl_lot_numbers.color%type := p_color;
390 l_volume mtl_lot_numbers.volume%type := p_volume;
391 l_volume_uom mtl_lot_numbers.volume_uom%type := p_volume_uom;
392 l_place_of_origin mtl_lot_numbers.place_of_origin%type := p_place_of_origin;
393 l_best_by_date mtl_lot_numbers.best_by_date%type := p_best_by_date;
394 l_length mtl_lot_numbers.length%type := p_length;
395 l_length_uom mtl_lot_numbers.length_uom%type := p_length_uom;
396 l_recycled_content mtl_lot_numbers.recycled_content%type := p_recycled_content;
397 l_thickness mtl_lot_numbers.thickness%type := p_thickness;
398 l_thickness_uom mtl_lot_numbers.thickness_uom%type := p_thickness_uom;
399 l_width mtl_lot_numbers.width%type := p_width;
400 l_width_uom mtl_lot_numbers.width_uom%type := p_width_uom;
401 l_curl_wrinkle_fold mtl_lot_numbers.curl_wrinkle_fold%type := p_curl_wrinkle_fold;
402 l_vendor_id mtl_lot_numbers.vendor_id%type := p_vendor_id;
403 l_territory_code mtl_lot_numbers.territory_code%type := p_territory_code;
404 l_lot_attribute_category mtl_lot_numbers.lot_attribute_category%TYPE := p_lot_attribute_category;
405 l_c_attribute1 mtl_lot_numbers.c_attribute1%type := p_c_attribute1;
406 l_c_attribute2 mtl_lot_numbers.c_attribute2%type := p_c_attribute2;
407 l_c_attribute3 mtl_lot_numbers.c_attribute3%type := p_c_attribute3;
408 l_c_attribute4 mtl_lot_numbers.c_attribute4%type := p_c_attribute4;
409 l_c_attribute5 mtl_lot_numbers.c_attribute5%type := p_c_attribute5;
410 l_c_attribute6 mtl_lot_numbers.c_attribute6%type := p_c_attribute6;
411 l_c_attribute7 mtl_lot_numbers.c_attribute7%type := p_c_attribute7;
412 l_c_attribute8 mtl_lot_numbers.c_attribute8%type := p_c_attribute8;
413 l_c_attribute9 mtl_lot_numbers.c_attribute9%type := p_c_attribute9;
414 l_c_attribute10 mtl_lot_numbers.c_attribute10%type := p_c_attribute10;
415 l_c_attribute11 mtl_lot_numbers.c_attribute11%type := p_c_attribute11;
416 l_c_attribute12 mtl_lot_numbers.c_attribute12%type := p_c_attribute12;
417 l_c_attribute13 mtl_lot_numbers.c_attribute13%type := p_c_attribute13;
418 l_c_attribute14 mtl_lot_numbers.c_attribute14%type := p_c_attribute14;
419 l_c_attribute15 mtl_lot_numbers.c_attribute15%type := p_c_attribute15;
420 l_c_attribute16 mtl_lot_numbers.c_attribute16%type := p_c_attribute16;
421 l_c_attribute17 mtl_lot_numbers.c_attribute17%type := p_c_attribute17;
422 l_c_attribute18 mtl_lot_numbers.c_attribute18%type := p_c_attribute18;
423 l_c_attribute19 mtl_lot_numbers.c_attribute19%type := p_c_attribute19;
424 l_c_attribute20 mtl_lot_numbers.c_attribute20%type := p_c_attribute20;
425 l_d_attribute1 mtl_lot_numbers.d_attribute1%type := p_d_attribute1;
426 l_d_attribute2 mtl_lot_numbers.d_attribute2%type := p_d_attribute2;
427 l_d_attribute3 mtl_lot_numbers.d_attribute3%type := p_d_attribute3;
428 l_d_attribute4 mtl_lot_numbers.d_attribute4%type := p_d_attribute4;
429 l_d_attribute5 mtl_lot_numbers.d_attribute5%type := p_d_attribute5;
430 l_d_attribute6 mtl_lot_numbers.d_attribute6%type := p_d_attribute6;
431 l_d_attribute7 mtl_lot_numbers.d_attribute7%type := p_d_attribute7;
432 l_d_attribute8 mtl_lot_numbers.d_attribute8%type := p_d_attribute8;
433 l_d_attribute9 mtl_lot_numbers.d_attribute9%type := p_d_attribute9;
434 l_d_attribute10 mtl_lot_numbers.d_attribute10%type := p_d_attribute10;
435 l_n_attribute1 mtl_lot_numbers.n_attribute1%type := p_n_attribute1;
436 l_n_attribute2 mtl_lot_numbers.n_attribute2%type := p_n_attribute2;
437 l_n_attribute3 mtl_lot_numbers.n_attribute3%type := p_n_attribute3;
438 l_n_attribute4 mtl_lot_numbers.n_attribute4%type := p_n_attribute4;
439 l_n_attribute5 mtl_lot_numbers.n_attribute5%type := p_n_attribute5;
440 l_n_attribute6 mtl_lot_numbers.n_attribute6%type := p_n_attribute6;
441 l_n_attribute7 mtl_lot_numbers.n_attribute7%type := p_n_attribute7;
442 l_n_attribute8 mtl_lot_numbers.n_attribute8%type := p_n_attribute8;
443 l_n_attribute9 mtl_lot_numbers.n_attribute9%type := p_n_attribute9;
444 l_n_attribute10 mtl_lot_numbers.n_attribute10%type := p_n_attribute10;
445 l_attribute_category mtl_lot_numbers.attribute_category%type := p_attribute_category;
446 l_attribute1 mtl_lot_numbers.attribute1%type := p_attribute1;
447 l_attribute2 mtl_lot_numbers.attribute2%type := p_attribute2;
448 l_attribute3 mtl_lot_numbers.attribute3%type := p_attribute3;
449 l_attribute4 mtl_lot_numbers.attribute4%type := p_attribute4;
450 l_attribute5 mtl_lot_numbers.attribute5%type := p_attribute5;
451 l_attribute6 mtl_lot_numbers.attribute6%type := p_attribute6;
452 l_attribute7 mtl_lot_numbers.attribute7%type := p_attribute7;
453 l_attribute8 mtl_lot_numbers.attribute8%type := p_attribute8;
454 l_attribute9 mtl_lot_numbers.attribute9%type := p_attribute9;
455 l_attribute10 mtl_lot_numbers.attribute10%type := p_attribute10;
456 l_attribute11 mtl_lot_numbers.attribute11%type := p_attribute11;
457 l_attribute12 mtl_lot_numbers.attribute12%type := p_attribute12;
458 l_attribute13 mtl_lot_numbers.attribute13%type := p_attribute13;
459 l_attribute14 mtl_lot_numbers.attribute14%type := p_attribute14;
460 l_attribute15 mtl_lot_numbers.attribute15%type := p_attribute15;
461 l_source_code mtl_transaction_lots_interface.source_code%TYPE;
462 l_source_line_id mtl_transaction_lots_interface.source_line_id%TYPE;
463 l_serial_control_code mtl_system_items.serial_number_control_code%TYPE;
464 l_api_version CONSTANT NUMBER := 1.0;
465 l_api_name CONSTANT VARCHAR2(30) := 'insert_mtli';
466 l_progress NUMBER; --Progress Indicator
467 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'), 0);
468
469 l_att_org_id NUMBER;
470
471 --nsinghi bug#5209065 START. Default the variables to input parameters.
472 l_origination_type mtl_lot_numbers.origination_type%TYPE := p_origination_type; --OPM Convergence
473 l_availability_type mtl_lot_numbers.availability_type%TYPE; --OPM Convergence
474 l_expiration_action_code mtl_lot_numbers.expiration_action_code%TYPE := p_expiration_action_code; --OPM Convergence
475 l_expiration_action_date mtl_lot_numbers.expiration_action_date%TYPE := p_expiration_action_date; -- OPM Convergence
476 l_hold_date mtl_lot_numbers.hold_date%TYPE := p_hold_date; --OPM Convergence
477 l_parent_lot_number mtl_lot_numbers.parent_lot_number%TYPE := p_parent_lot_number; --OPM Convergence
478 l_secondary_quantity NUMBER := p_secondary_quantity;
479 --nsinghi bug#5209065 END.
480 /* Bug 13727314 */
481 l_lot_status_id mtl_lot_numbers.status_id%type;
482 l_default_status_id number:= NULL;
483 l_allow_status_entry VARCHAR2(3) := NVL(fnd_profile.VALUE('INV_ALLOW_ONHAND_STATUS_ENTRY'), 'N');
484
485 BEGIN
486
487 -- Standard call to check for call compatibility.
488 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version,
489 l_api_name, 'inv_rcv_integration_apis') THEN
490 print_debug('FND_API not compatible inv_rcv_integration_apis.insert_mtli', 4);
491 RAISE fnd_api.g_exc_unexpected_error;
492 END IF;
493
494 -- Initialize message list if p_init_msg_list is set to TRUE.
495 IF fnd_api.to_boolean(p_init_msg_lst) THEN
496 fnd_msg_pub.initialize;
497 END IF;
498
499 --Initialize the return status
500 x_return_status := FND_API.G_RET_STS_SUCCESS;
501
502 --If the lot number and transaction_interface_id combination already exists
503 --then add the specified transaction_quantity and primary_quantity to the
504 --current lot interface record.
505 IF p_transaction_interface_id IS NOT NULL THEN
506 BEGIN
507 SELECT 1
508 , serial_transaction_temp_id
509 INTO l_lot_count
510 , x_serial_transaction_temp_id
511 FROM mtl_transaction_lots_interface MTLI
512 WHERE transaction_interface_id = p_transaction_interface_id
513 AND product_transaction_id = p_product_transaction_id
514 AND Ltrim(Rtrim(lot_number)) = Ltrim(Rtrim(p_lot_number))
515 AND ROWNUM = 1
516 AND EXISTS (
517 SELECT 1
518 FROM rcv_transactions_interface RTI
519 WHERE RTI.INTERFACE_TRANSACTION_ID = MTLI.product_transaction_id
520 AND RTI.item_id = p_inventory_item_id
521 AND RTI.to_organization_id = p_organization_id
522 );
523 EXCEPTION
524 WHEN NO_DATA_FOUND THEN
525 l_lot_count := 0;
526 WHEN OTHERS THEN
527 l_lot_count := 0;
528 IF (l_debug = 1) THEN
529 print_debug('Exception trying to find lot existance', 4);
530 END IF;
531 END;
532
533 IF (l_debug = 1) THEN
534 print_debug('Count of lots records for the intf id: ' || l_lot_count, 4);
535 END IF;
536
537 IF l_lot_count = 1 THEN
538 UPDATE mtl_transaction_lots_interface
539 SET transaction_quantity = transaction_quantity + p_transaction_quantity
540 , primary_quantity = primary_quantity + p_primary_quantity
541 , secondary_transaction_quantity = secondary_transaction_quantity + p_secondary_quantity
542 WHERE transaction_interface_id = p_transaction_interface_id
543 AND Ltrim(Rtrim(lot_number)) = Ltrim(Rtrim(p_lot_number));
544
545 IF (l_debug = 1) THEN
546 print_debug('Updated ' || SQL%ROWCOUNT || ' lot record(s)', 4);
547 END IF;
548
549 --Since the attributes have already been updated, just do nothing
550 RETURN;
551 END IF; --END IF lot_count = 1
552 END IF; --END IF p_transaction_interface_id IS NOT NULL
553
554 --Generate transaction_interface_id if the parameter is NULL
555 IF (p_transaction_interface_id IS NULL) THEN
556 SELECT mtl_material_transactions_s.NEXTVAL
557 INTO l_transaction_interface_id
558 FROM sys.dual;
559 ELSE
560 l_transaction_interface_id := p_transaction_interface_id;
561 END IF;
562
563 l_serial_control_code := inv_rcv_cache.get_sn_ctrl_code(p_organization_id,p_inventory_item_id);
564
565 IF l_serial_control_code not in (1,6) THEN -- Bug 9008016
566 SELECT mtl_material_transactions_s.NEXTVAL
567 INTO l_serial_transaction_temp_id
568 FROM sys.DUAL;
569 ELSE
570 l_serial_transaction_temp_id := NULL;
571 END IF;
572
573 --Generate production_transaction_id if the parameter is NULL
574 IF (p_product_transaction_id IS NULL) THEN
575 SELECT rcv_transactions_interface_s.NEXTVAL
576 INTO l_product_transaction_id
577 FROM sys.dual;
578 ELSE
579 l_product_transaction_id := p_product_transaction_id;
580 END IF;
581
582 /* Logic to insert lot attributes.
583 * Check the value of the parameter p_att_exist
584 * If this value is "N" then use the input parameters to insert the attributes
585 * If this value is "Y", then open the cursor passing the lot, item and org
586 * Use the values fetched from the cursor to insert the attributes
587 */
588 IF (NVL(p_att_exist, 'Y') = 'Y') THEN
589 IF p_from_org_id IS NOT NULL THEN
590 l_att_org_id := p_from_org_id;
591 ELSE
592 l_att_org_id := p_organization_id;
593 END IF;
594
595 BEGIN
596 OPEN c_mln_attributes(p_lot_number, p_inventory_item_id, l_att_org_id);
597 FETCH c_mln_attributes INTO
598 l_lot_number
599 , l_expiration_date
600 , l_description
601 , l_vendor_name
602 , l_supplier_lot_number
603 , l_grade_code
604 , l_origination_date
605 , l_date_code
606 , l_status_id
607 , l_change_date
608 , l_age
609 , l_retest_date
610 , l_maturity_date
611 , l_item_size
612 , l_color
613 , l_volume
614 , l_volume_uom
615 , l_place_of_origin
616 , l_best_by_date
617 , l_length
618 , l_length_uom
619 , l_recycled_content
620 , l_thickness
621 , l_thickness_uom
622 , l_width
623 , l_width_uom
624 , l_curl_wrinkle_fold
625 , l_vendor_id
626 , l_territory_code
627 , l_lot_attribute_category
628 , l_c_attribute1
629 , l_c_attribute2
630 , l_c_attribute3
631 , l_c_attribute4
632 , l_c_attribute5
633 , l_c_attribute6
634 , l_c_attribute7
635 , l_c_attribute8
636 , l_c_attribute9
637 , l_c_attribute10
638 , l_c_attribute11
639 , l_c_attribute12
640 , l_c_attribute13
641 , l_c_attribute14
642 , l_c_attribute15
643 , l_c_attribute16
644 , l_c_attribute17
645 , l_c_attribute18
646 , l_c_attribute19
647 , l_c_attribute20
648 , l_d_attribute1
649 , l_d_attribute2
650 , l_d_attribute3
651 , l_d_attribute4
652 , l_d_attribute5
653 , l_d_attribute6
654 , l_d_attribute7
655 , l_d_attribute8
656 , l_d_attribute9
657 , l_d_attribute10
658 , l_n_attribute1
659 , l_n_attribute2
660 , l_n_attribute3
661 , l_n_attribute4
662 , l_n_attribute5
663 , l_n_attribute6
664 , l_n_attribute7
665 , l_n_attribute8
666 , l_n_attribute9
667 , l_n_attribute10
668 , l_attribute_category
669 , l_attribute1
670 , l_attribute2
671 , l_attribute3
672 , l_attribute4
673 , l_attribute5
674 , l_attribute6
675 , l_attribute7
676 , l_attribute8
677 , l_attribute9
678 , l_attribute10
679 , l_attribute11
680 , l_attribute12
681 , l_attribute13
682 , l_attribute14
683 , l_attribute15
684 , l_origination_type
685 , l_availability_type
686 , l_expiration_action_code
687 , l_expiration_action_date
688 , l_hold_date;
689 IF c_mln_attributes%ISOPEN THEN
690 CLOSE c_mln_attributes;
691 END IF;
692 EXCEPTION
693 WHEN OTHERS THEN
694 IF c_mln_attributes%ISOPEN THEN
695 CLOSE c_mln_attributes;
696 END IF;
697 END;
698 END IF; --END IF p_att_exist = 'Y'
699
700 /* Bug 13727314 */
701
702 if inv_cache.set_org_rec(p_organization_id) then
703 l_default_status_id := inv_cache.org_rec.default_status_id;
704 end if;
705
706 IF (l_default_status_id is not null AND l_allow_status_entry = 'Y' AND l_serial_control_code not in (2,5)) THEN
707 l_lot_status_id := p_status_id;
708 else
709 l_lot_status_id := l_status_id;
710 END IF;
711
712 INSERT INTO MTL_TRANSACTION_LOTS_INTERFACE (
713 transaction_interface_id
714 , source_code
715 , source_line_id
716 , last_update_date
717 , last_updated_by
718 , creation_date
719 , created_by
720 , last_update_login
721 , lot_number
722 , lot_expiration_date
723 , transaction_quantity
724 , primary_quantity
725 , serial_transaction_temp_id
726 , description
727 , vendor_name
728 , supplier_lot_number
729 , origination_date
730 , date_code
731 , grade_code
732 , change_date
733 , maturity_date
734 , status_id
735 , retest_date
736 , age
737 , item_size
738 , color
739 , volume
740 , volume_uom
741 , place_of_origin
742 , best_by_date
743 , length
744 , length_uom
745 , recycled_content
746 , thickness
747 , thickness_uom
748 , width
749 , width_uom
750 , curl_wrinkle_fold
751 , lot_attribute_category
752 , c_attribute1
753 , c_attribute2
754 , c_attribute3
755 , c_attribute4
756 , c_attribute5
757 , c_attribute6
758 , c_attribute7
759 , c_attribute8
760 , c_attribute9
761 , c_attribute10
762 , c_attribute11
763 , c_attribute12
764 , c_attribute13
765 , c_attribute14
766 , c_attribute15
767 , c_attribute16
768 , c_attribute17
769 , c_attribute18
770 , c_attribute19
771 , c_attribute20
772 , d_attribute1
773 , d_attribute2
774 , d_attribute3
775 , d_attribute4
776 , d_attribute5
777 , d_attribute6
778 , d_attribute7
779 , d_attribute8
780 , d_attribute9
781 , d_attribute10
782 , n_attribute1
783 , n_attribute2
784 , n_attribute3
785 , n_attribute4
786 , n_attribute5
787 , n_attribute6
788 , n_attribute7
789 , n_attribute8
790 , n_attribute9
791 , n_attribute10
792 , vendor_id
793 , territory_code
794 , product_transaction_id
795 , product_code
796 /* , attribute_category
797 , attribute1
798 , attribute2
799 , attribute3
800 , attribute4
801 , attribute5
802 , attribute6
803 , attribute7
804 , attribute8
805 , attribute9
806 , attribute10
807 , attribute11
808 , attribute12
809 , attribute13
810 , attribute14
811 , attribute15 */
812 , origination_type --OPM Convergence
813 , expiration_action_code --OPM Convergence
814 , expiration_action_date --OPM Convergence
815 , hold_date --OPM Convergence
816 , secondary_transaction_quantity --OPM Convergence
817 , parent_lot_number --OPM Convergence
818 )
819 VALUES (
820 l_transaction_interface_id
821 , l_source_code
822 , l_source_line_id
823 , SYSDATE
824 , FND_GLOBAL.USER_ID
825 , SYSDATE
826 , FND_GLOBAL.USER_ID
827 , FND_GLOBAL.LOGIN_ID
828 , Ltrim(Rtrim(p_lot_number))
829 , l_expiration_date
830 , p_transaction_quantity
831 , p_primary_quantity
832 , l_serial_transaction_temp_id
833 , l_description
834 , l_vendor_name
835 , l_supplier_lot_number
836 , l_origination_date
837 , l_date_code
838 , l_grade_code
839 , l_change_date
840 , l_maturity_date
841 , l_lot_status_id --Bug13727314
842 , l_retest_date
843 , l_age
844 , l_item_size
845 , l_color
846 , l_volume
847 , l_volume_uom
848 , l_place_of_origin
849 , l_best_by_date
850 , l_length
851 , l_length_uom
852 , l_recycled_content
853 , l_thickness
854 , l_thickness_uom
855 , l_width
856 , l_width_uom
857 , l_curl_wrinkle_fold
858 , l_lot_attribute_category
859 , l_c_attribute1
860 , l_c_attribute2
861 , l_c_attribute3
862 , l_c_attribute4
863 , l_c_attribute5
864 , l_c_attribute6
865 , l_c_attribute7
866 , l_c_attribute8
867 , l_c_attribute9
868 , l_c_attribute10
869 , l_c_attribute11
870 , l_c_attribute12
871 , l_c_attribute13
872 , l_c_attribute14
873 , l_c_attribute15
874 , l_c_attribute16
875 , l_c_attribute17
876 , l_c_attribute18
877 , l_c_attribute19
878 , l_c_attribute20
879 , l_d_attribute1
880 , l_d_attribute2
881 , l_d_attribute3
882 , l_d_attribute4
883 , l_d_attribute5
884 , l_d_attribute6
885 , l_d_attribute7
886 , l_d_attribute8
887 , l_d_attribute9
888 , l_d_attribute10
889 , l_n_attribute1
890 , l_n_attribute2
891 , l_n_attribute3
892 , l_n_attribute4
893 , l_n_attribute5
894 , l_n_attribute6
895 , l_n_attribute7
896 , l_n_attribute8
897 , l_n_attribute9
898 , l_n_attribute10
899 , l_vendor_id
900 , l_territory_code
901 , l_product_transaction_id
902 , p_product_code
903 /* , l_attribute_category
904 , l_attribute1
905 , l_attribute2
906 , l_attribute3
907 , l_attribute4
908 , l_attribute5
909 , l_attribute6
910 , l_attribute7
911 , l_attribute8
912 , l_attribute9
913 , l_attribute10
914 , l_attribute11
915 , l_attribute12
916 , l_attribute13
917 , l_attribute14
918 , l_attribute15 */
919 , l_origination_type --OPM Convergence
920 , l_expiration_action_code --OPM Convergence
921 , l_expiration_action_date --OPM Convergence
922 , l_hold_date --OPM Convergence
923 , p_secondary_quantity --OPM Convergence
924 , l_parent_lot_number --OPM Convergence
925 );
926
927 --If the flag p_update_mln is set to 'Y' then update MTL_LOT_NUMBERS
928 --with the attributes from the parameters
929 IF (NVL(p_update_mln, 'N') = 'Y') THEN
930 BEGIN
931 SELECT count(1)
932 INTO l_lot_exists
933 FROM mtl_lot_numbers
934 WHERE lot_number = Ltrim(Rtrim(p_lot_number))
935 AND inventory_item_id = p_inventory_item_id
936 AND organization_id = p_organization_id;
937
938 IF l_lot_exists > 0 THEN
939 UPDATE mtl_lot_numbers
940 SET description = l_description
941 , vendor_name = l_vendor_name
942 , supplier_lot_number = l_supplier_lot_number
943 , origination_date = l_origination_date
944 , date_code = l_date_code
945 , grade_code = l_grade_code
946 , change_date = l_change_date
947 , maturity_date = l_maturity_date
948 , retest_date = l_retest_date
949 , age = l_age
950 , item_size = l_item_size
951 , color = l_color
952 , volume = l_volume
953 , volume_uom = l_volume_uom
954 , place_of_origin = l_place_of_origin
955 , best_by_date = l_best_by_date
956 , length = l_length
957 , length_uom = l_length_uom
958 , recycled_content = l_recycled_content
959 , thickness = l_thickness
960 , thickness_uom = l_thickness_uom
961 , width = l_width
962 , width_uom = l_width_uom
963 , curl_wrinkle_fold = l_curl_wrinkle_fold
964 , vendor_id = l_vendor_id
965 , territory_code = l_territory_code
966 , lot_attribute_category = l_lot_attribute_category
967 , c_attribute1 = l_c_attribute1
968 , c_attribute2 = l_c_attribute2
969 , c_attribute3 = l_c_attribute3
970 , c_attribute4 = l_c_attribute4
971 , c_attribute5 = l_c_attribute5
972 , c_attribute6 = l_c_attribute6
973 , c_attribute7 = l_c_attribute7
974 , c_attribute8 = l_c_attribute8
975 , c_attribute9 = l_c_attribute9
976 , c_attribute10 = l_c_attribute10
977 , c_attribute11 = l_c_attribute11
978 , c_attribute12 = l_c_attribute12
979 , c_attribute13 = l_c_attribute13
980 , c_attribute14 = l_c_attribute14
981 , c_attribute15 = l_c_attribute15
982 , c_attribute16 = l_c_attribute16
983 , c_attribute17 = l_c_attribute17
984 , c_attribute18 = l_c_attribute18
985 , c_attribute19 = l_c_attribute19
986 , c_attribute20 = l_c_attribute20
987 , d_attribute1 = l_d_attribute1
988 , d_attribute2 = l_d_attribute2
989 , d_attribute3 = l_d_attribute3
990 , d_attribute4 = l_d_attribute4
991 , d_attribute5 = l_d_attribute5
992 , d_attribute6 = l_d_attribute6
993 , d_attribute7 = l_d_attribute7
994 , d_attribute8 = l_d_attribute8
995 , d_attribute9 = l_d_attribute9
996 , d_attribute10 = l_d_attribute10
997 , n_attribute1 = l_n_attribute1
998 , n_attribute2 = l_n_attribute2
999 , n_attribute3 = l_n_attribute3
1000 , n_attribute4 = l_n_attribute4
1001 , n_attribute5 = l_n_attribute5
1002 , n_attribute6 = l_n_attribute6
1003 , n_attribute7 = l_n_attribute7
1004 , n_attribute8 = l_n_attribute8
1005 , n_attribute9 = l_n_attribute9
1006 , n_attribute10 = l_n_attribute10
1007 , attribute_category = l_attribute_category
1008 , attribute1 = l_attribute1
1009 , attribute2 = l_attribute2
1010 , attribute3 = l_attribute3
1011 , attribute4 = l_attribute4
1012 , attribute5 = l_attribute5
1013 , attribute6 = l_attribute6
1014 , attribute7 = l_attribute7
1015 , attribute8 = l_attribute8
1016 , attribute9 = l_attribute9
1017 , attribute10 = l_attribute10
1018 , attribute11 = l_attribute11
1019 , attribute12 = l_attribute12
1020 , attribute13 = l_attribute13
1021 , attribute14 = l_attribute14
1022 , attribute15 = l_attribute15
1023 , origination_type = l_origination_type --OPM Convergence
1024 , availability_type = l_availability_type --OPM Convergence
1025 , expiration_action_code = l_expiration_action_code--OPM Convergence
1026 , expiration_action_date = l_expiration_action_date --OPM Convergence
1027 , hold_date = l_hold_date --OPM Convergence
1028 , parent_lot_number = l_parent_lot_number --OPM Convergence
1029 WHERE lot_number = Ltrim(Rtrim(p_lot_number))
1030 AND inventory_item_id = p_inventory_item_id
1031 AND organization_id = p_organization_id;
1032 END IF; --END If lot exists
1033 EXCEPTION
1034 WHEN OTHERS THEN
1035 NULL;
1036 END;
1037 END IF; --END IF p_update_mln = 'Y'
1038
1039 --Reassign the generated values
1040 p_transaction_interface_id := l_transaction_interface_id;
1041 x_serial_transaction_temp_id := l_serial_transaction_temp_id;
1042 p_product_transaction_id := l_product_transaction_id;
1043
1044 IF (l_debug = 1) THEN
1045 print_debug('p_transaction_interface_id returned: ' || p_transaction_interface_id, 4);
1046 print_debug('x_serial_transaction_temp_id returned: ' || x_serial_transaction_temp_id, 4);
1047 print_debug('p_product_transaction_id returned: ' || p_product_transaction_id, 4);
1048 END IF;
1049
1050 EXCEPTION
1051 WHEN FND_API.G_EXC_ERROR THEN
1052 IF c_mln_attributes%ISOPEN THEN
1053 CLOSE c_mln_attributes;
1054 END IF;
1055 x_return_status := FND_API.G_RET_STS_ERROR;
1056 fnd_msg_pub.count_and_get (
1057 p_encoded => FND_API.G_FALSE
1058 , p_count => x_msg_count
1059 , p_data => x_msg_data );
1060 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1061 IF c_mln_attributes%ISOPEN THEN
1062 CLOSE c_mln_attributes;
1063 END IF;
1064 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1065 fnd_msg_pub.count_and_get (
1066 p_encoded => FND_API.G_FALSE
1067 , p_count => x_msg_count
1068 , p_data => x_msg_data );
1069 WHEN OTHERS THEN
1070 IF c_mln_attributes%ISOPEN THEN
1071 CLOSE c_mln_attributes;
1072 END IF;
1073 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1074 fnd_msg_pub.count_and_get (
1075 p_encoded => FND_API.G_FALSE
1076 , p_count => x_msg_count
1077 , p_data => x_msg_data );
1078 IF SQLCODE IS NOT NULL THEN
1079 inv_mobile_helper_functions.sql_error (
1080 'INV_RCV_INTEGRATION_APIS.INSERT_MTLI',
1081 l_progress,
1082 SQLCODE);
1083 END IF;
1084 END insert_mtli;
1085
1086 /*----------------------------------------------------------------------------
1087 * PROCEDURE: insert_msni
1088 * Description:
1089 * This procedure inserts a record into MTL_SERIAL_NUMBERS_INTERFACE
1090 * Generate transaction_interface_id if the parameter is NULL
1091 * Generate product_transaction_id if the parameter is NULL
1092 * The insert logic is based on the parameter p_att_exist.
1093 * If p_att_exist is "N" Then (attributes are not available in table)
1094 * Read the input parameters (including attributes) into a PL/SQL table
1095 * Insert one record into MSNI with the from and to serial numbers passed
1096 * Else
1097 * Loop through each serial number between the from and to serial number
1098 * Fetch the attributes into one row of the PL/SQL table and
1099 * For each row in the PL/SQL table, insert one MSNI record
1100 * End If
1101 *
1102 * @param p_api_version - Version of the API
1103 * @param p_init_msg_lst - Flag to initialize message list
1104 * @param x_return_status
1105 * Return status indicating Success (S), Error (E), Unexpected Error (U)
1106 * @param x_msg_count
1107 * Number of messages in message list
1108 * @param x_msg_data
1109 * Stacked messages text
1110 * @param p_transaction_interface_id - MTLI.Interface Transaction ID
1111 * @param p_fm_serial_number - From Serial Number
1112 * @param p_to_serial_number - To Serial Number
1113 * @param p_organization_id - Organization ID
1114 * @param p_inventory_item_id - Inventory Item ID
1115 * @param p_status_id - Material Status for the lot
1116 * @param p_product_transaction_id - Product Transaction Id. This parameter
1117 * is stamped with the transaction identifier with
1118 * @param p_product_code - Code of the product creating this record
1119 * @param p_att_exist - Flag to indicate if attributes exist
1120 * @param p_update_msn - Flag to update MSN with attributes
1121 * @param named attributes - Named attributes
1122 * @param C Attributes - Character atributes (1 - 20)
1123 * @param D Attributes - Date atributes (1 - 10)
1124 * @param N Attributes - Number atributes (1 - 10)
1125 * @param p_attribute_cateogry - Attribute Category
1126 * @param Attribute1-15 - Serial Attributes
1127 *
1128 * @ return: NONE
1129 *---------------------------------------------------------------------------*/
1130
1131 PROCEDURE insert_msni(
1132 p_api_version IN NUMBER
1133 , p_init_msg_lst IN VARCHAR2
1134 , x_return_status OUT NOCOPY VARCHAR2
1135 , x_msg_count OUT NOCOPY NUMBER
1136 , x_msg_data OUT NOCOPY VARCHAR2
1137 , p_transaction_interface_id IN OUT NOCOPY NUMBER
1138 , p_fm_serial_number IN VARCHAR2
1139 , p_to_serial_number IN VARCHAR2
1140 , p_organization_id IN NUMBER
1141 , p_inventory_item_id IN NUMBER
1142 , p_status_id IN NUMBER
1143 , p_product_transaction_id IN OUT NOCOPY NUMBER
1144 , p_product_code IN VARCHAR2
1145 , p_att_exist IN VARCHAR2
1146 , p_update_msn IN VARCHAR2
1147 , p_vendor_serial_number IN VARCHAR2
1148 , p_vendor_lot_number IN VARCHAR2
1149 , p_parent_serial_number IN VARCHAR2
1150 , p_origination_date IN DATE
1151 , p_territory_code IN VARCHAR2
1152 , p_time_since_new IN NUMBER
1153 , p_cycles_since_new IN NUMBER
1154 , p_time_since_overhaul IN NUMBER
1155 , p_cycles_since_overhaul IN NUMBER
1156 , p_time_since_repair IN NUMBER
1157 , p_cycles_since_repair IN NUMBER
1158 , p_time_since_visit IN NUMBER
1159 , p_cycles_since_visit IN NUMBER
1160 , p_time_since_mark IN NUMBER
1161 , p_cycles_since_mark IN NUMBER
1162 , p_number_of_repairs IN NUMBER
1163 , p_serial_attribute_category IN VARCHAR2
1164 , p_c_attribute1 IN VARCHAR2
1165 , p_c_attribute2 IN VARCHAR2
1166 , p_c_attribute3 IN VARCHAR2
1167 , p_c_attribute4 IN VARCHAR2
1168 , p_c_attribute5 IN VARCHAR2
1169 , p_c_attribute6 IN VARCHAR2
1170 , p_c_attribute7 IN VARCHAR2
1171 , p_c_attribute8 IN VARCHAR2
1172 , p_c_attribute9 IN VARCHAR2
1173 , p_c_attribute10 IN VARCHAR2
1174 , p_c_attribute11 IN VARCHAR2
1175 , p_c_attribute12 IN VARCHAR2
1176 , p_c_attribute13 IN VARCHAR2
1177 , p_c_attribute14 IN VARCHAR2
1178 , p_c_attribute15 IN VARCHAR2
1179 , p_c_attribute16 IN VARCHAR2
1180 , p_c_attribute17 IN VARCHAR2
1181 , p_c_attribute18 IN VARCHAR2
1182 , p_c_attribute19 IN VARCHAR2
1183 , p_c_attribute20 IN VARCHAR2
1184 , p_d_attribute1 IN DATE
1185 , p_d_attribute2 IN DATE
1186 , p_d_attribute3 IN DATE
1187 , p_d_attribute4 IN DATE
1188 , p_d_attribute5 IN DATE
1189 , p_d_attribute6 IN DATE
1190 , p_d_attribute7 IN DATE
1191 , p_d_attribute8 IN DATE
1192 , p_d_attribute9 IN DATE
1193 , p_d_attribute10 IN DATE
1194 , p_n_attribute1 IN NUMBER
1195 , p_n_attribute2 IN NUMBER
1196 , p_n_attribute3 IN NUMBER
1197 , p_n_attribute4 IN NUMBER
1198 , p_n_attribute5 IN NUMBER
1199 , p_n_attribute6 IN NUMBER
1200 , p_n_attribute7 IN NUMBER
1201 , p_n_attribute8 IN NUMBER
1202 , p_n_attribute9 IN NUMBER
1203 , p_n_attribute10 IN NUMBER
1204 , p_attribute_category IN VARCHAR2
1205 , p_attribute1 IN VARCHAR2
1206 , p_attribute2 IN VARCHAR2
1207 , p_attribute3 IN VARCHAR2
1208 , p_attribute4 IN VARCHAR2
1209 , p_attribute5 IN VARCHAR2
1210 , p_attribute6 IN VARCHAR2
1211 , p_attribute7 IN VARCHAR2
1212 , p_attribute8 IN VARCHAR2
1213 , p_attribute9 IN VARCHAR2
1214 , p_attribute10 IN VARCHAR2
1215 , p_attribute11 IN VARCHAR2
1216 , p_attribute12 IN VARCHAR2
1217 , p_attribute13 IN VARCHAR2
1218 , p_attribute14 IN VARCHAR2
1219 , p_attribute15 IN VARCHAR2
1220 ) IS
1221 CURSOR c_msn_attributes ( v_serial_number VARCHAR2,
1222 v_inventory_item_id NUMBER) IS
1223 SELECT serial_number fm_serial_number
1224 , serial_number to_serial_number
1225 , to_number(NULL) transaction_interface_id
1226 , status_id
1227 , to_number(NULL) product_transaction_id
1228 , to_char(NULL) product_code
1229 , vendor_serial_number
1230 , vendor_lot_number
1231 , parent_serial_number
1232 , origination_date
1233 , territory_code
1234 , time_since_new
1235 , cycles_since_new
1236 , time_since_overhaul
1237 , cycles_since_overhaul
1238 , time_since_repair
1239 , cycles_since_repair
1240 , time_since_visit
1241 , cycles_since_visit
1242 , time_since_mark
1243 , cycles_since_mark
1244 , number_of_repairs
1245 , serial_attribute_category
1246 , c_attribute1
1247 , c_attribute2
1248 , c_attribute3
1249 , c_attribute4
1250 , c_attribute5
1251 , c_attribute6
1252 , c_attribute7
1253 , c_attribute8
1254 , c_attribute9
1255 , c_attribute10
1256 , c_attribute11
1257 , c_attribute12
1258 , c_attribute13
1259 , c_attribute14
1260 , c_attribute15
1261 , c_attribute16
1262 , c_attribute17
1263 , c_attribute18
1264 , c_attribute19
1265 , c_attribute20
1266 , d_attribute1
1267 , d_attribute2
1268 , d_attribute3
1269 , d_attribute4
1270 , d_attribute5
1271 , d_attribute6
1272 , d_attribute7
1273 , d_attribute8
1274 , d_attribute9
1275 , d_attribute10
1276 , n_attribute1
1277 , n_attribute2
1278 , n_attribute3
1279 , n_attribute4
1280 , n_attribute5
1281 , n_attribute6
1282 , n_attribute7
1283 , n_attribute8
1284 , n_attribute9
1285 , n_attribute10
1286 , attribute_category
1287 , attribute1
1288 , attribute2
1289 , attribute3
1290 , attribute4
1291 , attribute5
1292 , attribute6
1293 , attribute7
1294 , attribute8
1295 , attribute9
1296 , attribute10
1297 , attribute11
1298 , attribute12
1299 , attribute13
1300 , attribute14
1301 , attribute15
1302 FROM mtl_serial_numbers
1303 WHERE serial_number = v_serial_number
1304 AND inventory_item_id = v_inventory_item_id;
1305
1306 TYPE msni_rec_tp IS RECORD (
1307 fm_serial_number mtl_serial_numbers.serial_number%TYPE
1308 , to_serial_number mtl_serial_numbers.serial_number%TYPE
1309 , transaction_interface_id mtl_serial_numbers_interface.transaction_interface_id%TYPE
1310 , status_id mtl_serial_numbers.status_id%TYPE
1311 , product_transaction_id mtl_serial_numbers_interface.product_transaction_id%TYPE
1312 , product_code mtl_serial_numbers_interface.product_code%TYPE
1313 , vendor_serial_number mtl_serial_numbers.vendor_serial_number%TYPE
1314 , vendor_lot_number mtl_serial_numbers.vendor_lot_number%TYPE
1315 , parent_serial_number mtl_serial_numbers.parent_serial_number%TYPE
1316 , origination_date mtl_serial_numbers.origination_date%TYPE
1317 , territory_code mtl_serial_numbers.territory_code%TYPE
1318 , time_since_new mtl_serial_numbers.time_since_new%TYPE
1319 , cycles_since_new mtl_serial_numbers.cycles_since_new%TYPE
1320 , time_since_overhaul mtl_serial_numbers.time_since_overhaul%TYPE
1321 , cycles_since_overhaul mtl_serial_numbers.cycles_since_overhaul%TYPE
1322 , time_since_repair mtl_serial_numbers.time_since_repair%TYPE
1323 , cycles_since_repair mtl_serial_numbers.cycles_since_repair%TYPE
1324 , time_since_visit mtl_serial_numbers.time_since_visit%TYPE
1325 , cycles_since_visit mtl_serial_numbers.cycles_since_visit%TYPE
1326 , time_since_mark mtl_serial_numbers.time_since_mark%TYPE
1327 , cycles_since_mark mtl_serial_numbers.cycles_since_mark%TYPE
1328 , number_of_repairs mtl_serial_numbers.number_of_repairs%TYPE
1329 , serial_attribute_category mtl_serial_numbers.serial_attribute_category%TYPE
1330 , c_attribute1 mtl_serial_numbers.c_attribute1%TYPE
1331 , c_attribute2 mtl_serial_numbers.c_attribute2%TYPE
1332 , c_attribute3 mtl_serial_numbers.c_attribute3%TYPE
1333 , c_attribute4 mtl_serial_numbers.c_attribute4%TYPE
1334 , c_attribute5 mtl_serial_numbers.c_attribute5%TYPE
1335 , c_attribute6 mtl_serial_numbers.c_attribute6%TYPE
1336 , c_attribute7 mtl_serial_numbers.c_attribute7%TYPE
1337 , c_attribute8 mtl_serial_numbers.c_attribute8%TYPE
1338 , c_attribute9 mtl_serial_numbers.c_attribute9%TYPE
1339 , c_attribute10 mtl_serial_numbers.c_attribute10%TYPE
1340 , c_attribute11 mtl_serial_numbers.c_attribute11%TYPE
1341 , c_attribute12 mtl_serial_numbers.c_attribute12%TYPE
1342 , c_attribute13 mtl_serial_numbers.c_attribute13%TYPE
1343 , c_attribute14 mtl_serial_numbers.c_attribute14%TYPE
1344 , c_attribute15 mtl_serial_numbers.c_attribute15%TYPE
1345 , c_attribute16 mtl_serial_numbers.c_attribute16%TYPE
1346 , c_attribute17 mtl_serial_numbers.c_attribute17%TYPE
1347 , c_attribute18 mtl_serial_numbers.c_attribute18%TYPE
1348 , c_attribute19 mtl_serial_numbers.c_attribute19%TYPE
1349 , c_attribute20 mtl_serial_numbers.c_attribute20%TYPE
1350 , d_attribute1 mtl_serial_numbers.d_attribute1%TYPE
1351 , d_attribute2 mtl_serial_numbers.d_attribute2%TYPE
1352 , d_attribute3 mtl_serial_numbers.d_attribute3%TYPE
1353 , d_attribute4 mtl_serial_numbers.d_attribute4%TYPE
1354 , d_attribute5 mtl_serial_numbers.d_attribute5%TYPE
1355 , d_attribute6 mtl_serial_numbers.d_attribute6%TYPE
1356 , d_attribute7 mtl_serial_numbers.d_attribute7%TYPE
1357 , d_attribute8 mtl_serial_numbers.d_attribute8%TYPE
1358 , d_attribute9 mtl_serial_numbers.d_attribute9%TYPE
1359 , d_attribute10 mtl_serial_numbers.d_attribute10%TYPE
1360 , n_attribute1 mtl_serial_numbers.n_attribute1%TYPE
1361 , n_attribute2 mtl_serial_numbers.n_attribute2%TYPE
1362 , n_attribute3 mtl_serial_numbers.n_attribute3%TYPE
1363 , n_attribute4 mtl_serial_numbers.n_attribute4%TYPE
1364 , n_attribute5 mtl_serial_numbers.n_attribute5%TYPE
1365 , n_attribute6 mtl_serial_numbers.n_attribute6%TYPE
1366 , n_attribute7 mtl_serial_numbers.n_attribute7%TYPE
1367 , n_attribute8 mtl_serial_numbers.n_attribute8%TYPE
1368 , n_attribute9 mtl_serial_numbers.n_attribute9%TYPE
1369 , n_attribute10 mtl_serial_numbers.n_attribute10%TYPE
1370 , attribute_category mtl_serial_numbers_interface.attribute_category%TYPE
1371 , attribute1 mtl_serial_numbers.attribute1%TYPE
1372 , attribute2 mtl_serial_numbers.attribute2%TYPE
1373 , attribute3 mtl_serial_numbers.attribute3%TYPE
1374 , attribute4 mtl_serial_numbers.attribute4%TYPE
1375 , attribute5 mtl_serial_numbers.attribute5%TYPE
1376 , attribute6 mtl_serial_numbers.attribute6%TYPE
1377 , attribute7 mtl_serial_numbers.attribute7%TYPE
1378 , attribute8 mtl_serial_numbers.attribute8%TYPE
1379 , attribute9 mtl_serial_numbers.attribute9%TYPE
1380 , attribute10 mtl_serial_numbers.attribute10%TYPE
1381 , attribute11 mtl_serial_numbers.attribute11%TYPE
1382 , attribute12 mtl_serial_numbers.attribute12%TYPE
1383 , attribute13 mtl_serial_numbers.attribute13%TYPE
1384 , attribute14 mtl_serial_numbers.attribute14%TYPE
1385 , attribute15 mtl_serial_numbers.attribute15%TYPE
1386 );
1387
1388 TYPE msni_rec_tbl_tp IS TABLE OF msni_rec_tp
1389 INDEX BY BINARY_INTEGER;
1390
1391 --Local Variables
1392 l_msni_rec_tbl msni_rec_tbl_tp; --Table to hold each MSNI record inserted
1393 l_transaction_interface_id NUMBER; --transaction_interface_id generated
1394 l_product_transaction_id NUMBER; --product_transaction_id generated
1395 l_fm_serial_number mtl_serial_numbers.serial_number%TYPE := p_fm_serial_number;
1396 l_to_serial_number mtl_serial_numbers.serial_number%TYPE := p_to_serial_number;
1397 l_cur_serial_number mtl_serial_numbers.serial_number%TYPE;
1398 l_serial_prefix VARCHAR2(30); --serial number prefix
1399 l_from_ser_number NUMBER; --numberic part of from serial number
1400 l_to_ser_number NUMBER; --numeric part of to serial number
1401 l_cur_ser_num NUMBER; --numeric part of current serial part
1402 l_ser_num_length NUMBER; --serial number length
1403 l_prefix_length NUMBER; --prefix length
1404 l_range_numbers NUMBER; --no. of serial numbers in the range
1405 l_msni_tbl_count NUMBER; --Count of records to be inserted
1406 l_user_id NUMBER := fnd_global.user_id;
1407 l_login_id NUMBER := fnd_global.login_id;
1408 l_source_code mtl_serial_numbers_interface.source_code%TYPE;
1409 l_source_line_id mtl_serial_numbers_interface.source_line_id%TYPE;
1410 l_api_version CONSTANT NUMBER := 1.0;
1411 l_api_name CONSTANT VARCHAR2(30) := 'insert_mtli';
1412 l_success NUMBER;
1413 l_progress NUMBER; --Progress Indicator
1414 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
1415 BEGIN
1416
1417 -- Standard call to check for call compatibility.
1418 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version,
1419 l_api_name, 'inv_rcv_integration_apis') THEN
1420 print_debug('FND_API not compatible','inv_rcv_integration_apis.insert_msni');
1421 RAISE fnd_api.g_exc_unexpected_error;
1422 END IF;
1423
1424 -- Initialize message list if p_init_msg_list is set to TRUE.
1425 IF fnd_api.to_boolean(p_init_msg_lst) THEN
1426 fnd_msg_pub.initialize;
1427 END IF;
1428
1429 --Initialize the return status
1430 x_return_status := fnd_api.g_ret_sts_success;
1431
1432 --Generate transaction_interface_id if necessary
1433 IF (p_transaction_interface_id IS NULL) THEN
1434 SELECT mtl_material_transactions_s.NEXTVAL
1435 INTO l_transaction_interface_id
1436 FROM sys.dual;
1437 ELSE
1438 l_transaction_interface_id := p_transaction_interface_id;
1439 END IF;
1440
1441 --Generate production_transaction_id if the parameter is NULL
1442 IF (p_product_transaction_id IS NULL AND p_product_code = 'RCV') THEN
1443 SELECT rcv_transactions_interface_s.NEXTVAL
1444 INTO l_product_transaction_id
1445 FROM sys.dual;
1446 ELSE
1447 l_product_transaction_id := p_product_transaction_id;
1448 END IF;
1449
1450 /* To insert the attributes, check the value of p_att_exist.
1451 * If the parameter p_att_exist is 'N' then
1452 * Read the input parameters and store them in the table
1453 * Create one MSNI record with from and to serial number from parameters
1454 * Else
1455 * Loop through each serial number between from and to serial number
1456 * Fetch the attributes into the table for each serial number
1457 * Create on MSNI record with from and to serial number as current serial
1458 */
1459 IF (NVL(p_att_exist, 'Y') = 'N') THEN
1460 l_msni_rec_tbl(1).fm_serial_number := p_fm_serial_number;
1461 l_msni_rec_tbl(1).to_serial_number := p_to_serial_number;
1462 l_msni_rec_tbl(1).transaction_interface_id := l_transaction_interface_id;
1463 l_msni_rec_tbl(1).status_id := p_status_id;
1464 l_msni_rec_tbl(1).product_transaction_id := l_product_transaction_id;
1465 l_msni_rec_tbl(1).product_code := p_product_code;
1466 l_msni_rec_tbl(1).vendor_serial_number := p_vendor_serial_number;
1467 l_msni_rec_tbl(1).vendor_lot_number := p_vendor_lot_number;
1468 l_msni_rec_tbl(1).parent_serial_number := p_parent_serial_number;
1469 l_msni_rec_tbl(1).origination_date := p_origination_date;
1470 l_msni_rec_tbl(1).territory_code := p_territory_code;
1471 l_msni_rec_tbl(1).time_since_new := p_time_since_new;
1472 l_msni_rec_tbl(1).cycles_since_new := p_cycles_since_new;
1473 l_msni_rec_tbl(1).time_since_overhaul := p_time_since_overhaul;
1474 l_msni_rec_tbl(1).cycles_since_overhaul := p_cycles_since_overhaul;
1475 l_msni_rec_tbl(1).time_since_repair := p_time_since_repair;
1476 l_msni_rec_tbl(1).cycles_since_repair :=p_cycles_since_repair;
1477 l_msni_rec_tbl(1).time_since_visit := p_time_since_visit;
1478 l_msni_rec_tbl(1).cycles_since_visit := p_cycles_since_visit;
1479 l_msni_rec_tbl(1).time_since_mark := p_time_since_mark;
1480 l_msni_rec_tbl(1).cycles_since_mark := p_cycles_since_mark;
1481 l_msni_rec_tbl(1).number_of_repairs := p_number_of_repairs;
1482 l_msni_rec_tbl(1).serial_attribute_category := p_serial_attribute_category;
1483 l_msni_rec_tbl(1).c_attribute1 := p_c_attribute1;
1484 l_msni_rec_tbl(1).c_attribute2 := p_c_attribute2;
1485 l_msni_rec_tbl(1).c_attribute3 := p_c_attribute3;
1486 l_msni_rec_tbl(1).c_attribute4 := p_c_attribute4;
1487 l_msni_rec_tbl(1).c_attribute5 := p_c_attribute5;
1488 l_msni_rec_tbl(1).c_attribute6 := p_c_attribute6;
1489 l_msni_rec_tbl(1).c_attribute7 := p_c_attribute7;
1490 l_msni_rec_tbl(1).c_attribute8 := p_c_attribute8;
1491 l_msni_rec_tbl(1).c_attribute9 := p_c_attribute9;
1492 l_msni_rec_tbl(1).c_attribute10 := p_c_attribute10;
1493 l_msni_rec_tbl(1).c_attribute11 := p_c_attribute11;
1494 l_msni_rec_tbl(1).c_attribute12 := p_c_attribute12;
1495 l_msni_rec_tbl(1).c_attribute13 := p_c_attribute13;
1496 l_msni_rec_tbl(1).c_attribute14 := p_c_attribute14;
1497 l_msni_rec_tbl(1).c_attribute15 := p_c_attribute15;
1498 l_msni_rec_tbl(1).c_attribute16 := p_c_attribute16;
1499 l_msni_rec_tbl(1).c_attribute17 := p_c_attribute17;
1500 l_msni_rec_tbl(1).c_attribute18 := p_c_attribute18;
1501 l_msni_rec_tbl(1).c_attribute19 := p_c_attribute19;
1502 l_msni_rec_tbl(1).c_attribute20 := p_c_attribute20;
1503 l_msni_rec_tbl(1).d_attribute1 := p_d_attribute1;
1504 l_msni_rec_tbl(1).d_attribute2 := p_d_attribute2;
1505 l_msni_rec_tbl(1).d_attribute3 := p_d_attribute3;
1506 l_msni_rec_tbl(1).d_attribute4 := p_d_attribute4;
1507 l_msni_rec_tbl(1).d_attribute5 := p_d_attribute5;
1508 l_msni_rec_tbl(1).d_attribute6 := p_d_attribute6;
1509 l_msni_rec_tbl(1).d_attribute7 := p_d_attribute7;
1510 l_msni_rec_tbl(1).d_attribute8 := p_d_attribute8;
1511 l_msni_rec_tbl(1).d_attribute9 := p_d_attribute9;
1512 l_msni_rec_tbl(1).d_attribute10 := p_d_attribute10;
1513 l_msni_rec_tbl(1).n_attribute1 := p_n_attribute1;
1514 l_msni_rec_tbl(1).n_attribute2 := p_n_attribute2;
1515 l_msni_rec_tbl(1).n_attribute3 := p_n_attribute3;
1516 l_msni_rec_tbl(1).n_attribute4 := p_n_attribute4;
1517 l_msni_rec_tbl(1).n_attribute5 := p_n_attribute5;
1518 l_msni_rec_tbl(1).n_attribute6 := p_n_attribute6;
1519 l_msni_rec_tbl(1).n_attribute7 := p_n_attribute7;
1520 l_msni_rec_tbl(1).n_attribute8 := p_n_attribute8;
1521 l_msni_rec_tbl(1).n_attribute9 := p_n_attribute9;
1522 l_msni_rec_tbl(1).n_attribute10 := p_n_attribute10;
1523 l_msni_rec_tbl(1).attribute_category := p_attribute_category;
1524 l_msni_rec_tbl(1).attribute1 := p_attribute1;
1525 l_msni_rec_tbl(1).attribute2 := p_attribute2;
1526 l_msni_rec_tbl(1).attribute3 := p_attribute3;
1527 l_msni_rec_tbl(1).attribute4 := p_attribute4;
1528 l_msni_rec_tbl(1).attribute5 := p_attribute5;
1529 l_msni_rec_tbl(1).attribute6 := p_attribute6;
1530 l_msni_rec_tbl(1).attribute7 := p_attribute7;
1531 l_msni_rec_tbl(1).attribute8 := p_attribute8;
1532 l_msni_rec_tbl(1).attribute9 := p_attribute9;
1533 l_msni_rec_tbl(1).attribute10 := p_attribute10;
1534 l_msni_rec_tbl(1).attribute11 := p_attribute11;
1535 l_msni_rec_tbl(1).attribute12 := p_attribute12;
1536 l_msni_rec_tbl(1).attribute13 := p_attribute13;
1537 l_msni_rec_tbl(1).attribute14 := p_attribute14;
1538 l_msni_rec_tbl(1).attribute15 := p_attribute15;
1539 ELSE --fetch the serial info and attributes from MSN
1540 --Get the numeric part of the from and to serial numbers
1541 inv_validate.number_from_sequence(l_fm_serial_number, l_serial_prefix, l_from_ser_number);
1542
1543 inv_validate.number_from_sequence(l_to_serial_number, l_serial_prefix, l_to_ser_number);
1544
1545 --Get the no. of serials in the range, prefix length and numeric part
1546 l_range_numbers := l_to_ser_number - l_from_ser_number + 1;
1547 l_ser_num_length := LENGTH(l_fm_serial_number);
1548 l_prefix_length := LENGTH(l_serial_prefix);
1549
1550 IF (l_debug = 1) THEN
1551 print_debug('No. of serials in the range : ' || l_range_numbers, 4);
1552 print_debug('Serial Number length: ' || l_ser_num_length, 4);
1553 print_debug('Prefix length : ' || l_prefix_length, 4);
1554 END IF;
1555
1556 --For each serial number in the range, fetch the serial info and create
1557 --a row in the table which would later be inserted into MSNI
1558 FOR i IN 1 .. l_range_numbers LOOP
1559 l_cur_ser_num := l_from_ser_number + i -1;
1560 l_cur_serial_number := l_serial_prefix ||
1561 LPAD(l_cur_ser_num, l_ser_num_length - NVL(l_prefix_length,0), '0');
1562
1563 IF (l_debug = 1) THEN
1564 print_debug('current serial number : ' || l_cur_serial_number, 4);
1565 END IF;
1566
1567 OPEN c_msn_attributes(l_cur_serial_number, p_inventory_item_id);
1568 FETCH c_msn_attributes INTO l_msni_rec_tbl(i);
1569 CLOSE c_msn_attributes;
1570
1571 --Assign the values for the serial number and ids from the generated values
1572 l_msni_rec_tbl(i).transaction_interface_id := l_transaction_interface_id;
1573 l_msni_rec_tbl(i).fm_serial_number := l_cur_serial_number;
1574 l_msni_rec_tbl(i).to_serial_number := l_cur_serial_number;
1575 l_msni_rec_tbl(i).product_transaction_id := l_product_transaction_id;
1576 l_msni_rec_tbl(i).product_code := p_product_code;
1577 END LOOP; --END For each serial number in the range
1578 END IF; --END IF p_att_exist = 'N'
1579
1580 --At this stage, we have a PL/SQL table containing one or more rows to be
1581 --inserted into MTL_SERIAL_NUMBERS_INTERFACE. Loop through the table and
1582 --insert each record
1583 l_msni_tbl_count := l_msni_rec_tbl.COUNT;
1584
1585 IF (l_debug = 1) THEN
1586 print_debug('Count of records in the table: ' || l_msni_tbl_count, 4);
1587 END IF;
1588
1589 IF l_msni_tbl_count <= 0 THEN
1590 IF (l_debug = 1) THEN
1591 print_debug('Unexpected error. The table of serials is empty!', 4);
1592 END IF;
1593 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1594 END IF;
1595
1596 FOR i IN 1 .. l_msni_tbl_count LOOP
1597
1598 INSERT INTO MTL_SERIAL_NUMBERS_INTERFACE (
1599 transaction_interface_id
1600 , source_code
1601 , source_line_id
1602 , last_update_date
1603 , last_updated_by
1604 , creation_date
1605 , created_by
1606 , last_update_login
1607 , fm_serial_number
1608 , to_serial_number
1609 , status_id
1610 , product_transaction_id
1611 , product_code
1612 , vendor_serial_number
1613 , vendor_lot_number
1614 , parent_serial_number
1615 , origination_date
1616 , territory_code
1617 , time_since_new
1618 , cycles_since_new
1619 , time_since_overhaul
1620 , cycles_since_overhaul
1621 , time_since_repair
1622 , cycles_since_repair
1623 , time_since_visit
1624 , cycles_since_visit
1625 , time_since_mark
1626 , cycles_since_mark
1627 , number_of_repairs
1628 , serial_attribute_category
1629 , c_attribute1
1630 , c_attribute2
1631 , c_attribute3
1632 , c_attribute4
1633 , c_attribute5
1634 , c_attribute6
1635 , c_attribute7
1636 , c_attribute8
1637 , c_attribute9
1638 , c_attribute10
1639 , c_attribute11
1640 , c_attribute12
1641 , c_attribute13
1642 , c_attribute14
1643 , c_attribute15
1644 , c_attribute16
1645 , c_attribute17
1646 , c_attribute18
1647 , c_attribute19
1648 , c_attribute20
1649 , d_attribute1
1650 , d_attribute2
1651 , d_attribute3
1652 , d_attribute4
1653 , d_attribute5
1654 , d_attribute6
1655 , d_attribute7
1656 , d_attribute8
1657 , d_attribute9
1658 , d_attribute10
1659 , n_attribute1
1660 , n_attribute2
1661 , n_attribute3
1662 , n_attribute4
1663 , n_attribute5
1664 , n_attribute6
1665 , n_attribute7
1666 , n_attribute8
1667 , n_attribute9
1668 , n_attribute10
1669 , attribute_category
1670 , attribute1
1671 , attribute2
1672 , attribute3
1673 , attribute4
1674 , attribute5
1675 , attribute6
1676 , attribute7
1677 , attribute8
1678 , attribute9
1679 , attribute10
1680 , attribute11
1681 , attribute12
1682 , attribute13
1683 , attribute14
1684 , attribute15
1685 )
1686 VALUES (
1687 l_msni_rec_tbl(i).transaction_interface_id
1688 , l_source_code
1689 , l_source_line_id
1690 , SYSDATE
1691 , l_user_id
1692 , SYSDATE
1693 , l_user_id
1694 , l_login_id
1695 , l_msni_rec_tbl(i).fm_serial_number
1696 , l_msni_rec_tbl(i).to_serial_number
1697 , l_msni_rec_tbl(i).status_id
1698 , l_msni_rec_tbl(i).product_transaction_id
1699 , l_msni_rec_tbl(i).product_code
1700 , l_msni_rec_tbl(i).vendor_serial_number
1701 , l_msni_rec_tbl(i).vendor_lot_number
1702 , l_msni_rec_tbl(i).parent_serial_number
1703 , l_msni_rec_tbl(i).origination_date
1704 , l_msni_rec_tbl(i).territory_code
1705 , l_msni_rec_tbl(i).time_since_new
1706 , l_msni_rec_tbl(i).cycles_since_new
1707 , l_msni_rec_tbl(i).time_since_overhaul
1708 , l_msni_rec_tbl(i).cycles_since_overhaul
1709 , l_msni_rec_tbl(i).time_since_repair
1710 , l_msni_rec_tbl(i).cycles_since_repair
1711 , l_msni_rec_tbl(i).time_since_visit
1712 , l_msni_rec_tbl(i).cycles_since_visit
1713 , l_msni_rec_tbl(i).time_since_mark
1714 , l_msni_rec_tbl(i).cycles_since_mark
1715 , l_msni_rec_tbl(i).number_of_repairs
1716 , l_msni_rec_tbl(i).serial_attribute_category
1717 , l_msni_rec_tbl(i).c_attribute1
1718 , l_msni_rec_tbl(i).c_attribute2
1719 , l_msni_rec_tbl(i).c_attribute3
1720 , l_msni_rec_tbl(i).c_attribute4
1721 , l_msni_rec_tbl(i).c_attribute5
1722 , l_msni_rec_tbl(i).c_attribute6
1723 , l_msni_rec_tbl(i).c_attribute7
1724 , l_msni_rec_tbl(i).c_attribute8
1725 , l_msni_rec_tbl(i).c_attribute9
1726 , l_msni_rec_tbl(i).c_attribute10
1727 , l_msni_rec_tbl(i).c_attribute11
1728 , l_msni_rec_tbl(i).c_attribute12
1729 , l_msni_rec_tbl(i).c_attribute13
1730 , l_msni_rec_tbl(i).c_attribute14
1731 , l_msni_rec_tbl(i).c_attribute15
1732 , l_msni_rec_tbl(i).c_attribute16
1733 , l_msni_rec_tbl(i).c_attribute17
1734 , l_msni_rec_tbl(i).c_attribute18
1735 , l_msni_rec_tbl(i).c_attribute19
1736 , l_msni_rec_tbl(i).c_attribute20
1737 , l_msni_rec_tbl(i).d_attribute1
1738 , l_msni_rec_tbl(i).d_attribute2
1739 , l_msni_rec_tbl(i).d_attribute3
1740 , l_msni_rec_tbl(i).d_attribute4
1741 , l_msni_rec_tbl(i).d_attribute5
1742 , l_msni_rec_tbl(i).d_attribute6
1743 , l_msni_rec_tbl(i).d_attribute7
1744 , l_msni_rec_tbl(i).d_attribute8
1745 , l_msni_rec_tbl(i).d_attribute9
1746 , l_msni_rec_tbl(i).d_attribute10
1747 , l_msni_rec_tbl(i).n_attribute1
1748 , l_msni_rec_tbl(i).n_attribute2
1749 , l_msni_rec_tbl(i).n_attribute3
1750 , l_msni_rec_tbl(i).n_attribute4
1751 , l_msni_rec_tbl(i).n_attribute5
1752 , l_msni_rec_tbl(i).n_attribute6
1753 , l_msni_rec_tbl(i).n_attribute7
1754 , l_msni_rec_tbl(i).n_attribute8
1755 , l_msni_rec_tbl(i).n_attribute9
1756 , l_msni_rec_tbl(i).n_attribute10
1757 , l_msni_rec_tbl(i).attribute_category
1758 , l_msni_rec_tbl(i).attribute1
1759 , l_msni_rec_tbl(i).attribute2
1760 , l_msni_rec_tbl(i).attribute3
1761 , l_msni_rec_tbl(i).attribute4
1762 , l_msni_rec_tbl(i).attribute5
1763 , l_msni_rec_tbl(i).attribute6
1764 , l_msni_rec_tbl(i).attribute7
1765 , l_msni_rec_tbl(i).attribute8
1766 , l_msni_rec_tbl(i).attribute9
1767 , l_msni_rec_tbl(i).attribute10
1768 , l_msni_rec_tbl(i).attribute11
1769 , l_msni_rec_tbl(i).attribute12
1770 , l_msni_rec_tbl(i).attribute13
1771 , l_msni_rec_tbl(i).attribute14
1772 , l_msni_rec_tbl(i).attribute15
1773 );
1774 --If the flag p_update_msn is set then update MSN with the attributes
1775 IF (NVL(p_update_msn, 'N') = 'Y') THEN
1776 UPDATE mtl_serial_numbers
1777 SET vendor_serial_number = l_msni_rec_tbl(i).vendor_serial_number
1778 , vendor_lot_number = l_msni_rec_tbl(i).vendor_lot_number
1779 , parent_serial_number = l_msni_rec_tbl(i).parent_serial_number
1780 , origination_date = l_msni_rec_tbl(i).origination_date
1781 , territory_code = l_msni_rec_tbl(i).territory_code
1782 , time_since_new = l_msni_rec_tbl(i).time_since_new
1783 , cycles_since_new = l_msni_rec_tbl(i).cycles_since_new
1784 , time_since_overhaul = l_msni_rec_tbl(i).time_since_overhaul
1785 , cycles_since_overhaul = l_msni_rec_tbl(i).cycles_since_overhaul
1786 , time_since_repair = l_msni_rec_tbl(i).time_since_repair
1787 , cycles_since_repair = l_msni_rec_tbl(i).cycles_since_repair
1788 , time_since_visit = l_msni_rec_tbl(i).time_since_visit
1789 , cycles_since_visit = l_msni_rec_tbl(i).cycles_since_visit
1790 , time_since_mark = l_msni_rec_tbl(i).time_since_mark
1791 , cycles_since_mark = l_msni_rec_tbl(i).cycles_since_mark
1792 , number_of_repairs = l_msni_rec_tbl(i).number_of_repairs
1793 , serial_attribute_category = l_msni_rec_tbl(i).serial_attribute_category
1794 , c_attribute1 = l_msni_rec_tbl(i).c_attribute1
1795 , c_attribute2 = l_msni_rec_tbl(i).c_attribute2
1796 , c_attribute3 = l_msni_rec_tbl(i).c_attribute3
1797 , c_attribute4 = l_msni_rec_tbl(i).c_attribute4
1798 , c_attribute5 = l_msni_rec_tbl(i).c_attribute5
1799 , c_attribute6 = l_msni_rec_tbl(i).c_attribute6
1800 , c_attribute7 = l_msni_rec_tbl(i).c_attribute7
1801 , c_attribute8 = l_msni_rec_tbl(i).c_attribute8
1802 , c_attribute9 = l_msni_rec_tbl(i).c_attribute9
1803 , c_attribute10 = l_msni_rec_tbl(i).c_attribute10
1804 , c_attribute11 = l_msni_rec_tbl(i).c_attribute11
1805 , c_attribute12 = l_msni_rec_tbl(i).c_attribute12
1806 , c_attribute13 = l_msni_rec_tbl(i).c_attribute13
1807 , c_attribute14 = l_msni_rec_tbl(i).c_attribute14
1808 , c_attribute15 = l_msni_rec_tbl(i).c_attribute15
1809 , c_attribute16 = l_msni_rec_tbl(i).c_attribute16
1810 , c_attribute17 = l_msni_rec_tbl(i).c_attribute17
1811 , c_attribute18 = l_msni_rec_tbl(i).c_attribute18
1812 , c_attribute19 = l_msni_rec_tbl(i).c_attribute19
1813 , c_attribute20 = l_msni_rec_tbl(i).c_attribute20
1814 , d_attribute1 = l_msni_rec_tbl(i).d_attribute1
1815 , d_attribute2 = l_msni_rec_tbl(i).d_attribute2
1816 , d_attribute3 = l_msni_rec_tbl(i).d_attribute3
1817 , d_attribute4 = l_msni_rec_tbl(i).d_attribute4
1818 , d_attribute5 = l_msni_rec_tbl(i).d_attribute5
1819 , d_attribute6 = l_msni_rec_tbl(i).d_attribute6
1820 , d_attribute7 = l_msni_rec_tbl(i).d_attribute7
1821 , d_attribute8 = l_msni_rec_tbl(i).d_attribute8
1822 , d_attribute9 = l_msni_rec_tbl(i).d_attribute9
1823 , d_attribute10 = l_msni_rec_tbl(i).d_attribute10
1824 , n_attribute1 = l_msni_rec_tbl(i).n_attribute1
1825 , n_attribute2 = l_msni_rec_tbl(i).n_attribute2
1826 , n_attribute3 = l_msni_rec_tbl(i).n_attribute3
1827 , n_attribute4 = l_msni_rec_tbl(i).n_attribute4
1828 , n_attribute5 = l_msni_rec_tbl(i).n_attribute5
1829 , n_attribute6 = l_msni_rec_tbl(i).n_attribute6
1830 , n_attribute7 = l_msni_rec_tbl(i).n_attribute7
1831 , n_attribute8 = l_msni_rec_tbl(i).n_attribute8
1832 , n_attribute9 = l_msni_rec_tbl(i).n_attribute9
1833 , n_attribute10 = l_msni_rec_tbl(i).n_attribute10
1834 , attribute_category = l_msni_rec_tbl(i).attribute_category
1835 , attribute1 = l_msni_rec_tbl(i).attribute1
1836 , attribute2 = l_msni_rec_tbl(i).attribute2
1837 , attribute3 = l_msni_rec_tbl(i).attribute3
1838 , attribute4 = l_msni_rec_tbl(i).attribute4
1839 , attribute5 = l_msni_rec_tbl(i).attribute5
1840 , attribute6 = l_msni_rec_tbl(i).attribute6
1841 , attribute7 = l_msni_rec_tbl(i).attribute7
1842 , attribute8 = l_msni_rec_tbl(i).attribute8
1843 , attribute9 = l_msni_rec_tbl(i).attribute9
1844 , attribute10 = l_msni_rec_tbl(i).attribute10
1845 , attribute11 = l_msni_rec_tbl(i).attribute11
1846 , attribute12 = l_msni_rec_tbl(i).attribute12
1847 , attribute13 = l_msni_rec_tbl(i).attribute13
1848 , attribute14 = l_msni_rec_tbl(i).attribute14
1849 , attribute15 = l_msni_rec_tbl(i).attribute15
1850 WHERE inventory_item_id = p_inventory_item_id
1851 AND serial_number between
1852 l_msni_rec_tbl(i).fm_serial_number and
1853 l_msni_rec_tbl(i).to_serial_number
1854 AND LENGTH(serial_number) = LENGTH(p_fm_serial_number);
1855 END IF; --END If p_update_msn = 'Y'
1856 END LOOP; --END for each serial record in the table
1857
1858 --Now mark the serials passed to the API. Set the group_mark_id with
1859 --the product_transaction_id generated above
1860 serial_check.inv_mark_serial(
1861 from_serial_number => p_fm_serial_number
1862 , to_serial_number => p_to_serial_number
1863 , item_id => p_inventory_item_id
1864 , org_id => p_organization_id
1865 , hdr_id => l_product_transaction_id
1866 , temp_id => NULL
1867 , lot_temp_id => NULL
1868 , success => l_success );
1869
1870 --Reassign the generated values
1871 p_transaction_interface_id := l_transaction_interface_id;
1872 p_product_transaction_id := l_product_transaction_id;
1873
1874 IF (l_debug = 1) THEN
1875 print_debug('p_transaction_interface_id returned: ' || p_transaction_interface_id, 4);
1876 print_debug('p_product_transaction_id returned: ' || p_product_transaction_id, 4);
1877 END IF;
1878
1879 EXCEPTION
1880 WHEN FND_API.G_EXC_ERROR THEN
1881 IF c_msn_attributes%ISOPEN THEN
1882 CLOSE c_msn_attributes;
1883 END IF;
1884 x_return_status := fnd_api.g_ret_sts_error;
1885 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
1886 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1887 IF c_msn_attributes%ISOPEN THEN
1888 CLOSE c_msn_attributes;
1889 END IF;
1890 x_return_status := fnd_api.g_ret_sts_unexp_error;
1891 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
1892 WHEN OTHERS THEN
1893 IF c_msn_attributes%ISOPEN THEN
1894 CLOSE c_msn_attributes;
1895 END IF;
1896 x_return_status := fnd_api.g_ret_sts_unexp_error;
1897 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
1898
1899 IF SQLCODE IS NOT NULL THEN
1900 inv_mobile_helper_functions.sql_error('INV_RCV_INTEGRATION_APIS.INSERT_MSNI', l_progress, SQLCODE);
1901 END IF;
1902 END insert_msni;
1903
1904 -- being called from iSP to validate the lot number
1905 FUNCTION validate_lot_number(
1906 p_api_version IN NUMBER
1907 , p_init_msg_lst IN VARCHAR2
1908 , x_return_status OUT NOCOPY VARCHAR2
1909 , x_msg_count OUT NOCOPY NUMBER
1910 , x_msg_data OUT NOCOPY VARCHAR2
1911 , x_is_new_lot OUT NOCOPY VARCHAR2
1912 , p_validation_mode IN NUMBER
1913 , p_org_id IN NUMBER
1914 , p_inventory_item_id IN NUMBER
1915 , p_lot_number IN VARCHAR2
1916 , p_expiration_date IN DATE
1917 , p_txn_type IN NUMBER
1918 , p_disable_flag IN NUMBER
1919 , p_attribute_category IN VARCHAR2
1920 , p_lot_attribute_category IN VARCHAR2
1921 , p_attributes_tbl IN inv_lot_api_pub.char_tbl
1922 , p_c_attributes_tbl IN inv_lot_api_pub.char_tbl
1923 , p_n_attributes_tbl IN inv_lot_api_pub.number_tbl
1924 , p_d_attributes_tbl IN inv_lot_api_pub.date_tbl
1925 , p_grade_code IN VARCHAR2
1926 , p_origination_date IN DATE
1927 , p_date_code IN VARCHAR2
1928 , p_status_id IN NUMBER
1929 , p_change_date IN DATE
1930 , p_age IN NUMBER
1931 , p_retest_date IN DATE
1932 , p_maturity_date IN DATE
1933 , p_item_size IN NUMBER
1934 , p_color IN VARCHAR2
1935 , p_volume IN NUMBER
1936 , p_volume_uom IN VARCHAR2
1937 , p_place_of_origin IN VARCHAR2
1938 , p_best_by_date IN DATE
1939 , p_length IN NUMBER
1940 , p_length_uom IN VARCHAR2
1941 , p_recycled_content IN NUMBER
1942 , p_thickness IN NUMBER
1943 , p_thickness_uom IN VARCHAR2
1944 , p_width IN NUMBER
1945 , p_width_uom IN VARCHAR2
1946 , p_territory_code IN VARCHAR2
1947 , p_supplier_lot_number IN VARCHAR2
1948 , p_vendor_name IN VARCHAR2
1949 )
1950 RETURN BOOLEAN IS
1951 l_lot_exists VARCHAR2(1) := 'N';
1952 l_unique_lot BOOLEAN;
1953 l_lot_uniqueness NUMBER;
1954 no_shelf_life_control CONSTANT NUMBER := 1;
1955 item_shelf_life_days CONSTANT NUMBER := 2;
1956 user_defined_exp_date CONSTANT NUMBER := 4;
1957 l_shelf_life_days mtl_system_items.shelf_life_days%TYPE;
1958 l_shelf_life_code mtl_system_items.shelf_life_code%TYPE;
1959 l_expiration_date mtl_lot_numbers.expiration_date%TYPE;
1960 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1961 BEGIN
1962 x_return_status := g_ret_sts_success;
1963
1964 fnd_msg_pub.initialize;
1965
1966 IF l_debug = 1 THEN
1967 print_debug('validate_lot_number: Entered with following parameters:', 9);
1968 print_debug('validate_lot_number: lot_number: ' || p_lot_number, 9);
1969 print_debug('validate_lot_number: org_id: ' || p_org_id, 9);
1970 print_debug('validate_lot_number: item_id: ' || p_inventory_item_id, 9);
1971 print_debug('validate_lot_number: expiration_date: ' || p_expiration_date, 9);
1972 print_debug('validate_lot_number: validation_mode: ' || p_validation_mode, 9);
1973 print_debug('validate_lot_number: txn_type: ' || p_txn_type, 9);
1974 print_debug('validate_lot_number: disable_flag: ' || p_disable_flag, 9);
1975 print_debug('validate_lot_number: attribute_category: ' ||p_attribute_category, 9);
1976 print_debug('validate_lot_number: lot_attribute_category: ' ||p_lot_attribute_category, 9);
1977 -- print_debug('validate_lot_number: attributes_tbl: ' ||p_attributes_tbl, 9);
1978 -- print_debug('validate_lot_number: c_attributes_tbl: ' ||p_c_attributes_tbl, 9);
1979 -- print_debug('validate_lot_number: n_attributes_tbl: ' ||p_n_attributes_tbl, 9);
1980 -- print_debug('validate_lot_number: d_attributes_tbl: ' ||p_d_attributes_tbl, 9);
1981 print_debug('validate_lot_number: grade_code: ' || p_grade_code, 9);
1982 print_debug('validate_lot_number: origination_date: ' ||p_origination_date, 9);
1983 print_debug('validate_lot_number: date_code: ' || p_date_code, 9);
1984 print_debug('validate_lot_number: status_id: ' || p_status_id, 9);
1985 print_debug('validate_lot_number: change_date: ' || p_change_date, 9);
1986 print_debug('validate_lot_number: age: ' || p_age, 9);
1987 print_debug('validate_lot_number: retest_date: ' || p_retest_date, 9);
1988 print_debug('validate_lot_number: maturity_date: ' ||p_maturity_date, 9);
1989 print_debug('validate_lot_number: item_size: ' || p_item_size, 9);
1990 print_debug('validate_lot_number: color: ' || p_color, 9);
1991 print_debug('validate_lot_number: volume: ' || p_volume, 9);
1992 print_debug('validate_lot_number: volume_uom: ' || p_volume_uom, 9);
1993 print_debug('validate_lot_number: place_of_origin: ' ||p_place_of_origin, 9);
1994 print_debug('validate_lot_number: best_by_date: ' || p_best_by_date, 9);
1995 print_debug('validate_lot_number: length: ' || p_length, 9);
1996 print_debug('validate_lot_number: length_uom: ' || p_length_uom, 9);
1997 print_debug('validate_lot_number: recycled_content: ' ||p_recycled_content, 9);
1998 print_debug('validate_lot_number: thickness: ' || p_thickness, 9);
1999 print_debug('validate_lot_number: thickness_uom: ' ||p_thickness_uom, 9);
2000 print_debug('validate_lot_number: width: ' || p_width, 9);
2001 print_debug('validate_lot_number: width_uom: ' || p_width_uom, 9);
2002 print_debug('validate_lot_number: territory_code: ' ||p_territory_code, 9);
2003 print_debug('validate_lot_number: supplier_lot_number: ' ||p_supplier_lot_number, 9);
2004 print_debug('validate_lot_number: vendor_name: ' || p_vendor_name, 9);
2005 END IF;
2006
2007 --The validations should be called only if the transaction type is Ship
2008 IF p_txn_type = INV_RCV_INTEGRATION_APIS.G_SHIP THEN
2009 --First check if the lot exists in the given organization
2010 BEGIN
2011 SELECT 'Y'
2012 INTO l_lot_exists
2013 FROM mtl_lot_numbers
2014 WHERE lot_number = LTRIM(RTRIM(p_lot_number))
2015 AND inventory_item_id = p_inventory_item_id
2016 AND organization_id = p_org_id;
2017 EXCEPTION
2018 WHEN OTHERS THEN
2019 l_lot_exists := 'N';
2020 END;
2021
2022 IF (l_debug = 1) THEN
2023 print_debug('validate_lot_number: Lot Exists: ' || l_lot_exists, 9);
2024 END IF;
2025
2026 IF (l_lot_exists = 'Y') THEN
2027 x_is_new_lot := 'N';
2028 ELSE
2029 x_is_new_lot := 'Y';
2030 END IF;
2031
2032 --then raise an error indicating invalid lot number
2033 IF (p_validation_mode = INV_RCV_INTEGRATION_APIS.G_EXISTS_ONLY AND
2034 l_lot_exists = 'N') THEN
2035 fnd_message.set_name('INV', 'INV_INVALID_LOT');
2036 fnd_msg_pub.ADD;
2037 x_return_status := FND_API.g_ret_sts_error;
2038 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
2039 print_debug('err: ' || x_msg_data, 9);
2040 RETURN FALSE;
2041 END IF;
2042
2043 --If the lot number exists for the given item and org combination then:
2044 -- a) Check for lot number uniqueness within the organization
2045 -- b) Validate the expiration date based on shelf life code
2046 IF (p_lot_number IS NOT NULL) THEN
2047 --If the lot exists, check for lot uniqueness
2048 l_unique_lot := inv_lot_api_pub.validate_unique_lot(
2049 p_org_id => p_org_id
2050 , p_inventory_item_id => p_inventory_item_id
2051 , p_lot_uniqueness => l_lot_uniqueness
2052 , p_auto_lot_number => p_lot_number);
2053
2054 --If the lot is not unique then raise an error
2055 IF NOT l_unique_lot THEN
2056 fnd_message.set_name('INV', 'INV_LOT_UNIQUE_FAIL');
2057 fnd_msg_pub.add;
2058 x_return_status := FND_API.g_ret_sts_error;
2059 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
2060 RETURN FALSE;
2061 END IF;
2062
2063 --Validate the expiration date based on shelf life code
2064 BEGIN
2065 SELECT shelf_life_days
2066 , shelf_life_code
2067 INTO l_shelf_life_days
2068 , l_shelf_life_code
2069 FROM mtl_system_items
2070 WHERE inventory_item_id = p_inventory_item_id
2071 AND organization_id = p_org_id;
2072 EXCEPTION
2073 WHEN NO_DATA_FOUND THEN
2074 fnd_message.set_name('INV', 'INV_SHELF_LIFE_ERROR');
2075 fnd_message.set_token('INV', 'ITEM');
2076 fnd_msg_pub.ADD;
2077 IF l_debug = 1 THEN
2078 print_debug('validate_lot_number: Unable to fetch shelf life code for the inventory item passed', 9);
2079 END IF;
2080 x_return_status := FND_API.g_ret_sts_error;
2081 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
2082 RETURN FALSE;
2083 END;
2084
2085 IF l_debug = 1 THEN
2086 print_debug('validate_lot_number: item shelf life code ' || l_shelf_life_code, 9);
2087 END IF;
2088
2089 IF l_shelf_life_code = item_shelf_life_days THEN
2090 IF l_debug = 1 THEN
2091 print_debug('Shelf_life code is of type ITEM_SHELF_LIFE_DAYS', 9);
2092 END IF;
2093
2094 SELECT SYSDATE + l_shelf_life_days
2095 INTO l_expiration_date
2096 FROM DUAL;
2097
2098 IF TRUNC(l_expiration_date) <> trunc(p_expiration_date) THEN
2099 fnd_message.set_name('INV', 'INV_EXP_DATE_NOT_CONSIDER');
2100 fnd_msg_pub.ADD;
2101 IF l_debug = 1 THEN
2102 print_debug('validate_lot_number: Expiration will not be considered for shelf_life code of type ITEM_SHELF_LIFE_DAYS', 9);
2103 END IF;
2104 x_return_status := FND_API.g_ret_sts_error;
2105 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
2106 RETURN FALSE;
2107 END IF;
2108 ELSIF l_shelf_life_code = user_defined_exp_date THEN
2109 IF l_debug = 1 THEN
2110 print_debug('validate_lot_number: Shelf_life code is of type USER_DEFINED_EXP_DATE', 9);
2111 END IF;
2112 IF p_expiration_date IS NULL THEN
2113 fnd_message.set_name('INV', 'INV_LOT_EXPREQD');
2114 fnd_msg_pub.ADD;
2115 IF l_debug = 1 THEN
2116 print_debug('validate_lot_number: Lot expiration date is required ', 9);
2117 END IF;
2118 x_return_status := FND_API.g_ret_sts_error;
2119 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
2120 RETURN FALSE;
2121 ELSE
2122 l_expiration_date := p_expiration_date;
2123 END IF;
2124 ELSE
2125 IF l_debug = 1 THEN
2126 print_debug('validate_lot_number: Shelf_life code is of type NO_SHELF_LIFE_CONTROL', 9);
2127 END IF;
2128 END IF; /* l_shelf_life_code = item_shelf_life_days */
2129 END IF; --END IF validations for the lot if it exists
2130
2131 --If the validation mode is exists_or_create and the the lot does not exist
2132 IF (p_validation_mode = INV_RCV_INTEGRATION_APIS.G_EXISTS_OR_CREATE AND
2133 l_lot_exists = 'N') THEN
2134 --Call the create_inv_lot API to create the lot number
2135 l_expiration_date := p_expiration_date;
2136 IF (l_debug = 1) THEN
2137 print_debug('validate_lot_number: calling inv_lot_api_pub.creat_inv_lot to create the lot', 9);
2138 END IF;
2139
2140 inv_lot_api_pub.create_inv_lot(x_return_status => x_return_status
2141 , x_msg_count => x_msg_count
2142 , x_msg_data => x_msg_data
2143 , p_inventory_item_id => p_inventory_item_id
2144 , p_organization_id => p_org_id
2145 , p_lot_number => p_lot_number
2146 , p_expiration_date => l_expiration_date
2147 , p_disable_flag => p_disable_flag
2148 , p_attribute_category => p_attribute_category
2149 , p_lot_attribute_category => p_lot_attribute_category
2150 , p_attributes_tbl => p_attributes_tbl
2151 , p_c_attributes_tbl => p_c_attributes_tbl
2152 , p_n_attributes_tbl => p_n_attributes_tbl
2153 , p_d_attributes_tbl => p_d_attributes_tbl
2154 , p_grade_code => p_grade_code
2155 , p_origination_date => p_origination_date
2156 , p_date_code => p_date_code
2157 , p_status_id => p_status_id
2158 , p_change_date => p_change_date
2159 , p_age => p_age
2160 , p_retest_date => p_retest_date
2161 , p_maturity_date => p_maturity_date
2162 , p_item_size => p_item_size
2163 , p_color => p_color
2164 , p_volume => p_volume
2165 , p_volume_uom => p_volume_uom
2166 , p_place_of_origin => p_place_of_origin
2167 , p_best_by_date => p_best_by_date
2168 , p_length => p_Length
2169 , p_length_uom => p_length_uom
2170 , p_recycled_content => p_recycled_content
2171 , p_thickness => p_thickness
2172 , p_thickness_uom => p_thickness_uom
2173 , p_width => p_width
2174 , p_width_uom => p_width_uom
2175 , p_territory_code => p_territory_code
2176 , p_supplier_lot_number => p_supplier_lot_number
2177 , p_vendor_name => p_vendor_name
2178 , p_source => inv_lot_api_pub.inv);
2179 IF x_return_status <> fnd_api.g_ret_sts_success THEN
2180 IF (l_debug = 1) THEN
2181 print_debug('validate_lot_number: Error in creating the lot number', 9);
2182 END IF;
2183 x_return_status := FND_API.g_ret_sts_error;
2184 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
2185 RETURN FALSE;
2186 END IF;
2187 END IF; --END IF create the lot number
2188 --This API should be getting called only for a ship transaction from iSP.
2189 ELSE
2190 fnd_message.set_name('INV','INV_NOT_IMPLEMENTED');
2191 fnd_msg_pub.add;
2192 x_return_status := FND_API.g_ret_sts_error;
2193 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
2194 RETURN FALSE;
2195 END IF; --END IF Check the transaction type
2196
2197 --All the validations have passed successfully, then
2198 RETURN TRUE;
2199
2200 EXCEPTION
2201 WHEN OTHERS THEN
2202 IF (l_debug = 1) THEN
2203 print_debug('validate_lot_number: Exception occurred in validate_lot_number', 3);
2204 END IF;
2205 x_return_status := FND_API.g_ret_sts_error;
2206 fnd_msg_pub.count_and_get(
2207 p_encoded => fnd_api.g_false
2208 , p_count => x_msg_count
2209 , p_data => x_msg_data);
2210 IF SQLCODE IS NOT NULL THEN
2211 inv_mobile_helper_functions.sql_error(
2212 'INV_RCV_INTEGRATION_APIS.VALIDATE_LOT_NUMBER', SQLCODE, SQLERRM);
2213 END IF;
2214 RETURN FALSE;
2215 END validate_lot_number;
2216
2217 -- being called from iSP to validate the range of serial numbers
2218 FUNCTION validate_serial_range(
2219 p_api_version IN NUMBER
2220 , p_init_msg_lst IN VARCHAR2
2221 , x_return_status OUT NOCOPY VARCHAR2
2222 , x_msg_count OUT NOCOPY NUMBER
2223 , x_msg_data OUT NOCOPY VARCHAR2
2224 , p_validation_mode IN NUMBER
2225 , p_org_id IN NUMBER
2226 , p_inventory_item_id IN NUMBER
2227 , p_quantity IN NUMBER
2228 , p_revision IN VARCHAR2
2229 , p_lot_number IN VARCHAR2
2230 , p_fm_serial_number IN VARCHAR2
2231 , p_to_serial_number IN OUT NOCOPY VARCHAR2
2232 , p_txn_type IN NUMBER
2233 )
2234 RETURN BOOLEAN IS
2235 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2236 l_serial_diff NUMBER := 0;
2237 l_serial_qty NUMBER := 0;
2238 l_serial_prefix VARCHAR2(30);
2239 l_cur_serial_number mtl_serial_numbers.serial_number%TYPE;
2240 l_from_ser_number NUMBER;
2241 l_to_ser_number NUMBER;
2242 l_cur_ser_num NUMBER;
2243 l_ser_num_length NUMBER;
2244 l_prefix_length NUMBER;
2245 l_range_numbers NUMBER;
2246 l_serial_exists VARCHAR2(1);
2247 l_return NUMBER;
2248 --Bug 11708191 Modified the type of the bleow parameter to the msn.serial_number type.
2249 l_to_serial_number mtl_serial_numbers.serial_number%TYPE; --Bug 8413853
2250 BEGIN
2251 x_return_status := g_ret_sts_success;
2252
2253 fnd_msg_pub.initialize;
2254
2255
2256 IF l_debug = 1 THEN
2257 print_debug('validate_serial_range: Entered with following parameters:', 9);
2258 print_debug('validate_serial_range: fm_serial_number: ' || p_fm_serial_number, 9);
2259 print_debug('validate_serial_range: to_serial_number: ' || p_to_serial_number, 9);
2260 print_debug('validate_serial_range: quantity: ' || p_quantity, 9);
2261 print_debug('validate_serial_range: org_id: ' || p_org_id, 9);
2262 print_debug('validate_serial_range: item_id: ' || p_inventory_item_id, 9);
2263 print_debug('validate_serial_range: revision: ' || p_revision, 9);
2264 print_debug('validate_serial_range: lot_number: ' || p_lot_number, 9);
2265 print_debug('validate_serial_range: validation_mode: ' || p_validation_mode, 9);
2266 print_debug('validate_serial_range: txn_type: ' || p_txn_type, 9);
2267 END IF;
2268
2269 --The validations should be called only if the transaction type is Ship
2270 IF p_txn_type = INV_RCV_INTEGRATION_APIS.G_SHIP THEN
2271 --If from serial is not given then raise an error
2272 IF p_fm_serial_number IS NULL THEN
2273 IF (l_debug = 1) THEN
2274 print_debug('validate_serial_range: From Serial Number cannot be NULL', 9);
2275 END IF;
2276 fnd_message.set_name('INV', 'INV_INLTIS_FROMSER');
2277 fnd_msg_pub.add;
2278 x_return_status := FND_API.g_ret_sts_error;
2279 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
2280 RETURN FALSE;
2281 END IF;
2282
2283 --If to serial number and quantity both are NULL then raise an error
2284 IF p_to_serial_number IS NULL AND p_quantity IS NULL THEN
2285 IF (l_debug = 1) THEN
2286 print_debug('validate_serial_range: To Serial and quantity both cannot be NULL', 9);
2287 END IF;
2288 fnd_message.set_name('INV', 'INV_INLTIS_RANGE');
2289 fnd_msg_pub.add;
2290 x_return_status := FND_API.g_ret_sts_error;
2291 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
2292 RETURN FALSE;
2293 END IF;
2294
2295 --If to serial number is passed, get the difference between from and to serials
2296 IF p_to_serial_number IS NOT NULL THEN
2297 l_serial_diff := inv_serial_number_pub.get_serial_diff(p_fm_serial_number, p_to_serial_number);
2298
2299 --If there was any problem in the from and serials length, this API would
2300 --return the difference as -1. If this is so, then return an error
2301 IF l_serial_diff < 0 THEN
2302 IF (l_debug = 1) THEN
2303 print_debug('validate_serial_range: Length of from and to serials do not match', 9);
2304 END IF;
2305 -- Bug Fix 4375959
2306 -- fnd_message.set_name('INV', 'INV_INLTIS_RANGE');
2307 fnd_msg_pub.add;
2308 x_return_status := FND_API.g_ret_sts_error;
2309 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
2310 RETURN FALSE;
2311 END IF; --End If serial difference is < 0
2312
2313 --If quantity is > 1 and the serial difference does not match the quantity
2314 --then raise an error
2315
2316 -- Code changed to p_quantity > 0, handle a bug regarding validation of
2317 -- where the quantity is 1. Bug No. 3579958
2318 IF (p_quantity IS NOT NULL AND p_quantity > 0 AND p_quantity <> l_serial_diff) THEN
2319 IF (l_debug = 1) THEN
2320 print_debug('validate_serial_range: Serial quantity does not match transaction quantity', 9);
2321 END IF;
2322 fnd_message.set_name('INV', 'INV_SERQTY_NOTMATCH');
2323 fnd_msg_pub.add;
2324 x_return_status := FND_API.g_ret_sts_error;
2325 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
2326 RETURN FALSE;
2327 END IF;
2328 END IF; --END IF p_to_serial_number is NOT NULL
2329
2330 --If the validation mode is exists_only then check for the existence of each
2331 --serial within the range
2332 IF p_validation_mode = INV_RCV_INTEGRATION_APIS.G_EXISTS_ONLY THEN
2333 --If to serial is not given then raise an error
2334 IF p_to_serial_number IS NULL THEN
2335 IF (l_debug = 1) THEN
2336 print_debug('validate_serial_range: To serial number must be specified', 9);
2337 END IF;
2338 fnd_message.set_name('INV', 'INV_SERIAL_NOT_ENTERED');
2339 fnd_msg_pub.add;
2340 x_return_status := FND_API.g_ret_sts_error;
2341 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
2342 RETURN FALSE;
2343 END IF; --END IF p_to_serial_number IS NULL
2344
2345 --Now check for the existence of all the serials in the range
2346 inv_validate.number_from_sequence(p_fm_serial_number, l_serial_prefix, l_from_ser_number);
2347 inv_validate.number_from_sequence(p_to_serial_number, l_serial_prefix, l_to_ser_number);
2348 --Get the no. of serials in the range, prefix length and numeric part
2349 l_range_numbers := l_to_ser_number - l_from_ser_number + 1;
2350 l_ser_num_length := LENGTH(p_fm_serial_number);
2351 l_prefix_length := LENGTH(l_serial_prefix);
2352
2353 IF (l_debug = 1) THEN
2354 print_debug('No. of serials in the range : ' || l_range_numbers, 4);
2355 print_debug('Serial Number length: ' || l_ser_num_length, 4);
2356 print_debug('Prefix length : ' || l_prefix_length, 4);
2357 END IF;
2358
2359 FOR i IN 1 .. l_range_numbers LOOP
2360 l_cur_ser_num := l_from_ser_number + i -1;
2361 l_cur_serial_number := l_serial_prefix ||
2362 LPAD(l_cur_ser_num, l_ser_num_length - NVL(l_prefix_length,0), '0');
2363 BEGIN
2364 SELECT 'Y'
2365 INTO l_serial_exists
2366 FROM mtl_serial_numbers
2367 WHERE inventory_item_id = p_inventory_item_id
2368 AND serial_number = l_cur_serial_number
2369 AND current_organization_id = p_org_id
2370 AND current_status IN (1,6);
2371 EXCEPTION
2372 WHEN OTHERS THEN
2373 IF (l_debug = 1) THEN
2374 print_debug('validate_serial_number: could not find the serial number: ' || l_cur_serial_number, 9);
2375 END IF;
2376 fnd_message.set_name('INV', 'INV_SER_NOTEXIST');
2377 fnd_message.set_token('TOKEN', l_cur_serial_number);
2378 fnd_msg_pub.add;
2379 x_return_status := FND_API.g_ret_sts_error;
2380 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
2381 RETURN FALSE;
2382 END;
2383 END LOOP; --END For each serial in the range
2384 END IF; --END IF g_exists_only
2385
2386 --If the validation mode is exists_or_create then call the validate_range_serial
2387 --API that will validate the serial number as well as create one if it does not exist
2388 IF p_validation_mode = INV_RCV_INTEGRATION_APIS.G_EXISTS_OR_CREATE THEN
2389 IF (l_debug = 1) THEN
2390 print_debug('validate_lot_number: calling inv_serial_number_pub.validate_serials to validate/create the serials', 9);
2391 END IF;
2392 --Bug 8413853 To Validate the Marked Serials added Parameter p_check_for_grp_mark_id
2393 IF(p_to_serial_number is not null) THEN
2394 l_to_serial_number:=p_to_serial_number;
2395 END IF;
2396 l_return := inv_serial_number_pub.validate_serials(
2397 p_org_id => p_org_id
2398 , p_item_id => p_inventory_item_id
2399 , p_qty => l_serial_qty
2400 , p_rev => p_revision
2401 , p_lot => p_lot_number
2402 , p_start_ser => p_fm_serial_number
2403 , p_check_for_grp_mark_id =>'Y'
2404 , p_trx_src_id => NULL
2405 , p_trx_action_id => NULL
2406 , x_end_ser => p_to_serial_number
2407 , x_proc_msg => x_msg_data);
2408
2409 --Set the error message and raise in case of a validation failure
2410 --Bug 8413853 Since we are caling this from ISP if any serial in the range
2411 -- has marked then we should throw error. So, we are just comparing the
2412 -- passed to serial is equal to returned to serial.
2413 IF(l_to_serial_number<>p_to_serial_number) then
2414 l_return:=1;
2415 End IF;
2416 IF l_return = 1 THEN
2417 IF (l_debug = 1) THEN
2418 print_debug('validate_serial_range: Error returned by validate_serials', 9);
2419 END IF;
2420 fnd_message.set_name('INV', 'INVALID_SERIAL_NUMBER');
2421 fnd_msg_pub.ADD;
2422 x_return_status := FND_API.g_ret_sts_error;
2423 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
2424 RETURN FALSE;
2425 END IF;
2426 END IF; --END IF g_exists_or_create
2427
2428 --All the validations have passed return TRUE
2429 RETURN TRUE;
2430
2431 --This API should be getting called only for a ship transaction from iSP.
2432 ELSE
2433 fnd_message.set_name('INV','INV_NOT_IMPLEMENTED');
2434 fnd_msg_pub.add;
2435 x_return_status := FND_API.g_ret_sts_error;
2436 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
2437 RETURN FALSE;
2438 END IF; --END IF Check the transaction type
2439
2440 EXCEPTION
2441 WHEN OTHERS THEN
2442 IF (l_debug = 1) THEN
2443 print_debug('validate_serial_range: Exception occurred in validate_serial_range', 3);
2444 END IF;
2445 x_return_status := FND_API.g_ret_sts_error;
2446 fnd_msg_pub.count_and_get(
2447 p_encoded => fnd_api.g_false
2448 , p_count => x_msg_count
2449 , p_data => x_msg_data);
2450 IF SQLCODE IS NOT NULL THEN
2451 inv_mobile_helper_functions.sql_error(
2452 'INV_RCV_INTEGRATION_APIS.VALIDATE_SERIAL_RANGE', SQLCODE, SQLERRM);
2453 END IF;
2454 RETURN FALSE;
2455 END validate_serial_range;
2456
2457 function validate_lot_serial_info
2458 (p_api_version IN NUMBER
2459 , p_init_msg_lst IN VARCHAR2
2460 , x_return_status OUT NOCOPY VARCHAR2
2461 , x_msg_count OUT NOCOPY NUMBER
2462 , x_msg_data OUT NOCOPY VARCHAR2
2463 , p_validation_mode IN NUMBER
2464 , p_rti_id IN NUMBER
2465 ) return BOOLEAN
2466 IS
2467 l_exist NUMBER;
2468 l_org_id NUMBER;
2469 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2470 BEGIN
2471 x_return_status := g_ret_sts_success;
2472
2473 -- If not patchsetJ then return from this place.
2474 IF (inv_rcv_common_apis.g_inv_patch_level < inv_rcv_common_apis.g_patchset_j) THEN
2475 IF (l_debug = 1) THEN
2476 print_debug('validate_lot_serial_info Return from the API call AS not pset J or below pset J level', 4);
2477 END IF;
2478 return TRUE ;
2479 END IF;
2480
2481 /** OPM change Bug# 3061052**/
2482 -- removed the check to error out in case of OPM transaction.
2483
2484 --call the lot/serial validation procedure
2485
2486 inv_rcv_integration_pvt.validate_lot_serial_info(P_RTI_ID => P_RTI_ID,
2487 X_RETURN_STATUS => X_RETURN_STATUS,
2488 X_MSG_COUNT => X_MSG_COUNT,
2489 X_MSG_DATA => X_MSG_DATA);
2490
2491 IF (x_return_status <> g_ret_sts_success) THEN
2492
2493 /*INVCONV */
2494 IF (l_debug = 1) THEN
2495 print_debug('validate_lot_serial_info: private API new debug', 4);
2496 END IF;
2497 /*end , INVCONV*/
2498
2499 x_msg_data := x_msg_data||':'||fnd_message.get_string('INV','INV_LOT_SERIAL_VALIDATION_FAIL');
2500 fnd_message.set_name('INV','INV_LOT_SERIAL_VALIDATION_FAIL');
2501 fnd_msg_pub.ADD;
2502 RETURN FALSE;
2503 END IF;
2504
2505 RETURN TRUE;
2506 EXCEPTION
2507 WHEN OTHERS THEN
2508 IF (l_debug = 1) THEN
2509 print_debug('validate_lot_serial_info: private API throws exception', 4);
2510 END IF;
2511 x_return_status := g_ret_sts_error;
2512 RETURN FALSE;
2513 END validate_lot_serial_info;
2514
2515 function generate_lot_number
2516 (p_api_version IN NUMBER
2517 , p_init_msg_lst IN VARCHAR2
2518 , p_commit IN VARCHAR2
2519 , x_return_status OUT NOCOPY VARCHAR2
2520 , x_msg_count OUT NOCOPY NUMBER
2521 , x_msg_data OUT NOCOPY VARCHAR2
2522 , p_org_id IN NUMBER
2523 , p_inventory_item_id IN NUMBER
2524 ) return VARCHAR2
2525 IS
2526 BEGIN
2527 x_return_status := g_ret_sts_success;
2528
2529 x_return_status := g_ret_sts_error;
2530 fnd_message.set_name('INV','INV_NOT_IMPLEMENTED');
2531 fnd_msg_pub.add;
2532 fnd_msg_pub.count_and_get
2533 ( p_count => x_msg_count
2534 , p_data => x_msg_data
2535 );
2536 RETURN NULL;
2537 END generate_lot_number;
2538
2539 procedure generate_serial_numbers
2540 (p_api_version IN NUMBER
2541 , p_init_msg_lst IN VARCHAR2 DEFAULT g_false
2542 , p_commit IN VARCHAR2 DEFAULT g_false
2543 , x_return_status OUT NOCOPY VARCHAR2
2544 , x_msg_count OUT NOCOPY NUMBER
2545 , x_msg_data OUT NOCOPY VARCHAR2
2546 , p_org_id IN NUMBER
2547 , p_inventory_item_id IN NUMBER
2548 , p_quantity IN NUMBER
2549 , p_revision IN VARCHAR2
2550 , p_lot_number IN VARCHAR2
2551 , x_start_serial OUT NOCOPY VARCHAR2
2552 , x_end_serial OUT NOCOPY VARCHAR2
2553 ) IS
2554 BEGIN
2555 x_return_status := g_ret_sts_success;
2556
2557 x_return_status := g_ret_sts_error;
2558 fnd_message.set_name('INV','INV_NOT_IMPLEMENTED');
2559 fnd_msg_pub.add;
2560 fnd_msg_pub.count_and_get
2561 ( p_count => x_msg_count
2562 , p_data => x_msg_data
2563 );
2564 END generate_serial_numbers;
2565
2566 function validate_lpn
2567 (p_api_version IN NUMBER
2568 , p_init_msg_lst IN VARCHAR2 DEFAULT g_false
2569 , x_return_status OUT NOCOPY VARCHAR2
2570 , x_msg_count OUT NOCOPY NUMBER
2571 , x_msg_data OUT NOCOPY VARCHAR2
2572 , p_validation_mode IN NUMBER DEFAULT G_EXISTS_ONLY
2573 , p_org_id IN NUMBER
2574 , p_lpn_id IN OUT NOCOPY NUMBER
2575 , p_lpn IN VARCHAR2
2576 , p_parent_lpn_id IN NUMBER DEFAULT NULL
2577 ) return BOOLEAN
2578 IS
2579 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2580 l_exists NUMBER;
2581 l_lpn_id NUMBER;
2582 BEGIN
2583 x_return_status := g_ret_sts_success;
2584
2585 fnd_msg_pub.initialize;
2586
2587 IF (p_validation_mode = g_exists_only OR p_validation_mode = g_exists_or_create) THEN
2588 BEGIN
2589 select 1, lpn_id
2590 into l_exists, l_lpn_id
2591 from wms_license_plate_numbers wlpn
2592 where wlpn.organization_id = nvl(p_org_id, wlpn.organization_id )
2593 and wlpn.license_plate_number = p_lpn
2594 and wlpn.lpn_id = nvl(p_lpn_id, wlpn.lpn_id)
2595 and ( (p_parent_lpn_id is null ) or ( wlpn.parent_lpn_id = p_parent_lpn_id and p_parent_lpn_id is not null ))
2596 and rownum = 1;
2597 IF p_lpn_id IS NULL THEN
2598 p_lpn_id := l_lpn_id;
2599 END IF;
2600 RETURN TRUE;
2601 EXCEPTION
2602 WHEN no_data_found THEN
2603 IF p_validation_mode = g_exists_only THEN
2604 IF (l_debug = 1) THEN
2605 print_debug( 'This is an invalid lpn => ' || ' lpn = '||p_lpn ||' lpn_id= '||p_lpn_id,1 );
2606 END IF;
2607 x_return_status := g_ret_sts_error;
2608 fnd_message.set_name('WMS', 'WMS_CONT_INVALID_LPN');
2609 fnd_msg_pub.ADD;
2610 fnd_msg_pub.count_and_get
2611 ( p_count => x_msg_count
2612 , p_data => x_msg_data
2613 );
2614 RETURN FALSE;
2615 ELSE -- IF p_validation_mode = g_exists_only THEN
2616 -- Call Container API to create LPN
2617
2618 -- Bug 5461966: Pass p_api_version as number
2619 wms_container_pvt.create_lpn(
2620 p_api_version => 1.0,
2621 p_init_msg_list => g_false,
2622 p_commit => g_false,
2623 p_validation_level => fnd_api.g_valid_level_full,
2624 x_return_status => x_return_status,
2625 x_msg_count => x_msg_count,
2626 x_msg_data => x_msg_data,
2627 p_lpn => p_lpn,
2628 p_organization_id => p_org_id,
2629 x_lpn_id => l_lpn_id );
2630 if x_return_status <> G_RET_STS_SUCCESS Then
2631 x_return_status := g_ret_sts_error;
2632 IF (l_debug = 1) THEN
2633 print_debug( 'Error creating lpn => '||'lpn = '||p_lpn ||'lpn_id= '||p_lpn_id,1 );
2634 END IF;
2635 fnd_message.set_name('WMS', 'WMS_LPN_NOTGEN');
2636 fnd_msg_pub.ADD;
2637 fnd_msg_pub.count_and_get
2638 ( p_count => x_msg_count
2639 , p_data => x_msg_data
2640 );
2641 RETURN FALSE;
2642 End if;
2643 p_lpn_id := l_lpn_id;
2644 RETURN TRUE;
2645 END IF;-- IF p_validation_mode = g_exists_only THEN
2646
2647 WHEN OTHERS THEN
2648 IF (l_debug = 1) THEN
2649 print_debug( 'This is an invalid lpn => ' || ' lpn = '||p_lpn ||' lpn_id= '||p_lpn_id,1 );
2650 END IF;
2651 x_return_status := g_ret_sts_error;
2652 fnd_message.set_name('WMS', 'WMS_CONT_INVALID_LPN');
2653 fnd_msg_pub.ADD;
2654 fnd_msg_pub.count_and_get
2655 ( p_count => x_msg_count
2656 , p_data => x_msg_data
2657 );
2658 RETURN FALSE;
2659 END;
2660 ELSE
2661 x_return_status := g_ret_sts_error;
2662 fnd_message.set_name('INV','INV_NOT_IMPLEMENTED');
2663 fnd_msg_pub.add;
2664 fnd_msg_pub.count_and_get
2665 ( p_count => x_msg_count
2666 , p_data => x_msg_data
2667 );
2668 RETURN FALSE;
2669 END IF;
2670 END validate_lpn;
2671
2672 function validate_lpn_info
2673 (p_api_version IN NUMBER DEFAULT 1.0
2674 , p_init_msg_lst IN VARCHAR2 DEFAULT g_false
2675 , x_return_status OUT NOCOPY VARCHAR2
2676 , x_msg_count OUT NOCOPY NUMBER
2677 , x_msg_data OUT NOCOPY VARCHAR2
2678 , p_validation_mode IN NUMBER DEFAULT G_EXISTS_OR_CREATE
2679 , p_lpn_group_id IN NUMBER
2680 ) return BOOLEAN
2681 IS
2682 l_exist NUMBER;
2683 l_org_id NUMBER;
2684
2685 l_api_name VARCHAR2(30) := 'VALIDATE_LPN_INFO';
2686 l_api_version CONSTANT NUMBER := 1.0;
2687
2688 l_progress VARCHAR2(10) := '00';
2689 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2690 BEGIN
2691 x_return_status := g_ret_sts_success;
2692
2693 l_progress := '10';
2694
2695 IF (l_debug = 1) THEN
2696 print_debug('Inside VALIDATE_LPN_INFO ... LPN_GROUP_ID:'||p_lpn_group_id,1);
2697 END IF;
2698
2699 -- If not patchsetJ then return from this place.
2700 IF (inv_rcv_common_apis.g_inv_patch_level < inv_rcv_common_apis.g_patchset_j) THEN
2701 IF (l_debug = 1) THEN
2702 print_debug('VALIDATE_LPN_INFO: Return from the API call As not pset J or below pset J level', 4);
2703 END IF;
2704 return TRUE ;
2705 END IF;
2706
2707 --l_progress := '20';
2708
2709 /*INVCONV , This currently restricts OPM transaction and calls
2710 inv_rcv_integration_pvt.validate_lpn_info for validating discrete transactions
2711 Remove process specific checks.Remove restriction for OPM transaction.
2712 Punit Kumar.
2713 */
2714
2715 /*
2716 -- get the org_id and item_id from rti to check if OPM transaction
2717 BEGIN
2718 SELECT DISTINCT(to_organization_id)
2719 INTO l_org_id
2720 FROM rcv_transactions_interface
2721 WHERE lpn_group_id = p_lpn_group_id;
2722 EXCEPTION
2723 WHEN OTHERS THEN
2724 NULL;
2725 END;
2726
2727 l_progress := '30';
2728
2729 -- check if this is a OPM transaction. If it is a OPM transaction then
2730 -- error out. This API should not be called for a OPM transaction.
2731 IF gml_process_flags.check_process_orgn(l_org_id) = 1 THEN
2732 x_return_status := g_ret_sts_error;
2733 fnd_message.set_name('INV','INV_NOT_IMPLEMENTED');
2734 fnd_msg_pub.add;
2735 fnd_msg_pub.count_and_get
2736 ( p_count => x_msg_count
2737 , p_data => x_msg_data
2738 );
2739 RETURN FALSE;
2740 END IF;
2741 */
2742 print_debug('Inside VALIDATE_LPN_INFO ... LPN_GROUP_ID:'||p_lpn_group_id,1);
2743 l_progress := '40';
2744
2745 IF (l_debug = 1) THEN
2746 print_debug('INVCONV,Remove process specific checks.Remove restriction for OPM transaction.'||l_progress, 4);
2747 END IF;
2748 /*end , INVCONV */
2749
2750 -- Standard call to check for call compatibility.
2751 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version,
2752 l_api_name, 'inv_rcv_integration_apis') THEN
2753 print_debug('FND_API not compatible inv_rcv_integration_apis.validate_lpn_info', 4);
2754 RAISE fnd_api.g_exc_unexpected_error;
2755 END IF;
2756
2757 l_progress := '50';
2758
2759 -- Initialize message list if p_init_msg_list is set to TRUE.
2760 IF fnd_api.to_boolean(p_init_msg_lst) THEN
2761 fnd_msg_pub.initialize;
2762 END IF;
2763
2764 l_progress := '60';
2765
2766 inv_rcv_integration_pvt.validate_lpn_info(p_lpn_group_id => p_lpn_group_id,
2767 x_return_status => x_return_status,
2768 x_msg_count => x_msg_count,
2769 x_msg_data => x_msg_data);
2770
2771 IF (l_debug = 1) THEN
2772 print_debug('VALIDATE_LPN_INFO: x_return_status from private api: '||x_return_status,1);
2773 END IF;
2774
2775 IF (x_return_status <> g_ret_sts_success) THEN
2776 l_progress := '70';
2777 x_msg_data := x_msg_data||':'||fnd_message.get_string('INV','INV_LPN_VALIDATION_FAILED');
2778 IF (l_debug = 1) THEN
2779 print_debug('VALIDATE_LPN_INFO: x_msg_data: '||x_msg_data,1);
2780 END IF;
2781 fnd_message.set_name('INV','INV_LPN_VALIDATION_FAILED');
2782 fnd_msg_pub.ADD;
2783 RETURN FALSE;
2784 END IF;
2785
2786 l_progress := '80';
2787
2788 RETURN TRUE;
2789 EXCEPTION
2790 WHEN OTHERS THEN
2791 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2792
2793 IF SQLCODE IS NOT NULL THEN
2794 inv_mobile_helper_functions.sql_error (
2795 'INV_RCV_INTEGRATION_APIS.VALIDATE_LPN_INFO',
2796 l_progress,
2797 SQLCODE);
2798 END IF;
2799 RETURN FALSE;
2800 END validate_lpn_info;
2801
2802 procedure generate_lpn
2803 (p_api_version IN NUMBER
2804 , p_init_msg_lst IN VARCHAR2
2805 , p_commit IN VARCHAR2
2806 , x_return_status OUT NOCOPY VARCHAR2
2807 , x_msg_count OUT NOCOPY NUMBER
2808 , x_msg_data OUT NOCOPY VARCHAR2
2809 , p_lpn_id OUT NOCOPY NUMBER
2810 , p_lpn OUT NOCOPY VARCHAR2
2811 , p_organization_id IN NUMBER
2812 ) IS
2813 BEGIN
2814 x_return_status := g_ret_sts_success;
2815
2816 x_return_status := g_ret_sts_error;
2817 fnd_message.set_name('INV','INV_NOT_IMPLEMENTED');
2818 fnd_msg_pub.add;
2819 fnd_msg_pub.count_and_get
2820 ( p_count => x_msg_count
2821 , p_data => x_msg_data
2822 );
2823 END generate_lpn;
2824
2825
2826 procedure explode_lpn
2827 (p_api_version IN NUMBER
2828 , p_init_msg_lst IN VARCHAR2
2829 , x_return_status OUT NOCOPY VARCHAR2
2830 , x_msg_count OUT NOCOPY NUMBER
2831 , x_msg_data OUT NOCOPY VARCHAR2
2832 , p_group_id IN NUMBER
2833 , p_request_id IN NUMBER
2834 ) IS
2835 l_exist NUMBER;
2836 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2837 BEGIN
2838 x_return_status := g_ret_sts_success;
2839
2840 /*
2841 BEGIN
2842 SELECT 1
2843 INTO l_exist
2844 FROM dual
2845 WHERE exists (SELECT 1
2846 FROM rcv_transactions_interface
2847 WHERE group_id = p_group_id
2848 AND item_id is null
2849 AND item_description is null
2850 AND (lpn_id is not null
2851 OR license_plate_number is not null
2852 OR transfer_lpn_id is not null
2853 OR transfer_license_plate_number is not null)
2854 );
2855 x_return_status := g_ret_sts_error;
2856 fnd_message.set_name('INV','INV_NOT_IMPLEMENTED');
2857 fnd_msg_pub.add;
2858 fnd_msg_pub.count_and_get
2859 ( p_count => x_msg_count
2860 , p_data => x_msg_data
2861 );
2862 EXCEPTION
2863 when NO_DATA_FOUND then
2864 x_return_status := g_ret_sts_success;
2865 when OTHERS then
2866 x_return_status := g_ret_sts_error;
2867 fnd_message.set_name('INV','INV_NOT_IMPLEMENTED');
2868 fnd_msg_pub.add;
2869 fnd_msg_pub.count_and_get
2870 ( p_count => x_msg_count
2871 , p_data => x_msg_data
2872 );
2873 END;
2874 */
2875 -- l_exist := 0;
2876 -- SELECT 1
2877 -- into l_exist
2878 -- FROM rcv_transactions_interface
2879 -- WHERE group_id = p_group_id ;
2880 -- print_debug('group_id =' || p_group_id, 4);
2881 -- print_debug('exist =' || l_exist, 4);
2882
2883 -- If not patchsetJ then return from this place.
2884 IF (inv_rcv_common_apis.g_inv_patch_level < inv_rcv_common_apis.g_patchset_j) THEN
2885 IF (l_debug = 1) THEN
2886 print_debug('Explode_lpn Return from the API call AS not pset J or below pset J level', 4);
2887 END IF;
2888 return ;
2889 END IF;
2890
2891 -- Call the Private API
2892 inv_rcv_integration_pvt.explode_lpn(p_request_id,p_group_id);
2893
2894 END explode_lpn;
2895
2896 /** validates subinventory and locator type called from TM for ROI trxns
2897 * Check for rti records for current rti id or group id with wrong transaction type
2898 * and subinventory/locator type combinations
2899 * if group id is passed then we have to check through a cursor all rtis for
2900 * that group id. if rti id is passed , we are sure that it is only one record
2901 * in which case we use a select statement directly
2902 */
2903 PROCEDURE validate_sub_loc(
2904 p_api_version IN NUMBER
2905 , p_init_msg_lst IN VARCHAR2
2906 , x_return_status OUT NOCOPY VARCHAR2
2907 , x_msg_count OUT NOCOPY NUMBER
2908 , x_msg_data OUT NOCOPY VARCHAR2
2909 , p_group_id IN NUMBER
2910 , p_request_id IN NUMBER
2911 , p_rti_id IN NUMBER
2912 , p_validation_mode IN NUMBER
2913 ) IS
2914 l_exist NUMBER;
2915 l_sub VARCHAR2(10);
2916 l_locator_id NUMBER;
2917 l_trx_type VARCHAR2(30);
2918 l_org_id NUMBER;
2919 l_sub_type NUMBER;
2920 l_lpn_cont_flag NUMBER;
2921 l_loc_type NUMBER;
2922 l_lpn_id NUMBER;
2923 l_lpn_num VARCHAR2(30);
2924 l_auto_transact_code VARCHAR2(30);
2925 l_location_id NUMBER;
2926
2927 --BUG 3633752: Break up the c_rti_sub_check cursor. This is made
2928 --to improve performance
2929 CURSOR c_rti_sub_check_grp_intf_id IS
2930 SELECT subinventory
2931 , locator_id
2932 , transaction_type
2933 , to_organization_id
2934 , NVL(auto_transact_code, '@@@') auto_transact_code
2935 , transfer_lpn_id
2936 , transfer_license_plate_number
2937 , location_id
2938 FROM rcv_transactions_interface rti
2939 WHERE GROUP_ID = p_group_id
2940 AND interface_transaction_id = p_rti_id;
2941
2942 CURSOR c_rti_sub_check_grp_id IS
2943 SELECT subinventory
2944 , locator_id
2945 , transaction_type
2946 , to_organization_id
2947 , NVL(auto_transact_code, '@@@') auto_transact_code
2948 , transfer_lpn_id
2949 , transfer_license_plate_number
2950 , location_id
2951 FROM rcv_transactions_interface rti
2952 WHERE GROUP_ID = p_group_id;
2953
2954 CURSOR c_rti_sub_check_intf_id IS
2955 SELECT subinventory
2956 , locator_id
2957 , transaction_type
2958 , to_organization_id
2959 , NVL(auto_transact_code, '@@@') auto_transact_code
2960 , transfer_lpn_id
2961 , transfer_license_plate_number
2962 , location_id
2963 FROM rcv_transactions_interface rti
2964 WHERE interface_transaction_id = p_rti_id;
2965
2966 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
2967 BEGIN
2968 x_return_status := g_ret_sts_success;
2969
2970 IF (l_debug = 1) THEN
2971 print_debug('VALIDATE_SUB_LOC 10: Entered with values ...', 1);
2972 print_debug('VALIDATE_SUB_LOC 10: ...RTID :' || p_rti_id, 1);
2973 print_debug('VALIDATE_SUB_LOC 10: ...GID :' || p_group_id, 1);
2974 print_debug('VALIDATE_SUB_LOC 10: ...REQID :' || p_request_id, 1);
2975 END IF;
2976
2977 IF (p_group_id IS NOT NULL) THEN
2978 IF (p_rti_id IS NOT NULL) THEN
2979 OPEN c_rti_sub_check_grp_intf_id;
2980 ELSE
2981 OPEN c_rti_sub_check_grp_id;
2982 END IF;
2983 ELSE
2984 IF (p_rti_id IS NOT NULL) THEN
2985 OPEN c_rti_sub_check_intf_id;
2986 ELSE
2987 print_debug('VALIDATE_SUB_LOC 15: RTI and GID cannott both be null', 1);
2988 END IF;
2989 END IF;
2990
2991 LOOP
2992 IF (p_group_id IS NOT NULL) THEN
2993 IF (p_rti_id IS NOT NULL) THEN
2994 FETCH c_rti_sub_check_grp_intf_id
2995 INTO l_sub, l_locator_id, l_trx_type, l_org_id,
2996 l_auto_transact_code, l_lpn_id, l_lpn_num, l_location_id;
2997 EXIT WHEN c_rti_sub_check_grp_intf_id%NOTFOUND;
2998 ELSE
2999 FETCH c_rti_sub_check_grp_id
3000 INTO l_sub, l_locator_id, l_trx_type, l_org_id,
3001 l_auto_transact_code, l_lpn_id, l_lpn_num, l_location_id;
3002 EXIT WHEN c_rti_sub_check_grp_id%NOTFOUND;
3003 END IF;
3004 ELSE
3005 IF (p_rti_id IS NOT NULL) THEN
3006 FETCH c_rti_sub_check_intf_id
3007 INTO l_sub, l_locator_id, l_trx_type, l_org_id,
3008 l_auto_transact_code, l_lpn_id, l_lpn_num, l_location_id;
3009 EXIT WHEN c_rti_sub_check_intf_id%NOTFOUND;
3010 ELSE
3011 print_debug('VALIDATE_SUB_LOC 15: RTI and GID cannott both be null', 1);
3012 END IF;
3013 END IF;
3014
3015 IF l_sub IS NOT NULL THEN
3016 SELECT NVL(subinventory_type, 1), Nvl(lpn_controlled_flag, 2)
3017 INTO l_sub_type, l_lpn_cont_flag
3018 FROM mtl_secondary_inventories msi
3019 WHERE secondary_inventory_name = l_sub
3020 AND organization_id = l_org_id;
3021
3022 IF (l_sub_type = 2
3023 AND(l_trx_type = 'DELIVER'
3024 OR l_auto_transact_code = 'DELIVER'))
3025 OR(l_sub_type = 1
3026 AND(l_trx_type IN
3027 ('RECEIVE','ACCEPT','REJECT','TRANSFER','RETURN TO RECEIVING')
3028 AND l_auto_transact_code <> 'DELIVER')) THEN
3029 x_return_status := g_ret_sts_error;
3030
3031 IF (l_debug = 1) THEN
3032 print_debug('VALIDATE_SUB_LOC 60: Invalid Subinventory Type for the transaction:' || l_sub_type || ':' || l_trx_type, 1);
3033 END IF;
3034
3035 fnd_message.set_name('INV', 'INV_INVALID_SUBINV');
3036 fnd_msg_pub.ADD;
3037 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
3038 RETURN;
3039 END IF; --IF (l_sub_type = 2
3040
3041 IF (l_lpn_cont_flag = 2
3042 AND (l_lpn_id IS NOT NULL
3043 OR l_lpn_num IS NOT NULL)
3044 AND (l_trx_type IN
3045 ('RECEIVE','ACCEPT','REJECT','TRANSFER','DELIVER','RETURN TO RECEIVING'))) THEN
3046 x_return_status := g_ret_sts_error;
3047
3048 IF (l_debug = 1) THEN
3049 print_debug('VALIDATE_SUB_LOC 70: Invalid LPN Controlled Flag for the transaction:' || l_lpn_cont_flag || ':' || l_lpn_id || ':' || l_lpn_num, 1);
3050 END IF;
3051
3052 fnd_message.set_name('INV', 'INV_INVALID_SUBINV');
3053 fnd_msg_pub.ADD;
3054 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
3055 RETURN;
3056 END IF; --IF (l_lpn_cont_flag = 2
3057 END IF; --IF l_sub IS NOT NULL THEN
3058
3059 IF l_locator_id IS NOT NULL THEN
3060 SELECT NVL(l_loc_type, -1)
3061 INTO l_loc_type
3062 FROM mtl_item_locations mil
3063 WHERE inventory_location_id = l_locator_id
3064 AND organization_id = l_org_id;
3065
3066 IF (l_loc_type = 3 AND
3067 (
3068 l_trx_type IN('RECEIVE','TRANSFER','ACCEPT',
3069 'REJECT','RETURN TO RECEIVING')
3070 AND l_auto_transact_code <> 'DELIVER'
3071 )
3072 )
3073 OR(l_loc_type IN(6, 7)
3074 AND(l_trx_type = 'DELIVER'
3075 OR l_auto_transact_code = 'DELIVER')) THEN
3076 x_return_status := g_ret_sts_error;
3077
3078 IF (l_debug = 1) THEN
3079 print_debug('VALIDATE_SUB_LOC 80: Invalid Locator Type for the transaction:' || l_loc_type || ':' || l_trx_type, 1);
3080 END IF;
3081 fnd_message.set_name('INV', 'INV_INT_LOCCODE');
3082 fnd_msg_pub.ADD;
3083 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
3084 RETURN;
3085 END IF;
3086 END IF; --IF l_locator_id IS NOT NULL THEN
3087
3088 IF l_sub IS NULL AND l_location_id IS NOT NULL THEN
3089 BEGIN
3090 IF (l_lpn_id is NOT NULL) THEN
3091 --BUG 3633752: Break up the query below. This is made
3092 --to improve performance
3093 SELECT 1
3094 INTO l_exist
3095 FROM dual
3096 WHERE exists (SELECT '1'
3097 FROM rcv_supply rs, wms_license_plate_numbers wlpn
3098 WHERE wlpn.lpn_id = l_lpn_id
3099 AND wlpn.lpn_id = rs.lpn_id
3100 AND rs.location_id <> l_location_id);
3101 ELSE
3102 SELECT 1
3103 INTO l_exist
3104 FROM dual
3105 WHERE exists (SELECT '1'
3106 FROM rcv_supply rs, wms_license_plate_numbers wlpn
3107 WHERE wlpn.license_plate_number = l_lpn_num
3108 AND wlpn.lpn_id = rs.lpn_id
3109 AND rs.location_id <> l_location_id);
3110 END IF;
3111
3112 --error
3113 IF (l_debug = 1) THEN
3114 print_debug('VALIDATE_SUB_LOC 90: Invalid Location for the transaction:' || l_location_id || ':' || l_trx_type, 1);
3115 END IF;
3116 fnd_message.set_name('INV', 'INV_INVALID_LOCATION');
3117 fnd_msg_pub.ADD;
3118 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
3119 RETURN;
3120 EXCEPTION
3121 WHEN no_data_found THEN
3122 IF (l_debug = 1) THEN
3123 print_debug('VALIDATE_SUB_LOC 100: Valid Location for the transaction:' || l_location_id || ':' || l_trx_type, 1);
3124 END IF;
3125 x_return_status := g_ret_sts_success;
3126 WHEN OTHERS THEN
3127 IF (l_debug = 1) THEN
3128 print_debug('VALIDATE_SUB_LOC 110: Invalid Location for the transaction:' || l_location_id || ':' || l_trx_type, 1);
3129 END IF;
3130 fnd_message.set_name('INV', 'INV_INVALID_LOCATION');
3131 fnd_msg_pub.ADD;
3132 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
3133 RETURN;
3134 END ;
3135 END IF; --IF (l_sub IS NULL AND l_location_id IS NOT NULL THEN
3136 END LOOP;
3137
3138 IF (c_rti_sub_check_grp_intf_id%isopen) THEN
3139 CLOSE c_rti_sub_check_grp_intf_id;
3140 END IF;
3141
3142 IF (c_rti_sub_check_grp_id%isopen) THEN
3143 CLOSE c_rti_sub_check_grp_id;
3144 END IF;
3145
3146 IF (c_rti_sub_check_intf_id%isopen) THEN
3147 CLOSE c_rti_sub_check_grp_id;
3148 END IF;
3149
3150 -- END IF;
3151 IF (l_debug = 1) THEN
3152 print_debug('VALIDATE_SUB_LOC 120: Exitting Successfully...', 4);
3153 END IF;
3154
3155 EXCEPTION
3156 WHEN OTHERS THEN
3157 IF (l_debug = 1) THEN
3158 print_debug('VALIDATE_SUB_LOC: Expected exception occured...', 4);
3159 END IF;
3160
3161 IF (c_rti_sub_check_grp_intf_id%isopen) THEN
3162 CLOSE c_rti_sub_check_grp_intf_id;
3163 END IF;
3164
3165 IF (c_rti_sub_check_grp_id%isopen) THEN
3166 CLOSE c_rti_sub_check_grp_id;
3167 END IF;
3168
3169 IF (c_rti_sub_check_intf_id%isopen) THEN
3170 CLOSE c_rti_sub_check_grp_id;
3171 END IF;
3172 END validate_sub_loc;
3173
3174
3175 function split_lot_serial
3176 (p_api_version IN NUMBER DEFAULT 1.0
3177 , p_init_msg_lst IN VARCHAR2 DEFAULT g_false
3178 , x_return_status OUT NOCOPY VARCHAR2
3179 , x_msg_count OUT NOCOPY NUMBER
3180 , x_msg_data OUT NOCOPY VARCHAR2
3181 , p_new_rti_info IN inv_rcv_integration_apis.child_rec_tb_tp
3182 ) return BOOLEAN
3183 IS
3184 l_exist NUMBER;
3185 l_rti_id NUMBER;
3186
3187 l_api_name VARCHAR2(30) := 'SPLIT_LOT_SERIAL';
3188 l_api_version CONSTANT NUMBER := 1.0;
3189 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3190 BEGIN
3191 x_return_status := g_ret_sts_success;
3192
3193 -- Standard call to check for call compatibility.
3194 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version,
3195 l_api_name, 'inv_rcv_integration_apis') THEN
3196 IF (l_debug = 1) THEN
3197 print_debug('FND_API not compatible inv_rcv_integration_apis.split_lot_serial', 4);
3198 END IF;
3199 RAISE fnd_api.g_exc_unexpected_error;
3200 END IF;
3201
3202 -- Initialize message list if p_init_msg_list is set to TRUE.
3203 IF fnd_api.to_boolean(p_init_msg_lst) THEN
3204 fnd_msg_pub.initialize;
3205 END IF;
3206
3207 inv_rcv_integration_pvt.split_lot_serial(p_rti_tb => p_new_rti_info,
3208 x_return_status => x_return_status,
3209 x_msg_count => x_msg_count,
3210 x_msg_data => x_msg_data);
3211 IF (x_return_status <> g_ret_sts_success) THEN
3212 IF (l_debug = 1) THEN
3213 print_debug('inv_rcv_intergration_pvt.split_lot_serial returned error',4);
3214 END IF;
3215 RETURN FALSE;
3216 END IF;
3217
3218 IF (l_debug = 1) THEN
3219 print_debug('inv_rcv_integration_pvt.split_lot_serial returned success',4);
3220 END IF;
3221
3222 RETURN TRUE;
3223 END split_lot_serial;
3224
3225 function process_transaction
3226 (p_api_version IN NUMBER
3227 , p_init_msg_lst IN VARCHAR2
3228 , x_return_status OUT NOCOPY VARCHAR2
3229 , x_msg_count OUT NOCOPY NUMBER
3230 , x_msg_data OUT NOCOPY VARCHAR2
3231 , p_rti_id IN NUMBER
3232 ) return BOOLEAN
3233 IS
3234 l_org_id NUMBER;
3235 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3236 BEGIN
3237 x_return_status := g_ret_sts_success;
3238
3239 IF (l_debug = 1) THEN
3240 print_debug ('Inside Process Transaction ...',1);
3241 END IF;
3242
3243 /* INVCONV, This Procedure is currentlyt being called only by a discrete organization.
3244 Now on it shall be called for Process organization too
3245 It restricts the OPM transaction (through org_id) This needs to be removed.
3246 Punit Kumar*/
3247
3248 /*
3249 -- get the org_id and item_id from rti to check if OPM transaction
3250
3251 BEGIN
3252 SELECT to_organization_id
3253 INTO l_org_id
3254 FROM rcv_transactions_interface
3255 WHERE interface_transaction_id = p_rti_id;
3256 EXCEPTION
3257 WHEN OTHERS THEN
3258 NULL;
3259 END;
3260
3261 -- check if this is a OPM transaction. If it is a OPM transaction then
3262 -- error out. This API should not be called for a OPM transaction.
3263 IF gml_process_flags.check_process_orgn(l_org_id) = 1 THEN
3264 x_return_status := g_ret_sts_error;
3265 fnd_message.set_name('INV','INV_NOT_IMPLEMENTED');
3266 fnd_msg_pub.add;
3267 fnd_msg_pub.count_and_get
3268 ( p_count => x_msg_count
3269 , p_data => x_msg_data
3270 );
3271 RETURN FALSE;
3272 END IF;
3273 */
3274 /*end , INVCONV */
3275
3276 -- If not patchsetJ then return from this place.
3277 IF (inv_rcv_common_apis.g_inv_patch_level < inv_rcv_common_apis.g_patchset_j) THEN
3278 IF (l_debug = 1) THEN
3279 print_debug('process_txn: Return from the API call AS not pset J or below pset J level', 4);
3280 END IF;
3281 return TRUE ;
3282 END IF;
3283
3284 /*INVCONV*/
3285 IF (l_debug = 1) THEN
3286 print_debug('process_transaction restricts the OPM transaction (through org_id) This needs to be removed.', 4);
3287 END IF;
3288 /*end , INVCONV */
3289 --call the process_txn to process the receiving transaction
3290
3291 inv_rcv_integration_pvt.process_txn(p_txn_id => p_rti_id,
3292 x_return_status => x_return_status,
3293 x_msg_count => x_msg_count,
3294 x_msg_data => x_msg_data
3295 );
3296 IF (x_return_status <> g_ret_sts_success) THEN
3297 x_msg_data := x_msg_data||':'||fnd_message.get_string('INV','INV_FAILED');
3298 fnd_message.set_name('INV','INV_TRANSACTION_FAILED');
3299 fnd_msg_pub.ADD;
3300 RETURN FALSE;
3301 END IF;
3302
3303 RETURN TRUE;
3304 EXCEPTION
3305 WHEN OTHERS THEN
3306 IF (l_debug = 1) THEN
3307 print_debug('process_txn: private API throws exception', 4);
3308 END IF;
3309 x_return_status := g_ret_sts_error;
3310 RETURN FALSE;
3311 END process_transaction;
3312
3313 function complete_lpn_group
3314 (p_api_version IN NUMBER
3315 , p_init_msg_lst IN VARCHAR2
3316 , x_return_status OUT NOCOPY VARCHAR2
3317 , x_msg_count OUT NOCOPY NUMBER
3318 , x_msg_data OUT NOCOPY VARCHAR2
3319 , p_lpn_group_id IN NUMBER
3320 , p_group_id IN NUMBER
3321 , p_shipment_header_id IN NUMBER
3322 ) return BOOLEAN
3323 IS
3324
3325 CURSOR c_txn_types IS
3326 SELECT DISTINCT DECODE (rt.transaction_type,'ACCEPT','INSPECT',
3327 'REJECT','INSPECT',
3328 'DELIVER', DECODE (mp.wms_enabled_flag,'Y','PUTAWAY','DELIVER'),
3329 transaction_type) transaction_type
3330 FROM rcv_transactions rt,
3331 mtl_parameters mp
3332 WHERE rt.group_id = p_group_id
3333 AND rt.organization_id = mp.organization_id;
3334
3335 CURSOR c_pregen_cursor IS
3336 SELECT DISTINCT rt.transfer_lpn_id
3337 , rt.organization_id
3338 FROM rcv_transactions rt
3339 , mtl_parameters mp
3340 , mtl_system_items_kfv msi
3341 , rcv_supply rs
3342 WHERE mp.wms_enabled_flag = 'Y'
3343 AND mp.organization_id = rt.organization_id
3344 AND rt.transaction_type IN ('RECEIVE','ACCEPT','REJECT')
3345 AND rs.rcv_transaction_id = rt.transaction_id
3346 AND rs.supply_type_code = 'RECEIVING'
3347 AND rs.item_id = msi.inventory_item_id
3348 AND msi.organization_id = rt.organization_id
3349 AND rt.group_id = p_group_id
3350 AND rt.lpn_group_id = p_lpn_group_id
3351 AND ((msi.lot_control_code = 2
3352 AND exists (SELECT 1
3353 FROM rcv_lots_supply rsl
3354 WHERE rsl.transaction_id = rs.rcv_transaction_id)
3355 )
3356 OR
3357 (msi.lot_control_code = 1))
3358 AND ((msi.serial_number_control_code IN (2,5)
3359 AND exists (SELECT 1
3360 FROM rcv_serials_supply rss
3361 WHERE rss.transaction_id = rs.rcv_transaction_id)
3362 )
3363 OR
3364 (msi.serial_number_control_code IN (1, 6)))
3365 ORDER BY rt.transfer_lpn_id;
3366
3367 l_label_status VARCHAR2(500);
3368 l_bus_flow_code NUMBER;
3369 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3370 l_progress VARCHAR2(10) := '00';
3371 l_cd_count NUMBER;--bug 6412992, 6907475;
3372
3373 BEGIN
3374 x_return_status := g_ret_sts_success;
3375
3376 l_progress := '10';
3377
3378 IF (l_debug = 1) THEN
3379 print_debug('COMPLETE_LPN_GROUP - Entered...',1);
3380 print_debug('COMPLETE_LPN_GROUP - SHIPMENT_HEADER_ID:'||p_shipment_header_id,1);
3381 print_debug('COMPLETE_LPN_GROUP - LPN_GROUP_ID:'||p_lpn_group_id,1);
3382 print_debug('COMPLETE_LPN_GROUP - GROUP_ID:'||p_group_id,1);
3383 END IF;
3384
3385 l_progress := '20';
3386
3387 -- If not patchsetJ then return from this place.
3388 IF (inv_rcv_common_apis.g_inv_patch_level < inv_rcv_common_apis.g_patchset_j) THEN
3389 IF (l_debug = 1) THEN
3390 print_debug('Complete_lpn_group: Return from the API call AS not pset J or below pset J level', 4);
3391 END IF;
3392 return TRUE ;
3393 END IF;
3394
3395 IF (l_debug = 1) THEN
3396 print_debug('COMPLETE_LPN_GROUP - Calling label print api...',1);
3397 END IF;
3398
3399 IF (p_shipment_header_id IS NOT NULL) THEN
3400 inv_label.print_label_wrap(x_return_status => x_return_status
3401 , x_msg_count => x_msg_count
3402 , x_msg_data => x_msg_data
3403 , x_label_status => l_label_status
3404 , p_business_flow_code => inv_label.WMS_BF_IMPORT_ASN
3405 , p_transaction_id => p_shipment_header_id
3406 , p_transaction_identifier => inv_label.TRX_ID_RSH);
3407
3408 ELSIF (p_group_id IS NOT NULL AND p_lpn_group_id IS NULL) THEN
3409 FOR l_txn_types IN C_TXN_TYPES
3410 LOOP
3411 IF l_txn_types.transaction_type = 'RECEIVE' THEN
3412 L_BUS_FLOW_CODE := inv_label.WMS_BF_RECEIPT;
3413 ELSIF l_txn_types.transaction_type = 'INSPECT' THEN
3414 L_BUS_FLOW_CODE := inv_label.WMS_BF_INSPECTION;
3415 ELSIF l_txn_types.transaction_type = 'DELIVER' THEN
3416 L_BUS_FLOW_CODE := inv_label.WMS_BF_DELIVERY;
3417 ELSIF l_txn_types.transaction_type = 'PUTAWAY' THEN
3418 L_BUS_FLOW_CODE := inv_label.WMS_BF_PUTAWAY_DROP;
3419 END IF;
3420
3421
3422 IF l_txn_types.transaction_type <> 'PUTAWAY' THEN --BUG 6412992
3423 --CALL LABEL PRINTING API WITH L_BUS_FLOW_CODE
3424 inv_label.print_label_wrap(x_return_status => x_return_status
3425 , x_msg_count => x_msg_count
3426 , x_msg_data => x_msg_data
3427 , x_label_status => l_label_status
3428 , p_business_flow_code => l_bus_flow_code
3429 , p_transaction_id => p_group_id
3430 , p_transaction_identifier => inv_label.trx_id_rt);
3431 ELSE--bug 6412992
3432 BEGIN
3433 --bug 6907475 added the condition to check for context of rt's transfer lpn_id to stop putaway label
3434 --for sales order cross dock
3435 SELECT 1 into l_cd_count
3436 FROM rcv_transactions rt
3437 WHERE rt.group_id= p_group_id
3438 AND (rt.wip_entity_id IS NOT NULL
3439 OR(rt.transfer_lpn_id IS NOT NULL AND EXISTS
3440 (SELECT 1 FROM wms_license_plate_numbers wlpn
3441 WHERE wlpn.lpn_id = rt.transfer_lpn_id
3442 AND wlpn.lpn_context = 11)))
3443 AND ROWNUM =1;
3444 IF (l_debug = 1) THEN
3445 print_debug('Line is cross docked so no need to call for put away business flow',1);
3446 END IF;
3447 EXCEPTION
3448 WHEN NO_DATA_FOUND THEN
3449 inv_label.print_label_wrap(x_return_status => x_return_status
3450 , x_msg_count => x_msg_count
3451 , x_msg_data => x_msg_data
3452 , x_label_status => l_label_status
3453 , p_business_flow_code => l_bus_flow_code
3454 , p_transaction_id => p_group_id
3455 , p_transaction_identifier => inv_label.trx_id_rt);
3456 END;
3457 END IF; --bug 6412992 end
3458
3459
3460 END LOOP;
3461 END IF;
3462
3463 IF (p_lpn_group_id IS NOT NULL ) THEN
3464 FOR l_pregen_rec IN c_pregen_cursor LOOP
3465 IF (l_debug = 1) THEN
3466 print_debug('COMPLETE_LPN_GROUP - Before calling start_pregenerate_program:'||l_progress,1);
3467 l_progress := '30';
3468 END IF;
3469
3470 wms_putaway_suggestions.start_pregenerate_program
3471 (p_org_id => l_pregen_rec.organization_id,
3472 p_lpn_id => l_pregen_rec.transfer_lpn_id,
3473 x_return_status => x_return_status,
3474 x_msg_count => x_msg_count,
3475 x_msg_data => x_msg_data);
3476
3477 IF (l_debug = 1) THEN
3478 print_debug('COMPLETE_LPN_GROUP - After calling start_pregenerate_program:'||x_return_status||':'||l_progress,1);
3479 l_progress := '40';
3480 END IF;
3481
3482 IF (x_return_status <> 'S') THEN
3483 x_return_status := 'S';
3484 END IF;
3485
3486 END LOOP;
3487 END IF;
3488
3489 IF (l_debug = 1) THEN
3490 print_debug('COMPLETE_LPN_GROUP - Done calling label print api...',1);
3491 print_debug('COMPLETE_LPN_GROUP - Return Status:'||x_return_status,1);
3492 END IF;
3493 --14408061
3494 IF p_lpn_group_id IS NOT NULL AND inv_rcv_integration_pvt.g_lpn_tbl.COUNT>0 THEN
3495 IF (l_debug = 1) THEN
3496 print_debug('Deleting the LPN plsql table...g_lpn_tbl ',1);
3497 END IF;
3498 inv_rcv_integration_pvt.g_lpn_tbl.DELETE();
3499 END IF;
3500 --14408061
3501 RETURN TRUE;
3502 END complete_lpn_group;
3503
3504
3505 PROCEDURE split_mmtt
3506 (x_orig_mol_rec IN OUT nocopy mol_rec,
3507 x_new_mol_rec IN OUT nocopy mol_rec,
3508 x_return_status OUT NOCOPY VARCHAR2,
3509 x_msg_count OUT NOCOPY NUMBER,
3510 x_msg_data OUT NOCOPY VARCHAR2,
3511 p_prim_qty_to_splt IN NUMBER,
3512 p_qty_to_splt IN NUMBER,
3513 p_prim_uom_code IN VARCHAR2,
3514 -- OPM Convergence
3515 p_sec_qty_to_splt IN NUMBER,
3516 p_sec_uom_code IN VARCHAR2,
3517 p_updt_putaway_temp_tbl IN VARCHAR2,
3518 p_txn_header_id IN NUMBER,
3519 p_txn_temp_id IN NUMBER,
3520 p_remaining_mmtt_splt IN VARCHAR2,
3521 p_operation_type IN VARCHAR2)
3522 IS
3523 CURSOR mmtt_cur IS
3524 SELECT transaction_header_id
3525 ,transaction_temp_id
3526 ,source_code
3527 ,source_line_id
3528 ,transaction_mode
3529 ,lock_flag
3530 ,last_update_date
3531 ,last_updated_by
3532 ,creation_date
3533 ,created_by
3534 ,last_update_login
3535 ,request_id
3536 ,program_application_id
3537 ,program_id
3538 ,program_update_date
3539 ,inventory_item_id
3540 ,revision
3541 ,organization_id
3542 ,subinventory_code
3543 ,locator_id
3544 ,transaction_quantity
3545 ,primary_quantity
3546 ,transaction_uom
3547 ,transaction_cost
3548 ,transaction_type_id
3549 ,transaction_action_id
3550 ,transaction_source_type_id
3551 ,transaction_source_id
3552 ,transaction_source_name
3553 ,transaction_date
3554 ,acct_period_id
3555 ,distribution_account_id
3556 ,transaction_reference
3557 ,requisition_line_id
3558 ,requisition_distribution_id
3559 ,reason_id
3560 ,Ltrim(Rtrim(lot_number)) lot_number
3561 ,lot_expiration_date
3562 ,serial_number
3563 ,receiving_document
3564 ,demand_id
3565 ,rcv_transaction_id
3566 ,move_transaction_id
3567 ,completion_transaction_id
3568 ,wip_entity_type
3569 ,schedule_id
3570 ,repetitive_line_id
3571 ,employee_code
3572 ,primary_switch
3573 ,schedule_update_code
3574 ,setup_teardown_code
3575 ,item_ordering
3576 ,negative_req_flag
3577 ,operation_seq_num
3578 ,picking_line_id
3579 ,trx_source_line_id
3580 ,trx_source_delivery_id
3581 ,physical_adjustment_id
3582 ,cycle_count_id
3583 ,rma_line_id
3584 ,customer_ship_id
3585 ,currency_code
3586 ,currency_conversion_rate
3587 ,currency_conversion_type
3588 ,currency_conversion_date
3589 ,ussgl_transaction_code
3590 ,vendor_lot_number
3591 ,encumbrance_account
3592 ,encumbrance_amount
3593 ,ship_to_location
3594 ,shipment_number
3595 ,transfer_cost
3596 ,transportation_cost
3597 ,transportation_account
3598 ,freight_code
3599 ,containers
3600 ,waybill_airbill
3601 ,expected_arrival_date
3602 ,transfer_subinventory
3603 ,transfer_organization
3604 ,transfer_to_location
3605 ,new_average_cost
3606 ,value_change
3607 ,percentage_change
3608 ,material_allocation_temp_id
3609 ,demand_source_header_id
3610 ,demand_source_line
3611 ,demand_source_delivery
3612 ,item_segments
3613 ,item_description
3614 ,item_trx_enabled_flag
3615 ,item_location_control_code
3616 ,item_restrict_subinv_code
3617 ,item_restrict_locators_code
3618 ,item_revision_qty_control_code
3619 ,item_primary_uom_code
3620 ,item_uom_class
3621 ,item_shelf_life_code
3622 ,item_shelf_life_days
3623 ,item_lot_control_code
3624 ,item_serial_control_code
3625 ,item_inventory_asset_flag
3626 ,allowed_units_lookup_code
3627 ,department_id
3628 ,department_code
3629 ,wip_supply_type
3630 ,supply_subinventory
3631 ,supply_locator_id
3632 ,valid_subinventory_flag
3633 ,valid_locator_flag
3634 ,locator_segments
3635 ,current_locator_control_code
3636 ,number_of_lots_entered
3637 ,wip_commit_flag
3638 ,next_lot_number
3639 ,lot_alpha_prefix
3640 ,next_serial_number
3641 ,serial_alpha_prefix
3642 ,shippable_flag
3643 ,posting_flag
3644 ,required_flag
3645 ,process_flag
3646 ,error_code
3647 ,error_explanation
3648 ,attribute_category
3649 ,attribute1
3650 ,attribute2
3651 ,attribute3
3652 ,attribute4
3653 ,attribute5
3654 ,attribute6
3655 ,attribute7
3656 ,attribute8
3657 ,attribute9
3658 ,attribute10
3659 ,attribute11
3660 ,attribute12
3661 ,attribute13
3662 ,attribute14
3663 ,attribute15
3664 ,movement_id
3665 ,reservation_quantity
3666 ,shipped_quantity
3667 ,transaction_line_number
3668 ,task_id
3669 ,to_task_id
3670 ,source_task_id
3671 ,project_id
3672 ,source_project_id
3673 ,pa_expenditure_org_id
3674 ,to_project_id
3675 ,expenditure_type
3676 ,final_completion_flag
3677 ,transfer_percentage
3678 ,transaction_sequence_id
3679 ,material_account
3680 ,material_overhead_account
3681 ,resource_account
3682 ,outside_processing_account
3683 ,overhead_account
3684 ,flow_schedule
3685 ,cost_group_id
3686 ,demand_class
3687 ,qa_collection_id
3688 ,kanban_card_id
3689 ,overcompletion_transaction_qty
3690 ,overcompletion_primary_qty
3691 ,overcompletion_transaction_id
3692 ,end_item_unit_number
3693 ,scheduled_payback_date
3694 ,line_type_code
3695 ,parent_transaction_temp_id
3696 ,put_away_strategy_id
3697 ,put_away_rule_id
3698 ,pick_strategy_id
3699 ,pick_rule_id
3700 ,move_order_line_id
3701 ,task_group_id
3702 ,pick_slip_number
3703 ,reservation_id
3704 ,common_bom_seq_id
3705 ,common_routing_seq_id
3706 ,org_cost_group_id
3707 ,cost_type_id
3708 ,transaction_status
3709 ,standard_operation_id
3710 ,task_priority
3711 ,wms_task_type
3712 ,parent_line_id
3713 ,transfer_cost_group_id
3714 ,lpn_id
3715 ,transfer_lpn_id
3716 ,wms_task_status
3717 ,content_lpn_id
3718 ,container_item_id
3719 ,cartonization_id
3720 ,pick_slip_date
3721 ,rebuild_item_id
3722 ,rebuild_serial_number
3723 ,rebuild_activity_id
3724 ,rebuild_job_name
3725 ,organization_type
3726 ,transfer_organization_type
3727 ,owning_organization_id
3728 ,owning_tp_type
3729 ,xfr_owning_organization_id
3730 ,transfer_owning_tp_type
3731 ,planning_organization_id
3732 ,planning_tp_type
3733 ,xfr_planning_organization_id
3734 ,transfer_planning_tp_type
3735 ,secondary_uom_code
3736 ,secondary_transaction_quantity
3737 ,allocated_lpn_id
3738 ,schedule_number
3739 ,scheduled_flag
3740 ,class_code
3741 ,schedule_group
3742 ,build_sequence
3743 ,bom_revision
3744 ,routing_revision
3745 ,bom_revision_date
3746 ,routing_revision_date
3747 ,alternate_bom_designator
3748 ,alternate_routing_designator
3749 ,transaction_batch_id
3750 ,transaction_batch_seq
3751 ,operation_plan_id
3752 ,move_order_header_id
3753 ,serial_allocated_flag
3754 FROM mtl_material_transactions_temp
3755 WHERE
3756 -- For call from putaway: p_operation_type will be null
3757 -- For call from item load putaway, type will be 'LOAD' or 'DROP'
3758 (((p_operation_type IS NULL OR p_operation_type IN ('LOAD','DROP'))
3759 AND p_remaining_mmtt_splt = 'N'
3760 AND Nvl(transaction_header_id, -2) <> Nvl(p_txn_header_id, -1))
3761 OR
3762 -- For call from putaway when it is splitting the remaining header id
3763 (p_operation_type IS NULL
3764 AND p_remaining_mmtt_splt = 'Y'
3765 AND transaction_header_id = p_txn_header_id)
3766 OR
3767 -- For call from deliver
3768 (p_operation_type = 'DELIVER'
3769 AND transaction_temp_id = p_txn_temp_id))
3770 AND move_order_line_id = x_orig_mol_rec.line_id
3771 AND ((transaction_source_type_id = 1 AND
3772 transaction_action_id = 27) OR
3773 ( transaction_source_type_id = 7 AND
3774 transaction_action_id = 12) OR
3775 ( transaction_source_type_id = 12 AND
3776 transaction_action_id = 27) OR
3777 ( transaction_source_type_id = 4 AND
3778 transaction_action_id = 2) OR
3779 ( transaction_source_type_id = 5 AND
3780 transaction_action_id IN (27,31) ) OR
3781 ( transaction_source_type_id = 4 AND
3782 transaction_action_id = 27)OR
3783 ( transaction_source_type_id = 13 AND
3784 transaction_action_id = 12) ) --bugfix 5263798
3785 order by transaction_temp_id asc; --bugfix 6189438
3786
3787 l_orig_mmtt_rec mmtt_cur%ROWTYPE;
3788 l_new_mmtt_rec mmtt_cur%ROWTYPE;
3789 l_prim_qty_to_splt NUMBER := p_prim_qty_to_splt;
3790 l_qty_to_splt NUMBER := p_qty_to_splt;
3791 -- OPM Convergence
3792 l_sec_qty_to_splt NUMBER := NVL(p_sec_qty_to_splt, 0);
3793
3794 l_new_mmtt_id NUMBER;
3795 l_mmtts_to_split wms_atf_runtime_pub_apis.task_id_table_type;
3796 l_lot_control_code NUMBER;
3797 l_serial_control_code NUMBER;
3798 l_new_txn_tb inv_rcv_common_apis.trans_rec_tb_tp;
3799 l_temp NUMBER;
3800 l_sysdate DATE := Sysdate;
3801 l_debug NUMBER := Nvl(fnd_profile.value('INV_DEBUG_TRACE'), 0);
3802 l_progress VARCHAR2(10) := '0';
3803 l_error_code NUMBER;
3804 l_inspection_flag NUMBER;
3805 l_load_flag NUMBER;
3806 l_drop_flag NUMBER;
3807 l_load_prim_quantity NUMBER;
3808 l_inspect_prim_quantity NUMBER;
3809 l_drop_prim_quantity NUMBER;
3810
3811
3812 l_skip_iteration VARCHAR2(1) := 'N';
3813 BEGIN
3814 IF (l_debug = 1) THEN
3815 print_debug('SPLIT_MMTT Modified: Entering...', 4); --bug 6189438
3816 print_debug(' p_prim_qty_to_splt => '||p_prim_qty_to_splt,4);
3817 print_debug(' p_qty_to_splt => '||p_qty_to_splt,4);
3818 print_debug(' p_prim_uom_code => '||p_prim_uom_code,4);
3819 -- OPM Convergence
3820 print_debug(' p_sec_qty_to_splt => '||p_sec_qty_to_splt,4);
3821 print_debug(' p_sec_uom_code => '||p_sec_uom_code,4);
3822 print_debug(' p_updt_putaway_temp_tbl =>'||p_updt_putaway_temp_tbl,4);
3823 print_debug(' p_txn_header_id => '||p_txn_header_id,4);
3824 print_debug(' p_remaining_mmtt_splt => '||p_remaining_mmtt_splt,4);
3825 END IF;
3826
3827 x_return_status := g_ret_sts_success;
3828
3829 l_progress := '10';
3830
3831 OPEN mmtt_cur;
3832
3833 l_progress := '20';
3834
3835 l_skip_iteration := 'N';
3836 LOOP
3837 l_skip_iteration := 'N';
3838 l_progress := '30';
3839 FETCH mmtt_cur INTO l_orig_mmtt_rec;
3840 l_progress := '40';
3841 IF (p_remaining_mmtt_splt = 'Y') THEN
3842 IF (mmtt_cur%notfound) THEN
3843 IF (l_debug = 1) THEN
3844 print_debug('SPLIT_MMTT: No remaining MMTT with header_id '
3845 || p_txn_header_id || ' found MOL',4);
3846 END IF;
3847 fnd_message.set_name('WMS','WMS_TASK_NO_ELIGIBLE_TASKS');
3848 fnd_msg_pub.add;
3849 RAISE fnd_api.g_exc_error;
3850 END IF;
3851 ELSE
3852 IF (mmtt_cur%notfound) THEN
3853 IF (l_debug = 1) THEN
3854 print_debug('SPLIT_MMTT - No MMTT have been found for MOL:'
3855 ||x_orig_mol_rec.line_id,4);
3856 END IF;
3857
3858 -- Update original MOL
3859 x_orig_mol_rec.primary_quantity := x_orig_mol_rec.primary_quantity - l_prim_qty_to_splt;
3860 x_orig_mol_rec.quantity := x_orig_mol_rec.quantity - l_qty_to_splt;
3861 x_orig_mol_rec.secondary_quantity := NVL(x_orig_mol_rec.secondary_quantity,0) - l_sec_qty_to_splt;
3862
3863
3864 -- Update new MOL
3865 x_new_mol_rec.primary_quantity := x_new_mol_rec.primary_quantity + l_prim_qty_to_splt;
3866 x_new_mol_rec.quantity := x_new_mol_rec.quantity + l_qty_to_splt;
3867 x_new_mol_rec.secondary_quantity := NVL(x_new_mol_rec.secondary_quantity,0) + l_sec_qty_to_splt;
3868
3869 IF (l_debug = 1) THEN
3870 print_debug('SPLIT_MMTT - Progress:'||l_progress||
3871 ' Successfully assigned non-detailed quantity',
3872 4);
3873 END IF;
3874 EXIT;
3875 END IF; --IF (mmtt_cur%notfound)
3876 END IF; --(p_remaining_mmtt_splt = 'Y')
3877
3878 l_progress := '70';
3879
3880 -- If the operation is LOAD or DROP, then need to filter MMTT
3881 IF (p_operation_type IN ('LOAD', 'DROP')) THEN
3882 wms_atf_runtime_pub_apis.validate_operation
3883 (x_return_status => x_return_status
3884 ,x_msg_data => x_msg_data
3885 ,x_msg_count => x_msg_count
3886 ,x_error_code => l_error_code
3887 ,x_inspection_flag => l_inspection_flag
3888 ,x_load_flag => l_load_flag
3889 ,x_drop_flag => l_drop_flag
3890 ,x_load_prim_quantity => l_load_prim_quantity
3891 ,x_drop_prim_quantity => l_drop_prim_quantity
3892 ,x_inspect_prim_quantity => l_inspect_prim_quantity
3893 ,p_source_task_id => l_orig_mmtt_rec.transaction_temp_id
3894 ,p_move_order_line_id => NULL
3895 ,p_inventory_item_id => NULL
3896 ,p_lpn_id => NULL
3897 ,p_activity_type_id => 1 -- INBOUND
3898 ,p_organization_id => l_orig_mmtt_rec.organization_id);
3899 IF (x_return_status <> fnd_api.g_ret_sts_success) THEN
3900 IF (l_debug = 1) THEN
3901 print_debug('SPLIT_MMTT: validate_operation failed',4);
3902 END IF;
3903 RAISE fnd_api.g_exc_error;
3904 END IF;
3905
3906 IF (l_debug = 1) THEN
3907 print_debug('SPLIT_MMTT: Values returned from call to validate_operation',4);
3908 print_debug(' x_inspection_flag: =======> ' || l_inspection_flag,4);
3909 print_debug(' x_load_flag: =============> ' || l_load_flag,4);
3910 print_debug(' x_drop_flag: =============> ' || l_drop_flag,4);
3911 print_debug(' x_load_prim_quantity: ====> ' || l_load_prim_quantity,4);
3912 print_debug(' x_drop_prim_quantity: ====> ' || l_drop_prim_quantity,4);
3913 print_debug(' x_inspect_prim_quantity: => ' || l_inspect_prim_quantity,4);
3914 END IF;
3915
3916 IF (p_operation_type = 'LOAD') THEN
3917 IF (l_load_flag <> 3) THEN
3918 IF (l_debug = 1) THEN
3919 print_debug('SPLIT_MMTT: MMTT Not fully loaded, next iteration...',4);
3920 END IF;
3921 fnd_message.set_name('WMS', 'WMS_TASK_NOT_FULLY_LOADED');
3922 fnd_msg_pub.ADD;
3923 l_skip_iteration := 'Y';
3924 END IF;
3925 ELSE -- p_peration_type = 'DROP'
3926 IF (l_drop_flag <> 3) THEN
3927 IF (l_debug = 1) THEN
3928 print_debug('SPLIT_MMTT: MMTT Not fully dropped, next iteration...',4);
3929 END IF;
3930 fnd_message.set_name('WMS', 'WMS_TASK_NOT_FULLY_DROPPED');
3931 fnd_msg_pub.ADD;
3932 l_skip_iteration := 'Y';
3933 END IF;
3934 END IF;
3935 END IF; --IF (p_operation_type IN ('LOAD', 'DROP'))
3936
3937 IF (l_skip_iteration <> 'Y') THEN
3938 -- MMTT Exists
3939 -- If the MMTT has more than enough to split, than split this MMTT into the new MOL
3940 IF (l_orig_mmtt_rec.primary_quantity > l_prim_qty_to_splt) THEN
3941
3942 IF (l_debug = 1) THEN
3943 print_debug('SPLIT_MMTT: splitting MMTT'||
3944 l_orig_mmtt_rec.transaction_temp_id||
3945 ' with QTY:' || l_orig_mmtt_rec.primary_quantity||
3946 ' into QTY:' || l_prim_qty_to_splt ||
3947 ' with SEC QTY:' || l_orig_mmtt_rec.secondary_transaction_quantity||
3948 ' into SEC QTY:' || l_sec_qty_to_splt ||
3949 '... More than enough',4);
3950 END IF;
3951
3952 l_progress := '80';
3953
3954 --create a new MMTT record, set the new quantity, insert into MMTT table
3955 l_new_mmtt_rec := l_orig_mmtt_rec;
3956 l_new_mmtt_rec.move_order_line_id := x_new_mol_rec.line_id;
3957 l_new_mmtt_rec.primary_quantity := l_prim_qty_to_splt;
3958 -- OPMCOnvergence
3959 l_new_mmtt_rec.secondary_transaction_quantity := l_sec_qty_to_splt;
3960
3961 --Must use mmtt quantity and primary quantity for conversion
3962 IF (l_orig_mmtt_rec.transaction_uom = x_orig_mol_rec.uom_code) THEN
3963 l_temp := l_qty_to_splt;
3964 ELSE
3965 l_temp := inv_rcv_cache.convert_qty
3966 (p_inventory_item_id => x_orig_mol_rec.inventory_item_id
3967 ,p_from_qty => l_prim_qty_to_splt
3968 ,p_from_uom_code => p_prim_uom_code
3969 ,p_to_uom_code => l_orig_mmtt_rec.transaction_uom
3970 );
3971 END IF;
3972
3973 l_progress := '90';
3974
3975 l_new_mmtt_rec.transaction_quantity := l_temp;
3976
3977 BEGIN
3978 INSERT INTO mtl_material_transactions_temp
3979 ( transaction_header_id
3980 ,transaction_temp_id
3981 ,source_code
3982 ,source_line_id
3983 ,transaction_mode
3984 ,lock_flag
3985 ,last_update_date
3986 ,last_updated_by
3987 ,creation_date
3988 ,created_by
3989 ,last_update_login
3990 ,request_id
3991 ,program_application_id
3992 ,program_id
3993 ,program_update_date
3994 ,inventory_item_id
3995 ,revision
3996 ,organization_id
3997 ,subinventory_code
3998 ,locator_id
3999 ,transaction_quantity
4000 ,primary_quantity
4001 ,transaction_uom
4002 ,transaction_cost
4003 ,transaction_type_id
4004 ,transaction_action_id
4005 ,transaction_source_type_id
4006 ,transaction_source_id
4007 ,transaction_source_name
4008 ,transaction_date
4009 ,acct_period_id
4010 ,distribution_account_id
4011 ,transaction_reference
4012 ,requisition_line_id
4013 ,requisition_distribution_id
4014 ,reason_id
4015 ,lot_number
4016 ,lot_expiration_date
4017 ,serial_number
4018 ,receiving_document
4019 ,demand_id
4020 ,rcv_transaction_id
4021 ,move_transaction_id
4022 ,completion_transaction_id
4023 ,wip_entity_type
4024 ,schedule_id
4025 ,repetitive_line_id
4026 ,employee_code
4027 ,primary_switch
4028 ,schedule_update_code
4029 ,setup_teardown_code
4030 ,item_ordering
4031 ,negative_req_flag
4032 ,operation_seq_num
4033 ,picking_line_id
4034 ,trx_source_line_id
4035 ,trx_source_delivery_id
4036 ,physical_adjustment_id
4037 ,cycle_count_id
4038 ,rma_line_id
4039 ,customer_ship_id
4040 ,currency_code
4041 ,currency_conversion_rate
4042 ,currency_conversion_type
4043 ,currency_conversion_date
4044 ,ussgl_transaction_code
4045 ,vendor_lot_number
4046 ,encumbrance_account
4047 ,encumbrance_amount
4048 ,ship_to_location
4049 ,shipment_number
4050 ,transfer_cost
4051 ,transportation_cost
4052 ,transportation_account
4053 ,freight_code
4054 ,containers
4055 ,waybill_airbill
4056 ,expected_arrival_date
4057 ,transfer_subinventory
4058 ,transfer_organization
4059 ,transfer_to_location
4060 ,new_average_cost
4061 ,value_change
4062 ,percentage_change
4063 ,material_allocation_temp_id
4064 ,demand_source_header_id
4065 ,demand_source_line
4066 ,demand_source_delivery
4067 ,item_segments
4068 ,item_description
4069 ,item_trx_enabled_flag
4070 ,item_location_control_code
4071 ,item_restrict_subinv_code
4072 ,item_restrict_locators_code
4073 ,item_revision_qty_control_code
4074 ,item_primary_uom_code
4075 ,item_uom_class
4076 ,item_shelf_life_code
4077 ,item_shelf_life_days
4078 ,item_lot_control_code
4079 ,item_serial_control_code
4080 ,item_inventory_asset_flag
4081 ,allowed_units_lookup_code
4082 ,department_id
4083 ,department_code
4084 ,wip_supply_type
4085 ,supply_subinventory
4086 ,supply_locator_id
4087 ,valid_subinventory_flag
4088 ,valid_locator_flag
4089 ,locator_segments
4090 ,current_locator_control_code
4091 ,number_of_lots_entered
4092 ,wip_commit_flag
4093 ,next_lot_number
4094 ,lot_alpha_prefix
4095 ,next_serial_number
4096 ,serial_alpha_prefix
4097 ,shippable_flag
4098 ,posting_flag
4099 ,required_flag
4100 ,process_flag
4101 ,error_code
4102 ,error_explanation
4103 ,attribute_category
4104 ,attribute1
4105 ,attribute2
4106 ,attribute3
4107 ,attribute4
4108 ,attribute5
4109 ,attribute6
4110 ,attribute7
4111 ,attribute8
4112 ,attribute9
4113 ,attribute10
4114 ,attribute11
4115 ,attribute12
4116 ,attribute13
4117 ,attribute14
4118 ,attribute15
4119 ,movement_id
4120 ,reservation_quantity
4121 ,shipped_quantity
4122 ,transaction_line_number
4123 ,task_id
4124 ,to_task_id
4125 ,source_task_id
4126 ,project_id
4127 ,source_project_id
4128 ,pa_expenditure_org_id
4129 ,to_project_id
4130 ,expenditure_type
4131 ,final_completion_flag
4132 ,transfer_percentage
4133 ,transaction_sequence_id
4134 ,material_account
4135 ,material_overhead_account
4136 ,resource_account
4137 ,outside_processing_account
4138 ,overhead_account
4139 ,flow_schedule
4140 ,cost_group_id
4141 ,demand_class
4142 ,qa_collection_id
4143 ,kanban_card_id
4144 ,overcompletion_transaction_qty
4145 ,overcompletion_primary_qty
4146 ,overcompletion_transaction_id
4147 ,end_item_unit_number
4148 ,scheduled_payback_date
4149 ,line_type_code
4150 ,parent_transaction_temp_id
4151 ,put_away_strategy_id
4152 ,put_away_rule_id
4153 ,pick_strategy_id
4154 ,pick_rule_id
4155 ,move_order_line_id
4156 ,task_group_id
4157 ,pick_slip_number
4158 ,reservation_id
4159 ,common_bom_seq_id
4160 ,common_routing_seq_id
4161 ,org_cost_group_id
4162 ,cost_type_id
4163 ,transaction_status
4164 ,standard_operation_id
4165 ,task_priority
4166 ,wms_task_type
4167 ,parent_line_id
4168 ,transfer_cost_group_id
4169 ,lpn_id
4170 ,transfer_lpn_id
4171 ,wms_task_status
4172 ,content_lpn_id
4173 ,container_item_id
4174 ,cartonization_id
4175 ,pick_slip_date
4176 ,rebuild_item_id
4177 ,rebuild_serial_number
4178 ,rebuild_activity_id
4179 ,rebuild_job_name
4180 ,organization_type
4181 ,transfer_organization_type
4182 ,owning_organization_id
4183 ,owning_tp_type
4184 ,xfr_owning_organization_id
4185 ,transfer_owning_tp_type
4186 ,planning_organization_id
4187 ,planning_tp_type
4188 ,xfr_planning_organization_id
4189 ,transfer_planning_tp_type
4190 ,secondary_uom_code
4191 ,secondary_transaction_quantity
4192 ,allocated_lpn_id
4193 ,schedule_number
4194 ,scheduled_flag
4195 ,class_code
4196 ,schedule_group
4197 ,build_sequence
4198 ,bom_revision
4199 ,routing_revision
4200 ,bom_revision_date
4201 ,routing_revision_date
4202 ,alternate_bom_designator
4203 ,alternate_routing_designator
4204 ,transaction_batch_id
4205 ,transaction_batch_seq
4206 ,operation_plan_id
4207 ,move_order_header_id
4208 ,serial_allocated_flag )
4209 VALUES
4210 ( mtl_material_transactions_s.NEXTVAL --use different header
4211 ,mtl_material_transactions_s.NEXTVAL
4212 ,l_new_mmtt_rec.SOURCE_CODE
4213 ,l_new_mmtt_rec.SOURCE_LINE_ID
4214 ,l_new_mmtt_rec.TRANSACTION_MODE
4215 ,l_new_mmtt_rec.LOCK_FLAG
4216 ,l_sysdate
4217 ,l_new_mmtt_rec.LAST_UPDATED_BY
4218 ,l_sysdate
4219 ,l_new_mmtt_rec.CREATED_BY
4220 ,l_new_mmtt_rec.LAST_UPDATE_LOGIN
4221 ,l_new_mmtt_rec.REQUEST_ID
4222 ,l_new_mmtt_rec.PROGRAM_APPLICATION_ID
4223 ,l_new_mmtt_rec.PROGRAM_ID
4224 ,l_new_mmtt_rec.PROGRAM_UPDATE_DATE
4225 ,l_new_mmtt_rec.INVENTORY_ITEM_ID
4226 ,l_new_mmtt_rec.REVISION
4227 ,l_new_mmtt_rec.ORGANIZATION_ID
4228 ,l_new_mmtt_rec.SUBINVENTORY_CODE
4229 ,l_new_mmtt_rec.LOCATOR_ID
4230 ,l_new_mmtt_rec.TRANSACTION_QUANTITY
4231 ,l_new_mmtt_rec.PRIMARY_QUANTITY
4232 ,l_new_mmtt_rec.TRANSACTION_UOM
4233 ,l_new_mmtt_rec.TRANSACTION_COST
4234 ,l_new_mmtt_rec.TRANSACTION_TYPE_ID
4235 ,l_new_mmtt_rec.TRANSACTION_ACTION_ID
4236 ,l_new_mmtt_rec.TRANSACTION_SOURCE_TYPE_ID
4237 ,l_new_mmtt_rec.TRANSACTION_SOURCE_ID
4238 ,l_new_mmtt_rec.TRANSACTION_SOURCE_NAME
4239 ,l_new_mmtt_rec.TRANSACTION_DATE
4240 ,l_new_mmtt_rec.ACCT_PERIOD_ID
4241 ,l_new_mmtt_rec.DISTRIBUTION_ACCOUNT_ID
4242 ,l_new_mmtt_rec.TRANSACTION_REFERENCE
4243 ,l_new_mmtt_rec.REQUISITION_LINE_ID
4244 ,l_new_mmtt_rec.REQUISITION_DISTRIBUTION_ID
4245 ,l_new_mmtt_rec.REASON_ID
4246 ,Ltrim(Rtrim(l_new_mmtt_rec.lot_number))
4247 ,l_new_mmtt_rec.LOT_EXPIRATION_DATE
4248 ,l_new_mmtt_rec.SERIAL_NUMBER
4249 ,l_new_mmtt_rec.RECEIVING_DOCUMENT
4250 ,l_new_mmtt_rec.DEMAND_ID
4251 ,l_new_mmtt_rec.RCV_TRANSACTION_ID
4252 ,l_new_mmtt_rec.MOVE_TRANSACTION_ID
4253 ,l_new_mmtt_rec.COMPLETION_TRANSACTION_ID
4254 ,l_new_mmtt_rec.WIP_ENTITY_TYPE
4255 ,l_new_mmtt_rec.SCHEDULE_ID
4256 ,l_new_mmtt_rec.REPETITIVE_LINE_ID
4257 ,l_new_mmtt_rec.employee_code
4258 ,l_new_mmtt_rec.PRIMARY_SWITCH
4259 ,l_new_mmtt_rec.SCHEDULE_UPDATE_CODE
4260 ,l_new_mmtt_rec.SETUP_TEARDOWN_CODE
4261 ,l_new_mmtt_rec.ITEM_ORDERING
4262 ,l_new_mmtt_rec.NEGATIVE_REQ_FLAG
4263 ,l_new_mmtt_rec.OPERATION_SEQ_NUM
4264 ,l_new_mmtt_rec.PICKING_LINE_ID
4265 ,l_new_mmtt_rec.TRX_SOURCE_LINE_ID
4266 ,l_new_mmtt_rec.TRX_SOURCE_DELIVERY_ID
4267 ,l_new_mmtt_rec.PHYSICAL_ADJUSTMENT_ID
4268 ,l_new_mmtt_rec.CYCLE_COUNT_ID
4269 ,l_new_mmtt_rec.RMA_LINE_ID
4270 ,l_new_mmtt_rec.CUSTOMER_SHIP_ID
4271 ,l_new_mmtt_rec.CURRENCY_CODE
4272 ,l_new_mmtt_rec.CURRENCY_CONVERSION_RATE
4273 ,l_new_mmtt_rec.CURRENCY_CONVERSION_TYPE
4274 ,l_new_mmtt_rec.CURRENCY_CONVERSION_DATE
4275 ,l_new_mmtt_rec.USSGL_TRANSACTION_CODE
4276 ,l_new_mmtt_rec.VENDOR_LOT_NUMBER
4277 ,l_new_mmtt_rec.ENCUMBRANCE_ACCOUNT
4278 ,l_new_mmtt_rec.ENCUMBRANCE_AMOUNT
4279 ,l_new_mmtt_rec.SHIP_TO_LOCATION
4280 ,l_new_mmtt_rec.SHIPMENT_NUMBER
4281 ,l_new_mmtt_rec.TRANSFER_COST
4282 ,l_new_mmtt_rec.TRANSPORTATION_COST
4283 ,l_new_mmtt_rec.TRANSPORTATION_ACCOUNT
4284 ,l_new_mmtt_rec.FREIGHT_CODE
4285 ,l_new_mmtt_rec.CONTAINERS
4286 ,l_new_mmtt_rec.WAYBILL_AIRBILL
4287 ,l_new_mmtt_rec.EXPECTED_ARRIVAL_DATE
4288 ,l_new_mmtt_rec.TRANSFER_SUBINVENTORY
4289 ,l_new_mmtt_rec.TRANSFER_ORGANIZATION
4290 ,l_new_mmtt_rec.TRANSFER_TO_LOCATION
4291 ,l_new_mmtt_rec.NEW_AVERAGE_COST
4292 ,l_new_mmtt_rec.VALUE_CHANGE
4293 ,l_new_mmtt_rec.PERCENTAGE_CHANGE
4294 ,l_new_mmtt_rec.MATERIAL_ALLOCATION_TEMP_ID
4295 ,l_new_mmtt_rec.DEMAND_SOURCE_HEADER_ID
4296 ,l_new_mmtt_rec.DEMAND_SOURCE_LINE
4297 ,l_new_mmtt_rec.DEMAND_SOURCE_DELIVERY
4298 ,l_new_mmtt_rec.ITEM_SEGMENTS
4299 ,l_new_mmtt_rec.ITEM_DESCRIPTION
4300 ,l_new_mmtt_rec.ITEM_TRX_ENABLED_FLAG
4301 ,l_new_mmtt_rec.ITEM_LOCATION_CONTROL_CODE
4302 ,l_new_mmtt_rec.ITEM_RESTRICT_SUBINV_CODE
4303 ,l_new_mmtt_rec.ITEM_RESTRICT_LOCATORS_CODE
4304 ,l_new_mmtt_rec.ITEM_REVISION_QTY_CONTROL_CODE
4305 ,l_new_mmtt_rec.ITEM_PRIMARY_UOM_CODE
4306 ,l_new_mmtt_rec.ITEM_UOM_CLASS
4307 ,l_new_mmtt_rec.ITEM_SHELF_LIFE_CODE
4308 ,l_new_mmtt_rec.ITEM_SHELF_LIFE_DAYS
4309 ,l_new_mmtt_rec.ITEM_LOT_CONTROL_CODE
4310 ,l_new_mmtt_rec.ITEM_SERIAL_CONTROL_CODE
4311 ,l_new_mmtt_rec.ITEM_INVENTORY_ASSET_FLAG
4312 ,l_new_mmtt_rec.ALLOWED_UNITS_LOOKUP_CODE
4313 ,l_new_mmtt_rec.DEPARTMENT_ID
4314 ,l_new_mmtt_rec.DEPARTMENT_CODE
4315 ,l_new_mmtt_rec.WIP_SUPPLY_TYPE
4316 ,l_new_mmtt_rec.SUPPLY_SUBINVENTORY
4317 ,l_new_mmtt_rec.SUPPLY_LOCATOR_ID
4318 ,l_new_mmtt_rec.VALID_SUBINVENTORY_FLAG
4319 ,l_new_mmtt_rec.VALID_LOCATOR_FLAG
4320 ,l_new_mmtt_rec.LOCATOR_SEGMENTS
4321 ,l_new_mmtt_rec.CURRENT_LOCATOR_CONTROL_CODE
4322 ,l_new_mmtt_rec.NUMBER_OF_LOTS_ENTERED
4323 ,l_new_mmtt_rec.WIP_COMMIT_FLAG
4324 ,l_new_mmtt_rec.NEXT_LOT_NUMBER
4325 ,l_new_mmtt_rec.LOT_ALPHA_PREFIX
4326 ,l_new_mmtt_rec.NEXT_SERIAL_NUMBER
4327 ,l_new_mmtt_rec.SERIAL_ALPHA_PREFIX
4328 ,l_new_mmtt_rec.SHIPPABLE_FLAG
4329 ,l_new_mmtt_rec.POSTING_FLAG
4330 ,l_new_mmtt_rec.REQUIRED_FLAG
4331 ,l_new_mmtt_rec.PROCESS_FLAG
4332 ,l_new_mmtt_rec.ERROR_CODE
4333 ,l_new_mmtt_rec.ERROR_EXPLANATION
4334 ,l_new_mmtt_rec.ATTRIBUTE_CATEGORY
4335 ,l_new_mmtt_rec.ATTRIBUTE1
4336 ,l_new_mmtt_rec.ATTRIBUTE2
4337 ,l_new_mmtt_rec.ATTRIBUTE3
4338 ,l_new_mmtt_rec.ATTRIBUTE4
4339 ,l_new_mmtt_rec.ATTRIBUTE5
4340 ,l_new_mmtt_rec.ATTRIBUTE6
4341 ,l_new_mmtt_rec.ATTRIBUTE7
4342 ,l_new_mmtt_rec.ATTRIBUTE8
4343 ,l_new_mmtt_rec.ATTRIBUTE9
4344 ,l_new_mmtt_rec.ATTRIBUTE10
4345 ,l_new_mmtt_rec.ATTRIBUTE11
4346 ,l_new_mmtt_rec.ATTRIBUTE12
4347 ,l_new_mmtt_rec.ATTRIBUTE13
4348 ,l_new_mmtt_rec.ATTRIBUTE14
4349 ,l_new_mmtt_rec.ATTRIBUTE15
4350 ,l_new_mmtt_rec.MOVEMENT_ID
4351 ,l_new_mmtt_rec.RESERVATION_QUANTITY
4352 ,l_new_mmtt_rec.SHIPPED_QUANTITY
4353 ,l_new_mmtt_rec.TRANSACTION_LINE_NUMBER
4354 ,l_new_mmtt_rec.TASK_ID
4355 ,l_new_mmtt_rec.TO_TASK_ID
4356 ,l_new_mmtt_rec.SOURCE_TASK_ID
4357 ,l_new_mmtt_rec.PROJECT_ID
4358 ,l_new_mmtt_rec.SOURCE_PROJECT_ID
4359 ,l_new_mmtt_rec.PA_EXPENDITURE_ORG_ID
4360 ,l_new_mmtt_rec.TO_PROJECT_ID
4361 ,l_new_mmtt_rec.EXPENDITURE_TYPE
4362 ,l_new_mmtt_rec.FINAL_COMPLETION_FLAG
4363 ,l_new_mmtt_rec.TRANSFER_PERCENTAGE
4364 ,l_new_mmtt_rec.TRANSACTION_SEQUENCE_ID
4365 ,l_new_mmtt_rec.MATERIAL_ACCOUNT
4366 ,l_new_mmtt_rec.MATERIAL_OVERHEAD_ACCOUNT
4367 ,l_new_mmtt_rec.RESOURCE_ACCOUNT
4368 ,l_new_mmtt_rec.OUTSIDE_PROCESSING_ACCOUNT
4369 ,l_new_mmtt_rec.OVERHEAD_ACCOUNT
4370 ,l_new_mmtt_rec.FLOW_SCHEDULE
4371 ,l_new_mmtt_rec.COST_GROUP_ID
4372 ,l_new_mmtt_rec.DEMAND_CLASS
4373 ,l_new_mmtt_rec.QA_COLLECTION_ID
4374 ,l_new_mmtt_rec.KANBAN_CARD_ID
4375 ,l_new_mmtt_rec.OVERCOMPLETION_TRANSACTION_QTY
4376 ,l_new_mmtt_rec.OVERCOMPLETION_PRIMARY_QTY
4377 ,l_new_mmtt_rec.OVERCOMPLETION_TRANSACTION_ID
4378 ,l_new_mmtt_rec.END_ITEM_UNIT_NUMBER
4379 ,l_new_mmtt_rec.SCHEDULED_PAYBACK_DATE
4380 ,l_new_mmtt_rec.LINE_TYPE_CODE
4381 ,l_new_mmtt_rec.PARENT_TRANSACTION_TEMP_ID
4382 ,l_new_mmtt_rec.PUT_AWAY_STRATEGY_ID
4383 ,l_new_mmtt_rec.PUT_AWAY_RULE_ID
4384 ,l_new_mmtt_rec.PICK_STRATEGY_ID
4385 ,l_new_mmtt_rec.PICK_RULE_ID
4386 ,l_new_mmtt_rec.MOVE_ORDER_LINE_ID
4387 ,l_new_mmtt_rec.TASK_GROUP_ID
4388 ,l_new_mmtt_rec.PICK_SLIP_NUMBER
4389 ,l_new_mmtt_rec.RESERVATION_ID
4390 ,l_new_mmtt_rec.COMMON_BOM_SEQ_ID
4391 ,l_new_mmtt_rec.COMMON_ROUTING_SEQ_ID
4392 ,l_new_mmtt_rec.ORG_COST_GROUP_ID
4393 ,l_new_mmtt_rec.COST_TYPE_ID
4394 ,l_new_mmtt_rec.TRANSACTION_STATUS
4395 ,l_new_mmtt_rec.STANDARD_OPERATION_ID
4396 ,l_new_mmtt_rec.TASK_PRIORITY
4397 ,l_new_mmtt_rec.WMS_TASK_TYPE
4398 ,l_new_mmtt_rec.PARENT_LINE_ID
4399 ,l_new_mmtt_rec.TRANSFER_COST_GROUP_ID
4400 ,l_new_mmtt_rec.LPN_ID
4401 ,l_new_mmtt_rec.TRANSFER_LPN_ID
4402 ,l_new_mmtt_rec.WMS_TASK_STATUS
4403 ,l_new_mmtt_rec.CONTENT_LPN_ID
4404 ,l_new_mmtt_rec.CONTAINER_ITEM_ID
4405 ,l_new_mmtt_rec.CARTONIZATION_ID
4406 ,l_new_mmtt_rec.PICK_SLIP_DATE
4407 ,l_new_mmtt_rec.REBUILD_ITEM_ID
4408 ,l_new_mmtt_rec.REBUILD_SERIAL_NUMBER
4409 ,l_new_mmtt_rec.REBUILD_ACTIVITY_ID
4410 ,l_new_mmtt_rec.REBUILD_JOB_NAME
4411 ,l_new_mmtt_rec.ORGANIZATION_TYPE
4412 ,l_new_mmtt_rec.TRANSFER_ORGANIZATION_TYPE
4413 ,l_new_mmtt_rec.OWNING_ORGANIZATION_ID
4414 ,l_new_mmtt_rec.OWNING_TP_TYPE
4415 ,l_new_mmtt_rec.XFR_OWNING_ORGANIZATION_ID
4416 ,l_new_mmtt_rec.TRANSFER_OWNING_TP_TYPE
4417 ,l_new_mmtt_rec.PLANNING_ORGANIZATION_ID
4418 ,l_new_mmtt_rec.PLANNING_TP_TYPE
4419 ,l_new_mmtt_rec.XFR_PLANNING_ORGANIZATION_ID
4420 ,l_new_mmtt_rec.TRANSFER_PLANNING_TP_TYPE
4421 ,l_new_mmtt_rec.SECONDARY_UOM_CODE
4422 ,l_new_mmtt_rec.SECONDARY_TRANSACTION_QUANTITY
4423 ,l_new_mmtt_rec.ALLOCATED_LPN_ID
4424 ,l_new_mmtt_rec.SCHEDULE_NUMBER
4425 ,l_new_mmtt_rec.SCHEDULED_FLAG
4426 ,l_new_mmtt_rec.CLASS_CODE
4427 ,l_new_mmtt_rec.SCHEDULE_GROUP
4428 ,l_new_mmtt_rec.BUILD_SEQUENCE
4429 ,l_new_mmtt_rec.BOM_REVISION
4430 ,l_new_mmtt_rec.ROUTING_REVISION
4431 ,l_new_mmtt_rec.BOM_REVISION_DATE
4432 ,l_new_mmtt_rec.ROUTING_REVISION_DATE
4433 ,l_new_mmtt_rec.ALTERNATE_BOM_DESIGNATOR
4434 ,l_new_mmtt_rec.ALTERNATE_ROUTING_DESIGNATOR
4435 ,l_new_mmtt_rec.TRANSACTION_BATCH_ID
4436 ,l_new_mmtt_rec.TRANSACTION_BATCH_SEQ
4437 ,l_new_mmtt_rec.operation_plan_id
4438 ,l_new_mmtt_rec.move_order_header_id
4439 ,l_new_mmtt_rec.serial_allocated_flag)
4440 returning transaction_temp_id INTO l_new_mmtt_id;
4441 EXCEPTION
4442 WHEN OTHERS THEN
4443 IF (l_debug = 1) THEN
4444 print_debug('SPLIT_MMTT: Error inserting mmtt', 4);
4445 END IF;
4446 RAISE fnd_api.g_exc_error;
4447 END;
4448
4449 l_progress := '100';
4450
4451 IF (l_debug = 1) THEN
4452 print_debug('SPLIT_MMTT: MMTT successfully inserted: ' || l_new_mmtt_id,4);
4453 END IF;
4454
4455 /* Update original mmtt */
4456 l_orig_mmtt_rec.primary_quantity := l_orig_mmtt_rec.primary_quantity - l_prim_qty_to_splt;
4457 l_orig_mmtt_rec.transaction_quantity := l_orig_mmtt_rec.transaction_quantity - l_temp;
4458 l_orig_mmtt_rec.secondary_transaction_quantity := l_orig_mmtt_rec.secondary_transaction_quantity - l_sec_qty_to_splt;
4459
4460 BEGIN
4461 UPDATE
4462 mtl_material_transactions_temp
4463 SET
4464 primary_quantity = l_orig_mmtt_rec.primary_quantity
4465 ,transaction_quantity = l_orig_mmtt_rec.transaction_quantity
4466 , secondary_transaction_quantity = decode (l_orig_mmtt_rec.secondary_uom_code, NULL, NULL, l_orig_mmtt_rec.secondary_transaction_quantity)
4467 WHERE
4468 transaction_temp_id = l_orig_mmtt_rec.transaction_temp_id;
4469 EXCEPTION
4470 WHEN OTHERS THEN
4471 IF (l_debug = 1) THEN
4472 print_debug('SPLIT_MMTT: Error updating original MMTT', 4);
4473 END IF;
4474 RAISE fnd_api.g_exc_error;
4475 END;
4476
4477 l_progress := '110';
4478 -- Update putaway_temp_table if necessary
4479 IF (p_updt_putaway_temp_tbl = fnd_api.g_true) THEN
4480 BEGIN
4481 UPDATE
4482 wms_putaway_group_tasks_gtmp
4483 SET
4484 primary_quantity = l_orig_mmtt_rec.primary_quantity
4485 ,transaction_quantity = Decode(primary_quantity,
4486 transaction_quantity,
4487 l_orig_mmtt_rec.transaction_quantity,
4488 inv_rcv_cache.convert_qty
4489 (x_orig_mol_rec.inventory_item_id
4490 ,l_orig_mmtt_rec.primary_quantity
4491 ,p_prim_uom_code
4492 ,l_orig_mmtt_rec.transaction_uom
4493 ,NULL
4494 ))
4495 ,secondary_quantity = decode (l_orig_mmtt_rec.secondary_uom_code, NULL, NULL, l_orig_mmtt_rec.secondary_transaction_quantity)
4496 WHERE
4497 row_type = 'All Task'
4498 AND move_order_line_id = x_orig_mol_rec.line_id
4499 AND transaction_temp_id = l_orig_mmtt_rec.transaction_temp_id;
4500 EXCEPTION
4501 WHEN OTHERS THEN
4502 IF (l_debug = 1) THEN
4503 print_debug('SPLIT_MMTT: Error updating putaway temp table',
4504 4);
4505 END IF;
4506 RAISE fnd_api.g_exc_error;
4507 END;
4508 END IF;
4509
4510 l_progress := '120';
4511 -- Split parent MMTT, operation instance, operation plan
4512 l_mmtts_to_split(1) := l_new_mmtt_id;
4513 wms_atf_runtime_pub_apis.split_operation_instance
4514 (p_source_task_id => l_orig_mmtt_rec.transaction_temp_id,
4515 p_new_task_id_table => l_mmtts_to_split,
4516 p_activity_type_id => 1, -- INBOUND
4517 x_return_status => x_return_status,
4518 x_msg_count => x_msg_count,
4519 x_error_code => l_error_code,
4520 x_msg_data => x_msg_data);
4521 IF (x_return_status <> g_ret_sts_success) THEN
4522 IF (l_debug = 1) THEN
4523 print_debug('SPLIT_MMTT: Error in split_operation_instance',4);
4524 END IF;
4525 fnd_message.set_name('WMS','WMS_TASK_SPLIT_FAIL');
4526 fnd_msg_pub.add;
4527 RAISE fnd_api.g_exc_error;
4528 END IF;
4529 l_progress := '130';
4530
4531 /* Retrieve lot/serial control code call break to split */
4532 /* MTLT/MSLT */
4533 BEGIN
4534 l_lot_control_code := inv_rcv_cache.get_lot_control_code(x_orig_mol_rec.organization_id,
4535 x_orig_mol_rec.inventory_item_id);
4536 l_serial_control_code := 1;
4537 EXCEPTION
4538 WHEN OTHERS THEN
4539 IF (l_debug = 1) THEN
4540 print_debug('No entry exists for inventory_item_id:'
4541 || x_orig_mol_rec.inventory_item_id ||
4542 ' organization:' ||
4543 x_orig_mol_rec.organization_id, 4);
4544 END IF;
4545 RAISE fnd_api.g_exc_error;
4546 END;
4547 l_progress := '140';
4548
4549 l_new_txn_tb(1).transaction_id := l_new_mmtt_id;
4550 l_new_txn_tb(1).primary_quantity := l_new_mmtt_rec.primary_quantity;
4551
4552 IF (l_debug = 1) THEN
4553 print_debug('SPLIT_MMTT - Progress:'||l_progress||' Calling break to break lot serial',4);
4554 print_debug(' p_lot_control_code => ' ||
4555 l_lot_control_code,4);
4556 print_debug(' p_serial_control_code => '||
4557 l_serial_control_code,4);
4558 END IF;
4559
4560 BEGIN
4561 inv_rcv_common_apis.break
4562 ( p_original_tid => l_orig_mmtt_rec.transaction_temp_id
4563 ,p_new_transactions_tb => l_new_txn_tb
4564 ,p_lot_control_code => l_lot_control_code
4565 ,p_serial_control_code => l_serial_control_code);
4566 EXCEPTION
4567 WHEN OTHERS THEN
4568 IF (l_debug = 1) THEN
4569 print_debug('SPLIT_MMTT: Error breaking lot serial', 4);
4570 END IF;
4571 RAISE fnd_api.g_exc_error;
4572 END;
4573
4574 l_progress := '150';
4575
4576 IF (p_remaining_mmtt_splt = 'N') THEN
4577 -- only update qty in the first loop
4578 -- Update new MOL with the increased quantity
4579 x_new_mol_rec.primary_quantity := x_new_mol_rec.primary_quantity + l_prim_qty_to_splt;
4580 x_new_mol_rec.quantity := x_new_mol_rec.quantity + l_qty_to_splt;
4581 x_new_mol_rec.secondary_quantity := x_new_mol_rec.secondary_quantity + l_sec_qty_to_splt;
4582
4583 -- Update old MOL with the reduced quantity
4584 x_orig_mol_rec.primary_quantity := x_orig_mol_rec.primary_quantity - l_prim_qty_to_splt;
4585 x_orig_mol_rec.quantity := x_orig_mol_rec.quantity - l_qty_to_splt;
4586 x_orig_mol_rec.secondary_quantity := x_orig_mol_rec.secondary_quantity - l_sec_qty_to_splt;
4587 END IF; -- IF (p_remaining_mmtt_splt = 'N') THEN
4588 x_orig_mol_rec.quantity_detailed := x_orig_mol_rec.quantity_detailed - l_qty_to_splt;
4589 x_orig_mol_rec.secondary_quantity_detailed := x_orig_mol_rec.secondary_quantity_detailed - l_sec_qty_to_splt;
4590
4591 x_new_mol_rec.quantity_detailed := x_new_mol_rec.quantity_detailed + l_qty_to_splt;
4592 x_new_mol_rec.secondary_quantity_detailed := x_new_mol_rec.secondary_quantity_detailed + l_sec_qty_to_splt;
4593
4594 IF (l_debug = 1) THEN
4595 print_debug('SPLIT_MMTT: MMTT Split completed sucessfully', 4);
4596 END IF;
4597 l_progress := '160';
4598
4599 EXIT;
4600 ELSIF (l_orig_mmtt_rec.primary_quantity = l_prim_qty_to_splt) THEN
4601 /* If the MMTT has the exact amount to be split, then simply update the original mmtt mol pointer */
4602
4603 IF (l_debug = 1) THEN
4604 print_debug('SPLIT_MMTT: splitting MMTT:'||
4605 l_orig_mmtt_rec.transaction_temp_id||
4606 ' with QTY:' ||
4607 l_orig_mmtt_rec.primary_quantity||
4608 ' into QTY:' || l_prim_qty_to_splt ||
4609 ' with SEC QTY:' ||
4610 l_orig_mmtt_rec.secondary_transaction_quantity||
4611 ' into SEC QTY:' || l_sec_qty_to_splt ||
4612 '... Exact amount',4);
4613 END IF;
4614
4615 l_progress := '170';
4616 BEGIN
4617 UPDATE mtl_material_transactions_temp
4618 SET move_order_line_id = x_new_mol_rec.line_id
4619 WHERE transaction_temp_id = l_orig_mmtt_rec.transaction_temp_id;
4620 EXCEPTION
4621 WHEN OTHERS THEN
4622 IF (l_debug = 1) THEN
4623 print_debug('SPLIT_MMTT: Error updating mmtt with id '
4624 || l_orig_mmtt_rec.transaction_temp_id, 4);
4625 END IF;
4626 RAISE fnd_api.g_exc_error;
4627 END;
4628 l_progress := '180';
4629
4630 -- Update MOL of putaway_temp_table if necessary
4631 IF (p_updt_putaway_temp_tbl = fnd_api.g_true) THEN
4632 BEGIN
4633 UPDATE
4634 wms_putaway_group_tasks_gtmp
4635 SET move_order_line_id = x_new_mol_rec.line_id
4636 WHERE transaction_temp_id = l_orig_mmtt_rec.transaction_temp_id
4637 AND move_order_line_id = x_orig_mol_rec.line_id
4638 AND row_type = 'All Task';
4639 EXCEPTION
4640 WHEN OTHERS THEN
4641 IF (l_debug = 1) THEN
4642 print_debug('Error updating[[[ putaway temp table',
4643 4);
4644 END IF;
4645 RAISE fnd_api.g_exc_error;
4646 END;
4647 END IF;
4648 l_progress := '190';
4649
4650 IF (p_remaining_mmtt_splt = 'N') THEN
4651 -- Don't update qty in second loop, since it is updated during
4652 -- the first one
4653 -- Update new MOL with the increased quantity
4654 x_new_mol_rec.primary_quantity := x_new_mol_rec.primary_quantity + l_prim_qty_to_splt;
4655 x_new_mol_rec.quantity := x_new_mol_rec.quantity + l_qty_to_splt;
4656 x_new_mol_rec.secondary_quantity := x_new_mol_rec.secondary_quantity + l_sec_qty_to_splt;
4657
4658 -- Update original MOL with the reduced quantity
4659 x_orig_mol_rec.primary_quantity := x_orig_mol_rec.primary_quantity - l_prim_qty_to_splt;
4660 x_orig_mol_rec.quantity := x_orig_mol_rec.quantity - l_qty_to_splt;
4661 x_orig_mol_rec.secondary_quantity := x_orig_mol_rec.secondary_quantity - l_sec_qty_to_splt;
4662 END IF;
4663 x_orig_mol_rec.quantity_detailed := x_orig_mol_rec.quantity_detailed - l_qty_to_splt;
4664 x_orig_mol_rec.secondary_quantity_detailed := x_orig_mol_rec.secondary_quantity_detailed - l_sec_qty_to_splt;
4665
4666 x_new_mol_rec.quantity_detailed := x_new_mol_rec.quantity_detailed + l_qty_to_splt;
4667 x_new_mol_rec.secondary_quantity_detailed := x_new_mol_rec.secondary_quantity_detailed + l_sec_qty_to_splt;
4668
4669
4670 IF (l_debug = 1) THEN
4671 print_debug('SPLIT_MMTT - Progress:'|| l_progress ||
4672 ' Successfully update MMTT to new MOL', 4);
4673 END IF;
4674 l_progress := '200';
4675
4676 EXIT;
4677
4678 ELSE -- l_orig_mmtt_rec.primary_quantity < l_prim_qty_to_splt
4679 --If the MMTT does not have enough to be split, then associate the MMTT with the new MOL, *
4680 --then reduce split quantity so it will continue in the next iteration
4681
4682 IF (l_debug = 1) THEN
4683 print_debug('SPLIT_MMTT: Splitting MMTT:'||
4684 l_orig_mmtt_rec.transaction_temp_id||
4685 ' with QTY:' ||
4686 l_orig_mmtt_rec.primary_quantity||
4687 ' into QTY:' || l_prim_qty_to_splt ||
4688 ' with QTY:' ||
4689 l_orig_mmtt_rec.secondary_transaction_quantity||
4690 ' into QTY:' || l_sec_qty_to_splt ||
4691 '... Not enough',4);
4692 END IF;
4693
4694 l_progress := '210';
4695 -- Make sure that these values are not less than zero
4696 IF (x_orig_mol_rec.quantity_delivered < 0) THEN
4697 x_orig_mol_rec.quantity_delivered := 0;
4698 END IF;
4699
4700 IF (x_orig_mol_rec.quantity_detailed < 0) THEN
4701 x_orig_mol_rec.quantity_detailed := 0;
4702 END IF;
4703
4704 IF (x_orig_mol_rec.secondary_quantity_delivered < 0) THEN
4705 x_orig_mol_rec.secondary_quantity_delivered := 0;
4706 END IF;
4707
4708 IF (x_orig_mol_rec.secondary_quantity_detailed < 0) THEN
4709 x_orig_mol_rec.secondary_quantity_detailed := 0;
4710 END IF;
4711
4712 l_progress := '220';
4713
4714 BEGIN
4715 UPDATE mtl_material_transactions_temp
4716 SET move_order_line_id = x_new_mol_rec.line_id
4717 WHERE transaction_temp_id =
4718 l_orig_mmtt_rec.transaction_temp_id;
4719 EXCEPTION
4720 WHEN OTHERS THEN
4721 IF (l_debug = 1) THEN
4722 print_debug('SPLIT_MMTT: Error updating MMTT with id:'
4723 || l_orig_mmtt_rec.transaction_temp_id, 4);
4724 END IF;
4725 RAISE fnd_api.g_exc_error;
4726 END;
4727
4728 l_progress := '230';
4729
4730 -- Delete from putawau_temp_table if necessary
4731 IF (p_updt_putaway_temp_tbl = fnd_api.g_true) THEN
4732 BEGIN
4733 UPDATE
4734 wms_putaway_group_tasks_gtmp
4735 SET
4736 move_order_line_id = x_new_mol_rec.line_id
4737 WHERE transaction_temp_id = l_orig_mmtt_rec.transaction_temp_id
4738 AND move_order_line_id = x_orig_mol_rec.line_id
4739 AND row_type = 'All Task';
4740 EXCEPTION
4741 WHEN OTHERS THEN
4742 IF (l_debug = 1) THEN
4743 print_debug('Error updating putaway temp table',
4744 4);
4745 END IF;
4746 RAISE fnd_api.g_exc_error;
4747 END;
4748 END IF;
4749 l_progress := '240';
4750
4751 IF (l_orig_mmtt_rec.transaction_uom <> x_orig_mol_rec.uom_code) THEN
4752 l_temp := inv_rcv_cache.convert_qty
4753 (p_inventory_item_id => x_orig_mol_rec.inventory_item_id
4754 ,p_from_qty => l_orig_mmtt_rec.transaction_quantity
4755 ,p_from_uom_code => l_orig_mmtt_rec.transaction_uom
4756 ,p_to_uom_code => x_orig_mol_rec.uom_code
4757 );
4758 ELSE
4759 l_temp := l_orig_mmtt_rec.transaction_quantity;
4760 END IF;
4761
4762 l_progress := '250';
4763
4764 IF (p_remaining_mmtt_splt = 'N') THEN
4765 -- don't update qty, since it is updated in first loop
4766 -- Update new MOL with the increased quantity
4767 x_new_mol_rec.primary_quantity := x_new_mol_rec.primary_quantity + l_orig_mmtt_rec.primary_quantity;
4768 x_new_mol_rec.quantity := x_new_mol_rec.quantity + l_temp;
4769 x_new_mol_rec.secondary_quantity := x_new_mol_rec.secondary_quantity + l_orig_mmtt_rec.secondary_transaction_quantity;
4770
4771 -- Update original MOL with the reduced quantity
4772 x_orig_mol_rec.primary_quantity := x_orig_mol_rec.primary_quantity - l_orig_mmtt_rec.primary_quantity;
4773 x_orig_mol_rec.quantity := x_orig_mol_rec.quantity - l_temp;
4774 x_orig_mol_rec.secondary_quantity := x_orig_mol_rec.secondary_quantity - l_orig_mmtt_rec.secondary_transaction_quantity;
4775 END IF;
4776
4777 x_new_mol_rec.quantity_detailed := x_new_mol_rec.quantity_detailed + l_temp;
4778 x_new_mol_rec.secondary_quantity_detailed := x_new_mol_rec.secondary_quantity_detailed + l_orig_mmtt_rec.secondary_transaction_quantity;
4779
4780 x_orig_mol_rec.quantity_detailed := x_orig_mol_rec.quantity_detailed - l_temp;
4781 x_orig_mol_rec.secondary_quantity_detailed := x_orig_mol_rec.secondary_quantity_detailed - l_orig_mmtt_rec.secondary_transaction_quantity;
4782
4783 -- Update p_mo_splt_tb and l_qty_to_splt so that
4784 -- the new qty will be looked at in the next iteration
4785 l_prim_qty_to_splt := l_prim_qty_to_splt-l_orig_mmtt_rec.primary_quantity;
4786 l_qty_to_splt := l_qty_to_splt - l_temp;
4787 l_sec_qty_to_splt := l_sec_qty_to_splt-l_orig_mmtt_rec.secondary_transaction_quantity;
4788
4789 l_progress := '260';
4790 IF (l_debug = 1) THEN
4791 print_debug('SPLIT_MMTT: Need to look at next MMTT', 4);
4792 print_debug('SPLIT_MMTT - MOL:'||x_orig_mol_rec.line_id ||
4793 ' QTY:'||x_orig_mol_rec.quantity||
4794 ' PRIM_QTY:'||x_orig_mol_rec.quantity ||
4795 ' QTY_DTD:'||x_orig_mol_rec.quantity_detailed||
4796 ' QTY_DLVD:'||x_orig_mol_rec.quantity_delivered||
4797 ' SEC QTY:'||x_orig_mol_rec.secondary_quantity||
4798 ' SEC QTY_DTD:'||x_orig_mol_rec.secondary_quantity_detailed||
4799 ' SEC QTY_DLVD:'||x_orig_mol_rec.secondary_quantity_delivered,4
4800 );
4801 print_debug('SPLIT_MMTT - MOL:'||x_new_mol_rec.line_id ||
4802 ' QTY:'||x_new_mol_rec.quantity||
4803 ' PRIM_QTY:'||x_new_mol_rec.quantity ||
4804 ' QTY_DTD:'||x_new_mol_rec.quantity_detailed||
4805 ' QTY_DLVD:'||x_orig_mol_rec.quantity_delivered||
4806 ' SEC QTY:'||x_new_mol_rec.secondary_quantity ||
4807 ' SEC QTY_DTD:'||x_new_mol_rec.secondary_quantity_detailed||
4808 ' SEC QTY_DLVD:'||x_orig_mol_rec.secondary_quantity_delivered,4
4809 );
4810 END IF;
4811 END IF; --IF (l_orig_mmtt_rec.primary_quantity > l_prim_qty_to_splt)
4812 ELSE
4813 IF (l_debug = 1) THEN
4814 print_debug('Skipping this MMTT:'||l_orig_mmtt_rec.transaction_temp_id,4);
4815 END IF;
4816 END IF; --IF (l_skip_iteration <> 'Y') THEN
4817
4818 END LOOP;
4819
4820 IF (mmtt_cur%isopen) THEN
4821 CLOSE mmtt_cur;
4822 END IF;
4823
4824 EXCEPTION
4825 WHEN OTHERS THEN
4826 IF (l_debug = 1) THEN
4827 print_debug('SPLIT_MMTT: Exception occurred after progress = ' ||
4828 l_progress || ' SQLCODE = ' || SQLCODE,4);
4829 END IF;
4830 IF (mmtt_cur%isopen) THEN
4831 CLOSE mmtt_cur;
4832 END IF;
4833 x_return_status := g_ret_sts_unexp_err;
4834 fnd_msg_pub.count_and_get
4835 ( p_count => x_msg_count
4836 ,p_data => x_msg_data );
4837 END split_mmtt;
4838
4839
4840 PROCEDURE split_mo
4841 (p_orig_mol_id IN NUMBER,
4842 p_mo_splt_tb IN OUT nocopy mo_in_tb_tp,
4843 p_updt_putaway_temp_tbl IN VARCHAR2 DEFAULT fnd_api.g_false,
4844 p_txn_header_id IN NUMBER DEFAULT NULL,
4845 p_operation_type IN VARCHAR2 DEFAULT NULL,
4846 x_return_status OUT NOCOPY VARCHAR2,
4847 x_msg_count OUT NOCOPY NUMBER,
4848 x_msg_data OUT NOCOPY VARCHAR2
4849 )
4850 IS
4851
4852 l_orig_mol_rec mol_rec;
4853 l_new_mol_rec mol_rec;
4854 l_total_to_split NUMBER;
4855 l_line_num NUMBER;
4856 l_prim_uom_code VARCHAR2(3);
4857 -- OPMCOnvergence
4858 l_sec_uom_code VARCHAR2(3);
4859 l_qty_to_splt number_tb_type;
4860 -- OPMCovergence
4861 l_sec_qty_to_splt number_tb_type;
4862 l_remaining_prim_qty NUMBER;
4863 l_remaining_qty NUMBER;
4864 -- OPMCovergence
4865 l_remaining_sec_qty NUMBER;
4866 l_debug NUMBER := Nvl(fnd_profile.value('INV_DEBUG_TRACE'), 0);
4867 l_progress VARCHAR2(10) := '00';
4868
4869 l_SECONDARY_QUANTITY NUMBER; --OPM Convergence
4870 l_SECONDARY_QUANTITY_DELIVERED NUMBER; --OPM Convergence
4871 l_SECONDARY_QUANTITY_DETAILED number;--OPM Convergence
4872 l_SECONDARY_REQUIRED_QUANTITY number;--OPM Convergence
4873
4874 l_new_wdd_id NUMBER; --R12: XDOCK EXE
4875 l_new_reservation_id NUMBER;--R12: XDOCK EXE
4876 l_doc_type NUMBER;
4877 l_rsv_query_rec inv_reservation_global.mtl_reservation_rec_type; --R12: XDOCK EXE
4878 l_rsv_results_tbl inv_reservation_global.mtl_reservation_tbl_type;
4879 l_rsv_update_rec inv_reservation_global.mtl_reservation_rec_type;
4880 l_rsv_results_count NUMBER;
4881 l_dummy_serial inv_reservation_global.serial_number_tbl_type;
4882 l_error_code NUMBER;
4883
4884 BEGIN
4885 SAVEPOINT split_mo_pub;
4886
4887 x_return_status := g_ret_sts_success;
4888
4889 l_progress := '10';
4890
4891 IF (l_debug = 1) THEN
4892 print_debug('SPLIT_MO - Entering ...',4);
4893 END IF;
4894
4895 /* Retrieve MOL with the given line_id */
4896 BEGIN
4897 SELECT
4898 line_id
4899 ,header_id
4900 ,quantity
4901 ,primary_quantity
4902 ,Nvl(quantity_delivered,0)
4903 ,Nvl(quantity_detailed,0)
4904 ,uom_code
4905 ,inventory_item_id
4906 ,organization_id
4907 , SECONDARY_UOM_CODE --OPM Convergence
4908 , NVL(SECONDARY_QUANTITY,0) --OPM Convergence
4909 , NVL(SECONDARY_QUANTITY_DELIVERED,0) --OPM Convergence
4910 , NVL(SECONDARY_QUANTITY_DETAILED,0) --OPM Convergence
4911 , NVL(SECONDARY_REQUIRED_QUANTITY,0) --OPM Convergence
4912 , backorder_delivery_detail_id --R12: XDOCK EXE
4913 , crossdock_type --R12: XDOCK EXE
4914 INTO
4915 l_orig_mol_rec.line_id
4916 ,l_orig_mol_rec.header_id
4917 ,l_orig_mol_rec.quantity
4918 ,l_orig_mol_rec.primary_quantity
4919 ,l_orig_mol_rec.quantity_delivered
4920 ,l_orig_mol_rec.quantity_detailed
4921 ,l_orig_mol_rec.uom_code
4922 ,l_orig_mol_rec.inventory_item_id
4923 ,l_orig_mol_rec.organization_id
4924 ,l_orig_mol_rec.SECONDARY_UOM --OPM Convergence
4925 ,l_orig_mol_rec.SECONDARY_QUANTITY --OPM Convergence
4926 ,l_orig_mol_rec.SECONDARY_QUANTITY_DELIVERED --OPM Convergence
4927 ,l_orig_mol_rec.SECONDARY_QUANTITY_DETAILED --OPM Convergence
4928 ,l_orig_mol_rec.SECONDARY_REQUIRED_QUANTITY --OPM Convergence
4929 ,l_orig_mol_rec.backorder_delivery_detail_id
4930 ,l_orig_mol_rec.crossdock_type
4931 FROM
4932 mtl_txn_request_lines
4933 WHERE
4934 line_id = p_orig_mol_id;
4935
4936 EXCEPTION
4937 WHEN OTHERS THEN
4938 IF (l_debug = 1) THEN
4939 print_debug('SPLIT_MO: Unable to find original MOL!',4);
4940 print_debug('SPLIT_MO: SQLCODE = ' || SQLCODE, 4);
4941 END IF;
4942 END;
4943
4944 -- Original MOL shouldn't have quantity <= 0
4945 IF (l_orig_mol_rec.primary_quantity <= 0) THEN
4946 IF (l_debug = 1) THEN
4947 print_debug('SPLIT_MO ERROR: Original quantity is <= 0', 4);
4948 END IF;
4949 fnd_message.set_name('INV', 'INV_INVALID_QTY');
4950 fnd_msg_pub.ADD;
4951 RAISE fnd_api.g_exc_error;
4952 END IF;
4953
4954 l_progress := '20';
4955
4956 -- Needs the primary_uom_code for quantities conversion
4957 l_prim_uom_code := inv_rcv_cache.get_primary_uom_code(l_orig_mol_rec.organization_id,
4958 l_orig_mol_rec.inventory_item_id);
4959
4960 l_sec_uom_code := l_orig_mol_rec.secondary_uom;
4961
4962 l_progress := '25';
4963
4964 -- Validate that there are enough to be split
4965 -- l_total_to_split will be in l_orig_mol_rec.uom_code
4966 l_total_to_split := 0;
4967
4968 FOR l_indx IN 1 .. p_mo_splt_tb.COUNT LOOP
4969 -- Make sure that p_mo_splt_tb contains valid entries
4970 IF (p_mo_splt_tb(l_indx).prim_qty <= 0) THEN
4971 IF (l_debug = 1) THEN
4972 print_debug('SPLIT_MO - ERROR: Quantity to split is <= 0', 4);
4973 END IF;
4974 fnd_message.set_name('INV', 'INV_INVALID_QTY');
4975 fnd_msg_pub.ADD;
4976 RAISE fnd_api.g_exc_error;
4977 END IF;
4978
4979 -- convert prim quantities to transaction uom in mol first
4980 IF (l_orig_mol_rec.uom_code = l_prim_uom_code) THEN
4981 l_qty_to_splt(l_indx) := p_mo_splt_tb(l_indx).prim_qty;
4982 ELSE
4983
4984 l_qty_to_splt(l_indx) := inv_rcv_cache.convert_qty
4985 (p_inventory_item_id => l_orig_mol_rec.inventory_item_id
4986 ,p_from_qty => p_mo_splt_tb(l_indx).prim_qty
4987 ,p_from_uom_code => l_prim_uom_code
4988 ,p_to_uom_code => l_orig_mol_rec.uom_code
4989 );
4990 END IF;
4991 l_sec_qty_to_splt(l_indx) := p_mo_splt_tb(l_indx).sec_qty;
4992 l_total_to_split := l_total_to_split + l_qty_to_splt(l_indx);
4993 END LOOP;
4994
4995 l_progress := '30';
4996
4997 IF (l_debug = 1) THEN
4998 print_debug('SPLIT_MO: Original MOL state:', 4);
4999 print_debug('SPLIT_MO: MOL:'||l_orig_mol_rec.line_id ||
5000 ' QTY:'||l_orig_mol_rec.quantity||
5001 ' PRIM_QTY:'||l_orig_mol_rec.quantity ||
5002 ' QTY_DTD:'||l_orig_mol_rec.quantity_detailed||
5003 ' QTY_DLVD:'||l_orig_mol_rec.quantity_delivered,4
5004 );
5005 print_debug('SPLIT_MO: MOL:'||l_orig_mol_rec.line_id ||
5006 ' SEC_QTY:'||l_orig_mol_rec.secondary_quantity||
5007 ' SEC_QTY_DTD:'||l_orig_mol_rec.secondary_quantity_detailed||
5008 ' SEC_QTY_DLVD:'||l_orig_mol_rec.secondary_quantity_delivered,4
5009 );
5010 END IF;
5011
5012
5013 IF((l_orig_mol_rec.quantity-l_orig_mol_rec.quantity_delivered) < l_total_to_split) THEN
5014 /* not enough to be split */
5015 IF (l_debug = 1) THEN
5016 print_debug('SPLIT_MO - ERROR: Original QTY only '||l_orig_mol_rec.quantity
5017 || ', not enough to split ' || l_total_to_split, 4);
5018 END IF;
5019 fnd_message.set_name('INV', 'INV_INSUFFICIENT_QTY');
5020 fnd_msg_pub.ADD;
5021 RAISE fnd_api.g_exc_error;
5022 END IF;
5023
5024 l_progress := '40';
5025
5026 -- Get max line num, which is used to create unique line_num
5027 SELECT MAX(line_number)
5028 INTO l_line_num
5029 FROM mtl_txn_request_lines
5030 WHERE header_id = l_orig_mol_rec.header_id;
5031
5032 l_progress := '50';
5033
5034 IF (l_debug = 1) THEN
5035 print_debug('SPLIT_MO: Original MOL state:', 4);
5036 print_debug('SPLIT_MO: MOL:'||l_orig_mol_rec.line_id ||
5037 ' QTY:'||l_orig_mol_rec.quantity||
5038 ' PRIM_QTY:'||l_orig_mol_rec.quantity ||
5039 ' QTY_DTD:'||l_orig_mol_rec.quantity_detailed||
5040 ' QTY_DLVD:'||l_orig_mol_rec.quantity_delivered,4
5041 );
5042 print_debug('SPLIT_MO: MOL:'||l_orig_mol_rec.line_id ||
5043 ' SEC_QTY:'||l_orig_mol_rec.secondary_quantity||
5044 ' SEC_QTY_DTD:'||l_orig_mol_rec.secondary_quantity_detailed||
5045 ' SEC_QTY_DLVD:'||l_orig_mol_rec.secondary_quantity_delivered,4
5046 );
5047 END IF;
5048
5049 l_progress := '53';
5050
5051 -- Loop through the requested quantity table and split one-by-one
5052 FOR l_indx IN 1 .. p_mo_splt_tb.COUNT LOOP
5053
5054 IF (l_indx = p_mo_splt_tb.COUNT AND ( l_qty_to_splt(l_indx) = l_orig_mol_rec.quantity-l_orig_mol_rec.quantity_delivered) )
5055 THEN
5056 -- If the last entry matched the orig mol quantity
5057 -- then simply assoc the last one with the original mol
5058 -- No need to create a new MOL
5059 IF (l_debug = 1) THEN
5060 print_debug('SPLIT_MO: Last Qty to split matched exactly the orig mol qty',4);
5061 END IF;
5062 p_mo_splt_tb(l_indx).line_id := p_orig_mol_id;
5063 ELSE
5064
5065 IF (l_debug = 1) THEN
5066 print_debug('SPLIT_MO: Creating new MOL for quantity ' ||
5067 p_mo_splt_tb(l_indx).prim_qty||' Sec Qty '||NVL(p_mo_splt_tb(l_indx).sec_qty,NULL),4);
5068 END IF;
5069
5070 /* Create a MOL record, with most of its fields copied from the */
5071 /* original MOL */
5072 l_progress := '55';
5073
5074 IF (l_debug = 1) THEN
5075 print_debug('SPLIT_MO - Copy MOL', 4);
5076 END IF;
5077
5078 --Initializate these fields
5079 l_new_mol_rec.header_id := l_orig_mol_rec.header_id;
5080 l_new_mol_rec.quantity := 0;
5081 l_new_mol_rec.primary_quantity := 0;
5082 l_new_mol_rec.quantity_delivered := 0;
5083 l_new_mol_rec.quantity_detailed := 0;
5084 l_new_mol_rec.uom_code := l_orig_mol_rec.uom_code;
5085 l_new_mol_rec.inventory_item_id := l_orig_mol_rec.inventory_item_id;
5086 l_new_mol_rec.organization_id := l_orig_mol_rec.organization_id;
5087 l_new_mol_rec.line_number := l_line_num + l_indx;
5088 l_new_mol_rec.backorder_delivery_detail_id := l_orig_mol_rec.backorder_delivery_detail_id; --Bug 5948720
5089
5090 --OPM Convergence
5091 l_new_mol_rec.secondary_quantity := 0;
5092 l_new_mol_rec.secondary_quantity_delivered := 0;
5093 l_new_mol_rec.secondary_quantity_detailed := 0;
5094
5095
5096 BEGIN
5097 SELECT MTL_TXN_REQUEST_LINES_S.NEXTVAL
5098 INTO l_new_mol_rec.line_id
5099 FROM dual;
5100
5101 INSERT INTO mtl_txn_request_lines
5102 (
5103 LINE_ID
5104 ,HEADER_ID
5105 ,LINE_NUMBER
5106 ,ORGANIZATION_ID
5107 ,INVENTORY_ITEM_ID
5108 ,REVISION
5109 ,FROM_SUBINVENTORY_ID
5110 ,FROM_SUBINVENTORY_CODE
5111 ,FROM_LOCATOR_ID
5112 ,TO_SUBINVENTORY_CODE
5113 ,TO_SUBINVENTORY_ID
5114 ,TO_LOCATOR_ID
5115 ,TO_ACCOUNT_ID
5116 ,SHIP_TO_LOCATION_ID
5117 ,LOT_NUMBER
5118 ,SERIAL_NUMBER_START
5119 ,SERIAL_NUMBER_END
5120 ,UOM_CODE
5121 ,QUANTITY
5122 ,QUANTITY_DELIVERED
5123 ,QUANTITY_DETAILED
5124 ,DATE_REQUIRED
5125 ,REASON_ID
5126 ,REFERENCE
5127 ,REFERENCE_TYPE_CODE
5128 ,REFERENCE_ID
5129 ,REFERENCE_DETAIL_ID
5130 ,ASSIGNMENT_ID
5131 ,PROJECT_ID
5132 ,TASK_ID
5133 ,TRANSACTION_HEADER_ID
5134 ,LINE_STATUS
5135 ,STATUS_DATE
5136 ,LAST_UPDATED_BY
5137 ,LAST_UPDATE_LOGIN
5138 ,LAST_UPDATE_DATE
5139 ,CREATED_BY
5140 ,CREATION_DATE
5141 ,REQUEST_ID
5142 ,PROGRAM_APPLICATION_ID
5143 ,PROGRAM_ID
5144 ,PROGRAM_UPDATE_DATE
5145 ,ATTRIBUTE1
5146 ,ATTRIBUTE2
5147 ,ATTRIBUTE3
5148 ,ATTRIBUTE4
5149 ,ATTRIBUTE5
5150 ,ATTRIBUTE6
5151 ,ATTRIBUTE7
5152 ,ATTRIBUTE8
5153 ,ATTRIBUTE9
5154 ,ATTRIBUTE10
5155 ,ATTRIBUTE11
5156 ,ATTRIBUTE12
5157 ,ATTRIBUTE13
5158 ,ATTRIBUTE14
5159 ,ATTRIBUTE15
5160 ,ATTRIBUTE_CATEGORY
5161 ,TXN_SOURCE_ID
5162 ,TXN_SOURCE_LINE_ID
5163 ,TXN_SOURCE_LINE_DETAIL_ID
5164 ,TRANSACTION_TYPE_ID
5165 ,TRANSACTION_SOURCE_TYPE_ID
5166 ,PRIMARY_QUANTITY
5167 ,TO_ORGANIZATION_ID
5168 ,PUT_AWAY_STRATEGY_ID
5169 ,PICK_STRATEGY_ID
5170 ,UNIT_NUMBER
5171 ,FROM_COST_GROUP_ID
5172 ,TO_COST_GROUP_ID
5173 ,LPN_ID
5174 ,TO_LPN_ID
5175 ,INSPECTION_STATUS
5176 ,PICK_METHODOLOGY_ID
5177 ,CONTAINER_ITEM_ID
5178 ,CARTON_GROUPING_ID
5179 ,BACKORDER_DELIVERY_DETAIL_ID
5180 ,WMS_PROCESS_FLAG
5181 ,PICK_SLIP_NUMBER
5182 ,PICK_SLIP_DATE
5183 ,SHIP_SET_ID
5184 ,SHIP_MODEL_ID
5185 ,MODEL_QUANTITY
5186 ,CROSSDOCK_TYPE
5187 ,REQUIRED_QUANTITY
5188 ,SECONDARY_QUANTITY --OPM Convergence
5189 ,SECONDARY_QUANTITY_DELIVERED --OPM Convergence
5190 ,SECONDARY_QUANTITY_DETAILED --OPM Convergence
5191 ,WIP_ENTITY_ID --Bug 5934992
5192 ,OPERATION_SEQ_NUM --Bug 5948720
5193 ,WIP_SUPPLY_TYPE --Bug 5948720
5194 )
5195 SELECT
5196 l_new_mol_rec.line_id --LINE_ID
5197 ,HEADER_ID
5198 ,l_new_mol_rec.line_number --LINE_NUMBER
5199 ,ORGANIZATION_ID
5200 ,INVENTORY_ITEM_ID
5201 ,REVISION
5202 ,FROM_SUBINVENTORY_ID
5203 ,FROM_SUBINVENTORY_CODE
5204 ,FROM_LOCATOR_ID
5205 ,TO_SUBINVENTORY_CODE
5206 ,TO_SUBINVENTORY_ID
5207 ,TO_LOCATOR_ID
5208 ,TO_ACCOUNT_ID
5209 ,SHIP_TO_LOCATION_ID
5210 ,LOT_NUMBER
5211 ,SERIAL_NUMBER_START
5212 ,SERIAL_NUMBER_END
5213 ,UOM_CODE
5214 ,0 --QUANTITY
5215 ,0 --QUANTITY_DELIVERED
5216 ,0 --QUANTITY_DETAILED
5217 ,DATE_REQUIRED
5218 ,REASON_ID
5219 ,REFERENCE
5220 ,REFERENCE_TYPE_CODE
5221 ,REFERENCE_ID
5222 ,REFERENCE_DETAIL_ID
5223 ,ASSIGNMENT_ID
5224 ,PROJECT_ID
5225 ,TASK_ID
5226 ,TRANSACTION_HEADER_ID
5227 ,LINE_STATUS
5228 ,STATUS_DATE
5229 ,LAST_UPDATED_BY
5230 ,LAST_UPDATE_LOGIN
5231 ,Sysdate --LAST_UPDATE_DATE
5232 ,CREATED_BY
5233 ,Sysdate --CREATION_DATE
5234 ,REQUEST_ID
5235 ,PROGRAM_APPLICATION_ID
5236 ,PROGRAM_ID
5237 ,PROGRAM_UPDATE_DATE
5238 ,ATTRIBUTE1
5239 ,ATTRIBUTE2
5240 ,ATTRIBUTE3
5241 ,ATTRIBUTE4
5242 ,ATTRIBUTE5
5243 ,ATTRIBUTE6
5244 ,ATTRIBUTE7
5245 ,ATTRIBUTE8
5246 ,ATTRIBUTE9
5247 ,ATTRIBUTE10
5248 ,ATTRIBUTE11
5249 ,ATTRIBUTE12
5250 ,ATTRIBUTE13
5251 ,ATTRIBUTE14
5252 ,ATTRIBUTE15
5253 ,ATTRIBUTE_CATEGORY
5254 ,TXN_SOURCE_ID
5255 ,TXN_SOURCE_LINE_ID
5256 ,TXN_SOURCE_LINE_DETAIL_ID
5257 ,TRANSACTION_TYPE_ID
5258 ,TRANSACTION_SOURCE_TYPE_ID
5259 ,0 --PRIMARY_QUANTITY
5260 ,TO_ORGANIZATION_ID
5261 ,PUT_AWAY_STRATEGY_ID
5262 ,PICK_STRATEGY_ID
5263 ,UNIT_NUMBER
5264 ,FROM_COST_GROUP_ID
5265 ,TO_COST_GROUP_ID
5266 ,LPN_ID
5267 ,TO_LPN_ID
5268 ,INSPECTION_STATUS
5269 ,PICK_METHODOLOGY_ID
5270 ,CONTAINER_ITEM_ID
5271 ,CARTON_GROUPING_ID
5272 ,BACKORDER_DELIVERY_DETAIL_ID
5273 ,WMS_PROCESS_FLAG
5274 ,PICK_SLIP_NUMBER
5275 ,PICK_SLIP_DATE
5276 ,SHIP_SET_ID
5277 ,SHIP_MODEL_ID
5278 ,MODEL_QUANTITY
5279 ,CROSSDOCK_TYPE
5280 ,REQUIRED_QUANTITY
5281 ,0--SECONDARY_QUANTITY --OPM Convergence
5282 ,0--SECONDARY_QUANTITY_DELIVERED --OPM Convergence
5283 ,0--SECONDARY_QUANTITY_DETAILED --OPM Convergence
5284 ,WIP_ENTITY_ID --Bug 5934992
5285 ,OPERATION_SEQ_NUM --Bug 5948720
5286 ,WIP_SUPPLY_TYPE --Bug 5948720
5287 FROM mtl_txn_request_lines
5288 WHERE line_id = l_orig_mol_rec.line_id;
5289 EXCEPTION
5290 WHEN OTHERS THEN
5291 IF (l_debug = 1) THEN
5292 print_debug('SPLIT_MO: Error copying move order lines', 4);
5293 print_debug('SPLIT_MO: SQLCODE = ' || SQLCODE, 4);
5294 END IF;
5295 fnd_message.set_name('WMS','WMS_MO_CREATE_FAIL');
5296 fnd_msg_pub.add;
5297 RAISE fnd_api.g_exc_error;
5298 END;
5299
5300 l_progress := '60';
5301 IF (l_debug = 1) THEN
5302 print_debug('SPLIT_MO - MOL sucessfully created with line_id = '||
5303 l_new_mol_rec.line_id || ' , ROWCOUNT = ' ||
5304 SQL%ROWCOUNT,4);
5305 END IF;
5306
5307 l_progress := '62';
5308
5309 split_mmtt(x_orig_mol_rec => l_orig_mol_rec
5310 ,x_new_mol_rec => l_new_mol_rec
5311 ,x_return_status => x_return_status
5312 ,x_msg_count => x_msg_count
5313 ,x_msg_data => x_msg_data
5314 ,p_prim_qty_to_splt => p_mo_splt_tb(l_indx).prim_qty
5315 ,p_qty_to_splt => l_qty_to_splt(l_indx)
5316 ,p_prim_uom_code => l_prim_uom_code
5317 -- OPM COnvergence
5318 ,p_sec_qty_to_splt => l_sec_qty_to_splt(l_indx)
5319 ,p_sec_uom_code => l_sec_uom_code
5320 ,p_updt_putaway_temp_tbl => p_updt_putaway_temp_tbl
5321 ,p_txn_header_id => p_txn_header_id
5322 ,p_txn_temp_id => p_txn_header_id
5323 ,p_remaining_mmtt_splt => 'N'
5324 ,p_operation_type => p_operation_type);
5325 IF (x_return_status <> g_ret_sts_success) THEN
5326 IF (l_debug = 1) THEN
5327 print_debug('SPLIT_MO: Error in split_mmtt',4);
5328 END IF;
5329 RAISE fnd_api.g_exc_error;
5330 END IF;
5331
5332 l_progress := '64';
5333
5334 IF (l_orig_mol_rec.quantity_detailed > l_orig_mol_rec.quantity) THEN
5335 IF (p_txn_header_id IS NULL) THEN
5336 IF (l_debug = 1) THEN
5337 print_debug('SPLIT_MO: Not possible',4);
5338 END IF;
5339 RAISE fnd_api.g_exc_error;
5340 ELSE -- p_txn_header_id is not null
5341 l_remaining_qty := l_orig_mol_rec.quantity_detailed - l_orig_mol_rec.quantity;
5342 IF (l_prim_uom_code <> l_orig_mol_rec.uom_code) THEN
5343 l_remaining_prim_qty := inv_rcv_cache.convert_qty
5344 (p_inventory_item_id => l_orig_mol_rec.inventory_item_id
5345 ,p_from_qty => l_remaining_qty
5346 ,p_from_uom_code => l_orig_mol_rec.uom_code
5347 ,p_to_uom_code => l_prim_uom_code
5348 );
5349 ELSE
5350 l_remaining_prim_qty := l_remaining_qty;
5351 END IF; --IF (l_prim_uom_code <> l_orig_mol_rec.uom_code)
5352 l_remaining_sec_qty := l_orig_mol_rec.secondary_quantity_detailed - l_orig_mol_rec.secondary_quantity;
5353
5354 split_mmtt(x_orig_mol_rec => l_orig_mol_rec
5355 ,x_new_mol_rec => l_new_mol_rec
5356 ,x_return_status => x_return_status
5357 ,x_msg_count => x_msg_count
5358 ,x_msg_data => x_msg_data
5359 ,p_prim_qty_to_splt => l_remaining_prim_qty
5360 ,p_qty_to_splt => l_remaining_qty
5361 ,p_prim_uom_code => l_prim_uom_code
5362 -- OPMConvergence
5363 ,p_sec_qty_to_splt => l_remaining_sec_qty
5364 ,p_sec_uom_code => l_sec_uom_code
5365 ,p_updt_putaway_temp_tbl => p_updt_putaway_temp_tbl
5366 ,p_txn_header_id => p_txn_header_id
5367 ,p_txn_temp_id => p_txn_header_id
5368 ,p_remaining_mmtt_splt => 'Y'
5369 ,p_operation_type => p_operation_type);
5370 IF (x_return_status <> g_ret_sts_success) THEN
5371 IF (l_debug = 1) THEN
5372 print_debug('SPLIT_MO: Error in split_mmtt',4);
5373 END IF;
5374 RAISE fnd_api.g_exc_error;
5375 END IF;
5376 END IF;
5377 END IF; -- IF (l_orig_mol_rec.quantity_detailed > l_orig_mol_rec.quantity)
5378
5379 l_progress := '66';
5380
5381 --R12: XDOCK EXE
5382 IF l_orig_mol_rec.backorder_delivery_detail_id IS NOT NULL AND l_orig_mol_rec.crossdock_type = 1 THEN
5383
5384 IF (l_debug = 1) THEN
5385 print_debug('Calling inv_rcv_reservation_util.split_wdd',4);
5386 END IF;
5387
5388 inv_rcv_reservation_util.split_wdd
5389 (x_return_status => x_return_status
5390 ,x_msg_count => x_msg_count
5391 ,x_msg_data => x_msg_data
5392 ,x_new_wdd_id => l_new_wdd_id
5393 ,p_wdd_id => l_orig_mol_rec.backorder_delivery_detail_id
5394 ,p_new_mol_id => l_new_mol_rec.line_id
5395 ,p_qty_to_splt => l_new_mol_rec.primary_quantity);
5396
5397 IF (l_debug = 1) THEN
5398 print_debug('Returned from inv_rcv_reservation_util.split_wdd',4);
5399 print_debug('x_return_status =>'||x_return_status,4);
5400 END IF;
5401
5402 IF (x_return_status <> fnd_api.g_ret_sts_success) THEN
5403 IF (l_debug = 1) THEN
5404 print_debug('x_msg_data: '||x_msg_data,4);
5405 print_debug('x_msg_count: '||x_msg_count,4);
5406 print_debug('SQLCODE:'||SQLCODE||' SQLERRM:'||SQLERRM,4);
5407 print_debug('Raising Exception!!!',4);
5408 END IF;
5409 l_progress := '@@@';
5410 RAISE fnd_api.g_exc_unexpected_error;
5411 END IF;
5412
5413 l_new_mol_rec.backorder_delivery_detail_id := l_new_wdd_id;
5414
5415 BEGIN
5416 SELECT Nvl(source_document_type_id, -1)
5417 INTO l_doc_type
5418 FROM wsh_delivery_details
5419 WHERE delivery_detail_id = l_orig_mol_rec.backorder_delivery_detail_id;
5420 EXCEPTION
5421 WHEN OTHERS THEN
5422 IF (l_debug = 1) THEN
5423 print_debug('Error retrieving doc type for SO',4);
5424 END IF;
5425 RAISE fnd_api.g_exc_unexpected_error;
5426 END;
5427
5428 IF l_doc_type = 10 THEN
5429 l_rsv_query_rec.demand_source_type_id := inv_reservation_global.g_source_type_internal_ord;
5430 ELSE
5431 l_rsv_query_rec.demand_source_type_id := inv_reservation_global.g_source_type_oe;
5432 END if;
5433
5434 l_rsv_query_rec.demand_source_line_detail := l_orig_mol_rec.backorder_delivery_detail_id;
5435
5436 IF (l_debug = 1) THEN
5437 print_debug('Calling inv_reservation_pub.query_reservation',4);
5438 END IF;
5439
5440 inv_reservation_pub.query_reservation
5441 (p_api_version_number => 1.0
5442 , x_return_status => x_return_status
5443 , x_msg_count => x_msg_count
5444 , x_msg_data => x_msg_data
5445 , p_query_input => l_rsv_query_rec
5446 , p_lock_records => fnd_api.g_true --???
5447 , p_sort_by_req_date => inv_reservation_global.g_query_demand_ship_date_desc--There's shoudl be just 1 row
5448 , x_mtl_reservation_tbl => l_rsv_results_tbl
5449 , x_mtl_reservation_tbl_count => l_rsv_results_count
5450 , x_error_code => l_error_code
5451 );
5452
5453 IF (l_debug = 1) THEN
5454 print_debug('Returned from inv_reservation_pub.query_reservation',4);
5455 print_debug('x_return_status: '||x_return_status,4);
5456 END IF;
5457
5458 IF (x_return_status <> fnd_api.g_ret_sts_success) THEN
5459 IF (l_debug = 1) THEN
5460 print_debug('x_error_code: '||l_error_code,4);
5461 print_debug('x_msg_data: '||x_msg_data,4);
5462 print_debug('x_msg_count: '||x_msg_count,4);
5463 print_debug('SQLCODE:'||SQLCODE||' SQLERRM:'||SQLERRM,4);
5464 print_debug('Raising Exception!!!',4);
5465 END IF;
5466 l_progress := '@@@';
5467 RAISE fnd_api.g_exc_unexpected_error;
5468 END IF;
5469
5470 l_rsv_update_rec := l_rsv_results_tbl(1);
5471 l_rsv_update_rec.demand_source_line_detail := l_new_wdd_id;
5472 l_rsv_update_rec.primary_reservation_quantity := l_new_mol_rec.primary_quantity;
5473 l_rsv_update_rec.reservation_quantity :=
5474 inv_rcv_cache.convert_qty
5475 (p_inventory_item_id => l_new_mol_rec.inventory_item_id
5476 ,p_from_qty => l_new_mol_rec.primary_quantity
5477 ,p_from_uom_code => l_rsv_results_tbl(1).primary_uom_code
5478 ,p_to_uom_code => l_rsv_results_tbl(1).reservation_uom_code
5479 );
5480
5481 IF (l_debug = 1) THEN
5482 print_debug('Calling inv_reservation_pub.transfer_reservation',4);
5483 END IF;
5484
5485 inv_reservation_pub.transfer_reservation
5486 (p_api_version_number => 1.0
5487 ,x_return_status => x_return_status
5488 ,x_msg_count => x_msg_count
5489 ,x_msg_data => x_msg_data
5490 ,p_original_rsv_rec => l_rsv_results_tbl(1)
5491 ,p_to_rsv_rec => l_rsv_update_rec
5492 ,p_original_serial_number => l_dummy_serial
5493 ,p_to_serial_number => l_dummy_serial
5494 ,p_validation_flag => fnd_api.g_false --??
5495 ,x_to_reservation_id => l_new_reservation_id);
5496
5497 IF (l_debug = 1) THEN
5498 print_debug('Returned from inv_reservation_pub.transfer_reservation',4);
5499 print_debug('x_return_status =>'||x_return_status,4);
5500 print_debug('x_to_reservation_id =>'||l_new_reservation_id,4);
5501 END IF;
5502
5503 IF (x_return_status <> fnd_api.g_ret_sts_success) THEN
5504 IF (l_debug = 1) THEN
5505 print_debug('x_msg_data: '||x_msg_data,4);
5506 print_debug('x_msg_count: '||x_msg_count,4);
5507 print_debug('SQLCODE:'||SQLCODE||' SQLERRM:'||SQLERRM,4);
5508 print_debug('Raising Exception!!!',4);
5509 END IF;
5510 l_progress := '@@@';
5511 RAISE fnd_api.g_exc_unexpected_error;
5512 END IF;
5513
5514 END IF;--IF l_orig_mol_rec.backorder_delivery_detail_id IS NOT NULL
5515 --R12: XDOCK EXE END
5516
5517 IF (l_debug = 1) THEN
5518 print_debug('SPLIT_MO - Update new MOL:'
5519 ||l_new_mol_rec.line_id||' with quantity = ' ||
5520 l_new_mol_rec.quantity || ' qty_dlvd = ' ||
5521 l_new_mol_rec.quantity_delivered || ' qty_dtld = ' ||
5522 l_new_mol_rec.quantity_detailed, 4);
5523
5524 print_debug('with sec quantity = ' ||
5525 l_new_mol_rec.secondary_quantity || ' sec_qty_dlvd = ' ||
5526 l_new_mol_rec.secondary_quantity_delivered || ' sec_qty_dtld = ' ||
5527 l_new_mol_rec.secondary_quantity_detailed, 4);
5528 END IF;
5529
5530 BEGIN
5531 IF (l_orig_mol_rec.SECONDARY_UOM IS NOT NULL) THEN
5532 UPDATE
5533 mtl_txn_request_lines
5534 SET
5535 primary_quantity = l_new_mol_rec.primary_quantity
5536 ,quantity = l_new_mol_rec.quantity
5537 ,quantity_delivered = l_new_mol_rec.quantity_delivered
5538 ,quantity_detailed = l_new_mol_rec.quantity_detailed
5539 -- OPM COnvergence
5540 ,secondary_quantity = l_new_mol_rec.secondary_quantity
5541 ,secondary_quantity_delivered = l_new_mol_rec.secondary_quantity_delivered
5542 ,secondary_quantity_detailed = l_new_mol_rec.secondary_quantity_detailed
5543 ,last_update_date = Sysdate
5544 ,backorder_delivery_detail_id = l_new_mol_rec.backorder_delivery_detail_id
5545 WHERE
5546 line_id = l_new_mol_rec.line_id;
5547 ELSE
5548 UPDATE
5549 mtl_txn_request_lines
5550 SET
5551 primary_quantity = l_new_mol_rec.primary_quantity
5552 ,quantity = l_new_mol_rec.quantity
5553 ,quantity_delivered = l_new_mol_rec.quantity_delivered
5554 ,quantity_detailed = l_new_mol_rec.quantity_detailed
5555 ,last_update_date = Sysdate
5556 ,backorder_delivery_detail_id = l_new_mol_rec.backorder_delivery_detail_id
5557 WHERE
5558 line_id = l_new_mol_rec.line_id;
5559
5560
5561 END IF;
5562
5563 EXCEPTION
5564 WHEN OTHERS THEN
5565 IF (l_debug = 1) THEN
5566 print_debug('SPLIT_MO: Error update new mol record',
5567 4);
5568 END IF;
5569 RAISE fnd_api.g_exc_error;
5570 END;
5571
5572 l_progress := '190';
5573
5574 -- Update the p_mo_splt_tb with the new MOL line_id
5575 p_mo_splt_tb(l_indx).line_id := l_new_mol_rec.line_id;
5576 p_mo_splt_tb(l_indx).wdd_id := l_new_mol_rec.backorder_delivery_detail_id;
5577 p_mo_splt_tb(l_indx).reservation_id := l_new_reservation_id;
5578
5579 IF (l_debug = 1) THEN
5580 print_debug('SPLIT_MO: Final MOL State in this iteration:',4);
5581 print_debug('SPLIT_MO ORIG: MOL:'||l_orig_mol_rec.line_id ||
5582 ' QTY:'||l_orig_mol_rec.quantity||
5583 ' PRIM_QTY:'||l_orig_mol_rec.quantity ||
5584 ' QTY_DTD:'||l_orig_mol_rec.quantity_detailed||
5585 ' QTY_DLVD:'||l_orig_mol_rec.quantity_delivered||
5586 ' SEC_QTY:'||l_orig_mol_rec.secondary_quantity||
5587 ' SEC_QTY_DTD:'||l_orig_mol_rec.secondary_quantity_detailed||
5588 ' SEC_QTY_DLVD:'||l_orig_mol_rec.secondary_quantity_delivered,4
5589 );
5590
5591 print_debug('SPLIT_MO NEW: MOL:'||l_new_mol_rec.line_id ||
5592 ' QTY:'||l_new_mol_rec.quantity||
5593 ' PRIM_QTY:'||l_new_mol_rec.quantity ||
5594 ' QTY_DTD:'||l_new_mol_rec.quantity_detailed||
5595 ' QTY_DLVD:'||l_orig_mol_rec.quantity_delivered||
5596 ' SEC_QTY:'||l_new_mol_rec.secondary_quantity ||
5597 ' SEC_QTY_DTD:'||l_new_mol_rec.secondary_quantity_detailed||
5598 ' SEC_QTY_DLVD:'||l_orig_mol_rec.secondary_quantity_delivered,4
5599 );
5600 END IF;
5601 END IF; --(l_indx = p_mo_splt_tb.COUNT AND ( l_qty_to_splt(l_indx) = l_orig_mol_rec.quantity-l_orig_mol_rec.quantity_delivered) )
5602 END LOOP;
5603
5604
5605 -- At the end, needs to update original MOL
5606 -- Make sure that these values are not less than zero
5607 IF (l_orig_mol_rec.quantity_delivered < 0) THEN
5608 l_orig_mol_rec.quantity_delivered := 0;
5609 END IF;
5610
5611 IF (l_orig_mol_rec.quantity_detailed < 0) THEN
5612 l_orig_mol_rec.quantity_detailed := 0;
5613 END IF;
5614
5615 IF (l_orig_mol_rec.secondary_quantity_delivered < 0) THEN
5616 l_orig_mol_rec.secondary_quantity_delivered := 0;
5617 END IF;
5618
5619 IF (l_orig_mol_rec.secondary_quantity_detailed < 0) THEN
5620 l_orig_mol_rec.secondary_quantity_detailed := 0;
5621 END IF;
5622
5623 IF (l_debug = 1) THEN
5624 print_debug('SPLIT_MO - Update original MOL:'
5625 ||l_orig_mol_rec.line_id ||' with quantity = ' ||
5626 l_orig_mol_rec.quantity || ' qty_dlvd = ' ||
5627 l_orig_mol_rec.quantity_delivered || ' qty_dtld = ' ||
5628 l_orig_mol_rec.quantity_detailed||' with sec quantity = ' ||
5629 l_orig_mol_rec.secondary_quantity || ' sec_qty_dlvd = ' ||
5630 l_orig_mol_rec.secondary_quantity_delivered || ' sec_qty_dtld = ' ||
5631 l_orig_mol_rec.secondary_quantity_detailed,4);
5632 END IF;
5633
5634 l_progress := '200';
5635 BEGIN
5636 IF (l_orig_mol_rec.SECONDARY_UOM IS NOT NULL) THEN
5637 UPDATE
5638 mtl_txn_request_lines
5639 SET
5640 primary_quantity = l_orig_mol_rec.primary_quantity
5641 ,quantity = l_orig_mol_rec.quantity
5642 ,quantity_delivered = l_orig_mol_rec.quantity_delivered
5643 ,quantity_detailed = l_orig_mol_rec.quantity_detailed
5644 -- OPM COnvergence
5645 ,secondary_quantity = l_orig_mol_rec.secondary_quantity
5646 ,secondary_quantity_delivered = l_orig_mol_rec.secondary_quantity_delivered
5647 ,secondary_quantity_detailed = l_orig_mol_rec.secondary_quantity_detailed
5648 ,last_update_date = Sysdate
5649 WHERE
5650 line_id = l_orig_mol_rec.line_id;
5651 ELSE
5652 UPDATE
5653 mtl_txn_request_lines
5654 SET
5655 primary_quantity = l_orig_mol_rec.primary_quantity
5656 ,quantity = l_orig_mol_rec.quantity
5657 ,quantity_delivered = l_orig_mol_rec.quantity_delivered
5658 ,quantity_detailed = l_orig_mol_rec.quantity_detailed
5659 ,last_update_date = Sysdate
5660 WHERE
5661 line_id = l_orig_mol_rec.line_id;
5662 END IF;
5663 EXCEPTION
5664 WHEN OTHERS THEN
5665 IF (l_debug = 1) THEN
5666 print_debug('SPLIT_MO ERROR: error update original MOL',4);
5667 END IF;
5668 RAISE fnd_api.g_exc_error;
5669 END;
5670
5671 l_progress := '210';
5672 IF (l_debug = 1) THEN
5673 print_debug('SPLIT_MO - Quitting split_mo', 4);
5674 END IF;
5675
5676 EXCEPTION
5677 WHEN OTHERS THEN
5678 IF (l_debug = 1) THEN
5679 print_debug('SPLIT_MO: Exception occured after l_progress = ' ||
5680 l_progress || ' SQLCODE = ' || SQLCODE, 4);
5681 END IF;
5682
5683 x_return_status := g_ret_sts_unexp_err;
5684
5685 fnd_msg_pub.count_and_get
5686 ( p_count => x_msg_count
5687 ,p_data => x_msg_data );
5688 ROLLBACK TO split_mo_pub;
5689 END split_mo;
5690
5691 PROCEDURE split_mo
5692 (p_orig_mol_id IN NUMBER,
5693 p_mo_splt_tb IN OUT NOCOPY mo_in_tb_tp,
5694 x_return_status OUT NOCOPY VARCHAR2,
5695 x_msg_count OUT NOCOPY NUMBER,
5696 x_msg_data OUT NOCOPY VARCHAR2
5697 )
5698 IS
5699 l_txn_header_id NUMBER;
5700 l_operation_type VARCHAR2(15);
5701 l_updt_putaway_temp_tbl VARCHAR2(5);
5702 BEGIN
5703 x_return_status := g_ret_sts_success;
5704 inv_rcv_integration_apis.split_mo
5705 (p_orig_mol_id => p_orig_mol_id
5706 , p_mo_splt_tb => p_mo_splt_tb
5707 , p_updt_putaway_temp_tbl => l_updt_putaway_temp_tbl
5708 , p_txn_header_id => l_txn_header_id
5709 , p_operation_type => l_operation_type
5710 , x_return_status => x_return_status
5711 , x_msg_count => x_msg_count
5712 , x_msg_data => x_msg_data);
5713
5714 END split_mo;
5715
5716
5717 PROCEDURE split_mmtt
5718 (p_orig_mmtt_id NUMBER
5719 ,p_prim_qty_to_splt NUMBER
5720 ,p_prim_uom_code VARCHAR2
5721 ,x_new_mmtt_id OUT nocopy NUMBER
5722 ,x_return_status OUT NOCOPY VARCHAR2
5723 ,x_msg_count OUT NOCOPY NUMBER
5724 ,x_msg_data OUT NOCOPY VARCHAR2
5725 ) IS
5726 CURSOR mmtt_cur IS
5727 SELECT transaction_header_id
5728 ,transaction_temp_id
5729 ,source_code
5730 ,source_line_id
5731 ,transaction_mode
5732 ,lock_flag
5733 ,last_update_date
5734 ,last_updated_by
5735 ,creation_date
5736 ,created_by
5737 ,last_update_login
5738 ,request_id
5739 ,program_application_id
5740 ,program_id
5741 ,program_update_date
5742 ,inventory_item_id
5743 ,revision
5744 ,organization_id
5745 ,subinventory_code
5746 ,locator_id
5747 ,transaction_quantity
5748 ,primary_quantity
5749 ,transaction_uom
5750 ,transaction_cost
5751 ,transaction_type_id
5752 ,transaction_action_id
5753 ,transaction_source_type_id
5754 ,transaction_source_id
5755 ,transaction_source_name
5756 ,transaction_date
5757 ,acct_period_id
5758 ,distribution_account_id
5759 ,transaction_reference
5760 ,requisition_line_id
5761 ,requisition_distribution_id
5762 ,reason_id
5763 ,Ltrim(Rtrim(lot_number)) lot_number
5764 ,lot_expiration_date
5765 ,serial_number
5766 ,receiving_document
5767 ,demand_id
5768 ,rcv_transaction_id
5769 ,move_transaction_id
5770 ,completion_transaction_id
5771 ,wip_entity_type
5772 ,schedule_id
5773 ,repetitive_line_id
5774 ,employee_code
5775 ,primary_switch
5776 ,schedule_update_code
5777 ,setup_teardown_code
5778 ,item_ordering
5779 ,negative_req_flag
5780 ,operation_seq_num
5781 ,picking_line_id
5782 ,trx_source_line_id
5783 ,trx_source_delivery_id
5784 ,physical_adjustment_id
5785 ,cycle_count_id
5786 ,rma_line_id
5787 ,customer_ship_id
5788 ,currency_code
5789 ,currency_conversion_rate
5790 ,currency_conversion_type
5791 ,currency_conversion_date
5792 ,ussgl_transaction_code
5793 ,vendor_lot_number
5794 ,encumbrance_account
5795 ,encumbrance_amount
5796 ,ship_to_location
5797 ,shipment_number
5798 ,transfer_cost
5799 ,transportation_cost
5800 ,transportation_account
5801 ,freight_code
5802 ,containers
5803 ,waybill_airbill
5804 ,expected_arrival_date
5805 ,transfer_subinventory
5806 ,transfer_organization
5807 ,transfer_to_location
5808 ,new_average_cost
5809 ,value_change
5810 ,percentage_change
5811 ,material_allocation_temp_id
5812 ,demand_source_header_id
5813 ,demand_source_line
5814 ,demand_source_delivery
5815 ,item_segments
5816 ,item_description
5817 ,item_trx_enabled_flag
5818 ,item_location_control_code
5819 ,item_restrict_subinv_code
5820 ,item_restrict_locators_code
5821 ,item_revision_qty_control_code
5822 ,item_primary_uom_code
5823 ,item_uom_class
5824 ,item_shelf_life_code
5825 ,item_shelf_life_days
5826 ,item_lot_control_code
5827 ,item_serial_control_code
5828 ,item_inventory_asset_flag
5829 ,allowed_units_lookup_code
5830 ,department_id
5831 ,department_code
5832 ,wip_supply_type
5833 ,supply_subinventory
5834 ,supply_locator_id
5835 ,valid_subinventory_flag
5836 ,valid_locator_flag
5837 ,locator_segments
5838 ,current_locator_control_code
5839 ,number_of_lots_entered
5840 ,wip_commit_flag
5841 ,next_lot_number
5842 ,lot_alpha_prefix
5843 ,next_serial_number
5844 ,serial_alpha_prefix
5845 ,shippable_flag
5846 ,posting_flag
5847 ,required_flag
5848 ,process_flag
5849 ,error_code
5850 ,error_explanation
5851 ,attribute_category
5852 ,attribute1
5853 ,attribute2
5854 ,attribute3
5855 ,attribute4
5856 ,attribute5
5857 ,attribute6
5858 ,attribute7
5859 ,attribute8
5860 ,attribute9
5861 ,attribute10
5862 ,attribute11
5863 ,attribute12
5864 ,attribute13
5865 ,attribute14
5866 ,attribute15
5867 ,movement_id
5868 ,reservation_quantity
5869 ,shipped_quantity
5870 ,transaction_line_number
5871 ,task_id
5872 ,to_task_id
5873 ,source_task_id
5874 ,project_id
5875 ,source_project_id
5876 ,pa_expenditure_org_id
5877 ,to_project_id
5878 ,expenditure_type
5879 ,final_completion_flag
5880 ,transfer_percentage
5881 ,transaction_sequence_id
5882 ,material_account
5883 ,material_overhead_account
5884 ,resource_account
5885 ,outside_processing_account
5886 ,overhead_account
5887 ,flow_schedule
5888 ,cost_group_id
5889 ,demand_class
5890 ,qa_collection_id
5891 ,kanban_card_id
5892 ,overcompletion_transaction_qty
5893 ,overcompletion_primary_qty
5894 ,overcompletion_transaction_id
5895 ,end_item_unit_number
5896 ,scheduled_payback_date
5897 ,line_type_code
5898 ,parent_transaction_temp_id
5899 ,put_away_strategy_id
5900 ,put_away_rule_id
5901 ,pick_strategy_id
5902 ,pick_rule_id
5903 ,move_order_line_id
5904 ,task_group_id
5905 ,pick_slip_number
5906 ,reservation_id
5907 ,common_bom_seq_id
5908 ,common_routing_seq_id
5909 ,org_cost_group_id
5910 ,cost_type_id
5911 ,transaction_status
5912 ,standard_operation_id
5913 ,task_priority
5914 ,wms_task_type
5915 ,parent_line_id
5916 ,transfer_cost_group_id
5917 ,lpn_id
5918 ,transfer_lpn_id
5919 ,wms_task_status
5920 ,content_lpn_id
5921 ,container_item_id
5922 ,cartonization_id
5923 ,pick_slip_date
5924 ,rebuild_item_id
5925 ,rebuild_serial_number
5926 ,rebuild_activity_id
5927 ,rebuild_job_name
5928 ,organization_type
5929 ,transfer_organization_type
5930 ,owning_organization_id
5931 ,owning_tp_type
5932 ,xfr_owning_organization_id
5933 ,transfer_owning_tp_type
5934 ,planning_organization_id
5935 ,planning_tp_type
5936 ,xfr_planning_organization_id
5937 ,transfer_planning_tp_type
5938 ,secondary_uom_code
5939 ,secondary_transaction_quantity
5940 ,allocated_lpn_id
5941 ,schedule_number
5942 ,scheduled_flag
5943 ,class_code
5944 ,schedule_group
5945 ,build_sequence
5946 ,bom_revision
5947 ,routing_revision
5948 ,bom_revision_date
5949 ,routing_revision_date
5950 ,alternate_bom_designator
5951 ,alternate_routing_designator
5952 ,transaction_batch_id
5953 ,transaction_batch_seq
5954 ,operation_plan_id
5955 ,move_order_header_id
5956 ,serial_allocated_flag
5957 FROM mtl_material_transactions_temp
5958 WHERE transaction_temp_id = p_orig_mmtt_id;
5959
5960 l_mmtt_rec mmtt_cur%ROWTYPE;
5961 l_new_mmtt_id NUMBER;
5962 l_mmtts_to_split wms_atf_runtime_pub_apis.task_id_table_type;
5963 l_sysdate DATE := Sysdate;
5964
5965 l_error_code NUMBER;
5966 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
5967 l_progress VARCHAR2(10) := '10';
5968
5969 BEGIN
5970
5971 x_return_status := g_ret_sts_success;
5972
5973 IF (l_debug = 1) THEN
5974 print_debug('SPLIT_MMTT: Entering...', 4);
5975 print_debug(' p_orig_mmtt_id => '||p_orig_mmtt_id,4);
5976 print_debug(' p_prim_qty_to_splt => '||p_prim_qty_to_splt,4);
5977 print_debug(' p_prim_uom_code => '||p_prim_uom_code,4);
5978 END IF;
5979
5980 OPEN mmtt_cur;
5981 FETCH mmtt_cur INTO l_mmtt_rec;
5982 CLOSE mmtt_cur;
5983
5984 IF (Nvl(l_mmtt_rec.primary_quantity,0) < p_prim_qty_to_splt) THEN
5985 print_debug(' ORIG MMTT QYT > P_PRIM_QTY_SPLT!!',1);
5986 RAISE fnd_api.g_exc_unexpected_error;
5987 END IF;
5988
5989 l_mmtt_rec.primary_quantity := p_prim_qty_to_splt;
5990
5991 IF (l_mmtt_rec.transaction_uom <> p_prim_uom_code) THEN
5992 l_mmtt_rec.transaction_quantity := inv_rcv_cache.convert_qty
5993 (p_inventory_item_id => l_mmtt_rec.inventory_item_id
5994 ,p_from_qty => p_prim_qty_to_splt
5995 ,p_from_uom_code => p_prim_uom_code
5996 ,p_to_uom_code => l_mmtt_rec.transaction_uom
5997 );
5998 ELSE
5999 l_mmtt_rec.transaction_quantity := p_prim_qty_to_splt;
6000 END IF;
6001
6002 BEGIN
6003 INSERT INTO mtl_material_transactions_temp
6004 ( transaction_header_id
6005 ,transaction_temp_id
6006 ,source_code
6007 ,source_line_id
6008 ,transaction_mode
6009 ,lock_flag
6010 ,last_update_date
6011 ,last_updated_by
6012 ,creation_date
6013 ,created_by
6014 ,last_update_login
6015 ,request_id
6016 ,program_application_id
6017 ,program_id
6018 ,program_update_date
6019 ,inventory_item_id
6020 ,revision
6021 ,organization_id
6022 ,subinventory_code
6023 ,locator_id
6024 ,transaction_quantity
6025 ,primary_quantity
6026 ,transaction_uom
6027 ,transaction_cost
6028 ,transaction_type_id
6029 ,transaction_action_id
6030 ,transaction_source_type_id
6031 ,transaction_source_id
6032 ,transaction_source_name
6033 ,transaction_date
6034 ,acct_period_id
6035 ,distribution_account_id
6036 ,transaction_reference
6037 ,requisition_line_id
6038 ,requisition_distribution_id
6039 ,reason_id
6040 ,lot_number
6041 ,lot_expiration_date
6042 ,serial_number
6043 ,receiving_document
6044 ,demand_id
6045 ,rcv_transaction_id
6046 ,move_transaction_id
6047 ,completion_transaction_id
6048 ,wip_entity_type
6049 ,schedule_id
6050 ,repetitive_line_id
6051 ,employee_code
6052 ,primary_switch
6053 ,schedule_update_code
6054 ,setup_teardown_code
6055 ,item_ordering
6056 ,negative_req_flag
6057 ,operation_seq_num
6058 ,picking_line_id
6059 ,trx_source_line_id
6060 ,trx_source_delivery_id
6061 ,physical_adjustment_id
6062 ,cycle_count_id
6063 ,rma_line_id
6064 ,customer_ship_id
6065 ,currency_code
6066 ,currency_conversion_rate
6067 ,currency_conversion_type
6068 ,currency_conversion_date
6069 ,ussgl_transaction_code
6070 ,vendor_lot_number
6071 ,encumbrance_account
6072 ,encumbrance_amount
6073 ,ship_to_location
6074 ,shipment_number
6075 ,transfer_cost
6076 ,transportation_cost
6077 ,transportation_account
6078 ,freight_code
6079 ,containers
6080 ,waybill_airbill
6081 ,expected_arrival_date
6082 ,transfer_subinventory
6083 ,transfer_organization
6084 ,transfer_to_location
6085 ,new_average_cost
6086 ,value_change
6087 ,percentage_change
6088 ,material_allocation_temp_id
6089 ,demand_source_header_id
6090 ,demand_source_line
6091 ,demand_source_delivery
6092 ,item_segments
6093 ,item_description
6094 ,item_trx_enabled_flag
6095 ,item_location_control_code
6096 ,item_restrict_subinv_code
6097 ,item_restrict_locators_code
6098 ,item_revision_qty_control_code
6099 ,item_primary_uom_code
6100 ,item_uom_class
6101 ,item_shelf_life_code
6102 ,item_shelf_life_days
6103 ,item_lot_control_code
6104 ,item_serial_control_code
6105 ,item_inventory_asset_flag
6106 ,allowed_units_lookup_code
6107 ,department_id
6108 ,department_code
6109 ,wip_supply_type
6110 ,supply_subinventory
6111 ,supply_locator_id
6112 ,valid_subinventory_flag
6113 ,valid_locator_flag
6114 ,locator_segments
6115 ,current_locator_control_code
6116 ,number_of_lots_entered
6117 ,wip_commit_flag
6118 ,next_lot_number
6119 ,lot_alpha_prefix
6120 ,next_serial_number
6121 ,serial_alpha_prefix
6122 ,shippable_flag
6123 ,posting_flag
6124 ,required_flag
6125 ,process_flag
6126 ,error_code
6127 ,error_explanation
6128 ,attribute_category
6129 ,attribute1
6130 ,attribute2
6131 ,attribute3
6132 ,attribute4
6133 ,attribute5
6134 ,attribute6
6135 ,attribute7
6136 ,attribute8
6137 ,attribute9
6138 ,attribute10
6139 ,attribute11
6140 ,attribute12
6141 ,attribute13
6142 ,attribute14
6143 ,attribute15
6144 ,movement_id
6145 ,reservation_quantity
6146 ,shipped_quantity
6147 ,transaction_line_number
6148 ,task_id
6149 ,to_task_id
6150 ,source_task_id
6151 ,project_id
6152 ,source_project_id
6153 ,pa_expenditure_org_id
6154 ,to_project_id
6155 ,expenditure_type
6156 ,final_completion_flag
6157 ,transfer_percentage
6158 ,transaction_sequence_id
6159 ,material_account
6160 ,material_overhead_account
6161 ,resource_account
6162 ,outside_processing_account
6163 ,overhead_account
6164 ,flow_schedule
6165 ,cost_group_id
6166 ,demand_class
6167 ,qa_collection_id
6168 ,kanban_card_id
6169 ,overcompletion_transaction_qty
6170 ,overcompletion_primary_qty
6171 ,overcompletion_transaction_id
6172 ,end_item_unit_number
6173 ,scheduled_payback_date
6174 ,line_type_code
6175 ,parent_transaction_temp_id
6176 ,put_away_strategy_id
6177 ,put_away_rule_id
6178 ,pick_strategy_id
6179 ,pick_rule_id
6180 ,move_order_line_id
6181 ,task_group_id
6182 ,pick_slip_number
6183 ,reservation_id
6184 ,common_bom_seq_id
6185 ,common_routing_seq_id
6186 ,org_cost_group_id
6187 ,cost_type_id
6188 ,transaction_status
6189 ,standard_operation_id
6190 ,task_priority
6191 ,wms_task_type
6192 ,parent_line_id
6193 ,transfer_cost_group_id
6194 ,lpn_id
6195 ,transfer_lpn_id
6196 ,wms_task_status
6197 ,content_lpn_id
6198 ,container_item_id
6199 ,cartonization_id
6200 ,pick_slip_date
6201 ,rebuild_item_id
6202 ,rebuild_serial_number
6203 ,rebuild_activity_id
6204 ,rebuild_job_name
6205 ,organization_type
6206 ,transfer_organization_type
6207 ,owning_organization_id
6208 ,owning_tp_type
6209 ,xfr_owning_organization_id
6210 ,transfer_owning_tp_type
6211 ,planning_organization_id
6212 ,planning_tp_type
6213 ,xfr_planning_organization_id
6214 ,transfer_planning_tp_type
6215 ,secondary_uom_code
6216 ,secondary_transaction_quantity
6217 ,allocated_lpn_id
6218 ,schedule_number
6219 ,scheduled_flag
6220 ,class_code
6221 ,schedule_group
6222 ,build_sequence
6223 ,bom_revision
6224 ,routing_revision
6225 ,bom_revision_date
6226 ,routing_revision_date
6227 ,alternate_bom_designator
6228 ,alternate_routing_designator
6229 ,transaction_batch_id
6230 ,transaction_batch_seq
6231 ,operation_plan_id
6232 ,move_order_header_id
6233 ,serial_allocated_flag )
6234 VALUES
6235 ( mtl_material_transactions_s.NEXTVAL --use different header
6236 ,mtl_material_transactions_s.NEXTVAL
6237 ,l_mmtt_rec.SOURCE_CODE
6238 ,l_mmtt_rec.SOURCE_LINE_ID
6239 ,l_mmtt_rec.TRANSACTION_MODE
6240 ,l_mmtt_rec.LOCK_FLAG
6241 ,l_sysdate
6242 ,l_mmtt_rec.LAST_UPDATED_BY
6243 ,l_sysdate
6244 ,l_mmtt_rec.CREATED_BY
6245 ,l_mmtt_rec.LAST_UPDATE_LOGIN
6246 ,l_mmtt_rec.REQUEST_ID
6247 ,l_mmtt_rec.PROGRAM_APPLICATION_ID
6248 ,l_mmtt_rec.PROGRAM_ID
6249 ,l_mmtt_rec.PROGRAM_UPDATE_DATE
6250 ,l_mmtt_rec.INVENTORY_ITEM_ID
6251 ,l_mmtt_rec.REVISION
6252 ,l_mmtt_rec.ORGANIZATION_ID
6253 ,l_mmtt_rec.SUBINVENTORY_CODE
6254 ,l_mmtt_rec.LOCATOR_ID
6255 ,l_mmtt_rec.TRANSACTION_QUANTITY
6256 ,l_mmtt_rec.PRIMARY_QUANTITY
6257 ,l_mmtt_rec.TRANSACTION_UOM
6258 ,l_mmtt_rec.TRANSACTION_COST
6259 ,l_mmtt_rec.TRANSACTION_TYPE_ID
6260 ,l_mmtt_rec.TRANSACTION_ACTION_ID
6261 ,l_mmtt_rec.TRANSACTION_SOURCE_TYPE_ID
6262 ,l_mmtt_rec.TRANSACTION_SOURCE_ID
6263 ,l_mmtt_rec.TRANSACTION_SOURCE_NAME
6264 ,l_mmtt_rec.TRANSACTION_DATE
6265 ,l_mmtt_rec.ACCT_PERIOD_ID
6266 ,l_mmtt_rec.DISTRIBUTION_ACCOUNT_ID
6267 ,l_mmtt_rec.TRANSACTION_REFERENCE
6268 ,l_mmtt_rec.REQUISITION_LINE_ID
6269 ,l_mmtt_rec.REQUISITION_DISTRIBUTION_ID
6270 ,l_mmtt_rec.REASON_ID
6271 ,Ltrim(Rtrim(l_mmtt_rec.lot_number))
6272 ,l_mmtt_rec.LOT_EXPIRATION_DATE
6273 ,l_mmtt_rec.SERIAL_NUMBER
6274 ,l_mmtt_rec.RECEIVING_DOCUMENT
6275 ,l_mmtt_rec.DEMAND_ID
6276 ,l_mmtt_rec.RCV_TRANSACTION_ID
6277 ,l_mmtt_rec.MOVE_TRANSACTION_ID
6278 ,l_mmtt_rec.COMPLETION_TRANSACTION_ID
6279 ,l_mmtt_rec.WIP_ENTITY_TYPE
6280 ,l_mmtt_rec.SCHEDULE_ID
6281 ,l_mmtt_rec.REPETITIVE_LINE_ID
6282 ,l_mmtt_rec.employee_code
6283 ,l_mmtt_rec.PRIMARY_SWITCH
6284 ,l_mmtt_rec.SCHEDULE_UPDATE_CODE
6285 ,l_mmtt_rec.SETUP_TEARDOWN_CODE
6286 ,l_mmtt_rec.ITEM_ORDERING
6287 ,l_mmtt_rec.NEGATIVE_REQ_FLAG
6288 ,l_mmtt_rec.OPERATION_SEQ_NUM
6289 ,l_mmtt_rec.PICKING_LINE_ID
6290 ,l_mmtt_rec.TRX_SOURCE_LINE_ID
6291 ,l_mmtt_rec.TRX_SOURCE_DELIVERY_ID
6292 ,l_mmtt_rec.PHYSICAL_ADJUSTMENT_ID
6293 ,l_mmtt_rec.CYCLE_COUNT_ID
6294 ,l_mmtt_rec.RMA_LINE_ID
6295 ,l_mmtt_rec.CUSTOMER_SHIP_ID
6296 ,l_mmtt_rec.CURRENCY_CODE
6297 ,l_mmtt_rec.CURRENCY_CONVERSION_RATE
6298 ,l_mmtt_rec.CURRENCY_CONVERSION_TYPE
6299 ,l_mmtt_rec.CURRENCY_CONVERSION_DATE
6300 ,l_mmtt_rec.USSGL_TRANSACTION_CODE
6301 ,l_mmtt_rec.VENDOR_LOT_NUMBER
6302 ,l_mmtt_rec.ENCUMBRANCE_ACCOUNT
6303 ,l_mmtt_rec.ENCUMBRANCE_AMOUNT
6304 ,l_mmtt_rec.SHIP_TO_LOCATION
6305 ,l_mmtt_rec.SHIPMENT_NUMBER
6306 ,l_mmtt_rec.TRANSFER_COST
6307 ,l_mmtt_rec.TRANSPORTATION_COST
6308 ,l_mmtt_rec.TRANSPORTATION_ACCOUNT
6309 ,l_mmtt_rec.FREIGHT_CODE
6310 ,l_mmtt_rec.CONTAINERS
6311 ,l_mmtt_rec.WAYBILL_AIRBILL
6312 ,l_mmtt_rec.EXPECTED_ARRIVAL_DATE
6313 ,l_mmtt_rec.TRANSFER_SUBINVENTORY
6314 ,l_mmtt_rec.TRANSFER_ORGANIZATION
6315 ,l_mmtt_rec.TRANSFER_TO_LOCATION
6316 ,l_mmtt_rec.NEW_AVERAGE_COST
6317 ,l_mmtt_rec.VALUE_CHANGE
6318 ,l_mmtt_rec.PERCENTAGE_CHANGE
6319 ,l_mmtt_rec.MATERIAL_ALLOCATION_TEMP_ID
6320 ,l_mmtt_rec.DEMAND_SOURCE_HEADER_ID
6321 ,l_mmtt_rec.DEMAND_SOURCE_LINE
6322 ,l_mmtt_rec.DEMAND_SOURCE_DELIVERY
6323 ,l_mmtt_rec.ITEM_SEGMENTS
6324 ,l_mmtt_rec.ITEM_DESCRIPTION
6325 ,l_mmtt_rec.ITEM_TRX_ENABLED_FLAG
6326 ,l_mmtt_rec.ITEM_LOCATION_CONTROL_CODE
6327 ,l_mmtt_rec.ITEM_RESTRICT_SUBINV_CODE
6328 ,l_mmtt_rec.ITEM_RESTRICT_LOCATORS_CODE
6329 ,l_mmtt_rec.ITEM_REVISION_QTY_CONTROL_CODE
6330 ,l_mmtt_rec.ITEM_PRIMARY_UOM_CODE
6331 ,l_mmtt_rec.ITEM_UOM_CLASS
6332 ,l_mmtt_rec.ITEM_SHELF_LIFE_CODE
6333 ,l_mmtt_rec.ITEM_SHELF_LIFE_DAYS
6334 ,l_mmtt_rec.ITEM_LOT_CONTROL_CODE
6335 ,l_mmtt_rec.ITEM_SERIAL_CONTROL_CODE
6336 ,l_mmtt_rec.ITEM_INVENTORY_ASSET_FLAG
6337 ,l_mmtt_rec.ALLOWED_UNITS_LOOKUP_CODE
6338 ,l_mmtt_rec.DEPARTMENT_ID
6339 ,l_mmtt_rec.DEPARTMENT_CODE
6340 ,l_mmtt_rec.WIP_SUPPLY_TYPE
6341 ,l_mmtt_rec.SUPPLY_SUBINVENTORY
6342 ,l_mmtt_rec.SUPPLY_LOCATOR_ID
6343 ,l_mmtt_rec.VALID_SUBINVENTORY_FLAG
6344 ,l_mmtt_rec.VALID_LOCATOR_FLAG
6345 ,l_mmtt_rec.LOCATOR_SEGMENTS
6346 ,l_mmtt_rec.CURRENT_LOCATOR_CONTROL_CODE
6347 ,l_mmtt_rec.NUMBER_OF_LOTS_ENTERED
6348 ,l_mmtt_rec.WIP_COMMIT_FLAG
6349 ,l_mmtt_rec.NEXT_LOT_NUMBER
6350 ,l_mmtt_rec.LOT_ALPHA_PREFIX
6351 ,l_mmtt_rec.NEXT_SERIAL_NUMBER
6352 ,l_mmtt_rec.SERIAL_ALPHA_PREFIX
6353 ,l_mmtt_rec.SHIPPABLE_FLAG
6354 ,l_mmtt_rec.POSTING_FLAG
6355 ,l_mmtt_rec.REQUIRED_FLAG
6356 ,l_mmtt_rec.PROCESS_FLAG
6357 ,l_mmtt_rec.ERROR_CODE
6358 ,l_mmtt_rec.ERROR_EXPLANATION
6359 ,l_mmtt_rec.ATTRIBUTE_CATEGORY
6360 ,l_mmtt_rec.ATTRIBUTE1
6361 ,l_mmtt_rec.ATTRIBUTE2
6362 ,l_mmtt_rec.ATTRIBUTE3
6363 ,l_mmtt_rec.ATTRIBUTE4
6364 ,l_mmtt_rec.ATTRIBUTE5
6365 ,l_mmtt_rec.ATTRIBUTE6
6366 ,l_mmtt_rec.ATTRIBUTE7
6367 ,l_mmtt_rec.ATTRIBUTE8
6368 ,l_mmtt_rec.ATTRIBUTE9
6369 ,l_mmtt_rec.ATTRIBUTE10
6370 ,l_mmtt_rec.ATTRIBUTE11
6371 ,l_mmtt_rec.ATTRIBUTE12
6372 ,l_mmtt_rec.ATTRIBUTE13
6373 ,l_mmtt_rec.ATTRIBUTE14
6374 ,l_mmtt_rec.ATTRIBUTE15
6375 ,l_mmtt_rec.MOVEMENT_ID
6376 ,l_mmtt_rec.RESERVATION_QUANTITY
6377 ,l_mmtt_rec.SHIPPED_QUANTITY
6378 ,l_mmtt_rec.TRANSACTION_LINE_NUMBER
6379 ,l_mmtt_rec.TASK_ID
6380 ,l_mmtt_rec.TO_TASK_ID
6381 ,l_mmtt_rec.SOURCE_TASK_ID
6382 ,l_mmtt_rec.PROJECT_ID
6383 ,l_mmtt_rec.SOURCE_PROJECT_ID
6384 ,l_mmtt_rec.PA_EXPENDITURE_ORG_ID
6385 ,l_mmtt_rec.TO_PROJECT_ID
6386 ,l_mmtt_rec.EXPENDITURE_TYPE
6387 ,l_mmtt_rec.FINAL_COMPLETION_FLAG
6388 ,l_mmtt_rec.TRANSFER_PERCENTAGE
6389 ,l_mmtt_rec.TRANSACTION_SEQUENCE_ID
6390 ,l_mmtt_rec.MATERIAL_ACCOUNT
6391 ,l_mmtt_rec.MATERIAL_OVERHEAD_ACCOUNT
6392 ,l_mmtt_rec.RESOURCE_ACCOUNT
6393 ,l_mmtt_rec.OUTSIDE_PROCESSING_ACCOUNT
6394 ,l_mmtt_rec.OVERHEAD_ACCOUNT
6395 ,l_mmtt_rec.FLOW_SCHEDULE
6396 ,l_mmtt_rec.COST_GROUP_ID
6397 ,l_mmtt_rec.DEMAND_CLASS
6398 ,l_mmtt_rec.QA_COLLECTION_ID
6399 ,l_mmtt_rec.KANBAN_CARD_ID
6400 ,l_mmtt_rec.OVERCOMPLETION_TRANSACTION_QTY
6401 ,l_mmtt_rec.OVERCOMPLETION_PRIMARY_QTY
6402 ,l_mmtt_rec.OVERCOMPLETION_TRANSACTION_ID
6403 ,l_mmtt_rec.END_ITEM_UNIT_NUMBER
6404 ,l_mmtt_rec.SCHEDULED_PAYBACK_DATE
6405 ,l_mmtt_rec.LINE_TYPE_CODE
6406 ,l_mmtt_rec.PARENT_TRANSACTION_TEMP_ID
6407 ,l_mmtt_rec.PUT_AWAY_STRATEGY_ID
6408 ,l_mmtt_rec.PUT_AWAY_RULE_ID
6409 ,l_mmtt_rec.PICK_STRATEGY_ID
6410 ,l_mmtt_rec.PICK_RULE_ID
6411 ,l_mmtt_rec.MOVE_ORDER_LINE_ID
6412 ,l_mmtt_rec.TASK_GROUP_ID
6413 ,l_mmtt_rec.PICK_SLIP_NUMBER
6414 ,l_mmtt_rec.RESERVATION_ID
6415 ,l_mmtt_rec.COMMON_BOM_SEQ_ID
6416 ,l_mmtt_rec.COMMON_ROUTING_SEQ_ID
6417 ,l_mmtt_rec.ORG_COST_GROUP_ID
6418 ,l_mmtt_rec.COST_TYPE_ID
6419 ,l_mmtt_rec.TRANSACTION_STATUS
6420 ,l_mmtt_rec.STANDARD_OPERATION_ID
6421 ,l_mmtt_rec.TASK_PRIORITY
6422 ,l_mmtt_rec.WMS_TASK_TYPE
6423 ,l_mmtt_rec.PARENT_LINE_ID
6424 ,l_mmtt_rec.TRANSFER_COST_GROUP_ID
6425 ,l_mmtt_rec.LPN_ID
6426 ,l_mmtt_rec.TRANSFER_LPN_ID
6427 ,l_mmtt_rec.WMS_TASK_STATUS
6428 ,l_mmtt_rec.CONTENT_LPN_ID
6429 ,l_mmtt_rec.CONTAINER_ITEM_ID
6430 ,l_mmtt_rec.CARTONIZATION_ID
6431 ,l_mmtt_rec.PICK_SLIP_DATE
6432 ,l_mmtt_rec.REBUILD_ITEM_ID
6433 ,l_mmtt_rec.REBUILD_SERIAL_NUMBER
6434 ,l_mmtt_rec.REBUILD_ACTIVITY_ID
6435 ,l_mmtt_rec.REBUILD_JOB_NAME
6436 ,l_mmtt_rec.ORGANIZATION_TYPE
6437 ,l_mmtt_rec.TRANSFER_ORGANIZATION_TYPE
6438 ,l_mmtt_rec.OWNING_ORGANIZATION_ID
6439 ,l_mmtt_rec.OWNING_TP_TYPE
6440 ,l_mmtt_rec.XFR_OWNING_ORGANIZATION_ID
6441 ,l_mmtt_rec.TRANSFER_OWNING_TP_TYPE
6442 ,l_mmtt_rec.PLANNING_ORGANIZATION_ID
6443 ,l_mmtt_rec.PLANNING_TP_TYPE
6444 ,l_mmtt_rec.XFR_PLANNING_ORGANIZATION_ID
6445 ,l_mmtt_rec.TRANSFER_PLANNING_TP_TYPE
6446 ,l_mmtt_rec.SECONDARY_UOM_CODE
6447 ,l_mmtt_rec.SECONDARY_TRANSACTION_QUANTITY
6448 ,l_mmtt_rec.ALLOCATED_LPN_ID
6449 ,l_mmtt_rec.SCHEDULE_NUMBER
6450 ,l_mmtt_rec.SCHEDULED_FLAG
6451 ,l_mmtt_rec.CLASS_CODE
6452 ,l_mmtt_rec.SCHEDULE_GROUP
6453 ,l_mmtt_rec.BUILD_SEQUENCE
6454 ,l_mmtt_rec.BOM_REVISION
6455 ,l_mmtt_rec.ROUTING_REVISION
6456 ,l_mmtt_rec.BOM_REVISION_DATE
6457 ,l_mmtt_rec.ROUTING_REVISION_DATE
6458 ,l_mmtt_rec.ALTERNATE_BOM_DESIGNATOR
6459 ,l_mmtt_rec.ALTERNATE_ROUTING_DESIGNATOR
6460 ,l_mmtt_rec.TRANSACTION_BATCH_ID
6461 ,l_mmtt_rec.TRANSACTION_BATCH_SEQ
6462 ,l_mmtt_rec.operation_plan_id
6463 ,l_mmtt_rec.move_order_header_id
6464 ,l_mmtt_rec.serial_allocated_flag)
6465 returning transaction_temp_id INTO l_new_mmtt_id;
6466 EXCEPTION
6467 WHEN OTHERS THEN
6468 IF (l_debug = 1) THEN
6469 print_debug('SPLIT_MMTT: Error while inserting new MMTT!', 4);
6470 print_debug('SQLCODE:'||SQLCODE||' SQLERRM:'||SQLERRM,11);
6471 END IF;
6472 RAISE fnd_api.g_exc_unexpected_error;
6473 END;
6474
6475 x_new_mmtt_id := l_new_mmtt_id;
6476
6477 BEGIN
6478 UPDATE mtl_material_transactions_temp
6479 SET transaction_quantity = transaction_quantity - l_mmtt_rec.transaction_quantity
6480 , primary_quantity = primary_quantity - l_mmtt_rec.primary_quantity
6481 WHERE transaction_temp_id = p_orig_mmtt_id;
6482 EXCEPTION
6483 WHEN OTHERS THEN
6484 IF (l_debug = 1) THEN
6485 print_debug('SPLIT_MMTT: Error while updating original MMTT!', 4);
6486 print_debug('SQLCODE:'||SQLCODE||' SQLERRM:'||SQLERRM,11);
6487 END IF;
6488 RAISE fnd_api.g_exc_unexpected_error;
6489 END ;
6490
6491 -- Split parent MMTT, operation instance, operation plan
6492 l_mmtts_to_split(1) := l_new_mmtt_id;
6493 wms_atf_runtime_pub_apis.split_operation_instance
6494 (p_source_task_id => p_orig_mmtt_id,
6495 p_new_task_id_table => l_mmtts_to_split,
6496 p_activity_type_id => 1, -- INBOUND
6497 x_return_status => x_return_status,
6498 x_msg_count => x_msg_count,
6499 x_error_code => l_error_code,
6500 x_msg_data => x_msg_data);
6501 IF (x_return_status <> g_ret_sts_success) THEN
6502 IF (l_debug = 1) THEN
6503 print_debug('SPLIT_MMTT: Error in split_operation_instance',4);
6504 END IF;
6505 fnd_message.set_name('WMS','WMS_TASK_SPLIT_FAIL');
6506 fnd_msg_pub.add;
6507 RAISE fnd_api.g_exc_error;
6508 END IF;
6509
6510 IF l_debug = 1 THEN
6511 print_debug(' x_new_mmtt_id => '||x_new_mmtt_id,4);
6512 print_debug(' Exitting SPLIT_MMTT',4);
6513 END IF;
6514
6515 EXCEPTION
6516 WHEN OTHERS THEN
6517 IF (l_debug = 1) THEN
6518 print_debug('SPLIT_MMTT: Exception occurred after progress = ' ||
6519 l_progress || ' SQLCODE = ' || SQLCODE,4);
6520 END IF;
6521 IF (mmtt_cur%isopen) THEN
6522 CLOSE mmtt_cur;
6523 END IF;
6524 x_return_status := g_ret_sts_unexp_err;
6525 fnd_msg_pub.count_and_get
6526 ( p_count => x_msg_count
6527 ,p_data => x_msg_data );
6528 END split_mmtt;
6529 END inv_rcv_integration_apis;