VIEW: APPS.FV_FACTS_PERIOD_BALANCES_TB_V

Source

View Text - Preformatted

SELECT A.CCID CCID, A.SET_OF_BOOKS_ID SET_OF_BOOKS_ID, A.PERIOD_YEAR PERIOD_YEAR, A.PERIOD_NUM PERIOD_NUM, A.PERIOD_NAME PERIOD_NAME, A.FUND_GROUP FUND_GROUP, A.ACCOUNT_NUMBER ACCOUNT_NUMBER, A.FUND_VALUE FUND_VALUE, A.DEPT_ID DEPT_ID, A.BUREAU_ID BUREAU_ID, '  ' ELIMINATIONS_DEPT , A.G_NG_INDICATOR G_NG_INDICATOR , A.END_BAL_IND END_BAL_IND , 'R' REPORTED_STATUS , A.USSGL_ACCOUNT USSGL_ACCOUNT , A.EXCH_NON_EXCH EXCH_NON_EXCH , A.CUST_NON_CUST CUST_NON_CUST , A.BUDGET_SUBFUNCTION BUDGET_SUBFUNCTION , A.ACCOUNT_TYPE ACCOUNT_TYPE , BALANCE_AMOUNT AMOUNT, BEGIN_BALANCE BEGINNING_BALANCE, PERIOD_CY_DR_BAL PERIOD_DR, PERIOD_CY_CR_BAL PERIOD_CR, PERIOD_BEGIN_BAL PERIOD_BEGIN_BAL, DECODE(SIGN(BALANCE_AMOUNT), -1,'C','D') D_C_INDICATOR, 'G' BALANCE_TYPE, NULL RECIPIENT_NAME FROM FV_FACTS1_PERIOD_ATTRIBUTES A where a.reported_group IN ('1', 'E') UNION ALL SELECT A.CCID CCID, A.SET_OF_BOOKS_ID SET_OF_BOOKS_ID, A.PERIOD_YEAR PERIOD_YEAR, B.PERIOD_NUM PERIOD_NUM, A.PERIOD_NAME PERIOD_NAME, A.FUND_GROUP FUND_GROUP, A.ACCOUNT_NUMBER ACCOUNT_NUMBER, A.FUND_VALUE FUND_VALUE, A.DEPT_ID DEPT_ID, A.BUREAU_ID BUREAU_ID, DECODE(a.g_ng_indicator, 'F', DECODE(b.eliminations_dept, null, '00', b.eliminations_dept), 'Y', DECODE(b.party_classification, 'FEDERAL', DECODE(b.eliminations_dept, null, '00', b.eliminations_dept), NULL,b.eliminations_dept, '  ')) ELIMINATIONS_DEPT, DECODE(a.g_ng_indicator, 'Y', DECODE(b.party_classification, 'FEDERAL', 'F', NULL, DECODE(TRIM(B.ATTRIBUTE_VALUE),NULL,'N','F'), 'N'), a.g_ng_indicator) G_NG_INDICATOR, A.END_BAL_IND END_BAL_IND , 'R' REPORTED_STATUS , A.USSGL_ACCOUNT USSGL_ACCOUNT , A.EXCH_NON_EXCH EXCH_NON_EXCH , A.CUST_NON_CUST CUST_NON_CUST , A.BUDGET_SUBFUNCTION BUDGET_SUBFUNCTION , A.ACCOUNT_TYPE ACCOUNT_TYPE , B.AMOUNT AMOUNT , 0 BEGINNING_BALANCE , DECODE(B.PERIOD_NUM, A.PERIOD_NUM, DECODE(B.D_C_INDICATOR, 'D', B.AMOUNT, 0),0) PERIOD_DR, DECODE(B.PERIOD_NUM, A.PERIOD_NUM, DECODE(B.D_C_INDICATOR, 'C', ABS(B.AMOUNT), 0),0) PERIOD_CR, DECODE(B.PERIOD_NUM, A.PERIOD_NUM, 0, B.AMOUNT) PERIOD_BEGIN_BAL , B.D_C_INDICATOR D_C_INDICATOR, B.BALANCE_TYPE BALANCE_TYPE, B.RECIPIENT_NAME RECIPIENT_NAME FROM FV_FACTS1_PERIOD_ATTRIBUTES A, FV_FACTS1_LINE_BALANCES B WHERE a.ccid = b.ccid AND a.period_year = b.period_year AND a.set_of_books_id = b.set_of_books_id AND a.reported_group = '2' UNION ALL SELECT A.CCID CCID, A.SET_OF_BOOKS_ID SET_OF_BOOKS_ID, A.PERIOD_YEAR PERIOD_YEAR, A.PERIOD_NUM PERIOD_NUM, A.PERIOD_NAME PERIOD_NAME, B.FUND_GROUP FUND_GROUP, A.ACCOUNT_NUMBER ACCOUNT_NUMBER, A.FUND_VALUE FUND_VALUE, A.DEPT_ID DEPT_ID, A.BUREAU_ID BUREAU_ID, B.ELIMINATIONS_DEPT ELIMINATIONS_DEPT , B.G_NG_INDICATOR G_NG_INDICATOR , A.END_BAL_IND END_BAL_IND , 'R' REPORTED_STATUS , A.USSGL_ACCOUNT USSGL_ACCOUNT , A.EXCH_NON_EXCH EXCH_NON_EXCH , A.CUST_NON_CUST CUST_NON_CUST , A.BUDGET_SUBFUNCTION BUDGET_SUBFUNCTION , A.ACCOUNT_TYPE ACCOUNT_TYPE , 0 AMOUNT , 0 BEGINNING_BALANCE , 0 PERIOD_DR, 0 PERIOD_CR, B.AMOUNT PERIOD_BEGIN_BAL , B.D_C_INDICATOR D_C_INDICATOR, 'E' BALANCE_TYPE, B.RECIPIENT_NAME RECIPIENT_NAME FROM FV_FACTS1_PERIOD_ATTRIBUTES A, FV_FACTS_ENDING_BALANCES B WHERE a.ccid = b.ccid AND a.period_year = b.fiscal_year + 1 AND a.set_of_books_id = b.set_of_books_id AND a.reported_group = '2' AND a.account_type IN ('A','L','O') UNION ALL SELECT A.CCID CCID, A.SET_OF_BOOKS_ID SET_OF_BOOKS_ID, A.PERIOD_YEAR PERIOD_YEAR, A.PERIOD_NUM PERIOD_NUM, A.PERIOD_NAME PERIOD_NAME, A.FUND_GROUP FUND_GROUP, A.ACCOUNT_NUMBER ACCOUNT_NUMBER, A.FUND_VALUE FUND_VALUE, A.DEPT_ID DEPT_ID, A.BUREAU_ID BUREAU_ID, DECODE(B.G_NG_INDICATOR , 'F','00','  ') ELIMINATIONS_DEPT , DECODE(B.G_NG_INDICATOR , 'F','F','N') G_NG_INDICATOR , A.END_BAL_IND END_BAL_IND , 'R' REPORTED_STATUS , A.USSGL_ACCOUNT USSGL_ACCOUNT , A.EXCH_NON_EXCH EXCH_NON_EXCH , A.CUST_NON_CUST CUST_NON_CUST , A.BUDGET_SUBFUNCTION BUDGET_SUBFUNCTION , A.ACCOUNT_TYPE ACCOUNT_TYPE , B.AMOUNT AMOUNT , 0 BEGINNING_BALANCE , DECODE(B.BALANCE_TYPE , 'D', DECODE(B.D_C_INDICATOR, 'D', B.AMOUNT, 0), 0) PERIOD_DR, DECODE(B.BALANCE_TYPE , 'D', DECODE(B.D_C_INDICATOR, 'C', ABS(B.AMOUNT), 0), 0) PERIOD_CR, DECODE(B.BALANCE_TYPE, 'B', B.AMOUNT, 0) PERIOD_BEGIN_BAL , DECODE(SIGN(B.AMOUNT), -1,'C','D') D_C_INDICATOR, B.BALANCE_TYPE BALANCE_TYPE, B.RECIPIENT_NAME RECIPIENT_NAME FROM FV_FACTS1_PERIOD_ATTRIBUTES A, FV_FACTS1_DIFF_BALANCES B WHERE a.ccid = b.ccid AND a.period_year = b.period_year AND a.set_of_books_id = b.set_of_books_id AND a.reported_group = '2'

