1 PACKAGE BODY WMS_OP_PLAN_DETAILS_PKG AS
2 /* $Header: WMSOPLDB.pls 120.0 2005/05/25 08:53:00 appldev noship $ */
3 --
4 PROCEDURE INSERT_ROW (
5 x_rowid IN OUT nocopy VARCHAR2
6 ,x_operation_plan_detail_id IN NUMBER
7 ,x_last_updated_by IN NUMBER
8 ,x_last_update_date IN DATE
9 ,x_created_by IN NUMBER
10 ,x_creation_date IN DATE
11 ,x_last_update_login IN NUMBER
12 ,x_operation_plan_id IN NUMBER
13 ,x_operation_type IN NUMBER
14 ,x_operation_sequence IN NUMBER
15 ,x_bulk_pick_type IN NUMBER
16 ,x_drop_lpn_option IN NUMBER
17 ,x_wait_for_group_completion IN VARCHAR2
18 ,x_system_dispatched IN VARCHAR2
19 ,x_op_segment_completed IN VARCHAR2
20 ,x_zone_selection_criteria IN NUMBER
21 ,x_pre_specified_zone_id IN NUMBER
22 ,x_zone_selection_api_id IN NUMBER
23 ,x_sub_selection_criteria IN NUMBER
24 ,x_pre_specified_sub_code IN VARCHAR2
25 ,x_sub_selection_api_id IN NUMBER
26 ,x_loc_selection_criteria IN NUMBER
27 ,x_pre_specified_loc_id IN NUMBER
28 ,x_loc_selection_api_id IN NUMBER
29 ,x_activity_segment IN NUMBER
30 ,x_attribute_category IN VARCHAR2
31 ,x_attribute1 IN VARCHAR2
32 ,x_attribute2 IN VARCHAR2
33 ,x_attribute3 IN VARCHAR2
34 ,x_attribute4 IN VARCHAR2
35 ,x_attribute5 IN VARCHAR2
36 ,x_attribute6 IN VARCHAR2
37 ,x_attribute7 IN VARCHAR2
38 ,x_attribute8 IN VARCHAR2
39 ,x_attribute9 IN VARCHAR2
40 ,x_attribute10 IN VARCHAR2
41 ,x_attribute11 IN VARCHAR2
42 ,x_attribute12 IN VARCHAR2
43 ,x_attribute13 IN VARCHAR2
44 ,x_attribute14 IN VARCHAR2
45 ,x_attribute15 IN VARCHAR2
46 --,x_task_mode IN NUMBER (out of scope for patchset 'J')
47 --,x_operation_method IN NUMBER (out of scope for patchset 'J')
48 ,x_lpn_selection_criteria IN NUMBER
49 ,x_lpn_selection_api_id IN NUMBER
50 --,x_catch_secondary_qty IN NUMBER (out of scope for patchset 'J')
51 ,x_loc_mtrl_grp_rule_id IN NUMBER
52 ,x_lpn_mtrl_grp_rule_id IN NUMBER
53 ,x_organization_id IN NUMBER
54 ,x_is_in_inventory IN VARCHAR2
55 ,x_subsequent_op_plan_id IN NUMBER
56 ,x_consolidation_method_id IN NUMBER
57 )IS
58 CURSOR C IS SELECT ROWID FROM WMS_OP_PLAN_DETAILS
59 WHERE operation_plan_detail_id = x_operation_plan_detail_id
60 AND operation_plan_id = x_operation_plan_id;
61 BEGIN
62 INSERT INTO WMS_OP_PLAN_DETAILS (
63 operation_plan_id
64 ,operation_plan_detail_id
65 ,operation_type
66 ,operation_sequence
67 ,bulk_pick_type
68 ,drop_lpn_option
69 ,wait_for_group_completion
70 ,system_dispatched
71 ,op_segment_completed
72 ,zone_selection_criteria
73 ,pre_specified_zone_id
74 ,zone_selection_api_id
75 ,sub_selection_criteria
76 ,pre_specified_sub_code
77 ,sub_selection_api_id
78 ,loc_selection_criteria
79 ,pre_specified_loc_id
80 ,loc_selection_api_id
81 ,activity_segment
82 ,created_by
83 ,creation_date
84 ,last_updated_by
85 ,last_update_date
86 ,last_update_login
87 ,attribute_category
88 ,attribute1
89 ,attribute2
90 ,attribute3
91 ,attribute4
92 ,attribute5
93 ,attribute6
94 ,attribute7
95 ,attribute8
96 ,attribute9
97 ,attribute10
98 ,attribute11
99 ,attribute12
100 ,attribute13
101 ,attribute14
102 ,attribute15
103 --,task_mode (out of scope for patchset 'J')
104 --,operation_method (out of scope for patchset 'J')
105 ,lpn_selection_criteria
106 ,lpn_selection_api_id
107 --,catch_secondary_qty (out of scope for patchset 'J')
108 ,loc_mtrl_grp_rule_id
109 ,lpn_mtrl_grp_rule_id
110 ,organization_id
111 ,is_in_inventory
112 ,subsequent_op_plan_id
113 ,consolidation_method_id
114 ) values (
115 x_operation_plan_id
116 ,x_operation_plan_detail_id
117 ,x_operation_type
118 ,x_operation_sequence
119 ,x_bulk_pick_type
120 ,x_drop_lpn_option
121 ,x_wait_for_group_completion
122 ,x_system_dispatched
123 ,x_op_segment_completed
124 ,x_zone_selection_criteria
125 ,x_pre_specified_zone_id
126 ,x_zone_selection_api_id
127 ,x_sub_selection_criteria
128 ,x_pre_specified_sub_code
129 ,x_sub_selection_api_id
130 ,x_loc_selection_criteria
131 ,x_pre_specified_loc_id
132 ,x_loc_selection_api_id
133 ,x_activity_segment
134 ,x_created_by
135 ,x_creation_date
136 ,x_last_updated_by
137 ,x_last_update_date
138 ,x_last_update_login
139 ,x_attribute_category
140 ,x_attribute1
141 ,x_attribute2
142 ,x_attribute3
143 ,x_attribute4
144 ,x_attribute5
145 ,x_attribute6
146 ,x_attribute7
147 ,x_attribute8
148 ,x_attribute9
149 ,x_attribute10
150 ,x_attribute11
151 ,x_attribute12
152 ,x_attribute13
153 ,x_attribute14
154 ,x_attribute15
155 --,x_task_mode (out of scope for patchset 'J')
156 --,x_operation_method (out of scope for patchset 'J')
157 ,x_lpn_selection_criteria
158 ,x_lpn_selection_api_id
159 --,x_catch_secondary_qty (out of scope for patchset 'J')
160 ,x_loc_mtrl_grp_rule_id
161 ,x_lpn_mtrl_grp_rule_id
162 ,x_organization_id
163 ,x_is_in_inventory
164 ,x_subsequent_op_plan_id
165 ,x_consolidation_method_id
166 );
167
168 OPEN C;
169 FETCH C INTO x_rowid;
170 IF (C%NOTFOUND) THEN
171 CLOSE C;
172 RAISE NO_DATA_FOUND;
173 END IF;
174 CLOSE C;
175 END INSERT_ROW;
176 --
177 --
178 PROCEDURE UPDATE_ROW (
179 x_operation_plan_detail_id IN NUMBER
180 ,x_last_updated_by IN NUMBER
181 ,x_last_update_date IN DATE
182 ,x_last_update_login IN NUMBER
183 ,x_operation_plan_id IN NUMBER
184 ,x_operation_type IN NUMBER
185 ,x_operation_sequence IN NUMBER
186 ,x_bulk_pick_type IN NUMBER
187 ,x_drop_lpn_option IN NUMBER
188 ,x_wait_for_group_completion IN VARCHAR2
189 ,x_system_dispatched IN VARCHAR2
190 ,x_op_segment_completed IN VARCHAR2
191 ,x_zone_selection_criteria IN NUMBER
192 ,x_pre_specified_zone_id IN NUMBER
193 ,x_zone_selection_api_id IN NUMBER
194 ,x_sub_selection_criteria IN NUMBER
195 ,x_pre_specified_sub_code IN VARCHAR2
196 ,x_sub_selection_api_id IN NUMBER
197 ,x_loc_selection_criteria IN NUMBER
198 ,x_pre_specified_loc_id IN NUMBER
199 ,x_loc_selection_api_id IN NUMBER
200 ,x_activity_segment IN NUMBER
201 ,x_attribute_category IN VARCHAR2
202 ,x_attribute1 IN VARCHAR2
203 ,x_attribute2 IN VARCHAR2
204 ,x_attribute3 IN VARCHAR2
205 ,x_attribute4 IN VARCHAR2
206 ,x_attribute5 IN VARCHAR2
207 ,x_attribute6 IN VARCHAR2
208 ,x_attribute7 IN VARCHAR2
209 ,x_attribute8 IN VARCHAR2
210 ,x_attribute9 IN VARCHAR2
211 ,x_attribute10 IN VARCHAR2
212 ,x_attribute11 IN VARCHAR2
213 ,x_attribute12 IN VARCHAR2
214 ,x_attribute13 IN VARCHAR2
215 ,x_attribute14 IN VARCHAR2
216 ,x_attribute15 IN VARCHAR2
217 --,x_task_mode IN NUMBER (out of scope for patchset 'J')
218 --,x_operation_method IN NUMBER (out of scope for patchset 'J')
219 ,x_lpn_selection_criteria IN NUMBER
220 ,x_lpn_selection_api_id IN NUMBER
221 --,x_catch_secondary_qty IN NUMBER (out of scope for patchset 'J')
222 ,x_loc_mtrl_grp_rule_id IN NUMBER
223 ,x_lpn_mtrl_grp_rule_id IN NUMBER
224 ,x_organization_id IN NUMBER
225 ,x_is_in_inventory IN VARCHAR2
226 ,x_subsequent_op_plan_id IN NUMBER
227 ,x_consolidation_method_id IN NUMBER
228 )IS
229 BEGIN
230 UPDATE WMS_OP_PLAN_DETAILS SET
231 last_updated_by = x_last_updated_by
232 ,last_update_date = x_last_update_date
233 ,last_update_login = x_last_update_login
234 ,operation_type = x_operation_type
235 ,operation_sequence = x_operation_sequence
236 ,bulk_pick_type = x_bulk_pick_type
237 ,drop_lpn_option = x_drop_lpn_option
238 ,wait_for_group_completion = x_wait_for_group_completion
239 ,system_dispatched = x_system_dispatched
240 ,op_segment_completed = x_op_segment_completed
241 ,zone_selection_criteria = x_zone_selection_criteria
242 ,pre_specified_zone_id = x_pre_specified_zone_id
243 ,zone_selection_api_id = x_zone_selection_api_id
244 ,sub_selection_criteria = x_sub_selection_criteria
245 ,pre_specified_sub_code = x_pre_specified_sub_code
246 ,sub_selection_api_id = x_sub_selection_api_id
247 ,loc_selection_criteria = x_loc_selection_criteria
248 ,pre_specified_loc_id = x_pre_specified_loc_id
249 ,loc_selection_api_id = x_loc_selection_api_id
250 ,activity_segment = x_activity_segment
251 ,attribute_category = x_attribute_category
252 ,attribute1 = x_attribute1
253 ,attribute2 = x_attribute2
254 ,attribute3 = x_attribute3
255 ,attribute4 = x_attribute4
256 ,attribute5 = x_attribute5
257 ,attribute6 = x_attribute6
258 ,attribute7 = x_attribute7
259 ,attribute8 = x_attribute8
260 ,attribute9 = x_attribute9
261 ,attribute10 = x_attribute10
262 ,attribute11 = x_attribute11
263 ,attribute12 = x_attribute12
264 ,attribute13 = x_attribute13
265 ,attribute14 = x_attribute14
266 ,attribute15 = x_attribute15
267 --,task_mode = x_task_mode (out of scope for patchset 'J')
268 --,operation_method = x_operation_method (out of scope for patchset 'J')
269 ,lpn_selection_criteria = x_lpn_selection_criteria
270 ,lpn_selection_api_id = x_lpn_selection_api_id
271 --,catch_secondary_qty = x_catch_secondary_qty (out of scope for patchset 'J')
272 ,loc_mtrl_grp_rule_id = x_loc_mtrl_grp_rule_id
273 ,lpn_mtrl_grp_rule_id = x_lpn_mtrl_grp_rule_id
274 ,organization_id = x_organization_id
275 ,is_in_inventory = x_is_in_inventory
276 ,subsequent_op_plan_id = x_subsequent_op_plan_id
277 ,consolidation_method_id = x_consolidation_method_id
278 WHERE operation_plan_detail_id = x_operation_plan_detail_id
279 AND operation_plan_id = x_operation_plan_id;
280
281 IF (SQL%NOTFOUND) THEN
282 RAISE NO_DATA_FOUND;
283 END IF;
284 END UPDATE_ROW;
285 --
286 PROCEDURE LOAD_ROW (
287 x_operation_plan_detail_id IN NUMBER
288 ,x_owner IN VARCHAR2
289 ,x_last_update_date IN VARCHAR2
290 ,x_operation_plan_id IN NUMBER
291 ,x_operation_type IN NUMBER
292 ,x_operation_sequence IN NUMBER
293 ,x_bulk_pick_type IN NUMBER
294 ,x_drop_lpn_option IN NUMBER
295 ,x_wait_for_group_completion IN VARCHAR2
296 ,x_system_dispatched IN VARCHAR2
297 ,x_op_segment_completed IN VARCHAR2
298 ,x_zone_selection_criteria IN NUMBER
299 ,x_pre_specified_zone_id IN NUMBER
300 ,x_zone_selection_api_id IN NUMBER
301 ,x_sub_selection_criteria IN NUMBER
302 ,x_pre_specified_sub_code IN VARCHAR2
303 ,x_sub_selection_api_id IN NUMBER
304 ,x_loc_selection_criteria IN NUMBER
305 ,x_pre_specified_loc_id IN NUMBER
306 ,x_loc_selection_api_id IN NUMBER
307 ,x_activity_segment IN NUMBER
308 ,x_attribute_category IN VARCHAR2
309 ,x_attribute1 IN VARCHAR2
310 ,x_attribute2 IN VARCHAR2
311 ,x_attribute3 IN VARCHAR2
312 ,x_attribute4 IN VARCHAR2
313 ,x_attribute5 IN VARCHAR2
314 ,x_attribute6 IN VARCHAR2
315 ,x_attribute7 IN VARCHAR2
316 ,x_attribute8 IN VARCHAR2
317 ,x_attribute9 IN VARCHAR2
318 ,x_attribute10 IN VARCHAR2
319 ,x_attribute11 IN VARCHAR2
320 ,x_attribute12 IN VARCHAR2
321 ,x_attribute13 IN VARCHAR2
322 ,x_attribute14 IN VARCHAR2
323 ,x_attribute15 IN VARCHAR2
324 --,x_task_mode IN NUMBER (out of scope for patchset 'J')
325 --,x_operation_method IN NUMBER (out of scope for patchset 'J')
326 ,x_lpn_selection_criteria IN NUMBER
327 ,x_lpn_selection_api_id IN NUMBER
328 --,x_catch_secondary_qty IN NUMBER (out of scope for patchset 'J')
329 ,x_loc_mtrl_grp_rule_id IN NUMBER
330 ,x_lpn_mtrl_grp_rule_id IN NUMBER
331 ,x_organization_id IN NUMBER
332 ,x_is_in_inventory IN VARCHAR2
333 ,x_subsequent_op_plan_id IN NUMBER
334 ,x_consolidation_method_id IN NUMBER
335 ) IS
336 BEGIN
337 DECLARE
338 l_operation_plan_detail_id NUMBER;
339 l_user_id NUMBER := 0;
340 l_row_id VARCHAR2(64);
341 --l_sysdate DATE;
342 BEGIN
343 IF (x_owner = 'SEED') THEN
344 l_user_id := 1;
345 END IF;
346 --
347 --SELECT Sysdate INTO l_sysdate FROM dual ;
348 l_operation_plan_detail_id := fnd_number.canonical_to_number(x_operation_plan_detail_id);
349 WMS_OP_PLAN_DETAILS_PKG.update_row (
350 x_operation_plan_detail_id => l_operation_plan_detail_id
351 ,x_last_updated_by => l_user_id
352 ,x_last_update_date => to_date(x_last_update_date,'yyyy/mm/dd')
353 ,x_last_update_login => 0
354 ,x_operation_plan_id => x_operation_plan_id
355 ,x_operation_type => x_operation_type
356 ,x_operation_sequence => x_operation_sequence
357 ,x_bulk_pick_type => x_bulk_pick_type
358 ,x_drop_lpn_option => x_drop_lpn_option
359 ,x_wait_for_group_completion => x_wait_for_group_completion
363 ,x_pre_specified_zone_id => x_pre_specified_zone_id
360 ,x_system_dispatched => x_system_dispatched
361 ,x_op_segment_completed => x_op_segment_completed
362 ,x_zone_selection_criteria => x_zone_selection_criteria
364 ,x_zone_selection_api_id => x_zone_selection_api_id
365 ,x_sub_selection_criteria => x_sub_selection_criteria
366 ,x_pre_specified_sub_code => x_pre_specified_sub_code
367 ,x_sub_selection_api_id => x_sub_selection_api_id
368 ,x_loc_selection_criteria => x_loc_selection_criteria
369 ,x_pre_specified_loc_id => x_pre_specified_loc_id
370 ,x_loc_selection_api_id => x_loc_selection_api_id
371 ,x_activity_segment => x_activity_segment
372 ,x_attribute_category => x_attribute_category
373 ,x_attribute1 => x_attribute1
374 ,x_attribute2 => x_attribute2
375 ,x_attribute3 => x_attribute3
376 ,x_attribute4 => x_attribute4
377 ,x_attribute5 => x_attribute5
378 ,x_attribute6 => x_attribute6
379 ,x_attribute7 => x_attribute7
380 ,x_attribute8 => x_attribute8
381 ,x_attribute9 => x_attribute9
382 ,x_attribute10 => x_attribute10
383 ,x_attribute11 => x_attribute11
384 ,x_attribute12 => x_attribute12
385 ,x_attribute13 => x_attribute13
386 ,x_attribute14 => x_attribute14
387 ,x_attribute15 => x_attribute15
388 --,x_task_mode => x_task_mode (out of scope for patchset 'J')
389 --,x_operation_method => x_operation_method (out of scope for patchset 'J')
390 ,x_lpn_selection_criteria => x_lpn_selection_criteria
391 ,x_lpn_selection_api_id => x_lpn_selection_api_id
392 --,x_catch_secondary_qty => x_catch_secondary_qty (out of scope for patchset 'J')
393 ,x_loc_mtrl_grp_rule_id => x_loc_mtrl_grp_rule_id
394 ,x_lpn_mtrl_grp_rule_id => x_lpn_mtrl_grp_rule_id
395 ,x_organization_id => x_organization_id
396 ,x_is_in_inventory => x_is_in_inventory
397 ,x_subsequent_op_plan_id => x_subsequent_op_plan_id
398 ,x_consolidation_method_id => x_consolidation_method_id
399 );
400 EXCEPTION
401 WHEN no_data_found THEN
402 WMS_OP_PLAN_DETAILS_PKG.insert_row (
403 x_rowid => l_row_id
404 ,x_operation_plan_detail_id => l_operation_plan_detail_id
405 ,x_last_updated_by => l_user_id
406 ,x_last_update_date => to_date(x_last_update_date,'yyyy/mm/dd')
407 ,x_created_by => l_user_id
408 ,x_creation_date => to_date(x_last_update_date,'yyyy/mm/dd')
409 ,x_last_update_login => 0
410 ,x_operation_plan_id => x_operation_plan_id
411 ,x_operation_type => x_operation_type
412 ,x_operation_sequence => x_operation_sequence
413 ,x_bulk_pick_type => x_bulk_pick_type
414 ,x_drop_lpn_option => x_drop_lpn_option
415 ,x_wait_for_group_completion => x_wait_for_group_completion
416 ,x_system_dispatched => x_system_dispatched
417 ,x_op_segment_completed => x_op_segment_completed
418 ,x_zone_selection_criteria => x_zone_selection_criteria
419 ,x_pre_specified_zone_id => x_pre_specified_zone_id
420 ,x_zone_selection_api_id => x_zone_selection_api_id
421 ,x_sub_selection_criteria => x_sub_selection_criteria
422 ,x_pre_specified_sub_code => x_pre_specified_sub_code
423 ,x_sub_selection_api_id => x_sub_selection_api_id
424 ,x_loc_selection_criteria => x_loc_selection_criteria
425 ,x_pre_specified_loc_id => x_pre_specified_loc_id
426 ,x_loc_selection_api_id => x_loc_selection_api_id
427 ,x_activity_segment => x_activity_segment
428 ,x_attribute_category => x_attribute_category
429 ,x_attribute1 => x_attribute1
430 ,x_attribute2 => x_attribute2
434 ,x_attribute6 => x_attribute6
431 ,x_attribute3 => x_attribute3
432 ,x_attribute4 => x_attribute4
433 ,x_attribute5 => x_attribute5
435 ,x_attribute7 => x_attribute7
436 ,x_attribute8 => x_attribute8
437 ,x_attribute9 => x_attribute9
438 ,x_attribute10 => x_attribute10
439 ,x_attribute11 => x_attribute11
440 ,x_attribute12 => x_attribute12
441 ,x_attribute13 => x_attribute13
442 ,x_attribute14 => x_attribute14
443 ,x_attribute15 => x_attribute15
444 --,x_task_mode => x_task_mode (out of scope for patchset 'J')
445 --,x_operation_method => x_operation_method (out of scope for patchset 'J')
446 ,x_lpn_selection_criteria => x_lpn_selection_criteria
447 ,x_lpn_selection_api_id => x_lpn_selection_api_id
448 --,x_catch_secondary_qty => x_catch_secondary_qty (out of scope for patchset 'J')
449 ,x_loc_mtrl_grp_rule_id => x_loc_mtrl_grp_rule_id
450 ,x_lpn_mtrl_grp_rule_id => x_lpn_mtrl_grp_rule_id
451 ,x_organization_id => x_organization_id
452 ,x_is_in_inventory => x_is_in_inventory
453 ,x_subsequent_op_plan_id => x_subsequent_op_plan_id
454 ,x_consolidation_method_id => x_consolidation_method_id
455 );
456 END;
457 END LOAD_ROW;
458
459
460 --added by Grace 07/28/03
461 PROCEDURE LOCK_ROW (
462 x_operation_plan_detail_id IN NUMBER
463 ,x_operation_plan_id IN NUMBER
464 ,x_operation_type IN NUMBER
465 ,x_operation_sequence IN NUMBER
466 ,x_bulk_pick_type IN NUMBER
467 ,x_drop_lpn_option IN NUMBER
468 ,x_wait_for_group_completion IN VARCHAR2
469 ,x_system_dispatched IN VARCHAR2
470 ,x_op_segment_completed IN VARCHAR2
471 ,x_zone_selection_criteria IN NUMBER
472 ,x_pre_specified_zone_id IN NUMBER
473 ,x_zone_selection_api_id IN NUMBER
474 ,x_sub_selection_criteria IN NUMBER
475 ,x_pre_specified_sub_code IN VARCHAR2
476 ,x_sub_selection_api_id IN NUMBER
477 ,x_loc_selection_criteria IN NUMBER
478 ,x_pre_specified_loc_id IN NUMBER
479 ,x_loc_selection_api_id IN NUMBER
480 ,x_activity_segment IN NUMBER
481 ,x_attribute_category IN VARCHAR2
482 ,x_attribute1 IN VARCHAR2
483 ,x_attribute2 IN VARCHAR2
484 ,x_attribute3 IN VARCHAR2
485 ,x_attribute4 IN VARCHAR2
486 ,x_attribute5 IN VARCHAR2
487 ,x_attribute6 IN VARCHAR2
488 ,x_attribute7 IN VARCHAR2
489 ,x_attribute8 IN VARCHAR2
490 ,x_attribute9 IN VARCHAR2
491 ,x_attribute10 IN VARCHAR2
492 ,x_attribute11 IN VARCHAR2
493 ,x_attribute12 IN VARCHAR2
494 ,x_attribute13 IN VARCHAR2
495 ,x_attribute14 IN VARCHAR2
496 ,x_attribute15 IN VARCHAR2
497 --,x_task_mode IN NUMBER (out of scope for patchset 'J')
498 --,x_operation_method IN NUMBER (out of scope for patchset 'J')
499 ,x_lpn_selection_criteria IN NUMBER
500 ,x_lpn_selection_api_id IN NUMBER
504 ,x_organization_id IN NUMBER
501 --,x_catch_secondary_qty IN NUMBER (out of scope for patchset 'J')
502 ,x_loc_mtrl_grp_rule_id IN NUMBER
503 ,x_lpn_mtrl_grp_rule_id IN NUMBER
505 ,x_is_in_inventory IN VARCHAR2
506 ,x_subsequent_op_plan_id IN NUMBER
507 ,x_consolidation_method_id IN NUMBER
508 ) IS
509 CURSOR C IS SELECT
510 operation_plan_id
511 ,operation_type
512 ,operation_sequence
513 ,bulk_pick_type
514 ,drop_lpn_option
515 ,wait_for_group_completion
516 ,system_dispatched
517 ,op_segment_completed
518 ,zone_selection_criteria
519 ,pre_specified_zone_id
520 ,zone_selection_api_id
521 ,sub_selection_criteria
522 ,pre_specified_sub_code
523 ,sub_selection_api_id
524 ,loc_selection_criteria
525 ,pre_specified_loc_id
526 ,loc_selection_api_id
527 ,activity_segment
528 ,attribute_category
529 ,attribute1
530 ,attribute2
531 ,attribute3
532 ,attribute4
533 ,attribute5
534 ,attribute6
535 ,attribute7
536 ,attribute8
537 ,attribute9
538 ,attribute10
539 ,attribute11
540 ,attribute12
541 ,attribute13
542 ,attribute14
543 ,attribute15
544 --,x_task_mode IN NUMBER (out of scope for patchset 'J')
545 --,x_operation_method IN NUMBER (out of scope for patchset 'J')
546 ,lpn_selection_criteria
547 ,lpn_selection_api_id
548 --,x_catch_secondary_qty IN NUMBER (out of scope for patchset 'J')
549 ,loc_mtrl_grp_rule_id
550 ,lpn_mtrl_grp_rule_id
551 ,organization_id
552 ,is_in_inventory
553 ,subsequent_op_plan_id
554 ,consolidation_method_id
555 from wms_op_plan_details
556 where operation_plan_detail_id = x_operation_plan_detail_id
557 for UPDATE of OPERATION_PLAN_DETAIL_ID NOWAIT;
558 recinfo C%rowtype;
559
560
561 BEGIN
562 OPEN C;
563 fetch C into recinfo;
564 if(C%NOTFOUND) then
565 close C;
566 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
567 APP_EXCEPTION.Raise_Exception;
568 end if;
569 CLOSE C;
570
571 if ( (recinfo.operation_plan_id = x_operation_plan_id)
572 AND (recinfo.operation_type = X_operation_type)
573 AND (recinfo.operation_sequence = X_operation_sequence)
574 AND ( (recinfo.bulk_pick_type = X_bulk_pick_type)
575 OR ( (recinfo.bulk_pick_type is null)
576 AND (X_bulk_pick_type is null)))
577 AND ( (recinfo.drop_lpn_option = X_drop_lpn_option)
578 OR ( (recinfo.drop_lpn_option is null)
582 AND (X_wait_for_group_completion is null)))
579 AND (X_drop_lpn_option is null)))
580 AND ( (recinfo.wait_for_group_completion = X_wait_for_group_completion)
581 OR ( (recinfo.wait_for_group_completion is null)
583 AND ( (recinfo.system_dispatched = X_system_dispatched)
584 OR ( (recinfo.system_dispatched is null)
585 AND (X_system_dispatched is null)))
586 AND ( (recinfo.op_segment_completed = X_op_segment_completed)
587 OR ( (recinfo.op_segment_completed is null)
588 AND (X_op_segment_completed is null)))
589 AND ( (recinfo.zone_selection_criteria = X_zone_selection_criteria)
590 OR ( (recinfo.zone_selection_criteria is null)
591 AND (X_zone_selection_criteria is null)))
592 AND ( (recinfo.pre_specified_zone_id = X_pre_specified_zone_id)
593 OR ( (recinfo.pre_specified_zone_id is null)
594 AND (X_pre_specified_zone_id is null)))
595 AND ( (recinfo.zone_selection_api_id = X_zone_selection_api_id)
596 OR ( (recinfo.zone_selection_api_id is null)
597 AND (X_zone_selection_api_id is null)))
598 AND ( (recinfo.sub_selection_criteria = X_sub_selection_criteria)
599 OR ( (recinfo.sub_selection_criteria is null)
600 AND (X_sub_selection_criteria is null)))
601 AND ( (recinfo.pre_specified_sub_code= X_pre_specified_sub_code)
602 OR ( (recinfo.pre_specified_sub_code is null)
603 AND (X_pre_specified_sub_code is null)))
604 AND ( (recinfo.sub_selection_api_id = X_sub_selection_api_id)
605 OR ( (recinfo.sub_selection_api_id is null)
606 AND (X_sub_selection_api_id is null)))
607 AND ( (recinfo.loc_selection_criteria = X_loc_selection_criteria)
608 OR ( (recinfo.loc_selection_criteria is null)
609 AND (X_loc_selection_criteria is null)))
610 AND ( (recinfo.pre_specified_loc_id = X_pre_specified_loc_id)
611 OR ( (recinfo.pre_specified_loc_id is null)
612 AND (X_pre_specified_loc_id is null)))
613 AND ( (recinfo.loc_selection_api_id = X_loc_selection_api_id)
614 OR ( (recinfo.loc_selection_api_id is null)
615 AND (X_loc_selection_api_id is null)))
616 AND ( (recinfo.activity_segment = X_activity_segment)
617 OR ( (recinfo.activity_segment is null)
618 AND (X_activity_segment is null)))
619 AND ( (recinfo.attribute_category = X_attribute_category)
620 OR ( (recinfo.attribute_category is null)
621 AND (X_attribute_category is null)))
622 AND ( (recinfo.attribute1 = X_attribute1)
623 OR ( (recinfo.attribute1 is null)
624 AND (X_attribute1 is null)))
625 AND ( (recinfo.attribute2 = X_attribute2)
626 OR ( (recinfo.attribute2 is null)
627 AND (X_attribute2 is null)))
628 AND ( (recinfo.attribute3 = X_attribute3)
629 OR ( (recinfo.attribute3 is null)
630 AND (X_attribute3 is null)))
631 AND ( (recinfo.attribute4 = X_attribute4)
632 OR ( (recinfo.attribute4 is null)
633 AND (X_attribute4 is null)))
634 AND ( (recinfo.attribute5 = X_attribute5)
635 OR ( (recinfo.attribute5 is null)
636 AND (X_attribute5 is null)))
637 AND ( (recinfo.attribute6 = X_attribute6)
638 OR ( (recinfo.attribute6 is null)
639 AND (X_attribute6 is null)))
640 AND ( (recinfo.attribute6 = X_attribute6)
641 OR ( (recinfo.attribute6 is null)
642 AND (X_attribute6 is null)))
643 AND ( (recinfo.attribute7 = X_attribute7)
644 OR ( (recinfo.attribute7 is null)
645 AND (X_attribute7 is null)))
646 AND ( (recinfo.attribute8 = X_attribute8)
647 OR ( (recinfo.attribute8 is null)
648 AND (X_attribute8 is null)))
649 AND ( (recinfo.attribute9 = X_attribute9)
650 OR ( (recinfo.attribute9 is null)
651 AND (X_attribute9 is null)))
652 AND ( (recinfo.attribute10 = x_attribute10)
653 OR ( (recinfo.attribute10 is null)
654 AND (x_attribute10 is null)))
655 AND ( (recinfo.attribute13 = x_attribute13)
656 OR ( (recinfo.attribute13 is null)
657 AND (x_attribute13 is null)))
658 AND ( (recinfo.attribute11 = x_attribute11)
659 OR ( (recinfo.attribute11 is null)
660 AND (x_attribute11 is null)))
661 AND ( (recinfo.attribute12 = x_attribute12)
662 OR ( (recinfo.attribute12 is null)
663 AND (x_attribute12 is null)))
664 AND ( (recinfo.attribute14 = x_attribute14)
665 OR ( (recinfo.attribute14 is null)
666 AND (x_attribute14 is null)))
667 AND ( (recinfo.attribute15 = x_attribute15)
668 OR ( (recinfo.attribute15 is null)
669 AND (x_attribute15 is null)))
670 AND ( (recinfo.lpn_selection_criteria = x_lpn_selection_criteria)
671 OR ( (recinfo.lpn_selection_criteria is null)
672 AND (x_lpn_selection_criteria is null)))
673 AND ( (recinfo.lpn_selection_api_id = x_lpn_selection_api_id)
674 OR ( (recinfo.lpn_selection_api_id is null)
675 AND (x_lpn_selection_api_id is null)))
676 AND ( (recinfo.loc_mtrl_grp_rule_id = x_loc_mtrl_grp_rule_id)
677 OR ( (recinfo.loc_mtrl_grp_rule_id is null)
678 AND (x_loc_mtrl_grp_rule_id is null)))
679 AND ( (recinfo.lpn_mtrl_grp_rule_id = x_lpn_mtrl_grp_rule_id)
680 OR ( (recinfo.lpn_mtrl_grp_rule_id is null)
681 AND (x_lpn_mtrl_grp_rule_id is null)))
682 AND ( (recinfo.organization_id = x_organization_id)
683 OR ( (recinfo.organization_id is null)
684 AND (x_organization_id is null)))
685 AND ( (recinfo.is_in_inventory = x_is_in_inventory)
686 OR ( (recinfo.is_in_inventory is null)
687 AND (x_is_in_inventory is null)))
688 AND ( (recinfo.subsequent_op_plan_id = x_subsequent_op_plan_id)
689 OR ( (recinfo.subsequent_op_plan_id is null)
690 AND (x_subsequent_op_plan_id is null)))
691 AND ( (recinfo.consolidation_method_id = x_consolidation_method_id)
692 OR ( (recinfo.consolidation_method_id is null)
693 AND (x_consolidation_method_id is null)))
694 ) then
695 return;
696
697 else
698 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
699 APP_EXCEPTION.Raise_Exception;
700 end if;
701
702 END lock_row;
703
704
705
706
707 -- Added by Grace Xiao 07/28/03
708
709 PROCEDURE delete_row (
710 x_operation_plan_detail_id IN NUMBER
711 ) IS
712
713 BEGIN
714
715 delete from WMS_OP_PLAN_DETAILS
716 where OPERATION_PLAN_DETAIL_ID = X_OPERATION_PLAN_DETAIL_ID;
717
718 if (sql%notfound) then
719 raise no_data_found;
720 end if;
721
722 END delete_row;
723
724
725
726 END WMS_OP_PLAN_DETAILS_PKG;
[Home] [Help]