set termout off
/*
||  Import General Ledger balance information into the
||  GL_INTERFACE table for fiscal years 1996 and 1997.
||
||  Make sure the directory where the source flat file will
||  be located is entered in the INIT.ORA file.  This directory
||  must be on the same operating platform as the INIT.ORA file.
||  Also, in a PC environment, it must be on the same drive.
||
||  The flat file must be comma (,) delimited with one
||  record per line.  The first line of the flat file will
||  be considered a header line and its contents will be
||  discarded.  The information should be structured
||  as follows:
||
||          "A","B",C,D,E,F,G,H,I,J,K,L,M,N,O,P
||
||  where:  A = legacy system GL account value
||          B = legacy system location value
||          C = fiscal year beginning balance
||          D = period 1 amount
||          E = period 2 amount
||          F = period 3 amount
||          G = period 4 amount
||          H = period 5 amount
||          I = period 6 amount
||          J = period 7 amount
||          K = period 8 amount
||          L = period 9 amount
||          M = period 10 amount
||          N = period 11 amount
||          O = period 12 amount
||          P = adjusting period amount
||
||  Each flat file will contain one fiscal year of GL entries
||  for one company within the organization.  This script will
||  prompt for the fiscal year and the applicable company GL code.
||
*/
set termout on

set serveroutput on size 100000
set verify off

/* Get user parameters */
accept S_FILENAME char prompt 'Enter the flat file name......'
accept N_YEAR number prompt   'Enter the fiscal year (YYYY)..'
accept S_COMPANY char prompt  'Enter the company code........'

/* Main conversion processing routine */
declare
    /* Flat file management variables */
    F_FILEHANDLE    utl_file.file_type;
    S_FILEDIR       varchar2(30)        := '/usr/users/rpitts';
    S_STRING        varchar2(1000);

    /* Working variables */
    type T_READFILE is table of varchar2(20) index by binary_integer;
    T_FIELD         T_READFILE;
    N_LINE_COUNT    number;
    N_START_POS     number;
    N_LENGTH        number;
    S_NEW_ACCT      varchar2(20);
    S_NEW_LOC       varchar2(20);
    N_DEBIT         number;
    N_CREDIT        number;
    B_DEBUG_MODE    boolean := TRUE;

    /* Exception handlers */
    FATAL_ERROR     exception;
    END_OF_FILE     exception;
    SKIP_LINE       exception;

