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