[Home] [Help]
PACKAGE BODY: APPS.WF_WL_REGIONS_PKG
Source
1 package body WF_WL_REGIONS_PKG as
2 /* $Header: wfwlregb.pls 120.0.12020000.5 2013/05/15 21:23:22 alsosa noship $ */
3 g_mode varchar2(10);
4
5 procedure LoaderSetMode(x_mode in varchar2) is
6 begin
7 g_mode := x_mode;
8 end LoaderSetMode;
9
10 function is_update_allowed(X_CUSTOM_LEVEL_NEW in varchar2,
11 X_CUSTOM_LEVEL_OLD in varchar2) return varchar2
12 is
13 begin
14 -- Cannot overwrite data with a higher customization level
15 if X_CUSTOM_LEVEL_NEW = 'U' then
16 if X_CUSTOM_LEVEL_OLD in ('C','L') then
17 return ('N'); -- Error will be logged
18 elsif X_CUSTOM_LEVEL_OLD = 'U' then
19 return ('Y'); -- Return Y. Update is based on the caller
20 end if;
21 elsif X_CUSTOM_LEVEL_NEW = 'L' then
22 if X_CUSTOM_LEVEL_OLD = 'C' then
23 return('N'); -- Error will be logged
24 elsif X_CUSTOM_LEVEL_OLD = 'U' then
25 return('Y'); -- Override it
26 else
27 return('Y'); -- Customization Level is L
28 end if;
29 elsif X_CUSTOM_LEVEL_NEW = 'C' then
30 return('Y'); -- Override the values in the database irrespective of the value
31 end if;
32 end is_update_allowed;
33
34 function fetch_custom_level (p_region_key in varchar2) return varchar2 is
35 l_custom_level varchar2(1);
36 l_region_id number := FIND_REGION_ID(p_region_key);
37 begin
38 select CUSTOMIZATION_LEVEL into l_custom_level
39 from WF_WL_REGIONS
40 where REGION_ID=l_region_id;
41 return l_custom_level;
42 end fetch_custom_level;
43
44 function FIND_REGION_ID(X_KEY in VARCHAR2) return number is
45 l_region_id number;
46 begin
47 select REGION_ID into l_region_id
48 from WF_WL_REGIONS
49 where REGION_KEY=X_KEY;
50 return l_region_id;
51 exception
52 when others then
53 raise_application_error (-20000, 'WF_WL_REGIONS_PKG.FIND_REGION_ID: unable to find region '||X_KEY);
54 end FIND_REGION_ID;
55
56 procedure INSERT_REGION_BY_KEY (X_REGION_KEY in VARCHAR2,
57 X_REGION in VARCHAR2,
58 X_CONTENT_TYPE in VARCHAR2,
59 X_SEQUENCE in NUMBER,
60 X_MESSAGE_TYPE in VARCHAR2,
61 X_MESSAGE_NAME in VARCHAR2,
62 X_CUSTOMIZATION_LEVEL in VARCHAR2,
63 X_IS_SEEDED in VARCHAR2,
64 X_STATUS in VARCHAR2,
65 X_TITLE in VARCHAR2,
66 X_DESCRIPTION in VARCHAR2,
67 X_OWNER IN VARCHAR2,
68 X_LAST_UPDATE_DATE IN VARCHAR2) is
69 cursor c_newrow is select ROWID from WF_WL_REGIONS
70 where REGION_KEY = X_REGION_KEY;
71 l_rowid varchar2(30);
72 l_luby number := FND_LOAD_UTIL.OWNER_ID(X_OWNER);
73 l_ludate date := nvl(to_date(X_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);
74 begin
75 insert into WF_WL_REGIONS (REGION_ID,
76 REGION_KEY,
77 REGION,
78 CONTENT_TYPE,
79 SEQUENCE,
80 MESSAGE_TYPE,
81 MESSAGE_NAME,
82 CUSTOMIZATION_LEVEL,
83 IS_SEEDED,
84 STATUS,
85 CREATION_DATE,
86 CREATED_BY,
87 LAST_UPDATE_DATE,
88 LAST_UPDATED_BY,
89 LAST_UPDATE_LOGIN,
90 SECURITY_GROUP_ID)
91 values (WF_WL_REGIONS_S.NEXTVAL,
92 X_REGION_KEY,
93 X_REGION,
94 X_CONTENT_TYPE,
95 X_SEQUENCE,
96 X_MESSAGE_TYPE,
97 X_MESSAGE_NAME,
98 X_CUSTOMIZATION_LEVEL,
99 X_IS_SEEDED,
100 X_STATUS,
101 l_ludate, --CREATION_DATE,
102 l_luby, --CREATED_BY,
103 l_ludate, --LAST_UPDATE_DATE,
104 l_luby, --LAST_UPDATED_BY,
105 0, --LAST_UPDATE_LOGIN,
106 FND_GLOBAL.SECURITY_GROUP_ID);
107
108 insert into WF_WL_REGIONS_TL (REGION_ID,
109 TITLE,
110 DESCRIPTION,
111 LANGUAGE,
112 SOURCE_LANG,
113 CREATION_DATE,
114 CREATED_BY,
115 LAST_UPDATE_DATE,
116 LAST_UPDATED_BY,
117 LAST_UPDATE_LOGIN,
118 SECURITY_GROUP_ID)
119 select WF_WL_REGIONS_S.CURRVAL,
120 X_TITLE,
121 X_DESCRIPTION,
122 L.LANGUAGE_CODE,
123 userenv('LANG'),
124 l_ludate, --CREATION_DATE,
125 l_luby, --CREATED_BY,
126 l_ludate, --LAST_UPDATE_DATE,
127 l_luby, --LAST_UPDATED_BY,
128 0, --LAST_UPDATE_LOGIN,
129 FND_GLOBAL.SECURITY_GROUP_ID
130 from FND_LANGUAGES L
131 where L.INSTALLED_FLAG in ('I', 'B')
132 and not exists
133 (select NULL
134 from WF_WL_REGIONS_TL TL, WF_WL_REGIONS B
135 where B.REGION_ID = TL.REGION_ID and
136 B.REGION_KEY = X_REGION_KEY and
137 TL.LANGUAGE = L.LANGUAGE_CODE);
138
139 open c_newrow;
140 fetch c_newrow into l_rowid;
141 if (c_newrow%notfound) then
142 close c_newrow;
143 raise_application_error (-20000, 'WF_WL_REGIONS_PKG.INSERT_REGION_BY_KEY: unable to create region '||X_REGION_KEY);
144 end if;
145 close c_newrow;
146 end INSERT_REGION_BY_KEY;
147
148 procedure UPDATE_REGION_BY_KEY (X_REGION_KEY in VARCHAR2,
149 X_REGION in VARCHAR2,
150 X_CONTENT_TYPE in VARCHAR2,
151 X_SEQUENCE in NUMBER,
152 X_MESSAGE_TYPE in VARCHAR2,
153 X_MESSAGE_NAME in VARCHAR2,
154 X_CUSTOMIZATION_LEVEL in VARCHAR2,
155 X_IS_SEEDED in VARCHAR2,
156 X_STATUS in VARCHAR2,
157 X_TITLE in VARCHAR2,
158 X_DESCRIPTION in VARCHAR2,
159 X_OWNER IN VARCHAR2,
160 X_LAST_UPDATE_DATE IN VARCHAR2) is
161 l_custom_level varchar2(1);
162 l_luby number := FND_LOAD_UTIL.OWNER_ID(X_OWNER);
163 l_ludate date := nvl(to_date(X_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);
164 begin
165 if g_mode='FORCE' or (X_CUSTOMIZATION_LEVEL = 'C' and g_mode = 'UPGRADE') then
166 update WF_WL_REGIONS B
167 set B.REGION = X_REGION,
168 B.CONTENT_TYPE = X_CONTENT_TYPE,
169 B.SEQUENCE = X_SEQUENCE,
170 B.MESSAGE_TYPE = X_MESSAGE_TYPE,
171 B.MESSAGE_NAME = X_MESSAGE_NAME,
172 B.CUSTOMIZATION_LEVEL = X_CUSTOMIZATION_LEVEL,
173 B.IS_SEEDED = X_IS_SEEDED,
174 B.STATUS = X_STATUS,
175 B.LAST_UPDATE_DATE = l_ludate,
176 B.LAST_UPDATED_BY = l_luby,
177 B.LAST_UPDATE_LOGIN = 0
178 where B.REGION_KEY = X_REGION_KEY;
179 if (sql%notfound) then
180 raise no_data_found;
181 end if;
182 else
183 -- In FNDLOAD there are only two modes: FORCE and UPGRADE. If we are here it means
184 -- that g_mode='UPGRADE' and X_CUSTOMIZATION_LEVEL<>'C'
185 l_custom_level := fetch_custom_level(X_REGION_KEY);
186 if is_update_allowed(X_CUSTOMIZATION_LEVEL, l_custom_level) = 'Y' then
187 update WF_WL_REGIONS B
188 set B.STATUS = X_STATUS,
189 B.LAST_UPDATE_DATE = l_ludate,
190 B.LAST_UPDATED_BY = l_luby,
191 B.LAST_UPDATE_LOGIN = 0
192 where B.REGION_KEY = X_REGION_KEY;
193 if (sql%notfound) then
194 raise no_data_found;
195 end if;
196 else
197 raise_application_error(-20000, 'WF_WL_REGIONS_PKG.UPDATE_REGION_BY_KEY unable to load region '||X_REGION_KEY||' due to customization');
198 end if;
199 end if;
200 TRANSLATE_REGION_BY_KEY (X_REGION_KEY => UPDATE_REGION_BY_KEY.X_REGION_KEY,
201 X_TITLE => UPDATE_REGION_BY_KEY.X_TITLE,
202 X_DESCRIPTION => UPDATE_REGION_BY_KEY.X_DESCRIPTION,
203 X_OWNER => UPDATE_REGION_BY_KEY.X_OWNER,
204 X_LAST_UPDATE_DATE => UPDATE_REGION_BY_KEY.X_LAST_UPDATE_DATE);
205 exception
206 when no_data_found then
207 INSERT_REGION_BY_KEY (X_REGION_KEY => UPDATE_REGION_BY_KEY.X_REGION_KEY,
208 X_REGION => UPDATE_REGION_BY_KEY.X_REGION,
209 X_CONTENT_TYPE => UPDATE_REGION_BY_KEY.X_CONTENT_TYPE,
210 X_SEQUENCE => UPDATE_REGION_BY_KEY.X_SEQUENCE,
211 X_MESSAGE_TYPE => UPDATE_REGION_BY_KEY.X_MESSAGE_TYPE,
212 X_MESSAGE_NAME => UPDATE_REGION_BY_KEY.X_MESSAGE_NAME,
213 X_CUSTOMIZATION_LEVEL => UPDATE_REGION_BY_KEY.X_CUSTOMIZATION_LEVEL,
214 X_IS_SEEDED => UPDATE_REGION_BY_KEY.X_IS_SEEDED,
215 X_STATUS => UPDATE_REGION_BY_KEY.X_STATUS,
216 X_TITLE => UPDATE_REGION_BY_KEY.X_TITLE,
217 X_DESCRIPTION => UPDATE_REGION_BY_KEY.X_DESCRIPTION,
218 X_OWNER => UPDATE_REGION_BY_KEY.X_OWNER,
219 X_LAST_UPDATE_DATE => UPDATE_REGION_BY_KEY.X_LAST_UPDATE_DATE);
220 end UPDATE_REGION_BY_KEY;
221
222 procedure ADD_LANGUAGE is
223 begin
224 insert into WF_WL_REGIONS_TL (REGION_ID,
225 TITLE,
226 DESCRIPTION,
227 LANGUAGE,
228 SOURCE_LANG,
229 CREATION_DATE,
230 CREATED_BY,
231 LAST_UPDATE_DATE,
232 LAST_UPDATED_BY,
233 LAST_UPDATE_LOGIN,
234 SECURITY_GROUP_ID)
235 select B.REGION_ID,
236 B.TITLE,
237 B.DESCRIPTION,
238 L.LANGUAGE_CODE,
239 B.SOURCE_LANG,
240 B.CREATION_DATE,
241 B.CREATED_BY,
242 B.LAST_UPDATE_DATE,
243 B.LAST_UPDATED_BY,
244 B.LAST_UPDATE_LOGIN,
245 B.SECURITY_GROUP_ID
246 from WF_WL_REGIONS_TL B, FND_LANGUAGES L
247 where L.INSTALLED_FLAG in ('I', 'B')
248 AND B.LANGUAGE = userenv('LANG')
249 and not exists
250 (select NULL
251 from WF_WL_REGIONS_TL TL
252 where B.REGION_ID = TL.REGION_ID and
253 TL.LANGUAGE = L.LANGUAGE_CODE);
254 exception
255 when others then
256 raise_application_error (-20000, 'WF_WL_REGIONS_PKG.ADD_LANGUAGE: unable to add '||userenv('LANG')||' translation due to error '||sqlcode);
257 end ADD_LANGUAGE;
258
259 procedure TRANSLATE_REGION_BY_KEY (X_REGION_KEY in VARCHAR2,
260 X_TITLE in VARCHAR2,
261 X_DESCRIPTION in VARCHAR2,
262 X_OWNER IN VARCHAR2,
263 X_LAST_UPDATE_DATE IN VARCHAR2) is
264 l_region_id number := FIND_REGION_ID(X_REGION_KEY);
265 l_luby number := FND_LOAD_UTIL.OWNER_ID(X_OWNER);
266 l_ludate date := nvl(to_date(X_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);
267 BEGIN
268 UPDATE WF_WL_REGIONS_TL TL
269 SET TL.TITLE = X_TITLE,
270 TL.DESCRIPTION = X_DESCRIPTION,
271 TL.SOURCE_LANG = userenv('LANG'),
272 TL.LAST_UPDATE_DATE = l_ludate,
273 TL.LAST_UPDATED_BY = l_luby
274 WHERE TL.REGION_ID = l_region_id and
275 userenv('LANG') in (TL.LANGUAGE, TL.SOURCE_LANG);
276 IF (sql%rowcount=0) THEN
277 raise_application_error(-20000, 'WF_WL_REGIONS_PKG.TRANSLATE_REGION_BY_KEY unable to find region '
278 ||X_REGION_KEY||'. Ensure base language is uploaded first');
279 END IF;
280
281 end TRANSLATE_REGION_BY_KEY;
282 end WF_WL_REGIONS_PKG;