View: IGI_GL_BALANCES_V

Product:	IGI - Public Sector Financials International
Description:	Based on GL_BALANCES, GL_PERIOD_STATUSES and GL_SETS_OF_BOOKS
Implementation/DBA Data:	APPS.IGI_GL_BALANCES_V

View Text

SELECT SOB.SET_OF_BOOKS_ID SET_OF_BOOKS_ID
, PS.PERIOD_NAME PERIOD_NAME
, PS.PERIOD_YEAR PERIOD_YEAR
, PS.PERIOD_NUM PERIOD_NUM
, BA.ROWID ROW_ID
, BA.CURRENCY_CODE CURRENCY_CODE
, BA.CODE_COMBINATION_ID CODE_COMBINATION_ID
, BA.ACTUAL_FLAG ACTUAL_FLAG
, BA.ENCUMBRANCE_TYPE_ID ENCUMBRANCE_TYPE_ID
, BA.BUDGET_VERSION_ID BUDGET_VERSION_ID
, BA.TRANSLATED_FLAG TRANSLATED_FLAG
, BA.TEMPLATE_ID TEMPLATE_ID
, GLR03300_PKG.GET_FACTOR FACTOR
, /* * DEBIT AMOUNTS */ DECODE(BA.CODE_COMBINATION_ID
, NULL
, NULL
, (NVL(BA.PERIOD_NET_DR
, 0))) /GLR03300_PKG.GET_FACTOR ENTERED_PERIOD_NET_DR_NUM
, DECODE(BA.CODE_COMBINATION_ID
, NULL
, NULL
, (DECODE(BA.CURRENCY_CODE
, SOB.CURRENCY_CODE
, NULL
, 'STAT'
, NULL
, NVL(BA.PERIOD_NET_DR_BEQ
, 0)))) /GLR03300_PKG.GET_FACTOR FUNCTIONAL_PERIOD_NET_DR
, /* * CREDIT AMOUNTS */ DECODE(BA.CODE_COMBINATION_ID
, NULL
, NULL
, (NVL(BA.PERIOD_NET_CR
, 0))) /GLR03300_PKG.GET_FACTOR ENTERED_PERIOD_NET_CR_NUM
, DECODE(BA.CODE_COMBINATION_ID
, NULL
, NULL
, (DECODE(BA.CURRENCY_CODE
, SOB.CURRENCY_CODE
, NULL
, 'STAT'
, NULL
, NVL(BA.PERIOD_NET_CR_BEQ
, 0)))) /GLR03300_PKG.GET_FACTOR FUNCTIONAL_PERIOD_NET_CR
, /* * QUARTER TO DATE AMOUNT */ DECODE(BA.CODE_COMBINATION_ID
, NULL
, NULL
, ((NVL(BA.QUARTER_TO_DATE_DR
, 0) - NVL(BA.QUARTER_TO_DATE_CR
, 0)) + (NVL(BA.PERIOD_NET_DR
, 0) - NVL(BA.PERIOD_NET_CR
, 0)))) /GLR03300_PKG.GET_FACTOR ENTERED_QUARTER_TO_DATE_NUM
, /* * PROJECT TO DATE AMOUNT */ DECODE(BA.CODE_COMBINATION_ID
, NULL
, NULL
, ((NVL(BA.PROJECT_TO_DATE_DR
, 0) - NVL(BA.PROJECT_TO_DATE_CR
, 0)) + (NVL(BA.PERIOD_NET_DR
, 0) - NVL(BA.PERIOD_NET_CR
, 0)))) /GLR03300_PKG.GET_FACTOR ENTERED_PROJECT_TO_DATE_NUM
, /* * YEAR TO DATE AMOUNTS * IPSAG MODIFIED 7/10/98 */ DECODE(IGIGGACC_PKG.GET_BUD_STATUS_FLAG
, 'Y'
, DECODE(IGIGGACC_PKG.GET_BALANCED_BUDGET_FLAG
, 'Y'
, DECODE(BA.CODE_COMBINATION_ID
, NULL
, NULL
, ((NVL(BA.BEGIN_BALANCE_DR
, 0)- NVL(BA.BEGIN_BALANCE_CR
, 0))+ (NVL(BA.PERIOD_NET_DR
, 0)- NVL(BA.PERIOD_NET_CR
, 0)))) /GLR03300_PKG.GET_FACTOR
, DECODE(IGIGGACC_PKG.GET_AVERAGE_UNBALANCED
, 'N'
, DECODE(BA.CODE_COMBINATION_ID
, NULL
, NULL
, ((NVL(BA.BEGIN_BALANCE_DR
, 0)- NVL(BA.BEGIN_BALANCE_CR
, 0))+ (NVL(BA.PERIOD_NET_DR
, 0)- NVL(BA.PERIOD_NET_CR
, 0)))) /GLR03300_PKG.GET_FACTOR
, (DECODE(BA.CODE_COMBINATION_ID
, NULL
, NULL
, ((NVL(BA.BEGIN_BALANCE_DR
, 0)- NVL(BA.BEGIN_BALANCE_CR
, 0))+ (NVL(BA.PERIOD_NET_DR
, 0)- NVL(BA.PERIOD_NET_CR
, 0)))) /GLR03300_PKG.GET_FACTOR)/BA.PERIOD_NUM))
, DECODE(BA.CODE_COMBINATION_ID
, NULL
, NULL
, ((NVL(BA.BEGIN_BALANCE_DR
, 0) - NVL(BA.BEGIN_BALANCE_CR
, 0)) + (NVL(BA.PERIOD_NET_DR
, 0) - NVL(BA.PERIOD_NET_CR
, 0)))) /GLR03300_PKG.GET_FACTOR) ENTERED_YEAR_TO_DATE_NUM
, DECODE(BA.CODE_COMBINATION_ID
, NULL
, NULL
, (DECODE(BA.CURRENCY_CODE
, SOB.CURRENCY_CODE
, NULL
, 'STAT'
, NULL
, (NVL(BA.BEGIN_BALANCE_DR_BEQ
, 0) - NVL(BA.BEGIN_BALANCE_CR_BEQ
, 0)) + (NVL(BA.PERIOD_NET_DR_BEQ
, 0) - NVL(BA.PERIOD_NET_CR_BEQ
, 0))))) /GLR03300_PKG.GET_FACTOR FUNCTIONAL_YEAR_TO_DATE
, /* * YEAR TO DATE AMOUNTS */ DECODE(BA.CODE_COMBINATION_ID
, NULL
, NULL
, ((NVL(BA.PERIOD_NET_DR
, 0) - NVL(BA.PERIOD_NET_CR
, 0)))) /GLR03300_PKG.GET_FACTOR ENTERED_PERIOD_TO_DATE_NUM
, DECODE(BA.CODE_COMBINATION_ID
, NULL
, NULL
, (DECODE(BA.CURRENCY_CODE
, SOB.CURRENCY_CODE
, NULL
, 'STAT'
, NULL
, (NVL(BA.PERIOD_NET_DR_BEQ
, 0) - NVL(BA.PERIOD_NET_CR_BEQ
, 0))))) /GLR03300_PKG.GET_FACTOR FUNCTIONAL_PERIOD_TO_DATE
, BA.LAST_UPDATE_DATE LAST_UPDATE_DATE
, BA.LAST_UPDATED_BY LAST_UPDATED_BY
FROM GL_BALANCES BA
, GL_PERIOD_STATUSES PS
, GL_SETS_OF_BOOKS SOB
WHERE NVL(DECODE(BA.TRANSLATED_FLAG(+)
, 'N'
, 'Y'
, BA.TRANSLATED_FLAG(+))
, DECODE(GLR03300_PKG.GET_ACTUAL_FLAG
, 'A'
, DECODE(GLR03300_PKG.GET_CURRENCY_CODE
, GLR03300_PKG.GET_FUNCTIONAL_CURRENCY_CODE
, 'R'
, 'STAT'
, 'R'
, DECODE(GLR03300_PKG.GET_TRANSLATED_FLAG
, 'E'
, DECODE(BA.CURRENCY_CODE(+)
, GLR03300_PKG.GET_FUNCTIONAL_CURRENCY_CODE
, 'R'
, 'STAT'
, 'R'
, DECODE(BA.TEMPLATE_ID(+)
, NULL
, 'R'
, 'X'))
, DECODE(BA.TEMPLATE_ID(+)
, NULL
, 'R'
, 'Y')))
, 'B'
, DECODE(GLR03300_PKG.GET_CURRENCY_CODE
, GLR03300_PKG.GET_FUNCTIONAL_CURRENCY_CODE
, 'R'
, 'STAT'
, 'R'
, DECODE(GLR03300_PKG.GET_TRANSLATED_FLAG
, 'E'
, DECODE(BA.CURRENCY_CODE(+)
, GLR03300_PKG.GET_FUNCTIONAL_CURRENCY_CODE
, 'R'
, 'STAT'
, 'R'
, DECODE(BA.TEMPLATE_ID(+)
, NULL
, 'R'
, 'X'))
, DECODE(BA.TEMPLATE_ID(+)
, NULL
, 'R'
, 'Y')))
, 'E'
, 'E')) = DECODE(GLR03300_PKG.GET_ACTUAL_FLAG
, 'A'
, DECODE(GLR03300_PKG.GET_CURRENCY_CODE
, GLR03300_PKG.GET_FUNCTIONAL_CURRENCY_CODE
, 'R'
, 'STAT'
, 'R'
, DECODE(GLR03300_PKG.GET_TRANSLATED_FLAG
, 'E'
, 'R'
, 'Y'))
, 'B'
, DECODE(GLR03300_PKG.GET_CURRENCY_CODE
, GLR03300_PKG.GET_FUNCTIONAL_CURRENCY_CODE
, 'R'
, 'STAT'
, 'R'
, DECODE(GLR03300_PKG.GET_TRANSLATED_FLAG
, 'E'
, 'R'
, 'Y'))
, 'E'
, 'E')
AND NVL(BA.BUDGET_VERSION_ID(+)
, -100) = NVL(GLR03300_PKG.GET_BUDGET_VERSION_ID
, -100)
AND NVL(BA.ENCUMBRANCE_TYPE_ID(+)
, -100) = NVL(GLR03300_PKG.GET_ENCUMBRANCE_TYPE_ID
, -100)
AND NVL(BA.TEMPLATE_ID(+)
, -100) = NVL(GLR03300_PKG.GET_TEMPLATE_ID
, -100)
AND BA.CODE_COMBINATION_ID(+) = GLR03300_PKG.GET_CODE_COMBINATION_ID
AND BA.ACTUAL_FLAG(+) = GLR03300_PKG.GET_ACTUAL_FLAG
AND BA.CURRENCY_CODE(+) = DECODE(GLR03300_PKG.GET_CURRENCY_OPTION
, 'A'
, DECODE(GLR03300_PKG.GET_TRANSLATED_FLAG
, 'T'
, DECODE(BA.CURRENCY_CODE(+)
, GLR03300_PKG.GET_FUNCTIONAL_CURRENCY_CODE
, NULL
, 'STAT'
, NULL
, BA.CURRENCY_CODE(+))
, BA.CURRENCY_CODE(+))
, GLR03300_PKG.GET_CURRENCY_CODE)
AND BA.PERIOD_NAME(+) = PS.PERIOD_NAME
AND BA.SET_OF_BOOKS_ID(+) = PS.SET_OF_BOOKS_ID
AND PS.APPLICATION_ID = 101
AND PS.SET_OF_BOOKS_ID = SOB.SET_OF_BOOKS_ID

Columns