VIEW: APPS.IGC_CBC_DR_V

Source

View Text - Preformatted

SELECT CCV.ROWID ROW_ID, CCV.CC_HEADER_ID CC_HEADER_ID, CCV.CC_ACCT_LINE_ID CC_ACCT_LINE_ID, CCV.CODE_COMBINATION_ID CODE_COMBINATION_ID, CCV.BATCH_LINE_NUM BATCH_LINE_NUM, CCV.CC_TRANSACTION_DATE CC_TRANSACTION_DATE, CCV.CC_FUNC_DR_AMT CC_FUNC_DR_AMT, CCV.CC_FUNC_CR_AMT CC_FUNC_CR_AMT, NVL(CCV.CC_FUNC_DR_AMT,0) - NVL(CCV.CC_FUNC_CR_AMT,0) TRANSACTION_AMOUNT, CCV.JE_SOURCE_NAME JE_SOURCE_NAME, CCV.JE_CATEGORY_NAME JE_CATEGORY_NAME, CCV.PERIOD_SET_NAME PERIOD_SET_NAME, CCV.PERIOD_NAME PERIOD_NAME, GPS.PERIOD_YEAR PERIOD_YEAR, GPS.PERIOD_NUM PERIOD_NUM, CCV.ACTUAL_FLAG ACTUAL_FLAG, GL2.DESCRIPTION BALANCE_TYPE, CCV.RESULT_CODE_SOURCE RESULT_CODE_SOURCE, CCV.BUDGET_DEST_FLAG BUDGET_DEST_FLAG, FL.MEANING BUDGET_TYPE, CCV.REFERENCE_10 AMOUNT_TYPE_CODE, GL4.MEANING AMOUNT_TYPE, CCV.SET_OF_BOOKS_ID SET_OF_BOOKS_ID, CCV.ENCUMBRANCE_TYPE_ID ENCUMBRANCE_TYPE_ID, GET.ENCUMBRANCE_TYPE ENCUMBRANCE_TYPE, CCV.CBC_RESULT_CODE CBC_RESULT_CODE, DECODE(CCV.CBC_RESULT_CODE, NULL, 'This account has not been funds checked', GL1.DESCRIPTION) RESULT_CODE_MEANING, CCV.STATUS_CODE STATUS_CODE, DECODE(ccv.status_code, NULL, 'Not Funds Checked', GL3.MEANING) STATUS_CODE_MEANING, CCV.BUDGET_VERSION_ID BUDGET_VERSION_ID, GBV.BUDGET_NAME BUDGET_NAME, CCV.BUDGET_AMT BUDGET_AMT, CCV.COMMITMENT_ENCMBRNC_AMT COMMITMENT_ENCMBRNC_AMT, CCV.OBLIGATION_ENCMBRNC_AMT OBLIGATION_ENCMBRNC_AMT, NVL(CCV.COMMITMENT_ENCMBRNC_AMT,0) + NVL(OBLIGATION_ENCMBRNC_AMT,0) ENCUMBRED_AMOUNT, CCV.FUNDS_AVAILABLE_AMT FUNDS_AVAILABLE_AMT, CCV.CURRENCY_CODE CURRENCY_CODE, CCV.TRANSACTION_DESCRIPTION TRANSACTION_DESCRIPTION, CCV.LAST_UPDATE_DATE LAST_UPDATE_DATE, CCV.LAST_UPDATED_BY LAST_UPDATED_BY, CCV.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN, CCV.CREATION_DATE CREATION_DATE, CCV.CREATED_BY CREATED_BY, CCV.REFERENCE_6, CCV.DOCUMENT_TYPE FROM IGC_CC_INTERFACE_V CCV,GL_LOOKUPS GL1, GL_LOOKUPS GL2,GL_ENCUMBRANCE_TYPES GET, GL_LOOKUPS GL3,GL_BUDGET_VERSIONS GBV, GL_PERIOD_STATUSES GPS, FND_LOOKUPS FL,GL_LOOKUPS GL4 WHERE GL1.LOOKUP_TYPE(+) = 'FUNDS_CHECK_RESULT_CODE' AND GL1.LOOKUP_CODE(+) = CCV.CBC_RESULT_CODE AND GL2.LOOKUP_TYPE = 'BATCH_TYPE' AND GL2.LOOKUP_CODE = CCV.ACTUAL_FLAG AND GET.ENCUMBRANCE_TYPE_ID(+) = CCV.ENCUMBRANCE_TYPE_ID AND GL3.LOOKUP_TYPE(+) = 'FUNDS_CHECK_STATUS_CODE' AND GL3.LOOKUP_CODE(+) = CCV.STATUS_CODE AND GBV.BUDGET_VERSION_ID(+) = CCV.BUDGET_VERSION_ID AND FL.LOOKUP_TYPE(+) = 'IGC_CC_BUDGET_TYPE' AND FL.LOOKUP_CODE(+) = CCV.BUDGET_DEST_FLAG AND GPS.APPLICATION_ID(+) = 101 AND GPS.SET_OF_BOOKS_ID(+) = CCV.SET_OF_BOOKS_ID AND GPS.PERIOD_NAME(+) = CCV.PERIOD_NAME AND GL4.LOOKUP_TYPE(+) = 'PTD_YTD' AND GL4.LOOKUP_CODE(+) = CCV.REFERENCE_10

View Text - HTML Formatted

