1 PACKAGE WIP_SERIAL_NUMBER_CLEANUP AUTHID CURRENT_USER AS
2 /* $Header: wipsncls.pls 115.6 2002/11/29 14:28:36 rmahidha ship $ */
3
4 TYPE INVENTORY_ITEM_ID_T IS TABLE OF
5 MTL_SERIAL_NUMBERS.INVENTORY_ITEM_ID%TYPE
6 INDEX BY BINARY_INTEGER;
7
8 TYPE SERIAL_NUMBER_T IS TABLE OF
9 MTL_SERIAL_NUMBERS.SERIAL_NUMBER%TYPE
10 INDEX BY BINARY_INTEGER;
11
12 TYPE LAST_UPDATE_DATE_T IS TABLE OF
13 MTL_SERIAL_NUMBERS.LAST_UPDATE_DATE%TYPE
14 INDEX BY BINARY_INTEGER;
15
16 TYPE LAST_UPDATED_BY_T IS TABLE OF
17 MTL_SERIAL_NUMBERS.LAST_UPDATED_BY%TYPE
18 INDEX BY BINARY_INTEGER;
19
20 TYPE CREATION_DATE_T IS TABLE OF
21 MTL_SERIAL_NUMBERS.CREATION_DATE%TYPE
22 INDEX BY BINARY_INTEGER;
23
24 TYPE CREATED_BY_T IS TABLE OF
25 MTL_SERIAL_NUMBERS.CREATED_BY%TYPE
26 INDEX BY BINARY_INTEGER;
27
28 TYPE LAST_UPDATE_LOGIN_T IS TABLE OF
29 MTL_SERIAL_NUMBERS.LAST_UPDATE_LOGIN%TYPE
30 INDEX BY BINARY_INTEGER;
31
32 TYPE REQUEST_ID_T IS TABLE OF
33 MTL_SERIAL_NUMBERS.REQUEST_ID%TYPE
34 INDEX BY BINARY_INTEGER;
35
36 TYPE PROGRAM_APPLICATION_ID_T IS TABLE OF
37 MTL_SERIAL_NUMBERS.PROGRAM_APPLICATION_ID%TYPE
38 INDEX BY BINARY_INTEGER;
39
40 TYPE PROGRAM_ID_T IS TABLE OF
41 MTL_SERIAL_NUMBERS.PROGRAM_ID%TYPE
42 INDEX BY BINARY_INTEGER;
43
44 TYPE PROGRAM_UPDATE_DATE_T IS TABLE OF
45 MTL_SERIAL_NUMBERS.PROGRAM_UPDATE_DATE%TYPE
46 INDEX BY BINARY_INTEGER;
47
48 TYPE INITIALIZATION_DATE_T IS TABLE OF
49 MTL_SERIAL_NUMBERS.INITIALIZATION_DATE%TYPE
50 INDEX BY BINARY_INTEGER;
51
52 TYPE COMPLETION_DATE_T IS TABLE OF
53 MTL_SERIAL_NUMBERS.COMPLETION_DATE%TYPE
54 INDEX BY BINARY_INTEGER;
55
56 TYPE SHIP_DATE_T IS TABLE OF
57 MTL_SERIAL_NUMBERS.SHIP_DATE%TYPE
58 INDEX BY BINARY_INTEGER;
59
60 TYPE CURRENT_STATUS_T IS TABLE OF
61 MTL_SERIAL_NUMBERS.CURRENT_STATUS%TYPE
62 INDEX BY BINARY_INTEGER;
63
64 TYPE REVISION_T IS TABLE OF
65 MTL_SERIAL_NUMBERS.REVISION%TYPE
66 INDEX BY BINARY_INTEGER;
67
68 TYPE LOT_NUMBER_T IS TABLE OF
69 MTL_SERIAL_NUMBERS.LOT_NUMBER%TYPE
70 INDEX BY BINARY_INTEGER;
71
72 TYPE FIXED_ASSET_TAG_T IS TABLE OF
73 MTL_SERIAL_NUMBERS.FIXED_ASSET_TAG%TYPE
74 INDEX BY BINARY_INTEGER;
75
76 TYPE RESERVED_ORDER_ID_T IS TABLE OF
77 MTL_SERIAL_NUMBERS.RESERVED_ORDER_ID%TYPE
78 INDEX BY BINARY_INTEGER;
79
80 TYPE PARENT_ITEM_ID_T IS TABLE OF
81 MTL_SERIAL_NUMBERS.PARENT_ITEM_ID%TYPE
82 INDEX BY BINARY_INTEGER;
83
84 TYPE PARENT_SERIAL_NUMBER_T IS TABLE OF
85 MTL_SERIAL_NUMBERS.PARENT_SERIAL_NUMBER%TYPE
86 INDEX BY BINARY_INTEGER;
87
88 TYPE ORIGINAL_WIP_ENTITY_ID_T IS TABLE OF
89 MTL_SERIAL_NUMBERS.ORIGINAL_WIP_ENTITY_ID%TYPE
90 INDEX BY BINARY_INTEGER;
91
92 TYPE ORIGINAL_UNIT_VENDOR_ID_T IS TABLE OF
93 MTL_SERIAL_NUMBERS.ORIGINAL_UNIT_VENDOR_ID%TYPE
94 INDEX BY BINARY_INTEGER;
95
96 TYPE VENDOR_SERIAL_NUMBER_T IS TABLE OF
97 MTL_SERIAL_NUMBERS.VENDOR_SERIAL_NUMBER%TYPE
98 INDEX BY BINARY_INTEGER;
99
100 TYPE VENDOR_LOT_NUMBER_T IS TABLE OF
101 MTL_SERIAL_NUMBERS.VENDOR_LOT_NUMBER%TYPE
102 INDEX BY BINARY_INTEGER;
103
104 TYPE LAST_TXN_SOURCE_TYPE_ID_T IS TABLE OF
105 MTL_SERIAL_NUMBERS.LAST_TXN_SOURCE_TYPE_ID%TYPE
106 INDEX BY BINARY_INTEGER;
107
108 TYPE LAST_TRANSACTION_ID_T IS TABLE OF
109 MTL_SERIAL_NUMBERS.LAST_TRANSACTION_ID%TYPE
110 INDEX BY BINARY_INTEGER;
111
112 TYPE LAST_RECEIPT_ISSUE_TYPE_T IS TABLE OF
113 MTL_SERIAL_NUMBERS.LAST_RECEIPT_ISSUE_TYPE%TYPE
114 INDEX BY BINARY_INTEGER;
115
116 TYPE LAST_TXN_SOURCE_NAME_T IS TABLE OF
117 MTL_SERIAL_NUMBERS.LAST_TXN_SOURCE_NAME%TYPE
118 INDEX BY BINARY_INTEGER;
119
120 TYPE LAST_TXN_SOURCE_ID_T IS TABLE OF
121 MTL_SERIAL_NUMBERS.LAST_TXN_SOURCE_ID%TYPE
122 INDEX BY BINARY_INTEGER;
123
124 TYPE DESCRIPTIVE_TEXT_T IS TABLE OF
125 MTL_SERIAL_NUMBERS.DESCRIPTIVE_TEXT%TYPE
126 INDEX BY BINARY_INTEGER;
127
128 TYPE CURRENT_SUBINVENTORY_CODE_T IS TABLE OF
129 MTL_SERIAL_NUMBERS.CURRENT_SUBINVENTORY_CODE%TYPE
130 INDEX BY BINARY_INTEGER;
131
132 TYPE CURRENT_LOCATOR_ID_T IS TABLE OF
133 MTL_SERIAL_NUMBERS.CURRENT_LOCATOR_ID%TYPE
134 INDEX BY BINARY_INTEGER;
135
136 TYPE CURRENT_ORGANIZATION_ID_T IS TABLE OF
137 MTL_SERIAL_NUMBERS.CURRENT_ORGANIZATION_ID%TYPE
138 INDEX BY BINARY_INTEGER;
139
140 TYPE ATTRIBUTE_CATEGORY_T IS TABLE OF
141 MTL_SERIAL_NUMBERS.ATTRIBUTE_CATEGORY%TYPE
142 INDEX BY BINARY_INTEGER;
143
144 TYPE ATTRIBUTE1_T IS TABLE OF
145 MTL_SERIAL_NUMBERS.ATTRIBUTE1%TYPE
146 INDEX BY BINARY_INTEGER;
147
148 TYPE ATTRIBUTE2_T IS TABLE OF
149 MTL_SERIAL_NUMBERS.ATTRIBUTE2%TYPE
150 INDEX BY BINARY_INTEGER;
151
152 TYPE ATTRIBUTE3_T IS TABLE OF
153 MTL_SERIAL_NUMBERS.ATTRIBUTE3%TYPE
154 INDEX BY BINARY_INTEGER;
155
156 TYPE ATTRIBUTE4_T IS TABLE OF
157 MTL_SERIAL_NUMBERS.ATTRIBUTE4%TYPE
158 INDEX BY BINARY_INTEGER;
159
160 TYPE ATTRIBUTE5_T IS TABLE OF
161 MTL_SERIAL_NUMBERS.ATTRIBUTE5%TYPE
162 INDEX BY BINARY_INTEGER;
163
164 TYPE ATTRIBUTE6_T IS TABLE OF
165 MTL_SERIAL_NUMBERS.ATTRIBUTE6%TYPE
166 INDEX BY BINARY_INTEGER;
167
168 TYPE ATTRIBUTE7_T IS TABLE OF
169 MTL_SERIAL_NUMBERS.ATTRIBUTE7%TYPE
170 INDEX BY BINARY_INTEGER;
171
172 TYPE ATTRIBUTE8_T IS TABLE OF
173 MTL_SERIAL_NUMBERS.ATTRIBUTE8%TYPE
174 INDEX BY BINARY_INTEGER;
175
176 TYPE ATTRIBUTE9_T IS TABLE OF
177 MTL_SERIAL_NUMBERS.ATTRIBUTE9%TYPE
178 INDEX BY BINARY_INTEGER;
179
180 TYPE ATTRIBUTE10_T IS TABLE OF
181 MTL_SERIAL_NUMBERS.ATTRIBUTE10%TYPE
182 INDEX BY BINARY_INTEGER;
183
184 TYPE ATTRIBUTE11_T IS TABLE OF
185 MTL_SERIAL_NUMBERS.ATTRIBUTE11%TYPE
186 INDEX BY BINARY_INTEGER;
187
188 TYPE ATTRIBUTE12_T IS TABLE OF
189 MTL_SERIAL_NUMBERS.ATTRIBUTE12%TYPE
190 INDEX BY BINARY_INTEGER;
191
192 TYPE ATTRIBUTE13_T IS TABLE OF
193 MTL_SERIAL_NUMBERS.ATTRIBUTE13%TYPE
194 INDEX BY BINARY_INTEGER;
195
196 TYPE ATTRIBUTE14_T IS TABLE OF
197 MTL_SERIAL_NUMBERS.ATTRIBUTE14%TYPE
198 INDEX BY BINARY_INTEGER;
199
200 TYPE ATTRIBUTE15_T IS TABLE OF
201 MTL_SERIAL_NUMBERS.ATTRIBUTE15%TYPE
202 INDEX BY BINARY_INTEGER;
203
204 TYPE GROUP_MARK_ID_T IS TABLE OF
205 MTL_SERIAL_NUMBERS.GROUP_MARK_ID%TYPE
206 INDEX BY BINARY_INTEGER;
207
208 TYPE LINE_MARK_ID_T IS TABLE OF
209 MTL_SERIAL_NUMBERS.LINE_MARK_ID%TYPE
210 INDEX BY BINARY_INTEGER;
211
212 TYPE LOT_LINE_MARK_ID_T IS TABLE OF
213 MTL_SERIAL_NUMBERS.LOT_LINE_MARK_ID%TYPE
214 INDEX BY BINARY_INTEGER;
215
216 TYPE MTL_SERIAL_NUMBERS_REC IS RECORD (
217 NUMRECS NUMBER,
218 INVENTORY_ITEM_ID INVENTORY_ITEM_ID_T,
219 SERIAL_NUMBER SERIAL_NUMBER_T,
220 LAST_UPDATE_DATE LAST_UPDATE_DATE_T,
221 LAST_UPDATED_BY LAST_UPDATED_BY_T,
222 CREATION_DATE CREATION_DATE_T,
223 CREATED_BY CREATED_BY_T,
224 LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN_T,
225 REQUEST_ID REQUEST_ID_T,
226 PROGRAM_APPLICATION_ID PROGRAM_APPLICATION_ID_T,
227 PROGRAM_ID PROGRAM_ID_T,
228 PROGRAM_UPDATE_DATE PROGRAM_UPDATE_DATE_T,
229 INITIALIZATION_DATE INITIALIZATION_DATE_T,
230 COMPLETION_DATE COMPLETION_DATE_T,
231 SHIP_DATE SHIP_DATE_T,
232 CURRENT_STATUS CURRENT_STATUS_T,
233 REVISION REVISION_T,
234 LOT_NUMBER LOT_NUMBER_T,
235 FIXED_ASSET_TAG FIXED_ASSET_TAG_T,
236 RESERVED_ORDER_ID RESERVED_ORDER_ID_T,
237 PARENT_ITEM_ID PARENT_ITEM_ID_T,
238 PARENT_SERIAL_NUMBER PARENT_SERIAL_NUMBER_T,
239 ORIGINAL_WIP_ENTITY_ID ORIGINAL_WIP_ENTITY_ID_T,
240 ORIGINAL_UNIT_VENDOR_ID ORIGINAL_UNIT_VENDOR_ID_T,
241 VENDOR_SERIAL_NUMBER VENDOR_SERIAL_NUMBER_T,
242 VENDOR_LOT_NUMBER VENDOR_LOT_NUMBER_T,
243 LAST_TXN_SOURCE_TYPE_ID LAST_TXN_SOURCE_TYPE_ID_T,
244 LAST_TRANSACTION_ID LAST_TRANSACTION_ID_T,
245 LAST_RECEIPT_ISSUE_TYPE LAST_RECEIPT_ISSUE_TYPE_T,
246 LAST_TXN_SOURCE_NAME LAST_TXN_SOURCE_NAME_T,
247 LAST_TXN_SOURCE_ID LAST_TXN_SOURCE_ID_T,
248 DESCRIPTIVE_TEXT DESCRIPTIVE_TEXT_T,
249 CURRENT_SUBINVENTORY_CODE CURRENT_SUBINVENTORY_CODE_T,
250 CURRENT_LOCATOR_ID CURRENT_LOCATOR_ID_T,
251 CURRENT_ORGANIZATION_ID CURRENT_ORGANIZATION_ID_T,
252 ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY_T,
253 ATTRIBUTE1 ATTRIBUTE1_T,
254 ATTRIBUTE2 ATTRIBUTE2_T,
255 ATTRIBUTE3 ATTRIBUTE3_T,
256 ATTRIBUTE4 ATTRIBUTE4_T,
257 ATTRIBUTE5 ATTRIBUTE5_T,
258 ATTRIBUTE6 ATTRIBUTE6_T,
259 ATTRIBUTE7 ATTRIBUTE7_T,
260 ATTRIBUTE8 ATTRIBUTE8_T,
261 ATTRIBUTE9 ATTRIBUTE9_T,
262 ATTRIBUTE10 ATTRIBUTE10_T,
263 ATTRIBUTE11 ATTRIBUTE11_T,
264 ATTRIBUTE12 ATTRIBUTE12_T,
265 ATTRIBUTE13 ATTRIBUTE13_T,
266 ATTRIBUTE14 ATTRIBUTE14_T,
267 ATTRIBUTE15 ATTRIBUTE15_T,
268 GROUP_MARK_ID GROUP_MARK_ID_T,
269 LINE_MARK_ID LINE_MARK_ID_T,
270 LOT_LINE_MARK_ID LOT_LINE_MARK_ID_T
271 );
272
273 TYPE MTL_SERIAL_NUMBERS_MARK_REC IS RECORD (
274 NUMRECS NUMBER,
275 SERIAL_NUMBER SERIAL_NUMBER_T,
276 INVENTORY_ITEM_ID INVENTORY_ITEM_ID_T,
277 GROUP_MARK_ID GROUP_MARK_ID_T,
278 LINE_MARK_ID LINE_MARK_ID_T,
279 LOT_LINE_MARK_ID LOT_LINE_MARK_ID_T
280 );
281
282 procedure insert_rows(p_serials in mtl_serial_numbers_rec);
283
284 SUCCESS constant number := 0;
285 FAILURE constant number := -1;
286
287 procedure mark(
288 p_serials in mtl_serial_numbers_mark_rec,
289 p_retcode out NOCOPY number);
290
291 END WIP_SERIAL_NUMBER_CLEANUP;
[Home] [Help]