[Home] [Help]
PACKAGE BODY: APPS.CSF_DC_QUERIES_PKG
Source
1 package body CSF_DC_QUERIES_PKG as
2 /* $Header: CSFDCQTB.pls 120.0 2005/05/25 10:55:06 appldev noship $ */
3
4 procedure insert_row
5 ( p_row_id IN OUT NOCOPY varchar2
6 , p_query_id IN OUT NOCOPY number
7 , p_created_by IN OUT NOCOPY number
8 , p_creation_date IN OUT NOCOPY date
9 , p_last_updated_by IN OUT NOCOPY number
10 , p_last_update_date IN OUT NOCOPY date
11 , p_last_update_login IN OUT NOCOPY number
12 , p_object_version_number IN OUT NOCOPY number
13 , p_name IN varchar2
14 , p_description IN varchar2
15 , p_where_clause IN varchar2
16 , p_user_id IN number
17 , p_seeded_flag IN varchar2
18 , p_start_date_active IN date
19 , p_end_date_active IN date
20 )
21 is
22
23 cursor c_rowid ( p_query_id number )
24 is
25 select row_id
26 from csf_dc_queries_vl
27 where query_id = p_query_id;
28
29 begin
30
31 if p_query_id is null then
32 select csf_dc_queries_s.nextval
33 into p_query_id
34 from dual;
35 end if;
36
37 if p_created_by is null then
38 p_created_by := fnd_global.user_id;
39 end if;
40
41 if p_last_updated_by is null then
42 p_last_updated_by := fnd_global.user_id;
43 end if;
44
45 if p_last_update_login is null then
46 p_last_update_login := fnd_global.login_id;
47 end if;
48
49 if p_creation_date is null then
50 p_creation_date := sysdate;
51 end if;
52
53 if p_last_update_date is null then
54 p_last_update_date := sysdate;
55 end if;
56
57 insert into csf_dc_queries_b
58 ( query_id
59 , created_by
60 , creation_date
61 , last_updated_by
62 , last_update_date
63 , last_update_login
64 , object_version_number
65 , where_clause
66 , user_id
67 , seeded_flag
68 , start_date_active
69 , end_date_active
70 )
71 values
72 ( p_query_id
73 , p_created_by
74 , p_creation_date
75 , p_last_updated_by
76 , p_last_update_date
77 , p_last_update_login
78 , p_object_version_number
79 , p_where_clause
80 , p_user_id
81 , p_seeded_flag
82 , p_start_date_active
83 , p_end_date_active
84 );
85
86 insert into csf_dc_queries_tl
87 ( query_id
88 , language
89 , source_lang
90 , created_by
91 , creation_date
92 , last_updated_by
93 , last_update_date
94 , last_update_login
95 , name
96 , description
97 )
98 select
99 p_query_id
100 , l.language_code
101 , userenv('LANG')
102 , p_created_by
103 , p_creation_date
104 , p_last_updated_by
105 , p_last_update_date
106 , p_last_update_login
107 , p_name
108 , p_description
109 from fnd_languages l
110 where l.installed_flag in ('I','B')
111 and not exists
112 ( select ''
113 from csf_dc_queries_tl t
114 where t.query_id = p_query_id
115 and t.language = l.language_code );
116
117 open c_rowid ( p_query_id );
118 fetch c_rowid into p_row_id;
119 if c_rowid%notfound
120 then
121 close c_rowid;
122 raise NO_DATA_FOUND;
123 end if;
124 close c_rowid;
125
126 end insert_row;
127
128 procedure update_row
129 ( p_query_id IN OUT NOCOPY number
130 , p_created_by IN OUT NOCOPY number
131 , p_creation_date IN OUT NOCOPY date
132 , p_last_updated_by IN OUT NOCOPY number
133 , p_last_update_date IN OUT NOCOPY date
134 , p_last_update_login IN OUT NOCOPY number
135 , p_object_version_number IN OUT NOCOPY number
136 , p_name IN varchar2
137 , p_description IN varchar2
138 , p_where_clause IN varchar2
139 , p_user_id IN number
140 , p_seeded_flag IN varchar2
141 , p_start_date_active IN date
142 , p_end_date_active IN date
143 )
144 is
145 begin
146
147 if p_last_updated_by is null then
148 p_last_updated_by := fnd_global.user_id;
149 end if;
150
151 if p_last_update_login is null then
152 p_last_update_login := fnd_global.login_id;
153 end if;
154
155 if p_last_update_date is null then
156 p_last_update_date := sysdate;
157 end if;
158
159 update csf_dc_queries_b
160 set last_updated_by = p_last_updated_by
161 , last_update_date = p_last_update_date
162 , last_update_login = p_last_update_login
163 , object_version_number = p_object_version_number
164 , where_clause = p_where_clause
165 , user_id = p_user_id
166 , seeded_flag = p_seeded_flag
167 , start_date_active = p_start_date_active
168 , end_date_active = p_end_date_active
169 where query_id = p_query_id;
170
171 if sql%notfound
172 then
173 raise NO_DATA_FOUND;
174 end if;
175
176 update csf_dc_queries_tl
177 set source_lang = userenv('LANG')
178 , last_updated_by = p_last_updated_by
179 , last_update_date = p_last_update_date
180 , last_update_login = p_last_update_login
181 , name = p_name
182 , description = p_description
183 where query_id = p_query_id
184 and userenv('lang') in (language, source_lang);
185
186 end update_row;
187
188 procedure translate_row
189 (
190 p_query_id IN varchar2
191 , p_owner IN varchar2
192 , p_name IN varchar2
193 , p_description IN varchar2
194 )
195 is
196 begin
197 update csf_dc_queries_tl
198 set description = p_description,
199 name = p_name,
200 last_update_date = sysdate,
201 last_updated_by = decode(p_owner, 'SEED', 1, 0),
202 last_update_login = 0,
203 source_lang = userenv('LANG')
204 where query_id = p_query_id
205 and userenv('LANG') in (language, source_lang);
206 end translate_row;
207
208 procedure load_row
209 ( p_query_id IN varchar2
210 , p_owner IN varchar2
211 , p_object_version_number IN varchar2
212 , p_name IN varchar2
213 , p_description IN varchar2
214 , p_where_clause IN varchar2
215 , p_user_id IN varchar2
216 , p_seeded_flag IN varchar2
217 , p_start_date_active IN varchar2
218 , p_end_date_active IN varchar2
219 )
220 is
221 l_query_id number;
222 l_object_version_number number;
223 l_update_date date;
224 l_row_id varchar2(64);
225 l_user number;
226
227 begin
228 l_query_id := to_number(p_query_id);
229 l_object_version_number := to_number(p_object_version_number);
230 l_update_date := sysdate;
231 l_user := 0;
232
233 if (p_owner = 'SEED')
234 then
235 l_user := 1;
236 end if;
237
238 update_row
239 ( p_query_id => l_query_id
240 , p_created_by => l_user
241 , p_creation_date => l_update_date
242 , p_last_updated_by => l_user
243 , p_last_update_date => l_update_date
244 , p_last_update_login => l_user
245 , p_object_version_number => l_object_version_number
246 , p_name => p_name
247 , p_description => p_description
248 , p_where_clause => p_where_clause
249 , p_user_id => to_number(p_user_id)
250 , p_seeded_flag => p_seeded_flag
251 , p_start_date_active => to_date(p_start_date_active,'DD/MM/RRRR')
252 , p_end_date_active => to_date(p_end_date_active,'DD/MM/RRRR')
253 );
254 exception
255 when NO_DATA_FOUND then
256 insert_row
257 ( p_row_id => l_row_id
258 , p_query_id => l_query_id
259 , p_created_by => l_user
260 , p_creation_date => l_update_date
261 , p_last_updated_by => l_user
262 , p_last_update_date => l_update_date
263 , p_last_update_login => l_user
264 , p_object_version_number => l_object_version_number
265 , p_name => p_name
266 , p_description => p_description
267 , p_where_clause => p_where_clause
268 , p_user_id => to_number(p_user_id)
269 , p_seeded_flag => p_seeded_flag
270 , p_start_date_active => to_date(p_start_date_active,'DD/MM/RRRR')
271 , p_end_date_active => to_date(p_end_date_active,'DD/MM/RRRR')
272 );
273 end load_row;
274
275
276 PROCEDURE add_language
277 IS
278 BEGIN
279
280 DELETE FROM csf_dc_queries_tl t
281 WHERE NOT EXISTS
282 (SELECT ''
283 FROM csf_dc_queries_tl tl
284 WHERE tl.query_id = t.query_id
285 );
286
287 UPDATE csf_dc_queries_tl t SET ( name , description) =
288 ( SELECT tl.name, tl.description
289 FROM csf_dc_queries_tl tl
290 WHERE tl.query_id = t.query_id
291 AND tl.language = t.source_lang)
292 WHERE (t.query_id, t.language) IN
293 (SELECT subt.query_id, subt.language
294 FROM csf_dc_queries_tl subb, csf_dc_queries_tl subt
295 WHERE subb.query_id = subt.query_id
296 AND subb.language = subt.source_lang
297 AND (subb.name <> subt.name
298 OR subb.description <> subt.description
299 OR (subb.description is null and subt.description is not null)
300 OR (subb.description is not null and subt.description is null))
301 );
302
303 INSERT INTO csf_dc_queries_tl
304 ( query_id
305 , description
306 , name
307 , created_by
308 , creation_date
309 , last_updated_by
310 , last_update_date
311 , last_update_login
312 , language
313 , source_lang
314 )
315 SELECT b.query_id
316 , b.description
317 , b.name
318 , b.created_by
319 , b.creation_date
320 , b.last_updated_by
321 , b.last_update_date
322 , b.last_update_login
323 , l.language_code
324 , b.source_lang
325 FROM csf_dc_queries_tl b
326 , fnd_languages l
327 WHERE l.installed_flag in ('I', 'B')
328 AND b.language = userenv('LANG')
329 AND NOT EXISTS
330 ( SELECT NULL
331 FROM csf_dc_queries_tl t
332 WHERE t.query_id = b.query_id
333 AND t.language = l.language_code );
334 END add_language;
335
336 end CSF_DC_QUERIES_PKG;