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