[Home] [Help]
PACKAGE BODY: APPS.WMS_OP_PLANS_PKG
Source
1 PACKAGE BODY WMS_OP_PLANS_PKG AS
2 /* $Header: WMSOPTBB.pls 120.1 2006/06/19 06:55:28 amohamme noship $ */
3
4 --
5 PROCEDURE INSERT_ROW (
6 x_rowid IN OUT nocopy VARCHAR2
7 ,x_operation_plan_id IN NUMBER
8 ,x_last_updated_by IN NUMBER
9 ,x_last_update_date IN DATE
10 ,x_created_by IN NUMBER
11 ,x_creation_date IN DATE
12 ,x_last_update_login IN NUMBER
13 ,x_operation_plan_name IN VARCHAR2
14 ,x_language IN VARCHAR2
15 ,x_source_lang IN VARCHAR2
16 ,x_description IN VARCHAR2
17 ,x_system_task_type IN NUMBER
18 ,x_organization_id IN NUMBER
19 ,x_user_defined IN VARCHAR2
20 ,x_enabled_flag IN VARCHAR2
21 ,x_effective_date_from IN DATE
22 ,x_effective_date_to IN DATE
23 ,x_activity_type_id IN NUMBER
24 ,x_common_to_all_org IN VARCHAR2
25 ,x_plan_type_id IN NUMBER
26 ,x_attribute_category IN VARCHAR2 DEFAULT NULL
27 ,x_attribute1 IN VARCHAR2 DEFAULT NULL
28 ,x_attribute2 IN VARCHAR2 DEFAULT NULL
29 ,x_attribute3 IN VARCHAR2 DEFAULT NULL
30 ,x_attribute4 IN VARCHAR2 DEFAULT NULL
31 ,x_attribute5 IN VARCHAR2 DEFAULT NULL
32 ,x_attribute6 IN VARCHAR2 DEFAULT NULL
33 ,x_attribute7 IN VARCHAR2 DEFAULT NULL
34 ,x_attribute8 IN VARCHAR2 DEFAULT NULL
35 ,x_attribute9 IN VARCHAR2 DEFAULT NULL
36 ,x_attribute10 IN VARCHAR2 DEFAULT NULL
37 ,x_attribute11 IN VARCHAR2 DEFAULT NULL
38 ,x_attribute12 IN VARCHAR2 DEFAULT NULL
39 ,x_attribute13 IN VARCHAR2 DEFAULT NULL
40 ,x_attribute14 IN VARCHAR2 DEFAULT NULL
41 ,x_attribute15 IN VARCHAR2 DEFAULT NULL
42 ,x_default_flag IN VARCHAR2
43 ,x_template_flag IN VARCHAR2
44 ,x_crossdock_to_wip_flag IN VARCHAR2
45 )IS
46 CURSOR C IS SELECT ROWID FROM WMS_OP_PLANS_B
47 WHERE operation_plan_id = x_operation_plan_id;
48 BEGIN
49
50 INSERT INTO WMS_OP_PLANS_B (
51 operation_plan_id
52 ,last_updated_by
53 ,last_update_date
54 ,created_by
55 ,creation_date
56 ,last_update_login
57 ,system_task_type
58 ,organization_id
59 ,user_defined
60 ,enabled_flag
61 ,effective_date_from
62 ,effective_date_to
63 ,activity_type_id
64 ,common_to_all_org
65 ,plan_type_id
66 ,attribute_category
67 ,attribute1
68 ,attribute2
69 ,attribute3
70 ,attribute4
71 ,attribute5
72 ,attribute6
73 ,attribute7
74 ,attribute8
75 ,attribute9
76 ,attribute10
77 ,attribute11
78 ,attribute12
79 ,attribute13
80 ,attribute14
81 ,attribute15
82 ,default_flag
83 ,template_flag
84 ,crossdock_to_wip_flag
85 ) values (
86 x_operation_plan_id
87 ,x_last_updated_by
88 ,x_last_update_date
89 ,x_created_by
90 ,x_creation_date
91 ,x_last_update_login
92 ,x_system_task_type
93 ,x_organization_id
94 ,x_user_defined
95 ,x_enabled_flag
96 ,x_effective_date_from
97 ,x_effective_date_to
98 ,x_activity_type_id
99 ,x_common_to_all_org
100 ,x_plan_type_id
101 ,x_attribute_category
102 ,x_attribute1
103 ,x_attribute2
104 ,x_attribute3
105 ,x_attribute4
106 ,x_attribute5
107 ,x_attribute6
108 ,x_attribute7
109 ,x_attribute8
110 ,x_attribute9
111 ,x_attribute10
112 ,x_attribute11
113 ,x_attribute12
114 ,x_attribute13
115 ,x_attribute14
116 ,x_attribute15
117 ,x_default_flag
118 ,x_template_flag
119 ,x_crossdock_to_wip_flag
120 );
121
122 insert into WMS_OP_PLANS_TL (
123 operation_plan_id
124 ,last_updated_by
125 ,last_update_date
126 ,created_by
127 ,creation_date
128 ,last_update_login
129 ,operation_plan_name
130 ,description
131 ,language
132 ,source_lang
133 ) select
134 x_operation_plan_id
135 ,x_last_updated_by
136 ,x_last_update_date
137 ,x_created_by
138 ,x_creation_date
139 ,x_last_update_login
140 ,x_operation_plan_name
141 ,x_description
142 ,l.language_code
143 ,userenv('LANG')
144 from FND_LANGUAGES L
145 where L.INSTALLED_FLAG in ('I', 'B')
146 and not exists
147 (select NULL
148 from WMS_OP_PLANS_TL T
149 where T.OPERATION_PLAN_ID = X_OPERATION_PLAN_ID
150 and T.LANGUAGE = L.LANGUAGE_CODE);
151
152 OPEN C;
153 FETCH C INTO x_rowid;
154 IF (C%NOTFOUND) THEN
155 CLOSE C;
156 RAISE NO_DATA_FOUND;
157 END IF;
158 CLOSE C;
159 END INSERT_ROW;
160 --
161 --
162 PROCEDURE UPDATE_ROW (
163 x_operation_plan_id IN NUMBER
164 ,x_last_updated_by IN NUMBER
165 ,x_last_update_date IN DATE
166 ,x_last_update_login IN NUMBER
167 ,x_operation_plan_name IN VARCHAR2
168 ,x_language IN VARCHAR2
169 ,x_source_lang IN VARCHAR2
170 ,x_description IN VARCHAR2
171 ,x_system_task_type IN NUMBER
172 ,x_organization_id IN NUMBER
173 ,x_user_defined IN VARCHAR2
174 ,x_enabled_flag IN VARCHAR2
175 ,x_effective_date_from IN DATE
176 ,x_effective_date_to IN DATE
177 ,x_activity_type_id IN NUMBER
178 ,x_common_to_all_org IN VARCHAR2
179 ,x_plan_type_id IN NUMBER
180 ,x_attribute_category IN VARCHAR2 DEFAULT NULL
181 ,x_attribute1 IN VARCHAR2 DEFAULT NULL
182 ,x_attribute2 IN VARCHAR2 DEFAULT NULL
183 ,x_attribute3 IN VARCHAR2 DEFAULT NULL
184 ,x_attribute4 IN VARCHAR2 DEFAULT NULL
185 ,x_attribute5 IN VARCHAR2 DEFAULT NULL
186 ,x_attribute6 IN VARCHAR2 DEFAULT NULL
187 ,x_attribute7 IN VARCHAR2 DEFAULT NULL
188 ,x_attribute8 IN VARCHAR2 DEFAULT NULL
189 ,x_attribute9 IN VARCHAR2 DEFAULT NULL
190 ,x_attribute10 IN VARCHAR2 DEFAULT NULL
191 ,x_attribute11 IN VARCHAR2 DEFAULT NULL
192 ,x_attribute12 IN VARCHAR2 DEFAULT NULL
193 ,x_attribute13 IN VARCHAR2 DEFAULT NULL
194 ,x_attribute14 IN VARCHAR2 DEFAULT NULL
195 ,x_attribute15 IN VARCHAR2 DEFAULT NULL
196 ,x_default_flag IN VARCHAR2
197 ,x_template_flag IN VARCHAR2
198 ,x_crossdock_to_wip_flag IN VARCHAR2
199 )IS
200 BEGIN
201 UPDATE WMS_OP_PLANS_B SET
202 last_updated_by = x_last_updated_by
203 ,last_update_date = x_last_update_date
204 ,last_update_login = x_last_update_login
205 ,system_task_type = x_system_task_type
206 ,organization_id = x_organization_id
207 ,user_defined = x_user_defined
208 ,enabled_flag = x_enabled_flag
209 ,effective_date_from = x_effective_date_from
210 ,effective_date_to = x_effective_date_to
211 ,activity_type_id = x_activity_type_id
212 ,common_to_all_org = x_common_to_all_org
213 ,plan_type_id = x_plan_type_id
214 ,attribute_category = x_attribute_category
215 ,attribute1 = x_attribute1
216 ,attribute2 = x_attribute2
217 ,attribute3 = x_attribute3
218 ,attribute4 = x_attribute4
219 ,attribute5 = x_attribute5
220 ,attribute6 = x_attribute6
221 ,attribute7 = x_attribute7
222 ,attribute8 = x_attribute8
223 ,attribute9 = x_attribute9
224 ,attribute10 = x_attribute10
225 ,attribute11 = x_attribute11
226 ,attribute12 = x_attribute12
227 ,attribute13 = x_attribute13
228 ,attribute14 = x_attribute14
229 ,attribute15 = x_attribute15
230 ,default_flag = x_default_flag
231 ,template_flag = x_template_flag
232 ,crossdock_to_wip_flag = x_crossdock_to_wip_flag
233 WHERE operation_plan_id = x_operation_plan_id;
234
235 if (sql%notfound) then
236 raise no_data_found;
237 end if;
238
239 update WMS_OP_PLANS_TL set
240 operation_plan_name = x_operation_plan_name,
241 description = x_description,
242 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
243 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
244 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
245 SOURCE_LANG = userenv('LANG')
246 where OPERATION_PLAN_ID = X_OPERATION_PLAN_ID
247 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
248
249 END UPDATE_ROW;
250 --
251 PROCEDURE LOAD_ROW(
252 x_operation_plan_id IN NUMBER
253 ,x_owner IN VARCHAR2
254 ,x_last_update_date IN VARCHAR2
255 ,x_system_task_type IN NUMBER
256 ,x_organization_id IN NUMBER
257 ,x_user_defined IN VARCHAR2
258 ,x_enabled_flag IN VARCHAR2
259 ,x_effective_date_from IN DATE
260 ,x_effective_date_to IN DATE
261 ,x_activity_type_id IN NUMBER
262 ,x_common_to_all_org IN VARCHAR2
263 ,x_plan_type_id IN NUMBER
264 ,x_attribute_category IN VARCHAR2 DEFAULT NULL
265 ,x_attribute1 IN VARCHAR2 DEFAULT NULL
266 ,x_attribute2 IN VARCHAR2 DEFAULT NULL
267 ,x_attribute3 IN VARCHAR2 DEFAULT NULL
268 ,x_attribute4 IN VARCHAR2 DEFAULT NULL
269 ,x_attribute5 IN VARCHAR2 DEFAULT NULL
270 ,x_attribute6 IN VARCHAR2 DEFAULT NULL
271 ,x_attribute7 IN VARCHAR2 DEFAULT NULL
272 ,x_attribute8 IN VARCHAR2 DEFAULT NULL
273 ,x_attribute9 IN VARCHAR2 DEFAULT NULL
274 ,x_attribute10 IN VARCHAR2 DEFAULT NULL
275 ,x_attribute11 IN VARCHAR2 DEFAULT NULL
276 ,x_attribute12 IN VARCHAR2 DEFAULT NULL
277 ,x_attribute13 IN VARCHAR2 DEFAULT NULL
278 ,x_attribute14 IN VARCHAR2 DEFAULT NULL
279 ,x_attribute15 IN VARCHAR2 DEFAULT NULL
280 ,x_operation_plan_name IN VARCHAR2
281 ,x_description IN VARCHAR2
282 ,x_default_flag IN VARCHAR2
283 ,x_template_flag IN VARCHAR2
284 ,x_crossdock_to_wip_flag IN VARCHAR2
285 ,x_custom_mode IN VARCHAR2
286 ) IS
287
288 l_operation_plan_id NUMBER;
289 l_row_id VARCHAR2(64);
290 f_luby number; -- entity owner in file
291 f_ludate date; -- entity update date in file
292 db_luby number; -- entity owner in db
293 db_ludate date; -- entity update date in db
294 BEGIN
295 -- Translate owner to file_last_updated_by
296 f_luby := fnd_load_util.owner_id(X_OWNER);
297
298 -- Translate char last_update_date to date
299 f_ludate := nvl(to_date(X_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);
300 BEGIN
301 l_operation_plan_id := fnd_number.canonical_to_number(x_operation_plan_id);
302 select LAST_UPDATED_BY,LAST_UPDATE_DATE
303 INTO db_luby,db_ludate
304 from wms_op_plans_B where
305 operation_plan_id = l_operation_plan_id ;
306 -- Test for customization and version
307 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
308 db_ludate, X_CUSTOM_MODE)) then
309 -- Update existing row
310 WMS_OP_PLANS_PKG.update_row (
311 x_operation_plan_id => l_operation_plan_id
312 ,x_last_updated_by => f_luby
313 ,x_last_update_date => f_ludate
314 ,x_last_update_login => 0
315 ,x_operation_plan_name => x_operation_plan_name
316 ,x_language => NULL
317 ,x_source_lang => NULL
318 ,x_description => x_description
319 ,x_system_task_type => x_system_task_type
320 ,x_organization_id => x_organization_id
321 ,x_user_defined => x_user_defined
322 ,x_enabled_flag => x_enabled_flag
323 ,x_effective_date_from => x_effective_date_from
324 ,x_effective_date_to => x_effective_date_to
325 ,x_activity_type_id => x_activity_type_id
326 ,x_common_to_all_org => x_common_to_all_org
327 ,x_plan_type_id => x_plan_type_id
328 ,x_attribute_category => x_attribute_category
329 ,x_attribute1 => x_attribute1
330 ,x_attribute2 => x_attribute2
331 ,x_attribute3 => x_attribute3
332 ,x_attribute4 => x_attribute4
333 ,x_attribute5 => x_attribute5
334 ,x_attribute6 => x_attribute6
335 ,x_attribute7 => x_attribute7
336 ,x_attribute8 => x_attribute8
337 ,x_attribute9 => x_attribute9
338 ,x_attribute10 => x_attribute10
339 ,x_attribute11 => x_attribute11
340 ,x_attribute12 => x_attribute12
341 ,x_attribute13 => x_attribute13
342 ,x_attribute14 => x_attribute14
343 ,x_attribute15 => x_attribute15
344 ,x_default_flag => x_default_flag
345 ,x_template_flag => x_template_flag
346 ,x_crossdock_to_wip_flag => x_crossdock_to_wip_flag
347 );
348 end if;
349 EXCEPTION
350 WHEN no_data_found THEN
351 -- Record doesn't exist - insert in all cases
352 WMS_OP_PLANS_PKG.insert_row (
353 x_rowid => l_row_id
354 ,x_operation_plan_id => l_operation_plan_id
355 ,x_last_updated_by => f_luby
356 ,x_last_update_date => f_ludate
357 ,x_created_by => f_luby
358 ,x_creation_date => f_ludate
359 ,x_last_update_login => 0
360 ,x_operation_plan_name => x_operation_plan_name
361 ,x_language => NULL
362 ,x_source_lang => NULL
363 ,x_description => x_description
364 ,x_system_task_type => x_system_task_type
365 ,x_organization_id => x_organization_id
366 ,x_user_defined => x_user_defined
367 ,x_enabled_flag => x_enabled_flag
368 ,x_effective_date_from => x_effective_date_from
369 ,x_effective_date_to => x_effective_date_to
370 ,x_activity_type_id => x_activity_type_id
371 ,x_common_to_all_org => x_common_to_all_org
372 ,x_plan_type_id => x_plan_type_id
373 ,x_attribute_category => x_attribute_category
374 ,x_attribute1 => x_attribute1
375 ,x_attribute2 => x_attribute2
376 ,x_attribute3 => x_attribute3
377 ,x_attribute4 => x_attribute4
378 ,x_attribute5 => x_attribute5
379 ,x_attribute6 => x_attribute6
380 ,x_attribute7 => x_attribute7
381 ,x_attribute8 => x_attribute8
382 ,x_attribute9 => x_attribute9
383 ,x_attribute10 => x_attribute10
384 ,x_attribute11 => x_attribute11
385 ,x_attribute12 => x_attribute12
386 ,x_attribute13 => x_attribute13
387 ,x_attribute14 => x_attribute14
388 ,x_attribute15 => x_attribute15
389 ,x_default_flag => x_default_flag
390 ,x_template_flag => x_template_flag
391 ,x_crossdock_to_wip_flag => x_crossdock_to_wip_flag
392 );
393 END;
394 END LOAD_ROW;
395
396
397 -- Added by Grace Xiao 07/28/03
398
399 PROCEDURE delete_row (
400 x_operation_plan_id IN NUMBER
401 ) IS
402
403 BEGIN
404
405 delete from WMS_OP_PLANS_B
406 where OPERATION_PLAN_ID = X_OPERATION_PLAN_ID;
407
408 if (sql%notfound) then
409 raise no_data_found;
410 end if;
411
412 delete from WMS_OP_PLANS_TL
413 where operation_plan_id = X_OPERATION_PLAN_ID;
414
415 if (sql%notfound) then
416 raise no_data_found;
417 end if;
418
419 END delete_row;
420
421
422 -- Added by Grace Xiao 07/28/03
423
424 PROCEDURE lock_row (
425 x_operation_plan_id IN NUMBER
426 ,x_operation_plan_name IN VARCHAR2
427 ,x_description IN VARCHAR2
428 ,x_system_task_type IN NUMBER
429 ,x_organization_id IN NUMBER
430 ,x_user_defined IN VARCHAR2
431 ,x_enabled_flag IN VARCHAR2
432 ,x_effective_date_from IN DATE
433 ,x_effective_date_to IN DATE
434 ,x_activity_type_id IN NUMBER
435 ,x_common_to_all_org IN VARCHAR2
436 ,x_plan_type_id IN NUMBER
437 ,x_attribute_category IN VARCHAR2 DEFAULT NULL
438 ,x_attribute1 IN VARCHAR2 DEFAULT NULL
439 ,x_attribute2 IN VARCHAR2 DEFAULT NULL
440 ,x_attribute3 IN VARCHAR2 DEFAULT NULL
441 ,x_attribute4 IN VARCHAR2 DEFAULT NULL
442 ,x_attribute5 IN VARCHAR2 DEFAULT NULL
443 ,x_attribute6 IN VARCHAR2 DEFAULT NULL
447 ,x_attribute10 IN VARCHAR2 DEFAULT NULL
444 ,x_attribute7 IN VARCHAR2 DEFAULT NULL
445 ,x_attribute8 IN VARCHAR2 DEFAULT NULL
446 ,x_attribute9 IN VARCHAR2 DEFAULT NULL
448 ,x_attribute11 IN VARCHAR2 DEFAULT NULL
449 ,x_attribute12 IN VARCHAR2 DEFAULT NULL
450 ,x_attribute13 IN VARCHAR2 DEFAULT NULL
451 ,x_attribute14 IN VARCHAR2 DEFAULT NULL
452 ,x_attribute15 IN VARCHAR2 DEFAULT NULL
453 ,x_default_flag IN VARCHAR2
454 ,x_template_flag IN VARCHAR2
455 ,x_crossdock_to_wip_flag IN VARCHAR2
456 ) IS
457 cursor C IS SELECT
458 system_task_type
459 ,organization_id
460 ,user_defined
461 ,enabled_flag
462 ,effective_date_from
463 ,effective_date_to
464 ,activity_type_id
465 ,common_to_all_org
466 ,plan_type_id
467 ,attribute_category
468 ,attribute1
469 ,attribute2
470 ,attribute3
471 ,attribute4
472 ,attribute5
473 ,attribute6
474 ,attribute7
475 ,attribute8
476 ,attribute9
477 ,attribute10
478 ,attribute11
479 ,attribute12
480 ,attribute13
481 ,attribute14
482 ,attribute15
483 ,default_flag
484 ,template_flag
485 ,crossdock_to_wip_flag
486 from wms_op_plans_b
487 where operation_plan_id = x_operation_plan_id
488 for UPDATE of OPERATION_PLAN_ID NOWAIT;
489 recinfo C%rowtype;
490
491
492 cursor c1 is select
493 operation_plan_name,
494 description,
495 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
496 from wms_op_plans_tl
497 where operation_plan_id = x_operation_plan_id
498 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
499 for UPDATE of OPERATION_PLAN_ID NOWAIT;
500 --recinfo1 c1%rowtype;
501
502 BEGIN
503 OPEN C;
504 fetch C into recinfo;
505 if(C%NOTFOUND) then
506 close C;
507 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
508 APP_EXCEPTION.Raise_Exception;
509 end if;
510 CLOSE C;
511
512 if ( ((recinfo.SYSTEM_TASK_TYPE = x_system_task_type)
513 OR ( (recinfo.SYSTEM_TASK_TYPE is null)
514 AND (x_system_task_type is null)))
515 AND ( (recinfo.ACTIVITY_TYPE_ID = x_activity_type_id)
516 OR ( (recinfo.ACTIVITY_TYPE_ID is null)
517 AND (x_activity_type_id is null)))
518 AND ( (recinfo.PLAN_TYPE_ID = x_plan_type_id)
519 OR ( (recinfo.PLAN_TYPE_ID is null)
520 AND (x_plan_type_id is null)))
521 AND ( (recinfo.COMMON_TO_ALL_ORG = x_common_to_all_org)
522 OR ( (recinfo.COMMON_TO_ALL_ORG is null)
523 AND (X_COMMON_TO_ALL_ORG is null)))
524 AND ( (recinfo.EFFECTIVE_DATE_FROM = X_EFFECTIVE_DATE_FROM)
525 OR ( (recinfo.effective_date_from is null)
526 AND (x_effective_date_from is null)))
527 AND ( (recinfo.EFFECTIVE_DATE_TO = X_EFFECTIVE_DATE_TO)
528 OR ( (recinfo.effective_date_to is null)
529 AND (x_effective_date_to is null)))
530 AND ( (recinfo.ORGANIZATION_ID = X_ORGANIZATION_ID)
531 OR ( (recinfo.ORGANIZATION_ID is null)
532 AND (x_ORGANIZATION_ID is null)))
533 AND ( (recinfo.USER_DEFINED = X_USER_DEFINED)
534 OR ( (recinfo.USER_DEFINED is null)
535 AND (x_USER_DEFINED is null)))
536 AND ( (recinfo.ENABLED_FLAG = X_ENABLED_FLAG)
537 OR ( (recinfo.ENABLED_FLAG is null)
538 AND (x_ENABLED_FLAG is null)))
539 AND ( (recinfo.TEMPLATE_FLAG = X_TEMPLATE_FLAG)
540 OR ( (recinfo.TEMPLATE_FLAG is null)
541 AND (x_TEMPLATE_FLAG is null)))
542 AND ( (recinfo.crossdock_to_wip_flag = x_crossdock_to_wip_flag)
543 OR ( (recinfo.crossdock_to_wip_flag is null)
544 AND (x_crossdock_to_wip_flag is null)))
545 AND ( (recinfo.DEFAULT_FLAG = X_DEFAULT_FLAG)
546 OR ( (recinfo.DEFAULT_FLAG is null)
547 AND (x_DEFAULT_FLAG is null)))
548 AND ( (recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
549 OR ( (recinfo.ATTRIBUTE_CATEGORY is null)
550 AND (X_ATTRIBUTE_CATEGORY is null)))
551 AND ( (recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
552 OR ( (recinfo.ATTRIBUTE1 is null)
553 AND (X_ATTRIBUTE1 is null)))
554 AND ( (recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
555 OR ( (recinfo.ATTRIBUTE10 is null)
556 AND (X_ATTRIBUTE10 is null)))
557 AND ( (recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
561 OR ( (recinfo.ATTRIBUTE8 is null)
558 OR ( (recinfo.ATTRIBUTE11 is null)
559 AND (X_ATTRIBUTE11 is null)))
560 AND ( (recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
562 AND (X_ATTRIBUTE8 is null)))
563 AND ( (recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
564 OR ( (recinfo.ATTRIBUTE9 is null)
565 AND (X_ATTRIBUTE9 is null)))
566 AND ( (recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
567 OR ( (recinfo.ATTRIBUTE15 is null)
568 AND (X_ATTRIBUTE15 is null)))
569 AND ( (recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
570 OR ( (recinfo.ATTRIBUTE14 is null)
571 AND (X_ATTRIBUTE14 is null)))
572 AND ( (recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
573 OR ( (recinfo.ATTRIBUTE3 is null)
574 AND (X_ATTRIBUTE3 is null)))
575 AND ( (recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
576 OR ( (recinfo.ATTRIBUTE4 is null)
577 AND (X_ATTRIBUTE4 is null)))
578 AND ( (recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
579 OR ( (recinfo.ATTRIBUTE5 is null)
580 AND (X_ATTRIBUTE5 is null)))
581 AND ( (recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
582 OR ( (recinfo.ATTRIBUTE6 is null)
583 AND (X_ATTRIBUTE6 is null)))
584 AND ( (recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
585 OR ( (recinfo.ATTRIBUTE7 is null)
586 AND (X_ATTRIBUTE7 is null)))
587 AND ( (recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
588 OR ( (recinfo.ATTRIBUTE2 is null)
589 AND (X_ATTRIBUTE2 is null)))
590 AND ( (recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
591 OR ( (recinfo.ATTRIBUTE12 is null)
592 AND (X_ATTRIBUTE12 is null)))
593 AND ( (recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
594 OR ( (recinfo.ATTRIBUTE13 is null)
595 AND (X_ATTRIBUTE13 is null)))
596 ) then
597 null;
598
599 else
600 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
601 APP_EXCEPTION.Raise_Exception;
602 end if;
603
604 for tlinfo in c1 loop
605 if (tlinfo.BASELANG = 'Y') then
606 if ( (tlinfo.OPERATION_PLAN_NAME = X_OPERATION_PLAN_NAME)
607 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
608 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
609 ) then
610 return;
611 else
612 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
613 app_exception.raise_exception;
614 end if;
615 end if;
616 end loop;
617 return;
618 end LOCK_ROW;
619
620 --added BY grace 12/09/03
621
622 PROCEDURE translate_row
623 (
624 x_operation_plan_id IN VARCHAR2 ,
625 x_owner IN VARCHAR2 ,
626 x_last_update_date IN VARCHAR2 ,
627 x_operation_plan_name IN VARCHAR2 ,
628 x_description IN VARCHAR2 ,
629 x_custom_mode IN VARCHAR2
630 ) IS
631
632 f_luby number; -- entity owner in file
633 f_ludate date; -- entity update date in file
634 db_luby number; -- entity owner in db
635 db_ludate date; -- entity update date in db
636 BEGIN
637 -- Translate owner to file_last_updated_by
638 f_luby := fnd_load_util.owner_id(X_OWNER);
639
640 -- Translate char last_update_date to date
641 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
642 /*
643 UPDATE wms_op_plans_b SET
644 last_update_date = Sysdate,
645 last_updated_by = Decode(x_owner, 'SEED', 1, 0),
646 last_update_login = 0
647 WHERE operation_plan_id = fnd_number.canonical_to_number(x_operation_plan_id);
648 */
649 BEGIN
650 -- Test for customization and version
651 select LAST_UPDATED_BY,LAST_UPDATE_DATE
652 INTO db_luby,db_ludate
653 from wms_op_plans_B where
654 operation_plan_id = fnd_number.canonical_to_number(x_operation_plan_id);
655 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
656 db_ludate, x_custom_mode)) then
657
658 -- Update translations for this language
659 UPDATE wms_op_plans_tl SET
660 operation_plan_name = x_operation_plan_name,
661 description = x_description,
662 last_update_date = f_ludate,
663 last_updated_by = f_luby,
664 last_update_login = 0,
665 source_lang = userenv('LANG')
666 WHERE operation_plan_id = fnd_number.canonical_to_number(x_operation_plan_id) AND userenv('LANG') IN (language, source_lang);
667 END IF;
668 exception
669 when no_data_found then
670 -- Do not insert missing translations, skip this row
671 null;
672 end;
673 END translate_row;
674
675
676
677 procedure ADD_LANGUAGE
678 is
679 begin
680 delete from WMS_OP_PLANS_TL T
681 where not exists
682 (select NULL
683 from WMS_OP_PLANS_B B
684 where B.OPERATION_PLAN_ID = T.OPERATION_PLAN_ID
685 );
686
687 update WMS_OP_PLANS_TL T set (
688 OPERATION_PLAN_NAME,
689 DESCRIPTION
690 ) = (select
691 B.OPERATION_PLAN_NAME,
692 B.DESCRIPTION
693 from WMS_OP_PLANS_TL B
694 where B.OPERATION_PLAN_ID = T.OPERATION_PLAN_ID
695 and B.LANGUAGE = T.SOURCE_LANG)
696 where (
697 T.OPERATION_PLAN_ID,
698 T.LANGUAGE
699 ) in (select
700 SUBT.OPERATION_PLAN_ID,
701 SUBT.LANGUAGE
702 from WMS_OP_PLANS_TL SUBB, WMS_OP_PLANS_TL SUBT
703 where SUBB.OPERATION_PLAN_ID = SUBT.OPERATION_PLAN_ID
704 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
705 and (SUBB.OPERATION_PLAN_NAME <> SUBT.OPERATION_PLAN_NAME
706 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
707 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
708 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
709 ));
710
711 insert into WMS_OP_PLANS_TL (
712 DESCRIPTION,
713 CREATED_BY,
714 OPERATION_PLAN_NAME,
715 LAST_UPDATE_LOGIN,
716 OPERATION_PLAN_ID,
717 LAST_UPDATED_BY,
718 LAST_UPDATE_DATE,
719 CREATION_DATE,
720 LANGUAGE,
721 SOURCE_LANG
722 ) select /*+ ORDERED */
723 B.DESCRIPTION,
724 B.CREATED_BY,
725 B.OPERATION_PLAN_NAME,
726 B.LAST_UPDATE_LOGIN,
727 B.OPERATION_PLAN_ID,
728 B.LAST_UPDATED_BY,
729 B.LAST_UPDATE_DATE,
730 B.CREATION_DATE,
731 L.LANGUAGE_CODE,
732 B.SOURCE_LANG
733 from WMS_OP_PLANS_TL B, FND_LANGUAGES L
734 where L.INSTALLED_FLAG in ('I', 'B')
735 and B.LANGUAGE = userenv('LANG')
736 and not exists
737 (select NULL
738 from WMS_OP_PLANS_TL T
739 where T.OPERATION_PLAN_ID = B.OPERATION_PLAN_ID
740 and T.LANGUAGE = L.LANGUAGE_CODE);
741 end ADD_LANGUAGE;
742
743 END WMS_OP_PLANS_PKG;