[Home] [Help]
PACKAGE BODY: APPS.OZF_GL_TRANSFER_PVT
Source
1 PACKAGE BODY OZF_GL_TRANSFER_PVT AS
2 /* $Header: ozfvgtrb.pls 120.4.12010000.2 2008/09/19 05:31:16 psomyaju ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OZF_GL_TRANSFER_PVT';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'ozfgltrb.pls';
6
7 -- Function checks if the set of books has any reporting sobs.
8 -- Returns 'Y' if there are reporting sobs for a primary or secondary sob.
9 -- This function called from the SRS program definition for
10 -- Claims Transfer to GL for parameter
11 -- Transfer Reporting SOBs.
12
13 FUNCTION ozf_mc_check (p_psob_id NUMBER)
14 RETURN varchar2 IS
15 cursor c1 (sob_id number) IS
16 SELECT sp.set_of_books_id
17 FROM gl_sets_of_books sob,
18 ozf_sys_parameters sp
19 WHERE sob.set_of_books_id = sp.set_of_books_id
20 AND sp.set_of_books_id = nvl(sob_id, sp.set_of_books_id)
21 AND sp.set_of_books_id <> -1;
22
23 l_org_id NUMBER(15);
24 l_mrc_flag VARCHAR2(1);
25 l_return_flag VARCHAR2(1) := 'N';
26
27 BEGIN
28 l_org_id := MO_GLOBAL.GET_CURRENT_ORG_ID(); -- R12 Enhancements
29 for rec in c1(p_psob_id) loop
30 gl_mc_info.mrc_enabled(rec.set_of_books_id, 682, l_org_id, '',l_mrc_flag);
31 if l_mrc_flag = 'Y' then
32 l_return_flag := 'Y';
33 end if;
34 end loop;
35
36 IF l_return_flag = 'Y' THEN
37 return 'Y';
38 ELSE
39 return NULL;
40 END IF;
41 END ozf_mc_check;
42
43 -- Calls common transfer to GL API.
44 PROCEDURE OZF_GL_TRANSFER (
45 p_errbuf OUT NOCOPY VARCHAR2
46 ,p_retcode OUT NOCOPY NUMBER
47 ,p_selection_type NUMBER
48 ,p_set_of_books_id NUMBER
49 ,p_include_reporting_sob VARCHAR2
50 ,p_batch_name VARCHAR2
51 ,p_start_date VARCHAR2
52 ,p_end_date VARCHAR2
53 ,p_accounting_method VARCHAR2
54 ,p_document_class VARCHAR2
55 ,p_journal_category VARCHAR2
56 ,p_validate_account VARCHAR2
57 ,p_gl_transfer_mode VARCHAR2
58 ,p_submit_journal_import VARCHAR2
59 ,p_summary_journal_entry VARCHAR2
60 ,p_process_days NUMBER
61 ,p_debug_flag VARCHAR2
62 ,p_trace_flag VARCHAR2 )
63 IS
64
65 l_sob_list xla_gl_transfer_pkg.t_sob_list := xla_gl_transfer_pkg.t_sob_list();
66 l_sob_info gl_mc_info.t_ael_sob_info;
67
68 i NUMBER := 0;
69 l_request_id NUMBER; -- Concurrent Request Id
70 l_appl_id NUMBER; -- Application Id.
71 l_user_id NUMBER; -- User Id.
72 l_org_id NUMBER;
73 -- Bug 5606829
74 l_org_code VARCHAR2(240);
75 l_curr_calling_sequence VARCHAR2(240);
76 l_debug_info VARCHAR2(1000);
77 l_je_category xla_gl_transfer_pkg.t_ae_category;
78 l_start_date DATE;
79 l_end_date DATE;
80 l_errbuf VARCHAR2(1000);
81
82 l_api_name CONSTANT VARCHAR2(30) := 'OZF_GL_TRANSFER';
83 l_api_version CONSTANT NUMBER := 1.0;
84 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
85 l_val_acct VARCHAR2(30);
86 l_gl_interface VARCHAR2(30);
87 l_submit_journal_import VARCHAR2(30);
88 l_je_category_name VARCHAR2(80);
89
90 -- Bug 4888509
91 CURSOR je_name_csr (je_name VARCHAR2) IS
92 SELECT USER_JE_CATEGORY_NAME FROM gl_je_categories
93 where JE_CATEGORY_NAME = je_name;
94
95 CURSOR lookup_name_csr (lk_type VARCHAR2, lk_code VARCHAR2) IS
96 SELECT meaning
97 from fnd_lookups
98 where lookup_type = lk_type
99 and lookup_code = lk_code;
100
101 CURSOR submit_jrnl_import_csr (lk_type VARCHAR2, lk_code VARCHAR2) IS
102 SELECT l.meaning
103 FROM fnd_lookups l, fnd_product_installations i
104 WHERE l.lookup_type = lk_type
105 AND i.application_id = 101
106 AND (i.status = 'I' or (i.status = 'S' and l.lookup_code = 'N'))
107 AND l.lookup_code = lk_code;
108
109 CURSOR c1 (sob_id NUMBER) IS
110 SELECT sob.set_of_books_id
111 , sob.name
112 , sob.currency_code
113 , 'Accrual' accounting_method
114 , 'P' sob_type
115 , 'N' encumbrance_flag
116 FROM gl_sets_of_books sob,
117 ozf_sys_parameters sp
118 WHERE sob.set_of_books_id = sp.set_of_books_id
119 AND sp.set_of_books_id = decode(sob_id, -1,sp.set_of_books_id, sob_id)
120 AND sp.set_of_books_id <> -1;
121
122 BEGIN
123
124 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Start ='||G_PKG_NAME);
125 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '*--------------------------------Transfer To GL Execution Report -----------------------------*');
126 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Execution Starts On: ' || to_char(sysdate,'MM-DD-YYYY HH24:MI:SS'));
127 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '*---------------------------------------------------------------------------------------------*');
128
129 FND_FILE.PUT_LINE(FND_FILE.LOG, 'In Parameters : ');
130
131 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_selection_type : '||p_selection_type );
132 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_set_of_books_id : '||p_set_of_books_id );
133 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_include_reporting_sob : '||p_include_reporting_sob );
134 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_batch_name : '||p_batch_name );
135 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_start_date : '||p_start_date );
136 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_end_date : '||p_end_date );
137 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_accounting_method : '||p_accounting_method );
138 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_document_class : '||p_document_class );
139 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_journal_category : '||p_journal_category );
140 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_validate_account : '||p_validate_account );
141 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_gl_transfer_mode : '||p_gl_transfer_mode );
142 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_submit_journal_import : '||p_submit_journal_import );
143 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_summary_journal_entry : '||p_summary_journal_entry );
144 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_process_days : '||p_process_days );
145 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_debug_flag : '||p_debug_flag );
146
147 -- Set the start date. Currently utilizing the gl_periods set for
148 -- AR applicaiotn.Change to 682 after open/close periods functionality is
149 -- provided to Funds and Claims module.
150
151 /* Bugfix: 7412302 - Changed start date to G_MISS_DATE in case of NULL.
152 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Before deriving the start date');
153
154 IF p_start_date is null THEN
155 if p_set_of_books_id <> -1 then
156 select min(start_date)
157 into l_start_date
158 from gl_period_statuses
159 where application_id = 222
160 and set_of_books_id = p_set_of_books_id
161 and nvl(adjustment_period_flag,'N') = 'N'
162 and closing_status IN ( 'O','F');
163 else
164 select min(start_date)
165 into l_start_date
166 from gl_period_statuses
167 where application_id = 222
168 and set_of_books_id in ( select set_of_books_id
169 from ozf_sys_parameters
170 where set_of_books_id <> -1)
171 and nvl(adjustment_period_flag,'N') = 'N'
172 and closing_status IN ( 'O','F');
173 end if;
174 END IF;
175
176 FND_FILE.PUT_LINE(FND_FILE.LOG, 'After deriving start date '||l_start_date);
177 */
178
179
180 IF p_start_date IS NOT NULL THEN
181 l_start_date := FND_DATE.CANONICAL_TO_DATE(p_start_date);
182 ELSE
183 l_start_date := FND_API.G_MISS_DATE;
184 END IF;
185
186 l_end_date := FND_DATE.CANONICAL_TO_DATE(p_end_date);
187
188 FND_FILE.PUT_LINE(FND_FILE.LOG, 'After deriving start date '||l_start_date);
189 FND_FILE.PUT_LINE(FND_FILE.LOG, 'After converting end date '||l_end_date);
190
191 -- If the Document class is Claims then transfer Claims and Deduction
192 -- entries.
193
194 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Before deriving the je_category - Document class : '||p_document_class);
195
196 IF p_document_class IS NOT NULL THEN
197 IF nvl(p_document_class,'A') = 'Claims' THEN
198 l_je_category(1) := 'Claims';
199 l_je_category(2) := 'Deductions';
200 l_je_category(3) := 'Settlement';
201 ELSIF nvl(p_document_class,'A') = 'Budget' THEN
202 l_je_category(1) := 'Fixed Budgets';
203 l_je_category(2) := 'Accrual Budgets';
204 ELSIF nvl(p_document_class,'A') = 'All' THEN
205 l_je_category(1) := 'Claims';
206 l_je_category(2) := 'Deductions';
207 l_je_category(3) := 'Fixed Budgets';
208 l_je_category(4) := 'Accrual Budgets';
209 l_je_category(5) := 'Settlement';
210 END IF;
211 ELSE -- Called from SRS
212 IF p_journal_category = 'A' THEN
213 l_je_category(1) := 'Claims';
214 l_je_category(2) := 'Deductions';
215 l_je_category(3) := 'Fixed Budgets';
216 l_je_category(4) := 'Accrual Budgets';
217 l_je_category(5) := 'Settlement';
218 ELSE
219 l_je_category(1) := p_journal_category;
220 END IF;
221 END IF;
222
223 FND_FILE.PUT_LINE(FND_FILE.LOG, 'After deriving the je_category');
224
225 --Get Profile Information
226 l_request_id := FND_GLOBAL.conc_request_id;
227 l_appl_id := FND_GLOBAL.resp_appl_id;
228 l_user_id := FND_GLOBAL.user_id;
229 l_org_id := MO_GLOBAL.GET_CURRENT_ORG_ID(); -- R12 Enhancements
230
231 FND_FILE.PUT_LINE(FND_FILE.LOG, 'After getting user profile from client_info');
232 l_debug_info := 'Poplating l_sob_info table';
233
234 FOR rec IN c1(p_set_of_books_id) LOOP
235 i := i+1 ;
236 l_sob_info(i).sob_id := rec.set_of_books_id;
237 l_sob_info(i).currency_code := rec.currency_code;
238 l_sob_info(i).accounting_method := rec.accounting_method;
239 l_sob_info(i).sob_type := rec.sob_type;
240 l_sob_info(i).encumb_flag := rec.encumbrance_flag;
241 l_sob_info(i).sob_name := rec.name;
242 END LOOP; /* c1 loop */
243
244 FND_FILE.PUT_LINE(FND_FILE.LOG, 'After populating sob_info');
245
246 /* -- If MRC is Installed
247 IF Nvl(p_include_reporting_sob,'N') = 'Y' THEN
248 l_debug_info := 'Calling MRC API to get the reporting set of books';
249 gl_mc_info.ap_ael_sobs(l_sob_info);
250 END IF;
251 */
252
253 -- Populate l_sob_list for common transfer API
254 l_debug_info := 'Populating l_sob_list table';
255
256 FOR i IN l_sob_info.first..l_sob_info.last LOOP
257 l_sob_list.EXTEND;
258 l_sob_list(i).sob_id := l_sob_info(i).sob_id;
259 l_sob_list(i).sob_name := l_sob_info(i).sob_name;
260 l_sob_list(i).sob_curr_code := l_sob_info(i).currency_code;
261 l_sob_list(i).encum_flag := l_sob_info(i).encumb_flag;
262 END LOOP;
263
264 FND_FILE.PUT_LINE(FND_FILE.LOG, 'After populating sob_list');
265
266 -- Get org_code
267 l_debug_info := 'Getting Organization Name';
268
269 -- Fix for bug 4888509
270 IF l_org_id IS NOT NULL THEN
271 l_org_code := mo_global.get_ou_name(l_org_id);
272 END IF;
273
274 --Call common transfer API
275 l_debug_info :='Calling Common Transfer API';
276
277 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Before calling xla_gl_transfer');
278
279 IF p_journal_category IS NOT NULL THEN
280 IF p_journal_category = 'A' THEN
281 OPEN lookup_name_csr('BOTH_ALL_NONE', p_journal_category);
282 FETCH lookup_name_csr INTO l_je_category_name;
283 CLOSE lookup_name_csr;
284 ELSE
285 OPEN je_name_csr(p_journal_category);
286 FETCH je_name_csr into l_je_category_name;
287 CLOSE je_name_csr;
288 END IF;
289 END IF;
290
291 IF p_validate_account IS NOT NULL THEN
292 OPEN lookup_name_csr('YES_NO', p_validate_account);
293 FETCH lookup_name_csr into l_val_acct;
294 CLOSE lookup_name_csr;
295 END IF;
296
297 IF p_gl_transfer_mode IS NOT NULL THEN
298 OPEN lookup_name_csr('XLA_GL_TRANSFER_MODE', p_gl_transfer_mode);
299 FETCH lookup_name_csr into l_gl_interface;
300 CLOSE lookup_name_csr;
301 END IF;
302
303 IF p_submit_journal_import IS NOT NULL THEN
304 OPEN submit_jrnl_import_csr('YES_NO', p_submit_journal_import);
305 FETCH submit_jrnl_import_csr into l_submit_journal_import;
306 CLOSE submit_jrnl_import_csr;
307 END IF;
308
309 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Set of Books Name : '||l_sob_info(1).sob_name );
310 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Batch Name : '||p_batch_name );
311 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'From Date : '||l_start_date );
312 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'To Date : '||l_end_date );
313 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Journal Category : '||l_je_category_name );
314 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Validate Accounts : '||l_val_acct );
315 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Transfer to Gl Interface : '||l_gl_interface );
316 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Submit Journal Import : '||l_submit_journal_import );
317
318 xla_gl_transfer_pkg.xla_gl_transfer
319 (
320 p_application_id => l_appl_id,
321 p_user_id => l_user_id,
322 p_request_id => l_request_id,
323 p_org_id => l_org_id,
324 p_program_name => 'OZF1',
325 p_selection_type => p_selection_type,
326 p_sob_list => l_sob_list,
327 p_batch_name => p_batch_name,
328 p_source_doc_id => NULL,
329 p_source_document_table => NULL,
330 p_start_date => l_start_date,
331 p_end_date => l_end_date,
332 p_journal_category => l_je_category,
333 p_validate_account => p_validate_account,
334 p_gl_transfer_mode => p_gl_transfer_mode,
335 p_submit_journal_import => p_submit_journal_import,
336 p_summary_journal_entry => p_summary_journal_entry,
337 p_process_days => p_process_days,
338 p_batch_desc => l_org_code || ' ' || p_batch_name,
339 p_je_desc => l_org_code || ' ' || p_batch_name,
340 p_je_line_desc => l_org_code || ' ' || p_batch_name,
341 p_debug_flag => p_debug_flag
342 );
343
344 FND_FILE.PUT_LINE(FND_FILE.LOG, 'After calling xla_gl_transfer');
345 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '*---------------------------------------------------------------------------------------------*');
346 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Execution Status: Successful' );
347 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Execution Ends On: ' || to_char(sysdate,'MM-DD-YYYY HH24:MI:SS'));
348 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '*---------------------------------------------------------------------------------------------*');
349
350 EXCEPTION
351 WHEN OTHERS THEN
352 p_errbuf := Sqlerrm;
353 p_retcode := 2;
354 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error: '||SQLERRM);
355 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '*---------------------------------------------------------------------------------------------*');
356 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Execution Status: Failure (Error:' ||SQLCODE||SQLERRM || ')');
357 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Execution Ends On: ' || to_char(sysdate,'MM-DD-YYYY HH24:MI:SS'));
358 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '*---------------------------------------------------------------------------------------------*');
359
360 IF (SQLCODE <> -20001) THEN
361 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error: '||SQLCODE||SQLERRM);
362 --APP_EXCEPTION.RAISE_EXCEPTION;
363 RAISE;
364 ELSE
365 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error: '||SQLCODE||SQLERRM);
366 --g_error_message := Sqlerrm;
367 APP_EXCEPTION.RAISE_EXCEPTION;
368 END IF;
369 END OZF_GL_TRANSFER;
370
371 END OZF_GL_TRANSFER_PVT;