[Home] [Help]
PACKAGE BODY: APPS.GL_PROCURE_TO_PAY
Source
1 PACKAGE BODY GL_PROCURE_TO_PAY AS
2 /* $Header: gluprocb.pls 120.2 2006/03/22 05:50:05 adesu ship $ */
3
4 --
5 -- PUBLIC FUNCTIONS
6 --
7
8 --
9 -- Procedure
10 -- export_from_gl_interface
11 -- Purpose
12 -- Export all data from GL_INTERFACE into a flat file and purge GL_INTERFACE.
13 -- (details in the package specifications).
14 -- History
15 -- 04-18-01 O Monnier Created
16 -- Arguments
17 -- x_filename The file name
18 -- x_dir The directory
19 -- x_output_type The output type (TEXT or XML)
20 PROCEDURE export_from_gl_interface( x_filename VARCHAR2,
21 x_dir VARCHAR2,
22 x_output_type VARCHAR2)
23 IS
24 CURSOR c_gl_interface IS
25 SELECT TRANSLATE(i.status,',',' ') AS status,
26 LTRIM(TO_CHAR(i.set_of_books_id,'999999999999999')) AS set_of_books_id,
27 TO_CHAR(i.accounting_date,'YYYY/MM/DD') AS accounting_date,
28 TRANSLATE(i.currency_code,',',' ') AS currency_code,
29 TO_CHAR(i.date_created,'YYYY/MM/DD') AS date_created,
30 LTRIM(TO_CHAR(i.created_by,'999999999999999')) AS created_by,
31 i.actual_flag AS actual_flag,
32 TRANSLATE(i.user_je_category_name,',',' ') AS user_je_category_name,
33 TRANSLATE(i.user_je_source_name,',',' ') AS user_je_source_name,
34 TO_CHAR(i.currency_conversion_date,'YYYY/MM/DD') AS currency_conversion_date,
35 LTRIM(TO_CHAR(i.encumbrance_type_id,'999999999999999')) AS encumbrance_type_id,
36 LTRIM(TO_CHAR(i.budget_version_id,'999999999999999')) AS budget_version_id,
37 TRANSLATE(i.user_currency_conversion_type,',',' ') AS user_currency_conversion_type,
38 LTRIM(TO_CHAR(i.currency_conversion_rate,'999999999999999999999.999999999999')) AS currency_conversion_rate,
39 TRANSLATE(i.originating_bal_seg_value,',',' ') AS originating_bal_seg_value,
40 TRANSLATE(cc.segment1,',',' ') AS segment1,
41 TRANSLATE(cc.segment2,',',' ') AS segment2,
42 TRANSLATE(cc.segment3,',',' ') AS segment3,
43 TRANSLATE(cc.segment4,',',' ') AS segment4,
44 TRANSLATE(cc.segment5,',',' ') AS segment5,
45 LTRIM(TO_CHAR(i.entered_dr,'999999999999999999999.999999999999')) AS entered_dr,
46 LTRIM(TO_CHAR(i.entered_cr,'999999999999999999999.999999999999')) AS entered_cr,
47 LTRIM(TO_CHAR(i.accounted_dr,'999999999999999999999.999999999999')) AS accounted_dr,
48 LTRIM(TO_CHAR(i.accounted_cr,'999999999999999999999.999999999999')) AS accounted_cr,
49 TRANSLATE(i.reference1,',',' ') AS batch_name,
50 TRANSLATE(i.reference2,',',' ') AS batch_description,
51 TRANSLATE(i.reference3,',',' ') AS dual_currency_rate,
52 TRANSLATE(i.reference4,',',' ') AS journal_name,
53 TRANSLATE(i.reference5,',',' ') AS journal_description,
54 TRANSLATE(i.reference6,',',' ') AS journal_reference,
55 TRANSLATE(i.reference7,',',' ') AS journal_reversal_flag,
56 TRANSLATE(i.reference8,',',' ') AS journal_reversal_period,
57 TRANSLATE(i.reference9,',',' ') AS journal_reversal_method,
58 TRANSLATE(i.reference10,',',' ') AS line_description,
59 TRANSLATE(i.reference21,',',' ') AS line_reference1,
60 TRANSLATE(i.reference22,',',' ') AS line_reference2,
61 TRANSLATE(i.reference23,',',' ') AS line_reference3,
62 TRANSLATE(i.reference24,',',' ') AS line_reference4,
63 TRANSLATE(i.reference25,',',' ') AS line_reference5,
64 TRANSLATE(i.reference26,',',' ') AS line_reference6,
65 TRANSLATE(i.reference27,',',' ') AS line_reference7,
66 TRANSLATE(i.reference28,',',' ') AS line_reference8,
67 TRANSLATE(i.reference29,',',' ') AS line_reference9,
68 TRANSLATE(i.reference30,',',' ') AS line_reference10,
69 LTRIM(TO_CHAR(i.stat_amount,'999999999999999999999.999999999999')) AS stat_amount,
70 LTRIM(TO_CHAR(i.group_id,'999999999999999')) AS group_id,
71 LTRIM(TO_CHAR(i.subledger_doc_sequence_id,'999999999999999')) AS subledger_doc_sequence_id,
72 LTRIM(TO_CHAR(i.subledger_doc_sequence_value,'999999999999999999999.999999999999')) AS subledger_doc_sequence_value,
73 TRANSLATE(i.ussgl_transaction_code,',',' ') AS ussgl_transaction_code,
74 TRANSLATE(i.jgzz_recon_ref,',',' ') AS jgzz_recon_ref,
75 LTRIM(TO_CHAR(i.gl_sl_link_id,'999999999999999')) AS gl_sl_link_id,
76 TRANSLATE(i.gl_sl_link_table,',',' ') AS gl_sl_link_table,
77 TRANSLATE(s.name,',',' ') AS set_of_books_name,
78 TRANSLATE(f.id_flex_structure_code,',',' ') AS id_flex_structure_code,
79 LTRIM(TO_CHAR(i.code_combination_id,'999999999999999')) AS code_combination_id,
80 cc.account_type AS account_type,
81 cc.enabled_flag AS enabled_flag,
82 cc.summary_flag AS summary_flag,
83 TRANSLATE(s.period_set_name,',',' ') AS period_set_name,
84 TRANSLATE(d.period_name,',',' ') AS period_name,
85 LTRIM(TO_CHAR(p.period_year,'999999999999999')) AS period_year,
86 LTRIM(TO_CHAR(p.period_num,'999999999999999')) AS period_num,
87 LTRIM(TO_CHAR(p.quarter_num,'999999999999999')) AS quarter_num,
88 LTRIM(TO_CHAR(i.ledger_id,'999999999999999')) AS ledger_id
89 FROM GL_INTERFACE i,
90 GL_SETS_OF_BOOKS s,
91 FND_ID_FLEX_STRUCTURES f,
92 GL_DATE_PERIOD_MAP d,
93 GL_PERIODS p,
94 GL_CODE_COMBINATIONS cc
95 WHERE f.id_flex_num = s.chart_of_accounts_id
96 AND f.id_flex_code = 'GL#'
97 AND f.application_id = 101
98 AND p.period_name = d.period_name
99 AND p.period_set_name = d.period_set_name
100 AND d.accounting_date = trunc(i.accounting_date)
101 AND d.period_type = s.accounted_period_type
102 AND d.period_set_name = s.period_set_name
103 AND s.set_of_books_id = decode(i.ledger_id, -1, i.set_of_books_id, i.ledger_id)
104 AND cc.code_combination_id (+) = i.code_combination_id
105 AND i.request_id + 0 = -2;
106
107 p_gl_interface c_gl_interface%ROWTYPE;
108 OUT_FNAME VARCHAR2(255); -- file name
109 TEMP_DIR VARCHAR2(255); -- directory
110 F_OUT UTL_FILE.FILE_TYPE; -- file handle
111 MAX_LINESIZE BINARY_INTEGER := 32767; -- maximum size for each line in bytes
112 v_num_line_exported NUMBER := 0; -- number of lines exported to the file
113 v_num_line_in_table NUMBER := 0; -- number of lines in the table
114 v_num_line_deleted NUMBER := 0; -- number of lines deleted
115 user_error VARCHAR2(255); -- to store translated file_error
116 FOREIGN_KEY_ERROR EXCEPTION;
117 DELETE_ERROR EXCEPTION;
118
119 BEGIN
120 --
121 -- Default the parameters
122 --
123 OUT_FNAME := x_filename;
124 TEMP_DIR := x_dir;
125
126 IF (OUT_FNAME IS NULL) THEN
127 IF (UPPER(x_output_type) = 'XML') THEN
128 OUT_FNAME := 'default.xml';
129 ELSE
130 OUT_FNAME := 'default.out';
131 END IF;
132 END IF;
133
134 IF (TEMP_DIR IS NULL) THEN
135 -- Use first entry of the 'utl_file_dir' parameter as the TEMP_DIR
136 SELECT SUBSTRB(TRANSLATE(LTRIM(value),',',' '),
137 1,
138 INSTR( TRANSLATE(LTRIM(value),',',' ')||' ' ,' ') - 1)
139 INTO TEMP_DIR
140 FROM v$parameter
141 WHERE name = 'utl_file_dir';
142
143 -- Default the directory to the current directory if any directory is accessible.
144 IF (TEMP_DIR = '*') THEN
145 TEMP_DIR := '.';
146 END IF;
147
148 -- Raise an exception if no directory is specified in 'utl_file_dir'.
149 IF ( TEMP_DIR IS NULL ) THEN
150 RAISE UTL_FILE.INVALID_PATH;
151 END IF;
152 END IF;
153
154 --
155 -- Open and close file to use the workaround for bug
156 --
157 F_OUT := UTL_FILE.FOPEN(TEMP_DIR, OUT_FNAME, 'a');
158 BEGIN
159 UTL_FILE.FCLOSE(F_OUT);
160 EXCEPTION
161 WHEN OTHERS THEN
162 NULL;
163 END;
164
165 --
166 -- Opening the file handle in APPEND mode
167 --
168 F_OUT := UTL_FILE.FOPEN(TEMP_DIR, OUT_FNAME, 'a', MAX_LINESIZE);
169
170 -- Mark all the data in the GL_INTERFACE table
171 UPDATE GL_INTERFACE
172 SET request_id = -2;
173
174 -- Count the number of rows in the table
175 v_num_line_in_table := SQL%ROWCOUNT;
176
177 --
178 -- Insert all data into GL_INTERFACE_HISTORY as a back-up.
179 --
180 -- Bug Fix 5056457. Replaced the i.set_of_books_id with
181 -- decode(i.ledger_id, -1, i.set_of_books_id, i.ledger_id)
182 -- in the where clause, as either column could be used to
183 -- store ledger id.
184
185 INSERT INTO GL_INTERFACE_HISTORY(status,
186 set_of_books_id,
187 accounting_date,
188 currency_code,
189 date_created,
190 created_by,
191 actual_flag,
192 user_je_category_name,
193 user_je_source_name,
194 encumbrance_type_id,
195 budget_version_id,
196 currency_conversion_date,
197 user_currency_conversion_type,
198 currency_conversion_rate,
199 average_journal_flag,
200 originating_bal_seg_value,
201 segment1,
202 segment2,
203 segment3,
204 segment4,
205 segment5,
206 segment6,
207 segment7,
208 segment8,
209 segment9,
210 segment10,
211 segment11,
212 segment12,
213 segment13,
214 segment14,
215 segment15,
216 segment16,
217 segment17,
218 segment18,
219 segment19,
220 segment20,
221 segment21,
222 segment22,
223 segment23,
224 segment24,
225 segment25,
226 segment26,
227 segment27,
228 segment28,
229 segment29,
230 segment30,
231 entered_dr,
232 entered_cr,
233 accounted_dr,
234 accounted_cr,
235 transaction_date,
236 reference1,
237 reference2,
238 reference3,
239 reference4,
240 reference5,
241 reference6,
242 reference7,
243 reference8,
244 reference9,
245 reference10,
246 reference11,
247 reference12,
248 reference13,
249 reference14,
250 reference15,
251 reference16,
252 reference17,
253 reference18,
254 reference19,
255 reference20,
256 reference21,
257 reference22,
258 reference23,
259 reference24,
260 reference25,
261 reference26,
262 reference27,
263 reference28,
264 reference29,
265 reference30,
266 je_batch_id,
267 period_name,
268 je_header_id,
269 je_line_num,
270 chart_of_accounts_id,
271 functional_currency_code,
272 code_combination_id,
273 date_created_in_gl,
274 warning_code,
275 status_description,
276 stat_amount,
277 group_id,
278 request_id,
279 subledger_doc_sequence_id,
280 subledger_doc_sequence_value,
281 attribute1,
282 attribute2,
283 attribute3,
284 attribute4,
285 attribute5,
286 attribute6,
287 attribute7,
288 attribute8,
289 attribute9,
290 attribute10,
291 attribute11,
292 attribute12,
293 attribute13,
294 attribute14,
295 attribute15,
296 attribute16,
297 attribute17,
298 attribute18,
299 attribute19,
300 attribute20,
301 context,
302 context2,
303 invoice_date,
304 tax_code,
305 invoice_identifier,
306 invoice_amount,
307 context3,
308 ussgl_transaction_code,
309 descr_flex_error_message,
310 ledger_id)
311 SELECT i.status,
312 i.set_of_books_id,
313 i.accounting_date,
314 i.currency_code,
315 i.date_created,
316 i.created_by,
317 i.actual_flag,
318 i.user_je_category_name,
319 i.user_je_source_name,
320 i.encumbrance_type_id,
321 i.budget_version_id,
322 i.currency_conversion_date,
323 i.user_currency_conversion_type,
324 i.currency_conversion_rate,
325 i.average_journal_flag,
326 i.originating_bal_seg_value,
327 i.segment1,
328 i.segment2,
329 i.segment3,
330 i.segment4,
331 i.segment5,
332 i.segment6,
333 i.segment7,
334 i.segment8,
335 i.segment9,
336 i.segment10,
337 i.segment11,
338 i.segment12,
339 i.segment13,
340 i.segment14,
341 i.segment15,
342 i.segment16,
343 i.segment17,
344 i.segment18,
345 i.segment19,
346 i.segment20,
347 i.segment21,
348 i.segment22,
349 i.segment23,
350 i.segment24,
351 i.segment25,
352 i.segment26,
353 i.segment27,
354 i.segment28,
355 i.segment29,
356 i.segment30,
357 i.entered_dr,
358 i.entered_cr,
359 i.accounted_dr,
360 i.accounted_cr,
361 i.transaction_date,
362 i.reference1,
363 i.reference2,
364 i.reference3,
365 i.reference4,
366 i.reference5,
367 i.reference6,
368 i.reference7,
369 i.reference8,
370 i.reference9,
371 i.reference10,
372 i.reference11,
373 i.reference12,
374 i.reference13,
375 i.reference14,
376 i.reference15,
377 i.reference16,
378 i.reference17,
379 i.reference18,
380 i.reference19,
381 i.reference20,
382 i.reference21,
383 i.reference22,
384 i.reference23,
385 i.reference24,
386 i.reference25,
387 i.reference26,
388 i.reference27,
389 i.reference28,
390 i.reference29,
391 i.reference30,
392 i.je_batch_id,
393 i.period_name,
394 i.je_header_id,
395 i.je_line_num,
396 i.chart_of_accounts_id,
397 i.functional_currency_code,
398 i.code_combination_id,
399 i.date_created_in_gl,
400 i.warning_code,
401 i.status_description,
402 i.stat_amount,
403 i.group_id,
404 i.request_id,
405 i.subledger_doc_sequence_id,
406 i.subledger_doc_sequence_value,
407 i.attribute1,
408 i.attribute2,
409 i.attribute3,
410 i.attribute4,
411 i.attribute5,
412 i.attribute6,
413 i.attribute7,
414 i.attribute8,
415 i.attribute9,
416 i.attribute10,
417 i.attribute11,
418 i.attribute12,
419 i.attribute13,
420 i.attribute14,
421 i.attribute15,
422 i.attribute16,
423 i.attribute17,
424 i.attribute18,
425 i.attribute19,
426 i.attribute20,
427 i.context,
428 i.context2,
429 i.invoice_date,
430 i.tax_code,
431 i.invoice_identifier,
432 i.invoice_amount,
433 i.context3,
434 i.ussgl_transaction_code,
435 i.descr_flex_error_message,
436 i.ledger_id
437 FROM GL_INTERFACE i,
438 GL_SETS_OF_BOOKS s,
439 FND_ID_FLEX_STRUCTURES f,
440 GL_DATE_PERIOD_MAP d,
441 GL_PERIODS p
442 WHERE f.id_flex_num = s.chart_of_accounts_id
443 AND f.id_flex_code = 'GL#'
444 AND f.application_id = 101
445 AND p.period_name = d.period_name
446 AND p.period_set_name = d.period_set_name
447 AND d.accounting_date = trunc(i.accounting_date)
448 AND d.period_type = s.accounted_period_type
449 AND d.period_set_name = s.period_set_name
450 AND s.set_of_books_id = decode(i.ledger_id, -1, i.set_of_books_id, i.ledger_id)
451 AND i.request_id + 0 = -2;
452
453 -- Count the number of rows that would be retrieved by the cursor
454 -- We need to count this number before writing to the file, because
455 -- we cannot delete any data from the file afterwards.
456 v_num_line_exported := SQL%ROWCOUNT;
457
458 --
459 -- Abort if some lines would not be processed because some foreign
460 -- key information is missing.
461 --
462 IF (v_num_line_exported <> v_num_line_in_table) THEN
463 RAISE FOREIGN_KEY_ERROR;
464 END IF;
465
466 --
467 -- Write Data to the file
468 --
469 IF (UPPER(x_output_type) = 'XML') THEN
470
471 --
472 -- XML format
473 --
474 -- Bug Fix 5056457. Added code to insert ledger_id field in the xml/text file.
475
476 UTL_FILE.PUT_LINE (F_OUT,'<?xml version="1.0"?>');
477 UTL_FILE.FFLUSH(F_OUT);
478 UTL_FILE.PUT_LINE (F_OUT,'<GlInterfaceExport>');
479 UTL_FILE.FFLUSH(F_OUT);
480
481 FOR p_gl_interface IN c_gl_interface LOOP
482 UTL_FILE.PUT_LINE (F_OUT,' <GlInterface>');
483 UTL_FILE.FFLUSH(F_OUT);
484 UTL_FILE.PUT_LINE (F_OUT,' <Status>'||p_gl_interface.status||'</Status>');
485 UTL_FILE.FFLUSH(F_OUT);
486 UTL_FILE.PUT_LINE (F_OUT,' <SetOfBooksId>'||p_gl_interface.set_of_books_id||'</SetOfBooksId>');
487 UTL_FILE.FFLUSH(F_OUT);
488 UTL_FILE.PUT_LINE (F_OUT,' <AccountingDate>'||p_gl_interface.accounting_date||'</AccountingDate>');
489 UTL_FILE.FFLUSH(F_OUT);
490 UTL_FILE.PUT_LINE (F_OUT,' <CurrencyCode>'||p_gl_interface.currency_code||'</CurrencyCode>');
491 UTL_FILE.FFLUSH(F_OUT);
492 UTL_FILE.PUT_LINE (F_OUT,' <DateCreated>'||p_gl_interface.date_created||'</DateCreated>');
493 UTL_FILE.FFLUSH(F_OUT);
494 UTL_FILE.PUT_LINE (F_OUT,' <CreatedBy>'||p_gl_interface.created_by||'</CreatedBy>');
495 UTL_FILE.FFLUSH(F_OUT);
496 UTL_FILE.PUT_LINE (F_OUT,' <ActualFlag>'||p_gl_interface.actual_flag||'</ActualFlag>');
497 UTL_FILE.FFLUSH(F_OUT);
498 UTL_FILE.PUT_LINE (F_OUT,' <UserJeCategoryName>'||p_gl_interface.user_je_category_name||'</UserJeCategoryName>');
499 UTL_FILE.FFLUSH(F_OUT);
500 UTL_FILE.PUT_LINE (F_OUT,' <UserJeSourceName>'||p_gl_interface.user_je_source_name||'</UserJeSourceName>');
501 UTL_FILE.FFLUSH(F_OUT);
502 UTL_FILE.PUT_LINE (F_OUT,' <CurrencyConversionDate>'||p_gl_interface.currency_conversion_date||'</CurrencyConversionDate>');
503 UTL_FILE.FFLUSH(F_OUT);
504 UTL_FILE.PUT_LINE (F_OUT,' <EncumbranceTypeId>'||p_gl_interface.encumbrance_type_id||'</EncumbranceTypeId>');
505 UTL_FILE.FFLUSH(F_OUT);
506 UTL_FILE.PUT_LINE (F_OUT,' <BudgetVersionId>'||p_gl_interface.budget_version_id||'</BudgetVersionId>');
507 UTL_FILE.FFLUSH(F_OUT);
508 UTL_FILE.PUT_LINE (F_OUT,' <UserCurrencyConversionType>'||p_gl_interface.user_currency_conversion_type||'</UserCurrencyConversionType>');
509 UTL_FILE.FFLUSH(F_OUT);
510 UTL_FILE.PUT_LINE (F_OUT,' <CurrencyConversionRate>'||p_gl_interface.currency_conversion_rate||'</CurrencyConversionRate>');
511 UTL_FILE.FFLUSH(F_OUT);
512 UTL_FILE.PUT_LINE (F_OUT,' <OriginatingBalSegValue>'||p_gl_interface.originating_bal_seg_value||'</OriginatingBalSegValue>');
513 UTL_FILE.FFLUSH(F_OUT);
514 UTL_FILE.PUT_LINE (F_OUT,' <Segment1>'||p_gl_interface.segment1||'</Segment1>');
515 UTL_FILE.FFLUSH(F_OUT);
516 UTL_FILE.PUT_LINE (F_OUT,' <Segment2>'||p_gl_interface.segment2||'</Segment2>');
517 UTL_FILE.FFLUSH(F_OUT);
518 UTL_FILE.PUT_LINE (F_OUT,' <Segment3>'||p_gl_interface.segment3||'</Segment3>');
519 UTL_FILE.FFLUSH(F_OUT);
520 UTL_FILE.PUT_LINE (F_OUT,' <Segment4>'||p_gl_interface.segment4||'</Segment4>');
521 UTL_FILE.FFLUSH(F_OUT);
522 UTL_FILE.PUT_LINE (F_OUT,' <Segment5>'||p_gl_interface.segment5||'</Segment5>');
523 UTL_FILE.FFLUSH(F_OUT);
524 UTL_FILE.PUT_LINE (F_OUT,' <EnteredDr>'||p_gl_interface.entered_dr||'</EnteredDr>');
525 UTL_FILE.FFLUSH(F_OUT);
526 UTL_FILE.PUT_LINE (F_OUT,' <EnteredCr>'||p_gl_interface.entered_cr||'</EnteredCr>');
527 UTL_FILE.FFLUSH(F_OUT);
528 UTL_FILE.PUT_LINE (F_OUT,' <AccountedDr>'||p_gl_interface.accounted_dr||'</AccountedDr>');
529 UTL_FILE.FFLUSH(F_OUT);
530 UTL_FILE.PUT_LINE (F_OUT,' <AccountedCr>'||p_gl_interface.accounted_cr||'</AccountedCr>');
531 UTL_FILE.FFLUSH(F_OUT);
532 UTL_FILE.PUT_LINE (F_OUT,' <BatchName>'||p_gl_interface.batch_name||'</BatchName>');
533 UTL_FILE.FFLUSH(F_OUT);
534 UTL_FILE.PUT_LINE (F_OUT,' <BatchDescription>'||p_gl_interface.batch_description||'</BatchDescription>');
535 UTL_FILE.FFLUSH(F_OUT);
536 UTL_FILE.PUT_LINE (F_OUT,' <DualCurrencyRate>'||p_gl_interface.dual_currency_rate||'</DualCurrencyRate>');
537 UTL_FILE.FFLUSH(F_OUT);
538 UTL_FILE.PUT_LINE (F_OUT,' <JournalName>'||p_gl_interface.journal_name||'</JournalName>');
539 UTL_FILE.FFLUSH(F_OUT);
540 UTL_FILE.PUT_LINE (F_OUT,' <JournalDescription>'||p_gl_interface.journal_description||'</JournalDescription>');
541 UTL_FILE.FFLUSH(F_OUT);
542 UTL_FILE.PUT_LINE (F_OUT,' <JournalReference>'||p_gl_interface.journal_reference||'</JournalReference>');
543 UTL_FILE.FFLUSH(F_OUT);
544 UTL_FILE.PUT_LINE (F_OUT,' <JournalReversalFlag>'||p_gl_interface.journal_reversal_flag||'</JournalReversalFlag>');
545 UTL_FILE.FFLUSH(F_OUT);
546 UTL_FILE.PUT_LINE (F_OUT,' <JournalReversalPeriod>'||p_gl_interface.journal_reversal_period||'</JournalReversalPeriod>');
547 UTL_FILE.FFLUSH(F_OUT);
548 UTL_FILE.PUT_LINE (F_OUT,' <JournalReversalMethod>'||p_gl_interface.journal_reversal_method||'</JournalReversalMethod>');
549 UTL_FILE.FFLUSH(F_OUT);
550 UTL_FILE.PUT_LINE (F_OUT,' <LineDescription>'||p_gl_interface.line_description||'</LineDescription>');
551 UTL_FILE.FFLUSH(F_OUT);
552 UTL_FILE.PUT_LINE (F_OUT,' <LineReference1>'||p_gl_interface.line_reference1||'</LineReference1>');
553 UTL_FILE.FFLUSH(F_OUT);
554 UTL_FILE.PUT_LINE (F_OUT,' <LineReference2>'||p_gl_interface.line_reference2||'</LineReference2>');
555 UTL_FILE.FFLUSH(F_OUT);
556 UTL_FILE.PUT_LINE (F_OUT,' <LineReference3>'||p_gl_interface.line_reference3||'</LineReference3>');
557 UTL_FILE.FFLUSH(F_OUT);
558 UTL_FILE.PUT_LINE (F_OUT,' <LineReference4>'||p_gl_interface.line_reference4||'</LineReference4>');
559 UTL_FILE.FFLUSH(F_OUT);
560 UTL_FILE.PUT_LINE (F_OUT,' <LineReference5>'||p_gl_interface.line_reference5||'</LineReference5>');
561 UTL_FILE.FFLUSH(F_OUT);
562 UTL_FILE.PUT_LINE (F_OUT,' <LineReference6>'||p_gl_interface.line_reference6||'</LineReference6>');
563 UTL_FILE.FFLUSH(F_OUT);
564 UTL_FILE.PUT_LINE (F_OUT,' <LineReference7>'||p_gl_interface.line_reference7||'</LineReference7>');
565 UTL_FILE.FFLUSH(F_OUT);
566 UTL_FILE.PUT_LINE (F_OUT,' <LineReference8>'||p_gl_interface.line_reference8||'</LineReference8>');
567 UTL_FILE.FFLUSH(F_OUT);
568 UTL_FILE.PUT_LINE (F_OUT,' <LineReference9>'||p_gl_interface.line_reference9||'</LineReference9>');
569 UTL_FILE.FFLUSH(F_OUT);
570 UTL_FILE.PUT_LINE (F_OUT,' <LineReference10>'||p_gl_interface.line_reference10||'</LineReference10>');
571 UTL_FILE.FFLUSH(F_OUT);
572 UTL_FILE.PUT_LINE (F_OUT,' <StatAmount>'||p_gl_interface.stat_amount||'</StatAmount>');
573 UTL_FILE.FFLUSH(F_OUT);
574 UTL_FILE.PUT_LINE (F_OUT,' <GroupId>'||p_gl_interface.group_id||'</GroupId>');
575 UTL_FILE.FFLUSH(F_OUT);
576 UTL_FILE.PUT_LINE (F_OUT,' <SubledgerDocSequenceId>'||p_gl_interface.subledger_doc_sequence_id||'</SubledgerDocSequenceId>');
577 UTL_FILE.FFLUSH(F_OUT);
578 UTL_FILE.PUT_LINE (F_OUT,' <SubledgerDocSequenceValue>'||p_gl_interface.subledger_doc_sequence_value||'</SubledgerDocSequenceValue>');
579 UTL_FILE.FFLUSH(F_OUT);
580 UTL_FILE.PUT_LINE (F_OUT,' <UssglTransactionCode>'||p_gl_interface.ussgl_transaction_code||'</UssglTransactionCode>');
581 UTL_FILE.FFLUSH(F_OUT);
582 UTL_FILE.PUT_LINE (F_OUT,' <JgzzReconRef>'||p_gl_interface.jgzz_recon_ref||'</JgzzReconRef>');
583 UTL_FILE.FFLUSH(F_OUT);
584 UTL_FILE.PUT_LINE (F_OUT,' <GlSlLinkId>'||p_gl_interface.gl_sl_link_id||'</GlSlLinkId>');
585 UTL_FILE.FFLUSH(F_OUT);
586 UTL_FILE.PUT_LINE (F_OUT,' <GlSlLinkTable>'||p_gl_interface.gl_sl_link_table||'</GlSlLinkTable>');
587 UTL_FILE.FFLUSH(F_OUT);
588 UTL_FILE.PUT_LINE (F_OUT,' <SetOfBooksName>'||p_gl_interface.set_of_books_name||'</SetOfBooksName>');
589 UTL_FILE.FFLUSH(F_OUT);
590 UTL_FILE.PUT_LINE (F_OUT,' <IdFlexStructureCode>'||p_gl_interface.id_flex_structure_code||'</IdFlexStructureCode>');
591 UTL_FILE.FFLUSH(F_OUT);
592 UTL_FILE.PUT_LINE (F_OUT,' <CodeCombinationId>'||p_gl_interface.code_combination_id||'</CodeCombinationId>');
593 UTL_FILE.FFLUSH(F_OUT);
594 UTL_FILE.PUT_LINE (F_OUT,' <AccountType>'||p_gl_interface.account_type||'</AccountType>');
595 UTL_FILE.FFLUSH(F_OUT);
596 UTL_FILE.PUT_LINE (F_OUT,' <EnabledFlag>'||p_gl_interface.enabled_flag||'</EnabledFlag>');
597 UTL_FILE.FFLUSH(F_OUT);
598 UTL_FILE.PUT_LINE (F_OUT,' <SummaryFlag>'||p_gl_interface.summary_flag||'</SummaryFlag>');
599 UTL_FILE.FFLUSH(F_OUT);
600 UTL_FILE.PUT_LINE (F_OUT,' <PeriodSetName>'||p_gl_interface.period_set_name||'</PeriodSetName>');
601 UTL_FILE.FFLUSH(F_OUT);
602 UTL_FILE.PUT_LINE (F_OUT,' <PeriodName>'||p_gl_interface.period_name||'</PeriodName>');
603 UTL_FILE.FFLUSH(F_OUT);
604 UTL_FILE.PUT_LINE (F_OUT,' <PeriodYear>'||p_gl_interface.period_year||'</PeriodYear>');
605 UTL_FILE.FFLUSH(F_OUT);
606 UTL_FILE.PUT_LINE (F_OUT,' <PeriodNum>'||p_gl_interface.period_num||'</PeriodNum>');
607 UTL_FILE.FFLUSH(F_OUT);
608 UTL_FILE.PUT_LINE (F_OUT,' <QuarterNum>'||p_gl_interface.quarter_num||'</QuarterNum>');
609 UTL_FILE.FFLUSH(F_OUT);
610 UTL_FILE.PUT_LINE (F_OUT,' <LedgerId>'||p_gl_interface.ledger_id||'</LedgerId>');
611 UTL_FILE.FFLUSH(F_OUT);
612 UTL_FILE.PUT_LINE (F_OUT,' </GlInterface>');
613 END LOOP;
614
615 UTL_FILE.PUT_LINE (F_OUT,'</GlInterfaceExport>');
616 UTL_FILE.FFLUSH(F_OUT);
617
618 ELSE
619
620 --
621 -- Text format
622 --
623 FOR p_gl_interface IN c_gl_interface LOOP
624 UTL_FILE.PUT (F_OUT,p_gl_interface.status||',');
625 UTL_FILE.FFLUSH(F_OUT);
626 UTL_FILE.PUT (F_OUT,p_gl_interface.set_of_books_id||',');
627 UTL_FILE.FFLUSH(F_OUT);
628 UTL_FILE.PUT (F_OUT,p_gl_interface.accounting_date||',');
629 UTL_FILE.FFLUSH(F_OUT);
630 UTL_FILE.PUT (F_OUT,p_gl_interface.currency_code||',');
631 UTL_FILE.FFLUSH(F_OUT);
632 UTL_FILE.PUT (F_OUT,p_gl_interface.date_created||',');
633 UTL_FILE.FFLUSH(F_OUT);
634 UTL_FILE.PUT (F_OUT,p_gl_interface.created_by||',');
635 UTL_FILE.FFLUSH(F_OUT);
636 UTL_FILE.PUT (F_OUT,p_gl_interface.actual_flag||',');
637 UTL_FILE.FFLUSH(F_OUT);
638 UTL_FILE.PUT (F_OUT,p_gl_interface.user_je_category_name||',');
639 UTL_FILE.FFLUSH(F_OUT);
640 UTL_FILE.PUT (F_OUT,p_gl_interface.user_je_source_name||',');
641 UTL_FILE.FFLUSH(F_OUT);
642 UTL_FILE.PUT (F_OUT,p_gl_interface.currency_conversion_date||',');
643 UTL_FILE.FFLUSH(F_OUT);
644 UTL_FILE.PUT (F_OUT,p_gl_interface.encumbrance_type_id||',');
645 UTL_FILE.FFLUSH(F_OUT);
646 UTL_FILE.PUT (F_OUT,p_gl_interface.budget_version_id||',');
647 UTL_FILE.FFLUSH(F_OUT);
648 UTL_FILE.PUT (F_OUT,p_gl_interface.user_currency_conversion_type||',');
649 UTL_FILE.FFLUSH(F_OUT);
650 UTL_FILE.PUT (F_OUT,p_gl_interface.currency_conversion_rate||',');
651 UTL_FILE.FFLUSH(F_OUT);
652 UTL_FILE.PUT (F_OUT,p_gl_interface.originating_bal_seg_value||',');
653 UTL_FILE.FFLUSH(F_OUT);
654 UTL_FILE.PUT (F_OUT,p_gl_interface.segment1||',');
655 UTL_FILE.FFLUSH(F_OUT);
656 UTL_FILE.PUT (F_OUT,p_gl_interface.segment2||',');
657 UTL_FILE.FFLUSH(F_OUT);
658 UTL_FILE.PUT (F_OUT,p_gl_interface.segment3||',');
659 UTL_FILE.FFLUSH(F_OUT);
660 UTL_FILE.PUT (F_OUT,p_gl_interface.segment4||',');
661 UTL_FILE.FFLUSH(F_OUT);
662 UTL_FILE.PUT (F_OUT,p_gl_interface.segment5||',');
663 UTL_FILE.FFLUSH(F_OUT);
664 UTL_FILE.PUT (F_OUT,p_gl_interface.entered_dr||',');
665 UTL_FILE.FFLUSH(F_OUT);
666 UTL_FILE.PUT (F_OUT,p_gl_interface.entered_cr||',');
667 UTL_FILE.FFLUSH(F_OUT);
668 UTL_FILE.PUT (F_OUT,p_gl_interface.accounted_dr||',');
669 UTL_FILE.FFLUSH(F_OUT);
670 UTL_FILE.PUT (F_OUT,p_gl_interface.accounted_cr||',');
671 UTL_FILE.FFLUSH(F_OUT);
672 UTL_FILE.PUT (F_OUT,p_gl_interface.batch_name||',');
673 UTL_FILE.FFLUSH(F_OUT);
674 UTL_FILE.PUT (F_OUT,p_gl_interface.batch_description||',');
675 UTL_FILE.FFLUSH(F_OUT);
676 UTL_FILE.PUT (F_OUT,p_gl_interface.dual_currency_rate||',');
677 UTL_FILE.FFLUSH(F_OUT);
678 UTL_FILE.PUT (F_OUT,p_gl_interface.journal_name||',');
679 UTL_FILE.FFLUSH(F_OUT);
680 UTL_FILE.PUT (F_OUT,p_gl_interface.journal_description||',');
681 UTL_FILE.FFLUSH(F_OUT);
682 UTL_FILE.PUT (F_OUT,p_gl_interface.journal_reference||',');
683 UTL_FILE.FFLUSH(F_OUT);
684 UTL_FILE.PUT (F_OUT,p_gl_interface.journal_reversal_flag||',');
685 UTL_FILE.FFLUSH(F_OUT);
686 UTL_FILE.PUT (F_OUT,p_gl_interface.journal_reversal_period||',');
687 UTL_FILE.FFLUSH(F_OUT);
688 UTL_FILE.PUT (F_OUT,p_gl_interface.journal_reversal_method||',');
689 UTL_FILE.FFLUSH(F_OUT);
690 UTL_FILE.PUT (F_OUT,p_gl_interface.line_description||',');
691 UTL_FILE.FFLUSH(F_OUT);
692 UTL_FILE.PUT (F_OUT,p_gl_interface.line_reference1||',');
693 UTL_FILE.FFLUSH(F_OUT);
694 UTL_FILE.PUT (F_OUT,p_gl_interface.line_reference2||',');
695 UTL_FILE.FFLUSH(F_OUT);
696 UTL_FILE.PUT (F_OUT,p_gl_interface.line_reference3||',');
697 UTL_FILE.FFLUSH(F_OUT);
698 UTL_FILE.PUT (F_OUT,p_gl_interface.line_reference4||',');
699 UTL_FILE.FFLUSH(F_OUT);
700 UTL_FILE.PUT (F_OUT,p_gl_interface.line_reference5||',');
701 UTL_FILE.FFLUSH(F_OUT);
702 UTL_FILE.PUT (F_OUT,p_gl_interface.line_reference6||',');
703 UTL_FILE.FFLUSH(F_OUT);
704 UTL_FILE.PUT (F_OUT,p_gl_interface.line_reference7||',');
705 UTL_FILE.FFLUSH(F_OUT);
706 UTL_FILE.PUT (F_OUT,p_gl_interface.line_reference8||',');
707 UTL_FILE.FFLUSH(F_OUT);
708 UTL_FILE.PUT (F_OUT,p_gl_interface.line_reference9||',');
709 UTL_FILE.FFLUSH(F_OUT);
710 UTL_FILE.PUT (F_OUT,p_gl_interface.line_reference10||',');
711 UTL_FILE.FFLUSH(F_OUT);
712 UTL_FILE.PUT (F_OUT,p_gl_interface.stat_amount||',');
713 UTL_FILE.FFLUSH(F_OUT);
714 UTL_FILE.PUT (F_OUT,p_gl_interface.group_id||',');
715 UTL_FILE.FFLUSH(F_OUT);
716 UTL_FILE.PUT (F_OUT,p_gl_interface.subledger_doc_sequence_id||',');
717 UTL_FILE.FFLUSH(F_OUT);
718 UTL_FILE.PUT (F_OUT,p_gl_interface.subledger_doc_sequence_value||',');
719 UTL_FILE.FFLUSH(F_OUT);
720 UTL_FILE.PUT (F_OUT,p_gl_interface.ussgl_transaction_code||',');
721 UTL_FILE.FFLUSH(F_OUT);
722 UTL_FILE.PUT (F_OUT,p_gl_interface.jgzz_recon_ref||',');
723 UTL_FILE.FFLUSH(F_OUT);
724 UTL_FILE.PUT (F_OUT,p_gl_interface.gl_sl_link_id||',');
725 UTL_FILE.FFLUSH(F_OUT);
726 UTL_FILE.PUT (F_OUT,p_gl_interface.gl_sl_link_table||',');
727 UTL_FILE.FFLUSH(F_OUT);
728 UTL_FILE.PUT (F_OUT,p_gl_interface.set_of_books_name||',');
729 UTL_FILE.FFLUSH(F_OUT);
730 UTL_FILE.PUT (F_OUT,p_gl_interface.id_flex_structure_code||',');
731 UTL_FILE.FFLUSH(F_OUT);
732 UTL_FILE.PUT (F_OUT,p_gl_interface.code_combination_id||',');
733 UTL_FILE.FFLUSH(F_OUT);
734 UTL_FILE.PUT (F_OUT,p_gl_interface.account_type||',');
735 UTL_FILE.FFLUSH(F_OUT);
736 UTL_FILE.PUT (F_OUT,p_gl_interface.enabled_flag||',');
737 UTL_FILE.FFLUSH(F_OUT);
738 UTL_FILE.PUT (F_OUT,p_gl_interface.summary_flag||',');
739 UTL_FILE.FFLUSH(F_OUT);
740 UTL_FILE.PUT (F_OUT,p_gl_interface.period_set_name||',');
741 UTL_FILE.FFLUSH(F_OUT);
742 UTL_FILE.PUT (F_OUT,p_gl_interface.period_name||',');
743 UTL_FILE.FFLUSH(F_OUT);
744 UTL_FILE.PUT (F_OUT,p_gl_interface.period_year||',');
745 UTL_FILE.FFLUSH(F_OUT);
746 UTL_FILE.PUT (F_OUT,p_gl_interface.period_num||',');
747 UTL_FILE.FFLUSH(F_OUT);
748 UTL_FILE.PUT (F_OUT,p_gl_interface.quarter_num);
749 UTL_FILE.FFLUSH(F_OUT);
750 UTL_FILE.PUT_LINE (F_OUT,p_gl_interface.ledger_id);
751 UTL_FILE.FFLUSH(F_OUT);
752 END LOOP;
753
754 END IF;
755
756 --
757 -- Closing the File Handle
758 --
759 BEGIN
760 UTL_FILE.FCLOSE(F_OUT);
761 EXCEPTION
762 WHEN OTHERS THEN
763 NULL;
764 END;
765
766 --
767 -- Clean up rows from GL_INTERFACE
768 --
769 DELETE FROM GL_INTERFACE
770 WHERE request_id + 0 = -2;
771
772 -- Check the number of rows deleted from the table
773 v_num_line_deleted := SQL%ROWCOUNT;
774
775 IF (v_num_line_exported <> v_num_line_deleted) THEN
776 RAISE DELETE_ERROR;
777 END IF;
778
779 --
780 -- Print the directory and file name in the log file
781 --
782 BEGIN
783 FND_FILE.put_line(FND_FILE.LOG,TEMP_DIR||OUT_FNAME);
784 EXCEPTION
785 WHEN OTHERS THEN
786 NULL;
787 END;
788
789 EXCEPTION
790 WHEN UTL_FILE.INVALID_PATH THEN
791 fnd_message.set_name('FND', 'CONC-FILE_ERROR');
792 fnd_message.set_token('TEMP_FILE', OUT_FNAME, FALSE);
793 user_error := substrb(fnd_message.get, 1, 255);
794
795 fnd_message.set_name('FND','CONC-TEMPFILE_INVALID_PATH');
796 fnd_message.set_token('FILE_DIR', TEMP_DIR, FALSE);
797
798 raise_application_error(-20100, user_error);
799
800 WHEN UTL_FILE.INVALID_MODE THEN
801 fnd_message.set_name('FND', 'CONC-FILE_ERROR');
802 fnd_message.set_token('TEMP_FILE', OUT_FNAME, FALSE);
803 user_error := substrb(fnd_message.get, 1, 255);
804
805 fnd_message.set_name('FND','CONC-TEMPFILE_INVALID_MODE');
806 fnd_message.set_token('TEMP_FILE', OUT_FNAME, FALSE);
807 fnd_message.set_token('FILE_MODE', 'w', FALSE);
808
809 raise_application_error(-20100, user_error);
810
811 WHEN UTL_FILE.INVALID_OPERATION THEN
812 fnd_message.set_name('FND', 'CONC-FILE_ERROR');
813 fnd_message.set_token('TEMP_FILE', OUT_FNAME, FALSE);
814 user_error := substrb(fnd_message.get, 1, 255);
815
816 fnd_message.set_name('FND','CONC-TEMPFILE_INVALID_OPERATN');
817 fnd_message.set_token('TEMP_FILE', OUT_FNAME, FALSE);
818 fnd_message.set_token('TEMP_DIR', TEMP_DIR, FALSE);
819
820 raise_application_error(-20100, user_error);
821
822 WHEN UTL_FILE.INVALID_MAXLINESIZE THEN
823 fnd_message.set_name('FND', 'CONC-FILE_ERROR');
824 fnd_message.set_token('TEMP_FILE', OUT_FNAME, FALSE);
825 user_error := substrb(fnd_message.get, 1, 255);
826
827 fnd_message.set_name('FND', 'CONC-TEMPFILE_INVALID_MAXLINESIZE');
828 fnd_message.set_token('TEMP_FILE', OUT_FNAME, FALSE);
829 fnd_message.set_token('MAXLINE', MAX_LINESIZE, FALSE);
830
831 raise_application_error(-20100, user_error);
832
833 WHEN UTL_FILE.WRITE_ERROR THEN
834 fnd_message.set_name('FND', 'CONC-FILE_ERROR');
835 fnd_message.set_token('TEMP_FILE', OUT_FNAME, FALSE);
836 user_error := substrb(fnd_message.get, 1, 255);
837
838 fnd_message.set_name('FND', 'CONC-TEMPFILE_WRITE_ERROR');
839 fnd_message.set_token('TEMP_FILE', OUT_FNAME, FALSE);
840 fnd_message.set_token('TEMP_DIR', TEMP_DIR, FALSE);
841
842 raise_application_error(-20100, user_error);
843
844 WHEN DELETE_ERROR THEN
845 fnd_message.set_name('FND', 'DELETE_ERROR');
846 raise_application_error(-20100, 'DELETE_ERROR');
847
848 WHEN FOREIGN_KEY_ERROR THEN
849 fnd_message.set_name('FND', 'FOREIGN_KEY_ERROR');
850 raise_application_error(-20100, 'FOREIGN_KEY_ERROR');
851
852 WHEN OTHERS THEN
853 app_exception.raise_exception;
854
855 END export_from_gl_interface;
856
857 --
858 -- Procedure
859 -- export_from_gl_interface
860 -- Purpose
861 -- Concurrent job version of export_from_gl_interface.
862 -- History
863 -- 04-18-01 O Monnier Created
864 -- Arguments
865 -- errbuf Standard error buffer
866 -- retcode Standard return code
867 -- x_filename The file name
868 -- x_dir The directory
869 -- x_output_type The output type (TEXT or XML)
870 PROCEDURE export_from_gl_interface( errbuf OUT NOCOPY VARCHAR2,
871 retcode OUT NOCOPY VARCHAR2,
872 x_filename IN VARCHAR2,
873 x_dir IN VARCHAR2,
874 x_output_type IN VARCHAR2 ) IS
875 BEGIN
876 DECLARE
877 l_message VARCHAR2(1000);
878 BEGIN
879 GL_PROCURE_TO_PAY.export_from_gl_interface(x_filename => x_filename,
880 x_dir => x_dir,
881 x_output_type => x_output_type);
882 EXCEPTION
883 WHEN OTHERS THEN
884 errbuf := SQLERRM ;
885 retcode := '2';
886 l_message := errbuf;
887 FND_FILE.put_line(FND_FILE.LOG,l_message);
888 app_exception.raise_exception;
889 END;
890 END export_from_gl_interface;
891
892 END GL_PROCURE_TO_PAY;