[Home] [Help]
PACKAGE BODY: APPS.SO_PICKING_LINE_DETAILS_PKG
Source
1 PACKAGE BODY SO_PICKING_LINE_DETAILS_PKG as
2 /* $Header: WSHPLDHB.pls 115.0 99/07/16 08:19:35 porting ship $ */
3
4 PROCEDURE Insert_Row(X_Rowid IN OUT VARCHAR2,
5 X_Picking_Line_Detail_Id IN OUT NUMBER,
6 X_Last_Update_Date DATE,
7 X_Last_Updated_By NUMBER,
8 X_Created_By NUMBER,
9 X_Creation_Date DATE,
10 X_Last_Update_Login NUMBER,
11 X_Picking_Line_Id NUMBER,
12 X_Warehouse_Id NUMBER,
13 X_Requested_Quantity NUMBER,
14 X_Shipped_Quantity NUMBER,
15 X_Serial_Number VARCHAR2,
16 X_Lot_Number VARCHAR2,
17 X_Customer_Requested_Lot_Flag VARCHAR2,
18 X_Revision VARCHAR2,
19 X_Subinventory VARCHAR2,
20 X_Inventory_Location_Id NUMBER,
21 X_Inventory_Location_Segments VARCHAR2,
22 X_Detail_Type_Code VARCHAR2,
23 X_Context VARCHAR2,
24 X_Attribute1 VARCHAR2,
25 X_Attribute2 VARCHAR2,
26 X_Attribute3 VARCHAR2,
27 X_Attribute4 VARCHAR2,
28 X_Attribute5 VARCHAR2,
29 X_Attribute6 VARCHAR2,
30 X_Attribute7 VARCHAR2,
31 X_Attribute8 VARCHAR2,
32 X_Attribute9 VARCHAR2,
33 X_Attribute10 VARCHAR2,
34 X_Attribute11 VARCHAR2,
35 X_Attribute12 VARCHAR2,
36 X_Attribute13 VARCHAR2,
37 X_Attribute14 VARCHAR2,
38 X_Attribute15 VARCHAR2,
39 X_Released_Flag VARCHAR2,
40 X_Schedule_Date DATE,
41 X_Schedule_Level NUMBER,
42 X_Schedule_Status_Code VARCHAR2,
43 X_Demand_Id NUMBER,
44 X_Autoscheduled_Flag VARCHAR2,
45 X_Delivery NUMBER,
46 X_Wip_Reserved_Quantity NUMBER,
47 X_Wip_Completed_Quantity NUMBER,
48 X_Supply_Source_Type NUMBER,
49 X_Supply_Source_Header_Id NUMBER,
50 X_Update_Flag VARCHAR2,
51 X_Demand_Class_Code VARCHAR2,
52 X_Reservable_Flag VARCHAR2,
53 X_Transactable_Flag VARCHAR2,
54 X_Latest_Acceptable_Date DATE,
55 X_Delivery_Id NUMBER,
56 X_Departure_Id NUMBER,
57 X_Master_Container_Item_Id NUMBER,
58 X_Detail_Container_Item_Id NUMBER,
59 X_Dpw_Assigned_Flag VARCHAR2,
60 X_Load_Seq_Number NUMBER,
61 X_Transaction_Temp_Id NUMBER,
62 X_Container_id NUMBER,
63 X_Movement_id NUMBER
64 ) IS
65 CURSOR C IS SELECT rowid FROM so_picking_line_details
66 WHERE picking_line_detail_id = X_Picking_Line_Detail_Id;
67 CURSOR C2 IS SELECT so_picking_line_details_s.nextval FROM sys.dual;
68 BEGIN
69 if (X_Picking_Line_Detail_Id is NULL) then
70 OPEN C2;
71 FETCH C2 INTO X_Picking_Line_Detail_Id;
72 CLOSE C2;
73 end if;
74
75 INSERT INTO so_picking_line_details(
76 picking_line_detail_id,
77 last_update_date,
78 last_updated_by,
79 created_by,
80 creation_date,
81 last_update_login,
82 picking_line_id,
83 warehouse_id,
84 requested_quantity,
85 shipped_quantity,
86 serial_number,
87 lot_number,
88 customer_requested_lot_flag,
89 revision,
90 subinventory,
91 inventory_location_id,
92 segment1,
93 segment2,
94 segment3,
95 segment4,
96 segment5,
97 segment6,
98 segment7,
99 segment8,
100 segment9,
101 segment10,
102 segment11,
103 segment12,
104 segment13,
105 segment14,
106 segment15,
107 segment16,
108 segment17,
109 segment18,
110 segment19,
111 segment20,
112 inventory_location_segments,
113 detail_type_code,
114 context,
115 attribute1,
116 attribute2,
117 attribute3,
118 attribute4,
119 attribute5,
120 attribute6,
121 attribute7,
122 attribute8,
123 attribute9,
124 attribute10,
125 attribute11,
126 attribute12,
127 attribute13,
128 attribute14,
129 attribute15,
130 released_flag,
131 schedule_date,
132 schedule_level,
133 schedule_status_code,
134 demand_id,
135 autoscheduled_flag,
136 delivery,
137 wip_reserved_quantity,
138 wip_completed_quantity,
139 supply_source_type,
140 supply_source_header_id,
141 update_flag,
142 demand_class_code,
143 reservable_flag,
144 transactable_flag,
145 latest_acceptable_date,
146 delivery_id,
147 departure_id,
148 master_container_item_id,
149 detail_container_item_id,
150 dpw_assigned_flag,
151 load_seq_number,
152 transaction_temp_id,
153 container_id,
154 movement_id
155 ) VALUES (
156 X_Picking_Line_Detail_Id,
157 X_Last_Update_Date,
158 X_Last_Updated_By,
159 X_Created_By,
160 X_Creation_Date,
161 X_Last_Update_Login,
162 X_Picking_Line_Id,
163 X_Warehouse_Id,
164 X_Requested_Quantity,
165 X_Shipped_Quantity,
166 X_Serial_Number,
167 X_Lot_Number,
168 X_Customer_Requested_Lot_Flag,
169 X_Revision,
170 X_Subinventory,
171 X_Inventory_Location_Id,
172 NULL,
173 NULL,
174 NULL,
175 NULL,
176 NULL,
177 NULL,
178 NULL,
179 NULL,
180 NULL,
181 NULL,
182 NULL,
183 NULL,
184 NULL,
185 NULL,
186 NULL,
187 NULL,
188 NULL,
189 NULL,
190 NULL,
191 NULL,
192 X_Inventory_Location_Segments,
193 X_Detail_Type_Code,
194 X_Context,
195 X_Attribute1,
196 X_Attribute2,
197 X_Attribute3,
198 X_Attribute4,
199 X_Attribute5,
200 X_Attribute6,
201 X_Attribute7,
202 X_Attribute8,
203 X_Attribute9,
204 X_Attribute10,
205 X_Attribute11,
206 X_Attribute12,
207 X_Attribute13,
208 X_Attribute14,
209 X_Attribute15,
210 X_Released_Flag,
211 X_Schedule_Date,
212 X_Schedule_Level,
213 X_Schedule_Status_Code,
214 X_Demand_Id,
215 X_Autoscheduled_Flag,
216 X_Delivery,
217 X_Wip_Reserved_Quantity,
218 X_Wip_Completed_Quantity,
219 X_Supply_Source_Type,
220 X_Supply_Source_Header_Id,
221 X_Update_Flag,
222 X_Demand_Class_Code,
223 X_Reservable_Flag,
224 X_Transactable_Flag,
225 X_Latest_Acceptable_Date,
226 X_Delivery_Id,
227 X_Departure_Id,
228 X_Master_Container_Item_Id,
229 X_Detail_Container_Item_Id,
230 X_Dpw_Assigned_Flag,
231 X_Load_Seq_Number,
232 X_Transaction_Temp_Id,
233 X_Container_Id,
234 X_Movement_id
235 );
236
237 OPEN C;
238 FETCH C INTO X_Rowid;
239 if (C%NOTFOUND) then
240 CLOSE C;
241 Raise NO_DATA_FOUND;
242 end if;
243 CLOSE C;
244 END Insert_Row;
245
246
247 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
248 X_Picking_Line_Detail_Id NUMBER,
249 X_Picking_Line_Id NUMBER,
250 X_Warehouse_Id NUMBER,
251 X_Requested_Quantity NUMBER,
252 X_Shipped_Quantity NUMBER,
253 X_Serial_Number VARCHAR2,
254 X_Lot_Number VARCHAR2,
255 X_Customer_Requested_Lot_Flag VARCHAR2,
256 X_Revision VARCHAR2,
257 X_Subinventory VARCHAR2,
258 X_Inventory_Location_Id NUMBER,
259 X_Inventory_Location_Segments VARCHAR2,
260 X_Detail_Type_Code VARCHAR2,
261 X_Context VARCHAR2,
262 X_Attribute1 VARCHAR2,
263 X_Attribute2 VARCHAR2,
264 X_Attribute3 VARCHAR2,
265 X_Attribute4 VARCHAR2,
266 X_Attribute5 VARCHAR2,
267 X_Attribute6 VARCHAR2,
268 X_Attribute7 VARCHAR2,
269 X_Attribute8 VARCHAR2,
270 X_Attribute9 VARCHAR2,
271 X_Attribute10 VARCHAR2,
272 X_Attribute11 VARCHAR2,
273 X_Attribute12 VARCHAR2,
274 X_Attribute13 VARCHAR2,
275 X_Attribute14 VARCHAR2,
276 X_Attribute15 VARCHAR2,
277 X_Released_Flag VARCHAR2,
278 X_Schedule_Date DATE,
279 X_Schedule_Level NUMBER,
280 X_Schedule_Status_Code VARCHAR2,
281 X_Demand_Id NUMBER,
282 X_Autoscheduled_Flag VARCHAR2,
283 X_Delivery NUMBER,
284 X_Wip_Reserved_Quantity NUMBER,
285 X_Wip_Completed_Quantity NUMBER,
286 X_Supply_Source_Type NUMBER,
287 X_Supply_Source_Header_Id NUMBER,
288 X_Update_Flag VARCHAR2,
289 X_Demand_Class_Code VARCHAR2,
290 X_Reservable_Flag VARCHAR2,
291 X_Transactable_Flag VARCHAR2,
292 X_Latest_Acceptable_Date DATE,
293 X_Delivery_Id NUMBER,
294 X_Departure_Id NUMBER,
295 X_Master_Container_Item_Id NUMBER,
296 X_Detail_Container_Item_Id NUMBER,
297 X_Dpw_Assigned_Flag VARCHAR2,
298 X_Load_Seq_Number NUMBER,
299 X_Transaction_Temp_Id NUMBER,
300 X_Container_id NUMBER,
301 X_Movement_id NUMBER
302 ) IS
303 CURSOR C IS
304 SELECT *
305 FROM so_picking_line_details
306 WHERE rowid = X_Rowid
307 FOR UPDATE of Picking_Line_Detail_Id NOWAIT;
308 Recinfo C%ROWTYPE;
309
310
311 BEGIN
312 OPEN C;
313 FETCH C INTO Recinfo;
314 if (C%NOTFOUND) then
315 CLOSE C;
316 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
317 APP_EXCEPTION.Raise_Exception;
318 end if;
319 CLOSE C;
320 if (
321 (Recinfo.picking_line_detail_id = X_Picking_Line_Detail_Id)
322 AND (Recinfo.picking_line_id = X_Picking_Line_Id)
323 AND (Recinfo.warehouse_id = X_Warehouse_Id)
324 AND (Recinfo.requested_quantity = X_Requested_Quantity)
325 AND ( (Recinfo.shipped_quantity = X_Shipped_Quantity)
326 OR ( (Recinfo.shipped_quantity IS NULL)
327 AND (X_Shipped_Quantity IS NULL)))
328 AND ( (Recinfo.serial_number = X_Serial_Number)
329 OR ( (Recinfo.serial_number IS NULL)
330 AND (X_Serial_Number IS NULL)))
331 AND ( (Recinfo.lot_number = X_Lot_Number)
332 OR ( (Recinfo.lot_number IS NULL)
333 AND (X_Lot_Number IS NULL)))
334 AND ( (Recinfo.customer_requested_lot_flag = X_Customer_Requested_Lot_Flag)
335 OR ( (Recinfo.customer_requested_lot_flag IS NULL)
336 AND (X_Customer_Requested_Lot_Flag IS NULL)))
337 AND ( (Recinfo.revision = X_Revision)
338 OR ( (Recinfo.revision IS NULL)
339 AND (X_Revision IS NULL)))
340 AND ( (Recinfo.subinventory = X_Subinventory)
341 OR ( (Recinfo.subinventory IS NULL)
342 AND (X_Subinventory IS NULL)))
343 AND ( (Recinfo.inventory_location_id = X_Inventory_Location_Id)
344 OR ( (Recinfo.inventory_location_id IS NULL)
345 AND (X_Inventory_Location_Id IS NULL)))
346 AND ( (Recinfo.inventory_location_segments = X_Inventory_Location_Segments)
347 OR ( (Recinfo.inventory_location_segments IS NULL)
348 AND (X_Inventory_Location_Segments IS NULL)))
349 AND ( (Recinfo.detail_type_code = X_Detail_Type_Code)
350 OR ( (Recinfo.detail_type_code IS NULL)
351 AND (X_Detail_Type_Code IS NULL)))
352 AND ( (Recinfo.context = X_Context)
353 OR ( (Recinfo.context IS NULL)
354 AND (X_Context IS NULL)))
355 AND ( (Recinfo.attribute1 = X_Attribute1)
356 OR ( (Recinfo.attribute1 IS NULL)
357 AND (X_Attribute1 IS NULL)))
358 AND ( (Recinfo.attribute2 = X_Attribute2)
359 OR ( (Recinfo.attribute2 IS NULL)
360 AND (X_Attribute2 IS NULL)))
361 AND ( (Recinfo.attribute3 = X_Attribute3)
362 OR ( (Recinfo.attribute3 IS NULL)
363 AND (X_Attribute3 IS NULL)))
364 AND ( (Recinfo.attribute4 = X_Attribute4)
365 OR ( (Recinfo.attribute4 IS NULL)
366 AND (X_Attribute4 IS NULL)))
367 AND ( (Recinfo.attribute5 = X_Attribute5)
368 OR ( (Recinfo.attribute5 IS NULL)
369 AND (X_Attribute5 IS NULL)))
370 AND ( (Recinfo.attribute6 = X_Attribute6)
371 OR ( (Recinfo.attribute6 IS NULL)
372 AND (X_Attribute6 IS NULL)))
373 AND ( (Recinfo.attribute7 = X_Attribute7)
374 OR ( (Recinfo.attribute7 IS NULL)
375 AND (X_Attribute7 IS NULL)))
376 AND ( (Recinfo.attribute8 = X_Attribute8)
377 OR ( (Recinfo.attribute8 IS NULL)
378 AND (X_Attribute8 IS NULL)))
379 AND ( (Recinfo.attribute9 = X_Attribute9)
380 OR ( (Recinfo.attribute9 IS NULL)
381 AND (X_Attribute9 IS NULL)))
382 AND ( (Recinfo.attribute10 = X_Attribute10)
383 OR ( (Recinfo.attribute10 IS NULL)
384 AND (X_Attribute10 IS NULL)))
385 AND ( (Recinfo.attribute11 = X_Attribute11)
386 OR ( (Recinfo.attribute11 IS NULL)
387 AND (X_Attribute11 IS NULL)))
388 AND ( (Recinfo.attribute12 = X_Attribute12)
389 OR ( (Recinfo.attribute12 IS NULL)
390 AND (X_Attribute12 IS NULL)))
391 AND ( (Recinfo.attribute13 = X_Attribute13)
392 OR ( (Recinfo.attribute13 IS NULL)
393 AND (X_Attribute13 IS NULL)))
394 AND ( (Recinfo.attribute14 = X_Attribute14)
395 OR ( (Recinfo.attribute14 IS NULL)
396 AND (X_Attribute14 IS NULL)))
397 AND ( (Recinfo.attribute15 = X_Attribute15)
398 OR ( (Recinfo.attribute15 IS NULL)
399 AND (X_Attribute15 IS NULL)))
400 AND ( (Recinfo.released_flag = X_Released_Flag)
401 OR ( (Recinfo.released_flag IS NULL)
402 AND (X_Released_Flag IS NULL)))
403 AND ( (Recinfo.schedule_date = X_Schedule_Date)
404 OR ( (Recinfo.schedule_date IS NULL)
405 AND (X_Schedule_Date IS NULL)))
406 AND ( (Recinfo.schedule_level = X_Schedule_Level)
407 OR ( (Recinfo.schedule_level IS NULL)
408 AND (X_Schedule_Level IS NULL)))
409 AND ( (Recinfo.schedule_status_code = X_Schedule_Status_Code)
410 OR ( (Recinfo.schedule_status_code IS NULL)
411 AND (X_Schedule_Status_Code IS NULL)))
412 AND ( (Recinfo.demand_id = X_Demand_Id)
413 OR ( (Recinfo.demand_id IS NULL)
414 AND (X_Demand_Id IS NULL)))
415 AND ( (Recinfo.autoscheduled_flag = X_Autoscheduled_Flag)
416 OR ( (Recinfo.autoscheduled_flag IS NULL)
417 AND (X_Autoscheduled_Flag IS NULL)))
418 AND ( (Recinfo.delivery = X_Delivery)
419 OR ( (Recinfo.delivery IS NULL)
420 AND (X_Delivery IS NULL)))
421 AND ( (Recinfo.wip_reserved_quantity = X_Wip_Reserved_Quantity)
422 OR ( (Recinfo.wip_reserved_quantity IS NULL)
423 AND (X_Wip_Reserved_Quantity IS NULL)))
424 AND ( (Recinfo.wip_completed_quantity = X_Wip_Completed_Quantity)
425 OR ( (Recinfo.wip_completed_quantity IS NULL)
426 AND (X_Wip_Completed_Quantity IS NULL)))
427 AND ( (Recinfo.supply_source_type = X_Supply_Source_Type)
428 OR ( (Recinfo.supply_source_type IS NULL)
429 AND (X_Supply_Source_Type IS NULL)))
430 AND ( (Recinfo.supply_source_header_id = X_Supply_Source_Header_Id)
431 OR ( (Recinfo.supply_source_header_id IS NULL)
432 AND (X_Supply_Source_Header_Id IS NULL)))
433 AND ( (Recinfo.update_flag = X_Update_Flag)
434 OR ( (Recinfo.update_flag IS NULL)
435 AND (X_Update_Flag IS NULL)))
436 AND ( (Recinfo.demand_class_code = X_Demand_Class_Code)
437 OR ( (Recinfo.demand_class_code IS NULL)
438 AND (X_Demand_Class_Code IS NULL)))
439 AND ( (Recinfo.reservable_flag = X_Reservable_Flag)
440 OR ( (Recinfo.reservable_flag IS NULL)
441 AND (X_Reservable_Flag IS NULL)))
442 AND ( (Recinfo.transactable_flag = X_Transactable_Flag)
443 OR ( (Recinfo.transactable_flag IS NULL)
444 AND (X_Transactable_Flag IS NULL)))
445 AND ( (Recinfo.latest_acceptable_date = X_Latest_Acceptable_Date)
446 OR ( (Recinfo.latest_acceptable_date IS NULL)
447 AND (X_Latest_Acceptable_Date IS NULL)))
448 AND ( (Recinfo.delivery_id = X_Delivery_Id)
449 OR ( (Recinfo.delivery_id IS NULL)
450 AND (X_Delivery_Id IS NULL)))
451 AND ( (Recinfo.departure_id = X_Departure_Id)
452 OR ( (Recinfo.departure_id IS NULL)
453 AND (X_Departure_Id IS NULL)))
454 AND ( (Recinfo.master_container_item_id = X_Master_Container_Item_Id)
455 OR ( (Recinfo.master_container_item_id IS NULL)
456 AND (X_Master_Container_Item_Id IS NULL)))
457 AND ( (Recinfo.detail_container_item_id = X_Detail_Container_Item_Id)
458 OR ( (Recinfo.detail_container_item_id IS NULL)
459 AND (X_Detail_Container_Item_Id IS NULL)))
460 AND ( (Recinfo.dpw_assigned_flag = X_Dpw_Assigned_Flag)
461 OR ( (Recinfo.dpw_assigned_flag IS NULL)
462 AND (X_Dpw_Assigned_Flag IS NULL)))
463 AND ( (Recinfo.load_seq_number = X_Load_Seq_Number)
464 OR ( (Recinfo.load_seq_number IS NULL)
465 AND (X_Load_Seq_Number IS NULL)))
466 AND ( (Recinfo.transaction_temp_id = X_Transaction_Temp_Id)
467 OR ( (Recinfo.transaction_temp_id IS NULL)
468 AND (X_Transaction_Temp_Id IS NULL)))
469 AND ( (Recinfo.movement_id = X_Movement_Id)
470 OR ( (Recinfo.movement_id IS NULL)
471 AND (X_Movement_id IS NULL)))
472 ) then
473 return;
474 else
475 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
476 APP_EXCEPTION.Raise_Exception;
477 end if;
478 END Lock_Row;
479
480
481
482
483 END SO_PICKING_LINE_DETAILS_PKG;