[Home] [Help]
PACKAGE BODY: APPS.LOT_SPLIT_DATA_INSERT
Source
1 PACKAGE BODY lot_split_data_insert AS
2 /* $Header: INVLSPLB.pls 120.1 2006/03/14 02:43:58 rsagar noship $ */
3 g_header_id NUMBER := 0;
4 g_pkg_name VARCHAR2(100) := 'lot_split_data_insert';
5
6
7
8 PROCEDURE set_table (
9 p_transaction_type_id IN MTL_TRANSACTION_TYPES.transaction_type_id%TYPE,
10 p_transaction_lot IN VARCHAR2,
11 p_inventory_item_id IN MTL_SYSTEM_ITEMS.inventory_item_id%TYPE,
12 p_revision IN MTL_ITEM_REVISIONS.revision%TYPE,
13 p_organization_id IN MTL_PARAMETERS.organization_id%TYPE,
14 p_subinventory_code IN MTL_SECONDARY_INVENTORIES.secondary_inventory_name%TYPE,
15 p_locator_id IN MTL_ITEM_LOCATIONS.inventory_location_id%TYPE,
16 p_transaction_quantity IN MTL_MATERIAL_TRANSACTIONS.transaction_quantity%TYPE,
17 p_primary_quantity IN MTL_MATERIAL_TRANSACTIONS.primary_quantity%TYPE,
18 p_transaction_uom IN MTL_UNITS_OF_MEASURE.uom_code%TYPE,
19 p_item_description IN MTL_SYSTEM_ITEMS_KFV.description%TYPE,
20 p_item_location_control_code IN MTL_SYSTEM_ITEMS_KFV.location_control_code%TYPE,
21 p_item_restrict_subinv_code IN MTL_SYSTEM_ITEMS_KFV.restrict_subinventories_code%TYPE,
22 p_item_restrict_locators_code IN MTL_SYSTEM_ITEMS_KFV.restrict_locators_code%TYPE,
23 p_item_revision_qty_control_cd IN MTL_SYSTEM_ITEMS_KFV.revision_qty_control_code%TYPE,
24 p_item_primary_uom_code IN MTL_SYSTEM_ITEMS_KFV.primary_uom_code%TYPE,
25 p_item_shelf_life_code IN MTL_SYSTEM_ITEMS_KFV.shelf_life_code%TYPE,
26 p_item_shelf_life_days IN MTL_SYSTEM_ITEMS_KFV.shelf_life_days%TYPE,
27 p_allowed_units_lookup_code IN MTL_SYSTEM_ITEMS_KFV.allowed_units_lookup_code%TYPE,
28 p_lot_number IN MTL_LOT_NUMBERS.lot_number%TYPE,
29 p_lot_expiration_date IN MTL_LOT_NUMBERS.expiration_date%TYPE,
30 p_total_qty IN MTL_MATERIAL_TRANSACTIONS.transaction_quantity%TYPE,
31 p_lpn_id IN WMS_LICENSE_PLATE_NUMBERS.lpn_id%TYPE,
32 p_lpn_number IN WMS_LICENSE_PLATE_NUMBERS.license_plate_number%TYPE,
33 p_xfr_lpn_id IN WMS_LICENSE_PLATE_NUMBERS.lpn_id%TYPE,
34 p_userid IN NUMBER,
35 p_costgroup_id IN WMS_LPN_CONTENTS.cost_group_id%TYPE,
36 p_project_id IN NUMBER,
37 p_task_id IN NUMBER,
38 x_return_status OUT NOCOPY VARCHAR2,
39 x_header_id OUT NOCOPY NUMBER,
40 x_msg_data OUT NOCOPY VARCHAR2,
41 x_msg_count OUT NOCOPY NUMBER,
42 p_description IN MTL_LOT_NUMBERS.DESCRIPTION%TYPE,
43 p_vendor_id IN MTL_LOT_NUMBERS.VENDOR_ID%TYPE,
44 p_supplier_lot_number IN MTL_LOT_NUMBERS.SUPPLIER_LOT_NUMBER%TYPE,
45 p_territory_code IN MTL_LOT_NUMBERS.TERRITORY_CODE%TYPE,
46 p_grade_code IN MTL_LOT_NUMBERS.GRADE_CODE%TYPE,
47 p_origination_date IN MTL_LOT_NUMBERS.ORIGINATION_DATE%TYPE,
48 p_date_code IN MTL_LOT_NUMBERS.DATE_CODE%TYPE,
49 p_status_id IN MTL_LOT_NUMBERS.STATUS_ID%TYPE,
50 p_change_date IN MTL_LOT_NUMBERS.CHANGE_DATE%TYPE,
51 p_age IN MTL_LOT_NUMBERS.AGE%TYPE,
52 p_retest_date IN MTL_LOT_NUMBERS.RETEST_DATE%TYPE,
53 p_maturity_date IN MTL_LOT_NUMBERS.MATURITY_DATE%TYPE,
54 p_lot_attribute_category IN MTL_LOT_NUMBERS.LOT_ATTRIBUTE_CATEGORY%TYPE,
55 p_item_size IN MTL_LOT_NUMBERS.ITEM_SIZE%TYPE,
56 p_color IN MTL_LOT_NUMBERS.COLOR%TYPE,
57 p_volume IN MTL_LOT_NUMBERS.VOLUME%TYPE,
58 p_volume_uom IN MTL_LOT_NUMBERS.VOLUME_UOM%TYPE,
59 p_place_of_origin IN MTL_LOT_NUMBERS.PLACE_OF_ORIGIN%TYPE,
60 p_best_by_date IN MTL_LOT_NUMBERS.BEST_BY_DATE%TYPE,
61 p_length IN MTL_LOT_NUMBERS.LENGTH%TYPE,
62 p_length_uom IN MTL_LOT_NUMBERS.LENGTH_UOM%TYPE,
63 p_recycled_content IN MTL_LOT_NUMBERS.RECYCLED_CONTENT%TYPE,
64 p_thickness IN MTL_LOT_NUMBERS.THICKNESS%TYPE,
65 p_thickness_uom IN MTL_LOT_NUMBERS.THICKNESS_UOM%TYPE,
66 p_width IN MTL_LOT_NUMBERS.WIDTH%TYPE,
67 p_width_uom IN MTL_LOT_NUMBERS.WIDTH_UOM%TYPE,
68 p_curl_wrinkle_fold IN MTL_LOT_NUMBERS.CURL_WRINKLE_FOLD%TYPE,
69 p_c_attribute1 IN MTL_LOT_NUMBERS.C_ATTRIBUTE1%TYPE,
70 p_c_attribute2 IN MTL_LOT_NUMBERS.C_ATTRIBUTE2%TYPE,
71 p_c_attribute3 IN MTL_LOT_NUMBERS.C_ATTRIBUTE3%TYPE,
72 p_c_attribute4 IN MTL_LOT_NUMBERS.C_ATTRIBUTE4%TYPE,
73 p_c_attribute5 IN MTL_LOT_NUMBERS.C_ATTRIBUTE5%TYPE,
74 p_c_attribute6 IN MTL_LOT_NUMBERS.C_ATTRIBUTE6%TYPE,
75 p_c_attribute7 IN MTL_LOT_NUMBERS.C_ATTRIBUTE7%TYPE,
76 p_c_attribute8 IN MTL_LOT_NUMBERS.C_ATTRIBUTE8%TYPE,
77 p_c_attribute9 IN MTL_LOT_NUMBERS.C_ATTRIBUTE9%TYPE,
78 p_c_attribute10 IN MTL_LOT_NUMBERS.C_ATTRIBUTE10%TYPE,
79 p_c_attribute11 IN MTL_LOT_NUMBERS.C_ATTRIBUTE11%TYPE,
80 p_c_attribute12 IN MTL_LOT_NUMBERS.C_ATTRIBUTE12%TYPE,
81 p_c_attribute13 IN MTL_LOT_NUMBERS.C_ATTRIBUTE13%TYPE,
82 p_c_attribute14 IN MTL_LOT_NUMBERS.C_ATTRIBUTE14%TYPE,
83 p_c_attribute15 IN MTL_LOT_NUMBERS.C_ATTRIBUTE15%TYPE,
84 p_c_attribute16 IN MTL_LOT_NUMBERS.C_ATTRIBUTE16%TYPE,
85 p_c_attribute17 IN MTL_LOT_NUMBERS.C_ATTRIBUTE17%TYPE,
86 p_c_attribute18 IN MTL_LOT_NUMBERS.C_ATTRIBUTE18%TYPE,
87 p_c_attribute19 IN MTL_LOT_NUMBERS.C_ATTRIBUTE19%TYPE,
88 p_c_attribute20 IN MTL_LOT_NUMBERS.C_ATTRIBUTE20%TYPE,
89 p_d_attribute1 IN MTL_LOT_NUMBERS.D_ATTRIBUTE1%TYPE,
90 p_d_attribute2 IN MTL_LOT_NUMBERS.D_ATTRIBUTE2%TYPE,
91 p_d_attribute3 IN MTL_LOT_NUMBERS.D_ATTRIBUTE3%TYPE,
92 p_d_attribute4 IN MTL_LOT_NUMBERS.D_ATTRIBUTE4%TYPE,
93 p_d_attribute5 IN MTL_LOT_NUMBERS.D_ATTRIBUTE5%TYPE,
94 p_d_attribute6 IN MTL_LOT_NUMBERS.D_ATTRIBUTE6%TYPE,
95 p_d_attribute7 IN MTL_LOT_NUMBERS.D_ATTRIBUTE7%TYPE,
96 p_d_attribute8 IN MTL_LOT_NUMBERS.D_ATTRIBUTE8%TYPE,
97 p_d_attribute9 IN MTL_LOT_NUMBERS.D_ATTRIBUTE9%TYPE,
98 p_d_attribute10 IN MTL_LOT_NUMBERS.D_ATTRIBUTE10%TYPE,
99 p_n_attribute1 IN MTL_LOT_NUMBERS.N_ATTRIBUTE1%TYPE,
100 p_n_attribute2 IN MTL_LOT_NUMBERS.N_ATTRIBUTE2%TYPE,
101 p_n_attribute3 IN MTL_LOT_NUMBERS.N_ATTRIBUTE3%TYPE,
102 p_n_attribute4 IN MTL_LOT_NUMBERS.N_ATTRIBUTE4%TYPE,
103 p_n_attribute5 IN MTL_LOT_NUMBERS.N_ATTRIBUTE5%TYPE,
104 p_n_attribute6 IN MTL_LOT_NUMBERS.N_ATTRIBUTE6%TYPE,
105 p_n_attribute7 IN MTL_LOT_NUMBERS.N_ATTRIBUTE7%TYPE,
106 p_n_attribute8 IN MTL_LOT_NUMBERS.N_ATTRIBUTE8%TYPE,
107 p_n_attribute9 IN MTL_LOT_NUMBERS.N_ATTRIBUTE9%TYPE,
108 p_n_attribute10 IN MTL_LOT_NUMBERS.N_ATTRIBUTE10%TYPE,
109 p_sec_transaction_quantity IN MTL_MATERIAL_TRANSACTIONS.secondary_transaction_quantity%TYPE DEFAULT NULL, -- Bug #4093379 INVCONV
110 p_item_secondary_uom_code IN MTL_SYSTEM_ITEMS_KFV.secondary_uom_code%TYPE DEFAULT NULL, -- Bug #4093379 INVCONV
111 p_parent_lot_number IN MTL_LOT_NUMBERS.parent_lot_number%TYPE DEFAULT NULL, -- Bug #4093379 INVCONV
112 p_origination_type IN MTL_TRANSACTION_LOTS_TEMP.origination_type%TYPE DEFAULT NULL, -- Bug #4093379 INVCONV
113 p_expiration_action_date IN MTL_TRANSACTION_LOTS_TEMP.expiration_action_date%TYPE DEFAULT NULL, -- Bug #4093379 INVCONV
114 p_expiration_action_code IN MTL_TRANSACTION_LOTS_TEMP.expiration_action_code%TYPE DEFAULT NULL, -- Bug #4093379 INVCONV
115 p_hold_date IN MTL_TRANSACTION_LOTS_TEMP.hold_date%TYPE DEFAULT NULL, -- Bug #4093379 INVCONV
116 p_reason_id IN MTL_TRANSACTION_LOTS_TEMP.reason_id%TYPE DEFAULT NULL -- Bug #4093379 INVCONV
117 )
118 IS
119 ind INTEGER := 0;
120 l_dir VARCHAR2(1000) := NULL;
121 l_filename VARCHAR2(100) := 'ins_mmtt' || to_char(sysdate, 'dd:mm:hh24:mi');
122 x_mesg_data VARCHAR2(2000) := 'None';
123 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
124 BEGIN
125 /* For lot split transaction (INV_Globals.G_type_inv_lot_split) there is a single start
126 lot and multiple resulting
127 Start Lot always gets stored in the 1st index position .
128 For Lot merger (inv_globals.G_type_inv_lot_merge) 1st index position should store
129 the single resulting lot from
130 multiple starting lots.
131 For lot translate transaction (INV_Globals.G_type_inv_lot_translate) there is a
132 single start lot and single resulting lot.
133 Start Lot always gets stored in the 1st index position.
134 */
135
136 x_return_status := FND_API.G_RET_STS_SUCCESS;
137 x_header_id := NULL;
138
139 IF tab_index is NULL
140 THEN
141 tab_index := 0;
142 END IF;
143
144 ind := tab_index + 1;
145
146 tab_input(ind).transaction_type_id := p_transaction_type_id;
147 tab_input(ind).transaction_lot := p_transaction_lot;
148 tab_input(ind).inventory_item_id := p_inventory_item_id;
149 tab_input(ind).revision := p_revision;
150 tab_input(ind).organization_id := p_organization_id;
151 tab_input(ind).subinventory_code := p_subinventory_code;
152 tab_input(ind).locator_id := p_locator_id;
153 tab_input(ind).transaction_quantity := p_transaction_quantity;
154 tab_input(ind).secondary_transaction_quantity := p_sec_transaction_quantity; -- Bug #4093379 INVCONV
155 tab_input(ind).primary_quantity := p_primary_quantity;
156 tab_input(ind).transaction_uom := p_transaction_uom;
157
158 tab_input(ind).item_description := p_item_description;
159 tab_input(ind).item_location_control_code := p_item_location_control_code;
160 tab_input(ind).item_restrict_subinv_code := p_item_restrict_subinv_code;
161 tab_input(ind).item_restrict_locators_code := p_item_restrict_locators_code;
162 tab_input(ind).item_revision_qty_control_code := p_item_revision_qty_control_cd;
163 tab_input(ind).item_primary_uom_code := p_item_primary_uom_code;
164 tab_input(ind).item_secondary_uom_code := p_item_secondary_uom_code; -- Bug #4093379 INVCONV
165 tab_input(ind).item_shelf_life_code := p_item_shelf_life_code;
166 tab_input(ind).item_shelf_life_days := p_item_shelf_life_days;
167 tab_input(ind).allowed_units_lookup_code := p_allowed_units_lookup_code;
168
169 tab_input(ind).lot_number := LTRIM(RTRIM(p_lot_number));
170 tab_input(ind).parent_lot_number := LTRIM(RTRIM(p_parent_lot_number)); -- Bug #4093379 INVCONV
171 tab_input(ind).origination_type := p_origination_type; -- Bug #4093379 INVCONV
172 tab_input(ind).lot_expiration_date := p_lot_expiration_date;
173 tab_input(ind).expiration_action_date := p_expiration_action_date; -- Bug #4093379 INVCONV
174 tab_input(ind).expiration_action_code := p_expiration_action_code; -- Bug #4093379 INVCONV
175 tab_input(ind).hold_date := p_hold_date; -- Bug #4093379 INVCONV
176 tab_input(ind).reason_id := p_reason_id; -- Bug #4093379 INVCONV
177 tab_input(ind).lpn_id := p_lpn_id;
178 tab_input(ind).lpn_number := p_lpn_number;
179 tab_input(ind).xfr_lpn_id := p_xfr_lpn_id;
180 tab_input(ind).cost_group_id := p_costgroup_id;
181 tab_input(ind).project_id := p_project_id;
182 tab_input(ind).task_id := p_task_id;
183
184 tab_input(ind).transaction_temp_id := NULL;
185 tab_input(ind).transaction_header_id := NULL;
186 tab_input(ind).transaction_batch_id := NULL;
187 tab_input(ind).transaction_batch_seq := NULL;
188
189 tab_input(ind).description := p_description;
190 tab_input(ind).vendor_id := p_vendor_id;
191 tab_input(ind).supplier_lot_number := p_supplier_lot_number;
192 tab_input(ind).territory_code := p_territory_code;
193 tab_input(ind).grade_code := p_grade_code;
194 tab_input(ind).origination_date := p_origination_date;
195 tab_input(ind).date_code := p_date_code;
196 tab_input(ind).status_id := p_status_id;
197 tab_input(ind).change_date := p_change_date;
198 tab_input(ind).age := p_age;
199 tab_input(ind).retest_date := p_retest_date;
200 tab_input(ind).maturity_date := p_maturity_date;
201 tab_input(ind).lot_attribute_category := p_lot_attribute_category;
202 tab_input(ind).item_size := p_item_size;
203 tab_input(ind).color := p_color;
204 tab_input(ind).volume := p_volume;
205 tab_input(ind).volume_uom := p_volume_uom;
206 tab_input(ind).place_of_origin := p_place_of_origin;
207 tab_input(ind).best_by_date := p_best_by_date;
208 tab_input(ind).length := p_length;
209 tab_input(ind).length_uom := p_length_uom;
210 tab_input(ind).recycled_content := p_recycled_content;
211 tab_input(ind).thickness := p_thickness;
212 tab_input(ind).thickness_uom := p_thickness_uom;
213 tab_input(ind).width := p_width;
214 tab_input(ind).width_uom := p_width_uom;
215 tab_input(ind).curl_wrinkle_fold := p_curl_wrinkle_fold;
216 tab_input(ind).c_attribute1 := p_c_attribute1;
217 tab_input(ind).c_attribute2 := p_c_attribute2;
218 tab_input(ind).c_attribute3 := p_c_attribute3;
219 tab_input(ind).c_attribute4 := p_c_attribute4;
220 tab_input(ind).c_attribute5 := p_c_attribute5;
221 tab_input(ind).c_attribute6 := p_c_attribute6;
222 tab_input(ind).c_attribute7 := p_c_attribute7;
223 tab_input(ind).c_attribute8 := p_c_attribute8;
224 tab_input(ind).c_attribute9 := p_c_attribute9;
225 tab_input(ind).c_attribute10 := p_c_attribute10;
226 tab_input(ind).c_attribute11 := p_c_attribute11;
227 tab_input(ind).c_attribute12 := p_c_attribute12;
228 tab_input(ind).c_attribute13 := p_c_attribute13;
229 tab_input(ind).c_attribute14 := p_c_attribute14;
230 tab_input(ind).c_attribute15 := p_c_attribute15;
231 tab_input(ind).c_attribute16 := p_c_attribute16;
232 tab_input(ind).c_attribute17 := p_c_attribute17;
233 tab_input(ind).c_attribute18 := p_c_attribute18;
234 tab_input(ind).c_attribute19 := p_c_attribute19;
235 tab_input(ind).c_attribute20 := p_c_attribute20;
236 tab_input(ind).d_attribute1 := p_d_attribute1;
237 tab_input(ind).d_attribute2 := p_d_attribute2;
238 tab_input(ind).d_attribute3 := p_d_attribute3;
239 tab_input(ind).d_attribute4 := p_d_attribute4;
240 tab_input(ind).d_attribute5 := p_d_attribute5;
241 tab_input(ind).d_attribute6 := p_d_attribute6;
242 tab_input(ind).d_attribute7 := p_d_attribute7;
243 tab_input(ind).d_attribute8 := p_d_attribute8;
244 tab_input(ind).d_attribute9 := p_d_attribute9;
245 tab_input(ind).d_attribute10 := p_d_attribute10;
246 tab_input(ind).n_attribute1 := p_n_attribute1;
247 tab_input(ind).n_attribute2 := p_n_attribute2;
248 tab_input(ind).n_attribute3 := p_n_attribute3;
249 tab_input(ind).n_attribute4 := p_n_attribute4;
250 tab_input(ind).n_attribute5 := p_n_attribute5;
251 tab_input(ind).n_attribute6 := p_n_attribute6;
252 tab_input(ind).n_attribute7 := p_n_attribute7;
253 tab_input(ind).n_attribute8 := p_n_attribute8;
254 tab_input(ind).n_attribute9 := p_n_attribute9;
255 tab_input(ind).n_attribute10 := p_n_attribute10;
256
257 tab_index := ind;
258
259 IF (l_debug = 1) THEN
260 INV_TRX_UTIL_PUB.trace('out :' || p_total_qty || ':' ,g_pkg_name,9);
261 INV_TRX_UTIL_PUB.trace('Ttype :' ||p_transaction_type_id || ':' ,g_pkg_name,9);
262 INV_TRX_UTIL_PUB.trace('Tlot :' ||p_transaction_lot || ':' ,g_pkg_name,9);
263 INV_TRX_UTIL_PUB.trace('Item :' ||p_inventory_item_id || ':' ,g_pkg_name,9);
264 INV_TRX_UTIL_PUB.trace('Org :' ||p_organization_id || ':' ,g_pkg_name,9);
265 INV_TRX_UTIL_PUB.trace('Sub :' ||p_subinventory_code || ':' ,g_pkg_name,9);
266 INV_TRX_UTIL_PUB.trace('Loc :' ||p_locator_id || ':' ,g_pkg_name,9);
267 INV_TRX_UTIL_PUB.trace('TQty :' ||p_transaction_quantity || ':' ,g_pkg_name,9);
268 INV_TRX_UTIL_PUB.trace('PQty:' ||p_primary_quantity || ':' ,g_pkg_name,9);
269 END IF;
270
271 IF (p_total_qty IS NOT NULL)
272 THEN
273 IF (l_debug = 1) THEN
274 INV_TRX_UTIL_PUB.trace('p_split is <> nul',g_pkg_name,9);
275 END IF;
276 lot_split_data_insert.insert_mmtt(p_total_qty,
277 p_transaction_type_id,
278 p_userid,
279 x_return_status);
280 IF (l_debug = 1) THEN
281 INV_TRX_UTIL_PUB.trace('after returning from insert_mmtt:Stat:' || x_return_status || ':',g_pkg_name,9);
282 END IF;
283 IF (x_return_status = FND_API.G_RET_STS_ERROR)
284 THEN
285 IF (l_debug = 1) THEN
286 INV_TRX_UTIL_PUB.trace('FND_API.G_RET_STS_ERRO',g_pkg_name,9);
287 END IF;
288 RAISE FND_API.G_EXC_ERROR ;
289 END IF;
290 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR)
291 THEN
292 IF (l_debug = 1) THEN
293 INV_TRX_UTIL_PUB.trace('FND_API.G_RET_STS_UNEXPERRO',g_pkg_name,9);
294 END IF;
295 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
296 END IF;
297 x_header_id := tab_input(1).transaction_header_id;
298 IF (l_debug = 1) THEN
299 INV_TRX_UTIL_PUB.trace('Header: '|| x_header_id ,g_pkg_name,9);
300 END IF;
301 tab_index :=0;
302 tab_input.delete;
303 END IF;
304 IF (l_debug = 1) THEN
305 INV_TRX_UTIL_PUB.trace('mmtt over ' ,g_pkg_name,9);
306 END IF;
307 FND_MSG_PUB.Count_And_Get
308 ( p_count => x_msg_count ,
309 p_data => x_msg_data
310 );
311
312 EXCEPTION
313 WHEN FND_API.G_EXC_ERROR THEN
314 ROLLBACK ;
315 x_return_status := FND_API.G_RET_STS_ERROR ;
316 FND_MSG_PUB.Count_And_Get
317 (p_encoded => FND_API.G_FALSE,
318 p_count => x_msg_count,
319 p_data => x_msg_data);
320 IF (l_debug = 1) THEN
321 INV_TRX_UTIL_PUB.trace(x_msg_data || 'Close',g_pkg_name,9);
322 END IF;
323 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
324 ROLLBACK ;
325 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
326 FND_MSG_PUB.Count_And_Get
327 (p_encoded => FND_API.G_FALSE,
328 p_count => x_msg_count,
329 p_data => x_msg_data);
330 IF (l_debug = 1) THEN
331 INV_TRX_UTIL_PUB.trace(x_msg_data || 'Close',g_pkg_name,9);
332 END IF;
333 WHEN OTHERS
334 THEN
335 ROLLBACK ;
336 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
337 FND_MSG_PUB.Count_And_Get
338 (p_encoded => FND_API.G_FALSE,
339 p_count => x_msg_count,
340 p_data => x_msg_data);
341 IF (l_debug = 1) THEN
342 INV_TRX_UTIL_PUB.trace(x_msg_data || 'Close',g_pkg_name,9);
343 END IF;
344 END set_table;
345 --
346 PROCEDURE insert_data(p_ind IN NUMBER,
347 p_ind_1st IN NUMBER,
348 p_userid IN NUMBER,
349 p_transaction_action_id IN NUMBER,
350 p_transaction_source_type_id IN NUMBER,
351 p_acct_period_id IN NUMBER,
352 p_parent_id IN NUMBER,
353 p_dist_account_id IN NUMBER,
354 x_return_status OUT NOCOPY VARCHAR2)
355 IS
356 l_msg_data VARCHAR2(2000) := 'None..' ;
357 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
358 BEGIN
359 x_return_status := FND_API.G_RET_STS_SUCCESS;
360 BEGIN
361 l_msg_data := 'mtl_material_transactions_temp';
362 INSERT
363 INTO mtl_material_transactions_temp
364 (transaction_header_id
365 ,transaction_temp_id
366 ,transaction_mode
367 ,lock_flag
368 ,Process_flag
369 ,last_update_date
370 ,last_updated_by
371 ,creation_date
372 ,created_by
373 ,last_update_login
374 ,request_id
375 ,program_application_id
376 ,program_id
377 ,program_update_date
378 ,inventory_item_id
379 ,revision
380 ,organization_id
381 ,subinventory_code
382 ,locator_id
383 ,transaction_quantity
384 ,primary_quantity
385 ,transaction_uom
386 ,transaction_type_id
387 ,transaction_action_id
388 ,transaction_source_type_id
389 ,transaction_date
390 ,acct_period_id
391 ,distribution_account_id
392 ,item_description
393 ,item_location_control_code
394 ,item_restrict_subinv_code
395 ,item_restrict_locators_code
396 ,item_revision_qty_control_code
397 ,item_primary_uom_code
398 ,item_shelf_life_code
399 ,item_shelf_life_days
400 ,item_lot_control_code
401 ,item_serial_control_code
402 ,allowed_units_lookup_code
403 ,parent_transaction_temp_id
404 ,lpn_id
405 ,transfer_lpn_id
406 ,cost_group_id
407 ,project_id
408 ,task_id
409 ,transaction_batch_id
410 ,transaction_batch_seq
411 ,secondary_transaction_quantity -- Bug #4093379 INVCONV
412 ,secondary_uom_code) -- Bug #4093379 INVCONV
413 VALUES
414 ( tab_input(p_ind).transaction_header_id
415 ,tab_input(p_ind).transaction_temp_id
416 ,3
417 ,'N'
418 ,'Y'
419 ,SYSDATE
420 ,p_userid
421 ,SYSDATE
422 ,p_userid
423 ,p_userid
424 ,NULL
425 ,NULL
426 ,NULL
427 ,NULL
428 ,tab_input(p_ind).inventory_item_id
429 ,tab_input(p_ind).revision
430 ,tab_input(p_ind).organization_id
431 ,tab_input(p_ind).subinventory_code
432 ,tab_input(p_ind).locator_id
433 ,tab_input(p_ind).transaction_quantity
434 ,tab_input(p_ind).primary_quantity
435 ,tab_input(p_ind).transaction_uom
436 ,tab_input(p_ind).transaction_type_id
437 ,p_transaction_action_id
438 ,p_transaction_source_type_id
439 ,SYSDATE
440 ,p_acct_period_id
441 ,p_dist_account_id
442 ,tab_input(p_ind_1st).item_description
443 ,tab_input(p_ind_1st).item_location_control_code
444 ,tab_input(p_ind_1st).item_restrict_subinv_code
445 ,tab_input(p_ind_1st).item_restrict_locators_code
446 ,tab_input(p_ind_1st).item_revision_qty_control_code
447 ,tab_input(p_ind_1st).item_primary_uom_code
448 ,tab_input(p_ind_1st).item_shelf_life_code
449 ,tab_input(p_ind_1st).item_shelf_life_days
450 ,2
451 ,1
452 ,tab_input(p_ind_1st).allowed_units_lookup_code
453 ,p_parent_id
454 ,tab_input(p_ind).lpn_id
455 ,tab_input(p_ind).xfr_lpn_id
456 ,tab_input(p_ind).cost_group_id
457 ,tab_input(p_ind).project_id
458 ,tab_input(p_ind).task_id
459 ,tab_input(p_ind).transaction_batch_id
460 ,tab_input(p_ind).transaction_batch_seq
461 ,tab_input(p_ind).secondary_transaction_quantity -- Bug #4093379 INVCONV
462 ,tab_input(p_ind).item_secondary_uom_code); -- Bug #4093379 INVCONV
463 IF (l_debug = 1) THEN
464 INV_TRX_UTIL_PUB.trace('inserted mmtt ..' || p_ind,g_pkg_name,9);
465 END IF;
466
467 IF (l_debug = 1) THEN
468 INV_TRX_UTIL_PUB.trace('inserting mtlt ' || p_ind,g_pkg_name,9);
469 END IF;
470 l_msg_data := 'mtl_transaction_lots_temp ';
471
472 INSERT INTO
473 mtl_transaction_lots_temp
474 (transaction_temp_id
475 ,last_update_date
476 ,last_updated_by
477 ,creation_date
478 ,created_by
479 ,last_update_login
480 ,request_id
481 ,program_application_id
482 ,program_id
483 ,program_update_date
484 ,transaction_quantity
485 ,primary_quantity
486 ,secondary_quantity -- Bug #40993379 INVCONV
487 ,lot_number
488 ,lot_expiration_date
489 ,description
490 ,vendor_id
491 ,supplier_lot_number
492 ,territory_code
493 ,grade_code
494 ,origination_date
495 ,date_code
496 ,status_id
497 ,change_date
498 ,age
499 ,retest_date
500 ,maturity_date
501 ,lot_attribute_category
502 ,item_size
503 ,color
504 ,volume
505 ,volume_uom
506 ,place_of_origin
507 ,best_by_date
508 ,length
509 ,length_uom
510 ,recycled_content
511 ,thickness
512 ,thickness_uom
513 ,width
514 ,width_uom
515 ,curl_wrinkle_fold
516 ,c_attribute1
517 ,c_attribute2
518 ,c_attribute3
519 ,c_attribute4
520 ,c_attribute5
521 ,c_attribute6
522 ,c_attribute7
523 ,c_attribute8
524 ,c_attribute9
525 ,c_attribute10
526 ,c_attribute11
527 ,c_attribute12
528 ,c_attribute13
529 ,c_attribute14
530 ,c_attribute15
531 ,c_attribute16
532 ,c_attribute17
533 ,c_attribute18
534 ,c_attribute19
535 ,c_attribute20
536 ,d_attribute1
537 ,d_attribute2
538 ,d_attribute3
539 ,d_attribute4
540 ,d_attribute5
541 ,d_attribute6
542 ,d_attribute7
543 ,d_attribute8
544 ,d_attribute9
545 ,d_attribute10
546 ,n_attribute1
547 ,n_attribute2
548 ,n_attribute3
549 ,n_attribute4
550 ,n_attribute5
551 ,n_attribute6
552 ,n_attribute7
553 ,n_attribute8
554 ,n_attribute9
555 ,n_attribute10
556 ,secondary_unit_of_measure -- Bug #4093379 INVCONV
557 ,parent_lot_number -- Bug #4093379 INVCONV
558 ,origination_type -- Bug #4093379 INVCONV
559 ,expiration_action_date -- Bug #4093379 INVCONV
560 ,expiration_action_code -- Bug #4093379 INVCONV
561 ,hold_date -- Bug #4093379 INVCONV
562 ,reason_id) -- Bug #4093379 INVCONV
563 VALUES
564 ( tab_input(p_ind).transaction_temp_id
565 ,SYSDATE
566 ,p_userid
567 ,SYSDATE
568 ,p_userid
569 ,p_userid
570 ,NULL
571 ,NULL
572 ,NULL
573 ,NULL
574 ,abs(tab_input(p_ind).transaction_quantity)
575 ,abs(tab_input(p_ind).primary_quantity)
576 ,abs(tab_input(p_ind).secondary_transaction_quantity) -- Bug #4093379 INVCONV
577 ,tab_input(p_ind).lot_number
578 ,tab_input(p_ind).lot_expiration_date
579 ,tab_input(p_ind).description
580 ,tab_input(p_ind).vendor_id
581 ,tab_input(p_ind).supplier_lot_number
582 ,tab_input(p_ind).territory_code
583 ,tab_input(p_ind).grade_code
584 ,tab_input(p_ind).origination_date
585 ,tab_input(p_ind).date_code
586 ,tab_input(p_ind).status_id
587 ,tab_input(p_ind).change_date
588 ,tab_input(p_ind).age
589 ,tab_input(p_ind).retest_date
590 ,tab_input(p_ind).maturity_date
591 ,tab_input(p_ind).lot_attribute_category
592 ,tab_input(p_ind).item_size
593 ,tab_input(p_ind).color
594 ,tab_input(p_ind).volume
595 ,tab_input(p_ind).volume_uom
596 ,tab_input(p_ind).place_of_origin
597 ,tab_input(p_ind).best_by_date
598 ,tab_input(p_ind).length
599 ,tab_input(p_ind).length_uom
600 ,tab_input(p_ind).recycled_content
601 ,tab_input(p_ind).thickness
602 ,tab_input(p_ind).thickness_uom
603 ,tab_input(p_ind).width
604 ,tab_input(p_ind).width_uom
605 ,tab_input(p_ind).curl_wrinkle_fold
606 ,tab_input(p_ind).c_attribute1
607 ,tab_input(p_ind).c_attribute2
608 ,tab_input(p_ind).c_attribute3
609 ,tab_input(p_ind).c_attribute4
610 ,tab_input(p_ind).c_attribute5
611 ,tab_input(p_ind).c_attribute6
612 ,tab_input(p_ind).c_attribute7
613 ,tab_input(p_ind).c_attribute8
614 ,tab_input(p_ind).c_attribute9
615 ,tab_input(p_ind).c_attribute10
616 ,tab_input(p_ind).c_attribute11
617 ,tab_input(p_ind).c_attribute12
618 ,tab_input(p_ind).c_attribute13
619 ,tab_input(p_ind).c_attribute14
620 ,tab_input(p_ind).c_attribute15
621 ,tab_input(p_ind).c_attribute16
622 ,tab_input(p_ind).c_attribute17
623 ,tab_input(p_ind).c_attribute18
624 ,tab_input(p_ind).c_attribute19
625 ,tab_input(p_ind).c_attribute20
626 ,tab_input(p_ind).d_attribute1
627 ,tab_input(p_ind).d_attribute2
628 ,tab_input(p_ind).d_attribute3
629 ,tab_input(p_ind).d_attribute4
630 ,tab_input(p_ind).d_attribute5
631 ,tab_input(p_ind).d_attribute6
632 ,tab_input(p_ind).d_attribute7
633 ,tab_input(p_ind).d_attribute8
634 ,tab_input(p_ind).d_attribute9
635 ,tab_input(p_ind).d_attribute10
636 ,tab_input(p_ind).n_attribute1
637 ,tab_input(p_ind).n_attribute2
638 ,tab_input(p_ind).n_attribute3
639 ,tab_input(p_ind).n_attribute4
640 ,tab_input(p_ind).n_attribute5
641 ,tab_input(p_ind).n_attribute6
642 ,tab_input(p_ind).n_attribute7
643 ,tab_input(p_ind).n_attribute8
644 ,tab_input(p_ind).n_attribute9
645 ,tab_input(p_ind).n_attribute10
646 ,tab_input(p_ind).item_secondary_uom_code -- Bug #4093379 INVCONV
647 ,tab_input(p_ind).parent_lot_number -- Bug #4093379 INVCONV
648 ,tab_input(p_ind).origination_type -- Bug #4093379 INVCONV
649 ,tab_input(p_ind).expiration_action_date -- Bug #4093379 INVCONV
650 ,tab_input(p_ind).expiration_action_code -- Bug #4093379 INVCONV
651 ,tab_input(p_ind).hold_date -- Bug #4093379 INVCONV
652 ,tab_input(p_ind).reason_id -- Bug #4093379 INVCONV
653 );
654 IF (l_debug = 1) THEN
655 INV_TRX_UTIL_PUB.trace('inserted mtlt ' || p_ind,g_pkg_name,9);
656 END IF;
657 EXCEPTION
658 WHEN FND_API.G_EXC_ERROR THEN
659 x_return_status := FND_API.G_RET_STS_ERROR ;
660 IF (l_debug = 1) THEN
661 INV_TRX_UTIL_PUB.trace( 'G_EXC_ERROR:INV_LOT_COMMIT_FAILURE ' || l_msg_data || ':' || sqlerrm,g_pkg_name,9);
662 END IF;
663 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
664 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
665 IF (l_debug = 1) THEN
666 INV_TRX_UTIL_PUB.trace( 'G_EXC_UNEXPECTED_ERROR:INV_LOT_COMMIT_FAILURE ' || l_msg_data || ':' || sqlerrm,g_pkg_name,9);
667 END IF;
668 WHEN OTHERS THEN
669 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
670 FND_MESSAGE.SET_NAME('INV', 'INV_LOT_COMMIT_FAILURE');
671 -- INV_LOT_COMMIT_FAILURE: There has been a database insert error, please contact your dba or Oracle Support'
672 FND_MSG_PUB.ADD;
673 IF (l_debug = 1) THEN
674 INV_TRX_UTIL_PUB.trace( 'Other INV_LOT_COMMIT_FAILURE ' || l_msg_data || ':' || sqlerrm,g_pkg_name,9);
675 END IF;
676 END ;
677 END insert_data;
678
679 PROCEDURE insert_mmtt(p_total_qty IN NUMBER,
680 p_transaction_type_id IN NUMBER,
681 p_userid IN NUMBER,
682 x_return_status OUT NOCOPY VARCHAR2)
683 IS
684 ind INTEGER := 0;
685 ind_1st INTEGER := 1;
686 l_parent_id NUMBER := 0;
687 l_header_id NUMBER := 0;
688 l_temp_id NUMBER := 0;
689 l_batch_id NUMBER := 0;
690 l_transaction_action_id NUMBER := inv_globals.G_Action_inv_lot_split;
691 l_transaction_source_type_id NUMBER := inv_globals.G_SourceType_Inventory;
692 l_acct_period_id org_acct_periods.acct_period_id%TYPE := 0;
693 l_dist_account_id mtl_parameters.distribution_account_id%TYPE := NULL;
694 l_wsm_enabled_flag mtl_parameters.wsm_enabled_flag%TYPE := 'N';
695 l_xfr_lpn_id NUMBER := NULL;
696 l_msg_count NUMBER := NULL;
697 l_msg_data VARCHAR2(2000) := NULL;
698 l_label_status VARCHAR2(300) := NULL;
699
700 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
701 BEGIN
702 IF (l_debug = 1) THEN
703 INV_TRX_UTIL_PUB.trace('in insert_mmtt',g_pkg_name,9);
704 END IF;
705 x_return_status := FND_API.G_RET_STS_SUCCESS;
706
707 l_msg_data := 'select header_id from dual';
708 SELECT mtl_material_transactions_s.NEXTVAL
709 INTO l_header_id
710 FROM DUAL;
711 g_header_id := l_header_id;
712
713 l_msg_data := 'select batch_id from dual';
714 SELECT mtl_material_transactions_s.NEXTVAL
715 INTO l_batch_id
716 FROM DUAL;
717
718 l_msg_data := 'select acct_period_id from org_acct_periods';
719
720 BEGIN
721 IF (l_debug = 1) THEN
722 INV_TRX_UTIL_PUB.trace('getting acct_period_id', g_pkg_name, 9);
723 END IF;
724 SELECT acct_period_id
725 INTO l_acct_period_id
726 FROM org_acct_periods
727 WHERE INV_LE_TIMEZONE_PUB.get_le_day_for_inv_org(Sysdate,tab_input(1).organization_id)
728 >= trunc(period_start_date )
729 AND INV_LE_TIMEZONE_PUB.get_le_day_for_inv_org(Sysdate,tab_input(1).organization_id)
730 <= trunc(schedule_close_date)
731 AND organization_id = tab_input(1).organization_id;
732
733
734 EXCEPTION
735 WHEN NO_DATA_FOUND THEN
736 FND_MESSAGE.SET_NAME('INV', 'INV_NOOPEN_PERIOD_FOR_DATE');
737 FND_MESSAGE.SET_TOKEN('ATTRIBUTE1', SYSDATE);
738 FND_MSG_PUB.ADD;
739 x_return_status := FND_API.G_RET_STS_ERROR ;
740 IF (l_debug = 1) THEN
741 INV_TRX_UTIL_PUB.trace( 'INV_NOOPEN_PERIOD_FOR_DATE '
742 || ':' || sqlerrm,g_pkg_name,9);
743 END IF;
744 RAISE FND_API.G_EXC_ERROR ;
745 WHEN OTHERS THEN
746 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
747 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
748 IF (l_debug = 1) THEN
749 INV_TRX_UTIL_PUB.trace( 'Others:INV_NOOPEN_PERIOD_FOR_DATE '
750 || ':' || sqlerrm,g_pkg_name,9);
751 END IF;
752 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
753 END;
754
755 /* For Lot Translate, need to get the distribution account id and populate
756 into mmtt */
757 IF (p_transaction_type_id = inv_globals.G_type_inv_lot_translate) THEN
758 IF (l_debug = 1) THEN
759 INV_TRX_UTIL_PUB.trace('lot translate before getting dist acct', g_pkg_name, 9);
760 END IF;
761 BEGIN
762 SELECT distribution_account_id,
763 wsm_enabled_flag
764 INTO l_dist_account_id,
765 l_wsm_enabled_flag
766 FROM mtl_parameters
767 WHERE organization_id = tab_input(1).organization_id;
768
769 IF (l_dist_account_id IS NULL AND l_wsm_enabled_flag = 'Y') THEN
770 IF (l_debug = 1) THEN
771 INV_TRX_UTIL_PUB.trace('dist acct is null and wsm enabled = Y', g_pkg_name, 9);
772 END IF;
773 SELECT transaction_account_id
774 INTO l_dist_account_id
775 FROM wsm_parameters
776 WHERE organization_id = tab_input(1).organization_id;
777 END IF;
778
779 IF (l_dist_account_id IS NULL) THEN
780 FND_MESSAGE.SET_NAME('INV', 'INV_NO_DIST_ACCOUNT_ID');
781 FND_MSG_PUB.ADD;
782 IF (l_debug = 1) THEN
783 INV_TRX_UTIL_PUB.trace('INV_NO_DIST_ACCOUNT_ID' || ':' || sqlerrm, g_pkg_name, 9);
784 END IF;
785 x_return_status := FND_API.G_RET_STS_ERROR;
786 RAISE FND_API.G_EXC_ERROR;
787 END IF;
788
789 EXCEPTION
790 WHEN NO_DATA_FOUND THEN
791 FND_MESSAGE.SET_NAME('INV', 'INV_NO_DIST_ACCOUNT_ID');
792 FND_MSG_PUB.ADD;
793 IF (l_debug = 1) THEN
794 INV_TRX_UTIL_PUB.trace('INV_NO_DIST_ACCOUNT_ID' || ':' || sqlerrm, g_pkg_name, 9);
795 END IF;
796 x_return_status := FND_API.G_RET_STS_ERROR;
797 RAISE FND_API.G_EXC_ERROR;
798 WHEN OTHERS THEN
799 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
800 FND_MESSAGE.SET_NAME('INV', 'INV_NO_DIST_ACCOUNT_ID');
801 FND_MSG_PUB.ADD;
802 IF (l_debug = 1) THEN
803 INV_TRX_UTIL_PUB.trace('INV_NO_DIST_ACCOUNT_ID' || ':' || sqlerrm, g_pkg_name, 9);
804 END IF;
805 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
806 END;
807
808 END IF;
809
810 l_msg_data := 'if trx_type';
811 IF ( p_transaction_type_id = inv_globals.G_type_inv_lot_split)
812 THEN
813 tab_input(ind_1st).transaction_quantity := p_total_qty;
814 l_transaction_action_id := inv_globals.G_Action_inv_lot_split;
815 ELSE
816 IF ( p_transaction_type_id = inv_globals.G_type_inv_lot_merge)
817 THEN
818 l_transaction_action_id := inv_globals.G_Action_inv_lot_merge;
819 ELSE
820 tab_input(ind_1st).transaction_quantity := p_total_qty;
821 l_transaction_action_id := inv_globals.G_Action_inv_lot_translate;
822 END IF;
823 END IF;
824 -- populate transaction_temp_id in the table so that you know the parent id to
825 -- be populated in all transactions in mmtt
826 l_msg_data := 'FOR ind IN 1..tab_index';
827 FOR ind IN 1..tab_index
828 LOOP
829 l_msg_data := 'Next temp_id ';
830 SELECT mtl_material_transactions_s.NEXTVAL
831 INTO l_temp_id
832 FROM DUAL;
833
834 tab_input(ind).transaction_temp_id := l_temp_id;
835 tab_input(ind).transaction_header_id := l_header_id;
836 tab_input(ind).transaction_batch_id := l_batch_id;
837
838 /* This condition will be true only for resulting lots where there is a scope for
839 creating a new lpn */
840 IF (tab_input(ind).lpn_number IS NOT NULL) AND
841 (tab_input(ind).lpn_id IS NULL) AND
842 (tab_input(ind).xfr_lpn_id IS NULL OR
843 tab_input(ind).xfr_lpn_id = 0 ) THEN
844 BEGIN
845 l_msg_data := 'select lpn';
846 SELECT lpn_id
847 INTO l_xfr_lpn_id
848 FROM wms_license_plate_numbers
849 WHERE license_plate_number = tab_input(ind).lpn_number
850 AND parent_lpn_id IS NULL
851 AND lpn_context IN (1,5); -- Bug No 3886482, Pick LPNS with status 'Defined But Not Used'
852
853 IF (l_debug = 1) THEN
854 INV_TRX_UTIL_PUB.trace('Lpn Found for ' || tab_input(ind).lpn_number ||
855 ':Id: ' || l_xfr_lpn_id,g_pkg_name,9);
856 END IF;
857 EXCEPTION
858 WHEN NO_DATA_FOUND THEN
859 IF (l_debug = 1) THEN
860 INV_TRX_UTIL_PUB.trace('No Lpn Found for ' || tab_input(ind).lpn_number,g_pkg_name,9);
861 END IF;
862 l_msg_data := l_msg_data || 'NO_DATA_FOUND: ' || sqlerrm;
863 IF (l_debug = 1) THEN
864 INV_TRX_UTIL_PUB.trace('msg:' || l_msg_data,g_pkg_name,9);
865 END IF;
866 WMS_Container_PUB.Create_LPN
867 ( p_api_version => 1.0,
868 x_return_status => x_return_status ,
869 x_msg_count => l_msg_count ,
870 x_msg_data => l_msg_data ,
871 p_lpn => tab_input(ind).lpn_number ,
872 p_organization_id => tab_input(ind).organization_id ,
873 p_revision => tab_input(ind).revision ,
874 p_lot_number => tab_input(ind).lot_number ,
875 p_subinventory => tab_input(ind).subinventory_code,
876 p_locator_id => tab_input(ind).locator_id ,
877 x_lpn_id => l_xfr_lpn_id);
878
879 IF (l_debug = 1) THEN
880 INV_TRX_UTIL_PUB.trace('Generate_lpn for ' || tab_input(ind).lpn_number ||
881 ':Id: ' || l_xfr_lpn_id ||
882 'stat ' || x_return_status ||
883 'msgt ' || l_msg_count ||
884 'data ' || l_msg_data,g_pkg_name,9);
885 END IF;
886
887 IF (x_return_status = FND_API.G_RET_STS_ERROR)
888 THEN
889 IF (l_debug = 1) THEN
890 INV_TRX_UTIL_PUB.trace('FND_API.G_RET_STS_ERROR',g_pkg_name,9);
891 END IF;
892 FND_MESSAGE.SET_NAME('WMS', 'WMS_LPN_GENERATION_FAIL');
893 FND_MSG_PUB.ADD;
894 RAISE FND_API.G_EXC_ERROR ;
895 END IF;
896 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR)
897 THEN
898 FND_MESSAGE.SET_NAME('WMS', 'WMS_LPN_GENERATION_FAIL');
899 FND_MSG_PUB.ADD;
900 IF (l_debug = 1) THEN
901 INV_TRX_UTIL_PUB.trace('FND_API.G_RET_STS_UNEXPERROR',g_pkg_name,9);
902 END IF;
903 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
904 END IF;
905
906 WHEN OTHERS THEN
907 IF (l_debug = 1) THEN
908 INV_TRX_UTIL_PUB.trace('No Lpn Found for ' || tab_input(ind).lpn_number,g_pkg_name,9);
909 END IF;
910 l_msg_data := 'LPN not found: ' || sqlerrm;
911 IF (l_debug = 1) THEN
912 INV_TRX_UTIL_PUB.trace(l_msg_data,g_pkg_name,9);
913 END IF;
914 FND_MESSAGE.SET_NAME('INV', 'INV_FIELD_INVALID');
915 FND_MESSAGE.SET_TOKEN('ENTITY1', tab_input(ind).lpn_number);
916 FND_MSG_PUB.ADD;
917 RAISE FND_API.G_EXC_ERROR ;
918 EXIT;
919 END ;
920 ELSE
921 l_xfr_lpn_id := tab_input(ind).xfr_lpn_id;
922 END IF;
923
924 tab_input(ind).xfr_lpn_id := l_xfr_lpn_id;
925
926 IF (tab_input(ind).transaction_lot = 'S'
927 AND (p_transaction_type_id = inv_globals.G_type_inv_lot_split OR p_transaction_type_id = inv_globals.G_type_inv_lot_translate))
928 THEN
929 l_parent_id := l_temp_id;
930 ELSE
931 IF (tab_input(ind).transaction_lot = 'R'
932 AND p_transaction_type_id = inv_globals.G_type_inv_lot_merge)
933 THEN
934 l_parent_id := l_temp_id;
935 END IF;
936 END IF;
937
938 IF (tab_input(ind_1st).item_primary_uom_code <> tab_input(ind).transaction_uom)
939 THEN
940 tab_input(ind).primary_quantity := inv_convert.inv_um_convert
941 ( tab_input(ind).inventory_item_id
942 , 5
943 , tab_input(ind).transaction_quantity
944 , tab_input(ind).transaction_uom
945 , tab_input(ind_1st).item_primary_uom_code
946 , null
947 , null);
948 END IF;
949 IF (tab_input(ind).transaction_lot = 'S')
950 THEN
951 tab_input(ind).primary_quantity := tab_input(ind).primary_quantity * -1;
952 tab_input(ind).transaction_quantity := tab_input(ind).transaction_quantity * -1;
953 tab_input(ind).secondary_transaction_quantity := tab_input(ind).secondary_transaction_quantity * -1; -- Bug #4093379 INVCONV
954 END IF;
955
956 IF (p_transaction_type_id = inv_globals.G_type_inv_lot_translate OR
957 p_transaction_type_id = inv_globals.G_type_inv_lot_split) THEN
958 SELECT MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL
959 INTO tab_input(ind).transaction_batch_seq
960 FROM DUAL;
961 END IF;
962
963 END LOOP;
964
965 IF ( p_transaction_type_id = inv_globals.G_type_inv_lot_merge) THEN
966 FOR ind IN REVERSE 1..tab_index
967 LOOP
968 SELECT MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL
969 INTO tab_input(ind).transaction_batch_seq
970 FROM DUAL;
971 END LOOP;
972 END IF;
973
974 IF ( p_transaction_type_id = inv_globals.G_type_inv_lot_split OR p_transaction_type_id = inv_globals.G_type_inv_lot_translate)
975 THEN
976 FOR ind IN 1..tab_index
977 LOOP
978 IF (l_debug = 1) THEN
979 INV_TRX_UTIL_PUB.trace(ind || ': Header_id ' || tab_input(ind).transaction_header_id,g_pkg_name,9);
980 INV_TRX_UTIL_PUB.trace(ind || ': Parent_id ' || l_parent_id,g_pkg_name,9);
981 INV_TRX_UTIL_PUB.trace(ind || ': Temp_id ' || tab_input(ind).transaction_temp_id,g_pkg_name,9);
982 INV_TRX_UTIL_PUB.trace(ind || ': Batch_id ' || tab_input(ind).transaction_batch_id,g_pkg_name,9);
983 INV_TRX_UTIL_PUB.trace(ind || ': Batch_seq ' || tab_input(ind).transaction_batch_seq,g_pkg_name,9);
984 INV_TRX_UTIL_PUB.trace(ind || ': 1 ' || tab_input(ind).transaction_type_id,g_pkg_name,9);
985 INV_TRX_UTIL_PUB.trace(ind || ': 2 ' || tab_input(ind).transaction_lot ,g_pkg_name,9);
986 INV_TRX_UTIL_PUB.trace(ind || ': 3 ' || tab_input(ind).inventory_item_id ,g_pkg_name,9);
987 INV_TRX_UTIL_PUB.trace(ind || ': 4 ' || tab_input(ind).revision ,g_pkg_name,9);
988 INV_TRX_UTIL_PUB.trace(ind || ': 5 ' || tab_input(ind).organization_id ,g_pkg_name,9);
989 INV_TRX_UTIL_PUB.trace(ind || ': 6 ' || tab_input(ind).subinventory_code ,g_pkg_name,9);
990 INV_TRX_UTIL_PUB.trace(ind || ': 7 ' || tab_input(ind).locator_id ,g_pkg_name,9);
991 INV_TRX_UTIL_PUB.trace(ind || ': 8 ' || tab_input(ind).transaction_quantity,g_pkg_name,9);
992 INV_TRX_UTIL_PUB.trace(ind || ': 9 ' || tab_input(ind).primary_quantity ,g_pkg_name,9);
993 INV_TRX_UTIL_PUB.trace(ind || ': 0 ' || tab_input(ind).transaction_uom ,g_pkg_name,9);
994 INV_TRX_UTIL_PUB.trace(ind || ': 1 ' || tab_input(ind).secondary_transaction_quantity ,g_pkg_name,9); -- Bug #4093379 INVCONV
995 END IF;
996
997 IF (l_debug = 1) THEN
998 INV_TRX_UTIL_PUB.trace(ind || ': 1 ' || tab_input(ind_1st).item_description ,g_pkg_name,9);
999 INV_TRX_UTIL_PUB.trace(ind || ': 2 ' || tab_input(ind_1st).item_location_control_code,g_pkg_name,9);
1000 INV_TRX_UTIL_PUB.trace(ind || ': 3 ' || tab_input(ind_1st).item_restrict_subinv_code ,g_pkg_name,9);
1001 INV_TRX_UTIL_PUB.trace(ind || ': 4 ' || tab_input(ind_1st).item_restrict_locators_code,g_pkg_name,9);
1002 INV_TRX_UTIL_PUB.trace(ind || ': 5 ' || tab_input(ind_1st).item_revision_qty_control_code,g_pkg_name,9);
1003 INV_TRX_UTIL_PUB.trace(ind || ': 6 ' || tab_input(ind_1st).item_primary_uom_code ,g_pkg_name,9);
1004 INV_TRX_UTIL_PUB.trace(ind || ': 7 ' || tab_input(ind_1st).item_shelf_life_code ,g_pkg_name,9);
1005 INV_TRX_UTIL_PUB.trace(ind || ': 8 ' || tab_input(ind_1st).item_shelf_life_days ,g_pkg_name,9);
1006 INV_TRX_UTIL_PUB.trace(ind || ': 9 ' || tab_input(ind_1st).allowed_units_lookup_code ,g_pkg_name,9);
1007 INV_TRX_UTIL_PUB.trace(ind || ': 0 ' || tab_input(ind).lot_number ,g_pkg_name,9);
1008 INV_TRX_UTIL_PUB.trace(ind || ': 1 ' || tab_input(ind).lot_expiration_date ,g_pkg_name,9);
1009 INV_TRX_UTIL_PUB.trace(ind || ': 2 ' || p_total_qty,g_pkg_name,9);
1010 INV_TRX_UTIL_PUB.trace(ind || ': 3 ' || tab_input(ind).lpn_id,g_pkg_name,9);
1011 INV_TRX_UTIL_PUB.trace(ind || ': 4 ' || tab_input(ind).lpn_number ,g_pkg_name,9);
1012 INV_TRX_UTIL_PUB.trace(ind || ': 5 ' || tab_input(ind).item_secondary_uom_code ,g_pkg_name,9); -- Bug #4093379 INVCONV
1013 INV_TRX_UTIL_PUB.trace(ind || ': 6 ' || tab_input(ind).parent_lot_number ,g_pkg_name,9); -- Bug #4093379 INVCONV
1014 INV_TRX_UTIL_PUB.trace(ind || ': 7 ' || tab_input(ind).expiration_action_date ,g_pkg_name,9); -- Bug #4093379 INVCONV
1015 INV_TRX_UTIL_PUB.trace(ind || ': 8 ' || tab_input(ind).expiration_action_code ,g_pkg_name,9); -- Bug #4093379 INVCONV
1016 INV_TRX_UTIL_PUB.trace(ind || ': 9 ' || tab_input(ind).hold_date ,g_pkg_name,9); -- Bug #4093379 INVCONV
1017 INV_TRX_UTIL_PUB.trace(ind || ': 0 ' || tab_input(ind).reason_id ,g_pkg_name,9); -- Bug #4093379 INVCONV
1018 END IF;
1019
1020 insert_data (p_ind => ind,
1021 p_ind_1st => ind_1st ,
1022 p_userid => p_userid ,
1023 p_transaction_action_id => l_transaction_action_id ,
1024 p_transaction_source_type_id => l_transaction_source_type_id ,
1025 p_acct_period_id => l_acct_period_id ,
1026 p_parent_id => l_parent_id ,
1027 p_dist_account_id => l_dist_account_id,
1028 x_return_status => x_return_status);
1029
1030 IF (x_return_status = FND_API.G_RET_STS_ERROR)
1031 THEN
1032 IF (l_debug = 1) THEN
1033 INV_TRX_UTIL_PUB.trace('Split..FND_API.G_RET_STS_ERROR',g_pkg_name,9);
1034 END IF;
1035 RAISE FND_API.G_EXC_ERROR ;
1036 END IF;
1037 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR)
1038 THEN
1039 IF (l_debug = 1) THEN
1040 INV_TRX_UTIL_PUB.trace('Split..FND_API.G_RET_STS_UNEXPERROR',g_pkg_name,9);
1041 END IF;
1042 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1043 END IF;
1044
1045 END LOOP;
1046 END IF;
1047
1048 IF ( p_transaction_type_id = inv_globals.G_type_inv_lot_merge) THEN
1049 FOR ind IN REVERSE 1..tab_index
1050 LOOP
1051 IF (l_debug = 1) THEN
1052 INV_TRX_UTIL_PUB.trace(ind || ': Header_id ' || tab_input(ind).transaction_header_id,g_pkg_name,9);
1053 INV_TRX_UTIL_PUB.trace(ind || ': Parent_id ' || l_parent_id,g_pkg_name,9);
1054 INV_TRX_UTIL_PUB.trace(ind || ': Temp_id ' || tab_input(ind).transaction_temp_id,g_pkg_name,9);
1055 INV_TRX_UTIL_PUB.trace(ind || ': Batch_id ' || tab_input(ind).transaction_batch_id,g_pkg_name,9);
1056 INV_TRX_UTIL_PUB.trace(ind || ': Batch_seq ' || tab_input(ind).transaction_batch_seq,g_pkg_name,9);
1057 INV_TRX_UTIL_PUB.trace(ind || ': 1 ' || tab_input(ind).transaction_type_id,g_pkg_name,9);
1058 INV_TRX_UTIL_PUB.trace(ind || ': 2 ' || tab_input(ind).transaction_lot ,g_pkg_name,9);
1059 INV_TRX_UTIL_PUB.trace(ind || ': 3 ' || tab_input(ind).inventory_item_id ,g_pkg_name,9);
1060 INV_TRX_UTIL_PUB.trace(ind || ': 4 ' || tab_input(ind).revision ,g_pkg_name,9);
1061 INV_TRX_UTIL_PUB.trace(ind || ': 5 ' || tab_input(ind).organization_id ,g_pkg_name,9);
1062 INV_TRX_UTIL_PUB.trace(ind || ': 6 ' || tab_input(ind).subinventory_code ,g_pkg_name,9);
1063 INV_TRX_UTIL_PUB.trace(ind || ': 7 ' || tab_input(ind).locator_id ,g_pkg_name,9);
1064 INV_TRX_UTIL_PUB.trace(ind || ': 8 ' || tab_input(ind).transaction_quantity,g_pkg_name,9);
1065 INV_TRX_UTIL_PUB.trace(ind || ': 9 ' || tab_input(ind).primary_quantity ,g_pkg_name,9);
1066 INV_TRX_UTIL_PUB.trace(ind || ': 0 ' || tab_input(ind).transaction_uom ,g_pkg_name,9);
1067 INV_TRX_UTIL_PUB.trace(ind || ': 1 ' || tab_input(ind).secondary_transaction_quantity ,g_pkg_name,9); -- Bug #4093379 INVCONV
1068 END IF;
1069
1070 IF (l_debug = 1) THEN
1071 INV_TRX_UTIL_PUB.trace(ind || ': 1 ' || tab_input(ind_1st).item_description ,g_pkg_name,9);
1072 INV_TRX_UTIL_PUB.trace(ind || ': 2 ' || tab_input(ind_1st).item_location_control_code,g_pkg_name,9);
1073 INV_TRX_UTIL_PUB.trace(ind || ': 3 ' || tab_input(ind_1st).item_restrict_subinv_code ,g_pkg_name,9);
1074 INV_TRX_UTIL_PUB.trace(ind || ': 4 ' || tab_input(ind_1st).item_restrict_locators_code,g_pkg_name,9);
1075 INV_TRX_UTIL_PUB.trace(ind || ': 5 ' || tab_input(ind_1st).item_revision_qty_control_code,g_pkg_name,9);
1076 INV_TRX_UTIL_PUB.trace(ind || ': 6 ' || tab_input(ind_1st).item_primary_uom_code ,g_pkg_name,9);
1077 INV_TRX_UTIL_PUB.trace(ind || ': 7 ' || tab_input(ind_1st).item_shelf_life_code ,g_pkg_name,9);
1078 INV_TRX_UTIL_PUB.trace(ind || ': 8 ' || tab_input(ind_1st).item_shelf_life_days ,g_pkg_name,9);
1079 INV_TRX_UTIL_PUB.trace(ind || ': 9 ' || tab_input(ind_1st).allowed_units_lookup_code ,g_pkg_name,9);
1080 INV_TRX_UTIL_PUB.trace(ind || ': 0 ' || tab_input(ind).lot_number ,g_pkg_name,9);
1081 INV_TRX_UTIL_PUB.trace(ind || ': 1 ' || tab_input(ind).lot_expiration_date ,g_pkg_name,9);
1082 INV_TRX_UTIL_PUB.trace(ind || ': 2 ' || p_total_qty,g_pkg_name,9);
1083 INV_TRX_UTIL_PUB.trace(ind || ': 3 ' || tab_input(ind).lpn_id ,g_pkg_name,9);
1084 INV_TRX_UTIL_PUB.trace(ind || ': 4 ' || tab_input(ind).lpn_number ,g_pkg_name,9);
1085 INV_TRX_UTIL_PUB.trace(ind || ': 5 ' || tab_input(ind).item_secondary_uom_code ,g_pkg_name,9); -- Bug #4093379 INVCONV
1086 INV_TRX_UTIL_PUB.trace(ind || ': 6 ' || tab_input(ind).parent_lot_number ,g_pkg_name,9); -- Bug #4093379 INVCONV
1087 INV_TRX_UTIL_PUB.trace(ind || ': 7 ' || tab_input(ind).expiration_action_date ,g_pkg_name,9); -- Bug #4093379 INVCONV
1088 INV_TRX_UTIL_PUB.trace(ind || ': 8 ' || tab_input(ind).expiration_action_code ,g_pkg_name,9); -- Bug #4093379 INVCONV
1089 INV_TRX_UTIL_PUB.trace(ind || ': 9 ' || tab_input(ind).hold_date ,g_pkg_name,9); -- Bug #4093379 INVCONV
1090 INV_TRX_UTIL_PUB.trace(ind || ': 0 ' || tab_input(ind).reason_id ,g_pkg_name,9); -- Bug #4093379 INVCONV
1091 END IF;
1092
1093 insert_data (p_ind => ind,
1094 p_ind_1st => ind_1st ,
1095 p_userid => p_userid ,
1096 p_transaction_action_id => l_transaction_action_id ,
1097 p_transaction_source_type_id => l_transaction_source_type_id ,
1098 p_acct_period_id => l_acct_period_id ,
1099 p_parent_id => l_parent_id ,
1100 p_dist_account_id => l_dist_account_id,
1101 x_return_status => x_return_status);
1102
1103 IF (x_return_status = FND_API.G_RET_STS_ERROR)
1104 THEN
1105 IF (l_debug = 1) THEN
1106 INV_TRX_UTIL_PUB.trace('Merge..FND_API.G_RET_STS_ERROR',g_pkg_name,9);
1107 INV_TRX_UTIL_PUB.trace('x_msg_data:x_label_status:'|| l_msg_data || ':'
1108 || l_label_status || ':' ,g_pkg_name,9);
1109 END IF;
1110 RAISE FND_API.G_EXC_ERROR ;
1111 END IF;
1112 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR)
1113 THEN
1114 IF (l_debug = 1) THEN
1115 INV_TRX_UTIL_PUB.trace('Merge..FND_API.G_RET_STS_UNEXPERROR',g_pkg_name,9);
1116 INV_TRX_UTIL_PUB.trace('x_msg_data:x_label_status:'|| l_msg_data || ':'
1117 || l_label_status || ':' ,g_pkg_name,9);
1118 END IF;
1119 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1120 END IF;
1121
1122 END LOOP;
1123 END IF;
1124 EXCEPTION
1125 WHEN FND_API.G_EXC_ERROR THEN
1126 x_return_status := FND_API.G_RET_STS_ERROR ;
1127 IF (l_debug = 1) THEN
1128 INV_TRX_UTIL_PUB.trace('insert_mmtt:G_EXC_ERROR: ' || sqlerrm ,g_pkg_name,9);
1129 END IF;
1130 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1131 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1132 IF (l_debug = 1) THEN
1133 INV_TRX_UTIL_PUB.trace('insert_mmtt:G_EXC_UNEXPECTED_ERROR: ' || sqlerrm ,g_pkg_name,9);
1134 END IF;
1135 WHEN OTHERS
1136 THEN
1137 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1138 IF (l_debug = 1) THEN
1139 INV_TRX_UTIL_PUB.trace('FND_API.G_RET_STS_UNEXP_ERROR; outer most others' || sqlerrm,g_pkg_name,9);
1140 INV_TRX_UTIL_PUB.trace('other outer most...file closed' ,g_pkg_name,9);
1141 INV_TRX_UTIL_PUB.trace(l_msg_data,g_pkg_name,9);
1142 END IF;
1143 END insert_mmtt;
1144 --
1145 PROCEDURE select_init_parameters(
1146 p_organization_id IN MTL_ORGANIZATIONS.organization_id%TYPE,
1147 x_stock_locator_code OUT NOCOPY MTL_parameters.stock_locator_control_code%TYPE,
1148 x_wmsinstall OUT NOCOPY VARCHAR2,
1149 x_wmsorg OUT NOCOPY VARCHAR2,
1150 x_split_txnname OUT NOCOPY MTL_TRANSACTION_TYPES.transaction_type_name%TYPE,
1151 x_merge_txnname OUT NOCOPY MTL_TRANSACTION_TYPES.transaction_type_name%TYPE,
1152 x_translate_txnname OUT NOCOPY MTL_TRANSACTION_TYPES.transaction_type_name%TYPE,
1153 x_cost_group_id OUT NOCOPY CST_COST_GROUPS.cost_group_id%TYPE,
1154 x_primary_cost_method OUT NOCOPY MTL_PARAMETERS.primary_cost_method%TYPE,
1155 x_wsm_enabled_flag OUT NOCOPY VARCHAR2,
1156 x_return_status OUT NOCOPY VARCHAR2,
1157 x_msg_data OUT NOCOPY VARCHAR2,
1158 x_msg_count OUT NOCOPY NUMBER,
1159 x_dist_account_id OUT NOCOPY mtl_parameters.distribution_account_id%TYPE
1160 )
1161
1162 IS
1163 l_transaction_type_id MTL_TRANSACTION_TYPES.transaction_type_id%TYPE;
1164 CURSOR cur_trx_types IS
1165 SELECT transaction_type_name , transaction_type_id
1166 FROM mtl_transaction_types
1167 WHERE transaction_type_id IN ( INV_Globals.G_type_inv_lot_split,
1168 INV_Globals.G_type_inv_lot_merge,
1169 INV_Globals.G_type_inv_lot_translate);
1170 -- (82, 83, 84);
1171 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1172 BEGIN
1173 x_return_status := FND_API.G_RET_STS_SUCCESS;
1174 x_wmsinstall := 'N';
1175 x_wmsorg := 'N';
1176 x_split_txnname := '';
1177 x_merge_txnname := '';
1178 x_stock_locator_code := '';
1179 x_wsm_enabled_flag := 'N';
1180 l_transaction_type_id := '';
1181
1182 BEGIN
1183 FOR rec_trx_types IN cur_trx_types
1184 LOOP
1185 IF (rec_trx_types.transaction_type_id = 82) THEN
1186 x_split_txnname := rec_trx_types.transaction_type_name;
1187 ELSE IF (rec_trx_types.transaction_type_id = 83) THEN
1188 x_merge_txnname := rec_trx_types.transaction_type_name;
1189 ELSE
1190 x_translate_txnname := rec_trx_types.transaction_type_name;
1191 END IF;
1192 END IF;
1193 END LOOP;
1194 EXCEPTION
1195 WHEN NO_DATA_FOUND THEN
1196 FND_MESSAGE.SET_NAME('INV', 'INV_TRANSACTION_TYPE_ERROR');
1197 FND_MESSAGE.SET_TOKEN('ENTITY1', INV_Globals.G_type_inv_lot_split || ',' ||
1198 INV_Globals.G_type_inv_lot_merge || ',' ||
1199 INV_Globals.G_type_inv_lot_translate);
1200 FND_MSG_PUB.ADD;
1201 RAISE FND_API.G_EXC_ERROR ;
1202
1203 END;
1204
1205 BEGIN
1206 SELECT stock_locator_control_code
1207 ,default_cost_group_id
1208 ,primary_cost_method
1209 ,wsm_enabled_flag
1210 ,distribution_account_id
1211 INTO x_stock_locator_code
1212 ,x_cost_group_id
1213 ,x_primary_cost_method
1214 ,x_wsm_enabled_flag
1215 , x_dist_account_id
1216 FROM mtl_parameters
1217 WHERE organization_id = p_organization_id;
1218
1219 IF (x_dist_account_id IS NULL AND x_wsm_enabled_flag = 'Y') THEN
1220 IF (l_debug = 1) THEN
1221 INV_TRX_UTIL_PUB.trace('dist acct is null and wsm enabled = Y', g_pkg_name, 9);
1222 END IF;
1223 SELECT NVL(transaction_account_id, 0)
1224 INTO x_dist_account_id
1225 FROM wsm_parameters
1226 WHERE organization_id = p_organization_id;
1227 END IF;
1228
1229 IF (l_debug = 1) THEN
1230 INV_TRX_UTIL_PUB.trace('dist acct = ' || x_dist_account_id, g_pkg_name, 9);
1231 END IF;
1232
1233 EXCEPTION
1234 WHEN NO_DATA_FOUND THEN
1235 FND_MESSAGE.SET_NAME('INV', 'INV_NO_LOCATOR_CONTROL_ORG');
1236 FND_MSG_PUB.ADD;
1237 RAISE FND_API.G_EXC_ERROR ;
1238 WHEN OTHERS THEN
1239 FND_MESSAGE.SET_NAME('INV', 'INV_NO_LOCATOR_CONTROL_ORG');
1240 FND_MSG_PUB.ADD;
1241 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1242 END;
1243
1244 -- Check if the WMS is installed ?
1245 BEGIN
1246 INV_TXN_VALIDATIONS.CHECK_WMS_INSTALL (
1247 x_return_status => x_wmsinstall,
1248 p_msg_count => x_msg_count,
1249 p_msg_data => x_msg_data,
1250 p_org => NULL) ;
1251
1252 EXCEPTION
1253 WHEN OTHERS THEN
1254 FND_MESSAGE.SET_NAME('WMS', 'WMS_INSTALL_CHK_ERROR');
1255 -- WMS_INSTALL_CHK_ERROR : Error determining if WMS is Installed
1256 FND_MSG_PUB.ADD;
1257 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1258 END;
1259
1260 -- Check if the organization is a WMS organization ?
1261 BEGIN
1262 INV_TXN_VALIDATIONS.CHECK_WMS_INSTALL (
1263 x_return_status => x_wmsorg,
1264 p_msg_count => x_msg_count,
1265 p_msg_data => x_msg_data,
1266 p_org => p_organization_id);
1267
1268 EXCEPTION
1269 WHEN OTHERS THEN
1270 FND_MESSAGE.SET_NAME('INV', 'INV_WMS_ORG');
1271 -- INV_WMS_ORG: Error Determining if Organization is WMS enabled
1272 FND_MSG_PUB.ADD;
1273 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1274 END;
1275
1276 x_return_status := FND_API.G_RET_STS_SUCCESS;
1277 FND_MSG_PUB.Count_And_Get
1278 ( p_count => x_msg_count ,
1279 p_data => x_msg_data
1280 );
1281 EXCEPTION
1282 WHEN FND_API.G_EXC_ERROR THEN
1283 x_return_status := FND_API.G_RET_STS_ERROR ;
1284 FND_MSG_PUB.Count_And_Get
1285 (p_encoded => FND_API.G_FALSE,
1286 p_count => x_msg_count,
1287 p_data => x_msg_data);
1288 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1289 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1290 FND_MSG_PUB.Count_And_Get
1291 (p_encoded => FND_API.G_FALSE,
1292 p_count => x_msg_count,
1293 p_data => x_msg_data);
1294 WHEN OTHERS THEN
1295 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1296 FND_MSG_PUB.Count_And_Get
1297 (p_encoded => FND_API.G_FALSE,
1298 p_count => x_msg_count,
1299 p_data => x_msg_data);
1300
1301 END select_init_parameters;
1302
1303 --
1304 END lot_split_data_insert;
1305