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