VIEW: APPS.IGC_CBC_DET_BALANCES_V

Source

View Text - Preformatted

SELECT SET_OF_BOOKS_ID, CODE_COMBINATION_ID, BUDGET_VERSION_ID, BUDGET_AMOUNT, COMM_AMOUNT, ACT_AMOUNT, OTH_AMOUNT, COMM_AMOUNT+ACT_AMOUNT+OTH_AMOUNT TOT_AMOUNT, BUDGET_AMOUNT-COMM_AMOUNT-ACT_AMOUNT-OTH_AMOUNT FA_AMOUNT, substr(IGC_CBC_INQUIRY_PKG.Get_amount_type,1,4) AMOUNT_TYPE, substr(IGC_CBC_INQUIRY_PKG.Get_period_name,1,20) PERIOD_NAME, sysdate LAST_UPDATE_DATE, -1 LAST_UPDATED_BY FROM ( SELECT ln.SET_OF_BOOKS_ID, ln.CODE_COMBINATION_ID, MAX(NVL( ln.BUDGET_VERSION_ID,0)) BUDGET_VERSION_ID, SUM(decode(ln.actual_flag,'B',(NVL(ln.ENTERED_DR,0)-NVL(ln.ENTERED_CR,0)),0)) BUDGET_AMOUNT, SUM(decode(ln.actual_flag,'E',decode(ln.ENCUMBRANCE_TYPE_ID,cc_prov_encmbrnc_type_id,(NVL(ln.ENTERED_DR,0)-NVL(ln.ENTERED_CR,0)),0),0)) COMM_AMOUNT, SUM(decode(ln.actual_flag,'E',decode(ln.ENCUMBRANCE_TYPE_ID,cc_conf_encmbrnc_type_id,(NVL(ln.ENTERED_DR,0)-NVL(ln.ENTERED_CR,0)),0),0)) ACT_AMOUNT , SUM(decode(ln.actual_flag,'E',decode(ln.ENCUMBRANCE_TYPE_ID,cc_conf_encmbrnc_type_id,0,cc_prov_encmbrnc_type_id,0,(NVL(ln.ENTERED_DR,0)-NVL(ln.ENTERED_CR,0))),0)) OTH_AMOUNT FROM igc_cbc_je_lines ln, igc_cc_encmbrnc_org_v WHERE IGC_CBC_INQUIRY_PKG.Check_amount_type(ln.period_name,ln.period_year, ln.quarter_num, ln.period_num,actual_flag,budget_version_id)='T' GROUP BY ln.SET_OF_BOOKS_ID,ln.CODE_COMBINATION_ID )

View Text - HTML Formatted

SELECT SET_OF_BOOKS_ID
, CODE_COMBINATION_ID
, BUDGET_VERSION_ID
, BUDGET_AMOUNT
, COMM_AMOUNT
, ACT_AMOUNT
, OTH_AMOUNT
, COMM_AMOUNT+ACT_AMOUNT+OTH_AMOUNT TOT_AMOUNT
, BUDGET_AMOUNT-COMM_AMOUNT-ACT_AMOUNT-OTH_AMOUNT FA_AMOUNT
, SUBSTR(IGC_CBC_INQUIRY_PKG.GET_AMOUNT_TYPE
, 1
, 4) AMOUNT_TYPE
, SUBSTR(IGC_CBC_INQUIRY_PKG.GET_PERIOD_NAME
, 1
, 20) PERIOD_NAME
, SYSDATE LAST_UPDATE_DATE
, -1 LAST_UPDATED_BY
FROM ( SELECT LN.SET_OF_BOOKS_ID
, LN.CODE_COMBINATION_ID
, MAX(NVL( LN.BUDGET_VERSION_ID
, 0)) BUDGET_VERSION_ID
, SUM(DECODE(LN.ACTUAL_FLAG
, 'B'
, (NVL(LN.ENTERED_DR
, 0)-NVL(LN.ENTERED_CR
, 0))
, 0)) BUDGET_AMOUNT
, SUM(DECODE(LN.ACTUAL_FLAG
, 'E'
, DECODE(LN.ENCUMBRANCE_TYPE_ID
, CC_PROV_ENCMBRNC_TYPE_ID
, (NVL(LN.ENTERED_DR
, 0)-NVL(LN.ENTERED_CR
, 0))
, 0)
, 0)) COMM_AMOUNT
, SUM(DECODE(LN.ACTUAL_FLAG
, 'E'
, DECODE(LN.ENCUMBRANCE_TYPE_ID
, CC_CONF_ENCMBRNC_TYPE_ID
, (NVL(LN.ENTERED_DR
, 0)-NVL(LN.ENTERED_CR
, 0))
, 0)
, 0)) ACT_AMOUNT
, SUM(DECODE(LN.ACTUAL_FLAG
, 'E'
, DECODE(LN.ENCUMBRANCE_TYPE_ID
, CC_CONF_ENCMBRNC_TYPE_ID
, 0
, CC_PROV_ENCMBRNC_TYPE_ID
, 0
, (NVL(LN.ENTERED_DR
, 0)-NVL(LN.ENTERED_CR
, 0)))
, 0)) OTH_AMOUNT
FROM IGC_CBC_JE_LINES LN
, IGC_CC_ENCMBRNC_ORG_V
WHERE IGC_CBC_INQUIRY_PKG.CHECK_AMOUNT_TYPE(LN.PERIOD_NAME
, LN.PERIOD_YEAR
, LN.QUARTER_NUM
, LN.PERIOD_NUM
, ACTUAL_FLAG
, BUDGET_VERSION_ID)='T' GROUP BY LN.SET_OF_BOOKS_ID
, LN.CODE_COMBINATION_ID )