[Home] [Help]
PACKAGE BODY: APPS.WIP_SERIAL_NUMBER_CLEANUP
Source
1 PACKAGE BODY WIP_SERIAL_NUMBER_CLEANUP AS
2 /* $Header: wipsnclb.pls 115.7 2002/12/03 12:05:51 simishra ship $ */
3
4 procedure fetch_and_delete(
5 p_grp_id in number,
6 p_status in number,
7 p_serials in out nocopy mtl_serial_numbers_rec) is
8
9 i number := 0;
10
11 cursor get_serials(
12 c_grp_id number,
13 c_status number) is
14 select
15 INVENTORY_ITEM_ID,
16 SERIAL_NUMBER,
17 LAST_UPDATE_DATE,
18 LAST_UPDATED_BY,
19 CREATION_DATE,
20 CREATED_BY,
21 LAST_UPDATE_LOGIN,
22 REQUEST_ID,
23 PROGRAM_APPLICATION_ID,
24 PROGRAM_ID,
25 PROGRAM_UPDATE_DATE,
26 INITIALIZATION_DATE,
27 COMPLETION_DATE,
28 SHIP_DATE,
29 CURRENT_STATUS,
30 REVISION,
31 LOT_NUMBER,
32 FIXED_ASSET_TAG,
33 RESERVED_ORDER_ID,
34 PARENT_ITEM_ID,
35 PARENT_SERIAL_NUMBER,
36 ORIGINAL_WIP_ENTITY_ID,
37 ORIGINAL_UNIT_VENDOR_ID,
38 VENDOR_SERIAL_NUMBER,
39 VENDOR_LOT_NUMBER,
40 LAST_TXN_SOURCE_TYPE_ID,
41 LAST_TRANSACTION_ID,
42 LAST_RECEIPT_ISSUE_TYPE,
43 LAST_TXN_SOURCE_NAME,
44 LAST_TXN_SOURCE_ID,
45 DESCRIPTIVE_TEXT,
46 CURRENT_SUBINVENTORY_CODE,
47 CURRENT_LOCATOR_ID,
48 CURRENT_ORGANIZATION_ID,
49 ATTRIBUTE_CATEGORY,
50 ATTRIBUTE1,
51 ATTRIBUTE2,
52 ATTRIBUTE3,
53 ATTRIBUTE4,
54 ATTRIBUTE5,
55 ATTRIBUTE6,
56 ATTRIBUTE7,
57 ATTRIBUTE8,
58 ATTRIBUTE9,
59 ATTRIBUTE10,
60 ATTRIBUTE11,
61 ATTRIBUTE12,
62 ATTRIBUTE13,
63 ATTRIBUTE14,
64 ATTRIBUTE15,
65 GROUP_MARK_ID,
66 LINE_MARK_ID,
67 LOT_LINE_MARK_ID
68 from mtl_serial_numbers
69 where group_mark_id = c_grp_id
70 and current_status = c_status;
71
72 serial_rec get_serials%rowtype;
73 begin
74 -- initialize
75 if (p_serials.numrecs is NULL) then
76 -- p_serials is empty
77 p_serials.numrecs := i;
78 else
79 -- p_serials already has records
80 i := p_serials.numrecs;
81 end if;
82
83 open get_serials(
84 c_grp_id => p_grp_id,
85 c_status => p_status);
86
87 loop
88 fetch get_serials into serial_rec;
89
90 exit when (get_serials%NOTFOUND);
91
92 i := i + 1;
93 p_serials.numrecs := i;
94 p_serials.INVENTORY_ITEM_ID(i) := serial_rec.INVENTORY_ITEM_ID;
95 p_serials.SERIAL_NUMBER(i) := serial_rec.SERIAL_NUMBER;
96 p_serials.LAST_UPDATE_DATE(i) := serial_rec.LAST_UPDATE_DATE;
97 p_serials.LAST_UPDATED_BY(i) := serial_rec.LAST_UPDATED_BY;
98 p_serials.CREATION_DATE(i) := serial_rec.CREATION_DATE;
99 p_serials.CREATED_BY(i) := serial_rec.CREATED_BY;
100 p_serials.LAST_UPDATE_LOGIN(i) := serial_rec.LAST_UPDATE_LOGIN;
101 p_serials.REQUEST_ID(i) := serial_rec.REQUEST_ID;
102 p_serials.PROGRAM_APPLICATION_ID(i) := serial_rec.PROGRAM_APPLICATION_ID;
103 p_serials.PROGRAM_ID(i) := serial_rec.PROGRAM_ID;
104 p_serials.PROGRAM_UPDATE_DATE(i) := serial_rec.PROGRAM_UPDATE_DATE;
105 p_serials.INITIALIZATION_DATE(i) := serial_rec.INITIALIZATION_DATE;
106 p_serials.COMPLETION_DATE(i) := serial_rec.COMPLETION_DATE;
107 p_serials.SHIP_DATE(i) := serial_rec.SHIP_DATE;
108 p_serials.CURRENT_STATUS(i) := serial_rec.CURRENT_STATUS;
109 p_serials.REVISION(i) := serial_rec.REVISION;
110 p_serials.LOT_NUMBER(i) := serial_rec.LOT_NUMBER;
111 p_serials.FIXED_ASSET_TAG(i) := serial_rec.FIXED_ASSET_TAG;
112 p_serials.RESERVED_ORDER_ID(i) := serial_rec.RESERVED_ORDER_ID;
113 p_serials.PARENT_ITEM_ID(i) := serial_rec.PARENT_ITEM_ID;
114 p_serials.PARENT_SERIAL_NUMBER(i) := serial_rec.PARENT_SERIAL_NUMBER;
115 p_serials.ORIGINAL_WIP_ENTITY_ID(i) := serial_rec.ORIGINAL_WIP_ENTITY_ID;
116 p_serials.ORIGINAL_UNIT_VENDOR_ID(i) := serial_rec.ORIGINAL_UNIT_VENDOR_ID;
117 p_serials.VENDOR_SERIAL_NUMBER(i) := serial_rec.VENDOR_SERIAL_NUMBER;
118 p_serials.VENDOR_LOT_NUMBER(i) := serial_rec.VENDOR_LOT_NUMBER;
119 p_serials.LAST_TXN_SOURCE_TYPE_ID(i) := serial_rec.LAST_TXN_SOURCE_TYPE_ID;
120 p_serials.LAST_TRANSACTION_ID(i) := serial_rec.LAST_TRANSACTION_ID;
121 p_serials.LAST_RECEIPT_ISSUE_TYPE(i) := serial_rec.LAST_RECEIPT_ISSUE_TYPE;
122 p_serials.LAST_TXN_SOURCE_NAME(i) := serial_rec.LAST_TXN_SOURCE_NAME;
123 p_serials.LAST_TXN_SOURCE_ID(i) := serial_rec.LAST_TXN_SOURCE_ID;
124 p_serials.DESCRIPTIVE_TEXT(i) := serial_rec.DESCRIPTIVE_TEXT;
125 p_serials.CURRENT_SUBINVENTORY_CODE(i) := serial_rec.CURRENT_SUBINVENTORY_CODE;
126 p_serials.CURRENT_LOCATOR_ID(i) := serial_rec.CURRENT_LOCATOR_ID;
127 p_serials.CURRENT_ORGANIZATION_ID(i) := serial_rec.CURRENT_ORGANIZATION_ID;
128 p_serials.ATTRIBUTE_CATEGORY(i) := serial_rec.ATTRIBUTE_CATEGORY;
129 p_serials.ATTRIBUTE1(i) := serial_rec.ATTRIBUTE1;
130 p_serials.ATTRIBUTE2(i) := serial_rec.ATTRIBUTE2;
131 p_serials.ATTRIBUTE3(i) := serial_rec.ATTRIBUTE3;
132 p_serials.ATTRIBUTE4(i) := serial_rec.ATTRIBUTE4;
133 p_serials.ATTRIBUTE5(i) := serial_rec.ATTRIBUTE5;
134 p_serials.ATTRIBUTE6(i) := serial_rec.ATTRIBUTE6;
135 p_serials.ATTRIBUTE7(i) := serial_rec.ATTRIBUTE7;
136 p_serials.ATTRIBUTE8(i) := serial_rec.ATTRIBUTE8;
137 p_serials.ATTRIBUTE9(i) := serial_rec.ATTRIBUTE9;
138 p_serials.ATTRIBUTE10(i) := serial_rec.ATTRIBUTE10;
139 p_serials.ATTRIBUTE11(i) := serial_rec.ATTRIBUTE11;
140 p_serials.ATTRIBUTE12(i) := serial_rec.ATTRIBUTE12;
141 p_serials.ATTRIBUTE13(i) := serial_rec.ATTRIBUTE13;
142 p_serials.ATTRIBUTE14(i) := serial_rec.ATTRIBUTE14;
143 p_serials.ATTRIBUTE15(i) := serial_rec.ATTRIBUTE15;
144 p_serials.GROUP_MARK_ID(i) := serial_rec.GROUP_MARK_ID;
145 p_serials.LINE_MARK_ID(i) := serial_rec.LINE_MARK_ID;
146 p_serials.LOT_LINE_MARK_ID(i) := serial_rec.LOT_LINE_MARK_ID;
147 end loop;
148
149 close get_serials;
150
151 if (p_serials.numrecs > 0) then
152 delete mtl_serial_numbers
153 where group_mark_id = p_grp_id
154 and current_status = p_status;
155 end if;
156 end fetch_and_delete;
157
158 procedure insert_rows(
159 p_serials in mtl_serial_numbers_rec) is
160 i number := 1;
161 begin
162 while (i <= nvl(p_serials.numrecs, 0)) loop
163 insert into mtl_serial_numbers (
164 INVENTORY_ITEM_ID,
165 SERIAL_NUMBER,
166 LAST_UPDATE_DATE,
167 LAST_UPDATED_BY,
168 CREATION_DATE,
169 CREATED_BY,
170 LAST_UPDATE_LOGIN,
171 REQUEST_ID,
172 PROGRAM_APPLICATION_ID,
173 PROGRAM_ID,
174 PROGRAM_UPDATE_DATE,
175 INITIALIZATION_DATE,
176 COMPLETION_DATE,
177 SHIP_DATE,
178 CURRENT_STATUS,
179 REVISION,
180 LOT_NUMBER,
181 FIXED_ASSET_TAG,
182 RESERVED_ORDER_ID,
183 PARENT_ITEM_ID,
184 PARENT_SERIAL_NUMBER,
185 ORIGINAL_WIP_ENTITY_ID,
186 ORIGINAL_UNIT_VENDOR_ID,
187 VENDOR_SERIAL_NUMBER,
188 VENDOR_LOT_NUMBER,
189 LAST_TXN_SOURCE_TYPE_ID,
190 LAST_TRANSACTION_ID,
191 LAST_RECEIPT_ISSUE_TYPE,
192 LAST_TXN_SOURCE_NAME,
193 LAST_TXN_SOURCE_ID,
194 DESCRIPTIVE_TEXT,
195 CURRENT_SUBINVENTORY_CODE,
196 CURRENT_LOCATOR_ID,
197 CURRENT_ORGANIZATION_ID,
198 ATTRIBUTE_CATEGORY,
199 ATTRIBUTE1,
200 ATTRIBUTE2,
201 ATTRIBUTE3,
202 ATTRIBUTE4,
203 ATTRIBUTE5,
204 ATTRIBUTE6,
205 ATTRIBUTE7,
206 ATTRIBUTE8,
207 ATTRIBUTE9,
208 ATTRIBUTE10,
209 ATTRIBUTE11,
210 ATTRIBUTE12,
211 ATTRIBUTE13,
212 ATTRIBUTE14,
213 ATTRIBUTE15,
214 GROUP_MARK_ID,
215 LINE_MARK_ID,
216 LOT_LINE_MARK_ID
217 ) values (
218 p_serials.INVENTORY_ITEM_ID(i),
219 p_serials.SERIAL_NUMBER(i),
220 p_serials.LAST_UPDATE_DATE(i),
221 p_serials.LAST_UPDATED_BY(i),
222 p_serials.CREATION_DATE(i),
223 p_serials.CREATED_BY(i),
224 p_serials.LAST_UPDATE_LOGIN(i),
225 p_serials.REQUEST_ID(i),
226 p_serials.PROGRAM_APPLICATION_ID(i),
227 p_serials.PROGRAM_ID(i),
228 p_serials.PROGRAM_UPDATE_DATE(i),
229 p_serials.INITIALIZATION_DATE(i),
230 p_serials.COMPLETION_DATE(i),
231 p_serials.SHIP_DATE(i),
232 p_serials.CURRENT_STATUS(i),
233 p_serials.REVISION(i),
234 p_serials.LOT_NUMBER(i),
235 p_serials.FIXED_ASSET_TAG(i),
236 p_serials.RESERVED_ORDER_ID(i),
237 p_serials.PARENT_ITEM_ID(i),
238 p_serials.PARENT_SERIAL_NUMBER(i),
239 p_serials.ORIGINAL_WIP_ENTITY_ID(i),
240 p_serials.ORIGINAL_UNIT_VENDOR_ID(i),
241 p_serials.VENDOR_SERIAL_NUMBER(i),
242 p_serials.VENDOR_LOT_NUMBER(i),
243 p_serials.LAST_TXN_SOURCE_TYPE_ID(i),
244 p_serials.LAST_TRANSACTION_ID(i),
245 p_serials.LAST_RECEIPT_ISSUE_TYPE(i),
246 p_serials.LAST_TXN_SOURCE_NAME(i),
247 p_serials.LAST_TXN_SOURCE_ID(i),
248 p_serials.DESCRIPTIVE_TEXT(i),
249 p_serials.CURRENT_SUBINVENTORY_CODE(i),
250 p_serials.CURRENT_LOCATOR_ID(i),
251 p_serials.CURRENT_ORGANIZATION_ID(i),
252 p_serials.ATTRIBUTE_CATEGORY(i),
253 p_serials.ATTRIBUTE1(i),
254 p_serials.ATTRIBUTE2(i),
255 p_serials.ATTRIBUTE3(i),
256 p_serials.ATTRIBUTE4(i),
257 p_serials.ATTRIBUTE5(i),
258 p_serials.ATTRIBUTE6(i),
259 p_serials.ATTRIBUTE7(i),
260 p_serials.ATTRIBUTE8(i),
261 p_serials.ATTRIBUTE9(i),
262 p_serials.ATTRIBUTE10(i),
263 p_serials.ATTRIBUTE11(i),
264 p_serials.ATTRIBUTE12(i),
265 p_serials.ATTRIBUTE13(i),
266 p_serials.ATTRIBUTE14(i),
267 p_serials.ATTRIBUTE15(i),
268 p_serials.GROUP_MARK_ID(i),
269 p_serials.LINE_MARK_ID(i),
270 p_serials.LOT_LINE_MARK_ID(i)
271 );
272
273 i := i + 1;
274 end loop;
275 end insert_rows;
276
277 procedure fetch_and_unmark(
278 p_hdr_id in number,
279 p_serials in out nocopy mtl_serial_numbers_mark_rec) is
280 i number := 0;
281
282 cursor get_serials(c_hdr_id number) is
283 select
284 SERIAL_NUMBER,
285 INVENTORY_ITEM_ID,
286 GROUP_MARK_ID,
287 LINE_MARK_ID,
288 LOT_LINE_MARK_ID
289 from mtl_serial_numbers
290 where group_mark_id = c_hdr_id;
291
292 serial_rec get_serials%rowtype;
293 begin
294 -- initialize
295 if (p_serials.numrecs is NULL) then
296 -- p_serials is empty
297 p_serials.numrecs := i;
298 else
299 -- p_serials already has records
300 i := p_serials.numrecs;
301 end if;
302
303 open get_serials(c_hdr_id => p_hdr_id);
304
305 loop
306 fetch get_serials into serial_rec;
307
308 exit when (get_serials%NOTFOUND);
309
310 i := i + 1;
311 p_serials.numrecs := i;
312 p_serials.SERIAL_NUMBER(i) := serial_rec.SERIAL_NUMBER;
313 p_serials.INVENTORY_ITEM_ID(i) := serial_rec.INVENTORY_ITEM_ID;
314 p_serials.GROUP_MARK_ID(i) := serial_rec.GROUP_MARK_ID;
315 p_serials.LINE_MARK_ID(i) := serial_rec.LINE_MARK_ID;
316 p_serials.LOT_LINE_MARK_ID(i) := serial_rec.LOT_LINE_MARK_ID;
317 end loop;
318
319 close get_serials;
320
321 if (p_serials.numrecs > 0) then
322 update mtl_serial_numbers
323 set group_mark_id = null,
324 line_mark_id = null,
325 lot_line_mark_id = null
326 where group_mark_id = p_hdr_id;
327 end if;
328 end fetch_and_unmark;
329
330 procedure mark(
331 p_serials in mtl_serial_numbers_mark_rec,
332 p_retcode out nocopy number) is
333 i number := 1;
334
335 -- cursor to lock rows
336 cursor lock_rows(
337 c_item_id number,
338 c_serial varchar2) is
339 select 'x'
340 from mtl_serial_numbers
341 where inventory_item_id = c_item_id
342 and serial_number = c_serial
343 for update nowait;
344
345 x_dummy varchar2(1);
346 begin
347 p_retcode := SUCCESS;
348
349 savepoint mark_serials;
350
351 while (i <= nvl(p_serials.numrecs, 0)) loop
352 /* When remarking only this session should be transacting these
353 serial numbers. If cannot update, then some other session has
354 locked this serial number => error out. */
355 open lock_rows(
356 c_item_id => p_serials.INVENTORY_ITEM_ID(i),
357 c_serial => p_serials.SERIAL_NUMBER(i));
358 fetch lock_rows into x_dummy;
359 close lock_rows;
360
361 update mtl_serial_numbers
362 set group_mark_id = p_serials.GROUP_MARK_ID(i),
363 line_mark_id = p_serials.LINE_MARK_ID(i),
364 lot_line_mark_id = p_serials.LOT_LINE_MARK_ID(i)
365 where inventory_item_id = p_serials.INVENTORY_ITEM_ID(i)
366 and serial_number = p_serials.SERIAL_NUMBER(i);
367
368 i := i + 1;
369 end loop;
370
371 exception
372 when others then
373 /* cannot lock serial numbers should exception to here */
374 /* as well as other types of exceptions */
375 close lock_rows;
376 rollback to mark_serials;
377 p_retcode := FAILURE;
378 end mark;
379
380 END WIP_SERIAL_NUMBER_CLEANUP;