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.IEB_SVC_CAT_TEMPS_SEED_PKG

Source


1 PACKAGE BODY ieb_svc_cat_temps_seed_pkg AS
2 /* $Header: IEBSCTPB.pls 120.3 2005/09/29 06:03:45 appldev ship $ */
3      PROCEDURE insert_row(
4           x_wbsc_id                          IN NUMBER
5         , x_svcpln_svcpln_id                 IN NUMBER
6         , x_created_by                       IN NUMBER
7         , x_creation_date                    IN DATE
8         , x_last_update_date                 IN DATE
9         , x_last_updated_by                  IN NUMBER
10         , x_last_update_login                IN NUMBER
11         , x_media_type                       IN VARCHAR2
12         , x_depth                            IN NUMBER
13         , x_parent_id                        IN NUMBER
14         , x_original_name                    IN VARCHAR2
15         , x_active_y_n                       IN VARCHAR2
16         , x_source_table_name                IN VARCHAR2
17         , x_src_tbl_key_column               IN VARCHAR2
18         , x_src_tbl_value_column             IN VARCHAR2
19         , x_src_tbl_value_translation_fl     IN VARCHAR2
20         , x_src_tbl_where_clause             IN VARCHAR2
21         , x_MEDIA_TYPE_ID                    IN NUMBER
22         , x_SERVICE_CATEGORY_NAME            IN VARCHAR2
23         , x_DESCRIPTION                      IN VARCHAR2
24         , x_MEDIA_CATEGORY_LABEL             IN VARCHAR2
25      ) IS
26         CURSOR l_insert IS
27           SELECT 'X'
28           FROM ieb_svc_cat_temps_b
29           WHERE wbsc_id = x_wbsc_id;
30      BEGIN
31         INSERT INTO ieb_svc_cat_temps_b (
32           wbsc_id
33         , svcpln_svcpln_id
34         , created_by
35         , creation_date
36         , last_update_date
37         , last_updated_by
38         , last_update_login
39         , media_type
40         , depth
41         , parent_id
42         , original_name
43         , active_y_n
44         , source_table_name
45         , src_tbl_key_column
46         , src_tbl_value_column
47         , src_tbl_value_translation_flag
48         , src_tbl_where_clause
49         , media_type_id
50         ) VALUES (
51           x_wbsc_id
52         , x_svcpln_svcpln_id
53         , DECODE(x_created_by,FND_API.G_MISS_NUM,NULL,x_created_by)
54         , DECODE(x_creation_date,FND_API.G_MISS_DATE,TO_DATE(NULL),x_creation_date)
55         , DECODE(x_last_update_date,FND_API.G_MISS_DATE,TO_DATE(NULL),x_last_update_date)
56         , DECODE(x_last_updated_by,FND_API.G_MISS_NUM,NULL,x_last_updated_by)
57         , DECODE(x_last_update_login,FND_API.G_MISS_NUM,NULL,x_last_update_login)
58         , DECODE(x_media_type,FND_API.G_MISS_CHAR,NULL,x_media_type)
59         , DECODE(x_depth,FND_API.G_MISS_NUM,NULL,x_depth)
60         , DECODE(x_parent_id,FND_API.G_MISS_NUM,NULL,x_parent_id)
61         , DECODE(x_original_name,FND_API.G_MISS_CHAR,NULL,x_original_name)
62         , DECODE(x_active_y_n,FND_API.G_MISS_CHAR,NULL,x_active_y_n)
63         , DECODE(x_source_table_name,FND_API.G_MISS_CHAR,NULL,x_source_table_name)
64         , DECODE(x_src_tbl_key_column,FND_API.G_MISS_CHAR,NULL,x_src_tbl_key_column)
65         , DECODE(x_src_tbl_value_column,FND_API.G_MISS_CHAR,NULL,x_src_tbl_value_column)
66         , DECODE(x_src_tbl_value_translation_fl,FND_API.G_MISS_CHAR,NULL,x_src_tbl_value_translation_fl)
67         , DECODE(x_src_tbl_where_clause,FND_API.G_MISS_CHAR,NULL,x_src_tbl_where_clause)
68         , x_MEDIA_TYPE_ID
69         );
70 
71         INSERT INTO ieb_svc_cat_temps_tl (
72           wbsc_id
73         , created_by
74         , creation_date
75         , last_update_date
76         , last_updated_by
77         , last_update_login
78         , SERVICE_CATEGORY_NAME
79         , DESCRIPTION
80         , MEDIA_CATEGORY_LABEL
81         , language
82         , source_lang
83        ) select
84             x_wbsc_id
85           , DECODE(x_created_by,FND_API.G_MISS_NUM,NULL,x_created_by)
86           , DECODE(x_creation_date,FND_API.G_MISS_DATE,TO_DATE(NULL),x_creation_date)
87           , DECODE(x_last_update_date,FND_API.G_MISS_DATE,TO_DATE(NULL),x_last_update_date)
88           , DECODE(x_last_updated_by,FND_API.G_MISS_NUM,NULL,x_last_updated_by)
89           , DECODE(x_last_update_login,FND_API.G_MISS_NUM,NULL,x_last_update_login)
90           , DECODE(x_SERVICE_CATEGORY_NAME,FND_API.G_MISS_CHAR,NULL,x_SERVICE_CATEGORY_NAME)
91           , DECODE(x_DESCRIPTION,FND_API.G_MISS_CHAR,NULL,x_DESCRIPTION)
92           , DECODE(x_MEDIA_CATEGORY_LABEL,FND_API.G_MISS_CHAR,NULL,x_MEDIA_CATEGORY_LABEL)
93           , l.language_code
94           , USERENV('LANG')
95         from fnd_languages l
96         WHERE l.installed_flag IN ('I', 'B')
97         AND NOT EXISTS
98          (SELECT NULL
99          FROM ieb_svc_cat_temps_tl t
100          WHERE t.wbsc_id = x_wbsc_id
101          AND t.language = l.language_code);
102 
103      END insert_row;
104 
105      PROCEDURE update_row(
106           x_wbsc_id                          IN NUMBER
107         , x_svcpln_svcpln_id                 IN NUMBER
108         , x_last_update_date                 IN DATE
109         , x_last_updated_by                  IN NUMBER
110         , x_last_update_login                IN NUMBER
111         , x_media_type                       IN VARCHAR2
112         , x_depth                            IN NUMBER
113         , x_parent_id                        IN NUMBER
114         , x_original_name                    IN VARCHAR2
115         , x_active_y_n                       IN VARCHAR2
116         , x_source_table_name                IN VARCHAR2
117         , x_src_tbl_key_column               IN VARCHAR2
118         , x_src_tbl_value_column             IN VARCHAR2
119         , x_src_tbl_value_translation_fl     IN VARCHAR2
120         , x_src_tbl_where_clause             IN VARCHAR2
121         , x_media_type_id                    IN NUMBER
122         , x_SERVICE_CATEGORY_NAME            IN VARCHAR2
123         , x_DESCRIPTION                      IN VARCHAR2
124         , x_MEDIA_CATEGORY_LABEL             IN VARCHAR2
125      ) IS
126      BEGIN
127         UPDATE ieb_svc_cat_temps_b
128         SET
129          svcpln_svcpln_id=DECODE(x_svcpln_svcpln_id,FND_API.G_MISS_NUM,NULL,x_svcpln_svcpln_id)
130         , last_update_date=DECODE(x_last_update_date,FND_API.G_MISS_DATE,TO_DATE(NULL),x_last_update_date)
131         , last_updated_by=DECODE(x_last_updated_by,FND_API.G_MISS_NUM,NULL,x_last_updated_by)
132         , last_update_login=DECODE(x_last_update_login,FND_API.G_MISS_NUM,NULL,x_last_update_login)
133         , media_type=DECODE(x_media_type,FND_API.G_MISS_CHAR,NULL,x_media_type)
134         , depth=DECODE(x_depth,FND_API.G_MISS_NUM,NULL,x_depth)
135         , parent_id=DECODE(x_parent_id,FND_API.G_MISS_NUM,NULL,x_parent_id)
136         , original_name=DECODE(x_original_name,FND_API.G_MISS_CHAR,NULL,x_original_name)
137         , active_y_n=DECODE(x_active_y_n,FND_API.G_MISS_CHAR,NULL,x_active_y_n)
138         , source_table_name=DECODE(x_source_table_name,FND_API.G_MISS_CHAR,NULL,x_source_table_name)
139         , src_tbl_key_column=DECODE(x_src_tbl_key_column,FND_API.G_MISS_CHAR,NULL,x_src_tbl_key_column)
140         , src_tbl_value_column=DECODE(x_src_tbl_value_column,FND_API.G_MISS_CHAR,NULL,x_src_tbl_value_column)
141         , src_tbl_value_translation_flag=DECODE(x_src_tbl_value_translation_fl,FND_API.G_MISS_CHAR,NULL,x_src_tbl_value_translation_fl)
142         , src_tbl_where_clause=DECODE(x_src_tbl_where_clause,FND_API.G_MISS_CHAR,NULL,x_src_tbl_where_clause)
143         , media_type_id = x_media_type_id
144         WHERE
145           wbsc_id=x_wbsc_id;
146 
147         IF (SQL%NOTFOUND) THEN
148           RAISE NO_DATA_FOUND;
149         END IF;
150 
151         UPDATE ieb_svc_cat_temps_tl
152         SET
153           last_update_date=DECODE(x_last_update_date,FND_API.G_MISS_DATE,TO_DATE(NULL),x_last_update_date)
154         , last_updated_by=DECODE(x_last_updated_by,FND_API.G_MISS_NUM,NULL,x_last_updated_by)
155         , last_update_login=DECODE(x_last_update_login,FND_API.G_MISS_NUM,NULL,x_last_update_login)
156         , SERVICE_CATEGORY_NAME = DECODE(x_SERVICE_CATEGORY_NAME,FND_API.G_MISS_CHAR,NULL,x_SERVICE_CATEGORY_NAME)
157         , DESCRIPTION = DECODE(x_DESCRIPTION,FND_API.G_MISS_CHAR,NULL,x_DESCRIPTION)
158         , MEDIA_CATEGORY_LABEL = DECODE(x_MEDIA_CATEGORY_LABEL,FND_API.G_MISS_CHAR,NULL,x_MEDIA_CATEGORY_LABEL)
159         , source_lang = USERENV('LANG')
160          WHERE
161             wbsc_id=x_wbsc_id
162          AND USERENV('LANG') IN (language, source_lang);
163 
164         IF (SQL%NOTFOUND) THEN
165           RAISE NO_DATA_FOUND;
166         END IF;
167 
168      END update_row;
169 
170      PROCEDURE load_row (
171           p_wbsc_id IN NUMBER,
172           p_svcpln_svcpln_id IN NUMBER,
173           p_media_type IN VARCHAR2,
174           p_depth IN NUMBER,
175           p_parent_id IN NUMBER,
176           p_original_name IN VARCHAR2,
177           p_active_y_n IN VARCHAR2,
178           p_source_table_name IN VARCHAR2,
179           p_src_tbl_key_column IN VARCHAR2,
180           p_src_tbl_value_column IN VARCHAR2,
181           p_src_tbl_value_translation_fl IN VARCHAR2,
182           p_src_tbl_where_clause IN VARCHAR2,
183           p_media_type_id        IN NUMBER,
184           p_SERVICE_CATEGORY_NAME IN VARCHAR2,
185           p_DESCRIPTION IN VARCHAR2,
186           p_MEDIA_CATEGORY_LABEL IN VARCHAR2,
187           p_OWNER IN VARCHAR2) is
188     BEGIN
189       DECLARE
190         user_id  number := 0;
191       BEGIN
192 
193 	   user_id := fnd_load_util.owner_id(p_OWNER);
194 
195         update_row(p_wbsc_id, p_svcpln_svcpln_id, sysdate, user_id, 0, p_media_type,
196                    p_depth, p_parent_id, p_original_name, p_active_y_n,
197                    p_source_table_name, p_src_tbl_key_column, p_src_tbl_value_column,
198                    p_src_tbl_value_translation_fl, p_src_tbl_where_clause,  p_media_type_id,
199                    p_SERVICE_CATEGORY_NAME, p_DESCRIPTION, p_MEDIA_CATEGORY_LABEL);
200 
201       EXCEPTION
202          when no_data_found then
203 
204         insert_row(p_wbsc_id, p_svcpln_svcpln_id, user_id, sysdate, sysdate, user_id, 0,
205                    p_media_type,
206                    p_depth, p_parent_id,  p_original_name, p_active_y_n,
207                    p_source_table_name, p_src_tbl_key_column, p_src_tbl_value_column,
208                    p_src_tbl_value_translation_fl, p_src_tbl_where_clause, p_media_type_id,
209                    p_SERVICE_CATEGORY_NAME, p_DESCRIPTION, p_MEDIA_CATEGORY_LABEL);
210 
211       END;
212     END load_row;
213 
214      PROCEDURE load_seed_row (
215           p_wbsc_id IN NUMBER,
216           p_svcpln_svcpln_id IN NUMBER,
217           p_media_type IN VARCHAR2,
218           p_depth IN NUMBER,
219           p_parent_id IN NUMBER,
220           p_original_name IN VARCHAR2,
221           p_active_y_n IN VARCHAR2,
222           p_source_table_name IN VARCHAR2,
223           p_src_tbl_key_column IN VARCHAR2,
224 	     p_src_tbl_value_column IN VARCHAR2,
225 	     p_src_tbl_value_translation_fl IN VARCHAR2,
226 	     p_src_tbl_where_clause IN VARCHAR2,
227 	     p_media_type_id        IN NUMBER,
228 	     p_SERVICE_CATEGORY_NAME IN VARCHAR2,
229 	     p_DESCRIPTION IN VARCHAR2,
230 	     p_MEDIA_CATEGORY_LABEL IN VARCHAR2,
231 	     p_OWNER IN VARCHAR2,
232 		p_UPLOAD_MODE IN VARCHAR2) is
233 	BEGIN
234 		if (p_UPLOAD_MODE = 'NLS') then
235             ieb_svc_cat_temps_seed_pkg.TRANSLATE_ROW (
236 		                  p_WBSC_ID,
237 			             p_SERVICE_CATEGORY_NAME,
238 			             p_DESCRIPTION,
239 			             p_MEDIA_CATEGORY_LABEL,
240 			             p_OWNER);
241 	      else
242              ieb_svc_cat_temps_seed_pkg.LOAD_ROW (
243 		             p_WBSC_ID,
244 		             p_SVCPLN_SVCPLN_ID,
245 		             p_MEDIA_TYPE,
246 			        p_DEPTH,
247 			        p_PARENT_ID,
248 			        p_ORIGINAL_NAME,
249 			        p_ACTIVE_Y_N,
250 			        p_SOURCE_TABLE_NAME,
251 			        p_SRC_TBL_KEY_COLUMN,
252 			        p_SRC_TBL_VALUE_COLUMN,
253 			        p_SRC_TBL_VALUE_TRANSLATION_FL,
254 			        p_SRC_TBL_WHERE_CLAUSE,
255 			        p_MEDIA_TYPE_ID,
256 			        p_SERVICE_CATEGORY_NAME,
257 			        p_DESCRIPTION,
258 			        p_MEDIA_CATEGORY_LABEL,
259 			        p_OWNER);
260 	      end if;
261 	END load_seed_row;
262 
263     PROCEDURE translate_row (
264           p_wbsc_id IN NUMBER,
265           p_SERVICE_CATEGORY_NAME IN VARCHAR2,
266           p_DESCRIPTION IN VARCHAR2,
267           p_MEDIA_CATEGORY_LABEL IN VARCHAR2,
268           p_OWNER IN VARCHAR2) is
269     BEGIN
270       DECLARE
271         user_id  number := 0;
272       BEGIN
273         user_id := fnd_load_util.owner_id(p_OWNER);
274 
275 
276        UPDATE ieb_svc_cat_temps_tl
277         SET
278           last_update_date=sysdate
279         , last_updated_by=user_id
280         , last_update_login=0
281         , SERVICE_CATEGORY_NAME = DECODE(p_SERVICE_CATEGORY_NAME,FND_API.G_MISS_CHAR,
282                                          NULL,p_SERVICE_CATEGORY_NAME)
283         , DESCRIPTION = DECODE(p_DESCRIPTION,FND_API.G_MISS_CHAR,NULL,p_DESCRIPTION)
284         , MEDIA_CATEGORY_LABEL = DECODE(p_MEDIA_CATEGORY_LABEL,FND_API.G_MISS_CHAR,NULL,                                                    p_MEDIA_CATEGORY_LABEL)
285         , source_lang = USERENV('LANG')
286          WHERE
287             wbsc_id=p_wbsc_id
288          AND USERENV('LANG') IN (language, source_lang);
289 
290       END;
291 
292     END translate_row;
293 
294 procedure ADD_LANGUAGE
295 is
296 begin
297   delete from IEB_SVC_CAT_TEMPS_TL T
298   where not exists
299     (select NULL
300     from IEB_SVC_CAT_TEMPS_B B
301     where B.WBSC_ID = T.WBSC_ID
302     );
303 
304   update IEB_SVC_CAT_TEMPS_TL T set (
305       SERVICE_CATEGORY_NAME,
306       DESCRIPTION
307     ) = (select
308       B.SERVICE_CATEGORY_NAME,
309       B.DESCRIPTION
310     from IEB_SVC_CAT_TEMPS_TL B
311     where B.WBSC_ID = T.WBSC_ID
312     and B.LANGUAGE = T.SOURCE_LANG)
313   where (
314       T.WBSC_ID,
315       T.LANGUAGE
316   ) in (select
317       SUBT.WBSC_ID,
318       SUBT.LANGUAGE
319     from IEB_SVC_CAT_TEMPS_TL SUBB, IEB_SVC_CAT_TEMPS_TL SUBT
320     where SUBB.WBSC_ID = SUBT.WBSC_ID
321     and SUBB.LANGUAGE = SUBT.SOURCE_LANG
322     and (SUBB.SERVICE_CATEGORY_NAME <> SUBT.SERVICE_CATEGORY_NAME
323       or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
324       or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
325       or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
326   ));
327 
328     insert into IEB_SVC_CAT_TEMPS_TL (
329       DESCRIPTION,
330       CREATION_DATE,
331       LAST_UPDATED_BY,
332       LAST_UPDATE_DATE,
333       LAST_UPDATE_LOGIN,
334       SERVICE_CATEGORY_NAME,
335       WBSC_ID,
336       CREATED_BY,
337       LANGUAGE,
338       SOURCE_LANG
339     ) select
340       B.DESCRIPTION,
341       B.CREATION_DATE,
342       B.LAST_UPDATED_BY,
343       B.LAST_UPDATE_DATE,
344       B.LAST_UPDATE_LOGIN,
345       B.SERVICE_CATEGORY_NAME,
346       B.WBSC_ID,
347       B.CREATED_BY,
348       L.LANGUAGE_CODE,
349       B.SOURCE_LANG
350     from IEB_SVC_CAT_TEMPS_TL B, FND_LANGUAGES L
351     where L.INSTALLED_FLAG in ('I', 'B')
352     and B.LANGUAGE = userenv('LANG')
353     and not exists
354       (select NULL
355       from IEB_SVC_CAT_TEMPS_TL T
356       where T.WBSC_ID = B.WBSC_ID
357       and T.LANGUAGE = L.LANGUAGE_CODE);
358 
359 end ADD_LANGUAGE;
360 
361 
362  END ieb_svc_cat_temps_seed_pkg;