1 PACKAGE BODY OE_SO_PLN_SERVER AS
2 /* $Header: OESOPLNB.pls 115.2 99/07/16 08:11:06 porting shi $ */
3
4 PROCEDURE When_Validate_Record
5 (
6 X_Row_Id IN VARCHAR2,
7 P_Picking_Line_Id IN NUMBER,
8 P_Db_Schedule_Date IN OUT DATE,
9 P_Db_Demand_Class_Code IN OUT VARCHAR2,
10 P_Db_Ship_To_Site_Use_Id IN OUT NUMBER,
11 P_Db_Warehouse_Id IN OUT NUMBER,
12 P_Db_Ship_To_Contact_Id IN OUT NUMBER,
13 P_Db_Shipment_Priority_Code IN OUT VARCHAR2,
14 P_Db_Ship_Method_Code IN OUT VARCHAR2,
15 P_Db_Reserved_Quantity IN OUT NUMBER,
16 P_Schedule_Action_Code IN VARCHAR2,
17 P_Schedule_Date IN DATE,
18 P_Demand_Class_Code IN VARCHAR2,
19 P_Ship_To_Site_Use_Id IN NUMBER,
20 P_Warehouse_Id IN NUMBER,
21 P_Ship_To_Contact_Id IN NUMBER,
22 P_Shipment_Priority_Code IN VARCHAR2,
23 P_Ship_Method_Code IN VARCHAR2,
24 P_Reserved_Quantity IN NUMBER,
25 P_Result OUT VARCHAR2
26 )
27 is
28 Result VARCHAR2(1) := 'Y';
29 begin
30 P_Result := 'Y';
31 OE_SO_PLN_SERVER.Get_Schedule_DB_Values
32 (
33 X_Row_Id,
34 P_Picking_Line_Id,
35 P_Db_Schedule_Date,
36 P_Db_Demand_Class_Code,
37 P_Db_Ship_To_Site_Use_Id,
38 P_Db_Warehouse_Id,
39 P_Db_Ship_To_Contact_Id,
40 P_Db_Shipment_Priority_Code,
41 P_Db_Ship_Method_Code,
42 P_Db_Reserved_Quantity,
43 Result
44 );
45 if (Result = 'N') then
46 P_Result := 'N';
47 Return;
48 end if;
49 if P_Schedule_Action_Code in ('RESERVE') then
50 if P_Reserved_Quantity <> P_Db_Reserved_Quantity then
51 OE_MSG.Set_Buffer_Message('OE_SCH_RES_QTY_CHG_NOT_ALLOWED');
52 end if;
53 elsif P_Schedule_Action_Code in ('UNRESERVE','UNDEMAND','UNSCHEDULE') then
54 if P_Warehouse_Id <> P_Db_Warehouse_Id then
55 OE_MSG.Set_Buffer_Message('OE_SCH_WH_CHG_NOT_ALLOWED');
56 else
57 if P_Schedule_Date <> P_Db_Schedule_Date then
58 OE_MSG.Set_Buffer_Message('OE_SCH_DATE_CHG_NOT_ALLOWED');
59 else
60 if P_Demand_Class_Code <> P_Db_Demand_Class_Code then
61 OE_MSG.Set_Buffer_Message('OE_SCH_DEM_CL_CHG_NOT_ALLOWED');
62 end if;
63 end if;
64 end if;
65 elsif P_Schedule_Action_Code = 'DEMAND' then
66 if P_Reserved_Quantity <> P_Db_Reserved_Quantity then
67 OE_MSG.Set_Buffer_Message('OE_SCH_RES_QTY_CHG_NOT_ALLOWED');
68 else
69 if P_Warehouse_Id <> P_Db_Warehouse_Id then
70 OE_MSG.Set_Buffer_Message('OE_SCH_WH_CHG_NOT_ALLOWED');
71 else
72 if P_Schedule_Date <> P_Db_Schedule_Date then
73 OE_MSG.Set_Buffer_Message('OE_SCH_DATE_CHG_NOT_ALLOWED');
74 else
75 if P_Demand_Class_Code <> P_Db_Demand_Class_Code then
76 OE_MSG.Set_Buffer_Message('OE_SCH_DEM_CL_CHG_NOT_ALLOWED');
77 end if;
78 end if;
79 end if;
80 end if;
81 elsif P_Schedule_Action_Code = 'ATP CHECK' then
82 if P_Ship_To_Site_Use_Id <> P_Db_Ship_To_Site_Use_Id then
83 OE_MSG.Set_Buffer_Message('OE_SCH_SHIP_TO_CHG_NOT_ALLOWED');
84 else
85 if P_Reserved_Quantity <> P_Db_Reserved_Quantity then
86 OE_MSG.Set_Buffer_Message('OE_SCH_RES_QTY_CHG_NOT_ALLOWED');
87 else
88 if P_Warehouse_Id <> P_Db_Warehouse_Id then
89 OE_MSG.Set_Buffer_Message('OE_SCH_WH_CHG_NOT_ALLOWED');
90 else
91 if P_Schedule_Date <> P_Db_Schedule_Date then
92 OE_MSG.Set_Buffer_Message('OE_SCH_DATE_CHG_NOT_ALLOWED');
93 else
94 if P_Demand_Class_Code <> P_Db_Demand_Class_Code then
95 OE_MSG.Set_Buffer_Message('OE_SCH_DEM_CL_CHG_NOT_ALLOWED');
96 end if;
97 end if;
98 end if;
99 end if;
100 end if;
101 else
102 null;
103 end if;
104 Return;
105
106 exception
107 when OTHERS then
108 OE_MSG.Internal_Exception
109 ('OE_SO_PLN_SERVER.When_Validate_Record',
110 'When-Validate-Record', 'PICKING_LINE');
111 Result := 'N';
112 end When_Validate_Record;
113
114
115 FUNCTION DB_Reserved_Quantity (X_Picking_Line_Id IN NUMBER) return NUMBER is
116 DB_Res_Qty NUMBER := 0;
117 begin
118
119 SELECT NVL(SUM ( NVL ( REQUESTED_QUANTITY , 0 ) ), 0)
120 INTO DB_Res_Qty
121 FROM SO_PICKING_LINE_DETAILS
122 WHERE SCHEDULE_STATUS_CODE = 'RESERVED'
123 AND PICKING_LINE_ID = X_Picking_Line_Id;
124
125 Return (DB_Res_Qty);
126
127 exception
128
129 when NO_DATA_FOUND then return(DB_Res_Qty);
130 when OTHERS then RAISE;
131
132 end DB_Reserved_Quantity;
133
134 PROCEDURE Get_Schedule_DB_Values(
135 X_Row_Id IN VARCHAR2
136 , X_Picking_Line_Id IN NUMBER
137 , P_Db_Schedule_Date OUT DATE
138 , P_Db_Demand_Class_Code OUT VARCHAR2
139 , P_Db_Ship_To_Site_Use_Id OUT NUMBER
140 , P_Db_Warehouse_Id OUT NUMBER
141 , P_Db_Ship_To_Contact_Id OUT NUMBER
142 , P_Db_Shipment_Priority_Code OUT VARCHAR2
143 , P_Db_Ship_Method_Code OUT VARCHAR2
144 , P_Db_Reserved_Quantity OUT NUMBER
145 , Result OUT VARCHAR2
146 ) is
147 begin
148
149 Result := 'Y';
150
151 SELECT schedule_date
152 , demand_class_code
153 , ship_to_site_use_id
154 , warehouse_id
155 , ship_to_contact_id
156 , shipment_priority_code
157 , ship_method_code
158 INTO P_Db_Schedule_Date
159 , P_Db_Demand_Class_Code
160 , P_Db_Ship_To_Site_Use_Id
161 , P_Db_Warehouse_Id
162 , P_Db_Ship_To_Contact_Id
163 , P_Db_Shipment_Priority_Code
164 , P_Db_Ship_Method_Code
165 FROM SO_PICKING_LINES
166 WHERE rowid = X_Row_Id;
167
168 P_Db_Reserved_Quantity :=
169 OE_SO_PLN_SERVER.DB_Reserved_Quantity(X_Picking_Line_Id);
170
171 exception
172 when OTHERS then
173 OE_MSG.Internal_Exception('OE_SO_PLN_SERVER.Get_Schedule_DB_Values',
174 'Get_DB_Values', 'P_LINE');
175 Result := 'N';
176 end Get_Schedule_DB_Values;
177
178
179 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
180 X_ATTRIBUTE1 VARCHAR2,
181 X_ATTRIBUTE10 VARCHAR2,
182 X_ATTRIBUTE11 VARCHAR2,
183 X_ATTRIBUTE12 VARCHAR2,
184 X_ATTRIBUTE13 VARCHAR2,
185 X_ATTRIBUTE14 VARCHAR2,
186 X_ATTRIBUTE15 VARCHAR2,
187 X_ATTRIBUTE2 VARCHAR2,
188 X_ATTRIBUTE3 VARCHAR2,
189 X_ATTRIBUTE4 VARCHAR2,
190 X_ATTRIBUTE5 VARCHAR2,
191 X_ATTRIBUTE6 VARCHAR2,
192 X_ATTRIBUTE7 VARCHAR2,
193 X_ATTRIBUTE8 VARCHAR2,
194 X_ATTRIBUTE9 VARCHAR2,
195 X_CANCELLED_QUANTITY NUMBER,
196 X_COMPONENT_CODE VARCHAR2,
197 X_COMPONENT_RATIO NUMBER,
198 X_COMPONENT_SEQUENCE_ID NUMBER,
199 X_CONFIGURATION_ITEM_FLAG VARCHAR2,
200 X_CONTEXT VARCHAR2,
201 X_CREATED_BY NUMBER,
202 X_CREATION_DATE DATE,
203 X_DATE_CONFIRMED DATE,
204 X_DATE_REQUESTED DATE,
205 X_DEMAND_CLASS_CODE VARCHAR2,
206 X_INCLUDED_ITEM_FLAG VARCHAR2,
207 X_INVENTORY_ITEM_ID NUMBER,
208 X_INVENTORY_STATUS VARCHAR2,
209 X_INVOICED_QUANTITY NUMBER,
210 X_LAST_UPDATED_BY NUMBER,
211 X_LAST_UPDATE_DATE DATE,
212 X_LAST_UPDATE_LOGIN NUMBER,
213 X_LATEST_ACCEPTABLE_DATE DATE,
214 X_LINE_DETAIL_ID NUMBER,
215 X_ORDER_LINE_ID NUMBER,
216 X_ORGANIZATION_ID NUMBER,
217 X_ORIGINAL_REQUESTED_QUANTITY NUMBER,
218 X_PICKING_HEADER_ID NUMBER,
219 X_PICKING_LINE_ID NUMBER,
220 X_PROGRAM_APPLICATION_ID NUMBER,
221 X_PROGRAM_ID NUMBER,
222 X_PROGRAM_UPDATE_DATE DATE,
223 X_RA_INTERFACE_STATUS VARCHAR2,
224 X_REQUESTED_QUANTITY NUMBER,
225 X_REQUEST_ID NUMBER,
226 X_SCHEDULE_DATE DATE,
227 X_SEQUENCE_NUMBER NUMBER,
228 X_SHIPMENT_PRIORITY_CODE VARCHAR2,
229 X_SHIPPED_QUANTITY NUMBER,
230 X_SHIP_METHOD_CODE VARCHAR2,
231 X_SHIP_TO_CONTACT_ID NUMBER,
232 X_SHIP_TO_SITE_USE_ID NUMBER,
233 X_UNIT_CODE VARCHAR2,
234 X_WAREHOUSE_ID NUMBER,
235 Result OUT VARCHAR2)IS
236
237 CURSOR C IS SELECT * FROM so_picking_lines
238 WHERE rowid = X_rowid
239 FOR UPDATE NOWAIT;
240
241 Recinfo C%ROWTYPE;
242 record_changed exception;
243 resource_busy exception;
244 record_deleted exception;
245
246 PRAGMA EXCEPTION_INIT (resource_busy, -54);
247
248 BEGIN
249 OPEN C;
250
251 FETCH C INTO Recinfo;
252 if (C%NOTFOUND) then
253 CLOSE C;
254 raise record_deleted;
255 -- APP_EXCEPTION.Raise_Exception;
256 end if;
257
258 CLOSE C;
259
260 if (
261 ((Recinfo.ATTRIBUTE1 <> X_ATTRIBUTE1)
262 AND NOT (Recinfo.ATTRIBUTE1 is NULL AND X_ATTRIBUTE1 is NULL)) AND
263 ((Recinfo.ATTRIBUTE10 <> X_ATTRIBUTE10)
264 AND NOT (Recinfo.ATTRIBUTE10 is NULL AND X_ATTRIBUTE10 is NULL)) AND
265 ((Recinfo.ATTRIBUTE11 <> X_ATTRIBUTE11)
266 AND NOT (Recinfo.ATTRIBUTE11 is NULL AND X_ATTRIBUTE11 is NULL)) AND
267 ((Recinfo.ATTRIBUTE12 <> X_ATTRIBUTE12)
268 AND NOT (Recinfo.ATTRIBUTE12 is NULL AND X_ATTRIBUTE12 is NULL)) AND
269 ((Recinfo.ATTRIBUTE13 <> X_ATTRIBUTE13)
270 AND NOT (Recinfo.ATTRIBUTE13 is NULL AND X_ATTRIBUTE13 is NULL)) AND
271 ((Recinfo.ATTRIBUTE14 <> X_ATTRIBUTE14)
272 AND NOT (Recinfo.ATTRIBUTE14 is NULL AND X_ATTRIBUTE14 is NULL)) AND
273 ((Recinfo.ATTRIBUTE15 <> X_ATTRIBUTE15)
274 AND NOT (Recinfo.ATTRIBUTE15 is NULL AND X_ATTRIBUTE15 is NULL)) AND
275 ((Recinfo.ATTRIBUTE2 <> X_ATTRIBUTE2)
276 AND NOT (Recinfo.ATTRIBUTE2 is NULL AND X_ATTRIBUTE2 is NULL)) AND
277 ((Recinfo.ATTRIBUTE3 <> X_ATTRIBUTE3)
278 AND NOT (Recinfo.ATTRIBUTE3 is NULL AND X_ATTRIBUTE3 is NULL)) AND
279 ((Recinfo.ATTRIBUTE4 <> X_ATTRIBUTE4)
280 AND NOT (Recinfo.ATTRIBUTE4 is NULL AND X_ATTRIBUTE4 is NULL)) AND
281 ((Recinfo.ATTRIBUTE5 <> X_ATTRIBUTE5)
282 AND NOT (Recinfo.ATTRIBUTE5 is NULL AND X_ATTRIBUTE5 is NULL)) AND
283 ((Recinfo.ATTRIBUTE6 <> X_ATTRIBUTE6)
284 AND NOT (Recinfo.ATTRIBUTE6 is NULL AND X_ATTRIBUTE6 is NULL)) AND
285 ((Recinfo.ATTRIBUTE7 <> X_ATTRIBUTE7)
286 AND NOT (Recinfo.ATTRIBUTE7 is NULL AND X_ATTRIBUTE7 is NULL)) AND
287 ((Recinfo.ATTRIBUTE8 <> X_ATTRIBUTE8)
288 AND NOT (Recinfo.ATTRIBUTE8 is NULL AND X_ATTRIBUTE8 is NULL)) AND
289 ((Recinfo.ATTRIBUTE9 <> X_ATTRIBUTE9)
290 AND NOT (Recinfo.ATTRIBUTE9 is NULL AND X_ATTRIBUTE9 is NULL)) AND
291 ((Recinfo.CANCELLED_QUANTITY <> X_CANCELLED_QUANTITY)
292 AND NOT (Recinfo.CANCELLED_QUANTITY is NULL AND X_CANCELLED_QUANTITY is NULL)) AND
293 ((Recinfo.COMPONENT_CODE <> X_COMPONENT_CODE)
294 AND NOT (Recinfo.COMPONENT_CODE is NULL AND X_COMPONENT_CODE is NULL)) AND
295 ((Recinfo.COMPONENT_RATIO <> X_COMPONENT_RATIO)
296 AND NOT (Recinfo.COMPONENT_RATIO is NULL AND X_COMPONENT_RATIO is NULL)) AND
297 ((Recinfo.COMPONENT_SEQUENCE_ID <> X_COMPONENT_SEQUENCE_ID)
298 AND NOT (Recinfo.COMPONENT_SEQUENCE_ID is NULL AND X_COMPONENT_SEQUENCE_ID is NULL)) AND
299 ((Recinfo.CONFIGURATION_ITEM_FLAG <> X_CONFIGURATION_ITEM_FLAG)
300 AND NOT (Recinfo.CONFIGURATION_ITEM_FLAG is NULL AND X_CONFIGURATION_ITEM_FLAG is NULL))
301 ) then
302 raise record_changed;
303 end if;
304
305 if (
306 ((Recinfo.CONTEXT <> X_CONTEXT)
307 AND NOT (Recinfo.CONTEXT is NULL AND X_CONTEXT is NULL)) AND
308 ((Recinfo.CREATED_BY <> X_CREATED_BY)
309 AND NOT (Recinfo.CREATED_BY is NULL AND X_CREATED_BY is NULL)) AND
310 ((Recinfo.CREATION_DATE <> X_CREATION_DATE)
311 AND NOT (Recinfo.CREATION_DATE is NULL AND X_CREATION_DATE is NULL)) AND
312 ((Recinfo.DATE_CONFIRMED <> X_DATE_CONFIRMED)
313 AND NOT (Recinfo.DATE_CONFIRMED is NULL AND X_DATE_CONFIRMED is NULL)) AND
314 ((Recinfo.DATE_REQUESTED <> X_DATE_REQUESTED)
315 AND NOT (Recinfo.DATE_REQUESTED is NULL AND X_DATE_REQUESTED is NULL)) AND
316 ((Recinfo.DEMAND_CLASS_CODE <> X_DEMAND_CLASS_CODE)
317 AND NOT (Recinfo.DEMAND_CLASS_CODE is NULL AND X_DEMAND_CLASS_CODE is NULL)) AND
318 ((Recinfo.INCLUDED_ITEM_FLAG <> X_INCLUDED_ITEM_FLAG)
319 AND NOT (Recinfo.INCLUDED_ITEM_FLAG is NULL AND X_INCLUDED_ITEM_FLAG is NULL)) AND
320 ((Recinfo.INVENTORY_ITEM_ID <> X_INVENTORY_ITEM_ID)
321 AND NOT (Recinfo.INVENTORY_ITEM_ID is NULL AND X_INVENTORY_ITEM_ID is NULL)) AND
322 ((Recinfo.INVENTORY_STATUS <> X_INVENTORY_STATUS)
323 AND NOT (Recinfo.INVENTORY_STATUS is NULL AND X_INVENTORY_STATUS is NULL)) AND
324 ((Recinfo.INVOICED_QUANTITY <> X_INVOICED_QUANTITY)
325 AND NOT (Recinfo.INVOICED_QUANTITY is NULL AND X_INVOICED_QUANTITY is NULL)) AND
326 ((Recinfo.LAST_UPDATED_BY <> X_LAST_UPDATED_BY)
327 AND NOT (Recinfo.LAST_UPDATED_BY is NULL AND X_LAST_UPDATED_BY is NULL)) AND
328 ((Recinfo.LAST_UPDATE_DATE <> X_LAST_UPDATE_DATE)
329 AND NOT (Recinfo.LAST_UPDATE_DATE is NULL AND X_LAST_UPDATE_DATE is NULL)) AND
330 ((Recinfo.LAST_UPDATE_LOGIN <> X_LAST_UPDATE_LOGIN)
331 AND NOT (Recinfo.LAST_UPDATE_LOGIN is NULL AND X_LAST_UPDATE_LOGIN is NULL)) AND
332 ((Recinfo.LATEST_ACCEPTABLE_DATE <> X_LATEST_ACCEPTABLE_DATE)
333 AND NOT (Recinfo.LATEST_ACCEPTABLE_DATE is NULL AND X_LATEST_ACCEPTABLE_DATE is NULL)) AND
334 ((Recinfo.LINE_DETAIL_ID <> X_LINE_DETAIL_ID)
335 AND NOT (Recinfo.LINE_DETAIL_ID is NULL AND X_LINE_DETAIL_ID is NULL)) AND
336 ((Recinfo.ORDER_LINE_ID <> X_ORDER_LINE_ID)
337 AND NOT (Recinfo.ORDER_LINE_ID is NULL AND X_ORDER_LINE_ID is NULL)) AND
338 -- ((Recinfo.ORGANIZATION_ID <> X_ORGANIZATION_ID)
339 -- AND NOT (Recinfo.ORGANIZATION_ID is NULL AND X_ORGANIZATION_ID is NULL)) AND
340 ((Recinfo.ORIGINAL_REQUESTED_QUANTITY <> X_ORIGINAL_REQUESTED_QUANTITY)
341 AND NOT (Recinfo.ORIGINAL_REQUESTED_QUANTITY is NULL AND X_ORIGINAL_REQUESTED_QUANTITY is NULL))
342 ) then
343 raise record_changed;
344 end if;
345
346 if (
347 ((Recinfo.PICKING_HEADER_ID <> X_PICKING_HEADER_ID)
348 AND NOT (Recinfo.PICKING_HEADER_ID is NULL AND X_PICKING_HEADER_ID is NULL)) AND
349 ((Recinfo.PICKING_LINE_ID <> X_PICKING_LINE_ID)
350 AND NOT (Recinfo.PICKING_LINE_ID is NULL AND X_PICKING_LINE_ID is NULL)) AND
351 ((Recinfo.PROGRAM_APPLICATION_ID <> X_PROGRAM_APPLICATION_ID)
352 AND NOT (Recinfo.PROGRAM_APPLICATION_ID is NULL AND X_PROGRAM_APPLICATION_ID is NULL)) AND
353 ((Recinfo.PROGRAM_ID <> X_PROGRAM_ID)
354 AND NOT (Recinfo.PROGRAM_ID is NULL AND X_PROGRAM_ID is NULL)) AND
355 ((Recinfo.PROGRAM_UPDATE_DATE <> X_PROGRAM_UPDATE_DATE)
356 AND NOT (Recinfo.PROGRAM_UPDATE_DATE is NULL AND X_PROGRAM_UPDATE_DATE is NULL)) AND
357 ((Recinfo.RA_INTERFACE_STATUS <> X_RA_INTERFACE_STATUS)
358 AND NOT (Recinfo.RA_INTERFACE_STATUS is NULL AND X_RA_INTERFACE_STATUS is NULL)) AND
359 ((Recinfo.REQUESTED_QUANTITY <> X_REQUESTED_QUANTITY)
360 AND NOT (Recinfo.REQUESTED_QUANTITY is NULL AND X_REQUESTED_QUANTITY is NULL)) AND
361 ((Recinfo.REQUEST_ID <> X_REQUEST_ID)
362 AND NOT (Recinfo.REQUEST_ID is NULL AND X_REQUEST_ID is NULL)) AND
363 ((Recinfo.SCHEDULE_DATE <> X_SCHEDULE_DATE)
364 AND NOT (Recinfo.SCHEDULE_DATE is NULL AND X_SCHEDULE_DATE is NULL)) AND
365 ((Recinfo.SEQUENCE_NUMBER <> X_SEQUENCE_NUMBER)
366 AND NOT (Recinfo.SEQUENCE_NUMBER is NULL AND X_SEQUENCE_NUMBER is NULL)) AND
367 ((Recinfo.SHIPMENT_PRIORITY_CODE <> X_SHIPMENT_PRIORITY_CODE)
368 AND NOT (Recinfo.SHIPMENT_PRIORITY_CODE is NULL AND X_SHIPMENT_PRIORITY_CODE is NULL)) AND
369 ((Recinfo.SHIPPED_QUANTITY <> X_SHIPPED_QUANTITY)
370 AND NOT (Recinfo.SHIPPED_QUANTITY is NULL AND X_SHIPPED_QUANTITY is NULL)) AND
371 ((Recinfo.SHIP_METHOD_CODE <> X_SHIP_METHOD_CODE)
372 AND NOT (Recinfo.SHIP_METHOD_CODE is NULL AND X_SHIP_METHOD_CODE is NULL))
373 ) then
374 raise record_changed;
375 end if;
376
377 if (
378 ((Recinfo.SHIP_TO_CONTACT_ID <> X_SHIP_TO_CONTACT_ID)
379 AND NOT (Recinfo.SHIP_TO_CONTACT_ID is NULL AND X_SHIP_TO_CONTACT_ID is NULL)) AND
380 ((Recinfo.SHIP_TO_SITE_USE_ID <> X_SHIP_TO_SITE_USE_ID)
381 AND NOT (Recinfo.SHIP_TO_SITE_USE_ID is NULL AND X_SHIP_TO_SITE_USE_ID is NULL)) AND
382 ((Recinfo.UNIT_CODE <> X_UNIT_CODE)
383 AND NOT (Recinfo.UNIT_CODE is NULL AND X_UNIT_CODE is NULL)) AND
384 ((Recinfo.WAREHOUSE_ID <> X_WAREHOUSE_ID)
385 AND NOT (Recinfo.WAREHOUSE_ID is NULL AND X_WAREHOUSE_ID is NULL))
386 ) then
387 raise record_changed;
388 end if;
389
390 Result := 'SUCCESS';
391 exception
392 when record_changed then
393 Result := 'RECORD_CHANGED';
394 when resource_busy then
395 Result := 'RESOURCE_BUSY';
396 when record_deleted then
397 Result := 'RECORD_DELETED';
398 END Lock_Row;
399
400 PROCEDURE Update_Row(X_Rowid VARCHAR2,
401 X_ATTRIBUTE1 VARCHAR2,
402 X_ATTRIBUTE10 VARCHAR2,
403 X_ATTRIBUTE11 VARCHAR2,
404 X_ATTRIBUTE12 VARCHAR2,
405 X_ATTRIBUTE13 VARCHAR2,
406 X_ATTRIBUTE14 VARCHAR2,
407 X_ATTRIBUTE15 VARCHAR2,
408 X_ATTRIBUTE2 VARCHAR2,
409 X_ATTRIBUTE3 VARCHAR2,
410 X_ATTRIBUTE4 VARCHAR2,
411 X_ATTRIBUTE5 VARCHAR2,
412 X_ATTRIBUTE6 VARCHAR2,
413 X_ATTRIBUTE7 VARCHAR2,
414 X_ATTRIBUTE8 VARCHAR2,
415 X_ATTRIBUTE9 VARCHAR2,
416 X_CANCELLED_QUANTITY NUMBER,
417 X_COMPONENT_CODE VARCHAR2,
418 X_COMPONENT_RATIO NUMBER,
419 X_COMPONENT_SEQUENCE_ID NUMBER,
420 X_CONFIGURATION_ITEM_FLAG VARCHAR2,
421 X_CONTEXT VARCHAR2,
422 X_CREATED_BY NUMBER,
423 X_CREATION_DATE DATE,
424 X_DATE_CONFIRMED DATE,
425 X_DATE_REQUESTED DATE,
426 X_DEMAND_CLASS_CODE VARCHAR2,
427 X_INCLUDED_ITEM_FLAG VARCHAR2,
428 X_INVENTORY_ITEM_ID NUMBER,
429 X_INVENTORY_STATUS VARCHAR2,
430 X_INVOICED_QUANTITY NUMBER,
431 X_LAST_UPDATED_BY NUMBER,
432 X_LAST_UPDATE_DATE DATE,
433 X_LAST_UPDATE_LOGIN NUMBER,
434 X_LATEST_ACCEPTABLE_DATE DATE,
435 X_LINE_DETAIL_ID NUMBER,
436 X_ORDER_LINE_ID NUMBER,
437 X_ORGANIZATION_ID NUMBER,
438 X_ORIGINAL_REQUESTED_QUANTITY NUMBER,
439 X_PICKING_HEADER_ID NUMBER,
440 X_PICKING_LINE_ID NUMBER,
441 X_PROGRAM_APPLICATION_ID NUMBER,
442 X_PROGRAM_ID NUMBER,
443 X_PROGRAM_UPDATE_DATE DATE,
444 X_RA_INTERFACE_STATUS VARCHAR2,
445 X_REQUESTED_QUANTITY NUMBER,
446 X_REQUEST_ID NUMBER,
447 X_SCHEDULE_DATE DATE,
448 X_SEQUENCE_NUMBER NUMBER,
449 X_SHIPMENT_PRIORITY_CODE VARCHAR2,
450 X_SHIPPED_QUANTITY NUMBER,
451 X_SHIP_METHOD_CODE VARCHAR2,
452 X_SHIP_TO_CONTACT_ID NUMBER,
453 X_SHIP_TO_SITE_USE_ID NUMBER,
454 X_UNIT_CODE VARCHAR2,
455 X_WAREHOUSE_ID NUMBER
456 )IS
457
458 BEGIN
459 UPDATE so_picking_lines
460 SET
461 ATTRIBUTE1 = X_ATTRIBUTE1,
462 ATTRIBUTE10 = X_ATTRIBUTE10,
463 ATTRIBUTE11 = X_ATTRIBUTE11,
464 ATTRIBUTE12 = X_ATTRIBUTE12,
465 ATTRIBUTE13 = X_ATTRIBUTE13,
466 ATTRIBUTE14 = X_ATTRIBUTE14,
467 ATTRIBUTE15 = X_ATTRIBUTE15,
468 ATTRIBUTE2 = X_ATTRIBUTE2,
469 ATTRIBUTE3 = X_ATTRIBUTE3,
470 ATTRIBUTE4 = X_ATTRIBUTE4,
471 ATTRIBUTE5 = X_ATTRIBUTE5,
472 ATTRIBUTE6 = X_ATTRIBUTE6,
473 ATTRIBUTE7 = X_ATTRIBUTE7,
474 ATTRIBUTE8 = X_ATTRIBUTE8,
475 ATTRIBUTE9 = X_ATTRIBUTE9,
476 CANCELLED_QUANTITY = X_CANCELLED_QUANTITY,
477 COMPONENT_CODE = X_COMPONENT_CODE,
478 COMPONENT_RATIO = X_COMPONENT_RATIO,
479 COMPONENT_SEQUENCE_ID = X_COMPONENT_SEQUENCE_ID,
480 CONFIGURATION_ITEM_FLAG = X_CONFIGURATION_ITEM_FLAG,
481 CONTEXT = X_CONTEXT,
482 CREATED_BY = X_CREATED_BY,
483 CREATION_DATE = X_CREATION_DATE,
484 DATE_CONFIRMED = X_DATE_CONFIRMED,
485 DATE_REQUESTED = X_DATE_REQUESTED,
486 DEMAND_CLASS_CODE = X_DEMAND_CLASS_CODE,
487 INCLUDED_ITEM_FLAG = X_INCLUDED_ITEM_FLAG,
488 INVENTORY_ITEM_ID = X_INVENTORY_ITEM_ID,
489 INVENTORY_STATUS = X_INVENTORY_STATUS,
490 INVOICED_QUANTITY = X_INVOICED_QUANTITY,
491 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
492 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
493 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
494 LATEST_ACCEPTABLE_DATE = X_LATEST_ACCEPTABLE_DATE,
495 LINE_DETAIL_ID = X_LINE_DETAIL_ID,
496 ORDER_LINE_ID = X_ORDER_LINE_ID,
497 -- ORGANIZATION_ID = X_ORGANIZATION_ID,
498 ORIGINAL_REQUESTED_QUANTITY = X_ORIGINAL_REQUESTED_QUANTITY,
499 PICKING_HEADER_ID = X_PICKING_HEADER_ID,
500 PICKING_LINE_ID = X_PICKING_LINE_ID,
501 PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
502 PROGRAM_ID = X_PROGRAM_ID,
503 PROGRAM_UPDATE_DATE = X_PROGRAM_UPDATE_DATE,
504 RA_INTERFACE_STATUS = X_RA_INTERFACE_STATUS,
505 REQUESTED_QUANTITY = X_REQUESTED_QUANTITY,
506 REQUEST_ID = X_REQUEST_ID,
507 SCHEDULE_DATE = X_SCHEDULE_DATE,
508 SEQUENCE_NUMBER = X_SEQUENCE_NUMBER,
509 SHIPMENT_PRIORITY_CODE = X_SHIPMENT_PRIORITY_CODE,
510 SHIPPED_QUANTITY = X_SHIPPED_QUANTITY,
511 SHIP_METHOD_CODE = X_SHIP_METHOD_CODE,
512 SHIP_TO_CONTACT_ID = X_SHIP_TO_CONTACT_ID,
513 SHIP_TO_SITE_USE_ID = X_SHIP_TO_SITE_USE_ID,
514 UNIT_CODE = X_UNIT_CODE,
515 WAREHOUSE_ID = X_WAREHOUSE_ID
516 WHERE ROWID = X_Rowid;
517
518 IF (SQL%NOTFOUND) then
519 Raise NO_DATA_FOUND;
520 end if;
521
522 exception
523 when OTHERS then
524 OE_MSG.Internal_Exception('OE_SO_PLN_SERVER.Update_Row',NULL,NULL);
525
526 END Update_Row;
527
528 FUNCTION complex_details (x_picking_line_id IN NUMBER) RETURN BOOLEAN IS
529 complexDetails NUMBER := 0;
530 BEGIN
531
532 SELECT min(1)
533 INTO complexDetails
534 FROM so_picking_line_details
535 WHERE picking_line_id = x_picking_line_id
536 AND NVL( released_flag, 'N') = 'N'
537 HAVING COUNT( DISTINCT warehouse_id) > 1
538 OR COUNT( DISTINCT schedule_date) > 1
539 OR COUNT( DISTINCT revision) > 1
540 OR COUNT( DISTINCT lot_number) > 1
541 OR COUNT( DISTINCT subinventory) > 1
542 OR COUNT( DISTINCT demand_class_code) > 1
543 OR ( COUNT( subinventory) > 0
544 AND COUNT( subinventory) <> COUNT(1))
545 OR ( COUNT( warehouse_id) > 0
546 AND COUNT( warehouse_id) <> COUNT(1))
547 OR ( COUNT( schedule_date) > 0
548 AND COUNT( schedule_date) <> COUNT(1))
549 OR ( COUNT( lot_number) > 0
550 AND COUNT( lot_number) <> COUNT(1))
551 OR ( COUNT( revision) > 0
552 AND COUNT( revision) <> COUNT(1))
553 OR ( COUNT( demand_class_code) > 0
554 AND COUNT( demand_class_code) <> COUNT(1));
555
556 IF complexDetails = 1 THEN
557 RETURN TRUE;
558 ELSE
559 RETURN FALSE;
560 END IF;
561
562 EXCEPTION
563
564 WHEN NO_DATA_FOUND THEN RETURN FALSE;
565 WHEN OTHERS THEN RETURN FALSE;
566
567 END complex_details;
568
569
570 PROCEDURE Validate_Reserved_Qty (
571 P_Picking_Line_Id IN NUMBER,
572 P_Reserved_Qty IN NUMBER,
573 P_Original_Requested_Qty IN NUMBER,
574 P_Cancelled_Qty IN NUMBER,
575 P_Released_Qty IN NUMBER,
576 P_Result OUT VARCHAR2
577 ) is
578 begin
579
580 P_Result := 'Y';
581
582 if (NVL(P_Reserved_Qty, 0) = OE_SO_PLN_SERVER.DB_Reserved_Quantity(P_Picking_Line_Id)) then
583 Return;
584 end if;
585
586 if (P_Reserved_Qty > (NVL(P_Original_Requested_Qty, 0)
587 - NVL(P_Cancelled_Qty, 0) )) then
588 OE_MSG.Set_Buffer_Message('OE_SCH_RES_MORE_ORD_QTY');
589 P_Result := 'N';
590 Return;
591 elsif (P_Reserved_Qty < NVL(P_Released_Qty, 0) ) then
592 OE_MSG.Set_Buffer_Message('OE_SCH_RES_LESS_REL_QTY','RELEASED_QUANTITY',to_char(P_Released_Qty));
593 P_Result := 'N';
594 Return;
595 else
596 if (OE_SO_PLN_SERVER.Complex_Details(P_Picking_Line_Id)) then
597 OE_MSG.Set_Buffer_Message('OE_SCH_COMPLEX_DETAILS');
598 P_Result := 'N';
599 Return;
600 end if;
601 end if;
602
603 Return;
604
605 end Validate_Reserved_Qty;
606
607 END OE_SO_PLN_SERVER;
[Home] [Help]