eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.WMS_PARAMETERS_PKG

Source


1 package body WMS_PARAMETERS_PKG as
2 /* $Header: WMSPPARB.pls 120.1 2005/06/20 07:10:33 appldev ship $ */
3 procedure INSERT_ROW (
4   X_ROWID IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
5   X_PARAMETER_ID in NUMBER,
6   X_OBJECT_ID in NUMBER,
7   X_DB_OBJECT_REF_TYPE_CODE in NUMBER,
8   X_DB_OBJECT_ID in NUMBER,
9   X_DB_OBJECT_REFERENCE_ID in NUMBER,
10   X_PARAMETER_TYPE_CODE in NUMBER,
11   X_COLUMN_NAME in VARCHAR2,
12   X_EXPRESSION in VARCHAR2,
13   X_DATA_TYPE_CODE in NUMBER,
14   X_USE_FOR_PUT_SORT_FLAG in VARCHAR2,
15   X_USE_FOR_PUT_REST_FLAG in VARCHAR2,
16   X_USE_FOR_PUT_QTYF_FLAG in VARCHAR2,
17   X_USE_FOR_PICK_SORT_FLAG in VARCHAR2,
18   X_USE_FOR_PICK_REST_FLAG in VARCHAR2,
19   X_USE_FOR_PICK_QTYF_FLAG in VARCHAR2,
20   X_USE_FOR_TT_ASSN_FLAG   in VARCHAR2,
21   X_USE_FOR_LABEL_REST_FLAG in VARCHAR2,
22   X_USE_FOR_CG_REST_FLAG   in VARCHAR2,
23   X_USE_FOR_PICK_CONSIST_FLAG   in VARCHAR2,
24   X_USER_DEFINED_FLAG in VARCHAR2,
25   X_FLEXFIELD_USAGE_CODE in VARCHAR2,
26   X_FLEXFIELD_APPLICATION_ID in NUMBER,
27   X_FLEXFIELD_NAME in VARCHAR2,
28   X_ATTRIBUTE_CATEGORY in VARCHAR2,
29   X_ATTRIBUTE1 in VARCHAR2,
30   X_ATTRIBUTE2 in VARCHAR2,
31   X_ATTRIBUTE3 in VARCHAR2,
32   X_ATTRIBUTE4 in VARCHAR2,
33   X_ATTRIBUTE5 in VARCHAR2,
34   X_ATTRIBUTE6 in VARCHAR2,
35   X_ATTRIBUTE7 in VARCHAR2,
36   X_ATTRIBUTE8 in VARCHAR2,
37   X_ATTRIBUTE9 in VARCHAR2,
38   X_ATTRIBUTE10 in VARCHAR2,
39   X_ATTRIBUTE11 in VARCHAR2,
40   X_ATTRIBUTE12 in VARCHAR2,
41   X_ATTRIBUTE13 in VARCHAR2,
42   X_ATTRIBUTE14 in VARCHAR2,
43   X_ATTRIBUTE15 in VARCHAR2,
44   X_NAME in VARCHAR2,
45   X_DESCRIPTION in VARCHAR2,
46   X_CREATION_DATE in DATE,
47   X_CREATED_BY in NUMBER,
48   X_LAST_UPDATE_DATE in DATE,
49   X_LAST_UPDATED_BY in NUMBER,
50   X_LAST_UPDATE_LOGIN in NUMBER,
51   X_USE_FOR_OP_SELECTION_FLAG in VARCHAR2
52 ) is
53   cursor C is select ROWID from WMS_PARAMETERS_B
54     where PARAMETER_ID = X_PARAMETER_ID
55     ;
56 begin
57   insert into WMS_PARAMETERS_B (
58     PARAMETER_ID,
59     OBJECT_ID,
60     DB_OBJECT_REF_TYPE_CODE,
61     DB_OBJECT_ID,
62     DB_OBJECT_REFERENCE_ID,
63     PARAMETER_TYPE_CODE,
64     COLUMN_NAME,
65     EXPRESSION,
66     DATA_TYPE_CODE,
67     USE_FOR_PUT_SORT_FLAG,
68     USE_FOR_PUT_REST_FLAG,
69     USE_FOR_PUT_QTYF_FLAG,
70     USE_FOR_PICK_SORT_FLAG,
71     USE_FOR_PICK_REST_FLAG,
72     USE_FOR_PICK_QTYF_FLAG,
73     USE_FOR_TT_ASSN_FLAG,
74     USE_FOR_LABEL_REST_FLAG,
75     USE_FOR_CG_REST_FLAG,
76     USE_FOR_PICK_CONSIST_FLAG,
77     USER_DEFINED_FLAG,
78     FLEXFIELD_USAGE_CODE,
79     FLEXFIELD_APPLICATION_ID,
80     FLEXFIELD_NAME,
81     ATTRIBUTE_CATEGORY,
82     ATTRIBUTE1,
83     ATTRIBUTE2,
84     ATTRIBUTE3,
85     ATTRIBUTE4,
86     ATTRIBUTE5,
87     ATTRIBUTE6,
88     ATTRIBUTE7,
89     ATTRIBUTE8,
90     ATTRIBUTE9,
91     ATTRIBUTE10,
92     ATTRIBUTE11,
93     ATTRIBUTE12,
94     ATTRIBUTE13,
95     ATTRIBUTE14,
96     ATTRIBUTE15,
97     CREATION_DATE,
98     CREATED_BY,
99     LAST_UPDATE_DATE,
100     LAST_UPDATED_BY,
101     last_update_login,
102     USE_FOR_OP_SELECTION_FLAG
103   ) values (
104     X_PARAMETER_ID,
105     X_OBJECT_ID,
106     X_DB_OBJECT_REF_TYPE_CODE,
107     X_DB_OBJECT_ID,
108     X_DB_OBJECT_REFERENCE_ID,
109     X_PARAMETER_TYPE_CODE,
110     X_COLUMN_NAME,
111     X_EXPRESSION,
112     X_DATA_TYPE_CODE,
113     X_USE_FOR_PUT_SORT_FLAG,
114     X_USE_FOR_PUT_REST_FLAG,
115     X_USE_FOR_PUT_QTYF_FLAG,
116     X_USE_FOR_PICK_SORT_FLAG,
117     X_USE_FOR_PICK_REST_FLAG,
118     X_USE_FOR_PICK_QTYF_FLAG,
119     X_USE_FOR_TT_ASSN_FLAG,
120     X_USE_FOR_LABEL_REST_FLAG,
121     X_USE_FOR_CG_REST_FLAG,
122     X_USE_FOR_PICK_CONSIST_FLAG,
123     X_USER_DEFINED_FLAG,
124     X_FLEXFIELD_USAGE_CODE,
125     X_FLEXFIELD_APPLICATION_ID,
126     X_FLEXFIELD_NAME,
127     X_ATTRIBUTE_CATEGORY,
128     X_ATTRIBUTE1,
129     X_ATTRIBUTE2,
130     X_ATTRIBUTE3,
131     X_ATTRIBUTE4,
132     X_ATTRIBUTE5,
133     X_ATTRIBUTE6,
134     X_ATTRIBUTE7,
135     X_ATTRIBUTE8,
136     X_ATTRIBUTE9,
137     X_ATTRIBUTE10,
138     X_ATTRIBUTE11,
139     X_ATTRIBUTE12,
140     X_ATTRIBUTE13,
141     X_ATTRIBUTE14,
142     X_ATTRIBUTE15,
143     X_CREATION_DATE,
144     X_CREATED_BY,
145     X_LAST_UPDATE_DATE,
146     X_LAST_UPDATED_BY,
147     X_LAST_UPDATE_LOGIN,
148     X_USE_FOR_OP_SELECTION_FLAG
149     );
150 
151   insert into WMS_PARAMETERS_TL (
152     PARAMETER_ID,
153     LAST_UPDATED_BY,
154     LAST_UPDATE_DATE,
155     CREATED_BY,
156     CREATION_DATE,
157     LAST_UPDATE_LOGIN,
158     NAME,
159     DESCRIPTION,
160     LANGUAGE,
161     SOURCE_LANG
162   ) select
163     X_PARAMETER_ID,
164     X_LAST_UPDATED_BY,
165     X_LAST_UPDATE_DATE,
166     X_CREATED_BY,
167     X_CREATION_DATE,
168     X_LAST_UPDATE_LOGIN,
169     X_NAME,
170     X_DESCRIPTION,
171     L.LANGUAGE_CODE,
172     userenv('LANG')
173   from FND_LANGUAGES L
174   where L.INSTALLED_FLAG in ('I', 'B')
175   and not exists
176     (select NULL
177     from WMS_PARAMETERS_TL T
178     where T.PARAMETER_ID = X_PARAMETER_ID
179     and T.LANGUAGE = L.LANGUAGE_CODE);
180 
181   open c;
182   fetch c into X_ROWID;
183   if (c%notfound) then
184     close c;
185     raise no_data_found;
186   end if;
187   close c;
188 
189 end INSERT_ROW;
190 
191 procedure LOCK_ROW (
192   X_PARAMETER_ID in NUMBER,
193   X_OBJECT_ID in NUMBER,
194   X_DB_OBJECT_REF_TYPE_CODE in NUMBER,
195   X_DB_OBJECT_ID in NUMBER,
196   X_DB_OBJECT_REFERENCE_ID in NUMBER,
197   X_PARAMETER_TYPE_CODE in NUMBER,
198   X_COLUMN_NAME in VARCHAR2,
199   X_EXPRESSION in VARCHAR2,
200   X_DATA_TYPE_CODE in NUMBER,
201   X_USE_FOR_PUT_SORT_FLAG   in VARCHAR2,
202   X_USE_FOR_PUT_REST_FLAG   in VARCHAR2,
203   X_USE_FOR_PUT_QTYF_FLAG   in VARCHAR2,
204   X_USE_FOR_PICK_SORT_FLAG  in VARCHAR2,
205   X_USE_FOR_PICK_REST_FLAG  in VARCHAR2,
206   X_USE_FOR_PICK_QTYF_FLAG  in VARCHAR2,
207   X_USE_FOR_TT_ASSN_FLAG    in VARCHAR2,
208   X_USE_FOR_LABEL_REST_FLAG in VARCHAR2,
209   X_USE_FOR_CG_REST_FLAG    in VARCHAR2,
210   X_USE_FOR_PICK_CONSIST_FLAG    in VARCHAR2,
211   X_USER_DEFINED_FLAG in VARCHAR2,
212   X_FLEXFIELD_USAGE_CODE in VARCHAR2,
213   X_FLEXFIELD_APPLICATION_ID in NUMBER,
214   X_FLEXFIELD_NAME in VARCHAR2,
215   X_ATTRIBUTE_CATEGORY in VARCHAR2,
216   X_ATTRIBUTE1 in VARCHAR2,
217   X_ATTRIBUTE2 in VARCHAR2,
218   X_ATTRIBUTE3 in VARCHAR2,
219   X_ATTRIBUTE4 in VARCHAR2,
220   X_ATTRIBUTE5 in VARCHAR2,
221   X_ATTRIBUTE6 in VARCHAR2,
222   X_ATTRIBUTE7 in VARCHAR2,
223   X_ATTRIBUTE8 in VARCHAR2,
224   X_ATTRIBUTE9 in VARCHAR2,
225   X_ATTRIBUTE10 in VARCHAR2,
226   X_ATTRIBUTE11 in VARCHAR2,
227   X_ATTRIBUTE12 in VARCHAR2,
228   X_ATTRIBUTE13 in VARCHAR2,
229   X_ATTRIBUTE14 in VARCHAR2,
230   X_ATTRIBUTE15 in VARCHAR2,
231   X_NAME in VARCHAR2,
232   X_DESCRIPTION in VARCHAR2,
233   X_USE_FOR_OP_SELECTION_FLAG in VARCHAR2
234   ) is
235   cursor c is select
236       OBJECT_ID,
237       DB_OBJECT_REF_TYPE_CODE,
238       DB_OBJECT_ID,
239       DB_OBJECT_REFERENCE_ID,
240       PARAMETER_TYPE_CODE,
241       COLUMN_NAME,
242       EXPRESSION,
243       DATA_TYPE_CODE,
244       USE_FOR_PUT_SORT_FLAG,
245       USE_FOR_PUT_REST_FLAG,
246       USE_FOR_PUT_QTYF_FLAG,
247       USE_FOR_PICK_SORT_FLAG,
248       USE_FOR_PICK_REST_FLAG,
249       USE_FOR_PICK_QTYF_FLAG,
250       USE_FOR_TT_ASSN_FLAG,
251       USE_FOR_LABEL_REST_FLAG,
252       USE_FOR_CG_REST_FLAG,
253       USE_FOR_PICK_CONSIST_FLAG,
254       USER_DEFINED_FLAG,
255       FLEXFIELD_USAGE_CODE,
256       FLEXFIELD_APPLICATION_ID,
257       FLEXFIELD_NAME,
258       ATTRIBUTE_CATEGORY,
259       ATTRIBUTE1,
260       ATTRIBUTE2,
261       ATTRIBUTE3,
262       ATTRIBUTE4,
263       ATTRIBUTE5,
264       ATTRIBUTE6,
265       ATTRIBUTE7,
266       ATTRIBUTE8,
267       ATTRIBUTE9,
268       ATTRIBUTE10,
269       ATTRIBUTE11,
270       ATTRIBUTE12,
271       ATTRIBUTE13,
272       ATTRIBUTE14,
273       ATTRIBUTE15,
274       USE_FOR_OP_SELECTION_FLAG
275     from WMS_PARAMETERS_B
276     where PARAMETER_ID = X_PARAMETER_ID
277     for update of PARAMETER_ID nowait;
278   recinfo c%rowtype;
279 
280   cursor c1 is select
281       NAME,
282       DESCRIPTION,
283       decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
284     from WMS_PARAMETERS_TL
285     where PARAMETER_ID = X_PARAMETER_ID
286     and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
287     for update of PARAMETER_ID nowait;
288 begin
289   open c;
290   fetch c into recinfo;
291   if (c%notfound) then
292     close c;
293     fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
294     app_exception.raise_exception;
295   end if;
296   close c;
297   if (    (recinfo.OBJECT_ID = X_OBJECT_ID)
298       AND (recinfo.DB_OBJECT_REF_TYPE_CODE = X_DB_OBJECT_REF_TYPE_CODE)
299       AND ((recinfo.DB_OBJECT_ID = X_DB_OBJECT_ID)
300            OR ((recinfo.DB_OBJECT_ID is null) AND (X_DB_OBJECT_ID is null)))
301       AND ((recinfo.DB_OBJECT_REFERENCE_ID = X_DB_OBJECT_REFERENCE_ID)
302            OR ((recinfo.DB_OBJECT_REFERENCE_ID is null) AND (X_DB_OBJECT_REFERENCE_ID is null)))
303       AND (recinfo.PARAMETER_TYPE_CODE = X_PARAMETER_TYPE_CODE)
304       AND ((recinfo.COLUMN_NAME = X_COLUMN_NAME)
305            OR ((recinfo.COLUMN_NAME is null) AND (X_COLUMN_NAME is null)))
306       AND ((recinfo.EXPRESSION = X_EXPRESSION)
307            OR ((recinfo.EXPRESSION is null) AND (X_EXPRESSION is null)))
308       AND (recinfo.DATA_TYPE_CODE = X_DATA_TYPE_CODE)
309       AND (recinfo.USE_FOR_PUT_SORT_FLAG   = X_USE_FOR_PUT_SORT_FLAG)
310       AND (recinfo.USE_FOR_PUT_REST_FLAG   = X_USE_FOR_PUT_REST_FLAG)
311       AND (recinfo.USE_FOR_PUT_QTYF_FLAG   = X_USE_FOR_PUT_QTYF_FLAG)
312       AND (recinfo.USE_FOR_PICK_SORT_FLAG  = X_USE_FOR_PICK_SORT_FLAG)
313       AND (recinfo.USE_FOR_PICK_REST_FLAG  = X_USE_FOR_PICK_REST_FLAG)
314       AND (recinfo.USE_FOR_PICK_QTYF_FLAG  = X_USE_FOR_PICK_QTYF_FLAG)
315       AND (recinfo.USE_FOR_TT_ASSN_FLAG    = X_USE_FOR_TT_ASSN_FLAG)
316       AND (recinfo.USE_FOR_LABEL_REST_FLAG = X_USE_FOR_LABEL_REST_FLAG)
317       AND (recinfo.USE_FOR_CG_REST_FLAG    = X_USE_FOR_CG_REST_FLAG)
318       AND (recinfo.USE_FOR_PICK_CONSIST_FLAG = X_USE_FOR_PICK_CONSIST_FLAG)
319       AND (recinfo.USER_DEFINED_FLAG = X_USER_DEFINED_FLAG)
320       AND ((recinfo.FLEXFIELD_USAGE_CODE = X_FLEXFIELD_USAGE_CODE)
321            OR ((recinfo.FLEXFIELD_USAGE_CODE is null) AND (X_FLEXFIELD_USAGE_CODE is null)))
322       AND ((recinfo.FLEXFIELD_APPLICATION_ID = X_FLEXFIELD_APPLICATION_ID)
323            OR ((recinfo.FLEXFIELD_APPLICATION_ID is null) AND (X_FLEXFIELD_APPLICATION_ID is null)))
324       AND ((recinfo.FLEXFIELD_NAME = X_FLEXFIELD_NAME)
325            OR ((recinfo.FLEXFIELD_NAME is null) AND (X_FLEXFIELD_NAME is null)))
326       AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
327            OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
328       AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
329            OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
330       AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
331            OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
332       AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
333            OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
334       AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
335            OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
336       AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
337            OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
338       AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
339            OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
340       AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
341            OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
342       AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
343            OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
344       AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
345            OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
346       AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
347            OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
348       AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
349            OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
350       AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
351            OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
352       AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
353            OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
354       AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
355            OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
356       AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
357            OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
358       AND ((recinfo.USE_FOR_OP_SELECTION_FLAG = X_USE_FOR_OP_SELECTION_FLAG)
359            OR ((recinfo.USE_FOR_OP_SELECTION_FLAG is null) AND (X_USE_FOR_OP_SELECTION_FLAG is null)))
360   ) then
361     null;
362   else
363     fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
364     app_exception.raise_exception;
365   end if;
366 
367   for tlinfo in c1 loop
368     if (tlinfo.BASELANG = 'Y') then
369       if (    (tlinfo.NAME = X_NAME)
370           AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
371                OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
372       ) then
373         null;
374       else
375         fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
376         app_exception.raise_exception;
377       end if;
378     end if;
379   end loop;
380   return;
381 end LOCK_ROW;
382 
383 procedure UPDATE_ROW (
384   X_PARAMETER_ID in NUMBER,
385   X_OBJECT_ID in NUMBER,
386   X_DB_OBJECT_REF_TYPE_CODE in NUMBER,
387   X_DB_OBJECT_ID in NUMBER,
388   X_DB_OBJECT_REFERENCE_ID in NUMBER,
389   X_PARAMETER_TYPE_CODE in NUMBER,
390   X_COLUMN_NAME in VARCHAR2,
391   X_EXPRESSION in VARCHAR2,
392   X_DATA_TYPE_CODE in NUMBER,
393   X_USE_FOR_PUT_SORT_FLAG   in VARCHAR2,
394   X_USE_FOR_PUT_REST_FLAG   in VARCHAR2,
395   X_USE_FOR_PUT_QTYF_FLAG   in VARCHAR2,
396   X_USE_FOR_PICK_SORT_FLAG  in VARCHAR2,
397   X_USE_FOR_PICK_REST_FLAG  in VARCHAR2,
398   X_USE_FOR_PICK_QTYF_FLAG  in VARCHAR2,
399   X_USE_FOR_TT_ASSN_FLAG    in VARCHAR2,
400   X_USE_FOR_LABEL_REST_FLAG in VARCHAR2,
401   X_USE_FOR_CG_REST_FLAG    in VARCHAR2,
402   X_USE_FOR_PICK_CONSIST_FLAG    in VARCHAR2,
403   X_USER_DEFINED_FLAG in VARCHAR2,
404   X_FLEXFIELD_USAGE_CODE in VARCHAR2,
405   X_FLEXFIELD_APPLICATION_ID in NUMBER,
406   X_FLEXFIELD_NAME in VARCHAR2,
407   X_ATTRIBUTE_CATEGORY in VARCHAR2,
408   X_ATTRIBUTE1 in VARCHAR2,
409   X_ATTRIBUTE2 in VARCHAR2,
410   X_ATTRIBUTE3 in VARCHAR2,
411   X_ATTRIBUTE4 in VARCHAR2,
412   X_ATTRIBUTE5 in VARCHAR2,
413   X_ATTRIBUTE6 in VARCHAR2,
414   X_ATTRIBUTE7 in VARCHAR2,
415   X_ATTRIBUTE8 in VARCHAR2,
416   X_ATTRIBUTE9 in VARCHAR2,
417   X_ATTRIBUTE10 in VARCHAR2,
418   X_ATTRIBUTE11 in VARCHAR2,
419   X_ATTRIBUTE12 in VARCHAR2,
420   X_ATTRIBUTE13 in VARCHAR2,
421   X_ATTRIBUTE14 in VARCHAR2,
422   X_ATTRIBUTE15 in VARCHAR2,
423   X_NAME in VARCHAR2,
424   X_DESCRIPTION in VARCHAR2,
425   X_LAST_UPDATE_DATE in DATE,
426   X_LAST_UPDATED_BY in NUMBER,
427   X_LAST_UPDATE_LOGIN in NUMBER,
431   update WMS_PARAMETERS_B set
428   X_USE_FOR_OP_SELECTION_FLAG in VARCHAR2
429 ) is
430 begin
432     OBJECT_ID = X_OBJECT_ID,
433     DB_OBJECT_REF_TYPE_CODE = X_DB_OBJECT_REF_TYPE_CODE,
434     DB_OBJECT_ID = X_DB_OBJECT_ID,
435     DB_OBJECT_REFERENCE_ID = X_DB_OBJECT_REFERENCE_ID,
436     PARAMETER_TYPE_CODE = X_PARAMETER_TYPE_CODE,
437     COLUMN_NAME = X_COLUMN_NAME,
438     EXPRESSION = X_EXPRESSION,
439     DATA_TYPE_CODE = X_DATA_TYPE_CODE,
440     USE_FOR_PUT_SORT_FLAG   = X_USE_FOR_PUT_SORT_FLAG,
441     USE_FOR_PUT_REST_FLAG   = X_USE_FOR_PUT_REST_FLAG,
442     USE_FOR_PUT_QTYF_FLAG   = X_USE_FOR_PUT_QTYF_FLAG,
443     USE_FOR_PICK_SORT_FLAG  = X_USE_FOR_PICK_SORT_FLAG,
444     USE_FOR_PICK_REST_FLAG  = X_USE_FOR_PICK_REST_FLAG,
445     USE_FOR_PICK_QTYF_FLAG  = X_USE_FOR_PICK_QTYF_FLAG,
446     USE_FOR_TT_ASSN_FLAG    = X_USE_FOR_TT_ASSN_FLAG,
447     USE_FOR_LABEL_REST_FLAG = X_USE_FOR_LABEL_REST_FLAG,
448     USE_FOR_CG_REST_FLAG    = X_USE_FOR_CG_REST_FLAG,
449     USE_FOR_PICK_CONSIST_FLAG    = X_USE_FOR_PICK_CONSIST_FLAG,
450     USER_DEFINED_FLAG = X_USER_DEFINED_FLAG,
451     FLEXFIELD_USAGE_CODE = X_FLEXFIELD_USAGE_CODE,
452     FLEXFIELD_APPLICATION_ID = X_FLEXFIELD_APPLICATION_ID,
453     FLEXFIELD_NAME = X_FLEXFIELD_NAME,
454     ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
455     ATTRIBUTE1 = X_ATTRIBUTE1,
456     ATTRIBUTE2 = X_ATTRIBUTE2,
457     ATTRIBUTE3 = X_ATTRIBUTE3,
458     ATTRIBUTE4 = X_ATTRIBUTE4,
459     ATTRIBUTE5 = X_ATTRIBUTE5,
460     ATTRIBUTE6 = X_ATTRIBUTE6,
461     ATTRIBUTE7 = X_ATTRIBUTE7,
462     ATTRIBUTE8 = X_ATTRIBUTE8,
463     ATTRIBUTE9 = X_ATTRIBUTE9,
464     ATTRIBUTE10 = X_ATTRIBUTE10,
465     ATTRIBUTE11 = X_ATTRIBUTE11,
466     ATTRIBUTE12 = X_ATTRIBUTE12,
467     ATTRIBUTE13 = X_ATTRIBUTE13,
468     ATTRIBUTE14 = X_ATTRIBUTE14,
469     ATTRIBUTE15 = X_ATTRIBUTE15,
470     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
471     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
472     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
473     USE_FOR_OP_SELECTION_FLAG = X_USE_FOR_OP_SELECTION_FLAG
474     where PARAMETER_ID = X_PARAMETER_ID;
475 
476   if (sql%notfound) then
477     raise no_data_found;
478   end if;
479 
480   update WMS_PARAMETERS_TL set
481     NAME = X_NAME,
482     DESCRIPTION = X_DESCRIPTION,
483     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
484     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
485     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
486     SOURCE_LANG = userenv('LANG')
487   where PARAMETER_ID = X_PARAMETER_ID
488   and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
489 
490   if (sql%notfound) then
491     raise no_data_found;
492   end if;
493 end UPDATE_ROW;
494 
495 procedure DELETE_ROW (
496   X_PARAMETER_ID in NUMBER
497 ) is
498 begin
499   delete from WMS_PARAMETERS_TL
500   where PARAMETER_ID = X_PARAMETER_ID;
501 
502   if (sql%notfound) then
503     raise no_data_found;
504   end if;
505 
506   delete from WMS_PARAMETERS_B
507   where PARAMETER_ID = X_PARAMETER_ID;
508 
509   if (sql%notfound) then
510     raise no_data_found;
511   end if;
512 end DELETE_ROW;
513 
514 procedure ADD_LANGUAGE
515 is
516 begin
517   delete from WMS_PARAMETERS_TL T
518   where not exists
519     (select NULL
520     from WMS_PARAMETERS_B B
521     where B.PARAMETER_ID = T.PARAMETER_ID
522     );
523 
524   update WMS_PARAMETERS_TL T set (
525       NAME,
526       DESCRIPTION
527     ) = (select
528       B.NAME,
529       B.DESCRIPTION
530     from WMS_PARAMETERS_TL B
531     where B.PARAMETER_ID = T.PARAMETER_ID
532     and B.LANGUAGE = T.SOURCE_LANG)
533   where (
534       T.PARAMETER_ID,
535       T.LANGUAGE
536   ) in (select
537       SUBT.PARAMETER_ID,
538       SUBT.LANGUAGE
539     from WMS_PARAMETERS_TL SUBB, WMS_PARAMETERS_TL SUBT
540     where SUBB.PARAMETER_ID = SUBT.PARAMETER_ID
541     and SUBB.LANGUAGE = SUBT.SOURCE_LANG
542     and (SUBB.NAME <> SUBT.NAME
543       or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
544       or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
545       or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
546   ));
547 
548   insert into WMS_PARAMETERS_TL (
549     PARAMETER_ID,
550     LAST_UPDATED_BY,
551     LAST_UPDATE_DATE,
552     CREATED_BY,
553     CREATION_DATE,
554     LAST_UPDATE_LOGIN,
555     NAME,
556     DESCRIPTION,
557     LANGUAGE,
558     SOURCE_LANG
559   ) select
560     B.PARAMETER_ID,
561     B.LAST_UPDATED_BY,
562     B.LAST_UPDATE_DATE,
563     B.CREATED_BY,
564     B.CREATION_DATE,
565     B.LAST_UPDATE_LOGIN,
566     B.NAME,
567     B.DESCRIPTION,
568     L.LANGUAGE_CODE,
569     B.SOURCE_LANG
570   from WMS_PARAMETERS_TL B, FND_LANGUAGES L
571   where L.INSTALLED_FLAG in ('I', 'B')
572   and B.LANGUAGE = userenv('LANG')
573   and not exists
574     (select NULL
575     from WMS_PARAMETERS_TL T
576     where T.PARAMETER_ID = B.PARAMETER_ID
577     and T.LANGUAGE = L.LANGUAGE_CODE);
578 end ADD_LANGUAGE;
579 PROCEDURE translate_row
580   (
581    x_parameter_id             IN  VARCHAR2 ,
582    x_owner                    IN  VARCHAR2 ,
583    x_name                     IN  VARCHAR2 ,
584    x_description              IN  VARCHAR2
585    ) IS
586 BEGIN
587    UPDATE wms_parameters_tl SET
588      name              = x_name,
589      description       = x_description,
590      last_update_date  = Sysdate,
591      last_updated_by   = Decode(x_owner, 'SEED', 1, 0),
592      last_update_login = 0,
593      source_lang       = userenv('LANG')
594      WHERE parameter_id = fnd_number.canonical_to_number(x_parameter_id)
595      AND userenv('LANG') IN (language, source_lang);
596 END translate_row;
597 
598 PROCEDURE load_row
599   (
600    x_parameter_id             IN  VARCHAR2 ,
601    x_owner                    IN  VARCHAR2 ,
602    x_object_id                IN  VARCHAR2 ,
603    x_db_object_ref_type_code  IN  VARCHAR2 ,
604    x_db_object_id             IN  VARCHAR2 ,
605    x_db_object_reference_id   IN  VARCHAR2 ,
606    x_parameter_type_code      IN  VARCHAR2 ,
607    x_column_name              IN  VARCHAR2 ,
608    x_expression               IN  VARCHAR2 ,
609    x_data_type_code           IN  VARCHAR2 ,
610    x_use_for_put_sort_flag    IN  VARCHAR2 ,
611    x_use_for_put_rest_flag    IN  VARCHAR2 ,
612    x_use_for_put_qtyf_flag    IN  VARCHAR2 ,
613    x_use_for_pick_sort_flag   IN  VARCHAR2 ,
614    x_use_for_pick_rest_flag   IN  VARCHAR2 ,
615    x_use_for_pick_qtyf_flag   IN  VARCHAR2 ,
616    x_use_for_TT_assn_flag     IN  VARCHAR2 ,
617    x_use_for_label_rest_flag  IN  VARCHAR2 ,
618    x_use_for_CG_rest_flag     IN  VARCHAR2 ,
619    x_use_for_pick_consist_flag IN  VARCHAR2 ,
620    x_use_for_op_selection_flag IN  VARCHAR2 ,
621    x_user_defined_flag        IN  VARCHAR2 ,
622    x_flexfield_usage_code     IN  VARCHAR2 ,
623    x_flexfield_application_id IN  VARCHAR2 ,
624    x_flexfield_name           IN  VARCHAR2 ,
625    x_attribute_category       IN  VARCHAR2 ,
626    x_attribute1               IN  VARCHAR2 ,
627    x_attribute2 	      IN  VARCHAR2 ,
628    x_attribute3 	      IN  VARCHAR2 ,
629    x_attribute4 	      IN  VARCHAR2 ,
630    x_attribute5 	      IN  VARCHAR2 ,
631    x_attribute6 	      IN  VARCHAR2 ,
632    x_attribute7 	      IN  VARCHAR2 ,
633    x_attribute8 	      IN  VARCHAR2 ,
634    x_attribute9 	      IN  VARCHAR2 ,
635    x_attribute10 	      IN  VARCHAR2 ,
636    x_attribute11 	      IN  VARCHAR2 ,
637    x_attribute12 	      IN  VARCHAR2 ,
638    x_attribute13 	      IN  VARCHAR2 ,
639    x_attribute14 	      IN  VARCHAR2 ,
640    x_attribute15 	      IN  VARCHAR2 ,
641    x_name                     IN  VARCHAR2 ,
642    x_description              IN  VARCHAR2
643   ) IS
644 BEGIN
645    DECLARE
646       l_parameter_id             NUMBER;
647       l_object_id                NUMBER;
648       l_db_object_ref_type_code  NUMBER;
649       l_db_object_id             NUMBER;
650       l_db_object_reference_id   NUMBER;
651       l_parameter_type_code      NUMBER;
652       l_data_type_code           NUMBER;
653       l_flexfield_application_id NUMBER;
654       l_user_id                  NUMBER := 0;
655       l_row_id                   VARCHAR2(64);
656       l_sysdate                  DATE;
657    BEGIN
658       IF (x_owner = 'SEED') THEN
659 	 l_user_id := 1;
660       END IF;
661       --
662       SELECT Sysdate INTO l_sysdate FROM dual;
663       l_parameter_id := fnd_number.canonical_to_number(x_parameter_id);
664       l_object_id := fnd_number.canonical_to_number(x_object_id);
665       l_db_object_ref_type_code := fnd_number.canonical_to_number(x_db_object_ref_type_code);
666       l_db_object_id := fnd_number.canonical_to_number(x_db_object_id);
667       l_db_object_reference_id := fnd_number.canonical_to_number(x_db_object_reference_id);
668       l_parameter_type_code := fnd_number.canonical_to_number(x_parameter_type_code);
669       l_data_type_code := fnd_number.canonical_to_number(x_data_type_code);
670       l_flexfield_application_id :=
671 	fnd_number.canonical_to_number(x_flexfield_application_id);
672       wms_parameters_pkg.update_row
673 	(
674  	  x_parameter_id             => l_parameter_id
675 	 ,x_object_id 		     => l_object_id
676 	 ,x_db_object_ref_type_code  => l_db_object_ref_type_code
677 	 ,x_db_object_id 	     => l_db_object_id
678 	 ,x_db_object_reference_id   => l_db_object_reference_id
679 	 ,x_parameter_type_code      => l_parameter_type_code
680 	 ,x_column_name		     => x_column_name
681 	 ,x_expression 		     => x_expression
682 	 ,x_data_type_code 	     => l_data_type_code
683 	 ,x_use_for_put_sort_flag    => x_use_for_put_sort_flag
684 	 ,x_use_for_put_rest_flag    => x_use_for_put_rest_flag
685 	 ,x_use_for_put_qtyf_flag    => x_use_for_put_qtyf_flag
686 	 ,x_use_for_pick_sort_flag   => x_use_for_pick_sort_flag
687 	 ,x_use_for_pick_rest_flag   => x_use_for_pick_rest_flag
688 	 ,x_use_for_pick_qtyf_flag   => x_use_for_pick_qtyf_flag
689 	 ,x_use_for_TT_assn_flag     => x_use_for_TT_assn_flag
690 	 ,x_use_for_label_rest_flag  => x_use_for_label_rest_flag
691 	 ,x_use_for_CG_rest_flag     => x_use_for_CG_rest_flag
692 	 ,x_use_for_pick_consist_flag => x_use_for_pick_consist_flag
693 	 ,x_user_defined_flag 	     => x_user_defined_flag
694 	 ,x_flexfield_usage_code     => x_flexfield_usage_code
695 	 ,x_flexfield_application_id => l_flexfield_application_id
696 	 ,x_flexfield_name 	     => x_flexfield_name
697 	 ,x_attribute_category	     => x_attribute_category
698 	 ,x_attribute1 		     => x_attribute1
699 	 ,x_attribute2 		     => x_attribute2
700 	 ,x_attribute3 		     => x_attribute3
701 	 ,x_attribute4 		     => x_attribute4
702 	 ,x_attribute5 		     => x_attribute5
703 	 ,x_attribute6 		     => x_attribute6
704 	 ,x_attribute7               => x_attribute7
705 	 ,x_attribute8 		     => x_attribute8
706 	 ,x_attribute9 		     => x_attribute9
707 	 ,x_attribute10		     => x_attribute10
708 	 ,x_attribute11		     => x_attribute11
709 	 ,x_attribute12		     => x_attribute12
710 	 ,x_attribute13		     => x_attribute13
711 	 ,x_attribute14		     => x_attribute14
712 	 ,x_attribute15		     => x_attribute15
713 	 ,x_name        	     => x_name
714 	 ,x_description 	     => x_description
715 	 ,x_last_update_date         => l_sysdate
716 	 ,x_last_updated_by          => l_user_id
717 	 ,x_last_update_login        => 0
718          ,x_use_for_op_selection_flag => x_use_for_op_selection_flag
719 	);
720    EXCEPTION
721       WHEN no_data_found THEN
722 	 wms_parameters_pkg.insert_row
723 	   (
724 	     x_rowid                    => l_row_id
725 	    ,x_parameter_id             => l_parameter_id
726 	    ,x_object_id 		=> l_object_id
727 	    ,x_db_object_ref_type_code  => l_db_object_ref_type_code
728 	    ,x_db_object_id 	        => l_db_object_id
729 	    ,x_db_object_reference_id   => l_db_object_reference_id
730 	    ,x_parameter_type_code      => l_parameter_type_code
731 	    ,x_column_name		=> x_column_name
732 	    ,x_expression 		=> x_expression
733 	    ,x_data_type_code 	        => l_data_type_code
734 	    ,x_use_for_put_sort_flag    => x_use_for_put_sort_flag
735 	    ,x_use_for_put_rest_flag    => x_use_for_put_rest_flag
736 	    ,x_use_for_put_qtyf_flag    => x_use_for_put_qtyf_flag
737 	    ,x_use_for_pick_sort_flag   => x_use_for_pick_sort_flag
738 	    ,x_use_for_pick_rest_flag   => x_use_for_pick_rest_flag
739 	    ,x_use_for_pick_qtyf_flag   => x_use_for_pick_qtyf_flag
740 	    ,x_use_for_TT_assn_flag     => x_use_for_TT_assn_flag
741 	    ,x_use_for_label_rest_flag  => x_use_for_label_rest_flag
742 	    ,x_use_for_CG_rest_flag     => x_use_for_CG_rest_flag
743 	    ,x_use_for_pick_consist_flag => x_use_for_pick_consist_flag
744 	    ,x_user_defined_flag 	=> x_user_defined_flag
745 	    ,x_flexfield_usage_code     => x_flexfield_usage_code
746 	    ,x_flexfield_application_id => l_flexfield_application_id
747 	    ,x_flexfield_name 	        => x_flexfield_name
748 	    ,x_attribute_category	=> x_attribute_category
749 	    ,x_attribute1 		=> x_attribute1
750 	    ,x_attribute2 		=> x_attribute2
751 	    ,x_attribute3 		=> x_attribute3
752 	    ,x_attribute4 		=> x_attribute4
753 	    ,x_attribute5 		=> x_attribute5
754 	    ,x_attribute6 		=> x_attribute6
755 	    ,x_attribute7               => x_attribute7
756 	    ,x_attribute8 		=> x_attribute8
757 	    ,x_attribute9 		=> x_attribute9
758 	    ,x_attribute10		=> x_attribute10
759 	    ,x_attribute11		=> x_attribute11
760 	    ,x_attribute12		=> x_attribute12
761 	    ,x_attribute13		=> x_attribute13
762 	    ,x_attribute14		=> x_attribute14
763 	    ,x_attribute15		=> x_attribute15
764 	    ,x_name        	        => x_name
765 	    ,x_description 	        => x_description
766 	    ,x_last_update_date         => l_sysdate
767 	    ,x_last_updated_by          => l_user_id
768 	    ,x_last_update_login        => 0
769 	    ,x_created_by               => l_user_id
770 	    ,x_creation_date            => l_sysdate
771             ,x_use_for_op_selection_flag => x_use_for_op_selection_flag
772 	   );
773    END;
774 END load_row;
775 end WMS_PARAMETERS_PKG;