[Home] [Help]
PACKAGE BODY: APPS.MTL_LOT_NUMBERS_PKG
Source
1 PACKAGE BODY MTL_LOT_NUMBERS_PKG as
2 /* $Header: INVTDILB.pls 120.5.12020000.2 2012/08/13 06:16:01 avrose ship $ */
3
4
5 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
6
7 X_Inventory_Item_Id NUMBER,
8 X_Organization_Id NUMBER,
9 X_Lot_Number VARCHAR2,
10 X_Expiration_Date DATE,
11 X_Disable_Flag NUMBER,
12 X_Attribute_Category VARCHAR2,
13 X_Attribute1 VARCHAR2,
14 X_Attribute2 VARCHAR2,
15 X_Attribute3 VARCHAR2,
16 X_Attribute4 VARCHAR2,
17 X_Attribute5 VARCHAR2,
18 X_Attribute6 VARCHAR2,
19 X_Attribute7 VARCHAR2,
20 X_Attribute8 VARCHAR2,
21 X_Attribute9 VARCHAR2,
22 X_Attribute10 VARCHAR2,
23 X_Attribute11 VARCHAR2,
24 X_Attribute12 VARCHAR2,
25 X_Attribute13 VARCHAR2,
26 X_Attribute14 VARCHAR2,
27 X_Attribute15 VARCHAR2,
28 X_Status_ID NUMBER,
29 X_Description VARCHAR2,
30 X_Vendor_Id NUMBER,
31 X_Grade_Code VARCHAR2,
32 X_Origination_Date DATE,
33 X_Date_Code VARCHAR2,
34 X_Change_Date DATE,
35 X_Age NUMBER,
36 X_Retest_Date DATE,
37 X_Maturity_Date DATE,
38 X_Lot_Attribute_Category VARCHAR2,
39 X_Item_Size NUMBER,
40 X_Color VARCHAR2,
41 X_Volume NUMBER,
42 X_Volume_UOM VARCHAR2,
43 X_Place_of_Origin VARCHAR2,
44 X_Best_by_Date DATE,
45 X_Length NUMBER,
46 X_Length_UOM VARCHAR2,
47 X_Recycled_Content NUMBER,
48 X_Thickness NUMBER,
49 X_Thickness_UOM VARCHAR2,
50 X_Width NUMBER,
51 X_Width_UOM VARCHAR2,
52 X_Curl_Wrinkle_Fold VARCHAR2,
53 X_C_Attribute1 VARCHAR2,
54 X_C_Attribute2 VARCHAR2,
55 X_C_Attribute3 VARCHAR2,
56 X_C_Attribute4 VARCHAR2,
57 X_C_Attribute5 VARCHAR2,
58 X_C_Attribute6 VARCHAR2,
59 X_C_Attribute7 VARCHAR2,
60 X_C_Attribute8 VARCHAR2,
61 X_C_Attribute9 VARCHAR2,
62 X_C_Attribute10 VARCHAR2,
63 X_C_Attribute11 VARCHAR2,
64 X_C_Attribute12 VARCHAR2,
65 X_C_Attribute13 VARCHAR2,
66 X_C_Attribute14 VARCHAR2,
67 X_C_Attribute15 VARCHAR2,
68 X_C_Attribute16 VARCHAR2,
69 X_C_Attribute17 VARCHAR2,
70 X_C_Attribute18 VARCHAR2,
71 X_C_Attribute19 VARCHAR2,
72 X_C_Attribute20 VARCHAR2,
73 X_D_Attribute1 DATE,
74 X_D_Attribute2 DATE,
75 X_D_Attribute3 DATE,
76 X_D_Attribute4 DATE,
77 X_D_Attribute5 DATE,
78 X_D_Attribute6 DATE,
79 X_D_Attribute7 DATE,
80 X_D_Attribute8 DATE,
81 X_D_Attribute9 DATE,
82 X_D_Attribute10 DATE,
83 X_N_Attribute1 NUMBER,
84 X_N_Attribute2 NUMBER,
85 X_N_Attribute3 NUMBER,
86 X_N_Attribute4 NUMBER,
87 X_N_Attribute5 NUMBER,
88 X_N_Attribute6 NUMBER,
89 X_N_Attribute7 NUMBER,
90 X_N_Attribute8 NUMBER,
91 X_N_Attribute10 NUMBER,
92 X_Supplier_Lot_Number VARCHAR2,
93 X_N_Attribute9 NUMBER,
94 X_Territory_Code VARCHAR2,
95 X_Parent_Lot_Number VARCHAR2,
96 X_Origination_Type NUMBER,
97 X_Expiration_Action_Date DATE,
98 X_Expiration_Action_Code VARCHAR2,
99 X_Hold_Date DATE
100
101 ) IS
102 CURSOR C IS
103 SELECT *
104 FROM mtl_lot_numbers
105 WHERE rowid = X_Rowid
106 FOR UPDATE of Organization_Id NOWAIT;
107 Recinfo C%ROWTYPE;
108
109
110 BEGIN
111 OPEN C;
112 FETCH C INTO Recinfo;
113 if (C%NOTFOUND) then
114 CLOSE C;
115 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
116 APP_EXCEPTION.Raise_Exception;
117 end if;
118 CLOSE C;
119 if (
120
121 (Recinfo.inventory_item_id = X_Inventory_Item_Id)
122 AND (Recinfo.organization_id = X_Organization_Id)
123 AND (Recinfo.lot_number = X_Lot_Number)
124 AND ( (Recinfo.expiration_date = X_Expiration_Date)
125 OR ( (Recinfo.expiration_date IS NULL)
126 AND (X_Expiration_Date IS NULL)))
127 AND ( (Recinfo.disable_flag = X_Disable_Flag)
128 OR ( (Recinfo.disable_flag IS NULL)
129 AND (X_Disable_Flag IS NULL)))
130 AND ( (Recinfo.attribute_category = X_Attribute_Category)
131 OR ( (Recinfo.attribute_category IS NULL)
132 AND (X_Attribute_Category IS NULL)))
133 AND ( (Recinfo.attribute1 = X_Attribute1)
134 OR ( (Recinfo.attribute1 IS NULL)
135 AND (X_Attribute1 IS NULL)))
136 AND ( (Recinfo.attribute2 = X_Attribute2)
137 OR ( (Recinfo.attribute2 IS NULL)
138 AND (X_Attribute2 IS NULL)))
139 AND ( (Recinfo.attribute3 = X_Attribute3)
140 OR ( (Recinfo.attribute3 IS NULL)
141 AND (X_Attribute3 IS NULL)))
142 AND ( (Recinfo.attribute4 = X_Attribute4)
143 OR ( (Recinfo.attribute4 IS NULL)
144 AND (X_Attribute4 IS NULL)))
145 AND ( (Recinfo.attribute5 = X_Attribute5)
146 OR ( (Recinfo.attribute5 IS NULL)
147 AND (X_Attribute5 IS NULL)))
148 AND ( (Recinfo.attribute6 = X_Attribute6)
149 OR ( (Recinfo.attribute6 IS NULL)
150 AND (X_Attribute6 IS NULL)))
151 AND ( (Recinfo.attribute7 = X_Attribute7)
152 OR ( (Recinfo.attribute7 IS NULL)
153 AND (X_Attribute7 IS NULL)))
154 AND ( (Recinfo.attribute8 = X_Attribute8)
155 OR ( (Recinfo.attribute8 IS NULL)
156 AND (X_Attribute8 IS NULL)))
157 AND ( (Recinfo.attribute9 = X_Attribute9)
158 OR ( (Recinfo.attribute9 IS NULL)
159 AND (X_Attribute9 IS NULL)))
160 AND ( (Recinfo.attribute10 = X_Attribute10)
161 OR ( (Recinfo.attribute10 IS NULL)
162 AND (X_Attribute10 IS NULL)))
163 AND ( (Recinfo.attribute11 = X_Attribute11)
164 OR ( (Recinfo.attribute11 IS NULL)
165 AND (X_Attribute11 IS NULL)))
166 AND ( (Recinfo.attribute12 = X_Attribute12)
167 OR ( (Recinfo.attribute12 IS NULL)
168 AND (X_Attribute12 IS NULL)))
169 AND ( (Recinfo.attribute13 = X_Attribute13)
170 OR ( (Recinfo.attribute13 IS NULL)
171 AND (X_Attribute13 IS NULL)))
172 AND ( (Recinfo.attribute14 = X_Attribute14)
173 OR ( (Recinfo.attribute14 IS NULL)
174 AND (X_Attribute14 IS NULL)))
175 AND ( (Recinfo.attribute15 = X_Attribute15)
176 OR ( (Recinfo.attribute15 IS NULL)
177 AND (X_Attribute15 IS NULL)))
178 AND ( (Recinfo.status_id = X_status_id)
179 OR ( (Recinfo.status_id IS NULL)
180 AND (X_status_id IS NULL)))
181 AND ( (Recinfo.Description = X_Description)
182 OR ( (Recinfo.description IS NULL)
183 AND (X_Description IS NULL)))
184 AND ( (Recinfo.Vendor_Id = X_Vendor_id)
185 OR ( (Recinfo.vendor_id IS NULL)
186 AND (X_vendor_id IS NULL)))
187 AND ( (Recinfo.Grade_Code = X_Grade_Code)
188 OR ( (Recinfo.Grade_Code IS NULL)
189 AND (X_Grade_Code IS NULL)))
190 AND ( (Recinfo.Origination_date = X_Origination_date)
191 OR ( (Recinfo.Origination_date IS NULL)
192 AND (X_Origination_date IS NULL)))
193 AND ( (Recinfo.Date_Code = X_Date_Code)
194 OR ( (Recinfo.Date_Code IS NULL)
195 AND (X_Date_Code IS NULL)))
196 AND ( (Recinfo.Change_Date = X_Change_Date)
197 OR ( (Recinfo.Change_Date IS NULL)
198 AND (X_Change_Date IS NULL)))
199 AND ( (Recinfo.Age = X_Age)
200 OR ( (Recinfo.Age IS NULL)
201 AND (X_Age IS NULL)))
202 AND ( (Recinfo.Retest_Date = X_Retest_Date)
203 OR ( (Recinfo.Retest_Date IS NULL)
204 AND (X_Retest_Date IS NULL)))
205 AND ( (Recinfo.Maturity_Date = X_Maturity_Date)
206 OR ( (Recinfo.Maturity_Date IS NULL)
207 AND (X_Maturity_Date IS NULL)))
208 AND ( (Recinfo.item_size = X_item_size)
209 OR ( (Recinfo.item_size IS NULL)
210 AND (X_item_size IS NULL)))
211 AND ( (Recinfo.Color = X_Color)
212 OR ( (Recinfo.Color IS NULL)
213 AND (X_Color IS NULL)))
214 AND ( (Recinfo.Volume = X_Volume)
215 OR ( (Recinfo.Volume IS NULL)
216 AND (X_Volume IS NULL)))
217 AND ( (Recinfo.Volume_UOM = X_Volume_UOM)
218 OR ( (Recinfo.Volume_UOM IS NULL)
219 AND (X_Volume_UOM IS NULL)))
220 AND ( (Recinfo.Place_of_origin = X_Place_of_origin)
221 OR ( (Recinfo.Place_of_origin IS NULL)
222 AND (X_Place_of_origin IS NULL)))
223 AND ( (Recinfo.Best_by_Date = X_Best_by_Date)
224 OR ( (Recinfo.Best_by_Date IS NULL)
225 AND (X_Best_by_Date IS NULL)))
226 AND ( (Recinfo.Length = X_Length)
227 OR ( (Recinfo.Length IS NULL)
228 AND (X_Length IS NULL)))
229 AND ( (Recinfo.Length_UOM = X_Length_UOM)
230 OR ( (Recinfo.Length_UOM IS NULL)
231 AND (X_Length_UOM IS NULL)))
232 AND ( (Recinfo.Recycled_content = X_Recycled_content)
233 OR ( (Recinfo.Recycled_content IS NULL)
234 AND (X_Recycled_content IS NULL)))
235 AND ( (Recinfo.Thickness = X_Thickness)
236 OR ( (Recinfo.Thickness IS NULL)
237 AND (X_Thickness IS NULL)))
238 AND ( (Recinfo.Thickness_UOM = X_Thickness_UOM)
239 OR ( (Recinfo.Thickness_UOM IS NULL)
240 AND (X_Thickness_UOM IS NULL)))
241 AND ( (Recinfo.Width = X_Width)
242 OR ( (Recinfo.Width IS NULL)
243 AND (X_Width IS NULL)))
244 AND ( (Recinfo.Width_UOM = X_Width_UOM)
245 OR ( (Recinfo.Width_UOM IS NULL)
246 AND (X_Width_UOM IS NULL)))
247 AND ( (Recinfo.Curl_Wrinkle_Fold = X_Curl_Wrinkle_Fold)
248 OR ( (Recinfo.Curl_Wrinkle_Fold IS NULL)
249 AND (X_Curl_Wrinkle_Fold IS NULL)))
250 AND ( (Recinfo.lot_attribute_category = X_lot_Attribute_Category)
251 OR ( (Recinfo.lot_attribute_category IS NULL)
252 AND (X_lot_Attribute_Category IS NULL)))
253 AND ( (Recinfo.c_attribute1 = X_C_Attribute1)
254 OR ( (Recinfo.c_attribute1 IS NULL)
255 AND (X_C_Attribute1 IS NULL)))
256 AND ( (Recinfo.c_attribute2 = X_C_Attribute2)
257 OR ( (Recinfo.c_attribute2 IS NULL)
258 AND (X_C_Attribute2 IS NULL)))
259 AND ( (Recinfo.c_attribute3 = X_C_Attribute3)
260 OR ( (Recinfo.c_attribute3 IS NULL)
261 AND (X_C_Attribute3 IS NULL)))
262 AND ( (Recinfo.c_attribute4 = X_C_Attribute4)
263 OR ( (Recinfo.c_attribute4 IS NULL)
264 AND (X_C_Attribute4 IS NULL)))
265 AND ( (Recinfo.c_attribute5 = X_C_Attribute5)
266 OR ( (Recinfo.c_attribute5 IS NULL)
267 AND (X_C_Attribute5 IS NULL)))
268 AND ( (Recinfo.c_attribute6 = X_C_Attribute6)
269 OR ( (Recinfo.c_attribute6 IS NULL)
270 AND (X_C_Attribute6 IS NULL)))
271 AND ( (Recinfo.c_attribute7 = X_C_Attribute7)
272 OR ( (Recinfo.c_attribute7 IS NULL)
273 AND (X_C_Attribute7 IS NULL)))
274 AND ( (Recinfo.c_attribute8 = X_C_Attribute8)
275 OR ( (Recinfo.c_attribute8 IS NULL)
276 AND (X_C_Attribute8 IS NULL)))
277 AND ( (Recinfo.c_attribute9 = X_C_Attribute9)
278 OR ( (Recinfo.c_attribute9 IS NULL)
279 AND (X_C_Attribute9 IS NULL)))
280 AND ( (Recinfo.c_attribute10 = X_C_Attribute10)
281 OR ( (Recinfo.c_attribute10 IS NULL)
282 AND (X_C_Attribute10 IS NULL)))
283 AND ( (Recinfo.c_attribute11 = X_C_Attribute11)
284 OR ( (Recinfo.c_attribute11 IS NULL)
285 AND (X_C_Attribute11 IS NULL)))
286 AND ( (Recinfo.c_attribute12 = X_C_Attribute12)
287 OR ( (Recinfo.c_attribute12 IS NULL)
288 AND (X_C_Attribute12 IS NULL)))
289 AND ( (Recinfo.c_attribute13 = X_C_Attribute13)
290 OR ( (Recinfo.c_attribute13 IS NULL)
291 AND (X_C_Attribute13 IS NULL)))
292 AND ( (Recinfo.c_attribute14 = X_C_Attribute14)
293 OR ( (Recinfo.c_attribute14 IS NULL)
294 AND (X_C_Attribute14 IS NULL)))
295 AND ( (Recinfo.c_attribute15 = X_C_Attribute15)
296 OR ( (Recinfo.c_attribute15 IS NULL)
297 AND (X_C_Attribute15 IS NULL)))
298 AND ( (Recinfo.c_attribute16 = X_C_Attribute16)
299 OR ( (Recinfo.c_attribute16 IS NULL)
300 AND (X_C_Attribute16 IS NULL)))
301 AND ( (Recinfo.c_attribute17 = X_C_Attribute17)
302 OR ( (Recinfo.c_attribute17 IS NULL)
303 AND (X_C_Attribute17 IS NULL)))
304 AND ( (Recinfo.c_attribute18 = X_C_Attribute18)
305 OR ( (Recinfo.c_attribute18 IS NULL)
306 AND (X_C_Attribute18 IS NULL)))
307 AND ( (Recinfo.c_attribute19 = X_C_Attribute19)
308 OR ( (Recinfo.c_attribute19 IS NULL)
309 AND (X_C_Attribute19 IS NULL)))
310 AND ( (Recinfo.c_attribute20 = X_C_Attribute20)
311 OR ( (Recinfo.c_attribute20 IS NULL)
312 AND (X_C_Attribute20 IS NULL)))
313 AND ( (Recinfo.d_attribute1 = X_D_Attribute1)
314 OR ( (Recinfo.d_attribute1 IS NULL)
315 AND (X_D_Attribute1 IS NULL)))
316 AND ( (Recinfo.d_attribute2 = X_D_Attribute2)
317 OR ( (Recinfo.d_attribute2 IS NULL)
318 AND (X_D_Attribute2 IS NULL)))
319 AND ( (Recinfo.d_attribute3 = X_D_Attribute3)
320 OR ( (Recinfo.d_attribute3 IS NULL)
321 AND (X_D_Attribute3 IS NULL)))
322 AND ( (Recinfo.d_attribute4 = X_D_Attribute4)
323 OR ( (Recinfo.d_attribute4 IS NULL)
324 AND (X_D_Attribute4 IS NULL)))
325 AND ( (Recinfo.d_attribute5 = X_D_Attribute5)
326 OR ( (Recinfo.d_attribute5 IS NULL)
327 AND (X_D_Attribute5 IS NULL)))
328 AND ( (Recinfo.d_attribute6 = X_D_Attribute6)
329 OR ( (Recinfo.d_attribute6 IS NULL)
330 AND (X_D_Attribute6 IS NULL)))
331 AND ( (Recinfo.d_attribute7 = X_D_Attribute7)
332 OR ( (Recinfo.d_attribute7 IS NULL)
333 AND (X_D_Attribute7 IS NULL)))
334 AND ( (Recinfo.d_attribute8 = X_D_Attribute8)
335 OR ( (Recinfo.d_attribute8 IS NULL)
336 AND (X_D_Attribute8 IS NULL)))
337 AND ( (Recinfo.d_attribute9 = X_D_Attribute9)
338 OR ( (Recinfo.d_attribute9 IS NULL)
339 AND (X_D_Attribute9 IS NULL)))
340 AND ( (Recinfo.d_attribute10 = X_D_Attribute10)
341 OR ( (Recinfo.d_attribute10 IS NULL)
342 AND (X_D_Attribute10 IS NULL))) AND ( (Recinfo.n_attribute1 = X_N_Attribute1)
343 OR ( (Recinfo.n_attribute1 IS NULL)
344 AND (X_N_Attribute1 IS NULL)))
345 AND ( (Recinfo.n_attribute2 = X_N_Attribute2)
346 OR ( (Recinfo.n_attribute2 IS NULL)
347 AND (X_N_Attribute2 IS NULL)))
348 AND ( (Recinfo.n_attribute3 = X_N_Attribute3)
349 OR ( (Recinfo.n_attribute3 IS NULL)
350 AND (X_N_Attribute3 IS NULL)))
351 AND ( (Recinfo.n_attribute4 = X_N_Attribute4)
352 OR ( (Recinfo.n_attribute4 IS NULL)
353 AND (X_N_Attribute4 IS NULL)))
354 AND ( (Recinfo.n_attribute5 = X_N_Attribute5)
355 OR ( (Recinfo.n_attribute5 IS NULL)
356 AND (X_N_Attribute5 IS NULL)))
357 AND ( (Recinfo.n_attribute6 = X_N_Attribute6)
358 OR ( (Recinfo.n_attribute6 IS NULL)
359 AND (X_N_Attribute6 IS NULL)))
360 AND ( (Recinfo.n_attribute7 = X_N_Attribute7)
361 OR ( (Recinfo.n_attribute7 IS NULL)
362 AND (X_N_Attribute7 IS NULL)))
363 AND ( (Recinfo.n_attribute8 = X_N_Attribute8)
364 OR ( (Recinfo.n_attribute8 IS NULL)
365 AND (X_N_Attribute8 IS NULL)))
366 AND ( (Recinfo.n_attribute9 = X_N_Attribute9)
367 OR ( (Recinfo.n_attribute9 IS NULL)
368 AND (X_N_Attribute9 IS NULL)))
369 AND ( (Recinfo.n_attribute10 = X_N_Attribute10)
370 OR ( (Recinfo.n_attribute10 IS NULL)
371 AND (X_N_Attribute10 IS NULL)))
372 AND ( (Recinfo.parent_lot_number = X_Parent_Lot_Number)
373 OR ( (Recinfo.parent_lot_number IS NULL)
374 AND (X_Parent_Lot_Number IS NULL)))
375 AND ( (Recinfo.origination_type = X_Origination_Type)
376 OR ( (Recinfo.origination_type IS NULL)
377 AND (X_Origination_Type IS NULL)))
378 AND ( (Recinfo.expiration_action_date = X_Expiration_Action_Date)
379 OR ( (Recinfo.expiration_action_date IS NULL)
380 AND (X_Expiration_Action_Date IS NULL)))
381 AND ( (Recinfo.expiration_action_code = X_Expiration_Action_Code)
382 OR ( (Recinfo.expiration_action_code IS NULL)
383 AND (X_Expiration_Action_Code IS NULL)))
384 AND ( (Recinfo.hold_date = X_Hold_Date)
385 OR ( (Recinfo.hold_date IS NULL)
386 AND (X_Hold_Date IS NULL)))
387 ) then
388 return;
389 else
390 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
391 APP_EXCEPTION.Raise_Exception;
392 end if;
393 END Lock_Row;
394
395
396
397 /*========================================
398 SFeinstein Convergence
399 Added Sampling_Event_ID to MTL_LOT_NUMBERS table
400 ========================================*/
401 PROCEDURE Update_Row(X_Rowid VARCHAR2,
402 X_Inventory_Item_Id NUMBER,
403 X_Organization_Id NUMBER,
404 X_Lot_Number VARCHAR2,
405 X_Last_Update_Date DATE,
406 X_Last_Updated_By NUMBER,
407 X_Last_Update_Login NUMBER,
408 X_Expiration_Date DATE,
409 X_Disable_Flag NUMBER,
410 X_Attribute_Category VARCHAR2,
411 X_Attribute1 VARCHAR2,
412 X_Attribute2 VARCHAR2,
413 X_Attribute3 VARCHAR2,
414 X_Attribute4 VARCHAR2,
415 X_Attribute5 VARCHAR2,
416 X_Attribute6 VARCHAR2,
417 X_Attribute7 VARCHAR2,
418 X_Attribute8 VARCHAR2,
419 X_Attribute9 VARCHAR2,
420 X_Attribute10 VARCHAR2,
421 X_Attribute11 VARCHAR2,
422 X_Attribute12 VARCHAR2,
423 X_Attribute13 VARCHAR2,
424 X_Attribute14 VARCHAR2,
425 X_Attribute15 VARCHAR2,
426 X_Status_ID NUMBER,
427 X_Description VARCHAR2,
428 X_Vendor_Id NUMBER,
429 X_Grade_Code VARCHAR2,
430 X_Origination_Date DATE,
431 X_Date_Code VARCHAR2,
432 X_Change_Date DATE,
433 X_Age NUMBER,
434 X_Retest_Date DATE,
435 X_Maturity_Date DATE,
436 X_Lot_Attribute_Category VARCHAR2,
437 X_Item_Size NUMBER,
438 X_Color VARCHAR2,
439 X_Volume NUMBER,
440 X_Volume_UOM VARCHAR2,
441 X_Place_of_Origin VARCHAR2,
442 X_Best_by_Date DATE,
443 X_Length NUMBER,
444 X_Length_UOM VARCHAR2,
445 X_Recycled_Content NUMBER,
446 X_Thickness NUMBER,
447 X_Thickness_UOM VARCHAR2,
448 X_Width NUMBER,
449 X_Width_UOM VARCHAR2,
450 X_Curl_Wrinkle_Fold VARCHAR2,
451 X_C_Attribute1 VARCHAR2,
452 X_C_Attribute2 VARCHAR2,
453 X_C_Attribute3 VARCHAR2,
454 X_C_Attribute4 VARCHAR2,
455 X_C_Attribute5 VARCHAR2,
456 X_C_Attribute6 VARCHAR2,
457 X_C_Attribute7 VARCHAR2,
458 X_C_Attribute8 VARCHAR2,
459 X_C_Attribute9 VARCHAR2,
460 X_C_Attribute10 VARCHAR2,
461 X_C_Attribute11 VARCHAR2,
462 X_C_Attribute12 VARCHAR2,
463 X_C_Attribute13 VARCHAR2,
464 X_C_Attribute14 VARCHAR2,
465 X_C_Attribute15 VARCHAR2,
466 X_C_Attribute16 VARCHAR2,
467 X_C_Attribute17 VARCHAR2,
468 X_C_Attribute18 VARCHAR2,
469 X_C_Attribute19 VARCHAR2,
470 X_C_Attribute20 VARCHAR2,
471 X_D_Attribute1 DATE,
472 X_D_Attribute2 DATE,
473 X_D_Attribute3 DATE,
474 X_D_Attribute4 DATE,
475 X_D_Attribute5 DATE,
476 X_D_Attribute6 DATE,
477 X_D_Attribute7 DATE,
478 X_D_Attribute8 DATE,
479 X_D_Attribute9 DATE,
480 X_D_Attribute10 DATE,
481 X_N_Attribute1 NUMBER,
482 X_N_Attribute2 NUMBER,
483 X_N_Attribute3 NUMBER,
484 X_N_Attribute4 NUMBER,
485 X_N_Attribute5 NUMBER,
486 X_N_Attribute6 NUMBER,
487 X_N_Attribute7 NUMBER,
488 X_N_Attribute8 NUMBER,
489 X_N_Attribute10 NUMBER,
490 X_Supplier_Lot_Number VARCHAR2,
491 X_N_Attribute9 NUMBER,
492 X_Territory_Code VARCHAR2,
493 X_Parent_Lot_Number VARCHAR2,
494 X_Origination_Type NUMBER,
495 X_Expiration_Action_Date DATE,
496 X_Expiration_Action_Code VARCHAR2,
497 X_Hold_Date DATE,
498 X_Sampling_Event_ID NUMBER DEFAULT NULL
499
500 ) IS
501 l_status_id NUMBER;
502 /*========================================
503 Joe DiIorio - Convergence
504 Added l_grade_code and variables
505 to support call to update grade history.
506 ========================================*/
507 l_grade_code MTL_LOT_NUMBERS.GRADE_CODE%TYPE;
508 l_status VARCHAR2(2);
509 l_message VARCHAR2(2000);
510 /* Jalaj Srivastava Bug 4998256
511 added varaibles below for call to quantity tree */
512
513 l_qoh number;
514 l_rqoh number;
515 l_qr number;
516 l_qs number;
517 l_att number;
518 l_atr number;
519 l_sqoh number;
520 l_srqoh number;
521 l_sqr number;
522 l_sqs number;
523 l_satt number;
524 l_satr number;
525 l_return_status varchar2(1);
526 l_msg_count pls_integer;
527 l_msg_data varchar2(4000);
528
529 l_global_profile VARCHAR2(1) := 'N'; --Global Lot ER
530 l_orgid NUMBER; --Global Lot ER
531
532 --Global Lot ER Start
533 CURSOR get_all_orgid(p_item_id IN NUMBER, p_lot_number IN VARCHAR2) IS
534 SELECT organization_id
535 FROM mtl_lot_numbers
536 WHERE inventory_item_id = p_item_id
537 AND lot_number = p_lot_number;
538 --Global Lot ER End
539
540
541 BEGIN
542 l_global_profile := NVL(FND_PROFILE.VALUE('INV_GLOBAL_LOT_ATTRIBUTES'),'N');--Global Lot ER
543
544 /* WMS Material Status Enhancements
545 To maintain the Material Status History obtain the Status ID before
546 the record get updated with modified values. */
547
548 SELECT status_id, grade_code
549 INTO l_status_id, l_grade_code
550 FROM mtl_lot_numbers
551 WHERE rowid = X_Rowid;
552
553 /*===================================
554 Joe DiIorio - Convergence
555 Get existing grade code to compare
556 later if it has changed.
557 ===================================
558 S Feinstein - removed new select code
559 and incorporated it in select above
560 ===================================
561 SELECT grade_code
562 INTO l_grade_code
563 FROM mtl_lot_numbers
564 WHERE rowid = X_Rowid;
565 ===================================*/
566
567
568 /*========================================
569 SFeinstein Convergence
570 Added Sampling_Event_ID to columns updated
571 ========================================*/
572 UPDATE mtl_lot_numbers
573 SET
574 inventory_item_id = X_Inventory_Item_Id,
575 organization_id = X_Organization_Id,
576 lot_number = X_Lot_Number,
577 last_update_date = X_Last_Update_Date,
578 last_updated_by = X_Last_Updated_By,
579 last_update_login = X_Last_Update_Login,
580 expiration_date = X_Expiration_Date,
581 disable_flag = X_Disable_Flag,
582 attribute_category = X_Attribute_Category,
583 attribute1 = X_Attribute1,
584 attribute2 = X_Attribute2,
585 attribute3 = X_Attribute3,
586 attribute4 = X_Attribute4,
587 attribute5 = X_Attribute5,
588 attribute6 = X_Attribute6,
589 attribute7 = X_Attribute7,
590 attribute8 = X_Attribute8,
591 attribute9 = X_Attribute9,
592 attribute10 = X_Attribute10,
593 attribute11 = X_Attribute11,
594 attribute12 = X_Attribute12,
595 attribute13 = X_Attribute13,
596 attribute14 = X_Attribute14,
597 attribute15 = X_Attribute15,
598 status_id = X_Status_ID,
599 description = X_Description,
600 vendor_id = X_Vendor_Id,
601 grade_code = X_Grade_Code,
602 origination_date = X_Origination_Date,
603 date_code = X_Date_Code,
604 change_date = X_Change_date,
605 age = X_Age,
606 retest_date = X_Retest_Date,
607 maturity_date = X_Maturity_Date,
608 lot_attribute_category = X_Lot_Attribute_Category,
609 item_size = X_Item_Size,
610 color = X_Color,
611 volume = X_volume,
612 volume_uom = X_Volume_UOM,
613 place_of_origin = X_Place_Of_Origin,
614 best_by_date = X_Best_BY_Date,
615 length = X_Length,
616 length_uom = X_Length_UOM,
617 recycled_content = X_Recycled_Content,
618 thickness = X_Thickness,
619 thickness_uom = X_Thickness_UOM,
620 width = X_Width,
621 width_uom = X_Width_UOM,
622 curl_wrinkle_fold = X_Curl_Wrinkle_Fold,
623 c_attribute1 = X_C_Attribute1,
624 c_attribute2 = X_C_Attribute2,
625 c_attribute3 = X_C_Attribute3,
626 c_attribute4 = X_C_Attribute4,
627 c_attribute5 = X_C_Attribute5,
628 c_attribute6 = X_C_Attribute6,
629 c_attribute7 = X_C_Attribute7,
630 c_attribute8 = X_C_Attribute8,
631 c_attribute9 = X_C_Attribute9,
632 c_attribute10 = X_C_Attribute10,
633 c_attribute11 = X_C_Attribute11,
634 c_attribute12 = X_C_Attribute12,
635 c_attribute13 = X_C_Attribute13,
636 c_attribute14 = X_C_Attribute14,
637 c_attribute15 = X_C_Attribute15,
638 c_attribute16 = X_C_Attribute16,
639 c_attribute17 = X_C_Attribute17,
640 c_attribute18 = X_C_Attribute18,
641 c_attribute19 = X_C_Attribute19,
642 c_attribute20 = X_C_Attribute20,
643 d_attribute1 = X_D_Attribute1,
644 d_attribute2 = X_D_Attribute2,
645 d_attribute3 = X_D_Attribute3,
646 d_attribute4 = X_D_Attribute4,
647 d_attribute5 = X_D_Attribute5,
648 d_attribute6 = X_D_Attribute6,
649 d_attribute7 = X_D_Attribute7,
650 d_attribute8 = X_D_Attribute8,
651 d_attribute9 = X_D_Attribute9,
652 d_attribute10 = X_D_Attribute10,
653 n_attribute1 = X_N_Attribute1,
654 n_attribute2 = X_N_Attribute2,
655 n_attribute3 = X_N_Attribute3,
656 n_attribute4 = X_N_Attribute4,
657 n_attribute5 = X_N_Attribute5,
658 n_attribute6 = X_N_Attribute6,
659 n_attribute7 = X_N_Attribute7,
660 n_attribute8 = X_N_Attribute8,
661 n_attribute10 = X_N_Attribute10,
662 supplier_lot_number = X_Supplier_Lot_Number,
663 n_attribute9 = X_N_Attribute9,
664 territory_code = X_Territory_code,
665 parent_lot_number = X_Parent_Lot_Number,
666 origination_type = X_Origination_Type,
667 expiration_action_date = X_Expiration_Action_Date,
668 expiration_action_code = X_Expiration_Action_Code,
669 hold_date = X_Hold_Date,
670 Sampling_Event_ID = X_Sampling_Event_ID
671 WHERE rowid = X_Rowid;
672
673 --Global Lot ER Start
674 IF l_global_profile = 'Y' THEN
675 UPDATE mtl_lot_numbers
676 SET
677 expiration_date = X_Expiration_Date,
678 origination_date = X_Origination_Date,
679 maturity_date = X_Maturity_Date,
680 retest_date = X_Retest_Date,
681 expiration_action_date = X_Expiration_Action_Date,
682 expiration_action_code = X_Expiration_Action_Code,
683 hold_date = X_Hold_Date,
684 grade_code = X_Grade_Code
685 WHERE inventory_item_id = X_Inventory_Item_Id
686 AND lot_number = X_Lot_Number;
687 END IF;
688 --Global Lot ER End
689
690 /* WMS Enhancements
691 This Procedure Caters to the insertion of records in the
692 table MTL_MATERIAL_STATUS_HISTORY. */
693
694 --BUG 7258237 For updating status wms install is not required
695 IF --(INV_INSTALL.ADV_INV_INSTALLED(P_Organization_ID => NULL)) AND
696 (X_Status_ID IS NOT NULL) AND
697 (X_Status_ID <> l_status_id) THEN
698 MTL_SECONDARY_INVENTORIES_PKG.Status_History
699 ( X_Organization_ID,
700 X_Inventory_Item_ID,
701 X_Lot_Number,
702 NULL,
703 2,
704 X_Status_ID,
705 NULL,
706 NULL,
707 X_Last_Update_Date,
708 X_Last_Updated_By,
709 X_Last_Updated_By,
710 X_Last_Update_Date,
711 X_Last_Update_Login);
712 END IF;
713
714
715 /*===================================
716 Joe DiIorio - Convergence
717 Added l_grade_code
718 ===================================*/
719 IF (INV_INSTALL.ADV_INV_INSTALLED(P_Organization_ID => NULL)) AND
720 (X_Grade_Code IS NOT NULL) AND
721 (X_Grade_Code <> l_grade_code) THEN
722 /* Jalaj Srivastava Bug 4998256
723 Get primary and seconday onhand */
724
725 --Global Lot ER Start
726 IF l_global_profile = 'Y' THEN
727
728 OPEN get_all_orgid(X_inventory_item_id,X_lot_number);
729 LOOP
730 FETCH get_all_orgid INTO l_orgid;
731 EXIT WHEN get_all_orgid%NOTFOUND;
732
733 inv_quantity_tree_pub.query_quantities
734 (p_api_version_number => 1.0
735 ,x_return_status => l_return_status
736 ,x_msg_count => l_msg_count
737 ,x_msg_data => l_msg_data
738 ,p_organization_id => l_orgid --X_organization_id --Global Lot ER
739 ,p_inventory_item_id => X_inventory_item_id
740 ,p_tree_mode => inv_quantity_tree_pub.g_transaction_mode
741 ,p_is_revision_control => false
742 ,p_is_lot_control => true
743 ,p_is_serial_control => false
744 ,p_grade_code => null
745 ,p_revision => null
746 ,p_lot_number => X_lot_number
747 ,p_subinventory_code => null
748 ,p_locator_id => null
749 ,x_qoh => l_qoh
750 ,x_rqoh => l_rqoh
751 ,x_qr => l_qr
752 ,x_qs => l_qs
753 ,x_att => l_att
754 ,x_atr => l_atr
755 ,x_sqoh => l_sqoh
756 ,x_srqoh => l_srqoh
757 ,x_sqr => l_sqr
758 ,x_sqs => l_sqs
759 ,x_satt => l_satt
760 ,x_satr => l_satr
761 );
762
763
764 /*========================
765 Upgrade Grade History
766 ========================*/
767 INSERT INTO MTL_LOT_GRADE_HISTORY
768 (
769 GRADE_UPDATE_ID,
770 INVENTORY_ITEM_ID,
771 ORGANIZATION_ID,
772 LOT_NUMBER,
773 UPDATE_METHOD,
774 NEW_GRADE_CODE,
775 OLD_GRADE_CODE,
776 PRIMARY_QUANTITY,
777 SECONDARY_QUANTITY,
778 UPDATE_REASON_ID,
779 INITIAL_GRADE_FLAG,
780 FROM_MOBILE_APPS_FLAG,
781 GRADE_UPDATE_DATE,
782 ATTRIBUTE1,
783 ATTRIBUTE2,
784 ATTRIBUTE3,
785 ATTRIBUTE4,
786 ATTRIBUTE5,
787 ATTRIBUTE6,
788 ATTRIBUTE7,
789 ATTRIBUTE8,
790 ATTRIBUTE9,
791 ATTRIBUTE10,
792 ATTRIBUTE11,
793 ATTRIBUTE12,
794 ATTRIBUTE13,
795 ATTRIBUTE14,
796 ATTRIBUTE15,
797 ATTRIBUTE_CATEGORY,
798 CREATION_DATE,
799 CREATED_BY,
800 LAST_UPDATED_BY,
801 LAST_UPDATE_DATE,
802 LAST_UPDATE_LOGIN
803 )
804 VALUES
805 (
806 MTL_LOT_GRADE_HISTORY_S.NEXTVAL,
807 X_INVENTORY_ITEM_ID,
808 l_orgid,--X_ORGANIZATION_ID, --Global Lot ER
809 X_LOT_NUMBER,
810 INV_MATERIAL_STATUS_PUB.g_update_method_manual,/* Jalaj Srivastava Bug 4998256 pass update_method as manual instead of null */
811 X_GRADE_CODE,
812 l_grade_code,
813 l_qoh, /* Jalaj Srivastava Bug 4998256 pass primary onhand */
814 l_sqoh, /* Jalaj Srivastava Bug 4998256 pass secondary onhand */
815 NULL,
816 'N',
817 'N',
818 SYSDATE,
819 NULL,
820 NULL,
821 NULL,
822 NULL,
823 NULL,
824 NULL,
825 NULL,
826 NULL,
827 NULL,
828 NULL,
829 NULL,
830 NULL,
831 NULL,
832 NULL,
833 NULL,
834 NULL,
835 SYSDATE,
836 FND_GLOBAL.USER_ID,
837 FND_GLOBAL.USER_ID,
838 SYSDATE,
839 FND_GLOBAL.LOGIN_ID
840 );
841
842
843
844 END LOOP;
845
846 IF get_all_orgid%ISOPEN THEN
847 CLOSE get_all_orgid;
848 END IF;
849 --Global Lot ER END
850
851 ELSE --l_global_profile = 'N' --Global Lot ER,
852 inv_quantity_tree_pub.query_quantities
853 (p_api_version_number => 1.0
854 ,x_return_status => l_return_status
855 ,x_msg_count => l_msg_count
856 ,x_msg_data => l_msg_data
857 ,p_organization_id => X_organization_id
858 ,p_inventory_item_id => X_inventory_item_id
859 ,p_tree_mode => inv_quantity_tree_pub.g_transaction_mode
860 ,p_is_revision_control => false
861 ,p_is_lot_control => true
862 ,p_is_serial_control => false
863 ,p_grade_code => null
864 ,p_revision => null
865 ,p_lot_number => X_lot_number
866 ,p_subinventory_code => null
867 ,p_locator_id => null
868 ,x_qoh => l_qoh
869 ,x_rqoh => l_rqoh
870 ,x_qr => l_qr
871 ,x_qs => l_qs
872 ,x_att => l_att
873 ,x_atr => l_atr
874 ,x_sqoh => l_sqoh
875 ,x_srqoh => l_srqoh
876 ,x_sqr => l_sqr
877 ,x_sqs => l_sqs
878 ,x_satt => l_satt
879 ,x_satr => l_satr
880 );
881
882
883 /*========================
884 Upgrade Grade History
885 ========================*/
886 INSERT INTO MTL_LOT_GRADE_HISTORY
887 (
888 GRADE_UPDATE_ID,
889 INVENTORY_ITEM_ID,
890 ORGANIZATION_ID,
891 LOT_NUMBER,
892 UPDATE_METHOD,
893 NEW_GRADE_CODE,
894 OLD_GRADE_CODE,
895 PRIMARY_QUANTITY,
896 SECONDARY_QUANTITY,
897 UPDATE_REASON_ID,
898 INITIAL_GRADE_FLAG,
899 FROM_MOBILE_APPS_FLAG,
900 GRADE_UPDATE_DATE,
901 ATTRIBUTE1,
902 ATTRIBUTE2,
903 ATTRIBUTE3,
904 ATTRIBUTE4,
905 ATTRIBUTE5,
906 ATTRIBUTE6,
907 ATTRIBUTE7,
908 ATTRIBUTE8,
909 ATTRIBUTE9,
910 ATTRIBUTE10,
911 ATTRIBUTE11,
912 ATTRIBUTE12,
913 ATTRIBUTE13,
914 ATTRIBUTE14,
915 ATTRIBUTE15,
916 ATTRIBUTE_CATEGORY,
917 CREATION_DATE,
918 CREATED_BY,
919 LAST_UPDATED_BY,
920 LAST_UPDATE_DATE,
921 LAST_UPDATE_LOGIN
922 )
923 VALUES
924 (
925 MTL_LOT_GRADE_HISTORY_S.NEXTVAL,
926 X_INVENTORY_ITEM_ID,
927 X_ORGANIZATION_ID,
928 X_LOT_NUMBER,
929 INV_MATERIAL_STATUS_PUB.g_update_method_manual,/* Jalaj Srivastava Bug 4998256 pass update_method as manual instead of null */
930 X_GRADE_CODE,
931 l_grade_code,
932 l_qoh, /* Jalaj Srivastava Bug 4998256 pass primary onhand */
933 l_sqoh, /* Jalaj Srivastava Bug 4998256 pass secondary onhand */
934 NULL,
935 'N',
936 'N',
937 SYSDATE,
938 NULL,
939 NULL,
940 NULL,
941 NULL,
942 NULL,
943 NULL,
944 NULL,
945 NULL,
946 NULL,
947 NULL,
948 NULL,
949 NULL,
950 NULL,
951 NULL,
952 NULL,
953 NULL,
954 SYSDATE,
955 FND_GLOBAL.USER_ID,
956 FND_GLOBAL.USER_ID,
957 SYSDATE,
958 FND_GLOBAL.LOGIN_ID
959 );
960
961 END IF;
962
963 END IF;
964
965 END Update_Row;
966
967
968
969
970
971
972 PROCEDURE Insert_Row(X_Inventory_Item_Id NUMBER,
973 X_Organization_Id NUMBER,
974 X_Lot_Number VARCHAR2,
975 X_Creation_Date DATE,
976 X_Created_By NUMBER,
977 X_Last_Update_Date DATE,
978 X_Last_Updated_By NUMBER,
979 X_Last_Update_Login NUMBER,
980 X_Request_Id NUMBER,
981 X_Program_Application_Id NUMBER,
982 X_Program_Id NUMBER,
983 X_Program_Update_Date DATE,
984 X_Expiration_Date DATE,
985 X_Disable_Flag NUMBER,
986 X_Attribute_Category VARCHAR2,
987 X_Attribute1 VARCHAR2,
988 X_Attribute2 VARCHAR2,
989 X_Attribute3 VARCHAR2,
990 X_Attribute4 VARCHAR2,
991 X_Attribute5 VARCHAR2,
992 X_Attribute6 VARCHAR2,
993 X_Attribute7 VARCHAR2,
994 X_Attribute8 VARCHAR2,
995 X_Attribute9 VARCHAR2,
996 X_Attribute10 VARCHAR2,
997 X_Attribute11 VARCHAR2,
998 X_Attribute12 VARCHAR2,
999 X_Attribute13 VARCHAR2,
1000 X_Attribute14 VARCHAR2,
1001 X_Attribute15 VARCHAR2,
1002 X_Status_ID NUMBER,
1003 X_Description VARCHAR2,
1004 X_Vendor_Id NUMBER,
1005 X_Grade_Code VARCHAR2,
1006 X_Origination_Date DATE,
1007 X_Date_Code VARCHAR2,
1008 X_Change_Date DATE,
1009 X_Age NUMBER,
1010 X_Retest_Date DATE,
1011 X_Maturity_Date DATE,
1012 X_Lot_Attribute_Category VARCHAR2,
1013 X_Item_Size NUMBER,
1014 X_Color VARCHAR2,
1015 X_Volume NUMBER,
1016 X_Volume_UOM VARCHAR2,
1017 X_Place_of_Origin VARCHAR2,
1018 X_Best_by_Date DATE,
1019 X_Length NUMBER,
1020 X_Length_UOM VARCHAR2,
1021 X_Recycled_Content NUMBER,
1022 X_Thickness NUMBER,
1023 X_Thickness_UOM VARCHAR2,
1024 X_Width NUMBER,
1025 X_Width_UOM VARCHAR2,
1026 X_Curl_Wrinkle_Fold VARCHAR2,
1027 X_C_Attribute1 VARCHAR2,
1028 X_C_Attribute2 VARCHAR2,
1029 X_C_Attribute3 VARCHAR2,
1030 X_C_Attribute4 VARCHAR2,
1031 X_C_Attribute5 VARCHAR2,
1032 X_C_Attribute6 VARCHAR2,
1033 X_C_Attribute7 VARCHAR2,
1034 X_C_Attribute8 VARCHAR2,
1035 X_C_Attribute9 VARCHAR2,
1036 X_C_Attribute10 VARCHAR2,
1037 X_C_Attribute11 VARCHAR2,
1038 X_C_Attribute12 VARCHAR2,
1039 X_C_Attribute13 VARCHAR2,
1040 X_C_Attribute14 VARCHAR2,
1041 X_C_Attribute15 VARCHAR2,
1042 X_C_Attribute16 VARCHAR2,
1043 X_C_Attribute17 VARCHAR2,
1044 X_C_Attribute18 VARCHAR2,
1045 X_C_Attribute19 VARCHAR2,
1046 X_C_Attribute20 VARCHAR2,
1047 X_D_Attribute1 DATE,
1048 X_D_Attribute2 DATE,
1049 X_D_Attribute3 DATE,
1050 X_D_Attribute4 DATE,
1051 X_D_Attribute5 DATE,
1052 X_D_Attribute6 DATE,
1053 X_D_Attribute7 DATE,
1054 X_D_Attribute8 DATE,
1055 X_D_Attribute9 DATE,
1056 X_D_Attribute10 DATE,
1057 X_N_Attribute1 NUMBER,
1058 X_N_Attribute2 NUMBER,
1059 X_N_Attribute3 NUMBER,
1060 X_N_Attribute4 NUMBER,
1061 X_N_Attribute5 NUMBER,
1062 X_N_Attribute6 NUMBER,
1063 X_N_Attribute7 NUMBER,
1064 X_N_Attribute8 NUMBER,
1065 X_N_Attribute9 NUMBER,
1066 X_N_Attribute10 NUMBER,
1067 X_Supplier_Lot_Number VARCHAR2,
1068 X_Territory_Code VARCHAR2,
1069 X_Parent_Lot_Number VARCHAR2,
1070 X_Origination_Type NUMBER,
1071 X_Expiration_Action_Date DATE,
1072 X_Expiration_Action_Code VARCHAR2,
1073 X_Hold_Date DATE ,
1074 X_SAMPLING_EVENT_ID NUMBER DEFAULT NULL
1075 )
1076 IS
1077 l_global_profile VARCHAR2(1) := 'N'; --Global Lot ER
1078 l_Expiration_Date DATE;--Global Lot ER
1079 l_Origination_Date DATE;--Global Lot ER
1080 l_Maturity_Date DATE;--Global Lot ER
1081 l_Retest_Date DATE;--Global Lot ER
1082 l_Expiration_Action_Date DATE;--Global Lot ER
1083 l_Expiration_Action_Code VARCHAR2(30);--Global Lot ER
1084 l_Hold_Date DATE;--Global Lot ER
1085 l_Grade_Code VARCHAR2(30);--Global Lot ER
1086 l_tmp NUMBER := 0;--Global Lot ER
1087 l_default_status NUMBER; -- Bug 14399953
1088
1089 BEGIN
1090 --Global Lot ER Start
1091 l_global_profile := NVL(FND_PROFILE.VALUE('INV_GLOBAL_LOT_ATTRIBUTES'),'N');--Global Lot ER
1092
1093 IF l_global_profile = 'Y' THEN
1094 BEGIN
1095 SELECT 1,Expiration_Date,origination_date,maturity_date,retest_date,expiration_action_date,expiration_action_code,hold_date,grade_code
1096 INTO l_tmp,l_Expiration_Date,l_Origination_Date,l_Maturity_Date,l_Retest_Date,l_Expiration_Action_Date,l_Expiration_Action_Code,l_Hold_Date,l_Grade_Code
1097 FROM mtl_lot_numbers
1098 WHERE organization_id <> X_Organization_Id
1099 AND inventory_item_id = X_Inventory_Item_Id
1100 AND lot_number = X_Lot_Number
1101 AND ROWNUM = 1;
1102
1103 EXCEPTION
1104 WHEN NO_DATA_FOUND THEN
1105 l_tmp := 0;
1106 END;
1107 END IF;
1108
1109 --If No same lot exist in other org
1110 IF Nvl(l_tmp,0) = 0 THEN
1111 l_Expiration_Date := X_Expiration_Date;
1112 l_Origination_Date:= X_Origination_Date;
1113 l_Maturity_Date := X_Maturity_Date;
1114 l_Retest_Date := X_Retest_Date;
1115 l_Expiration_Action_Date := X_Expiration_Action_Date;
1116 l_Expiration_Action_Code := X_Expiration_Action_Code;
1117 l_Hold_Date := X_Hold_Date;
1118 l_Grade_Code := X_Grade_Code;
1119 END IF;
1120 --Global Lot ER END
1121
1122 INSERT INTO MTL_LOT_NUMBERS (
1123 Inventory_Item_Id,
1124 Organization_Id,
1125 Lot_Number,
1126 Creation_Date,
1127 Created_By,
1128 Last_Update_Date,
1129 Last_Updated_By,
1130 Last_Update_Login,
1131 Request_Id,
1132 Program_Application_Id,
1133 Program_Id,
1134 Program_Update_Date,
1135 Expiration_Date,
1136 Disable_Flag,
1137 Attribute_Category,
1138 Attribute1, Attribute2, Attribute3, Attribute4,
1139 Attribute5, Attribute6, Attribute7, Attribute8,
1140 Attribute9, Attribute10, Attribute11, Attribute12,
1141 Attribute13, Attribute14, Attribute15,
1142 Status_ID,
1143 Description,
1144 Vendor_Id,
1145 Grade_Code,
1146 Origination_Date,
1147 Date_Code,
1148 Change_Date,
1149 Age,
1150 Retest_Date,
1151 Maturity_Date,
1152 Lot_Attribute_Category,
1153 Item_Size,
1154 Color,
1155 Volume,
1156 Volume_UOM,
1157 Place_of_Origin,
1158 Best_by_Date,
1159 Length,
1160 Length_UOM,
1161 Recycled_Content,
1162 Thickness,
1163 Thickness_UOM,
1164 Width,
1165 Width_UOM,
1166 Curl_Wrinkle_Fold,
1167 C_Attribute1, C_Attribute2, C_Attribute3, C_Attribute4,
1168 C_Attribute5, C_Attribute6, C_Attribute7, C_Attribute8,
1169 C_Attribute9, C_Attribute10, C_Attribute11, C_Attribute12,
1170 C_Attribute13, C_Attribute14, C_Attribute15, C_Attribute16,
1171 C_Attribute17, C_Attribute18, C_Attribute19, C_Attribute20,
1172 D_Attribute1, D_Attribute2, D_Attribute3, D_Attribute4,
1173 D_Attribute5, D_Attribute6, D_Attribute7, D_Attribute8,
1174 D_Attribute9, D_Attribute10,
1175 N_Attribute1, N_Attribute2, N_Attribute3, N_Attribute4,
1176 N_Attribute5, N_Attribute6, N_Attribute7, N_Attribute8,
1177 N_Attribute9, N_Attribute10,
1178 Supplier_Lot_Number,
1179 Territory_Code,
1180 Parent_Lot_Number,
1181 Origination_Type,
1182 Expiration_Action_Date,
1183 Expiration_Action_Code,
1184 Hold_Date
1185 ,gen_object_id -- NSRIVAST, INVCONV
1186 ,sampling_event_id -- Bug 4115021 OPM Inventory Convergence
1187 )
1188 VALUES
1189 (X_Inventory_Item_Id,
1190 X_Organization_Id,
1191 X_Lot_Number,
1192 X_Creation_Date,
1193 X_Created_By,
1194 X_Last_Update_Date,
1195 X_Last_Updated_By,
1196 X_Last_Update_Login,
1197 X_Request_Id,
1198 X_Program_Application_Id,
1199 X_Program_Id,
1200 X_Program_Update_Date,
1201 l_Expiration_Date,--Global Lot ER
1202 X_Disable_Flag,
1203 X_Attribute_Category,
1204 X_Attribute1, X_Attribute2, X_Attribute3, X_Attribute4,
1205 X_Attribute5, X_Attribute6, X_Attribute7, X_Attribute8,
1206 X_Attribute9, X_Attribute10, X_Attribute11, X_Attribute12,
1207 X_Attribute13, X_Attribute14, X_Attribute15,
1208 X_Status_ID,
1209 X_Description,
1210 X_Vendor_Id,
1211 l_Grade_Code,--Global Lot ER
1212 l_Origination_Date,--Global Lot ER
1213 X_Date_Code,
1214 X_Change_Date,
1215 X_Age,
1216 l_Retest_Date,--Global Lot ER
1217 l_Maturity_Date,--Global Lot ER
1218 X_Lot_Attribute_Category,
1219 X_Item_Size,
1220 X_Color,
1221 X_Volume,
1222 X_Volume_UOM,
1223 X_Place_of_Origin,
1224 X_Best_by_Date,
1225 X_Length,
1226 X_Length_UOM,
1227 X_Recycled_Content,
1228 X_Thickness,
1229 X_Thickness_UOM,
1230 X_Width,
1231 X_Width_UOM,
1232 X_Curl_Wrinkle_Fold,
1233 X_C_Attribute1, X_C_Attribute2, X_C_Attribute3, X_C_Attribute4,
1234 X_C_Attribute5, X_C_Attribute6, X_C_Attribute7, X_C_Attribute8,
1235 X_C_Attribute9, X_C_Attribute10, X_C_Attribute11, X_C_Attribute12,
1236 X_C_Attribute13, X_C_Attribute14, X_C_Attribute15, X_C_Attribute16,
1237 X_C_Attribute17, X_C_Attribute18, X_C_Attribute19, X_C_Attribute20,
1238 X_D_Attribute1, X_D_Attribute2, X_D_Attribute3, X_D_Attribute4,
1239 X_D_Attribute5, X_D_Attribute6, X_D_Attribute7, X_D_Attribute8,
1240 X_D_Attribute9, X_D_Attribute10,
1241 X_N_Attribute1, X_N_Attribute2, X_N_Attribute3, X_N_Attribute4,
1242 X_N_Attribute5, X_N_Attribute6, X_N_Attribute7, X_N_Attribute8,
1243 X_N_Attribute9, X_N_Attribute10,
1244 X_Supplier_Lot_Number,
1245 X_Territory_Code,
1246 X_Parent_Lot_Number,
1247 X_Origination_Type,
1248 l_Expiration_Action_Date,--Global Lot ER
1249 l_Expiration_Action_Code,--Global Lot ER
1250 l_Hold_Date,--Global Lot ER
1251 mtl_gen_object_id_s.NEXTVAL -- NSRIVAST, INVCONV
1252 ,x_sampling_event_id
1253 );
1254
1255
1256
1257 /* WMS Enhancements
1258 This Procedure Caters to the insertion of records in the
1259 table MTL_MATERIAL_STATUS_HISTORY. */
1260 -- Bug 7502482 WMS installation is not required For Inserting Lot History.
1261 IF --(INV_INSTALL.ADV_INV_INSTALLED(P_Organization_ID => NULL)) AND
1262 (X_Status_ID IS NOT NULL) THEN
1263
1264 /*=================================================
1265 BUG#4222397 = Changed to pass Y for value of
1266 initial_status_flag and named the parameters.
1267 =================================================*/
1268 --Bug 14399953 For checking if onhand status is enabled for org.
1269 SELECT default_status_id INTO l_default_status
1270 FROM mtl_parameters
1271 WHERE ORGANIZATION_ID = X_Organization_ID;
1272
1273 IF l_default_status IS NULL THEN
1274 MTL_SECONDARY_INVENTORIES_PKG.Status_History
1275 (x_organization_id => X_Organization_ID
1276 , x_inventory_item_id => X_Inventory_Item_ID
1277 , x_lot_number => X_Lot_Number
1278 , x_serial_number => NULL
1279 , x_update_method => 2
1280 , x_status_id => X_Status_ID
1281 , x_zone_code => NULL
1282 , x_locator_id => NULL
1283 , x_creation_date => X_Last_Update_Date
1284 , x_created_by => X_Last_Updated_By
1285 , x_last_updated_by => X_Last_Updated_By
1286 , x_last_update_date => X_Last_Update_Date
1287 , x_last_update_login => X_Last_Update_Login
1288 , x_initial_status_flag => 'Y');
1289 END IF;
1290
1291 END IF;
1292
1293 END Insert_Row;
1294
1295 END MTL_LOT_NUMBERS_PKG;