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