[Home] [Help]
PACKAGE BODY: APPS.OZF_OFFER_PERFORMANCES_PKG
Source
1 PACKAGE BODY OZF_OFFER_PERFORMANCES_PKG as
2 /* $Header: ozftperb.pls 120.0 2005/06/01 02:50:06 appldev noship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- OZF_OFFER_PERFORMANCES_PKG
7 -- Purpose
8 --
9 -- History
10 --
11 -- NOTE
12 --
13 -- End of Comments
14 -- ===============================================================
15
16
17 G_PKG_NAME CONSTANT VARCHAR2(30):= 'OZF_OFFER_PERFORMANCES_PKG';
18 G_FILE_NAME CONSTANT VARCHAR2(12) := 'ozftperb.pls';
19
20
21 ----------------------------------------------------------
22 ---- MEDIA ----
23 ----------------------------------------------------------
24
25 -- ========================================================
26 --
27 -- NAME
28 -- createInsertBody
29 --
30 -- PURPOSE
31 --
32 -- NOTES
33 --
34 -- HISTORY
35 --
36 -- ========================================================
37 PROCEDURE Insert_Row(
38 px_offer_performance_id IN OUT NOCOPY NUMBER,
39 p_list_header_id NUMBER,
40 p_last_update_date DATE,
41 p_last_updated_by NUMBER,
42 p_creation_date DATE,
43 p_created_by NUMBER,
44 p_last_update_login NUMBER,
45 px_object_version_number IN OUT NOCOPY NUMBER,
46 p_product_attribute_context VARCHAR2,
47 p_product_attribute VARCHAR2,
48 p_product_attr_value VARCHAR2,
49 p_channel_id NUMBER,
50 p_start_date DATE,
51 p_end_date DATE,
52 p_estimated_value NUMBER,
53 p_required_flag VARCHAR2,
54 p_attribute_category VARCHAR2,
55 p_attribute1 VARCHAR2,
56 p_attribute2 VARCHAR2,
57 p_attribute3 VARCHAR2,
58 p_attribute4 VARCHAR2,
59 p_attribute5 VARCHAR2,
60 p_attribute6 VARCHAR2,
61 p_attribute7 VARCHAR2,
62 p_attribute8 VARCHAR2,
63 p_attribute9 VARCHAR2,
64 p_attribute10 VARCHAR2,
65 p_attribute11 VARCHAR2,
66 p_attribute12 VARCHAR2,
67 p_attribute13 VARCHAR2,
68 p_attribute14 VARCHAR2,
69 p_attribute15 VARCHAR2,
70 p_security_group_id NUMBER,
71 p_requirement_type VARCHAR2,
72 p_uom_code VARCHAR2,
73 p_description VARCHAR2)
74
75 IS
76 x_rowid VARCHAR2(30);
77
78
79 BEGIN
80
81
82 px_object_version_number := 1;
83
84
85 INSERT INTO OZF_OFFER_PERFORMANCES(
86 offer_performance_id,
87 list_header_id,
88 last_update_date,
89 last_updated_by,
90 creation_date,
91 created_by,
92 last_update_login,
93 object_version_number,
94 product_attribute_context,
95 product_attribute,
96 product_attr_value,
97 channel_id,
98 start_date,
99 end_date,
100 estimated_value,
101 required_flag,
102 attribute_category,
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 security_group_id,
119 requirement_type,
120 uom_code,
121 description)
122 VALUES (
123 DECODE( px_offer_performance_id, FND_API.g_miss_num, NULL, px_offer_performance_id),
124 DECODE( p_list_header_id, FND_API.g_miss_num, NULL, p_list_header_id),
125 DECODE( p_last_update_date, FND_API.g_miss_date, to_date(NULL), p_last_update_date),
126 DECODE( p_last_updated_by, FND_API.g_miss_num, NULL, p_last_updated_by),
127 DECODE( p_creation_date, FND_API.g_miss_date, to_date(NULL), p_creation_date),
128 DECODE( p_created_by, FND_API.g_miss_num, NULL, p_created_by),
129 DECODE( p_last_update_login, FND_API.g_miss_num, NULL, p_last_update_login),
130 DECODE( px_object_version_number, FND_API.g_miss_num, NULL, px_object_version_number),
131 DECODE( p_product_attribute_context, FND_API.g_miss_char, NULL, p_product_attribute_context),
132 DECODE( p_product_attribute, FND_API.g_miss_char, NULL, p_product_attribute),
133 DECODE( p_product_attr_value, FND_API.g_miss_char, NULL, p_product_attr_value),
134 DECODE( p_channel_id, FND_API.g_miss_num, NULL, p_channel_id),
135 DECODE( p_start_date, FND_API.g_miss_date, to_date(NULL), p_start_date),
136 DECODE( p_end_date, FND_API.g_miss_date, to_date(NULL), p_end_date),
137 DECODE( p_estimated_value, FND_API.g_miss_num, NULL, p_estimated_value),
138 DECODE( p_required_flag, FND_API.g_miss_char, NULL, p_required_flag),
139 DECODE( p_attribute_category, FND_API.g_miss_char, NULL, p_attribute_category),
140 DECODE( p_attribute1, FND_API.g_miss_char, NULL, p_attribute1),
141 DECODE( p_attribute2, FND_API.g_miss_char, NULL, p_attribute2),
142 DECODE( p_attribute3, FND_API.g_miss_char, NULL, p_attribute3),
143 DECODE( p_attribute4, FND_API.g_miss_char, NULL, p_attribute4),
144 DECODE( p_attribute5, FND_API.g_miss_char, NULL, p_attribute5),
145 DECODE( p_attribute6, FND_API.g_miss_char, NULL, p_attribute6),
146 DECODE( p_attribute7, FND_API.g_miss_char, NULL, p_attribute7),
147 DECODE( p_attribute8, FND_API.g_miss_char, NULL, p_attribute8),
148 DECODE( p_attribute9, FND_API.g_miss_char, NULL, p_attribute9),
149 DECODE( p_attribute10, FND_API.g_miss_char, NULL, p_attribute10),
150 DECODE( p_attribute11, FND_API.g_miss_char, NULL, p_attribute11),
151 DECODE( p_attribute12, FND_API.g_miss_char, NULL, p_attribute12),
152 DECODE( p_attribute13, FND_API.g_miss_char, NULL, p_attribute13),
153 DECODE( p_attribute14, FND_API.g_miss_char, NULL, p_attribute14),
154 DECODE( p_attribute15, FND_API.g_miss_char, NULL, p_attribute15),
155 DECODE( p_security_group_id, FND_API.g_miss_num, NULL, p_security_group_id),
156 DECODE( p_requirement_type, FND_API.g_miss_char, NULL, p_requirement_type),
157 DECODE( p_uom_code, FND_API.g_miss_char, NULL, p_uom_code),
158 DECODE( p_description, FND_API.g_miss_char, NULL, p_description));
159 END Insert_Row;
160
161
162 ----------------------------------------------------------
163 ---- MEDIA ----
164 ----------------------------------------------------------
165
166 -- ========================================================
167 --
168 -- NAME
169 -- createUpdateBody
170 --
171 -- PURPOSE
172 --
173 -- NOTES
174 --
175 -- HISTORY
176 --
177 -- ========================================================
178 PROCEDURE Update_Row(
179 p_offer_performance_id NUMBER,
180 p_list_header_id NUMBER,
181 p_last_update_date DATE,
182 p_last_updated_by NUMBER,
183 p_creation_date DATE,
184 p_created_by NUMBER,
185 p_last_update_login NUMBER,
186 p_object_version_number NUMBER,
187 p_product_attribute_context VARCHAR2,
188 p_product_attribute VARCHAR2,
189 p_product_attr_value VARCHAR2,
190 p_channel_id NUMBER,
191 p_start_date DATE,
192 p_end_date DATE,
193 p_estimated_value NUMBER,
194 p_required_flag VARCHAR2,
195 p_attribute_category VARCHAR2,
196 p_attribute1 VARCHAR2,
197 p_attribute2 VARCHAR2,
198 p_attribute3 VARCHAR2,
199 p_attribute4 VARCHAR2,
200 p_attribute5 VARCHAR2,
201 p_attribute6 VARCHAR2,
202 p_attribute7 VARCHAR2,
203 p_attribute8 VARCHAR2,
204 p_attribute9 VARCHAR2,
205 p_attribute10 VARCHAR2,
206 p_attribute11 VARCHAR2,
207 p_attribute12 VARCHAR2,
208 p_attribute13 VARCHAR2,
209 p_attribute14 VARCHAR2,
210 p_attribute15 VARCHAR2,
211 p_security_group_id NUMBER,
212 p_requirement_type VARCHAR2,
213 p_uom_code VARCHAR2,
214 p_description VARCHAR2)
215
216 IS
217 BEGIN
218 Update OZF_OFFER_PERFORMANCES
219 SET
220 offer_performance_id = DECODE( p_offer_performance_id, FND_API.g_miss_num, offer_performance_id, p_offer_performance_id),
221 list_header_id = DECODE( p_list_header_id, FND_API.g_miss_num, list_header_id, p_list_header_id),
222 last_update_date = DECODE( p_last_update_date, FND_API.g_miss_date, last_update_date, p_last_update_date),
223 last_updated_by = DECODE( p_last_updated_by, FND_API.g_miss_num, last_updated_by, p_last_updated_by),
224 last_update_login = DECODE( p_last_update_login, FND_API.g_miss_num, last_update_login, p_last_update_login),
225 object_version_number = DECODE( p_object_version_number, FND_API.g_miss_num, object_version_number, p_object_version_number),
226 product_attribute_context = DECODE( p_product_attribute_context, FND_API.g_miss_char, product_attribute_context, p_product_attribute_context),
227 product_attribute = DECODE( p_product_attribute, FND_API.g_miss_char, product_attribute, p_product_attribute),
228 product_attr_value = DECODE( p_product_attr_value, FND_API.g_miss_char, product_attr_value, p_product_attr_value),
229 channel_id = DECODE( p_channel_id, FND_API.g_miss_num, channel_id, p_channel_id),
230 start_date = DECODE( p_start_date, FND_API.g_miss_date, start_date, p_start_date),
231 end_date = DECODE( p_end_date, FND_API.g_miss_date, end_date, p_end_date),
232 estimated_value = DECODE( p_estimated_value, FND_API.g_miss_num, estimated_value, p_estimated_value),
233 required_flag = DECODE( p_required_flag, FND_API.g_miss_char, required_flag, p_required_flag),
234 attribute_category = DECODE( p_attribute_category, FND_API.g_miss_char, attribute_category, p_attribute_category),
235 attribute1 = DECODE( p_attribute1, FND_API.g_miss_char, attribute1, p_attribute1),
236 attribute2 = DECODE( p_attribute2, FND_API.g_miss_char, attribute2, p_attribute2),
237 attribute3 = DECODE( p_attribute3, FND_API.g_miss_char, attribute3, p_attribute3),
238 attribute4 = DECODE( p_attribute4, FND_API.g_miss_char, attribute4, p_attribute4),
239 attribute5 = DECODE( p_attribute5, FND_API.g_miss_char, attribute5, p_attribute5),
240 attribute6 = DECODE( p_attribute6, FND_API.g_miss_char, attribute6, p_attribute6),
241 attribute7 = DECODE( p_attribute7, FND_API.g_miss_char, attribute7, p_attribute7),
242 attribute8 = DECODE( p_attribute8, FND_API.g_miss_char, attribute8, p_attribute8),
243 attribute9 = DECODE( p_attribute9, FND_API.g_miss_char, attribute9, p_attribute9),
244 attribute10 = DECODE( p_attribute10, FND_API.g_miss_char, attribute10, p_attribute10),
245 attribute11 = DECODE( p_attribute11, FND_API.g_miss_char, attribute11, p_attribute11),
246 attribute12 = DECODE( p_attribute12, FND_API.g_miss_char, attribute12, p_attribute12),
247 attribute13 = DECODE( p_attribute13, FND_API.g_miss_char, attribute13, p_attribute13),
248 attribute14 = DECODE( p_attribute14, FND_API.g_miss_char, attribute14, p_attribute14),
249 attribute15 = DECODE( p_attribute15, FND_API.g_miss_char, attribute15, p_attribute15),
250 security_group_id = DECODE( p_security_group_id, FND_API.g_miss_num, security_group_id, p_security_group_id),
251 requirement_type = DECODE( p_requirement_type, FND_API.g_miss_char, requirement_type, p_requirement_type),
252 uom_code = DECODE( p_uom_code, FND_API.g_miss_char, uom_code, p_uom_code),
253 description = DECODE( p_description, FND_API.g_miss_char, description, p_description)
254 WHERE OFFER_PERFORMANCE_ID = p_OFFER_PERFORMANCE_ID
255 AND object_version_number = p_object_version_number-1;
256
257 IF (SQL%NOTFOUND) THEN
258 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
259 END IF;
260 END Update_Row;
261
262
263 ----------------------------------------------------------
264 ---- MEDIA ----
265 ----------------------------------------------------------
266
267 -- ========================================================
268 --
269 -- NAME
270 -- createDeleteBody
271 --
272 -- PURPOSE
273 --
274 -- NOTES
275 --
276 -- HISTORY
277 --
278 -- ========================================================
279 PROCEDURE Delete_Row(
280 p_OFFER_PERFORMANCE_ID NUMBER)
281 IS
282 BEGIN
283 DELETE FROM OZF_OFFER_PERFORMANCES
284 WHERE OFFER_PERFORMANCE_ID = p_OFFER_PERFORMANCE_ID;
285 If (SQL%NOTFOUND) then
286 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
287 End If;
288 END Delete_Row ;
289
290
291
292 ----------------------------------------------------------
293 ---- MEDIA ----
294 ----------------------------------------------------------
295
296 -- ========================================================
297 --
298 -- NAME
299 -- createLockBody
300 --
301 -- PURPOSE
302 --
303 -- NOTES
304 --
305 -- HISTORY
306 --
307 -- ========================================================
308 PROCEDURE Lock_Row(
309 p_offer_performance_id NUMBER,
310 p_list_header_id NUMBER,
311 p_last_update_date DATE,
312 p_last_updated_by NUMBER,
313 p_creation_date DATE,
314 p_created_by NUMBER,
315 p_last_update_login NUMBER,
316 p_object_version_number NUMBER,
317 p_product_attribute_context VARCHAR2,
318 p_product_attribute VARCHAR2,
319 p_product_attr_value VARCHAR2,
320 p_channel_id NUMBER,
321 p_start_date DATE,
322 p_end_date DATE,
323 p_estimated_value NUMBER,
324 p_required_flag VARCHAR2,
325 p_attribute_category VARCHAR2,
326 p_attribute1 VARCHAR2,
327 p_attribute2 VARCHAR2,
328 p_attribute3 VARCHAR2,
329 p_attribute4 VARCHAR2,
330 p_attribute5 VARCHAR2,
331 p_attribute6 VARCHAR2,
332 p_attribute7 VARCHAR2,
333 p_attribute8 VARCHAR2,
334 p_attribute9 VARCHAR2,
335 p_attribute10 VARCHAR2,
336 p_attribute11 VARCHAR2,
337 p_attribute12 VARCHAR2,
338 p_attribute13 VARCHAR2,
339 p_attribute14 VARCHAR2,
340 p_attribute15 VARCHAR2,
341 p_security_group_id NUMBER,
342 p_requirement_type VARCHAR2,
343 p_uom_code VARCHAR2,
344 p_description VARCHAR2)
345
346 IS
347 CURSOR C IS
348 SELECT *
349 FROM OZF_OFFER_PERFORMANCES
350 WHERE OFFER_PERFORMANCE_ID = p_OFFER_PERFORMANCE_ID
354 OPEN c;
351 FOR UPDATE of OFFER_PERFORMANCE_ID NOWAIT;
352 Recinfo C%ROWTYPE;
353 BEGIN
355 FETCH c INTO Recinfo;
356 If (c%NOTFOUND) then
357 CLOSE c;
358 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
359 APP_EXCEPTION.RAISE_EXCEPTION;
360 END IF;
361 CLOSE C;
362 IF (
363 ( Recinfo.offer_performance_id = p_offer_performance_id)
364 AND ( ( Recinfo.list_header_id = p_list_header_id)
365 OR ( ( Recinfo.list_header_id IS NULL )
366 AND ( p_list_header_id IS NULL )))
367 AND ( ( Recinfo.last_update_date = p_last_update_date)
368 OR ( ( Recinfo.last_update_date IS NULL )
369 AND ( p_last_update_date IS NULL )))
370 AND ( ( Recinfo.last_updated_by = p_last_updated_by)
371 OR ( ( Recinfo.last_updated_by IS NULL )
372 AND ( p_last_updated_by IS NULL )))
373 AND ( ( Recinfo.creation_date = p_creation_date)
374 OR ( ( Recinfo.creation_date IS NULL )
375 AND ( p_creation_date IS NULL )))
376 AND ( ( Recinfo.created_by = p_created_by)
377 OR ( ( Recinfo.created_by IS NULL )
378 AND ( p_created_by IS NULL )))
379 AND ( ( Recinfo.last_update_login = p_last_update_login)
380 OR ( ( Recinfo.last_update_login IS NULL )
381 AND ( p_last_update_login IS NULL )))
382 AND ( ( Recinfo.object_version_number = p_object_version_number)
383 OR ( ( Recinfo.object_version_number IS NULL )
384 AND ( p_object_version_number IS NULL )))
385 AND ( ( Recinfo.product_attribute_context = p_product_attribute_context)
386 OR ( ( Recinfo.product_attribute_context IS NULL )
387 AND ( p_product_attribute_context IS NULL )))
388 AND ( ( Recinfo.product_attribute = p_product_attribute)
389 OR ( ( Recinfo.product_attribute IS NULL )
390 AND ( p_product_attribute IS NULL )))
391 AND ( ( Recinfo.product_attr_value = p_product_attr_value)
392 OR ( ( Recinfo.product_attr_value IS NULL )
393 AND ( p_product_attr_value IS NULL )))
394 AND ( ( Recinfo.channel_id = p_channel_id)
395 OR ( ( Recinfo.channel_id IS NULL )
396 AND ( p_channel_id IS NULL )))
397 AND ( ( Recinfo.start_date = p_start_date)
398 OR ( ( Recinfo.start_date IS NULL )
399 AND ( p_start_date IS NULL )))
400 AND ( ( Recinfo.end_date = p_end_date)
401 OR ( ( Recinfo.end_date IS NULL )
402 AND ( p_end_date IS NULL )))
403 AND ( ( Recinfo.estimated_value = p_estimated_value)
404 OR ( ( Recinfo.estimated_value IS NULL )
405 AND ( p_estimated_value IS NULL )))
406 AND ( ( Recinfo.required_flag = p_required_flag)
407 OR ( ( Recinfo.required_flag IS NULL )
408 AND ( p_required_flag IS NULL )))
409 AND ( ( Recinfo.attribute_category = p_attribute_category)
410 OR ( ( Recinfo.attribute_category IS NULL )
411 AND ( p_attribute_category IS NULL )))
412 AND ( ( Recinfo.attribute1 = p_attribute1)
413 OR ( ( Recinfo.attribute1 IS NULL )
414 AND ( p_attribute1 IS NULL )))
415 AND ( ( Recinfo.attribute2 = p_attribute2)
416 OR ( ( Recinfo.attribute2 IS NULL )
417 AND ( p_attribute2 IS NULL )))
418 AND ( ( Recinfo.attribute3 = p_attribute3)
419 OR ( ( Recinfo.attribute3 IS NULL )
420 AND ( p_attribute3 IS NULL )))
421 AND ( ( Recinfo.attribute4 = p_attribute4)
422 OR ( ( Recinfo.attribute4 IS NULL )
423 AND ( p_attribute4 IS NULL )))
424 AND ( ( Recinfo.attribute5 = p_attribute5)
425 OR ( ( Recinfo.attribute5 IS NULL )
426 AND ( p_attribute5 IS NULL )))
427 AND ( ( Recinfo.attribute6 = p_attribute6)
428 OR ( ( Recinfo.attribute6 IS NULL )
429 AND ( p_attribute6 IS NULL )))
430 AND ( ( Recinfo.attribute7 = p_attribute7)
431 OR ( ( Recinfo.attribute7 IS NULL )
432 AND ( p_attribute7 IS NULL )))
433 AND ( ( Recinfo.attribute8 = p_attribute8)
434 OR ( ( Recinfo.attribute8 IS NULL )
435 AND ( p_attribute8 IS NULL )))
436 AND ( ( Recinfo.attribute9 = p_attribute9)
437 OR ( ( Recinfo.attribute9 IS NULL )
438 AND ( p_attribute9 IS NULL )))
439 AND ( ( Recinfo.attribute10 = p_attribute10)
440 OR ( ( Recinfo.attribute10 IS NULL )
441 AND ( p_attribute10 IS NULL )))
442 AND ( ( Recinfo.attribute11 = p_attribute11)
443 OR ( ( Recinfo.attribute11 IS NULL )
444 AND ( p_attribute11 IS NULL )))
445 AND ( ( Recinfo.attribute12 = p_attribute12)
446 OR ( ( Recinfo.attribute12 IS NULL )
447 AND ( p_attribute12 IS NULL )))
448 AND ( ( Recinfo.attribute13 = p_attribute13)
449 OR ( ( Recinfo.attribute13 IS NULL )
450 AND ( p_attribute13 IS NULL )))
451 AND ( ( Recinfo.attribute14 = p_attribute14)
452 OR ( ( Recinfo.attribute14 IS NULL )
453 AND ( p_attribute14 IS NULL )))
454 AND ( ( Recinfo.attribute15 = p_attribute15)
455 OR ( ( Recinfo.attribute15 IS NULL )
456 AND ( p_attribute15 IS NULL )))
457 AND ( ( Recinfo.security_group_id = p_security_group_id)
458 OR ( ( Recinfo.security_group_id IS NULL )
459 AND ( p_security_group_id IS NULL )))
460 ) THEN
464 APP_EXCEPTION.RAISE_EXCEPTION;
461 RETURN;
462 ELSE
463 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
465 END IF;
466 END Lock_Row;
467
468 END OZF_OFFER_PERFORMANCES_PKG;