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