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