This article lists the public EPM queries and descriptions for this topic. Please review available public queries below before making a new query.
EPM public query names and descriptions for General Ledger
- GLS8020 (Budget Status) (DOCX): Query will return Fund, SID, Ledger information for the accounting period, fund code and department ID user prompts on.
- GL Activity by Acctg Period (DOCX): EPM version that emulates the GL Activity report. This is a good report to use with a pivot table.
- Agency Journal Error Report (DOCX): Public query for Fin Team of the Journal Transaction Table where Source is (DC, ONL, PC, SSJ) and Journal Header Status in (E,I,T) OR Budget Header Status = E by Deptid and Fiscal Year.
- Agency Valid Journal Report (DOCX): Public query for Fin Team of Journal Transaction Table where Source in (DC, ONL, PC, SSJ) and Journal Header Status = V and Budget Header Status = V by Deptid and Fiscal Year.
- Agency ALL Journal Errors Report (DOCX): Public query for Fin Team of Journal Transaction Table Where Journal Header Status in (E,I,T) OR Budget Header Status = E by Deptid and Fiscal Year.
- Budget Journal not posted (DOCX): Query off the Budget Transaction table to identify Journals where Budget Header Status is not Posted or Unposted.
- Budget Lapse and Carry-Forward (DOCX): This query provides the closing and carry-forward balances for an agency by Fiscal Year and Commitment Control Ledger group.
- Budget Ref not equal to FY (DOCX): Query for GL Users to ID wrong Budget Refs for Budgeted Funds. Prompts on DeptID and Fiscal Year where Ledger = Mod Accrual and Account is between 40000 and 59999, and Amount ? 0, and Budget Ref ? Fiscal Year.
- Budget Details Inquiry (DOCX): Replicates FN production report, Budget Details Inquiry. Query prompts on a Ledger Group, Budget Period, Fiscal Year, and a range of Department ID, Account, Fund, and SID. This query uses the EPM reporting table, CTW_LEDG_KK_BAL.
- GL Expenditures by FY (DOCX): GL query by DeptID, Account, and SID.
- GL Expenditure by Acctg Period (DOCX): GL query includes DeptID as a prompt but does not include DeptID in the returned query answer. This means that information is summarized at the agency level. If a specific DeptID is used in the prompt, only that department will be returned.
- Expenditures by Object (DOCX): Query replicates FN GL report, Expenditure Report by Object. Prompts on ranges for Department, Fund, SID, Account and by ledger, fiscal year and accounting period.
- FIN Accounting Entries by Pay Check # (DOCX): Query for Fiscal Year End to tie FIN Accounting Payroll entries back to an HRMS Paycheck #. Query prompts for Journal date (Check Date) and Paycheck Number, Deptid% and Account%. You can prompt for Account values that is LIKE 5% to get only rows for payroll expenditures.
- Mod Accrual and Mod Cash period report (DOCX): Query to capture Mod Cash and Mod Accrual posted total amounts by Deptid, Fund, Sid, Account, Fiscal Year. The query provides a range of accounting periods within a fiscal year.
- Entries to Cash Accounts (DOCX): This query returns a list of manual journal entries to cash accounts by Fund/SID. Please reference: Non-Appropriated Cash Status Report http://www.core-ct.state.ct.us/reports/docs/ctglr17174.doc
- Non-Approp Funds Cash Balance (DOCX): This query provides balances for the cash accounts used in the CTGLR17174 - Non-Appropriated Cash Balance Report. http://www.core-ct.state.ct.us/reports/docs/ctglr17174.doc
- Non-Approp Funds Cash Expenses (DOCX): This query provides balances for the expense accounts used in the CTGLR17174 - Non-Appropriated Cash Balance Report. http://www.core-ct.state.ct.us/reports/docs/ctglr17174.doc
- Non-Approp Funds Cash Revenue (DOCX): This query provides balances for the revenue accounts used in the CTGLR17174 - Non-Appropriated Cash Balance Report. http://www.core-ct.state.ct.us/reports/docs/ctglr17174.doc
- PS Charges from Other DeptIDs (DOCX): This query identifies personal services (50000 Account) journal entries in the HR Accounting Line table charged by other departments where the journal line reference (or paycheck number) does not exist on the agency's corresponding Payroll data for a particular pay period. This query prompts for a Deptid, Journal Date (check date) and corresponding Pay End Date.
- Journal Entries for Payroll (DOCX): Query to generate payroll entries after conversion to Financials. Users can run this to see all payroll entries for a Journal Date (or Check Date). Query also prompts for Deptid, Fund, Sid, and Journal ID. Paycheck number is included on this table.
- Trial Balance Report (DOCX): Query replicates FN report, Trial Balance. Query prompts on range of Departments, Fund, SID and Account. Also prompts on accounting period and fiscal year.
- Journal Entry Query (DOCX): Query will provide Journal Entry information for Business Unit, journal date range, and Ledger the user prompts on for transactions where Budget Header line and status are Valid and Journal Header Status is Posted and Journal Line status = 0 or 'OK'.
- Reconcile CTGLR 341/342 to Budget (DOCX): This query has been developed to ASSIST in the reconciliation Pre-encumbrance and Encumbrance Balance reports to the Budget. This is used to facilitate reconciliation of DATCLEAN entries created to reverse double encumbrances or pre-encumbrances. At times, GL Journal Entries are posted to the system by OSC, in order to correct encumbrance / pre-encumbrance issues. These entries all begin with the Journal ID of DATCLEAN.
- Posted Journals by Account (DOCX): From the CT_JRNL_TRANS_TBL, this query shows posted journals for a date range and the account to which it was paid.
- Ledger Balance (CFSR) (DOCX): This query provides ledger balance information based provided by the user at the runtime. The user is asked for DeptID (this may use a wildcard), Fund, SID, and a Period range. The query uses the CT_LEDG_KK_BAL table.
- Ledger Bal by Acctg Period (DOCX): Ledger Balance report where Periods are decoded columns = columns are pivoted using 'Expressions' so each period (1-12) is displayed as its own column vs. row
- Ledger Bal Per – Pivot in Excel (DOCX): Ledger Balance report prompts for Acc Period, CT SID, Fund, DEPTID and Fiscal Year. You can download to excel and create pivot table
- MOD_ACCRL & MOD_CASH Journals (DOCX): Journal information for modified accrual and modified cash ledgers prompts for deptid, fund, SID, account, fiscal year, and accounting period.
- Refunds of Expenditures (DOCX): Using the CT_JRNL_TRN_TBL, this query is designed to show refunds of expenditures sorted by period and journal ID.
- Payroll Detail – Other Agency Charge (DOCX): Query provides detailed payroll data by pay period where the personal service charges are for an employee of a different agency. Only those rows associated with a state coding string are returned. Use this query to identify payroll charges in the general ledger from other agencies.
- Cross BU Payroll Data - (Agency Member or Not) (DOCX): Query provides detailed payroll data by pay period for personal service charges to a specific business unit. Only those rows associated with a state coding string are returned. Use this query to balance payroll entries to general ledger entries.
- GL to Payroll Summary Reconciliation (DOCX): Query to crosswalk PR accounting entries back to EPM DET PAYROLL and or HRMS Payroll Summary reports by Deptid, Fund, SID, Account and Fiscal Year for PR Reconciliation Job Aid.
- GL to Payroll Summary Reconcil (DOCX): Query will provide Journal information for a Department ID or range of Department IDs based upon journal date range, Ledger, and Department ID.
- Journal Entry Query (DOCX): Query will provide Journal information for a Department ID or range of Department IDs based upon journal date range, Ledger, and Department ID.
- Specific Journal ID & Date (DOCX): Query will provide Journal information for a Department ID or range of Department IDs based upon journal date range, Ledger, and Department ID.
- 55050 Sub-recipient Journal Detail (DOCX): Query developed to assist agencies in monitoring and identifying those expenditures as journal entries, coded as pass through funds to sub-recipients GL account 55050 for Federal Grant reporting. Each agency is responsible in the correct reporting of expenditures coded as sub awards.
- Commitment Control Ledger / Month (DOCX): A custom PS/Query used to provide Commitment Control Ledger Balance information for a specific commitment control ledger group (e.g., KK_AGY1, KK_ALLOT), fiscal year, period, and department ID or range of department IDs.
More resources