[Home] [Help]
PACKAGE BODY: APPS.OKL_GL_TRANSFER_PVT
Source
1 PACKAGE BODY Okl_Gl_Transfer_Pvt AS
2 /* $Header: OKLRGLTB.pls 120.8 2007/01/24 12:43:40 rgooty noship $ */
3
4
5 -- Calls common transfer to GL API.
6
7 FUNCTION GET_PROPER_LENGTH(p_input_data IN VARCHAR2,
8 p_input_length IN NUMBER,
9 p_input_type IN VARCHAR2)
10 RETURN VARCHAR2
11
12 IS
13
14 x_return_data VARCHAR2(1000);
15
16 BEGIN
17
18 IF (p_input_type = 'TITLE') THEN
19 IF (p_input_data IS NOT NULL) THEN
20 x_return_data := RPAD(SUBSTR(ltrim(rtrim(p_input_data)),1,p_input_length),p_input_length,' ');
21 ELSE
22 x_return_data := RPAD(' ',p_input_length,' ');
23 END IF;
24 ELSE
25 IF (p_input_data IS NOT NULL) THEN
26 IF (length(p_input_data) > p_input_length) THEN
27 x_return_data := RPAD('*',p_input_length,'*');
28 ELSE
29 x_return_data := RPAD(p_input_data,p_input_length,' ');
30 END IF;
31 ELSE
32 x_return_data := RPAD(' ',p_input_length,' ');
33 END IF;
34 END IF;
35
36 RETURN x_return_data;
37
38 END GET_PROPER_LENGTH;
39
40
41 PROCEDURE CREATE_REPORT(p_request_id NUMBER)
42 IS
43 --Added by Keerthi to format the report
44
45 /* TYPE report_rec_type IS RECORD(
46 transaction_date char(12),
47 contract_number char(20),
48 transaction_number char(14),
49 transaction_line_number char(6),
50 accounting_date char(12),
51 dr_cr_flag char(4),
52 accounted_amount char(19),
53 account char(30),
54 currency char(4));
55 */
56
57 --Changed by Santonyr to support multi-byte formats Bug Number 2960042
58
59 TYPE report_rec_type IS RECORD(
60 transaction_date VARCHAR2(36),
61 contract_number VARCHAR2(60),
62 transaction_number VARCHAR2(42),
63 transaction_line_number VARCHAR2(18),
64 accounting_date VARCHAR2(36),
65 dr_cr_flag VARCHAR2(12),
66 accounted_amount VARCHAR2(57),
67 account VARCHAR2(90),
68 currency VARCHAR2(12));
69
70
71 l_transaction_date_len NUMBER := 12;
72 --PAGARG Bug 4198290 As Account is moved to next line, increased the length
73 --for contract number
74 l_contract_num_len NUMBER := 45;
75 l_transaction_num_len NUMBER := 14;
76 l_transaction_line_num_len NUMBER := 7;
77 l_accounting_date_len NUMBER := 12;
78 l_dr_cr_flag_len NUMBER := 4;
79 l_accounted_amount_len NUMBER := 19;
80 --PAGARG Bug 4198290 As Account is moved to next line, increased the length
81 l_account_len NUMBER := 90;
82 --PAGARG Bug 4198290 As Account is moved to next line, increased the length
83 --for currency
84 l_currency_len NUMBER := 8;
85 l_offset_len NUMBER := 115;
86
87 header_report1_rec report_rec_type;
88 header_report2_rec report_rec_type;
89 proc_report_rec report_rec_type;
90 non_proc_report_rec report_rec_type;
91
92 -- Changed by Santonyr on 18th Jun, 2003 to fix the bug 3012735
93 -- Changed the length of variables to hold the multi-byte values
94
95 l_line_length NUMBER :=121;
96 l_total_process NUMBER :=0;
97 l_error_process NUMBER :=0;
98 l_success_process NUMBER :=0;
99 l_set_of_books_name VARCHAR2(150);
100 l_temp_trx_number VARCHAR2(90);
101 l_accounted_amount VARCHAR2(60);
102 l_dr_cr_flag VARCHAR2(30);
103 l_temp_trx_type VARCHAR2(90);
104 l_temp_contract_number VARCHAR2(120);
105 l_date_transaction_occurred DATE;
106 l_temp_line_number NUMBER;
107 l_temp_acc_date DATE;
108 l_temp_period_name VARCHAR2(90);
109 l_temp_start_date DATE;
110 l_temp_end_date DATE;
111 l_org_name VARCHAR2(150);
112 l_org_id NUMBER;
113
114 CURSOR cntrct_details_csr(l_source_id NUMBER) IS
115 SELECT tcn.trx_number,
116 khr.contract_number,
117 tcn.date_transaction_occurred,
118 tcl.line_number
119 FROM okl_trx_contracts tcn,
120 okl_trx_types_v try,
121 okc_k_headers_b khr,
122 okl_txl_cntrct_lns tcl
123 WHERE tcn.id = tcl.tcn_id AND
124 tcn.try_id = try.id AND
125 tcn.khr_id = khr.id AND
126 tcl.id = l_source_id;
127
128 CURSOR asset_details_csr(l_source_id NUMBER) IS
129 SELECT tas.trans_number,
130 khr.contract_number,
131 tas.date_trans_occurred,
132 tal.line_number
133 FROM okl_trx_assets tas,
134 okl_txl_assets_b tal,
135 okl_trx_types_v try,
136 okc_k_headers_b khr
137 WHERE tas.id = tal.tas_id AND
138 tas.try_id = try.id AND
139 tal.dnz_khr_id = khr.id AND
140 tal.id = l_source_id;
141
142 CURSOR ae_category_csr(p_request_id NUMBER) IS
143 SELECT sum(accounted_dr) total_dr,
144 sum(accounted_cr) total_cr,
145 try.name try_name,
146 try.id try_id,
147 aeh.ae_category ae_category,
148 ael.currency_code
149 FROM okl_Ae_headers aeh,
150 okl_ae_lines ael,
151 okl_trx_types_v try,
152 okl_txl_cntrct_lns tcl,
153 okl_trx_contracts tcn
154 WHERE aeh.ae_header_id=ael.ae_header_id
155 AND aeh.accounting_error_code IS NULL
156 AND ael.accounting_error_code IS NULL
157 AND aeh.request_id = p_request_id
158 AND ael.source_id = tcl.id
159 AND tcl.tcn_id = tcn.id
160 AND tcn.try_id = try.id
161 GROUP BY aeh.ae_category,
162 try.name,
163 try.id,
164 ael.currency_code
165 UNION
166 SELECT sum(accounted_dr) total_dr,
167 sum(accounted_cr) total_cr,
168 try.name try_name,
169 try.id try_id,
170 aeh.ae_category ae_category,
171 ael.currency_code
172 FROM okl_Ae_headers aeh,
173 okl_ae_lines ael,
174 okl_trx_types_v try,
175 okl_trx_assets tas,
176 okl_txl_assets_b tal
177 WHERE aeh.ae_header_id=ael.ae_header_id
178 AND aeh.accounting_error_code IS NULL
179 AND ael.accounting_error_code IS NULL
180 AND aeh.request_id = p_request_id
181 AND ael.source_id = tal.id
182 AND tal.tas_id = tas.id
183 AND tas.try_id = try.id
184 GROUP BY aeh.ae_category,
185 try.name,
186 try.id,
187 ael.currency_code;
188
189 CURSOR gl_proc_dst_csr(p_request_id NUMBER,
190 p_category VARCHAR,
191 p_try_id NUMBER,
192 p_currency_code VARCHAR) IS
193 SELECT ael.source_table,
194 ael.source_id,
195 ael.ae_line_number,
196 aeh.accounting_date,
197 aeh.ae_category,
198 ael.accounted_dr,
199 ael.accounted_cr,
200 okl_Accounting_util.get_concat_segments(ael.code_combination_id) account,
201 ael.currency_code
202 FROM okl_ae_headers aeh,
203 okl_ae_lines ael,
204 okl_txl_cntrct_lns tcl,
205 okl_trx_contracts tcn
206 WHERE ael.ae_header_id = aeh.ae_header_id
207 AND ael.gl_transfer_error_code is null
208 AND aeh.request_id = p_request_id
209 AND aeh.ae_category=p_category
210 AND ael.currency_code = p_currency_code
211 AND tcl.id = ael.source_id
212 AND tcl.tcn_id = tcn.id
213 AND tcn.try_id = p_try_id
214 ORDER BY ael.source_id;
215
216 CURSOR gl_error_dst_csr(p_request_id NUMBER) IS
217 SELECT ael.source_table,
218 ael.source_id,
219 ael.ae_line_number,
220 aeh.accounting_date,
221 aeh.ae_category,
222 ael.accounted_dr,
223 ael.accounted_cr,
224 okl_Accounting_util.get_concat_segments(ael.code_combination_id) account,
225 ael.currency_code
226 FROM okl_ae_headers aeh,
227 okl_ae_lines ael
228 WHERE ael.ae_header_id = aeh.ae_header_id
229 AND ael.gl_transfer_error_code is not null
230 AND aeh.request_id = p_request_id
231 ORDER BY ael.source_id;
232
233 CURSOR header_details_csr(p_request_id NUMBER) IS
234 SELECT count(*) total,
235 ael.gl_transfer_error_code
236 FROM okl_ae_headers aeh,
237 okl_ae_lines ael
238 WHERE aeh.request_id = p_request_id
239 AND aeh.ae_header_id = ael.ae_header_id
240 GROUP BY ael.gl_transfer_error_code;
241
242 CURSOR org_csr (p_org_id IN NUMBER) IS
243 SELECT name
244 FROM hr_operating_units
245 WHERE organization_id = p_org_id;
246
247 BEGIN
248
249
250 l_org_id := MO_GLOBAL.GET_CURRENT_ORG_ID();
251
252 --to print the header in 2 lines
253
254 header_report1_rec.transaction_date :=GET_PROPER_LENGTH(okl_accounting_util.get_message_token
255 ('OKL_LP_GL_TRANSFER','OKL_TRANSACTION'),l_transaction_date_len,'TITLE');
256
257 header_report1_rec.contract_number :=GET_PROPER_LENGTH(okl_accounting_util.get_message_token
258 ('OKL_LP_GL_TRANSFER','OKL_CONTRACT'),l_contract_num_len,'TITLE');
259
260 header_report1_rec.transaction_number :=GET_PROPER_LENGTH(okl_accounting_util.get_message_token
261 ('OKL_LP_GL_TRANSFER','OKL_TRANSACTION'),l_transaction_num_len,'TITLE');
262
263 header_report1_rec.transaction_line_number :=GET_PROPER_LENGTH(okl_accounting_util.get_message_token
264 ('OKL_LP_GL_TRANSFER','OKL_ACC_LINE'),l_transaction_line_num_len,'TITLE');
265
266 header_report1_rec.accounting_date :=GET_PROPER_LENGTH(okl_accounting_util.get_message_token
267 ('OKL_LP_GL_TRANSFER','OKL_ACCOUNT_ING'),l_accounting_date_len,'TITLE');
268
269 header_report1_rec.dr_cr_flag :=GET_PROPER_LENGTH(okl_accounting_util.get_message_token
270 ('OKL_LP_GL_TRANSFER','OKL_DR_CR_FLAG'),l_dr_cr_flag_len,'TITLE');
271
272 header_report1_rec.accounted_amount :=GET_PROPER_LENGTH(okl_accounting_util.get_message_token
273 ('OKL_LP_GL_TRANSFER','OKL_ACCOUNTED'),l_accounted_amount_len,'TITLE');
274
275 --PAGARG Bug 4198290 As Account is moved to next line, no need to trim it.
276 --Can accomodate the complete title
277 header_report1_rec.account := okl_accounting_util.get_message_token('OKL_LP_GL_TRANSFER','OKL_ACCOUNT');
278
279 header_report1_rec.currency :=GET_PROPER_LENGTH(okl_accounting_util.get_message_token
280 ('OKL_LP_GL_TRANSFER','OKL_CURRENCY'),l_currency_len,'TITLE');
281
282 header_report2_rec.transaction_date :=GET_PROPER_LENGTH(okl_accounting_util.get_message_token
283 ('OKL_LP_GL_TRANSFER','OKL_DATE'),l_transaction_date_len,'TITLE');
284
285 header_report2_rec.contract_number :=GET_PROPER_LENGTH(okl_accounting_util.get_message_token
286 ('OKL_LP_GL_TRANSFER','OKL_NUMBER'),l_contract_num_len,'TITLE');
287
288 header_report2_rec.transaction_number :=GET_PROPER_LENGTH(okl_accounting_util.get_message_token
289 ('OKL_LP_GL_TRANSFER','OKL_NUMBER'),l_transaction_num_len,'TITLE');
290
291 header_report2_rec.transaction_line_number :=GET_PROPER_LENGTH(okl_accounting_util.get_message_token
292 ('OKL_LP_GL_TRANSFER','OKL_NUMBER'),l_transaction_line_num_len,'TITLE');
293
294 header_report2_rec.accounting_date :=GET_PROPER_LENGTH(okl_accounting_util.get_message_token
295 ('OKL_LP_GL_TRANSFER','OKL_DATE'),l_accounting_date_len,'TITLE');
296
297 header_report2_rec.dr_cr_flag :=LPAD(' ',l_dr_cr_flag_len,' ');
298
299 header_report2_rec.accounted_amount :=GET_PROPER_LENGTH(okl_accounting_util.get_message_token
300 ('OKL_LP_GL_TRANSFER','OKL_AMOUNT'),l_accounted_amount_len,'TITLE');
301
302 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD(' ', 54 , ' ' ) || okl_accounting_util.get_message_token
303 ('OKL_LP_GL_TRANSFER','OKL_ACCT_LEASE_MANAGEMENT'));
304 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD(' ', 54 , ' ' ) || '-----------------------');
305 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD(' ', 51 , ' ' ) || okl_accounting_util.get_message_token
306 ('OKL_LP_GL_TRANSFER','OKL_GL_TRANSFER_REPORT'));
307 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD(' ', 51 , ' ' ) || '------------------------------');
308 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '');
309 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '');
310
311 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(okl_accounting_util.get_message_token('OKL_LP_GL_TRANSFER',
312 'OKL_RUN_DATE'),30,' ')||':'|| SUBSTR(TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI'),1,30));
313
314 l_set_of_books_name := okl_accounting_util.get_set_of_books_name (okl_accounting_util.get_set_of_books_id);
315
316 FOR org_rec IN org_csr (l_org_id)
317 LOOP
318 l_org_name := org_rec.name;
319 END LOOP;
320
321 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(okl_accounting_util.get_message_token('OKL_LP_GL_TRANSFER',
322 'OKL_SET_OF_BOOKS'),30,' ')
323 ||':'|| RPAD(SUBSTR(l_set_of_books_name, 1, 30), 30, ' ') || LPAD(' ', 45 , ' ' ) );
324 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD( okl_accounting_util.get_message_token
325 ('OKL_LP_GL_TRANSFER','OKL_OPERUNIT'),30,' ')
326 ||':'|| SUBSTR(l_org_name, 1, 30) );
327
328 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD(' ',l_line_length,' '));
329 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD(' ',l_line_length,' '));
330
331 FOR header_details_rec IN header_details_csr(p_request_id)
332 LOOP
333
334 IF header_details_rec.gl_transfer_error_code is null THEN
335 l_success_process := header_details_rec.total;
336 END IF;
337
338 IF header_details_rec.gl_transfer_error_code is not null THEN
339 l_error_process := header_details_rec.total;
340 END IF;
341
342 END LOOP;
343
344 l_total_process :=l_success_process + l_error_process;
345
346 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(okl_accounting_util.get_message_token('OKL_LP_GL_TRANSFER',
347 'OKL_TOTAL_ACCT_LINES'),65,' ') ||':'||l_total_process);
348 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(okl_accounting_util.get_message_token('OKL_LP_GL_TRANSFER',
349 'OKL_ACCT_LINES_SUCCESS'),65,' ') ||':'||l_success_process);
350 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(okl_accounting_util.get_message_token('OKL_LP_GL_TRANSFER',
351 'OKL_ACCT_LINES_ERROR'),65,' ') ||':'||l_error_process);
352 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD(' ',l_line_length,' '));
353 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD(' ',l_line_length,' '));
354
355 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,okl_accounting_util.get_message_token('OKL_LP_GL_TRANSFER','OKL_PROCESSED_ENTRIES'));
356 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'-----------------');
357 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD(' ',l_line_length,' '));
358
359 OPEN ae_category_csr(p_request_id);
360
361 IF ae_category_csr%NOTFOUND THEN
362 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,okl_accounting_util.get_message_token('OKL_LP_GL_TRANSFER','OKL_NO_RECORDS'));
363 END IF;
364
365 CLOSE ae_category_csr;
366
367 FOR ae_category_rec IN ae_category_csr(p_request_id)
368 LOOP
369
370 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(okl_accounting_util.get_message_token('OKL_LP_GL_TRANSFER',
371 'OKL_JOURNAL_CATEGORY')||' :'||ae_category_rec.ae_category ||' '||
372 okl_accounting_util.get_message_token('OKL_LP_GL_TRANSFER','OKL_TRANSACTION_TYPE')||
373 ':'||ae_category_rec.try_name , l_line_length ,' '));
374 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD(' ',l_line_length,' '));
375
376 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,header_report1_rec.transaction_date||
377 header_report1_rec.contract_number||
378 header_report1_rec.transaction_number ||
379 header_report1_rec.transaction_line_number ||
380 header_report1_rec.accounting_date ||
381 header_report1_rec.dr_cr_flag ||
382 header_report1_rec.accounted_amount ||
383 header_report1_rec.currency);
384
385 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,header_report2_rec.transaction_date||
386 header_report2_rec.contract_number||
387 header_report2_rec.transaction_number ||
388 header_report2_rec.transaction_line_number ||
389 header_report2_rec.accounting_date||
390 header_report2_rec.dr_cr_flag ||
391 header_report2_rec.accounted_amount );
392
393 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD('-', l_line_length , '-' ));
394
395 FOR gl_proc_dst_rec IN gl_proc_dst_csr(p_request_id,
396 ae_category_rec.ae_category,
397 ae_category_rec.try_id,
398 ae_category_rec.currency_code)
399 LOOP
400
401 IF (gl_proc_dst_rec.source_table='OKL_TXL_CNTRCT_LNS') THEN
402
403 OPEN cntrct_details_csr(gl_proc_dst_rec.source_id) ;
404 FETCH cntrct_details_csr INTO l_temp_trx_number,
405 l_temp_contract_number ,
406 l_date_transaction_occurred,
407 l_temp_line_number;
408 CLOSE cntrct_details_csr;
409
410 ELSE
411
412 OPEN asset_details_csr(gl_proc_dst_rec.source_id) ;
413 FETCH asset_details_csr INTO l_temp_trx_number,
414 l_temp_contract_number ,
415 l_date_transaction_occurred,
416 l_temp_line_number;
417 CLOSE asset_details_csr;
418
419 END IF;
420
421 IF gl_proc_dst_rec.accounted_dr IS NOT NULL THEN
422 l_dr_cr_flag := 'D';
423 l_accounted_amount := gl_proc_dst_rec.accounted_dr;
424 END IF;
425
426 IF gl_proc_dst_rec.accounted_cr IS NOT NULL THEN
427 l_dr_cr_flag := 'C';
428 l_accounted_amount := gl_proc_dst_rec.accounted_cr;
429 END IF;
430
431 proc_report_rec.transaction_date := GET_PROPER_LENGTH(l_date_transaction_occurred,
432 l_transaction_date_len,'DATA');
433 --PAGARG Bug 4198290 Instead of putting * substring the contract number
434 --if exceeds given length
435 proc_report_rec.contract_number :=
436 RPAD(SUBSTR(l_temp_contract_number,1,l_contract_num_len),l_contract_num_len, ' ');
437 proc_report_rec.transaction_number := GET_PROPER_LENGTH(l_temp_trx_number,l_transaction_num_len,'DATA');
438 proc_report_rec.transaction_line_number := GET_PROPER_LENGTH(l_temp_line_number,
439 l_transaction_line_num_len,'DATA');
440 proc_report_rec.accounting_date := GET_PROPER_LENGTH(gl_proc_dst_rec.accounting_date,
441 l_accounting_date_len,'DATA');
442 proc_report_rec.dr_cr_flag := GET_PROPER_LENGTH(l_dr_cr_flag,
443 l_dr_cr_flag_len,'DATA');
444 proc_report_rec.accounted_amount := GET_PROPER_LENGTH(okl_Accounting_util.format_amount(l_accounted_amount,gl_proc_dst_rec.currency_code),
445 l_accounted_amount_len,'DATA');
446 --PAGARG Bug 4198290 As Account is moved to next line, no need to
447 --check length, display complete data
448 proc_report_rec.account := gl_proc_dst_rec.account;
449 proc_report_rec.currency := GET_PROPER_LENGTH(gl_proc_dst_rec.currency_code,
450 l_currency_len,'DATA');
451
452 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,proc_report_rec.transaction_date ||
453 proc_report_rec.contract_number ||
454 proc_report_rec.transaction_number ||
455 proc_report_rec.transaction_line_number ||
456 proc_report_rec.accounting_date ||
457 proc_report_rec.dr_cr_flag ||
458 proc_report_rec.accounted_amount||
459 proc_report_rec.currency);
460 --PAGARG Bug 4198290 Move account data to next line
461 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, header_report1_rec.account ||' : '|| proc_report_rec.account);
462
463 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD(' ', l_line_length , ' ' ));
464
465 END LOOP;
466
467 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD('-', l_line_length , '-' ));
468
469 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,okl_accounting_util.get_message_token('OKL_LP_GL_TRANSFER','OKL_TOTAL') ||
470 RPAD(okl_accounting_util.get_message_token('OKL_LP_GL_TRANSFER','OKL_DEBIT'),l_accounted_amount_len,' ') ||':'||
471 GET_PROPER_LENGTH(okl_Accounting_util.format_amount(ae_category_rec.total_dr,ae_category_rec.currency_code), l_accounted_amount_len,'DATA') );
472
473 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,okl_accounting_util.get_message_token('OKL_LP_GL_TRANSFER','OKL_TOTAL') ||
474 RPAD(okl_accounting_util.get_message_token('OKL_LP_GL_TRANSFER','OKL_CREDIT_AMT'),l_accounted_amount_len,' ') ||':'||
475 GET_PROPER_LENGTH(okl_Accounting_util.format_amount(ae_category_rec.total_cr,ae_category_rec.currency_code), l_accounted_amount_len,'DATA'));
476
477 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD('-', l_line_length , '-' ));
478 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD(' ',l_line_length,' '));
479 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD(' ',l_line_length,' '));
480
481 END LOOP;
482
483 --For Unprocessed entries
484 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,okl_accounting_util.get_message_token('OKL_LP_GL_TRANSFER','OKL_ERROR_LOG'));
485 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'---------');
486 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD(' ',l_line_length,' '));
487
488 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,okl_accounting_util.get_message_token('OKL_LP_GL_TRANSFER','OKL_INVALID_ACCOUNT'));
489 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'---------------');
490 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD(' ',l_line_length,' '));
491
492 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,header_report1_rec.transaction_date||
493 header_report1_rec.contract_number||
494 header_report1_rec.transaction_number ||
495 header_report1_rec.transaction_line_number ||
496 header_report1_rec.accounting_date ||
497 header_report1_rec.dr_cr_flag ||
498 header_report1_rec.accounted_amount ||
499 header_report1_rec.currency);
500
501
502 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,header_report2_rec.transaction_date||
503 header_report2_rec.contract_number||
504 header_report2_rec.transaction_number ||
505 header_report2_rec.transaction_line_number ||
506 header_report2_rec.accounting_date||
507 header_report2_rec.dr_cr_flag ||
508 header_report2_rec.accounted_amount );
509
510 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD('-', l_line_length , '-' ));
511
512 OPEN gl_error_dst_csr(p_request_id);
513
514 IF gl_error_dst_csr%NOTFOUND THEN
515 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,okl_accounting_util.get_message_token('OKL_LP_GL_TRANSFER','OKL_NO_RECORDS'));
516 END IF;
517
518 CLOSE gl_error_dst_csr;
519
520 FOR gl_error_dst_rec IN gl_error_dst_csr(p_request_id) LOOP
521
522 IF (gl_error_dst_rec.source_table='OKL_TXL_CNTRCT_LNS') THEN
523
524 OPEN cntrct_details_csr(gl_error_dst_rec.source_id) ;
525 FETCH cntrct_details_csr INTO l_temp_trx_number,
526 l_temp_contract_number ,
527 l_date_transaction_occurred,
528 l_temp_line_number;
529 CLOSE cntrct_details_csr;
530
531 ELSE
532
533 OPEN asset_details_csr(gl_error_dst_rec.source_id) ;
534 FETCH asset_details_csr INTO l_temp_trx_number ,
535 l_temp_contract_number ,
536 l_date_transaction_occurred,
537 l_temp_line_number;
538 CLOSE asset_details_csr;
539
540 END IF;
541
542 IF gl_error_dst_rec.accounted_dr IS NOT NULL THEN
543 l_dr_cr_flag := 'D';
544 l_accounted_amount := gl_error_dst_rec.accounted_dr;
545 END IF;
546
547 IF gl_error_dst_rec.accounted_cr IS NOT NULL THEN
548 l_dr_cr_flag := 'C';
549 l_accounted_amount := gl_error_dst_rec.accounted_cr;
550 END IF;
551
552 non_proc_report_rec.transaction_date := GET_PROPER_LENGTH(l_date_transaction_occurred,l_transaction_date_len,'DATA');
553 --PAGARG Bug 4198290 Instead of putting * substring the contract number
554 --if exceeds given length
555 non_proc_report_rec.contract_number :=
556 RPAD(SUBSTR(l_temp_contract_number,1,l_contract_num_len),l_contract_num_len,' ');
557 non_proc_report_rec.transaction_number := GET_PROPER_LENGTH(l_temp_trx_number,l_transaction_num_len,'DATA');
558 non_proc_report_rec.transaction_line_number := GET_PROPER_LENGTH(l_temp_line_number,l_transaction_line_num_len,'DATA');
559 non_proc_report_rec.accounting_date := GET_PROPER_LENGTH(gl_error_dst_rec.accounting_date,l_accounting_date_len,'DATA');
560 non_proc_report_rec.dr_cr_flag := GET_PROPER_LENGTH(l_dr_cr_flag,
561 l_dr_cr_flag_len,'DATA');
562 non_proc_report_rec.accounted_amount := GET_PROPER_LENGTH(okl_Accounting_util.format_amount(l_accounted_amount,gl_error_dst_rec.currency_code),
563 l_accounted_amount_len,'DATA');
564 --PAGARG Bug 4198290 As Account is moved to next line, no need to
565 --check length, display complete data
566 non_proc_report_rec.account := gl_error_dst_rec.account;
567 non_proc_report_rec.currency := GET_PROPER_LENGTH(gl_error_dst_rec.currency_code,l_currency_len,'DATA');
568
569 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,non_proc_report_rec.transaction_date ||
570 non_proc_report_rec.contract_number ||
571 non_proc_report_rec.transaction_number ||
572 non_proc_report_rec.transaction_line_number ||
573 non_proc_report_rec.accounting_date ||
574 non_proc_report_rec.dr_cr_flag ||
575 non_proc_report_rec.accounted_amount ||
576 non_proc_report_rec.currency);
577 --PAGARG Bug 4198290 Move account data to next line
578 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, header_report1_rec.account ||' : '|| non_proc_report_rec.account);
579
580 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, RPAD(' ', l_line_length , ' ' ));
581
582 END LOOP;
583
584 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD('-', l_line_length , '-' ));
585
586 END CREATE_REPORT;
587
588
589 PROCEDURE OKL_GL_transfer (p_errbuf OUT NOCOPY VARCHAR2
590 ,p_retcode OUT NOCOPY NUMBER
591 ,p_batch_name IN VARCHAR2
592 ,p_from_date IN VARCHAR2
593 ,p_to_date IN VARCHAR2
594 ,p_validate_account IN VARCHAR2
595 ,p_gl_transfer_mode IN VARCHAR2
596 ,p_submit_journal_import IN VARCHAR2 )
597 IS
598 BEGIN
599 --Stubbed out this procedure for SLA Uptake of periodic reversal concurrent program.
600 FND_MESSAGE.SET_NAME( application => g_app_name ,
601 name => 'OKL_OBS_GL_TRANSFER_PRG' );
602 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
603 END OKL_GL_transfer;
604
605 PROCEDURE OKL_gl_transfer_con
606 (p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
607 ,x_return_status OUT NOCOPY VARCHAR2
608 ,x_msg_count OUT NOCOPY NUMBER
609 ,x_msg_data OUT NOCOPY VARCHAR2
610 ,p_batch_name IN VARCHAR2
611 ,p_from_date IN DATE
612 ,p_to_date IN DATE
613 ,p_validate_account IN VARCHAR2
614 ,p_gl_transfer_mode IN VARCHAR2
615 ,p_submit_journal_import IN VARCHAR2
616 ,x_request_id OUT NOCOPY NUMBER
617 )
618 AS
619 l_api_name VARCHAR2(30) := 'OKL_GL_TRANSFER_CON';
620 l_api_version NUMBER := 1.0;
621 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
622 l_from_date VARCHAR2(20) ;
623 l_to_date VARCHAR2(20) ;
624
625 BEGIN
626
627 x_return_status := OKL_API.G_RET_STS_SUCCESS;
628
629 l_return_status := OKL_API.start_activity(l_api_name
630 ,G_PKG_NAME
631 ,p_init_msg_list
632 ,l_api_version
633 ,l_api_version
634 ,'_PVT'
635 ,x_return_status);
636
637 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
638 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
639 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
640 RAISE OKL_API.G_EXCEPTION_ERROR;
641 END IF;
642
643 -- Added these validations to make sure the values are passed
644 -- Added by Saran on 28, Jan 2002
645
646 -- check for period name before submitting the request.
647 IF (p_to_date IS NULL) OR (p_to_date = Okl_Api.G_MISS_DATE) THEN
648 OKL_API.set_message('OKC', G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'To Date');
649 RAISE OKL_API.G_EXCEPTION_ERROR;
650 END IF;
651
652 -- check for validate account before submitting the request.
653 IF (p_validate_account IS NULL) OR (p_validate_account = Okl_Api.G_MISS_CHAR) THEN
654 OKL_API.set_message('OKC', G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'Validate Account');
655 RAISE Okl_Api.G_EXCEPTION_ERROR;
656 END IF;
657
658
659 -- check for GL Transfer Mode before submitting the request.
660 IF (p_gl_transfer_mode IS NULL) OR (p_gl_transfer_mode = Okl_Api.G_MISS_CHAR) THEN
661 OKL_API.set_message('OKC', G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'GL Transfer Mode');
662 RAISE Okl_Api.G_EXCEPTION_ERROR;
663 END IF;
664
665
666 -- check for submit journal import before submitting the request.
667 IF (p_submit_journal_import IS NULL) OR (p_submit_journal_import = Okl_Api.G_MISS_CHAR) THEN
668 OKL_API.set_message('OKC', G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'Submit Journal Import');
669 RAISE Okl_Api.G_EXCEPTION_ERROR;
670 END IF;
671
672 l_from_date := FND_DATE.DATE_TO_CANONICAL(p_from_date);
673 l_to_date := FND_DATE.DATE_TO_CANONICAL(p_to_date);
674
675
676 --call to okl gl transfer concurrent program
677 FND_REQUEST.set_org_id(mo_global.get_current_org_id); --MOAC- Concurrent request
678 x_request_id := Fnd_Request.SUBMIT_REQUEST
679 (application => 'OKL'
680 ,program => 'OKLGLINT'
681 ,argument1 => p_batch_name
682 ,argument2 => l_from_date
683 ,argument3 => l_to_date
684 ,argument4 => p_validate_account
685 ,argument5 => p_gl_transfer_mode
686 ,argument6 => p_submit_journal_import
687 );
688
689
690 -- Added these validations to check to see if the request has been submitted successfully.
691 -- Added by Saran on 28, Jan 2002
692
693 IF x_request_id = 0 THEN
694 OKL_API.set_message(p_app_name => 'OFA',
695 p_msg_name => 'FA_DEPRN_TAX_ERROR',
696 p_token1 => 'REQUEST_ID',
697 p_token1_value => x_request_id);
698
699 RAISE okl_api.g_exception_error;
700 END IF;
701
702 OKL_API.end_activity(x_msg_count, x_msg_data);
703
704 EXCEPTION
705 WHEN OKL_API.G_EXCEPTION_ERROR THEN
706 x_return_status := Okl_Api.handle_exceptions(l_api_name
707 ,g_pkg_name
708 ,'OKL_API.G_RET_STS_ERROR'
709 ,x_msg_count
710 ,x_msg_data
711 ,'_PVT');
712 WHEN okl_api.g_exception_unexpected_error THEN
713 x_return_status := OKL_API.HANDLE_EXCEPTIONS(l_api_name
714 ,g_pkg_name
715 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
716 ,x_msg_count
717 ,x_msg_data
718 ,'_PVT');
719 WHEN OTHERS THEN
720 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
721 (l_api_name,
722 G_PKG_NAME,
723 'OTHERS',
724 x_msg_count,
725 x_msg_data,
726 '_PVT');
727
728 END OKL_gl_transfer_con;
729
730 END Okl_Gl_Transfer_Pvt;