[Home] [Help]
PACKAGE BODY: APPS.OZF_COPY_OFFER_PVT
Source
1 PACKAGE BODY OZF_COPY_OFFER_PVT AS
2 /* $Header: ozfvcpob.pls 120.20 2011/12/06 05:35:30 apyadav ship $ */
3
4 G_CREATE CONSTANT VARCHAR2(30) := 'CREATE';
5 TYPE line_mapping_rec_type IS RECORD
6 (org_line_id NUMBER
7 ,new_line_id NUMBER);
8
9 TYPE line_mapping_tbl_type IS TABLE OF line_mapping_rec_type INDEX BY BINARY_INTEGER;
10
11 PROCEDURE copy_vo_mkt_options
12 (
13 p_api_version IN NUMBER
14 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
15 , p_commit IN VARCHAR2 := FND_API.G_FALSE
16 , p_validation_level IN VARCHAR2 := FND_API.G_VALID_LEVEL_FULL
17 , x_return_status OUT NOCOPY VARCHAR2
18 , x_msg_count OUT NOCOPY NUMBER
19 , x_msg_data OUT NOCOPY VARCHAR2
20 , p_sourceObjectId IN NUMBER
21 , p_destOfferId IN NUMBER
22 )
23 IS
24
25 l_api_name CONSTANT VARCHAR2(30) := 'copy_vo_mkt_options';
26 l_api_version_number CONSTANT NUMBER := 1.0;
27 l_mo_rec OZF_offer_Market_Options_PVT.vo_mo_rec_type ;
28
29 CURSOR c_sourceMktOptions(cp_listHeaderId NUMBER , cp_groupNumber NUMBER) IS
30 SELECT group_number, retroactive_flag, beneficiary_party_id, combine_schedule_flag, volume_tracking_level_code, accrue_to_code, precedence
31 FROM ozf_offr_market_options
32 WHERE qp_list_header_id = cp_listHeaderId
33 AND group_number = cp_groupNumber;
34
35 CURSOR c_destMktOptions(cp_offerId NUMBER) IS
36 SELECT offer_market_option_id , object_version_number, group_number
37 FROM ozf_offr_market_options
38 WHERE offer_id = cp_offerId;
39
40 BEGIN
41 x_return_status := FND_API.G_RET_STS_SUCCESS;
42 FOR l_destMktOptions IN c_destMktOptions(cp_offerId => p_destOfferId) LOOP
43 l_mo_rec.offer_market_option_id := l_destMktOptions.offer_market_option_id;
44 l_mo_rec.object_version_number := l_destMktOptions.object_version_number;
45 l_mo_rec.group_number := l_destMktOptions.group_number;
46 FOR l_sourceMktOptions IN c_sourceMktOptions(cp_listHeaderId => p_sourceObjectId, cp_groupNumber => l_destMktOptions.group_number) LOOP
47 l_mo_rec.retroactive_flag := l_sourceMktOptions.retroactive_flag;
48 l_mo_rec.beneficiary_party_id := l_sourceMktOptions.beneficiary_party_id;
49 l_mo_rec.combine_schedule_flag := l_sourceMktOptions.combine_schedule_flag;
50 l_mo_rec.volume_tracking_level_code := l_sourceMktOptions.volume_tracking_level_code;
51 l_mo_rec.accrue_to_code := l_sourceMktOptions.accrue_to_code;
52 l_mo_rec.precedence := l_sourceMktOptions.precedence;
53 END LOOP;
54
55
56 OZF_offer_Market_Options_PVT.Update_market_options(
57 p_api_version_number => 1.0
58 , p_init_msg_list => FND_API.G_FALSE
59 , p_commit => FND_API.G_FALSE
60 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
61
62 , x_return_status => x_return_status
63 , x_msg_count => x_msg_count
64 , x_msg_data => x_msg_data
65
66 , p_mo_rec => l_mo_rec
67 );
68 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
69 RAISE FND_API.G_EXC_ERROR;
70 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
71 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
72 END IF;
73
74 END LOOP;
75
76 EXCEPTION
77 WHEN Fnd_Api.G_EXC_ERROR THEN
78 x_return_status := Fnd_Api.g_ret_sts_error;
79 ROLLBACK TO copy_vo_mkt_options;
80 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_ERROR )
81 THEN
82 Fnd_Msg_Pub.Add_Exc_Msg( G_PKG_NAME,l_api_name);
83 END IF;
84 Fnd_Msg_Pub.Count_AND_Get
85 ( p_count => x_msg_count,
86 p_data => x_msg_data,
87 p_encoded => Fnd_Api.G_FALSE
88 );
89
90 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
91 x_return_status := Fnd_Api.g_ret_sts_unexp_error;
92 ROLLBACK TO copy_vo_mkt_options;
93 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR )
94 THEN
95 Fnd_Msg_Pub.Add_Exc_Msg( G_PKG_NAME,l_api_name);
96 END IF;
97 Fnd_Msg_Pub.Count_AND_Get
98 ( p_count => x_msg_count,
99 p_data => x_msg_data,
100 p_encoded => Fnd_Api.G_FALSE
101 );
102
103 WHEN OTHERS THEN
104 x_return_status := Fnd_Api.g_ret_sts_unexp_error;
105 ROLLBACK TO copy_vo_mkt_options;
106 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR )
107 THEN
108 Fnd_Msg_Pub.Add_Exc_Msg( G_PKG_NAME,l_api_name);
109 END IF;
110 Fnd_Msg_Pub.Count_AND_Get
111 ( p_count => x_msg_count,
112 p_data => x_msg_data,
113 p_encoded => Fnd_Api.G_FALSE
114 );
115
116 END copy_vo_mkt_options;
117
118
119 PROCEDURE copy_vo_products
120 (
121 p_api_version IN NUMBER
122 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
123 , p_commit IN VARCHAR2 := FND_API.G_FALSE
124 , p_validation_level IN VARCHAR2 := FND_API.G_VALID_LEVEL_FULL
125 , x_return_status OUT NOCOPY VARCHAR2
126 , x_msg_count OUT NOCOPY NUMBER
127 , x_msg_data OUT NOCOPY VARCHAR2
128 , p_sourceTierHeaderId IN NUMBER
129 , p_destTierHeaderId IN NUMBER
130 , p_destOfferId IN NUMBER
131 )
132 IS
133 l_api_version_number CONSTANT NUMBER := 1.0;
134 l_api_name CONSTANT VARCHAR2(30) := 'copy_vo_products';
135 l_productId NUMBER;
136
137 CURSOR c_products(cp_sourceTierHeaderId NUMBER) IS
138 SELECT product_context
139 , product_attribute
140 , product_attr_value
141 , apply_discount_flag
142 , include_volume_flag
143 , excluder_flag
144 FROM ozf_offer_discount_products
145 WHERE offer_discount_line_id = cp_sourceTierHeaderId;
146 l_vo_prod_rec OZF_Volume_Offer_disc_PVT.vo_prod_rec_type;
147 BEGIN
148 -- initialize
149 SAVEPOINT copy_vo_products;
150 x_return_status := FND_API.G_RET_STS_SUCCESS;
151 -- loop thru and create products
152 FOR l_products IN c_products(cp_sourceTierHeaderId => p_sourceTierHeaderId) LOOP
153 l_vo_prod_rec.excluder_flag := l_products.excluder_flag;
154 l_vo_prod_rec.offer_discount_line_id := p_destTierHeaderId;
155 l_vo_prod_rec.offer_id := p_destOfferId;
156 l_vo_prod_rec.product_context := l_products.product_context;
157 l_vo_prod_rec.product_attribute := l_products.product_attribute;
158 l_vo_prod_rec.product_attr_value := l_products.product_attr_value;
159 l_vo_prod_rec.apply_discount_flag := l_products.apply_discount_flag;
160 l_vo_prod_rec.include_volume_flag := l_products.include_volume_flag;
161
162 OZF_Volume_Offer_disc_PVT.Create_vo_product(
163 p_api_version_number => 1.0
164 , p_init_msg_list => FND_API.G_FALSE
165 , p_commit => FND_API.G_FALSE
166 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
167
168 , x_return_status => x_return_status
169 , x_msg_count => x_msg_count
170 , x_msg_data => x_msg_data
171
172 , p_vo_prod_rec => l_vo_prod_rec
173 , x_off_discount_product_id => l_productId
174 );
175
176 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
177 RAISE FND_API.G_EXC_ERROR;
178 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
179 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
180 END IF;
181
182 END LOOP;
183
184 -- exception
185 END copy_vo_products;
186
187 PROCEDURE copy_vo_qualifiers
188 (
189 p_api_version IN NUMBER
190 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
191 , p_commit IN VARCHAR2 := FND_API.G_FALSE
192 , p_validation_level IN VARCHAR2 := FND_API.G_VALID_LEVEL_FULL
193 , x_return_status OUT NOCOPY VARCHAR2
194 , x_msg_count OUT NOCOPY NUMBER
195 , x_msg_data OUT NOCOPY VARCHAR2
196 , p_destListHeaderId IN NUMBER
197 , p_sourceListHeaderId IN NUMBER
198 )
199 IS
200 CURSOR c_qualifiers(cp_listHeaderId NUMBER) IS
201 SELECT qualifier_context
202 , qualifier_attribute
203 , qualifier_attr_value
204 , comparison_operator_code
205 , qualifier_attr_value_to
206 , qualifier_grouping_no
207 , list_header_id
208 , start_date_active
209 , end_date_active
210 , active_flag
211 , context
212 , attribute1
213 , attribute2
214 , attribute3
215 , attribute4
216 , attribute5
217 , attribute6
218 , attribute7
219 , attribute8
220 , attribute9
221 , attribute10
222 , attribute11
223 , attribute12
224 , attribute13
225 , attribute14
226 , attribute15
227 FROM qp_qualifiers
228 WHERE list_header_id = cp_listHeaderId;
229
230 l_qualifiers_rec OZF_OFFER_PVT.qualifiers_Rec_Type;
231 l_api_version_number CONSTANT NUMBER := 1.0;
232 l_api_name CONSTANT VARCHAR2(30) := 'copy_vo_qualifiers';
233 BEGIN
234 -- initialize
235 SAVEPOINT copy_vo_qualifiers;
236 x_return_status := FND_API.G_RET_STS_SUCCESS;
237 -- loop thru and create qualifiers
238 FOR l_qualifiers IN c_qualifiers(cp_listHeaderId => p_sourceListHeaderId) LOOP
239
240 l_qualifiers_rec.qualifier_context := l_qualifiers.qualifier_context;
241 l_qualifiers_rec.qualifier_attribute := l_qualifiers.qualifier_attribute;
242 l_qualifiers_rec.qualifier_attr_value := l_qualifiers.qualifier_attr_value;
243 l_qualifiers_rec.comparison_operator_code := l_qualifiers.comparison_operator_code;
244 l_qualifiers_rec.qualifier_attr_value_to := l_qualifiers.qualifier_attr_value_to;
245 l_qualifiers_rec.qualifier_grouping_no := l_qualifiers.qualifier_grouping_no;
246 l_qualifiers_rec.list_header_id := p_destListHeaderId;
247 l_qualifiers_rec.start_date_active := l_qualifiers.start_date_active;
248 l_qualifiers_rec.end_date_active := l_qualifiers.end_date_active;
249 --l_qualifiers_rec.active_flag := l_qualifiers.active_flag;
250 l_qualifiers_rec.context := l_qualifiers.context;
251 l_qualifiers_rec.attribute1 := l_qualifiers.attribute1;
252 l_qualifiers_rec.attribute2 := l_qualifiers.attribute2;
253 l_qualifiers_rec.attribute3 := l_qualifiers.attribute3;
254 l_qualifiers_rec.attribute4 := l_qualifiers.attribute4;
255 l_qualifiers_rec.attribute5 := l_qualifiers.attribute5;
256 l_qualifiers_rec.attribute6 := l_qualifiers.attribute6;
257 l_qualifiers_rec.attribute7 := l_qualifiers.attribute7;
258 l_qualifiers_rec.attribute8 := l_qualifiers.attribute8;
259 l_qualifiers_rec.attribute9 := l_qualifiers.attribute9;
260 l_qualifiers_rec.attribute10 := l_qualifiers.attribute10;
261 l_qualifiers_rec.attribute11 := l_qualifiers.attribute11;
262 l_qualifiers_rec.attribute12 := l_qualifiers.attribute12;
263 l_qualifiers_rec.attribute13 := l_qualifiers.attribute13;
264 l_qualifiers_rec.attribute14 := l_qualifiers.attribute14;
265 l_qualifiers_rec.attribute15 := l_qualifiers.attribute15;
266
267
268 OZF_Volume_Offer_Qual_PVT.create_vo_qualifier
269 (
270 p_api_version_number => 1.0
271 , p_init_msg_list => FND_API.G_FALSE
272 , p_commit => FND_API.G_FALSE
273 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
274
275 , x_return_status => x_return_status
276 , x_msg_count => x_msg_count
277 , x_msg_data => x_msg_data
278
279 , p_qualifiers_rec => l_qualifiers_rec
280 );
281 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
282 RAISE FND_API.G_EXC_ERROR;
283 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
284 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
285 END IF;
286
287 END LOOP;
288
289 EXCEPTION
290
291 WHEN Fnd_Api.G_EXC_ERROR THEN
292 x_return_status := Fnd_Api.g_ret_sts_error;
293 ROLLBACK TO copy_vo_qualifiers;
294 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_ERROR )
295 THEN
296 Fnd_Msg_Pub.Add_Exc_Msg( G_PKG_NAME,l_api_name);
297 END IF;
298 Fnd_Msg_Pub.Count_AND_Get
299 ( p_count => x_msg_count,
300 p_data => x_msg_data,
301 p_encoded => Fnd_Api.G_FALSE
302 );
303
304 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
305 x_return_status := Fnd_Api.g_ret_sts_unexp_error;
306 ROLLBACK TO copy_vo_qualifiers;
307 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR )
308 THEN
309 Fnd_Msg_Pub.Add_Exc_Msg( G_PKG_NAME,l_api_name);
310 END IF;
311 Fnd_Msg_Pub.Count_AND_Get
312 ( p_count => x_msg_count,
313 p_data => x_msg_data,
314 p_encoded => Fnd_Api.G_FALSE
315 );
316
317 WHEN OTHERS THEN
318 x_return_status := Fnd_Api.g_ret_sts_unexp_error;
319 ROLLBACK TO copy_vo_qualifiers;
320 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR )
321 THEN
322 Fnd_Msg_Pub.Add_Exc_Msg( G_PKG_NAME,l_api_name);
323 END IF;
324 Fnd_Msg_Pub.Count_AND_Get
325 ( p_count => x_msg_count,
326 p_data => x_msg_data,
327 p_encoded => Fnd_Api.G_FALSE
328 );
329
330 -- exception
331 END copy_vo_qualifiers;
332
333
334
335 PROCEDURE copy_vo_tiers
336 (
337 p_api_version IN NUMBER
338 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
339 , p_commit IN VARCHAR2 := FND_API.G_FALSE
340 , p_validation_level IN VARCHAR2 := FND_API.G_VALID_LEVEL_FULL
341 , x_return_status OUT NOCOPY VARCHAR2
342 , x_msg_count OUT NOCOPY NUMBER
343 , x_msg_data OUT NOCOPY VARCHAR2
344 , p_sourceTierHeaderId IN NUMBER
345 , p_destTierHeaderId IN NUMBER
346 , p_destOfferId IN NUMBER
347 )
348 IS
349 l_api_version_number CONSTANT NUMBER := 1.0;
350 l_api_name CONSTANT VARCHAR2(30) := 'copy_vo_tiers';
351 CURSOR c_tiers(cp_parentDiscountId NUMBER) IS
352 SELECT volume_from
353 , volume_to
354 , discount
355 , formula_id
356 , tier_type
357 , volume_operator
358 , volume_break_type
359 , tier_level
360 FROM ozf_offer_discount_lines
361 WHERE parent_discount_line_id = cp_parentDiscountId;
362
363 l_discountLineId NUMBER;
364 l_vo_disc_rec OZF_Volume_Offer_disc_PVT.vo_disc_rec_type;
365
366 BEGIN
367 -- initialize
368 SAVEPOINT copy_vo_tiers;
369 x_return_status := FND_API.G_RET_STS_SUCCESS;
370 -- loop thru and populate discounts
371 -- create tiers
372 FOR l_tiers IN c_tiers(cp_parentDiscountId => p_sourceTierHeaderId) LOOP
373 l_vo_disc_rec.offer_id := p_destOfferId;
374 l_vo_disc_rec.parent_discount_line_id := p_destTierHeaderId;
375 l_vo_disc_rec.tier_type := l_tiers.tier_type;
376 l_vo_disc_rec.volume_from := l_tiers.volume_from;
377 l_vo_disc_rec.volume_to := l_tiers.volume_to;
378 l_vo_disc_rec.volume_operator := l_tiers.volume_operator;
379 l_vo_disc_rec.volume_break_type := l_tiers.volume_break_type;
380 l_vo_disc_rec.discount := l_tiers.discount;
381 l_vo_disc_rec.formula_id := l_tiers.formula_id;
382 l_vo_disc_rec.tier_level := l_tiers.tier_level;
383
384 OZF_Volume_Offer_disc_PVT.Create_vo_discount(
385 p_api_version_number => 1.0
386 , p_init_msg_list => FND_API.G_FALSE
387 , p_commit => FND_API.G_FALSE
388 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
389
390 , x_return_status => x_return_status
391 , x_msg_count => x_msg_count
392 , x_msg_data => x_msg_data
393
394 , p_vo_disc_rec => l_vo_disc_rec
395 , x_vo_discount_line_id => l_discountLineId
396 );
397 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
398 RAISE FND_API.G_EXC_ERROR;
399 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
400 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
401 END IF;
402
403 ozf_utility_pvt.debug_message('Discount LineId created is: '||l_discountLineId);
404
405
406 END LOOP;
407
408 -- exception
409 null;
410 END copy_vo_tiers;
411
412 PROCEDURE copy_vo_discounts
413 (
414 p_api_version IN NUMBER
415 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
416 , p_commit IN VARCHAR2 := FND_API.G_FALSE
417 , p_validation_level IN VARCHAR2 := FND_API.G_VALID_LEVEL_FULL
418 , x_return_status OUT NOCOPY VARCHAR2
419 , x_msg_count OUT NOCOPY NUMBER
420 , x_msg_data OUT NOCOPY VARCHAR2
421 , p_sourceObjectId IN NUMBER
422 , p_destOfferId IN NUMBER
423 )
424 IS
425 l_vo_disc_rec OZF_Volume_Offer_disc_PVT.vo_disc_rec_type;
426 l_discountLineId NUMBER;
427 l_api_version_number CONSTANT NUMBER := 1.0;
428 l_api_name CONSTANT VARCHAR2(30) := 'copy_vo_discounts';
429
430
431 CURSOR c_pbh (cp_offerId NUMBER) IS
432 SELECT a.volume_type
433 , a.volume_break_type
434 , a.discount_type
435 , a.uom_code
436 , c.discount_table_name
437 , c.description
438 , a.tier_level
439 , a.offer_discount_line_id
440 FROM ozf_offer_discount_lines a, ozf_offr_disc_struct_name_b b , ozf_offr_disc_struct_name_tl c
441 WHERE a.offer_discount_line_id = b.offer_discount_Line_id
442 AND b.offr_disc_struct_name_id = c.offr_disc_struct_name_id
443 AND c.language = USERENV('LANG')
444 AND a.offer_id = cp_offerId;
445
446 CURSOR c_offerId(cp_listHeaderId NUMBER) IS
447 SELECT offer_id FROM ozf_offers
448 WHERE qp_list_header_id = cp_listHeaderId;
449 l_sourceOfferId NUMBER := null;
450 BEGIN
451 -- initialize
452 SAVEPOINT copy_vo_discounts;
453 x_return_status := FND_API.G_RET_STS_SUCCESS;
454 l_sourceOfferId := null;
455
456 OPEN c_offerId(cp_listHeaderId => p_sourceObjectId);
457 FETCH c_offerId INTO l_sourceOfferId;
458 IF c_offerId%NOTFOUND THEN
459 OZF_Utility_PVT.Error_Message('OZF_OFFR_CPY_NO_OFFER');
460 RAISE FND_API.G_EXC_ERROR;
461 END IF;
462 CLOSE c_offerId;
463
464
465 FOR l_pbh in c_pbh(cp_offerId => l_sourceOfferId ) LOOP
466
467 l_vo_disc_rec.offer_id := p_destOfferId;
468 l_vo_disc_rec.tier_type := 'PBH';
469 l_vo_disc_rec.volume_type := l_pbh.volume_type;
470 l_vo_disc_rec.volume_break_type := l_pbh.volume_break_type;
471 l_vo_disc_rec.discount_type := l_pbh.discount_type;
472 l_vo_disc_rec.uom_code := l_pbh.uom_code;
473 l_vo_disc_rec.name := l_pbh.discount_table_name;
474 l_vo_disc_rec.description := l_pbh.description;
475 l_vo_disc_rec.tier_level := l_pbh.tier_level;
476
477 ozf_utility_pvt.debug_message('Calling create PBH disc');
478 OZF_Volume_Offer_disc_PVT.Create_vo_discount(
479 p_api_version_number => 1.0
480 , p_init_msg_list => FND_API.G_FALSE
481 , p_commit => FND_API.G_FALSE
482 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
483
484 , x_return_status => x_return_status
485 , x_msg_count => x_msg_count
486 , x_msg_data => x_msg_data
487
488 , p_vo_disc_rec => l_vo_disc_rec
489 , x_vo_discount_line_id => l_discountLineId
490 );
491
492 ozf_utility_pvt.debug_message('PBH Id is:'||l_discountLineId);
493
494 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
495 RAISE FND_API.G_EXC_ERROR;
496 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
497 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
498 END IF;
499
500 -- create discounts
501 copy_vo_tiers
502 (
503 p_api_version => 1.0
504 , p_init_msg_list => FND_API.G_FALSE
505 , p_commit => FND_API.G_FALSE
506 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
507 , x_return_status => x_return_status
508 , x_msg_count => x_msg_count
509 , x_msg_data => x_msg_data
510 , p_sourceTierHeaderId => l_pbh.offer_discount_line_id
511 , p_destTierHeaderId => l_discountLineId
512 , p_destOfferId => p_destOfferId
513 );
514 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
515 RAISE FND_API.G_EXC_ERROR;
516 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
517 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
518 END IF;
519 ozf_utility_pvt.debug_message('Discount Tier created is:'||l_discountLineId);
520
521 -- create products
522 copy_vo_products
523 (
524 p_api_version => 1.0
525 , p_init_msg_list => FND_API.G_FALSE
526 , p_commit => FND_API.G_FALSE
527 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
528 , x_return_status => x_return_status
529 , x_msg_count => x_msg_count
530 , x_msg_data => x_msg_data
531 , p_sourceTierHeaderId => l_pbh.offer_discount_line_id
532 , p_destTierHeaderId => l_discountLineId
533 , p_destOfferId => p_destOfferId
534 );
535 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
536 RAISE FND_API.G_EXC_ERROR;
537 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
538 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
539 END IF;
540
541 END LOOP;
542
543
544 EXCEPTION
545
546 WHEN Fnd_Api.G_EXC_ERROR THEN
547 x_return_status := Fnd_Api.g_ret_sts_error;
548 ROLLBACK TO copy_vo_discounts;
549 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_ERROR )
550 THEN
551 Fnd_Msg_Pub.Add_Exc_Msg( G_PKG_NAME,l_api_name);
552 END IF;
553 Fnd_Msg_Pub.Count_AND_Get
554 ( p_count => x_msg_count,
555 p_data => x_msg_data,
556 p_encoded => Fnd_Api.G_FALSE
557 );
558
559 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
560 x_return_status := Fnd_Api.g_ret_sts_unexp_error;
561 ROLLBACK TO copy_vo_discounts;
562 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR )
563 THEN
564 Fnd_Msg_Pub.Add_Exc_Msg( G_PKG_NAME,l_api_name);
565 END IF;
566 Fnd_Msg_Pub.Count_AND_Get
567 ( p_count => x_msg_count,
568 p_data => x_msg_data,
569 p_encoded => Fnd_Api.G_FALSE
570 );
571
572 WHEN OTHERS THEN
573 x_return_status := Fnd_Api.g_ret_sts_unexp_error;
574 ROLLBACK TO copy_vo_discounts;
575 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR )
576 THEN
577 Fnd_Msg_Pub.Add_Exc_Msg( G_PKG_NAME,l_api_name);
578 END IF;
579 Fnd_Msg_Pub.Count_AND_Get
580 ( p_count => x_msg_count,
581 p_data => x_msg_data,
582 p_encoded => Fnd_Api.G_FALSE
583 );
584
585 -- loop thru source discounts
586 -- create pbh
587 -- use pbh id to create discount lines
588 -- use pbh id to create products
589
590 -- exception
591 END copy_vo_discounts;
592
593 PROCEDURE copy_vo_preset_tiers
594 (
595 p_api_version IN NUMBER
596 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
597 , p_commit IN VARCHAR2 := FND_API.G_FALSE
598 , p_validation_level IN VARCHAR2 := FND_API.G_VALID_LEVEL_FULL
599 , x_return_status OUT NOCOPY VARCHAR2
600 , x_msg_count OUT NOCOPY NUMBER
601 , x_msg_data OUT NOCOPY VARCHAR2
602 , p_sourceObjectId IN NUMBER
603 , p_destOfferId IN NUMBER
604 )
605 IS
606 l_api_name CONSTANT VARCHAR2(30) := 'copy_vo_preset_tiers';
607 l_api_version_number CONSTANT NUMBER := 1.0;
608 BEGIN
609 -- initialize
610 x_return_status := FND_API.G_RET_STS_SUCCESS;
611 -- loop thru. and copy the preset tiers for source object
612 /*l_preset_tier_rec.offer_market_option_id := 101000;
613 l_preset_tier_rec.pbh_offer_discount_id := 7001;
614 l_preset_tier_rec.dis_offer_discount_id := 7002;*/
615 /*OZF_MO_PRESET_TIERS_PVT.Create_mo_preset_tiers(
616 p_api_version_number => 1.0
617 , p_init_msg_list => FND_API.G_FALSE
618 , p_commit => FND_API.G_FALSE
619 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
620
621 , x_return_status => x_return_status
622 , x_msg_count => x_msg_count
623 , x_msg_data => x_msg_data
624
625 , p_preset_tier_rec => l_preset_tier_rec
626 , x_market_preset_tier_id => l_market_preset_tier_id
627 );*/
628 -- exception
629 END copy_vo_preset_tiers;
630
631
632
633 PROCEDURE copy_vo_header(
634 p_api_version IN NUMBER
635 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
636 , p_commit IN VARCHAR2 := FND_API.G_FALSE
637 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
638 , x_return_status OUT NOCOPY VARCHAR2
639 , x_msg_count OUT NOCOPY NUMBER
640 , x_msg_data OUT NOCOPY VARCHAR2
641 , p_listHeaderId IN NUMBER
642 , x_OfferId OUT NOCOPY NUMBER
643 , x_listHeaderId OUT NOCOPY NUMBER
644 , p_copy_columns_table IN AMS_CpyUtility_PVT.copy_columns_table_type
645 , p_attributes_table IN AMS_CpyUtility_PVT.copy_attributes_table_type
646 , p_custom_setup_id IN NUMBER
647 )
648 IS
649 l_modifier_list_rec OZF_OFFER_PVT.modifier_list_rec_type ;
650 l_modifier_line_tbl OZF_OFFER_PVT.MODIFIER_LINE_TBL_TYPE ;
651 l_listHeaderId NUMBER;
652 l_errLoc NUMBER;
653 l_api_version_number CONSTANT NUMBER := 1.0;
654 l_api_name CONSTANT VARCHAR2(30) := 'copy_vo_header';
655 l_offer_type CONSTANT VARCHAR2(30) := 'VOLUME_OFFER';
656 l_offer_id NUMBER;
657
658 l_budget_source_type VARCHAR2(30);
659 l_budget_source_id NUMBER;
660 l_status_code VARCHAR2(30);
661 l_end_date_active DATE;
662
663 CURSOR c_offer_details (p_listHeaderId NUMBER) IS
664 SELECT a.modifier_level_code
665 , a.offer_type
666 , a.activity_media_id
667 , a.reusable
668 , b.list_type_code
669 , a.transaction_currency_code
670 , a.perf_date_from
671 , a.perf_date_to
672 , a.custom_setup_id
673 , a.functional_currency_code
674 , b.currency_code
675 , b.ask_for_flag
676 , b.start_date_active_first
677 , b.end_date_active_first
678 , b.active_date_first_type
679 , b.start_date_active_second
680 , b.end_date_active_second
681 , b.active_date_second_type
682 , a.budget_source_type
683 , a.budget_source_id
684 , a.budget_amount_tc
685 , a.offer_amount
686 , a.volume_offer_type
687 , a.budget_offer_yn
688 , a.confidential_flag
689 , a.source_from_parent
690 , b.global_flag
691 , b.orig_org_id
692 , b.context
693 , b.attribute1
694 , b.attribute2
695 , b.attribute3
696 , b.attribute4
697 , b.attribute5
698 , b.attribute6
699 , b.attribute7
700 , b.attribute8
701 , b.attribute9
702 , b.attribute10
703 , b.attribute11
704 , b.attribute12
705 , b.attribute13
706 , b.attribute14
707 , b.attribute15
708 FROM ozf_offers a, qp_list_headers_all b
709 WHERE a.qp_list_header_id = b.list_header_id
710 AND a.qp_list_header_id = p_listHeaderId;
711
712 CURSOR c_offerId (cp_listHeaderId NUMBER) IS
713 SELECT offer_id FROM ozf_offers
714 WHERE qp_list_header_id = cp_listHeaderId ;
715
716 --rvkondur Bug#12888855
717 CURSOR c_funds_all_details(l_fund_id VARCHAR2) IS
718 SELECT status_code, end_date_active
719 FROM ozf_funds_all_b
720 WHERE fund_id = l_fund_id;
721
722 BEGIN
723 -- establish save point
724 SAVEPOINT copy_vo_header;
725 -- check api version compatibility
726 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
727 p_api_version,
728 l_api_name,
729 G_PKG_NAME)
730 THEN
731 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
732 END IF;
733
734 -- Initialize message list if p_init_msg_list is set to TRUE.
735 IF FND_API.to_Boolean( p_init_msg_list ) THEN
736 FND_MSG_PUB.initialize;
737 END IF;
738
739 -- Initialize API return status to SUCCESS
740 x_return_status := FND_API.G_RET_STS_SUCCESS;
741
742 -- initialize the record sent to api for creation
743 l_modifier_list_rec := null;
744
745 -- populate ui values into record.
746 AMS_CpyUtility_PVT.get_column_value ('newObjName' , p_copy_columns_table , l_modifier_list_rec.description);
747 AMS_CpyUtility_PVT.get_column_value ('offerCode' , p_copy_columns_table , l_modifier_list_rec.offer_code);
748 AMS_CpyUtility_PVT.get_column_value ('startDateActive' , p_copy_columns_table , l_modifier_list_rec.start_date_active);
749 AMS_CpyUtility_PVT.get_column_value ('endDateActive' , p_copy_columns_table , l_modifier_list_rec.end_date_active);
750 AMS_CpyUtility_PVT.get_column_value ('ownerId' , p_copy_columns_table , l_modifier_list_rec.owner_id);
751 AMS_CpyUtility_PVT.get_column_value ('description' , p_copy_columns_table , l_modifier_list_rec.comments);
752
753 FOR l_offer_details IN c_offer_details(p_listHeaderId) LOOP
754
755 --rvkondur Bug#12888855
756 l_budget_source_type := l_offer_details.budget_source_type;
757 l_budget_source_id := l_offer_details.budget_source_id;
758
759 OPEN c_funds_all_details(l_budget_source_id);
760 FETCH c_funds_all_details INTO l_status_code, l_end_date_active;
761 CLOSE c_funds_all_details;
762
763 IF (l_status_code <> 'ACTIVE' OR (l_modifier_list_rec.start_date_active IS NOT NULL
764 AND l_end_date_active < l_modifier_list_rec.start_date_active)) THEN
765 l_budget_source_type := null;
766 l_budget_source_id := null;
767 END IF;
768
769 -- populate source object values into the record
770 l_modifier_list_rec.modifier_level_code := l_offer_details.modifier_level_code;
771 l_modifier_list_rec.offer_type := l_offer_type;
772 l_modifier_list_rec.activity_media_id := l_offer_details.activity_media_id;
773 l_modifier_list_rec.reusable := l_offer_details.reusable;
774 l_modifier_list_rec.list_type_code := l_offer_details.list_type_code;
775 l_modifier_list_rec.transaction_currency_code := l_offer_details.transaction_currency_code;
776 l_modifier_list_rec.perf_date_from := l_offer_details.perf_date_from;
777 l_modifier_list_rec.perf_date_to := l_offer_details.perf_date_to;
778 l_modifier_list_rec.custom_setup_id := p_custom_setup_id;
779 l_modifier_list_rec.functional_currency_code := l_offer_details.functional_currency_code;
780 l_modifier_list_rec.currency_code := l_offer_details.currency_code;
781 l_modifier_list_rec.ask_for_flag := l_offer_details.ask_for_flag;
782 l_modifier_list_rec.start_date_active_first := l_offer_details.start_date_active_first;
783 l_modifier_list_rec.end_date_active_first := l_offer_details.end_date_active_first;
784 l_modifier_list_rec.active_date_first_type := l_offer_details.active_date_first_type;
785 l_modifier_list_rec.start_date_active_second := l_offer_details.start_date_active_second;
786 l_modifier_list_rec.end_date_active_second := l_offer_details.end_date_active_second;
787 l_modifier_list_rec.active_date_second_type := l_offer_details.active_date_second_type;
788 l_modifier_list_rec.budget_source_type := l_budget_source_type;
789 l_modifier_list_rec.budget_source_id := l_budget_source_id;
790 l_modifier_list_rec.budget_amount_tc := l_offer_details.budget_amount_tc;
791 l_modifier_list_rec.budget_offer_yn := l_offer_details.budget_offer_yn ;
792 l_modifier_list_rec.offer_amount := l_offer_details.offer_amount;
793 l_modifier_list_rec.volume_offer_type := l_offer_details.volume_offer_type;
794 l_modifier_list_rec.confidential_flag := l_offer_details.confidential_flag;
795 --l_modifier_list_rec.committed_amount_eq_max :=
796 l_modifier_list_rec.source_from_parent := l_offer_details.source_from_parent;
797 l_modifier_list_rec.global_flag := l_offer_details.global_flag;
798 l_modifier_list_rec.orig_org_id := l_offer_details.orig_org_id;
799 l_modifier_list_rec.modifier_operation := G_CREATE;
800 l_modifier_list_rec.offer_operation := G_CREATE;
801
802 l_modifier_list_rec.offer_id := FND_API.G_MISS_NUM;
803 l_modifier_list_rec.amount_limit_id := FND_API.G_MISS_NUM;
804 l_modifier_list_rec.uses_limit_id := FND_API.G_MISS_NUM;
805 l_modifier_list_rec.qp_list_header_id := FND_API.G_MISS_NUM;
806
807
808 l_modifier_list_rec.context := l_offer_details.context;
809 l_modifier_list_rec.attribute1 := l_offer_details.attribute1;
810 l_modifier_list_rec.attribute2 := l_offer_details.attribute2;
811 l_modifier_list_rec.attribute3 := l_offer_details.attribute3;
812 l_modifier_list_rec.attribute4 := l_offer_details.attribute4;
813 l_modifier_list_rec.attribute5 := l_offer_details.attribute5;
814 l_modifier_list_rec.attribute6 := l_offer_details.attribute6;
815 l_modifier_list_rec.attribute7 := l_offer_details.attribute7;
816 l_modifier_list_rec.attribute8 := l_offer_details.attribute8;
817 l_modifier_list_rec.attribute9 := l_offer_details.attribute9;
818 l_modifier_list_rec.attribute10 := l_offer_details.attribute10;
819 l_modifier_list_rec.attribute11 := l_offer_details.attribute11;
820 l_modifier_list_rec.attribute12 := l_offer_details.attribute12;
821 l_modifier_list_rec.attribute13 := l_offer_details.attribute13;
822 l_modifier_list_rec.attribute14 := l_offer_details.attribute14;
823 l_modifier_list_rec.attribute15 := l_offer_details.attribute15;
824
825 END LOOP;
826
827 -- call api to create new header
828 OZF_OFFER_PVT.process_modifiers
829 (
830 p_init_msg_list => FND_API.G_FALSE
831 ,p_api_version => 1.0
832 ,p_commit => FND_API.G_FALSE
833 ,x_return_status => x_return_status
834 ,x_msg_count => x_msg_count
835 ,x_msg_data => x_msg_data
836 ,p_offer_type => l_offer_type
837 ,p_modifier_list_rec => l_modifier_list_rec
838 ,p_modifier_line_tbl => l_modifier_line_tbl
839 ,x_qp_list_header_id => l_listHeaderId
840 ,x_error_location => l_errLoc
841 );
842 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
843 RAISE FND_API.G_EXC_ERROR;
844 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
845 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
846 END IF;
847
848 l_offer_id := null;
849
850 OPEN c_offerId(cp_listHeaderId => l_listHeaderId);
851 FETCH c_offerId into l_offer_id;
852 IF c_offerId%NOTFOUND THEN
853 l_offer_id := -1;
854 END IF;
855 CLOSE c_offerId;
856
857 copy_vo_discounts
858 (
859 p_api_version => 1.0
860 , p_init_msg_list => FND_API.G_FALSE
861 , p_commit => FND_API.G_FALSE
862 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
863 , x_return_status => x_return_status
864 , x_msg_count => x_msg_count
865 , x_msg_data => x_msg_data
866 , p_sourceObjectId => p_listHeaderId
867 , p_destOfferId => l_offer_id
868 );
869 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
870 RAISE FND_API.G_EXC_ERROR;
871 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
872 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
873 END IF;
874
875 -- copy qualifiers
876 IF AMS_CpyUtility_PVT.is_copy_attribute ('ELIG', p_attributes_table) = FND_API.G_TRUE THEN
877 ozf_utility_pvt.debug_message('Copy Eligibility');
878 copy_vo_qualifiers
879 (
880 p_api_version => 1.0
881 , p_init_msg_list => FND_API.G_FALSE
882 , p_commit => FND_API.G_FALSE
883 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
884 , x_return_status => x_return_status
885 , x_msg_count => x_msg_count
886 , x_msg_data => x_msg_data
887 , p_sourceListHeaderId => p_listHeaderId
888 , p_destListHeaderId => l_listHeaderId
889 );
890 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
891 RAISE FND_API.G_EXC_ERROR;
892 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
893 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
894 END IF;
895 -- copy market options
896 -- market options are created with default options, so update the market options to the ones in the source offer
897 IF AMS_CpyUtility_PVT.is_copy_attribute ('MKT_OPT', p_attributes_table) = FND_API.G_TRUE THEN
898 ozf_utility_pvt.debug_message('Copy Market Options');
899 copy_vo_mkt_options
900 (
901 p_api_version => 1.0
902 , p_init_msg_list => FND_API.G_FALSE
903 , p_commit => FND_API.G_FALSE
904 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
905 , x_return_status => x_return_status
906 , x_msg_count => x_msg_count
907 , x_msg_data => x_msg_data
908 , p_sourceObjectId => p_listHeaderId
909 , p_destOfferId => l_offer_id
910 );
911
912 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
913 RAISE FND_API.G_EXC_ERROR;
914 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
915 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
916 END IF;
917 END IF;
918 END IF;
919
920 ozf_utility_pvt.debug_message('QplistHeaderId returned :'||l_listHeaderId);
921
922 ozf_utility_pvt.debug_message('OfferId :'||l_offer_id);
923 x_OfferId := l_offer_id;
924 x_listHeaderId := l_listHeaderId;
925 -- exception
926 EXCEPTION
927
928 WHEN Fnd_Api.G_EXC_ERROR THEN
929 x_return_status := Fnd_Api.g_ret_sts_error;
930 ROLLBACK TO copy_vo_header;
931 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_ERROR )
932 THEN
933 Fnd_Msg_Pub.Add_Exc_Msg( G_PKG_NAME,l_api_name);
934 END IF;
935 Fnd_Msg_Pub.Count_AND_Get
936 ( p_count => x_msg_count,
937 p_data => x_msg_data,
938 p_encoded => Fnd_Api.G_FALSE
939 );
940
941 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
942 x_return_status := Fnd_Api.g_ret_sts_unexp_error;
943 ROLLBACK TO copy_vo_header;
944 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR )
945 THEN
946 Fnd_Msg_Pub.Add_Exc_Msg( G_PKG_NAME,l_api_name);
947 END IF;
948 Fnd_Msg_Pub.Count_AND_Get
949 ( p_count => x_msg_count,
950 p_data => x_msg_data,
951 p_encoded => Fnd_Api.G_FALSE
952 );
953
954 WHEN OTHERS THEN
955 x_return_status := Fnd_Api.g_ret_sts_unexp_error;
956 ROLLBACK TO copy_vo_header;
957 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR )
958 THEN
959 Fnd_Msg_Pub.Add_Exc_Msg( G_PKG_NAME,l_api_name);
960 END IF;
961 Fnd_Msg_Pub.Count_AND_Get
962 ( p_count => x_msg_count,
963 p_data => x_msg_data,
964 p_encoded => Fnd_Api.G_FALSE
965 );
966
967 END copy_vo_header;
968
969
970 FUNCTION get_rltd_line_id(l_org_list_header_id NUMBER, l_new_list_header_id NUMBER)
971 RETURN line_mapping_tbl_type
972 IS
973
974 l_line_mapping_tbl line_mapping_tbl_type;
975 l_index NUMBER := 0;
976
977 CURSOR c_mod_sum IS
978 SELECT *
979 FROM qp_modifier_summary_v
980 WHERE list_header_id = l_org_list_header_id;
981 -- l_mod_sum_rec c_mod_sum%ROWTYPE;
982
983 CURSOR c_list_line_id(l_mod_sum_rec c_mod_sum%ROWTYPE) IS
984 SELECT list_line_id
985 FROM qp_modifier_summary_v
986 WHERE list_header_id = l_new_list_header_id
987 AND list_line_type_code = l_mod_sum_rec.list_line_type_code
988 AND automatic_flag = l_mod_sum_rec.automatic_flag
989 AND modifier_level_code = l_mod_sum_rec.modifier_level_code
990 AND NVL(price_break_type_code,'z') = NVL(l_mod_sum_rec.price_break_type_code,'z')
991 AND NVL(operand,-99999) = NVL(l_mod_sum_rec.operand,-99999)
992 AND NVL(arithmetic_operator,'z') = NVL(l_mod_sum_rec.arithmetic_operator,'z')
993 AND NVL(override_flag,'z') = NVL(l_mod_sum_rec.override_flag,'z')
994 AND NVL(print_on_invoice_flag,'z') = NVL(l_mod_sum_rec.print_on_invoice_flag,'z')
995 AND NVL(pricing_group_sequence,-99999) = NVL(l_mod_sum_rec.pricing_group_sequence,-99999)
996 AND NVL(incompatibility_grp_code,'z') = NVL(l_mod_sum_rec.incompatibility_grp_code,'z')
997 AND NVL(product_precedence,-99999) = NVL(l_mod_sum_rec.product_precedence,-99999)
998 AND NVL(pricing_phase_id,-99999) = NVL(l_mod_sum_rec.pricing_phase_id,-99999)
999 AND NVL(product_attribute_context,'z') = NVL(l_mod_sum_rec.product_attribute_context,'z')
1000 AND NVL(product_attr,'z') = NVL(l_mod_sum_rec.product_attr,'z')
1001 AND NVL(product_attr_val,'z') = NVL(l_mod_sum_rec.product_attr_val,'z')
1002 AND NVL(product_uom_code,'z') = NVL(l_mod_sum_rec.product_uom_code,'z')
1003 AND NVL(comparison_operator_code,'z') = NVL(l_mod_sum_rec.comparison_operator_code,'z')
1004 AND NVL(pricing_attribute_context,'z') = NVL(l_mod_sum_rec.pricing_attribute_context,'z')
1005 AND NVL(pricing_attr,'z') = NVL(l_mod_sum_rec.pricing_attr,'z')
1006 AND NVL(pricing_attr_value_from,'z') = NVL(l_mod_sum_rec.pricing_attr_value_from,'z')
1007 AND NVL(pricing_attr_value_to,'z') = NVL(l_mod_sum_rec.pricing_attr_value_to,'z')
1008 AND NVL(excluder_flag,'z') = NVL(l_mod_sum_rec.excluder_flag,'z')
1009 AND NVL(attribute_grouping_no,-99999) = NVL(l_mod_sum_rec.attribute_grouping_no,-99999)
1010 AND NVL(to_rltd_modifier_id,'z') = NVL(l_mod_sum_rec.to_rltd_modifier_id,'z')
1011 AND NVL(rltd_modifier_id,'z') = NVL(l_mod_sum_rec.rltd_modifier_id,'z')
1012 AND NVL(accrual_flag,'z') = NVL(l_mod_sum_rec.accrual_flag,'z')
1013 AND NVL(accrual_conversion_rate,-99999) = NVL(l_mod_sum_rec.accrual_conversion_rate,-99999)
1014 AND NVL(estim_accrual_rate,-99999) = NVL(l_mod_sum_rec.estim_accrual_rate,-99999)
1015 AND NVL(price_by_formula_id,-99999) = NVL(l_mod_sum_rec.price_by_formula_id,-99999)
1016 AND NVL(generate_using_formula_id,-99999) = NVL(l_mod_sum_rec.generate_using_formula_id,-99999);
1017 /*
1018 AND LIST_LINE_TYPE_CODE = l_mod_sum_rec.LIST_LINE_TYPE_CODE
1019 AND AUTOMATIC_FLAG = l_mod_sum_rec.AUTOMATIC_FLAG
1020 AND MODIFIER_LEVEL_CODE = l_mod_sum_rec.MODIFIER_LEVEL_CODE
1021 AND NVL(LIST_PRICE,-99999) = NVL(l_mod_sum_rec.LIST_PRICE,-99999)
1022 AND NVL(LIST_PRICE_UOM_CODE,'z') = NVL(l_mod_sum_rec.LIST_PRICE_UOM_CODE,'z')
1023 AND NVL(PRIMARY_UOM_FLAG,'z') = NVL(l_mod_sum_rec.PRIMARY_UOM_FLAG,'z')
1024 AND NVL(INVENTORY_ITEM_ID,-99999) = NVL(l_mod_sum_rec.INVENTORY_ITEM_ID,-99999)
1025 AND NVL(ORGANIZATION_ID,-99999) = NVL(l_mod_sum_rec.ORGANIZATION_ID,-99999)
1026 AND NVL(RELATED_ITEM_ID,-99999) = NVL(l_mod_sum_rec.RELATED_ITEM_ID,-99999)
1027 AND NVL(RELATIONSHIP_TYPE_ID,-99999) = NVL(l_mod_sum_rec.RELATIONSHIP_TYPE_ID,-99999)
1028 AND NVL(SUBSTITUTION_CONTEXT,'z') = NVL(l_mod_sum_rec.SUBSTITUTION_CONTEXT,'z')
1029 AND NVL(SUBSTITUTION_ATTR,'z') = NVL(l_mod_sum_rec.SUBSTITUTION_ATTR,'z')
1030 AND NVL(SUBSTITUTION_VAL,'z') = NVL(l_mod_sum_rec.SUBSTITUTION_VAL,'z')
1031 AND NVL(REVISION,'z') = NVL(l_mod_sum_rec.REVISION,'z')
1032 AND NVL(REVISION_REASON_CODE,'z') = NVL(l_mod_sum_rec.REVISION_REASON_CODE,'z')
1033 AND NVL(CONTEXT,'z') = NVL(l_mod_sum_rec.CONTEXT,'z')
1034 AND NVL(ATTRIBUTE1,'z') = NVL(l_mod_sum_rec.ATTRIBUTE1,'z')
1035 AND NVL(ATTRIBUTE2,'z') = NVL(l_mod_sum_rec.ATTRIBUTE2,'z')
1036 AND NVL(COMMENTS,'z') = NVL(l_mod_sum_rec.COMMENTS,'z')
1037 AND NVL(ATTRIBUTE3,'z') = NVL(l_mod_sum_rec.ATTRIBUTE3,'z')
1038 AND NVL(ATTRIBUTE4,'z') = NVL(l_mod_sum_rec.ATTRIBUTE4,'z')
1039 AND NVL(ATTRIBUTE5,'z') = NVL(l_mod_sum_rec.ATTRIBUTE5,'z')
1040 AND NVL(ATTRIBUTE6,'z') = NVL(l_mod_sum_rec.ATTRIBUTE6,'z')
1041 AND NVL(ATTRIBUTE7,'z') = NVL(l_mod_sum_rec.ATTRIBUTE7,'z')
1042 AND NVL(ATTRIBUTE8,'z') = NVL(l_mod_sum_rec.ATTRIBUTE8,'z')
1043 AND NVL(ATTRIBUTE9,'z') = NVL(l_mod_sum_rec.ATTRIBUTE9,'z')
1044 AND NVL(ATTRIBUTE10,'z') = NVL(l_mod_sum_rec.ATTRIBUTE10,'z')
1045 AND NVL(INCLUDE_ON_RETURNS_FLAG,'z') = NVL(l_mod_sum_rec.INCLUDE_ON_RETURNS_FLAG,'z')
1046 AND NVL(ATTRIBUTE11,'z') = NVL(l_mod_sum_rec.ATTRIBUTE11,'z')
1047 AND NVL(ATTRIBUTE12,'z') = NVL(l_mod_sum_rec.ATTRIBUTE12,'z')
1048 AND NVL(ATTRIBUTE13,'z') = NVL(l_mod_sum_rec.ATTRIBUTE13,'z')
1049 AND NVL(ATTRIBUTE14,'z') = NVL(l_mod_sum_rec.ATTRIBUTE14,'z')
1050 AND NVL(ATTRIBUTE15,'z') = NVL(l_mod_sum_rec.ATTRIBUTE15,'z')
1051 AND NVL(PRICE_BREAK_TYPE_CODE,'z') = NVL(l_mod_sum_rec.PRICE_BREAK_TYPE_CODE,'z')
1052 AND NVL(PERCENT_PRICE,-99999) = NVL(l_mod_sum_rec.PERCENT_PRICE,-99999)
1053 AND NVL(EFFECTIVE_PERIOD_UOM,'z') = NVL(l_mod_sum_rec.EFFECTIVE_PERIOD_UOM,'z')
1054 AND NVL(NUMBER_EFFECTIVE_PERIODS,-99999) = NVL(l_mod_sum_rec.NUMBER_EFFECTIVE_PERIODS,-99999)
1055 AND NVL(OPERAND,-99999) = NVL(l_mod_sum_rec.OPERAND,-99999)
1056 AND NVL(ARITHMETIC_OPERATOR,'z') = NVL(l_mod_sum_rec.ARITHMETIC_OPERATOR,'z')
1057 AND NVL(OVERRIDE_FLAG,'z') = NVL(l_mod_sum_rec.OVERRIDE_FLAG,'z')
1058 AND NVL(PRINT_ON_INVOICE_FLAG,'z') = NVL(l_mod_sum_rec.PRINT_ON_INVOICE_FLAG,'z')
1059 AND NVL(REBATE_TRANSACTION_TYPE_CODE,'z') = NVL(l_mod_sum_rec.REBATE_TRANSACTION_TYPE_CODE,'z')
1060 AND NVL(DB_ESTIM_ACCRUAL_RATE,-99999) = NVL(l_mod_sum_rec.DB_ESTIM_ACCRUAL_RATE,-99999)
1061 AND NVL(PRICE_BY_FORMULA_ID,-99999) = NVL(l_mod_sum_rec.PRICE_BY_FORMULA_ID,-99999)
1062 AND NVL(GENERATE_USING_FORMULA_ID,-99999) = NVL(l_mod_sum_rec.GENERATE_USING_FORMULA_ID,-99999)
1063 AND NVL(REPRICE_FLAG,'z') = NVL(l_mod_sum_rec.REPRICE_FLAG,'z')
1064 AND NVL(DB_ACCRUAL_FLAG,'z') = NVL(l_mod_sum_rec.DB_ACCRUAL_FLAG,'z')
1065 AND NVL(PRICING_GROUP_SEQUENCE,-99999) = NVL(l_mod_sum_rec.PRICING_GROUP_SEQUENCE,-99999)
1066 AND NVL(INCOMPATIBILITY_GRP_CODE,'z') = NVL(l_mod_sum_rec.INCOMPATIBILITY_GRP_CODE,'z')
1067 AND NVL(LIST_LINE_NO,'z') = NVL(l_mod_sum_rec.LIST_LINE_NO,'z')
1068 AND NVL(PRODUCT_PRECEDENCE,-99999) = NVL(l_mod_sum_rec.PRODUCT_PRECEDENCE,-99999)
1069 AND NVL(PRICING_PHASE_ID,-99999) = NVL(l_mod_sum_rec.PRICING_PHASE_ID,-99999)
1070 AND NVL(DB_NUMBER_EXPIRATION_PERIODS,-99999) = NVL(l_mod_sum_rec.DB_NUMBER_EXPIRATION_PERIODS,-99999)
1071 AND NVL(DB_EXPIRATION_PERIOD_UOM,'z') = NVL(l_mod_sum_rec.DB_EXPIRATION_PERIOD_UOM,'z')
1072 AND NVL(ESTIM_GL_VALUE,-99999) = NVL(l_mod_sum_rec.ESTIM_GL_VALUE,-99999)
1073 AND NVL(DB_ACCRUAL_CONVERSION_RATE,-99999) = NVL(l_mod_sum_rec.DB_ACCRUAL_CONVERSION_RATE,-99999)
1074 AND NVL(BENEFIT_PRICE_LIST_LINE_ID,-99999) = NVL(l_mod_sum_rec.BENEFIT_PRICE_LIST_LINE_ID,-99999)
1075 AND NVL(PRORATION_TYPE_CODE,'z') = NVL(l_mod_sum_rec.PRORATION_TYPE_CODE,'z')
1076 AND NVL(DB_BENEFIT_QTY,-99999) = NVL(l_mod_sum_rec.DB_BENEFIT_QTY,-99999)
1077 AND NVL(DB_BENEFIT_UOM_CODE,'z') = NVL(l_mod_sum_rec.DB_BENEFIT_UOM_CODE,'z')
1078 AND NVL(CHARGE_TYPE_CODE,'z') = NVL(l_mod_sum_rec.CHARGE_TYPE_CODE,'z')
1079 AND NVL(CHARGE_SUBTYPE_CODE,'z') = NVL(l_mod_sum_rec.CHARGE_SUBTYPE_CODE,'z')
1080 AND NVL(BENEFIT_LIMIT,-99999) = NVL(l_mod_sum_rec.BENEFIT_LIMIT,-99999)
1081 AND NVL(PRODUCT_ATTRIBUTE_CONTEXT,'z') = NVL(l_mod_sum_rec.PRODUCT_ATTRIBUTE_CONTEXT,'z')
1082 AND NVL(PRODUCT_ATTR,'z') = NVL(l_mod_sum_rec.PRODUCT_ATTR,'z')
1083 AND NVL(PRODUCT_ATTR_VAL,'z') = NVL(l_mod_sum_rec.PRODUCT_ATTR_VAL,'z')
1084 AND NVL(PRODUCT_UOM_CODE,'z') = NVL(l_mod_sum_rec.PRODUCT_UOM_CODE,'z')
1085 AND NVL(COMPARISON_OPERATOR_CODE,'z') = NVL(l_mod_sum_rec.COMPARISON_OPERATOR_CODE,'z')
1086 AND NVL(PRICING_ATTRIBUTE_CONTEXT,'z') = NVL(l_mod_sum_rec.PRICING_ATTRIBUTE_CONTEXT,'z')
1087 AND NVL(PRICING_ATTR,'z') = NVL(l_mod_sum_rec.PRICING_ATTR,'z')
1088 AND NVL(PRICING_ATTR_VALUE_FROM,'z') = NVL(l_mod_sum_rec.PRICING_ATTR_VALUE_FROM,'z')
1089 AND NVL(PRICING_ATTR_VALUE_TO,'z') = NVL(l_mod_sum_rec.PRICING_ATTR_VALUE_TO,'z')
1090 AND NVL(PRICING_ATTRIBUTE_DATATYPE,'z') = NVL(l_mod_sum_rec.PRICING_ATTRIBUTE_DATATYPE,'z')
1091 AND NVL(PRODUCT_ATTRIBUTE_DATATYPE,'z') = NVL(l_mod_sum_rec.PRODUCT_ATTRIBUTE_DATATYPE,'z')
1092 AND NVL(EXCLUDER_FLAG,'z') = NVL(l_mod_sum_rec.EXCLUDER_FLAG,'z')
1093 AND NVL(ATTRIBUTE_GROUPING_NO,-99999) = NVL(l_mod_sum_rec.ATTRIBUTE_GROUPING_NO,-99999)
1094 AND NVL(TO_RLTD_MODIFIER_ID,'z') = NVL(l_mod_sum_rec.TO_RLTD_MODIFIER_ID,'z')
1095 AND NVL(RLTD_MODIFIER_GRP_NO,'z') = NVL(l_mod_sum_rec.RLTD_MODIFIER_GRP_NO,'z')
1096 AND NVL(RLTD_MODIFIER_GRP_TYPE,'z') = NVL(l_mod_sum_rec.RLTD_MODIFIER_GRP_TYPE,'z')
1097 AND NVL(RLTD_MODIFIER_ID,'z') = NVL(l_mod_sum_rec.RLTD_MODIFIER_ID,'z')
1098 AND NVL(PRORATION_TYPE,'z') = NVL(l_mod_sum_rec.PRORATION_TYPE,'z')
1099 AND NVL(PRICING_PHASE,'z') = NVL(l_mod_sum_rec.PRICING_PHASE,'z')
1100 AND NVL(INCOMPATIBILITY_GRP,'z') = NVL(l_mod_sum_rec.INCOMPATIBILITY_GRP,'z')
1101 AND NVL(MODIFIER_LEVEL,'z') = NVL(l_mod_sum_rec.MODIFIER_LEVEL,'z')
1102 AND NVL(LIST_LINE_TYPE,'z') = NVL(l_mod_sum_rec.LIST_LINE_TYPE,'z')
1103 AND NVL(PRICE_BREAK_TYPE,'z') = NVL(l_mod_sum_rec.PRICE_BREAK_TYPE,'z')
1104 AND NVL(CHARGE_NAME,'z') = NVL(l_mod_sum_rec.CHARGE_NAME,'z')
1105 AND NVL(FORMULA,'z') = NVL(l_mod_sum_rec.FORMULA,'z')
1106 AND NVL(ARITHMETIC_OPERATOR_TYPE,'z') = NVL(l_mod_sum_rec.ARITHMETIC_OPERATOR_TYPE,'z')
1107 AND NVL(NUMBER_EXPIRATION_PERIODS,-99999) = NVL(l_mod_sum_rec.NUMBER_EXPIRATION_PERIODS,-99999)
1108 AND NVL(EXPIRATION_PERIOD_UOM,'z') = NVL(l_mod_sum_rec.EXPIRATION_PERIOD_UOM,'z')
1109 AND NVL(COUP_NUMBER_EXPIRATION_PERIODS,-99999) = NVL(l_mod_sum_rec.COUP_NUMBER_EXPIRATION_PERIODS,-99999)
1110 AND NVL(COUP_EXPIRATION_PERIOD_UOM,'z') = NVL(l_mod_sum_rec.COUP_EXPIRATION_PERIOD_UOM,'z')
1111 AND NVL(BRK_NUMBER_EXPIRATION_PERIODS,-99999) = NVL(l_mod_sum_rec.BRK_NUMBER_EXPIRATION_PERIODS,-99999)
1112 AND NVL(BRK_EXPIRATION_PERIOD_UOM,'z') = NVL(l_mod_sum_rec.BRK_EXPIRATION_PERIOD_UOM,'z')
1113 AND NVL(REBATE_TRANSACTION_TYPE,'z') = NVL(l_mod_sum_rec.REBATE_TRANSACTION_TYPE,'z')
1114 AND NVL(BRK_REB_TRANSACTION_TYPE,'z') = NVL(l_mod_sum_rec.BRK_REB_TRANSACTION_TYPE,'z')
1115 AND NVL(BENEFIT_QTY,-99999) = NVL(l_mod_sum_rec.BENEFIT_QTY,-99999)
1116 AND NVL(BENEFIT_UOM_CODE,'z') = NVL(l_mod_sum_rec.BENEFIT_UOM_CODE,'z')
1117 AND NVL(COUP_BENEFIT_QTY,-99999) = NVL(l_mod_sum_rec.COUP_BENEFIT_QTY,-99999)
1118 AND NVL(COUP_BENEFIT_UOM_CODE,'z') = NVL(l_mod_sum_rec.COUP_BENEFIT_UOM_CODE,'z')
1119 AND NVL(COUP_LIST_LINE_NO,'z') = NVL(l_mod_sum_rec.COUP_LIST_LINE_NO,'z')
1120 AND NVL(ACCRUAL_FLAG,'z') = NVL(l_mod_sum_rec.ACCRUAL_FLAG,'z')
1121 AND NVL(BRK_ACCRUAL_FLAG,'z') = NVL(l_mod_sum_rec.BRK_ACCRUAL_FLAG,'z')
1122 AND NVL(ACCRUAL_CONVERSION_RATE,-99999) = NVL(l_mod_sum_rec.ACCRUAL_CONVERSION_RATE,-99999)
1123 AND NVL(COUP_ACCRUAL_CONVERSION_RATE,-99999) = NVL(l_mod_sum_rec.COUP_ACCRUAL_CONVERSION_RATE,-99999)
1124 AND NVL(COUP_ESTIM_ACCRUAL_RATE,-99999) = NVL(l_mod_sum_rec.COUP_ESTIM_ACCRUAL_RATE,-99999)
1125 AND NVL(BRK_ACCRUAL_CONVERSION_RATE,-99999) = NVL(l_mod_sum_rec.BRK_ACCRUAL_CONVERSION_RATE,-99999)
1126 AND NVL(ESTIM_ACCRUAL_RATE,-99999) = NVL(l_mod_sum_rec.ESTIM_ACCRUAL_RATE,-99999)
1127 AND NVL(BRK_ESTIM_ACCRUAL_RATE,-99999) = NVL(l_mod_sum_rec.BRK_ESTIM_ACCRUAL_RATE,-99999)
1128 AND NVL(BREAK_LINE_TYPE_CODE,'z') = NVL(l_mod_sum_rec.BREAK_LINE_TYPE_CODE,'z')
1129 AND NVL(BREAK_LINE_TYPE,'z') = NVL(l_mod_sum_rec.BREAK_LINE_TYPE,'z')
1130 AND NVL(PRODUCT_ID,'z') = NVL(l_mod_sum_rec.PRODUCT_ID,'z')
1131 AND NVL(DESCRIPTION,'z') = NVL(l_mod_sum_rec.DESCRIPTION,'z')
1132 AND NVL(PRICING_ATTR_SEG_NAME,'z') = NVL(l_mod_sum_rec.PRICING_ATTR_SEG_NAME,'z')
1133 AND NVL(PROD_ATTR_SEGMENT_NAME,'z') = NVL(l_mod_sum_rec.PROD_ATTR_SEGMENT_NAME,'z')
1134 AND NVL(RELATED_ITEM,'z') = NVL(l_mod_sum_rec.RELATED_ITEM,'z')
1135 AND NVL(SUBSTITUTION_ATTRIBUTE,'z') = NVL(l_mod_sum_rec.SUBSTITUTION_ATTRIBUTE,'z')
1136 AND NVL(SUBSTITUTION_VALUE,'z') = NVL(l_mod_sum_rec.SUBSTITUTION_VALUE,'z')
1137 AND NVL(SUB_SEGMENT_NAME,'z') = NVL(l_mod_sum_rec.SUB_SEGMENT_NAME,'z')
1138 AND NVL(PRODUCT_ATTRIBUTE_TYPE,'z') = NVL(l_mod_sum_rec.PRODUCT_ATTRIBUTE_TYPE,'z')
1139 AND NVL(PRODUCT_ATTR_VALUE,'z') = NVL(l_mod_sum_rec.PRODUCT_ATTR_VALUE,'z')
1140 AND NVL(PRICING_ATTRIBUTE,'z') = NVL(l_mod_sum_rec.PRICING_ATTRIBUTE,'z');
1141 */
1142 BEGIN
1143
1144 FOR l_mod_sum_rec IN c_mod_sum LOOP
1145 FOR l_list_line IN c_list_line_id(l_mod_sum_rec) LOOP
1146 l_index := l_index + 1;
1147 l_line_mapping_tbl(l_index).org_line_id := l_mod_sum_rec.list_line_id;
1148 l_line_mapping_tbl(l_index).new_line_id := l_list_line.list_line_id;
1149 END LOOP;
1150 END LOOP;
1151
1152 RETURN l_line_mapping_tbl;
1153
1154 END get_rltd_line_id;
1155
1156
1157 PROCEDURE copy_discount_line(p_org_line_id IN NUMBER
1158 ,p_parent_line_id IN NUMBER
1159 ,p_offer_id IN NUMBER
1160 ,x_new_line_id OUT NOCOPY NUMBER)
1161 IS
1162 CURSOR c_new_line_id IS
1163 SELECT ozf_offer_discount_lines_s.NEXTVAL
1164 FROM DUAL;
1165
1166 CURSOR c_line_id_exists(l_id NUMBER) IS
1167 SELECT 1
1168 FROM DUAL
1169 WHERE EXISTS (SELECT 1
1170 FROM ozf_offer_discount_lines
1171 WHERE offer_discount_line_id = l_id);
1172
1173 CURSOR c_line_detail IS
1174 SELECT *
1175 FROM ozf_offer_discount_lines
1176 WHERE offer_discount_line_id = p_org_line_id;
1177
1178 l_line_detail c_line_detail%ROWTYPE;
1179 l_count NUMBER;
1180 BEGIN
1181 LOOP
1182 l_count := NULL;
1183
1184 OPEN c_new_line_id;
1185 FETCH c_new_line_id INTO x_new_line_id;
1186 CLOSE c_new_line_id;
1187
1188 OPEN c_line_id_exists(x_new_line_id);
1189 FETCH c_line_id_exists INTO l_count;
1190 CLOSE c_line_id_exists;
1191
1192 EXIT WHEN l_count IS NULL;
1193 END LOOP;
1194
1195 OPEN c_line_detail;
1196 FETCH c_line_detail INTO l_line_detail;
1197 CLOSE c_line_detail;
1198
1199 INSERT INTO ozf_offer_discount_lines(offer_discount_line_id
1200 ,parent_discount_line_id
1201 ,volume_from
1202 ,volume_to
1203 ,volume_operator
1204 ,volume_type
1205 ,volume_break_type
1206 ,discount
1207 ,discount_type
1208 ,tier_type
1209 ,tier_level
1210 ,incompatibility_group
1211 ,precedence
1212 ,bucket
1213 ,scan_value
1214 ,scan_data_quantity
1215 ,scan_unit_forecast
1216 ,channel_id
1217 ,adjustment_flag
1218 ,start_date_active
1219 ,end_date_active
1220 ,uom_code
1221 ,creation_date
1222 ,created_by
1223 ,last_update_date
1224 ,last_updated_by
1225 ,last_update_login
1226 ,object_version_number
1227 ,offer_id)
1228 VALUES(x_new_line_id
1229 ,p_parent_line_id
1230 ,l_line_detail.volume_from
1231 ,l_line_detail.volume_to
1232 ,l_line_detail.volume_operator
1233 ,l_line_detail.volume_type
1234 ,l_line_detail.volume_break_type
1235 ,l_line_detail.discount
1236 ,l_line_detail.discount_type
1237 ,l_line_detail.tier_type
1238 ,l_line_detail.tier_level
1239 ,l_line_detail.incompatibility_group
1240 ,l_line_detail.precedence
1241 ,l_line_detail.bucket
1242 ,l_line_detail.scan_value
1243 ,l_line_detail.scan_data_quantity
1244 ,l_line_detail.scan_unit_forecast
1245 ,l_line_detail.channel_id
1246 ,l_line_detail.adjustment_flag
1247 ,l_line_detail.start_date_active
1248 ,l_line_detail.end_date_active
1249 ,l_line_detail.uom_code
1250 ,SYSDATE
1251 ,FND_GLOBAL.user_id
1252 ,SYSDATE
1253 ,FND_GLOBAL.user_id
1254 ,FND_GLOBAL.conc_login_id
1255 ,1
1256 ,p_offer_id);
1257 END copy_discount_line;
1258
1259
1260 PROCEDURE copy_discount_prod(p_org_prod_id IN NUMBER
1261 ,p_parent_prod_id IN NUMBER
1262 ,p_offer_id IN NUMBER
1263 ,p_new_line_id IN NUMBER
1264 ,x_new_prod_id OUT NOCOPY NUMBER)
1265 IS
1266 CURSOR c_new_prod_id IS
1267 SELECT ozf_offer_discount_products_s.NEXTVAL
1268 FROM DUAL;
1269
1270 CURSOR c_prod_id_exists(l_id NUMBER) IS
1271 SELECT 1
1272 FROM DUAL
1273 WHERE EXISTS (SELECT 1
1274 FROM ozf_offer_discount_products
1275 WHERE off_discount_product_id = l_id);
1276
1277 CURSOR c_product_detail IS
1278 SELECT *
1279 FROM ozf_offer_discount_products
1280 WHERE off_discount_product_id = p_org_prod_id;
1281
1282 l_product_detail c_product_detail%ROWTYPE;
1283 l_count NUMBER;
1284 BEGIN
1285 LOOP
1286 l_count := NULL;
1287
1288 OPEN c_new_prod_id;
1289 FETCH c_new_prod_id INTO x_new_prod_id;
1290 CLOSE c_new_prod_id;
1291
1292 OPEN c_prod_id_exists(x_new_prod_id);
1293 FETCH c_prod_id_exists INTO l_count;
1294 CLOSE c_prod_id_exists;
1295
1296 EXIT WHEN l_count IS NULL;
1297 END LOOP;
1298
1299 OPEN c_product_detail;
1300 FETCH c_product_detail INTO l_product_detail;
1301 CLOSE c_product_detail;
1302
1303 INSERT INTO ozf_offer_discount_products(off_discount_product_id
1304 ,product_level
1305 ,product_id
1306 ,excluder_flag
1307 ,uom_code
1308 ,start_date_active
1309 ,end_date_active
1310 ,offer_discount_line_id
1311 ,offer_id
1312 ,creation_date
1313 ,created_by
1314 ,last_update_date
1315 ,last_updated_by
1316 ,last_update_login
1317 ,object_version_number
1318 ,parent_off_disc_prod_id)
1319 VALUES(x_new_prod_id
1320 ,l_product_detail.product_level
1321 ,l_product_detail.product_id
1322 ,l_product_detail.excluder_flag
1323 ,l_product_detail.uom_code
1324 ,l_product_detail.start_date_active
1325 ,l_product_detail.end_date_active
1326 ,p_new_line_id
1327 ,p_offer_id
1328 ,SYSDATE
1329 ,FND_GLOBAL.user_id
1330 ,SYSDATE
1331 ,FND_GLOBAL.user_id
1332 ,FND_GLOBAL.conc_login_id
1333 ,1
1334 ,p_parent_prod_id);
1335 END copy_discount_prod;
1336
1337
1338 PROCEDURE copy_na_line_offer(p_source_object_id IN NUMBER
1339 ,p_new_offer_id IN NUMBER)
1340 IS
1341 CURSOR c_parent_lines IS
1342 SELECT offer_discount_line_id
1343 FROM ozf_offer_discount_lines
1344 WHERE offer_id = (SELECT offer_id
1345 FROM ozf_offers
1346 WHERE qp_list_header_id = p_source_object_id)
1347 AND parent_discount_line_id IS NULL; -- start from main line, then process multi-tier and excl
1348
1349 CURSOR c_tier_excl_lines(p_parent_line_id NUMBER) IS
1350 SELECT offer_discount_line_id
1351 FROM ozf_offer_discount_lines
1352 WHERE offer_id = (SELECT offer_id
1353 FROM ozf_offers
1354 WHERE qp_list_header_id = p_source_object_id)
1355 AND parent_discount_line_id = p_parent_line_id;
1356
1357 CURSOR c_product_id(p_line_id NUMBER) IS
1358 SELECT off_discount_product_id
1359 FROM ozf_offer_discount_products
1360 WHERE offer_discount_line_id = p_line_id;
1361
1362 l_new_line_id NUMBER;
1363 l_product_id NUMBER;
1364 l_dummy NUMBER;
1365 BEGIN
1366 FOR l_parent_line IN c_parent_lines LOOP
1367 copy_discount_line(p_org_line_id => l_parent_line.offer_discount_line_id
1368 ,p_parent_line_id => NULL
1369 ,p_offer_id => p_new_offer_id
1370 ,x_new_line_id => l_new_line_id); -- new line_id. will be used for prod, tier, and excl
1371
1372 OPEN c_product_id(l_parent_line.offer_discount_line_id);
1373 FETCH c_product_id INTO l_product_id;
1374 CLOSE c_product_id;
1375
1376 copy_discount_prod(p_org_prod_id => l_product_id
1377 ,p_parent_prod_id => NULL
1378 ,p_offer_id => p_new_offer_id
1379 ,p_new_line_id => l_new_line_id
1380 ,x_new_prod_id => l_dummy);
1381
1382 FOR l_tier_excl_line IN c_tier_excl_lines(l_parent_line.offer_discount_line_id) LOOP
1383 copy_discount_line(p_org_line_id => l_tier_excl_line.offer_discount_line_id
1384 ,p_parent_line_id => l_new_line_id
1385 ,p_offer_id => p_new_offer_id
1386 ,x_new_line_id => l_dummy);
1387
1388 END LOOP;
1389 END LOOP;
1390 END copy_na_line_offer;
1391
1392
1393 PROCEDURE copy_na_header_offer(p_source_object_id IN NUMBER
1394 ,p_new_offer_id IN NUMBER)
1395 IS
1396 CURSOR c_line_tiers IS
1397 SELECT offer_discount_line_id
1398 FROM ozf_offer_discount_lines
1399 WHERE offer_id = (SELECT offer_id
1400 FROM ozf_offers
1401 WHERE qp_list_header_id = p_source_object_id);
1402
1403 CURSOR c_parent_products IS
1404 SELECT off_discount_product_id
1405 FROM ozf_offer_discount_products
1406 WHERE offer_id = (SELECT offer_id
1407 FROM ozf_offers
1408 WHERE qp_list_header_id = p_source_object_id)
1409 AND parent_off_disc_prod_id IS NULL;
1410
1411 CURSOR c_excl_products(p_parent_prod_id NUMBER) IS
1412 SELECT off_discount_product_id
1413 FROM ozf_offer_discount_products
1414 WHERE offer_id = (SELECT offer_id
1415 FROM ozf_offers
1416 WHERE qp_list_header_id = p_source_object_id)
1417 AND parent_off_disc_prod_id = p_parent_prod_id;
1418
1419 l_new_prod_id NUMBER;
1420 l_dummy NUMBER;
1421 BEGIN
1422 FOR l_line_tier IN c_line_tiers LOOP
1423 copy_discount_line(p_org_line_id => l_line_tier.offer_discount_line_id
1424 ,p_parent_line_id => NULL
1425 ,p_offer_id => p_new_offer_id
1426 ,x_new_line_id => l_dummy);
1427 END LOOP;
1428
1429 FOR l_parent_product IN c_parent_products LOOP
1430 copy_discount_prod(p_org_prod_id => l_parent_product.off_discount_product_id
1431 ,p_parent_prod_id => NULL
1432 ,p_offer_id => p_new_offer_id
1433 ,p_new_line_id => -1
1434 ,x_new_prod_id => l_new_prod_id);
1435
1436 FOR l_excl_prod IN c_excl_products(l_parent_product.off_discount_product_id) LOOP
1437 copy_discount_prod(p_org_prod_id => l_excl_prod.off_discount_product_id
1438 ,p_parent_prod_id => l_new_prod_id
1439 ,p_offer_id => p_new_offer_id
1440 ,p_new_line_id => -1
1441 ,x_new_prod_id => l_dummy);
1442 END LOOP;
1443 END LOOP;
1444 END copy_na_header_offer;
1445
1446
1447 PROCEDURE copy_na_market_elig(p_source_object_id IN NUMBER
1448 ,p_new_offer_id IN NUMBER)
1449 IS
1450 CURSOR c_new_qual_id IS
1451 SELECT ozf_offer_qualifiers_s.NEXTVAL
1452 FROM DUAL;
1453
1454 CURSOR c_qual_id_exists(l_id NUMBER) IS
1455 SELECT 1
1456 FROM DUAL
1457 WHERE EXISTS (SELECT 1
1458 FROM ozf_offer_qualifiers
1459 WHERE qualifier_id = l_id);
1460
1461 CURSOR c_market_elig IS
1462 SELECT *
1463 FROM ozf_offer_qualifiers
1464 WHERE offer_id = (SELECT offer_id
1465 FROM ozf_offers
1466 WHERE qp_list_header_id = p_source_object_id);
1467
1468 l_new_qual_id NUMBER;
1469 l_count NUMBER;
1470 BEGIN
1471 LOOP
1472 l_count := NULL;
1473
1474 OPEN c_new_qual_id;
1475 FETCH c_new_qual_id INTO l_new_qual_id;
1476 CLOSE c_new_qual_id;
1477
1478 OPEN c_qual_id_exists(l_new_qual_id);
1479 FETCH c_qual_id_exists INTO l_count;
1480 CLOSE c_qual_id_exists;
1481
1482 EXIT WHEN l_count IS NULL;
1483 END LOOP;
1484
1485 FOR l_market_elig IN c_market_elig LOOP
1486 INSERT INTO ozf_offer_qualifiers(qualifier_id
1487 ,creation_date
1488 ,created_by
1489 ,last_update_date
1490 ,last_updated_by
1491 ,last_update_login
1492 ,qualifier_grouping_no
1493 ,qualifier_context
1494 ,qualifier_attribute
1495 ,qualifier_attr_value
1496 ,start_date_active
1497 ,end_date_active
1498 ,offer_id
1499 ,offer_discount_line_id
1500 ,context
1501 ,attribute1
1502 ,attribute2
1503 ,attribute3
1504 ,attribute4
1505 ,attribute5
1506 ,attribute6
1507 ,attribute7
1508 ,attribute8
1509 ,attribute9
1510 ,attribute10
1511 ,attribute11
1512 ,attribute12
1513 ,attribute13
1514 ,attribute14
1515 ,attribute15
1516 ,active_flag
1517 ,object_version_number)
1518 VALUES(l_new_qual_id
1519 ,SYSDATE
1520 ,FND_GLOBAL.user_id
1521 ,SYSDATE
1522 ,FND_GLOBAL.user_id
1523 ,FND_GLOBAL.conc_login_id
1524 ,l_market_elig.qualifier_grouping_no
1525 ,l_market_elig.qualifier_context
1526 ,l_market_elig.qualifier_attribute
1527 ,l_market_elig.qualifier_attr_value
1528 ,l_market_elig.start_date_active
1529 ,l_market_elig.end_date_active
1530 ,p_new_offer_id
1531 ,NULL
1532 ,l_market_elig.context
1533 ,l_market_elig.attribute1
1534 ,l_market_elig.attribute2
1535 ,l_market_elig.attribute3
1536 ,l_market_elig.attribute4
1537 ,l_market_elig.attribute5
1538 ,l_market_elig.attribute6
1539 ,l_market_elig.attribute7
1540 ,l_market_elig.attribute8
1541 ,l_market_elig.attribute9
1542 ,l_market_elig.attribute10
1543 ,l_market_elig.attribute11
1544 ,l_market_elig.attribute12
1545 ,l_market_elig.attribute13
1546 ,l_market_elig.attribute14
1547 ,l_market_elig.attribute15
1548 ,l_market_elig.active_flag
1549 ,1);
1550 END LOOP;
1551 END copy_na_market_elig;
1552
1553
1554
1555
1556 PROCEDURE copy_offer_detail(
1557 p_api_version IN NUMBER,
1558 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1559 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1560 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1561 x_return_status OUT NOCOPY VARCHAR2,
1562 x_msg_count OUT NOCOPY NUMBER,
1563 x_msg_data OUT NOCOPY VARCHAR2,
1564 p_source_object_id IN NUMBER,
1565 p_attributes_table IN AMS_CpyUtility_PVT.copy_attributes_table_type,
1566 p_copy_columns_table IN AMS_CpyUtility_PVT.copy_columns_table_type,
1567 x_new_object_id OUT NOCOPY NUMBER,
1568 p_custom_setup_id IN NUMBER)
1569 IS
1570
1571 l_api_name CONSTANT VARCHAR2(30) := 'copy_offer_detail';
1572 l_api_version_number CONSTANT NUMBER := 1.0;
1573 l_src_list_header_id NUMBER;
1574 l_new_list_header_id NUMBER;
1575 l_index1 NUMBER := 0;
1576 l_index2 NUMBER := 0;
1577 l_modifier_list_rec ozf_offer_pvt.modifier_list_rec_type;
1578
1579 l_return_status VARCHAR2(1);
1580
1581 l_errnum NUMBER;
1582 l_errcode VARCHAR2(80);
1583 l_errmsg VARCHAR2(3000);
1584 l_error_location NUMBER;
1585
1586 l_dummy NUMBER;
1587 l_errbuf VARCHAR2(3000);
1588 l_retcode NUMBER;
1589 l_count_ozf_code NUMBER;
1590 l_count_qp_code NUMBER;
1591 l_code_is_unique VARCHAR2(1);
1592 l_new_offer_id NUMBER;
1593 l_new_limit_id NUMBER;
1594 l_new_limit_line_id NUMBER;
1595 l_new_qual_id NUMBER;
1596 l_new_qual_line_id NUMBER;
1597 l_line_mapping_tbl line_mapping_tbl_type;
1598 l_limit_mapping_tbl line_mapping_tbl_type;
1599 l_qual_mapping_tbl line_mapping_tbl_type;
1600 l_new_modifier_id NUMBER;
1601 l_new_rltd_modifier_id NUMBER;
1602 l_related_lines_rec ozf_related_lines_pvt.related_lines_rec_type;
1603 l_related_deal_lines_id NUMBER;
1604 l_act_product_id NUMBER;
1605 l_temp_id NUMBER;
1606 l_prev_line_id NUMBER;
1607 l_default_team NUMBER;
1608
1609 l_budget_source_type VARCHAR2(30);
1610 l_budget_source_id NUMBER;
1611 l_status_code VARCHAR2(30);
1612 l_end_date_active DATE;
1613
1614 CURSOR c_list_header_detail IS
1615 SELECT *
1616 FROM qp_list_headers_b --Bug 13360969 changed table name to fetch global flag, previously fetching of global flag was not consistent.
1617 WHERE list_header_id = p_source_object_id;
1618 l_list_header_rec c_list_header_detail%ROWTYPE;
1619
1620 CURSOR c_offer_detail IS
1621 SELECT *
1622 FROM ozf_offers
1623 WHERE qp_list_header_id = p_source_object_id;
1624 l_offer_rec c_offer_detail%ROWTYPE;
1625
1626 CURSOR c_count_ozf_code(l_code VARCHAR2) IS
1627 SELECT 1
1628 FROM DUAL
1629 WHERE EXISTS (SELECT 1
1630 FROM ozf_offers
1631 WHERE offer_code = l_code);
1632
1633 CURSOR c_count_qp_code(l_code VARCHAR2) IS
1634 SELECT 1
1635 FROM DUAL
1636 WHERE EXISTS (SELECT 1
1637 FROM qp_list_headers_tl -- Bug 13360969 changed table name
1638 WHERE name = l_code);
1639
1640 CURSOR c_get_list_header_id(l_name VARCHAR2) IS
1641 SELECT list_header_id
1642 FROM qp_list_headers_tl -- Bug 13360969 changed table name to fetch list_header_id, fetching of list_header_id was not consisitent with qp_list_headers
1643 WHERE name = l_name;
1644
1645 CURSOR c_new_offer_id IS
1646 SELECT ozf_offers_s.NEXTVAL
1647 FROM DUAL;
1648
1649 CURSOR c_offer_id_exists (l_id IN NUMBER) IS
1650 SELECT 1
1651 FROM DUAL
1652 WHERE EXISTS (SELECT 1
1653 FROM ozf_offers
1654 WHERE offer_id = l_id);
1655
1656 CURSOR c_new_limit_id IS
1657 SELECT qp_limits_s.NEXTVAL
1658 FROM DUAL;
1659
1660 CURSOR c_limit_id_exists (l_id IN NUMBER) IS
1661 SELECT 1
1662 FROM DUAL
1663 WHERE EXISTS (SELECT 1
1664 FROM qp_limits
1665 WHERE limit_id = l_id);
1666
1667 CURSOR c_related_deal_lines IS
1668 SELECT *
1669 FROM ozf_related_deal_lines
1670 WHERE qp_list_header_id = p_source_object_id;
1671
1672 CURSOR c_vol_offer_tiers IS
1673 SELECT *
1674 FROM ozf_volume_offer_tiers
1675 WHERE qp_list_header_id = p_source_object_id;
1676 l_vol_offr_tier_rec ozf_vol_offr_pvt.vol_offr_tier_rec_type;
1677
1678 CURSOR c_act_products IS
1679 SELECT *
1680 FROM ams_act_products
1681 WHERE arc_act_product_used_by = 'OFFR'
1682 AND act_product_used_by_id = p_source_object_id;
1683 l_act_product_rec ams_actproduct_pvt.act_product_rec_type;
1684
1685 CURSOR c_excluded_products(l_act_prod_id NUMBER) IS
1686 SELECT *
1687 FROM ams_act_products
1688 WHERE arc_act_product_used_by = 'PROD'
1689 AND act_product_used_by_id = l_act_prod_id;
1690
1691 CURSOR c_offer_lines IS
1692 SELECT list_line_id
1693 FROM qp_list_lines
1694 WHERE list_header_id = p_source_object_id;
1695
1696 CURSOR c_line_limits IS
1697 SELECT *
1698 FROM qp_limits
1699 WHERE list_header_id = p_source_object_id
1700 ORDER BY list_line_id;
1701
1702 CURSOR c_contact_point IS
1703 SELECT *
1704 FROM ams_act_contact_points
1705 WHERE arc_contact_used_by = 'OFFR'
1706 AND act_contact_used_by_id = p_source_object_id;
1707 l_contact_point_rec ams_cnt_point_pvt.cnt_point_rec_type;
1708
1709 CURSOR c_market_elig IS
1710 SELECT qualifier_context
1711 ,qualifier_attribute
1712 ,qualifier_attr_value
1713 ,qualifier_attr_value_to
1714 ,comparison_operator_code
1715 ,qualifier_grouping_no
1716 ,list_line_id
1717 ,list_header_id
1718 ,start_date_active
1719 ,end_date_active
1720 FROM qp_qualifiers
1721 WHERE list_header_id = p_source_object_id;
1722 l_qualifier_tbl ozf_offer_pvt.qualifiers_tbl_type;
1723
1724 CURSOR c_adv_options IS
1725 SELECT modifier_level_code
1726 ,pricing_phase_id
1727 ,incompatibility_grp_code
1728 ,product_precedence
1729 ,pricing_group_sequence
1730 ,print_on_invoice_flag
1731 FROM qp_list_lines
1732 WHERE list_header_id = p_source_object_id;
1733 l_adv_options_rec Ozf_Offer_Pvt.Advanced_Option_Rec_Type;
1734
1735 -- bug 3747303
1736 CURSOR c_vol_off_discount IS
1737 SELECT discount_type_code, discount
1738 FROM ozf_volume_offer_tiers
1739 WHERE qp_list_header_id = p_source_object_id
1740 AND tier_value_from =
1741 (SELECT MIN(tier_value_from)
1742 FROM ozf_volume_offer_tiers
1743 WHERE qp_list_header_id = p_source_object_id);
1744 l_discount_type VARCHAR2(30);
1745 l_discount NUMBER;
1746 l_offer_id NUMBER;
1747
1748 --nepanda added cursor for Bug 10385923
1749 CURSOR c_edit_metrics_detail(p_user_or_role_id NUMBER) IS
1750 SELECT admin_flag
1751 FROM ams_act_access
1752 WHERE act_access_to_object_id = p_source_object_id
1753 AND user_or_role_id = p_user_or_role_id;
1754
1755 --rvkondur Bug#12403402
1756 CURSOR c_funds_all_details(l_fund_id VARCHAR2) IS
1757 SELECT status_code, end_date_active
1758 FROM ozf_funds_all_b
1759 WHERE fund_id = l_fund_id;
1760
1761 l_admin_flag VARCHAR2(2);
1762 BEGIN
1763 -- Standard Start of API savepoint
1764 SAVEPOINT copy_offer_detail;
1765
1766 -- Standard call to check for call compatibility.
1767 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1768 p_api_version,
1769 l_api_name,
1770 G_PKG_NAME)
1771 THEN
1772 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1773 END IF;
1774
1775 -- Initialize message list if p_init_msg_list is set to TRUE.
1776 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1777 FND_MSG_PUB.initialize;
1778 END IF;
1779
1780 -- Initialize API return status to SUCCESS
1781 x_return_status := FND_API.G_RET_STS_SUCCESS;
1782
1783 OPEN c_offer_detail;
1784 FETCH c_offer_detail INTO l_offer_rec;
1785 CLOSE c_offer_detail;
1786
1787 l_offer_rec.custom_setup_id := p_custom_setup_id;
1788
1789 -- getting values from UI
1790 AMS_CpyUtility_PVT.get_column_value ('newObjName', p_copy_columns_table, l_modifier_list_rec.description);
1791 AMS_CpyUtility_PVT.get_column_value ('offerCode', p_copy_columns_table, l_offer_rec.offer_code);
1792 --AMS_CpyUtility_PVT.get_column_value ('offerCode', p_copy_columns_table, l_modifier_list_rec.name);
1793 AMS_CpyUtility_PVT.get_column_value ('startDateActive', p_copy_columns_table, l_modifier_list_rec.start_date_active);
1794 AMS_CpyUtility_PVT.get_column_value ('endDateActive', p_copy_columns_table, l_modifier_list_rec.end_date_active);
1795 AMS_CpyUtility_PVT.get_column_value ('ownerId', p_copy_columns_table, l_offer_rec.owner_id);
1796 AMS_CpyUtility_PVT.get_column_value ('description', p_copy_columns_table, l_modifier_list_rec.comments);
1797
1798 /*ninarasi bug 12621645 - removed start date validation for copy offer.
1799 -- bug fix 2779988: validate start date and end date
1800 IF l_modifier_list_rec.start_date_active IS NOT NULL
1801 AND l_modifier_list_rec.start_date_active <> FND_API.G_MISS_DATE THEN
1802 IF l_modifier_list_rec.start_date_active < TRUNC(SYSDATE) AND l_offer_rec.offer_type <> 'NET_ACCRUAL' THEN
1803 Fnd_Message.SET_NAME('OZF','OZF_OFFR_STARTDATE_LT_SYSDATE');
1804 Fnd_Msg_Pub.ADD;
1805 RAISE FND_API.G_EXC_ERROR;
1806 END IF;
1807 END IF; -- end validation start date if
1808 */
1809
1810 IF l_modifier_list_rec.end_date_active IS NOT NULL
1811 AND l_modifier_list_rec.end_date_active <> FND_API.G_MISS_DATE THEN
1812 IF l_modifier_list_rec.end_date_active < TRUNC(SYSDATE) AND l_offer_rec.offer_type <> 'NET_ACCRUAL' THEN
1813 Fnd_Message.SET_NAME('OZF','OZF_OFFR_ENDDATE_LT_SYSDATE');
1814 Fnd_Msg_Pub.ADD;
1815 RAISE FND_API.G_EXC_ERROR;
1816 END IF;
1817 END IF; -- end end date validation if
1818
1819 IF l_offer_rec.offer_type IN ('SCAN_DATA', 'NET_ACCRUAL') OR (l_offer_rec.offer_type = 'LUMPSUM' AND l_offer_rec.custom_setup_id <> 110) THEN -- not applicable to soft fund
1820 IF l_modifier_list_rec.start_date_active IS NULL THEN
1821 ozf_utility_pvt.error_message('OZF_OFFR_NO_START_DATE');
1822 RAISE FND_API.G_EXC_ERROR;
1823 END IF;
1824 END IF; -- end scan data lumpsum start date validation if
1825
1826 IF l_modifier_list_rec.start_date_active IS NOT NULL
1827 AND l_modifier_list_rec.start_date_active <> FND_API.G_MISS_DATE
1828 AND l_modifier_list_rec.end_date_active IS NOT NULL
1829 AND l_modifier_list_rec.end_date_active <> FND_API.G_MISS_DATE
1830 THEN
1831 IF l_modifier_list_rec.start_date_active > l_modifier_list_rec.end_date_active THEN
1832 Fnd_Message.SET_NAME('QP','QP_STRT_DATE_BFR_END_DATE');
1833 Fnd_Msg_Pub.ADD;
1834 RAISE FND_API.G_EXC_ERROR;
1835 END IF;
1836 END IF; -- end start date before end date validation
1837
1838 -- validate source code
1839 IF l_offer_rec.offer_code IS NULL OR l_offer_rec.offer_code = FND_API.G_MISS_CHAR THEN
1840 LOOP
1841 l_count_ozf_code := 0;
1842 l_count_qp_code := 0;
1843
1844 l_offer_rec.offer_code := Ams_Sourcecode_Pvt.get_new_source_code (
1845 p_object_type => 'OFFR',
1846 p_custsetup_id => l_offer_rec.custom_setup_id,
1847 p_global_flag => Fnd_Api.g_false
1848 );
1849
1850 OPEN c_count_ozf_code(l_offer_rec.offer_code);
1851 FETCH c_count_ozf_code INTO l_count_ozf_code;
1852 CLOSE c_count_ozf_code;
1853
1854 OPEN c_count_qp_code(l_offer_rec.offer_code);
1855 FETCH c_count_qp_code INTO l_count_qp_code;
1856 CLOSE c_count_qp_code;
1857
1858 IF l_count_ozf_code = 0 AND l_count_qp_code = 0 THEN
1859 l_code_is_unique := 'Y';
1860 ELSE
1861 l_code_is_unique := 'N';
1862 END IF;
1863
1864 EXIT WHEN l_code_is_unique = 'Y';
1865 END LOOP;
1866 ELSE
1867 OPEN c_count_ozf_code(l_offer_rec.offer_code);
1868 FETCH c_count_ozf_code INTO l_count_ozf_code;
1869 CLOSE c_count_ozf_code;
1870
1871 OPEN c_count_qp_code(l_offer_rec.offer_code);
1872 FETCH c_count_qp_code INTO l_count_qp_code;
1873 CLOSE c_count_qp_code;
1874
1875 IF l_count_ozf_code > 0 OR l_count_qp_code > 0 THEN
1876 ozf_utility_pvt.error_message('OZF_OFFR_COPY_DUP_CODE');
1877 RAISE FND_API.G_EXC_ERROR;
1878 END IF;
1879 END IF; -- end validate source code
1880
1881 IF l_offer_rec.offer_type <> 'VOLUME_OFFER' THEN
1882 OPEN c_list_header_detail;
1883 FETCH c_list_header_detail INTO l_list_header_rec;
1884 CLOSE c_list_header_detail;
1885 -- copy header and lines, limits and related lines not copied yet
1886
1887 QP_COPY_MODIFIERS_PVT.Copy_Discounts
1888 (
1889 errbuf => l_errbuf
1890 , retcode => l_retcode
1891 , p_from_list_header_id => p_source_object_id
1892 , p_new_price_list_name => l_offer_rec.offer_code
1893 , p_description => l_modifier_list_rec.description
1894 , p_start_date_active => fnd_date.date_to_canonical(l_modifier_list_rec.start_date_active)
1895 , p_end_date_active => fnd_date.date_to_canonical(l_modifier_list_rec.end_date_active)
1896 , p_rounding_factor => NULL
1897 , p_effective_dates_flag => 'N'
1898 --added for moac bug 4673872
1899 , p_global_flag => l_list_header_rec.global_flag
1900 , p_org_id => l_list_header_rec.orig_org_id
1901 );
1902
1903 -- get new header id
1904 l_offer_rec.qp_list_header_id := NULL; --Bug 13360969, assigned to NULL to avoid previous value
1905 OPEN c_get_list_header_id(l_offer_rec.offer_code);
1906 FETCH c_get_list_header_id INTO l_offer_rec.qp_list_header_id;
1907 CLOSE c_get_list_header_id;
1908 --14-Jan-2003 JULOU Copy did not put the new description from the UI
1909 -- put description from UI
1910 UPDATE qp_list_headers_b
1911 SET comments = l_modifier_list_rec.comments
1912 WHERE list_header_id = l_offer_rec.qp_list_header_id;
1913
1914 -- insert into ozf_offers
1915 LOOP
1916 l_dummy := NULL;
1917 OPEN c_new_offer_id;
1918 FETCH c_new_offer_id INTO l_offer_rec.offer_id;
1919 CLOSE c_new_offer_id;
1920
1921 OPEN c_offer_id_exists(l_offer_rec.offer_id);
1922 FETCH c_offer_id_exists INTO l_dummy;
1923 CLOSE c_offer_id_exists;
1924 EXIT WHEN l_dummy IS NULL;
1925 END LOOP;
1926
1927 --rvkondur Bug#12403402
1928 l_budget_source_type := l_offer_rec.budget_source_type;
1929 l_budget_source_id := l_offer_rec.budget_source_id;
1930
1931 OPEN c_funds_all_details(l_budget_source_id);
1932 FETCH c_funds_all_details INTO l_status_code, l_end_date_active;
1933 CLOSE c_funds_all_details;
1934
1935 IF (l_status_code <> 'ACTIVE' OR (l_modifier_list_rec.start_date_active IS NOT NULL
1936 AND l_end_date_active < l_modifier_list_rec.start_date_active)) THEN
1937 l_budget_source_type := null;
1938 l_budget_source_id := null;
1939 END IF;
1940
1941 INSERT INTO ozf_offers
1942 (offer_id
1943 ,qp_list_header_id
1944 ,offer_type
1945 ,offer_code
1946 ,reusable
1947 ,custom_setup_id
1948 ,user_status_id
1949 ,owner_id
1950 ,object_version_number
1951 ,customer_reference
1952 ,buying_group_contact_id
1953 ,perf_date_from
1954 ,perf_date_to
1955 ,status_code
1956 ,order_value_discount_type
1957 ,modifier_level_code
1958 ,offer_amount
1959 ,lumpsum_amount
1960 ,lumpsum_payment_type
1961 ,security_group_id
1962 ,distribution_type
1963 ,budget_amount_fc
1964 ,budget_amount_tc
1965 ,transaction_currency_code
1966 ,functional_currency_code
1967 ,account_closed_flag
1968 ,activity_media_id
1969 ,qualifier_id
1970 ,qualifier_type
1971 ,budget_offer_yn
1972 ,creation_date
1973 ,created_by
1974 ,last_updated_by
1975 ,last_update_date
1976 ,last_update_login
1977 ,qualifier_deleted
1978 ,break_type
1979 ,volume_offer_type
1980 ,confidential_flag
1981 ,budget_source_type
1982 ,budget_source_id
1983 ,retroactive
1984 ,source_from_parent
1985 ,last_recal_date
1986 ,buyer_name
1987 ,tier_level
1988 ,na_rule_header_id
1989 ,autopay_flag
1990 ,autopay_days
1991 ,autopay_method
1992 ,autopay_party_attr
1993 ,autopay_party_id
1994 ,beneficiary_account_id
1995 ,sales_method_flag
1996 ,org_id
1997 ,fund_request_curr_code)
1998 VALUES
1999 (l_offer_rec.offer_id
2000 ,l_offer_rec.qp_list_header_id
2001 ,l_offer_rec.offer_type
2002 ,l_offer_rec.offer_code
2003 ,l_offer_rec.reusable
2004 ,l_offer_rec.custom_setup_id
2005 ,ozf_utility_pvt.get_default_user_status('OZF_OFFER_STATUS','DRAFT')
2006 ,l_offer_rec.owner_id
2007 ,1
2008 ,l_offer_rec.customer_reference
2009 ,l_offer_rec.buying_group_contact_id
2010 ,l_offer_rec.perf_date_from
2011 ,l_offer_rec.perf_date_to
2012 ,'DRAFT'
2013 ,l_offer_rec.order_value_discount_type
2014 ,l_offer_rec.modifier_level_code
2015 ,l_offer_rec.offer_amount
2016 ,l_offer_rec.lumpsum_amount
2017 ,l_offer_rec.lumpsum_payment_type
2018 ,l_offer_rec.security_group_id
2019 ,l_offer_rec.distribution_type
2020 ,l_offer_rec.budget_amount_fc
2021 ,l_offer_rec.budget_amount_tc
2022 ,l_offer_rec.transaction_currency_code
2023 ,l_offer_rec.functional_currency_code
2024 ,'N'
2025 ,l_offer_rec.activity_media_id
2026 ,l_offer_rec.qualifier_id
2027 ,l_offer_rec.qualifier_type
2028 ,DECODE(l_offer_rec.custom_setup_id, 101, 'Y', 108, 'Y', 'N')--l_offer_rec.budget_offer_yn
2029 ,SYSDATE
2030 ,FND_GLOBAL.user_id
2031 ,FND_GLOBAL.user_id
2032 ,SYSDATE
2033 ,FND_GLOBAL.conc_login_id
2034 ,NULL
2035 ,l_offer_rec.break_type
2036 ,l_offer_rec.volume_offer_type
2037 ,l_offer_rec.confidential_flag
2038 ,l_budget_source_type
2039 ,l_budget_source_id
2040 ,l_offer_rec.retroactive
2041 ,l_offer_rec.source_from_parent
2042 ,l_modifier_list_rec.start_date_active -- default last_recal_date to offer start date
2043 ,l_offer_rec.buyer_name
2044 ,l_offer_rec.tier_level
2045 ,l_offer_rec.na_rule_header_id
2046 ,l_offer_rec.autopay_flag
2047 ,l_offer_rec.autopay_days
2048 ,l_offer_rec.autopay_method
2049 ,l_offer_rec.autopay_party_attr
2050 ,l_offer_rec.autopay_party_id
2051 ,l_offer_rec.beneficiary_account_id
2052 ,l_offer_rec.sales_method_flag
2053 ,l_offer_rec.org_id
2054 ,NVL(l_offer_rec.transaction_currency_code, fnd_profile.value('JTF_PROFILE_DEFAULT_CURRENCY')));
2055 -- end insert into ozf_offers
2056
2057 --7627663,insert the offer details to AMS_SOURCE_CODES for all offers except volume offer.
2058 AMS_CampaignRules_PVT.push_source_code(
2059 l_offer_rec.offer_code,
2060 'OFFR',
2061 l_offer_rec.qp_list_header_id
2062 );
2063
2064
2065 -- create access for new offer owner
2066 INSERT INTO ams_act_access
2067 (activity_access_id
2068 ,last_update_date
2069 ,last_updated_by
2070 ,creation_date
2071 ,created_by
2072 ,act_access_to_object_id
2073 ,arc_act_access_to_object
2074 ,user_or_role_id
2075 ,arc_user_or_role_type
2076 ,last_update_login
2077 ,object_version_number
2078 ,active_from_date
2079 ,admin_flag
2080 ,approver_flag
2081 ,active_to_date
2082 ,security_group_id
2083 ,delete_flag
2084 ,owner_flag)
2085 VALUES
2086 (ams_act_access_s.NEXTVAL
2087 ,SYSDATE
2088 ,FND_GLOBAL.user_id
2089 ,SYSDATE
2090 ,FND_GLOBAL.user_id
2091 ,l_offer_rec.qp_list_header_id
2092 ,'OFFR'
2093 ,l_offer_rec.owner_id
2094 ,'USER'
2095 ,FND_GLOBAL.conc_login_id
2096 ,1
2097 ,SYSDATE
2098 ,'Y' --NULL nepanda For Bug 10385923. Setting Admin Flag for OWNER to Y, as it should be 'Y' by default
2099 ,NULL
2100 ,NULL
2101 ,NULL
2102 ,'N'
2103 ,'Y');
2104 -- end create access for owner
2105
2106 -- create access in ams_act_access_denorm for new offer owner
2107 INSERT INTO ams_act_access_denorm
2108 (access_denorm_id
2109 ,object_type
2110 ,object_id
2111 ,resource_id
2112 ,edit_metrics_yn
2113 ,source_code
2114 ,access_type
2115 ,creation_date
2116 ,last_update_date
2117 ,last_update_login
2118 ,last_updated_by
2119 ,created_by)
2120 VALUES
2121 (ams_act_access_denorm_s.NEXTVAL
2122 ,'OFFR'
2123 ,l_offer_rec.qp_list_header_id
2124 ,l_offer_rec.owner_id
2125 ,'Y'
2126 ,l_offer_rec.offer_code
2127 ,NULL
2128 ,SYSDATE
2129 ,SYSDATE
2130 ,FND_GLOBAL.conc_login_id
2131 ,FND_GLOBAL.user_id
2132 ,FND_GLOBAL.user_id);
2133 -- end create access in ams_act_access_denorm
2134
2135 -- create access for default team
2136 l_default_team := FND_PROFILE.value('OZF_DEFAULT_OFFER_TEAM');
2137
2138 IF l_default_team IS NOT NULL AND l_default_team <> FND_API.G_MISS_NUM THEN
2139
2140 --nepanda For Bug 10385923. Getting the admin flag for default team and the offer from which this offer is getting copied.
2141 OPEN c_edit_metrics_detail(l_default_team);
2142 FETCH c_edit_metrics_detail INTO l_admin_flag;
2143 CLOSE c_edit_metrics_detail;
2144
2145 INSERT INTO ams_act_access
2146 (activity_access_id
2147 ,last_update_date
2148 ,last_updated_by
2149 ,creation_date
2150 ,created_by
2151 ,act_access_to_object_id
2152 ,arc_act_access_to_object
2153 ,user_or_role_id
2154 ,arc_user_or_role_type
2155 ,last_update_login
2156 ,object_version_number
2157 ,active_from_date
2158 ,admin_flag
2159 ,approver_flag
2160 ,active_to_date
2161 ,security_group_id
2162 ,delete_flag
2163 ,owner_flag)
2164 VALUES
2165 (ams_act_access_s.NEXTVAL
2166 ,SYSDATE
2167 ,FND_GLOBAL.user_id
2168 ,SYSDATE
2169 ,FND_GLOBAL.user_id
2170 ,l_offer_rec.qp_list_header_id
2171 ,'OFFR'
2172 ,l_default_team
2173 ,'GROUP'
2174 ,FND_GLOBAL.conc_login_id
2175 ,1
2176 ,SYSDATE
2177 ,l_admin_flag --nepanda For Bug 10385923. Updating the admin flag for the default group with the admin flag of the offer from which this offer is getting copied
2178 ,NULL
2179 ,NULL
2180 ,NULL
2181 ,'N'
2182 ,'N');
2183 END IF;
2184 -- end create default access for default team
2185
2186 -- copy NET_ACCRUAL offer
2187 IF l_offer_rec.offer_type = 'NET_ACCRUAL' THEN
2188 IF l_offer_rec.tier_level = 'LINE' THEN -- line based discount
2189 copy_na_line_offer(p_source_object_id => p_source_object_id
2190 ,p_new_offer_id => l_offer_rec.offer_id);
2191 ELSIF l_offer_rec.tier_level = 'HEADER' THEN-- tier based discount
2192 copy_na_header_offer(p_source_object_id => p_source_object_id
2193 ,p_new_offer_id => l_offer_rec.offer_id);
2194 END IF;
2195 END IF;
2196
2197 -- build list line id mapping table
2198 l_line_mapping_tbl := get_rltd_line_id(p_source_object_id, l_offer_rec.qp_list_header_id);
2199 l_limit_mapping_tbl := get_rltd_line_id(p_source_object_id, l_offer_rec.qp_list_header_id);
2200 -- end build mapping table
2201
2202 -- process limits
2203 FOR l_line_limits IN c_line_limits LOOP
2204 IF l_line_limits.list_line_id = -1 THEN
2205 LOOP
2206 --l_new_limit_id := NULL;
2207 l_dummy := NULL;
2208 OPEN c_new_limit_id;
2209 FETCH c_new_limit_id INTO l_new_limit_id;
2210 CLOSE c_new_limit_id;
2211
2212 OPEN c_limit_id_exists(l_new_limit_id);
2213 FETCH c_limit_id_exists INTO l_dummy;
2214 CLOSE c_limit_id_exists;
2215 EXIT WHEN l_dummy IS NULL;
2216 END LOOP;
2217
2218 INSERT INTO qp_limits
2219 (limit_id
2220 ,creation_date
2221 ,created_by
2222 ,last_update_date
2223 ,last_updated_by
2224 ,last_update_login
2225 ,program_application_id
2226 ,program_id
2227 ,program_update_date
2228 ,request_id
2229 ,list_header_id
2230 ,list_line_id
2231 ,limit_number
2232 ,basis
2233 ,organization_flag
2234 ,limit_level_code
2235 ,limit_exceed_action_code
2236 ,limit_hold_flag
2237 ,multival_attr1_type
2238 ,multival_attr1_context
2239 ,multival_attribute1
2240 ,multival_attr1_datatype
2241 ,multival_attr2_type
2242 ,multival_attr2_context
2243 ,multival_attribute2
2244 ,multival_attr2_datatype
2245 ,amount
2246 ,context
2247 ,attribute1
2248 ,attribute2
2249 ,attribute3
2250 ,attribute4
2251 ,attribute5
2252 ,attribute6
2253 ,attribute7
2254 ,attribute8
2255 ,attribute9
2256 ,attribute10
2257 ,attribute11
2258 ,attribute12
2259 ,attribute13
2260 ,attribute14
2261 ,attribute15
2262 ,each_attr_exists
2263 ,non_each_attr_count
2264 ,total_attr_count)
2265 VALUES
2266 (l_new_limit_id
2267 ,SYSDATE
2268 ,FND_GLOBAL.user_id
2269 ,SYSDATE
2270 ,FND_GLOBAL.user_id
2271 ,FND_GLOBAL.conc_login_id
2272 ,l_line_limits.program_application_id
2273 ,l_line_limits.program_id
2274 ,SYSDATE
2275 ,NULL
2276 ,l_offer_rec.qp_list_header_id
2277 ,-1
2278 ,l_line_limits.limit_number
2279 ,l_line_limits.basis
2280 ,l_line_limits.organization_flag
2281 ,l_line_limits.limit_level_code
2282 ,l_line_limits.limit_exceed_action_code
2283 ,l_line_limits.limit_hold_flag
2284 ,l_line_limits.multival_attr1_type
2285 ,l_line_limits.multival_attr1_context
2286 ,l_line_limits.multival_attribute1
2287 ,l_line_limits.multival_attr1_datatype
2288 ,l_line_limits.multival_attr2_type
2289 ,l_line_limits.multival_attr2_context
2290 ,l_line_limits.multival_attribute2
2291 ,l_line_limits.multival_attr2_datatype
2292 ,l_line_limits.amount
2293 ,l_line_limits.context
2294 ,l_line_limits.attribute1
2295 ,l_line_limits.attribute2
2296 ,l_line_limits.attribute3
2297 ,l_line_limits.attribute4
2298 ,l_line_limits.attribute5
2299 ,l_line_limits.attribute6
2300 ,l_line_limits.attribute7
2301 ,l_line_limits.attribute8
2302 ,l_line_limits.attribute9
2303 ,l_line_limits.attribute10
2304 ,l_line_limits.attribute11
2305 ,l_line_limits.attribute12
2306 ,l_line_limits.attribute13
2307 ,l_line_limits.attribute14
2308 ,l_line_limits.attribute15
2309 ,l_line_limits.each_attr_exists
2310 ,l_line_limits.non_each_attr_count
2311 ,l_line_limits.total_attr_count);
2312 ELSE
2313 IF l_prev_line_id <> l_line_limits.list_line_id THEN
2314 -- remove assigned source line id from mapping tablel
2315 FOR i IN 1..l_limit_mapping_tbl.count LOOP
2316 IF l_new_limit_line_id = l_limit_mapping_tbl(i).new_line_id THEN
2317 l_limit_mapping_tbl(i) := NULL;
2318 END IF;
2319 END LOOP;
2320 END IF;
2321
2322 l_new_limit_line_id := NULL;
2323
2324 FOR i IN 1..l_limit_mapping_tbl.count LOOP
2325 IF l_line_limits.list_line_id = l_limit_mapping_tbl(i).org_line_id THEN
2326 l_new_limit_line_id := l_limit_mapping_tbl(i).new_line_id;
2327 -- find matching line, populate limits
2328 LOOP
2329 --l_new_limit_id := NULL;
2330 l_dummy := NULL;
2331 OPEN c_new_limit_id;
2332 FETCH c_new_limit_id INTO l_new_limit_id;
2333 CLOSE c_new_limit_id;
2334
2335 OPEN c_limit_id_exists(l_new_limit_id);
2336 FETCH c_limit_id_exists INTO l_dummy;
2337 CLOSE c_limit_id_exists;
2338 EXIT WHEN l_dummy IS NULL;
2339 END LOOP;
2340
2341 INSERT INTO qp_limits
2342 (limit_id
2343 ,creation_date
2344 ,created_by
2345 ,last_update_date
2346 ,last_updated_by
2347 ,last_update_login
2348 ,program_application_id
2349 ,program_id
2350 ,program_update_date
2351 ,request_id
2352 ,list_header_id
2353 ,list_line_id
2354 ,limit_number
2355 ,basis
2356 ,organization_flag
2357 ,limit_level_code
2358 ,limit_exceed_action_code
2359 ,limit_hold_flag
2360 ,multival_attr1_type
2361 ,multival_attr1_context
2362 ,multival_attribute1
2363 ,multival_attr1_datatype
2364 ,multival_attr2_type
2365 ,multival_attr2_context
2366 ,multival_attribute2
2367 ,multival_attr2_datatype
2368 ,amount
2369 ,context
2370 ,attribute1
2371 ,attribute2
2372 ,attribute3
2373 ,attribute4
2374 ,attribute5
2375 ,attribute6
2376 ,attribute7
2377 ,attribute8
2378 ,attribute9
2379 ,attribute10
2380 ,attribute11
2381 ,attribute12
2382 ,attribute13
2383 ,attribute14
2384 ,attribute15
2385 ,each_attr_exists
2386 ,non_each_attr_count
2387 ,total_attr_count)
2388 VALUES
2389 (l_new_limit_id
2390 ,SYSDATE
2391 ,FND_GLOBAL.user_id
2392 ,SYSDATE
2393 ,FND_GLOBAL.user_id
2394 ,FND_GLOBAL.conc_login_id
2395 ,l_line_limits.program_application_id
2396 ,l_line_limits.program_id
2397 ,SYSDATE
2398 ,NULL
2399 ,l_offer_rec.qp_list_header_id
2400 ,l_new_limit_line_id
2401 ,l_line_limits.limit_number
2402 ,l_line_limits.basis
2403 ,l_line_limits.organization_flag
2404 ,l_line_limits.limit_level_code
2405 ,l_line_limits.limit_exceed_action_code
2406 ,l_line_limits.limit_hold_flag
2407 ,l_line_limits.multival_attr1_type
2408 ,l_line_limits.multival_attr1_context
2409 ,l_line_limits.multival_attribute1
2410 ,l_line_limits.multival_attr1_datatype
2411 ,l_line_limits.multival_attr2_type
2412 ,l_line_limits.multival_attr2_context
2413 ,l_line_limits.multival_attribute2
2414 ,l_line_limits.multival_attr2_datatype
2415 ,l_line_limits.amount
2416 ,l_line_limits.context
2417 ,l_line_limits.attribute1
2418 ,l_line_limits.attribute2
2419 ,l_line_limits.attribute3
2420 ,l_line_limits.attribute4
2421 ,l_line_limits.attribute5
2422 ,l_line_limits.attribute6
2423 ,l_line_limits.attribute7
2424 ,l_line_limits.attribute8
2425 ,l_line_limits.attribute9
2426 ,l_line_limits.attribute10
2427 ,l_line_limits.attribute11
2428 ,l_line_limits.attribute12
2429 ,l_line_limits.attribute13
2430 ,l_line_limits.attribute14
2431 ,l_line_limits.attribute15
2432 ,l_line_limits.each_attr_exists
2433 ,l_line_limits.non_each_attr_count
2434 ,l_line_limits.total_attr_count);
2435 EXIT;
2436 END IF;
2437 END LOOP;
2438 END IF;
2439 l_prev_line_id := l_line_limits.list_line_id;
2440 END LOOP;
2441 -- end processing limits
2442
2443 IF l_offer_rec.offer_type = 'DEAL' THEN
2444 -- process related lines for trade deal offers
2445 FOR l_related_deal_line IN c_related_deal_lines LOOP
2446 l_new_modifier_id := NULL;
2447 l_new_rltd_modifier_id := NULL;
2448
2449 -- get new source line id
2450 FOR i IN 1..l_line_mapping_tbl.count LOOP
2451 IF l_related_deal_line.modifier_id = l_line_mapping_tbl(i).org_line_id THEN
2452 l_new_modifier_id := l_line_mapping_tbl(i).new_line_id;
2453 EXIT;
2454 END IF;
2455 END LOOP;
2456
2457 -- remove assigned source line id from mapping table
2458 FOR i IN 1..l_line_mapping_tbl.count LOOP
2459 IF l_new_modifier_id = l_line_mapping_tbl(i).new_line_id THEN
2460 l_line_mapping_tbl(i) := NULL;
2461 END IF;
2462 END LOOP;
2463
2464 IF l_related_deal_line.related_modifier_id IS NOT NULL
2465 AND l_related_deal_line.related_modifier_id <> FND_API.G_MISS_NUM
2466 THEN
2467 -- get new rltd line id
2468 FOR i IN 1..l_line_mapping_tbl.count LOOP
2469 IF l_related_deal_line.related_modifier_id = l_line_mapping_tbl(i).org_line_id THEN
2470 l_new_rltd_modifier_id := l_line_mapping_tbl(i).new_line_id;
2471 EXIT;
2472 END IF;
2473 END LOOP;
2474
2475 -- remove assigned rltd line id from mapping table
2476 FOR i IN 1..l_line_mapping_tbl.count LOOP
2477 IF l_new_rltd_modifier_id = l_line_mapping_tbl(i).new_line_id THEN
2478 l_line_mapping_tbl(i) := NULL;
2479 END IF;
2480 END LOOP;
2481 ELSE
2482 l_new_rltd_modifier_id := NULL;
2483 END IF;
2484
2485 -- create related line info in ozf_related_deal_lines
2486 l_related_lines_rec.modifier_id := l_new_modifier_id;
2487 l_related_lines_rec.related_modifier_id := l_new_rltd_modifier_id;
2488 l_related_lines_rec.object_version_number := 1;
2489 l_related_lines_rec.estimated_qty_is_max := l_related_deal_line.estimated_qty_is_max;
2490 l_related_lines_rec.estimated_amount_is_max := l_related_deal_line.estimated_amount_is_max;
2491 l_related_lines_rec.estimated_qty := l_related_deal_line.estimated_qty;
2492 l_related_lines_rec.estimated_amount := l_related_deal_line.estimated_amount;
2493 l_related_lines_rec.estimate_qty_uom := l_related_deal_line.estimate_qty_uom;
2494 l_related_lines_rec.qp_list_header_id := l_offer_rec.qp_list_header_id;
2495
2496 ozf_Related_Lines_PVT.Create_related_lines
2497 (p_api_version_number => 1.0
2498 ,x_return_status => x_return_Status
2499 ,x_msg_count => x_msg_count
2500 ,x_msg_data => x_msg_data
2501 ,p_related_lines_rec => l_related_lines_rec
2502 ,x_related_deal_lines_id => l_related_deal_lines_id);
2503 END LOOP;
2504 -- end processing related lines for trade deal
2505 --END IF;
2506
2507 ELSIF l_offer_rec.offer_type IN ('LUMPSUM', 'SCAN_DATA') THEN
2508 -- copy lines for lumpsum and scan data
2509 FOR l_act_product IN c_act_products LOOP
2510 l_act_product_rec.act_product_used_by_id := l_offer_rec.qp_list_header_id;
2511 l_act_product_rec.arc_act_product_used_by := 'OFFR';
2512 l_act_product_rec.product_sale_type := l_act_product.product_sale_type;
2513 l_act_product_rec.primary_product_flag := l_act_product.primary_product_flag;
2514 l_act_product_rec.enabled_flag := l_act_product.enabled_flag;
2515 l_act_product_rec.excluded_flag := l_act_product.excluded_flag;
2516 l_act_product_rec.category_id := l_act_product.category_id;
2517 l_act_product_rec.category_set_id := l_act_product.category_set_id;
2518 l_act_product_rec.organization_id := l_act_product.organization_id;
2519 l_act_product_rec.inventory_item_id := l_act_product.inventory_item_id;
2520 l_act_product_rec.level_type_code := l_act_product.level_type_code;
2521 l_act_product_rec.line_lumpsum_amount := l_act_product.line_lumpsum_amount;
2522 l_act_product_rec.line_lumpsum_qty := l_act_product.line_lumpsum_qty;
2523 l_act_product_rec.attribute_category := l_act_product.attribute_category;
2524 l_act_product_rec.attribute1 := l_act_product.attribute1;
2525 l_act_product_rec.attribute2 := l_act_product.attribute2;
2526 l_act_product_rec.attribute3 := l_act_product.attribute3;
2527 l_act_product_rec.attribute4 := l_act_product.attribute4;
2528 l_act_product_rec.attribute5 := l_act_product.attribute5;
2529 l_act_product_rec.attribute6 := l_act_product.attribute6;
2530 l_act_product_rec.attribute7 := l_act_product.attribute7;
2531 l_act_product_rec.attribute8 := l_act_product.attribute8;
2532 l_act_product_rec.attribute9 := l_act_product.attribute9;
2533 l_act_product_rec.attribute10 := l_act_product.attribute10;
2534 l_act_product_rec.attribute11 := l_act_product.attribute11;
2535 l_act_product_rec.attribute12 := l_act_product.attribute12;
2536 l_act_product_rec.attribute13 := l_act_product.attribute13;
2537 l_act_product_rec.attribute14 := l_act_product.attribute14;
2538 l_act_product_rec.attribute15 := l_act_product.attribute15;
2539 l_act_product_rec.channel_id := l_act_product.channel_id;
2540 l_act_product_rec.uom_code := l_act_product.uom_code;
2541 l_act_product_rec.quantity := l_act_product.quantity;
2542 l_act_product_rec.scan_value := l_act_product.scan_value;
2543 l_act_product_rec.scan_unit_forecast := l_act_product.scan_unit_forecast;
2544 l_act_product_rec.adjustment_flag := l_act_product.adjustment_flag;
2545
2546 ams_ActProduct_PVT.Create_Act_Product(p_api_version => p_api_version
2547 ,p_init_msg_list => p_init_msg_list
2548 ,p_commit => p_commit
2549 ,p_validation_level => p_validation_level
2550 ,x_return_status => x_return_status
2551 ,x_msg_count => x_msg_count
2552 ,x_msg_data => x_msg_data
2553 ,p_act_Product_rec => l_act_product_rec
2554 ,x_act_Product_id => l_act_product_id);
2555
2556 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2557 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2558 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
2559 RAISE FND_API.G_EXC_ERROR;
2560 END IF;
2561
2562 l_temp_id := l_act_product_id;
2563
2564 -- copy exclusion
2565 FOR l_excluded_product IN c_excluded_products(l_act_product.activity_product_id) LOOP
2566 l_act_product_rec := NULL;
2567
2568 l_act_product_rec.act_product_used_by_id := l_temp_id;
2569 l_act_product_rec.arc_act_product_used_by := 'PROD';
2570 l_act_product_rec.product_sale_type := l_excluded_product.product_sale_type;
2571 l_act_product_rec.primary_product_flag := l_excluded_product.primary_product_flag;
2572 l_act_product_rec.enabled_flag := l_excluded_product.enabled_flag;
2573 l_act_product_rec.excluded_flag := l_excluded_product.excluded_flag;
2574 l_act_product_rec.category_id := l_excluded_product.category_id;
2575 l_act_product_rec.category_set_id := l_excluded_product.category_set_id;
2576 l_act_product_rec.organization_id := l_excluded_product.organization_id;
2577 l_act_product_rec.inventory_item_id := l_excluded_product.inventory_item_id;
2578 l_act_product_rec.level_type_code := l_excluded_product.level_type_code;
2579 l_act_product_rec.line_lumpsum_amount := l_excluded_product.line_lumpsum_amount;
2580 l_act_product_rec.line_lumpsum_qty := l_excluded_product.line_lumpsum_qty;
2581 l_act_product_rec.attribute_category := l_excluded_product.attribute_category;
2582 l_act_product_rec.attribute1 := l_excluded_product.attribute1;
2583 l_act_product_rec.attribute2 := l_excluded_product.attribute2;
2584 l_act_product_rec.attribute3 := l_excluded_product.attribute3;
2585 l_act_product_rec.attribute4 := l_excluded_product.attribute4;
2586 l_act_product_rec.attribute5 := l_excluded_product.attribute5;
2587 l_act_product_rec.attribute6 := l_excluded_product.attribute6;
2588 l_act_product_rec.attribute7 := l_excluded_product.attribute7;
2589 l_act_product_rec.attribute8 := l_excluded_product.attribute8;
2590 l_act_product_rec.attribute9 := l_excluded_product.attribute9;
2591 l_act_product_rec.attribute10 := l_excluded_product.attribute10;
2592 l_act_product_rec.attribute11 := l_excluded_product.attribute11;
2593 l_act_product_rec.attribute12 := l_excluded_product.attribute12;
2594 l_act_product_rec.attribute13 := l_excluded_product.attribute13;
2595 l_act_product_rec.attribute14 := l_excluded_product.attribute14;
2596 l_act_product_rec.attribute15 := l_excluded_product.attribute15;
2597 l_act_product_rec.channel_id := l_excluded_product.channel_id;
2598 l_act_product_rec.uom_code := l_excluded_product.uom_code;
2599 l_act_product_rec.quantity := l_excluded_product.quantity;
2600 l_act_product_rec.scan_value := l_excluded_product.scan_value;
2601 l_act_product_rec.scan_unit_forecast := l_excluded_product.scan_unit_forecast;
2602 l_act_product_rec.adjustment_flag := l_excluded_product.adjustment_flag;
2603 ams_ActProduct_PVT.Create_Act_Product(p_api_version => p_api_version
2604 ,p_init_msg_list => p_init_msg_list
2605 ,p_commit => p_commit
2606 ,p_validation_level => p_validation_level
2607 ,x_return_status => x_return_status
2608 ,x_msg_count => x_msg_count
2609 ,x_msg_data => x_msg_data
2610 ,p_act_Product_rec => l_act_product_rec
2611 ,x_act_Product_id => l_act_product_id);
2612
2613 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2614 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2615 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
2616 RAISE FND_API.G_EXC_ERROR;
2617 END IF;
2618 END LOOP;
2619 -- end copying exclusion
2620 END LOOP;
2621 -- end copying lines for lumpsum and scan data
2622 END IF;
2623
2624 ELSIF l_offer_rec.offer_type = 'VOLUME_OFFER' THEN
2625 -- if volume offer, copy tier info
2626 -- copy list header
2627 DECLARE
2628 l_listHeaderId NUMBER;
2629 BEGIN
2630 copy_vo_header
2631 (
2632 p_api_version => 1.0
2633 , p_init_msg_list => FND_API.G_FALSE
2634 , p_commit => FND_API.G_FALSE
2635 , p_validation_level => p_validation_level
2636 , x_return_status => x_return_status
2637 , x_msg_count => x_msg_count
2638 , x_msg_data => x_msg_data
2639 , p_listHeaderId => p_source_object_id
2640 , x_OfferId => l_offer_id
2641 , x_listHeaderId => l_listHeaderId
2642 , p_copy_columns_table => p_copy_columns_table
2643 , p_attributes_table => p_attributes_table
2644 , p_custom_setup_id => p_custom_setup_id
2645 );
2646 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2647 RAISE FND_API.G_EXC_ERROR;
2648 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2649 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2650 END IF;
2651 l_offer_rec.qp_list_header_id := l_listHeaderId;
2652 END;
2653 END IF;
2654
2655 IF AMS_CpyUtility_PVT.is_copy_attribute ('ATCH', p_attributes_table) = FND_API.G_TRUE THEN
2656 l_errnum := 0;
2657 l_errcode := NULL;
2658 l_errmsg := NULL;
2659 ams_copyelements_pvt.copy_act_attachments( p_src_act_type => 'OZF_OFFR',
2660 p_src_act_id => p_source_object_id,
2661 p_new_act_id => l_offer_rec.qp_list_header_id,
2662 p_errnum => l_errnum,
2663 p_errcode => l_errcode,
2664 p_errmsg => l_errmsg);
2665 END IF;
2666
2667 IF AMS_CpyUtility_PVT.is_copy_attribute ('CPNT', p_attributes_table) = FND_API.G_TRUE THEN
2668 FOR l_contact_point IN c_contact_point LOOP
2669 l_contact_point_rec.contact_point_id := Fnd_Api.g_miss_num;
2670 l_contact_point_rec.last_update_date := SYSDATE;
2671 l_contact_point_rec.last_updated_by := FND_GLOBAL.user_id;
2672 l_contact_point_rec.creation_date := SYSDATE;
2673 l_contact_point_rec.created_by := FND_GLOBAL.user_id;
2674 l_contact_point_rec.last_update_login := FND_GLOBAL.conc_login_id;
2675 l_contact_point_rec.object_version_number := 1;
2676 l_contact_point_rec.arc_contact_used_by := 'OFFR';
2677 l_contact_point_rec.act_contact_used_by_id := l_offer_rec.qp_list_header_id;
2678 l_contact_point_rec.contact_point_type := l_contact_point.contact_point_type;
2679 l_contact_point_rec.contact_point_value := l_contact_point.contact_point_value;
2680 l_contact_point_rec.city := l_contact_point.city;
2681 l_contact_point_rec.country := l_contact_point.country;
2682 l_contact_point_rec.zipcode := l_contact_point.zipcode;
2683 l_contact_point_rec.attribute_category := l_contact_point.attribute_category;
2684 l_contact_point_rec.attribute1 := l_contact_point.attribute1;
2685 l_contact_point_rec.attribute2 := l_contact_point.attribute2;
2686 l_contact_point_rec.attribute3 := l_contact_point.attribute3;
2687 l_contact_point_rec.attribute4 := l_contact_point.attribute4;
2688 l_contact_point_rec.attribute5 := l_contact_point.attribute5;
2689 l_contact_point_rec.attribute6 := l_contact_point.attribute6;
2690 l_contact_point_rec.attribute7 := l_contact_point.attribute7;
2691 l_contact_point_rec.attribute8 := l_contact_point.attribute8;
2692 l_contact_point_rec.attribute9 := l_contact_point.attribute9;
2693 l_contact_point_rec.attribute10 := l_contact_point.attribute10;
2694 l_contact_point_rec.attribute11 := l_contact_point.attribute11;
2695 l_contact_point_rec.attribute12 := l_contact_point.attribute12;
2696 l_contact_point_rec.attribute13 := l_contact_point.attribute13;
2697 l_contact_point_rec.attribute14 := l_contact_point.attribute14;
2698 l_contact_point_rec.attribute15 := l_contact_point.attribute15;
2699
2700 ams_cnt_point_pvt.create_cnt_point(p_api_version_number => p_api_version
2701 ,p_init_msg_list => p_init_msg_list
2702 ,p_commit => p_commit
2703 ,p_validation_level => p_validation_level
2704 ,x_return_status => x_return_status
2705 ,x_msg_count => x_msg_count
2706 ,x_msg_data => x_msg_data
2707 ,p_cnt_point_rec => l_contact_point_rec
2708 ,x_contact_point_id => l_dummy
2709 );
2710
2711 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2712 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2713 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
2714 RAISE FND_API.G_EXC_ERROR;
2715 END IF;
2716 END LOOP;
2717 END IF;
2718 /* ELIG is copied by QP API
2719 IF AMS_CpyUtility_PVT.is_copy_attribute ('ELIG', p_attributes_table) = FND_API.G_TRUE THEN
2720 FOR l_market_elig_rec IN c_market_elig LOOP
2721 l_index2 := l_index2 + 1;
2722 l_qualifier_tbl(l_index2).qualifier_context := l_market_elig_rec.qualifier_context;
2723 l_qualifier_tbl(l_index2).qualifier_attribute := l_market_elig_rec.qualifier_attribute;
2724 l_qualifier_tbl(l_index2).qualifier_attr_value := l_market_elig_rec.qualifier_attr_value;
2725 l_qualifier_tbl(l_index2).qualifier_attr_value_to := l_market_elig_rec.qualifier_attr_value_to;
2726 l_qualifier_tbl(l_index2).comparison_operator_code := l_market_elig_rec.comparison_operator_code;
2727 l_qualifier_tbl(l_index2).qualifier_grouping_no := l_market_elig_rec.qualifier_grouping_no;
2728 l_qualifier_tbl(l_index2).list_line_id := l_market_elig_rec.list_line_id;
2729 l_qualifier_tbl(l_index2).list_header_id := l_offer_rec.qp_list_header_id;
2730 l_qualifier_tbl(l_index2).start_date_active := l_market_elig_rec.start_date_active;
2731 l_qualifier_tbl(l_index2).end_date_active := l_market_elig_rec.end_date_active;
2732 l_qualifier_tbl(l_index2).operation := 'CREATE';
2733 END LOOP;
2734
2735 IF l_index2 > 0 THEN -- if no market elig l_index2=0
2736 ozf_offer_pvt.process_market_qualifiers(p_init_msg_list => p_init_msg_list
2737 ,p_api_version => p_api_version
2738 ,p_commit => p_commit
2739 ,x_return_status => x_return_status
2740 ,x_msg_count => x_msg_count
2741 ,x_msg_data => x_msg_data
2742 ,p_qualifiers_tbl => l_qualifier_tbl
2743 ,x_error_location => l_error_location);
2744 END IF;
2745
2746 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2747 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2748 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
2749 RAISE FND_API.G_EXC_ERROR;
2750 END IF;
2751 END IF;
2752 */
2753 IF AMS_CpyUtility_PVT.is_copy_attribute ('ELIG', p_attributes_table) = FND_API.G_FALSE THEN
2754 IF l_offer_rec.offer_type IN ('ACCRUAL', 'OFF_INVOICE', 'TERMS', 'ORDER', 'OID') THEN
2755 DELETE FROM qp_qualifiers
2756 WHERE list_header_id = l_offer_rec.qp_list_header_id
2757 AND list_line_id = -1;
2758 END IF;
2759 ELSE
2760 IF l_offer_rec.offer_type = 'NET_ACCRUAL' THEN
2761 copy_na_market_elig(p_source_object_id => p_source_object_id
2762 ,p_new_offer_id => l_offer_rec.offer_id);
2763 END IF;
2764 END IF;
2765
2766 IF AMS_CpyUtility_PVT.is_copy_attribute ('CONTENT', p_attributes_table) = FND_API.G_TRUE THEN
2767 l_errnum := 0;
2768 l_errcode := NULL;
2769 l_errmsg := NULL;
2770 ams_copyelements_pvt.copy_act_content(p_src_act_type => 'OZF_OFFR',
2771 p_src_act_id => p_source_object_id,
2772 p_new_act_id => l_offer_rec.qp_list_header_id,
2773 p_errnum => l_errnum,
2774 p_errcode => l_errcode,
2775 p_errmsg => l_errmsg);
2776 END IF;
2777
2778 IF AMS_CpyUtility_PVT.is_copy_attribute ('TEAM', p_attributes_table) = FND_API.G_TRUE THEN
2779 l_errnum := 0;
2780 l_errcode := NULL;
2781 l_errmsg := NULL;
2782 ams_copyelements_pvt.copy_act_access(p_src_act_type => 'OFFR',
2783 p_new_act_type => 'OFFR',
2784 p_src_act_id => p_source_object_id,
2785 p_new_act_id => l_offer_rec.qp_list_header_id,
2786 p_errnum => l_errnum,
2787 p_errcode => l_errcode,
2788 p_errmsg => l_errmsg);
2789 END IF;
2790
2791 x_new_object_id := l_offer_rec.qp_list_header_id;
2792
2793 EXCEPTION
2794
2795 WHEN Fnd_Api.G_EXC_ERROR THEN
2796 x_return_status := Fnd_Api.g_ret_sts_error;
2797 ROLLBACK TO copy_offer_detail;
2798 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_ERROR )
2799 THEN
2800 Fnd_Msg_Pub.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2801 END IF;
2802 Fnd_Msg_Pub.Count_AND_Get
2803 ( p_count => x_msg_count,
2804 p_data => x_msg_data,
2805 p_encoded => Fnd_Api.G_FALSE
2806 );
2807
2808 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
2809 x_return_status := Fnd_Api.g_ret_sts_unexp_error;
2810 ROLLBACK TO copy_offer_detail;
2811 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR )
2812 THEN
2813 Fnd_Msg_Pub.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2814 END IF;
2815 Fnd_Msg_Pub.Count_AND_Get
2816 ( p_count => x_msg_count,
2817 p_data => x_msg_data,
2818 p_encoded => Fnd_Api.G_FALSE
2819 );
2820
2821 WHEN OTHERS THEN
2822 x_return_status := Fnd_Api.g_ret_sts_unexp_error;
2823 ROLLBACK TO copy_offer_detail;
2824 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR )
2825 THEN
2826 Fnd_Msg_Pub.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2827 END IF;
2828 Fnd_Msg_Pub.Count_AND_Get
2829 ( p_count => x_msg_count,
2830 p_data => x_msg_data,
2831 p_encoded => Fnd_Api.G_FALSE
2832 );
2833
2834 END copy_offer_detail;
2835
2836 PROCEDURE copy_offer(p_api_version IN NUMBER,
2837 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2838 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2839 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2840 x_return_status OUT NOCOPY VARCHAR2,
2841 x_msg_count OUT NOCOPY NUMBER,
2842 x_msg_data OUT NOCOPY VARCHAR2,
2843 p_source_object_id IN NUMBER,
2844 p_attributes_table IN AMS_CpyUtility_PVT.copy_attributes_table_type,
2845 p_copy_columns_table IN AMS_CpyUtility_PVT.copy_columns_table_type,
2846 x_new_object_id OUT NOCOPY NUMBER,
2847 x_custom_setup_id OUT NOCOPY NUMBER)
2848 IS
2849 CURSOR c_budget_offer_yn IS
2850 SELECT budget_offer_yn, offer_type, custom_setup_id
2851 FROM ozf_offers
2852 WHERE qp_list_header_id = p_source_object_id;
2853
2854 l_budget_offer_yn VARCHAR2(1);
2855 l_offer_type VARCHAR2(30);
2856 l_api_name VARCHAR2(30) := 'copy_offer';
2857 BEGIN
2858 SAVEPOINT copy_offer;
2859 -- Initialize API return status to SUCCESS
2860 x_return_status := FND_API.G_RET_STS_SUCCESS;
2861
2862 OPEN c_budget_offer_yn;
2863 FETCH c_budget_offer_yn INTO l_budget_offer_yn, l_offer_type, x_custom_setup_id;
2864 CLOSE c_budget_offer_yn;
2865
2866 IF l_budget_offer_yn = 'Y' THEN
2867 IF l_offer_type = 'ACCRUAL' THEN
2868 x_custom_setup_id := 91;
2869 ELSIF l_offer_type = 'VOLUME_OFFER' THEN
2870 x_custom_setup_id := 98;
2871 END IF;
2872 END IF;
2873
2874 copy_offer_detail
2875 (p_api_version => p_api_version,
2876 p_init_msg_list => p_init_msg_list,
2877 p_commit => p_commit,
2878 p_validation_level => p_validation_level,
2879 x_return_status => x_return_status,
2880 x_msg_count => x_msg_count,
2881 x_msg_data => x_msg_data,
2882 p_source_object_id => p_source_object_id,
2883 p_attributes_table => p_attributes_table,
2884 p_copy_columns_table => p_copy_columns_table,
2885 x_new_object_id => x_new_object_id,
2886 p_custom_setup_id => x_custom_setup_id);
2887
2888 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2889 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2890 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
2891 RAISE FND_API.G_EXC_ERROR;
2892 END IF;
2893
2894 EXCEPTION
2895 WHEN Fnd_Api.G_EXC_ERROR THEN
2896 x_return_status := Fnd_Api.g_ret_sts_error;
2897 ROLLBACK TO copy_offer;
2898 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_ERROR )
2899 THEN
2900 Fnd_Msg_Pub.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2901 END IF;
2902 Fnd_Msg_Pub.Count_AND_Get
2903 ( p_count => x_msg_count,
2904 p_data => x_msg_data,
2905 p_encoded => Fnd_Api.G_FALSE
2906 );
2907
2908 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
2909 x_return_status := Fnd_Api.g_ret_sts_unexp_error;
2910 ROLLBACK TO copy_offer;
2911 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR )
2912 THEN
2913 Fnd_Msg_Pub.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2914 END IF;
2915 Fnd_Msg_Pub.Count_AND_Get
2916 ( p_count => x_msg_count,
2917 p_data => x_msg_data,
2918 p_encoded => Fnd_Api.G_FALSE
2919 );
2920
2921 WHEN OTHERS THEN
2922 x_return_status := Fnd_Api.g_ret_sts_unexp_error;
2923 ROLLBACK TO copy_offer;
2924 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR )
2925 THEN
2926 Fnd_Msg_Pub.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2927 END IF;
2928 Fnd_Msg_Pub.Count_AND_Get
2929 ( p_count => x_msg_count,
2930 p_data => x_msg_data,
2931 p_encoded => Fnd_Api.G_FALSE
2932 );
2933
2934 END copy_offer;
2935
2936 END OZF_COPY_OFFER_PVT;