begin

    /* Open data file for reading */
    dbms_output.put_line('Reading data flat file');
    begin
        F_FILEHANDLE := utl_file.fopen(S_FILEDIR, '&&S_FILENAME', 'r');
    exception
        when utl_file.INVALID_PATH then
            dbms_output.put_line(
                'ERROR: Unable to open source file for reading');
            dbms_output.put_line(
                '.      Verify that the directory exists in the '||
                'INIT.ORA file');
            raise FATAL_ERROR;

        when utl_file.INVALID_OPERATION then
            dbms_output.put_line(
                'ERROR: Unable to open source file for reading');
            dbms_output.put_line(
                '.      Verify that the file is located in the '||
                'specified directory');
            raise FATAL_ERROR;

        when OTHERS then
            dbms_output.put_line(
                'Unhandled error when opening source file for reading');
            raise FATAL_ERROR;
    end;

    /* Discard first line of file */
    utl_file.get_line(F_FILEHANDLE, S_STRING);

    /* Line read loop */
    N_LINE_COUNT := 0;
    begin
    loop
    begin
        /* Get next line from file */
        begin
            utl_file.get_line(F_FILEHANDLE, S_STRING);
        exception
            when NO_DATA_FOUND then
                raise END_OF_FILE;
        end;
        N_LINE_COUNT := N_LINE_COUNT + 1;

        /* Skip blank lines */
        if (rtrim(ltrim(S_STRING)) IS NULL) then
            raise SKIP_LINE;
        end if;

        /* Break line into field elements */
        N_START_POS := 1;
        for N_INDEX in 1..15 loop
            N_LENGTH := instr(S_STRING,',',1,N_INDEX) - N_START_POS;
            T_FIELD(N_INDEX) := 
                rtrim(ltrim(substr(S_STRING,N_START_POS,N_LENGTH)));
            N_START_POS := N_START_POS + N_LENGTH + 1;
        end loop;
        T_FIELD(16) := substr(S_STRING,N_START_POS);

        /* Strip quotation marks from first and second fields */
        T_FIELD(1) := substr(T_FIELD(1),2,length(T_FIELD(1))-2);
        T_FIELD(2) := substr(T_FIELD(2),2,length(T_FIELD(2))-2);

        /* Convert the GL account value */
        begin
            /* Look for exception record first */
            S_NEW_LOC := NULL;
            select  new_account,
                    new_location
            into    S_NEW_ACCT,
                    S_NEW_LOC
            from    ahp_gl_account_xref
            where   old_account = lpad(T_FIELD(1),4,'0')
            and     exception_location = lpad(T_FIELD(2),4,'0');

            if (S_NEW_LOC IS NOT NULL) then
                T_FIELD(2) := S_NEW_LOC;
            end if;
        exception
            when NO_DATA_FOUND then
                begin
                    /* Exception not found.  Look for global entry */
                    S_NEW_LOC := NULL;
                    select  new_account,
                            new_location
                    into    S_NEW_ACCT,
                            S_NEW_LOC
                    from    ahp_gl_account_xref
                    where   old_account = lpad(T_FIELD(1),4,'0')
                    and     exception_location IS NULL;

                    if (S_NEW_LOC IS NOT NULL) then
                        T_FIELD(2) := S_NEW_LOC;
                    end if;
                exception
                    when NO_DATA_FOUND then
                        dbms_output.put_line(
                            'Line '||N_LINE_COUNT||': Unable to '||
                            'cross reference old GL account ('||
                            T_FIELD(1)||'-'||T_FIELD(2)||')');
                        raise SKIP_LINE;
                end;
        end;

        /* If company 01, convert the location value */
        if ('&&S_COMPANY' = '01') then
            select  decode(lpad(T_FIELD(2),4,'0'),
                        '1721', '1796',
                        '2452', '2497',
                        '1704', '1798',
                        '2415', '2499',
                        '1215', '1299',
                        '1216', '1299',
                        '1217', '1299',
                        '1218', '1299',
                        '1405', '1499',
                        '0814', '0899',
                        '0817', '0899',
                        '0818', '0899',
                        '0210', '0299',
                        '0219', '0299',
                        '0225', '0299',
                        '1829', '1896',
                        '1830', '1896',
                        '1831', '1896',
                        '1832', '1896',
                        '2416', '2498',
                        '0516', '0599',
                        '0517', '0599',
                        '0518', '0599',
                        '0519', '0599',
                        '0522', '0599',
                        '0523', '0599',
                        '0701', '0799',
                        '0711', '0799',
                        '0403', '0499',
                        '0407', '0499',
                        '0409', '0499',
                        '0509', '0598',
                        '0520', '0598',
                        '0420', '0498',
                        '0425', '0498',
                        '0426', '0498',
                        '0815', '0898',
                        '0816', '0898',
                        '0838', '0898',
                        '6083', '0898',
                        '1701', '1799',
                        '1302', '1399',
                        '1303', '1399',
                        '1304', '1399',
                        '1305', '1399',
                        '1309', '1399',
                        '1833', '1892',
                        '2801', '2899',
                        '1804', '1899',
                        '1805', '1899',
                        '1806', '1899',
                        '1717', '1797',
                        lpad(T_FIELD(2),4,'0') )
                into    S_NEW_LOC
                from    dual;
        else
            S_NEW_LOC := lpad(T_FIELD(2),4,'0');
        end if;

        /* If FY '96, insert FY '95 ending balance */
        if (&&N_YEAR = 1996) then
            if (to_number(T_FIELD(3)) = 0) then
                /* Zero amount.  Do not create journal entry */
                NULL;
            else
                /* Determine if debit or credit */
                if (to_number(T_FIELD(3)) < 0) then
                    N_DEBIT := NULL;
                    N_CREDIT := abs(to_number(T_FIELD(3)));
                else
                    N_DEBIT := to_number(T_FIELD(3));
                    N_CREDIT := NULL;
                end if;

                /* Create journal entry */
                if (B_DEBUG_MODE = FALSE) then
                    insert
                    into    gl_interface
                    (       accounting_date,
                            actual_flag,
                            created_by,
                            currency_code,
                            date_created,
                            entered_cr,
                            entered_dr,
                            reference2,
                            segment1,
                            segment2,
                            segment3,
                            segment4,
                            set_of_books_id,
                            status,
                            user_je_category_name,
                            user_je_source_name,
                            group_id
                    )
                    values
                    (       to_date('31-DEC-1995','DD-MON-YYYY'),
                            'A',
                            1,
                            'USD',
                            sysdate,
                            N_CREDIT,
                            N_DEBIT,
                            'Conversion',
                            '&&S_COMPANY',
                            S_NEW_LOC,
                            S_NEW_ACCT,
                            '0000',
                            1,
                            'NEW',
                            'Adjustment',
                            'Conversion',
                            199513
                    );
                end if;
            end if;
        end if;

        /* Insert period 1-12 amounts */
        for N_INDEX in 1..12 loop

            if (to_number(T_FIELD(N_INDEX+3)) = 0) then
                /* Zero amount.  Do not create journal entry */
                NULL;
            else
                /* Determine if debit or credit */
                if (to_number(T_FIELD(N_INDEX+3)) < 0) then
                    N_DEBIT := NULL;
                    N_CREDIT := abs(to_number(T_FIELD(N_INDEX+3)));
                else
                    N_DEBIT := to_number(T_FIELD(N_INDEX+3));
                    N_CREDIT := NULL;
                end if;

                /* Create journal entry */
                if (B_DEBUG_MODE = FALSE) then
                    insert
                    into    gl_interface
                    (       accounting_date,
                            actual_flag,
                            created_by,
                            currency_code,
                            date_created,
                            entered_cr,
                            entered_dr,
                            reference2,
                            segment1,
                            segment2,
                            segment3,
                            segment4,
                            set_of_books_id,
                            status,
                            user_je_category_name,
                            user_je_source_name,
                            group_id
                    )
                    values
                    (       last_day(to_date(
                                '01-'||to_char(N_INDEX,'09')||
                                to_char(&&N_YEAR,'9999'),
                                'DD-MM-YYYY'))-1,
                            'A',
                            1,
                            'USD',
                            sysdate,
                            N_CREDIT,
                            N_DEBIT,
                            'Conversion',
                            '&&S_COMPANY',
                            S_NEW_LOC,
                            S_NEW_ACCT,
                            '0000',
                            1,
                            'NEW',
                            'Adjustment',
                            'Conversion',
                            ((&&N_YEAR*100)+N_INDEX)
                    );
                end if;
            end if;
        end loop;

        /* Insert adjusting period amount */
        if (to_number(T_FIELD(16)) = 0) then
            /* Zero amount.  Do not create journal entry */
            NULL;
        else
            /* Determine if debit or credit */
            if (to_number(T_FIELD(16)) < 0) then
                N_DEBIT := NULL;
                N_CREDIT := abs(to_number(T_FIELD(16)));
            else
                N_DEBIT := to_number(T_FIELD(16));
                N_CREDIT := NULL;
            end if;

            /* Create journal entry */
            if (B_DEBUG_MODE = FALSE) then
                insert
                into    gl_interface
                (       accounting_date,
                        actual_flag,
                        created_by,
                        currency_code,
                        date_created,
                        entered_cr,
                        entered_dr,
                        reference2,
                        segment1,
                        segment2,
                        segment3,
                        segment4,
                        set_of_books_id,
                        status,
                        user_je_category_name,
                        user_je_source_name,
                        group_id
                )
                values
                (       to_date('31-DEC-'||
                            to_char(&&N_YEAR,'9999'),'DD-MON-YYYY'),
                        'A',
                        1,
                        'USD',
                        sysdate,
                        N_CREDIT,
                        N_DEBIT,
                        'Conversion',
                        '&&S_COMPANY',
                        S_NEW_LOC,
                        S_NEW_ACCT,
                        '0000',
                        1,
                        'NEW',
                        'Adjustment',
                        'Conversion',
                        ((&&N_YEAR*100)+13)
                );
            end if;
        end if;

    exception
        when SKIP_LINE then
            NULL;
    end;
    end loop;
    exception
        when END_OF_FILE then
            dbms_output.put_line(N_LINE_COUNT||' lines read');
    end;
    /* End of line read loop */

    /* Close file */
    utl_file.fclose(F_FILEHANDLE);

exception
    when FATAL_ERROR then
        rollback;
        utl_file.fclose(F_FILEHANDLE);
        dbms_output.put_line('Conversion process terminated');

    when OTHERS then
        dbms_output.put_line('Error at line '||N_LINE_COUNT);
        raise;
end;
/

set verify on
set serveroutput off
