[Home] [Help]
PACKAGE BODY: APPS.OZF_DISC_LINE_PKG
Source
1 PACKAGE BODY OZF_DISC_LINE_PKG as
2 /* $Header: ozftodlb.pls 120.1 2006/05/04 15:25:15 julou noship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- OZF_DISC_LINE_PKG
7 -- Purpose
8 --
9 -- History
10 -- Wed May 18 2005:11/57 AM RSSHARMA Added Insert_row and Update_row for Volume Offers
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_DISC_LINE_PKG';
21 G_FILE_NAME CONSTANT VARCHAR2(12) := 'ozftdlb.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_discount_line_id IN OUT NOCOPY NUMBER,
40 p_parent_discount_line_id NUMBER,
41 p_volume_from NUMBER,
42 p_volume_to NUMBER,
43 p_volume_operator VARCHAR2,
44 p_volume_type VARCHAR2,
45 p_volume_break_type VARCHAR2,
46 p_discount NUMBER,
47 p_discount_type VARCHAR2,
48 p_tier_type VARCHAR2,
49 p_tier_level VARCHAR2,
50 p_incompatibility_group VARCHAR2,
51 p_precedence NUMBER,
52 p_bucket VARCHAR2,
53 p_scan_value NUMBER,
54 p_scan_data_quantity NUMBER,
55 p_scan_unit_forecast NUMBER,
56 p_channel_id NUMBER,
57 p_adjustment_flag VARCHAR2,
58 p_start_date_active DATE,
59 p_end_date_active DATE,
60 p_uom_code VARCHAR2,
61 p_creation_date DATE,
62 p_created_by NUMBER,
63 p_last_update_date DATE,
64 p_last_updated_by NUMBER,
65 p_last_update_login NUMBER,
66 px_object_version_number IN OUT NOCOPY NUMBER,
67 p_context VARCHAR2,
68 p_attribute1 VARCHAR2,
69 p_attribute2 VARCHAR2,
70 p_attribute3 VARCHAR2,
71 p_attribute4 VARCHAR2,
72 p_attribute5 VARCHAR2,
73 p_attribute6 VARCHAR2,
74 p_attribute7 VARCHAR2,
75 p_attribute8 VARCHAR2,
76 p_attribute9 VARCHAR2,
77 p_attribute10 VARCHAR2,
78 p_attribute11 VARCHAR2,
79 p_attribute12 VARCHAR2,
80 p_attribute13 VARCHAR2,
81 p_attribute14 VARCHAR2,
82 p_attribute15 VARCHAR2,
83 p_offer_id NUMBER)
84
85 IS
86 x_rowid VARCHAR2(30);
87
88
89 BEGIN
90
91
92 px_object_version_number := nvl(px_object_version_number, 1);
93
94
95 INSERT INTO ozf_offer_discount_lines(
96 offer_discount_line_id,
97 parent_discount_line_id,
98 volume_from,
99 volume_to,
100 volume_operator,
101 volume_type,
102 volume_break_type,
103 discount,
104 discount_type,
105 tier_type,
106 tier_level,
107 incompatibility_group,
108 precedence,
109 bucket,
110 scan_value,
111 scan_data_quantity,
112 scan_unit_forecast,
113 channel_id,
114 adjustment_flag,
115 start_date_active,
116 end_date_active,
117 uom_code,
118 creation_date,
119 created_by,
120 last_update_date,
121 last_updated_by,
122 last_update_login,
123 object_version_number,
124 context,
125 attribute1,
126 attribute2,
127 attribute3,
128 attribute4,
129 attribute5,
130 attribute6,
131 attribute7,
132 attribute8,
133 attribute9,
134 attribute10,
135 attribute11,
136 attribute12,
137 attribute13,
138 attribute14,
139 attribute15,
140 offer_id
141 ) VALUES (
142 DECODE( px_offer_discount_line_id, FND_API.G_MISS_NUM, NULL, px_offer_discount_line_id),
143 DECODE( p_parent_discount_line_id, FND_API.G_MISS_NUM, NULL, p_parent_discount_line_id),
144 DECODE( p_volume_from, FND_API.G_MISS_NUM, NULL, p_volume_from),
145 DECODE( p_volume_to, FND_API.G_MISS_NUM, NULL, p_volume_to),
146 DECODE( p_volume_operator, FND_API.g_miss_char, NULL, p_volume_operator),
147 DECODE( p_volume_type, FND_API.g_miss_char, NULL, p_volume_type),
148 DECODE( p_volume_break_type, FND_API.g_miss_char, NULL, p_volume_break_type),
149 DECODE( p_discount, FND_API.G_MISS_NUM, NULL, p_discount),
150 DECODE( p_discount_type, FND_API.g_miss_char, NULL, p_discount_type),
151 DECODE( p_tier_type, FND_API.g_miss_char, NULL, p_tier_type),
152 DECODE( p_tier_level, FND_API.g_miss_char, NULL, p_tier_level),
153 DECODE( p_incompatibility_group, FND_API.g_miss_char, NULL, p_incompatibility_group),
154 DECODE( p_precedence, FND_API.G_MISS_NUM, NULL, p_precedence),
155 DECODE( p_bucket, FND_API.g_miss_char, NULL, p_bucket),
156 DECODE( p_scan_value, FND_API.G_MISS_NUM, NULL, p_scan_value),
157 DECODE( p_scan_data_quantity, FND_API.G_MISS_NUM, NULL, p_scan_data_quantity),
158 DECODE( p_scan_unit_forecast, FND_API.G_MISS_NUM, NULL, p_scan_unit_forecast),
159 DECODE( p_channel_id, FND_API.G_MISS_NUM, NULL, p_channel_id),
160 DECODE( p_adjustment_flag, FND_API.g_miss_char, NULL, p_adjustment_flag),
161 DECODE( p_start_date_active, FND_API.G_MISS_DATE, to_date(NULL) , p_start_date_active),
162 DECODE( p_end_date_active, FND_API.G_MISS_DATE, to_date(NULL), p_end_date_active),
163 DECODE( p_uom_code, FND_API.g_miss_char, NULL, p_uom_code),
164 DECODE( p_creation_date, FND_API.G_MISS_DATE, SYSDATE, p_creation_date),
165 DECODE( p_created_by, FND_API.G_MISS_NUM, FND_GLOBAL.USER_ID, p_created_by),
166 DECODE( p_last_update_date, FND_API.G_MISS_DATE, SYSDATE, p_last_update_date),
167 DECODE( p_last_updated_by, FND_API.G_MISS_NUM, FND_GLOBAL.USER_ID, p_last_updated_by),
168 DECODE( p_last_update_login, FND_API.G_MISS_NUM, FND_GLOBAL.CONC_LOGIN_ID, p_last_update_login),
169 DECODE( px_object_version_number, FND_API.G_MISS_NUM, 1, px_object_version_number),
170 DECODE( p_context, FND_API.g_miss_char, NULL, p_context),
171 DECODE( p_attribute1, FND_API.g_miss_char, NULL, p_attribute1),
172 DECODE( p_attribute2, FND_API.g_miss_char, NULL, p_attribute2),
173 DECODE( p_attribute3, FND_API.g_miss_char, NULL, p_attribute3),
174 DECODE( p_attribute4, FND_API.g_miss_char, NULL, p_attribute4),
175 DECODE( p_attribute5, FND_API.g_miss_char, NULL, p_attribute5),
176 DECODE( p_attribute6, FND_API.g_miss_char, NULL, p_attribute6),
177 DECODE( p_attribute7, FND_API.g_miss_char, NULL, p_attribute7),
178 DECODE( p_attribute8, FND_API.g_miss_char, NULL, p_attribute8),
179 DECODE( p_attribute9, FND_API.g_miss_char, NULL, p_attribute9),
180 DECODE( p_attribute10, FND_API.g_miss_char, NULL, p_attribute10),
181 DECODE( p_attribute11, FND_API.g_miss_char, NULL, p_attribute11),
182 DECODE( p_attribute12, FND_API.g_miss_char, NULL, p_attribute12),
183 DECODE( p_attribute13, FND_API.g_miss_char, NULL, p_attribute13),
184 DECODE( p_attribute14, FND_API.g_miss_char, NULL, p_attribute14),
185 DECODE( p_attribute15, FND_API.g_miss_char, NULL, p_attribute15),
186 DECODE( p_offer_id, FND_API.G_MISS_NUM, NULL, p_offer_id));
187
188 END Insert_Row;
189
190 -- ========================================================
191 --
192 -- NAME
193 -- Insert_Row for volume offer
194 --
195 -- PURPOSE
196 --
197 -- NOTES
198 --
199 -- HISTORY
200 --
201 -- ========================================================
202 PROCEDURE Insert_Row(
203 px_offer_discount_line_id IN OUT NOCOPY NUMBER,
204 p_parent_discount_line_id NUMBER,
205 p_volume_from NUMBER,
206 p_volume_to NUMBER,
207 p_volume_operator VARCHAR2,
208 p_volume_type VARCHAR2,
209 p_volume_break_type VARCHAR2,
210 p_discount NUMBER,
211 p_discount_type VARCHAR2,
212 p_tier_type VARCHAR2,
213 p_tier_level VARCHAR2,
214 p_incompatibility_group VARCHAR2,
215 p_precedence NUMBER,
216 p_bucket VARCHAR2,
217 p_scan_value NUMBER,
218 p_scan_data_quantity NUMBER,
219 p_scan_unit_forecast NUMBER,
220 p_channel_id NUMBER,
221 p_adjustment_flag VARCHAR2,
222 p_start_date_active DATE,
223 p_end_date_active DATE,
224 p_uom_code VARCHAR2,
225 p_creation_date DATE,
226 p_created_by NUMBER,
227 p_last_update_date DATE,
228 p_last_updated_by NUMBER,
229 p_last_update_login NUMBER,
230 px_object_version_number IN OUT NOCOPY NUMBER,
231 p_context VARCHAR2,
232 p_attribute1 VARCHAR2,
233 p_attribute2 VARCHAR2,
234 p_attribute3 VARCHAR2,
235 p_attribute4 VARCHAR2,
236 p_attribute5 VARCHAR2,
237 p_attribute6 VARCHAR2,
238 p_attribute7 VARCHAR2,
239 p_attribute8 VARCHAR2,
240 p_attribute9 VARCHAR2,
241 p_attribute10 VARCHAR2,
242 p_attribute11 VARCHAR2,
243 p_attribute12 VARCHAR2,
244 p_attribute13 VARCHAR2,
245 p_attribute14 VARCHAR2,
246 p_attribute15 VARCHAR2,
247 p_offer_id NUMBER,
248 p_formula_id NUMBER)
249
250 IS
251 x_rowid VARCHAR2(30);
252
253
254 BEGIN
255
256
257 px_object_version_number := nvl(px_object_version_number, 1);
258
259
260 INSERT INTO ozf_offer_discount_lines(
261 offer_discount_line_id,
262 parent_discount_line_id,
263 volume_from,
264 volume_to,
265 volume_operator,
266 volume_type,
267 volume_break_type,
268 discount,
269 discount_type,
270 tier_type,
271 tier_level,
272 incompatibility_group,
273 precedence,
274 bucket,
275 scan_value,
276 scan_data_quantity,
277 scan_unit_forecast,
278 channel_id,
279 adjustment_flag,
280 start_date_active,
281 end_date_active,
282 uom_code,
283 creation_date,
284 created_by,
285 last_update_date,
286 last_updated_by,
287 last_update_login,
288 object_version_number,
289 context,
290 attribute1,
291 attribute2,
292 attribute3,
293 attribute4,
294 attribute5,
295 attribute6,
296 attribute7,
297 attribute8,
298 attribute9,
299 attribute10,
300 attribute11,
301 attribute12,
302 attribute13,
303 attribute14,
304 attribute15,
305 offer_id,
306 formula_id
307 ) VALUES (
308 DECODE( px_offer_discount_line_id, FND_API.G_MISS_NUM, NULL, px_offer_discount_line_id),
309 DECODE( p_parent_discount_line_id, FND_API.G_MISS_NUM, NULL, p_parent_discount_line_id),
310 DECODE( p_volume_from, FND_API.G_MISS_NUM, NULL, p_volume_from),
311 DECODE( p_volume_to, FND_API.G_MISS_NUM, NULL, p_volume_to),
312 DECODE( p_volume_operator, FND_API.g_miss_char, NULL, p_volume_operator),
313 DECODE( p_volume_type, FND_API.g_miss_char, NULL, p_volume_type),
314 DECODE( p_volume_break_type, FND_API.g_miss_char, NULL, p_volume_break_type),
315 DECODE( p_discount, FND_API.G_MISS_NUM, NULL, p_discount),
316 DECODE( p_discount_type, FND_API.g_miss_char, NULL, p_discount_type),
317 DECODE( p_tier_type, FND_API.g_miss_char, NULL, p_tier_type),
318 DECODE( p_tier_level, FND_API.g_miss_char, NULL, p_tier_level),
319 DECODE( p_incompatibility_group, FND_API.g_miss_char, NULL, p_incompatibility_group),
320 DECODE( p_precedence, FND_API.G_MISS_NUM, NULL, p_precedence),
321 DECODE( p_bucket, FND_API.g_miss_char, NULL, p_bucket),
322 DECODE( p_scan_value, FND_API.G_MISS_NUM, NULL, p_scan_value),
323 DECODE( p_scan_data_quantity, FND_API.G_MISS_NUM, NULL, p_scan_data_quantity),
324 DECODE( p_scan_unit_forecast, FND_API.G_MISS_NUM, NULL, p_scan_unit_forecast),
325 DECODE( p_channel_id, FND_API.G_MISS_NUM, NULL, p_channel_id),
326 DECODE( p_adjustment_flag, FND_API.g_miss_char, NULL, p_adjustment_flag),
327 DECODE( p_start_date_active, FND_API.G_MISS_DATE, to_date(NULL) , p_start_date_active),
328 DECODE( p_end_date_active, FND_API.G_MISS_DATE, to_date(NULL), p_end_date_active),
329 DECODE( p_uom_code, FND_API.g_miss_char, NULL, p_uom_code),
330 DECODE( p_creation_date, FND_API.G_MISS_DATE, SYSDATE, p_creation_date),
331 DECODE( p_created_by, FND_API.G_MISS_NUM, FND_GLOBAL.USER_ID, p_created_by),
332 DECODE( p_last_update_date, FND_API.G_MISS_DATE, SYSDATE, p_last_update_date),
333 DECODE( p_last_updated_by, FND_API.G_MISS_NUM, FND_GLOBAL.USER_ID, p_last_updated_by),
334 DECODE( p_last_update_login, FND_API.G_MISS_NUM, FND_GLOBAL.CONC_LOGIN_ID, p_last_update_login),
335 DECODE( px_object_version_number, FND_API.G_MISS_NUM, 1, px_object_version_number),
336 DECODE( p_context, FND_API.g_miss_char, NULL, p_context),
337 DECODE( p_attribute1, FND_API.g_miss_char, NULL, p_attribute1),
338 DECODE( p_attribute2, FND_API.g_miss_char, NULL, p_attribute2),
339 DECODE( p_attribute3, FND_API.g_miss_char, NULL, p_attribute3),
340 DECODE( p_attribute4, FND_API.g_miss_char, NULL, p_attribute4),
341 DECODE( p_attribute5, FND_API.g_miss_char, NULL, p_attribute5),
342 DECODE( p_attribute6, FND_API.g_miss_char, NULL, p_attribute6),
343 DECODE( p_attribute7, FND_API.g_miss_char, NULL, p_attribute7),
344 DECODE( p_attribute8, FND_API.g_miss_char, NULL, p_attribute8),
345 DECODE( p_attribute9, FND_API.g_miss_char, NULL, p_attribute9),
346 DECODE( p_attribute10, FND_API.g_miss_char, NULL, p_attribute10),
347 DECODE( p_attribute11, FND_API.g_miss_char, NULL, p_attribute11),
348 DECODE( p_attribute12, FND_API.g_miss_char, NULL, p_attribute12),
349 DECODE( p_attribute13, FND_API.g_miss_char, NULL, p_attribute13),
350 DECODE( p_attribute14, FND_API.g_miss_char, NULL, p_attribute14),
351 DECODE( p_attribute15, FND_API.g_miss_char, NULL, p_attribute15),
352 DECODE( p_offer_id, FND_API.G_MISS_NUM, NULL, p_offer_id),
353 DECODE( p_formula_id, FND_API.G_MISS_NUM, NULL, p_formula_id)
354 );
355
356 END Insert_Row;
357
358
359
360
361
362
363 -- ========================================================
364 --
365 -- NAME
366 -- Update_Row
367 --
368 -- PURPOSE
369 --
370 -- NOTES
371 --
372 -- HISTORY
373 --
374 -- ========================================================
375 PROCEDURE Update_Row(
376 p_offer_discount_line_id NUMBER,
377 p_parent_discount_line_id NUMBER,
378 p_volume_from NUMBER,
379 p_volume_to NUMBER,
380 p_volume_operator VARCHAR2,
381 p_volume_type VARCHAR2,
382 p_volume_break_type VARCHAR2,
383 p_discount NUMBER,
384 p_discount_type VARCHAR2,
385 p_tier_type VARCHAR2,
386 p_tier_level VARCHAR2,
387 p_incompatibility_group VARCHAR2,
388 p_precedence NUMBER,
389 p_bucket VARCHAR2,
390 p_scan_value NUMBER,
391 p_scan_data_quantity NUMBER,
392 p_scan_unit_forecast NUMBER,
393 p_channel_id NUMBER,
394 p_adjustment_flag VARCHAR2,
395 p_start_date_active DATE,
396 p_end_date_active DATE,
397 p_uom_code VARCHAR2,
398 p_last_update_date DATE,
399 p_last_updated_by NUMBER,
400 p_last_update_login NUMBER,
401 p_object_version_number IN NUMBER,
402 p_context VARCHAR2,
403 p_attribute1 VARCHAR2,
404 p_attribute2 VARCHAR2,
405 p_attribute3 VARCHAR2,
406 p_attribute4 VARCHAR2,
407 p_attribute5 VARCHAR2,
408 p_attribute6 VARCHAR2,
409 p_attribute7 VARCHAR2,
410 p_attribute8 VARCHAR2,
411 p_attribute9 VARCHAR2,
412 p_attribute10 VARCHAR2,
413 p_attribute11 VARCHAR2,
414 p_attribute12 VARCHAR2,
415 p_attribute13 VARCHAR2,
416 p_attribute14 VARCHAR2,
417 p_attribute15 VARCHAR2,
418 p_offer_id NUMBER)
419
420 IS
421 BEGIN
422 Update ozf_offer_discount_lines
423 SET
427 volume_to = DECODE( p_volume_to, null, volume_to, FND_API.G_MISS_NUM, null, p_volume_to),
424 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),
425 parent_discount_line_id = DECODE( p_parent_discount_line_id, null, parent_discount_line_id, FND_API.G_MISS_NUM, null, p_parent_discount_line_id),
426 volume_from = DECODE( p_volume_from, null, volume_from, FND_API.G_MISS_NUM, null, p_volume_from),
428 volume_operator = DECODE( p_volume_operator, null, volume_operator, FND_API.g_miss_char, null, p_volume_operator),
429 volume_type = DECODE( p_volume_type, null, volume_type, FND_API.g_miss_char, null, p_volume_type),
430 volume_break_type = DECODE( p_volume_break_type, null, volume_break_type, FND_API.g_miss_char, null, p_volume_break_type),
431 discount = DECODE( p_discount, null, discount, FND_API.G_MISS_NUM, null, p_discount),
432 discount_type = DECODE( p_discount_type, null, discount_type, FND_API.g_miss_char, null, p_discount_type),
433 tier_type = DECODE( p_tier_type, null, tier_type, FND_API.g_miss_char, null, p_tier_type),
434 tier_level = DECODE( p_tier_level, null, tier_level, FND_API.g_miss_char, null, p_tier_level),
435 incompatibility_group = DECODE( p_incompatibility_group, null, incompatibility_group, FND_API.g_miss_char, null, p_incompatibility_group),
436 precedence = DECODE( p_precedence, null, precedence, FND_API.G_MISS_NUM, null, p_precedence),
437 bucket = DECODE( p_bucket, null, bucket, FND_API.g_miss_char, null, p_bucket),
438 scan_value = DECODE( p_scan_value, null, scan_value, FND_API.G_MISS_NUM, null, p_scan_value),
439 scan_data_quantity = DECODE( p_scan_data_quantity, null, scan_data_quantity, FND_API.G_MISS_NUM, null, p_scan_data_quantity),
440 scan_unit_forecast = DECODE( p_scan_unit_forecast, null, scan_unit_forecast, FND_API.G_MISS_NUM, null, p_scan_unit_forecast),
441 channel_id = DECODE( p_channel_id, null, channel_id, FND_API.G_MISS_NUM, null, p_channel_id),
442 adjustment_flag = DECODE( p_adjustment_flag, null, adjustment_flag, FND_API.g_miss_char, null, p_adjustment_flag),
443 start_date_active = DECODE( p_start_date_active, to_date(NULL), start_date_active, FND_API.G_MISS_DATE, to_date(NULL), p_start_date_active),
444 end_date_active = DECODE( p_end_date_active, to_date(NULL), end_date_active, FND_API.G_MISS_DATE, to_date(NULL), p_end_date_active),
445 uom_code = DECODE( p_uom_code, null, uom_code, FND_API.g_miss_char, null, p_uom_code),
446 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),
447 last_updated_by = DECODE( p_last_updated_by, null, last_updated_by, FND_API.G_MISS_NUM, null, p_last_updated_by),
448 last_update_login = DECODE( p_last_update_login, null, last_update_login, FND_API.G_MISS_NUM, null, p_last_update_login),
449 object_version_number = nvl(p_object_version_number,0) + 1 ,
450 context = DECODE( p_context, FND_API.g_miss_char, context, p_context),
451 attribute1 = DECODE( p_attribute1, FND_API.g_miss_char, attribute1, p_attribute1),
452 attribute2 = DECODE( p_attribute2, FND_API.g_miss_char, attribute2, p_attribute2),
453 attribute3 = DECODE( p_attribute3, FND_API.g_miss_char, attribute3, p_attribute3),
454 attribute4 = DECODE( p_attribute4, FND_API.g_miss_char, attribute4, p_attribute4),
455 attribute5 = DECODE( p_attribute5, FND_API.g_miss_char, attribute5, p_attribute5),
456 attribute6 = DECODE( p_attribute6, FND_API.g_miss_char, attribute6, p_attribute6),
457 attribute7 = DECODE( p_attribute7, FND_API.g_miss_char, attribute7, p_attribute7),
458 attribute8 = DECODE( p_attribute8, FND_API.g_miss_char, attribute8, p_attribute8),
459 attribute9 = DECODE( p_attribute9, FND_API.g_miss_char, attribute9, p_attribute9),
460 attribute10 = DECODE( p_attribute10, FND_API.g_miss_char, attribute10, p_attribute10),
461 attribute11 = DECODE( p_attribute11, FND_API.g_miss_char, attribute11, p_attribute11),
462 attribute12 = DECODE( p_attribute12, FND_API.g_miss_char, attribute12, p_attribute12),
463 attribute13 = DECODE( p_attribute13, FND_API.g_miss_char, attribute13, p_attribute13),
464 attribute14 = DECODE( p_attribute14, FND_API.g_miss_char, attribute14, p_attribute14),
465 attribute15 = DECODE( p_attribute15, FND_API.g_miss_char, attribute15, p_attribute15),
466 offer_id = DECODE( p_offer_id, null, offer_id, FND_API.G_MISS_NUM, null, p_offer_id)
467 WHERE offer_discount_line_id = p_offer_discount_line_id
468 AND object_version_number = p_object_version_number;
469
470
471 IF (SQL%NOTFOUND) THEN
472 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
473 END IF;
474
475
476 END Update_Row;
477
478
479
480 -- ========================================================
481 --
482 -- NAME
483 -- Update_Row
484 --
485 -- PURPOSE
486 --
487 -- NOTES
488 --
489 -- HISTORY
490 --
491 -- ========================================================
492 PROCEDURE Update_Row(
493 p_offer_discount_line_id NUMBER,
494 p_parent_discount_line_id NUMBER,
495 p_volume_from NUMBER,
496 p_volume_to NUMBER,
497 p_volume_operator VARCHAR2,
498 p_volume_type VARCHAR2,
499 p_volume_break_type VARCHAR2,
500 p_discount NUMBER,
501 p_discount_type VARCHAR2,
502 p_tier_type VARCHAR2,
503 p_tier_level VARCHAR2,
504 p_incompatibility_group VARCHAR2,
505 p_precedence NUMBER,
506 p_bucket VARCHAR2,
507 p_scan_value NUMBER,
508 p_scan_data_quantity NUMBER,
509 p_scan_unit_forecast NUMBER,
513 p_end_date_active DATE,
510 p_channel_id NUMBER,
511 p_adjustment_flag VARCHAR2,
512 p_start_date_active DATE,
514 p_uom_code VARCHAR2,
515 p_last_update_date DATE,
516 p_last_updated_by NUMBER,
517 p_last_update_login NUMBER,
518 p_object_version_number IN NUMBER,
519 p_context VARCHAR2,
520 p_attribute1 VARCHAR2,
521 p_attribute2 VARCHAR2,
522 p_attribute3 VARCHAR2,
523 p_attribute4 VARCHAR2,
524 p_attribute5 VARCHAR2,
525 p_attribute6 VARCHAR2,
526 p_attribute7 VARCHAR2,
527 p_attribute8 VARCHAR2,
528 p_attribute9 VARCHAR2,
529 p_attribute10 VARCHAR2,
530 p_attribute11 VARCHAR2,
531 p_attribute12 VARCHAR2,
532 p_attribute13 VARCHAR2,
533 p_attribute14 VARCHAR2,
534 p_attribute15 VARCHAR2,
535 p_offer_id NUMBER,
536 p_formula_id NUMBER)
537
538 IS
539 BEGIN
540 Update ozf_offer_discount_lines
541 SET
542 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),
543 parent_discount_line_id = DECODE( p_parent_discount_line_id, null, parent_discount_line_id, FND_API.G_MISS_NUM, null, p_parent_discount_line_id),
544 volume_from = DECODE( p_volume_from, null, volume_from, FND_API.G_MISS_NUM, null, p_volume_from),
545 volume_to = DECODE( p_volume_to, null, volume_to, FND_API.G_MISS_NUM, null, p_volume_to),
546 volume_operator = DECODE( p_volume_operator, null, volume_operator, FND_API.g_miss_char, null, p_volume_operator),
547 volume_type = DECODE( p_volume_type, null, volume_type, FND_API.g_miss_char, null, p_volume_type),
548 volume_break_type = DECODE( p_volume_break_type, null, volume_break_type, FND_API.g_miss_char, null, p_volume_break_type),
549 discount = DECODE( p_discount, null, discount, FND_API.G_MISS_NUM, null, p_discount),
550 discount_type = DECODE( p_discount_type, null, discount_type, FND_API.g_miss_char, null, p_discount_type),
551 tier_type = DECODE( p_tier_type, null, tier_type, FND_API.g_miss_char, null, p_tier_type),
552 tier_level = DECODE( p_tier_level, null, tier_level, FND_API.g_miss_char, null, p_tier_level),
553 incompatibility_group = DECODE( p_incompatibility_group, null, incompatibility_group, FND_API.g_miss_char, null, p_incompatibility_group),
554 precedence = DECODE( p_precedence, null, precedence, FND_API.G_MISS_NUM, null, p_precedence),
555 bucket = DECODE( p_bucket, null, bucket, FND_API.g_miss_char, null, p_bucket),
556 scan_value = DECODE( p_scan_value, null, scan_value, FND_API.G_MISS_NUM, null, p_scan_value),
557 scan_data_quantity = DECODE( p_scan_data_quantity, null, scan_data_quantity, FND_API.G_MISS_NUM, null, p_scan_data_quantity),
558 scan_unit_forecast = DECODE( p_scan_unit_forecast, null, scan_unit_forecast, FND_API.G_MISS_NUM, null, p_scan_unit_forecast),
559 channel_id = DECODE( p_channel_id, null, channel_id, FND_API.G_MISS_NUM, null, p_channel_id),
560 adjustment_flag = DECODE( p_adjustment_flag, null, adjustment_flag, FND_API.g_miss_char, null, p_adjustment_flag),
561 start_date_active = DECODE( p_start_date_active, to_date(NULL), start_date_active, FND_API.G_MISS_DATE, to_date(NULL), p_start_date_active),
562 end_date_active = DECODE( p_end_date_active, to_date(NULL), end_date_active, FND_API.G_MISS_DATE, to_date(NULL), p_end_date_active),
563 uom_code = DECODE( p_uom_code, null, uom_code, FND_API.g_miss_char, null, p_uom_code),
564 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),
565 last_updated_by = DECODE( p_last_updated_by, null, last_updated_by, FND_API.G_MISS_NUM, null, p_last_updated_by),
566 last_update_login = DECODE( p_last_update_login, null, last_update_login, FND_API.G_MISS_NUM, null, p_last_update_login),
567 object_version_number = nvl(p_object_version_number,0) + 1 ,
568 context = DECODE( p_context, FND_API.g_miss_char, context, p_context),
569 attribute1 = DECODE( p_attribute1, FND_API.g_miss_char, attribute1, p_attribute1),
570 attribute2 = DECODE( p_attribute2, FND_API.g_miss_char, attribute2, p_attribute2),
571 attribute3 = DECODE( p_attribute3, FND_API.g_miss_char, attribute3, p_attribute3),
572 attribute4 = DECODE( p_attribute4, FND_API.g_miss_char, attribute4, p_attribute4),
573 attribute5 = DECODE( p_attribute5, FND_API.g_miss_char, attribute5, p_attribute5),
574 attribute6 = DECODE( p_attribute6, FND_API.g_miss_char, attribute6, p_attribute6),
575 attribute7 = DECODE( p_attribute7, FND_API.g_miss_char, attribute7, p_attribute7),
576 attribute8 = DECODE( p_attribute8, FND_API.g_miss_char, attribute8, p_attribute8),
577 attribute9 = DECODE( p_attribute9, FND_API.g_miss_char, attribute9, p_attribute9),
578 attribute10 = DECODE( p_attribute10, FND_API.g_miss_char, attribute10, p_attribute10),
579 attribute11 = DECODE( p_attribute11, FND_API.g_miss_char, attribute11, p_attribute11),
580 attribute12 = DECODE( p_attribute12, FND_API.g_miss_char, attribute12, p_attribute12),
581 attribute13 = DECODE( p_attribute13, FND_API.g_miss_char, attribute13, p_attribute13),
582 attribute14 = DECODE( p_attribute14, FND_API.g_miss_char, attribute14, p_attribute14),
583 attribute15 = DECODE( p_attribute15, FND_API.g_miss_char, attribute15, p_attribute15),
584 offer_id = DECODE( p_offer_id, null, offer_id, FND_API.G_MISS_NUM, null, p_offer_id),
585 formula_id = DECODE( p_formula_id, null, formula_id, FND_API.G_MISS_NUM, null, p_formula_id)
586 WHERE offer_discount_line_id = p_offer_discount_line_id
590 IF (SQL%NOTFOUND) THEN
587 AND object_version_number = p_object_version_number;
588
589
591 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
592 END IF;
593
594
595 END Update_Row;
596
597
598
599 -- ========================================================
600 --
601 -- NAME
602 -- Delete_Row
603 --
604 -- PURPOSE
605 --
606 -- NOTES
607 --
608 -- HISTORY
609 --
610 -- ========================================================
611 PROCEDURE Delete_Row(
612 p_offer_discount_line_id NUMBER,
613 p_object_version_number NUMBER)
614 IS
615 BEGIN
616 DELETE FROM ozf_offer_discount_lines
617 WHERE offer_discount_line_id = p_offer_discount_line_id
618 AND object_version_number = p_object_version_number;
619 If (SQL%NOTFOUND) then
620 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
621 End If;
622 END Delete_Row ;
623
624
625
626
627 PROCEDURE delete_tiers(p_offer_discount_line_id NUMBER)
628 IS
629 BEGIN
630 DELETE FROM ozf_offer_discount_lines WHERE parent_discount_line_id = p_offer_discount_line_id;
631
632 If (SQL%NOTFOUND) then
633 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
634 End If;
635
636 END delete_tiers;
637 -- ========================================================
638 --
639 -- NAME
640 -- Lock_Row
641 --
642 -- PURPOSE
643 --
644 -- NOTES
645 --
646 -- HISTORY
647 --
648 -- ========================================================
649 PROCEDURE Lock_Row(
650 p_offer_discount_line_id NUMBER,
651 p_object_version_number NUMBER)
652 IS
653 CURSOR C IS
654 SELECT *
655 FROM ozf_offer_discount_lines
656 WHERE offer_discount_line_id = p_offer_discount_line_id
657 AND object_version_number = p_object_version_number
658 FOR UPDATE OF offer_discount_line_id NOWAIT;
659 Recinfo C%ROWTYPE;
660 BEGIN
661
662 OPEN c;
663 FETCH c INTO Recinfo;
664 IF (c%NOTFOUND) THEN
665 CLOSE c;
666 AMS_Utility_PVT.error_message ('AMS_API_RECORD_NOT_FOUND');
667 RAISE FND_API.g_exc_error;
668 END IF;
669 CLOSE c;
670 END Lock_Row;
671
672
673
674 END OZF_DISC_LINE_PKG;