VIEW: APPS.FV_GTAS_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.ACCOUNT_NUMBER ACCOUNT_NUMBER, A.FUND_VALUE FUND_VALUE, 'R' REPORTED_STATUS, A.USSGL_ACCOUNT USSGL_ACCOUNT, A.EXCH_NON_EXCH EXCH_NON_EXCH, A.CUST_NON_CUST CUST_NON_CUST, A.ACCOUNT_TYPE ACCOUNT_TYPE, PERIOD_BEGIN_BAL PERIOD_BEGIN_BAL, PERIOD_CY_DR_BAL PERIOD_DR, PERIOD_CY_CR_BAL PERIOD_CR, DECODE(SIGN(BALANCE_AMOUNT), -1,'C','D') D_C_INDICATOR, 'G' BALANCE_TYPE, A.DIRECT_OR_REIMB_CODE, A.APPOR_CAT_CODE, A.APPOR_CAT_B_CODE, A.PROGRAM_REPT_CODE, A.BEA_CAT_CODE, A.BORR_SRC_CODE, A.NEW_BAL_CODE, A.CUR_SUBSEQUENT_CODE, A.PYA_CODE, A.CREDIT_COHORT_YR, A.PROGRAM_COST_IND, A.TREASURY_SYMBOL_ID, NULL budget_impact_ind, NULL authority_type_code, NULL fed_non_fed, NULL trading_partner_agency_id, NULL TRADING_PARTNER_MAIN_ACCOUNT, A.END_BAL_IND END_BAL_IND, 'B' BEGIN_END FROM FV_GTAS1_PERIOD_ATTRIBUTES A WHERE a.reported_group = '1' 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.ACCOUNT_NUMBER ACCOUNT_NUMBER, A.FUND_VALUE FUND_VALUE, 'R' REPORTED_STATUS, A.USSGL_ACCOUNT USSGL_ACCOUNT, A.EXCH_NON_EXCH EXCH_NON_EXCH, A.CUST_NON_CUST CUST_NON_CUST, A.ACCOUNT_TYPE ACCOUNT_TYPE, B.AMOUNT PERIOD_BEGIN_BAL, 0 PERIOD_DR, 0 PERIOD_CR, B.D_C_INDICATOR D_C_INDICATOR, 'P' BALANCE_TYPE, A.DIRECT_OR_REIMB_CODE, A.APPOR_CAT_CODE, A.APPOR_CAT_B_CODE, A.PROGRAM_REPT_CODE, A.BEA_CAT_CODE, A.BORR_SRC_CODE, A.NEW_BAL_CODE, A.CUR_SUBSEQUENT_CODE, A.PYA_CODE, A.CREDIT_COHORT_YR, A.PROGRAM_COST_IND, A.TREASURY_SYMBOL_ID, B.budget_impact_ind, B.authority_type_code, B.fed_non_fed, B.trading_partner_agency_id, B.TRADING_PARTNER_MAIN_ACCOUNT, A.END_BAL_IND END_BAL_IND, 'B' BEGIN_END FROM FV_gtas1_PERIOD_ATTRIBUTES A, FV_gtas_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' 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.ACCOUNT_NUMBER ACCOUNT_NUMBER, A.FUND_VALUE FUND_VALUE, 'R' REPORTED_STATUS, A.USSGL_ACCOUNT USSGL_ACCOUNT, A.EXCH_NON_EXCH EXCH_NON_EXCH, A.CUST_NON_CUST CUST_NON_CUST, A.ACCOUNT_TYPE ACCOUNT_TYPE, DECODE(B.PERIOD_NUM, A.PERIOD_NUM, 0, B.AMOUNT) PERIOD_BEGIN_BAL, 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, B.D_C_INDICATOR D_C_INDICATOR, 'L' BALANCE_TYPE, A.DIRECT_OR_REIMB_CODE, A.APPOR_CAT_CODE, A.APPOR_CAT_B_CODE, A.PROGRAM_REPT_CODE, A.BEA_CAT_CODE, A.BORR_SRC_CODE, A.NEW_BAL_CODE, A.CUR_SUBSEQUENT_CODE, A.PYA_CODE, A.CREDIT_COHORT_YR, A.PROGRAM_COST_IND, A.TREASURY_SYMBOL_ID, B.budget_impact_ind, B.authority_type_code, B.fed_non_fed, B.trading_partner_agency_id, B.TRADING_PARTNER_MAIN_ACCOUNT, A.END_BAL_IND END_BAL_IND, 'E' BEGIN_END FROM FV_gtas1_PERIOD_ATTRIBUTES A, fv_gtas_activity_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.ACCOUNT_NUMBER ACCOUNT_NUMBER
, A.FUND_VALUE FUND_VALUE
, 'R' REPORTED_STATUS
, A.USSGL_ACCOUNT USSGL_ACCOUNT
, A.EXCH_NON_EXCH EXCH_NON_EXCH
, A.CUST_NON_CUST CUST_NON_CUST
, A.ACCOUNT_TYPE ACCOUNT_TYPE
, PERIOD_BEGIN_BAL PERIOD_BEGIN_BAL
, PERIOD_CY_DR_BAL PERIOD_DR
, PERIOD_CY_CR_BAL PERIOD_CR
, DECODE(SIGN(BALANCE_AMOUNT)
, -1
, 'C'
, 'D') D_C_INDICATOR
, 'G' BALANCE_TYPE
, A.DIRECT_OR_REIMB_CODE
, A.APPOR_CAT_CODE
, A.APPOR_CAT_B_CODE
, A.PROGRAM_REPT_CODE
, A.BEA_CAT_CODE
, A.BORR_SRC_CODE
, A.NEW_BAL_CODE
, A.CUR_SUBSEQUENT_CODE
, A.PYA_CODE
, A.CREDIT_COHORT_YR
, A.PROGRAM_COST_IND
, A.TREASURY_SYMBOL_ID
, NULL BUDGET_IMPACT_IND
, NULL AUTHORITY_TYPE_CODE
, NULL FED_NON_FED
, NULL TRADING_PARTNER_AGENCY_ID
, NULL TRADING_PARTNER_MAIN_ACCOUNT
, A.END_BAL_IND END_BAL_IND
, 'B' BEGIN_END
FROM FV_GTAS1_PERIOD_ATTRIBUTES A
WHERE A.REPORTED_GROUP = '1' 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.ACCOUNT_NUMBER ACCOUNT_NUMBER
, A.FUND_VALUE FUND_VALUE
, 'R' REPORTED_STATUS
, A.USSGL_ACCOUNT USSGL_ACCOUNT
, A.EXCH_NON_EXCH EXCH_NON_EXCH
, A.CUST_NON_CUST CUST_NON_CUST
, A.ACCOUNT_TYPE ACCOUNT_TYPE
, B.AMOUNT PERIOD_BEGIN_BAL
, 0 PERIOD_DR
, 0 PERIOD_CR
, B.D_C_INDICATOR D_C_INDICATOR
, 'P' BALANCE_TYPE
, A.DIRECT_OR_REIMB_CODE
, A.APPOR_CAT_CODE
, A.APPOR_CAT_B_CODE
, A.PROGRAM_REPT_CODE
, A.BEA_CAT_CODE
, A.BORR_SRC_CODE
, A.NEW_BAL_CODE
, A.CUR_SUBSEQUENT_CODE
, A.PYA_CODE
, A.CREDIT_COHORT_YR
, A.PROGRAM_COST_IND
, A.TREASURY_SYMBOL_ID
, B.BUDGET_IMPACT_IND
, B.AUTHORITY_TYPE_CODE
, B.FED_NON_FED
, B.TRADING_PARTNER_AGENCY_ID
, B.TRADING_PARTNER_MAIN_ACCOUNT
, A.END_BAL_IND END_BAL_IND
, 'B' BEGIN_END
FROM FV_GTAS1_PERIOD_ATTRIBUTES A
, FV_GTAS_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' 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.ACCOUNT_NUMBER ACCOUNT_NUMBER
, A.FUND_VALUE FUND_VALUE
, 'R' REPORTED_STATUS
, A.USSGL_ACCOUNT USSGL_ACCOUNT
, A.EXCH_NON_EXCH EXCH_NON_EXCH
, A.CUST_NON_CUST CUST_NON_CUST
, A.ACCOUNT_TYPE ACCOUNT_TYPE
, DECODE(B.PERIOD_NUM
, A.PERIOD_NUM
, 0
, B.AMOUNT) PERIOD_BEGIN_BAL
, 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
, B.D_C_INDICATOR D_C_INDICATOR
, 'L' BALANCE_TYPE
, A.DIRECT_OR_REIMB_CODE
, A.APPOR_CAT_CODE
, A.APPOR_CAT_B_CODE
, A.PROGRAM_REPT_CODE
, A.BEA_CAT_CODE
, A.BORR_SRC_CODE
, A.NEW_BAL_CODE
, A.CUR_SUBSEQUENT_CODE
, A.PYA_CODE
, A.CREDIT_COHORT_YR
, A.PROGRAM_COST_IND
, A.TREASURY_SYMBOL_ID
, B.BUDGET_IMPACT_IND
, B.AUTHORITY_TYPE_CODE
, B.FED_NON_FED
, B.TRADING_PARTNER_AGENCY_ID
, B.TRADING_PARTNER_MAIN_ACCOUNT
, A.END_BAL_IND END_BAL_IND
, 'E' BEGIN_END
FROM FV_GTAS1_PERIOD_ATTRIBUTES A
, FV_GTAS_ACTIVITY_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'