[Home] [Help]
PACKAGE BODY: APPS.OZF_RESALE_PVT
Source
1 PACKAGE BODY OZF_RESALE_PVT AS
2 /* $Header: ozfvrssb.pls 120.6.12000000.1 2007/01/18 08:35:17 appldev ship $ */
3
4 -- Package name : OZF_RESALE_PVT
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OZF_RESALE_PVT';
11 G_FILE_NAME CONSTANT VARCHAR2(30) := 'ozfvssb.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 CURSOR g_org_id_csr(p_id number) IS
17 SELECT org_id
18 FROM ozf_resale_batches_all
19 WHERE resale_batch_id = p_id;
20
21
22 ---------------------------------------------------------------------
23 -- PROCEDURE
24 -- Initiate_Payment
25 --
26 -- PURPOSE
27 -- This procedure to initiate payment process for a batch.
28 --
29 -- PARAMETERS
30 --
31 --
32 -- NOTES
33 ---------------------------------------------------------------------
34 PROCEDURE Initiate_Payment (
35 p_api_version IN NUMBER
36 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
37 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
38 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
39 ,p_resale_batch_id IN NUMBER
40 ,x_return_status OUT NOCOPY VARCHAR2
41 ,x_msg_data OUT NOCOPY VARCHAR2
42 ,x_msg_count OUT NOCOPY NUMBER
43 )
44 IS
45 l_api_name CONSTANT VARCHAR2(30) := 'Initiate_Payment';
46 l_api_version CONSTANT NUMBER := 1.0;
47 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
48 --
49 l_return_status varchar2(30);
50 l_msg_data varchar2(2000);
51 l_msg_count number;
52
53 l_batch_type varchar2(30);
54 l_org_id number;
55 BEGIN
56 -- Standard begin of API savepoint
57 SAVEPOINT INITIATE_PAYMENT;
58 -- Standard call to check for call compatibility.
59 IF NOT FND_API.Compatible_API_Call (
60 l_api_version,
61 p_api_version,
62 l_api_name,
63 G_PKG_NAME)
64 THEN
65 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
66 END IF;
67
68 -- Debug Message
69 IF OZF_DEBUG_HIGH_ON THEN
70 OZF_UTILITY_PVT.debug_message(p_message_text => l_full_name||': Start');
71 END IF;
72
73 --Initialize message list if p_init_msg_list is TRUE.
74 IF FND_API.To_Boolean (p_init_msg_list) THEN
75 FND_MSG_PUB.initialize;
76 END IF;
77
78 -- Initialize API return status to sucess
79 x_return_status := FND_API.G_RET_STS_SUCCESS;
80
81 OPEN g_org_id_csr (p_resale_batch_id);
82 FETCH g_org_id_csr into l_org_id;
83 CLOSE g_org_id_csr;
84
85 IF l_org_id is not null THEN
86 fnd_client_info.set_org_context(to_char(l_org_id));
87 ELSE
88 OZF_UTILITY_PVT.error_message(p_message_name => 'OZF_ORG_ID_NOTFOUND');
89 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
90 END IF;
91
92 OPEN OZF_RESALE_COMMON_PVT.g_batch_type_csr(p_resale_batch_id);
93 FETCH OZF_RESALE_COMMON_PVT.g_batch_type_csr into l_batch_type;
94 CLOSE OZF_RESALE_COMMON_PVT.g_batch_type_csr;
95
96 IF l_batch_type is null THEN
97 OZF_UTILITY_PVT.error_message(p_message_name => 'OZF_RESALE_BATCH_TYPE_NULL');
98 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
99 END IF;
100
101 IF l_batch_type = OZF_RESALE_COMMON_PVT.G_CHARGEBACK THEN
102
103 OZF_CHARGEBACK_PVT.Initiate_Payment(
104 p_api_version => 1
105 ,p_init_msg_list => FND_API.G_FALSE
106 ,p_commit => FND_API.G_FALSE
107 ,p_validation_level=> FND_API.G_VALID_LEVEL_FULL
108 ,p_resale_batch_id => p_resale_batch_id
109 ,x_return_status => l_return_status
110 ,x_msg_data => l_msg_data
111 ,x_msg_count => l_msg_count
112 );
113
114 IF l_return_status = FND_API.g_ret_sts_error THEN
115 OZF_UTILITY_PVT.error_message(p_message_name => 'OZF_RESALE_CHBK_PAYMNT_ERR');
116 RAISE FND_API.g_exc_error;
117 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
118 OZF_UTILITY_PVT.error_message(p_message_name => 'OZF_RESALE_CHBK_PAYMNT_ERR');
119 RAISE FND_API.g_exc_unexpected_error;
120 END IF;
121 ELSIF l_batch_type = OZF_RESALE_COMMON_PVT.G_SPECIAL_PRICING THEN
122
123 OZF_SPECIAL_PRICING_PVT.Initiate_Payment(
124 p_api_version => 1
125 ,p_init_msg_list => FND_API.G_FALSE
126 ,p_commit => FND_API.G_FALSE
127 ,p_validation_level=> FND_API.G_VALID_LEVEL_FULL
128 ,p_resale_batch_id => p_resale_batch_id
129 ,x_return_status => l_return_status
130 ,x_msg_data => l_msg_data
131 ,x_msg_count => l_msg_count
132 );
133
134 IF l_return_status = FND_API.g_ret_sts_error THEN
135 OZF_UTILITY_PVT.error_message(p_message_name => 'OZF_RESALE_SPP_PAYMNT_ERR');
136 RAISE FND_API.g_exc_error;
137 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
138 OZF_UTILITY_PVT.error_message(p_message_name => 'OZF_RESALE_SPP_PAYMNT_ERR');
139 RAISE FND_API.g_exc_unexpected_error;
140 END IF;
141 ELSIF l_batch_type = OZF_RESALE_COMMON_PVT.G_TRACING THEN
142
143 OZF_TRACING_ORDER_PVT.Initiate_Payment(
144 p_api_version => 1
145 ,p_init_msg_list => FND_API.G_FALSE
146 ,p_commit => FND_API.G_FALSE
147 ,p_validation_level=> FND_API.G_VALID_LEVEL_FULL
148 ,p_resale_batch_id => p_resale_batch_id
149 ,x_return_status => l_return_status
150 ,x_msg_data => l_msg_data
151 ,x_msg_count => l_msg_count
152 );
153
154 IF l_return_status = FND_API.g_ret_sts_error THEN
155 OZF_UTILITY_PVT.error_message(p_message_name => 'OZF_RESALE_TRAC_PAYMNT_ERR');
156 RAISE FND_API.g_exc_error;
157 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
158 OZF_UTILITY_PVT.error_message(p_message_name => 'OZF_RESALE_TRAC_PAYMNT_ERR');
159 RAISE FND_API.g_exc_unexpected_error;
160 END IF;
161 END IF;
162
163 -- Debug Message
164 IF OZF_DEBUG_HIGH_ON THEN
165 OZF_UTILITY_PVT.debug_message(p_message_text => l_full_name||': End');
166 END IF;
167 --Standard call to get message count and if count=1, get the message
168 FND_MSG_PUB.Count_And_Get (
169 p_encoded => FND_API.G_FALSE,
170 p_count => x_msg_count,
171 p_data => x_msg_data
172 );
173 x_return_status := l_return_status;
174
175 EXCEPTION
176 WHEN FND_API.G_EXC_ERROR THEN
177 ROLLBACK TO INITIATE_PAYMENT;
178 x_return_status := FND_API.G_RET_STS_ERROR;
179 -- Standard call to get message count and if count=1, get the message
180 FND_MSG_PUB.Count_And_Get (
181 p_encoded => FND_API.G_FALSE,
182 p_count => x_msg_count,
183 p_data => x_msg_data
184 );
185 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
186 ROLLBACK TO INITIATE_PAYMENT;
187 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
188 -- Standard call to get message count and if count=1, get the message
189 FND_MSG_PUB.Count_And_Get (
190 p_encoded => FND_API.G_FALSE,
191 p_count => x_msg_count,
192 p_data => x_msg_data
193 );
194 WHEN OTHERS THEN
195 ROLLBACK TO INITIATE_PAYMENT;
196 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
197 -- Standard call to get message count and if count=1, get the message
198 FND_MSG_PUB.Count_And_Get (
199 p_encoded => FND_API.G_FALSE,
200 p_count => x_msg_count,
201 p_data => x_msg_data
202 );
203 END Initiate_Payment;
204
205 ---------------------------------------------------------------------
206 -- PROCEDURE
207 -- Initiate_Payment_WFL
208 --
209 -- PURPOSE
210 -- This procedure is called by a workflow to allow user to start the data process
211 --
212 -- PARAMETERS
213 --
214 --
215 -- NOTES
216 ---------------------------------------------------------------------
217 PROCEDURE Initiate_Payment_WFL(
218 itemtype in varchar2,
219 itemkey in varchar2,
220 actid in number,
221 funcmode in varchar2,
222 resultout in OUT NOCOPY varchar2)
223 IS
224 l_api_name CONSTANT VARCHAR2(30) := 'Initiate_Payment_WFL';
225 l_api_version CONSTANT NUMBER := 1.0;
226 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
227 --
228 l_resultout VARCHAR2(30);
229 l_resale_batch_id NUMBER;
230 l_return_status VARCHAR2(1);
231 l_msg_count NUMBER;
232 l_msg_data VARCHAR2(2000);
233
234 l_error_msg varchar2(4000);
235 BEGIN
236 IF OZF_DEBUG_HIGH_ON THEN
237 OZF_UTILITY_PVT.debug_message(p_message_text => l_full_name||': Start');
238 END IF;
239
240 IF (funcmode = 'RUN') THEN
241 l_resale_batch_id := wf_engine.GetItemAttrText(itemtype => itemtype,
242 itemkey => itemkey,
243 aname => 'RESALE_BATCH_ID');
244 Initiate_Payment (
245 p_api_version => 1.0
246 ,p_init_msg_list => FND_API.G_FALSE
247 ,p_commit => FND_API.G_FALSE
248 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
249 ,p_resale_batch_id => l_resale_batch_id
250 ,x_return_status => l_return_status
251 ,x_msg_data => l_msg_data
252 ,x_msg_count => l_msg_count
253 );
254 IF OZF_DEBUG_LOW_ON THEN
255 OZF_UTILITY_PVT.debug_message('subscribe process iface is complete ');
256 END IF;
257 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
258 RAISE FND_API.G_EXC_ERROR;
259 END IF;
260 l_resultout := 'COMPLETE:Y';
261 ELSIF (funcmode = 'CANCEL') THEN
262 l_resultout := 'COMPLETE:Y';
263
264 ELSIF (funcmode in ('RESPOND', 'FORWARD', 'TRANSFER')) THEN
265 l_resultout := 'COMPLETE:Y';
266
267 ELSIF (funcmode = 'TIMEOUT') THEN
268 l_resultout := 'COMPLETE:Y';
269 END IF;
270 resultout := l_resultout;
271 EXCEPTION
272 WHEN FND_API.G_EXC_ERROR THEN
273 FND_MSG_PUB.count_and_get (
274 p_encoded => FND_API.g_false
275 ,p_count => l_msg_count
276 ,p_data => l_msg_data
277 );
278
279 WF_CORE.context(
280 'OZF_RESALE_PVT'
281 ,'INITIATE_PAYMENT_WFL'
282 ,itemtype
283 ,itemkey
284 ,actid
285 ,l_error_msg
286 );
287 resultout := 'COMPLETE:N';
288 RETURN;
289
290 WHEN OTHERS THEN
291 fnd_msg_pub.Count_And_Get(
292 p_encoded => FND_API.G_TRUE
293 ,p_count => l_msg_count
294 ,p_data => l_msg_data);
295 wf_core.context(G_PKG_NAME, l_api_name,l_msg_data);
296 raise;
297 END Initiate_Payment_WFL;
298
299 ---------------------------------------------------------------------
300 -- PROCEDURE
301 -- Process_Iface
302 --
303 -- PURPOSE
304 -- This procedure to initiate data process of records in resales table.
305 --
306 -- PARAMETERS
307 --
308 --
309 -- NOTES
310 ---------------------------------------------------------------------
311 PROCEDURE Process_Iface (
312 p_api_version IN NUMBER
313 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
314 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
315 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
316 ,p_resale_batch_id IN NUMBER
317 ,x_return_status OUT NOCOPY VARCHAR2
318 ,x_msg_data OUT NOCOPY VARCHAR2
319 ,x_msg_count OUT NOCOPY NUMBER
320 )
321 IS
322 l_api_name CONSTANT VARCHAR2(30) := 'Process_Iface';
323 l_api_version CONSTANT NUMBER := 1.0;
324 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
325 --
326 l_return_status varchar2(30);
327 l_msg_data varchar2(2000);
328 l_msg_count number;
329 --
330 l_batch_status VARCHAR2(30);
331 l_batch_count number;
332
333 l_batch_type varchar2(30);
334 i number;
335 l_open_lines_tbl OZF_RESALE_COMMON_PVT.interface_lines_tbl_type;
336 --
337 l_disputed_count number;
338 l_duplicate_count number;
339 l_dup_line_id number;
340 l_dup_adjustment_id number;
341 l_reprocessing boolean;
342 l_org_id number;
343 --
344 /*
345 CURSOR open_lines_csr(p_id IN NUMBER) IS
346 SELECT *
347 FROM ozf_resale_lines_int
348 WHERE status_code =OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_OPEN
349 AND resale_batch_id = p_id;
350 */
351
352 CURSOR batch_count_csr(pc_batch_id NUMBER) IS
353 SELECT NVL(batch_count,0)
354 FROM ozf_resale_batches
355 WHERE resale_batch_id = pc_batch_id;
356
357 CURSOR duplicate_count_csr(p_id NUMBER) IS
358 SELECT count(1)
359 FROM ozf_resale_lines_int
360 WHERE status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DUPLICATED
361 AND resale_batch_id = p_id;
362
363 CURSOR disputed_count_csr(p_id number) IS
364 SELECT count(1)
365 FROM ozf_resale_lines_int
366 WHERE status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DISPUTED
367 AND resale_batch_id = p_id;
368 --
369 BEGIN
370 -- Standard begin of API savepoint
371 SAVEPOINT PROCESS_IFACE;
372 -- Standard call to check for call compatibility.
373 IF NOT FND_API.Compatible_API_Call (
374 l_api_version,
375 p_api_version,
376 l_api_name,
377 G_PKG_NAME)
378 THEN
379 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
380 END IF;
381
382 --Initialize message list if p_init_msg_list is TRUE.
383 IF FND_API.To_Boolean (p_init_msg_list) THEN
384 FND_MSG_PUB.initialize;
385 END IF;
386
387 -- Debug Message
388 IF OZF_DEBUG_HIGH_ON THEN
389 OZF_UTILITY_PVT.debug_message(p_message_text => l_full_name||': Start');
390 END IF;
391
392 -- Initialize API return status to sucess
393 x_return_status := FND_API.G_RET_STS_SUCCESS;
394
395 OPEN g_org_id_csr (p_resale_batch_id);
396 FETCH g_org_id_csr into l_org_id;
397 CLOSE g_org_id_csr;
398
399 IF l_org_id is not null THEN
400 fnd_client_info.set_org_context(to_char(l_org_id));
401 ELSE
402 OZF_UTILITY_PVT.error_message(p_message_name => 'OZF_ORG_ID_NOTFOUND');
403 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
404 END IF;
405
406 OPEN OZF_RESALE_COMMON_PVT.g_batch_type_csr(p_resale_batch_id);
407 FETCH OZF_RESALE_COMMON_PVT.g_batch_type_csr into l_batch_type;
408 CLOSE OZF_RESALE_COMMON_PVT.g_batch_type_csr;
409
410 -- Make sure that batch type is not null
411 IF l_batch_type is null THEN
412 OZF_UTILITY_PVT.error_message(p_message_name => 'OZF_RESALE_BATCH_TYPE_NULL');
413 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
414 END IF;
415
416 -- Remove any log associated with this batch
417 BEGIN
418 DELETE FROM ozf_resale_logs_all
419 WHERE resale_id_type = 'BATCH'
420 AND resale_id = p_resale_batch_id;
421 EXCEPTION
422 WHEN OTHERS THEN
423 OZF_UTILITY_PVT.error_message('OZF_RESALE_DEL_LOG_ERR');
424 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
425 END;
426
427 -- Varify the line information for this batch
428 OZF_RESALE_COMMON_PVT.Validate_batch(
429 p_api_version => 1
430 ,p_init_msg_list => FND_API.G_FALSE
431 ,p_commit => FND_API.G_FALSE
432 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
433 ,p_resale_batch_id => p_resale_batch_id
434 ,x_batch_status => l_batch_status
435 ,x_return_status => l_return_status
436 ,x_msg_data => l_msg_data
437 ,x_msg_count => l_msg_count
438 );
439
440 IF l_return_status<> FND_API.G_RET_STS_SUCCESS THEN
441 OZF_UTILITY_PVT.error_message(p_message_name => 'OZF_RESALE_BATCH_VALIDATE_ERR');
442 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
443 END IF;
444
445 -- set disputed_code to null for the lines to be processed.
446 update ozf_resale_lines_int
447 set dispute_code = null
448 where resale_batch_id = p_resale_batch_id
449 and status_code= OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_OPEN;
450
451 -- update tracing order lines to processed for this order to be processed
452 update ozf_resale_lines_int
453 set status_code= OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED
454 where resale_batch_id = p_resale_batch_id
455 and status_code= OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_OPEN
456 and tracing_flag = 'T';
457
458 /*
459 i := 1;
460 OPEN open_lines_csr(p_resale_batch_id);
461 LOOP
462 exit when open_lines_csr%NOTFOUND;
463 FETCH open_lines_csr into l_open_lines_tbl(i);
464 i:= i+1;
465 END LOOP;
466 CLOSE open_lines_csr;
467 */
468 OZF_RESALE_COMMON_PVT.Update_Duplicates (
469 p_api_version => 1.0,
470 p_init_msg_list => FND_API.G_FALSE,
471 p_commit => FND_API.G_FALSE,
472 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
473 p_resale_batch_id => p_resale_batch_id,
474 p_resale_batch_type => l_batch_type,
475 p_batch_status => l_batch_status,
476 x_batch_status => l_batch_status,
477 x_return_status => l_return_status,
478 x_msg_count => l_msg_count,
479 x_msg_data => l_msg_data);
480 --
481 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
482 RAISE FND_API.G_EXC_ERROR;
483 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
484 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
485 END IF;
486
487 IF l_batch_status = OZF_RESALE_COMMON_PVT.G_BATCH_REJECTED THEN
488 IF OZF_DEBUG_LOW_ON THEN
489 OZF_UTILITY_PVT.debug_message('OZF_RESALE_REJECTED');
490 END IF;
491 ELSE
492 -- Varify the line information for this batch
493 OZF_RESALE_COMMON_PVT.Validate_Order_Record(
494 p_api_version => 1
495 ,p_init_msg_list => FND_API.G_FALSE
496 ,p_commit => FND_API.G_FALSE
497 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
498 ,p_resale_batch_id => p_resale_batch_id
499 ,x_return_status => l_return_status
500 ,x_msg_data => l_msg_data
501 ,x_msg_count => l_msg_count
502 );
503 IF l_return_status<> FND_API.G_RET_STS_SUCCESS THEN
504 OZF_UTILITY_PVT.error_message(p_message_name => 'OZF_RESALE_VALIDATE_ERR');
505 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
506 END IF;
507
508 -- Call different validation here.
509 -- calling chargeback validation for this batch
510 IF l_batch_type = OZF_RESALE_COMMON_PVT.G_TP_ACCRUAL THEN
511 OZF_TP_ACCRUAL_PVT.Validate_Order_Record(
512 p_api_version => 1
513 ,p_init_msg_list => FND_API.G_FALSE
514 ,p_commit => FND_API.G_FALSE
515 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
516 ,p_resale_batch_id => p_resale_batch_id
517 ,p_caller_type => OZF_TP_ACCRUAL_PVT.G_IFACE_CALLER
518 ,x_return_status => l_return_status
519 ,x_msg_data => l_msg_data
520 ,x_msg_count => l_msg_count
521 );
522 IF l_return_status<> FND_API.G_RET_STS_SUCCESS THEN
523 OZF_UTILITY_PVT.error_message(p_message_name => 'OZF_RESALE_TP_VALIDATE_ERR');
524 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
525 END IF;
526 ELSIF l_batch_type = OZF_RESALE_COMMON_PVT.G_CHARGEBACK THEN
527 OZF_CHARGEBACK_PVT.Validate_Order_Record(
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_resale_batch_id => p_resale_batch_id
533 ,x_return_status => l_return_status
534 ,x_msg_data => l_msg_data
535 ,x_msg_count => l_msg_count
536 );
537 IF l_return_status<> FND_API.G_RET_STS_SUCCESS THEN
538 OZF_UTILITY_PVT.error_message(p_message_name => 'OZF_RESALE_CHBK_VALIDATE_ERR');
539 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
540 END IF;
541 ELSIF l_batch_type = OZF_RESALE_COMMON_PVT.G_SPECIAL_PRICING THEN
542 OZF_SPECIAL_PRICING_PVT.Validate_Order_Record(
543 p_api_version => 1
544 ,p_init_msg_list => FND_API.G_FALSE
545 ,p_commit => FND_API.G_FALSE
546 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
547 ,p_resale_batch_id => p_resale_batch_id
548 ,x_return_status => l_return_status
549 ,x_msg_data => l_msg_data
550 ,x_msg_count => l_msg_count
551 );
552 IF l_return_status<> FND_API.G_RET_STS_SUCCESS THEN
553 OZF_UTILITY_PVT.error_message(p_message_name => 'OZF_RESALE_SPP_VALIDATE_ERR');
554 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
555 END IF;
556 ELSIF l_batch_type = OZF_RESALE_COMMON_PVT.G_TRACING THEN
557 OZF_TRACING_ORDER_PVT.Validate_Order_Record(
558 p_api_version => 1
559 ,p_init_msg_list => FND_API.G_FALSE
560 ,p_commit => FND_API.G_FALSE
561 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
562 ,p_resale_batch_id => p_resale_batch_id
563 ,x_return_status => l_return_status
564 ,x_msg_data => l_msg_data
565 ,x_msg_count => l_msg_count
566 );
567 IF l_return_status<> FND_API.G_RET_STS_SUCCESS THEN
568 OZF_UTILITY_PVT.error_message(p_message_name => 'OZF_RESALE_TRAC_VALIDATE_ERR');
569 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
570 END IF;
571 ELSE
572 OZF_UTILITY_PVT.error_message(p_message_name => 'OZF_RESALE_WNG_BATCH_TYPE');
573 RAISE FND_API.g_exc_unexpected_error;
574 END IF;
575
576 /*
577 OPEN duplicate_count_csr (p_resale_batch_id);
578 FETCH duplicate_count_csr into l_duplicate_count;
579 CLOSE duplicate_count_csr;
580
581 OPEN batch_count_csr (p_resale_batch_id);
582 FETCH batch_count_csr into l_batch_count;
583 CLOSE batch_count_csr;
584
585 IF l_duplicate_count = l_batch_count THEN
586 update ozf_resale_batches_all
587 set status_code = OZF_RESALE_COMMON_PVT.G_BATCH_REJECTED
588 where resale_batch_id = p_resale_batch_id;
589 ELSE
590 */
591 IF l_batch_type = OZF_RESALE_COMMON_PVT.G_TP_ACCRUAL THEN
592 OZF_TP_ACCRUAL_PVT.Process_Order(
593 p_api_version => 1
594 ,p_init_msg_list => FND_API.G_FALSE
595 ,p_commit => FND_API.G_FALSE
596 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
597 ,p_resale_batch_id => p_resale_batch_id
598 ,x_return_status => l_return_status
599 ,x_msg_data => l_msg_data
600 ,x_msg_count => l_msg_count
601 );
602
603 IF l_return_status<> FND_API.G_RET_STS_SUCCESS THEN
604 OZF_UTILITY_PVT.error_message(p_message_name => 'OZF_RESALE_TP_PROC_ERR');
605 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
606 END IF;
607 ELSIF l_batch_type = OZF_RESALE_COMMON_PVT.G_CHARGEBACK THEN
608 OZF_CHARGEBACK_PVT.Process_Order(
609 p_api_version => 1
610 ,p_init_msg_list => FND_API.G_FALSE
611 ,p_commit => FND_API.G_FALSE
612 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
613 ,p_resale_batch_id => p_resale_batch_id
614 ,x_return_status => l_return_status
615 ,x_msg_data => l_msg_data
616 ,x_msg_count => l_msg_count
617 );
618 IF l_return_status<> FND_API.G_RET_STS_SUCCESS THEN
619 OZF_UTILITY_PVT.error_message(p_message_name => 'OZF_RESALE_CHBK_PROC_ERR');
620 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
621 END IF;
622 ELSIF l_batch_type = OZF_RESALE_COMMON_PVT.G_SPECIAL_PRICING THEN
623 OZF_SPECIAL_PRICING_PVT.Process_Order(
624 p_api_version => 1
625 ,p_init_msg_list => FND_API.G_FALSE
626 ,p_commit => FND_API.G_FALSE
627 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
628 ,p_resale_batch_id => p_resale_batch_id
629 ,x_return_status => l_return_status
630 ,x_msg_data => l_msg_data
631 ,x_msg_count => l_msg_count
632 );
633 IF l_return_status<> FND_API.G_RET_STS_SUCCESS THEN
634 OZF_UTILITY_PVT.error_message(p_message_name => 'OZF_RESALE_SPP_PROC_ERR');
635 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
636 END IF;
637 ELSIF l_batch_type = OZF_RESALE_COMMON_PVT.G_TRACING THEN
638 OZF_TRACING_ORDER_PVT.Process_Order(
639 p_api_version => 1
640 ,p_init_msg_list => FND_API.G_FALSE
641 ,p_commit => FND_API.G_FALSE
642 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
643 ,p_resale_batch_id => p_resale_batch_id
644 ,x_return_status => l_return_status
645 ,x_msg_data => l_msg_data
646 ,x_msg_count => l_msg_count
647 );
648
649 IF l_return_status<> FND_API.G_RET_STS_SUCCESS THEN
650 OZF_UTILITY_PVT.error_message(p_message_name => 'OZF_RESALE_TRAC_PROC_ERR');
651 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
652 END IF;
653 ELSE
654 OZF_UTILITY_PVT.error_message(p_message_name => 'OZF_RESALE_WNG_BATCH_TYPE');
655 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
656 END IF;
657 END IF; -- IF status is not rejected.
658
659 -- Debug Message
660 IF OZF_DEBUG_HIGH_ON THEN
661 OZF_UTILITY_PVT.debug_message(p_message_text => l_full_name||': End');
662 END IF;
663 --Standard call to get message count and if count=1, get the message
664 FND_MSG_PUB.Count_And_Get (
665 p_encoded => FND_API.G_FALSE,
666 p_count => x_msg_count,
667 p_data => x_msg_data
668 );
669
670 EXCEPTION
671 WHEN FND_API.G_EXC_ERROR THEN
672 x_return_status := FND_API.G_RET_STS_ERROR;
673 -- Standard call to get message count and if count=1, get the message
674 FND_MSG_PUB.Count_And_Get (
675 p_encoded => FND_API.G_FALSE,
676 p_count => x_msg_count,
677 p_data => x_msg_data
678 );
679 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
680 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
681 -- Standard call to get message count and if count=1, get the message
682 FND_MSG_PUB.Count_And_Get (
683 p_encoded => FND_API.G_FALSE,
684 p_count => x_msg_count,
685 p_data => x_msg_data
686 );
687 WHEN OTHERS THEN
688 ROLLBACK TO PROCESS_IFACE;
689 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
690 -- Standard call to get message count and if count=1, get the message
691 FND_MSG_PUB.Count_And_Get (
692 p_encoded => FND_API.G_FALSE,
693 p_count => x_msg_count,
694 p_data => x_msg_data
695 );
696 END Process_Iface;
697
698 ---------------------------------------------------------------------
699 -- PROCEDURE
700 -- Process_Iface_WFL
701 --
702 -- PURPOSE
703 -- This procedure is called by a workflow to allow user to start the data process
704 --
705 -- PARAMETERS
706 --
707 --
708 -- NOTES
709 ---------------------------------------------------------------------
710 PROCEDURE Process_Iface_WFL(
711 itemtype in varchar2,
712 itemkey in varchar2,
713 actid in number,
714 funcmode in varchar2,
715 resultout in OUT NOCOPY varchar2)
716 IS
717 l_api_name CONSTANT VARCHAR2(30) := 'Process_Iface_WFL';
718 l_api_version CONSTANT NUMBER := 1.0;
719 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
720 --
721 l_resultout VARCHAR2(30);
722 l_resale_batch_id NUMBER;
723 l_return_status VARCHAR2(1);
724 l_msg_count NUMBER;
725 l_msg_data VARCHAR2(2000);
726
727 l_error_msg varchar2(4000);
728 BEGIN
729 IF OZF_DEBUG_HIGH_ON THEN
730 OZF_UTILITY_PVT.debug_message(p_message_text => l_full_name||': Start');
731 END IF;
732
733 IF (funcmode = 'RUN') THEN
734 l_resale_batch_id := wf_engine.GetItemAttrText(itemtype => itemtype,
735 itemkey => itemkey,
736 aname => 'RESALE_BATCH_ID');
737 Process_iface (
738 p_api_version => 1.0
739 ,p_init_msg_list => FND_API.G_FALSE
740 ,p_commit => FND_API.G_FALSE
741 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
742 ,p_resale_batch_id => l_resale_batch_id
743 ,x_return_status => l_return_status
744 ,x_msg_data => l_msg_data
745 ,x_msg_count => l_msg_count
746 );
747 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
748 raise FND_API.G_EXC_ERROR;
749 END IF;
750 l_resultout := 'COMPLETE:Y';
751 ELSIF (funcmode = 'CANCEL') THEN
752 l_resultout := 'COMPLETE:Y';
753
754 ELSIF (funcmode in ('RESPOND', 'FORWARD', 'TRANSFER')) THEN
755 l_resultout := 'COMPLETE:Y';
756
757 ELSIF (funcmode = 'TIMEOUT') THEN
758 l_resultout := 'COMPLETE:Y';
759 END IF;
760 resultout := l_resultout;
761 EXCEPTION
762 WHEN FND_API.G_EXC_ERROR THEN
763 FND_MSG_PUB.count_and_get (
764 p_encoded => FND_API.g_false
765 ,p_count => l_msg_count
766 ,p_data => l_msg_data
767 );
768 WF_CORE.context(
769 'OZF_RESALE_PVT'
770 ,'PROCESS_IFACE_WFL'
771 ,itemtype
772 ,itemkey
773 ,actid
774 ,l_error_msg
775 );
776 resultout := 'COMPLETE:N';
777 RETURN;
778
779 WHEN OTHERS THEN
780 fnd_msg_pub.Count_And_Get(
781 p_encoded => FND_API.G_TRUE
782 ,p_count => l_msg_count
783 ,p_data => l_msg_data);
784
785 wf_core.context(G_PKG_NAME, l_api_name,l_msg_data);
786 raise;
787 END Process_Iface_WFL;
788
789 ---------------------------------------------------------------------
790 -- PROCEDURE
791 -- Process_Resale
792 --
793 -- PURPOSE
794 -- This procedure to initiate data process of records in resales table.
795 --
796 -- AT the point, we only suppor the process of Third party accrual for resale data.
797 -- PARAMETERS
798 --
799 --
800 -- NOTES
801 ---------------------------------------------------------------------
802 PROCEDURE Process_Resale (
803 p_api_version IN NUMBER
804 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
805 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
806 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
807 ,p_resale_batch_id IN NUMBER
808 ,x_return_status OUT NOCOPY VARCHAR2
809 ,x_msg_data OUT NOCOPY VARCHAR2
810 ,x_msg_count OUT NOCOPY NUMBER
811 )
812 IS
813 l_api_name CONSTANT VARCHAR2(30) := 'Process_Resale';
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 l_batch_type varchar2(30);
822
823 l_batch_number VARCHAR2(30);
824
825 CURSOR csr_batch_number(cv_batch_id IN NUMBER) IS
826 SELECT batch_number
827 FROM ozf_resale_batches
828 WHERE resale_batch_id = cv_batch_id;
829
830 BEGIN
831
832 -- Standard begin of API savepoint
833 SAVEPOINT PROCESS_RESALE;
834 -- Standard call to check for call compatibility.
835 IF NOT FND_API.Compatible_API_Call (
836 l_api_version,
837 p_api_version,
838 l_api_name,
839 G_PKG_NAME)
840 THEN
841 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
842 END IF;
843
844 --Initialize message list if p_init_msg_list is TRUE.
845 IF FND_API.To_Boolean (p_init_msg_list) THEN
846 FND_MSG_PUB.initialize;
847 END IF;
848
849 -- Initialize API return status to sucess
850 x_return_status := FND_API.G_RET_STS_SUCCESS;
851
852
853 -- Debug Message
854 IF OZF_DEBUG_HIGH_ON THEN
855 OZF_UTILITY_PVT.debug_message(p_message_text => l_full_name||': Start');
856 END IF;
857
858 IF p_resale_batch_id is null THEN
859 OZF_UTILITY_PVT.error_message(p_message_name => 'OZF_RESALE_BATCH_ID_NULL');
860 FND_MESSAGE.set_name('OZF', 'OZF_RESALE_BATCH_ID_NULL');
861 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Error : '||FND_MESSAGE.get);
862 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
863 GOTO PROC_RESALE_END;
864 END IF;
865
866 OPEN csr_batch_number(p_resale_batch_id);
867 FETCH csr_batch_number INTO l_batch_number;
868 CLOSE csr_batch_number;
869
870 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Batch Number : '||l_batch_number);
871 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' ');
872
873 OPEN OZF_RESALE_COMMON_PVT.g_batch_type_csr(p_resale_batch_id);
874 FETCH OZF_RESALE_COMMON_PVT.g_batch_type_csr into l_batch_type;
875 CLOSE OZF_RESALE_COMMON_PVT.g_batch_type_csr;
876
877 IF l_batch_type is null THEN
878 OZF_UTILITY_PVT.error_message(p_message_name => 'OZF_RESALE_BATCH_TYPE_NULL');
879 FND_MESSAGE.set_name('OZF', 'OZF_RESALE_BATCH_TYPE_NULL');
880 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' Error : '||FND_MESSAGE.get);
881 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
882 GOTO PROC_RESALE_END;
883 END IF;
884
885 /*
886 IF l_batch_type = OZF_RESALE_COMMON_PVT.G_TP_ACCRUAL THEN
887 OZF_UTILITY_PVT.error_message(p_message_name => 'OZF_RESALE_DUP_PROCESS_TP');
888 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
889 END IF;
890 */
891
892 -- Only third party accrual is available for resale processing.
893 OZF_TP_ACCRUAL_PVT.process_resale(
894 p_api_version => 1
895 ,p_init_msg_list => FND_API.G_FALSE
896 ,p_commit => FND_API.G_FALSE
897 ,p_validation_level=> FND_API.G_VALID_LEVEL_FULL
898 ,p_resale_batch_id => p_resale_batch_id
899 ,x_return_status => l_return_status
900 ,x_msg_data => l_msg_data
901 ,x_msg_count => l_msg_count
902 );
903 IF l_return_status = FND_API.g_ret_sts_error THEN
904 RAISE FND_API.g_exc_error;
905 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
906 RAISE FND_API.g_exc_unexpected_error;
907 END IF;
908
909 <<PROC_RESALE_END>>
910
911 -- Debug Message
912 IF OZF_DEBUG_HIGH_ON THEN
913 OZF_UTILITY_PVT.debug_message(l_full_name||': End');
914 END IF;
915 --Standard call to get message count and if count=1, get the message
916 FND_MSG_PUB.Count_And_Get (
917 p_encoded => FND_API.G_FALSE,
918 p_count => x_msg_count,
919 p_data => x_msg_data
920 );
921 EXCEPTION
922 WHEN FND_API.G_EXC_ERROR THEN
923 x_return_status := FND_API.G_RET_STS_ERROR;
924 -- Standard call to get message count and if count=1, get the message
925 FND_MSG_PUB.Count_And_Get (
926 p_encoded => FND_API.G_FALSE,
927 p_count => x_msg_count,
928 p_data => x_msg_data
929 );
930 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
931 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
932 -- Standard call to get message count and if count=1, get the message
933 FND_MSG_PUB.Count_And_Get (
934 p_encoded => FND_API.G_FALSE,
935 p_count => x_msg_count,
936 p_data => x_msg_data
937 );
938 WHEN OTHERS THEN
939 ROLLBACK TO PROCESS_RESALE;
940 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
941 -- Standard call to get message count and if count=1, get the message
942 FND_MSG_PUB.Count_And_Get (
943 p_encoded => FND_API.G_FALSE,
944 p_count => x_msg_count,
945 p_data => x_msg_data
946 );
947 END Process_Resale;
948
949 ---------------------------------------------------------------------
950 -- PROCEDURE
951 -- Process_Resale
952 --
953 -- PURPOSE
954 -- This procedure to initiate data process of records in resales table.
955 --
956 -- PARAMETERS
957 --
958 --
959 -- NOTES
960 ---------------------------------------------------------------------
961 PROCEDURE Process_Resale (
962 p_api_version IN NUMBER
963 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
964 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
965 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
966 ,p_start_date IN DATE
967 ,p_end_date IN DATE
968 ,p_partner_cust_account_id IN NUMBER
969 ,x_return_status OUT NOCOPY VARCHAR2
970 ,x_msg_data OUT NOCOPY VARCHAR2
971 ,x_msg_count OUT NOCOPY NUMBER
972 )
973 IS
974 l_api_name CONSTANT VARCHAR2(30) := 'Process_Resale';
975 l_api_version CONSTANT NUMBER := 1.0;
976 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
977 --
978 l_return_status varchar2(30);
979 l_msg_data varchar2(2000);
980 l_msg_count number;
981
982 l_start_date date:= p_start_date;
983 l_end_date date:= p_end_date;
984
985 CURSOR range_without_customer_csr(p_start_date date, p_end_date date) is
986 SELECT resale_batch_id
987 FROM ozf_resale_batches
988 WHERE report_start_date >= p_start_date
989 AND report_end_date <= p_end_date
990 AND status_code = 'CLOSED';
991
992 CURSOR range_with_customer_csr(p_start_date date, p_end_date date, p_cust_account_id NUMBER) is
993 SELECT resale_batch_id
994 FROM ozf_resale_batches
995 WHERE report_start_date >= p_start_date
996 AND report_end_date <= p_end_date
997 AND partner_cust_account_id = p_cust_account_id
998 AND status_code = 'CLOSED';
999
1000 CURSOR csr_customer_batches(cv_cust_account_id IN NUMBER) IS
1001 SELECT resale_batch_id
1002 FROM ozf_resale_batches
1003 WHERE partner_cust_account_id = cv_cust_account_id
1004 AND status_code = 'CLOSED';
1005
1006
1007 TYPE batch_id_tbl_type is table of NUMBER index by binary_integer;
1008 l_batch_id_tbl batch_id_tbl_type;
1009
1010 i number;
1011 BEGIN
1012 -- Standard begin of API savepoint
1013 SAVEPOINT PROCESS_RESALE_ALL;
1014 -- Standard call to check for call compatibility.
1015 IF NOT FND_API.Compatible_API_Call (
1016 l_api_version,
1017 p_api_version,
1018 l_api_name,
1019 G_PKG_NAME)
1020 THEN
1021 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1022 END IF;
1023
1024 --Initialize message list if p_init_msg_list is TRUE.
1025 IF FND_API.To_Boolean (p_init_msg_list) THEN
1026 FND_MSG_PUB.initialize;
1027 END IF;
1028
1029 -- Initialize API return status to sucess
1030 x_return_status := FND_API.G_RET_STS_SUCCESS;
1031
1032 -- Debug Message
1033 IF OZF_DEBUG_HIGH_ON THEN
1034 OZF_UTILITY_PVT.debug_message(l_full_name||': Start');
1035 END IF;
1036
1037 -- Bug 4518607 (+)
1038 /*
1039 IF l_start_date is NULL THEN
1040 l_start_date := sysdate;
1041 END IF;
1042
1043 IF l_end_date is NULL THEN
1044 l_end_date := sysdate;
1045 END IF;
1046 */
1047
1048 IF l_start_date IS NOT NULL AND
1049 l_end_date IS NULL THEN
1050 l_end_date := SYSDATE;
1051 END IF;
1052
1053 IF l_end_date IS NOT NULL AND
1054 l_start_date IS NULL THEN
1055 l_start_date := SYSDATE;
1056 END IF;
1057
1058 IF l_start_date IS NOT NULL AND
1059 l_end_date IS NOT NULL AND
1060 l_start_date > l_end_date THEN
1061 ozf_utility_pvt.error_message('OZF_RESALE_WNG_DATE_RANGE');
1062 FND_MESSAGE.set_name('OZF', 'OZF_RESALE_WNG_DATE_RANGE');
1063 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Error : '||FND_MESSAGE.get);
1064 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1065 GOTO PROC_RESALE_2_END;
1066 END IF;
1067 -- Bug 4518607 (-)
1068
1069 IF p_partner_cust_account_id IS NULL THEN
1070 -- Bug 4518607 (+)
1071 IF l_start_date IS NOT NULL AND
1072 l_end_date IS NOT NULL THEN
1073 OPEN range_without_customer_csr(l_start_date, l_end_date);
1074 FETCH range_without_customer_csr BULK COLLECT INTO l_batch_id_tbl;
1075 CLOSE range_without_customer_csr;
1076 END IF;
1077 -- Bug 4518607 (-)
1078 ELSE
1079 -- Bug 4518607 (+)
1080 IF l_start_date IS NULL THEN
1081 OPEN csr_customer_batches(p_partner_cust_account_id);
1082 FETCH csr_customer_batches BULK COLLECT INTO l_batch_id_tbl;
1083 CLOSE csr_customer_batches;
1084 ELSIF l_start_date IS NOT NULL AND
1085 l_end_date IS NOT NULL THEN
1086 -- Bug 4518607 (-)
1087 OPEN range_with_customer_csr(l_start_date, l_end_date, p_partner_cust_account_id);
1088 FETCH range_with_customer_csr BULK COLLECT INTO l_batch_id_tbl;
1089 CLOSE range_with_customer_csr;
1090 END IF;
1091 END IF;
1092
1093 i:= 1;
1094
1095 IF l_batch_id_tbl.exists(1) THEN
1096 FOR j in 1..l_batch_id_tbl.LAST LOOP
1097 process_resale(
1098 p_api_version => 1
1099 ,p_init_msg_list => FND_API.G_FALSE
1100 ,p_commit => FND_API.G_FALSE
1101 ,p_validation_level=> FND_API.G_VALID_LEVEL_FULL
1102 ,p_resale_batch_id => l_batch_id_tbl(j)
1103 ,x_return_status => l_return_status
1104 ,x_msg_data => l_msg_data
1105 ,x_msg_count => l_msg_count
1106 );
1107 IF l_return_status = FND_API.g_ret_sts_error THEN
1108 ozf_utility_pvt.error_message('OZF_PROC_RESALE_ERR');
1109 RAISE FND_API.G_EXC_ERROR;
1110 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1111 ozf_utility_pvt.error_message('OZF_PROC_RESALE_ERR');
1112 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1113 END IF;
1114 END LOOP;
1115 ELSE
1116 IF OZF_DEBUG_LOW_ON THEN
1117 OZF_UTILITY_PVT.debug_message('No batch is specified.');
1118 END IF;
1119 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'No batches found to be process.');
1120 END IF;
1121
1122
1123 <<PROC_RESALE_2_END>>
1124
1125 -- Debug Message
1126 IF OZF_DEBUG_HIGH_ON THEN
1127 OZF_UTILITY_PVT.debug_message(l_full_name||': End');
1128 END IF;
1129 --Standard call to get message count and if count=1, get the message
1130 FND_MSG_PUB.Count_And_Get (
1131 p_encoded => FND_API.G_FALSE,
1132 p_count => x_msg_count,
1133 p_data => x_msg_data
1134 );
1135 EXCEPTION
1136 WHEN FND_API.G_EXC_ERROR THEN
1137 x_return_status := FND_API.G_RET_STS_ERROR;
1138 -- Standard call to get message count and if count=1, get the message
1139 FND_MSG_PUB.Count_And_Get (
1140 p_encoded => FND_API.G_FALSE,
1141 p_count => x_msg_count,
1142 p_data => x_msg_data
1143 );
1144 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1145 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1146 -- Standard call to get message count and if count=1, get the message
1147 FND_MSG_PUB.Count_And_Get (
1148 p_encoded => FND_API.G_FALSE,
1149 p_count => x_msg_count,
1150 p_data => x_msg_data
1151 );
1152 WHEN OTHERS THEN
1153 ROLLBACK TO PROCESS_RESALE_ALL;
1154 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1155 -- Standard call to get message count and if count=1, get the message
1156 FND_MSG_PUB.Count_And_Get (
1157 p_encoded => FND_API.G_FALSE,
1158 p_count => x_msg_count,
1159 p_data => x_msg_data
1160 );
1161 END Process_Resale;
1162
1163 ---------------------------------------------------------------------
1164 -- PROCEDURE
1165 -- Start_Process_Resale
1166 --
1167 -- PURPOSE
1168 -- This procedure starts to process batches from interface table.
1169 --
1170 -- PARAMETERS
1171 --
1172 --
1173 -- NOTES
1174 ---------------------------------------------------------------------
1175 PROCEDURE Start_Process_Resale (
1176 ERRBUF OUT NOCOPY VARCHAR2,
1177 RETCODE OUT NOCOPY NUMBER,
1178 p_resale_batch_id IN NUMBER,
1179 p_start_date IN DATE,
1180 p_end_date IN DATE,
1181 p_partner_cust_account_id IN NUMBER
1182 )
1183 IS
1184 l_api_name CONSTANT VARCHAR2(30) := 'Start_Process_Resale';
1185 l_api_version CONSTANT NUMBER := 1.0;
1186 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
1187 --
1188 l_return_status varchar2(30);
1189 l_msg_data varchar2(2000);
1190 l_msg_count number;
1191
1192 l_start_date date := sysdate;
1193 l_end_date date := sysdate;
1194 BEGIN
1195
1196 SAVEPOINT Start_PROC_RESALE;
1197 RETCODE := 0;
1198
1199 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '*======================================================================================================*');
1200 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Execution Starts On: ' || to_char(sysdate,'MM-DD-YYYY HH24:MI:SS'));
1201 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '*------------------------------------------------------------------------------------------------------*');
1202 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Request Id : '||FND_GLOBAL.CONC_REQUEST_ID);
1203 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '-------------------------------------------------------------------------------------------------------*');
1204 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Resale Batch Id : '||TO_CHAR(p_resale_batch_id));
1205 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Start Date : '||p_start_date);
1206 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'End Date : '||p_end_date);
1207 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Cust Account Id : '||TO_CHAR(p_partner_cust_account_id));
1208 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '*------------------------------------------------------------------------------------------------------*');
1209 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' ');
1210 -- Debug Message
1211 IF OZF_DEBUG_LOW_ON THEN
1212 ozf_utility_PVT.debug_message(l_full_name||': start');
1213 END IF;
1214
1215 IF p_resale_batch_id is NULL THEN
1216 Process_resale (
1217 p_api_version => 1.0
1218 ,p_init_msg_list => FND_API.G_FALSE
1219 ,p_commit => FND_API.G_FALSE
1220 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
1221 ,p_start_date => p_start_date
1222 ,p_end_date => p_end_date
1223 ,p_partner_cust_account_id => p_partner_cust_account_id
1224 ,x_return_status => l_return_status
1225 ,x_msg_data => l_msg_data
1226 ,x_msg_count => l_msg_count
1227 );
1228 IF l_return_status = FND_API.g_ret_sts_error THEN
1229 ozf_utility_pvt.error_message('OZF_PROC_RESALE_ERR');
1230 RAISE FND_API.g_exc_error;
1231 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1232 ozf_utility_pvt.error_message('OZF_PROC_RESALE_ERR');
1233 RAISE FND_API.g_exc_unexpected_error;
1234 END IF;
1235 ELSE
1236 Process_resale (
1237 p_api_version => 1.0
1238 ,p_init_msg_list => FND_API.G_FALSE
1239 ,p_commit => FND_API.G_FALSE
1240 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
1241 ,p_resale_batch_id => p_resale_batch_id
1242 ,x_return_status => l_return_status
1243 ,x_msg_data => l_msg_data
1244 ,x_msg_count => l_msg_count
1245 );
1246 IF l_return_status = FND_API.g_ret_sts_error THEN
1247 ozf_utility_pvt.error_message('OZF_PROC_RESALE_ERR');
1248 RAISE FND_API.g_exc_error;
1249 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1250 ozf_utility_pvt.error_message('OZF_PROC_RESALE_ERR');
1251 RAISE FND_API.g_exc_unexpected_error;
1252 END IF;
1253 END IF;
1254
1255 -- Debug Message
1256 IF OZF_DEBUG_HIGH_ON THEN
1257 OZF_UTILITY_PVT.debug_message(l_full_name||': end');
1258 END IF;
1259
1260 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '*------------------------------------------------------------------------------------------------------*');
1261 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Execution Ends On: ' || to_char(sysdate,'MM-DD-YYYY HH24:MI:SS'));
1262 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '*======================================================================================================*');
1263
1264 -- Write all messages to a log
1265 OZF_UTILITY_PVT.Write_Conc_Log;
1266 EXCEPTION
1267 WHEN FND_API.g_exc_error THEN
1268 OZF_UTILITY_PVT.Write_Conc_Log;
1269 ERRBUF := l_msg_data;
1270 RETCODE := 1;
1271
1272 WHEN FND_API.g_exc_unexpected_error THEN
1273 OZF_UTILITY_PVT.Write_Conc_Log;
1274 ERRBUF := l_msg_data;
1275 RETCODE := 2;
1276
1277 WHEN OTHERS THEN
1278 ROLLBACK TO Start_PROC_RESALE;
1279 OZF_UTILITY_PVT.Write_Conc_Log;
1280 ERRBUF := l_msg_data;
1281 RETCODE := 2;
1282 END Start_Process_Resale;
1283
1284 ---------------------------------------------------------------------
1285 -- PROCEDURE
1286 -- Purge
1287 --
1288 -- PURPOSE
1289 -- Purge the successfully processed records
1290 --
1291 -- PARAMETERS
1292 --
1293 -- NOTES
1294 --
1295 ---------------------------------------------------------------------
1296 PROCEDURE Purge(
1297 p_api_version IN NUMBER
1298 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
1299 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
1300 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
1301 ,p_data_source_code IN VARCHAR2
1302 ,p_resale_batch_id IN NUMBER
1303 ,x_return_status OUT NOCOPY VARCHAR2
1304 ,x_msg_data OUT NOCOPY VARCHAR2
1305 ,x_msg_count OUT NOCOPY NUMBER
1306 )IS
1307 l_api_name CONSTANT VARCHAR2(30) := 'Purge';
1308 l_api_version CONSTANT NUMBER := 1.0;
1309 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
1310 --
1311
1312 BEGIN
1313 -- Standard begin of API savepoint
1314 SAVEPOINT Resale_Purge;
1315 -- Standard call to check for call compatibility.
1316 IF NOT FND_API.Compatible_API_Call (
1317 l_api_version,
1318 p_api_version,
1319 l_api_name,
1320 G_PKG_NAME)
1321 THEN
1322 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1323 END IF;
1324
1325 --Initialize message list if p_init_msg_list is TRUE.
1326 IF FND_API.To_Boolean (p_init_msg_list) THEN
1327 FND_MSG_PUB.initialize;
1328 END IF;
1329
1330 -- Initialize API return status to sucess
1331 x_return_status := FND_API.G_RET_STS_SUCCESS;
1332
1333 -- Debug Message
1334 IF OZF_DEBUG_HIGH_ON THEN
1335 OZF_UTILITY_PVT.debug_message(l_full_name||': Start');
1336 END IF;
1337
1338 IF p_resale_batch_id IS NULL THEN
1339 -- Purge the records that have been successfully processed
1340 IF p_data_source_code IS NULL OR
1341 p_data_source_code = 'ALL' THEN
1342 BEGIN
1343 DELETE FROM ozf_resale_lines_int_all a
1344 WHERE a.resale_batch_id IN (
1345 SELECT b.resale_batch_id
1346 FROM ozf_resale_batches b
1347 WHERE b.purge_flag IS NULL
1348 AND b.status_code = 'CLOSED'
1349 );
1350
1351 UPDATE ozf_resale_batches
1352 SET purge_flag = 'T'
1353 WHERE purge_flag IS NULL
1354 AND status_code = 'CLOSED';
1355 EXCEPTION
1356 WHEN OTHERS THEN
1357 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1358 END;
1359 ELSE
1360 BEGIN
1361 DELETE FROM ozf_resale_lines_int_all a
1362 WHERE a.data_source_code = p_data_source_code
1363 AND a.resale_batch_id IN (
1364 SELECT b.resale_batch_id
1365 FROM ozf_resale_batches b
1366 WHERE b.purge_flag IS NULL
1367 AND b.status_code = 'CLOSED'
1368 AND b.data_source_code = p_data_source_code
1369 );
1370
1371 UPDATE ozf_resale_batches
1372 SET purge_flag = 'T'
1373 WHERE purge_flag IS NULL
1374 AND status_code = 'CLOSED'
1375 AND data_source_code = p_data_source_code;
1376 EXCEPTION
1377 WHEN OTHERS THEN
1378 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1379 END;
1380 END IF;
1381 ELSE -- else (if p_batch_number is not null)
1382 -- Purge the records that have been successfully processed
1383 IF p_data_source_code IS NULL OR
1384 p_data_source_code = 'ALL' THEN
1385 BEGIN
1386 DELETE FROM ozf_resale_lines_int_all a
1387 WHERE a.resale_batch_id IN (
1388 SELECT b.resale_batch_id
1389 FROM ozf_resale_batches b
1390 WHERE b.purge_flag IS NULL
1391 AND b.resale_batch_id = p_resale_batch_id
1392 AND b.status_code NOT IN ('CLOSED', 'PROCESSING', 'PENDING_PAYMENT')
1393 );
1394
1395 DELETE FROM ozf_resale_batches_all
1396 WHERE resale_batch_id = p_resale_batch_id
1397 AND status_code NOT IN ('CLOSED', 'PROCESSING', 'PENDING_PAYMENT');
1398 EXCEPTION
1399 WHEN OTHERS THEN
1400 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1401 END;
1402 ELSE
1403 BEGIN
1404 DELETE FROM ozf_resale_lines_int_all a
1405 WHERE a.data_source_code = p_data_source_code
1406 AND a.resale_batch_id IN (
1407 SELECT b.resale_batch_id
1408 FROM ozf_resale_batches b
1409 WHERE b.purge_flag IS NULL
1410 AND b.data_source_code = p_data_source_code
1411 AND b.resale_batch_id = p_resale_batch_id
1412 );
1413
1414 DELETE FROM ozf_resale_batches_all
1415 WHERE resale_batch_id = p_resale_batch_id
1416 AND data_source_code = p_data_source_code
1417 AND status_code NOT IN ('CLOSED', 'PROCESSING', 'PENDING_PAYMENT');
1418 EXCEPTION
1419 WHEN OTHERS THEN
1420 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1421 END;
1422 END IF;
1423 END IF;
1424
1425
1426 -- Debug Message
1427 IF OZF_DEBUG_HIGH_ON THEN
1428 OZF_UTILITY_PVT.debug_message(l_full_name||': End');
1429 END IF;
1430 --Standard call to get message count and if count=1, get the message
1431 FND_MSG_PUB.Count_And_Get (
1432 p_encoded => FND_API.G_FALSE,
1433 p_count => x_msg_count,
1434 p_data => x_msg_data
1435 );
1436
1437 EXCEPTION
1438 WHEN FND_API.G_EXC_ERROR THEN
1439 ROLLBACK TO Resale_Purge ;
1440
1441 x_return_status := FND_API.G_RET_STS_ERROR;
1442 -- Standard call to get message count and if count=1, get the message
1443 FND_MSG_PUB.Count_And_Get (
1444 p_encoded => FND_API.G_FALSE,
1445 p_count => x_msg_count,
1446 p_data => x_msg_data
1447 );
1448 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1449 ROLLBACK TO Resale_Purge;
1450 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1451
1452 -- Standard call to get message count and if count=1, get the message
1453 FND_MSG_PUB.Count_And_Get (
1454 p_encoded => FND_API.G_FALSE,
1455 p_count => x_msg_count,
1456 p_data => x_msg_data
1457 );
1458 WHEN OTHERS THEN
1459 ROLLBACK TO Resale_Purge;
1460 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1461
1462 -- Standard call to get message count and if count=1, get the message
1463 FND_MSG_PUB.Count_And_Get (
1464 p_encoded => FND_API.G_FALSE,
1465 p_count => x_msg_count,
1466 p_data => x_msg_data
1467 );
1468 END Purge;
1469
1470 ---------------------------------------------------------------------
1471 -- PROCEDURE
1472 -- Start_Purge
1473 --
1474 -- PURPOSE
1475 -- This procedure starts to remove processed date from interface table.
1476 --
1477 -- PARAMETERS
1478 --
1479 --
1480 -- NOTES
1481 ---------------------------------------------------------------------
1482 PROCEDURE Start_Purge (
1483 ERRBUF OUT NOCOPY VARCHAR2,
1484 RETCODE OUT NOCOPY NUMBER,
1485 p_data_source_code IN VARCHAR2 := NULL,
1486 p_resale_batch_id IN NUMBER := NULL
1487 )IS
1488 l_api_name CONSTANT VARCHAR2(30) := 'Start_Process_Purge';
1489 l_api_version CONSTANT NUMBER := 1.0;
1490 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
1491 --
1492 l_return_status varchar2(30);
1493 l_msg_data varchar2(2000);
1494 l_msg_count number;
1495 begin
1496
1497 SAVEPOINT Start_RESALE_Purge;
1498 RETCODE := 0;
1499 -- Debug Message
1500 IF OZF_DEBUG_HIGH_ON THEN
1501 OZF_UTILITY_PVT.debug_message(l_full_name||': Start');
1502 END IF;
1503
1504 FND_FILE.PUT_LINE(FND_FILE.LOG, '/*--- Start to purge processed order ---*/');
1505
1506 Purge (
1507 p_api_version => 1.0
1508 ,p_init_msg_list => FND_API.G_FALSE
1509 ,p_commit => FND_API.G_FALSE
1510 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
1511 ,p_data_source_code => p_data_source_code
1512 ,p_resale_batch_id => p_resale_batch_id
1513 ,x_return_status => l_return_status
1514 ,x_msg_data => l_msg_data
1515 ,x_msg_count => l_msg_count
1516 );
1517 IF l_return_status = FND_API.g_ret_sts_error THEN
1518 ozf_utility_pvt.error_message('OZF_PURGE_CHG_INT_ERR');
1519 RAISE FND_API.g_exc_error;
1520 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1521 ozf_utility_pvt.error_message('OZF_PURGE_CHG_INT_ERR');
1522 RAISE FND_API.g_exc_unexpected_error;
1523 END IF;
1524
1525 FND_FILE.PUT_LINE(FND_FILE.LOG, '/*--- End of purge processed order ---*/');
1526
1527 -- Debug Message
1528 IF OZF_DEBUG_HIGH_ON THEN
1529 OZF_UTILITY_PVT.debug_message(l_full_name||': End');
1530 END IF;
1531
1532 -- Write all messages to a log
1533 OZF_UTILITY_PVT.Write_Conc_Log;
1534 EXCEPTION
1535 WHEN FND_API.g_exc_error THEN
1536 ROLLBACK TO Start_RESALE_Purge;
1537 FND_FILE.PUT_LINE(FND_FILE.LOG, '/*--- Error happened during purge ---*/');
1538 OZF_UTILITY_PVT.Write_Conc_Log;
1539 ERRBUF := l_msg_data;
1540 RETCODE := 1;
1541
1542 WHEN FND_API.g_exc_unexpected_error THEN
1543 ROLLBACK TO Start_RESALE_Purge;
1544 FND_FILE.PUT_LINE(FND_FILE.LOG, '/*--- Error happened during purge ---*/');
1545 OZF_UTILITY_PVT.Write_Conc_Log;
1546 ERRBUF := l_msg_data;
1547 RETCODE := 2;
1548
1549 WHEN OTHERS THEN
1550 ROLLBACK TO Start_RESALE_Purge;
1551 FND_FILE.PUT_LINE(FND_FILE.LOG, '/*--- Error happened during purge ---*/');
1552 OZF_UTILITY_PVT.Write_Conc_Log;
1553 ERRBUF := l_msg_data;
1554 RETCODE := 2;
1555
1556 END Start_Purge;
1557
1558 END OZF_RESALE_PVT;