1 PACKAGE BODY OZF_AUTOPAY_PVT AS
2 /* $Header: ozfvatob.pls 120.9 2008/01/25 08:53:51 nirprasa ship $ */
3 -- Start of Comments
4 -- Package name : OZF_AUTOPAY_PVT
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OZF_AUTOPAY_PVT';
11 G_UPDATE_EVENT CONSTANT VARCHAR2(30) := 'UPDATE';
12 G_DAILY CONSTANT VARCHAR2(30) := 'DAYS';
13 G_WEEKLY CONSTANT VARCHAR2(30) := 'WEEKS';
14 G_MONTHLY CONSTANT VARCHAR2(30) := 'MONTHS';
15 G_QUARTERLY CONSTANT VARCHAR2(30) := 'QUARTERS';
16 G_ANNUAL CONSTANT VARCHAR2(30) := 'YEAR';
17 G_OFFER_TYPE CONSTANT VARCHAR2(30) := 'OFFR';
18 G_CAMPAIGN_TYPE CONSTANT VARCHAR2(30) := 'CAMP';
19 G_AUTOPAY_FLAG_OFF CONSTANT VARCHAR2(40) := 'Autopay flag is not turned on.';
20 G_AUTOPAY_PERIOD_MISS CONSTANT VARCHAR2(40) := 'Autopay period information missing.';
21 G_AUTOPAY_PLAN_TYPE_ERR CONSTANT VARCHAR2(40) := 'Can not hanlde this plan type.';
22 G_CLAIM_SETUP_ID CONSTANT NUMBER := 2001;
23 G_CLAIM_STATUS CONSTANT VARCHAR2(30) := 'OZF_CLAIM_STATUS';
24 G_OPEN_STATUS CONSTANT VARCHAR2(30) := 'OPEN';
25 G_CLOSED_STATUS CONSTANT VARCHAR2(30) := 'CLOSED';
26
27 OZF_DEBUG_HIGH_ON BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_high);
28 OZF_DEBUG_LOW_ON BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low);
29
30 ---------------------------------------------------------------------
31 -- Definitions of some packagewise cursors.
32 ---------------------------------------------------------------------
33 CURSOR g_site_info_csr(p_id in number) IS
34 SELECT trade_profile_id,
35 cust_account_id,
36 site_use_id,
37 payment_method,
38 vendor_id,
39 vendor_site_id,
40 last_paid_date,
41 autopay_periodicity,
42 autopay_periodicity_type,
43 autopay_flag,
44 claim_threshold,
45 claim_currency,
46 org_id
47 FROM ozf_cust_trd_prfls_all
48 WHERE site_use_id = p_id;
49
50 CURSOR g_customer_info_csr(p_id in number) IS
51 SELECT trade_profile_id,
52 cust_account_id,
53 site_use_id,
54 payment_method,
55 vendor_id,
56 vendor_site_id,
57 last_paid_date,
58 autopay_periodicity,
59 autopay_periodicity_type,
60 autopay_flag,
61 claim_threshold,
62 claim_currency,
63 org_id
64 FROM ozf_cust_trd_prfls
65 WHERE cust_account_id = p_id
66 AND site_use_id IS NULL;
67
68 CURSOR g_party_trade_info_csr(p_id in number) IS
69 SELECT trade_profile_id,
70 cust_account_id,
71 site_use_id,
72 payment_method,
73 vendor_id,
74 vendor_site_id,
75 last_paid_date,
76 autopay_periodicity,
77 autopay_periodicity_type,
78 autopay_flag,
79 claim_threshold,
80 claim_currency,
81 org_id
82 FROM ozf_cust_trd_prfls
83 WHERE party_id = p_id
84 AND cust_account_id IS NULL;
85
86
87 ---------------------------------------------------------------------
88 -- PROCEDURE
89 -- validate_customer_info
90 --
91 -- PURPOSE
92 -- This procedure validates customer info
93 --
94 -- PARAMETERS
95 -- p_cust_account : custome account id
96 -- x_days_due : days due
97 --
98 -- NOTES
99 ---------------------------------------------------------------------
100 PROCEDURE validate_customer_info (
101 p_customer_info in g_customer_info_csr%rowtype,
102 x_return_status OUT NOCOPY varchar2
103 )
104 IS
105 CURSOR csr_cust_name(cv_cust_account_id IN NUMBER) IS
106 SELECT CONCAT(CONCAT(party.party_name, ' ('), CONCAT(ca.account_number, ') '))
107 FROM hz_cust_accounts ca
108 , hz_parties party
109 WHERE ca.party_id = party.party_id
110 AND ca.cust_account_id = cv_cust_account_id;
111
112 l_cust_account_id number := p_customer_info.cust_account_id;
113 l_cust_name_num VARCHAR2(70);
114
115 BEGIN
116 -- Initialize API return status to sucess
117 x_return_status := FND_API.G_RET_STS_SUCCESS;
118
119 IF p_customer_info.claim_currency is null THEN
120 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
121 OPEN csr_cust_name(l_cust_account_id);
122 FETCH csr_cust_name INTO l_cust_name_num;
123 CLOSE csr_cust_name;
124
125 FND_MESSAGE.Set_Name('OZF', 'OZF_CLAIM_ATPY_CURRENCY_MISS');
126 FND_MESSAGE.Set_Token('ID',l_cust_name_num);
127 FND_MSG_PUB.ADD;
128 END IF;
129 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
130 END IF;
131
132 IF p_customer_info.payment_method is null THEN
133 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
134 OPEN csr_cust_name(l_cust_account_id);
135 FETCH csr_cust_name INTO l_cust_name_num;
136 CLOSE csr_cust_name;
137
138 FND_MESSAGE.Set_Name('OZF', 'OZF_CLAIM_ATPY_PYMTHD_MISS');
139 FND_MESSAGE.Set_Token('ID',l_cust_name_num);
140 FND_MSG_PUB.ADD;
141 END IF;
142 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
143 END IF;
144
145 IF p_customer_info.payment_method = 'CHECK' THEN
146 IF p_customer_info.vendor_id is NULL OR
147 p_customer_info.vendor_site_id is NULL THEN
148 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
149 OPEN csr_cust_name(l_cust_account_id);
150 FETCH csr_cust_name INTO l_cust_name_num;
151 CLOSE csr_cust_name;
152
153 FND_MESSAGE.Set_Name('OZF', 'OZF_CLAIM_ATPY_VENDOR_MISS');
154 FND_MESSAGE.Set_Token('ID',l_cust_name_num);
155 FND_MSG_PUB.ADD;
156 END IF;
157 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
158 END IF;
159 ELSIF p_customer_info.payment_method = 'CREDIT_MEMO' THEN
160 IF p_customer_info.site_use_id is NULL THEN
161 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
162 OPEN csr_cust_name(l_cust_account_id);
163 FETCH csr_cust_name INTO l_cust_name_num;
164 CLOSE csr_cust_name;
165
166 FND_MESSAGE.Set_Name('OZF', 'OZF_CLAIM_ATPY_SITEID_MISS');
167 FND_MESSAGE.Set_Token('ID',l_cust_name_num);
168 FND_MSG_PUB.ADD;
169 END IF;
170 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
171 END IF;
172 END IF;
173
174 /*
175 IF p_customer_info.org_id is null THEN
176 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
177 OPEN csr_cust_name(l_cust_account_id);
178 FETCH csr_cust_name INTO l_cust_name_num;
179 CLOSE csr_cust_name;
180
181 FND_MESSAGE.Set_Name('OZF', 'OZF_CLAIM_ATPY_ORG_ID_MISS');
182 FND_MESSAGE.Set_Token('ID',l_cust_name_num);
183 FND_MSG_PUB.add;
184 END IF;FND_MSG_PUB.ADD;
185 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
186 END IF;
187 */
188
189 EXCEPTION
190 WHEN OTHERS THEN
191 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
192 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
193 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_ATPY_CUSTOMER_ERR');
194 FND_MSG_PUB.add;
195 END IF;
196 END validate_customer_info;
197
198
199
200 ---------------------------------------------------------------------
201 -- PROCEDURE
202 -- get_pay_date
203 --
204 -- PURPOSE
205 -- This procedure computes the date a payment has to be made based on last_paid_date and periodicity.
206 --
207 -- PARAMETERS
208 -- p_type: type of peroidicity
209 -- p_period: how many period
210 -- p_last_date: last date a payment is made
211 -- x_pay_date: date a payment should be made based on the last_paid_date and periodicity
212 -- x_return_status
213 --
214 -- NOTES
215 ---------------------------------------------------------------------
216 PROCEDURE get_pay_date(p_type IN VARCHAR2,
217 p_period IN NUMBER,
218 p_last_date IN DATE,
219 x_pay_date OUT NOCOPY DATE,
220 x_return_status OUT NOCOPY VARCHAR2
221 )
222 IS
223 l_return_date date;
224 BEGIN
225 -- Initialize API return status to sucess
226 x_return_status := FND_API.G_RET_STS_SUCCESS;
227
228 IF p_type = G_DAILY THEN
229 l_return_date := p_last_date + p_period;
230 ELSIF p_type = G_WEEKLY THEN
231 l_return_date := p_last_date + p_period*7;
232 ELSIF p_type = G_MONTHLY THEN
233 l_return_date := add_months(p_last_date, p_period);
234 ELSIF p_type = G_QUARTERLY THEN
235 l_return_date := add_months(p_last_date, p_period*3);
236 -- ELSIF p_type = G_SEMI_ANNUAL THEN
237 -- l_return_date := add_months(p_last_date, p_period*6);
238 ELSIF p_type = G_ANNUAL THEN
239 l_return_date := add_months(p_last_date, p_period*12);
240 ELSE
241 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
242 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_ATPY_AUPD_MISS');
243 FND_MSG_PUB.add;
244 END IF;
245 RAISE FND_API.G_EXC_ERROR;
246 END IF;
247 x_pay_date := l_return_date;
248 EXCEPTION
249 WHEN FND_API.G_EXC_ERROR THEN
250 x_return_status := FND_API.G_RET_STS_ERROR;
251 END get_pay_date;
252
253 ---------------------------------------------------------------------
254 -- PROCEDURE
255 -- create_claim_for_cust
256 --
257 -- PURPOSE
258 -- This procedure creates a claim and its lines for a customer based on the utilization table.
259 -- It will then settle it based on different payment method.
260 --
261 -- PARAMETERS
262 -- p_customer_info IN g_customer_info_csr%rowtype
263 -- p_amount IN number,
264 -- p_mode IN varchar2
265 -- p_auto_reason_code_id IN number
266 -- p_auto_claim_type_id IN number
267 -- p_autopay_periodicity IN number
268 -- p_autopay_periodicity_type IN VARCHAR2
269 -- p_offer_payment_method IN VARCHAR2
270 -- p_funds_util_flt IN OZF_Claim_Accrual_PVT.funds_util_flt_type
271 -- x_return_status OUT VARCHAR2
272 --
273 -- NOTES
274 ---------------------------------------------------------------------
275 PROCEDURE create_claim_for_cust(
276 p_customer_info IN g_customer_info_csr%rowtype,
277 p_amount IN number,
278 p_mode IN varchar2,
279 p_auto_reason_code_id IN number,
280 p_auto_claim_type_id IN number,
281 p_autopay_periodicity IN number,
282 p_autopay_periodicity_type IN VARCHAR2,
283 p_offer_payment_method IN VARCHAR2,
284 p_funds_util_flt IN OZF_Claim_Accrual_PVT.funds_util_flt_type,
285 x_return_status OUT NOCOPY VARCHAR2
286 )
287 IS
288 l_amount number := p_amount;
289 l_cust_account_id number;
290 l_last_pay_date date;
291 l_claim_id number;
292 l_claim_rec OZF_CLAIM_PVT.claim_rec_type;
293 l_claim_settle_rec OZF_CLAIM_PVT.claim_rec_type;
294 l_funds_util_flt OZF_Claim_Accrual_PVT.funds_util_flt_type := p_funds_util_flt;
295 l_plan_type VARCHAR2(30);
296
297 CURSOR csr_cust_name(cv_cust_account_id IN NUMBER) IS
298 SELECT CONCAT(CONCAT(party.party_name, ' ('), CONCAT(ca.account_number, ') '))
299 FROM hz_cust_accounts ca
300 , hz_parties party
301 WHERE ca.party_id = party.party_id
302 AND ca.cust_account_id = cv_cust_account_id;
303
304 l_cust_name_num VARCHAR2(70);
305
306 CURSOR claim_info_csr(p_claim_id in number) IS
307 select object_version_number, sales_rep_id
308 from ozf_claims_all
309 where claim_id = p_claim_id;
310
311 l_object_version_number number;
312 l_return_status varchar2(1);
313 l_msg_data varchar2(2000);
314 l_msg_count number;
315
316 l_autopay_periodicity number;
317 l_autopay_periodicity_type VARCHAR2(30);
318
319 CURSOR csr_ar_system_options IS
320 SELECT salesrep_required_flag
321 FROM ar_system_parameters;
322 l_salesrep_req_flag VARCHAR2(1);
323 l_sales_rep_id NUMBER;
324
325 CURSOR csr_claim_num(cv_claim_id IN NUMBER) IS
326 SELECT claim_number, amount, cust_billto_acct_site_id
327 FROM ozf_claims
328 WHERE claim_id = cv_claim_id;
329 l_claim_num varchar2(30);
330 l_claim_amt number;
331 l_cust_billto_acct_site_id number;
332
333 l_eligible_flag varchar2(1);
334
335 BEGIN
336 -- Initialize API return status to sucess
337 x_return_status := FND_API.G_RET_STS_SUCCESS;
338
339 -- For this customer: check whether there is a need to create a claim
340 -- check sum of acctd_amount from utiliztion only create claims with positvit amount
341 IF l_amount is NOT NULL AND l_amount > 0 THEN
342 IF p_mode = 'B' THEN
343 -- IF the mode is 'Backdated'
344 l_eligible_flag := FND_API.g_true;
345 ELSE
346 IF p_customer_info.autopay_flag = FND_API.G_TRUE THEN
347 -- create a claim for this customer
348 IF (p_customer_info.claim_threshold is NOT NULL AND
349 l_amount > p_customer_info.claim_threshold) THEN
350 -- create a claim record based on l_cust_id_tbl(i).amount > l_cust_id_tbl(i).claim_threshold
351 l_eligible_flag := FND_API.g_true;
352 ELSE
353 -- create a claim based on frequency.
354 -- Need to get last pay date
355 IF p_customer_info.LAST_PAID_DATE is NULL THEN
356 -- Will pay it now
357 l_last_pay_date := sysdate;
358 ELSE
359 -- assign p_autopay_periodicity and p_autopay_periodicity_type
360 IF p_customer_info.autopay_periodicity_type is NULL OR
361 p_customer_info.autopay_periodicity is NULL THEN
362 IF p_autopay_periodicity is NULL OR
363 p_autopay_periodicity_type is NULL THEN
364 -- write to a log file
365 -- skip this customer
366 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
367 OPEN csr_cust_name(p_customer_info.cust_account_id);
368 FETCH csr_cust_name INTO l_cust_name_num;
369 CLOSE csr_cust_name;
370
371 FND_MESSAGE.Set_Name('OZF', 'OZF_CLAIM_ATPY_PERIOD_MISS');
372 FND_MESSAGE.Set_Token('ID', l_cust_name_num);
373 FND_MSG_PUB.ADD;
374 END IF;
375 RAISE FND_API.g_exc_unexpected_error;
376 --goto end_loop;
377 END IF;
378 l_autopay_periodicity := p_autopay_periodicity;
379 l_autopay_periodicity_type := p_autopay_periodicity_type;
380 ELSE
381 l_autopay_periodicity := p_customer_info.autopay_periodicity;
382 l_autopay_periodicity_type := p_customer_info.autopay_periodicity_type;
383 END IF;
384 END IF;
385
386 IF l_last_pay_date is NULL THEN
387 -- get last pay date
388 get_pay_date(
389 p_type => l_autopay_periodicity_type,
390 p_period => l_autopay_periodicity,
391 p_last_date => p_customer_info.last_paid_date,
392 x_pay_date => l_last_pay_date,
393 x_return_status => l_return_status
394 );
395 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
396 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
397 OPEN csr_cust_name(p_customer_info.cust_account_id);
398 FETCH csr_cust_name INTO l_cust_name_num;
399 CLOSE csr_cust_name;
400
401 FND_MESSAGE.Set_Name('OZF', 'OZF_CLAIM_ATPY_PAY_DATE_MISS');
402 FND_MESSAGE.Set_Token('ID', l_cust_name_num);
403 FND_MSG_PUB.ADD;
404 END IF;
405 RAISE FND_API.g_exc_unexpected_error;
406 END IF;
407 END IF;
408
409 -- pay customer is last_pay_date has passed.
410 IF l_last_pay_date <= sysdate THEN
411 -- create a claim record based on frequency
412 -- NOTE: There is not exchange info here since the functional
413 -- currency is the default claim currency.
414 l_eligible_flag := FND_API.g_true;
415 END IF;
416 END IF;
417 END IF; -- end of if p_customer_info.autopay_flag checking
418 END IF; -- end of if p_mode checking
419
420 IF l_eligible_flag = FND_API.g_true THEN
421 l_claim_rec.claim_class := 'CLAIM';
422 l_claim_rec.claim_type_id := p_auto_claim_type_id;
423 l_claim_rec.reason_code_id := p_auto_reason_code_id;
424 -- Modified for FXGL Enhancement
425 -- The claim currency will be the accrual currency and not the customer
426 -- currency. As no cross currency associtaion is supported
427 -- For a particular accrual in X currerncy to be associated
428 -- claim must also be in X currency
429 l_claim_rec.currency_code := l_funds_util_flt.utiz_currency_code;
430 l_claim_rec.cust_account_id := p_customer_info.cust_account_id;
431 l_claim_rec.cust_billto_acct_site_id := p_customer_info.site_use_id;
432 l_claim_rec.vendor_id := p_customer_info.vendor_id;
433 l_claim_rec.vendor_site_id := p_customer_info.vendor_site_id;
434 -- offer's payment method overrides trade profile
435 IF p_offer_payment_method IS NOT NULL THEN
436 l_claim_rec.payment_method := p_offer_payment_method;
437 ELSE
438 l_claim_rec.payment_method := p_customer_info.payment_method;
439 END IF;
440 l_claim_rec.created_from := 'AUTOPAY';
441
442 l_funds_util_flt.cust_account_id := p_customer_info.cust_account_id;
443 IF p_offer_payment_method IS NOT NULL THEN
444 l_funds_util_flt.offer_payment_method := p_offer_payment_method;
445 ELSE
446 l_funds_util_flt.offer_payment_method := 'NULL';
447 END IF;
448
449 OZF_CLAIM_ACCRUAL_PVT.Create_Claim_For_Accruals(
450 p_api_version => 1.0
451 ,p_init_msg_list => FND_API.g_false
452 ,p_commit => FND_API.g_false
453 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
454 ,x_return_status => l_return_status
455 ,x_msg_count => l_msg_count
456 ,x_msg_data => l_msg_data
457 ,p_claim_rec => l_claim_rec
458 ,p_funds_util_flt => l_funds_util_flt
459 ,x_claim_id => l_claim_id
460 );
461 IF l_return_status <> FND_API.g_ret_sts_success THEN
462 x_return_status := FND_API.G_RET_STS_ERROR;
463 RETURN;
464 END IF;
465
466 IF l_claim_id is not NULL THEN
467 -- update to settle the claim
468 OPEN claim_info_csr(l_claim_id);
469 FETCH claim_info_csr into l_object_version_number, l_sales_rep_id;
470 CLOSE claim_info_csr;
471
472 l_claim_settle_rec.claim_id := l_claim_id;
473 l_claim_settle_rec.object_version_number := l_object_version_number;
474 l_claim_settle_rec.USER_STATUS_ID := to_number( ozf_utility_pvt.GET_DEFAULT_USER_STATUS(
475 P_STATUS_TYPE=> G_CLAIM_STATUS,
476 P_STATUS_CODE=> G_CLOSED_STATUS
477 ));
478
479 ------------------------------------------------------
480 -- Sales Credit
481 -- Bug 2950241 fixing: default Sales Rep in Claims
482 -- if "Requires Salesperson" in AR system options.
483 ------------------------------------------------------
484 IF l_sales_rep_id IS NULL THEN
485 OPEN csr_ar_system_options;
486 FETCH csr_ar_system_options INTO l_salesrep_req_flag;
487 CLOSE csr_ar_system_options;
488
489 IF l_salesrep_req_flag = 'Y' THEN
490 l_claim_settle_rec.sales_rep_id := -3; -- No Sales Credit
491 END IF;
492 END IF;
493
494 OZF_claim_PVT.Update_claim(
495 P_Api_Version => 1.0,
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 X_Return_Status => l_return_status,
500 X_Msg_Count => l_msg_count,
501 X_Msg_Data => l_msg_data,
502 P_claim => l_claim_settle_Rec,
503 p_event => 'UPDATE',
504 p_mode => OZF_claim_Utility_pvt.G_AUTO_MODE,
505 X_Object_Version_Number => l_object_version_number
506 );
507 IF l_return_status <> FND_API.g_ret_sts_success THEN
508 x_return_status := FND_API.G_RET_STS_ERROR;
509 RETURN;
510 END IF;
511 END IF;
512
513 ELSE
514 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
515 OPEN csr_cust_name(p_customer_info.cust_account_id);
516 FETCH csr_cust_name INTO l_cust_name_num;
517 CLOSE csr_cust_name;
518
519 FND_MESSAGE.Set_Name('OZF', 'OZF_CLAIM_ATPY_CUST_INELIG');
520 FND_MESSAGE.Set_Token('ID', l_cust_name_num);
521 FND_MSG_PUB.ADD;
522 END IF;
523 RAISE FND_API.g_exc_unexpected_error;
524 END IF;
525 ELSE
526 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
527 OPEN csr_cust_name(p_customer_info.cust_account_id);
528 FETCH csr_cust_name INTO l_cust_name_num;
529 CLOSE csr_cust_name;
530
531 FND_MESSAGE.Set_Name('OZF', 'OZF_CLAIM_ATPY_AMT_SMALL');
532 FND_MESSAGE.Set_Token('ID', l_cust_name_num);
533 FND_MSG_PUB.ADD;
534 END IF;
535 RAISE FND_API.g_exc_unexpected_error;
536 END IF;
537
538 OPEN csr_claim_num(l_claim_id);
539 FETCH csr_claim_num INTO l_claim_num, l_claim_amt,l_cust_billto_acct_site_id;
540 CLOSE csr_claim_num;
541
542
543 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Claim Number: '||l_claim_num);
544 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Claim Amount: '||l_claim_amt);
545
546 IF l_cust_billto_acct_site_id IS NOT NULL
547 OR l_cust_billto_acct_site_id<>0 THEN
548
549 FND_FILE.PUT_LINE(FND_FILE.LOG, 'The claim is created for bill_to site: '||l_cust_billto_acct_site_id);
550 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'The claim is created for bill_to site: '|| l_cust_billto_acct_site_id );
551
552 END IF;
553
554 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Claim Number : '||l_claim_num );
555 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Claim Amount : '||l_claim_amt );
556
557 EXCEPTION
558 WHEN OTHERS THEN
559 x_return_status := FND_API.G_RET_STS_ERROR;
560 END create_claim_for_cust;
561
562
563 ---------------------------------------------------------------------
564 -- PROCEDURE
565 -- Create_Claim_for_BD_Offer
566 --
567 -- PURPOSE
568 -- Create a claim for a backdated offer.
569 --
570 -- PARAMETERS
571 -- p_offer_tbl : list of offers info that a claim will be created on.
572 --
573 ---------------------------------------------------------------------
574 PROCEDURE Create_Claim_for_BD_Offer(
575 p_api_version IN NUMBER
576 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
577 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
578 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
579 ,x_return_status OUT NOCOPY VARCHAR2
580 ,x_msg_data OUT NOCOPY VARCHAR2
581 ,x_msg_count OUT NOCOPY NUMBER
582 ,p_offer_tbl IN offer_tbl_type
583 )
584 IS
585 l_return_status varchar2(1);
586 l_msg_data varchar2(2000);
587 l_msg_count number;
588
589 l_api_name CONSTANT VARCHAR2(30) := 'Create_Claim_for_BD_Offer';
590 l_api_version CONSTANT NUMBER := 1.0;
591 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
592
593 CURSOR sys_parameter_info_csr IS
594 SELECT autopay_flag
595 , autopay_reason_code_id
596 , autopay_claim_type_id
597 , autopay_periodicity
598 , autopay_periodicity_type
599 FROM ozf_sys_parameters;
600
601 l_autopay_flag varchar2(1);
602 l_auto_reason_code_id number;
603 l_auto_claim_type_id number;
604 l_autopay_periodicity number;
605 l_autopay_periodicity_type VARCHAR2(30);
606 l_cust_account_id number;
607 l_amount number;
608
609 CURSOR settlement_method_CSR(p_id in number) is
610 select settlement_code
611 from ozf_offer_adjustments_b
612 where list_header_id = p_id;
613
614 l_customer_info g_customer_info_csr%rowtype;
615 l_funds_util_flt OZF_CLAIM_ACCRUAL_PVT.funds_util_flt_type := NULL;
616
617 BEGIN
618
619 SAVEPOINT BDOffer;
620
621 -- Debug Message
622 IF OZF_DEBUG_LOW_ON THEN
623 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
624 FND_MESSAGE.Set_Token('TEXT',l_full_name||': Start');
625 FND_MSG_PUB.Add;
626 END IF;
627
628 -- get autopay_flag, reason_code_id
629 OPEN sys_parameter_info_csr;
630 FETCH sys_parameter_info_csr INTO l_autopay_flag
631 , l_auto_reason_code_id
632 , l_auto_claim_type_id
633 , l_autopay_periodicity
634 , l_autopay_periodicity_type;
635 CLOSE sys_parameter_info_csr;
636
637 -- check reason_code and claim_type from sys_parameters.
638 IF l_auto_reason_code_id is NULL THEN
639 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
640 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_REASON_CD_MISSING');
641 FND_MSG_PUB.add;
642 END IF;
643 RAISE FND_API.g_exc_unexpected_error;
644 END IF;
645
646 IF l_auto_claim_type_id is NULL THEN
647 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
648 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_CLAIM_TYPE_MISSING');
649 FND_MSG_PUB.add;
650 END IF;
651 RAISE FND_API.g_exc_unexpected_error;
652 END IF;
653
654 -- Loop through p_offer table
655 For i in p_offer_tbl.FIRST..p_offer_tbl.COUNT LOOP
656 IF l_cust_account_id is not null THEN
657 -- Get customer information
658 OPEN g_customer_info_csr(l_cust_account_id);
659 FETCH g_customer_info_csr into l_customer_info;
660 CLOSE g_customer_info_csr;
661
662 validate_customer_info (
663 p_customer_info => l_customer_info,
664 x_return_status => l_return_status
665 );
666 -- skip this customer if we can not get all the info.
667 IF l_return_status = FND_API.g_ret_sts_error or
668 l_return_status = FND_API.g_ret_sts_unexp_error THEN
669 RAISE FND_API.g_exc_unexpected_error;
670 --goto end_loop;
671 END IF;
672
673 -- But, we need to overwrite the payment method for the customer;
674 OPEN settlement_method_CSR(p_offer_tbl(i).offer_id);
675 FETCH settlement_method_CSR into l_customer_info.payment_method;
676 CLOSE settlement_method_CSR;
677
678 l_funds_util_flt := null;
679 l_funds_util_flt.activity_id := p_offer_tbl(i).offer_id;
680 l_funds_util_flt.activity_type := G_OFFER_TYPE;
681 l_funds_util_flt.adjustment_type_id := p_offer_tbl(i).adjustment_type_id;
682
683 create_claim_for_cust(p_customer_info => l_customer_info,
684 p_amount => p_offer_tbl(i).amount,
685 p_mode => 'B',
686 p_auto_reason_code_id => l_auto_reason_code_id,
687 p_auto_claim_type_id => l_auto_claim_type_id,
688 p_autopay_periodicity => l_autopay_periodicity,
689 p_autopay_periodicity_type => l_autopay_periodicity_type,
690 p_offer_payment_method=> null,
691 p_funds_util_flt => l_funds_util_flt,
692 x_return_status => l_return_status
693 );
694 IF l_return_status = FND_API.g_ret_sts_error THEN
695 RAISE FND_API.g_exc_error;
696 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
697 RAISE FND_API.g_exc_unexpected_error;
698 END IF;
699 END IF;
700 END LOOP;
701
702 -- Debug Message
703 IF OZF_DEBUG_LOW_ON THEN
704 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
705 FND_MESSAGE.Set_Token('TEXT',l_full_name||': End');
706 FND_MSG_PUB.Add;
707 END IF;
708
709 EXCEPTION
710 WHEN FND_API.g_exc_error THEN
711 ROLLBACK TO BDOffer;
712 FND_MSG_PUB.count_and_get (
713 p_encoded => FND_API.g_false
714 ,p_count => l_msg_count
715 ,p_data => l_msg_data
716 );
717 WHEN FND_API.g_exc_unexpected_error THEN
718 ROLLBACK TO BDOffer;
719 FND_MSG_PUB.count_and_get (
720 p_encoded => FND_API.g_false
721 ,p_count => l_msg_count
722 ,p_data => l_msg_data
723 );
724 WHEN OTHERS THEN
725 ROLLBACK TO BDOffer;
726 FND_MSG_PUB.count_and_get (
727 p_encoded => FND_API.g_false
728 ,p_count => l_msg_count
729 ,p_data => l_msg_data
730 );
731 END Create_claim_for_bd_offer;
732
733
734 --------------------------------------------------------------------------------
735 -- API name : Start_Autopay
736 -- Type : Public
737 -- Pre-reqs : None
738 -- Function :
739 -- Parameters :
740 --
741 -- IN : p_run_mode IN VARCHAR2 Optional
742 -- : p_customer_id IN NUMBER Optional
743 -- : p_relationship_type IN VARCHAR2 Optional
744 -- : p_related_cust_account_id IN NUMBER Optional
745 -- : p_buy_group_party_id IN NUMBER Optional
746 -- : p_select_cust_children_flag IN VARCHAR2 Optional
747 -- : p_pay_to_customer IN VARCHAR2 Optional
748 -- : p_fund_id IN NUMBER Optional
749 -- : p_plan_type IN NUMBER Optional
750 -- : p_offer_type IN VARCHAR2 Optional
751 -- : p_plan_id IN NUMBER Optional
752 -- : p_product_category_id IN NUMBER Optional
753 -- : p_product_id IN NUMBER Optional
754 -- : p_end_date IN VARCHAR2 Optional
755 -- : p_org_id IN NUMBER Optional
756 --
757 -- Version : Current version 1.0
758 --
759 -- Note: This program automatically creates a claim for a set of customers
760 -- The customer set is selected based on the input paramter. Also, we will pay a cusomter:
761 -- if a customer utiliztion amount summation is greater than his threshold_amount
762 -- or if the current date passes last_paid_date + threshold period.
763 -- End of Comments
764 --------------------------------------------------------------------------------
765 PROCEDURE Start_Autopay (
766 ERRBUF OUT NOCOPY VARCHAR2,
767 RETCODE OUT NOCOPY NUMBER,
768 p_org_id IN NUMBER DEFAULT NULL,
769 p_run_mode IN VARCHAR2 := NULL,
770 p_customer_id IN NUMBER := NULL,
771 p_relationship_type IN VARCHAR2 := NULL,
772 p_related_cust_account_id IN NUMBER := NULL,
773 p_buy_group_party_id IN NUMBER := NULL,
774 p_select_cust_children_flag IN VARCHAR2 := 'N',
775 p_pay_to_customer IN VARCHAR2 := NULL,
776 p_fund_id IN NUMBER := NULL,
777 p_plan_type IN VARCHAR2 := NULL,
778 p_offer_type IN VARCHAR2 := NULL,
779 p_plan_id IN NUMBER := NULL,
780 p_product_category_id IN NUMBER := NULL,
781 p_product_id IN NUMBER := NULL,
782 p_end_date IN VARCHAR2,
783 p_group_by_offer IN VARCHAR2
784 )
785 IS
786 l_return_status varchar2(1);
787 l_msg_data varchar2(2000);
788 l_msg_count number;
789
790 l_api_name CONSTANT VARCHAR2(30) := 'Start_Autopay';
791 l_api_version CONSTANT NUMBER := 1.0;
792 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
793
794 CURSOR sys_parameter_info_csr IS
795 SELECT autopay_flag
796 , autopay_reason_code_id
797 , autopay_claim_type_id
798 , autopay_periodicity
799 , autopay_periodicity_type
800 FROM ozf_sys_parameters;
801 l_autopay_flag varchar2(1);
802 l_auto_reason_code_id number;
803 l_auto_claim_type_id number;
804 l_autopay_periodicity number;
805 l_autopay_periodicity_type VARCHAR2(30);
806 l_run_mode VARCHAR2(80);
807 l_pay_to VARCHAR2(80);
808 l_rel_cust_name VARCHAR2(70);
809 l_cust_name VARCHAR2(70);
810 l_rlship VARCHAR2(80);
811 l_fund_name VARCHAR2(240);
812 l_plan_type_name VARCHAR2(240);
813 l_plan_name VARCHAR2(240);
814 l_prod_cat_name VARCHAR2(240);
815 l_prod_name VARCHAR2(240);
816 l_buy_gp_name VARCHAR2(70);
817 l_members_flag VARCHAR2(80);
818 l_offer_type_name VARCHAR2(80);
819
820 l_bill_to_site_use_id NUMBER;
821 l_prev_site_use_id NUMBER;
822
823 CURSOR csr_meaning(lkup_type IN VARCHAR2, lkup_code IN VARCHAR2) IS
824 SELECT MEANING
825 FROM OZF_LOOKUPS
826 WHERE lookup_type = lkup_type
827 AND LOOKUP_CODE = lkup_code;
828
829 CURSOR csr_rlship(lkup_code IN VARCHAR2) IS
830 SELECT ar.MEANING
831 FROM ar_lookups ar
832 WHERE ar.lookup_type = 'RELATIONSHIP_TYPE'
833 AND ar.lookup_code = lkup_code;
834
835 CURSOR csr_members(lkup_type IN VARCHAR2, lkup_code IN VARCHAR2) IS
836 SELECT MEANING
837 FROM FND_LOOKUPS
838 WHERE lookup_type = lkup_type
839 AND LOOKUP_CODE = lkup_code;
840
841 CURSOR csr_fund_name(p_fund_id IN NUMBER) IS
842 SELECT f.SHORT_NAME
843 FROM OZF_FUNDS_VL f
844 WHERE f.FUND_ID = p_fund_id;
845
846 CURSOR csr_offer_name(off_id IN NUMBER) IS
847 SELECT QP.DESCRIPTION
848 FROM QP_LIST_HEADERS_VL qp
849 WHERE qp.list_header_id = off_id;
850
851 CURSOR csr_prod_cat_name(prod_cat_id IN NUMBER) IS
852 SELECT MCT.DESCRIPTION
853 FROM MTL_CATEGORIES_TL MCT
854 WHERE MCT.CATEGORY_ID = prod_cat_id;
855
856 CURSOR csr_prod_name(p_product_id IN NUMBER) IS
857 SELECT DESCRIPTION
858 FROM MTL_SYSTEM_ITEMS_KFV
859 WHERE INVENTORY_ITEM_ID = p_product_id
860 AND organization_id = FND_PROFILE.VALUE('AMS_ITEM_ORGANIZATION_ID');
861
862 CURSOR csr_cust_name(cv_cust_account_id IN NUMBER) IS
863 SELECT CONCAT(CONCAT(party.party_name, ' ('), CONCAT(ca.account_number, ') '))
864 FROM hz_cust_accounts ca
865 , hz_parties party
866 WHERE ca.party_id = party.party_id
867 AND ca.cust_account_id = cv_cust_account_id;
868
869 CURSOR csr_get_party_id(cv_cust_account_id IN NUMBER) IS
870 SELECT party_id
871 FROM hz_cust_accounts
872 WHERE cust_account_id = cv_cust_account_id;
873
874 CURSOR csr_party_name(cv_party_id IN NUMBER) IS
875 SELECT party_name
876 FROM hz_parties
877 WHERE party_id = cv_party_id;
878
879 CURSOR csr_offer_pay_name(cv_payment_method IN VARCHAR2) IS
880 SELECT meaning
881 FROM ozf_lookups
882 WHERE lookup_type = 'OZF_AUTOPAY_METHOD'
883 AND lookup_code = cv_payment_method;
884
885 --Multiorg Changes
886 CURSOR operating_unit_csr IS
887 SELECT ou.organization_id org_id
888 FROM hr_operating_units ou
889 WHERE mo_global.check_access(ou.organization_id) = 'Y';
890
891 TYPE EmpCurType IS REF CURSOR;
892 l_emp_csr NUMBER; --EmpCurType;
893
894 l_stmt VARCHAR2(3000);
895 l_funds_util_flt OZF_CLAIM_ACCRUAL_PVT.funds_util_flt_type := NULL;
896
897 l_cust_account_id number;
898 l_amount number;
899 l_customer_info g_customer_info_csr%rowtype;
900 l_cust_name_num VARCHAR2(70);
901 l_offer_pay_method VARCHAR2(30);
902 l_offer_pay_name VARCHAR2(80);
903 l_party_id NUMBER;
904 l_trade_prf_exist BOOLEAN := FALSE;
905 l_ignore NUMBER;
906
907 l_cust_info_invalid BOOLEAN := FALSE;
908 l_prev_cust_account_id NUMBER;
909 l_utiz_currency VARCHAR2(15);
910
911 TYPE trd_prf_tbl_type IS TABLE OF NUMBER
912 INDEX BY BINARY_INTEGER;
913 l_trd_prof_tbl trd_prf_tbl_type;
914 i BINARY_INTEGER := 1;
915
916 l_plan_id NUMBER;
917
918 --Multiorg Changes
919 m NUMBER := 0;
920 l_org_id OZF_UTILITY_PVT.operating_units_tbl;
921
922 BEGIN
923
924 SAVEPOINT AutoPay;
925
926 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '*------------------------------ Claims Autopay Execution Report ------------------------------*');
927 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Execution Starts On: ' || to_char(sysdate,'MM-DD-YYYY HH24:MI:SS'));
928 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '*---------------------------------------------------------------------------------------------*');
929
930 -- Debug Message
931 IF OZF_DEBUG_LOW_ON THEN
932 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
933 FND_MESSAGE.Set_Token('TEXT',l_full_name||': Start');
934 FND_MSG_PUB.Add;
935 END IF;
936
937 --Multiorg Changes
938 MO_GLOBAL.init('OZF');
939
940 IF p_org_id IS NULL THEN
941 MO_GLOBAL.set_policy_context('M',null);
942 OPEN operating_unit_csr;
943 LOOP
944 FETCH operating_unit_csr into l_org_id(m);
945 m := m + 1;
946 EXIT WHEN operating_unit_csr%NOTFOUND;
947 END LOOP;
948 CLOSE operating_unit_csr;
949 ELSE
950 l_org_id(m) := p_org_id;
951 END IF;
952
953 --Multiorg Changes
954 IF (l_org_id.COUNT > 0) THEN
955 FOR m IN l_org_id.FIRST..l_org_id.LAST LOOP
956 BEGIN
957 MO_GLOBAL.set_policy_context('S',l_org_id(m));
958 -- Write OU info to OUT file
959 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Operating Unit: ' || MO_GLOBAL.get_ou_name(l_org_id(m)));
960 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '-----------------------------------------------------');
961 -- Write OU info to LOG file
962 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Operating Unit: ' || MO_GLOBAL.get_ou_name(l_org_id(m)));
963 FND_FILE.PUT_LINE(FND_FILE.LOG, '-----------------------------------------------------');
964
965 IF p_run_mode IS NOT NULL THEN
966 OPEN csr_meaning('OZF_CLAIM_AUTOPAY_RUNMODE', p_run_mode);
967 FETCH csr_meaning INTO l_run_mode;
968 CLOSE csr_meaning;
969 END IF;
970
971 IF p_customer_id IS NOT NULL THEN
972 OPEN csr_cust_name(p_customer_id);
973 FETCH csr_cust_name INTO l_cust_name;
974 CLOSE csr_cust_name;
975
976 IF p_relationship_type IS NOT NULL THEN
977 OPEN csr_rlship(p_relationship_type);
978 FETCH csr_rlship INTO l_rlship;
979 CLOSE csr_rlship;
980
981 IF p_related_cust_account_id IS NOT NULL THEN
982 OPEN csr_cust_name(p_related_cust_account_id);
983 FETCH csr_cust_name INTO l_rel_cust_name;
984 CLOSE csr_cust_name;
985
986 IF p_pay_to_customer IS NOT NULL THEN
987 OPEN csr_meaning('OZF_CLAIM_PAYTO_TYPE', p_pay_to_customer);
988 FETCH csr_meaning INTO l_pay_to;
989 CLOSE csr_meaning;
990 END IF;
991
992 END IF;
993
994 END IF;
995
996 IF p_buy_group_party_id IS NOT NULL THEN
997 OPEN csr_party_name(p_buy_group_party_id);
998 FETCH csr_party_name INTO l_buy_gp_name;
999 CLOSE csr_party_name;
1000 END IF;
1001
1002 IF p_select_cust_children_flag IS NOT NULL THEN
1003 OPEN csr_members('YES_NO', p_select_cust_children_flag);
1004 FETCH csr_members INTO l_members_flag;
1005 CLOSE csr_members;
1006 END IF;
1007
1008 END IF;
1009
1010 IF p_fund_id IS NOT NULL THEN
1011 OPEN csr_fund_name(p_fund_id);
1012 FETCH csr_fund_name INTO l_fund_name;
1013 CLOSE csr_fund_name;
1014 END IF;
1015
1016 IF p_plan_type IS NOT NULL THEN
1017 OPEN csr_meaning('OZF_CLAIM_ASSO_ACT_TYPE', p_plan_type);
1018 FETCH csr_meaning INTO l_plan_type_name;
1019 CLOSE csr_meaning;
1020
1021 IF p_plan_id IS NOT NULL THEN
1022 OPEN csr_offer_name(p_plan_id);
1023 FETCH csr_offer_name INTO l_plan_name;
1024 CLOSE csr_offer_name;
1025 END IF;
1026 END IF;
1027
1028 IF p_offer_type IS NOT NULL THEN
1029 OPEN csr_meaning('OZF_OFFER_TYPE', p_offer_type);
1030 FETCH csr_meaning INTO l_offer_type_name;
1031 CLOSE csr_meaning;
1032 END IF;
1033
1034 IF p_product_category_id IS NOT NULL THEN
1035 OPEN csr_prod_cat_name(p_product_category_id);
1036 FETCH csr_prod_cat_name INTO l_prod_cat_name;
1037 CLOSE csr_prod_cat_name;
1038 END IF;
1039
1040 IF p_product_id IS NOT NULL THEN
1041 OPEN csr_prod_name(p_product_id);
1042 FETCH csr_prod_name INTO l_prod_name;
1043 CLOSE csr_prod_name;
1044 END IF;
1045
1046 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, rpad('Run Mode', 40, ' ') || ': ' || l_run_mode);
1047 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, rpad('Customer Name', 40, ' ') || ': '|| l_cust_name);
1048 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, rpad('Relationship', 40, ' ') || ': ' || l_rlship);
1049 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, rpad('Related Customer', 40, ' ') || ': ' || l_rel_cust_name);
1050 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, rpad('Buying Group and Members', 40, ' ') || ': ' || l_buy_gp_name);
1051 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, rpad('Include All Member Earnings', 40, ' ') || ': ' || l_members_flag);
1052 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, rpad('Pay To', 40, ' ') || ': ' || l_pay_to);
1053 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, rpad('Fund Name', 40, ' ') || ': ' || l_fund_name);
1054 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, rpad('Activity Type', 40, ' ') || ': ' || l_plan_type_name);
1055 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, rpad('Offer Type', 40, ' ') || ': ' || l_offer_type_name);
1056 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, rpad('Activity Name', 40, ' ') || ': ' || l_plan_name);
1057 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, rpad('Product Category', 40, ' ') || ': ' || l_prod_cat_name);
1058 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, rpad('Product', 40, ' ') || ': ' || l_prod_name);
1059 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, rpad('End Date', 40, ' ') || ': ' || p_end_date);
1060 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, rpad('Group By Offer', 40, ' ') || ': ' || p_group_by_offer);
1061 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '*---------------------------------------------------------------------------------------------*');
1062
1063 /*------------ Autopay starts ---------------*/
1064
1065 -- get autopay_flag, reason_code_id
1066 OPEN sys_parameter_info_csr;
1067 FETCH sys_parameter_info_csr INTO l_autopay_flag
1068 , l_auto_reason_code_id
1069 , l_auto_claim_type_id
1070 , l_autopay_periodicity
1071 , l_autopay_periodicity_type;
1072 CLOSE sys_parameter_info_csr;
1073
1074 -- check reason_code and claim_type from sys_parameters.
1075 IF l_auto_reason_code_id is NULL THEN
1076 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1077 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_REASON_CD_MISSING');
1078 FND_MSG_PUB.add;
1079 END IF;
1080 RAISE FND_API.g_exc_unexpected_error;
1081 END IF;
1082
1083 IF l_auto_claim_type_id is NULL THEN
1084 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1085 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_CLAIM_TYPE_MISSING');
1086 FND_MSG_PUB.add;
1087 END IF;
1088 RAISE FND_API.g_exc_unexpected_error;
1089 END IF;
1090
1091 -- construct the following sql based on the inputs
1092 l_funds_util_flt.run_mode := p_run_mode;
1093 l_funds_util_flt.utilization_type := null;
1094 l_funds_util_flt.offer_type := p_offer_type;
1095 l_funds_util_flt.activity_type := p_plan_type;
1096 l_funds_util_flt.activity_id := p_plan_id;
1097 l_funds_util_flt.fund_id := p_fund_id;
1098 l_funds_util_flt.adjustment_type_id := null;
1099 IF p_product_id IS NOT NULL THEN
1100 l_funds_util_flt.product_level_type := 'PRODUCT';
1101 l_funds_util_flt.product_id := p_product_id;
1102 ELSIF p_product_category_id IS NOT NULL THEN
1103 l_funds_util_flt.product_level_type := 'FAMILY';
1104 l_funds_util_flt.product_id := p_product_category_id;
1105 END IF;
1106
1107 -- additional filter conditions
1108 l_funds_util_flt.cust_account_id := p_customer_id;
1109 l_funds_util_flt.relationship_type := p_relationship_type;
1110 l_funds_util_flt.related_cust_account_id := p_related_cust_account_id;
1111 l_funds_util_flt.buy_group_party_id := p_buy_group_party_id;
1112 l_funds_util_flt.select_cust_children_flag := p_select_cust_children_flag;
1113 l_funds_util_flt.pay_to_customer := p_pay_to_customer;
1114 l_funds_util_flt.end_date := FND_DATE.CANONICAL_TO_DATE(p_end_date);
1115 l_funds_util_flt.group_by_offer := NVL(p_group_by_offer,'N'); --R12
1116
1117 -- Changes for FXGL Enhancement
1118 -- l_funds_util_flt.utiz_currency_code is null here
1119 -- and all currency records are retrieved but they are
1120 -- grouped by currency_code
1121 -- The amount_remaining is in utiz_curency and not in functional currency
1122
1123 OZF_Claim_Accrual_Pvt.Get_Utiz_Sql_Stmt(
1124 p_api_version => 1.0
1125 ,p_init_msg_list => FND_API.g_false
1126 ,p_commit => FND_API.g_false
1127 ,p_validation_level => FND_API.g_valid_level_full
1128 ,x_return_status => l_return_status
1129 ,x_msg_count => l_msg_count
1130 ,x_msg_data => l_msg_data
1131 ,p_summary_view => 'AUTOPAY'
1132 ,p_funds_util_flt => l_funds_util_flt
1133 ,p_cust_account_id => p_customer_id
1134 ,x_utiz_sql_stmt => l_stmt
1135 );
1136
1137 IF l_return_status <> FND_API.g_ret_sts_success THEN
1138 RAISE FND_API.g_exc_error;
1139 END IF;
1140
1141 -- log query for debugging
1142 IF OZF_DEBUG_LOW_ON THEN
1143 OZF_UTILITY_PVT.write_conc_log;
1144 END IF;
1145
1146 l_emp_csr := DBMS_SQL.open_cursor;
1147 FND_DSQL.set_cursor(l_emp_csr);
1148 DBMS_SQL.parse(l_emp_csr, l_stmt, DBMS_SQL.native);
1149 DBMS_SQL.define_column(l_emp_csr, 1, l_offer_pay_method, 30);
1150 DBMS_SQL.define_column(l_emp_csr, 2, l_amount);
1151 DBMS_SQL.define_column(l_emp_csr, 3, l_utiz_currency, 15 );
1152
1153
1154 --R12.1 enhancement add l_bill_to_site_use_id and fetch its value
1155 -- This will be used to group the customer earnings.
1156 DBMS_SQL.define_column(l_emp_csr, 4, l_bill_to_site_use_id );
1157
1158
1159 IF p_customer_id IS NULL THEN
1160 DBMS_SQL.define_column(l_emp_csr, 5, l_cust_account_id);
1161 END IF;
1162
1163 IF NVL(p_group_by_offer,'N') = 'Y' AND p_customer_id IS NULL THEN
1164 DBMS_SQL.define_column(l_emp_csr, 6, l_plan_id);
1165 ELSIF NVL(p_group_by_offer,'N') = 'Y' AND p_customer_id IS NOT NULL THEN
1166 DBMS_SQL.define_column(l_emp_csr, 5, l_plan_id);
1167 END IF;
1168
1169 FND_DSQL.do_binds;
1170
1171 l_ignore := DBMS_SQL.execute(l_emp_csr);
1172 LOOP
1173 FND_MSG_PUB.initialize;
1174
1175 IF DBMS_SQL.fetch_rows(l_emp_csr) > 0 THEN
1176 DBMS_SQL.column_value(l_emp_csr, 1, l_offer_pay_method);
1177 DBMS_SQL.column_value(l_emp_csr, 2, l_amount);
1178 DBMS_SQL.column_value(l_emp_csr, 3, l_utiz_currency);
1179 DBMS_SQL.column_value(l_emp_csr, 4, l_bill_to_site_use_id); --R12.1 enhancement nirprasa
1180
1181 IF p_customer_id IS NULL THEN
1182 DBMS_SQL.column_value(l_emp_csr, 5, l_cust_account_id);
1183 ELSE
1184 l_cust_account_id := p_customer_id;
1185 END IF;
1186
1187 IF NVL(p_group_by_offer,'N') = 'Y' AND p_customer_id IS NULL THEN
1188 DBMS_SQL.column_value(l_emp_csr, 6, l_plan_id);
1189 l_funds_util_flt.activity_id := l_plan_id;
1190 ELSIF NVL(p_group_by_offer,'N') = 'Y' AND p_customer_id IS NOT NULL THEN
1191 DBMS_SQL.column_value(l_emp_csr, 5, l_plan_id);
1192 l_funds_util_flt.activity_id := l_plan_id;
1193 ELSE
1194 l_funds_util_flt.activity_id := NULL;
1195 END IF;
1196
1197 -- FXGL Enhancement : Add utiz_currency_code to l_funds_util_flt
1198 -- This is a required filter
1199 -- This will ensure assoc happens for each currency line
1200
1201 l_funds_util_flt.utiz_currency_code := l_utiz_currency;
1202
1203
1204 -- In case of buying group/related customer accruals,
1205 -- the amount can be paid either to buying group/related customer or
1206 -- to claiming customer based on p_pay_to_customer.
1207 IF p_pay_to_customer = 'RELATED'
1208 THEN
1209 IF p_related_cust_account_id IS NOT NULL THEN
1210 l_cust_account_id := p_related_cust_account_id;
1211 END IF;
1212 END IF;
1213
1214 BEGIN
1215 SAVEPOINT AUTOPAY_CUST;
1216
1217 IF l_cust_account_id is not null THEN
1218
1219 --R12.1 enhancement the call should once per site instead of onec per account.
1220
1221
1222
1223 /*IF l_prev_cust_account_id IS NULL OR
1224 l_cust_account_id <> l_prev_cust_account_id THEN*/
1225
1226 IF l_prev_site_use_id IS NULL OR
1227 l_bill_to_site_use_id <> l_prev_site_use_id THEN
1228
1229 l_cust_name_num := NULL;
1230 l_customer_info := NULL;
1231 l_party_id := NULL;
1232
1233 -- Get customer information for log message purpose
1234 OPEN csr_cust_name(l_cust_account_id);
1235 FETCH csr_cust_name INTO l_cust_name_num;
1236 CLOSE csr_cust_name;
1237
1238
1239 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, rpad('l_bill_to_site_use_id', 40, ' ') || ': ' || l_bill_to_site_use_id);
1240
1241 -- 1. get trade profile by site_use_id
1242 OPEN g_site_info_csr(l_bill_to_site_use_id);
1243 FETCH g_site_info_csr into l_customer_info;
1244 IF g_site_info_csr%NOTFOUND THEN
1245 l_trade_prf_exist := FALSE;
1246 ELSE
1247 l_trade_prf_exist := TRUE;
1248 END IF;
1249 CLOSE g_site_info_csr;
1250
1251 -- 2. if trade profile is not there for site,
1252 -- then get trade profile by account level
1253 IF NOT l_trade_prf_exist THEN
1254
1255
1256
1257
1258 OPEN g_customer_info_csr(l_cust_account_id);
1259 FETCH g_customer_info_csr into l_customer_info;
1260 IF g_customer_info_csr%NOTFOUND THEN
1261 l_trade_prf_exist := FALSE;
1262 ELSE
1263 l_trade_prf_exist := TRUE;
1264 END IF;
1265 CLOSE g_customer_info_csr;
1266
1267 END IF;
1268
1269 -- 3. if trade profile is not there for customer,
1270 -- then get trade profile by party_id level
1271 IF NOT l_trade_prf_exist THEN
1272 OPEN csr_get_party_id(l_cust_account_id);
1273 FETCH csr_get_party_id INTO l_party_id;
1274 CLOSE csr_get_party_id;
1275
1276 IF l_party_id IS NOT NULL THEN
1277 OPEN g_party_trade_info_csr(l_party_id);
1278 FETCH g_party_trade_info_csr INTO l_customer_info;
1279 IF g_party_trade_info_csr%NOTFOUND THEN
1280 l_trade_prf_exist := FALSE;
1281 ELSE
1282 l_trade_prf_exist := TRUE;
1283 END IF;
1284 CLOSE g_party_trade_info_csr;
1285 END IF;
1286 END IF;
1287
1288 l_customer_info.cust_account_id := l_cust_account_id;
1289 validate_customer_info (
1290 p_customer_info => l_customer_info,
1291 x_return_status => l_return_status
1292 );
1293 -- skip this customer if we can not get all the info.
1294 IF l_return_status = FND_API.g_ret_sts_error or
1295 l_return_status = FND_API.g_ret_sts_unexp_error THEN
1296 l_cust_info_invalid := true;
1297 ELSE
1298 l_cust_info_invalid := FALSE;
1299 END IF;
1300
1301 END IF;
1302 l_prev_site_use_id := l_bill_to_site_use_id;
1303
1304 IF p_customer_id IS NULL THEN
1305 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Customer Name : '||l_cust_name_num );
1306 END IF;
1307 IF l_offer_pay_method IS NOT NULL THEN
1308 OPEN csr_offer_pay_name(l_offer_pay_method);
1309 FETCH csr_offer_pay_name INTO l_offer_pay_name;
1310 CLOSE csr_offer_pay_name;
1311 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Offer Payment Method: '||l_offer_pay_name);
1312 END IF;
1313
1314 IF l_cust_info_invalid THEN
1315 RAISE FND_API.g_exc_unexpected_error;
1316 END IF;
1317
1318 IF l_trade_prf_exist THEN
1319
1320 IF l_customer_info.autopay_flag = FND_API.g_true THEN
1321 l_funds_util_flt.bill_to_site_use_id := l_bill_to_site_use_id; --R12.1 enhancements
1322
1323 FND_FILE.PUT_LINE(FND_FILE.LOG, '/*--- Start AUTOPAY for customer '||l_cust_name_num||' ---*/');
1324 create_claim_for_cust(p_customer_info => l_customer_info,
1325 p_amount => l_amount,
1326 p_mode => 'N',
1327 p_auto_reason_code_id => l_auto_reason_code_id,
1328 p_auto_claim_type_id => l_auto_claim_type_id,
1329 p_autopay_periodicity => l_autopay_periodicity,
1330 p_autopay_periodicity_type => l_autopay_periodicity_type,
1331 p_offer_payment_method => l_offer_pay_method,
1332 p_funds_util_flt => l_funds_util_flt,
1333 x_return_status => l_return_status
1334 );
1335 IF l_return_status = FND_API.g_ret_sts_error THEN
1336 RAISE FND_API.g_exc_error;
1337 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1338 RAISE FND_API.g_exc_unexpected_error;
1339 END IF;
1340
1341 -- Store Trade Profile for later updation
1342 IF l_trade_prf_exist THEN
1343 l_trd_prof_tbl(i) := l_customer_info.trade_profile_id;
1344 END IF;
1345
1346 --FND_FILE.PUT_LINE(FND_FILE.LOG, 'Claim Amount = '||l_amount);
1347 FND_FILE.PUT_LINE(FND_FILE.LOG, '===> Success.');
1348 FND_FILE.PUT_LINE(FND_FILE.LOG, '/*--- End ---*/');
1349 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
1350 --FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Claim Amount : '||l_amount );
1351 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Status : Auto Pay Success. ');
1352 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '');
1353 OZF_UTILITY_PVT.write_conc_log;
1354 ELSE
1355 FND_FILE.PUT_LINE(FND_FILE.LOG, '/*--- Start AUTOPAY for customer '||l_cust_name_num||' ---*/');
1356 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Autopay flag is not turned on in Trade Profile for customer '||l_cust_name_num);
1357 FND_FILE.PUT_LINE(FND_FILE.LOG, '/*--- End ---*/');
1358 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
1359 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Status : Auto Pay Failed. ');
1360 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Autopay flag is not turned on in Trade Profile for this customer. ');
1361 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '');
1362 END IF; -- end of if autopay flag is turning on
1363 ELSE
1364 FND_FILE.PUT_LINE(FND_FILE.LOG, '/*--- Start AUTOPAY for customer '||l_cust_name_num||' ---*/');
1365 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Trade Profile is not existing for customer '||l_cust_name_num);
1366 FND_FILE.PUT_LINE(FND_FILE.LOG, '/*--- End ---*/');
1367 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
1368 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Status : Auto Pay Failed. ');
1369 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Trade Profile is not existing for this customer.');
1370 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '');
1371 END IF; -- end of if l_trade_prf_exist
1372 END IF; -- end of if l_cust_account_id is not null
1373
1374 EXCEPTION
1375 WHEN FND_API.G_EXC_ERROR THEN
1376 ROLLBACK TO AUTOPAY_CUST;
1377 FND_FILE.PUT_LINE(FND_FILE.LOG, '===> Failed.');
1378 OZF_UTILITY_PVT.write_conc_log;
1379 FND_FILE.PUT_LINE(FND_FILE.LOG, '/*--- End ---*/');
1380 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
1381 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Status : Auto Pay Failed. ');
1382 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Error : ' || FND_MSG_PUB.get(FND_MSG_PUB.count_msg, FND_API.g_false));
1383 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '');
1384 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1385 ROLLBACK TO AUTOPAY_CUST;
1386 FND_FILE.PUT_LINE(FND_FILE.LOG, '===> Failed.');
1387 OZF_UTILITY_PVT.write_conc_log;
1388 FND_FILE.PUT_LINE(FND_FILE.LOG, '/*--- End ---*/');
1389 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
1390 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Status : Auto Pay Failed. ');
1391 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Error : ' || FND_MSG_PUB.get(FND_MSG_PUB.count_msg, FND_API.g_false));
1392 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '');
1393
1394 WHEN OTHERS THEN
1395 ROLLBACK TO AUTOPAY_CUST;
1396 FND_FILE.PUT_LINE(FND_FILE.LOG, '===> Failed.');
1397 IF OZF_DEBUG_LOW_ON THEN
1398 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
1399 FND_MESSAGE.Set_Token('TEXT',sqlerrm);
1400 FND_MSG_PUB.Add;
1401 END IF;
1402 OZF_UTILITY_PVT.write_conc_log;
1403 FND_FILE.PUT_LINE(FND_FILE.LOG, '/*--- End ---*/');
1404 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
1405 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Status : Auto Pay Failed. ');
1406 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Error : ' || SQLCODE||SQLERRM);
1407 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '');
1408 END;
1409
1410 ELSE
1411 EXIT;
1412 END IF;
1413 END LOOP;
1414 --CLOSE l_emp_csr;
1415 DBMS_SQL.close_cursor(l_emp_csr);
1416
1417 -- update the trade_profiles together
1418 FORALL i IN 1..l_trd_prof_tbl.COUNT
1419 UPDATE OZF_CUST_TRD_PRFLS_ALL
1420 SET last_paid_date = SYSDATE
1421 WHERE trade_profile_id = l_trd_prof_tbl(i) ;
1422
1423
1424 -- Debug Message
1425 IF OZF_DEBUG_LOW_ON THEN
1426 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
1427 FND_MESSAGE.Set_Token('TEXT',l_full_name||': End');
1428 FND_MSG_PUB.Add;
1429 END IF;
1430
1431 -- Write all messages to a log
1432 OZF_UTILITY_PVT.Write_Conc_Log;
1433
1434 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '*---------------------------------------------------------------------------------------------*');
1435 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Execution Status: Successful' );
1436 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Execution Ends On: ' || to_char(sysdate,'MM-DD-YYYY HH24:MI:SS'));
1437 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '*---------------------------------------------------------------------------------------------*');
1438
1439 EXCEPTION
1440 WHEN FND_API.g_exc_error THEN
1441 ROLLBACK TO AutoPay;
1442
1443 OZF_UTILITY_PVT.Write_Conc_Log;
1444 ERRBUF := l_msg_data;
1445 RETCODE := 2;
1446 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Execution Status: Failure (Error:' || FND_MSG_PUB.get(1, FND_API.g_false)||')');
1447 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Execution Ends On: ' || to_char(sysdate,'MM-DD-YYYY HH24:MI:SS'));
1448 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '*---------------------------------------------------------------------------------------------*');
1449
1450 WHEN FND_API.g_exc_unexpected_error THEN
1451 ROLLBACK TO AutoPay;
1452
1453 OZF_UTILITY_PVT.Write_Conc_Log;
1454 ERRBUF := l_msg_data;
1455 RETCODE := 1; -- show status as warning if claim type/reason is missing, Fix for 5158782
1456 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Execution Status: ( Warning:' || FND_MSG_PUB.get(1, FND_API.g_false)||')');
1457 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Execution Ends On: ' || to_char(sysdate,'MM-DD-YYYY HH24:MI:SS'));
1458 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '*---------------------------------------------------------------------------------------------*');
1459
1460 WHEN OTHERS THEN
1461 ROLLBACK TO AutoPay;
1462
1463 OZF_UTILITY_PVT.Write_Conc_Log;
1464 ERRBUF := l_msg_data;
1465 RETCODE := 2;
1466 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Execution Status: Failure (Error:' ||SQLCODE||SQLERRM || ')');
1467 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Execution Ends On: ' || to_char(sysdate,'MM-DD-YYYY HH24:MI:SS'));
1468 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '*---------------------------------------------------------------------------------------------*');
1469
1470 END;
1471 END LOOP;
1472 END IF;
1473 END Start_Autopay;
1474
1475 END OZF_AUTOPAY_PVT;
[Home] [Help]