[Home] [Help]
PACKAGE BODY: APPS.OE_LOT_SERIAL_UTIL
Source
1 PACKAGE BODY OE_Lot_Serial_Util AS
2 /* $Header: OEXUSRLB.pls 120.0.12000000.2 2007/07/24 05:29:24 cpati ship $ */
3
4 -- Global constant holding the package name
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OE_Lot_Serial_Util';
7
8 -- Procedure Clear_Dependent_Attr
9
10 PROCEDURE Clear_Dependent_Attr
11 ( p_attr_id IN NUMBER := FND_API.G_MISS_NUM
12 , p_x_Lot_Serial_rec IN OUT NOCOPY OE_Order_PUB.Lot_Serial_Rec_Type
13 , p_old_Lot_Serial_rec IN OE_Order_PUB.Lot_Serial_Rec_Type :=
14 OE_Order_PUB.G_MISS_LOT_SERIAL_REC
15 )
16 IS
17 l_index NUMBER := 0;
18 l_src_attr_tbl OE_GLOBALS.NUMBER_Tbl_Type;
19 l_dep_attr_tbl OE_GLOBALS.NUMBER_Tbl_Type;
20 BEGIN
21
22 -- SINCE THIS PROCEDURE IS DOING NOTHING, FOR PERFORMANCE, IT RETURNS IMMEDIATELY
23 RETURN;
24
25 -- Load out record
26
27
28 -- If attr_id is missing compare old and new records and for
29 -- every changed attribute clear its dependent fields.
30
31 IF p_attr_id = FND_API.G_MISS_NUM THEN
32
33 IF NOT OE_GLOBALS.Equal(p_x_Lot_Serial_rec.attribute1,p_old_Lot_Serial_rec.attribute1)
34 THEN
35 l_index := l_index + 1;
36 l_src_attr_tbl(l_index) := OE_LOT_SERIAL_UTIL.G_ATTRIBUTE1;
37 END IF;
38
39 IF NOT OE_GLOBALS.Equal(p_x_Lot_Serial_rec.attribute10,p_old_Lot_Serial_rec.attribute10)
40 THEN
41 l_index := l_index + 1;
42 l_src_attr_tbl(l_index) := OE_LOT_SERIAL_UTIL.G_ATTRIBUTE10;
43 END IF;
44
45 IF NOT OE_GLOBALS.Equal(p_x_Lot_Serial_rec.attribute11,p_old_Lot_Serial_rec.attribute11)
46 THEN
47 l_index := l_index + 1;
48 l_src_attr_tbl(l_index) := OE_LOT_SERIAL_UTIL.G_ATTRIBUTE11;
49 END IF;
50
51 IF NOT OE_GLOBALS.Equal(p_x_Lot_Serial_rec.attribute12,p_old_Lot_Serial_rec.attribute12)
52 THEN
53 l_index := l_index + 1;
54 l_src_attr_tbl(l_index) := OE_LOT_SERIAL_UTIL.G_ATTRIBUTE12;
55 END IF;
56
57 IF NOT OE_GLOBALS.Equal(p_x_Lot_Serial_rec.attribute13,p_old_Lot_Serial_rec.attribute13)
58 THEN
59 l_index := l_index + 1;
60 l_src_attr_tbl(l_index) := OE_LOT_SERIAL_UTIL.G_ATTRIBUTE13;
61 END IF;
62
63 IF NOT OE_GLOBALS.Equal(p_x_Lot_Serial_rec.attribute14,p_old_Lot_Serial_rec.attribute14)
64 THEN
65 l_index := l_index + 1;
66 l_src_attr_tbl(l_index) := OE_LOT_SERIAL_UTIL.G_ATTRIBUTE14;
67 END IF;
68
69 IF NOT OE_GLOBALS.Equal(p_x_Lot_Serial_rec.attribute15,p_old_Lot_Serial_rec.attribute15)
70 THEN
71 l_index := l_index + 1;
72 l_src_attr_tbl(l_index) := OE_LOT_SERIAL_UTIL.G_ATTRIBUTE15;
73 END IF;
74
75 IF NOT OE_GLOBALS.Equal(p_x_Lot_Serial_rec.attribute2,p_old_Lot_Serial_rec.attribute2)
76 THEN
77 l_index := l_index + 1;
78 l_src_attr_tbl(l_index) := OE_LOT_SERIAL_UTIL.G_ATTRIBUTE2;
79 END IF;
80
81 IF NOT OE_GLOBALS.Equal(p_x_Lot_Serial_rec.attribute3,p_old_Lot_Serial_rec.attribute3)
82 THEN
83 l_index := l_index + 1;
84 l_src_attr_tbl(l_index) := OE_LOT_SERIAL_UTIL.G_ATTRIBUTE3;
85 END IF;
86
87 IF NOT OE_GLOBALS.Equal(p_x_Lot_Serial_rec.attribute4,p_old_Lot_Serial_rec.attribute4)
88 THEN
89 l_index := l_index + 1;
90 l_src_attr_tbl(l_index) := OE_LOT_SERIAL_UTIL.G_ATTRIBUTE4;
91 END IF;
92
93 IF NOT OE_GLOBALS.Equal(p_x_Lot_Serial_rec.attribute5,p_old_Lot_Serial_rec.attribute5)
94 THEN
95 l_index := l_index + 1;
96 l_src_attr_tbl(l_index) := OE_LOT_SERIAL_UTIL.G_ATTRIBUTE5;
97 END IF;
98
99 IF NOT OE_GLOBALS.Equal(p_x_Lot_Serial_rec.attribute6,p_old_Lot_Serial_rec.attribute6)
100 THEN
101 l_index := l_index + 1;
102 l_src_attr_tbl(l_index) := OE_LOT_SERIAL_UTIL.G_ATTRIBUTE6;
103 END IF;
104
105 IF NOT OE_GLOBALS.Equal(p_x_Lot_Serial_rec.attribute7,p_old_Lot_Serial_rec.attribute7)
106 THEN
107 l_index := l_index + 1;
108 l_src_attr_tbl(l_index) := OE_LOT_SERIAL_UTIL.G_ATTRIBUTE7;
109 END IF;
110
111 IF NOT OE_GLOBALS.Equal(p_x_Lot_Serial_rec.attribute8,p_old_Lot_Serial_rec.attribute8)
112 THEN
113 l_index := l_index + 1;
114 l_src_attr_tbl(l_index) := OE_LOT_SERIAL_UTIL.G_ATTRIBUTE8;
115 END IF;
116
117 IF NOT OE_GLOBALS.Equal(p_x_Lot_Serial_rec.attribute9,p_old_Lot_Serial_rec.attribute9)
118 THEN
119 l_index := l_index + 1;
120 l_src_attr_tbl(l_index) := OE_LOT_SERIAL_UTIL.G_ATTRIBUTE9;
121 END IF;
122
123 IF NOT OE_GLOBALS.Equal(p_x_Lot_Serial_rec.context,p_old_Lot_Serial_rec.context)
124 THEN
125 l_index := l_index + 1;
126 l_src_attr_tbl(l_index) := OE_LOT_SERIAL_UTIL.G_CONTEXT;
127 END IF;
128
129 IF NOT OE_GLOBALS.Equal(p_x_Lot_Serial_rec.created_by,p_old_Lot_Serial_rec.created_by)
130 THEN
131 l_index := l_index + 1;
132 l_src_attr_tbl(l_index) := OE_LOT_SERIAL_UTIL.G_CREATED_BY;
133 END IF;
134
135 IF NOT OE_GLOBALS.Equal(p_x_Lot_Serial_rec.creation_date,p_old_Lot_Serial_rec.creation_date)
136 THEN
137 l_index := l_index + 1;
138 l_src_attr_tbl(l_index) := OE_LOT_SERIAL_UTIL.G_CREATION_DATE;
139 END IF;
140
141 IF NOT OE_GLOBALS.Equal(p_x_Lot_Serial_rec.from_serial_number,p_old_Lot_Serial_rec.from_serial_number)
142 THEN
143 l_index := l_index + 1;
144 l_src_attr_tbl(l_index) := OE_LOT_SERIAL_UTIL.G_FROM_SERIAL_NUMBER;
145 END IF;
146
147 IF NOT OE_GLOBALS.Equal(p_x_Lot_Serial_rec.last_updated_by,p_old_Lot_Serial_rec.last_updated_by)
148 THEN
149 l_index := l_index + 1;
150 l_src_attr_tbl(l_index) := OE_LOT_SERIAL_UTIL.G_LAST_UPDATED_BY;
151 END IF;
152
153 IF NOT OE_GLOBALS.Equal(p_x_Lot_Serial_rec.last_update_date,p_old_Lot_Serial_rec.last_update_date)
154 THEN
155 l_index := l_index + 1;
156 l_src_attr_tbl(l_index) := OE_LOT_SERIAL_UTIL.G_LAST_UPDATE_DATE;
157 END IF;
158
159 IF NOT OE_GLOBALS.Equal(p_x_Lot_Serial_rec.last_update_login,p_old_Lot_Serial_rec.last_update_login)
160 THEN
161 l_index := l_index + 1;
162 l_src_attr_tbl(l_index) := OE_LOT_SERIAL_UTIL.G_LAST_UPDATE_LOGIN;
163 END IF;
164
165 IF NOT OE_GLOBALS.Equal(p_x_Lot_Serial_rec.line_id,p_old_Lot_Serial_rec.line_id)
166 THEN
167 l_index := l_index + 1;
168 l_src_attr_tbl(l_index) := OE_LOT_SERIAL_UTIL.G_LINE;
169 END IF;
170
171 IF NOT OE_GLOBALS.Equal(p_x_Lot_Serial_rec.line_set_id,p_old_Lot_Serial_rec.line_set_id)
172 THEN
173 l_index := l_index + 1;
174 l_src_attr_tbl(l_index) := OE_LOT_SERIAL_UTIL.G_LINE_SET;
175 END IF;
176
177 IF NOT OE_GLOBALS.Equal(p_x_Lot_Serial_rec.lot_number,p_old_Lot_Serial_rec.lot_number)
178 THEN
179 l_index := l_index + 1;
180 l_src_attr_tbl(l_index) := OE_LOT_SERIAL_UTIL.G_LOT_NUMBER;
181 END IF;
182
183 /* IF OE_CODE_CONTROL.Get_Code_Release_Level >= '110510' -- INVCONV
184 THEN
185 IF NOT OE_GLOBALS.Equal(p_x_Lot_Serial_rec.sublot_number,p_old_Lot_Serial_rec.sublot_number) -- OPM 2380194 -- INVCONV
186 THEN
187 l_index := l_index + 1;
188 l_src_attr_tbl(l_index) := OE_LOT_SERIAL_UTIL.G_SUBLOT_NUMBER;
189 END IF;
190
191 END IF; */
192
193 IF NOT OE_GLOBALS.Equal(p_x_Lot_Serial_rec.lot_serial_id,p_old_Lot_Serial_rec.lot_serial_id)
194 THEN
195 l_index := l_index + 1;
196 l_src_attr_tbl(l_index) := OE_LOT_SERIAL_UTIL.G_LOT_SERIAL;
197 END IF;
198
199 IF NOT OE_GLOBALS.Equal(p_x_Lot_Serial_rec.quantity,p_old_Lot_Serial_rec.quantity)
200 THEN
201 l_index := l_index + 1;
202 l_src_attr_tbl(l_index) := OE_LOT_SERIAL_UTIL.G_QUANTITY;
203 END IF;
204
205 IF OE_CODE_CONTROL.Get_Code_Release_Level >= '110510'
206 THEN
207 IF NOT OE_GLOBALS.Equal(p_x_Lot_Serial_rec.quantity2,p_old_Lot_Serial_rec.quantity2) -- OPM 2380194
208 THEN
209 l_index := l_index + 1;
210 l_src_attr_tbl(l_index) := OE_LOT_SERIAL_UTIL.G_QUANTITY2;
211 END IF;
212 END IF;
213 IF NOT OE_GLOBALS.Equal(p_x_Lot_Serial_rec.to_serial_number,p_old_Lot_Serial_rec.to_serial_number)
214 THEN
215 l_index := l_index + 1;
216 l_src_attr_tbl(l_index) := OE_LOT_SERIAL_UTIL.G_TO_SERIAL_NUMBER;
217 END IF;
218
219 ELSIF p_attr_id = G_ATTRIBUTE1 THEN
220 l_index := l_index + 1;
221 l_src_attr_tbl(l_index) := OE_LOT_SERIAL_UTIL.G_ATTRIBUTE1;
222 ELSIF p_attr_id = G_ATTRIBUTE10 THEN
223 l_index := l_index + 1;
224 l_src_attr_tbl(l_index) := OE_LOT_SERIAL_UTIL.G_ATTRIBUTE10;
225 ELSIF p_attr_id = G_ATTRIBUTE11 THEN
226 l_index := l_index + 1;
227 l_src_attr_tbl(l_index) := OE_LOT_SERIAL_UTIL.G_ATTRIBUTE11;
228 ELSIF p_attr_id = G_ATTRIBUTE12 THEN
229 l_index := l_index + 1;
230 l_src_attr_tbl(l_index) := OE_LOT_SERIAL_UTIL.G_ATTRIBUTE12;
231 ELSIF p_attr_id = G_ATTRIBUTE13 THEN
232 l_index := l_index + 1;
233 l_src_attr_tbl(l_index) := OE_LOT_SERIAL_UTIL.G_ATTRIBUTE13;
234 ELSIF p_attr_id = G_ATTRIBUTE14 THEN
235 l_index := l_index + 1;
236 l_src_attr_tbl(l_index) := OE_LOT_SERIAL_UTIL.G_ATTRIBUTE14;
237 ELSIF p_attr_id = G_ATTRIBUTE15 THEN
238 l_index := l_index + 1;
239 l_src_attr_tbl(l_index) := OE_LOT_SERIAL_UTIL.G_ATTRIBUTE15;
240 ELSIF p_attr_id = G_ATTRIBUTE2 THEN
241 l_index := l_index + 1;
242 l_src_attr_tbl(l_index) := OE_LOT_SERIAL_UTIL.G_ATTRIBUTE2;
243 ELSIF p_attr_id = G_ATTRIBUTE3 THEN
244 l_index := l_index + 1;
245 l_src_attr_tbl(l_index) := OE_LOT_SERIAL_UTIL.G_ATTRIBUTE3;
246 ELSIF p_attr_id = G_ATTRIBUTE4 THEN
247 l_index := l_index + 1;
248 l_src_attr_tbl(l_index) := OE_LOT_SERIAL_UTIL.G_ATTRIBUTE4;
249 ELSIF p_attr_id = G_ATTRIBUTE5 THEN
250 l_index := l_index + 1;
251 l_src_attr_tbl(l_index) := OE_LOT_SERIAL_UTIL.G_ATTRIBUTE5;
252 ELSIF p_attr_id = G_ATTRIBUTE6 THEN
253 l_index := l_index + 1;
254 l_src_attr_tbl(l_index) := OE_LOT_SERIAL_UTIL.G_ATTRIBUTE6;
255 ELSIF p_attr_id = G_ATTRIBUTE7 THEN
256 l_index := l_index + 1;
257 l_src_attr_tbl(l_index) := OE_LOT_SERIAL_UTIL.G_ATTRIBUTE7;
258 ELSIF p_attr_id = G_ATTRIBUTE8 THEN
259 l_index := l_index + 1;
260 l_src_attr_tbl(l_index) := OE_LOT_SERIAL_UTIL.G_ATTRIBUTE8;
261 ELSIF p_attr_id = G_ATTRIBUTE9 THEN
262 l_index := l_index + 1;
263 l_src_attr_tbl(l_index) := OE_LOT_SERIAL_UTIL.G_ATTRIBUTE9;
264 ELSIF p_attr_id = G_CONTEXT THEN
265 l_index := l_index + 1;
266 l_src_attr_tbl(l_index) := OE_LOT_SERIAL_UTIL.G_CONTEXT;
267 ELSIF p_attr_id = G_CREATED_BY THEN
268 l_index := l_index + 1;
269 l_src_attr_tbl(l_index) := OE_LOT_SERIAL_UTIL.G_CREATED_BY;
270 ELSIF p_attr_id = G_CREATION_DATE THEN
271 l_index := l_index + 1;
272 l_src_attr_tbl(l_index) := OE_LOT_SERIAL_UTIL.G_CREATION_DATE;
273 ELSIF p_attr_id = G_FROM_SERIAL_NUMBER THEN
274 l_index := l_index + 1;
275 l_src_attr_tbl(l_index) := OE_LOT_SERIAL_UTIL.G_FROM_SERIAL_NUMBER;
276 ELSIF p_attr_id = G_LAST_UPDATED_BY THEN
277 l_index := l_index + 1;
278 l_src_attr_tbl(l_index) := OE_LOT_SERIAL_UTIL.G_LAST_UPDATED_BY;
279 ELSIF p_attr_id = G_LAST_UPDATE_DATE THEN
280 l_index := l_index + 1;
281 l_src_attr_tbl(l_index) := OE_LOT_SERIAL_UTIL.G_LAST_UPDATE_DATE;
282 ELSIF p_attr_id = G_LAST_UPDATE_LOGIN THEN
283 l_index := l_index + 1;
284 l_src_attr_tbl(l_index) := OE_LOT_SERIAL_UTIL.G_LAST_UPDATE_LOGIN;
285 ELSIF p_attr_id = G_LINE THEN
286 l_index := l_index + 1;
287 l_src_attr_tbl(l_index) := OE_LOT_SERIAL_UTIL.G_LINE;
288 ELSIF p_attr_id = G_LOT_NUMBER THEN
289 l_index := l_index + 1;
290 l_src_attr_tbl(l_index) := OE_LOT_SERIAL_UTIL.G_LOT_NUMBER;
291 ELSIF p_attr_id = G_LOT_SERIAL THEN
292 l_index := l_index + 1;
293 l_src_attr_tbl(l_index) := OE_LOT_SERIAL_UTIL.G_LOT_SERIAL;
294 /*ELSIF p_attr_id = G_SUBLOT_NUMBER THEN --OPM 2380194 INVCONV
295 IF OE_CODE_CONTROL.CODE_RELEASE_LEVEL >= '110510'
296 THEN
297 l_index := l_index + 1;
298 l_src_attr_tbl(l_index) := OE_LOT_SERIAL_UTIL.G_SUBLOT_NUMBER;
299 END IF; */
300 ELSIF p_attr_id = G_QUANTITY THEN
301 l_index := l_index + 1;
302 l_src_attr_tbl(l_index) := OE_LOT_SERIAL_UTIL.G_QUANTITY;
303 l_src_attr_tbl(l_index) := OE_LOT_SERIAL_UTIL.G_QUANTITY;
304
305 ELSIF p_attr_id = G_QUANTITY2 THEN --OPM 2380194
306 IF OE_CODE_CONTROL.CODE_RELEASE_LEVEL >= '110510'
307 THEN
308 l_index := l_index + 1;
309 l_src_attr_tbl(l_index) := OE_LOT_SERIAL_UTIL.G_QUANTITY2;
310 END IF;
311 ELSIF p_attr_id = G_TO_SERIAL_NUMBER THEN
312 l_index := l_index + 1;
313 l_src_attr_tbl(l_index) := OE_LOT_SERIAL_UTIL.G_TO_SERIAL_NUMBER;
314 END IF;
315
316 END Clear_Dependent_Attr;
317
318 -- Procedure Apply_Attribute_Changes
319
320 PROCEDURE Apply_Attribute_Changes
321 ( p_x_Lot_Serial_rec IN OUT NOCOPY OE_Order_PUB.Lot_Serial_Rec_Type
322 , p_old_Lot_Serial_rec IN OE_Order_PUB.Lot_Serial_Rec_Type :=
323 OE_Order_PUB.G_MISS_LOT_SERIAL_REC
324 )
325 IS
326 l_serial_validation_flag VARCHAR2(1) := 'N';
327 x_prefix VARCHAR2(80);
328 x_quantity VARCHAR2(80);
329 x_from_number VARCHAR2(80);
330 x_to_number VARCHAR2(80);
331 x_error_code NUMBER;
332
333 BEGIN
334
335 -- Please take out the comment when there is going to be a code associated
336 -- with following attributes
337 /*
338 IF NOT OE_GLOBALS.Equal(p_x_Lot_Serial_rec.attribute1,p_old_Lot_Serial_rec.attribute1)
339 THEN
340 NULL;
341 END IF;
342
343 IF NOT OE_GLOBALS.Equal(p_x_Lot_Serial_rec.attribute10,p_old_Lot_Serial_rec.attribute10)
344 THEN
345 NULL;
346 END IF;
347
348 IF NOT OE_GLOBALS.Equal(p_x_Lot_Serial_rec.attribute11,p_old_Lot_Serial_rec.attribute11)
349 THEN
350 NULL;
351 END IF;
352
353 IF NOT OE_GLOBALS.Equal(p_x_Lot_Serial_rec.attribute12,p_old_Lot_Serial_rec.attribute12)
354 THEN
355 NULL;
356 END IF;
357
358 IF NOT OE_GLOBALS.Equal(p_x_Lot_Serial_rec.attribute13,p_old_Lot_Serial_rec.attribute13)
359 THEN
360 NULL;
361 END IF;
362
363 IF NOT OE_GLOBALS.Equal(p_x_Lot_Serial_rec.attribute14,p_old_Lot_Serial_rec.attribute14)
364 THEN
365 NULL;
366 END IF;
367
368 IF NOT OE_GLOBALS.Equal(p_x_Lot_Serial_rec.attribute15,p_old_Lot_Serial_rec.attribute15)
369 THEN
370 NULL;
371 END IF;
372
373 IF NOT OE_GLOBALS.Equal(p_x_Lot_Serial_rec.attribute2,p_old_Lot_Serial_rec.attribute2)
374 THEN
375 NULL;
376 END IF;
377
378 IF NOT OE_GLOBALS.Equal(p_x_Lot_Serial_rec.attribute3,p_old_Lot_Serial_rec.attribute3)
379 THEN
380 NULL;
381 END IF;
382
383 IF NOT OE_GLOBALS.Equal(p_x_Lot_Serial_rec.attribute4,p_old_Lot_Serial_rec.attribute4)
384 THEN
385 NULL;
386 END IF;
387
388 IF NOT OE_GLOBALS.Equal(p_x_Lot_Serial_rec.attribute5,p_old_Lot_Serial_rec.attribute5)
389 THEN
390 NULL;
391 END IF;
392
393 IF NOT OE_GLOBALS.Equal(p_x_Lot_Serial_rec.attribute6,p_old_Lot_Serial_rec.attribute6)
394 THEN
395 NULL;
396 END IF;
397
398 IF NOT OE_GLOBALS.Equal(p_x_Lot_Serial_rec.attribute7,p_old_Lot_Serial_rec.attribute7)
399 THEN
400 NULL;
401 END IF;
402
403 IF NOT OE_GLOBALS.Equal(p_x_Lot_Serial_rec.attribute8,p_old_Lot_Serial_rec.attribute8)
404 THEN
405 NULL;
406 END IF;
407
408 IF NOT OE_GLOBALS.Equal(p_x_Lot_Serial_rec.attribute9,p_old_Lot_Serial_rec.attribute9)
409 THEN
410 NULL;
411 END IF;
412
413 IF NOT OE_GLOBALS.Equal(p_x_Lot_Serial_rec.context,p_old_Lot_Serial_rec.context)
414 THEN
415 NULL;
416 END IF;
417
418 IF NOT OE_GLOBALS.Equal(p_x_Lot_Serial_rec.created_by,p_old_Lot_Serial_rec.created_by)
419 THEN
420 NULL;
421 END IF;
422
423 IF NOT OE_GLOBALS.Equal(p_x_Lot_Serial_rec.creation_date,p_old_Lot_Serial_rec.creation_date)
424 THEN
425 NULL;
426 END IF;
427
428
429 IF NOT OE_GLOBALS.Equal(p_x_Lot_Serial_rec.last_updated_by,p_old_Lot_Serial_rec.last_updated_by)
430 THEN
431 NULL;
432 END IF;
433
434 IF NOT OE_GLOBALS.Equal(p_x_Lot_Serial_rec.last_update_date,p_old_Lot_Serial_rec.last_update_date)
435 THEN
436 NULL;
437 END IF;
438
439 IF NOT OE_GLOBALS.Equal(p_x_Lot_Serial_rec.last_update_login,p_old_Lot_Serial_rec.last_update_login)
440 THEN
441 NULL;
442 END IF;
443
444 IF NOT OE_GLOBALS.Equal(p_x_Lot_Serial_rec.line_id,p_old_Lot_Serial_rec.line_id)
445 THEN
446 NULL;
447 END IF;
448
449 IF NOT OE_GLOBALS.Equal(p_x_Lot_Serial_rec.line_set_id,p_old_Lot_Serial_rec.line_set_id)
450 THEN
451 NULL;
452 END IF;
453
454 IF NOT OE_GLOBALS.Equal(p_x_Lot_Serial_rec.lot_number,p_old_Lot_Serial_rec.lot_number)
455 THEN
456 NULL;
457 END IF;
458
459 IF NOT OE_GLOBALS.Equal(p_x_Lot_Serial_rec.lot_serial_id,p_old_Lot_Serial_rec.lot_serial_id)
460 THEN
461 NULL;
462 END IF;
463
464 IF NOT OE_GLOBALS.Equal(p_x_Lot_Serial_rec.from_serial_number,p_old_Lot_Serial_rec.from_serial_number)
465 THEN
466 l_serial_validation_flag := 'Y';
467 END IF;
468
469 IF NOT OE_GLOBALS.Equal(p_x_Lot_Serial_rec.quantity,p_old_Lot_Serial_rec.quantity)
470 THEN
471 l_serial_validation_flag := 'Y';
472 END IF;
473
474 IF NOT OE_GLOBALS.Equal(p_x_Lot_Serial_rec.to_serial_number,p_old_Lot_Serial_rec.to_serial_number)
475 THEN
476 l_serial_validation_flag := 'Y';
477 END IF;
478
479 IF l_serial_validation_flag = 'Y' THEN
480
481 IF p_x_Lot_Serial_rec.from_serial_number IS NOT NULL AND
482 p_x_Lot_Serial_rec.from_serial_number <> FND_API.G_MISS_CHAR AND
483 p_x_Lot_Serial_rec.to_serial_number <> FND_API.G_MISS_CHAR
484 THEN
485 IF NOT MTL_SERIAL_CHECK.INV_SERIAL_INFO(
486 p_x_Lot_Serial_rec.from_serial_number,
487 p_x_Lot_Serial_rec.to_serial_number,
488 x_prefix,
489 x_quantity,
490 x_from_number,
491 x_to_number,
492 x_error_code)
493 THEN
494 fnd_message.set_name('ONT','OE_QUANTITY_MISMATCH');
495 oe_msg_pub.ADD;
496 raise FND_API.G_EXC_ERROR;
497 ELSE
498 IF p_x_Lot_Serial_rec.quantity <> x_quantity THEN
499 fnd_message.set_name('ONT','OE_QUANTITY_MISMATCH');
500 oe_msg_pub.ADD;
501 raise FND_API.G_EXC_ERROR;
502 END IF;
503 END IF;
504
505 END IF;
506 l_serial_validation_flag := 'N';
507
508 END IF;
509 */
510 NULL;
511
512 END Apply_Attribute_Changes;
513
514 -- Procedure Complete_Record
515
516 PROCEDURE Complete_Record
517 ( p_x_Lot_Serial_rec IN OUT NOCOPY OE_Order_PUB.Lot_Serial_Rec_Type
518 , p_old_Lot_Serial_rec IN OE_Order_PUB.Lot_Serial_Rec_Type
519 )
520 IS
521 BEGIN
522
523 OE_DEBUG_PUB.ADD('Inside complete record',1);
524 OE_DEBUG_PUB.ADD('The quantity is '||to_char(p_x_Lot_Serial_rec.quantity),1);
525 IF p_x_Lot_Serial_rec.attribute1 = FND_API.G_MISS_CHAR THEN
526 p_x_Lot_Serial_rec.attribute1 := p_old_Lot_Serial_rec.attribute1;
527 END IF;
528
529 IF p_x_Lot_Serial_rec.attribute10 = FND_API.G_MISS_CHAR THEN
530 p_x_Lot_Serial_rec.attribute10 := p_old_Lot_Serial_rec.attribute10;
531 END IF;
532
533 IF p_x_Lot_Serial_rec.attribute11 = FND_API.G_MISS_CHAR THEN
534 p_x_Lot_Serial_rec.attribute11 := p_old_Lot_Serial_rec.attribute11;
535 END IF;
536
537 IF p_x_Lot_Serial_rec.attribute12 = FND_API.G_MISS_CHAR THEN
538 p_x_Lot_Serial_rec.attribute12 := p_old_Lot_Serial_rec.attribute12;
539 END IF;
540
541 IF p_x_Lot_Serial_rec.attribute13 = FND_API.G_MISS_CHAR THEN
542 p_x_Lot_Serial_rec.attribute13 := p_old_Lot_Serial_rec.attribute13;
543 END IF;
544
545 IF p_x_Lot_Serial_rec.attribute14 = FND_API.G_MISS_CHAR THEN
546 p_x_Lot_Serial_rec.attribute14 := p_old_Lot_Serial_rec.attribute14;
547 END IF;
548
549 IF p_x_Lot_Serial_rec.attribute15 = FND_API.G_MISS_CHAR THEN
550 p_x_Lot_Serial_rec.attribute15 := p_old_Lot_Serial_rec.attribute15;
551 END IF;
552
553 IF p_x_Lot_Serial_rec.attribute2 = FND_API.G_MISS_CHAR THEN
554 p_x_Lot_Serial_rec.attribute2 := p_old_Lot_Serial_rec.attribute2;
555 END IF;
556
557 IF p_x_Lot_Serial_rec.attribute3 = FND_API.G_MISS_CHAR THEN
558 p_x_Lot_Serial_rec.attribute3 := p_old_Lot_Serial_rec.attribute3;
559 END IF;
560
561 IF p_x_Lot_Serial_rec.attribute4 = FND_API.G_MISS_CHAR THEN
562 p_x_Lot_Serial_rec.attribute4 := p_old_Lot_Serial_rec.attribute4;
563 END IF;
564
565 IF p_x_Lot_Serial_rec.attribute5 = FND_API.G_MISS_CHAR THEN
566 p_x_Lot_Serial_rec.attribute5 := p_old_Lot_Serial_rec.attribute5;
567 END IF;
568
569 IF p_x_Lot_Serial_rec.attribute6 = FND_API.G_MISS_CHAR THEN
570 p_x_Lot_Serial_rec.attribute6 := p_old_Lot_Serial_rec.attribute6;
571 END IF;
572
573 IF p_x_Lot_Serial_rec.attribute7 = FND_API.G_MISS_CHAR THEN
574 p_x_Lot_Serial_rec.attribute7 := p_old_Lot_Serial_rec.attribute7;
575 END IF;
576
577 IF p_x_Lot_Serial_rec.attribute8 = FND_API.G_MISS_CHAR THEN
578 p_x_Lot_Serial_rec.attribute8 := p_old_Lot_Serial_rec.attribute8;
579 END IF;
580
581 IF p_x_Lot_Serial_rec.attribute9 = FND_API.G_MISS_CHAR THEN
582 p_x_Lot_Serial_rec.attribute9 := p_old_Lot_Serial_rec.attribute9;
583 END IF;
584
585 IF p_x_Lot_Serial_rec.context = FND_API.G_MISS_CHAR THEN
586 p_x_Lot_Serial_rec.context := p_old_Lot_Serial_rec.context;
587 END IF;
588
589 IF p_x_Lot_Serial_rec.created_by = FND_API.G_MISS_NUM THEN
590 p_x_Lot_Serial_rec.created_by := p_old_Lot_Serial_rec.created_by;
591 END IF;
592
593 IF p_x_Lot_Serial_rec.creation_date = FND_API.G_MISS_DATE THEN
594 p_x_Lot_Serial_rec.creation_date := p_old_Lot_Serial_rec.creation_date;
595 END IF;
596
597 IF p_x_Lot_Serial_rec.from_serial_number = FND_API.G_MISS_CHAR THEN
598 p_x_Lot_Serial_rec.from_serial_number := p_old_Lot_Serial_rec.from_serial_number;
599 END IF;
600
601 IF p_x_Lot_Serial_rec.last_updated_by = FND_API.G_MISS_NUM THEN
602 p_x_Lot_Serial_rec.last_updated_by := p_old_Lot_Serial_rec.last_updated_by;
603 END IF;
604
605 IF p_x_Lot_Serial_rec.last_update_date = FND_API.G_MISS_DATE THEN
606 p_x_Lot_Serial_rec.last_update_date := p_old_Lot_Serial_rec.last_update_date;
607 END IF;
608
609 IF p_x_Lot_Serial_rec.last_update_login = FND_API.G_MISS_NUM THEN
610 p_x_Lot_Serial_rec.last_update_login := p_old_Lot_Serial_rec.last_update_login;
611 END IF;
612
613 IF p_x_Lot_Serial_rec.line_id = FND_API.G_MISS_NUM THEN
614 p_x_Lot_Serial_rec.line_id := p_old_Lot_Serial_rec.line_id;
615 END IF;
616
617 IF p_x_Lot_Serial_rec.line_set_id = FND_API.G_MISS_NUM THEN
618 p_x_Lot_Serial_rec.line_set_id := p_old_Lot_Serial_rec.line_set_id;
619 END IF;
620
621 IF p_x_Lot_Serial_rec.lot_number = FND_API.G_MISS_CHAR THEN
622 p_x_Lot_Serial_rec.lot_number := p_old_Lot_Serial_rec.lot_number;
623 END IF;
624
625 IF OE_CODE_CONTROL.Get_Code_Release_Level >= '110510'
626 THEN
627 /*IF p_x_Lot_Serial_rec.sublot_number = FND_API.G_MISS_CHAR THEN
628 p_x_Lot_Serial_rec.sublot_number := p_old_Lot_Serial_rec.sublot_number;
629 END IF; --OPM 2380194 */ -- INVCONV
630
631 IF p_x_Lot_Serial_rec.quantity2 = FND_API.G_MISS_NUM THEN --OPM 2380194
632 p_x_Lot_Serial_rec.quantity2 := p_old_Lot_Serial_rec.quantity2;
633 END IF;
634 END IF;
635
636 IF p_x_Lot_Serial_rec.lot_serial_id = FND_API.G_MISS_NUM THEN
637 p_x_Lot_Serial_rec.lot_serial_id := p_old_Lot_Serial_rec.lot_serial_id;
638 END IF;
639
640 IF p_x_Lot_Serial_rec.quantity = FND_API.G_MISS_NUM THEN
641 p_x_Lot_Serial_rec.quantity := p_old_Lot_Serial_rec.quantity;
642 END IF;
643
644 IF p_x_Lot_Serial_rec.to_serial_number = FND_API.G_MISS_CHAR THEN
645 p_x_Lot_Serial_rec.to_serial_number := p_old_Lot_Serial_rec.to_serial_number;
646 END IF;
647
648 OE_DEBUG_PUB.ADD('The quantity is '||to_char(p_x_Lot_Serial_rec.quantity),1);
649 END Complete_Record;
650
651 -- Procedure Convert_Miss_To_Null
652
653 PROCEDURE Convert_Miss_To_Null
654 ( p_x_Lot_Serial_rec IN OUT NOCOPY OE_Order_PUB.Lot_Serial_Rec_Type
655 )
656 IS
657 BEGIN
658
659 IF p_x_Lot_Serial_rec.attribute1 = FND_API.G_MISS_CHAR THEN
660 p_x_Lot_Serial_rec.attribute1 := NULL;
661 END IF;
662
663 IF p_x_Lot_Serial_rec.attribute10 = FND_API.G_MISS_CHAR THEN
664 p_x_Lot_Serial_rec.attribute10 := NULL;
665 END IF;
666
667 IF p_x_Lot_Serial_rec.attribute11 = FND_API.G_MISS_CHAR THEN
668 p_x_Lot_Serial_rec.attribute11 := NULL;
669 END IF;
670
671 IF p_x_Lot_Serial_rec.attribute12 = FND_API.G_MISS_CHAR THEN
672 p_x_Lot_Serial_rec.attribute12 := NULL;
673 END IF;
674
675 IF p_x_Lot_Serial_rec.attribute13 = FND_API.G_MISS_CHAR THEN
676 p_x_Lot_Serial_rec.attribute13 := NULL;
677 END IF;
678
679 IF p_x_Lot_Serial_rec.attribute14 = FND_API.G_MISS_CHAR THEN
680 p_x_Lot_Serial_rec.attribute14 := NULL;
681 END IF;
682
683 IF p_x_Lot_Serial_rec.attribute15 = FND_API.G_MISS_CHAR THEN
684 p_x_Lot_Serial_rec.attribute15 := NULL;
685 END IF;
686
687 IF p_x_Lot_Serial_rec.attribute2 = FND_API.G_MISS_CHAR THEN
688 p_x_Lot_Serial_rec.attribute2 := NULL;
689 END IF;
690
691 IF p_x_Lot_Serial_rec.attribute3 = FND_API.G_MISS_CHAR THEN
692 p_x_Lot_Serial_rec.attribute3 := NULL;
693 END IF;
694
695 IF p_x_Lot_Serial_rec.attribute4 = FND_API.G_MISS_CHAR THEN
696 p_x_Lot_Serial_rec.attribute4 := NULL;
697 END IF;
698
699 IF p_x_Lot_Serial_rec.attribute5 = FND_API.G_MISS_CHAR THEN
700 p_x_Lot_Serial_rec.attribute5 := NULL;
701 END IF;
702
703 IF p_x_Lot_Serial_rec.attribute6 = FND_API.G_MISS_CHAR THEN
704 p_x_Lot_Serial_rec.attribute6 := NULL;
705 END IF;
706
707 IF p_x_Lot_Serial_rec.attribute7 = FND_API.G_MISS_CHAR THEN
708 p_x_Lot_Serial_rec.attribute7 := NULL;
709 END IF;
710
711 IF p_x_Lot_Serial_rec.attribute8 = FND_API.G_MISS_CHAR THEN
712 p_x_Lot_Serial_rec.attribute8 := NULL;
713 END IF;
714
715 IF p_x_Lot_Serial_rec.attribute9 = FND_API.G_MISS_CHAR THEN
716 p_x_Lot_Serial_rec.attribute9 := NULL;
717 END IF;
718
719 IF p_x_Lot_Serial_rec.context = FND_API.G_MISS_CHAR THEN
720 p_x_Lot_Serial_rec.context := NULL;
721 END IF;
722
723 IF p_x_Lot_Serial_rec.created_by = FND_API.G_MISS_NUM THEN
724 p_x_Lot_Serial_rec.created_by := NULL;
725 END IF;
726
727 IF p_x_Lot_Serial_rec.creation_date = FND_API.G_MISS_DATE THEN
728 p_x_Lot_Serial_rec.creation_date := NULL;
729 END IF;
730
731 IF p_x_Lot_Serial_rec.from_serial_number = FND_API.G_MISS_CHAR THEN
732 p_x_Lot_Serial_rec.from_serial_number := NULL;
733 END IF;
734
735 IF p_x_Lot_Serial_rec.last_updated_by = FND_API.G_MISS_NUM THEN
736 p_x_Lot_Serial_rec.last_updated_by := NULL;
737 END IF;
738
739 IF p_x_Lot_Serial_rec.last_update_date = FND_API.G_MISS_DATE THEN
740 p_x_Lot_Serial_rec.last_update_date := NULL;
741 END IF;
742
743 IF p_x_Lot_Serial_rec.last_update_login = FND_API.G_MISS_NUM THEN
744 p_x_Lot_Serial_rec.last_update_login := NULL;
745 END IF;
746
747 IF p_x_Lot_Serial_rec.line_id = FND_API.G_MISS_NUM THEN
748 p_x_Lot_Serial_rec.line_id := NULL;
749 END IF;
750
751 IF p_x_Lot_Serial_rec.line_set_id = FND_API.G_MISS_NUM THEN
752 p_x_Lot_Serial_rec.line_set_id := NULL;
753 END IF;
754
755 IF p_x_Lot_Serial_rec.lot_number = FND_API.G_MISS_CHAR THEN
756 p_x_Lot_Serial_rec.lot_number := NULL;
757 END IF;
758
759 IF OE_CODE_CONTROL.Get_Code_Release_Level >= '110510'
760 THEN
761
762 /*IF p_x_Lot_Serial_rec.sublot_number = FND_API.G_MISS_CHAR THENINVCONV
763 p_x_Lot_Serial_rec.sublot_number := NULL;
764 END IF; --OPM 2380194 */
765
766 IF p_x_Lot_Serial_rec.quantity2 = FND_API.G_MISS_NUM THEN --OPM 2380194
767 p_x_Lot_Serial_rec.quantity2 := NULL;
768 END IF;
769 END IF;
770
771 IF p_x_Lot_Serial_rec.lot_serial_id = FND_API.G_MISS_NUM THEN
772 p_x_Lot_Serial_rec.lot_serial_id := NULL;
773 END IF;
774
775 IF p_x_Lot_Serial_rec.quantity = FND_API.G_MISS_NUM THEN
776 p_x_Lot_Serial_rec.quantity := NULL;
777 END IF;
778
779 IF p_x_Lot_Serial_rec.to_serial_number = FND_API.G_MISS_CHAR THEN
780 p_x_Lot_Serial_rec.to_serial_number := NULL;
781 END IF;
782
783 END Convert_Miss_To_Null;
784
785 -- Procedure Set_Line_Set_ID
786 -- When parent line is split, this procedure should be called to update
787 -- the line set id to point to the line set.
788 -- Whenever line_set_id is set, the records should be accessed by line_set_id
789 Procedure Set_Line_Set_ID
790 ( p_Line_ID IN NUMBER
791 , p_Line_Set_ID IN NUMBER)
792 IS
793 BEGIN
794
795 UPDATE OE_LOT_SERIAL_NUMBERS
796 SET LINE_SET_ID = p_Line_Set_ID
797 WHERE LINE_ID = p_Line_ID;
798
799 IF (SQL%NOTFOUND) THEN
800 -- No lot/serial numbers attached to the return line, do nothing
801 NULL;
802 END IF;
803
804 EXCEPTION
805
806 WHEN OTHERS THEN
807
808 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
809 THEN
810 OE_MSG_PUB.Add_Exc_Msg
811 ( G_PKG_NAME
812 , 'Set_Line_Set_ID'
813 );
814 END IF;
815
816 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
817
818 END Set_Line_Set_ID;
819
820 -- Procedure Update_Row
821
822 PROCEDURE Update_Row
823 ( p_Lot_Serial_rec IN OUT NOCOPY OE_Order_PUB.Lot_Serial_Rec_Type
824 )
825 IS
826 l_lock_control NUMBER;
827 /* jolin start*/
828 --added for notification framework
829 l_lot_serial_rec OE_Order_PUB.Lot_serial_Rec_Type;
830 l_index NUMBER;
831 l_return_status VARCHAR2(1);
832 /* jolin end*/
833
834 BEGIN
835
836 SELECT lock_control
837 INTO l_lock_control
838 FROM OE_LOT_SERIAL_NUMBERS
839 WHERE lot_serial_id = p_Lot_Serial_rec.lot_serial_id;
840
841 l_lock_control := l_lock_control + 1;
842
843 /* jolin start*/
844 --added query_row for notification framework
845 --before update, query lot serial record, this record will be used
846 --to update global picture
847
848 OE_LOT_SERIAL_UTIL.Query_Row(p_lot_serial_id => p_lot_serial_rec.lot_serial_id,
849 x_lot_serial_rec =>l_lot_serial_rec);
850 oe_debug_pub.add('before update, lot_serial_id= '|| l_lot_serial_rec.lot_serial_id, 1);
851 /* jolin end*/
852
853 UPDATE OE_LOT_SERIAL_NUMBERS
854 SET ATTRIBUTE1 = p_Lot_Serial_rec.attribute1
855 , ATTRIBUTE10 = p_Lot_Serial_rec.attribute10
856 , ATTRIBUTE11 = p_Lot_Serial_rec.attribute11
857 , ATTRIBUTE12 = p_Lot_Serial_rec.attribute12
858 , ATTRIBUTE13 = p_Lot_Serial_rec.attribute13
859 , ATTRIBUTE14 = p_Lot_Serial_rec.attribute14
860 , ATTRIBUTE15 = p_Lot_Serial_rec.attribute15
861 , ATTRIBUTE2 = p_Lot_Serial_rec.attribute2
862 , ATTRIBUTE3 = p_Lot_Serial_rec.attribute3
863 , ATTRIBUTE4 = p_Lot_Serial_rec.attribute4
864 , ATTRIBUTE5 = p_Lot_Serial_rec.attribute5
865 , ATTRIBUTE6 = p_Lot_Serial_rec.attribute6
866 , ATTRIBUTE7 = p_Lot_Serial_rec.attribute7
867 , ATTRIBUTE8 = p_Lot_Serial_rec.attribute8
868 , ATTRIBUTE9 = p_Lot_Serial_rec.attribute9
869 , CONTEXT = p_Lot_Serial_rec.context
870 , CREATED_BY = p_Lot_Serial_rec.created_by
871 , CREATION_DATE = p_Lot_Serial_rec.creation_date
872 , FROM_SERIAL_NUMBER = p_Lot_Serial_rec.from_serial_number
873 , LAST_UPDATED_BY = p_Lot_Serial_rec.last_updated_by
874 , LAST_UPDATE_DATE = p_Lot_Serial_rec.last_update_date
875 , LAST_UPDATE_LOGIN = p_Lot_Serial_rec.last_update_login
876 , LINE_ID = p_Lot_Serial_rec.line_id
877 , LOT_NUMBER = p_Lot_Serial_rec.lot_number
878 -- , SUBLOT_NUMBER = p_Lot_Serial_rec.sublot_number --OPM 2380194 INVCONV
879 , LOT_SERIAL_ID = p_Lot_Serial_rec.lot_serial_id
880 , QUANTITY = p_Lot_Serial_rec.quantity
881 , QUANTITY2 = p_Lot_Serial_rec.quantity2 --OPM 2380194
882 , TO_SERIAL_NUMBER = p_Lot_Serial_rec.to_serial_number
883 , ORIG_SYS_LOTSERIAL_REF = p_Lot_Serial_rec.orig_sys_lotserial_ref
884 , LINE_SET_ID = p_Lot_Serial_rec.line_set_id
885 , LOCK_CONTROL = p_Lot_Serial_rec.lock_control
886 WHERE LOT_SERIAL_ID = p_Lot_Serial_rec.lot_serial_id
887 ;
888
889 /* jolin start*/
890 IF OE_CODE_CONTROL.CODE_RELEASE_LEVEL >= '110508' THEN
891
892 -- calling notification framework to update global picture
893
894 oe_debug_pub.add('after update, old lot serial Id= ' || l_lot_serial_rec.lot_serial_id);
895 oe_debug_pub.add('after update, new lot serial Id= ' || p_lot_serial_rec.lot_serial_id);
896
897 OE_ORDER_UTIL.Update_Global_Picture(
898 p_Upd_New_Rec_If_Exists => True,
899 p_lot_serial_rec => p_lot_serial_rec,
900 p_old_lot_serial_rec => l_lot_serial_rec,
901 p_lot_serial_id => p_lot_serial_rec.lot_serial_id,
902 x_index => l_index,
903 x_return_status => l_return_status);
904
905 OE_DEBUG_PUB.ADD('Update_Global Return Status from OE_LOT_SERIAL_UTIL.update_row is: ' || l_return_status);
906
907 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
908 OE_DEBUG_PUB.ADD('EVENT NOTIFY - Unexpected Error');
909 OE_DEBUG_PUB.ADD('Exiting OE_LOT_SERIAL_UTIL.Update_ROW', 1);
910 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
911 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
912 OE_DEBUG_PUB.ADD('Update_Global_Picture Error in OE_LOT_SERIAL_UTIL.Update_row');
913 OE_DEBUG_PUB.ADD('Exiting OE_LOT_SERIAL_UTIL.Update_ROW', 1);
914 RAISE FND_API.G_EXC_ERROR;
915 END IF;
916
917 -- notification framework end
918 END IF; /* code set is pack H or higher */
919 /* jolin end*/
920
921 EXCEPTION
922
923 WHEN OTHERS THEN
924
925 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
926 THEN
927 OE_MSG_PUB.Add_Exc_Msg
928 ( G_PKG_NAME
929 , 'Update_Row'
930 );
931 END IF;
932
933 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
934
935 END Update_Row;
936
937 -- Procedure Insert_Row
938
939 PROCEDURE Insert_Row
940 ( p_Lot_Serial_rec IN OUT NOCOPY OE_Order_PUB.Lot_Serial_Rec_Type
941 )
942 IS
943 l_lock_control NUMBER := 1;
944 /* jolin start*/
945 l_index NUMBER;
946 l_return_status VARCHAR2(1);
947 /* jolin end*/
948
949 BEGIN
950
951 INSERT INTO OE_LOT_SERIAL_NUMBERS
952 ( ATTRIBUTE1
953 , ATTRIBUTE10
954 , ATTRIBUTE11
955 , ATTRIBUTE12
956 , ATTRIBUTE13
957 , ATTRIBUTE14
958 , ATTRIBUTE15
959 , ATTRIBUTE2
960 , ATTRIBUTE3
961 , ATTRIBUTE4
962 , ATTRIBUTE5
963 , ATTRIBUTE6
964 , ATTRIBUTE7
965 , ATTRIBUTE8
966 , ATTRIBUTE9
967 , CONTEXT
968 , CREATED_BY
969 , CREATION_DATE
970 , FROM_SERIAL_NUMBER
971 , LAST_UPDATED_BY
972 , LAST_UPDATE_DATE
973 , LAST_UPDATE_LOGIN
974 , LINE_ID
975 , LOT_NUMBER
976 -- , SUBLOT_NUMBER --OPM 2380194 INVCONV
977 , LOT_SERIAL_ID
978 , QUANTITY
979 , QUANTITY2 --OPM 2380194
980 , TO_SERIAL_NUMBER
981 , ORIG_SYS_LOTSERIAL_REF
982 , LINE_SET_ID
983 , LOCK_CONTROL
984 )
985 VALUES
986 ( p_Lot_Serial_rec.attribute1
987 , p_Lot_Serial_rec.attribute10
988 , p_Lot_Serial_rec.attribute11
989 , p_Lot_Serial_rec.attribute12
990 , p_Lot_Serial_rec.attribute13
991 , p_Lot_Serial_rec.attribute14
992 , p_Lot_Serial_rec.attribute15
993 , p_Lot_Serial_rec.attribute2
994 , p_Lot_Serial_rec.attribute3
995 , p_Lot_Serial_rec.attribute4
996 , p_Lot_Serial_rec.attribute5
997 , p_Lot_Serial_rec.attribute6
998 , p_Lot_Serial_rec.attribute7
999 , p_Lot_Serial_rec.attribute8
1000 , p_Lot_Serial_rec.attribute9
1001 , p_Lot_Serial_rec.context
1002 , p_Lot_Serial_rec.created_by
1003 , p_Lot_Serial_rec.creation_date
1004 , p_Lot_Serial_rec.from_serial_number
1005 , p_Lot_Serial_rec.last_updated_by
1006 , p_Lot_Serial_rec.last_update_date
1007 , p_Lot_Serial_rec.last_update_login
1008 , p_Lot_Serial_rec.line_id
1009 , p_Lot_Serial_rec.lot_number
1010 -- , p_Lot_Serial_rec.sublot_number --OPM 2380194 INVCONV
1011 , p_Lot_Serial_rec.lot_serial_id
1012 , p_Lot_Serial_rec.quantity
1013 , p_Lot_Serial_rec.quantity2 --OPM 2380194
1014 , p_Lot_Serial_rec.to_serial_number
1015 , p_Lot_Serial_rec.orig_sys_lotserial_ref
1016 , p_Lot_Serial_rec.line_set_id
1017 , p_Lot_Serial_rec.lock_control
1018 );
1019
1020 /* jolin start*/
1021 IF OE_CODE_CONTROL.CODE_RELEASE_LEVEL >= '110508' THEN
1022 -- calling notification framework to update global picture
1023 OE_ORDER_UTIL.Update_Global_Picture(
1024 p_Upd_New_Rec_If_Exists => True,
1025 p_old_lot_serial_rec => NULL,
1026 p_lot_serial_rec => p_lot_serial_rec,
1027 p_lot_serial_id => p_lot_serial_rec.lot_serial_id,
1028 x_index => l_index,
1029 x_return_status => l_return_status);
1030
1031 OE_DEBUG_PUB.ADD('Update_Global Return Status from OE_LOT_SERIAL_UTIL.insert_row is: ' || l_return_status);
1032 OE_DEBUG_PUB.ADD('returned index is: ' || l_index ,1);
1033
1034
1035 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1036 OE_DEBUG_PUB.ADD('EVENT NOTIFY - Unexpected Error');
1037 OE_DEBUG_PUB.ADD('Exiting OE_LOT_SERIAL_UTIL.INSERT_ROW', 1);
1038 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1039 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1040 OE_DEBUG_PUB.ADD('Update_Global_Picture Error in OE_LOT_SERIAL_UTIL.Insert_row');
1041 OE_DEBUG_PUB.ADD('Exiting OE_LOT_SERIAL_UTIL.INSERT_ROW', 1);
1042 RAISE FND_API.G_EXC_ERROR;
1043 END IF;
1044
1045 -- notification framework end
1046 END IF; /* code set is pack H or higher */
1047 /* jolin end */
1048
1049 EXCEPTION
1050
1051 WHEN OTHERS THEN
1052
1053 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1054 THEN
1055 OE_MSG_PUB.Add_Exc_Msg
1056 ( G_PKG_NAME
1057 , 'Insert_Row'
1058 );
1059 END IF;
1060
1061 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1062
1063 END Insert_Row;
1064
1065 /* Function Is_Last_In_Line_Set: to check if there is any other line that is in the same
1066 * line set as the current line, pass in line id and return boolean
1067 */
1068
1069 FUNCTION Is_Last_In_Line_Set(p_line_id NUMBER)
1070 RETURN BOOLEAN
1071 IS
1072 l_line_rec oe_order_pub.line_rec_type;
1073 l_line_set_id NUMBER;
1074 dummy NUMBER;
1075 BEGIN
1076
1077 oe_line_util.query_row(p_line_id=>p_line_id, x_line_rec=>l_line_rec);
1078
1079 l_line_set_id := l_line_rec.line_set_id;
1080
1081 IF (l_line_rec.line_set_id IS NOT NULL AND
1082 l_line_rec.line_set_id <> fnd_api.g_miss_num)
1083 THEN
1084
1085 BEGIN
1086 SELECT 1 INTO dummy
1087 FROM oe_order_lines
1088 WHERE line_set_id = l_line_set_id
1089 AND line_id <> p_line_id;
1090 EXCEPTION
1091 WHEN others THEN
1092 RETURN TRUE;
1093 END;
1094 RETURN FALSE;
1095 ELSE
1096 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1097 END IF;
1098
1099 END Is_Last_In_Line_Set;
1100
1101 -- Procedure Delete_Row
1102
1103 PROCEDURE Delete_Row
1104 ( p_lot_serial_id IN NUMBER := fnd_api.g_miss_num
1105 , p_line_id IN NUMBER := fnd_api.g_miss_num
1106
1107 )
1108 IS
1109 l_line_rec OE_ORDER_PUB.line_rec_type;
1110 /* jolin start*/
1111 -- added for notification framework
1112 l_old_lot_serial_rec OE_Order_PUB.Lot_Serial_Rec_Type;
1113 l_new_lot_serial_rec OE_Order_PUB.Lot_Serial_Rec_Type;
1114 l_index NUMBER;
1115 l_return_status VARCHAR2(1);
1116
1117 CURSOR line_set IS
1118 SELECT lot_serial_id
1119 FROM OE_LOT_SERIAL_NUMBERS
1120 WHERE line_set_id = l_line_rec.line_set_id;
1121
1122 CURSOR lot_serial IS
1123 SELECT lot_serial_id
1124 FROM OE_LOT_SERIAL_NUMBERS
1125 WHERE line_id = p_line_id;
1126
1127 /* jolin end*/
1128
1129
1130 BEGIN
1131
1132 oe_debug_pub.add('Entering OE_LOT_SERIAL_UTIL.DELETE_ROW', 1);
1133 IF (p_line_id <> FND_API.G_MISS_NUM)
1134 THEN
1135
1136 OE_LINE_UTIL.Query_Row(p_line_id=>p_line_id,x_line_rec=>l_line_rec);
1137
1138 IF (l_line_rec.line_category_code = OE_GLOBALS.G_RETURN_CATEGORY_CODE)
1139 THEN
1140 IF (l_line_rec.line_set_id IS NULL OR
1141 l_line_rec.line_set_id = FND_API.G_MISS_NUM)
1142 THEN
1143 /* jolin start*/
1144 IF OE_CODE_CONTROL.CODE_RELEASE_LEVEL >= '110508' THEN
1145 --added for notification framework to update global picture for lot serials for this line_id
1146
1147 oe_debug_pub.add('JPN: Line ID' || p_line_id);
1148 FOR l_lots IN lot_serial
1149 LOOP
1150 --query lot serial record, then call notification framework to update global picture.
1151
1152 OE_LOT_SERIAL_UTIL.Query_Row(p_lot_serial_id => l_lots.lot_serial_id,
1153 x_lot_serial_rec =>l_old_lot_serial_rec);
1154
1155 oe_debug_pub.add('in delete row, lot_serial_id= '|| l_lots.lot_serial_id , 1);
1156
1157 /* Set the operation on the record so that globals are updated as well */
1158 l_new_lot_serial_rec.operation := OE_GLOBALS.G_OPR_DELETE;
1159 l_new_lot_serial_rec.lot_serial_id :=l_lots.lot_serial_id;
1160
1161 OE_ORDER_UTIL.Update_Global_Picture(
1162 p_Upd_New_Rec_If_Exists => True,
1163 p_lot_serial_rec => l_new_lot_serial_rec,
1164 p_old_lot_serial_rec => l_old_lot_serial_rec,
1165 p_lot_serial_id => l_lots.lot_serial_id,
1166 x_index => l_index,
1167 x_return_status => l_return_status);
1168
1169 OE_DEBUG_PUB.ADD('Update_Global Return Status from OE_LOT_SERIAL_UTIL.delete_row' ||
1170 ' for deleting line set lot_serial line is: ' || l_return_status);
1171
1172 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1173 OE_DEBUG_PUB.ADD('EVENT NOTIFY - Unexpected Error');
1174 OE_DEBUG_PUB.ADD('Exiting OE_LOT_SERIAL_UTIL.DELETE_ROW', 1);
1175 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1176 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1177 OE_DEBUG_PUB.ADD('Update_Global_Picture Error in OE_LOT_SERIAL_UTIL.Delete_row');
1178 OE_DEBUG_PUB.ADD('Exiting OE_LOT_SERIAL_UTIL.DELETE_ROW', 1);
1179 RAISE FND_API.G_EXC_ERROR;
1180 END IF;
1181
1182 END LOOP;
1183 -- notification framework end
1184 END IF; /* code set is pack H or higher */
1185 /* jolin end*/
1186
1187 -- delete lot serial numbers for this line
1188 DELETE FROM oe_lot_serial_numbers
1189 WHERE line_id = p_line_id;
1190
1191 ELSIF (Is_Last_In_Line_Set(p_line_id)) THEN
1192
1193 /* jolin start*/
1194 IF OE_CODE_CONTROL.CODE_RELEASE_LEVEL >= '110508' THEN
1195 --added for notification framework to update global picture for lot serials in line set
1196
1197 FOR l_set IN line_set
1198 LOOP
1199 --query lot serial record, then call notification framework to update global picture.
1200
1201 OE_LOT_SERIAL_UTIL.Query_Row(p_lot_serial_id => l_set.lot_serial_id,
1202 x_lot_serial_rec =>l_old_lot_serial_rec);
1203
1204 oe_debug_pub.add('in delete row, lot_serial_id= '|| l_set.lot_serial_id , 1);
1205
1206 /* Set the operation on the record so that globals are updated as well */
1207 l_new_lot_serial_rec.operation := OE_GLOBALS.G_OPR_DELETE;
1208 l_new_lot_serial_rec.lot_serial_id :=l_set.lot_serial_id;
1209
1210 OE_ORDER_UTIL.Update_Global_Picture(
1211 p_Upd_New_Rec_If_Exists => True,
1212 p_lot_serial_rec => l_new_lot_serial_rec,
1213 p_old_lot_serial_rec => l_old_lot_serial_rec,
1214 p_lot_serial_id => l_set.lot_serial_id,
1215 x_index => l_index,
1216 x_return_status => l_return_status);
1217
1218 OE_DEBUG_PUB.ADD('Update_Global Return Status from OE_LOT_SERIAL_UTIL.delete_row' ||
1219 ' for deleting line set lot_serial line is: ' || l_return_status);
1220
1221 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1222 OE_DEBUG_PUB.ADD('EVENT NOTIFY - Unexpected Error');
1223 OE_DEBUG_PUB.ADD('Exiting OE_LOT_SERIAL_UTIL.DELETE_ROW', 1);
1224 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1225 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1226 OE_DEBUG_PUB.ADD('Update_Global_Picture Error in OE_LOT_SERIAL_UTIL.Delete_row');
1227 OE_DEBUG_PUB.ADD('Exiting OE_LOT_SERIAL_UTIL.DELETE_ROW', 1);
1228 RAISE FND_API.G_EXC_ERROR;
1229 END IF;
1230
1231 END LOOP;
1232 -- notification framework end
1233 END IF; /* code set is pack H or higher */
1234 /* jolin end*/
1235
1236 -- delete lot serial records for this line set
1237 DELETE FROM oe_lot_serial_numbers
1238 WHERE line_set_id = l_line_rec.line_set_id;
1239
1240 ELSE -- there are other lines in this line set, keep lot/serial numbers
1241 NULL;
1242
1243 END IF;
1244 END IF; -- return_line
1245
1246 ELSE -- no line_id
1247 DELETE FROM OE_LOT_SERIAL_NUMBERS
1248 WHERE LOT_SERIAL_ID = p_lot_serial_id;
1249
1250 END IF; -- line_id or lot_serial_id
1251
1252 EXCEPTION
1253
1254 WHEN OTHERS THEN
1255
1256 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1257 THEN
1258 OE_MSG_PUB.Add_Exc_Msg
1259 ( G_PKG_NAME
1260 , 'Delete_Row'
1261 );
1262 END IF;
1263
1264 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1265
1266 END Delete_Row;
1267
1268 -- Procedure Query_Row
1269
1270 PROCEDURE Query_Row
1271 ( p_lot_serial_id IN NUMBER
1272 , x_lot_serial_rec IN OUT NOCOPY OE_Order_PUB.Lot_Serial_Rec_Type
1273 )
1274 IS
1275 l_lot_serial_tbl OE_Order_PUB.Lot_Serial_Tbl_Type;
1276 BEGIN
1277
1278 Query_Rows
1279 ( p_lot_serial_id => p_lot_serial_id
1280 ,x_lot_serial_tbl => l_lot_serial_tbl
1281 );
1282 x_lot_serial_rec := l_lot_serial_tbl(1);
1283
1284 END Query_Row;
1285
1286 -- Procedure Query_Rows
1287
1288 --
1289
1290 PROCEDURE Query_Rows
1291 ( p_lot_serial_id IN NUMBER :=
1292 FND_API.G_MISS_NUM
1293 , p_line_id IN NUMBER :=
1294 FND_API.G_MISS_NUM
1295 , p_line_set_id IN NUMBER :=
1296 FND_API.G_MISS_NUM
1297 , x_lot_serial_tbl IN OUT NOCOPY OE_Order_PUB.Lot_Serial_Tbl_Type
1298 )
1299 IS
1300 l_Lot_Serial_rec OE_Order_PUB.Lot_Serial_Rec_Type;
1301 l_line_rec OE_Order_PUB.Line_Rec_Type;
1302 l_line_id NUMBER := p_line_id;
1303 l_line_set_id NUMBER := p_line_set_id;
1304 l_count NUMBER; --6052770
1305 CURSOR l_Lot_Serial_csr IS
1306 SELECT ATTRIBUTE1
1307 , ATTRIBUTE10
1308 , ATTRIBUTE11
1309 , ATTRIBUTE12
1310 , ATTRIBUTE13
1311 , ATTRIBUTE14
1312 , ATTRIBUTE15
1313 , ATTRIBUTE2
1314 , ATTRIBUTE3
1315 , ATTRIBUTE4
1316 , ATTRIBUTE5
1317 , ATTRIBUTE6
1318 , ATTRIBUTE7
1319 , ATTRIBUTE8
1320 , ATTRIBUTE9
1321 , CONTEXT
1322 , CREATED_BY
1323 , CREATION_DATE
1324 , FROM_SERIAL_NUMBER
1325 , LAST_UPDATED_BY
1326 , LAST_UPDATE_DATE
1327 , LAST_UPDATE_LOGIN
1328 , LINE_ID
1329 , LOT_NUMBER
1330 -- , SUBLOT_NUMBER --OPM 2380194 INVCONV
1331 , LOT_SERIAL_ID
1332 , QUANTITY
1333 , QUANTITY2 --OPM 2380194
1334 , TO_SERIAL_NUMBER
1335 , LINE_SET_ID
1336 , LOCK_CONTROL
1337 FROM OE_LOT_SERIAL_NUMBERS
1338 WHERE ( LOT_SERIAL_ID = p_lot_serial_id
1339 )
1340 OR ( LINE_ID = l_line_id
1341 )
1342 OR ( LINE_SET_ID = l_line_set_id
1343 );
1344
1345 BEGIN
1346
1347 IF
1348 (p_lot_serial_id IS NOT NULL
1349 AND
1350 p_lot_serial_id <> FND_API.G_MISS_NUM)
1351 AND
1352 (p_line_id IS NOT NULL
1353 AND
1354 p_line_id <> FND_API.G_MISS_NUM)
1355 THEN
1356 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1357 THEN
1358 OE_MSG_PUB.Add_Exc_Msg
1359 ( G_PKG_NAME
1360 , 'Query Rows'
1361 , 'Keys are mutually exclusive: lot_serial_id = '|| p_lot_serial_id || ', line_id = '|| p_line_id
1362 );
1363 END IF;
1364
1365 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1366
1367 END IF;
1368
1369 IF
1370 (p_lot_serial_id IS NOT NULL
1371 AND
1372 p_lot_serial_id <> FND_API.G_MISS_NUM)
1373 AND
1374 (p_line_set_id IS NOT NULL
1375 AND
1376 p_line_set_id <> FND_API.G_MISS_NUM)
1377 THEN
1378 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1379 THEN
1380 OE_MSG_PUB.Add_Exc_Msg
1381 ( G_PKG_NAME
1382 , 'Query Rows'
1383 , 'Keys are mutually exclusive: lot_serial_id = '|| p_lot_serial_id || ', line_set_id = '|| p_line_set_id
1384 );
1385 END IF;
1386
1387 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1388
1389 END IF;
1390
1391 IF
1392 (p_line_set_id IS NOT NULL
1393 AND
1394 p_line_set_id <> FND_API.G_MISS_NUM)
1395 AND
1396 (p_line_id IS NOT NULL
1397 AND
1398 p_line_id <> FND_API.G_MISS_NUM)
1399 THEN
1400 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1401 THEN
1402 OE_MSG_PUB.Add_Exc_Msg
1403 ( G_PKG_NAME
1404 , 'Query Rows'
1405 , 'Keys are mutually exclusive: line_set_id = '|| p_line_set_id ||', line_id = '|| p_line_id
1406 );
1407 END IF;
1408
1409 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1410
1411 END IF;
1412
1413 /* After a return line is split, lot serial numbers attach to the
1414 whole line set, so check if this line belongs to a line set
1415 and if yes, query by line set */
1416 IF
1417 (p_line_id IS NOT NULL
1418 AND
1419 p_line_id <> FND_API.G_MISS_NUM)
1420 THEN
1421
1422 -- find line set id and query by line set id
1423 OE_LINE_UTIL.QUERY_ROW(p_line_id=>p_line_id,x_line_rec=>l_line_rec);
1424
1425 IF
1426 (l_line_rec.line_set_id IS NOT NULL
1427 AND
1428 l_line_rec.line_set_id <> FND_API.G_MISS_NUM)
1429 THEN
1430 l_line_set_id := l_line_rec.line_set_id;
1431 l_line_id := FND_API.G_MISS_NUM;
1432 END IF;
1433
1434 END IF;
1435
1436 -- Loop over fetched records
1437
1438 l_count := 1; --6052770
1439 FOR l_implicit_rec IN l_Lot_Serial_csr LOOP
1440
1441 /*6052770
1442 l_Lot_Serial_rec.attribute1 := l_implicit_rec.ATTRIBUTE1;
1443 l_Lot_Serial_rec.attribute10 := l_implicit_rec.ATTRIBUTE10;
1444 l_Lot_Serial_rec.attribute11 := l_implicit_rec.ATTRIBUTE11;
1445 l_Lot_Serial_rec.attribute12 := l_implicit_rec.ATTRIBUTE12;
1446 l_Lot_Serial_rec.attribute13 := l_implicit_rec.ATTRIBUTE13;
1447 l_Lot_Serial_rec.attribute14 := l_implicit_rec.ATTRIBUTE14;
1448 l_Lot_Serial_rec.attribute15 := l_implicit_rec.ATTRIBUTE15;
1449 l_Lot_Serial_rec.attribute2 := l_implicit_rec.ATTRIBUTE2;
1450 l_Lot_Serial_rec.attribute3 := l_implicit_rec.ATTRIBUTE3;
1451 l_Lot_Serial_rec.attribute4 := l_implicit_rec.ATTRIBUTE4;
1452 l_Lot_Serial_rec.attribute5 := l_implicit_rec.ATTRIBUTE5;
1453 l_Lot_Serial_rec.attribute6 := l_implicit_rec.ATTRIBUTE6;
1454 l_Lot_Serial_rec.attribute7 := l_implicit_rec.ATTRIBUTE7;
1455 l_Lot_Serial_rec.attribute8 := l_implicit_rec.ATTRIBUTE8;
1456 l_Lot_Serial_rec.attribute9 := l_implicit_rec.ATTRIBUTE9;
1457 l_Lot_Serial_rec.context := l_implicit_rec.CONTEXT;
1458 l_Lot_Serial_rec.created_by := l_implicit_rec.CREATED_BY;
1459 l_Lot_Serial_rec.creation_date := l_implicit_rec.CREATION_DATE;
1460 l_Lot_Serial_rec.from_serial_number := l_implicit_rec.FROM_SERIAL_NUMBER;
1461 l_Lot_Serial_rec.last_updated_by := l_implicit_rec.LAST_UPDATED_BY;
1462 l_Lot_Serial_rec.last_update_date := l_implicit_rec.LAST_UPDATE_DATE;
1463 l_Lot_Serial_rec.last_update_login := l_implicit_rec.LAST_UPDATE_LOGIN;
1464 l_Lot_Serial_rec.line_id := l_implicit_rec.LINE_ID;
1465 l_Lot_Serial_rec.lot_number := l_implicit_rec.LOT_NUMBER;
1466 -- l_Lot_Serial_rec.sublot_number := l_implicit_rec.SUBLOT_NUMBER; --OPM 2380194 INVCONV
1467 l_Lot_Serial_rec.lot_serial_id := l_implicit_rec.LOT_SERIAL_ID;
1468 l_Lot_Serial_rec.quantity := l_implicit_rec.QUANTITY;
1469 l_Lot_Serial_rec.quantity2 := l_implicit_rec.QUANTITY2; --OPM 2380194
1470 l_Lot_Serial_rec.to_serial_number := l_implicit_rec.TO_SERIAL_NUMBER;
1471 l_Lot_Serial_rec.line_set_id := l_implicit_rec.LINE_SET_ID;
1472 l_Lot_Serial_rec.lock_control := l_implicit_rec.LOCK_CONTROL;
1473 x_Lot_Serial_tbl(x_Lot_Serial_tbl.COUNT + 1) := l_Lot_Serial_rec;
1474 6052770*/
1475
1476 /*6052770*/
1477
1478 x_Lot_Serial_tbl(l_count).attribute1 := l_implicit_rec.ATTRIBUTE1;
1479 x_Lot_Serial_tbl(l_count).attribute10 := l_implicit_rec.ATTRIBUTE10;
1480 x_Lot_Serial_tbl(l_count).attribute11 := l_implicit_rec.ATTRIBUTE11;
1481 x_Lot_Serial_tbl(l_count).attribute12 := l_implicit_rec.ATTRIBUTE12;
1482 x_Lot_Serial_tbl(l_count).attribute13 := l_implicit_rec.ATTRIBUTE13;
1483 x_Lot_Serial_tbl(l_count).attribute14 := l_implicit_rec.ATTRIBUTE14;
1484 x_Lot_Serial_tbl(l_count).attribute15 := l_implicit_rec.ATTRIBUTE15;
1485 x_Lot_Serial_tbl(l_count).attribute2 := l_implicit_rec.ATTRIBUTE2;
1486 x_Lot_Serial_tbl(l_count).attribute3 := l_implicit_rec.ATTRIBUTE3;
1487 x_Lot_Serial_tbl(l_count).attribute4 := l_implicit_rec.ATTRIBUTE4;
1488 x_Lot_Serial_tbl(l_count).attribute5 := l_implicit_rec.ATTRIBUTE5;
1489 x_Lot_Serial_tbl(l_count).attribute6 := l_implicit_rec.ATTRIBUTE6;
1490 x_Lot_Serial_tbl(l_count).attribute7 := l_implicit_rec.ATTRIBUTE7;
1491 x_Lot_Serial_tbl(l_count).attribute8 := l_implicit_rec.ATTRIBUTE8;
1492 x_Lot_Serial_tbl(l_count).attribute9 := l_implicit_rec.ATTRIBUTE9;
1493 x_Lot_Serial_tbl(l_count).context := l_implicit_rec.CONTEXT;
1494 x_Lot_Serial_tbl(l_count).created_by := l_implicit_rec.CREATED_BY;
1495 x_Lot_Serial_tbl(l_count).creation_date := l_implicit_rec.CREATION_DATE;
1496 x_Lot_Serial_tbl(l_count).from_serial_number := l_implicit_rec.FROM_SERIAL_NUMBER;
1497 x_Lot_Serial_tbl(l_count).last_updated_by := l_implicit_rec.LAST_UPDATED_BY;
1498 x_Lot_Serial_tbl(l_count).last_update_date := l_implicit_rec.LAST_UPDATE_DATE;
1499 x_Lot_Serial_tbl(l_count).last_update_login := l_implicit_rec.LAST_UPDATE_LOGIN;
1500 x_Lot_Serial_tbl(l_count).line_id := l_implicit_rec.LINE_ID;
1501 x_Lot_Serial_tbl(l_count).lot_number := l_implicit_rec.LOT_NUMBER;
1502 x_Lot_Serial_tbl(l_count).lot_serial_id := l_implicit_rec.LOT_SERIAL_ID;
1503 x_Lot_Serial_tbl(l_count).quantity := l_implicit_rec.QUANTITY;
1504 x_Lot_Serial_tbl(l_count).to_serial_number := l_implicit_rec.TO_SERIAL_NUMBER;
1505 x_Lot_Serial_tbl(l_count).line_set_id := l_implicit_rec.LINE_SET_ID;
1506 x_Lot_Serial_tbl(l_count).lock_control := l_implicit_rec.LOCK_CONTROL;
1507
1508 l_count := l_count + 1;
1509
1510 /*6052770*/
1511
1512
1513 END LOOP;
1514
1515
1516 -- PK sent and no rows found
1517
1518 IF
1519 (p_lot_serial_id IS NOT NULL
1520 AND
1521 p_lot_serial_id <> FND_API.G_MISS_NUM)
1522 AND
1523 (x_Lot_Serial_tbl.COUNT = 0)
1524 THEN
1525 RAISE NO_DATA_FOUND;
1526 END IF;
1527
1528
1529 EXCEPTION
1530
1531 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1532
1533 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1534
1535 WHEN OTHERS THEN
1536
1537 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1538 THEN
1539 OE_MSG_PUB.Add_Exc_Msg
1540 ( G_PKG_NAME
1541 , 'Query_Rows'
1542 );
1543 END IF;
1544
1545 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1546
1547 END Query_Rows;
1548
1549 -- Procedure lock_Row
1550 --
1551
1552 PROCEDURE Lock_Row
1553 ( x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1554 , p_lot_serial_id IN NUMBER := FND_API.G_MISS_NUM
1555 , p_x_Lot_Serial_rec IN OUT NOCOPY OE_Order_PUB.Lot_Serial_Rec_Type
1556 )
1557 IS
1558
1559 l_lot_serial_id NUMBER;
1560 l_lock_control NUMBER;
1561 BEGIN
1562
1563 oe_debug_pub.add('Entering OE_LOT_SERIAL_UTIL.LOCK_ROW', 1);
1564
1565 SAVEPOINT Lock_Row;
1566
1567 l_lock_control := NULL;
1568
1569 -- Retrieve the primary key.
1570 IF p_lot_serial_id <> FND_API.G_MISS_NUM THEN
1571 l_lot_serial_id := p_lot_serial_id;
1572 ELSE
1573 l_lot_serial_id := p_x_Lot_Serial_rec.lot_serial_id;
1574 l_lock_control := p_x_Lot_Serial_rec.lock_control;
1575 END IF;
1576
1577 SELECT lot_serial_id
1578 INTO l_lot_serial_id
1579 FROM oe_lot_serial_numbers
1580 WHERE lot_serial_id = l_lot_serial_id
1581 FOR UPDATE NOWAIT;
1582
1583 oe_debug_pub.add('selected for update', 1);
1584
1585 OE_Lot_serial_Util.Query_Row
1586 (p_lot_serial_id => l_lot_serial_id
1587 ,x_lot_serial_rec => p_x_lot_serial_rec );
1588
1589
1590 -- If lock_control is passed, then return the locked record.
1591 IF l_lock_control is NULL OR
1592 l_lock_control <> FND_API.G_MISS_NUM THEN
1593
1594 -- Set return status
1595 x_return_status := FND_API.G_RET_STS_SUCCESS;
1596 p_x_lot_serial_rec.return_status := FND_API.G_RET_STS_SUCCESS;
1597
1598 RETURN;
1599
1600 END IF;
1601
1602 -- Row locked. If the whole record is passed, then
1603 -- Compare IN attributes to DB attributes.
1604
1605 IF OE_GLOBALS.Equal(p_x_lot_serial_rec.lock_control,
1606 l_lock_control)
1607 THEN
1608
1609 -- Set return status
1610
1611 x_return_status := FND_API.G_RET_STS_SUCCESS;
1612 p_x_lot_serial_rec.return_status := FND_API.G_RET_STS_SUCCESS;
1613
1614 ELSE
1615
1616 -- Row has changed by another user.
1617
1618 x_return_status := FND_API.G_RET_STS_ERROR;
1619 p_x_lot_serial_rec.return_status := FND_API.G_RET_STS_ERROR;
1620
1621 IF oe_msg_pub.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
1622 THEN
1623
1624 -- Release the lock
1625 ROLLBACK TO Lock_Row;
1626
1627 FND_MESSAGE.SET_NAME('ONT','OE_LOCK_ROW_CHANGED');
1628 oe_msg_pub.Add;
1629
1630 END IF;
1631
1632 END IF;
1633
1634 oe_debug_pub.add('Exiting OE_Lot_serial_UTIL.LOCK_ROW', 1);
1635
1636 EXCEPTION
1637
1638 WHEN NO_DATA_FOUND THEN
1639
1640 x_return_status := FND_API.G_RET_STS_ERROR;
1641 p_x_Lot_Serial_rec.return_status := FND_API.G_RET_STS_ERROR;
1642
1643 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
1644 THEN
1645
1646 FND_MESSAGE.SET_NAME('OE','OE_LOCK_ROW_DELETED');
1647 OE_MSG_PUB.Add;
1648
1649 END IF;
1650 WHEN APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION THEN
1651
1652 x_return_status := FND_API.G_RET_STS_ERROR;
1653 p_x_Lot_Serial_rec.return_status := FND_API.G_RET_STS_ERROR;
1654
1655 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
1656 THEN
1657
1658 FND_MESSAGE.SET_NAME('OE','OE_LOCK_ROW_ALREADY_LOCKED');
1659 OE_MSG_PUB.Add;
1660
1661 END IF;
1662 WHEN OTHERS THEN
1663
1664 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1665 p_x_Lot_Serial_rec.return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1666
1667 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1668 THEN
1669 OE_MSG_PUB.Add_Exc_Msg
1670 ( G_PKG_NAME
1671 , 'Lock_Row'
1672 );
1673 END IF;
1674
1675 END Lock_Row;
1676
1677
1678 PROCEDURE Lock_Rows
1679 ( p_lot_serial_id IN NUMBER :=
1680 FND_API.G_MISS_NUM
1681 , p_line_id IN NUMBER :=
1682 FND_API.G_MISS_NUM
1683 , x_lot_serial_tbl OUT NOCOPY OE_Order_PUB.Lot_serial_Tbl_Type
1684 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1685 )
1686 IS
1687 CURSOR lock_lot_serial(p_line_id NUMBER) IS
1688 SELECT lot_serial_id
1689 FROM oe_lot_serial_numbers
1690 WHERE line_id = p_line_id
1691 FOR UPDATE NOWAIT;
1692
1693 l_lot_serial_id NUMBER;
1694 BEGIN
1695
1696 oe_debug_pub.add('entering oe_lot_serial_util.lock_rows', 1);
1697
1698 IF (p_lot_serial_id IS NOT NULL AND
1699 p_lot_serial_id <> FND_API.G_MISS_NUM) AND
1700 (p_line_id IS NOT NULL AND
1701 p_line_id <> FND_API.G_MISS_NUM)
1702 THEN
1703 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1704 THEN
1705 OE_MSG_PUB.Add_Exc_Msg
1706 ( G_PKG_NAME
1707 , 'Lock Rows'
1708 , 'Keys are mutually exclusive: lot_serial_id = '||
1709 p_lot_serial_id || ', line_id = '|| p_line_id );
1710 END IF;
1711
1712 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1713
1714 END IF;
1715
1716 IF p_lot_serial_id <> FND_API.G_MISS_NUM THEN
1717
1718 SELECT lot_serial_id
1719 INTO l_lot_serial_id
1720 FROM OE_LOT_SERIAL_NUMBERS
1721 WHERE lot_serial_id = p_lot_serial_id
1722 FOR UPDATE NOWAIT;
1723
1724 END IF;
1725
1726 -- people should not pass in null line_id unnecessarily,
1727 -- if they already passed in lot_serial_id.
1728
1729 BEGIN
1730
1731 IF p_line_id <> FND_API.G_MISS_NUM THEN
1732
1733 SAVEPOINT LOCK_ROWS;
1734 OPEN lock_lot_serial(p_line_id);
1735
1736 LOOP
1737 FETCH lock_lot_serial INTO l_lot_serial_id;
1738 EXIT WHEN lock_lot_serial%NOTFOUND;
1739 END LOOP;
1740
1741 CLOSE lock_lot_serial;
1742
1743 END IF;
1744
1745 EXCEPTION
1746 WHEN OTHERS THEN
1747 ROLLBACK TO LOCK_ROWS;
1748
1749 IF lock_lot_serial%ISOPEN THEN
1750 CLOSE lock_lot_serial;
1751 END IF;
1752
1753 RAISE;
1754 END;
1755
1756 -- locked all
1757
1758 OE_Lot_serial_Util.Query_Rows
1759 (p_lot_serial_id => p_lot_serial_id
1760 ,p_line_id => p_line_id
1761 ,x_lot_serial_tbl => x_lot_serial_tbl
1762 );
1763
1764 x_return_status := FND_API.G_RET_STS_SUCCESS;
1765
1766 oe_debug_pub.add('exiting oe_lot_serial_util.lock_rows', 1);
1767
1768 EXCEPTION
1769 WHEN NO_DATA_FOUND THEN
1770
1771 x_return_status := FND_API.G_RET_STS_ERROR;
1772
1773 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
1774 THEN
1775 fnd_message.set_name('ONT','OE_LOCK_ROW_DELETED');
1776 OE_MSG_PUB.Add;
1777 END IF;
1778
1779 WHEN APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION THEN
1780
1781 x_return_status := FND_API.G_RET_STS_ERROR;
1782 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
1783 THEN
1784 fnd_message.set_name('ONT','OE_LOCK_ROW_ALREADY_LOCKED');
1785 OE_MSG_PUB.Add;
1786 END IF;
1787
1788 WHEN OTHERS THEN
1789
1790 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1791
1792 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1793 THEN
1794 OE_MSG_PUB.Add_Exc_Msg
1795 ( G_PKG_NAME
1796 , 'Lock_Rows'
1797 );
1798 END IF;
1799
1800 END Lock_Rows;
1801
1802
1803 -- Function Get_Values
1804
1805 FUNCTION Get_Values
1806 ( p_Lot_Serial_rec IN OE_Order_PUB.Lot_Serial_Rec_Type
1807 , p_old_Lot_Serial_rec IN OE_Order_PUB.Lot_Serial_Rec_Type :=
1808 OE_Order_PUB.G_MISS_LOT_SERIAL_REC
1809 ) RETURN OE_Order_PUB.Lot_Serial_Val_Rec_Type
1810 IS
1811 l_Lot_Serial_val_rec OE_Order_PUB.Lot_Serial_Val_Rec_Type;
1812 BEGIN
1813
1814 NULL;
1815
1816 RETURN l_Lot_Serial_val_rec;
1817
1818 END Get_Values;
1819
1820 -- Function Get_Ids
1821
1822 PROCEDURE Get_Ids
1823 ( p_x_Lot_Serial_rec IN OUT NOCOPY OE_Order_PUB.Lot_Serial_Rec_Type
1824 , p_Lot_Serial_val_rec IN OE_Order_PUB.Lot_Serial_Val_Rec_Type
1825 )
1826 IS
1827 BEGIN
1828
1829 -- initialize return_status.
1830
1831 p_x_Lot_Serial_rec.return_status := FND_API.G_RET_STS_SUCCESS;
1832
1833 END Get_Ids;
1834
1835 END OE_Lot_Serial_Util;