GL OUTBOUND INTERFACE

CREATE OR REPLACE PROCEDURE APPS.XXTUI_GL_PWC_PROC(ERRBUF OUT VARCHAR2,
                                              RETCODE OUT NUMBER,
                                              P_FROM_DEFAULT_EFFECTIVE_DATE IN VARCHAR2,
                                              P_TO_DEFAULT_EFFECTIVE_DATE   IN VARCHAR2)
IS
l_file_handle    UTL_FILE.FILE_TYPE;
l_file_name      VARCHAR2(200) := 'TUI_PWC_GL_'||
                                  TO_CHAR(SYSDATE,'DDMONYYYYHH24MISS')||
                                  '.txt';
                                 
l_from_date      DATE := FND_DATE.CANONICAL_TO_DATE(P_FROM_DEFAULT_EFFECTIVE_DATE);
l_to_date        DATE := FND_DATE.CANONICAL_TO_DATE(P_TO_DEFAULT_EFFECTIVE_DATE);
                                 
BEGIN
l_file_handle := UTL_FILE.FOPEN(
                 '/Dev/devora/DEV/db/tech_st/11.1.0/appsutil/outbound/DEV_rs',
                 l_file_name,
                 'W'); ----SELECT VALUE FROM V$PARAMETER WHERE NAME='utl_file_dir' (we can get utl dir path through this api)
                
                
FOR rec_journal IN (SELECT gjb.name batch_name,
                           gjh.name journal_name,
                           gjh.date_created,
                           gjh.currency_code,
                           gjh.period_name,
                           gjl.entered_dr,
                           gjl.entered_cr,
                           gjl.accounted_dr,
                           gjl.accounted_cr,
                           gjs.user_je_source_name source_name,
                           gjc.user_je_category_name category_name,
                           gjl.je_line_num,
                           gcck.concatenated_segments code_combinations,
                           gl.name ledger_name,
                           gjh.currency_conversion_date,
                           gjh.currency_conversion_rate,
                           gjl.description,
                           gjh.posted_date
                      FROM GL_JE_BATCHES gjb,
                           GL_JE_HEADERS gjh,
                           GL_JE_LINES gjl,
                           GL_JE_SOURCES gjs,
                           GL_JE_CATEGORIES gjc,
                           GL_CODE_COMBINATIONS_KFV gcck,
                           GL_LEDGERS gl
                     WHERE 1=1--GJB.NAME = 'TUI BATCH 25MAR13'
                       AND gjb.je_batch_id = gjh.je_batch_id
                       AND gjh.je_header_id = gjl.je_header_id
                       AND gjh.je_source = gjs.je_source_name
                       AND gjh.je_category = gjc.je_category_name
                       AND gjl.code_combination_id = gcck.code_combination_id
                       AND gjh.ledger_id = gl.ledger_id
                       AND trunc(gjh.DEFAULT_EFFECTIVE_DATE)
                           BETWEEN l_from_date
                               AND l_to_date)  
LOOP


UTL_FILE.PUT_LINE(l_file_handle,              
                           rec_journal.batch_name||'|'||
                           rec_journal.journal_name||'|'||
                           rec_journal.date_created||'|'||
                           rec_journal.currency_code||'|'||
                           rec_journal.period_name||'|'||
                           rec_journal.entered_dr||'|'||
                           rec_journal.entered_cr||'|'||
                           rec_journal.accounted_dr||'|'||
                           rec_journal.accounted_cr||'|'||
                           rec_journal.source_name||'|'||
                           rec_journal.category_name||'|'||
                           rec_journal.je_line_num||'|'||
                           rec_journal.code_combinations||'|'||
                           rec_journal.ledger_name||'|'||
                           rec_journal.currency_conversion_date||'|'||
                           rec_journal.currency_conversion_rate||'|'||
                           rec_journal.description||'|'||
                           rec_journal.posted_date);

END LOOP;       
UTL_FILE.FCLOSE(l_file_handle);
END;