eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.PSA_BC_REPORT_V

Source

View Text - Preformatted


SELECT b.row_id ROW_ID, b.APPLICATION_ID APPLICATION_ID, psav.APPLICATION_SHORT_NAME APPLICATION_SHORT_NAME, psav.APPLICATION_NAME, b.LEDGER_ID LEDGER_ID, b.EVENT_ID EVENT_ID, b.SOURCE_DISTRIBUTION_TYPE SOURCE_DISTRIBUTION_TYPE, b.SOURCE_DISTRIBUTION_ID_NUM_1 SOURCE_DISTRIBUTION_ID_NUM_1, b.SOURCE_DISTRIBUTION_ID_NUM_2 SOURCE_DISTRIBUTION_ID_NUM_2, b.SOURCE_DISTRIBUTION_ID_NUM_3 SOURCE_DISTRIBUTION_ID_NUM_3, b.SOURCE_DISTRIBUTION_ID_NUM_4 SOURCE_DISTRIBUTION_ID_NUM_4, b.SOURCE_DISTRIBUTION_ID_NUM_5 SOURCE_DISTRIBUTION_ID_NUM_5, b.SOURCE_DISTRIBUTION_ID_CHAR_1 SOURCE_DISTRIBUTION_ID_CHAR_1, b.SOURCE_DISTRIBUTION_ID_CHAR_2 SOURCE_DISTRIBUTION_ID_CHAR_2, b.SOURCE_DISTRIBUTION_ID_CHAR_3 SOURCE_DISTRIBUTION_ID_CHAR_3, b.SOURCE_DISTRIBUTION_ID_CHAR_4 SOURCE_DISTRIBUTION_ID_CHAR_4, b.SOURCE_DISTRIBUTION_ID_CHAR_5 SOURCE_DISTRIBUTION_ID_CHAR_5, b.packet_id PACKET_ID, b.je_source_name JE_SOURCE_NAME, b.je_category_name JE_CATEGORY_NAME, b.code_combination_id CODE_COMBINATION_ID, b.actual_flag ACTUAL_FLAG, b.period_name PERIOD_NAME, b.currency_code CURRENCY_CODE, b.status_code STATUS_CODE, bv1.budget_type BUDGET_TYPE, b.budget_version_id BUDGET_VERSION_ID, b.encumbrance_type_id ENCUMBRANCE_TYPE_ID, b.template_id TEMPLATE_ID, b.entered_dr ENTERED_DR, b.entered_cr ENTERED_CR, DECODE ( b.status_code, 'C',NULL, 'P',NULL, DECODE ( nvl ( b.funds_check_level_code, 'N') , 'N',NULL, DECODE ( b.effect_on_funds_code, 'D', DECODE ( b.result_code, 'P01',NULL, 'P02',NULL, 'P03',NULL, 'P04',NULL, 'P05',NULL, 'P06',NULL, 'P07',NULL, 'P08',NULL, 'P09',NULL, 'F20',NULL, 'F21',NULL, 'F22',NULL, 'F23',NULL, 'F24',NULL, 'F25',NULL, 'F26',NULL, 'F27',NULL, 'F28',NULL, 'F29',NULL, nvl ( b.accounted_dr,0 ) ) ,NULL ) ) ) ACCOUNTED_DR, DECODE ( b.status_code, 'C',NULL, 'P',NULL, DECODE ( nvl ( b.funds_check_level_code, 'N') , 'N',NULL, DECODE ( b.effect_on_funds_code, 'D', DECODE ( b.result_code, 'P01',NULL, 'P02',NULL, 'P03',NULL, 'P04',NULL, 'P05',NULL, 'P06',NULL, 'P07',NULL, 'P08',NULL, 'P09',NULL, 'F20',NULL, 'F21',NULL, 'F22',NULL, 'F23',NULL, 'F24',NULL, 'F25',NULL, 'F26',NULL, 'F27',NULL, 'F28',NULL, 'F29',NULL, nvl ( b.accounted_cr,0 ) ) ,NULL ) ) ) ACCOUNTED_CR, b.ussgl_transaction_code USSGL_TRANSACTION_CODE, b.account_segment_value ACCOUNT_SEGMENT_VALUE, b.automatic_encumbrance_flag AUTOMATIC_ENCUMBRANCE_FLAG, b.funding_budget_version_id FUNDING_BUDGET_VERSION_ID, b.funds_check_level_code FUNDS_CHECK_LEVEL_CODE, b.amount_type AMOUNT_TYPE, b.boundary_code BOUNDARY_CODE, b.tolerance_percentage TOLERANCE_PERCENTAGE, b.tolerance_amount TOLERANCE_AMOUNT, b.override_amount OVERRIDE_AMOUNT, b.dr_cr_code DR_CR_CODE, b.account_type ACCOUNT_TYPE, b.account_category_code ACCOUNT_CATEGORY_CODE, b.effect_on_funds_code EFFECT_ON_FUNDS_CODE, b.result_code RESULT_CODE, DECODE ( b.status_code, 'C',NULL, 'P',NULL, DECODE ( nvl ( b.funds_check_level_code, 'N') , 'N',NULL, DECODE ( b.effect_on_funds_code, 'D', DECODE ( b.result_code, 'P01',NULL, 'P02',NULL, 'P03',NULL, 'P04',NULL, 'P05',NULL, 'P06',NULL, 'P07',NULL, 'P08',NULL, 'P09',NULL, 'F20',NULL, 'F21',NULL, 'F22',NULL, 'F23',NULL, 'F24',NULL, 'F25',NULL, 'F26',NULL, 'F27',NULL, 'F28',NULL, 'F29',NULL, nvl ( b.budget_posted_balance,0 ) ) ,NULL ) ) ) BUDGET_POSTED_BALANCE, DECODE ( b.status_code, 'C',NULL, 'P',NULL, DECODE ( nvl ( b.funds_check_level_code, 'N') , 'N',NULL, DECODE ( b.effect_on_funds_code, 'D', DECODE ( b.result_code, 'P01',NULL, 'P02',NULL, 'P03',NULL, 'P04',NULL, 'P05',NULL, 'P06',NULL, 'P07',NULL, 'P08',NULL, 'P09',NULL, 'F20',NULL, 'F21',NULL, 'F22',NULL, 'F23',NULL, 'F24',NULL, 'F25',NULL, 'F26',NULL, 'F27',NULL, 'F28',NULL, 'F29',NULL, nvl ( b.actual_posted_balance,0 ) ) ,NULL ) ) ) ACTUAL_POSTED_BALANCE, 0 COMM_ENC_POSTED_BAL, 0 OBLI_ENC_POSTED_BAL, DECODE ( b.status_code, 'C',NULL, 'P',NULL, DECODE ( nvl ( b.funds_check_level_code, 'N') , 'N',NULL, DECODE ( b.effect_on_funds_code, 'D', DECODE ( b.result_code, 'P01',NULL, 'P02',NULL, 'P03',NULL, 'P04',NULL, 'P05',NULL, 'P06',NULL, 'P07',NULL, 'P08',NULL, 'P09',NULL, 'F20',NULL, 'F21',NULL, 'F22',NULL, 'F23',NULL, 'F24',NULL, 'F25',NULL, 'F26',NULL, 'F27',NULL, 'F28',NULL, 'F29',NULL, nvl ( b.encumbrance_posted_balance,0 ) ) ,NULL ) ) ) ENC_POSTED_BALANCE, DECODE ( b.status_code, 'C',NULL, 'P',NULL, DECODE ( nvl ( b.funds_check_level_code, 'N') , 'N',NULL, DECODE ( b.effect_on_funds_code, 'D', DECODE ( b.result_code, 'P01',NULL, 'P02',NULL, 'P03',NULL, 'P04',NULL, 'P05',NULL, 'P06',NULL, 'P07',NULL, 'P08',NULL, 'P09',NULL, 'F20',NULL, 'F21',NULL, 'F22',NULL, 'F23',NULL, 'F24',NULL, 'F25',NULL, 'F26',NULL, 'F27',NULL, 'F28',NULL, 'F29',NULL, nvl ( b.budget_approved_balance,0 ) ) ,NULL ) ) ) BUDGET_APPROVED_BALANCE, DECODE ( b.status_code, 'C',NULL, 'P',NULL, DECODE ( nvl ( b.funds_check_level_code, 'N') , 'N',NULL, DECODE ( b.effect_on_funds_code, 'D', DECODE ( b.result_code, 'P01',NULL, 'P02',NULL, 'P03',NULL, 'P04',NULL, 'P05',NULL, 'P06',NULL, 'P07',NULL, 'P08',NULL, 'P09',NULL, 'F20',NULL, 'F21',NULL, 'F22',NULL, 'F23',NULL, 'F24',NULL, 'F25',NULL, 'F26',NULL, 'F27',NULL, 'F28',NULL, 'F29',NULL, nvl ( b.actual_approved_balance,0 ) ) ,NULL ) ) ) ACTUAL_APPROVED_BALANCE, 0 COMM_ENC_APPROVED_BAL, 0 OBLI_ENC_APPROVED_BAL, DECODE ( b.status_code, 'C',NULL, 'P',NULL, DECODE ( nvl ( b.funds_check_level_code, 'N') , 'N',NULL, DECODE ( b.effect_on_funds_code, 'D', DECODE ( b.result_code, 'P01',NULL, 'P02',NULL, 'P03',NULL, 'P04',NULL, 'P05',NULL, 'P06',NULL, 'P07',NULL, 'P08',NULL, 'P09',NULL, 'F20',NULL, 'F21',NULL, 'F22',NULL, 'F23',NULL, 'F24',NULL, 'F25',NULL, 'F26',NULL, 'F27',NULL, 'F28',NULL, 'F29',NULL, nvl ( b.encumbrance_approved_balance,0 ) ) ,NULL ) ) ) ENC_APPROVED_BALANCE, DECODE ( b.status_code, 'C',NULL, 'P',NULL, DECODE ( nvl ( b.funds_check_level_code, 'N') , 'N',NULL, DECODE ( b.effect_on_funds_code, 'D', DECODE ( b.result_code, 'P01',NULL, 'P02',NULL, 'P03',NULL, 'P04',NULL, 'P05',NULL, 'P06',NULL, 'P07',NULL, 'P08',NULL, 'P09',NULL, 'F20',NULL, 'F21',NULL, 'F22',NULL, 'F23',NULL, 'F24',NULL, 'F25',NULL, 'F26',NULL, 'F27',NULL, 'F28',NULL, 'F29',NULL, nvl ( b.budget_pending_balance,0 ) ) ,NULL ) ) ) BUDGET_PENDING_BALANCE, DECODE ( b.status_code, 'C',NULL, 'P',NULL, DECODE ( nvl ( b.funds_check_level_code, 'N') , 'N',NULL, DECODE ( b.effect_on_funds_code, 'D', DECODE ( b.result_code, 'P01',NULL, 'P02',NULL, 'P03',NULL, 'P04',NULL, 'P05',NULL, 'P06',NULL, 'P07',NULL, 'P08',NULL, 'P09',NULL, 'F20',NULL, 'F21',NULL, 'F22',NULL, 'F23',NULL, 'F24',NULL, 'F25',NULL, 'F26',NULL, 'F27',NULL, 'F28',NULL, 'F29',NULL, nvl ( b.actual_pending_balance,0 ) ) ,NULL ) ) ) ACTUAL_PENDING_BALANCE, 0 COMM_ENC_PENDING_BAL, 0 OBLI_ENC_PENDING_BAL, DECODE ( b.status_code, 'C',NULL, 'P',NULL, DECODE ( nvl ( b.funds_check_level_code, 'N') , 'N',NULL, DECODE ( b.effect_on_funds_code, 'D', DECODE ( b.result_code, 'P01',NULL, 'P02',NULL, 'P03',NULL, 'P04',NULL, 'P05',NULL, 'P06',NULL, 'P07',NULL, 'P08',NULL, 'P09',NULL, 'F20',NULL, 'F21',NULL, 'F22',NULL, 'F23',NULL, 'F24',NULL, 'F25',NULL, 'F26',NULL, 'F27',NULL, 'F28',NULL, 'F29',NULL, nvl ( b.encumbrance_pending_balance,0 ) ) ,NULL ) ) ) ENC_PENDING_BALANCE, DECODE ( b.status_code, 'C',NULL, 'P',NULL, DECODE ( nvl ( b.funds_check_level_code, 'N') , 'N',NULL, DECODE ( b.effect_on_funds_code, 'D', DECODE ( b.result_code, 'P01',NULL, 'P02',NULL, 'P03',NULL, 'P04',NULL, 'P05',NULL, 'P06',NULL, 'P07',NULL, 'P08',NULL, 'P09',NULL, 'F20',NULL, 'F21',NULL, 'F22',NULL, 'F23',NULL, 'F24',NULL, 'F25',NULL, 'F26',NULL, 'F27',NULL, 'F28',NULL, 'F29',NULL, nvl ( b.budget_posted_balance,0 ) + nvl ( b.budget_approved_balance,0 ) + nvl ( b.budget_pending_balance,0 ) ) ,NULL ) ) ) BUDGET_TOTAL_BALANCE, 0 COMM_ENC_TOTAL_BAL, 0 OBLI_ENC_TOTAL_BAL, DECODE ( b.status_code, 'C',NULL, 'P',NULL, DECODE ( nvl ( b.funds_check_level_code, 'N') , 'N',NULL, DECODE ( b.effect_on_funds_code, 'D', DECODE ( b.result_code, 'P01',NULL, 'P02',NULL, 'P03',NULL, 'P04',NULL, 'P05',NULL, 'P06',NULL, 'P07',NULL, 'P08',NULL, 'P09',NULL, 'F20',NULL, 'F21',NULL, 'F22',NULL, 'F23',NULL, 'F24',NULL, 'F25',NULL, 'F26',NULL, 'F27',NULL, 'F28',NULL, 'F29',NULL, nvl ( b.encumbrance_posted_balance,0 ) + nvl ( b.encumbrance_approved_balance,0 ) + nvl ( b.encumbrance_pending_balance,0 ) ) ,NULL ) ) ) ENC_TOTAL_BALANCE, DECODE ( b.status_code, 'C',NULL, 'P',NULL, DECODE ( nvl ( b.funds_check_level_code, 'N') , 'N',NULL, DECODE ( b.effect_on_funds_code, 'D', DECODE ( b.result_code, 'P01',NULL, 'P02',NULL, 'P03',NULL, 'P04',NULL, 'P05',NULL, 'P06',NULL, 'P07',NULL, 'P08',NULL, 'P09',NULL, 'F20',NULL, 'F21',NULL, 'F22',NULL, 'F23',NULL, 'F24',NULL, 'F25',NULL, 'F26',NULL, 'F27',NULL, 'F28',NULL, 'F29',NULL, nvl ( b.actual_posted_balance,0 ) + nvl ( b.actual_approved_balance,0 ) + nvl ( b.actual_pending_balance,0 ) ) ,NULL ) ) ) ACTUAL_TOTAL_BALANCE, DECODE ( b.status_code, 'C',NULL, 'P',NULL, DECODE ( nvl ( b.funds_check_level_code, 'N') , 'N',NULL, DECODE ( b.effect_on_funds_code, 'D', DECODE ( b.result_code, 'P01',NULL, 'P02',NULL, 'P03',NULL, 'P04',NULL, 'P05',NULL, 'P06',NULL, 'P07',NULL, 'P08',NULL, 'P09',NULL, 'F20',NULL, 'F21',NULL, 'F22',NULL, 'F23',NULL, 'F24',NULL, 'F25',NULL, 'F26',NULL, 'F27',NULL, 'F28',NULL, 'F29',NULL, DECODE ( b.account_category_code, 'B', nvl ( actual_posted_balance,0 ) , nvl ( b.budget_posted_balance,0 ) - nvl ( b.encumbrance_posted_balance,0 ) - nvl ( b.actual_posted_balance,0 ) ) ) ,NULL ) ) ) AVAIL_POSTED_BALANCE, DECODE ( b.status_code, 'C',NULL, 'P',NULL, DECODE ( nvl ( b.funds_check_level_code, 'N') , 'N',NULL, DECODE ( b.effect_on_funds_code, 'D', DECODE ( b.result_code, 'P01',NULL, 'P02',NULL, 'P03',NULL, 'P04',NULL, 'P05',NULL, 'P06',NULL, 'P07',NULL, 'P08',NULL, 'P09',NULL, 'F20',NULL, 'F21',NULL, 'F22',NULL, 'F23',NULL, 'F24',NULL, 'F25',NULL, 'F26',NULL, 'F27',NULL, 'F28',NULL, 'F29',NULL, DECODE ( b.account_category_code, 'B', nvl ( actual_approved_balance,0 ) , nvl ( b.budget_approved_balance,0 ) - nvl ( b.encumbrance_approved_balance,0 ) - nvl ( b.actual_approved_balance,0 ) ) ) ,NULL ) ) ) AVAIL_APPROVED_BALANCE, DECODE ( b.status_code, 'C',NULL, 'P',NULL, DECODE ( nvl ( b.funds_check_level_code, 'N') , 'N',NULL, DECODE ( b.effect_on_funds_code, 'D', DECODE ( b.result_code, 'P01',NULL, 'P02',NULL, 'P03',NULL, 'P04',NULL, 'P05',NULL, 'P06',NULL, 'P07',NULL, 'P08',NULL, 'P09',NULL, 'F20',NULL, 'F21',NULL, 'F22',NULL, 'F23',NULL, 'F24',NULL, 'F25',NULL, 'F26',NULL, 'F27',NULL, 'F28',NULL, 'F29',NULL, DECODE ( b.account_category_code, 'B', nvl ( actual_pending_balance,0 ) , nvl ( b.budget_pending_balance,0 ) - nvl ( b.encumbrance_pending_balance,0 ) - nvl ( b.actual_pending_balance,0 ) ) ) ,NULL ) ) ) AVAIL_PENDING_BALANCE, DECODE (nvl(b.funds_check_level_code, 'N'),'N', NULL, DECODE( b.effect_on_funds_code,'I',NULL, DECODE ( b.account_category_code, 'B',( nvl ( b.budget_posted_balance,0 ) + nvl ( b.budget_approved_balance,0 ) + nvl ( b.budget_pending_balance,0 ) ) - ( nvl ( b.actual_posted_balance,0 ) + nvl ( b.actual_approved_balance,0 ) + nvl ( b.actual_pending_balance,0 ) ) , ( nvl ( b.budget_posted_balance,0 ) + nvl ( b.budget_approved_balance,0 ) + nvl ( b.budget_pending_balance,0 ) ) - ( nvl ( b.encumbrance_posted_balance,0 ) + nvl ( b.encumbrance_approved_balance,0 ) + nvl ( b.encumbrance_pending_balance,0 ) ) - ( nvl ( b.actual_posted_balance,0 ) + nvl ( b.actual_approved_balance,0 ) + nvl ( b.actual_pending_balance,0 ))))) AVAIL_TOTAL_BALANCE, b.reference1 REFERENCE1, b.reference2 REFERENCE2, b.reference3 REFERENCE3, b.reference4 REFERENCE4, b.reference5 REFERENCE5, b.reference13 REFERENCE13, b.reference14 REFERENCE14, b.reference15 REFERENCE15, b.je_batch_name JE_BATCH_NAME, b.je_batch_id JE_BATCH_ID, b.je_header_id JE_HEADER_ID, b.je_line_num JE_LINE_NUM, b.je_line_description JE_LINE_DESCRIPTION, c.user_je_category_name USER_JE_CATEGORY_NAME, s.user_je_source_name USER_JE_SOURCE_NAME, DECODE(B.ACCOUNT_TYPE, 'R', DECODE(EFFECT_ON_FUNDS_CODE, 'D', ABS(NVL(B.ACCOUNTED_DR,0) - NVL(B.ACCOUNTED_CR,0)), ABS(NVL(B.ACCOUNTED_DR,0) - NVL(B.ACCOUNTED_CR,0)) * -1), 'L', DECODE(EFFECT_ON_FUNDS_CODE, 'D', ABS(NVL(B.ACCOUNTED_DR,0) - NVL(B.ACCOUNTED_CR,0)), ABS(NVL(B.ACCOUNTED_DR,0) - NVL(B.ACCOUNTED_CR,0)) * -1), 'O', DECODE(EFFECT_ON_FUNDS_CODE, 'D', ABS(NVL(B.ACCOUNTED_DR,0) - NVL(B.ACCOUNTED_CR,0)), ABS(NVL(B.ACCOUNTED_DR,0) - NVL(B.ACCOUNTED_CR,0)) * -1), DECODE(EFFECT_ON_FUNDS_CODE,'D', ABS(NVL(B.ACCOUNTED_DR,0) - NVL(B.ACCOUNTED_CR,0)) * -1, ABS(NVL(B.ACCOUNTED_DR,0) - NVL(B.ACCOUNTED_CR,0)))) TRANSACTION_AMOUNT, DECODE(nvl(b.funds_check_level_code, 'N'),'N', NULL, DECODE( b.effect_on_funds_code,'I',NULL, DECODE ( b.account_category_code, 'B',( nvl ( b.budget_posted_balance,0 ) + nvl ( b.budget_approved_balance,0 ) + nvl ( b.budget_pending_balance,0 ) ) - ( nvl ( b.actual_posted_balance,0 ) + nvl ( b.actual_approved_balance,0 ) + nvl ( b.actual_pending_balance,0 ) ) , ( nvl ( b.budget_posted_balance,0 ) + nvl ( b.budget_approved_balance,0 ) + nvl ( b.budget_pending_balance,0 ) ) - ( nvl ( b.encumbrance_posted_balance,0 ) + nvl ( b.encumbrance_approved_balance,0 ) + nvl ( b.encumbrance_pending_balance,0 ) ) - ( nvl ( b.actual_posted_balance,0 ) + nvl ( b.actual_approved_balance,0 ) + nvl ( b.actual_pending_balance,0 ))) + DECODE(b.status_code,'F', 0,'R', 0,'T', 0,'C', 0, 'P', 0, DECODE(b.currency_code,'STAT',null, DECODE(B.ACCOUNT_TYPE, 'R', DECODE(EFFECT_ON_FUNDS_CODE, 'D', ABS(NVL(B.ACCOUNTED_DR,0) - NVL(B.ACCOUNTED_CR,0)), ABS(NVL(B.ACCOUNTED_DR,0) - NVL(B.ACCOUNTED_CR,0)) * -1), 'L', DECODE(EFFECT_ON_FUNDS_CODE, 'D', ABS(NVL(B.ACCOUNTED_DR,0) - NVL(B.ACCOUNTED_CR,0)), ABS(NVL(B.ACCOUNTED_DR,0) - NVL(B.ACCOUNTED_CR,0)) * -1), 'O', DECODE(EFFECT_ON_FUNDS_CODE, 'D', ABS(NVL(B.ACCOUNTED_DR,0) - NVL(B.ACCOUNTED_CR,0)), ABS(NVL(B.ACCOUNTED_DR,0) - NVL(B.ACCOUNTED_CR,0)) * -1), DECODE(EFFECT_ON_FUNDS_CODE,'D', ABS(NVL(B.ACCOUNTED_DR,0) - NVL(B.ACCOUNTED_CR,0)) * -1, ABS(NVL(B.ACCOUNTED_DR,0) - NVL(B.ACCOUNTED_CR,0)))) )))) CURRENT_FUNDS_AVAILABLE, NULL ACTUAL_FLAG_MEANING, et.encumbrance_type ENCUMBRANCE_TYPE, NULL STATUS_CODE_MEANING, NULL RESULT_CODE_MEANING, bv1.budget_name FUNDING_BUDGET_NAME, NULL AMOUNT_TYPE_MEANING, NULL BOUNDARY_MEANING, gls.concatenated_description TEMPLATE, NULL LEVEL_MEANING, NULL ACCOUNT_TYPE_MEANING, NULL EFFECT_ON_FUNDS_MEANING, jeb.name BATCH_NAME, jeh.name HEADER_NAME, b.ae_header_id, b.ae_line_num, b.period_num, b.period_year, b.quarter_num, decode(decode(b.status_code, 'S', 'S/A', 'A', 'S/A', 'F', 'F/R/T', 'R', 'F/R/T', 'T', 'F/R/T', null), 'S/A', decode(b.result_code, 'P20', 'W', 'P22', 'W', 'P25', 'W', 'P27', 'W', 'P29', 'W', 'P31', 'W', 'P35', 'W', 'P36', 'W', 'P37', 'W', 'P38', 'W', 'P39', 'W', 'P'), 'F/R/T', 'F', null) EFFECTIVE_STATUS FROM GL_JE_HEADERS jeh, GL_JE_BATCHES jeb, GL_SUMMARY_TEMPLATES gls, GL_JE_CATEGORIES c, GL_JE_SOURCES s, GL_ENCUMBRANCE_TYPES et, GL_BUDGET_VERSIONS bv1, (SELECT B1.PACKET_ID, LEDGER_ID, JE_SOURCE_NAME, JE_CATEGORY_NAME, CODE_COMBINATION_ID, ACTUAL_FLAG, PERIOD_NAME, PERIOD_YEAR, PERIOD_NUM, QUARTER_NUM, CURRENCY_CODE, STATUS_CODE, LAST_UPDATE_DATE, LAST_UPDATED_BY, BUDGET_VERSION_ID, ENCUMBRANCE_TYPE_ID, TEMPLATE_ID, ENTERED_DR, ENTERED_CR, ACCOUNTED_DR, ACCOUNTED_CR, USSGL_TRANSACTION_CODE, ORIGINATING_ROWID AS ROW_ID, ACCOUNT_SEGMENT_VALUE, AUTOMATIC_ENCUMBRANCE_FLAG, FUNDING_BUDGET_VERSION_ID, FUNDS_CHECK_LEVEL_CODE, AMOUNT_TYPE, BOUNDARY_CODE, TOLERANCE_PERCENTAGE, TOLERANCE_AMOUNT, OVERRIDE_AMOUNT, DR_CR_CODE, ACCOUNT_TYPE, ACCOUNT_CATEGORY_CODE, EFFECT_ON_FUNDS_CODE, RESULT_CODE, BUDGET_POSTED_BALANCE, ACTUAL_POSTED_BALANCE, ENCUMBRANCE_POSTED_BALANCE, BUDGET_APPROVED_BALANCE, ACTUAL_APPROVED_BALANCE, ENCUMBRANCE_APPROVED_BALANCE, BUDGET_PENDING_BALANCE, ACTUAL_PENDING_BALANCE, ENCUMBRANCE_PENDING_BALANCE, REFERENCE1, REFERENCE2, REFERENCE3, REFERENCE4, REFERENCE5, JE_BATCH_NAME, JE_BATCH_ID, JE_HEADER_ID, JE_LINE_NUM, JE_LINE_DESCRIPTION, REFERENCE6, REFERENCE7, REFERENCE8, REFERENCE9, REFERENCE10, REFERENCE11, REFERENCE12, REFERENCE13, REFERENCE14, REFERENCE15, REQUEST_ID, USSGL_PARENT_ID, USSGL_LINK_TO_PARENT_ID, B1.EVENT_ID, AE_HEADER_ID, AE_LINE_NUM, BC_DATE, B1.SOURCE_DISTRIBUTION_TYPE, SOURCE_DISTRIBUTION_ID_CHAR_1, SOURCE_DISTRIBUTION_ID_CHAR_2, SOURCE_DISTRIBUTION_ID_CHAR_3, SOURCE_DISTRIBUTION_ID_CHAR_4, SOURCE_DISTRIBUTION_ID_CHAR_5, B1.SOURCE_DISTRIBUTION_ID_NUM_1, B1.SOURCE_DISTRIBUTION_ID_NUM_2, B1.SOURCE_DISTRIBUTION_ID_NUM_3, B1.SOURCE_DISTRIBUTION_ID_NUM_4, B1.SOURCE_DISTRIBUTION_ID_NUM_5, SESSION_ID, SERIAL_ID, APPLICATION_ID, ENTITY_ID, GROUP_ID FROM GL_BC_PACKETS B1,PSA_BC_REPORT_EVENTS_GT G1 WHERE B1.PACKET_ID = G1.PACKET_ID UNION ALL SELECT B1.PACKET_ID, LEDGER_ID, JE_SOURCE_NAME, JE_CATEGORY_NAME, CODE_COMBINATION_ID, ACTUAL_FLAG, PERIOD_NAME, PERIOD_YEAR, PERIOD_NUM, QUARTER_NUM, CURRENCY_CODE, STATUS_CODE, LAST_UPDATE_DATE, LAST_UPDATED_BY, BUDGET_VERSION_ID, ENCUMBRANCE_TYPE_ID, TEMPLATE_ID, ENTERED_DR, ENTERED_CR, ACCOUNTED_DR, ACCOUNTED_CR, USSGL_TRANSACTION_CODE, ORIGINATING_ROWID AS ROW_ID, ACCOUNT_SEGMENT_VALUE, AUTOMATIC_ENCUMBRANCE_FLAG, FUNDING_BUDGET_VERSION_ID, FUNDS_CHECK_LEVEL_CODE, AMOUNT_TYPE, BOUNDARY_CODE, TOLERANCE_PERCENTAGE, TOLERANCE_AMOUNT, OVERRIDE_AMOUNT, DR_CR_CODE, ACCOUNT_TYPE, ACCOUNT_CATEGORY_CODE, EFFECT_ON_FUNDS_CODE, RESULT_CODE, BUDGET_POSTED_BALANCE, ACTUAL_POSTED_BALANCE, ENCUMBRANCE_POSTED_BALANCE, BUDGET_APPROVED_BALANCE, ACTUAL_APPROVED_BALANCE, ENCUMBRANCE_APPROVED_BALANCE, BUDGET_PENDING_BALANCE, ACTUAL_PENDING_BALANCE, ENCUMBRANCE_PENDING_BALANCE, REFERENCE1, REFERENCE2, REFERENCE3, REFERENCE4, REFERENCE5, JE_BATCH_NAME, JE_BATCH_ID, JE_HEADER_ID, JE_LINE_NUM, JE_LINE_DESCRIPTION, REFERENCE6, REFERENCE7, REFERENCE8, REFERENCE9, REFERENCE10, REFERENCE11, REFERENCE12, REFERENCE13, REFERENCE14, REFERENCE15, REQUEST_ID, USSGL_PARENT_ID, USSGL_LINK_TO_PARENT_ID, B1.EVENT_ID, AE_HEADER_ID, AE_LINE_NUM, BC_DATE, B1.SOURCE_DISTRIBUTION_TYPE, SOURCE_DISTRIBUTION_ID_CHAR_1, SOURCE_DISTRIBUTION_ID_CHAR_2, SOURCE_DISTRIBUTION_ID_CHAR_3, SOURCE_DISTRIBUTION_ID_CHAR_4, SOURCE_DISTRIBUTION_ID_CHAR_5, B1.SOURCE_DISTRIBUTION_ID_NUM_1, B1.SOURCE_DISTRIBUTION_ID_NUM_2, B1.SOURCE_DISTRIBUTION_ID_NUM_3, B1.SOURCE_DISTRIBUTION_ID_NUM_4, B1.SOURCE_DISTRIBUTION_ID_NUM_5, SESSION_ID, SERIAL_ID, APPLICATION_ID, ENTITY_ID, GROUP_ID FROM GL_BC_PACKETS_HISTS B1,PSA_BC_REPORT_EVENTS_GT G1 WHERE B1.PACKET_ID = G1.PACKET_ID ) b, PSA_BC_APPLICATION_V PSAV WHERE jeb.je_batch_id ( + ) = b.je_batch_id AND jeh.je_header_id ( + ) = b.je_header_id AND gls.template_id ( + ) = b.template_id AND c.je_category_name = b.je_category_name AND s.je_source_name = b.je_source_name AND bv1.budget_version_id ( + ) = b.funding_budget_version_id AND et.encumbrance_type_id ( + ) = b.encumbrance_type_id AND b.application_id = PSAV.application_id

