[Home] [Help]
PACKAGE BODY: APPS.OZF_TP_ACCRUAL_PVT
Source
1 PACKAGE BODY OZF_TP_ACCRUAL_PVT AS
2 /* $Header: ozfvtpab.pls 120.24.12010000.4 2008/12/02 01:37:50 julou ship $ */
3
4 -- Package name : OZF_TP_ACCRUAL_PVT
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- END of Comments
9
10 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OZF_TP_ACCRUAL_PVT';
11 G_FILE_NAME CONSTANT VARCHAR2(30) := 'ozfvtpab.pls';
12
13 G_PRICING_EVENT CONSTANT VARCHAR2(30) := 'PRICING';
14
15 G_TP_ACCRUAL_UTIL_TYPE CONSTANT VARCHAR2(30) :='ADJUSTMENT';
16
17 OZF_DEBUG_HIGH_ON BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_high);
18 OZF_DEBUG_LOW_ON BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low);
19
20 G_PRC_DIFF_BUDGET NUMBER := FND_PROFILE.value('OZF_THRDPTY_PRCDIFF_BUDGET');
21 G_TP_DEFAULT_PRICE_LIST NUMBER := FND_PROFILE.value('OZF_TP_ACCRUAL_PRICE_LIST');
22 G_PRICING_SIM_EVENT VARCHAR2(30) := FND_PROFILE.value('OZF_PRICING_SIMULATION_EVENT');
23 G_ACCRUAL_ON_SELLING VARCHAR2(1) := FND_PROFILE.value('OZF_ACC_ON_SELLING_PRICE');
24 G_BULK_LIMIT NUMBER := NVL(FND_PROFILE.value('OZF_BULK_LIMIT_SIZE') , 500);
25 G_ALLOW_INTER_COMMIT VARCHAR2(1) := NVL(FND_PROFILE.value('OZF_ALLOW_INTER_COMMIT'), 'Y');
26 G_CONC_REQUEST_ID NUMBER := FND_GLOBAL.CONC_REQUEST_ID;
27
28 ---------------------------------------------------------------------
29 -- FUNCTION
30 -- is_valid_offer
31 --
32 -- PURPOSE
33 -- This procedure this offer should be accrued or not
34 --
35 -- PARAMETERS
36 -- p_list_header_id
37 --
38 -- NOTES
39 --------------------------------------------------------------------
40 PROCEDURE Is_Valid_Offer(
41 p_list_header_id IN NUMBER,
42 p_list_line_id IN NUMBER,
43 p_line_id IN NUMBER,
44 p_object_type IN VARCHAR2,
45 x_result OUT NOCOPY BOOLEAN,
46 x_return_status OUT NOCOPY VARCHAR2
47 )
48 IS
49 l_result VARCHAR2(2) := NULL;
50
51 CURSOR omo_offer_csr IS
52 SELECT 1
53 FROM ozf_offers
54 WHERE qp_list_header_id = p_list_header_id;
55
56 CURSOR line_adjustment_csr IS
57 SELECT 1
58 FROM ozf_resale_adjustments
59 WHERE resale_line_id = p_line_id
60 AND list_header_id = p_list_header_id
61 AND list_line_id = p_list_line_id;
62
63 BEGIN
64 -- Initialize API return status to sucess
65 x_return_status := FND_API.G_RET_STS_SUCCESS;
66
67 IF p_object_type <> 'PL' THEN
68 -- First it has to be an OMO offer
69 OPEN omo_offer_csr;
70 FETCH omo_offer_csr INTO l_result;
71 CLOSE omo_offer_csr;
72
73 IF l_result is not NULL AND
74 l_result = '1' THEN
75
76 x_result:= true;
77 ELSE
78 x_result:= false;
79 END IF;
80 ELSE
81 x_result:= true;
82 END IF;
83
84 l_result := null;
85 IF x_result THEN
86 -- Second it has not been accrued before
87 OPEN line_adjustment_csr;
88 FETCH line_adjustment_csr INTO l_result;
89 CLOSE line_adjustment_csr;
90 IF l_result is not NULL THEN
91 x_result:= false;
92 END IF;
93 END IF;
94 EXCEPTION
95 WHEN FND_API.G_EXC_ERROR THEN
96 x_return_status := FND_API.G_RET_STS_ERROR;
97 WHEN OTHERS THEN
98 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
99 ozf_utility_pvt.error_message('OZF_RESALE_IS_TM_OFFER');
100 END is_valid_offer;
101
102 ---------------------------------------------------------------------
103 -- PROCEDURE
104 -- Validate_Batch
105 --
106 -- PURPOSE
107 -- This procedure validates the batch information
108 --
109 -- PARAMETERS
110 --
111 --
112 -- NOTES
113 ---------------------------------------------------------------------
114 PROCEDURE Validate_Batch(
115 p_api_version IN NUMBER
116 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
117 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
118 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
119 ,p_resale_batch_id IN NUMBER
120 ,x_return_status OUT NOCOPY VARCHAR2
121 ,x_msg_data OUT NOCOPY VARCHAR2
122 ,x_msg_count OUT NOCOPY NUMBER
123 )IS
124 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Batch';
125 l_api_version CONSTANT NUMBER := 1.0;
126 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
127 --
128
129 CURSOR batch_info_csr IS
130 SELECT status_code
131 , org_id
132 from ozf_resale_batches
133 WHERE resale_batch_id = p_resale_batch_id;
134
135 l_status_code varchar2(30);
136 l_org_id NUMBER;
137
138 BEGIN
139 -- Standard begin of API savepoint
140 -- Standard call to check for call compatibility.
141 IF NOT FND_API.Compatible_API_Call (
142 l_api_version,
143 p_api_version,
144 l_api_name,
145 G_PKG_NAME)
146 THEN
147 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
148 END IF;
149
150 -- Initialize API return status to sucess
151 x_return_status := FND_API.G_RET_STS_SUCCESS;
152
153 --Initialize message list if p_init_msg_list is TRUE.
154 IF FND_API.To_Boolean (p_init_msg_list) THEN
155 FND_MSG_PUB.initialize;
156 END IF;
157
158 -- Debug Message
159 IF OZF_DEBUG_HIGH_ON THEN
160 OZF_UTILITY_PVT.debug_message(p_message_text => l_full_name||': Start');
161 END IF;
162
163 -- First, do some basic check
164
165 OPEN batch_info_csr;
166 FETCH batch_info_csr INTO l_status_code, l_org_id;
167 CLOSE batch_info_csr;
168
169 -- Check status
170 IF l_status_code <> 'CLOSED' THEN
171
172 x_return_status := FND_API.G_RET_STS_ERROR;
173 ozf_utility_pvt.error_message('OZF_BATCH_STATUS_WNG');
174 BEGIN
175 INSERT INTO ozf_resale_logs_all(
176 resale_log_id,
177 resale_id,
178 resale_id_type,
179 error_code,
180 error_message,
181 column_name,
182 column_value,
183 org_id
184 ) SELECT
185 ozf_resale_logs_all_s.nextval,
186 p_resale_batch_id,
187 'BATCH',
188 'OZF_BATCH_STATUS_WNG',
189 FND_MESSAGE.get_string('OZF','OZF_BATCH_STATUS_WNG'),
190 'STATUS_CODE',
191 l_status_code,
192 l_org_id
193 FROM dual
194 WHERE NOT EXISTS (
195 SELECT 1
196 FROM ozf_resale_logs a
197 WHERE a.resale_id = p_resale_batch_id
198 AND a.resale_id_type = 'BATCH'
199 AND a.error_code = 'OZF_BATCH_STATUS_WNG'
200 );
201 EXCEPTION
202 WHEN OTHERS THEN
203 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
204 FND_MESSAGE.set_name('OZF', 'OZF_INS_RESALE_LOG_WRG');
205 FND_MESSAGE.Set_Token('TEXT',l_full_name||': End');
206 FND_MSG_PUB.add;
207 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
208 END IF;
209 END;
210 END IF;
211
212 -- Debug Message
213 IF OZF_DEBUG_HIGH_ON THEN
214 OZF_UTILITY_PVT.debug_message(p_message_text => l_full_name||': End');
215 END IF;
216
217 --Standard call to get message count and if count=1, get the message
218 FND_MSG_PUB.Count_And_Get (
219 p_encoded => FND_API.G_FALSE,
220 p_count => x_msg_count,
221 p_data => x_msg_data
222 );
223 EXCEPTION
224 WHEN FND_API.G_EXC_ERROR THEN
225 x_return_status := FND_API.G_RET_STS_ERROR;
226 -- Standard call to get message count and if count=1, get the message
227 FND_MSG_PUB.Count_And_Get (
228 p_encoded => FND_API.G_FALSE,
229 p_count => x_msg_count,
230 p_data => x_msg_data
231 );
232 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
233 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
234 -- Standard call to get message count and if count=1, get the message
235 FND_MSG_PUB.Count_And_Get (
236 p_encoded => FND_API.G_FALSE,
237 p_count => x_msg_count,
238 p_data => x_msg_data
239 );
240 WHEN OTHERS THEN
241 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
242
243 -- Standard call to get message count and if count=1, get the message
244 FND_MSG_PUB.Count_And_Get (
245 p_encoded => FND_API.G_FALSE,
246 p_count => x_msg_count,
247 p_data => x_msg_data
248 );
249 END Validate_Batch;
250
251 ---------------------------------------------------------------------
252 -- PROCEDURE
253 -- Validate_Order_Record
254 --
255 -- PURPOSE
256 -- This procedure validates the order information
257 -- These are validation specific to third party accrual process
258 --
259 -- PARAMETERS
260 --
261 --
262 -- NOTES
263 ---------------------------------------------------------------------
264 PROCEDURE Validate_Order_Record(
265 p_api_version IN NUMBER
266 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
267 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
268 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
269 ,p_resale_batch_id IN NUMBER
270 ,p_caller_type IN VARCHAR2
271 ,x_return_status OUT NOCOPY VARCHAR2
272 ,x_msg_data OUT NOCOPY VARCHAR2
273 ,x_msg_count OUT NOCOPY NUMBER
274 )IS
275 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Order_Record';
276 l_api_version CONSTANT NUMBER := 1.0;
277 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
278 --
279
280 BEGIN
281 -- Standard BEGIN of API savepoint
282 -- Standard call to check for call compatibility.
283 IF NOT FND_API.Compatible_API_Call (
284 l_api_version,
285 p_api_version,
286 l_api_name,
287 G_PKG_NAME)
288 THEN
289 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
290 END IF;
291
292 --Initialize message list if p_init_msg_list is TRUE.
293 IF FND_API.To_Boolean (p_init_msg_list) THEN
294 FND_MSG_PUB.initialize;
295 END IF;
296
297 -- Debug Message
298 IF OZF_DEBUG_HIGH_ON THEN
299 OZF_UTILITY_PVT.debug_message(p_message_text => l_full_name||': Start');
300 END IF;
301
302 -- Initialize API return status to sucess
303 x_return_status := FND_API.G_RET_STS_SUCCESS;
304
305 -- Make sure that selling_price is not NULL for direct customers
306 IF p_caller_type = 'IFACE' THEN
307 BEGIN
308 INSERT INTO ozf_resale_logs_all(
309 resale_log_id,
310 resale_id,
311 resale_id_type,
312 error_code,
313 error_message,
314 column_name,
315 column_value,
316 org_id
317 ) SELECT
318 ozf_resale_logs_all_s.nextval,
319 resale_line_int_id,
320 'IFACE',
321 'OZF_RESALE_SELL_PRICE_NULL',
322 FND_MESSAGE.get_string('OZF','OZF_RESALE_SELL_PRICE_NULL'),
323 'SELLING_PRICE',
324 NULL,
325 org_id
326 FROM ozf_resale_lines_int_all b
327 WHERE b.status_code = 'OPEN'
328 AND b.direct_customer_flag = 'T'
329 AND b.selling_price IS NULL
330 AND b.resale_batch_id = p_resale_batch_id
331 AND NOT EXISTS(
332 SELECT 1
333 FROM ozf_resale_logs_all a
334 WHERE a.resale_id = b.resale_line_int_id
335 AND a.resale_id_type = 'IFACE'
336 AND a.error_code ='OZF_RESALE_SELL_PRICE_NULL'
337 );
338 EXCEPTION
339 WHEN OTHERS THEN
340 ozf_utility_pvt.error_message('OZF_INS_RESALE_LOG_WRG');
341 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
342 END;
343
344 BEGIN
345 UPDATE ozf_resale_lines_int_all
346 SET status_code = 'DISPUTED',
347 dispute_code = 'OZF_RESALE_SELL_PRICE_NULL'
348 WHERE status_code = 'OPEN'
349 AND direct_customer_flag = 'T'
350 AND selling_price IS NULL
351 AND resale_batch_id = p_resale_batch_id;
352 EXCEPTION
353 WHEN OTHERS THEN
354 ozf_utility_pvt.error_message('OZF_UPD_RESALE_INT_WRG');
355 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
356 END;
357
358 ELSE
359 BEGIN
360 INSERT INTO ozf_resale_logs_all(
361 resale_log_id,
362 resale_id,
363 resale_id_type,
364 error_code,
365 error_message,
366 column_name,
367 column_value,
368 org_id
369 ) SELECT
370 ozf_resale_logs_all_s.nextval,
371 b.resale_line_id,
372 'LINE',
373 'OZF_RESALE_SELL_PRICE_NULL',
374 FND_MESSAGE.get_string('OZF','OZF_RESALE_SELL_PRICE_NULL'),
375 'SELLING_PRICE',
376 NULL,
377 b.org_id
378 FROM ozf_resale_lines_all b
379 , ozf_resale_batch_line_maps_all c
380 WHERE b.direct_customer_flag = 'T'
381 AND b.selling_price IS NULL
382 AND b.resale_line_id = c.resale_line_id
383 AND c.resale_batch_id = p_resale_batch_id
384 AND NOT EXISTS(SELECT 1
385 FROM ozf_resale_logs_all a
386 WHERE a.resale_id = b.resale_line_id
387 AND a.resale_id_type = 'LINE'
388 AND a.error_code ='OZF_RESALE_SELL_PRICE_NULL'
389 );
390 EXCEPTION
391 WHEN OTHERS THEN
392 ozf_utility_pvt.error_message('OZF_INS_RESALE_LOG_WRG');
393 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
394 END;
395 END IF;
396
397 -- Debug Message
398 IF OZF_DEBUG_HIGH_ON THEN
399 OZF_UTILITY_PVT.debug_message(p_message_text => l_full_name||': End');
400 END IF;
401 --Standard call to get message count and if count=1, get the message
402 FND_MSG_PUB.Count_And_Get (
403 p_encoded => FND_API.G_FALSE,
404 p_count => x_msg_count,
405 p_data => x_msg_data
406 );
407 EXCEPTION
408 WHEN FND_API.G_EXC_ERROR THEN
409 x_return_status := FND_API.G_RET_STS_ERROR;
410 -- Standard call to get message count and if count=1, get the message
411 FND_MSG_PUB.Count_And_Get (
412 p_encoded => FND_API.G_FALSE,
413 p_count => x_msg_count,
414 p_data => x_msg_data
415 );
416 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
417 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
418 -- Standard call to get message count and if count=1, get the message
419 FND_MSG_PUB.Count_And_Get (
420 p_encoded => FND_API.G_FALSE,
421 p_count => x_msg_count,
422 p_data => x_msg_data
423 );
424 WHEN OTHERS THEN
425 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
426
427 -- Standard call to get message count and if count=1, get the message
428 FND_MSG_PUB.Count_And_Get (
429 p_encoded => FND_API.G_FALSE,
430 p_count => x_msg_count,
431 p_data => x_msg_data
432 );
433 END Validate_Order_Record;
434
435 ---------------------------------------------------------------------
436 -- PROCEDURE
437 -- process_one_line
438 --
439 -- PURPOSE
440 -- This procedure process the pricing call result for third party accrual.
441 -- It creates resale lines and accruals based on the discount information.
442 --
443 -- PARAMETERS
444 -- p_resale_line_int_rec IN OZF_RESALE_COMMON_PVT.g_interface_rec_csr%rowtype,
445 -- p_resale_line_rec IN OZF_RESALE_LINES_ALL%rowtype,
446 -- p_line_result_rec IN OZF_ORDER_PRICE_PVT.LINE_REC_TYPE,
447 -- p_header_id IN NUMBER,
448 -- p_resale_batch_id IN NUMBER,
449 -- p_inventory_tracking IN BOOLEAN,
450 -- p_price_diff_fund_id IN NUMBER,
451 -- p_object_type IN VARCHAR2,
452 -- x_return_status OUT NOCOPY VARCHAR2)
453 --
454 -- NOTES
455 -- 1. Non-monetray accruals have not been considered. Should look INTO ldets.benefit_qty
456 -- and ldets.benefit_uom for calculation.
457 --
458 ---------------------------------------------------------------------
459 PROCEDURE Process_One_Line(
460 p_resale_line_int_rec IN OZF_RESALE_COMMON_PVT.g_interface_rec_csr%rowtype,
461 p_resale_line_rec IN OZF_RESALE_LINES%rowtype,
462 p_line_result_rec IN OZF_ORDER_PRICE_PVT.LINE_REC_TYPE,
463 p_header_id IN NUMBER,
464 p_resale_batch_id IN NUMBER,
465 p_inventory_tracking IN BOOLEAN,
466 p_price_diff_fund_id IN NUMBER,
467 p_caller_type IN VARCHAR2,
468 p_approver_id IN NUMBER,
469 x_return_status OUT NOCOPY VARCHAR2
470 )
471 IS
472 l_api_name CONSTANT VARCHAR2(30) := 'Process_One_Line';
473 l_api_version CONSTANT NUMBER := 1.0;
474 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
475 ---
476 l_msg_data VARCHAR2(2000);
477 l_msg_count NUMBER;
478 l_return_status VARCHAR2(30);
479 --
480 -- NOTES: PPL has pricing_group_sequence as 0
481 CURSOR line_ldets_tbl_csr(p_index IN NUMBER) IS
482 SELECT *
483 FROM qp_ldets_v
484 WHERE line_index = p_index
485 ORDER BY pricing_group_sequence;
486
487 TYPE line_ldets_tbl_type IS TABLE OF line_ldets_tbl_csr%rowtype
488 INDEX BY binary_integer;
489
490 l_line_ldets_tbl line_ldets_tbl_type;
491 l_line_id NUMBER := NULL;
492 l_ldets_tbl OZF_ORDER_PRICE_PVT.LDETS_TBL_TYPE;
493
494 --j NUMBER :=1;
495 m NUMBER := 1;
496
497 --l_pric_act_budgets_rec ozf_actbudgets_pvt.act_budgets_rec_type;
498 --l_pric_act_util_rec ozf_actbudgets_pvt.act_util_rec_type;
499 --l_pric_price_adj_rec ozf_resale_adjustments_all%rowtype;
500
501 l_act_budgets_rec OZF_ACTBUDGETS_PVT.act_budgets_rec_type;
502 l_act_util_rec OZF_ACTBUDGETS_PVT.act_util_rec_type;
503 l_adjustment_rec OZF_RESALE_ADJUSTMENTS_ALL%rowtype;
504
505 l_is_valid_offer BOOLEAN;
506 l_price_diff_util BOOLEAN;
507 l_object_type VARCHAR2(30);
508 l_line_int_rec OZF_RESALE_LINES_INT%rowtype;
509
510 l_rate NUMBER;
511 l_exchange_type VARCHAR2(30);
512 l_to_create_utilization BOOLEAN;
513
514 /*
515 -- Only chargeback batch can share the agreement information.
516 CURSOR adjustment_info( p_line_id NUMBER,
517 p_batch_id NUMBER) IS
518 SELECT a.orig_system_agreement_uom,
519 a.orig_system_agreement_name,
520 a.orig_system_agreement_type,
521 a.orig_system_agreement_status,
522 a.orig_system_agreement_curr,
523 a.orig_system_agreement_price,
524 a.orig_system_agreement_quantity,
525 a.agreement_id, a.agreement_type,
526 a.agreement_name, a.agreement_price,
527 a.agreement_uom_code,
528 a.corrected_agreement_id,
529 a.corrected_agreement_name,
530 a.credit_code,
531 a.credit_advice_date
532 FROM ozf_resale_adjustments a, ozf_resale_batches b
533 WHERE a.resale_line_id = p_line_id
534 AND a.resale_batch_id = p_batch_id
535 AND a.line_agreement_flag = 'T'
536 AND a.resale_batch_id = b.resale_batch_id
537 AND b.batch_type = OZF_RESALE_COMMON_PVT.G_CHARGEBACK
538 AND rownum = 1;
539 */
540 l_log_id NUMBER;
541
542 l_sales_transaction_id NUMBER;
543 l_sales_transaction_rec OZF_SALES_TRANSACTIONS_PVT.sales_transaction_rec_type;
544 l_vol_offr_apply_discount NUMBER;
545
546 CURSOR party_id_csr(p_cust_account_id IN NUMBER) IS
547 SELECT party_id
548 FROM hz_cust_accounts
549 WHERE cust_account_id = p_cust_account_id;
550
551 CURSOR party_site_id_csr(p_account_site_id number) is
552 SELECT party_site_id
553 FROM hz_cust_acct_sites
554 WHERE cust_acct_site_id = p_account_site_id;
555
556 l_new_request_amount NUMBER;
557
558 -- julou 5723309: create util for VO PBH line only
559 CURSOR c_offer_type(p_qp_list_header_id NUMBER) IS
560 SELECT offer_type
561 FROM ozf_offers
562 WHERE qp_list_header_id = p_qp_list_header_id;
563 l_offer_type VARCHAR2(30);
564 -- end julou 5723309
565 BEGIN
566 IF OZF_DEBUG_HIGH_ON THEN
567 OZF_UTILITY_PVT.debug_message(p_message_text => l_full_name||': Start');
568 END IF;
569
570 SAVEPOINT PROC_ONE_LINE;
571
572 x_return_status := FND_API.G_RET_STS_SUCCESS;
573
574 -- Make sure that records are passed in due to different caller type
575 IF p_caller_type = 'IFACE' AND
576 p_resale_line_int_rec.resale_line_int_id IS NULL THEN
577 ozf_utility_pvt.error_message('OZF_RESALE_INT_RECD_NULL');
578 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
579 END IF;
580
581 IF p_caller_type = 'RESALE' AND
582 p_resale_line_rec.resale_line_id IS NULL THEN
583 ozf_utility_pvt.error_message('OZF_RESALE_RECD_NULL');
584 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
585 END IF;
586
587 -- create a resale line record, if it's FROM iface
588 IF p_caller_type = 'IFACE' THEN
589 OZF_RESALE_COMMON_PVT.Insert_resale_line(
590 p_api_version => 1
591 ,p_init_msg_list => FND_API.G_FALSE
592 ,p_commit => FND_API.G_FALSE
593 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
594 ,p_line_int_rec => p_resale_line_int_rec
595 ,p_header_id => p_header_id
596 ,x_line_id => l_line_id
597 ,x_return_status => l_return_status
598 ,x_msg_data => l_msg_data
599 ,x_msg_count => l_msg_count
600 );
601 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
602 ozf_utility_pvt.error_message('OZF_INS_RESALE_LINE_WRG');
603 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
604 END IF;
605
606 OZF_RESALE_COMMON_PVT.Insert_Resale_Line_Mapping(
607 p_api_version => 1
608 ,p_init_msg_list => FND_API.G_FALSE
609 ,p_commit => FND_API.G_FALSE
610 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
611 ,p_resale_batch_id => p_resale_batch_id
612 ,p_line_id => l_line_id
613 ,x_return_status => l_return_status
614 ,x_msg_data => l_msg_data
615 ,x_msg_count => l_msg_count
616 );
617 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
618 RAISE FND_API.G_EXC_ERROR;
619 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
620 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
621 END IF;
622
623 OZF_RESALE_COMMON_PVT.Create_Sales_Transaction(
624 p_api_version => 1
625 ,p_init_msg_list => FND_API.G_FALSE
626 ,p_commit => FND_API.G_FALSE
627 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
628 ,p_line_int_rec => p_resale_line_int_rec
629 ,p_header_id => p_header_id
630 ,p_line_id => l_line_id
631 ,x_sales_transaction_id => l_sales_transaction_id
632 ,x_return_status => l_return_status
633 ,x_msg_data => l_msg_data
634 ,x_msg_count => l_msg_count
635 );
636 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
637 RAISE FND_API.G_EXC_ERROR;
638 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
639 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
640 END IF;
641
642 -- Bug 4380203 (+)
643 -- Bug 4380203 Fixing: Inventory Temp table is already updated in Validate_Inventory_Level
644 /*
645 IF p_inventory_tracking THEN
646 OZF_SALES_TRANSACTIONS_PVT.update_inventory_tmp (
647 p_api_version => 1.0
648 ,p_init_msg_list => FND_API.G_FALSE
649 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
650 ,p_sales_transaction_id => l_sales_transaction_id
651 ,x_return_status => l_return_status
652 ,x_msg_data => l_msg_data
653 ,x_msg_count => l_msg_count
654 );
655 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
656 RAISE FND_API.G_EXC_ERROR;
657 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
658 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
659 END IF;
660 END IF;
661 */
662 -- Bug 4380203 (-)
663 ELSE
664 -- For resale caller, just need to gat the id
665 l_line_id := p_resale_line_rec.resale_line_id;
666 END IF;
667
668 -- I create adjustment, if a line was created before and adjustment is new
669 -- or a new line is just created
670
671 -- clear up the temparory result PL/SQL table
672 IF l_ldets_tbl.EXISTS(1) THEN
673 l_ldets_tbl.DELETE;
674 END IF;
675
676 m:=1;
677
678 OPEN line_ldets_tbl_csr(p_line_result_rec.line_index);
679 FETCH line_ldets_tbl_csr BULK COLLECT INTO l_ldets_tbl;
680 --LOOP
681 -- FETCH line_ldets_tbl_csr INTO l_ldets_tbl(m);
682 -- EXIT when line_ldets_tbl_csr%NOTFOUND;
683 -- m := m + 1;
684 --END LOOP;
685 CLOSE line_ldets_tbl_csr;
686
687 l_price_diff_util := ( p_caller_type = 'IFACE' AND
688 p_line_result_rec.unit_price < p_resale_line_int_rec.selling_price
689 )
690 OR
691 ( p_caller_type = 'RESALE' AND
692 p_line_result_rec.unit_price < p_resale_line_rec.selling_price
693 );
694
695 IF OZF_DEBUG_LOW_ON THEN
696 IF l_price_diff_util THEN
697 ozf_utility_PVT.debug_message(l_api_name||' >> do price adjustment');
698 ELSE
699 ozf_utility_pvt.debug_message(l_api_name||' >> no price adjustment');
700 END IF;
701 END IF;
702
703 IF l_ldets_tbl.EXISTS(1) THEN
704 FOR k IN 1..l_ldets_tbl.LAST LOOP
705
706 l_adjustment_rec := NULL;
707 l_act_budgets_rec := NULL;
708 l_act_util_rec := NULL;
709
710 -- Look in to list_line_type_code in view or CREATED_FROM_LIST_LINE_TYPE in tbl = 'DIS'
711 -- IF applied_flag= 'Y' OR
712 -- applied_flag= 'N' AND accrual_flag = 'Y' AND automatic_flag ='Y'THEN
713 -- create a price adjustment record
714 -- create a util_rec and act_budet_rec based on the discount
715 -- END IF;
716 -- INSERT INTO price adustment table
717 -- call budget api
718 -- Create an accrual for this accrual, pass in l_header_id as a refrence.
719 -- IF CREATED_FROM_LIST_LINE_TYPE = 'PBH' Then
720 -- do the same thing for each child line
721 -- END IF;
722 -- list_line_type_code 'PLL' is added.
723
724 IF OZF_DEBUG_LOW_ON THEN
725 ozf_utility_pvt.debug_message(l_api_name||' >> qp_ldets_v (+)');
726 ozf_utility_pvt.debug_message('l_ldets_tbl('||k||').list_header_id = '||l_ldets_tbl(k).list_header_id);
727 ozf_utility_pvt.debug_message('l_ldets_tbl('||k||').list_line_type_code = '||l_ldets_tbl(k).list_line_type_code);
728 ozf_utility_pvt.debug_message('l_ldets_tbl('||k||').applied_flag = '||l_ldets_tbl(k).applied_flag);
729 ozf_utility_pvt.debug_message('l_ldets_tbl('||k||').accrual_flag = '||l_ldets_tbl(k).accrual_flag);
730 ozf_utility_pvt.debug_message('l_ldets_tbl('||k||').automatic_flag = '||l_ldets_tbl(k).automatic_flag);
731 ozf_utility_pvt.debug_message(l_api_name||' >> qp_ldets_v (-)');
732 END IF;
733
734 IF l_ldets_tbl(k).list_line_type_code IN ('DIS','PBH', 'PLL') THEN
735 -- create utilization based on an offer
736
737 IF (l_ldets_tbl(k).applied_flag = 'Y' AND
738 l_ldets_tbl(k).accrual_flag = 'N'
739 )
740 OR
741 (l_ldets_tbl(k).accrual_flag = 'Y' AND
742 l_ldets_tbl(k).automatic_flag = 'Y'
743 )
744 OR
745 l_ldets_tbl(k).list_line_type_code = 'PLL' THEN
746
747 IF l_ldets_tbl(k).list_line_type_code = 'PLL' THEN
748 l_object_type := 'PL';
749 ELSE
750 l_object_type := 'OFFR';
751 END IF;
752
753 -- only accrual for TM offers
754 Is_Valid_Offer(p_list_header_id => l_ldets_tbl(k).list_header_id,
755 p_list_line_id => l_ldets_tbl(k).list_line_id,
756 p_line_id => l_line_id,
757 p_object_type => l_object_type,
758 x_result => l_is_valid_offer,
759 x_return_status => l_return_status
760 );
761 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
762 RAISE FND_API.G_EXC_ERROR;
763 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
764 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
765 END IF;
766
767 IF OZF_DEBUG_LOW_ON THEN
768 IF l_is_valid_offer OR l_ldets_tbl(k).list_line_type_code = 'PLL' THEN
769 ozf_utility_pvt.debug_message(l_api_name||' >> Accrual for TM OFFR or PLL adjustment (+)');
770 ozf_utility_pvt.debug_message('l_ldets_tbl('||k||').list_header_id = '||l_ldets_tbl(k).list_header_id);
771 ozf_utility_pvt.debug_message('l_ldets_tbl('||k||').order_qty_adj_amt = '||l_ldets_tbl(k).order_qty_adj_amt);
772 ozf_utility_pvt.debug_message('l_ldets_tbl('||k||').line_quantity = '||l_ldets_tbl(k).line_quantity);
773 ozf_utility_pvt.debug_message('order line priced_quantity = '||p_line_result_rec.priced_quantity);
774 IF l_ldets_tbl(k).list_line_type_code = 'PLL' THEN
775 ozf_utility_pvt.debug_message('order line unit_price = '||p_line_result_rec.unit_price);
776 END IF;
777 ozf_utility_pvt.debug_message(l_api_name||' >> Accrual for TM OFFR or PLL adjustment (-)');
778 END IF;
779 END IF;
780
781 IF l_is_valid_offer THEN
782 IF (l_ldets_tbl(k).list_line_type_code = 'PLL' AND
783 l_price_diff_util
784 )
785 OR
786 l_ldets_tbl(k).list_line_type_code IN ('DIS','PBH') THEN
787
788 -- IF l_ldets_tbl(k).line_quantity IS NULL THEN
789 l_ldets_tbl(k).line_quantity := NVL(p_line_result_rec.priced_quantity, ABS(p_resale_line_int_rec.quantity));
790 -- END IF;
791
792 -- BUG 4581928 (+)
793 IF p_caller_type = 'IFACE' THEN
794 l_ldets_tbl(k).line_quantity := l_ldets_tbl(k).line_quantity
795 * SIGN(p_resale_line_int_rec.quantity);
796 ELSE -- p_caller_type = 'RESALE'
797 l_ldets_tbl(k).line_quantity := l_ldets_tbl(k).line_quantity
798 * SIGN(p_resale_line_rec.quantity);
799 END IF;
800 -- BUG 4581928 (-)
801
802 -- R12 Volumn Offer Enhancement (+)
803 IF l_object_type = 'OFFR' THEN
804 OZF_VOLUME_CALCULATION_PUB.Update_Tracking_Line(
805 p_init_msg_list => FND_API.g_false
806 ,p_api_version => 1.0
807 ,p_commit => FND_API.g_false
808 ,x_return_status => l_return_status
809 ,x_msg_count => l_msg_count
810 ,x_msg_data => l_msg_data
811 ,p_list_header_id => l_ldets_tbl(k).list_header_id
812 ,p_interface_line_id => p_resale_line_int_rec.resale_line_int_id
813 ,p_resale_line_id => l_line_id
814 );
815 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
816 RAISE FND_API.G_EXC_ERROR;
817 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
818 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
819 END IF;
820 END IF;
821 -- R12 Volumn Offer Enhancement (-)
822
823 OPEN OZF_RESALE_COMMON_PVT.g_adjustment_id_csr;
824 FETCH OZF_RESALE_COMMON_PVT.g_adjustment_id_csr INTO l_adjustment_rec.resale_adjustment_id;
825 CLOSE OZF_RESALE_COMMON_PVT.g_adjustment_id_csr;
826
827 l_adjustment_rec.resale_batch_id := p_resale_batch_id;
828 l_adjustment_rec.resale_line_id := l_line_id;
829 l_adjustment_rec.list_header_id := l_ldets_tbl(k).list_header_id;
830 l_adjustment_rec.list_line_id := l_ldets_tbl(k).list_line_id;
831 IF l_ldets_tbl(k).list_line_type_code = 'PLL' THEN
832 IF p_caller_type = 'IFACE' THEN
833 l_adjustment_rec.accepted_amount :=
834 -1 * (p_resale_line_int_rec.selling_price - p_line_result_rec.unit_price);
835 ELSE
836 l_adjustment_rec.accepted_amount :=
837 -1 * (p_resale_line_rec.selling_price - p_line_result_rec.unit_price);
838 END IF;
839 ELSE
840 IF l_ldets_tbl(k).applied_flag = 'Y' THEN
841 l_adjustment_rec.accepted_amount := l_ldets_tbl(k).order_qty_adj_amt;
842 END IF;
843 END IF;
844 -- BUG 4558568 (+)
845 --l_adjustment_rec.total_accepted_amount :=
846 --l_adjustment_rec.accepted_amount * ABS(l_ldets_tbl(k).line_quantity); --abs(p_line_result_rec.priced_quantity);
847 --l_adjustment_rec.priced_quantity := ABS(l_ldets_tbl(k).line_quantity); --abs(p_line_result_rec.priced_quantity);
848 l_adjustment_rec.total_accepted_amount :=
849 l_adjustment_rec.accepted_amount * l_ldets_tbl(k).line_quantity;
850 l_adjustment_rec.priced_quantity := l_ldets_tbl(k).line_quantity;
851 -- BUG 4558568 (-)
852 l_adjustment_rec.priced_uom_code := p_line_result_rec.priced_uom_code;
853 l_adjustment_rec.operand := l_ldets_tbl(k).operand_value;
854 l_adjustment_rec.operand_calculation_code := l_ldets_tbl(k).operand_calculation_code;
855 l_adjustment_rec.priced_unit_price := p_line_result_rec.unit_price;
856 l_adjustment_rec.calculated_price := p_line_result_rec.unit_price;
857 l_adjustment_rec.STATUS_CODE := 'CLOSED';
858 l_adjustment_rec.claimed_amount := 0;
859 l_adjustment_rec.total_claimed_amount := 0;
860 l_adjustment_rec.allowed_amount := 0;
861 l_adjustment_rec.total_allowed_amount := 0;
862 l_adjustment_rec.tolerance_flag := 'F';
863 l_adjustment_rec.line_tolerance_amount := 0;
864
865 IF l_ldets_tbl(k).list_line_type_code = 'PLL' AND
866 p_caller_type = 'IFACE' THEN
867 l_adjustment_rec.orig_system_agreement_uom := p_resale_line_int_rec.orig_system_agreement_uom;
868 l_adjustment_rec.orig_system_agreement_name := p_resale_line_int_rec.orig_system_agreement_name;
869 l_adjustment_rec.orig_system_agreement_type := p_resale_line_int_rec.orig_system_agreement_type;
870 l_adjustment_rec.orig_system_agreement_status := p_resale_line_int_rec.orig_system_agreement_status;
871 l_adjustment_rec.orig_system_agreement_curr := p_resale_line_int_rec.orig_system_agreement_curr;
872 l_adjustment_rec.orig_system_agreement_price := p_resale_line_int_rec.orig_system_agreement_price;
873 l_adjustment_rec.orig_system_agreement_quantity := p_resale_line_int_rec.orig_system_agreement_quantity;
874 l_adjustment_rec.agreement_id := p_resale_line_int_rec.agreement_id;
875 l_adjustment_rec.agreement_type := p_resale_line_int_rec.agreement_type;
876 l_adjustment_rec.agreement_name := p_resale_line_int_rec.agreement_name;
877 l_adjustment_rec.agreement_price := p_resale_line_int_rec.agreement_price;
878 l_adjustment_rec.AGREEMENT_uom_code := p_resale_line_int_rec.agreement_uom_code;
879 l_adjustment_rec.corrected_agreement_id := p_resale_line_int_rec.corrected_agreement_id;
880 l_adjustment_rec.corrected_agreement_name := p_resale_line_int_rec.corrected_agreement_name;
881 l_adjustment_rec.credit_code := p_resale_line_int_rec.credit_code;
882 l_adjustment_rec.credit_advice_date := p_resale_line_int_rec.credit_advice_date;
883 l_adjustment_rec.line_agreement_flag := 'T';
884 ELSE
885 l_adjustment_rec.orig_system_agreement_uom := NULL;
886 l_adjustment_rec.orig_system_agreement_name := NULL;
887 l_adjustment_rec.orig_system_agreement_type := NULL;
888 l_adjustment_rec.orig_system_agreement_status := NULL;
889 l_adjustment_rec.orig_system_agreement_curr := NULL;
890 l_adjustment_rec.orig_system_agreement_price := NULL;
891 l_adjustment_rec.orig_system_agreement_quantity := NULL;
892 l_adjustment_rec.agreement_id := NULL;
893 l_adjustment_rec.agreement_type := NULL;
894 l_adjustment_rec.agreement_name := NULL;
895 l_adjustment_rec.agreement_price := NULL;
896 l_adjustment_rec.agreement_uom_code := NULL;
897 l_adjustment_rec.corrected_agreement_id := NULL;
898 l_adjustment_rec.corrected_agreement_name := NULL;
899 l_adjustment_rec.credit_code := NULL;
900 l_adjustment_rec.credit_advice_date := NULL;
901 l_adjustment_rec.line_agreement_flag := 'F';
902 END IF;
903
904 -- R12 MOAC (+)
905 IF p_caller_type = 'IFACE' THEN
906 l_adjustment_rec.org_id := p_resale_line_int_rec.org_id;
907 ELSE
908 l_adjustment_rec.org_id := p_resale_line_rec.org_id;
909 END IF;
910 -- R12 MOAC (-)
911
912 -- Create act Utilization Record.
913 l_act_util_rec.object_type := 'TP_ORDER';
914 l_act_util_rec.object_id := l_line_id;
915 l_act_util_rec.product_level_type :='PRODUCT';
916
917 l_act_util_rec.bill_to_site_use_id := p_resale_line_rec.bill_to_site_use_id;
918 l_act_util_rec.ship_to_site_use_id := p_resale_line_rec.ship_to_site_use_id;
919 ozf_utility_pvt.write_conc_log('JL: bill_to_site_use_id = ' || p_resale_line_rec.bill_to_site_use_id);
920 ozf_utility_pvt.write_conc_log('JL: ship_to_site_use_id = ' || p_resale_line_rec.ship_to_site_use_id);
921 IF p_caller_type = 'IFACE' THEN
922 l_act_util_rec.product_id := p_resale_line_int_rec.inventory_item_Id;
923 -- Here, there is a need to look INTO trading group issue
924 -- Handled by budget
925 l_act_util_rec.billto_cust_account_id := p_resale_line_int_rec.bill_to_cust_account_id ;
926 l_act_util_rec.gl_date := p_resale_line_int_rec.date_shipped;
927 -- R12 MOAC (+)
928 l_act_util_rec.org_id := p_resale_line_int_rec.org_id;
929 -- R12 MOAC (-)
930 ELSE
931 l_act_util_rec.product_id := p_resale_line_rec.inventory_item_Id;
932 -- Here, there is a need to look INTO trading group issue
933 -- Handled by budget
934 l_act_util_rec.billto_cust_account_id := p_resale_line_rec.bill_to_cust_account_id;
935 l_act_util_rec.gl_date := p_resale_line_rec.date_shipped;
936 -- R12 MOAC (+)
937 l_act_util_rec.org_id := p_resale_line_rec.org_id;
938 -- R12 MOAC (-)
939 END IF;
940
941 -- Reference for batch
942 l_act_util_rec.reference_type := 'BATCH';
943 l_act_util_rec.reference_id := p_resale_batch_id;
944 l_act_util_rec.price_adjustment_id := l_adjustment_rec.resale_adjustment_id;
945
946 IF l_ldets_tbl(k).list_line_type_code = 'PLL' THEN
947 l_act_util_rec.utilization_type :='ADJUSTMENT' ; -- Adjustmen for price difference
948 l_act_util_rec.adjustment_type_id := -10;
949 ELSE
950 l_act_util_rec.utilization_type :='UTILIZED' ; -- Always it is utilized.
951 END IF;
952
953 -- Create act Budget Record.
954 l_act_budgets_rec.act_budget_used_by_id := l_ldets_tbl(k).list_header_id;
955 l_act_budgets_rec.budget_source_id := l_ldets_tbl(k).list_header_id;
956 l_act_budgets_rec.status_code := 'APPROVED';
957 l_act_budgets_rec.transfer_type := 'UTILIZED';
958
959 IF l_ldets_tbl(k).list_line_type_code = 'PLL' THEN
960 l_act_budgets_rec.arc_act_budget_used_by := 'PRIC';
961 l_act_budgets_rec.budget_source_type := 'PRIC';
962 l_act_budgets_rec.approver_id := ozf_utility_pvt.get_resource_id(p_approver_id);
963 l_act_budgets_rec.requester_id := ozf_utility_pvt.get_resource_id(p_approver_id);
964 l_act_budgets_rec.request_currency := p_line_result_rec.currency_code;
965
966 -- Get fund info for price difference
967 -- get chargeback budget id FROM profile
968 IF p_price_diff_fund_id IS NULL THEN
969 ozf_utility_pvt.error_message('OZF_THRDPTY_BUDGET_ERR');
970 RAISE FND_API.g_exc_error;
971 ELSE
972 l_act_budgets_rec.parent_source_id := p_price_diff_fund_id;
973 END IF;
974
975 l_act_budgets_rec.parent_src_curr := OZF_ACTBUDGETS_PVT.get_object_currency (
976 'FUND'
977 ,l_act_budgets_rec.parent_source_id
978 ,l_return_status
979 );
980 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
981 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
982 END IF;
983 l_act_budgets_rec.justification := 'THIRD PARTY PRICE DIFF';
984 ELSE
985 l_act_budgets_rec.arc_act_budget_used_by := 'OFFR';
986 l_act_budgets_rec.budget_source_type := 'OFFR';
987
988 l_act_budgets_rec.request_currency := OZF_ACTBUDGETS_PVT.get_object_currency (
989 'OFFR'
990 ,l_ldets_tbl(k).list_header_id
991 ,l_return_status
992 );
993 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
994 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
995 END IF;
996 END IF;
997
998 -- get adjusted amount in budget currency
999 -- Problem in 115.9 is fixed. no need here.
1000 l_act_budgets_rec.request_amount := l_adjustment_rec.total_accepted_amount;
1001
1002 IF p_line_result_rec.currency_code <> l_act_budgets_rec.request_currency THEN
1003 -- get convert type
1004 OPEN OZF_RESALE_COMMON_PVT.g_exchange_rate_type_csr;
1005 FETCH OZF_RESALE_COMMON_PVT.g_exchange_rate_type_csr INTO l_exchange_type;
1006 CLOSE OZF_RESALE_COMMON_PVT.g_exchange_rate_type_csr;
1007
1008 OZF_UTILITY_PVT.convert_currency(
1009 p_from_currency => p_line_result_rec.currency_code
1010 ,p_to_currency => l_act_budgets_rec.request_currency
1011 ,p_conv_type => l_exchange_type
1012 ,p_conv_rate => FND_API.G_MISS_NUM
1013 ,p_conv_date => sysdate
1014 ,p_from_amount => l_act_budgets_rec.request_amount
1015 ,x_return_status => l_return_status
1016 ,x_to_amount => l_new_request_amount
1017 ,x_rate => l_rate
1018 );
1019 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1020 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1021 END IF;
1022 l_act_budgets_rec.request_amount := l_new_request_amount;
1023 END IF;
1024 IF OZF_DEBUG_LOW_ON THEN
1025 ozf_utility_PVT.debug_message('act budget: '||l_act_budgets_rec.request_amount);
1026 END IF;
1027
1028 -- Utilization always have different sign than the price adjustment
1029 l_adjustment_rec.calculated_amount := l_act_budgets_rec.request_amount;
1030 l_act_budgets_rec.request_amount := l_act_budgets_rec.request_amount * -1;
1031
1032 IF l_ldets_tbl(k).list_line_type_code = 'PLL' THEN
1033 l_act_budgets_rec.parent_src_apprvd_amt := l_act_budgets_rec.request_amount;
1034 IF p_line_result_rec.currency_code <> l_act_budgets_rec.parent_src_curr THEN
1035 -- get convert type
1036 OPEN OZF_RESALE_COMMON_PVT.g_exchange_rate_type_csr;
1037 FETCH OZF_RESALE_COMMON_PVT.g_exchange_rate_type_csr INTO l_exchange_type;
1038 CLOSE OZF_RESALE_COMMON_PVT.g_exchange_rate_type_csr;
1039
1040 OZF_UTILITY_PVT.convert_currency(
1041 p_FROM_currency => p_line_result_rec.currency_code
1042 ,p_to_currency => l_act_budgets_rec.parent_src_curr
1043 ,p_conv_type => l_exchange_type
1044 ,p_conv_rate => FND_API.G_MISS_NUM
1045 ,p_conv_date => sysdate
1046 ,p_FROM_amount => l_act_budgets_rec.parent_src_apprvd_amt
1047 ,x_return_status => l_return_status
1048 ,x_to_amount => l_new_request_amount
1049 ,x_rate => l_rate
1050 );
1051 IF l_return_status = FND_API.g_ret_sts_error THEN
1052 RAISE FND_API.g_exc_error;
1053 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1054 RAISE FND_API.g_exc_error;
1055 END IF;
1056 l_act_budgets_rec.parent_src_apprvd_amt := l_new_request_amount;
1057 END IF;
1058 END IF;
1059
1060 -- julou 5723309: create util for VO PBH line only
1061 OPEN c_offer_type(l_ldets_tbl(k).list_header_id);
1062 FETCH c_offer_type INTO l_offer_type;
1063 CLOSE c_offer_type;
1064
1065 IF OZF_DEBUG_LOW_ON THEN
1066 ozf_utility_pvt.debug_message(' JL offer_type: ' || l_offer_type);
1067 ozf_utility_pvt.debug_message(' JL list_line_type_code: ' || l_ldets_tbl(k).list_line_type_code);
1068 ozf_utility_pvt.debug_message(' JL calculated_amount: ' || l_adjustment_rec.calculated_amount);
1069 END IF;
1070
1071 l_to_create_utilization := (l_ldets_tbl(k).list_line_type_code = 'PLL' AND
1072 l_adjustment_rec.calculated_amount IS NOT NULL)
1073 OR
1074 (l_ldets_tbl(k).list_line_type_code = 'DIS' AND
1075 l_offer_type <> 'VOLUME_OFFER' AND
1076 l_adjustment_rec.calculated_amount IS NOT NULL)
1077 OR
1078 (l_ldets_tbl(k).list_line_type_code = 'PBH' AND
1079 --l_offer_type = 'VOLUME_OFFER' AND --multi-tier offer also stores effective accrual on PBH line
1080 l_adjustment_rec.calculated_amount IS NOT NULL);
1081 -- end julou 5723309
1082 /*
1083 l_to_create_utilization := (l_ldets_tbl(k).list_line_type_code = 'PLL' AND
1084 l_adjustment_rec.calculated_amount IS NOT NULL)
1085 OR
1086 (l_ldets_tbl(k).list_line_type_code = 'DIS' AND
1087 l_adjustment_rec.calculated_amount IS NOT NULL);
1088 */
1089 OZF_RESALE_COMMON_PVT.Create_Adj_and_Utilization(
1090 p_api_version => 1
1091 ,p_init_msg_list => FND_API.G_FALSE
1092 ,p_commit => FND_API.G_FALSE
1093 ,p_validation_level=> FND_API.G_VALID_LEVEL_FULL
1094 ,p_price_adj_rec => l_adjustment_rec
1095 ,p_act_budgets_rec => l_act_budgets_rec
1096 ,p_act_util_rec => l_act_util_rec
1097 ,p_to_create_utilization => l_to_create_utilization
1098 ,x_return_status => l_return_status
1099 ,x_msg_data => l_msg_data
1100 ,x_msg_count => l_msg_count
1101 );
1102 --IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1103 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1104 --END IF;
1105
1106 IF l_return_status = fnd_api.g_ret_sts_error THEN
1107 RAISE fnd_api.g_exc_error;
1108 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1109 RAISE fnd_api.g_exc_unexpected_error;
1110 END IF;
1111
1112 IF l_to_create_utilization AND
1113 l_act_budgets_rec.arc_act_budget_used_by = 'OFFR' THEN
1114
1115 IF p_caller_type = 'IFACE' THEN
1116 l_sales_transaction_rec.sold_from_cust_account_id := p_resale_line_int_rec.sold_from_cust_account_id;
1117 l_sales_transaction_rec.sold_to_cust_account_id := p_resale_line_int_rec.bill_to_cust_account_id;
1118 l_sales_transaction_rec.sold_to_party_id := p_resale_line_int_rec.bill_to_party_id;
1119 l_sales_transaction_rec.sold_to_party_site_id := p_resale_line_int_rec.bill_to_party_site_id;
1120 l_sales_transaction_rec.bill_to_site_use_id := p_resale_line_int_rec.bill_to_site_use_id;
1121 l_sales_transaction_rec.ship_to_site_use_id := p_resale_line_int_rec.ship_to_site_use_id;
1122 l_sales_transaction_rec.transaction_date := p_resale_line_int_rec.date_ordered;
1123 IF p_resale_line_int_rec.product_transfer_movement_type = 'TI' THEN
1124 l_sales_transaction_rec.transfer_type := 'IN';
1125 ELSIF p_resale_line_int_rec.product_transfer_movement_type = 'TO' THEN
1126 l_sales_transaction_rec.transfer_type := 'OUT';
1127 ELSIF p_resale_line_int_rec.product_transfer_movement_type = 'DC' THEN
1128 l_sales_transaction_rec.transfer_type := 'OUT';
1129 ELSIF p_resale_line_int_rec.product_transfer_movement_type = 'CD' THEN
1130 l_sales_transaction_rec.transfer_type := 'IN';
1131 END IF;
1132 l_sales_transaction_rec.quantity := p_resale_line_int_rec.quantity;
1133 l_sales_transaction_rec.uom_code := p_resale_line_int_rec.uom_code;
1134 -- l_sales_transaction_rec.amount := ABS(p_resale_line_int_rec.selling_price * p_resale_line_int_rec.quantity);
1135 l_sales_transaction_rec.amount := ABS(p_line_result_rec.unit_price * p_resale_line_int_rec.quantity);
1136 l_sales_transaction_rec.currency_code := p_resale_line_int_rec.currency_code;
1137 l_sales_transaction_rec.inventory_item_id := p_resale_line_int_rec.inventory_item_id;
1138 l_sales_transaction_rec.header_id := p_header_id;
1139 l_sales_transaction_rec.line_id := l_line_id;
1140 l_sales_transaction_rec.source_code := 'IS';
1141 ELSIF p_caller_type = 'RESALE' THEN
1142 l_sales_transaction_rec.sold_from_cust_account_id := p_resale_line_rec.sold_from_cust_account_id;
1143 l_sales_transaction_rec.sold_to_cust_account_id := p_resale_line_rec.bill_to_cust_account_id;
1144 l_sales_transaction_rec.sold_to_party_id := p_resale_line_rec.bill_to_party_id;
1145 l_sales_transaction_rec.sold_to_party_site_id := p_resale_line_rec.bill_to_party_site_id;
1146 l_sales_transaction_rec.bill_to_site_use_id := p_resale_line_rec.bill_to_site_use_id;
1147 l_sales_transaction_rec.ship_to_site_use_id := p_resale_line_rec.ship_to_site_use_id;
1148 l_sales_transaction_rec.transaction_date := p_resale_line_rec.date_ordered;
1149 IF p_resale_line_rec.product_transfer_movement_type = 'TI' THEN
1150 l_sales_transaction_rec.transfer_type := 'IN';
1151 ELSIF p_resale_line_rec.product_transfer_movement_type = 'TO' THEN
1152 l_sales_transaction_rec.transfer_type := 'OUT';
1153 ELSIF p_resale_line_rec.product_transfer_movement_type = 'DC' THEN
1154 l_sales_transaction_rec.transfer_type := 'OUT';
1155 ELSIF p_resale_line_rec.product_transfer_movement_type = 'CD' THEN
1156 l_sales_transaction_rec.transfer_type := 'IN';
1157 END IF;
1158 l_sales_transaction_rec.quantity := p_resale_line_rec.quantity;
1159 l_sales_transaction_rec.uom_code := p_resale_line_rec.uom_code;
1160 --l_sales_transaction_rec.amount := ABS(p_resale_line_rec.selling_price * p_resale_line_rec.quantity);
1161 -- changed by feliu on 12/13/06 since selling_price could be null.
1162 l_sales_transaction_rec.amount := ABS(p_line_result_rec.unit_price * p_resale_line_rec.quantity);
1163 l_sales_transaction_rec.currency_code := p_resale_line_rec.currency_code;
1164 l_sales_transaction_rec.inventory_item_id := p_resale_line_rec.inventory_item_id;
1165 l_sales_transaction_rec.header_id := p_header_id;
1166 l_sales_transaction_rec.line_id := l_line_id;
1167 l_sales_transaction_rec.source_code := 'IS';
1168 END IF;
1169
1170 OZF_VOLUME_CALCULATION_PUB.Create_Volume(
1171 p_init_msg_list => FND_API.g_false
1172 ,p_api_version => 1.0
1173 ,p_commit => FND_API.g_false
1174 ,x_return_status => l_return_status
1175 ,x_msg_count => l_msg_count
1176 ,x_msg_data => l_msg_data
1177 ,p_volume_detail_rec => l_sales_transaction_rec
1178 ,p_qp_list_header_id => l_ldets_tbl(k).list_header_id
1179 ,x_apply_discount => l_vol_offr_apply_discount
1180 );
1181 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1182 RAISE FND_API.G_EXC_ERROR;
1183 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1184 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1185 END IF;
1186
1187
1188 END IF;
1189 END IF; -- END line_type_code is the 'PLL' and price differ or in ('PHB','DIS')
1190 END IF; -- END is valid offer
1191 END IF; -- END accrual flag
1192 END IF; -- END list type
1193 END LOOP; -- END LOOP through ldets_lines
1194 END IF; -- END if ldets_line has nothing
1195
1196 x_return_status := l_return_status;
1197
1198 IF OZF_DEBUG_HIGH_ON THEN
1199 OZF_UTILITY_PVT.debug_message(p_message_text => l_full_name||': start');
1200 END IF;
1201
1202 EXCEPTION
1203 WHEN FND_API.G_EXC_ERROR THEN
1204 ROLLBACK TO PROC_ONE_LINE;
1205 x_return_status := FND_API.G_RET_STS_ERROR;
1206 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1207 ROLLBACK TO PROC_ONE_LINE;
1208 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1209 WHEN OTHERS THEN
1210 ROLLBACK TO PROC_ONE_LINE;
1211 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1212 END process_one_line;
1213
1214 ---------------------------------------------------------------------
1215 -- PROCEDURE
1216 -- process_pricing_result
1217 --
1218 -- PURPOSE
1219 -- This procedure process the pricing call result. It creates accruals based
1220 -- on the discount information.
1221 --
1222 -- PARAMETERS
1223 -- p_resale_batch_id IN NUMBER,
1224 -- p_line_tbl IN OZF_ORDER_PRICE_PVT.LINE_REC_TBL_TYPE,
1225 -- p_caller_type IN VARCHAR2,
1226 -- x_return_status OUT NOCOPY VARCHAR2
1227 --
1228 -- NOTES
1229 -- 1. Non-monetray accruals have not been considered. Should look INTO ldets.benefit_qty
1230 -- and ldets.benefit_uom for calculation.
1231 -- 2. We will not do third party accruals on tracing data
1232 --
1233 ---------------------------------------------------------------------
1234 PROCEDURE Process_Pricing_Result(
1235 p_resale_batch_id IN NUMBER,
1236 p_line_tbl IN OZF_ORDER_PRICE_PVT.LINE_REC_TBL_TYPE,
1237 p_caller_type IN VARCHAR2,
1238 x_return_status OUT NOCOPY VARCHAR2
1239 )
1240 IS
1241 l_api_name CONSTANT VARCHAR2(30) := 'Process_Pricing_Result';
1242 l_api_version CONSTANT NUMBER := 1.0;
1243 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
1244 ---
1245 l_msg_data VARCHAR2(2000);
1246 l_msg_count NUMBER;
1247 l_return_status VARCHAR2(30);
1248 --
1249 CURSOR order_identifiers_csr (p_id IN NUMBER) IS
1250 SELECT order_number
1251 , bill_to_cust_account_id
1252 , date_ordered
1253 FROM ozf_resale_lines_int_all
1254 WHERE resale_line_int_id = p_id;
1255
1256 CURSOR resale_info_csr (p_id IN NUMBER) IS
1257 SELECT resale_header_id
1258 FROM ozf_resale_lines
1259 WHERE resale_line_id = p_id;
1260
1261 l_order_number VARCHAR2(30);
1262 l_cust_account_id NUMBER;
1263 l_date_ordered DATE;
1264
1265 l_has_error BOOLEAN := FALSE;
1266 l_log_id NUMBER;
1267
1268 CURSOR resale_rec_csr (p_id IN NUMBER) IS
1269 SELECT *
1270 FROM ozf_resale_lines
1271 WHERE resale_line_id = p_id;
1272
1273 l_resale_int_rec OZF_RESALE_COMMON_PVT.g_interface_rec_csr%rowtype;
1274 l_resale_rec OZF_RESALE_LINES%rowtype;
1275
1276 l_header_id NUMBER;
1277 l_line_id NUMBER;
1278
1279 CURSOR exchange_rate_type_csr IS
1280 SELECT exchange_rate_type
1281 FROM ozf_sys_parameters;
1282
1283 l_default_exchange_type VARCHAR2(30);
1284 l_exchange_type VARCHAR2(30);
1285 l_exchange_date DATE;
1286 l_acctd_adj_unit_price NUMBER;
1287 l_acctd_selling_price NUMBER;
1288 l_rate NUMBER;
1289
1290 CURSOR func_currency_cd_csr IS
1291 SELECT gs.currency_code
1292 FROM gl_sets_of_books gs,
1293 ozf_sys_parameters osp
1294 WHERE gs.set_of_books_id = osp.set_of_books_id
1295 AND osp.org_id = MO_GLOBAL.GET_CURRENT_ORG_ID(); -- BUG 5058027
1296
1297 l_func_currency_code VARCHAR2(15);
1298
1299
1300 CURSOR dup_line_csr( p_id IN NUMBER
1301 , p_order_number IN VARCHAR2
1302 , p_cust_id IN NUMBER
1303 , p_date IN DATE
1304 ) IS
1305 SELECT 1
1306 FROM ozf_resale_lines_int_all
1307 WHERE resale_batch_id = p_id
1308 AND order_number = p_order_number
1309 AND bill_to_cust_account_id = p_cust_id
1310 AND date_ordered = p_date
1311 AND status_code = 'DUPLICATED';
1312
1313
1314 l_create_order_header BOOLEAN;
1315 l_dup_line_count NUMBER;
1316 l_inventory_tracking VARCHAR2(30);
1317
1318 CURSOR dup_header_csr( p_id IN NUMBER
1319 , p_order_number IN VARCHAR2
1320 , p_cust_id IN NUMBER
1321 , p_date IN DATE
1322 ) IS
1323 SELECT a.resale_header_id
1324 FROM ozf_resale_headers_all a
1325 , ozf_resale_lines_int_all b
1326 , ozf_resale_lines_all c
1327 WHERE b.resale_batch_id = p_id
1328 AND b.order_number = p_order_number
1329 AND b.bill_to_cust_account_id = p_cust_id
1330 AND b.date_ordered = p_date
1331 AND b.status_code = 'DUPLICATED'
1332 AND b.duplicated_line_id = c.resale_line_id
1333 AND c.resale_header_id = a.resale_header_id;
1334
1335 l_fund_id NUMBER:= G_PRC_DIFF_BUDGET; --fnd_profile.value('OZF_THRDPTY_PRCDIFF_BUDGET');
1336 l_id_type VARCHAR2(30);
1337
1338 CURSOR batch_info_csr (p_id IN NUMBER) IS
1339 SELECT partner_cust_account_id,
1340 partner_party_id,
1341 report_start_date,
1342 report_end_date,
1343 last_updated_by
1344 FROM ozf_resale_batches_all
1345 WHERE resale_batch_id = p_id;
1346
1347 l_partner_cust_account_id NUMBER;
1348 l_partner_party_id NUMBER;
1349 l_report_start_date DATE;
1350 l_report_end_date DATE;
1351 l_last_updated_by NUMBER(15);
1352 -- Bug 4380203 (+)
1353 l_inventory_level_valid BOOLEAN;
1354 -- Bug 4380203 (-)
1355 -- bug 6317120
1356 l_org_id NUMBER;
1357 -- end bug 6317120
1358 BEGIN
1359 -- Debug Message
1360 IF OZF_DEBUG_HIGH_ON THEN
1361 OZF_UTILITY_PVT.debug_message(p_message_text => l_full_name||': start');
1362 END IF;
1363
1364 x_return_status := FND_API.G_RET_STS_SUCCESS;
1365
1366 SAVEPOINT PROC_PRIC_RESULT;
1367
1368 OPEN batch_info_csr(p_resale_batch_id);
1369 FETCH batch_info_csr into l_partner_cust_account_id,
1370 l_partner_party_id,
1371 l_report_start_date,
1372 l_report_end_date,
1373 l_last_updated_by;
1374 CLOSE batch_info_csr;
1375
1376 IF l_fund_id is null THEN
1377 ozf_utility_pvt.error_message('OZF_THRDPTY_BUDGET_ERR');
1378 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' Please setup Profile OZF : Price Difference Budget before running Third Party Accrual.');
1379 RAISE FND_API.g_exc_error;
1380 END IF;
1381
1382 -- First check whether the order result collection EXISTS or not
1383 IF p_line_tbl.EXISTS(1) THEN
1384 -- get order identification
1385 IF p_caller_type = 'IFACE' THEN
1386 OPEN order_identifiers_csr(p_line_tbl(1).chargeback_int_id);
1387 FETCH order_identifiers_csr INTO l_order_number,
1388 l_cust_account_id,
1389 l_date_ordered;
1390 CLOSE order_identifiers_csr;
1391 ELSE
1392 OPEN resale_info_csr(p_line_tbl(1).chargeback_int_id);
1393 FETCH resale_info_csr INTO l_header_id;
1394 CLOSE resale_info_csr;
1395 END IF;
1396
1397 -- LOOP through the result to find if there is an error in the result.
1398 FOR i in 1..p_line_tbl.LAST LOOP
1399 l_has_error := p_line_tbl(i).pricing_status_code <> QP_PREQ_PUB.G_STATUS_NEW AND
1400 p_line_tbl(i).pricing_status_code <> QP_PREQ_PUB.G_STATUS_UNCHANGED AND
1401 p_line_tbl(i).pricing_status_code <> QP_PREQ_PUB.G_STATUS_UPDATED;
1402
1403 EXIT WHEN l_has_error;
1404 END LOOP;
1405
1406 IF l_has_error THEN
1407 -- IF there is an error for a line or lines, we need to UPDATE the whole order as error;
1408 -- nothing to UPDATE if it's FROM line.
1409 IF p_caller_type = 'IFACE' THEN
1410 BEGIN
1411 UPDATE ozf_resale_lines_int_all
1412 SET status_code = 'DISPUTED'
1413 WHERE status_code = 'OPEN'
1414 AND order_NUMBER = l_order_number
1415 AND bill_to_cust_account_id = l_cust_account_id
1416 AND date_ordered = l_date_ordered
1417 AND resale_batch_id = p_resale_batch_id;
1418 EXCEPTION
1419 WHEN OTHERS THEN
1420 ozf_utility_pvt.error_message('OZF_UPD_RESALE_INT_WRG');
1421 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1422 END;
1423 l_id_type := 'IFACE';
1424 ELSE
1425 l_id_type := 'LINE';
1426 END IF;
1427
1428 FOR i in 1..p_line_tbl.LAST LOOP
1429 BEGIN
1430 OPEN OZF_RESALE_COMMON_PVT.g_log_id_csr;
1431 FETCH OZF_RESALE_COMMON_PVT.g_log_id_csr INTO l_log_id;
1432 CLOSE OZF_RESALE_COMMON_PVT.g_log_id_csr;
1433
1434 -- julou bug 6317120. get org_id from table
1435 IF l_id_type = 'LINE' THEN
1436 OPEN OZF_RESALE_COMMON_PVT.gc_line_org_id(p_line_tbl(i).chargeback_int_id);
1437 FETCH OZF_RESALE_COMMON_PVT.gc_line_org_id INTO l_org_id;
1438 CLOSE OZF_RESALE_COMMON_PVT.gc_line_org_id;
1439 ELSIF l_id_type = 'IFACE' THEN
1440 OPEN OZF_RESALE_COMMON_PVT.gc_iface_org_id(p_line_tbl(i).chargeback_int_id);
1441 FETCH OZF_RESALE_COMMON_PVT.gc_iface_org_id INTO l_org_id;
1442 CLOSE OZF_RESALE_COMMON_PVT.gc_iface_org_id;
1443 END IF;
1444
1445 OZF_RESALE_LOGS_PKG.Insert_Row(
1446 px_resale_log_id => l_log_id,
1447 p_resale_id => p_line_tbl(i).chargeback_int_id,
1448 p_resale_id_type => l_id_type,
1449 p_error_code => p_line_tbl(i).pricing_status_code,
1450 p_error_message => p_line_tbl(i).pricing_status_text, p_column_name => NULL,
1451 p_column_value => NULL,
1452 --px_org_id => OZF_RESALE_COMMON_PVT.g_org_id
1453 px_org_id => l_org_id
1454 );
1455 EXCEPTION
1456 WHEN OTHERS THEN
1457 ozf_utility_pvt.error_message('OZF_INS_RESALE_LOG_WRG');
1458 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1459 END;
1460 END LOOP;
1461 ELSE
1462 -- There is no error in the resulting. We need to process the result one by one.
1463 -- Since there is no time overlap between data process and payment initiatioin, I will
1464 -- not check duplicates again
1465
1466 IF p_caller_type = 'IFACE' THEN
1467 --We need to check create an order header first.
1468 OPEN dup_line_csr( p_resale_batch_id
1469 , l_order_number
1470 , l_cust_account_id
1471 , l_date_ordered
1472 );
1473 FETCH dup_line_csr INTO l_dup_line_count;
1474 CLOSE dup_line_csr;
1475
1476 -- Here, I assume if a line is the duplicate of another line, then they share
1477 -- the same order header. Hence all order with this duplicated line share the
1478 -- the same order with the oringinal lines.
1479
1480 l_create_order_header := l_dup_line_count IS NULL;
1481
1482 -- Check whether there is a need to do inventory_verification
1483 OPEN OZF_RESALE_COMMON_PVT.g_inventory_tracking_csr;
1484 FETCH OZF_RESALE_COMMON_PVT.g_inventory_tracking_csr INTO l_inventory_tracking;
1485 CLOSE OZF_RESALE_COMMON_PVT.g_inventory_tracking_csr;
1486
1487 IF l_inventory_tracking = 'T' THEN
1488 OZF_SALES_TRANSACTIONS_PVT.Initiate_Inventory_tmp (
1489 p_api_version => 1.0
1490 ,p_init_msg_list => FND_API.G_FALSE
1491 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
1492 ,p_resale_batch_id => p_resale_batch_id
1493 ,p_start_date => l_report_start_date
1494 ,p_end_date => l_report_end_date
1495 ,x_return_status => l_return_status
1496 ,x_msg_count => l_msg_count
1497 ,x_msg_data => l_msg_data
1498 );
1499 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1500 OZF_UTILITY_PVT.error_message('OZF_RESALE_INIT_INV_TMP_ERR');
1501 RAISE FND_API.G_EXC_ERROR;
1502 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1503 OZF_UTILITY_PVT.error_message('OZF_RESALE_INIT_INV_TMP_ERR');
1504 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1505 END IF;
1506 /*
1507 OZF_SALES_TRANSACTIONS_PVT.Initiate_Inventory_tmp (
1508 p_api_version => 1.0
1509 ,p_init_msg_list => FND_API.G_FALSE
1510 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
1511 ,p_party_id => l_partner_party_id
1512 ,p_start_date => l_report_start_date
1513 ,x_return_status => l_return_status
1514 ,x_msg_count => l_msg_count
1515 ,x_msg_data => l_msg_data
1516 );
1517 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1518 ozf_utility_pvt.error_message('OZF_RESALE_INIT_INV_TMP_ERR');
1519 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1520 END IF;
1521 */
1522 -- Bug
1523 END IF;
1524
1525 -- get functional currency code and convertion type
1526 OPEN func_currency_cd_csr;
1527 FETCH func_currency_cd_csr INTO l_func_currency_code;
1528 CLOSE func_currency_cd_csr;
1529
1530 OPEN exchange_rate_type_csr;
1531 FETCH exchange_rate_type_csr INTO l_default_exchange_type;
1532 CLOSE exchange_rate_type_csr;
1533 END IF;
1534
1535 -- For each chargeback of the line, we will update the line and
1536 -- create an record in the ozf_resale_adjustment_all.
1537 For i in 1..p_line_tbl.LAST LOOP
1538 IF p_line_tbl(i).line_type_code = 'LINE' THEN
1539
1540 IF p_caller_type = 'IFACE' THEN
1541 -- Process interface data
1542
1543 OPEN OZF_RESALE_COMMON_PVT.g_interface_rec_csr(p_line_tbl(i).chargeback_int_id);
1544 FETCH OZF_RESALE_COMMON_PVT.g_interface_rec_csr INTO l_resale_int_rec;
1545 CLOSE OZF_RESALE_COMMON_PVT.g_interface_rec_csr;
1546
1547 -- Bug 4380203 (+)
1548
1549 -- Check inventory level FOR thIS order.
1550 -- If inventory level IS lower than the asked, then there IS no need to
1551 -- continue processing
1552 IF l_inventory_tracking = 'T' THEN
1553 IF OZF_DEBUG_LOW_ON THEN
1554 OZF_UTILITY_PVT.debug_message(l_api_name||' >> Need inventory tracking' );
1555 END IF;
1556
1557 -- Check inventory level first
1558 OZF_SALES_TRANSACTIONS_PVT.Validate_Inventory_level (
1559 p_api_version => 1.0
1560 ,p_init_msg_list => FND_API.G_FALSE
1561 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
1562 ,p_line_int_rec => l_resale_int_rec
1563 ,x_valid => l_inventory_level_valid
1564 ,x_return_status => l_return_status
1565 ,x_msg_count => l_msg_count
1566 ,x_msg_data => l_msg_data
1567 );
1568
1569 IF NOT l_inventory_level_valid THEN
1570 IF OZF_DEBUG_LOW_ON THEN
1571 OZF_UTILITY_PVT.debug_message(l_api_name||' >> Did not pass inventory checking');
1572 END IF;
1573 --
1574 OZF_RESALE_COMMON_PVT.Insert_Resale_Log (
1575 p_id_value => l_resale_int_rec.resale_line_int_id,
1576 p_id_type => 'IFACE',
1577 p_error_code => 'OZF_RESALE_INV_LEVEL_ERROR',
1578 p_column_name => NULL,
1579 p_column_value => NULL,
1580 x_return_status => l_return_status
1581 );
1582 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1583 RAISE FND_API.g_exc_error;
1584 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1585 RAISE FND_API.g_exc_unexpected_error;
1586 END IF;
1587
1588 -- Delink resale interface line from batch
1589 UPDATE ozf_resale_lines_int_all
1590 SET resale_batch_id = null,
1591 request_id = null,
1592 status_code = 'DISPUTED',
1593 dispute_code = 'OZF_LT_INVT'
1594 WHERE resale_line_int_id = l_resale_int_rec.resale_line_int_id;
1595
1596 RAISE FND_API.g_exc_error;
1597
1598 -- Batch status won't be set to DISPUTED.
1599 -- TP Accrual Process will still continue.
1600 ELSE
1601 IF OZF_DEBUG_LOW_ON THEN
1602 OZF_UTILITY_PVT.debug_message(l_api_name||' >> Pass inventory validation');
1603 END IF;
1604 END IF;
1605 --
1606 END IF;
1607 -- Bug 4380203 (-)
1608
1609 IF i = 1 THEN
1610 -- I need to create a header
1611 IF l_create_order_header THEN
1612 OZF_RESALE_COMMON_PVT.Insert_Resale_Header(
1613 p_api_version => 1
1614 ,p_init_msg_list => FND_API.G_FALSE
1615 ,p_commit => FND_API.G_FALSE
1616 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
1617 ,p_line_int_rec => l_resale_int_rec
1618 ,x_header_id => l_header_id
1619 ,x_return_status => l_return_status
1620 ,x_msg_data => l_msg_data
1621 ,x_msg_count => l_msg_count
1622 );
1623 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1624 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1625 END IF;
1626 ELSE
1627 -- get header id of the dup lines
1628 OPEN dup_header_csr( p_resale_batch_id
1629 , l_order_number
1630 , l_cust_account_id
1631 , l_date_ordered
1632 );
1633 FETCH dup_header_csr INTO l_header_id;
1634 CLOSE dup_header_csr;
1635
1636 END IF;
1637 END IF;
1638
1639 -- I will convert the adjusted amount to functional currency code.
1640 l_exchange_type := l_resale_int_rec.exchange_rate_type;
1641 l_exchange_date := l_resale_int_rec.exchange_rate_date;
1642 l_rate := l_resale_int_rec.exchange_rate;
1643
1644 IF l_func_currency_code <> l_resale_int_rec.currency_code THEN
1645 IF l_rate IS NULL THEN
1646 IF l_exchange_type IS NULL THEN
1647 l_exchange_type := l_default_exchange_type;
1648 END IF;
1649
1650 IF l_exchange_type IS NULL THEN
1651 ozf_utility_pvt.error_message('OZF_CLAIM_CONTYPE_MISSING');
1652 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1653 END IF;
1654
1655 IF l_exchange_date IS NULL THEN
1656 l_exchange_date := sysdate;
1657 END IF;
1658
1659 IF OZF_DEBUG_LOW_ON THEN
1660 OZF_UTILITY_PVT.debug_message(l_api_name||' >> Convert Currency <<');
1661 OZF_UTILITY_PVT.debug_message(l_api_name||' >> from currency :' || l_resale_int_rec.currency_code);
1662 OZF_UTILITY_PVT.debug_message(l_api_name||' >> to currency :' || l_func_currency_code);
1663 OZF_UTILITY_PVT.debug_message(l_api_name||' >> rate :' || l_rate);
1664 OZF_UTILITY_PVT.debug_message(l_api_name||' >> exchange date :' || l_exchange_date);
1665 OZF_UTILITY_PVT.debug_message(l_api_name||' >> exchange type :' || l_exchange_type);
1666 END IF;
1667
1668 OZF_UTILITY_PVT.Convert_Currency(
1669 p_from_currency => l_resale_int_rec.currency_code
1670 ,p_to_currency => l_func_currency_code
1671 ,p_conv_type => l_exchange_type
1672 ,p_conv_rate => l_rate
1673 ,p_conv_date => l_exchange_date
1674 ,p_from_amount => p_line_tbl(i).adjusted_unit_price
1675 ,x_return_status => l_return_status
1676 ,x_to_amount => l_acctd_adj_unit_price
1677 ,x_rate => l_rate
1678 );
1679 IF l_return_status = FND_API.g_ret_sts_error THEN
1680 RAISE FND_API.g_exc_error;
1681 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1682 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1683 END IF;
1684
1685 OZF_UTILITY_PVT.Convert_Currency(
1686 p_from_currency => l_resale_int_rec.currency_code
1687 ,p_to_currency => l_func_currency_code
1688 ,p_conv_type => l_exchange_type
1689 ,p_conv_rate => l_rate
1690 ,p_conv_date => l_exchange_date
1691 ,p_FROM_amount => l_resale_int_rec.selling_price
1692 ,x_return_status => l_return_status
1693 ,x_to_amount => l_acctd_selling_price
1694 ,x_rate => l_rate);
1695 IF l_return_status = FND_API.g_ret_sts_error THEN
1696 RAISE FND_API.g_exc_error;
1697 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1698 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1699 END IF;
1700 ELSE
1701 l_acctd_adj_unit_price := OZF_UTILITY_PVT.CurrRound(p_line_tbl(i).adjusted_unit_price*l_rate, l_func_currency_code);
1702 l_acctd_selling_price := OZF_UTILITY_PVT.CurrRound(l_resale_int_rec.selling_price*l_rate, l_func_currency_code);
1703 END IF;
1704 ELSE
1705 l_rate := 1;
1706 l_acctd_adj_unit_price := p_line_tbl(i).adjusted_unit_price;
1707 l_acctd_selling_price := l_resale_int_rec.selling_price;
1708 END IF;
1709 ELSE
1710 -- Process Resale Data
1711 l_inventory_tracking := 'F';
1712 -- get resale_rec
1713 OPEN resale_rec_csr(p_line_tbl(i).chargeback_int_id);
1714 FETCH resale_rec_csr INTO l_resale_rec;
1715 CLOSE resale_rec_csr;
1716 END IF;
1717
1718 Process_One_Line(
1719 p_resale_line_int_rec => l_resale_int_rec,
1720 p_resale_line_rec => l_resale_rec,
1721 p_line_result_rec => p_line_tbl(i),
1722 p_header_id => l_header_id,
1723 p_resale_batch_id => p_resale_batch_id,
1724 p_inventory_tracking => l_inventory_tracking = 'T',
1725 p_price_diff_fund_id => l_fund_id,
1726 p_caller_type => p_caller_type,
1727 p_approver_id => l_last_updated_by,
1728 x_return_status => l_return_status
1729 );
1730 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1731 -- drop this line from batch if it is from interface
1732 IF p_caller_type = 'IFACE' THEN
1733 BEGIN
1734 UPDATE ozf_resale_lines_int_all
1735 SET resale_batch_id = NULL,
1736 request_id = NULL,
1737 status_code = 'DISPUTED',
1738 dispute_code = 'OZF_PRIC_RESULT_ERR'
1739 WHERE resale_line_int_id = p_line_tbl(i).chargeback_int_id;
1740 EXCEPTION
1741 WHEN OTHERS THEN
1742 ozf_utility_pvt.error_message('OZF_UPD_RESALE_INT_WRG');
1743 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1744 END;
1745 END IF;
1746 ELSE
1747 -- CLOSE THIS LINE If it is from interface
1748 IF p_caller_type = 'IFACE' THEN
1749 BEGIN
1750 UPDATE ozf_resale_lines_int_all
1751 SET status_code= 'CLOSED'
1752 WHERE resale_line_int_id = p_line_tbl(i).chargeback_int_id;
1753 EXCEPTION
1754 WHEN OTHERS THEN
1755 ozf_utility_pvt.error_message('OZF_UPD_RESALE_INT_WRG');
1756 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1757 END;
1758 END IF;
1759 END IF;
1760 END IF; -- END if current record is a line
1761 END LOOP; -- END LOOP through lines
1762 END IF; -- END of checking error
1763 END IF; -- END of EXISTS
1764
1765 x_return_status := l_return_status;
1766
1767 IF OZF_DEBUG_HIGH_ON THEN
1768 OZF_UTILITY_PVT.debug_message(p_message_text => l_full_name||': end');
1769 END IF;
1770
1771 EXCEPTION
1772 WHEN FND_API.G_EXC_ERROR THEN
1773 ROLLBACK TO PROC_PRIC_RESULT;
1774 x_return_status := FND_API.G_RET_STS_ERROR;
1775 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1776 ROLLBACK TO PROC_PRIC_RESULT;
1777 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1778 WHEN OTHERS THEN
1779 ROLLBACK TO PROC_PRIC_RESULT;
1780 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1781 END process_pricing_result;
1782
1783 ---------------------------------------------------------------------
1784 -- PROCEDURE
1785 -- Process_Resale_Order
1786 --
1787 -- PURPOSE
1788 -- Process resale batch information. Reads date FROM ozf_reasle_lines table.
1789 --
1790 -- PARAMETERS
1791 --
1792 -- NOTES
1793 --
1794 ---------------------------------------------------------------------
1795 PROCEDURE Process_Resale_Order
1796 (
1797 p_api_version IN NUMBER
1798 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
1799 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
1800 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
1801 ,p_resale_batch_id IN NUMBER
1802 ,x_return_status OUT NOCOPY VARCHAR2
1803 ,x_msg_data OUT NOCOPY VARCHAR2
1804 ,x_msg_count OUT NOCOPY NUMBER
1805 )IS
1806 l_api_name CONSTANT VARCHAR2(30) := 'Process_Resale_Order';
1807 l_api_version CONSTANT NUMBER := 1.0;
1808 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
1809 --
1810 l_return_status VARCHAR2(30);
1811 l_msg_data VARCHAR2(2000);
1812 l_msg_count NUMBER;
1813
1814 CURSOR header_id_csr IS
1815 SELECT a.resale_header_id
1816 , a.header_attribute_category
1817 , a.header_attribute1
1818 , a.header_attribute2
1819 , a.header_attribute3
1820 , a.header_attribute4
1821 , a.header_attribute5
1822 , a.header_attribute6
1823 , a.header_attribute7
1824 , a.header_attribute8
1825 , a.header_attribute9
1826 , a.header_attribute10
1827 , a.header_attribute11
1828 , a.header_attribute12
1829 , a.header_attribute13
1830 , a.header_attribute14
1831 , a.header_attribute15
1832 FROM ozf_resale_headers_all a
1833 , ozf_resale_lines_all b
1834 , ozf_resale_batch_line_maps_all c
1835 WHERE a.resale_header_id = b.resale_header_id
1836 AND b.resale_line_id = c.resale_line_id
1837 AND c.resale_batch_id = p_resale_batch_id;
1838
1839 TYPE header_id_tbl_type is TABLE OF header_id_csr%rowtype INDEX BY binary_integer;
1840 l_header_id_tbl header_id_tbl_type;
1841
1842 CURSOR order_header_csr(p_header_id in NUMBER) IS
1843 SELECT *
1844 FROM ozf_resale_headers
1845 WHERE resale_header_id = p_header_id;
1846
1847 l_header_rec order_header_csr%rowtype;
1848
1849 CURSOR order_set_csr(p_header_id in NUMBER) IS
1850 SELECT *
1851 FROM ozf_resale_lines
1852 WHERE resale_header_id = p_header_id;
1853
1854 TYPE resale_lines_tbl_type is TABLE OF order_set_csr%rowtype INDEX BY binary_integer;
1855 l_order_set_tbl resale_lines_tbl_type;
1856
1857 l_control_rec QP_PREQ_GRP.CONTROL_RECORD_TYPE;
1858
1859 l_line_tbl OZF_ORDER_PRICE_PVT.LINE_REC_TBL_TYPE;
1860 l_ldets_tbl OZF_ORDER_PRICE_PVT.LDETS_TBL_TYPE;
1861 l_related_lines_tbl OZF_ORDER_PRICE_PVT.RLTD_LINE_TBL_TYPE;
1862
1863 p NUMBER;
1864 k NUMBER;
1865
1866 l_log_id NUMBER;
1867
1868 l_temp_count NUMBER;
1869 l_temp_data VARCHAR2(2000);
1870 l_price_flag VARCHAR2(1) := NULL;
1871
1872 --mkothari 13-dec-2006
1873 l_list_price_override_flag VARCHAR2(1) := NULL;
1874
1875 l_accrual_on_selling VARCHAR2(3);
1876 l_default_price_list_id NUMBER;
1877 -- bug 6317120
1878 l_org_id NUMBER;
1879 -- end bug 6317120
1880 BEGIN
1881 -- Standard BEGIN of API savepoint
1882 SAVEPOINT Process_Resale_Order;
1883 -- Standard call to check for call compatibility.
1884 IF NOT FND_API.Compatible_API_Call (
1885 l_api_version,
1886 p_api_version,
1887 l_api_name,
1888 G_PKG_NAME)
1889 THEN
1890 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1891 END IF;
1892
1893 --Initialize message list if p_init_msg_list is TRUE.
1894 IF FND_API.To_Boolean (p_init_msg_list) THEN
1895 FND_MSG_PUB.initialize;
1896 END IF;
1897
1898 -- Debug Message
1899 IF OZF_DEBUG_HIGH_ON THEN
1900 OZF_UTILITY_PVT.debug_message(p_message_text => l_full_name||': Start');
1901 END IF;
1902
1903 -- Initialize API return status to sucess
1904 x_return_status := FND_API.G_RET_STS_SUCCESS;
1905
1906 -- We need to UPDATE the order FROM indirect customers so that they're not to be included in the
1907 -- pricing simulation;
1908
1909 -- Get profile value for price list
1910 l_default_price_list_id := G_TP_DEFAULT_PRICE_LIST; --fnd_profile.value('OZF_TP_ACCRUAL_PRICE_LIST');
1911
1912 -- Define control rec
1913 -- setup pricing_event based on purpose code and profile
1914 -- privcing_event is based on profile
1915
1916 l_control_rec.pricing_event := fnd_profile.value('OZF_PRICING_SIMULATION_EVENT');
1917 IF l_control_rec.pricing_event is NULL THEN
1918 IF OZF_DEBUG_LOW_ON THEN
1919 ozf_utility_PVT.debug_message('pricing event default');
1920 END IF;
1921 l_control_rec.pricing_event := 'BATCH,BOOK,SHIP';
1922 ELSE
1923 IF l_control_rec.pricing_event = 'BATCH' THEN
1924 l_control_rec.pricing_event := 'BATCH';
1925 ELSIF l_control_rec.pricing_event = 'BOOK' THEN
1926 l_control_rec.pricing_event := 'BATCH,BOOK';
1927 ELSIF l_control_rec.pricing_event = 'SHIP' THEN
1928 l_control_rec.pricing_event := 'BATCH,BOOK,SHIP';
1929 END IF;
1930 END IF;
1931
1932 IF OZF_DEBUG_LOW_ON THEN
1933 ozf_utility_PVT.debug_message('Event:' ||l_control_rec.pricing_event );
1934 END IF;
1935
1936 l_control_rec.calculate_flag := 'Y';
1937 l_control_rec.simulation_flag := 'Y';
1938 l_control_rec.source_order_amount_flag := 'Y';
1939 l_control_rec.GSA_CHECK_FLAG := 'N';
1940 l_control_rec.GSA_DUP_CHECK_FLAG := 'N';
1941 l_control_rec.TEMP_TABLE_INSERT_FLAG := 'N';
1942
1943 IF l_header_id_tbl.EXISTS(1) THEN
1944 l_header_id_tbl.DELETE;
1945 END IF;
1946 --p := 1;
1947 OPEN header_id_csr;
1948 FETCH header_id_csr BULK COLLECT INTO l_header_id_tbl;
1949 -- LOOP
1950 -- FETCH header_id_csr INTO l_header_id_tbl(p);
1951 -- EXIT when header_id_csr%notfound;
1952 -- p:= p+1;
1953 -- END LOOP;
1954 CLOSE header_id_csr;
1955
1956 IF l_header_id_tbl.EXISTS(1) THEN
1957 -- setup price_flag based on profile
1958 l_accrual_on_selling := G_ACCRUAL_ON_SELLING; --fnd_profile.value('OZF_ACC_ON_SELLING_PRICE');
1959
1960 -- If this profile is not set, we default the value to 'N'
1961
1962 IF l_accrual_on_selling is NULL THEN
1963 l_accrual_on_selling := 'N';
1964 END IF;
1965
1966 -- convert the value of the profile to proper price flag value
1967 IF l_accrual_on_selling = 'Y' THEN
1968 --l_price_flag := 'G'; -- 'G' is not implemented in QP -- mkothari
1969
1970 --mkothari 13-dec-2006
1971 l_price_flag := 'Y';
1972 l_list_price_override_flag := 'Y';
1973
1974 ELSE
1975 l_price_flag := 'Y';
1976 END IF;
1977 IF OZF_DEBUG_LOW_ON THEN
1978 ozf_utility_PVT.debug_message('Price flag:' ||l_price_flag );
1979 ozf_utility_PVT.debug_message('List Price Override Flag:' ||l_list_price_override_flag);
1980 END IF;
1981
1982 For i in 1..l_header_id_tbl.LAST
1983 LOOP
1984
1985 IF l_header_id_tbl(i).resale_header_id is not NULL THEN
1986
1987 QP_Price_Request_Context.Set_Request_Id;
1988
1989 IF OZF_DEBUG_LOW_ON THEN
1990 ozf_utility_PVT.debug_message('/*--- Processing order for order NUMBER:'||l_header_id_tbl(i).resale_header_id||'---*/');
1991 END IF;
1992 -- Before start process, clean up the data structures if necessary.
1993 IF l_order_set_tbl.EXISTS(1) THEN l_order_set_tbl.DELETE; END IF;
1994 IF l_line_tbl.EXISTS(1) THEN l_line_tbl.DELETE; END IF;
1995 IF l_ldets_tbl.EXISTS(1) THEN l_ldets_tbl.DELETE; END IF;
1996 IF l_related_lines_tbl.EXISTS(1) THEN l_related_lines_tbl.DELETE; END IF;
1997 IF OZF_ORDER_PRICE_PVT.G_LINE_REC_TBL.EXISTS(1) THEN OZF_ORDER_PRICE_PVT.G_LINE_REC_TBL.DELETE; END IF;
1998 IF OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL.EXISTS(1) THEN OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL.DELETE; END IF;
1999
2000 -- get order_header_rec
2001 OPEN order_header_csr(l_header_id_tbl(i).resale_header_id);
2002 FETCH order_header_csr INTO l_header_rec;
2003 CLOSE order_header_csr;
2004
2005 --k:=1;
2006 OPEN order_set_csr(l_header_id_tbl(i).resale_header_id);
2007 FETCH order_set_csr BULK COLLECT INTO l_order_set_tbl;
2008 --LOOP
2009 -- FETCH order_set_csr INTO l_order_set_tbl(k);
2010 -- EXIT when order_set_csr%notfound;
2011 -- k:=k+1;
2012 --END LOOP;
2013 CLOSE order_set_csr;
2014
2015 IF OZF_DEBUG_LOW_ON THEN
2016 ozf_utility_PVT.debug_message('after order set'||l_order_set_tbl.LAST);
2017 END IF;
2018 IF l_order_set_tbl.exists(1) THEN
2019 For J in 1..l_order_set_tbl.LAST
2020 LOOP
2021
2022 -- ???????? Purge the any error message that might be there. Rethink this
2023 -- OK for now
2024 BEGIN
2025 DELETE FROM ozf_resale_logs
2026 WHERE resale_id = l_order_set_tbl(J).resale_line_id
2027 AND resale_id_type = OZF_RESALE_COMMON_PVT.G_ID_TYPE_LINE;
2028 EXCEPTION
2029 WHEN OTHERS THEN
2030 ozf_utility_pvt.error_message('OZF_DEL_RESALE_LOG_WRG');
2031 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2032 END;
2033
2034 IF OZF_DEBUG_LOW_ON THEN
2035 ozf_utility_PVT.debug_message(l_api_name||'>> building order line for resale line id: '||l_order_set_tbl(j).resale_line_id);
2036 END IF;
2037
2038 -- INSERT INTO l_line_tbl
2039 l_line_tbl(j).line_index := j;
2040 l_line_tbl(j).line_id := l_order_set_tbl(j).resale_line_id;
2041 l_line_tbl(j).line_type_code := OZF_ORDER_PRICE_PVT.G_ORDER_LINE_TYPE;
2042 l_line_tbl(j).pricing_effective_date := l_order_set_tbl(j).date_ordered;
2043 l_line_tbl(j).active_date_first := l_order_set_tbl(j).date_ordered;
2044 l_line_tbl(j).active_date_first_type := 'ORD';
2045 l_line_tbl(j).active_date_second := l_order_set_tbl(j).date_shipped;
2046 l_line_tbl(j).active_date_second_type := 'SHIP';
2047 l_line_tbl(j).line_quantity := ABS(l_order_set_tbl(j).quantity); -- BUG 4581928
2048 l_line_tbl(j).line_uom_code := l_order_set_tbl(j).uom_code;
2049 l_line_tbl(j).request_type_code := 'ONT';
2050 -- Pricing might be able to default it
2051
2052 --mkothari 13-dec-2006
2053 --IF l_price_flag ='G' THEN
2054 IF l_list_price_override_flag = 'Y' THEN
2055 l_line_tbl(j).priced_quantity := ABS(l_order_set_tbl(j).quantity); -- BUG 4581928
2056 l_line_tbl(j).priced_uom_code := l_order_set_tbl(j).uom_code;
2057 l_line_tbl(j).unit_price := l_order_set_tbl(j).selling_price;
2058 END IF;
2059 l_line_tbl(j).currency_code := l_order_set_tbl(j).currency_code;
2060 IF l_header_rec.price_list_id IS NOT NULL THEN
2061 l_line_tbl(j).price_list_id := l_header_rec.price_list_id;
2062 ELSE
2063 l_line_tbl(j).price_list_id := l_default_price_list_id;
2064 END IF;
2065 l_line_tbl(j).price_flag := l_price_flag;
2066 --mkothari 13-dec-2006
2067 l_line_tbl(j).list_price_override_flag := l_list_price_override_flag;
2068
2069 l_line_tbl(j).pricing_status_code := QP_PREQ_GRP.G_STATUS_UNCHANGED;
2070 l_line_tbl(j).chargeback_int_id := l_order_set_tbl(j).resale_line_id;
2071 l_line_tbl(j).resale_table_type := 'LINE'; -- bug 5360598
2072 -- l_line_tbl(j).UNIT_PRICE := NULL;
2073 -- l_line_tbl(j).PERCENT_PRICE := NULL;
2074 -- l_line_tbl(j).UOM_QUANTITY := NULL;
2075 -- l_line_tbl(j).ADJUSTED_UNIT_PRICE := NULL;
2076 -- l_line_tbl(j).UPD_ADJUSTED_UNIT_PRICE NUMBER:= FND_API.G_MISS_NUM,
2077 -- l_line_tbl(j).PROCESSED_FLAG VARCHAR2(1):= FND_API.G_MISS_CHAR,
2078 -- l_line_tbl(j).PROCESSING_ORDER := NULL;
2079 -- l_line_tbl(j).PRICING_STATUS_TEXT := NULL;
2080 -- l_line_tbl(j).ROUNDING_FLAG := NULL;
2081 -- l_line_tbl(j).ROUNDING_FACTOR := NULL;
2082 -- l_line_tbl(j).QUALIFIERS_EXIST_FLAG := NULL;
2083 -- l_line_tbl(j).PRICING_ATTRS_EXIST_FLAG := NULL;
2084 -- l_line_tbl(j).PL_VALIDATED_FLAG := NULL;
2085 -- l_line_tbl(j).PRICE_REQUEST_CODE := NULL;
2086 -- l_line_tbl(j).USAGE_PRICING_TYPE := NULL;
2087 -- l_line_tbl(j).LINE_CATEGORY := NULL;
2088
2089
2090 -- populate the order_price global line arrary
2091 -- Here I only populate the values of the qualifiers for ONT.
2092 -- The real global structure will be populate in ozf_order_price_pvt.
2093 -- And it's value can be change in OZF_CHARGEBACK_ATTRMAP_PUB
2094
2095 OZF_ORDER_PRICE_PVT.G_LINE_REC_TBL(j).header_id := l_order_set_tbl.LAST + 1;
2096 OZF_ORDER_PRICE_PVT.G_LINE_REC_TBL(j).line_id := l_order_set_tbl(j).resale_line_id;
2097 OZF_ORDER_PRICE_PVT.G_LINE_REC_TBL(j).line_type_id := l_header_rec.order_type_id;
2098 OZF_ORDER_PRICE_PVT.G_LINE_REC_TBL(j).inventory_item_id := l_order_set_tbl(j).inventory_item_id;
2099 OZF_ORDER_PRICE_PVT.G_LINE_REC_TBL(j).ordered_quantity := ABS(l_order_set_tbl(j).quantity);
2100 OZF_ORDER_PRICE_PVT.G_LINE_REC_TBL(j).order_quantity_uom := l_order_set_tbl(j).uom_code;
2101 OZF_ORDER_PRICE_PVT.G_LINE_REC_TBL(j).invoice_to_org_id := l_order_set_tbl(j).bill_to_site_use_id;
2102 OZF_ORDER_PRICE_PVT.G_LINE_REC_TBL(j).ship_to_org_id := l_order_set_tbl(j).ship_to_site_use_id;
2103 OZF_ORDER_PRICE_PVT.G_LINE_REC_TBL(j).sold_to_org_id := l_order_set_tbl(j).bill_to_cust_account_id;
2104 --OZF_ORDER_PRICE_PVT.G_LINE_REC_TBL(j).sold_from_org_id := l_order_set_tbl(j).sold_from_cust_account_id;
2105 OZF_ORDER_PRICE_PVT.G_LINE_REC_TBL(j).invoice_to_party_id := l_order_set_tbl(j).bill_to_party_id;
2106 OZF_ORDER_PRICE_PVT.G_LINE_REC_TBL(j).invoice_to_party_site_id := l_order_set_tbl(j).bill_to_party_site_id;
2107 OZF_ORDER_PRICE_PVT.G_LINE_REC_TBL(j).ship_to_party_id := l_order_set_tbl(j).ship_to_party_id;
2108 OZF_ORDER_PRICE_PVT.G_LINE_REC_TBL(j).ship_to_party_site_id := l_order_set_tbl(j).ship_to_party_site_id;
2109 OZF_ORDER_PRICE_PVT.G_LINE_REC_TBL(j).price_list_id := l_line_tbl(j).price_list_id;
2110 OZF_ORDER_PRICE_PVT.G_LINE_REC_TBL(j).request_date := l_order_set_tbl(j).date_ordered;
2111 OZF_ORDER_PRICE_PVT.G_LINE_REC_TBL(j).actual_shipment_date := l_order_set_tbl(j).date_shipped;
2112 OZF_ORDER_PRICE_PVT.G_LINE_REC_TBL(j).pricing_date := l_order_set_tbl(j).date_ordered;
2113
2114 -- R12 Populate Global Resale Structure (+)
2115 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).batch_type := 'TP_ACCRUAL';
2116 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).qp_context_request_id := QP_Price_Request_Context.Get_Request_Id;
2117 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).line_index := l_line_tbl(j).line_index;
2118 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).resale_table_type := 'RESALE';
2119 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).line_id := l_order_set_tbl(j).resale_line_id;
2120 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).resale_transfer_type := l_order_set_tbl(j).resale_transfer_type;
2121 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).product_transfer_movement_type := l_order_set_tbl(j).product_transfer_movement_type;
2122 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).product_transfer_date := l_order_set_tbl(j).product_transfer_date;
2123 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).tracing_flag := l_order_set_tbl(j).tracing_flag;
2124 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).sold_from_cust_account_id := l_order_set_tbl(j).sold_from_cust_account_id;
2125 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).sold_from_site_id := l_order_set_tbl(j).sold_from_site_id;
2126 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).sold_from_contact_party_id := l_order_set_tbl(j).sold_from_contact_party_id;
2127 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).ship_from_cust_account_id := l_order_set_tbl(j).ship_from_cust_account_id;
2128 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).ship_from_site_id := l_order_set_tbl(j).ship_from_site_id;
2129 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).ship_from_contact_party_id := l_order_set_tbl(j).ship_from_contact_party_id;
2130 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).bill_to_party_id := l_order_set_tbl(j).bill_to_party_id;
2131 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).bill_to_party_site_id := l_order_set_tbl(j).bill_to_party_site_id;
2132 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).bill_to_contact_party_id := l_order_set_tbl(j).bill_to_contact_party_id;
2133 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).ship_to_party_id := l_order_set_tbl(j).ship_to_party_id;
2134 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).ship_to_party_site_id := l_order_set_tbl(j).ship_to_party_site_id;
2135 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).ship_to_contact_party_id := l_order_set_tbl(j).ship_to_contact_party_id;
2136 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).end_cust_party_id := l_order_set_tbl(j).end_cust_party_id;
2137 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).end_cust_site_use_id := l_order_set_tbl(j).end_cust_site_use_id;
2138 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).end_cust_site_use_code := l_order_set_tbl(j).end_cust_site_use_code;
2139 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).end_cust_party_site_id := l_order_set_tbl(j).end_cust_party_site_id;
2140 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).end_cust_contact_party_id := l_order_set_tbl(j).end_cust_contact_party_id;
2141 --OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).data_source_code := ??
2142 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).header_attribute_category := l_header_id_tbl(i).header_attribute_category;
2143 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).header_attribute1 := l_header_id_tbl(i).header_attribute1;
2144 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).header_attribute2 := l_header_id_tbl(i).header_attribute2;
2145 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).header_attribute3 := l_header_id_tbl(i).header_attribute3;
2146 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).header_attribute4 := l_header_id_tbl(i).header_attribute4;
2147 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).header_attribute5 := l_header_id_tbl(i).header_attribute5;
2148 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).header_attribute6 := l_header_id_tbl(i).header_attribute6;
2149 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).header_attribute7 := l_header_id_tbl(i).header_attribute7;
2150 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).header_attribute8 := l_header_id_tbl(i).header_attribute8;
2151 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).header_attribute9 := l_header_id_tbl(i).header_attribute9;
2152 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).header_attribute10 := l_header_id_tbl(i).header_attribute10;
2153 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).header_attribute11 := l_header_id_tbl(i).header_attribute11;
2154 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).header_attribute12 := l_header_id_tbl(i).header_attribute12;
2155 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).header_attribute13 := l_header_id_tbl(i).header_attribute13;
2156 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).header_attribute14 := l_header_id_tbl(i).header_attribute14;
2157 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).header_attribute15 := l_header_id_tbl(i).header_attribute15;
2158 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).line_attribute_category := l_order_set_tbl(j).line_attribute_category;
2159 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).line_attribute1 := l_order_set_tbl(j).line_attribute1;
2160 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).line_attribute2 := l_order_set_tbl(j).line_attribute2;
2161 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).line_attribute3 := l_order_set_tbl(j).line_attribute3;
2162 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).line_attribute4 := l_order_set_tbl(j).line_attribute4;
2163 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).line_attribute5 := l_order_set_tbl(j).line_attribute5;
2164 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).line_attribute6 := l_order_set_tbl(j).line_attribute6;
2165 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).line_attribute7 := l_order_set_tbl(j).line_attribute7;
2166 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).line_attribute8 := l_order_set_tbl(j).line_attribute8;
2167 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).line_attribute9 := l_order_set_tbl(j).line_attribute9;
2168 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).line_attribute10 := l_order_set_tbl(j).line_attribute10;
2169 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).line_attribute11 := l_order_set_tbl(j).line_attribute11;
2170 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).line_attribute12 := l_order_set_tbl(j).line_attribute12;
2171 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).line_attribute13 := l_order_set_tbl(j).line_attribute13;
2172 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).line_attribute14 := l_order_set_tbl(j).line_attribute14;
2173 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).line_attribute15 := l_order_set_tbl(j).line_attribute15;
2174 -- R12 Populate Global Resale Structure (-)
2175
2176
2177
2178 END LOOP;
2179
2180 IF OZF_DEBUG_LOW_ON THEN
2181 ozf_utility_PVT.debug_message(l_api_name||'>> building order header');
2182 END IF;
2183
2184 -- build summary line
2185 k := l_order_set_tbl.LAST + 1;
2186 l_line_tbl(k).line_index := k;
2187 l_line_tbl(k).line_id := NULL;
2188 l_line_tbl(k).line_type_code := OZF_ORDER_PRICE_PVT.G_ORDER_HEADER_TYPE;
2189 l_line_tbl(k).pricing_effective_date := l_header_rec.date_ordered;
2190 l_line_tbl(k).active_date_first := l_header_rec.date_ordered;
2191 l_line_tbl(k).active_date_first_type := 'ORD'; -- Change because of ONT QP order 'NO TYPE';
2192 l_line_tbl(k).active_date_second := l_header_rec.date_shipped;
2193 l_line_tbl(k).active_date_second_type := 'SHIP'; -- change because of ONT QP order 'NO TYPE';
2194 l_line_tbl(k).request_type_code := 'ONT';
2195 l_line_tbl(k).currency_code := l_order_set_tbl(1).currency_code;
2196 l_line_tbl(k).price_list_id := l_line_tbl(1).price_list_id;
2197 l_line_tbl(k).price_flag := l_price_flag;
2198
2199 --mkothari 13-dec-2006
2200 l_line_tbl(k).list_price_override_flag := l_list_price_override_flag;
2201 l_line_tbl(k).pricing_status_code := QP_PREQ_GRP.G_STATUS_UNCHANGED;
2202 l_line_tbl(k).chargeback_int_id := l_order_set_tbl(1).resale_line_id;
2203 l_line_tbl(k).resale_table_type := 'LINE'; -- bug 5360598
2204
2205
2206 -- populate the order_price global header structure
2207 -- Here I only populate the values of the qualifiers for ONT.
2208 -- The real global structure will be populate in ozf_order_price_pvt.
2209 -- And it's value can be change in OZF_CHARGEBACK_ATTRMAP_PUB
2210
2211 -- Might be able to add more value here.
2212 OZF_ORDER_PRICE_PVT.G_HEADER_REC.header_id := k;
2213 OZF_ORDER_PRICE_PVT.G_HEADER_REC.order_type_id := l_header_rec.order_type_id;
2214 OZF_ORDER_PRICE_PVT.G_HEADER_REC.sold_to_org_id := l_header_rec.bill_to_cust_account_id;
2215 OZF_ORDER_PRICE_PVT.G_HEADER_REC.invoice_to_org_id := l_header_rec.bill_to_site_use_id;
2216 OZF_ORDER_PRICE_PVT.G_HEADER_REC.ship_to_org_id := l_header_rec.ship_to_site_use_id;
2217 OZF_ORDER_PRICE_PVT.G_HEADER_REC.invoice_to_party_id := l_header_rec.bill_to_party_id;
2218 OZF_ORDER_PRICE_PVT.G_HEADER_REC.invoice_to_party_site_id := l_header_rec.bill_to_party_site_id;
2219 OZF_ORDER_PRICE_PVT.G_HEADER_REC.ship_to_party_id := l_header_rec.ship_to_party_id;
2220 OZF_ORDER_PRICE_PVT.G_HEADER_REC.ship_to_party_site_id := l_header_rec.ship_to_party_site_id;
2221 OZF_ORDER_PRICE_PVT.G_HEADER_REC.price_list_id := l_line_tbl(1).price_list_id;
2222 OZF_ORDER_PRICE_PVT.G_HEADER_REC.ordered_date := l_header_rec.date_ordered;
2223 OZF_ORDER_PRICE_PVT.G_HEADER_REC.request_date := l_header_rec.date_ordered;
2224 OZF_ORDER_PRICE_PVT.G_HEADER_REC.pricing_date := l_header_rec.date_ordered;
2225
2226
2227 IF OZF_DEBUG_LOW_ON THEN
2228 ozf_utility_PVT.debug_message(l_api_name||'>> Calling Get_Order_');
2229 END IF;
2230
2231 OZF_ORDER_PRICE_PVT.Get_Order_Price (
2232 p_api_version => 1.0
2233 ,p_init_msg_list => FND_API.G_FALSE
2234 ,p_commit => FND_API.G_FALSE
2235 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
2236 ,x_return_status => l_return_status
2237 ,x_msg_data => l_msg_data
2238 ,x_msg_count => l_msg_count
2239 ,p_control_rec => l_control_rec
2240 ,xp_line_tbl => l_line_tbl
2241 ,x_ldets_tbl => l_ldets_tbl
2242 ,x_related_lines_tbl => l_related_lines_tbl
2243 );
2244 IF l_return_status<> FND_API.G_RET_STS_SUCCESS THEN
2245 ozf_utility_pvt.error_message('OZF_GET_ORDER_PRIC_ERR');
2246
2247 FOR p in 1..l_order_set_tbl.LAST
2248 LOOP
2249 BEGIN
2250 OPEN OZF_RESALE_COMMON_PVT.g_log_id_csr;
2251 FETCH OZF_RESALE_COMMON_PVT.g_log_id_csr INTO l_log_id;
2252 CLOSE OZF_RESALE_COMMON_PVT.g_log_id_csr;
2253
2254 -- julou bug 6317120. get org_id from table
2255 OPEN OZF_RESALE_COMMON_PVT.gc_line_org_id(l_order_set_tbl(p).resale_line_id);
2256 FETCH OZF_RESALE_COMMON_PVT.gc_line_org_id INTO l_org_id;
2257 CLOSE OZF_RESALE_COMMON_PVT.gc_line_org_id;
2258
2259 OZF_RESALE_LOGS_PKG.Insert_Row(
2260 px_resale_log_id => l_log_id,
2261 p_resale_id => l_order_set_tbl(p).resale_line_id,
2262 p_resale_id_type => OZF_RESALE_COMMON_PVT.G_ID_TYPE_LINE,
2263 p_error_code => 'OZF_GET_ORDER_PRIC_ERR',
2264 p_error_message => fnd_message.get_string('OZF','OZF_GET_ORDER_PRIC_ERR'),
2265 p_column_name => NULL,
2266 p_column_value => NULL,
2267 --px_org_id => OZF_RESALE_COMMON_PVT.g_org_id
2268 px_org_id => l_org_id
2269 );
2270 EXCEPTION
2271 WHEN OTHERS THEN
2272 ozf_utility_pvt.error_message('OZF_INS_RESALE_LOG_WRG');
2273 RAISE FND_API.g_exc_unexpected_error;
2274 END;
2275 END LOOP;
2276 IF OZF_DEBUG_LOW_ON THEN
2277 ozf_utility_PVT.debug_message('/*--- Get order price failed ---*/');
2278 END IF;
2279 GOTO END_LOOP;
2280 END IF;
2281 IF OZF_DEBUG_LOW_ON THEN
2282 ozf_utility_PVT.debug_message('/*--- Get order price succeeded ---*/');
2283 ozf_utility_PVT.debug_message('calling process price result');
2284 ozf_utility_PVT.debug_message('/*--- Calling process_price_result: ---*/');
2285 END IF;
2286
2287 -- Here, reasle_batch_id is passed for some process convenience, it's not necessary.
2288
2289 process_pricing_result(
2290 p_resale_batch_id => p_resale_batch_id,
2291 p_line_tbl => l_line_tbl,
2292 p_caller_type => G_RESALE_CALLER,
2293 x_return_status => l_return_status
2294 );
2295 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2296 FND_MSG_PUB.Count_And_Get (
2297 p_encoded => FND_API.G_FALSE,
2298 p_count => l_temp_count,
2299 p_data => l_temp_data
2300 );
2301
2302 fnd_msg_pub.Get(
2303 p_msg_index => l_temp_count,
2304 p_encoded => FND_API.G_FALSE,
2305 p_data => l_temp_data,
2306 p_msg_index_out => l_temp_count
2307 );
2308 IF OZF_DEBUG_LOW_ON THEN
2309 ozf_utility_PVT.debug_message('After process_price:'||l_temp_count||' ,'||l_temp_data);
2310 END IF;
2311 FOR i in 1..l_order_set_tbl.LAST
2312 LOOP
2313 BEGIN
2314 OPEN OZF_RESALE_COMMON_PVT.g_log_id_csr;
2315 FETCH OZF_RESALE_COMMON_PVT.g_log_id_csr INTO l_log_id;
2316 CLOSE OZF_RESALE_COMMON_PVT.g_log_id_csr;
2317
2318 -- julou bug 6317120. get org_id from table
2319 OPEN OZF_RESALE_COMMON_PVT.gc_line_org_id(l_order_set_tbl(i).resale_line_id);
2320 FETCH OZF_RESALE_COMMON_PVT.gc_line_org_id INTO l_org_id;
2321 CLOSE OZF_RESALE_COMMON_PVT.gc_line_org_id;
2322
2323 OZF_RESALE_LOGS_PKG.Insert_Row(
2324 px_resale_log_id => l_log_id,
2325 p_resale_id => l_order_set_tbl(i).resale_line_id,
2326 p_resale_id_type => OZF_RESALE_COMMON_PVT.G_ID_TYPE_LINE,
2327 p_error_code => 'OZF_PROC_PRIC_RESLT_ERR',
2328 p_error_message => fnd_message.get_string('OZF','OZF_PROC_PRIC_RESLT_ERR'),
2329 p_column_name => NULL,
2330 p_column_value => NULL,
2331 --px_org_id => OZF_RESALE_COMMON_PVT.g_org_id
2332 px_org_id => l_org_id
2333 );
2334 EXCEPTION
2335 WHEN OTHERS THEN
2336 ozf_utility_pvt.error_message('OZF_INS_RESALE_LOG_WRG');
2337 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2338 END;
2339 END LOOP;
2340 IF OZF_DEBUG_LOW_ON THEN
2341 ozf_utility_PVT.debug_message('/*--- process_price_result Failed ---*/');
2342 END IF;
2343 goto END_LOOP;
2344 END IF;
2345 ELSE
2346 IF OZF_DEBUG_LOW_ON THEN
2347 ozf_utility_PVT.debug_message('No order line to be processed');
2348 END IF;
2349 END IF;
2350 END IF; -- END if for order_NUMBER, bill_to cust not NULL
2351 << END_LOOP >>
2352 NULL;
2353 END LOOP;
2354 ELSE
2355 IF OZF_DEBUG_LOW_ON THEN
2356 ozf_utility_PVT.debug_message('/*--- No order to process ---*/');
2357 END IF;
2358 END IF;
2359 -- Debug Message
2360 IF OZF_DEBUG_HIGH_ON THEN
2361 OZF_UTILITY_PVT.debug_message(l_full_name||': End'|| x_return_status);
2362 END IF;
2363 --Standard call to get message count and if count=1, get the message
2364 FND_MSG_PUB.Count_And_Get (
2365 p_encoded => FND_API.G_FALSE,
2366 p_count => x_msg_count,
2367 p_data => x_msg_data
2368 );
2369 EXCEPTION
2370 WHEN FND_API.G_EXC_ERROR THEN
2371 ROLLBACK TO Process_Resale_Order;
2372 x_return_status := FND_API.G_RET_STS_ERROR;
2373 -- Standard call to get message count and if count=1, get the message
2374 FND_MSG_PUB.Count_And_Get (
2375 p_encoded => FND_API.G_FALSE,
2376 p_count => x_msg_count,
2377 p_data => x_msg_data
2378 );
2379 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2380 ROLLBACK TO Process_Resale_Order;
2381 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2382
2383 -- Standard call to get message count and if count=1, get the message
2384 FND_MSG_PUB.Count_And_Get (
2385 p_encoded => FND_API.G_FALSE,
2386 p_count => x_msg_count,
2387 p_data => x_msg_data
2388 );
2389 WHEN OTHERS THEN
2390 ROLLBACK TO Process_Resale_Order;
2391 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2392
2393 -- Standard call to get message count and if count=1, get the message
2394 FND_MSG_PUB.Count_And_Get (
2395 p_encoded => FND_API.G_FALSE,
2396 p_count => x_msg_count,
2397 p_data => x_msg_data
2398 );
2399 END Process_Resale_Order;
2400
2401 ---------------------------------------------------------------------
2402 -- PROCEDURE
2403 -- Process_Resale
2404 --
2405 -- PURPOSE
2406 -- Process a batch FROM resale tables.
2407 --
2408 -- PARAMETERS
2409 --
2410 -- NOTES
2411 --
2412 ---------------------------------------------------------------------
2413 PROCEDURE Process_Resale (
2414 p_api_version IN NUMBER
2415 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
2416 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
2417 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
2418 ,p_resale_batch_id IN NUMBER
2419 ,x_return_status OUT NOCOPY VARCHAR2
2420 ,x_msg_data OUT NOCOPY VARCHAR2
2421 ,x_msg_count OUT NOCOPY NUMBER
2422 )IS
2423 l_api_name CONSTANT VARCHAR2(30) := 'PROCESS_RESALE';
2424 l_api_version CONSTANT NUMBER := 1.0;
2425 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
2426 --
2427 l_return_status VARCHAR2(30);
2428 l_msg_data VARCHAR2(2000);
2429 l_msg_count NUMBER;
2430
2431 BEGIN
2432 -- Standard BEGIN of API savepoint
2433 SAVEPOINT PROCESS_TP_RESALE;
2434 -- Standard call to check for call compatibility.
2435 IF NOT FND_API.Compatible_API_Call (
2436 l_api_version,
2437 p_api_version,
2438 l_api_name,
2439 G_PKG_NAME)
2440 THEN
2441 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2442 END IF;
2443
2444 --Initialize message lISt if p_init_msg_list IS TRUE.
2445 IF FND_API.To_BOOLEAN (p_init_msg_list) THEN
2446 FND_MSG_PUB.initialize;
2447 END IF;
2448
2449 -- Debug Message
2450 IF OZF_DEBUG_LOW_ON THEN
2451 OZF_UTILITY_PVT.debug_message(l_full_name||': Start');
2452 END IF;
2453
2454 -- Initialize API return status to sucess
2455 x_return_status := FND_API.G_RET_STS_SUCCESS;
2456
2457 -- validate batch for resale processing
2458 Validate_batch(
2459 p_api_version => 1
2460 ,p_init_msg_list => FND_API.G_FALSE
2461 ,p_commit => FND_API.G_FALSE
2462 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
2463 ,p_resale_batch_id => p_resale_batch_id
2464 ,x_return_status => l_return_status
2465 ,x_msg_data => l_msg_data
2466 ,x_msg_count => l_msg_count
2467 );
2468
2469 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2470 RAISE FND_API.G_EXC_ERROR;
2471 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2472 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2473 END IF;
2474
2475 -- calling Third party accrual validation for this batch
2476 Validate_Order_Record(
2477 p_api_version => 1
2478 ,p_init_msg_list => FND_API.G_FALSE
2479 ,p_commit => FND_API.G_FALSE
2480 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
2481 ,p_resale_batch_id => p_resale_batch_id
2482 ,p_caller_type => G_RESALE_CALLER
2483 ,x_return_status => l_return_status
2484 ,x_msg_data => l_msg_data
2485 ,x_msg_count => l_msg_count
2486 );
2487
2488 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2489 RAISE FND_API.G_EXC_ERROR;
2490 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2491 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2492 END IF;
2493
2494 Process_Resale_Order (
2495 p_api_version => 1.0
2496 ,p_init_msg_list => FND_API.G_FALSE
2497 ,p_commit => FND_API.G_FALSE
2498 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
2499 ,p_resale_batch_id => p_resale_batch_id
2500 ,x_return_status => l_return_status
2501 ,x_msg_data => l_msg_data
2502 ,x_msg_count => l_msg_count
2503 );
2504
2505 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2506 RAISE FND_API.G_EXC_ERROR;
2507 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2508 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2509 END IF;
2510
2511 -- Debug Message
2512 IF OZF_DEBUG_HIGH_ON THEN
2513 OZF_UTILITY_PVT.debug_message(p_message_text => l_full_name||': End');
2514 END IF;
2515 --Standard call to get message count and if count=1, get the message
2516 FND_MSG_PUB.Count_And_Get (
2517 p_encoded => FND_API.G_FALSE,
2518 p_count => x_msg_count,
2519 p_data => x_msg_data
2520 );
2521
2522 EXCEPTION
2523 WHEN FND_API.G_EXC_ERROR THEN
2524 ROLLBACK TO PROCESS_TP_RESALE;
2525 x_return_status := FND_API.G_RET_STS_ERROR;
2526 -- Standard call to get message count and if count=1, get the message
2527 FND_MSG_PUB.Count_And_Get (
2528 p_encoded => FND_API.G_FALSE,
2529 p_count => x_msg_count,
2530 p_data => x_msg_data
2531 );
2532 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2533 ROLLBACK TO PROCESS_TP_RESALE;
2534 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2535
2536 -- Standard call to get message count and if count=1, get the message
2537 FND_MSG_PUB.Count_And_Get (
2538 p_encoded => FND_API.G_FALSE,
2539 p_count => x_msg_count,
2540 p_data => x_msg_data
2541 );
2542 WHEN OTHERS THEN
2543 ROLLBACK TO PROCESS_TP_RESALE;
2544 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2545
2546 -- Standard call to get message count and if count=1, get the message
2547 FND_MSG_PUB.Count_And_Get (
2548 p_encoded => FND_API.G_FALSE,
2549 p_count => x_msg_count,
2550 p_data => x_msg_data
2551 );
2552 END Process_Resale;
2553
2554 ---------------------------------------------------------------------
2555 -- PROCEDURE
2556 -- Move_Indirect_Customer_Order
2557 --
2558 -- PURPOSE
2559 --
2560 -- This procedure is to move indirect customer data to the resale tables. It
2561 -- is called during the process of IFACE data
2562 --
2563 -- THIS IS TO BE USED IN MAKE PAYMENT
2564 -- need to INSERT these transaction in inventory
2565 --
2566 -- PARAMETERS
2567 --
2568 --
2569 -- NOTES
2570 --
2571 ---------------------------------------------------------------------
2572 PROCEDURE Move_Indirect_Customer_Order
2573 (
2574 p_api_version IN NUMBER
2575 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
2576 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
2577 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
2578 ,p_resale_batch_id IN NUMBER
2579 ,x_return_status OUT NOCOPY VARCHAR2
2580 ,x_msg_data OUT NOCOPY VARCHAR2
2581 ,x_msg_count OUT NOCOPY NUMBER
2582 )IS
2583 l_api_name CONSTANT VARCHAR2(30) := 'Move_Indirect_Customer_Order';
2584 l_api_version CONSTANT NUMBER := 1.0;
2585 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
2586 --
2587 l_return_status VARCHAR2(30);
2588 l_msg_data VARCHAR2(2000);
2589 l_msg_count NUMBER;
2590
2591 l_org_id NUMBER;
2592 -- Start: bug # 5997978 fixed
2593 /*CURSOR org_id_csr IS
2594 SELECT (TO_NUMBER(SUBSTRB(USERENV('CLIENT_INFO'), 1, 10)))
2595 FROM dual; */
2596 l_batch_org_id NUMBER;
2597 CURSOR org_id_csr(cv_resale_batch_id IN NUMBER) IS
2598 SELECT org_id
2599 FROM ozf_resale_batches_all
2600 WHERE resale_batch_id = cv_resale_batch_id;
2601 -- End: bug # 5997978 fixed
2602
2603 CURSOR order_num_csr is
2604 SELECT distinct order_NUMBER,
2605 bill_to_cust_account_id,
2606 date_ordered
2607 FROM ozf_resale_lines_int
2608 WHERE status_code=OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_OPEN
2609 AND direct_customer_flag = 'F'
2610 AND resale_batch_id = p_resale_batch_id
2611 ORDER BY date_ordered;
2612
2613 TYPE order_num_tbl_type is TABLE OF order_num_csr%rowtype INDEX BY binary_integer;
2614 l_order_num_tbl order_num_tbl_type;
2615
2616 -- we only need one record
2617 CURSOR interface_rec_csr(p_num in VARCHAR2,
2618 p_name in VARCHAR2,
2619 p_date in date) IS
2620 SELECT *
2621 FROM ozf_resale_lines_int
2622 WHERE order_NUMBER = p_num
2623 AND bill_to_cust_account_id = p_name
2624 AND date_ordered = p_date
2625 AND direct_customer_flag ='F'
2626 AND status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_OPEN
2627 AND resale_batch_id = p_resale_batch_id;
2628
2629 TYPE interface_tbl_type is TABLE OF OZF_RESALE_COMMON_PVT.g_interface_rec_csr%rowtype INDEX BY binary_integer;
2630
2631 l_resale_int_tbl interface_tbl_type;
2632
2633 l_header_id NUMBER;
2634 l_line_id NUMBER;
2635
2636 j NUMBER;
2637 k NUMBER;
2638 BEGIN
2639 -- Standard BEGIN of API savepoint
2640 SAVEPOINT TP_ACCRUAL_MV_IC;
2641 -- Standard call to check for call compatibility.
2642 IF NOT FND_API.Compatible_API_Call (
2643 l_api_version,
2644 p_api_version,
2645 l_api_name,
2646 G_PKG_NAME)
2647 THEN
2648 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2649 END IF;
2650
2651 --Initialize message list if p_init_msg_list is TRUE.
2652 IF FND_API.To_Boolean (p_init_msg_list) THEN
2653 FND_MSG_PUB.initialize;
2654 END IF;
2655
2656 -- Debug Message
2657 IF OZF_DEBUG_HIGH_ON THEN
2658 OZF_UTILITY_PVT.debug_message(p_message_text => l_full_name||': Start');
2659 END IF;
2660
2661 -- Initialize API return status to sucess
2662 x_return_status := FND_API.G_RET_STS_SUCCESS;
2663
2664 IF OZF_DEBUG_LOW_ON THEN
2665 ozf_utility_PVT.debug_message('in mv id cust' );
2666 END IF;
2667 -- get org_id
2668 -- Start: bug # 5997978 fixed
2669 --OPEN org_id_csr;
2670 OPEN org_id_csr(p_resale_batch_id);
2671 -- End: bug # 5997978 fixed
2672 FETCH org_id_csr INTO l_batch_org_id;
2673 l_org_id := MO_GLOBAL.get_valid_org(l_batch_org_id);
2674 CLOSE org_id_csr;
2675
2676 IF l_org_id is NULL THEN
2677 ozf_utility_pvt.error_message('OZF_CLAIM_ORG_ID_MISSING');
2678 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2679 END IF;
2680
2681 --j:=1;
2682 IF l_order_num_tbl.EXISTS(1) THEN
2683 l_order_num_tbl.DELETE;
2684 END IF;
2685 -- INSERT order headers and UPDATE lines.
2686 OPEN order_num_csr;
2687 FETCH order_num_csr BULK COLLECT INTO l_order_num_tbl;
2688 --LOOP
2689 -- EXIT WHEN order_num_csr%NOTFOUND;
2690 -- FETCH order_num_csr INTO l_order_num_tbl(j);
2691 -- j:=j+1;
2692 --END LOOP;
2693 CLOSE order_num_csr;
2694
2695 IF l_order_num_tbl.EXISTS(1) THEN
2696 FOR i in 1..l_order_num_tbl.LAST
2697 LOOP
2698 IF OZF_DEBUG_LOW_ON THEN
2699 ozf_utility_PVT.debug_message('mv indirect customer with order NUMBER, cust name:' || l_order_num_tbl(i).order_NUMBER|| ','||l_order_num_tbl(i).bill_to_cust_account_id);
2700 END IF;
2701
2702 j:=1;
2703 OPEN interface_rec_csr( l_order_num_tbl(i).order_NUMBER,
2704 l_order_num_tbl(i).bill_to_cust_account_id,
2705 l_order_num_tbl(i).date_ordered);
2706 LOOP
2707 EXIT when interface_rec_csr%NOTFOUND;
2708 FETCH interface_rec_csr INTO l_resale_int_tbl(j);
2709 j:=j+1;
2710 END LOOP;
2711 CLOSE interface_rec_csr;
2712
2713 -- DELETE the error log before INSERT the orders
2714 DELETE FROM ozf_resale_logs_all a
2715 WHERE a.resale_id_type = 'IFACE'
2716 AND a.resale_id IN (
2717 SELECT resale_line_int_id
2718 FROM ozf_resale_lines_int_all b
2719 WHERE b.direct_customer_flag = 'F'
2720 AND b.status_code = 'OPEN'
2721 AND b.order_number = l_order_num_tbl(i).order_number
2722 AND b.bill_to_cust_account_id = l_order_num_tbl(i).bill_to_cust_account_id
2723 AND b.date_ordered = l_order_num_tbl(i).date_ordered
2724 AND b.resale_batch_id = p_resale_batch_id
2725 );
2726
2727 -- ???? Consider recording order info in ozf_sales_transactions
2728
2729 IF l_resale_int_tbl.EXISTS(1) THEN
2730
2731 FOR k in 1..l_resale_int_tbl.LAST LOOP
2732
2733 -- Create an order header for the order_NUMBER;
2734 IF k = 1 THEN
2735 OZF_RESALE_COMMON_PVT.Insert_resale_header(
2736 p_api_version => 1
2737 ,p_init_msg_list => FND_API.G_FALSE
2738 ,p_commit => FND_API.G_FALSE
2739 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
2740 ,p_line_int_rec => l_resale_int_tbl(k)
2741 ,x_header_id => l_header_id
2742 ,x_return_status => l_return_status
2743 ,x_msg_data => l_msg_data
2744 ,x_msg_count => l_msg_count
2745 );
2746 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2747 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2748 END IF;
2749 END IF;
2750
2751 OZF_RESALE_COMMON_PVT.Insert_resale_line(
2752 p_api_version => 1
2753 ,p_init_msg_list => FND_API.G_FALSE
2754 ,p_commit => FND_API.G_FALSE
2755 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
2756 ,p_line_int_rec => l_resale_int_tbl(k)
2757 ,p_header_id => l_header_id
2758 ,x_line_id => l_line_id
2759 ,x_return_status => l_return_status
2760 ,x_msg_data => l_msg_data
2761 ,x_msg_count => l_msg_count
2762 );
2763 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2764 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2765 END IF;
2766 END LOOP;
2767 END IF;
2768 END LOOP;
2769
2770 BEGIN
2771 UPDATE ozf_resale_lines_int
2772 SET status_code= OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_CLOSED
2773 WHERE direct_customer_flag ='F'
2774 AND status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_OPEN
2775 AND resale_batch_id = p_resale_batch_id;
2776 EXCEPTION
2777 WHEN OTHERS THEN
2778 ozf_utility_pvt.error_message('OZF_UPD_RESALE_INT_WRG');
2779 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2780 END;
2781 END IF;
2782 -- Debug Message
2783 IF OZF_DEBUG_HIGH_ON THEN
2784 OZF_UTILITY_PVT.debug_message(p_message_text => l_full_name||': End');
2785 END IF;
2786
2787 --Standard call to get message count and if count=1, get the message
2788 FND_MSG_PUB.Count_And_Get (
2789 p_encoded => FND_API.G_FALSE,
2790 p_count => x_msg_count,
2791 p_data => x_msg_data
2792 );
2793 EXCEPTION
2794 WHEN FND_API.G_EXC_ERROR THEN
2795 x_return_status := FND_API.G_RET_STS_ERROR;
2796 -- Standard call to get message count and if count=1, get the message
2797 FND_MSG_PUB.Count_And_Get (
2798 p_encoded => FND_API.G_FALSE,
2799 p_count => x_msg_count,
2800 p_data => x_msg_data
2801 );
2802 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2803 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2804
2805 -- Standard call to get message count and if count=1, get the message
2806 FND_MSG_PUB.Count_And_Get (
2807 p_encoded => FND_API.G_FALSE,
2808 p_count => x_msg_count,
2809 p_data => x_msg_data
2810 );
2811 WHEN OTHERS THEN
2812 ROLLBACK TO TP_ACCRUAL_MV_IC;
2813 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2814
2815 -- Standard call to get message count and if count=1, get the message
2816 FND_MSG_PUB.Count_And_Get (
2817 p_encoded => FND_API.G_FALSE,
2818 p_count => x_msg_count,
2819 p_data => x_msg_data
2820 );
2821 END Move_Indirect_Customer_Order;
2822
2823 ---------------------------------------------------------------------
2824 -- PROCEDURE
2825 -- Process_Order
2826 --
2827 -- PURPOSE
2828 -- Process order information. Only direct customer order will be simulated.
2829 -- It is called by UI or concurrent program.
2830 --
2831 -- PARAMETERS
2832 --
2833 -- NOTES
2834 --
2835 ---------------------------------------------------------------------
2836 PROCEDURE Process_Order(
2837 p_api_version IN NUMBER
2838 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
2839 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
2840 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
2841 ,p_resale_batch_id IN NUMBER
2842 ,x_return_status OUT NOCOPY VARCHAR2
2843 ,x_msg_data OUT NOCOPY VARCHAR2
2844 ,x_msg_count OUT NOCOPY NUMBER
2845 )IS
2846 l_api_name CONSTANT VARCHAR2(30) := 'Process_Order';
2847 l_api_version CONSTANT NUMBER := 1.0;
2848 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
2849 --
2850 l_return_status VARCHAR2(30);
2851 l_msg_data VARCHAR2(2000);
2852 l_msg_count NUMBER;
2853
2854 CURSOR order_num_csr IS
2855 SELECT DISTINCT order_NUMBER,
2856 bill_to_cust_account_id,
2857 date_ordered
2858 FROM ozf_resale_lines_int_all
2859 WHERE status_code IN ('OPEN', 'DUPLICATED')
2860 AND duplicated_adjustment_id IS NULL
2861 AND resale_batch_id = p_resale_batch_id
2862 ORDER BY date_ordered;
2863
2864 TYPE order_num_tbl_type IS TABLE OF order_num_csr%rowtype
2865 INDEX BY binary_integer;
2866
2867 l_order_num_tbl order_num_tbl_type;
2868
2869 CURSOR order_set_csr( p_order_number IN VARCHAR2,
2870 p_id IN NUMBER,
2871 p_date IN DATE
2872 ) IS
2873 SELECT *
2874 FROM ozf_resale_lines_int_all
2875 WHERE order_number = p_order_number
2876 AND bill_to_cust_account_id= p_id
2877 AND date_ordered = p_date
2878 AND status_code IN ('OPEN', 'DUPLICATED')
2879 AND duplicated_adjustment_id is NULL
2880 AND resale_batch_id = p_resale_batch_id
2881 AND tracing_flag = 'F';
2882
2883 TYPE resale_lines_tbl_type IS TABLE OF order_set_csr%rowtype
2884 INDEX BY binary_integer;
2885
2886 l_order_set_tbl resale_lines_tbl_type;
2887
2888 l_control_rec QP_PREQ_GRP.CONTROL_RECORD_TYPE;
2889
2890 l_line_tbl OZF_ORDER_PRICE_PVT.LINE_REC_TBL_TYPE;
2891 l_ldets_tbl OZF_ORDER_PRICE_PVT.LDETS_TBL_TYPE;
2892 l_related_lines_tbl OZF_ORDER_PRICE_PVT.RLTD_LINE_TBL_TYPE;
2893
2894 p NUMBER;
2895 k NUMBER;
2896
2897 l_log_id NUMBER;
2898
2899 l_temp_count NUMBER;
2900 l_temp_data VARCHAR2(2000);
2901 l_price_flag VARCHAR2(1) := NULL;
2902
2903 --mkothari 13-dec-2006
2904 l_list_price_override_flag VARCHAR2(1) := NULL;
2905
2906 CURSOR dup_adjustments_csr( p_order_number IN VARCHAR2,
2907 p_id IN NUMBER,
2908 p_date IN DATE
2909 ) IS
2910 SELECT *
2911 FROM ozf_resale_lines_int_all
2912 WHERE order_number = p_order_number
2913 AND bill_to_cust_account_id = p_id
2914 AND date_ordered = p_date
2915 AND status_code = 'DUPLICATED'
2916 AND duplicated_adjustment_id IS NOT NULL
2917 AND resale_batch_id = p_resale_batch_id
2918 AND tracing_flag = 'F';
2919
2920 CURSOR tracing_data_csr( p_order_number IN VARCHAR2,
2921 p_id IN NUMBER,
2922 p_date IN DATE
2923 ) IS
2924 SELECT *
2925 FROM ozf_resale_lines_int_all
2926 WHERE order_number = p_order_number -- ?? need this
2927 AND bill_to_cust_account_id = p_id -- ?? need this
2928 AND date_ordered = p_date
2929 AND status_code IN ('OPEN', 'DUPLICATED')
2930 AND resale_batch_id = p_resale_batch_id
2931 AND tracing_flag = 'T';
2932
2933 -- [BEGIN OF BUG 4233341 FIXING]
2934 CURSOR csr_valid_line_count(cv_batch_id IN NUMBER) IS
2935 SELECT COUNT(1)
2936 FROM ozf_resale_lines_int_all
2937 WHERE status_code IN ('PROCESSED', 'CLOSED', 'DUPLICATED')
2938 AND resale_batch_id = cv_batch_id;
2939
2940 CURSOR csr_get_batch_number(cv_batch_id IN NUMBER) IS
2941 SELECT batch_number
2942 FROM ozf_resale_batches_all
2943 WHERE resale_batch_id = cv_batch_id;
2944
2945 l_valid_line_count NUMBER;
2946 l_batch_number VARCHAR2(30);
2947
2948 CURSOR csr_out_dispute_pre_proc(cv_batch_id IN NUMBER) IS
2949 SELECT i.resale_line_int_id id
2950 , lk.meaning dispute_code
2951 , lg.error_message
2952 , lg.column_name
2953 , lg.column_value
2954 FROM ozf_resale_lines_int_all i
2955 , ozf_resale_logs_all lg
2956 , ozf_lookups lk
2957 WHERE i.dispute_code = lk.lookup_code(+)
2958 AND lk.lookup_type(+) = 'OZF_RESALE_DISPUTE_CODE'
2959 AND i.status_code = 'DISPUTED'
2960 AND i.resale_batch_id = cv_batch_id
2961 AND i.resale_line_int_id = lg.resale_id (+)
2962 ORDER BY i.resale_line_int_id;
2963
2964 TYPE output_dispute_line_tbl IS TABLE OF csr_out_dispute_pre_proc%ROWTYPE
2965 INDEX BY BINARY_INTEGER;
2966
2967 l_output_dispute_line_tbl output_dispute_line_tbl;
2968 i_output_idx NUMBER;
2969
2970 /*
2971 TYPE output_dispute_line_id_tbl IS
2972 TABLE OF ozf_resale_lines_int_all.resale_line_int_id%TYPE
2973 INDEX BY BINARY_INTEGER;
2974
2975 TYPE output_dispute_code_tbl IS
2976 TABLE OF ozf_lookups.meaning%TYPE
2977 INDEX BY BINARY_INTEGER;
2978
2979 TYPE output_dispute_bill_to_tbl IS
2980 TABLE OF ozf_resale_lines_int_all.bill_to_party_name%TYPE
2981 INDEX BY BINARY_INTEGER;
2982
2983 TYPE output_dispute_order_tbl IS
2984 TABLE OF ozf_resale_lines_int_all.order_number%ROWTYPE
2985 INDEX BY BINARY_INTEGER;
2986
2987 TYPE output_dispute_order_date_tbl IS
2988 TABLE OF ozf_resale_lines_int_all.date_ordered%ROWTYPE
2989 INDEX BY BINARY_INTEGER;
2990
2991 TYPE output_dispute_item_tbl IS
2992 TABLE OF ozf_resale_lines_int_all.item_number%ROWTYPE
2993 INDEX BY BINARY_INTEGER;
2994
2995 l_output_dispute_code_tbl output_dispute_code_tbl;
2996 l_output_dispute_bill_to_tbl output_dispute_bill_to_tbl;
2997 l_output_dispute_order_tbl output_dispute_order_tbl;
2998 l_output_dispute_order_date_tbl output_dispute_order_date_tbl;
2999 l_output_dispute_item_tbl output_dispute_item_tbl;
3000 */
3001 -- [END OF BUG 4233341 FIXING]
3002
3003 l_lines_disputed NUMBER;
3004 l_calculated_amount NUMBER;
3005 l_total_accepted_amount NUMBER;
3006 l_total_allowed_amount NUMBER;
3007 l_total_disputed_amount NUMBER;
3008 l_total_claimed_amount NUMBER;
3009 l_status_code VARCHAR2(30);
3010
3011 l_lines_invalid NUMBER;
3012 l_accrual_on_selling VARCHAR2(3);
3013 l_new_batch_status VARCHAR2(30);
3014 l_default_price_list_id NUMBER;
3015 -- bug 6317120
3016 l_org_id NUMBER;
3017 -- end bug 6317120
3018 BEGIN
3019 -- Standard BEGIN of API savepoint
3020 SAVEPOINT Process_Order;
3021 -- Standard call to check for call compatibility.
3022 IF NOT FND_API.Compatible_API_Call (
3023 l_api_version,
3024 p_api_version,
3025 l_api_name,
3026 G_PKG_NAME)
3027 THEN
3028 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3029 END IF;
3030
3031 --Initialize message list if p_init_msg_list is TRUE.
3032 IF FND_API.To_Boolean (p_init_msg_list) THEN
3033 FND_MSG_PUB.initialize;
3034 END IF;
3035
3036 -- Debug Message
3037 IF OZF_DEBUG_HIGH_ON THEN
3038 OZF_UTILITY_PVT.debug_message(p_message_text => l_full_name||': start');
3039 END IF;
3040
3041 -- Initialize API return status to sucess
3042 x_return_status := FND_API.G_RET_STS_SUCCESS;
3043
3044 -- Call preprocess here.
3045 OZF_PRE_PROCESS_PVT.Resale_Pre_Process(
3046 p_api_version_number => 1
3047 ,p_init_msg_list => FND_API.G_FALSE
3048 ,p_commit => FND_API.G_FALSE
3049 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
3050 ,p_batch_id => p_resale_batch_id
3051 ,x_batch_status => l_new_batch_status
3052 ,x_return_status => l_return_status
3053 ,x_msg_data => l_msg_data
3054 ,x_msg_count => l_msg_count
3055 );
3056 IF l_return_status <> FND_API.g_ret_sts_SUCCESS THEN
3057 ozf_utility_pvt.error_message('OZF_PRE_PROCESS_ERR');
3058 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3059 END IF;
3060
3061 -- [BUG 4233341 FIXING]: add output file
3062 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' Disputed Interface Lines After Pre-Processing:');
3063 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' ');
3064 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' Interface');
3065 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' Line Id Dispute Code Error Column Name Column Value');
3066 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' --------- -------------------------------- -------------------------------- -------------------- ------------');
3067
3068 --i_output_idx := 1;
3069 IF l_output_dispute_line_tbl.EXISTS(1) THEN
3070 l_output_dispute_line_tbl.DELETE;
3071 END IF;
3072 OPEN csr_out_dispute_pre_proc(p_resale_batch_id);
3073 FETCH csr_out_dispute_pre_proc BULK COLLECT INTO l_output_dispute_line_tbl;
3074 --LOOP
3075 -- FETCH csr_out_dispute_pre_proc INTO l_output_dispute_line_tbl(i_output_idx);
3076 -- EXIT WHEN csr_out_dispute_pre_proc%NOTFOUND;
3077 -- i_output_idx := i_output_idx + 1;
3078 --END LOOP;
3079 /*
3080 FETCH csr_out_dispute_pre_proc BULK COLLECT INTO l_output_dispute_code_tbl
3081 , l_output_dispute_bill_to_tbl
3082 , l_output_dispute_order_tbl
3083 , l_output_dispute_order_date_tbl
3084 , l_output_dispute_item_tbl;
3085 */
3086 CLOSE csr_out_dispute_pre_proc;
3087
3088 FOR i_output_idx IN 1..l_output_dispute_line_tbl.COUNT LOOP
3089 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' '
3090 ||RPAD(l_output_dispute_line_tbl(i_output_idx).id, 10, ' ')
3091 ||RPAD(l_output_dispute_line_tbl(i_output_idx).dispute_code, 33, ' ')
3092 ||RPAD(l_output_dispute_line_tbl(i_output_idx).error_message, 34, ' ')
3093 ||RPAD(l_output_dispute_line_tbl(i_output_idx).column_name, 21, ' ')
3094 ||RPAD(l_output_dispute_line_tbl(i_output_idx).column_value, 15, ' ')
3095 );
3096 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' ');
3097 END LOOP;
3098 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' ');
3099
3100 -- remove all the disputed lines
3101 UPDATE ozf_resale_lines_int
3102 SET resale_batch_id = null
3103 , request_id = null -- [BUG 4233341 FIXING]
3104 Where resale_batch_id = p_resale_batch_id
3105 and status_code = 'DISPUTED';
3106
3107 -- OK to do it here
3108 Move_Indirect_Customer_Order (
3109 p_api_version => 1
3110 ,p_init_msg_list => FND_API.G_FALSE
3111 ,p_commit => FND_API.G_FALSE
3112 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
3113 ,p_resale_batch_id => p_resale_batch_id
3114 ,x_return_status => l_return_status
3115 ,x_msg_data => l_msg_data
3116 ,x_msg_count => l_msg_count
3117 );
3118 IF l_return_status <> FND_API.g_ret_sts_SUCCESS THEN
3119 ozf_utility_pvt.error_message('OZF_MV_ID_CUST_ORDER_ERR');
3120 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3121 END IF;
3122
3123 -- Get profile value for price list
3124 l_default_price_list_id := G_TP_DEFAULT_PRICE_LIST; --fnd_profile.value('OZF_TP_ACCRUAL_PRICE_LIST');
3125
3126 -- Define control rec
3127 -- setup pricing_event based on purpose code and profile
3128 -- privcing_event is based on profile
3129
3130 -- We need to UPDATE the order FROM indirect customers so that they're not to be included in the
3131 -- pricing simulation;
3132 l_control_rec.pricing_event := G_PRICING_SIM_EVENT; --fnd_profile.value('OZF_PRICING_SIMULATION_EVENT');
3133 IF l_control_rec.pricing_event is NULL THEN
3134 l_control_rec.pricing_event := 'BATCH,BOOK,SHIP';
3135 ELSE
3136 IF l_control_rec.pricing_event = 'BATCH' THEN
3137 l_control_rec.pricing_event := 'BATCH';
3138 ELSIF l_control_rec.pricing_event = 'BOOK' THEN
3139 l_control_rec.pricing_event := 'BATCH,BOOK';
3140 ELSIF l_control_rec.pricing_event = 'SHIP' THEN
3141 l_control_rec.pricing_event := 'BATCH,BOOK,SHIP';
3142 END IF;
3143 END IF;
3144
3145 IF OZF_DEBUG_LOW_ON THEN
3146 ozf_utility_PVT.debug_message('Event:' ||l_control_rec.pricing_event );
3147 END IF;
3148
3149 l_control_rec.calculate_flag := 'Y';
3150 l_control_rec.simulation_flag := 'Y';
3151 l_control_rec.source_order_amount_flag := 'Y';
3152 l_control_rec.gsa_check_flag := 'N';
3153 l_control_rec.gsa_dup_check_flag := 'N';
3154 l_control_rec.temp_table_insert_flag := 'N';
3155
3156 --p := 1;
3157 IF l_order_num_tbl.EXISTS(1) THEN
3158 l_order_num_tbl.DELETE;
3159 END IF;
3160 OPEN order_num_csr;
3161 FETCH order_num_csr BULK COLLECT INTO l_order_num_tbl;
3162 --LOOP
3163 -- FETCH order_num_csr INTO l_order_num_tbl(p);
3164 -- EXIT when order_num_csr%notfound;
3165 -- p:= p+1;
3166 --END LOOP;
3167 CLOSE order_num_csr;
3168
3169 IF l_order_num_tbl.EXISTS(1) THEN
3170
3171 l_accrual_on_selling := fnd_profile.value('OZF_ACC_ON_SELLING_PRICE');
3172
3173 -- If this profile is not set, we default the value to 'N'
3174
3175 IF l_accrual_on_selling IS NULL THEN
3176 l_accrual_on_selling := 'N';
3177 END IF;
3178
3179 -- convert the value of the profile to proper price flag value
3180 IF l_accrual_on_selling = 'Y' THEN
3181 --l_price_flag := 'G'; -- 'G' is not implemented in QP -- mkothari
3182
3183 --mkothari 13-dec-2006
3184 l_price_flag := 'Y';
3185 l_list_price_override_flag := 'Y';
3186
3187 ELSE
3188 l_price_flag := 'Y';
3189 END IF;
3190
3191 IF OZF_DEBUG_LOW_ON THEN
3192 ozf_utility_PVT.debug_message('Price flag:' ||l_price_flag );
3193 ozf_utility_PVT.debug_message('List Price Override Flag:' ||l_list_price_override_flag);
3194 END IF;
3195
3196 FOR i IN 1..l_order_num_tbl.LAST LOOP
3197 IF l_order_num_tbl(i).order_number IS NOT NULL AND
3198 l_order_num_tbl(i).bill_to_cust_account_id IS NOT NULL AND
3199 l_order_num_tbl(i).date_ordered IS NOT NULL THEN
3200
3201 -- UPDATE tracing order lines to processed for this order to be processed
3202 UPDATE ozf_resale_lines_int_all
3203 SET status_code= 'PROCESSED'
3204 WHERE status_code = 'OPEN'
3205 AND order_number = l_order_num_tbl(i).order_number
3206 AND bill_to_cust_account_id = l_order_num_tbl(i).bill_to_cust_account_id
3207 AND date_ordered = l_order_num_tbl(i).date_ordered
3208 AND tracing_flag = 'T'
3209 AND resale_batch_id = p_resale_batch_id; -- bug 5222273
3210
3211 QP_Price_Request_Context.Set_Request_Id;
3212
3213 IF OZF_DEBUG_LOW_ON THEN
3214 ozf_utility_PVT.debug_message(l_api_name||'>> order_number = '||l_order_num_tbl(i).order_number||' (+)');
3215 ozf_utility_PVT.debug_message(l_api_name||'>> bill_to_cust_account_id = '||l_order_num_tbl(i).bill_to_cust_account_id);
3216 END IF;
3217
3218 --k:=1;
3219 OPEN order_set_csr( l_order_num_tbl(i).order_number
3220 , l_order_num_tbl(i).bill_to_cust_account_id
3221 , l_order_num_tbl(i).date_ordered
3222 );
3223
3224 LOOP
3225
3226 -- Before start process, clean up the data structures if necessary.
3227 IF l_order_set_tbl.EXISTS(1) THEN l_order_set_tbl.DELETE; END IF;
3228 IF l_line_tbl.EXISTS(1) THEN l_line_tbl.DELETE; END IF;
3229 IF l_ldets_tbl.EXISTS(1) THEN l_ldets_tbl.DELETE; END IF;
3230 IF l_related_lines_tbl.EXISTS(1) THEN l_related_lines_tbl.DELETE; END IF;
3231 IF OZF_ORDER_PRICE_PVT.G_LINE_REC_TBL.EXISTS(1) THEN OZF_ORDER_PRICE_PVT.G_LINE_REC_TBL.DELETE; END IF;
3232 IF OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL.EXISTS(1) THEN OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL.DELETE; END IF;
3233
3234 FETCH order_set_csr BULK COLLECT INTO l_order_set_tbl LIMIT G_BULK_LIMIT;
3235 IF l_order_set_tbl.FIRST IS NULL THEN
3236 EXIT;
3237 END IF;
3238 --CLOSE order_set_csr;
3239 /*
3240 FETCH order_set_csr BULK COLLECT INTO l_order_set_tbl;
3241 --LOOP
3242 -- FETCH order_set_csr INTO l_order_set_tbl(k);
3243 -- EXIT when order_set_csr%notfound;
3244 -- k := k+1;
3245 --END LOOP;
3246 CLOSE order_set_csr;
3247 */
3248
3249 IF OZF_DEBUG_LOW_ON THEN
3250 ozf_utility_PVT.debug_message(l_api_name||'>> order count = '||l_order_set_tbl.COUNT);
3251 END IF;
3252
3253 FOR j IN l_order_set_tbl.FIRST .. l_order_set_tbl.LAST
3254 LOOP
3255 -- Purge the any error message that might be there.
3256 BEGIN
3257 DELETE FROM ozf_resale_logs
3258 WHERE resale_id = l_order_set_tbl(j).resale_line_int_id
3259 AND resale_id_type = 'IFACE';
3260 EXCEPTION
3261 WHEN OTHERS THEN
3262 ozf_utility_pvt.error_message('OZF_DEL_RESALE_LOG_WRG');
3263 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3264 END;
3265
3266 IF OZF_DEBUG_LOW_ON THEN
3267 ozf_utility_PVT.debug_message(l_api_name||'>> building order line for inteface id: '||l_order_set_tbl(j).resale_line_int_id);
3268 END IF;
3269
3270 -- INSERT INTO l_line_tbl
3271 l_line_tbl(j).line_index := j;
3272 l_line_tbl(j).line_id := l_order_set_tbl(j).resale_line_int_id;
3273 l_line_tbl(j).line_type_code := OZF_ORDER_PRICE_PVT.G_ORDER_LINE_TYPE;
3274 l_line_tbl(j).pricing_effective_date := l_order_set_tbl(j).date_ordered;
3275 l_line_tbl(j).active_date_first := l_order_set_tbl(j).date_ordered;
3276 l_line_tbl(j).active_date_first_type := 'ORD';
3277 l_line_tbl(j).active_date_second := l_order_set_tbl(j).date_shipped;
3278 l_line_tbl(j).active_date_second_type := 'SHIP';
3279 l_line_tbl(j).line_quantity := ABS(l_order_set_tbl(j).quantity); -- BUG 4581928
3280 l_line_tbl(j).line_uom_code := l_order_set_tbl(j).uom_code;
3281 l_line_tbl(j).request_type_code := 'ONT';
3282 -- Pricing might be able to default it
3283 --mkothari 13-dec-2006
3284 --IF l_price_flag ='G' THEN
3285 IF l_list_price_override_flag = 'Y' THEN
3286 l_line_tbl(j).priced_quantity := ABS(l_order_set_tbl(j).quantity); -- BUG 4581928
3287 l_line_tbl(j).priced_uom_code := l_order_set_tbl(j).uom_code;
3288 l_line_tbl(j).unit_price := l_order_set_tbl(j).selling_price;
3289 END IF;
3290 l_line_tbl(j).currency_code := l_order_set_tbl(j).currency_code;
3291 IF l_order_set_tbl(j).price_list_id IS NULL THEN
3292 l_line_tbl(j).price_list_id := l_default_price_list_id;
3293 ELSE
3294 l_line_tbl(j).price_list_id := l_order_set_tbl(j).price_list_id;
3295 END IF;
3296 l_line_tbl(j).price_flag := l_price_flag;
3297 --mkothari 13-dec-2006
3298 l_line_tbl(j).list_price_override_flag := l_list_price_override_flag;
3299 l_line_tbl(j).pricing_status_code := QP_PREQ_GRP.G_STATUS_UNCHANGED;
3300 l_line_tbl(j).chargeback_int_id := l_order_set_tbl(j).resale_line_int_id;
3301 l_line_tbl(j).resale_table_type := 'IFACE'; -- bug 5360598
3302 -- l_line_tbl(j).UNIT_PRICE := NULL;
3303 -- l_line_tbl(j).PERCENT_PRICE := NULL;
3304 -- l_line_tbl(j).UOM_QUANTITY := NULL;
3305 -- l_line_tbl(j).ADJUSTED_UNIT_PRICE := NULL;
3306 -- l_line_tbl(j).UPD_ADJUSTED_UNIT_PRICE NUMBER:= FND_API.G_MISS_NUM,
3307 -- l_line_tbl(j).PROCESSED_FLAG VARCHAR2(1):= FND_API.G_MISS_CHAR,
3308 -- l_line_tbl(j).PROCESSING_ORDER := NULL;
3309 -- l_line_tbl(j).PRICING_STATUS_TEXT := NULL;
3310 -- l_line_tbl(j).ROUNDING_FLAG := NULL;
3311 -- l_line_tbl(j).ROUNDING_FACTOR := NULL;
3312 -- l_line_tbl(j).QUALIFIERS_EXIST_FLAG := NULL;
3313 -- l_line_tbl(j).PRICING_ATTRS_EXIST_FLAG := NULL;
3314 -- l_line_tbl(j).PL_VALIDATED_FLAG := NULL;
3315 -- l_line_tbl(j).PRICE_REQUEST_CODE := NULL;
3316 -- l_line_tbl(j).USAGE_PRICING_TYPE := NULL;
3317 -- l_line_tbl(j).LINE_CATEGORY := NULL;
3318
3319 -- populate the order_price global line arrary
3320 -- Here I only populate the values of the qualifiers for ONT.
3321 -- The real global structure will be populate in ozf_order_price_pvt.
3322 -- And it's value can be change in OZF_CHARGEBACK_ATTRMAP_PUB
3323
3324 OZF_ORDER_PRICE_PVT.G_LINE_REC_TBL(j).header_id := l_order_set_tbl.LAST + 1;
3325 OZF_ORDER_PRICE_PVT.G_LINE_REC_TBL(j).line_id := l_order_set_tbl(j).resale_line_int_id;
3326 OZF_ORDER_PRICE_PVT.G_LINE_REC_TBL(j).line_type_id := l_order_set_tbl(j).order_type_id;
3327 OZF_ORDER_PRICE_PVT.G_LINE_REC_TBL(j).inventory_item_id := l_order_set_tbl(j).inventory_item_id;
3328 OZF_ORDER_PRICE_PVT.G_LINE_REC_TBL(j).ordered_quantity := ABS(l_order_set_tbl(j).quantity); -- BUG 4581928
3329 OZF_ORDER_PRICE_PVT.G_LINE_REC_TBL(j).order_quantity_uom := l_order_set_tbl(j).uom_code;
3330 OZF_ORDER_PRICE_PVT.G_LINE_REC_TBL(j).invoice_to_org_id := l_order_set_tbl(j).bill_to_site_use_id;
3331 OZF_ORDER_PRICE_PVT.G_LINE_REC_TBL(j).ship_to_org_id := l_order_set_tbl(j).ship_to_site_use_id;
3332 OZF_ORDER_PRICE_PVT.G_LINE_REC_TBL(j).sold_to_org_id := l_order_set_tbl(j).bill_to_cust_account_id;
3333 --OZF_ORDER_PRICE_PVT.G_LINE_REC_TBL(j).sold_from_org_id := l_order_set_tbl(j).sold_from_cust_account_id;
3334 OZF_ORDER_PRICE_PVT.G_LINE_REC_TBL(j).invoice_to_party_id := l_order_set_tbl(j).bill_to_party_id;
3335 OZF_ORDER_PRICE_PVT.G_LINE_REC_TBL(j).invoice_to_party_site_id := l_order_set_tbl(j).bill_to_party_site_id;
3336 OZF_ORDER_PRICE_PVT.G_LINE_REC_TBL(j).ship_to_party_id := l_order_set_tbl(j).ship_to_party_id;
3337 OZF_ORDER_PRICE_PVT.G_LINE_REC_TBL(j).ship_to_party_site_id := l_order_set_tbl(j).ship_to_party_site_id;
3338 OZF_ORDER_PRICE_PVT.G_LINE_REC_TBL(j).price_list_id := l_line_tbl(j).price_list_id;
3339 OZF_ORDER_PRICE_PVT.G_LINE_REC_TBL(j).request_date := l_order_set_tbl(j).date_ordered;
3340 OZF_ORDER_PRICE_PVT.G_LINE_REC_TBL(j).actual_shipment_date := l_order_set_tbl(j).date_shipped;
3341 OZF_ORDER_PRICE_PVT.G_LINE_REC_TBL(j).pricing_date := l_order_set_tbl(j).date_ordered;
3342
3343 -- R12 Populate Global Resale Structure (+)
3344 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).batch_type := 'TP_ACCRUAL';
3345 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).qp_context_request_id := QP_Price_Request_Context.Get_Request_Id;
3346 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).line_index := l_line_tbl(j).line_index;
3347 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).resale_table_type := 'IFACE';
3348 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).line_id := l_order_set_tbl(j).resale_line_int_id;
3349 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).resale_transfer_type := l_order_set_tbl(j).resale_transfer_type;
3350 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).product_transfer_movement_type := l_order_set_tbl(j).product_transfer_movement_type;
3351 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).product_transfer_date := l_order_set_tbl(j).product_transfer_date;
3352 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).tracing_flag := l_order_set_tbl(j).tracing_flag;
3353 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).sold_from_cust_account_id := l_order_set_tbl(j).sold_from_cust_account_id;
3354 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).sold_from_site_id := l_order_set_tbl(j).sold_from_site_id;
3355 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).sold_from_contact_party_id := l_order_set_tbl(j).sold_from_contact_party_id;
3356 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).ship_from_cust_account_id := l_order_set_tbl(j).ship_from_cust_account_id;
3357 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).ship_from_site_id := l_order_set_tbl(j).ship_from_site_id;
3358 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).ship_from_contact_party_id := l_order_set_tbl(j).ship_from_contact_party_id;
3359 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).bill_to_party_id := l_order_set_tbl(j).bill_to_party_id;
3360 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).bill_to_party_site_id := l_order_set_tbl(j).bill_to_party_site_id;
3361 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).bill_to_contact_party_id := l_order_set_tbl(j).bill_to_contact_party_id;
3362 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).ship_to_party_id := l_order_set_tbl(j).ship_to_party_id;
3363 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).ship_to_party_site_id := l_order_set_tbl(j).ship_to_party_site_id;
3364 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).ship_to_contact_party_id := l_order_set_tbl(j).ship_to_contact_party_id;
3365 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).end_cust_party_id := l_order_set_tbl(j).end_cust_party_id;
3366 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).end_cust_site_use_id := l_order_set_tbl(j).end_cust_site_use_id;
3367 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).end_cust_site_use_code := l_order_set_tbl(j).end_cust_site_use_code;
3368 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).end_cust_party_site_id := l_order_set_tbl(j).end_cust_party_site_id;
3369 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).end_cust_contact_party_id := l_order_set_tbl(j).end_cust_contact_party_id;
3370 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).data_source_code := l_order_set_tbl(j).data_source_code;
3371 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).header_attribute_category := l_order_set_tbl(j).header_attribute_category;
3372 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).header_attribute1 := l_order_set_tbl(j).header_attribute1;
3373 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).header_attribute2 := l_order_set_tbl(j).header_attribute2;
3374 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).header_attribute3 := l_order_set_tbl(j).header_attribute3;
3375 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).header_attribute4 := l_order_set_tbl(j).header_attribute4;
3376 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).header_attribute5 := l_order_set_tbl(j).header_attribute5;
3377 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).header_attribute6 := l_order_set_tbl(j).header_attribute6;
3378 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).header_attribute7 := l_order_set_tbl(j).header_attribute7;
3379 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).header_attribute8 := l_order_set_tbl(j).header_attribute8;
3380 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).header_attribute9 := l_order_set_tbl(j).header_attribute9;
3381 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).header_attribute10 := l_order_set_tbl(j).header_attribute10;
3382 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).header_attribute11 := l_order_set_tbl(j).header_attribute11;
3383 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).header_attribute12 := l_order_set_tbl(j).header_attribute12;
3384 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).header_attribute13 := l_order_set_tbl(j).header_attribute13;
3385 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).header_attribute14 := l_order_set_tbl(j).header_attribute14;
3386 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).header_attribute15 := l_order_set_tbl(j).header_attribute15;
3387 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).line_attribute_category := l_order_set_tbl(j).line_attribute_category;
3388 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).line_attribute1 := l_order_set_tbl(j).line_attribute1;
3389 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).line_attribute2 := l_order_set_tbl(j).line_attribute2;
3390 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).line_attribute3 := l_order_set_tbl(j).line_attribute3;
3391 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).line_attribute4 := l_order_set_tbl(j).line_attribute4;
3392 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).line_attribute5 := l_order_set_tbl(j).line_attribute5;
3393 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).line_attribute6 := l_order_set_tbl(j).line_attribute6;
3394 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).line_attribute7 := l_order_set_tbl(j).line_attribute7;
3395 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).line_attribute8 := l_order_set_tbl(j).line_attribute8;
3396 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).line_attribute9 := l_order_set_tbl(j).line_attribute9;
3397 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).line_attribute10 := l_order_set_tbl(j).line_attribute10;
3398 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).line_attribute11 := l_order_set_tbl(j).line_attribute11;
3399 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).line_attribute12 := l_order_set_tbl(j).line_attribute12;
3400 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).line_attribute13 := l_order_set_tbl(j).line_attribute13;
3401 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).line_attribute14 := l_order_set_tbl(j).line_attribute14;
3402 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(j).line_attribute15 := l_order_set_tbl(j).line_attribute15;
3403 -- R12 Populate Global Resale Structure (-)
3404
3405 END LOOP; ---FOR i IN l_order_set_tbl.FIRST .. l_order_set_tbl.LAST
3406
3407
3408 IF OZF_DEBUG_LOW_ON THEN
3409 ozf_utility_PVT.debug_message(l_api_name||'>> building order header');
3410 END IF;
3411
3412 -- build summary line
3413 k := l_order_set_tbl.LAST + 1;
3414 l_line_tbl(k).line_index := k;
3415 l_line_tbl(k).line_id := NULL;
3416 l_line_tbl(k).line_type_code := OZF_ORDER_PRICE_PVT.G_ORDER_HEADER_TYPE;
3417 l_line_tbl(k).pricing_effective_date := l_order_set_tbl(1).date_ordered;
3418 l_line_tbl(k).active_date_first := l_order_set_tbl(1).date_ordered;
3419 l_line_tbl(k).active_date_first_type := 'ORD'; -- Change because of ONT QP order 'NO TYPE';
3420 l_line_tbl(k).active_date_second := l_order_set_tbl(1).date_shipped;
3421 l_line_tbl(k).active_date_second_type := 'SHIP'; -- change because of ONT QP order 'NO TYPE';
3422 l_line_tbl(k).request_type_code := 'ONT';
3423 l_line_tbl(k).currency_code := l_order_set_tbl(1).currency_code;
3424 l_line_tbl(k).price_list_id := l_line_tbl(1).price_list_id;
3425 l_line_tbl(k).price_flag := l_price_flag;
3426
3427 --mkothari 13-dec-2006
3428 l_line_tbl(k).list_price_override_flag := l_list_price_override_flag;
3429 l_line_tbl(k).pricing_status_code := QP_PREQ_GRP.G_STATUS_UNCHANGED;
3430 l_line_tbl(k).chargeback_int_id := l_order_set_tbl(1).resale_line_int_id;
3431 l_line_tbl(k).resale_table_type := 'IFACE'; -- bug 5360598
3432
3433 -- l_line_tbl(k).LINE_QUANTITY := NULL;
3434 -- l_line_tbl(k).LINE_UOM_CODE := NULL;
3435 -- l_line_tbl(k).PRICED_QUANTITY := NULL;
3436 -- l_line_tbl(k).PRICED_UOM_CODE := NULL;
3437 -- l_line_tbl(j).UNIT_PRICE := l_order_set_tbl(j).
3438 -- l_line_tbl(j).PERCENT_PRICE := l_order_set_tbl(j).
3439 -- l_line_tbl(j).UOM_QUANTITY := l_order_set_tbl(j).
3440 -- l_line_tbl(j).ADJUSTED_UNIT_PRICE := l_order_set_tbl(j).
3441 -- l_line_tbl(j).UPD_ADJUSTED_UNIT_PRICE NUMBER:= FND_API.G_MISS_NUM,
3442 -- l_line_tbl(j).PROCESSED_FLAG VARCHAR2(1):= FND_API.G_MISS_CHAR,
3443 -- l_line_tbl(j).PROCESSING_ORDER := NULL;
3444 -- l_line_tbl(j).PRICING_STATUS_TEXT := NULL;
3445 -- l_line_tbl(j).ROUNDING_FLAG := NULL;
3446 -- l_line_tbl(j).ROUNDING_FACTOR := NULL;
3447 -- l_line_tbl(j).QUALIFIERS_EXIST_FLAG := NULL;
3448 -- l_line_tbl(j).PRICING_ATTRS_EXIST_FLAG := NULL;
3449 -- l_line_tbl(j).PL_VALIDATED_FLAG := NULL;
3450 -- l_line_tbl(j).PRICE_REQUEST_CODE := NULL;
3451 -- l_line_tbl(j).USAGE_PRICING_TYPE := NULL;
3452 -- l_line_tbl(j).LINE_CATEGORY := NULL;
3453
3454 -- populate the order_price global header structure
3455 -- Here I only populate the values of the qualifiers for ONT.
3456 -- The real global structure will be populate in ozf_order_price_pvt.
3457 -- And it's value can be change in OZF_CHARGEBACK_ATTRMAP_PUB
3458
3459 -- Might be able to add more value here.
3460 OZF_ORDER_PRICE_PVT.G_HEADER_REC.header_id := k;
3461 OZF_ORDER_PRICE_PVT.G_HEADER_REC.order_type_id := l_order_set_tbl(1).order_type_id;
3462 OZF_ORDER_PRICE_PVT.G_HEADER_REC.sold_to_org_id := l_order_set_tbl(1).bill_to_cust_account_id;
3463 OZF_ORDER_PRICE_PVT.G_HEADER_REC.invoice_to_org_id := l_order_set_tbl(1).bill_to_site_use_id;
3464 OZF_ORDER_PRICE_PVT.G_HEADER_REC.ship_to_org_id := l_order_set_tbl(1).ship_to_site_use_id;
3465 OZF_ORDER_PRICE_PVT.G_HEADER_REC.invoice_to_party_id := l_order_set_tbl(1).bill_to_party_id;
3466 OZF_ORDER_PRICE_PVT.G_HEADER_REC.invoice_to_party_site_id := l_order_set_tbl(1).bill_to_party_site_id;
3467 OZF_ORDER_PRICE_PVT.G_HEADER_REC.ship_to_party_id := l_order_set_tbl(1).ship_to_party_id;
3468 OZF_ORDER_PRICE_PVT.G_HEADER_REC.ship_to_party_site_id := l_order_set_tbl(1).ship_to_party_site_id;
3469 OZF_ORDER_PRICE_PVT.G_HEADER_REC.price_list_id := l_line_tbl(1).price_list_id;
3470 OZF_ORDER_PRICE_PVT.G_HEADER_REC.ordered_date := l_order_set_tbl(1).date_ordered;
3471 OZF_ORDER_PRICE_PVT.G_HEADER_REC.request_date := l_order_set_tbl(1).date_ordered;
3472 OZF_ORDER_PRICE_PVT.G_HEADER_REC.pricing_date := l_order_set_tbl(1).date_ordered;
3473
3474 IF OZF_DEBUG_LOW_ON THEN
3475 ozf_utility_PVT.debug_message(l_api_name||'>> Calling Get_Order_');
3476 END IF;
3477
3478 OZF_ORDER_PRICE_PVT.Get_Order_Price (
3479 p_api_version => 1.0
3480 ,p_init_msg_list => FND_API.G_FALSE
3481 ,p_commit => FND_API.G_FALSE
3482 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
3483 ,x_return_status => l_return_status
3484 ,x_msg_data => l_msg_data
3485 ,x_msg_count => l_msg_count
3486 ,p_control_rec => l_control_rec
3487 ,xp_line_tbl => l_line_tbl
3488 ,x_ldets_tbl => l_ldets_tbl
3489 ,x_related_lines_tbl => l_related_lines_tbl
3490 );
3491 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3492 ozf_utility_pvt.error_message('OZF_GET_ORDER_PRIC_ERR');
3493
3494 BEGIN
3495 UPDATE ozf_resale_lines_int
3496 SET status_code = 'DISPUTED'
3497 WHERE status_code = 'OPEN'
3498 AND order_number = l_order_num_tbl(i).order_number
3499 AND bill_to_cust_account_id = l_order_num_tbl(i).bill_to_cust_account_id
3500 AND date_ordered = l_order_num_tbl(i).date_ordered
3501 AND resale_batch_id = p_resale_batch_id;
3502 EXCEPTION
3503 WHEN OTHERS THEN
3504 ozf_utility_pvt.error_message('OZF_UPD_RESALE_INT_WRG');
3505 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3506 END;
3507
3508 FOR p in 1..l_order_set_tbl.LAST
3509 LOOP
3510 BEGIN
3511 OPEN OZF_RESALE_COMMON_PVT.g_log_id_csr;
3512 FETCH OZF_RESALE_COMMON_PVT.g_log_id_csr INTO l_log_id;
3513 CLOSE OZF_RESALE_COMMON_PVT.g_log_id_csr;
3514
3515 -- julou bug 6317120. get org_id from table
3516 OPEN OZF_RESALE_COMMON_PVT.gc_iface_org_id(l_order_set_tbl(p).resale_line_int_id);
3517 FETCH OZF_RESALE_COMMON_PVT.gc_iface_org_id INTO l_org_id;
3518 CLOSE OZF_RESALE_COMMON_PVT.gc_iface_org_id;
3519
3520 OZF_RESALE_LOGS_PKG.Insert_Row(
3521 px_resale_log_id => l_log_id,
3522 p_resale_id => l_order_set_tbl(p).resale_line_int_id,
3523 p_resale_id_type => 'IFACE',
3524 p_error_code => 'OZF_GET_ORDER_PRIC_ERR',
3525 p_error_message => FND_MESSAGE.get_string('OZF','OZF_GET_ORDER_PRIC_ERR'),
3526 p_column_name => NULL,
3527 p_column_value => NULL,
3528 --px_org_id => OZF_RESALE_COMMON_PVT.g_org_id
3529 px_org_id => l_org_id
3530 );
3531 EXCEPTION
3532 WHEN OTHERS THEN
3533 ozf_utility_pvt.error_message('OZF_INS_RESALE_LOG_WRG');
3534 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3535 END;
3536 END LOOP;
3537
3538 IF OZF_DEBUG_LOW_ON THEN
3539 ozf_utility_PVT.debug_message(l_api_name||'>> Get_Order_Price Failed!');
3540 END IF;
3541
3542 GOTO END_LOOP;
3543 END IF;
3544
3545 Process_Pricing_Result(
3546 p_resale_batch_id => p_resale_batch_id,
3547 p_line_tbl => l_line_tbl,
3548 p_caller_type => 'IFACE',
3549 x_return_status => l_return_status
3550 );
3551 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3552 FND_MSG_PUB.Count_And_Get (
3553 p_encoded => FND_API.G_FALSE,
3554 p_count => l_temp_count,
3555 p_data => l_temp_data
3556 );
3557
3558 FND_MSG_PUB.Get(
3559 p_msg_index => l_temp_count,
3560 p_encoded => FND_API.G_FALSE,
3561 p_data => l_temp_data,
3562 p_msg_index_out => l_temp_count
3563 );
3564
3565 FOR p in 1..l_order_set_tbl.LAST LOOP
3566 BEGIN
3567 OPEN OZF_RESALE_COMMON_PVT.g_log_id_csr;
3568 FETCH OZF_RESALE_COMMON_PVT.g_log_id_csr INTO l_log_id;
3569 CLOSE OZF_RESALE_COMMON_PVT.g_log_id_csr;
3570
3571 -- julou bug 6317120. get org_id from table
3572 OPEN OZF_RESALE_COMMON_PVT.gc_iface_org_id(l_order_set_tbl(p).resale_line_int_id);
3573 FETCH OZF_RESALE_COMMON_PVT.gc_iface_org_id INTO l_org_id;
3574 CLOSE OZF_RESALE_COMMON_PVT.gc_iface_org_id;
3575
3576 OZF_RESALE_LOGS_PKG.Insert_Row(
3577 px_resale_log_id => l_log_id,
3578 p_resale_id => l_order_set_tbl(p).resale_line_int_id,
3579 p_resale_id_type => 'IFACE',
3580 p_error_code => 'OZF_PRIC_RESULT_ERR',
3581 p_error_message => FND_MESSAGE.get_string('OZF','OZF_PRIC_RESULT_ERR'),
3582 p_column_name => NULL,
3583 p_column_value => NULL,
3584 --px_org_id => OZF_RESALE_COMMON_PVT.g_org_id
3585 px_org_id => l_org_id
3586 );
3587 EXCEPTION
3588 WHEN OTHERS THEN
3589 ozf_utility_pvt.error_message('OZF_INS_RESALE_LOG_WRG');
3590 END;
3591 END LOOP;
3592
3593 BEGIN
3594 UPDATE ozf_resale_lines_int_all
3595 SET status_code = 'DISPUTED'
3596 WHERE status_code = 'OPEN'
3597 AND order_number = l_order_num_tbl(i).order_number
3598 AND bill_to_cust_account_id = l_order_num_tbl(i).bill_to_cust_account_id
3599 AND date_ordered = l_order_num_tbl(i).date_ordered
3600 AND resale_batch_id = p_resale_batch_id;
3601 EXCEPTION
3602 WHEN OTHERS THEN
3603 ozf_utility_pvt.error_message('OZF_UPD_RESALE_INT_WRG');
3604 END;
3605
3606 GOTO END_LOOP;
3607 END IF;
3608 /* ????????????
3609 -- non tracing data and non dup data process successful
3610 IF l_header_id is NULL THEN
3611 -- create a header_id for duplicated adjs and tracing_data
3612 END IF;
3613
3614 OPEN dup adjustment
3615
3616 create link between batch_id and duplicated_line_id
3617
3618 OPEN tracing
3619 If status_code = duplicated then
3620 create a link between batch_id and duplicated_line_id
3621 else
3622 create a line and a link.
3623 END
3624
3625 */
3626
3627
3628
3629 IF OZF_DEBUG_LOW_ON THEN
3630 ozf_utility_PVT.debug_message(l_api_name||'>>- Success and Committed: Processing order for order number:'||l_order_num_tbl(i).order_number||'(-)');
3631 ozf_utility_PVT.debug_message(l_api_name||'>>- and customer:'||l_order_num_tbl(i).bill_to_cust_account_id||'(-)');
3632 END IF;
3633
3634 -- commit the data created by processing these G_BULK_LIMIT (default 500) lines
3635 IF G_ALLOW_INTER_COMMIT = 'Y' THEN
3636 COMMIT;
3637 END IF;
3638
3639 << END_LOOP >>
3640 null;
3641
3642 EXIT WHEN order_set_csr%NOTFOUND;
3643 END LOOP; -- OPEN order_set_csr
3644 CLOSE order_set_csr;
3645 END IF; -- END if for order_NUMBER, bill_to cust not NULL
3646 END LOOP; -- END LOOP FOR l_order_num_tbl
3647
3648 ELSE -- IF l_order_num_tbl.EXISTS(1) THEN
3649
3650 IF OZF_DEBUG_LOW_ON THEN
3651 ozf_utility_PVT.debug_message(l_api_name||'>> No Order to process <<');
3652 END IF;
3653 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' There is no valid order to process.');
3654
3655 END IF; -- IF l_order_num_tbl.EXISTS(1) THEN
3656
3657
3658 -- [BUG 4233341 FIXING]: add output file
3659 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' Disputed Lines After Processing Order:');
3660 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' ');
3661 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' Interface');
3662 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' Line Id Dispute Code Error Column Name Column Value');
3663 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' --------- -------------------------------- -------------------------------- -------------------- ------------');
3664
3665 --i_output_idx := 1;
3666 IF l_output_dispute_line_tbl.EXISTS(1) THEN
3667 l_output_dispute_line_tbl.DELETE;
3668 END IF;
3669 OPEN csr_out_dispute_pre_proc(p_resale_batch_id);
3670 FETCH csr_out_dispute_pre_proc BULK COLLECT INTO l_output_dispute_line_tbl;
3671 --LOOP
3672 -- FETCH csr_out_dispute_pre_proc INTO l_output_dispute_line_tbl(i_output_idx);
3673 -- EXIT WHEN csr_out_dispute_pre_proc%NOTFOUND;
3674 -- i_output_idx := i_output_idx + 1;
3675 --END LOOP;
3676 /*
3677 FETCH csr_out_dispute_pre_proc BULK COLLECT INTO l_output_dispute_code_tbl
3678 , l_output_dispute_bill_to_tbl
3679 , l_output_dispute_order_tbl
3680 , l_output_dispute_order_date_tbl
3681 , l_output_dispute_item_tbl;
3682 */
3683 CLOSE csr_out_dispute_pre_proc;
3684
3685 FOR i_output_idx IN 1..l_output_dispute_line_tbl.COUNT LOOP
3686 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' '
3687 ||RPAD(l_output_dispute_line_tbl(i_output_idx).id, 10, ' ')
3688 ||RPAD(l_output_dispute_line_tbl(i_output_idx).dispute_code, 33, ' ')
3689 ||RPAD(l_output_dispute_line_tbl(i_output_idx).error_message, 34, ' ')
3690 ||RPAD(l_output_dispute_line_tbl(i_output_idx).column_name, 21, ' ')
3691 ||RPAD(l_output_dispute_line_tbl(i_output_idx).column_value, 15, ' ')
3692 );
3693 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' ');
3694 END LOOP;
3695 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' ');
3696
3697 -- [BEGIN OF BUG 4233341 FIXING]
3698 -- delink all the new/processed/disputed lines
3699 UPDATE ozf_resale_lines_int_all
3700 SET resale_batch_id = null
3701 , request_id = null
3702 WHERE resale_batch_id = p_resale_batch_id
3703 AND status_code IN ('NEW', 'OPEN', 'DISPUTED'); -- 'PROCESSED'
3704
3705 OPEN csr_valid_line_count(p_resale_batch_id);
3706 FETCH csr_valid_line_count INTO l_valid_line_count;
3707 CLOSE csr_valid_line_count;
3708
3709 IF l_valid_line_count > 0 THEN
3710 -- close this batch
3711 UPDATE ozf_resale_batches_all
3712 SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_CLOSED
3713 , batch_count = l_valid_line_count
3714 WHERE resale_batch_id = p_resale_batch_id;
3715
3716 OPEN csr_get_batch_number(p_resale_batch_id);
3717 FETCH csr_get_batch_number INTO l_batch_number;
3718 CLOSE csr_get_batch_number;
3719
3720 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' Batch Successfully Created and Closed:');
3721 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' ');
3722 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' Batch Number: '||l_batch_number);
3723 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' Successfully Processed: '||l_valid_line_count);
3724 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' ');
3725
3726 ELSE
3727 DELETE FROM ozf_resale_batches_all
3728 WHERE resale_batch_id = p_resale_batch_id;
3729 END IF;
3730 -- [END OF BUG 4233341 FIXING]
3731
3732
3733 -- Debug Message
3734 IF OZF_DEBUG_HIGH_ON THEN
3735 OZF_UTILITY_PVT.debug_message(p_message_text => l_full_name||': end');
3736 END IF;
3737 --Standard call to get message count and if count=1, get the message
3738 FND_MSG_PUB.Count_And_Get (
3739 p_encoded => FND_API.G_FALSE,
3740 p_count => x_msg_count,
3741 p_data => x_msg_data
3742 );
3743
3744 EXCEPTION
3745 WHEN FND_API.G_EXC_ERROR THEN
3746 x_return_status := FND_API.G_RET_STS_ERROR;
3747 -- Standard call to get message count and if count=1, get the message
3748 FND_MSG_PUB.Count_And_Get (
3749 p_encoded => FND_API.G_FALSE,
3750 p_count => x_msg_count,
3751 p_data => x_msg_data
3752 );
3753 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3754 ROLLBACK TO Process_Order;
3755 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3756
3757 -- Standard call to get message count and if count=1, get the message
3758 FND_MSG_PUB.Count_And_Get (
3759 p_encoded => FND_API.G_FALSE,
3760 p_count => x_msg_count,
3761 p_data => x_msg_data
3762 );
3763 WHEN OTHERS THEN
3764 ROLLBACK TO Process_Order;
3765 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3766
3767 -- Standard call to get message count and if count=1, get the message
3768 FND_MSG_PUB.Count_And_Get (
3769 p_encoded => FND_API.G_FALSE,
3770 p_count => x_msg_count,
3771 p_data => x_msg_data
3772 );
3773 END Process_Order;
3774
3775
3776 ---------------------------------------------------------------------
3777 -- PROCEDURE
3778 -- Process_TP_ACCRUAL
3779 --
3780 -- PURPOSE
3781 -- This function is for backword compatable. It is called by the concurrent program.
3782 --
3783 --
3784 -- PARAMETERS
3785 --
3786 --
3787 -- NOTES
3788 --
3789 -- HISTORY
3790 -- SEP-02-2008 ateotia bug # 7375849 fixed. FP:11510-R12 7369835
3791 -- THIRD PARTY ACCRUAL FROM INTERFACE TABLE FINSIHES WITH ERROR
3792 ---------------------------------------------------------------------
3793 PROCEDURE Process_TP_ACCRUAL (
3794 p_api_version IN NUMBER
3795 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
3796 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
3797 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
3798 ,p_data_source_code IN VARCHAR2 := NULL
3799 ,x_return_status OUT NOCOPY VARCHAR2
3800 ,x_msg_data OUT NOCOPY VARCHAR2
3801 ,x_msg_count OUT NOCOPY NUMBER
3802 )
3803 IS
3804 l_api_name CONSTANT VARCHAR2(30) := 'Process_TP_ACCRUAL';
3805 l_api_version CONSTANT NUMBER := 1.0;
3806 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
3807 --
3808 l_return_status VARCHAR2(30);
3809 l_msg_data VARCHAR2(2000);
3810 l_msg_count NUMBER;
3811
3812 /*
3813 CURSOR batch_set_csr(p_data_source_code in VARCHAR2,
3814 p_start_date in date,
3815 p_END_date in date) IS
3816 SELECT resale_batch_id
3817 FROM ozf_resale_batches
3818 WHERE data_source_code = p_data_source_code;
3819 */
3820
3821 TYPE batch_set_tbl_type is TABLE OF NUMBER INDEX BY binary_integer;
3822 l_batch_tbl batch_set_tbl_type;
3823
3824 i NUMBER:=1;
3825
3826 CURSOR account_id_csr (p_id NUMBER)IS
3827 SELECT distinct sold_from_cust_account_id, org_id, currency_code
3828 FROM ozf_resale_lines_int
3829 WHERE resale_batch_id IS NULL
3830 AND request_id = p_id;
3831
3832 l_sold_from_cust_id_tbl OZF_RESALE_COMMON_PVT.number_tbl_type;
3833
3834 -- bug # 7375849 fixed by ateotia (+)
3835 --l_sold_from_cust_name_tbl OZF_RESALE_COMMON_PVT.varchar_tbl_type;
3836 TYPE varchar_tbl_type IS TABLE OF VARCHAR2(360) INDEX BY BINARY_INTEGER;
3837 l_sold_from_cust_name_tbl varchar_tbl_type;
3838 -- bug # 7375849 fixed by ateotia (-)
3839
3840 l_org_id_tbl OZF_RESALE_COMMON_PVT.number_tbl_type;
3841 l_currency_code_tbl OZF_RESALE_COMMON_PVT.varchar_tbl_type;
3842
3843 CURSOR party_id_csr(p_id number)IS
3844 SELECT hca.party_id
3845 -- bug # 7375849 fixed by ateotia (+)
3846 --, substr(hp.party_name, 1,30)
3847 , hp.party_name
3848 -- bug # 7375849 fixed by ateotia (-)
3849 FROM hz_cust_accounts hca
3850 , hz_parties hp
3851 WHERE hca.cust_account_id = p_id
3852 AND hca.party_id = hp.party_id;
3853
3854 l_party_id number;
3855 l_party_name varchar2(360);
3856
3857 l_obj_number NUMBER := 1.0;
3858
3859 CURSOR resale_batch_id_csr IS
3860 SELECT ozf_resale_batches_all_s.nextval
3861 FROM dual;
3862 l_resale_batch_id NUMBER;
3863
3864 CURSOR resale_batch_number_csr IS
3865 SELECT to_char(ozf_resale_batch_number_s.nextval)
3866 FROM dual;
3867 l_resale_batch_number VARCHAR2(30);
3868
3869 CURSOR line_info_csr(p_id NUMBER,
3870 p_org_id NUMBER,
3871 p_currency_code VARCHAR2) IS
3872 SELECT orli.created_from
3873 , orli.data_source_code
3874 , orli.sold_from_cust_account_id
3875 , orli.sold_from_site_id
3876 , orli.sold_from_contact_party_id
3877 , orli.sold_from_contact_name
3878 , orli.sold_from_email
3879 , orli.sold_from_phone
3880 , orli.sold_from_fax
3881 ,orli.currency_code
3882 FROM ozf_resale_lines_int orli
3883 WHERE orli.resale_batch_id IS NULL
3884 AND orli.sold_from_cust_account_id = p_id
3885 AND orli.request_id = G_CONC_REQUEST_ID
3886 AND orli.org_id = p_org_id
3887 AND orli.currency_code = p_currency_code
3888 AND rownum = 1;
3889
3890 l_created_from VARCHAR2(30);
3891 l_data_source_code VARCHAR2(30);
3892 l_sold_from_cust_account_id NUMBER;
3893 l_sold_from_site_id NUMBER;
3894 l_sold_from_contact_party_id NUMBER;
3895 l_sold_from_contact_name VARCHAR2(240);
3896 l_sold_from_email VARCHAR2(240);
3897 l_sold_from_phone VARCHAR2(240);
3898 l_sold_from_fax VARCHAR2(240);
3899 l_currency_code VARCHAR2(30);
3900
3901 CURSOR start_end_date_csr( p_account_id NUMBER,
3902 p_org_id NUMBER,
3903 p_currency_code VARCHAR2) IS
3904 SELECT MIN(date_ordered), MAX(date_ordered)
3905 FROM ozf_resale_lines_int_all
3906 WHERE sold_from_cust_account_id = p_account_id
3907 AND request_id = FND_GLOBAL.CONC_REQUEST_ID
3908 AND org_id = p_org_id
3909 AND currency_code = p_currency_code;
3910
3911 l_start_date date;
3912 l_end_date date;
3913
3914 BEGIN
3915 -- Standard BEGIN of API savepoint
3916 SAVEPOINT Process_TP_ACCRUAL;
3917 -- Standard call to check for call compatibility.
3918 IF NOT FND_API.Compatible_API_Call (
3919 l_api_version,
3920 p_api_version,
3921 l_api_name,
3922 G_PKG_NAME)
3923 THEN
3924 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3925 END IF;
3926
3927 --Initialize message list if p_init_msg_list is TRUE.
3928 IF FND_API.To_Boolean (p_init_msg_list) THEN
3929 FND_MSG_PUB.initialize;
3930 END IF;
3931
3932 -- Debug Message
3933 IF OZF_DEBUG_HIGH_ON THEN
3934 OZF_UTILITY_PVT.debug_message(p_message_text => l_full_name||': Start');
3935 END IF;
3936
3937 -- Initialize API return status to sucess
3938 x_return_status := FND_API.G_RET_STS_SUCCESS;
3939
3940 /*
3941 IF p_data_source_code is NULL THEN
3942 ozf_utility_pvt.error_message('OZF_RESALE_TP_SOURCE_NULL');
3943 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3944 END IF;
3945 */
3946
3947 -- Mark all the records that will be processed.
3948 IF p_data_source_code is null OR
3949 p_data_source_code = 'ALL' THEN
3950
3951 IF p_data_source_code is null THEN
3952 IF OZF_DEBUG_LOW_ON THEN
3953 ozf_utility_PVT.debug_message('source code is null, id:' || FND_GLOBAL.CONC_REQUEST_ID );
3954 END IF;
3955 ELSE
3956 IF OZF_DEBUG_LOW_ON THEN
3957 ozf_utility_PVT.debug_message('source code is '|| p_data_source_code||', id:' || FND_GLOBAL.CONC_REQUEST_ID );
3958 END IF;
3959 END IF;
3960 UPDATE ozf_resale_lines_int_all
3961 SET request_id = G_CONC_REQUEST_ID
3962 , dispute_code = null
3963 , program_application_id = FND_GLOBAL.PROG_APPL_ID
3964 , program_update_date = SYSDATE
3965 , program_id = FND_GLOBAL.CONC_PROGRAM_ID
3966 WHERE resale_batch_id IS NULL
3967 AND request_id IS NULL
3968 AND org_id = MO_GLOBAL.get_current_org_id();
3969
3970 ELSE
3971 IF OZF_DEBUG_LOW_ON THEN
3972 ozf_utility_PVT.debug_message('source code is '|| p_data_source_code||', id:' || FND_GLOBAL.CONC_REQUEST_ID );
3973 END IF;
3974 UPDATE ozf_resale_lines_int_all
3975 SET request_id = G_CONC_REQUEST_ID
3976 , dispute_code = null
3977 , program_application_id = FND_GLOBAL.PROG_APPL_ID
3978 , program_update_date = SYSDATE
3979 , program_id = FND_GLOBAL.CONC_PROGRAM_ID
3980 WHERE resale_batch_id IS NULL
3981 AND data_source_code = p_data_source_code
3982 AND request_id IS NULL
3983 AND org_id = MO_GLOBAL.get_current_org_id();
3984 END IF;
3985
3986 DELETE FROM ozf_resale_logs_all
3987 WHERE resale_id IN (SELECT resale_line_int_id
3988 FROM ozf_resale_lines_int
3989 WHERE request_id = G_CONC_REQUEST_ID)
3990 AND resale_id_type = 'IFACE';
3991
3992 -- [BUG 4233341 FIXING: Add program output file]
3993 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Request Id : '||G_CONC_REQUEST_ID);
3994 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Data Source Code : '||p_data_source_code);
3995 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '*------------------------------------------------------------------------------------------------------*');
3996
3997 -- list of partner_party_id based on the sold_from_cust_account_id
3998 --Add org_id, currency_code
3999 OPEN account_id_csr(G_CONC_REQUEST_ID);
4000 FETCH account_id_csr BULK COLLECT INTO l_sold_from_cust_id_tbl, l_org_id_tbl, l_currency_code_tbl;
4001 CLOSE account_id_csr;
4002
4003 IF l_sold_from_cust_id_tbl.exists(1) THEN
4004 FOR j in 1..l_sold_from_cust_id_tbl.LAST LOOP
4005
4006 l_party_id := NULL;
4007 l_party_name := NULL;
4008
4009 OPEN party_id_csr (l_sold_from_cust_id_tbl(j));
4010 FETCH party_id_csr into l_party_id, l_party_name;
4011 CLOSE party_id_csr;
4012
4013 --create a batch header with resale_batch_id
4014 OPEN resale_batch_id_csr;
4015 FETCH resale_batch_id_csr into l_resale_batch_id;
4016 CLOSE resale_batch_id_csr;
4017
4018 OPEN resale_batch_number_csr;
4019 FETCH resale_batch_number_csr into l_resale_batch_number;
4020 CLOSE resale_batch_number_csr;
4021
4022 -- get one line
4023 OPEN line_info_csr(l_sold_from_cust_id_tbl(j),
4024 l_org_id_tbl(j),
4025 l_currency_code_tbl(j));
4026 FETCH line_info_csr INTO l_created_from,
4027 l_data_source_code,
4028 l_sold_from_cust_account_id,
4029 l_sold_from_site_id,
4030 l_sold_from_contact_party_id,
4031 l_sold_from_contact_name,
4032 l_sold_from_email,
4033 l_sold_from_phone,
4034 l_sold_from_fax,
4035 l_currency_code;
4036 CLOSE line_info_csr;
4037
4038 OPEN start_end_date_csr(l_sold_from_cust_id_tbl(j),
4039 l_org_id_tbl(j),
4040 l_currency_code_tbl(j));
4041 FETCH start_end_date_csr into l_start_date, l_end_date;
4042 CLOSE start_end_date_csr;
4043
4044 OZF_RESALE_BATCHES_PKG.Insert_Row(
4045 px_resale_batch_id => l_resale_batch_id,
4046 px_object_version_number => l_obj_number,
4047 p_last_update_date => SYSdate,
4048 p_last_updated_by => NVL(FND_GLOBAL.user_id,-1),
4049 p_creation_date => SYSdate,
4050 p_request_id => G_CONC_REQUEST_ID,
4051 p_created_by => NVL(FND_GLOBAL.user_id,-1),
4052 p_last_update_login => NVL(FND_GLOBAL.conc_login_id,-1),
4053 p_program_application_id => FND_GLOBAL.PROG_APPL_ID,
4054 p_program_update_date => SYSdate,
4055 p_program_id => FND_GLOBAL.CONC_PROGRAM_ID,
4056 p_created_from => l_created_from,
4057 p_batch_number => l_resale_batch_number,
4058 p_batch_type => OZF_RESALE_COMMON_PVT.G_TP_ACCRUAL,
4059 p_batch_count => NULL,
4060 p_year => NULL,
4061 p_month => NULL,
4062 p_report_date => trunc(sysdate),
4063 p_report_start_date => trunc(l_start_date),
4064 p_report_end_date => trunc(l_end_date),
4065 p_status_code => OZF_RESALE_COMMON_PVT.G_BATCH_NEW,
4066 p_data_source_code => l_data_source_code,
4067 p_reference_type => NULL,
4068 p_reference_number => NULL,
4069 p_comments => NULL,
4070 p_partner_claim_number => NULL,
4071 p_transaction_purpose_code => NULL,
4072 p_transaction_type_code => NULL,
4073 p_partner_type => NULL,
4074 p_partner_id => NULL,
4075 p_partner_party_id => l_party_id,
4076 p_partner_cust_account_id => l_sold_from_cust_id_tbl(j) ,
4077 p_partner_site_id => l_sold_from_site_id,
4078 p_partner_contact_party_id => l_sold_from_contact_party_id ,
4079 p_partner_contact_name => l_sold_from_contact_name,
4080 p_partner_email => l_sold_from_email,
4081 p_partner_phone => l_sold_from_phone,
4082 p_partner_fax => l_sold_from_fax,
4083 p_header_tolerance_operand => NULL,
4084 p_header_tolerance_calc_code => NULL,
4085 p_line_tolerance_operand => NULL,
4086 p_line_tolerance_calc_code => NULL,
4087 p_currency_code => l_currency_code_tbl(j),
4088 p_claimed_amount => NULL,
4089 p_allowed_amount => NULL,
4090 p_paid_amount => NULL,
4091 p_disputed_amount => NULL,
4092 p_accepted_amount => NULL,
4093 p_lines_invalid => NULL,
4094 p_lines_w_tolerance => NULL,
4095 p_lines_disputed => NULL,
4096 p_batch_set_id_code => NULL,
4097 p_credit_code => NULL,
4098 p_credit_advice_date => NULL,
4099 p_purge_flag => NULL,
4100 p_attribute_category => NULL,
4101 p_attribute1 => NULL,
4102 p_attribute2 => NULL,
4103 p_attribute3 => NULL,
4104 p_attribute4 => NULL,
4105 p_attribute5 => NULL,
4106 p_attribute6 => NULL,
4107 p_attribute7 => NULL,
4108 p_attribute8 => NULL,
4109 p_attribute9 => NULL,
4110 p_attribute10 => NULL,
4111 p_attribute11 => NULL,
4112 p_attribute12 => NULL,
4113 p_attribute13 => NULL,
4114 p_attribute14 => NULL,
4115 p_attribute15 => NULL,
4116 px_org_id => l_org_id_tbl(j));
4117
4118 l_batch_tbl(i) := l_resale_batch_id;
4119 l_sold_from_cust_name_tbl(i) := l_party_name;
4120 i := i +1;
4121
4122 UPDATE ozf_resale_lines_int_all orli
4123 SET resale_batch_id = l_resale_batch_id
4124 WHERE orli.sold_from_cust_account_id = l_sold_from_cust_id_tbl(j)
4125 AND orli.org_id = l_org_id_tbl(j)
4126 AND orli.currency_code = l_currency_code_tbl(j)
4127 AND orli.request_id = G_CONC_REQUEST_ID;
4128 END LOOP;
4129 END IF;
4130
4131 IF l_batch_tbl.EXISTS(1) THEN
4132 FOR i in 1..l_batch_tbl.LAST LOOP
4133 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '* Sold From Customer: '||l_sold_from_cust_name_tbl(i));
4134 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '* Currency Code: '||l_currency_code_tbl(i));
4135 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' ');
4136
4137 Process_order(
4138 p_api_version => 1
4139 ,p_init_msg_list => FND_API.G_FALSE
4140 ,p_commit => FND_API.G_FALSE
4141 ,p_validation_level=> FND_API.G_VALID_LEVEL_FULL
4142 ,p_resale_batch_id => l_batch_tbl(i)
4143 ,x_return_status => l_return_status
4144 ,x_msg_data => l_msg_data
4145 ,x_msg_count => l_msg_count
4146 );
4147 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4148 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4149 END IF;
4150 END LOOP;
4151 END IF; -- END if l_batch_tbl.EXISTS
4152
4153 -- Debug Message
4154 IF OZF_DEBUG_HIGH_ON THEN
4155 OZF_UTILITY_PVT.debug_message(p_message_text => l_full_name||': End');
4156 END IF;
4157 --Standard call to get message count and if count=1, get the message
4158 FND_MSG_PUB.Count_And_Get (
4159 p_encoded => FND_API.G_FALSE,
4160 p_count => x_msg_count,
4161 p_data => x_msg_data
4162 );
4163 x_return_status := l_return_status;
4164 EXCEPTION
4165 WHEN FND_API.G_EXC_ERROR THEN
4166 x_return_status := FND_API.G_RET_STS_ERROR;
4167 -- Standard call to get message count and if count=1, get the message
4168 FND_MSG_PUB.Count_And_Get (
4169 p_encoded => FND_API.G_FALSE,
4170 p_count => x_msg_count,
4171 p_data => x_msg_data
4172 );
4173 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4174 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4175
4176 -- Standard call to get message count and if count=1, get the message
4177 FND_MSG_PUB.Count_And_Get (
4178 p_encoded => FND_API.G_FALSE,
4179 p_count => x_msg_count,
4180 p_data => x_msg_data
4181 );
4182 WHEN OTHERS THEN
4183 ROLLBACK TO Process_TP_ACCRUAL;
4184 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4185
4186 -- Standard call to get message count and if count=1, get the message
4187 FND_MSG_PUB.Count_And_Get (
4188 p_encoded => FND_API.G_FALSE,
4189 p_count => x_msg_count,
4190 p_data => x_msg_data
4191 );
4192 END Process_TP_ACCRUAL;
4193
4194 ---------------------------------------------------------------------
4195 -- PROCEDURE
4196 -- Start_TP_ACCRUAL
4197 --
4198 -- PURPOSE
4199 -- This procedure to initiate concurrent program to process third party accrual.
4200 -- It is to for backword compatibility issue on 11.5.9
4201 --
4202 -- PARAMETERS
4203 --
4204 --
4205 -- NOTES
4206 ---------------------------------------------------------------------
4207 PROCEDURE Start_TP_ACCRUAL (
4208 ERRBUF OUT NOCOPY VARCHAR2,
4209 RETCODE OUT NOCOPY NUMBER,
4210 p_data_source_code IN VARCHAR2 :=NULL
4211 )
4212 IS
4213 l_api_name CONSTANT VARCHAR2(30) := 'Start_TP_ACCRUAL';
4214 l_api_version CONSTANT NUMBER := 1.0;
4215 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
4216 --
4217 l_return_status VARCHAR2(30);
4218 l_msg_data VARCHAR2(2000);
4219 l_msg_count NUMBER;
4220 BEGIN
4221
4222 SAVEPOINT START_TP_ACCRUAL;
4223 RETCODE := 0;
4224
4225 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '*======================================================================================================*');
4226 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Execution Starts On: ' || to_char(sysdate,'MM-DD-YYYY HH24:MI:SS'));
4227 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '*------------------------------------------------------------------------------------------------------*');
4228 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'MO_GLOBAL.GET_CURRENT_ORG_ID: ' || MO_GLOBAL.GET_CURRENT_ORG_ID());
4229 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '*------------------------------------------------------------------------------------------------------*');
4230
4231
4232 -- Debug Message
4233 IF OZF_DEBUG_HIGH_ON THEN
4234 OZF_UTILITY_PVT.debug_message(p_message_text => l_full_name||': Start');
4235 END IF;
4236
4237 Process_TP_ACCRUAL (
4238 p_api_version => 1.0
4239 ,p_init_msg_list => FND_API.G_FALSE
4240 ,p_commit => FND_API.G_FALSE
4241 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
4242 ,p_data_source_code => p_data_source_code
4243 ,x_return_status => l_return_status
4244 ,x_msg_data => l_msg_data
4245 ,x_msg_count => l_msg_count
4246 );
4247 IF l_return_status = FND_API.g_ret_sts_error THEN
4248 ozf_utility_pvt.error_message('OZF_PROC_RESALE_ERR');
4249 RAISE FND_API.G_EXC_ERROR;
4250 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
4251 ozf_utility_pvt.error_message('OZF_PROC_RESALE_ERR');
4252 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4253 END IF;
4254
4255 -- Debug Message
4256 IF OZF_DEBUG_HIGH_ON THEN
4257 OZF_UTILITY_PVT.debug_message(p_message_text => l_full_name||': End');
4258 END IF;
4259
4260 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '*------------------------------------------------------------------------------------------------------*');
4261 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Execution Ends On: ' || to_char(sysdate,'MM-DD-YYYY HH24:MI:SS'));
4262 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '*======================================================================================================*');
4263
4264 -- Write all messages to a log
4265 OZF_UTILITY_PVT.Write_Conc_Log;
4266 EXCEPTION
4267 WHEN FND_API.g_exc_error THEN
4268 OZF_UTILITY_PVT.Write_Conc_Log;
4269 ERRBUF := l_msg_data;
4270
4271 WHEN FND_API.g_exc_unexpected_error THEN
4272 OZF_UTILITY_PVT.Write_Conc_Log;
4273 ERRBUF := l_msg_data;
4274 RETCODE := 2;
4275
4276 WHEN OTHERS THEN
4277 ROLLBACK TO START_TP_ACCRUAL;
4278 OZF_UTILITY_PVT.Write_Conc_Log;
4279 ERRBUF := l_msg_data;
4280 RETCODE := 2;
4281 END Start_TP_ACCRUAL;
4282
4283 END OZF_TP_ACCRUAL_PVT;