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