1 package body WF_LOOKUP_TYPES_PKG as
2 /* $Header: wflutb.pls 120.3 2005/10/04 23:24:11 rtodi ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY VARCHAR2,
5 X_LOOKUP_TYPE in VARCHAR2,
6 X_ITEM_TYPE in VARCHAR2,
7 X_PROTECT_LEVEL in NUMBER,
8 X_CUSTOM_LEVEL in NUMBER,
9 X_DISPLAY_NAME in VARCHAR2,
10 X_DESCRIPTION in VARCHAR2
11 ) is
12 cursor C is select ROWID from WF_LOOKUP_TYPES_TL
13 where LOOKUP_TYPE = X_LOOKUP_TYPE
14 and LANGUAGE = userenv('LANG')
15 ;
16 begin
17 insert into WF_LOOKUP_TYPES_TL (
18 LOOKUP_TYPE,
19 DISPLAY_NAME,
20 ITEM_TYPE,
21 PROTECT_LEVEL,
22 CUSTOM_LEVEL,
23 DESCRIPTION,
24 LANGUAGE,
25 SOURCE_LANG
26 ) select
27 X_LOOKUP_TYPE,
28 X_DISPLAY_NAME,
29 X_ITEM_TYPE,
30 X_PROTECT_LEVEL,
31 X_CUSTOM_LEVEL,
32 X_DESCRIPTION,
33 L.CODE,
34 userenv('LANG')
35 from WF_LANGUAGES L
36 where L.INSTALLED_FLAG = 'Y'
37 and not exists
38 (select NULL
39 from WF_LOOKUP_TYPES_TL T
40 where T.LOOKUP_TYPE = X_LOOKUP_TYPE
41 and T.LANGUAGE = L.CODE);
42
43 open c;
44 fetch c into X_ROWID;
45 if (c%notfound) then
46 close c;
47 raise no_data_found;
48 end if;
49 close c;
50
51 exception
52 when others then
53 wf_core.context('Wf_Lookup_Types_Pkg', 'Insert_Row', x_lookup_type);
54 raise;
55 end INSERT_ROW;
56
57 procedure LOCK_ROW (
58 X_LOOKUP_TYPE in VARCHAR2,
59 X_ITEM_TYPE in VARCHAR2,
60 X_PROTECT_LEVEL in NUMBER,
61 X_CUSTOM_LEVEL in NUMBER,
62 X_DISPLAY_NAME in VARCHAR2,
63 X_DESCRIPTION in VARCHAR2
64 ) is
65 cursor c1 is select
66 ITEM_TYPE,
67 PROTECT_LEVEL,
68 CUSTOM_LEVEL,
69 DISPLAY_NAME,
70 DESCRIPTION
71 from WF_LOOKUP_TYPES_TL
72 where LOOKUP_TYPE = X_LOOKUP_TYPE
73 and LANGUAGE = userenv('LANG')
74 for update of LOOKUP_TYPE nowait;
75 tlinfo c1%rowtype;
76
77 begin
78 open c1;
79 fetch c1 into tlinfo;
80 if (c1%notfound) then
81 close c1;
82 wf_core.raise('WF_RECORD_DELETED');
83 end if;
84 close c1;
85
86 if ( (tlinfo.DISPLAY_NAME = X_DISPLAY_NAME)
87 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
88 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
89 AND (tlinfo.ITEM_TYPE = X_ITEM_TYPE)
90 AND (tlinfo.PROTECT_LEVEL = X_PROTECT_LEVEL)
91 AND (tlinfo.CUSTOM_LEVEL = X_CUSTOM_LEVEL)
92 ) then
93 null;
94 else
95 wf_core.raise('WF_RECORD_CHANGED');
96 end if;
97 return;
98
99 exception
100 when others then
101 wf_core.context('Wf_Lookup_Types_Pkg', 'Lock_Row', x_lookup_type);
102 raise;
103 end LOCK_ROW;
104
105 procedure UPDATE_ROW (
106 X_LOOKUP_TYPE in VARCHAR2,
107 X_ITEM_TYPE in VARCHAR2,
108 X_PROTECT_LEVEL in NUMBER,
109 X_CUSTOM_LEVEL in NUMBER,
110 X_DISPLAY_NAME in VARCHAR2,
111 X_DESCRIPTION in VARCHAR2
112 ) is
113 begin
114 update WF_LOOKUP_TYPES_TL set
115 ITEM_TYPE = X_ITEM_TYPE,
116 PROTECT_LEVEL = X_PROTECT_LEVEL,
117 CUSTOM_LEVEL = X_CUSTOM_LEVEL,
118 DISPLAY_NAME = X_DISPLAY_NAME,
119 DESCRIPTION = X_DESCRIPTION,
120 SOURCE_LANG = userenv('LANG')
121 where LOOKUP_TYPE = X_LOOKUP_TYPE
122 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
123
124 if (sql%notfound) then
125 raise no_data_found;
126 end if;
127
128 exception
129 when others then
130 wf_core.context('Wf_Lookup_Types_Pkg', 'Update_Row', x_lookup_type);
131 raise;
132 end UPDATE_ROW;
133
134 procedure DELETE_ROW (
135 X_LOOKUP_TYPE in VARCHAR2
136 ) is
137 begin
138 delete from WF_LOOKUP_TYPES_TL
139 where LOOKUP_TYPE = X_LOOKUP_TYPE;
140
141 if (sql%notfound) then
142 raise no_data_found;
143 end if;
144
145 exception
146 when others then
147 wf_core.context('Wf_Lookup_Types_Pkg', 'Delete_Row', x_lookup_type);
148 raise;
149 end DELETE_ROW;
150
151 procedure ADD_LANGUAGE
152 is
153 begin
154 /* The following update statement is commented out */
155 /* as a quick workaround to fix the time-consuming table handler issue */
156 /* Eventually we'll need to turn them into a separate fix_language procedure */
157 /*
158
159 update WF_LOOKUP_TYPES_TL T set (
160 DISPLAY_NAME,
161 DESCRIPTION
162 ) = (select
163 B.DISPLAY_NAME,
164 B.DESCRIPTION
165 from WF_LOOKUP_TYPES_TL B
166 where B.LOOKUP_TYPE = T.LOOKUP_TYPE
167 and B.LANGUAGE = T.SOURCE_LANG)
168 where (
169 T.LOOKUP_TYPE,
170 T.LANGUAGE
171 ) in (select
172 SUBT.LOOKUP_TYPE,
173 SUBT.LANGUAGE
174 from WF_LOOKUP_TYPES_TL SUBB, WF_LOOKUP_TYPES_TL SUBT
175 where SUBB.LOOKUP_TYPE = SUBT.LOOKUP_TYPE
176 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
177 and (SUBB.DISPLAY_NAME <> SUBT.DISPLAY_NAME
178 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
179 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
180 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
181 ));
182
183 */
184
185 insert into WF_LOOKUP_TYPES_TL (
186 LOOKUP_TYPE,
187 DISPLAY_NAME,
188 ITEM_TYPE,
189 PROTECT_LEVEL,
190 CUSTOM_LEVEL,
191 DESCRIPTION,
192 LANGUAGE,
193 SOURCE_LANG
194 ) select
195 B.LOOKUP_TYPE,
196 B.DISPLAY_NAME,
197 B.ITEM_TYPE,
198 B.PROTECT_LEVEL,
199 B.CUSTOM_LEVEL,
200 B.DESCRIPTION,
201 L.CODE,
202 B.SOURCE_LANG
203 from WF_LOOKUP_TYPES_TL B, WF_LANGUAGES L
204 where L.INSTALLED_FLAG = 'Y'
205 and B.LANGUAGE = userenv('LANG')
206 and (B.LOOKUP_TYPE , L.CODE ) NOT IN
207 (select /*+ hash_aj index_ffs(T,WF_LOOKUP_TYPES_TL_PK ) */
208 T.LOOKUP_TYPE ,T.LANGUAGE
209 from WF_LOOKUP_TYPES_TL T);
210
211 end ADD_LANGUAGE;
212
213 end WF_LOOKUP_TYPES_PKG;
[Home] [Help]