[Home] [Help]
PACKAGE BODY: APPS.OZF_COPY_OFFER_PVT
Source
1 PACKAGE BODY OZF_COPY_OFFER_PVT AS
2 /* $Header: ozfvcpob.pls 120.11.12010000.3 2008/12/11 03:50:15 nirprasa 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 CURSOR c_offer_details (p_listHeaderId NUMBER) IS
658 SELECT a.modifier_level_code
659 , a.offer_type
660 , a.activity_media_id
661 , a.reusable
662 , b.list_type_code
663 , a.transaction_currency_code
664 , a.perf_date_from
665 , a.perf_date_to
666 , a.custom_setup_id
667 , a.functional_currency_code
668 , b.currency_code
669 , b.ask_for_flag
670 , b.start_date_active_first
671 , b.end_date_active_first
672 , b.active_date_first_type
673 , b.start_date_active_second
674 , b.end_date_active_second
675 , b.active_date_second_type
676 , a.budget_source_type
677 , a.budget_source_id
678 , a.budget_amount_tc
679 , a.offer_amount
680 , a.volume_offer_type
681 , a.budget_offer_yn
682 , a.confidential_flag
683 , a.source_from_parent
684 , b.global_flag
685 , b.orig_org_id
686 , b.context
687 , b.attribute1
688 , b.attribute2
689 , b.attribute3
690 , b.attribute4
691 , b.attribute5
692 , b.attribute6
693 , b.attribute7
694 , b.attribute8
695 , b.attribute9
696 , b.attribute10
697 , b.attribute11
698 , b.attribute12
699 , b.attribute13
700 , b.attribute14
701 , b.attribute15
702 FROM ozf_offers a, qp_list_headers_all b
703 WHERE a.qp_list_header_id = b.list_header_id
704 AND a.qp_list_header_id = p_listHeaderId;
705
706 CURSOR c_offerId (cp_listHeaderId NUMBER) IS
707 SELECT offer_id FROM ozf_offers
708 WHERE qp_list_header_id = cp_listHeaderId ;
709
710 BEGIN
711 -- establish save point
712 SAVEPOINT copy_vo_header;
713 -- check api version compatibility
714 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
715 p_api_version,
716 l_api_name,
717 G_PKG_NAME)
718 THEN
719 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
720 END IF;
721
722 -- Initialize message list if p_init_msg_list is set to TRUE.
723 IF FND_API.to_Boolean( p_init_msg_list ) THEN
724 FND_MSG_PUB.initialize;
725 END IF;
726
727 -- Initialize API return status to SUCCESS
728 x_return_status := FND_API.G_RET_STS_SUCCESS;
729
730 -- initialize the record sent to api for creation
731 l_modifier_list_rec := null;
732
733 -- populate ui values into record.
734 AMS_CpyUtility_PVT.get_column_value ('newObjName' , p_copy_columns_table , l_modifier_list_rec.description);
735 AMS_CpyUtility_PVT.get_column_value ('offerCode' , p_copy_columns_table , l_modifier_list_rec.offer_code);
736 AMS_CpyUtility_PVT.get_column_value ('startDateActive' , p_copy_columns_table , l_modifier_list_rec.start_date_active);
737 AMS_CpyUtility_PVT.get_column_value ('endDateActive' , p_copy_columns_table , l_modifier_list_rec.end_date_active);
738 AMS_CpyUtility_PVT.get_column_value ('ownerId' , p_copy_columns_table , l_modifier_list_rec.owner_id);
739 AMS_CpyUtility_PVT.get_column_value ('description' , p_copy_columns_table , l_modifier_list_rec.comments);
740
741 FOR l_offer_details IN c_offer_details(p_listHeaderId) LOOP
742 -- populate source object values into the record
743 l_modifier_list_rec.modifier_level_code := l_offer_details.modifier_level_code;
744 l_modifier_list_rec.offer_type := l_offer_type;
745 l_modifier_list_rec.activity_media_id := l_offer_details.activity_media_id;
746 l_modifier_list_rec.reusable := l_offer_details.reusable;
747 l_modifier_list_rec.list_type_code := l_offer_details.list_type_code;
748 l_modifier_list_rec.transaction_currency_code := l_offer_details.transaction_currency_code;
749 l_modifier_list_rec.perf_date_from := l_offer_details.perf_date_from;
750 l_modifier_list_rec.perf_date_to := l_offer_details.perf_date_to;
751 l_modifier_list_rec.custom_setup_id := p_custom_setup_id;
752 l_modifier_list_rec.functional_currency_code := l_offer_details.functional_currency_code;
753 l_modifier_list_rec.currency_code := l_offer_details.currency_code;
754 l_modifier_list_rec.ask_for_flag := l_offer_details.ask_for_flag;
755 l_modifier_list_rec.start_date_active_first := l_offer_details.start_date_active_first;
756 l_modifier_list_rec.end_date_active_first := l_offer_details.end_date_active_first;
757 l_modifier_list_rec.active_date_first_type := l_offer_details.active_date_first_type;
758 l_modifier_list_rec.start_date_active_second := l_offer_details.start_date_active_second;
759 l_modifier_list_rec.end_date_active_second := l_offer_details.end_date_active_second;
760 l_modifier_list_rec.active_date_second_type := l_offer_details.active_date_second_type;
761 l_modifier_list_rec.budget_source_type := l_offer_details.budget_source_type;
762 l_modifier_list_rec.budget_source_id := l_offer_details.budget_source_id;
763 l_modifier_list_rec.budget_amount_tc := l_offer_details.budget_amount_tc;
764 l_modifier_list_rec.budget_offer_yn := l_offer_details.budget_offer_yn ;
765 l_modifier_list_rec.offer_amount := l_offer_details.offer_amount;
766 l_modifier_list_rec.volume_offer_type := l_offer_details.volume_offer_type;
767 l_modifier_list_rec.confidential_flag := l_offer_details.confidential_flag;
768 --l_modifier_list_rec.committed_amount_eq_max :=
769 l_modifier_list_rec.source_from_parent := l_offer_details.source_from_parent;
770 l_modifier_list_rec.global_flag := l_offer_details.global_flag;
771 l_modifier_list_rec.orig_org_id := l_offer_details.orig_org_id;
772 l_modifier_list_rec.modifier_operation := G_CREATE;
773 l_modifier_list_rec.offer_operation := G_CREATE;
774
775 l_modifier_list_rec.offer_id := FND_API.G_MISS_NUM;
776 l_modifier_list_rec.amount_limit_id := FND_API.G_MISS_NUM;
777 l_modifier_list_rec.uses_limit_id := FND_API.G_MISS_NUM;
778 l_modifier_list_rec.qp_list_header_id := FND_API.G_MISS_NUM;
779
780
781 l_modifier_list_rec.context := l_offer_details.context;
782 l_modifier_list_rec.attribute1 := l_offer_details.attribute1;
783 l_modifier_list_rec.attribute2 := l_offer_details.attribute2;
784 l_modifier_list_rec.attribute3 := l_offer_details.attribute3;
785 l_modifier_list_rec.attribute4 := l_offer_details.attribute4;
786 l_modifier_list_rec.attribute5 := l_offer_details.attribute5;
787 l_modifier_list_rec.attribute6 := l_offer_details.attribute6;
788 l_modifier_list_rec.attribute7 := l_offer_details.attribute7;
789 l_modifier_list_rec.attribute8 := l_offer_details.attribute8;
790 l_modifier_list_rec.attribute9 := l_offer_details.attribute9;
791 l_modifier_list_rec.attribute10 := l_offer_details.attribute10;
792 l_modifier_list_rec.attribute11 := l_offer_details.attribute11;
793 l_modifier_list_rec.attribute12 := l_offer_details.attribute12;
794 l_modifier_list_rec.attribute13 := l_offer_details.attribute13;
795 l_modifier_list_rec.attribute14 := l_offer_details.attribute14;
796 l_modifier_list_rec.attribute15 := l_offer_details.attribute15;
797
798 END LOOP;
799
800 -- call api to create new header
801 OZF_OFFER_PVT.process_modifiers
802 (
803 p_init_msg_list => FND_API.G_FALSE
804 ,p_api_version => 1.0
805 ,p_commit => FND_API.G_FALSE
806 ,x_return_status => x_return_status
807 ,x_msg_count => x_msg_count
808 ,x_msg_data => x_msg_data
809 ,p_offer_type => l_offer_type
810 ,p_modifier_list_rec => l_modifier_list_rec
811 ,p_modifier_line_tbl => l_modifier_line_tbl
812 ,x_qp_list_header_id => l_listHeaderId
813 ,x_error_location => l_errLoc
814 );
815 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
816 RAISE FND_API.G_EXC_ERROR;
817 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
818 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
819 END IF;
820
821 l_offer_id := null;
822
823 OPEN c_offerId(cp_listHeaderId => l_listHeaderId);
824 FETCH c_offerId into l_offer_id;
825 IF c_offerId%NOTFOUND THEN
826 l_offer_id := -1;
827 END IF;
828 CLOSE c_offerId;
829
830 copy_vo_discounts
831 (
832 p_api_version => 1.0
833 , p_init_msg_list => FND_API.G_FALSE
834 , p_commit => FND_API.G_FALSE
835 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
836 , x_return_status => x_return_status
837 , x_msg_count => x_msg_count
838 , x_msg_data => x_msg_data
839 , p_sourceObjectId => p_listHeaderId
840 , p_destOfferId => l_offer_id
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 -- copy qualifiers
849 IF AMS_CpyUtility_PVT.is_copy_attribute ('ELIG', p_attributes_table) = FND_API.G_TRUE THEN
850 ozf_utility_pvt.debug_message('Copy Eligibility');
851 copy_vo_qualifiers
852 (
853 p_api_version => 1.0
854 , p_init_msg_list => FND_API.G_FALSE
855 , p_commit => FND_API.G_FALSE
856 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
857 , x_return_status => x_return_status
858 , x_msg_count => x_msg_count
859 , x_msg_data => x_msg_data
860 , p_sourceListHeaderId => p_listHeaderId
861 , p_destListHeaderId => l_listHeaderId
862 );
863 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
864 RAISE FND_API.G_EXC_ERROR;
865 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
866 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
867 END IF;
868 -- copy market options
869 -- market options are created with default options, so update the market options to the ones in the source offer
870 IF AMS_CpyUtility_PVT.is_copy_attribute ('MKT_OPT', p_attributes_table) = FND_API.G_TRUE THEN
871 ozf_utility_pvt.debug_message('Copy Market Options');
872 copy_vo_mkt_options
873 (
874 p_api_version => 1.0
875 , p_init_msg_list => FND_API.G_FALSE
876 , p_commit => FND_API.G_FALSE
877 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
878 , x_return_status => x_return_status
879 , x_msg_count => x_msg_count
880 , x_msg_data => x_msg_data
881 , p_sourceObjectId => p_listHeaderId
882 , p_destOfferId => l_offer_id
883 );
884
885 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
886 RAISE FND_API.G_EXC_ERROR;
887 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
888 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
889 END IF;
890 END IF;
891 END IF;
892
893 ozf_utility_pvt.debug_message('QplistHeaderId returned :'||l_listHeaderId);
894
895 ozf_utility_pvt.debug_message('OfferId :'||l_offer_id);
896 x_OfferId := l_offer_id;
897 x_listHeaderId := l_listHeaderId;
898 -- exception
899 EXCEPTION
900
901 WHEN Fnd_Api.G_EXC_ERROR THEN
902 x_return_status := Fnd_Api.g_ret_sts_error;
903 ROLLBACK TO copy_vo_header;
904 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_ERROR )
905 THEN
906 Fnd_Msg_Pub.Add_Exc_Msg( G_PKG_NAME,l_api_name);
907 END IF;
908 Fnd_Msg_Pub.Count_AND_Get
909 ( p_count => x_msg_count,
910 p_data => x_msg_data,
911 p_encoded => Fnd_Api.G_FALSE
912 );
913
914 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
915 x_return_status := Fnd_Api.g_ret_sts_unexp_error;
916 ROLLBACK TO copy_vo_header;
917 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR )
918 THEN
919 Fnd_Msg_Pub.Add_Exc_Msg( G_PKG_NAME,l_api_name);
920 END IF;
921 Fnd_Msg_Pub.Count_AND_Get
922 ( p_count => x_msg_count,
923 p_data => x_msg_data,
924 p_encoded => Fnd_Api.G_FALSE
925 );
926
927 WHEN OTHERS THEN
928 x_return_status := Fnd_Api.g_ret_sts_unexp_error;
929 ROLLBACK TO copy_vo_header;
930 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR )
931 THEN
932 Fnd_Msg_Pub.Add_Exc_Msg( G_PKG_NAME,l_api_name);
933 END IF;
934 Fnd_Msg_Pub.Count_AND_Get
935 ( p_count => x_msg_count,
936 p_data => x_msg_data,
937 p_encoded => Fnd_Api.G_FALSE
938 );
939
940 END copy_vo_header;
941
942
943 FUNCTION get_rltd_line_id(l_org_list_header_id NUMBER, l_new_list_header_id NUMBER)
944 RETURN line_mapping_tbl_type
945 IS
946
947 l_line_mapping_tbl line_mapping_tbl_type;
948 l_index NUMBER := 0;
949
950 CURSOR c_mod_sum IS
951 SELECT *
952 FROM qp_modifier_summary_v
953 WHERE list_header_id = l_org_list_header_id;
954 -- l_mod_sum_rec c_mod_sum%ROWTYPE;
955
956 CURSOR c_list_line_id(l_mod_sum_rec c_mod_sum%ROWTYPE) IS
957 SELECT list_line_id
958 FROM qp_modifier_summary_v
959 WHERE list_header_id = l_new_list_header_id
960 AND list_line_type_code = l_mod_sum_rec.list_line_type_code
961 AND automatic_flag = l_mod_sum_rec.automatic_flag
962 AND modifier_level_code = l_mod_sum_rec.modifier_level_code
963 AND NVL(price_break_type_code,'z') = NVL(l_mod_sum_rec.price_break_type_code,'z')
964 AND NVL(operand,-99999) = NVL(l_mod_sum_rec.operand,-99999)
965 AND NVL(arithmetic_operator,'z') = NVL(l_mod_sum_rec.arithmetic_operator,'z')
966 AND NVL(override_flag,'z') = NVL(l_mod_sum_rec.override_flag,'z')
967 AND NVL(print_on_invoice_flag,'z') = NVL(l_mod_sum_rec.print_on_invoice_flag,'z')
968 AND NVL(pricing_group_sequence,-99999) = NVL(l_mod_sum_rec.pricing_group_sequence,-99999)
969 AND NVL(incompatibility_grp_code,'z') = NVL(l_mod_sum_rec.incompatibility_grp_code,'z')
970 AND NVL(product_precedence,-99999) = NVL(l_mod_sum_rec.product_precedence,-99999)
971 AND NVL(pricing_phase_id,-99999) = NVL(l_mod_sum_rec.pricing_phase_id,-99999)
972 AND NVL(product_attribute_context,'z') = NVL(l_mod_sum_rec.product_attribute_context,'z')
973 AND NVL(product_attr,'z') = NVL(l_mod_sum_rec.product_attr,'z')
974 AND NVL(product_attr_val,'z') = NVL(l_mod_sum_rec.product_attr_val,'z')
975 AND NVL(product_uom_code,'z') = NVL(l_mod_sum_rec.product_uom_code,'z')
976 AND NVL(comparison_operator_code,'z') = NVL(l_mod_sum_rec.comparison_operator_code,'z')
977 AND NVL(pricing_attribute_context,'z') = NVL(l_mod_sum_rec.pricing_attribute_context,'z')
978 AND NVL(pricing_attr,'z') = NVL(l_mod_sum_rec.pricing_attr,'z')
979 AND NVL(pricing_attr_value_from,'z') = NVL(l_mod_sum_rec.pricing_attr_value_from,'z')
980 AND NVL(pricing_attr_value_to,'z') = NVL(l_mod_sum_rec.pricing_attr_value_to,'z')
981 AND NVL(excluder_flag,'z') = NVL(l_mod_sum_rec.excluder_flag,'z')
982 AND NVL(attribute_grouping_no,-99999) = NVL(l_mod_sum_rec.attribute_grouping_no,-99999)
983 AND NVL(to_rltd_modifier_id,'z') = NVL(l_mod_sum_rec.to_rltd_modifier_id,'z')
984 AND NVL(rltd_modifier_id,'z') = NVL(l_mod_sum_rec.rltd_modifier_id,'z')
985 AND NVL(accrual_flag,'z') = NVL(l_mod_sum_rec.accrual_flag,'z')
986 AND NVL(accrual_conversion_rate,-99999) = NVL(l_mod_sum_rec.accrual_conversion_rate,-99999)
987 AND NVL(estim_accrual_rate,-99999) = NVL(l_mod_sum_rec.estim_accrual_rate,-99999)
988 AND NVL(price_by_formula_id,-99999) = NVL(l_mod_sum_rec.price_by_formula_id,-99999)
989 AND NVL(generate_using_formula_id,-99999) = NVL(l_mod_sum_rec.generate_using_formula_id,-99999);
990 /*
991 AND LIST_LINE_TYPE_CODE = l_mod_sum_rec.LIST_LINE_TYPE_CODE
992 AND AUTOMATIC_FLAG = l_mod_sum_rec.AUTOMATIC_FLAG
993 AND MODIFIER_LEVEL_CODE = l_mod_sum_rec.MODIFIER_LEVEL_CODE
994 AND NVL(LIST_PRICE,-99999) = NVL(l_mod_sum_rec.LIST_PRICE,-99999)
995 AND NVL(LIST_PRICE_UOM_CODE,'z') = NVL(l_mod_sum_rec.LIST_PRICE_UOM_CODE,'z')
996 AND NVL(PRIMARY_UOM_FLAG,'z') = NVL(l_mod_sum_rec.PRIMARY_UOM_FLAG,'z')
997 AND NVL(INVENTORY_ITEM_ID,-99999) = NVL(l_mod_sum_rec.INVENTORY_ITEM_ID,-99999)
998 AND NVL(ORGANIZATION_ID,-99999) = NVL(l_mod_sum_rec.ORGANIZATION_ID,-99999)
999 AND NVL(RELATED_ITEM_ID,-99999) = NVL(l_mod_sum_rec.RELATED_ITEM_ID,-99999)
1000 AND NVL(RELATIONSHIP_TYPE_ID,-99999) = NVL(l_mod_sum_rec.RELATIONSHIP_TYPE_ID,-99999)
1001 AND NVL(SUBSTITUTION_CONTEXT,'z') = NVL(l_mod_sum_rec.SUBSTITUTION_CONTEXT,'z')
1002 AND NVL(SUBSTITUTION_ATTR,'z') = NVL(l_mod_sum_rec.SUBSTITUTION_ATTR,'z')
1003 AND NVL(SUBSTITUTION_VAL,'z') = NVL(l_mod_sum_rec.SUBSTITUTION_VAL,'z')
1004 AND NVL(REVISION,'z') = NVL(l_mod_sum_rec.REVISION,'z')
1005 AND NVL(REVISION_REASON_CODE,'z') = NVL(l_mod_sum_rec.REVISION_REASON_CODE,'z')
1006 AND NVL(CONTEXT,'z') = NVL(l_mod_sum_rec.CONTEXT,'z')
1007 AND NVL(ATTRIBUTE1,'z') = NVL(l_mod_sum_rec.ATTRIBUTE1,'z')
1008 AND NVL(ATTRIBUTE2,'z') = NVL(l_mod_sum_rec.ATTRIBUTE2,'z')
1009 AND NVL(COMMENTS,'z') = NVL(l_mod_sum_rec.COMMENTS,'z')
1010 AND NVL(ATTRIBUTE3,'z') = NVL(l_mod_sum_rec.ATTRIBUTE3,'z')
1011 AND NVL(ATTRIBUTE4,'z') = NVL(l_mod_sum_rec.ATTRIBUTE4,'z')
1012 AND NVL(ATTRIBUTE5,'z') = NVL(l_mod_sum_rec.ATTRIBUTE5,'z')
1013 AND NVL(ATTRIBUTE6,'z') = NVL(l_mod_sum_rec.ATTRIBUTE6,'z')
1014 AND NVL(ATTRIBUTE7,'z') = NVL(l_mod_sum_rec.ATTRIBUTE7,'z')
1015 AND NVL(ATTRIBUTE8,'z') = NVL(l_mod_sum_rec.ATTRIBUTE8,'z')
1016 AND NVL(ATTRIBUTE9,'z') = NVL(l_mod_sum_rec.ATTRIBUTE9,'z')
1017 AND NVL(ATTRIBUTE10,'z') = NVL(l_mod_sum_rec.ATTRIBUTE10,'z')
1018 AND NVL(INCLUDE_ON_RETURNS_FLAG,'z') = NVL(l_mod_sum_rec.INCLUDE_ON_RETURNS_FLAG,'z')
1019 AND NVL(ATTRIBUTE11,'z') = NVL(l_mod_sum_rec.ATTRIBUTE11,'z')
1020 AND NVL(ATTRIBUTE12,'z') = NVL(l_mod_sum_rec.ATTRIBUTE12,'z')
1021 AND NVL(ATTRIBUTE13,'z') = NVL(l_mod_sum_rec.ATTRIBUTE13,'z')
1022 AND NVL(ATTRIBUTE14,'z') = NVL(l_mod_sum_rec.ATTRIBUTE14,'z')
1023 AND NVL(ATTRIBUTE15,'z') = NVL(l_mod_sum_rec.ATTRIBUTE15,'z')
1024 AND NVL(PRICE_BREAK_TYPE_CODE,'z') = NVL(l_mod_sum_rec.PRICE_BREAK_TYPE_CODE,'z')
1025 AND NVL(PERCENT_PRICE,-99999) = NVL(l_mod_sum_rec.PERCENT_PRICE,-99999)
1026 AND NVL(EFFECTIVE_PERIOD_UOM,'z') = NVL(l_mod_sum_rec.EFFECTIVE_PERIOD_UOM,'z')
1027 AND NVL(NUMBER_EFFECTIVE_PERIODS,-99999) = NVL(l_mod_sum_rec.NUMBER_EFFECTIVE_PERIODS,-99999)
1028 AND NVL(OPERAND,-99999) = NVL(l_mod_sum_rec.OPERAND,-99999)
1029 AND NVL(ARITHMETIC_OPERATOR,'z') = NVL(l_mod_sum_rec.ARITHMETIC_OPERATOR,'z')
1030 AND NVL(OVERRIDE_FLAG,'z') = NVL(l_mod_sum_rec.OVERRIDE_FLAG,'z')
1031 AND NVL(PRINT_ON_INVOICE_FLAG,'z') = NVL(l_mod_sum_rec.PRINT_ON_INVOICE_FLAG,'z')
1032 AND NVL(REBATE_TRANSACTION_TYPE_CODE,'z') = NVL(l_mod_sum_rec.REBATE_TRANSACTION_TYPE_CODE,'z')
1033 AND NVL(DB_ESTIM_ACCRUAL_RATE,-99999) = NVL(l_mod_sum_rec.DB_ESTIM_ACCRUAL_RATE,-99999)
1034 AND NVL(PRICE_BY_FORMULA_ID,-99999) = NVL(l_mod_sum_rec.PRICE_BY_FORMULA_ID,-99999)
1035 AND NVL(GENERATE_USING_FORMULA_ID,-99999) = NVL(l_mod_sum_rec.GENERATE_USING_FORMULA_ID,-99999)
1036 AND NVL(REPRICE_FLAG,'z') = NVL(l_mod_sum_rec.REPRICE_FLAG,'z')
1037 AND NVL(DB_ACCRUAL_FLAG,'z') = NVL(l_mod_sum_rec.DB_ACCRUAL_FLAG,'z')
1038 AND NVL(PRICING_GROUP_SEQUENCE,-99999) = NVL(l_mod_sum_rec.PRICING_GROUP_SEQUENCE,-99999)
1039 AND NVL(INCOMPATIBILITY_GRP_CODE,'z') = NVL(l_mod_sum_rec.INCOMPATIBILITY_GRP_CODE,'z')
1040 AND NVL(LIST_LINE_NO,'z') = NVL(l_mod_sum_rec.LIST_LINE_NO,'z')
1041 AND NVL(PRODUCT_PRECEDENCE,-99999) = NVL(l_mod_sum_rec.PRODUCT_PRECEDENCE,-99999)
1042 AND NVL(PRICING_PHASE_ID,-99999) = NVL(l_mod_sum_rec.PRICING_PHASE_ID,-99999)
1043 AND NVL(DB_NUMBER_EXPIRATION_PERIODS,-99999) = NVL(l_mod_sum_rec.DB_NUMBER_EXPIRATION_PERIODS,-99999)
1044 AND NVL(DB_EXPIRATION_PERIOD_UOM,'z') = NVL(l_mod_sum_rec.DB_EXPIRATION_PERIOD_UOM,'z')
1045 AND NVL(ESTIM_GL_VALUE,-99999) = NVL(l_mod_sum_rec.ESTIM_GL_VALUE,-99999)
1046 AND NVL(DB_ACCRUAL_CONVERSION_RATE,-99999) = NVL(l_mod_sum_rec.DB_ACCRUAL_CONVERSION_RATE,-99999)
1047 AND NVL(BENEFIT_PRICE_LIST_LINE_ID,-99999) = NVL(l_mod_sum_rec.BENEFIT_PRICE_LIST_LINE_ID,-99999)
1048 AND NVL(PRORATION_TYPE_CODE,'z') = NVL(l_mod_sum_rec.PRORATION_TYPE_CODE,'z')
1049 AND NVL(DB_BENEFIT_QTY,-99999) = NVL(l_mod_sum_rec.DB_BENEFIT_QTY,-99999)
1050 AND NVL(DB_BENEFIT_UOM_CODE,'z') = NVL(l_mod_sum_rec.DB_BENEFIT_UOM_CODE,'z')
1051 AND NVL(CHARGE_TYPE_CODE,'z') = NVL(l_mod_sum_rec.CHARGE_TYPE_CODE,'z')
1052 AND NVL(CHARGE_SUBTYPE_CODE,'z') = NVL(l_mod_sum_rec.CHARGE_SUBTYPE_CODE,'z')
1053 AND NVL(BENEFIT_LIMIT,-99999) = NVL(l_mod_sum_rec.BENEFIT_LIMIT,-99999)
1054 AND NVL(PRODUCT_ATTRIBUTE_CONTEXT,'z') = NVL(l_mod_sum_rec.PRODUCT_ATTRIBUTE_CONTEXT,'z')
1055 AND NVL(PRODUCT_ATTR,'z') = NVL(l_mod_sum_rec.PRODUCT_ATTR,'z')
1056 AND NVL(PRODUCT_ATTR_VAL,'z') = NVL(l_mod_sum_rec.PRODUCT_ATTR_VAL,'z')
1057 AND NVL(PRODUCT_UOM_CODE,'z') = NVL(l_mod_sum_rec.PRODUCT_UOM_CODE,'z')
1058 AND NVL(COMPARISON_OPERATOR_CODE,'z') = NVL(l_mod_sum_rec.COMPARISON_OPERATOR_CODE,'z')
1059 AND NVL(PRICING_ATTRIBUTE_CONTEXT,'z') = NVL(l_mod_sum_rec.PRICING_ATTRIBUTE_CONTEXT,'z')
1060 AND NVL(PRICING_ATTR,'z') = NVL(l_mod_sum_rec.PRICING_ATTR,'z')
1061 AND NVL(PRICING_ATTR_VALUE_FROM,'z') = NVL(l_mod_sum_rec.PRICING_ATTR_VALUE_FROM,'z')
1062 AND NVL(PRICING_ATTR_VALUE_TO,'z') = NVL(l_mod_sum_rec.PRICING_ATTR_VALUE_TO,'z')
1063 AND NVL(PRICING_ATTRIBUTE_DATATYPE,'z') = NVL(l_mod_sum_rec.PRICING_ATTRIBUTE_DATATYPE,'z')
1064 AND NVL(PRODUCT_ATTRIBUTE_DATATYPE,'z') = NVL(l_mod_sum_rec.PRODUCT_ATTRIBUTE_DATATYPE,'z')
1065 AND NVL(EXCLUDER_FLAG,'z') = NVL(l_mod_sum_rec.EXCLUDER_FLAG,'z')
1066 AND NVL(ATTRIBUTE_GROUPING_NO,-99999) = NVL(l_mod_sum_rec.ATTRIBUTE_GROUPING_NO,-99999)
1067 AND NVL(TO_RLTD_MODIFIER_ID,'z') = NVL(l_mod_sum_rec.TO_RLTD_MODIFIER_ID,'z')
1068 AND NVL(RLTD_MODIFIER_GRP_NO,'z') = NVL(l_mod_sum_rec.RLTD_MODIFIER_GRP_NO,'z')
1069 AND NVL(RLTD_MODIFIER_GRP_TYPE,'z') = NVL(l_mod_sum_rec.RLTD_MODIFIER_GRP_TYPE,'z')
1070 AND NVL(RLTD_MODIFIER_ID,'z') = NVL(l_mod_sum_rec.RLTD_MODIFIER_ID,'z')
1071 AND NVL(PRORATION_TYPE,'z') = NVL(l_mod_sum_rec.PRORATION_TYPE,'z')
1072 AND NVL(PRICING_PHASE,'z') = NVL(l_mod_sum_rec.PRICING_PHASE,'z')
1073 AND NVL(INCOMPATIBILITY_GRP,'z') = NVL(l_mod_sum_rec.INCOMPATIBILITY_GRP,'z')
1074 AND NVL(MODIFIER_LEVEL,'z') = NVL(l_mod_sum_rec.MODIFIER_LEVEL,'z')
1075 AND NVL(LIST_LINE_TYPE,'z') = NVL(l_mod_sum_rec.LIST_LINE_TYPE,'z')
1076 AND NVL(PRICE_BREAK_TYPE,'z') = NVL(l_mod_sum_rec.PRICE_BREAK_TYPE,'z')
1077 AND NVL(CHARGE_NAME,'z') = NVL(l_mod_sum_rec.CHARGE_NAME,'z')
1078 AND NVL(FORMULA,'z') = NVL(l_mod_sum_rec.FORMULA,'z')
1079 AND NVL(ARITHMETIC_OPERATOR_TYPE,'z') = NVL(l_mod_sum_rec.ARITHMETIC_OPERATOR_TYPE,'z')
1080 AND NVL(NUMBER_EXPIRATION_PERIODS,-99999) = NVL(l_mod_sum_rec.NUMBER_EXPIRATION_PERIODS,-99999)
1081 AND NVL(EXPIRATION_PERIOD_UOM,'z') = NVL(l_mod_sum_rec.EXPIRATION_PERIOD_UOM,'z')
1082 AND NVL(COUP_NUMBER_EXPIRATION_PERIODS,-99999) = NVL(l_mod_sum_rec.COUP_NUMBER_EXPIRATION_PERIODS,-99999)
1083 AND NVL(COUP_EXPIRATION_PERIOD_UOM,'z') = NVL(l_mod_sum_rec.COUP_EXPIRATION_PERIOD_UOM,'z')
1084 AND NVL(BRK_NUMBER_EXPIRATION_PERIODS,-99999) = NVL(l_mod_sum_rec.BRK_NUMBER_EXPIRATION_PERIODS,-99999)
1085 AND NVL(BRK_EXPIRATION_PERIOD_UOM,'z') = NVL(l_mod_sum_rec.BRK_EXPIRATION_PERIOD_UOM,'z')
1086 AND NVL(REBATE_TRANSACTION_TYPE,'z') = NVL(l_mod_sum_rec.REBATE_TRANSACTION_TYPE,'z')
1087 AND NVL(BRK_REB_TRANSACTION_TYPE,'z') = NVL(l_mod_sum_rec.BRK_REB_TRANSACTION_TYPE,'z')
1088 AND NVL(BENEFIT_QTY,-99999) = NVL(l_mod_sum_rec.BENEFIT_QTY,-99999)
1089 AND NVL(BENEFIT_UOM_CODE,'z') = NVL(l_mod_sum_rec.BENEFIT_UOM_CODE,'z')
1090 AND NVL(COUP_BENEFIT_QTY,-99999) = NVL(l_mod_sum_rec.COUP_BENEFIT_QTY,-99999)
1091 AND NVL(COUP_BENEFIT_UOM_CODE,'z') = NVL(l_mod_sum_rec.COUP_BENEFIT_UOM_CODE,'z')
1092 AND NVL(COUP_LIST_LINE_NO,'z') = NVL(l_mod_sum_rec.COUP_LIST_LINE_NO,'z')
1093 AND NVL(ACCRUAL_FLAG,'z') = NVL(l_mod_sum_rec.ACCRUAL_FLAG,'z')
1094 AND NVL(BRK_ACCRUAL_FLAG,'z') = NVL(l_mod_sum_rec.BRK_ACCRUAL_FLAG,'z')
1095 AND NVL(ACCRUAL_CONVERSION_RATE,-99999) = NVL(l_mod_sum_rec.ACCRUAL_CONVERSION_RATE,-99999)
1096 AND NVL(COUP_ACCRUAL_CONVERSION_RATE,-99999) = NVL(l_mod_sum_rec.COUP_ACCRUAL_CONVERSION_RATE,-99999)
1097 AND NVL(COUP_ESTIM_ACCRUAL_RATE,-99999) = NVL(l_mod_sum_rec.COUP_ESTIM_ACCRUAL_RATE,-99999)
1098 AND NVL(BRK_ACCRUAL_CONVERSION_RATE,-99999) = NVL(l_mod_sum_rec.BRK_ACCRUAL_CONVERSION_RATE,-99999)
1099 AND NVL(ESTIM_ACCRUAL_RATE,-99999) = NVL(l_mod_sum_rec.ESTIM_ACCRUAL_RATE,-99999)
1100 AND NVL(BRK_ESTIM_ACCRUAL_RATE,-99999) = NVL(l_mod_sum_rec.BRK_ESTIM_ACCRUAL_RATE,-99999)
1101 AND NVL(BREAK_LINE_TYPE_CODE,'z') = NVL(l_mod_sum_rec.BREAK_LINE_TYPE_CODE,'z')
1102 AND NVL(BREAK_LINE_TYPE,'z') = NVL(l_mod_sum_rec.BREAK_LINE_TYPE,'z')
1103 AND NVL(PRODUCT_ID,'z') = NVL(l_mod_sum_rec.PRODUCT_ID,'z')
1104 AND NVL(DESCRIPTION,'z') = NVL(l_mod_sum_rec.DESCRIPTION,'z')
1105 AND NVL(PRICING_ATTR_SEG_NAME,'z') = NVL(l_mod_sum_rec.PRICING_ATTR_SEG_NAME,'z')
1106 AND NVL(PROD_ATTR_SEGMENT_NAME,'z') = NVL(l_mod_sum_rec.PROD_ATTR_SEGMENT_NAME,'z')
1107 AND NVL(RELATED_ITEM,'z') = NVL(l_mod_sum_rec.RELATED_ITEM,'z')
1108 AND NVL(SUBSTITUTION_ATTRIBUTE,'z') = NVL(l_mod_sum_rec.SUBSTITUTION_ATTRIBUTE,'z')
1109 AND NVL(SUBSTITUTION_VALUE,'z') = NVL(l_mod_sum_rec.SUBSTITUTION_VALUE,'z')
1110 AND NVL(SUB_SEGMENT_NAME,'z') = NVL(l_mod_sum_rec.SUB_SEGMENT_NAME,'z')
1111 AND NVL(PRODUCT_ATTRIBUTE_TYPE,'z') = NVL(l_mod_sum_rec.PRODUCT_ATTRIBUTE_TYPE,'z')
1112 AND NVL(PRODUCT_ATTR_VALUE,'z') = NVL(l_mod_sum_rec.PRODUCT_ATTR_VALUE,'z')
1113 AND NVL(PRICING_ATTRIBUTE,'z') = NVL(l_mod_sum_rec.PRICING_ATTRIBUTE,'z');
1114 */
1115 BEGIN
1116
1117 FOR l_mod_sum_rec IN c_mod_sum LOOP
1118 FOR l_list_line IN c_list_line_id(l_mod_sum_rec) LOOP
1119 l_index := l_index + 1;
1120 l_line_mapping_tbl(l_index).org_line_id := l_mod_sum_rec.list_line_id;
1121 l_line_mapping_tbl(l_index).new_line_id := l_list_line.list_line_id;
1122 END LOOP;
1123 END LOOP;
1124
1125 RETURN l_line_mapping_tbl;
1126
1127 END get_rltd_line_id;
1128
1129
1130 PROCEDURE copy_discount_line(p_org_line_id IN NUMBER
1131 ,p_parent_line_id IN NUMBER
1132 ,p_offer_id IN NUMBER
1133 ,x_new_line_id OUT NOCOPY NUMBER)
1134 IS
1135 CURSOR c_new_line_id IS
1136 SELECT ozf_offer_discount_lines_s.NEXTVAL
1137 FROM DUAL;
1138
1139 CURSOR c_line_id_exists(l_id NUMBER) IS
1140 SELECT 1
1141 FROM DUAL
1142 WHERE EXISTS (SELECT 1
1143 FROM ozf_offer_discount_lines
1144 WHERE offer_discount_line_id = l_id);
1145
1146 CURSOR c_line_detail IS
1147 SELECT *
1148 FROM ozf_offer_discount_lines
1149 WHERE offer_discount_line_id = p_org_line_id;
1150
1151 l_line_detail c_line_detail%ROWTYPE;
1152 l_count NUMBER;
1153 BEGIN
1154 LOOP
1155 l_count := NULL;
1156
1157 OPEN c_new_line_id;
1158 FETCH c_new_line_id INTO x_new_line_id;
1159 CLOSE c_new_line_id;
1160
1161 OPEN c_line_id_exists(x_new_line_id);
1162 FETCH c_line_id_exists INTO l_count;
1163 CLOSE c_line_id_exists;
1164
1165 EXIT WHEN l_count IS NULL;
1166 END LOOP;
1167
1168 OPEN c_line_detail;
1169 FETCH c_line_detail INTO l_line_detail;
1170 CLOSE c_line_detail;
1171
1172 INSERT INTO ozf_offer_discount_lines(offer_discount_line_id
1173 ,parent_discount_line_id
1174 ,volume_from
1175 ,volume_to
1176 ,volume_operator
1177 ,volume_type
1178 ,volume_break_type
1179 ,discount
1180 ,discount_type
1181 ,tier_type
1182 ,tier_level
1183 ,incompatibility_group
1184 ,precedence
1185 ,bucket
1186 ,scan_value
1187 ,scan_data_quantity
1188 ,scan_unit_forecast
1189 ,channel_id
1190 ,adjustment_flag
1191 ,start_date_active
1192 ,end_date_active
1193 ,uom_code
1194 ,creation_date
1195 ,created_by
1196 ,last_update_date
1197 ,last_updated_by
1198 ,last_update_login
1199 ,object_version_number
1200 ,offer_id)
1201 VALUES(x_new_line_id
1202 ,p_parent_line_id
1203 ,l_line_detail.volume_from
1204 ,l_line_detail.volume_to
1205 ,l_line_detail.volume_operator
1206 ,l_line_detail.volume_type
1207 ,l_line_detail.volume_break_type
1208 ,l_line_detail.discount
1209 ,l_line_detail.discount_type
1210 ,l_line_detail.tier_type
1211 ,l_line_detail.tier_level
1212 ,l_line_detail.incompatibility_group
1213 ,l_line_detail.precedence
1214 ,l_line_detail.bucket
1215 ,l_line_detail.scan_value
1216 ,l_line_detail.scan_data_quantity
1217 ,l_line_detail.scan_unit_forecast
1218 ,l_line_detail.channel_id
1219 ,l_line_detail.adjustment_flag
1220 ,l_line_detail.start_date_active
1221 ,l_line_detail.end_date_active
1222 ,l_line_detail.uom_code
1223 ,SYSDATE
1224 ,FND_GLOBAL.user_id
1225 ,SYSDATE
1226 ,FND_GLOBAL.user_id
1227 ,FND_GLOBAL.conc_login_id
1228 ,1
1229 ,p_offer_id);
1230 END copy_discount_line;
1231
1232
1233 PROCEDURE copy_discount_prod(p_org_prod_id IN NUMBER
1234 ,p_parent_prod_id IN NUMBER
1235 ,p_offer_id IN NUMBER
1236 ,p_new_line_id IN NUMBER
1237 ,x_new_prod_id OUT NOCOPY NUMBER)
1238 IS
1239 CURSOR c_new_prod_id IS
1240 SELECT ozf_offer_discount_products_s.NEXTVAL
1241 FROM DUAL;
1242
1243 CURSOR c_prod_id_exists(l_id NUMBER) IS
1244 SELECT 1
1245 FROM DUAL
1246 WHERE EXISTS (SELECT 1
1247 FROM ozf_offer_discount_products
1248 WHERE off_discount_product_id = l_id);
1249
1250 CURSOR c_product_detail IS
1251 SELECT *
1252 FROM ozf_offer_discount_products
1253 WHERE off_discount_product_id = p_org_prod_id;
1254
1255 l_product_detail c_product_detail%ROWTYPE;
1256 l_count NUMBER;
1257 BEGIN
1258 LOOP
1259 l_count := NULL;
1260
1261 OPEN c_new_prod_id;
1262 FETCH c_new_prod_id INTO x_new_prod_id;
1263 CLOSE c_new_prod_id;
1264
1265 OPEN c_prod_id_exists(x_new_prod_id);
1266 FETCH c_prod_id_exists INTO l_count;
1267 CLOSE c_prod_id_exists;
1268
1269 EXIT WHEN l_count IS NULL;
1270 END LOOP;
1271
1272 OPEN c_product_detail;
1273 FETCH c_product_detail INTO l_product_detail;
1274 CLOSE c_product_detail;
1275
1276 INSERT INTO ozf_offer_discount_products(off_discount_product_id
1277 ,product_level
1278 ,product_id
1279 ,excluder_flag
1280 ,uom_code
1281 ,start_date_active
1282 ,end_date_active
1283 ,offer_discount_line_id
1284 ,offer_id
1285 ,creation_date
1286 ,created_by
1287 ,last_update_date
1288 ,last_updated_by
1289 ,last_update_login
1290 ,object_version_number
1291 ,parent_off_disc_prod_id)
1292 VALUES(x_new_prod_id
1293 ,l_product_detail.product_level
1294 ,l_product_detail.product_id
1295 ,l_product_detail.excluder_flag
1296 ,l_product_detail.uom_code
1297 ,l_product_detail.start_date_active
1298 ,l_product_detail.end_date_active
1299 ,p_new_line_id
1300 ,p_offer_id
1301 ,SYSDATE
1302 ,FND_GLOBAL.user_id
1303 ,SYSDATE
1304 ,FND_GLOBAL.user_id
1305 ,FND_GLOBAL.conc_login_id
1306 ,1
1307 ,p_parent_prod_id);
1308 END copy_discount_prod;
1309
1310
1311 PROCEDURE copy_na_line_offer(p_source_object_id IN NUMBER
1312 ,p_new_offer_id IN NUMBER)
1313 IS
1314 CURSOR c_parent_lines IS
1315 SELECT offer_discount_line_id
1316 FROM ozf_offer_discount_lines
1317 WHERE offer_id = (SELECT offer_id
1318 FROM ozf_offers
1319 WHERE qp_list_header_id = p_source_object_id)
1320 AND parent_discount_line_id IS NULL; -- start from main line, then process multi-tier and excl
1321
1322 CURSOR c_tier_excl_lines(p_parent_line_id NUMBER) IS
1323 SELECT offer_discount_line_id
1324 FROM ozf_offer_discount_lines
1325 WHERE offer_id = (SELECT offer_id
1326 FROM ozf_offers
1327 WHERE qp_list_header_id = p_source_object_id)
1328 AND parent_discount_line_id = p_parent_line_id;
1329
1330 CURSOR c_product_id(p_line_id NUMBER) IS
1331 SELECT off_discount_product_id
1332 FROM ozf_offer_discount_products
1333 WHERE offer_discount_line_id = p_line_id;
1334
1335 l_new_line_id NUMBER;
1336 l_product_id NUMBER;
1337 l_dummy NUMBER;
1338 BEGIN
1339 FOR l_parent_line IN c_parent_lines LOOP
1340 copy_discount_line(p_org_line_id => l_parent_line.offer_discount_line_id
1341 ,p_parent_line_id => NULL
1342 ,p_offer_id => p_new_offer_id
1343 ,x_new_line_id => l_new_line_id); -- new line_id. will be used for prod, tier, and excl
1344
1345 OPEN c_product_id(l_parent_line.offer_discount_line_id);
1346 FETCH c_product_id INTO l_product_id;
1347 CLOSE c_product_id;
1348
1349 copy_discount_prod(p_org_prod_id => l_product_id
1350 ,p_parent_prod_id => NULL
1351 ,p_offer_id => p_new_offer_id
1352 ,p_new_line_id => l_new_line_id
1353 ,x_new_prod_id => l_dummy);
1354
1355 FOR l_tier_excl_line IN c_tier_excl_lines(l_parent_line.offer_discount_line_id) LOOP
1356 copy_discount_line(p_org_line_id => l_tier_excl_line.offer_discount_line_id
1357 ,p_parent_line_id => l_new_line_id
1358 ,p_offer_id => p_new_offer_id
1359 ,x_new_line_id => l_dummy);
1360
1361 END LOOP;
1362 END LOOP;
1363 END copy_na_line_offer;
1364
1365
1366 PROCEDURE copy_na_header_offer(p_source_object_id IN NUMBER
1367 ,p_new_offer_id IN NUMBER)
1368 IS
1369 CURSOR c_line_tiers IS
1370 SELECT offer_discount_line_id
1371 FROM ozf_offer_discount_lines
1372 WHERE offer_id = (SELECT offer_id
1373 FROM ozf_offers
1374 WHERE qp_list_header_id = p_source_object_id);
1375
1376 CURSOR c_parent_products IS
1377 SELECT off_discount_product_id
1378 FROM ozf_offer_discount_products
1379 WHERE offer_id = (SELECT offer_id
1380 FROM ozf_offers
1381 WHERE qp_list_header_id = p_source_object_id)
1382 AND parent_off_disc_prod_id IS NULL;
1383
1384 CURSOR c_excl_products(p_parent_prod_id NUMBER) IS
1385 SELECT off_discount_product_id
1386 FROM ozf_offer_discount_products
1387 WHERE offer_id = (SELECT offer_id
1388 FROM ozf_offers
1389 WHERE qp_list_header_id = p_source_object_id)
1390 AND parent_off_disc_prod_id = p_parent_prod_id;
1391
1392 l_new_prod_id NUMBER;
1393 l_dummy NUMBER;
1394 BEGIN
1395 FOR l_line_tier IN c_line_tiers LOOP
1396 copy_discount_line(p_org_line_id => l_line_tier.offer_discount_line_id
1397 ,p_parent_line_id => NULL
1398 ,p_offer_id => p_new_offer_id
1399 ,x_new_line_id => l_dummy);
1400 END LOOP;
1401
1402 FOR l_parent_product IN c_parent_products LOOP
1403 copy_discount_prod(p_org_prod_id => l_parent_product.off_discount_product_id
1404 ,p_parent_prod_id => NULL
1405 ,p_offer_id => p_new_offer_id
1406 ,p_new_line_id => -1
1407 ,x_new_prod_id => l_new_prod_id);
1408
1409 FOR l_excl_prod IN c_excl_products(l_parent_product.off_discount_product_id) LOOP
1410 copy_discount_prod(p_org_prod_id => l_excl_prod.off_discount_product_id
1411 ,p_parent_prod_id => l_new_prod_id
1412 ,p_offer_id => p_new_offer_id
1413 ,p_new_line_id => -1
1414 ,x_new_prod_id => l_dummy);
1415 END LOOP;
1416 END LOOP;
1417 END copy_na_header_offer;
1418
1419
1420 PROCEDURE copy_na_market_elig(p_source_object_id IN NUMBER
1421 ,p_new_offer_id IN NUMBER)
1422 IS
1423 CURSOR c_new_qual_id IS
1424 SELECT ozf_offer_qualifiers_s.NEXTVAL
1425 FROM DUAL;
1426
1427 CURSOR c_qual_id_exists(l_id NUMBER) IS
1428 SELECT 1
1429 FROM DUAL
1430 WHERE EXISTS (SELECT 1
1431 FROM ozf_offer_qualifiers
1432 WHERE qualifier_id = l_id);
1433
1434 CURSOR c_market_elig IS
1435 SELECT *
1436 FROM ozf_offer_qualifiers
1437 WHERE offer_id = (SELECT offer_id
1438 FROM ozf_offers
1439 WHERE qp_list_header_id = p_source_object_id);
1440
1441 l_new_qual_id NUMBER;
1442 l_count NUMBER;
1443 BEGIN
1444 LOOP
1445 l_count := NULL;
1446
1447 OPEN c_new_qual_id;
1448 FETCH c_new_qual_id INTO l_new_qual_id;
1449 CLOSE c_new_qual_id;
1450
1451 OPEN c_qual_id_exists(l_new_qual_id);
1452 FETCH c_qual_id_exists INTO l_count;
1453 CLOSE c_qual_id_exists;
1454
1455 EXIT WHEN l_count IS NULL;
1456 END LOOP;
1457
1458 FOR l_market_elig IN c_market_elig LOOP
1459 INSERT INTO ozf_offer_qualifiers(qualifier_id
1460 ,creation_date
1461 ,created_by
1462 ,last_update_date
1463 ,last_updated_by
1464 ,last_update_login
1465 ,qualifier_grouping_no
1466 ,qualifier_context
1467 ,qualifier_attribute
1468 ,qualifier_attr_value
1469 ,start_date_active
1470 ,end_date_active
1471 ,offer_id
1472 ,offer_discount_line_id
1473 ,context
1474 ,attribute1
1475 ,attribute2
1476 ,attribute3
1477 ,attribute4
1478 ,attribute5
1479 ,attribute6
1480 ,attribute7
1481 ,attribute8
1482 ,attribute9
1483 ,attribute10
1484 ,attribute11
1485 ,attribute12
1486 ,attribute13
1487 ,attribute14
1488 ,attribute15
1489 ,active_flag
1490 ,object_version_number)
1491 VALUES(l_new_qual_id
1492 ,SYSDATE
1493 ,FND_GLOBAL.user_id
1494 ,SYSDATE
1495 ,FND_GLOBAL.user_id
1496 ,FND_GLOBAL.conc_login_id
1497 ,l_market_elig.qualifier_grouping_no
1498 ,l_market_elig.qualifier_context
1499 ,l_market_elig.qualifier_attribute
1500 ,l_market_elig.qualifier_attr_value
1501 ,l_market_elig.start_date_active
1502 ,l_market_elig.end_date_active
1503 ,p_new_offer_id
1504 ,NULL
1505 ,l_market_elig.context
1506 ,l_market_elig.attribute1
1507 ,l_market_elig.attribute2
1508 ,l_market_elig.attribute3
1509 ,l_market_elig.attribute4
1510 ,l_market_elig.attribute5
1511 ,l_market_elig.attribute6
1512 ,l_market_elig.attribute7
1513 ,l_market_elig.attribute8
1514 ,l_market_elig.attribute9
1515 ,l_market_elig.attribute10
1516 ,l_market_elig.attribute11
1517 ,l_market_elig.attribute12
1518 ,l_market_elig.attribute13
1519 ,l_market_elig.attribute14
1520 ,l_market_elig.attribute15
1521 ,l_market_elig.active_flag
1522 ,1);
1523 END LOOP;
1524 END copy_na_market_elig;
1525
1526
1527
1528
1529 PROCEDURE copy_offer_detail(
1530 p_api_version IN NUMBER,
1531 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1532 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1533 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1534 x_return_status OUT NOCOPY VARCHAR2,
1535 x_msg_count OUT NOCOPY NUMBER,
1536 x_msg_data OUT NOCOPY VARCHAR2,
1537 p_source_object_id IN NUMBER,
1541 p_custom_setup_id IN NUMBER)
1538 p_attributes_table IN AMS_CpyUtility_PVT.copy_attributes_table_type,
1539 p_copy_columns_table IN AMS_CpyUtility_PVT.copy_columns_table_type,
1540 x_new_object_id OUT NOCOPY NUMBER,
1542 IS
1543
1544 l_api_name CONSTANT VARCHAR2(30) := 'copy_offer_detail';
1545 l_api_version_number CONSTANT NUMBER := 1.0;
1546 l_src_list_header_id NUMBER;
1547 l_new_list_header_id NUMBER;
1548 l_index1 NUMBER := 0;
1549 l_index2 NUMBER := 0;
1550 l_modifier_list_rec ozf_offer_pvt.modifier_list_rec_type;
1551
1552 l_return_status VARCHAR2(1);
1553
1554 l_errnum NUMBER;
1555 l_errcode VARCHAR2(80);
1556 l_errmsg VARCHAR2(3000);
1557 l_error_location NUMBER;
1558
1559 l_dummy NUMBER;
1560 l_errbuf VARCHAR2(3000);
1561 l_retcode NUMBER;
1562 l_count_ozf_code NUMBER;
1563 l_count_qp_code NUMBER;
1564 l_code_is_unique VARCHAR2(1);
1565 l_new_offer_id NUMBER;
1566 l_new_limit_id NUMBER;
1567 l_new_limit_line_id NUMBER;
1568 l_new_qual_id NUMBER;
1569 l_new_qual_line_id NUMBER;
1570 l_line_mapping_tbl line_mapping_tbl_type;
1571 l_limit_mapping_tbl line_mapping_tbl_type;
1572 l_qual_mapping_tbl line_mapping_tbl_type;
1573 l_new_modifier_id NUMBER;
1574 l_new_rltd_modifier_id NUMBER;
1575 l_related_lines_rec ozf_related_lines_pvt.related_lines_rec_type;
1576 l_related_deal_lines_id NUMBER;
1577 l_act_product_id NUMBER;
1578 l_temp_id NUMBER;
1579 l_prev_line_id NUMBER;
1580 l_default_team NUMBER;
1581
1582 CURSOR c_list_header_detail IS
1583 SELECT *
1584 FROM qp_list_headers
1585 WHERE list_header_id = p_source_object_id;
1586 l_list_header_rec c_list_header_detail%ROWTYPE;
1587
1588 CURSOR c_offer_detail IS
1589 SELECT *
1590 FROM ozf_offers
1591 WHERE qp_list_header_id = p_source_object_id;
1592 l_offer_rec c_offer_detail%ROWTYPE;
1593
1594 CURSOR c_count_ozf_code(l_code VARCHAR2) IS
1595 SELECT 1
1596 FROM DUAL
1597 WHERE EXISTS (SELECT 1
1598 FROM ozf_offers
1599 WHERE offer_code = l_code);
1600
1601 CURSOR c_count_qp_code(l_code VARCHAR2) IS
1602 SELECT 1
1603 FROM DUAL
1604 WHERE EXISTS (SELECT 1
1605 FROM qp_list_headers
1606 WHERE name = l_code);
1607
1608 CURSOR c_get_list_header_id(l_name VARCHAR2) IS
1609 SELECT list_header_id
1610 FROM qp_list_headers
1611 WHERE name = l_name;
1612
1613 CURSOR c_new_offer_id IS
1614 SELECT ozf_offers_s.NEXTVAL
1615 FROM DUAL;
1616
1617 CURSOR c_offer_id_exists (l_id IN NUMBER) IS
1618 SELECT 1
1619 FROM DUAL
1620 WHERE EXISTS (SELECT 1
1621 FROM ozf_offers
1622 WHERE offer_id = l_id);
1623
1624 CURSOR c_new_limit_id IS
1625 SELECT qp_limits_s.NEXTVAL
1626 FROM DUAL;
1627
1628 CURSOR c_limit_id_exists (l_id IN NUMBER) IS
1629 SELECT 1
1630 FROM DUAL
1631 WHERE EXISTS (SELECT 1
1632 FROM qp_limits
1633 WHERE limit_id = l_id);
1634
1635 CURSOR c_related_deal_lines IS
1636 SELECT *
1637 FROM ozf_related_deal_lines
1638 WHERE qp_list_header_id = p_source_object_id;
1639
1640 CURSOR c_vol_offer_tiers IS
1641 SELECT *
1642 FROM ozf_volume_offer_tiers
1643 WHERE qp_list_header_id = p_source_object_id;
1644 l_vol_offr_tier_rec ozf_vol_offr_pvt.vol_offr_tier_rec_type;
1645
1646 CURSOR c_act_products IS
1647 SELECT *
1648 FROM ams_act_products
1649 WHERE arc_act_product_used_by = 'OFFR'
1650 AND act_product_used_by_id = p_source_object_id;
1651 l_act_product_rec ams_actproduct_pvt.act_product_rec_type;
1652
1653 CURSOR c_excluded_products(l_act_prod_id NUMBER) IS
1654 SELECT *
1655 FROM ams_act_products
1656 WHERE arc_act_product_used_by = 'PROD'
1657 AND act_product_used_by_id = l_act_prod_id;
1658
1659 CURSOR c_offer_lines IS
1660 SELECT list_line_id
1661 FROM qp_list_lines
1662 WHERE list_header_id = p_source_object_id;
1663
1664 CURSOR c_line_limits IS
1665 SELECT *
1666 FROM qp_limits
1667 WHERE list_header_id = p_source_object_id
1668 ORDER BY list_line_id;
1669
1670 CURSOR c_contact_point IS
1671 SELECT *
1672 FROM ams_act_contact_points
1673 WHERE arc_contact_used_by = 'OFFR'
1674 AND act_contact_used_by_id = p_source_object_id;
1675 l_contact_point_rec ams_cnt_point_pvt.cnt_point_rec_type;
1676
1677 CURSOR c_market_elig IS
1678 SELECT qualifier_context
1679 ,qualifier_attribute
1680 ,qualifier_attr_value
1681 ,qualifier_attr_value_to
1682 ,comparison_operator_code
1683 ,qualifier_grouping_no
1684 ,list_line_id
1685 ,list_header_id
1686 ,start_date_active
1687 ,end_date_active
1688 FROM qp_qualifiers
1689 WHERE list_header_id = p_source_object_id;
1690 l_qualifier_tbl ozf_offer_pvt.qualifiers_tbl_type;
1691
1692 CURSOR c_adv_options IS
1693 SELECT modifier_level_code
1694 ,pricing_phase_id
1695 ,incompatibility_grp_code
1696 ,product_precedence
1697 ,pricing_group_sequence
1698 ,print_on_invoice_flag
1699 FROM qp_list_lines
1700 WHERE list_header_id = p_source_object_id;
1701 l_adv_options_rec Ozf_Offer_Pvt.Advanced_Option_Rec_Type;
1702
1703 -- bug 3747303
1704 CURSOR c_vol_off_discount IS
1705 SELECT discount_type_code, discount
1706 FROM ozf_volume_offer_tiers
1707 WHERE qp_list_header_id = p_source_object_id
1708 AND tier_value_from =
1709 (SELECT MIN(tier_value_from)
1710 FROM ozf_volume_offer_tiers
1711 WHERE qp_list_header_id = p_source_object_id);
1712 l_discount_type VARCHAR2(30);
1713 l_discount NUMBER;
1714 l_offer_id NUMBER;
1715 BEGIN
1716 -- Standard Start of API savepoint
1717 SAVEPOINT copy_offer_detail;
1718
1719 -- Standard call to check for call compatibility.
1720 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1721 p_api_version,
1722 l_api_name,
1723 G_PKG_NAME)
1724 THEN
1725 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1726 END IF;
1727
1728 -- Initialize message list if p_init_msg_list is set to TRUE.
1729 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1730 FND_MSG_PUB.initialize;
1731 END IF;
1732
1733 -- Initialize API return status to SUCCESS
1734 x_return_status := FND_API.G_RET_STS_SUCCESS;
1735
1736 OPEN c_offer_detail;
1737 FETCH c_offer_detail INTO l_offer_rec;
1738 CLOSE c_offer_detail;
1739
1740 l_offer_rec.custom_setup_id := p_custom_setup_id;
1741
1742 -- getting values from UI
1743 AMS_CpyUtility_PVT.get_column_value ('newObjName', p_copy_columns_table, l_modifier_list_rec.description);
1744 AMS_CpyUtility_PVT.get_column_value ('offerCode', p_copy_columns_table, l_offer_rec.offer_code);
1745 --AMS_CpyUtility_PVT.get_column_value ('offerCode', p_copy_columns_table, l_modifier_list_rec.name);
1746 AMS_CpyUtility_PVT.get_column_value ('startDateActive', p_copy_columns_table, l_modifier_list_rec.start_date_active);
1747 AMS_CpyUtility_PVT.get_column_value ('endDateActive', p_copy_columns_table, l_modifier_list_rec.end_date_active);
1748 AMS_CpyUtility_PVT.get_column_value ('ownerId', p_copy_columns_table, l_offer_rec.owner_id);
1749 AMS_CpyUtility_PVT.get_column_value ('description', p_copy_columns_table, l_modifier_list_rec.comments);
1750
1751 -- bug fix 2779988: validate start date and end date
1752 IF l_modifier_list_rec.start_date_active IS NOT NULL
1753 AND l_modifier_list_rec.start_date_active <> FND_API.G_MISS_DATE THEN
1754 IF l_modifier_list_rec.start_date_active < TRUNC(SYSDATE) AND l_offer_rec.offer_type <> 'NET_ACCRUAL' THEN
1755 Fnd_Message.SET_NAME('OZF','OZF_OFFR_STARTDATE_LT_SYSDATE');
1756 Fnd_Msg_Pub.ADD;
1757 RAISE FND_API.G_EXC_ERROR;
1758 END IF;
1759 END IF; -- end validation start date if
1760
1761 IF l_modifier_list_rec.end_date_active IS NOT NULL
1762 AND l_modifier_list_rec.end_date_active <> FND_API.G_MISS_DATE THEN
1763 IF l_modifier_list_rec.end_date_active < TRUNC(SYSDATE) AND l_offer_rec.offer_type <> 'NET_ACCRUAL' THEN
1764 Fnd_Message.SET_NAME('OZF','OZF_OFFR_ENDDATE_LT_SYSDATE');
1765 Fnd_Msg_Pub.ADD;
1766 RAISE FND_API.G_EXC_ERROR;
1767 END IF;
1768 END IF; -- end end date validation if
1769
1770 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
1771 IF l_modifier_list_rec.start_date_active IS NULL THEN
1772 ozf_utility_pvt.error_message('OZF_OFFR_NO_START_DATE');
1773 RAISE FND_API.G_EXC_ERROR;
1774 END IF;
1775 END IF; -- end scan data lumpsum start date validation if
1776
1777 IF l_modifier_list_rec.start_date_active IS NOT NULL
1778 AND l_modifier_list_rec.start_date_active <> FND_API.G_MISS_DATE
1779 AND l_modifier_list_rec.end_date_active IS NOT NULL
1780 AND l_modifier_list_rec.end_date_active <> FND_API.G_MISS_DATE
1781 THEN
1782 IF l_modifier_list_rec.start_date_active > l_modifier_list_rec.end_date_active THEN
1783 Fnd_Message.SET_NAME('QP','QP_STRT_DATE_BFR_END_DATE');
1784 Fnd_Msg_Pub.ADD;
1785 RAISE FND_API.G_EXC_ERROR;
1786 END IF;
1787 END IF; -- end start date before end date validation
1788
1789 -- validate source code
1790 IF l_offer_rec.offer_code IS NULL OR l_offer_rec.offer_code = FND_API.G_MISS_CHAR THEN
1791 LOOP
1792 l_count_ozf_code := 0;
1793 l_count_qp_code := 0;
1794
1795 l_offer_rec.offer_code := Ams_Sourcecode_Pvt.get_new_source_code (
1796 p_object_type => 'OFFR',
1797 p_custsetup_id => l_offer_rec.custom_setup_id,
1798 p_global_flag => Fnd_Api.g_false
1799 );
1800
1801 OPEN c_count_ozf_code(l_offer_rec.offer_code);
1802 FETCH c_count_ozf_code INTO l_count_ozf_code;
1803 CLOSE c_count_ozf_code;
1804
1805 OPEN c_count_qp_code(l_offer_rec.offer_code);
1806 FETCH c_count_qp_code INTO l_count_qp_code;
1807 CLOSE c_count_qp_code;
1808
1809 IF l_count_ozf_code = 0 AND l_count_qp_code = 0 THEN
1810 l_code_is_unique := 'Y';
1811 ELSE
1812 l_code_is_unique := 'N';
1816 END LOOP;
1813 END IF;
1814
1815 EXIT WHEN l_code_is_unique = 'Y';
1817 ELSE
1818 OPEN c_count_ozf_code(l_offer_rec.offer_code);
1819 FETCH c_count_ozf_code INTO l_count_ozf_code;
1820 CLOSE c_count_ozf_code;
1821
1822 OPEN c_count_qp_code(l_offer_rec.offer_code);
1823 FETCH c_count_qp_code INTO l_count_qp_code;
1824 CLOSE c_count_qp_code;
1825
1826 IF l_count_ozf_code > 0 OR l_count_qp_code > 0 THEN
1827 ozf_utility_pvt.error_message('OZF_OFFR_COPY_DUP_CODE');
1828 RAISE FND_API.G_EXC_ERROR;
1829 END IF;
1830 END IF; -- end validate source code
1831
1832 IF l_offer_rec.offer_type <> 'VOLUME_OFFER' THEN
1833 OPEN c_list_header_detail;
1834 FETCH c_list_header_detail INTO l_list_header_rec;
1835 CLOSE c_list_header_detail;
1836 -- copy header and lines, limits and related lines not copied yet
1837
1838 QP_COPY_MODIFIERS_PVT.Copy_Discounts
1839 (
1840 errbuf => l_errbuf
1841 , retcode => l_retcode
1842 , p_from_list_header_id => p_source_object_id
1843 , p_new_price_list_name => l_offer_rec.offer_code
1844 , p_description => l_modifier_list_rec.description
1845 , p_start_date_active => fnd_date.date_to_canonical(l_modifier_list_rec.start_date_active)
1846 , p_end_date_active => fnd_date.date_to_canonical(l_modifier_list_rec.end_date_active)
1847 , p_rounding_factor => NULL
1848 , p_effective_dates_flag => 'N'
1849 --added for moac bug 4673872
1850 , p_global_flag => l_list_header_rec.global_flag
1851 , p_org_id => l_list_header_rec.orig_org_id
1852 );
1853
1854 -- get new header id
1855 OPEN c_get_list_header_id(l_offer_rec.offer_code);
1856 FETCH c_get_list_header_id INTO l_offer_rec.qp_list_header_id;
1857 CLOSE c_get_list_header_id;
1858 --14-Jan-2003 JULOU Copy did not put the new description from the UI
1859 -- put description from UI
1860 UPDATE qp_list_headers_b
1861 SET comments = l_modifier_list_rec.comments
1862 WHERE list_header_id = l_offer_rec.qp_list_header_id;
1863
1864 -- insert into ozf_offers
1865 LOOP
1866 l_dummy := NULL;
1867 OPEN c_new_offer_id;
1868 FETCH c_new_offer_id INTO l_offer_rec.offer_id;
1869 CLOSE c_new_offer_id;
1870
1871 OPEN c_offer_id_exists(l_offer_rec.offer_id);
1872 FETCH c_offer_id_exists INTO l_dummy;
1873 CLOSE c_offer_id_exists;
1874 EXIT WHEN l_dummy IS NULL;
1875 END LOOP;
1876
1877
1878
1879 INSERT INTO ozf_offers
1880 (offer_id
1881 ,qp_list_header_id
1882 ,offer_type
1883 ,offer_code
1884 ,reusable
1885 ,custom_setup_id
1886 ,user_status_id
1887 ,owner_id
1888 ,object_version_number
1889 ,customer_reference
1890 ,buying_group_contact_id
1891 ,perf_date_from
1892 ,perf_date_to
1893 ,status_code
1894 ,order_value_discount_type
1895 ,modifier_level_code
1896 ,offer_amount
1897 ,lumpsum_amount
1898 ,lumpsum_payment_type
1899 ,security_group_id
1900 ,distribution_type
1901 ,budget_amount_fc
1902 ,budget_amount_tc
1903 ,transaction_currency_code
1904 ,functional_currency_code
1905 ,account_closed_flag
1906 ,activity_media_id
1907 ,qualifier_id
1908 ,qualifier_type
1909 ,budget_offer_yn
1910 ,creation_date
1911 ,created_by
1912 ,last_updated_by
1913 ,last_update_date
1914 ,last_update_login
1915 ,qualifier_deleted
1916 ,break_type
1917 ,volume_offer_type
1918 ,confidential_flag
1919 ,budget_source_type
1920 ,budget_source_id
1921 ,retroactive
1922 ,source_from_parent
1923 ,last_recal_date
1924 ,buyer_name
1925 ,tier_level
1926 ,na_rule_header_id
1927 ,autopay_flag
1928 ,autopay_days
1929 ,autopay_method
1930 ,autopay_party_attr
1931 ,autopay_party_id
1932 ,beneficiary_account_id
1933 ,sales_method_flag
1934 ,org_id
1935 ,fund_request_curr_code)
1936 VALUES
1937 (l_offer_rec.offer_id
1938 ,l_offer_rec.qp_list_header_id
1939 ,l_offer_rec.offer_type
1940 ,l_offer_rec.offer_code
1941 ,l_offer_rec.reusable
1942 ,l_offer_rec.custom_setup_id
1943 ,ozf_utility_pvt.get_default_user_status('OZF_OFFER_STATUS','DRAFT')
1944 ,l_offer_rec.owner_id
1945 ,1
1946 ,l_offer_rec.customer_reference
1947 ,l_offer_rec.buying_group_contact_id
1948 ,l_offer_rec.perf_date_from
1949 ,l_offer_rec.perf_date_to
1950 ,'DRAFT'
1951 ,l_offer_rec.order_value_discount_type
1952 ,l_offer_rec.modifier_level_code
1953 ,l_offer_rec.offer_amount
1954 ,l_offer_rec.lumpsum_amount
1955 ,l_offer_rec.lumpsum_payment_type
1956 ,l_offer_rec.security_group_id
1957 ,l_offer_rec.distribution_type
1958 ,l_offer_rec.budget_amount_fc
1959 ,l_offer_rec.budget_amount_tc
1960 ,l_offer_rec.transaction_currency_code
1961 ,l_offer_rec.functional_currency_code
1962 ,'N'
1963 ,l_offer_rec.activity_media_id
1964 ,l_offer_rec.qualifier_id
1965 ,l_offer_rec.qualifier_type
1966 ,DECODE(l_offer_rec.custom_setup_id, 101, 'Y', 108, 'Y', 'N')--l_offer_rec.budget_offer_yn
1967 ,SYSDATE
1968 ,FND_GLOBAL.user_id
1969 ,FND_GLOBAL.user_id
1970 ,SYSDATE
1971 ,FND_GLOBAL.conc_login_id
1972 ,NULL
1973 ,l_offer_rec.break_type
1974 ,l_offer_rec.volume_offer_type
1975 ,l_offer_rec.confidential_flag
1976 ,l_offer_rec.budget_source_type
1977 ,l_offer_rec.budget_source_id
1978 ,l_offer_rec.retroactive
1979 ,l_offer_rec.source_from_parent
1980 ,l_modifier_list_rec.start_date_active -- default last_recal_date to offer start date
1981 ,l_offer_rec.buyer_name
1982 ,l_offer_rec.tier_level
1983 ,l_offer_rec.na_rule_header_id
1984 ,l_offer_rec.autopay_flag
1985 ,l_offer_rec.autopay_days
1986 ,l_offer_rec.autopay_method
1987 ,l_offer_rec.autopay_party_attr
1988 ,l_offer_rec.autopay_party_id
1989 ,l_offer_rec.beneficiary_account_id
1990 ,l_offer_rec.sales_method_flag
1991 ,l_offer_rec.org_id
1992 ,NVL(l_offer_rec.transaction_currency_code, fnd_profile.value('JTF_PROFILE_DEFAULT_CURRENCY')));
1993 -- end insert into ozf_offers
1994
1995 --7627663,insert the offer details to AMS_SOURCE_CODES for all offers except volume offer.
1996 AMS_CampaignRules_PVT.push_source_code(
1997 l_offer_rec.offer_code,
1998 'OFFR',
1999 l_offer_rec.qp_list_header_id
2000 );
2001
2002
2003 -- create access for new offer owner
2004 INSERT INTO ams_act_access
2005 (activity_access_id
2006 ,last_update_date
2007 ,last_updated_by
2008 ,creation_date
2009 ,created_by
2010 ,act_access_to_object_id
2011 ,arc_act_access_to_object
2012 ,user_or_role_id
2013 ,arc_user_or_role_type
2014 ,last_update_login
2015 ,object_version_number
2016 ,active_from_date
2017 ,admin_flag
2018 ,approver_flag
2019 ,active_to_date
2020 ,security_group_id
2021 ,delete_flag
2022 ,owner_flag)
2023 VALUES
2024 (ams_act_access_s.NEXTVAL
2025 ,SYSDATE
2026 ,FND_GLOBAL.user_id
2027 ,SYSDATE
2028 ,FND_GLOBAL.user_id
2029 ,l_offer_rec.qp_list_header_id
2030 ,'OFFR'
2031 ,l_offer_rec.owner_id
2032 ,'USER'
2033 ,FND_GLOBAL.conc_login_id
2034 ,1
2035 ,SYSDATE
2036 ,NULL
2037 ,NULL
2038 ,NULL
2039 ,NULL
2040 ,'N'
2041 ,'Y');
2042 -- end create access for owner
2043
2044 -- create access in ams_act_access_denorm for new offer owner
2045 INSERT INTO ams_act_access_denorm
2046 (access_denorm_id
2047 ,object_type
2048 ,object_id
2049 ,resource_id
2050 ,edit_metrics_yn
2051 ,source_code
2052 ,access_type
2053 ,creation_date
2054 ,last_update_date
2055 ,last_update_login
2056 ,last_updated_by
2057 ,created_by)
2058 VALUES
2059 (ams_act_access_denorm_s.NEXTVAL
2060 ,'OFFR'
2061 ,l_offer_rec.qp_list_header_id
2062 ,l_offer_rec.owner_id
2063 ,'Y'
2064 ,l_offer_rec.offer_code
2065 ,NULL
2066 ,SYSDATE
2067 ,SYSDATE
2068 ,FND_GLOBAL.conc_login_id
2069 ,FND_GLOBAL.user_id
2070 ,FND_GLOBAL.user_id);
2071 -- end create access in ams_act_access_denorm
2072
2073 -- create access for default team
2074 l_default_team := FND_PROFILE.value('OZF_DEFAULT_OFFER_TEAM');
2075 IF l_default_team IS NOT NULL AND l_default_team <> FND_API.G_MISS_NUM THEN
2076 INSERT INTO ams_act_access
2077 (activity_access_id
2078 ,last_update_date
2079 ,last_updated_by
2080 ,creation_date
2081 ,created_by
2082 ,act_access_to_object_id
2083 ,arc_act_access_to_object
2084 ,user_or_role_id
2085 ,arc_user_or_role_type
2086 ,last_update_login
2087 ,object_version_number
2088 ,active_from_date
2089 ,admin_flag
2090 ,approver_flag
2091 ,active_to_date
2092 ,security_group_id
2093 ,delete_flag
2094 ,owner_flag)
2095 VALUES
2096 (ams_act_access_s.NEXTVAL
2097 ,SYSDATE
2098 ,FND_GLOBAL.user_id
2099 ,SYSDATE
2100 ,FND_GLOBAL.user_id
2101 ,l_offer_rec.qp_list_header_id
2102 ,'OFFR'
2103 ,l_default_team
2104 ,'GROUP'
2105 ,FND_GLOBAL.conc_login_id
2106 ,1
2107 ,SYSDATE
2108 ,NULL
2109 ,NULL
2110 ,NULL
2111 ,NULL
2112 ,'N'
2113 ,'N');
2114 END IF;
2115 -- end create default access for default team
2116
2117 -- copy NET_ACCRUAL offer
2118 IF l_offer_rec.offer_type = 'NET_ACCRUAL' THEN
2119 IF l_offer_rec.tier_level = 'LINE' THEN -- line based discount
2120 copy_na_line_offer(p_source_object_id => p_source_object_id
2121 ,p_new_offer_id => l_offer_rec.offer_id);
2122 ELSIF l_offer_rec.tier_level = 'HEADER' THEN-- tier based discount
2123 copy_na_header_offer(p_source_object_id => p_source_object_id
2124 ,p_new_offer_id => l_offer_rec.offer_id);
2125 END IF;
2126 END IF;
2127
2128 -- build list line id mapping table
2129 l_line_mapping_tbl := get_rltd_line_id(p_source_object_id, l_offer_rec.qp_list_header_id);
2130 l_limit_mapping_tbl := get_rltd_line_id(p_source_object_id, l_offer_rec.qp_list_header_id);
2131 -- end build mapping table
2132
2133 -- process limits
2134 FOR l_line_limits IN c_line_limits LOOP
2135 IF l_line_limits.list_line_id = -1 THEN
2136 LOOP
2137 --l_new_limit_id := NULL;
2138 l_dummy := NULL;
2139 OPEN c_new_limit_id;
2140 FETCH c_new_limit_id INTO l_new_limit_id;
2141 CLOSE c_new_limit_id;
2142
2143 OPEN c_limit_id_exists(l_new_limit_id);
2144 FETCH c_limit_id_exists INTO l_dummy;
2145 CLOSE c_limit_id_exists;
2146 EXIT WHEN l_dummy IS NULL;
2147 END LOOP;
2148
2149 INSERT INTO qp_limits
2150 (limit_id
2151 ,creation_date
2152 ,created_by
2153 ,last_update_date
2154 ,last_updated_by
2155 ,last_update_login
2156 ,program_application_id
2157 ,program_id
2158 ,program_update_date
2159 ,request_id
2160 ,list_header_id
2161 ,list_line_id
2162 ,limit_number
2163 ,basis
2164 ,organization_flag
2165 ,limit_level_code
2166 ,limit_exceed_action_code
2167 ,limit_hold_flag
2168 ,multival_attr1_type
2169 ,multival_attr1_context
2170 ,multival_attribute1
2171 ,multival_attr1_datatype
2172 ,multival_attr2_type
2173 ,multival_attr2_context
2174 ,multival_attribute2
2175 ,multival_attr2_datatype
2176 ,amount
2177 ,context
2178 ,attribute1
2179 ,attribute2
2180 ,attribute3
2181 ,attribute4
2182 ,attribute5
2183 ,attribute6
2184 ,attribute7
2185 ,attribute8
2186 ,attribute9
2187 ,attribute10
2188 ,attribute11
2189 ,attribute12
2190 ,attribute13
2191 ,attribute14
2192 ,attribute15
2193 ,each_attr_exists
2194 ,non_each_attr_count
2195 ,total_attr_count)
2196 VALUES
2197 (l_new_limit_id
2198 ,SYSDATE
2199 ,FND_GLOBAL.user_id
2200 ,SYSDATE
2201 ,FND_GLOBAL.user_id
2202 ,FND_GLOBAL.conc_login_id
2203 ,l_line_limits.program_application_id
2204 ,l_line_limits.program_id
2205 ,SYSDATE
2206 ,NULL
2207 ,l_offer_rec.qp_list_header_id
2208 ,-1
2209 ,l_line_limits.limit_number
2210 ,l_line_limits.basis
2211 ,l_line_limits.organization_flag
2212 ,l_line_limits.limit_level_code
2213 ,l_line_limits.limit_exceed_action_code
2214 ,l_line_limits.limit_hold_flag
2215 ,l_line_limits.multival_attr1_type
2216 ,l_line_limits.multival_attr1_context
2217 ,l_line_limits.multival_attribute1
2218 ,l_line_limits.multival_attr1_datatype
2219 ,l_line_limits.multival_attr2_type
2220 ,l_line_limits.multival_attr2_context
2221 ,l_line_limits.multival_attribute2
2222 ,l_line_limits.multival_attr2_datatype
2223 ,l_line_limits.amount
2224 ,l_line_limits.context
2225 ,l_line_limits.attribute1
2226 ,l_line_limits.attribute2
2227 ,l_line_limits.attribute3
2228 ,l_line_limits.attribute4
2229 ,l_line_limits.attribute5
2230 ,l_line_limits.attribute6
2231 ,l_line_limits.attribute7
2232 ,l_line_limits.attribute8
2233 ,l_line_limits.attribute9
2234 ,l_line_limits.attribute10
2235 ,l_line_limits.attribute11
2236 ,l_line_limits.attribute12
2237 ,l_line_limits.attribute13
2238 ,l_line_limits.attribute14
2239 ,l_line_limits.attribute15
2240 ,l_line_limits.each_attr_exists
2241 ,l_line_limits.non_each_attr_count
2242 ,l_line_limits.total_attr_count);
2243 ELSE
2244 IF l_prev_line_id <> l_line_limits.list_line_id THEN
2245 -- remove assigned source line id from mapping tablel
2246 FOR i IN 1..l_limit_mapping_tbl.count LOOP
2247 IF l_new_limit_line_id = l_limit_mapping_tbl(i).new_line_id THEN
2248 l_limit_mapping_tbl(i) := NULL;
2249 END IF;
2250 END LOOP;
2251 END IF;
2252
2253 l_new_limit_line_id := NULL;
2254
2255 FOR i IN 1..l_limit_mapping_tbl.count LOOP
2256 IF l_line_limits.list_line_id = l_limit_mapping_tbl(i).org_line_id THEN
2257 l_new_limit_line_id := l_limit_mapping_tbl(i).new_line_id;
2258 -- find matching line, populate limits
2259 LOOP
2260 --l_new_limit_id := NULL;
2261 l_dummy := NULL;
2262 OPEN c_new_limit_id;
2263 FETCH c_new_limit_id INTO l_new_limit_id;
2264 CLOSE c_new_limit_id;
2265
2266 OPEN c_limit_id_exists(l_new_limit_id);
2267 FETCH c_limit_id_exists INTO l_dummy;
2268 CLOSE c_limit_id_exists;
2269 EXIT WHEN l_dummy IS NULL;
2270 END LOOP;
2271
2272 INSERT INTO qp_limits
2273 (limit_id
2274 ,creation_date
2275 ,created_by
2276 ,last_update_date
2277 ,last_updated_by
2278 ,last_update_login
2279 ,program_application_id
2280 ,program_id
2281 ,program_update_date
2282 ,request_id
2283 ,list_header_id
2284 ,list_line_id
2285 ,limit_number
2286 ,basis
2287 ,organization_flag
2288 ,limit_level_code
2289 ,limit_exceed_action_code
2290 ,limit_hold_flag
2291 ,multival_attr1_type
2292 ,multival_attr1_context
2293 ,multival_attribute1
2294 ,multival_attr1_datatype
2295 ,multival_attr2_type
2296 ,multival_attr2_context
2297 ,multival_attribute2
2298 ,multival_attr2_datatype
2299 ,amount
2300 ,context
2301 ,attribute1
2302 ,attribute2
2303 ,attribute3
2304 ,attribute4
2305 ,attribute5
2306 ,attribute6
2307 ,attribute7
2308 ,attribute8
2309 ,attribute9
2310 ,attribute10
2311 ,attribute11
2312 ,attribute12
2313 ,attribute13
2314 ,attribute14
2315 ,attribute15
2316 ,each_attr_exists
2317 ,non_each_attr_count
2318 ,total_attr_count)
2319 VALUES
2320 (l_new_limit_id
2321 ,SYSDATE
2322 ,FND_GLOBAL.user_id
2323 ,SYSDATE
2324 ,FND_GLOBAL.user_id
2325 ,FND_GLOBAL.conc_login_id
2326 ,l_line_limits.program_application_id
2327 ,l_line_limits.program_id
2328 ,SYSDATE
2329 ,NULL
2330 ,l_offer_rec.qp_list_header_id
2331 ,l_new_limit_line_id
2332 ,l_line_limits.limit_number
2333 ,l_line_limits.basis
2334 ,l_line_limits.organization_flag
2335 ,l_line_limits.limit_level_code
2336 ,l_line_limits.limit_exceed_action_code
2337 ,l_line_limits.limit_hold_flag
2338 ,l_line_limits.multival_attr1_type
2339 ,l_line_limits.multival_attr1_context
2340 ,l_line_limits.multival_attribute1
2341 ,l_line_limits.multival_attr1_datatype
2342 ,l_line_limits.multival_attr2_type
2343 ,l_line_limits.multival_attr2_context
2344 ,l_line_limits.multival_attribute2
2345 ,l_line_limits.multival_attr2_datatype
2346 ,l_line_limits.amount
2347 ,l_line_limits.context
2348 ,l_line_limits.attribute1
2349 ,l_line_limits.attribute2
2350 ,l_line_limits.attribute3
2351 ,l_line_limits.attribute4
2352 ,l_line_limits.attribute5
2353 ,l_line_limits.attribute6
2354 ,l_line_limits.attribute7
2355 ,l_line_limits.attribute8
2356 ,l_line_limits.attribute9
2357 ,l_line_limits.attribute10
2358 ,l_line_limits.attribute11
2359 ,l_line_limits.attribute12
2360 ,l_line_limits.attribute13
2361 ,l_line_limits.attribute14
2362 ,l_line_limits.attribute15
2363 ,l_line_limits.each_attr_exists
2364 ,l_line_limits.non_each_attr_count
2365 ,l_line_limits.total_attr_count);
2366 EXIT;
2367 END IF;
2368 END LOOP;
2369 END IF;
2370 l_prev_line_id := l_line_limits.list_line_id;
2371 END LOOP;
2372 -- end processing limits
2373
2374 IF l_offer_rec.offer_type = 'DEAL' THEN
2375 -- process related lines for trade deal offers
2376 FOR l_related_deal_line IN c_related_deal_lines LOOP
2377 l_new_modifier_id := NULL;
2378 l_new_rltd_modifier_id := NULL;
2379
2380 -- get new source line id
2381 FOR i IN 1..l_line_mapping_tbl.count LOOP
2382 IF l_related_deal_line.modifier_id = l_line_mapping_tbl(i).org_line_id THEN
2383 l_new_modifier_id := l_line_mapping_tbl(i).new_line_id;
2384 EXIT;
2385 END IF;
2386 END LOOP;
2387
2388 -- remove assigned source line id from mapping table
2389 FOR i IN 1..l_line_mapping_tbl.count LOOP
2390 IF l_new_modifier_id = l_line_mapping_tbl(i).new_line_id THEN
2391 l_line_mapping_tbl(i) := NULL;
2392 END IF;
2393 END LOOP;
2394
2395 IF l_related_deal_line.related_modifier_id IS NOT NULL
2396 AND l_related_deal_line.related_modifier_id <> FND_API.G_MISS_NUM
2397 THEN
2398 -- get new rltd line id
2399 FOR i IN 1..l_line_mapping_tbl.count LOOP
2400 IF l_related_deal_line.related_modifier_id = l_line_mapping_tbl(i).org_line_id THEN
2401 l_new_rltd_modifier_id := l_line_mapping_tbl(i).new_line_id;
2402 EXIT;
2403 END IF;
2404 END LOOP;
2405
2406 -- remove assigned rltd line id from mapping table
2407 FOR i IN 1..l_line_mapping_tbl.count LOOP
2408 IF l_new_rltd_modifier_id = l_line_mapping_tbl(i).new_line_id THEN
2409 l_line_mapping_tbl(i) := NULL;
2410 END IF;
2411 END LOOP;
2412 ELSE
2413 l_new_rltd_modifier_id := NULL;
2414 END IF;
2415
2416 -- create related line info in ozf_related_deal_lines
2417 l_related_lines_rec.modifier_id := l_new_modifier_id;
2418 l_related_lines_rec.related_modifier_id := l_new_rltd_modifier_id;
2419 l_related_lines_rec.object_version_number := 1;
2420 l_related_lines_rec.estimated_qty_is_max := l_related_deal_line.estimated_qty_is_max;
2421 l_related_lines_rec.estimated_amount_is_max := l_related_deal_line.estimated_amount_is_max;
2422 l_related_lines_rec.estimated_qty := l_related_deal_line.estimated_qty;
2423 l_related_lines_rec.estimated_amount := l_related_deal_line.estimated_amount;
2424 l_related_lines_rec.estimate_qty_uom := l_related_deal_line.estimate_qty_uom;
2425 l_related_lines_rec.qp_list_header_id := l_offer_rec.qp_list_header_id;
2426
2427 ozf_Related_Lines_PVT.Create_related_lines
2428 (p_api_version_number => 1.0
2429 ,x_return_status => x_return_Status
2430 ,x_msg_count => x_msg_count
2431 ,x_msg_data => x_msg_data
2432 ,p_related_lines_rec => l_related_lines_rec
2433 ,x_related_deal_lines_id => l_related_deal_lines_id);
2434 END LOOP;
2435 -- end processing related lines for trade deal
2436 --END IF;
2437
2438 ELSIF l_offer_rec.offer_type IN ('LUMPSUM', 'SCAN_DATA') THEN
2439 -- copy lines for lumpsum and scan data
2440 FOR l_act_product IN c_act_products LOOP
2441 l_act_product_rec.act_product_used_by_id := l_offer_rec.qp_list_header_id;
2442 l_act_product_rec.arc_act_product_used_by := 'OFFR';
2443 l_act_product_rec.product_sale_type := l_act_product.product_sale_type;
2444 l_act_product_rec.primary_product_flag := l_act_product.primary_product_flag;
2445 l_act_product_rec.enabled_flag := l_act_product.enabled_flag;
2446 l_act_product_rec.excluded_flag := l_act_product.excluded_flag;
2447 l_act_product_rec.category_id := l_act_product.category_id;
2448 l_act_product_rec.category_set_id := l_act_product.category_set_id;
2449 l_act_product_rec.organization_id := l_act_product.organization_id;
2450 l_act_product_rec.inventory_item_id := l_act_product.inventory_item_id;
2451 l_act_product_rec.level_type_code := l_act_product.level_type_code;
2452 l_act_product_rec.line_lumpsum_amount := l_act_product.line_lumpsum_amount;
2453 l_act_product_rec.line_lumpsum_qty := l_act_product.line_lumpsum_qty;
2454 l_act_product_rec.attribute_category := l_act_product.attribute_category;
2455 l_act_product_rec.attribute1 := l_act_product.attribute1;
2456 l_act_product_rec.attribute2 := l_act_product.attribute2;
2457 l_act_product_rec.attribute3 := l_act_product.attribute3;
2458 l_act_product_rec.attribute4 := l_act_product.attribute4;
2459 l_act_product_rec.attribute5 := l_act_product.attribute5;
2460 l_act_product_rec.attribute6 := l_act_product.attribute6;
2464 l_act_product_rec.attribute10 := l_act_product.attribute10;
2461 l_act_product_rec.attribute7 := l_act_product.attribute7;
2462 l_act_product_rec.attribute8 := l_act_product.attribute8;
2463 l_act_product_rec.attribute9 := l_act_product.attribute9;
2465 l_act_product_rec.attribute11 := l_act_product.attribute11;
2466 l_act_product_rec.attribute12 := l_act_product.attribute12;
2467 l_act_product_rec.attribute13 := l_act_product.attribute13;
2468 l_act_product_rec.attribute14 := l_act_product.attribute14;
2469 l_act_product_rec.attribute15 := l_act_product.attribute15;
2470 l_act_product_rec.channel_id := l_act_product.channel_id;
2471 l_act_product_rec.uom_code := l_act_product.uom_code;
2472 l_act_product_rec.quantity := l_act_product.quantity;
2473 l_act_product_rec.scan_value := l_act_product.scan_value;
2474 l_act_product_rec.scan_unit_forecast := l_act_product.scan_unit_forecast;
2475 l_act_product_rec.adjustment_flag := l_act_product.adjustment_flag;
2476
2477 ams_ActProduct_PVT.Create_Act_Product(p_api_version => p_api_version
2478 ,p_init_msg_list => p_init_msg_list
2479 ,p_commit => p_commit
2480 ,p_validation_level => p_validation_level
2481 ,x_return_status => x_return_status
2482 ,x_msg_count => x_msg_count
2483 ,x_msg_data => x_msg_data
2484 ,p_act_Product_rec => l_act_product_rec
2485 ,x_act_Product_id => l_act_product_id);
2486
2487 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2488 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2489 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
2490 RAISE FND_API.G_EXC_ERROR;
2491 END IF;
2492
2493 l_temp_id := l_act_product_id;
2494
2495 -- copy exclusion
2496 FOR l_excluded_product IN c_excluded_products(l_act_product.activity_product_id) LOOP
2497 l_act_product_rec := NULL;
2498
2499 l_act_product_rec.act_product_used_by_id := l_temp_id;
2500 l_act_product_rec.arc_act_product_used_by := 'PROD';
2501 l_act_product_rec.product_sale_type := l_excluded_product.product_sale_type;
2502 l_act_product_rec.primary_product_flag := l_excluded_product.primary_product_flag;
2503 l_act_product_rec.enabled_flag := l_excluded_product.enabled_flag;
2504 l_act_product_rec.excluded_flag := l_excluded_product.excluded_flag;
2505 l_act_product_rec.category_id := l_excluded_product.category_id;
2506 l_act_product_rec.category_set_id := l_excluded_product.category_set_id;
2507 l_act_product_rec.organization_id := l_excluded_product.organization_id;
2508 l_act_product_rec.inventory_item_id := l_excluded_product.inventory_item_id;
2509 l_act_product_rec.level_type_code := l_excluded_product.level_type_code;
2510 l_act_product_rec.line_lumpsum_amount := l_excluded_product.line_lumpsum_amount;
2511 l_act_product_rec.line_lumpsum_qty := l_excluded_product.line_lumpsum_qty;
2512 l_act_product_rec.attribute_category := l_excluded_product.attribute_category;
2513 l_act_product_rec.attribute1 := l_excluded_product.attribute1;
2514 l_act_product_rec.attribute2 := l_excluded_product.attribute2;
2515 l_act_product_rec.attribute3 := l_excluded_product.attribute3;
2516 l_act_product_rec.attribute4 := l_excluded_product.attribute4;
2517 l_act_product_rec.attribute5 := l_excluded_product.attribute5;
2518 l_act_product_rec.attribute6 := l_excluded_product.attribute6;
2519 l_act_product_rec.attribute7 := l_excluded_product.attribute7;
2520 l_act_product_rec.attribute8 := l_excluded_product.attribute8;
2521 l_act_product_rec.attribute9 := l_excluded_product.attribute9;
2522 l_act_product_rec.attribute10 := l_excluded_product.attribute10;
2523 l_act_product_rec.attribute11 := l_excluded_product.attribute11;
2524 l_act_product_rec.attribute12 := l_excluded_product.attribute12;
2525 l_act_product_rec.attribute13 := l_excluded_product.attribute13;
2526 l_act_product_rec.attribute14 := l_excluded_product.attribute14;
2527 l_act_product_rec.attribute15 := l_excluded_product.attribute15;
2528 l_act_product_rec.channel_id := l_excluded_product.channel_id;
2529 l_act_product_rec.uom_code := l_excluded_product.uom_code;
2530 l_act_product_rec.quantity := l_excluded_product.quantity;
2531 l_act_product_rec.scan_value := l_excluded_product.scan_value;
2532 l_act_product_rec.scan_unit_forecast := l_excluded_product.scan_unit_forecast;
2533 l_act_product_rec.adjustment_flag := l_excluded_product.adjustment_flag;
2534 ams_ActProduct_PVT.Create_Act_Product(p_api_version => p_api_version
2535 ,p_init_msg_list => p_init_msg_list
2536 ,p_commit => p_commit
2537 ,p_validation_level => p_validation_level
2538 ,x_return_status => x_return_status
2539 ,x_msg_count => x_msg_count
2540 ,x_msg_data => x_msg_data
2541 ,p_act_Product_rec => l_act_product_rec
2542 ,x_act_Product_id => l_act_product_id);
2543
2544 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2548 END IF;
2545 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2546 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
2547 RAISE FND_API.G_EXC_ERROR;
2549 END LOOP;
2550 -- end copying exclusion
2551 END LOOP;
2552 -- end copying lines for lumpsum and scan data
2553 END IF;
2554
2555 ELSIF l_offer_rec.offer_type = 'VOLUME_OFFER' THEN
2556 -- if volume offer, copy tier info
2557 -- copy list header
2558 DECLARE
2559 l_listHeaderId NUMBER;
2560 BEGIN
2561 copy_vo_header
2562 (
2563 p_api_version => 1.0
2564 , p_init_msg_list => FND_API.G_FALSE
2565 , p_commit => FND_API.G_FALSE
2566 , p_validation_level => p_validation_level
2567 , x_return_status => x_return_status
2568 , x_msg_count => x_msg_count
2569 , x_msg_data => x_msg_data
2570 , p_listHeaderId => p_source_object_id
2571 , x_OfferId => l_offer_id
2572 , x_listHeaderId => l_listHeaderId
2573 , p_copy_columns_table => p_copy_columns_table
2574 , p_attributes_table => p_attributes_table
2575 , p_custom_setup_id => p_custom_setup_id
2576 );
2577 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2578 RAISE FND_API.G_EXC_ERROR;
2579 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2580 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2581 END IF;
2582 l_offer_rec.qp_list_header_id := l_listHeaderId;
2583 END;
2584 END IF;
2585
2586 IF AMS_CpyUtility_PVT.is_copy_attribute ('ATCH', p_attributes_table) = FND_API.G_TRUE THEN
2587 l_errnum := 0;
2588 l_errcode := NULL;
2589 l_errmsg := NULL;
2590 ams_copyelements_pvt.copy_act_attachments( p_src_act_type => 'OZF_OFFR',
2591 p_src_act_id => p_source_object_id,
2592 p_new_act_id => l_offer_rec.qp_list_header_id,
2593 p_errnum => l_errnum,
2594 p_errcode => l_errcode,
2595 p_errmsg => l_errmsg);
2596 END IF;
2597
2598 IF AMS_CpyUtility_PVT.is_copy_attribute ('CPNT', p_attributes_table) = FND_API.G_TRUE THEN
2599 FOR l_contact_point IN c_contact_point LOOP
2600 l_contact_point_rec.contact_point_id := Fnd_Api.g_miss_num;
2601 l_contact_point_rec.last_update_date := SYSDATE;
2602 l_contact_point_rec.last_updated_by := FND_GLOBAL.user_id;
2603 l_contact_point_rec.creation_date := SYSDATE;
2604 l_contact_point_rec.created_by := FND_GLOBAL.user_id;
2605 l_contact_point_rec.last_update_login := FND_GLOBAL.conc_login_id;
2606 l_contact_point_rec.object_version_number := 1;
2607 l_contact_point_rec.arc_contact_used_by := 'OFFR';
2608 l_contact_point_rec.act_contact_used_by_id := l_offer_rec.qp_list_header_id;
2609 l_contact_point_rec.contact_point_type := l_contact_point.contact_point_type;
2610 l_contact_point_rec.contact_point_value := l_contact_point.contact_point_value;
2611 l_contact_point_rec.city := l_contact_point.city;
2612 l_contact_point_rec.country := l_contact_point.country;
2613 l_contact_point_rec.zipcode := l_contact_point.zipcode;
2614 l_contact_point_rec.attribute_category := l_contact_point.attribute_category;
2615 l_contact_point_rec.attribute1 := l_contact_point.attribute1;
2616 l_contact_point_rec.attribute2 := l_contact_point.attribute2;
2617 l_contact_point_rec.attribute3 := l_contact_point.attribute3;
2618 l_contact_point_rec.attribute4 := l_contact_point.attribute4;
2619 l_contact_point_rec.attribute5 := l_contact_point.attribute5;
2620 l_contact_point_rec.attribute6 := l_contact_point.attribute6;
2621 l_contact_point_rec.attribute7 := l_contact_point.attribute7;
2622 l_contact_point_rec.attribute8 := l_contact_point.attribute8;
2623 l_contact_point_rec.attribute9 := l_contact_point.attribute9;
2624 l_contact_point_rec.attribute10 := l_contact_point.attribute10;
2625 l_contact_point_rec.attribute11 := l_contact_point.attribute11;
2626 l_contact_point_rec.attribute12 := l_contact_point.attribute12;
2627 l_contact_point_rec.attribute13 := l_contact_point.attribute13;
2628 l_contact_point_rec.attribute14 := l_contact_point.attribute14;
2629 l_contact_point_rec.attribute15 := l_contact_point.attribute15;
2630
2631 ams_cnt_point_pvt.create_cnt_point(p_api_version_number => p_api_version
2632 ,p_init_msg_list => p_init_msg_list
2633 ,p_commit => p_commit
2634 ,p_validation_level => p_validation_level
2635 ,x_return_status => x_return_status
2636 ,x_msg_count => x_msg_count
2637 ,x_msg_data => x_msg_data
2638 ,p_cnt_point_rec => l_contact_point_rec
2639 ,x_contact_point_id => l_dummy
2640 );
2641
2645 RAISE FND_API.G_EXC_ERROR;
2642 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2643 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2644 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
2646 END IF;
2647 END LOOP;
2648 END IF;
2649 /* ELIG is copied by QP API
2650 IF AMS_CpyUtility_PVT.is_copy_attribute ('ELIG', p_attributes_table) = FND_API.G_TRUE THEN
2651 FOR l_market_elig_rec IN c_market_elig LOOP
2652 l_index2 := l_index2 + 1;
2653 l_qualifier_tbl(l_index2).qualifier_context := l_market_elig_rec.qualifier_context;
2654 l_qualifier_tbl(l_index2).qualifier_attribute := l_market_elig_rec.qualifier_attribute;
2655 l_qualifier_tbl(l_index2).qualifier_attr_value := l_market_elig_rec.qualifier_attr_value;
2656 l_qualifier_tbl(l_index2).qualifier_attr_value_to := l_market_elig_rec.qualifier_attr_value_to;
2657 l_qualifier_tbl(l_index2).comparison_operator_code := l_market_elig_rec.comparison_operator_code;
2658 l_qualifier_tbl(l_index2).qualifier_grouping_no := l_market_elig_rec.qualifier_grouping_no;
2659 l_qualifier_tbl(l_index2).list_line_id := l_market_elig_rec.list_line_id;
2660 l_qualifier_tbl(l_index2).list_header_id := l_offer_rec.qp_list_header_id;
2661 l_qualifier_tbl(l_index2).start_date_active := l_market_elig_rec.start_date_active;
2662 l_qualifier_tbl(l_index2).end_date_active := l_market_elig_rec.end_date_active;
2663 l_qualifier_tbl(l_index2).operation := 'CREATE';
2664 END LOOP;
2665
2666 IF l_index2 > 0 THEN -- if no market elig l_index2=0
2667 ozf_offer_pvt.process_market_qualifiers(p_init_msg_list => p_init_msg_list
2668 ,p_api_version => p_api_version
2669 ,p_commit => p_commit
2670 ,x_return_status => x_return_status
2671 ,x_msg_count => x_msg_count
2672 ,x_msg_data => x_msg_data
2673 ,p_qualifiers_tbl => l_qualifier_tbl
2674 ,x_error_location => l_error_location);
2675 END IF;
2676
2677 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2678 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2679 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
2680 RAISE FND_API.G_EXC_ERROR;
2681 END IF;
2682 END IF;
2683 */
2684 IF AMS_CpyUtility_PVT.is_copy_attribute ('ELIG', p_attributes_table) = FND_API.G_FALSE THEN
2685 IF l_offer_rec.offer_type IN ('ACCRUAL', 'OFF_INVOICE', 'TERMS', 'ORDER', 'OID') THEN
2686 DELETE FROM qp_qualifiers
2687 WHERE list_header_id = l_offer_rec.qp_list_header_id
2688 AND list_line_id = -1;
2689 END IF;
2690 ELSE
2691 IF l_offer_rec.offer_type = 'NET_ACCRUAL' THEN
2692 copy_na_market_elig(p_source_object_id => p_source_object_id
2693 ,p_new_offer_id => l_offer_rec.offer_id);
2694 END IF;
2695 END IF;
2696
2697 IF AMS_CpyUtility_PVT.is_copy_attribute ('CONTENT', p_attributes_table) = FND_API.G_TRUE THEN
2698 l_errnum := 0;
2699 l_errcode := NULL;
2700 l_errmsg := NULL;
2701 ams_copyelements_pvt.copy_act_content(p_src_act_type => 'OZF_OFFR',
2702 p_src_act_id => p_source_object_id,
2703 p_new_act_id => l_offer_rec.qp_list_header_id,
2704 p_errnum => l_errnum,
2705 p_errcode => l_errcode,
2706 p_errmsg => l_errmsg);
2707 END IF;
2708
2709 IF AMS_CpyUtility_PVT.is_copy_attribute ('TEAM', p_attributes_table) = FND_API.G_TRUE THEN
2710 l_errnum := 0;
2711 l_errcode := NULL;
2712 l_errmsg := NULL;
2713 ams_copyelements_pvt.copy_act_access(p_src_act_type => 'OFFR',
2714 p_new_act_type => 'OFFR',
2715 p_src_act_id => p_source_object_id,
2716 p_new_act_id => l_offer_rec.qp_list_header_id,
2717 p_errnum => l_errnum,
2718 p_errcode => l_errcode,
2719 p_errmsg => l_errmsg);
2720 END IF;
2721
2722 x_new_object_id := l_offer_rec.qp_list_header_id;
2723
2724 EXCEPTION
2725
2726 WHEN Fnd_Api.G_EXC_ERROR THEN
2727 x_return_status := Fnd_Api.g_ret_sts_error;
2728 ROLLBACK TO copy_offer_detail;
2729 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_ERROR )
2730 THEN
2731 Fnd_Msg_Pub.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2732 END IF;
2733 Fnd_Msg_Pub.Count_AND_Get
2734 ( p_count => x_msg_count,
2735 p_data => x_msg_data,
2736 p_encoded => Fnd_Api.G_FALSE
2737 );
2738
2739 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
2740 x_return_status := Fnd_Api.g_ret_sts_unexp_error;
2741 ROLLBACK TO copy_offer_detail;
2742 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR )
2743 THEN
2744 Fnd_Msg_Pub.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2745 END IF;
2746 Fnd_Msg_Pub.Count_AND_Get
2747 ( p_count => x_msg_count,
2748 p_data => x_msg_data,
2749 p_encoded => Fnd_Api.G_FALSE
2750 );
2751
2752 WHEN OTHERS THEN
2753 x_return_status := Fnd_Api.g_ret_sts_unexp_error;
2754 ROLLBACK TO copy_offer_detail;
2755 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR )
2756 THEN
2757 Fnd_Msg_Pub.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2758 END IF;
2759 Fnd_Msg_Pub.Count_AND_Get
2760 ( p_count => x_msg_count,
2761 p_data => x_msg_data,
2762 p_encoded => Fnd_Api.G_FALSE
2763 );
2764
2765 END copy_offer_detail;
2766
2767 PROCEDURE copy_offer(p_api_version IN NUMBER,
2768 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2769 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2770 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2771 x_return_status OUT NOCOPY VARCHAR2,
2772 x_msg_count OUT NOCOPY NUMBER,
2773 x_msg_data OUT NOCOPY VARCHAR2,
2774 p_source_object_id IN NUMBER,
2775 p_attributes_table IN AMS_CpyUtility_PVT.copy_attributes_table_type,
2776 p_copy_columns_table IN AMS_CpyUtility_PVT.copy_columns_table_type,
2777 x_new_object_id OUT NOCOPY NUMBER,
2778 x_custom_setup_id OUT NOCOPY NUMBER)
2779 IS
2780 CURSOR c_budget_offer_yn IS
2781 SELECT budget_offer_yn, offer_type, custom_setup_id
2782 FROM ozf_offers
2783 WHERE qp_list_header_id = p_source_object_id;
2784
2785 l_budget_offer_yn VARCHAR2(1);
2786 l_offer_type VARCHAR2(30);
2787 l_api_name VARCHAR2(30) := 'copy_offer';
2788 BEGIN
2789 SAVEPOINT copy_offer;
2790 -- Initialize API return status to SUCCESS
2791 x_return_status := FND_API.G_RET_STS_SUCCESS;
2792
2793 OPEN c_budget_offer_yn;
2794 FETCH c_budget_offer_yn INTO l_budget_offer_yn, l_offer_type, x_custom_setup_id;
2795 CLOSE c_budget_offer_yn;
2796
2797 IF l_budget_offer_yn = 'Y' THEN
2798 IF l_offer_type = 'ACCRUAL' THEN
2799 x_custom_setup_id := 91;
2800 ELSIF l_offer_type = 'VOLUME_OFFER' THEN
2801 x_custom_setup_id := 98;
2802 END IF;
2803 END IF;
2804
2805 copy_offer_detail
2806 (p_api_version => p_api_version,
2807 p_init_msg_list => p_init_msg_list,
2808 p_commit => p_commit,
2809 p_validation_level => p_validation_level,
2810 x_return_status => x_return_status,
2811 x_msg_count => x_msg_count,
2812 x_msg_data => x_msg_data,
2813 p_source_object_id => p_source_object_id,
2814 p_attributes_table => p_attributes_table,
2815 p_copy_columns_table => p_copy_columns_table,
2816 x_new_object_id => x_new_object_id,
2817 p_custom_setup_id => x_custom_setup_id);
2818
2819 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2820 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2821 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
2822 RAISE FND_API.G_EXC_ERROR;
2823 END IF;
2824
2825 EXCEPTION
2826 WHEN Fnd_Api.G_EXC_ERROR THEN
2827 x_return_status := Fnd_Api.g_ret_sts_error;
2828 ROLLBACK TO copy_offer;
2829 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_ERROR )
2830 THEN
2831 Fnd_Msg_Pub.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2832 END IF;
2833 Fnd_Msg_Pub.Count_AND_Get
2834 ( p_count => x_msg_count,
2835 p_data => x_msg_data,
2836 p_encoded => Fnd_Api.G_FALSE
2837 );
2838
2839 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
2840 x_return_status := Fnd_Api.g_ret_sts_unexp_error;
2841 ROLLBACK TO copy_offer;
2842 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR )
2843 THEN
2844 Fnd_Msg_Pub.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2845 END IF;
2846 Fnd_Msg_Pub.Count_AND_Get
2847 ( p_count => x_msg_count,
2848 p_data => x_msg_data,
2849 p_encoded => Fnd_Api.G_FALSE
2850 );
2851
2852 WHEN OTHERS THEN
2853 x_return_status := Fnd_Api.g_ret_sts_unexp_error;
2854 ROLLBACK TO copy_offer;
2855 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR )
2856 THEN
2857 Fnd_Msg_Pub.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2858 END IF;
2859 Fnd_Msg_Pub.Count_AND_Get
2860 ( p_count => x_msg_count,
2861 p_data => x_msg_data,
2862 p_encoded => Fnd_Api.G_FALSE
2863 );
2864
2865 END copy_offer;
2866
2867 END OZF_COPY_OFFER_PVT;