[Home] [Help]
PACKAGE BODY: APPS.WSH_PACKED_CONTAINERS_PKG
Source
1 PACKAGE BODY WSH_PACKED_CONTAINERS_PKG as
2 /* $Header: WSHPCKHB.pls 115.1 99/07/16 08:19:23 porting s $ */
3
4 PROCEDURE Insert_Row(X_Rowid IN OUT VARCHAR2,
5 X_Container_Id IN OUT NUMBER,
6 X_Delivery_Id NUMBER,
7 X_Container_Inventory_Item_Id NUMBER,
8 X_Master_Container_Id NUMBER,
9 X_Parent_Container_Id NUMBER,
10 X_Quantity NUMBER,
11 X_Sequence_Number NUMBER,
12 X_Parent_Sequence_Number NUMBER,
13 X_Gross_Weight NUMBER,
14 X_Weight_Uom_Code VARCHAR2,
15 X_Volume_Uom_Code VARCHAR2,
16 X_Volume NUMBER,
17 X_Fill_Percent NUMBER,
18 X_Net_Weight NUMBER,
19 X_Organization_Id NUMBER,
20 X_Subinventory VARCHAR2,
21 X_Inventory_Location_Id NUMBER,
22 X_Revision VARCHAR2,
23 X_Lot_Number VARCHAR2,
24 X_Serial_Number VARCHAR2,
25 X_Master_Serial_Number VARCHAR2,
26 X_Inventory_Status VARCHAR2,
27 X_Ra_Interface_Status VARCHAR2,
28 X_Attribute_Category VARCHAR2,
29 X_Attribute1 VARCHAR2,
30 X_Attribute2 VARCHAR2,
31 X_Attribute3 VARCHAR2,
32 X_Attribute4 VARCHAR2,
33 X_Attribute5 VARCHAR2,
34 X_Attribute6 VARCHAR2,
35 X_Attribute7 VARCHAR2,
36 X_Attribute8 VARCHAR2,
37 X_Attribute9 VARCHAR2,
38 X_Attribute10 VARCHAR2,
39 X_Attribute11 VARCHAR2,
40 X_Attribute12 VARCHAR2,
41 X_Attribute13 VARCHAR2,
42 X_Attribute14 VARCHAR2,
43 X_Attribute15 VARCHAR2,
44 X_Creation_Date DATE,
45 X_Created_By NUMBER,
46 X_Last_Update_Date DATE,
47 X_Last_Updated_By NUMBER,
48 X_Last_Update_Login NUMBER
49 ) IS
50 CURSOR C IS SELECT rowid FROM wsh_packed_containers
51 WHERE container_id = X_Container_Id;
52 CURSOR C2 IS SELECT wsh_packed_containers_s.nextval FROM sys.dual;
53 BEGIN
54 IF (X_Container_Id IS NULL) THEN
55 OPEN C2;
56 FETCH C2 INTO X_Container_Id;
57 CLOSE C2;
58 END IF;
59
60 INSERT INTO wsh_packed_containers(
61
62 container_id,
63 delivery_id,
64 container_inventory_item_id,
65 master_container_id,
66 parent_container_id,
67 quantity,
68 sequence_number,
69 parent_sequence_number,
70 gross_weight,
71 weight_uom_code,
72 volume_uom_code,
73 volume,
74 fill_percent,
75 net_weight,
76 organization_id,
77 subinventory,
78 inventory_location_id,
79 revision,
80 lot_number,
81 serial_number,
82 master_serial_number,
83 inventory_status,
84 ra_interface_status,
85 attribute_category,
86 attribute1,
87 attribute2,
88 attribute3,
89 attribute4,
90 attribute5,
91 attribute6,
92 attribute7,
93 attribute8,
94 attribute9,
95 attribute10,
96 attribute11,
97 attribute12,
98 attribute13,
99 attribute14,
100 attribute15,
101 creation_date,
102 created_by,
103 last_update_date,
104 last_updated_by,
105 last_update_login
106 ) VALUES (
107 X_Container_Id,
108 X_Delivery_Id,
109 X_Container_Inventory_Item_Id,
110 X_Master_Container_Id,
111 X_Parent_Container_Id,
112 X_Quantity,
113 X_Sequence_Number,
114 X_Parent_Sequence_Number,
115 X_Gross_Weight,
116 X_Weight_Uom_Code,
117 X_Volume_Uom_Code,
118 X_Volume,
119 X_Fill_Percent,
120 X_Net_Weight,
121 X_Organization_Id,
122 X_Subinventory,
123 X_Inventory_Location_Id,
124 X_Revision,
125 X_Lot_Number,
126 X_Serial_Number,
127 X_Master_Serial_Number,
128 X_Inventory_Status,
129 X_Ra_Interface_Status,
130 X_Attribute_Category,
131 X_Attribute1,
132 X_Attribute2,
133 X_Attribute3,
134 X_Attribute4,
135 X_Attribute5,
136 X_Attribute6,
137 X_Attribute7,
138 X_Attribute8,
139 X_Attribute9,
140 X_Attribute10,
141 X_Attribute11,
142 X_Attribute12,
143 X_Attribute13,
144 X_Attribute14,
145 X_Attribute15,
146 X_Creation_Date,
147 X_Created_By,
148 X_Last_Update_Date,
149 X_Last_Updated_By,
150 X_Last_Update_Login
151 );
152
153 OPEN C;
154 FETCH C INTO X_Rowid;
155 IF (C%NOTFOUND) THEN
156 CLOSE C;
157 RAISE NO_DATA_FOUND;
158 END IF;
159 CLOSE C;
160 END Insert_Row;
161
162
163 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
164 X_Container_Id NUMBER,
165 X_Delivery_Id NUMBER,
166 X_Container_Inventory_Item_Id NUMBER,
167 X_Master_Container_Id NUMBER,
168 X_Parent_Container_Id NUMBER,
169 X_Quantity NUMBER,
170 X_Sequence_Number NUMBER,
171 X_Parent_Sequence_Number NUMBER,
172 X_Gross_Weight NUMBER,
173 X_Weight_Uom_Code VARCHAR2,
174 X_Volume_Uom_Code VARCHAR2,
175 X_Volume NUMBER,
176 X_Fill_Percent NUMBER,
177 X_Net_Weight NUMBER,
178 X_Organization_Id NUMBER,
179 X_Subinventory VARCHAR2,
180 X_Inventory_Location_Id NUMBER,
181 X_Revision VARCHAR2,
182 X_Lot_Number VARCHAR2,
183 X_Serial_Number VARCHAR2,
184 X_Master_Serial_Number VARCHAR2,
185 X_Inventory_Status VARCHAR2,
186 X_Ra_Interface_Status VARCHAR2,
187 X_Attribute_Category VARCHAR2,
188 X_Attribute1 VARCHAR2,
189 X_Attribute2 VARCHAR2,
190 X_Attribute3 VARCHAR2,
191 X_Attribute4 VARCHAR2,
192 X_Attribute5 VARCHAR2,
193 X_Attribute6 VARCHAR2,
194 X_Attribute7 VARCHAR2,
195 X_Attribute8 VARCHAR2,
196 X_Attribute9 VARCHAR2,
197 X_Attribute10 VARCHAR2,
198 X_Attribute11 VARCHAR2,
199 X_Attribute12 VARCHAR2,
200 X_Attribute13 VARCHAR2,
201 X_Attribute14 VARCHAR2,
202 X_Attribute15 VARCHAR2
203 ) IS
204 CURSOR C IS
205 SELECT *
206 FROM wsh_packed_containers
207 WHERE rowid = X_Rowid
208 FOR UPDATE of Container_Id NOWAIT;
209 Recinfo C%ROWTYPE;
210
211
212 BEGIN
213 OPEN C;
214 FETCH C INTO Recinfo;
215 IF (C%NOTFOUND) THEN
216 CLOSE C;
217 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
218 APP_EXCEPTION.Raise_Exception;
219 END IF;
220 CLOSE C;
221 IF (
222
223 (Recinfo.container_id = X_Container_Id)
224 AND (Recinfo.delivery_id = X_Delivery_Id)
225 AND (Recinfo.container_inventory_item_id = X_Container_Inventory_Item_Id)
226 AND (Recinfo.quantity = X_Quantity)
227 AND ( (Recinfo.sequence_number = X_Sequence_Number)
228 OR ( (Recinfo.sequence_number IS NULL)
229 AND (X_Sequence_Number IS NULL)))
230 AND ( (Recinfo.parent_sequence_number = X_Parent_Sequence_Number)
231 OR ( (Recinfo.parent_sequence_number IS NULL)
232 AND (X_Parent_Sequence_Number IS NULL)))
233 AND ( (Recinfo.gross_weight = X_Gross_Weight)
234 OR ( (Recinfo.gross_weight IS NULL)
235 AND (X_Gross_Weight IS NULL)))
236 AND ( (Recinfo.volume = X_Volume)
237 OR ( (Recinfo.volume IS NULL)
238 AND (X_Volume IS NULL)))
239 AND ( (Recinfo.fill_percent = X_Fill_Percent)
240 OR ( (Recinfo.fill_percent IS NULL)
241 AND (X_Fill_Percent IS NULL)))
242 AND ( (Recinfo.net_weight = X_Net_Weight)
243 OR ( (Recinfo.net_weight IS NULL)
244 AND (X_Net_Weight IS NULL)))
245 AND ( (Recinfo.weight_uom_code = X_Weight_Uom_Code)
246 OR ( (Recinfo.weight_uom_code IS NULL)
247 AND (X_Weight_Uom_Code IS NULL)))
248 AND ( (Recinfo.volume_uom_code = X_Volume_Uom_Code)
249 OR ( (Recinfo.volume_uom_code IS NULL)
250 AND (X_Volume_Uom_Code IS NULL)))
251 AND ( (Recinfo.organization_id = X_Organization_Id)
252 OR ( (Recinfo.organization_id IS NULL)
253 AND (X_Organization_Id IS NULL)))
254 AND ( (Recinfo.subinventory = X_Subinventory)
255 OR ( (Recinfo.subinventory IS NULL)
256 AND (X_Subinventory IS NULL)))
257 AND ( (Recinfo.inventory_location_id = X_Inventory_Location_Id)
258 OR ( (Recinfo.inventory_location_id IS NULL)
259 AND (X_Inventory_Location_Id IS NULL)))
260 AND ( (Recinfo.revision = X_Revision)
261 OR ( (Recinfo.revision IS NULL)
262 AND (X_Revision IS NULL)))
263 AND ( (Recinfo.lot_number = X_Lot_Number)
264 OR ( (Recinfo.lot_number IS NULL)
265 AND (X_Lot_Number IS NULL)))
266 AND ( (Recinfo.serial_number = X_Serial_Number)
267 OR ( (Recinfo.serial_number IS NULL)
268 AND (X_Serial_Number IS NULL)))
269 AND ( (Recinfo.master_serial_number = X_Master_Serial_Number)
270 OR ( (Recinfo.master_serial_number IS NULL)
271 AND (X_Master_Serial_Number IS NULL)))
272 AND ( (Recinfo.inventory_status = X_Inventory_Status)
273 OR ( (Recinfo.inventory_status IS NULL)
274 AND (X_Inventory_Status IS NULL)))
275 AND ( (Recinfo.ra_interface_status = X_Ra_Interface_Status)
276 OR ( (Recinfo.ra_interface_status IS NULL)
277 AND (X_Ra_Interface_Status IS NULL)))
278 AND ( (Recinfo.attribute_category = X_Attribute_Category)
279 OR ( (Recinfo.attribute_category IS NULL)
280 AND (X_Attribute_Category IS NULL)))
281 AND ( (Recinfo.attribute1 = X_Attribute1)
282 OR ( (Recinfo.attribute1 IS NULL)
283 AND (X_Attribute1 IS NULL)))
284 AND ( (Recinfo.attribute2 = X_Attribute2)
285 OR ( (Recinfo.attribute2 IS NULL)
286 AND (X_Attribute2 IS NULL)))
287 AND ( (Recinfo.attribute3 = X_Attribute3)
288 OR ( (Recinfo.attribute3 IS NULL)
289 AND (X_Attribute3 IS NULL)))
290 AND ( (Recinfo.attribute4 = X_Attribute4)
291 OR ( (Recinfo.attribute4 IS NULL)
292 AND (X_Attribute4 IS NULL)))
293 AND ( (Recinfo.attribute5 = X_Attribute5)
294 OR ( (Recinfo.attribute5 IS NULL)
295 AND (X_Attribute5 IS NULL)))
296 AND ( (Recinfo.attribute6 = X_Attribute6)
297 OR ( (Recinfo.attribute6 IS NULL)
298 AND (X_Attribute6 IS NULL)))
299 AND ( (Recinfo.attribute7 = X_Attribute7)
300 OR ( (Recinfo.attribute7 IS NULL)
301 AND (X_Attribute7 IS NULL)))
302 AND ( (Recinfo.attribute8 = X_Attribute8)
303 OR ( (Recinfo.attribute8 IS NULL)
304 AND (X_Attribute8 IS NULL)))
305 AND ( (Recinfo.attribute9 = X_Attribute9)
306 OR ( (Recinfo.attribute9 IS NULL)
307 AND (X_Attribute9 IS NULL)))
308 AND ( (Recinfo.attribute10 = X_Attribute10)
309 OR ( (Recinfo.attribute10 IS NULL)
310 AND (X_Attribute10 IS NULL)))
311 AND ( (Recinfo.attribute11 = X_Attribute11)
312 OR ( (Recinfo.attribute11 IS NULL)
313 AND (X_Attribute11 IS NULL)))
314 AND ( (Recinfo.attribute12 = X_Attribute12)
315 OR ( (Recinfo.attribute12 IS NULL)
316 AND (X_Attribute12 IS NULL)))
317 AND ( (Recinfo.attribute13 = X_Attribute13)
318 OR ( (Recinfo.attribute13 IS NULL)
319 AND (X_Attribute13 IS NULL)))
320 AND ( (Recinfo.attribute14 = X_Attribute14)
321 OR ( (Recinfo.attribute14 IS NULL)
322 AND (X_Attribute14 IS NULL)))
323 AND ( (Recinfo.attribute15 = X_Attribute15)
324 OR ( (Recinfo.attribute15 IS NULL)
325 AND (X_Attribute15 IS NULL)))
326 AND ( (Recinfo.master_container_id = X_Master_Container_Id)
327 OR ( (Recinfo.master_container_id IS NULL)
328 AND (X_Master_Container_Id IS NULL)))
329 AND ( (Recinfo.parent_container_id = X_Parent_Container_Id)
330 OR ( (Recinfo.parent_container_id IS NULL)
331 AND (X_Parent_Container_Id IS NULL)))
332 ) THEN
333 RETURN;
334 ELSE
335 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
336 APP_EXCEPTION.Raise_Exception;
337 END IF;
341
338 END Lock_Row;
339
340
342 PROCEDURE Update_Row(X_Rowid VARCHAR2,
343 X_Container_Id NUMBER,
344 X_Delivery_Id NUMBER,
345 X_Container_Inventory_Item_Id NUMBER,
346 X_Master_Container_Id NUMBER,
347 X_Parent_Container_Id NUMBER,
348 X_Quantity NUMBER,
349 X_Sequence_Number NUMBER,
350 X_Parent_Sequence_Number NUMBER,
351 X_Gross_Weight NUMBER,
352 X_Weight_Uom_Code VARCHAR2,
353 X_Volume_Uom_Code VARCHAR2,
354 X_Volume NUMBER,
355 X_Fill_Percent NUMBER,
356 X_Net_Weight NUMBER,
357 X_Organization_Id NUMBER,
358 X_Subinventory VARCHAR2,
359 X_Inventory_Location_Id NUMBER,
360 X_Revision VARCHAR2,
361 X_Lot_Number VARCHAR2,
362 X_Serial_Number VARCHAR2,
363 X_Master_Serial_Number VARCHAR2,
364 X_Inventory_Status VARCHAR2,
365 X_Ra_Interface_Status VARCHAR2,
366 X_Attribute_Category VARCHAR2,
367 X_Attribute1 VARCHAR2,
368 X_Attribute2 VARCHAR2,
369 X_Attribute3 VARCHAR2,
370 X_Attribute4 VARCHAR2,
371 X_Attribute5 VARCHAR2,
372 X_Attribute6 VARCHAR2,
373 X_Attribute7 VARCHAR2,
374 X_Attribute8 VARCHAR2,
375 X_Attribute9 VARCHAR2,
376 X_Attribute10 VARCHAR2,
377 X_Attribute11 VARCHAR2,
378 X_Attribute12 VARCHAR2,
379 X_Attribute13 VARCHAR2,
380 X_Attribute14 VARCHAR2,
381 X_Attribute15 VARCHAR2,
382 X_Last_Update_Date DATE,
383 X_Last_Updated_By NUMBER,
384 X_Last_Update_Login NUMBER
385 ) IS
386 BEGIN
387 UPDATE wsh_packed_containers
388 SET
389 container_id = X_Container_Id,
390 delivery_id = X_Delivery_Id,
391 container_inventory_item_id = X_Container_Inventory_Item_Id,
392 master_container_id = X_Master_Container_Id,
393 parent_container_id = X_Parent_Container_Id,
394 quantity = X_Quantity,
395 sequence_number = X_Sequence_Number,
396 parent_sequence_number = X_Parent_Sequence_Number,
397 gross_weight = X_Gross_Weight,
398 weight_uom_code = X_Weight_Uom_Code,
399 volume_uom_code = X_Volume_Uom_Code,
400 volume = X_Volume,
401 fill_percent = X_Fill_Percent,
402 net_weight = X_Net_Weight,
403 organization_id = X_Organization_Id,
404 subinventory = X_Subinventory,
405 inventory_location_id = X_Inventory_Location_Id,
406 revision = X_Revision,
407 lot_number = X_Lot_Number,
408 serial_number = X_Serial_Number,
409 master_serial_number = X_Master_Serial_Number,
410 inventory_status = X_Inventory_Status,
411 ra_interface_status = X_Ra_Interface_Status,
412 attribute_category = X_Attribute_Category,
413 attribute1 = X_Attribute1,
414 attribute2 = X_Attribute2,
415 attribute3 = X_Attribute3,
416 attribute4 = X_Attribute4,
417 attribute5 = X_Attribute5,
418 attribute6 = X_Attribute6,
419 attribute7 = X_Attribute7,
420 attribute8 = X_Attribute8,
421 attribute9 = X_Attribute9,
422 attribute10 = X_Attribute10,
423 attribute11 = X_Attribute11,
424 attribute12 = X_Attribute12,
425 attribute13 = X_Attribute13,
426 attribute14 = X_Attribute14,
427 attribute15 = X_Attribute15,
428 last_update_date = X_Last_Update_Date,
429 last_updated_by = X_Last_Updated_By,
430 last_update_login = X_Last_Update_Login
431 WHERE rowid = X_Rowid;
432
433 IF (SQL%NOTFOUND) THEN
434 RAISE NO_DATA_FOUND;
435 END IF;
436 END Update_Row;
437 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
438 BEGIN
439 DELETE FROM wsh_packed_containers
440 WHERE rowid = X_Rowid;
441
445 END Delete_Row;
442 IF (SQL%NOTFOUND) THEN
443 RAISE NO_DATA_FOUND;
444 END IF;
446
447
448 END WSH_PACKED_CONTAINERS_PKG;