View Text - HTML Formatted

SELECT B.ROW_ID ROW_ID
, B.APPLICATION_ID APPLICATION_ID
, PSAV.APPLICATION_SHORT_NAME APPLICATION_SHORT_NAME
, PSAV.APPLICATION_NAME
, B.LEDGER_ID LEDGER_ID
, B.EVENT_ID EVENT_ID
, B.SOURCE_DISTRIBUTION_TYPE SOURCE_DISTRIBUTION_TYPE
, B.SOURCE_DISTRIBUTION_ID_NUM_1 SOURCE_DISTRIBUTION_ID_NUM_1
, B.SOURCE_DISTRIBUTION_ID_NUM_2 SOURCE_DISTRIBUTION_ID_NUM_2
, B.SOURCE_DISTRIBUTION_ID_NUM_3 SOURCE_DISTRIBUTION_ID_NUM_3
, B.SOURCE_DISTRIBUTION_ID_NUM_4 SOURCE_DISTRIBUTION_ID_NUM_4
, B.SOURCE_DISTRIBUTION_ID_NUM_5 SOURCE_DISTRIBUTION_ID_NUM_5
, B.SOURCE_DISTRIBUTION_ID_CHAR_1 SOURCE_DISTRIBUTION_ID_CHAR_1
, B.SOURCE_DISTRIBUTION_ID_CHAR_2 SOURCE_DISTRIBUTION_ID_CHAR_2
, B.SOURCE_DISTRIBUTION_ID_CHAR_3 SOURCE_DISTRIBUTION_ID_CHAR_3
, B.SOURCE_DISTRIBUTION_ID_CHAR_4 SOURCE_DISTRIBUTION_ID_CHAR_4
, B.SOURCE_DISTRIBUTION_ID_CHAR_5 SOURCE_DISTRIBUTION_ID_CHAR_5
, B.PACKET_ID PACKET_ID
, B.JE_SOURCE_NAME JE_SOURCE_NAME
, B.JE_CATEGORY_NAME JE_CATEGORY_NAME
, B.CODE_COMBINATION_ID CODE_COMBINATION_ID
, B.ACTUAL_FLAG ACTUAL_FLAG
, B.PERIOD_NAME PERIOD_NAME
, B.CURRENCY_CODE CURRENCY_CODE
, B.STATUS_CODE STATUS_CODE
, BV1.BUDGET_TYPE BUDGET_TYPE
, B.BUDGET_VERSION_ID BUDGET_VERSION_ID
, B.ENCUMBRANCE_TYPE_ID ENCUMBRANCE_TYPE_ID
, B.TEMPLATE_ID TEMPLATE_ID
, B.ENTERED_DR ENTERED_DR
, B.ENTERED_CR ENTERED_CR
, DECODE ( B.STATUS_CODE
, 'C'
, NULL
, 'P'
, NULL
, DECODE ( NVL ( B.FUNDS_CHECK_LEVEL_CODE
, 'N')
, 'N'
, NULL
, DECODE ( B.EFFECT_ON_FUNDS_CODE
, 'D'
, DECODE ( B.RESULT_CODE
, 'P01'
, NULL
, 'P02'
, NULL
, 'P03'
, NULL
, 'P04'
, NULL
, 'P05'
, NULL
, 'P06'
, NULL
, 'P07'
, NULL
, 'P08'
, NULL
, 'P09'
, NULL
, 'F20'
, NULL
, 'F21'
, NULL
, 'F22'
, NULL
, 'F23'
, NULL
, 'F24'
, NULL
, 'F25'
, NULL
, 'F26'
, NULL
, 'F27'
, NULL
, 'F28'
, NULL
, 'F29'
, NULL
, NVL ( B.ACCOUNTED_DR
, 0 ) )
, NULL ) ) ) ACCOUNTED_DR
, DECODE ( B.STATUS_CODE
, 'C'
, NULL
, 'P'
, NULL
, DECODE ( NVL ( B.FUNDS_CHECK_LEVEL_CODE
, 'N')
, 'N'
, NULL
, DECODE ( B.EFFECT_ON_FUNDS_CODE
, 'D'
, DECODE ( B.RESULT_CODE
, 'P01'
, NULL
, 'P02'
, NULL
, 'P03'
, NULL
, 'P04'
, NULL
, 'P05'
, NULL
, 'P06'
, NULL
, 'P07'
, NULL
, 'P08'
, NULL
, 'P09'
, NULL
, 'F20'
, NULL
, 'F21'
, NULL
, 'F22'
, NULL
, 'F23'
, NULL
, 'F24'
, NULL
, 'F25'
, NULL
, 'F26'
, NULL
, 'F27'
, NULL
, 'F28'
, NULL
, 'F29'
, NULL
, NVL ( B.ACCOUNTED_CR
, 0 ) )
, NULL ) ) ) ACCOUNTED_CR
, B.USSGL_TRANSACTION_CODE USSGL_TRANSACTION_CODE
, B.ACCOUNT_SEGMENT_VALUE ACCOUNT_SEGMENT_VALUE
, B.AUTOMATIC_ENCUMBRANCE_FLAG AUTOMATIC_ENCUMBRANCE_FLAG
, B.FUNDING_BUDGET_VERSION_ID FUNDING_BUDGET_VERSION_ID
, B.FUNDS_CHECK_LEVEL_CODE FUNDS_CHECK_LEVEL_CODE
, B.AMOUNT_TYPE AMOUNT_TYPE
, B.BOUNDARY_CODE BOUNDARY_CODE
, B.TOLERANCE_PERCENTAGE TOLERANCE_PERCENTAGE
, B.TOLERANCE_AMOUNT TOLERANCE_AMOUNT
, B.OVERRIDE_AMOUNT OVERRIDE_AMOUNT
, B.DR_CR_CODE DR_CR_CODE
, B.ACCOUNT_TYPE ACCOUNT_TYPE
, B.ACCOUNT_CATEGORY_CODE ACCOUNT_CATEGORY_CODE
, B.EFFECT_ON_FUNDS_CODE EFFECT_ON_FUNDS_CODE
, B.RESULT_CODE RESULT_CODE
, DECODE ( B.STATUS_CODE
, 'C'
, NULL
, 'P'
, NULL
, DECODE ( NVL ( B.FUNDS_CHECK_LEVEL_CODE
, 'N')
, 'N'
, NULL
, DECODE ( B.EFFECT_ON_FUNDS_CODE
, 'D'
, DECODE ( B.RESULT_CODE
, 'P01'
, NULL
, 'P02'
, NULL
, 'P03'
, NULL
, 'P04'
, NULL
, 'P05'
, NULL
, 'P06'
, NULL
, 'P07'
, NULL
, 'P08'
, NULL
, 'P09'
, NULL
, 'F20'
, NULL
, 'F21'
, NULL
, 'F22'
, NULL
, 'F23'
, NULL
, 'F24'
, NULL
, 'F25'
, NULL
, 'F26'
, NULL
, 'F27'
, NULL
, 'F28'
, NULL
, 'F29'
, NULL
, NVL ( B.BUDGET_POSTED_BALANCE
, 0 ) )
, NULL ) ) ) BUDGET_POSTED_BALANCE
, DECODE ( B.STATUS_CODE
, 'C'
, NULL
, 'P'
, NULL
, DECODE ( NVL ( B.FUNDS_CHECK_LEVEL_CODE
, 'N')
, 'N'
, NULL
, DECODE ( B.EFFECT_ON_FUNDS_CODE
, 'D'
, DECODE ( B.RESULT_CODE
, 'P01'
, NULL
, 'P02'
, NULL
, 'P03'
, NULL
, 'P04'
, NULL
, 'P05'
, NULL
, 'P06'
, NULL
, 'P07'
, NULL
, 'P08'
, NULL
, 'P09'
, NULL
, 'F20'
, NULL
, 'F21'
, NULL
, 'F22'
, NULL
, 'F23'
, NULL
, 'F24'
, NULL
, 'F25'
, NULL
, 'F26'
, NULL
, 'F27'
, NULL
, 'F28'
, NULL
, 'F29'
, NULL
, NVL ( B.ACTUAL_POSTED_BALANCE
, 0 ) )
, NULL ) ) ) ACTUAL_POSTED_BALANCE
, 0 COMM_ENC_POSTED_BAL
, 0 OBLI_ENC_POSTED_BAL
, DECODE ( B.STATUS_CODE
, 'C'
, NULL
, 'P'
, NULL
, DECODE ( NVL ( B.FUNDS_CHECK_LEVEL_CODE
, 'N')
, 'N'
, NULL
, DECODE ( B.EFFECT_ON_FUNDS_CODE
, 'D'
, DECODE ( B.RESULT_CODE
, 'P01'
, NULL
, 'P02'
, NULL
, 'P03'
, NULL
, 'P04'
, NULL
, 'P05'
, NULL
, 'P06'
, NULL
, 'P07'
, NULL
, 'P08'
, NULL
, 'P09'
, NULL
, 'F20'
, NULL
, 'F21'
, NULL
, 'F22'
, NULL
, 'F23'
, NULL
, 'F24'
, NULL
, 'F25'
, NULL
, 'F26'
, NULL
, 'F27'
, NULL
, 'F28'
, NULL
, 'F29'
, NULL
, NVL ( B.ENCUMBRANCE_POSTED_BALANCE
, 0 ) )
, NULL ) ) ) ENC_POSTED_BALANCE
, DECODE ( B.STATUS_CODE
, 'C'
, NULL
, 'P'
, NULL
, DECODE ( NVL ( B.FUNDS_CHECK_LEVEL_CODE
, 'N')
, 'N'
, NULL
, DECODE ( B.EFFECT_ON_FUNDS_CODE
, 'D'
, DECODE ( B.RESULT_CODE
, 'P01'
, NULL
, 'P02'
, NULL
, 'P03'
, NULL
, 'P04'
, NULL
, 'P05'
, NULL
, 'P06'
, NULL
, 'P07'
, NULL
, 'P08'
, NULL
, 'P09'
, NULL
, 'F20'
, NULL
, 'F21'
, NULL
, 'F22'
, NULL
, 'F23'
, NULL
, 'F24'
, NULL
, 'F25'
, NULL
, 'F26'
, NULL
, 'F27'
, NULL
, 'F28'
, NULL
, 'F29'
, NULL
, NVL ( B.BUDGET_APPROVED_BALANCE
, 0 ) )
, NULL ) ) ) BUDGET_APPROVED_BALANCE
, DECODE ( B.STATUS_CODE
, 'C'
, NULL
, 'P'
, NULL
, DECODE ( NVL ( B.FUNDS_CHECK_LEVEL_CODE
, 'N')
, 'N'
, NULL
, DECODE ( B.EFFECT_ON_FUNDS_CODE
, 'D'
, DECODE ( B.RESULT_CODE
, 'P01'
, NULL
, 'P02'
, NULL
, 'P03'
, NULL
, 'P04'
, NULL
, 'P05'
, NULL
, 'P06'
, NULL
, 'P07'
, NULL
, 'P08'
, NULL
, 'P09'
, NULL
, 'F20'
, NULL
, 'F21'
, NULL
, 'F22'
, NULL
, 'F23'
, NULL
, 'F24'
, NULL
, 'F25'
, NULL
, 'F26'
, NULL
, 'F27'
, NULL
, 'F28'
, NULL
, 'F29'
, NULL
, NVL ( B.ACTUAL_APPROVED_BALANCE
, 0 ) )
, NULL ) ) ) ACTUAL_APPROVED_BALANCE
, 0 COMM_ENC_APPROVED_BAL
, 0 OBLI_ENC_APPROVED_BAL
, DECODE ( B.STATUS_CODE
, 'C'
, NULL
, 'P'
, NULL
, DECODE ( NVL ( B.FUNDS_CHECK_LEVEL_CODE
, 'N')
, 'N'
, NULL
, DECODE ( B.EFFECT_ON_FUNDS_CODE
, 'D'
, DECODE ( B.RESULT_CODE
, 'P01'
, NULL
, 'P02'
, NULL
, 'P03'
, NULL
, 'P04'
, NULL
, 'P05'
, NULL
, 'P06'
, NULL
, 'P07'
, NULL
, 'P08'
, NULL
, 'P09'
, NULL
, 'F20'
, NULL
, 'F21'
, NULL
, 'F22'
, NULL
, 'F23'
, NULL
, 'F24'
, NULL
, 'F25'
, NULL
, 'F26'
, NULL
, 'F27'
, NULL
, 'F28'
, NULL
, 'F29'
, NULL
, NVL ( B.ENCUMBRANCE_APPROVED_BALANCE
, 0 ) )
, NULL ) ) ) ENC_APPROVED_BALANCE
, DECODE ( B.STATUS_CODE
, 'C'
, NULL
, 'P'
, NULL
, DECODE ( NVL ( B.FUNDS_CHECK_LEVEL_CODE
, 'N')
, 'N'
, NULL
, DECODE ( B.EFFECT_ON_FUNDS_CODE
, 'D'
, DECODE ( B.RESULT_CODE
, 'P01'
, NULL
, 'P02'
, NULL
, 'P03'
, NULL
, 'P04'
, NULL
, 'P05'
, NULL
, 'P06'
, NULL
, 'P07'
, NULL
, 'P08'
, NULL
, 'P09'
, NULL
, 'F20'
, NULL
, 'F21'
, NULL
, 'F22'
, NULL
, 'F23'
, NULL
, 'F24'
, NULL
, 'F25'
, NULL
, 'F26'
, NULL
, 'F27'
, NULL
, 'F28'
, NULL
, 'F29'
, NULL
, NVL ( B.BUDGET_PENDING_BALANCE
, 0 ) )
, NULL ) ) ) BUDGET_PENDING_BALANCE
, DECODE ( B.STATUS_CODE
, 'C'
, NULL
, 'P'
, NULL
, DECODE ( NVL ( B.FUNDS_CHECK_LEVEL_CODE
, 'N')
, 'N'
, NULL
, DECODE ( B.EFFECT_ON_FUNDS_CODE
, 'D'
, DECODE ( B.RESULT_CODE
, 'P01'
, NULL
, 'P02'
, NULL
, 'P03'
, NULL
, 'P04'
, NULL
, 'P05'
, NULL
, 'P06'
, NULL
, 'P07'
, NULL
, 'P08'
, NULL
, 'P09'
, NULL
, 'F20'
, NULL
, 'F21'
, NULL
, 'F22'
, NULL
, 'F23'
, NULL
, 'F24'
, NULL
, 'F25'
, NULL
, 'F26'
, NULL
, 'F27'
, NULL
, 'F28'
, NULL
, 'F29'
, NULL
, NVL ( B.ACTUAL_PENDING_BALANCE
, 0 ) )
, NULL ) ) ) ACTUAL_PENDING_BALANCE
, 0 COMM_ENC_PENDING_BAL
, 0 OBLI_ENC_PENDING_BAL
, DECODE ( B.STATUS_CODE
, 'C'
, NULL
, 'P'
, NULL
, DECODE ( NVL ( B.FUNDS_CHECK_LEVEL_CODE
, 'N')
, 'N'
, NULL
, DECODE ( B.EFFECT_ON_FUNDS_CODE
, 'D'
, DECODE ( B.RESULT_CODE
, 'P01'
, NULL
, 'P02'
, NULL
, 'P03'
, NULL
, 'P04'
, NULL
, 'P05'
, NULL
, 'P06'
, NULL
, 'P07'
, NULL
, 'P08'
, NULL
, 'P09'
, NULL
, 'F20'
, NULL
, 'F21'
, NULL
, 'F22'
, NULL
, 'F23'
, NULL
, 'F24'
, NULL
, 'F25'
, NULL
, 'F26'
, NULL
, 'F27'
, NULL
, 'F28'
, NULL
, 'F29'
, NULL
, NVL ( B.ENCUMBRANCE_PENDING_BALANCE
, 0 ) )
, NULL ) ) ) ENC_PENDING_BALANCE
, DECODE ( B.STATUS_CODE
, 'C'
, NULL
, 'P'
, NULL
, DECODE ( NVL ( B.FUNDS_CHECK_LEVEL_CODE
, 'N')
, 'N'
, NULL
, DECODE ( B.EFFECT_ON_FUNDS_CODE
, 'D'
, DECODE ( B.RESULT_CODE
, 'P01'
, NULL
, 'P02'
, NULL
, 'P03'
, NULL
, 'P04'
, NULL
, 'P05'
, NULL
, 'P06'
, NULL
, 'P07'
, NULL
, 'P08'
, NULL
, 'P09'
, NULL
, 'F20'
, NULL
, 'F21'
, NULL
, 'F22'
, NULL
, 'F23'
, NULL
, 'F24'
, NULL
, 'F25'
, NULL
, 'F26'
, NULL
, 'F27'
, NULL
, 'F28'
, NULL
, 'F29'
, NULL
, NVL ( B.BUDGET_POSTED_BALANCE
, 0 ) + NVL ( B.BUDGET_APPROVED_BALANCE
, 0 ) + NVL ( B.BUDGET_PENDING_BALANCE
, 0 ) )
, NULL ) ) ) BUDGET_TOTAL_BALANCE
, 0 COMM_ENC_TOTAL_BAL
, 0 OBLI_ENC_TOTAL_BAL
, DECODE ( B.STATUS_CODE
, 'C'
, NULL
, 'P'
, NULL
, DECODE ( NVL ( B.FUNDS_CHECK_LEVEL_CODE
, 'N')
, 'N'
, NULL
, DECODE ( B.EFFECT_ON_FUNDS_CODE
, 'D'
, DECODE ( B.RESULT_CODE
, 'P01'
, NULL
, 'P02'
, NULL
, 'P03'
, NULL
, 'P04'
, NULL
, 'P05'
, NULL
, 'P06'
, NULL
, 'P07'
, NULL
, 'P08'
, NULL
, 'P09'
, NULL
, 'F20'
, NULL
, 'F21'
, NULL
, 'F22'
, NULL
, 'F23'
, NULL
, 'F24'
, NULL
, 'F25'
, NULL
, 'F26'
, NULL
, 'F27'
, NULL
, 'F28'
, NULL
, 'F29'
, NULL
, NVL ( B.ENCUMBRANCE_POSTED_BALANCE
, 0 ) + NVL ( B.ENCUMBRANCE_APPROVED_BALANCE
, 0 ) + NVL ( B.ENCUMBRANCE_PENDING_BALANCE
, 0 ) )
, NULL ) ) ) ENC_TOTAL_BALANCE
, DECODE ( B.STATUS_CODE
, 'C'
, NULL
, 'P'
, NULL
, DECODE ( NVL ( B.FUNDS_CHECK_LEVEL_CODE
, 'N')
, 'N'
, NULL
, DECODE ( B.EFFECT_ON_FUNDS_CODE
, 'D'
, DECODE ( B.RESULT_CODE
, 'P01'
, NULL
, 'P02'
, NULL
, 'P03'
, NULL
, 'P04'
, NULL
, 'P05'
, NULL
, 'P06'
, NULL
, 'P07'
, NULL
, 'P08'
, NULL
, 'P09'
, NULL
, 'F20'
, NULL
, 'F21'
, NULL
, 'F22'
, NULL
, 'F23'
, NULL
, 'F24'
, NULL
, 'F25'
, NULL
, 'F26'
, NULL
, 'F27'
, NULL
, 'F28'
, NULL
, 'F29'
, NULL
, NVL ( B.ACTUAL_POSTED_BALANCE
, 0 ) + NVL ( B.ACTUAL_APPROVED_BALANCE
, 0 ) + NVL ( B.ACTUAL_PENDING_BALANCE
, 0 ) )
, NULL ) ) ) ACTUAL_TOTAL_BALANCE
, DECODE ( B.STATUS_CODE
, 'C'
, NULL
, 'P'
, NULL
, DECODE ( NVL ( B.FUNDS_CHECK_LEVEL_CODE
, 'N')
, 'N'
, NULL
, DECODE ( B.EFFECT_ON_FUNDS_CODE
, 'D'
, DECODE ( B.RESULT_CODE
, 'P01'
, NULL
, 'P02'
, NULL
, 'P03'
, NULL
, 'P04'
, NULL
, 'P05'
, NULL
, 'P06'
, NULL
, 'P07'
, NULL
, 'P08'
, NULL
, 'P09'
, NULL
, 'F20'
, NULL
, 'F21'
, NULL
, 'F22'
, NULL
, 'F23'
, NULL
, 'F24'
, NULL
, 'F25'
, NULL
, 'F26'
, NULL
, 'F27'
, NULL
, 'F28'
, NULL
, 'F29'
, NULL
, DECODE ( B.ACCOUNT_CATEGORY_CODE
, 'B'
, NVL ( ACTUAL_POSTED_BALANCE
, 0 )
, NVL ( B.BUDGET_POSTED_BALANCE
, 0 ) - NVL ( B.ENCUMBRANCE_POSTED_BALANCE
, 0 ) - NVL ( B.ACTUAL_POSTED_BALANCE
, 0 ) ) )
, NULL ) ) ) AVAIL_POSTED_BALANCE
, DECODE ( B.STATUS_CODE
, 'C'
, NULL
, 'P'
, NULL
, DECODE ( NVL ( B.FUNDS_CHECK_LEVEL_CODE
, 'N')
, 'N'
, NULL
, DECODE ( B.EFFECT_ON_FUNDS_CODE
, 'D'
, DECODE ( B.RESULT_CODE
, 'P01'
, NULL
, 'P02'
, NULL
, 'P03'
, NULL
, 'P04'
, NULL
, 'P05'
, NULL
, 'P06'
, NULL
, 'P07'
, NULL
, 'P08'
, NULL
, 'P09'
, NULL
, 'F20'
, NULL
, 'F21'
, NULL
, 'F22'
, NULL
, 'F23'
, NULL
, 'F24'
, NULL
, 'F25'
, NULL
, 'F26'
, NULL
, 'F27'
, NULL
, 'F28'
, NULL
, 'F29'
, NULL
, DECODE ( B.ACCOUNT_CATEGORY_CODE
, 'B'
, NVL ( ACTUAL_APPROVED_BALANCE
, 0 )
, NVL ( B.BUDGET_APPROVED_BALANCE
, 0 ) - NVL ( B.ENCUMBRANCE_APPROVED_BALANCE
, 0 ) - NVL ( B.ACTUAL_APPROVED_BALANCE
, 0 ) ) )
, NULL ) ) ) AVAIL_APPROVED_BALANCE
, DECODE ( B.STATUS_CODE
, 'C'
, NULL
, 'P'
, NULL
, DECODE ( NVL ( B.FUNDS_CHECK_LEVEL_CODE
, 'N')
, 'N'
, NULL
, DECODE ( B.EFFECT_ON_FUNDS_CODE
, 'D'
, DECODE ( B.RESULT_CODE
, 'P01'
, NULL
, 'P02'
, NULL
, 'P03'
, NULL
, 'P04'
, NULL
, 'P05'
, NULL
, 'P06'
, NULL
, 'P07'
, NULL
, 'P08'
, NULL
, 'P09'
, NULL
, 'F20'
, NULL
, 'F21'
, NULL
, 'F22'
, NULL
, 'F23'
, NULL
, 'F24'
, NULL
, 'F25'
, NULL
, 'F26'
, NULL
, 'F27'
, NULL
, 'F28'
, NULL
, 'F29'
, NULL
, DECODE ( B.ACCOUNT_CATEGORY_CODE
, 'B'
, NVL ( ACTUAL_PENDING_BALANCE
, 0 )
, NVL ( B.BUDGET_PENDING_BALANCE
, 0 ) - NVL ( B.ENCUMBRANCE_PENDING_BALANCE
, 0 ) - NVL ( B.ACTUAL_PENDING_BALANCE
, 0 ) ) )
, NULL ) ) ) AVAIL_PENDING_BALANCE
, DECODE (NVL(B.FUNDS_CHECK_LEVEL_CODE
, 'N')
, 'N'
, NULL
, DECODE( B.EFFECT_ON_FUNDS_CODE
, 'I'
, NULL
, DECODE ( B.ACCOUNT_CATEGORY_CODE
, 'B'
, ( NVL ( B.BUDGET_POSTED_BALANCE
, 0 ) + NVL ( B.BUDGET_APPROVED_BALANCE
, 0 ) + NVL ( B.BUDGET_PENDING_BALANCE
, 0 ) ) - ( NVL ( B.ACTUAL_POSTED_BALANCE
, 0 ) + NVL ( B.ACTUAL_APPROVED_BALANCE
, 0 ) + NVL ( B.ACTUAL_PENDING_BALANCE
, 0 ) )
, ( NVL ( B.BUDGET_POSTED_BALANCE
, 0 ) + NVL ( B.BUDGET_APPROVED_BALANCE
, 0 ) + NVL ( B.BUDGET_PENDING_BALANCE
, 0 ) ) - ( NVL ( B.ENCUMBRANCE_POSTED_BALANCE
, 0 ) + NVL ( B.ENCUMBRANCE_APPROVED_BALANCE
, 0 ) + NVL ( B.ENCUMBRANCE_PENDING_BALANCE
, 0 ) ) - ( NVL ( B.ACTUAL_POSTED_BALANCE
, 0 ) + NVL ( B.ACTUAL_APPROVED_BALANCE
, 0 ) + NVL ( B.ACTUAL_PENDING_BALANCE
, 0 ))))) AVAIL_TOTAL_BALANCE
, B.REFERENCE1 REFERENCE1
, B.REFERENCE2 REFERENCE2
, B.REFERENCE3 REFERENCE3
, B.REFERENCE4 REFERENCE4
, B.REFERENCE5 REFERENCE5
, B.REFERENCE13 REFERENCE13
, B.REFERENCE14 REFERENCE14
, B.REFERENCE15 REFERENCE15
, B.JE_BATCH_NAME JE_BATCH_NAME
, B.JE_BATCH_ID JE_BATCH_ID
, B.JE_HEADER_ID JE_HEADER_ID
, B.JE_LINE_NUM JE_LINE_NUM
, B.JE_LINE_DESCRIPTION JE_LINE_DESCRIPTION
, C.USER_JE_CATEGORY_NAME USER_JE_CATEGORY_NAME
, S.USER_JE_SOURCE_NAME USER_JE_SOURCE_NAME
, DECODE(B.ACCOUNT_TYPE
, 'R'
, DECODE(EFFECT_ON_FUNDS_CODE
, 'D'
, ABS(NVL(B.ACCOUNTED_DR
, 0) - NVL(B.ACCOUNTED_CR
, 0))
, ABS(NVL(B.ACCOUNTED_DR
, 0) - NVL(B.ACCOUNTED_CR
, 0)) * -1)
, 'L'
, DECODE(EFFECT_ON_FUNDS_CODE
, 'D'
, ABS(NVL(B.ACCOUNTED_DR
, 0) - NVL(B.ACCOUNTED_CR
, 0))
, ABS(NVL(B.ACCOUNTED_DR
, 0) - NVL(B.ACCOUNTED_CR
, 0)) * -1)
, 'O'
, DECODE(EFFECT_ON_FUNDS_CODE
, 'D'
, ABS(NVL(B.ACCOUNTED_DR
, 0) - NVL(B.ACCOUNTED_CR
, 0))
, ABS(NVL(B.ACCOUNTED_DR
, 0) - NVL(B.ACCOUNTED_CR
, 0)) * -1)
, DECODE(EFFECT_ON_FUNDS_CODE
, 'D'
, ABS(NVL(B.ACCOUNTED_DR
, 0) - NVL(B.ACCOUNTED_CR
, 0)) * -1
, ABS(NVL(B.ACCOUNTED_DR
, 0) - NVL(B.ACCOUNTED_CR
, 0)))) TRANSACTION_AMOUNT
, DECODE(NVL(B.FUNDS_CHECK_LEVEL_CODE
, 'N')
, 'N'
, NULL
, DECODE( B.EFFECT_ON_FUNDS_CODE
, 'I'
, NULL
, DECODE ( B.ACCOUNT_CATEGORY_CODE
, 'B'
, ( NVL ( B.BUDGET_POSTED_BALANCE
, 0 ) + NVL ( B.BUDGET_APPROVED_BALANCE
, 0 ) + NVL ( B.BUDGET_PENDING_BALANCE
, 0 ) ) - ( NVL ( B.ACTUAL_POSTED_BALANCE
, 0 ) + NVL ( B.ACTUAL_APPROVED_BALANCE
, 0 ) + NVL ( B.ACTUAL_PENDING_BALANCE
, 0 ) )
, ( NVL ( B.BUDGET_POSTED_BALANCE
, 0 ) + NVL ( B.BUDGET_APPROVED_BALANCE
, 0 ) + NVL ( B.BUDGET_PENDING_BALANCE
, 0 ) ) - ( NVL ( B.ENCUMBRANCE_POSTED_BALANCE
, 0 ) + NVL ( B.ENCUMBRANCE_APPROVED_BALANCE
, 0 ) + NVL ( B.ENCUMBRANCE_PENDING_BALANCE
, 0 ) ) - ( NVL ( B.ACTUAL_POSTED_BALANCE
, 0 ) + NVL ( B.ACTUAL_APPROVED_BALANCE
, 0 ) + NVL ( B.ACTUAL_PENDING_BALANCE
, 0 ))) + DECODE(B.STATUS_CODE
, 'F'
, 0
, 'R'
, 0
, 'T'
, 0
, 'C'
, 0
, 'P'
, 0
, DECODE(B.CURRENCY_CODE
, 'STAT'
, NULL
, DECODE(B.ACCOUNT_TYPE
, 'R'
, DECODE(EFFECT_ON_FUNDS_CODE
, 'D'
, ABS(NVL(B.ACCOUNTED_DR
, 0) - NVL(B.ACCOUNTED_CR
, 0))
, ABS(NVL(B.ACCOUNTED_DR
, 0) - NVL(B.ACCOUNTED_CR
, 0)) * -1)
, 'L'
, DECODE(EFFECT_ON_FUNDS_CODE
, 'D'
, ABS(NVL(B.ACCOUNTED_DR
, 0) - NVL(B.ACCOUNTED_CR
, 0))
, ABS(NVL(B.ACCOUNTED_DR
, 0) - NVL(B.ACCOUNTED_CR
, 0)) * -1)
, 'O'
, DECODE(EFFECT_ON_FUNDS_CODE
, 'D'
, ABS(NVL(B.ACCOUNTED_DR
, 0) - NVL(B.ACCOUNTED_CR
, 0))
, ABS(NVL(B.ACCOUNTED_DR
, 0) - NVL(B.ACCOUNTED_CR
, 0)) * -1)
, DECODE(EFFECT_ON_FUNDS_CODE
, 'D'
, ABS(NVL(B.ACCOUNTED_DR
, 0) - NVL(B.ACCOUNTED_CR
, 0)) * -1
, ABS(NVL(B.ACCOUNTED_DR
, 0) - NVL(B.ACCOUNTED_CR
, 0)))) )))) CURRENT_FUNDS_AVAILABLE
, NULL ACTUAL_FLAG_MEANING
, ET.ENCUMBRANCE_TYPE ENCUMBRANCE_TYPE
, NULL STATUS_CODE_MEANING
, NULL RESULT_CODE_MEANING
, BV1.BUDGET_NAME FUNDING_BUDGET_NAME
, NULL AMOUNT_TYPE_MEANING
, NULL BOUNDARY_MEANING
, GLS.CONCATENATED_DESCRIPTION TEMPLATE
, NULL LEVEL_MEANING
, NULL ACCOUNT_TYPE_MEANING
, NULL EFFECT_ON_FUNDS_MEANING
, JEB.NAME BATCH_NAME
, JEH.NAME HEADER_NAME
, B.AE_HEADER_ID
, B.AE_LINE_NUM
, B.PERIOD_NUM
, B.PERIOD_YEAR
, B.QUARTER_NUM
, DECODE(DECODE(B.STATUS_CODE
, 'S'
, 'S/A'
, 'A'
, 'S/A'
, 'F'
, 'F/R/T'
, 'R'
, 'F/R/T'
, 'T'
, 'F/R/T'
, NULL)
, 'S/A'
, DECODE(B.RESULT_CODE
, 'P20'
, 'W'
, 'P22'
, 'W'
, 'P25'
, 'W'
, 'P27'
, 'W'
, 'P29'
, 'W'
, 'P31'
, 'W'
, 'P35'
, 'W'
, 'P36'
, 'W'
, 'P37'
, 'W'
, 'P38'
, 'W'
, 'P39'
, 'W'
, 'P')
, 'F/R/T'
, 'F'
, NULL) EFFECTIVE_STATUS
FROM GL_JE_HEADERS JEH
, GL_JE_BATCHES JEB
, GL_SUMMARY_TEMPLATES GLS
, GL_JE_CATEGORIES C
, GL_JE_SOURCES S
, GL_ENCUMBRANCE_TYPES ET
, GL_BUDGET_VERSIONS BV1
, (SELECT B1.PACKET_ID
, LEDGER_ID
, JE_SOURCE_NAME
, JE_CATEGORY_NAME
, CODE_COMBINATION_ID
, ACTUAL_FLAG
, PERIOD_NAME
, PERIOD_YEAR
, PERIOD_NUM
, QUARTER_NUM
, CURRENCY_CODE
, STATUS_CODE
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, BUDGET_VERSION_ID
, ENCUMBRANCE_TYPE_ID
, TEMPLATE_ID
, ENTERED_DR
, ENTERED_CR
, ACCOUNTED_DR
, ACCOUNTED_CR
, USSGL_TRANSACTION_CODE
, ORIGINATING_ROWID AS ROW_ID
, ACCOUNT_SEGMENT_VALUE
, AUTOMATIC_ENCUMBRANCE_FLAG
, FUNDING_BUDGET_VERSION_ID
, FUNDS_CHECK_LEVEL_CODE
, AMOUNT_TYPE
, BOUNDARY_CODE
, TOLERANCE_PERCENTAGE
, TOLERANCE_AMOUNT
, OVERRIDE_AMOUNT
, DR_CR_CODE
, ACCOUNT_TYPE
, ACCOUNT_CATEGORY_CODE
, EFFECT_ON_FUNDS_CODE
, RESULT_CODE
, BUDGET_POSTED_BALANCE
, ACTUAL_POSTED_BALANCE
, ENCUMBRANCE_POSTED_BALANCE
, BUDGET_APPROVED_BALANCE
, ACTUAL_APPROVED_BALANCE
, ENCUMBRANCE_APPROVED_BALANCE
, BUDGET_PENDING_BALANCE
, ACTUAL_PENDING_BALANCE
, ENCUMBRANCE_PENDING_BALANCE
, REFERENCE1
, REFERENCE2
, REFERENCE3
, REFERENCE4
, REFERENCE5
, JE_BATCH_NAME
, JE_BATCH_ID
, JE_HEADER_ID
, JE_LINE_NUM
, JE_LINE_DESCRIPTION
, REFERENCE6
, REFERENCE7
, REFERENCE8
, REFERENCE9
, REFERENCE10
, REFERENCE11
, REFERENCE12
, REFERENCE13
, REFERENCE14
, REFERENCE15
, REQUEST_ID
, USSGL_PARENT_ID
, USSGL_LINK_TO_PARENT_ID
, B1.EVENT_ID
, AE_HEADER_ID
, AE_LINE_NUM
, BC_DATE
, B1.SOURCE_DISTRIBUTION_TYPE
, SOURCE_DISTRIBUTION_ID_CHAR_1
, SOURCE_DISTRIBUTION_ID_CHAR_2
, SOURCE_DISTRIBUTION_ID_CHAR_3
, SOURCE_DISTRIBUTION_ID_CHAR_4
, SOURCE_DISTRIBUTION_ID_CHAR_5
, B1.SOURCE_DISTRIBUTION_ID_NUM_1
, B1.SOURCE_DISTRIBUTION_ID_NUM_2
, B1.SOURCE_DISTRIBUTION_ID_NUM_3
, B1.SOURCE_DISTRIBUTION_ID_NUM_4
, B1.SOURCE_DISTRIBUTION_ID_NUM_5
, SESSION_ID
, SERIAL_ID
, APPLICATION_ID
, ENTITY_ID
, GROUP_ID
FROM GL_BC_PACKETS B1
, PSA_BC_REPORT_EVENTS_GT G1
WHERE B1.PACKET_ID = G1.PACKET_ID UNION ALL SELECT B1.PACKET_ID
, LEDGER_ID
, JE_SOURCE_NAME
, JE_CATEGORY_NAME
, CODE_COMBINATION_ID
, ACTUAL_FLAG
, PERIOD_NAME
, PERIOD_YEAR
, PERIOD_NUM
, QUARTER_NUM
, CURRENCY_CODE
, STATUS_CODE
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, BUDGET_VERSION_ID
, ENCUMBRANCE_TYPE_ID
, TEMPLATE_ID
, ENTERED_DR
, ENTERED_CR
, ACCOUNTED_DR
, ACCOUNTED_CR
, USSGL_TRANSACTION_CODE
, ORIGINATING_ROWID AS ROW_ID
, ACCOUNT_SEGMENT_VALUE
, AUTOMATIC_ENCUMBRANCE_FLAG
, FUNDING_BUDGET_VERSION_ID
, FUNDS_CHECK_LEVEL_CODE
, AMOUNT_TYPE
, BOUNDARY_CODE
, TOLERANCE_PERCENTAGE
, TOLERANCE_AMOUNT
, OVERRIDE_AMOUNT
, DR_CR_CODE
, ACCOUNT_TYPE
, ACCOUNT_CATEGORY_CODE
, EFFECT_ON_FUNDS_CODE
, RESULT_CODE
, BUDGET_POSTED_BALANCE
, ACTUAL_POSTED_BALANCE
, ENCUMBRANCE_POSTED_BALANCE
, BUDGET_APPROVED_BALANCE
, ACTUAL_APPROVED_BALANCE
, ENCUMBRANCE_APPROVED_BALANCE
, BUDGET_PENDING_BALANCE
, ACTUAL_PENDING_BALANCE
, ENCUMBRANCE_PENDING_BALANCE
, REFERENCE1
, REFERENCE2
, REFERENCE3
, REFERENCE4
, REFERENCE5
, JE_BATCH_NAME
, JE_BATCH_ID
, JE_HEADER_ID
, JE_LINE_NUM
, JE_LINE_DESCRIPTION
, REFERENCE6
, REFERENCE7
, REFERENCE8
, REFERENCE9
, REFERENCE10
, REFERENCE11
, REFERENCE12
, REFERENCE13
, REFERENCE14
, REFERENCE15
, REQUEST_ID
, USSGL_PARENT_ID
, USSGL_LINK_TO_PARENT_ID
, B1.EVENT_ID
, AE_HEADER_ID
, AE_LINE_NUM
, BC_DATE
, B1.SOURCE_DISTRIBUTION_TYPE
, SOURCE_DISTRIBUTION_ID_CHAR_1
, SOURCE_DISTRIBUTION_ID_CHAR_2
, SOURCE_DISTRIBUTION_ID_CHAR_3
, SOURCE_DISTRIBUTION_ID_CHAR_4
, SOURCE_DISTRIBUTION_ID_CHAR_5
, B1.SOURCE_DISTRIBUTION_ID_NUM_1
, B1.SOURCE_DISTRIBUTION_ID_NUM_2
, B1.SOURCE_DISTRIBUTION_ID_NUM_3
, B1.SOURCE_DISTRIBUTION_ID_NUM_4
, B1.SOURCE_DISTRIBUTION_ID_NUM_5
, SESSION_ID
, SERIAL_ID
, APPLICATION_ID
, ENTITY_ID
, GROUP_ID
FROM GL_BC_PACKETS_HISTS B1
, PSA_BC_REPORT_EVENTS_GT G1
WHERE B1.PACKET_ID = G1.PACKET_ID ) B
, PSA_BC_APPLICATION_V PSAV
WHERE JEB.JE_BATCH_ID ( + ) = B.JE_BATCH_ID
AND JEH.JE_HEADER_ID ( + ) = B.JE_HEADER_ID
AND GLS.TEMPLATE_ID ( + ) = B.TEMPLATE_ID
AND C.JE_CATEGORY_NAME = B.JE_CATEGORY_NAME
AND S.JE_SOURCE_NAME = B.JE_SOURCE_NAME
AND BV1.BUDGET_VERSION_ID ( + ) = B.FUNDING_BUDGET_VERSION_ID
AND ET.ENCUMBRANCE_TYPE_ID ( + ) = B.ENCUMBRANCE_TYPE_ID
AND B.APPLICATION_ID = PSAV.APPLICATION_ID