[Home] [Help]
PACKAGE BODY: APPS.OZF_RESALE_PUB
Source
1 PACKAGE BODY OZF_RESALE_PUB AS
2 /* $Header: ozfprssb.pls 120.8.12020000.2 2012/07/09 05:52:21 bkunjan ship $ */
3
4 -- Package name : OZF_RESALE_PUB
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OZF_RESALE_PUB';
11 G_FILE_NAME CONSTANT VARCHAR2(30) := 'ozfprssb.pls';
12
13 OZF_DEBUG_HIGH_ON BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_high);
14 OZF_DEBUG_LOW_ON BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low);
15
16 ---------------------------------------------------------------------
17 -- PROCEDURE
18 -- Start_Process_Iface
19 --
20 -- PURPOSE
21 -- This procedure to initiate data process of records in resales table.
22 --
23 -- PARAMETERS
24 --
25 --
26 -- NOTES
27 ---------------------------------------------------------------------
28 PROCEDURE Start_Process_Iface (
29 p_api_version IN NUMBER
30 ,p_init_msg_list IN VARCHAR2
31 ,p_commit IN VARCHAR2
32 ,p_validation_level IN NUMBER
33 ,p_resale_batch_id IN NUMBER
34 ,x_return_status OUT NOCOPY VARCHAR2
35 ,x_msg_data OUT NOCOPY VARCHAR2
36 ,x_msg_count OUT NOCOPY NUMBER
37 )
38 IS
39 l_api_name CONSTANT VARCHAR2(30) := 'Start_Process_Iface';
40 l_api_version CONSTANT NUMBER := 1.0;
41 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
42 --
43 l_return_status VARCHAR2(30);
44 l_msg_data VARCHAR2(2000);
45 l_msg_count NUMBER;
46
47 CURSOR csr_batch_org_id(cv_resale_batch_id IN NUMBER) IS
48 SELECT org_id
49 FROM ozf_resale_batches_all
50 WHERE resale_batch_id = cv_resale_batch_id;
51
52 l_resale_org_id NUMBER;
53
54 BEGIN
55 -- Standard begin of API savepoint
56 SAVEPOINT PROCESS_IFACE_PUB;
57 -- Standard call to check for call compatibility.
58 IF NOT FND_API.Compatible_API_Call (
59 l_api_version,
60 p_api_version,
61 l_api_name,
62 G_PKG_NAME)
63 THEN
64 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
65 END IF;
66
67 --Initialize message list if p_init_msg_list is TRUE.
68 IF FND_API.To_Boolean (p_init_msg_list) THEN
69 FND_MSG_PUB.initialize;
70 END IF;
71
72 -- Debug Message
73 IF OZF_DEBUG_LOW_ON THEN
74 OZF_UTILITY_PVT.debug_message(p_message_text => l_full_name||': start');
75 END IF;
76
77 -- Initialize API return status to sucess
78 x_return_status := FND_API.G_RET_STS_SUCCESS;
79
80 -- R12 MOAC Enhancement (+)
81 OPEN csr_batch_org_id(p_resale_batch_id);
82 FETCH csr_batch_org_id INTO l_resale_org_id;
83 CLOSE csr_batch_org_id;
84
85 IF l_resale_org_id IS NULL THEN
86 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
87 FND_MESSAGE.set_name('OZF', 'OZF_ORG_ID_NOTFOUND');
88 FND_MSG_PUB.add;
89 END IF;
90 RAISE FND_API.g_exc_error;
91 END IF;
92
93 MO_GLOBAL.set_policy_context('S', l_resale_org_id);
94 -- R12 MOAC Enhancement (-)
95
96 BEGIN
97 OZF_RESALE_WF_PVT.Start_Data_Process(
98 p_resale_batch_id => p_resale_batch_id
99 ,p_caller_type => 'UI'
100 );
101 EXCEPTION
102 WHEN OTHERS THEN
103 RAISE FND_API.g_exc_unexpected_error;
104 END;
105
106 IF FND_API.to_boolean(p_commit) THEN
107 COMMIT;
108 END IF;
109
110 -- Debug Message
111 IF OZF_DEBUG_LOW_ON THEN
112 OZF_UTILITY_PVT.debug_message(p_message_text => l_full_name||': end');
113 END IF;
114
115 --Standard call to get message count and if count=1, get the message
116 FND_MSG_PUB.Count_And_Get (
117 p_encoded => FND_API.G_FALSE,
118 p_count => x_msg_count,
119 p_data => x_msg_data
120 );
121
122 EXCEPTION
123 WHEN FND_API.G_EXC_ERROR THEN
124 ROLLBACK TO PROCESS_IFACE_PUB;
125 x_return_status := FND_API.G_RET_STS_ERROR;
126 FND_MSG_PUB.Count_And_Get (
127 p_encoded => FND_API.G_FALSE,
128 p_count => x_msg_count,
129 p_data => x_msg_data
130 );
131 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
132 ROLLBACK TO PROCESS_IFACE_PUB;
133 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
134 FND_MSG_PUB.Count_And_Get (
135 p_encoded => FND_API.G_FALSE,
136 p_count => x_msg_count,
137 p_data => x_msg_data
138 );
139 WHEN OTHERS THEN
140 ROLLBACK TO PROCESS_IFACE_PUB;
141 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
142 FND_MSG_PUB.Count_And_Get (
143 p_encoded => FND_API.G_FALSE,
144 p_count => x_msg_count,
145 p_data => x_msg_data
146 );
147
148 END Start_Process_Iface;
149
150
151 ---------------------------------------------------------------------
152 -- PROCEDURE
153 -- Start_Payment
154 --
155 -- PURPOSE
156 -- This procedure to initiate batch payment
157 --
158 -- PARAMETERS
159 --
160 --
161 -- NOTES
162 ---------------------------------------------------------------------
163 PROCEDURE Start_Payment (
164 p_api_version IN NUMBER
165 ,p_init_msg_list IN VARCHAR2
166 ,p_commit IN VARCHAR2
167 ,p_validation_level IN NUMBER
168 ,p_resale_batch_id IN NUMBER
169 ,x_return_status OUT NOCOPY VARCHAR2
170 ,x_msg_data OUT NOCOPY VARCHAR2
171 ,x_msg_count OUT NOCOPY NUMBER
172 )
173 IS
174 l_api_name CONSTANT VARCHAR2(30) := 'Start_Payment';
175 l_api_version CONSTANT NUMBER := 1.0;
176 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
177 --
178 l_return_status VARCHAR2(30);
179 l_msg_data VARCHAR2(2000);
180 l_msg_count NUMBER;
181
182 CURSOR csr_batch_org_id(cv_resale_batch_id IN NUMBER) IS
183 SELECT org_id
184 FROM ozf_resale_batches_all
185 WHERE resale_batch_id = cv_resale_batch_id;
186
187 l_resale_org_id NUMBER;
188
189 BEGIN
190 -- Standard begin of API savepoint
191 SAVEPOINT BATCH_PAYMENT_PUB;
192
193 -- Standard call to check for call compatibility.
194 IF NOT FND_API.Compatible_API_Call (
195 l_api_version,
196 p_api_version,
197 l_api_name,
198 G_PKG_NAME)
199 THEN
200 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
201 END IF;
202
203 --Initialize message list if p_init_msg_list is TRUE.
204 IF FND_API.To_Boolean (p_init_msg_list) THEN
205 FND_MSG_PUB.initialize;
206 END IF;
207
208 -- Debug Message
209 IF OZF_DEBUG_LOW_ON THEN
210 OZF_UTILITY_PVT.debug_message(p_message_text => l_full_name||': start');
211 END IF;
212
213 -- Initialize API return status to sucess
214 x_return_status := FND_API.G_RET_STS_SUCCESS;
215
216 -- R12 MOAC Enhancement (+)
217 OPEN csr_batch_org_id(p_resale_batch_id);
218 FETCH csr_batch_org_id INTO l_resale_org_id;
219 CLOSE csr_batch_org_id;
220
221 IF l_resale_org_id IS NULL THEN
222 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
223 FND_MESSAGE.set_name('OZF', 'OZF_ORG_ID_NOTFOUND');
224 FND_MSG_PUB.add;
225 END IF;
226 RAISE FND_API.g_exc_error;
227 END IF;
228
229 MO_GLOBAL.set_policy_context('S', l_resale_org_id);
230 -- R12 MOAC Enhancement (-)
231
232 BEGIN
233 OZF_RESALE_WF_PVT.Start_Batch_Payment(
234 p_resale_batch_id => p_resale_batch_id
235 ,p_caller_type => 'UI'
236 );
237 EXCEPTION
238 WHEN OTHERS THEN
239 RAISE FND_API.g_exc_unexpected_error;
240 END;
241
242 IF FND_API.to_boolean(p_commit) THEN
243 COMMIT;
244 END IF;
245
246
247 -- Debug Message
248 IF OZF_DEBUG_LOW_ON THEN
249 OZF_UTILITY_PVT.debug_message(p_message_text => l_full_name||': end');
250 END IF;
251
252 --Standard call to get message count and if count=1, get the message
253 FND_MSG_PUB.Count_And_Get (
254 p_encoded => FND_API.G_FALSE,
255 p_count => x_msg_count,
256 p_data => x_msg_data
257 );
258
259 EXCEPTION
260 WHEN FND_API.G_EXC_ERROR THEN
261 ROLLBACK TO BATCH_PAYMENT_PUB;
262 x_return_status := FND_API.G_RET_STS_ERROR;
263 FND_MSG_PUB.Count_And_Get (
264 p_encoded => FND_API.G_FALSE,
265 p_count => x_msg_count,
266 p_data => x_msg_data
267 );
268 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
269 ROLLBACK TO BATCH_PAYMENT_PUB;
270 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
271 FND_MSG_PUB.Count_And_Get (
272 p_encoded => FND_API.G_FALSE,
273 p_count => x_msg_count,
274 p_data => x_msg_data
275 );
276 WHEN OTHERS THEN
277 ROLLBACK TO BATCH_PAYMENT_PUB;
278 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
279 FND_MSG_PUB.Count_And_Get (
280 p_encoded => FND_API.G_FALSE,
281 p_count => x_msg_count,
282 p_data => x_msg_data
283 );
284 END Start_Payment;
285
286
287
288 ---------------------------------------------------------------------
289 -- PROCEDURE
290 -- Start_Purge
291 --
292 -- PURPOSE
293 -- Purge the successfully processed records
294 --
295 -- PARAMETERS
296 --
297 -- NOTES
298 --
299 ---------------------------------------------------------------------
300 PROCEDURE Start_Purge
301 (
302 p_api_version IN NUMBER
303 ,p_init_msg_list IN VARCHAR2
304 ,p_commit IN VARCHAR2
305 ,p_validation_level IN NUMBER
306 ,p_data_source_code IN VARCHAR2
307 ,x_return_status OUT NOCOPY VARCHAR2
308 ,x_msg_data OUT NOCOPY VARCHAR2
309 ,x_msg_count OUT NOCOPY NUMBER
310 )IS
311 l_api_name CONSTANT VARCHAR2(30) := 'Start_Purge';
312 l_api_version CONSTANT NUMBER := 1.0;
313 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
314 --
315 l_return_status varchar2(30);
316 l_msg_data varchar2(2000);
317 l_msg_count number;
318
319 BEGIN
320 -- Standard begin of API savepoint
321 SAVEPOINT RESALE_PURGE_PUB;
322 -- Standard call to check for call compatibility.
323 IF NOT FND_API.Compatible_API_Call (
324 l_api_version,
325 p_api_version,
326 l_api_name,
327 G_PKG_NAME)
328 THEN
329 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
330 END IF;
331
332 --Initialize message list if p_init_msg_list is TRUE.
333 IF FND_API.To_Boolean (p_init_msg_list) THEN
334 FND_MSG_PUB.initialize;
335 END IF;
336
337 -- Debug Message
338 IF OZF_DEBUG_LOW_ON THEN
339 OZF_UTILITY_PVT.debug_message(p_message_text => l_full_name||': start');
340 END IF;
341
342 -- Initialize API return status to sucess
343 x_return_status := FND_API.G_RET_STS_SUCCESS;
344
345 OZF_RESALE_PVT.Purge(
346 p_api_version => 1.0
347 ,p_init_msg_list => p_init_msg_list
348 ,p_commit => FND_API.g_false
349 ,p_validation_level => p_validation_level
350 ,p_data_source_code => p_data_source_code
351 ,x_return_status => l_return_status
352 ,x_msg_data => l_msg_data
353 ,x_msg_count => l_msg_count
354 );
355 IF l_return_status = FND_API.g_ret_sts_error THEN
356 RAISE FND_API.g_exc_error;
357 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
358 RAISE FND_API.g_exc_unexpected_error;
359 END IF;
360
361 IF FND_API.to_boolean(p_commit) THEN
362 COMMIT;
363 END IF;
364
365 -- Debug Message
366 IF OZF_DEBUG_LOW_ON THEN
367 OZF_UTILITY_PVT.debug_message(p_message_text => l_full_name||': End');
368 END IF;
369 --Standard call to get message count and if count=1, get the message
370 FND_MSG_PUB.Count_And_Get (
371 p_encoded => FND_API.G_FALSE,
372 p_count => x_msg_count,
373 p_data => x_msg_data
374 );
375
376 EXCEPTION
377 WHEN FND_API.G_EXC_ERROR THEN
378 ROLLBACK TO RESALE_PURGE_PUB ;
379 x_return_status := FND_API.G_RET_STS_ERROR;
380 FND_MSG_PUB.Count_And_Get (
381 p_encoded => FND_API.G_FALSE,
382 p_count => x_msg_count,
383 p_data => x_msg_data
384 );
385 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
386 ROLLBACK TO RESALE_PURGE_PUB;
387 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
388 FND_MSG_PUB.Count_And_Get (
389 p_encoded => FND_API.G_FALSE,
390 p_count => x_msg_count,
391 p_data => x_msg_data
392 );
393 WHEN OTHERS THEN
394 ROLLBACK TO RESALE_PURGE_PUB;
395 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
396 FND_MSG_PUB.Count_And_Get (
397 p_encoded => FND_API.G_FALSE,
398 p_count => x_msg_count,
399 p_data => x_msg_data
400 );
401
402 END Start_Purge;
403 ---------------------------------------------------------------------
404 -- PROCDURE
405 -- Chk_accrual_exists
406 --
407 -- PURPOSE
408 -- Procedure to check TP Accrual is already created or not
409 --
410 ---------------------------------------------------------------------
411
412 PROCEDURE Chk_accrual_exists(
413 p_list_header_id IN NUMBER,
414 p_list_line_id IN NUMBER,
415 p_line_id IN NUMBER,
416 x_result OUT NOCOPY BOOLEAN,
417 x_return_status OUT NOCOPY VARCHAR2
418 )
419 IS
420 l_result VARCHAR2(2) := NULL;
421
422 CURSOR line_adjustment_csr IS
423 SELECT 1
424 FROM ozf_resale_adjustments
425 WHERE resale_line_id = p_line_id
426 AND list_header_id = p_list_header_id
427 AND list_line_id IN
428 ( (SELECT from_list_line_id
429 FROM ozf_offer_adj_rltd_lines
430 START WITH to_list_line_id = p_list_line_id
431 CONNECT BY PRIOR from_list_line_id = to_list_line_id), p_list_line_id );
432
433 BEGIN
434 -- Initialize API return status to sucess
435 x_return_status := FND_API.G_RET_STS_SUCCESS;
436
437 l_result := null;
438
439 OPEN line_adjustment_csr;
440 FETCH line_adjustment_csr INTO l_result;
441 CLOSE line_adjustment_csr;
442
443 IF l_result IS NOT NULL THEN
444 x_result:= FALSE;
445 END IF;
446
447 EXCEPTION
448 WHEN FND_API.G_EXC_ERROR THEN
449 x_return_status := FND_API.G_RET_STS_ERROR;
450 WHEN OTHERS THEN
451 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
452 ozf_utility_pvt.error_message('OZF_RESALE_IS_TM_OFFER');
453 END Chk_accrual_exists;
454
455 ---------------------------------------------------------------------
456 -- PROCEDURE
457 -- Create_resale_utilization
458 --
459 -- PURPOSE
460 -- Public API to Create utilizations for Third party accruals.
461 --
462 -- PARAMETERS
463 -- l_resale_util_rec : Resale utilization record
464 -- x_utilization_id : Returns Utilization Identifier
465 --
466 -- NOTES
467 --
468 -- HISTORY
469 -- 10/29/2011 BKUNJAN Created for ER 12985686
470 ---------------------------------------------------------------------
471
472 PROCEDURE Create_resale_utilization (
473 p_api_version IN NUMBER
474 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
475 , p_commit IN VARCHAR2 := FND_API.G_FALSE
476 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
477 , p_resale_util_rec IN resale_util_rec_type
478 , x_return_status OUT NOCOPY VARCHAR2
479 , x_msg_data OUT NOCOPY VARCHAR2
480 , x_msg_count OUT NOCOPY NUMBER
481 , x_utilization_id OUT NOCOPY NUMBER
482 )
483 IS
484 l_api_name CONSTANT VARCHAR2(30) := 'Create_resale_utilization';
485 l_api_version_number CONSTANT NUMBER := 1.0;
486 l_return_status VARCHAR2(1);
487 l_msg_data VARCHAR2(2000);
488 l_msg_count NUMBER;
489
490 l_adjustment_rec OZF_RESALE_ADJUSTMENTS_ALL%rowtype;
491 l_act_budgets_rec OZF_ACTBUDGETS_PVT.act_budgets_rec_type;
492 l_act_util_rec OZF_ACTBUDGETS_PVT.act_util_rec_type;
493 l_sales_transaction_rec OZF_SALES_TRANSACTIONS_PVT.sales_transaction_rec_type;
494 l_resale_util_rec resale_util_rec_type := p_resale_util_rec;
495 l_offer_type VARCHAR2(30);
496 l_custom_setup_id NUMBER;
497 l_offer_name VARCHAR2(2000); --//Fix for Bug 13687127
498 l_resale_batch_id NUMBER;
499 l_list_line_id NUMBER;
500 l_arithmetic_operator VARCHAR2(15);
501 l_accrual_on_selling VARCHAR2(1);
502 l_requester_id NUMBER;
503 l_ssd_on_idsm VARCHAR2(1);
504 l_sales_transaction_id NUMBER;
505 l_vol_offr_apply_discount NUMBER;
506 l_user_id NUMBER;
507 l_utilization_id NUMBER;
508 l_offer_id NUMBER;
509 l_beneficiary_account_id NUMBER;
510 l_result NUMBER;
511 l_batch_currency_code VARCHAR2(15);
512 l_batch_org_id NUMBER;
513 l_offer_currency_code VARCHAR2(15);
514 l_qp_org_id NUMBER;
515 l_status_code VARCHAR2(30);
516
517 CURSOR c_resale_batch_id (cv_resale_batch_id IN NUMBER)IS
518 SELECT resale_batch_id
519 ,currency_code
520 ,org_id
521 FROM ozf_resale_batches_all
522 WHERE resale_batch_id = cv_resale_batch_id
523 AND status_code = 'CLOSED';
524
525 CURSOR c_get_resale_lines (cv_resale_batch_id IN NUMBER
526 ,cv_resale_line_id IN NUMBER) IS
527 SELECT orl.resale_line_id
528 ,orl.resale_header_id
529 ,orl.bill_to_cust_account_id
530 ,orl.bill_to_party_id
531 ,orl.bill_to_party_site_id
532 ,orl.bill_to_site_use_id
533 ,orl.ship_to_site_use_id
534 ,orl.sold_from_cust_account_id
535 ,orl.inventory_item_Id
536 ,orl.quantity
537 ,orl.uom_code
538 ,orl.currency_code
539 ,orl.selling_price
540 ,orl.date_ordered
541 ,orl.date_shipped
542 ,orl.date_invoiced
543 ,orl.product_transfer_movement_type
544 ,orl.last_updated_by
545 ,orl.org_id
546 ,orl.supplier_item_cost
547 FROM ozf_resale_lines_all orl,
548 ozf_resale_batches_all orb,
549 ozf_resale_batch_line_maps_all orblm
550 WHERE orblm.resale_batch_id = orb.resale_batch_id
551 AND orblm.resale_line_id = orl.resale_line_id
552 AND orb.resale_batch_id = cv_resale_batch_id
553 AND orl.resale_line_id = cv_resale_line_id;
554
555 l_resale_line_rec c_get_resale_lines%ROWTYPE;
556
557 CURSOR c_get_offer_details(cv_qp_list_header_id NUMBER) IS
558 SELECT off.offer_type,
559 off.custom_setup_id,
560 off.transaction_currency_code,
561 qp.orig_org_id,
562 qp.description,
563 off.status_code
564 FROM ozf_offers off,
565 qp_list_headers_all qp
566 WHERE off.qp_list_header_id = qp.list_header_id
567 AND off.qp_list_header_id = cv_qp_list_header_id;
568
569 CURSOR c_list_line(cv_list_header_id IN NUMBER
570 ,cv_list_line_id IN NUMBER)IS
571 SELECT list_line_id,
572 arithmetic_operator
573 FROM qp_list_lines
574 WHERE list_header_id = cv_list_header_id
575 AND list_line_id = cv_list_line_id;
576
577 CURSOR c_sd_request_header_id(p_list_header_id IN NUMBER) IS
578 SELECT request_header_id
579 FROM ozf_sd_request_headers_all_b
580 WHERE offer_id =p_list_header_id;
581
582 CURSOR c_sd_offer_discount (p_list_line_id IN NUMBER) IS
583 SELECT NVL(qpll.price_by_formula_id, qpll.operand),
584 qpll.arithmetic_operator
585 FROM qp_list_lines qpll
586 WHERE qpll.list_line_id = p_list_line_id;
587
588 CURSOR c_get_user_id(cv_user_id IN NUMBER) IS
589 SELECT user_id
590 FROM fnd_user
591 WHERE user_id = cv_user_id;
592
593
594 CURSOR line_adjustment_csr IS
595 SELECT 1
596 FROM ozf_resale_adjustments
597 WHERE resale_line_id = p_resale_util_rec.resale_line_id
598 AND list_header_id = p_resale_util_rec.list_header_id
599 AND list_line_id IN ((SELECT from_list_line_id
600 FROM ozf_offer_adj_rltd_lines
601 START WITH to_list_line_id = p_resale_util_rec.list_line_id
602 CONNECT BY PRIOR from_list_line_id = to_list_line_id), p_resale_util_rec.list_line_id );
603
604
605 BEGIN
606 -- Standard Start of API savepoint
607 SAVEPOINT Create_resale_utilization;
608 -- Standard call to check for call compatibility.
609 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
610 p_api_version,
611 l_api_name,
612 G_PKG_NAME)
613 THEN
614 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
615 END IF;
616
617 -- Initialize message list if p_init_msg_list is set to TRUE.
618 IF FND_API.to_Boolean( p_init_msg_list ) THEN
619 FND_MSG_PUB.initialize;
620 END IF;
621
622 -- Debug Message
623 IF OZF_DEBUG_HIGH_ON THEN
624 OZF_UTILITY_PVT.debug_message('Public API: ' || l_api_name || ' start');
625 END IF;
626
627 -- Initialize API return status to SUCCESS
628 x_return_status := FND_API.G_RET_STS_SUCCESS;
629
630 --//API Body
631 --=====================================================================================================================
632 --//Validations
633 IF OZF_DEBUG_HIGH_ON THEN
634 OZF_UTILITY_PVT.debug_message('Validation Starts...');
635 END IF;
636
637 --//1.resale_batch_id
638 IF l_resale_util_rec.resale_batch_id IS NULL OR l_resale_util_rec.resale_batch_id = FND_API.g_miss_num THEN
639 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
640 FND_MESSAGE.set_name('OZF', 'OZF_NO_RESALE_BATCH_ID');
641 --//Resale batch Identifier is mandatory.Please provide a valid Resale Batch Identifier.
642 FND_MSG_PUB.add;
643 END IF;
644 x_return_status := fnd_api.g_ret_sts_error;
645 RETURN;
646 ELSE
647 OPEN c_resale_batch_id(l_resale_util_rec.resale_batch_id);
648 FETCH c_resale_batch_id INTO l_resale_batch_id,l_batch_currency_code,l_batch_org_id;
649 CLOSE c_resale_batch_id;
650
651 IF l_resale_batch_id IS NULL THEN
652 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
653 FND_MESSAGE.set_name('OZF', 'OZF_INVALID_RESALE_BATCH_ID');
654 --//Resale batch Identifier provided is Invalid.Please provide a valid Resale Batch Identifier.
655 FND_MSG_PUB.add;
656 END IF;
657 x_return_status := fnd_api.g_ret_sts_error;
658 RETURN;
659 END IF;
660 END IF;
661
662 --//2.resale_line_id
663 IF l_resale_util_rec.resale_line_id IS NULL OR l_resale_util_rec.resale_line_id = FND_API.g_miss_num THEN
664 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
665 FND_MESSAGE.set_name('OZF', 'OZF_NO_RESALE_LINE_ID');
666 --//Resale Line Identifier is mandatory.Please provide a valid Resale Line Identifier.
667 FND_MSG_PUB.add;
668 END IF;
669 x_return_status := fnd_api.g_ret_sts_error;
670 RETURN;
671 ELSE
672 OPEN c_get_resale_lines(l_resale_util_rec.resale_batch_id,l_resale_util_rec.resale_line_id);
673 FETCH c_get_resale_lines INTO l_resale_line_rec;
674
675 IF c_get_resale_lines%NOTFOUND THEN
676 --OZF_UTILITY_PVT.debug_message('INSIDE '||l_resale_line_rec.resale_line_id);
677 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
678 FND_MESSAGE.set_name('OZF', 'OZF_INVALID_RESALE_LINE_ID');
679 --//Resale Line Identifier provided is invalid.Please provide a valid Resale Line Identifier.
680 FND_MSG_PUB.add;
681 END IF;
682 x_return_status := fnd_api.g_ret_sts_error;
683 RETURN;
684 END IF;
685 CLOSE c_get_resale_lines;
686
687 END IF;
688
689 --//3.list_header_id
690 IF l_resale_util_rec.list_header_id IS NULL OR l_resale_util_rec.list_header_id = FND_API.g_miss_num THEN
691 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
692 FND_MESSAGE.set_name('OZF', 'OZF_NO_LIST_HEADER_ID');
693 --//List Header Identifier is mandatory.Please provide a valid List Header Identifier.
694 FND_MSG_PUB.add;
695 END IF;
696 x_return_status := fnd_api.g_ret_sts_error;
697 RETURN;
698 ELSE
699 --//Get offer details
700 OPEN c_get_offer_details(l_resale_util_rec.list_header_id);
701 FETCH c_get_offer_details INTO l_offer_type, l_custom_setup_id,l_offer_currency_code,l_qp_org_id,l_offer_name,l_status_code;
702
703 IF c_get_offer_details%NOTFOUND THEN
704 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
705 FND_MESSAGE.set_name('OZF', 'OZF_INVALID_LIST_HEADER_ID');
706 --//List Header Identifier provided is invalid.Please provide a valid List Header Identifier.
707 FND_MSG_PUB.add;
708 END IF;
709 x_return_status := fnd_api.g_ret_sts_error;
710 RETURN;
711 END IF;
712 CLOSE c_get_offer_details;
713 END IF;
714
715 --//Offer type Validation
716 IF l_offer_type NOT IN ('ACCRUAL','OFF_INVOICE','VOLUME_OFFER','OID','DEAL','ORDER') THEN
717 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
718 FND_MESSAGE.set_name('OZF', 'OZF_OFFR_TYPE_UNSUPPORTED');
719 --//Offer type of the provided offer is not supported to create resale utilization.
720 FND_MSG_PUB.add;
721 END IF;
722 x_return_status := fnd_api.g_ret_sts_error;
723 RETURN;
724 END IF;
725
726 --//Status code validation - Only ACTIVE offers are eligible to create utilization
727 IF l_status_code <> 'ACTIVE' THEN
728 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
729 FND_MESSAGE.set_name('OZF', 'OZF_OFFR_STATUS_UNSUPPORTED');
730 --//Offer should be in Active status to create resale utilization.
731 FND_MSG_PUB.add;
732 END IF;
733 x_return_status := fnd_api.g_ret_sts_error;
734 RETURN;
735 END IF;
736
737
738 --//If Offer currency code is not null, it should match with resale header currency code
739 IF l_offer_currency_code IS NOT NULL THEN
740 IF l_offer_currency_code <> l_batch_currency_code THEN
741 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
742 FND_MESSAGE.set_name('OZF', 'OZF_BATCH_CURR_MISMATCH');
743 --//Offer Currecy Code should be same as Resale batch Currency Code.
744 FND_MSG_PUB.add;
745 END IF;
746 x_return_status := fnd_api.g_ret_sts_error;
747 RETURN;
748 END IF;
749 END IF;
750
751 --//If Offer org_id is not null, it should match with resale header Org_id
752 IF l_qp_org_id IS NOT NULL THEN
753 IF l_qp_org_id <> l_batch_org_id THEN
754 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
755 FND_MESSAGE.set_name('OZF', 'OZF_RESALE_ORG_MISMATCH');
756 --//Offer Organization Identifier should be same as Resale batch Organization Identifier.
757 FND_MSG_PUB.add;
758 END IF;
759 x_return_status := fnd_api.g_ret_sts_error;
760 RETURN;
761 END IF;
762 END IF;
763
764 --//4.list_line_id
765 IF l_resale_util_rec.list_line_id IS NULL OR l_resale_util_rec.list_line_id = FND_API.g_miss_num THEN
766 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
767 FND_MESSAGE.set_name('OZF', 'OZF_NO_LIST_LINE_ID');
768 --//List Line Identifier is mandatory.Please provide a valid List Line Identifier.
769 FND_MSG_PUB.add;
770 END IF;
771 x_return_status := fnd_api.g_ret_sts_error;
772 RETURN;
773 ELSE
774 OPEN c_list_line(l_resale_util_rec.list_header_id,l_resale_util_rec.list_line_id);
775 FETCH c_list_line INTO l_list_line_id,l_arithmetic_operator;
776 CLOSE c_list_line;
777
778 IF l_list_line_id IS NULL THEN
779 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
780 FND_MESSAGE.set_name('OZF', 'OZF_INVALID_LIST_LINE_ID');
781 --//List Line Identifier provided is invalid.Please provide a valid List Line Identifier.
782 FND_MSG_PUB.add;
783 END IF;
784 x_return_status := fnd_api.g_ret_sts_error;
785 RETURN;
786 END IF;
787 END IF;
788
789 --//Check for the Duplicate Accrual
790 OPEN line_adjustment_csr;
791 FETCH line_adjustment_csr INTO l_result;
792 CLOSE line_adjustment_csr;
793
794 IF l_result IS NOT NULL THEN
795 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
796 FND_MESSAGE.set_name('OZF', 'OZF_DUP_RESALE_ACCRUAL');
797 --//Utilization is already created for provided list header and line combination
798 FND_MSG_PUB.add;
799 END IF;
800 x_return_status := fnd_api.g_ret_sts_error;
801 RETURN;
802 END IF;
803
804 --//5.accepted_amount
805 IF l_resale_util_rec.accepted_amount IS NULL OR l_resale_util_rec.accepted_amount = FND_API.g_miss_num THEN
806 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
807 FND_MESSAGE.set_name('OZF', 'OZF_NO_ACCEPTED_AMOUNT');
808 --//Accepted Amount is mandatory.Please provide a valid Accepted Amount.
809 FND_MSG_PUB.add;
810 END IF;
811 x_return_status := fnd_api.g_ret_sts_error;
812 RETURN;
813 END IF;
814
815 --//7.priced_quantity
816 IF l_resale_util_rec.quantity IS NULL OR l_resale_util_rec.quantity = FND_API.g_miss_num THEN
817 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
818 FND_MESSAGE.set_name('OZF', 'OZF_NO_PRICED_QUANTITY');
819 --//Priced Quantity is mandatory.Please provide a valid Priced Quantity.
820 FND_MSG_PUB.add;
821 END IF;
822 x_return_status := fnd_api.g_ret_sts_error;
823 RETURN;
824 END IF;
825
826 --//8. operand
827 IF l_resale_util_rec.discount_value IS NULL OR l_resale_util_rec.discount_value = FND_API.g_miss_num THEN
828 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
829 FND_MESSAGE.set_name('OZF', 'OZF_NO_DISCOUNT_VALUE');
830 --//Discount value is mandatory.Please provide a valid Discount value.
831 FND_MSG_PUB.add;
832 END IF;
833 x_return_status := fnd_api.g_ret_sts_error;
834 RETURN;
835 END IF;
836
837 --//9. operand_calculation_code
838 IF l_resale_util_rec.discount_type IS NULL OR l_resale_util_rec.discount_type = FND_API.g_miss_char THEN
839 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
840 FND_MESSAGE.set_name('OZF', 'OZF_NO_DISCOUNT_TYPE');
841 --//Discount type is mandatory.Please provide a valid Discount type.
842 FND_MSG_PUB.add;
843 END IF;
844 x_return_status := fnd_api.g_ret_sts_error;
845 RETURN;
846 ELSE
847 IF l_resale_util_rec.discount_type <> l_arithmetic_operator THEN
848 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
849 FND_MESSAGE.set_name('OZF', 'OZF_INVALID_OPER_CALC_CODE');
850 --//Operend Calculation Code Should be the same as of offer.
851 FND_MSG_PUB.add;
852 END IF;
853 x_return_status := fnd_api.g_ret_sts_error;
854 RETURN;
855 END IF;
856 END IF;
857
858 --//10.selling_price
859 l_accrual_on_selling := FND_PROFILE.value('OZF_ACC_ON_SELLING_PRICE');
860 --//Profile - OZF: Third Party Accrual on Selling Price
861
862 IF l_accrual_on_selling = 'Y' THEN
863 IF l_resale_line_rec.selling_price IS NULL OR l_resale_line_rec.selling_price = FND_API.g_miss_num THEN
864 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
865 FND_MESSAGE.set_name('OZF', 'OZF_ACC_ON_SELLING_PRICE_ERR');
866 --//Selling price should be available in resale lines when Profile OZF: Third Party Accrual on Selling Price is set to Yes.
867 FND_MSG_PUB.add;
868 END IF;
869 x_return_status := fnd_api.g_ret_sts_error;
870 RETURN;
871 ELSE
872 l_resale_util_rec.unit_price := l_resale_line_rec.selling_price;
873 END IF;
874 ELSE -- l_accrual_on_selling IS N or NULL
875 IF l_resale_util_rec.unit_price IS NULL OR l_resale_util_rec.unit_price = FND_API.g_miss_num THEN
876 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
877 FND_MESSAGE.set_name('OZF', 'OZF_NO_PRICED_UNIT_PRICE');
878 --//Unit Price is mandatory.Please provide a valid Unit Price.
879 FND_MSG_PUB.add;
880 END IF;
881 x_return_status := fnd_api.g_ret_sts_error;
882 RETURN;
883 END IF;
884 END IF;
885
886 --//11.requester_id
887 IF l_resale_util_rec.requester_id IS NULL OR l_resale_util_rec.requester_id = FND_API.g_miss_num THEN
888 l_requester_id := OZF_UTILITY_PVT.get_resource_id(l_resale_line_rec.last_updated_by);
889 IF l_requester_id IS NULL THEN
890 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
891 FND_MESSAGE.set_name('OZF', 'OZF_SD_USER_IS_NOT_RESOURCE');
892 --//User is not a valid resource.
893 FND_MSG_PUB.add;
894 END IF;
895 x_return_status := fnd_api.g_ret_sts_error;
896 RETURN;
897 END IF;
898 ELSE
899 OPEN c_get_user_id(l_resale_util_rec.requester_id);
900 FETCH c_get_user_id INTO l_user_id;
901 CLOSE c_get_user_id;
902
903 IF l_user_id IS NULL THEN
904 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
905 FND_MESSAGE.set_name('OZF', 'OZF_SD_INVALID_USER');
906 --//The User Id provided is invalid, please provide a valid User Id.
907 FND_MSG_PUB.add;
908 END IF;
909 x_return_status := fnd_api.g_ret_sts_error;
910 RETURN;
911 END IF;
912
913 l_requester_id := OZF_UTILITY_PVT.get_resource_id(l_resale_util_rec.requester_id);
914 IF l_requester_id IS NULL THEN
915 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
916 FND_MESSAGE.set_name('OZF', 'OZF_SD_USER_IS_NOT_RESOURCE');
917 --//User is not a valid resource.
918 FND_MSG_PUB.add;
919 END IF;
920 x_return_status := fnd_api.g_ret_sts_error;
921 RETURN;
922 END IF;
923 END IF;
924
925 --//12.currency_code
926 IF l_resale_util_rec.currency_code IS NULL OR l_resale_util_rec.currency_code = FND_API.g_miss_char THEN
927 l_resale_util_rec.currency_code := l_resale_line_rec.currency_code;
928 ELSE
929 IF l_resale_util_rec.currency_code <> l_resale_line_rec.currency_code THEN
930 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
931 FND_MESSAGE.set_name('OZF', 'OZF_CURRENCY_CODE_MISMATCH');
932 --//Provided currency code should be same as Resale batch Currency code.
933 FND_MSG_PUB.add;
934 END IF;
935 x_return_status := fnd_api.g_ret_sts_error;
936 RETURN;
937 END IF;
938 END IF;
939
940 --//13.uom_code
941 IF l_resale_util_rec.uom_code IS NULL OR l_resale_util_rec.uom_code = FND_API.g_miss_char THEN
942 l_resale_util_rec.uom_code := l_resale_line_rec.uom_code;
943 ELSE
944 IF l_resale_util_rec.uom_code <> l_resale_line_rec.uom_code THEN
945 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
946 FND_MESSAGE.set_name('OZF', 'OZF_UOM_CODE_MISMATCH');
947 --//Provided Unit of Measurement Code should be same as Resale batch Unit of Measurement Code.
948 FND_MSG_PUB.add;
949 END IF;
950 x_return_status := fnd_api.g_ret_sts_error;
951 RETURN;
952 END IF;
953 END IF;
954
955
956 IF l_resale_util_rec.inventory_item_Id IS NULL OR l_resale_util_rec.inventory_item_Id = FND_API.g_miss_num THEN
957 l_resale_util_rec.inventory_item_Id := l_resale_line_rec.inventory_item_Id;
958 ELSE
959 IF l_resale_util_rec.inventory_item_Id <> l_resale_line_rec.inventory_item_Id THEN
960 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
961 FND_MESSAGE.set_name('OZF', 'OZF_PRODUCT_CODE_MISMATCH');
962 --//Provided product Code should be same as Resale lines Product code.
963 FND_MSG_PUB.add;
964 END IF;
965 x_return_status := fnd_api.g_ret_sts_error;
966 RETURN;
967 END IF;
968 END IF;
969
970 IF OZF_DEBUG_HIGH_ON THEN
971 OZF_UTILITY_PVT.debug_message('Validation Completed...');
972 OZF_UTILITY_PVT.debug_message('Populate Records');
973 END IF;
974
975 --// Populate l_adjustment_rec
976 l_adjustment_rec.resale_batch_id := l_resale_util_rec.resale_batch_id;
977 l_adjustment_rec.resale_line_id := l_resale_util_rec.resale_line_id;
978 l_adjustment_rec.list_header_id := l_resale_util_rec.list_header_id;
979 l_adjustment_rec.list_line_id := l_resale_util_rec.list_line_id;
980 l_adjustment_rec.accepted_amount := l_resale_util_rec.accepted_amount * -1; --//Fix for Bug 13814170
981 l_adjustment_rec.priced_quantity := l_resale_util_rec.quantity;
982 l_adjustment_rec.operand := l_resale_util_rec.discount_value;
983 l_adjustment_rec.operand_calculation_code := l_resale_util_rec.discount_type;
984 l_adjustment_rec.priced_unit_price := l_resale_util_rec.unit_price;
985 l_adjustment_rec.calculated_price := l_resale_util_rec.unit_price;
986 l_adjustment_rec.status_code := 'CLOSED';
987 l_adjustment_rec.claimed_amount := 0;
988 l_adjustment_rec.total_claimed_amount := 0;
989 l_adjustment_rec.allowed_amount := 0;
990 l_adjustment_rec.total_allowed_amount := 0;
991 l_adjustment_rec.tolerance_flag := 'F';
992 l_adjustment_rec.line_tolerance_amount := 0;
993 l_adjustment_rec.orig_system_agreement_uom := NULL;
994 l_adjustment_rec.orig_system_agreement_name := NULL;
995 l_adjustment_rec.orig_system_agreement_type := NULL;
996 l_adjustment_rec.orig_system_agreement_status := NULL;
997 l_adjustment_rec.orig_system_agreement_curr := NULL;
998 l_adjustment_rec.orig_system_agreement_price := NULL;
999 l_adjustment_rec.orig_system_agreement_quantity := NULL;
1000 l_adjustment_rec.agreement_id := NULL;
1001 l_adjustment_rec.agreement_type := NULL;
1002 l_adjustment_rec.agreement_name := NULL;
1003 l_adjustment_rec.agreement_price := NULL;
1004 l_adjustment_rec.agreement_uom_code := NULL;
1005 l_adjustment_rec.corrected_agreement_id := NULL;
1006 l_adjustment_rec.corrected_agreement_name := NULL;
1007 l_adjustment_rec.credit_code := NULL;
1008 l_adjustment_rec.credit_advice_date := NULL;
1009 l_adjustment_rec.line_agreement_flag := 'F';
1010 l_adjustment_rec.org_id := l_resale_line_rec.org_id;
1011 l_adjustment_rec.total_accepted_amount := (l_resale_util_rec.accepted_amount * l_resale_util_rec.quantity) * - 1; --//Fix for Bug 13814170
1012 l_adjustment_rec.priced_uom_code := l_resale_line_rec.uom_code;
1013 l_adjustment_rec.calculated_amount := l_adjustment_rec.total_accepted_amount; --//Fix for Bug 13814170
1014
1015
1016 MO_GLOBAL.set_policy_context('S', l_adjustment_rec.org_id);
1017 --// Populate l_act_budgets_rec
1018 l_act_budgets_rec.arc_act_budget_used_by := 'OFFR';
1019 l_act_budgets_rec.act_budget_used_by_id := l_resale_util_rec.list_header_id;
1020 l_act_budgets_rec.budget_source_type := 'OFFR';
1021 l_act_budgets_rec.budget_source_id := l_resale_util_rec.list_header_id;
1022 l_act_budgets_rec.status_code := 'APPROVED';
1023 l_act_budgets_rec.transfer_type := 'UTILIZED';
1024 l_act_budgets_rec.approver_id := l_requester_id;
1025 l_act_budgets_rec.requester_id := l_requester_id;
1026 l_act_budgets_rec.request_currency := l_resale_line_rec.currency_code;
1027 l_act_budgets_rec.request_amount := l_adjustment_rec.total_accepted_amount * -1; --//Fix for Bug 13814170
1028 l_act_budgets_rec.exchange_rate_date := l_resale_util_rec.exchange_rate_date;
1029
1030 --// Populate l_act_util_rec
1031 l_act_util_rec.object_type := 'TP_ORDER';
1032 l_act_util_rec.object_id := l_resale_util_rec.resale_line_id;
1033 l_act_util_rec.product_level_type := 'PRODUCT';
1034 l_act_util_rec.bill_to_site_use_id := l_resale_line_rec.bill_to_site_use_id;
1035 l_act_util_rec.ship_to_site_use_id := l_resale_line_rec.ship_to_site_use_id;
1036 l_act_util_rec.product_id := l_resale_util_rec.inventory_item_Id;
1037 l_act_util_rec.billto_cust_account_id := l_resale_line_rec.bill_to_cust_account_id;
1038 l_act_util_rec.gl_date := l_resale_line_rec.date_shipped;
1039 l_act_util_rec.org_id := l_resale_line_rec.org_id;
1040 l_act_util_rec.utilization_type :='UTILIZED' ;
1041 l_act_util_rec.fund_request_currency_code := OZF_ACTBUDGETS_PVT.get_object_currency
1042 ('OFFR'
1043 ,l_resale_util_rec.list_header_id
1044 ,l_return_status);
1045 l_act_util_rec.plan_currency_code := l_act_budgets_rec.request_currency;
1046
1047
1048
1049 --//Supplier Ship and Debit IDSM flow.
1050 IF l_custom_setup_id = 118 THEN
1051 OPEN c_sd_request_header_id(l_resale_util_rec.list_header_id);
1052 FETCH c_sd_request_header_id INTO l_act_util_rec.reference_id;
1053 CLOSE c_sd_request_header_id;
1054
1055 l_act_util_rec.reference_type := 'SD_REQUEST';
1056
1057 --Populate Discount Amount, Discount Type, Discount Currency for SDR Offers
1058 OPEN c_sd_offer_discount(l_resale_util_rec.list_line_id);
1059 FETCH c_sd_offer_discount INTO l_act_util_rec.discount_amount, l_act_util_rec.discount_type;
1060 CLOSE c_sd_offer_discount;
1061
1062 IF l_act_util_rec.discount_type in ('AMT', 'NEWPRICE') THEN
1063 l_act_util_rec.discount_amount_currency_code := l_act_budgets_rec.request_currency;
1064 END IF;
1065
1066 l_ssd_on_idsm := FND_PROFILE.value('OZF_SSD_ON_IDSM');
1067
1068 IF OZF_DEBUG_LOW_ON THEN
1069 OZF_UTILITY_PVT.debug_message('OZF: Supplier Ship and Debit on Indirect Sales :' || l_ssd_on_idsm);
1070 END IF;
1071
1072 IF l_ssd_on_idsm = 'Y' THEN
1073 IF (l_resale_line_rec.supplier_item_cost = -1 AND (l_resale_util_rec.discount_type = '%' OR l_resale_util_rec.discount_type ='UNIT_PRICE')) THEN
1074 IF FND_MSG_PUB.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
1075 FND_MESSAGE.Set_Token('OZF', 'OZF_COST_PRICE_NOT_FOUND');
1076 --//Program Error: No cost price was found to complete this SDR offer accrual.
1077 FND_MESSAGE.Set_Token('OFFR',l_offer_name);
1078 FND_MESSAGE.Set_Token('ORDER',l_act_util_rec.object_id);
1079 FND_MESSAGE.Set_Token('ITEM',l_act_util_rec.product_id);
1080 FND_MESSAGE.Set_Token('TEXT',sqlerrm);
1081 FND_MSG_PUB.add;
1082 END IF;
1083 RAISE fnd_api.g_exc_error;
1084 END IF;
1085
1086 l_act_util_rec.cost_price := l_resale_line_rec.supplier_item_cost;
1087 l_act_util_rec.cost_price_currency_code := l_act_budgets_rec.request_currency;
1088 END IF;
1089 ELSE
1090 l_act_util_rec.reference_type := 'BATCH';
1091 l_act_util_rec.reference_id := l_resale_util_rec.resale_batch_id;
1092 END IF;
1093
1094 l_act_util_rec.attribute_category := l_resale_util_rec.attribute_category;
1095 l_act_util_rec.attribute1 := l_resale_util_rec.attribute1;
1096 l_act_util_rec.attribute2 := l_resale_util_rec.attribute2;
1097 l_act_util_rec.attribute3 := l_resale_util_rec.attribute3;
1098 l_act_util_rec.attribute4 := l_resale_util_rec.attribute4;
1099 l_act_util_rec.attribute5 := l_resale_util_rec.attribute5;
1100 l_act_util_rec.attribute6 := l_resale_util_rec.attribute6;
1101 l_act_util_rec.attribute7 := l_resale_util_rec.attribute7;
1102 l_act_util_rec.attribute8 := l_resale_util_rec.attribute8;
1103 l_act_util_rec.attribute9 := l_resale_util_rec.attribute9;
1104 l_act_util_rec.attribute10 := l_resale_util_rec.attribute10;
1105 l_act_util_rec.attribute11 := l_resale_util_rec.attribute11;
1106 l_act_util_rec.attribute12 := l_resale_util_rec.attribute12;
1107 l_act_util_rec.attribute13 := l_resale_util_rec.attribute13;
1108 l_act_util_rec.attribute14 := l_resale_util_rec.attribute14;
1109 l_act_util_rec.attribute15 := l_resale_util_rec.attribute15;
1110
1111 --//populate beneficary for volume offer
1112 IF l_offer_type = 'VOLUME_OFFER' AND l_act_util_rec.object_type = 'TP_ORDER' AND l_act_budgets_rec.arc_act_budget_used_by = 'OFFR' THEN
1113 l_beneficiary_account_id := OZF_VOLUME_CALCULATION_PUB.get_beneficiary(l_offer_id,l_act_util_rec.object_id);
1114 IF OZF_DEBUG_LOW_ON THEN
1115 ozf_utility_pvt.debug_message ('l_offer_id: ' || l_offer_id);
1116 ozf_utility_pvt.debug_message ('l_beneficiary_account_id: ' || l_beneficiary_account_id);
1117 END IF;
1118
1119 IF l_beneficiary_account_id = 0 THEN
1120 l_act_util_rec.cust_account_id := l_act_util_rec.billto_cust_account_id;
1121 ELSE
1122 l_act_util_rec.cust_account_id := l_beneficiary_account_id;
1123 l_act_util_rec.ship_to_site_use_id := NULL;
1124 l_act_util_rec.bill_to_site_use_id := NULL;
1125 END IF;
1126 END IF;
1127
1128 OPEN OZF_RESALE_COMMON_PVT.g_adjustment_id_csr;
1129 FETCH OZF_RESALE_COMMON_PVT.g_adjustment_id_csr INTO l_adjustment_rec.resale_adjustment_id;
1130 CLOSE OZF_RESALE_COMMON_PVT.g_adjustment_id_csr;
1131
1132 l_act_util_rec.price_adjustment_id := l_adjustment_rec.resale_adjustment_id;
1133
1134 IF OZF_DEBUG_HIGH_ON THEN
1135 OZF_UTILITY_PVT.debug_message('Before Call Create_Adj_and_Utilization');
1136 OZF_UTILITY_PVT.debug_message('l_adjustment_rec.resale_adjustment_id :'|| l_adjustment_rec.resale_adjustment_id);
1137 END IF;
1138
1139
1140 --//Invoke private API to create Adjustments and Utilizations
1141 OZF_RESALE_COMMON_PVT.Create_Adj_and_Utilization(
1142 p_api_version => 1
1143 ,p_init_msg_list => FND_API.G_FALSE
1144 ,p_commit => FND_API.G_FALSE
1145 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
1146 ,p_price_adj_rec => l_adjustment_rec
1147 ,p_act_budgets_rec => l_act_budgets_rec
1148 ,p_act_util_rec => l_act_util_rec
1149 ,p_to_create_utilization => TRUE
1150 ,x_return_status => l_return_status
1151 ,x_msg_data => l_msg_data
1152 ,x_msg_count => l_msg_count
1153 ,x_utilization_id => l_utilization_id
1154 );
1155
1156 IF OZF_DEBUG_HIGH_ON THEN
1157 OZF_UTILITY_PVT.debug_message('After Call Create_Adj_and_Utilization ');
1158 OZF_UTILITY_PVT.debug_message('l_utilization_id :'|| l_utilization_id);
1159 OZF_UTILITY_PVT.debug_message('l_return_status :'|| l_return_status);
1160 END IF;
1161
1162 IF l_return_status = fnd_api.g_ret_sts_error THEN
1163 RAISE fnd_api.g_exc_error;
1164 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1165 RAISE fnd_api.g_exc_unexpected_error;
1166 END IF;
1167 x_utilization_id := l_utilization_id;
1168
1169
1170 IF l_offer_type = 'VOLUME_OFFER' AND l_act_budgets_rec.arc_act_budget_used_by = 'OFFR' THEN
1171 l_sales_transaction_rec.sold_from_cust_account_id := l_resale_line_rec.sold_from_cust_account_id;
1172 l_sales_transaction_rec.sold_to_cust_account_id := l_resale_line_rec.bill_to_cust_account_id;
1173 l_sales_transaction_rec.sold_to_party_id := l_resale_line_rec.bill_to_party_id;
1174 l_sales_transaction_rec.sold_to_party_site_id := l_resale_line_rec.bill_to_party_site_id;
1175 l_sales_transaction_rec.bill_to_site_use_id := l_resale_line_rec.bill_to_site_use_id;
1176 l_sales_transaction_rec.ship_to_site_use_id := l_resale_line_rec.ship_to_site_use_id;
1177 l_sales_transaction_rec.transaction_date := l_resale_line_rec.date_ordered;
1178 l_sales_transaction_rec.quantity := l_resale_line_rec.quantity;
1179 l_sales_transaction_rec.uom_code := l_resale_line_rec.uom_code;
1180 l_sales_transaction_rec.amount := ABS(l_resale_util_rec.unit_price * l_resale_line_rec.quantity);
1181 l_sales_transaction_rec.currency_code := l_resale_line_rec.currency_code;
1182 l_sales_transaction_rec.inventory_item_id := l_resale_line_rec.inventory_item_id;
1183 l_sales_transaction_rec.header_id := l_resale_line_rec.resale_header_id;
1184 l_sales_transaction_rec.line_id := l_resale_line_rec.resale_line_id;
1185 l_sales_transaction_rec.source_code := 'IS';
1186
1187 IF l_resale_line_rec.product_transfer_movement_type = 'TI' THEN
1188 l_sales_transaction_rec.transfer_type := 'IN';
1189 ELSIF l_resale_line_rec.product_transfer_movement_type = 'TO' THEN
1190 l_sales_transaction_rec.transfer_type := 'OUT';
1191 ELSIF l_resale_line_rec.product_transfer_movement_type = 'DC' THEN
1192 l_sales_transaction_rec.transfer_type := 'OUT';
1193 ELSIF l_resale_line_rec.product_transfer_movement_type = 'CD' THEN
1194 l_sales_transaction_rec.transfer_type := 'IN';
1195 END IF;
1196
1197 OZF_VOLUME_CALCULATION_PUB.Create_Volume(
1198 p_init_msg_list => FND_API.g_false
1199 ,p_api_version => 1.0
1200 ,p_commit => FND_API.g_false
1201 ,x_return_status => l_return_status
1202 ,x_msg_count => l_msg_count
1203 ,x_msg_data => l_msg_data
1204 ,p_volume_detail_rec => l_sales_transaction_rec
1205 ,p_qp_list_header_id => l_resale_util_rec.list_header_id
1206 ,x_apply_discount => l_vol_offr_apply_discount
1207 );
1208 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1209 RAISE FND_API.G_EXC_ERROR;
1210 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1211 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1212 END IF;
1213 END IF;
1214
1215 --================================================================================================================
1216 --// Commit the process
1217 IF OZF_DEBUG_HIGH_ON THEN
1218 OZF_UTILITY_PVT.debug_message('Public API: '|| l_api_name||' End');
1219 END IF;
1220 IF FND_API.to_Boolean( p_commit )
1221 THEN
1222 COMMIT WORK;
1223 END IF;
1224
1225 FND_MSG_PUB.Count_And_Get (
1226 p_encoded => FND_API.G_FALSE,
1227 p_count => x_msg_count,
1228 p_data => x_msg_data
1229 );
1230
1231 EXCEPTION
1232 WHEN FND_API.G_EXC_ERROR THEN
1233 ROLLBACK TO Create_resale_utilization;
1234 x_return_status := FND_API.G_RET_STS_ERROR;
1235 -- Standard call to get message count and if count=1, get the message
1236 FND_MSG_PUB.Count_And_Get (
1237 p_encoded => FND_API.G_FALSE,
1238 p_count => x_msg_count,
1239 p_data => x_msg_data
1240 );
1241 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1242 ROLLBACK TO Create_resale_utilization;
1243 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1244 -- Standard call to get message count and if count=1, get the message
1245 FND_MSG_PUB.Count_And_Get (
1246 p_encoded => FND_API.G_FALSE,
1247 p_count => x_msg_count,
1248 p_data => x_msg_data
1249 );
1250 WHEN OTHERS THEN
1251 ROLLBACK TO Create_resale_utilization;
1252 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1253 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1254 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1255 END IF;
1256 -- Standard call to get message count and if count=1, get the message
1257 FND_MSG_PUB.Count_And_Get (
1258 p_encoded => FND_API.G_FALSE,
1259 p_count => x_msg_count,
1260 p_data => x_msg_data
1261 );
1262 End Create_resale_utilization;
1263
1264 END OZF_RESALE_PUB;