[Home] [Help]
PACKAGE BODY: APPS.CSR_COSTS_PKG
Source
1 package body CSR_COSTS_PKG as
2 /*$Header: CSRSIPCB.pls 115.13 2002/11/22 13:01:39 jgrondel ship $
3 +========================================================================+
4 | Copyright (c) 1999 Oracle Corporation |
5 | Redwood Shores, California, USA |
6 | All rights reserved. |
10 CSR_COSTS_PKG
7 +========================================================================+
8 Name
9 ----
11
12 Purpose
13 -------
14 Insert, update, delete or lock tables belonging to view CSR_COSTS_VL:
15 - base table CSR_COSTS_ALL_B, and
16 - translation table CSR_COSTS_ALL_TL.
17 Restore data integrity to a corrupted base/translation pair.
18
19 History
20 -------
21 10-DEC-1999 E.Kerkhoven First creation
22 3-JAN-2000 M. van Teeseling Translate_row and load_row added
23 13-NOV-2002 J. van Grondelle Bug 2664009.
24 Added NOCOPY hint to procedure
25 out-parameters.
26 +========================================================================+
27 */
28 procedure insert_row
29 (
30 p_row_id IN OUT NOCOPY varchar2
31 , p_cost_id IN OUT NOCOPY number
32 , p_name IN varchar2
33 , p_value IN number
34 , p_description IN varchar2
35 , p_created_by IN OUT NOCOPY number
36 , p_creation_date IN OUT NOCOPY date
37 , p_last_updated_by IN OUT NOCOPY number
38 , p_last_update_date IN OUT NOCOPY date
39 , p_last_update_login IN OUT NOCOPY number
40 , p_attribute1 IN varchar2
41 , p_attribute2 IN varchar2
42 , p_attribute3 IN varchar2
43 , p_attribute4 IN varchar2
44 , p_attribute5 IN varchar2
45 , p_attribute6 IN varchar2
46 , p_attribute7 IN varchar2
47 , p_attribute8 IN varchar2
48 , p_attribute9 IN varchar2
49 , p_attribute10 IN varchar2
50 , p_attribute11 IN varchar2
51 , p_attribute12 IN varchar2
52 , p_attribute13 IN varchar2
53 , p_attribute14 IN varchar2
54 , p_attribute15 IN varchar2
55 , p_attribute_category IN varchar2
56 , p_org_id IN number
57 )
58 is
59 cursor c_cost ( p_cost_id number )
60 is
61 select row_id
62 from csr_costs_vl
63 where cost_id = p_cost_id;
64
65 begin
66
67 if p_cost_id is null then
68 select csr_costs_all_b_s1.nextval
69 into p_cost_id
70 from dual;
71 end if;
72
73 if p_created_by is null then
74 p_created_by := fnd_global.user_id;
75 end if;
76
77 if p_last_updated_by is null then
78 p_last_updated_by := fnd_global.user_id;
79 end if;
80
81 if p_last_update_login is null then
82 p_last_update_login := fnd_global.login_id;
83 end if;
84
85 if p_creation_date is null then
86 p_creation_date := sysdate;
87 end if;
88
89 if p_last_update_date is null then
90 p_last_update_date := sysdate;
91 end if;
92
93 insert into csr_costs_all_b
94 (
95 cost_id
96 , name
97 , value
98 , created_by
99 , creation_date
100 , last_updated_by
101 , last_update_date
102 , last_update_login
103 , attribute1
104 , attribute2
105 , attribute3
106 , attribute4
107 , attribute5
108 , attribute6
109 , attribute7
110 , attribute8
111 , attribute9
112 , attribute10
113 , attribute11
114 , attribute12
115 , attribute13
116 , attribute14
117 , attribute15
118 , attribute_category
119 , org_id
120 )
121 values
122 (
123 p_cost_id
124 , p_name
125 , p_value
126 , p_created_by
127 , p_creation_date
128 , p_last_updated_by
129 , p_last_update_date
130 , p_last_update_login
131 , p_attribute1
132 , p_attribute2
133 , p_attribute3
134 , p_attribute4
135 , p_attribute5
136 , p_attribute6
137 , p_attribute7
138 , p_attribute8
139 , p_attribute9
140 , p_attribute10
141 , p_attribute11
142 , p_attribute12
143 , p_attribute13
144 , p_attribute14
145 , p_attribute15
146 , p_attribute_category
147 , p_org_id
148 );
149
150 insert into csr_costs_all_tl
151 (
152 cost_id
153 , description
154 , created_by
155 , creation_date
156 , last_updated_by
157 , last_update_date
158 , last_update_login
159 , language
160 , source_lang
161 )
162 select p_cost_id
163 , p_description
164 , p_created_by
165 , p_creation_date
166 , p_last_updated_by
167 , p_last_update_date
168 , p_last_update_login
169 , l.language_code
170 , userenv('LANG')
171 from fnd_languages l
172 where l.installed_flag in ('I','B')
173 and not exists
174 ( select ''
175 from csr_costs_all_tl t
176 where t.cost_id = p_cost_id
177 and t.language = l.language_code );
178
179 open c_cost ( p_cost_id );
180 fetch c_cost into p_row_id;
181 if c_cost%notfound
182 then
183 close c_cost;
184 raise NO_DATA_FOUND;
185 end if;
186 close c_cost;
187 end insert_row;
188
192 , p_name IN varchar2
189 procedure lock_row
190 (
191 p_cost_id IN number
193 , p_value IN number
194 , p_description IN varchar2
195 , p_attribute1 IN varchar2
196 , p_attribute2 IN varchar2
197 , p_attribute3 IN varchar2
198 , p_attribute4 IN varchar2
199 , p_attribute5 IN varchar2
200 , p_attribute6 IN varchar2
201 , p_attribute7 IN varchar2
202 , p_attribute8 IN varchar2
203 , p_attribute9 IN varchar2
204 , p_attribute10 IN varchar2
205 , p_attribute11 IN varchar2
206 , p_attribute12 IN varchar2
207 , p_attribute13 IN varchar2
208 , p_attribute14 IN varchar2
209 , p_attribute15 IN varchar2
210 , p_attribute_category IN varchar2
211 )
212 is
213 cursor c_cost
214 is
215 select *
216 from csr_costs_vl
217 where cost_id = p_cost_id
218 for update nowait;
219
220 l_rec c_cost%rowtype;
221
222 begin
223 open c_cost;
224 fetch c_cost into l_rec;
225
226 if c_cost%notfound
227 then
228 close c_cost;
229 fnd_message.set_name('FND','FORM_RECORD_DELETED');
230 app_exception.raise_exception;
231 end if;
232 close c_cost;
233
234 if l_rec.name <> rtrim(p_name)
235 or l_rec.value <> p_value
236 or not csr_utilities.compare_values(rtrim(p_description),l_rec.description)
237 or not csr_utilities.compare_values(rtrim(p_attribute1),l_rec.attribute1)
238 or not csr_utilities.compare_values(rtrim(p_attribute2),l_rec.attribute2)
239 or not csr_utilities.compare_values(rtrim(p_attribute3),l_rec.attribute3)
240 or not csr_utilities.compare_values(rtrim(p_attribute4),l_rec.attribute4)
241 or not csr_utilities.compare_values(rtrim(p_attribute5),l_rec.attribute5)
242 or not csr_utilities.compare_values(rtrim(p_attribute6),l_rec.attribute6)
243 or not csr_utilities.compare_values(rtrim(p_attribute7),l_rec.attribute7)
244 or not csr_utilities.compare_values(rtrim(p_attribute8),l_rec.attribute8)
245 or not csr_utilities.compare_values(rtrim(p_attribute9),l_rec.attribute9)
246 or not csr_utilities.compare_values(rtrim(p_attribute10),l_rec.attribute10)
247 or not csr_utilities.compare_values(rtrim(p_attribute11),l_rec.attribute11)
248 or not csr_utilities.compare_values(rtrim(p_attribute12),l_rec.attribute12)
249 or not csr_utilities.compare_values(rtrim(p_attribute13),l_rec.attribute13)
250 or not csr_utilities.compare_values(rtrim(p_attribute14),l_rec.attribute14)
251 or not csr_utilities.compare_values(rtrim(p_attribute15),l_rec.attribute15)
252 or not csr_utilities.compare_values(rtrim(p_attribute_category),
253 l_rec.attribute_category)
254 then
255 fnd_message.set_name('FND','FORM_RECORD_CHANGED');
256 app_exception.raise_exception;
257 end if;
258 end lock_row;
259
260 procedure update_row
261 (
262 p_cost_id IN number
263 , p_name IN varchar2
264 , p_value IN number
265 , p_description IN varchar2
266 , p_last_updated_by IN OUT NOCOPY number
267 , p_last_update_date IN OUT NOCOPY date
268 , p_last_update_login IN OUT NOCOPY number
269 , p_attribute1 IN varchar2
270 , p_attribute2 IN varchar2
271 , p_attribute3 IN varchar2
272 , p_attribute4 IN varchar2
273 , p_attribute5 IN varchar2
274 , p_attribute6 IN varchar2
275 , p_attribute7 IN varchar2
276 , p_attribute8 IN varchar2
277 , p_attribute9 IN varchar2
278 , p_attribute10 IN varchar2
279 , p_attribute11 IN varchar2
280 , p_attribute12 IN varchar2
281 , p_attribute13 IN varchar2
282 , p_attribute14 IN varchar2
283 , p_attribute15 IN varchar2
284 , p_attribute_category IN varchar2
285 )
286 is
287 begin
288
289 if p_last_updated_by is null then
290 p_last_updated_by := fnd_global.user_id;
291 end if;
292
293 if p_last_update_login is null then
294 p_last_update_login := fnd_global.login_id;
295 end if;
296
297 if p_last_update_date is null then
298 p_last_update_date := sysdate;
299 end if;
300
301 update csr_costs_all_b
302 set name = p_name
303 , value = p_value
304 , last_update_date = p_last_update_date
305 , last_updated_by = p_last_updated_by
306 , last_update_login = p_last_update_login
307 , attribute1 = p_attribute1
308 , attribute2 = p_attribute2
309 , attribute3 = p_attribute3
310 , attribute4 = p_attribute4
311 , attribute5 = p_attribute5
312 , attribute6 = p_attribute6
313 , attribute7 = p_attribute7
314 , attribute8 = p_attribute8
315 , attribute9 = p_attribute9
316 , attribute10 = p_attribute10
317 , attribute11 = p_attribute11
318 , attribute12 = p_attribute12
319 , attribute13 = p_attribute13
320 , attribute14 = p_attribute14
321 , attribute15 = p_attribute15
322 , attribute_category = p_attribute_category
323 where cost_id = p_cost_id;
324
325 if sql%notfound
326 then
327 raise NO_DATA_FOUND;
328 end if;
329
330 update csr_costs_all_tl
331 set description = p_description
332 , last_update_date = p_last_update_date
336 where cost_id = p_cost_id
333 , last_updated_by = p_last_updated_by
334 , last_update_login = p_last_update_login
335 , source_lang = userenv('lang')
337 and userenv('lang') in (language, source_lang);
338
339 if sql%notfound
340 then
341 raise NO_DATA_FOUND;
342 end if;
343 end update_row;
344
345 procedure delete_row
346 (
347 p_cost_id IN number
348 )
349 is
350 begin
351 delete from csr_costs_all_tl
352 where cost_id = p_cost_id;
353
354 if sql%notfound
355 then
356 raise NO_DATA_FOUND;
357 end if;
358
359 delete from csr_costs_all_b
360 where cost_id = p_cost_id;
361
362 if sql%notfound
363 then
364 raise NO_DATA_FOUND;
365 end if;
366 end delete_row;
367
368 procedure add_language
369 is
370 begin
371 delete from csr_costs_all_tl t
372 where not exists
373 ( select ''
374 from csr_costs_all_b b
375 where b.cost_id = t.cost_id );
376
377 update csr_costs_all_tl t
378 set description =
379 ( select b.description
380 from csr_costs_all_tl b
381 where b.cost_id = t.cost_id
382 and b.language = t.source_lang )
383 where ( t.cost_id, t.language ) in
384 ( select subt.cost_id
385 , subt.language
386 from csr_costs_all_tl subb
387 , csr_costs_all_tl subt
388 where subb.cost_id = subt.cost_id
389 and subb.language = subt.source_lang
390 and ( subb.description <> subt.description
391 or ( subb.description is null
392 and subt.description is not null )
393 or ( subb.description is not null
394 and subt.description is null ) ) );
395
396 insert into csr_costs_all_tl
397 ( cost_id
398 , description
399 , created_by
400 , creation_date
401 , last_updated_by
402 , last_update_date
403 , last_update_login
404 , language
405 , source_lang
406 )
407 select b.cost_id
408 , b.description
409 , b.created_by
410 , b.creation_date
411 , b.last_updated_by
412 , b.last_update_date
413 , b.last_update_login
414 , l.language_code
415 , b.source_lang
416 from csr_costs_all_tl b
417 , fnd_languages l
418 where l.installed_flag in ('I', 'B')
419 and b.language = userenv('LANG')
420 and not exists
421 ( select null
422 from csr_costs_all_tl t
423 where t.cost_id = b.cost_id
424 and t.language = l.language_code );
425 end add_language;
426
427 procedure translate_row
428 (
429 p_cost_id IN varchar2
430 , p_owner IN varchar2
431 , p_description IN varchar2
432 )
433 is
434 begin
435 update CSR_COSTS_ALL_TL
436 set description = p_description,
437 last_update_date = sysdate,
438 last_updated_by = decode(p_owner, 'SEED', 1, 0),
439 last_update_login = 0,
440 source_lang = userenv('LANG')
441 where cost_id = to_number(p_cost_id)
442 and userenv('LANG') in (language, source_lang);
443 end translate_row;
444
445 procedure load_row
446 (
447 p_cost_id IN varchar2
448 , p_name IN varchar2
449 , p_value IN varchar2
450 , p_description IN varchar2
451 , p_owner IN varchar2
452 , p_attribute1 IN varchar2
453 , p_attribute2 IN varchar2
454 , p_attribute3 IN varchar2
455 , p_attribute4 IN varchar2
456 , p_attribute5 IN varchar2
457 , p_attribute6 IN varchar2
458 , p_attribute7 IN varchar2
459 , p_attribute8 IN varchar2
460 , p_attribute9 IN varchar2
461 , p_attribute10 IN varchar2
462 , p_attribute11 IN varchar2
463 , p_attribute12 IN varchar2
464 , p_attribute13 IN varchar2
465 , p_attribute14 IN varchar2
466 , p_attribute15 IN varchar2
467 , p_attribute_category IN varchar2
468 , p_org_id IN varchar2
469 )
470 is
471 l_cost_id number := to_number(p_cost_id);
472 l_update_date date := sysdate;
473 l_row_id varchar2(64);
474 l_user_id number := 0;
475 begin
476 if (p_owner = 'SEED')
477 then
478 l_user_id := 1;
479 end if;
480
481 update_row
482 (
483 p_cost_id => l_cost_id
484 , p_name => p_name
485 , p_value => to_number(p_value)
486 , p_description => p_description
487 , p_last_updated_by => l_user_id
488 , p_last_update_date => l_update_date
489 , p_last_update_login => l_user_id
490 , p_attribute1 => p_attribute1
491 , p_attribute2 => p_attribute2
492 , p_attribute3 => p_attribute3
493 , p_attribute4 => p_attribute4
494 , p_attribute5 => p_attribute5
495 , p_attribute6 => p_attribute6
496 , p_attribute7 => p_attribute7
497 , p_attribute8 => p_attribute8
498 , p_attribute9 => p_attribute9
499 , p_attribute10 => p_attribute10
500 , p_attribute11 => p_attribute11
501 , p_attribute12 => p_attribute12
502 , p_attribute13 => p_attribute13
503 , p_attribute14 => p_attribute14
504 , p_attribute15 => p_attribute15
505 , p_attribute_category => p_attribute_category
506 );
507 exception
508 when NO_DATA_FOUND then
509 insert_row
510 (
511 p_row_id => l_row_id
512 , p_cost_id => l_cost_id
513 , p_name => p_name
514 , p_value => to_number(p_value)
515 , p_description => p_description
516 , p_created_by => l_user_id
517 , p_creation_date => l_update_date
518 , p_last_updated_by => l_user_id
519 , p_last_update_date => l_update_date
520 , p_last_update_login => l_user_id
521 , p_attribute1 => p_attribute1
522 , p_attribute2 => p_attribute2
523 , p_attribute3 => p_attribute3
524 , p_attribute4 => p_attribute4
525 , p_attribute5 => p_attribute5
526 , p_attribute6 => p_attribute6
527 , p_attribute7 => p_attribute7
528 , p_attribute8 => p_attribute8
529 , p_attribute9 => p_attribute9
530 , p_attribute10 => p_attribute10
531 , p_attribute11 => p_attribute11
532 , p_attribute12 => p_attribute12
533 , p_attribute13 => p_attribute13
534 , p_attribute14 => p_attribute14
535 , p_attribute15 => p_attribute15
536 , p_attribute_category => p_attribute_category
537 , p_org_id => to_number(p_org_id)
538 );
539 end load_row;
540
541 end CSR_COSTS_PKG;