View Text - HTML Formatted

SELECT A.CCID CCID
, A.SET_OF_BOOKS_ID SET_OF_BOOKS_ID
, A.PERIOD_YEAR PERIOD_YEAR
, A.PERIOD_NUM PERIOD_NUM
, A.PERIOD_NAME PERIOD_NAME
, A.FUND_GROUP FUND_GROUP
, A.ACCOUNT_NUMBER ACCOUNT_NUMBER
, A.FUND_VALUE FUND_VALUE
, A.DEPT_ID DEPT_ID
, A.BUREAU_ID BUREAU_ID
, ' ' ELIMINATIONS_DEPT
, A.G_NG_INDICATOR G_NG_INDICATOR
, A.END_BAL_IND END_BAL_IND
, 'R' REPORTED_STATUS
, A.USSGL_ACCOUNT USSGL_ACCOUNT
, A.EXCH_NON_EXCH EXCH_NON_EXCH
, A.CUST_NON_CUST CUST_NON_CUST
, A.BUDGET_SUBFUNCTION BUDGET_SUBFUNCTION
, A.ACCOUNT_TYPE ACCOUNT_TYPE
, BALANCE_AMOUNT AMOUNT
, BEGIN_BALANCE BEGINNING_BALANCE
, PERIOD_CY_DR_BAL PERIOD_DR
, PERIOD_CY_CR_BAL PERIOD_CR
, PERIOD_BEGIN_BAL PERIOD_BEGIN_BAL
, DECODE(SIGN(BALANCE_AMOUNT)
, -1
, 'C'
, 'D') D_C_INDICATOR
, 'G' BALANCE_TYPE
, NULL RECIPIENT_NAME
FROM FV_FACTS1_PERIOD_ATTRIBUTES A
WHERE A.REPORTED_GROUP IN ('1'
, 'E') UNION ALL SELECT A.CCID CCID
, A.SET_OF_BOOKS_ID SET_OF_BOOKS_ID
, A.PERIOD_YEAR PERIOD_YEAR
, B.PERIOD_NUM PERIOD_NUM
, A.PERIOD_NAME PERIOD_NAME
, A.FUND_GROUP FUND_GROUP
, A.ACCOUNT_NUMBER ACCOUNT_NUMBER
, A.FUND_VALUE FUND_VALUE
, A.DEPT_ID DEPT_ID
, A.BUREAU_ID BUREAU_ID
, DECODE(A.G_NG_INDICATOR
, 'F'
, DECODE(B.ELIMINATIONS_DEPT
, NULL
, '00'
, B.ELIMINATIONS_DEPT)
, 'Y'
, DECODE(B.PARTY_CLASSIFICATION
, 'FEDERAL'
, DECODE(B.ELIMINATIONS_DEPT
, NULL
, '00'
, B.ELIMINATIONS_DEPT)
, NULL
, B.ELIMINATIONS_DEPT
, ' ')) ELIMINATIONS_DEPT
, DECODE(A.G_NG_INDICATOR
, 'Y'
, DECODE(B.PARTY_CLASSIFICATION
, 'FEDERAL'
, 'F'
, NULL
, DECODE(TRIM(B.ATTRIBUTE_VALUE)
, NULL
, 'N'
, 'F')
, 'N')
, A.G_NG_INDICATOR) G_NG_INDICATOR
, A.END_BAL_IND END_BAL_IND
, 'R' REPORTED_STATUS
, A.USSGL_ACCOUNT USSGL_ACCOUNT
, A.EXCH_NON_EXCH EXCH_NON_EXCH
, A.CUST_NON_CUST CUST_NON_CUST
, A.BUDGET_SUBFUNCTION BUDGET_SUBFUNCTION
, A.ACCOUNT_TYPE ACCOUNT_TYPE
, B.AMOUNT AMOUNT
, 0 BEGINNING_BALANCE
, DECODE(B.PERIOD_NUM
, A.PERIOD_NUM
, DECODE(B.D_C_INDICATOR
, 'D'
, B.AMOUNT
, 0)
, 0) PERIOD_DR
, DECODE(B.PERIOD_NUM
, A.PERIOD_NUM
, DECODE(B.D_C_INDICATOR
, 'C'
, ABS(B.AMOUNT)
, 0)
, 0) PERIOD_CR
, DECODE(B.PERIOD_NUM
, A.PERIOD_NUM
, 0
, B.AMOUNT) PERIOD_BEGIN_BAL
, B.D_C_INDICATOR D_C_INDICATOR
, B.BALANCE_TYPE BALANCE_TYPE
, B.RECIPIENT_NAME RECIPIENT_NAME
FROM FV_FACTS1_PERIOD_ATTRIBUTES A
, FV_FACTS1_LINE_BALANCES B
WHERE A.CCID = B.CCID
AND A.PERIOD_YEAR = B.PERIOD_YEAR
AND A.SET_OF_BOOKS_ID = B.SET_OF_BOOKS_ID
AND A.REPORTED_GROUP = '2' UNION ALL SELECT A.CCID CCID
, A.SET_OF_BOOKS_ID SET_OF_BOOKS_ID
, A.PERIOD_YEAR PERIOD_YEAR
, A.PERIOD_NUM PERIOD_NUM
, A.PERIOD_NAME PERIOD_NAME
, B.FUND_GROUP FUND_GROUP
, A.ACCOUNT_NUMBER ACCOUNT_NUMBER
, A.FUND_VALUE FUND_VALUE
, A.DEPT_ID DEPT_ID
, A.BUREAU_ID BUREAU_ID
, B.ELIMINATIONS_DEPT ELIMINATIONS_DEPT
, B.G_NG_INDICATOR G_NG_INDICATOR
, A.END_BAL_IND END_BAL_IND
, 'R' REPORTED_STATUS
, A.USSGL_ACCOUNT USSGL_ACCOUNT
, A.EXCH_NON_EXCH EXCH_NON_EXCH
, A.CUST_NON_CUST CUST_NON_CUST
, A.BUDGET_SUBFUNCTION BUDGET_SUBFUNCTION
, A.ACCOUNT_TYPE ACCOUNT_TYPE
, 0 AMOUNT
, 0 BEGINNING_BALANCE
, 0 PERIOD_DR
, 0 PERIOD_CR
, B.AMOUNT PERIOD_BEGIN_BAL
, B.D_C_INDICATOR D_C_INDICATOR
, 'E' BALANCE_TYPE
, B.RECIPIENT_NAME RECIPIENT_NAME
FROM FV_FACTS1_PERIOD_ATTRIBUTES A
, FV_FACTS_ENDING_BALANCES B
WHERE A.CCID = B.CCID
AND A.PERIOD_YEAR = B.FISCAL_YEAR + 1
AND A.SET_OF_BOOKS_ID = B.SET_OF_BOOKS_ID
AND A.REPORTED_GROUP = '2'
AND A.ACCOUNT_TYPE IN ('A'
, 'L'
, 'O') UNION ALL SELECT A.CCID CCID
, A.SET_OF_BOOKS_ID SET_OF_BOOKS_ID
, A.PERIOD_YEAR PERIOD_YEAR
, A.PERIOD_NUM PERIOD_NUM
, A.PERIOD_NAME PERIOD_NAME
, A.FUND_GROUP FUND_GROUP
, A.ACCOUNT_NUMBER ACCOUNT_NUMBER
, A.FUND_VALUE FUND_VALUE
, A.DEPT_ID DEPT_ID
, A.BUREAU_ID BUREAU_ID
, DECODE(B.G_NG_INDICATOR
, 'F'
, '00'
, ' ') ELIMINATIONS_DEPT
, DECODE(B.G_NG_INDICATOR
, 'F'
, 'F'
, 'N') G_NG_INDICATOR
, A.END_BAL_IND END_BAL_IND
, 'R' REPORTED_STATUS
, A.USSGL_ACCOUNT USSGL_ACCOUNT
, A.EXCH_NON_EXCH EXCH_NON_EXCH
, A.CUST_NON_CUST CUST_NON_CUST
, A.BUDGET_SUBFUNCTION BUDGET_SUBFUNCTION
, A.ACCOUNT_TYPE ACCOUNT_TYPE
, B.AMOUNT AMOUNT
, 0 BEGINNING_BALANCE
, DECODE(B.BALANCE_TYPE
, 'D'
, DECODE(B.D_C_INDICATOR
, 'D'
, B.AMOUNT
, 0)
, 0) PERIOD_DR
, DECODE(B.BALANCE_TYPE
, 'D'
, DECODE(B.D_C_INDICATOR
, 'C'
, ABS(B.AMOUNT)
, 0)
, 0) PERIOD_CR
, DECODE(B.BALANCE_TYPE
, 'B'
, B.AMOUNT
, 0) PERIOD_BEGIN_BAL
, DECODE(SIGN(B.AMOUNT)
, -1
, 'C'
, 'D') D_C_INDICATOR
, B.BALANCE_TYPE BALANCE_TYPE
, B.RECIPIENT_NAME RECIPIENT_NAME
FROM FV_FACTS1_PERIOD_ATTRIBUTES A
, FV_FACTS1_DIFF_BALANCES B
WHERE A.CCID = B.CCID
AND A.PERIOD_YEAR = B.PERIOD_YEAR
AND A.SET_OF_BOOKS_ID = B.SET_OF_BOOKS_ID
AND A.REPORTED_GROUP = '2'