[Home] [Help]
PACKAGE BODY: APPS.OZF_VO_DISC_STRUCT_NAME_PKG
Source
1 PACKAGE BODY OZF_VO_DISC_STRUCT_NAME_PKG AS
2 /* $Header: ozftdsnb.pls 120.3 2005/11/15 13:50:55 gramanat noship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- OZF_VO_DISC_STRUCT_NAME_PKG
7 -- Purpose
8 --
9 -- History
10 --
11 -- NOTE
12 --
13 -- This Api is generated with Latest version of
14 -- Rosetta, where g_miss indicates NULL and
15 -- NULL indicates missing value. Rosetta Version 1.55
16 -- End of Comments
17 -- ===============================================================
18
19 PROCEDURE Add_Language
20 IS
21 BEGIN
22 delete from ozf_offr_disc_struct_name_tl T
23 where not exists
24 (select NULL
25 from ozf_offr_disc_struct_name_b b
26 where b.offr_disc_struct_name_id = t.offr_disc_struct_name_id
27 );
28
29 update ozf_offr_disc_struct_name_tl t set (
30 discount_table_name
31 , description
32 ) = (select
33 b.discount_table_name
34 , b.description
35 from ozf_offr_disc_struct_name_tl b
36 where b.offr_disc_struct_name_id = t.offr_disc_struct_name_id
37 and b.language = t.source_lang)
38 where (
39 t.offr_disc_struct_name_id,
40 t.language
41 ) in (select
42 subt.offr_disc_struct_name_id,
43 subt.language
44 from ozf_offr_disc_struct_name_tl subb, ozf_offr_disc_struct_name_tl subt
45 where subb.offr_disc_struct_name_id = subt.offr_disc_struct_name_id
46 and subb.language = subt.source_lang
47 and (subb.discount_table_name <> subt.discount_table_name
48 or subb.description <> subt.description
49 or (subb.description is null and subt.description is not null)
50 or (subb.description is not null and subt.description is null)
51 ));
52
53 insert into ozf_offr_disc_struct_name_tl (
54 offr_disc_struct_name_id,
55 creation_date,
56 created_by,
57 last_update_date,
58 last_updated_by,
59 last_update_login,
60 discount_table_name,
61 description,
62 language,
63 source_lang
64 ) select
65 b.offr_disc_struct_name_id,
66 b.creation_date,
67 b.created_by,
68 b.last_update_date,
69 b.last_updated_by,
70 b.last_update_login,
71 b.discount_table_name,
72 b.description,
73 l.language_code,
74 b.source_lang
75 from ozf_offr_disc_struct_name_tl b, fnd_languages l
76 where l.installed_flag in ('I', 'B')
77 and b.language = userenv('lang')
78 and not exists
79 (select null
80 from ozf_offr_disc_struct_name_tl t
81 where t.offr_disc_struct_name_id = b.offr_disc_struct_name_id
82 and t.language = l.language_code);
83 END ADD_LANGUAGE;
84
85
86
87
88 -- ========================================================
89 --
90 -- NAME
91 -- Insert_Row
92 --
93 -- PURPOSE
94 --
95 -- NOTES
96 --
97 -- HISTORY
98 --
99 -- ========================================================
100 PROCEDURE Insert_Row(
101 px_offr_disc_struct_name_id IN OUT NOCOPY NUMBER
102 , p_offer_discount_line_id IN NUMBER
103 , p_creation_date IN DATE
104 , p_created_by IN NUMBER
105 , p_last_updated_by IN NUMBER
106 , p_last_update_date IN DATE
107 , p_last_update_login IN NUMBER
108 , p_name IN VARCHAR2
109 , p_description IN VARCHAR2
110 , px_object_version_number IN OUT NOCOPY NUMBER
111 )
112 IS
113 x_rowid VARCHAR2(30);
114 BEGIN
115 px_object_version_number := nvl(px_object_version_number, 1);
116
117 INSERT INTO ozf_offr_disc_struct_name_b(
118 offr_disc_struct_name_id
119 , offer_discount_line_id
120 , object_version_number
121 , creation_date
122 , created_by
123 , last_updated_by
124 , last_update_date
125 , last_update_login
126 ) VALUES (
127 px_offr_disc_struct_name_id
128 , p_offer_discount_line_id
129 , DECODE( px_object_version_number, FND_API.G_MISS_NUM, 1, px_object_version_number)
130 , DECODE( p_creation_date, FND_API.G_MISS_DATE, SYSDATE, p_creation_date)
131 , DECODE( p_created_by, FND_API.G_MISS_NUM, FND_GLOBAL.USER_ID, p_created_by)
132 , DECODE( p_last_updated_by, FND_API.G_MISS_NUM, FND_GLOBAL.USER_ID, p_last_updated_by)
133 , DECODE( p_last_update_date, FND_API.G_MISS_DATE, SYSDATE, p_last_update_date)
134 , DECODE( p_last_update_login, FND_API.G_MISS_NUM, FND_GLOBAL.CONC_LOGIN_ID, p_last_update_login)
135 );
136
137 INSERT INTO ozf_offr_disc_struct_name_tl(
138 offr_disc_struct_name_id
139 , language
140 , last_update_date
141 , last_updated_by
142 , creation_date
143 , created_by
144 , last_update_login
145 , source_lang
146 , discount_table_name
147 , description
148 )
149 SELECT
150 px_offr_disc_struct_name_id
151 , l.language_code
152 , DECODE( p_last_update_date, to_date(NULL), SYSDATE, p_last_update_date)
153 , DECODE( p_last_updated_by, NULL, FND_GLOBAL.USER_ID, p_last_updated_by)
154 , DECODE( p_creation_date, to_date(NULL), SYSDATE, p_creation_date)
155 , DECODE( p_created_by, NULL, FND_GLOBAL.USER_ID, p_created_by)
156 , DECODE( p_last_update_login, NULL, FND_GLOBAL.CONC_LOGIN_ID, p_last_update_login)
157 , USERENV('LANG')
158 , p_name
159 , p_description
160 FROM fnd_languages l
161 WHERE l.installed_flag IN ('I','B')
162 AND NOT EXISTS(SELECT NULL FROM ozf_offr_disc_struct_name_tl t
163 WHERE t.offr_disc_struct_name_id = DECODE( px_offr_disc_struct_name_id, FND_API.G_MISS_NUM, NULL, px_offr_disc_struct_name_id)
164 AND t.language = l.language_code);
165
166 END Insert_Row;
167
168
169
170 -- ========================================================
171 --
172 -- NAME
173 -- Update_Row
174 --
175 -- PURPOSE
176 --
177 -- NOTES
178 --
179 -- HISTORY
180 --
181 -- ========================================================
182 PROCEDURE Update_Row(
183 p_offr_disc_struct_name_id IN NUMBER
184 , p_offer_discount_line_id IN NUMBER
185 , p_last_update_date IN DATE
186 , p_last_updated_by IN NUMBER
187 , p_last_update_login IN NUMBER
188 , p_name IN VARCHAR2
189 , p_description IN VARCHAR2
190 , px_object_version_number IN OUT NOCOPY NUMBER
191 )
192 IS
193 BEGIN
194 Update ozf_offr_disc_struct_name_b
195 SET
196 offr_disc_struct_name_id = DECODE( p_offr_disc_struct_name_id, null, offr_disc_struct_name_id, FND_API.G_MISS_NUM, null, p_offr_disc_struct_name_id)
197 , offer_discount_line_id = DECODE( p_offer_discount_line_id, null, offer_discount_line_id, FND_API.G_MISS_NUM, null, p_offer_discount_line_id)
198 , last_update_date = DECODE( p_last_update_date, to_date(null), last_update_date, FND_API.G_MISS_DATE, to_date(NULL), p_last_update_date)
199 , last_updated_by = DECODE( p_last_updated_by, null, last_updated_by, FND_API.G_MISS_NUM, null, p_last_updated_by)
200 , last_update_login = DECODE( p_last_update_login, null, last_update_login, FND_API.G_MISS_NUM, null, p_last_update_login)
201 , object_version_number = object_version_number + 1
202 WHERE offr_disc_struct_name_id = p_offr_disc_struct_name_id
203 AND object_version_number = px_object_version_number;
204
205 UPDATE ozf_offr_disc_struct_name_tl
206 set discount_table_name = DECODE( p_name, null, discount_table_name, FND_API.g_miss_char, null, p_name)
207 , description = DECODE( p_description, null, description, FND_API.g_miss_char, null, p_description)
208 , last_update_date = DECODE( p_last_update_date, to_date(NULL), last_update_date, FND_API.G_MISS_DATE, to_date(NULL), p_last_update_date)
209 , last_updated_by = DECODE( p_last_updated_by, null, last_updated_by, FND_API.G_MISS_NUM, null, p_last_updated_by)
210 , last_update_login = DECODE( p_last_update_login, null, last_update_login, FND_API.G_MISS_NUM, null, p_last_update_login)
211 , source_lang = USERENV('LANG')
212 WHERE offr_disc_struct_name_id = p_offr_disc_struct_name_id
213 AND USERENV('LANG') IN (language, source_lang);
214
215 IF (SQL%NOTFOUND) THEN
216 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
217 END IF;
218
219 px_object_version_number := nvl(px_object_version_number,0) + 1;
220
221 END Update_Row;
222
223
224
225 -- ========================================================
226 --
227 -- NAME
228 -- Delete_Row
229 --
230 -- PURPOSE
231 --
232 -- NOTES
233 --
234 -- HISTORY
235 --
236 -- ========================================================
237 PROCEDURE Delete_Row(
238 p_offr_disc_struct_name_id NUMBER,
239 p_object_version_number NUMBER
240 )
241 IS
242 BEGIN
243 DELETE FROM ozf_offr_disc_struct_name_tl
244 WHERE offr_disc_struct_name_id = p_offr_disc_struct_name_id;
245
246 If (SQL%NOTFOUND) then
247 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
248 End If;
249
250 DELETE FROM ozf_offr_disc_struct_name_b
251 WHERE offr_disc_struct_name_id = p_offr_disc_struct_name_id
252 AND object_version_number = p_object_version_number;
253
254 If (SQL%NOTFOUND) then
255 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
256 End If;
257 END Delete_Row ;
258
259
260
261 -- ========================================================
262 --
263 -- NAME
264 -- Lock_Row
265 --
266 -- PURPOSE
267 --
268 -- NOTES
269 --
270 -- HISTORY
271 --
272 -- ========================================================
273 PROCEDURE Lock_Row(
274 p_offr_disc_struct_name_id NUMBER,
275 p_object_version_number NUMBER)
276 IS
277 CURSOR C IS
278 SELECT *
279 FROM ozf_offr_disc_struct_name_b
280 WHERE offr_disc_struct_name_id = p_offr_disc_struct_name_id
281 AND object_version_number = p_object_version_number
282 FOR UPDATE OF offr_disc_struct_name_id NOWAIT;
283 Recinfo C%ROWTYPE;
284 BEGIN
285
286 OPEN c;
287 FETCH c INTO Recinfo;
288 IF (c%NOTFOUND) THEN
289 CLOSE c;
290 OZF_Utility_PVT.error_message ('OZF_API_RECORD_NOT_FOUND');
291 RAISE FND_API.g_exc_error;
292 END IF;
293 CLOSE c;
294 END Lock_Row;
295
296 -- ==============================================================================
297 -- Start of Comments
298 -- ==============================================================================
299 -- API Name
300 -- add_language
301 -- Type
302 -- Private
303 -- History
304 --
305 -- NOTE
306 --
307 -- End of Comments
308 -- ===============================================================
309
310
311 /*
312 PROCEDURE Add_Language
313 IS
314 BEGIN
315 delete from ozf_offr_disc_struct_name_tl T
316 where not exists
317 (select NULL
318 from ozf_offr_disc_struct_name_b b
319 where b.offr_disc_struct_name_id = t.offr_disc_struct_name_id
320 );
321
322 update ozf_offr_disc_struct_name_tl t set (
323 name
324 , description
325 ) = (select
326 b.name
327 , b.description
328 from ozf_offr_disc_struct_name_tl b
329 where b.offr_disc_struct_name_id = t.offr_disc_struct_name_id
330 and b.language = t.source_lang)
331 where (
332 t.offr_disc_struct_name_id,
333 t.language
334 ) in (select
335 subt.offer_adjustment_id,
336 subt.language
337 from ozf_offer_adjustments_tl subb, ozf_offer_adjustments_tl subt
338 where subb.offer_adjustment_id = subt.offer_adjustment_id
339 and subb.language = subt.source_lang
340 and (subb.offer_adjustment_name <> subt.offer_adjustment_name
341 or subb.description <> subt.description
342 or (subb.description is null and subt.description is not null)
343 or (subb.description is not null and subt.description is null)
344 ));
345
346 insert into ozf_offer_adjustments_tl (
347 offer_adjustment_id,
348 creation_date,
349 created_by,
350 last_update_date,
351 last_updated_by,
352 last_update_login,
353 offer_adjustment_name,
354 description,
355 language,
356 source_lang
357 ) select
358 b.offer_adjustment_id,
359 b.creation_date,
360 b.created_by,
361 b.last_update_date,
362 b.last_updated_by,
363 b.last_update_login,
364 b.offer_adjustment_name,
365 b.description,
366 l.language_code,
367 b.source_lang
368 from ozf_offer_adjustments_tl b, fnd_languages l
369 where l.installed_flag in ('I', 'B')
370 and b.language = userenv('lang')
371 and not exists
372 (select null
373 from ozf_offer_adjustments_tl t
374 where t.offer_adjustment_id = b.offer_adjustment_id
375 and t.language = l.language_code);
376 END ADD_LANGUAGE;
377 */
378
379
380 END OZF_VO_DISC_STRUCT_NAME_PKG;