set echo off
Set Trimspool on
set feedback off
set verify off
set termout off
set numwidth 20
set space 0
set pagesize 0
set linesize 30000
/*
Oracle E-Business Suite (EBS) Version: R12
General Ledger Extraction Script
Script Version: 1.8
*/
-- *** Enter starting and ending date values here:
-- *** Format MM/DD/YYYY, for example 12/31/2014
DEFINE V_start_date = 'MM/DD/YYYY'
DEFINE V_end_date = 'MM/DD/YYYY'
-- *** Enter starting and ending period values here:
-- *** Format MM, for example 1 for first period and 12 for last period
DEFINE v_start_period = 'M'
DEFINE v_end_period = 'M'
-- *** Enter fiscal year value here:
-- *** Format YYYY, for example 2014
DEFINE V_fiscal_year = 'YYYY'
-- *** Enter ledger IDs here:
-- *** Format, (list of ledgerIDs, separated by comma)
DEFINE V_ledgerID = (123)
-- *** Enter SEGMENT where the Account Number is here:
-- *** Format, SEGMENT# for example SEGMENT3
DEFINE V_Acct_Segment = 'SEGMENTX'
-- Update Date Format for Extraction
alter session set NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MI:SS';
set termout on
PROMPT
PROMPT ********* PwC Journal Extraction Script for Oracle R12 *********
PROMPT
PROMPT Script configured with the following values:
PROMPT
PROMPT - Starting date set to : &&V_start_date
PROMPT - Ending date set to : &&V_end_date
PROMPT - Starting period set to : &&v_start_period
PROMPT - Ending period set to : &&v_end_period
PROMPT - Fiscal year set to : &&V_fiscal_year
PROMPT - Ledger ID (s) set to : &&V_ledgerID
PROMPT - Account Segment set to : &&V_Acct_Segment
PROMPT
PROMPT If any of the above parameters are incorrect, please cancel the query and update accordingly.
PROMPT
PROMPT Now processing system date...
set termout off
----------------------------------CAPTURE TODAYS DATE----------------------------------------------------------
/*This file will provide the day the DBA ran the script */
spool Date_Ran.txt
SELECT 'SYSDATE' FROM sys.dual;
SELECT sysdate from dual;
spool OFF
set termout on
PROMPT Done. Finished processing system date.
set termout off
---------------------------------------------Capture Journal Entry Data---------------------------------------------
/* This file will provide the journal entry detail needed for analysis */
set termout on
PROMPT Now processing Journal Entry detail...
set termout off
spool GL.txt
SELECT 'ACCOUNTED_DR|ACCOUNTED_CR|ENTERED_DR|ENTERED_CR|CURRENCY_ENTERED|EFFECTIVE_DATE|CODE_COMBINATION_ID|DESCRIPTION_LINES|LEDGER_ID|JE_HEADER_ID|JE_LINE_NUM|GL_SL_LINK_ID|GL_SL_LINK_TABLE|STATUS_LINES|JE_BATCH_ID|JE_SOURCE|JE_CATEGORY|PERIOD_NAME|NAME_HEADERS|STATUS_HEADERS|CREATION_DATE|CREATED_BY_LINES|CREATED_BY_HEADERS|LAST_UPDATE_DATE|LAST_UPDATED_BY|DESCRIPTION_HEADERS|ACCRUAL_REV_JE_HEADER_ID|REVERSED_JE_HEADER_ID|ACTUAL_FLAG|JE_FROM_SLA_FLAG|POSTED_DATE|POSTED_BY|APPROVER_EMPLOYEE_ID|NAME_BATCHES|DESCRIPTION_BATCHES|SEGMENT1|SEGMENT2|SEGMENT3|SEGMENT4|SEGMENT5|SEGMENT6|SEGMENT7|SEGMENT8|SEGMENT9|SEGMENT10|SEGMENT11|SEGMENT12|SEGMENT13|SEGMENT14|SEGMENT15|SEGMENT16|SEGMENT17|SEGMENT18|SEGMENT19|SEGMENT20|SEGMENT21|SEGMENT22|SEGMENT23|SEGMENT24|SEGMENT25|SEGMENT26|SEGMENT27|SEGMENT28|SEGMENT29|SEGMENT30' from sys.dual;
SELECT '"'||a.ACCOUNTED_DR||'"|"'|| -- DR AMT
a.ACCOUNTED_CR||'"|"'|| -- CR AMT
a.ENTERED_DR||'"|"'||
a.ENTERED_CR||'"|"'||
b.CURRENCY_CODE||'"|"'|| -- CURRENCY USED IN TRANSACTION, ALIGNS WITH ENTERED AMTS
a.EFFECTIVE_DATE||'"|"'|| -- ACCOUNTING DATE
a.CODE_COMBINATION_ID||'"|"'|| -- TIES TO SEGMENT FIELDS
REPLACE(REPLACE(REPLACE(REPLACE(a.DESCRIPTION,'|','-'),CHR(13),' '),CHR(10),' '),CHR(9),' ')||'"|"'|| -- LINE DESCRIPTION
a.LEDGER_ID||'"|"'|| -- PART OF JOURNAL ID
a.JE_HEADER_ID||'"|"'|| -- PART OF JOURNAL ID
a.JE_LINE_NUM||'"|"'|| -- LINE NUMBER
a.GL_SL_LINK_ID||'"|"'|| -- ID WHICH LINKS GL TO SL
a.GL_SL_LINK_TABLE||'"|"'|| -- TABLE IN SL WHERE JOURNAL CAME FROM
a.STATUS||'"|"'|| -- WANT POSTED 'P' LINES ONLY
b.JE_BATCH_ID||'"|"'||
b.JE_SOURCE||'"|"'|| -- JOURNAL SOURCE CODE
b.JE_CATEGORY||'"|"'||
b.PERIOD_NAME||'"|"'|| -- ACCOUNTING PERIOD
b.NAME||'"|"'|| -- ENTRY NAME
b.STATUS||'"|"'|| -- WANT POSTED 'P' ENTRIES ONLY
b.CREATION_DATE||'"|"'|| -- CREATION DATE
a.CREATED_BY||'"|"'||
b.CREATED_BY||'"|"'||
b.LAST_UPDATE_DATE||'"|"'||
b.LAST_UPDATED_BY||'"|"'||
REPLACE(REPLACE(REPLACE(REPLACE(b.DESCRIPTION,'|','-'),CHR(13),' '),CHR(10),' '),CHR(9),' ')||'"|"'|| -- ENTRY DESCRIPTION
b.ACCRUAL_REV_JE_HEADER_ID||'"|"'|| -- USED FOR REVERSALS
b.REVERSED_JE_HEADER_ID||'"|"'|| -- USED FOR REVERSALS
b.ACTUAL_FLAG||'"|"'|| -- WANT ACCOUNTING 'A' ENTRIES ONLY
b.JE_FROM_SLA_FLAG||'"|"'|| -- DENOTES WHETHER THE ENTRY CAME FROM THE SL
c.POSTED_DATE||'"|"'|| -- POSTING DATE
c.POSTED_BY||'"|"'|| -- Approver ID, unless the APPROVER_EMPLOYEE_ID field is not blank
c.APPROVER_EMPLOYEE_ID||'"|"'|| -- Approver ID when not blank
c.NAME||'"|"'|| -- BATCH NAME
REPLACE(REPLACE(REPLACE(REPLACE(c.DESCRIPTION,'|','-'),CHR(13),' '),CHR(10),' '),CHR(9),' ')||'"|"'|| -- BATCH DESCRIPTION
e.SEGMENT1||'"|"'||
e.SEGMENT2||'"|"'||
e.SEGMENT3||'"|"'||
e.SEGMENT4||'"|"'||
e.SEGMENT5||'"|"'||
e.SEGMENT6||'"|"'||
e.SEGMENT7||'"|"'||
e.SEGMENT8||'"|"'||
e.SEGMENT9||'"|"'||
e.SEGMENT10||'"|"'||
e.SEGMENT11||'"|"'||
e.SEGMENT12||'"|"'||
e.SEGMENT13||'"|"'||
e.SEGMENT14||'"|"'||
e.SEGMENT15||'"|"'||
e.SEGMENT16||'"|"'||
e.SEGMENT17||'"|"'||
e.SEGMENT18||'"|"'||
e.SEGMENT19||'"|"'||
e.SEGMENT20||'"|"'||
e.SEGMENT21||'"|"'||
e.SEGMENT22||'"|"'||
e.SEGMENT23||'"|"'||
e.SEGMENT24||'"|"'||
e.SEGMENT25||'"|"'||
e.SEGMENT26||'"|"'||
e.SEGMENT27||'"|"'||
e.SEGMENT28||'"|"'||
e.SEGMENT29||'"|"'||
e.SEGMENT30||'"'
FROM GL_JE_LINES a
LEFT JOIN GL_JE_HEADERS b
ON a.JE_HEADER_ID = b.JE_HEADER_ID
LEFT JOIN GL_JE_BATCHES c
ON b.JE_BATCH_ID = c.JE_BATCH_ID
LEFT JOIN GL_CODE_COMBINATIONS e
ON a.CODE_COMBINATION_ID = e.CODE_COMBINATION_ID
LEFT JOIN GL_LOOKUPS f
ON c.APPROVAL_STATUS_CODE = f.LOOKUP_CODE
INNER JOIN GL_JE_CATEGORIES_TL g
ON b.JE_CATEGORY = g.JE_CATEGORY_NAME
INNER JOIN GL_JE_SOURCES_TL h
ON b.JE_SOURCE = h.JE_SOURCE_NAME
WHERE f.LOOKUP_TYPE = 'JE_BATCH_APPROVAL_STATUS'
AND a.EFFECTIVE_DATE >= '&&V_start_date 00:00:00' -- MIN ACCOUNTING DATE
AND a.EFFECTIVE_DATE <= '&&V_end_date 23:59:59' -- MAX ACCOUNTING DATE
AND a.LEDGER_ID in &&V_ledgerID -- LIST IN-SCOPE LEDGERS
AND a.STATUS = 'P' -- ONLY POSTED LINES
AND b.STATUS = 'P' -- ONLY POSTED HEADERS
AND b.ACTUAL_FLAG ='A' -- ONLY POSTED ITEMS THAT ARE ACTUAL ACCOUNTING ENTRIES
AND h.LANGUAGE='US' -- ALL INSTANCES OF ORACLE HAVE THE MAIN RECORD WITH LANGUAGE 'US'
AND g.LANGUAGE='US' -- ALL INSTANCES OF ORACLE HAVE THE MAIN RECORD WITH LANGUAGE 'US'
AND UPPER(B.CURRENCY_CODE) <> 'STAT' -- REMOVE STATISTICAL LINES
ORDER BY a.JE_HEADER_ID, a.JE_LINE_NUM;
spool off
set termout on
PROMPT Done. Finished processing Journal Entry detail.
set termout off
---------------------------------------------Capture Trial Balance Data---------------------------------------------
/* This file will provide the opening and closing trial balances for analysis */
set termout on
PROMPT Now processing Trial Balances...
set termout off
spool TB.txt
SELECT 'ACCOUNT_TYPE|SEGMENT1|SEGMENT2|SEGMENT3|SEGMENT4|SEGMENT5|SEGMENT6|SEGMENT7|SEGMENT8|SEGMENT9|SEGMENT10|SEGMENT11|SEGMENT12|SEGMENT13|SEGMENT14|SEGMENT15|SEGMENT16|SEGMENT17|SEGMENT18|SEGMENT19|SEGMENT20|SEGMENT21|sEGMENT22|SEGMENT23|SEGMENT24|SEGMENT25|SEGMENT26|SEGMENT27|SEGMENT28|SEGMENT29|SEGMENT30|LEDGER_ID|CODE_COMBINATION_ID|PERIOD_YEAR|PERIOD_NUM|CURRENCY_CODE|ACTUAL_FLAG|TRANSLATED_FLAG|BEG_BAL|PERIOD_NET_DR|PERIOD_NET_CR|END_BAL' from sys.dual;
SELECT '"'||b.ACCOUNT_TYPE||'"|"'||
b.SEGMENT1||'"|"'||
b.SEGMENT2||'"|"'||
b.SEGMENT3||'"|"'||
b.SEGMENT4||'"|"'||
b.SEGMENT5||'"|"'||
b.SEGMENT6||'"|"'||
b.SEGMENT7||'"|"'||
b.SEGMENT8||'"|"'||
b.SEGMENT9||'"|"'||
b.SEGMENT10||'"|"'||
b.SEGMENT11||'"|"'||
b.SEGMENT12||'"|"'||
b.SEGMENT13||'"|"'||
b.SEGMENT14||'"|"'||
b.SEGMENT15||'"|"'||
b.SEGMENT16||'"|"'||
b.SEGMENT17||'"|"'||
b.SEGMENT18||'"|"'||
b.SEGMENT19||'"|"'||
b.SEGMENT20||'"|"'||
b.SEGMENT21||'"|"'||
b.sEGMENT22||'"|"'||
b.SEGMENT23||'"|"'||
b.SEGMENT24||'"|"'||
b.SEGMENT25||'"|"'||
b.SEGMENT26||'"|"'||
b.SEGMENT27||'"|"'||
b.SEGMENT28||'"|"'||
b.SEGMENT29||'"|"'||
b.SEGMENT30||'"|"'||
a.LEDGER_ID||'"|"'||
a.CODE_COMBINATION_ID||'"|"'||
a.PERIOD_YEAR||'"|"'||
a.PERIOD_NUM||'"|"'||
a.CURRENCY_CODE||'"|"'||
a.ACTUAL_FLAG||'"|"'|| -- WANT ACTUAL 'A' ACCOUNTING ENTRIES
a.TRANSLATED_FLAG||'"|"'|| -- ONLY WANT BLANK INSTANCES
SUM((CASE WHEN a.PERIOD_NUM = '&&v_start_period' THEN a.BEGIN_BALANCE_DR - a.BEGIN_BALANCE_CR ELSE 0.00 END))||'"|"'|| -- BEGINNING PERIOD BALANCE
SUM (a.PERIOD_NET_DR)||'"|"'|| -- GL PERIOD DEBITS
SUM (a.PERIOD_NET_CR)||'"|"'|| -- GL PERIOD CREDITS
(SUM((CASE WHEN a.PERIOD_NUM = '&&v_end_period' THEN (a.BEGIN_BALANCE_DR - a.BEGIN_BALANCE_CR) ELSE 0.00 END)) + SUM (a.PERIOD_NET_DR) - SUM (a.PERIOD_NET_CR))||'"' -- ENDING PERIOD BALANCE
FROM GL_BALANCES a
LEFT JOIN GL_CODE_COMBINATIONS b
ON a.CODE_COMBINATION_ID = b.CODE_COMBINATION_ID
WHERE a.LEDGER_ID in &&V_ledgerID
AND a.PERIOD_YEAR = &&V_fiscal_year
AND (a.PERIOD_NUM = '&&v_start_period' OR a.PERIOD_NUM = '&&v_end_period')
AND a.ACTUAL_FLAG = 'A' -- SET TO ACTUAL
AND a.TRANSLATED_FLAG IS NULL -- SET TO NONE TRANSLATED; SHOULD TIE TO ACCOUNTED AMOUNTS FROM GL LINES
AND UPPER(a.CURRENCY_CODE) <> 'STAT' -- REMOVE STATISTICAL LINES
GROUP BY b.ACCOUNT_TYPE,
b.SEGMENT1,
b.SEGMENT2,
b.SEGMENT3,
b.SEGMENT4,
b.SEGMENT5,
b.SEGMENT6,
b.SEGMENT7,
b.SEGMENT8,
b.SEGMENT9,
b.SEGMENT10,
b.SEGMENT11,
b.SEGMENT12,
b.SEGMENT13,
b.SEGMENT14,
b.SEGMENT15,
b.SEGMENT16,
b.SEGMENT17,
b.SEGMENT18,
b.SEGMENT19,
b.SEGMENT20,
b.SEGMENT21,
b.sEGMENT22,
b.SEGMENT23,
b.SEGMENT24,
b.SEGMENT25,
b.SEGMENT26,
b.SEGMENT27,
b.SEGMENT28,
b.SEGMENT29,
b.SEGMENT30,
a.LEDGER_ID,
a.CODE_COMBINATION_ID,
a.PERIOD_YEAR,
a.PERIOD_NUM,
a.CURRENCY_CODE,
a.ACTUAL_FLAG,
a.TRANSLATED_FLAG
ORDER BY b.SEGMENT1,
b.SEGMENT2,
b.SEGMENT3,
b.SEGMENT4,
b.SEGMENT5,
b.SEGMENT6,
b.SEGMENT7,
b.SEGMENT8,
b.SEGMENT9,
b.SEGMENT10,
b.SEGMENT11,
b.SEGMENT12,
b.SEGMENT13,
b.SEGMENT14,
b.SEGMENT15,
b.SEGMENT16,
b.SEGMENT17,
b.SEGMENT18,
b.SEGMENT19,
b.SEGMENT20,
b.SEGMENT21,
b.sEGMENT22,
b.SEGMENT23,
b.SEGMENT24,
b.SEGMENT25,
b.SEGMENT26,
b.SEGMENT27,
b.SEGMENT28,
b.SEGMENT29,
b.SEGMENT30,
a.LEDGER_ID,
a.CODE_COMBINATION_ID,
a.PERIOD_YEAR,
a.PERIOD_NUM,
a.CURRENCY_CODE,
a.ACTUAL_FLAG,
a.TRANSLATED_FLAG;
spool off
set termout on
PROMPT Done. Finished processing Trial Balances.
set termout off
---------------------------------------------Capture Chart of Accounts data---------------------------------------------
/* This file will provide the chart of accounts */
set termout on
PROMPT Now processing Chart of Accounts table...
set termout off
spool COA.txt
SELECT 'LEDGER_ID|ACCOUNT|APPLICATION_ID|ID_FLEX_CODE|DESCRIPTION_ACCOUNT|ACCOUNT_TYPE|LEDGER_LANGUAGE|CURRENCY_ACCOUNTED' from sys.dual;
SELECT '"'||m.LEDGER_ID||'"|"'|| -- LEDGER ID
m.FLEX_VALUE||'"|"'|| -- ACCOUNT; DRIVEN BY VARIABLE
m.APPLICATION_ID||'"|"'|| -- FILTER FOR THE APPROPRIATE VALUE (101 TENDS TO BE THE DEFAULT)
m.ID_FLEX_CODE||'"|"'|| -- FILTER FOR THE APPROPRIATE VALUE (GL TENDS TO BE THE DEFAULT)
n.DESCRIPTION||'"|"'|| -- ACCOUNT DESCRIPTION
c.ACCOUNT_TYPE||'"|"'|| -- ACCOUNT TYPE (A: ASSET, L: LIAB, O: S/E, R: Revenue, E: Expense)
n.LANGUAGE||'"|"'|| -- BASE LANGUAGE THE LEDGER IS IN
m.BASE_CURRENCY||'"' -- BASE / ACCOUNTED CURRENCY
FROM FND_FLEX_VALUES_TL n
INNER JOIN (SELECT l.FLEX_VALUE_ID, l.FLEX_VALUE, k.FLEX_VALUE_SET_ID, k.ID_FLEX_NUM, k.CHART_OF_ACCOUNTS_ID, k.LEDGER_ID, k.BASE_CURRENCY, k.APPLICATION_ID, k.ID_FLEX_CODE
FROM FND_FLEX_VALUES l
INNER JOIN (SELECT j.SEGMENT_NAME, j.FLEX_VALUE_SET_ID, j.ID_FLEX_NUM, i.CHART_OF_ACCOUNTS_ID, i.LEDGER_ID, i.BASE_CURRENCY, j.APPLICATION_ID, j.ID_FLEX_CODE
FROM FND_ID_FLEX_SEGMENTS j
INNER JOIN (SELECT CHART_OF_ACCOUNTS_ID, --#1 OBTAIN COA ID VIA IN-SCOPE LEDGERS
LEDGER_ID,
CURRENCY_CODE AS BASE_CURRENCY
FROM GL_LEDGERS
WHERE LEDGER_ID IN &&V_ledgerID) i -- VALUE PROMPT FOR IN-SCOPE LEDGER
ON j.ID_FLEX_NUM=i.CHART_OF_ACCOUNTS_ID --#2 OBTAIN FLEX_VALUE_SET_ID VIA COA ID
WHERE APPLICATION_ID = '101' -- CAN VARY
AND ID_FLEX_CODE = 'GL#' -- CAN VARY
AND UPPER(SEGMENT_NAME) = 'ACCOUNT') k
ON l.FLEX_VALUE_SET_ID=k.FLEX_VALUE_SET_ID) m --#3 OBTAIN FLEX_VALUE_ID TO LINK TO DESCRIPTION
ON n.FLEX_VALUE_ID=m.FLEX_VALUE_ID
INNER JOIN (
SELECT a.LEDGER_ID, b.CHART_OF_ACCOUNTS_ID, b.&&V_Acct_Segment, b.ACCOUNT_TYPE
FROM GL_JE_LINES a
LEFT JOIN GL_CODE_COMBINATIONS b
ON a.CODE_COMBINATION_ID=b.CODE_COMBINATION_ID
WHERE LEDGER_ID IN &&V_ledgerID
GROUP BY a.LEDGER_ID, b.CHART_OF_ACCOUNTS_ID, b.&&V_Acct_Segment, b.ACCOUNT_TYPE
ORDER BY a.LEDGER_ID, b.&&V_Acct_Segment
) c
ON m.LEDGER_ID=c.LEDGER_ID AND m.FLEX_VALUE=c.&&V_Acct_Segment
ORDER BY m.LEDGER_ID, m.FLEX_VALUE;
spool off
SEGMENT3
set termout on
PROMPT Done. Finished processing Chart of Accounts.
set termout off
---------------------------------------------Capture User Data---------------------------------------------
/* This file will provide the user name & description */
set termout on
PROMPT Now processing User table...
set termout off
spool Users.txt
select 'user_id|user_name|user_description' from sys.dual;
select '"'||user_id||'"|"'||
user_name||'"|"'||
replace((replace(description,'|','-')),chr(10),' ')||'"'
from fnd_user;
spool off
set termout on
PROMPT Done. Finished processing User table.
set termout off
---------------------------------------------Capture Journal Entry Sources Data---------------------------------------------
/* This file will provide details related to all journal sources defined in the Oracle enviornment */
set termout on
PROMPT Now processing Journal Entry Sources...
set termout off
spool GL_Sources.txt
select 'JE_SOURCE_NAME|USER_JE_SOURCE_NAME|DESCRIPTION|LANGUAGE|JOURNAL_REFERENCE_FLAG|OVERRIDE_EDITS_FLAG|JOURNAL_APPROVAL_FLAG|EFFECTIVE_DATE_RULE_CODE|LAST_UPDATE_DATE|LAST_UPDATED_BY|CREATION_DATE|CREATED_BY' from sys.dual;
select '"'||replace(JE_SOURCE_NAME,'|',';')||'"|"'||
replace(USER_JE_SOURCE_NAME,'|',';')||'"|"'||
replace(DESCRIPTION,'|',';')||'"|"'||
replace(LANGUAGE,'|',';')||'"|"'||
replace(JOURNAL_REFERENCE_FLAG,'|',';')||'"|"'||
replace(OVERRIDE_EDITS_FLAG,'|',';')||'"|"'||
replace(JOURNAL_APPROVAL_FLAG,'|',';')||'"|"'||
replace(EFFECTIVE_DATE_RULE_CODE,'|',';')||'"|"'||
replace(LAST_UPDATE_DATE,'|',';')||'"|"'||
replace(LAST_UPDATED_BY,'|',';')||'"|"'||
replace(CREATION_DATE,'|',';')||'"|"'||
replace(CREATED_BY,'|',';')||'"'
from GL.GL_JE_SOURCES_TL;
spool off
set termout on
PROMPT Done. Finished processing Journal Entry Sources.
set termout off
---------------------------------------------Capture Manual Subledger Journals Data---------------------------------------------
/* This file will provide Manual Subledger Journals created during the period. For manual vs. automated source consideration. */
set termout on
PROMPT Now processing Manual Subleger Journals...
set termout off
spool GL_Manual_Subledger1.txt
SELECT 'LEDGER_ID|PERIOD_NAME|je_batch_id|je_header_id' FROM sys.dual;
SELECT '"'||REPLACE(a.LEDGER_ID,'|',';')||'"|"'||
REPLACE(a.PERIOD_NAME,'|',';')||'"|"'||
REPLACE(c.JE_BATCH_ID,'|',';')||'"|"'||
REPLACE(c.JE_HEADER_ID,'|',';')||'"'
FROM XLA.XLA_AE_HEADERS a
INNER JOIN XLA.XLA_AE_LINES b
ON a.AE_HEADER_ID = b.AE_HEADER_ID
INNER JOIN GL.GL_IMPORT_REFERENCES c
ON c.GL_SL_LINK_ID = b.GL_SL_LINK_ID
LEFT JOIN GL_CODE_COMBINATIONS d
ON b.CODE_COMBINATION_ID = d.CODE_COMBINATION_ID
WHERE a.EVENT_TYPE_CODE = 'MANUAL'
AND a.LEDGER_ID in &&V_ledgerID
AND a.ACCOUNTING_DATE >= '&&V_start_date 00:00:00'
AND a.ACCOUNTING_DATE <= '&&V_end_date 23:59:59'
GROUP BY a.LEDGER_ID,
a.PERIOD_NAME,
c.JE_BATCH_ID,
c.JE_HEADER_ID;
spool off
set termout on
PROMPT Done. Finished processing Manual Subledger Journals.
set termout off
---------------------------------------------Capture Auto Altered Journals---------------------------------------------
/* If management has not locked down the source field the user can modify a manual or ADI journal source to look like a system journal.
This query will identify all journals that were not imported from the subledger but have a subledger journal source.
For manual vs. automated source consideration. */
set termout on
PROMPT Now processing Auto Altered Journals...
set termout off
spool GL_Auto_Altered.txt
SELECT 'LEDGER_ID|PERIOD_NAME|JE_BATCH_ID|JE_HEADER_ID' FROM sys.dual;
SELECT '"'||REPLACE(LEDGER_ID,'|',';')||'"|"'||
REPLACE(PERIOD_NAME,'|',';')||'"|"'||
REPLACE(JE_BATCH_ID,'|',';')||'"|"'||
REPLACE(JE_HEADER_ID,'|',';')||'"'
FROM GL_JE_HEADERS
WHERE NVL(JE_FROM_SLA_FLAG,'N') NOT IN('Y', 'U')---Y: Yes, was from the subleger; U: Upgraded data (i.e. from 11i to R12)
AND LEDGER_ID IN &&V_ledgerID
AND STATUS = 'P' ---Only Actual Posted Journals
AND ACTUAL_FLAG = 'A' ---Only Actual Accounting entries
AND currency_code <> 'STAT' ---STAT used for statistical journals and are not actual journal entries
AND REVERSED_JE_HEADER_ID IS NULL ---Exclude Reversing journals because these also don't have links to the subledger but are not typical manual or spreadsheet journals
AND DEFAULT_EFFECTIVE_DATE >= '01-Apr-2016'--'&&V_start_date 00:00:00'
AND DEFAULT_EFFECTIVE_DATE <='31-Mar-2017' --'&&V_end_date 23:59:59'
AND JE_SOURCE IN (SELECT DISTINCT JE_SOURCE
FROM GL_JE_HEADERS
WHERE JE_FROM_SLA_FLAG = 'Y'
AND DEFAULT_EFFECTIVE_DATE >= '01-Apr-2016'--'&&V_start_date 00:00:00'
AND DEFAULT_EFFECTIVE_DATE <= --'&&V_end_date 23:59:59'
)
GROUP BY LEDGER_ID, PERIOD_NAME, JE_BATCH_ID, JE_HEADER_ID;
spool OFF
set termout on
PROMPT Done. Finished processing Auto Altered Journals.
PROMPT
PROMPT Done. PwC Journal Extraction Queries completed successfully.
PROMPT
set termout off
set feedback on
set heading on
set verify on
set space 1
set pagesize 26
set echo on
Set Trimspool on
set feedback off
set verify off
set termout off
set numwidth 20
set space 0
set pagesize 0
set linesize 30000
/*
Oracle E-Business Suite (EBS) Version: R12
General Ledger Extraction Script
Script Version: 1.8
*/
-- *** Enter starting and ending date values here:
-- *** Format MM/DD/YYYY, for example 12/31/2014
DEFINE V_start_date = 'MM/DD/YYYY'
DEFINE V_end_date = 'MM/DD/YYYY'
-- *** Enter starting and ending period values here:
-- *** Format MM, for example 1 for first period and 12 for last period
DEFINE v_start_period = 'M'
DEFINE v_end_period = 'M'
-- *** Enter fiscal year value here:
-- *** Format YYYY, for example 2014
DEFINE V_fiscal_year = 'YYYY'
-- *** Enter ledger IDs here:
-- *** Format, (list of ledgerIDs, separated by comma)
DEFINE V_ledgerID = (123)
-- *** Enter SEGMENT where the Account Number is here:
-- *** Format, SEGMENT# for example SEGMENT3
DEFINE V_Acct_Segment = 'SEGMENTX'
-- Update Date Format for Extraction
alter session set NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MI:SS';
set termout on
PROMPT
PROMPT ********* PwC Journal Extraction Script for Oracle R12 *********
PROMPT
PROMPT Script configured with the following values:
PROMPT
PROMPT - Starting date set to : &&V_start_date
PROMPT - Ending date set to : &&V_end_date
PROMPT - Starting period set to : &&v_start_period
PROMPT - Ending period set to : &&v_end_period
PROMPT - Fiscal year set to : &&V_fiscal_year
PROMPT - Ledger ID (s) set to : &&V_ledgerID
PROMPT - Account Segment set to : &&V_Acct_Segment
PROMPT
PROMPT If any of the above parameters are incorrect, please cancel the query and update accordingly.
PROMPT
PROMPT Now processing system date...
set termout off
----------------------------------CAPTURE TODAYS DATE----------------------------------------------------------
/*This file will provide the day the DBA ran the script */
spool Date_Ran.txt
SELECT 'SYSDATE' FROM sys.dual;
SELECT sysdate from dual;
spool OFF
set termout on
PROMPT Done. Finished processing system date.
set termout off
---------------------------------------------Capture Journal Entry Data---------------------------------------------
/* This file will provide the journal entry detail needed for analysis */
set termout on
PROMPT Now processing Journal Entry detail...
set termout off
spool GL.txt
SELECT 'ACCOUNTED_DR|ACCOUNTED_CR|ENTERED_DR|ENTERED_CR|CURRENCY_ENTERED|EFFECTIVE_DATE|CODE_COMBINATION_ID|DESCRIPTION_LINES|LEDGER_ID|JE_HEADER_ID|JE_LINE_NUM|GL_SL_LINK_ID|GL_SL_LINK_TABLE|STATUS_LINES|JE_BATCH_ID|JE_SOURCE|JE_CATEGORY|PERIOD_NAME|NAME_HEADERS|STATUS_HEADERS|CREATION_DATE|CREATED_BY_LINES|CREATED_BY_HEADERS|LAST_UPDATE_DATE|LAST_UPDATED_BY|DESCRIPTION_HEADERS|ACCRUAL_REV_JE_HEADER_ID|REVERSED_JE_HEADER_ID|ACTUAL_FLAG|JE_FROM_SLA_FLAG|POSTED_DATE|POSTED_BY|APPROVER_EMPLOYEE_ID|NAME_BATCHES|DESCRIPTION_BATCHES|SEGMENT1|SEGMENT2|SEGMENT3|SEGMENT4|SEGMENT5|SEGMENT6|SEGMENT7|SEGMENT8|SEGMENT9|SEGMENT10|SEGMENT11|SEGMENT12|SEGMENT13|SEGMENT14|SEGMENT15|SEGMENT16|SEGMENT17|SEGMENT18|SEGMENT19|SEGMENT20|SEGMENT21|SEGMENT22|SEGMENT23|SEGMENT24|SEGMENT25|SEGMENT26|SEGMENT27|SEGMENT28|SEGMENT29|SEGMENT30' from sys.dual;
SELECT '"'||a.ACCOUNTED_DR||'"|"'|| -- DR AMT
a.ACCOUNTED_CR||'"|"'|| -- CR AMT
a.ENTERED_DR||'"|"'||
a.ENTERED_CR||'"|"'||
b.CURRENCY_CODE||'"|"'|| -- CURRENCY USED IN TRANSACTION, ALIGNS WITH ENTERED AMTS
a.EFFECTIVE_DATE||'"|"'|| -- ACCOUNTING DATE
a.CODE_COMBINATION_ID||'"|"'|| -- TIES TO SEGMENT FIELDS
REPLACE(REPLACE(REPLACE(REPLACE(a.DESCRIPTION,'|','-'),CHR(13),' '),CHR(10),' '),CHR(9),' ')||'"|"'|| -- LINE DESCRIPTION
a.LEDGER_ID||'"|"'|| -- PART OF JOURNAL ID
a.JE_HEADER_ID||'"|"'|| -- PART OF JOURNAL ID
a.JE_LINE_NUM||'"|"'|| -- LINE NUMBER
a.GL_SL_LINK_ID||'"|"'|| -- ID WHICH LINKS GL TO SL
a.GL_SL_LINK_TABLE||'"|"'|| -- TABLE IN SL WHERE JOURNAL CAME FROM
a.STATUS||'"|"'|| -- WANT POSTED 'P' LINES ONLY
b.JE_BATCH_ID||'"|"'||
b.JE_SOURCE||'"|"'|| -- JOURNAL SOURCE CODE
b.JE_CATEGORY||'"|"'||
b.PERIOD_NAME||'"|"'|| -- ACCOUNTING PERIOD
b.NAME||'"|"'|| -- ENTRY NAME
b.STATUS||'"|"'|| -- WANT POSTED 'P' ENTRIES ONLY
b.CREATION_DATE||'"|"'|| -- CREATION DATE
a.CREATED_BY||'"|"'||
b.CREATED_BY||'"|"'||
b.LAST_UPDATE_DATE||'"|"'||
b.LAST_UPDATED_BY||'"|"'||
REPLACE(REPLACE(REPLACE(REPLACE(b.DESCRIPTION,'|','-'),CHR(13),' '),CHR(10),' '),CHR(9),' ')||'"|"'|| -- ENTRY DESCRIPTION
b.ACCRUAL_REV_JE_HEADER_ID||'"|"'|| -- USED FOR REVERSALS
b.REVERSED_JE_HEADER_ID||'"|"'|| -- USED FOR REVERSALS
b.ACTUAL_FLAG||'"|"'|| -- WANT ACCOUNTING 'A' ENTRIES ONLY
b.JE_FROM_SLA_FLAG||'"|"'|| -- DENOTES WHETHER THE ENTRY CAME FROM THE SL
c.POSTED_DATE||'"|"'|| -- POSTING DATE
c.POSTED_BY||'"|"'|| -- Approver ID, unless the APPROVER_EMPLOYEE_ID field is not blank
c.APPROVER_EMPLOYEE_ID||'"|"'|| -- Approver ID when not blank
c.NAME||'"|"'|| -- BATCH NAME
REPLACE(REPLACE(REPLACE(REPLACE(c.DESCRIPTION,'|','-'),CHR(13),' '),CHR(10),' '),CHR(9),' ')||'"|"'|| -- BATCH DESCRIPTION
e.SEGMENT1||'"|"'||
e.SEGMENT2||'"|"'||
e.SEGMENT3||'"|"'||
e.SEGMENT4||'"|"'||
e.SEGMENT5||'"|"'||
e.SEGMENT6||'"|"'||
e.SEGMENT7||'"|"'||
e.SEGMENT8||'"|"'||
e.SEGMENT9||'"|"'||
e.SEGMENT10||'"|"'||
e.SEGMENT11||'"|"'||
e.SEGMENT12||'"|"'||
e.SEGMENT13||'"|"'||
e.SEGMENT14||'"|"'||
e.SEGMENT15||'"|"'||
e.SEGMENT16||'"|"'||
e.SEGMENT17||'"|"'||
e.SEGMENT18||'"|"'||
e.SEGMENT19||'"|"'||
e.SEGMENT20||'"|"'||
e.SEGMENT21||'"|"'||
e.SEGMENT22||'"|"'||
e.SEGMENT23||'"|"'||
e.SEGMENT24||'"|"'||
e.SEGMENT25||'"|"'||
e.SEGMENT26||'"|"'||
e.SEGMENT27||'"|"'||
e.SEGMENT28||'"|"'||
e.SEGMENT29||'"|"'||
e.SEGMENT30||'"'
FROM GL_JE_LINES a
LEFT JOIN GL_JE_HEADERS b
ON a.JE_HEADER_ID = b.JE_HEADER_ID
LEFT JOIN GL_JE_BATCHES c
ON b.JE_BATCH_ID = c.JE_BATCH_ID
LEFT JOIN GL_CODE_COMBINATIONS e
ON a.CODE_COMBINATION_ID = e.CODE_COMBINATION_ID
LEFT JOIN GL_LOOKUPS f
ON c.APPROVAL_STATUS_CODE = f.LOOKUP_CODE
INNER JOIN GL_JE_CATEGORIES_TL g
ON b.JE_CATEGORY = g.JE_CATEGORY_NAME
INNER JOIN GL_JE_SOURCES_TL h
ON b.JE_SOURCE = h.JE_SOURCE_NAME
WHERE f.LOOKUP_TYPE = 'JE_BATCH_APPROVAL_STATUS'
AND a.EFFECTIVE_DATE >= '&&V_start_date 00:00:00' -- MIN ACCOUNTING DATE
AND a.EFFECTIVE_DATE <= '&&V_end_date 23:59:59' -- MAX ACCOUNTING DATE
AND a.LEDGER_ID in &&V_ledgerID -- LIST IN-SCOPE LEDGERS
AND a.STATUS = 'P' -- ONLY POSTED LINES
AND b.STATUS = 'P' -- ONLY POSTED HEADERS
AND b.ACTUAL_FLAG ='A' -- ONLY POSTED ITEMS THAT ARE ACTUAL ACCOUNTING ENTRIES
AND h.LANGUAGE='US' -- ALL INSTANCES OF ORACLE HAVE THE MAIN RECORD WITH LANGUAGE 'US'
AND g.LANGUAGE='US' -- ALL INSTANCES OF ORACLE HAVE THE MAIN RECORD WITH LANGUAGE 'US'
AND UPPER(B.CURRENCY_CODE) <> 'STAT' -- REMOVE STATISTICAL LINES
ORDER BY a.JE_HEADER_ID, a.JE_LINE_NUM;
spool off
set termout on
PROMPT Done. Finished processing Journal Entry detail.
set termout off
---------------------------------------------Capture Trial Balance Data---------------------------------------------
/* This file will provide the opening and closing trial balances for analysis */
set termout on
PROMPT Now processing Trial Balances...
set termout off
spool TB.txt
SELECT 'ACCOUNT_TYPE|SEGMENT1|SEGMENT2|SEGMENT3|SEGMENT4|SEGMENT5|SEGMENT6|SEGMENT7|SEGMENT8|SEGMENT9|SEGMENT10|SEGMENT11|SEGMENT12|SEGMENT13|SEGMENT14|SEGMENT15|SEGMENT16|SEGMENT17|SEGMENT18|SEGMENT19|SEGMENT20|SEGMENT21|sEGMENT22|SEGMENT23|SEGMENT24|SEGMENT25|SEGMENT26|SEGMENT27|SEGMENT28|SEGMENT29|SEGMENT30|LEDGER_ID|CODE_COMBINATION_ID|PERIOD_YEAR|PERIOD_NUM|CURRENCY_CODE|ACTUAL_FLAG|TRANSLATED_FLAG|BEG_BAL|PERIOD_NET_DR|PERIOD_NET_CR|END_BAL' from sys.dual;
SELECT '"'||b.ACCOUNT_TYPE||'"|"'||
b.SEGMENT1||'"|"'||
b.SEGMENT2||'"|"'||
b.SEGMENT3||'"|"'||
b.SEGMENT4||'"|"'||
b.SEGMENT5||'"|"'||
b.SEGMENT6||'"|"'||
b.SEGMENT7||'"|"'||
b.SEGMENT8||'"|"'||
b.SEGMENT9||'"|"'||
b.SEGMENT10||'"|"'||
b.SEGMENT11||'"|"'||
b.SEGMENT12||'"|"'||
b.SEGMENT13||'"|"'||
b.SEGMENT14||'"|"'||
b.SEGMENT15||'"|"'||
b.SEGMENT16||'"|"'||
b.SEGMENT17||'"|"'||
b.SEGMENT18||'"|"'||
b.SEGMENT19||'"|"'||
b.SEGMENT20||'"|"'||
b.SEGMENT21||'"|"'||
b.sEGMENT22||'"|"'||
b.SEGMENT23||'"|"'||
b.SEGMENT24||'"|"'||
b.SEGMENT25||'"|"'||
b.SEGMENT26||'"|"'||
b.SEGMENT27||'"|"'||
b.SEGMENT28||'"|"'||
b.SEGMENT29||'"|"'||
b.SEGMENT30||'"|"'||
a.LEDGER_ID||'"|"'||
a.CODE_COMBINATION_ID||'"|"'||
a.PERIOD_YEAR||'"|"'||
a.PERIOD_NUM||'"|"'||
a.CURRENCY_CODE||'"|"'||
a.ACTUAL_FLAG||'"|"'|| -- WANT ACTUAL 'A' ACCOUNTING ENTRIES
a.TRANSLATED_FLAG||'"|"'|| -- ONLY WANT BLANK INSTANCES
SUM((CASE WHEN a.PERIOD_NUM = '&&v_start_period' THEN a.BEGIN_BALANCE_DR - a.BEGIN_BALANCE_CR ELSE 0.00 END))||'"|"'|| -- BEGINNING PERIOD BALANCE
SUM (a.PERIOD_NET_DR)||'"|"'|| -- GL PERIOD DEBITS
SUM (a.PERIOD_NET_CR)||'"|"'|| -- GL PERIOD CREDITS
(SUM((CASE WHEN a.PERIOD_NUM = '&&v_end_period' THEN (a.BEGIN_BALANCE_DR - a.BEGIN_BALANCE_CR) ELSE 0.00 END)) + SUM (a.PERIOD_NET_DR) - SUM (a.PERIOD_NET_CR))||'"' -- ENDING PERIOD BALANCE
FROM GL_BALANCES a
LEFT JOIN GL_CODE_COMBINATIONS b
ON a.CODE_COMBINATION_ID = b.CODE_COMBINATION_ID
WHERE a.LEDGER_ID in &&V_ledgerID
AND a.PERIOD_YEAR = &&V_fiscal_year
AND (a.PERIOD_NUM = '&&v_start_period' OR a.PERIOD_NUM = '&&v_end_period')
AND a.ACTUAL_FLAG = 'A' -- SET TO ACTUAL
AND a.TRANSLATED_FLAG IS NULL -- SET TO NONE TRANSLATED; SHOULD TIE TO ACCOUNTED AMOUNTS FROM GL LINES
AND UPPER(a.CURRENCY_CODE) <> 'STAT' -- REMOVE STATISTICAL LINES
GROUP BY b.ACCOUNT_TYPE,
b.SEGMENT1,
b.SEGMENT2,
b.SEGMENT3,
b.SEGMENT4,
b.SEGMENT5,
b.SEGMENT6,
b.SEGMENT7,
b.SEGMENT8,
b.SEGMENT9,
b.SEGMENT10,
b.SEGMENT11,
b.SEGMENT12,
b.SEGMENT13,
b.SEGMENT14,
b.SEGMENT15,
b.SEGMENT16,
b.SEGMENT17,
b.SEGMENT18,
b.SEGMENT19,
b.SEGMENT20,
b.SEGMENT21,
b.sEGMENT22,
b.SEGMENT23,
b.SEGMENT24,
b.SEGMENT25,
b.SEGMENT26,
b.SEGMENT27,
b.SEGMENT28,
b.SEGMENT29,
b.SEGMENT30,
a.LEDGER_ID,
a.CODE_COMBINATION_ID,
a.PERIOD_YEAR,
a.PERIOD_NUM,
a.CURRENCY_CODE,
a.ACTUAL_FLAG,
a.TRANSLATED_FLAG
ORDER BY b.SEGMENT1,
b.SEGMENT2,
b.SEGMENT3,
b.SEGMENT4,
b.SEGMENT5,
b.SEGMENT6,
b.SEGMENT7,
b.SEGMENT8,
b.SEGMENT9,
b.SEGMENT10,
b.SEGMENT11,
b.SEGMENT12,
b.SEGMENT13,
b.SEGMENT14,
b.SEGMENT15,
b.SEGMENT16,
b.SEGMENT17,
b.SEGMENT18,
b.SEGMENT19,
b.SEGMENT20,
b.SEGMENT21,
b.sEGMENT22,
b.SEGMENT23,
b.SEGMENT24,
b.SEGMENT25,
b.SEGMENT26,
b.SEGMENT27,
b.SEGMENT28,
b.SEGMENT29,
b.SEGMENT30,
a.LEDGER_ID,
a.CODE_COMBINATION_ID,
a.PERIOD_YEAR,
a.PERIOD_NUM,
a.CURRENCY_CODE,
a.ACTUAL_FLAG,
a.TRANSLATED_FLAG;
spool off
set termout on
PROMPT Done. Finished processing Trial Balances.
set termout off
---------------------------------------------Capture Chart of Accounts data---------------------------------------------
/* This file will provide the chart of accounts */
set termout on
PROMPT Now processing Chart of Accounts table...
set termout off
spool COA.txt
SELECT 'LEDGER_ID|ACCOUNT|APPLICATION_ID|ID_FLEX_CODE|DESCRIPTION_ACCOUNT|ACCOUNT_TYPE|LEDGER_LANGUAGE|CURRENCY_ACCOUNTED' from sys.dual;
SELECT '"'||m.LEDGER_ID||'"|"'|| -- LEDGER ID
m.FLEX_VALUE||'"|"'|| -- ACCOUNT; DRIVEN BY VARIABLE
m.APPLICATION_ID||'"|"'|| -- FILTER FOR THE APPROPRIATE VALUE (101 TENDS TO BE THE DEFAULT)
m.ID_FLEX_CODE||'"|"'|| -- FILTER FOR THE APPROPRIATE VALUE (GL TENDS TO BE THE DEFAULT)
n.DESCRIPTION||'"|"'|| -- ACCOUNT DESCRIPTION
c.ACCOUNT_TYPE||'"|"'|| -- ACCOUNT TYPE (A: ASSET, L: LIAB, O: S/E, R: Revenue, E: Expense)
n.LANGUAGE||'"|"'|| -- BASE LANGUAGE THE LEDGER IS IN
m.BASE_CURRENCY||'"' -- BASE / ACCOUNTED CURRENCY
FROM FND_FLEX_VALUES_TL n
INNER JOIN (SELECT l.FLEX_VALUE_ID, l.FLEX_VALUE, k.FLEX_VALUE_SET_ID, k.ID_FLEX_NUM, k.CHART_OF_ACCOUNTS_ID, k.LEDGER_ID, k.BASE_CURRENCY, k.APPLICATION_ID, k.ID_FLEX_CODE
FROM FND_FLEX_VALUES l
INNER JOIN (SELECT j.SEGMENT_NAME, j.FLEX_VALUE_SET_ID, j.ID_FLEX_NUM, i.CHART_OF_ACCOUNTS_ID, i.LEDGER_ID, i.BASE_CURRENCY, j.APPLICATION_ID, j.ID_FLEX_CODE
FROM FND_ID_FLEX_SEGMENTS j
INNER JOIN (SELECT CHART_OF_ACCOUNTS_ID, --#1 OBTAIN COA ID VIA IN-SCOPE LEDGERS
LEDGER_ID,
CURRENCY_CODE AS BASE_CURRENCY
FROM GL_LEDGERS
WHERE LEDGER_ID IN &&V_ledgerID) i -- VALUE PROMPT FOR IN-SCOPE LEDGER
ON j.ID_FLEX_NUM=i.CHART_OF_ACCOUNTS_ID --#2 OBTAIN FLEX_VALUE_SET_ID VIA COA ID
WHERE APPLICATION_ID = '101' -- CAN VARY
AND ID_FLEX_CODE = 'GL#' -- CAN VARY
AND UPPER(SEGMENT_NAME) = 'ACCOUNT') k
ON l.FLEX_VALUE_SET_ID=k.FLEX_VALUE_SET_ID) m --#3 OBTAIN FLEX_VALUE_ID TO LINK TO DESCRIPTION
ON n.FLEX_VALUE_ID=m.FLEX_VALUE_ID
INNER JOIN (
SELECT a.LEDGER_ID, b.CHART_OF_ACCOUNTS_ID, b.&&V_Acct_Segment, b.ACCOUNT_TYPE
FROM GL_JE_LINES a
LEFT JOIN GL_CODE_COMBINATIONS b
ON a.CODE_COMBINATION_ID=b.CODE_COMBINATION_ID
WHERE LEDGER_ID IN &&V_ledgerID
GROUP BY a.LEDGER_ID, b.CHART_OF_ACCOUNTS_ID, b.&&V_Acct_Segment, b.ACCOUNT_TYPE
ORDER BY a.LEDGER_ID, b.&&V_Acct_Segment
) c
ON m.LEDGER_ID=c.LEDGER_ID AND m.FLEX_VALUE=c.&&V_Acct_Segment
ORDER BY m.LEDGER_ID, m.FLEX_VALUE;
spool off
SEGMENT3
set termout on
PROMPT Done. Finished processing Chart of Accounts.
set termout off
---------------------------------------------Capture User Data---------------------------------------------
/* This file will provide the user name & description */
set termout on
PROMPT Now processing User table...
set termout off
spool Users.txt
select 'user_id|user_name|user_description' from sys.dual;
select '"'||user_id||'"|"'||
user_name||'"|"'||
replace((replace(description,'|','-')),chr(10),' ')||'"'
from fnd_user;
spool off
set termout on
PROMPT Done. Finished processing User table.
set termout off
---------------------------------------------Capture Journal Entry Sources Data---------------------------------------------
/* This file will provide details related to all journal sources defined in the Oracle enviornment */
set termout on
PROMPT Now processing Journal Entry Sources...
set termout off
spool GL_Sources.txt
select 'JE_SOURCE_NAME|USER_JE_SOURCE_NAME|DESCRIPTION|LANGUAGE|JOURNAL_REFERENCE_FLAG|OVERRIDE_EDITS_FLAG|JOURNAL_APPROVAL_FLAG|EFFECTIVE_DATE_RULE_CODE|LAST_UPDATE_DATE|LAST_UPDATED_BY|CREATION_DATE|CREATED_BY' from sys.dual;
select '"'||replace(JE_SOURCE_NAME,'|',';')||'"|"'||
replace(USER_JE_SOURCE_NAME,'|',';')||'"|"'||
replace(DESCRIPTION,'|',';')||'"|"'||
replace(LANGUAGE,'|',';')||'"|"'||
replace(JOURNAL_REFERENCE_FLAG,'|',';')||'"|"'||
replace(OVERRIDE_EDITS_FLAG,'|',';')||'"|"'||
replace(JOURNAL_APPROVAL_FLAG,'|',';')||'"|"'||
replace(EFFECTIVE_DATE_RULE_CODE,'|',';')||'"|"'||
replace(LAST_UPDATE_DATE,'|',';')||'"|"'||
replace(LAST_UPDATED_BY,'|',';')||'"|"'||
replace(CREATION_DATE,'|',';')||'"|"'||
replace(CREATED_BY,'|',';')||'"'
from GL.GL_JE_SOURCES_TL;
spool off
set termout on
PROMPT Done. Finished processing Journal Entry Sources.
set termout off
---------------------------------------------Capture Manual Subledger Journals Data---------------------------------------------
/* This file will provide Manual Subledger Journals created during the period. For manual vs. automated source consideration. */
set termout on
PROMPT Now processing Manual Subleger Journals...
set termout off
spool GL_Manual_Subledger1.txt
SELECT 'LEDGER_ID|PERIOD_NAME|je_batch_id|je_header_id' FROM sys.dual;
SELECT '"'||REPLACE(a.LEDGER_ID,'|',';')||'"|"'||
REPLACE(a.PERIOD_NAME,'|',';')||'"|"'||
REPLACE(c.JE_BATCH_ID,'|',';')||'"|"'||
REPLACE(c.JE_HEADER_ID,'|',';')||'"'
FROM XLA.XLA_AE_HEADERS a
INNER JOIN XLA.XLA_AE_LINES b
ON a.AE_HEADER_ID = b.AE_HEADER_ID
INNER JOIN GL.GL_IMPORT_REFERENCES c
ON c.GL_SL_LINK_ID = b.GL_SL_LINK_ID
LEFT JOIN GL_CODE_COMBINATIONS d
ON b.CODE_COMBINATION_ID = d.CODE_COMBINATION_ID
WHERE a.EVENT_TYPE_CODE = 'MANUAL'
AND a.LEDGER_ID in &&V_ledgerID
AND a.ACCOUNTING_DATE >= '&&V_start_date 00:00:00'
AND a.ACCOUNTING_DATE <= '&&V_end_date 23:59:59'
GROUP BY a.LEDGER_ID,
a.PERIOD_NAME,
c.JE_BATCH_ID,
c.JE_HEADER_ID;
spool off
set termout on
PROMPT Done. Finished processing Manual Subledger Journals.
set termout off
---------------------------------------------Capture Auto Altered Journals---------------------------------------------
/* If management has not locked down the source field the user can modify a manual or ADI journal source to look like a system journal.
This query will identify all journals that were not imported from the subledger but have a subledger journal source.
For manual vs. automated source consideration. */
set termout on
PROMPT Now processing Auto Altered Journals...
set termout off
spool GL_Auto_Altered.txt
SELECT 'LEDGER_ID|PERIOD_NAME|JE_BATCH_ID|JE_HEADER_ID' FROM sys.dual;
SELECT '"'||REPLACE(LEDGER_ID,'|',';')||'"|"'||
REPLACE(PERIOD_NAME,'|',';')||'"|"'||
REPLACE(JE_BATCH_ID,'|',';')||'"|"'||
REPLACE(JE_HEADER_ID,'|',';')||'"'
FROM GL_JE_HEADERS
WHERE NVL(JE_FROM_SLA_FLAG,'N') NOT IN('Y', 'U')---Y: Yes, was from the subleger; U: Upgraded data (i.e. from 11i to R12)
AND LEDGER_ID IN &&V_ledgerID
AND STATUS = 'P' ---Only Actual Posted Journals
AND ACTUAL_FLAG = 'A' ---Only Actual Accounting entries
AND currency_code <> 'STAT' ---STAT used for statistical journals and are not actual journal entries
AND REVERSED_JE_HEADER_ID IS NULL ---Exclude Reversing journals because these also don't have links to the subledger but are not typical manual or spreadsheet journals
AND DEFAULT_EFFECTIVE_DATE >= '01-Apr-2016'--'&&V_start_date 00:00:00'
AND DEFAULT_EFFECTIVE_DATE <='31-Mar-2017' --'&&V_end_date 23:59:59'
AND JE_SOURCE IN (SELECT DISTINCT JE_SOURCE
FROM GL_JE_HEADERS
WHERE JE_FROM_SLA_FLAG = 'Y'
AND DEFAULT_EFFECTIVE_DATE >= '01-Apr-2016'--'&&V_start_date 00:00:00'
AND DEFAULT_EFFECTIVE_DATE <= --'&&V_end_date 23:59:59'
)
GROUP BY LEDGER_ID, PERIOD_NAME, JE_BATCH_ID, JE_HEADER_ID;
spool OFF
set termout on
PROMPT Done. Finished processing Auto Altered Journals.
PROMPT
PROMPT Done. PwC Journal Extraction Queries completed successfully.
PROMPT
set termout off
set feedback on
set heading on
set verify on
set space 1
set pagesize 26
set echo on
No comments:
Post a Comment