--The query asks the user for the level2 (cabinet), level 3 (unit), level 4 (major category), level 5 (category), or org #.
--Enter % in any field to pull everything at that level. The user is also prompted for the cycle (01-12)
--or enter % for all cycles.
--The results list every direct expenditure transaction that occurred during the cycle provided by
--fund/org/acct. Columns include fiscal year, level/title 3, level/title 4, level/title 5, fund,
--org, acct/title, prog code, transaction date, cycle, document #, reference # (found in deposits, etc.),
--sequence # (can be deleted upon import in to Excel), transaction description, and amount.
--To query a different acct code range, change the numbers in red on the 9th line of the actual query
--(blue "WITH" is the 1st line).
WITH
--ATITLE: Matches the acct code range specified below with the account title from FTVACCT_ACCT_CODE table
ATITLE AS
(SELECT A.FTVACCT_TITLE AS TTL,
A.FTVACCT_ACCT_CODE AS ACCT
FROM REPORTS.FTVACCT_CURRENT A
-- EDIT LINE BELOW TO PULL DIFFERENT ACCT CODE RANGES
WHERE A.FTVACCT_ACCT_CODE BETWEEN '2000' AND '8999'
),
--ORGN: Matches the org or org level specified by the user and returns levels 3-5 in the results
ORGN AS
(SELECT DISTINCT
O.LEVEL2,
O.TITLE2,
O.LEVEL3,
O.TITLE3,
O.LEVEL4,
O.TITLE4,
O.LEVEL5,
O.TITLE5,
O.ORGN_CODE AS ORG
FROM FTVORGN_LEVELS O
WHERE O.LEVEL2 LIKE :LEVEL2 /*Level 2 or % for ALL*/
AND O.LEVEL3 LIKE :LEVEL3 /*Level 3 or % for ALL*/
AND O.LEVEL4 LIKE :LEVEL4 /*Level 4 or % for ALL*/
AND O.LEVEL5 LIKE :LEVEL5 /*Level 5 or % for ALL*/
AND O.ORGN_CODE LIKE :ORG /*Org or % for ALL*/
AND O.DATA_ENTRY_IND = 'Y'),
--TRAN: Joins the FGBTRND and FGBTRNH tables and returns YTD data (field code '03') based on the
--cycle entered by the user, the specified acct range in ATITLE, and the org range entered by the user
TRAN AS
(SELECT DISTINCT
D.FGBTRND_FSYR_CODE AS FY,
D.FGBTRND_FUND_CODE AS FUND,
D.FGBTRND_ACCT_CODE AS ACCT,
D.FGBTRND_DOC_CODE AS DOC,
D.FGBTRND_PROG_CODE AS PROG,
D.FGBTRND_ACTV_CODE AS ACTV,
D.FGBTRND_SUBMISSION_NUMBER AS SUB,
D.FGBTRND_ORGN_CODE AS ORG,
D.FGBTRND_TRANS_AMT AS AMT,
D.FGBTRND_ITEM_NUM AS ITEM,
H.FGBTRNH_TRANS_DATE AS TRANS_DATE,
H.FGBTRNH_TRANS_DESC AS TRANS_DESC,
D.FGBTRND_POSTING_PERIOD AS CY,
H.FGBTRNH_DOC_REF_NUM AS REFNUM,
D.FGBTRND_SEQ_NUM AS SEQNUM
FROM FGBTRND D, ORGN, FGBTRNH H, ATITLE
WHERE D.FGBTRND_FSYR_CODE LIKE :FY /*18 thru Current FY or % for ALL*/
AND D.FGBTRND_LEDGER_IND = 'O'
AND D.FGBTRND_FIELD_CODE = '03'
AND D.FGBTRND_ORGN_CODE = ORGN.ORG
AND D.FGBTRND_ACCT_CODE = ATITLE.ACCT
AND D.FGBTRND_POSTING_PERIOD LIKE :CY /*CYCLE 01-12 OR % for ALL*/
AND D.FGBTRND_DOC_CODE = H.FGBTRNH_DOC_CODE
AND D.FGBTRND_SUBMISSION_NUMBER = H.FGBTRNH_SUBMISSION_NUMBER
AND D.FGBTRND_ITEM_NUM = H.FGBTRNH_ITEM_NUM
AND D.FGBTRND_SEQ_NUM = H.FGBTRNH_SEQ_NUM
AND D.FGBTRND_FUND_CODE = H.FGBTRNH_FUND_CODE
AND D.FGBTRND_ORGN_CODE = H.FGBTRNH_ORGN_CODE
AND D.FGBTRND_ACCT_CODE = H.FGBTRNH_ACCT_CODE
AND D.FGBTRND_FSYR_CODE = H.FGBTRNH_FSYR_CODE
)
SELECT DISTINCT
TRAN.FY,
ORGN.LEVEL2,
ORGN.LEVEL3,
ORGN.TITLE3,
ORGN.LEVEL4,
ORGN.TITLE4,
ORGN.LEVEL5,
ORGN.TITLE5,
TRAN.FUND,
TRAN.ORG,
TRAN.ACCT,
ATITLE.TTL AS ACCT_TITLE,
TRAN.PROG,
TRAN.ACTV,
TRAN.TRANS_DATE,
TRAN.CY,
TRAN.DOC,
TRAN.REFNUM,
TRAN.SEQNUM,
TRAN.TRANS_DESC,
TRAN.AMT
FROM TRAN, ATITLE, ORGN
WHERE ATITLE.ACCT = TRAN.ACCT
AND TRAN.ORG = ORGN.ORG
ORDER BY ORG ASC,
FUND ASC,
ACCT ASC,
TRANS_DATE ASC