[Home] [Help]
PACKAGE BODY: APPS.OZF_CHARGEBACK_PVT
Source
1 PACKAGE BODY OZF_CHARGEBACK_PVT AS
2 /* $Header: ozfvcbkb.pls 120.23 2008/04/08 13:28:02 ateotia ship $ */
3
4 -- Package name : OZF_CHARGEBACK_PVT
5 -- Purpose :
6 -- HISTORY :
7 -- NOTE :
8 -- END of Comments
9
10 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OZF_CHARGEBACK_PVT';
11 G_FILE_NAME CONSTANT VARCHAR2(30) := 'ozfvcbkb.pls';
12 G_PRICING_EVENT CONSTANT VARCHAR2(30) := 'PRICE';
13 G_TP_ACCRUAL CONSTANT VARCHAR2(30) := 'TP_ACCRUAL';
14 G_CHBK_UTIL_TYPE CONSTANT VARCHAR2(30) := 'CHARGEBACK';
15
16 OZF_DEBUG_HIGH_ON BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_high);
17 OZF_DEBUG_LOW_ON BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low);
18 OZF_UNEXP_ERROR BOOLEAN := FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.g_msg_lvl_unexp_error);
19
20 ---------------------------------------------------------------------
21 -- PROCEDURE
22 -- Initiate_Payment
23 --
24 -- PURPOSE
25 -- Initiate payment FOR a batch.
26 --
27 -- PARAMETERS
28 --
29 -- NOTES
30 --
31 ---------------------------------------------------------------------
32 PROCEDURE Initiate_Payment (
33 p_api_version IN NUMBER
34 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
35 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
36 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
37 ,p_resale_batch_id IN NUMBER
38 ,x_return_status OUT NOCOPY VARCHAR2
39 ,x_msg_data OUT NOCOPY VARCHAR2
40 ,x_msg_count OUT NOCOPY NUMBER
41 )
42 IS
43 l_api_name CONSTANT VARCHAR2(30) := 'Initiate_Payment';
44 l_api_version CONSTANT NUMBER := 1.0;
45 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
46 --
47 l_return_status VARCHAR2(30);
48 l_msg_data VARCHAR2(2000);
49 l_msg_count NUMBER;
50 --
51 l_batch_status VARCHAR2(30);
52 l_batch_type VARCHAR2(30);
53 l_partner_cust_account_id NUMBER;
54 l_partner_party_id NUMBER;
55 l_report_start_date DATE;
56 l_report_end_date DATE;
57 l_batch_number VARCHAR2(30);
58 l_last_updated_by NUMBER(15);
59 --
60 l_count NUMBER;
61 TYPE id_type IS RECORD (id NUMBER);
62 TYPE id_tbl_type IS TABLE OF id_type INDEX BY BINARY_INTEGER;
63
64 l_line_int_rec OZF_RESALE_COMMON_PVT.g_interface_rec_csr%ROWTYPE;
65 l_valid_line_id_tbl id_tbl_type;
66 i NUMBER;
67 l_chargeback_fund_id NUMBER;
68 l_header_id NUMBER;
69 l_line_id NUMBER;
70 l_create_order_header BOOLEAN := false;
71 --
72 l_inventory_tracking VARCHAR2(1);
73
74 --
75 l_auto_tp_accrual VARCHAR2(1);
76 l_claim_rec OZF_CLAIM_PVT.claim_rec_type;
77 l_funds_util_flt OZF_CLAIM_ACCRUAL_PVT.funds_util_flt_type;
78 l_claim_id NUMBER;
79 l_amount_claimed NUMBER;
80 l_reprocessing BOOLEAN;
81 l_inventory_level_valid BOOLEAN;
82 l_sales_transaction_id NUMBER;
83 l_currency_code VARCHAR2(15);
84
85 l_dup_header_id_tbl OZF_RESALE_COMMON_PVT.number_tbl_type;
86 --
87 CURSOR batch_info_csr (p_id IN NUMBER) IS
88 SELECT status_code,
89 batch_type,
90 partner_cust_account_id,
91 partner_party_id,
92 report_start_date,
93 report_end_date,
94 batch_number,
95 last_updated_by,
96 currency_code,
97 partner_claim_number
98 FROM ozf_resale_batches
99 WHERE resale_batch_id = p_id;
100
101 CURSOR open_line_count_csr (p_id IN NUMBER) IS
102 SELECT count(1)
103 FROM ozf_resale_lines_int
104 WHERE resale_batch_id = p_id
105 AND status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_OPEN;
106
107 CURSOR valid_line_id_csr(p_id IN NUMBER,
108 p_order_number IN VARCHAR2,
109 p_cust_id IN NUMBER,
110 p_date IN DATE) IS
111 SELECT resale_line_int_id
112 FROM ozf_resale_lines_int
113 WHERE resale_batch_id = p_id
114 AND order_number = p_order_number
115 AND sold_from_cust_account_id = p_cust_id
116 AND date_ordered = p_date
117 AND status_code = 'PROCESSED';
118 -- AND status_code in (OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DUPLICATED, OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED);
119 -- AND duplicated_adjustment_id <> -1;
120
121 CURSOR dup_header_id_csr( p_id IN NUMBER
122 , p_order_number IN VARCHAR2
123 , p_cust_id IN NUMBER
124 , p_date IN DATE ) IS
125 SELECT a.resale_header_id
126 FROM ozf_resale_headers a
127 , ozf_resale_lines_int b
128 , ozf_resale_lines c
129 WHERE b.resale_batch_id = p_id
130 AND b.order_number = p_order_number
131 AND b.sold_from_cust_account_id = p_cust_id
132 AND b.date_ordered = p_date
133 AND b.status_code = 'PROCESSED'
134 -- AND b.status_code IN ('DUPLICATED', 'PROCESSED')
135 AND b.duplicated_line_id = c.resale_line_id
136 AND c.resale_header_id = a.resale_header_id;
137
138
139 CURSOR batch_order_num_csr(p_id IN NUMBER) IS
140 SELECT DISTINCT order_number,
141 sold_from_cust_account_id,
142 date_ordered
143 FROM ozf_resale_lines_int
144 WHERE resale_batch_id = p_id
145 AND status_code = 'PROCESSED'
146 -- AND status_code in(OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DUPLICATED, OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED)
147 -- AND duplicated_adjustment_id <> -1
148 ORDER BY date_ordered;
149
150 TYPE order_num_tbl_type IS TABLE of batch_order_num_csr%ROWTYPE
151 INDEX BY BINARY_INTEGER;
152 l_order_num_tbl order_num_tbl_type;
153
154 CURSOR auto_tp_accrual_csr IS
155 SELECT auto_tp_accrual_flag
156 FROM ozf_sys_parameters;
157
158 CURSOR claimed_amount_csr(p_claim_id IN NUMBER) IS
159 SELECT amount
160 FROM ozf_claims
161 WHERE claim_id = p_claim_id;
162
163 CURSOR end_cust_relation_flag_csr IS
164 SELECT end_cust_relation_flag
165 -- BUG 4992408 (+)
166 -- FROM ozf_sys_parameters_all;
167 FROM ozf_sys_parameters;
168 -- BUG 4992408 (-)
169
170 l_end_cust_relation_flag VARCHAR2(30);
171 l_new_party_rec OZF_RESALE_COMMON_PVT.party_rec_type;
172 l_partner_claim_num VARCHAR2(30);
173
174 error_no_rollback EXCEPTION;
175
176 CURSOR csr_orig_billto_cust(cv_resale_batch_id IN NUMBER) IS
177 SELECT DISTINCT bill_to_party_name
178 , bill_to_address
179 , bill_to_city
180 , bill_to_state
181 , bill_to_postal_code
182 , bill_to_country
183 FROM ozf_resale_lines_int_all
184 WHERE resale_batch_id = cv_resale_batch_id
185 AND status_code = 'PROCESSED'
186 AND ( duplicated_line_id IS NULL
187 OR ( duplicated_line_id IS NOT NULL AND duplicated_adjustment_id = -1))
188 AND bill_to_party_id IS NULL
189 AND bill_to_cust_account_id IS NULL
190 AND bill_to_party_name IS NOT NULL;
191
192 TYPE orig_billto_cust_tbl_type IS TABLE of csr_orig_billto_cust%ROWTYPE
193 INDEX BY BINARY_INTEGER;
194 l_orig_billto_cust_tbl orig_billto_cust_tbl_type;
195
196 CURSOR csr_orig_end_cust(cv_resale_batch_id IN NUMBER) IS
197 SELECT DISTINCT end_cust_party_name
198 , end_cust_address
199 , end_cust_city
200 , end_cust_state
201 , end_cust_postal_code
202 , end_cust_country
203 , end_cust_site_use_code
204 FROM ozf_resale_lines_int_all
205 WHERE resale_batch_id = cv_resale_batch_id
206 AND status_code = 'PROCESSED'
207 AND ( duplicated_line_id IS NULL
208 OR ( duplicated_line_id IS NOT NULL AND duplicated_adjustment_id = -1))
209 AND end_cust_party_id IS NULL
210 AND end_cust_party_name IS NOT NULL;
211
212 TYPE orig_end_cust_tbl_type IS TABLE of csr_orig_end_cust%ROWTYPE
213 INDEX BY BINARY_INTEGER;
214 l_orig_end_cust_tbl orig_end_cust_tbl_type;
215
216
217
218 BEGIN
219 -- Standard begin of API savepoint
220 SAVEPOINT Initiate_Payment;
221 -- Standard call to check FOR call compatibility.
222 IF NOT FND_API.Compatible_API_Call (
223 l_api_version,
224 p_api_version,
225 l_api_name,
226 G_PKG_NAME)
227 THEN
228 RAISE FND_API.g_exc_unexpected_error;
229 END IF;
230
231 --Initialize message if p_init_msg_list IS TRUE.
232 IF FND_API.To_BOOLEAN (p_init_msg_list) THEN
233 FND_MSG_PUB.initialize;
234 END IF;
235
236 -- Debug Message
237 IF OZF_DEBUG_HIGH_ON THEN
238 OZF_UTILITY_PVT.debug_message(l_full_name||': Start');
239 END IF;
240
241 -- Initialize API return status to sucess
242 x_return_status := FND_API.G_RET_STS_SUCCESS;
243
244 OPEN batch_info_csr(p_resale_batch_id);
245 FETCH batch_info_csr INTO l_batch_status,
246 l_batch_type,
247 l_partner_cust_account_id,
248 l_partner_party_id,
249 l_report_start_date,
250 l_report_end_date,
251 l_batch_number,
252 l_last_updated_by,
253 l_currency_code,
254 l_partner_claim_num;
255 CLOSE batch_info_csr;
256
257 IF OZF_DEBUG_LOW_ON THEN
258 OZF_UTILITY_PVT.debug_message('status ' ||l_batch_status);
259 OZF_UTILITY_PVT.debug_message('compare to ' ||OZF_RESALE_COMMON_PVT.G_BATCH_PENDING_PAYMENT);
260 END IF;
261
262 -- --------------------------------------------------
263 -- Stop Initiate_Payment if
264 -- 1. Batch Status is not in Pending Payment
265 -- 2. There are open line(s) exists in the batch
266 -- --------------------------------------------------
267
268 IF l_batch_status <> OZF_RESALE_COMMON_PVT.G_BATCH_PENDING_PAYMENT THEN
269 -- Only DISPUTED AND PROCESSED batch can be paid.
270 OZF_UTILITY_PVT.error_message('OZF_RESALE_WRONG_STAUS_TO_PAY');
271 RAISE FND_API.g_exc_error;
272 END IF;
273
274 OPEN open_line_count_csr(p_resale_batch_id);
275 FETCH open_line_count_csr INTO l_count;
276 CLOSE open_line_count_csr;
277
278 IF l_count IS NOT NULL AND l_count <> 0 THEN
279 --Can not pay if there is an OPEN line
280 OZF_UTILITY_PVT.error_message('OZF_RESALE_OPEN_LINE_EXIST');
281 RAISE FND_API.g_exc_error;
282 END IF;
283
284
285 IF l_batch_status = OZF_RESALE_COMMON_PVT.G_BATCH_PENDING_PAYMENT THEN
286 -- Get the budget where accruals are posted
287 l_chargeback_fund_id := FND_PROFILE.value('OZF_CHARGEBACK_BUDGET');
288 --
289 IF l_chargeback_fund_id IS NULL THEN
290 --OZF_UTILITY_PVT.error_message('OZF_CHBK_BUDGET_NULL');
291
292 OZF_RESALE_COMMON_PVT.Insert_Resale_Log (
293 p_id_value => p_resale_batch_id
294 ,p_id_type => OZF_RESALE_COMMON_PVT.G_ID_TYPE_BATCH
295 ,p_error_code => 'OZF_CHBK_BUDGET_NULL'
296 ,p_column_name => NULL
297 ,p_column_value => NULL
298 ,x_return_status => l_return_status
299 );
300 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
301 RAISE FND_API.G_EXC_ERROR;
302 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
303 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
304 END IF;
305
306 UPDATE ozf_resale_batches_all
307 SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_DISPUTED
308 WHERE resale_batch_id = p_resale_batch_id;
309
310 RAISE error_no_rollback;
311 --RAISE FND_API.g_exc_error;
312 END IF;
313 --
314 IF OZF_DEBUG_HIGH_ON THEN
315 OZF_UTILITY_PVT.debug_message('Chargeback Budget: ' || l_chargeback_fund_id);
316 END IF;
317
318 -- Check whether there is a need to do inventory_verification
319 OPEN OZF_RESALE_COMMON_PVT.g_inventory_tracking_csr;
320 FETCH OZF_RESALE_COMMON_PVT.g_inventory_tracking_csr INTO l_inventory_tracking;
321 CLOSE OZF_RESALE_COMMON_PVT.g_inventory_tracking_csr;
322
323 IF l_inventory_tracking = 'T' THEN
324 OZF_SALES_TRANSACTIONS_PVT.Initiate_Inventory_tmp (
325 p_api_version => 1.0
326 ,p_init_msg_list => FND_API.G_FALSE
327 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
328 ,p_resale_batch_id => p_resale_batch_id
329 ,p_start_date => l_report_start_date
330 ,p_end_date => l_report_end_date
331 ,x_return_status => l_return_status
332 ,x_msg_count => l_msg_count
333 ,x_msg_data => l_msg_data
334 );
335 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
336 OZF_UTILITY_PVT.error_message('OZF_RESALE_INIT_INV_TMP_ERR');
337 RAISE FND_API.G_EXC_ERROR;
338 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
339 OZF_UTILITY_PVT.error_message('OZF_RESALE_INIT_INV_TMP_ERR');
340 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
341 END IF;
342 END IF;
343
344 OPEN end_cust_relation_flag_csr;
345 FETCH end_cust_relation_flag_csr INTO l_end_cust_relation_flag;
346 CLOSE end_cust_relation_flag_csr;
347
348 IF l_end_cust_relation_flag = 'T' THEN
349 -- check bill_to customer
350 OPEN csr_orig_billto_cust(p_resale_batch_id);
351 FETCH csr_orig_billto_cust BULK COLLECT INTO l_orig_billto_cust_tbl;
352 CLOSE csr_orig_billto_cust;
353
354 IF l_orig_billto_cust_tbl.COUNT > 0 THEN
355 FOR i IN 1..l_orig_billto_cust_tbl.COUNT LOOP
356 l_new_party_rec := NULL;
357 -- Bug 4737415 (+)
358 l_new_party_rec.partner_party_id := l_partner_party_id;
359 -- Bug 4737415 (-)
360 l_new_party_rec.name := l_orig_billto_cust_tbl(i).bill_to_party_name;
361 l_new_party_rec.address := l_orig_billto_cust_tbl(i).bill_to_address;
362 l_new_party_rec.city := l_orig_billto_cust_tbl(i).bill_to_city;
363 l_new_party_rec.state := l_orig_billto_cust_tbl(i).bill_to_state;
364 l_new_party_rec.postal_Code := l_orig_billto_cust_tbl(i).bill_to_postal_code;
365 l_new_party_rec.country := l_orig_billto_cust_tbl(i).bill_to_country;
366 l_new_party_rec.site_Use_Code := 'BILL_TO';
367
368 OZF_RESALE_COMMON_PVT.Create_Party(
369 p_api_version => 1.0
370 ,p_init_msg_list => FND_API.G_FALSE
371 ,p_commit => FND_API.G_FALSE
372 ,p_validation_level=> FND_API.G_VALID_LEVEL_FULL
373 ,px_party_rec => l_new_party_rec
374 ,x_return_status => l_return_status
375 ,x_msg_data => l_msg_data
376 ,x_msg_count => l_msg_count
377 );
378 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
379 RAISE FND_API.G_EXC_ERROR;
380 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
381 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
382 END IF;
383
384 UPDATE ozf_resale_lines_int_all
385 SET bill_to_party_id = l_new_party_rec.party_id
386 , bill_to_party_site_id = l_new_party_rec.party_site_id
387 WHERE resale_batch_id = p_resale_batch_id
388 AND bill_to_party_id IS NULL
389 AND bill_to_cust_account_id IS NULL
390 AND bill_to_party_name = l_orig_billto_cust_tbl(i).bill_to_party_name
391 AND bill_to_address = l_orig_billto_cust_tbl(i).bill_to_address
392 AND bill_to_city = l_orig_billto_cust_tbl(i).bill_to_city
393 AND bill_to_state = l_orig_billto_cust_tbl(i).bill_to_state
394 AND bill_to_postal_code = l_orig_billto_cust_tbl(i).bill_to_postal_code
395 AND bill_to_country = l_orig_billto_cust_tbl(i).bill_to_country;
396
397 END LOOP;
398 END IF;
399
400 -- check end_customer
401 OPEN csr_orig_end_cust(p_resale_batch_id);
402 FETCH csr_orig_end_cust BULK COLLECT INTO l_orig_end_cust_tbl;
403 CLOSE csr_orig_end_cust;
404
405 IF l_orig_end_cust_tbl.COUNT > 0 THEN
406 FOR i IN 1..l_orig_end_cust_tbl.COUNT LOOP
407 l_new_party_rec := NULL;
408 -- Bug 4737415 (+)
409 l_new_party_rec.partner_party_id := l_partner_party_id;
410 -- Bug 4737415 (-)
411 -- reset values:
412 l_new_party_rec.party_id := NULL;
413 l_new_party_rec.party_site_id := NULL;
414 l_new_party_rec.party_site_use_id := NULL;
415 l_new_party_rec.name := l_orig_end_cust_tbl(i).end_cust_party_name;
416 l_new_party_rec.address := l_orig_end_cust_tbl(i).end_cust_address;
417 l_new_party_rec.city := l_orig_end_cust_tbl(i).end_cust_city;
418 l_new_party_rec.state := l_orig_end_cust_tbl(i).end_cust_state;
419 l_new_party_rec.postal_code := l_orig_end_cust_tbl(i).end_cust_postal_code;
420 l_new_party_rec.country := l_orig_end_cust_tbl(i).end_cust_country;
421 l_new_party_rec.site_use_code := l_orig_end_cust_tbl(i).end_cust_site_use_code;
422
423 OZF_RESALE_COMMON_PVT.Create_Party
424 ( p_api_version => 1.0
425 ,p_init_msg_list => FND_API.G_FALSE
426 ,p_commit => FND_API.G_FALSE
427 ,p_validation_level=> FND_API.G_VALID_LEVEL_FULL
428 ,px_party_rec => l_new_party_rec
429 ,x_return_status => l_return_status
430 ,x_msg_data => l_msg_data
431 ,x_msg_count => l_msg_count
432 );
433 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
434 RAISE FND_API.G_EXC_ERROR;
435 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
436 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
437 END IF;
438
439 UPDATE ozf_resale_lines_int_all
440 SET end_cust_party_id = l_new_party_rec.party_id
441 , end_cust_party_site_id = l_new_party_rec.party_site_id
442 WHERE resale_batch_id = p_resale_batch_id
443 AND end_cust_party_id IS NULL
444 AND end_cust_party_name = l_orig_end_cust_tbl(i).end_cust_party_name
445 AND end_cust_address = l_orig_end_cust_tbl(i).end_cust_address
446 AND end_cust_city = l_orig_end_cust_tbl(i).end_cust_city
447 AND end_cust_state = l_orig_end_cust_tbl(i).end_cust_state
448 AND end_cust_postal_code = l_orig_end_cust_tbl(i).end_cust_postal_code
449 AND end_cust_country = l_orig_end_cust_tbl(i).end_cust_country
450 AND end_cust_site_use_code = l_orig_end_cust_tbl(i).end_cust_site_use_code;
451 END LOOP;
452 END IF;
453 END IF;
454
455
456 --i:=1;
457 IF l_order_num_tbl.EXISTS(1) THEN
458 l_order_num_tbl.DELETE;
459 END IF;
460 OPEN batch_order_num_csr(p_resale_batch_id);
461 FETCH batch_order_num_csr BULK COLLECT INTO l_order_num_tbl;
462 --LOOP
463 -- FETCH batch_order_num_csr INTO l_order_num_tbl(i);
464 -- EXIT WHEN batch_order_num_csr%NOTFOUND;
465 -- i:= i+1;
466 --END LOOP;
467 CLOSE batch_order_num_csr;
468
469 IF l_order_num_tbl.EXISTS(1) THEN -- IF B
470 FOR k in 1..l_order_num_tbl.LAST LOOP -- FOR C
471 IF OZF_DEBUG_LOW_ON THEN
472 ozf_utility_PVT.debug_message('PROCESS ORDER: ');
473 ozf_utility_PVT.debug_message('ORDER NUMBER: '||l_order_num_tbl(k).order_number);
474 ozf_utility_PVT.debug_message('SOLD_FROM_ACCT: '||l_order_num_tbl(k).sold_from_cust_account_id);
475 ozf_utility_PVT.debug_message('DATE ORDERED: '||l_order_num_tbl(k).date_ordered);
476 END IF;
477
478 -- [begin jxwu header_fix]:
479 -- Here, I assume if a line is the duplicate of another line, then they share
480 -- the same order header. Hence all order with this duplicated line share the
481 -- the same order with the oringinal lines.
482 IF l_dup_header_id_tbl.EXISTS(1) THEN
483 l_dup_header_id_tbl.DELETE;
484 END IF;
485 OPEN dup_header_id_csr(p_resale_batch_id,
486 l_order_num_tbl(k).order_number,
487 l_order_num_tbl(k).sold_from_cust_account_id,
488 l_order_num_tbl(k).date_ordered
489 );
490 FETCH dup_header_id_csr BULK COLLECT INTO l_dup_header_id_tbl;
491 CLOSE dup_header_id_csr;
492
493 IF l_dup_header_id_tbl.EXISTS(1) THEN
494 -- BUG 4670154 (+)
495 -- Cursor dup_header_id_csr will return multiple rows if same
496 -- order numbers exists in the same batch.
497 /*
498 IF l_dup_header_id_tbl.EXISTS(2) THEN
499
500 -- There is something wrong with this order. dispute all the orders
501 -- and move to the next one.
502 -- JXWU move update to common pvt
503 UPDATE ozf_resale_lines_int_all
504 SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DISPUTED,
505 dispute_code = 'OZF_RESALE_MULTI_HEADER',
506 followup_action_code = 'C',
507 response_type = 'CA',
508 response_code = 'N'
509 WHERE resale_batch_id = p_resale_batch_id
510 AND order_number = l_order_num_tbl(k).order_number
511 AND sold_from_cust_account_id = l_order_num_tbl(k).sold_from_cust_account_id
512 AND date_ordered = l_order_num_tbl(k).date_ordered
513 AND status_code in (OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DUPLICATED, OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED);
514 --AND duplicated_adjustment_id <> -1;
515
516 GOTO END_ORDER_HEADER;
517 ELSE
518 */
519 l_create_order_header := false;
520 l_header_id := l_dup_header_id_tbl(1);
521 --END IF;
522 -- BUG 4670154 (-)
523 ELSE
524 l_create_order_header := true;
525 END IF;
526 -- [End jxuw header_fix]
527
528 -- Here only DUPLICATED and PROCESSED lines are considered.
529 -- DISPUTED lines will not be moved to resale order table.
530 --i:=1;
531 IF l_valid_line_id_tbl.EXISTS(1) THEN
532 l_valid_line_id_tbl.DELETE;
533 END IF;
534 OPEN valid_line_id_csr(p_resale_batch_id,
535 l_order_num_tbl(k).order_number,
536 l_order_num_tbl(k).sold_from_cust_account_id,
537 l_order_num_tbl(k).date_ordered);
538 FETCH valid_line_id_csr BULK COLLECT INTO l_valid_line_id_tbl;
539 --LOOP
540 -- EXIT WHEN valid_line_id_csr%NOTFOUND;
541 -- FETCH valid_line_id_csr INTO l_valid_line_id_tbl(i);
542 -- i := i+1;
543 --END LOOP;
544 CLOSE valid_line_id_csr;
545
546 -- Again, we need to check whether if any line IS a duplicate or NOT.
547 IF l_valid_line_id_tbl.EXISTS(1) THEN -- IF D
548 -- I then try to create resale data.
549 FOR j in 1..l_valid_line_id_tbl.last -- FOR E
550 LOOP
551 IF OZF_DEBUG_LOW_ON THEN
552 OZF_UTILITY_PVT.debug_message('Current line_int_id:' || l_valid_line_id_tbl(j).id);
553 END IF;
554
555 OPEN OZF_RESALE_COMMON_PVT.g_interface_rec_csr(l_valid_line_id_tbl(j).id);
556 FETCH OZF_RESALE_COMMON_PVT.g_interface_rec_csr INTO l_line_int_rec;
557 CLOSE OZF_RESALE_COMMON_PVT.g_interface_rec_csr;
558
559 -- Need to check against inventory
560 -- Only need to check inventory for non-duplicate resale lines
561 IF l_line_int_rec.status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED AND
562 ( l_line_int_rec.duplicated_adjustment_id IS NULL
563 OR l_line_int_rec.duplicated_adjustment_id = -1 ) AND
564 l_inventory_tracking = 'T' THEN
565 -- Check inventory level first
566 OZF_SALES_TRANSACTIONS_PVT.Validate_Inventory_level (
567 p_api_version => 1.0
568 ,p_init_msg_list => FND_API.G_FALSE
569 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
570 ,p_line_int_rec => l_line_int_rec
571 ,x_valid => l_inventory_level_valid
572 ,x_return_status => l_return_status
573 ,x_msg_count => l_msg_count
574 ,x_msg_data => l_msg_data
575 );
576 IF NOT l_inventory_level_valid THEN
577 IF OZF_DEBUG_LOW_ON THEN
578 OZF_UTILITY_PVT.debug_message('Did NOT pass inventory checking');
579 END IF;
580 -- Here turn thIS line to DISPUTED AND create a dISput code FOR it.
581 UPDATE ozf_resale_lines_int
582 SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DISPUTED,
583 dispute_code = 'OZF_LT_INVT',
584 followup_action_code = 'C',
585 response_type = 'CA',
586 response_code = 'N'
587 WHERE resale_line_int_id = l_line_int_rec.resale_line_int_id;
588
589 -- SET Batch as DISPUTED
590 UPDATE ozf_resale_batches
591 SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_DISPUTED
592 WHERE resale_batch_id = l_line_int_rec.resale_batch_id;
593
594 GOTO END_ORDER_LINE;
595 END IF;
596 -- Check WAC
597 END IF;
598
599 -- First, check whether there is need to create a header for this order
600 -- SLKRISHN Add logic to derive or insert header
601 -- see jxwu header_fix.
602 IF j = 1 THEN
603 -- Determin header id
604 IF l_create_order_header THEN
605 OZF_RESALE_COMMON_PVT.Insert_Resale_Header(
606 p_api_version => 1
607 ,p_init_msg_list => FND_API.G_FALSE
608 ,p_commit => FND_API.G_FALSE
609 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
610 ,p_line_int_rec => l_line_int_rec
611 ,x_header_id => l_header_id
612 ,x_return_status => l_return_status
613 ,x_msg_data => l_msg_data
614 ,x_msg_count => l_msg_count
615 );
616 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
617 RAISE FND_API.G_EXC_ERROR;
618 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
619 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
620 END IF;
621 ELSE
622 NULL;
623 -- We should have the l_header_id FROM at the order level
624 END IF;
625 END IF;
626
627 IF OZF_DEBUG_LOW_ON THEN
628 OZF_UTILITY_PVT.debug_message('header_id is '|| l_header_id);
629 END IF;
630
631 IF l_line_int_rec.status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED THEN
632 IF (l_line_int_rec.duplicated_line_id IS NULL) OR
633 (l_line_int_rec.duplicated_line_id IS NOT NULL AND
634 l_line_int_rec.duplicated_adjustment_id = -1) THEN
635 -- We need to create party id for bill_to and end customer if
636 -- users wants us to do it.
637
638 -- No problem so far. Insert INTO batch_lines TABLE
639 OZF_RESALE_COMMON_PVT.Insert_Resale_Line(
640 p_api_version => 1
641 ,p_init_msg_list => FND_API.G_FALSE
642 ,p_commit => FND_API.G_FALSE
643 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
644 ,p_line_int_rec => l_line_int_rec
645 ,p_header_id => l_header_id
646 ,x_line_id => l_line_id
647 ,x_return_status => l_return_status
648 ,x_msg_data => l_msg_data
649 ,x_msg_count => l_msg_count
650 );
651 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
652 RAISE FND_API.G_EXC_ERROR;
653 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
654 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
655 END IF;
656
657 -- FOR processed order line, I need to create a transaction FOR it.
658 OZF_RESALE_COMMON_PVT.Create_Sales_Transaction (
659 p_api_version => 1.0
660 ,p_init_msg_list => FND_API.G_FALSE
661 ,p_commit => FND_API.G_FALSE
662 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
663 ,p_line_int_rec => l_line_int_rec
664 ,p_header_id => l_header_id
665 ,p_line_id => l_line_id
666 ,x_sales_transaction_id => l_sales_transaction_id
667 ,x_return_status => l_return_status
668 ,x_msg_data => l_msg_data
669 ,x_msg_count => l_msg_count
670 );
671 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
672 RAISE FND_API.G_EXC_ERROR;
673 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
674 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
675 END IF;
676 --
677
678 -- Bug 4380203 Fixing (+)
679 -- Bug 4380203 Fixing: Inventory Temp table is already updated in Validate_Inventory_Level
680 /*
681 IF l_inventory_tracking = 'T' THEN
682 OZF_SALES_TRANSACTIONS_PVT.Update_Inventory_Tmp(
683 p_api_version => 1.0
684 ,p_init_msg_list => FND_API.G_FALSE
685 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
686 ,p_sales_transaction_id => l_sales_transaction_id
687 ,x_return_status => l_return_status
688 ,x_msg_data => l_msg_data
689 ,x_msg_count => l_msg_count
690 );
691 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
692 RAISE FND_API.G_EXC_ERROR;
693 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
694 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
695 END IF;
696 --
697 END IF;
698 */
699 -- Bug 4380203 Fixing (-)
700 ELSE
701 l_line_id := l_line_int_rec.duplicated_line_id;
702 END IF;
703
704 ELSIF l_line_int_rec.status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DUPLICATED THEN
705 l_line_id := l_line_int_rec.duplicated_line_id;
706 END IF;
707
708 IF OZF_DEBUG_LOW_ON THEN
709 OZF_UTILITY_PVT.debug_message('line_id is '|| l_line_id);
710 END IF;
711
712 IF l_line_int_rec.status_code = 'PROCESSED' THEN
713 -- Create Resale Line Mappings only for Processed Lines
714 -- l_line_int_rec.status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DUPLICATED THEN
715 -- -- only create mapping FOR the lines that are processed or
716 -- -- duplicated, yet the adjustment IS new then
717 OZF_RESALE_COMMON_PVT.Insert_Resale_Line_Mapping(
718 p_api_version => 1
719 ,p_init_msg_list => FND_API.G_FALSE
720 ,p_commit => FND_API.G_FALSE
721 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
722 ,p_resale_batch_id => p_resale_batch_id
723 ,p_line_id => l_line_id
724 ,x_return_status => l_return_status
725 ,x_msg_data => l_msg_data
726 ,x_msg_count => l_msg_count
727 );
728 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
729 RAISE FND_API.G_EXC_ERROR;
730 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
731 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
732 END IF;
733 END IF;
734
735 IF l_line_int_rec.status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED AND
736 l_line_int_rec.tracing_flag = 'F' THEN
737 --
738 OZF_RESALE_COMMON_PVT.Create_Utilization(
739 p_api_version => 1.0
740 ,p_init_msg_LIST => FND_API.G_FALSE
741 ,p_commit => FND_API.G_FALSE
742 ,p_validation_level=> FND_API.G_VALID_LEVEL_FULL
743 ,p_line_int_rec => l_line_int_rec
744 ,p_fund_id => l_chargeback_fund_id
745 ,p_line_id => l_line_id
746 ,p_cust_account_id => l_partner_cust_account_id
747 ,p_approver_id => l_last_updated_by
748 ,x_return_status => l_return_status
749 ,x_msg_data => l_msg_data
750 ,x_msg_count => l_msg_count
751 );
752 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
753 RAISE FND_API.G_EXC_ERROR;
754 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
755 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
756 END IF;
757 END IF; -- If this line is a processed one
758 --
759 << END_ORDER_LINE >>
760 NULL;
761 END LOOP; -- END LOOP FOR this order -- FOR E
762 END IF; -- if valid line id EXISTS -- IF D
763 << END_ORDER_HEADER>>
764 NULL;
765 END LOOP; -- END LOOP FOR the batch FOR C
766 END IF; -- END order_num EXISTS IF B
767
768 OZF_RESALE_COMMON_PVT.Update_Batch_Calculations (
769 p_api_version => 1.0
770 ,p_init_msg_list => FND_API.G_FALSE
771 ,p_commit => FND_API.G_FALSE
772 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
773 ,p_resale_batch_id => p_resale_batch_id
774 ,x_return_status => l_return_status
775 ,x_msg_data => l_msg_data
776 ,x_msg_count => l_msg_count
777 );
778 IF l_return_status = FND_API.g_ret_sts_error THEN
779 RAISE FND_API.g_exc_error;
780 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
781 RAISE FND_API.g_exc_unexpected_error;
782 END IF;
783
784 -- After all the processes are done, call claim autopay
785 l_claim_rec.cust_account_id := l_partner_cust_account_id;
786 l_claim_rec.source_object_class := OZF_RESALE_COMMON_PVT.G_BATCH_OBJECT_CLASS;
787 l_claim_rec.source_object_id := p_resale_batch_id;
788 l_claim_rec.source_object_number := l_batch_number;
789 l_claim_rec.batch_id := p_resale_batch_id;
790 l_claim_rec.batch_type := OZF_RESALE_COMMON_PVT.G_BATCH_REF_TYPE;
791 l_claim_rec.customer_ref_number := l_partner_claim_num;
792 l_claim_rec.currency_code := l_currency_code;
793 l_funds_util_flt.cust_account_id := l_partner_cust_account_id;
794 l_funds_util_flt.reference_type := OZF_RESALE_COMMON_PVT.G_BATCH_REF_TYPE;
795 l_funds_util_flt.reference_id := p_resale_batch_id;
796 l_funds_util_flt.utilization_type:= G_CHBK_UTIL_TYPE;
797
798 OZF_Claim_Accrual_PVT.Pay_Claim_for_Accruals(
799 p_api_version => 1.0
800 ,p_init_msg_list => FND_API.g_false
801 ,p_commit => FND_API.g_false
802 ,p_validation_level => FND_API.g_valid_level_full
803 ,x_return_status => l_return_status
804 ,x_msg_count => l_msg_count
805 ,x_msg_data => l_msg_data
806 ,p_claim_rec => l_claim_rec
807 ,p_funds_util_flt => l_funds_util_flt
808 ,x_claim_id => l_claim_id
809 );
810 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
811 RAISE FND_API.g_exc_unexpected_error;
812 END IF;
813
814
815 IF OZF_DEBUG_LOW_ON THEN
816 OZF_UTILITY_PVT.debug_message('new claim_id:' || l_claim_id);
817 END IF;
818
819 -- Claim is created successfully.
820 IF l_claim_id IS NOT NULL THEN
821 -- get claimed amount
822 OPEN claimed_amount_csr(l_claim_id);
823 FETCH claimed_amount_csr INTO l_amount_claimed;
824 CLOSE claimed_amount_csr;
825
826 BEGIN
827 -- BUG 4395931 (+)
828 UPDATE ozf_resale_lines_int
829 SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_CLOSED
830 WHERE resale_batch_id = p_resale_batch_id
831 AND status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED;
832
833 /*
834 -- SLKRISHN change to common procedure
835 UPDATE ozf_resale_lines_int
836 SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_CLOSED
837 WHERE resale_batch_id = p_resale_batch_id
838 AND status_code in(OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DUPLICATED, OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED);
839 --AND duplicated_adjustment_id <> -1;
840 */
841 -- BUG 4395931 (-)
842
843 -- UPDATE batch status to closed -- might change later.
844 UPDATE ozf_resale_batches
845 SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_CLOSED,
846 paid_amount = l_amount_claimed
847 WHERE resale_batch_id = p_resale_batch_id;
848
849 EXCEPTION
850 WHEN OTHERS THEN
851 OZF_UTILITY_PVT.error_message('OZF_UPD_RESALE_WRG','TEXT',l_full_name||': End');
852 RAISE FND_API.g_exc_unexpected_error;
853 END;
854
855 -- mchang: Run Third Party Accrual After Chargeback Calculation was
856 -- done in workflow already. No need to check here.
857 /*
858 -- ??????? Need to think whether there IS a better way ---
859 -- Once the autopay was successful, check options whether to start chargeback
860 OPEN auto_tp_accrual_csr;
861 FETCH auto_tp_accrual_csr INTO l_auto_tp_accrual;
862 CLOSE auto_tp_accrual_csr;
863 */
864 END IF;
865 -- END IF; -- END if not rejected
866 END IF; -- END IF batch processed or disputed
867
868 -- Debug Message
869 IF OZF_DEBUG_HIGH_ON THEN
870 OZF_UTILITY_PVT.debug_message(l_full_name||': End');
871 END IF;
872
873 --Standard call to get message count AND if count=1, get the message
874 FND_MSG_PUB.Count_and_Get (
875 p_encoded => FND_API.G_FALSE,
876 p_count => x_msg_count,
877 p_data => x_msg_data
878 );
879
880 EXCEPTION
881 WHEN error_no_rollback THEN
882 x_return_status := FND_API.G_RET_STS_ERROR;
883 FND_MSG_PUB.Count_And_Get (
884 p_encoded => FND_API.G_FALSE,
885 p_count => x_msg_count,
886 p_data => x_msg_data
887 );
888 WHEN FND_API.g_exc_error THEN
889 ROLLBACK TO Initiate_Payment;
890 x_return_status := FND_API.G_RET_STS_ERROR;
891 -- Standard call to get message count AND IF count=1, get the message
892 FND_MSG_PUB.Count_And_Get (
893 p_encoded => FND_API.G_FALSE,
894 p_count => x_msg_count,
895 p_data => x_msg_data
896 );
897 WHEN FND_API.g_exc_unexpected_error THEN
898 ROLLBACK TO Initiate_Payment;
899 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
900 -- Standard call to get message count AND IF count=1, get the message
901 FND_MSG_PUB.Count_And_Get (
902 p_encoded => FND_API.G_FALSE,
903 p_count => x_msg_count,
904 p_data => x_msg_data
905 );
906 WHEN OTHERS THEN
907 ROLLBACK TO Initiate_Payment;
908 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
909 IF OZF_UNEXP_ERROR THEN
910 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
911 END IF;
912 -- Standard call to get message count AND IF count=1, get the message
913 FND_MSG_PUB.Count_And_Get (
914 p_encoded => FND_API.G_FALSE,
915 p_count => x_msg_count,
916 p_data => x_msg_data
917 );
918 END Initiate_Payment;
919
920 ---------------------------------------------------------------------
921 -- PROCEDURE
922 -- Validate_Order_Record
923 --
924 -- PURPOSE
925 -- ThIS procedure validates the order information
926 -- These are validation specific to chargeback process
927 --
928 -- PARAMETERS
929 --
930 --
931 -- NOTES
932 ---------------------------------------------------------------------
933 PROCEDURE Validate_Order_Record(
934 p_api_version IN NUMBER
935 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
936 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
937 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
938 ,p_resale_batch_id IN NUMBER
939 ,x_return_status OUT NOCOPY VARCHAR2
940 ,x_msg_data OUT NOCOPY VARCHAR2
941 ,x_msg_count OUT NOCOPY NUMBER
942 )IS
943 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Order_Record';
944 l_api_version CONSTANT NUMBER := 1.0;
945 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
946 --
947 l_id_tbl OZF_RESALE_COMMON_PVT.number_tbl_type;
948 l_err_tbl OZF_RESALE_COMMON_PVT.varchar_tbl_type;
949 l_col_tbl OZF_RESALE_COMMON_PVT.varchar_tbl_type;
950 l_val_tbl OZF_RESALE_COMMON_PVT.long_varchar_tbl_type;
951 l_return_status VARCHAR2(1);
952 --
953 CURSOR null_columns_csr (p_batch_id in number) IS
954 SELECT resale_line_int_id,
955 'OZF_RESALE_PUR_PRICE_MISSING',
956 'PURCHASE_PRICE',
957 NULL
958 FROM ozf_resale_lines_int_all
959 WHERE resale_batch_id = p_batch_id
960 AND status_code IN (OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_OPEN, OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DISPUTED)
961 AND tracing_flag = 'F'
962 AND purchase_price IS NULL;
963
964 --
965 BEGIN
966 -- Standard call to check FOR call compatibility.
967 IF NOT FND_API.Compatible_API_Call (
968 l_api_version,
969 p_api_version,
970 l_api_name,
971 G_PKG_NAME)
972 THEN
973 RAISE FND_API.g_exc_unexpected_error;
974 END IF;
975
976 -- Debug Message
977 IF OZF_DEBUG_HIGH_ON THEN
978 OZF_UTILITY_PVT.debug_message(l_full_name||': Start');
979 END IF;
980
981 --Initialize message lISt if p_init_msg_list IS TRUE.
982 IF FND_API.To_BOOLEAN (p_init_msg_list) THEN
983 FND_MSG_PUB.initialize;
984 END IF;
985
986 -- Initialize API return status to sucess
987 x_return_status := FND_API.G_RET_STS_SUCCESS;
988
989 -- bulk select all lines with missing order numbers
990 IF l_id_tbl.EXISTS(1) THEN
991 l_id_tbl.DELETE;
992 END IF;
993 IF l_err_tbl.EXISTS(1) THEN
994 l_err_tbl.DELETE;
995 END IF;
996 IF l_col_tbl.EXISTS(1) THEN
997 l_col_tbl.DELETE;
998 END IF;
999 IF l_val_tbl.EXISTS(1) THEN
1000 l_val_tbl.DELETE;
1001 END IF;
1002 OPEN null_columns_csr (p_resale_batch_id);
1003 FETCH null_columns_csr BULK COLLECT INTO l_id_tbl, l_err_tbl, l_col_tbl, l_val_tbl;
1004 CLOSE null_columns_csr;
1005 --
1006
1007 -- log disputed lines
1008 OZF_RESALE_COMMON_PVT.Bulk_Insert_Resale_Log (
1009 p_id_value => l_id_tbl,
1010 p_id_type => OZF_RESALE_COMMON_PVT.G_ID_TYPE_IFACE,
1011 p_error_code => l_err_tbl,
1012 p_column_name => l_col_tbl,
1013 p_column_value => l_val_tbl,
1014 p_batch_id => p_resale_batch_id, --bug # 5997978 fixed
1015 x_return_status => l_return_status
1016 );
1017
1018 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1019 RAISE FND_API.g_exc_error;
1020 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1021 RAISE FND_API.g_exc_unexpected_error;
1022 END IF;
1023 --
1024
1025 OZF_RESALE_COMMON_PVT.Bulk_Dispute_Line (
1026 p_batch_id => p_resale_batch_id,
1027 p_line_status => OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_OPEN,
1028 x_return_status => l_return_status
1029 );
1030 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1031 RAISE FND_API.g_exc_error;
1032 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1033 RAISE FND_API.g_exc_unexpected_error;
1034 END IF;
1035 --
1036
1037 -- Debug Message
1038 IF OZF_DEBUG_HIGH_ON THEN
1039 OZF_UTILITY_PVT.debug_message(l_full_name||': End');
1040 END IF;
1041
1042 --Standard call to get message count AND if count=1, get the message
1043 FND_MSG_PUB.Count_and_Get (
1044 p_encoded => FND_API.G_FALSE,
1045 p_count => x_msg_count,
1046 p_data => x_msg_data
1047 );
1048 --
1049 EXCEPTION
1050 WHEN FND_API.g_exc_error THEN
1051 x_return_status := FND_API.G_RET_STS_ERROR;
1052 -- Standard call to get message count AND if count=1, get the message
1053 FND_MSG_PUB.Count_and_Get (
1054 p_encoded => FND_API.G_FALSE,
1055 p_count => x_msg_count,
1056 p_data => x_msg_data
1057 );
1058 WHEN FND_API.g_exc_unexpected_error THEN
1059 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1060 -- Standard call to get message count AND if count=1, get the message
1061 FND_MSG_PUB.Count_and_Get (
1062 p_encoded => FND_API.G_FALSE,
1063 p_count => x_msg_count,
1064 p_data => x_msg_data
1065 );
1066 WHEN OTHERS THEN
1067 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1068 -- Standard call to get message count AND if count=1, get the message
1069 FND_MSG_PUB.Count_and_Get (
1070 p_encoded => FND_API.G_FALSE,
1071 p_count => x_msg_count,
1072 p_data => x_msg_data
1073 );
1074 END Validate_Order_Record;
1075
1076 ---------------------------------------------------------------------
1077 -- PROCEDURE
1078 -- Process_Pricing_Result
1079 --
1080 -- PURPOSE
1081 -- ThIS procedure process the pricing call result. It creates accruals based
1082 -- on the dIScount information.
1083 --
1084 -- PARAMETERS
1085 -- p_resale_batch_id IN NUMBER,
1086 -- p_line_tbl IN OZF_ORDER_PRICE_PVT.line_rec_tbl_type,
1087 -- p_inventory_tracking IN VARCHAR2,
1088 -- x_return_status out VARCHAR2
1089 --
1090 -- NOTES
1091 -- 1. Non-monetray accruals have NOT been considered. Should look INTO ldets.benefit_qty
1092 -- AND ldets.benefit_uom FOR calculation.
1093 --
1094 ---------------------------------------------------------------------
1095 PROCEDURE Process_Pricing_Result (
1096 p_resale_batch_id IN NUMBER,
1097 p_line_tbl IN OZF_ORDER_PRICE_PVT.line_rec_tbl_type,
1098 p_inventory_tracking IN VARCHAR2,
1099 x_return_status OUT NOCOPY VARCHAR2
1100 )
1101 IS
1102 --
1103 l_api_name CONSTANT VARCHAR2(30) := 'Process_Pricing_Result';
1104 l_api_version CONSTANT NUMBER := 1.0;
1105 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
1106 --
1107 l_msg_data VARCHAR2(2000);
1108 l_msg_count NUMBER;
1109 l_return_status VARCHAR2(30);
1110 --
1111 l_order_number VARCHAR2(30);
1112 l_cust_account_id NUMBER;
1113 l_date_ordered DATE;
1114 l_has_error BOOLEAN:=false;
1115 l_log_id NUMBER;
1116 l_resale_int_rec OZF_RESALE_COMMON_PVT.g_interface_rec_csr%ROWTYPE;
1117 l_header_id NUMBER;
1118 l_line_id NUMBER;
1119 l_object_version_number NUMBER :=1;
1120
1121 l_default_exchange_type VARCHAR2(30);
1122 l_exchange_type VARCHAR2(30);
1123 l_exchange_date DATE;
1124 l_acctd_adj_unit_price NUMBER;
1125 l_acctd_selling_price NUMBER;
1126 l_rate NUMBER;
1127 l_func_currency_code VARCHAR2(15);
1128 l_IS_error BOOLEAN:=FALSE;
1129 l_inventory_level_valid BOOLEAN;
1130 l_allowed_amount NUMBER;
1131
1132 l_id_tbl OZF_RESALE_COMMON_PVT.number_tbl_type;
1133 l_err_tbl OZF_RESALE_COMMON_PVT.varchar_tbl_type;
1134 l_col_tbl OZF_RESALE_COMMON_PVT.varchar_tbl_type;
1135 l_val_tbl OZF_RESALE_COMMON_PVT.long_varchar_tbl_type;
1136 --
1137 CURSOR order_identifiers_csr (p_id IN NUMBER) IS
1138 SELECT order_number
1139 , sold_from_cust_account_id
1140 , date_ordered
1141 FROM ozf_resale_lines_int
1142 WHERE resale_line_int_id = p_id;
1143
1144 CURSOR func_currency_cd_csr IS
1145 SELECT gs.currency_code
1146 FROM gl_sets_of_books gs,
1147 ozf_sys_parameters osp
1148 WHERE gs.set_of_books_id = osp.set_of_books_id
1149 AND osp.org_id = MO_GLOBAL.get_current_org_id();
1150
1151 CURSOR open_lines_csr(p_order_number VARCHAR2,
1152 p_date_ordered DATE,
1153 p_cust_account_id NUMBER )IS
1154 SELECT resale_line_int_id, 'OZF_RESALE_PRICE_ERROR', NULL, NULL
1155 FROM ozf_resale_lines_int
1156 WHERE status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_OPEN
1157 AND order_number = l_order_number
1158 AND date_ordered = l_date_ordered
1159 AND sold_from_cust_account_id =l_cust_account_id
1160 AND dispute_code is null
1161 AND resale_batch_id = p_resale_batch_id;
1162
1163 l_unit_purchase_price number;
1164 l_uom_ratio number;
1165 BEGIN
1166 SAVEPOINT Process_Pricing_Result;
1167
1168 x_return_status := FND_API.G_RET_STS_SUCCESS;
1169
1170 -- Debug Message
1171 IF OZF_DEBUG_HIGH_ON THEN
1172 OZF_UTILITY_PVT.debug_message(l_full_name||': Start');
1173 END IF;
1174
1175 -- First check whether the order result collection EXISTS or NOT
1176 IF p_line_tbl.EXISTS(1) THEN
1177
1178 IF OZF_DEBUG_LOW_ON THEN
1179 OZF_UTILITY_PVT.debug_message('There is one line');
1180 END IF;
1181
1182 OPEN order_identifiers_csr(p_line_tbl(1).chargeback_int_id);
1183 FETCH order_identifiers_csr INTO l_order_number, l_cust_account_id, l_date_ordered;
1184 CLOSE order_identifiers_csr;
1185
1186 -- LOOP through the result to find if there is an error in the result.
1187 FOR i in 1..p_line_tbl.LAST
1188 LOOP
1189 l_is_error := p_line_tbl(i).pricing_status_code <> QP_PREQ_PUB.G_STATUS_NEW AND
1190 p_line_tbl(i).pricing_status_code <> QP_PREQ_PUB.G_STATUS_UNCHANGED AND
1191 p_line_tbl(i).pricing_status_code <> QP_PREQ_PUB.G_STATUS_UPDATED;
1192 IF OZF_DEBUG_LOW_ON THEN
1193 OZF_UTILITY_PVT.debug_message('pricing status code ' || p_line_tbl(i).pricing_status_code);
1194 END IF;
1195
1196 IF l_is_error then
1197 IF OZF_DEBUG_LOW_ON THEN
1198 OZF_UTILITY_PVT.debug_message('line '||p_line_tbl(i).chargeback_int_id || ' has pricing error' );
1199 END IF;
1200 BEGIN
1201 update ozf_resale_lines_int
1202 set status_code =OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DISPUTED,
1203 dispute_code = 'OZF_RESALE_PRICE_ERROR',
1204 followup_action_code = 'C',
1205 response_type = 'CA',
1206 response_code = 'N'
1207 where resale_line_int_id = p_line_tbl(i).chargeback_int_id;
1208 EXCEPTION
1209 WHEN OTHERS THEN
1210 ozf_utility_pvt.error_message('OZF_UPD_RESALE_INT_WRG');
1211 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1212 END;
1213
1214 OZF_RESALE_COMMON_PVT.Insert_Resale_Log (
1215 p_id_value => p_line_tbl(i).chargeback_int_id,
1216 p_id_type => OZF_RESALE_COMMON_PVT.G_ID_TYPE_IFACE,
1217 p_error_code => p_line_tbl(i).pricing_status_code,
1218 p_error_message => p_line_tbl(i).pricing_status_text,
1219 p_column_name => NULL,
1220 p_column_value => NULL,
1221 x_return_status => l_return_status);
1222 --
1223 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1224 RAISE FND_API.g_exc_error;
1225 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1226 RAISE FND_API.g_exc_unexpected_error;
1227 END IF;
1228
1229 OZF_RESALE_COMMON_PVT.Insert_Resale_Log (
1230 p_id_value => p_line_tbl(i).chargeback_int_id,
1231 p_id_type => OZF_RESALE_COMMON_PVT.G_ID_TYPE_IFACE,
1232 p_error_code => 'OZF_RESALE_PRICE_ERROR',
1233 p_column_name => NULL,
1234 p_column_value => NULL,
1235 x_return_status => l_return_status);
1236 --
1237 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1238 RAISE FND_API.g_exc_error;
1239 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1240 RAISE FND_API.g_exc_unexpected_error;
1241 END IF;
1242 --
1243 END IF;
1244
1245 -- If there IS an error, remember it
1246 l_has_error := l_has_error OR l_is_error;
1247 END LOOP;
1248
1249 IF l_has_error THEN
1250 IF l_id_tbl.EXISTS(1) THEN
1251 l_id_tbl.DELETE;
1252 END IF;
1253 IF l_err_tbl.EXISTS(1) THEN
1254 l_err_tbl.DELETE;
1255 END IF;
1256 IF l_col_tbl.EXISTS(1) THEN
1257 l_col_tbl.DELETE;
1258 END IF;
1259 IF l_val_tbl.EXISTS(1) THEN
1260 l_val_tbl.DELETE;
1261 END IF;
1262 OPEN open_lines_csr (l_order_number, l_date_ordered, l_cust_account_id);
1263 FETCH open_lines_csr BULK COLLECT INTO l_id_tbl, l_err_tbl, l_col_tbl, l_val_tbl;
1264 CLOSE open_lines_csr;
1265
1266 -- log disputed lines
1267 OZF_RESALE_COMMON_PVT.Bulk_Insert_Resale_Log (
1268 p_id_value => l_id_tbl,
1269 p_id_type => OZF_RESALE_COMMON_PVT.G_ID_TYPE_IFACE,
1270 p_error_code => l_err_tbl,
1271 p_column_name => l_col_tbl,
1272 p_column_value => l_val_tbl,
1273 p_batch_id => p_resale_batch_id, --bug # 5997978 fixed
1274 x_return_status => l_return_status
1275 );
1276 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1277 RAISE FND_API.G_EXC_ERROR;
1278 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1279 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1280 END IF;
1281
1282 BEGIN
1283 UPDATE ozf_resale_lines_int_all
1284 SET status_code =OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DISPUTED,
1285 dispute_code = 'OZF_RESALE_PRICE_ERROR',
1286 followup_action_code = 'C',
1287 response_type = 'CA',
1288 response_code = 'N'
1289 WHERE status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_OPEN
1290 AND order_number = l_order_number
1291 AND date_ordered = l_date_ordered
1292 AND sold_from_cust_account_id =l_cust_account_id
1293 AND dispute_code is null
1294 AND resale_batch_id = p_resale_batch_id;
1295 EXCEPTION
1296 WHEN OTHERS THEN
1297 ozf_utility_pvt.error_message( 'OZF_UPD_RESALE_INT_WRG');
1298 RAISE FND_API.g_exc_unexpected_error;
1299 END;
1300
1301 ELSE
1302 -- There is no error in the result. We need to process the result one by one.
1303 IF OZF_DEBUG_LOW_ON THEN
1304 OZF_UTILITY_PVT.debug_message('No Error in the result' );
1305 END IF;
1306
1307 -- get functional currency code AND convertion type
1308 OPEN func_currency_cd_csr;
1309 FETCH func_currency_cd_csr INTO l_func_currency_code;
1310 CLOSE func_currency_cd_csr;
1311
1312 OPEN OZF_RESALE_COMMON_PVT.g_exchange_rate_type_csr;
1313 FETCH OZF_RESALE_COMMON_PVT.g_exchange_rate_type_csr INTO l_default_exchange_type;
1314 CLOSE OZF_RESALE_COMMON_PVT.g_exchange_rate_type_csr;
1315
1316 -- FOR each chargeback of the line, we will UPDATE the line
1317 FOR i in 1..p_line_tbl.LAST LOOP
1318 IF p_line_tbl(i).line_type_code = 'LINE' THEN
1319 OPEN OZF_RESALE_COMMON_PVT.g_interface_rec_csr(p_line_tbl(i).chargeback_int_id);
1320 FETCH OZF_RESALE_COMMON_PVT.g_interface_rec_csr INTO l_resale_int_rec;
1321 CLOSE OZF_RESALE_COMMON_PVT.g_interface_rec_csr;
1322
1323 -- Check inventory level FOR thIS order.
1324 -- If inventory level IS lower than the asked, then there IS no need to
1325 -- continue processing
1326 IF p_inventory_tracking = 'T' AND
1327 l_resale_int_rec.duplicated_line_id IS NULL AND
1328 ( l_resale_int_rec.duplicated_adjustment_id IS NULL
1329 OR l_resale_int_rec.duplicated_adjustment_id = -1 ) THEN
1330 IF OZF_DEBUG_LOW_ON THEN
1331 OZF_UTILITY_PVT.debug_message('Need inventory tracking' );
1332 END IF;
1333
1334 -- Check inventory level first
1335 OZF_SALES_TRANSACTIONS_PVT.Validate_Inventory_level (
1336 p_api_version => 1.0
1337 ,p_init_msg_list => FND_API.G_FALSE
1338 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
1339 ,p_line_int_rec => l_resale_int_rec
1340 ,x_valid => l_inventory_level_valid
1341 ,x_return_status => l_return_status
1342 ,x_msg_count => l_msg_count
1343 ,x_msg_data => l_msg_data
1344 );
1345
1346 IF NOT l_inventory_level_valid THEN
1347 IF OZF_DEBUG_LOW_ON THEN
1348 OZF_UTILITY_PVT.debug_message('Did not pass inventory checking');
1349 END IF;
1350 --
1351 OZF_RESALE_COMMON_PVT.Insert_Resale_Log (
1352 p_id_value => p_line_tbl(i).chargeback_int_id,
1353 p_id_type => OZF_RESALE_COMMON_PVT.G_ID_TYPE_IFACE,
1354 p_error_code => 'OZF_RESALE_INV_LEVEL_ERROR',
1355 p_column_name => NULL,
1356 p_column_value => NULL,
1357 x_return_status => l_return_status);
1358 --
1359 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1360 RAISE FND_API.g_exc_error;
1361 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1362 RAISE FND_API.g_exc_unexpected_error;
1363 END IF;
1364 --
1365 -- SET Batch as DISPUTED
1366 -- SLKRISHN change to common procedure
1367 UPDATE ozf_resale_lines_int
1368 SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DISPUTED,
1369 dispute_code = 'OZF_LT_INVT',
1370 followup_action_code = 'C',
1371 response_type = 'CA',
1372 response_code = 'N'
1373 WHERE resale_line_int_id = l_resale_int_rec.resale_line_int_id;
1374
1375 UPDATE ozf_resale_batches
1376 SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_DISPUTED
1377 WHERE resale_batch_id = l_resale_int_rec.resale_batch_id;
1378 --
1379 GOTO END_LOOP3;
1380 ELSE
1381 IF OZF_DEBUG_LOW_ON THEN
1382 OZF_UTILITY_PVT.debug_message('Pass inventory test' );
1383 END IF;
1384 END IF;
1385 --
1386 END IF;
1387
1388 -- Check WAC
1389 --END IF;
1390
1391 -- Convert the adjusted amount to functional currency code.
1392 l_exchange_type := l_resale_int_rec.exchange_rate_type;
1393 l_exchange_date := l_resale_int_rec.exchange_rate_date;
1394 l_rate := l_resale_int_rec.exchange_rate;
1395 IF l_func_currency_code <> l_resale_int_rec.CURRENCY_CODE THEN
1396 IF l_rate IS NULL THEN
1397 IF l_exchange_type IS NULL THEN
1398 l_exchange_type := l_default_exchange_type;
1399 END IF;
1400
1401 IF l_exchange_type IS NULL THEN
1402 OZF_UTILITY_PVT.error_message('OZF_CLAIM_CONTYPE_MISSING');
1403 RAISE FND_API.g_exc_unexpected_error;
1404 END IF;
1405
1406 IF l_exchange_date IS NULL THEN
1407 l_exchange_date := sysdate;
1408 END IF;
1409 IF OZF_DEBUG_LOW_ON THEN
1410 OZF_UTILITY_PVT.debug_message('FROM currency:' || l_resale_int_rec.currency_code);
1411 OZF_UTILITY_PVT.debug_message('to currency:' || l_func_currency_code);
1412 OZF_UTILITY_PVT.debug_message('rate:' || l_rate);
1413 OZF_UTILITY_PVT.debug_message('exchange DATE:' || l_exchange_date);
1414 OZF_UTILITY_PVT.debug_message('exchange type:' || l_exchange_type);
1415 END IF;
1416
1417 -- convert unit price
1418 OZF_UTILITY_PVT.Convert_Currency(
1419 p_from_currency => l_resale_int_rec.CURRENCY_CODE
1420 ,p_to_currency => l_func_currency_code
1421 ,p_conv_type => l_exchange_type
1422 ,p_conv_rate => l_rate
1423 ,p_conv_date => l_exchange_date
1424 ,p_from_amount => p_line_tbl(i).unit_price
1425 ,x_return_status => l_return_status
1426 ,x_to_amount => l_acctd_adj_unit_price
1427 ,x_rate => l_rate);
1428 IF l_return_status = FND_API.g_ret_sts_error THEN
1429 RAISE FND_API.g_exc_error;
1430 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1431 RAISE FND_API.g_exc_unexpected_error;
1432 END IF;
1433
1434 -- convert adjusted selling price
1435 OZF_UTILITY_PVT.Convert_Currency(
1436 p_from_currency => l_resale_int_rec.CURRENCY_CODE
1437 ,p_to_currency => l_func_currency_code
1438 ,p_conv_type => l_exchange_type
1439 ,p_conv_rate => l_rate
1440 ,p_conv_date => l_exchange_date
1441 ,p_from_amount => l_resale_int_rec.selling_price
1442 ,x_return_status => l_return_status
1443 ,x_to_amount => l_acctd_selling_price
1444 ,x_rate => l_rate);
1445 IF l_return_status = FND_API.g_ret_sts_error THEN
1446 RAISE FND_API.g_exc_error;
1447 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1448 RAISE FND_API.g_exc_unexpected_error;
1449 END IF;
1450 ELSE
1451 l_acctd_adj_unit_price := OZF_UTILITY_PVT.CurrRound(p_line_tbl(i).unit_price*l_rate, l_func_currency_code);
1452 l_acctd_selling_price := OZF_UTILITY_PVT.CurrRound(l_resale_int_rec.selling_price*l_rate, l_func_currency_code);
1453 END IF;
1454 ELSE
1455 l_rate := 1;
1456 l_acctd_adj_unit_price := p_line_tbl(i).unit_price;
1457 l_acctd_selling_price := l_resale_int_rec.selling_price;
1458 END IF;
1459
1460 -- In case of chargeback, we will not create resale lines and adjustments right away.
1461 l_line_id := l_resale_int_rec.resale_line_int_id;
1462 l_resale_int_rec.exchange_rate := l_rate;
1463 l_resale_int_rec.exchange_rate_date := l_exchange_date;
1464 l_resale_int_rec.exchange_rate_type := l_exchange_type;
1465 l_resale_int_rec.acctd_calculated_price := l_acctd_adj_unit_price;
1466 l_resale_int_rec.acctd_selling_price := l_acctd_selling_price;
1467
1468 -- First check the difference between purchase uom and uom
1469 IF l_resale_int_rec.purchase_uom_code = l_resale_int_rec.uom_code THEN
1470 l_unit_purchase_price := l_resale_int_rec.purchase_price;
1471 ELSE
1472 l_uom_ratio := inv_convert.inv_um_convert(
1473 l_resale_int_rec.inventory_item_id,
1474 null,
1475 1,
1476 l_resale_int_rec.purchase_uom_code,
1477 l_resale_int_rec.uom_code,
1478 null, null);
1479 l_unit_purchase_price := OZF_UTILITY_PVT.CurrRound(l_resale_int_rec.purchase_price/l_uom_ratio
1480 , l_resale_int_rec.currency_code);
1481 END IF;
1482
1483 IF OZF_DEBUG_LOW_ON THEN
1484 OZF_UTILITY_PVT.debug_message('Unit Purchase Price:' || l_unit_purchase_price);
1485 END IF;
1486 -- Get allowed amount
1487 IF p_line_tbl(i).unit_price < l_unit_purchase_price THEN
1488 IF SIGN(p_line_tbl(i).line_quantity) = -1 THEN
1489 l_allowed_amount := (l_unit_purchase_price - p_line_tbl(i).unit_price) * -1;
1490 ELSE
1491 l_allowed_amount := l_unit_purchase_price - p_line_tbl(i).unit_price;
1492 END IF;
1493
1494 ELSE
1495 l_allowed_amount := 0;
1496 END IF;
1497
1498 IF OZF_DEBUG_LOW_ON THEN
1499 OZF_UTILITY_PVT.debug_message('Allowed amount:' || l_allowed_amount);
1500 END IF;
1501
1502 -- Update the results of Chargeback Calculation
1503 OZF_RESALE_COMMON_PVT.Update_Line_Calculations(
1504 p_resale_line_int_rec => l_resale_int_rec,
1505 p_unit_price => p_line_tbl(i).unit_price,
1506 p_line_quantity => p_line_tbl(i).line_quantity,
1507 p_allowed_amount => l_allowed_amount,
1508 x_return_status => l_return_status);
1509 --
1510 IF l_return_status = FND_API.g_ret_sts_error THEN
1511 RAISE FND_API.g_exc_error;
1512 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1513 RAISE FND_API.g_exc_unexpected_error;
1514 END IF;
1515 --
1516 END IF; -- END if current record IS a line
1517 << END_LOOP3>>
1518 NULL;
1519 END LOOP; -- END LOOP through lines
1520
1521 -- Dispute all the lines from this order
1522 OZF_RESALE_COMMON_PVT.Bulk_Dispute_Line (
1523 p_batch_id => p_resale_batch_id,
1524 p_line_status => OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_OPEN,
1525 x_return_status => l_return_status
1526 );
1527 --
1528 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1529 RAISE FND_API.g_exc_error;
1530 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1531 RAISE FND_API.g_exc_unexpected_error;
1532 END IF;
1533 --
1534 END IF; -- END of l_has_error
1535 ELSE
1536 IF OZF_DEBUG_LOW_ON THEN
1537 OZF_UTILITY_PVT.debug_message('There is no line to be processed');
1538 END IF;
1539 END IF; -- END of EXISTS
1540
1541 -- Debug Message
1542 IF OZF_DEBUG_HIGH_ON THEN
1543 OZF_UTILITY_PVT.debug_message(l_full_name||': End');
1544 END IF;
1545
1546 EXCEPTION
1547 WHEN FND_API.g_exc_error THEN
1548 ROLLBACK TO Process_Pricing_Result;
1549 x_return_status := FND_API.G_RET_STS_ERROR;
1550 WHEN FND_API.g_exc_unexpected_error THEN
1551 ROLLBACK TO Process_Pricing_Result;
1552 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1553 WHEN OTHERS THEN
1554 ROLLBACK TO Process_Pricing_Result;
1555 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1556 IF OZF_UNEXP_ERROR THEN
1557 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1558 END IF;
1559 END Process_Pricing_Result;
1560
1561 ---------------------------------------------------------------------
1562 -- PROCEDURE
1563 -- Get_Order_Price
1564 --
1565 -- PURPOSE
1566 -- Process order information to get agreemenet price
1567 --
1568 -- PARAMETERS
1569 --
1570 -- NOTES
1571 --
1572 ---------------------------------------------------------------------
1573 PROCEDURE Get_Order_Price (
1574 p_resale_batch_id IN NUMBER,
1575 p_order_number IN VARCHAR2,
1576 p_sold_from_cust_acct_id IN NUMBER,
1577 p_date_ordered IN DATE,
1578 x_line_tbl OUT NOCOPY OZF_ORDER_PRICE_PVT.line_rec_tbl_type,
1579 x_ldets_tbl OUT NOCOPY OZF_ORDER_PRICE_PVT.LDETS_TBL_TYPE,
1580 x_related_lines_tbl OUT NOCOPY OZF_ORDER_PRICE_PVT.RLTD_LINE_TBL_TYPE,
1581 x_return_status OUT NOCOPY VARCHAR2
1582 )
1583 IS
1584 --
1585 l_api_name CONSTANT VARCHAR2(30) := 'Get_Order_Price';
1586 l_api_version CONSTANT NUMBER := 1.0;
1587 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
1588 --
1589 l_msg_data VARCHAR2(2000);
1590 l_msg_count NUMBER;
1591 l_return_status VARCHAR2(30);
1592 --
1593 l_line_tbl OZF_ORDER_PRICE_PVT.LINE_REC_TBL_TYPE;
1594 l_ldets_tbl OZF_ORDER_PRICE_PVT.LDETS_TBL_TYPE;
1595 l_related_lines_tbl OZF_ORDER_PRICE_PVT.RLTD_LINE_TBL_TYPE;
1596 l_control_rec QP_PREQ_GRP.CONTROL_RECORD_TYPE;
1597 --
1598 l_line_count NUMBER;
1599 J NUMBER;
1600 K NUMBER;
1601 l_price_flag VARCHAR2(1) := NULL;
1602 --
1603 CURSOR order_set_csr(p_order_number IN VARCHAR2,
1604 p_id IN NUMBER,
1605 p_date IN DATE,
1606 p_batch_id IN NUMBER ) IS
1607 SELECT *
1608 FROM ozf_resale_lines_int
1609 WHERE order_number = p_order_number
1610 AND sold_from_cust_account_id= p_id
1611 AND date_ordered = p_date
1612 AND status_code = 'OPEN'
1613 -- AND status_code in(OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_OPEN, OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DUPLICATED)
1614 -- AND duplicated_adjustment_id IS NULL
1615 -- bug # 6822541 fixed by ateotia (+)
1616 AND resale_batch_id = p_batch_id;
1617 --AND tracing_flag = 'F';
1618 -- bug # 6822541 fixed by ateotia (-)
1619
1620 TYPE order_set_tbl IS TABLE OF order_set_csr%ROWTYPE INDEX BY BINARY_INTEGER;
1621
1622 l_order_set_tbl order_set_tbl;
1623
1624 -- bug 6511302 (+) need to validate inventory for tracing lines
1625 CURSOR c_order_set_trc(p_order_number IN VARCHAR2,
1626 p_id IN NUMBER,
1627 p_date IN DATE,
1628 p_batch_id IN NUMBER ) IS
1629 SELECT *
1630 FROM ozf_resale_lines_int
1631 WHERE order_number = p_order_number
1632 AND sold_from_cust_account_id= p_id
1633 AND date_ordered = p_date
1634 AND status_code = 'OPEN'
1635 AND resale_batch_id = p_batch_id
1636 AND tracing_flag = 'T';
1637
1638 TYPE order_set_trc_tbl IS TABLE OF c_order_set_trc%ROWTYPE INDEX BY BINARY_INTEGER;
1639
1640 l_order_set_trc_tbl order_set_trc_tbl;
1641 l_inventory_tracking VARCHAR2(2);
1642 l_inventory_level_valid BOOLEAN;
1643 l_resale_int_rec OZF_RESALE_COMMON_PVT.g_interface_rec_csr%ROWTYPE;
1644 -- 6511302 (-)
1645
1646 --
1647 BEGIN
1648 -- Debug Message
1649 IF OZF_DEBUG_HIGH_ON THEN
1650 OZF_UTILITY_PVT.debug_message(l_full_name||': Start');
1651 END IF;
1652
1653 SAVEPOINT Get_Order_Price;
1654
1655 -- Initialize API return status to sucess
1656 x_return_status := FND_API.G_RET_STS_SUCCESS;
1657
1658 -- bug 6511302 (+) validate inventory for tracing lines
1659 OPEN OZF_RESALE_COMMON_PVT.g_inventory_tracking_csr;
1660 FETCH OZF_RESALE_COMMON_PVT.g_inventory_tracking_csr INTO l_inventory_tracking;
1661 CLOSE OZF_RESALE_COMMON_PVT.g_inventory_tracking_csr;
1662
1663 IF l_inventory_tracking = 'T' THEN -- validate inventory for tracing lines. order lines will be validated during pricing phase
1664 OPEN c_order_set_trc(p_order_number,
1665 p_sold_from_cust_acct_id,
1666 p_date_ordered,
1667 p_resale_batch_id);
1668 FETCH c_order_set_trc BULK COLLECT INTO l_order_set_trc_tbl;
1669 CLOSE c_order_set_trc;
1670
1671 IF l_order_set_trc_tbl.COUNT > 0 THEN
1672 FOR i IN 1..l_order_set_trc_tbl.COUNT LOOP
1673 OZF_UTILITY_PVT.debug_message(l_full_name || ' Validating tracing line ' || l_order_set_trc_tbl(i).resale_line_int_id);
1674 OPEN OZF_RESALE_COMMON_PVT.g_interface_rec_csr(l_order_set_trc_tbl(i).resale_line_int_id);
1675 FETCH OZF_RESALE_COMMON_PVT.g_interface_rec_csr INTO l_resale_int_rec;
1676 CLOSE OZF_RESALE_COMMON_PVT.g_interface_rec_csr;
1677
1678 IF l_resale_int_rec.duplicated_adjustment_id IS NULL OR l_resale_int_rec.duplicated_adjustment_id = -1 THEN
1679 -- Check inventory level first
1680 OZF_SALES_TRANSACTIONS_PVT.Validate_Inventory_level (
1681 p_api_version => 1.0
1682 ,p_init_msg_list => FND_API.G_FALSE
1683 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
1684 ,p_line_int_rec => l_resale_int_rec
1685 ,x_valid => l_inventory_level_valid
1686 ,x_return_status => l_return_status
1687 ,x_msg_count => l_msg_count
1688 ,x_msg_data => l_msg_data
1689 );
1690
1691 IF NOT l_inventory_level_valid THEN
1692 IF OZF_DEBUG_LOW_ON THEN
1693 OZF_UTILITY_PVT.debug_message('Did NOT pass inventory checking');
1694 END IF;
1695
1696 -- log
1697 OZF_RESALE_COMMON_PVT.Insert_Resale_Log (
1698 p_id_value => l_order_set_trc_tbl(i).resale_line_int_id,
1699 p_id_type => OZF_RESALE_COMMON_PVT.G_ID_TYPE_IFACE,
1700 p_error_code => 'OZF_RESALE_INV_LEVEL_ERROR',
1701 p_column_name => NULL,
1702 p_column_value => NULL,
1703 x_return_status => l_return_status);
1704
1705 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1706 RAISE FND_API.g_exc_error;
1707 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1708 RAISE FND_API.g_exc_unexpected_error;
1709 END IF;
1710
1711 -- Set line to DISPUTED
1712 UPDATE ozf_resale_lines_int
1713 SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DISPUTED,
1714 dispute_code = 'OZF_LT_INVT',
1715 followup_action_code = 'C',
1716 response_type = 'CA',
1717 response_code = 'N'
1718 WHERE resale_line_int_id = l_resale_int_rec.resale_line_int_id;
1719
1720 -- SET Batch as DISPUTED
1721 UPDATE ozf_resale_batches
1722 SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_DISPUTED
1723 WHERE resale_batch_id = l_resale_int_rec.resale_batch_id;
1724 END IF;
1725 END IF;
1726 END LOOP;
1727 END IF;
1728 END IF;
1729 -- bug 6511302 (-)
1730
1731 -- Define control rec
1732 -- setup pricing_event based on purpose code AND profile
1733 -- privcing_event = 'PRICE' FOR chargeback
1734 l_control_rec.pricing_event := G_PRICING_EVENT;
1735 --l_control_rec.pricing_event := 'BATCH';
1736 l_control_rec.calculate_flag := 'Y';
1737 l_control_rec.simulation_flag := 'Y';
1738 l_control_rec.source_order_amount_flag := 'Y';
1739 l_control_rec.gsa_check_flag := 'N';
1740 l_control_rec.gsa_dup_check_flag := 'N';
1741 l_control_rec.temp_table_insert_flag := 'N';
1742
1743 IF OZF_DEBUG_LOW_ON THEN
1744 OZF_UTILITY_PVT.debug_message('Event:' ||l_control_rec.pricing_event );
1745 END IF;
1746
1747 -- Price flag has to be Y to get the price.
1748 l_price_flag := 'Y';
1749
1750 IF OZF_DEBUG_LOW_ON THEN
1751 OZF_UTILITY_PVT.debug_message('Price flag:' ||l_price_flag );
1752 END IF;
1753
1754 QP_Price_Request_Context.Set_Request_Id;
1755
1756 IF OZF_DEBUG_LOW_ON THEN
1757 OZF_UTILITY_PVT.debug_message('/*--- Processing order for order number:'||p_order_number||'---*/');
1758 OZF_UTILITY_PVT.debug_message('/*--- and cusomter:'||p_sold_from_cust_acct_id||'---*/');
1759 END IF;
1760
1761 -- Before start process, clean up the data structures if necessary.
1762 IF l_order_set_tbl.EXISTS(1) THEN l_order_set_tbl.DELETE; END IF;
1763 IF l_line_tbl.EXISTS(1) THEN l_line_tbl.DELETE; END IF;
1764 IF l_ldets_tbl.EXISTS(1) THEN l_ldets_tbl.DELETE; END IF;
1765 IF l_related_lines_tbl.EXISTS(1) THEN l_related_lines_tbl.DELETE; END IF;
1766 IF OZF_ORDER_PRICE_PVT.g_line_rec_tbl.EXISTS(1) THEN OZF_ORDER_PRICE_PVT.g_line_rec_tbl.DELETE; END IF;
1767
1768 -- Get all lines in an order
1769 --l_line_count := 1;
1770 OPEN order_set_csr(p_order_number,
1771 p_sold_from_cust_acct_id,
1772 p_date_ordered,
1773 p_resale_batch_id);
1774 FETCH order_set_csr BULK COLLECT INTO l_order_set_tbl;
1775 --LOOP
1776 -- FETCH order_set_csr INTO l_order_set_tbl(l_line_count);
1777 -- EXIT WHEN order_set_csr%NOTFOUND;
1778 -- l_line_count := l_line_count + 1;
1779 --END LOOP;
1780 CLOSE order_set_csr;
1781
1782 IF OZF_DEBUG_LOW_ON THEN
1783 OZF_UTILITY_PVT.debug_message('after order set'||l_order_set_tbl.count);
1784 END IF;
1785
1786 IF l_order_set_tbl.EXISTS(1) THEN
1787 FOR J in 1..l_order_set_tbl.LAST
1788 LOOP
1789 -- Purge the any error message that might be there.
1790 BEGIN
1791 -- SLKRISHN move to a common procedure
1792 DELETE FROM ozf_resale_logs
1793 WHERE resale_id = l_order_set_tbl(J).resale_line_int_id
1794 AND resale_id_type = OZF_RESALE_COMMON_PVT.G_ID_TYPE_IFACE;
1795 EXCEPTION
1796 WHEN OTHERS THEN
1797 IF OZF_UNEXP_ERROR THEN
1798 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1799 END IF;
1800 RAISE FND_API.g_exc_unexpected_error;
1801 END;
1802 IF OZF_DEBUG_LOW_ON THEN
1803 OZF_UTILITY_PVT.debug_message('/*--- Buidling order line info for inteface id: '||l_order_set_tbl(J).resale_line_int_id ||'---*/');
1804 END IF;
1805
1806 -- insert INTO l_line_tbl
1807 l_line_tbl(J).line_index := J;
1808 l_line_tbl(J).line_id := NULL;
1809 l_line_tbl(J).line_type_code := OZF_ORDER_PRICE_PVT.G_ORDER_LINE_TYPE;
1810 l_line_tbl(J).pricing_effective_date := l_order_set_tbl(J).date_ordered;
1811 l_line_tbl(J).active_date_first := l_order_set_tbl(J).date_ordered;
1812 l_line_tbl(J).active_date_first_type := 'ORD';
1813 l_line_tbl(J).active_date_second := l_order_set_tbl(J).date_shipped;
1814 l_line_tbl(J).active_date_second_type := 'SHIP';
1815 l_line_tbl(J).line_quantity := l_order_set_tbl(J).Quantity;
1816 l_line_tbl(J).line_uom_code := l_order_set_tbl(J).uom_code;
1817 l_line_tbl(J).request_type_code := 'ONT';
1818 l_line_tbl(J).priced_quantity := NULL;
1819 l_line_tbl(J).priced_uom_code := NULL;
1820 l_line_tbl(J).unit_price := NULL;
1821 l_line_tbl(J).currency_code := l_order_set_tbl(J).currency_code;
1822 --l_line_tbl(J).unit_price := NULL;
1823 --l_line_tbl(J).percent_price := NULL;
1824 --l_line_tbl(J).uom_quantity := NULL;
1825 --l_line_tbl(J).adjusted_unit_price := NULL;
1826 --l_line_tbl(J).upd_adjusted_unit_price := FND_API.G_MISS_NUM,
1827 --l_line_tbl(J).processed_flag := FND_API.G_MISS_CHAR,
1828 l_line_tbl(J).price_flag := l_price_flag;
1829 --l_line_tbl(J).processing_order := NULL;
1830 l_line_tbl(J).pricing_status_code := QP_PREQ_GRP.G_STATUS_UNCHANGED;
1831 --l_line_tbl(J).pricing_status_text := NULL;
1832 --l_line_tbl(J).rounding_flag := NULL;
1833 --l_line_tbl(J).rounding_factor := NULL;
1834 --l_line_tbl(J).qualifiers_EXIST_flag := NULL;
1835 --l_line_tbl(J).pricing_attrs_EXIST_flag := NULL;
1836 IF l_order_set_tbl(J).corrected_agreement_id IS NOT NULL THEN
1837 l_line_tbl(J).price_list_id := l_order_set_tbl(J).corrected_agreement_id;
1838 ELSE
1839 l_line_tbl(J).price_list_id := l_order_set_tbl(J).agreement_id;
1840 END IF;
1841 --l_line_tbl(J).pl_VALIDATED_flag := NULL;
1842 --l_line_tbl(J).price_request_code := NULL;
1843 --l_line_tbl(J).usage_pricing_type := NULL;
1844 --l_line_tbl(J).line_category := NULL;
1845 l_line_tbl(J).chargeback_int_id := l_order_set_tbl(J).resale_line_int_id;
1846 l_line_tbl(J).resale_table_type := 'IFACE'; -- bug 5360598
1847
1848 -- populate the order_price global line arrary
1849 -- Here I only populate the values of the qualifiers FOR ONT.
1850 -- The real global structure will be populate in ozf_order_price_pvt.
1851 -- AND it's value can be change in OZF_CHARGEBACK_ATTRMAP_PUB
1852
1853 OZF_ORDER_PRICE_PVT.G_LINE_REC_TBL(J).header_id := l_order_set_tbl.LAST +1;
1854 OZF_ORDER_PRICE_PVT.G_LINE_REC_TBL(J).inventory_item_id := l_order_set_tbl(J).inventory_item_id;
1855 OZF_ORDER_PRICE_PVT.G_LINE_REC_TBL(J).line_id := NULL;
1856 OZF_ORDER_PRICE_PVT.G_LINE_REC_TBL(J).ordered_quantity := l_order_set_tbl(J).quantity;
1857 OZF_ORDER_PRICE_PVT.G_LINE_REC_TBL(J).order_quantity_uom := l_order_set_tbl(J).uom_code;
1858
1859 IF l_order_set_tbl(J).corrected_agreement_id IS NOT NULL THEN
1860 OZF_ORDER_PRICE_PVT.G_LINE_REC_TBL(J).price_list_id := l_order_set_tbl(J).corrected_agreement_id;
1861 ELSE
1862 OZF_ORDER_PRICE_PVT.G_LINE_REC_TBL(J).price_list_id := l_order_set_tbl(J).agreement_id;
1863 END IF;
1864
1865 OZF_ORDER_PRICE_PVT.G_LINE_REC_TBL(J).request_date := l_order_set_tbl(J).date_ordered;
1866 OZF_ORDER_PRICE_PVT.G_LINE_REC_TBL(J).invoice_to_org_id := l_order_set_tbl(J).bill_to_site_use_id;
1867 OZF_ORDER_PRICE_PVT.G_LINE_REC_TBL(J).ship_to_org_id := l_order_set_tbl(J).ship_to_site_use_id;
1868 OZF_ORDER_PRICE_PVT.G_LINE_REC_TBL(J).sold_to_org_id := l_order_set_tbl(J).bill_to_cust_account_id;
1869 -- [BEGIN OF BUG 4186465 FIXING]
1870 OZF_ORDER_PRICE_PVT.G_LINE_REC_TBL(J).invoice_to_party_id := l_order_set_tbl(J).bill_to_party_id;
1871 OZF_ORDER_PRICE_PVT.G_LINE_REC_TBL(J).invoice_to_party_site_id := l_order_set_tbl(J).bill_to_party_site_id;
1872 OZF_ORDER_PRICE_PVT.G_LINE_REC_TBL(J).ship_to_party_id := l_order_set_tbl(J).ship_to_party_id;
1873 OZF_ORDER_PRICE_PVT.G_LINE_REC_TBL(J).ship_to_party_site_id := l_order_set_tbl(J).ship_to_party_site_id;
1874 -- [END OF BUG 4186465 FIXING]
1875 -- OZF_ORDER_PRICE_PVT.G_LINE_REC_TBL(J).sold_from_org_id := l_order_set_tbl(J).sold_from_cust_account_id;
1876 OZF_ORDER_PRICE_PVT.G_LINE_REC_TBL(J).actual_shipment_date := l_order_set_tbl(J).date_shipped;
1877 OZF_ORDER_PRICE_PVT.G_LINE_REC_TBL(J).pricing_date := l_order_set_tbl(J).date_ordered;
1878 OZF_ORDER_PRICE_PVT.G_LINE_REC_TBL(J).line_type_id := l_order_set_tbl(J).order_type_id;
1879
1880
1881 -- R12 Populate Global Resale Structure (+)
1882 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(J).batch_type := 'CHARGEBACK';
1883 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(J).qp_context_request_id := QP_Price_Request_Context.Get_Request_Id;
1884 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(J).line_index := l_line_tbl(J).line_index;
1885 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(J).resale_table_type := 'IFACE';
1886 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(J).line_id := l_order_set_tbl(J).resale_line_int_id;
1887 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(J).resale_transfer_type := l_order_set_tbl(J).resale_transfer_type;
1888 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(J).product_transfer_movement_type := l_order_set_tbl(J).product_transfer_movement_type;
1889 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(J).product_transfer_date := l_order_set_tbl(J).product_transfer_date;
1890 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(J).tracing_flag := l_order_set_tbl(J).tracing_flag;
1891 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(J).sold_from_cust_account_id := l_order_set_tbl(J).sold_from_cust_account_id;
1892 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(J).sold_from_site_id := l_order_set_tbl(J).sold_from_site_id;
1893 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(J).sold_from_contact_party_id := l_order_set_tbl(J).sold_from_contact_party_id;
1894 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(J).ship_from_cust_account_id := l_order_set_tbl(J).ship_from_cust_account_id;
1895 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(J).ship_from_site_id := l_order_set_tbl(J).ship_from_site_id;
1896 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(J).ship_from_contact_party_id := l_order_set_tbl(J).ship_from_contact_party_id;
1897 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(J).bill_to_party_id := l_order_set_tbl(J).bill_to_party_id;
1898 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(J).bill_to_party_site_id := l_order_set_tbl(J).bill_to_party_site_id;
1899 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(J).bill_to_contact_party_id := l_order_set_tbl(J).bill_to_contact_party_id;
1900 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(J).ship_to_party_id := l_order_set_tbl(J).ship_to_party_id;
1901 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(J).ship_to_party_site_id := l_order_set_tbl(J).ship_to_party_site_id;
1902 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(J).ship_to_contact_party_id := l_order_set_tbl(J).ship_to_contact_party_id;
1903 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(J).end_cust_party_id := l_order_set_tbl(J).end_cust_party_id;
1904 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(J).end_cust_site_use_id := l_order_set_tbl(J).end_cust_site_use_id;
1905 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(J).end_cust_site_use_code := l_order_set_tbl(J).end_cust_site_use_code;
1906 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(J).end_cust_party_site_id := l_order_set_tbl(J).end_cust_party_site_id;
1907 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(J).end_cust_contact_party_id := l_order_set_tbl(J).end_cust_contact_party_id;
1908 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(J).data_source_code := l_order_set_tbl(J).data_source_code;
1909 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(J).header_attribute_category := l_order_set_tbl(J).header_attribute_category;
1910 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(J).header_attribute1 := l_order_set_tbl(J).header_attribute1;
1911 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(J).header_attribute2 := l_order_set_tbl(J).header_attribute2;
1912 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(J).header_attribute3 := l_order_set_tbl(J).header_attribute3;
1913 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(J).header_attribute4 := l_order_set_tbl(J).header_attribute4;
1914 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(J).header_attribute5 := l_order_set_tbl(J).header_attribute5;
1915 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(J).header_attribute6 := l_order_set_tbl(J).header_attribute6;
1916 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(J).header_attribute7 := l_order_set_tbl(J).header_attribute7;
1917 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(J).header_attribute8 := l_order_set_tbl(J).header_attribute8;
1918 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(J).header_attribute9 := l_order_set_tbl(J).header_attribute9;
1919 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(J).header_attribute10 := l_order_set_tbl(J).header_attribute10;
1920 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(J).header_attribute11 := l_order_set_tbl(J).header_attribute11;
1921 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(J).header_attribute12 := l_order_set_tbl(J).header_attribute12;
1922 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(J).header_attribute13 := l_order_set_tbl(J).header_attribute13;
1923 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(J).header_attribute14 := l_order_set_tbl(J).header_attribute14;
1924 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(J).header_attribute15 := l_order_set_tbl(J).header_attribute15;
1925 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(J).line_attribute_category := l_order_set_tbl(J).line_attribute_category;
1926 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(J).line_attribute1 := l_order_set_tbl(J).line_attribute1;
1927 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(J).line_attribute2 := l_order_set_tbl(J).line_attribute2;
1928 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(J).line_attribute3 := l_order_set_tbl(J).line_attribute3;
1929 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(J).line_attribute4 := l_order_set_tbl(J).line_attribute4;
1930 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(J).line_attribute5 := l_order_set_tbl(J).line_attribute5;
1931 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(J).line_attribute6 := l_order_set_tbl(J).line_attribute6;
1932 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(J).line_attribute7 := l_order_set_tbl(J).line_attribute7;
1933 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(J).line_attribute8 := l_order_set_tbl(J).line_attribute8;
1934 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(J).line_attribute9 := l_order_set_tbl(J).line_attribute9;
1935 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(J).line_attribute10 := l_order_set_tbl(J).line_attribute10;
1936 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(J).line_attribute11 := l_order_set_tbl(J).line_attribute11;
1937 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(J).line_attribute12 := l_order_set_tbl(J).line_attribute12;
1938 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(J).line_attribute13 := l_order_set_tbl(J).line_attribute13;
1939 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(J).line_attribute14 := l_order_set_tbl(J).line_attribute14;
1940 OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(J).line_attribute15 := l_order_set_tbl(J).line_attribute15;
1941 -- R12 Populate Global Resale Structure (-)
1942
1943
1944 IF OZF_DEBUG_LOW_ON THEN
1945 OZF_UTILITY_PVT.debug_message('/*---END Buidling order line FOR inteface id: '||l_order_set_tbl(J).resale_line_int_id ||'---*/');
1946 END IF;
1947 END LOOP;
1948 IF OZF_DEBUG_LOW_ON THEN
1949 OZF_UTILITY_PVT.debug_message('/*--- Buidling order header FOR order ---*/');
1950 END IF;
1951
1952 -- build summary line
1953 k := l_order_set_tbl.LAST +1;
1954 l_line_tbl(k).LINE_INDEX := k;
1955 l_line_tbl(k).LINE_ID := NULL;
1956 l_line_tbl(k).LINE_TYPE_CODE := OZF_ORDER_PRICE_PVT.G_ORDER_HEADER_TYPE;
1957 l_line_tbl(k).PRICING_EFFECTIVE_DATE := l_line_tbl(1).PRICING_EFFECTIVE_DATE;
1958 l_line_tbl(k).ACTIVE_DATE_FIRST := l_line_tbl(1).ACTIVE_DATE_FIRST;
1959 l_line_tbl(k).ACTIVE_DATE_FIRST_TYPE := 'ORD'; -- Change because of ONT QP order 'NO TYPE';
1960 l_line_tbl(k).ACTIVE_DATE_SECOND := l_line_tbl(1).ACTIVE_DATE_SECOND;
1961 l_line_tbl(k).ACTIVE_DATE_SECOND_TYPE := 'SHIP'; -- change because of ONT QP order 'NO TYPE';
1962 --l_line_tbl(k).LINE_QUANTITY := NULL;
1963 --l_line_tbl(k).LINE_UOM_CODE := NULL;
1964 l_line_tbl(k).REQUEST_TYPE_CODE := 'ONT';
1965 --l_line_tbl(k).PRICED_QUANTITY := NULL;
1966 --l_line_tbl(k).PRICED_UOM_CODE := NULL;
1967 l_line_tbl(k).CURRENCY_CODE := l_line_tbl(1).currency_code;
1968 --l_line_tbl(J).UNIT_PRICE := l_order_set_tbl(J).
1969 --l_line_tbl(J).PERCENT_PRICE := l_order_set_tbl(J).
1970 --l_line_tbl(J).UOM_QUANTITY := l_order_set_tbl(J).
1971 --l_line_tbl(J).ADJUSTED_UNIT_PRICE := l_order_set_tbl(J).
1972 --l_line_tbl(J).UPD_ADJUSTED_UNIT_PRICE := FND_API.G_MISS_NUM,
1973 --l_line_tbl(J).PROCESSED_FLAG := FND_API.G_MISS_CHAR,
1974 l_line_tbl(k).PRICE_FLAG := l_price_flag;
1975 --l_line_tbl(J).PROCESSING_ORDER := NULL;
1976 l_line_tbl(k).PRICING_STATUS_CODE := QP_PREQ_GRP.G_STATUS_UNCHANGED;
1977 --l_line_tbl(J).PRICING_STATUS_TEXT := NULL;
1978 --l_line_tbl(J).ROUNDING_FLAG := NULL;
1979 --l_line_tbl(J).ROUNDING_FACTOR := NULL;
1980 --l_line_tbl(J).QUALIFIERS_EXIST_FLAG := NULL;
1981 --l_line_tbl(J).PRICING_ATTRS_EXIST_FLAG := NULL;
1982 l_line_tbl(k).price_list_id := l_line_tbl(1).price_list_id;
1983 --l_line_tbl(J).PL_VALIDATED_FLAG := NULL;
1984 --l_line_tbl(J).PRICE_REQUEST_CODE := NULL;
1985 --l_line_tbl(J).USAGE_PRICING_TYPE := NULL;
1986 --l_line_tbl(J).LINE_CATEGORY := NULL;
1987 l_line_tbl(k).chargeback_int_id := l_order_set_tbl(1).resale_line_int_id; -- SLKRISHN correct?
1988 -- JXWU varified.
1989 l_line_tbl(k).resale_table_type := 'IFACE'; -- bug 5360598
1990
1991 -- populate the order_price global header structure
1992 -- Here I only populate the values of the qualifiers FOR ONT.
1993 -- The real global structure will be populate in ozf_order_price_pvt.
1994 -- AND it's value can be change in OZF_CHARGEBACK_ATTRMAP_PUB
1995 -- Might be able to add more value here.
1996 OZF_ORDER_PRICE_PVT.G_HEADER_REC.header_id := k;
1997 OZF_ORDER_PRICE_PVT.G_HEADER_REC.sold_to_org_id := OZF_ORDER_PRICE_PVT.G_LINE_REC_TBL(1).sold_to_org_id;
1998 OZF_ORDER_PRICE_PVT.G_HEADER_REC.order_type_id := l_order_set_tbl(1).order_type_id;
1999 OZF_ORDER_PRICE_PVT.G_HEADER_REC.invoice_to_org_id := OZF_ORDER_PRICE_PVT.G_LINE_REC_TBL(1).invoice_to_org_id;
2000 OZF_ORDER_PRICE_PVT.G_HEADER_REC.ship_to_org_id := OZF_ORDER_PRICE_PVT.G_LINE_REC_TBL(1).ship_to_org_id;
2001 -- [BEGIN OF BUG 4186465 FIXING]
2002 OZF_ORDER_PRICE_PVT.G_HEADER_REC.invoice_to_party_id := OZF_ORDER_PRICE_PVT.G_LINE_REC_TBL(1).invoice_to_party_id;
2003 OZF_ORDER_PRICE_PVT.G_HEADER_REC.invoice_to_party_site_id := OZF_ORDER_PRICE_PVT.G_LINE_REC_TBL(1).invoice_to_party_site_id;
2004 OZF_ORDER_PRICE_PVT.G_HEADER_REC.ship_to_party_id := OZF_ORDER_PRICE_PVT.G_LINE_REC_TBL(1).ship_to_party_id;
2005 OZF_ORDER_PRICE_PVT.G_HEADER_REC.ship_to_party_site_id := OZF_ORDER_PRICE_PVT.G_LINE_REC_TBL(1).ship_to_party_site_id;
2006 -- [END OF BUG 4186465 FIXING]
2007 OZF_ORDER_PRICE_PVT.G_HEADER_REC.ordered_date := l_order_set_tbl(1).date_ordered;
2008 OZF_ORDER_PRICE_PVT.G_HEADER_REC.price_list_id := l_line_tbl(1).price_list_id;
2009 OZF_ORDER_PRICE_PVT.G_HEADER_REC.request_date := l_order_set_tbl(1).date_ordered;
2010 OZF_ORDER_PRICE_PVT.G_HEADER_REC.pricing_date := l_order_set_tbl(1).date_ordered;
2011 IF OZF_DEBUG_HIGH_ON THEN
2012 OZF_UTILITY_PVT.debug_message('/*---END Buidling order header FOR order ---*/');
2013 OZF_UTILITY_PVT.debug_message('/*--- Calling get order price ---*/');
2014 END IF;
2015
2016 -- Get the agreement price for order lines
2017 OZF_ORDER_PRICE_PVT.Get_Order_Price (
2018 p_api_version => 1.0
2019 ,p_init_msg_list => FND_API.G_FALSE
2020 ,p_commit => FND_API.G_FALSE
2021 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
2022 ,x_return_status => l_return_status
2023 ,x_msg_data => l_msg_data
2024 ,x_msg_count => l_msg_count
2025 ,p_control_rec => l_control_rec
2026 ,xp_line_tbl => l_line_tbl
2027 ,x_ldets_tbl => x_ldets_tbl
2028 ,x_related_lines_tbl => x_related_lines_tbl
2029 );
2030 IF l_return_status = FND_API.g_ret_sts_error THEN
2031 RAISE FND_API.g_exc_error;
2032 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2033 RAISE FND_API.g_exc_unexpected_error;
2034 END IF;
2035
2036 x_line_tbl := l_line_tbl;
2037 END IF;
2038
2039 -- Debug Message
2040 IF OZF_DEBUG_HIGH_ON THEN
2041 OZF_UTILITY_PVT.debug_message(l_full_name||': End');
2042 END IF;
2043 EXCEPTION
2044 WHEN FND_API.g_exc_error THEN
2045 ROLLBACK TO Get_Order_Price;
2046 x_return_status := FND_API.G_RET_STS_ERROR;
2047 WHEN FND_API.g_exc_unexpected_error THEN
2048 ROLLBACK TO Get_Order_Price;
2049 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2050 WHEN OTHERS THEN
2051 ROLLBACK TO Get_Order_Price;
2052 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2053 IF OZF_UNEXP_ERROR THEN
2054 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2055 END IF;
2056 END Get_Order_Price;
2057
2058 ---------------------------------------------------------------------
2059 -- PROCEDURE
2060 -- Process_Order
2061 --
2062 -- PURPOSE
2063 -- Process order information. Only direct customer order will be simulated.
2064 --
2065 -- PARAMETERS
2066 --
2067 -- NOTES
2068 --
2069 ---------------------------------------------------------------------
2070 PROCEDURE Process_Order
2071 (
2072 p_api_version IN NUMBER
2073 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
2074 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
2075 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
2076 ,p_resale_batch_id IN NUMBER
2077 ,x_return_status OUT NOCOPY VARCHAR2
2078 ,x_msg_data OUT NOCOPY VARCHAR2
2079 ,x_msg_count OUT NOCOPY NUMBER
2080 )IS
2081 l_api_name CONSTANT VARCHAR2(30) := 'Process_Order';
2082 l_api_version CONSTANT NUMBER := 1.0;
2083 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
2084 --
2085 l_return_status VARCHAR2(1);
2086 l_msg_data VARCHAR2(2000);
2087 l_msg_count NUMBER;
2088 --
2089 k NUMBER;
2090 --
2091 l_log_id NUMBER;
2092 l_temp_count NUMBER;
2093 l_temp_data VARCHAR2(2000);
2094 --
2095 CURSOR order_num_csr IS
2096 SELECT DISTINCT order_number,
2097 sold_from_cust_account_id,
2098 date_ordered
2099 FROM ozf_resale_lines_int
2100 WHERE status_code = 'OPEN' --(OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_OPEN, OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DUPLICATED)
2101 AND resale_batch_id = p_resale_batch_id
2102 --AND (duplicated_adjustment_id IS NULL OR
2103 -- duplicated_adjustment_id = -1 )
2104 ORDER BY date_ordered;
2105
2106 l_cust_account_id_tbl OZF_RESALE_COMMON_PVT.number_tbl_type;
2107 l_order_num_tbl OZF_RESALE_COMMON_PVT.varchar_tbl_type;
2108 l_order_date_tbl OZF_RESALE_COMMON_PVT.date_tbl_type;
2109
2110 l_line_tbl OZF_ORDER_PRICE_PVT.LINE_REC_TBL_TYPE;
2111 l_ldets_tbl OZF_ORDER_PRICE_PVT.LDETS_TBL_TYPE;
2112 l_related_lines_tbl OZF_ORDER_PRICE_PVT.RLTD_LINE_TBL_TYPE;
2113
2114 CURSOR batch_info_csr (p_id IN NUMBER) IS
2115 SELECT partner_party_id, report_start_date, report_end_date
2116 FROM ozf_resale_batches
2117 WHERE resale_batch_id = p_id;
2118
2119 l_partner_party_id NUMBER;
2120 l_report_start_date DATE;
2121 l_report_end_date DATE;
2122 l_inventory_tracking VARCHAR2(2);
2123
2124 --
2125 BEGIN
2126 -- Standard begin of API savepoint
2127 SAVEPOINT Process_Order;
2128
2129 -- Standard call to check FOR call compatibility.
2130 IF NOT FND_API.Compatible_API_Call (
2131 l_api_version,
2132 p_api_version,
2133 l_api_name,
2134 G_PKG_NAME)
2135 THEN
2136 RAISE FND_API.g_exc_unexpected_error;
2137 END IF;
2138
2139 --Initialize message lISt if p_init_msg_list IS TRUE.
2140 IF FND_API.To_BOOLEAN (p_init_msg_list) THEN
2141 FND_MSG_PUB.initialize;
2142 END IF;
2143
2144 -- Debug Message
2145 IF OZF_DEBUG_HIGH_ON THEN
2146 OZF_UTILITY_PVT.debug_message(p_message_text => l_full_name||': Start');
2147 END IF;
2148
2149 -- Initialize API return status to sucess
2150 x_return_status := FND_API.G_RET_STS_SUCCESS;
2151
2152 -- Move Delete logs to resale_pre_process
2153 -- Delete the logs for the current batch
2154 OZF_RESALE_COMMON_PVT.Delete_Log(
2155 p_api_version => 1.0
2156 ,p_init_msg_list => FND_API.G_FALSE
2157 ,p_commit => FND_API.G_FALSE
2158 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
2159 ,p_resale_batch_id => p_resale_batch_id
2160 ,x_return_status => l_return_status
2161 ,x_msg_count => l_msg_count
2162 ,x_msg_data => l_msg_data
2163 );
2164 IF l_return_status = FND_API.g_ret_sts_error THEN
2165 RAISE FND_API.g_exc_error;
2166 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2167 RAISE FND_API.g_exc_unexpected_error;
2168 END IF;
2169
2170 -- Check whether there IS a need to do inventory_verification
2171 OPEN OZF_RESALE_COMMON_PVT.g_inventory_tracking_csr;
2172 FETCH OZF_RESALE_COMMON_PVT.g_inventory_tracking_csr INTO l_inventory_tracking;
2173 CLOSE OZF_RESALE_COMMON_PVT.g_inventory_tracking_csr;
2174 OZF_UTILITY_PVT.debug_message(p_message_text => l_full_name||': inventory flag = ' || l_inventory_tracking);
2175 -- populates the temp tables
2176 IF l_inventory_tracking = 'T' THEN
2177 OPEN batch_info_csr(p_resale_batch_id);
2178 FETCH batch_info_csr INTO l_partner_party_id
2179 , l_report_start_date
2180 , l_report_end_date;
2181 CLOSE batch_info_csr;
2182
2183 OZF_SALES_TRANSACTIONS_PVT.Initiate_Inventory_tmp (
2184 p_api_version => 1.0
2185 ,p_init_msg_list => FND_API.G_FALSE
2186 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
2187 ,p_resale_batch_id => p_resale_batch_id
2188 ,p_start_date => l_report_start_date
2189 ,p_end_date => l_report_end_date
2190 ,x_return_status => l_return_status
2191 ,x_msg_count => l_msg_count
2192 ,x_msg_data => l_msg_data
2193 );
2194 IF l_return_status = FND_API.g_ret_sts_error THEN
2195 RAISE FND_API.g_exc_error;
2196 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2197 RAISE FND_API.g_exc_unexpected_error;
2198 END IF;
2199 END IF;
2200
2201
2202 -- fetch all orders into a cursor.
2203 IF l_order_num_tbl.EXISTS(1) THEN
2204 l_order_num_tbl.DELETE;
2205 END IF;
2206 IF l_cust_account_id_tbl.EXISTS(1) THEN
2207 l_cust_account_id_tbl.DELETE;
2208 END IF;
2209 IF l_order_date_tbl.EXISTS(1) THEN
2210 l_order_date_tbl.DELETE;
2211 END IF;
2212 OPEN order_num_csr;
2213 FETCH order_num_csr BULK COLLECT INTO l_order_num_tbl,l_cust_account_id_tbl, l_order_date_tbl;
2214 CLOSE order_num_csr;
2215 OZF_UTILITY_PVT.debug_message(p_message_text => l_full_name||' order count = ' || l_order_num_tbl.count);
2216 --
2217 -- Get agreement price for all lines order by order
2218 IF l_order_num_tbl.EXISTS(1) THEN
2219 -- Loop through each order record
2220 FOR i in 1..l_order_num_tbl.LAST
2221 LOOP
2222 IF l_cust_account_id_tbl(i) IS NOT NULL AND
2223 l_order_num_tbl(i) IS NOT NULL AND
2224 l_order_date_tbl(i) IS NOT NULL
2225 THEN
2226 --
2227 Get_Order_Price (
2228 p_resale_batch_id => p_resale_batch_id,
2229 p_order_number => l_order_num_tbl(i),
2230 p_sold_from_cust_acct_id => l_cust_account_id_tbl(i),
2231 p_date_ordered => l_order_date_tbl(i),
2232 x_line_tbl => l_line_tbl,
2233 x_ldets_tbl => l_ldets_tbl,
2234 x_related_lines_tbl => l_related_lines_tbl,
2235 x_return_status => l_return_status
2236 );
2237 --
2238 -- log errors and dispute line if there are any error in processing the order
2239 -- Continue to next order in case of errors
2240 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2241 IF l_line_tbl.EXISTS(1) THEN
2242 FOR j in 1..l_line_tbl.LAST
2243 LOOP
2244
2245 IF OZF_DEBUG_LOW_ON THEN
2246 OZF_UTILITY_PVT.debug_message('/*--- before get_order_price Insert resale log done ---*/');
2247 OZF_UTILITY_PVT.debug_message('/*--- chargeback int id:' || l_line_tbl(j).chargeback_int_id);
2248 OZF_UTILITY_PVT.debug_message('/*--- Message type: '||OZF_RESALE_COMMON_PVT.G_ID_TYPE_IFACE);
2249 END IF;
2250
2251 OZF_RESALE_COMMON_PVT.Insert_Resale_Log (
2252 p_id_value => l_line_tbl(j).chargeback_int_id,
2253 p_id_type => OZF_RESALE_COMMON_PVT.G_ID_TYPE_IFACE,
2254 p_error_code => 'OZF_GET_ORDER_PRIC_ERR',
2255 p_column_name => NULL,
2256 p_column_value => NULL,
2257 x_return_status => l_return_status);
2258 --
2259 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2260 RAISE FND_API.g_exc_error;
2261 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2262 RAISE FND_API.g_exc_unexpected_error;
2263 END IF;
2264 --
2265 END LOOP;
2266 IF OZF_DEBUG_LOW_ON THEN
2267 OZF_UTILITY_PVT.debug_message('/*--- After get_order_price Insert resale log done ---*/');
2268 END IF;
2269
2270 -- Dispute all the lines from this order
2271 OZF_RESALE_COMMON_PVT.Bulk_Dispute_Line (
2272 p_batch_id => p_resale_batch_id,
2273 p_line_status => OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_OPEN,
2274 x_return_status => l_return_status
2275 );
2276 --
2277 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2278 RAISE FND_API.g_exc_error;
2279 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2280 RAISE FND_API.g_exc_unexpected_error;
2281 END IF;
2282 IF OZF_DEBUG_LOW_ON THEN
2283 OZF_UTILITY_PVT.debug_message('/*--- Get order price failed ---*/');
2284 END IF;
2285 GOTO END_LOOP;
2286 ELSE
2287 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2288 RAISE FND_API.g_exc_error;
2289 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2290 RAISE FND_API.g_exc_unexpected_error;
2291 END IF;
2292 END IF;
2293 END IF; -- not success status
2294
2295 IF OZF_DEBUG_LOW_ON THEN
2296 OZF_UTILITY_PVT.debug_message('/*--- Get order price succeeded ---*/');
2297 OZF_UTILITY_PVT.debug_message('/*--- Calling process_price_result: ---*/');
2298 END IF;
2299
2300 -- Process pricing result
2301 Process_Pricing_Result(
2302 p_resale_batch_id => p_resale_batch_id,
2303 p_line_tbl => l_line_tbl,
2304 p_inventory_tracking => l_inventory_tracking,
2305 x_return_status => l_return_status
2306 );
2307
2308 -- insert error messages into error stack
2309 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2310 FOR j in 1..l_line_tbl.LAST
2311 LOOP
2312 OZF_RESALE_COMMON_PVT.Insert_Resale_Log (
2313 p_id_value => l_line_tbl(j).chargeback_int_id,
2314 p_id_type => OZF_RESALE_COMMON_PVT.G_ID_TYPE_IFACE,
2315 p_error_code => 'OZF_PROC_PRIC_RESLT_ERR',
2316 p_column_name => NULL,
2317 p_column_value => NULL,
2318 x_return_status => l_return_status);
2319 --
2320 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2321 RAISE FND_API.g_exc_error;
2322 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2323 RAISE FND_API.g_exc_unexpected_error;
2324 END IF;
2325 --
2326 END LOOP;
2327
2328 IF OZF_DEBUG_LOW_ON THEN
2329 OZF_UTILITY_PVT.debug_message('/*--- After process_pricing_result Insert resale log done ---*/');
2330 END IF;
2331
2332 BEGIN
2333 UPDATE ozf_resale_lines_int
2334 SET status_code =OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DISPUTED,
2335 dispute_code = 'OZF_PROC_PRIC_RESLT_ERR',
2336 followup_action_code = 'C',
2337 response_type = 'CA',
2338 response_code = 'N'
2339 WHERE status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_OPEN
2340 AND order_number = l_order_num_tbl(i)
2341 AND date_ordered = l_order_date_tbl(i)
2342 AND sold_from_cust_account_id =l_cust_account_id_tbl(i)
2343 AND dispute_code is null
2344 AND resale_batch_id = p_resale_batch_id;
2345 EXCEPTION
2346 WHEN OTHERS THEN
2347 ozf_utility_pvt.error_message( 'OZF_UPD_RESALE_INT_WRG');
2348 RAISE FND_API.g_exc_unexpected_error;
2349 END;
2350 --
2351 IF OZF_DEBUG_LOW_ON THEN
2352 OZF_UTILITY_PVT.debug_message('/*--- Process Pricing Result Failed ---*/');
2353 END IF;
2354 GOTO END_LOOP;
2355 END IF;
2356
2357 -- Bug 4387465 (+)
2358 UPDATE ozf_resale_lines_int_all
2359 SET status_code= 'PROCESSED'
2360 WHERE status_code = 'OPEN'
2361 AND order_number = l_order_num_tbl(i)
2362 AND sold_from_cust_account_id = l_cust_account_id_tbl(i)
2363 AND date_ordered = l_order_date_tbl(i)
2364 AND tracing_flag = 'T'
2365 AND resale_batch_id = p_resale_batch_id; -- bug 5222273
2366 -- Bug 4387465 (-)
2367
2368 IF OZF_DEBUG_LOW_ON THEN
2369 OZF_UTILITY_PVT.debug_message('/*--- Success: Processing order for order number:'||l_order_num_tbl(i)||'---*/');
2370 OZF_UTILITY_PVT.debug_message('/*--- and cusomter:'||l_cust_account_id_tbl(i)||'---*/');
2371 END IF;
2372 END IF; -- END if FOR order_number, sold_from cust, date_ordered NOT NULL
2373 << END_LOOP >>
2374 NULL;
2375 END LOOP; -- END LOOP FOR l_order_num_tbl
2376 ELSE
2377 IF OZF_DEBUG_LOW_ON THEN
2378 OZF_UTILITY_PVT.debug_message('/*--- No order to process ---*/');
2379 END IF;
2380 END IF;
2381
2382 -- Update Chargeback header with processing detail
2383 OZF_RESALE_COMMON_PVT.Update_Batch_Calculations (
2384 p_api_version => 1.0
2385 ,p_init_msg_list => FND_API.G_FALSE
2386 ,p_commit => FND_API.G_FALSE
2387 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
2388 ,p_resale_batch_id => p_resale_batch_id
2389 ,x_return_status => l_return_status
2390 ,x_msg_data => l_msg_data
2391 ,x_msg_count => l_msg_count
2392 );
2393 IF l_return_status = FND_API.g_ret_sts_error THEN
2394 RAISE FND_API.g_exc_error;
2395 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2396 RAISE FND_API.g_exc_unexpected_error;
2397 END IF;
2398
2399 -- Debug Message
2400 IF OZF_DEBUG_HIGH_ON THEN
2401 OZF_UTILITY_PVT.debug_message(p_message_text => l_full_name||': End');
2402 END IF;
2403 --Standard call to get message count AND if count=1, get the message
2404 FND_MSG_PUB.Count_and_Get (
2405 p_encoded => FND_API.G_FALSE,
2406 p_count => x_msg_count,
2407 p_data => x_msg_data
2408 );
2409 EXCEPTION
2410 WHEN FND_API.g_exc_error THEN
2411 ROLLBACK TO Process_Order;
2412 x_return_status := FND_API.G_RET_STS_ERROR;
2413 -- Standard call to get message count AND IF count=1, get the message
2414 FND_MSG_PUB.Count_and_Get (
2415 p_encoded => FND_API.G_FALSE,
2416 p_count => x_msg_count,
2417 p_data => x_msg_data
2418 );
2419 WHEN FND_API.g_exc_unexpected_error THEN
2420 ROLLBACK TO Process_Order;
2421 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2422 -- Standard call to get message count AND IF count=1, get the message
2423 FND_MSG_PUB.Count_and_Get (
2424 p_encoded => FND_API.G_FALSE,
2425 p_count => x_msg_count,
2426 p_data => x_msg_data
2427 );
2428 WHEN OTHERS THEN
2429 ROLLBACK TO Process_Order;
2430 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2431 IF OZF_UNEXP_ERROR THEN
2432 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2433 END IF;
2434 -- Standard call to get message count AND IF count=1, get the message
2435 FND_MSG_PUB.Count_and_Get (
2436 p_encoded => FND_API.G_FALSE,
2437 p_count => x_msg_count,
2438 p_data => x_msg_data
2439 );
2440 END Process_Order;
2441
2442 END OZF_CHARGEBACK_PVT;