--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 2, 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 8th 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 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 2-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_15 and FGBTRNH_15 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_15 D, ORGN, FGBTRNH_15 H, ATITLE, FTVRUCL_CURRENT RUCL
    WHERE     D.FGBTRND_LEDGER_IND = 'O'
          AND D.FGBTRND_FIELD_CODE = '03'
          AND RUCL.FTVRUCL_CLASS_TYPE = 'J'
          AND H.FGBTRNH_RUCL_CODE = RUCL.FTVRUCL_RUCL_CODE
          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.TITLE2,       
       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