[Home] [Help]
PACKAGE BODY: APPS.IEC_G_CPN_PERSONALIZE_PKG
Source
1 PACKAGE BODY IEC_G_CPN_PERSONALIZE_PKG as
2 /* $Header: IECCPB.pls 120.3 2005/07/13 13:11:08 appldev noship $ */
3
4
5 procedure INSERT_ROW (
6 X_ROWID out nocopy VARCHAR2,
7 X_CPN_PERSONALIZE_ID in NUMBER,
8 X_SEARCH_NAME in VARCHAR2,
9 X_OBJECT_VERSION_NUMBER in NUMBER,
10 X_OWNER in VARCHAR2,
11 X_CREATION_DATE in DATE,
12 X_CREATED_BY in NUMBER,
13 X_LAST_UPDATE_DATE in DATE,
14 X_LAST_UPDATED_BY in NUMBER,
15 X_LAST_UPDATE_LOGIN in NUMBER
16 ) is
17 cursor C is select ROWID from IEC_G_CPN_PERSONALIZE_B
18 where CPN_PERSONALIZE_ID = X_CPN_PERSONALIZE_ID
19 ;
20 begin
21 x_rowid := NULL;
22
23 insert into IEC_G_CPN_PERSONALIZE_B (
24 CPN_PERSONALIZE_ID,
25 OBJECT_VERSION_NUMBER,
26 OWNER,
27 CREATION_DATE,
28 CREATED_BY,
29 LAST_UPDATE_DATE,
30 LAST_UPDATED_BY,
31 LAST_UPDATE_LOGIN
32 ) values (
33 X_CPN_PERSONALIZE_ID,
34 X_OBJECT_VERSION_NUMBER,
35 X_OWNER,
36 X_CREATION_DATE,
37 X_CREATED_BY,
38 X_LAST_UPDATE_DATE,
39 X_LAST_UPDATED_BY,
40 X_LAST_UPDATE_LOGIN
41 );
42
43 insert into IEC_G_CPN_PERSONALIZE_TL (
44 CPN_PERSONALIZE_ID,
45 SEARCH_NAME,
46 CREATED_BY,
47 CREATION_DATE,
48 LAST_UPDATED_BY,
49 LAST_UPDATE_DATE,
50 LAST_UPDATE_LOGIN,
51 LANGUAGE,
52 SOURCE_LANG
53 ) select
54 X_CPN_PERSONALIZE_ID,
55 X_SEARCH_NAME,
56 X_CREATED_BY,
57 X_CREATION_DATE,
58 X_LAST_UPDATED_BY,
59 X_LAST_UPDATE_DATE,
60 X_LAST_UPDATE_LOGIN,
61 L.LANGUAGE_CODE,
62 userenv('LANG')
63 from FND_LANGUAGES L
64 where L.INSTALLED_FLAG in ('I', 'B')
65 and not exists
66 (select NULL
67 from IEC_G_CPN_PERSONALIZE_TL T
68 where T.CPN_PERSONALIZE_ID = X_CPN_PERSONALIZE_ID
69 and T.LANGUAGE = L.LANGUAGE_CODE);
70
71 open c;
72 fetch c into X_ROWID;
73 if (c%notfound) then
74 close c;
75 raise no_data_found;
76 end if;
77 close c;
78
79 end INSERT_ROW;
80
81
82 procedure LOCK_ROW (
83 X_CPN_PERSONALIZE_ID in NUMBER,
84 X_OBJECT_VERSION_NUMBER in NUMBER,
85 X_OWNER in VARCHAR2
86 ) is
87 cursor c is select
88 OBJECT_VERSION_NUMBER
89 from IEC_G_CPN_PERSONALIZE_B
90 where CPN_PERSONALIZE_ID = X_CPN_PERSONALIZE_ID
91 for update of CPN_PERSONALIZE_ID nowait;
92 recinfo c%rowtype;
93
94 begin
95 open c;
96 fetch c into recinfo;
97 if (c%notfound) then
98 close c;
99 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
100 app_exception.raise_exception;
101 end if;
102 close c;
103 if recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER then
104 null;
105 else
106 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
107 app_exception.raise_exception;
108 end if;
109
110 return;
111 end LOCK_ROW;
112
113
114 procedure UPDATE_ROW (
115 X_CPN_PERSONALIZE_ID in NUMBER,
116 X_SEARCH_NAME in VARCHAR2,
117 X_OBJECT_VERSION_NUMBER in NUMBER,
118 X_OWNER in VARCHAR2,
119 X_LAST_UPDATE_DATE in DATE,
120 X_LAST_UPDATED_BY in NUMBER,
121 X_LAST_UPDATE_LOGIN in NUMBER
122 ) is
123 begin
124 update IEC_G_CPN_PERSONALIZE_B set
125 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
126 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
127 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
128 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
129 where CPN_PERSONALIZE_ID = X_CPN_PERSONALIZE_ID ;
130
131 if (sql%notfound) then
132 raise no_data_found;
133 end if;
134
135 end UPDATE_ROW;
136
137
138 procedure DELETE_ROW (
139 X_CPN_PERSONALIZE_ID in NUMBER
140 ) is
141 begin
142 delete from IEC_G_CPN_PERSONALIZE_B
143 where CPN_PERSONALIZE_ID = X_CPN_PERSONALIZE_ID;
144
145 delete from IEC_G_CPN_PERSONALIZE_TL
146 where CPN_PERSONALIZE_ID = X_CPN_PERSONALIZE_ID;
147
148 if (sql%notfound) then
149 raise no_data_found;
150 end if;
151 end DELETE_ROW;
152
153 procedure TRANSLATE_ROW (
154 X_CPN_PERSONALIZE_ID in NUMBER,
155 X_SEARCH_NAME in VARCHAR2,
156 P_OWNER IN VARCHAR2
157 ) is
158 BEGIN
159 UPDATE IEC_G_CPN_PERSONALIZE_TL SET
160 source_lang = userenv('LANG'),
161 SEARCH_NAME = X_SEARCH_NAME,
162 last_update_date = sysdate,
163 last_updated_by = fnd_load_util.owner_id(P_OWNER),
164 last_update_login = 0
165 WHERE CPN_PERSONALIZE_ID = X_CPN_PERSONALIZE_ID
166 AND userenv('LANG') IN (language, source_lang);
167
168 END TRANSLATE_ROW;
169
170
171 procedure LOAD_ROW (
172 X_CPN_PERSONALIZE_ID NUMBER,
173 X_SEARCH_NAME in VARCHAR2,
174 X_OWNER in VARCHAR2,
175 X_OWNER_REF in VARCHAR2
176 ) is
177
178
179 USER_ID NUMBER;
180 ROW_ID VARCHAR2(500);
181
182 begin
183
184 USER_ID := fnd_load_util.owner_id(X_OWNER_REF);
185
186
187 UPDATE_ROW ( X_CPN_PERSONALIZE_ID
188 , X_SEARCH_NAME
189 , 0
190 , X_OWNER
191 , SYSDATE
192 , USER_ID
193 , 0);
194
195 exception
196 when no_data_found then
197 INSERT_ROW ( ROW_ID
198 , X_CPN_PERSONALIZE_ID
199 , X_SEARCH_NAME
200 , 0
201 , X_OWNER
202 , SYSDATE
203 , USER_ID
204 , SYSDATE
205 , USER_ID
206 , 0);
207
208 end LOAD_ROW;
209
210 procedure LOAD_SEED_ROW (
211 X_UPLOAD_MODE in VARCHAR2,
212 X_CPN_PERSONALIZE_ID in NUMBER,
213 X_SEARCH_NAME in VARCHAR2,
214 X_OWNER in VARCHAR2,
215 X_OWNER_REF in VARCHAR2
216 ) is
217 begin
218 if(X_UPLOAD_MODE = 'NLS') then
219 IEC_G_CPN_PERSONALIZE_PKG.TRANSLATE_ROW (
220 X_CPN_PERSONALIZE_ID,
221 X_SEARCH_NAME,
222 X_OWNER_REF);
223 else
224 IEC_G_CPN_PERSONALIZE_PKG.LOAD_ROW (
225 X_CPN_PERSONALIZE_ID,
226 X_SEARCH_NAME,
227 X_OWNER,
228 X_OWNER_REF);
229 end if;
230 end LOAD_SEED_ROW;
231
232 procedure ADD_LANGUAGE
233 is
234 begin
235 delete from IEC_G_CPN_PERSONALIZE_TL T
236 where not exists
237 (select NULL
238 from IEC_G_CPN_PERSONALIZE_B B
239 where B.CPN_PERSONALIZE_ID = T.CPN_PERSONALIZE_ID
240 );
241
242 update IEC_G_CPN_PERSONALIZE_TL T set (
243 SEARCH_NAME
244 ) = (select
245 B.SEARCH_NAME
246 from IEC_G_CPN_PERSONALIZE_TL B
247 where B.CPN_PERSONALIZE_ID = T.CPN_PERSONALIZE_ID
248 and B.LANGUAGE = T.SOURCE_LANG)
249 where (
250 T.CPN_PERSONALIZE_ID,
251 T.LANGUAGE
252 ) in (select
253 SUBT.CPN_PERSONALIZE_ID,
254 SUBT.LANGUAGE
255 from IEC_G_CPN_PERSONALIZE_TL SUBB, IEC_G_CPN_PERSONALIZE_TL SUBT
256 where SUBB.CPN_PERSONALIZE_ID = SUBT.CPN_PERSONALIZE_ID
257 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
258 and (SUBB.SEARCH_NAME <> SUBT.SEARCH_NAME
259 or (SUBB.SEARCH_NAME is null and SUBT.SEARCH_NAME is not null)
260 or (SUBB.SEARCH_NAME is not null and SUBT.SEARCH_NAME is null)
261 ));
262
263 insert into IEC_G_CPN_PERSONALIZE_TL (
264 CPN_PERSONALIZE_ID,
265 SEARCH_NAME,
266 CREATED_BY,
267 CREATION_DATE,
268 LAST_UPDATED_BY,
269 LAST_UPDATE_DATE,
270 LAST_UPDATE_LOGIN,
271 LANGUAGE,
272 SOURCE_LANG
273 ) select
274 B.CPN_PERSONALIZE_ID,
275 B.SEARCH_NAME,
276 B.CREATED_BY,
277 B.CREATION_DATE,
278 B.LAST_UPDATED_BY,
279 B.LAST_UPDATE_DATE,
280 B.LAST_UPDATE_LOGIN,
281 L.LANGUAGE_CODE,
282 B.SOURCE_LANG
283 from IEC_G_CPN_PERSONALIZE_TL B, FND_LANGUAGES L
284 where L.INSTALLED_FLAG in ('I', 'B')
285 and B.LANGUAGE = userenv('LANG')
286 and not exists
287 (select NULL
288 from IEC_G_CPN_PERSONALIZE_TL T
289 where T.CPN_PERSONALIZE_ID = B.CPN_PERSONALIZE_ID
290 and T.LANGUAGE = L.LANGUAGE_CODE);
291 end ADD_LANGUAGE;
292
293 end IEC_G_CPN_PERSONALIZE_PKG;