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