[Home] [Help]
PACKAGE BODY: APPS.OZF_GL_TRANSFER_PVT
Source
1 PACKAGE BODY OZF_GL_TRANSFER_PVT AS
2 /* $Header: ozfvgtrb.pls 120.7.12020000.4 2013/02/01 17:00:08 bkunjan ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OZF_GL_TRANSFER_PVT';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'ozfgltrb.pls';
6
7 ---------------------------------------------------------------------
8 -- PROCEDURE
9 -- UpdateGLDate
10 --
11 -- PURPOSE
12 -- It will update the GL date of the TM source and and call the
13 -- XLA Update API to update the Event Date to the first date of
14 -- earliest open period.
15 --
16 -- NOTES
17 --
18 -- HISTORY
19 -- 07-Dec-2012 KPATRO Created Bug#15949068 :
20 -- sweep fix for entries in a closed gl period
21 -- 29-Jan-2013 BKUNJAN Changes after code review
22 ---------------------------------------------------------------------
23
24 PROCEDURE Update_GL_Date ( p_api_version IN NUMBER
25 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
26 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
27 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
28 ,p_ledger_id IN NUMBER
29 ,x_return_status OUT NOCOPY VARCHAR2
30 )
31 IS
32
33 CURSOR c_xla_event_info (cv_open_period_gl_date IN DATE )IS
34 SELECT evt.event_id event_id,
35 evt.entity_id entity_id,
36 evt.event_type_code event_type_code,
37 te.legal_entity_id legal_entity_id,
38 te.source_id_int_1 source_id_int_1,
39 te.security_id_int_1 security_id_int_1,
40 te.ledger_id ledger_id,
41 te.entity_code entity_code
42 FROM xla_events evt,
43 xla_transaction_entities_upg te
44 WHERE evt.application_id = 682
45 AND evt.event_status_code ='U'
46 AND evt.process_status_code IN ('U','I','D')
47 AND evt.entity_id = te.entity_id
48 AND te.ledger_id = p_ledger_id
49 AND evt.event_date < cv_open_period_gl_date;
50
51 CURSOR c_get_open_period_gl_date IS
52 SELECT MIN(start_date)
53 FROM gl_period_statuses a
54 , ozf_sys_parameters_all b
55 WHERE a.application_id = 101
56 AND a.set_of_books_id = b.set_of_books_id
57 AND b.set_of_books_id = p_ledger_id
58 AND nvl(a.adjustment_period_flag,'N') = 'N'
59 AND a.closing_status IN ( 'O','F');
60
61 CURSOR c_get_year_id(cv_event_date IN DATE) IS
62 SELECT ent_year_id
63 FROM OZF_TIME_ENT_YEAR
64 WHERE cv_event_date BETWEEN start_date AND end_date;
65
66 CURSOR c_claim_objverno(cv_claim_id IN NUMBER) IS
67 SELECT object_version_number
68 FROM ozf_claims_all
69 WHERE claim_id = cv_claim_id;
70
71
72 l_open_period_gl_date DATE := NULL;
73 l_event_date DATE := NULL;
74 l_event_source_info XLA_EVENTS_PUB_PKG.t_event_source_info;
75 l_security XLA_EVENTS_PUB_PKG.t_security;
76
77 l_api_name CONSTANT VARCHAR2(30) := 'Update_GL_Date';
78 l_api_version CONSTANT NUMBER := 1.0;
79 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
80
81 l_claim_rec OZF_CLAIM_PVT.claim_rec_type;
82 l_return_status VARCHAR2(30);
83 l_msg_data VARCHAR2(2000);
84 l_msg_count NUMBER;
85 l_year_id NUMBER;
86 l_obj_version_number NUMBER;
87
88 BEGIN
89 -- Standard Start of API savepoint
90 SAVEPOINT Update_GL_Date;
91 -- Standard call to check for call compatibility.
92 IF NOT FND_API.Compatible_API_Call ( l_api_version,
93 p_api_version,
94 l_api_name,
95 G_PKG_NAME)
96 THEN
97 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
98 END IF;
99 -- Initialize message list if p_init_msg_list is set to TRUE.
100 IF FND_API.to_Boolean( p_init_msg_list )
101 THEN
102 FND_MSG_PUB.initialize;
103 END IF;
104
105 -- Initialize API return status to SUCCESS
106 x_return_status := FND_API.G_RET_STS_SUCCESS;
107 --
108 -- API body
109 --
110 OPEN c_get_open_period_gl_date;
111 FETCH c_get_open_period_gl_date INTO l_open_period_gl_date;
112 CLOSE c_get_open_period_gl_date;
113
114 FOR c_xla_event_data IN c_xla_event_info (l_open_period_gl_date) LOOP
115 FND_FILE.PUT_LINE(FND_FILE.LOG, 'c_xla_event_data.event_id: ' ||c_xla_event_data.event_id);
116 FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_open_period_gl_date: '|| l_open_period_gl_date);
117 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Utilization_id/Claim_id ' || l_event_source_info.source_id_int_1);
118
119 l_event_date := l_open_period_gl_date;
120
121 l_event_source_info.legal_entity_id := c_xla_event_data.legal_entity_id;
122 l_event_source_info.source_id_int_1 := c_xla_event_data.source_id_int_1;
123 l_security.security_id_int_1 := c_xla_event_data.security_id_int_1;
124 l_event_source_info.ledger_id := c_xla_event_data.ledger_id;
125 l_event_source_info.entity_type_code := c_xla_event_data.entity_code;
126
127 IF (l_event_source_info.entity_type_code = 'ACCRUAL') THEN
128 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Updating Accrual Record for Utilization_id:' || l_event_source_info.source_id_int_1);
129 OPEN c_get_year_id(l_event_date);
130 FETCH c_get_year_id INTO l_year_id;
131 CLOSE c_get_year_id;
132
133 UPDATE ozf_funds_utilized_all_b
134 SET gl_date = l_event_date,
135 year_id = l_year_id,
136 last_update_date = SYSDATE,
137 last_updated_by = NVL(FND_GLOBAL.user_id,-1)
138 WHERE utilization_id = l_event_source_info.source_id_int_1;
139
140 ELSIF (l_event_source_info.entity_type_code = 'CLAIM_SETTLEMENT') THEN
141 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Updating Claim Record for claim_id:' || l_event_source_info.source_id_int_1);
142
143 OPEN c_claim_objverno(l_event_source_info.source_id_int_1);
144 FETCH c_claim_objverno INTO l_obj_version_number;
145 CLOSE c_claim_objverno;
146
147 --//Populate the claim recordset
148 l_claim_rec.claim_id := l_event_source_info.source_id_int_1;
149 l_claim_rec.object_version_number := l_obj_version_number;
150 l_claim_rec.gl_date := l_event_date;
151
152 OZF_CLAIM_PVT.Update_Claim(
153 p_api_version => l_api_version,
154 p_init_msg_list => FND_API.G_FALSE,
155 p_commit => FND_API.G_FALSE,
156 P_Validation_Level => FND_API.G_VALID_LEVEL_FULL,
157 x_return_status => l_return_status,
158 x_msg_count => l_msg_count,
159 x_msg_data => l_msg_data,
160 p_claim => l_claim_rec,
161 p_event => 'UPDATE',
162 p_mode => OZF_claim_Utility_pvt.G_AUTO_MODE,
163 x_object_version_number => l_obj_version_number );
164
165 -- Check return status from the above procedure call
166 IF l_return_status = FND_API.G_RET_STS_ERROR then
167 RAISE FND_API.G_EXC_ERROR;
168 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
169 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
170 END IF;
171 END IF;
172
173 l_event_source_info.application_id := 682;
174
175 --Call XLA Update API to update the event Date
176 XLA_EVENTS_PUB_PKG.update_event
177 (p_event_source_info => l_event_source_info
178 ,p_event_id => c_xla_event_data.event_id
179 ,p_event_type_code => c_xla_event_data.event_type_code
180 ,p_event_date => l_event_date
181 ,p_valuation_method => ''
182 ,p_security_context => l_security
183 ,p_transaction_date => INV_LE_TIMEZONE_PUB.get_le_day_time_for_ou(TRUNC(l_event_date),l_security.security_id_int_1));
184
185
186 END LOOP;
187
188 EXCEPTION
189 WHEN FND_API.G_EXC_ERROR THEN
190 ROLLBACK TO Update_GL_Date;
191 x_return_status := FND_API.G_RET_STS_ERROR;
192 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Expected Error from UpdateGLDate: '||SQLERRM);
193
194 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
195 ROLLBACK TO Update_GL_Date;
196 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
197 FND_FILE.PUT_LINE(FND_FILE.LOG, 'UnExpected Error from UpdateGLDate:: '||SQLERRM);
198
199 WHEN OTHERS THEN
200 ROLLBACK TO Update_GL_Date;
201 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
202 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error from UpdateGLDate:: '||SQLERRM);
203
204 END Update_GL_Date;
205
206 ---------------------------------------------------------------------
207 -- PROCEDURE
208 -- CreateAccounting
209 --
210 -- PURPOSE
211 -- It will trigger the SLA Create Accounting Program
212 --
213 -- NOTES
214 --
215 -- HISTORY
216 -- 09-Mar-2010 KPATRO Created ER#9382547 ChRM-SLA Uptake
217 ---------------------------------------------------------------------
218
219
220 PROCEDURE CreateAccounting(
221 errbuf OUT NOCOPY VARCHAR2,
222 retcode OUT NOCOPY NUMBER,
223 p_org_id IN NUMBER,
224 p_source_application_id IN NUMBER,
225 p_application_id IN NUMBER,
226 p_dummy IN VARCHAR2,
227 p_ledger_id IN NUMBER,
228 P_PROCESS_CATEGORY_CODE IN VARCHAR2,
229 P_END_DATE IN VARCHAR2,
230 P_CREATE_ACCOUNTING_FLAG IN VARCHAR2,
231 P_DUMMY_PARAM_1 IN VARCHAR2,
232 P_ACCOUNTING_MODE IN VARCHAR2,
233 P_DUMMY_PARAM_2 IN VARCHAR2,
234 P_ERRORS_ONLY_FLAG IN VARCHAR2,
235 P_REPORT_STYLE IN VARCHAR2,
236 P_TRANSFER_TO_GL_FLAG IN VARCHAR2,
237 P_DUMMY_PARAM_3 IN VARCHAR2,
238 P_POST_IN_GL_FLAG IN VARCHAR2,
239 P_GL_BATCH_NAME IN VARCHAR2,
240 P_MIN_PRECISION IN NUMBER,
241 P_INCLUDE_ZERO_AMOUNT_LINES IN VARCHAR2,
242 P_REQUEST_ID IN NUMBER,
243 P_ENTITY_ID IN NUMBER,
244 P_SOURCE_APPLICATION_NAME IN VARCHAR2,
245 P_APPLICATION_NAME IN VARCHAR2,
246 P_LEDGER_NAME IN VARCHAR2,
247 P_PROCESS_CATEGORY_NAME IN VARCHAR2,
248 P_CREATE_ACCOUNTING IN VARCHAR2,
249 P_ACCOUNTING_MODE_NAME IN VARCHAR2,
250 P_ERRORS_ONLY IN VARCHAR2,
251 P_ACCOUNTING_REPORT_LEVEL IN VARCHAR2,
252 P_TRANSFER_TO_GL IN VARCHAR2,
253 P_POST_IN_GL IN VARCHAR2,
254 P_INCLUDE_ZERO_AMT_LINES IN VARCHAR2,
255 P_VALUATION_METHOD_CODE IN VARCHAR2,
256 P_SECURITY_INT_1 IN NUMBER,
257 P_SECURITY_INT_2 IN NUMBER,
258 P_SECURITY_INT_3 IN NUMBER,
259 P_SECURITY_CHAR_1 IN VARCHAR2,
260 P_SECURITY_CHAR_2 IN VARCHAR2,
261 P_SECURITY_CHAR_3 IN VARCHAR2,
262 P_CONC_REQUEST_ID IN NUMBER,
263 P_INCLUDE_USER_TRX_ID_FLAG IN VARCHAR2,
264 P_INCLUDE_USER_TRX_IDENTIFIERS IN VARCHAR2,
265 P_DebugFlag IN VARCHAR2,
266 P_USER_ID IN NUMBER
267 )
268 IS
269
270 l_reqid NUMBER;
271 --Fix for Bug 12426106
272 l_bool BOOLEAN;
273 l_iso_language VARCHAR2(30);
274 l_iso_territory VARCHAR2(30);
275
276 l_return_status VARCHAR2(10);
277
278
279
280 BEGIN
281
282 SAVEPOINT CREATE_Accounting;
283
284 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Start ='||G_PKG_NAME);
285 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '*--------------------------------Create Accounting Report -----------------------------*');
286 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Execution Starts On: ' || to_char(sysdate,'MM-DD-YYYY HH24:MI:SS'));
287 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '*---------------------------------------------------------------------------------------*');
288
289 FND_FILE.PUT_LINE(FND_FILE.LOG, 'In Parameters : ');
290
291 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_ledger_name : '||p_ledger_name );
292 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_end_date : '||p_end_date );
293 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_transfer_to_gl_flag : '||p_transfer_to_gl_flag );
294 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_gl_batch_name : '||p_gl_batch_name );
295 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_transfer_to_gl : '||p_transfer_to_gl );
296 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_post_in_gl : '||p_post_in_gl );
297 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_ledger_id : '||p_ledger_id );
298
299
300 Update_GL_Date(p_api_version => 1.0,
301 p_init_msg_list => FND_API.G_FALSE,
302 p_validation_level=> FND_API.G_VALID_LEVEL_FULL,
303 p_ledger_id => p_ledger_id,
304 x_return_status => l_return_status);
305
306 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Return Status from Update_GL_Date : '|| l_return_status );
307
308 IF l_return_status = FND_API.g_ret_sts_error THEN
309 RAISE FND_API.G_EXC_ERROR;
310 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
311 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
312 END IF;
313
314
315 --Fix for Bug 12426106
316 SELECT lower(iso_language),iso_territory
317 INTO l_iso_language,
318 l_iso_territory
319 FROM FND_LANGUAGES
320 WHERE language_code = USERENV('LANG');
321
322
323
324 l_bool := fnd_request.add_layout
325 (template_appl_name => 'XLA',
326 template_code => 'XLAACCPB01',
327 template_language => l_iso_language,
328 template_territory => l_iso_territory,
329 output_format => 'PDF');
330
331
332
333 l_reqid := fnd_request.submit_request('XLA',
334 'XLAACCPB',
335 '',
336 '',
337 FALSE,
338 p_source_application_id,
339 p_application_id,
340 p_dummy,
341 p_ledger_id,
342 p_process_category_code,
343 p_end_date,
344 p_create_accounting_flag,
345 p_dummy_param_1,
346 p_accounting_mode,
347 p_dummy_param_2,
348 p_errors_only_flag,
349 p_report_style,
350 p_transfer_to_gl_flag,
351 p_dummy_param_3,
352 p_post_in_gl_flag,
353 p_gl_batch_name,
354 p_min_precision,
355 p_include_zero_amount_lines,
356 p_request_id,
357 p_entity_id,
358 p_source_application_name,
359 p_application_name,
360 p_ledger_name,
361 p_process_category_name,
362 p_create_accounting,
363 p_accounting_mode_name,
364 p_errors_only,
365 p_accounting_report_level,
366 p_transfer_to_gl,
367 p_post_in_gl,
368 p_include_zero_amt_lines,
369 p_valuation_method_code,
370 p_security_int_1,
371 p_security_int_2,
372 p_security_int_3,
373 p_security_char_1,
374 p_security_char_2,
375 p_security_char_3,
376 p_conc_request_id,
377 p_include_user_trx_id_flag,
378 p_include_user_trx_identifiers,
379 p_debugflag,
380 p_user_id
381 );
382
383 IF l_reqid=0 THEN
384 FND_FILE.PUT_LINE(fnd_file.log,'Could not launch Create Accounting Request');
385 retcode:=1;
386 END IF;
387
388
389
390
391
392 IF retcode<>1 THEN
393 retcode:=0;
394 END IF;
395 COMMIT;
396
397 FND_FILE.PUT_LINE(FND_FILE.LOG, 'After calling SLA Accounting Program');
398 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '*---------------------------------------------------------------------------------------------*');
399 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Execution Status: Successful' );
400 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Execution Ends On: ' || to_char(sysdate,'MM-DD-YYYY HH24:MI:SS'));
401 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '*---------------------------------------------------------------------------------------------*');
402
403 EXCEPTION
404 WHEN FND_API.G_EXC_ERROR THEN
405 retcode:=2;
406 ROLLBACK TO CREATE_Accounting;
407 errbuf:= FND_API.G_RET_STS_ERROR;
408 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Expected Error: '||SQLERRM);
409 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '*---------------------------------------------------------------------------------------------*');
410 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Execution Status: Failure (Error:' ||SQLCODE||SQLERRM || ')');
411 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Execution Ends On: ' || to_char(sysdate,'MM-DD-YYYY HH24:MI:SS'));
412 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '*---------------------------------------------------------------------------------------------*');
413
414 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
415 retcode:=2;
416 ROLLBACK TO CREATE_Accounting;
417 errbuf:= FND_API.G_RET_STS_UNEXP_ERROR;
418 FND_FILE.PUT_LINE(FND_FILE.LOG, 'UnExpected Error: '||SQLERRM);
419 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '*---------------------------------------------------------------------------------------------*');
420 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Execution Status: Failure (Error:' ||SQLCODE||SQLERRM || ')');
421 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Execution Ends On: ' || to_char(sysdate,'MM-DD-YYYY HH24:MI:SS'));
422 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '*---------------------------------------------------------------------------------------------*');
423 WHEN OTHERS THEN
424 retcode:=2;
425 ROLLBACK TO CREATE_Accounting;
426 errbuf:= FND_API.G_RET_STS_UNEXP_ERROR;
427 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error: '||SQLERRM);
428 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '*---------------------------------------------------------------------------------------------*');
429 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Execution Status: Failure (Error:' ||SQLCODE||SQLERRM || ')');
430 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Execution Ends On: ' || to_char(sysdate,'MM-DD-YYYY HH24:MI:SS'));
431 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '*---------------------------------------------------------------------------------------------*');
432
433 END CreateAccounting;
434
435 END OZF_GL_TRANSFER_PVT;