[Home] [Help]
PACKAGE BODY: APPS.OZF_GL_INTERFACE_PVT
Source
1 PACKAGE BODY OZF_GL_INTERFACE_PVT AS
2 /* $Header: ozfvglib.pls 120.34.12020000.5 2013/02/18 11:29:53 bkunjan ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OZF_GL_INTERFACE_PVT';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'ozfvglib.pls';
6
7 OZF_DEBUG_HIGH_ON BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_high);
8 OZF_DEBUG_LOW_ON BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low);
9 G_CLAIM_ID NUMBER;
10
11 ---------------------------------------------------------------------
12 --Bugfix 7431334 - Pushed get_org_id function at top for forward declaration as
13 --many procedures refering this function in this package.
14
15 --ER 13069257
16 TYPE actg_rec_type IS RECORD (
17 CODE_COMBINATION_ID NUMBER
18 ,ACCOUNT_TYPE VARCHAR2(30)
19 );
20
21 TYPE actg_tbl_type is TABLE OF actg_rec_type INDEX BY BINARY_INTEGER;
22
23 /*FUNCTION get_org_id (
24 p_source_id in number
25 , p_source_table in varchar2
26 )
27 RETURN NUMBER
28 IS
29 l_org_id number;
30
31 CURSOR claim_org_id_csr (p_id in number) IS
32 select org_id
33 from ozf_claims_all
34 where claim_id = p_id;
35
36 CURSOR util_org_id_csr (p_id in number) IS
37 select org_id
38 from ozf_funds_utilized_all_b
39 where utilization_id = p_id;
40
41 BEGIN
42
43 IF p_source_table = 'OZF_FUNDS_UTILIZED_ALL_B' THEN
44 OPEN util_org_id_csr (p_source_id);
45 FETCH util_org_id_csr INTO l_org_id;
46 CLOSE util_org_id_csr;
47 ELSIF p_source_table = 'OZF_CLAIMS_ALL' THEN
48 OPEN claim_org_id_csr (p_source_id);
49 FETCH claim_org_id_csr INTO l_org_id;
50 CLOSE claim_org_id_csr;
51 END IF;
52
53 RETURN l_org_id;
54
55 EXCEPTION
56 WHEN OTHERS THEN
57 RETURN NULL;
58 END;
59 */
60 ---------------------------------------------------------------------
61 PROCEDURE Get_GL_Account(
62 p_api_version IN NUMBER
63 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
64 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
65 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
66
67 ,x_return_status OUT NOCOPY VARCHAR2
68 ,x_msg_data OUT NOCOPY VARCHAR2
69 ,x_msg_count OUT NOCOPY NUMBER
70
71 ,p_source_id IN NUMBER
72 ,p_source_table IN VARCHAR2
73 ,p_account_type IN VARCHAR2
74 ,p_event_type IN VARCHAR2 DEFAULT NULL
75 ,x_cc_id_tbl OUT NOCOPY CC_ID_TBL)
76 IS
77 l_api_name CONSTANT VARCHAR2(30) := 'Get_GL_Account';
78 l_api_version CONSTANT NUMBER := 1.0;
79 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
80 -- in variables
81 l_claim_id number;
82 -- accounts
83 l_expense_account number;
84 l_accrual_liability number;
85 l_ven_clearing number;
86 l_rec_clearing number;
87 -- claim type accounts
88 l_ct_ven_clearing number;
89 l_ct_rec_clearing number;
90
91 l_order_id number;
92 l_line_id number;
93 l_plan_id NUMBER;
94 l_object_type VARCHAR2(30);
95 l_object_id NUMBER;
96 l_exchange_rate NUMBER;
97 l_exchange_rate_type VARCHAR2(150);
98 l_exchange_rate_date DATE;
99
100 -- out variables
101 l_ccid number;
102 l_concat_segs varchar2(2000);
103 l_concat_ids varchar2(2000);
104 l_concat_descrs varchar2(2000);
105
106 l_account_type VARCHAR2(30) := p_account_type;
107 -- table and counter
108 TYPE acct_gen_rec IS RECORD (
109 amount number,
110 acctd_amount number,
111 currency_code varchar2(15),
112 utilization_id number,
113 line_util_id number,
114 claim_id number,
115 budget_id number,
116 offer_type varchar2(30),
117 offer_id number,
118 line_type varchar2(80),
119 line_id number,
120 item_type varchar2(30),
121 item_id number,
122 price_adj_id number,
123 cust_account_id number
124 );
125
126 TYPE acct_gen_tbl is TABLE of acct_gen_rec;
127
128 l_acct_gen_tbl acct_gen_tbl;
129 l_rec_num number := 1;
130
131 -- get claim_id for a claim line
132 CURSOR get_claim_id_csr(cv_claim_line_id in number) IS
133 select claim_id
134 from ozf_claim_lines_all
135 where claim_line_id = cv_claim_line_id;
136
137 -- default accounts from claim type
138 CURSOR get_claim_type_acc_csr(cv_claim_id in number) IS
139 select ct.gl_id_ded_clearing -- vendor clearing account
140 , ct.gl_id_ded_adj_clearing -- receivables clearing account
141 from ozf_claim_types_all_b ct
142 , ozf_claims_all c
143 where ct.claim_type_id = c.claim_type_id
144 and c.claim_id = cv_claim_id;
145
146 -- default accounts from System Parameter
147 CURSOR claim_get_sys_param_csr(p_id in number) IS
148 select osp.gl_id_ded_adj -- expense account
149 , osp.gl_id_accr_promo_liab -- accrual liability account
150 , osp.gl_id_ded_clearing -- vendor clearing account
151 , osp.gl_rec_clearing_account -- receivables clearing account
152 FROM ozf_sys_parameters_all osp
153 , ozf_claims_all oc
154 WHERE osp.org_id = NVL(oc.org_id, -99)
155 AND oc.claim_id = p_id;
156
157 -- get accrual totals based on a claim and accrual currency
158 -- (used for posting to clearing accounts)
159 CURSOR get_claim_line_amt_csr(p_id in number) IS
160 SELECT l.claim_currency_amount
161 , l.acctd_amount
162 , c.currency_code
163 , c.claim_id
164 FROM ozf_claim_lines_all l
165 , ozf_claims_all c
166 WHERE l.claim_id = c.claim_id
167 --AND l.earnings_associated_flag = 'T'
168 AND l.claim_line_id = p_id;
169
170 -- get accrual details for one accrual
171 --(used when creating accruals/adjustements)
172 -- Fix for Bug 8846853
173 CURSOR get_util_acc_csr(p_id in number) IS
174 select u.plan_curr_amount
175 , u.acctd_amount
176 --, u.currency_code
177 , u.utilization_id
178 , u.fund_id
179 , u.component_type
180 , u.component_id
181 , u.object_type
182 , u.object_id
183 , u.product_level_type
184 , u.product_id
185 , u.price_adjustment_id
186 , u.cust_account_id
187 , u.plan_id
188 , u.object_type
189 , u.object_id
190 , u.exchange_rate
191 , u.exchange_rate_type
192 , u.exchange_rate_date
193 from ozf_funds_utilized_all_b u
194 where u.utilization_id = p_id;
195
196 -- get accrual details based on a claim
197 --(used when off-setting accruals paid through claims)
198 CURSOR get_claim_acc_csr(p_id in number) IS
199 select u.amount
200 , u.utilized_acctd_amount
201 , u.currency_code
202 , u.utilization_id
203 , u.claim_line_util_id
204 , f.fund_id
205 , f.component_type
206 , f.component_id
207 , f.object_type
208 , f.object_id
209 , f.product_level_type
210 , f.product_id
211 , f.price_adjustment_id
212 , f.cust_account_id
213 from ozf_claim_lines_util_all u
214 , ozf_claim_lines_all l
215 , ozf_claims_all c
216 , ozf_funds_utilized_all_b f
217 where u.claim_line_id = l.claim_line_id
218 and u.utilization_id = f.utilization_id
219 and l.claim_id = c.claim_id
220 and c.claim_id = p_id
221 order by u.claim_line_util_id asc;
222
223
224
225 BEGIN
226 -- Standard begin of API savepoint
227 SAVEPOINT Get_GL_Account_PVT;
228 -- Standard call to check for call compatibility.
229 IF NOT FND_API.Compatible_API_Call (
230 l_api_version,
231 p_api_version,
232 l_api_name,
233 G_PKG_NAME)
234 THEN
235 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
236 END IF;
237 -- Debug Message
238 IF OZF_DEBUG_LOW_ON THEN
239 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
240 FND_MESSAGE.Set_Token('TEXT',l_full_name||': Start');
241 FND_MSG_PUB.Add;
242 END IF;
243 --Initialize message list if p_init_msg_list is TRUE.
244 IF FND_API.To_Boolean (p_init_msg_list) THEN
245 FND_MSG_PUB.initialize;
246 END IF;
247 -- Initialize API return status to sucess
248 x_return_status := FND_API.G_RET_STS_SUCCESS;
249
250 IF OZF_DEBUG_LOW_ON THEN
251 OZF_Utility_PVT.debug_message('p_account_type : '||p_account_type);
252 OZF_Utility_PVT.debug_message('p_source_table : '||p_source_table);
253 OZF_Utility_PVT.debug_message('p_source_id : '||p_source_id);
254 END IF;
255 l_acct_gen_tbl := acct_gen_tbl();
256
257 --ER#9382547 ChRM-SLA Uptake
258 IF (upper(p_source_table) = 'OZF_CLAIM_LINES_ALL' )THEN
259
260 -- get the claim id
261 OPEN get_claim_id_csr(p_source_id);
262 FETCH get_claim_id_csr INTO l_claim_id;
263 CLOSE get_claim_id_csr;
264
265
266 -- get default accounts from Claim Type
267 OPEN get_claim_type_acc_csr(l_claim_id);
268 FETCH get_claim_type_acc_csr INTO l_ct_ven_clearing,
269 l_ct_rec_clearing;
270 CLOSE get_claim_type_acc_csr;
271
272 -- get default accounts from system parameters
273 OPEN claim_get_sys_param_csr(l_claim_id);
274 FETCH claim_get_sys_param_csr INTO l_expense_account,
275 l_accrual_liability,
276 l_ven_clearing,
277 l_rec_clearing;
278 CLOSE claim_get_sys_param_csr;
279
280 -- take vendor clearing from claim type if available
281 IF l_ct_ven_clearing is not null AND
282 l_ct_ven_clearing <> -10
283 THEN
284 l_ven_clearing := l_ct_ven_clearing;
285 END IF;
286
287 -- take receivable clearing from claim type if available
288 IF l_ct_rec_clearing is not null AND
289 l_ct_rec_clearing <> -10
290 THEN
291 l_rec_clearing := l_ct_rec_clearing;
292 END IF;
293
294 IF p_account_type = 'VEN_CLEARING' OR
295 p_account_type = 'REC_CLEARING'
296 THEN
297 -- returns only one record
298 OPEN get_claim_line_amt_csr(p_source_id);
299 l_acct_gen_tbl.extend;
300 FETCH get_claim_line_amt_csr INTO
301 l_acct_gen_tbl(l_rec_num).amount,
302 l_acct_gen_tbl(l_rec_num).acctd_amount,
303 l_acct_gen_tbl(l_rec_num).currency_code,
304 l_acct_gen_tbl(l_rec_num).claim_id;
305 CLOSE get_claim_line_amt_csr;
306 END IF;
307
308 END IF;
309
310 IF upper(p_source_table) = 'OZF_FUNDS_UTILIZED_ALL_B' THEN
311
312 IF p_account_type = 'ACCRUAL_LIABILITY' OR
313 p_account_type = 'EXPENSE ACCOUNT'
314 THEN
315 OPEN get_util_acc_csr(p_source_id);
316 l_acct_gen_tbl.extend;
317 FETCH get_util_acc_csr INTO
318 l_acct_gen_tbl(l_rec_num).amount,
319 l_acct_gen_tbl(l_rec_num).acctd_amount,
320 --l_acct_gen_tbl(l_rec_num).currency_code,
321 l_acct_gen_tbl(l_rec_num).utilization_id,
322 l_acct_gen_tbl(l_rec_num).budget_id,
323 l_acct_gen_tbl(l_rec_num).offer_type,
324 l_acct_gen_tbl(l_rec_num).offer_id,
325 l_acct_gen_tbl(l_rec_num).line_type,
326 l_acct_gen_tbl(l_rec_num).line_id,
327 l_acct_gen_tbl(l_rec_num).item_type,
328 l_acct_gen_tbl(l_rec_num).item_id,
329 l_acct_gen_tbl(l_rec_num).price_adj_id,
330 l_acct_gen_tbl(l_rec_num).cust_account_id,
331 l_plan_id,
332 l_object_type,
333 l_object_id,
334 l_exchange_rate,
335 l_exchange_rate_type,
336 l_exchange_rate_date;
337 -- send offer id if the type if OFFR
338 IF l_acct_gen_tbl(l_rec_num).offer_type <> 'OFFR' THEN
339 l_acct_gen_tbl(l_rec_num).offer_id := null;
340 END IF;
341 CLOSE get_util_acc_csr;
342 END IF;
343 ELSIF upper(p_source_table) = 'OZF_CLAIMS_ALL' THEN
344 l_claim_id := p_source_id;
345
346 IF p_account_type = 'ACCRUAL_LIABILITY'
347 OR p_account_type = 'EXPENSE ACCOUNT' THEN
348 OPEN get_claim_acc_csr(p_source_id);
349 LOOP
350 l_acct_gen_tbl.extend;
351 FETCH get_claim_acc_csr INTO
352 l_acct_gen_tbl(l_rec_num).amount,
353 l_acct_gen_tbl(l_rec_num).acctd_amount,
354 l_acct_gen_tbl(l_rec_num).currency_code,
355 l_acct_gen_tbl(l_rec_num).utilization_id,
356 l_acct_gen_tbl(l_rec_num).line_util_id,
357 l_acct_gen_tbl(l_rec_num).budget_id,
358 l_acct_gen_tbl(l_rec_num).offer_type,
359 l_acct_gen_tbl(l_rec_num).offer_id,
360 l_acct_gen_tbl(l_rec_num).line_type,
361 l_acct_gen_tbl(l_rec_num).line_id,
362 l_acct_gen_tbl(l_rec_num).item_type,
363 l_acct_gen_tbl(l_rec_num).item_id,
364 l_acct_gen_tbl(l_rec_num).price_adj_id,
365 l_acct_gen_tbl(l_rec_num).cust_account_id;
366
367 -- send offer id if the type if OFFR
368 IF l_acct_gen_tbl(l_rec_num).offer_type <> 'OFFR' THEN
369 l_acct_gen_tbl(l_rec_num).offer_id := null;
370 END IF;
371 -- set the claim id
372 l_acct_gen_tbl(l_rec_num).claim_id := p_source_id;
373 EXIT WHEN get_claim_acc_csr%notfound;
374 l_rec_num := l_rec_num + 1;
375 END LOOP;
376 CLOSE get_claim_acc_csr;
377 END IF;
378 END IF;
379
380 x_cc_id_tbl := CC_ID_TBL();
381
382 IF OZF_DEBUG_LOW_ON THEN
383 OZF_Utility_PVT.debug_message('l_acct_gen_tbl.count Before calling account generator API : '||l_acct_gen_tbl.count);
384 END IF;
385
386 FOR i in 1..l_acct_gen_tbl.count LOOP
387
388 IF p_account_type = 'VEN_CLEARING' OR
389 p_account_type = 'REC_CLEARING'
390 THEN
391 IF l_acct_gen_tbl(i).amount is not null THEN
392 -- populate the cc id
393 x_cc_id_tbl.extend();
394 IF p_account_type = 'VEN_CLEARING' THEN
395 x_cc_id_tbl(i).code_combination_id := l_ven_clearing;
396 ELSIF p_account_type = 'REC_CLEARING' THEN
397 x_cc_id_tbl(i).code_combination_id := l_rec_clearing;
398 END IF;
399 END IF;
400 ELSE
401 BEGIN
402 if l_acct_gen_tbl(i).amount is not null THEN
403 -- send line id/order_id if the type is LINE or ORDER
404 IF l_acct_gen_tbl(i).line_type = 'ORDER' THEN
405 l_line_id := null;
406 l_order_id := l_acct_gen_tbl(i).line_id;
407 ELSIF l_acct_gen_tbl(i).line_type = 'LINE' THEN
408 l_line_id := l_acct_gen_tbl(i).line_id;
409 l_order_id := null;
410 ELSE
411 l_line_id := null;
412 l_order_id := null;
413 END IF;
414
415 IF OZF_DEBUG_LOW_ON THEN
416 OZF_Utility_PVT.debug_message('Before Calling Account Generator API');
417 OZF_Utility_PVT.debug_message('p_event_type:' || p_event_type);
418 END IF;
419 -- Fix for Bug#13891326 -- Commented the code. Fix for Bug#13721874
420 /*IF (p_event_type IS NOT NULL and p_event_type = 'SETTLE_BY_AP_DEBIT') THEN
421 IF(l_acct_gen_tbl(i).amount is not null and l_acct_gen_tbl(i).amount > 0) THEN
422 l_account_type := 'EXPENSE ACCOUNT';
423 ELSE
424 l_account_type := 'ACCRUAL_LIABILITY';
425 END IF;
426 END IF;
427 */
428
429 Ozf_Acct_Generator.Start_Process (
430 p_api_version_number => 1.0,
431 p_init_msg_list => FND_API.G_FALSE,
432 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
433 x_return_status => x_return_status,
434 x_msg_count => x_msg_count,
435 x_msg_data => x_msg_data,
436 p_account_type => l_account_type,
437 p_claim_id => l_acct_gen_tbl(i).claim_id,
438 p_budget_id => l_acct_gen_tbl(i).budget_id,
439 p_utilization_id => l_acct_gen_tbl(i).utilization_id,
440 p_offer_id => l_acct_gen_tbl(i).offer_id,
441 p_order_id => l_order_id,
442 p_line_id => l_line_id,
443 p_item_type => l_acct_gen_tbl(i).item_type,
444 p_item_id => l_acct_gen_tbl(i).item_id,
445 p_price_adj_id => l_acct_gen_tbl(i).price_adj_id,
446 p_cust_account_id => l_acct_gen_tbl(i).cust_account_id,
447 x_return_ccid => l_ccid,
448 x_concat_segs => l_concat_segs,
449 x_concat_ids => l_concat_ids,
450 x_concat_descrs => l_concat_descrs
451 );
452
453 -- populate the cc id
454 x_cc_id_tbl.extend();
455 x_cc_id_tbl(i).amount := l_acct_gen_tbl(i).amount;
456 x_cc_id_tbl(i).code_combination_id := l_ccid;
457 x_cc_id_tbl(i).line_util_id := l_acct_gen_tbl(i).line_util_id;
458
459 end if; -- end amount is not null
460
461 EXCEPTION
462 WHEN OTHERS THEN
463 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
464 FND_MESSAGE.set_name('OZF', 'OZF_GL_ACCT_GEN_ERROR');
465 FND_MSG_PUB.add;
466 END IF;
467 x_return_status := FND_API.g_ret_sts_unexp_error;
468 END;
469 IF OZF_DEBUG_HIGH_ON THEN
470 OZF_UTILITY_PVT.debug_message('St and Id '||x_return_status||'-'||l_ccid);
471 END IF;
472 IF x_return_status = FND_API.g_ret_sts_error OR
473 x_return_status = FND_API.g_ret_sts_unexp_error
474 THEN
475 EXIT;
476 END IF;
477 END IF;
478 END LOOP;
479
480 --Standard check of commit
481 IF FND_API.To_Boolean ( p_commit ) THEN
482 COMMIT WORK;
483 END IF;
484 -- Debug Message
485 IF OZF_DEBUG_LOW_ON THEN
486 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
487 FND_MESSAGE.Set_Token('TEXT',l_full_name||': End');
488 FND_MSG_PUB.Add;
489 END IF;
490 --Standard call to get message count and if count=1, get the message
491 FND_MSG_PUB.Count_And_Get (
492 p_encoded => FND_API.G_FALSE,
493 p_count => x_msg_count,
494 p_data => x_msg_data
495 );
496 EXCEPTION
497 WHEN FND_API.G_EXC_ERROR THEN
498 ROLLBACK TO Get_GL_Account_PVT;
499 x_return_status := FND_API.G_RET_STS_ERROR;
500 -- Standard call to get message count and if count=1, get the message
501 FND_MSG_PUB.Count_And_Get (
502 p_encoded => FND_API.G_FALSE,
503 p_count => x_msg_count,
504 p_data => x_msg_data
505 );
506 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
507 ROLLBACK TO Get_GL_Account_PVT;
508 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
509 -- Standard call to get message count and if count=1, get the message
510 FND_MSG_PUB.Count_And_Get (
511 p_encoded => FND_API.G_FALSE,
512 p_count => x_msg_count,
513 p_data => x_msg_data
514 );
515 WHEN OTHERS THEN
516 ROLLBACK TO Get_GL_Account_PVT;
517 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
518 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
519 THEN
520 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
521 END IF;
522 -- Standard call to get message count and if count=1, get the message
523 FND_MSG_PUB.Count_And_Get (
524 p_encoded => FND_API.G_FALSE,
525 p_count => x_msg_count,
526 p_data => x_msg_data
527 );
528 --
529 END Get_GL_Account;
530 ---------------------------------------------------------------------
531 /*PROCEDURE get_ae_category (p_source_table IN VARCHAR2,
532 p_source_id IN NUMBER,
533 x_ae_category OUT NOCOPY VARCHAR2,
534 x_sob_id OUT NOCOPY NUMBER,
535 x_period_name OUT NOCOPY VARCHAR2,
536 x_return_status OUT NOCOPY VARCHAR2)
537 IS
538 l_type varchar2(30);
539
540 CURSOR get_fund_type_csr(p_id in number) IS
541 SELECT f.fund_type
542 , f.ledger_id
543 , g.period_set_name
544 FROM ozf_funds_all_b f
545 , gl_sets_of_books g
546 , ozf_funds_utilized_all_b u
547 WHERE f.ledger_id = g.set_of_books_id
548 AND f.fund_id = u.fund_id
549 AND u.utilization_id = p_id;
550
551 CURSOR get_claim_class_csr(p_id in number) IS
552 SELECT c.claim_class
553 , c.set_of_books_id
554 , g.period_set_name
555 FROM ozf_claims_all c
556 , gl_sets_of_books g
557 WHERE c.set_of_books_id = g.set_of_books_id
558 AND c.claim_id = p_id;
559
560 BEGIN
561 IF upper(p_source_table) = 'OZF_FUNDS_UTILIZED_ALL_B' THEN
562 OPEN get_fund_type_csr(p_source_id);
563 FETCH get_fund_type_csr into l_type, x_sob_id, x_period_name;
564 CLOSE get_fund_type_csr;
565 IF l_type = 'FIXED' THEN
566 x_ae_category := 'Fixed Budgets';
567 ELSIF l_type = 'FULLY_ACCRUED' THEN
568 x_ae_category := 'Accrual Budgets';
569 ELSE
570 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
571 FND_MESSAGE.set_name('OZF', 'OZF_GL_FUND_TYPE_ERROR');
572 FND_MSG_PUB.add;
573 END IF;
574 x_return_status := FND_API.g_ret_sts_error;
575 END IF;
576 ELSIF upper(p_source_table) = 'OZF_CLAIMS_ALL' THEN
577 OPEN get_claim_class_csr(p_source_id);
578 FETCH get_claim_class_csr into l_type, x_sob_id, x_period_name;
579 CLOSE get_claim_class_csr;
580
581 -- ae category is settlement when settling any claims
582 x_ae_category := 'Settlement';
583
584 /*
585 IF l_type = 'CLAIM' THEN
586 x_ae_category := 'Claims';
587 ELSIF l_type = 'DEDUCTION' THEN
588 x_ae_category := 'Deductions';
589 END IF;
590
591 ELSE
592 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
593 FND_MESSAGE.set_name('OZF', 'OZF_GL_INVALID_SOURCE_TABLE');
594 FND_MSG_PUB.add;
595 END IF;
596 x_return_status := FND_API.g_ret_sts_error;
597 END IF;
598 EXCEPTION
599 WHEN OTHERS THEN
600 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
601 FND_MESSAGE.set_name('OZF', 'OZF_GL_AE_CATG_ERROR');
602 FND_MSG_PUB.add;
603 END IF;
604 x_return_status := FND_API.g_ret_sts_unexp_error;
605 END get_ae_category;
606 */
607 ---------------------------------------------------------------------
608 /*FUNCTION get_event_number(
609 p_event_type_code IN VARCHAR2,
610 p_adjustment_type IN VARCHAR2,
611 x_return_status OUT NOCOPY VARCHAR2)
612 RETURN NUMBER
613 IS
614 BEGIN
615 IF upper(p_event_type_code) = 'ACCRUAL' THEN
616 IF p_adjustment_type = 'P' THEN
617 return 1;
618 ELSIF p_adjustment_type = 'N' THEN
619 return 6;
620 END IF;
621 ELSIF p_event_type_code = 'ACCRUAL_ADJUSTMENT' THEN
622 IF p_adjustment_type = 'P' THEN
623 return 2;
624 ELSIF p_adjustment_type = 'N' THEN
625 return 3;
626 END IF;
627 ELSIF p_event_type_code = 'SETTLE_BY_CHECK' THEN
628 return 4;
629 ELSIF p_event_type_code = 'SETTLE_BY_CREDIT' THEN
630 return 5;
631 ELSIF p_event_type_code = 'CONTRA_CHARGE' THEN
632 return 7;
633 ELSIF p_event_type_code = 'SETTLE_BY_DEBIT' THEN
634 return 8;
635 ELSIF p_event_type_code = 'SETTLE_BY_WO' THEN
636 IF p_adjustment_type = 'P' THEN
637 return 9;
638 ELSIF p_adjustment_type = 'N' THEN
639 return 10;
640 END IF;
641 ELSIF p_event_type_code = 'OFF_INVOICE' THEN
642 IF p_adjustment_type = 'P' THEN
643 return 11;
644 ELSIF p_adjustment_type = 'N' THEN
645 return 12;
646 END IF;
647 -- R12 changes start
648 ELSIF p_event_type_code = 'SETTLE_BY_AP_DEBIT' THEN
649 IF p_adjustment_type = 'P' THEN
650 return 13;
651 ELSIF p_adjustment_type = 'N' THEN
652 return 14;
653 END IF;
654 ELSIF p_event_type_code = 'SETTLE_BY_AP_INVOICE' THEN
655 return 15;
656 ELSIF p_event_type_code = 'SETTLE_BY_OTHER' THEN
657 return 16;
658 -- R12 changes end
659 -- R12.1 Changes Start
660 ELSIF p_event_type_code = 'SETTLE_BY_ACCOUNTING_ONLY' THEN
661 return 17;
662 -- R12.1 Changes End
663 ELSE
664 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
665 FND_MESSAGE.set_name('OZF', 'OZF_GL_INVALID_ACCT_EVENT');
666 FND_MSG_PUB.add;
667 END IF;
668 x_return_status := FND_API.g_ret_sts_error;
669 return null;
670 END IF;
671 EXCEPTION
672 WHEN OTHERS THEN
673 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
674 FND_MESSAGE.set_name('OZF', 'OZF_GL_EVE_NUM_ERROR');
675 FND_MSG_PUB.add;
676 END IF;
677 x_return_status := FND_API.g_ret_sts_unexp_error;
678 END get_event_number;
679 */
680 ---------------------------------------------------------------------
681 FUNCTION get_account_type_code(p_event_type_code IN VARCHAR2,
682 p_source_object_class IN VARCHAR2,
683 p_event_type IN VARCHAR2,
684 x_return_status OUT NOCOPY VARCHAR2)
685 RETURN VARCHAR2
686 IS
687
688 CURSOR taxfor_csr (p_claim_id IN NUMBER)IS
689 SELECT tax_for
690 FROM ozf_claim_sttlmnt_methods_all csm,ozf_claims_all c
691 WHERE csm.settlement_method = c.payment_method
692 AND c.claim_id =p_claim_id
693 AND csm.org_id = c.org_id;
694
695 l_taxfor VARCHAR2(2);
696
697 BEGIN
698 IF OZF_DEBUG_HIGH_ON THEN
699 OZF_UTILITY_PVT.debug_message('Get_Account_Type_Code procedure');
700 END IF;
701 IF p_event_type_code = 'ACCRUAL_CREATION' OR
702 p_event_type_code = 'ACCRUAL_ADJUSTMENT' THEN
703 IF p_event_type = 'DR' THEN
704 return 'EXPENSE ACCOUNT';
705 ELSIF p_event_type = 'CR' THEN
706 return 'ACCRUAL_LIABILITY';
707 END IF;
708 ELSIF p_event_type_code = 'PAID_ADJUSTMENT' THEN
709 IF p_event_type = 'DR' THEN
710 return 'ACCRUAL_LIABILITY';
711 ELSIF p_event_type = 'CR' THEN
712 return 'REC_CLEARING';
713 END IF;
714 -- SETTLE_BY_AP_INVOICE : Includes Claim Settle with CHECK/EFT/WIRE, AP Default settlement method.
715 -- SETTLE_BY_AP_DEBIT : Claim Settle with AP Debit Settlement method.
716 -- SETTLE_BY_AP_CUSTOM : Claim Settled with Payable Custom settlement method.
717 ELSIF p_event_type_code IN ('SETTLE_BY_AP_INVOICE','SETTLE_BY_AP_DEBIT','SETTLE_BY_AP_CUSTOM' ) THEN
718 IF (p_source_object_class IS NOT NULL AND p_source_object_class = 'SD_SUPPLIER') THEN
719 IF p_event_type = 'CR' THEN
720 return 'EXPENSE ACCOUNT';
721 ELSIF p_event_type = 'DR' THEN
722 return 'VEN_CLEARING';
723 END IF;
724 ELSE
725 IF p_event_type = 'DR' THEN
726 return 'ACCRUAL_LIABILITY';
727 ELSIF p_event_type = 'CR' THEN
728 return 'VEN_CLEARING';
729 END IF;
730 END IF;
731 -- R12.1 Enhancement: checking the evet code for Accounting only
732 ELSIF p_event_type_code = 'SETTLE_BY_CREDIT_MEMO' OR
733 p_event_type_code = 'SETTLE_BY_AR_AP_NETTING' OR
734 p_event_type_code = 'SETTLE_BY_ACCOUNTING_ONLY'
735 THEN
736 IF p_event_type = 'DR' THEN
737 return 'ACCRUAL_LIABILITY';
738 ELSIF p_event_type = 'CR' THEN
739 return 'REC_CLEARING';
740 END IF;
741 ELSIF p_event_type_code = 'SETTLE_BY_DEBIT_MEMO' THEN
742 IF p_event_type = 'DR' THEN
743 return 'REC_CLEARING';
744 ELSIF p_event_type = 'CR' THEN
745 return 'ACCRUAL_LIABILITY';
746 END IF;
747 -- R12 changes start
748 /* ELSIF p_event_type_code = 'SETTLE_BY_AP_DEBIT' THEN
749 --IF p_adjustment_type = 'P' THEN
750 IF p_event_type = 'DR' THEN
751 return 'VEN_CLEARING';
752 ELSIF p_event_type = 'CR' THEN
753 return 'EXPENSE ACCOUNT';
754 END IF;
755 /*ELSIF p_adjustment_type = 'N' THEN
756 IF p_event_type = 'DR' THEN
757 return 'VEN_CLEARING';
758 ELSIF p_event_type = 'CR' THEN
759 return 'ACCRUAL_LIABILITY';
760 END IF;
761 END IF;
762 */
763 ELSIF p_event_type_code = 'OFF_INVOICE' THEN
764 IF p_event_type = 'DR' THEN
765 return 'EXPENSE ACCOUNT';
766 ELSIF p_event_type = 'CR' THEN
767 return 'REVENUE_ACCOUNT';
768 END IF;
769 ELSIF p_event_type_code = 'INVOICE_DISCOUNT' THEN --ninarasi fix for bug 14144628
770 IF p_event_type = 'DR' THEN
771 return 'EXPENSE ACCOUNT';
772 ELSIF p_event_type = 'CR' THEN
773 return 'REVENUE_ACCOUNT';
774 END IF;
775 ELSIF p_event_type_code = 'SETTLE_BY_AR_CUSTOM' THEN
776 IF p_event_type = 'DR' THEN
777 return 'ACCRUAL_LIABILITY';
778 ELSIF p_event_type = 'CR' THEN
779 return 'REC_CLEARING';
780 END IF;
781 /*ELSIF p_event_type_code = 'SETTLE_BY_AP_CUSTOM' THEN
782 IF p_event_type = 'DR' THEN
783 return 'ACCRUAL_LIABILITY';
784 ELSIF p_event_type = 'CR' THEN
785 return 'VEN_CLEARING';
786 END IF;
787 */
788 -- R12 changes end
789 ELSE
790 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
791 FND_MESSAGE.set_name('OZF', 'OZF_GL_INVALID_ACCT_EVENT');
792 FND_MSG_PUB.add;
793 END IF;
794 x_return_status := FND_API.g_ret_sts_error;
795 return null;
796 END IF;
797 EXCEPTION
798 WHEN OTHERS THEN
799 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
800 FND_MESSAGE.set_name('OZF', 'OZF_GL_ACC_TYPE_ERROR');
801 FND_MSG_PUB.add;
802 END IF;
803 x_return_status := FND_API.g_ret_sts_unexp_error;
804 END get_account_type_code;
805
806 ---------------------------------------------------------------------
807 /*FUNCTION get_account_description(
808 p_gl_rec IN gl_interface_rec_type,
809 p_account_type_code IN VARCHAR2,
810 x_return_status OUT NOCOPY VARCHAR2)
811 RETURN VARCHAR2
812 IS
813 l_fund_number varchar2(30);
814 l_claim_number varchar2(30);
815 l_offer_code varchar2(30);
816 l_ae_category varchar2(80);
817 l_period_name varchar2(80);
818 l_sob number;
819
820 CURSOR offer_code_csr(cv_utilization_id IN NUMBER) IS
821 select o.offer_code
822 from ozf_funds_utilized_all_b fu
823 , ozf_offers o
824 where fu.plan_type = 'OFFR'
825 and fu.plan_id = o.qp_list_header_id
826 and fu.utilization_id = cv_utilization_id;
827
828 CURSOR fund_number_csr(cv_utilization_id IN NUMBER) IS
829 select f.fund_number
830 from ozf_funds_utilized_all_b fu
831 , ozf_funds_all_b f
832 where fu.fund_id = f.fund_id
833 and fu.utilization_id = cv_utilization_id;
834
835 CURSOR claim_number_csr(cv_claim_id IN NUMBER) IS
836 select claim_number
837 from ozf_claims_all
838 where claim_id = cv_claim_id;
839
840 BEGIN
841 IF OZF_DEBUG_HIGH_ON THEN
842 OZF_UTILITY_PVT.debug_message('Get_Account_Description procedure');
843 END IF;
844 get_ae_category (p_source_table => p_gl_rec.source_table,
845 p_source_id => p_gl_rec.source_id,
846 x_ae_category => l_ae_category,
847 x_sob_id => l_sob,
848 x_period_name => l_period_name,
849 x_return_status => x_return_status);
850
851 IF x_return_status = FND_API.g_ret_sts_error THEN
852 RAISE FND_API.G_EXC_ERROR;
853 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
854 RAISE FND_API.g_exc_unexpected_error;
855 END IF;
856
857 IF l_ae_category = 'Fixed Budgets' THEN
858 OPEN offer_code_csr(p_gl_rec.source_id);
859 FETCH offer_code_csr INTO l_offer_code;
860 CLOSE offer_code_csr;
861
862 IF l_offer_code IS NOT NULL THEN
863 IF p_account_type_code = 'EXPENSE ACCOUNT' THEN
864 FND_MESSAGE.SET_NAME('OZF', 'OZF_GL_DESC_FB_EXPENSE');
865 FND_MESSAGE.SET_TOKEN('OFFER', l_offer_code, FALSE);
866 RETURN FND_MESSAGE.GET;
867 ELSIF p_account_type_code = 'ACCRUAL_LIABILITY' THEN
868 FND_MESSAGE.SET_NAME('OZF', 'OZF_GL_DESC_FB_ACCRUAL');
869 FND_MESSAGE.SET_TOKEN('OFFER', l_offer_code, FALSE);
870 RETURN FND_MESSAGE.GET;
871 END IF;
872 END IF;
873 ELSIF l_ae_category = 'Accrual Budgets' THEN
874 OPEN fund_number_csr(p_gl_rec.source_id);
875 FETCH fund_number_csr INTO l_fund_number;
876 CLOSE fund_number_csr;
877
878 IF p_account_type_code = 'EXPENSE ACCOUNT' THEN
879 FND_MESSAGE.SET_NAME('OZF', 'OZF_GL_DESC_AB_EXPENSE');
880 FND_MESSAGE.SET_TOKEN('FUND', l_fund_number, FALSE);
881 RETURN FND_MESSAGE.GET;
882 ELSIF p_account_type_code = 'ACCRUAL_LIABILITY' THEN
883 FND_MESSAGE.SET_NAME('OZF', 'OZF_GL_DESC_AB_ACCRUAL');
884 FND_MESSAGE.SET_TOKEN('FUND', l_fund_number, FALSE);
885 RETURN FND_MESSAGE.GET;
886 END IF;
887 ELSIF l_ae_category = 'Settlement' THEN
888 OPEN claim_number_csr(p_gl_rec.source_id);
889 FETCH claim_number_csr INTO l_claim_number;
890 CLOSE claim_number_csr;
891
892 IF p_account_type_code = 'ACCRUAL_LIABILITY' THEN
893 FND_MESSAGE.SET_NAME('OZF', 'OZF_GL_DESC_STL_ACCRUAL');
894 FND_MESSAGE.SET_TOKEN('CLAIM', l_claim_number, FALSE);
895 RETURN FND_MESSAGE.GET;
896 ELSIF p_account_type_code = 'REC_CLEARING' OR
897 p_account_type_code = 'VEN_CLEARING'
898 THEN
899 FND_MESSAGE.SET_NAME('OZF', 'OZF_GL_DESC_STL_CLEARING');
900 FND_MESSAGE.SET_TOKEN('CLAIM', l_claim_number, FALSE);
901 RETURN FND_MESSAGE.GET;
902 -- Added for Bug 6751352
903 ELSIF p_account_type_code = 'EXPENSE ACCOUNT' THEN
904 FND_MESSAGE.SET_NAME('OZF', 'OZF_GL_DESC_STL_EXPENSE');
905 FND_MESSAGE.SET_TOKEN('CLAIM', l_claim_number, FALSE);
906 RETURN FND_MESSAGE.GET;
907 END IF;
908 END IF;
909
910 RETURN null;
911
912 EXCEPTION
913 WHEN OTHERS THEN
914 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
915 FND_MESSAGE.set_name('OZF', 'OZF_GL_ACC_DESC_ERROR');
916 FND_MSG_PUB.add;
917 END IF;
918 x_return_status := FND_API.g_ret_sts_unexp_error;
919 END get_account_description;
920 */
921 ---------------------------------------------------------------------
922
923 PROCEDURE get_acctg_accounts(
924 p_gl_rec IN gl_interface_rec_type,
925 x_actg_tbl OUT NOCOPY actg_tbl_type,
926 x_return_status OUT NOCOPY VARCHAR2 )
927 IS
928
929 l_msg_data varchar2(2000);
930 l_msg_count number;
931
932 l_cl_tot number;
933
934 l_expense_account number;
935 l_accrual_liability number;
936 l_ven_clearing number;
937 l_rec_clearing number;
938 l_rec_deduction number;
939
940 l_debit_event varchar2(3) := 'DR';
941 l_credit_event varchar2(3) := 'CR';
942 l_dr_cc_id_tbl CC_ID_TBL;
943 l_cr_cc_id_tbl CC_ID_TBL;
944 l_dr_account_type_code varchar2(30);
945 l_cr_account_type_code varchar2(30);
946
947 l_amount number;
948 l_acctd_amount number;
949 l_currency_code varchar2(15);
950 l_code_combination_id number;
951 l_actg_rec_type actg_rec_type;
952
953 -- Fix for Bug#13721874
954 CURSOR c_claim_source_info (p_claim_id IN NUMBER)IS
955 SELECT cla.source_object_class
956 FROM ozf_claims_all cla
957 WHERE claim_id = p_claim_id;
958 l_source_object_class VARCHAR2(30);
959
960
961 BEGIN
962 IF OZF_DEBUG_HIGH_ON THEN
963 OZF_UTILITY_PVT.debug_message('get_acctg_accounts procedure');
964 OZF_UTILITY_PVT.debug_message('l_source_object_class:' || l_source_object_class);
965 END IF;
966
967 G_CLAIM_ID := p_gl_rec.source_id;
968
969 -- Fix for Bug#13721874
970 OPEN c_claim_source_info(p_gl_rec.source_id);
971 FETCH c_claim_source_info INTO l_source_object_class;
972 CLOSE c_claim_source_info;
973
974 l_dr_account_type_code := get_account_type_code(
975 p_event_type_code => p_gl_rec.event_type_code,
976 p_source_object_class => l_source_object_class, -- Fix for Bug#13721874
977 p_event_type => l_debit_event,
978 x_return_status => x_return_status);
979
980 IF x_return_status = FND_API.g_ret_sts_error THEN
981 RAISE FND_API.G_EXC_ERROR;
982 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
983 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
984 END IF;
985
986 l_cr_account_type_code := get_account_type_code(
987 p_event_type_code => p_gl_rec.event_type_code,
988 p_source_object_class => l_source_object_class, -- Fix for Bug#13721874
989 p_event_type => l_credit_event,
990 x_return_status => x_return_status);
991
992 IF x_return_status = FND_API.g_ret_sts_error THEN
993 RAISE FND_API.G_EXC_ERROR;
994 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
995 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
996 END IF;
997
998 IF OZF_DEBUG_HIGH_ON THEN
999 OZF_UTILITY_PVT.debug_message('p_gl_rec.source_table:' || p_gl_rec.source_table);
1000 OZF_UTILITY_PVT.debug_message('l_cr_account_type_code:' || l_cr_account_type_code );
1001 OZF_UTILITY_PVT.debug_message('l_dr_account_type_code:' || l_dr_account_type_code);
1002 END IF;
1003
1004 IF (upper(p_gl_rec.source_table) = 'OZF_FUNDS_UTILIZED_ALL_B') THEN
1005
1006 IF OZF_DEBUG_HIGH_ON THEN
1007 OZF_UTILITY_PVT.debug_message('Getting GL Account for debiting');
1008 END IF;
1009 -- get accounts from account generator for debiting
1010
1011 -- For Paid Adjustment we will call the account generatot for Accrual Liability account for DR only as the
1012 -- CR account is a Clearing account.
1013 IF(p_gl_rec.event_type_code = 'PAID_ADJUSTMENT' AND l_dr_account_type_code = 'ACCRUAL_LIABILITY') THEN
1014
1015 Get_GL_Account(
1016 p_api_version => 1.0,
1017 p_init_msg_list => FND_API.G_FALSE,
1018 p_validation_level=> FND_API.G_VALID_LEVEL_FULL,
1019 x_msg_data => l_msg_data,
1020 x_msg_count => l_msg_count,
1021 x_return_status => x_return_status,
1022 p_source_id => p_gl_rec.source_id,
1023 p_source_table => p_gl_rec.source_table,
1024 p_account_type => l_dr_account_type_code,
1025 x_cc_id_tbl => l_dr_cc_id_tbl);
1026
1027 IF x_return_status = FND_API.g_ret_sts_error THEN
1028 RAISE FND_API.G_EXC_ERROR;
1029 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
1030 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1031 END IF;
1032
1033
1034 ELSE
1035
1036 Get_GL_Account(
1037 p_api_version => 1.0,
1038 p_init_msg_list => FND_API.G_FALSE,
1039 p_validation_level=> FND_API.G_VALID_LEVEL_FULL,
1040 x_msg_data => l_msg_data,
1041 x_msg_count => l_msg_count,
1042 x_return_status => x_return_status,
1043 p_source_id => p_gl_rec.source_id,
1044 p_source_table => p_gl_rec.source_table,
1045 p_account_type => l_dr_account_type_code,
1046 x_cc_id_tbl => l_dr_cc_id_tbl);
1047
1048 IF x_return_status = FND_API.g_ret_sts_error THEN
1049 RAISE FND_API.G_EXC_ERROR;
1050 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
1051 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1052 END IF;
1053
1054 IF OZF_DEBUG_HIGH_ON THEN
1055 OZF_UTILITY_PVT.debug_message('Getting GL Account for crediting');
1056 END IF;
1057 -- get accounts from account generator for crediting
1058
1059 Get_GL_Account(
1060 p_api_version => 1.0,
1061 p_init_msg_list => FND_API.G_FALSE,
1062 p_validation_level=> FND_API.G_VALID_LEVEL_FULL,
1063 x_msg_data => l_msg_data,
1064 x_msg_count => l_msg_count,
1065 x_return_status => x_return_status,
1066 p_source_id => p_gl_rec.source_id,
1067 p_source_table => p_gl_rec.source_table,
1068 p_account_type => l_cr_account_type_code,
1069 x_cc_id_tbl => l_cr_cc_id_tbl);
1070
1071 IF x_return_status = FND_API.g_ret_sts_error THEN
1072 RAISE FND_API.G_EXC_ERROR;
1073 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
1074 IF OZF_DEBUG_HIGH_ON THEN
1075 OZF_UTILITY_PVT.debug_message('Unexp Error in getting CR account');
1076 END IF;
1077 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1078 END IF;
1079 END IF;
1080 ELSIF(upper(p_gl_rec.source_table) = 'OZF_CLAIMS_ALL') THEN
1081
1082 IF OZF_DEBUG_HIGH_ON THEN
1083 OZF_UTILITY_PVT.debug_message('l_source_object_class:' || l_source_object_class);
1084 END IF;
1085
1086 IF (p_gl_rec.event_type_code IN ('SETTLE_BY_CREDIT_MEMO','SETTLE_BY_AR_AP_NETTING','SETTLE_BY_ACCOUNTING_ONLY',
1087 'SETTLE_BY_AR_CUSTOM')
1088 OR (p_gl_rec.event_type_code IN ('SETTLE_BY_AP_CUSTOM','SETTLE_BY_AP_INVOICE','SETTLE_BY_AP_DEBIT')
1089 AND (l_source_object_class IS NULL OR l_source_object_class <> 'SD_SUPPLIER'))) THEN
1090 IF OZF_DEBUG_HIGH_ON THEN
1091 OZF_UTILITY_PVT.debug_message('Getting GL Account for debiting');
1092 END IF;
1093 -- get accounts from account generator for debiting
1094 Get_GL_Account(
1095 p_api_version => 1.0,
1096 p_init_msg_list => FND_API.G_FALSE,
1097 p_validation_level=> FND_API.G_VALID_LEVEL_FULL,
1098 x_msg_data => l_msg_data,
1099 x_msg_count => l_msg_count,
1100 x_return_status => x_return_status,
1101 p_source_id => p_gl_rec.source_id,
1102 p_source_table => p_gl_rec.source_table,
1103 p_account_type => l_dr_account_type_code,
1104 x_cc_id_tbl => l_dr_cc_id_tbl);
1105 IF x_return_status = FND_API.g_ret_sts_error THEN
1106 RAISE FND_API.G_EXC_ERROR;
1107 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
1108 IF OZF_DEBUG_HIGH_ON THEN
1109 OZF_UTILITY_PVT.debug_message('Unexp Error in getting DR account');
1110 END IF;
1111 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1112 END IF;
1113 ELSIF(p_gl_rec.event_type_code IN ('SETTLE_BY_DEBIT_MEMO')
1114 OR (p_gl_rec.event_type_code IN ('SETTLE_BY_AP_CUSTOM','SETTLE_BY_AP_INVOICE','SETTLE_BY_AP_DEBIT')
1115 AND (l_source_object_class IS NOT NULL AND l_source_object_class = 'SD_SUPPLIER'))) THEN
1116 IF OZF_DEBUG_HIGH_ON THEN
1117 OZF_UTILITY_PVT.debug_message('Getting GL Account for crediting');
1118 END IF;
1119 -- get accounts from account generator for crediting
1120 Get_GL_Account(
1121 p_api_version => 1.0,
1122 p_init_msg_list => FND_API.G_FALSE,
1123 p_validation_level=> FND_API.G_VALID_LEVEL_FULL,
1124 x_msg_data => l_msg_data,
1125 x_msg_count => l_msg_count,
1126 x_return_status => x_return_status,
1127 p_source_id => p_gl_rec.source_id,
1128 p_source_table => p_gl_rec.source_table,
1129 p_account_type => l_cr_account_type_code,
1130 p_event_type => p_gl_rec.event_type_code,
1131 x_cc_id_tbl => l_cr_cc_id_tbl);
1132
1133 IF x_return_status = FND_API.g_ret_sts_error THEN
1134 RAISE FND_API.G_EXC_ERROR;
1135 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
1136 IF OZF_DEBUG_HIGH_ON THEN
1137 OZF_UTILITY_PVT.debug_message('Unexp Error in getting CR account');
1138 END IF;
1139 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1140 END IF;
1141 END IF;
1142 END IF;
1143 OZF_UTILITY_PVT.debug_message('After Get_gl_account');
1144 -- for accruals and accrual adjustments
1145 IF upper(p_gl_rec.source_table) = 'OZF_FUNDS_UTILIZED_ALL_B' THEN
1146 IF p_gl_rec.event_type_code = 'ACCRUAL_CREATION' OR
1147 p_gl_rec.event_type_code = 'ACCRUAL_ADJUSTMENT' THEN
1148
1149 IF OZF_DEBUG_LOW_ON THEN
1150 OZF_UTILITY_PVT.debug_message('l_dr_cc_id_tbl(1).code_combination_id:' || l_dr_cc_id_tbl(1).code_combination_id);
1151 OZF_UTILITY_PVT.debug_message('l_cr_cc_id_tbl(1).code_combination_id:' || l_cr_cc_id_tbl(1).code_combination_id);
1152 END IF;
1153 x_actg_tbl(1).CODE_COMBINATION_ID := l_dr_cc_id_tbl(1).code_combination_id;
1154 x_actg_tbl(1).ACCOUNT_TYPE := 'EXPENSE ACCOUNT';
1155 x_actg_tbl(2).CODE_COMBINATION_ID := l_cr_cc_id_tbl(1).code_combination_id;
1156 x_actg_tbl(2).ACCOUNT_TYPE := 'ACCRUAL_LIABILITY';
1157
1158 ELSIF (p_gl_rec.event_type_code = 'PAID_ADJUSTMENT') THEN
1159 x_actg_tbl(1).CODE_COMBINATION_ID := l_dr_cc_id_tbl(1).code_combination_id;
1160 x_actg_tbl(1).ACCOUNT_TYPE := 'ACCRUAL_LIABILITY';
1161 ELSIF p_gl_rec.event_type_code = 'INVOICE_DISCOUNT' THEN --ninarasi fix for bug 14144628
1162 x_actg_tbl(1).CODE_COMBINATION_ID := l_dr_cc_id_tbl(1).code_combination_id;
1163 x_actg_tbl(1).ACCOUNT_TYPE := 'EXPENSE ACCOUNT';
1164 ELSE
1165 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1166 FND_MESSAGE.set_name('OZF', 'OZF_GL_INVALID_SOURCE_EVENT');
1167 FND_MSG_PUB.add;
1168 END IF;
1169 x_return_status := FND_API.g_ret_sts_error;
1170 END IF;
1171
1172 ELSIF upper(p_gl_rec.source_table) = 'OZF_CLAIMS_ALL' THEN
1173
1174
1175 IF p_gl_rec.event_type_code IN ('SETTLE_BY_CREDIT_MEMO','SETTLE_BY_AR_AP_NETTING','SETTLE_BY_ACCOUNTING_ONLY',
1176 'SETTLE_BY_AR_CUSTOM')
1177 THEN
1178 -- debit line -- accrual liability
1179 FOR i in 1..l_dr_cc_id_tbl.count LOOP
1180 IF OZF_DEBUG_LOW_ON THEN
1181 OZF_UTILITY_PVT.debug_message('l_dr_cc_id_tbl(i).line_util_id:' || l_dr_cc_id_tbl(i).line_util_id);
1182 OZF_UTILITY_PVT.debug_message('l_dr_cc_id_tbl(i).code_combination_id:' || l_dr_cc_id_tbl(i).code_combination_id);
1183 END IF;
1184 x_actg_tbl(l_dr_cc_id_tbl(i).line_util_id).CODE_COMBINATION_ID := l_dr_cc_id_tbl(i).code_combination_id;
1185 x_actg_tbl(l_dr_cc_id_tbl(i).line_util_id).ACCOUNT_TYPE := 'ACCRUAL_LIABILITY';
1186 END LOOP;
1187 ELSIF(p_gl_rec.event_type_code IN ('SETTLE_BY_AP_DEBIT','SETTLE_BY_AP_CUSTOM','SETTLE_BY_AP_INVOICE')) THEN
1188 IF(l_source_object_class IS NOT NULL AND l_source_object_class = 'SD_SUPPLIER') THEN
1189 FOR i in 1..l_cr_cc_id_tbl.count LOOP
1190 x_actg_tbl(l_cr_cc_id_tbl(i).line_util_id).CODE_COMBINATION_ID := l_cr_cc_id_tbl(i).code_combination_id;
1191 x_actg_tbl(l_cr_cc_id_tbl(i).line_util_id).ACCOUNT_TYPE := 'EXPENSE ACCOUNT';
1192 END LOOP;
1193 ELSE
1194 FOR i in 1..l_dr_cc_id_tbl.count LOOP
1195 x_actg_tbl(l_dr_cc_id_tbl(i).line_util_id).CODE_COMBINATION_ID := l_dr_cc_id_tbl(i).code_combination_id;
1196 x_actg_tbl(l_dr_cc_id_tbl(i).line_util_id).ACCOUNT_TYPE := 'ACCRUAL_LIABILITY';
1197 END LOOP;
1198 END IF;
1199 ELSIF(p_gl_rec.event_type_code = 'SETTLE_BY_DEBIT_MEMO') THEN
1200 FOR i in 1..l_cr_cc_id_tbl.count LOOP
1201 x_actg_tbl(l_cr_cc_id_tbl(i).line_util_id).CODE_COMBINATION_ID := l_cr_cc_id_tbl(i).code_combination_id;
1202 x_actg_tbl(l_cr_cc_id_tbl(i).line_util_id).ACCOUNT_TYPE := 'ACCRUAL_LIABILITY';
1203 END LOOP;
1204
1205 END IF;
1206 -- if source table is different
1207 ELSE
1208 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1209 FND_MESSAGE.set_name('OZF', 'OZF_GL_INVALID_SOURCE_TABLE');
1210 FND_MSG_PUB.add;
1211 END IF;
1212 x_return_status := FND_API.g_ret_sts_error;
1213 END IF;
1214
1215 EXCEPTION
1216 WHEN FND_API.G_EXC_ERROR THEN
1217 x_return_status := FND_API.G_RET_STS_ERROR;
1218 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1219 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1220 WHEN OTHERS THEN
1221 OZF_UTILITY_PVT.debug_message('sqlerrm:'|| sqlerrm);
1222 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1223 FND_MESSAGE.set_name('OZF', 'OZF_GL_LINE_AMOUNT_CALC_ERROR');
1224 FND_MSG_PUB.add;
1225 END IF;
1226 x_return_status := FND_API.g_ret_sts_unexp_error;
1227 END;
1228
1229 ---------------------------------------------------------------------
1230
1231 -- Start of fix for bug 4582919
1232 /*FUNCTION get_gl_period (
1233 p_sob_id IN NUMBER
1234 , p_acc_date IN DATE
1235 , p_dflt_period IN VARCHAR2
1236 )
1237 RETURN VARCHAR2
1238 IS
1239
1240 l_gl_period VARCHAR2(30); -- size of 15 is enough
1241
1242 CURSOR gl_period_csr (p_sob_id IN NUMBER, p_acc_date IN DATE) IS
1243 SELECT G.period_name
1244 FROM gl_period_statuses G
1245 WHERE G.application_id = 101
1246 AND G.closing_status in ('O', 'F')
1247 AND NVL(G.adjustment_period_flag, 'N') = 'N'
1248 AND G.set_of_books_id = p_sob_id
1249 AND trunc(p_acc_date) between G.start_date and G.end_date;
1250
1251 BEGIN
1252
1253 OPEN gl_period_csr (p_sob_id, p_acc_date);
1254 FETCH gl_period_csr INTO l_gl_period;
1255 CLOSE gl_period_csr;
1256
1257 IF l_gl_period IS NULL THEN
1258 l_gl_period := p_dflt_period;
1259 END IF;
1260
1261 RETURN l_gl_period;
1262
1263 EXCEPTION
1264 WHEN OTHERS THEN
1265 RETURN p_dflt_period;
1266 END;
1267 */
1268 ---------------------------------------------------------------------
1269 -- End of fix for bug 4582919
1270
1271 /*FUNCTION get_gl_date (
1272 p_source_id in number
1273 , p_source_table in varchar2
1274 )
1275 RETURN DATE
1276 IS
1277
1278 l_gl_date date := SYSDATE;
1279 l_claim_date date;
1280 l_org_id number;
1281 l_gl_date_type varchar2(30);
1282
1283 CURSOR claim_gl_date_csr (p_id in number) IS
1284 select gl_date
1285 , claim_date
1286 , org_id
1287 from ozf_claims_all
1288 where claim_id = p_id;
1289
1290 CURSOR util_gl_date_csr (p_id in number) IS
1291 select gl_date
1292 from ozf_funds_utilized_all_b
1293 where utilization_id = p_id;
1294
1295 CURSOR csr_get_gl_date_type(p_id IN NUMBER) IS
1296 SELECT gl_date_type
1297 FROM ozf_sys_parameters_all
1298 WHERE org_id = NVL(p_id, -99);
1299
1300 BEGIN
1301
1302 IF p_source_table = 'OZF_FUNDS_UTILIZED_ALL_B' THEN
1303
1304 OPEN util_gl_date_csr (p_source_id);
1305 FETCH util_gl_date_csr INTO l_gl_date;
1306 CLOSE util_gl_date_csr;
1307
1308 IF l_gl_date IS NULL THEN
1309 l_gl_date := SYSDATE;
1310 END IF;
1311
1312 ELSIF p_source_table = 'OZF_CLAIMS_ALL' THEN
1313
1314 OPEN claim_gl_date_csr (p_source_id);
1315 FETCH claim_gl_date_csr INTO l_gl_date, l_claim_date, l_org_id;
1316 CLOSE claim_gl_date_csr;
1317
1318 IF l_gl_date IS NULL THEN
1319
1320 OPEN csr_get_gl_date_type(l_org_id);
1321 FETCH csr_get_gl_date_type INTO l_gl_date_type;
1322 CLOSE csr_get_gl_date_type;
1323
1324 IF l_gl_date_type = 'CLAIM_DATE' THEN
1325 l_gl_date := l_claim_date;
1326 END IF;
1327
1328 IF l_gl_date_type = 'SYSTEM_DATE' THEN
1329 l_gl_date := SYSDATE;
1330 END IF;
1331
1332 END IF;
1333 END IF;
1334
1335 RETURN trunc(l_gl_date);
1336
1337 EXCEPTION
1338 WHEN OTHERS THEN
1339 l_gl_date := SYSDATE;
1340 RETURN trunc(l_gl_date);
1341 END;
1342 */
1343 ---------------------------------------------------------------------
1344 /*
1345 Moved this function to top of package for forward declaration purpose
1346 Bugfix 7431334
1347 FUNCTION get_org_id (
1348 p_source_id in number
1349 , p_source_table in varchar2
1350 )
1351 RETURN NUMBER
1352 IS
1353 l_org_id number;
1354
1355 CURSOR claim_org_id_csr (p_id in number) IS
1356 select org_id
1357 from ozf_claims_all
1358 where claim_id = p_id;
1359
1360 CURSOR util_org_id_csr (p_id in number) IS
1361 select org_id
1362 from ozf_funds_utilized_all_b
1363 where utilization_id = p_id;
1364
1365 BEGIN
1366
1367 IF p_source_table = 'OZF_FUNDS_UTILIZED_ALL_B' THEN
1368 OPEN util_org_id_csr (p_source_id);
1369 FETCH util_org_id_csr INTO l_org_id;
1370 CLOSE util_org_id_csr;
1371 ELSIF p_source_table = 'OZF_CLAIMS_ALL' THEN
1372 OPEN claim_org_id_csr (p_source_id);
1373 FETCH claim_org_id_csr INTO l_org_id;
1374 CLOSE claim_org_id_csr;
1375 END IF;
1376
1377 RETURN l_org_id;
1378
1379 EXCEPTION
1380 WHEN OTHERS THEN
1381 RETURN NULL;
1382 END;
1383 */
1384 ---------------------------------------------------------------------
1385 /*PROCEDURE Construct_Acctng_Event_Rec(
1386 p_gl_rec IN gl_interface_rec_type
1387 ,x_return_status OUT NOCOPY VARCHAR2
1388 ,x_accounting_event_rec OUT NOCOPY OZF_acctng_events_PVT.acctng_event_rec_type
1389 ) IS
1390 l_created varchar2(30) := 'CREATED';
1391 l_event_number number;
1392 l_gl_date date;
1393 l_org_id number;
1394 BEGIN
1395
1396 -- accounting header rec
1397 l_event_number := get_event_number(
1398 p_event_type_code => p_gl_rec.event_type_code,
1399 p_adjustment_type => p_gl_rec.adjustment_type,
1400 x_return_status => x_return_status);
1401
1402 IF x_return_status = FND_API.g_ret_sts_error THEN
1403 RAISE FND_API.G_EXC_ERROR;
1404 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
1405 RAISE FND_API.g_exc_unexpected_error;
1406 END IF;
1407
1408 l_gl_date := get_gl_date(p_source_id => p_gl_rec.source_id,
1409 p_source_table => upper(p_gl_rec.source_table));
1410 l_org_id := get_org_id(p_source_id => p_gl_rec.source_id,
1411 p_source_table => upper(p_gl_rec.source_table));
1412
1413 x_accounting_event_rec.accounting_date := l_gl_date;
1414 x_accounting_event_rec.event_number := l_event_number;
1415 x_accounting_event_rec.event_status_code := nvl(p_gl_rec.event_status_code, l_created);
1416 x_accounting_event_rec.event_type_code := p_gl_rec.event_type_code;
1417 x_accounting_event_rec.source_id := p_gl_rec.source_id;
1418 x_accounting_event_rec.source_table := upper(p_gl_rec.source_table);
1419 x_accounting_event_rec.org_id := l_org_id;
1420
1421 EXCEPTION
1422 WHEN FND_API.G_EXC_ERROR THEN
1423 x_return_status := FND_API.G_RET_STS_ERROR;
1424 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1425 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1426 WHEN OTHERS THEN
1427 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1428 FND_MESSAGE.set_name('OZF', 'OZF_GL_ACCTNG_EVENT_ERROR');
1429 FND_MSG_PUB.add;
1430 END IF;
1431 x_return_status := FND_API.g_ret_sts_unexp_error;
1432 END Construct_Acctng_Event_Rec;
1433 */
1434 ---------------------------------------------------------------------
1435 /*PROCEDURE Construct_Header_Rec(
1436 p_gl_rec IN gl_interface_rec_type
1437 ,x_return_status OUT NOCOPY VARCHAR2
1438 ,x_ae_header_rec OUT NOCOPY OZF_ae_header_PVT.ae_header_rec_type
1439 ) IS
1440 l_ae_category varchar2(80);
1441 l_event_desc varchar2(160);
1442 l_event_type varchar2(80);
1443 l_event_status varchar2(80);
1444 l_period_name varchar2(80); -- get from profile
1445 l_gl_period varchar2(30); -- Fix for bug 4582919
1446 l_sob number; --get from profile
1447 l_org_id number;
1448 l_gl_date date;
1449
1450 CURSOR meaning_csr(p_type in varchar2) IS
1451 select meaning
1452 from fnd_lookup_types_tl
1453 where lookup_type = p_type;
1454
1455 BEGIN
1456 -- get AE Category, SOB and Period
1457 get_ae_category (p_source_table => p_gl_rec.source_table,
1458 p_source_id => p_gl_rec.source_id,
1459 x_ae_category => l_ae_category,
1460 x_sob_id => l_sob,
1461 x_period_name => l_period_name,
1462 x_return_status => x_return_status);
1463
1464 IF OZF_DEBUG_HIGH_ON THEN
1465 OZF_UTILITY_PVT.debug_message('AE Category'||l_ae_category);
1466 END IF;
1467 IF x_return_status = FND_API.g_ret_sts_error THEN
1468 RAISE FND_API.G_EXC_ERROR;
1469 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
1470 RAISE FND_API.g_exc_unexpected_error;
1471 END IF;
1472
1473 --- get event status meaning
1474 open meaning_csr(p_gl_rec.event_status_code);
1475 fetch meaning_csr into l_event_status;
1476 close meaning_csr;
1477
1478 -- get event type meaning
1479 open meaning_csr(p_gl_rec.event_type_code);
1480 fetch meaning_csr into l_event_type;
1481 close meaning_csr;
1482
1483 -- event description
1484 l_event_desc := l_event_type || ' '|| l_event_status;
1485
1486 -- org id
1487 l_org_id := get_org_id(p_source_id => p_gl_rec.source_id,
1488 p_source_table => upper(p_gl_rec.source_table));
1489
1490 l_gl_date := get_gl_date(p_source_id => p_gl_rec.source_id,
1491 p_source_table => upper(p_gl_rec.source_table));
1492
1493 -- Start of fix for bug 4582919
1494 l_gl_period := get_gl_period(p_sob_id => l_sob,
1495 p_acc_date => l_gl_date,
1496 p_dflt_period => l_period_name );
1497 -- End of fix for bug 4582919
1498
1499 x_ae_header_rec.accounting_date := l_gl_date; --sysdate; changed by feliu on 12/30/2003
1500 x_ae_header_rec.ae_category := l_ae_category;
1501 x_ae_header_rec.cross_currency_flag := 'N';
1502 x_ae_header_rec.description := l_event_desc;
1503 x_ae_header_rec.gl_reversal_flag := 'N';
1504 x_ae_header_rec.period_name := l_gl_period; -- Fix for bug 4582919
1505 x_ae_header_rec.set_of_books_id := l_sob;
1506 x_ae_header_rec.gl_transfer_flag := 'N';
1507 x_ae_header_rec.gl_transfer_run_id := -1;
1508 x_ae_header_rec.org_id := l_org_id;
1509
1510 EXCEPTION
1511 WHEN FND_API.G_EXC_ERROR THEN
1512 x_return_status := FND_API.G_RET_STS_ERROR;
1513 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1514 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1515 WHEN OTHERS THEN
1516 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1517 FND_MESSAGE.set_name('OZF', 'OZF_GL_AE_HEADER_ERROR');
1518 FND_MSG_PUB.add;
1519 END IF;
1520 x_return_status := FND_API.g_ret_sts_unexp_error;
1521 END Construct_Header_Rec;
1522 */
1523 ---------------------------------------------------------------------
1524 /*
1525 PROCEDURE Construct_Line_Rec(
1526 p_gl_rec IN gl_interface_rec_type
1527 ,x_return_status OUT NOCOPY VARCHAR2
1528 ,x_ae_line_tbl OUT NOCOPY OZF_ae_line_PVT.ae_line_tbl_type
1529 ) IS
1530 l_num_dr_lines number := 1;
1531 l_num_cr_lines number := 1;
1532 l_num_lines number := 1;
1533 l_amount_tbl amount_tbl_type;
1534 l_org_id number;
1535
1536 CURSOR get_fxgl_gain_ccid_csr( p_org_id in number ) is
1537 select code_combination_id_gain
1538 from ar_system_parameters_all
1539 where org_id = p_org_id;
1540
1541 CURSOR get_fxgl_loss_ccid_csr( p_org_id in number ) is
1542 select code_combination_id_loss
1543 from ar_system_parameters_all
1544 where org_id = p_org_id;
1545
1546 CURSOR get_fxgl_amt_csr( p_claim_id in number) IS
1547 SELECT SUM(u.amount)
1548 , SUM(u.fxgl_acctd_amount)
1549 , c.currency_code
1550 FROM ozf_claim_lines_util_all u
1551 , ozf_claim_lines_all l
1552 , ozf_claims_all c
1553 WHERE l.claim_id = c.claim_id
1554 AND l.earnings_associated_flag = 'T'
1555 AND l.claim_line_id = u.claim_line_id
1556 AND c.claim_id = p_claim_id
1557 GROUP BY c.currency_code;
1558
1559 l_fxgl_gain_ccid number;
1560 l_fxgl_loss_ccid number;
1561
1562 l_amount number;
1563 l_acctd_amount number;
1564 l_currency_code varchar2(15);
1565 l_line_type_code varchar2(30);
1566
1567 l_ae_line_tbl OZF_ae_line_PVT.ae_line_tbl_type;
1568 l_rec_num number;
1569
1570 BEGIN
1571 -- get line amounts for credit and debit lines
1572 get_acctg_accounts(
1573 p_gl_rec => p_gl_rec,
1574 x_amount_tbl => l_amount_tbl,
1575 x_return_status => x_return_status );
1576
1577 IF x_return_status = FND_API.g_ret_sts_error THEN
1578 RAISE FND_API.G_EXC_ERROR;
1579 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
1580 RAISE FND_API.g_exc_unexpected_error;
1581 END IF;
1582
1583 -- org id
1584 l_org_id := get_org_id(p_source_id => p_gl_rec.source_id,
1585 p_source_table => upper(p_gl_rec.source_table));
1586
1587 OPEN get_fxgl_gain_ccid_csr( l_org_id );
1588 FETCH get_fxgl_gain_ccid_csr INTO l_fxgl_gain_ccid;
1589 CLOSE get_fxgl_gain_ccid_csr;
1590
1591 OPEN get_fxgl_loss_ccid_csr( l_org_id );
1592 FETCH get_fxgl_loss_ccid_csr INTO l_fxgl_loss_ccid;
1593 CLOSE get_fxgl_loss_ccid_csr;
1594
1595 IF l_fxgl_gain_ccid is null THEN
1596 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1597 FND_MESSAGE.set_name('OZF', 'OZF_GL_NO_FXGL_ACCOUNT_SETUP');
1598 FND_MSG_PUB.add;
1599 END IF;
1600 x_return_status := FND_API.g_ret_sts_error;
1601 return;
1602 END IF;
1603
1604 IF l_fxgl_loss_ccid is null THEN
1605 l_fxgl_loss_ccid := l_fxgl_gain_ccid;
1606 END IF;
1607
1608 -- ae lines
1609 x_ae_line_tbl := OZF_ae_line_PVT.ae_line_tbl_type();
1610 l_num_lines := l_amount_tbl.count;
1611 IF OZF_DEBUG_HIGH_ON THEN
1612 OZF_UTILITY_PVT.debug_message('Number of Lines: ' || l_num_lines);
1613 END IF;
1614 FOR i in 1..l_num_lines LOOP
1615 x_ae_line_tbl.extend;
1616 x_ae_line_tbl(i) := null;
1617 --OZF_UTILITY_PVT.debug_message('Line Number before entering: ' || x_ae_line_tbl(i).ae_line_number);
1618 --if x_ae_line_tbl(i).ae_line_type_code = FND_API.G_MISS_CHAR then
1619 --OZF_UTILITY_PVT.debug_message('Line Type Code before entering - miss char: ' || x_ae_line_tbl(i).ae_line_type_code);
1620 --else
1621 --OZF_UTILITY_PVT.debug_message('Line Type Code before entering: ' || x_ae_line_tbl(i).ae_line_type_code);
1622 --end if;
1623 --OZF_UTILITY_PVT.debug_message('Line Type Code assigned value: ' || x_amount_tbl(i).line_type_code);
1624 --OZF_UTILITY_PVT.debug_message('Processing Line: ' || i);
1625
1626 -- Fix for Bug 7430768
1627 -- Fix for Bug 8274064, This will only valid for -ve adjustment for claims
1628 -- Fix for Bug 8666602
1629 IF (upper(p_gl_rec.source_table) = 'OZF_CLAIMS_ALL' AND p_gl_rec.adjustment_type = 'N'
1630 AND p_gl_rec.event_type_code <> 'SETTLE_BY_DEBIT') THEN
1631 x_ae_line_tbl(i).entered_dr := (l_amount_tbl(i).entered_dr)*-1;
1632 x_ae_line_tbl(i).entered_cr := (l_amount_tbl(i).entered_cr)*-1;
1633 x_ae_line_tbl(i).accounted_dr := (l_amount_tbl(i).accounted_dr)*-1;
1634 x_ae_line_tbl(i).accounted_cr := (l_amount_tbl(i).accounted_cr)*-1;
1635 ELSE
1636 x_ae_line_tbl(i).entered_dr := l_amount_tbl(i).entered_dr;
1637 x_ae_line_tbl(i).entered_cr := l_amount_tbl(i).entered_cr;
1638 x_ae_line_tbl(i).accounted_dr := l_amount_tbl(i).accounted_dr;
1639 x_ae_line_tbl(i).accounted_cr := l_amount_tbl(i).accounted_cr;
1640 END IF;
1641 x_ae_line_tbl(i).ae_line_number := i;
1642 x_ae_line_tbl(i).ae_line_type_code := l_amount_tbl(i).line_type_code;
1643 x_ae_line_tbl(i).code_combination_id := l_amount_tbl(i).code_combination_id;
1644 x_ae_line_tbl(i).source_id := p_gl_rec.source_id;
1645 x_ae_line_tbl(i).source_table := upper(p_gl_rec.source_table);
1646
1647 x_ae_line_tbl(i).currency_code := l_amount_tbl(i).curr_code_tc;
1648 x_ae_line_tbl(i).org_id := l_org_id;
1649 --x_ae_line_tbl(i).currency_conversion_type := l_amount_tbl(i).currency_conversion_type;
1650 --x_ae_line_tbl(i).currency_conversion_rate := l_amount_tbl(i).currency_conversion_rate;
1651 --x_ae_line_tbl(i).currency_conversion_date := l_amount_tbl(i).currency_conversion_date;
1652
1653 -- use REFERENCE2 for utilization_id, and REFERENCE3 for claim_line_util_id
1654 x_ae_line_tbl(i).reference2 := l_amount_tbl(i).utilization_id;
1655 x_ae_line_tbl(i).reference3 := l_amount_tbl(i).line_util_id;
1656
1657 x_ae_line_tbl(i).description := get_account_description(
1658 p_gl_rec => p_gl_rec,
1659 p_account_type_code => l_amount_tbl(i).line_type_code,
1660 x_return_status => x_return_status );
1661
1662 IF x_return_status = FND_API.g_ret_sts_error THEN
1663 RAISE FND_API.G_EXC_ERROR;
1664 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
1665 RAISE FND_API.g_exc_unexpected_error;
1666 END IF;
1667
1668 --OZF_UTILITY_PVT.debug_message('Line Number after entering: ' || x_ae_line_tbl(i).ae_line_number);
1669 --if x_ae_line_tbl(i).ae_line_type_code = FND_API.G_MISS_CHAR then
1670 --OZF_UTILITY_PVT.debug_message('Line Type Code after entering - miss char: ' || x_ae_line_tbl(i).ae_line_type_code);
1671 --else
1672 --OZF_UTILITY_PVT.debug_message('Line Type Code after entering: ' || x_ae_line_tbl(i).ae_line_type_code);
1673 --end if;
1674 END LOOP;
1675 --x_ae_line_tbl := l_ae_line_tbl;
1676
1677 -- R12 FXGL Enhancement
1678 IF upper(p_gl_rec.source_table) = 'OZF_CLAIMS_ALL' THEN
1679 G_CLAIM_ID := p_gl_rec.source_id;
1680 l_rec_num := x_ae_line_tbl.count;
1681
1682 l_line_type_code := get_account_type_code(
1683 p_event_type_code => p_gl_rec.event_type_code,
1684 p_event_type => 'CR',
1685 p_adjustment_type => p_gl_rec.adjustment_type,
1686 x_return_status => x_return_status);
1687
1688 IF x_return_status = FND_API.g_ret_sts_error THEN
1689 RAISE FND_API.G_EXC_ERROR;
1690 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
1691 RAISE FND_API.g_exc_unexpected_error;
1692 END IF;
1693
1694 OPEN get_fxgl_amt_csr(p_gl_rec.source_id);
1695 LOOP
1696 FETCH get_fxgl_amt_csr INTO l_amount, l_acctd_amount, l_currency_code;
1697 EXIT WHEN get_fxgl_amt_csr%notfound;
1698
1699 l_rec_num := l_rec_num + 1;
1700
1701 IF l_acctd_amount < 0 THEN
1702 x_ae_line_tbl.extend;
1703 x_ae_line_tbl( l_rec_num ) := null;
1704 x_ae_line_tbl( l_rec_num ).ae_line_number := l_rec_num;
1705 x_ae_line_tbl( l_rec_num ).ae_line_type_code := l_line_type_code;
1706 x_ae_line_tbl( l_rec_num ).code_combination_id := l_fxgl_gain_ccid;
1707 x_ae_line_tbl( l_rec_num ).source_id := p_gl_rec.source_id;
1708 x_ae_line_tbl( l_rec_num ).source_table := upper(p_gl_rec.source_table);
1709 x_ae_line_tbl( l_rec_num ).entered_dr := 0;
1710 x_ae_line_tbl( l_rec_num ).entered_cr := 0;
1711 x_ae_line_tbl( l_rec_num ).accounted_dr := 0;
1712 x_ae_line_tbl( l_rec_num ).accounted_cr := l_acctd_amount * -1;
1713 x_ae_line_tbl( l_rec_num ).currency_code := l_currency_code;
1714 x_ae_line_tbl( l_rec_num ).org_id := l_org_id;
1715 x_ae_line_tbl( l_rec_num ).description := get_account_description(
1716 p_gl_rec => p_gl_rec,
1717 p_account_type_code => l_line_type_code,
1718 x_return_status => x_return_status );
1719
1720 IF x_return_status = FND_API.g_ret_sts_error THEN
1721 RAISE FND_API.G_EXC_ERROR;
1722 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
1723 RAISE FND_API.g_exc_unexpected_error;
1724 END IF;
1725 ELSIF l_acctd_amount > 0 THEN
1726 x_ae_line_tbl.extend;
1727 x_ae_line_tbl( l_rec_num ) := null;
1728 x_ae_line_tbl( l_rec_num ).ae_line_number := l_rec_num;
1729 x_ae_line_tbl( l_rec_num ).ae_line_type_code := l_line_type_code;
1730 x_ae_line_tbl( l_rec_num ).code_combination_id := l_fxgl_loss_ccid;
1731 x_ae_line_tbl( l_rec_num ).source_id := p_gl_rec.source_id;
1732 x_ae_line_tbl( l_rec_num ).source_table := upper(p_gl_rec.source_table);
1733 x_ae_line_tbl( l_rec_num ).entered_dr := 0;
1734 x_ae_line_tbl( l_rec_num ).entered_cr := 0;
1735 x_ae_line_tbl( l_rec_num ).accounted_cr := 0;
1736 x_ae_line_tbl( l_rec_num ).accounted_dr := l_acctd_amount;
1737 x_ae_line_tbl( l_rec_num ).currency_code := l_currency_code;
1738 x_ae_line_tbl( l_rec_num ).org_id := l_org_id;
1739 x_ae_line_tbl( l_rec_num ).description := get_account_description(
1740 p_gl_rec => p_gl_rec,
1741 p_account_type_code => l_line_type_code,
1742 x_return_status => x_return_status );
1743
1744 IF x_return_status = FND_API.g_ret_sts_error THEN
1745 RAISE FND_API.G_EXC_ERROR;
1746 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
1747 RAISE FND_API.g_exc_unexpected_error;
1748 END IF;
1749 END IF;
1750 END LOOP;
1751 CLOSE get_fxgl_amt_csr;
1752 END IF;
1753
1754
1755 EXCEPTION
1756 WHEN FND_API.G_EXC_ERROR THEN
1757 x_return_status := FND_API.G_RET_STS_ERROR;
1758 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1759 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1760 WHEN OTHERS THEN
1761 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1762 FND_MESSAGE.set_name('OZF', 'OZF_GL_AE_LINE_ERROR');
1763 FND_MSG_PUB.add;
1764 END IF;
1765 x_return_status := FND_API.g_ret_sts_unexp_error;
1766 END Construct_Line_Rec;
1767 */
1768 ---------------------------------------------------------------------
1769 /*PROCEDURE Set_Accounting_Rules(
1770 p_api_version IN NUMBER
1771 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
1772 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
1773
1774 ,x_msg_data OUT NOCOPY VARCHAR2
1775 ,x_msg_count OUT NOCOPY NUMBER
1776 ,x_return_status OUT NOCOPY VARCHAR2
1777
1778 ,p_gl_rec IN gl_interface_rec_type
1779 --,p_acctng_entries IN varchar2
1780 --,x_accounting_event_rec OUT NOCOPY OZF_acctng_events_PVT.acctng_event_rec_type
1781 --,x_ae_header_rec OUT NOCOPY OZF_ae_header_PVT.ae_header_rec_type
1782 ,x_actg_tbl OUT NOCOPY actg_tbl_type )
1783 IS
1784 l_api_name CONSTANT VARCHAR2(30) := 'Set_Accounting_Rule';
1785 l_api_version CONSTANT NUMBER := 1.0;
1786 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
1787 --
1788 BEGIN
1789 -- Standard call to check for call compatibility.
1790 IF NOT FND_API.Compatible_API_Call (
1791 l_api_version,
1792 p_api_version,
1793 l_api_name,
1794 G_PKG_NAME)
1795 THEN
1796 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1797 END IF;
1798 -- Debug Message
1799 IF OZF_DEBUG_LOW_ON THEN
1800 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
1801 FND_MESSAGE.Set_Token('TEXT',l_full_name||': Start');
1802 FND_MSG_PUB.Add;
1803 END IF;
1804 --Initialize message list if p_init_msg_list is TRUE.
1805 IF FND_API.To_Boolean (p_init_msg_list) THEN
1806 FND_MSG_PUB.initialize;
1807 END IF;
1808 -- Initialize API return status to sucess
1809 x_return_status := FND_API.G_RET_STS_SUCCESS;
1810
1811
1812
1813 -- construct accounting event
1814 /* Construct_Acctng_Event_Rec(
1815 p_gl_rec => p_gl_rec
1816 ,x_return_status => x_return_status
1817 ,x_accounting_event_rec => x_accounting_event_rec);
1818
1819 IF x_return_status = FND_API.g_ret_sts_error THEN
1820 RAISE FND_API.G_EXC_ERROR;
1821 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
1822 RAISE FND_API.g_exc_unexpected_error;
1823 END IF;
1824
1825 IF p_acctng_entries = 'T' THEN
1826 -- ae header rec
1827 Construct_Header_Rec(
1828 p_gl_rec => p_gl_rec
1829 ,x_return_status => x_return_status
1830 ,x_ae_header_rec => x_ae_header_rec);
1831
1832 IF x_return_status = FND_API.g_ret_sts_error THEN
1833 RAISE FND_API.G_EXC_ERROR;
1834 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
1835 RAISE FND_API.g_exc_unexpected_error;
1836 END IF;
1837
1838 -- ae lines
1839 Construct_Line_Rec(
1840 p_gl_rec => p_gl_rec
1841 ,x_return_status => x_return_status
1842 ,x_ae_line_tbl => x_ae_line_tbl);
1843
1844 IF x_return_status = FND_API.g_ret_sts_error THEN
1845 RAISE FND_API.G_EXC_ERROR;
1846 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
1847 RAISE FND_API.g_exc_unexpected_error;
1848 END IF;
1849 END IF;
1850
1851
1852 get_acctg_accounts(
1853 p_gl_rec => p_gl_rec,
1854 x_actg_tbl => l_actg_tbl,
1855 x_return_status => x_return_status );
1856
1857 IF x_return_status = FND_API.g_ret_sts_error THEN
1858 RAISE FND_API.G_EXC_ERROR;
1859 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
1860 RAISE FND_API.g_exc_unexpected_error;
1861 END IF;
1862
1863 -- Debug Message
1864 IF OZF_DEBUG_LOW_ON THEN
1865 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
1866 FND_MESSAGE.Set_Token('TEXT',l_full_name||': End');
1867 FND_MSG_PUB.Add;
1868 END IF;
1869 --Standard call to get message count and if count=1, get the message
1870 FND_MSG_PUB.Count_And_Get (
1871 p_encoded => FND_API.G_FALSE,
1872 p_count => x_msg_count,
1873 p_data => x_msg_data
1874 );
1875 EXCEPTION
1876 WHEN FND_API.G_EXC_ERROR THEN
1877 x_return_status := FND_API.G_RET_STS_ERROR;
1878 -- Standard call to get message count and if count=1, get the message
1879 FND_MSG_PUB.Count_And_Get (
1880 p_encoded => FND_API.G_FALSE,
1881 p_count => x_msg_count,
1882 p_data => x_msg_data
1883 );
1884 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1885 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1886 -- Standard call to get message count and if count=1, get the message
1887 FND_MSG_PUB.Count_And_Get (
1888 p_encoded => FND_API.G_FALSE,
1889 p_count => x_msg_count,
1890 p_data => x_msg_data
1891 );
1892 WHEN OTHERS THEN
1893 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1894 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1895 THEN
1896 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1897 END IF;
1898 -- Standard call to get message count and if count=1, get the message
1899 FND_MSG_PUB.Count_And_Get (
1900 p_encoded => FND_API.G_FALSE,
1901 p_count => x_msg_count,
1902 p_data => x_msg_data
1903 );
1904 END set_accounting_rules;
1905 */
1906
1907 --//Start ChRM SLA Uptake
1908 ---------------------------------------------------------------------
1909 -- PROCEDURE
1910 -- Create_SLA_Accrual_Extract
1911 --
1912 -- PURPOSE
1913 -- Tis procedure will create event for Accruals and populate SLA Extract
1914 -- table for Accruals,Adjustments and Off-Invoice Offers
1915 --
1916 -- PARAMETERS
1917 -- p_utilization_id - Utilization id
1918 -- p_event_type_code - Event type code
1919 -- p_adj_cr_ccid - Adjustment credit account ccid
1920 -- p_adj_dr_cc_id - Adjustment debit account ccid
1921 --
1922 -- NOTES
1923 --
1924 -- HISTORY
1925 -- 12-Feb-10 BKUNJAN ER#9382547 ChRM-SLA Uptake -Created
1926 ---------------------------------------------------------------------
1927 PROCEDURE Create_SLA_Accrual_Extract (
1928 p_api_version IN NUMBER
1929 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
1930 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
1931 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
1932
1933 ,x_return_status OUT NOCOPY VARCHAR2
1934 ,x_msg_data OUT NOCOPY VARCHAR2
1935 ,x_msg_count OUT NOCOPY NUMBER
1936
1937 ,p_utilization_id IN NUMBER
1938 ,p_event_type_code IN VARCHAR2
1939 ,p_adj_cr_ccid IN NUMBER := NULL
1940 ,p_adj_dr_cc_id IN NUMBER := NULL
1941 ,p_gl_rec IN gl_interface_rec_type
1942 )
1943
1944 IS
1945 l_api_name CONSTANT VARCHAR2(30) := 'Create_SLA_Accrual_Extract';
1946 l_api_version CONSTANT NUMBER := 1.0;
1947 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
1948
1949 l_event_source_info xla_events_pub_pkg.t_event_source_info;
1950 l_reference_info xla_events_pub_pkg.t_event_reference_info;
1951 l_security_context xla_events_pub_pkg.t_security;
1952 l_event_date DATE;
1953 l_xla_event_id NUMBER := 0;
1954 l_event_type_code VARCHAR2(30);
1955 l_org_id NUMBER;
1956 l_gl_date DATE;
1957
1958 -- ER 13069257
1959 l_actg_flag VARCHAR2(1) := 'N';
1960 l_actg_profile VARCHAR2(30);
1961 l_actg_tbl actg_tbl_type;
1962 l_actg_accrual_ccid NUMBER;
1963 l_actg_expense_ccid NUMBER;
1964 l_adjustment_type VARCHAR2(30);
1965 l_revenue_acct NUMBER;
1966
1967 CURSOR c_hr_operating_unit (p_org_id IN NUMBER)IS
1968 SELECT TO_NUMBER(hou.default_legal_context_id) legal_entity,
1969 TO_NUMBER(hou.set_of_books_id) ledger_id
1970 FROM HR_OPERATING_UNITS HOU
1971 WHERE HOU.ORGANIZATION_ID = p_org_id;
1972
1973 --ER 13069257
1974 CURSOR c_util_details IS
1975 SELECT org_id, gl_date,adjustment_type
1976 FROM ozf_funds_utilized_all_b
1977 WHERE utilization_id = p_utilization_id;
1978
1979 CURSOR c_get_entity_type_code(cv_event_type_code IN VARCHAR2) IS
1980 SELECT entity_code
1981 FROM xla_event_types_vl
1982 WHERE application_id = 682
1983 AND event_type_Code = cv_event_type_code;
1984 BEGIN
1985 -- Standard begin of API savepoint
1986 SAVEPOINT Create_SLA_Accrual_Extract;
1987 -- Standard call to check for call compatibility.
1988 IF NOT FND_API.Compatible_API_Call (
1989 l_api_version,
1990 p_api_version,
1991 l_api_name,
1992 G_PKG_NAME)
1993 THEN
1994 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1995 END IF;
1996 -- Debug Message
1997 IF OZF_DEBUG_LOW_ON THEN
1998 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
1999 FND_MESSAGE.Set_Token('TEXT',l_full_name||': Start');
2000 FND_MSG_PUB.Add;
2001 END IF;
2002 --Initialize message list if p_init_msg_list is TRUE.
2003 IF FND_API.To_Boolean (p_init_msg_list) THEN
2004 FND_MSG_PUB.initialize;
2005 END IF;
2006 -- Initialize API return status to sucess
2007 x_return_status := FND_API.G_RET_STS_SUCCESS;
2008
2009 --ER 13069257
2010 OPEN c_util_details;
2011 FETCH c_util_details INTO l_org_id, l_gl_date,l_adjustment_type;
2012 CLOSE c_util_details;
2013
2014 --//Fix for Bug 14498604
2015 IF p_gl_rec.event_type_code = 'INVOICE_DISCOUNT' THEN
2016 l_revenue_acct := OZF_UTILITY_PVT.get_revenue_account(p_utilization_id => p_utilization_id
2017 ,p_org_id => l_org_id);
2018 IF l_revenue_acct IS NULL THEN
2019 IF OZF_DEBUG_LOW_ON THEN
2020 OZF_Utility_PVT.debug_message('OZF_UTILITY_PVT.get_revenue_account() Call :- Revenue Account is Null ');
2021 END IF;
2022 RAISE FND_API.G_EXC_ERROR;
2023 END IF;
2024 END IF;
2025
2026
2027 IF OZF_DEBUG_LOW_ON THEN
2028 OZF_Utility_PVT.debug_message('Adjustment Type: '||l_adjustment_type);
2029 END IF;
2030
2031
2032 l_actg_profile := NVL(fnd_profile.value('OZF_USE_ACCOUNT_GENERATOR'),'N');
2033
2034 /*
2035 If the Account generator profile is YES then we will check for the skip from account generator flag passed from public API, if passed as false then we will
2036 call account generator else if true then we will not call account generator. If the Account Generator profile is NO then
2037 irrespective of the skip flag we will not call the account generator workflow.
2038 */
2039 IF (l_actg_profile = 'Y') THEN
2040 IF (p_gl_rec.skip_account_gen_flag = 'T')THEN
2041 l_actg_flag := 'N';
2042 ELSE
2043 l_actg_flag := 'Y';
2044 END IF;
2045 END IF;
2046
2047 IF OZF_DEBUG_LOW_ON THEN
2048 OZF_Utility_PVT.debug_message('Account Generator Profile Value: '||l_actg_flag);
2049 END IF;
2050 IF(l_actg_flag = 'Y') THEN
2051 get_acctg_accounts(
2052 p_gl_rec => p_gl_rec,
2053 x_actg_tbl => l_actg_tbl,
2054 x_return_status => x_return_status );
2055
2056 IF x_return_status = FND_API.g_ret_sts_error THEN
2057 RAISE FND_API.G_EXC_ERROR;
2058 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
2059 RAISE FND_API.g_exc_unexpected_error;
2060 END IF;
2061 IF OZF_DEBUG_LOW_ON THEN
2062 OZF_Utility_PVT.debug_message('Number of Record l_actg_tbl.count : '||l_actg_tbl.count);
2063 END IF;
2064
2065 --Populate TM SLA Accrual extract table
2066 IF(l_actg_tbl.count > 0) THEN
2067 IF (p_gl_rec.event_type_code = 'PAID_ADJUSTMENT') THEN
2068 l_actg_accrual_ccid := l_actg_tbl(1).CODE_COMBINATION_ID;
2069 ELSIF p_gl_rec.event_type_code = 'INVOICE_DISCOUNT' THEN --ninarasi fix for bug 14144628
2070 l_actg_expense_ccid := l_actg_tbl(1).CODE_COMBINATION_ID;
2071 ELSE
2072 l_actg_expense_ccid := l_actg_tbl(1).CODE_COMBINATION_ID;
2073 l_actg_accrual_ccid := l_actg_tbl(2).CODE_COMBINATION_ID;
2074 END IF;
2075 END IF;
2076
2077 IF OZF_DEBUG_LOW_ON THEN
2078 OZF_Utility_PVT.debug_message('After Loop l_actg_accrual_ccid:'|| l_actg_accrual_ccid);
2079 OZF_Utility_PVT.debug_message('After Loop l_actg_expense_ccid'|| l_actg_expense_ccid);
2080 END IF;
2081
2082 END IF;
2083
2084 IF OZF_DEBUG_LOW_ON THEN
2085 OZF_Utility_PVT.debug_message('--------- Create_SLA_Accrual_Extract ----------');
2086 OZF_Utility_PVT.debug_message('utilization_id : '||p_utilization_id);
2087 OZF_Utility_PVT.debug_message('event_type_code : '||p_event_type_code);
2088 END IF;
2089
2090
2091
2092 OPEN c_hr_operating_unit (l_org_id);
2093 FETCH c_hr_operating_unit INTO l_event_source_info.legal_entity_id,
2094 l_event_source_info.ledger_id;
2095 CLOSE c_hr_operating_unit;
2096
2097 OPEN c_get_entity_type_code(p_event_type_code);
2098 FETCH c_get_entity_type_code INTO l_event_source_info.entity_type_code;
2099 CLOSE c_get_entity_type_code;
2100
2101 l_event_source_info.application_id := 682;
2102 l_event_source_info.entity_type_code := NVL(l_event_source_info.entity_type_code,'ACCRUAL');
2103 l_event_source_info.source_id_int_1 := p_utilization_id;
2104 l_security_context.security_id_int_1 := l_org_id;
2105
2106 --Raise SLA event for the event type
2107 l_xla_event_id := XLA_EVENTS_PUB_PKG.create_event(
2108 p_event_source_info => l_event_source_info,
2109 p_event_type_code => p_event_type_code,
2110 p_event_date => l_gl_date,
2111 p_event_status_code => xla_events_pub_pkg.c_event_unprocessed,
2112 p_event_number => NULL,
2113 p_reference_info => l_reference_info,
2114 p_valuation_method => '',
2115 p_transaction_date => INV_LE_TIMEZONE_PUB.get_le_day_time_for_ou(TRUNC(l_gl_date),l_org_id),
2116 p_security_context => l_security_context);
2117
2118
2119 INSERT INTO OZF_XLA_ACCRUALS
2120 (XLA_ACCRUAL_ID
2121 ,EVENT_TYPE_CODE
2122 ,ENTITY_CODE
2123 ,EVENT_ID
2124 ,UTILIZATION_ID
2125 ,ADJUSTMENT_CR_ACCT_CCID
2126 ,ADJUSTMENT_DR_ACCT_CCID
2127 ,ORG_ID
2128 ,CREATION_DATE
2129 ,CREATED_BY
2130 ,LAST_UPDATE_DATE
2131 ,LAST_UPDATED_BY
2132 ,LAST_UPDATE_LOGIN
2133 ,ACTG_ACCRUAL_ACCT_CCID
2134 ,ACTG_EXPENSE_ACCT_CCID)
2135
2136 VALUES ( OZF_XLA_ACCRUALS_S.NEXTVAL
2137 ,p_event_type_code
2138 ,l_event_source_info.entity_type_code
2139 ,l_xla_event_id
2140 ,p_utilization_id
2141 ,p_adj_cr_ccid
2142 ,p_adj_dr_cc_id
2143 ,l_org_id
2144 ,SYSDATE
2145 ,NVL (fnd_global.user_id, -1)
2146 ,SYSDATE
2147 ,NVL (fnd_global.user_id, -1)
2148 ,NVL (fnd_global.conc_login_id, -1)
2149 ,l_actg_accrual_ccid
2150 ,l_actg_expense_ccid
2151 );
2152
2153
2154 --Standard check of commit
2155 IF FND_API.To_Boolean ( p_commit ) THEN
2156 COMMIT WORK;
2157 END IF;
2158 -- Debug Message
2159 IF OZF_DEBUG_LOW_ON THEN
2160 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
2161 FND_MESSAGE.Set_Token('TEXT',l_full_name||': End');
2162 FND_MSG_PUB.Add;
2163 END IF;
2164 --Standard call to get message count and if count=1, get the message
2165 FND_MSG_PUB.Count_And_Get (
2166 p_encoded => FND_API.G_FALSE,
2167 p_count => x_msg_count,
2168 p_data => x_msg_data
2169 );
2170 EXCEPTION
2171 WHEN DUP_VAL_ON_INDEX THEN --bug 13517522 - raise and catch error if duplicate value is inserted for column UTILIZATION_ID
2172 ROLLBACK TO Create_SLA_Accrual_Extract;
2173 x_return_status := FND_API.G_RET_STS_SUCCESS;
2174
2175 ozf_utility_pvt.write_conc_log('duplicate event raised for accrual');
2176 ozf_utility_pvt.debug_message('duplicate event raised for accrual');
2177
2178 WHEN FND_API.G_EXC_ERROR THEN
2179 ROLLBACK TO Create_SLA_Accrual_Extract;
2180 x_return_status := FND_API.G_RET_STS_ERROR;
2181 -- Standard call to get message count and if count=1, get the message
2182 FND_MSG_PUB.Count_And_Get (
2183 p_encoded => FND_API.G_FALSE,
2184 p_count => x_msg_count,
2185 p_data => x_msg_data
2186 );
2187 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2188 ROLLBACK TO Create_SLA_Accrual_Extract;
2189 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2190 -- Standard call to get message count and if count=1, get the message
2191 FND_MSG_PUB.Count_And_Get (
2192 p_encoded => FND_API.G_FALSE,
2193 p_count => x_msg_count,
2194 p_data => x_msg_data
2195 );
2196 WHEN OTHERS THEN
2197 ROLLBACK TO Create_SLA_Accrual_Extract;
2198 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2199 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2200 THEN
2201 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2202 END IF;
2203 -- Standard call to get message count and if count=1, get the message
2204 FND_MSG_PUB.Count_And_Get (
2205 p_encoded => FND_API.G_FALSE,
2206 p_count => x_msg_count,
2207 p_data => x_msg_data
2208 );
2209
2210 END Create_SLA_Accrual_Extract;
2211
2212 ---------------------------------------------------------------------
2213 -- PROCEDURE
2214 -- Create_SLA_Claim_Extract
2215 --
2216 -- PURPOSE
2217 -- To trigger the SLA events and create Extract entries
2218 -- for promotional claim settlement
2219 -- PARAMETERS
2220 -- p_claim_id : claim_id for which the event is raised.
2221 -- p_event_type_code : event_type_code for the claim.
2222 -- p_reversal_flag : Reversal flag will be used for account
2223 -- reversal.
2224 --
2225 -- NOTES
2226 --
2227 -- HISTORY
2228 -- 05/03/2010 kpatro Created for ER#9382547 ChRM-SLA Uptake
2229 -- 09/19/2011 BKUNJAN ER 12985686 .Added x_xla_event_id parameter
2230 ---------------------------------------------------------------------
2231 PROCEDURE Create_SLA_Claim_Extract (
2232 p_api_version IN NUMBER
2233 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
2234 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
2235 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
2236
2237 ,x_return_status OUT NOCOPY VARCHAR2
2238 ,x_msg_data OUT NOCOPY VARCHAR2
2239 ,x_msg_count OUT NOCOPY NUMBER
2240
2241 ,p_claim_id IN NUMBER
2242 ,p_event_type_code IN VARCHAR2
2243 ,p_gl_rec IN gl_interface_rec_type
2244
2245 ,x_xla_event_id OUT NOCOPY NUMBER
2246 )
2247 IS
2248 l_api_name CONSTANT VARCHAR2(30) := 'Create_SLA_Claim_Extract';
2249 l_api_version CONSTANT NUMBER := 1.0;
2250 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
2251
2252 l_event_source_info xla_events_pub_pkg.t_event_source_info;
2253 l_reference_info xla_events_pub_pkg.t_event_reference_info;
2254 l_security_context xla_events_pub_pkg.t_security;
2255 l_xla_event_id NUMBER := 0;
2256 l_event_type_code VARCHAR2(30);
2257 l_xla_claim_hed_seq NUMBER;
2258
2259 l_org_id NUMBER ;
2260 l_gl_date DATE;
2261 l_claim_date DATE;
2262 l_count_claim_utils NUMBER;
2263 l_claim_line_util_id NUMBER;
2264 l_gl_date_type varchar2(30);
2265 l_counter NUMBER :=0;
2266
2267 l_reversal_flag VARCHAR2(1);
2268
2269 --ER:13069257
2270 l_actg_flag VARCHAR2(30);
2271 l_actg_tbl actg_tbl_type;
2272 l_actg_claim_acc_ccid NUMBER;
2273 l_actg_claim_exp_ccid NUMBER;
2274
2275 l_related_event_id NUMBER := -1;
2276 l_parent_header_id NUMBER;
2277
2278 l_payment_method VARCHAR2(30);
2279 l_cancelled_date DATE;
2280
2281
2282 CURSOR c_hr_operating_unit (p_org_id IN NUMBER) IS
2283 SELECT TO_NUMBER(hou.default_legal_context_id) legal_entity,
2284 TO_NUMBER(hou.set_of_books_id) ledger_id
2285 FROM HR_OPERATING_UNITS HOU
2286 WHERE HOU.ORGANIZATION_ID = p_org_id;
2287
2288 CURSOR c_claim_info (p_claim_id IN NUMBER)IS
2289 SELECT cla.gl_date, cla.org_id, cla.claim_date, osp.gl_date_type,cla.payment_method
2290 FROM ozf_claims_all cla, ozf_sys_parameters_all osp
2291 WHERE claim_id = p_claim_id
2292 AND cla.org_id = osp.org_id;
2293
2294 CURSOR c_count_claim_utils(p_id IN NUMBER) IS
2295 SELECT count(*)
2296 FROM ozf_claim_lines_util_all
2297 WHERE claim_line_id IN (SELECT claim_line_id
2298 FROM ozf_claim_lines_all
2299 WHERE claim_id = p_id);
2300
2301 CURSOR c_get_claim_line_util(p_claim_id IN NUMBER) IS
2302 SELECT clu.claim_line_util_id
2303 FROM ozf_claim_lines_util_all clu,
2304 ozf_claim_lines_all cln
2305 WHERE clu.claim_line_id = cln.claim_line_id
2306 AND cln.claim_id = p_claim_id;
2307
2308
2309 CURSOR c_xla_claims_header_seq IS
2310 SELECT OZF_XLA_CLAIM_HEADERS_S.NEXTVAL
2311 FROM DUAL;
2312
2313 --//ER 12985686
2314 CURSOR c_get_entity_type_code(cv_event_type_code IN VARCHAR2) IS
2315 SELECT entity_code
2316 FROM xla_event_types_vl
2317 WHERE application_id = 682
2318 AND event_type_Code = cv_event_type_code;
2319
2320 -- Fix for Bug#13542516
2321 CURSOR c_get_parent_event_id(p_claim_id IN NUMBER) IS
2322 SELECT event_id,xla_claim_header_id
2323 FROM ozf_xla_claim_headers
2324 WHERE claim_id = p_claim_id
2325 AND event_type_code <> 'CLAIM_SETTLEMENT_REVERSAL'
2326 order by event_id desc;
2327
2328 CURSOR csr_inv_cancel_date(cv_claim_id IN NUMBER) IS
2329 SELECT cancelled_date
2330 FROM ap_invoices_all ap,
2331 ozf_claims_all oc
2332 WHERE claim_id = cv_claim_id
2333 AND TO_NUMBER(ap.reference_key1) = oc.claim_id
2334 AND ap.vendor_id = oc.vendor_id
2335 AND ap.vendor_site_id = oc.vendor_site_id
2336 ORDER BY ap.creation_date desc;
2337
2338
2339 BEGIN
2340 -- Standard begin of API savepoint
2341 SAVEPOINT Create_SLA_Claim_Extract;
2342 -- Standard call to check for call compatibility.
2343 IF NOT FND_API.Compatible_API_Call (
2344 l_api_version,
2345 p_api_version,
2346 l_api_name,
2347 G_PKG_NAME)
2348 THEN
2349 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2350 END IF;
2351 -- Debug Message
2352 IF OZF_DEBUG_LOW_ON THEN
2353 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
2354 FND_MESSAGE.Set_Token('TEXT',l_full_name||': Start');
2355 FND_MSG_PUB.Add;
2356 END IF;
2357 --Initialize message list if p_init_msg_list is TRUE.
2358 IF FND_API.To_Boolean (p_init_msg_list) THEN
2359 FND_MSG_PUB.initialize;
2360 END IF;
2361 -- Initialize API return status to sucess
2362 x_return_status := FND_API.G_RET_STS_SUCCESS;
2363
2364 OPEN c_count_claim_utils (p_claim_id);
2365 FETCH c_count_claim_utils INTO l_count_claim_utils;
2366 CLOSE c_count_claim_utils;
2367
2368 IF OZF_DEBUG_LOW_ON THEN
2369 OZF_Utility_PVT.debug_message('Number of Claim Line Utils: ' || l_count_claim_utils);
2370 END IF;
2371
2372 -- If Association is there then raise the event
2373 IF(l_count_claim_utils > 0) THEN
2374
2375 IF OZF_DEBUG_LOW_ON THEN
2376 OZF_Utility_PVT.debug_message('--------- Create_SLA_Claim_Extract ----------');
2377 OZF_Utility_PVT.debug_message('Claim_ID : '||p_claim_id);
2378 OZF_Utility_PVT.debug_message('event_type_code : '||p_event_type_code);
2379 END IF;
2380 --ER:13069257
2381
2382 l_actg_flag := NVL(fnd_profile.value('OZF_USE_ACCOUNT_GENERATOR'),'N');
2383
2384 IF OZF_DEBUG_LOW_ON THEN
2385 OZF_Utility_PVT.debug_message('Account Generator Profile Value: '||l_actg_flag);
2386 END IF;
2387
2388 /*If the value of OZF:Use Account Generator Workflow is set as YES then call the account generator otherwise not*/
2389
2390 IF(l_actg_flag = 'Y') THEN
2391 IF (p_gl_rec.event_type_code IS NOT NULL AND p_gl_rec.event_type_code <> 'CLAIM_SETTLEMENT_REVERSAL') THEN
2392 get_acctg_accounts(
2393 p_gl_rec => p_gl_rec,
2394 x_actg_tbl => l_actg_tbl,
2395 x_return_status => x_return_status );
2396 IF x_return_status = FND_API.g_ret_sts_error THEN
2397 RAISE FND_API.G_EXC_ERROR;
2398 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
2399 RAISE FND_API.g_exc_unexpected_error;
2400 END IF;
2401 END IF;
2402
2403 END IF;
2404
2405
2406 OPEN c_claim_info(p_claim_id);
2407 FETCH c_claim_info INTO l_gl_date,l_org_id,l_claim_date,l_gl_date_type,l_payment_method;
2408 CLOSE c_claim_info;
2409
2410 OPEN c_hr_operating_unit(l_org_id);
2411 FETCH c_hr_operating_unit INTO l_event_source_info.legal_entity_id,
2412 l_event_source_info.ledger_id;
2413 CLOSE c_hr_operating_unit;
2414
2415
2416 IF OZF_DEBUG_LOW_ON THEN
2417 OZF_Utility_PVT.debug_message('l_gl_date : '||l_gl_date);
2418 OZF_Utility_PVT.debug_message('l_org_id : '||l_org_id);
2419 OZF_Utility_PVT.debug_message('l_claim_date : '||l_claim_date);
2420 END IF;
2421
2422 --//Fix for Bug 16215152
2423 IF p_event_type_code = 'CLAIM_SETTLEMENT_REVERSAL' THEN
2424 IF l_payment_method IN ('CHECK','EFT','WIRE','AP_DEFAULT','AP_DEBIT') THEN
2425 OPEN csr_inv_cancel_date(p_claim_id);
2426 FETCH csr_inv_cancel_date INTO l_cancelled_date;
2427 CLOSE csr_inv_cancel_date;
2428 l_gl_date := NVL(l_cancelled_date,SYSDATE);
2429 ELSE
2430 l_gl_date := SYSDATE;
2431 END IF;
2432 ELSE
2433 IF l_gl_date IS NULL THEN
2434 IF l_gl_date_type = 'CLAIM_DATE' THEN
2435 l_gl_date := l_claim_date;
2436 ELSE --l_gl_date_type = 'SYSTEM_DATE'
2437 l_gl_date := SYSDATE;
2438 END IF;
2439 END IF;
2440 END IF;
2441
2442 l_gl_date := TRUNC(l_gl_date);
2443
2444 --//ER 12985686
2445 OPEN c_get_entity_type_code(p_event_type_code);
2446 FETCH c_get_entity_type_code INTO l_event_source_info.entity_type_code;
2447 CLOSE c_get_entity_type_code;
2448
2449 l_event_source_info.application_id := 682;
2450 l_event_source_info.entity_type_code := l_event_source_info.entity_type_code;
2451 l_event_source_info.source_id_int_1 := p_claim_id;
2452 l_security_context.security_id_int_1 := l_org_id;
2453
2454 l_xla_event_id := XLA_EVENTS_PUB_PKG.create_event(
2455 p_event_source_info => l_event_source_info,
2456 p_event_type_code => p_event_type_code,
2457 p_event_date => l_gl_date,
2458 p_event_status_code => xla_events_pub_pkg.c_event_unprocessed,
2459 p_event_number => NULL,
2460 p_reference_info => l_reference_info,
2461 p_valuation_method => '',
2462 p_transaction_date => INV_LE_TIMEZONE_PUB.get_le_day_time_for_ou(trunc(l_gl_date),l_org_id),
2463 p_security_context => l_security_context);
2464
2465
2466 x_xla_event_id := l_xla_event_id;
2467
2468 IF OZF_DEBUG_LOW_ON THEN
2469 OZF_Utility_PVT.debug_message('l_xla_event_id : '||l_xla_event_id);
2470 OZF_Utility_PVT.debug_message('p_event_type_code : '||p_event_type_code);
2471 END IF;
2472
2473
2474 IF (p_event_type_code IS NOT NULL AND p_event_type_code = 'CLAIM_SETTLEMENT_REVERSAL') THEN
2475 -- Fix for Bug#13542516
2476 OPEN c_get_parent_event_id (p_claim_id);
2477 FETCH c_get_parent_event_id INTO l_related_event_id,l_parent_header_id;
2478 CLOSE c_get_parent_event_id;
2479
2480 IF OZF_DEBUG_LOW_ON THEN
2481 OZF_Utility_PVT.debug_message('l_related_event_id : '||l_related_event_id);
2482 OZF_Utility_PVT.debug_message('l_parent_header_id : '||l_parent_header_id);
2483 END IF;
2484
2485 --update parent's reversal_event_id with the reversal event
2486 UPDATE ozf_xla_claim_headers
2487 SET related_event_id = l_xla_event_id
2488 WHERE xla_claim_header_id = l_parent_header_id;
2489
2490 l_reversal_flag := 'Y';
2491
2492 END IF;
2493
2494 IF OZF_DEBUG_LOW_ON THEN
2495 OZF_Utility_PVT.debug_message('l_reversal_flag : '||l_reversal_flag);
2496 END IF;
2497
2498 OPEN c_xla_claims_header_seq;
2499 FETCH c_xla_claims_header_seq INTO l_xla_claim_hed_seq;
2500 CLOSE c_xla_claims_header_seq;
2501
2502 INSERT INTO OZF_XLA_CLAIM_HEADERS
2503 (XLA_CLAIM_HEADER_ID
2504 ,EVENT_TYPE_CODE
2505 ,ENTITY_CODE
2506 ,EVENT_ID
2507 ,CLAIM_ID
2508 ,ORG_ID
2509 ,CREATION_DATE
2510 ,CREATED_BY
2511 ,LAST_UPDATE_DATE
2512 ,LAST_UPDATED_BY
2513 ,LAST_UPDATE_LOGIN
2514 ,REVERSAL_FLAG
2515 ,related_event_id)
2516
2517 VALUES (l_xla_claim_hed_seq
2518 ,p_event_type_code
2519 ,l_event_source_info.entity_type_code
2520 ,l_xla_event_id
2521 ,p_claim_id
2522 ,l_org_id
2523 ,SYSDATE
2524 ,NVL (fnd_global.user_id, -1)
2525 ,SYSDATE
2526 ,NVL (fnd_global.user_id, -1)
2527 ,NVL (fnd_global.conc_login_id, -1)
2528 ,l_reversal_flag
2529 ,l_related_event_id -- Fix for Bug#13542516
2530 );
2531
2532 IF (p_event_type_code IS NOT NULL AND p_event_type_code <> 'CLAIM_SETTLEMENT_REVERSAL') THEN
2533
2534 OPEN c_get_claim_line_util(p_claim_id);
2535 LOOP
2536 FETCH c_get_claim_line_util INTO l_claim_line_util_id;
2537 l_actg_claim_acc_ccid := null;
2538 l_actg_claim_exp_ccid := null;
2539
2540 IF OZF_DEBUG_LOW_ON THEN
2541 OZF_Utility_PVT.debug_message('Before Inserting l_actg_tbl.count:' || l_actg_tbl.count);
2542 END IF;
2543
2544 IF(l_actg_tbl.count > 0) THEN
2545 IF p_event_type_code IN ('SETTLE_BY_CREDIT_MEMO','SETTLE_BY_AR_AP_NETTING','SETTLE_BY_ACCOUNTING_ONLY',
2546 'SETTLE_BY_AR_CUSTOM','SETTLE_BY_DEBIT_MEMO') THEN
2547 l_actg_claim_acc_ccid := l_actg_tbl(l_claim_line_util_id).CODE_COMBINATION_ID;
2548 ELSIF (p_event_type_code IN ('SETTLE_BY_AP_DEBIT','SETTLE_BY_AP_INVOICE','SETTLE_BY_AP_CUSTOM')) THEN
2549 IF(l_actg_tbl(l_claim_line_util_id).ACCOUNT_TYPE = 'ACCRUAL_LIABILITY') THEN
2550 l_actg_claim_acc_ccid := l_actg_tbl(l_claim_line_util_id).CODE_COMBINATION_ID;
2551 ELSIF(l_actg_tbl(l_claim_line_util_id).ACCOUNT_TYPE = 'EXPENSE ACCOUNT') THEN
2552 l_actg_claim_exp_ccid := l_actg_tbl(l_claim_line_util_id).CODE_COMBINATION_ID;
2553 END IF;
2554
2555 END IF;
2556 END IF;
2557
2558 EXIT WHEN c_get_claim_line_util%notfound;
2559
2560 l_counter := l_counter +1;
2561
2562 INSERT INTO OZF_XLA_CLAIM_LINES
2563 (XLA_CLAIM_LINE_ID
2564 ,XLA_CLAIM_HEADER_ID
2565 ,LINE_NUMBER
2566 ,CLAIM_LINE_UTIL_ID
2567 ,CREATION_DATE
2568 ,CREATED_BY
2569 ,LAST_UPDATE_DATE
2570 ,LAST_UPDATED_BY
2571 ,LAST_UPDATE_LOGIN
2572 ,ACTG_CLAIM_ACC_ACCT_CCID
2573 ,ACTG_CLAIM_EXP_ACCT_CCID)
2574
2575 VALUES (OZF_XLA_CLAIM_LINES_S.NEXTVAL
2576 ,l_xla_claim_hed_seq
2577 ,l_counter
2578 ,l_claim_line_util_id
2579 ,SYSDATE
2580 ,NVL (fnd_global.user_id, -1)
2581 ,SYSDATE
2582 ,NVL (fnd_global.user_id, -1)
2583 ,NVL (fnd_global.conc_login_id, -1)
2584 ,l_actg_claim_acc_ccid
2585 ,l_actg_claim_exp_ccid
2586 );
2587 END LOOP;
2588 CLOSE c_get_claim_line_util;
2589
2590 END IF;
2591
2592 END IF;
2593 --Standard check of commit
2594 IF FND_API.To_Boolean ( p_commit ) THEN
2595 COMMIT WORK;
2596 END IF;
2597 -- Debug Message
2598 IF OZF_DEBUG_LOW_ON THEN
2599 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
2600 FND_MESSAGE.Set_Token('TEXT',l_full_name||': End');
2601 FND_MSG_PUB.Add;
2602 END IF;
2603 --Standard call to get message count and if count=1, get the message
2604 FND_MSG_PUB.Count_And_Get (
2605 p_encoded => FND_API.G_FALSE,
2606 p_count => x_msg_count,
2607 p_data => x_msg_data
2608 );
2609 EXCEPTION
2610 WHEN DUP_VAL_ON_INDEX THEN -- Fix for Bug#13542516
2611 --amitamku fix for bug 16073366 - corrected the savepoint name
2612 ROLLBACK TO Create_SLA_Claim_Extract;
2613 x_return_status := FND_API.G_RET_STS_SUCCESS;
2614
2615 ozf_utility_pvt.write_conc_log('duplicate event raised for Claim Settlement');
2616 ozf_utility_pvt.debug_message('duplicate event raised for Claim Settlement');
2617
2618 WHEN FND_API.G_EXC_ERROR THEN
2619 ROLLBACK TO Create_SLA_Claim_Extract;
2620 x_return_status := FND_API.G_RET_STS_ERROR;
2621 -- Standard call to get message count and if count=1, get the message
2622 FND_MSG_PUB.Count_And_Get (
2623 p_encoded => FND_API.G_FALSE,
2624 p_count => x_msg_count,
2625 p_data => x_msg_data
2626 );
2627 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2628 ROLLBACK TO Create_SLA_Claim_Extract;
2629 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2630 -- Standard call to get message count and if count=1, get the message
2631 FND_MSG_PUB.Count_And_Get (
2632 p_encoded => FND_API.G_FALSE,
2633 p_count => x_msg_count,
2634 p_data => x_msg_data
2635 );
2636 WHEN OTHERS THEN
2637 ROLLBACK TO Create_SLA_Claim_Extract;
2638 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2639 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2640 THEN
2641 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2642 END IF;
2643 -- Standard call to get message count and if count=1, get the message
2644 FND_MSG_PUB.Count_And_Get (
2645 p_encoded => FND_API.G_FALSE,
2646 p_count => x_msg_count,
2647 p_data => x_msg_data
2648 );
2649
2650 END Create_SLA_Claim_Extract;
2651
2652
2653 ---------------------------------------------------------------------
2654 -- PROCEDURE
2655 -- Create_Gl_Entry
2656 --
2657 -- PURPOSE
2658 -- Create a gl entry.
2659 --
2660 -- PARAMETERS
2661 -- p_gl_rec : the new record to be inserted
2662 -- x_event_id : return the claim_id of the new reason code
2663 --
2664 -- NOTES
2665 -- 1. object_version_number will be set to 1.
2666
2667 -- HISTORY
2668 -- 05/03/2010 kpatro Updated for ER#9382547 ChRM-SLA Uptake
2669 --
2670 ---------------------------------------------------------------------
2671 PROCEDURE Create_Gl_Entry (
2672 p_api_version IN NUMBER
2673 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
2674 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
2675 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
2676
2677 ,x_return_status OUT NOCOPY VARCHAR2
2678 ,x_msg_data OUT NOCOPY VARCHAR2
2679 ,x_msg_count OUT NOCOPY NUMBER
2680
2681 ,p_gl_rec IN gl_interface_rec_type
2682 )
2683 IS
2684 l_api_name CONSTANT VARCHAR2(30) := 'Create_Gl_Entry';
2685 l_api_version CONSTANT NUMBER := 1.0;
2686 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
2687
2688 l_post_to_gl varchar2(1) := 'X';
2689 l_off_inv_gl varchar2(1);
2690 l_xla_event_id NUMBER;
2691 l_utiz_amount NUMBER; --ninarasi fix for bug 13054588
2692 l_utiz_amt_rem NUMBER; -- Fix for Bug 13596344
2693
2694 CURSOR accrual_gl_posting_csr(p_id in number) IS
2695 SELECT NVL(osp.post_to_gl, 'F')
2696 , NVL(osp.gl_acct_for_offinv_flag, 'F'),plan_curr_amount, --ninarasi fix for bug 13054588
2697 plan_curr_amount_remaining -- Fix for Bug 13596344
2698 FROM ozf_sys_parameters_all osp
2699 , ozf_funds_utilized_all_b ofa
2700 WHERE osp.org_id = ofa.org_id
2701 AND ofa.utilization_id = p_id;
2702
2703 CURSOR claim_gl_posting_csr(p_id in number) IS
2704 SELECT NVL(osp.post_to_gl, 'F')
2705 FROM ozf_sys_parameters_all osp
2706 , ozf_claims_all oc
2707 WHERE osp.org_id = oc.org_id
2708 AND oc.claim_id = p_id;
2709
2710 --//Bug fix 10037158
2711 CURSOR offer_type_csr(p_id in number) IS
2712 SELECT offer_type FROM
2713 ozf_offers a,
2714 ozf_funds_utilized_all_b b
2715 WHERE a.qp_list_header_id = b.plan_id
2716 AND b.utilization_id = p_id;
2717
2718 l_offer_type varchar2(30) ;
2719
2720 BEGIN
2721 -- Standard begin of API savepoint
2722 SAVEPOINT Create_Gl_Entry_PVT;
2723 -- Standard call to check for call compatibility.
2724 IF NOT FND_API.Compatible_API_Call (
2725 l_api_version,
2726 p_api_version,
2727 l_api_name,
2728 G_PKG_NAME)
2729 THEN
2730 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2731 END IF;
2732 -- Debug Message
2733 IF OZF_DEBUG_LOW_ON THEN
2734 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
2735 FND_MESSAGE.Set_Token('TEXT',l_full_name||': Start');
2736 FND_MSG_PUB.Add;
2737 END IF;
2738 --Initialize message list if p_init_msg_list is TRUE.
2739 IF FND_API.To_Boolean (p_init_msg_list) THEN
2740 FND_MSG_PUB.initialize;
2741 END IF;
2742 -- Initialize API return status to sucess
2743 x_return_status := FND_API.G_RET_STS_SUCCESS;
2744
2745 IF OZF_DEBUG_LOW_ON THEN
2746 OZF_Utility_PVT.debug_message('--------- create_gl_entry ----------');
2747 OZF_Utility_PVT.debug_message('event_type_code : '||p_gl_rec.event_type_code);
2748 OZF_Utility_PVT.debug_message('source_id : '||p_gl_rec.source_id);
2749 OZF_Utility_PVT.debug_message('source_table : '||p_gl_rec.source_table);
2750 END IF;
2751
2752
2753 --//ER#9382547 ChRM-SLA Uptake
2754 /*
2755 -- [BEGIN OF BUG 4039894 FIXING]
2756 -- Avoid GL entry creation if posting was done before for utilization.
2757 IF p_gl_rec.source_table = 'OZF_FUNDS_UTILIZED_ALL_B' THEN
2758 OPEN chk_ae_exist(p_gl_rec.source_id, 'OZF_FUNDS_UTILIZED_ALL_B');
2759 FETCH chk_ae_exist INTO l_acc_event_id;
2760 CLOSE chk_ae_exist;
2761
2762 IF l_acc_event_id IS NOT NULL THEN
2763 IF OZF_DEBUG_HIGH_ON THEN
2764 FND_MESSAGE.set_name('OZF', 'OZF_ACCT_GL_ENTRY_EXIST');
2765 FND_MESSAGE.set_token('SOURCE_ID', p_gl_rec.source_id);
2766 FND_MESSAGE.set_token('SOURCE_TABLE', p_gl_rec.source_table);
2767 FND_MSG_PUB.add;
2768 END IF;
2769 RAISE FND_API.G_EXC_ERROR;
2770 END IF;
2771 END IF;
2772 -- [END OF BUG 4039894 FIXING]
2773 */
2774
2775
2776 -- check if post to gl flag in system parameter is set to T
2777 IF upper(p_gl_rec.source_table) = 'OZF_CLAIMS_ALL' THEN
2778 OPEN claim_gl_posting_csr(p_gl_rec.source_id);
2779 FETCH claim_gl_posting_csr INTO l_post_to_gl;
2780 CLOSE claim_gl_posting_csr;
2781 ELSIF upper(p_gl_rec.source_table) = 'OZF_FUNDS_UTILIZED_ALL_B' THEN
2782 --Fix for Bug 13596344
2783 OPEN accrual_gl_posting_csr(p_gl_rec.source_id);
2784 FETCH accrual_gl_posting_csr INTO l_post_to_gl, l_off_inv_gl,
2785 l_utiz_amount,l_utiz_amt_rem; --ninarasi fix for bug 13054588
2786 CLOSE accrual_gl_posting_csr;
2787
2788
2789 IF l_post_to_gl = 'X' THEN --ninarasi fix for bug 13793109
2790 OZF_Utility_PVT.debug_message('Inside null : '||l_post_to_gl);
2791 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2792 FND_MESSAGE.set_name('OZF', 'OZF_GL_NO_SYSPARAM');
2793 FND_MSG_PUB.add;
2794 END IF;
2795 x_return_status := FND_API.g_ret_sts_error;
2796 return;
2797 END IF;
2798
2799 -- Bug Fix 10037158
2800 OPEN offer_type_csr(p_gl_rec.source_id);
2801 FETCH offer_type_csr INTO l_offer_type;
2802 CLOSE offer_type_csr;
2803 END IF;
2804
2805 IF OZF_DEBUG_LOW_ON THEN
2806 OZF_Utility_PVT.debug_message('SYSPARAM: Post to GL is ' || l_post_to_gl);
2807 OZF_Utility_PVT.debug_message('SYSPARAM: Post to Offinvoice is ' || l_off_inv_gl);
2808 END IF;
2809
2810 -- check if event type is off_invoice. create gl entries only when system
2811 -- parameters requires so
2812 IF p_gl_rec.event_type_code = 'INVOICE_DISCOUNT' AND l_off_inv_gl = 'F' THEN --ninarasi fix for bug 14210080
2813 l_post_to_gl := 'F';
2814 IF OZF_DEBUG_LOW_ON THEN
2815 OZF_Utility_PVT.debug_message('GL posting for Off Invoice is not required when gl_acct_for_offinv_flag is not checked.');
2816 END IF;
2817 OZF_Utility_PVT.write_conc_log('GL posting for Off Invoice is not required when gl_acct_for_offinv_flag is not checked.');
2818 END IF;
2819
2820 --//Bugfix 10037158
2821 IF UPPER(p_gl_rec.source_table) = 'OZF_FUNDS_UTILIZED_ALL_B' AND l_offer_type = 'TERMS' THEN
2822 l_post_to_gl := 'F';
2823 IF OZF_DEBUG_LOW_ON THEN
2824 OZF_Utility_PVT.debug_message('GL posting for Terms Upgrade Offer is not required.');
2825 END IF;
2826 END IF;
2827
2828 -- Fix for Bug 13596344
2829 IF (p_gl_rec.event_type_code = 'PAID_ADJUSTMENT' AND upper(p_gl_rec.source_table) = 'OZF_FUNDS_UTILIZED_ALL_B')THEN
2830 l_utiz_amount := l_utiz_amt_rem;
2831 END IF;
2832
2833 --ninarasi fix for bug 13054588
2834 IF l_post_to_gl <> 'F' AND l_utiz_amount = 0 THEN
2835 l_post_to_gl := 'F';
2836 IF OZF_DEBUG_LOW_ON THEN
2837 OZF_Utility_PVT.write_conc_log('GL Posting for $0 accruals is not required.');
2838 END IF;
2839 END IF;
2840
2841
2842 --//ER#9382547 ChRM-SLA Uptake
2843 /*
2844 -- check if claim has promotions. create gl entries only for promotional claims
2845 IF upper(p_gl_rec.source_table) = 'OZF_CLAIMS_ALL' THEN
2846 OPEN promo_claim_csr(p_gl_rec.source_id);
2847 FETCH promo_claim_csr INTO l_asso_amount;
2848 CLOSE promo_claim_csr;
2849
2850 IF l_asso_amount = 0 THEN
2851 l_post_to_gl := 'F';
2852 IF OZF_DEBUG_LOW_ON THEN
2853 OZF_Utility_PVT.debug_message('Claim has no earnings associated');
2854 END IF;
2855 END IF;
2856 END IF;
2857 */
2858 -- create entries in GL interface tables only when post_to_gl is T
2859
2860 --//ER#9382547 ChRM-SLA Uptake
2861 -- Here we check the post to General ledger flag from system parameter.
2862 -- If it us cheked and based on the source table we trigger SLA event and
2863 -- create the extract table for accrual and claim settlement
2864 OZF_Utility_PVT.debug_message('SYSPARAM: Post to GL is ' || l_post_to_gl);
2865 OZF_Utility_PVT.debug_message('Source Table : ' || p_gl_rec.source_table);
2866 IF l_post_to_gl = 'T' THEN
2867 IF UPPER(p_gl_rec.source_table) = 'OZF_FUNDS_UTILIZED_ALL_B' THEN
2868
2869 OZF_Utility_PVT.write_conc_log('Before Calling Create_SLA_Accrual_Extract');
2870 Create_SLA_Accrual_Extract (
2871 p_api_version => 1.0
2872 ,p_init_msg_list => FND_API.G_FALSE
2873 ,p_commit => FND_API.G_FALSE
2874 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
2875 ,x_return_status => x_return_status
2876 ,x_msg_data => x_msg_data
2877 ,x_msg_count => x_msg_count
2878 ,p_utilization_id => p_gl_rec.source_id
2879 ,p_event_type_code => p_gl_rec.event_type_code
2880 ,p_adj_cr_ccid => p_gl_rec.cr_code_combination_id
2881 ,p_adj_dr_cc_id => p_gl_rec.dr_code_combination_id
2882 ,p_gl_rec => p_gl_rec
2883 );
2884
2885 IF x_return_status = FND_API.g_ret_sts_error THEN
2886 RAISE FND_API.G_EXC_ERROR;
2887 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
2888 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2889 END IF;
2890 ELSIF UPPER(p_gl_rec.source_table) = 'OZF_CLAIMS_ALL' THEN
2891
2892 Create_SLA_Claim_Extract (
2893 p_api_version => 1.0
2894 ,p_init_msg_list => FND_API.G_FALSE
2895 ,p_commit => FND_API.G_FALSE
2896 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
2897 ,x_return_status => x_return_status
2898 ,x_msg_data => x_msg_data
2899 ,x_msg_count => x_msg_count
2900 ,p_claim_id => p_gl_rec.source_id
2901 ,p_event_type_code => p_gl_rec.event_type_code
2902 ,p_gl_rec => p_gl_rec
2903 ,x_xla_event_id => l_xla_event_id
2904 );
2905
2906 IF x_return_status = FND_API.g_ret_sts_error THEN
2907 RAISE FND_API.G_EXC_ERROR;
2908 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
2909 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2910 END IF;
2911 END IF;
2912 END IF; --IF l_post_to_gl = 'T' THEN
2913
2914 --//ER#9382547 ChRM-SLA Uptake
2915 --//Start Skipping Existing code for GL Interfacing
2916 /*
2917 --l_acctng_entries := nvl(fnd_profile.value('OZF_ACCT_GEN_ONLINE'),'T');
2918
2919 -- get the SLA table values populated
2920 Set_Accounting_Rules(
2921 P_Api_Version => 1.0,
2922 P_Init_Msg_List => FND_API.G_FALSE,
2923 P_Validation_Level => FND_API.G_VALID_LEVEL_FULL,
2924 X_Msg_Data => x_msg_data,
2925 X_Msg_Count => x_msg_count,
2926 X_Return_Status => x_return_status,
2927 p_gl_rec => p_gl_rec,
2928 p_acctng_entries => l_acctng_entries,
2929 x_accounting_event_rec => l_acctng_event_rec,
2930 x_ae_header_rec => l_ae_header_rec,
2931 x_ae_line_tbl => l_ae_line_tbl );
2932
2933 IF x_return_status = FND_API.g_ret_sts_error THEN
2934 --IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2935 -- FND_MESSAGE.set_name('OZF', 'OZF_GL_ACCT_RULE_ERROR');
2936 -- FND_MSG_PUB.add;
2937 --END IF;
2938 RAISE FND_API.G_EXC_ERROR;
2939 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
2940 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2941 END IF;
2942
2943 -- populate interface tables for GL enter creation
2944 -- Create accounting event
2945 OZF_acctng_events_PVT.Create_acctng_events(
2946 P_Api_Version_Number => 1.0,
2947 P_Init_Msg_List => FND_API.G_FALSE,
2948 P_Commit => FND_API.G_FALSE,
2949 P_Validation_Level => FND_API.G_VALID_LEVEL_FULL,
2950 X_Return_Status => x_return_status,
2951 X_Msg_Count => x_msg_count,
2952 X_Msg_Data => x_msg_data,
2953 P_ACCTNG_EVENT_Rec => l_ACCTNG_EVENT_Rec,
2954 X_ACCOUNTING_EVENT_ID => l_ACCOUNTING_EVENT_ID);
2955
2956 IF x_return_status = FND_API.g_ret_sts_error THEN
2957 RAISE FND_API.G_EXC_ERROR;
2958 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
2959 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2960 END IF;
2961
2962 IF l_acctng_entries = 'T' THEN
2963
2964 -- Create account event header
2965 l_ae_header_rec.accounting_event_id := l_accounting_event_id;
2966 OZF_ae_header_PVT.Create_ae_header(
2967 P_Api_Version_Number => 1.0,
2968 P_Init_Msg_List => FND_API.G_FALSE,
2969 P_Commit => FND_API.G_FALSE,
2970 P_Validation_Level => FND_API.G_VALID_LEVEL_FULL,
2971 X_Return_Status => x_return_status,
2972 X_Msg_Count => x_msg_count,
2973 X_Msg_Data => x_msg_data,
2974 P_AE_HEADER_Rec => l_AE_HEADER_Rec,
2975 X_AE_HEADER_ID => l_AE_HEADER_ID);
2976
2977 IF x_return_status = FND_API.g_ret_sts_error THEN
2978 RAISE FND_API.G_EXC_ERROR;
2979 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
2980 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2981 END IF;
2982
2983 -- Create account event lines
2984 FOR i in 1..l_AE_LINE_Tbl.count LOOP
2985 l_AE_LINE_Tbl(i).ae_header_id := l_ae_header_id;
2986 IF OZF_DEBUG_HIGH_ON THEN
2987 OZF_UTILITY_PVT.debug_message('Line Number before calling api: ' ||
2988 l_ae_line_tbl(i).ae_line_number);
2989 END IF;
2990 END LOOP;
2991
2992 OZF_ae_line_PVT.Create_ae_line(
2993 P_Api_Version_Number => 1.0,
2994 P_Init_Msg_List => FND_API.G_FALSE,
2995 P_Commit => FND_API.G_FALSE,
2996 P_Validation_Level => FND_API.G_VALID_LEVEL_FULL,
2997 X_Return_Status => x_return_status,
2998 X_Msg_Count => x_msg_count,
2999 X_Msg_Data => x_msg_data,
3000 P_AE_LINE_Tbl => l_AE_LINE_Tbl,
3001 X_AE_LINE_ID => l_AE_LINE_ID_Tbl);
3002
3003 IF x_return_status = FND_API.g_ret_sts_error THEN
3004 RAISE FND_API.G_EXC_ERROR;
3005 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
3006 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3007 END IF;
3008
3009 END IF; -- if l_acctng_entries = 'T'
3010
3011 IF OZF_DEBUG_LOW_ON THEN
3012 OZF_Utility_PVT.debug_message('Accounting event: id = '||l_accounting_event_id);
3013 END IF;
3014 */
3015 --//End Skipping Existing code for GL Interfacing
3016
3017 -- pass accounting event id
3018 --//ER#9382547 ChRM-SLA Uptake
3019
3020 --Standard check of commit
3021 IF FND_API.To_Boolean ( p_commit ) THEN
3022 COMMIT WORK;
3023 END IF;
3024 -- Debug Message
3025 IF OZF_DEBUG_LOW_ON THEN
3026 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
3027 FND_MESSAGE.Set_Token('TEXT',l_full_name||': End');
3028 FND_MSG_PUB.Add;
3029 END IF;
3030 --Standard call to get message count and if count=1, get the message
3031 FND_MSG_PUB.Count_And_Get (
3032 p_encoded => FND_API.G_FALSE,
3033 p_count => x_msg_count,
3034 p_data => x_msg_data
3035 );
3036 EXCEPTION
3037 WHEN FND_API.G_EXC_ERROR THEN
3038 ROLLBACK TO Create_Gl_Entry_PVT;
3039 x_return_status := FND_API.G_RET_STS_ERROR;
3040 -- Standard call to get message count and if count=1, get the message
3041 FND_MSG_PUB.Count_And_Get (
3042 p_encoded => FND_API.G_FALSE,
3043 p_count => x_msg_count,
3044 p_data => x_msg_data
3045 );
3046 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3047 ROLLBACK TO Create_Gl_Entry_PVT;
3048 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3049 -- Standard call to get message count and if count=1, get the message
3050 FND_MSG_PUB.Count_And_Get (
3051 p_encoded => FND_API.G_FALSE,
3052 p_count => x_msg_count,
3053 p_data => x_msg_data
3054 );
3055 WHEN OTHERS THEN
3056 ROLLBACK TO Create_Gl_Entry_PVT;
3057 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3058 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3059 THEN
3060 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
3061 END IF;
3062 -- Standard call to get message count and if count=1, get the message
3063 FND_MSG_PUB.Count_And_Get (
3064 p_encoded => FND_API.G_FALSE,
3065 p_count => x_msg_count,
3066 p_data => x_msg_data
3067 );
3068 --
3069 END Create_Gl_Entry;
3070 ---------------------------------------------------------------------
3071 -- PROCEDURE
3072 -- Create_Acctng_Entries
3073 --
3074 -- PURPOSE
3075 -- Create accounting headers and lines
3076 --
3077 -- PARAMETERS
3078 -- p_gl_rec : the new record to be inserted
3079 -- x_event_id : return the claim_id of the new reason code
3080 --
3081 -- NOTES
3082 -- 1. object_version_number will be set to 1.
3083 ---------------------------------------------------------------------
3084 /*PROCEDURE Create_Acctng_Entries (
3085 p_api_version IN NUMBER
3086 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
3087 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
3088 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
3089
3090 ,x_return_status OUT NOCOPY VARCHAR2
3091 ,x_msg_data OUT NOCOPY VARCHAR2
3092 ,x_msg_count OUT NOCOPY NUMBER
3093
3094 ,p_event_id IN NUMBER
3095 ,p_gl_rec IN gl_interface_rec_type
3096 )
3097 IS
3098 l_api_name CONSTANT VARCHAR2(30) := 'Create_Acctng_Entries';
3099 l_api_version CONSTANT NUMBER := 1.0;
3100 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
3101 --
3102 l_acctng_event_rec OZF_acctng_events_PVT.acctng_event_rec_type;
3103 l_ae_header_rec OZF_ae_header_PVT.ae_header_rec_type;
3104 l_ae_line_tbl OZF_ae_line_PVT.ae_line_tbl_type;
3105
3106 l_accounting_event_id number := p_event_id;
3107 l_ae_header_id number;
3108 l_ae_line_id_tbl OZF_ae_line_PVT.number_tbl_type;
3109 --
3110 l_acctng_entries varchar2(3);
3111 l_event_status_code varchar2(30);
3112
3113 CURSOR check_acct_status_csr (p_id in number) IS
3114 select event_status_code
3115 from ozf_acctng_events_all
3116 where accounting_event_id = p_id;
3117
3118 BEGIN
3119 -- Standard begin of API savepoint
3120 SAVEPOINT Create_Acctng_Entries;
3121 -- Standard call to check for call compatibility.
3122 IF NOT FND_API.Compatible_API_Call (
3123 l_api_version,
3124 p_api_version,
3125 l_api_name,
3126 G_PKG_NAME)
3127 THEN
3128 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3129 END IF;
3130 -- Debug Message
3131 IF OZF_DEBUG_LOW_ON THEN
3132 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
3133 FND_MESSAGE.Set_Token('TEXT',l_full_name||': Start');
3134 FND_MSG_PUB.Add;
3135 END IF;
3136 --Initialize message list if p_init_msg_list is TRUE.
3137 IF FND_API.To_Boolean (p_init_msg_list) THEN
3138 FND_MSG_PUB.initialize;
3139 END IF;
3140 -- Initialize API return status to sucess
3141 x_return_status := FND_API.G_RET_STS_SUCCESS;
3142
3143 OPEN check_acct_status_csr(l_accounting_event_id);
3144 FETCH check_acct_status_csr INTO l_event_status_code;
3145 CLOSE check_acct_status_csr;
3146
3147 -- raise error if the event status code is already accounted
3148 IF l_event_status_code = 'ACCOUNTED' THEN
3149 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3150 FND_MESSAGE.set_name('OZF', 'OZF_GL_INCORR_EVENT_STATUS');
3151 FND_MSG_PUB.add;
3152 END IF;
3153 RAISE FND_API.G_EXC_ERROR;
3154 END IF;
3155
3156 -- Construct Header Rec
3157 -- ae header rec
3158 Construct_Header_Rec(
3159 p_gl_rec => p_gl_rec
3160 ,x_return_status => x_return_status
3161 ,x_ae_header_rec => l_ae_header_rec);
3162
3163 IF x_return_status = FND_API.g_ret_sts_error THEN
3164 RAISE FND_API.G_EXC_ERROR;
3165 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
3166 RAISE FND_API.g_exc_unexpected_error;
3167 END IF;
3168
3169 -- ae lines
3170 Construct_Line_Rec(
3171 p_gl_rec => p_gl_rec
3172 ,x_return_status => x_return_status
3173 ,x_ae_line_tbl => l_ae_line_tbl);
3174
3175 IF x_return_status = FND_API.g_ret_sts_error THEN
3176 RAISE FND_API.G_EXC_ERROR;
3177 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
3178 RAISE FND_API.g_exc_unexpected_error;
3179 END IF;
3180
3181 -- Create account event header
3182 l_ae_header_rec.accounting_event_id := l_accounting_event_id;
3183 OZF_ae_header_PVT.Create_ae_header(
3184 P_Api_Version_Number => 1.0,
3185 P_Init_Msg_List => FND_API.G_FALSE,
3186 P_Commit => FND_API.G_FALSE,
3187 P_Validation_Level => FND_API.G_VALID_LEVEL_FULL,
3188 X_Return_Status => x_return_status,
3189 X_Msg_Count => x_msg_count,
3190 X_Msg_Data => x_msg_data,
3191 P_AE_HEADER_Rec => l_AE_HEADER_Rec,
3192 X_AE_HEADER_ID => l_AE_HEADER_ID);
3193
3194 IF x_return_status = FND_API.g_ret_sts_error THEN
3195 RAISE FND_API.G_EXC_ERROR;
3196 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
3197 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3198 END IF;
3199
3200 -- Create account event lines
3201 FOR i in 1..l_AE_LINE_Tbl.count LOOP
3202 l_AE_LINE_Tbl(i).ae_header_id := l_ae_header_id;
3203 IF OZF_DEBUG_HIGH_ON THEN
3204 OZF_UTILITY_PVT.debug_message('Line Number before calling api: ' ||
3205 l_ae_line_tbl(i).ae_line_number);
3206 END IF;
3207 END LOOP;
3208
3209 OZF_ae_line_PVT.Create_ae_line(
3210 P_Api_Version_Number => 1.0,
3211 P_Init_Msg_List => FND_API.G_FALSE,
3212 P_Commit => FND_API.G_FALSE,
3213 P_Validation_Level => FND_API.G_VALID_LEVEL_FULL,
3214 X_Return_Status => x_return_status,
3215 X_Msg_Count => x_msg_count,
3216 X_Msg_Data => x_msg_data,
3217 P_AE_LINE_Tbl => l_AE_LINE_Tbl,
3218 X_AE_LINE_ID => l_AE_LINE_ID_Tbl);
3219
3220 IF x_return_status = FND_API.g_ret_sts_error THEN
3221 RAISE FND_API.G_EXC_ERROR;
3222 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
3223 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3224 END IF;
3225
3226 -- Update accounting events table
3227 UPDATE ozf_acctng_events_all
3228 SET event_status_code = 'ACCOUNTED'
3229 where accounting_event_id = l_accounting_event_id;
3230
3231 --Standard check of commit
3232 IF FND_API.To_Boolean ( p_commit ) THEN
3233 COMMIT WORK;
3234 END IF;
3235 -- Debug Message
3236 IF OZF_DEBUG_LOW_ON THEN
3237 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
3238 FND_MESSAGE.Set_Token('TEXT',l_full_name||': End');
3239 FND_MSG_PUB.Add;
3240 END IF;
3241 --Standard call to get message count and if count=1, get the message
3242 FND_MSG_PUB.Count_And_Get (
3243 p_encoded => FND_API.G_FALSE,
3244 p_count => x_msg_count,
3245 p_data => x_msg_data
3246 );
3247 EXCEPTION
3248 WHEN FND_API.G_EXC_ERROR THEN
3249 ROLLBACK TO Create_Acctng_Entries;
3250 x_return_status := FND_API.G_RET_STS_ERROR;
3251 -- Standard call to get message count and if count=1, get the message
3252 FND_MSG_PUB.Count_And_Get (
3253 p_encoded => FND_API.G_FALSE,
3254 p_count => x_msg_count,
3255 p_data => x_msg_data
3256 );
3257 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3258 ROLLBACK TO Create_Acctng_Entries;
3259 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3260 -- Standard call to get message count and if count=1, get the message
3261 FND_MSG_PUB.Count_And_Get (
3262 p_encoded => FND_API.G_FALSE,
3263 p_count => x_msg_count,
3264 p_data => x_msg_data
3265 );
3266 WHEN OTHERS THEN
3267 ROLLBACK TO Create_Acctng_Entries;
3268 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3269 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3270 THEN
3271 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
3272 END IF;
3273 -- Standard call to get message count and if count=1, get the message
3274 FND_MSG_PUB.Count_And_Get (
3275 p_encoded => FND_API.G_FALSE,
3276 p_count => x_msg_count,
3277 p_data => x_msg_data
3278 );
3279 --
3280 END Create_Acctng_Entries;
3281 */
3282 ---------------------------------------------------------------------
3283
3284 -- Start R12 Enhancements
3285
3286 ---------------------------------------------------------------------
3287 -- PROCEDURE
3288 -- Revert_GL_Entry
3289 --
3290 -- PURPOSE
3291 -- When promotional claims are cancelled, this API is called to
3292 -- delete corresponding accounting entries. If the entries
3293 -- are already interfaced to GL, entries in reverse will be
3294 -- created to undo the posting.
3295 --
3296 -- PARAMETERS
3297 -- p_claim_id : the claim that is cancelled
3298 --
3299 -- NOTES
3300 ---------------------------------------------------------------------
3301 /*PROCEDURE Revert_GL_Entry (
3302 p_api_version IN NUMBER
3303 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
3304 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
3305 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
3306
3307 ,x_return_status OUT NOCOPY VARCHAR2
3308 ,x_msg_data OUT NOCOPY VARCHAR2
3309 ,x_msg_count OUT NOCOPY NUMBER
3310
3311 ,p_claim_id IN NUMBER
3312 )
3313 IS
3314 l_api_name CONSTANT VARCHAR2(30) := 'Revert_GL_Entry';
3315 l_api_version CONSTANT NUMBER := 1.0;
3316 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
3317
3318 l_acctng_event_rec OZF_ACCTNG_EVENTS_PVT.acctng_event_rec_type;
3319 l_ae_header_rec OZF_AE_HEADER_PVT.ae_header_rec_type;
3320 l_ae_line_tbl OZF_AE_LINE_PVT.ae_line_tbl_type;
3321
3322 l_new_event_id NUMBER;
3323 l_new_header_id NUMBER;
3324 l_new_line_id_tbl OZF_AE_LINE_PVT.number_tbl_type;
3325
3326 l_ae_header_id NUMBER;
3327 l_aeh_object_version_num NUMBER;
3328 l_gl_transfer_yn VARCHAR2(1);
3329 l_ae_line_id NUMBER;
3330 l_ael_object_version_num NUMBER;
3331 l_accounting_event_id NUMBER;
3332 l_ae_object_version_num NUMBER;
3333
3334 k PLS_INTEGER := 1;
3335
3336 l_accounted_cr NUMBER;
3337 l_accounted_dr NUMBER;
3338 l_ae_line_number NUMBER;
3339 l_ae_line_type_code VARCHAR2(30);
3340 l_ccid NUMBER;
3341 l_currency_code VARCHAR2(30);
3342 l_description VARCHAR2(240);
3343 l_entered_cr NUMBER;
3344 l_entered_dr NUMBER;
3345 l_source_id NUMBER;
3346 l_source_table VARCHAR2(30);
3347 l_org_id NUMBER;
3348
3349 --//Bug 7633112
3350 l_reference2 VARCHAR2(240);
3351 l_reference3 VARCHAR2(240);
3352
3353 --Bug7391920
3354 l_period_closed VARCHAR2(1);
3355 l_set_of_books_id NUMBER;
3356
3357 -- Get account event details for given source_id
3358 -- For now accounting entries are reverted only for CLAIMS
3359 -- *TODO* scope to make this generic
3360 CURSOR get_accounting_event_csr( p_source_id in NUMBER ) IS
3361 SELECT accounting_event_id
3362 , event_number
3363 , event_status_code
3364 , event_type_code
3365 , source_id
3366 , source_table
3367 , org_id
3368 , accounting_date
3369 , object_version_number
3370 from ozf_acctng_events_all
3371 where source_table = 'OZF_CLAIMS_ALL'
3372 and source_id = p_source_id;
3373
3374 -- Get header details for given accounting event
3375 CURSOR get_ae_header_csr( p_accounting_event_id in NUMBER ) IS
3376 select ae_header_id
3377 , ae_category
3378 , cross_currency_flag
3379 , description
3380 , gl_reversal_flag
3381 , period_name
3382 , set_of_books_id
3383 , gl_transfer_flag
3384 , org_id
3385 , object_version_number
3386 from ozf_ae_headers_all
3387 where accounting_event_id = p_accounting_event_id;
3388
3389 -- Get line ids for given header
3390 CURSOR get_ae_lines_csr( p_ae_header_id in NUMBER ) IS
3391 select ae_line_id
3392 , object_version_number
3393 from ozf_ae_lines_all
3394 where ae_header_id = p_ae_header_id;
3395
3396 -- Get line details for given header
3397 --//Bug 7633112
3398 CURSOR get_ae_lines_dtl_csr( p_ae_header_id in NUMBER ) IS
3399 select accounted_cr
3400 , accounted_dr
3401 , entered_cr
3402 , entered_dr
3403 , ae_line_number
3404 , ae_line_type_code
3405 , code_combination_id
3406 , currency_code
3407 , description
3408 , source_id
3409 , source_table
3410 , org_id
3411 , reference2
3412 , reference3
3413 from ozf_ae_lines_all
3414 where ae_header_id = p_ae_header_id;
3415
3416 --Bug7391920 - Added cursor c_period_closed
3417 CURSOR c_period_closed ( l_acct_date DATE ) IS
3418 SELECT closing_status
3419 FROM gl_period_statuses a
3420 , ozf_sys_parameters b
3421 WHERE application_id = 222
3422 AND a.set_of_books_id = b.set_of_books_id
3423 AND l_acct_date BETWEEN start_date AND end_date
3424 AND NVL(adjustment_period_flag,'N') = 'N';
3425
3426
3427 --Bug7391920 - Added cursor c_open_period
3428 CURSOR c_open_period IS
3429 SELECT MIN(start_date)
3430 FROM gl_period_statuses a
3431 , ozf_sys_parameters b
3432 WHERE a.application_id = 222
3433 AND a.set_of_books_id = b.set_of_books_id
3434 AND nvl(a.adjustment_period_flag,'N') = 'N'
3435 AND a.closing_status IN ( 'O','F');
3436
3437 BEGIN
3438 -- Standard begin of API savepoint
3439 SAVEPOINT Revert_GL_Entry;
3440
3441 -- Standard call to check for call compatibility.
3442 IF NOT FND_API.Compatible_API_Call (
3443 l_api_version,
3444 p_api_version,
3445 l_api_name,
3446 G_PKG_NAME)
3447 THEN
3448 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3449 END IF;
3450
3451 IF OZF_DEBUG_LOW_ON THEN
3452 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
3453 FND_MESSAGE.Set_Token('TEXT',l_full_name||': Start');
3454 FND_MSG_PUB.Add;
3455 END IF;
3456
3457 --Initialize message list if p_init_msg_list is TRUE.
3458 IF FND_API.To_Boolean (p_init_msg_list) THEN
3459 FND_MSG_PUB.initialize;
3460 END IF;
3461
3462 -- Initialize API return status to sucess
3463 x_return_status := FND_API.G_RET_STS_SUCCESS;
3464
3465 IF OZF_DEBUG_LOW_ON THEN
3466 OZF_Utility_PVT.debug_message('--------- Revert_GL_Entry ----------');
3467 OZF_Utility_PVT.debug_message('claim_id : '||p_claim_id);
3468 END IF;
3469
3470 OPEN get_accounting_event_csr( p_claim_id );
3471 FETCH get_accounting_event_csr INTO l_accounting_event_id
3472 , l_acctng_event_rec.event_number
3473 , l_acctng_event_rec.event_status_code
3474 , l_acctng_event_rec.event_type_code
3475 , l_acctng_event_rec.source_id
3476 , l_acctng_event_rec.source_table
3477 , l_acctng_event_rec.org_id
3478 , l_acctng_event_rec.accounting_date
3479 , l_ae_object_version_num;
3480 CLOSE get_accounting_event_csr;
3481
3482 IF l_accounting_event_id IS NULL OR l_accounting_event_id = FND_API.G_MISS_NUM THEN
3483 IF OZF_DEBUG_LOW_ON THEN
3484 OZF_Utility_PVT.debug_message('There is no accounting event for the source. Return to the caller.');
3485 END IF;
3486
3487 RETURN;
3488 END IF;
3489
3490 IF OZF_DEBUG_LOW_ON THEN
3491 OZF_Utility_PVT.debug_message('Found matching event id : '||l_accounting_event_id);
3492 END IF;
3493
3494 OPEN get_ae_header_csr( l_accounting_event_id );
3495 FETCH get_ae_header_csr INTO l_ae_header_id
3496 , l_ae_header_rec.ae_category
3497 , l_ae_header_rec.cross_currency_flag
3498 , l_ae_header_rec.description
3499 , l_ae_header_rec.gl_reversal_flag
3500 , l_ae_header_rec.period_name
3501 , l_ae_header_rec.set_of_books_id
3502 , l_gl_transfer_yn
3503 , l_ae_header_rec.org_id
3504 , l_aeh_object_version_num;
3505 CLOSE get_ae_header_csr;
3506
3507 IF OZF_DEBUG_LOW_ON THEN
3508 OZF_Utility_PVT.debug_message('Found matching header id : '||l_ae_header_id);
3509 END IF;
3510
3511 IF l_gl_transfer_yn = 'Y' THEN
3512 -- Create reverse entries in the SLA
3513 IF OZF_DEBUG_LOW_ON THEN
3514 OZF_Utility_PVT.debug_message('Transferred to GL, create reverse entries');
3515 END IF;
3516
3517 -- First create accounting event for the reversal event
3518 /*OZF_ACCTNG_EVENTS_PVT.Create_Acctng_Events(
3519 P_Api_Version_Number => 1.0,
3520 P_Init_Msg_List => FND_API.G_FALSE,
3521 P_Commit => FND_API.G_FALSE,
3522 P_Validation_Level => FND_API.G_VALID_LEVEL_FULL,
3523 x_return_status => x_return_status,
3524 x_msg_count => x_msg_count,
3525 x_msg_data => x_msg_data,
3526 P_Acctng_Event_Rec => l_acctng_event_rec,
3527 X_Accounting_Event_Id => l_new_event_id);
3528
3529 IF x_return_status = FND_API.g_ret_sts_error THEN
3530 RAISE FND_API.G_EXC_ERROR;
3531 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
3532 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3533 END IF;
3534
3535
3536 IF OZF_DEBUG_LOW_ON THEN
3537 OZF_Utility_PVT.debug_message('Created new accounting event');
3538 END IF;
3539
3540 -- Create a new header for reversal entries
3541 l_ae_header_rec.gl_transfer_flag := 'N';
3542 l_ae_header_rec.gl_transfer_run_id := -1;
3543 l_ae_header_rec.accounting_event_id := l_new_event_id;
3544 l_ae_header_rec.accounting_date := l_acctng_event_rec.accounting_date;
3545
3546 --Bug7391920 - Added cursor FETCH c_period_closed
3547 OPEN c_period_closed(l_acctng_event_rec.accounting_date);
3548 FETCH c_period_closed INTO l_period_closed;
3549 CLOSE c_period_closed;
3550
3551 --Bug7391920 - Added IF block
3552 IF l_period_closed <> 'O' THEN
3553 OPEN c_open_period;
3554 FETCH c_open_period INTO l_ae_header_rec.accounting_date;
3555 CLOSE c_open_period;
3556 END IF;
3557
3558 /*OZF_ae_header_PVT.Create_ae_header(
3559 P_Api_Version_Number => 1.0,
3560 P_Init_Msg_List => FND_API.G_FALSE,
3561 P_Commit => FND_API.G_FALSE,
3562 P_Validation_Level => FND_API.G_VALID_LEVEL_FULL,
3563 x_return_status => x_return_status,
3564 x_msg_count => x_msg_count,
3565 x_msg_data => x_msg_data,
3566 P_AE_HEADER_Rec => l_ae_header_rec,
3567 X_AE_HEADER_ID => l_new_header_id);
3568
3569 IF x_return_status = FND_API.g_ret_sts_error THEN
3570 RAISE FND_API.G_EXC_ERROR;
3571 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
3572 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3573 END IF;
3574
3575
3576 IF OZF_DEBUG_LOW_ON THEN
3577 OZF_Utility_PVT.debug_message('Created new accounting header');
3578 END IF;
3579
3580 -- Create new lines for the reverse entries, by switching credit/debit amounts
3581 k := 1;
3582 l_ae_line_tbl := null;
3583 l_ae_line_tbl := OZF_ae_line_PVT.ae_line_tbl_type();
3584
3585 OPEN get_ae_lines_dtl_csr( l_ae_header_id );
3586 LOOP
3587 FETCH get_ae_lines_dtl_csr INTO l_accounted_cr
3588 , l_accounted_dr
3589 , l_entered_cr
3590 , l_entered_dr
3591 , l_ae_line_number
3592 , l_ae_line_type_code
3593 , l_ccid
3594 , l_currency_code
3595 , l_description
3596 , l_source_id
3597 , l_source_table
3598 , l_org_id
3599 , l_reference2 --//Bug 7633112
3600 , l_reference3;
3601 EXIT WHEN get_ae_lines_dtl_csr%NOTFOUND;
3602
3603 l_ae_line_tbl.extend;
3604 l_ae_line_tbl(k) := null;
3605 l_ae_line_tbl(k).ae_line_number := l_ae_line_number;
3606 l_ae_line_tbl(k).ae_line_type_code := l_ae_line_type_code;
3607 l_ae_line_tbl(k).code_combination_id := l_ccid;
3608 l_ae_line_tbl(k).currency_code := l_currency_code;
3609 l_ae_line_tbl(k).description := l_description;
3610 l_ae_line_tbl(k).source_id := l_source_id;
3611 l_ae_line_tbl(k).source_table := l_source_table;
3612 l_ae_line_tbl(k).org_id := l_org_id;
3613 l_ae_line_tbl(k).ae_header_id := l_new_header_id;
3614 --//Bug 7633112
3615 l_ae_line_tbl(k).reference2 := l_reference2;
3616 l_ae_line_tbl(k).reference3 := l_reference3;
3617
3618 -- Reverse debit/credit amounts
3619 IF l_accounted_cr IS NOT NULL THEN
3620 l_ae_line_tbl(k).accounted_cr := NULL;
3621 l_ae_line_tbl(k).accounted_dr := l_accounted_cr;
3622 l_ae_line_tbl(k).entered_cr := NULL;
3623 l_ae_line_tbl(k).entered_dr := l_entered_cr;
3624 ELSIF l_accounted_dr IS NOT NULL THEN
3625 l_ae_line_tbl(k).accounted_cr := l_accounted_dr;
3626 l_ae_line_tbl(k).accounted_dr := NULL;
3627 l_ae_line_tbl(k).entered_cr := l_entered_dr;
3628 l_ae_line_tbl(k).entered_dr := NULL;
3629 END IF;
3630
3631 k := k + 1;
3632 END LOOP;
3633 CLOSE get_ae_lines_dtl_csr;
3634
3635 /* OZF_ae_line_PVT.Create_ae_line(
3636 P_Api_Version_Number => 1.0,
3637 P_Init_Msg_List => FND_API.G_FALSE,
3638 P_Commit => FND_API.G_FALSE,
3639 P_Validation_Level => FND_API.G_VALID_LEVEL_FULL,
3640 x_return_status => x_return_status,
3641 x_msg_count => x_msg_count,
3642 x_msg_data => x_msg_data,
3643 P_AE_LINE_Tbl => l_ae_line_tbl,
3644 X_AE_LINE_ID => l_new_line_id_tbl);
3645
3646 IF x_return_status = FND_API.g_ret_sts_error THEN
3647 RAISE FND_API.G_EXC_ERROR;
3648 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
3649 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3650 END IF;
3651
3652
3653 IF OZF_DEBUG_LOW_ON THEN
3654 OZF_Utility_PVT.debug_message('Created new accounting lines with reversed amounts');
3655 END IF;
3656
3657 -- Finally, mark original header as reversed
3658 UPDATE ozf_ae_headers_all
3659 SET gl_reversal_flag = 'Y'
3660 WHERE ae_header_id = l_ae_header_id;
3661 ELSE
3662 -- Delete SLA entries not transferred to GL
3663 IF OZF_DEBUG_LOW_ON THEN
3664 OZF_Utility_PVT.debug_message('Not transferred to GL, delete SLA entries');
3665 END IF;
3666 --//Bugfix :7297267
3667 OPEN get_ae_lines_csr( l_ae_header_id );
3668 LOOP
3669 FETCH get_ae_lines_csr INTO l_ae_line_id, l_ael_object_version_num;
3670 EXIT WHEN get_ae_lines_csr%notfound;
3671
3672 IF OZF_DEBUG_HIGH_ON THEN
3673 OZF_UTILITY_PVT.debug_message('Delting SLA line, line_id:' || l_ae_line_id);
3674 END IF;
3675
3676 -- Delete SLA lines not transferred
3677 /*OZF_AE_LINE_PVT.Delete_Ae_Line(
3678 p_api_version_number => 1.0,
3679 p_init_msg_list => FND_API.G_FALSE,
3680 p_commit => FND_API.G_FALSE,
3681 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3682 x_return_status => x_return_status,
3683 x_msg_count => x_msg_count,
3684 x_msg_data => x_msg_data,
3685 p_ae_line_id => l_ae_line_id,
3686 p_object_version_number => l_ael_object_version_num);
3687
3688 IF x_return_status = FND_API.g_ret_sts_error THEN
3689 RAISE FND_API.G_EXC_ERROR;
3690 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
3691 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3692 END IF;
3693
3694 END LOOP;
3695 CLOSE get_ae_lines_csr;
3696
3697 IF OZF_DEBUG_LOW_ON THEN
3698 OZF_Utility_PVT.debug_message('Deleted lines');
3699 END IF;
3700
3701 -- Delete SLA header not transferred
3702 /*OZF_AE_HEADER_PVT.Delete_Ae_Header(
3703 p_api_version_number => 1.0,
3704 p_init_msg_list => FND_API.G_FALSE,
3705 p_commit => FND_API.G_FALSE,
3706 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3707 x_return_status => x_return_status,
3708 x_msg_count => x_msg_count,
3709 x_msg_data => x_msg_data,
3710 p_ae_header_id => l_ae_header_id,
3711 p_object_version_number => l_aeh_object_version_num);
3712
3713 IF x_return_status = FND_API.g_ret_sts_error THEN
3714 RAISE FND_API.G_EXC_ERROR;
3715 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
3716 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3717 END IF;
3718
3719
3720 IF OZF_DEBUG_LOW_ON THEN
3721 OZF_Utility_PVT.debug_message('Deleted header');
3722 END IF;
3723
3724 -- Delete SLA accounting event not transferred
3725 /*OZF_ACCTNG_EVENTS_PVT.Delete_Acctng_Events(
3726 p_api_version_number => 1.0,
3727 p_init_msg_list => FND_API.G_FALSE,
3728 p_commit => FND_API.G_FALSE,
3729 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3730 x_return_status => x_return_status,
3731 x_msg_count => x_msg_count,
3732 x_msg_data => x_msg_data,
3733 p_accounting_event_id => l_accounting_event_id,
3734 p_object_version_number => l_ae_object_version_num);
3735
3736 IF x_return_status = FND_API.g_ret_sts_error THEN
3737 RAISE FND_API.G_EXC_ERROR;
3738 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
3739 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3740 END IF;
3741
3742
3743 IF OZF_DEBUG_LOW_ON THEN
3744 OZF_Utility_PVT.debug_message('Deleted header');
3745 OZF_Utility_PVT.debug_message('Accounting entries successfully reverted.');
3746 END IF;
3747
3748 END IF;
3749
3750 --Standard check of commit
3751 IF FND_API.To_Boolean ( p_commit ) THEN
3752 COMMIT WORK;
3753 END IF;
3754 -- Debug Message
3755 IF OZF_DEBUG_LOW_ON THEN
3756 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
3757 FND_MESSAGE.Set_Token('TEXT',l_full_name||': End');
3758 FND_MSG_PUB.Add;
3759 FND_MSG_PUB.Add;
3760 END IF;
3761 --Standard call to get message count and if count=1, get the message
3762 FND_MSG_PUB.Count_And_Get (
3763 p_encoded => FND_API.G_FALSE,
3764 p_count => x_msg_count,
3765 p_data => x_msg_data
3766 );
3767 EXCEPTION
3768 WHEN FND_API.G_EXC_ERROR THEN
3769 ROLLBACK TO Revert_GL_Entry;
3770 x_return_status := FND_API.G_RET_STS_ERROR;
3771 -- Standard call to get message count and if count=1, get the message
3772 FND_MSG_PUB.Count_And_Get (
3773 p_encoded => FND_API.G_FALSE,
3774 p_count => x_msg_count,
3775 p_data => x_msg_data
3776 );
3777 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3778 ROLLBACK TO Revert_GL_Entry;
3779 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3780 -- Standard call to get message count and if count=1, get the message
3781 FND_MSG_PUB.Count_And_Get (
3782 p_encoded => FND_API.G_FALSE,
3783 p_count => x_msg_count,
3784 p_data => x_msg_data
3785 );
3786 WHEN OTHERS THEN
3787 ROLLBACK TO Revert_GL_Entry;
3788 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3789 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3790 THEN
3791 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
3792 END IF;
3793 -- Standard call to get message count and if count=1, get the message
3794 FND_MSG_PUB.Count_And_Get (
3795 p_encoded => FND_API.G_FALSE,
3796 p_count => x_msg_count,
3797 p_data => x_msg_data
3798 );
3799
3800 END Revert_GL_Entry;
3801 */
3802
3803 ---------------------------------------------------------------------
3804 -- PROCEDURE
3805 -- Post_Accrual_To_GL
3806 --
3807 -- PURPOSE
3808 -- For budget adjustment/utilization, the API will be called.
3809 --
3810 -- PARAMETERS
3811 -- p_utilization_id Funds utilization_id
3812 -- p_event_type_code SLA Event type code
3813 -- p_dr_code_combination_id Debit code combination id
3814 -- p_cr_code_combination_id Credit code combination id
3815 --
3816 -- NOTES
3817 -- 8-Mar-10 BKUNJAN ER#9382547 ChRM-SLA Uptake - Removed the OUT parameter
3818 -- x_event_id and IN Parameter p_adjustment_type.
3819 -- renamed p_utilization_type to p_event_type_code
3820 -- removed p_skip_acct_gen_flag.
3821 ---------------------------------------------------------------------
3822 PROCEDURE Post_Accrual_To_GL (
3823 p_api_version IN NUMBER
3824 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
3825 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
3826 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
3827
3828 ,x_return_status OUT NOCOPY VARCHAR2
3829 ,x_msg_data OUT NOCOPY VARCHAR2
3830 ,x_msg_count OUT NOCOPY NUMBER
3831
3832 ,p_utilization_id IN NUMBER
3833 ,p_event_type_code IN VARCHAR2
3834 ,p_dr_code_combination_id IN NUMBER := NULL
3835 ,p_cr_code_combination_id IN NUMBER := NULL
3836 )
3837 IS
3838 l_api_name CONSTANT VARCHAR2(30) := 'Post_Accrual_To_GL';
3839 l_api_version CONSTANT NUMBER := 1.0;
3840 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
3841 l_skip_acct_gen_flag VARCHAR2(1);
3842
3843 l_gl_rec OZF_GL_INTERFACE_PVT.gl_interface_rec_type;
3844
3845 BEGIN
3846 -- Standard begin of API savepoint
3847 SAVEPOINT Post_Accrual_To_GL;
3848 -- Standard call to check for call compatibility.
3849 IF NOT FND_API.Compatible_API_Call (
3850 l_api_version,
3851 p_api_version,
3852 l_api_name,
3853 G_PKG_NAME)
3854 THEN
3855 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3856 END IF;
3857 -- Debug Message
3858 IF OZF_DEBUG_LOW_ON THEN
3859 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
3860 FND_MESSAGE.Set_Token('TEXT',l_full_name||': Start');
3861 FND_MSG_PUB.Add;
3862 END IF;
3863 --Initialize message list if p_init_msg_list is TRUE.
3864 IF FND_API.To_Boolean (p_init_msg_list) THEN
3865 FND_MSG_PUB.initialize;
3866 END IF;
3867 -- Initialize API return status to sucess
3868 x_return_status := FND_API.G_RET_STS_SUCCESS;
3869
3870 IF OZF_DEBUG_LOW_ON THEN
3871 OZF_Utility_PVT.debug_message('--------- Post_Accrual_To_GL ----------');
3872 OZF_Utility_PVT.debug_message('utilization_id : '||p_utilization_id);
3873 OZF_Utility_PVT.debug_message('utilization_type : '|| p_event_type_code);
3874 OZF_Utility_PVT.debug_message('debit_cc_id : '||p_dr_code_combination_id);
3875 OZF_Utility_PVT.debug_message('credit_cc_id : '||p_cr_code_combination_id);
3876 END IF;
3877
3878 -- Get the Skip Account Generator Flaf
3879 --ER 13069257
3880 l_skip_acct_gen_flag := NVL(ozf_fund_utilized_pub.g_skip_acct_gen_flag, 'F');
3881
3882 -- construct gl interface record
3883 l_gl_rec.event_type_code := p_event_type_code;
3884 l_gl_rec.event_status_code := 'ACCOUNTED';
3885 l_gl_rec.source_id := p_utilization_id;
3886 l_gl_rec.source_table := 'OZF_FUNDS_UTILIZED_ALL_B';
3887 l_gl_rec.dr_code_combination_id := p_dr_code_combination_id;
3888 l_gl_rec.cr_code_combination_id := p_cr_code_combination_id;
3889 --ER 13069257
3890 l_gl_rec.SKIP_ACCOUNT_GEN_FLAG := l_skip_acct_gen_flag;
3891
3892
3893 OZF_GL_INTERFACE_PVT.Create_Gl_Entry (
3894 p_api_version => l_api_version
3895 ,p_init_msg_list => FND_API.g_false
3896 ,p_commit => FND_API.g_false
3897 ,p_validation_level => FND_API.g_valid_level_full
3898 ,x_return_status => x_return_status
3899 ,x_msg_data => x_msg_data
3900 ,x_msg_count => x_msg_count
3901 ,p_gl_rec => l_gl_rec
3902 );
3903
3904 IF x_return_status = FND_API.g_ret_sts_error THEN
3905 RAISE FND_API.G_EXC_ERROR;
3906 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
3907 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3908 END IF;
3909
3910 --Standard check of commit
3911 IF FND_API.To_Boolean ( p_commit ) THEN
3912 COMMIT WORK;
3913 END IF;
3914 -- Debug Message
3915 IF OZF_DEBUG_LOW_ON THEN
3916 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
3917 FND_MESSAGE.Set_Token('TEXT',l_full_name||': End');
3918 FND_MSG_PUB.Add;
3919 END IF;
3920 --Standard call to get message count and if count=1, get the message
3921 FND_MSG_PUB.Count_And_Get (
3922 p_encoded => FND_API.G_FALSE,
3923 p_count => x_msg_count,
3924 p_data => x_msg_data
3925 );
3926 EXCEPTION
3927 WHEN FND_API.G_EXC_ERROR THEN
3928 ROLLBACK TO Post_Accrual_To_GL;
3929 x_return_status := FND_API.G_RET_STS_ERROR;
3930 -- Standard call to get message count and if count=1, get the message
3931 FND_MSG_PUB.Count_And_Get (
3932 p_encoded => FND_API.G_FALSE,
3933 p_count => x_msg_count,
3934 p_data => x_msg_data
3935 );
3936 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3937 ROLLBACK TO Post_Accrual_To_GL;
3938 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3939 -- Standard call to get message count and if count=1, get the message
3940 FND_MSG_PUB.Count_And_Get (
3941 p_encoded => FND_API.G_FALSE,
3942 p_count => x_msg_count,
3943 p_data => x_msg_data
3944 );
3945 WHEN OTHERS THEN
3946 ROLLBACK TO Post_Accrual_To_GL;
3947 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3948 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3949 THEN
3950 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
3951 END IF;
3952 -- Standard call to get message count and if count=1, get the message
3953 FND_MSG_PUB.Count_And_Get (
3954 p_encoded => FND_API.G_FALSE,
3955 p_count => x_msg_count,
3956 p_data => x_msg_data
3957 );
3958
3959 END Post_Accrual_To_GL;
3960
3961 ---------------------------------------------------------------------
3962 -- PROCEDURE
3963 -- Post_Claim_To_GL
3964
3965 --
3966 -- PURPOSE
3967 -- For Claim settlement to be posted to GL, use this API.
3968 --
3969 -- PARAMETERS
3970 -- p_claim_id Claim_id
3971 -- p_claim_class 'CLAIM''CHARGE''DEDUCTION''OVERPAYMENT'
3972 -- p_settlement_method 'CREDIT_MEMO''DEBIT_MEMO''CHECK''AP_DEBIT'
3973 -- x_clear_code_combination_id Code combination id of AR or AP clearing account
3974 --
3975 -- NOTES
3976 -- 05/03/2010 kpatro Updated for ER#9382547 ChRM-SLA Uptake
3977 -- Updated the Event Type Code,
3978 -- Removed the Event_ID,x_clear_code_combination_id
3979 -- OUT parameter and p_claim_class IN parameter
3980 -- Removed the Adjustment Type checks
3981 ---------------------------------------------------------------------
3982 PROCEDURE Post_Claim_To_GL (
3983 p_api_version IN NUMBER
3984 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
3985 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
3986 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
3987
3988 ,x_return_status OUT NOCOPY VARCHAR2
3989 ,x_msg_data OUT NOCOPY VARCHAR2
3990 ,x_msg_count OUT NOCOPY NUMBER
3991
3992 ,p_claim_id IN NUMBER
3993 ,p_settlement_method IN VARCHAR2
3994 )
3995 IS
3996 l_api_name CONSTANT VARCHAR2(30) := 'Post_Claim_To_GL';
3997 l_api_version CONSTANT NUMBER := 1.0;
3998 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
3999
4000 CURSOR taxfor_csr (p_claim_id IN NUMBER)IS
4001 SELECT tax_for
4002 FROM ozf_claim_sttlmnt_methods_all csm,ozf_claims_all c
4003 WHERE csm.settlement_method = c.payment_method
4004 AND c.claim_id =p_claim_id
4005 AND csm.org_id = c.org_id;
4006
4007
4008 l_gl_rec OZF_GL_INTERFACE_PVT.gl_interface_rec_type;
4009 l_taxfor VARCHAR2(2);
4010
4011 BEGIN
4012 -- Standard begin of API savepoint
4013 SAVEPOINT Post_Claim_To_GL;
4014 -- Standard call to check for call compatibility.
4015 IF NOT FND_API.Compatible_API_Call (
4016 l_api_version,
4017 p_api_version,
4018 l_api_name,
4019 G_PKG_NAME)
4020 THEN
4021 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4022 END IF;
4023 -- Debug Message
4024 IF OZF_DEBUG_LOW_ON THEN
4025 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
4026 FND_MESSAGE.Set_Token('TEXT',l_full_name||': Start');
4027 FND_MSG_PUB.Add;
4028 END IF;
4029 --Initialize message list if p_init_msg_list is TRUE.
4030 IF FND_API.To_Boolean (p_init_msg_list) THEN
4031 FND_MSG_PUB.initialize;
4032 END IF;
4033 -- Initialize API return status to sucess
4034 x_return_status := FND_API.G_RET_STS_SUCCESS;
4035
4036 IF OZF_DEBUG_LOW_ON THEN
4037 OZF_Utility_PVT.debug_message('--------- Post_Claim_To_GL ----------');
4038 OZF_Utility_PVT.debug_message('claim_id : '||p_claim_id);
4039 OZF_Utility_PVT.debug_message('settlement method : '||p_settlement_method);
4040 END IF;
4041
4042 OPEN taxfor_csr(p_claim_id);
4043 FETCH taxfor_csr INTO l_taxfor;
4044 CLOSE taxfor_csr;
4045
4046 -- construct gl interface record
4047 -- R12.1 Enhancement : GL rec for Accounting only
4048 -- ER#9382547 Constructed the Event Type Codes of claims based on the settlement
4049 -- methods and pass the event type code to SLA
4050 IF p_settlement_method = 'CREDIT_MEMO' THEN
4051 l_gl_rec.event_type_code := 'SETTLE_BY_CREDIT_MEMO';
4052 ELSIF p_settlement_method = 'DEBIT_MEMO' THEN
4053 l_gl_rec.event_type_code := 'SETTLE_BY_DEBIT_MEMO';
4054 ELSIF p_settlement_method IN ('CHECK', 'WIRE', 'EFT','AP_DEFAULT') THEN
4055 l_gl_rec.event_type_code := 'SETTLE_BY_AP_INVOICE';
4056 ELSIF p_settlement_method = 'AP_DEBIT' THEN
4057 l_gl_rec.event_type_code := 'SETTLE_BY_AP_DEBIT';
4058 ELSIF p_settlement_method = 'ACCOUNTING_ONLY' THEN
4059 l_gl_rec.event_type_code := 'SETTLE_INTERNAL_SHIP_DEBIT';
4060 -- Fix for Bug 9536761
4061 ELSIF p_settlement_method = 'CONTRA_CHARGE' THEN
4062 l_gl_rec.event_type_code := 'SETTLE_BY_AR_AP_NETTING';
4063 ELSIF p_settlement_method = 'CLAIM_SETTLEMENT_REVERSAL' THEN
4064 l_gl_rec.event_type_code := 'CLAIM_SETTLEMENT_REVERSAL';
4065 ELSE
4066 IF(l_taxfor = 'AR') THEN
4067 l_gl_rec.event_type_code := 'SETTLE_BY_AR_CUSTOM';
4068 ELSIF (l_taxfor = 'AP') THEN
4069 l_gl_rec.event_type_code := 'SETTLE_BY_AP_CUSTOM';
4070 END IF;
4071 END IF;
4072
4073 l_gl_rec.event_status_code := 'ACCOUNTED';
4074 l_gl_rec.source_id := p_claim_id;
4075 l_gl_rec.source_table := 'OZF_CLAIMS_ALL';
4076 -- Adjutment Type Logic is now seeded with JLTs via switch
4077 /*IF p_claim_class IN ('CLAIM', 'DEDUCTION') THEN
4078 l_gl_rec.adjustment_type := 'P';
4079 ELSIF p_claim_class IN ('CHARGE', 'OVERPAYMENT') THEN
4080 l_gl_rec.adjustment_type := 'N';
4081 END IF;
4082 */
4083 OZF_GL_INTERFACE_PVT.Create_Gl_Entry (
4084 p_api_version => l_api_version
4085 ,p_init_msg_list => FND_API.g_false
4086 ,p_commit => FND_API.g_false
4087 ,p_validation_level => FND_API.g_valid_level_full
4088 ,x_return_status => x_return_status
4089 ,x_msg_data => x_msg_data
4090 ,x_msg_count => x_msg_count
4091 ,p_gl_rec => l_gl_rec
4092 );
4093
4094 IF x_return_status = FND_API.g_ret_sts_error THEN
4095 RAISE FND_API.G_EXC_ERROR;
4096 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
4097 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4098 END IF;
4099
4100 --Standard check of commit
4101 IF FND_API.To_Boolean ( p_commit ) THEN
4102 COMMIT WORK;
4103 END IF;
4104 -- Debug Message
4105 IF OZF_DEBUG_LOW_ON THEN
4106 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
4107 FND_MESSAGE.Set_Token('TEXT',l_full_name||': End');
4108 FND_MSG_PUB.Add;
4109 END IF;
4110 --Standard call to get message count and if count=1, get the message
4111 FND_MSG_PUB.Count_And_Get (
4112 p_encoded => FND_API.G_FALSE,
4113 p_count => x_msg_count,
4114 p_data => x_msg_data
4115 );
4116 EXCEPTION
4117 WHEN FND_API.G_EXC_ERROR THEN
4118 ROLLBACK TO Post_Claim_To_GL;
4119 x_return_status := FND_API.G_RET_STS_ERROR;
4120 -- Standard call to get message count and if count=1, get the message
4121 FND_MSG_PUB.Count_And_Get (
4122 p_encoded => FND_API.G_FALSE,
4123 p_count => x_msg_count,
4124 p_data => x_msg_data
4125 );
4126 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4127 ROLLBACK TO Post_Claim_To_GL;
4128 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4129 -- Standard call to get message count and if count=1, get the message
4130 FND_MSG_PUB.Count_And_Get (
4131 p_encoded => FND_API.G_FALSE,
4132 p_count => x_msg_count,
4133 p_data => x_msg_data
4134 );
4135 WHEN OTHERS THEN
4136 ROLLBACK TO Post_Claim_To_GL;
4137 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4138 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4139 THEN
4140 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
4141 END IF;
4142 -- Standard call to get message count and if count=1, get the message
4143 FND_MSG_PUB.Count_And_Get (
4144 p_encoded => FND_API.G_FALSE,
4145 p_count => x_msg_count,
4146 p_data => x_msg_data
4147 );
4148
4149 END Post_Claim_To_GL;
4150
4151 ---------------------------------------------------------------------
4152 -- PROCEDURE
4153 -- Defer_Claim_GL_Posting (Function)
4154 --
4155 -- PURPOSE
4156 -- Function to be used by Claims to test if 'OZF: Claim
4157 -- Settlement Workflow' should be called to defer GL posting
4158 --
4159 -- PARAMETERS
4160 -- p_claim_id : claim_id for which the check is done.
4161 --
4162 -- NOTES
4163 ---------------------------------------------------------------------
4164 /*FUNCTION Defer_Claim_GL_Posting (
4165 p_claim_id IN NUMBER
4166 ) RETURN BOOLEAN
4167 IS
4168 l_return BOOLEAN := FALSE;
4169
4170 BEGIN
4171
4172 RETURN l_return;
4173
4174 END Defer_Claim_GL_Posting;
4175 */
4176 -- End R12 Enhancements
4177 END OZF_GL_INTERFACE_PVT;
4178