[Home] [Help]
PACKAGE BODY: APPS.INV_LOT_TRX_VALIDATION_PVT
Source
1 PACKAGE BODY inv_lot_trx_validation_pvt AS
2 /* $Header: INVVLTVB.pls 120.10 2006/07/26 11:39:03 pmadadi noship $ */
3
4 /*Bug:5354721. The following table holds the name, value type and length of all the
5 lot Attributes of MTL_LOT_NUMBERS table which will then be used in
6 get_lot_att_record procedure to populate the values for the corresponding columns.
7 */
8
9 g_lot_attr_tbl inv_lot_sel_attr.lot_sel_attributes_tbl_type;
10
11 /*Bug 5354721. Building the following statement to get lot attributes from MTLI or MLN. */
12 g_select_stmt LONG :=
13 'SELECT
14 NVL(MTLI.ATTRIBUTE_CATEGORY , MLN.ATTRIBUTE_CATEGORY ),
15 NVL(MTLI.ATTRIBUTE1 , MLN.ATTRIBUTE1 ),
16 NVL(MTLI.ATTRIBUTE2 , MLN.ATTRIBUTE2 ),
17 NVL(MTLI.ATTRIBUTE3 , MLN.ATTRIBUTE3 ),
18 NVL(MTLI.ATTRIBUTE4 , MLN.ATTRIBUTE4 ),
19 NVL(MTLI.ATTRIBUTE5 , MLN.ATTRIBUTE5 ),
20 NVL(MTLI.ATTRIBUTE6 , MLN.ATTRIBUTE6 ),
21 NVL(MTLI.ATTRIBUTE7 , MLN.ATTRIBUTE7 ),
22 NVL(MTLI.ATTRIBUTE8 , MLN.ATTRIBUTE8 ),
23 NVL(MTLI.ATTRIBUTE9 , MLN.ATTRIBUTE9 ),
24 NVL(MTLI.ATTRIBUTE10 , MLN.ATTRIBUTE10 ),
25 NVL(MTLI.ATTRIBUTE11 , MLN.ATTRIBUTE11 ),
26 NVL(MTLI.ATTRIBUTE12 , MLN.ATTRIBUTE12 ),
27 NVL(MTLI.ATTRIBUTE13 , MLN.ATTRIBUTE13 ),
28 NVL(MTLI.ATTRIBUTE14 , MLN.ATTRIBUTE14 ),
29 NVL(MTLI.ATTRIBUTE15 , MLN.ATTRIBUTE15 ),
30 NVL(MTLI.DESCRIPTION , MLN.DESCRIPTION ),
31 NVL(MTLI.VENDOR_NAME , MLN.VENDOR_NAME ),
32 NVL(MTLI.DATE_CODE , MLN.DATE_CODE ),
33 NVL(MTLI.CHANGE_DATE , MLN.CHANGE_DATE ),
34 NVL(MTLI.AGE , MLN.AGE ),
35 NVL(MTLI.LOT_ATTRIBUTE_CATEGORY , MLN.LOT_ATTRIBUTE_CATEGORY ),
36 NVL(MTLI.ITEM_SIZE , MLN.ITEM_SIZE ),
37 NVL(MTLI.COLOR , MLN.COLOR ),
38 NVL(MTLI.VOLUME , MLN.VOLUME ),
39 NVL(MTLI.VOLUME_UOM , MLN.VOLUME_UOM ),
40 NVL(MTLI.PLACE_OF_ORIGIN , MLN.PLACE_OF_ORIGIN ),
41 NVL(MTLI.BEST_BY_DATE , MLN.BEST_BY_DATE ),
42 NVL(MTLI.LENGTH , MLN.LENGTH ),
43 NVL(MTLI.LENGTH_UOM , MLN.LENGTH_UOM ),
44 NVL(MTLI.RECYCLED_CONTENT , MLN.RECYCLED_CONTENT ),
45 NVL(MTLI.THICKNESS , MLN.THICKNESS ),
46 NVL(MTLI.THICKNESS_UOM , MLN.THICKNESS_UOM ),
47 NVL(MTLI.WIDTH , MLN.WIDTH ),
48 NVL(MTLI.WIDTH_UOM , MLN.WIDTH_UOM ),
49 NVL(MTLI.CURL_WRINKLE_FOLD , MLN.CURL_WRINKLE_FOLD ),
50 NVL(MTLI.C_ATTRIBUTE1 , MLN.C_ATTRIBUTE1 ),
51 NVL(MTLI.C_ATTRIBUTE2 , MLN.C_ATTRIBUTE2 ),
52 NVL(MTLI.C_ATTRIBUTE3 , MLN.C_ATTRIBUTE3 ),
53 NVL(MTLI.C_ATTRIBUTE4 , MLN.C_ATTRIBUTE4 ),
54 NVL(MTLI.C_ATTRIBUTE5 , MLN.C_ATTRIBUTE5 ),
55 NVL(MTLI.C_ATTRIBUTE6 , MLN.C_ATTRIBUTE6 ),
56 NVL(MTLI.C_ATTRIBUTE7 , MLN.C_ATTRIBUTE7 ),
57 NVL(MTLI.C_ATTRIBUTE8 , MLN.C_ATTRIBUTE8 ),
58 NVL(MTLI.C_ATTRIBUTE9 , MLN.C_ATTRIBUTE9 ),
59 NVL(MTLI.C_ATTRIBUTE10 , MLN.C_ATTRIBUTE10 ),
60 NVL(MTLI.C_ATTRIBUTE11 , MLN.C_ATTRIBUTE11 ),
61 NVL(MTLI.C_ATTRIBUTE12 , MLN.C_ATTRIBUTE12 ),
62 NVL(MTLI.C_ATTRIBUTE13 , MLN.C_ATTRIBUTE13 ),
63 NVL(MTLI.C_ATTRIBUTE14 , MLN.C_ATTRIBUTE14 ),
64 NVL(MTLI.C_ATTRIBUTE15 , MLN.C_ATTRIBUTE15 ),
65 NVL(MTLI.C_ATTRIBUTE16 , MLN.C_ATTRIBUTE16 ),
66 NVL(MTLI.C_ATTRIBUTE17 , MLN.C_ATTRIBUTE17 ),
67 NVL(MTLI.C_ATTRIBUTE18 , MLN.C_ATTRIBUTE18 ),
68 NVL(MTLI.C_ATTRIBUTE19 , MLN.C_ATTRIBUTE19 ),
69 NVL(MTLI.C_ATTRIBUTE20 , MLN.C_ATTRIBUTE20 ),
70 NVL(MTLI.D_ATTRIBUTE1 , MLN.D_ATTRIBUTE1 ),
71 NVL(MTLI.D_ATTRIBUTE2 , MLN.D_ATTRIBUTE2 ),
72 NVL(MTLI.D_ATTRIBUTE3 , MLN.D_ATTRIBUTE3 ),
73 NVL(MTLI.D_ATTRIBUTE4 , MLN.D_ATTRIBUTE4 ),
74 NVL(MTLI.D_ATTRIBUTE5 , MLN.D_ATTRIBUTE5 ),
75 NVL(MTLI.D_ATTRIBUTE6 , MLN.D_ATTRIBUTE6 ),
76 NVL(MTLI.D_ATTRIBUTE7 , MLN.D_ATTRIBUTE7 ),
77 NVL(MTLI.D_ATTRIBUTE8 , MLN.D_ATTRIBUTE8 ),
78 NVL(MTLI.D_ATTRIBUTE9 , MLN.D_ATTRIBUTE9 ),
79 NVL(MTLI.D_ATTRIBUTE10 , MLN.D_ATTRIBUTE10 ),
80 NVL(MTLI.N_ATTRIBUTE1 , MLN.N_ATTRIBUTE1 ),
81 NVL(MTLI.N_ATTRIBUTE2 , MLN.N_ATTRIBUTE2 ),
82 NVL(MTLI.N_ATTRIBUTE3 , MLN.N_ATTRIBUTE3 ),
83 NVL(MTLI.N_ATTRIBUTE4 , MLN.N_ATTRIBUTE4 ),
84 NVL(MTLI.N_ATTRIBUTE5 , MLN.N_ATTRIBUTE5 ),
85 NVL(MTLI.N_ATTRIBUTE6 , MLN.N_ATTRIBUTE6 ),
86 NVL(MTLI.N_ATTRIBUTE7 , MLN.N_ATTRIBUTE7 ),
87 NVL(MTLI.N_ATTRIBUTE8 , MLN.N_ATTRIBUTE8 ),
88 NVL(MTLI.N_ATTRIBUTE9 , MLN.N_ATTRIBUTE9 ),
89 NVL(MTLI.N_ATTRIBUTE10 , MLN.N_ATTRIBUTE10 ),
90 NVL(MTLI.VENDOR_ID , MLN.VENDOR_ID ),
91 NVL(MTLI.TERRITORY_CODE , MLN.TERRITORY_CODE )
92 ';
93
94 PROCEDURE print_debug (p_message IN VARCHAR2, p_module IN VARCHAR2)
95 IS
96 l_debug NUMBER := NVL (fnd_profile.VALUE ('INV_DEBUG_TRACE'), 0);
97 --PRAGMA AUTONOMOUS_TRANSACTION;
98 BEGIN
99 --dbms_output.put_line(g_pkg_name||'.'||p_module||': ' || p_message);
100 IF (l_debug = 1)
101 THEN
102 --INSERT INTO abhi(log, pkg, module) VALUES(p_message, g_pkg_name, p_module);
103 --COMMIT;
104 inv_log_util.TRACE (p_message, g_pkg_name || '.' || p_module, 9);
105 END IF;
106 END print_debug;
107
108
109 /***************************************************************************
110 Fetch the records from Interface tables and populate
111 the tables which are later used for validations purpose.
112
113 Added following for serial controlled items:-
114
115 If the item is serial controlled Then
116 If lot split and merge Then
117 get the values for the st_ser_num_tbl etc from MSNI
118 Else If lot Translate Then
119 get the Serial Numbers belonging to the lot item org combo and
120 populate the tables.
121 End If;
122
123 *****************************************************************************/
124 PROCEDURE populate_records (
125 x_validation_status OUT NOCOPY VARCHAR2
126 , x_return_status OUT NOCOPY VARCHAR2
127 , x_st_interface_id_tbl OUT NOCOPY inv_lot_trx_validation_pub.number_table
128 , x_st_item_id_tbl OUT NOCOPY inv_lot_trx_validation_pub.number_table
129 , x_st_org_id_tbl OUT NOCOPY inv_lot_trx_validation_pub.number_table
130 , x_st_revision_tbl OUT NOCOPY inv_lot_trx_validation_pub.revision_table
131 , x_st_sub_code_tbl OUT NOCOPY inv_lot_trx_validation_pub.sub_code_table
132 , x_st_locator_id_tbl OUT NOCOPY inv_lot_trx_validation_pub.number_table
133 , x_st_lot_num_tbl OUT NOCOPY inv_lot_trx_validation_pub.lot_number_table
134 ,
135 --Support for Lot Serial
136 x_st_ser_num_tbl OUT NOCOPY inv_lot_trx_validation_pub.serial_number_table
137 , x_st_ser_parent_lot_tbl OUT NOCOPY inv_lot_trx_validation_pub.parent_lot_table
138 , x_rs_ser_parent_lot_tbl OUT NOCOPY inv_lot_trx_validation_pub.parent_lot_table
139 , x_rs_ser_num_tbl OUT NOCOPY inv_lot_trx_validation_pub.serial_number_table
140 , x_st_ser_status_tbl OUT NOCOPY inv_lot_trx_validation_pub.number_table
141 , x_rs_ser_status_tbl OUT NOCOPY inv_lot_trx_validation_pub.number_table
142 , x_st_ser_grp_mark_id_tbl OUT NOCOPY inv_lot_trx_validation_pub.number_table
143 , x_rs_ser_grp_mark_id_tbl OUT NOCOPY inv_lot_trx_validation_pub.number_table
144 , x_st_ser_parent_sub_tbl OUT NOCOPY inv_lot_trx_validation_pub.parent_sub_table
145 , x_st_ser_parent_loc_tbl OUT NOCOPY inv_lot_trx_validation_pub.parent_loc_table
146 ,
147 --Support for Lot Serial
148 x_st_lpn_id_tbl OUT NOCOPY inv_lot_trx_validation_pub.number_table
149 , x_st_quantity_tbl OUT NOCOPY inv_lot_trx_validation_pub.number_table
150 , x_st_cost_group_tbl OUT NOCOPY inv_lot_trx_validation_pub.number_table
151 , x_st_uom_tbl OUT NOCOPY inv_lot_trx_validation_pub.uom_table
152 , x_st_status_id_tbl OUT NOCOPY inv_lot_trx_validation_pub.number_table
153 , x_rs_interface_id_tbl OUT NOCOPY inv_lot_trx_validation_pub.number_table
154 , x_rs_item_id_tbl OUT NOCOPY inv_lot_trx_validation_pub.number_table
155 , x_rs_org_id_tbl OUT NOCOPY inv_lot_trx_validation_pub.number_table
156 , x_rs_revision_tbl OUT NOCOPY inv_lot_trx_validation_pub.revision_table
157 , x_rs_sub_code_tbl OUT NOCOPY inv_lot_trx_validation_pub.sub_code_table
158 , x_rs_locator_id_tbl OUT NOCOPY inv_lot_trx_validation_pub.number_table
159 , x_rs_lot_num_tbl OUT NOCOPY inv_lot_trx_validation_pub.lot_number_table
160 , x_rs_lpn_id_tbl OUT NOCOPY inv_lot_trx_validation_pub.number_table
161 , x_rs_quantity_tbl OUT NOCOPY inv_lot_trx_validation_pub.number_table
162 , x_rs_cost_group_tbl OUT NOCOPY inv_lot_trx_validation_pub.number_table
163 , x_rs_uom_tbl OUT NOCOPY inv_lot_trx_validation_pub.uom_table
164 , x_rs_status_id_tbl OUT NOCOPY inv_lot_trx_validation_pub.number_table
165 , x_st_lot_exp_tbl OUT NOCOPY inv_lot_trx_validation_pub.date_table
166 , x_rs_lot_exp_tbl OUT NOCOPY inv_lot_trx_validation_pub.date_table
167 , x_transaction_type_id OUT NOCOPY NUMBER
168 , x_acct_period_tbl OUT NOCOPY inv_lot_trx_validation_pub.number_table
169 , x_st_dist_account_id OUT NOCOPY NUMBER
170 , x_rs_dist_account_id OUT NOCOPY NUMBER
171 , p_parent_id IN NUMBER
172 )
173 IS
174 CURSOR mti_csr (p_parent_id NUMBER)
175 IS
176 SELECT transaction_interface_id
177 , inventory_item_id
178 , revision
179 , organization_id
180 , transaction_quantity
181 , primary_quantity
182 , transaction_uom
183 , subinventory_code
184 , locator_id
185 , transaction_type_id
186 , transaction_action_id
187 , acct_period_id
188 , distribution_account_id
189 , transfer_subinventory
190 , transfer_organization
191 , transfer_locator
192 , parent_id
193 , cost_group_id
194 , transfer_cost_group_id
195 , lpn_id
196 , transfer_lpn_id
197 FROM mtl_transactions_interface
198 WHERE parent_id = p_parent_id;
199
200 CURSOR mtli_csr (p_transaction_interface_id NUMBER)
201 IS
202 SELECT transaction_interface_id
203 , lot_number
204 , lot_expiration_date
205 , transaction_quantity
206 , primary_quantity
207 , grade_code
208 , origination_date
209 , date_code
210 , status_id
211 , change_date
212 , age
213 , retest_date
214 , maturity_date
215 , lot_attribute_category
216 , item_size
217 , color
218 , volume
219 , volume_uom
220 , place_of_origin
221 , best_by_date
222 , LENGTH
223 , length_uom
224 , recycled_content
225 , thickness
226 , thickness_uom
227 , width
228 , width_uom
229 , curl_wrinkle_fold
230 , c_attribute1
231 , c_attribute2
232 , c_attribute3
233 , c_attribute4
234 , c_attribute5
235 , c_attribute6
236 , c_attribute7
237 , c_attribute8
238 , c_attribute9
239 , c_attribute10
240 , c_attribute11
241 , c_attribute12
242 , c_attribute13
243 , c_attribute14
244 , c_attribute15
245 , c_attribute16
246 , c_attribute17
247 , c_attribute18
248 , c_attribute19
249 , c_attribute20
250 , d_attribute1
251 , d_attribute2
252 , d_attribute3
253 , d_attribute4
254 , d_attribute5
255 , d_attribute6
256 , d_attribute7
257 , d_attribute8
258 , d_attribute9
259 , d_attribute10
260 , n_attribute1
261 , n_attribute2
262 , n_attribute3
263 , n_attribute4
264 , n_attribute5
265 , n_attribute6
266 , n_attribute7
267 , n_attribute8
268 , n_attribute10
269 , supplier_lot_number
270 , n_attribute9
271 , territory_code
272 , serial_transaction_temp_id
273 FROM mtl_transaction_lots_interface
274 WHERE transaction_interface_id = p_transaction_interface_id;
275
276 /*Support for Lot Serial:
277 *this cursor is to be used in case of the lot split and merge transactions and not for lot
278 *translate transaction
279 */
280 CURSOR msni_csr (p_serial_transaction_temp_id IN NUMBER)
281 IS
282 SELECT fm_serial_number
283 , NVL (to_serial_number, fm_serial_number) to_serial_number
284 FROM mtl_serial_numbers_interface
285 WHERE transaction_interface_id = p_serial_transaction_temp_id;
286
287 l_ser_csr msni_csr%ROWTYPE;
288
289
290 CURSOR per_serial_msn_csr (
291 p_serial_number IN VARCHAR2
292 , p_organization_id IN NUMBER
293 , p_inventory_item_id IN NUMBER
294 )
295 IS
296 SELECT group_mark_id
297 , status_id
298 FROM mtl_serial_numbers
299 WHERE serial_number = p_serial_number
300 AND current_organization_id = p_organization_id
301 AND inventory_item_id = p_inventory_item_id;
302
303
304 CURSOR per_serial_msn_src_csr (
305 p_serial_number IN VARCHAR2
306 , p_organization_id IN NUMBER
307 , p_inventory_item_id IN NUMBER
308 , p_lot_number IN VARCHAR2
309 , p_subinventory_code IN VARCHAR2
310 , p_locator_id IN NUMBER
311 , p_lpn_id IN NUMBER
312 , p_revision IN VARCHAR2
313 )
314 IS
315 SELECT group_mark_id
316 , status_id
317 FROM mtl_serial_numbers
318 WHERE serial_number = p_serial_number
319 AND current_organization_id = p_organization_id
320 AND inventory_item_id = p_inventory_item_id
321 AND lot_number = p_lot_number
322 AND current_subinventory_code = p_subinventory_code
323 AND nvl(current_locator_id, -9999) = nvl(p_locator_id, -9999)
324 AND nvl(lpn_id, -9999) = nvl(p_lpn_id, -9999)
325 AND nvl(revision, '@#') = nvl(p_revision, '@#')
326 AND current_status in (1,3,6)
327 AND reservation_id IS NULL;
328
329
330 /*Support for Lot Serial:
331 *This cursor is to be used for lot translate transaction. Here we have to get all the serial for
332 *that item ,lot, sub and locator combination from the mtl_serial_nunbers table.
333 */
334 CURSOR msn_csr (
335 p_lot_number IN VARCHAR2
336 , p_inventory_item_id IN NUMBER
337 , p_subinventory_code IN VARCHAR2
338 , p_locator_id IN NUMBER
339 , p_organization_id IN NUMBER
340 , p_lpn_id IN NUMBER
341 , p_revision IN VARCHAR2
342 )
343 IS
344 SELECT serial_number
345 , status_id
346 , group_mark_id
347 FROM mtl_serial_numbers
348 WHERE lot_number = p_lot_number
349 AND current_organization_id = p_organization_id
350 AND inventory_item_id = p_inventory_item_id
351 AND current_subinventory_code = p_subinventory_code
352 AND nvl(current_locator_id, -9999) = nvl(p_locator_id, -9999)
353 AND nvl(lpn_id, -9999) = nvl(p_lpn_id , -9999)
354 AND nvl(revision, '@#') = nvl(p_revision, '@#')
355 AND current_status IN (1,3,6)
356 AND reservation_id IS NULL;
357
358 l_ser_msn_csr msn_csr%ROWTYPE;
359 l_transaction_type_id NUMBER;
360 l_transaction_interface_id NUMBER;
361 l_transaction_action_id NUMBER;
362 l_st_item_id_tbl inv_lot_trx_validation_pub.number_table;
363 l_st_org_id_tbl inv_lot_trx_validation_pub.number_table;
364 l_st_revision_tbl inv_lot_trx_validation_pub.revision_table;
365 l_st_quantity_tbl inv_lot_trx_validation_pub.number_table;
366 l_st_primary_quantity_tbl inv_lot_trx_validation_pub.number_table;
367 l_st_uom_tbl inv_lot_trx_validation_pub.uom_table;
368 l_st_locator_id_tbl inv_lot_trx_validation_pub.number_table;
369 l_st_sub_code_tbl inv_lot_trx_validation_pub.sub_code_table;
370 l_st_cost_group_id_tbl inv_lot_trx_validation_pub.number_table;
371 l_st_lpn_id_tbl inv_lot_trx_validation_pub.number_table;
372 l_rs_item_id_tbl inv_lot_trx_validation_pub.number_table;
373 l_rs_org_id_tbl inv_lot_trx_validation_pub.number_table;
374 l_rs_revision_tbl inv_lot_trx_validation_pub.revision_table;
375 l_rs_quantity_tbl inv_lot_trx_validation_pub.number_table;
376 l_rs_primary_quantity_tbl inv_lot_trx_validation_pub.number_table;
377 l_rs_uom_tbl inv_lot_trx_validation_pub.uom_table;
378 l_rs_locator_id_tbl inv_lot_trx_validation_pub.number_table;
379 l_rs_sub_code_tbl inv_lot_trx_validation_pub.sub_code_table;
380 l_rs_lpn_id_tbl inv_lot_trx_validation_pub.number_table;
381 l_rs_cost_group_id_tbl inv_lot_trx_validation_pub.number_table;
382 l_st_lot_number_tbl inv_lot_trx_validation_pub.lot_number_table;
383 l_rs_lot_number_tbl inv_lot_trx_validation_pub.lot_number_table;
384 l_st_ser_number_tbl inv_lot_trx_validation_pub.serial_number_table;
385 l_st_ser_parent_lot_tbl inv_lot_trx_validation_pub.parent_lot_table;
386 l_rs_ser_parent_lot_tbl inv_lot_trx_validation_pub.parent_lot_table;
387 l_rs_ser_number_tbl inv_lot_trx_validation_pub.serial_number_table;
388 l_st_ser_status_tbl inv_lot_trx_validation_pub.number_table;
389 l_rs_ser_status_tbl inv_lot_trx_validation_pub.number_table;
390 l_st_ser_group_mark_id_tbl inv_lot_trx_validation_pub.number_table;
391 l_rs_ser_group_mark_id_tbl inv_lot_trx_validation_pub.number_table;
392 l_st_ser_parent_sub_tbl inv_lot_trx_validation_pub.parent_sub_table;
393 l_st_ser_parent_loc_tbl inv_lot_trx_validation_pub.parent_loc_table;
394 l_serial_transaction_temp_id NUMBER;
395 l_serial_diff NUMBER;
396 l_status_id NUMBER;
397 l_group_mark_id NUMBER;
398 l_st_status_id_tbl inv_lot_trx_validation_pub.number_table;
399 l_rs_status_id_tbl inv_lot_trx_validation_pub.number_table;
400 l_st_interface_id_tbl inv_lot_trx_validation_pub.number_table;
401 l_rs_interface_id_tbl inv_lot_trx_validation_pub.number_table;
402 l_rs_lot_exp_tbl inv_lot_trx_validation_pub.date_table;
403 l_st_lot_exp_tbl inv_lot_trx_validation_pub.date_table;
404 l_rs_index NUMBER;
405 l_st_index NUMBER;
406 l_st_ser_index NUMBER;
407 l_rs_ser_index NUMBER;
408 l_serial_code NUMBER;
409 l_source_record VARCHAR2 (1);
410 l_next_serial VARCHAR2 (30);
411 l_old_serial VARCHAR2 (30);
412 l_count NUMBER;
413 l_primary_quantity NUMBER;
414 l_primary_uom_code VARCHAR2 (3);
415 l_transaction_quantity NUMBER;
416 l_acct_period_tbl inv_lot_trx_validation_pub.number_table;
417 l_st_dist_account_id NUMBER;
418 l_rs_dist_account_id NUMBER;
419 l_validation_status VARCHAR2 (1);
420 l_start_primary_uom VARCHAR2 (3);
421 l_revision_control VARCHAR2 (5);
422 l_debug NUMBER
423 := NVL (fnd_profile.VALUE ('INV_DEBUG_TRACE'), 0);
424 BEGIN
425 l_validation_status := 'Y';
426
427 IF (l_debug = 1)
428 THEN
429 print_debug ('Inside populate_records', 'Populate_REcords');
430 print_debug ('l_marker 10', 'Populate_REcords');
431 print_debug ('p_parent_id is ' || p_parent_id, 'Populate_Records');
432 END IF;
433
434 l_st_item_id_tbl := inv_lot_trx_validation_pub.number_table ();
435 l_st_org_id_tbl := inv_lot_trx_validation_pub.number_table ();
436 l_st_revision_tbl := inv_lot_trx_validation_pub.revision_table ();
437 l_st_quantity_tbl := inv_lot_trx_validation_pub.number_table ();
438 l_st_primary_quantity_tbl := inv_lot_trx_validation_pub.number_table ();
439 l_st_uom_tbl := inv_lot_trx_validation_pub.uom_table ();
440 l_st_locator_id_tbl := inv_lot_trx_validation_pub.number_table ();
441 l_st_sub_code_tbl := inv_lot_trx_validation_pub.sub_code_table ();
442 l_st_cost_group_id_tbl := inv_lot_trx_validation_pub.number_table ();
443 l_st_lpn_id_tbl := inv_lot_trx_validation_pub.number_table ();
444 l_rs_item_id_tbl := inv_lot_trx_validation_pub.number_table ();
445 l_rs_org_id_tbl := inv_lot_trx_validation_pub.number_table ();
446 l_rs_revision_tbl := inv_lot_trx_validation_pub.revision_table ();
447 l_rs_quantity_tbl := inv_lot_trx_validation_pub.number_table ();
448 l_rs_primary_quantity_tbl := inv_lot_trx_validation_pub.number_table ();
449 l_rs_uom_tbl := inv_lot_trx_validation_pub.uom_table ();
450 l_rs_locator_id_tbl := inv_lot_trx_validation_pub.number_table ();
451 l_rs_sub_code_tbl := inv_lot_trx_validation_pub.sub_code_table ();
452 l_rs_lpn_id_tbl := inv_lot_trx_validation_pub.number_table ();
453 l_rs_cost_group_id_tbl := inv_lot_trx_validation_pub.number_table ();
454 l_st_lot_number_tbl := inv_lot_trx_validation_pub.lot_number_table ();
455 l_rs_lot_number_tbl := inv_lot_trx_validation_pub.lot_number_table ();
456 l_st_ser_status_tbl := inv_lot_trx_validation_pub.number_table ();
457 l_rs_ser_status_tbl := inv_lot_trx_validation_pub.number_table ();
458 l_st_ser_group_mark_id_tbl := inv_lot_trx_validation_pub.number_table ();
459 l_rs_ser_group_mark_id_tbl := inv_lot_trx_validation_pub.number_table ();
460 l_st_interface_id_tbl := inv_lot_trx_validation_pub.number_table ();
461 l_rs_interface_id_tbl := inv_lot_trx_validation_pub.number_table ();
462 l_st_status_id_tbl := inv_lot_trx_validation_pub.number_table ();
463 l_rs_status_id_tbl := inv_lot_trx_validation_pub.number_table ();
464 l_st_lot_exp_tbl := inv_lot_trx_validation_pub.date_table ();
465 l_rs_lot_exp_tbl := inv_lot_trx_validation_pub.date_table ();
466 l_acct_period_tbl := inv_lot_trx_validation_pub.number_table ();
467 l_count := 0;
468 l_rs_index := 0;
469 l_st_index := 0;
470 l_st_ser_index := 0;
471 l_rs_ser_index := 0;
472 l_st_dist_account_id := NULL;
473 l_rs_dist_account_id := NULL;
474 print_debug ('l_marker 20', 'Populate_REcords');
475
476 FOR l_mti_csr IN mti_csr (p_parent_id)
477 LOOP
478 l_count := l_count + 1;
479 l_transaction_interface_id := l_mti_csr.transaction_interface_id;
480
481 IF (l_debug = 1)
482 THEN
483 print_debug ( 'l_transaction_interface_id is '
484 || l_transaction_interface_id
485 , 'populate_records'
486 );
487 print_debug ('l_count is ' || l_count, 'populate_records');
488 print_debug ('Account Period Id ' || l_mti_csr.acct_period_id
489 , 'populate_records'
490 );
491 END IF;
492
493 l_transaction_type_id := l_mti_csr.transaction_type_id;
494 l_transaction_action_id := l_mti_csr.transaction_action_id;
495 l_acct_period_tbl.EXTEND (1);
496 l_acct_period_tbl (l_count) := l_mti_csr.acct_period_id;
497
498 /*Derive the primary quantity */
499 BEGIN
500 SELECT primary_uom_code
501 INTO l_start_primary_uom
502 FROM mtl_system_items
503 WHERE organization_id = l_mti_csr.organization_id
504 AND inventory_item_id = l_mti_csr.inventory_item_id;
505 EXCEPTION
506 WHEN NO_DATA_FOUND
507 THEN
508 fnd_message.set_name ('INV', 'INV_INT_ITEM_CODE');
509 fnd_msg_pub.ADD;
510 RAISE fnd_api.g_exc_error;
511 END;
512 IF (l_start_primary_uom <> l_mti_csr.transaction_uom)
513 THEN
514 IF (l_debug = 1)
515 THEN
516 print_debug ( 'The UOMs are different '
517 , 'populate_records'
518 );
519 END IF;
520
521 -- call inv_um.convert
522 l_primary_quantity :=
523 inv_convert.inv_um_convert (item_id => l_mti_csr.inventory_item_id
524 , PRECISION => 5
525 , from_quantity => l_mti_csr.transaction_quantity
526 , from_unit => l_mti_csr.transaction_uom
527 , to_unit => l_start_primary_uom
528 , from_name => NULL
529 , to_name => NULL
530 );
531
532 IF (l_primary_quantity = -99999)
533 THEN
534 fnd_message.set_name ('INV', 'INV-CANNOT CONVERT');
535 fnd_message.set_token ('UOM', l_mti_csr.transaction_uom);
536 fnd_message.set_token ('ROUTINE'
537 , g_pkg_name || 'Validate_Quantity');
538 fnd_msg_pub.ADD;
539 x_validation_status := 'N';
540 RAISE fnd_api.g_exc_unexpected_error;
541 END IF;
542 ELSE
543 l_primary_quantity := l_mti_csr.transaction_quantity;
544 END IF;
545
546
547 IF (l_debug = 1)
548 THEN
549 print_debug ('l_transaction_type_id is ' || l_transaction_type_id
550 , 'populate_records'
551 );
552 print_debug ('l_transaction_action_id is ' || l_transaction_action_id
553 , 'populate_records'
554 );
555 print_debug ('l_acct_period_id is ' || l_acct_period_tbl (l_count)
556 , 'populate_records'
557 );
558 END IF;
559
560 IF ( l_transaction_type_id = inv_globals.g_type_inv_lot_split
561 OR l_transaction_type_id = inv_globals.g_type_inv_lot_translate
562 )
563 THEN
564 IF (l_transaction_interface_id = p_parent_id)
565 THEN
566 IF (l_debug = 1)
567 THEN
568 print_debug ('l_marker 30 l_count' || l_count
569 , 'Populate_REcords'
570 );
571 print_debug
572 ( 'TRansaction Interface ID : Lot Split/Lot Translate'
573 || l_transaction_interface_id
574 , 'Populate_REcords'
575 );
576 END IF;
577
578 l_primary_quantity := -1 * ABS (l_primary_quantity);
579 l_transaction_quantity := -1 * ABS (l_mti_csr.transaction_quantity);
580 l_st_index := l_st_index + 1;
581 l_st_interface_id_tbl.EXTEND (1);
582 l_st_interface_id_tbl (l_st_index) :=
583 l_mti_csr.transaction_interface_id;
584 l_st_item_id_tbl.EXTEND (1);
585 l_st_item_id_tbl (l_st_index) := l_mti_csr.inventory_item_id;
586 l_st_org_id_tbl.EXTEND (1);
587 l_st_org_id_tbl (l_st_index) := l_mti_csr.organization_id;
588 l_st_revision_tbl.EXTEND (1);
589 l_st_revision_tbl (l_st_index) := l_mti_csr.revision;
590 l_st_quantity_tbl.EXTEND (1);
591 l_st_quantity_tbl (l_st_index) :=
592 ABS (l_mti_csr.transaction_quantity);
593 l_st_primary_quantity_tbl.EXTEND (1);
594 l_st_primary_quantity_tbl (l_st_index) := ABS (l_primary_quantity);
595 l_st_uom_tbl.EXTEND (1);
596 l_st_uom_tbl (l_st_index) := l_mti_csr.transaction_uom;
597 l_st_locator_id_tbl.EXTEND (1);
598 l_st_locator_id_tbl (l_st_index) := l_mti_csr.locator_id;
599 l_st_sub_code_tbl.EXTEND (1);
600 l_st_sub_code_tbl (l_st_index) := l_mti_csr.subinventory_code;
601 l_st_cost_group_id_tbl.EXTEND (1);
602 l_st_cost_group_id_tbl (l_st_index) := l_mti_csr.cost_group_id;
603 l_st_lpn_id_tbl.EXTEND (1);
604 l_st_lpn_id_tbl (l_st_index) := l_mti_csr.lpn_id;
605 l_st_dist_account_id := l_mti_csr.distribution_account_id;
606
607 IF (l_debug = 1)
608 THEN
609 print_debug ('l_marker 40 l_count' || l_count
610 , 'Populate_REcords'
611 );
612 print_debug ('Primary qty' || l_primary_quantity
613 , 'Populate_REcords'
614 );
615 END IF;
616 ELSE
617 IF (l_debug = 1)
618 THEN
619 print_debug ('l_marker 50 l_count' || l_count
620 , 'Populate_REcords'
621 );
622 print_debug
623 ( 'Else TRansaction Interface ID : Lot Split/Lot Translate'
624 || l_transaction_interface_id
625 , 'Populate_REcords'
626 );
627 END IF;
628
629 l_primary_quantity := 1 * ABS (l_primary_quantity);
630 l_transaction_quantity := 1 * ABS (l_mti_csr.transaction_quantity);
631 l_rs_index := l_rs_index + 1;
632 l_rs_interface_id_tbl.EXTEND (1);
633 l_rs_interface_id_tbl (l_rs_index) :=
634 l_mti_csr.transaction_interface_id;
635 l_rs_item_id_tbl.EXTEND (1);
636 l_rs_item_id_tbl (l_rs_index) := l_mti_csr.inventory_item_id;
637 l_rs_org_id_tbl.EXTEND (1);
638 l_rs_org_id_tbl (l_rs_index) := l_mti_csr.organization_id;
639 l_rs_revision_tbl.EXTEND (1);
640 l_rs_revision_tbl (l_rs_index) := l_mti_csr.revision;
641 l_rs_quantity_tbl.EXTEND (1);
642 l_rs_quantity_tbl (l_rs_index) :=
643 ABS (l_mti_csr.transaction_quantity);
644 l_rs_primary_quantity_tbl.EXTEND (1);
645 l_rs_primary_quantity_tbl (l_rs_index) := ABS (l_primary_quantity);
646 l_rs_uom_tbl.EXTEND (1);
647 l_rs_uom_tbl (l_rs_index) := l_mti_csr.transaction_uom;
648 l_rs_locator_id_tbl.EXTEND (1);
649 l_rs_locator_id_tbl (l_rs_index) := l_mti_csr.locator_id;
650 l_rs_sub_code_tbl.EXTEND (1);
651 l_rs_sub_code_tbl (l_rs_index) := l_mti_csr.subinventory_code;
652 l_rs_cost_group_id_tbl.EXTEND (1);
653 l_rs_cost_group_id_tbl (l_rs_index) := l_mti_csr.cost_group_id;
654 l_rs_lpn_id_tbl.EXTEND (1);
655 l_rs_lpn_id_tbl (l_rs_index) := l_mti_csr.transfer_lpn_id;
656 l_rs_dist_account_id := l_mti_csr.distribution_account_id;
657
658 IF (l_debug = 1)
659 THEN
660 print_debug ('l_marker 60 l_count' || l_count
661 , 'Populate_REcords'
662 );
663 print_debug ('Primary qty' || l_primary_quantity
664 , 'Populate_REcords'
665 );
666 END IF;
667 END IF;
668 ELSIF (l_transaction_type_id = inv_globals.g_type_inv_lot_merge)
669 THEN
670 IF (l_transaction_interface_id = p_parent_id)
671 THEN
672 IF (l_debug = 1)
673 THEN
674 print_debug ('l_marker 70 l_count' || l_count
675 , 'Populate_REcords'
676 );
677 print_debug ( 'TRansaction Interface ID : Lot Merge'
678 || l_transaction_interface_id
679 , 'Populate_REcords'
680 );
681 END IF;
682
683 l_primary_quantity := 1 * ABS (l_primary_quantity);
684 l_transaction_quantity := 1 * ABS (l_mti_csr.transaction_quantity);
685 l_rs_index := l_rs_index + 1;
686 l_rs_interface_id_tbl.EXTEND (1);
687 l_rs_interface_id_tbl (l_rs_index) :=
688 l_mti_csr.transaction_interface_id;
689 l_rs_item_id_tbl.EXTEND (1);
690 l_rs_item_id_tbl (l_rs_index) := l_mti_csr.inventory_item_id;
691 l_rs_org_id_tbl.EXTEND (1);
692 l_rs_org_id_tbl (l_rs_index) := l_mti_csr.organization_id;
693 l_rs_revision_tbl.EXTEND (1);
694 l_rs_revision_tbl (l_rs_index) := l_mti_csr.revision;
695 l_rs_quantity_tbl.EXTEND (1);
696 l_rs_quantity_tbl (l_rs_index) :=
697 ABS (l_mti_csr.transaction_quantity);
698 l_rs_primary_quantity_tbl.EXTEND (1);
699 l_rs_primary_quantity_tbl (l_rs_index) := ABS (l_primary_quantity);
700 l_rs_uom_tbl.EXTEND (1);
701 l_rs_uom_tbl (l_rs_index) := l_mti_csr.transaction_uom;
702 l_rs_locator_id_tbl.EXTEND (1);
703 l_rs_locator_id_tbl (l_rs_index) := l_mti_csr.locator_id;
704 l_rs_sub_code_tbl.EXTEND (1);
705 l_rs_sub_code_tbl (l_rs_index) := l_mti_csr.subinventory_code;
706 l_rs_cost_group_id_tbl.EXTEND (1);
707 l_rs_cost_group_id_tbl (l_rs_index) := l_mti_csr.cost_group_id;
708 l_rs_lpn_id_tbl.EXTEND (1);
709 l_rs_lpn_id_tbl (l_rs_index) := l_mti_csr.transfer_lpn_id;
710 ELSE
711 IF (l_debug = 1)
712 THEN
713 print_debug ('l_marker 80 l_count' || l_count
714 , 'Populate_REcords'
715 );
716 print_debug ( 'Else TRansaction Interface ID : Lot Merge'
717 || l_transaction_interface_id
718 , 'Populate_REcords'
719 );
720 END IF;
721
722 l_primary_quantity := -1 * ABS (l_primary_quantity);
723 l_transaction_quantity := -1 * ABS (l_mti_csr.transaction_quantity);
724 l_st_index := l_st_index + 1;
725 l_st_interface_id_tbl.EXTEND (1);
726 l_st_interface_id_tbl (l_st_index) :=
727 l_mti_csr.transaction_interface_id;
728 l_st_item_id_tbl.EXTEND (1);
729 l_st_item_id_tbl (l_st_index) := l_mti_csr.inventory_item_id;
730 l_st_org_id_tbl.EXTEND (1);
731 l_st_org_id_tbl (l_st_index) := l_mti_csr.organization_id;
732 l_st_revision_tbl.EXTEND (1);
733 l_st_revision_tbl (l_st_index) := l_mti_csr.revision;
734 l_st_quantity_tbl.EXTEND (1);
735 l_st_quantity_tbl (l_st_index) :=
736 ABS (l_mti_csr.transaction_quantity);
737 l_st_primary_quantity_tbl.EXTEND (1);
738 l_st_primary_quantity_tbl (l_st_index) := ABS (l_primary_quantity);
739 l_st_uom_tbl.EXTEND (1);
740 l_st_uom_tbl (l_st_index) := l_mti_csr.transaction_uom;
741 l_st_locator_id_tbl.EXTEND (1);
742 l_st_locator_id_tbl (l_st_index) := l_mti_csr.locator_id;
743 l_st_sub_code_tbl.EXTEND (1);
744 l_st_sub_code_tbl (l_st_index) := l_mti_csr.subinventory_code;
745 l_st_cost_group_id_tbl.EXTEND (1);
746 l_st_cost_group_id_tbl (l_st_index) := l_mti_csr.cost_group_id;
747 l_st_lpn_id_tbl.EXTEND (1);
748 l_st_lpn_id_tbl (l_st_index) := l_mti_csr.lpn_id;
749 END IF;
750 END IF;
751
752 IF (l_debug = 1)
753 THEN
754 print_debug ('l_marker 90 l_count' || l_count, 'Populate_REcords');
755 print_debug ('l_marker l_transaction_quantity' || l_transaction_quantity, 'Populate_REcords');
756 print_debug ('l_marker l_primary_quantity' || l_primary_quantity, 'Populate_REcords');
757 END IF;
758
759
760
761 UPDATE mtl_transactions_interface
762 SET transaction_quantity = l_transaction_quantity
763 , primary_quantity = l_primary_quantity
764 WHERE transaction_interface_id = l_transaction_interface_id;
765
766 UPDATE mtl_transaction_lots_interface
767 SET transaction_quantity = ABS (l_transaction_quantity)
768 , primary_quantity = ABS (l_primary_quantity)
769 WHERE transaction_interface_id = l_transaction_interface_id;
770
771 /*Support for Lot Serial */
772 SELECT serial_number_control_code
773 INTO l_serial_code
774 FROM mtl_system_items
775 WHERE inventory_item_id = l_mti_csr.inventory_item_id
776 AND organization_id = l_mti_csr.organization_id;
777
778 IF (l_debug = 1)
779 THEN
780 print_debug ('l_marker 100 l_count' || l_count, 'Populate_REcords');
781 print_debug ('l_st_index is ' || l_st_index, 'Populate_records');
782 print_debug ('l_rs_index is ' || l_rs_index, 'Populate_records');
783 END IF;
784
785 FOR l_lot_csr IN mtli_csr (l_transaction_interface_id)
786 LOOP
787 l_serial_transaction_temp_id := l_lot_csr.serial_transaction_temp_id;
788
789 IF (l_transaction_interface_id = p_parent_id)
790 THEN
791 l_source_record := 'Y';
792 ELSE
793 l_source_record := 'N';
794 END IF;
795
796 IF (l_debug = 1)
797 THEN
798 print_debug ('l_marker 110 l_count' || l_count
799 , 'Populate_REcords');
800 END IF;
801
802 IF ( l_transaction_type_id = inv_globals.g_type_inv_lot_split
803 OR l_transaction_type_id = inv_globals.g_type_inv_lot_translate
804 )
805 THEN
806 IF (l_debug = 1)
807 THEN
808 print_debug ('l_marker 120 l_count' || l_count
809 , 'Populate_REcords'
810 );
811 END IF;
812
813 IF (l_source_record = 'Y')
814 THEN
815 l_st_lot_number_tbl.EXTEND (1);
816 l_st_lot_number_tbl (l_st_index) := l_lot_csr.lot_number;
817 l_st_status_id_tbl.EXTEND (1);
818 l_st_status_id_tbl (l_st_index) := l_lot_csr.status_id;
819 l_st_lot_exp_tbl.EXTEND (1);
820 l_st_lot_exp_tbl (l_st_index) := l_lot_csr.lot_expiration_date;
821 ELSE
822 l_rs_lot_number_tbl.EXTEND (1);
823 l_rs_lot_number_tbl (l_rs_index) := l_lot_csr.lot_number;
824 l_rs_status_id_tbl.EXTEND (1);
825 l_rs_status_id_tbl (l_rs_index) := l_lot_csr.status_id;
826 l_rs_lot_exp_tbl.EXTEND (1);
827 l_rs_lot_exp_tbl (l_rs_index) := l_lot_csr.lot_expiration_date;
828 END IF;
829 ELSIF (l_transaction_type_id = inv_globals.g_type_inv_lot_merge)
830 THEN
831 IF (l_debug = 1)
832 THEN
833 print_debug ('l_marker 130 l_count' || l_count
834 , 'Populate_REcords'
835 );
836 END IF;
837
838 IF (l_source_record = 'Y')
839 THEN
840 l_rs_lot_number_tbl.EXTEND (1);
841 l_rs_lot_number_tbl (l_rs_index) := l_lot_csr.lot_number;
842 l_rs_status_id_tbl.EXTEND (1);
843 l_rs_status_id_tbl (l_rs_index) := l_lot_csr.status_id;
844 l_rs_lot_exp_tbl.EXTEND (1);
845 l_rs_lot_exp_tbl (l_rs_index) := l_lot_csr.lot_expiration_date;
846 ELSE
847 l_st_lot_number_tbl.EXTEND (1);
848 l_st_lot_number_tbl (l_st_index) := l_lot_csr.lot_number;
849 l_st_status_id_tbl.EXTEND (1);
850 l_st_status_id_tbl (l_st_index) := l_lot_csr.status_id;
851 l_st_lot_exp_tbl.EXTEND (1);
852 l_st_lot_exp_tbl (l_st_index) := l_lot_csr.lot_expiration_date;
853 END IF;
854 END IF;
855 /*Support for Lot Serial
856 *For each lot fetched from the MTLI cursor get all the serials for that lot and
857 *populate the starting and resultant records.
858 */
859 BEGIN
860 IF (l_serial_code IN (2, 5))
861 THEN
862 IF ( ( l_transaction_type_id =
863 inv_globals.g_type_inv_lot_split
864 AND l_source_record = 'Y'
865 )
866 OR ( l_transaction_type_id =
867 inv_globals.g_type_inv_lot_merge
868 AND l_source_record = 'N'
869 )
870 )
871 THEN
872 IF (l_debug = 1)
873 THEN
874 print_debug ('l_marker 140 l_count' || l_count
875 , 'Populate_REcords'
876 );
877 print_debug ('l_serial_transaction_temp_id ' || l_serial_transaction_temp_id
878 , 'Populate_REcords'
879 );
880 END IF;
881
882
883
884 FOR l_ser_csr IN msni_csr(l_serial_transaction_temp_id) LOOP
885 l_next_serial := l_ser_csr.fm_serial_number;
886 l_serial_diff :=
887 inv_serial_number_pub.get_serial_diff
888 (l_ser_csr.fm_serial_number
889 , l_ser_csr.to_serial_number
890 );
891
892 IF (l_serial_diff = -1)
893 THEN
894 fnd_message.set_name ('INV', 'INV_INVALID_SERIAL_RANGE');
895 fnd_msg_pub.ADD;
896 RAISE fnd_api.g_exc_unexpected_error;
897 END IF;
898
899 FOR i IN 1 .. l_serial_diff
900 LOOP
901 l_st_ser_index := l_st_ser_index + 1;
902 l_st_ser_number_tbl (l_next_serial) := l_next_serial;
903 l_st_ser_parent_lot_tbl (l_next_serial) := l_lot_csr.lot_number;
904 l_st_ser_parent_sub_tbl (l_next_serial) := l_mti_csr.subinventory_code;
905 l_st_ser_parent_loc_tbl (l_next_serial) := l_mti_csr.locator_id;
906
907 OPEN per_serial_msn_src_csr (
908 l_next_serial
909 , l_mti_csr.organization_id
910 , l_mti_csr.inventory_item_id
911 , l_lot_csr.lot_number
912 , l_mti_csr.subinventory_code
913 , l_mti_csr.locator_id
914 , l_mti_csr.lpn_id
915 , l_mti_csr.revision
916 );
917
918 FETCH per_serial_msn_src_csr
919 INTO l_group_mark_id
920 , l_status_id;
921
922 IF(per_serial_msn_src_csr%NOTFOUND) THEN
923 IF(per_serial_msn_src_csr%ISOPEN) THEN
924 CLOSE per_serial_msn_src_csr;
925 END IF;
926 fnd_message.set_name('INV', 'INVALID_SERIAL_NUMBER');
927 fnd_msg_pub.ADD;
928 RAISE NO_DATA_FOUND;
929 END IF;
930
931
932
933 l_st_ser_status_tbl.EXTEND (1);
934 l_st_ser_status_tbl (l_st_ser_index) := l_status_id;
935 l_st_ser_group_mark_id_tbl.EXTEND (1);
936 l_st_ser_group_mark_id_tbl (l_st_ser_index) := l_group_mark_id;
937
938 IF (per_serial_msn_src_csr%ISOPEN)
939 THEN
940 CLOSE per_serial_msn_src_csr;
941 END IF;
942
943 l_old_serial := l_next_serial;
944 l_next_serial :=
945 inv_serial_number_pub.increment_ser_num
946 (l_old_serial
947 , 1
948 );
949
950 IF (l_old_serial = l_next_serial)
951 THEN
952 fnd_message.set_name ('INV', 'INVALID_SERIAL_NUMBER');
953 fnd_msg_pub.ADD;
954 RAISE fnd_api.g_exc_unexpected_error;
955 END IF;
956 END LOOP;
957 END LOOP;
958 /*Resulting side records*/
959 ELSIF ( ( l_transaction_type_id =
960 inv_globals.g_type_inv_lot_split
961 AND l_source_record = 'N'
962 )
963 OR ( l_transaction_type_id =
964 inv_globals.g_type_inv_lot_merge
965 AND l_source_record = 'Y'
966 )
967 )
968 THEN
969 IF (l_debug = 1)
970 THEN
971 print_debug ('l_marker 150 l_count' || l_count
972 , 'Populate_REcords'
973 );
974 print_debug ('here is 150 open cursor'
975 , 'Populate_REcords'
976 );
977 print_debug ('l_serial_transaction_temp_id ' || l_serial_transaction_temp_id
978 , 'Populate_REcords'
979 );
980 END IF;
981
982 FOR l_ser_csr IN msni_csr(l_serial_transaction_temp_id) LOOP
983 l_next_serial := l_ser_csr.fm_serial_number;
984 print_debug ('processing serial ' || l_next_serial
985 , 'Populate_REcords'
986 );
987 l_serial_diff :=
988 inv_serial_number_pub.get_serial_diff
989 (l_ser_csr.fm_serial_number
990 , l_ser_csr.to_serial_number
991 );
992
993 IF (l_serial_diff = -1)
994 THEN
995 fnd_message.set_name ('INV', 'INV_INVALID_SERIAL_RANGE');
996 fnd_msg_pub.ADD;
997 RAISE fnd_api.g_exc_unexpected_error;
998 END IF;
999
1000 FOR i IN 1 .. l_serial_diff
1001 LOOP
1002 l_rs_ser_index := l_rs_ser_index + 1;
1003 l_rs_ser_number_tbl (l_next_serial) := l_next_serial;
1004 /*This will be used in validate_quantity to make sure that there
1005 *are same number of serials for each lot as in MTLI.quantity
1006 */
1007 l_rs_ser_parent_lot_tbl (l_next_serial) :=
1008 l_lot_csr.lot_number;
1009
1010 print_debug ('Open per_Ser_msn_csr for ' || l_next_serial
1011 , 'Populate_REcords'
1012 );
1013 OPEN per_serial_msn_csr (l_next_serial
1014 , l_mti_csr.organization_id
1015 , l_mti_csr.inventory_item_id
1016 );
1017 FETCH per_serial_msn_csr
1018 INTO l_group_mark_id
1019 , l_status_id;
1020
1021 IF(per_serial_msn_csr%NOTFOUND) THEN
1022 IF(per_serial_msn_csr%ISOPEN) THEN
1023 CLOSE per_serial_msn_csr;
1024 END IF;
1025 fnd_message.set_name('INV', 'INVALID_SERIAL_NUMBER');
1026 fnd_msg_pub.ADD;
1027 RAISE NO_DATA_FOUND;
1028 END IF;
1029
1030 l_rs_ser_status_tbl.EXTEND (1);
1031 l_rs_ser_status_tbl (l_rs_ser_index) := l_status_id;
1032 l_rs_ser_group_mark_id_tbl.EXTEND (1);
1033 l_rs_ser_group_mark_id_tbl (l_rs_ser_index) := l_group_mark_id;
1034
1035 IF (per_serial_msn_csr%ISOPEN)
1036 THEN
1037 CLOSE per_serial_msn_csr;
1038 END IF;
1039
1040 l_old_serial := l_next_serial;
1041 print_debug ('calling increment_serial_number ' || l_next_serial
1042 , 'Populate_REcords'
1043 );
1044 l_next_serial :=
1045 inv_serial_number_pub.increment_ser_num
1046 (l_old_serial
1047 , 1
1048 );
1049
1050 IF (l_next_serial = l_old_serial)
1051 THEN
1052 fnd_message.set_name ('INV', 'INVALID_SERIAL_NUMBER');
1053 fnd_msg_pub.ADD;
1054 RAISE fnd_api.g_exc_unexpected_error;
1055 END IF;
1056 END LOOP;
1057 END LOOP;
1058 /*It is a lot translate transaction.*/
1059 ELSIF (l_transaction_type_id =
1060 inv_globals.g_type_inv_lot_translate
1061 AND l_source_record = 'Y'
1062 )
1063 THEN
1064 l_st_ser_index := 0;
1065 l_rs_ser_index := 0;
1066 IF (l_debug = 1)
1067 THEN
1068 print_debug ('l_lot_csr.lot_number ' || l_lot_csr.lot_number
1069 , 'Populate_REcords'
1070 );
1071 print_debug ('l_mti_csr.inventory_item_id ' || l_mti_csr.inventory_item_id
1072 , 'Populate_REcords'
1073 );
1074 print_debug ('l_mti_csr.subinventory_code ' || l_mti_csr.subinventory_code
1075 , 'Populate_REcords'
1076 );
1077 print_debug ('l_mti_csr.locator_id ' || l_mti_csr.locator_id
1078 , 'Populate_REcords'
1079 );
1080 print_debug ('l_mti_csr.organization_id ' || l_mti_csr.organization_id
1081 , 'Populate_REcords'
1082 );
1083 print_debug ('l_mti_csr.lpn_id ' || l_mti_csr.lpn_id
1084 , 'Populate_REcords'
1085 );
1086 print_debug ('l_mti_csr.revision ' || l_mti_csr.revision
1087 , 'Populate_REcords'
1088 );
1089
1090 END IF;
1091
1092
1093 FOR l_ser_msn_csr IN msn_csr(l_lot_csr.lot_number
1094 , l_mti_csr.inventory_item_id
1095 , l_mti_csr.subinventory_code
1096 , l_mti_csr.locator_id
1097 , l_mti_csr.organization_id
1098 , l_mti_csr.lpn_id
1099 , l_mti_csr.revision
1100 )
1101 LOOP
1102 IF (l_debug = 1)
1103 THEN
1104 print_debug ('l_marker 160 l_count' || l_count
1105 , 'Populate_REcords'
1106 );
1107 END IF;
1108
1109 l_st_ser_index := l_st_ser_index + 1;
1110 l_st_ser_number_tbl (l_ser_msn_csr.serial_number) :=
1111 l_ser_msn_csr.serial_number;
1112 --This will be used at the time of inv_serial_number_pub.validate_serials
1113 l_st_ser_parent_lot_tbl (l_ser_msn_csr.serial_number) := l_lot_csr.lot_number;
1114 l_st_ser_status_tbl.EXTEND (1);
1115 l_st_ser_status_tbl (l_st_ser_index) := l_ser_msn_csr.status_id;
1116 l_st_ser_group_mark_id_tbl.EXTEND (1);
1117 l_st_ser_group_mark_id_tbl (l_st_ser_index) := l_ser_msn_csr.group_mark_id;
1118 /*Resulting serial array is also populated using the source record.
1119 *as the resulting item, lot combination may not be present in the
1120 *mtl_Serial_numbers table.
1121 */
1122 l_rs_ser_index := l_rs_ser_index + 1;
1123 l_rs_ser_number_tbl (l_ser_msn_csr.serial_number) := l_ser_msn_csr.serial_number;
1124 l_rs_ser_parent_lot_tbl (l_ser_msn_csr.serial_number) := l_lot_csr.lot_number;
1125 l_rs_ser_status_tbl.EXTEND (1);
1126 l_rs_ser_status_tbl (l_rs_ser_index) := l_ser_msn_csr.status_id;
1127 l_rs_ser_group_mark_id_tbl.EXTEND (1);
1128 l_rs_ser_group_mark_id_tbl (l_rs_ser_index) := l_ser_msn_csr.group_mark_id;
1129
1130
1131 END LOOP; --MSN_CSR
1132 END IF;
1133 END IF; --ITEM IS LOT/SERIAL.
1134 EXCEPTION
1135 WHEN OTHERS
1136 THEN
1137 IF (l_debug = 1)
1138 THEN
1139 print_debug ('l_marker 160 l_count' || l_count
1140 , 'Populate_REcords'
1141 );
1142 print_debug ('Serial Info in MSNI is invalid.Following may be the cause=>' ||
1143 ' a).Invalid Item/Rev/Lot/Sub/Loc/LPN/Serial combo ' ||
1144 ' b).Current status is not in 1 or 3 ' ||
1145 ' c).Serial is reserved '
1146 , 'Populate_REcords'
1147 );
1148 print_debug ('Error while fetching the serial information' || SQLERRM
1149 , 'Populate_REcords'
1150 );
1151 END IF;
1152
1153 l_validation_status := 'N';
1154 RAISE fnd_api.g_exc_unexpected_error;
1155 END;
1156 END LOOP; --End loop for MTLI records
1157 END LOOP; --End loop for MTI records
1158
1159 x_st_item_id_tbl := l_st_item_id_tbl;
1160 x_st_org_id_tbl := l_st_org_id_tbl;
1161 x_st_revision_tbl := l_st_revision_tbl;
1162 x_st_sub_code_tbl := l_st_sub_code_tbl;
1163 x_st_locator_id_tbl := l_st_locator_id_tbl;
1164 x_st_lot_num_tbl := l_st_lot_number_tbl;
1165 x_st_lpn_id_tbl := l_st_lpn_id_tbl;
1166 x_st_quantity_tbl := l_st_quantity_tbl;
1167 x_st_cost_group_tbl := l_st_cost_group_id_tbl;
1168 x_st_uom_tbl := l_st_uom_tbl;
1169 x_rs_item_id_tbl := l_rs_item_id_tbl;
1170 x_rs_org_id_tbl := l_rs_org_id_tbl;
1171 x_rs_revision_tbl := l_rs_revision_tbl;
1172 x_rs_sub_code_tbl := l_rs_sub_code_tbl;
1173 x_rs_locator_id_tbl := l_rs_locator_id_tbl;
1174 x_rs_lot_num_tbl := l_rs_lot_number_tbl;
1175 x_rs_lpn_id_tbl := l_rs_lpn_id_tbl;
1176 x_rs_quantity_tbl := l_rs_quantity_tbl;
1177 x_rs_cost_group_tbl := l_rs_cost_group_id_tbl;
1178 x_rs_uom_tbl := l_rs_uom_tbl;
1179 x_transaction_type_id := l_transaction_type_id;
1180 x_st_interface_id_tbl := l_st_interface_id_tbl;
1181 x_rs_interface_id_tbl := l_rs_interface_id_tbl;
1182 x_st_status_id_tbl := l_st_status_id_tbl;
1183 x_rs_status_id_tbl := l_rs_status_id_tbl;
1184 x_st_lot_exp_tbl := l_st_lot_exp_tbl;
1185 x_rs_lot_exp_tbl := l_rs_lot_exp_tbl;
1186 x_acct_period_tbl := l_acct_period_tbl;
1187 x_st_dist_account_id := l_st_dist_account_id;
1188 x_rs_dist_account_id := l_rs_dist_account_id;
1189 x_st_ser_num_tbl := l_st_ser_number_tbl;
1190 x_st_ser_parent_lot_tbl := l_st_ser_parent_lot_tbl;
1191 x_rs_ser_parent_lot_tbl := l_rs_ser_parent_lot_tbl;
1192 x_rs_ser_num_tbl := l_rs_ser_number_tbl;
1193 x_st_ser_status_tbl := l_st_ser_status_tbl;
1194 x_rs_ser_status_tbl := l_rs_ser_status_tbl;
1195 x_st_ser_grp_mark_id_tbl := l_st_ser_group_mark_id_tbl;
1196 x_rs_ser_grp_mark_id_tbl := l_rs_ser_group_mark_id_tbl;
1197 x_st_ser_parent_sub_tbl := l_st_ser_parent_sub_tbl;
1198 x_st_ser_parent_loc_tbl := l_st_ser_parent_loc_tbl;
1199 EXCEPTION
1200 WHEN fnd_api.g_exc_error
1201 THEN
1202 x_validation_status := l_validation_status;
1203 x_return_status := fnd_api.g_ret_sts_error;
1204 WHEN fnd_api.g_exc_unexpected_error
1205 THEN
1206 x_validation_status := l_validation_status;
1207 x_return_status := fnd_api.g_ret_sts_error;
1208 WHEN OTHERS
1209 THEN
1210 x_validation_status := 'E';
1211 x_return_status := fnd_api.g_ret_sts_error;
1212 END populate_records;
1213
1214 /* Bug 5354721. The following procedure populates the column name, value type and length
1215 of all the Lot Attribute columns in MTL_LOT_NUMBERS in the global table g_lot_attr_tbl.
1216 And this table will be then used in get_lot_attr_record procedure to get the values of the
1217 corresponding columns. Moved this part of code from get_lot_Attr_record to here as this metadata
1218 poupulation can only be done once and be re-used for all the subsequent records.
1219 */
1220 PROCEDURE get_lot_attr_table
1221 IS
1222 l_lot_attr_tbl inv_lot_sel_attr.lot_sel_attributes_tbl_type;
1223 l_debug NUMBER
1224 := NVL (fnd_profile.VALUE ('INV_DEBUG_TRACE'), 0);
1225
1226 BEGIN
1227
1228 print_debug('Before setting all the column names and types' , 'get_lot_attr_table');
1229 /*Bug:5408823. Instead of fetching the metadata details from ALL_TAB_COLUMNS,
1230 hardcoding them as follows.
1231 */
1232 l_lot_attr_tbl (1).column_name := 'ATTRIBUTE_CATEGORY';
1233 l_lot_attr_tbl (1).column_type := 'VARCHAR2';
1234 l_lot_attr_tbl (1).column_length := 30;
1235 l_lot_attr_tbl (2).column_name := 'ATTRIBUTE1';
1236 l_lot_attr_tbl (2).column_type := 'VARCHAR2';
1237 l_lot_attr_tbl (2).column_length := 150;
1238 l_lot_attr_tbl (3).column_name := 'ATTRIBUTE2' ;
1239 l_lot_attr_tbl (3).column_type := 'VARCHAR2';
1240 l_lot_attr_tbl (3).column_length := 150;
1241 l_lot_attr_tbl (4).column_name := 'ATTRIBUTE3';
1242 l_lot_attr_tbl (4).column_type := 'VARCHAR2';
1243 l_lot_attr_tbl (4).column_length := 150;
1244 l_lot_attr_tbl (5).column_name := 'ATTRIBUTE4';
1245 l_lot_attr_tbl (5).column_type := 'VARCHAR2';
1246 l_lot_attr_tbl (5).column_length := 150;
1247 l_lot_attr_tbl (6).column_name := 'ATTRIBUTE5';
1248 l_lot_attr_tbl (6).column_type := 'VARCHAR2';
1249 l_lot_attr_tbl (6).column_length := 150;
1250 l_lot_attr_tbl (7).column_name := 'ATTRIBUTE6';
1251 l_lot_attr_tbl (7).column_type := 'VARCHAR2';
1252 l_lot_attr_tbl (7).column_length := 150;
1253 l_lot_attr_tbl (8).column_name := 'ATTRIBUTE7';
1254 l_lot_attr_tbl (8).column_type := 'VARCHAR2';
1255 l_lot_attr_tbl (8).column_length := 150;
1256 l_lot_attr_tbl (9).column_name := 'ATTRIBUTE8';
1257 l_lot_attr_tbl (9).column_type := 'VARCHAR2';
1258 l_lot_attr_tbl (9).column_length := 150;
1259 l_lot_attr_tbl (10).column_name := 'ATTRIBUTE9';
1260 l_lot_attr_tbl (10).column_type := 'VARCHAR2';
1261 l_lot_attr_tbl (10).column_length := 150;
1262 l_lot_attr_tbl (11).column_name := 'ATTRIBUTE10';
1263 l_lot_attr_tbl (11).column_type := 'VARCHAR2';
1264 l_lot_attr_tbl (11).column_length := 150;
1265 l_lot_attr_tbl (12).column_name := 'ATTRIBUTE11';
1266 l_lot_attr_tbl (12).column_type := 'VARCHAR2';
1267 l_lot_attr_tbl (12).column_length := 150;
1268 l_lot_attr_tbl (13).column_name := 'ATTRIBUTE12';
1269 l_lot_attr_tbl (13).column_type := 'VARCHAR2';
1270 l_lot_attr_tbl (13).column_length := 150;
1271 l_lot_attr_tbl (14).column_name := 'ATTRIBUTE13';
1272 l_lot_attr_tbl (14).column_type := 'VARCHAR2';
1273 l_lot_attr_tbl (14).column_length := 150;
1274 l_lot_attr_tbl (15).column_name := 'ATTRIBUTE14';
1275 l_lot_attr_tbl (15).column_type := 'VARCHAR2';
1276 l_lot_attr_tbl (15).column_length := 150;
1277 l_lot_attr_tbl (16).column_name := 'ATTRIBUTE15';
1278 l_lot_attr_tbl (16).column_type := 'VARCHAR2';
1279 l_lot_attr_tbl (16).column_length := 150;
1280 l_lot_attr_tbl (17).column_name := 'DESCRIPTION' ;
1281 l_lot_attr_tbl (17).column_type := 'VARCHAR2';
1282 l_lot_attr_tbl (17).column_length := 256;
1283 l_lot_attr_tbl (18).column_name := 'VENDOR_NAME';
1284 l_lot_attr_tbl (18).column_type := 'VARCHAR2';
1285 l_lot_attr_tbl (18).column_length := 240;
1286 l_lot_attr_tbl (19).column_name := 'DATE_CODE';
1287 l_lot_attr_tbl (19).column_type := 'VARCHAR2';
1288 l_lot_attr_tbl (19).column_length := 150 ;
1289 l_lot_attr_tbl (20).column_name := 'CHANGE_DATE';
1290 l_lot_attr_tbl (20).column_type := 'DATE';
1291 l_lot_attr_tbl (20).column_length := 11;
1292 l_lot_attr_tbl (21).column_name := 'AGE';
1293 l_lot_attr_tbl (21).column_type := 'NUMBER';
1294 l_lot_attr_tbl (21).column_length := 38;
1295 l_lot_attr_tbl (22).column_name := 'LOT_ATTRIBUTE_CATEGORY';
1296 l_lot_attr_tbl (22).column_type := 'VARCHAR2';
1297 l_lot_attr_tbl (22).column_length := 30;
1298 l_lot_attr_tbl (23).column_name := 'ITEM_SIZE';
1299 l_lot_attr_tbl (23).column_type := 'NUMBER';
1300 l_lot_attr_tbl (23).column_length := 38;
1301 l_lot_attr_tbl (24).column_name := 'COLOR';
1302 l_lot_attr_tbl (24).column_type := 'VARCHAR2';
1303 l_lot_attr_tbl (24).column_length := 150;
1304 l_lot_attr_tbl (25).column_name := 'VOLUME';
1305 l_lot_attr_tbl (25).column_type := 'NUMBER';
1306 l_lot_attr_tbl (25).column_length := 38;
1307 l_lot_attr_tbl (26).column_name := 'VOLUME_UOM';
1308 l_lot_attr_tbl (26).column_type := 'VARCHAR2';
1309 l_lot_attr_tbl (26).column_length := 3;
1310 l_lot_attr_tbl (27).column_name := 'PLACE_OF_ORIGIN';
1311 l_lot_attr_tbl (27).column_type := 'VARCHAR2';
1312 l_lot_attr_tbl (27).column_length := 150;
1313 l_lot_attr_tbl (28).column_name := 'BEST_BY_DATE';
1314 l_lot_attr_tbl (28).column_type := 'DATE';
1315 l_lot_attr_tbl (28).column_length := 11;
1316 l_lot_attr_tbl (29).column_name := 'LENGTH';
1317 l_lot_attr_tbl (29).column_type := 'NUMBER';
1318 l_lot_attr_tbl (29).column_length := 38;
1319 l_lot_attr_tbl (30).column_name := 'LENGTH_UOM';
1320 l_lot_attr_tbl (30).column_type := 'VARCHAR2';
1321 l_lot_attr_tbl (30).column_length := 3;
1322 l_lot_attr_tbl (31).column_name := 'RECYCLED_CONTENT';
1323 l_lot_attr_tbl (31).column_type := 'NUMBER';
1324 l_lot_attr_tbl (31).column_length := 38;
1325 l_lot_attr_tbl (32).column_name := 'THICKNESS';
1326 l_lot_attr_tbl (32).column_type := 'NUMBER';
1327 l_lot_attr_tbl (32).column_length := 38;
1328 l_lot_attr_tbl (33).column_name := 'THICKNESS_UOM';
1329 l_lot_attr_tbl (33).column_type := 'VARCHAR2';
1330 l_lot_attr_tbl (33).column_length := 3;
1331 l_lot_attr_tbl (34).column_name := 'WIDTH';
1332 l_lot_attr_tbl (34).column_type := 'NUMBER';
1333 l_lot_attr_tbl (34).column_length := 38;
1334 l_lot_attr_tbl (35).column_name := 'WIDTH_UOM';
1335 l_lot_attr_tbl (35).column_type := 'VARCHAR2';
1336 l_lot_attr_tbl (35).column_length := 3;
1337 l_lot_attr_tbl (36).column_name := 'CURL_WRINKLE_FOLD';
1338 l_lot_attr_tbl (36).column_type := 'VARCHAR2';
1339 l_lot_attr_tbl (36).column_length := 150;
1340 l_lot_attr_tbl (37).column_name := 'C_ATTRIBUTE1';
1341 l_lot_attr_tbl (37).column_type := 'VARCHAR2';
1342 l_lot_attr_tbl (37).column_length := 150;
1343 l_lot_attr_tbl (38).column_name := 'C_ATTRIBUTE2';
1344 l_lot_attr_tbl (38).column_type := 'VARCHAR2';
1345 l_lot_attr_tbl (38).column_length := 150;
1346 l_lot_attr_tbl (39).column_name := 'C_ATTRIBUTE3';
1347 l_lot_attr_tbl (39).column_type := 'VARCHAR2';
1348 l_lot_attr_tbl (39).column_length := 150;
1349 l_lot_attr_tbl (40).column_name := 'C_ATTRIBUTE4';
1350 l_lot_attr_tbl (40).column_type := 'VARCHAR2';
1351 l_lot_attr_tbl (40).column_length := 150;
1352 l_lot_attr_tbl (41).column_name := 'C_ATTRIBUTE5';
1353 l_lot_attr_tbl (41).column_type := 'VARCHAR2';
1354 l_lot_attr_tbl (41).column_length := 150;
1355 l_lot_attr_tbl (42).column_name := 'C_ATTRIBUTE6';
1356 l_lot_attr_tbl (42).column_type := 'VARCHAR2';
1357 l_lot_attr_tbl (42).column_length := 150;
1358 l_lot_attr_tbl (43).column_name := 'C_ATTRIBUTE7';
1359 l_lot_attr_tbl (43).column_type := 'VARCHAR2';
1360 l_lot_attr_tbl (43).column_length := 150;
1361 l_lot_attr_tbl (44).column_name := 'C_ATTRIBUTE8';
1362 l_lot_attr_tbl (44).column_type := 'VARCHAR2';
1363 l_lot_attr_tbl (44).column_length := 150;
1364 l_lot_attr_tbl (45).column_name := 'C_ATTRIBUTE9';
1365 l_lot_attr_tbl (45).column_type := 'VARCHAR2';
1366 l_lot_attr_tbl (45).column_length := 150;
1367 l_lot_attr_tbl (46).column_name := 'C_ATTRIBUTE10';
1368 l_lot_attr_tbl (46).column_type := 'VARCHAR2';
1369 l_lot_attr_tbl (46).column_length := 150;
1370 l_lot_attr_tbl (47).column_name := 'C_ATTRIBUTE11';
1371 l_lot_attr_tbl (47).column_type := 'VARCHAR2';
1372 l_lot_attr_tbl (47).column_length := 150;
1373 l_lot_attr_tbl (48).column_name := 'C_ATTRIBUTE12';
1374 l_lot_attr_tbl (48).column_type := 'VARCHAR2';
1375 l_lot_attr_tbl (48).column_length := 150;
1376 l_lot_attr_tbl (49).column_name := 'C_ATTRIBUTE13';
1377 l_lot_attr_tbl (49).column_type := 'VARCHAR2';
1378 l_lot_attr_tbl (49).column_length := 150;
1379 l_lot_attr_tbl (50).column_name := 'C_ATTRIBUTE14';
1380 l_lot_attr_tbl (50).column_type := 'VARCHAR2';
1381 l_lot_attr_tbl (50).column_length := 150;
1382 l_lot_attr_tbl (51).column_name := 'C_ATTRIBUTE15';
1383 l_lot_attr_tbl (51).column_type := 'VARCHAR2';
1384 l_lot_attr_tbl (51).column_length := 150;
1385 l_lot_attr_tbl (52).column_name := 'C_ATTRIBUTE16';
1386 l_lot_attr_tbl (52).column_type := 'VARCHAR2';
1387 l_lot_attr_tbl (52).column_length := 150;
1388 l_lot_attr_tbl (53).column_name := 'C_ATTRIBUTE17';
1389 l_lot_attr_tbl (53).column_type := 'VARCHAR2';
1390 l_lot_attr_tbl (53).column_length := 150;
1391 l_lot_attr_tbl (54).column_name := 'C_ATTRIBUTE18';
1392 l_lot_attr_tbl (54).column_type := 'VARCHAR2';
1393 l_lot_attr_tbl (54).column_length := 150;
1394 l_lot_attr_tbl (55).column_name := 'C_ATTRIBUTE19';
1395 l_lot_attr_tbl (55).column_type := 'VARCHAR2';
1396 l_lot_attr_tbl (55).column_length := 150;
1397 l_lot_attr_tbl (56).column_name := 'C_ATTRIBUTE20';
1398 l_lot_attr_tbl (56).column_type := 'VARCHAR2';
1399 l_lot_attr_tbl (56).column_length := 150;
1400 l_lot_attr_tbl (57).column_name := 'D_ATTRIBUTE1';
1401 l_lot_attr_tbl (57).column_type := 'DATE';
1402 l_lot_attr_tbl (57).column_length := 11;
1403 l_lot_attr_tbl (58).column_name := 'D_ATTRIBUTE2';
1404 l_lot_attr_tbl (58).column_type := 'DATE';
1405 l_lot_attr_tbl (58).column_length := 11;
1406 l_lot_attr_tbl (59).column_name := 'D_ATTRIBUTE3';
1407 l_lot_attr_tbl (59).column_type := 'DATE';
1408 l_lot_attr_tbl (59).column_length := 11;
1409 l_lot_attr_tbl (60).column_name := 'D_ATTRIBUTE4';
1410 l_lot_attr_tbl (60).column_type := 'DATE';
1411 l_lot_attr_tbl (60).column_length := 11;
1412 l_lot_attr_tbl (61).column_name := 'D_ATTRIBUTE5';
1413 l_lot_attr_tbl (61).column_type := 'DATE';
1414 l_lot_attr_tbl (61).column_length := 11;
1415 l_lot_attr_tbl (62).column_name := 'D_ATTRIBUTE6';
1416 l_lot_attr_tbl (62).column_type := 'DATE';
1417 l_lot_attr_tbl (62).column_length := 11;
1418 l_lot_attr_tbl (63).column_name := 'D_ATTRIBUTE7';
1419 l_lot_attr_tbl (63).column_type := 'DATE';
1420 l_lot_attr_tbl (63).column_length := 11;
1421 l_lot_attr_tbl (64).column_name := 'D_ATTRIBUTE8';
1422 l_lot_attr_tbl (64).column_type := 'DATE';
1423 l_lot_attr_tbl (64).column_length := 11;
1424 l_lot_attr_tbl (65).column_name := 'D_ATTRIBUTE9';
1425 l_lot_attr_tbl (65).column_type := 'DATE';
1426 l_lot_attr_tbl (65).column_length := 11;
1427 l_lot_attr_tbl (66).column_name := 'D_ATTRIBUTE10';
1428 l_lot_attr_tbl (66).column_type := 'DATE';
1429 l_lot_attr_tbl (66).column_length := 11;
1430 l_lot_attr_tbl (67).column_name := 'N_ATTRIBUTE1';
1431 l_lot_attr_tbl (67).column_type := 'NUMBER';
1432 l_lot_attr_tbl (67).column_length := 38;
1433 l_lot_attr_tbl (68).column_name := 'N_ATTRIBUTE2';
1434 l_lot_attr_tbl (68).column_type := 'NUMBER';
1435 l_lot_attr_tbl (68).column_length := 38;
1436 l_lot_attr_tbl (69).column_name := 'N_ATTRIBUTE3';
1437 l_lot_attr_tbl (69).column_type := 'NUMBER';
1438 l_lot_attr_tbl (69).column_length := 38;
1439 l_lot_attr_tbl (70).column_name := 'N_ATTRIBUTE4';
1440 l_lot_attr_tbl (70).column_type := 'NUMBER';
1441 l_lot_attr_tbl (70).column_length := 38;
1442 l_lot_attr_tbl (71).column_name := 'N_ATTRIBUTE5';
1443 l_lot_attr_tbl (71).column_type := 'NUMBER';
1444 l_lot_attr_tbl (71).column_length := 38;
1445 l_lot_attr_tbl (72).column_name := 'N_ATTRIBUTE6';
1446 l_lot_attr_tbl (72).column_type := 'NUMBER';
1447 l_lot_attr_tbl (72).column_length := 38;
1448 l_lot_attr_tbl (73).column_name := 'N_ATTRIBUTE7';
1449 l_lot_attr_tbl (73).column_type := 'NUMBER';
1450 l_lot_attr_tbl (73).column_length := 38;
1451 l_lot_attr_tbl (74).column_name := 'N_ATTRIBUTE8';
1452 l_lot_attr_tbl (74).column_type := 'NUMBER';
1453 l_lot_attr_tbl (74).column_length := 38;
1454 l_lot_attr_tbl (75).column_name := 'N_ATTRIBUTE9';
1455 l_lot_attr_tbl (75).column_type := 'NUMBER';
1456 l_lot_attr_tbl (75).column_length := 38;
1457 l_lot_attr_tbl (76).column_name := 'N_ATTRIBUTE10';
1458 l_lot_attr_tbl (76).column_type := 'NUMBER';
1459 l_lot_attr_tbl (76).column_length := 38;
1460 l_lot_attr_tbl (77).column_name := 'VENDOR_ID';
1461 l_lot_attr_tbl (77).column_type := 'NUMBER';
1462 l_lot_attr_tbl (77).column_length := 38;
1463 l_lot_attr_tbl (78).column_name := 'TERRITORY_CODE';
1464 l_lot_attr_tbl (78).column_type := 'VARCHAR2';
1465 l_lot_attr_tbl (78).column_length := 30;
1466
1467
1468 print_debug('After setting all the column names and types' , 'get_lot_attr_table');
1469 g_lot_attr_tbl := l_lot_attr_tbl;
1470
1471 EXCEPTION
1472 WHEN OTHERS
1473 THEN
1474
1475 IF (l_debug = 1)
1476 THEN
1477 print_debug ('In Exception in get_lot_attr_table',
1478 'get_lot_attr_table'
1479 );
1480 END IF;
1481 END get_lot_attr_table;
1482
1483
1484 PROCEDURE get_lot_attr_record (
1485 x_lot_attr_tbl OUT NOCOPY inv_lot_sel_attr.lot_sel_attributes_tbl_type
1486 , p_transaction_interface_id IN NUMBER
1487 , p_lot_number IN VARCHAR2
1488 , p_starting_lot_number IN VARCHAR2
1489 , p_organization_id IN NUMBER
1490 , p_inventory_item_id IN NUMBER
1491 )
1492 IS
1493 /*Bug:5354721. Moved the following code to procedure get_lot_attr_table. */
1494 /*l_app_owner_schema VARCHAR2 (30);
1495 l_app_status VARCHAR2 (1);
1496 l_app_industry VARCHAR2 (1);
1497 l_app_info_status BOOLEAN
1498 := fnd_installation.get_app_info (application_short_name => 'INV'
1499 , status => l_app_status
1500 , industry => l_app_industry
1501 , oracle_schema => l_app_owner_schema
1502 );
1503
1504 CURSOR lot_column_csr (p_table_name VARCHAR2)
1505 IS
1506 SELECT column_name
1507 , data_type
1508 , data_length
1509 FROM all_tab_columns
1510 WHERE table_name = UPPER (p_table_name)
1511 AND owner = l_app_owner_schema
1512 AND column_id > 22
1513 ORDER BY column_id; */
1514
1515 l_lot_attr_tbl inv_lot_sel_attr.lot_sel_attributes_tbl_type;
1516 l_column_idx NUMBER;
1517 l_select_stmt LONG := NULL;
1518 l_sql_p INTEGER := NULL;
1519 l_rows_processed INTEGER := NULL;
1520 l_line NUMBER := 1;
1521 l_stmt LONG;
1522 l_lot_num NUMBER := 0;
1523 l_debug NUMBER
1524 := NVL (fnd_profile.VALUE ('INV_DEBUG_TRACE'), 0);
1525 BEGIN
1526
1527 /*Bug 5354721. The following satement copies all the columns names, type and length into l_lot_attr_tbl*/
1528 l_lot_attr_tbl := g_lot_attr_tbl;
1529
1530
1531 BEGIN
1532 SELECT COUNT (lot_number)
1533 INTO l_lot_num
1534 FROM mtl_lot_numbers mtl
1535 WHERE lot_number = p_lot_number
1536 AND inventory_item_id = p_inventory_item_id
1537 AND organization_id = p_organization_id;
1538 EXCEPTION
1539 WHEN OTHERS
1540 THEN
1541 fnd_message.set_name ('INV', 'INV_INVALID_LOT');
1542 fnd_msg_pub.ADD;
1543 RAISE fnd_api.g_exc_unexpected_error;
1544 END;
1545
1546 /*Bug:5354721. Moved the following code to get_lot_Attr_table where all the
1547 metadata information column name , type and length are populated in global table
1548 g_lot_attr_tbl.
1549
1550 Also, instead of building the SELECT clause here in the loop it is defined in the
1551 global varialble g_select_stmt only once.
1552
1553 */
1554 /*
1555 FOR l_lot_column_csr IN lot_column_csr ('MTL_TRANSACTION_LOTS_INTERFACE')
1556 LOOP
1557 l_column_idx := l_column_idx + 1;
1558 l_lot_attr_tbl (l_column_idx).column_name :=
1559 l_lot_column_csr.column_name;
1560 l_lot_attr_tbl (l_column_idx).column_type := l_lot_column_csr.data_type;
1561
1562 IF UPPER (l_lot_column_csr.data_type) = 'DATE'
1563 THEN
1564 l_lot_attr_tbl (l_column_idx).column_length := 11;
1565 ELSIF UPPER (l_lot_column_csr.data_type) = 'NUMBER'
1566 THEN
1567 l_lot_attr_tbl (l_column_idx).column_length := 38;
1568 ELSE
1569 l_lot_attr_tbl (l_column_idx).column_length :=
1570 l_lot_column_csr.data_length;
1571 END IF;
1572
1573 IF (l_column_idx = 1)
1574 THEN
1575 l_select_stmt :=
1576 l_select_stmt
1577 || ' NVL(MTLI.'
1578 || l_lot_attr_tbl (l_column_idx).column_name
1579 || ', MTL.'
1580 || l_lot_attr_tbl (l_column_idx).column_name
1581 || ')';
1582 ELSE
1583 l_select_stmt :=
1584 l_select_stmt
1585 || ' , NVL(MTLI.'
1586 || l_lot_attr_tbl (l_column_idx).column_name
1587 || ', MTL.'
1588 || l_lot_attr_tbl (l_column_idx).column_name
1589 || ')';
1590 END IF;
1591 END LOOP;
1592 */
1593
1594 IF (l_lot_num > 0)
1595 THEN
1596 /*Bug:5354721. Appending FROM clause to the global statement. */
1597 l_select_stmt :=
1598 g_select_stmt
1599 || ' FROM MTL_TRANSACTION_LOTS_INTERFACE MTLI, MTL_TRANSACTIONS_INTERFACE MTI, '
1600 || ' MTL_LOT_NUMBERS MLN '
1601 || ' WHERE mtli.transaction_interface_id = :b_interface_id '
1602 || ' AND mtli.lot_number = :b_lot_number '
1603 || ' AND mtli.transaction_interface_id = mti.transaction_interface_id '
1604 || ' AND mln.lot_number = mtli.lot_number (+)'
1605 || ' AND mln.inventory_item_id = mti.inventory_item_id (+)'
1606 || ' AND mln.organization_id = mti.organization_id (+)';
1607 ELSE
1608 -- If it is a new lot, we get all the attributes from MTI
1609 -- and for others which are not mentioned, we get ir from the
1610 -- parent lot. Passing a new parameter called starting lot
1611 -- which is the lot number of the parent lot.
1612 l_select_stmt :=
1613 g_select_stmt
1614 || ' FROM MTL_TRANSACTION_LOTS_INTERFACE MTLI, MTL_TRANSACTIONS_INTERFACE MTI, '
1615 || ' MTL_LOT_NUMBERS MLN '
1616 || ' WHERE mtli.transaction_interface_id = :b_interface_id '
1617 || ' AND mtli.lot_number = :b_lot_number '
1618 || ' AND mtli.transaction_interface_id = mti.transaction_interface_id '
1619 || ' AND mln.lot_number = :b_starting_lot_number'
1620 || ' AND mln.inventory_item_id = mti.inventory_item_id (+)'
1621 || ' AND mln.organization_id = mti.organization_id (+)';
1622 -- l_select_stmt := l_select_stmt || ' FROM MTL_TRANSACTION_LOTS_INTERFACE ' ||
1623 -- ' WHERE lot_number = :b_lot_number ' ||
1624 -- ' AND transaction_interface_id = :b_interface_id ';
1625 END IF;
1626
1627 --print_debug(l_select_stmt, 'get_lot_attr_record');
1628 IF (l_debug = 1)
1629 THEN
1630 print_debug ('after setting the sql stmt', 'get_lot_attr_record');
1631 END IF;
1632
1633 l_sql_p := DBMS_SQL.open_cursor;
1634 DBMS_SQL.parse (l_sql_p, l_select_stmt, DBMS_SQL.native);
1635 DBMS_SQL.bind_variable (l_sql_p
1636 , 'b_interface_id'
1637 , p_transaction_interface_id
1638 );
1639 DBMS_SQL.bind_variable (l_sql_p, 'b_lot_number', p_lot_number);
1640
1641 IF l_lot_num = 0
1642 THEN
1643 DBMS_SQL.bind_variable (l_sql_p
1644 , 'b_starting_lot_number'
1645 , p_starting_lot_number
1646 );
1647 END IF;
1648
1649 IF (l_debug = 1)
1650 THEN
1651 print_debug ('after open cursor and bind variables'
1652 , 'get_lot_attr_record'
1653 );
1654 END IF;
1655
1656 l_column_idx := 0;
1657
1658 FOR i IN 1 .. l_lot_attr_tbl.COUNT
1659 LOOP
1660 l_column_idx := i;
1661 DBMS_SQL.define_column (l_sql_p
1662 , l_column_idx
1663 , l_lot_attr_tbl (i).column_value
1664 , l_lot_attr_tbl (i).column_length
1665 );
1666 END LOOP;
1667
1668 IF (l_debug = 1)
1669 THEN
1670 print_debug ('after define columns', 'get_lot_attr_record');
1671 END IF;
1672
1673 l_rows_processed := DBMS_SQL.EXECUTE (l_sql_p);
1674
1675 IF (l_debug = 1)
1676 THEN
1677 print_debug ('l_rows_processed is ' || l_rows_processed
1678 , 'get_lot_attr_record'
1679 );
1680 print_debug ('Interface Id ' || p_transaction_interface_id
1681 , 'get_lot_attr_record'
1682 );
1683 print_debug ('Lot Number passed ' || p_lot_number
1684 , 'get_lot_attr_record');
1685 print_debug ('Starting Lot Number passed ' || p_starting_lot_number
1686 , 'get_lot_attr_record'
1687 );
1688 END IF;
1689
1690 LOOP
1691 IF (DBMS_SQL.fetch_rows (l_sql_p) > 0)
1692 THEN
1693 l_column_idx := 0;
1694
1695 FOR i IN 1 .. l_lot_attr_tbl.COUNT
1696 LOOP
1697 l_column_idx := i;
1698 DBMS_SQL.column_value (l_sql_p
1699 , l_column_idx
1700 , l_lot_attr_tbl (i).column_value
1701 );
1702 END LOOP;
1703 ELSE
1704 --dbms_sql.close_cursor(l_sql_p);
1705 EXIT;
1706 END IF;
1707
1708 EXIT;
1709 END LOOP;
1710
1711 IF (l_debug = 1)
1712 THEN
1713 print_debug ('after fetching rows', 'get_lot_attr_record');
1714 END IF;
1715
1716 DBMS_SQL.close_cursor (l_sql_p);
1717
1718 IF (l_debug = 1)
1719 THEN
1720 print_debug ('after closing cursor', 'get_lot_attr_record');
1721 print_debug ('Count of the attr table' || l_lot_attr_tbl.COUNT
1722 , 'get_lot_attr_record'
1723 );
1724 print_debug ('Lot Number' || l_lot_num, 'get_lot_attr_record');
1725 END IF;
1726
1727 FOR i IN 1 .. l_lot_attr_tbl.COUNT
1728 LOOP
1729 IF (l_lot_attr_tbl (i).column_value IS NOT NULL)
1730 THEN
1731 IF (l_debug = 1)
1732 THEN
1733 print_debug ('Column_NAME is ' || l_lot_attr_tbl (i).column_name
1734 , 'get_lot_attr_record'
1735 );
1736 print_debug ('Column Value is ' || l_lot_attr_tbl (i).column_value
1737 , 'get_lot_attr_record'
1738 );
1739 END IF;
1740 END IF;
1741 END LOOP;
1742
1743 x_lot_attr_tbl := l_lot_attr_tbl;
1744 EXCEPTION
1745 WHEN OTHERS
1746 THEN
1747 fnd_message.set_name ('WMS', 'WMS_GET_LOT_ATTR_ERROR');
1748 fnd_msg_pub.ADD;
1749
1750 -- FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1751 IF (l_debug = 1)
1752 THEN
1753 print_debug ('SQL : ' || SUBSTR (SQLERRM, 1, 200)
1754 , 'get_lot_attr_record'
1755 );
1756 END IF;
1757 END get_lot_attr_record;
1758
1759 PROCEDURE update_lot_attr_record (
1760 p_lot_attr_tbl IN inv_lot_sel_attr.lot_sel_attributes_tbl_type
1761 , p_transaction_interface_id IN NUMBER
1762 , p_lot_number IN VARCHAR2
1763 , p_organization_id IN NUMBER
1764 , p_inventory_item_id IN NUMBER
1765 )
1766 IS
1767 l_lot_attr_tbl inv_lot_sel_attr.lot_sel_attributes_tbl_type;
1768 l_column_idx NUMBER;
1769 l_update_stmt LONG := 'UPDATE MTL_TRANSACTION_LOTS_INTERFACE SET ';
1770 l_sql_p INTEGER := NULL;
1771 l_rows_processed INTEGER := NULL;
1772 l_debug NUMBER
1773 := NVL (fnd_profile.VALUE ('INV_DEBUG_TRACE'), 0);
1774 BEGIN
1775 print_debug ('Inside update attr', 'Validate_Lot_Split');
1776 print_debug ('Count' || p_lot_attr_tbl.COUNT, 'Update Lot attr');
1777 print_debug ('Lot Number' || p_lot_number, 'Update Lot attr');
1778 l_column_idx := 1;
1779
1780 FOR i IN 1 .. p_lot_attr_tbl.COUNT
1781 LOOP
1782 --if( i = 1 ) then
1783 IF (p_lot_attr_tbl (i).column_value IS NOT NULL)
1784 THEN
1785 IF (p_lot_attr_tbl (i).column_type = 'NUMBER')
1786 THEN
1787 EXECUTE IMMEDIATE 'Update mtl_transaction_lots_interface
1788 set '
1789 || p_lot_attr_tbl (i).column_name
1790 || ' = :1 '
1791 || 'where transaction_interface_id = :2 '
1792 USING p_lot_attr_tbl (i).column_value
1793 , p_transaction_interface_id;
1794 END IF;
1795
1796 IF (p_lot_attr_tbl (i).column_type = 'DATE')
1797 THEN
1798 EXECUTE IMMEDIATE 'Update Mtl_transaction_lots_interface
1799 SET '
1800 || p_lot_attr_tbl (i).column_name
1801 || ' = :1 '
1802 || 'where transaction_interface_id = :2 '
1803 USING
1804 fnd_date.canonical_to_date
1805 (p_lot_attr_tbl (i).column_value
1806 )
1807 , p_transaction_interface_id;
1808 END IF;
1809
1810 IF (p_lot_attr_tbl (i).column_type = 'VARCHAR2')
1811 THEN
1812 EXECUTE IMMEDIATE 'Update Mtl_transaction_lots_interface
1813 SET '
1814 || p_lot_attr_tbl (i).column_name
1815 || ' = :1 '
1816 || 'where transaction_interface_id = :2 '
1817 USING p_lot_attr_tbl (i).column_value
1818 , p_transaction_interface_id;
1819 END IF;
1820 END IF;
1821 -- end if;
1822 --print_debug(p_lot_attr_tbl(i).COLUMN_NAME, 'update_lot_attr_record');
1823 END LOOP;
1824 EXCEPTION
1825 WHEN OTHERS
1826 THEN
1827 fnd_message.set_name ('WMS', 'WMS_UPDATE_ATTR_ERROR');
1828 fnd_msg_pub.ADD;
1829
1830 -- FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1831 IF (l_debug = 1)
1832 THEN
1833 print_debug ('SQL : ' || SUBSTR (SQLERRM, 1, 200)
1834 , 'get_lot_attr_record'
1835 );
1836 END IF;
1837 END update_lot_attr_record;
1838
1839 PROCEDURE validate_lot_split_trx (
1840 x_return_status OUT NOCOPY VARCHAR2
1841 , x_msg_count OUT NOCOPY NUMBER
1842 , x_msg_data OUT NOCOPY VARCHAR2
1843 , x_validation_status OUT NOCOPY VARCHAR2
1844 , p_parent_id IN NUMBER
1845 )
1846 IS
1847 l_return_status VARCHAR2 (1);
1848 l_msg_count NUMBER;
1849 l_msg_data VARCHAR2 (255);
1850 l_validation_status VARCHAR2 (1);
1851 l_transaction_type_id NUMBER;
1852 -- l_acct_period_id NUMBER;
1853 l_transaction_interface_id NUMBER;
1854 l_transaction_action_id NUMBER;
1855 l_st_item_id_tbl inv_lot_trx_validation_pub.number_table;
1856 l_st_org_id_tbl inv_lot_trx_validation_pub.number_table;
1857 l_st_revision_tbl inv_lot_trx_validation_pub.revision_table;
1858 l_st_quantity_tbl inv_lot_trx_validation_pub.number_table;
1859 l_st_uom_tbl inv_lot_trx_validation_pub.uom_table;
1860 l_st_locator_id_tbl inv_lot_trx_validation_pub.number_table;
1861 l_st_sub_code_tbl inv_lot_trx_validation_pub.sub_code_table;
1862 l_st_cost_group_id_tbl inv_lot_trx_validation_pub.number_table;
1863 l_st_lpn_id_tbl inv_lot_trx_validation_pub.number_table;
1864 l_rs_item_id_tbl inv_lot_trx_validation_pub.number_table;
1865 l_rs_org_id_tbl inv_lot_trx_validation_pub.number_table;
1866 l_rs_revision_tbl inv_lot_trx_validation_pub.revision_table;
1867 l_rs_quantity_tbl inv_lot_trx_validation_pub.number_table;
1868 l_rs_uom_tbl inv_lot_trx_validation_pub.uom_table;
1869 l_rs_locator_id_tbl inv_lot_trx_validation_pub.number_table;
1870 l_rs_sub_code_tbl inv_lot_trx_validation_pub.sub_code_table;
1871 l_rs_lpn_id_tbl inv_lot_trx_validation_pub.number_table;
1872 l_rs_cost_group_id_tbl inv_lot_trx_validation_pub.number_table;
1873 l_st_lot_number_tbl inv_lot_trx_validation_pub.lot_number_table;
1874 l_rs_lot_number_tbl inv_lot_trx_validation_pub.lot_number_table;
1875 --Added for OSFM Support to Serialized Lot Items
1876 l_st_ser_number_tbl inv_lot_trx_validation_pub.serial_number_table;
1877 l_st_ser_parent_lot_tbl inv_lot_trx_validation_pub.parent_lot_table;
1878 l_rs_ser_parent_lot_tbl inv_lot_trx_validation_pub.parent_lot_table;
1879 l_rs_ser_number_tbl inv_lot_trx_validation_pub.serial_number_table;
1880 l_st_ser_status_tbl inv_lot_trx_validation_pub.number_table;
1881 l_rs_ser_status_tbl inv_lot_trx_validation_pub.number_table;
1882 l_st_ser_grp_mark_id_tbl inv_lot_trx_validation_pub.number_table;
1883 l_rs_ser_grp_mark_id_tbl inv_lot_trx_validation_pub.number_table;
1884 l_is_serial_control VARCHAR2 (1);
1885 l_st_ser_parent_sub_tbl inv_lot_trx_validation_pub.parent_sub_table;
1886 l_st_ser_parent_loc_tbl inv_lot_trx_validation_pub.parent_loc_table;
1887 --Added for OSFM Support to Serialized Lot Items
1888 l_st_status_id_tbl inv_lot_trx_validation_pub.number_table;
1889 l_rs_status_id_tbl inv_lot_trx_validation_pub.number_table;
1890 l_st_interface_id_tbl inv_lot_trx_validation_pub.number_table;
1891 l_rs_interface_id_tbl inv_lot_trx_validation_pub.number_table;
1892 l_rs_index NUMBER;
1893 l_count NUMBER;
1894 l_st_lot_attr_tbl inv_lot_sel_attr.lot_sel_attributes_tbl_type;
1895 l_rs_lot_attr_tbl inv_lot_sel_attr.lot_sel_attributes_tbl_type;
1896 l_lot_attr_tbl inv_lot_sel_attr.lot_sel_attributes_tbl_type;
1897 l_st_lot_exp_tbl inv_lot_trx_validation_pub.date_table;
1898 l_rs_lot_exp_tbl inv_lot_trx_validation_pub.date_table;
1899 l_acct_period_tbl inv_lot_trx_validation_pub.number_table;
1900 l_wms_installed VARCHAR2 (1);
1901 l_wms_enabled VARCHAR2 (1);
1902 l_wsm_enabled VARCHAR2 (1);
1903 l_st_dist_account_id NUMBER;
1904 l_rs_dist_account_id NUMBER;
1905 l_debug NUMBER
1906 := NVL (fnd_profile.VALUE ('INV_DEBUG_TRACE'), 0);
1907 l_is_serial_controlled VARCHAR2 (1);
1908 BEGIN
1909 IF (l_debug = 1)
1910 THEN
1911 print_debug ('inside validate_lot_split_trx', 'Validate_Lot_Split');
1912 print_debug ('calling populate_records', 'Validate_Lot_Split');
1913 print_debug ('breadcrumb 10', 'validate_lot_split_trx');
1914 END IF;
1915
1916 l_validation_status := 'Y';
1917 x_return_status := fnd_api.g_ret_sts_success;
1918
1919 BEGIN
1920 populate_records (x_validation_status => l_validation_status
1921 , x_return_status => x_return_status
1922 , x_st_interface_id_tbl => l_st_interface_id_tbl
1923 , x_st_item_id_tbl => l_st_item_id_tbl
1924 , x_st_org_id_tbl => l_st_org_id_tbl
1925 , x_st_revision_tbl => l_st_revision_tbl
1926 , x_st_sub_code_tbl => l_st_sub_code_tbl
1927 , x_st_locator_id_tbl => l_st_locator_id_tbl
1928 , x_st_lot_num_tbl => l_st_lot_number_tbl
1929 , x_st_ser_num_tbl => l_st_ser_number_tbl
1930 , x_st_ser_parent_lot_tbl => l_st_ser_parent_lot_tbl
1931 , x_rs_ser_parent_lot_tbl => l_rs_ser_parent_lot_tbl
1932 , x_rs_ser_num_tbl => l_rs_ser_number_tbl
1933 , x_st_ser_status_tbl => l_st_ser_status_tbl
1934 , x_rs_ser_status_tbl => l_rs_ser_status_tbl
1935 , x_st_ser_grp_mark_id_tbl => l_st_ser_grp_mark_id_tbl
1936 , x_rs_ser_grp_mark_id_tbl => l_rs_ser_grp_mark_id_tbl
1937 , x_st_ser_parent_sub_tbl => l_st_ser_parent_sub_tbl
1938 , x_st_ser_parent_loc_tbl => l_st_ser_parent_loc_tbl
1939 , x_st_lpn_id_tbl => l_st_lpn_id_tbl
1940 , x_st_quantity_tbl => l_st_quantity_tbl
1941 , x_st_cost_group_tbl => l_st_cost_group_id_tbl
1942 , x_st_uom_tbl => l_st_uom_tbl
1943 , x_st_status_id_tbl => l_st_status_id_tbl
1944 , x_rs_interface_id_tbl => l_rs_interface_id_tbl
1945 , x_rs_item_id_tbl => l_rs_item_id_tbl
1946 , x_rs_org_id_tbl => l_rs_org_id_tbl
1947 , x_rs_revision_tbl => l_rs_revision_tbl
1948 , x_rs_sub_code_tbl => l_rs_sub_code_tbl
1949 , x_rs_locator_id_tbl => l_rs_locator_id_tbl
1950 , x_rs_lot_num_tbl => l_rs_lot_number_tbl
1951 , x_rs_lpn_id_tbl => l_rs_lpn_id_tbl
1952 , x_rs_quantity_tbl => l_rs_quantity_tbl
1953 , x_rs_cost_group_tbl => l_rs_cost_group_id_tbl
1954 , x_rs_uom_tbl => l_rs_uom_tbl
1955 , x_rs_status_id_tbl => l_rs_status_id_tbl
1956 , x_st_lot_exp_tbl => l_st_lot_exp_tbl
1957 , x_rs_lot_exp_tbl => l_rs_lot_exp_tbl
1958 , x_transaction_type_id => l_transaction_type_id
1959 , x_acct_period_tbl => l_acct_period_tbl
1960 , x_st_dist_account_id => l_st_dist_account_id
1961 , x_rs_dist_account_id => l_rs_dist_account_id
1962 , p_parent_id => p_parent_id
1963 );
1964 EXCEPTION
1965 WHEN OTHERS
1966 THEN
1967 IF (l_debug = 1)
1968 THEN
1969 print_debug ('Populate_records raised error'
1970 , 'Validate_lot_Split_Trx'
1971 );
1972 END IF;
1973
1974 l_validation_status := 'N';
1975 fnd_message.set_name ('INV', 'INV_RETRIEVE_RECORD');
1976 RAISE fnd_api.g_exc_unexpected_error;
1977 END;
1978
1979 IF (x_return_status <> fnd_api.g_ret_sts_success)
1980 THEN
1981 l_validation_status := 'N';
1982 RAISE fnd_api.g_exc_error;
1983 ELSIF (l_validation_status <> 'Y')
1984 THEN
1985 RAISE fnd_api.g_exc_error;
1986 END IF;
1987
1988 IF (l_debug = 1)
1989 THEN
1990 print_debug ('breadcrumb 20', 'validate_lot_split_trx');
1991 END IF;
1992 /* Removing the check...
1993 -- If wms is not installed and wsm is not enabled, we do not support lot transactions through the interface
1994 inv_lot_trx_validation_pub.get_org_info
1995 (x_wms_installed => l_wms_installed
1996 , x_wsm_enabled => l_wsm_enabled
1997 , x_wms_enabled => l_wms_enabled
1998 , x_return_status => x_return_status
1999 , x_msg_count => x_msg_count
2000 , x_msg_data => x_msg_data
2001 , p_organization_id => l_st_org_id_tbl
2002 (1)
2003 );
2004
2005 IF (l_debug = 1)
2006 THEN
2007 print_debug ('breadcrumb 30', 'validate_lot_split_trx');
2008 END IF;
2009
2010 IF (x_return_status = fnd_api.g_ret_sts_error)
2011 THEN
2012 l_validation_status := 'N';
2013 RAISE fnd_api.g_exc_error;
2014 ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
2015 THEN
2016 l_validation_status := 'N';
2017 RAISE fnd_api.g_exc_unexpected_error;
2018 END IF;
2019
2020 IF (l_debug = 1)
2021 THEN
2022 print_debug ('breadcrumb 40', 'validate_lot_split_trx');
2023 END IF;
2024
2025 IF ((NVL (l_wsm_enabled, 'N') = 'N')
2026 AND (NVL (l_wms_installed, 'N') = 'N')
2027 )
2028 THEN
2029 -- raise error
2030 IF (l_debug = 1)
2031 THEN
2032 print_debug ('breadcrumb 50', 'validate_lot_split_trx');
2033 END IF;
2034
2035 print_debug ('Validation failed on wsm/wms install'
2036 , 'Validate_lot_Split_Trx'
2037 );
2038 fnd_message.set_name ('WMS', 'WMS_NOT_INSTALLED');
2039 fnd_msg_pub.ADD;
2040 RAISE fnd_api.g_exc_error;
2041 END IF;
2042 */
2043 IF (l_debug = 1)
2044 THEN
2045 print_debug ('calling Validate_Organization', 'Validate_lot_Split_Trx');
2046 print_debug ('breadcrumb 60', 'validate_lot_split_trx');
2047 END IF;
2048
2049 BEGIN
2050 inv_lot_trx_validation_pub.validate_organization
2051 (x_return_status => x_return_status
2052 , x_msg_count => x_msg_count
2053 , x_msg_data => x_msg_data
2054 , x_validation_status => l_validation_status
2055 , p_organization_id => l_st_org_id_tbl
2056 (1)
2057 , p_period_tbl => l_acct_period_tbl
2058 );
2059 EXCEPTION
2060 WHEN OTHERS
2061 THEN
2062 fnd_message.set_name ('INV', 'INV_RETRIEVE_PERIOD');
2063 fnd_msg_pub.ADD;
2064 RAISE fnd_api.g_exc_unexpected_error;
2065 END;
2066
2067 IF ( x_return_status <> fnd_api.g_ret_sts_success
2068 OR l_validation_status <> 'Y'
2069 )
2070 THEN
2071 RAISE fnd_api.g_exc_error;
2072 END IF;
2073
2074 IF (l_debug = 1)
2075 THEN
2076 print_debug ('calling validate_lots', 'Validate_lot_Split_Trx');
2077 print_debug ('breadcrumb 70', 'validate_lot_split_trx');
2078 END IF;
2079
2080 BEGIN
2081 inv_lot_trx_validation_pub.validate_lots
2082 (x_return_status => x_return_status
2083 , x_msg_count => x_msg_count
2084 , x_msg_data => x_msg_data
2085 , x_validation_status => l_validation_status
2086 , p_transaction_type_id => l_transaction_type_id
2087 , p_st_org_id_tbl => l_st_org_id_tbl
2088 , p_st_item_id_tbl => l_st_item_id_tbl
2089 , p_st_lot_num_tbl => l_st_lot_number_tbl
2090 , p_rs_org_id_tbl => l_rs_org_id_tbl
2091 , p_rs_item_id_tbl => l_rs_item_id_tbl
2092 , p_rs_lot_num_tbl => l_rs_lot_number_tbl
2093 , p_st_lot_exp_tbl => l_st_lot_exp_tbl
2094 , p_rs_lot_exp_tbl => l_rs_lot_exp_tbl
2095 , p_st_revision_tbl => l_st_revision_tbl
2096 , p_rs_revision_tbl => l_rs_revision_tbl
2097 , p_st_quantity_tbl => l_st_quantity_tbl
2098 , p_rs_quantity_tbl => l_rs_quantity_tbl
2099 );
2100 EXCEPTION
2101 WHEN OTHERS
2102 THEN
2103 IF (l_debug = 1)
2104 THEN
2105 print_debug ('breadcrumb 80', 'validate_lot_split_trx');
2106 END IF;
2107
2108 fnd_message.set_name ('WMS', 'WMS_VALIDATE_LOT_ERROR');
2109 fnd_msg_pub.ADD;
2110 RAISE fnd_api.g_exc_unexpected_error;
2111 END;
2112
2113 IF ( x_return_status <> fnd_api.g_ret_sts_success
2114 OR l_validation_status <> 'Y'
2115 )
2116 THEN
2117 IF (l_debug = 1)
2118 THEN
2119 print_debug ('breadcrumb 90', 'validate_lot_split_trx');
2120 END IF;
2121
2122 RAISE fnd_api.g_exc_error;
2123 END IF;
2124
2125 IF (l_debug = 1)
2126 THEN
2127 print_debug ('calling validate_material_status'
2128 , 'Validate_lot_Split_Trx'
2129 );
2130 END IF;
2131
2132 BEGIN
2133 IF (l_debug = 1)
2134 THEN
2135 print_debug ('breadcrumb 100', 'validate_lot_split_trx');
2136 END IF;
2137
2138 inv_lot_trx_validation_pub.validate_material_status
2139 (x_return_status => x_return_status
2140 , x_msg_count => x_msg_count
2141 , x_msg_data => x_msg_data
2142 , x_validation_status => l_validation_status
2143 , p_transaction_type_id => l_transaction_type_id
2144 , p_organization_id => l_st_org_id_tbl
2145 (1)
2146 , p_inventory_item_id => l_st_item_id_tbl
2147 (1)
2148 , p_lot_number => l_st_lot_number_tbl
2149 (1)
2150 , p_subinventory_code => l_st_sub_code_tbl
2151 (1)
2152 , p_locator_id => l_st_locator_id_tbl
2153 (1)
2154 , p_status_id => l_st_status_id_tbl
2155 (1)
2156 );
2157 EXCEPTION
2158 WHEN OTHERS
2159 THEN
2160 IF (l_debug = 1)
2161 THEN
2162 print_debug ('breadcrumb 110', 'validate_lot_split_trx');
2163 END IF;
2164
2165 fnd_message.set_name ('WMS', 'WMS_VALIDATE_STATUS_ERROR');
2166 fnd_msg_pub.ADD;
2167 fnd_msg_pub.count_and_get (p_count => x_msg_count
2168 , p_data => x_msg_data
2169 );
2170 RAISE fnd_api.g_exc_unexpected_error;
2171 END;
2172
2173 IF (l_debug = 1)
2174 THEN
2175 print_debug ('After calling validate_material_status'
2176 , 'Validate_lot_Split_Trx'
2177 );
2178 print_debug ('Message Count' || x_msg_count, 'Validate_lot_Split_Trx');
2179 print_debug ('Return Status' || x_return_status
2180 , 'Validate_lot_Split_Trx'
2181 );
2182 END IF;
2183
2184 IF ( x_return_status <> fnd_api.g_ret_sts_success
2185 OR l_validation_status <> 'Y'
2186 )
2187 THEN
2188 IF (l_debug = 1)
2189 THEN
2190 print_debug ('breadcrumb 120', 'validate_lot_split_trx');
2191 END IF;
2192
2193 fnd_message.set_name ('WMS', 'WMS_VALIDATE_STATUS_ERROR');
2194 fnd_msg_pub.ADD;
2195 fnd_msg_pub.count_and_get (p_count => x_msg_count
2196 , p_data => x_msg_data);
2197 RAISE fnd_api.g_exc_error;
2198 END IF;
2199
2200 BEGIN
2201 SELECT transaction_action_id
2202 INTO l_transaction_action_id
2203 FROM mtl_transaction_types
2204 WHERE transaction_type_id = l_transaction_type_id;
2205 EXCEPTION
2206 WHEN NO_DATA_FOUND
2207 THEN
2208 IF (l_debug = 1)
2209 THEN
2210 print_debug ('breadcrumb 130', 'validate_lot_split_trx');
2211 END IF;
2212
2213 fnd_message.set_name ('INV', 'INV_INT_TRX_TYPE');
2214 fnd_msg_pub.ADD;
2215 RAISE fnd_api.g_exc_unexpected_error;
2216 WHEN OTHERS
2217 THEN
2218 IF (l_debug = 1)
2219 THEN
2220 print_debug ('breadcrumb 140', 'validate_lot_split_trx');
2221 END IF;
2222
2223 fnd_message.set_name ('INV', 'INV_INT_TRX_TYPE');
2224 fnd_msg_pub.ADD;
2225 RAISE fnd_api.g_exc_unexpected_error;
2226 END;
2227
2228 IF (l_debug = 1)
2229 THEN
2230 print_debug ('calling validate_cost_groups', 'Validate_lot_Split_Trx');
2231 END IF;
2232
2233 BEGIN
2234 IF (l_debug = 1)
2235 THEN
2236 print_debug ('breadcrumb 150', 'validate_lot_split_trx');
2237 END IF;
2238
2239 inv_lot_trx_validation_pub.validate_cost_groups
2240 (x_rs_cost_group_tbl => l_rs_cost_group_id_tbl
2241 , x_return_status => x_return_status
2242 , x_msg_count => x_msg_count
2243 , x_msg_data => x_msg_data
2244 , x_validation_status => l_validation_status
2245 , p_transaction_type_id => l_transaction_type_id
2246 , p_transaction_action_id => l_transaction_action_id
2247 , p_st_org_id_tbl => l_st_org_id_tbl
2248 , p_st_item_id_tbl => l_st_item_id_tbl
2249 , p_st_sub_code_tbl => l_st_sub_code_tbl
2250 , p_st_loc_id_tbl => l_st_locator_id_tbl
2251 , p_st_lot_num_tbl => l_st_lot_number_tbl
2252 , p_st_cost_group_tbl => l_st_cost_group_id_tbl
2253 , p_st_revision_tbl => l_st_revision_tbl
2254 , p_st_lpn_id_tbl => l_st_lpn_id_tbl
2255 , p_rs_org_id_tbl => l_rs_org_id_tbl
2256 , p_rs_item_id_tbl => l_rs_org_id_tbl
2257 , p_rs_sub_code_tbl => l_rs_sub_code_tbl
2258 , p_rs_loc_id_tbl => l_rs_locator_id_tbl
2259 , p_rs_lot_num_tbl => l_rs_lot_number_tbl
2260 , p_rs_revision_tbl => l_rs_revision_tbl
2261 , p_rs_lpn_id_tbl => l_rs_lpn_id_tbl
2262 );
2263 EXCEPTION
2264 WHEN OTHERS
2265 THEN
2266 IF (l_debug = 1)
2267 THEN
2268 print_debug ('breadcrumb 160', 'validate_lot_split_trx');
2269 END IF;
2270
2271 fnd_message.set_name ('WMS', 'VALIDATE_COST_GROUP_ERROR');
2272 fnd_msg_pub.ADD;
2273 RAISE fnd_api.g_exc_unexpected_error;
2274 END;
2275
2276 IF (x_return_status <> fnd_api.g_ret_sts_success)
2277 THEN
2278 RAISE fnd_api.g_exc_error;
2279 ELSE
2280 IF (l_validation_status <> 'Y')
2281 THEN
2282 RAISE fnd_api.g_exc_error;
2283 ELSE
2284 FOR i IN 1 .. l_rs_interface_id_tbl.COUNT
2285 LOOP
2286 UPDATE mtl_transactions_interface
2287 SET cost_group_id = l_rs_cost_group_id_tbl (i)
2288 WHERE transaction_interface_id = l_rs_interface_id_tbl (i);
2289 END LOOP;
2290 END IF;
2291 END IF;
2292
2293 IF (l_debug = 1)
2294 THEN
2295 print_debug ('calling validate_quantity', 'Validate_lot_Split_Trx');
2296 END IF;
2297
2298 BEGIN
2299 IF (l_debug = 1)
2300 THEN
2301 print_debug ('breadcrumb 170', 'validate_lot_split_trx');
2302 END IF;
2303
2304
2305 inv_lot_trx_validation_pub.validate_quantity
2306 (x_return_status => x_return_status
2307 , x_msg_count => x_msg_count
2308 , x_msg_data => x_msg_data
2309 , x_validation_status => l_validation_status
2310 , p_transaction_type_id => l_transaction_type_id
2311 , p_st_org_id_tbl => l_st_org_id_tbl
2312 , p_st_item_id_tbl => l_st_item_id_tbl
2313 , p_st_sub_code_tbl => l_st_sub_code_tbl
2314 , p_st_loc_id_tbl => l_st_locator_id_tbl
2315 , p_st_lot_num_tbl => l_st_lot_number_tbl
2316 , p_st_cost_group_tbl => l_st_cost_group_id_tbl
2317 , p_st_revision_tbl => l_st_revision_tbl
2318 , p_st_lpn_id_tbl => l_st_lpn_id_tbl
2319 , p_st_quantity_tbl => l_st_quantity_tbl
2320 , p_st_uom_tbl => l_st_uom_tbl
2321 , p_st_ser_number_tbl => l_st_ser_number_tbl
2322 , p_st_ser_parent_lot_tbl => l_st_ser_parent_lot_tbl
2323 , p_rs_org_id_tbl => l_rs_org_id_tbl
2324 , p_rs_item_id_tbl => l_rs_item_id_tbl
2325 , p_rs_sub_code_tbl => l_rs_sub_code_tbl
2326 , p_rs_loc_id_tbl => l_rs_locator_id_tbl
2327 , p_rs_lot_num_tbl => l_rs_lot_number_tbl
2328 , p_rs_cost_group_tbl => l_rs_cost_group_id_tbl
2329 , p_rs_revision_tbl => l_rs_revision_tbl
2330 , p_rs_lpn_id_tbl => l_rs_lpn_id_tbl
2331 , p_rs_quantity_tbl => l_rs_quantity_tbl
2332 , p_rs_uom_tbl => l_rs_uom_tbl
2333 , p_rs_ser_number_tbl => l_rs_ser_number_tbl
2334 , p_rs_ser_parent_lot_tbl => l_rs_ser_parent_lot_tbl
2335 );
2336 EXCEPTION
2337 WHEN OTHERS
2338 THEN
2339 IF (l_debug = 1)
2340 THEN
2341 print_debug ('breadcrumb 180', 'validate_lot_split_trx');
2342 print_debug ('validate_quantity raised exception'
2343 , 'Validate_lot_Split_Trx'
2344 );
2345 END IF;
2346
2347 fnd_message.set_name ('WMS', 'WMS_VALIDATE_QUANTITY_ERROR');
2348 fnd_msg_pub.ADD;
2349 RAISE fnd_api.g_exc_unexpected_error;
2350 END;
2351
2352 IF ( x_return_status <> fnd_api.g_ret_sts_success
2353 OR l_validation_status <> 'Y'
2354 )
2355 THEN
2356 print_debug ('breadcrumb 190', 'validate_lot_split_trx');
2357 print_debug ('validate_quantity returned with Error'
2358 , 'Validate_lot_Split_Trx'
2359 );
2360 RAISE fnd_api.g_exc_error;
2361 END IF;
2362
2363 print_debug ('calling get_lot_attr_record for parent record'
2364 , 'Validate_lot_Split_Trx'
2365 );
2366 /*Added LPN Validations */
2367 BEGIN
2368 IF (l_debug = 1)
2369 THEN
2370 print_debug ('breadcrumb 191', 'validate_lot_split_trx');
2371 END IF;
2372
2373
2374 inv_lot_trx_validation_pub.validate_lpn_info
2375 (x_return_status => x_return_status
2376 , x_msg_count => x_msg_count
2377 , x_msg_data => x_msg_data
2378 , x_validation_status => l_validation_status
2379 , p_st_lpn_id_tbl => l_st_lpn_id_tbl
2380 , p_rs_lpn_id_tbl => l_rs_lpn_id_tbl
2381 , p_st_org_id_tbl => l_st_org_id_tbl
2382 , p_rs_org_id_tbl => l_rs_org_id_tbl
2383 , p_rs_sub_code_tbl => l_rs_sub_code_tbl
2384 , p_rs_locator_id_tbl => l_rs_locator_id_tbl
2385 );
2386 EXCEPTION
2387 WHEN OTHERS
2388 THEN
2389 IF (l_debug = 1)
2390 THEN
2391 print_debug ('breadcrumb 195', 'validate_lot_split_trx');
2392 print_debug ('validate_lpn_info raised exception'
2393 , 'Validate_lot_Split_Trx'
2394 );
2395 END IF;
2396
2397 fnd_message.set_name ('INV', 'INV_INT_LPN');
2398 fnd_msg_pub.ADD;
2399 RAISE fnd_api.g_exc_unexpected_error;
2400 END;
2401
2402 IF ( x_return_status <> fnd_api.g_ret_sts_success
2403 OR l_validation_status <> 'Y'
2404 )
2405 THEN
2406 print_debug ('breadcrumb 196', 'validate_lot_split_trx');
2407 print_debug ('validate_lpn_info returned with Error'
2408 , 'Validate_lot_Split_Trx'
2409 );
2410 RAISE fnd_api.g_exc_error;
2411 END IF;
2412 /*End of LPN Validations */
2413
2414 BEGIN
2415 BEGIN
2416 IF (l_debug = 1)
2417 THEN
2418 print_debug ('breadcrumb 200', 'validate_lot_split_trx');
2419 print_debug ('Determine the serial control code'
2420 , 'Validate_Lot_Split'
2421 );
2422 END IF;
2423
2424 SELECT DECODE (serial_number_control_code, 2, 'Y', 5, 'Y', 'N')
2425 INTO l_is_serial_controlled
2426 FROM mtl_system_items
2427 WHERE inventory_item_id = l_st_item_id_tbl (1)
2428 AND organization_id = l_st_org_id_tbl (1);
2429 EXCEPTION
2430 WHEN OTHERS
2431 THEN
2432 IF (l_debug = 1)
2433 THEN
2434 print_debug ('breadcrumb 210', 'validate_lot_split_trx');
2435 print_debug ('Cannot fetch the serial control code for the item'
2436 , 'Validate_lot_Split_Trx'
2437 );
2438 END IF;
2439
2440 l_validation_status := 'N';
2441 RAISE fnd_api.g_exc_unexpected_error;
2442 END;
2443
2444 IF (l_debug = 1)
2445 THEN
2446 print_debug ('l_is_serial_controlled ' || l_is_serial_controlled
2447 , 'Validate_Lot_Split'
2448 );
2449 END IF;
2450
2451 IF (l_is_serial_controlled = 'Y')
2452 THEN
2453 IF ( l_st_ser_number_tbl.COUNT = 0
2454 OR l_rs_ser_number_tbl.COUNT = 0
2455 OR l_st_ser_number_tbl.COUNT < l_rs_ser_number_tbl.COUNT
2456 )
2457 THEN
2458 IF (l_debug = 1)
2459 THEN
2460 print_debug ('breadcrumb 220', 'validate_lot_split_trx');
2461 print_debug
2462 ('Either the Serial records are empty or starting and result serial records do not match'
2463 , 'Validate_lot_Split_Trx'
2464 );
2465 END IF;
2466
2467 l_validation_status := 'N';
2468 fnd_message.set_name ('INV', 'INV_SERIAL_INFO_MISSING');
2469 fnd_msg_pub.ADD;
2470 RAISE fnd_api.g_exc_error;
2471 ELSE
2472 BEGIN
2473 IF (l_debug = 1)
2474 THEN
2475 print_debug ('Calling validate_serials'
2476 , 'Validate_lot_Split_Trx'
2477 );
2478 END IF;
2479
2480 inv_lot_trx_validation_pub.validate_serials
2481 (x_return_status => x_return_status
2482 , x_msg_count => x_msg_count
2483 , x_msg_data => x_msg_data
2484 , x_validation_status => l_validation_status
2485 , p_transaction_type_id => l_transaction_type_id
2486 , p_st_org_id_tbl => l_st_org_id_tbl
2487 , p_rs_org_id_tbl => l_rs_org_id_tbl
2488 , p_st_item_id_tbl => l_st_item_id_tbl
2489 , p_rs_item_id_tbl => l_rs_item_id_tbl
2490 , p_st_quantity_tbl => l_st_quantity_tbl
2491 --Needed for status control check
2492 , p_st_sub_code_tbl => l_st_sub_code_tbl
2493 , p_st_locator_id_tbl => l_st_locator_id_tbl
2494 , p_st_ser_parent_lot_tbl => l_st_ser_parent_lot_tbl
2495 , p_rs_lot_num_tbl => l_rs_lot_number_tbl
2496 , p_st_ser_number_tbl => l_st_ser_number_tbl
2497 , p_rs_ser_number_tbl => l_rs_ser_number_tbl
2498 , p_st_ser_status_tbl => l_st_ser_status_tbl
2499 , p_st_ser_grp_mark_id_tbl => l_st_ser_grp_mark_id_tbl
2500 , p_st_ser_parent_sub_tbl => l_st_ser_parent_sub_tbl
2501 , p_st_ser_parent_loc_tbl => l_st_ser_parent_loc_tbl
2502 );
2503 EXCEPTION
2504 WHEN OTHERS
2505 THEN
2506 IF (l_debug = 1)
2507 THEN
2508 print_debug ('breadcrumb 230', 'validate_lot_split_trx');
2509 print_debug ('Validate_serials has raised exception'
2510 , 'Validate_lot_Split_Trx'
2511 );
2512 END IF;
2513
2514 l_validation_status := 'N';
2515 fnd_message.set_name ('INV', 'INV_FAIL_VALIDATE_SERIAL');
2516 fnd_msg_pub.ADD;
2517 RAISE fnd_api.g_exc_unexpected_error;
2518 END;
2519
2520 IF ( x_return_status <> fnd_api.g_ret_sts_success
2521 OR l_validation_status <> 'Y'
2522 )
2523 THEN
2524 print_debug ('breadcrumb 240', 'validate_lot_split_trx');
2525 print_debug ('Validate_serials returned with error code'
2526 , 'Validate_lot_Split_Trx'
2527 );
2528 RAISE fnd_api.g_exc_error;
2529 END IF;
2530 END IF;
2531
2532 IF (l_debug = 1)
2533 THEN
2534 print_debug ('breadcrumb 250', 'validate_lot_split_trx');
2535 print_debug ('Validate_serials returned with success'
2536 , 'Validate_lot_Split_Trx'
2537 );
2538 END IF;
2539 END IF; --is lot serial controlled
2540 EXCEPTION
2541 WHEN OTHERS
2542 THEN
2543 IF (l_debug = 1)
2544 THEN
2545 print_debug ('breadcrumb 260', 'validate_lot_split_trx');
2546 print_debug ('Error while validating serials'
2547 , 'Validate_lot_Split_Trx'
2548 );
2549 END IF;
2550
2551 l_validation_status := 'N';
2552 fnd_message.set_name ('INV', 'INV_FAIL_VALIDATE_SERIAL');
2553 fnd_msg_pub.ADD;
2554 RAISE fnd_api.g_exc_unexpected_error;
2555 END;
2556
2557 /*Bug:5354721. The following procedure populates the column name, type and
2558 length for all the Lot Attributes. */
2559 get_lot_attr_table;
2560
2561 BEGIN
2562 get_lot_attr_record
2563 (x_lot_attr_tbl => l_st_lot_attr_tbl
2564 , p_transaction_interface_id => l_st_interface_id_tbl
2565 (1)
2566 , p_lot_number => l_st_lot_number_tbl
2567 (1)
2568 , p_starting_lot_number => l_st_lot_number_tbl
2569 (1)
2570 , p_organization_id => l_st_org_id_tbl (1)
2571 , p_inventory_item_id => l_st_item_id_tbl (1)
2572 );
2573 EXCEPTION
2574 WHEN OTHERS
2575 THEN
2576 fnd_message.set_name ('WMS', 'WMS_GET_LOT_ATTR_ERROR');
2577 fnd_msg_pub.ADD;
2578 l_validation_status := 'N';
2579 RAISE fnd_api.g_exc_unexpected_error;
2580 END;
2581
2582 FOR i IN 1 .. l_rs_interface_id_tbl.COUNT
2583 LOOP
2584 IF (l_debug = 1)
2585 THEN
2586 print_debug ('breadcrumb 270', 'validate_lot_split_trx');
2587 print_debug ('calling get_lot_attr_record for resultant records'
2588 , 'Validate_lot_Split_Trx'
2589 );
2590 END IF;
2591
2592 BEGIN
2593 get_lot_attr_record
2594 (x_lot_attr_tbl => l_rs_lot_attr_tbl
2595 , p_transaction_interface_id => l_rs_interface_id_tbl
2596 (i)
2597 , p_lot_number => l_rs_lot_number_tbl
2598 (i)
2599 , p_starting_lot_number => l_st_lot_number_tbl
2600 (1)
2601 , p_organization_id => l_rs_org_id_tbl (i)
2602 , p_inventory_item_id => l_rs_item_id_tbl (i)
2603 );
2604 EXCEPTION
2605 WHEN OTHERS
2606 THEN
2607 fnd_message.set_name ('WMS', 'WMS_GET_LOT_ATTR_ERROR');
2608 fnd_msg_pub.ADD;
2609 l_validation_status := 'N';
2610 RAISE fnd_api.g_exc_unexpected_error;
2611 END;
2612
2613 IF (l_debug = 1)
2614 THEN
2615 print_debug ('breadcrumb 280', 'validate_lot_split_trx');
2616 print_debug ('calling validate_attributes', 'Validate_lot_split_trx');
2617 END IF;
2618
2619 BEGIN
2620 inv_lot_trx_validation_pub.validate_attributes
2621 (x_return_status => x_return_status
2622 , x_msg_count => x_msg_count
2623 , x_msg_data => x_msg_data
2624 , x_validation_status => l_validation_status
2625 , x_lot_attr_tbl => l_lot_attr_tbl
2626 , p_lot_number => l_st_lot_number_tbl
2627 (1)
2628 , p_organization_id => l_rs_org_id_tbl
2629 (i)
2630 , p_inventory_item_id => l_rs_item_id_tbl
2631 (i)
2632 , p_parent_lot_attr_tbl => l_st_lot_attr_tbl
2633 , p_result_lot_attr_tbl => l_rs_lot_attr_tbl
2634 , p_transaction_type_id => l_transaction_type_id
2635 );
2636 EXCEPTION
2637 WHEN OTHERS
2638 THEN
2639 fnd_message.set_name ('WMS', 'WMS_VALIDATE_ATTR_ERROR');
2640 fnd_msg_pub.ADD;
2641 RAISE fnd_api.g_exc_unexpected_error;
2642 END;
2643
2644 IF ( x_return_status <> fnd_api.g_ret_sts_success
2645 OR l_validation_status <> 'Y'
2646 )
2647 THEN
2648 RAISE fnd_api.g_exc_error;
2649 ELSE
2650 IF (l_lot_attr_tbl.COUNT > 0)
2651 THEN
2652 -- this means user does not provide the lot attribute for the result lot.
2653 -- we need to update the mtl_transation_lots_interface with the parent
2654 -- lot attributes if it exists or use default lot attributes
2655 IF (l_debug = 1)
2656 THEN
2657 print_debug ('calling update_lot_attr_record'
2658 , 'validate_lot_split_trx'
2659 );
2660 END IF;
2661
2662 update_lot_attr_record
2663 (p_lot_attr_tbl => l_lot_attr_tbl
2664 , p_transaction_interface_id => l_rs_interface_id_tbl
2665 (i)
2666 , p_lot_number => l_rs_lot_number_tbl
2667 (i)
2668 , p_organization_id => l_rs_org_id_tbl (i)
2669 , p_inventory_item_id => l_rs_item_id_tbl (i)
2670 );
2671 END IF;
2672 END IF;
2673 END LOOP;
2674
2675 -- Call to compute the correct expiration date
2676 BEGIN
2677 inv_lot_trx_validation_pub.compute_lot_expiration
2678 (x_return_status => x_return_status
2679 , x_msg_count => x_msg_count
2680 , x_msg_data => x_msg_data
2681 , p_parent_id => p_parent_id
2682 , p_transaction_type_id => l_transaction_type_id
2683 , p_item_id => l_st_item_id_tbl
2684 (1)
2685 , p_organization_id => l_st_org_id_tbl (1)
2686 , p_st_lot_num => l_st_lot_number_tbl
2687 (1)
2688 , p_rs_lot_num_tbl => l_rs_lot_number_tbl
2689 , p_rs_lot_exp_tbl => l_rs_lot_exp_tbl
2690 );
2691 EXCEPTION
2692 WHEN OTHERS
2693 THEN
2694 fnd_message.set_name ('INV', 'INV_LOT_EXP_COMPUTE_ERROR');
2695 fnd_msg_pub.ADD;
2696 RAISE fnd_api.g_exc_unexpected_error;
2697 END;
2698
2699 IF (x_return_status <> fnd_api.g_ret_sts_success)
2700 THEN
2701 fnd_message.set_name ('INV', 'INV_LOT_EXP_COMPUTE_ERROR');
2702 fnd_msg_pub.ADD;
2703 l_validation_status := 'N';
2704 RAISE fnd_api.g_exc_error;
2705 END IF;
2706
2707 -- if we reach here, it means all validations are successfull
2708 x_return_status := fnd_api.g_ret_sts_success;
2709 x_validation_status := 'Y';
2710 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
2711 EXCEPTION
2712 WHEN fnd_api.g_exc_error
2713 THEN
2714 x_return_status := fnd_api.g_ret_sts_error;
2715 x_validation_status := l_validation_status;
2716 fnd_msg_pub.count_and_get (p_count => x_msg_count
2717 , p_data => x_msg_data);
2718 WHEN fnd_api.g_exc_unexpected_error
2719 THEN
2720 x_return_status := fnd_api.g_ret_sts_unexp_error;
2721 x_validation_status := l_validation_status;
2722 fnd_msg_pub.count_and_get (p_count => x_msg_count
2723 , p_data => x_msg_data);
2724 WHEN OTHERS
2725 THEN
2726 x_return_status := fnd_api.g_ret_sts_unexp_error;
2727 x_validation_status := 'E';
2728
2729 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
2730 THEN
2731 fnd_msg_pub.add_exc_msg (g_pkg_name, 'Validate_Lot_Split_Trx');
2732 END IF;
2733
2734 fnd_msg_pub.count_and_get (p_count => x_msg_count
2735 , p_data => x_msg_data);
2736 END validate_lot_split_trx;
2737
2738 PROCEDURE validate_lot_merge_trx (
2739 x_return_status OUT NOCOPY VARCHAR2
2740 , x_msg_count OUT NOCOPY NUMBER
2741 , x_msg_data OUT NOCOPY VARCHAR2
2742 , x_validation_status OUT NOCOPY VARCHAR2
2743 , p_parent_id IN NUMBER
2744 )
2745 IS
2746 l_return_status VARCHAR2 (1);
2747 l_msg_count NUMBER;
2748 l_msg_data VARCHAR2 (255);
2749 l_validation_status VARCHAR2 (1);
2750 l_transaction_type_id NUMBER;
2751 -- l_acct_period_id NUMBER;
2752 l_transaction_interface_id NUMBER;
2753 l_transaction_action_id NUMBER;
2754 l_st_item_id_tbl inv_lot_trx_validation_pub.number_table;
2755 l_st_org_id_tbl inv_lot_trx_validation_pub.number_table;
2756 l_st_revision_tbl inv_lot_trx_validation_pub.revision_table;
2757 l_st_quantity_tbl inv_lot_trx_validation_pub.number_table;
2758 l_st_uom_tbl inv_lot_trx_validation_pub.uom_table;
2759 l_st_locator_id_tbl inv_lot_trx_validation_pub.number_table;
2760 l_st_sub_code_tbl inv_lot_trx_validation_pub.sub_code_table;
2761 l_st_cost_group_id_tbl inv_lot_trx_validation_pub.number_table;
2762 l_st_lpn_id_tbl inv_lot_trx_validation_pub.number_table;
2763 l_rs_item_id_tbl inv_lot_trx_validation_pub.number_table;
2764 l_rs_org_id_tbl inv_lot_trx_validation_pub.number_table;
2765 l_rs_revision_tbl inv_lot_trx_validation_pub.revision_table;
2766 l_rs_quantity_tbl inv_lot_trx_validation_pub.number_table;
2767 l_rs_uom_tbl inv_lot_trx_validation_pub.uom_table;
2768 l_rs_locator_id_tbl inv_lot_trx_validation_pub.number_table;
2769 l_rs_sub_code_tbl inv_lot_trx_validation_pub.sub_code_table;
2770 l_rs_lpn_id_tbl inv_lot_trx_validation_pub.number_table;
2771 l_rs_cost_group_id_tbl inv_lot_trx_validation_pub.number_table;
2772 l_st_lot_number_tbl inv_lot_trx_validation_pub.lot_number_table;
2773 --Added for OSFM Support to Serialized Lot Items
2774 l_st_ser_number_tbl inv_lot_trx_validation_pub.serial_number_table;
2775 l_rs_ser_parent_lot_tbl inv_lot_trx_validation_pub.parent_lot_table;
2776 l_st_ser_parent_lot_tbl inv_lot_trx_validation_pub.parent_lot_table;
2777 l_rs_ser_number_tbl inv_lot_trx_validation_pub.serial_number_table;
2778 l_st_ser_status_tbl inv_lot_trx_validation_pub.number_table;
2779 l_rs_ser_status_tbl inv_lot_trx_validation_pub.number_table;
2780 l_st_ser_grp_mark_id_tbl inv_lot_trx_validation_pub.number_table;
2781 l_rs_ser_grp_mark_id_tbl inv_lot_trx_validation_pub.number_table;
2782 l_is_serial_controlled VARCHAR2 (1);
2783 l_st_ser_parent_sub_tbl inv_lot_trx_validation_pub.parent_sub_table;
2784 l_st_ser_parent_loc_tbl inv_lot_trx_validation_pub.parent_loc_table;
2785 --Added for OSFM Support to Serialized Lot Items
2786 l_rs_lot_number_tbl inv_lot_trx_validation_pub.lot_number_table;
2787 l_st_status_id_tbl inv_lot_trx_validation_pub.number_table;
2788 l_rs_status_id_tbl inv_lot_trx_validation_pub.number_table;
2789 l_st_interface_id_tbl inv_lot_trx_validation_pub.number_table;
2790 l_rs_interface_id_tbl inv_lot_trx_validation_pub.number_table;
2791 l_st_lot_exp_tbl inv_lot_trx_validation_pub.date_table;
2792 l_rs_lot_exp_tbl inv_lot_trx_validation_pub.date_table;
2793 l_rs_index NUMBER;
2794 l_count NUMBER;
2795 l_st_lot_attr_tbl inv_lot_sel_attr.lot_sel_attributes_tbl_type;
2796 l_rs_lot_attr_tbl inv_lot_sel_attr.lot_sel_attributes_tbl_type;
2797 l_lot_attr_tbl inv_lot_sel_attr.lot_sel_attributes_tbl_type;
2798 l_represenatative_lot mtl_transaction_lots_interface.lot_number%TYPE
2799 := NULL;
2800 l_max_lot_qty NUMBER := 0;
2801 l_lot_number mtl_transaction_lots_interface.lot_number%TYPE
2802 := NULL;
2803 l_acct_period_tbl inv_lot_trx_validation_pub.number_table;
2804 l_wms_installed VARCHAR2 (1);
2805 l_wms_enabled VARCHAR2 (1);
2806 l_wsm_enabled VARCHAR2 (1);
2807 l_st_dist_account_id NUMBER;
2808 l_rs_dist_account_id NUMBER;
2809 l_debug NUMBER
2810 := NVL (fnd_profile.VALUE ('INV_DEBUG_TRACE'), 0);
2811 BEGIN
2812 l_validation_status := 'Y';
2813 x_return_status := fnd_api.g_ret_sts_success;
2814
2815 IF (l_debug = 1)
2816 THEN
2817 print_debug ('calling populate records', 'validate_lot_merge_trx');
2818 END IF;
2819
2820 BEGIN
2821 populate_records (x_validation_status => l_validation_status
2822 , x_return_status => x_return_status
2823 , x_st_interface_id_tbl => l_st_interface_id_tbl
2824 , x_st_item_id_tbl => l_st_item_id_tbl
2825 , x_st_org_id_tbl => l_st_org_id_tbl
2826 , x_st_revision_tbl => l_st_revision_tbl
2827 , x_st_sub_code_tbl => l_st_sub_code_tbl
2828 , x_st_locator_id_tbl => l_st_locator_id_tbl
2829 , x_st_lot_num_tbl => l_st_lot_number_tbl
2830 , x_st_ser_num_tbl => l_st_ser_number_tbl
2831 , x_st_ser_parent_lot_tbl => l_st_ser_parent_lot_tbl
2832 , x_rs_ser_parent_lot_tbl => l_rs_ser_parent_lot_tbl
2833 , x_rs_ser_num_tbl => l_rs_ser_number_tbl
2834 , x_st_ser_status_tbl => l_st_ser_status_tbl
2835 , x_rs_ser_status_tbl => l_rs_ser_status_tbl
2836 , x_st_ser_grp_mark_id_tbl => l_st_ser_grp_mark_id_tbl
2837 , x_rs_ser_grp_mark_id_tbl => l_rs_ser_grp_mark_id_tbl
2838 , x_st_ser_parent_sub_tbl => l_st_ser_parent_sub_tbl
2839 , x_st_ser_parent_loc_tbl => l_st_ser_parent_loc_tbl
2840 , x_st_lpn_id_tbl => l_st_lpn_id_tbl
2841 , x_st_quantity_tbl => l_st_quantity_tbl
2842 , x_st_cost_group_tbl => l_st_cost_group_id_tbl
2843 , x_st_uom_tbl => l_st_uom_tbl
2844 , x_st_status_id_tbl => l_st_status_id_tbl
2845 , x_rs_interface_id_tbl => l_rs_interface_id_tbl
2846 , x_rs_item_id_tbl => l_rs_item_id_tbl
2847 , x_rs_org_id_tbl => l_rs_org_id_tbl
2848 , x_rs_revision_tbl => l_rs_revision_tbl
2849 , x_rs_sub_code_tbl => l_rs_sub_code_tbl
2850 , x_rs_locator_id_tbl => l_rs_locator_id_tbl
2851 , x_rs_lot_num_tbl => l_rs_lot_number_tbl
2852 , x_rs_lpn_id_tbl => l_rs_lpn_id_tbl
2853 , x_rs_quantity_tbl => l_rs_quantity_tbl
2854 , x_rs_cost_group_tbl => l_rs_cost_group_id_tbl
2855 , x_rs_uom_tbl => l_rs_uom_tbl
2856 , x_rs_status_id_tbl => l_rs_status_id_tbl
2857 , x_st_lot_exp_tbl => l_st_lot_exp_tbl
2858 , x_rs_lot_exp_tbl => l_rs_lot_exp_tbl
2859 , x_transaction_type_id => l_transaction_type_id
2860 , x_acct_period_tbl => l_acct_period_tbl
2861 , x_st_dist_account_id => l_st_dist_account_id
2862 , x_rs_dist_account_id => l_rs_dist_account_id
2863 , p_parent_id => p_parent_id
2864 );
2865 EXCEPTION
2866 WHEN OTHERS
2867 THEN
2868 IF (l_debug = 1)
2869 THEN
2870 print_debug ('Populate_records raised exception'
2871 , 'Validate_lot_merge_Trx'
2872 );
2873 END IF;
2874
2875 l_validation_status := 'N';
2876 fnd_message.set_name ('INV', 'INV_RETRIEVE_RECORD');
2877 RAISE fnd_api.g_exc_unexpected_error;
2878 END;
2879
2880 IF (x_return_status <> fnd_api.g_ret_sts_success)
2881 THEN
2882 l_validation_status := 'N';
2883 RAISE fnd_api.g_exc_error;
2884 ELSIF (l_validation_status <> 'Y')
2885 THEN
2886 RAISE fnd_api.g_exc_error;
2887 END IF;
2888 /*Removing the check...
2889 -- If wms is not installed and wsm is not enabled, we do not support lot transactions through the interface
2890 inv_lot_trx_validation_pub.get_org_info
2891 (x_wms_installed => l_wms_installed
2892 , x_wsm_enabled => l_wsm_enabled
2893 , x_wms_enabled => l_wms_enabled
2894 , x_return_status => x_return_status
2895 , x_msg_count => x_msg_count
2896 , x_msg_data => x_msg_data
2897 , p_organization_id => l_st_org_id_tbl
2898 (1)
2899 );
2900
2901 IF (x_return_status = fnd_api.g_ret_sts_error)
2902 THEN
2903 l_validation_status := 'N';
2904 RAISE fnd_api.g_exc_error;
2905 ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
2906 THEN
2907 l_validation_status := 'N';
2908 RAISE fnd_api.g_exc_unexpected_error;
2909 END IF;
2910
2911 IF ((NVL (l_wsm_enabled, 'N') = 'N')
2912 AND (NVL (l_wms_installed, 'N') = 'N')
2913 )
2914 THEN
2915 -- raise error
2916 print_debug ('Validation failed on wsm/wms install'
2917 , 'Validate_lot_Split_Trx'
2918 );
2919 fnd_message.set_name ('WMS', 'WMS_NOT_INSTALLED');
2920 fnd_msg_pub.ADD;
2921 l_validation_status := 'N';
2922 RAISE fnd_api.g_exc_error;
2923 END IF;
2924 */
2925 IF (l_debug = 1)
2926 THEN
2927 print_debug ('calling Validate_Organization', 'Validate_lot_Split_Trx');
2928 END IF;
2929
2930 BEGIN
2931 inv_lot_trx_validation_pub.validate_organization
2932 (x_return_status => x_return_status
2933 , x_msg_count => x_msg_count
2934 , x_msg_data => x_msg_data
2935 , x_validation_status => l_validation_status
2936 , p_organization_id => l_st_org_id_tbl
2937 (1)
2938 , p_period_tbl => l_acct_period_tbl
2939 );
2940 EXCEPTION
2941 WHEN OTHERS
2942 THEN
2943 fnd_message.set_name ('INV', 'INV_RETRIEVE_PERIOD');
2944 fnd_msg_pub.ADD;
2945 l_validation_status := 'N';
2946 RAISE fnd_api.g_exc_unexpected_error;
2947 END;
2948
2949 IF ( x_return_status <> fnd_api.g_ret_sts_success
2950 OR l_validation_status <> 'Y'
2951 )
2952 THEN
2953 RAISE fnd_api.g_exc_error;
2954 END IF;
2955
2956 IF (l_debug = 1)
2957 THEN
2958 print_debug ('calling validate_lots', 'validate_lot_merge_trx');
2959 END IF;
2960
2961 inv_lot_trx_validation_pub.validate_lots
2962 (x_return_status => x_return_status
2963 , x_msg_count => x_msg_count
2964 , x_msg_data => x_msg_data
2965 , x_validation_status => l_validation_status
2966 , p_transaction_type_id => l_transaction_type_id
2967 , p_st_org_id_tbl => l_st_org_id_tbl
2968 , p_st_item_id_tbl => l_st_item_id_tbl
2969 , p_st_lot_num_tbl => l_st_lot_number_tbl
2970 , p_rs_org_id_tbl => l_rs_org_id_tbl
2971 , p_rs_item_id_tbl => l_rs_item_id_tbl
2972 , p_rs_lot_num_tbl => l_rs_lot_number_tbl
2973 , p_st_lot_exp_tbl => l_st_lot_exp_tbl
2974 , p_rs_lot_exp_tbl => l_rs_lot_exp_tbl
2975 , p_st_revision_tbl => l_st_revision_tbl
2976 , p_rs_revision_tbl => l_rs_revision_tbl
2977 , p_st_quantity_tbl => l_st_quantity_tbl
2978 , p_rs_quantity_tbl => l_rs_quantity_tbl
2979 );
2980
2981 IF ( x_return_status <> fnd_api.g_ret_sts_success
2982 OR l_validation_status <> 'Y'
2983 )
2984 THEN
2985 RAISE fnd_api.g_exc_error;
2986 END IF;
2987
2988 IF (l_debug = 1)
2989 THEN
2990 print_debug ('calling validate_material_status'
2991 , 'validate_lot_merge_trx'
2992 );
2993 END IF;
2994
2995 FOR i IN 1 .. l_st_lot_number_tbl.COUNT
2996 LOOP
2997 inv_lot_trx_validation_pub.validate_material_status
2998 (x_return_status => x_return_status
2999 , x_msg_count => x_msg_count
3000 , x_msg_data => x_msg_data
3001 , x_validation_status => l_validation_status
3002 , p_transaction_type_id => l_transaction_type_id
3003 , p_organization_id => l_st_org_id_tbl (1)
3004 , p_inventory_item_id => l_st_item_id_tbl
3005 (1)
3006 , p_lot_number => l_st_lot_number_tbl
3007 (i)
3008 , p_subinventory_code => l_st_sub_code_tbl
3009 (i)
3010 , p_locator_id => l_st_locator_id_tbl
3011 (i)
3012 , p_status_id => l_st_status_id_tbl
3013 (i)
3014 );
3015
3016 IF (l_debug = 1)
3017 THEN
3018 print_debug ('After calling validate_material_status'
3019 , 'Validate_lot_Merge_Trx'
3020 );
3021 print_debug ('Lot Number is' || l_st_lot_number_tbl (i)
3022 , 'Validate_lot_Merge_Trx'
3023 );
3024 print_debug ('Status ID is' || l_st_status_id_tbl (i)
3025 , 'Validate_lot_Merge_Trx'
3026 );
3027 print_debug ('Message Count' || x_msg_count, 'Validate_lot_Merge_Trx');
3028 print_debug ('Return Status' || x_return_status
3029 , 'Validate_lot_merge_Trx'
3030 );
3031 END IF;
3032
3033 IF ( x_return_status <> fnd_api.g_ret_sts_success
3034 OR l_validation_status <> 'Y'
3035 )
3036 THEN
3037 fnd_message.set_name ('WMS', 'WMS_VALIDATE_STATUS_ERROR');
3038 fnd_msg_pub.ADD;
3039 RAISE fnd_api.g_exc_error;
3040 END IF;
3041 END LOOP;
3042
3043 BEGIN
3044 SELECT transaction_action_id
3045 INTO l_transaction_action_id
3046 FROM mtl_transaction_types
3047 WHERE transaction_type_id = l_transaction_type_id;
3048 EXCEPTION
3049 WHEN NO_DATA_FOUND
3050 THEN
3051 fnd_message.set_name ('INV', 'INV_INT_TRX_TYPE');
3052 fnd_msg_pub.ADD;
3053 l_validation_status := 'N';
3054 RAISE fnd_api.g_exc_unexpected_error;
3055 WHEN OTHERS
3056 THEN
3057 fnd_message.set_name ('INV', 'INV_INT_TRX_TYPE');
3058 fnd_msg_pub.ADD;
3059 l_validation_status := 'E';
3060 RAISE fnd_api.g_exc_unexpected_error;
3061 END;
3062
3063 IF (l_debug = 1)
3064 THEN
3065 print_debug ('calling validate_cost_groups', 'validate_lot_merge_trx');
3066 END IF;
3067
3068 inv_lot_trx_validation_pub.validate_cost_groups
3069 (x_rs_cost_group_tbl => l_rs_cost_group_id_tbl
3070 , x_return_status => x_return_status
3071 , x_msg_count => x_msg_count
3072 , x_msg_data => x_msg_data
3073 , x_validation_status => l_validation_status
3074 , p_transaction_type_id => l_transaction_type_id
3075 , p_transaction_action_id => l_transaction_action_id
3076 , p_st_org_id_tbl => l_st_org_id_tbl
3077 , p_st_item_id_tbl => l_st_item_id_tbl
3078 , p_st_sub_code_tbl => l_st_sub_code_tbl
3079 , p_st_loc_id_tbl => l_st_locator_id_tbl
3080 , p_st_lot_num_tbl => l_st_lot_number_tbl
3081 , p_st_cost_group_tbl => l_st_cost_group_id_tbl
3082 , p_st_revision_tbl => l_st_revision_tbl
3083 , p_st_lpn_id_tbl => l_st_lpn_id_tbl
3084 , p_rs_org_id_tbl => l_rs_org_id_tbl
3085 , p_rs_item_id_tbl => l_rs_org_id_tbl
3086 , p_rs_sub_code_tbl => l_rs_sub_code_tbl
3087 , p_rs_loc_id_tbl => l_rs_locator_id_tbl
3088 , p_rs_lot_num_tbl => l_rs_lot_number_tbl
3089 , p_rs_revision_tbl => l_rs_revision_tbl
3090 , p_rs_lpn_id_tbl => l_rs_lpn_id_tbl
3091 );
3092
3093 IF (x_return_status <> fnd_api.g_ret_sts_success)
3094 THEN
3095 l_validation_status := 'N';
3096 RAISE fnd_api.g_exc_error;
3097 ELSE
3098 IF (l_validation_status <> 'Y')
3099 THEN
3100 RAISE fnd_api.g_exc_error;
3101 ELSE
3102 FOR i IN 1 .. l_rs_interface_id_tbl.COUNT
3103 LOOP
3104 UPDATE mtl_transactions_interface
3105 SET cost_group_id = l_rs_cost_group_id_tbl (i)
3106 WHERE transaction_interface_id = l_rs_interface_id_tbl (i);
3107 END LOOP;
3108 END IF;
3109 END IF;
3110
3111 IF (l_debug = 1)
3112 THEN
3113 print_debug ('calling validate_quantity', 'validate_lot_merge_trx');
3114 END IF;
3115
3116 inv_lot_trx_validation_pub.validate_quantity
3117 (x_return_status => x_return_status
3118 , x_msg_count => x_msg_count
3119 , x_msg_data => x_msg_data
3120 , x_validation_status => l_validation_status
3121 , p_transaction_type_id => l_transaction_type_id
3122 , p_st_org_id_tbl => l_st_org_id_tbl
3123 , p_st_item_id_tbl => l_st_item_id_tbl
3124 , p_st_sub_code_tbl => l_st_sub_code_tbl
3125 , p_st_loc_id_tbl => l_st_locator_id_tbl
3126 , p_st_lot_num_tbl => l_st_lot_number_tbl
3127 , p_st_cost_group_tbl => l_st_cost_group_id_tbl
3128 , p_st_revision_tbl => l_st_revision_tbl
3129 , p_st_lpn_id_tbl => l_st_lpn_id_tbl
3130 , p_st_quantity_tbl => l_st_quantity_tbl
3131 , p_st_uom_tbl => l_st_uom_tbl
3132 , p_st_ser_parent_lot_tbl => l_st_ser_parent_lot_tbl
3133 , p_st_ser_number_tbl => l_st_ser_number_tbl
3134 , p_rs_org_id_tbl => l_rs_org_id_tbl
3135 , p_rs_item_id_tbl => l_rs_item_id_tbl
3136 , p_rs_sub_code_tbl => l_rs_sub_code_tbl
3137 , p_rs_loc_id_tbl => l_rs_locator_id_tbl
3138 , p_rs_lot_num_tbl => l_rs_lot_number_tbl
3139 , p_rs_cost_group_tbl => l_rs_cost_group_id_tbl
3140 , p_rs_revision_tbl => l_rs_revision_tbl
3141 , p_rs_lpn_id_tbl => l_rs_lpn_id_tbl
3142 , p_rs_quantity_tbl => l_rs_quantity_tbl
3143 , p_rs_uom_tbl => l_rs_uom_tbl
3144 , p_rs_ser_number_tbl => l_rs_ser_number_tbl
3145 , p_rs_ser_parent_lot_tbl => l_rs_ser_parent_lot_tbl
3146 );
3147
3148 IF ( x_return_status <> fnd_api.g_ret_sts_success
3149 OR l_validation_status <> 'Y'
3150 )
3151 THEN
3152 IF (l_debug = 1)
3153 THEN
3154 print_debug ('validate_quantity returned with error'
3155 , 'validate_lot_merge_trx'
3156 );
3157 END IF;
3158
3159 RAISE fnd_api.g_exc_error;
3160 END IF;
3161
3162 /*Call LPN Validations*/
3163 BEGIN
3164 IF (l_debug = 1)
3165 THEN
3166 print_debug('calling validate_lpn_info' , 'validate_lot_merge_trx');
3167 END IF;
3168
3169
3170
3171 inv_lot_trx_validation_pub.validate_lpn_info
3172 (x_return_status => x_return_status
3173 , x_msg_count => x_msg_count
3174 , x_msg_data => x_msg_data
3175 , x_validation_status => l_validation_status
3176 , p_st_lpn_id_tbl => l_st_lpn_id_tbl
3177 , p_rs_lpn_id_tbl => l_rs_lpn_id_tbl
3178 , p_st_org_id_tbl => l_st_org_id_tbl
3179 , p_rs_org_id_tbl => l_rs_org_id_tbl
3180 , p_rs_sub_code_tbl => l_rs_sub_code_tbl
3181 , p_rs_locator_id_tbl => l_rs_locator_id_tbl
3182 );
3183 IF(l_debug = 1) THEN
3184 print_debug('after validate_lpn_info ' , 'validate_lot_merge_trx');
3185 END IF;
3186
3187 EXCEPTION
3188 WHEN OTHERS
3189 THEN
3190 IF (l_debug = 1)
3191 THEN
3192 print_debug ('validate_lpn_info raised exception'
3193 , 'Validate_lot_merge_Trx'
3194 );
3195 END IF;
3196
3197 fnd_message.set_name ('INV', 'INV_INT_LPN');
3198 fnd_msg_pub.ADD;
3199 RAISE fnd_api.g_exc_unexpected_error;
3200 END;
3201
3202 IF ( x_return_status <> fnd_api.g_ret_sts_success
3203 OR l_validation_status <> 'Y'
3204 )
3205 THEN
3206
3207 print_debug ('validate_lpn_info returned with Error'
3208 , 'Validate_lot_merge_Trx'
3209 );
3210 RAISE fnd_api.g_exc_error;
3211 END IF;
3212
3213 /*Call LPN Validations*/
3214
3215
3216 BEGIN
3217 BEGIN
3218 IF (l_debug = 1)
3219 THEN
3220 print_debug ('Trying to get the serial control code'
3221 , 'validate_lot_merge_trx'
3222 );
3223 END IF;
3224
3225 SELECT DECODE (serial_number_control_code, 2, 'Y', 5, 'Y', 'N')
3226 INTO l_is_serial_controlled
3227 FROM mtl_system_items
3228 WHERE inventory_item_id = l_st_item_id_tbl (1)
3229 AND organization_id = l_st_org_id_tbl (1);
3230 EXCEPTION
3231 WHEN OTHERS
3232 THEN
3233 IF (l_debug = 1)
3234 THEN
3235 print_debug ('Cannot fetch the serial control code for the item'
3236 , 'Validate_lot_Merge_Trx'
3237 );
3238 END IF;
3239
3240 l_validation_status := 'E';
3241 RAISE fnd_api.g_exc_unexpected_error;
3242 END;
3243
3244 IF (l_is_serial_controlled = 'Y')
3245 THEN
3246 IF ( l_st_ser_number_tbl.COUNT = 0
3247 OR l_rs_ser_number_tbl.COUNT = 0
3248 OR l_st_ser_number_tbl.COUNT <> l_rs_ser_number_tbl.COUNT
3249 )
3250 THEN
3251 IF (l_debug = 1)
3252 THEN
3253 print_debug
3254 ('Either the serial record is empty or the starting and resulting records do not match'
3255 , 'Validate_lot_Merge_Trx'
3256 );
3257 END IF;
3258
3259 l_validation_status := 'N';
3260 fnd_message.set_name ('INV', 'INV_SERIAL_INFO_MISSING');
3261 fnd_msg_pub.ADD;
3262 RAISE fnd_api.g_exc_error;
3263 ELSE
3264 BEGIN
3265 IF (l_debug = 1)
3266 THEN
3267 print_debug ('calling validate_serials'
3268 , 'Validate_lot_Merge_Trx'
3269 );
3270 END IF;
3271
3272 inv_lot_trx_validation_pub.validate_serials
3273 (x_return_status => x_return_status
3274 , x_msg_count => x_msg_count
3275 , x_msg_data => x_msg_data
3276 , x_validation_status => l_validation_status
3277 , p_transaction_type_id => l_transaction_type_id
3278 , p_st_org_id_tbl => l_st_org_id_tbl
3279 , p_rs_org_id_tbl => l_rs_org_id_tbl
3280 , p_st_item_id_tbl => l_st_item_id_tbl
3281 , p_rs_item_id_tbl => l_rs_item_id_tbl
3282 , p_st_quantity_tbl => l_st_quantity_tbl
3283 --Needed for status control check
3284 , p_st_sub_code_tbl => l_st_sub_code_tbl
3285 , p_st_locator_id_tbl => l_st_locator_id_tbl
3286 , p_st_ser_parent_lot_tbl => l_st_ser_parent_lot_tbl
3287 , p_rs_lot_num_tbl => l_rs_lot_number_tbl
3288 , p_st_ser_number_tbl => l_st_ser_number_tbl
3289 , p_rs_ser_number_tbl => l_rs_ser_number_tbl
3290 , p_st_ser_status_tbl => l_st_ser_status_tbl
3291 , p_st_ser_grp_mark_id_tbl => l_st_ser_grp_mark_id_tbl
3292 , p_st_ser_parent_sub_tbl => l_st_ser_parent_sub_tbl
3293 , p_st_ser_parent_loc_tbl => l_st_ser_parent_loc_tbl
3294 );
3295
3296 EXCEPTION
3297 WHEN OTHERS
3298 THEN
3299 IF (l_debug = 1)
3300 THEN
3301 print_debug ('Validate_serials has raised exception'
3302 , 'Validate_lot_Merge_Trx'
3303 );
3304 END IF;
3305
3306 l_validation_status := 'N';
3307 fnd_message.set_name ('INV', 'INV_FAIL_VALIDATE_SERIAL');
3308 fnd_msg_pub.ADD;
3309 RAISE fnd_api.g_exc_unexpected_error;
3310 END;
3311
3312 IF ( x_return_status <> fnd_api.g_ret_sts_success
3313 OR l_validation_status <> 'Y'
3314 )
3315 THEN
3316 IF (l_debug = 1)
3317 THEN
3318 print_debug ('Validate_serials returned with error code'
3319 , 'Validate_lot_Merge_Trx'
3320 );
3321 END IF;
3322
3323 RAISE fnd_api.g_exc_error;
3324 END IF;
3325 END IF;
3326 END IF; --is lot serial controlled
3327 EXCEPTION
3328 WHEN OTHERS
3329 THEN
3330 IF (l_debug = 1)
3331 THEN
3332 print_debug ('Error while validating serial info'
3333 , 'Validate_lot_Merge_Trx'
3334 );
3335 END IF;
3336
3337 l_validation_status := 'N';
3338 fnd_message.set_name ('INV', 'INV_FAIL_VALIDATE_SERIAL');
3339 fnd_msg_pub.ADD;
3340 RAISE fnd_api.g_exc_unexpected_error;
3341 END;
3342
3343 /***** Check for the representative Lot and if it is populated
3344 ---populate the attributes based on the lot else send the default lot
3345 -- number ****/
3346 SELECT representative_lot_number
3347 INTO l_represenatative_lot
3348 FROM mtl_transactions_interface
3349 WHERE transaction_interface_id = l_st_interface_id_tbl (1);
3350
3351 IF l_represenatative_lot IS NULL
3352 THEN
3353 FOR i IN 1 .. l_st_interface_id_tbl.COUNT
3354 LOOP
3355 IF l_st_quantity_tbl (i) > l_max_lot_qty
3356 THEN
3357 l_lot_number := l_st_lot_number_tbl (i);
3358 l_max_lot_qty := l_st_quantity_tbl (i);
3359 l_transaction_interface_id := l_st_interface_id_tbl (i);
3360 END IF;
3361 END LOOP;
3362 ELSE
3363 l_lot_number := l_represenatative_lot;
3364 l_transaction_interface_id := l_st_interface_id_tbl (1);
3365 END IF;
3366
3367 IF (l_debug = 1)
3368 THEN
3369 print_debug ('l_lot_number is ' || l_lot_number
3370 , 'Validate_lot_merge_trx'
3371 );
3372 print_debug ('calling get_lot_attr_record for starting lot'
3373 , 'Validate_lot_merge_trx'
3374 );
3375 END IF;
3376
3377 /*Bug:5354721. The following procedure populates the column name, type and
3378 length for all the Lot Attributes. */
3379 get_lot_attr_table;
3380
3381 get_lot_attr_record
3382 (x_lot_attr_tbl => l_st_lot_attr_tbl
3383 , p_transaction_interface_id => l_transaction_interface_id
3384 , p_lot_number => l_lot_number
3385 , p_starting_lot_number => l_lot_number
3386 , p_organization_id => l_st_org_id_tbl (1)
3387 , p_inventory_item_id => l_st_item_id_tbl (1)
3388 );
3389
3390 IF (l_debug = 1)
3391 THEN
3392 print_debug ('calling get_lot_attr_record for resulting lot'
3393 , 'Validate_lot_Merge_Trx'
3394 );
3395 END IF;
3396
3397 get_lot_attr_record
3398 (x_lot_attr_tbl => l_rs_lot_attr_tbl
3399 , p_transaction_interface_id => l_rs_interface_id_tbl
3400 (1)
3401 , p_lot_number => l_rs_lot_number_tbl
3402 (1)
3403 , p_starting_lot_number => l_lot_number
3404 , p_organization_id => l_rs_org_id_tbl (1)
3405 , p_inventory_item_id => l_rs_item_id_tbl (1)
3406 );
3407
3408 IF (l_debug = 1)
3409 THEN
3410 print_debug ('calling validate_attributes', 'Validate_lot_merge_trx');
3411 END IF;
3412
3413 inv_lot_trx_validation_pub.validate_attributes
3414 (x_return_status => x_return_status
3415 , x_msg_count => x_msg_count
3416 , x_msg_data => x_msg_data
3417 , x_validation_status => l_validation_status
3418 , x_lot_attr_tbl => l_lot_attr_tbl
3419 , p_lot_number => l_rs_lot_number_tbl
3420 (1)
3421 , p_organization_id => l_rs_org_id_tbl
3422 (1)
3423 , p_inventory_item_id => l_rs_item_id_tbl
3424 (1)
3425 , p_parent_lot_attr_tbl => l_st_lot_attr_tbl
3426 , p_result_lot_attr_tbl => l_rs_lot_attr_tbl
3427 , p_transaction_type_id => l_transaction_type_id
3428 );
3429
3430 IF ( x_return_status <> fnd_api.g_ret_sts_success
3431 OR l_validation_status <> 'Y'
3432 )
3433 THEN
3434 RAISE fnd_api.g_exc_error;
3435 ELSE
3436 -- we have to update the attributes with either the max
3437 -- -lot or with the lot the User has specified as the
3438 -- resesenatattive lot
3439 IF (l_debug = 1)
3440 THEN
3441 print_debug ('callign update_lot_attr_record', 'validate_lot_merge');
3442 END IF;
3443
3444 IF (l_lot_attr_tbl.COUNT > 0)
3445 THEN
3446 update_lot_attr_record
3447 (p_lot_attr_tbl => l_lot_attr_tbl
3448 , p_transaction_interface_id => l_rs_interface_id_tbl
3449 (1)
3450 , p_lot_number => l_rs_lot_number_tbl
3451 (1)
3452 , p_organization_id => l_rs_org_id_tbl (1)
3453 , p_inventory_item_id => l_rs_item_id_tbl (1)
3454 );
3455 END IF;
3456 END IF;
3457
3458 -- Call to compute the correct expiration dates
3459 BEGIN
3460 l_st_lot_number_tbl (1) := l_lot_number;
3461 -- Send in just one lot number
3462 inv_lot_trx_validation_pub.compute_lot_expiration
3463 (x_return_status => x_return_status
3464 , x_msg_count => x_msg_count
3465 , x_msg_data => x_msg_data
3466 , p_parent_id => p_parent_id
3467 , p_transaction_type_id => l_transaction_type_id
3468 , p_item_id => l_st_item_id_tbl
3469 (1)
3470 , p_organization_id => l_st_org_id_tbl (1)
3471 , p_st_lot_num => l_st_lot_number_tbl
3472 (1)
3473 , p_rs_lot_num_tbl => l_rs_lot_number_tbl
3474 , p_rs_lot_exp_tbl => l_rs_lot_exp_tbl
3475 );
3476 EXCEPTION
3477 WHEN OTHERS
3478 THEN
3479 fnd_message.set_name ('INV', 'INV_LOT_EXP_COMPUTE_ERROR');
3480 fnd_msg_pub.ADD;
3481 RAISE fnd_api.g_exc_unexpected_error;
3482 END;
3483
3484 IF (x_return_status <> fnd_api.g_ret_sts_success)
3485 THEN
3486 fnd_message.set_name ('INV', 'INV_LOT_EXP_COMPUTE_ERROR');
3487 fnd_msg_pub.ADD;
3488 l_validation_status := 'N';
3489 RAISE fnd_api.g_exc_error;
3490 END IF;
3491
3492 -- if we reach here, it means all validations are successfull
3493 x_return_status := fnd_api.g_ret_sts_success;
3494 x_validation_status := 'Y';
3495 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
3496 EXCEPTION
3497 WHEN fnd_api.g_exc_error
3498 THEN
3499 x_return_status := fnd_api.g_ret_sts_error;
3500 x_validation_status := l_validation_status;
3501 fnd_msg_pub.count_and_get (p_count => x_msg_count
3502 , p_data => x_msg_data);
3503 WHEN fnd_api.g_exc_unexpected_error
3504 THEN
3505 x_return_status := fnd_api.g_ret_sts_unexp_error;
3506 x_validation_status := l_validation_status;
3507 fnd_msg_pub.count_and_get (p_count => x_msg_count
3508 , p_data => x_msg_data);
3509 WHEN OTHERS
3510 THEN
3511 x_return_status := fnd_api.g_ret_sts_unexp_error;
3512 x_validation_status := 'E';
3513
3514 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
3515 THEN
3516 fnd_msg_pub.add_exc_msg (g_pkg_name, 'validate_lot_merge_trx');
3517 END IF;
3518
3519 fnd_msg_pub.count_and_get (p_count => x_msg_count
3520 , p_data => x_msg_data);
3521 END validate_lot_merge_trx;
3522
3523 PROCEDURE validate_lot_translate_trx (
3524 x_return_status OUT NOCOPY VARCHAR2
3525 , x_msg_count OUT NOCOPY NUMBER
3526 , x_msg_data OUT NOCOPY VARCHAR2
3527 , x_validation_status OUT NOCOPY VARCHAR2
3528 , p_parent_id IN NUMBER
3529 )
3530 IS
3531 l_return_status VARCHAR2 (1);
3532 l_msg_count NUMBER;
3533 l_msg_data VARCHAR2 (255);
3534 l_validation_status VARCHAR2 (1);
3535 l_transaction_type_id NUMBER;
3536 -- l_acct_period_id NUMBER;
3537 l_transaction_interface_id NUMBER;
3538 l_transaction_action_id NUMBER;
3539 l_st_item_id_tbl inv_lot_trx_validation_pub.number_table;
3540 l_st_org_id_tbl inv_lot_trx_validation_pub.number_table;
3541 l_st_revision_tbl inv_lot_trx_validation_pub.revision_table;
3542 l_st_quantity_tbl inv_lot_trx_validation_pub.number_table;
3543 l_st_uom_tbl inv_lot_trx_validation_pub.uom_table;
3544 l_st_locator_id_tbl inv_lot_trx_validation_pub.number_table;
3545 l_st_sub_code_tbl inv_lot_trx_validation_pub.sub_code_table;
3546 l_st_cost_group_id_tbl inv_lot_trx_validation_pub.number_table;
3547 l_st_lpn_id_tbl inv_lot_trx_validation_pub.number_table;
3548 l_rs_item_id_tbl inv_lot_trx_validation_pub.number_table;
3549 l_rs_org_id_tbl inv_lot_trx_validation_pub.number_table;
3550 l_rs_revision_tbl inv_lot_trx_validation_pub.revision_table;
3551 l_rs_quantity_tbl inv_lot_trx_validation_pub.number_table;
3552 l_rs_uom_tbl inv_lot_trx_validation_pub.uom_table;
3553 l_rs_locator_id_tbl inv_lot_trx_validation_pub.number_table;
3554 l_rs_sub_code_tbl inv_lot_trx_validation_pub.sub_code_table;
3555 l_rs_lpn_id_tbl inv_lot_trx_validation_pub.number_table;
3556 l_rs_cost_group_id_tbl inv_lot_trx_validation_pub.number_table;
3557 l_st_lot_number_tbl inv_lot_trx_validation_pub.lot_number_table;
3558 l_rs_lot_number_tbl inv_lot_trx_validation_pub.lot_number_table;
3559 --Added for OSFM Support to Serialized Lot Items
3560 l_is_serial_controlled VARCHAR2 (1);
3561 l_st_ser_number_tbl inv_lot_trx_validation_pub.serial_number_table;
3562 l_st_ser_parent_lot_tbl inv_lot_trx_validation_pub.parent_lot_table;
3563 l_rs_ser_parent_lot_tbl inv_lot_trx_validation_pub.parent_lot_table;
3564 l_rs_ser_number_tbl inv_lot_trx_validation_pub.serial_number_table;
3565 l_st_ser_status_tbl inv_lot_trx_validation_pub.number_table;
3566 l_rs_ser_status_tbl inv_lot_trx_validation_pub.number_table;
3567 l_st_ser_grp_mark_id_tbl inv_lot_trx_validation_pub.number_table;
3568 l_rs_ser_grp_mark_id_tbl inv_lot_trx_validation_pub.number_table;
3569 l_st_ser_parent_sub_tbl inv_lot_trx_validation_pub.parent_sub_table;
3570 l_st_ser_parent_loc_tbl inv_lot_trx_validation_pub.parent_loc_table;
3571 --Added for OSFM Support to Serialized Lot Items
3572 l_st_status_id_tbl inv_lot_trx_validation_pub.number_table;
3573 l_rs_status_id_tbl inv_lot_trx_validation_pub.number_table;
3574 l_st_interface_id_tbl inv_lot_trx_validation_pub.number_table;
3575 l_rs_interface_id_tbl inv_lot_trx_validation_pub.number_table;
3576 l_rs_index NUMBER;
3577 l_count NUMBER;
3578 l_st_lot_attr_tbl inv_lot_sel_attr.lot_sel_attributes_tbl_type;
3579 l_rs_lot_attr_tbl inv_lot_sel_attr.lot_sel_attributes_tbl_type;
3580 l_st_lot_exp_tbl inv_lot_trx_validation_pub.date_table;
3581 l_rs_lot_exp_tbl inv_lot_trx_validation_pub.date_table;
3582 l_lot_attr_tbl inv_lot_sel_attr.lot_sel_attributes_tbl_type;
3583 l_distribution_account_id wsm_parameters.transaction_account_id%TYPE
3584 := NULL;
3585 l_acct_period_tbl inv_lot_trx_validation_pub.number_table;
3586 l_wms_installed VARCHAR2 (1);
3587 l_wms_enabled VARCHAR2 (1);
3588 l_wsm_enabled VARCHAR2 (1);
3589 l_st_dist_account_id NUMBER;
3590 l_rs_dist_account_id NUMBER;
3591 l_debug NUMBER
3592 := NVL (fnd_profile.VALUE ('INV_DEBUG_TRACE'), 0);
3593 l_dist_id NUMBER;
3594 l_interface_id NUMBER;
3595 BEGIN
3596 l_validation_status := 'Y';
3597 x_return_status := fnd_api.g_ret_sts_success;
3598
3599 IF (l_debug = 1)
3600 THEN
3601 print_debug ('Inside Validate_lot_translate', 'Validate_lot_translate');
3602 print_debug ('Calling populate_records', 'Validate_lot_translate');
3603 END IF;
3604
3605 BEGIN
3606 populate_records (x_validation_status => l_validation_status
3607 , x_return_status => x_return_status
3608 , x_st_interface_id_tbl => l_st_interface_id_tbl
3609 , x_st_item_id_tbl => l_st_item_id_tbl
3610 , x_st_org_id_tbl => l_st_org_id_tbl
3611 , x_st_revision_tbl => l_st_revision_tbl
3612 , x_st_sub_code_tbl => l_st_sub_code_tbl
3613 , x_st_locator_id_tbl => l_st_locator_id_tbl
3614 , x_st_lot_num_tbl => l_st_lot_number_tbl
3615 , x_st_ser_num_tbl => l_st_ser_number_tbl
3616 , x_st_ser_parent_lot_tbl => l_st_ser_parent_lot_tbl
3617 , x_rs_ser_parent_lot_tbl => l_rs_ser_parent_lot_tbl
3618 , x_rs_ser_num_tbl => l_rs_ser_number_tbl
3619 , x_st_ser_status_tbl => l_st_ser_status_tbl
3620 , x_rs_ser_status_tbl => l_rs_ser_status_tbl
3621 , x_st_ser_grp_mark_id_tbl => l_st_ser_grp_mark_id_tbl
3622 , x_rs_ser_grp_mark_id_tbl => l_rs_ser_grp_mark_id_tbl
3623 , x_st_ser_parent_sub_tbl => l_st_ser_parent_sub_tbl
3624 , x_st_ser_parent_loc_tbl => l_st_ser_parent_loc_tbl
3625 , x_st_lpn_id_tbl => l_st_lpn_id_tbl
3626 , x_st_quantity_tbl => l_st_quantity_tbl
3627 , x_st_cost_group_tbl => l_st_cost_group_id_tbl
3628 , x_st_uom_tbl => l_st_uom_tbl
3629 , x_st_status_id_tbl => l_st_status_id_tbl
3630 , x_rs_interface_id_tbl => l_rs_interface_id_tbl
3631 , x_rs_item_id_tbl => l_rs_item_id_tbl
3632 , x_rs_org_id_tbl => l_rs_org_id_tbl
3633 , x_rs_revision_tbl => l_rs_revision_tbl
3634 , x_rs_sub_code_tbl => l_rs_sub_code_tbl
3635 , x_rs_locator_id_tbl => l_rs_locator_id_tbl
3636 , x_rs_lot_num_tbl => l_rs_lot_number_tbl
3637 , x_rs_lpn_id_tbl => l_rs_lpn_id_tbl
3638 , x_rs_quantity_tbl => l_rs_quantity_tbl
3639 , x_rs_cost_group_tbl => l_rs_cost_group_id_tbl
3640 , x_rs_uom_tbl => l_rs_uom_tbl
3641 , x_rs_status_id_tbl => l_rs_status_id_tbl
3642 , x_st_lot_exp_tbl => l_st_lot_exp_tbl
3643 , x_rs_lot_exp_tbl => l_rs_lot_exp_tbl
3644 , x_transaction_type_id => l_transaction_type_id
3645 , x_acct_period_tbl => l_acct_period_tbl
3646 , x_st_dist_account_id => l_st_dist_account_id
3647 , x_rs_dist_account_id => l_rs_dist_account_id
3648 , p_parent_id => p_parent_id
3649 );
3650 EXCEPTION
3651 WHEN OTHERS
3652 THEN
3653 IF (l_debug = 1)
3654 THEN
3655 print_debug ('Populate_records raised error'
3656 , 'Validate_lot_translate_Trx'
3657 );
3658 END IF;
3659
3660 l_validation_status := 'N';
3661 fnd_message.set_name ('INV', 'INV_RETRIEVE_RECORD');
3662 RAISE fnd_api.g_exc_unexpected_error;
3663 END;
3664
3665 IF (x_return_status <> fnd_api.g_ret_sts_success)
3666 THEN
3667 l_validation_status := 'N';
3668 RAISE fnd_api.g_exc_error;
3669 ELSIF (l_validation_status <> 'Y')
3670 THEN
3671 RAISE fnd_api.g_exc_error;
3672 END IF;
3673 /*Removing the check...
3674 -- If wms is not installed and wsm is not enabled, we do not support lot transactions through the interface
3675 IF (l_debug = 1)
3676 THEN
3677 print_debug ('calling get_org_info', 'Validate_lot_translate_Trx');
3678 END IF;
3679
3680 inv_lot_trx_validation_pub.get_org_info
3681 (x_wms_installed => l_wms_installed
3682 , x_wsm_enabled => l_wsm_enabled
3683 , x_wms_enabled => l_wms_enabled
3684 , x_return_status => x_return_status
3685 , x_msg_count => x_msg_count
3686 , x_msg_data => x_msg_data
3687 , p_organization_id => l_st_org_id_tbl
3688 (1)
3689 );
3690
3691 IF (x_return_status = fnd_api.g_ret_sts_error)
3692 THEN
3693 IF (l_debug = 1)
3694 THEN
3695 print_debug ('get_org_info returned with error'
3696 , 'Validate_lot_translate_Trx'
3697 );
3698 END IF;
3699
3700 l_validation_status := 'N';
3701 RAISE fnd_api.g_exc_error;
3702 ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
3703 THEN
3704 IF (l_debug = 1)
3705 THEN
3706 print_debug ('get_org_info returned with unexpected error'
3707 , 'Validate_lot_translate_Trx'
3708 );
3709 END IF;
3710
3711 l_validation_status := 'E';
3712 RAISE fnd_api.g_exc_unexpected_error;
3713 END IF;
3714
3715 IF ((NVL (l_wsm_enabled, 'N') = 'N')
3716 AND (NVL (l_wms_installed, 'N') = 'N')
3717 )
3718 THEN
3719 -- raise error
3720 print_debug ('Validation failed on wsm/wms install'
3721 , 'Validate_lot_translate'
3722 );
3723 fnd_message.set_name ('WMS', 'WMS_NOT_INSTALLED');
3724 fnd_msg_pub.ADD;
3725 l_validation_status := 'N';
3726 RAISE fnd_api.g_exc_error;
3727 END IF;
3728 */
3729 IF (l_debug = 1)
3730 THEN
3731 print_debug ('calling Validate_Organization', 'Validate_lot_Split_Trx');
3732 END IF;
3733
3734 BEGIN
3735 inv_lot_trx_validation_pub.validate_organization
3736 (x_return_status => x_return_status
3737 , x_msg_count => x_msg_count
3738 , x_msg_data => x_msg_data
3739 , x_validation_status => l_validation_status
3740 , p_organization_id => l_st_org_id_tbl
3741 (1)
3742 , p_period_tbl => l_acct_period_tbl
3743 );
3744 EXCEPTION
3745 WHEN OTHERS
3746 THEN
3747 IF (l_debug = 1)
3748 THEN
3749 print_debug ('Validate_organization raised exception'
3750 , 'Validate_lot_translate_Trx'
3751 );
3752 END IF;
3753
3754 fnd_message.set_name ('INV', 'INV_RETRIEVE_PERIOD');
3755 fnd_msg_pub.ADD;
3756 l_validation_status := 'N';
3757 RAISE fnd_api.g_exc_unexpected_error;
3758 END;
3759
3760 IF ( x_return_status <> fnd_api.g_ret_sts_success
3761 OR l_validation_status <> 'Y'
3762 )
3763 THEN
3764 RAISE fnd_api.g_exc_error;
3765 END IF;
3766
3767 -- FOR lot Translate, we have TO get the distribution account id and
3768 -- populate it IN MTI
3769
3770 -- BEGIN
3771 IF (l_debug = 1)
3772 THEN
3773 print_debug ('after calling populate_records'
3774 , 'validate_lot_translate');
3775 print_debug ('getting wsm_enabled_flag from mtl_parameters'
3776 , 'validate_lot_translate'
3777 );
3778 END IF;
3779
3780 IF (l_debug = 1)
3781 THEN
3782 print_debug ('l_st_dist_account_id = ' || l_st_dist_account_id
3783 , 'Validate_lot_translate'
3784 );
3785 print_debug ('l_rs_dist_account_id = ' || l_rs_dist_account_id
3786 , 'Validate_lot_translate'
3787 );
3788 END IF;
3789
3790 IF (l_st_dist_account_id IS NULL OR l_rs_dist_account_id IS NULL)
3791 THEN
3792 IF (l_debug = 1)
3793 THEN
3794 print_debug ('l_wsm_enabled = ' || l_wsm_enabled
3795 , 'Validate_lot_translate'
3796 );
3797 END IF;
3798
3799 IF (NVL (l_wsm_enabled, 'N') = 'N')
3800 THEN
3801 IF (l_debug = 1)
3802 THEN
3803 print_debug ('Not OSFM Organization', 'Validate_lot_translate');
3804 END IF;
3805
3806 /*Bug:4879175. Removing the following check as the distribution_id needs
3807 to be fetched irrespective of WMS Installation in the organization*/
3808 /*IF (NVL (l_wms_installed, 'N') = 'Y')
3809 THEN*/
3810 IF (l_debug = 1)
3811 THEN
3812 print_debug ('l_wms_installed = ' || l_wms_installed
3813 , 'Validate_lot_translate'
3814 );
3815 END IF;
3816
3817 BEGIN
3818 SELECT distribution_account_id
3819 INTO l_distribution_account_id
3820 FROM mtl_parameters
3821 WHERE organization_id = l_st_org_id_tbl (1);
3822 EXCEPTION
3823 WHEN NO_DATA_FOUND
3824 THEN
3825 fnd_message.set_name ('INV', 'INV_NO_DIST_ACCOUNT_ID');
3826 fnd_msg_pub.ADD;
3827
3828 IF (l_debug = 1)
3829 THEN
3830 print_debug ('INV_NO_DIST_ACCOUNT_ID : ' || SQLERRM
3831 , 'Validate_lot_translate'
3832 );
3833 END IF;
3834
3835 l_validation_status := 'N';
3836 RAISE fnd_api.g_exc_error;
3837 END;
3838 /*Bug:4879175.Commenting the following ELSE part.*/
3839 /*ELSE
3840 IF (l_debug = 1)
3841 THEN
3842 print_debug ('Validation failed on wsm/wms install'
3843 , 'Validate_lot_Translate'
3844 );
3845 END IF;
3846
3847 fnd_message.set_name ('WMS', 'WMS_NOT_INSTALLED');
3848 fnd_msg_pub.ADD;
3849 l_validation_status := 'N';
3850 RAISE fnd_api.g_exc_error;
3851 END IF;*/
3852 ELSE
3853 BEGIN
3854 SELECT transaction_account_id
3855 INTO l_distribution_account_id
3856 FROM wsm_parameters
3857 WHERE organization_id = l_st_org_id_tbl (1);
3858 EXCEPTION
3859 WHEN NO_DATA_FOUND
3860 THEN
3861 fnd_message.set_name ('INV', 'INV_NO_DIST_ACCOUNT_ID');
3862 fnd_msg_pub.ADD;
3863
3864 IF (l_debug = 1)
3865 THEN
3866 print_debug ('INV_NO_DIST_ACCOUNT_ID : ' || SQLERRM
3867 , 'Validate_lot_translate'
3868 );
3869 END IF;
3870
3871 l_validation_status := 'N';
3872 RAISE fnd_api.g_exc_error;
3873 END;
3874 END IF;
3875
3876 IF (l_distribution_account_id IS NULL)
3877 THEN
3878 IF (l_debug = 1)
3879 THEN
3880 print_debug ('Distribution account id is null'
3881 , 'Validate_lot_translate'
3882 );
3883 fnd_message.set_name ('INV', 'INV_NO_DIST_ACCOUNT_ID');
3884 fnd_msg_pub.ADD;
3885 l_validation_status := 'N';
3886 x_return_status := fnd_api.g_ret_sts_error;
3887 RAISE fnd_api.g_exc_error;
3888 END IF;
3889 ELSE
3890 IF (l_debug = 1)
3891 THEN
3892 print_debug ( 'Updating dist account id: '
3893 || l_distribution_account_id
3894 , 'Validate_lot_translate'
3895 );
3896 print_debug ( 'l_st_interface_id_tbl(1): '
3897 || l_st_interface_id_tbl (1)
3898 , 'Validate_lot_translate'
3899 );
3900 print_debug ( 'l_rs_interface_id_tbl(1): '
3901 || l_rs_interface_id_tbl (1)
3902 , 'Validate_lot_translate'
3903 );
3904 END IF;
3905
3906 UPDATE mtl_transactions_interface
3907 SET distribution_account_id = l_distribution_account_id
3908 WHERE transaction_interface_id IN
3909 (l_st_interface_id_tbl (1), l_rs_interface_id_tbl (1));
3910 END IF;
3911 END IF;
3912
3913 IF (l_debug = 1)
3914 THEN
3915 print_debug ('Calling validate_lots', 'Validate_lot_translate');
3916 END IF;
3917
3918 inv_lot_trx_validation_pub.validate_lots
3919 (x_return_status => x_return_status
3920 , x_msg_count => x_msg_count
3921 , x_msg_data => x_msg_data
3922 , x_validation_status => l_validation_status
3923 , p_transaction_type_id => l_transaction_type_id
3924 , p_st_org_id_tbl => l_st_org_id_tbl
3925 , p_st_item_id_tbl => l_st_item_id_tbl
3926 , p_st_lot_num_tbl => l_st_lot_number_tbl
3927 , p_rs_org_id_tbl => l_rs_org_id_tbl
3928 , p_rs_item_id_tbl => l_rs_item_id_tbl
3929 , p_rs_lot_num_tbl => l_rs_lot_number_tbl
3930 , p_st_lot_exp_tbl => l_st_lot_exp_tbl
3931 , p_rs_lot_exp_tbl => l_rs_lot_exp_tbl
3932 , p_st_revision_tbl => l_st_revision_tbl
3933 , p_rs_revision_tbl => l_rs_revision_tbl
3934 , p_st_quantity_tbl => l_st_quantity_tbl
3935 , p_rs_quantity_tbl => l_rs_quantity_tbl
3936 );
3937
3938 IF ( x_return_status <> fnd_api.g_ret_sts_success
3939 OR l_validation_status <> 'Y'
3940 )
3941 THEN
3942 RAISE fnd_api.g_exc_error;
3943 END IF;
3944
3945 IF (l_debug = 1)
3946 THEN
3947 print_debug ('Calling validate_material_status'
3948 , 'Validate_lot_translate'
3949 );
3950 END IF;
3951
3952 inv_lot_trx_validation_pub.validate_material_status
3953 (x_return_status => x_return_status
3954 , x_msg_count => x_msg_count
3955 , x_msg_data => x_msg_data
3956 , x_validation_status => l_validation_status
3957 , p_transaction_type_id => l_transaction_type_id
3958 , p_organization_id => l_st_org_id_tbl
3959 (1)
3960 , p_inventory_item_id => l_st_item_id_tbl
3961 (1)
3962 , p_lot_number => l_st_lot_number_tbl
3963 (1)
3964 , p_subinventory_code => l_st_sub_code_tbl
3965 (1)
3966 , p_locator_id => l_st_locator_id_tbl
3967 (1)
3968 , p_status_id => l_st_status_id_tbl
3969 (1)
3970 );
3971
3972 IF (l_debug = 1)
3973 THEN
3974 print_debug ('After calling validate_material_status'
3975 , 'Validate_lot_Translate_Trx'
3976 );
3977 print_debug ('Message Count' || x_msg_count
3978 , 'Validate_lot_translate_Trx'
3979 );
3980 print_debug ('Return Status' || x_return_status
3981 , 'Validate_lot_translate_Trx'
3982 );
3983 END IF;
3984
3985 IF ( x_return_status <> fnd_api.g_ret_sts_success
3986 OR l_validation_status <> 'Y'
3987 )
3988 THEN
3989 fnd_message.set_name ('WMS', 'WMS_VALIDATE_STATUS_ERROR');
3990 fnd_msg_pub.ADD;
3991 RAISE fnd_api.g_exc_error;
3992 END IF;
3993
3994 BEGIN
3995 SELECT transaction_action_id
3996 INTO l_transaction_action_id
3997 FROM mtl_transaction_types
3998 WHERE transaction_type_id = l_transaction_type_id;
3999 EXCEPTION
4000 WHEN NO_DATA_FOUND
4001 THEN
4002 fnd_message.set_name ('INV', 'INV_INT_TRX_TYPE');
4003 fnd_msg_pub.ADD;
4004 l_validation_status := 'N';
4005 RAISE fnd_api.g_exc_unexpected_error;
4006 WHEN OTHERS
4007 THEN
4008 fnd_message.set_name ('INV', 'INV_INT_TRX_TYPE');
4009 fnd_msg_pub.ADD;
4010 l_validation_status := 'E';
4011 RAISE fnd_api.g_exc_unexpected_error;
4012 END;
4013
4014 IF (l_debug = 1)
4015 THEN
4016 print_debug ('Calling validate_cost_groups', 'Validate_lot_translate');
4017 END IF;
4018
4019 inv_lot_trx_validation_pub.validate_cost_groups
4020 (x_rs_cost_group_tbl => l_rs_cost_group_id_tbl
4021 , x_return_status => x_return_status
4022 , x_msg_count => x_msg_count
4023 , x_msg_data => x_msg_data
4024 , x_validation_status => l_validation_status
4025 , p_transaction_type_id => l_transaction_type_id
4026 , p_transaction_action_id => l_transaction_action_id
4027 , p_st_org_id_tbl => l_st_org_id_tbl
4028 , p_st_item_id_tbl => l_st_item_id_tbl
4029 , p_st_sub_code_tbl => l_st_sub_code_tbl
4030 , p_st_loc_id_tbl => l_st_locator_id_tbl
4031 , p_st_lot_num_tbl => l_st_lot_number_tbl
4032 , p_st_cost_group_tbl => l_st_cost_group_id_tbl
4033 , p_st_revision_tbl => l_st_revision_tbl
4034 , p_st_lpn_id_tbl => l_st_lpn_id_tbl
4035 , p_rs_org_id_tbl => l_rs_org_id_tbl
4036 , p_rs_item_id_tbl => l_rs_org_id_tbl
4037 , p_rs_sub_code_tbl => l_rs_sub_code_tbl
4038 , p_rs_loc_id_tbl => l_rs_locator_id_tbl
4039 , p_rs_lot_num_tbl => l_rs_lot_number_tbl
4040 , p_rs_revision_tbl => l_rs_revision_tbl
4041 , p_rs_lpn_id_tbl => l_rs_lpn_id_tbl
4042 );
4043
4044 IF (x_return_status <> fnd_api.g_ret_sts_success)
4045 THEN
4046 l_validation_status := 'N';
4047 RAISE fnd_api.g_exc_error;
4048 ELSE
4049 IF (l_validation_status <> 'Y')
4050 THEN
4051 RAISE fnd_api.g_exc_error;
4052 ELSE
4053 FOR i IN 1 .. l_rs_interface_id_tbl.COUNT
4054 LOOP
4055 UPDATE mtl_transactions_interface
4056 SET cost_group_id = l_rs_cost_group_id_tbl (i)
4057 , distribution_account_id = l_distribution_account_id
4058 WHERE transaction_interface_id = l_rs_interface_id_tbl (i);
4059 END LOOP;
4060 END IF;
4061 END IF;
4062
4063 IF (l_debug = 1)
4064 THEN
4065 print_debug ('Calling validate_quantity', 'Validate_lot_translate');
4066 END IF;
4067
4068 inv_lot_trx_validation_pub.validate_quantity
4069 (x_return_status => x_return_status
4070 , x_msg_count => x_msg_count
4071 , x_msg_data => x_msg_data
4072 , x_validation_status => l_validation_status
4073 , p_transaction_type_id => l_transaction_type_id
4074 , p_st_org_id_tbl => l_st_org_id_tbl
4075 , p_st_item_id_tbl => l_st_item_id_tbl
4076 , p_st_sub_code_tbl => l_st_sub_code_tbl
4077 , p_st_loc_id_tbl => l_st_locator_id_tbl
4078 , p_st_lot_num_tbl => l_st_lot_number_tbl
4079 , p_st_cost_group_tbl => l_st_cost_group_id_tbl
4080 , p_st_revision_tbl => l_st_revision_tbl
4081 , p_st_lpn_id_tbl => l_st_lpn_id_tbl
4082 , p_st_quantity_tbl => l_st_quantity_tbl
4083 , p_st_uom_tbl => l_st_uom_tbl
4084 , p_st_ser_parent_lot_tbl => l_st_ser_parent_lot_tbl
4085 , p_st_ser_number_tbl => l_st_ser_number_tbl
4086 , p_rs_org_id_tbl => l_rs_org_id_tbl
4087 , p_rs_item_id_tbl => l_rs_item_id_tbl
4088 , p_rs_sub_code_tbl => l_rs_sub_code_tbl
4089 , p_rs_loc_id_tbl => l_rs_locator_id_tbl
4090 , p_rs_lot_num_tbl => l_rs_lot_number_tbl
4091 , p_rs_cost_group_tbl => l_rs_cost_group_id_tbl
4092 , p_rs_revision_tbl => l_rs_revision_tbl
4093 , p_rs_lpn_id_tbl => l_rs_lpn_id_tbl
4094 , p_rs_quantity_tbl => l_rs_quantity_tbl
4095 , p_rs_uom_tbl => l_rs_uom_tbl
4096 , p_rs_ser_number_tbl => l_rs_ser_number_tbl
4097 , p_rs_ser_parent_lot_tbl => l_rs_ser_parent_lot_tbl
4098 );
4099
4100 IF ( x_return_status <> fnd_api.g_ret_sts_success
4101 OR l_validation_status <> 'Y'
4102 )
4103 THEN
4104 IF (l_debug = 1)
4105 THEN
4106 print_debug ('validate_quantity returned with error'
4107 , 'Validate_lot_translate_Trx'
4108 );
4109 END IF;
4110
4111 RAISE fnd_api.g_exc_error;
4112 END IF;
4113
4114 IF (l_debug = 1)
4115 THEN
4116 print_debug ('Calling get_lot_attr_record for parent record'
4117 , 'Validate_lot_translate'
4118 );
4119 END IF;
4120 /*Call LPN Validations*/
4121 BEGIN
4122 IF (l_debug = 1)
4123 THEN
4124 print_debug('calling validate_lpn_info' , 'validate_lot_translate_trx');
4125 END IF;
4126
4127
4128
4129 inv_lot_trx_validation_pub.validate_lpn_info
4130 (x_return_status => x_return_status
4131 , x_msg_count => x_msg_count
4132 , x_msg_data => x_msg_data
4133 , x_validation_status => l_validation_status
4134 , p_st_lpn_id_tbl => l_st_lpn_id_tbl
4135 , p_rs_lpn_id_tbl => l_rs_lpn_id_tbl
4136 , p_st_org_id_tbl => l_st_org_id_tbl
4137 , p_rs_org_id_tbl => l_rs_org_id_tbl
4138 , p_rs_sub_code_tbl => l_rs_sub_code_tbl
4139 , p_rs_locator_id_tbl => l_rs_locator_id_tbl
4140 );
4141 IF(l_debug = 1) THEN
4142 print_debug('after validate_lpn_info ' , 'validate_lot_translate_trx');
4143 END IF;
4144
4145 EXCEPTION
4146 WHEN OTHERS
4147 THEN
4148 IF (l_debug = 1)
4149 THEN
4150 print_debug ('validate_lpn_info raised exception'
4151 , 'Validate_lot_translate_Trx'
4152 );
4153 END IF;
4154
4155 fnd_message.set_name ('INV', 'INV_INT_LPN');
4156 fnd_msg_pub.ADD;
4157 RAISE fnd_api.g_exc_unexpected_error;
4158 END;
4159
4160 IF ( x_return_status <> fnd_api.g_ret_sts_success
4161 OR l_validation_status <> 'Y'
4162 )
4163 THEN
4164
4165 print_debug ('validate_lpn_info returned with Error'
4166 , 'Validate_lot_translate_Trx'
4167 );
4168 RAISE fnd_api.g_exc_error;
4169 END IF;
4170
4171 /*Call LPN Validations*/
4172
4173 BEGIN
4174 BEGIN
4175 IF (l_debug = 1)
4176 THEN
4177 print_debug ('getting serial control code'
4178 , 'Validate_lot_translate_Trx'
4179 );
4180 END IF;
4181
4182 SELECT DECODE (serial_number_control_code, 2, 'Y'
4183 , 5, 'Y',
4184 'N')
4185 INTO l_is_serial_controlled
4186 FROM mtl_system_items
4187 WHERE inventory_item_id = l_st_item_id_tbl (1)
4188 AND organization_id = l_st_org_id_tbl (1);
4189 EXCEPTION
4190 WHEN OTHERS
4191 THEN
4192 IF (l_debug = 1)
4193 THEN
4194 print_debug ('Cannot fetch the serial control code for the item'
4195 , 'Validate_lot_Translate_Trx'
4196 );
4197 END IF;
4198
4199 l_validation_status := 'N';
4200 RAISE fnd_api.g_exc_unexpected_error;
4201 END;
4202
4203 IF (l_is_serial_controlled = 'Y')
4204 THEN
4205 IF ( l_st_ser_number_tbl.COUNT = 0
4206 OR l_rs_ser_number_tbl.COUNT = 0
4207 OR l_st_ser_number_tbl.COUNT <> l_rs_ser_number_tbl.COUNT
4208 )
4209 THEN
4210 IF (l_debug = 1)
4211 THEN
4212 print_debug
4213 ('Either the serial records are empty or the starting and resulting records do not match'
4214 , 'Validate_lot_Translate_Trx'
4215 );
4216 END IF;
4217
4218 l_validation_status := 'N';
4219 fnd_message.set_name ('INV', 'INV_SERIAL_INFO_MISSING');
4220 fnd_msg_pub.ADD;
4221 RAISE fnd_api.g_exc_error;
4222 ELSE
4223 BEGIN
4224 IF (l_debug = 1)
4225 THEN
4226 print_debug ('calling validate_serials'
4227 , 'Validate_lot_Translate_Trx'
4228 );
4229 END IF;
4230
4231 inv_lot_trx_validation_pub.validate_serials
4232 (x_return_status => x_return_status
4233 , x_msg_count => x_msg_count
4234 , x_msg_data => x_msg_data
4235 , x_validation_status => l_validation_status
4236 , p_transaction_type_id => l_transaction_type_id
4237 , p_st_org_id_tbl => l_st_org_id_tbl
4238 , p_rs_org_id_tbl => l_rs_org_id_tbl
4239 , p_st_item_id_tbl => l_st_item_id_tbl
4240 , p_rs_item_id_tbl => l_rs_item_id_tbl
4241 , p_st_quantity_tbl => l_st_quantity_tbl
4242 --Needed for status control check
4243 , p_st_sub_code_tbl => l_st_sub_code_tbl
4244 , p_st_locator_id_tbl => l_st_locator_id_tbl
4245 , p_st_ser_parent_lot_tbl => l_st_ser_parent_lot_tbl
4246 , p_rs_lot_num_tbl => l_rs_lot_number_tbl
4247 , p_st_ser_number_tbl => l_st_ser_number_tbl
4248 , p_rs_ser_number_tbl => l_rs_ser_number_tbl
4249 , p_st_ser_status_tbl => l_st_ser_status_tbl
4250 , p_st_ser_grp_mark_id_tbl => l_st_ser_grp_mark_id_tbl
4251 , p_st_ser_parent_sub_tbl => l_st_ser_parent_sub_tbl
4252 , p_st_ser_parent_loc_tbl => l_st_ser_parent_loc_tbl
4253 );
4254 EXCEPTION
4255 WHEN OTHERS
4256 THEN
4257 IF (l_debug = 1)
4258 THEN
4259 print_debug ('Validate_serials has raised exception'
4260 , 'Validate_lot_Translate_Trx'
4261 );
4262 END IF;
4263
4264 l_validation_status := 'N';
4265 fnd_message.set_name ('INV', 'INV_FAIL_VALIDATE_SERIAL');
4266 fnd_msg_pub.ADD;
4267 RAISE fnd_api.g_exc_unexpected_error;
4268 END;
4269
4270 IF ( x_return_status <> fnd_api.g_ret_sts_success
4271 OR l_validation_status <> 'Y'
4272 )
4273 THEN
4274 IF (l_debug = 1)
4275 THEN
4276 print_debug ('Validate_serials returned with error code'
4277 , 'Validate_lot_Translate_Trx'
4278 );
4279 END IF;
4280
4281 RAISE fnd_api.g_exc_error;
4282 END IF;
4283 END IF;
4284 END IF; --is lot serial controlled
4285 EXCEPTION
4286 WHEN OTHERS
4287 THEN
4288 IF (l_debug = 1)
4289 THEN
4290 print_debug ('Error in validating serial info'
4291 , 'Validate_lot_Translate_Trx'
4292 );
4293 END IF;
4294
4295 l_validation_status := 'N';
4296 fnd_message.set_name ('INV', 'INV_FAIL_VALIDATE_SERIAL');
4297 fnd_msg_pub.ADD;
4298 RAISE fnd_api.g_exc_error;
4299 END;
4300
4301 /*Bug:5354721. The following procedure populates the column name, type and
4302 length for all the Lot Attributes. */
4303 get_lot_attr_table;
4304
4305 get_lot_attr_record
4306 (x_lot_attr_tbl => l_st_lot_attr_tbl
4307 , p_transaction_interface_id => l_st_interface_id_tbl
4308 (1)
4309 , p_lot_number => l_st_lot_number_tbl
4310 (1)
4311 , p_starting_lot_number => l_st_lot_number_tbl
4312 (1)
4313 , p_organization_id => l_st_org_id_tbl (1)
4314 , p_inventory_item_id => l_st_item_id_tbl (1)
4315 );
4316
4317 --For i in 1..l_rs_interface_id_tbl.COUNT loop
4318 IF (l_debug = 1)
4319 THEN
4320 print_debug ('Calling get_lot_attr_record for resultant records'
4321 , 'Validate_lot_translate'
4322 );
4323 print_debug ('l_rs_interface_id is ' || l_rs_interface_id_tbl (1)
4324 , 'Validate_lot_translate'
4325 );
4326 END IF;
4327
4328 get_lot_attr_record
4329 (x_lot_attr_tbl => l_rs_lot_attr_tbl
4330 , p_transaction_interface_id => l_rs_interface_id_tbl
4331 (1)
4332 , p_lot_number => l_rs_lot_number_tbl
4333 (1)
4334 , p_starting_lot_number => l_st_lot_number_tbl
4335 (1)
4336 , p_organization_id => l_rs_org_id_tbl (1)
4337 , p_inventory_item_id => l_rs_item_id_tbl (1)
4338 );
4339
4340 IF (l_debug = 1)
4341 THEN
4342 print_debug ('calling validate_attributes for resultant records'
4343 , 'Validate_lot_translate'
4344 );
4345 END IF;
4346
4347 inv_lot_trx_validation_pub.validate_attributes
4348 (x_return_status => x_return_status
4349 , x_msg_count => x_msg_count
4350 , x_msg_data => x_msg_data
4351 , x_validation_status => l_validation_status
4352 , x_lot_attr_tbl => l_lot_attr_tbl
4353 , p_lot_number => l_st_lot_number_tbl
4354 (1)
4355 , p_organization_id => l_rs_org_id_tbl
4356 (1)
4357 , p_inventory_item_id => l_rs_item_id_tbl
4358 (1)
4359 , p_parent_lot_attr_tbl => l_st_lot_attr_tbl
4360 , p_result_lot_attr_tbl => l_rs_lot_attr_tbl
4361 , p_transaction_type_id => l_transaction_type_id
4362 );
4363
4364 IF ( x_return_status <> fnd_api.g_ret_sts_success
4365 OR l_validation_status <> 'Y'
4366 )
4367 THEN
4368 RAISE fnd_api.g_exc_error;
4369 ELSE
4370 IF (l_lot_attr_tbl.COUNT > 0)
4371 THEN
4372 -- this means user does not provide the lot attribute for the result lot.
4373 -- we need to update the mtl_transation_lots_interface with the parent
4374 -- lot attributes if it exists or use default lot attributes
4375 update_lot_attr_record
4376 (p_lot_attr_tbl => l_lot_attr_tbl
4377 , p_transaction_interface_id => l_rs_interface_id_tbl
4378 (1)
4379 , p_lot_number => l_rs_lot_number_tbl
4380 (1)
4381 , p_organization_id => l_rs_org_id_tbl (1)
4382 , p_inventory_item_id => l_rs_item_id_tbl (1)
4383 );
4384 END IF;
4385 END IF;
4386
4387 --end loop;
4388 -- Call to compute the correct expiration dates
4389 BEGIN
4390 -- Send in just one lot number
4391 inv_lot_trx_validation_pub.compute_lot_expiration
4392 (x_return_status => x_return_status
4393 , x_msg_count => x_msg_count
4394 , x_msg_data => x_msg_data
4395 , p_parent_id => p_parent_id
4396 , p_transaction_type_id => l_transaction_type_id
4397 , p_item_id => l_st_item_id_tbl
4398 (1)
4399 , p_organization_id => l_st_org_id_tbl (1)
4400 , p_st_lot_num => l_st_lot_number_tbl
4401 (1)
4402 , p_rs_lot_num_tbl => l_rs_lot_number_tbl
4403 , p_rs_lot_exp_tbl => l_rs_lot_exp_tbl
4404 );
4405 EXCEPTION
4406 WHEN OTHERS
4407 THEN
4408 fnd_message.set_name ('INV', 'INV_LOT_EXP_COMPUTE_ERROR');
4409 fnd_msg_pub.ADD;
4410 l_validation_status := 'N';
4411 RAISE fnd_api.g_exc_unexpected_error;
4412 END;
4413
4414 IF (x_return_status <> fnd_api.g_ret_sts_success)
4415 THEN
4416 fnd_message.set_name ('INV', 'INV_LOT_EXP_COMPUTE_ERROR');
4417 fnd_msg_pub.ADD;
4418 l_validation_status := 'N';
4419 RAISE fnd_api.g_exc_error;
4420 END IF;
4421
4422 -- if we reach here, it means all validations are successfull
4423 x_return_status := fnd_api.g_ret_sts_success;
4424 x_validation_status := 'Y';
4425 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
4426 EXCEPTION
4427 WHEN fnd_api.g_exc_error
4428 THEN
4429 x_return_status := fnd_api.g_ret_sts_error;
4430 x_validation_status := l_validation_status;
4431 fnd_msg_pub.count_and_get (p_count => x_msg_count
4432 , p_data => x_msg_data);
4433 WHEN fnd_api.g_exc_unexpected_error
4434 THEN
4435 x_return_status := fnd_api.g_ret_sts_unexp_error;
4436 x_validation_status := l_validation_status;
4437 fnd_msg_pub.count_and_get (p_count => x_msg_count
4438 , p_data => x_msg_data);
4439 WHEN OTHERS
4440 THEN
4441 x_return_status := fnd_api.g_ret_sts_unexp_error;
4442 x_validation_status := 'E';
4443
4444 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
4445 THEN
4446 fnd_msg_pub.add_exc_msg (g_pkg_name, 'validate_lot_translate_trx');
4447 END IF;
4448
4449 fnd_msg_pub.count_and_get (p_count => x_msg_count
4450 , p_data => x_msg_data);
4451 END validate_lot_translate_trx;
4452 END inv_lot_trx_validation_pvt;