Explains how to use a preseeded query to import General Ledger balances from Peoplesoft sources sources using the EPM Integration Agent.

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 an Oracle Fusion Cloud Enterprise Performance Management 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 )
  )