4 -- PACKAGE:
1 PACKAGE BODY OZF_TRACING_ORDER_PVT AS
2 /* $Header: ozfvotrb.pls 120.12.12020000.2 2012/07/20 09:35:14 nepanda ship $ */
3 -------------------------------------------------------------------------------
5 -- OZF_TRACING_ORDER_PVT
6 --
7 -- PURPOSE:
8 -- Private API for Tracing batch.
9 --
10 -- HISTORY:
11 -- 15-Apr-2009 ateotia Bug# 8414563 fixed.
12 -- 06-May-2009 ateotia Bug# 8489216 fixed.
13 -- Moved the logic of End Customer/Bill_To/Ship_To
14 -- Party creation to Common Resale API.
15 -------------------------------------------------------------------------------
16
17 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OZF_TRACING_ORDER_PVT';
18 G_FILE_NAME CONSTANT VARCHAR2(30) := 'ozfvoctrb.pls';
19
20 OZF_DEBUG_HIGH_ON BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_high);
21 OZF_DEBUG_LOW_ON BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low);
22
23 ---------------------------------------------------------------------
24 -- PROCEDURE
25 -- Initiate_payment
26 --
27 -- PURPOSE
28 -- Initiate payment for a batch.
29 --
30 -- PARAMETERS
31 --
32 -- NOTES
33 --
34 ---------------------------------------------------------------------
35 PROCEDURE Initiate_payment (
36 p_api_version IN NUMBER
37 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
38 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
39 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
40 ,p_resale_batch_id IN NUMBER
41 ,x_return_status OUT NOCOPY VARCHAR2
42 ,x_msg_data OUT NOCOPY VARCHAR2
43 ,x_msg_count OUT NOCOPY NUMBER
44 )
45 IS
46 l_api_name CONSTANT VARCHAR2(30) := 'Initiate_payment';
47 l_api_version CONSTANT NUMBER := 1.0;
48 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
49 --
50 l_return_status VARCHAR2(30);
51 l_msg_data VARCHAR2(2000);
52 l_msg_count NUMBER;
53 l_direct_order_flag VARCHAR2(1) := 'N';
54 l_org_id NUMBER;
55
56 CURSOR batch_info_csr (p_id in NUMBER) IS
57 SELECT status_code,
58 batch_type,
59 partner_cust_account_id,
60 partner_party_id,
61 report_start_date,
62 report_end_date,
63 batch_number,
64 last_updated_by,
65 NVL(direct_order_flag,'N'), --For Bug#9447673 SSD IDSM
66 org_id
67 FROM ozf_resale_batches_all
68 WHERE resale_batch_id = p_id;
69 l_batch_status VARCHAR2(30);
70 l_batch_type VARCHAR2(30);
71 l_partner_cust_account_id NUMBER;
72 l_partner_party_id NUMBER;
73 l_report_start_date date;
74 l_report_end_date date;
75 l_batch_NUMBER VARCHAR2(240);
76 l_last_updated_by NUMBER(15);
77
78
79 CURSOR OPEN_line_count_csr (p_id in NUMBER) IS
80 SELECT count(1)
81 From ozf_resale_lines_int
82 WHERE resale_batch_id = p_id
83 AND status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_OPEN;
84 l_count NUMBER;
85
86 TYPE id_type IS RECORD (
87 id NUMBER
88 );
89
90 TYPE id_tbl_type is TABLE OF id_type INDEX BY binary_integer;
91
92 l_line_int_rec OZF_RESALE_COMMON_PVT.g_interface_rec_csr%rowtype;
93
94 CURSOR valid_line_id_csr(p_id in NUMBER,
95 p_order_number in VARCHAR2,
96 p_cust_id in NUMBER,
97 p_date in DATE)
98 IS
99 SELECT resale_line_int_id
100 FROM ozf_resale_lines_int
101 WHERE resale_batch_id = p_id
102 AND order_number = p_order_number
103 AND sold_from_cust_account_id = p_cust_id
104 AND date_ordered = p_date
105 AND status_code = 'PROCESSED';
106 --AND status_code in (OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DUPLICATED, OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED);
110 l_valid_line_id_tbl id_tbl_type;
107 --AND duplicated_adjustment_id <> -1;
108
109
111
112 i NUMBER;
113
114 l_create_order_header boolean := false;
115
116 CURSOR dup_header_id_csr( p_id IN NUMBER
117 , p_order_number IN VARCHAR2
118 , p_cust_id IN NUMBER
119 , p_date IN DATE
120 ) IS
121 SELECT a.resale_header_id
122 FROM ozf_resale_headers a
123 , ozf_resale_lines_int b
124 , ozf_resale_lines c
125 WHERE b.resale_batch_id = p_id
126 AND b.order_number = p_order_number
127 AND b.sold_from_cust_account_id = p_cust_id
128 AND b.date_ordered = p_date
129 AND b.status_code IN ('DUPLICATED', 'PROCESSED')
130 AND b.duplicated_line_id = c.resale_line_id
131 AND c.resale_header_id = a.resale_header_id;
132
133 l_dup_header_id_tbl OZF_RESALE_COMMON_PVT.number_tbl_type;
134
135 l_header_id NUMBER;
136 l_line_id NUMBER;
137
138 CURSOR batch_order_num_csr(p_id in NUMBER)is
139 SELECT DISTINCT order_number,
140 sold_from_cust_account_id,
141 date_ordered
142 FROM ozf_resale_lines_int
143 WHERE resale_batch_id = p_id
144 AND status_code in(OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DUPLICATED, OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED)
145 --AND duplicated_adjustment_id <> -1
146 ORDER BY date_ordered;
147
148 TYPE order_num_tbl_type is TABLE OF batch_order_num_csr%rowtype INDEX BY binary_integer;
149 l_order_num_tbl order_num_tbl_type;
150
151 l_inventory_tracking VARCHAR2(1);
152
153 l_sales_transaction_rec OZF_SALES_TRANSACTIONS_PVT.SALES_TRANSACTION_REC_TYPE;
154
155 l_inventory_level_valid boolean;
156 l_sales_transaction_id NUMBER;
157
158 CURSOR end_cust_relation_flag_csr IS
159 SELECT end_cust_relation_flag
160 -- BUG 4992408 (+)
161 -- FROM ozf_sys_parameters_all;
162 FROM ozf_sys_parameters;
163 -- BUG 4992408 (-)
164
165 l_end_cust_relation_flag varchar2(30);
166
167 --Bug# 8489216 fixed by ateotia(+)
168 /*
169 l_new_party_rec OZF_RESALE_COMMON_PVT.party_rec_type;
170
171 CURSOR csr_orig_billto_cust(cv_resale_batch_id IN NUMBER) IS
172 SELECT DISTINCT bill_to_party_name
173 , bill_to_address
174 , bill_to_city
175 , bill_to_state
176 , bill_to_postal_code
177 , bill_to_country
178 FROM ozf_resale_lines_int_all
179 WHERE resale_batch_id = cv_resale_batch_id
180 AND status_code = 'PROCESSED'
181 AND ( duplicated_line_id IS NULL
182 OR ( duplicated_line_id IS NOT NULL AND duplicated_adjustment_id = -1))
183 AND bill_to_party_id IS NULL
184 AND bill_to_cust_account_id IS NULL
185 AND bill_to_party_name IS NOT NULL;
186
187 TYPE orig_billto_cust_tbl_type IS TABLE of csr_orig_billto_cust%ROWTYPE
188 INDEX BY BINARY_INTEGER;
189 l_orig_billto_cust_tbl orig_billto_cust_tbl_type;
190
191 CURSOR csr_orig_end_cust(cv_resale_batch_id IN NUMBER) IS
192 SELECT DISTINCT end_cust_party_name
193 , end_cust_address
194 , end_cust_city
195 , end_cust_state
196 , end_cust_postal_code
197 , end_cust_country
198 , end_cust_site_use_code
199 FROM ozf_resale_lines_int_all
200 WHERE resale_batch_id = cv_resale_batch_id
201 AND status_code = 'PROCESSED'
202 AND ( duplicated_line_id IS NULL
203 OR ( duplicated_line_id IS NOT NULL AND duplicated_adjustment_id = -1))
204 AND end_cust_party_id IS NULL
205 AND end_cust_party_name IS NOT NULL;
206
207 TYPE orig_end_cust_tbl_type IS TABLE of csr_orig_end_cust%ROWTYPE
208 INDEX BY BINARY_INTEGER;
209 l_orig_end_cust_tbl orig_end_cust_tbl_type;
210 */
211 --Bug# 8489216 fixed by ateotia(-)
212
213 BEGIN
214 -- Standard begin of API savepoint
215 SAVEPOINT TRAC_INITIATE_PAYMENT;
216 -- Standard call to check for call compatibility.
217 IF NOT FND_API.Compatible_API_Call (
218 l_api_version,
219 p_api_version,
220 l_api_name,
221 G_PKG_NAME)
222 THEN
223 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
224 END IF;
225
226 --Initialize message list if p_init_msg_list is TRUE.
227 IF FND_API.To_Boolean (p_init_msg_list) THEN
228 FND_MSG_PUB.initialize;
229 END IF;
230
231 -- Debug Message
232 IF OZF_DEBUG_HIGH_ON THEN
233 OZF_UTILITY_PVT.debug_message(p_message_text => l_full_name||': Start');
234 END IF;
235
236
237 -- Initialize API return status to sucess
238 x_return_status := FND_API.G_RET_STS_SUCCESS;
239
240 OPEN Batch_info_csr(p_resale_batch_id);
241 FETCH batch_info_csr INTO l_batch_status,
242 l_batch_type,
243 l_partner_cust_account_id,
244 l_partner_party_id,
245 l_report_start_date,
246 l_report_end_date,
247 l_batch_NUMBER,
248 l_last_updated_by,
249 l_direct_order_flag,
250 l_org_id;
251 CLOSE batch_info_csr;
252
253 -- Fix for bug # 12716192 : commented extra set context
254 -- MO_GLOBAL.set_policy_context( p_access_mode => 'S', p_org_id => l_org_id);
255
256 IF l_batch_status = OZF_RESALE_COMMON_PVT.G_BATCH_PENDING_PAYMENT THEN
257
258 OPEN OPEN_line_count_csr(p_resale_batch_id);
259 FETCH OPEN_line_count_csr INTO l_count;
260 CLOSE OPEN_line_count_csr;
261
262 IF l_count <> 0 THEN
263 --Can not pay if there is an OPEN line
264 ozf_utility_pvt.error_message('OZF_RESALE_OPEN_LINE_EXIST');
265 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
266 ELSE
267 -- Check whether there is a need to do inventory_verification
268 OPEN OZF_RESALE_COMMON_PVT.g_inventory_tracking_csr;
269 FETCH OZF_RESALE_COMMON_PVT.g_inventory_tracking_csr INTO l_inventory_tracking;
270 CLOSE OZF_RESALE_COMMON_PVT.g_inventory_tracking_csr;
271
272 IF l_inventory_tracking = 'T'
273 AND ( l_direct_order_flag <> 'Y') THEN -- Indirect order Check
274
275 OZF_SALES_TRANSACTIONS_PVT.Initiate_Inventory_tmp (
276 p_api_version => 1.0
277 ,p_init_msg_list => FND_API.G_FALSE
278 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
279 ,p_resale_batch_id => p_resale_batch_id
280 ,p_start_date => l_report_start_date
281 ,p_end_date => l_report_end_date
282 ,x_return_status => l_return_status
283 ,x_msg_count => l_msg_count
284 ,x_msg_data => l_msg_data
285 );
286 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
287 OZF_UTILITY_PVT.error_message('OZF_RESALE_INIT_INV_TMP_ERR');
288 RAISE FND_API.G_EXC_ERROR;
289 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
290 OZF_UTILITY_PVT.error_message('OZF_RESALE_INIT_INV_TMP_ERR');
291 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
292 END IF;
293 END IF;
294
295 OPEN end_cust_relation_flag_csr;
296 FETCH end_cust_relation_flag_csr INTO l_end_cust_relation_flag;
297 CLOSE end_cust_relation_flag_csr;
298
299 IF l_end_cust_relation_flag = 'T' THEN
300 --Bug# 8489216 fixed by ateotia(+)
301 --Moved the logic of End Customer/Bill_To/Ship_To Party creation to Common Resale API.
302 OZF_RESALE_COMMON_PVT.Derive_Orig_Parties
303 ( p_api_version => 1.0
304 ,p_init_msg_list => FND_API.G_FALSE
305 ,p_commit => FND_API.G_FALSE
306 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
307 ,p_resale_batch_id => p_resale_batch_id
308 ,p_partner_party_id => l_partner_party_id
309 ,x_return_status => l_return_status
310 ,x_msg_data => l_msg_data
311 ,x_msg_count => l_msg_count
312 );
313 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
314 RAISE FND_API.G_EXC_ERROR;
315 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
316 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
317 END IF;
318 /*
319 -- check bill_to customer
320 OPEN csr_orig_billto_cust(p_resale_batch_id);
321 FETCH csr_orig_billto_cust BULK COLLECT INTO l_orig_billto_cust_tbl;
322 CLOSE csr_orig_billto_cust;
323
324 IF l_orig_billto_cust_tbl.COUNT > 0 THEN
325 FOR i IN 1..l_orig_billto_cust_tbl.COUNT LOOP
326 l_new_party_rec := NULL;
327 -- Bug 4737415 (+)
328 l_new_party_rec.partner_party_id := l_partner_party_id;
329 -- Bug 4737415 (-)
330 l_new_party_rec.name := l_orig_billto_cust_tbl(i).bill_to_party_name;
331 l_new_party_rec.address := l_orig_billto_cust_tbl(i).bill_to_address;
332 l_new_party_rec.city := l_orig_billto_cust_tbl(i).bill_to_city;
333 l_new_party_rec.state := l_orig_billto_cust_tbl(i).bill_to_state;
334 l_new_party_rec.postal_Code := l_orig_billto_cust_tbl(i).bill_to_postal_code;
335 l_new_party_rec.country := l_orig_billto_cust_tbl(i).bill_to_country;
336 l_new_party_rec.site_Use_Code := 'BILL_TO';
337
338 OZF_RESALE_COMMON_PVT.Create_Party(
339 p_api_version => 1.0
340 ,p_init_msg_list => FND_API.G_FALSE
341 ,p_commit => FND_API.G_FALSE
342 ,p_validation_level=> FND_API.G_VALID_LEVEL_FULL
343 ,px_party_rec => l_new_party_rec
344 ,x_return_status => l_return_status
345 ,x_msg_data => l_msg_data
346 ,x_msg_count => l_msg_count
347 );
348 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
349 RAISE FND_API.G_EXC_ERROR;
350 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
351 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
352 END IF;
353
354 UPDATE ozf_resale_lines_int_all
355 SET bill_to_party_id = l_new_party_rec.party_id
356 , bill_to_party_site_id = l_new_party_rec.party_site_id
357 WHERE resale_batch_id = p_resale_batch_id
358 AND bill_to_party_id IS NULL
359 AND bill_to_cust_account_id IS NULL
360 AND bill_to_party_name = l_orig_billto_cust_tbl(i).bill_to_party_name
361 AND bill_to_address = l_orig_billto_cust_tbl(i).bill_to_address
362 AND bill_to_city = l_orig_billto_cust_tbl(i).bill_to_city
363 AND bill_to_state = l_orig_billto_cust_tbl(i).bill_to_state
364 AND bill_to_postal_code = l_orig_billto_cust_tbl(i).bill_to_postal_code
365 AND bill_to_country = l_orig_billto_cust_tbl(i).bill_to_country;
366
367 END LOOP;
368 END IF;
369
370 -- check end_customer
371 OPEN csr_orig_end_cust(p_resale_batch_id);
372 FETCH csr_orig_end_cust BULK COLLECT INTO l_orig_end_cust_tbl;
373 CLOSE csr_orig_end_cust;
374
375 IF l_orig_end_cust_tbl.COUNT > 0 THEN
376 FOR i IN 1..l_orig_end_cust_tbl.COUNT LOOP
377 l_new_party_rec := NULL;
378 -- Bug 4737415 (+)
379 l_new_party_rec.partner_party_id := l_partner_party_id;
380 -- Bug 4737415 (-)
381 -- reset values:
382 l_new_party_rec.party_id := NULL;
383 l_new_party_rec.party_site_id := NULL;
384 l_new_party_rec.party_site_use_id := NULL;
385 l_new_party_rec.name := l_orig_end_cust_tbl(i).end_cust_party_name;
386 l_new_party_rec.address := l_orig_end_cust_tbl(i).end_cust_address;
387 l_new_party_rec.city := l_orig_end_cust_tbl(i).end_cust_city;
388 l_new_party_rec.state := l_orig_end_cust_tbl(i).end_cust_state;
389 l_new_party_rec.postal_code := l_orig_end_cust_tbl(i).end_cust_postal_code;
390 l_new_party_rec.country := l_orig_end_cust_tbl(i).end_cust_country;
391 l_new_party_rec.site_use_code := l_orig_end_cust_tbl(i).end_cust_site_use_code;
392
393 OZF_RESALE_COMMON_PVT.Create_Party
394 ( p_api_version => 1.0
395 ,p_init_msg_list => FND_API.G_FALSE
396 ,p_commit => FND_API.G_FALSE
397 ,p_validation_level=> FND_API.G_VALID_LEVEL_FULL
398 ,px_party_rec => l_new_party_rec
399 ,x_return_status => l_return_status
400 ,x_msg_data => l_msg_data
401 ,x_msg_count => l_msg_count
402 );
403 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
404 RAISE FND_API.G_EXC_ERROR;
405 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
406 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
407 END IF;
408
409 UPDATE ozf_resale_lines_int_all
410 SET end_cust_party_id = l_new_party_rec.party_id
411 , end_cust_party_site_id = l_new_party_rec.party_site_id
412 WHERE resale_batch_id = p_resale_batch_id
413 AND end_cust_party_id IS NULL
414 AND end_cust_party_name = l_orig_end_cust_tbl(i).end_cust_party_name
415 AND end_cust_address = l_orig_end_cust_tbl(i).end_cust_address
416 AND end_cust_city = l_orig_end_cust_tbl(i).end_cust_city
417 AND end_cust_state = l_orig_end_cust_tbl(i).end_cust_state
418 AND end_cust_postal_code = l_orig_end_cust_tbl(i).end_cust_postal_code
419 AND end_cust_country = l_orig_end_cust_tbl(i).end_cust_country
420 AND end_cust_site_use_code = l_orig_end_cust_tbl(i).end_cust_site_use_code;
421 END LOOP;
422 END IF;
423 */
424 --Bug# 8489216 fixed by ateotia(-)
425 END IF;
426
427 --i:=1;
428 IF l_order_num_tbl.EXISTS(1) THEN
429 l_order_num_tbl.DELETE;
430 END IF;
431 OPEN batch_order_num_csr(p_resale_batch_id);
432 FETCH batch_order_num_csr BULK COLLECT INTO l_order_num_tbl;
433 --LOOP
434 -- EXIT WHEN batch_order_num_csr%NOTFOUND;
435 -- FETCH batch_order_num_csr INTO l_order_num_tbl(i);
436 -- i:= i+1;
437 --END LOOP;
438 CLOSE batch_order_num_csr;
439
440 IF l_order_num_tbl.EXISTS(1) THEN -- IF B
441 FOR k in 1..l_order_num_tbl.LAST LOOP -- FOR C
442
443 IF OZF_DEBUG_LOW_ON THEN
444 ozf_utility_PVT.debug_message('PROCESS ORDER: ');
445 ozf_utility_PVT.debug_message('ORDER NUMBER: '||l_order_num_tbl(k).order_number);
446 ozf_utility_PVT.debug_message('SOLD_FROM_ACCT: '||l_order_num_tbl(k).sold_from_cust_account_id);
447 ozf_utility_PVT.debug_message('DATE ORDERED: '||l_order_num_tbl(k).date_ordered);
448 END IF;
449
450 -- beginjxwu header_fix
451 -- Here, I assume if a line is the duplicate of another line, then they share
452 -- the same order header. Hence all order with this duplicated line share the
453 -- the same order with the oringinal lines.
454 IF l_dup_header_id_tbl.EXISTS(1) THEN
455 l_dup_header_id_tbl.DELETE;
456 END IF;
457 OPEN dup_header_id_csr(p_resale_batch_id,
458 l_order_num_tbl(k).order_number,
459 l_order_num_tbl(k).sold_from_cust_account_id,
460 l_order_num_tbl(k).date_ordered
461 );
462 FETCH dup_header_id_csr BULK COLLECT INTO l_dup_header_id_tbl;
463 CLOSE dup_header_id_csr;
464
465 IF l_dup_header_id_tbl.EXISTS(1) THEN
466 IF l_dup_header_id_tbl.EXISTS(2) THEN
467
468 -- There is something wrong with this order. dispute all the orders
469 -- and move to the next one.
470 -- JXWU move update to common pvt
471 UPDATE ozf_resale_lines_int_all
472 SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DISPUTED,
473 dispute_code = 'OZF_RESALE_MULTI_HEADER',
474 followup_action_code = NVL2(followup_action_code,followup_action_code,'C'),
475 response_type = 'CA',
476 response_code = 'N'
477 WHERE resale_batch_id = p_resale_batch_id
478 AND order_number = l_order_num_tbl(k).order_number
479 AND sold_from_cust_account_id = l_order_num_tbl(k).sold_from_cust_account_id
480 AND date_ordered = l_order_num_tbl(k).date_ordered
481 AND status_code in (OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DUPLICATED, OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED);
482 --AND duplicated_adjustment_id <> -1;
483
484
485 GOTO END_ORDER_HEADER;
486 ELSE
487 l_create_order_header := false;
488 l_header_id := l_dup_header_id_tbl(1);
489 END IF;
490 ELSE
491 l_create_order_header := true;
492 END IF;
493
494 --End jxuw header_fix
495
496
497 -- Here only duplicated and processed lines are considered. DISPUTED lines will not
498 -- be moved to resale order table.
499 --i:=1;
500 IF l_valid_line_id_tbl.EXISTS(1) THEN
501 l_valid_line_id_tbl.DELETE;
502 END IF;
503 OPEN valid_line_id_csr(p_resale_batch_id,
504 l_order_num_tbl(k).order_number,
505 l_order_num_tbl(k).sold_from_cust_account_id,
506 l_order_num_tbl(k).date_ordered);
507 FETCH valid_line_id_csr BULK COLLECT INTO l_valid_line_id_tbl;
508 --LOOP
509 -- EXIT WHEN valid_line_id_csr%NOTFOUND;
510 -- FETCH valid_line_id_csr INTO l_valid_line_id_tbl(i);
511 -- i := i+1;
512 --END LOOP;
513 CLOSE valid_line_id_csr;
514
515 -- Again, we need to check whether if any line IS a duplicate or NOT.
516 IF l_valid_line_id_tbl.EXISTS(1) THEN -- IF D
517 -- I then try to create resale data.
518 FOR j in 1..l_valid_line_id_tbl.last -- FOR E
519 LOOP
520 IF OZF_DEBUG_LOW_ON THEN
521 OZF_UTILITY_PVT.debug_message('Current line_int_id:' || l_valid_line_id_tbl(j).id);
522 END IF;
523 OPEN OZF_RESALE_COMMON_PVT.g_interface_rec_csr(l_valid_line_id_tbl(j).id);
524 FETCH OZF_RESALE_COMMON_PVT.g_interface_rec_csr INTO l_line_int_rec;
525 CLOSE OZF_RESALE_COMMON_PVT.g_interface_rec_csr;
526
527
528 /*
529 For Bug#9447673 SSD IDSM ER - Restricting the
530 Validate inventory flow for Direct order only
531 */
532 -- Need to check against inventory
533 IF l_line_int_rec.status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED
534 AND l_inventory_tracking = 'T'
535 AND (l_direct_order_flag <> 'Y') THEN
536 -- Check inventory level first
537 OZF_SALES_TRANSACTIONS_PVT.Validate_Inventory_level (
538 p_api_version => 1.0
539 ,p_init_msg_list => FND_API.G_FALSE
540 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
541 ,p_line_int_rec => l_line_int_rec
542 ,x_valid => l_inventory_level_valid
543 ,x_return_status => l_return_status
544 ,x_msg_count => l_msg_count
545 ,x_msg_data => l_msg_data
546 );
547 IF NOT l_inventory_level_valid THEN
548 IF OZF_DEBUG_LOW_ON THEN
549 OZF_UTILITY_PVT.debug_message('Did NOT pass inventory checking');
550 END IF;
551 -- Here turn thIS line to DISPUTED AND create a dISput code FOR it.
552 UPDATE ozf_resale_lines_int
553 SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DISPUTED,
554 dispute_code = 'OZF_LT_INVT',
555 followup_action_code = NVL2(followup_action_code,followup_action_code,'C'),
556 response_type = 'CA',
557 response_code = 'N'
558 WHERE resale_line_int_id = l_line_int_rec.resale_line_int_id;
559
560 -- SET Batch as DISPUTED
561 UPDATE ozf_resale_batches
562 SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_DISPUTED
563 WHERE resale_batch_id = l_line_int_rec.resale_batch_id;
564
565 GOTO END_ORDER_LINE;
566 END IF;
567 -- Check WAC
568 END IF;
569
570 -- First, check whether there is need to create a header for this order
571 -- SLKRISHN Add logic to derive or insert header
572 -- see jxwu header_fix.
573 IF j = 1 THEN
574 -- Determin header id
575 IF l_create_order_header THEN
576 OZF_RESALE_COMMON_PVT.Insert_Resale_Header(
577 p_api_version => 1
578 ,p_init_msg_list => FND_API.G_FALSE
579 ,p_commit => FND_API.G_FALSE
580 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
581 ,p_line_int_rec => l_line_int_rec
582 ,x_header_id => l_header_id
583 ,x_return_status => l_return_status
584 ,x_msg_data => l_msg_data
585 ,x_msg_count => l_msg_count
586 );
587 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
588 RAISE FND_API.G_EXC_ERROR;
589 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
590 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
591 END IF;
592 --
593 ELSE
594 NULL;
595 -- We should have the l_header_id FROM at the order level
596 END IF;
597 END IF;
598
599 IF OZF_DEBUG_LOW_ON THEN
600 OZF_UTILITY_PVT.debug_message('header_id is '|| l_header_id);
601 END IF;
602
603 IF l_line_int_rec.status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED THEN
604 IF l_line_int_rec.duplicated_line_id IS NULL THEN
605
606 -- We need to create party id for bill_to and end customer if
607 -- users wants us to do it.
608
609 -- No problem so far. Insert INTO batch_lines TABLE
610 OZF_RESALE_COMMON_PVT.Insert_Resale_Line(
611 p_api_version => 1
612 ,p_init_msg_list => FND_API.G_FALSE
613 ,p_commit => FND_API.G_FALSE
614 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
615 ,p_line_int_rec => l_line_int_rec
616 ,p_header_id => l_header_id
617 ,x_line_id => l_line_id
618 ,x_return_status => l_return_status
619 ,x_msg_data => l_msg_data
620 ,x_msg_count => l_msg_count
621 );
622 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
623 RAISE FND_API.G_EXC_ERROR;
624 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
625 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
626 END IF;
627 --
628
629 -- FOR processed order line, I need to create a transaction FOR it.
630 OZF_RESALE_COMMON_PVT.Create_Sales_Transaction (
631 p_api_version => 1.0
632 ,p_init_msg_list => FND_API.G_FALSE
633 ,p_commit => FND_API.G_FALSE
634 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
635 ,p_line_int_rec => l_line_int_rec
636 ,p_header_id => l_header_id
637 ,p_line_id => l_line_id
638 ,x_sales_transaction_id => l_sales_transaction_id
639 ,x_return_status => l_return_status
640 ,x_msg_data => l_msg_data
641 ,x_msg_count => l_msg_count
642 );
643 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
644 RAISE FND_API.G_EXC_ERROR;
645 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
646 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
647 END IF;
648 --
649
650 /*
651 For Bug#9447673 SSD IDSM ER - Restricting the
652 update inventory flow for Direct order only
653 */
654 IF l_inventory_tracking = 'T'
655 AND (l_direct_order_flag <> 'Y') THEN
656 OZF_SALES_TRANSACTIONS_PVT.Update_Inventory_Tmp (
657 p_api_version => 1.0
658 ,p_init_msg_list => FND_API.G_FALSE
659 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
660 ,p_sales_transaction_id => l_sales_transaction_id
661 ,x_return_status => l_return_status
662 ,x_msg_data => l_msg_data
663 ,x_msg_count => l_msg_count
664 );
665 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
666 RAISE FND_API.G_EXC_ERROR;
667 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
668 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
669 END IF;
670 --
671 END IF;
672 ELSE
673 l_line_id := l_line_int_rec.duplicated_line_id;
674 END IF;
675 ELSIF l_line_int_rec.status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DUPLICATED THEN
676 l_line_id := l_line_int_rec.duplicated_line_id;
677 END IF;
678
679 IF OZF_DEBUG_LOW_ON THEN
680 OZF_UTILITY_PVT.debug_message('line_id is '|| l_line_id);
681 END IF;
682
683 IF l_line_int_rec.status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED OR
684 l_line_int_rec.status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DUPLICATED
685 THEN
686 -- only create mapping FOR the lines that are processed or
687 -- duplicated, yet the adjustment IS new then
688 OZF_RESALE_COMMON_PVT.Insert_Resale_Line_Mapping(
689 p_api_version => 1
690 ,p_init_msg_list => FND_API.G_FALSE
691 ,p_commit => FND_API.G_FALSE
692 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
693 ,p_resale_batch_id => p_resale_batch_id
694 ,p_line_id => l_line_id
695 ,x_return_status => l_return_status
696 ,x_msg_data => l_msg_data
700 RAISE FND_API.G_EXC_ERROR;
697 ,x_msg_count => l_msg_count
698 );
699 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
701 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
702 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
703 END IF;
704 END IF;
705
706 << END_ORDER_LINE >>
707 NULL;
708 END LOOP; -- END LOOP FOR this order -- FOR E
709 END IF; -- if valid line id EXISTS -- IF D
710 << END_ORDER_HEADER>>
711 NULL;
712 END LOOP; -- END LOOP FOR the batch FOR C
713 END IF; -- END order_num EXISTS IF B
714
715
716 -- END IF; -- END if not rejected
717 END IF; -- END l_count = 0
718
719
720 -- UPDATE batch line status to CLOSEd for each OPEN and duplicated lines.
721
722 BEGIN
723 UPDATE ozf_resale_lines_int_all
724 SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_CLOSED
725 WHERE resale_batch_id = p_resale_batch_id
726 --Bug# 8414563 fixed by ateotia(+)
727 --AND status_code in(OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DUPLICATED, OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED);
728 AND status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED;
729 --Bug# 8414563 fixed by ateotia(-)
730 --AND duplicated_adjustment_id <> -1;
731
732 -- UPDATE batch status to CLOSEd -- might change later.
733 UPDATE ozf_resale_batches_all
734 SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_CLOSED
735 WHERE resale_batch_id = p_resale_batch_id;
736 EXCEPTION
737 WHEN OTHERS THEN
738 ozf_utility_pvt.error_message('OZF_UPD_RESALE_WRG','TEXT',l_full_name||': END');
739 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
740 END;
741
742 ELSE
743 -- Only disputed and processed batch can be paid.
744 ozf_utility_pvt.error_message('OZF_RESALE_WRONG_STAUS_TO_PAY');
745 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
746 END IF;
747
748 -- Debug Message
749 IF OZF_DEBUG_HIGH_ON THEN
750 OZF_UTILITY_PVT.debug_message(p_message_text => l_full_name||': End');
751 END IF;
752 --Standard call to get message count and if count=1, get the message
753 FND_MSG_PUB.Count_And_Get (
754 p_encoded => FND_API.G_FALSE,
755 p_count => x_msg_count,
756 p_data => x_msg_data
757 );
758 x_return_status := l_return_status;
759 EXCEPTION
760 WHEN FND_API.G_EXC_ERROR THEN
761 x_return_status := FND_API.G_RET_STS_ERROR;
762 -- Standard call to get message count and if count=1, get the message
763 FND_MSG_PUB.Count_And_Get (
764 p_encoded => FND_API.G_FALSE,
765 p_count => x_msg_count,
766 p_data => x_msg_data
767 );
768 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
769 ROLLBACK TO TRAC_INITIATE_PAYMENT;
770 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
771
772 -- Standard call to get message count and if count=1, get the message
773 FND_MSG_PUB.Count_And_Get (
774 p_encoded => FND_API.G_FALSE,
775 p_count => x_msg_count,
776 p_data => x_msg_data
777 );
778 WHEN OTHERS THEN
779 ROLLBACK TO TRAC_INITIATE_PAYMENT;
780 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
781
782 -- Standard call to get message count and if count=1, get the message
783 FND_MSG_PUB.Count_And_Get (
784 p_encoded => FND_API.G_FALSE,
785 p_count => x_msg_count,
786 p_data => x_msg_data
787 );
788 END Initiate_payment;
789
790 ---------------------------------------------------------------------
791 -- PROCEDURE
792 -- Validate_Order_Record
793 --
794 -- PURPOSE
795 -- This procedure validates the order information
796 -- These are validation specific to chargeback process
797 --
798 -- PARAMETERS
799 --
800 --
801 -- NOTES
802 ---------------------------------------------------------------------
803 PROCEDURE Validate_Order_Record(
804 p_api_version IN NUMBER
805 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
806 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
807 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
808 ,p_resale_batch_id IN NUMBER
809 ,x_return_status OUT NOCOPY VARCHAR2
810 ,x_msg_data OUT NOCOPY VARCHAR2
811 ,x_msg_count OUT NOCOPY NUMBER
812 )IS
813 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Order_Record';
814 l_api_version CONSTANT NUMBER := 1.0;
815 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
816 --
817 l_return_status VARCHAR2(30);
818 l_msg_data VARCHAR2(2000);
819 l_msg_count NUMBER;
820
821 CURSOR non_tracing_count_csr(p_id IN NUMBER) IS
822 SELECT COUNT(1)
823 FROM ozf_resale_lines_int
824 WHERE resale_batch_id = p_id
825 AND tracing_flag = 'F';
826
827 l_non_tracing_count NUMBER;
828
829 BEGIN
830 -- Standard begin of API savepoint
834 p_api_version,
831 -- Standard call to check for call compatibility.
832 IF NOT FND_API.Compatible_API_Call (
833 l_api_version,
835 l_api_name,
836 G_PKG_NAME)
837 THEN
838 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
839 END IF;
840
841 -- Debug Message
842 IF OZF_DEBUG_HIGH_ON THEN
843 OZF_UTILITY_PVT.debug_message(p_message_text => l_full_name||': Start');
844 END IF;
845
846 --Initialize message list if p_init_msg_list is TRUE.
847 IF FND_API.To_Boolean (p_init_msg_list) THEN
848 FND_MSG_PUB.initialize;
849 END IF;
850
851 -- Initialize API return status to sucess
852 x_return_status := FND_API.G_RET_STS_SUCCESS;
853
854 OPEN non_tracing_count_csr (p_resale_batch_id);
855 FETCH non_tracing_count_csr INTO l_non_tracing_count;
856 CLOSE non_tracing_count_csr;
857
858 IF l_non_tracing_count > 0 THEN
859 BEGIN
860 INSERT INTO ozf_resale_logs_all(
861 RESALE_LOG_ID,
862 RESALE_ID,
863 RESALE_ID_TYPE,
864 ERROR_CODE,
865 ERROR_MESSAGE,
866 COLUMN_NAME,
867 COLUMN_VALUE,
868 ORG_ID
869 ) SELECT
870 ozf_resale_logs_all_s.nextval,
871 resale_line_int_id,
872 'IFACE',
873 'OZF_RESALE_NON_TRC',
874 FND_MESSAGE.get_string('OZF','OZF_RESALE_NON_TRC'),
875 'TRACING_FLAG',
876 tracing_flag,
877 org_id
878 FROM ozf_resale_lines_int_all b
879 WHERE b.status_code = 'OPEN'
880 AND b.tracing_flag = 'F'
881 AND b.resale_batch_id = p_resale_batch_id
882 AND NOT EXISTS(SELECT 1
883 FROM ozf_resale_logs_all a
884 WHERE a.resale_id = b.resale_line_int_id
885 AND a.RESALE_ID_TYPE = 'IFACE'
886 AND a.error_code ='OZF_RESALE_NON_TRC'
887 );
888 EXCEPTION
889 WHEN OTHERS THEN
890 OZF_UTILITY_PVT.error_message(
891 p_message_name => 'OZF_INS_RESALE_LOG_WRG',
892 p_token_name => 'TEXT',
893 p_token_value => l_full_name||': END');
894 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
895 END;
896
897 UPDATE ozf_resale_lines_int_all
898 SET status_code = 'DISPUTED',
899 dispute_code = 'OZF_RESALE_NON_TRC',
900 followup_action_code = NVL2(followup_action_code,followup_action_code,'C'),
901 response_type = 'CA',
902 response_code = 'N'
903 WHERE resale_batch_id = p_resale_batch_id
904 AND tracing_flag = 'F';
905 END IF;
906
907 -- Debug Message
908 IF OZF_DEBUG_HIGH_ON THEN
909 OZF_UTILITY_PVT.debug_message(p_message_text => l_full_name||': End');
910 END IF;
911 --Standard call to get message count and if count=1, get the message
912 FND_MSG_PUB.Count_And_Get (
913 p_encoded => FND_API.G_FALSE,
914 p_count => x_msg_count,
915 p_data => x_msg_data
916 );
917 EXCEPTION
918 WHEN FND_API.G_EXC_ERROR THEN
919 x_return_status := FND_API.G_RET_STS_ERROR;
920 -- Standard call to get message count and if count=1, get the message
921 FND_MSG_PUB.Count_And_Get (
922 p_encoded => FND_API.G_FALSE,
923 p_count => x_msg_count,
924 p_data => x_msg_data
925 );
926 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
927 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
928 -- Standard call to get message count and if count=1, get the message
929 FND_MSG_PUB.Count_And_Get (
930 p_encoded => FND_API.G_FALSE,
931 p_count => x_msg_count,
932 p_data => x_msg_data
933 );
934 WHEN OTHERS THEN
935 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
936
937 -- Standard call to get message count and if count=1, get the message
938 FND_MSG_PUB.Count_And_Get (
939 p_encoded => FND_API.G_FALSE,
940 p_count => x_msg_count,
941 p_data => x_msg_data
942 );
943 END Validate_Order_Record;
944
945
946 ---------------------------------------------------------------------
947 -- PROCEDURE
948 -- Process_Order
949 --
950 -- PURPOSE
951 -- Process order information for tracing data. No simulation is needed.
952 --
953 -- PARAMETERS
954 --
955 -- NOTES
956 --
957 ---------------------------------------------------------------------
958 PROCEDURE Process_Order
959 (
960 p_api_version IN NUMBER
961 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
962 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
963 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
964 ,p_resale_batch_id IN NUMBER
965 ,x_return_status OUT NOCOPY VARCHAR2
966 ,x_msg_data OUT NOCOPY VARCHAR2
967 ,x_msg_count OUT NOCOPY NUMBER
968 )IS
969 l_api_name CONSTANT VARCHAR2(30) := 'Process_Order';
970 l_api_version CONSTANT NUMBER := 1.0;
971 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
972 --
973 l_return_status VARCHAR2(30);
974 l_msg_data VARCHAR2(2000);
975 l_msg_count NUMBER;
976 l_direct_order_flag VARCHAR2(1) := 'N';
977
978 CURSOR order_num_csr IS
979 SELECT DISTINCT order_number,
980 sold_from_cust_account_id,
981 date_ordered
985 AND duplicated_adjustment_id IS NULL
982 FROM ozf_resale_lines_int
983 WHERE status_code = 'OPEN'
984 -- status_code in(OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_OPEN, OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DUPLICATED)
986 AND resale_batch_id = p_resale_batch_id
987 ORDER BY date_ordered;
988
989 l_cust_account_id_tbl OZF_RESALE_COMMON_PVT.number_tbl_type;
990 l_order_num_tbl OZF_RESALE_COMMON_PVT.varchar_tbl_type;
991 l_order_date_tbl OZF_RESALE_COMMON_PVT.date_tbl_type;
992
993 CURSOR order_set_csr(p_order_NUMBER in VARCHAR2,
994 p_cust_account_id in NUMBER,
995 p_date in date) IS
996 SELECT *
997 FROM ozf_resale_lines_int
998 WHERE order_NUMBER = p_order_NUMBER
999 AND date_ordered = p_date
1000 AND sold_from_cust_account_id = p_cust_account_id
1001 AND status_code = 'OPEN'
1002 --AND status_code in(OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_OPEN, OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DUPLICATED)
1003 AND duplicated_adjustment_id is NULL
1004 AND resale_batch_id = p_resale_batch_id;
1005
1006 TYPE resale_lines_tbl_type IS TABLE OF order_set_csr%rowtype INDEX BY binary_integer;
1007 l_order_set_tbl resale_lines_tbl_type;
1008
1009 CURSOR invalid_line_count_csr (p_id NUMBER) IS
1010 SELECT COUNT(1)
1011 FROM ozf_resale_lines_int
1012 WHERE status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DISPUTED
1013 AND dispute_code = OZF_RESALE_COMMON_PVT.G_INVALD_DISPUTE_CODE
1014 AND resale_batch_id = p_id;
1015
1016 CURSOR Batch_info_csr (p_id in NUMBER) IS
1017 SELECT partner_party_id,
1018 report_start_date,
1019 report_end_date,
1020 NVL(direct_order_flag,'N') -- For Bug#9447673 SSD IDSM
1021 FROM ozf_resale_batches
1022 WHERE resale_batch_id = p_id;
1023
1024 l_status_code VARCHAR2(30);
1025 l_inventory_level_valid boolean;
1026 l_inventory_tracking VARCHAR2(3);
1027 l_partner_party_id NUMBER;
1028 l_report_start_date DATE;
1029 l_report_end_date DATE;
1030 l_lines_disputed NUMBER;
1031 l_lines_invalid NUMBER;
1032 l_lines_duplicated NUMBER; --Bug# 8414563 fixed by ateotia
1033
1034 BEGIN
1035 -- Standard begin of API savepoint
1036 SAVEPOINT PROCESS_TRAC_ORDER;
1037 -- Standard call to check for call compatibility.
1038 IF NOT FND_API.Compatible_API_Call (
1039 l_api_version,
1040 p_api_version,
1041 l_api_name,
1042 G_PKG_NAME)
1043 THEN
1044 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1045 END IF;
1046
1047 -- Debug Message
1048 IF OZF_DEBUG_HIGH_ON THEN
1049 OZF_UTILITY_PVT.debug_message(p_message_text => l_full_name||': Start');
1050 END IF;
1051
1052 --Initialize message list if p_init_msg_list is TRUE.
1053 IF FND_API.To_Boolean (p_init_msg_list) THEN
1054 FND_MSG_PUB.initialize;
1055 END IF;
1056
1057 -- Initialize API return status to sucess
1058 x_return_status := FND_API.G_RET_STS_SUCCESS;
1059
1060
1061 /*
1062 OZF_RESALE_COMMON_PVT.Delete_Log(
1063 p_api_version => 1.0
1064 ,p_init_msg_list => FND_API.G_FALSE
1065 ,p_commit => FND_API.G_FALSE
1066 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
1067 ,p_resale_batch_id => p_resale_batch_id
1068 ,x_return_status => l_return_status
1069 ,x_msg_count => l_msg_count
1070 ,x_msg_data => l_msg_data
1071 );
1072 IF l_return_status = FND_API.g_ret_sts_error THEN
1073 RAISE FND_API.g_exc_error;
1074 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1075 RAISE FND_API.g_exc_unexpected_error;
1076 END IF;
1077 */
1078
1079 -- fetch all orders into a cursor.
1080 IF l_order_num_tbl.EXISTS(1) THEN
1081 l_order_num_tbl.DELETE;
1082 l_cust_account_id_tbl.DELETE;
1083 l_order_date_tbl.DELETE;
1084 END IF;
1085 OPEN order_num_csr;
1086 FETCH order_num_csr BULK COLLECT INTO l_order_num_tbl,l_cust_account_id_tbl, l_order_date_tbl;
1087 CLOSE order_num_csr;
1088
1089 IF l_order_num_tbl.EXISTS(1) THEN
1090
1091 OPEN OZF_RESALE_COMMON_PVT.g_inventory_tracking_csr;
1092 FETCH OZF_RESALE_COMMON_PVT.g_inventory_tracking_csr INTO l_inventory_tracking;
1093 CLOSE OZF_RESALE_COMMON_PVT.g_inventory_tracking_csr;
1094
1095 IF l_inventory_tracking = 'T' THEN
1096 OPEN Batch_info_csr(p_resale_batch_id);
1097 FETCH batch_info_csr INTO l_partner_party_id,
1098 l_report_start_date,
1099 l_report_end_date,
1100 l_direct_order_flag;
1101 CLOSE batch_info_csr;
1102
1103
1104 /*
1105 For Bug#9447673 SSD IDSM ER - Restricting the
1106 Initiate inventory flow for Direct order only
1107 */
1108 IF (l_direct_order_flag <> 'Y') THEN
1109 -- Bug 4380203 Fixing (+)
1110 OZF_SALES_TRANSACTIONS_PVT.Initiate_Inventory_tmp (
1111 p_api_version => 1.0
1112 ,p_init_msg_list => FND_API.G_FALSE
1113 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
1114 ,p_resale_batch_id => p_resale_batch_id
1115 ,p_start_date => l_report_start_date
1116 ,p_end_date => l_report_end_date
1117 ,x_return_status => l_return_status
1118 ,x_msg_count => l_msg_count
1119 ,x_msg_data => l_msg_data
1120 );
1121 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1122 OZF_UTILITY_PVT.error_message('OZF_RESALE_INIT_INV_TMP_ERR');
1126 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1123 RAISE FND_API.G_EXC_ERROR;
1124 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1125 OZF_UTILITY_PVT.error_message('OZF_RESALE_INIT_INV_TMP_ERR');
1127 END IF;
1128
1129 /*
1130 OZF_SALES_TRANSACTIONS_PVT.Initiate_Inventory_tmp (
1131 p_api_version => 1.0
1132 ,p_init_msg_list => FND_API.G_FALSE
1133 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
1134 ,p_party_id => l_partner_party_id
1135 ,p_start_date => l_report_start_date
1136 ,x_return_status => l_return_status
1137 ,x_msg_count => l_msg_count
1138 ,x_msg_data => l_msg_data
1139 );
1140 */
1141 -- Bug 4380203 Fixing (-)
1142 END IF; -- End of l_direct_order_flag
1143 END IF;
1144
1145 For i in 1..l_order_num_tbl.LAST
1146 LOOP
1147
1148 IF l_order_num_tbl(i) is not NULL AND
1149 l_cust_account_id_tbl(i) is not NULL AND
1150 l_order_date_tbl(i) is not NULL THEN
1151
1152 IF OZF_DEBUG_LOW_ON THEN
1153 ozf_utility_PVT.debug_message('/*--- Processing order for order NUMBER:'||l_order_num_tbl(i)||'---*/');
1154 END IF;
1155 -- Before start process, clean up the data structures if necessary.
1156
1157 --k:=1;
1158 IF l_order_set_tbl.EXISTS(1) THEN
1159 l_order_set_tbl.DELETE;
1160 END IF;
1161 OPEN order_set_csr(l_order_num_tbl(i),
1162 l_cust_account_id_tbl(i),
1163 l_order_date_tbl(i));
1164 FETCH order_set_csr BULK COLLECT INTO l_order_set_tbl;
1165 --LOOP
1166 -- FETCH order_set_csr INTO l_order_set_tbl(k);
1167 -- EXIT WHEN order_set_csr%notfound;
1168 -- k:=k+1;
1169 --END LOOP;
1170 CLOSE order_set_csr;
1171
1172 IF OZF_DEBUG_LOW_ON THEN
1173 ozf_utility_PVT.debug_message('after order set'||l_order_set_tbl.LAST);
1174 END IF;
1175
1176 For J in 1..l_order_set_tbl.LAST LOOP
1177 -- Purge the any error message that might be there.
1178 BEGIN
1179 DELETE FROM ozf_resale_logs
1180 WHERE resale_id = l_order_set_tbl(J).resale_line_int_id
1181 AND resale_id_type = OZF_RESALE_COMMON_PVT.G_ID_TYPE_IFACE;
1182 EXCEPTION
1183 WHEN OTHERS THEN
1184 ozf_utility_pvt.error_message('OZF_DEL_RESALE_LOG_WRG');
1185 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1186 END;
1187
1188 /*
1189 For Bug#9447673 SSD IDSM ER - Restricting the
1190 Validate inventory flow for Direct order only
1191 */
1192 IF l_inventory_tracking = 'T'
1193 AND ( l_direct_order_flag <> 'Y') THEN
1194
1195 -- Only need to Check inventory level
1196 OZF_SALES_TRANSACTIONS_PVT.Validate_Inventory_level (
1197 p_api_version => 1.0
1198 ,p_init_msg_list => FND_API.G_FALSE
1199 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
1200 ,p_line_int_rec => l_order_set_tbl(J)
1201 ,x_valid => l_inventory_level_valid
1202 ,x_return_status => l_return_status
1203 ,x_msg_count => l_msg_count
1204 ,x_msg_data => l_msg_data
1205 );
1206
1207 IF NOT l_inventory_level_valid THEN
1208
1209 IF OZF_DEBUG_LOW_ON THEN
1210 ozf_utility_PVT.debug_message('Did not pass inventory checking');
1211 END IF;
1212 -- Here turn this line to disputed and create a disput code for it.
1213 UPDATE ozf_resale_lines_int
1214 SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DISPUTED,
1215 dispute_code = 'OZF_LT_INVT',
1216 followup_action_code = NVL2(followup_action_code,followup_action_code,'C'),
1217 response_type = 'CA',
1218 response_code = 'N'
1219 WHERE resale_line_int_id = l_order_set_tbl(J).resale_line_int_id;
1220
1221 -- SET Batch as DISPUTED
1222 UPDATE ozf_resale_batches
1223 SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_DISPUTED
1224 WHERE resale_batch_id = p_resale_batch_id;
1225
1226 GOTO END_ORDER_LINE;
1227 ELSE
1228 -- This is a valid tracing line
1229 UPDATE ozf_resale_lines_int
1230 SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED
1231 WHERE resale_line_int_id = l_order_set_tbl(J).resale_line_int_id;
1232 END IF;
1233 ELSE
1234 -- Set line status to Processed if not tracking inventory
1235 UPDATE ozf_resale_lines_int
1236 SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED
1237 WHERE resale_line_int_Id = l_order_set_tbl(j).resale_line_int_id;
1238 END IF;
1239 << END_ORDER_LINE >>
1240 NULL;
1241 END LOOP; -- END LOOP for l_order_set_tbl
1242 IF OZF_DEBUG_LOW_ON THEN
1243 ozf_utility_PVT.debug_message('/*--- Success: Processing order for order NUMBER:'||l_order_num_tbl(i)||'---*/');
1244 END IF;
1245 END IF; -- END if for order_NUMBER, date_ordered not NULL
1246 << END_LOOP >>
1247 NULL;
1248 END LOOP; -- END LOOP for l_order_num_tbl
1249 ELSE
1250 IF OZF_DEBUG_LOW_ON THEN
1251 ozf_utility_PVT.debug_message('/*--- No order to process ---*/');
1252 END IF;
1253 END IF;
1254
1255 -- get data regard this process
1256 OPEN OZF_RESALE_COMMON_PVT.g_disputed_line_count_csr (p_resale_batch_id);
1257 FETCH OZF_RESALE_COMMON_PVT.g_disputed_line_count_csr INTO l_lines_disputed;
1258 CLOSE OZF_RESALE_COMMON_PVT.g_disputed_line_count_csr;
1259
1260 OPEN invalid_line_count_csr(p_resale_batch_id);
1261 FETCH invalid_line_count_csr INTO l_lines_invalid;
1262 CLOSE invalid_line_count_csr;
1263
1264 --Bug# 8414563 fixed by ateotia(+)
1265 OPEN OZF_RESALE_COMMON_PVT.g_duplicated_line_count_csr (p_resale_batch_id);
1266 FETCH OZF_RESALE_COMMON_PVT.g_duplicated_line_count_csr INTO l_lines_duplicated;
1267 CLOSE OZF_RESALE_COMMON_PVT.g_duplicated_line_count_csr;
1268
1269 --IF l_lines_disputed = 0 THEN
1270 IF (l_lines_disputed = 0 AND l_lines_duplicated = 0) THEN
1271 --Bug# 8414563 fixed by ateotia(-)
1272 l_status_code := OZF_RESALE_COMMON_PVT.G_BATCH_PROCESSED;
1273 ELSE
1274 -- batch is in dispute
1275 l_status_code := OZF_RESALE_COMMON_PVT.G_BATCH_DISPUTED;
1276 END IF;
1277
1278 -- Lastly, I will UPDATE the batch
1279 BEGIN
1280 UPDATE ozf_resale_batches_all
1281 SET status_code = l_status_code,
1282 --Bug# 8414563 fixed by ateotia(+)
1283 --lines_disputed = l_lines_disputed,
1284 lines_disputed = l_lines_disputed + l_lines_duplicated,
1285 --Bug# 8414563 fixed by ateotia(-)
1286 lines_invalid = l_lines_invalid
1287 WHERE resale_batch_id = p_resale_batch_id;
1288 EXCEPTION
1289 WHEN OTHERS THEN
1290 ozf_utility_pvt.error_message('OZF_UPD_RESALE_BATCH_WRG');
1291 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1292 END;
1293
1294
1295 -- Debug Message
1296 IF OZF_DEBUG_HIGH_ON THEN
1297 OZF_UTILITY_PVT.debug_message(p_message_text => l_full_name||': End');
1298 END IF;
1299 --Standard call to get message count and if count=1, get the message
1300 FND_MSG_PUB.Count_And_Get (
1301 p_encoded => FND_API.G_FALSE,
1302 p_count => x_msg_count,
1303 p_data => x_msg_data
1304 );
1305
1306 EXCEPTION
1307 WHEN FND_API.G_EXC_ERROR THEN
1308 ROLLBACK TO PROCESS_TRAC_ORDER;
1309 x_return_status := FND_API.G_RET_STS_ERROR;
1310 -- Standard call to get message count and if count=1, get the message
1311 FND_MSG_PUB.Count_And_Get (
1312 p_encoded => FND_API.G_FALSE,
1313 p_count => x_msg_count,
1314 p_data => x_msg_data
1315 );
1316 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1317 ROLLBACK TO PROCESS_TRAC_ORDER;
1318 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1319
1320 -- Standard call to get message count and if count=1, get the message
1321 FND_MSG_PUB.Count_And_Get (
1322 p_encoded => FND_API.G_FALSE,
1323 p_count => x_msg_count,
1324 p_data => x_msg_data
1325 );
1326 WHEN OTHERS THEN
1327 ROLLBACK TO PROCESS_TRAC_ORDER;
1328 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1329
1330 -- Standard call to get message count and if count=1, get the message
1331 FND_MSG_PUB.Count_And_Get (
1332 p_encoded => FND_API.G_FALSE,
1333 p_count => x_msg_count,
1334 p_data => x_msg_data
1335 );
1336 END Process_Order;
1337
1338 END OZF_TRACING_ORDER_PVT;
[Home] [Help]