[Home] [Help]
PACKAGE BODY: APPS.OZF_CREATE_OZF_PROD_LINE_PKG
Source
1 PACKAGE BODY OZF_Create_Ozf_Prod_Line_PKG as
2 /* $Header: ozftodpb.pls 120.0 2005/06/01 02:55:05 appldev noship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- OZF_Create_Ozf_Prod_Line_PKG
7 -- Purpose
8 --
9 -- History
10 -- Wed May 18 2005:11/52 AM RSSHARMA Added Insert_row and Update_row methods for Volume Offer
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_Create_Ozf_Prod_Line_PKG';
21 G_FILE_NAME CONSTANT VARCHAR2(12) := 'ozftodpb.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_off_discount_product_id IN OUT NOCOPY NUMBER,
40 p_parent_off_disc_prod_id IN NUMBER,
41 p_product_level VARCHAR2,
42 p_product_id NUMBER,
43 p_excluder_flag VARCHAR2,
44 p_uom_code VARCHAR2,
45 p_start_date_active DATE,
46 p_end_date_active DATE,
47 p_offer_discount_line_id NUMBER,
48 p_offer_id NUMBER,
49 p_creation_date DATE,
50 p_created_by NUMBER,
51 p_last_update_date DATE,
52 p_last_updated_by NUMBER,
53 p_last_update_login NUMBER,
54 px_object_version_number IN OUT NOCOPY NUMBER)
55
56 IS
57 x_rowid VARCHAR2(30);
58
59
60 BEGIN
61
62
63 px_object_version_number := nvl(px_object_version_number, 1);
64
65
66 INSERT INTO ozf_offer_discount_products(
67 off_discount_product_id,
68 parent_off_disc_prod_id,
69 product_level,
70 product_id,
71 excluder_flag,
72 uom_code,
73 start_date_active,
74 end_date_active,
75 offer_discount_line_id,
76 offer_id,
77 creation_date,
78 created_by,
79 last_update_date,
80 last_updated_by,
81 last_update_login,
82 object_version_number
83 ) VALUES (
84 DECODE( px_off_discount_product_id, FND_API.G_MISS_NUM, NULL, px_off_discount_product_id),
85 DECODE( p_parent_off_disc_prod_id, FND_API.G_MISS_NUM, NULL, p_parent_off_disc_prod_id),
86 DECODE( p_product_level, FND_API.g_miss_char, NULL, p_product_level),
87 DECODE( p_product_id, FND_API.G_MISS_NUM, NULL, p_product_id),
88 DECODE( p_excluder_flag, FND_API.g_miss_char, NULL, p_excluder_flag),
89 DECODE( p_uom_code, FND_API.g_miss_char, NULL, p_uom_code),
90 DECODE( p_start_date_active, FND_API.G_MISS_DATE, to_date(NULL), p_start_date_active),
91 DECODE( p_end_date_active, FND_API.G_MISS_DATE, to_date(NULL), p_end_date_active),
92 DECODE( p_offer_discount_line_id, FND_API.G_MISS_NUM, NULL, p_offer_discount_line_id),
93 DECODE( p_offer_id, FND_API.G_MISS_NUM, NULL, p_offer_id),
94 DECODE( p_creation_date, FND_API.G_MISS_DATE, SYSDATE, p_creation_date),
95 DECODE( p_created_by, FND_API.G_MISS_NUM, FND_GLOBAL.USER_ID, p_created_by),
96 DECODE( p_last_update_date, FND_API.G_MISS_DATE, SYSDATE, p_last_update_date),
97 DECODE( p_last_updated_by, FND_API.G_MISS_NUM, FND_GLOBAL.USER_ID, p_last_updated_by),
98 DECODE( p_last_update_login, FND_API.G_MISS_NUM, FND_GLOBAL.CONC_LOGIN_ID, p_last_update_login),
99 DECODE( px_object_version_number, FND_API.G_MISS_NUM, 1, px_object_version_number));
100
101 END Insert_Row;
102
103
104
105 -- ========================================================
106 --
107 -- NAME
108 -- Insert_Row
109 --
110 -- PURPOSE Insert row for Release 12 Volume Offers
111 --
112 -- NOTES
113 --
114 -- HISTORY
115 --
116 -- ========================================================
117 PROCEDURE Insert_Row(
118 px_off_discount_product_id IN OUT NOCOPY NUMBER,
119 p_parent_off_disc_prod_id IN NUMBER,
120 p_product_level VARCHAR2,
121 p_product_id NUMBER,
122 p_excluder_flag VARCHAR2,
123 p_uom_code VARCHAR2,
124 p_start_date_active DATE,
125 p_end_date_active DATE,
126 p_offer_discount_line_id NUMBER,
127 p_offer_id NUMBER,
128 p_creation_date DATE,
129 p_created_by NUMBER,
130 p_last_update_date DATE,
131 p_last_updated_by NUMBER,
132 p_last_update_login NUMBER,
133 p_product_context VARCHAR2,
134 p_product_attribute VARCHAR2,
135 p_product_attr_value VARCHAR2,
136 p_apply_discount_flag VARCHAR2,
137 p_include_volume_flag VARCHAR2,
138 px_object_version_number IN OUT NOCOPY NUMBER)
139
140 IS
141 x_rowid VARCHAR2(30);
142
143
144 BEGIN
145
146
147 px_object_version_number := nvl(px_object_version_number, 1);
148
149
150 INSERT INTO ozf_offer_discount_products(
151 off_discount_product_id,
152 parent_off_disc_prod_id,
153 product_level,
154 product_id,
155 excluder_flag,
156 uom_code,
157 start_date_active,
158 end_date_active,
159 offer_discount_line_id,
160 offer_id,
161 creation_date,
162 created_by,
163 last_update_date,
164 last_updated_by,
165 last_update_login,
166 product_context,
167 product_attribute,
168 product_attr_value,
169 apply_discount_flag,
170 include_volume_flag,
171 object_version_number
172 ) VALUES (
173 DECODE( px_off_discount_product_id, FND_API.G_MISS_NUM, NULL, px_off_discount_product_id),
174 DECODE( p_parent_off_disc_prod_id, FND_API.G_MISS_NUM, NULL, p_parent_off_disc_prod_id),
175 DECODE( p_product_level, FND_API.g_miss_char, NULL, p_product_level),
176 DECODE( p_product_id, FND_API.G_MISS_NUM, NULL, p_product_id),
177 DECODE( p_excluder_flag, FND_API.g_miss_char, NULL, p_excluder_flag),
178 DECODE( p_uom_code, FND_API.g_miss_char, NULL, p_uom_code),
179 DECODE( p_start_date_active, FND_API.G_MISS_DATE, to_date(NULL), p_start_date_active),
180 DECODE( p_end_date_active, FND_API.G_MISS_DATE, to_date(NULL), p_end_date_active),
181 DECODE( p_offer_discount_line_id, FND_API.G_MISS_NUM, NULL, p_offer_discount_line_id),
182 DECODE( p_offer_id, FND_API.G_MISS_NUM, NULL, p_offer_id),
183 DECODE( p_creation_date, FND_API.G_MISS_DATE, SYSDATE, p_creation_date),
184 DECODE( p_created_by, FND_API.G_MISS_NUM, FND_GLOBAL.USER_ID, p_created_by),
185 DECODE( p_last_update_date, FND_API.G_MISS_DATE, SYSDATE, p_last_update_date),
186 DECODE( p_last_updated_by, FND_API.G_MISS_NUM, FND_GLOBAL.USER_ID, p_last_updated_by),
187 DECODE( p_last_update_login, FND_API.G_MISS_NUM, FND_GLOBAL.CONC_LOGIN_ID, p_last_update_login),
188 DECODE( p_product_context, FND_API.g_miss_char, NULL, p_product_context),
189 DECODE( p_product_attribute, FND_API.g_miss_char, NULL, p_product_attribute),
190 DECODE( p_product_attr_value, FND_API.g_miss_char, NULL, p_product_attr_value),
191 DECODE( p_apply_discount_flag, FND_API.g_miss_char, NULL, p_apply_discount_flag),
192 DECODE( p_include_volume_flag, FND_API.g_miss_char, NULL, p_include_volume_flag),
193 DECODE( px_object_version_number, FND_API.G_MISS_NUM, 1, px_object_version_number)
194 );
195
196 END Insert_Row;
197
198
199
200 -- ========================================================
201 --
202 -- NAME
203 -- Update_Row
204 --
205 -- PURPOSE
206 --
207 -- NOTES
208 --
209 -- HISTORY
210 --
211 -- ========================================================
212 PROCEDURE Update_Row(
213 p_off_discount_product_id NUMBER,
214 p_parent_off_disc_prod_id NUMBER,
215 p_product_level VARCHAR2,
216 p_product_id NUMBER,
217 p_excluder_flag VARCHAR2,
218 p_uom_code VARCHAR2,
219 p_start_date_active DATE,
220 p_end_date_active DATE,
221 p_offer_discount_line_id NUMBER,
222 p_offer_id NUMBER,
223 p_last_update_date DATE,
224 p_last_updated_by NUMBER,
225 p_last_update_login NUMBER,
226 p_object_version_number IN NUMBER)
227
228 IS
229 BEGIN
230 Update ozf_offer_discount_products
231 SET
232 off_discount_product_id = DECODE( p_off_discount_product_id, null, off_discount_product_id, FND_API.G_MISS_NUM, null, p_off_discount_product_id),
233 parent_off_disc_prod_id = DECODE( p_parent_off_disc_prod_id, null, parent_off_disc_prod_id, FND_API.G_MISS_NUM, null, p_parent_off_disc_prod_id),
234 product_level = DECODE( p_product_level, null, product_level, FND_API.g_miss_char, null, p_product_level),
235 product_id = DECODE( p_product_id, null, product_id, FND_API.G_MISS_NUM, null, p_product_id),
236 excluder_flag = DECODE( p_excluder_flag, null, excluder_flag, FND_API.g_miss_char, null, p_excluder_flag),
237 uom_code = DECODE( p_uom_code, null, uom_code, FND_API.g_miss_char, null, p_uom_code),
241 offer_id = DECODE( p_offer_id, null, offer_id, FND_API.G_MISS_NUM, null, p_offer_id),
238 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),
239 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),
240 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),
242 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),
243 last_updated_by = DECODE( p_last_updated_by, null, last_updated_by, FND_API.G_MISS_NUM, null, p_last_updated_by),
244 last_update_login = DECODE( p_last_update_login, null, last_update_login, FND_API.G_MISS_NUM, null, p_last_update_login),
245 object_version_number = nvl(p_object_version_number,0) + 1
246 WHERE off_discount_product_id = p_off_discount_product_id
247 AND object_version_number = p_object_version_number;
248
249
250 IF (SQL%NOTFOUND) THEN
251 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
252 END IF;
253
254
255 END Update_Row;
256
257
258
259 -- ========================================================
260 --
261 -- NAME
262 -- Update_Row
263 --
264 -- PURPOSE Update row for Release 12 Volume Offers
265 --
266 -- NOTES
267 --
268 -- HISTORY
269 --
270 -- ========================================================
271 PROCEDURE Update_Row(
272 p_off_discount_product_id NUMBER,
273 p_parent_off_disc_prod_id NUMBER,
274 p_product_level VARCHAR2,
275 p_product_id NUMBER,
276 p_excluder_flag VARCHAR2,
277 p_uom_code VARCHAR2,
278 p_start_date_active DATE,
279 p_end_date_active DATE,
280 p_offer_discount_line_id NUMBER,
281 p_offer_id NUMBER,
282 p_last_update_date DATE,
283 p_last_updated_by NUMBER,
284 p_last_update_login NUMBER,
285 p_product_context VARCHAR2,
286 p_product_attribute VARCHAR2,
287 p_product_attr_value VARCHAR2,
288 p_apply_discount_flag VARCHAR2,
289 p_include_volume_flag VARCHAR2,
290 p_object_version_number IN NUMBER)
291
292 IS
293 BEGIN
294 Update ozf_offer_discount_products
295 SET
296 off_discount_product_id = DECODE( p_off_discount_product_id, null, off_discount_product_id, FND_API.G_MISS_NUM, null, p_off_discount_product_id),
297 parent_off_disc_prod_id = DECODE( p_parent_off_disc_prod_id, null, parent_off_disc_prod_id, FND_API.G_MISS_NUM, null, p_parent_off_disc_prod_id),
298 product_level = DECODE( p_product_level, null, product_level, FND_API.g_miss_char, null, p_product_level),
299 product_id = DECODE( p_product_id, null, product_id, FND_API.G_MISS_NUM, null, p_product_id),
300 excluder_flag = DECODE( p_excluder_flag, null, excluder_flag, FND_API.g_miss_char, null, p_excluder_flag),
301 uom_code = DECODE( p_uom_code, null, uom_code, FND_API.g_miss_char, null, p_uom_code),
302 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),
303 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),
304 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),
305 offer_id = DECODE( p_offer_id, null, offer_id, FND_API.G_MISS_NUM, null, p_offer_id),
306 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),
307 last_updated_by = DECODE( p_last_updated_by, null, last_updated_by, FND_API.G_MISS_NUM, null, p_last_updated_by),
308 last_update_login = DECODE( p_last_update_login, null, last_update_login, FND_API.G_MISS_NUM, null, p_last_update_login),
309 product_context = DECODE( p_product_context, null, product_context, FND_API.g_miss_char, null, p_product_context),
310 product_attribute = DECODE( p_product_attribute, null, product_attribute, FND_API.g_miss_char, null, p_product_attribute),
311 product_attr_value = DECODE( p_product_attr_value, null, product_attr_value, FND_API.g_miss_char, null, p_product_attr_value),
312 apply_discount_flag = DECODE( p_apply_discount_flag, null, apply_discount_flag, FND_API.g_miss_char, null, p_apply_discount_flag),
313 include_volume_flag = DECODE( p_include_volume_flag, null, include_volume_flag, FND_API.g_miss_char, null, p_include_volume_flag),
314 object_version_number = nvl(p_object_version_number,0) + 1
315 WHERE off_discount_product_id = p_off_discount_product_id
316 AND object_version_number = p_object_version_number;
317
318
319 IF (SQL%NOTFOUND) THEN
320 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
321 END IF;
322
323
324 END Update_Row;
325
326
327
328 -- ========================================================
329 --
330 -- NAME
331 -- Delete_Row
332 --
333 -- PURPOSE
334 --
335 -- NOTES
336 --
337 -- HISTORY
338 --
339 -- ========================================================
340 PROCEDURE Delete_Row(
341 p_off_discount_product_id NUMBER,
342 p_object_version_number NUMBER)
343 IS
344 BEGIN
345 DELETE FROM ozf_offer_discount_products
346 WHERE off_discount_product_id = p_off_discount_product_id
347 AND object_version_number = p_object_version_number;
348 If (SQL%NOTFOUND) then
349 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
350 End If;
351 END Delete_Row ;
352
353
354 PROCEDURE Delete_Product(
355 p_offer_discount_line_id NUMBER
356 )
357 IS
358 BEGIN
359 DELETE FROM ozf_offer_discount_products
360 WHERE offer_discount_line_id = p_offer_discount_line_id;
361 If (SQL%NOTFOUND) then
362 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
363 End If;
364
365 END Delete_Product;
366
367
368 -- ========================================================
369 --
370 -- NAME
371 -- Lock_Row
372 --
373 -- PURPOSE
374 --
375 -- NOTES
376 --
377 -- HISTORY
378 --
379 -- ========================================================
380 PROCEDURE Lock_Row(
381 p_off_discount_product_id NUMBER,
382 p_object_version_number NUMBER)
383 IS
384 CURSOR C IS
385 SELECT *
386 FROM ozf_offer_discount_products
387 WHERE off_discount_product_id = p_off_discount_product_id
388 AND object_version_number = p_object_version_number
389 FOR UPDATE OF off_discount_product_id NOWAIT;
390 Recinfo C%ROWTYPE;
391 BEGIN
392
393 OPEN c;
394 FETCH c INTO Recinfo;
395 IF (c%NOTFOUND) THEN
396 CLOSE c;
397 AMS_Utility_PVT.error_message ('AMS_API_RECORD_NOT_FOUND');
398 RAISE FND_API.g_exc_error;
399 END IF;
400 CLOSE c;
401 END Lock_Row;
402
403
404
405 END OZF_Create_Ozf_Prod_Line_PKG;