Filetype Excel XLSX | Posted on 08 Aug 2022 | 3 years ago
Authentication
digital resources
325x Filetype XLSX File size 0.03 MB Source: dof.ca.gov
Department of Finance
FI$Cal Reports and Queries (Rev. 6/30/2022)
The Department of Finance has created a “DOF” folder to store public queries available to users. The query names begin with “DFQ”.
To access an item, navigate to: Main Menu > Reporting Tools > Query > Query Viewer and select the Report/Query Name shown below.
User inquiries regarding: “Query Access Issues” should be resolved by FSC at: (855) 347-2250 (Toll Free) or fsc@fiscal.ca.gov
“Query Data Issues” should be forwarded to your department’s DOF Accounting Analyst.
Business
Area/
Module Query Query Name Description Purpose Query Date
Allocations Query DFQ_ALO_01_KK_ACTIVITY MEC Allocations Used to: 1) validate initial allocation configurations, 2) determine whether 3/7/2017
allocation steps ran as expected, and 3) filter data to see portions of each
step. This query shows the entries created in the ByPass Ledgers
(ALLC_ENC/ALLC_EXP) during the MEC Allocations process.
Asset Query DFQ_AM_01_ASSET_ACCTG_ENTRY Asset Accounting Entries Use to reconcile assets, and review depreciation entries. 4/26/2019
Management
Asset Query DFQ_AM_02_ASSET_DETAIL Asset Information Detail Use to review all assets in AM module. 4/26/2019
Management
Accounts Query DFQ_AP_01_EXP_ACCRUAL AP Expenditure Accrual Displays AP expenditure accrual information as per the original query with 3/7/2017
Payable the following modifications:
- Does not display Vouchers that have cleared
- Displays the appropriate Due To Account instead of the generic '50'
account
- The Fund, ENY, Program, and Ref fields are now optional
Accounts Query DFQ_AP_02_VOUCHERS_LISTING List of AP Voucher w/ Pmt Info Used to obtain a list of AP Vouchers with payment information. Includes 7/28/2017
Payable SCO Document ID (GERs) for TEA Vouchers.
Accounts Query DFQ_AP_03_FEDERAL_ACTIVITY Federal Vouchers for Draw Used to obtain a list of AP Vouchers for federal draw using the Scheduled 7/28/2017
Payable Date.
Accounts Query DFQ_AP_04_SUPPLIER_SEARCH List of Suppliers Used to obtain a list of Suppliers with address information. 12/15/2017
Payable
Accounts Query DFQ_AP_05_VOUCHER_BUILD_ERRORS Voucher Build Error Used to extract all transactions that are in Voucher Build Error and require 6/29/2018
Payable resolution in order to successfully build into a voucher. Commonly used
to resolve errors related to CalATERS interface vouchers and US Bank P-
Card interface vouchers.
Accounts Query DFQ_AP_06_VOUCHER_ACCTG_TRANS Voucher Accounting Transaction Used for researching purposes to find all related accounting transactions 6/29/2018
Payable for a specific voucher(s).
Accounts Query DFQ_AP_07_SPEEDCHARTS SpeedCharts - (AP & PO) Used to extract SpeedChart data with Active status. 7/13/2018
Payable
Accounts Query DFQ_AP_08_BOE_USE_TAX Vouchers with Use Tax (BOE) Used to extract vouchers with use tax amount. 10/22/2018
Payable
Accounts Query DFQ_AP_09_ACCRUAL Payables Accrual Detail - TB/GL Displays the detail of all payable accrual accounts. Use this to reconcile 5/16/2019
Payable against Trial Balance.
Accounts Query DFQ_AP_10_ACCRUAL_PROJECT AP Accrual DTL w/ Proj - TB/GL Displays the detail of all payable accrual accounts including project and 5/16/2019
Payable activity. Use this to reconcile against Trial Balance.
Accounts Query DFQ_AP_11_VCHR_SUB_SYS_ENTRIES AP Voucher Sub-System Entries Displays all the accounting transactions related to a voucher. Includes 4/3/2020
Payable cash reclassification entries. This query includes chartfield information,
voucher ID, Type and Origin.
*A report based on the query is in development. Subscribers of the FI$Cal Training Classes a n d e L e a r n ing Courses mailing list will be notified when available. Page 1 of 9
Department of Finance
FI$Cal Reports and Queries (Rev. 6/30/2022)
The Department of Finance has created a “DOF” folder to store public queries available to users. The query names begin with “DFQ”.
To access an item, navigate to: Main Menu > Reporting Tools > Query > Query Viewer and select the Report/Query Name shown below.
User inquiries regarding: “Query Access Issues” should be resolved by FSC at: (855) 347-2250 (Toll Free) or fsc@fiscal.ca.gov
“Query Data Issues” should be forwarded to your department’s DOF Accounting Analyst.
Business
Area/
Module Query Query Name Description Purpose Query Date
Accounts Query DFQ_AP_12_VCHR_APPROV_HISTORY AP Voucher Approval History Displays approval history status for vouchers. Includes departmental and 4/3/2020
Payable SCO approval information.This query includes departmental AP 1 and 2
approval information in addition to SCO approval status. Fields include
Voucher ID, Voucher Style, Origin, Supplier Information, Voucher Status,
and Approval status.
Accounts Query DFQ_AR_01_PENDING_ITEM_BY_DTL Pending Item w/ Dist.&Customer Used to obtain AR pending item transactions by detail. AR Processor or 3/7/2017
Receivable Approver can review the AR pending item transactions by distribution
lines. Query data has Group Unit, Group ID, Accounting Date, Posting
Status, Customer ID, Name, Item ID, Amount and Distribution lines.
Accounts Query DFQ_AR_02_ABATEMENT_ACCRUAL All Abatement Accruals Displays AR items and Abatements accrual information as per the original 7/28/2017
Receivable query with the following modifications:
- Displays all AR accounts including AR-Other and Deferred Receivables
- Update Prompts display
Accounts Query DFQ_AR_03_AP_AR_ACCRUAL AR and AP Accruals Combines AR abatement and AP expenditure Accrual query into one 7/28/2017
Receivable query. Displays all AR and AP expenditure Accrual.
- Update Prompts display
Accounts Query DFQ_AR_04_ITEMS_VALIDATION List of AR Items Used to obtain a list of posted AR item Transactions. 8/4/2017
Receivable
Accounts Query DFQ_AR_05_CUSTOMERS AR Customers List Used to obtain a list of AR Customers. 8/4/2017
Receivable
Accounts Query DFQ_AR_06_AGING_REPORT AR Aging Report Provides an AR aging report that displays starting balance, payments, 12/15/2017
Receivable ending balance, aging category, and customer conversations related to
the AR item.
Accounts Query DFQ_AR_07_CASH_RECEIPTS Cash Receipts Report Used to extract data of AR cash receipts detail. Useful for reconciliations 6/29/2018
Receivable or AR and revenues.
Accounts Query DFQ_AR_08_SPEEDTYPES SpeedTypes - (GL & AR) Used to extract SpeedTypes data. 7/13/2018
Receivable
Accounts Query DFQ_AR_09_CUSTOMERS_ACCT_HIST Breakdown By Cust Account History Use query to reconcile Accounts Receivable. This query will list original 7/27/2018
Receivable invoice and the amount written off.
Accounts Query DFQ_AR_10_DEPOSIT_SLIPS_LIST Deposit Slips List Use to obtain a list of Deposit Slip Transactions. 10/22/2018
Receivable
Accounts Query DFQ_AR_11_INCOMPLETE_DEPOSITS List of Incomplete Deposits Use to obtain a list of AR Deposits which are Incomplete and must be 1/25/2019
Receivable resolved prior to month end close.
Accounts Query DFQ_AR_12_INCOMPLETE_PAYMENTS List of Incomplete Payments Use to obtain a list of AR Payments which are Incomplete and must be 1/25/2019
Receivable resolved prior to month end close.
*A report based on the query is in development. Subscribers of the FI$Cal Training Classes a n d e L e a r n ing Courses mailing list will be notified when available. Page 2 of 9
Department of Finance
FI$Cal Reports and Queries (Rev. 6/30/2022)
The Department of Finance has created a “DOF” folder to store public queries available to users. The query names begin with “DFQ”.
To access an item, navigate to: Main Menu > Reporting Tools > Query > Query Viewer and select the Report/Query Name shown below.
User inquiries regarding: “Query Access Issues” should be resolved by FSC at: (855) 347-2250 (Toll Free) or fsc@fiscal.ca.gov
“Query Data Issues” should be forwarded to your department’s DOF Accounting Analyst.
Business
Area/
Module Query Query Name Description Purpose Query Date
Accounts Query DFQ_AR_13_CASH_RECEIPT_DETAIL Cash Receipt Detail Used to obtain a list of Detail AR Cash Receipts. Useful for 2/15/2019
Receivable reconciliations of billed and unbilled revenue. Additional fields include
Location Code and SCO Documents ID.
Note: If more than one payment sequence with identical funding occurs
within a deposit, only the first payment sequence will show the SCO
Document ID.
Accounts Query DFQ_AR_14_ACCRUAL AR Accrual Detail - TB/GL Displays the detail of all receivable accrual accounts. Use this to 6/6/2019
Receivable reconcile against Trial Balance.
Accounts Query DFQ_AR_15_ACCRUAL_PROJECT AR Accrual DTL w/ Proj - TB/GL Displays the detail of all receivable accrual accounts including project and 6/6/2019
Receivable activity. Use this to reconcile against Trial Balance.
Accounts Query DFQ_AR_16_AP_AR_ACCRUAL AP & AR Accrual Detail - TB/GL Combines the DFQ_AR_14_ACCRUAL and DFQ_AP_09_ACCRUAL 6/21/2019
Receivable queries into one query. Displays the detail of all receivable and payable
accrual accounts. Use this to reconcile against Trial Balance.
Accounts Query DFQ_AR_17_DIRECT_JRNL_SUB_SYS AR Direct Jrnl Sub-Sys Entries Displays all the accounting transactions related to an AR direct journal. 4/3/2020
Receivable This query includes chartfield information, Deposit ID, and Payment
Sequence.
Accounts Query DFQ_AR_18_ITEM_SUB_SYS_ENTRIES AR Item Sub-System Entries Displays all the accounting transactions related to an AR Item. This query 4/3/2020
Receivable includes chartfield information, Customer ID and Item ID.
Accounts Query DFQ_AR_19_DJP_APPROVAL_STATUS AR Direct Jrnl Approval Status Displays AR approval status for direct journal payments. 7/17/2020
Receivable
Accounts Query DFQ_AR_20_UNREMIT_DEPOSIT_YEC YEC Unremitted Deposit FY2019 Used to obtain a list of Deposits with accounting date 6/30/2020 and prior 7/17/2020
Receivable and payment date 7/1/2020 and after. This query will be used to assist
with the year-end accrual.
Accounts Query DFQ_AR_21_ITEM_AGING Item Aging by AR User Line Provides information to help monitor the AR aging by item “As Of Date.” 1/14/2021
Receivable This query captures the data from the AR User Line only.
Accounts Query DFQ_AR_22_PENDING_ITEMS_VALID AR Pending Items Validation Used to obtain a list of pending AR item Transactions. 2/11/2021
Receivable
Accounts Query DFQ_AR_23_OPEN_ITEM_DETAIL AR Open Item by Chartfield Used to obtain a detailed list of open AR items that includes all 6/7/2021
Receivable departmental lower Chartfields.
Accounts Query DFQ_AR_24_OPEN_ITEM_OFFSET AR Open Item w/ Offset Account Use to obtain a detail list of open AR items with the offset chartfields at 6/30/2022
Receivable the departmental level (AR and User Lines).
Cash Query DFQ_CM_01_CCP_CRD_PYMT_XREF CTS Acct Recon Cross-Reference* Used for CTS bank reconciliation. This query cross references the 3/3/2017
Management Voucher Number, Journal ID, Payment Reference, and Deposit ID. BU
and Accounting Date (To/From) are required fields and wildcard (%) is not
permitted. Accounting period is optional. All other fields are required and
wildcard (%) is permitted.
*A report based on the query is in development. Subscribers of the FI$Cal Training Classes a n d e L e a r n ing Courses mailing list will be notified when available. Page 3 of 9
Department of Finance
FI$Cal Reports and Queries (Rev. 6/30/2022)
The Department of Finance has created a “DOF” folder to store public queries available to users. The query names begin with “DFQ”.
To access an item, navigate to: Main Menu > Reporting Tools > Query > Query Viewer and select the Report/Query Name shown below.
User inquiries regarding: “Query Access Issues” should be resolved by FSC at: (855) 347-2250 (Toll Free) or fsc@fiscal.ca.gov
“Query Data Issues” should be forwarded to your department’s DOF Accounting Analyst.
Business
Area/
Module Query Query Name Description Purpose Query Date
Cash Query DFQ_CM_02_BANK_REC_XREF Bank Recon Cross-Reference Used in CTS bank reconciliation. Departments will reconcile their General 4/12/2019
Management Cash, Revolving Fund Cash, Agency Trust Fund Cash, and Pending Cash
accounts with their State Controller’s Office, Centralized Treasury Trust
System Account Statement bank balance, adjusted for deposits in-transit,
outstanding checks, and other reconciling items.
General Query DFQ_GL_01_JOURNAL_DETAIL GL Journal Detail Transactions Used to obtain GL journal (detail transactions) at a point of time for 5/25/2017
Ledger research purposes. The GL Processor or GL Approver can view all
transactions that are sent to the GL including both system and non-
system generated transactions.
General Query DFQ_GL_02_PFA_TRANSACTIONS PFA Transactions Used to verify PFA transactions to make sure transactions within the 6/29/2018
Ledger Program and Fund and ENY net to zero.
General Query DFQ_GL_03_REC_ACCRUAL_SUMMARY Accrual Exclude CNV &GL8-TB/GL Used to reconcile against Trial Balance. Displays a summary level of all 6/5/2020
Ledger receivable and payable accrual accounts. This query is a summary of the
DFQ_GL_04_REC_ACCRUAL.
The SCO/FI$Cal Document Extract extension must be run to populate this
query.
General Query DFQ_GL_04_REC_ACCRUAL AP AR Accrual w/ Doc ID TB/GL Used to reconcile against Trial Balance. Added a common field 6/5/2020
Ledger (Document ID) to the existing DFQ_AR_16_AP_AR_ACCRUAL query.
This query displays the detail of all receivable and payable accrual
accounts.
The SCO/FI$Cal Document Extract extension must be run to populate this
query.
• Document ID is a common field exisitng in two or more files, allowing the
user to match records/transactions from one file (SCO Tab Run) with the
General Query DFQ_GL_05_REC_KK_APPROP_LEVEL KK w/ Doc ID - DTL_EXP/DTL_REC Used to reconcile appropriation level activity for expenditures and 6/5/2020
record/transactions in the other files (KK/Accruals).
Ledger revenues. Added a common field (Document ID) to the existing
DFQ_KK_14_APPROP_ACTIVITY_LEVL query. This query returns both
the C_DTL_EXP & C_DTL_REC ledgers.
The SCO/FI$Cal Document Extract extension must be run to populate this
query.
• Document ID is a common field exisitng in two or more files, allowing the
user to match records/transactions from one file (SCO Tab Run) with the
record/transactions in the other files (KK/Accruals).
General Query DFQ_GL_06_REC_SCO_TAB_RUN SCO Tab Run Activity-No T Acct Used to reconcile appropriation level activity for expenditures and 6/5/2020
Ledger revenues. Added a common field (SCO Doc ID) to the existing SCO Tab
Run data for the database.
The SCO/FI$Cal Document Extract extension must be run to populate this
query.
*A report based on the query is in development. Subscribers of the FI$Cal Training Classes a n d e L e a r n ing Courses mailing list will be notified when available. Page 4 of 9
no reviews yet
Please Login to review.
