[Home] [Help]
PACKAGE BODY: APPS.OZF_PROMOTIONAL_OFFERS_PKG
Source
1 PACKAGE BODY OZF_Promotional_Offers_PKG as
2 /* $Header: ozftopob.pls 120.5.12020000.2 2013/01/30 08:21:33 apyadav ship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- OZF_Promotional_Offers_PKG
7 -- Purpose
8 --
9 -- History
10 -- MAY-17-2002 julou modified. See bug 2380113
11 -- removed created_by and creation_date from update api
12 --
13 -- NOTE
14 --
15 -- End of Comments
16 -- 17-Oct-2002 RSSHARMA added last_recal_date and buyer_name
17 -- 24-Oct-2002 RSSHARMA Added date_qualifier_profile_value
18 -- Tue May 03 2005:3/35 PM RSSHARMA Added sales_method_flag field
19 -- Wed Apr 05 2006:2/30 PM RSSHARMA Fixed bug # 5142859.Added fund_request_curr_code to insert_row
20 -- Mon Apr 24 2006:2/28 PM RSSHARMA Fixed bug # 5181359. Do not let in null values into budget_offer_yn column.
21 -- for null or g_miss values sent in put in N into the table
22 -- Thu Mar 03 2011 nirprasa ER # 11806714. Added percent_discount column.
23 --30-JAN-2013 apyadav Bug 14138664 - GSIAV: COPY OFFER DO NOT HAVE FLEXIBILITY TO CHOOSE THE CURRENCY
24 -- ===============================================================
25
26
27 G_PKG_NAME CONSTANT VARCHAR2(30):= 'OZF_Promotional_Offers_PKG';
28 G_FILE_NAME CONSTANT VARCHAR2(12) := 'ozftopob.pls';
29
30
31 ----------------------------------------------------------
32 ---- MEDIA ----
33 ----------------------------------------------------------
34
35 -- ========================================================
36 --
37 -- NAME
38 -- createInsertBody
39 --
40 -- PURPOSE
41 --
42 -- NOTES
43 --
44 -- HISTORY
45 --
46 -- ========================================================
47 PROCEDURE Insert_Row(
48 px_offer_id IN OUT NOCOPY NUMBER,
49 p_qp_list_header_id NUMBER,
50 p_offer_type VARCHAR2,
51 p_offer_code VARCHAR2,
52 p_activity_media_id NUMBER,
53 p_reusable VARCHAR2,
54 p_user_status_id NUMBER,
55 p_owner_id NUMBER,
56 p_wf_item_key VARCHAR2,
57 p_customer_reference VARCHAR2,
58 p_buying_group_contact_id NUMBER,
59 p_last_update_date DATE,
60 p_last_updated_by NUMBER,
61 p_creation_date DATE,
62 p_created_by NUMBER,
63 p_last_update_login NUMBER,
64 px_object_version_number IN OUT NOCOPY NUMBER,
65 p_perf_date_from DATE,
66 p_perf_date_to DATE,
67 p_status_code VARCHAR2,
68 p_status_date DATE,
69 p_modifier_level_code VARCHAR2,
70 p_order_value_discount_type VARCHAR2,
71 p_offer_amount NUMBER,
72 p_lumpsum_amount NUMBER,
73 p_lumpsum_payment_type VARCHAR2,
74 p_custom_setup_id NUMBER,
75 p_security_group_id NUMBER,
76 p_budget_amount_tc NUMBER,
77 p_budget_amount_fc NUMBER,
78 p_transaction_currency_Code VARCHAR2,
79 p_functional_currency_code VARCHAR2,
80 p_distribution_type VARCHAR2,
81 p_qualifier_id NUMBER,
82 p_qualifier_type VARCHAR2,
83 p_account_closed_flag VARCHAR2,
84 p_budget_offer_yn VARCHAR2,
85 p_break_type VARCHAR2,
86 p_retroactive VARCHAR2,
87 p_volume_offer_type VARCHAR2,
88 p_confidential_flag VARCHAR2,
89 p_budget_source_type VARCHAR2,
90 p_budget_source_id NUMBER,
91 p_source_from_parent VARCHAR2,
92 p_buyer_name VARCHAR2,
93 p_last_recal_date DATE,
94 p_date_qualifier VARCHAR2,
95 p_autopay_flag VARCHAR2,
96 p_autopay_days NUMBER,
97 p_autopay_method VARCHAR2,
98 p_autopay_party_attr VARCHAR2,
99 p_autopay_party_id NUMBER,
100 p_tier_level VARCHAR2,
101 p_na_rule_header_id NUMBER,
102 p_beneficiary_account_id NUMBER,
103 p_sales_method_flag VARCHAR2,
104 p_org_id NUMBER,
105 p_fund_request_curr_code VARCHAR2,
106 p_percent_discount NUMBER
107 )
108
109 IS
110 x_rowid VARCHAR2(30);
111
112
113 BEGIN
114
115
116 px_object_version_number := 1;
117
118
119 INSERT INTO OZF_OFFERS(
120 offer_id,
121 qp_list_header_id,
122 offer_type,
123 offer_code,
124 activity_media_id,
125 reusable,
126 user_status_id,
127 owner_id,
128 wf_item_key,
129 customer_reference,
130 buying_group_contact_id,
131 last_update_date,
132 last_updated_by,
133 creation_date,
134 created_by,
135 last_update_login,
136 object_version_number,
137 perf_date_from,
138 perf_date_to,
139 status_code,
140 status_date,
141 modifier_level_code,
142 order_value_discount_type,
143 offer_amount,
144 lumpsum_amount,
145 lumpsum_payment_type,
146 custom_setup_id,
147 budget_amount_tc,
148 budget_amount_fc,
149 transaction_currency_Code,
150 functional_currency_code,
151 distribution_type,
152 qualifier_id,
153 qualifier_type,
154 account_closed_flag,
155 budget_offer_yn,
156 break_type,
157 retroactive,
158 volume_offer_type,
159 confidential_flag,
160 budget_source_type,
161 budget_source_id ,
162 source_from_parent,
163 buyer_name,
164 last_recal_date,
165 date_qualifier_profile_value,
166 autopay_flag,
167 autopay_days,
168 autopay_method,
169 autopay_party_attr,
170 autopay_party_id,
171 tier_level,
172 na_rule_header_id,
173 beneficiary_account_id,
174 sales_method_flag,
175 org_id,
176 fund_request_curr_code,
177 percent_discount
178 )
179 VALUES (
180 DECODE( px_offer_id, FND_API.g_miss_num, NULL, px_offer_id),
181 DECODE( p_qp_list_header_id, FND_API.g_miss_num, NULL, p_qp_list_header_id),
182 DECODE( p_offer_type, FND_API.g_miss_char, NULL, p_offer_type),
183 DECODE( p_offer_code, FND_API.g_miss_char, NULL, p_offer_code),
184 DECODE( p_activity_media_id, FND_API.g_miss_num, NULL, p_activity_media_id),
185 DECODE( p_reusable, FND_API.g_miss_char, NULL, p_reusable),
186 DECODE( p_user_status_id, FND_API.g_miss_num, NULL, p_user_status_id),
187 DECODE( p_owner_id, FND_API.g_miss_num, NULL, p_owner_id),
188 DECODE( p_wf_item_key, FND_API.g_miss_char, NULL, p_wf_item_key),
189 DECODE( p_customer_reference, FND_API.g_miss_char, NULL, p_customer_reference),
190 DECODE( p_buying_group_contact_id, FND_API.g_miss_num, NULL, p_buying_group_contact_id),
191 DECODE( p_last_update_date, FND_API.g_miss_date, to_date(NULL), p_last_update_date),
192 DECODE( p_last_updated_by, FND_API.g_miss_num, NULL, p_last_updated_by),
193 DECODE( p_creation_date, FND_API.g_miss_date, to_date(NULL), p_creation_date),
194 DECODE( p_created_by, FND_API.g_miss_num, NULL, p_created_by),
195 DECODE( p_last_update_login, FND_API.g_miss_num, NULL, p_last_update_login),
196 DECODE( px_object_version_number, FND_API.g_miss_num, NULL, px_object_version_number),
197 DECODE( p_perf_date_from, FND_API.g_miss_date, to_date(NULL), p_perf_date_from),
198 DECODE( p_perf_date_to, FND_API.g_miss_date, to_date(NULL), p_perf_date_to),
199 DECODE( p_status_code, FND_API.g_miss_char, NULL, p_status_code),
200 DECODE( p_status_date, FND_API.g_miss_date, to_date(NULL), p_status_date),
201 DECODE( p_modifier_level_code, FND_API.g_miss_char, NULL, p_modifier_level_code),
202 DECODE( p_order_value_discount_type, FND_API.g_miss_char, NULL, p_order_value_discount_type),
203 DECODE( p_offer_amount, FND_API.g_miss_num, NULL, p_offer_amount),
204 DECODE( p_lumpsum_amount, FND_API.g_miss_num, NULL, p_lumpsum_amount),
205 DECODE( p_lumpsum_payment_Type, FND_API.g_miss_char, NULL, p_lumpsum_payment_type),
206 DECODE( p_custom_setup_id, FND_API.g_miss_num, NULL, p_custom_setup_id),
207 DECODE( p_budget_amount_tc, FND_API.g_miss_num, NULL, p_budget_amount_tc),
208 DECODE( p_budget_amount_fc, FND_API.g_miss_num, NULL, p_budget_amount_fc),
209 DECODE( p_transaction_currency_Code, FND_API.g_miss_char, NULL, p_transaction_currency_Code),
210 DECODE( p_functional_currency_code, FND_API.g_miss_char, NULL, p_functional_currency_code),
211 DECODE( p_distribution_type, FND_API.g_miss_char, NULL, p_distribution_type),
212 DECODE( p_qualifier_id, FND_API.g_miss_num, NULL, p_qualifier_id),
213 DECODE( p_qualifier_type, FND_API.g_miss_char, NULL, p_qualifier_type),
214 DECODE( p_account_closed_flag, FND_API.g_miss_char, NULL, p_account_closed_flag),
215 DECODE( p_budget_offer_yn, 'Y','Y', 'N'),
216 DECODE( p_break_type, FND_API.g_miss_char, NULL, p_break_type),
217 DECODE( p_retroactive, FND_API.g_miss_char, NULL, p_retroactive),
218 DECODE( p_volume_offer_type, FND_API.g_miss_char, NULL, p_volume_offer_type),
219 DECODE( p_confidential_flag, FND_API.g_miss_char, NVL(FND_PROFILE.value('OZF_OFFR_CONFIDENTIAL_FLAG'), 'N'), NULL, NVL(FND_PROFILE.value('OZF_OFFR_CONFIDENTIAL_FLAG'), 'N'), p_confidential_flag),
220 DECODE(p_budget_source_type, FND_API.g_miss_char,NULL,p_budget_source_type),
221 DECODE(p_budget_source_id , FND_API.g_miss_num,NULL,p_budget_source_id),
222 DECODE(p_source_from_parent , FND_API.g_miss_char,NULL,p_source_from_parent),
223 DECODE(p_buyer_name , FND_API.g_miss_char,NULL,p_buyer_name),
224 DECODE(p_last_recal_date , FND_API.g_miss_date,to_date(NULL),p_last_recal_date),
225 DECODE(p_date_qualifier , FND_API.g_miss_char,NULL,p_date_qualifier),
226 DECODE(p_autopay_flag , FND_API.g_miss_char,NULL,p_autopay_flag),
227 DECODE(p_autopay_days , FND_API.g_miss_num,NULL,p_autopay_days),
228 DECODE(p_autopay_method , FND_API.g_miss_char,NULL,p_autopay_method),
229 DECODE(p_autopay_party_attr , FND_API.g_miss_char,NULL,p_autopay_party_attr),
230 DECODE(p_autopay_party_id , FND_API.g_miss_num,NULL,p_autopay_party_id),
231 DECODE(p_tier_level,FND_API.g_miss_char,NULL,p_tier_level),
232 DECODE(p_na_rule_header_id,FND_API.g_miss_num,NULL,p_na_rule_header_id),
233 DECODE(p_beneficiary_account_id,FND_API.g_miss_num,NULL,p_beneficiary_account_id),
234 DECODE(p_sales_method_flag,FND_API.g_miss_char,NULL,p_sales_method_flag),
235 DECODE(p_org_id,FND_API.g_miss_num,NULL,p_org_id),
236 DECODE(p_fund_request_curr_code, FND_API.G_MISS_CHAR,FND_PROFILE.VALUE('JTF_PROFILE_DEFAULT_CURRENCY'),null, FND_PROFILE.VALUE('JTF_PROFILE_DEFAULT_CURRENCY'),p_fund_request_curr_code),
237 DECODE(p_percent_discount,FND_API.g_miss_num,NULL,p_percent_discount)
238 );
239 END Insert_Row;
240
241
242 ----------------------------------------------------------
243 ---- MEDIA ----
244 ----------------------------------------------------------
245
246 -- ========================================================
247 --
248 -- NAME
249 -- createUpdateBody
250 --
251 -- PURPOSE
252 --
253 -- NOTES
254 --
255 -- HISTORY
256 --
257 -- ========================================================
258 PROCEDURE Update_Row(
259 p_offer_id NUMBER,
260 p_qp_list_header_id NUMBER,
261 p_offer_type VARCHAR2,
262 p_offer_code VARCHAR2,
263 p_activity_media_id NUMBER,
264 p_reusable VARCHAR2,
265 p_user_status_id NUMBER,
266 p_owner_id NUMBER,
267 p_wf_item_key VARCHAR2,
268 p_customer_reference VARCHAR2,
269 p_buying_group_contact_id NUMBER,
270 p_last_update_date DATE,
271 p_last_updated_by NUMBER,
272 p_last_update_login NUMBER,
273 p_object_version_number NUMBER,
274 p_perf_date_from DATE,
275 p_perf_date_to DATE,
276 p_status_code VARCHAR2,
277 p_status_date DATE,
278 p_modifier_level_code VARCHAR2,
279 p_order_value_discount_type VARCHAR2,
280 p_offer_amount NUMBER,
281 p_lumpsum_amount NUMBER,
282 p_lumpsum_payment_type VARCHAR2,
283 p_custom_setup_id NUMBER,
284 p_security_group_id NUMBER,
285 p_budget_amount_tc NUMBER,
286 p_budget_amount_fc NUMBER,
287 p_transaction_currency_Code VARCHAR2,
288 p_functional_currency_code VARCHAR2,
289 p_distribution_type VARCHAR2,
290 p_qualifier_id NUMBER,
291 p_qualifier_type VARCHAR2,
292 p_account_closed_flag VARCHAR2,
293 p_budget_offer_yn VARCHAR2,
294 p_break_type VARCHAR2,
295 p_retroactive VARCHAR2,
296 p_volume_offer_type VARCHAR2,
297 p_confidential_flag VARCHAR2,
298 p_budget_source_type VARCHAR2,
299 p_budget_source_id NUMBER,
300 p_source_from_parent VARCHAR2,
301 p_buyer_name VARCHAR2,
302 p_last_recal_date DATE,
303 p_date_qualifier VARCHAR2,
304 p_autopay_flag VARCHAR2,
305 p_autopay_days NUMBER,
306 p_autopay_method VARCHAR2,
307 p_autopay_party_attr VARCHAR2,
308 p_autopay_party_id NUMBER,
309 p_tier_level VARCHAR2,
310 p_na_rule_header_id NUMBER,
311 p_beneficiary_account_id NUMBER,
312 p_sales_method_flag VARCHAR2,
313 p_org_id NUMBER,
314 p_start_date DATE,
315 p_percent_discount NUMBER,
316 p_fund_request_curr_code VARCHAR2
317 )
318
319 IS
320 BEGIN
321
322 Update OZF_OFFERS
323 SET
324 offer_id = DECODE( p_offer_id, FND_API.g_miss_num, offer_id, p_offer_id),
325 qp_list_header_id = DECODE( p_qp_list_header_id, FND_API.g_miss_num, qp_list_header_id, p_qp_list_header_id),
326 offer_type = DECODE( p_offer_type, FND_API.g_miss_char, offer_type, p_offer_type),
327 offer_code = DECODE( p_offer_code, FND_API.g_miss_char, offer_code, p_offer_code),
328 activity_media_id = DECODE( p_activity_media_id, FND_API.g_miss_num, activity_media_id, p_activity_media_id),
329 reusable = DECODE( p_reusable, FND_API.g_miss_char, reusable, p_reusable),
330 user_status_id = DECODE( p_user_status_id, FND_API.g_miss_num, user_status_id, p_user_status_id),
331 owner_id = DECODE( p_owner_id, FND_API.g_miss_num, owner_id, p_owner_id),
332 wf_item_key = DECODE( p_wf_item_key, FND_API.g_miss_char, wf_item_key, p_wf_item_key),
333 customer_reference = DECODE( p_customer_reference, FND_API.g_miss_char, customer_reference, p_customer_reference),
334 buying_group_contact_id = DECODE( p_buying_group_contact_id, FND_API.g_miss_num, buying_group_contact_id, p_buying_group_contact_id),
335 last_update_date = DECODE( p_last_update_date, FND_API.g_miss_date, last_update_date, p_last_update_date),
336 last_updated_by = DECODE( p_last_updated_by, FND_API.g_miss_num, last_updated_by, p_last_updated_by),
337 last_update_login = DECODE( p_last_update_login, FND_API.g_miss_num, last_update_login, p_last_update_login),
338 object_version_number = DECODE( p_object_version_number, FND_API.g_miss_num, object_version_number +1, p_object_version_number+1),
339 perf_date_from = DECODE( p_perf_date_from, FND_API.g_miss_date, perf_date_from, p_perf_date_from),
340 perf_date_to = DECODE( p_perf_date_to, FND_API.g_miss_date, perf_date_to, p_perf_date_to),
341 status_code = DECODE( p_status_code, FND_API.g_miss_char, status_code, p_status_code),
342 status_date = DECODE( p_status_date, FND_API.g_miss_date, status_date, p_status_date),
343 modifier_level_code = DECODE( p_modifier_level_code, FND_API.g_miss_char, modifier_level_code, p_modifier_level_code),
344 order_value_discount_type = DECODE( p_order_value_discount_type, FND_API.g_miss_char, order_value_discount_type, p_order_value_discount_type),
345 offer_amount = DECODE( p_offer_amount, FND_API.g_miss_num, offer_amount, p_offer_amount),
346 lumpsum_amount = DECODE( p_lumpsum_amount, FND_API.g_miss_num, lumpsum_amount, p_lumpsum_amount),
347 lumpsum_payment_type = DECODE( p_lumpsum_payment_type, FND_API.g_miss_char, lumpsum_payment_type, p_lumpsum_payment_type),
348 custom_setup_id = DECODE( p_custom_setup_id, FND_API.g_miss_num, custom_setup_id, p_custom_setup_id),
349 budget_amount_tc = DECODE( p_budget_amount_tc, FND_API.g_miss_num, budget_amount_tc, p_budget_amount_tc),
350 budget_amount_fc = DECODE( p_budget_amount_fc, FND_API.g_miss_num, budget_amount_fc, p_budget_amount_fc),
351 transaction_currency_Code = DECODE( p_transaction_currency_Code, FND_API.g_miss_char, transaction_currency_Code, p_transaction_currency_Code),
352 functional_currency_code = DECODE( p_functional_currency_code, FND_API.g_miss_char, functional_currency_code, p_functional_currency_code),
353 distribution_type = DECODE( p_distribution_type, FND_API.g_miss_char, distribution_type, p_distribution_type),
354 qualifier_id = DECODE( p_qualifier_id, FND_API.g_miss_num, qualifier_id, p_qualifier_id),
355 qualifier_type = DECODE( p_qualifier_type, FND_API.g_miss_char, qualifier_type, p_qualifier_type),
356 account_closed_flag = DECODE( p_account_closed_flag, FND_API.g_miss_char, account_closed_flag, p_account_closed_flag),
357 budget_offer_yn = DECODE( p_budget_offer_yn,'Y','Y', FND_API.g_miss_char, budget_offer_yn, 'N'),
358 break_type = DECODE( p_break_type, FND_API.g_miss_char, break_type, p_break_type),
359 retroactive = DECODE( p_retroactive, FND_API.g_miss_char, retroactive, p_retroactive),
360 volume_offer_type = DECODE( p_volume_offer_type, FND_API.g_miss_char, volume_offer_type, p_volume_offer_type),
361 confidential_flag = DECODE( p_confidential_flag, FND_API.g_miss_char, confidential_flag, NULL, NVL(FND_PROFILE.value('OZF_OFFR_CONFIDENTIAL_FLAG'), 'N'), p_confidential_flag),
362 budget_source_type = DECODE(p_budget_source_type,FND_API.g_miss_char,budget_source_type,p_budget_source_type),
363 budget_source_id = DECODE(p_budget_source_id , FND_API.g_miss_num,budget_source_id,p_budget_source_id),
364 source_from_parent = DECODE(p_source_from_parent , FND_API.g_miss_char,source_from_parent,p_source_from_parent),
365 buyer_name = DECODE(p_buyer_name , FND_API.g_miss_char,buyer_name,p_buyer_name),
366 last_recal_date = DECODE(p_last_recal_date , FND_API.g_miss_date,last_recal_date,p_last_recal_date),
367 date_qualifier_profile_value = DECODE(p_date_qualifier , FND_API.g_miss_char, date_qualifier_profile_value, p_date_qualifier),
368 autopay_flag = DECODE(p_autopay_flag , FND_API.g_miss_char,autopay_flag,p_autopay_flag),
369 autopay_days = DECODE(p_autopay_days , FND_API.g_miss_num,autopay_days,p_autopay_days),
370 autopay_method = DECODE(p_autopay_method , FND_API.g_miss_char,autopay_method,p_autopay_method),
371 autopay_party_attr = DECODE(p_autopay_party_attr , FND_API.g_miss_char,autopay_party_attr,p_autopay_party_attr),
372 autopay_party_id = DECODE(p_autopay_party_id , FND_API.g_miss_num,autopay_party_id,p_autopay_party_id),
373 tier_level = DECODE(p_tier_level , FND_API.g_miss_char , tier_level,p_tier_level),
374 na_rule_header_id = DECODE(p_na_rule_header_id , FND_API.g_miss_num , na_rule_header_id,p_na_rule_header_id),
375 beneficiary_account_id = DECODE(p_beneficiary_account_id , FND_API.g_miss_num , beneficiary_account_id,p_beneficiary_account_id),
376 sales_method_flag = DECODE(p_sales_method_flag , FND_API.g_miss_char , sales_method_flag,p_sales_method_flag),
377 org_id = DECODE(p_org_id , FND_API.g_miss_char , org_id,p_org_id),
378 start_date = DECODE( p_start_date, FND_API.g_miss_date, start_date, p_start_date),
379 percent_discount = DECODE(p_percent_discount,FND_API.g_miss_num,NULL,p_percent_discount),
380 --For Bug 14138664
381 fund_request_curr_code = DECODE(p_fund_request_curr_code, FND_API.G_MISS_CHAR,FND_PROFILE.VALUE('JTF_PROFILE_DEFAULT_CURRENCY'),null, FND_PROFILE.VALUE('JTF_PROFILE_DEFAULT_CURRENCY'),p_fund_request_curr_code)
382
383
384 WHERE OFFER_ID = p_OFFER_ID
385 AND object_version_number = p_object_version_number;
386
387 IF (SQL%NOTFOUND) THEN
388 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
389 END IF;
390 END Update_Row;
391
392
393 ----------------------------------------------------------
394 ---- MEDIA ----
395 ----------------------------------------------------------
396
397 -- ========================================================
398 --
399 -- NAME
400 -- createDeleteBody
401 --
402 -- PURPOSE
403 --
404 -- NOTES
405 --
406 -- HISTORY
407 --
408 -- ========================================================
409 PROCEDURE Delete_Row(
410 p_OFFER_ID NUMBER)
411 IS
412 BEGIN
413 DELETE FROM OZF_OFFERS
414 WHERE OFFER_ID = p_OFFER_ID;
415 If (SQL%NOTFOUND) then
416 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
417 End If;
418 END Delete_Row ;
419
420
421
422 ----------------------------------------------------------
423 ---- MEDIA ----
424 ----------------------------------------------------------
425
426 -- ========================================================
427 --
428 -- NAME
429 -- createLockBody
430 --
431 -- PURPOSE
432 --
433 -- NOTES
434 --
435 -- HISTORY
436 --
437 -- ========================================================
438 PROCEDURE Lock_Row(
439 p_offer_id NUMBER,
440 p_qp_list_header_id NUMBER,
441 p_offer_type VARCHAR2,
442 p_offer_code VARCHAR2,
443 p_activity_media_id NUMBER,
444 p_reusable VARCHAR2,
445 p_user_status_id NUMBER,
446 p_owner_id NUMBER,
447 p_wf_item_key VARCHAR2,
448 p_customer_reference VARCHAR2,
449 p_buying_group_contact_id NUMBER,
450 p_last_update_date DATE,
451 p_last_updated_by NUMBER,
452 p_creation_date DATE,
453 p_created_by NUMBER,
454 p_last_update_login NUMBER,
455 p_object_version_number NUMBER,
456 p_perf_date_from DATE,
457 p_perf_date_to DATE,
458 p_status_code VARCHAR2,
459 p_status_date DATE,
460 p_modifier_level_code VARCHAR2,
461 p_order_value_discount_type VARCHAR2,
462 p_offer_amount NUMBER,
463 p_lumpsum_amount NUMBER,
464 p_lumpsum_payment_type VARCHAR2,
465 p_custom_setup_id NUMBER,
466 p_security_group_id NUMBER,
467 p_budget_amount_tc NUMBER,
468 p_budget_amount_fc NUMBER,
469 p_transaction_currency_Code VARCHAR2,
470 p_functional_currency_code VARCHAR2,
471 p_distribution_type VARCHAR2,
472 p_qualifier_id NUMBER,
473 p_qualifier_type VARCHAR2,
474 p_account_closed_flag VARCHAR2,
475 p_budget_offer_yn VARCHAR2,
476 p_break_type VARCHAR2,
477 p_retroactive VARCHAR2,
478 p_volume_offer_type VARCHAR2,
479 p_confidential_flag VARCHAR2,
480 p_source_from_parent VARCHAR2,
481 p_buyer_name VARCHAR2,
482 p_last_recal_date DATE,
483 p_autopay_flag VARCHAR2,
484 p_autopay_days NUMBER,
485 p_autopay_method VARCHAR2,
486 p_autopay_party_attr VARCHAR2,
487 p_autopay_party_id NUMBER,
488 p_tier_level VARCHAR2,
489 p_na_rule_header_id NUMBER,
490 p_beneficiary_account_id NUMBER,
491 p_sales_method_flag VARCHAR2,
492 p_org_id NUMBER,
493 p_percent_discount NUMBER
494 )
495
496 IS
497 CURSOR C IS
498 SELECT *
499 FROM OZF_OFFERS
500 WHERE OFFER_ID = p_OFFER_ID
501 FOR UPDATE of OFFER_ID NOWAIT;
502 Recinfo C%ROWTYPE;
503 BEGIN
504 OPEN c;
505 FETCH c INTO Recinfo;
506 If (c%NOTFOUND) then
507 CLOSE c;
508 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
509 APP_EXCEPTION.RAISE_EXCEPTION;
510 END IF;
511 CLOSE C;
512 IF (
513 ( Recinfo.offer_id = p_offer_id)
514 AND ( ( Recinfo.qp_list_header_id = p_qp_list_header_id)
515 OR ( ( Recinfo.qp_list_header_id IS NULL )
516 AND ( p_qp_list_header_id IS NULL )))
517 AND ( ( Recinfo.offer_type = p_offer_type)
518 OR ( ( Recinfo.offer_type IS NULL )
519 AND ( p_offer_type IS NULL )))
520 AND ( ( Recinfo.offer_code = p_offer_code)
521 OR ( ( Recinfo.offer_code IS NULL )
522 AND ( p_offer_code IS NULL )))
523 AND ( ( Recinfo.activity_media_id = p_activity_media_id)
524 OR ( ( Recinfo.activity_media_id IS NULL )
525 AND ( p_activity_media_id IS NULL )))
526 AND ( ( Recinfo.reusable = p_reusable)
527 OR ( ( Recinfo.reusable IS NULL )
528 AND ( p_reusable IS NULL )))
529 AND ( ( Recinfo.user_status_id = p_user_status_id)
530 OR ( ( Recinfo.user_status_id IS NULL )
531 AND ( p_user_status_id IS NULL )))
532 AND ( ( Recinfo.owner_id = p_owner_id)
533 OR ( ( Recinfo.owner_id IS NULL )
534 AND ( p_owner_id IS NULL )))
535 AND ( ( Recinfo.wf_item_key = p_wf_item_key)
536 OR ( ( Recinfo.wf_item_key IS NULL )
537 AND ( p_wf_item_key IS NULL )))
538 AND ( ( Recinfo.customer_reference = p_customer_reference)
539 OR ( ( Recinfo.customer_reference IS NULL )
540 AND ( p_customer_reference IS NULL )))
541 AND ( ( Recinfo.buying_group_contact_id = p_buying_group_contact_id)
542 OR ( ( Recinfo.buying_group_contact_id IS NULL )
543 AND ( p_buying_group_contact_id IS NULL )))
544 AND ( ( Recinfo.last_update_date = p_last_update_date)
545 OR ( ( Recinfo.last_update_date IS NULL )
546 AND ( p_last_update_date IS NULL )))
547 AND ( ( Recinfo.last_updated_by = p_last_updated_by)
548 OR ( ( Recinfo.last_updated_by IS NULL )
549 AND ( p_last_updated_by IS NULL )))
550 AND ( ( Recinfo.creation_date = p_creation_date)
551 OR ( ( Recinfo.creation_date IS NULL )
552 AND ( p_creation_date IS NULL )))
553 AND ( ( Recinfo.created_by = p_created_by)
554 OR ( ( Recinfo.created_by IS NULL )
555 AND ( p_created_by IS NULL )))
556 AND ( ( Recinfo.last_update_login = p_last_update_login)
557 OR ( ( Recinfo.last_update_login IS NULL )
558 AND ( p_last_update_login IS NULL )))
559 AND ( ( Recinfo.object_version_number = p_object_version_number)
560 OR ( ( Recinfo.object_version_number IS NULL )
561 AND ( p_object_version_number IS NULL )))
562 AND ( ( Recinfo.perf_date_from = p_perf_date_from)
563 OR ( ( Recinfo.perf_date_from IS NULL )
564 AND ( p_perf_date_from IS NULL )))
565 AND ( ( Recinfo.perf_date_to = p_perf_date_to)
566 OR ( ( Recinfo.perf_date_to IS NULL )
567 AND ( p_perf_date_to IS NULL )))
568 AND ( ( Recinfo.status_code = p_status_code)
569 OR ( ( Recinfo.status_code IS NULL )
570 AND ( p_status_code IS NULL )))
571 AND ( ( Recinfo.status_date = p_status_date)
572 OR ( ( Recinfo.status_date IS NULL )
573 AND ( p_status_date IS NULL )))
574 AND ( ( Recinfo.modifier_level_code = p_modifier_level_code)
575 OR ( ( Recinfo.modifier_level_code IS NULL )
576 AND ( p_modifier_level_code IS NULL )))
577 AND ( ( Recinfo.order_value_discount_type = p_order_value_discount_type)
578 OR ( ( Recinfo.order_value_discount_type IS NULL )
579 AND ( p_order_value_discount_type IS NULL )))
580 AND ( ( Recinfo.offer_amount = p_offer_amount)
581 OR ( ( Recinfo.offer_amount IS NULL )
582 AND ( p_offer_amount IS NULL )))
583 AND ( ( Recinfo.lumpsum_amount = p_lumpsum_amount)
584 OR ( ( Recinfo.lumpsum_amount IS NULL )
585 AND ( p_lumpsum_amount IS NULL )))
586 AND ( ( Recinfo.lumpsum_payment_type = p_lumpsum_payment_type)
587 OR ( ( Recinfo.lumpsum_payment_type IS NULL )
588 AND ( p_lumpsum_payment_type IS NULL )))
589 AND ( ( Recinfo.custom_setup_id = p_custom_setup_id)
590 OR ( ( Recinfo.custom_setup_id IS NULL )
591 AND ( p_custom_setup_id IS NULL )))
592 AND ( ( Recinfo.security_group_id = p_security_group_id)
593 OR ( ( Recinfo.security_group_id IS NULL )
594 AND ( p_security_group_id IS NULL )))
595 AND ( ( Recinfo.budget_amount_tc = p_budget_amount_tc)
596 OR ( ( Recinfo.budget_amount_tc IS NULL )
597 AND ( p_budget_amount_tc IS NULL )))
598 AND ( ( Recinfo.budget_amount_fc = p_budget_amount_fc)
599 OR ( ( Recinfo.budget_amount_fc IS NULL )
600 AND ( p_budget_amount_tc IS NULL )))
601 AND ( ( Recinfo.transaction_currency_Code = p_transaction_currency_Code)
602 OR ( ( Recinfo.transaction_currency_Code IS NULL )
603 AND ( p_transaction_currency_Code IS NULL )))
604 AND ( ( Recinfo.functional_currency_code = p_functional_currency_code)
605 OR ( ( Recinfo.functional_currency_code IS NULL )
606 AND ( p_functional_currency_code IS NULL )))
607 AND ( ( Recinfo.distribution_type = p_distribution_type)
608 OR ( ( Recinfo.distribution_type IS NULL )
609 AND ( p_distribution_type IS NULL )))
610 AND ( ( Recinfo.qualifier_type = p_qualifier_type)
611 OR ( ( Recinfo.qualifier_type IS NULL )
612 AND ( p_qualifier_type IS NULL )))
613 AND ( ( Recinfo.qualifier_id = p_qualifier_id)
614 OR ( ( Recinfo.qualifier_id IS NULL )
615 AND ( p_qualifier_id IS NULL )))
616 AND ( ( Recinfo.account_closed_flag = p_account_closed_flag)
617 OR ( ( Recinfo.account_closed_flag IS NULL )
618 AND ( p_account_closed_flag IS NULL )))
619 AND ( ( Recinfo.budget_offer_yn = p_budget_offer_yn)
620 OR ( ( Recinfo.budget_offer_yn IS NULL )
621 AND ( p_budget_offer_yn IS NULL )))
622 AND ( ( Recinfo.break_type = p_break_type)
623 OR ( ( Recinfo.break_type IS NULL )
624 AND ( p_break_type IS NULL )))
625 AND ( ( Recinfo.retroactive = p_retroactive)
626 OR ( ( Recinfo.retroactive IS NULL )
627 AND ( p_retroactive IS NULL )))
628 AND ( ( Recinfo.volume_offer_type = p_volume_offer_type)
629 OR ( ( Recinfo.volume_offer_type IS NULL )
630 AND ( p_volume_offer_type IS NULL )))
631 AND ( ( Recinfo.confidential_flag = p_confidential_flag)
632 OR ( ( Recinfo.confidential_flag IS NULL )
633 AND ( p_confidential_flag IS NULL )))
634 AND ( ( Recinfo.source_from_parent = p_source_from_parent)
635 OR ( ( Recinfo.source_from_parent IS NULL )
636 AND ( p_source_from_parent IS NULL )))
637 AND ( ( Recinfo.buyer_name = p_buyer_name)
638 OR ( ( Recinfo.buyer_name IS NULL )
639 AND ( p_buyer_name IS NULL )))
640 AND ( ( Recinfo.last_recal_date = p_last_recal_date)
641 OR ( ( Recinfo.last_recal_date IS NULL )
642 AND ( p_last_recal_date IS NULL )))
643 AND ( ( Recinfo.autopay_flag = p_autopay_flag)
644 OR ( ( Recinfo.autopay_flag IS NULL )
645 AND ( p_autopay_flag IS NULL )))
646 AND ( ( Recinfo.autopay_days = p_autopay_days)
647 OR ( ( Recinfo.autopay_days IS NULL )
648 AND ( p_autopay_days IS NULL )))
649 AND ( ( Recinfo.autopay_method = p_autopay_method)
650 OR ( ( Recinfo.autopay_method IS NULL )
651 AND ( p_autopay_method IS NULL )))
652 AND ( ( Recinfo.autopay_party_attr = p_autopay_party_attr)
653 OR ( ( Recinfo.autopay_party_attr IS NULL )
654 AND ( p_autopay_party_attr IS NULL )))
655 AND ( ( Recinfo.autopay_party_id = p_autopay_party_id)
656 OR ( ( Recinfo.autopay_party_id IS NULL )
657 AND ( p_autopay_party_id IS NULL )))
658 AND ( ( Recinfo.tier_level = p_tier_level)
659 OR ( ( Recinfo.tier_level IS NULL )
660 AND ( p_tier_level IS NULL )))
661 AND ( ( Recinfo.na_rule_header_id = p_na_rule_header_id)
662 OR ( ( Recinfo.na_rule_header_id IS NULL )
663 AND ( p_na_rule_header_id IS NULL )))
664 AND ( ( Recinfo.beneficiary_account_id = p_beneficiary_account_id)
665 OR ( ( Recinfo.beneficiary_account_id IS NULL )
666 AND ( p_beneficiary_account_id IS NULL )))
667 AND ( ( Recinfo.sales_method_flag = p_sales_method_flag)
668 OR ( ( Recinfo.sales_method_flag IS NULL )
669 AND ( p_sales_method_flag IS NULL )))
670 AND ( ( Recinfo.org_id = p_org_id)
671 OR ( ( Recinfo.org_id IS NULL )
672 AND ( p_org_id IS NULL )))
673 AND ( ( Recinfo.percent_discount = p_percent_discount)
674 OR ( ( Recinfo.percent_discount IS NULL )
675 AND ( p_percent_discount IS NULL )))
676 )THEN
677 RETURN;
678 ELSE
679 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
680 APP_EXCEPTION.RAISE_EXCEPTION;
681 END IF;
682 END Lock_Row;
683
684 END OZF_Promotional_Offers_PKG;