[Home] [Help]
PACKAGE BODY: APPS.INV_SERIAL_NUMBER_PUB
Source
1 PACKAGE BODY inv_serial_number_pub AS
2 /* $Header: INVPSNB.pls 120.7.12010000.4 2008/12/02 10:07:52 ksivasa ship $*/
3
4 -- Global constant holding the package name
5 g_pkg_name CONSTANT VARCHAR2(30) := 'INV_SERIAL_NUMBER_PUB';
6 /* -- Added for DMV Project */
7 -- Bug# 6825191, Commenting the variables to be obsoleted
8 --G_first_row_of_trx boolean := true;
9 --G_first_row_trx_tmp_id number := 0;
10 l_status_after_p1 NUMBER := 0;
11 l_status_before_p1 NUMBER := 0;
12 MSN_UPDATE_FIRST_PASS BOOLEAN := TRUE;
13
14 PROCEDURE set_firstscan(p_firstscan IN BOOLEAN) IS
15 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
16 BEGIN
17 g_firstscan := p_firstscan;
18 END;
19
20 -- Procedure used to trace message for debugging
21 PROCEDURE invtrace(p_msg VARCHAR2 := NULL) IS
22 --Bug: 3772309: Performance bug fix.The fnd call happens everytime
23 -- debug_print is called.
24 -- l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
25 BEGIN
26 -- IF (l_debug = 1) THEN
27 inv_log_util.TRACE(p_msg, 'INVSER', 9);
28 -- END IF;
29 END;
30
31 PROCEDURE populateattributescolumn IS
32 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
33 BEGIN
34 g_serial_attributes_tbl(1).column_name := 'SERIAL_ATTRIBUTE_CATEGORY';
35 g_serial_attributes_tbl(1).column_type := 'VARCHAR2';
36 g_serial_attributes_tbl(2).column_name := 'ORIGINATION_DATE';
37 g_serial_attributes_tbl(2).column_type := 'DATE';
38 g_serial_attributes_tbl(3).column_name := 'C_ATTRIBUTE1';
39 g_serial_attributes_tbl(3).column_type := 'VARCHAR2';
40 g_serial_attributes_tbl(4).column_name := 'C_ATTRIBUTE2';
41 g_serial_attributes_tbl(4).column_type := 'VARCHAR2';
42 g_serial_attributes_tbl(5).column_name := 'C_ATTRIBUTE3';
43 g_serial_attributes_tbl(5).column_type := 'VARCHAR2';
44 g_serial_attributes_tbl(6).column_name := 'C_ATTRIBUTE4';
45 g_serial_attributes_tbl(6).column_type := 'VARCHAR2';
46 g_serial_attributes_tbl(7).column_name := 'C_ATTRIBUTE5';
47 g_serial_attributes_tbl(7).column_type := 'VARCHAR2';
48 g_serial_attributes_tbl(8).column_name := 'C_ATTRIBUTE6';
49 g_serial_attributes_tbl(8).column_type := 'VARCHAR2';
50 g_serial_attributes_tbl(9).column_name := 'C_ATTRIBUTE7';
51 g_serial_attributes_tbl(9).column_type := 'VARCHAR2';
52 g_serial_attributes_tbl(10).column_name := 'C_ATTRIBUTE8';
53 g_serial_attributes_tbl(10).column_type := 'VARCHAR2';
54 g_serial_attributes_tbl(11).column_name := 'C_ATTRIBUTE9';
55 g_serial_attributes_tbl(11).column_type := 'VARCHAR2';
56 g_serial_attributes_tbl(12).column_name := 'C_ATTRIBUTE10';
57 g_serial_attributes_tbl(12).column_type := 'VARCHAR2';
58 g_serial_attributes_tbl(13).column_name := 'C_ATTRIBUTE11';
59 g_serial_attributes_tbl(13).column_type := 'VARCHAR2';
60 g_serial_attributes_tbl(14).column_name := 'C_ATTRIBUTE12';
61 g_serial_attributes_tbl(14).column_type := 'VARCHAR2';
62 g_serial_attributes_tbl(15).column_name := 'C_ATTRIBUTE13';
63 g_serial_attributes_tbl(15).column_type := 'VARCHAR2';
64 g_serial_attributes_tbl(16).column_name := 'C_ATTRIBUTE14';
65 g_serial_attributes_tbl(16).column_type := 'VARCHAR2';
66 g_serial_attributes_tbl(17).column_name := 'C_ATTRIBUTE15';
67 g_serial_attributes_tbl(17).column_type := 'VARCHAR2';
68 g_serial_attributes_tbl(18).column_name := 'C_ATTRIBUTE16';
69 g_serial_attributes_tbl(18).column_type := 'VARCHAR2';
70 g_serial_attributes_tbl(19).column_name := 'C_ATTRIBUTE17';
71 g_serial_attributes_tbl(19).column_type := 'VARCHAR2';
72 g_serial_attributes_tbl(20).column_name := 'C_ATTRIBUTE18';
73 g_serial_attributes_tbl(20).column_type := 'VARCHAR2';
74 g_serial_attributes_tbl(21).column_name := 'C_ATTRIBUTE19';
75 g_serial_attributes_tbl(21).column_type := 'VARCHAR2';
76 g_serial_attributes_tbl(22).column_name := 'C_ATTRIBUTE20';
77 g_serial_attributes_tbl(22).column_type := 'VARCHAR2';
78 g_serial_attributes_tbl(23).column_name := 'D_ATTRIBUTE1';
79 g_serial_attributes_tbl(23).column_type := 'DATE';
80 g_serial_attributes_tbl(24).column_name := 'D_ATTRIBUTE2';
81 g_serial_attributes_tbl(24).column_type := 'DATE';
82 g_serial_attributes_tbl(25).column_name := 'D_ATTRIBUTE3';
83 g_serial_attributes_tbl(25).column_type := 'DATE';
84 g_serial_attributes_tbl(26).column_name := 'D_ATTRIBUTE4';
85 g_serial_attributes_tbl(26).column_type := 'DATE';
86 g_serial_attributes_tbl(27).column_name := 'D_ATTRIBUTE5';
87 g_serial_attributes_tbl(27).column_type := 'DATE';
88 g_serial_attributes_tbl(28).column_name := 'D_ATTRIBUTE6';
89 g_serial_attributes_tbl(28).column_type := 'DATE';
90 g_serial_attributes_tbl(29).column_name := 'D_ATTRIBUTE7';
91 g_serial_attributes_tbl(29).column_type := 'DATE';
92 g_serial_attributes_tbl(30).column_name := 'D_ATTRIBUTE8';
93 g_serial_attributes_tbl(30).column_type := 'DATE';
94 g_serial_attributes_tbl(31).column_name := 'D_ATTRIBUTE9';
95 g_serial_attributes_tbl(31).column_type := 'DATE';
96 g_serial_attributes_tbl(32).column_name := 'D_ATTRIBUTE10';
97 g_serial_attributes_tbl(32).column_type := 'DATE';
98 g_serial_attributes_tbl(33).column_name := 'N_ATTRIBUTE1';
99 g_serial_attributes_tbl(33).column_type := 'NUMBER';
100 g_serial_attributes_tbl(34).column_name := 'N_ATTRIBUTE2';
101 g_serial_attributes_tbl(34).column_type := 'NUMBER';
102 g_serial_attributes_tbl(35).column_name := 'N_ATTRIBUTE3';
103 g_serial_attributes_tbl(35).column_type := 'NUMBER';
104 g_serial_attributes_tbl(36).column_name := 'N_ATTRIBUTE4';
105 g_serial_attributes_tbl(36).column_type := 'NUMBER';
106 g_serial_attributes_tbl(37).column_name := 'N_ATTRIBUTE5';
107 g_serial_attributes_tbl(37).column_type := 'NUMBER';
108 g_serial_attributes_tbl(38).column_name := 'N_ATTRIBUTE6';
109 g_serial_attributes_tbl(38).column_type := 'NUMBER';
110 g_serial_attributes_tbl(39).column_name := 'N_ATTRIBUTE7';
111 g_serial_attributes_tbl(39).column_type := 'NUMBER';
112 g_serial_attributes_tbl(40).column_name := 'N_ATTRIBUTE8';
113 g_serial_attributes_tbl(40).column_type := 'NUMBER';
114 g_serial_attributes_tbl(41).column_name := 'N_ATTRIBUTE9';
115 g_serial_attributes_tbl(41).column_type := 'NUMBER';
116 g_serial_attributes_tbl(42).column_name := 'N_ATTRIBUTE10';
117 g_serial_attributes_tbl(42).column_type := 'NUMBER';
118 g_serial_attributes_tbl(43).column_name := 'STATUS_ID';
119 g_serial_attributes_tbl(43).column_type := 'NUMBER';
120 g_serial_attributes_tbl(44).column_name := 'TERRITORY_CODE';
121 g_serial_attributes_tbl(44).column_type := 'VARCHAR2';
122 END;
123
124 -- OverLoaded Procedure insertSerial for eAM
125 PROCEDURE insertserial
126 (
127 p_api_version IN NUMBER
128 , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
129 , p_commit IN VARCHAR2 := fnd_api.g_false
130 , p_validation_level IN NUMBER := fnd_api.g_valid_level_full
131 , p_inventory_item_id IN NUMBER
132 , p_organization_id IN NUMBER
133 , p_serial_number IN VARCHAR2
134 , p_current_status IN NUMBER
135 , p_group_mark_id IN NUMBER
136 , p_lot_number IN VARCHAR2
137 , p_initialization_date IN DATE DEFAULT SYSDATE
138 , x_return_status OUT NOCOPY VARCHAR2
139 , x_msg_count OUT NOCOPY NUMBER
140 , x_msg_data OUT NOCOPY VARCHAR2
141 , p_organization_type IN NUMBER DEFAULT NULL
142 , p_owning_org_id IN NUMBER DEFAULT NULL
143 , p_owning_tp_type IN NUMBER DEFAULT NULL
144 , p_planning_org_id IN NUMBER DEFAULT NULL
145 , p_planning_tp_type IN NUMBER DEFAULT NULL
146 ) IS
147 l_api_version CONSTANT NUMBER := 1.0;
148 l_api_name CONSTANT VARCHAR2(30) := 'insertSerial';
149 l_userid NUMBER;
150 l_loginid NUMBER;
151 l_serial_control_code NUMBER;
152 l_return_status VARCHAR2(1);
153 l_msg_data VARCHAR2(2000);
154 l_msg_count NUMBER;
155 isunique NUMBER;
156 item_count NUMBER;
157 eam_item NUMBER;
158 l_current_status NUMBER;
159 x_object_id NUMBER;
160 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
161 BEGIN
162 -- Standard Start of API savepoint
163 SAVEPOINT apiinsertserial_apipub;
164
165 -- Standard call to check for call compatibility.
166 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
167 RAISE fnd_api.g_exc_unexpected_error;
168 END IF;
169
170 -- Initialize message list if p_init_msg_list is set to TRUE.
171 IF fnd_api.to_boolean(p_init_msg_list) THEN
172 fnd_msg_pub.initialize;
173 END IF;
174
175 -- Initialize API return status to success
176 -- API body
177
178 --Block for Organization Validation
179 BEGIN
180 SELECT 1
181 INTO item_count
182 FROM mtl_parameters
183 WHERE organization_id = p_organization_id;
184 EXCEPTION
185 WHEN NO_DATA_FOUND THEN
186 item_count := 0;
187 WHEN OTHERS THEN
188 --Bug 3153585:Raising exception to populate error message correctly.
189 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
190 fnd_msg_pub.add_exc_msg('INV_SERIAL_NUMBER_PUB', 'insertSerial');
191 END IF;
192
193 RAISE fnd_api.g_exc_unexpected_error;
194 END;
195
196 IF (item_count = 0) THEN
197 fnd_message.set_name('INV', 'INV_INVALID_ORGANIZATION');
198 fnd_msg_pub.ADD;
199 --Bug 3153585:Raising exception to populate error message correctly.
200 RAISE fnd_api.g_exc_error;
201 END IF;
202
203 -- Block to check the Serial Control Code
204 BEGIN
205 SELECT serial_number_control_code
206 , eam_item_type
207 INTO l_serial_control_code
208 , eam_item
209 FROM mtl_system_items
210 WHERE inventory_item_id = p_inventory_item_id
211 AND organization_id = p_organization_id;
212
213 IF (l_serial_control_code = 1) THEN
214 fnd_message.set_name('INV', 'INV_ITEM_NOT_SERIAL_CONTROLLED');
215 fnd_msg_pub.ADD;
216 --Bug 3153585:Raising exception to populate error message correctly.
217 RAISE fnd_api.g_exc_error;
218 END IF;
219
220 IF eam_item IS NULL THEN
221 l_current_status := 1;
222 ELSE
223 l_current_status := p_current_status;
224 END IF;
225 EXCEPTION
226 WHEN fnd_api.g_exc_error THEN
227 RAISE fnd_api.g_exc_error;
228 --Bug 3153585:Raising exception to populate error message correctly.
229 WHEN NO_DATA_FOUND THEN
230 fnd_message.set_name('INV', 'INV_INVALID_ITEM');
231 fnd_msg_pub.ADD;
232 --Bug 3153585:Raising exception to populate error message correctly.
233 RAISE fnd_api.g_exc_error;
234 WHEN OTHERS THEN
235 --Bug 3152585:Raising Exception to populate error message correctly.
236 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
237 fnd_msg_pub.add_exc_msg('INV_SERIAL_NUMBER_PUB', 'insertSerial');
238 END IF;
239
240 RAISE fnd_api.g_exc_unexpected_error;
241 END;
242
243 SELECT mtl_gen_object_id_s.NEXTVAL
244 INTO x_object_id
245 FROM DUAL;
246
247 l_userid := fnd_global.user_id;
248 l_loginid := fnd_global.login_id;
249 isunique := is_serial_unique(p_organization_id, p_inventory_item_id, p_serial_number, x_msg_data);
250
251 IF (isunique = 0) THEN
252 INSERT INTO mtl_serial_numbers
253 (
254 inventory_item_id
255 , serial_number
256 , last_update_date
257 , last_updated_by
258 , creation_date
259 , created_by
260 , last_update_login
261 , current_status
262 , current_organization_id
263 , group_mark_id
264 , gen_object_id
265 , lot_number
266 , initialization_date
267 , organization_type
268 , owning_organization_id
269 , owning_tp_type
270 , planning_organization_id
271 , planning_tp_type
272 )
273 VALUES (
274 p_inventory_item_id
275 , p_serial_number
276 , SYSDATE
277 , l_userid
278 , SYSDATE
279 , l_userid
280 , l_loginid
281 , l_current_status
282 , p_organization_id
283 , p_group_mark_id
284 , x_object_id
285 , p_lot_number
286 , p_initialization_date
287 , NVL(p_organization_type, 2)
288 , NVL(p_owning_org_id, p_organization_id)
289 , NVL(p_owning_tp_type, 2)
290 , NVL(p_planning_org_id, p_organization_id)
291 , NVL(p_planning_tp_type, 2)
292 );
293 END IF;
294
295 x_return_status := fnd_api.g_ret_sts_success;
296
297 -- End of API body.
298 -- Standard check of p_commit.
299 IF fnd_api.to_boolean(p_commit) THEN
300 COMMIT WORK;
301 END IF;
302
303 -- Standard call to get message count and if count is 1, get message info.
304 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
305 EXCEPTION
306 WHEN fnd_api.g_exc_error THEN
307 ROLLBACK TO apiinsertserial_apipub;
308 --Bug 3153585:Populating the message from the message stack
309 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
310 x_return_status := fnd_api.g_ret_sts_error;
311 WHEN fnd_api.g_exc_unexpected_error THEN
312 --Bug 3153585:Populating the message from the message stack
313 ROLLBACK TO apiinsertserial_apipub;
314 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
315 x_return_status := fnd_api.g_ret_sts_unexp_error;
316 WHEN OTHERS THEN
317 ROLLBACK TO apiinsertserial_apipub;
318
319 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
320 fnd_msg_pub.add_exc_msg('INV_SERIAL_NUMBER_PUB', 'insertSerial');
321 END IF;
322
323 x_return_status := fnd_api.g_ret_sts_unexp_error;
324 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
325 END insertserial;
326
327 -- 'Serial Tracking in WIP project. insert wip_entity_id, operation_seq_num and intraoperation_step_type
328 -- into MSN.
329 PROCEDURE insertserial(
330 p_api_version IN NUMBER
331 , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
332 , p_commit IN VARCHAR2 := fnd_api.g_false
333 , p_validation_level IN NUMBER := fnd_api.g_valid_level_full
334 , p_inventory_item_id IN NUMBER
335 , p_organization_id IN NUMBER
336 , p_serial_number IN VARCHAR2
337 , p_initialization_date IN DATE
338 , p_completion_date IN DATE
339 , p_ship_date IN DATE
340 , p_revision IN VARCHAR2
341 , p_lot_number IN VARCHAR2
342 , p_current_locator_id IN NUMBER
343 , p_subinventory_code IN VARCHAR2
344 , p_trx_src_id IN NUMBER
345 , p_unit_vendor_id IN NUMBER
346 , p_vendor_lot_number IN VARCHAR2
347 , p_vendor_serial_number IN VARCHAR2
348 , p_receipt_issue_type IN NUMBER
349 , p_txn_src_id IN NUMBER
350 , p_txn_src_name IN VARCHAR2
351 , p_txn_src_type_id IN NUMBER
352 , p_transaction_id IN NUMBER
353 , p_current_status IN NUMBER
354 , p_parent_item_id IN NUMBER
355 , p_parent_serial_number IN VARCHAR2
356 , p_cost_group_id IN NUMBER
357 , p_transaction_action_id IN NUMBER
358 , p_transaction_temp_id IN NUMBER
359 , p_status_id IN NUMBER
360 , x_object_id OUT NOCOPY NUMBER
361 , x_return_status OUT NOCOPY VARCHAR2
362 , x_msg_count OUT NOCOPY NUMBER
363 , x_msg_data OUT NOCOPY VARCHAR2
364 , p_organization_type IN NUMBER DEFAULT NULL
365 , p_owning_org_id IN NUMBER DEFAULT NULL
366 , p_owning_tp_type IN NUMBER DEFAULT NULL
367 , p_planning_org_id IN NUMBER DEFAULT NULL
368 , p_planning_tp_type IN NUMBER DEFAULT NULL
369 --Serial Tracking in WIP project
370 , p_wip_entity_id IN NUMBER DEFAULT NULL
371 , p_operation_seq_num IN NUMBER DEFAULT NULL
372 , p_intraoperation_step_type IN NUMBER DEFAULT NULL
373 ) IS
374 l_api_version CONSTANT NUMBER := 1.0;
375 l_api_name CONSTANT VARCHAR2(30) := 'insertSerial';
376 l_userid NUMBER;
377 l_loginid NUMBER;
378 l_serial_control_code NUMBER;
379 l_attributes_default inv_lot_sel_attr.lot_sel_attributes_tbl_type;
380 l_attributes_default_count NUMBER;
381 l_attributes_in inv_lot_sel_attr.lot_sel_attributes_tbl_type;
382 l_column_idx BINARY_INTEGER := 44;
383 l_return_status VARCHAR2(1);
384 l_msg_data VARCHAR2(2000);
385 l_msg_count NUMBER;
386 l_status_rec inv_material_status_pub.mtl_status_update_rec_type;
387 l_status_id NUMBER := NULL;
388 l_lot_status_enabled VARCHAR2(1);
389 l_default_lot_status_id NUMBER := NULL;
390 l_serial_status_enabled VARCHAR2(1);
391 l_default_serial_status_id NUMBER;
392 l_wms_installed BOOLEAN;
393
394 CURSOR serial_temp_csr(p_transaction_temp_id NUMBER) IS
395 SELECT serial_attribute_category
396 , fnd_date.date_to_canonical(origination_date)
397 , c_attribute1
398 , c_attribute2
399 , c_attribute3
400 , c_attribute4
401 , c_attribute5
402 , c_attribute6
403 , c_attribute7
404 , c_attribute8
405 , c_attribute9
406 , c_attribute10
407 , c_attribute11
408 , c_attribute12
409 , c_attribute13
410 , c_attribute14
411 , c_attribute15
412 , c_attribute16
413 , c_attribute17
414 , c_attribute18
415 , c_attribute19
416 , c_attribute20
417 , fnd_date.date_to_canonical(d_attribute1)
418 , fnd_date.date_to_canonical(d_attribute2)
419 , fnd_date.date_to_canonical(d_attribute3)
420 , fnd_date.date_to_canonical(d_attribute4)
421 , fnd_date.date_to_canonical(d_attribute5)
422 , fnd_date.date_to_canonical(d_attribute6)
423 , fnd_date.date_to_canonical(d_attribute7)
424 , fnd_date.date_to_canonical(d_attribute8)
425 , fnd_date.date_to_canonical(d_attribute9)
426 , fnd_date.date_to_canonical(d_attribute10)
427 , TO_CHAR(n_attribute1)
428 , TO_CHAR(n_attribute2)
429 , TO_CHAR(n_attribute3)
430 , TO_CHAR(n_attribute4)
431 , TO_CHAR(n_attribute5)
432 , TO_CHAR(n_attribute6)
433 , TO_CHAR(n_attribute7)
434 , TO_CHAR(n_attribute8)
435 , TO_CHAR(n_attribute9)
436 , TO_CHAR(n_attribute10)
437 , status_id
438 , territory_code
439 FROM mtl_serial_numbers_temp
440 WHERE transaction_temp_id = p_transaction_temp_id
441 AND p_serial_number BETWEEN fm_serial_number AND NVL(to_serial_number, fm_serial_number);
442
443 l_input_idx BINARY_INTEGER;
444 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
445 BEGIN
446 -- Standard Start of API savepoint
447 SAVEPOINT apiinsertserial_apipub;
448
449 IF (l_debug = 1) THEN
450 inv_trx_util_pub.TRACE('In insertserial() procedure. ', 'INV_SERIAL_NUMBER_PUB', 9);
451 END IF;
452
453 -- Standard call to check for call compatibility.
454 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
455 RAISE fnd_api.g_exc_unexpected_error;
456 END IF;
457
458 -- Initialize message list if p_init_msg_list is set to TRUE.
459 IF fnd_api.to_boolean(p_init_msg_list) THEN
460 fnd_msg_pub.initialize;
461 END IF;
462
463 /** ssia we don't need this if condition **/
464 /*IF (p_transaction_action_id = 3 AND g_firstscan = FALSE) THEN*/
465 /*** ssia note end ****/
466 x_return_status := fnd_api.g_ret_sts_success;
467
468 /**ELSE**/
469 BEGIN
470 SELECT serial_number_control_code
471 INTO l_serial_control_code
472 FROM mtl_system_items
473 WHERE inventory_item_id = p_inventory_item_id
474 AND organization_id = p_organization_id;
475
476 -- invtrace('serial_number_control_code is ' || l_serial_control_code);
477 IF (l_serial_control_code = 1) THEN
478 -- invtrace('serial_control_code is 1.there is no serial control.');
479 fnd_message.set_name('INV', 'INV_ITEM_NOT_SERIAL_CONTROLLED');
480 fnd_msg_pub.ADD;
481 --Bug 3153585:Populating the message from the message stack
482 RAISE fnd_api.g_exc_error;
483 END IF;
484 EXCEPTION
485 WHEN NO_DATA_FOUND THEN
486 fnd_message.set_name('INV', 'INV_INVALID_ITEM');
487 fnd_msg_pub.ADD;
488 RAISE fnd_api.g_exc_error;
489 END;
490
491 SELECT mtl_gen_object_id_s.NEXTVAL
492 INTO x_object_id
493 FROM DUAL;
494
495 -- invtrace('next genealogy object id from the sequence mtl_gen_object_id_s is ' || x_object_id);
496 l_wms_installed :=
497 wms_install.check_install(
498 x_return_status => l_return_status
499 , x_msg_count => l_msg_count
500 , x_msg_data => l_msg_data
501 , p_organization_id => NULL --p_organization_id
502 );
503 -- bug 2771342/2768690
504 -- Moved this line before the if statement so that this code is
505 -- called for the else part of the if statement too.
506 populateattributescolumn();
507
508 -- invtrace('wms is installed?' || l_wms_installed);
509 IF (p_transaction_temp_id IS NOT NULL) THEN
510 -- invtrace('transaction_temp_id is not null. It is ' || p_transaction_temp_id);
511 OPEN serial_temp_csr(p_transaction_temp_id);
512
513 --populateattributescolumn();
514 FETCH serial_temp_csr
515 INTO g_serial_attributes_tbl(1).column_value
516 , g_serial_attributes_tbl(2).column_value
517 , g_serial_attributes_tbl(3).column_value
518 , g_serial_attributes_tbl(4).column_value
519 , g_serial_attributes_tbl(5).column_value
520 , g_serial_attributes_tbl(6).column_value
521 , g_serial_attributes_tbl(7).column_value
522 , g_serial_attributes_tbl(8).column_value
523 , g_serial_attributes_tbl(9).column_value
524 , g_serial_attributes_tbl(10).column_value
525 , g_serial_attributes_tbl(11).column_value
526 , g_serial_attributes_tbl(12).column_value
527 , g_serial_attributes_tbl(13).column_value
528 , g_serial_attributes_tbl(14).column_value
529 , g_serial_attributes_tbl(15).column_value
530 , g_serial_attributes_tbl(16).column_value
531 , g_serial_attributes_tbl(17).column_value
532 , g_serial_attributes_tbl(18).column_value
533 , g_serial_attributes_tbl(19).column_value
534 , g_serial_attributes_tbl(20).column_value
535 , g_serial_attributes_tbl(21).column_value
536 , g_serial_attributes_tbl(22).column_value
537 , g_serial_attributes_tbl(23).column_value
538 , g_serial_attributes_tbl(24).column_value
539 , g_serial_attributes_tbl(25).column_value
540 , g_serial_attributes_tbl(26).column_value
541 , g_serial_attributes_tbl(27).column_value
542 , g_serial_attributes_tbl(28).column_value
543 , g_serial_attributes_tbl(29).column_value
544 , g_serial_attributes_tbl(30).column_value
545 , g_serial_attributes_tbl(31).column_value
546 , g_serial_attributes_tbl(32).column_value
547 , g_serial_attributes_tbl(33).column_value
548 , g_serial_attributes_tbl(34).column_value
549 , g_serial_attributes_tbl(35).column_value
550 , g_serial_attributes_tbl(36).column_value
551 , g_serial_attributes_tbl(37).column_value
552 , g_serial_attributes_tbl(38).column_value
553 , g_serial_attributes_tbl(39).column_value
554 , g_serial_attributes_tbl(40).column_value
555 , g_serial_attributes_tbl(41).column_value
556 , g_serial_attributes_tbl(42).column_value
557 , g_serial_attributes_tbl(43).column_value
558 , g_serial_attributes_tbl(44).column_value;
559
560 CLOSE serial_temp_csr;
561
562 IF l_wms_installed THEN
563 -- invtrace('wms is installed ');
564 l_input_idx := 0;
565
566 FOR x IN 1 .. 44 LOOP
567 IF (g_serial_attributes_tbl(x).column_value IS NOT NULL) THEN
568 l_input_idx := l_input_idx + 1;
569 -- invtrace('in serial attributes loop. input_idx is ' || l_input_idx);
570 l_attributes_in(l_input_idx).column_name := g_serial_attributes_tbl(x).column_name;
571 -- invtrace('l_attributes_in(l_input_idx).column_name is ' || l_attributes_in(l_input_idx).column_name);
572 l_attributes_in(l_input_idx).column_type := g_serial_attributes_tbl(x).column_type;
573 -- invtrace('l_attributes_in(l_input_idx).column_type is ' || l_attributes_in(l_input_idx).column_type);
574 l_attributes_in(l_input_idx).column_value := g_serial_attributes_tbl(x).column_value;
575 -- invtrace('l_attributes_in(l_input_idx).column_value is ' || l_attributes_in(l_input_idx).column_value);
576 END IF;
577 END LOOP;
578 END IF; -- if wms installed is true
579 END IF; -- if transaction_Temp_id is not null
580
581 ----------------------------------------------------------
582 -- call inv_lot_sel_attr.get_default to get the default value
583 -- of the lot attributes
584 ---------------------------------------------------------
585 -- invtrace('calling inv_lot_sel_attr.get_default to get the default value of lot attributes');
586 IF l_wms_installed THEN
587 inv_lot_sel_attr.get_default(
588 x_attributes_default => l_attributes_default
589 , x_attributes_default_count => l_attributes_default_count
590 , x_return_status => l_return_status
591 , x_msg_count => l_msg_count
592 , x_msg_data => l_msg_data
593 , p_table_name => 'MTL_SERIAL_NUMBERS'
594 , p_attributes_name => 'Serial Attributes'
595 , p_inventory_item_id => p_inventory_item_id
596 , p_organization_id => p_organization_id
597 , p_lot_serial_number => p_serial_number
598 , p_attributes => l_attributes_in
599 );
600
601 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
602 x_return_status := l_return_status;
603 RAISE fnd_api.g_exc_unexpected_error;
604 END IF;
605
606 IF (l_attributes_default_count > 0) THEN
607 FOR i IN 1 .. l_attributes_default_count LOOP
608 FOR j IN 1 .. g_serial_attributes_tbl.COUNT LOOP
609 IF (l_attributes_default(i).column_name = g_serial_attributes_tbl(j).column_name) THEN
610 g_serial_attributes_tbl(j).column_value := l_attributes_default(i).column_value;
611 END IF;
612 END LOOP;
613 END LOOP;
614 END IF;
615 END IF; -- end if of wms install is true
616
617 --END IF; -- delete this since we don't need the if condition to set the l_return_status
618 l_userid := fnd_global.user_id;
619 l_loginid := fnd_global.login_id;
620
621 -- invtrace('transaction_action_id is ' || p_transaction_action_id);
622 IF (p_transaction_action_id = 3
623 AND g_firstscan = FALSE) THEN
624 -- invtrace(' inserting into MSN values ');
625 ---------#-#-#-#get the values later
626 INSERT INTO mtl_serial_numbers
627 (
628 inventory_item_id
629 , serial_number
630 , last_update_date
631 , last_updated_by
632 , creation_date
633 , created_by
634 , last_update_login
635 , request_id
636 , program_application_id
637 , program_id
638 , program_update_date
639 , initialization_date
640 , completion_date
641 , ship_date
642 , current_status
643 , revision
644 , lot_number
645 , fixed_asset_tag
646 , reserved_order_id
647 , parent_item_id
648 , parent_serial_number
649 , original_wip_entity_id
650 , original_unit_vendor_id
651 , vendor_serial_number
652 , vendor_lot_number
653 , last_txn_source_type_id
654 , last_transaction_id
655 , last_receipt_issue_type
656 , last_txn_source_name
657 , last_txn_source_id
658 , descriptive_text
659 , current_subinventory_code
660 , current_locator_id
661 , current_organization_id
662 , attribute_category
663 , attribute1
664 , attribute2
665 , attribute3
666 , attribute4
667 , attribute5
668 , attribute6
669 , attribute7
670 , attribute8
671 , attribute9
672 , attribute10
673 , attribute11
674 , attribute12
675 , attribute13
676 , attribute14
677 , attribute15
678 , group_mark_id
679 , line_mark_id
680 , lot_line_mark_id
681 , end_item_unit_number
682 , gen_object_id
683 , serial_attribute_category
684 , origination_date
685 , c_attribute1
686 , c_attribute2
687 , c_attribute3
688 , c_attribute4
689 , c_attribute5
690 , c_attribute6
691 , c_attribute7
692 , c_attribute8
693 , c_attribute9
694 , c_attribute10
695 , c_attribute11
696 , c_attribute12
697 , c_attribute13
698 , c_attribute14
699 , c_attribute15
700 , c_attribute16
701 , c_attribute17
702 , c_attribute18
703 , c_attribute19
704 , c_attribute20
705 , d_attribute1
706 , d_attribute2
707 , d_attribute3
708 , d_attribute4
709 , d_attribute5
710 , d_attribute6
711 , d_attribute7
712 , d_attribute8
713 , d_attribute9
714 , d_attribute10
715 , n_attribute1
716 , n_attribute2
717 , n_attribute3
718 , n_attribute4
719 , n_attribute5
720 , n_attribute6
721 , n_attribute7
722 , n_attribute8
723 , n_attribute9
724 , n_attribute10
725 , status_id
726 , territory_code
727 , cost_group_id
728 , organization_type
729 , owning_organization_id
730 , owning_tp_type
731 , planning_organization_id
732 , planning_tp_type
733 , wip_entity_id
734 , operation_seq_num
735 , intraoperation_step_type
736 )
737 SELECT inventory_item_id
738 , serial_number
739 , SYSDATE
740 , l_userid
741 , SYSDATE
742 , l_userid
743 , l_loginid
744 , request_id
745 , program_application_id
746 , program_id
747 , program_update_date
748 , initialization_date
749 , completion_date
750 , ship_date
751 , current_status
752 , revision
753 , lot_number
754 , fixed_asset_tag
755 , reserved_order_id
756 , parent_item_id
757 , parent_serial_number
758 , original_wip_entity_id
759 , original_unit_vendor_id
760 , vendor_serial_number
761 , vendor_lot_number
762 , last_txn_source_type_id
763 , p_transaction_id
764 , last_receipt_issue_type
765 , p_txn_src_name
766 , p_txn_src_id
767 , descriptive_text
768 , p_subinventory_code
769 , p_current_locator_id
770 , p_organization_id
771 , attribute_category
772 , attribute1
773 , attribute2
774 , attribute3
775 , attribute4
776 , attribute5
777 , attribute6
778 , attribute7
779 , attribute8
780 , attribute9
781 , attribute10
782 , attribute11
783 , attribute12
784 , attribute13
785 , attribute14
786 , attribute15
787 , group_mark_id
788 , line_mark_id
789 , lot_line_mark_id
790 , end_item_unit_number
791 , x_object_id
792 , serial_attribute_category
793 , origination_date
794 , c_attribute1
795 , c_attribute2
796 , c_attribute3
797 , c_attribute4
798 , c_attribute5
799 , c_attribute6
800 , c_attribute7
801 , c_attribute8
802 , c_attribute9
803 , c_attribute10
804 , c_attribute11
805 , c_attribute12
806 , c_attribute13
807 , c_attribute14
808 , c_attribute15
809 , c_attribute16
810 , c_attribute17
811 , c_attribute18
812 , c_attribute19
813 , c_attribute20
814 , d_attribute1
815 , d_attribute2
816 , d_attribute3
817 , d_attribute4
818 , d_attribute5
819 , d_attribute6
820 , d_attribute7
821 , d_attribute8
822 , d_attribute9
823 , d_attribute10
824 , n_attribute1
825 , n_attribute2
826 , n_attribute3
827 , n_attribute4
828 , n_attribute5
829 , n_attribute6
830 , n_attribute7
831 , n_attribute8
832 , n_attribute9
833 , n_attribute10
834 , status_id
835 , territory_code
836 , inv_cost_group_pub.g_cost_group_id
837 , NVL(p_organization_type, 2)
838 , NVL(p_owning_org_id, p_organization_id)
839 , NVL(p_owning_tp_type, 2)
840 , NVL(p_planning_org_id, p_organization_id)
841 , NVL(p_planning_tp_type, 2)
842 , wip_entity_id
843 , operation_seq_num
844 , intraoperation_step_type
845 FROM mtl_serial_numbers
846 WHERE serial_number = p_serial_number
847 AND current_organization_id = g_transfer_org_id
848 AND inventory_item_id = p_inventory_item_id
849 AND NOT EXISTS(
850 SELECT NULL
851 FROM mtl_serial_numbers sn
852 WHERE sn.serial_number = p_serial_number
853 AND sn.current_organization_id = p_organization_id
854 AND sn.inventory_item_id = p_inventory_item_id);
855
856 -- prepare to insert the initial status to the status history table
857 -- bug 1870120
858 SELECT status_id
859 INTO l_status_id
860 FROM mtl_serial_numbers
861 WHERE serial_number = p_serial_number
862 AND current_organization_id = p_organization_id
863 AND inventory_item_id = p_inventory_item_id;
864 -- invtrace('l_status_id from MSN when serial_number = ' || p_serial_number || ' current_organization_id = ' || p_organization_id || 'inventory_item_id = ' || p_inventory_item_id);
865 ELSE
866 /** Populate Serial Attribute Category info. **/
867 IF (l_wms_installed) THEN
868 -- invtrace('wms is installed. Calling inv_lot_sel_attr.get_context_code');
869 inv_lot_sel_attr.get_context_code(g_serial_attributes_tbl(1).column_value, p_organization_id, p_inventory_item_id
870 , 'Serial Attributes');
871 ELSE
872 -- invtrace('wms is not installed. populating all the column values in the g_serial_attributes_tbl with null value');
873 g_serial_attributes_tbl(1).column_value := NULL;
874 END IF;
875
876 -- if the p_status_id is null, then default the status if it is
877 -- specified in the mtl_system_items
878 -- invtrace('p_status_id is ' || p_status_id);
879 IF p_status_id IS NULL THEN
880 -- invtrace('p_status_id is null. calling inv_material_status_grp.get_lot_serial_status_control');
881 inv_material_status_grp.get_lot_serial_status_control(
882 p_organization_id => p_organization_id
883 , p_inventory_item_id => p_inventory_item_id
884 , x_return_status => l_return_status
885 , x_msg_count => l_msg_count
886 , x_msg_data => l_msg_data
887 , x_lot_status_enabled => l_lot_status_enabled
888 , x_default_lot_status_id => l_default_lot_status_id
889 , x_serial_status_enabled => l_serial_status_enabled
890 , x_default_serial_status_id => l_default_serial_status_id
891 );
892
893 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
894 x_return_status := l_return_status;
895 RAISE fnd_api.g_exc_unexpected_error;
896 END IF;
897
898 IF (NVL(l_serial_status_enabled, 'Y') = 'Y') THEN
899 l_status_id := l_default_serial_status_id;
900 END IF;
901 ELSE
902 l_status_id := p_status_id;
903 END IF;
904
905 -- invtrace('inserting into MSN values');
906 INSERT INTO mtl_serial_numbers
907 (
908 inventory_item_id
909 , serial_number
910 , last_update_date
911 , last_updated_by
912 , creation_date
913 , created_by
914 , last_update_login
915 , request_id
916 , program_application_id
917 , program_id
918 , program_update_date
919 , initialization_date
920 , completion_date
921 , ship_date
922 , current_status
923 , revision
924 , lot_number
925 , fixed_asset_tag
926 , reserved_order_id
927 , parent_item_id
928 , parent_serial_number
929 , original_wip_entity_id
930 , original_unit_vendor_id
931 , vendor_serial_number
932 , vendor_lot_number
933 , last_txn_source_type_id
934 , last_transaction_id
935 , last_receipt_issue_type
936 , last_txn_source_name
937 , last_txn_source_id
938 , descriptive_text
939 , current_subinventory_code
940 , current_locator_id
941 , current_organization_id
942 , attribute_category
943 , attribute1
944 , attribute2
945 , attribute3
946 , attribute4
947 , attribute5
948 , attribute6
949 , attribute7
950 , attribute8
951 , attribute9
952 , attribute10
953 , attribute11
954 , attribute12
955 , attribute13
956 , attribute14
957 , attribute15
958 , group_mark_id
959 , line_mark_id
960 , lot_line_mark_id
961 , end_item_unit_number
962 , gen_object_id
963 , serial_attribute_category
964 , origination_date
965 , c_attribute1
966 , c_attribute2
967 , c_attribute3
968 , c_attribute4
969 , c_attribute5
970 , c_attribute6
971 , c_attribute7
972 , c_attribute8
973 , c_attribute9
974 , c_attribute10
975 , c_attribute11
976 , c_attribute12
977 , c_attribute13
978 , c_attribute14
979 , c_attribute15
980 , c_attribute16
981 , c_attribute17
982 , c_attribute18
983 , c_attribute19
984 , c_attribute20
985 , d_attribute1
986 , d_attribute2
987 , d_attribute3
988 , d_attribute4
989 , d_attribute5
990 , d_attribute6
991 , d_attribute7
992 , d_attribute8
993 , d_attribute9
994 , d_attribute10
995 , n_attribute1
996 , n_attribute2
997 , n_attribute3
998 , n_attribute4
999 , n_attribute5
1000 , n_attribute6
1001 , n_attribute7
1002 , n_attribute8
1003 , n_attribute9
1004 , n_attribute10
1005 , status_id
1006 , territory_code
1007 , cost_group_id
1008 , organization_type
1009 , owning_organization_id
1010 , owning_tp_type
1011 , planning_organization_id
1012 , planning_tp_type
1013 , wip_entity_id
1014 , operation_seq_num
1015 , intraoperation_step_type
1016 )
1017 VALUES (
1018 p_inventory_item_id
1019 , p_serial_number
1020 , SYSDATE
1021 , l_userid
1022 , SYSDATE
1023 , l_userid
1024 , l_loginid
1025 , NULL
1026 , NULL
1027 , NULL
1028 , NULL
1029 , p_initialization_date
1030 , p_completion_date
1031 , p_ship_date
1032 , p_current_status
1033 , p_revision
1034 , p_lot_number
1035 , NULL
1036 , NULL
1037 , p_parent_item_id
1038 , p_parent_serial_number
1039 , p_trx_src_id
1040 , p_unit_vendor_id
1041 , p_vendor_serial_number
1042 , p_vendor_lot_number
1043 , p_txn_src_type_id
1044 , p_transaction_id
1045 , p_receipt_issue_type
1046 , p_txn_src_name
1047 , p_txn_src_id
1048 , g_serial_attributes_tbl(31).column_value
1049 , p_subinventory_code
1050 , p_current_locator_id
1051 , p_organization_id
1052 , NULL
1053 , NULL
1054 , NULL
1055 , NULL
1056 , NULL
1057 , NULL
1058 , NULL
1059 , NULL
1060 , NULL
1061 , NULL
1062 , NULL
1063 , NULL
1064 , NULL
1065 , NULL
1066 , NULL
1067 , NULL
1068 , NULL
1069 , NULL
1070 , NULL
1071 , NULL
1072 , x_object_id
1073 , g_serial_attributes_tbl(1).column_value
1074 , fnd_date.canonical_to_date(g_serial_attributes_tbl(2).column_value)
1075 , g_serial_attributes_tbl(3).column_value
1076 , g_serial_attributes_tbl(4).column_value
1077 , g_serial_attributes_tbl(5).column_value
1078 , g_serial_attributes_tbl(6).column_value
1079 , g_serial_attributes_tbl(7).column_value
1080 , g_serial_attributes_tbl(8).column_value
1081 , g_serial_attributes_tbl(9).column_value
1082 , g_serial_attributes_tbl(10).column_value
1083 , g_serial_attributes_tbl(11).column_value
1084 , g_serial_attributes_tbl(12).column_value
1085 , g_serial_attributes_tbl(13).column_value
1086 , g_serial_attributes_tbl(14).column_value
1087 , g_serial_attributes_tbl(15).column_value
1088 , g_serial_attributes_tbl(16).column_value
1089 , g_serial_attributes_tbl(17).column_value
1090 , g_serial_attributes_tbl(18).column_value
1091 , g_serial_attributes_tbl(19).column_value
1092 , g_serial_attributes_tbl(20).column_value
1093 , g_serial_attributes_tbl(21).column_value
1094 , g_serial_attributes_tbl(22).column_value
1095 , fnd_date.canonical_to_date(g_serial_attributes_tbl(23).column_value)
1096 , fnd_date.canonical_to_date(g_serial_attributes_tbl(24).column_value)
1097 , fnd_date.canonical_to_date(g_serial_attributes_tbl(25).column_value)
1098 , fnd_date.canonical_to_date(g_serial_attributes_tbl(26).column_value)
1099 , fnd_date.canonical_to_date(g_serial_attributes_tbl(27).column_value)
1100 , fnd_date.canonical_to_date(g_serial_attributes_tbl(28).column_value)
1101 , fnd_date.canonical_to_date(g_serial_attributes_tbl(29).column_value)
1102 , fnd_date.canonical_to_date(g_serial_attributes_tbl(30).column_value)
1103 , fnd_date.canonical_to_date(g_serial_attributes_tbl(31).column_value)
1104 , fnd_date.canonical_to_date(g_serial_attributes_tbl(32).column_value)
1105 , TO_NUMBER(g_serial_attributes_tbl(33).column_value)
1106 , TO_NUMBER(g_serial_attributes_tbl(34).column_value)
1107 , TO_NUMBER(g_serial_attributes_tbl(35).column_value)
1108 , TO_NUMBER(g_serial_attributes_tbl(36).column_value)
1109 , TO_NUMBER(g_serial_attributes_tbl(37).column_value)
1110 , TO_NUMBER(g_serial_attributes_tbl(38).column_value)
1111 , TO_NUMBER(g_serial_attributes_tbl(39).column_value)
1112 , TO_NUMBER(g_serial_attributes_tbl(40).column_value)
1113 , TO_NUMBER(g_serial_attributes_tbl(41).column_value)
1114 , TO_NUMBER(g_serial_attributes_tbl(42).column_value)
1115 , l_status_id
1116 , g_serial_attributes_tbl(44).column_value
1117 , inv_cost_group_pub.g_cost_group_id
1118 , NVL(p_organization_type, 2)
1119 , NVL(p_owning_org_id, p_organization_id)
1120 , NVL(p_owning_tp_type, 2)
1121 , NVL(p_planning_org_id, p_organization_id)
1122 , NVL(p_planning_tp_type, 2)
1123 , p_wip_entity_id
1124 , p_operation_seq_num
1125 , p_intraoperation_step_type
1126 );
1127 -- invtrace(' p_inventory_item_id ' || p_inventory_item_id);
1128 -- invtrace(', p_serial_number ' || p_serial_number);
1129 -- invtrace(' last_update_date ' || SYSDATE);
1130 -- invtrace('last updated by' || l_userid);
1131 -- invtrace('creation_date ' || SYSDATE);
1132 -- invtrace(' created_by ' ||l_userid);
1133 -- invtrace('last_update_login ' || l_loginid);
1134 -- invtrace('request_id ' || NULL);
1135 -- invtrace('program application id is NULL ');
1136 -- invtrace('program id is NULL');
1137 -- invtrace('program update date is NULL');
1138 -- invtrace('initialization_date is ' || p_initialization_date);
1139 -- invtrace('completion_date is ' || p_completion_date);
1140 -- invtrace('ship date is ' || p_ship_date);
1141 -- invtrace('current status is ' || p_current_status);
1142 -- invtrace('revision is ' || p_revision);
1143 -- invtrace('lot number is ' || p_lot_number);
1144 -- invtrace('fixed asset tag is NULL');
1145 -- invtrace('reserved order id is NULL');
1146 -- invtrace('parent item id is '|| p_parent_item_id);
1147 -- invtrace('parent_serial_number is ' || p_parent_serial_number);
1148 -- invtrace('trx_src_id is ' ||p_trx_src_id);
1149 -- invtrace('p_unit_vendor_id is ' ||p_unit_vendor_id);
1150 -- invtrace('p_vendor_serial_number is ' || p_vendor_serial_number);
1151 -- invtrace('p_vendor_lot_number is ' || p_vendor_lot_number);
1152 -- invtrace('p_txn_src_type_id is ' || p_txn_src_type_id);
1153 -- invtrace('p_transaction_id is ' || p_transaction_id);
1154 -- invtrace('p_receipt_issue_type ' || p_receipt_issue_type);
1155 -- invtrace('p_txn_src_name is ' || p_txn_src_name);
1156 -- invtrace('p_txn_src_id is ' || p_txn_src_id);
1157 -- invtrace('g_serial_attributes_tbl(31).column_value is ' || g_serial_attributes_tbl(31).column_value);
1158 -- invtrace('p_subinventory_code is ' || p_subinventory_code);
1159 -- invtrace('p_current_locator_id is ' || p_current_locator_id);
1160 -- invtrace('current organization_id is '|| p_organization_id);
1161 -- invtrace('attribute category is NULL');
1162 -- invtrace(' attributes 1 to 15 are NULL');
1163 -- invtrace('group mark id is NULL');
1164 -- invtrace('line mark_id is NULL');
1165 -- invtrace('lot_linemark_id is NULL');
1166 -- invtrace('end item unit number is NULL');
1167 -- invtrace('gen_object_id is ' ||x_object_id);
1168 -- invtrace('serial attribute category is ' || g_serial_attributes_tbl(1).column_value);
1169 -- invtrace('origination date is ' || fnd_date.canonical_to_date(g_serial_attributes_tbl(2).column_value));
1170 -- invtrace('c_attribute1 '|| g_serial_attributes_tbl(3).column_value);
1171 -- invtrace('cattribute2 ' || g_serial_attributes_tbl(4).column_value);
1172 -- invtrace('cattribute3 ' ||g_serial_attributes_tbl(5).column_value);
1173 -- invtrace('cattribute4 ' ||g_serial_attributes_tbl(6).column_value);
1174 -- invtrace('cattribute5 ' ||g_serial_attributes_tbl(7).column_value);
1175 -- invtrace('cattribute6 ' ||g_serial_attributes_tbl(8).column_value);
1176 -- invtrace('cattribute7 ' ||g_serial_attributes_tbl(9).column_value);
1177 -- invtrace('cattribute8 ' ||g_serial_attributes_tbl(10).column_value);
1178 -- invtrace('cattribute9 ' ||g_serial_attributes_tbl(11).column_value);
1179 -- invtrace('cattribute10 ' ||g_serial_attributes_tbl(12).column_value);
1180 -- invtrace('cattribute11 ' ||g_serial_attributes_tbl(13).column_value);
1181 -- invtrace('cattribute12 ' ||g_serial_attributes_tbl(14).column_value);
1182 -- invtrace('cattribute13 ' ||g_serial_attributes_tbl(15).column_value);
1183 -- invtrace('cattribute14 ' ||g_serial_attributes_tbl(16).column_value);
1184 -- invtrace('cattribute15 ' ||g_serial_attributes_tbl(17).column_value);
1185 -- invtrace('cattribute16 ' ||g_serial_attributes_tbl(18).column_value);
1186 -- invtrace('cattribute17 ' ||g_serial_attributes_tbl(19).column_value);
1187 -- invtrace('cattribute18 ' ||g_serial_attributes_tbl(20).column_value);
1188 -- invtrace('cattribute19 ' ||g_serial_attributes_tbl(21).column_value);
1189 -- invtrace('cattribute20 ' ||g_serial_attributes_tbl(22).column_value);
1190 -- invtrace('dattribute1 ' ||fnd_date.canonical_to_date(g_serial_attributes_tbl(23).column_value));
1191 -- invtrace('dattribute2 ' ||fnd_date.canonical_to_date(g_serial_attributes_tbl(24).column_value));
1192 -- invtrace('dattribute3 ' ||fnd_date.canonical_to_date(g_serial_attributes_tbl(25).column_value));
1193 -- invtrace('dattribute4 ' ||fnd_date.canonical_to_date(g_serial_attributes_tbl(26).column_value));
1194 -- invtrace('dattribute5 ' ||fnd_date.canonical_to_date(g_serial_attributes_tbl(27).column_value));
1195 -- invtrace('dattribute6 ' ||fnd_date.canonical_to_date(g_serial_attributes_tbl(28).column_value));
1196 -- invtrace('dattribute7 ' ||fnd_date.canonical_to_date(g_serial_attributes_tbl(29).column_value));
1197 -- invtrace('dattribute8 ' ||fnd_date.canonical_to_date(g_serial_attributes_tbl(30).column_value));
1198 -- invtrace('dattribute9 ' ||fnd_date.canonical_to_date(g_serial_attributes_tbl(31).column_value));
1199 -- invtrace('dattribute10 ' ||fnd_date.canonical_to_date(g_serial_attributes_tbl(32).column_value));
1200 -- invtrace('nattribute1 ' ||TO_NUMBER(g_serial_attributes_tbl(33).column_value));
1201 -- invtrace('nattribute2 ' ||TO_NUMBER(g_serial_attributes_tbl(34).column_value));
1202 -- invtrace('nattribute3 ' ||TO_NUMBER(g_serial_attributes_tbl(35).column_value));
1203 -- invtrace('nattribute4 ' ||TO_NUMBER(g_serial_attributes_tbl(36).column_value));
1204 -- invtrace('nattribute5 ' ||TO_NUMBER(g_serial_attributes_tbl(37).column_value));
1205 -- invtrace('nattribute6 ' ||TO_NUMBER(g_serial_attributes_tbl(38).column_value));
1206 -- invtrace('nattribute7 ' ||TO_NUMBER(g_serial_attributes_tbl(39).column_value));
1207 -- invtrace('nattribute8 ' ||TO_NUMBER(g_serial_attributes_tbl(40).column_value));
1208 -- invtrace('nattribute9 ' ||TO_NUMBER(g_serial_attributes_tbl(41).column_value));
1209 -- invtrace('nattribute10 ' ||TO_NUMBER(g_serial_attributes_tbl(42).column_value));
1210 -- invtrace('status id is ' ||l_status_id);
1211 -- invtrace('territory code is ' || g_serial_attributes_tbl(44).column_value);
1212 -- invtrace('cost group id is ' ||inv_cost_group_pub.g_cost_group_id);
1213 -- invtrace('organization type is ' ||NVL(p_organization_type, 2));
1214 -- invtrace('owning org id ' ||NVL(p_owning_org_id, p_organization_id));
1215 -- invtrace('owning tp type ' ||NVL(p_owning_tp_type, 2));
1216 -- invtrace('planning org id is ' ||NVL(p_planning_org_id, p_organization_id));
1217 -- invtrace('planning tp type is ' ||NVL(p_planning_tp_type, 2));
1218 -- invtrace('wip entity id is ' || p_wip_entity_id);
1219 -- invtrace('operation_seq_num is ' || p_operation_seq_num);
1220 -- invtrace('intraoperation_step_type is ' || p_intraoperation_step_type);
1221 END IF;
1222
1223 x_return_status := fnd_api.g_ret_sts_success;
1224
1225 -- insert into the status history table for bug 1870120
1226 IF (l_status_id IS NOT NULL) THEN
1227 l_status_rec.update_method := inv_material_status_pub.g_update_method_auto;
1228 l_status_rec.organization_id := p_organization_id;
1229 l_status_rec.inventory_item_id := p_inventory_item_id;
1230 l_status_rec.serial_number := p_serial_number;
1231 l_status_rec.status_id := l_status_id;
1232 l_status_rec.initial_status_flag := 'Y';
1233 inv_material_status_pkg.insert_status_history(l_status_rec);
1234 END IF;
1235
1236 -- Standard check of p_commit.
1237 IF fnd_api.to_boolean(p_commit) THEN
1238 COMMIT WORK;
1239 END IF;
1240
1241 -- Standard call to get message count and if count is 1, get message info.
1242 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
1243 EXCEPTION
1244 WHEN fnd_api.g_exc_error THEN
1245 ROLLBACK TO apiinsertserial_apipub;
1246 --Bug 3153585:Populating x_msg_data from message stack
1247 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
1248 x_return_status := fnd_api.g_ret_sts_error;
1249 WHEN OTHERS THEN
1250 ROLLBACK TO apiinsertserial_apipub;
1251
1252 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1253 fnd_msg_pub.add_exc_msg('INV_SERIAL_NUMBER_PUB', 'insertSerial');
1254 END IF;
1255
1256 --Bug 3153585:Populating x_msg_data from message stack
1257 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
1258 x_return_status := fnd_api.g_ret_sts_unexp_error;
1259 END insertserial;
1260
1261 PROCEDURE insert_range_serial(
1262 p_api_version IN NUMBER
1263 , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
1264 , p_commit IN VARCHAR2 := fnd_api.g_false
1265 , p_validation_level IN NUMBER := fnd_api.g_valid_level_full
1266 , p_inventory_item_id IN NUMBER
1267 , p_organization_id IN NUMBER
1268 , p_from_serial_number IN VARCHAR2
1269 , p_to_serial_number IN VARCHAR2
1270 , p_initialization_date IN DATE
1271 , p_completion_date IN DATE
1272 , p_ship_date IN DATE
1273 , p_revision IN VARCHAR2
1274 , p_lot_number IN VARCHAR2
1275 , p_current_locator_id IN NUMBER
1276 , p_subinventory_code IN VARCHAR2
1277 , p_trx_src_id IN NUMBER
1278 , p_unit_vendor_id IN NUMBER
1279 , p_vendor_lot_number IN VARCHAR2
1280 , p_vendor_serial_number IN VARCHAR2
1281 , p_receipt_issue_type IN NUMBER
1282 , p_txn_src_id IN NUMBER
1283 , p_txn_src_name IN VARCHAR2
1284 , p_txn_src_type_id IN NUMBER
1285 , p_transaction_id IN NUMBER
1286 , p_current_status IN NUMBER
1287 , p_parent_item_id IN NUMBER
1288 , p_parent_serial_number IN VARCHAR2
1289 , p_cost_group_id IN NUMBER
1290 , p_transaction_action_id IN NUMBER
1291 , p_transaction_temp_id IN NUMBER
1292 , p_status_id IN NUMBER
1293 , p_inspection_status IN NUMBER
1294 , x_object_id OUT NOCOPY NUMBER
1295 , x_return_status OUT NOCOPY VARCHAR2
1296 , x_msg_count OUT NOCOPY NUMBER
1297 , x_msg_data OUT NOCOPY VARCHAR2
1298 , p_organization_type IN NUMBER DEFAULT NULL
1299 , p_owning_org_id IN NUMBER DEFAULT NULL
1300 , p_owning_tp_type IN NUMBER DEFAULT NULL
1301 , p_planning_org_id IN NUMBER DEFAULT NULL
1302 , p_planning_tp_type IN NUMBER DEFAULT NULL
1303 , p_rcv_serial_flag IN VARCHAR2 DEFAULT NULL
1304 ) IS
1305 l_from_ser_number NUMBER;
1306 l_to_ser_number NUMBER;
1307 l_range_numbers NUMBER;
1308 l_temp_prefix VARCHAR2(30);
1309 l_cur_serial_number VARCHAR2(30);
1310 l_cur_ser_number NUMBER;
1311 l_object_id NUMBER;
1312 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
1313 l_msg_count NUMBER;
1314 l_msg_data VARCHAR2(2000);
1315 l_current_status NUMBER;
1316 l_group_mark_id NUMBER;
1317 l_api_version CONSTANT NUMBER := 1.0;
1318 l_api_name CONSTANT VARCHAR2(30) := 'insert_range_serial';
1319 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
1320 BEGIN
1321 -- invtrace('INV_SERIAL_NUMBER_PUB', 'insert_range_serial - 10');
1322 SAVEPOINT sp_insert_range_serial;
1323
1324 -- Standard call to check for call compatibility.
1325 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
1326 RAISE fnd_api.g_exc_unexpected_error;
1327 END IF;
1328
1329 -- Initialize message list if p_init_msg_list is set to TRUE.
1330 IF fnd_api.to_boolean(p_init_msg_list) THEN
1331 fnd_msg_pub.initialize;
1332 END IF;
1333
1334 -- Initialize API return status to success
1335 x_return_status := fnd_api.g_ret_sts_success;
1336 -- get the number part of the from serial
1337 inv_validate.number_from_sequence(p_from_serial_number, l_temp_prefix, l_from_ser_number);
1338 -- get the number part of the to serial
1339 inv_validate.number_from_sequence(p_to_serial_number, l_temp_prefix, l_to_ser_number);
1340 -- total number of serials inserted into mtl_serial_numbers
1341 l_range_numbers := l_to_ser_number - l_from_ser_number + 1;
1342
1343 FOR i IN 1 .. l_range_numbers LOOP
1344 l_cur_ser_number := l_from_ser_number + i - 1;
1345
1346 -- concatenate the serial number to be inserted
1347 IF (l_from_ser_number = -1
1348 AND l_to_ser_number = -1) THEN
1349 l_cur_serial_number := p_from_serial_number;
1350 ELSE
1351 l_cur_serial_number := SUBSTR(p_from_serial_number, 1, LENGTH(p_from_serial_number) - LENGTH(l_cur_ser_number))
1352 || l_cur_ser_number;
1353 END IF;
1354
1355 -- check the status code and group_mark_id
1356 BEGIN
1357 SELECT current_status
1358 , NVL(group_mark_id, -1)
1359 INTO l_current_status
1360 , l_group_mark_id
1361 FROM mtl_serial_numbers
1362 WHERE serial_number = l_cur_serial_number
1363 AND inventory_item_id = p_inventory_item_id;
1364 EXCEPTION
1365 WHEN NO_DATA_FOUND THEN
1366 l_current_status := -1;
1367 l_group_mark_id := -2;
1368 WHEN OTHERS THEN
1369 NULL;
1370 END;
1371
1372 --invtrace('INV_SERIAL_NUMBER_PUB', ' l_cur_serial_number, l_current_status AND l_group_mark_id ' || l_cur_serial_number || ' ' || l_current_status
1373 -- || ' : ' || l_group_mark_id);
1374
1375 /* Bug #1767236
1376 * When the status is 4 (out of stores) then update the status do not insert
1377 */
1378 IF (l_current_status = 1
1379 AND l_group_mark_id = -1)
1380 OR(l_current_status = 4
1381 AND l_group_mark_id = -1)
1382 OR(l_current_status = 6
1383 AND l_group_mark_id = -1) THEN
1384
1385 -- Bug 5385315, Update the current_organization_id to p_organization_id
1386 -- in mtl_serial_numbers while updating the current_status from 4 to 1.
1387
1388 IF (p_current_status = 1 AND l_current_status = 4) THEN
1389 -- pre-defined serial, update status
1390 UPDATE mtl_serial_numbers
1391 SET current_status = p_current_status
1392 , inspection_status = p_inspection_status
1393 , lpn_id = null --bug 5152103
1394 , current_organization_id = p_organization_id
1395 WHERE serial_number = l_cur_serial_number
1396 AND inventory_item_id = p_inventory_item_id;
1397 ELSE
1398 -- pre-defined serial, update status
1399 UPDATE mtl_serial_numbers
1400 SET current_status = p_current_status
1401 , inspection_status = p_inspection_status
1402 -- , lpn_id = decode(p_current_status,1,decode(current_status,4,null,lpn_id),lpn_id) --bug 5152103
1403 WHERE serial_number = l_cur_serial_number
1404 AND inventory_item_id = p_inventory_item_id;
1405 END IF;
1406 /* FP-J Lot/Serial Support Enhancement - Check for serial uniqueness
1407 * when the current status is Resides in receiving(7) also
1408 */
1409 ELSIF (l_current_status <> 5)
1410 OR(l_current_status = 5
1411 AND l_group_mark_id > 0)
1412 OR(l_current_status = 7
1413 AND l_group_mark_id > 0) THEN
1414 -- Need to do uniqueness check here.
1415 -- If any serial is in use, then discard the entire range insertion.
1416 IF is_serial_unique(p_org_id => p_organization_id, p_item_id => p_inventory_item_id, p_serial => l_cur_serial_number
1417 , x_proc_msg => l_msg_data) = 1 THEN
1418 fnd_message.set_name('INV', 'INV_SERIAL_USED');
1419 fnd_msg_pub.ADD;
1420 RAISE fnd_api.g_exc_error;
1421 ELSE
1422 -- invtrace('INV_SERIAL_NUMBER_PUB', 'insert_range_serial - 60');
1423 -- uniqueness check passed
1424 -- and it is not a pre-defined serial
1425 inv_serial_number_pub.insertserial(
1426 p_api_version => p_api_version
1427 , p_init_msg_list => p_init_msg_list
1428 , p_commit => p_commit
1429 , p_validation_level => p_validation_level
1430 , p_inventory_item_id => p_inventory_item_id
1431 , p_organization_id => p_organization_id
1432 , p_serial_number => l_cur_serial_number
1433 , p_initialization_date => p_initialization_date
1434 , p_completion_date => p_completion_date
1435 , p_ship_date => p_ship_date
1436 , p_revision => p_revision
1437 , p_lot_number => p_lot_number
1438 , p_current_locator_id => p_current_locator_id
1439 , p_subinventory_code => p_subinventory_code
1440 , p_trx_src_id => p_trx_src_id
1441 , p_unit_vendor_id => p_unit_vendor_id
1442 , p_vendor_lot_number => p_vendor_lot_number
1443 , p_vendor_serial_number => p_vendor_serial_number
1444 , p_receipt_issue_type => p_receipt_issue_type
1445 , p_txn_src_id => p_txn_src_id
1446 , p_txn_src_name => p_txn_src_name
1447 , p_txn_src_type_id => p_txn_src_type_id
1448 , p_transaction_id => p_transaction_id
1449 , p_current_status => p_current_status
1450 , p_parent_item_id => p_parent_item_id
1451 , p_parent_serial_number => p_parent_serial_number
1452 , p_cost_group_id => p_cost_group_id
1453 , p_transaction_action_id => p_transaction_action_id
1454 , p_transaction_temp_id => p_transaction_temp_id
1455 , p_status_id => p_status_id
1456 , x_object_id => l_object_id
1457 , x_return_status => l_return_status
1458 , x_msg_count => l_msg_count
1459 , x_msg_data => l_msg_data
1460 , p_organization_type => p_organization_type
1461 , p_owning_org_id => p_owning_org_id
1462 , p_owning_tp_type => p_owning_tp_type
1463 , p_planning_org_id => p_planning_org_id
1464 , p_planning_tp_type => p_planning_tp_type
1465 );
1466
1467 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
1468 fnd_message.set_name('INV', 'INV_SERIAL');
1469 fnd_msg_pub.ADD;
1470 RAISE fnd_api.g_exc_unexpected_error;
1471 END IF;
1472 END IF;
1473 END IF;
1474
1475 /* FP-J Lot/Serial Support Enhancement
1476 * If INV J and PO J are installed and this API is called from
1477 * the Mobile receiving UI, then we should only attach the revision and
1478 * lot number to the serial if it was newly created above (in which case
1479 * the current status of the serial would be defined but not used)
1480 * We should not be updating any other columns for the serial number
1481 * For this case, the flag p_rcv_serial_flag would have the value 'Y'
1482 *
1483 * If WMS or PO J are not installed or this API is called from other
1484 * routines, then updates to the serial number are retained
1485 */
1486 IF (NVL(p_rcv_serial_flag, 'N') <> 'Y') THEN
1487 UPDATE mtl_serial_numbers
1488 SET inspection_status = p_inspection_status
1489 , lot_number = p_lot_number
1490 , revision = p_revision
1491 , current_organization_id = p_organization_id
1492 , organization_type = NVL(p_organization_type, 2)
1493 , owning_organization_id = NVL(p_owning_org_id, p_organization_id)
1494 , owning_tp_type = NVL(p_owning_tp_type, 2)
1495 , planning_organization_id = NVL(p_planning_org_id, p_organization_id)
1496 , planning_tp_type = NVL(p_planning_tp_type, 2)
1497 WHERE serial_number = l_cur_serial_number
1498 AND inventory_item_id = p_inventory_item_id;
1499 ELSE
1500 UPDATE mtl_serial_numbers
1501 SET lot_number = p_lot_number
1502 , revision = p_revision
1503 WHERE serial_number = l_cur_serial_number
1504 AND inventory_item_id = p_inventory_item_id
1505 AND current_status IN(1, 4, 5, 6);
1506 END IF;
1507 END LOOP;
1508
1509 x_return_status := fnd_api.g_ret_sts_success;
1510
1511 -- End of API body
1512 -- Standard check of p_commit.
1513 IF fnd_api.to_boolean(p_commit) THEN
1514 COMMIT WORK;
1515 END IF;
1516
1517 -- Standard call to get message count and if count is 1,
1518 -- get message info.
1519 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1520 EXCEPTION
1521 WHEN fnd_api.g_exc_error THEN
1522 ROLLBACK TO sp_insert_range_serial;
1523 x_return_status := fnd_api.g_ret_sts_error;
1524 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1525 WHEN fnd_api.g_exc_unexpected_error THEN
1526 ROLLBACK TO sp_insert_range_serial;
1527 x_return_status := fnd_api.g_ret_sts_unexp_error;
1528 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1529 WHEN OTHERS THEN
1530 ROLLBACK TO sp_insert_range_serial;
1531 x_return_status := fnd_api.g_ret_sts_unexp_error;
1532 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1533 END insert_range_serial;
1534
1535 PROCEDURE updateserial(
1536 p_api_version IN NUMBER
1537 , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
1538 , p_commit IN VARCHAR2 := fnd_api.g_false
1539 , p_validation_level IN NUMBER := fnd_api.g_valid_level_full
1540 , p_inventory_item_id IN NUMBER
1541 , p_organization_id IN NUMBER
1542 , p_serial_number IN VARCHAR2
1543 , p_initialization_date IN DATE
1544 , p_completion_date IN DATE
1545 , p_ship_date IN DATE
1546 , p_revision IN VARCHAR2
1547 , p_lot_number IN VARCHAR2
1548 , p_current_locator_id IN NUMBER
1549 , p_subinventory_code IN VARCHAR2
1550 , p_trx_src_id IN NUMBER
1551 , p_unit_vendor_id IN NUMBER
1552 , p_vendor_lot_number IN VARCHAR2
1553 , p_vendor_serial_number IN VARCHAR2
1554 , p_receipt_issue_type IN NUMBER
1555 , p_txn_src_id IN NUMBER
1556 , p_txn_src_name IN VARCHAR2
1557 , p_txn_src_type_id IN NUMBER
1558 , p_current_status IN NUMBER
1559 , p_parent_item_id IN NUMBER
1560 , p_parent_serial_number IN VARCHAR2
1561 , p_serial_temp_id IN NUMBER
1562 , p_last_status IN NUMBER
1563 , p_status_id IN NUMBER
1564 , x_object_id OUT NOCOPY NUMBER
1565 , x_return_status OUT NOCOPY VARCHAR2
1566 , x_msg_count OUT NOCOPY NUMBER
1567 , x_msg_data OUT NOCOPY VARCHAR2
1568 , p_organization_type IN NUMBER DEFAULT NULL
1569 , p_owning_org_id IN NUMBER DEFAULT NULL
1570 , p_owning_tp_type IN NUMBER DEFAULT NULL
1571 , p_planning_org_id IN NUMBER DEFAULT NULL
1572 , p_planning_tp_type IN NUMBER DEFAULT NULL
1573 , p_transaction_action_id IN NUMBER DEFAULT NULL
1574 , p_wip_entity_id IN NUMBER DEFAULT NULL
1575 , p_operation_seq_num IN NUMBER DEFAULT NULL
1576 , p_intraoperation_step_type IN NUMBER DEFAULT NULL
1577 , p_line_mark_id IN NUMBER DEFAULT NULL
1578 ) IS
1579 l_api_version CONSTANT NUMBER := 1.0;
1580 l_api_name CONSTANT VARCHAR2(30) := 'updateSerial';
1581 l_userid NUMBER;
1582 l_loginid NUMBER;
1583 l_serial_attribute_category VARCHAR2(150);
1584 l_origination_date DATE;
1585 l_c_attribute1 VARCHAR2(150);
1586 l_c_attribute2 VARCHAR2(150);
1587 l_c_attribute3 VARCHAR2(150);
1588 l_c_attribute4 VARCHAR2(150);
1589 l_c_attribute5 VARCHAR2(150);
1590 l_c_attribute6 VARCHAR2(150);
1591 l_c_attribute7 VARCHAR2(150);
1592 l_c_attribute8 VARCHAR2(150);
1593 l_c_attribute9 VARCHAR2(150);
1594 l_c_attribute10 VARCHAR2(150);
1595 l_c_attribute11 VARCHAR2(150);
1596 l_c_attribute12 VARCHAR2(150);
1597 l_c_attribute13 VARCHAR2(150);
1598 l_c_attribute14 VARCHAR2(150);
1599 l_c_attribute15 VARCHAR2(150);
1600 l_c_attribute16 VARCHAR2(150);
1601 l_c_attribute17 VARCHAR2(150);
1602 l_c_attribute18 VARCHAR2(150);
1603 l_c_attribute19 VARCHAR2(150);
1604 l_c_attribute20 VARCHAR2(150);
1605 l_d_attribute1 DATE;
1606 l_d_attribute2 DATE;
1607 l_d_attribute3 DATE;
1608 l_d_attribute4 DATE;
1609 l_d_attribute5 DATE;
1610 l_d_attribute6 DATE;
1611 l_d_attribute7 DATE;
1612 l_d_attribute8 DATE;
1613 l_d_attribute9 DATE;
1614 l_d_attribute10 DATE;
1615 l_n_attribute1 NUMBER;
1616 l_n_attribute2 NUMBER;
1617 l_n_attribute3 NUMBER;
1618 l_n_attribute4 NUMBER;
1619 l_n_attribute5 NUMBER;
1620 l_n_attribute6 NUMBER;
1621 l_n_attribute7 NUMBER;
1622 l_n_attribute8 NUMBER;
1623 l_n_attribute9 NUMBER;
1624 l_n_attribute10 NUMBER;
1625 l_territory_code VARCHAR2(150);
1626 l_wms_installed BOOLEAN;
1627 l_return_status VARCHAR2(1);
1628 l_msg_data VARCHAR2(2000);
1629 l_msg_count NUMBER;
1630 l_group_mark_id NUMBER;
1631
1632 CURSOR l_serial_attr_csr(p_serial_temp_id NUMBER) IS
1633 SELECT serial_attribute_category
1634 , origination_date
1635 , c_attribute1
1636 , c_attribute2
1637 , c_attribute3
1638 , c_attribute4
1639 , c_attribute5
1640 , c_attribute6
1641 , c_attribute7
1642 , c_attribute8
1643 , c_attribute9
1644 , c_attribute10
1645 , c_attribute11
1646 , c_attribute12
1647 , c_attribute13
1648 , c_attribute14
1649 , c_attribute15
1650 , c_attribute16
1651 , c_attribute17
1652 , c_attribute18
1653 , c_attribute19
1654 , c_attribute20
1655 , d_attribute1
1656 , d_attribute2
1657 , d_attribute3
1658 , d_attribute4
1659 , d_attribute5
1660 , d_attribute6
1661 , d_attribute7
1662 , d_attribute8
1663 , d_attribute9
1664 , d_attribute10
1665 , n_attribute1
1666 , n_attribute2
1667 , n_attribute3
1668 , n_attribute4
1669 , n_attribute5
1670 , n_attribute6
1671 , n_attribute7
1672 , n_attribute8
1673 , n_attribute9
1674 , n_attribute10
1675 , territory_code
1676 FROM mtl_serial_numbers_temp
1677 WHERE transaction_temp_id = p_serial_temp_id
1678 AND p_serial_number BETWEEN fm_serial_number AND NVL(to_serial_number, fm_serial_number);
1679
1680 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
1681 BEGIN
1682 -- Standard Start of API savepoint
1683 SAVEPOINT apiupdateserial_apipub;
1684
1685 IF (l_debug = 1) THEN
1686 invtrace('*** Inside UpdateSerial ****');
1687 END IF;
1688
1689 -- Standard call to check for call compatibility.
1690 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
1691 RAISE fnd_api.g_exc_unexpected_error;
1692 END IF;
1693
1694 -- Initialize message list if p_init_msg_list is set to TRUE.
1695 IF fnd_api.to_boolean(p_init_msg_list) THEN
1696 fnd_msg_pub.initialize;
1697 END IF;
1698
1699 -- Initialize API return status to success
1700 x_return_status := fnd_api.g_ret_sts_success;
1701 -- API body
1702
1703 IF (l_debug = 1) THEN
1704 invtrace('CurStat =' || p_current_status || ',CG=' ||
1705 inv_cost_group_pub.g_cost_group_id || ',lastStat=' ||
1706 p_last_status || 'p_wip_entity_id = ' || p_wip_entity_id ||
1707 'SerialNumber = ' || p_serial_number);
1708 END IF;
1709 l_userid := fnd_global.user_id;
1710 l_loginid := fnd_global.login_id;
1711
1712 -- Get serial-attributes only if the last-state of serialNumber
1713 -- is 'Defined But Not Used' (1) or 'Dynamically generated' (6)
1714 IF p_wip_entity_id IS NOT NULL THEN
1715 l_group_mark_id := p_wip_entity_id;
1716 ELSE
1717 l_group_mark_id := NULL;
1718 END IF;
1719
1720 IF (p_last_status = 1)
1721 OR(p_last_status = 6) THEN
1722 l_wms_installed :=
1723 wms_install.check_install(x_return_status => l_return_status, x_msg_count => l_msg_count, x_msg_data => l_msg_data
1724 , p_organization_id => p_organization_id);
1725
1726 IF (l_wms_installed AND p_serial_temp_id IS NOT NULL) THEN
1727 -- invtrace('wms is installed... opening l_serial_attr_csr with serial_temp_id as '|| p_serial_temp_id);
1728 OPEN l_serial_attr_csr(p_serial_temp_id);
1729
1730 FETCH l_serial_attr_csr
1731 INTO l_serial_attribute_category
1732 , l_origination_date
1733 , l_c_attribute1
1734 , l_c_attribute2
1735 , l_c_attribute3
1736 , l_c_attribute4
1737 , l_c_attribute5
1738 , l_c_attribute6
1739 , l_c_attribute7
1740 , l_c_attribute8
1741 , l_c_attribute9
1742 , l_c_attribute10
1743 , l_c_attribute11
1744 , l_c_attribute12
1745 , l_c_attribute13
1746 , l_c_attribute14
1747 , l_c_attribute15
1748 , l_c_attribute16
1749 , l_c_attribute17
1750 , l_c_attribute18
1751 , l_c_attribute19
1752 , l_c_attribute20
1753 , l_d_attribute1
1754 , l_d_attribute2
1755 , l_d_attribute3
1756 , l_d_attribute4
1757 , l_d_attribute5
1758 , l_d_attribute6
1759 , l_d_attribute7
1760 , l_d_attribute8
1761 , l_d_attribute9
1762 , l_d_attribute10
1763 , l_n_attribute1
1764 , l_n_attribute2
1765 , l_n_attribute3
1766 , l_n_attribute4
1767 , l_n_attribute5
1768 , l_n_attribute6
1769 , l_n_attribute7
1770 , l_n_attribute8
1771 , l_n_attribute9
1772 , l_n_attribute10
1773 , l_territory_code;
1774 --END IF;
1775
1776 -- invtrace('updating MSN with values ');
1777 -- invtrace('current_status '|| p_current_status);
1778 -- invtrace('initialization_date '|| initialization_date);
1779 -- invtrace('completion_date '|| p_completion_date);
1780 -- invtrace('ship_date '|| p_ship_date);
1781 -- invtrace('revision '|| p_revision);
1782 -- invtrace('lot_number '|| p_lot_number);
1783 -- do not update group mark id for staging transfer vipartha
1784 -- invtrace('group_mark_id '|| DECODE(p_transaction_action_id, 28, group_mark_id, NULL));
1785 -- invtrace('line_mark_id '|| NULL);
1786 -- invtrace('lot_line_mark_id '|| NULL);
1787 -- invtrace('current_organization_id '|| p_organization_id);
1788 -- invtrace('organization_type '|| NVL(p_organization_type, 2));
1789 -- invtrace('owning_organization_id '|| NVL(p_owning_org_id, p_organization_id));
1790 -- invtrace('owning_tp_type '|| NVL(p_owning_tp_type, 2));
1791 -- invtrace('planning_organization_id '|| NVL(p_planning_org_id, p_organization_id));
1792 -- invtrace('planning_tp_type '|| NVL(p_planning_tp_type, 2));
1793 -- invtrace('current_locator_id '|| p_current_locator_id);
1794 -- invtrace('current_subinventory_code ' || p_subinventory_code);
1795 -- invtrace('original_wip_entity_id ' || p_trx_src_id);
1796 -- invtrace('original_unit_vendor_id ' || p_unit_vendor_id);
1797 -- invtrace('vendor_lot_number ' || p_vendor_lot_number);
1798 -- invtrace('vendor_serial_number ' || p_vendor_serial_number);
1799 -- invtrace('last_receipt_issue_type ' || p_receipt_issue_type);
1800 -- invtrace('last_txn_source_id ' || p_txn_src_id);
1801 -- invtrace('last_txn_source_type_id ' || p_txn_src_type_id);
1802 -- invtrace('last_txn_source_name ' || p_txn_src_name);
1803 -- invtrace('last_update_date ' || SYSDATE);
1804 -- invtrace('last_updated_by ' || l_userid);
1805 -- invtrace('parent_item_id ' || p_parent_item_id);
1806 -- invtrace('parent_serial_number ' || p_parent_serial_number);
1807 -- invtrace('origination_date ' || l_origination_date);
1808 -- invtrace('c_attribute1 ' || l_c_attribute1);
1809 -- invtrace('c_attribute2 ' || l_c_attribute2);
1810 -- invtrace('c_attribute3 ' || l_c_attribute3);
1811 -- invtrace('c_attribute4 ' || l_c_attribute4);
1812 -- invtrace('c_attribute5 ' || l_c_attribute5);
1813 -- invtrace('c_attribute6 ' || l_c_attribute6);
1814 -- invtrace('c_attribute7 ' || l_c_attribute7);
1815 -- invtrace('c_attribute8 ' || l_c_attribute8);
1816 -- invtrace('c_attribute9 ' || l_c_attribute9);
1817 -- invtrace('c_attribute10 ' || l_c_attribute10);
1818 -- invtrace('c_attribute11 ' || l_c_attribute11);
1819 -- invtrace('c_attribute12 ' || l_c_attribute12);
1820 -- invtrace('c_attribute13 ' || l_c_attribute13);
1821 -- invtrace('c_attribute14 ' || l_c_attribute14);
1822 -- invtrace('c_attribute15 ' || l_c_attribute15);
1823 -- invtrace('c_attribute16 ' || l_c_attribute16);
1824 -- invtrace('c_attribute17 ' || l_c_attribute17);
1825 -- invtrace('c_attribute18 ' || l_c_attribute18);
1826 -- invtrace('c_attribute19 ' || l_c_attribute19);
1827 -- invtrace('c_attribute20 ' || l_c_attribute20);
1828 -- invtrace('d_attribute1 ' || l_d_attribute1);
1829 -- invtrace('d_attribute2 ' || l_d_attribute2);
1830 -- invtrace('d_attribute3 ' || l_d_attribute3);
1831 -- invtrace('d_attribute4 ' || l_d_attribute4);
1832 -- invtrace('d_attribute5 ' || l_d_attribute5);
1833 -- invtrace('d_attribute6 ' || l_d_attribute6);
1834 -- invtrace('d_attribute7 ' || l_d_attribute7);
1835 -- invtrace('d_attribute8 ' || l_d_attribute8);
1836 -- invtrace('d_attribute9 ' || l_d_attribute9);
1837 -- invtrace('d_attribute10 ' || l_d_attribute10);
1838 -- invtrace('n_attribute1 ' || l_n_attribute1);
1839 -- invtrace('n_attribute2 ' || l_n_attribute2);
1840 -- invtrace('n_attribute3 ' || l_n_attribute3);
1841 -- invtrace('n_attribute4 ' || l_n_attribute4);
1842 -- invtrace('n_attribute5 ' || l_n_attribute5);
1843 -- invtrace('n_attribute6 ' || l_n_attribute6);
1844 -- invtrace('n_attribute7 ' || l_n_attribute7);
1845 -- invtrace('n_attribute8 ' || l_n_attribute8);
1846 -- invtrace('n_attribute9 ' || l_n_attribute9);
1847 -- invtrace('n_attribute10 ' || l_n_attribute10);
1848 -- invtrace('territory_code ' || l_territory_code);
1849 -- invtrace('cost_group_id ' || inv_cost_group_pub.g_cost_group_id);
1850
1851 UPDATE mtl_serial_numbers
1852 SET current_status = decode(p_current_status, null,
1853 decode(p_wip_entity_id, null, current_status, decode(current_status, 6, 1, current_status)),
1854 decode(p_wip_entity_id, null, p_current_status,decode(p_current_status, 6, 1, p_current_status)) )
1855 , initialization_date = initialization_date
1856 , completion_date = p_completion_date
1857 , ship_date = p_ship_date
1858 , revision = p_revision
1859 , lot_number = p_lot_number
1860 , -- do not update group mark id for staging transfer vipartha
1861 -- group_mark_id = DECODE(p_transaction_action_id, 28, group_mark_id, NULL)
1862 group_mark_id = DECODE(p_transaction_action_id, 28, group_mark_id, l_group_mark_id)
1863 , line_mark_id = p_line_mark_id
1864 , lot_line_mark_id = NULL
1865 , current_organization_id = p_organization_id
1866 , organization_type = NVL(p_organization_type, 2)
1867 , owning_organization_id = NVL(p_owning_org_id, p_organization_id)
1868 , owning_tp_type = NVL(p_owning_tp_type, 2)
1869 , planning_organization_id = NVL(p_planning_org_id, p_organization_id)
1870 , planning_tp_type = NVL(p_planning_tp_type, 2)
1871 , current_locator_id = p_current_locator_id
1872 , current_subinventory_code = p_subinventory_code
1873 , original_wip_entity_id = p_trx_src_id
1874 , original_unit_vendor_id = p_unit_vendor_id
1875 , vendor_lot_number = p_vendor_lot_number
1876 , vendor_serial_number = p_vendor_serial_number
1877 , last_receipt_issue_type = p_receipt_issue_type
1878 , last_txn_source_id = p_txn_src_id
1879 , last_txn_source_type_id = p_txn_src_type_id
1880 , last_txn_source_name = p_txn_src_name
1881 , last_update_date = SYSDATE
1882 , last_updated_by = l_userid
1883 , parent_item_id = p_parent_item_id
1884 , parent_serial_number = p_parent_serial_number
1885 , origination_date = l_origination_date
1886 , c_attribute1 = l_c_attribute1
1887 , c_attribute2 = l_c_attribute2
1888 , c_attribute3 = l_c_attribute3
1889 , c_attribute4 = l_c_attribute4
1890 , c_attribute5 = l_c_attribute5
1891 , c_attribute6 = l_c_attribute6
1892 , c_attribute7 = l_c_attribute7
1893 , c_attribute8 = l_c_attribute8
1894 , c_attribute9 = l_c_attribute9
1895 , c_attribute10 = l_c_attribute10
1896 , c_attribute11 = l_c_attribute11
1897 , c_attribute12 = l_c_attribute12
1898 , c_attribute13 = l_c_attribute13
1899 , c_attribute14 = l_c_attribute14
1900 , c_attribute15 = l_c_attribute15
1901 , c_attribute16 = l_c_attribute16
1902 , c_attribute17 = l_c_attribute17
1903 , c_attribute18 = l_c_attribute18
1904 , c_attribute19 = l_c_attribute19
1905 , c_attribute20 = l_c_attribute20
1906 , d_attribute1 = l_d_attribute1
1907 , d_attribute2 = l_d_attribute2
1908 , d_attribute3 = l_d_attribute3
1909 , d_attribute4 = l_d_attribute4
1910 , d_attribute5 = l_d_attribute5
1911 , d_attribute6 = l_d_attribute6
1912 , d_attribute7 = l_d_attribute7
1913 , d_attribute8 = l_d_attribute8
1914 , d_attribute9 = l_d_attribute9
1915 , d_attribute10 = l_d_attribute10
1916 , n_attribute1 = l_n_attribute1
1917 , n_attribute2 = l_n_attribute2
1918 , n_attribute3 = l_n_attribute3
1919 , n_attribute4 = l_n_attribute4
1920 , n_attribute5 = l_n_attribute5
1921 , n_attribute6 = l_n_attribute6
1922 , n_attribute7 = l_n_attribute7
1923 , n_attribute8 = l_n_attribute8
1924 , n_attribute9 = l_n_attribute9
1925 , n_attribute10 = l_n_attribute10
1926 , territory_code = l_territory_code
1927 , cost_group_id = inv_cost_group_pub.g_cost_group_id
1928 , wip_entity_id = p_wip_entity_id
1929 , operation_seq_num = p_operation_seq_num
1930 , intraoperation_step_type = p_intraoperation_step_type
1931 WHERE inventory_item_id = p_inventory_item_id
1932 AND serial_number = p_serial_number
1933 AND DECODE(current_status, 6, 1, current_status) = DECODE(p_last_status, 6, 1, p_last_status);
1934 ELSE
1935 UPDATE mtl_serial_numbers
1936 SET current_status = decode(p_current_status, null,
1937 decode(p_wip_entity_id, null, current_status, decode(current_status, 6, 1, current_Status)),
1938 decode(p_wip_entity_id, null, p_current_status, decode(p_current_status, 6, 1, p_current_status)))
1939 , initialization_date = initialization_date
1940 , completion_date = p_completion_date
1941 , ship_date = p_ship_date
1942 , revision = p_revision
1943 , lot_number = p_lot_number
1944 , -- do not update group mark id for staging transfer vipartha
1945 --group_mark_id = DECODE(p_transaction_action_id, 28, group_mark_id, NULL)
1946 group_mark_id = DECODE(p_transaction_action_id, 28, group_mark_id, l_group_mark_id)
1947 , line_mark_id = p_line_mark_id
1948 , lot_line_mark_id = NULL
1949 , current_organization_id = p_organization_id
1950 , current_locator_id = p_current_locator_id
1951 , current_subinventory_code = p_subinventory_code
1952 , original_wip_entity_id = p_trx_src_id
1953 , original_unit_vendor_id = p_unit_vendor_id
1954 , vendor_lot_number = p_vendor_lot_number
1955 , vendor_serial_number = p_vendor_serial_number
1956 , last_receipt_issue_type = p_receipt_issue_type
1957 , last_txn_source_id = p_txn_src_id
1958 , last_txn_source_type_id = p_txn_src_type_id
1959 , last_txn_source_name = p_txn_src_name
1960 , last_update_date = SYSDATE
1961 , last_updated_by = l_userid
1962 , parent_item_id = p_parent_item_id
1963 , parent_serial_number = p_parent_serial_number
1964 , wip_entity_id = p_wip_entity_id
1965 , operation_seq_num = p_operation_seq_num
1966 , intraoperation_step_type = p_intraoperation_step_type
1967 WHERE inventory_item_id = p_inventory_item_id
1968 AND serial_number = p_serial_number
1969 AND DECODE(current_status, 6, 1, current_status) = DECODE(p_last_status, 6, 1, p_last_status);
1970 END IF; -- !wms_installed OR p_serial_temp_id is NULL. --Bug4535887
1971 ELSE
1972 -- invtrace('last status is neither 1 nor 6');
1973 -- invtrace('updating MSN WITH values ');
1974 -- invtrace('current_status ' || p_current_status);
1975 -- invtrace(' initialization_date ' || initialization_date);
1976 -- invtrace(' completion_date ' || p_completion_date);
1977 -- invtrace(' ship_date ' || p_ship_date);
1978 -- invtrace(' revision ' || p_revision);
1979 -- invtrace(' lot_number ' || p_lot_number);
1980 -- do not update group mark id for staging transfer vipartha);
1981 -- invtrace('group_mark_id ' || DECODE(p_transaction_action_id,28,group_mark_id, NULL));
1982 -- invtrace(' line_mark_id is NULL');
1983 -- invtrace(' lot_line_mark_id IS NULL');
1984 -- invtrace(' current_organization_id ' || p_organization_id);
1985 -- invtrace(' current_locator_id ' || p_current_locator_id);
1986 -- invtrace(' current_subinventory_code ' || p_subinventory_code);
1987 -- invtrace(' original_wip_entity_id ' || p_trx_src_id);
1988 -- invtrace(' original_unit_vendor_id ' || p_unit_vendor_id);
1989 -- invtrace(' vendor_lot_number ' || p_vendor_lot_number);
1990 -- invtrace(' vendor_serial_number ' || p_vendor_serial_number);
1991 -- invtrace(' last_receipt_issue_type ' || p_receipt_issue_type);
1992 -- invtrace(' last_txn_source_id ' || p_txn_src_id);
1993 -- invtrace(' last_txn_source_type_id ' || p_txn_src_type_id);
1994 -- invtrace(' last_txn_source_name ' || p_txn_src_name);
1995 -- invtrace(' last_update_date ' || SYSDATE);
1996 -- invtrace(' last_updated_by ' || l_userid);
1997 -- invtrace(' parent_item_id ' || p_parent_item_id);
1998 -- invtrace(' parent_serial_number ' || p_parent_serial_number);
1999 UPDATE mtl_serial_numbers
2000 SET current_status = decode(p_current_status, null,
2001 decode(p_wip_entity_id, null, current_status, decode(current_status, 6, 1, current_Status)),
2002 decode(p_wip_entity_id, null, p_current_status, decode(p_current_status, 6, 1, p_current_status)))
2003 , initialization_date = initialization_date
2004 , completion_date = p_completion_date
2005 , ship_date = p_ship_date
2006 , revision = p_revision
2007 , lot_number = p_lot_number
2008 , -- do not update group mark id for staging transfer vipartha
2009 --group_mark_id = DECODE(p_transaction_action_id, 28, group_mark_id, NULL)
2010 group_mark_id = DECODE(p_transaction_action_id, 28, group_mark_id, l_group_mark_id)
2011 , line_mark_id = p_line_mark_id
2012 , lot_line_mark_id = NULL
2013 , current_organization_id = p_organization_id
2014 , current_locator_id = p_current_locator_id
2015 , current_subinventory_code = p_subinventory_code
2016 , original_wip_entity_id = p_trx_src_id
2017 , original_unit_vendor_id = p_unit_vendor_id
2018 , vendor_lot_number = p_vendor_lot_number
2019 , vendor_serial_number = p_vendor_serial_number
2020 , last_receipt_issue_type = p_receipt_issue_type
2021 , last_txn_source_id = p_txn_src_id
2022 , last_txn_source_type_id = p_txn_src_type_id
2023 , last_txn_source_name = p_txn_src_name
2024 , last_update_date = SYSDATE
2025 , last_updated_by = l_userid
2026 , parent_item_id = p_parent_item_id
2027 , parent_serial_number = p_parent_serial_number
2028 , wip_entity_id = p_wip_entity_id
2029 , operation_seq_num = p_operation_seq_num
2030 , intraoperation_step_type = p_intraoperation_step_type
2031 WHERE inventory_item_id = p_inventory_item_id
2032 AND serial_number = p_serial_number
2033 AND DECODE(current_status, 6, 1, current_status) = DECODE(p_last_status, 6, 1, p_last_status);
2034 END IF;
2035
2036 x_return_status := fnd_api.g_ret_sts_success;
2037
2038 -- End of API body.
2039 -- Standard check of p_commit.
2040 IF fnd_api.to_boolean(p_commit) THEN
2041 COMMIT WORK;
2042 END IF;
2043
2044 -- Standard call to get message count and if count is 1, get message info.
2045 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
2046 EXCEPTION
2047 WHEN OTHERS THEN
2048 ROLLBACK TO apiupdateserial_apipub;
2049
2050 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2051 fnd_msg_pub.add_exc_msg('INV_SERIAL_NUMBER_PUB', 'updateSerial');
2052 END IF;
2053
2054 x_return_status := fnd_api.g_ret_sts_unexp_error;
2055 END updateserial;
2056
2057 PROCEDURE insertunittrx(
2058 p_api_version IN NUMBER
2059 , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
2060 , p_commit IN VARCHAR2 := fnd_api.g_false
2061 , p_validation_level IN NUMBER := fnd_api.g_valid_level_full
2062 , p_inventory_item_id IN NUMBER
2063 , p_organization_id IN NUMBER
2064 , p_serial_number IN VARCHAR2
2065 , p_current_locator_id IN NUMBER
2066 , p_subinventory_code IN VARCHAR2
2067 , p_transaction_date IN DATE
2068 , p_txn_src_id IN NUMBER
2069 , p_txn_src_name IN VARCHAR2
2070 , p_txn_src_type_id IN NUMBER
2071 , p_transaction_id IN NUMBER
2072 , p_transaction_action_id IN NUMBER
2073 , p_transaction_temp_id IN NUMBER
2074 , p_receipt_issue_type IN NUMBER
2075 , p_customer_id IN NUMBER
2076 , p_ship_id IN NUMBER
2077 , p_status_id IN NUMBER
2078 , x_return_status OUT NOCOPY VARCHAR2
2079 , x_msg_count OUT NOCOPY NUMBER
2080 , x_msg_data OUT NOCOPY VARCHAR2
2081 ) IS
2082 l_api_version CONSTANT NUMBER := 1.0;
2083 l_api_name CONSTANT VARCHAR2(30) := 'insertSerial';
2084 l_userid NUMBER;
2085 l_loginid NUMBER;
2086 l_serial_control_code NUMBER;
2087 l_attributes_default inv_lot_sel_attr.lot_sel_attributes_tbl_type;
2088 l_attributes_default_count NUMBER;
2089 l_attributes_in inv_lot_sel_attr.lot_sel_attributes_tbl_type;
2090 l_column_idx BINARY_INTEGER := 44;
2091 l_return_status VARCHAR2(1);
2092 l_msg_data VARCHAR2(2000);
2093 l_msg_count NUMBER;
2094 l_wms_installed BOOLEAN;
2095
2096 l_sys_date date := NULL;
2097 l_date2 date := NULL;
2098 l_date23 date := NULL;
2099 l_date24 date := NULL;
2100 l_date25 date := NULL;
2101 l_date26 date := NULL;
2102 l_date27 date := NULL;
2103 l_date28 date := NULL;
2104 l_date29 date := NULL;
2105 l_date30 date := NULL;
2106 l_date31 date := NULL;
2107 l_date32 date := NULL;
2108
2109 l_num33 NUMBER := NULL;
2110 l_num34 NUMBER := NULL;
2111 l_num35 NUMBER := NULL;
2112 l_num36 NUMBER := NULL;
2113 l_num37 NUMBER := NULL;
2114 l_num38 NUMBER := NULL;
2115 l_num39 NUMBER := NULL;
2116 l_num40 NUMBER := NULL;
2117 l_num41 NUMBER := NULL;
2118 l_num42 NUMBER := NULL;
2119
2120 CURSOR serial_temp_csr(p_transaction_temp_id NUMBER) IS
2121 SELECT serial_attribute_category
2122 , fnd_date.date_to_canonical(origination_date)
2123 , c_attribute1
2124 , c_attribute2
2125 , c_attribute3
2126 , c_attribute4
2127 , c_attribute5
2128 , c_attribute6
2129 , c_attribute7
2130 , c_attribute8
2131 , c_attribute9
2132 , c_attribute10
2133 , c_attribute11
2134 , c_attribute12
2135 , c_attribute13
2136 , c_attribute14
2137 , c_attribute15
2138 , c_attribute16
2139 , c_attribute17
2140 , c_attribute18
2141 , c_attribute19
2142 , c_attribute20
2143 , fnd_date.date_to_canonical(d_attribute1)
2144 , fnd_date.date_to_canonical(d_attribute2)
2145 , fnd_date.date_to_canonical(d_attribute3)
2146 , fnd_date.date_to_canonical(d_attribute4)
2147 , fnd_date.date_to_canonical(d_attribute5)
2148 , fnd_date.date_to_canonical(d_attribute6)
2149 , fnd_date.date_to_canonical(d_attribute7)
2150 , fnd_date.date_to_canonical(d_attribute8)
2151 , fnd_date.date_to_canonical(d_attribute9)
2152 , fnd_date.date_to_canonical(d_attribute10)
2153 , TO_CHAR(n_attribute1)
2154 , TO_CHAR(n_attribute2)
2155 , TO_CHAR(n_attribute3)
2156 , TO_CHAR(n_attribute4)
2157 , TO_CHAR(n_attribute5)
2158 , TO_CHAR(n_attribute6)
2159 , TO_CHAR(n_attribute7)
2160 , TO_CHAR(n_attribute8)
2161 , TO_CHAR(n_attribute9)
2162 , TO_CHAR(n_attribute10)
2163 , status_id
2164 , territory_code
2165 FROM mtl_serial_numbers_temp
2166 WHERE transaction_temp_id = p_transaction_temp_id
2167 AND LPAD(p_serial_number,30) BETWEEN LPAD(fm_serial_number,30) AND LPAD(NVL(to_serial_number, fm_serial_number),30);
2168 /* Bug 3622025 -- Added the LPAD function in the above where clause */
2169
2170 l_input_idx BINARY_INTEGER;
2171 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
2172 BEGIN
2173 -- Standard Start of API savepoint
2174 SAVEPOINT apiinsertserial_apipub;
2175
2176 -- Standard call to check for call compatibility.
2177 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
2178 RAISE fnd_api.g_exc_unexpected_error;
2179 END IF;
2180
2181 -- Initialize message list if p_init_msg_list is set to TRUE.
2182 IF fnd_api.to_boolean(p_init_msg_list) THEN
2183 fnd_msg_pub.initialize;
2184 END IF;
2185
2186 -- Initialize API return status to success
2187
2188 /**IF (p_transaction_action_id = 3 AND g_firstscan = FALSE) THEN
2189 x_return_status := fnd_api.g_ret_sts_success;
2190 ELSE**/
2191 x_return_status := fnd_api.g_ret_sts_success;
2192 l_wms_installed :=
2193 wms_install.check_install(
2194 x_return_status => l_return_status
2195 , x_msg_count => l_msg_count
2196 , x_msg_data => l_msg_data
2197 , p_organization_id => NULL --p_organization_id
2198 );
2199
2200 IF (p_transaction_temp_id IS NOT NULL) THEN
2201 OPEN serial_temp_csr(p_transaction_temp_id);
2202
2203 populateattributescolumn();
2204
2205 FETCH serial_temp_csr
2206 INTO g_serial_attributes_tbl(1).column_value
2207 , g_serial_attributes_tbl(2).column_value
2208 , g_serial_attributes_tbl(3).column_value
2209 , g_serial_attributes_tbl(4).column_value
2210 , g_serial_attributes_tbl(5).column_value
2211 , g_serial_attributes_tbl(6).column_value
2212 , g_serial_attributes_tbl(7).column_value
2213 , g_serial_attributes_tbl(8).column_value
2214 , g_serial_attributes_tbl(9).column_value
2215 , g_serial_attributes_tbl(10).column_value
2216 , g_serial_attributes_tbl(11).column_value
2217 , g_serial_attributes_tbl(12).column_value
2218 , g_serial_attributes_tbl(13).column_value
2219 , g_serial_attributes_tbl(14).column_value
2220 , g_serial_attributes_tbl(15).column_value
2221 , g_serial_attributes_tbl(16).column_value
2222 , g_serial_attributes_tbl(17).column_value
2223 , g_serial_attributes_tbl(18).column_value
2224 , g_serial_attributes_tbl(19).column_value
2225 , g_serial_attributes_tbl(20).column_value
2226 , g_serial_attributes_tbl(21).column_value
2227 , g_serial_attributes_tbl(22).column_value
2228 , g_serial_attributes_tbl(23).column_value
2229 , g_serial_attributes_tbl(24).column_value
2230 , g_serial_attributes_tbl(25).column_value
2231 , g_serial_attributes_tbl(26).column_value
2232 , g_serial_attributes_tbl(27).column_value
2233 , g_serial_attributes_tbl(28).column_value
2234 , g_serial_attributes_tbl(29).column_value
2235 , g_serial_attributes_tbl(30).column_value
2236 , g_serial_attributes_tbl(31).column_value
2237 , g_serial_attributes_tbl(32).column_value
2238 , g_serial_attributes_tbl(33).column_value
2239 , g_serial_attributes_tbl(34).column_value
2240 , g_serial_attributes_tbl(35).column_value
2241 , g_serial_attributes_tbl(36).column_value
2242 , g_serial_attributes_tbl(37).column_value
2243 , g_serial_attributes_tbl(38).column_value
2244 , g_serial_attributes_tbl(39).column_value
2245 , g_serial_attributes_tbl(40).column_value
2246 , g_serial_attributes_tbl(41).column_value
2247 , g_serial_attributes_tbl(42).column_value
2248 , g_serial_attributes_tbl(43).column_value
2249 , g_serial_attributes_tbl(44).column_value;
2250
2251 CLOSE serial_temp_csr;
2252
2253 l_input_idx := 0;
2254
2255 IF l_wms_installed THEN
2256 FOR x IN 1 .. 44 LOOP
2257 IF (g_serial_attributes_tbl(x).column_value IS NOT NULL) THEN
2258 l_input_idx := l_input_idx + 1;
2259 l_attributes_in(l_input_idx).column_name := g_serial_attributes_tbl(x).column_name;
2260 l_attributes_in(l_input_idx).column_type := g_serial_attributes_tbl(x).column_type;
2261 l_attributes_in(l_input_idx).column_value := g_serial_attributes_tbl(x).column_value;
2262 END IF;
2263 END LOOP;
2264 END IF; -- if wms_installed is true
2265 END IF; -- if transaction_temp_id is not null
2266
2267 ----------------------------------------------------------
2268 -- call inv_lot_sel_attr.get_default to get the default value
2269 -- of the lot attributes
2270 ---------------------------------------------------------
2271 IF l_wms_installed THEN
2272 inv_lot_sel_attr.get_default(
2273 x_attributes_default => l_attributes_default
2274 , x_attributes_default_count => l_attributes_default_count
2275 , x_return_status => l_return_status
2276 , x_msg_count => l_msg_count
2277 , x_msg_data => l_msg_data
2278 , p_table_name => 'MTL_SERIAL_NUMBERS'
2279 , p_attributes_name => 'Serial Attributes'
2280 , p_inventory_item_id => p_inventory_item_id
2281 , p_organization_id => p_organization_id
2282 , p_lot_serial_number => p_serial_number
2283 , p_attributes => l_attributes_in
2284 );
2285
2286 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
2287 x_return_status := l_return_status;
2288 RAISE fnd_api.g_exc_unexpected_error;
2289 END IF;
2290
2291 /* Get the default attribs only when there is no value on the form (in MSNT).
2292 * In case the user changes the context and the attributes while recieving,
2293 * they would be lost if we get the default context again--2756040
2294 */
2295 IF (l_attributes_default_count > 0
2296 AND(g_serial_attributes_tbl(1).column_value = NULL)) THEN
2297 FOR i IN 1 .. l_attributes_default_count LOOP
2298 FOR j IN 1 .. g_serial_attributes_tbl.COUNT LOOP
2299 IF (l_attributes_default(i).column_name = g_serial_attributes_tbl(j).column_name) THEN
2300 g_serial_attributes_tbl(j).column_value := l_attributes_default(i).column_value;
2301 END IF;
2302 END LOOP;
2303 END LOOP;
2304 END IF;
2305 END IF; -- if wms install is true
2306
2307 l_userid := fnd_global.user_id;
2308 l_loginid := fnd_global.login_id;
2309 l_sys_date := SYSDATE;
2310
2311 IF (p_transaction_action_id = 3 AND g_firstscan = FALSE) THEN
2312 INSERT INTO mtl_unit_transactions
2313 (
2314 transaction_id
2315 , last_update_date
2316 , last_updated_by
2317 , creation_date
2318 , created_by
2319 , last_update_login
2320 , serial_number
2321 , inventory_item_id
2322 , organization_id
2323 , subinventory_code
2324 , locator_id
2325 , transaction_date
2326 , transaction_source_id
2327 , transaction_source_type_id
2328 , transaction_source_name
2329 , receipt_issue_type
2330 , customer_id
2331 , ship_id
2332 , serial_attribute_category
2333 , origination_date
2334 , c_attribute1
2335 , c_attribute2
2336 , c_attribute3
2337 , c_attribute4
2338 , c_attribute5
2339 , c_attribute6
2340 , c_attribute7
2341 , c_attribute8
2342 , c_attribute9
2343 , c_attribute10
2344 , c_attribute11
2345 , c_attribute12
2346 , c_attribute13
2347 , c_attribute14
2348 , c_attribute15
2349 , c_attribute16
2350 , c_attribute17
2351 , c_attribute18
2352 , c_attribute19
2353 , c_attribute20
2354 , d_attribute1
2355 , d_attribute2
2356 , d_attribute3
2357 , d_attribute4
2358 , d_attribute5
2359 , d_attribute6
2360 , d_attribute7
2361 , d_attribute8
2362 , d_attribute9
2363 , d_attribute10
2364 , n_attribute1
2365 , n_attribute2
2366 , n_attribute3
2367 , n_attribute4
2368 , n_attribute5
2369 , n_attribute6
2370 , n_attribute7
2371 , n_attribute8
2372 , n_attribute9
2373 , n_attribute10
2374 , status_id
2375 , territory_code
2376 , time_since_new
2377 , cycles_since_new
2378 , time_since_overhaul
2379 , cycles_since_overhaul
2380 , time_since_repair
2381 , cycles_since_repair
2382 , time_since_visit
2383 , cycles_since_visit
2384 , time_since_mark
2385 , cycles_since_mark
2386 , number_of_repairs
2387 )
2388 SELECT p_transaction_id
2389 , l_sys_date
2390 , l_userid
2391 , creation_date
2392 , created_by
2393 , l_loginid
2394 , p_serial_number
2395 , p_inventory_item_id
2396 , p_organization_id
2397 , p_subinventory_code
2398 , p_current_locator_id
2399 , p_transaction_date
2400 , p_txn_src_id
2401 , p_txn_src_type_id
2402 , p_txn_src_name
2403 , p_receipt_issue_type
2404 , p_customer_id
2405 , p_ship_id
2406 , serial_attribute_category
2407 , origination_date
2408 , c_attribute1
2409 , c_attribute2
2410 , c_attribute3
2411 , c_attribute4
2412 , c_attribute5
2413 , c_attribute6
2414 , c_attribute7
2415 , c_attribute8
2416 , c_attribute9
2417 , c_attribute10
2418 , c_attribute11
2419 , c_attribute12
2420 , c_attribute13
2421 , c_attribute14
2422 , c_attribute15
2423 , c_attribute16
2424 , c_attribute17
2425 , c_attribute18
2426 , c_attribute19
2427 , c_attribute20
2428 , d_attribute1
2429 , d_attribute2
2430 , d_attribute3
2431 , d_attribute4
2432 , d_attribute5
2433 , d_attribute6
2434 , d_attribute7
2435 , d_attribute8
2436 , d_attribute9
2437 , d_attribute10
2438 , n_attribute1
2439 , n_attribute2
2440 , n_attribute3
2441 , n_attribute4
2442 , n_attribute5
2443 , n_attribute6
2444 , n_attribute7
2445 , n_attribute8
2446 , n_attribute9
2447 , n_attribute10
2448 , status_id
2449 , territory_code
2450 , time_since_new
2451 , cycles_since_new
2452 , time_since_overhaul
2453 , cycles_since_overhaul
2454 , time_since_repair
2455 , cycles_since_repair
2456 , time_since_visit
2457 , cycles_since_visit
2458 , time_since_mark
2459 , cycles_since_mark
2460 , number_of_repairs
2461 FROM mtl_serial_numbers
2462 WHERE serial_number = p_serial_number
2463 AND current_organization_id = p_organization_id
2464 AND inventory_item_id = p_inventory_item_id;
2465 /* Bug 2207912 */
2466 /* The following not exists statement is commented out
2467 ** because this part of the statement gets executed
2468 ** only for ORG TRANSFER and for the delivery side of
2469 ** of transaction as firstscan is false now, before this
2470 ** insert statement gets executed the mtl_serial_number is
2471 ** table is already updated with the organization_id of the
2472 ** delivered org and status from the TM, so there will be an entry always exist
2473 ** ing for the where condition specified in the exists clasue
2474 ** for mtl_serial_number table
2475 ** So the insert statement will always fail.
2476 */
2477 --and not exists
2478 -- ( select NULL
2479 -- from mtl_serial_numbers sn
2480 -- where sn.serial_number = p_serial_number
2481 -- and sn.current_organization_id = p_organization_id
2482 -- and sn.inventory_item_id = p_inventory_item_id);
2483 ELSE
2484 IF l_wms_installed THEN
2485 /** 2756040 - Populate Serial Attribute Category info when it is not
2486 ** a receiving transaction **/
2487 IF ((g_serial_attributes_tbl(1).column_value = NULL)
2488 OR(p_transaction_action_id NOT IN(12, 27, 31))) THEN
2489 inv_lot_sel_attr.get_context_code(g_serial_attributes_tbl(1).column_value, p_organization_id, p_inventory_item_id
2490 , 'Serial Attributes');
2491 END IF;
2492 ELSE
2493 g_serial_attributes_tbl(1).column_value := NULL;
2494 END IF;
2495
2496 l_date2 := fnd_date.canonical_to_date(g_serial_attributes_tbl(2).COLUMN_VALUE);
2497 l_date23 := fnd_date.canonical_to_date(g_serial_attributes_tbl(23).COLUMN_VALUE);
2498 l_date24 := fnd_date.canonical_to_date(g_serial_attributes_tbl(24).COLUMN_VALUE);
2499 l_date25 := fnd_date.canonical_to_date(g_serial_attributes_tbl(25).COLUMN_VALUE);
2500 l_date26 := fnd_date.canonical_to_date(g_serial_attributes_tbl(26).COLUMN_VALUE);
2501 l_date27 := fnd_date.canonical_to_date(g_serial_attributes_tbl(27).COLUMN_VALUE);
2502 l_date28 := fnd_date.canonical_to_date(g_serial_attributes_tbl(28).COLUMN_VALUE);
2503 l_date29 := fnd_date.canonical_to_date(g_serial_attributes_tbl(29).COLUMN_VALUE);
2504 l_date30 := fnd_date.canonical_to_date(g_serial_attributes_tbl(30).COLUMN_VALUE);
2505 l_date31 := fnd_date.canonical_to_date(g_serial_attributes_tbl(31).COLUMN_VALUE);
2506 l_date32 := fnd_date.canonical_to_date(g_serial_attributes_tbl(32).COLUMN_VALUE);
2507 l_num33 := to_number(g_serial_attributes_tbl(33).COLUMN_VALUE);
2508 l_num34 := to_number(g_serial_attributes_tbl(34).COLUMN_VALUE);
2509 l_num35 := to_number(g_serial_attributes_tbl(35).COLUMN_VALUE);
2510 l_num36 := to_number(g_serial_attributes_tbl(36).COLUMN_VALUE);
2511 l_num37 := to_number(g_serial_attributes_tbl(37).COLUMN_VALUE);
2512 l_num38 := to_number(g_serial_attributes_tbl(38).COLUMN_VALUE);
2513 l_num39 := to_number(g_serial_attributes_tbl(39).COLUMN_VALUE);
2514 l_num40 := to_number(g_serial_attributes_tbl(40).COLUMN_VALUE);
2515 l_num41 := to_number(g_serial_attributes_tbl(41).COLUMN_VALUE);
2516 l_num42 := to_number(g_serial_attributes_tbl(42).COLUMN_VALUE);
2517
2518 IF (p_transaction_temp_id > 0) THEN
2519 --Bug 2067223 paranthesis are added in the where clause
2520 -- of the select statement
2521 INSERT INTO mtl_unit_transactions
2522 (
2523 transaction_id
2524 , last_update_date
2525 , last_updated_by
2526 , creation_date
2527 , created_by
2528 , last_update_login
2529 , serial_number
2530 , inventory_item_id
2531 , organization_id
2532 , subinventory_code
2533 , locator_id
2534 , transaction_date
2535 , transaction_source_id
2536 , transaction_source_type_id
2537 , transaction_source_name
2538 , receipt_issue_type
2539 , customer_id
2540 , ship_id
2541 , serial_attribute_category
2542 , origination_date
2543 , c_attribute1
2544 , c_attribute2
2545 , c_attribute3
2546 , c_attribute4
2547 , c_attribute5
2548 , c_attribute6
2549 , c_attribute7
2550 , c_attribute8
2551 , c_attribute9
2552 , c_attribute10
2553 , c_attribute11
2554 , c_attribute12
2555 , c_attribute13
2556 , c_attribute14
2557 , c_attribute15
2558 , c_attribute16
2559 , c_attribute17
2560 , c_attribute18
2561 , c_attribute19
2562 , c_attribute20
2563 , d_attribute1
2564 , d_attribute2
2565 , d_attribute3
2566 , d_attribute4
2567 , d_attribute5
2568 , d_attribute6
2569 , d_attribute7
2570 , d_attribute8
2571 , d_attribute9
2572 , d_attribute10
2573 , n_attribute1
2574 , n_attribute2
2575 , n_attribute3
2576 , n_attribute4
2577 , n_attribute5
2578 , n_attribute6
2579 , n_attribute7
2580 , n_attribute8
2581 , n_attribute9
2582 , n_attribute10
2583 , status_id
2584 , territory_code
2585 , time_since_new
2586 , cycles_since_new
2587 , time_since_overhaul
2588 , cycles_since_overhaul
2589 , time_since_repair
2590 , cycles_since_repair
2591 , time_since_visit
2592 , cycles_since_visit
2593 , time_since_mark
2594 , cycles_since_mark
2595 , number_of_repairs
2596 , product_code
2597 , product_transaction_id
2598 , attribute_category
2599 , attribute1
2600 , attribute2
2601 , attribute3
2602 , attribute4
2603 , attribute5
2604 , attribute6
2605 , attribute7
2606 , attribute8
2607 , attribute9
2608 , attribute10
2609 , attribute11
2610 , attribute12
2611 , attribute13
2612 , attribute14
2613 , attribute15
2614 )
2615 SELECT p_transaction_id
2616 , l_sys_date
2617 , l_userid
2618 , creation_date
2619 , l_userid
2620 , l_loginid
2621 , p_serial_number
2622 , p_inventory_item_id
2623 , p_organization_id
2624 , p_subinventory_code
2625 , p_current_locator_id
2626 , p_transaction_date
2627 , p_txn_src_id
2628 , p_txn_src_type_id
2629 , p_txn_src_name
2630 , p_receipt_issue_type
2631 , p_customer_id
2632 , p_ship_id
2633 , g_serial_attributes_tbl(1).column_value
2634 , l_date2
2635 , g_serial_attributes_tbl(3).column_value
2636 , g_serial_attributes_tbl(4).column_value
2637 , g_serial_attributes_tbl(5).column_value
2638 , g_serial_attributes_tbl(6).column_value
2639 , g_serial_attributes_tbl(7).column_value
2640 , g_serial_attributes_tbl(8).column_value
2641 , g_serial_attributes_tbl(9).column_value
2642 , g_serial_attributes_tbl(10).column_value
2643 , g_serial_attributes_tbl(11).column_value
2644 , g_serial_attributes_tbl(12).column_value
2645 , g_serial_attributes_tbl(13).column_value
2646 , g_serial_attributes_tbl(14).column_value
2647 , g_serial_attributes_tbl(15).column_value
2648 , g_serial_attributes_tbl(16).column_value
2649 , g_serial_attributes_tbl(17).column_value
2650 , g_serial_attributes_tbl(18).column_value
2651 , g_serial_attributes_tbl(19).column_value
2652 , g_serial_attributes_tbl(20).column_value
2653 , g_serial_attributes_tbl(21).column_value
2654 , g_serial_attributes_tbl(22).column_value
2655 , l_date23
2656 , l_date24
2657 , l_date25
2658 , l_date26
2659 , l_date27
2660 , l_date28
2661 , l_date29
2662 , l_date30
2663 , l_date31
2664 , l_date32
2665 , l_num33
2666 , l_num34
2667 , l_num35
2668 , l_num36
2669 , l_num37
2670 , l_num38
2671 , l_num39
2672 , l_num40
2673 , l_num41
2674 , l_num42
2675 , p_status_id
2676 , g_serial_attributes_tbl(44).column_value
2677 , time_since_new
2678 , cycles_since_new
2679 , time_since_overhaul
2680 , cycles_since_overhaul
2681 , time_since_repair
2682 , cycles_since_repair
2683 , time_since_visit
2684 , cycles_since_visit
2685 , time_since_mark
2686 , cycles_since_mark
2687 , number_of_repairs
2688 , product_code
2689 , product_transaction_id
2690 , attribute_category
2691 , attribute1
2692 , attribute2
2693 , attribute3
2694 , attribute4
2695 , attribute5
2696 , attribute6
2697 , attribute7
2698 , attribute8
2699 , attribute9
2700 , attribute10
2701 , attribute11
2702 , attribute12
2703 , attribute13
2704 , attribute14
2705 , attribute15
2706 FROM mtl_serial_numbers_temp
2707 WHERE transaction_temp_id = p_transaction_temp_id
2708 AND LPAD(p_serial_number,30) BETWEEN LPAD(fm_serial_number,30) AND LPAD(NVL(to_serial_number, fm_serial_number),30);
2709 /* Bug 3622025 -- Added the LPAD function in the above where clause */
2710 ELSE
2711 INSERT INTO mtl_unit_transactions
2712 (
2713 transaction_id
2714 , last_update_date
2715 , last_updated_by
2716 , creation_date
2717 , created_by
2718 , last_update_login
2719 , serial_number
2720 , inventory_item_id
2721 , organization_id
2722 , subinventory_code
2723 , locator_id
2724 , transaction_date
2725 , transaction_source_id
2726 , transaction_source_type_id
2727 , transaction_source_name
2728 , receipt_issue_type
2729 , customer_id
2730 , ship_id
2731 , serial_attribute_category
2732 , origination_date
2733 , c_attribute1
2734 , c_attribute2
2735 , c_attribute3
2736 , c_attribute4
2737 , c_attribute5
2738 , c_attribute6
2739 , c_attribute7
2740 , c_attribute8
2741 , c_attribute9
2742 , c_attribute10
2743 , c_attribute11
2744 , c_attribute12
2745 , c_attribute13
2746 , c_attribute14
2747 , c_attribute15
2748 , c_attribute16
2749 , c_attribute17
2750 , c_attribute18
2751 , c_attribute19
2752 , c_attribute20
2753 , d_attribute1
2754 , d_attribute2
2755 , d_attribute3
2756 , d_attribute4
2757 , d_attribute5
2758 , d_attribute6
2759 , d_attribute7
2760 , d_attribute8
2761 , d_attribute9
2762 , d_attribute10
2763 , n_attribute1
2764 , n_attribute2
2765 , n_attribute3
2766 , n_attribute4
2767 , n_attribute5
2768 , n_attribute6
2769 , n_attribute7
2770 , n_attribute8
2771 , n_attribute9
2772 , n_attribute10
2773 , status_id
2774 , territory_code
2775 , time_since_new
2776 , cycles_since_new
2777 , time_since_overhaul
2778 , cycles_since_overhaul
2779 , time_since_repair
2780 , cycles_since_repair
2781 , time_since_visit
2782 , cycles_since_visit
2783 , time_since_mark
2784 , cycles_since_mark
2785 , number_of_repairs
2786 )
2787 SELECT p_transaction_id
2788 , SYSDATE
2789 , l_userid
2790 , SYSDATE
2791 , l_userid
2792 , l_loginid
2793 , p_serial_number
2794 , p_inventory_item_id
2795 , p_organization_id
2796 , p_subinventory_code
2797 , p_current_locator_id
2798 , p_transaction_date
2799 , p_txn_src_id
2800 , p_txn_src_type_id
2801 , p_txn_src_name
2802 , p_receipt_issue_type
2803 , p_customer_id
2804 , p_ship_id
2805 , g_serial_attributes_tbl(1).column_value
2806 , l_date2
2807 , g_serial_attributes_tbl(3).column_value
2808 , g_serial_attributes_tbl(4).column_value
2809 , g_serial_attributes_tbl(5).column_value
2810 , g_serial_attributes_tbl(6).column_value
2811 , g_serial_attributes_tbl(7).column_value
2812 , g_serial_attributes_tbl(8).column_value
2813 , g_serial_attributes_tbl(9).column_value
2814 , g_serial_attributes_tbl(10).column_value
2815 , g_serial_attributes_tbl(11).column_value
2816 , g_serial_attributes_tbl(12).column_value
2817 , g_serial_attributes_tbl(13).column_value
2818 , g_serial_attributes_tbl(14).column_value
2819 , g_serial_attributes_tbl(15).column_value
2820 , g_serial_attributes_tbl(16).column_value
2821 , g_serial_attributes_tbl(17).column_value
2822 , g_serial_attributes_tbl(18).column_value
2823 , g_serial_attributes_tbl(19).column_value
2824 , g_serial_attributes_tbl(20).column_value
2825 , g_serial_attributes_tbl(21).column_value
2826 , g_serial_attributes_tbl(22).column_value
2827 , l_date23
2828 , l_date24
2829 , l_date25
2830 , l_date26
2831 , l_date27
2832 , l_date28
2833 , l_date29
2834 , l_date30
2835 , l_date31
2836 , l_date32
2837 , l_num33
2838 , l_num34
2839 , l_num35
2840 , l_num36
2841 , l_num37
2842 , l_num38
2843 , l_num39
2844 , l_num40
2845 , l_num41
2846 , l_num42
2847 , p_status_id
2848 , g_serial_attributes_tbl(44).column_value
2849 , msn.time_since_new
2850 , msn.cycles_since_new
2851 , msn.time_since_overhaul
2852 , msn.cycles_since_overhaul
2853 , msn.time_since_repair
2854 , msn.cycles_since_repair
2855 , msn.time_since_visit
2856 , msn.cycles_since_visit
2857 , msn.time_since_mark
2858 , msn.cycles_since_mark
2859 , msn.number_of_repairs
2860 FROM mtl_serial_numbers msn
2861 WHERE inventory_item_id = p_inventory_item_id
2862 AND serial_number = p_serial_number;
2863 END IF;
2864 END IF;
2865
2866 /*bug 2756040 Update MSN also with values from MSNT in case of
2867 receipt transaction or intransit receipt txn
2868 (transaction_action_id = 12 or 27) */
2869 IF (p_transaction_action_id IN(12, 27, 31)) THEN
2870 IF (l_debug = 1) THEN
2871 invtrace('transaction_action_id = ' || p_transaction_action_id
2872 || ' org _id ' || p_organization_id || 'item ' ||
2873 p_inventory_item_id);
2874 END IF;
2875
2876 BEGIN
2877 UPDATE mtl_serial_numbers
2878 SET serial_attribute_category = g_serial_attributes_tbl(1).column_value
2879 , origination_date = l_date2
2880 , c_attribute1 = g_serial_attributes_tbl(3).column_value
2881 , c_attribute2 = g_serial_attributes_tbl(4).column_value
2882 , c_attribute3 = g_serial_attributes_tbl(5).column_value
2883 , c_attribute4 = g_serial_attributes_tbl(6).column_value
2884 , c_attribute5 = g_serial_attributes_tbl(7).column_value
2885 , c_attribute6 = g_serial_attributes_tbl(8).column_value
2886 , c_attribute7 = g_serial_attributes_tbl(9).column_value
2887 , c_attribute8 = g_serial_attributes_tbl(10).column_value
2888 , c_attribute9 = g_serial_attributes_tbl(11).column_value
2889 , c_attribute10 = g_serial_attributes_tbl(12).column_value
2890 , c_attribute11 = g_serial_attributes_tbl(13).column_value
2891 , c_attribute12 = g_serial_attributes_tbl(14).column_value
2892 , c_attribute13 = g_serial_attributes_tbl(15).column_value
2893 , c_attribute14 = g_serial_attributes_tbl(16).column_value
2894 , c_attribute15 = g_serial_attributes_tbl(17).column_value
2895 , c_attribute16 = g_serial_attributes_tbl(18).column_value
2896 , c_attribute17 = g_serial_attributes_tbl(19).column_value
2897 , c_attribute18 = g_serial_attributes_tbl(20).column_value
2898 , c_attribute19 = g_serial_attributes_tbl(21).column_value
2899 , c_attribute20 = g_serial_attributes_tbl(22).column_value
2900 , d_attribute1 = l_date23
2901 , d_attribute2 = l_date24
2902 , d_attribute3 = l_date25
2903 , d_attribute4 = l_date26
2904 , d_attribute5 = l_date27
2905 , d_attribute6 = l_date28
2906 , d_attribute7 = l_date29
2907 , d_attribute8 = l_date30
2908 , d_attribute9 = l_date31
2909 , d_attribute10 = l_date32
2910 , n_attribute1 = l_num33
2911 , n_attribute2 = l_num34
2912 , n_attribute3 = l_num35
2913 , n_attribute4 = l_num36
2914 , n_attribute5 = l_num37
2915 , n_attribute6 = l_num38
2916 , n_attribute7 = l_num39
2917 , n_attribute8 = l_num40
2918 , n_attribute9 = l_num41
2919 , n_attribute10 = l_num42
2920 WHERE serial_number = p_serial_number
2921 AND inventory_item_id = p_inventory_item_id
2922 AND current_organization_id = p_organization_id;
2923
2924 IF (l_debug = 1) THEN
2925 invtrace('updating MSN with values ');
2926 invtrace('serial_attribute_category ' || g_serial_attributes_tbl(1).column_value);
2927 invtrace('origination_date ' || g_serial_attributes_tbl(2).column_value);
2928 invtrace(' C_ATTRIBUTE1 = ' || g_serial_attributes_tbl(3).column_value);
2929 invtrace('C_ATTRIBUTE2 = ' || g_serial_attributes_tbl(4).column_value);
2930 invtrace('C_ATTRIBUTE3 = ' || g_serial_attributes_tbl(5).column_value);
2931 invtrace('C_ATTRIBUTE4 = ' || g_serial_attributes_tbl(6).column_value);
2932 invtrace('C_ATTRIBUTE5 = ' || g_serial_attributes_tbl(7).column_value);
2933 invtrace('C_ATTRIBUTE6 = ' || g_serial_attributes_tbl(8).column_value);
2934 invtrace('C_ATTRIBUTE7 = ' || g_serial_attributes_tbl(9).column_value);
2935 invtrace('C_ATTRIBUTE8 = ' || g_serial_attributes_tbl(10).column_value);
2936 invtrace('C_ATTRIBUTE9 = ' || g_serial_attributes_tbl(11).column_value);
2937 invtrace('C_ATTRIBUTE10 = ' || g_serial_attributes_tbl(12).column_value);
2938 invtrace('C_ATTRIBUTE11 = ' || g_serial_attributes_tbl(13).column_value);
2939 invtrace('C_ATTRIBUTE12 = ' || g_serial_attributes_tbl(14).column_value);
2940 invtrace('C_ATTRIBUTE13 = ' || g_serial_attributes_tbl(15).column_value);
2941 invtrace('C_ATTRIBUTE14 = ' || g_serial_attributes_tbl(16).column_value);
2942 invtrace('C_ATTRIBUTE15 = ' || g_serial_attributes_tbl(17).column_value);
2943 invtrace('C_ATTRIBUTE16 = ' || g_serial_attributes_tbl(18).column_value);
2944 invtrace('C_ATTRIBUTE17 = ' || g_serial_attributes_tbl(19).column_value);
2945 invtrace('C_ATTRIBUTE18 = ' || g_serial_attributes_tbl(20).column_value);
2946 invtrace('C_ATTRIBUTE19 = ' || g_serial_attributes_tbl(21).column_value);
2947 invtrace('C_ATTRIBUTE20 = ' || g_serial_attributes_tbl(22).column_value);
2948 invtrace('D_ATTRIBUTE1 = ' || g_serial_attributes_tbl(23).column_value);
2949 invtrace('D_ATTRIBUTE2 = ' || g_serial_attributes_tbl(24).column_value);
2950 invtrace('D_ATTRIBUTE3 = ' || g_serial_attributes_tbl(25).column_value);
2951 invtrace('D_ATTRIBUTE4 = ' || g_serial_attributes_tbl(26).column_value);
2952 invtrace('D_ATTRIBUTE5 = ' || g_serial_attributes_tbl(27).column_value);
2953 invtrace('D_ATTRIBUTE6 = ' || g_serial_attributes_tbl(28).column_value);
2954 invtrace('D_ATTRIBUTE7 = ' || g_serial_attributes_tbl(29).column_value);
2955 invtrace('D_ATTRIBUTE8 = ' || g_serial_attributes_tbl(30).column_value);
2956 invtrace('D_ATTRIBUTE9 = ' || g_serial_attributes_tbl(31).column_value);
2957 invtrace('D_ATTRIBUTE10 = ' || g_serial_attributes_tbl(32).column_value);
2958 invtrace('N_ATTRIBUTE1 = ' || g_serial_attributes_tbl(33).column_value);
2959 invtrace('N_ATTRIBUTE2 = ' || g_serial_attributes_tbl(34).column_value);
2960 invtrace('N_ATTRIBUTE3 = ' || g_serial_attributes_tbl(35).column_value);
2961 invtrace('N_ATTRIBUTE4 = ' || g_serial_attributes_tbl(36).column_value);
2962 invtrace('N_ATTRIBUTE5 = ' || g_serial_attributes_tbl(37).column_value);
2963 invtrace('N_ATTRIBUTE6 = ' || g_serial_attributes_tbl(38).column_value);
2964 invtrace('N_ATTRIBUTE7 = ' || g_serial_attributes_tbl(39).column_value);
2965 invtrace('N_ATTRIBUTE8 = ' || g_serial_attributes_tbl(40).column_value);
2966 invtrace('N_ATTRIBUTE9 = ' || g_serial_attributes_tbl(41).column_value);
2967 invtrace('N_ATTRIBUTE10 = ' || g_serial_attributes_tbl(42).column_value);
2968 invtrace(' for the serial ' || p_serial_number);
2969 END IF;
2970 EXCEPTION
2971 WHEN NO_DATA_FOUND THEN
2972 IF (l_debug = 1) THEN
2973 invtrace('no data found while updating msn');
2974 END IF;
2975 WHEN OTHERS THEN
2976 IF (l_debug = 1) THEN
2977 invtrace('some other error' || SQLERRM);
2978 END IF;
2979 END;
2980 END IF;
2981
2982 x_return_status := fnd_api.g_ret_sts_success;
2983
2984 -- End of API body.
2985 -- Standard check of p_commit.
2986 IF fnd_api.to_boolean(p_commit) THEN
2987 COMMIT WORK;
2988 END IF;
2989
2990 -- Standard call to get message count and if count is 1, get message info.
2991 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => l_msg_data);
2992 x_msg_data := SUBSTR(l_msg_data, 0, 198);
2993 EXCEPTION
2994 WHEN OTHERS THEN
2995 ROLLBACK TO apiinsertserial_apipub;
2996
2997 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2998 fnd_msg_pub.add_exc_msg('INV_SERIAL_NUMBER_PUB', 'insertUnitTrx');
2999 END IF;
3000
3001 x_return_status := fnd_api.g_ret_sts_unexp_error;
3002 END insertunittrx;
3003
3004 ------------------------------------------------------------------------------
3005 -- Name: GENERATE_SERIALSJ
3006 --
3007 -- Input parameters:
3008 -- p_org_id Organization ID
3009 -- p_item_id Item ID
3010 -- p_qty Count of Serial Numbers
3011 -- p_wip_id Wip Entity ID
3012 -- p_rev Revision
3013 -- p_lot Lot Number
3014 -- p_skip_serial 0, do not skip, called in context of transaction
3015 -- processing
3016 -- 1, skip-serials : concurrent-program
3017 -- Output parameters:
3018 -- x_proc_msg Message from the Process-Manager
3019 -- return_status 0 on Success, 1 on Error
3020 --
3021 -- Function: Call specification for Java Stored Procedure. This function
3022 -- is not called directly but through GENERATE_SERIALS to support
3023 -- autonomous transaction
3024 --
3025 --
3026 FUNCTION generate_serialsj(
3027 p_org_id IN NUMBER
3028 , p_item_id IN NUMBER
3029 , p_qty IN NUMBER
3030 , p_wip_id IN NUMBER
3031 , p_rev IN VARCHAR2
3032 , p_lot IN VARCHAR2
3033 , p_skip_serial NUMBER
3034 , p_group_mark_id NUMBER
3035 , p_line_mark_id NUMBER
3036 , x_start_ser OUT NOCOPY VARCHAR2
3037 , x_end_ser OUT NOCOPY VARCHAR2
3038 , x_proc_msg OUT NOCOPY VARCHAR2
3039 )
3040 RETURN NUMBER AS
3041 LANGUAGE JAVA
3042 NAME 'oracle.apps.inv.transaction.server.TrxProcessor.generateSerials(java.lang.Long,
3043 java.lang.Long,
3044 java.lang.Long,
3045 java.lang.Long,
3046 java.lang.String,
3047 java.lang.String,
3048 java.lang.Long,
3049 java.lang.Long,
3050 java.lang.Long,
3051 java.lang.String[],
3052 java.lang.String[],
3053 java.lang.String[]) return java.lang.Integer';
3054
3055 --
3056 --
3057 -- Purpose: This procedure will be called from Concurrent Manager. This Procedure
3058 -- is replacement for INCTSN.opp. It generates the serial number.
3059 --
3060 -- MODIFICATION HISTORY
3061 -- Person Date Comments
3062 -- --------- ------ -------------------------------------------
3063 -- vipathak 8/31/01 Created.
3064 --
3065 -- Declare program variables as shown above
3066 PROCEDURE generate_serials(
3067 x_retcode OUT NOCOPY VARCHAR2
3068 , x_errbuf OUT NOCOPY VARCHAR2
3069 , p_org_id IN NUMBER
3070 , p_item_id IN NUMBER
3071 , p_qty IN NUMBER
3072 , p_serial_code IN VARCHAR2
3073 , p_wip_id IN NUMBER
3074 , p_rev IN NUMBER
3075 , p_lot IN NUMBER
3076 , p_group_mark_id IN NUMBER DEFAULT NULL
3077 , p_line_mark_id IN NUMBER DEFAULT NULL
3078 ) IS
3079 PRAGMA AUTONOMOUS_TRANSACTION;
3080 v_commit VARCHAR2(12) := fnd_api.g_true;
3081 v_mesg VARCHAR2(2000);
3082 l_start_ser VARCHAR2(100);
3083 l_end_ser VARCHAR2(100);
3084 v_retval NUMBER;
3085 ret BOOLEAN;
3086 l_skip_serial NUMBER := 1;
3087 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
3088 BEGIN
3089 v_retval :=
3090 generate_serialsj(
3091 p_org_id => p_org_id
3092 , p_item_id => p_item_id
3093 , p_qty => p_qty
3094 , p_wip_id => p_wip_id
3095 , p_rev => p_rev
3096 , p_lot => p_lot
3097 , p_skip_serial => l_skip_serial
3098 , p_group_mark_id => p_group_mark_id
3099 , p_line_mark_id => p_line_mark_id
3100 , x_start_ser => l_start_ser
3101 , x_end_ser => l_end_ser
3102 , x_proc_msg => v_mesg
3103 );
3104
3105 IF (v_retval = 1) THEN
3106 ret := fnd_concurrent.set_completion_status('ERROR', v_mesg);
3107 x_retcode := 2;
3108 x_errbuf := v_mesg;
3109 ELSIF(v_retval = 2) THEN
3110 ret := fnd_concurrent.set_completion_status('WARNING', v_mesg);
3111 x_retcode := 0;
3112 ELSE
3113 ret := fnd_concurrent.set_completion_status('NORMAL', v_mesg);
3114 x_retcode := 0;
3115 END IF;
3116
3117 COMMIT;
3118 EXCEPTION
3119 WHEN OTHERS THEN
3120 x_retcode := 2;
3121 x_errbuf := SUBSTR(fnd_msg_pub.get(p_encoded => fnd_api.g_false), 1, 250);
3122 ret := fnd_concurrent.set_completion_status('ERROR', v_mesg);
3123 RAISE;
3124 END generate_serials;
3125
3126 /*-------------------------------------------------------------------------------
3127 -- Name: GENERATE_SERIALS
3128 -- Wrapper for GENERATE_SERIALSJ with Autonomous Tramsaction support
3129 --
3130 -- Input parameters:
3131 -- p_org_id Organization ID
3132 -- p_item_id Item ID
3133 -- p_qty Count of Serial Numbers
3134 -- p_wip_id Wip Entity ID
3135 -- p_rev Revision
3136 -- p_lot Lot Number
3137 -- l_calling_program 0, being called from mobile UI
3138 -- Output parameters:
3139 -- x_proc_msg Message from the Process-Manager
3140 -- return_status 0 on Success, 1 on Error
3141 --
3142 --
3143 */
3144 FUNCTION generate_serials(
3145 p_org_id IN NUMBER
3146 , p_item_id IN NUMBER
3147 , p_qty IN NUMBER
3148 , p_wip_id IN NUMBER
3149 , p_rev IN VARCHAR2
3150 , p_lot IN VARCHAR2
3151 , p_group_mark_id IN NUMBER DEFAULT NULL
3152 , p_line_mark_id IN NUMBER DEFAULT NULL
3153 , x_start_ser OUT NOCOPY VARCHAR2
3154 , x_end_ser OUT NOCOPY VARCHAR2
3155 , x_proc_msg OUT NOCOPY VARCHAR2
3156 , p_skip_serial IN NUMBER DEFAULT NULL
3157 )
3158 RETURN NUMBER AS
3159 PRAGMA AUTONOMOUS_TRANSACTION;
3160 l_retval NUMBER;
3161 l_skip_serial NUMBER := NVL(p_skip_serial, 0);
3162 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
3163 BEGIN
3164 l_retval :=
3165 generate_serialsj(
3166 p_org_id => p_org_id
3167 , p_item_id => p_item_id
3168 , p_qty => p_qty
3169 , p_wip_id => p_wip_id
3170 , p_rev => p_rev
3171 , p_lot => p_lot
3172 , p_skip_serial => l_skip_serial
3173 , p_group_mark_id => p_group_mark_id
3174 , p_line_mark_id => p_line_mark_id
3175 , x_start_ser => x_start_ser
3176 , x_end_ser => x_end_ser
3177 , x_proc_msg => x_proc_msg
3178 );
3179 COMMIT;
3180 RETURN l_retval;
3181 END;
3182
3183 --
3184 -- Name: IS_SERIAL_UNIQUE
3185 --
3186 -- Input parameters:
3187 -- p_org_id Organization ID
3188 -- p_item_id Item ID
3189 -- p_serial Serial Number
3190 --
3191 -- Output parameters:
3192 -- x_proc_msg Message from the Process-Manager
3193 -- return_status 0 on Success, 1 on Error
3194 --
3195 --
3196 FUNCTION is_serial_unique(p_org_id IN NUMBER, p_item_id IN NUMBER, p_serial IN VARCHAR2, x_proc_msg OUT NOCOPY VARCHAR2)
3197 RETURN NUMBER AS
3198 LANGUAGE JAVA
3199 NAME 'oracle.apps.inv.transaction.server.TrxProcessor.isSerialNumberUnique(java.lang.Long,
3200 java.lang.Long,
3201 java.lang.String,
3202 java.lang.String[]) return java.lang.Integer';
3203
3204 --
3205 -- Name: GET_SERIAL_DIFF
3206 --
3207 -- Input parameters:
3208 -- p_fm_serial 'from' Serial Number
3209 -- p_to_serial 'to' Serial Number
3210 --
3211 -- Output parameters:
3212 -- return_status quantity between passed serial numbers
3213 --
3214 FUNCTION get_serial_diff(p_fm_serial IN VARCHAR2, p_to_serial IN VARCHAR2)
3215 RETURN NUMBER AS
3216 LANGUAGE JAVA
3217 NAME 'oracle.apps.inv.transaction.server.TrxProcessor.getSerNumDiff(java.lang.String,
3218 java.lang.String) return java.lang.Integer';
3219
3220 FUNCTION validate_serialsj(
3221 p_org_id IN NUMBER
3222 , p_item_id IN NUMBER
3223 , p_qty IN OUT NOCOPY NUMBER
3224 , p_rev IN VARCHAR2
3225 , p_lot IN VARCHAR2
3226 , p_start_ser IN VARCHAR2
3227 , p_trx_src_id IN NUMBER
3228 , p_trx_action_id IN NUMBER
3229 , p_subinventory_code IN VARCHAR2
3230 , p_locator_id IN NUMBER
3231 , p_wip_entity_id IN NUMBER
3232 , p_group_mark_id IN NUMBER
3233 , p_line_mark_id IN NUMBER
3234 , p_issue_receipt IN VARCHAR2
3235 , x_end_ser IN OUT NOCOPY VARCHAR2
3236 , x_proc_msg OUT NOCOPY VARCHAR2
3237 , p_check_for_grp_mark_id IN VARCHAR2
3238 , p_rcv_validate IN VARCHAR2
3239 , p_rcv_source_line_id IN NUMBER
3240 , p_xfr_org_id IN NUMBER -- Bug#4153297
3241 ) --Bug# 2656316
3242 RETURN NUMBER AS
3243 LANGUAGE JAVA
3244 NAME 'oracle.apps.inv.transaction.server.TrxProcessor.validateSerialNumbers(java.lang.Long,
3245 java.lang.Long,
3246 java.lang.Integer[],
3247 java.lang.String,
3248 java.lang.String,
3249 java.lang.String,
3250 java.lang.Integer,
3251 java.lang.Integer,
3252 java.lang.String,
3253 java.lang.Long,
3254 java.lang.Long,
3255 java.lang.Long,
3256 java.lang.Long,
3257 java.lang.String,
3258 java.lang.String[],
3259 java.lang.String[],
3260 java.lang.String,
3261 java.lang.String,
3262 java.lang.Long,
3263 java.lang.Long) return java.lang.Integer'; --Bug# 2656316
3264 --Bug#4153297
3265 --
3266 -- Bug 3194093 added two more parameters to the below function
3267 -- validate_serials() and to the above function validate_serialsj()
3268 -- p_rcv_validate,p_rcv_shipment_line_id to support serial
3269 -- validation for intransit receipt transactions
3270 -- applicable for Inter-org,Internal sales order Intransit txns
3271 -- Bug 3384652 Changing the param name p_rcv_shipment_line_id to
3272 -- p_rcv_source_line_id.And the value passed to this is either
3273 -- shipment_line_id or ram_line_id depending on the transaction
3274 -- Source type and action.To support serial validation for RMA
3275
3276 FUNCTION validate_serials(
3277 p_org_id IN NUMBER
3278 , p_item_id IN NUMBER
3279 , p_qty IN OUT NOCOPY NUMBER
3280 , p_rev IN VARCHAR2 DEFAULT NULL
3281 , p_lot IN VARCHAR2 DEFAULT NULL
3282 , p_start_ser IN VARCHAR2
3283 , p_trx_src_id IN NUMBER DEFAULT NULL
3284 , p_trx_action_id IN NUMBER DEFAULT NULL
3285 , p_subinventory_code IN VARCHAR2 DEFAULT NULL
3286 , p_locator_id IN NUMBER DEFAULT NULL
3287 , p_wip_entity_id IN NUMBER DEFAULT NULL
3288 , p_group_mark_id IN NUMBER DEFAULT NULL
3289 , p_line_mark_id IN NUMBER DEFAULT NULL
3290 , p_issue_receipt IN VARCHAR2 DEFAULT NULL
3291 , x_end_ser IN OUT NOCOPY VARCHAR2
3292 , x_proc_msg OUT NOCOPY VARCHAR2
3293 , p_check_for_grp_mark_id IN VARCHAR2
3294 , p_rcv_validate IN VARCHAR2 DEFAULT 'N'
3295 , p_rcv_source_line_id IN NUMBER DEFAULT -1
3296 , p_xfr_org_id IN NUMBER DEFAULT -1 -- Bug#4153297
3297 ) --Bug# 2656316
3298 RETURN NUMBER AS
3299 ret_number NUMBER := 0;
3300 local_locator_id NUMBER;
3301 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
3302 BEGIN
3303 IF (l_debug = 1) THEN
3304 inv_trx_util_pub.TRACE('About to call VALIDATE_SERIALSJ ', 'VALIDATE_SERIALS', 9);
3305 END IF;
3306
3307 ret_number :=
3308 validate_serialsj(
3309 p_org_id => p_org_id
3310 , p_item_id => p_item_id
3311 , p_qty => p_qty
3312 , p_rev => p_rev
3313 , p_lot => p_lot
3314 , p_start_ser => p_start_ser
3315 , p_trx_src_id => p_trx_src_id
3316 , p_trx_action_id => p_trx_action_id
3317 , p_subinventory_code => p_subinventory_code
3318 , p_locator_id => p_locator_id
3319 , p_wip_entity_id => p_wip_entity_id
3320 , p_group_mark_id => p_group_mark_id
3321 , p_line_mark_id => p_line_mark_id
3322 , p_issue_receipt => p_issue_receipt
3323 , x_end_ser => x_end_ser
3324 , x_proc_msg => x_proc_msg
3325 , p_check_for_grp_mark_id => p_check_for_grp_mark_id --Bug# 2656316
3326 , p_rcv_validate => p_rcv_validate
3327 , p_rcv_source_line_id => p_rcv_source_line_id
3328 , p_xfr_org_id => p_xfr_org_id -- Bug#4153297
3329 );
3330
3331 IF (l_debug = 1) THEN
3332 inv_trx_util_pub.TRACE('Returned from VALIDATE_SERIALSJ ', 'VALIDATE_SERIALS', 9);
3333 END IF;
3334
3335 RETURN ret_number;
3336 END validate_serials;
3337
3338
3339 FUNCTION increment_ser_num(p_curr_serial VARCHAR2, p_inc_value NUMBER) RETURN VARCHAR2 IS
3340 LANGUAGE JAVA NAME 'oracle.apps.inv.transaction.server.TrxProcessor.incrementSerNum(
3341 java.lang.String
3342 , java.lang.Long
3343 ) return java.lang.String' ;
3344
3345 --Procedure for validating and updating serial attributes.
3346 PROCEDURE validate_update_serial_att
3347 (x_return_status OUT NOCOPY VARCHAR2,
3348 x_msg_count OUT NOCOPY NUMBER,
3349 x_msg_data OUT NOCOPY VARCHAR2,
3350 x_validation_status OUT NOCOPY VARCHAR2,
3351 p_serial_number IN VARCHAR2,
3352 p_organization_id IN NUMBER,
3353 p_inventory_item_id IN NUMBER,
3354 p_serial_att_tbl IN inv_lot_sel_attr.lot_sel_attributes_tbl_type,
3355 p_validate_only IN BOOLEAN
3356 ) IS
3357 l_attributes_name VARCHAR2(50) := 'Serial Attributes';
3358 v_flexfield fnd_dflex.dflex_r;
3359 v_flexinfo fnd_dflex.dflex_dr;
3360 v_contexts fnd_dflex.contexts_dr;
3361 v_segments fnd_dflex.segments_dr;
3362 l_attributes_default_count NUMBER;
3363 l_enabled_attributes NUMBER;
3364 l_attributes_default INV_LOT_SEL_ATTR.Lot_Sel_Attributes_Tbl_Type;
3365 v_context_value mtl_flex_context.descriptive_flex_context_code%type;
3366 v_colName VARCHAR2(50);
3367 l_context_value VARCHAR2(150);
3368 l_return_status VARCHAR2(1);
3369 l_msg_data VARCHAR2(255);
3370 l_msg_count NUMBER;
3371 l_validation_status VARCHAR2(1);
3372 l_status BOOLEAN;
3373 l_count NUMBER := 0;
3374 l_rs_lot_attr_category VARCHAR2(30);
3375 l_st_lot_attr_category VARCHAR2(30);
3376 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3377 BEGIN
3378 IF (l_debug = 1) THEN
3379 invtrace('VALIDATE_UPDATE_SERIAL_ATT:Entered...');
3380 END IF;
3381
3382 x_return_status := FND_API.G_RET_STS_SUCCESS;
3383
3384 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
3385
3386 IF (l_debug = 1) THEN
3387 invtrace('VALIDATE_UPDATE_SERIAL_ATT:p_inventory_item_id='||p_inventory_item_id);
3388 invtrace('VALIDATE_UPDATE_SERIAL_ATT:p_organization_id='||p_organization_id);
3389 invtrace('VALIDATE_UPDATE_SERIAL_ATT:p_serial_number='||p_serial_number);
3390 END IF;
3391
3392 -- call to see if the Serial attributes is enabled for this item/org/category combination
3393 l_enabled_attributes := INV_LOT_SEL_ATTR.is_enabled(p_flex_name => l_attributes_name,
3394 p_organization_id => p_organization_id,
3395 p_inventory_item_id => p_inventory_item_id);
3396 IF (l_debug = 1) THEN
3397 invtrace('VALIDATE_UPDATE_SERIAL_ATT:l_enabled_attributes='||l_enabled_attributes);
3398 END IF;
3399
3400 --Populate serial attribute columns
3401
3402 populateattributescolumn;
3403
3404 IF (p_serial_att_tbl.COUNT <> 0 ) THEN
3405 -- derived from the start lot attributes
3406 FOR i IN 1..p_serial_att_tbl.COUNT LOOP
3407 FOR j IN 1..g_serial_attributes_tbl.COUNT LOOP
3408 IF (UPPER(g_serial_attributes_tbl(j).COLUMN_NAME) = UPPER(p_serial_att_tbl(i).COLUMN_NAME) ) THEN
3409 IF (l_debug = 1) THEN
3410 invtrace('VALIDATE_UPDATE_SERIAL_ATT:'||g_serial_attributes_tbl(j).COLUMN_NAME);
3411 END IF;
3412 g_serial_attributes_tbl(j).COLUMN_VALUE := p_serial_att_tbl(i).COLUMN_VALUE;
3413 IF (l_debug = 1) THEN
3414 invtrace('VALIDATE_UPDATE_SERIAL_ATT:'||g_serial_attributes_tbl(j).COLUMN_NAME||':'||g_serial_attributes_tbl(j).COLUMN_VALUE);
3415 END IF;
3416 END IF;
3417 EXIT WHEN (UPPER(g_serial_attributes_tbl(j).COLUMN_NAME) = UPPER(p_serial_att_tbl(i).COLUMN_NAME));
3418 END LOOP;
3419 END LOOP;
3420 END IF;
3421
3422 IF (l_debug = 1) THEN
3423 invtrace('VALIDATE_UPDATE_SERIAL_ATT:Calling inv_lot_sel_attr.get_default...');
3424 END IF;
3425
3426 inv_lot_sel_attr.get_default(x_attributes_default => l_attributes_default,
3427 x_attributes_default_count => l_attributes_default_count,
3428 x_return_status => l_return_status,
3429 x_msg_count => l_msg_count,
3430 x_msg_data => x_msg_data,
3431 p_table_name => 'MTL_SERIAL_NUMBERS',
3432 p_attributes_name => 'Serial Attributes',
3433 p_inventory_item_id => p_inventory_item_id,
3434 p_organization_id => p_organization_id,
3435 p_lot_serial_number => p_serial_number,
3436 p_attributes => g_serial_attributes_tbl);
3437
3438 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
3439 x_validation_status := 'N';
3440 x_return_status := l_return_status;
3441 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3442 END IF;
3443
3444 IF (l_debug = 1) THEN
3445 invtrace('VALIDATE_UPDATE_SERIAL_ATT:l_attributes_default_count='||l_attributes_default_count);
3446 END IF;
3447
3448 IF (l_attributes_default_count > 0) THEN
3449 FOR i IN 1..l_attributes_default_count LOOP
3450 FOR j IN 1..g_serial_attributes_tbl.COUNT LOOP
3451 IF (Upper(l_attributes_default(i).COLUMN_NAME) = Upper(g_serial_attributes_tbl(j).COLUMN_NAME)
3452 AND l_attributes_default(i).COLUMN_VALUE IS NOT NULL) THEN
3453 IF (l_debug = 1) THEN
3454 invtrace('VALIDATE_UPDATE_SERIAL_ATT:g_serial_attributes_tbl(j).COLUMN_VALUE='||g_serial_attributes_tbl(j).COLUMN_VALUE);
3455 invtrace('VALIDATE_UPDATE_SERIAL_ATT:l_attributes_default(i).COLUMN_VALUE='||l_attributes_default(i).column_value);
3456 END IF;
3457
3458 IF (g_serial_attributes_tbl(j).COLUMN_VALUE IS NULL) THEN
3459 g_serial_attributes_tbl(j).COLUMN_VALUE := l_attributes_default(i).COLUMN_VALUE;
3460 END IF;
3461
3462 g_serial_attributes_tbl(j).REQUIRED := l_attributes_default(i).REQUIRED;
3463
3464 END IF;
3465 EXIT WHEN (Upper(l_attributes_default(i).COLUMN_NAME) = Upper(g_serial_attributes_tbl(j).COLUMN_NAME));
3466 END LOOP;
3467 END LOOP;
3468 END IF;
3469
3470 IF (l_debug = 1) THEN
3471 invtrace('VALIDATE_UPDATE_SERIAL_ATT:Calling fnd_dflex.get_flexfield...');
3472 END IF;
3473 -- Get flexfield
3474 fnd_dflex.get_flexfield('INV', l_attributes_name, v_flexfield, v_flexinfo);
3475
3476 IF (l_debug = 1) THEN
3477 invtrace('VALIDATE_UPDATE_SERIAL_ATT:Calling fnd_dflex.get_context...');
3478 END IF;
3479 -- Get Contexts
3480 l_context_value := NULL;
3481 fnd_dflex.get_contexts(v_flexfield, v_contexts);
3482
3483 FOR i IN 1..g_serial_attributes_tbl.COUNT LOOP
3484 IF (Upper(g_serial_attributes_tbl(i).COLUMN_NAME) = 'SERIAL_ATTRIBUTE_CATEGORY'
3485 AND g_serial_attributes_tbl(i).column_value IS NULL ) THEN
3486 inv_lot_sel_attr.get_context_code(l_context_value, p_organization_id,p_inventory_item_id,l_attributes_name);
3487 g_serial_attributes_tbl(i).column_value := l_context_value;
3488 ELSE
3489 l_context_value := g_serial_attributes_tbl(i).column_value;
3490 END IF;
3491 EXIT WHEN (Upper(g_serial_attributes_tbl(i).COLUMN_NAME) = 'SERIAL_ATTRIBUTE_CATEGORY');
3492 END LOOP;
3493
3494 IF (l_debug = 1) THEN
3495 invtrace('VALIDATE_UPDATE_SERIAL_ATT:l_context_value='||l_context_value);
3496 END IF;
3497
3498 IF ( (l_enabled_attributes = 0 ) OR ( l_context_value is null)) then
3499
3500 IF (l_debug = 1) THEN
3501 invtrace('VALIDATE_UPDATE_SERIAL_ATT:l_context is null, attr enabaled=0');
3502 END IF;
3503
3504 l_validation_status := 'Y';
3505 x_msg_count := 0;
3506 x_msg_data := NULL;
3507 ELSE --IF ( (l_enabled_attributes = 0 ) OR ( l_context_value is null)) then
3508 IF (l_debug = 1) THEN
3509 invtrace('VALIDATE_UPDATE_SERIAL_ATT:Calling fnd_flex_descval.set_context_value...');
3510 END IF;
3511
3512 fnd_flex_descval.set_context_value(l_context_value);
3513
3514 IF (l_debug = 1) THEN
3515 invtrace('VALIDATE_UPDATE_SERIAL_ATT:Calling fnd_flex_descval.clear_column_values...');
3516 END IF;
3517
3518 fnd_flex_descval.clear_column_values;
3519
3520 IF (l_debug = 1) THEN
3521 invtrace('VALIDATE_UPDATE_SERIAL_ATT:Calling fnd_flex_descval.set_column_values SERIAL_ATTRIBUTE_CATEGORY='||l_context_value);
3522 END IF;
3523
3524 fnd_flex_descval.set_column_value('SERIAL_ATTRIBUTE_CATEGORY', l_context_value);
3525
3526 -- Setting the Values for Validating
3527 IF (l_debug = 1) THEN
3528 invtrace('VALIDATE_UPDATE_SERIAL_ATT:g_serial_attributes_tbl.COUNT='||g_serial_attributes_tbl.COUNT);
3529 END IF;
3530
3531 FOR i IN 1..v_contexts.ncontexts LOOP
3532 IF (v_contexts.is_enabled(i) AND ((UPPER(v_contexts.context_code(i)) = UPPER(l_context_value)) OR
3533 v_contexts.is_global(i))) THEN
3534 -- Get segments
3535 IF (l_debug = 1) THEN
3536 invtrace('VALIDATE_UPDATE_SERIAL_ATT:Calling fnd_dflex.get_segments...');
3537 END IF;
3538
3539 fnd_dflex.get_segments(fnd_dflex.make_context(v_flexfield, v_contexts.context_code(i)), v_segments, TRUE);
3540 <<segmentLoop>>
3541 FOR j IN 1..v_segments.nsegments LOOP
3542 IF v_segments.is_enabled(j) THEN
3543 v_colName := v_segments.application_column_name(j);
3544
3545 IF (l_debug = 1) THEN
3546 invtrace('VALIDATE_UPDATE_SERIAL_ATT:v_colName='||v_colName);
3547 END IF;
3548
3549 <<columnLoop>>
3550 FOR k IN 1..g_serial_attributes_tbl.COUNT LOOP
3551 IF UPPER(v_colName) = UPPER(g_serial_attributes_tbl(k).column_name) THEN
3552 IF (l_debug = 1) THEN
3553 invtrace('VALIDATE_UPDATE_SERIAL_ATT:'||g_serial_attributes_tbl(k).Column_name);
3554 END IF;
3555 -- Sets the Values for Validation
3556 -- Setting the column data type for validation
3557 IF g_serial_attributes_tbl(k).column_type = 'DATE' THEN
3558 IF (l_debug = 1) THEN
3559 invtrace('VALIDATE_UPDATE_SERIAL_ATT:set_column_value='||g_serial_attributes_tbl(k).column_value);
3560 END IF;
3561 fnd_flex_descval.set_column_value(g_serial_attributes_tbl(k).column_name,
3562 fnd_date.canonical_to_date(fnd_date.date_to_canonical(g_serial_attributes_tbl(k).column_value)));
3563 END IF;
3564
3565 IF g_serial_attributes_tbl(k).column_type = 'NUMBER' THEN
3566 IF (l_debug = 1) THEN
3567 invtrace('VALIDATE_UPDATE_SERIAL_ATT:set_column_value='||g_serial_attributes_tbl(k).column_value);
3568 END IF;
3569 fnd_flex_descval.set_column_value(g_serial_attributes_tbl(k).column_name,
3570 To_number(g_serial_attributes_tbl(k).column_value));
3571 END IF;
3572
3573 IF g_serial_attributes_tbl(k).column_type = 'VARCHAR2' THEN
3574 IF (l_debug = 1) THEN
3575 invtrace('VALIDATE_UPDATE_SERIAL_ATT:set_column_value='||g_serial_attributes_tbl(k).column_value);
3576 END IF;
3577 fnd_flex_descval.set_column_value(g_serial_attributes_tbl(k).column_name,
3578 g_serial_attributes_tbl(k).column_value);
3579 END IF;
3580
3581 IF (v_segments.is_required(j)) THEN
3582 IF (g_serial_attributes_tbl(k).COLUMN_VALUE IS NULL) THEN
3583 IF (l_debug = 1) THEN
3584 invtrace('VALIDATE_UPDATE_SERIAL_ATT:'||g_serial_attributes_tbl(k).COLUMN_NAME||':'||g_serial_attributes_tbl(k).COLUMN_VALUE);
3585 END IF;
3586 fnd_message.set_name('INV', 'INV_LOT_SEL_DEFAULT_REQUIRED');
3587 fnd_message.set_token('ATTRNAME',l_attributes_name);
3588 fnd_message.set_token('CONTEXTCODE', v_contexts.context_code(i));
3589 fnd_message.set_token('SEGMENT', v_segments.application_column_name(j));
3590 fnd_msg_pub.ADD;
3591 END IF;
3592 END IF;
3593 END IF;
3594 EXIT when (Upper(v_colName) = Upper(g_serial_attributes_tbl(k).column_name));
3595 END LOOP;
3596 END IF;
3597 END LOOP;
3598 END IF;
3599 END LOOP;
3600 -- Call the validating routine for Lot Attributes.
3601
3602 IF (l_debug = 1) THEN
3603 invtrace('VALIDATE_UPDATE_SERIAL_ATT:Calling fnd_flex_descval.validate_desccols...');
3604 END IF;
3605 l_status := fnd_flex_descval.validate_desccols(appl_short_name => 'INV',
3606 desc_flex_name => l_attributes_name);
3607 IF l_status = TRUE then
3608 IF (l_debug = 1) THEN
3609 invtrace('VALIDATE_UPDATE_SERIAL_ATT:l_status is TRUE');
3610 END IF;
3611 l_validation_status := 'Y';
3612 ELSE
3613 IF (l_debug = 1) THEN
3614 invtrace('VALIDATE_UPDATE_SERIAL_ATT:l_status is FALSE');
3615 END IF;
3616 l_validation_status := 'N';
3617 x_return_status := FND_API.G_RET_STS_ERROR ;
3618 x_msg_data := fnd_flex_descval.error_message;
3619 fnd_message.set_name('INV', 'GENERIC');
3620 fnd_message.set_token('MSGBODY', x_msg_data );
3621 fnd_msg_pub.ADD;
3622 x_msg_count := nvl(x_msg_count,0) + 1 ;
3623 RAISE FND_API.G_EXC_ERROR;
3624 END IF;
3625 END IF; -- if l_context_value is not null
3626
3627 x_validation_status := l_validation_status;
3628
3629 -- if validation passed then update the attributes.
3630
3631 IF (l_debug = 1) THEN
3632 invtrace('VALIDATE_UPDATE_SERIAL_ATT:Updating the Attributes...');
3633 END IF;
3634
3635 IF l_validation_status = 'Y' THEN
3636 IF NOT p_validate_only THEN
3637 UPDATE mtl_serial_numbers
3638 SET serial_attribute_category = g_serial_attributes_tbl(1).COLUMN_VALUE
3639 , origination_date = fnd_date.canonical_to_date(fnd_date.date_to_canonical(g_serial_attributes_tbl(2).COLUMN_VALUE))
3640 , c_attribute1 = g_serial_attributes_tbl(3).COLUMN_VALUE
3641 , c_attribute2 = g_serial_attributes_tbl(4).COLUMN_VALUE
3642 , c_attribute3 = g_serial_attributes_tbl(5).COLUMN_VALUE
3643 , c_attribute4 = g_serial_attributes_tbl(6).COLUMN_VALUE
3644 , c_attribute5 = g_serial_attributes_tbl(7).COLUMN_VALUE
3645 , c_attribute6 = g_serial_attributes_tbl(8).COLUMN_VALUE
3646 , c_attribute7 = g_serial_attributes_tbl(9).COLUMN_VALUE
3647 , c_attribute8 = g_serial_attributes_tbl(10).COLUMN_VALUE
3648 , c_attribute9 = g_serial_attributes_tbl(11).COLUMN_VALUE
3649 , c_attribute10 = g_serial_attributes_tbl(12).COLUMN_VALUE
3650 , c_attribute11 = g_serial_attributes_tbl(13).COLUMN_VALUE
3651 , c_attribute12 = g_serial_attributes_tbl(14).COLUMN_VALUE
3652 , c_attribute13 = g_serial_attributes_tbl(15).COLUMN_VALUE
3653 , c_attribute14 = g_serial_attributes_tbl(16).COLUMN_VALUE
3654 , c_attribute15 = g_serial_attributes_tbl(17).COLUMN_VALUE
3655 , c_attribute16 = g_serial_attributes_tbl(18).COLUMN_VALUE
3656 , c_attribute17 = g_serial_attributes_tbl(19).COLUMN_VALUE
3657 , c_attribute18 = g_serial_attributes_tbl(20).COLUMN_VALUE
3658 , c_attribute19 = g_serial_attributes_tbl(21).COLUMN_VALUE
3659 , c_attribute20 = g_serial_attributes_tbl(22).COLUMN_VALUE
3660 , d_attribute1 = fnd_date.canonical_to_date(fnd_date.date_to_canonical(g_serial_attributes_tbl(23).COLUMN_VALUE))
3661 , d_attribute2 = fnd_date.canonical_to_date(fnd_date.date_to_canonical(g_serial_attributes_tbl(24).COLUMN_VALUE))
3662 , d_attribute3 = fnd_date.canonical_to_date(fnd_date.date_to_canonical(g_serial_attributes_tbl(25).COLUMN_VALUE))
3663 , d_attribute4 = fnd_date.canonical_to_date(fnd_date.date_to_canonical(g_serial_attributes_tbl(26).COLUMN_VALUE))
3664 , d_attribute5 = fnd_date.canonical_to_date(fnd_date.date_to_canonical(g_serial_attributes_tbl(27).COLUMN_VALUE))
3665 , d_attribute6 = fnd_date.canonical_to_date(fnd_date.date_to_canonical(g_serial_attributes_tbl(28).COLUMN_VALUE))
3666 , d_attribute7 = fnd_date.canonical_to_date(fnd_date.date_to_canonical(g_serial_attributes_tbl(29).COLUMN_VALUE))
3667 , d_attribute8 = fnd_date.canonical_to_date(fnd_date.date_to_canonical(g_serial_attributes_tbl(30).COLUMN_VALUE))
3668 , d_attribute9 = fnd_date.canonical_to_date(fnd_date.date_to_canonical(g_serial_attributes_tbl(31).COLUMN_VALUE))
3669 , d_attribute10 = fnd_date.canonical_to_date(fnd_date.date_to_canonical(g_serial_attributes_tbl(32).COLUMN_VALUE))
3670 , n_attribute1 = to_number(g_serial_attributes_tbl(33).COLUMN_VALUE)
3671 , n_attribute2 = to_number(g_serial_attributes_tbl(34).COLUMN_VALUE)
3672 , n_attribute3 = to_number(g_serial_attributes_tbl(35).COLUMN_VALUE)
3673 , n_attribute4 = to_number(g_serial_attributes_tbl(36).COLUMN_VALUE)
3674 , n_attribute5 = to_number(g_serial_attributes_tbl(37).COLUMN_VALUE)
3675 , n_attribute6 = to_number(g_serial_attributes_tbl(38).COLUMN_VALUE)
3676 , n_attribute7 = to_number(g_serial_attributes_tbl(39).COLUMN_VALUE)
3677 , n_attribute8 = to_number(g_serial_attributes_tbl(40).COLUMN_VALUE)
3678 , n_attribute9 = to_number(g_serial_attributes_tbl(41).COLUMN_VALUE)
3679 , n_attribute10 = to_number(g_serial_attributes_tbl(42).COLUMN_VALUE)
3680 , status_id = Nvl(to_number(g_serial_attributes_tbl(43).COLUMN_VALUE),status_id)
3681 , territory_code = g_serial_attributes_tbl(44).COLUMN_VALUE
3682 WHERE inventory_item_id = p_inventory_item_id
3683 AND serial_number = p_serial_number
3684 AND current_organization_id = p_organization_id;
3685 END IF; -- IF NOT p_validate_only THEN
3686 END IF; --IF l_validation_status = 'Y' THEN
3687
3688 IF (l_debug = 1) THEN
3689 invtrace('VALIDATE_UPDATE_SERIAL_ATT:Exitting...');
3690 END IF;
3691
3692 EXCEPTION
3693 WHEN FND_API.G_EXC_ERROR THEN
3694 x_validation_status := l_validation_status;
3695 x_return_status := FND_API.G_RET_STS_ERROR;
3696 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3697 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3698 x_validation_status := l_validation_status;
3699 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3700 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3701 WHEN OTHERS THEN
3702 x_validation_status := l_validation_status;
3703 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3704 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3705 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,'Validate_Attributes');
3706 END IF;
3707 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3708 END validate_update_serial_att;
3709
3710 FUNCTION SNGetMask(P_txn_act_id IN NUMBER,
3711 P_txn_src_type_id IN NUMBER,
3712 P_serial_control IN NUMBER,
3713 x_to_status OUT NOCOPY NUMBER,
3714 x_dynamic_ok OUT NOCOPY NUMBER,
3715 P_receipt_issue_flag IN VARCHAR2,
3716 x_mask OUT NOCOPY VARCHAR2,
3717 x_errorcode OUT NOCOPY NUMBER)
3718 RETURN BOOLEAN IS
3719 --
3720 TYPE L_mask_tab IS TABLE OF VARCHAR2(17)
3721 INDEX BY BINARY_INTEGER;
3722 L_sn_mask L_mask_tab;
3723 L_group NUMBER := 0;
3724 --
3725 BEGIN
3726 x_errorcode := 0;
3727 x_to_status := 0;
3728 x_dynamic_ok := 0;
3729
3730 -- Bug 7427382, Modified the sn_mask array table to include statuses 6, 7 and 8 also.
3731
3732 L_sn_mask(1) := 'I0110100010000004';
3733 L_sn_mask(2) := 'I0000011010000014';
3734 L_sn_mask(3) := 'R0110001001000003';
3735 L_sn_mask(4) := 'R0000101991000013';
3736 L_sn_mask(5) := 'R0000011001000011';
3737 L_sn_mask(6) := '00000000000000000';
3738 L_sn_mask(7) := 'R0110001001000003';
3739 L_sn_mask(8) := 'R0000101001000013';
3740 L_sn_mask(9) := 'I0110100010000004';
3741 L_sn_mask(10):= '00000000000000000';
3742 L_sn_mask(11):= 'R0110110011000003';
3743 L_sn_mask(12):= 'I0110110010000004';
3744 L_sn_mask(13):= '00000000000000000';
3745 L_sn_mask(14):= 'R0110001001101003';
3746 L_sn_mask(15):= 'R0000101001101013';
3747 L_sn_mask(16):= 'I0110100010000005';
3748 L_sn_mask(17):= '00000000000000000';
3749 /*---------------------------------------------------------------------
3750 | Determine which group the transactions to. the value of
3751 | group will be used to provide the appropriate offset in the sn_mask
3752 | array table
3753 +----------------------------------------------------------------------*/
3754 -- Sales Order [SO] - 2
3755 -- RMA - 12
3756 -- SO RMA GROUP - 0
3757 IF P_txn_src_type_id in (2,12) then
3758 L_group := 0;
3759 ELSE
3760 IF P_txn_act_id = 2 THEN -- SUBXFR
3761 L_group := 10 ; -- SUB_XFER_GROUP
3762 ELSIF P_txn_act_id IN ( 12, 21 ) THEN -- INTERECEIPT(12) or INTSHIP(21)
3763 L_group := 13 ; -- INTRANS_GROUP
3764 ELSE -- Default Value
3765 L_group := 6 ; -- STD_GROUP
3766 END IF;
3767 END IF;
3768 L_group := L_group + 1; -- It starts from 0th position, just to avoid
3769 x_mask := L_sn_mask(L_group);
3770 /*---------------------------------------------------------------------
3771 | Match up the transaction with the appropriate mas and get the assigned
3772 | status. If there is no match, then to_status will still be zero after
3773 | the loop
3774 +-----------------------------------------------------------------------*/
3775 WHILE ( substr(x_mask,1,1) <> '0' )
3776 LOOP
3777 if ( substr(x_mask,1,1) = P_receipt_issue_flag ) AND
3778 ( substr(x_mask,P_serial_control+1,1) = '1' ) then
3779 x_to_status := to_number(substr(x_mask,17,1)); -- get the 17th character from mask
3780 x_dynamic_ok := to_number(substr(x_mask,16,1)); -- get the 16th character from mask
3781 exit;
3782 end if;
3783 L_group := L_group + 1; -- go to next mask group
3784 x_mask := L_sn_mask(L_group);
3785 END LOOP;
3786
3787 IF x_to_status = 0 then
3788 FND_MESSAGE.SET_NAME('INV', 'INV_INLTIS_SNGETMASK');
3789 FND_MSG_PUB.Add;
3790 x_errorcode := 123;
3791 return(FALSE);
3792 ELSE
3793 return(TRUE);
3794 END IF;
3795
3796 EXCEPTION
3797 WHEN OTHERS THEN
3798 x_errorcode := -1;
3799 return(FALSE);
3800 END SNGetmask;
3801
3802 PROCEDURE update_msn
3803 (x_return_status OUT NOCOPY VARCHAR2,
3804 x_msg_count OUT NOCOPY NUMBER,
3805 x_msg_data OUT NOCOPY VARCHAR2,
3806 p_trxdate IN DATE,
3807 p_transaction_temp_id IN NUMBER,
3808 p_rev IN VARCHAR2,
3809 p_lotnum IN VARCHAR2,
3810 p_orgid IN NUMBER,
3811 p_locid IN NUMBER, -- :lii,
3812 p_subinv IN VARCHAR2,
3813 p_trxsrctypid IN NUMBER,
3814 p_trxsrcid IN NUMBER,
3815 p_trx_act_id IN NUMBER,
3816 p_vendid IN NUMBER, -- :i_vendor_idi,
3817 p_venlot IN VARCHAR2,
3818 p_receipt_issue_type IN NUMBER,
3819 p_trxsname IN VARCHAR2,
3820 p_lstupdby IN NUMBER,
3821 p_parent_item_id IN NUMBER, -- :parent_item_i,
3822 p_parent_ser_num IN VARCHAR2, -- :parent_sn_i,
3823 p_ser_ctrl_code IN NUMBER,
3824 p_xfr_ser_ctrl_code IN NUMBER,
3825 p_trx_qty IN NUMBER,
3826 p_invitemid IN NUMBER,
3827 p_f_ser_num IN VARCHAR2,
3828 p_t_ser_num IN VARCHAR2,
3829 x_serial_updated OUT NOCOPY NUMBER
3830
3831 ) IS
3832 l_acct_prof_value VARCHAR2(1) := '';
3833 l_qty NUMBER := 0;
3834 l_last_status NUMBER := 0;
3835 l_to_status NUMBER := 0;
3836 l_canonical_trx_date DATE;
3837 l_sys_date DATE := SYSDATE;
3838 l_init_date DATE := trunc(sysdate);
3839 l_cg_id NUMBER := nvl(inv_cost_group_pub.g_cost_group_id, 0);
3840 l_upd_count NUMBER := 0;
3841 l_receipt_issue_flag VARCHAR2(1);
3842 l_error_code NUMBER;
3843 l_dynamic_ok NUMBER;
3844 l_mask VARCHAR2(17); -- Bug 7427382
3845 l_status BOOLEAN;
3846 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3847 BEGIN
3848 null;
3849
3850 IF (p_trx_act_id = inv_globals.g_action_stgxfr) THEN
3851 IF (l_debug = 1) THEN
3852 invtrace('The transaction action is staging transfer. Bulk processing of serials are not supported for this transaction');
3853
3854 END IF;
3855 RAISE FND_API.g_exc_unexpected_error;
3856 END IF;
3857
3858 IF nvl(fnd_profile.value('INV_RESTRICT_RCPT_SER'), '2') = '1' THEN
3859 l_acct_prof_Value := 'Y';
3860 else
3861 l_acct_prof_value := 'N';
3862 end if;
3863
3864 if( p_trx_qty < 0 ) THEN
3865 l_qty := -1 * p_trx_qty;
3866 else
3867 l_qty := p_trx_qty;
3868 end if;
3869 IF (l_debug = 1) THEN
3870 invtrace('l_acct_prof_value = ' || l_acct_prof_value);
3871 invtrace('p_trxdate = ' || p_trxdate);
3872 invtrace('p_transaction_temp_id = ' || p_transaction_temp_id);
3873 invtrace('p_rev = ' || p_rev);
3874 invtrace('p_lotnum = ' || p_lotnum);
3875 invtrace('p_orgid = ' || p_orgid);
3876 invtrace('p_locid = ' || p_locid);
3877 invtrace('p_subinv = ' || p_subinv);
3878 invtrace('p_trxsrctypid = ' || p_trxsrctypid);
3879 invtrace('p_trxsrcid = ' || p_trxsrcid);
3880 invtrace('p_trx_act_id = ' || p_trx_act_id);
3881 invtrace('p_vendid = ' || p_vendid);
3882 invtrace('p_venlot = ' || p_venlot);
3883 invtrace('p_receipt_issue_type = ' || p_receipt_issue_type);
3884 invtrace('p_trxsname = ' || p_trxsname);
3885 invtrace('p_lstupdby = ' || p_lstupdby);
3886 invtrace('p_parent_item_id = ' || p_parent_item_id);
3887 invtrace('p_parent_Ser_num = ' || p_parent_Ser_num);
3888 invtrace('p_ser_ctrl_code = ' || p_ser_ctrl_code);
3889 invtrace('p_xfr_ser_ctrl_code = ' || p_xfr_ser_ctrl_code);
3890 invtrace('p_trx_qty = ' || p_trx_qty);
3891 invtrace('p_invitemid = ' || p_invitemid);
3892 invtrace('p_f_ser_num = ' || p_f_ser_num);
3893 invtrace('p_t_ser_num = ' || p_t_ser_num);
3894 END IF;
3895
3896 SELECT current_status
3897 INTO l_last_status
3898 FROM mtl_serial_numbers
3899 WHERE inventory_item_id = p_invitemid
3900 AND serial_number = p_f_ser_num;
3901
3902 IF (l_debug = 1) THEN
3903 invtrace('l_last_status = ' || l_last_status);
3904 END IF;
3905
3906 if( p_receipt_issue_type = 1 ) THEN
3907 l_receipt_issue_flag := 'I';
3908 else
3909 l_receipt_issue_flag := 'R';
3910 end if;
3911
3912 IF (l_debug = 1) THEN
3913 invtrace('l_receipt_issue_flag = ' || l_receipt_issue_flag);
3914 END IF;
3915
3916 l_status := SNGetMask(p_txn_act_id => p_trx_act_id,
3917 p_txn_src_type_id => p_trxsrctypid,
3918 p_serial_control => p_ser_ctrl_code,
3919 x_to_status => l_to_status,
3920 x_dynamic_ok => l_dynamic_ok,
3921 p_receipt_issue_flag => l_receipt_issue_flag,
3922 x_mask => l_mask,
3923 x_errorcode => l_error_code) ;
3924
3925 IF (l_debug = 1) THEN
3926 invtrace('l_mask = ' || l_mask);
3927 END IF;
3928
3929 if( l_status = FALSE ) THEN
3930 IF (l_debug = 1) THEN
3931 invtrace('error from SNGetMask');
3932 END IF;
3933 raise FND_API.G_EXC_UNEXPECTED_ERROR;
3934 end if;
3935
3936 IF (l_debug = 1) THEN
3937 invtrace('l_to_status is ' || l_to_status);
3938 END IF;
3939
3940 IF( p_trx_qty < 0 AND p_trx_act_id = 2 ) THEN
3941 l_status_after_p1 := l_to_status;
3942 l_status_before_p1 := l_last_status;
3943 x_return_status := 'S';
3944 x_msg_count := 0;
3945 x_msg_data := NULL;
3946 x_serial_updated := abs(p_trx_qty);
3947 IF( MSN_UPDATE_FIRST_PASS ) THEN
3948 MSN_UPDATE_FIRST_PASS := FALSE;
3949 END if;
3950 return;
3951 else
3952 IF msn_update_first_pass THEN
3953 l_status_after_p1 := l_last_status;
3954 l_status_before_p1 := l_last_status;
3955 end if;
3956 end if;
3957
3958 IF (l_debug = 1) THEN
3959 invtrace('About to call fnd_date.canonical_to_date');
3960 --l_canonical_trx_date := fnd_date.canonical_to_date(p_trxdate);
3961 invtrace('After call fnd_date.canonical_to_date');
3962 END IF;
3963
3964 if( l_to_status in (1, 6) ) THEN
3965 UPDATE MTL_SERIAL_NUMBERS MSN
3966 SET msn.current_status = l_to_status,
3967 msn.initialization_date = l_init_date,
3968 msn.completion_date = null,
3969 msn.SHIP_DATE = NULL,
3970 msn.REVISION = NULL,
3971 msn.LOT_NUMBER = NULL,
3972 msn.GROUP_MARK_ID = NULL,
3973 msn.LINE_MARK_ID = NULL,
3974 msn.LOT_LINE_MARK_ID = NULL,
3975 msn.CURRENT_ORGANIZATION_ID = p_orgid,
3976 msn.CURRENT_LOCATOR_ID = NULL,
3977 msn.CURRENT_SUBINVENTORY_CODE = NULL,
3978 msn.ORIGINAL_WIP_ENTITY_ID = NULL,
3979 msn.ORIGINAL_UNIT_VENDOR_ID = NULL,
3980 msn.VENDOR_LOT_NUMBER = NULL,
3981 msn.LAST_RECEIPT_ISSUE_TYPE = p_receipt_issue_type,
3982 msn.LAST_TXN_SOURCE_ID =NULL,
3983 msn.LAST_TXN_SOURCE_TYPE_ID = NULL,
3984 msn.LAST_TXN_SOURCE_NAME = NULL,
3985 msn.LAST_UPDATE_DATE = l_sys_date,
3986 msn.LAST_UPDATED_BY = p_lstupdby,
3987 msn.PARENT_ITEM_ID = p_parent_item_id, -- :parent_item_i,
3988 msn.PARENT_SERIAL_NUMBER = p_parent_ser_num, -- :parent_sn_i,
3989 msn.PREVIOUS_STATUS = l_status_after_p1, -- l_last_status, -- p_last_status,
3990 msn.STATUS_ID = NULL,
3991 msn.ORGANIZATION_TYPE = 2,
3992 msn.OWNING_ORGANIZATION_ID = p_orgid,
3993 msn.OWNING_TP_TYPE = 2,
3994 msn.PLANNING_ORGANIZATION_ID = p_orgid,
3995 msn.PLANNING_TP_TYPE = 2
3996 WHERE
3997 msn.INVENTORY_ITEM_ID = p_invitemid
3998 AND msn.SERIAL_NUMBER BETWEEN p_f_ser_num AND p_t_ser_num
3999 AND decode( msn.CURRENT_STATUS, 6, 1, msn.CURRENT_STATUS ) = l_status_after_p1 -- l_last_status -- p_last_status
4000 AND Nvl(msn.owning_tp_type,2) <> 1
4001 AND Nvl(msn.owning_organization_id,msn.current_organization_id) = msn.current_organization_id
4002 AND inv_serial_number_pub.valsn(
4003 p_trxsrctypid, -- trx_src_typ_id IN NUMBER,
4004 p_trx_act_id, -- trx_action_id IN NUMBER,
4005 p_rev, -- revision IN VARCHAR2,
4006 p_subinv, -- curr_subinv_code IN VARCHAR2,
4007 p_locid, -- :lii, -- locator_id IN NUMBER,
4008 p_invitemid, -- item IN NUMBER,
4009 p_orgid, -- curr_org_id IN NUMBER,
4010 p_lotnum, -- lot IN VARCHAR2,
4011 msn.serial_number, -- curr_ser_num IN VARCHAR2,
4012 p_ser_ctrl_code, -- ser_num_ctrl_code IN NUMBER,
4013 p_xfr_ser_ctrl_code,
4014 p_trx_qty, -- trx_qty IN NUMBER,
4015 l_acct_prof_value, -- acct_prof_value IN VARCHAR2,
4016 l_mask, -- P_mask IN VARCHAR2,
4017 msn.current_status, /* db_current_status IN NUMBER, */
4018 msn.current_organization_id, -- db_current_organization_id IN NUMBER,
4019 msn.revision, -- db_revision IN VARCHAR2,
4020 msn.lot_number, -- db_lot_number IN VARCHAR2,
4021 msn.current_subinventory_code, -- db_current_subinventory_code IN VARCHAR2,
4022 msn.current_locator_id, -- db_current_locator_id IN NUMBER,
4023 decode( nvl( msn.original_wip_entity_id, -1 ), -1, -1 , 1 ), -- db_wip_ent_id_ind IN NUMBER,
4024 msn.last_txn_source_type_id -- db_lst_txn_src_typ_id IN NUMBER
4025 ) = l_to_status;
4026
4027 ELSE /* To status not in 1,6 */
4028
4029 -- l_canonical_trx_date := NULL;
4030 --l_canonical_trx_date := fnd_date.canonical_to_date( p_trxdate );
4031 --dbms_output.put_line('tostatus not in 1, 6');
4032 IF (l_debug = 1) THEN
4033 invtrace( 'To Status not in 1,6');
4034 END IF;
4035
4036 UPDATE MTL_SERIAL_NUMBERS msn
4037 SET
4038 msn.CURRENT_STATUS = l_to_status, -- p_current_status,
4039 msn.COMPLETION_DATE = NVL( msn.COMPLETION_DATE, p_trxdate ),
4040 msn.SHIP_DATE = DECODE( l_to_status, 3, NULL, NVL( msn.SHIP_DATE, p_trxdate ) ),
4041 msn.REVISION = DECODE( l_last_status, 3, msn.REVISION, p_rev ),
4042 msn.LOT_NUMBER = DECODE( l_last_status, 3, msn.LOT_NUMBER, p_lotnum ),
4043 msn.CURRENT_ORGANIZATION_ID = p_orgid,
4044 msn.CURRENT_LOCATOR_ID = p_locid, -- :lii,
4045 msn.CURRENT_SUBINVENTORY_CODE = p_subinv,
4046 msn.ORIGINAL_WIP_ENTITY_ID = decode( p_trxsrctypid, 5, p_trxsrcid, 2, NULL, msn.ORIGINAL_WIP_ENTITY_ID ),
4047 msn.ORIGINAL_UNIT_VENDOR_ID = NVL( msn.ORIGINAL_UNIT_VENDOR_ID, p_vendid ), -- :i_vendor_idi),
4048 msn.VENDOR_LOT_NUMBER = NVL( msn.VENDOR_LOT_NUMBER,p_venlot ),
4049 msn.LAST_RECEIPT_ISSUE_TYPE = p_receipt_issue_type,
4050 msn.LAST_TXN_SOURCE_ID = p_trxsrcid,
4051 msn.LAST_TXN_SOURCE_TYPE_ID = p_trxsrctypid,
4052 msn.LAST_TXN_SOURCE_NAME = p_trxsname,
4053 msn.GROUP_MARK_ID = NULL,
4054 msn.LINE_MARK_ID = NULL,
4055 msn.LOT_LINE_MARK_ID = NULL,
4056 msn.LAST_UPDATE_DATE = l_sys_date,
4057 msn.LAST_UPDATED_BY = p_lstupdby,
4058 msn.PARENT_ITEM_ID = p_parent_item_id, -- :parent_item_i,
4059 msn.PARENT_SERIAL_NUMBER = p_parent_ser_num, -- :parent_sn_i,
4060 msn.COST_GROUP_ID = l_cg_id,
4061 msn.ORGANIZATION_TYPE = 2,
4062 msn.OWNING_ORGANIZATION_ID = p_orgid,
4063 msn.OWNING_TP_TYPE = 2,
4064 msn.PLANNING_ORGANIZATION_ID = p_orgid,
4065 msn.PLANNING_TP_TYPE = 2
4066 WHERE
4067 msn.INVENTORY_ITEM_ID = p_invitemid
4068 AND msn.SERIAL_NUMBER BETWEEN p_f_ser_num AND p_t_ser_num
4069 AND decode( msn.CURRENT_STATUS, 6, 1, msn.CURRENT_STATUS ) = l_last_status
4070 AND Nvl(msn.owning_organization_id,msn.current_organization_id) = msn.current_organization_id
4071 AND Nvl(msn.owning_tp_type,2) <> 1
4072 AND inv_serial_number_pub.valsn(
4073 p_trxsrctypid, -- trx_src_typ_id IN NUMBER,
4074 p_trx_act_id, -- trx_action_id IN NUMBER,
4075 p_rev, -- revision IN VARCHAR2,
4076 p_subinv, -- curr_subinv_code IN VARCHAR2,
4077 p_locid, -- :lii, -- locator_id IN NUMBER,
4078 p_invitemid, -- item IN NUMBER,
4079 p_orgid, -- curr_org_id IN NUMBER,
4080 p_lotnum, -- lot IN VARCHAR2,
4081 msn.serial_number, -- curr_ser_num IN VARCHAR2,
4082 p_ser_ctrl_code, -- ser_num_ctrl_code IN NUMBER,
4083 nvl(p_xfr_ser_ctrl_code,1), -- p_xfr_ser_ctrl_code IN NUMBER
4084 p_trx_qty, -- trx_qty IN NUMBER,
4085 l_acct_prof_value, -- acct_prof_value IN VARCHAR2,
4086 l_mask, -- P_mask IN VARCHAR2,
4087 msn.current_status,
4088 msn.current_organization_id, -- db_current_organization_id IN NUMBER,
4089 msn.revision, -- db_revision IN VARCHAR2,
4090 msn.lot_number, -- db_lot_number IN VARCHAR2,
4091 msn.current_subinventory_code, -- db_current_subinventory_code IN VARCHAR2,
4092 msn.current_locator_id, -- db_current_locator_id IN NUMBER,
4093 decode( nvl( msn.original_wip_entity_id, -1 ), -1, -1 , 1 ), -- db_wip_ent_id_ind IN NUMBER,
4094 msn.last_txn_source_type_id -- db_lst_txn_src_typ_id IN NUMBER
4095 ) > 0;
4096
4097 end if;
4098
4099 l_upd_count := SQL%ROWCOUNT;
4100 IF (l_debug = 1) THEN
4101 invtrace( 'updated=' || to_char( l_upd_count ));
4102 END IF;
4103
4104 IF ( l_upd_count <> l_qty ) THEN
4105 IF (l_debug = 1) THEN
4106 invtrace( ' Updated not the same as the transaction. trx qty: ' || l_qty);
4107 END IF;
4108 x_return_status := 'W';
4109 x_msg_count := 0;
4110 x_msg_data := 'Can only update ' || to_char( l_upd_count ) || ' of ' ||
4111 to_char( l_qty ) || '. Rejecting update';
4112 ELSE
4113 IF (l_debug = 1) THEN
4114 invtrace( ' Updated same as the transaction. Success');
4115 END IF;
4116 x_serial_updated := l_upd_count;
4117 x_return_status := 'S';
4118 x_msg_count := 0;
4119 x_msg_data := NULL;
4120 END IF;
4121 EXCEPTION
4122 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4123 x_return_status := 'U';
4124 x_msg_data := substr(sqlerrm, 1, 255);
4125 x_msg_count := 1;
4126 IF (l_debug = 1) THEN
4127 invtrace( ' Unexpected error in update_msn API');
4128 END IF;
4129
4130 WHEN OTHERS THEN
4131 x_return_status := 'U';
4132 x_msg_data := substr( sqlerrm,1 , 255);
4133 IF (l_debug = 1) THEN
4134 invtrace( 'in when others');
4135 invtrace( 'x_return_status=' || x_return_status);
4136 invtrace( 'x_msg_data = ' || x_msg_data );
4137 IF (l_debug = 1) THEN
4138 invtrace( ' Error in update_msn API');
4139 END IF;
4140 END IF;
4141
4142 END update_msn;
4143
4144 FUNCTION getGroupId(
4145 p_trx_source_type_id IN NUMBER,
4146 p_trx_action_id IN NUMBER) RETURN NUMBER
4147 IS
4148 l_groupId NUMBER := 0;
4149 /*
4150 Trx_source_Type_id
4151 ------------------------
4152 2 - Sales Order
4153 12 - RMA
4154 8 - Internal Order
4155 7 - Internal Requisition
4156 16 - Project Contracts
4157
4158 TRX Action:
4159 ------------------
4160 2 - Sub Transfer
4161 5 - Planning Transfer
4162 6 - Consign Transfer
4163 12 - Intransit receipt
4164 21 - Intransit Shipment
4165 50 - pack
4166 51 - unpack
4167 52 - LPN Split
4168
4169 Group Id
4170 1 - SO_RMA_GROUP
4171 2 - SUB_XFER_GROUP
4172 3 - INTRANS_GROUP
4173 4 - PACKUNPACK_GROUP
4174 5 - STD_GROUP
4175 */
4176
4177 BEGIN
4178 if( p_trx_source_Type_id in (2, 7, 8, 12, 16 )) THEN
4179 l_groupId := 1;
4180 else
4181 if( p_trx_action_id in ( 2, 5, 6) ) THEN
4182 l_groupId := 2;
4183 elsif( p_trx_action_id in (12, 21) ) THEN
4184 l_groupId := 3;
4185 elsif( p_trx_action_id in (50, 51, 52 )) THEN
4186 l_groupId := 4;
4187 else
4188 l_groupId := 5;
4189 end if;
4190 END IF;
4191 return l_groupId;
4192 END getGroupId;
4193
4194 FUNCTION validate_status(
4195 p_trx_src_type_id IN number,
4196 p_trx_action_id IN number,
4197 p_isIssue IN boolean,
4198 p_ser_num_ctrl_code IN number,
4199 p_curr_status IN number,
4200 p_last_trx_src_type_id IN NUMBER,
4201 p_xfr_Ser_num_ctrl_code IN NUMBER,
4202 p_isRestrictRcptSerial IN NUMBER
4203 ) return number
4204 IS
4205 --l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
4206 l_groupId NUMBER := 0;
4207 l_newStatus NUMBER := p_curr_status;
4208 l_isRestrictRcptSerial NUMBER := p_isRestrictRcptSerial;
4209 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
4210
4211 BEGIN
4212 if( l_debug = 1 ) then
4213 invtrace('inside validate_status, p_trx_src_type_id = ' || p_trx_src_type_id);
4214 invtrace('inside validate_status, p_trx_action_id = ' || p_trx_action_id);
4215 invtrace('inside validate_status, p_ser_num_ctrl_code = ' || p_ser_num_ctrl_code);
4216 invtrace('inside validate_status, p_curr_status = ' || p_curr_status);
4217 invtrace('inside validate_status, p_last_trx_src_type_id = ' || p_last_trx_src_type_id);
4218 invtrace('inside validate_status, p_xfr_ser_num_ctrl_code = ' || p_xfr_ser_num_ctrl_code);
4219 end if;
4220 l_groupId := getGroupId(p_trx_src_type_id, p_trx_action_id);
4221
4222 --l_isRestrictRcptSerial := fnd_profile.value('INV_RESTRICT_RCPT_SER');
4223
4224 /*if( l_debug = 1 ) then
4225 invtrace('l_groupId = ' || l_groupId);
4226 end if;*/
4227
4228 if( l_groupId = 1 )THEN
4229 if( p_isIssue ) THEN
4230 if( p_ser_num_ctrl_code in (2, 3, 5)) THEN
4231 if( p_trx_src_type_id = 8 AND p_trx_action_id = 21 ) THEN
4232 if( p_curr_status = 3 ) THEN
4233 if( p_xfr_ser_num_ctrl_code = 1 ) THEN
4234 l_newStatus := 4;
4235 else
4236 l_newStatus := 5;
4237 end if;
4238 end if;
4239 elsif( p_curr_status = 3 ) THEN
4240 l_newStatus := 4;
4241 else
4242 --FND_MESSAGE.SET_NAME('INV', 'INV_INVALID_SERIAL');
4243 --FND_MSG_PUB.ADD;
4244 return -1;
4245 end if;
4246 elsif( p_ser_num_ctrl_code = 6 ) THEN
4247 if( p_trx_src_type_id = 8 AND p_trx_action_id = 21 ) THEN
4248 if( p_curr_status in (1, 3, 6) ) then
4249 l_newStatus := 5;
4250 end if;
4251 elsif( p_curr_status in (1, 3, 6) ) THEN
4252 l_newStatus := 4;
4253 else
4254 --FND_MESSAGE.SET_NAME('INV', 'INV_INVALID_SERIAL');
4255 --FND_MSG_PUB.ADD;
4256 return -2;
4257 end if;
4258 end if;
4259 else -- p_isIssue is false;
4260 if( p_ser_num_ctrl_code in (2, 3, 5) ) THEN
4261 if( p_curr_status in (1, 4, 6)) THEN
4262 l_newStatus := 3;
4263 else
4264 if( (p_trx_src_type_id in (12, 7)) AND (p_curr_status in (5, 7))) THEN
4265 l_newStatus := 4;
4266 else
4267 --FND_MESSAGE.SET_NAME('INV', 'INV_INVALID_SERIAL');
4268 --FND_MSG_PUB.ADD;
4269 return -3;
4270 end if;
4271 end if;
4272 elsif( p_ser_num_ctrl_code = 6 ) THEN
4273 if( p_last_trx_src_type_id = 12 AND p_curr_status = 1 AND l_isRestrictRcptSerial = 1 ) THEN
4274 --FND_MESSAGE.SET_NAME('INV', 'INV_INVALID_SERIAL');
4275 --FND_MSG_PUB.ADD;
4276 return -1;
4277 elsif( (p_trx_src_type_id = 7 OR p_trx_action_id = 12) AND (p_curr_status in (5, 7 ))) THEN
4278 l_newStatus := 1;
4279 else
4280 if( p_curr_status in (1, 4, 5, 6, 7 ) ) THEN
4281 l_newStatus := 1;
4282 else
4283 --FND_MESSAGE.SET_NAME('INV', 'INV_INVALID_SERIAL');
4284 --FND_MSG_PUB.ADD;
4285 return -1;
4286 end if;
4287 end if;
4288 end if;
4289 end if;
4290 elsif( l_groupId = 2 ) THEN
4291 if( p_ser_num_ctrl_code in (2, 3, 5)) THEN
4292 if( p_curr_status = 3 ) THEN
4293 l_newStatus := 3;
4294 else
4295 --FND_MESSAGE.SET_NAME('INV', 'INV_INVALID_SERIAL');
4296 --FND_MSG_PUB.ADD;
4297 return -1;
4298 end if;
4299 end if;
4300 elsif( l_groupId = 3 ) THEN
4301 if( p_isIssue ) THEN
4302 if( p_ser_num_ctrl_code in (2, 3, 5) ) then
4303 if( p_curr_status = 3 ) THEN
4304 if( p_xfr_ser_num_ctrl_code = 1 ) THEN
4305 l_newStatus := 4;
4306 else
4307 l_newStatus := 5;
4308 end if;
4309 else
4310 --FND_MESSAGE.SET_NAME('INV', 'INV_INVALID_SERIAL');
4311 --FND_MSG_PUB.ADD;
4312 return -1;
4313 end if;
4314 end if;
4315 else
4316 if( p_ser_num_ctrl_code in (2, 3, 5) ) THEN
4317 if( p_curr_status in (1, 4, 5, 6, 7) ) THEN
4318 l_newStatus := 3;
4319 else
4320 --FND_MESSAGE.SET_NAME('INV', 'INV_INVALID_SERIAL');
4321 --FND_MSG_PUB.ADD;
4322 return -1;
4323 end if;
4324 elsif( p_ser_num_ctrl_code = 6 ) THEN
4325 if( p_curr_status in (5,7) AND ( p_trx_src_type_id = 7 OR p_trx_action_id = 12) ) THEN
4326 l_newStatus := 1;
4327 else
4328 --FND_MESSAGE.SET_NAME('INV', 'INV_INVALID_SERIAL');
4329 --FND_MSG_PUB.ADD;
4330 return -1;
4331 end if;
4332 end if;
4333 end if;
4334 elsif( l_groupId = 4 ) THEN
4335 if( p_curr_status <> 3 ) THEN
4336 --FND_MESSAGE.SET_NAME('INV', 'INV_INVALID_SERIAL');
4337 --FND_MSG_PUB.ADD;
4338 return -1;
4339 end if;
4340 elsif( l_groupId = 5 ) THEN
4341 if( p_isIssue ) THEN
4342 if( p_ser_num_ctrl_code in (2, 3, 5)) THEN
4343 if( p_curr_status = 3 ) then
4344 l_newStatus := 4;
4345 else
4346 --FND_MESSAGE.SET_NAME('INV', 'INV_INVALID_SERIAL');
4347 --FND_MSG_PUB.ADD;
4348 return -1;
4349 end if;
4350 end if;
4351 else
4352 if( p_ser_num_ctrl_code in (2, 3, 5) ) THEN
4353 if( l_isRestrictRcptSerial = 1 AND p_trx_action_id = 27 AND
4354 p_curr_status = 4 AND p_last_trx_src_Type_id = 2 ) THEN
4355 --FND_MESSAGE.SET_NAME('INV', 'INV_INVALID_SERIAL');
4356 --FND_MSG_PUB.ADD;
4357 return -1;
4358 end if;
4359 if( p_curr_status in (1, 4, 6)) THEN
4360 if( p_trx_action_id = 3 AND p_xfr_ser_num_ctrl_code = 6 ) THEN
4361 l_newStatus := 1;
4362 else
4363 l_newStatus := 4;
4364 end if;
4365 else
4366 if( (p_trx_src_type_id in (1, 5) AND p_curr_status = 5 )OR
4367 (p_trx_src_type_id = 1 AND p_curr_status = 7 )) THEN
4368 l_newStatus := 3;
4369 else
4370 --FND_MESSAGE.SET_NAME('INV', 'INV_INVALID_SERIAL');
4371 --FND_MSG_PUB.ADD;
4372 return -1;
4373 end if;
4374 end if;
4375 end if;
4376 end if;
4377 end if;
4378
4379 return l_newStatus;
4380 End validate_status;
4381
4382 FUNCTION valsn(
4383 p_trx_src_type_id IN NUMBER,
4384 p_trx_action_id IN NUMBER,
4385 p_revision IN VARCHAR2,
4386 p_curr_subinv_code IN VARCHAR2,
4387 p_locator_id IN NUMBER,
4388 p_item IN NUMBER,
4389 p_curr_org_id IN NUMBER,
4390 p_lot IN VARCHAR2,
4391 p_curr_ser_num IN VARCHAR2,
4392 p_ser_num_ctrl_code IN NUMBER,
4393 p_xfr_ser_num_ctrl_code IN NUMBER,
4394 p_trx_qty IN NUMBER,
4395 p_acct_prof_value IN VARCHAR2,
4396 p_mask IN VARCHAR2,
4397 p_db_current_status IN NUMBER,
4398 p_db_current_organization_id IN NUMBER,
4399 p_db_revision IN VARCHAR2,
4400 p_db_lot_number IN VARCHAR2,
4401 p_db_current_subinventory_code IN VARCHAR2,
4402 p_db_current_locator_id IN NUMBER,
4403 p_db_wip_ent_id_ind IN NUMBER,
4404 p_db_lst_txn_src_type_id IN NUMBER
4405 ) RETURN NUMBER IS
4406 l_isIssue BOOLEAN := FALSE;
4407
4408 l_newStatus NUMBER := 0;
4409 l_retval NUMBER := 0;
4410 l_parent_ser_number VARCHAR2(30) := '';
4411 l_isRestrictRcptSerial NUMBER;
4412 l_groupId NUMBER := 0;
4413 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
4414 BEGIN
4415 --null;
4416 IF (l_debug = 1) THEN
4417 invtrace('p_trx_src_type_id = ' || p_trx_src_type_id);
4418 invtrace('p_trx_action_id = ' || p_trx_action_id);
4419 invtrace('p_revision = ' || p_revision);
4420 invtrace('p_curr_subinv_code = ' || p_curr_subinv_code);
4421 invtrace('p_locator_id = ' || p_locator_id);
4422 invtrace('p_item = ' || p_item);
4423 invtrace('p_curr_org_id = ' || p_curr_org_id);
4424 invtrace('p_lot = ' || p_lot);
4425 invtrace('p_curr_ser_num = ' || p_curr_ser_num);
4426 invtrace('p_ser_num_ctrl_code = ' || p_ser_num_ctrl_code);
4427 invtrace('p_xfr_ser_num_ctrl_code = ' || p_xfr_ser_num_ctrl_code);
4428 invtrace('p_trx_qty = ' || p_trx_qty);
4429 invtrace('p_acct_prof_value = ' || p_acct_prof_value);
4430 invtrace('p_mask = ' || p_mask);
4431 invtrace('p_db_current_status = ' || p_db_current_status);
4432 invtrace('p_db_current_organization_id = ' || p_db_current_organization_id);
4433 invtrace('p_db_revision = ' || p_db_revision);
4434 invtrace('p_db_lot_number = ' || p_db_lot_number);
4435 invtrace('p_db_current_subinventory_code = ' || p_db_current_subinventory_code);
4436 invtrace('p_db_current_locator_id = ' || p_db_current_locator_id);
4437 invtrace('p_db_wip_ent_id_ind = ' || p_db_wip_ent_id_ind);
4438 invtrace('p_db_lst_txn_src_type_id = ' || p_db_lst_txn_src_type_id);
4439 END IF;
4440
4441 if( p_trx_qty < 0 ) THEN
4442 l_isIssue := TRUE;
4443 IF (l_debug = 1) THEN
4444 invtrace('l_isIssue is true');
4445 END IF;
4446 else
4447 l_isIssue := FALSE;
4448 IF (l_debug = 1) THEN
4449 invtrace('l_isIssue is false');
4450 END IF;
4451 end if;
4452
4453 IF p_acct_prof_value = 'Y' THEN
4454 l_isRestrictRcptSerial := 1;
4455 else
4456 l_isRestrictRcptSerial := 0;
4457 end if;
4458
4459 -- getting the group id
4460 if( p_trx_src_Type_id in (2, 7, 8, 12, 16 )) THEN
4461 l_groupId := 1;
4462 else
4463 if( p_trx_action_id in ( 2, 5, 6) ) THEN
4464 l_groupId := 2;
4465 elsif( p_trx_action_id in (12, 21) ) THEN
4466 l_groupId := 3;
4467 elsif( p_trx_action_id in (50, 51, 52 )) THEN
4468 l_groupId := 4;
4469 else
4470 l_groupId := 5;
4471 end if;
4472 END IF;
4473
4474 --dbms_output.put_line('inside valsn, l_groupId = ' || l_groupId);
4475 IF (l_debug = 1) THEN
4476 invtrace('substr(p_mask, pdbcurrstats+7) = ' || substr(p_mask, p_db_current_status+7, 1));
4477 invtrace('l_groupId = ' || l_groupId);
4478 END IF;
4479
4480 IF substr( P_mask, p_db_current_status + 7, 1 )= '0' THEN
4481 -- return to_number( 'A' );
4482 -- ppush( 'avd_debug','db_current_status='|| to_char( db_current_status ) );
4483 -- ppush( 'avd_debug','curr_ser_num='|| curr_ser_num );
4484 -- ppush( 'avd_debug', '923');
4485 return -923;
4486 END IF;
4487
4488 -- Bug 7427382, supporting statuses 6, 7 and 8 also.
4489 IF ( p_db_current_status = -1 or p_db_current_status = 2 or
4490 p_db_current_status < 1 or p_db_current_status > 8 or p_db_current_status is NULL ) THEN
4491 --fnd_message.set_name( 'INV', 'INV_INVALID_SERIAL' );
4492 return -913;
4493 END IF;
4494
4495 -- validate status
4496 if( l_groupId = 1 )THEN
4497 if( l_isIssue ) THEN
4498 if( p_ser_num_ctrl_code in (2, 3, 5)) THEN
4499 if( p_trx_src_type_id = 8 AND p_trx_action_id = 21 ) THEN
4500 if( p_db_current_status = 3 ) THEN
4501 if( nvl(p_xfr_ser_num_ctrl_code, 0) = 1 ) THEN
4502 l_newStatus := 4;
4503 else
4504 l_newStatus := 5;
4505 end if;
4506 end if;
4507 elsif( p_db_current_status = 3 ) THEN
4508 l_newStatus := 4;
4509 else
4510 l_retval := -901;
4511 end if;
4512 elsif( p_ser_num_ctrl_code = 6 ) THEN
4513 if( p_trx_src_type_id = 8 AND p_trx_action_id = 21 ) THEN
4514 if( p_db_current_status in (1, 3, 6) ) then
4515 l_newStatus := 5;
4516 end if;
4517 elsif( p_db_current_status in (1, 3, 6) ) THEN
4518 l_newStatus := 4;
4519 else
4520 --FND_MESSAGE.SET_NAME('INV', 'INV_INVALID_SERIAL');
4521 --FND_MSG_PUB.ADD;
4522 l_retval := -902;
4523 end if;
4524 end if;
4525 else -- p_isIssue is false;
4526 if( p_ser_num_ctrl_code in (2, 3, 5) ) THEN
4527 if( p_db_current_status in (1, 4, 6)) THEN
4528 l_newStatus := 3;
4529 else
4530 if( (p_trx_src_type_id in (12, 7)) AND (p_db_current_status in (5, 7))) THEN
4531 l_newStatus := 4;
4532 else
4533 --FND_MESSAGE.SET_NAME('INV', 'INV_INVALID_SERIAL');
4534 --FND_MSG_PUB.ADD;
4535 l_retval := -903;
4536 end if;
4537 end if;
4538 elsif( p_ser_num_ctrl_code = 6 ) THEN
4539 if( p_db_lst_txn_src_type_id = 12 AND p_db_current_status = 1 AND l_isRestrictRcptSerial = 1 ) THEN
4540 --FND_MESSAGE.SET_NAME('INV', 'INV_INVALID_SERIAL');
4541 --FND_MSG_PUB.ADD;
4542 l_retval := -904;
4543 elsif( (p_trx_src_type_id = 7 OR p_trx_action_id = 12) AND (p_db_current_status in (5, 7 ))) THEN
4544 l_newStatus := 1;
4545 else
4546 if( p_db_current_status in (1, 4, 5, 6, 7 ) ) THEN
4547 l_newStatus := 1;
4548 else
4549 --FND_MESSAGE.SET_NAME('INV', 'INV_INVALID_SERIAL');
4550 --FND_MSG_PUB.ADD;
4551 l_retval := -905;
4552 end if;
4553 end if;
4554 end if;
4555 end if;
4556 elsif( l_groupId = 2 ) THEN
4557 if( p_ser_num_ctrl_code in (2, 3, 5)) THEN
4558 if( p_db_current_status = 3 ) THEN
4559 l_newStatus := 3;
4560 else
4561 --FND_MESSAGE.SET_NAME('INV', 'INV_INVALID_SERIAL');
4562 --FND_MSG_PUB.ADD;
4563 l_retval := -906;
4564 end if;
4565 end if;
4566 elsif( l_groupId = 3 ) THEN
4567 if( l_isIssue ) THEN
4568 if( p_ser_num_ctrl_code in (2, 3, 5) ) then
4569 if( p_db_current_status = 3 ) THEN
4570 if( p_xfr_ser_num_ctrl_code = 1 ) THEN
4571 l_newStatus := 4;
4572 else
4573 l_newStatus := 5;
4574 end if;
4575 else
4576 --FND_MESSAGE.SET_NAME('INV', 'INV_INVALID_SERIAL');
4577 --FND_MSG_PUB.ADD;
4578 l_retval := -907;
4579 end if;
4580 end if;
4581 else
4582 if( p_ser_num_ctrl_code in (2, 3, 5) ) THEN
4583 if( p_db_current_status in (1, 4, 5, 6, 7) ) THEN
4584 l_newStatus := 3;
4585 else
4586 --FND_MESSAGE.SET_NAME('INV', 'INV_INVALID_SERIAL');
4587 --FND_MSG_PUB.ADD;
4588 l_retval := -908;
4589 end if;
4590 elsif( p_ser_num_ctrl_code = 6 ) THEN
4591 if( p_db_current_status in (5,7) AND ( p_trx_src_type_id = 7 OR p_trx_action_id = 12) ) THEN
4592 l_newStatus := 1;
4593 else
4594 --FND_MESSAGE.SET_NAME('INV', 'INV_INVALID_SERIAL');
4595 --FND_MSG_PUB.ADD;
4596 l_retval := -908;
4597 end if;
4598 end if;
4599 end if;
4600 elsif( l_groupId = 4 ) THEN
4601 if( p_db_current_status <> 3 ) THEN
4602 --FND_MESSAGE.SET_NAME('INV', 'INV_INVALID_SERIAL');
4603 --FND_MSG_PUB.ADD;
4604 l_retval := -909;
4605 end if;
4606 elsif( l_groupId = 5 ) THEN
4607 --dbms_output.put_line('standard group');
4608 if( l_isIssue ) THEN
4609 if( p_ser_num_ctrl_code in (2, 3, 5)) THEN
4610 if( p_db_current_status = 3 ) then
4611 l_newStatus := 4;
4612 else
4613 --FND_MESSAGE.SET_NAME('INV', 'INV_INVALID_SERIAL');
4614 --FND_MSG_PUB.ADD;
4615 l_retval := -910;
4616 end if;
4617 end if;
4618 else
4619 --dbms_output.put_line('inside receipt part');
4620 if( p_ser_num_ctrl_code in (2, 3, 5) ) THEN
4621 if( l_isRestrictRcptSerial = 1 AND p_trx_action_id = 27 AND
4622 p_db_current_status = 4 AND p_db_lst_txn_src_Type_id = 2 ) THEN
4623 --FND_MESSAGE.SET_NAME('INV', 'INV_INVALID_SERIAL');
4624 --FND_MSG_PUB.ADD;
4625 l_retval := -911;
4626 end if;
4627 if( p_db_current_status in (1, 4, 6)) THEN
4628 if( p_trx_action_id = 3 AND p_xfr_ser_num_ctrl_code = 6 ) THEN
4629 l_newStatus := 1;
4630 else
4631 l_newStatus := 3;
4632 end if;
4633 else
4634 if( (p_trx_src_type_id in (1, 5) AND p_db_current_status = 5 )OR
4635 (p_trx_src_type_id = 1 AND p_db_current_status = 7 )) THEN
4636 l_newStatus := 3;
4637 else
4638 --FND_MESSAGE.SET_NAME('INV', 'INV_INVALID_SERIAL');
4639 --FND_MSG_PUB.ADD;
4640 l_retval := -912;
4641 end if;
4642 end if;
4643 end if;
4644 end if;
4645 end if;
4646
4647 /*l_NewStatus := validate_Status(p_trx_src_type_id, p_trx_action_id, l_isIssue, p_ser_num_ctrl_code, p_db_current_status,
4648 p_db_lst_txn_src_type_id, p_xfr_ser_num_ctrl_code, l_isRestrictRcptSerial);*/
4649
4650 IF ( ( p_db_current_status = 3 OR p_db_current_status = 1 )
4651 AND p_db_current_organization_id <> p_curr_org_id ) THEN
4652 --fnd_message.set_name( 'INV', 'INV_SER_ORG_INVALID' );
4653 l_retval := -914;
4654 END IF;
4655
4656
4657 IF (p_db_current_status = 3) THEN
4658 IF (p_db_revision IS NOT NULL AND p_db_revision <> p_revision ) THEN
4659
4660 --fnd_message.set_name('INV', 'INV_SER_REV_INVALID');
4661 --fnd_message.set_Token('TOKEN1', p_db_Revision);
4662 --fnd_message.set_Token('TOKEN2', p_revision);
4663 l_retval := -915;
4664 END IF;
4665
4666 IF ( p_db_lot_number IS NOT NULL AND p_db_Lot_Number <> p_lot ) THEN
4667 --fnd_message.set_name('INV', 'INV_SER_LOT_INVALID');
4668 --fnd_message.set_Token('TOKEN1', p_curr_ser_num);
4669 --fnd_message.set_Token('TOKEN2', p_db_Lot_Number);
4670 l_retval := -916;
4671 END IF;
4672 END IF;
4673
4674 /* commented out because of db_lpn_id...do not know why used */
4675 -- IF (bool AND db_current_status = 3 AND db_lpn_id = 0) THEN
4676 IF (l_isIssue AND p_db_current_status = 3 ) THEN
4677
4678 IF (p_db_current_subinventory_code IS NOT NULL AND
4679 p_db_current_subinventory_code <> p_curr_subinv_code) THEN
4680 --fnd_message.set_name('INV', 'INV_SER_SUB_INVALID');
4681 --fnd_message.set_Token('TOKEN1', p_curr_ser_num);
4682 --fnd_message.set_Token('TOKEN2', p_db_current_subinventory_code);
4683 l_retval := -917;
4684 END IF;
4685
4686 IF (p_db_current_locator_id <> 0 AND
4687 p_db_current_locator_id <> p_locator_id ) THEN
4688 --fnd_message.set_name('INV', 'INV_SER_LOC_INVALID');
4689 --fnd_message.set_Token('TOKEN1', p_curr_ser_num);
4690 l_retval := -918;
4691 END IF;
4692
4693 END IF;
4694
4695
4696 IF (p_trx_src_type_id = 5 ) THEN -- {
4697 IF ((p_trx_action_id = 31 AND p_db_current_status <> 1)
4698 OR (p_trx_action_id = 32)
4699 OR (p_trx_action_id = 27)) THEN -- {
4700 IF (p_db_wip_ent_id_ind = -1) THEN -- {
4701 --fnd_message.set_name('INV', 'INV_SER_STATUS_NA');
4702 --fnd_message.set_Token('TOKEN1', p_curr_ser_num);
4703 l_retval := -919; -- return(FALSE);
4704 END IF; -- }
4705 END IF; -- }
4706 END IF; -- }
4707
4708 -- For any receipt into warehouse for Serial Control - Dyn. at inv. receipt
4709 -- LOV does not validate the serial number entered.
4710 -- So any serial number with current status = 4 gets successfully received .
4711
4712 IF ( (p_ser_num_ctrl_code = 5) AND (p_db_current_status = 4 )
4713 AND (p_trx_qty > 0 ) AND ( p_trx_action_id =27 )
4714 AND (p_trx_src_type_id <> 5) AND (p_db_wip_ent_id_ind <> -1)
4715 AND ( p_acct_prof_value = 'Y' /*1*/) AND (p_db_lst_txn_src_type_id = 5 )) THEN -- {
4716
4717 --fnd_message.set_name('INV', 'INV_SER_STATUS_NA');
4718 --fnd_message.set_Token('TOKEN1', p_curr_ser_num);
4719 l_retval := -920; -- return(FALSE);
4720
4721 END IF; -- }
4722
4723 IF ( (p_ser_num_ctrl_code = 5) AND (p_db_current_status = 4 )
4724 AND (p_trx_qty > 0 ) AND ( p_trx_action_id = 27 )
4725 AND (p_trx_src_type_id <> 5) AND (p_db_wip_ent_id_ind <> -1)
4726 AND (p_acct_prof_value = 'Y' /*1*/) AND (p_db_lst_txn_src_type_id = 6 )
4727 AND (p_trx_src_type_id <> 6 )) THEN -- {
4728
4729 --fnd_message.set_name('INV', 'INV_SER_STATUS_NA');
4730 --fnd_message.set_Token('TOKEN1', p_curr_ser_num);
4731 l_retval := -920; -- return(FALSE);
4732
4733 END IF; -- }
4734
4735 IF ( (p_ser_num_ctrl_code = 5) AND (p_db_current_status = 4 )
4736 AND (( p_trx_action_id = 31 ) OR (p_trx_action_id = 1))
4737 AND (p_trx_src_type_id = 5) AND (p_db_wip_ent_id_ind <> -1)
4738 AND (p_acct_prof_value = 'Y' /*1*/)
4739 AND ((p_db_lst_txn_src_type_id = 5) OR (p_db_lst_txn_src_type_id = 6 ))) THEN -- {
4740
4741 --fnd_message.set_name('INV', 'INV_SER_STATUS_NA');
4742 --fnd_message.set_Token('TOKEN1', p_curr_ser_num);
4743 l_retval := -921; -- return(FALSE);
4744
4745 END IF; -- }
4746
4747 IF (l_debug = 1) THEN
4748 invtrace('l_retval = ' || l_retval);
4749 END IF;
4750
4751 IF ( l_retval > -900 ) then
4752 RETURN l_newstatus;
4753 -- RETURN validateStatus(trx_src_typ_id, trx_action_id, bool, ser_num_ctrl_code, db_current_status);
4754 ELSE
4755 RETURN l_retval;
4756 END IF;
4757 EXCEPTION
4758 WHEN OTHERS THEN
4759 RAISE;
4760 END valsn;
4761
4762 PROCEDURE insertRangeUnitTrx(
4763 p_api_version IN NUMBER,
4764 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
4765 p_commit IN VARCHAR2 := FND_API.G_FALSE,
4766 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
4767 p_inventory_item_id IN NUMBER,
4768 p_organization_id IN NUMBER,
4769 p_fm_serial_number IN VARCHAR2,
4770 p_to_serial_number IN VARCHAR2,
4771 p_current_locator_id IN NUMBER,
4772 p_subinventory_code IN VARCHAR2,
4773 p_transaction_date IN DATE,
4774 p_txn_src_id IN NUMBER,
4775 p_txn_src_name IN VARCHAR2,
4776 p_txn_src_type_id IN NUMBER,
4777 p_transaction_id IN NUMBER,
4778 p_transaction_action_id IN NUMBER,
4779 p_transaction_temp_id IN NUMBER,
4780 p_receipt_issue_type IN NUMBER,
4781 p_customer_id IN NUMBER,
4782 p_ship_id IN NUMBER,
4783 p_status_id IN NUMBER,
4784 x_return_status OUT nOCOPY VARCHAR2,
4785 x_msg_count OUT nOCOPY NUMBER,
4786 x_msg_data OUT nOCOPY VARCHAR2)
4787 IS
4788 l_api_version CONSTANT NUMBER := 1.0;
4789 l_api_name CONSTANT VARCHAR2(30):= 'insertRangeUnitTrx';
4790 l_userid NUMBER;
4791 l_loginid NUMBER;
4792 l_serial_control_code NUMBER;
4793 l_attributes_default INV_LOT_SEL_ATTR.LOT_SEL_ATTRIBUTES_TBL_TYPE;
4794 l_attributes_default_count NUMBER;
4795 l_attributes_in INV_LOT_SEL_ATTR.LOT_SEL_ATTRIBUTES_TBL_TYPE;
4796 l_column_idx BINARY_INTEGER := 44;
4797 l_return_status VARCHAR2(1);
4798 l_msg_data VARCHAR2(2000);
4799 l_msg_count NUMBER;
4800 l_upd_count NUMBER;
4801 l_TIME_SINCE_NEW mtl_serial_numbers_temp.TIME_SINCE_NEW%type;
4802 l_CYCLES_SINCE_NEW mtl_serial_numbers_temp.CYCLES_SINCE_NEW%type;
4803 l_TIME_SINCE_OVERHAUL mtl_serial_numbers_temp.TIME_SINCE_OVERHAUL%type := NULL;
4804 l_CYCLES_SINCE_OVERHAUL mtl_serial_numbers_temp.CYCLES_SINCE_OVERHAUL%type;
4805 l_TIME_SINCE_REPAIR mtl_serial_numbers_temp.TIME_SINCE_REPAIR%type;
4806 l_CYCLES_SINCE_REPAIR mtl_serial_numbers_temp.CYCLES_SINCE_REPAIR%type;
4807 l_TIME_SINCE_VISIT mtl_serial_numbers_temp.TIME_SINCE_VISIT%type;
4808 l_CYCLES_SINCE_VISIT mtl_serial_numbers_temp.CYCLES_SINCE_VISIT%type;
4809 l_TIME_SINCE_MARK mtl_serial_numbers_temp.TIME_SINCE_MARK%type;
4810 l_CYCLES_SINCE_MARK mtl_serial_numbers_temp.CYCLES_SINCE_MARK%type;
4811 l_NUMBER_OF_REPAIRS mtl_serial_numbers_temp.NUMBER_OF_REPAIRS%type;
4812
4813
4814 l_sys_date date := NULL;
4815 l_date2 date := NULL;
4816 l_date23 date := NULL;
4817 l_date24 date := NULL;
4818 l_date25 date := NULL;
4819 l_date26 date := NULL;
4820 l_date27 date := NULL;
4821 l_date28 date := NULL;
4822 l_date29 date := NULL;
4823 l_date30 date := NULL;
4824 l_date31 date := NULL;
4825 l_date32 date := NULL;
4826
4827 l_num33 NUMBER := NULL;
4828 l_num34 NUMBER := NULL;
4829 l_num35 NUMBER := NULL;
4830 l_num36 NUMBER := NULL;
4831 l_num37 NUMBER := NULL;
4832 l_num38 NUMBER := NULL;
4833 l_num39 NUMBER := NULL;
4834 l_num40 NUMBER := NULL;
4835 l_num41 NUMBER := NULL;
4836 l_num42 NUMBER := NULL;
4837
4838 cursor serial_temp_csr(p_transaction_temp_id NUMBER) is
4839 select SERIAL_ATTRIBUTE_CATEGORY
4840 , fnd_date.date_to_canonical(ORIGINATION_DATE )
4841 , C_ATTRIBUTE1
4842 , C_ATTRIBUTE2
4843 , C_ATTRIBUTE3
4844 , C_ATTRIBUTE4
4845 , C_ATTRIBUTE5
4846 , C_ATTRIBUTE6
4847 , C_ATTRIBUTE7
4848 , C_ATTRIBUTE8
4849 , C_ATTRIBUTE9
4850 , C_ATTRIBUTE10
4851 , C_ATTRIBUTE11
4852 , C_ATTRIBUTE12
4853 , C_ATTRIBUTE13
4854 , C_ATTRIBUTE14
4855 , C_ATTRIBUTE15
4856 , C_ATTRIBUTE16
4857 , C_ATTRIBUTE17
4858 , C_ATTRIBUTE18
4859 , C_ATTRIBUTE19
4860 , C_ATTRIBUTE20
4861 , fnd_date.date_to_canonical(D_ATTRIBUTE1 )
4862 , fnd_date.date_to_canonical(D_ATTRIBUTE2 )
4863 , fnd_date.date_to_canonical(D_ATTRIBUTE3 )
4864 , fnd_date.date_to_canonical(D_ATTRIBUTE4 )
4865 , fnd_date.date_to_canonical(D_ATTRIBUTE5 )
4866 , fnd_date.date_to_canonical(D_ATTRIBUTE6 )
4867 , fnd_date.date_to_canonical(D_ATTRIBUTE7)
4868 , fnd_date.date_to_canonical(D_ATTRIBUTE8)
4869 , fnd_date.date_to_canonical( D_ATTRIBUTE9)
4870 , fnd_date.date_to_canonical(D_ATTRIBUTE10 )
4871 , to_char(N_ATTRIBUTE1 )
4872 , to_char(N_ATTRIBUTE2)
4873 , to_char(N_ATTRIBUTE3)
4874 , to_char(N_ATTRIBUTE4)
4875 , to_char(N_ATTRIBUTE5)
4876 , to_char(N_ATTRIBUTE6)
4877 , to_char(N_ATTRIBUTE7)
4878 , to_char(N_ATTRIBUTE8)
4879 , to_char( N_ATTRIBUTE9)
4880 , to_char(N_ATTRIBUTE10)
4881 , STATUS_ID
4882 , TERRITORY_CODE
4883 , TIME_SINCE_NEW
4884 , CYCLES_SINCE_NEW
4885 , TIME_SINCE_OVERHAUL
4886 , CYCLES_SINCE_OVERHAUL
4887 , TIME_SINCE_REPAIR
4888 , CYCLES_SINCE_REPAIR
4889 , TIME_SINCE_VISIT
4890 , CYCLES_SINCE_VISIT
4891 , TIME_SINCE_MARK
4892 , CYCLES_SINCE_MARK
4893 , NUMBER_OF_REPAIRS
4894 from mtl_serial_numbers_temp
4895 where transaction_temp_id = p_transaction_temp_id
4896 and fm_serial_number = p_fm_serial_number and to_serial_number = p_to_serial_number;
4897 l_input_idx BINARY_INTEGER;
4898
4899 l_fm_serial_number VARCHAR2(30) := lpad(p_fm_serial_number, 30);
4900 l_to_serial_number VARCHAR2(30) := lpad(p_to_serial_number, 30);
4901 l_wms_installed BOOLEAN;
4902 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
4903 BEGIN
4904 IF (l_debug = 1) THEN
4905 invtrace('Inside InsertRangeUnitTrx');
4906 invtrace('l_fm_serial_number is ' || l_fm_serial_number);
4907 invtrace('l_to_serial_number is ' || l_to_serial_number);
4908 END IF;
4909
4910 --Bug# 6825191, Commenting out the code below.
4911 --It is safe to comment the following code because
4912 -- 1. variables G_first_row_of_trx, G_first_row_trx_tmp_id are
4913 -- not referenced anytime later.
4914 -- 2. having this code introduces two problems
4915 -- a. It inserts MUT only for the first MSNT in multiple MSNT scenario.
4916 -- b. It does not insert MUT's for receipt side in transfer transaction flows.
4917 /*
4918 IF ( G_first_row_of_trx ) THEN
4919 IF (l_debug = 1) THEN
4920 invtrace('setting G_first_row_of_trx');
4921 END IF;
4922
4923 G_first_row_of_trx := FALSE;
4924 G_first_row_trx_tmp_id := p_transaction_temp_id ;
4925 IF (l_debug = 1) THEN
4926 invtrace('G_first_row_trx_tmp_id = ' || G_first_row_trx_tmp_id);
4927 END IF;
4928 ELSE
4929 IF ( p_transaction_temp_id <> g_first_row_trx_tmp_id ) THEN
4930 g_first_row_trx_tmp_id := p_transaction_temp_id;
4931 ELSE
4932 x_return_status := FND_API.G_RET_STS_SUCCESS;
4933 RETURN;
4934 END IF;
4935 END IF;
4936 */
4937 -- Standard Start of API savepoint
4938 SAVEPOINT apiinsertserial_apipub;
4939
4940 -- Standard call to check for call compatibility.
4941 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
4942 RAISE fnd_api.g_exc_unexpected_error;
4943 END IF;
4944 -- Initialize message list if p_init_msg_list is set to TRUE.
4945 IF fnd_api.to_boolean(p_init_msg_list) THEN
4946 fnd_msg_pub.initialize;
4947 END IF;
4948
4949 -- Initialize API return status to success
4950
4951 /**IF (p_transaction_action_id = 3 AND g_firstscan = FALSE) THEN
4952 x_return_status := fnd_api.g_ret_sts_success;
4953 ELSE**/
4954 x_return_status := fnd_api.g_ret_sts_success;
4955 l_wms_installed :=
4956 wms_install.check_install(
4957 x_return_status => l_return_status
4958 , x_msg_count => l_msg_count
4959 , x_msg_data => l_msg_data
4960 , p_organization_id => NULL --p_organization_id
4961 );
4962
4963 IF (p_transaction_temp_id IS NOT NULL) THEN
4964 IF (l_debug = 1) THEN
4965 invtrace('p_transaction_temp_id is notnull');
4966 END IF;
4967
4968 OPEN serial_temp_csr(p_transaction_temp_id);
4969
4970 populateattributescolumn();
4971
4972 FETCH serial_temp_csr
4973 INTO g_serial_attributes_tbl(1).column_value
4974 , g_serial_attributes_tbl(2).column_value
4975 , g_serial_attributes_tbl(3).column_value
4976 , g_serial_attributes_tbl(4).column_value
4977 , g_serial_attributes_tbl(5).column_value
4978 , g_serial_attributes_tbl(6).column_value
4979 , g_serial_attributes_tbl(7).column_value
4980 , g_serial_attributes_tbl(8).column_value
4981 , g_serial_attributes_tbl(9).column_value
4982 , g_serial_attributes_tbl(10).column_value
4983 , g_serial_attributes_tbl(11).column_value
4984 , g_serial_attributes_tbl(12).column_value
4985 , g_serial_attributes_tbl(13).column_value
4986 , g_serial_attributes_tbl(14).column_value
4987 , g_serial_attributes_tbl(15).column_value
4988 , g_serial_attributes_tbl(16).column_value
4989 , g_serial_attributes_tbl(17).column_value
4990 , g_serial_attributes_tbl(18).column_value
4991 , g_serial_attributes_tbl(19).column_value
4992 , g_serial_attributes_tbl(20).column_value
4993 , g_serial_attributes_tbl(21).column_value
4994 , g_serial_attributes_tbl(22).column_value
4995 , g_serial_attributes_tbl(23).column_value
4996 , g_serial_attributes_tbl(24).column_value
4997 , g_serial_attributes_tbl(25).column_value
4998 , g_serial_attributes_tbl(26).column_value
4999 , g_serial_attributes_tbl(27).column_value
5000 , g_serial_attributes_tbl(28).column_value
5001 , g_serial_attributes_tbl(29).column_value
5002 , g_serial_attributes_tbl(30).column_value
5003 , g_serial_attributes_tbl(31).column_value
5004 , g_serial_attributes_tbl(32).column_value
5005 , g_serial_attributes_tbl(33).column_value
5006 , g_serial_attributes_tbl(34).column_value
5007 , g_serial_attributes_tbl(35).column_value
5008 , g_serial_attributes_tbl(36).column_value
5009 , g_serial_attributes_tbl(37).column_value
5010 , g_serial_attributes_tbl(38).column_value
5011 , g_serial_attributes_tbl(39).column_value
5012 , g_serial_attributes_tbl(40).column_value
5013 , g_serial_attributes_tbl(41).column_value
5014 , g_serial_attributes_tbl(42).column_value
5015 , g_serial_attributes_tbl(43).column_value
5016 , g_serial_attributes_tbl(44).column_value
5017 , l_TIME_SINCE_NEW, l_CYCLES_SINCE_NEW, l_TIME_SINCE_OVERHAUL, l_CYCLES_SINCE_OVERHAUL,
5018 l_TIME_SINCE_REPAIR, l_CYCLES_SINCE_REPAIR , l_TIME_SINCE_VISIT, l_CYCLES_SINCE_VISIT,
5019 l_TIME_SINCE_MARK, l_CYCLES_SINCE_MARK, l_NUMBER_OF_REPAIRS;
5020
5021 CLOSE serial_temp_csr;
5022
5023 l_input_idx := 0;
5024
5025 IF l_wms_installed THEN
5026 FOR x IN 1 .. 44 LOOP
5027 IF (g_serial_attributes_tbl(x).column_value IS NOT NULL) THEN
5028 l_input_idx := l_input_idx + 1;
5029 l_attributes_in(l_input_idx).column_name := g_serial_attributes_tbl(x).column_name;
5030 l_attributes_in(l_input_idx).column_type := g_serial_attributes_tbl(x).column_type;
5031 l_attributes_in(l_input_idx).column_value := g_serial_attributes_tbl(x).column_value;
5032 END IF;
5033 END LOOP;
5034 END IF; -- if wms_installed is true
5035 END IF; -- if transaction_temp_id is not null
5036
5037 ----------------------------------------------------------
5038 -- call inv_lot_sel_attr.get_default to get the default value
5039 -- of the lot attributes
5040 ---------------------------------------------------------
5041 IF l_wms_installed THEN
5042 inv_lot_sel_attr.get_default(
5043 x_attributes_default => l_attributes_default
5044 , x_attributes_default_count => l_attributes_default_count
5045 , x_return_status => l_return_status
5046 , x_msg_count => l_msg_count
5047 , x_msg_data => l_msg_data
5048 , p_table_name => 'MTL_SERIAL_NUMBERS'
5049 , p_attributes_name => 'Serial Attributes'
5050 , p_inventory_item_id => p_inventory_item_id
5051 , p_organization_id => p_organization_id
5052 , p_lot_serial_number => p_fm_serial_number
5053 , p_attributes => l_attributes_in
5054 );
5055
5056 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
5057 x_return_status := l_return_status;
5058 RAISE fnd_api.g_exc_unexpected_error;
5059 END IF;
5060
5061 /* Get the default attribs only when there is no value on the form (in MSNT).
5062 * In case the user changes the context and the attributes while recieving,
5063 * they would be lost if we get the default context again--2756040
5064 */
5065 IF (l_attributes_default_count > 0
5066 AND(g_serial_attributes_tbl(1).column_value = NULL)) THEN
5067 FOR i IN 1 .. l_attributes_default_count LOOP
5068 FOR j IN 1 .. g_serial_attributes_tbl.COUNT LOOP
5069 IF (l_attributes_default(i).column_name = g_serial_attributes_tbl(j).column_name) THEN
5070 g_serial_attributes_tbl(j).column_value := l_attributes_default(i).column_value;
5071 END IF;
5072 END LOOP;
5073 END LOOP;
5074 END IF;
5075 END IF; -- if wms install is true
5076
5077 l_userid := fnd_global.user_id;
5078 l_loginid := fnd_global.login_id;
5079 l_sys_date := SYSDATE;
5080
5081 IF (p_transaction_action_id = 3 AND g_firstscan = FALSE) THEN
5082 IF (l_debug = 1) THEN
5083 invtrace('p_transaction_action_id = 3 and g_firstscan is false');
5084 END IF;
5085 INSERT INTO mtl_unit_transactions
5086 (
5087 transaction_id
5088 , last_update_date
5089 , last_updated_by
5090 , creation_date
5091 , created_by
5092 , last_update_login
5093 , serial_number
5094 , inventory_item_id
5095 , organization_id
5096 , subinventory_code
5097 , locator_id
5098 , transaction_date
5099 , transaction_source_id
5100 , transaction_source_type_id
5101 , transaction_source_name
5102 , receipt_issue_type
5103 , customer_id
5104 , ship_id
5105 , serial_attribute_category
5106 , origination_date
5107 , c_attribute1
5108 , c_attribute2
5109 , c_attribute3
5110 , c_attribute4
5111 , c_attribute5
5112 , c_attribute6
5113 , c_attribute7
5114 , c_attribute8
5115 , c_attribute9
5116 , c_attribute10
5117 , c_attribute11
5118 , c_attribute12
5119 , c_attribute13
5120 , c_attribute14
5121 , c_attribute15
5122 , c_attribute16
5123 , c_attribute17
5124 , c_attribute18
5125 , c_attribute19
5126 , c_attribute20
5127 , d_attribute1
5128 , d_attribute2
5129 , d_attribute3
5130 , d_attribute4
5131 , d_attribute5
5132 , d_attribute6
5133 , d_attribute7
5134 , d_attribute8
5135 , d_attribute9
5136 , d_attribute10
5137 , n_attribute1
5138 , n_attribute2
5139 , n_attribute3
5140 , n_attribute4
5141 , n_attribute5
5142 , n_attribute6
5143 , n_attribute7
5144 , n_attribute8
5145 , n_attribute9
5146 , n_attribute10
5147 , status_id
5148 , territory_code
5149 , time_since_new
5150 , cycles_since_new
5151 , time_since_overhaul
5152 , cycles_since_overhaul
5153 , time_since_repair
5154 , cycles_since_repair
5155 , time_since_visit
5156 , cycles_since_visit
5157 , time_since_mark
5158 , cycles_since_mark
5159 , number_of_repairs
5160 )
5161 SELECT p_transaction_id
5162 , l_sys_date
5163 , l_userid
5164 , msn.creation_date
5165 , msn.created_by
5166 , l_loginid
5167 , msn.serial_number
5168 , p_inventory_item_id
5169 , p_organization_id
5170 , p_subinventory_code
5171 , p_current_locator_id
5172 , p_transaction_date
5173 , p_txn_src_id
5174 , p_txn_src_type_id
5175 , p_txn_src_name
5176 , p_receipt_issue_type
5177 , p_customer_id
5178 , p_ship_id
5179 , msn.serial_attribute_category
5180 , msn.origination_date
5181 , msn.c_attribute1
5182 , msn.c_attribute2
5183 , msn.c_attribute3
5184 , msn.c_attribute4
5185 , msn.c_attribute5
5186 , msn.c_attribute6
5187 , msn.c_attribute7
5188 , msn.c_attribute8
5189 , msn.c_attribute9
5190 , msn.c_attribute10
5191 , msn.c_attribute11
5192 , msn.c_attribute12
5193 , msn.c_attribute13
5194 , msn.c_attribute14
5195 , msn.c_attribute15
5196 , msn.c_attribute16
5197 , msn.c_attribute17
5198 , msn.c_attribute18
5199 , msn.c_attribute19
5200 , msn.c_attribute20
5201 , msn.d_attribute1
5202 , msn.d_attribute2
5203 , msn.d_attribute3
5204 , msn.d_attribute4
5205 , msn.d_attribute5
5206 , msn.d_attribute6
5207 , msn.d_attribute7
5208 , msn.d_attribute8
5209 , msn.d_attribute9
5210 , msn.d_attribute10
5211 , msn.n_attribute1
5212 , msn.n_attribute2
5213 , msn.n_attribute3
5214 , msn.n_attribute4
5215 , msn.n_attribute5
5216 , msn.n_attribute6
5217 , msn.n_attribute7
5218 , msn.n_attribute8
5219 , msn.n_attribute9
5220 , msn.n_attribute10
5221 , msn.status_id
5222 , msn.territory_code
5223 , msn.time_since_new
5224 , msn.cycles_since_new
5225 , msn.time_since_overhaul
5226 , msn.cycles_since_overhaul
5227 , msn.time_since_repair
5228 , msn.cycles_since_repair
5229 , msn.time_since_visit
5230 , msn.cycles_since_visit
5231 , msn.time_since_mark
5232 , msn.cycles_since_mark
5233 , msn.number_of_repairs
5234 FROM mtl_serial_numbers msn
5235 WHERE msn.serial_number between p_fm_serial_number and p_to_serial_number
5236 AND msn.current_organization_id = p_organization_id
5237 AND msn.inventory_item_id = p_inventory_item_id;
5238 /* Bug 2207912 */
5239 /* The following not exists statement is commented out
5240 ** because this part of the statement gets executed
5241 ** only for ORG TRANSFER and for the delivery side of
5242 ** of transaction as firstscan is false now, before this
5243 ** insert statement gets executed the mtl_serial_number is
5244 ** table is already updated with the organization_id of the
5245 ** delivered org and status from the TM, so there will be an entry always exist
5246 ** ing for the where condition specified in the exists clasue
5247 ** for mtl_serial_number table
5248 ** So the insert statement will always fail.
5249 */
5250 --and not exists
5251 -- ( select NULL
5252 -- from mtl_serial_numbers sn
5253 -- where sn.serial_number = p_serial_number
5254 -- and sn.current_organization_id = p_organization_id
5255 -- and sn.inventory_item_id = p_inventory_item_id);
5256 ELSE
5257 IF (l_debug = 1) THEN
5258 invtrace('not org transfer');
5259 END IF;
5260 IF l_wms_installed THEN
5261 /** 2756040 - Populate Serial Attribute Category info when it is not
5262 ** a receiving transaction **/
5263 IF ((g_serial_attributes_tbl(1).column_value = NULL)
5264 OR(p_transaction_action_id NOT IN(12, 27, 31))) THEN
5265 inv_lot_sel_attr.get_context_code(g_serial_attributes_tbl(1).column_value, p_organization_id, p_inventory_item_id
5266 , 'Serial Attributes');
5267 END IF;
5268 ELSE
5269 g_serial_attributes_tbl(1).column_value := NULL;
5270 END IF;
5271
5272 l_date2 := fnd_date.canonical_to_date(g_serial_attributes_tbl(2).COLUMN_VALUE);
5273 l_date23 := fnd_date.canonical_to_date(g_serial_attributes_tbl(23).COLUMN_VALUE);
5274 l_date24 := fnd_date.canonical_to_date(g_serial_attributes_tbl(24).COLUMN_VALUE);
5275 l_date25 := fnd_date.canonical_to_date(g_serial_attributes_tbl(25).COLUMN_VALUE);
5276 l_date26 := fnd_date.canonical_to_date(g_serial_attributes_tbl(26).COLUMN_VALUE);
5277 l_date27 := fnd_date.canonical_to_date(g_serial_attributes_tbl(27).COLUMN_VALUE);
5278 l_date28 := fnd_date.canonical_to_date(g_serial_attributes_tbl(28).COLUMN_VALUE);
5279 l_date29 := fnd_date.canonical_to_date(g_serial_attributes_tbl(29).COLUMN_VALUE);
5280 l_date30 := fnd_date.canonical_to_date(g_serial_attributes_tbl(30).COLUMN_VALUE);
5281 l_date31 := fnd_date.canonical_to_date(g_serial_attributes_tbl(31).COLUMN_VALUE);
5282 l_date32 := fnd_date.canonical_to_date(g_serial_attributes_tbl(32).COLUMN_VALUE);
5283 l_num33 := to_number(g_serial_attributes_tbl(33).COLUMN_VALUE);
5284 l_num34 := to_number(g_serial_attributes_tbl(34).COLUMN_VALUE);
5285 l_num35 := to_number(g_serial_attributes_tbl(35).COLUMN_VALUE);
5286 l_num36 := to_number(g_serial_attributes_tbl(36).COLUMN_VALUE);
5287 l_num37 := to_number(g_serial_attributes_tbl(37).COLUMN_VALUE);
5288 l_num38 := to_number(g_serial_attributes_tbl(38).COLUMN_VALUE);
5289 l_num39 := to_number(g_serial_attributes_tbl(39).COLUMN_VALUE);
5290 l_num40 := to_number(g_serial_attributes_tbl(40).COLUMN_VALUE);
5291 l_num41 := to_number(g_serial_attributes_tbl(41).COLUMN_VALUE);
5292 l_num42 := to_number(g_serial_attributes_tbl(42).COLUMN_VALUE);
5293
5294 IF (p_transaction_temp_id > 0) THEN
5295 --Bug 2067223 paranthesis are added in the where clause
5296 -- of the select statement
5297
5298 -- Bug 3772309: Removing this sql as this is not being used
5299 -- anywhere. for performance reason.
5300 /*****
5301 SELECT count(*)
5302 into l_upd_count
5303 FROM mtl_serial_numbers msn, mtl_serial_numbers_temp msnt
5304 WHERE msnt.transaction_temp_id = p_transaction_temp_id
5305 AND lpad(msn.serial_number, 30) between lpad(msnt.fm_serial_number,30) AND LPAD(NVL(msnt.to_serial_number, msnt.fm_serial_number),30)
5306 AND Lpad(msnt.fm_serial_number,30) = l_fm_serial_number
5307 AND Lpad(msnt.to_serial_number,30) = l_to_serial_number;
5308 IF (l_debug = 1) THEN
5309 invtrace('l_upd_count = ' || l_upd_count);
5310 end if;
5311 ****/
5312 IF (l_debug = 1) THEN
5313 invtrace('insert into mut with tempid = ' ||
5314 p_transaction_temp_id);
5315 END IF;
5316 -- Bug 3772309: Making some changes to the insert statement as it does full
5317 -- tablescan on MSN.
5318 IF (p_to_serial_number IS NOT NULL AND p_to_serial_number <>
5319 p_fm_serial_number) THEN
5320 IF (l_debug = 1) THEN
5321 invtrace('inside to_serial different from from_serial.');
5322 invtrace('from serial =. ' || p_fm_serial_number);
5323 invtrace('to serial =. ' || p_to_serial_number);
5324 END IF;
5325
5326 INSERT INTO mtl_unit_transactions
5327 (
5328 transaction_id
5329 , last_update_date
5330 , last_updated_by
5331 , creation_date
5332 , created_by
5333 , last_update_login
5334 , serial_number
5335 , inventory_item_id
5336 , organization_id
5337 , subinventory_code
5338 , locator_id
5339 , transaction_date
5340 , transaction_source_id
5341 , transaction_source_type_id
5342 , transaction_source_name
5343 , receipt_issue_type
5344 , customer_id
5345 , ship_id
5346 , serial_attribute_category
5347 , origination_date
5348 , c_attribute1
5349 , c_attribute2
5350 , c_attribute3
5351 , c_attribute4
5352 , c_attribute5
5353 , c_attribute6
5354 , c_attribute7
5355 , c_attribute8
5356 , c_attribute9
5357 , c_attribute10
5358 , c_attribute11
5359 , c_attribute12
5360 , c_attribute13
5361 , c_attribute14
5362 , c_attribute15
5363 , c_attribute16
5364 , c_attribute17
5365 , c_attribute18
5366 , c_attribute19
5367 , c_attribute20
5368 , d_attribute1
5369 , d_attribute2
5370 , d_attribute3
5371 , d_attribute4
5372 , d_attribute5
5373 , d_attribute6
5374 , d_attribute7
5375 , d_attribute8
5376 , d_attribute9
5377 , d_attribute10
5378 , n_attribute1
5379 , n_attribute2
5380 , n_attribute3
5381 , n_attribute4
5382 , n_attribute5
5383 , n_attribute6
5384 , n_attribute7
5385 , n_attribute8
5386 , n_attribute9
5387 , n_attribute10
5388 , status_id
5389 , territory_code
5390 , time_since_new
5391 , cycles_since_new
5392 , time_since_overhaul
5393 , cycles_since_overhaul
5394 , time_since_repair
5395 , cycles_since_repair
5396 , time_since_visit
5397 , cycles_since_visit
5398 , time_since_mark
5399 , cycles_since_mark
5400 , number_of_repairs
5401 , product_code
5402 , product_transaction_id
5403 , attribute_category
5404 , attribute1
5405 , attribute2
5406 , attribute3
5407 , attribute4
5408 , attribute5
5409 , attribute6
5410 , attribute7
5411 , attribute8
5412 , attribute9
5413 , attribute10
5414 , attribute11
5415 , attribute12
5416 , attribute13
5417 , attribute14
5418 , attribute15
5419 )
5420 SELECT p_transaction_id
5421 , l_sys_date
5422 , l_userid
5423 , l_sys_date
5424 , l_userid
5425 , l_loginid
5426 , msn.serial_number
5427 , p_inventory_item_id
5428 , p_organization_id
5429 , p_subinventory_code
5430 , p_current_locator_id
5431 , p_transaction_date
5432 , p_txn_src_id
5433 , p_txn_src_type_id
5434 , p_txn_src_name
5435 , p_receipt_issue_type
5436 , p_customer_id
5437 , p_ship_id
5438 , g_serial_attributes_tbl(1).column_value
5439 , l_date2
5440 , g_serial_attributes_tbl(3).column_value
5441 , g_serial_attributes_tbl(4).column_value
5442 , g_serial_attributes_tbl(5).column_value
5443 , g_serial_attributes_tbl(6).column_value
5444 , g_serial_attributes_tbl(7).column_value
5445 , g_serial_attributes_tbl(8).column_value
5446 , g_serial_attributes_tbl(9).column_value
5447 , g_serial_attributes_tbl(10).column_value
5448 , g_serial_attributes_tbl(11).column_value
5449 , g_serial_attributes_tbl(12).column_value
5450 , g_serial_attributes_tbl(13).column_value
5451 , g_serial_attributes_tbl(14).column_value
5452 , g_serial_attributes_tbl(15).column_value
5453 , g_serial_attributes_tbl(16).column_value
5454 , g_serial_attributes_tbl(17).column_value
5455 , g_serial_attributes_tbl(18).column_value
5456 , g_serial_attributes_tbl(19).column_value
5457 , g_serial_attributes_tbl(20).column_value
5458 , g_serial_attributes_tbl(21).column_value
5459 , g_serial_attributes_tbl(22).column_value
5460 , l_date23
5461 , l_date24
5462 , l_date25
5463 , l_date26
5464 , l_date27
5465 , l_date28
5466 , l_date29
5467 , l_date30
5468 , l_date31
5469 , l_date32
5470 , l_num33
5471 , l_num34
5472 , l_num35
5473 , l_num36
5474 , l_num37
5475 , l_num38
5476 , l_num39
5477 , l_num40
5478 , l_num41
5479 , l_num42
5480 , p_status_id
5481 , g_serial_attributes_tbl(44).column_value
5482 , l_time_since_new
5483 , l_cycles_since_new
5484 , l_time_since_overhaul
5485 , l_cycles_since_overhaul
5486 , l_time_since_repair
5487 , l_cycles_since_repair
5488 , l_time_since_visit
5489 , l_cycles_since_visit
5490 , l_time_since_mark
5491 , l_cycles_since_mark
5492 , l_number_of_repairs
5493 , msnt.product_code
5494 , msnt.product_transaction_id
5495 , msnt.attribute_category
5496 , msnt.attribute1
5497 , msnt.attribute2
5498 , msnt.attribute3
5499 , msnt.attribute4
5500 , msnt.attribute5
5501 , msnt.attribute6
5502 , msnt.attribute7
5503 , msnt.attribute8
5504 , msnt.attribute9
5505 , msnt.attribute10
5506 , msnt.attribute11
5507 , msnt.attribute12
5508 , msnt.attribute13
5509 , msnt.attribute14
5510 , msnt.attribute15
5511 FROM mtl_serial_numbers_temp msnt, mtl_serial_numbers msn
5512 WHERE msnt.transaction_temp_id = p_transaction_temp_id
5513 AND msn.current_organization_id = p_organization_id
5514 AND msn.inventory_item_id = p_inventory_item_id
5515 AND lpad(msn.serial_number,30) between lpad(msnt.fm_serial_number,30) AND lpad(msnt.to_serial_number,30)
5516 AND lpad(msnt.fm_serial_number,30) = l_fm_serial_number
5517 AND lpad(msnt.to_serial_number,30) = l_to_serial_number;
5518 /* Bug 3622025 -- Added the LPAD function in the above where clause */
5519
5520 l_upd_count := SQL%ROWCOUNT;
5521 IF (l_debug = 1) THEN
5522 invtrace('l_upd_count = ' || l_upd_count);
5523 END IF;
5524
5525 ELSE -- to_serial is either null or is the same as from serial
5526 IF (l_debug = 1) THEN
5527 invtrace('inside to_serial is either null or the same.');
5528 invtrace('from serial =. ' || p_fm_serial_number);
5529 invtrace('to serial =. ' || p_to_serial_number);
5530 END IF;
5531
5532 INSERT INTO mtl_unit_transactions
5533 (
5534 transaction_id
5535 , last_update_date
5536 , last_updated_by
5537 , creation_date
5538 , created_by
5539 , last_update_login
5540 , serial_number
5541 , inventory_item_id
5542 , organization_id
5543 , subinventory_code
5544 , locator_id
5545 , transaction_date
5546 , transaction_source_id
5547 , transaction_source_type_id
5548 , transaction_source_name
5549 , receipt_issue_type
5550 , customer_id
5551 , ship_id
5552 , serial_attribute_category
5553 , origination_date
5554 , c_attribute1
5555 , c_attribute2
5556 , c_attribute3
5557 , c_attribute4
5558 , c_attribute5
5559 , c_attribute6
5560 , c_attribute7
5561 , c_attribute8
5562 , c_attribute9
5563 , c_attribute10
5564 , c_attribute11
5565 , c_attribute12
5566 , c_attribute13
5567 , c_attribute14
5568 , c_attribute15
5569 , c_attribute16
5570 , c_attribute17
5571 , c_attribute18
5572 , c_attribute19
5573 , c_attribute20
5574 , d_attribute1
5575 , d_attribute2
5576 , d_attribute3
5577 , d_attribute4
5578 , d_attribute5
5579 , d_attribute6
5580 , d_attribute7
5581 , d_attribute8
5582 , d_attribute9
5583 , d_attribute10
5584 , n_attribute1
5585 , n_attribute2
5586 , n_attribute3
5587 , n_attribute4
5588 , n_attribute5
5589 , n_attribute6
5590 , n_attribute7
5591 , n_attribute8
5592 , n_attribute9
5593 , n_attribute10
5594 , status_id
5595 , territory_code
5596 , time_since_new
5597 , cycles_since_new
5598 , time_since_overhaul
5599 , cycles_since_overhaul
5600 , time_since_repair
5601 , cycles_since_repair
5602 , time_since_visit
5603 , cycles_since_visit
5604 , time_since_mark
5605 , cycles_since_mark
5606 , number_of_repairs
5607 , product_code
5608 , product_transaction_id
5609 , attribute_category
5610 , attribute1
5611 , attribute2
5612 , attribute3
5613 , attribute4
5614 , attribute5
5615 , attribute6
5616 , attribute7
5617 , attribute8
5618 , attribute9
5619 , attribute10
5620 , attribute11
5621 , attribute12
5622 , attribute13
5623 , attribute14
5624 , attribute15
5625 )
5626 SELECT p_transaction_id
5627 , l_sys_date
5628 , l_userid
5629 , l_sys_date
5630 , l_userid
5631 , l_loginid
5632 , msn.serial_number
5633 , p_inventory_item_id
5634 , p_organization_id
5635 , p_subinventory_code
5636 , p_current_locator_id
5637 , p_transaction_date
5638 , p_txn_src_id
5639 , p_txn_src_type_id
5640 , p_txn_src_name
5641 , p_receipt_issue_type
5642 , p_customer_id
5643 , p_ship_id
5644 , g_serial_attributes_tbl(1).column_value
5645 , l_date2
5646 , g_serial_attributes_tbl(3).column_value
5647 , g_serial_attributes_tbl(4).column_value
5648 , g_serial_attributes_tbl(5).column_value
5649 , g_serial_attributes_tbl(6).column_value
5650 , g_serial_attributes_tbl(7).column_value
5651 , g_serial_attributes_tbl(8).column_value
5652 , g_serial_attributes_tbl(9).column_value
5653 , g_serial_attributes_tbl(10).column_value
5654 , g_serial_attributes_tbl(11).column_value
5655 , g_serial_attributes_tbl(12).column_value
5656 , g_serial_attributes_tbl(13).column_value
5657 , g_serial_attributes_tbl(14).column_value
5658 , g_serial_attributes_tbl(15).column_value
5659 , g_serial_attributes_tbl(16).column_value
5660 , g_serial_attributes_tbl(17).column_value
5661 , g_serial_attributes_tbl(18).column_value
5662 , g_serial_attributes_tbl(19).column_value
5663 , g_serial_attributes_tbl(20).column_value
5664 , g_serial_attributes_tbl(21).column_value
5665 , g_serial_attributes_tbl(22).column_value
5666 , l_date23
5667 , l_date24
5668 , l_date25
5669 , l_date26
5670 , l_date27
5671 , l_date28
5672 , l_date29
5673 , l_date30
5674 , l_date31
5675 , l_date32
5676 , l_num33
5677 , l_num34
5678 , l_num35
5679 , l_num36
5680 , l_num37
5681 , l_num38
5682 , l_num39
5683 , l_num40
5684 , l_num41
5685 , l_num42
5686 , p_status_id
5687 , g_serial_attributes_tbl(44).column_value
5688 , l_time_since_new
5689 , l_cycles_since_new
5690 , l_time_since_overhaul
5691 , l_cycles_since_overhaul
5692 , l_time_since_repair
5693 , l_cycles_since_repair
5694 , l_time_since_visit
5695 , l_cycles_since_visit
5696 , l_time_since_mark
5697 , l_cycles_since_mark
5698 , l_number_of_repairs
5699 , msnt.product_code
5700 , msnt.product_transaction_id
5701 , msnt.attribute_category
5702 , msnt.attribute1
5703 , msnt.attribute2
5704 , msnt.attribute3
5705 , msnt.attribute4
5706 , msnt.attribute5
5707 , msnt.attribute6
5708 , msnt.attribute7
5709 , msnt.attribute8
5710 , msnt.attribute9
5711 , msnt.attribute10
5712 , msnt.attribute11
5713 , msnt.attribute12
5714 , msnt.attribute13
5715 , msnt.attribute14
5716 , msnt.attribute15
5717 FROM mtl_serial_numbers_temp msnt, mtl_serial_numbers msn
5718 WHERE msnt.transaction_temp_id = p_transaction_temp_id
5719 AND msn.current_organization_id = p_organization_id
5720 AND msn.inventory_item_id = p_inventory_item_id
5721 AND lpad(msn.serial_number,30) = lpad(msnt.fm_serial_number,30)
5722 AND lpad(msnt.fm_serial_number,30) = l_fm_serial_number;
5723 /* Bug 3622025 -- Added the LPAD function in the above where clause */
5724 END IF;
5725
5726 -- End changes to bug 3772309
5727 ELSE
5728 IF (l_debug = 1) THEN
5729 invtrace('no transaction temp id');
5730 END IF;
5731
5732 INSERT INTO mtl_unit_transactions
5733 (
5734 transaction_id
5735 , last_update_date
5736 , last_updated_by
5737 , creation_date
5738 , created_by
5739 , last_update_login
5740 , serial_number
5741 , inventory_item_id
5742 , organization_id
5743 , subinventory_code
5744 , locator_id
5745 , transaction_date
5746 , transaction_source_id
5747 , transaction_source_type_id
5748 , transaction_source_name
5749 , receipt_issue_type
5750 , customer_id
5751 , ship_id
5752 , serial_attribute_category
5753 , origination_date
5754 , c_attribute1
5755 , c_attribute2
5756 , c_attribute3
5757 , c_attribute4
5758 , c_attribute5
5759 , c_attribute6
5760 , c_attribute7
5761 , c_attribute8
5762 , c_attribute9
5763 , c_attribute10
5764 , c_attribute11
5765 , c_attribute12
5766 , c_attribute13
5767 , c_attribute14
5768 , c_attribute15
5769 , c_attribute16
5770 , c_attribute17
5771 , c_attribute18
5772 , c_attribute19
5773 , c_attribute20
5774 , d_attribute1
5775 , d_attribute2
5776 , d_attribute3
5777 , d_attribute4
5778 , d_attribute5
5779 , d_attribute6
5780 , d_attribute7
5781 , d_attribute8
5782 , d_attribute9
5783 , d_attribute10
5784 , n_attribute1
5785 , n_attribute2
5786 , n_attribute3
5787 , n_attribute4
5788 , n_attribute5
5789 , n_attribute6
5790 , n_attribute7
5791 , n_attribute8
5792 , n_attribute9
5793 , n_attribute10
5794 , status_id
5795 , territory_code
5796 , time_since_new
5797 , cycles_since_new
5798 , time_since_overhaul
5799 , cycles_since_overhaul
5800 , time_since_repair
5801 , cycles_since_repair
5802 , time_since_visit
5803 , cycles_since_visit
5804 , time_since_mark
5805 , cycles_since_mark
5806 , number_of_repairs
5807 )
5808 SELECT p_transaction_id
5809 , SYSDATE
5810 , l_userid
5811 , SYSDATE
5812 , l_userid
5813 , l_loginid
5814 , msn.serial_number
5815 , p_inventory_item_id
5816 , p_organization_id
5817 , p_subinventory_code
5818 , p_current_locator_id
5819 , p_transaction_date
5820 , p_txn_src_id
5821 , p_txn_src_type_id
5822 , p_txn_src_name
5823 , p_receipt_issue_type
5824 , p_customer_id
5825 , p_ship_id
5826 , g_serial_attributes_tbl(1).column_value
5827 , l_date2
5828 , g_serial_attributes_tbl(3).column_value
5829 , g_serial_attributes_tbl(4).column_value
5830 , g_serial_attributes_tbl(5).column_value
5831 , g_serial_attributes_tbl(6).column_value
5832 , g_serial_attributes_tbl(7).column_value
5833 , g_serial_attributes_tbl(8).column_value
5834 , g_serial_attributes_tbl(9).column_value
5835 , g_serial_attributes_tbl(10).column_value
5836 , g_serial_attributes_tbl(11).column_value
5837 , g_serial_attributes_tbl(12).column_value
5838 , g_serial_attributes_tbl(13).column_value
5839 , g_serial_attributes_tbl(14).column_value
5840 , g_serial_attributes_tbl(15).column_value
5841 , g_serial_attributes_tbl(16).column_value
5842 , g_serial_attributes_tbl(17).column_value
5843 , g_serial_attributes_tbl(18).column_value
5844 , g_serial_attributes_tbl(19).column_value
5845 , g_serial_attributes_tbl(20).column_value
5846 , g_serial_attributes_tbl(21).column_value
5847 , g_serial_attributes_tbl(22).column_value
5848 , l_date23
5849 , l_date24
5850 , l_date25
5851 , l_date26
5852 , l_date27
5853 , l_date28
5854 , l_date29
5855 , l_date30
5856 , l_date31
5857 , l_date32
5858 , l_num33
5859 , l_num34
5860 , l_num35
5861 , l_num36
5862 , l_num37
5863 , l_num38
5864 , l_num39
5865 , l_num40
5866 , l_num41
5867 , l_num42
5868 , p_status_id
5869 , g_serial_attributes_tbl(44).column_value
5870 , l_time_since_new
5871 , l_cycles_since_new
5872 , l_time_since_overhaul
5873 , l_cycles_since_overhaul
5874 , l_time_since_repair
5875 , l_cycles_since_repair
5876 , l_time_since_visit
5877 , l_cycles_since_visit
5878 , l_time_since_mark
5879 , l_cycles_since_mark
5880 , l_number_of_repairs
5881 FROM mtl_serial_numbers msn
5882 WHERE inventory_item_id = p_inventory_item_id
5883 AND serial_number between p_fm_serial_number AND p_to_serial_number;
5884 END IF;
5885 END IF;
5886
5887 /*bug 2756040 Update MSN also with values from MSNT in case of
5888 receipt transaction or intransit receipt txn
5889 (transaction_action_id = 12 or 27) */
5890 IF (p_transaction_action_id IN(12, 27, 31)) THEN
5891 IF (l_debug = 1) THEN
5892 invtrace('transaction_action_id = ' || p_transaction_action_id
5893 || ' org _id ' || p_organization_id || 'item ' ||
5894 p_inventory_item_id);
5895 END IF;
5896 BEGIN
5897 UPDATE mtl_serial_numbers
5898 SET serial_attribute_category = g_serial_attributes_tbl(1).column_value
5899 , origination_date = l_date2
5900 , c_attribute1 = g_serial_attributes_tbl(3).column_value
5901 , c_attribute2 = g_serial_attributes_tbl(4).column_value
5902 , c_attribute3 = g_serial_attributes_tbl(5).column_value
5903 , c_attribute4 = g_serial_attributes_tbl(6).column_value
5904 , c_attribute5 = g_serial_attributes_tbl(7).column_value
5905 , c_attribute6 = g_serial_attributes_tbl(8).column_value
5906 , c_attribute7 = g_serial_attributes_tbl(9).column_value
5907 , c_attribute8 = g_serial_attributes_tbl(10).column_value
5908 , c_attribute9 = g_serial_attributes_tbl(11).column_value
5909 , c_attribute10 = g_serial_attributes_tbl(12).column_value
5910 , c_attribute11 = g_serial_attributes_tbl(13).column_value
5911 , c_attribute12 = g_serial_attributes_tbl(14).column_value
5912 , c_attribute13 = g_serial_attributes_tbl(15).column_value
5913 , c_attribute14 = g_serial_attributes_tbl(16).column_value
5914 , c_attribute15 = g_serial_attributes_tbl(17).column_value
5915 , c_attribute16 = g_serial_attributes_tbl(18).column_value
5916 , c_attribute17 = g_serial_attributes_tbl(19).column_value
5917 , c_attribute18 = g_serial_attributes_tbl(20).column_value
5918 , c_attribute19 = g_serial_attributes_tbl(21).column_value
5919 , c_attribute20 = g_serial_attributes_tbl(22).column_value
5920 , d_attribute1 = l_date23
5921 , d_attribute2 = l_date24
5922 , d_attribute3 = l_date25
5923 , d_attribute4 = l_date26
5924 , d_attribute5 = l_date27
5925 , d_attribute6 = l_date28
5926 , d_attribute7 = l_date29
5927 , d_attribute8 = l_date30
5928 , d_attribute9 = l_date31
5929 , d_attribute10 = l_date32
5930 , n_attribute1 = l_num33
5931 , n_attribute2 = l_num34
5932 , n_attribute3 = l_num35
5933 , n_attribute4 = l_num36
5934 , n_attribute5 = l_num37
5935 , n_attribute6 = l_num38
5936 , n_attribute7 = l_num39
5937 , n_attribute8 = l_num40
5938 , n_attribute9 = l_num41
5939 , n_attribute10 = l_num42
5940 WHERE serial_number between p_fm_serial_number and p_to_serial_number
5941 AND inventory_item_id = p_inventory_item_id
5942 AND current_organization_id = p_organization_id;
5943
5944 IF (l_debug = 1) THEN
5945 invtrace('updating MSN with values ');
5946 invtrace('serial_attribute_category ' || g_serial_attributes_tbl(1).column_value);
5947 invtrace('origination_date ' || g_serial_attributes_tbl(2).column_value);
5948 invtrace(' C_ATTRIBUTE1 = ' || g_serial_attributes_tbl(3).column_value);
5949 invtrace('C_ATTRIBUTE2 = ' || g_serial_attributes_tbl(4).column_value);
5950 invtrace('C_ATTRIBUTE3 = ' || g_serial_attributes_tbl(5).column_value);
5951 invtrace('C_ATTRIBUTE4 = ' || g_serial_attributes_tbl(6).column_value);
5952 invtrace('C_ATTRIBUTE5 = ' || g_serial_attributes_tbl(7).column_value);
5953 invtrace('C_ATTRIBUTE6 = ' || g_serial_attributes_tbl(8).column_value);
5954 invtrace('C_ATTRIBUTE7 = ' || g_serial_attributes_tbl(9).column_value);
5955 invtrace('C_ATTRIBUTE8 = ' || g_serial_attributes_tbl(10).column_value);
5956 invtrace('C_ATTRIBUTE9 = ' || g_serial_attributes_tbl(11).column_value);
5957 invtrace('C_ATTRIBUTE10 = ' || g_serial_attributes_tbl(12).column_value);
5958 invtrace('C_ATTRIBUTE11 = ' || g_serial_attributes_tbl(13).column_value);
5959 invtrace('C_ATTRIBUTE12 = ' || g_serial_attributes_tbl(14).column_value);
5960 invtrace('C_ATTRIBUTE13 = ' || g_serial_attributes_tbl(15).column_value);
5961 invtrace('C_ATTRIBUTE14 = ' || g_serial_attributes_tbl(16).column_value);
5962 invtrace('C_ATTRIBUTE15 = ' || g_serial_attributes_tbl(17).column_value);
5963 invtrace('C_ATTRIBUTE16 = ' || g_serial_attributes_tbl(18).column_value);
5964 invtrace('C_ATTRIBUTE17 = ' || g_serial_attributes_tbl(19).column_value);
5965 invtrace('C_ATTRIBUTE18 = ' || g_serial_attributes_tbl(20).column_value);
5966 invtrace('C_ATTRIBUTE19 = ' || g_serial_attributes_tbl(21).column_value);
5967 invtrace('C_ATTRIBUTE20 = ' || g_serial_attributes_tbl(22).column_value);
5968 invtrace('D_ATTRIBUTE1 = ' || g_serial_attributes_tbl(23).column_value);
5969 invtrace('D_ATTRIBUTE2 = ' || g_serial_attributes_tbl(24).column_value);
5970 invtrace('D_ATTRIBUTE3 = ' || g_serial_attributes_tbl(25).column_value);
5971 invtrace('D_ATTRIBUTE4 = ' || g_serial_attributes_tbl(26).column_value);
5972 invtrace('D_ATTRIBUTE5 = ' || g_serial_attributes_tbl(27).column_value);
5973 invtrace('D_ATTRIBUTE6 = ' || g_serial_attributes_tbl(28).column_value);
5974 invtrace('D_ATTRIBUTE7 = ' || g_serial_attributes_tbl(29).column_value);
5975 invtrace('D_ATTRIBUTE8 = ' || g_serial_attributes_tbl(30).column_value);
5976 invtrace('D_ATTRIBUTE9 = ' || g_serial_attributes_tbl(31).column_value);
5977 invtrace('D_ATTRIBUTE10 = ' || g_serial_attributes_tbl(32).column_value);
5978 invtrace('N_ATTRIBUTE1 = ' || g_serial_attributes_tbl(33).column_value);
5979 invtrace('N_ATTRIBUTE2 = ' || g_serial_attributes_tbl(34).column_value);
5980 invtrace('N_ATTRIBUTE3 = ' || g_serial_attributes_tbl(35).column_value);
5981 invtrace('N_ATTRIBUTE4 = ' || g_serial_attributes_tbl(36).column_value);
5982 invtrace('N_ATTRIBUTE5 = ' || g_serial_attributes_tbl(37).column_value);
5983 invtrace('N_ATTRIBUTE6 = ' || g_serial_attributes_tbl(38).column_value);
5984 invtrace('N_ATTRIBUTE7 = ' || g_serial_attributes_tbl(39).column_value);
5985 invtrace('N_ATTRIBUTE8 = ' || g_serial_attributes_tbl(40).column_value);
5986 invtrace('N_ATTRIBUTE9 = ' || g_serial_attributes_tbl(41).column_value);
5987 invtrace('N_ATTRIBUTE10 = ' || g_serial_attributes_tbl(42).column_value);
5988 invtrace(' for the serial ' || p_fm_serial_number);
5989 END IF;
5990 EXCEPTION
5991 WHEN NO_DATA_FOUND THEN
5992 IF (l_debug = 1) THEN
5993 invtrace('no data found while updating msn');
5994 END IF;
5995
5996 WHEN OTHERS THEN
5997 IF (l_debug = 1) THEN
5998 invtrace('some other error' || SQLERRM);
5999 END IF;
6000
6001 END;
6002 END IF;
6003
6004 x_return_status := fnd_api.g_ret_sts_success;
6005 -- End of API body.
6006 -- Standard check of p_commit.
6007 IF fnd_api.to_boolean(p_commit) THEN
6008 COMMIT WORK;
6009 END IF;
6010
6011 -- Standard call to get message count and if count is 1, get message info.
6012 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => l_msg_data);
6013 x_msg_data := SUBSTR(l_msg_data, 0, 198);
6014 EXCEPTION
6015 WHEN OTHERS THEN
6016 ROLLBACK TO apiinsertserial_apipub;
6017
6018 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
6019 fnd_msg_pub.add_exc_msg('INV_SERIAL_NUMBER_PUB', 'insertRangeUnitTrx');
6020 END IF;
6021
6022 x_return_status := fnd_api.g_ret_sts_unexp_error;
6023 END insertRangeUnitTrx;
6024
6025 END inv_serial_number_pub;