[Home] [Help]
PACKAGE BODY: APPS.OZF_SPECIAL_PRICING_PVT
Source
1 PACKAGE BODY OZF_SPECIAL_PRICING_PVT AS
2 /* $Header: ozfvsppb.pls 120.27 2011/06/13 09:03:21 kpatro ship $ */
3
4 -- Package name : OZF_SPECIAL_PRICING_PVT
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- END of Comments
9
10 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OZF_SPECIAL_PRICING_PVT';
11 G_FILE_NAME CONSTANT VARCHAR2(30) := 'ozfvsppb.pls';
12
13 G_SPECIAL_PRICE_CLASS CONSTANT VARCHAR2(30) := 'SPECIAL_PRICE';
14 G_MEET_REQ CONSTANT VARCHAR2(30) := 'MEET_COMPETITOR';
15 G_BID_REQ CONSTANT VARCHAR2(30) := 'BID_REQUEST';
16 G_BLANKET_REQ CONSTANT VARCHAR2(30) := 'BLANKET_REQUEST';
17 G_SPECIAL_PRICING_OBJ CONSTANT VARCHAR2(30) :='SPECIAL_PRICE';
18
19 G_SPP_UTIL_TYPE CONSTANT VARCHAR2(30) :='UTILIZED';
20
21 OZF_DEBUG_HIGH_ON BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_high);
22 OZF_DEBUG_LOW_ON BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low);
23 OZF_UNEXP_ERROR CONSTANT BOOLEAN := FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.g_msg_lvl_unexp_error);
24 OZF_ERROR CONSTANT BOOLEAN := FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.g_msg_lvl_error);
25
26 G_OFF_INVOICE_OFFER CONSTANT VARCHAR2(30) :='OFF_INVOICE';
27
28 g_inventory_tracking VARCHAR2(1);
29
30
31
32 ---------------------------------------------------------------------
33 -- PROCEDURE
34 -- Initiate_payment
35 --
36 -- PURPOSE
37 -- Initiate payment for a batch.
38 --
39 -- PARAMETERS
40 --
41 -- NOTES
42 --
43 ---------------------------------------------------------------------
44 PROCEDURE Initiate_payment (
45 p_api_version IN NUMBER
46 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
47 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
48 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
49 ,p_resale_batch_id IN NUMBER
50 ,x_return_status OUT NOCOPY VARCHAR2
51 ,x_msg_data OUT NOCOPY VARCHAR2
52 ,x_msg_count OUT NOCOPY NUMBER
53 )
54 IS
55 l_api_name CONSTANT VARCHAR2(30) := 'Initiate_payment';
56 l_api_version CONSTANT NUMBER := 1.0;
57 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
58 --
59 l_return_status VARCHAR2(30);
60 l_msg_data VARCHAR2(2000);
61 l_msg_count NUMBER;
62
63 CURSOR Batch_info_csr (p_id in NUMBER) IS
64 SELECT status_code,
65 batch_type,
66 partner_cust_account_id,
67 partner_id,
68 partner_party_id,
69 report_start_date,
70 report_end_date,
71 batch_number,
72 last_updated_by
73 FROM ozf_resale_batches
74 WHERE resale_batch_id = p_id;
75 l_batch_status VARCHAR2(30);
76 l_batch_type VARCHAR2(30);
77 l_partner_cust_account_id NUMBER;
78 l_partner_id NUMBER;
79 l_partner_party_id NUMBER;
80 l_report_start_date date;
81 l_report_end_date date;
82 l_batch_number VARCHAR2(240);
83 l_last_updated_by NUMBER(15);
84
85 CURSOR OPEN_line_count_csr (p_id in NUMBER) IS
86 SELECT count(1)
87 From ozf_resale_lines_int
88 WHERE resale_batch_id = p_id
89 AND status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_OPEN;
90 l_count NUMBER;
91
92 TYPE id_type IS RECORD (
93 id NUMBER
94 );
95
96 TYPE id_tbl_type is TABLE OF id_type INDEX BY binary_integer;
97
98 l_line_int_rec OZF_RESALE_COMMON_PVT.g_interface_rec_csr%rowtype;
99
100 CURSOR valid_line_id_csr(p_id in NUMBER,
101 p_order_number in VARCHAR2,
102 p_cust_id in NUMBER,
103 p_date in date) IS
104 SELECT resale_line_int_id
105 FROM ozf_resale_lines_int
106 WHERE resale_batch_id = p_id
107 AND order_number = p_order_number
108 AND sold_from_cust_account_id = p_cust_id
109 AND date_ordered = p_date
110 AND status_code = 'PROCESSED';
111 --AND status_code in(OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DUPLICATED, OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED) ;
112
113 l_valid_line_id_tbl id_tbl_type;
114
115 i NUMBER;
116
117 l_create_order_header boolean := false;
118
119 l_chargeback_fund_id NUMBER;
120
121 l_header_id NUMBER;
122 l_line_id NUMBER;
123
124 CURSOR batch_order_num_csr(p_id in NUMBER) IS
125 SELECT distinct order_number,
126 sold_from_cust_account_id,
127 date_ordered
128 FROM ozf_resale_lines_int
129 WHERE resale_batch_id = p_id
130 AND status_code = 'PROCESSED'
131 --AND status_code in(OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DUPLICATED, OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED)
132 ORDER BY date_ordered;
133
134 TYPE order_num_tbl_type IS TABLE OF batch_order_num_csr%rowtype INDEX BY binary_integer;
135 l_order_num_tbl order_num_tbl_type;
136
137 l_inventory_tracking VARCHAR2(1);
138
139 l_sales_transaction_rec OZF_SALES_TRANSACTIONS_PVT.SALES_TRANSACTION_REC_TYPE;
140
141 l_dup_line_id NUMBER;
142
143 l_claim_rec ozf_claim_pvt.claim_rec_type;
144 l_funds_util_flt ozf_claim_accrual_pvt.funds_util_flt_type;
145 l_claim_id NUMBER;
146
147 CURSOR claimed_amount_csr(p_resale_batch_id in NUMBER) IS
148 -- Bug 4496370 (+)
149 --SELECT NVL(amount, 0)
150 SELECT NVL(SUM(amount), 0)
151 -- Bug 4496370 (-)
152 FROM ozf_claims
153 WHERE batch_id = p_resale_batch_id
154 AND batch_type = 'BATCH';
155 l_amount_claimed NUMBER;
156
157
158 l_inventory_level_valid boolean;
159 l_sales_transaction_id NUMBER;
160
161 CURSOR agreement_list_csr(p_resale_batch_id NUMBER) IS
162 SELECT distinct substr(agreement_name, 1, 30)
163 FROM ozf_resale_lines_int
164 where resale_batch_id = p_resale_batch_id;
165
166 TYPE agreement_name_type IS RECORD (
167 agreement_name VARCHAR2(30)
168 );
169
170 TYPE agreement_tbl_type IS TABLE OF agreement_name_type INDEX BY binary_integer;
171
172 l_agreement_tbl agreement_tbl_type;
173
174 CURSOR request_header_id_csr (p_agreement_number VARCHAR2,
175 p_partner_id NUMBER) IS
176 SELECT a.request_header_id,
177 a.request_number
178 FROM ozf_request_headers_all_vl a
179 WHERE a.agreement_number = p_agreement_number
180 AND a.status_code = 'APPROVED'
181 AND a.partner_id = p_partner_id
182 AND a.request_class = G_SPECIAL_PRICE_CLASS;
183
184 l_request_header_id NUMBER;
185 l_request_number VARCHAR2(30);
186 l_batch_disputed BOOLEAN := false;
187
188 CURSOR dup_header_id_csr( p_id IN NUMBER
189 , p_order_number IN VARCHAR2
190 , p_cust_id IN NUMBER
191 , p_date IN DATE ) IS
192 SELECT a.resale_header_id
193 FROM ozf_resale_headers a
194 , ozf_resale_lines_int b
195 , ozf_resale_lines c
196 WHERE b.resale_batch_id = p_id
197 AND b.order_number = p_order_number
198 AND b.sold_from_cust_account_id = p_cust_id
199 AND b.date_ordered = p_date
200 AND b.status_code IN ('DUPLICATED', 'PROCESSED')
201 AND b.duplicated_line_id = c.resale_line_id
202 AND c.resale_header_id = a.resale_header_id;
203
204 l_dup_header_id_tbl OZF_RESALE_COMMON_PVT.number_tbl_type;
205
206 --Start POS Batch Processing Changes
207 CURSOR csr_batch_request(cv_batch_id IN NUMBER, cv_partner_id IN NUMBER) IS
208 SELECT count(1)
209
210 FROM ozf_resale_lines_int s
211 , ozf_request_headers_all_b r
212 WHERE s.resale_batch_id = cv_batch_id
213 AND s.agreement_name = r.agreement_number
214 AND r.partner_id = cv_partner_id
215 AND r.status_code = 'APPROVED'
216 AND r.request_class = 'SPECIAL_PRICE'
217 AND r.offer_type='SCAN_DATA'
218 GROUP BY r.request_header_id
219 , r.request_number;
220
221 l_scan_data_cnt NUMBER(30);
222
223 l_auto_claim_profile varchar2(10):= FND_PROFILE.value('OZF_AUTO_CLAIM_POS');
224
225
226 --End POS Batch Processing Changes
227
228
229 BEGIN
230 -- Standard begin of API savepoint
231 SAVEPOINT INIT_PAYMENT_SPP;
232 -- Standard call to check for call compatibility.
233 IF NOT FND_API.Compatible_API_Call (
234 l_api_version,
235 p_api_version,
236 l_api_name,
237 G_PKG_NAME)
238 THEN
239 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
240 END IF;
241
242 --Initialize message list if p_init_msg_list is TRUE.
243 IF FND_API.To_Boolean (p_init_msg_list) THEN
244 FND_MSG_PUB.initialize;
245 END IF;
246
247 -- Debug Message
248 IF OZF_DEBUG_HIGH_ON THEN
249 OZF_UTILITY_PVT.debug_message(p_message_text => l_full_name||': Start');
250 END IF;
251
252 -- Initialize API return status to sucess
253 x_return_status := FND_API.G_RET_STS_SUCCESS;
254
255 OPEN Batch_info_csr(p_resale_batch_id);
256 FETCH batch_info_csr INTO l_batch_status,
257 l_batch_type,
258 l_partner_cust_account_id,
259 l_partner_id,
260 l_partner_party_id,
261 l_report_start_date,
262 l_report_end_date,
263 l_batch_number,
264 l_last_updated_by;
265 CLOSE batch_info_csr;
266
267 IF l_batch_status = OZF_RESALE_COMMON_PVT.G_BATCH_PENDING_PAYMENT THEN
268
269 OPEN OPEN_line_count_csr(p_resale_batch_id);
270 FETCH OPEN_line_count_csr INTO l_count;
271 CLOSE OPEN_line_count_csr;
272
273 IF l_count <> 0 THEN
274 --Can not pay if there is an OPEN line
275 ozf_utility_pvt.error_message('OZF_RESALE_OPEN_LINE_EXIST');
276 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
277 ELSE
278 -- There is no need to find a fund id for the SPECIAL PRICING REQUEST
279 l_chargeback_fund_id := NULL;
280
281 -- Check whether there is a need to do inventory_verification
282 OPEN OZF_RESALE_COMMON_PVT.g_inventory_tracking_csr;
283 FETCH OZF_RESALE_COMMON_PVT.g_inventory_tracking_csr INTO l_inventory_tracking;
284 CLOSE OZF_RESALE_COMMON_PVT.g_inventory_tracking_csr;
285
286 IF l_inventory_tracking = 'T' THEN
287 -- Bug 4380203 (+)
288 OZF_SALES_TRANSACTIONS_PVT.Initiate_Inventory_tmp (
289 p_api_version => 1.0
290 ,p_init_msg_list => FND_API.G_FALSE
291 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
292 ,p_resale_batch_id => p_resale_batch_id
293 ,p_start_date => l_report_start_date
294 ,p_end_date => l_report_end_date
295 ,x_return_status => l_return_status
296 ,x_msg_count => l_msg_count
297 ,x_msg_data => l_msg_data
298 );
299 IF l_return_status = FND_API.g_ret_sts_error THEN
300 RAISE FND_API.g_exc_error;
301 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
302 RAISE FND_API.g_exc_unexpected_error;
303 END IF;
304
305 /*
306 OZF_SALES_TRANSACTIONS_PVT.Initiate_Inventory_tmp (
307 p_api_version => 1.0
308 ,p_init_msg_list => FND_API.G_FALSE
309 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
310 ,p_party_id => l_partner_party_id
311 ,p_start_date => l_report_start_date
312 ,x_return_status => l_return_status
313 ,x_msg_count => l_msg_count
314 ,x_msg_data => l_msg_data
315 );
316
317 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
318 ozf_utility_pvt.error_message('OZF_RESALE_INIT_INV_TMP_ERR');
319 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
320 END IF;
321 */
322 -- Bug 4380203 (-)
323 END IF;
324 /*
325 -- SLKRISHN change to use Update_Duplicates
326 -- Check for Duplicates
327 OZF_RESALE_COMMON_PVT.Update_Duplicates (
328 p_api_version => 1.0,
329 p_init_msg_list => FND_API.G_FALSE,
330 p_commit => FND_API.G_FALSE,
331 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
332 p_resale_batch_id => p_resale_batch_id,
333 p_resale_batch_type => l_batch_type,
334 p_batch_status => l_batch_status,
335 x_batch_status => l_batch_status,
336 x_return_status => l_return_status,
337 x_msg_count => l_msg_count,
338 x_msg_data => l_msg_data);
339 --
340 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
341 RAISE FND_API.G_EXC_ERROR;
342 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
343 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
344 END IF;
345
346 IF l_batch_status = OZF_RESALE_COMMON_PVT.G_BATCH_REJECTED THEN
347 IF OZF_DEBUG_LOW_ON THEN
348 OZF_UTILITY_PVT.debug_message('OZF_RESALE_REJECTED');
349 END IF;
350 ELSE
351 */
352 --i:=1;
353 IF l_order_num_tbl.EXISTS(1) THEN
354 l_order_num_tbl.DELETE;
355 END IF;
356 OPEN batch_order_num_csr(p_resale_batch_id);
357 FETCH batch_order_num_csr BULK COLLECT INTO l_order_num_tbl;
358 --LOOP
359 -- FETCH batch_order_num_csr INTO l_order_num_tbl(i);
360 -- EXIT WHEN batch_order_num_csr%NOTFOUND;
361 -- i:= i+1;
362 --END LOOP;
363 CLOSE batch_order_num_csr;
364
365 IF l_order_num_tbl.EXISTS(1) THEN
366 FOR k in 1..l_order_num_tbl.LAST LOOP
367 IF OZF_DEBUG_LOW_ON THEN
368 ozf_utility_PVT.debug_message('PROCESS ORDER: ');
369 ozf_utility_PVT.debug_message('ORDER NUMBER: '||l_order_num_tbl(k).order_number);
370 ozf_utility_PVT.debug_message('sold_from_ACCT: '||l_order_num_tbl(k).sold_from_cust_account_id);
371 ozf_utility_PVT.debug_message('DATE ORDERED: '||l_order_num_tbl(k).date_ordered);
372 END IF;
373
374 -- beginjxwu header_fix
375 -- Here, I assume if a line is the duplicate of another line, then they share
376 -- the same order header. Hence all order with this duplicated line share the
377 -- the same order with the oringinal lines.
378 OPEN dup_header_id_csr(p_resale_batch_id,
379 l_order_num_tbl(k).order_number,
380 l_order_num_tbl(k).sold_from_cust_account_id,
381 l_order_num_tbl(k).date_ordered
382 );
383 FETCH dup_header_id_csr BULK COLLECT INTO l_dup_header_id_tbl;
384 CLOSE dup_header_id_csr;
385
386 IF l_dup_header_id_tbl.EXISTS(1) THEN
387 IF l_dup_header_id_tbl.EXISTS(2) THEN
388
389 -- There is something wrong with this order. dispute all the orders
390 -- and move to the next one.
391 -- JXWU move update to common pvt
392 UPDATE ozf_resale_lines_int_all
393 SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DISPUTED,
394 dispute_code = 'OZF_RESALE_MULTI_HEADER',
395 followup_action_code = NVL2(followup_action_code,followup_action_code,'C'),
396 response_type = 'CA',
397 response_code = 'N'
398 WHERE resale_batch_id = p_resale_batch_id
399 AND order_number = l_order_num_tbl(k).order_number
400 AND sold_from_cust_account_id = l_order_num_tbl(k).sold_from_cust_account_id
401 AND date_ordered = l_order_num_tbl(k).date_ordered
402 AND status_code in (OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DUPLICATED, OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED);
403
404 GOTO END_ORDER_HEADER;
405 ELSE
406 l_create_order_header := false;
407 l_header_id := l_dup_header_id_tbl(1);
408 END IF;
409 ELSE
410 l_create_order_header := true;
411 END IF;
412
413 --End jxuw header_fix
414
415 --i:=1;
416 -- Here only duplicated and processed lines are considered. Disputed lines will not
417 -- be moved to resale order table.
418 IF l_valid_line_id_tbl.EXISTS(1) THEN
419 l_valid_line_id_tbl.DELETE;
420 END IF;
421 OPEN valid_line_id_csr(p_resale_batch_id,
422 l_order_num_tbl(k).order_number,
423 l_order_num_tbl(k).sold_from_cust_account_id,
424 l_order_num_tbl(k).date_ordered);
425 FETCH valid_line_id_csr BULK COLLECT INTO l_valid_line_id_tbl;
426 --LOOP
427 -- FETCH valid_line_id_csr INTO l_valid_line_id_tbl(i);
428 -- EXIT WHEN valid_line_id_csr%NOTFOUND;
429 -- i := i+1;
430 --END LOOP;
431 CLOSE valid_line_id_csr;
432
433 -- Again, we need to check whether if any line is a duplicate or not.
434 IF l_valid_line_id_tbl.EXISTS(1) THEN
435
436 -- I then try to create resale data.
437 For j in 1..l_valid_line_id_tbl.last
438 LOOP
439 IF OZF_DEBUG_LOW_ON THEN
440 ozf_utility_PVT.debug_message('Current line_int_id:' || l_valid_line_id_tbl(j).id);
441 END IF;
442
443 OPEN OZF_RESALE_COMMON_PVT.g_interface_rec_csr(l_valid_line_id_tbl(j).id);
444 FETCH OZF_RESALE_COMMON_PVT.g_interface_rec_csr INTO l_line_int_rec;
445 CLOSE OZF_RESALE_COMMON_PVT.g_interface_rec_csr;
446
447 -- Need to check against inventory
448 IF l_line_int_rec.status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED AND
449 l_inventory_tracking = 'T' THEN
450
451 -- Check inventory level first
452 OZF_SALES_TRANSACTIONS_PVT.Validate_Inventory_level (
453 p_api_version => 1.0
454 ,p_init_msg_list => FND_API.G_FALSE
455 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
456 ,p_line_int_rec => l_line_int_rec
457 ,x_valid => l_inventory_level_valid
458 ,x_return_status => l_return_status
459 ,x_msg_count => l_msg_count
460 ,x_msg_data => l_msg_data
461 );
462
463 IF not l_inventory_level_valid THEN
464 IF OZF_DEBUG_LOW_ON THEN
465 ozf_utility_PVT.debug_message('Did not pass inventory checking');
466 END IF;
467
468 -- Here turn this line to disputed and create a disput code for it.
469 UPDATE ozf_resale_lines_int
470 SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DISPUTED,
471 dispute_code = 'OZF_LT_INVT',
472 followup_action_code = NVL2(followup_action_code,followup_action_code,'C'),
473 response_type = 'CA',
474 response_code = 'N'
475 WHERE resale_line_int_id = l_line_int_rec.resale_line_int_id;
476
477 -- SET Batch as disputed
478 UPDATE ozf_resale_batches
479 SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_DISPUTED
480 WHERE resale_batch_id = l_line_int_rec.resale_batch_id;
481
482 goto END_LOOP2;
483 END IF;
484
485 -- Check WAC
486
487 END IF;
488
489 -- First, check whether there is need to create a header for this order
490 IF j = 1 THEN
491 -- Determin header id
492 IF l_create_order_header THEN
493 OZF_RESALE_COMMON_PVT.Insert_resale_header(
494 p_api_version => 1
495 ,p_init_msg_list => FND_API.G_FALSE
496 ,p_commit => FND_API.G_FALSE
497 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
498 ,p_line_int_rec => l_line_int_rec
499 ,x_header_id => l_header_id
500 ,x_return_status => l_return_status
501 ,x_msg_data => l_msg_data
502 ,x_msg_count => l_msg_count
503 );
504 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
505 RAISE FND_API.G_EXC_ERROR;
506 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
507 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
508 END IF;
509 ELSE
510 NULL;
511 -- We should have the l_header_id FROM the order level
512 END IF;
513 END IF;
514
515 IF l_line_int_rec.status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED THEN
516 IF l_line_int_rec.duplicated_line_id is NULL THEN
517
518 -- No problem so far. Insert INTO batch_lines table
519 OZF_RESALE_COMMON_PVT.Insert_resale_line(
520 p_api_version => 1
521 ,p_init_msg_list => FND_API.G_FALSE
522 ,p_commit => FND_API.G_FALSE
523 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
524 ,p_line_int_rec => l_line_int_rec
525 ,p_header_id => l_header_id
526 ,x_line_id => l_line_id
527 ,x_return_status => l_return_status
528 ,x_msg_data => l_msg_data
529 ,x_msg_count => l_msg_count
530 );
531 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
532 RAISE FND_API.G_EXC_ERROR;
533 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
534 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
535 END IF;
536
537 -- FOR processed order line, I need to create a transaction FOR it.
538 OZF_RESALE_COMMON_PVT.Create_Sales_Transaction (
539 p_api_version => 1.0
540 ,p_init_msg_list => FND_API.G_FALSE
541 ,p_commit => FND_API.G_FALSE
542 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
543 ,p_line_int_rec => l_line_int_rec
544 ,p_header_id => l_header_id
545 ,p_line_id => l_line_id
546 ,x_sales_transaction_id => l_sales_transaction_id
547 ,x_return_status => l_return_status
548 ,x_msg_data => l_msg_data
549 ,x_msg_count => l_msg_count
550 );
551 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
552 RAISE FND_API.G_EXC_ERROR;
553 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
554 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
555 END IF;
556 -- Bug 4380203 Fixing (+)
557 -- Bug 4380203 Fixing: Inventory Temp table is already updated in Validate_Inventory_Level
558 /*
559 IF l_inventory_tracking = 'T' THEN
560 OZF_SALES_TRANSACTIONS_PVT.UPDATE_inventory_tmp (
561 p_api_version => 1.0
562 ,p_init_msg_list => FND_API.G_FALSE
563 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
564 ,p_sales_transaction_id => l_sales_transaction_id
565 ,x_return_status => l_return_status
566 ,x_msg_data => l_msg_data
567 ,x_msg_count => l_msg_count
568 );
569 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
570 RAISE FND_API.G_EXC_ERROR;
571 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
572 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
573 END IF;
574 END IF;
575 */
576 -- Bug 4380203 Fixing (-)
577 ELSE
578 l_line_id := l_line_int_rec.duplicated_line_id;
579 END IF;
580 ELSIF l_line_int_rec.status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DUPLICATED THEN
581 l_line_id := l_line_int_rec.duplicated_line_id;
582 END IF;
583 IF OZF_DEBUG_LOW_ON THEN
584 OZF_UTILITY_PVT.debug_message('line_id is '|| l_line_id);
585 END IF;
586
587 IF l_line_int_rec.status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED OR
588 l_line_int_rec.status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DUPLICATED THEN
589
590 -- only create mapping for the lines that are processed or
591 -- duplicated, yet the adjustment is new then
592 OZF_RESALE_COMMON_PVT.Insert_resale_line_mapping(
593 p_api_version => 1
594 ,p_init_msg_list => FND_API.G_FALSE
595 ,p_commit => FND_API.G_FALSE
596 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
597 ,p_resale_batch_id => p_resale_batch_id
598 ,p_line_id => l_line_id
599 ,x_return_status => l_return_status
600 ,x_msg_data => l_msg_data
601 ,x_msg_count => l_msg_count
602 );
603 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
604 RAISE FND_API.G_EXC_ERROR;
605 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
606 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
607 END IF;
608 END IF;
609
610 IF l_line_int_rec.status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED AND
611 l_line_int_rec.tracing_flag = 'F' THEN
612 OZF_RESALE_COMMON_PVT.Create_Utilization(
613 p_api_version => 1.0
614 ,p_init_msg_LIST => FND_API.G_FALSE
615 ,p_commit => FND_API.G_FALSE
616 ,p_validation_level=> FND_API.G_VALID_LEVEL_FULL
617 ,p_line_int_rec => l_line_int_rec
618 ,p_fund_id => l_chargeback_fund_id
619 ,p_line_id => l_line_id
620 ,p_cust_account_id => l_partner_cust_account_id
621 ,p_approver_id => l_last_updated_by
622 ,x_return_status => l_return_status
623 ,x_msg_data => l_msg_data
624 ,x_msg_count => l_msg_count
625 );
626 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
627 RAISE FND_API.G_EXC_ERROR;
628 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
629 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
630 END IF;
631
632 END IF; -- if this line is a processed one
633 << END_LOOP2 >>
634 NULL;
635 END LOOP; -- END LOOP for this order
636 END IF; -- if valid line id EXISTS
637 << END_ORDER_HEADER>>
638 NULL;
639 END LOOP; -- END LOOP for the batch
640 END IF; -- END order_num EXISTS
641 -- END IF; -- if not rejected
642 END IF; -- END l_count = 0
643
644 --Added code to call the claim creation process conditionally based on the profile option 'OZF: Auto Claim creation for POS' for Ship from stock accrual offers of SPR.
645 OPEN csr_batch_request(p_resale_batch_id, l_partner_id);
646 FETCH csr_batch_request INTO l_scan_data_cnt ;
647 IF (csr_batch_request%NOTFOUND) THEN
648 l_scan_data_cnt := 0;
649 END IF;
650 CLOSE csr_batch_request;
651
652 IF (l_auto_claim_profile = 'Y' ) OR (l_scan_data_cnt <> 0) THEN
653 OZF_Claim_Accrual_PVT.Initiate_Batch_Payment(
654 p_api_version => 1.0
655 ,p_init_msg_list => FND_API.g_false
656 ,p_commit => FND_API.g_false
657 ,p_validation_level => FND_API.g_valid_level_full
658 ,x_return_status => l_return_status
659 ,x_msg_count => l_msg_count
660 ,x_msg_data => l_msg_data
661 ,p_resale_batch_id => p_resale_batch_id
662 );
663
664 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
665 RAISE FND_API.G_EXC_ERROR;
666 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
667 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
668 END IF;
669
670 OPEN claimed_amount_csr(p_resale_batch_id);
671 FETCH claimed_amount_csr INTO l_amount_claimed;
672 CLOSE claimed_amount_csr;
673
674 IF l_amount_claimed <> 0 THEN
675
676 -- IF anything is paid, UPDATE batch line status to CLOSEd for each OPEN and duplicated lines.
677 BEGIN
678 UPDATE ozf_resale_lines_int
679 SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_CLOSED
680 WHERE resale_batch_id = p_resale_batch_id
681 AND status_code in(OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DUPLICATED, OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED) ;
682
683 -- UPDATE batch status to CLOSEd -- might change later.
684 UPDATE ozf_resale_batches
685 SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_CLOSED,
686 paid_amount = l_amount_claimed
687 WHERE resale_batch_id = p_resale_batch_id;
688 EXCEPTION
689 WHEN OTHERS THEN
690 ozf_utility_pvt.error_message('OZF_UPD_RESALE_WRG','TEXT',l_full_name||': END');
691 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
692 END;
693 END IF;
694 ELSE
695 BEGIN
696 -- UPDATE batch line status to CLOSED for duplicated and processed lines
697
698 UPDATE ozf_resale_lines_int
699 SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_CLOSED
700 WHERE resale_batch_id = p_resale_batch_id
701 AND status_code in(OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DUPLICATED, OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED) ;
702
703
704 -- UPDATE batch status to CLOSED
705 UPDATE ozf_resale_batches
706 SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_CLOSED
707 WHERE resale_batch_id = p_resale_batch_id;
708 EXCEPTION
709 WHEN OTHERS THEN
710 ozf_utility_pvt.error_message('OZF_UPD_RESALE_WRG','TEXT',l_full_name||': END');
711 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
712 END;
713
714
715 END IF;
716 ELSE
717 -- Only disputed and processed batch can be paid.
718 ozf_utility_pvt.error_message('OZF_RESALE_WRONG_STAUS_TO_PAY');
719 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
720 END IF;
721
722 -- Debug Message
723 IF OZF_DEBUG_HIGH_ON THEN
724 OZF_UTILITY_PVT.debug_message(p_message_text => l_full_name||': End');
725 END IF;
726 --Standard call to get message count and if count=1, get the message
727 FND_MSG_PUB.Count_And_Get (
728 p_encoded => FND_API.G_FALSE,
729 p_count => x_msg_count,
730 p_data => x_msg_data
731 );
732 x_return_status := l_return_status;
733 EXCEPTION
734 WHEN FND_API.G_EXC_ERROR THEN
735 ROLLBACK TO INIT_PAYMENT_SPP;
736 x_return_status := FND_API.G_RET_STS_ERROR;
737 -- Standard call to get message count and if count=1, get the message
738 FND_MSG_PUB.Count_And_Get (
739 p_encoded => FND_API.G_FALSE,
740 p_count => x_msg_count,
741 p_data => x_msg_data
742 );
743 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
744 ROLLBACK TO INIT_PAYMENT_SPP;
745 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
746
747 -- Standard call to get message count and if count=1, get the message
748 FND_MSG_PUB.Count_And_Get (
749 p_encoded => FND_API.G_FALSE,
750 p_count => x_msg_count,
751 p_data => x_msg_data
752 );
753 WHEN OTHERS THEN
754 ROLLBACK TO INIT_PAYMENT_SPP;
755 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
756
757 -- Standard call to get message count and if count=1, get the message
758 FND_MSG_PUB.Count_And_Get (
759 p_encoded => FND_API.G_FALSE,
760 p_count => x_msg_count,
761 p_data => x_msg_data
762 );
763 END Initiate_payment;
764
765 ---------------------------------------------------------------------
766 -- PROCEDURE
767 -- Validate_Order_Record
768 --
769 -- PURPOSE
770 -- This procedure validates the order information
771 -- These are validation specific to chargeback process
772 --
773 -- PARAMETERS
774 --
775 --
776 -- NOTES
777 ---------------------------------------------------------------------
778 PROCEDURE Validate_Order_Record(
779 p_api_version IN NUMBER
780 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
781 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
782 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
783 ,p_resale_batch_id IN NUMBER
784 ,x_return_status OUT NOCOPY VARCHAR2
785 ,x_msg_data OUT NOCOPY VARCHAR2
786 ,x_msg_count OUT NOCOPY NUMBER
787 )IS
788 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Order_Record';
789 l_api_version CONSTANT NUMBER := 1.0;
790 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
791 --
792
793 BEGIN
794 -- Standard begin of API savepoint
795 -- Standard call to check for call compatibility.
796 IF NOT FND_API.Compatible_API_Call (
797 l_api_version,
798 p_api_version,
799 l_api_name,
800 G_PKG_NAME)
801 THEN
802 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
803 END IF;
804
805 --Initialize message list if p_init_msg_list is TRUE.
806 IF FND_API.To_Boolean (p_init_msg_list) THEN
807 FND_MSG_PUB.initialize;
808 END IF;
809
810 -- Debug Message
811 IF OZF_DEBUG_HIGH_ON THEN
812 OZF_UTILITY_PVT.debug_message(p_message_text => l_full_name||': Start');
813 END IF;
814
815 -- Initialize API return status to sucess
816 x_return_status := FND_API.G_RET_STS_SUCCESS;
817
818 -- agreement_type should be 'SPO'
819 BEGIN
820 INSERT INTO ozf_resale_logs_all(
821 RESALE_LOG_ID,
822 RESALE_ID,
823 RESALE_ID_TYPE,
824 ERROR_CODE,
825 ERROR_MESSAGE,
826 COLUMN_NAME,
827 COLUMN_VALUE,
828 ORG_ID
829 ) SELECT
830 ozf_resale_logs_all_s.nextval,
831 resale_line_int_id,
832 'IFACE',
833 'OZF_RESALE_AGRM_TYPE_WNG',
834 FND_MESSAGE.get_string('OZF','OZF_RESALE_AGR_TYPE_WNG'),
835 'AGREEMENT_TYPE',
836 NULL,
837 org_id
838 FROM ozf_resale_lines_int_all b
839 WHERE b.status_code = 'OPEN'
840 AND b.tracing_flag = 'F'
841 AND b.agreement_type <>'SPO'
842 AND b.resale_batch_id = p_resale_batch_id
843 AND NOT EXISTS(SELECT 1
844 FROM ozf_resale_logs_all a
845 WHERE a.resale_id = b.resale_line_int_id
846 AND a.RESALE_ID_TYPE = 'IFACE'
847 AND a.error_code ='OZF_RESALE_AGRM_TYPE_WNG'
848 );
849 EXCEPTION
850 WHEN OTHERS THEN
851 OZF_UTILITY_PVT.error_message(
852 p_message_name => 'OZF_INS_RESALE_LOG_WRG',
853 p_token_name => 'TEXT',
854 p_token_value => l_full_name||': END');
855 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
856 END;
857
858 BEGIN
859 UPDATE ozf_resale_lines_int_all
860 SET status_code = 'DISPUTED',
861 dispute_code = 'OZF_RESALE_AGRM_TYPE_WNG',
862 followup_action_code = NVL2(followup_action_code,followup_action_code,'C'),
863 response_type = 'CA',
864 response_code = 'N'
865 WHERE status_code = 'OPEN'
866 AND tracing_flag = 'F'
867 AND agreement_type <>'SPO'
868 AND resale_batch_id = p_resale_batch_id;
869 EXCEPTION
870 WHEN OTHERS THEN
871 OZF_UTILITY_PVT.error_message(
872 p_message_name => 'OZF_UPD_RESALE_INT_WRG',
873 p_token_name => 'TEXT',
874 p_token_value => l_full_name||': END');
875 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
876 END;
877
878 -- purchase price not be NULL
879 BEGIN
880 INSERT INTO ozf_resale_logs_all(
881 RESALE_LOG_ID,
882 RESALE_ID,
883 RESALE_ID_TYPE,
884 ERROR_CODE,
885 ERROR_MESSAGE,
886 COLUMN_NAME,
887 COLUMN_VALUE,
888 ORG_ID
889 ) SELECT
890 ozf_resale_logs_all_s.nextval,
891 resale_line_int_id,
892 OZF_RESALE_COMMON_PVT.G_ID_TYPE_IFACE,
893 'OZF_RESALE_PUR_PRICE_MISSING',
894 fnd_message.get_string('OZF','OZF_RESALE_PUR_PRICE_MISSING'),
895 'PURCHASE_PRICE',
896 NULL,
897 org_id
898 FROM ozf_resale_lines_int_all b
899 WHERE b.status_code = 'OPEN'
900 AND b.tracing_flag = 'F'
901 AND b.purchase_price IS NULL
902 AND b.resale_batch_id = p_resale_batch_id
903 AND NOT EXISTS(SELECT 1
904 FROM ozf_resale_logs_all a
905 WHERE a.resale_id = b.resale_line_int_id
906 AND a.RESALE_ID_TYPE = 'IFACE'
907 AND a.error_code ='OZF_RESALE_PUR_PRICE_MISSING'
908 );
909 EXCEPTION
910 WHEN OTHERS THEN
911 OZF_UTILITY_PVT.error_message(
912 p_message_name => 'OZF_INS_RESALE_LOG_WRG',
913 p_token_name => 'TEXT',
914 p_token_value => l_full_name||': END');
915 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
916 END;
917
918 BEGIN
919 UPDATE ozf_resale_lines_int_all
920 SET status_code = 'DISPUTED',
921 dispute_code = 'OZF_RESALE_PUR_PRICE_MISSING',
922 followup_action_code = NVL2(followup_action_code,followup_action_code,'C'),
923 response_type = 'CA',
924 response_code = 'N'
925 WHERE status_code = 'OPEN'
926 AND tracing_flag = 'F'
927 AND purchase_price IS NULL
928 AND resale_batch_id = p_resale_batch_id;
929 EXCEPTION
930 WHEN OTHERS THEN
931 OZF_UTILITY_PVT.error_message(
932 p_message_name => 'OZF_UPD_RESALE_INT_WRG',
933 p_token_name => 'TEXT',
934 p_token_value => l_full_name||': END');
935 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
936 END;
937
938 -- Debug Message
939 IF OZF_DEBUG_HIGH_ON THEN
940 OZF_UTILITY_PVT.debug_message(p_message_text => l_full_name||': End');
941 END IF;
942 --Standard call to get message count and if count=1, get the message
943 FND_MSG_PUB.Count_And_Get (
944 p_encoded => FND_API.G_FALSE,
945 p_count => x_msg_count,
946 p_data => x_msg_data
947 );
948 EXCEPTION
949 WHEN FND_API.G_EXC_ERROR THEN
950 x_return_status := FND_API.G_RET_STS_ERROR;
951 -- Standard call to get message count and if count=1, get the message
952 FND_MSG_PUB.Count_And_Get (
953 p_encoded => FND_API.G_FALSE,
954 p_count => x_msg_count,
955 p_data => x_msg_data
956 );
957 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
958 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
959 -- Standard call to get message count and if count=1, get the message
960 FND_MSG_PUB.Count_And_Get (
961 p_encoded => FND_API.G_FALSE,
962 p_count => x_msg_count,
963 p_data => x_msg_data
964 );
965 WHEN OTHERS THEN
966 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
967
968 -- Standard call to get message count and if count=1, get the message
969 FND_MSG_PUB.Count_And_Get (
970 p_encoded => FND_API.G_FALSE,
971 p_count => x_msg_count,
972 p_data => x_msg_data
973 );
974 END Validate_Order_Record;
975
976 ---------------------------------------------------------------------
977 -- PROCEDURE
978 -- Process_One_Order
979 --
980 -- PURPOSE
981 -- Process information of a single order
982 --
983 -- PARAMETERS
984 --
985 -- NOTES
986 --
987 ---------------------------------------------------------------------
988 PROCEDURE Process_One_Order(
989 p_order_number IN VARCHAR2
990 ,p_sold_from_cust_account_id IN NUMBER
991 ,p_date_ordered IN DATE
992 ,p_resale_batch_id IN NUMBER
993 ,p_partner_id IN NUMBER
994 ,x_return_status OUT NOCOPY VARCHAR2
995 )IS
996 l_api_name CONSTANT VARCHAR2(30) := 'Process_One_Order';
997 l_api_version CONSTANT NUMBER := 1.0;
998 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
999
1000 l_return_status VARCHAR2(30):=FND_API.G_RET_STS_SUCCESS;
1001 l_msg_count NUMBER;
1002 l_msg_data VARCHAR2(2000);
1003 l_agreement_name VARCHAR2(400);
1004
1005
1006 CURSOR order_set_csr(p_order_number VARCHAR2,
1007 p_id NUMBER, p_date date,
1008 p_resale_id NUMBER) IS
1009 SELECT *
1010 FROM ozf_resale_lines_int
1011 WHERE order_number = p_order_number
1012 AND sold_from_cust_account_id= p_id
1013 AND date_ordered = p_date
1014 AND status_code = 'OPEN'
1015 --AND status_code in(OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_OPEN, OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DUPLICATED)
1016 AND duplicated_adjustment_id is NULL
1017 AND resale_batch_id = p_resale_id
1018 AND tracing_flag = 'F';
1019
1020 TYPE resale_lines_tbl_type is TABLE OF order_set_csr%rowtype INDEX BY binary_integer;
1021 l_order_set_tbl resale_lines_tbl_type;
1022
1023 -- bug # 6821886 fixed by ateotia (+)
1024 CURSOR all_order_set_csr(p_order_number VARCHAR2,
1025 p_id NUMBER, p_date date,
1026 p_resale_id NUMBER) IS
1027 SELECT *
1028 FROM ozf_resale_lines_int
1029 WHERE order_number = p_order_number
1030 AND sold_from_cust_account_id= p_id
1031 AND date_ordered = p_date
1032 AND status_code = 'OPEN'
1033 AND duplicated_adjustment_id is NULL
1034 AND resale_batch_id = p_resale_id;
1035
1036 TYPE all_resale_lines_tbl_type is TABLE OF order_set_csr%rowtype INDEX BY binary_integer;
1037 l_all_order_set_tbl all_resale_lines_tbl_type;
1038 -- bug # 6821886 fixed by ateotia (-)
1039
1040 k NUMBER;
1041
1042 CURSOR request_header_info_csr(p_agreement_num VARCHAR2,
1043 p_partner_id NUMBER) IS
1044 SELECT a.request_header_id,
1045 -- BUG 4627231 (+)
1046 -- a.start_date,
1047 -- a.end_date,
1048 TRUNC(a.start_date) start_date,
1049 TRUNC(a.end_date) end_date,
1050 -- BUG 4627231 (-)
1051 a.currency_code,
1052 a.request_type_code,
1053 a.end_cust_party_id,
1054 a.reseller_party_id,
1055 a.offer_id,
1056 a.offer_type,
1057 a.ship_from_stock_flag --POS Batch Processing by profiles by ateotia
1058 FROM ozf_request_headers_all_vl a
1059 WHERE a.agreement_number = p_agreement_num
1060 AND a.status_code = 'APPROVED'
1061 AND a.partner_id = p_partner_id
1062 AND a.request_class = G_SPECIAL_PRICE_CLASS;
1063
1064 l_ship_from_stock_flag VARCHAR2(1); --POS Batch Processing by profiles by ateotia
1065
1066 --POS Batch Processing by profiles by ateotia (+)
1067 CURSOR accrued_quantity_csr(p_request_header_id NUMBER,
1068 p_inventory_item_id NUMBER) IS
1069 SELECT sum(orl.quantity) used_quantity,
1070 orl.uom_code
1071 FROM ozf_resale_lines_all orl,
1072 ozf_resale_adjustments_all ora
1073 WHERE orl.inventory_item_id = p_inventory_item_id
1074 AND NVL(ora.corrected_agreement_id, ora.agreement_id) = p_request_header_id
1075 AND orl.resale_line_id = ora.resale_line_id
1076 AND ora.agreement_type = 'SPO'
1077 AND ora.status_code = 'CLOSED'
1078 GROUP BY orl.uom_code;
1079 --POS Batch Processing by profiles by ateotia (-)
1080
1081
1082 l_request_header_id NUMBER;
1083 l_header_start_date date;
1084 l_header_END_date date;
1085 l_request_type_code VARCHAR2(30);
1086 l_header_currency_code VARCHAR2(30);
1087 l_header_end_cust_party_id NUMBER;
1088 l_header_reseller_party_id NUMBER;
1089 l_offer_id NUMBER;
1090 l_offer_type VARCHAR2(30);
1091
1092 CURSOR request_line_info_csr(p_inventory_id NUMBER,
1093 p_agreement_num VARCHAR2,
1094 p_partner_id NUMBER) IS
1095 SELECT a.request_line_id,
1096 a.uom,
1097 a.quantity,
1098 a.item_price,
1099 a.approved_type,
1100 a.approved_amount,
1101 a.approved_max_qty,
1102 a.approved_min_qty
1103 FROM ozf_request_lines_all a,
1104 ozf_request_headers_all_vl b
1105 WHERE a.item_type = 'PRODUCT'
1106 AND a.item_id = p_inventory_id
1107 AND a.request_header_id = b.request_header_id
1108 AND b.agreement_number = p_agreement_num
1109 AND b.status_code = 'APPROVED'
1110 AND b.request_class = G_SPECIAL_PRICE_CLASS
1111 AND b.partner_id = p_partner_id;
1112
1113 l_request_line_id NUMBER;
1114 l_request_line_uom VARCHAR2(30);
1115 l_reqeust_line_quantity NUMBER;
1116 l_reqeust_line_item_price NUMBER;
1117 l_request_line_apprv_type VARCHAR2(30);
1118 l_request_line_apprv_amt NUMBER;
1119 l_request_line_apprv_max_qty NUMBER;
1120 l_request_line_apprv_min_qty NUMBER;
1121
1122 CURSOR used_quantity_csr(p_offer_id NUMBER,
1123 p_product_id NUMBER) IS
1124 SELECT sum(l.scan_unit) * a.quantity quantity_remaining,
1125 a.uom_code
1126 FROM ozf_funds_utilized_all_b u,
1127 ozf_claim_lines_util_all l,
1128 ams_act_products a
1129 WHERE u.utilization_id = l.utilization_id
1130 AND u.activity_product_id = a.activity_product_id
1131 AND u.plan_type = 'OFFR'
1132 AND u.plan_id = p_offer_id
1133 AND u.product_level_type = 'PRODUCT'
1134 AND u.product_id = p_product_id
1135 group by a.quantity, a.uom_code;
1136
1137 l_used_quantity NUMBER;
1138 l_used_uom_code VARCHAR2(20);
1139
1140 l_current_quantity NUMBER;
1141
1142 CURSOR remaining_amount_csr( p_request_header_id NUMBER) is
1143 SELECT r.request_header_id,
1144 sum(fu.acctd_amount_remaining) amount_remaining
1145 FROM ozf_funds_utilized_all_b fu,
1146 ozf_request_headers_all_b r
1147 WHERE r.offer_id = fu.plan_id
1148 AND request_header_id = p_request_header_id
1149 GROUP BY r.request_header_id;
1150
1151 l_remaining_amount NUMBER;
1152 l_accepted_amount NUMBER;
1153 l_allowed_amount NUMBER;
1154 l_line_tolerance_amount NUMBER;
1155 l_tolerance_flag VARCHAR2(1);
1156
1157 l_line_tolerance_calc_cd varchar2(30);
1158 l_line_tolerance_operand number;
1159
1160 CURSOR line_tolerance_csr(p_id in number) is
1161 select LINE_TOLERANCE_OPERAND, LINE_TOLERANCE_CALC_CODE
1162 from ozf_resale_batches
1163 where resale_batch_id = p_id;
1164
1165 l_need_tolerance boolean;
1166 l_allowed_or_claimed varchar2(30);
1167 l_dispute_code varchar2(30) := null;
1168 l_status_code varchar2(30);
1169 l_tolerance NUMBER;
1170
1171 l_resale_int_rec OZF_RESALE_COMMON_PVT.g_interface_rec_csr%ROWTYPE;
1172 l_inventory_level_valid BOOLEAN;
1173
1174
1175 BEGIN
1176 -- Standard begin of API savepoint
1177 SAVEPOINT Process_SPP_ONE_Order;
1178
1179 -- Initialize API return status to sucess
1180 x_return_status := FND_API.G_RET_STS_SUCCESS;
1181
1182 -- Debug Message
1183 IF OZF_DEBUG_HIGH_ON THEN
1184 OZF_UTILITY_PVT.debug_message(p_message_text => l_full_name||': Start');
1185 END IF;
1186
1187 -- bug # 6821886 fixed by ateotia (+)
1188 -- Before start process, clean up the data structures if necessary.
1189 IF l_all_order_set_tbl.EXISTS(1) THEN
1190 l_all_order_set_tbl.DELETE;
1191 END IF;
1192
1193 OPEN all_order_set_csr(p_order_number,
1194 p_sold_from_cust_account_id,
1195 p_date_ordered,
1196 p_resale_batch_id);
1197 FETCH all_order_set_csr BULK COLLECT INTO l_all_order_set_tbl;
1198 CLOSE all_order_set_csr;
1199
1200 IF l_all_order_set_tbl.exists(1) THEN
1201 For i in 1..l_all_order_set_tbl.LAST LOOP
1202
1203 OPEN OZF_RESALE_COMMON_PVT.g_interface_rec_csr(l_all_order_set_tbl(i).resale_line_int_id);
1204 FETCH OZF_RESALE_COMMON_PVT.g_interface_rec_csr INTO l_resale_int_rec;
1205 CLOSE OZF_RESALE_COMMON_PVT.g_interface_rec_csr;
1206
1207 IF OZF_DEBUG_LOW_ON THEN
1208 ozf_utility_PVT.debug_message(l_full_name || ' checking int line ' || l_all_order_set_tbl(i).resale_line_int_id);
1209 ozf_utility_PVT.debug_message(l_full_name || ' inventory tracking ' || g_inventory_tracking);
1210 END IF;
1211
1212 IF g_inventory_tracking = 'T' THEN
1213 -- Check inventory level first
1214 OZF_SALES_TRANSACTIONS_PVT.Validate_Inventory_level (
1215 p_api_version => 1.0
1216 ,p_init_msg_list => FND_API.G_FALSE
1217 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
1218 ,p_line_int_rec => l_resale_int_rec
1219 ,x_valid => l_inventory_level_valid
1220 ,x_return_status => l_return_status
1221 ,x_msg_count => l_msg_count
1222 ,x_msg_data => l_msg_data
1223 );
1224
1225 IF NOT l_inventory_level_valid THEN
1226 IF OZF_DEBUG_LOW_ON THEN
1227 ozf_utility_PVT.debug_message(l_full_name || ' inventory checking not passed!!!');
1228 END IF;
1229
1230 OZF_RESALE_COMMON_PVT.Insert_Resale_Log (
1231 p_id_value => l_all_order_set_tbl(i).resale_line_int_id,
1232 p_id_type => OZF_RESALE_COMMON_PVT.G_ID_TYPE_IFACE,
1233 p_error_code => 'OZF_RESALE_INV_LEVEL_ERROR',
1234 p_column_name => NULL,
1235 p_column_value => NULL,
1236 x_return_status => l_return_status);
1237
1238 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1239 RAISE FND_API.g_exc_error;
1240 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1241 RAISE FND_API.g_exc_unexpected_error;
1242 END IF;
1243
1244 -- SET Batch as DISPUTED
1245 -- SLKRISHN change to common procedure
1246 UPDATE ozf_resale_lines_int_all
1247 SET status_code = 'DISPUTED',
1248 dispute_code = 'OZF_LT_INVT',
1249 followup_action_code = NVL2(followup_action_code,followup_action_code,'C'),
1250 response_type = 'CA',
1251 response_code = 'N'
1252 WHERE resale_line_int_id = l_resale_int_rec.resale_line_int_id;
1253 ELSE
1254 UPDATE ozf_resale_lines_int_all
1255 SET status_code= 'PROCESSED'
1256 WHERE resale_line_int_id = l_resale_int_rec.resale_line_int_id
1257 AND tracing_flag = 'T';
1258 END IF;
1259 ELSE
1260 -- 7570302 update stock sale line to PROCESSED if inventory validation disabled
1261 UPDATE ozf_resale_lines_int_all
1262 SET status_code= 'PROCESSED'
1263 WHERE resale_line_int_id = l_resale_int_rec.resale_line_int_id
1264 AND tracing_flag = 'T';
1265 END IF;
1266 END LOOP;
1267 END IF;
1268 -- the following piece of code has been commented in order to validate the tracing lines.
1269 -- ???? UPDATE tracing order lines to processed for this order to be processed
1270 /* UPDATE ozf_resale_lines_int_all
1271 SET status_code = 'PROCESSED'
1272 WHERE status_code = 'OPEN'
1273 AND order_number = p_order_number
1274 AND sold_from_cust_account_id = p_sold_from_cust_account_id
1275 AND date_ordered = p_date_ordered
1276 AND tracing_flag = 'T'
1277 AND resale_batch_id = p_resale_batch_id; -- bug 5222273 */
1278 -- bug # 6821886 fixed by ateotia (-)
1279
1280 IF OZF_DEBUG_LOW_ON THEN
1281 OZF_UTILITY_PVT.debug_message('partner_id:' || p_partner_id);
1282 ozf_utility_PVT.debug_message('/*--- Processing order for order NUMBER:'||p_order_number||'---*/');
1283 ozf_utility_PVT.debug_message('/*--- And cusomter:'||p_sold_from_cust_account_id||'---*/');
1284 ozf_utility_PVT.debug_message('/*--- And date ordered:'||p_date_ordered||'---*/');
1285 END IF;
1286
1287 -- Before start process, clean up the data structures if necessary.
1288 IF l_order_set_tbl.EXISTS(1) THEN
1289 l_order_set_tbl.DELETE;
1290 END IF;
1291 --k:=1;
1292 OPEN order_set_csr(p_order_number,
1293 p_sold_from_cust_account_id,
1294 p_date_ordered,
1295 p_resale_batch_id);
1296 FETCH order_set_csr BULK COLLECT INTO l_order_set_tbl;
1297 --LOOP
1298 -- BUG 4491985 (+)
1299 --EXIT when order_set_csr%notfound;
1300 --FETCH order_set_csr INTO l_order_set_tbl(k);
1301 -- FETCH order_set_csr INTO l_order_set_tbl(k);
1302 -- EXIT when order_set_csr%notfound;
1303 -- BUG 4491985 (-)
1304 -- k:=k+1;
1305 --END LOOP;
1306 CLOSE order_set_csr;
1307
1308 IF OZF_DEBUG_LOW_ON THEN
1309 ozf_utility_PVT.debug_message('after order set:'||l_order_set_tbl.LAST);
1310 END IF;
1311
1312 IF l_order_set_tbl.exists(1) THEN
1313 For J in 1..l_order_set_tbl.LAST LOOP
1314
1315 -- Bug#9926288(+)
1316 l_order_set_tbl(J).agreement_name := NVL(l_order_set_tbl(J).corrected_agreement_name, l_order_set_tbl(J).agreement_name);
1317 -- Bug#9926288(-)
1318
1319 -- bug # 6821886 fixed by ateotia (+)
1320 /*-- Bug 4616588 (+)
1321 OPEN OZF_RESALE_COMMON_PVT.g_interface_rec_csr(l_order_set_tbl(J).resale_line_int_id);
1322 FETCH OZF_RESALE_COMMON_PVT.g_interface_rec_csr INTO l_resale_int_rec;
1323 CLOSE OZF_RESALE_COMMON_PVT.g_interface_rec_csr;
1324
1325 IF g_inventory_tracking = 'T' THEN
1326 -- Check inventory level first
1327 OZF_SALES_TRANSACTIONS_PVT.Validate_Inventory_level (
1328 p_api_version => 1.0
1329 ,p_init_msg_list => FND_API.G_FALSE
1330 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
1331 ,p_line_int_rec => l_resale_int_rec
1332 ,x_valid => l_inventory_level_valid
1333 ,x_return_status => l_return_status
1334 ,x_msg_count => l_msg_count
1335 ,x_msg_data => l_msg_data
1336 );
1337
1338 IF NOT l_inventory_level_valid THEN
1339 OZF_RESALE_COMMON_PVT.Insert_Resale_Log (
1340 p_id_value => l_order_set_tbl(J).resale_line_int_id,
1341 p_id_type => OZF_RESALE_COMMON_PVT.G_ID_TYPE_IFACE,
1342 p_error_code => 'OZF_RESALE_INV_LEVEL_ERROR',
1343 p_column_name => NULL,
1344 p_column_value => NULL,
1345 x_return_status => l_return_status);
1346 --
1347 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1348 RAISE FND_API.g_exc_error;
1349 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1350 RAISE FND_API.g_exc_unexpected_error;
1351 END IF;
1352 --
1353 -- SET Batch as DISPUTED
1354 -- SLKRISHN change to common procedure
1355 UPDATE ozf_resale_lines_int_all
1356 SET status_code = 'DISPUTED',
1357 dispute_code = 'OZF_LT_INVT',
1358 followup_action_code = 'C',
1359 response_type = 'CA',
1360 response_code = 'N'
1361 WHERE resale_line_int_id = l_resale_int_rec.resale_line_int_id;
1362 --
1363 GOTO END_LOOP;
1364 END IF;
1365 END IF;
1366 -- Bug 4616588 (-)*/
1367 -- bug # 6821886 fixed by ateotia (-)
1368
1369 -- request header level validation
1370 OPEN request_header_info_csr(l_order_set_tbl(J).agreement_name,
1371 p_partner_id);
1372 FETCH request_header_info_csr INTO l_request_header_id,
1373 l_header_start_date,
1374 l_header_end_date,
1375 l_header_currency_code,
1376 l_request_type_code,
1377 l_header_end_cust_party_id,
1378 l_header_reseller_party_id,
1379 l_offer_id,
1380 l_offer_type,
1381 l_ship_from_stock_flag; --POS Batch Processing by profiles by ateotia
1382 CLOSE request_header_info_csr;
1383
1384 IF OZF_DEBUG_LOW_ON THEN
1385 OZF_UTILITY_PVT.debug_message(p_message_text => 'start_date' || l_header_start_date);
1386 OZF_UTILITY_PVT.debug_message(p_message_text => 'end_date' || l_header_end_date);
1387 OZF_UTILITY_PVT.debug_message(p_message_text => 'request_type ' || l_request_type_code);
1388 OZF_UTILITY_PVT.debug_message(p_message_text => 'currency_code' || l_header_currency_code);
1389 END IF;
1390 IF l_header_start_date IS NULL OR
1391 l_request_type_code IS NULL OR
1392 l_header_currency_code is NULL THEN
1393 OZF_RESALE_COMMON_PVT.Insert_Resale_Log (
1394 p_id_value => l_order_set_tbl(J).resale_line_int_id,
1395 p_id_type => OZF_RESALE_COMMON_PVT.G_ID_TYPE_IFACE,
1396 p_error_code => 'OZF_RESALE_AGRM_WNG',
1397 p_column_name => NULL,
1398 p_column_value => NULL,
1399 x_return_status => l_return_status
1400 );
1401 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1402 RAISE FND_API.g_exc_error;
1403 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1404 RAISE FND_API.g_exc_unexpected_error;
1405 END IF;
1406
1407 BEGIN
1408 UPDATE ozf_resale_lines_int
1409 SET status_code=OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DISPUTED,
1410 dispute_code = 'OZF_RESALE_AGRM_WNG',
1411 followup_action_code = NVL2(followup_action_code,followup_action_code,'C'),
1412 response_type = 'CA',
1413 response_code = 'N'
1414 WHERE resale_line_int_id = l_order_set_tbl(J).resale_line_int_id
1415 AND resale_batch_id = p_resale_batch_id;
1416 EXCEPTION
1417 WHEN OTHERS THEN
1418 OZF_UTILITY_PVT.error_message(p_message_name => 'OZF_UPD_RESALE_INT_WRG');
1419 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1420 END;
1421 goto END_LOOP;
1422 END IF;
1423
1424 IF OZF_DEBUG_LOW_ON THEN
1425 OZF_UTILITY_PVT.debug_message(p_message_text => 'date_ordered' || l_order_set_tbl(J).date_ordered);
1426 END IF;
1427
1428
1429 IF l_order_set_tbl(J).date_ordered < l_header_start_date OR
1430 (l_header_end_date is not null AND
1431 l_order_set_tbl(J).date_ordered > l_header_end_date) THEN
1432 OZF_RESALE_COMMON_PVT.Insert_Resale_Log (
1433 p_id_value => l_order_set_tbl(J).resale_line_int_id,
1434 p_id_type => OZF_RESALE_COMMON_PVT.G_ID_TYPE_IFACE,
1435 p_error_code => 'OZF_RESALE_AGRM_RANG_WNG',
1436 p_column_name => NULL,
1437 p_column_value => NULL,
1438 x_return_status => l_return_status
1439 );
1440 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1441 RAISE FND_API.g_exc_error;
1442 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1443 RAISE FND_API.g_exc_unexpected_error;
1444 END IF;
1445
1446 BEGIN
1447 UPDATE ozf_resale_lines_int
1448 SET status_code=OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DISPUTED,
1449 dispute_code = 'OZF_RESALE_AGRM_RANG_WNG',
1450 followup_action_code = NVL2(followup_action_code,followup_action_code,'C'),
1451 response_type = 'CA',
1452 response_code = 'N'
1453 WHERE resale_line_int_id = l_order_set_tbl(J).resale_line_int_id
1454 AND resale_batch_id = p_resale_batch_id;
1455 EXCEPTION
1456 WHEN OTHERS THEN
1457 OZF_UTILITY_PVT.error_message(p_message_name => 'OZF_UPD_RESALE_INT_WRG');
1458 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1459 END;
1460 GOTO END_LOOP;
1461 END IF;
1462
1463 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1464 OZF_UTILITY_PVT.debug_message('line currency '||l_order_set_tbl(J).currency_code );
1465 END IF;
1466 IF l_order_set_tbl(J).currency_code <> l_header_currency_code THEN
1467 BEGIN
1468 UPDATE ozf_resale_lines_int
1469 SET status_code=OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DISPUTED,
1470 dispute_code = 'OZF_RESALE_AGRM_CURRENCY_WNG',
1471 followup_action_code = NVL2(followup_action_code,followup_action_code,'C'),
1472 response_type = 'CA',
1473 response_code = 'N'
1474 WHERE resale_line_int_id = l_order_set_tbl(J).resale_line_int_id
1475 AND resale_batch_id = p_resale_batch_id;
1476 EXCEPTION
1477 WHEN OTHERS THEN
1478 OZF_UTILITY_PVT.error_message(p_message_name => 'OZF_UPD_RESALE_INT_WRG');
1479 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1480 END;
1481 GOTO END_LOOP;
1482 END IF;
1483
1484 -- check customer information based on request_type_code
1485 IF l_request_type_code = G_BLANKET_REQ THEN
1486 -- no need to do check;
1487 NULL;
1488 ELSIF l_request_type_code = G_BID_REQ THEN
1489 -- When the request is bid request, end customer info is required. Reseller info is not required.
1490 -- Validation on resale data is passed, only when bill_to/ ship_to is not null and equal to the
1491 -- end customer.
1492
1493 IF OZF_DEBUG_LOW_ON THEN
1494 OZF_UTILITY_PVT.debug_message('In Bid request' );
1495 END IF;
1496 IF l_header_end_cust_party_id IS NOT NULL THEN
1497 -- One of the following should match
1498 IF (l_order_set_tbl(J).bill_to_party_id is not null AND
1499 l_order_set_tbl(J).bill_to_party_id = l_header_end_cust_party_id) OR
1500 (l_order_set_tbl(J).ship_to_party_id is not null AND
1501 l_order_set_tbl(J).ship_to_party_id = l_header_end_cust_party_id) THEN
1502
1503 -- Do nothing
1504 NULL;
1505 ELSE
1506 BEGIN
1507 UPDATE ozf_resale_lines_int
1508 SET status_code=OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DISPUTED,
1509 dispute_code = 'OZF_RESALE_AGRM_END_CUST_WNG',
1510 followup_action_code = NVL2(followup_action_code,followup_action_code,'C'),
1511 response_type = 'CA',
1512 response_code = 'N'
1513 WHERE resale_line_int_id = l_order_set_tbl(J).resale_line_int_id
1514 AND resale_batch_id = p_resale_batch_id;
1515 EXCEPTION
1516 WHEN OTHERS THEN
1517 OZF_UTILITY_PVT.error_message(p_message_name => 'OZF_UPD_RESALE_INT_WRG');
1518 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1519 END;
1520 GOTO END_LOOP;
1521 END IF;
1522 END IF;
1523 ELSIF l_request_type_code = G_MEET_REQ THEN
1524
1525 -- When the request is meet competitor, neither end customer info nor reseller info is required.
1526 -- Validation on resale data is passed, only as follows:
1527 -- IF reseller is not null then
1528 -- bill_to/ ship_to is not null and equal to the reseller,
1529 -- If end customer is not null then
1530 -- end customer is not null and equal to the end cusomter or
1531 -- end customer is null
1532 -- end if;
1533 -- ELSE
1534 -- If end customer is not null THEN
1535 -- bill_to/ ship_to is not null and equal to the end customer
1536 -- end if;
1537 -- end if;
1538 IF OZF_DEBUG_LOW_ON THEN
1539 OZF_UTILITY_PVT.debug_message('In Meet' );
1540 END IF;
1541 -- One of the following should match
1542 IF l_header_reseller_party_id IS NOT NULL THEN
1543 IF (l_order_set_tbl(J).bill_to_party_id is NOT NULL AND
1544 l_order_set_tbl(J).bill_to_party_id = l_header_reseller_party_id) OR
1545 (l_order_set_tbl(J).ship_to_party_id is NOT NULL AND
1546 l_order_set_tbl(J).ship_to_party_id = l_header_reseller_party_id) THEN
1547
1548 IF l_header_end_cust_party_id IS NOT NULL THEN
1549 IF (l_order_set_tbl(J).END_cust_party_id is not NULL AND
1550 l_order_set_tbl(J).END_cust_party_id = l_header_end_cust_party_id) OR
1551 (l_order_set_tbl(J).END_cust_party_id is NULL) THEN
1552 NULL;
1553 ELSE
1554 BEGIN
1555 UPDATE ozf_resale_lines_int
1556 SET status_code=OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DISPUTED,
1557 dispute_code = 'OZF_RESALE_AGRM_RESELL_WNG',
1558 followup_action_code = NVL2(followup_action_code,followup_action_code,'C'),
1559 response_type = 'CA',
1560 response_code = 'N'
1561 WHERE resale_line_int_id = l_order_set_tbl(J).resale_line_int_id
1562 AND resale_batch_id = p_resale_batch_id;
1563 EXCEPTION
1564 WHEN OTHERS THEN
1565 OZF_UTILITY_PVT.error_message(p_message_name => 'OZF_UPD_RESALE_INT_WRG');
1566 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1567 END;
1568 GOTO END_LOOP;
1569 END IF;
1570 END IF;
1571 ELSE
1572 BEGIN
1573 UPDATE ozf_resale_lines_int
1574 SET status_code=OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DISPUTED,
1575 dispute_code = 'OZF_RESALE_AGRM_RESELL_WNG',
1576 followup_action_code = NVL2(followup_action_code,followup_action_code,'C'),
1577 response_type = 'CA',
1578 response_code = 'N'
1579 WHERE resale_line_int_id = l_order_set_tbl(J).resale_line_int_id
1580 AND resale_batch_id = p_resale_batch_id;
1581 EXCEPTION
1582 WHEN OTHERS THEN
1583 OZF_UTILITY_PVT.error_message(p_message_name => 'OZF_UPD_RESALE_INT_WRG');
1584 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1585 END;
1586 GOTO END_LOOP;
1587 END IF;
1588 ELSE
1589 IF l_header_end_cust_party_id IS NOT NULL THEN
1590 IF (l_order_set_tbl(J).bill_to_party_id is not NULL AND
1591 l_order_set_tbl(J).bill_to_party_id = l_header_end_cust_party_id) OR
1592 (l_order_set_tbl(J).ship_to_party_id is not NULL AND
1593 l_order_set_tbl(J).ship_to_party_id = l_header_end_cust_party_id) THEN
1594
1595 NULL;
1596 ELSE
1597 BEGIN
1598 UPDATE ozf_resale_lines_int
1599 SET status_code=OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DISPUTED,
1600 dispute_code = 'OZF_RESALE_AGRM_RESELL_WNG',
1601 followup_action_code = NVL2(followup_action_code,followup_action_code,'C'),
1602 response_type = 'CA',
1603 response_code = 'N'
1604 WHERE resale_line_int_id = l_order_set_tbl(J).resale_line_int_id
1605 AND resale_batch_id = p_resale_batch_id;
1606 EXCEPTION
1607 WHEN OTHERS THEN
1608 OZF_UTILITY_PVT.error_message(p_message_name => 'OZF_UPD_RESALE_INT_WRG');
1609 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1610 END;
1611 GOTO END_LOOP;
1612 END IF;
1613 END IF;
1614 END IF;
1615 END IF;
1616
1617 IF l_offer_id IS NULL THEN
1618
1619 IF OZF_DEBUG_LOW_ON THEN
1620 OZF_UTILITY_PVT.debug_message('In Offer id null' );
1621 END IF;
1622 BEGIN
1623 UPDATE ozf_resale_lines_int
1624 SET status_code=OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DISPUTED,
1625 dispute_code = 'OZF_RESALE_AGRM_OFF_NULL',
1626 followup_action_code = NVL2(followup_action_code,followup_action_code,'C'),
1627 response_type = 'CA',
1628 response_code = 'N'
1629 WHERE resale_line_int_id = l_order_set_tbl(J).resale_line_int_id
1630 AND resale_batch_id = p_resale_batch_id;
1631 EXCEPTION
1632 WHEN OTHERS THEN
1633 OZF_UTILITY_PVT.error_message(p_message_name => 'OZF_UPD_RESALE_INT_WRG');
1634 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1635 END;
1636 GOTO END_LOOP;
1637 ELSE
1638 IF l_offer_type = G_OFF_INVOICE_OFFER THEN
1639 IF OZF_DEBUG_LOW_ON THEN
1640 OZF_UTILITY_PVT.debug_message('In Off invoice offer' );
1641 END IF;
1642 BEGIN
1643 UPDATE ozf_resale_lines_int
1644 SET status_code=OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DISPUTED,
1645 dispute_code = 'OZF_RESALE_AGRM_OFF_OFF_INV',
1646 followup_action_code = NVL2(followup_action_code,followup_action_code,'C'),
1647 response_type = 'CA',
1648 response_code = 'N'
1649 WHERE resale_line_int_id = l_order_set_tbl(J).resale_line_int_id
1650 AND resale_batch_id = p_resale_batch_id;
1651 EXCEPTION
1652 WHEN OTHERS THEN
1653 OZF_UTILITY_PVT.error_message(p_message_name => 'OZF_UPD_RESALE_INT_WRG');
1654 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1655 END;
1656 GOTO END_LOOP;
1657 END IF;
1658 END IF;
1659
1660 --Change for the bug#9913173 (+)
1661 /*IF l_order_set_tbl(J).agreement_name = l_order_set_tbl(J).corrected_agreement_name THEN
1662 l_agreement_name := l_order_set_tbl(J).agreement_name;
1663 ELSE
1664 l_agreement_name := l_order_set_tbl(J).corrected_agreement_name;
1665 END IF; */
1666 -- This bug#9913173 is also fixed as a part of the Bug#9926288.
1667 --Change for the bug#9913173 (-)
1668 -- request line level validation
1669 OPEN request_line_info_csr(l_order_set_tbl(J).inventory_item_id,
1670 l_order_set_tbl(J).agreement_name,
1671 p_partner_id);
1672 --EXIT when request_line_info_csr%NOTFOUND;
1673 FETCH request_line_info_csr INTO l_request_line_id,
1674 l_request_line_uom,
1675 l_reqeust_line_quantity,
1676 l_reqeust_line_item_price,
1677 l_request_line_apprv_type,
1678 l_request_line_apprv_amt,
1679 l_request_line_apprv_max_qty,
1680 l_request_line_apprv_min_qty;
1681 CLOSE request_line_info_csr;
1682
1683 IF OZF_DEBUG_LOW_ON THEN
1684 OZF_UTILITY_PVT.debug_message('request line_id:' || l_request_line_id );
1685 OZF_UTILITY_PVT.debug_message('request line uom:' || l_request_line_uom );
1686 OZF_UTILITY_PVT.debug_message('request line approved max:' || l_request_line_apprv_max_qty );
1687 OZF_UTILITY_PVT.debug_message('request line approved min' || l_request_line_apprv_min_qty );
1688 END IF;
1689 IF l_request_line_id is NULL THEN
1690 OZF_RESALE_COMMON_PVT.Insert_Resale_Log (
1691 p_id_value => l_order_set_tbl(J).resale_line_int_id,
1692 p_id_type => OZF_RESALE_COMMON_PVT.G_ID_TYPE_IFACE,
1693 p_error_code => 'OZF_RESALE_AGRM_PROD_WNG',
1694 p_column_name => 'ITEM_NUMBER',
1695 p_column_value => l_order_set_tbl(J).item_number,
1696 x_return_status => l_return_status
1697 );
1698 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1699 RAISE FND_API.g_exc_error;
1700 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1701 RAISE FND_API.g_exc_unexpected_error;
1702 END IF;
1703
1704 BEGIN
1705 UPDATE ozf_resale_lines_int
1706 SET status_code=OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DISPUTED,
1707 dispute_code = 'OZF_RESALE_AGRM_LINE_WNG',
1708 followup_action_code = NVL2(followup_action_code,followup_action_code,'C'),
1709 response_type = 'CA',
1710 response_code = 'N'
1711 WHERE resale_line_int_id = l_order_set_tbl(J).resale_line_int_id
1712 AND resale_batch_id = p_resale_batch_id;
1713 EXCEPTION
1714 WHEN OTHERS THEN
1715 OZF_UTILITY_PVT.error_message(p_message_name => 'OZF_UPD_RESALE_INT_WRG');
1716 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1717 END;
1718 goto END_LOOP;
1719 END IF;
1720
1721
1722 /*
1723 -- get current quantity and amount FROM tmp table
1724 OZF_RESALE_COMMON_PVT.get_available_quantity(
1725 p_api_version_number => 1.0,
1726 p_init_msg_list => FND_API.G_FALSE,
1727 p_commit => FND_API.G_FALSE,
1728 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1729 p_line_id => l_request_line_id,
1730 p_primary_uom_code => l_primary_uom_code,
1731 x_available_quantity=> l_available_quan,
1732 x_available_amount => l_available_amt,
1733 x_return_status => l_return_status,
1734 x_msg_count => l_msg_count,
1735 x_msg_data => l_msg_data
1736 );
1737
1738 */
1739
1740 l_current_quantity := l_order_set_tbl(J).quantity;
1741 IF OZF_DEBUG_LOW_ON THEN
1742 OZF_UTILITY_PVT.debug_message('init current quantity:' || l_current_quantity );
1743 END IF;
1744
1745 IF l_request_line_apprv_max_qty is not NULL OR
1746 l_request_line_apprv_min_qty is not NULL THEN
1747
1748 -- only to checking quantity if necessary
1749 --POS Batch Processing by profiles by ateotia (+)
1750 l_used_quantity := NULL;
1751 l_used_uom_code := NULL;
1752 IF (l_ship_from_stock_flag = 'Y' AND l_offer_type = 'ACCRUAL') THEN
1753 OPEN accrued_quantity_csr(l_request_header_id, l_order_set_tbl(J).inventory_item_id);
1754 FETCH accrued_quantity_csr INTO l_used_quantity, l_used_uom_code;
1755 CLOSE accrued_quantity_csr;
1756 ELSE
1757 OPEN used_quantity_csr(l_offer_id, l_order_set_tbl(J).inventory_item_id);
1758 FETCH used_quantity_csr INTO l_used_quantity, l_used_uom_code;
1759 CLOSE used_quantity_csr;
1760 END IF;
1761 /*
1762 OPEN used_quantity_csr(l_offer_id, l_order_set_tbl(J).inventory_item_id);
1763 FETCH used_quantity_csr INTO l_used_quantity,
1764 l_used_uom_code;
1765 CLOSE used_quantity_csr;*/
1766
1767 --POS Batch Processing by profiles by ateotia (-)
1768
1769 IF l_used_quantity IS NULL THEN
1770 l_used_quantity := 0;
1771 END IF;
1772
1773 IF l_used_uom_code IS NULL THEN
1774 l_used_uom_code := l_request_line_uom;
1775 END IF;
1776
1777 IF l_request_line_uom <> l_used_uom_code THEN
1778 -- conver the requeste line quantity
1779 IF l_request_line_apprv_max_qty is not NULL THEN
1780 l_request_line_apprv_max_qty := inv_convert.inv_um_convert(
1781 l_order_set_tbl(J).inventory_item_id,
1782 NULL,
1783 l_request_line_apprv_max_qty ,
1784 l_request_line_uom,
1785 l_used_uom_code,
1786 NULL, NULL);
1787 IF l_request_line_apprv_max_qty = -99999 THEN
1788 BEGIN
1789 UPDATE ozf_resale_lines_int
1790 SET status_code=OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DISPUTED,
1791 dispute_code = 'OZF_SPP_NO_UOM_CONV_MAX',
1792 followup_action_code = NVL2(followup_action_code,followup_action_code,'C'),
1793 response_type = 'CA',
1794 response_code = 'N'
1795 WHERE resale_line_int_id = l_order_set_tbl(J).resale_line_int_id
1796 AND resale_batch_id = p_resale_batch_id;
1797 EXCEPTION
1798 WHEN OTHERS THEN
1799 OZF_UTILITY_PVT.error_message(p_message_name => 'OZF_UPD_RESALE_INT_WRG');
1800 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1801 END;
1802 goto END_LOOP;
1803 END IF;
1804 END IF;
1805
1806 IF l_request_line_apprv_min_qty is not NULL THEN
1807 l_request_line_apprv_min_qty := inv_convert.inv_um_convert(
1808 l_order_set_tbl(J).inventory_item_id,
1809 NULL,
1810 l_request_line_apprv_min_qty ,
1811 l_request_line_uom,
1812 l_used_uom_code,
1813 NULL, NULL);
1814 IF l_request_line_apprv_min_qty = -99999 THEN
1815 BEGIN
1816 UPDATE ozf_resale_lines_int
1817 SET status_code=OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DISPUTED,
1818 dispute_code = 'OZF_SPP_NO_UOM_CONV_MIN',
1819 followup_action_code = NVL2(followup_action_code,followup_action_code,'C'),
1820 response_type = 'CA',
1821 response_code = 'N'
1822 WHERE resale_line_int_id = l_order_set_tbl(J).resale_line_int_id
1823 AND resale_batch_id = p_resale_batch_id;
1824 EXCEPTION
1825 WHEN OTHERS THEN
1826 OZF_UTILITY_PVT.error_message(p_message_name => 'OZF_UPD_RESALE_INT_WRG');
1827 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1828 END;
1829 goto END_LOOP;
1830 END IF;
1831 END IF;
1832 END IF;
1833
1834 IF l_order_set_tbl(J).uom_code <> l_used_uom_code THEN
1835 -- conver the quantity for the current order
1836 IF l_current_quantity is not NULL THEN
1837 l_current_quantity := inv_convert.inv_um_convert(
1838 l_order_set_tbl(J).inventory_item_id,
1839 NULL,
1840 l_current_quantity,
1841 l_order_set_tbl(J).uom_code,
1842 l_used_uom_code,
1843 NULL, NULL);
1844 IF l_current_quantity = -99999 THEN
1845 BEGIN
1846 UPDATE ozf_resale_lines_int
1847 SET status_code=OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DISPUTED,
1848 dispute_code = 'OZF_SPP_NO_UOM_CONV_CURR',
1849 followup_action_code = NVL2(followup_action_code,followup_action_code,'C'),
1850 response_type = 'CA',
1851 response_code = 'N'
1852 WHERE resale_line_int_id = l_order_set_tbl(J).resale_line_int_id
1853 AND resale_batch_id = p_resale_batch_id;
1854 EXCEPTION
1855 WHEN OTHERS THEN
1856 OZF_UTILITY_PVT.error_message(p_message_name => 'OZF_UPD_RESALE_INT_WRG');
1857 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1858 END;
1859 goto END_LOOP;
1860 END IF;
1861 END IF;
1862 END IF;
1863 IF OZF_DEBUG_LOW_ON THEN
1864 OZF_UTILITY_PVT.debug_message('2 current quantity:' || l_current_quantity );
1865 END IF;
1866
1867 IF l_request_line_apprv_max_qty is not NULL AND
1868 l_current_quantity + l_used_quantity > l_request_line_apprv_max_qty THEN
1869 BEGIN
1870 UPDATE ozf_resale_lines_int
1871 SET status_code=OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DISPUTED,
1872 dispute_code = 'OZF_RESALE_AGRM_QUN_GT_MAX',
1873 followup_action_code = NVL2(followup_action_code,followup_action_code,'C'),
1874 response_type = 'CA',
1875 response_code = 'N'
1876 WHERE resale_line_int_id = l_order_set_tbl(J).resale_line_int_id
1877 AND resale_batch_id = p_resale_batch_id;
1878 EXCEPTION
1879 WHEN OTHERS THEN
1880 OZF_UTILITY_PVT.error_message(p_message_name => 'OZF_UPD_RESALE_INT_WRG');
1881 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1882 END;
1883 goto END_LOOP;
1884 END IF;
1885
1886 IF l_request_line_apprv_min_qty is not NULL AND
1887 l_current_quantity + l_used_quantity < l_request_line_apprv_min_qty THEN
1888 BEGIN
1889 UPDATE ozf_resale_lines_int
1890 SET status_code=OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DISPUTED,
1891 dispute_code = 'OZF_RESALE_AGRM_QUN_LT_MIN',
1892 followup_action_code = NVL2(followup_action_code,followup_action_code,'C'),
1893 response_type = 'CA',
1894 response_code = 'N'
1895 WHERE resale_line_int_id = l_order_set_tbl(J).resale_line_int_id
1896 AND resale_batch_id = p_resale_batch_id;
1897 EXCEPTION
1898 WHEN OTHERS THEN
1899 OZF_UTILITY_PVT.error_message(p_message_name => 'OZF_UPD_RESALE_INT_WRG');
1900 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1901 END;
1902 goto END_LOOP;
1903 END IF;
1904
1905 -- UPDATE line and temp table with information FROM this line.
1906 l_used_quantity := l_used_quantity + l_current_quantity;
1907
1908 -- Need to continue here.
1909
1910 END IF;
1911
1912 --2. get claimed amount, claimed_amount + claiming_amount < total_amount ???
1913 OPEN remaining_amount_csr(l_request_header_id);
1914 FETCH remaining_amount_csr into l_request_header_id,
1915 l_remaining_amount;
1916 CLOSE remaining_amount_csr;
1917
1918 IF OZF_DEBUG_LOW_ON THEN
1919 OZF_UTILITY_PVT.debug_message('remaining amount:' || l_remaining_amount );
1920 END IF;
1921
1922
1923 -- allowed amount should be based on the request.
1924 -- Update the results of Special Pricing Calculation
1925 OZF_RESALE_COMMON_PVT.Update_Line_Calculations(
1926 p_resale_line_int_rec => l_order_set_tbl(J),
1927 p_unit_price => l_reqeust_line_item_price,
1928 p_line_quantity => l_current_quantity,
1929 p_allowed_amount => l_request_line_apprv_amt,
1930 x_return_status => l_return_status);
1931 --
1932 IF l_return_status = FND_API.g_ret_sts_error THEN
1933 RAISE FND_API.g_exc_error;
1934 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1935 RAISE FND_API.g_exc_unexpected_error;
1936 END IF;
1937 <<END_LOOP>>
1938 NULL;
1939 END LOOP;
1940 END IF;
1941 IF OZF_DEBUG_LOW_ON THEN
1942 ozf_utility_PVT.debug_message('/*--- Success: Processing order for order NUMBER:'||p_order_number||'---*/');
1943 ozf_utility_PVT.debug_message('/*--- AND cusomter:'||p_sold_from_cust_account_id||'---*/');
1944 ozf_utility_PVT.debug_message('/*--- And date ordered:'||p_date_ordered||'---*/');
1945 END IF;
1946
1947 -- Debug Message
1948 IF OZF_DEBUG_HIGH_ON THEN
1949 OZF_UTILITY_PVT.debug_message(p_message_text => l_full_name||': End');
1950 END IF;
1951
1952 x_return_status := l_return_status;
1953 EXCEPTION
1954 WHEN FND_API.G_EXC_ERROR THEN
1955 ROLLBACK TO Process_SPP_ONE_Order;
1956 x_return_status := FND_API.G_RET_STS_ERROR;
1957 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1958 ROLLBACK TO Process_SPP_ONE_Order;
1959 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1960 WHEN OTHERS THEN
1961 ROLLBACK TO Process_SPP_ONE_Order;
1962 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1963 END Process_One_Order;
1964
1965 ---------------------------------------------------------------------
1966 -- PROCEDURE
1967 -- Process_Order
1968 --
1969 -- PURPOSE
1970 --
1971 --
1972 -- PARAMETERS
1973 --
1974 -- NOTES
1975 --
1976 ---------------------------------------------------------------------
1977 PROCEDURE Process_Order(
1978 p_api_version IN NUMBER
1979 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
1980 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
1981 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
1982 ,p_resale_batch_id IN NUMBER
1983 ,x_return_status OUT NOCOPY VARCHAR2
1984 ,x_msg_data OUT NOCOPY VARCHAR2
1985 ,x_msg_count OUT NOCOPY NUMBER
1986 )IS
1987 l_api_name CONSTANT VARCHAR2(30) := 'Process_Order';
1988 l_api_version CONSTANT NUMBER := 1.0;
1989 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
1990 --
1991 l_return_status VARCHAR2(30);
1992 l_msg_data VARCHAR2(2000);
1993 l_msg_count NUMBER;
1994
1995 CURSOR partner_id_csr (p_resale_batch_id NUMBER)IS
1996 SELECT partner_id
1997 , report_start_date
1998 , report_end_date
1999 FROM ozf_resale_batches
2000 where resale_batch_id = p_resale_batch_id;
2001 l_partner_id NUMBER;
2002
2003 CURSOR order_num_csr IS
2004 SELECT DISTINCT order_number,
2005 sold_from_cust_account_id,
2006 date_ordered
2007 FROM ozf_resale_lines_int
2008 WHERE status_code = 'OPEN'
2009 --status_code in(OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_OPEN, OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DUPLICATED)
2010 AND duplicated_adjustment_id IS NULL
2011 AND resale_batch_id = p_resale_batch_id
2012 ORDER BY date_ordered;
2013
2014 l_cust_account_id_tbl OZF_RESALE_COMMON_PVT.number_tbl_type;
2015 l_order_num_tbl OZF_RESALE_COMMON_PVT.varchar_tbl_type;
2016 l_order_date_tbl OZF_RESALE_COMMON_PVT.date_tbl_type;
2017
2018 l_report_start_date DATE;
2019 l_report_end_date DATE;
2020
2021 BEGIN
2022 -- Standard begin of API savepoint
2023 SAVEPOINT Process_SPP_Order;
2024 -- Standard call to check for call compatibility.
2025 IF NOT FND_API.Compatible_API_Call (
2026 l_api_version,
2027 p_api_version,
2028 l_api_name,
2029 G_PKG_NAME)
2030 THEN
2031 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2032 END IF;
2033
2034 --Initialize message list if p_init_msg_list is TRUE.
2035 IF FND_API.To_Boolean (p_init_msg_list) THEN
2036 FND_MSG_PUB.initialize;
2037 END IF;
2038
2039 -- Debug Message
2040 IF OZF_DEBUG_HIGH_ON THEN
2041 OZF_UTILITY_PVT.debug_message(p_message_text => l_full_name||': Start');
2042 END IF;
2043
2044 -- Initialize API return status to sucess
2045 x_return_status := FND_API.G_RET_STS_SUCCESS;
2046
2047 -- Delete the logs for the current batch
2048 OZF_RESALE_COMMON_PVT.Delete_Log(
2049 p_api_version => 1.0
2050 ,p_init_msg_list => FND_API.G_FALSE
2051 ,p_commit => FND_API.G_FALSE
2052 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
2053 ,p_resale_batch_id => p_resale_batch_id
2054 ,x_return_status => l_return_status
2055 ,x_msg_count => l_msg_count
2056 ,x_msg_data => l_msg_data
2057 );
2058 IF l_return_status = FND_API.g_ret_sts_error THEN
2059 RAISE FND_API.g_exc_error;
2060 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2061 RAISE FND_API.g_exc_unexpected_error;
2062 END IF;
2063
2064
2065 OPEN partner_id_csr(p_resale_batch_id);
2066 FETCH partner_id_csr INTO l_partner_id
2067 , l_report_start_date
2068 , l_report_end_date;
2069 CLOSE partner_id_csr;
2070
2071
2072 -- Bug 4616588 (+)
2073 -- Check whether there is a need to do inventory_verification
2074 OPEN OZF_RESALE_COMMON_PVT.g_inventory_tracking_csr;
2075 FETCH OZF_RESALE_COMMON_PVT.g_inventory_tracking_csr INTO g_inventory_tracking;
2076 CLOSE OZF_RESALE_COMMON_PVT.g_inventory_tracking_csr;
2077
2078 -- populates the temp tables
2079 IF g_inventory_tracking = 'T' THEN
2080 OZF_SALES_TRANSACTIONS_PVT.Initiate_Inventory_tmp (
2081 p_api_version => 1.0
2082 ,p_init_msg_list => FND_API.G_FALSE
2083 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
2084 ,p_resale_batch_id => p_resale_batch_id
2085 ,p_start_date => l_report_start_date
2086 ,p_end_date => l_report_end_date
2087 ,x_return_status => l_return_status
2088 ,x_msg_count => l_msg_count
2089 ,x_msg_data => l_msg_data
2090 );
2091 IF l_return_status = FND_API.g_ret_sts_error THEN
2092 RAISE FND_API.g_exc_error;
2093 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2094 RAISE FND_API.g_exc_unexpected_error;
2095 END IF;
2096 END IF;
2097 -- Bug 4616588 (-)
2098
2099
2100 -- fetch all orders into a cursor.
2101 OPEN order_num_csr;
2102 FETCH order_num_csr BULK COLLECT INTO l_order_num_tbl,l_cust_account_id_tbl, l_order_date_tbl;
2103 CLOSE order_num_csr;
2104
2105
2106 IF l_order_num_tbl.EXISTS(1) THEN
2107
2108 For i in 1..l_order_num_tbl.LAST
2109 LOOP
2110 IF l_order_num_tbl(i) is not NULL AND
2111 l_cust_account_id_tbl(i) is not NULL AND
2112 l_order_date_tbl(i) is not NULL THEN
2113
2114 process_one_order(p_order_number => l_order_num_tbl(i),
2115 p_sold_from_cust_account_id => l_cust_account_id_tbl(i),
2116 p_date_ordered => l_order_date_tbl(i),
2117 p_resale_batch_id => p_resale_batch_id,
2118 p_partner_id => l_partner_id,
2119 x_return_status => l_return_status);
2120
2121 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2122 BEGIN
2123 update ozf_resale_lines_int
2124 set status_code =OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DISPUTED,
2125 dispute_code = 'OZF_PROC_PRIC_RESLT_ERR',
2126 followup_action_code = NVL2(followup_action_code,followup_action_code,'C'),
2127 response_type = 'CA',
2128 response_code = 'N'
2129 where status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_OPEN
2130 and order_number = l_order_num_tbl(i)
2131 and sold_from_cust_account_id =l_cust_account_id_tbl(i)
2132 and date_ordered = l_order_date_tbl(i)
2133 and resale_batch_id = p_resale_batch_id;
2134 EXCEPTION
2135 WHEN OTHERS THEN
2136 ozf_utility_pvt.error_message( 'OZF_UPD_RESALE_INT_WRG');
2137 RAISE FND_API.g_exc_unexpected_error;
2138 END;
2139 IF OZF_DEBUG_LOW_ON THEN
2140 ozf_utility_PVT.debug_message('/*--- process_one_order Failed ---*/');
2141 END IF;
2142 END IF;
2143 END IF; -- END if for order_number, sold_from cust, date_ordered not NULL
2144 END LOOP; -- END LOOP for l_order_num_tbl
2145 END IF;
2146
2147 -- Update Chargeback header with processing detail
2148 OZF_RESALE_COMMON_PVT.Update_Batch_Calculations (
2149 p_api_version => 1.0
2150 ,p_init_msg_list => FND_API.G_FALSE
2151 ,p_commit => FND_API.G_FALSE
2152 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
2153 ,p_resale_batch_id => p_resale_batch_id
2154 ,x_return_status => l_return_status
2155 ,x_msg_data => l_msg_data
2156 ,x_msg_count => l_msg_count
2157 );
2158 IF l_return_status = FND_API.g_ret_sts_error THEN
2159 RAISE FND_API.g_exc_error;
2160 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2161 RAISE FND_API.g_exc_unexpected_error;
2162 END IF;
2163
2164 -- Debug Message
2165 IF OZF_DEBUG_HIGH_ON THEN
2166 OZF_UTILITY_PVT.debug_message(p_message_text => l_full_name||': End');
2167 END IF;
2168 --Standard call to get message count and if count=1, get the message
2169 FND_MSG_PUB.Count_And_Get (
2170 p_encoded => FND_API.G_FALSE,
2171 p_count => x_msg_count,
2172 p_data => x_msg_data
2173 );
2174 x_return_status := l_return_status;
2175 EXCEPTION
2176 WHEN FND_API.G_EXC_ERROR THEN
2177 ROLLBACK TO Process_SPP_Order;
2178 x_return_status := FND_API.G_RET_STS_ERROR;
2179 -- Standard call to get message count and if count=1, get the message
2180 FND_MSG_PUB.Count_And_Get (
2181 p_encoded => FND_API.G_FALSE,
2182 p_count => x_msg_count,
2183 p_data => x_msg_data
2184 );
2185 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2186 ROLLBACK TO Process_SPP_Order;
2187 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2188
2189 -- Standard call to get message count and if count=1, get the message
2190 FND_MSG_PUB.Count_And_Get (
2191 p_encoded => FND_API.G_FALSE,
2192 p_count => x_msg_count,
2193 p_data => x_msg_data
2194 );
2195 WHEN OTHERS THEN
2196 ROLLBACK TO Process_SPP_Order;
2197 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2198
2199 -- Standard call to get message count and if count=1, get the message
2200 FND_MSG_PUB.Count_And_Get (
2201 p_encoded => FND_API.G_FALSE,
2202 p_count => x_msg_count,
2203 p_data => x_msg_data
2204 );
2205 END Process_Order;
2206
2207 END OZF_SPECIAL_PRICING_PVT;