ACT Data WarehouseQueriesDB2WikiSQL Executer (db2)Link Family
|
Did You Get What You Wanted?
|
| | cavouras |
|
Standard SQL Queries - Financial/Ledger
Financial Ledger
Standard SQL Query Scripts
-
Amount of money for a specified account spent by a Department within specified Accounting Periods
-
Departmental Ledger Transactions and Amounts by Accounting Period
-
Ledger Account Titles used within an Accounting Period by Index Number
-
On-line Transfer of Funds by Calendar Year and Department Organization Number
-
Account Summary by period, fund, organization, and account
-
Operating ledger summary
-
Amount of money spent for Temporary Help by Division between specified Accounting Periods
|
Query Name:
|
Certified?
|
|
Description:
|
|
SQL:
|
|
|
Parameters that may be modified are indicated in RED. When parameters are changed, the customer should validate that the returned data is what they desire.
|
|
|
Amount of money for a specified account spent by a Department within Accounting Periods
|
Certified.
|
|
Returns the account index, fund, amount and accounting period by a specified Accounting Period Range within a specified Department Organization Number for a specified account. The parameters for this query are: Department Organization Number 417660, Accounting Period Range > 199801 and Account 636055.
|
|
SELECT pi_account_index, pf_fund, current_mo_financial_amount, full_accounting_period
FROM f_ifoapal
WHERE po_organization = '417660'
AND pa_account = '636055'
AND full_accounting_period > 199801
ORDER BY pi_account_index
|
|
|
|
|
|
|
Departmental Ledger Transactions and Amounts by Accounting Period
|
|
|
Returns the document number and the amount for the each of a specified department's transactions for a specified fiscal accounting period. The parameters for this query are: Department Organization Number 724501 and Accounting Period 9803.
|
|
SELECT DISTINCT lt_document_number, lt_amount
FROM f_ifoapal A, f_ledger_activity B, f_ledger_transaction C
WHERE A.po_organization = '724501'
AND A.full_accounting_period = 199803
AND A.if_id = B.if_id
AND B.lt_id = C.lt_id
|
|
|
|
|
|
|
Ledger Account Titles used within an Accounting Period by Index Number
|
Certified.
|
|
Lists all the accounts, including their titles, used during a specified fiscal accounting period for a specified index number. The parameters for this query are: Accounting Period 9801 and Index Number ADC5027.
|
|
SELECT A.pa_account, pa_title
FROM f_ifoapal A, f_period_account B
WHERE A.full_accounting_period = 199801
AND A.pi_account_index = 'ADC5027'
AND A.pa_account = B.pa_account
AND A.accounting_period = B.accounting_period
|
|
|
|
|
|
|
On-line Transfer of Funds by Calendar Year and Department Organization Number
|
Certified.
|
|
Returns the On-line Transfer of Funds transactions for a specified calendar year by a specified department organization number. The parameters for this query are: Calendar Start Date 1997-01-01, Calendar End Date 1997-12-31, and Department Organization Number 724501.
|
|
SELECT DISTINCT B.th_document_number, th_document_date, th_document_amount
FROM tf_transfer_header A, tf_transfer_detail B
WHERE th_document_date BETWEEN '1997-01-01'
AND '1997-12-31'
AND po_organization = '724501'
AND A.th_document_number = B.th_document_number
|
|
|
|
|
|
|
Account summary by period, fund, organization, and account.
|
Certified.
|
|
Returns the total bufget, financial, and encumberance operating ledger amounts for specified accounts, funds, and timeframe. The parameters for this query are: Fund 73100A, Beginning Account 633000, Ending Account 659999, Beginning Fiscal Period 199800, and Ending Fiscal Period 199810.
|
|
SELECT
A.full_accounting_period PERIOD,
A.pf_fund FUND,
A.po_organization ORG,
A.pa_account ACCT,
sum(current_mo_budget_amount) BUDGET,
sum(current_mo_financial_amount) FINANCIAL,
sum(current_mo_encumbrance_amount) ENCUMB,
pa_title ACCT_TITLE,
po_title ORG_TITLE,
pf_title FUND_TITLE,
substring(A.pa_account,1,2) SUB
FROM
f_ifoapal A (index i_ifoapal_fund),
f_period_account C,
f_period_organization D,
f_period_fund E
WHERE
A.pf_fund = '73100A'
AND
A.pa_account >= '633000'
AND
A.pa_account <= '659999'
AND
A.full_accounting_period >= 199800
AND
A.full_accounting_period <= 199810
AND
A.pa_account = C.pa_account
AND
A.accounting_period = C.accounting_period
AND
A.po_organization = D.po_organization
AND
A.accounting_period = D.accounting_period
AND
A.pf_fund = E.pf_fund
AND
A.accounting_period = E.accounting_period
GROUP BY
A.full_accounting_period,
A.pf_fund,
A.po_organization,
A.pa_account,
pa_title,
po_title,
pf_title,
substring(A.pa_account,1,2)
|
|
|
|
|
|
|
Operating ledger Summary
|
Certified.
|
|
Returns the Operating Ledger information for a specified fiscal period. This
is the stored procedure used by Financial Link to produce the Summary
Operating Ledgers. For Detailed instructions on input parameters and
output fields, refer to the
proc_ol_main Design Document
The parameters used in this query are Accounting Period 199812, Ledger Date 1998-06-3
0, Include Period 14, Flag N, Index GRS8817,
Fund 76325A, Organization 721601 Account 7, Progra
m 724030 and Location ''.
|
|
exec proc_ol_main 199812, '1998-06-30', 'N', 'GRS8817', '76325A', '721601', '7', '724030', ''
|
|
|
|
|
|
|
Amount of money spent for Temporary Help by Division between specified Accounting Periods
|
Certified.
|
|
Returns the temporary help expenditures by a specified division organization code and full accounting periods. The parameters for this query are: Division Organization Code DAAAAA, Beginning Full Accounting Period 199800, and Ending Full Accounting Period 199814.
|
|
SELECT A.orgn_code, orgn_code_title
INTO #org_hier
FROM coa_db..orgnhier_table A, coa_db..orgn_table B
WHERE code_1 = 'DAAAAA'
AND A.orgn_code = B.orgn_code
AND B.start_date =
(select max(start_date)
from coa_db..orgn_table C
where C.unvrs_code = '01'
AND C.coa_code = 'A'
AND A.orgn_code = C.orgn_code)
SELECT pi_account_index, orgn_code_title, pf_fund,current_mo_financial_amount, full_accounting_period
FROM f_ifoapal, #org_hier
WHERE po_organization = orgn_code
AND pa_account = '636055'
AND full_accounting_period BETWEEN 199800 and 199814
ORDER BY pi_account_index
|
|
|
|
|
|
