[Home] [Help]
PACKAGE BODY: APPS.OZF_OFFER_ADJUSTMENT_PKG
Source
1 PACKAGE BODY OZF_Offer_Adjustment_PKG as
2 /* $Header: ozftoadb.pls 120.0 2005/06/01 02:22:16 appldev noship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- OZF_Offer_Adjustment_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
20 G_PKG_NAME CONSTANT VARCHAR2(30):= 'OZF_Offer_Adjustment_PKG';
21 G_FILE_NAME CONSTANT VARCHAR2(12) := 'ozftadjb.pls';
22
23
24
25
26 -- ========================================================
27 --
28 -- NAME
29 -- Insert_Row
30 --
31 -- PURPOSE
32 --
33 -- NOTES
34 --
35 -- HISTORY
36 --
37 -- ========================================================
38 PROCEDURE Insert_Row(
39 px_offer_adjustment_id IN OUT NOCOPY NUMBER,
40 p_effective_date DATE,
41 p_approved_date DATE,
42 p_settlement_code VARCHAR2,
43 p_status_code VARCHAR2,
44 p_list_header_id NUMBER,
45 p_version NUMBER,
46 p_budget_adjusted_flag VARCHAR2,
47 p_last_update_date DATE,
48 p_last_updated_by NUMBER,
49 p_creation_date DATE,
50 p_created_by NUMBER,
51 p_last_update_login NUMBER,
52 px_object_version_number IN OUT NOCOPY NUMBER,
53 p_attribute1 VARCHAR2,
54 p_attribute2 VARCHAR2,
55 p_attribute3 VARCHAR2,
56 p_attribute4 VARCHAR2,
57 p_attribute5 VARCHAR2,
58 p_attribute6 VARCHAR2,
59 p_attribute7 VARCHAR2,
60 p_attribute8 VARCHAR2,
61 p_attribute9 VARCHAR2,
62 p_attribute10 VARCHAR2,
63 p_attribute11 VARCHAR2,
64 p_attribute12 VARCHAR2,
65 p_attribute13 VARCHAR2,
66 p_attribute14 VARCHAR2,
67 p_attribute15 VARCHAR2,
68 p_offer_adjustment_name VARCHAR2,
69 p_description VARCHAR2
70 )
71
72 IS
73 x_rowid VARCHAR2(30);
74
75
76 BEGIN
77
78
79 px_object_version_number := nvl(px_object_version_number, 1);
80
81
82 INSERT INTO ozf_offer_adjustments_b(
83 offer_adjustment_id,
84 effective_date,
85 approved_date,
86 settlement_code,
87 status_code,
88 list_header_id,
89 version,
90 budget_adjusted_flag,
91 last_update_date,
92 last_updated_by,
93 creation_date,
94 created_by,
95 last_update_login,
96 object_version_number,
97 attribute1,
98 attribute2,
99 attribute3,
100 attribute4,
101 attribute5,
102 attribute6,
103 attribute7,
104 attribute8,
105 attribute9,
106 attribute10,
107 attribute11,
108 attribute12,
109 attribute13,
110 attribute14,
111 attribute15
112 ) VALUES (
113 px_offer_adjustment_id,
114 p_effective_date,
115 p_approved_date,
116 p_settlement_code,
117 p_status_code,
118 p_list_header_id,
119 p_version,
120 p_budget_adjusted_flag,
121 DECODE( p_last_update_date, FND_API.G_MISS_DATE, SYSDATE, p_last_update_date),
122 DECODE( p_last_updated_by, FND_API.G_MISS_NUM, FND_GLOBAL.USER_ID, p_last_updated_by),
123 DECODE( p_creation_date, FND_API.G_MISS_DATE, SYSDATE, p_creation_date),
124 DECODE( p_created_by, FND_API.G_MISS_NUM, FND_GLOBAL.USER_ID, p_created_by),
125 DECODE( p_last_update_login, FND_API.G_MISS_NUM, FND_GLOBAL.CONC_LOGIN_ID, p_last_update_login),
126 DECODE( px_object_version_number, FND_API.G_MISS_NUM, 1, px_object_version_number),
127 p_attribute1,
128 p_attribute2,
129 p_attribute3,
130 p_attribute4,
131 p_attribute5,
132 p_attribute6,
133 p_attribute7,
134 p_attribute8,
135 p_attribute9,
136 p_attribute10,
137 p_attribute11,
138 p_attribute12,
139 p_attribute13,
140 p_attribute14,
141 p_attribute15);
142
143 INSERT INTO ozf_offer_adjustments_tl(
144 offer_adjustment_id ,
145 language ,
146 last_update_date ,
147 last_updated_by ,
148 creation_date ,
149 created_by ,
150 last_update_login ,
151 source_lang ,
152 offer_adjustment_name ,
153 description
154 )
155 SELECT
156 px_offer_adjustment_id,
157 l.language_code,
158 DECODE( p_last_update_date, to_date(NULL), SYSDATE, p_last_update_date),
159 DECODE( p_last_updated_by, NULL, FND_GLOBAL.USER_ID, p_last_updated_by),
160 DECODE( p_creation_date, to_date(NULL), SYSDATE, p_creation_date),
161 DECODE( p_created_by, NULL, FND_GLOBAL.USER_ID, p_created_by),
162 DECODE( p_last_update_login, NULL, FND_GLOBAL.CONC_LOGIN_ID, p_last_update_login),
163 USERENV('LANG'),
164 p_offer_adjustment_name,
165 p_description
166 FROM fnd_languages l
167 WHERE l.installed_flag IN ('I','B')
168 AND NOT EXISTS(SELECT NULL FROM ozf_offer_adjustments_tl t
169 WHERE t.offer_adjustment_id = DECODE( px_offer_adjustment_id, FND_API.G_MISS_NUM, NULL, px_offer_adjustment_id)
170 AND t.language = l.language_code);
171 END Insert_Row;
172
173
174
175
176 -- ========================================================
177 --
178 -- NAME
179 -- Update_Row
180 --
181 -- PURPOSE
182 --
183 -- NOTES
184 --
185 -- HISTORY
186 --
187 -- ========================================================
188 PROCEDURE Update_Row(
189 p_offer_adjustment_id NUMBER,
190 p_effective_date DATE,
191 p_approved_date DATE,
192 p_settlement_code VARCHAR2,
193 p_status_code VARCHAR2,
194 p_list_header_id NUMBER,
195 p_version NUMBER,
196 p_budget_adjusted_flag VARCHAR2,
197 p_last_update_date DATE,
198 p_last_updated_by NUMBER,
199 p_last_update_login NUMBER,
200 px_object_version_number IN OUT NOCOPY NUMBER,
201 p_attribute1 VARCHAR2,
202 p_attribute2 VARCHAR2,
203 p_attribute3 VARCHAR2,
204 p_attribute4 VARCHAR2,
205 p_attribute5 VARCHAR2,
206 p_attribute6 VARCHAR2,
207 p_attribute7 VARCHAR2,
208 p_attribute8 VARCHAR2,
209 p_attribute9 VARCHAR2,
210 p_attribute10 VARCHAR2,
211 p_attribute11 VARCHAR2,
212 p_attribute12 VARCHAR2,
213 p_attribute13 VARCHAR2,
214 p_attribute14 VARCHAR2,
215 p_attribute15 VARCHAR2,
216 p_offer_adjustment_name VARCHAR2,
217 p_description VARCHAR2
218 )
219
220 IS
221 BEGIN
222 Update ozf_offer_adjustments_b
223 SET
224 offer_adjustment_id = DECODE( p_offer_adjustment_id, null, offer_adjustment_id, FND_API.G_MISS_NUM, null, p_offer_adjustment_id),
225 effective_date = DECODE( p_effective_date, to_date(NULL), effective_date, FND_API.G_MISS_DATE, to_date(NULL), p_effective_date),
226 approved_date = DECODE( p_approved_date, to_date(NULL), approved_date, FND_API.G_MISS_DATE, to_date(NULL), p_approved_date),
227 settlement_code = DECODE( p_settlement_code, null, settlement_code, FND_API.g_miss_char, null, p_settlement_code),
228 status_code = DECODE( p_status_code, null, status_code, FND_API.g_miss_char, null, p_status_code),
229 list_header_id = DECODE( p_list_header_id, null, list_header_id, FND_API.G_MISS_NUM, null, p_list_header_id),
230 version = DECODE( p_version, null, version, FND_API.G_MISS_NUM, null, p_version),
231 budget_adjusted_flag = DECODE( p_budget_adjusted_flag, null, budget_adjusted_flag, FND_API.g_miss_char, null, p_budget_adjusted_flag),
232 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),
233 last_updated_by = DECODE( p_last_updated_by, null, last_updated_by, FND_API.G_MISS_NUM, null, p_last_updated_by),
234 last_update_login = DECODE( p_last_update_login, null, last_update_login, FND_API.G_MISS_NUM, null, p_last_update_login),
235 object_version_number = object_version_number + 1 ,
236 attribute1 = DECODE( p_attribute1, null, attribute1, FND_API.g_miss_char, null, p_attribute1),
237 attribute2 = DECODE( p_attribute2, null, attribute2, FND_API.g_miss_char, null, p_attribute2),
238 attribute3 = DECODE( p_attribute3, null, attribute3, FND_API.g_miss_char, null, p_attribute3),
239 attribute4 = DECODE( p_attribute4, null, attribute4, FND_API.g_miss_char, null, p_attribute4),
240 attribute5 = DECODE( p_attribute5, null, attribute5, FND_API.g_miss_char, null, p_attribute5),
241 attribute6 = DECODE( p_attribute6, null, attribute6, FND_API.g_miss_char, null, p_attribute6),
242 attribute7 = DECODE( p_attribute7, null, attribute7, FND_API.g_miss_char, null, p_attribute7),
243 attribute8 = DECODE( p_attribute8, null, attribute8, FND_API.g_miss_char, null, p_attribute8),
244 attribute9 = DECODE( p_attribute9, null, attribute9, FND_API.g_miss_char, null, p_attribute9),
245 attribute10 = DECODE( p_attribute10, null, attribute10, FND_API.g_miss_char, null, p_attribute10),
246 attribute11 = DECODE( p_attribute11, null, attribute11, FND_API.g_miss_char, null, p_attribute11),
247 attribute12 = DECODE( p_attribute12, null, attribute12, FND_API.g_miss_char, null, p_attribute12),
248 attribute13 = DECODE( p_attribute13, null, attribute13, FND_API.g_miss_char, null, p_attribute13),
249 attribute14 = DECODE( p_attribute14, null, attribute14, FND_API.g_miss_char, null, p_attribute14),
250 attribute15 = DECODE( p_attribute15, null, attribute15, FND_API.g_miss_char, null, p_attribute15)
251 WHERE offer_adjustment_id = p_offer_adjustment_id
252 AND object_version_number = px_object_version_number;
253
254 UPDATE ozf_offer_adjustments_tl
255 set offer_adjustment_name = DECODE( p_offer_adjustment_name, null, offer_adjustment_name, FND_API.g_miss_char, null, p_offer_adjustment_name),
256 description = DECODE( p_description, null, description, FND_API.g_miss_char, null, p_description),
257 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),
258 last_updated_by = DECODE( p_last_updated_by, null, last_updated_by, FND_API.G_MISS_NUM, null, p_last_updated_by),
259 last_update_login = DECODE( p_last_update_login, null, last_update_login, FND_API.G_MISS_NUM, null, p_last_update_login),
260 source_lang = USERENV('LANG')
261 WHERE offer_adjustment_id = p_offer_adjustment_id
262 AND USERENV('LANG') IN (language, source_lang);
263
264 IF (SQL%NOTFOUND) THEN
265 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
266 END IF;
267
268 px_object_version_number := nvl(px_object_version_number,0) + 1;
269
270 END Update_Row;
271
272
273
274
275 -- ========================================================
276 --
277 -- NAME
278 -- Delete_Row
279 --
280 -- PURPOSE
281 --
282 -- NOTES
283 --
284 -- HISTORY
285 --
286 -- ========================================================
287 PROCEDURE Delete_Row(
288 p_offer_adjustment_id NUMBER,
289 p_object_version_number NUMBER)
290 IS
291 BEGIN
292
293 DELETE FROM ozf_offer_adjustments_tl
294 WHERE offer_adjustment_id = p_offer_adjustment_id;
295 If (SQL%NOTFOUND) then
296 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
297 End If;
298
299 DELETE FROM ozf_offer_adjustments_b
300 WHERE offer_adjustment_id = p_offer_adjustment_id
301 AND object_version_number = p_object_version_number;
302 If (SQL%NOTFOUND) then
303 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
304 End If;
305 END Delete_Row ;
306
307
308
309
310
311 -- ========================================================
312 --
313 -- NAME
314 -- Lock_Row
315 --
316 -- PURPOSE
317 --
318 -- NOTES
319 --
320 -- HISTORY
321 --
322 -- ========================================================
323 PROCEDURE Lock_Row(
324 p_offer_adjustment_id NUMBER,
325 p_object_version_number NUMBER)
326 IS
327 CURSOR C IS
328 SELECT *
329 FROM ozf_offer_adjustments_b
330 WHERE offer_adjustment_id = p_offer_adjustment_id
331 AND object_version_number = p_object_version_number
332 FOR UPDATE OF offer_adjustment_id NOWAIT;
333 Recinfo C%ROWTYPE;
334 BEGIN
335
336 OPEN c;
337 FETCH c INTO Recinfo;
338 IF (c%NOTFOUND) THEN
339 CLOSE c;
340 OZF_Utility_PVT.error_message ('OZF_API_RECORD_NOT_FOUND');
341 RAISE FND_API.g_exc_error;
342 END IF;
343 CLOSE c;
344 END Lock_Row;
345
346 -- ==============================================================================
347 -- Start of Comments
348 -- ==============================================================================
349 -- API Name
350 -- add_language
351 -- Type
352 -- Private
353 -- History
354 --
355 -- NOTE
356 --
357 -- End of Comments
358 -- ===============================================================
359
360
361
362 PROCEDURE Add_Language
363 IS
364 BEGIN
365 delete from ozf_offer_adjustments_tl T
366 where not exists
367 (select NULL
368 from ozf_offer_adjustments_b b
369 where b.offer_adjustment_id = t.offer_adjustment_id
370 );
371
372 update ozf_offer_adjustments_tl t set (
373 offer_adjustment_name
374 , description
375 ) = (select
376 b.offer_adjustment_name
377 , b.description
378 from ozf_offer_adjustments_tl b
379 where b.offer_adjustment_id = t.offer_adjustment_id
380 and b.language = t.source_lang)
381 where (
382 t.offer_adjustment_id,
383 t.language
384 ) in (select
385 subt.offer_adjustment_id,
386 subt.language
387 from ozf_offer_adjustments_tl subb, ozf_offer_adjustments_tl subt
388 where subb.offer_adjustment_id = subt.offer_adjustment_id
389 and subb.language = subt.source_lang
390 and (subb.offer_adjustment_name <> subt.offer_adjustment_name
391 or subb.description <> subt.description
392 or (subb.description is null and subt.description is not null)
393 or (subb.description is not null and subt.description is null)
394 ));
395
396 insert into ozf_offer_adjustments_tl (
397 offer_adjustment_id,
398 creation_date,
399 created_by,
400 last_update_date,
401 last_updated_by,
402 last_update_login,
403 offer_adjustment_name,
404 description,
405 language,
406 source_lang
407 ) select
408 b.offer_adjustment_id,
409 b.creation_date,
410 b.created_by,
411 b.last_update_date,
412 b.last_updated_by,
413 b.last_update_login,
414 b.offer_adjustment_name,
415 b.description,
416 l.language_code,
417 b.source_lang
418 from ozf_offer_adjustments_tl b, fnd_languages l
419 where l.installed_flag in ('I', 'B')
420 and b.language = userenv('lang')
421 and not exists
422 (select null
423 from ozf_offer_adjustments_tl t
424 where t.offer_adjustment_id = b.offer_adjustment_id
425 and t.language = l.language_code);
426 END ADD_LANGUAGE;
427
428
429
430
431 END OZF_Offer_Adjustment_PKG;