SELECT CCV.ROWID ROW_ID
, CCV.CC_HEADER_ID CC_HEADER_ID
, CCV.CC_ACCT_LINE_ID CC_ACCT_LINE_ID
, CCV.CODE_COMBINATION_ID CODE_COMBINATION_ID
, CCV.BATCH_LINE_NUM BATCH_LINE_NUM
, CCV.CC_TRANSACTION_DATE CC_TRANSACTION_DATE
, CCV.CC_FUNC_DR_AMT CC_FUNC_DR_AMT
, CCV.CC_FUNC_CR_AMT CC_FUNC_CR_AMT
, NVL(CCV.CC_FUNC_DR_AMT
, 0) - NVL(CCV.CC_FUNC_CR_AMT
, 0) TRANSACTION_AMOUNT
, CCV.JE_SOURCE_NAME JE_SOURCE_NAME
, CCV.JE_CATEGORY_NAME JE_CATEGORY_NAME
, CCV.PERIOD_SET_NAME PERIOD_SET_NAME
, CCV.PERIOD_NAME PERIOD_NAME
, GPS.PERIOD_YEAR PERIOD_YEAR
, GPS.PERIOD_NUM PERIOD_NUM
, CCV.ACTUAL_FLAG ACTUAL_FLAG
, GL2.DESCRIPTION BALANCE_TYPE
, CCV.RESULT_CODE_SOURCE RESULT_CODE_SOURCE
, CCV.BUDGET_DEST_FLAG BUDGET_DEST_FLAG
, FL.MEANING BUDGET_TYPE
, CCV.REFERENCE_10 AMOUNT_TYPE_CODE
, GL4.MEANING AMOUNT_TYPE
, CCV.SET_OF_BOOKS_ID SET_OF_BOOKS_ID
, CCV.ENCUMBRANCE_TYPE_ID ENCUMBRANCE_TYPE_ID
, GET.ENCUMBRANCE_TYPE ENCUMBRANCE_TYPE
, CCV.CBC_RESULT_CODE CBC_RESULT_CODE
, DECODE(CCV.CBC_RESULT_CODE
, NULL
, 'THIS ACCOUNT HAS NOT BEEN FUNDS CHECKED'
, GL1.DESCRIPTION) RESULT_CODE_MEANING
, CCV.STATUS_CODE STATUS_CODE
, DECODE(CCV.STATUS_CODE
, NULL
, 'NOT FUNDS CHECKED'
, GL3.MEANING) STATUS_CODE_MEANING
, CCV.BUDGET_VERSION_ID BUDGET_VERSION_ID
, GBV.BUDGET_NAME BUDGET_NAME
, CCV.BUDGET_AMT BUDGET_AMT
, CCV.COMMITMENT_ENCMBRNC_AMT COMMITMENT_ENCMBRNC_AMT
, CCV.OBLIGATION_ENCMBRNC_AMT OBLIGATION_ENCMBRNC_AMT
, NVL(CCV.COMMITMENT_ENCMBRNC_AMT
, 0) + NVL(OBLIGATION_ENCMBRNC_AMT
, 0) ENCUMBRED_AMOUNT
, CCV.FUNDS_AVAILABLE_AMT FUNDS_AVAILABLE_AMT
, CCV.CURRENCY_CODE CURRENCY_CODE
, CCV.TRANSACTION_DESCRIPTION TRANSACTION_DESCRIPTION
, CCV.LAST_UPDATE_DATE LAST_UPDATE_DATE
, CCV.LAST_UPDATED_BY LAST_UPDATED_BY
, CCV.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, CCV.CREATION_DATE CREATION_DATE
, CCV.CREATED_BY CREATED_BY
, CCV.REFERENCE_6
, CCV.DOCUMENT_TYPE
FROM IGC_CC_INTERFACE_V CCV
, GL_LOOKUPS GL1
, GL_LOOKUPS GL2
, GL_ENCUMBRANCE_TYPES GET
, GL_LOOKUPS GL3
, GL_BUDGET_VERSIONS GBV
, GL_PERIOD_STATUSES GPS
, FND_LOOKUPS FL
, GL_LOOKUPS GL4
WHERE GL1.LOOKUP_TYPE(+) = 'FUNDS_CHECK_RESULT_CODE'
AND GL1.LOOKUP_CODE(+) = CCV.CBC_RESULT_CODE
AND GL2.LOOKUP_TYPE = 'BATCH_TYPE'
AND GL2.LOOKUP_CODE = CCV.ACTUAL_FLAG
AND GET.ENCUMBRANCE_TYPE_ID(+) = CCV.ENCUMBRANCE_TYPE_ID
AND GL3.LOOKUP_TYPE(+) = 'FUNDS_CHECK_STATUS_CODE'
AND GL3.LOOKUP_CODE(+) = CCV.STATUS_CODE
AND GBV.BUDGET_VERSION_ID(+) = CCV.BUDGET_VERSION_ID
AND FL.LOOKUP_TYPE(+) = 'IGC_CC_BUDGET_TYPE'
AND FL.LOOKUP_CODE(+) = CCV.BUDGET_DEST_FLAG
AND GPS.APPLICATION_ID(+) = 101
AND GPS.SET_OF_BOOKS_ID(+) = CCV.SET_OF_BOOKS_ID
AND GPS.PERIOD_NAME(+) = CCV.PERIOD_NAME
AND GL4.LOOKUP_TYPE(+) = 'PTD_YTD'
AND GL4.LOOKUP_CODE(+) = CCV.REFERENCE_10