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