Peoplesoft General Ledger Preseeded Query Reference
You can use a preseeded query to import General Ledger balances from Peoplesoft sources and then create and register an application that can be loaded to a Oracle Enterprise Performance Management Cloud target applications using the EPM Integration Agent.
The following shows the prebuilt Peoplesoft query that comes with the EPM Integration agent:
SELECT
PL.BUSINESS_UNIT as "Business Unit",
PL.LEDGER as "Ledger",
PL.ACCOUNT as "Account",
PL.ALTACCT as "Alt Account",
PL.DEPTID as "Department",
PL.OPERATING_UNIT as "Operating Unit",
PL.PRODUCT as "Product",
PL.FUND_CODE as "Fund Code",
PL.CLASS_FLD as "Class",
PL.PROGRAM_CODE as "Program",
PL.BUDGET_REF as "Budget Reference",
PL.AFFILIATE as "Affiliate",
PL.AFFILIATE_INTRA1 as "Affiliate Intra1",
PL.AFFILIATE_INTRA2 as "Affiliate Intra2",
PL.CHARTFIELD1 as "Chartfield1",
PL.CHARTFIELD2 as "Chartfield2",
PL.CHARTFIELD3 as "Chartfield3",
PL.PROJECT_ID as "Project",
PL.BOOK_CODE as "Book Code",
PL.GL_ADJUST_TYPE as "GL Adjust Type",
PGA.STATISTICS_ACCOUNT as "Stat Account",
PGA.ACCOUNT_TYPE as "Account Type",
PGA.DESCR as "Account Description",
PL.CURRENCY_CD as "Currency",
PL.STATISTICS_CODE as "Stat Code",
PL.FISCAL_YEAR as "Fiscal Year",
PL.ACCOUNTING_PERIOD as "Accounting Period",
PL.POSTED_TOTAL_AMT as "Posted Total Amount",
PL.POSTED_BASE_AMT as "Posted Base Amount",
PL.POSTED_TRAN_AMT as "Posted Tran Amount",
PL.BASE_CURRENCY as "Base Currency",
PL.PROCESS_INSTANCE as "Process Instance"
FROM PS_LEDGER PL,
PS_GL_ACCOUNT_TBL PGA
WHERE ( 1=1 )
AND ( PL.BUSINESS_UNIT = ~BU~
AND PL.LEDGER = ~LEDGER~
AND PL.FISCAL_YEAR = ~YEAR~
AND PL.ACCOUNTING_PERIOD = ~PERIOD~
AND ( PGA.SETID = ( SELECT SETID
FROM PS_SET_CNTRL_REC
WHERE SETCNTRLVALUE = ~BU~
AND RECNAME = ''GL_ACCOUNT_TBL'' )
AND PGA.EFFDT = ( SELECT MAX(B.EFFDT)
FROM PS_GL_ACCOUNT_TBL B
WHERE PGA.SETID = B.SETID AND PGA.ACCOUNT = B.ACCOUNT )
)
AND ( PL.ACCOUNT=PGA.ACCOUNT )
)