It's that time of the year again, SOX audits.  There is nothing more I dread than audits; they suck all the fun out of work.  Our audit team is Ernest and Young.  A few years ago they started to require us to give them a listing of all journal entries and monthly trial balances for the year.  They take the beginning balance, roll it forward through the JE dump and make sure it matches the ending balance.  They also use it to look for any abnormalities in the numbers.  Obviously this is good for everyone expect for the poor IT guys that have to figure it out.

Get the Oracle scripts after the jump.

Each year we've had long meetings to discuss the format they need and we go back and forth with our vendors to come up with the report in the right format.  For some reason, E&Y doesn't keep a repository of the big financial systems out there to just give us the scripts to run.  Last year we had to run the reports for Solomon and Oracle Financials.  We fined tuned the scripts until it was exactly what they were looking for.  We then wrote a web app that allows us to run these on demand.  The weeks/months of torture is now just 2 mouse clicks away for us.  It's so easy we run them for fun each week (just kidding).

I've decided to make the world an easier place for the overworked IT folks.  Below are the two queries you need to create the GL file and the JE file.  Replace the year of 2008 to whatever year you need.  Instead of spending $1,000's a year on having someone write these for you, use the ones here and donate that money to "Talk Like A Pirate Day Foundation."

I offer no warranties and take no responsibility with the scripts below.  They work for us but you're bits might be wired differently...

Journal Entry File:

select replace(replace(replace(gjh.name, chr(13), chr(32)), chr(10), chr(32)), ',', '_') JOURNAL_NUMBER,
    gcc.concatenated_segments gl_account_number,
    NVL (gjl.accounted_dr, 0) - nvl(gjl.accounted_cr,0) amount,
    gcc.segment1 business_unit, gjl.effective_date effective_date,
    gjl.period_name period_name, gjl.creation_date entry_date,
    gjl.created_by preparer_id, gjh.je_source SOURCE,
    replace(replace(replace(gjl.description, chr(13), chr(32)), chr(10), chr(32)), ',', '_') DESCRIPTION,
    gl.description account_type, gcc.segment2 department,
    gjh.posted_date posted_date, gjl.code_combination_id account_id
FROM gl_je_headers gjh,
    gl_je_lines gjl,
    gl_code_combinations_kfv gcc,
    gl_lookups gl
WHERE gjh.je_header_id = gjl.je_header_id
    AND gjl.code_combination_id = gcc.code_combination_id
    AND gcc.gl_account_type = gl.lookup_code
    AND gl.lookup_type = 'ACCOUNT TYPE'
    AND gjl.status = 'P'
    AND TRUNC (gjl.effective_date) BETWEEN '1-JAN-2008' AND '31-DEC-2008'
ORDER BY gcc.concatenated_segments

GL Balance File:

SELECT ACCOUNT,
    account_type,
    sum(beginning_balance) beginning_balance,
    sum(ending_balance) ending_balance
    FROM 
    (SELECT gcc.concatenated_segments ACCOUNT,
    gl.description account_type,
    nvl(gb1.begin_balance_dr,0) - nvl(gb1.begin_balance_cr,0) beginning_balance,
    0 ending_balance
FROM gl_balances gb1,
    gl_periods gp1,
    gl_lookups gl,
    gl_code_combinations_kfv gcc
WHERE gp1.period_name = gb1.period_name
    AND gp1.period_type = gb1.period_type
    AND gp1.period_year = gb1.period_year
    AND gp1.period_num = gb1.period_num
    AND gb1.actual_flag = 'A'
    AND gb1.code_combination_id = gcc.code_combination_id
    AND gcc.gl_account_type=gl.lookup_code
    AND gl.lookup_type = 'ACCOUNT TYPE'
    AND gp1.START_DATE = TO_DATE('01-JAN-2008','DD-MON-YYYY')
UNION
SELECT gcc.concatenated_segments ACCOUNT,
    gl.description account_type,
    0 beginning_balance,
    nvl(gb1.begin_balance_dr,0) - nvl(gb1.begin_balance_cr,0) +  nvl(period_net_dr,0) - nvl(period_net_cr,0) ending_balance
FROM gl_balances gb1,
    gl_periods gp1,
    gl_lookups gl,
    gl_code_combinations_kfv gcc
WHERE gp1.period_name = gb1.period_name
    AND gp1.period_type = gb1.period_type
    AND gp1.period_year = gb1.period_year
    AND gp1.period_num = gb1.period_num
    AND gb1.actual_flag = 'A'
    AND gb1.code_combination_id = gcc.code_combination_id
    AND gcc.gl_account_type=gl.lookup_code
    AND gl.lookup_type = 'ACCOUNT TYPE'
    AND gp1.END_DATE = TO_DATE('31-DEC-2008','DD-MON-YYYY'))
GROUP BY ACCOUNT, account_type