VIEW: APPS.PSA_JE_BCP_LINES_V

Source

View Text - Preformatted

SELECT b.rowid BATCH_ROW_ID, 'JE' SOURCE, b.je_batch_id JE_BATCH_ID, h.ledger_id SET_OF_BOOKS_ID, /* bug 4474691 */ b.default_period_name PERIOD_NAME, ps.period_year PERIOD_YEAR, ps.period_num PERIOD_NUM, ps.effective_period_num EFFECTIVE_PERIOD_NUM, b.actual_flag ACTUAL_FLAG, b.status BATCH_STATUS, b.budgetary_control_status BUDGETARY_CONTROL_STATUS, b.name BATCH_NAME, b.description BATCH_DESCRIPTION, b.date_created BATCH_DATE_CREATED, b.posted_date BATCH_POSTED_DATE, b.ussgl_transaction_code BATCH_USSGL_TRANSACTION_CODE, b.status_verified BATCH_STATUS_VERIFIED, b.status_reset_flag BATCH_STATUS_RESET_FLAG, b.packet_id PACKET_ID, h.rowid HEADER_ROW_ID, h.je_header_id JE_HEADER_ID, h.name HEADER_NAME, h.je_source JE_SOURCE, h.je_category JE_CATEGORY, h.description HEADER_DESCRIPTION, h.external_reference EXTERNAL_REFERENCE, h.encumbrance_type_id ENCUMBRANCE_TYPE_ID, h.budget_version_id BUDGET_VERSION_ID, h.USSGL_TRANSACTION_CODE HEADER_USSGL_TRANSACTION_CODE, h.currency_code CURRENCY_CODE, h.accrual_rev_flag ACCRUAL_REV_FLAG, h.accrual_rev_period_name ACCRUAL_REV_PERIOD_NAME, h.accrual_rev_status ACCRUAL_REV_STATUS, h.status HEADER_STATUS, h.date_created HEADER_DATE_CREATED, h.multi_bal_seg_flag MULTI_BAL_SEG_FLAG, h.balanced_je_flag BALANCED_JE_FLAG, h.balancing_segment_value BALANCING_SEGMENT_VALUE, lines.rowid ROW_ID, lines.ledger_id LINE_SET_OF_BOOKS_ID, /* bug 4474691 */ lines.je_line_num LINE_JE_LINE_NUM, lines.code_combination_id LINE_CODE_COMBINATION_ID, lines.period_name LINE_PERIOD_NAME, lines.effective_date LINE_EFFECTIVE_DATE, lines.entered_dr LINE_ENTERED_DR_NUM, lines.entered_cr LINE_ENTERED_CR_NUM, lines.entered_dr LINE_ENTERED_DR, lines.entered_cr LINE_ENTERED_CR, lines.accounted_dr LINE_ACCOUNTED_DR, lines.accounted_cr LINE_ACCOUNTED_CR, lines.description LINE_DESCRIPTION, lines.reference_1 LINE_REFERENCE_1, lines.reference_2 LINE_REFERENCE_2, lines.reference_3 LINE_REFERENCE_3, lines.reference_4 LINE_REFERENCE_4, lines.reference_5 LINE_REFERENCE_5, lines.reference_6 LINE_REFERENCE_6, lines.reference_7 LINE_REFERENCE_7, lines.reference_8 LINE_REFERENCE_8, lines.reference_9 LINE_REFERENCE_9, lines.reference_10 LINE_REFERENCE_10, lines.ussgl_transaction_code LINE_USSGL_TRANSACTION_CODE, lines.subledger_doc_sequence_id SUBLEDGER_DOC_SEQUENCE_ID, lines.subledger_doc_sequence_value SUBLEDGER_DOC_SEQUENCE_VALUE, lines.last_update_date LAST_UPDATE_DATE, lines.last_updated_by LAST_UPDATED_BY FROM gl_period_statuses ps, gl_je_lines lines, gl_je_headers h, gl_je_batches b WHERE b.je_batch_id = h.je_batch_id AND b.average_journal_flag = 'N' AND b.status = 'P' AND h.je_header_id = lines.je_header_id AND lines.period_name = ps.period_name AND lines.ledger_id = ps.ledger_id /* bug 4474691*/ AND ps.application_id = 101 UNION ALL SELECT p.rowid BATCH_ROW_ID, 'BC' SOURCE, p.je_batch_id JE_BATCH_ID, p.ledger_id SET_OF_BOOKS_ID, /* bug 4474691 */ p.period_name PERIOD_NAME, ps.period_year PERIOD_YEAR, ps.period_num PERIOD_NUM, ps.effective_period_num EFFECTIVE_PERIOD_NUM, p.actual_flag ACTUAL_FLAG, p.status_code BATCH_STATUS, p.effect_on_funds_code BUDGETARY_CONTROL_STATUS, 'BC '||p.je_source_name BATCH_NAME, 'BC  '||p.je_source_name BATCH_DESCRIPTION, sysdate BATCH_DATE_CREATED, SYSDATE BATCH_POSTED_DATE, p.ussgl_transaction_code BATCH_USSGL_TRANSACTION_CODE, 'O' BATCH_STATUS_VERIFIED, 'O' BATCH_STATUS_RESET_FLAG, p.packet_id PACKET_ID, p.rowid HEADER_ROW_ID, p.je_header_id JE_HEADER_ID, 'BC '||p.je_source_name HEADER_NAME, p.je_source_name JE_SOURCE, p.je_category_name JE_CATEGORY, 'Budg Control' HEADER_DESCRIPTION, 'Budg Control' EXTERNAL_REFERENCE, p.encumbrance_type_id ENCUMBRANCE_TYPE_ID, p.budget_version_id BUDGET_VERSION_ID, p.USSGL_TRANSACTION_CODE HEADER_USSGL_TRANSACTION_CODE, p.currency_code CURRENCY_CODE, 'O' ACCRUAL_REV_FLAG, p.period_name ACCRUAL_REV_PERIOD_NAME, 'O' ACCRUAL_REV_STATUS, 'O' HEADER_STATUS, p.last_update_date HEADER_DATE_CREATED, 'O' MULTI_BAL_SEG_FLAG, 'O' BALANCED_JE_FLAG, '' BALANCING_SEGMENT_VALUE, p.rowid ROW_ID, p.ledger_id LINE_SET_OF_BOOKS_ID, /* bug 4474691 */ p.je_line_num LINE_JE_LINE_NUM, p.code_combination_id LINE_CODE_COMBINATION_ID, p.period_name LINE_PERIOD_NAME, p.last_update_date LINE_EFFECTIVE_DATE, p.entered_dr LINE_ENTERED_DR_NUM, p.entered_cr LINE_ENTERED_CR_NUM, p.entered_dr LINE_ENTERED_DR, p.entered_cr LINE_ENTERED_CR, p.accounted_dr LINE_ACCOUNTED_DR, p.accounted_cr LINE_ACCOUNTED_CR, 'Budg Control' LINE_DESCRIPTION, p.reference1 LINE_REFERENCE_1, p.reference2 LINE_REFERENCE_2, p.reference3 LINE_REFERENCE_3, p.reference4 LINE_REFERENCE_4, p.reference5 LINE_REFERENCE_5, p.reference6 LINE_REFERENCE_6, p.reference7 LINE_REFERENCE_7, p.reference8 LINE_REFERENCE_8, p.reference9 LINE_REFERENCE_9, p.reference10 LINE_REFERENCE_10, p.ussgl_transaction_code LINE_USSGL_TRANSACTION_CODE, 1 SUBLEDGER_DOC_SEQUENCE_ID, 1 SUBLEDGER_DOC_SEQUENCE_VALUE, p.last_update_date LAST_UPDATE_DATE, p.last_updated_by LAST_UPDATED_BY FROM gl_bc_packets p, gl_period_statuses ps WHERE p.status_code = 'A' AND p.period_name = ps.period_name AND p.ledger_id = ps.ledger_id /* bug 4474691 */ AND ps.application_id = 101

View Text - HTML Formatted

SELECT B.ROWID BATCH_ROW_ID
, 'JE' SOURCE
, B.JE_BATCH_ID JE_BATCH_ID
, H.LEDGER_ID SET_OF_BOOKS_ID
, /* BUG 4474691 */ B.DEFAULT_PERIOD_NAME PERIOD_NAME
, PS.PERIOD_YEAR PERIOD_YEAR
, PS.PERIOD_NUM PERIOD_NUM
, PS.EFFECTIVE_PERIOD_NUM EFFECTIVE_PERIOD_NUM
, B.ACTUAL_FLAG ACTUAL_FLAG
, B.STATUS BATCH_STATUS
, B.BUDGETARY_CONTROL_STATUS BUDGETARY_CONTROL_STATUS
, B.NAME BATCH_NAME
, B.DESCRIPTION BATCH_DESCRIPTION
, B.DATE_CREATED BATCH_DATE_CREATED
, B.POSTED_DATE BATCH_POSTED_DATE
, B.USSGL_TRANSACTION_CODE BATCH_USSGL_TRANSACTION_CODE
, B.STATUS_VERIFIED BATCH_STATUS_VERIFIED
, B.STATUS_RESET_FLAG BATCH_STATUS_RESET_FLAG
, B.PACKET_ID PACKET_ID
, H.ROWID HEADER_ROW_ID
, H.JE_HEADER_ID JE_HEADER_ID
, H.NAME HEADER_NAME
, H.JE_SOURCE JE_SOURCE
, H.JE_CATEGORY JE_CATEGORY
, H.DESCRIPTION HEADER_DESCRIPTION
, H.EXTERNAL_REFERENCE EXTERNAL_REFERENCE
, H.ENCUMBRANCE_TYPE_ID ENCUMBRANCE_TYPE_ID
, H.BUDGET_VERSION_ID BUDGET_VERSION_ID
, H.USSGL_TRANSACTION_CODE HEADER_USSGL_TRANSACTION_CODE
, H.CURRENCY_CODE CURRENCY_CODE
, H.ACCRUAL_REV_FLAG ACCRUAL_REV_FLAG
, H.ACCRUAL_REV_PERIOD_NAME ACCRUAL_REV_PERIOD_NAME
, H.ACCRUAL_REV_STATUS ACCRUAL_REV_STATUS
, H.STATUS HEADER_STATUS
, H.DATE_CREATED HEADER_DATE_CREATED
, H.MULTI_BAL_SEG_FLAG MULTI_BAL_SEG_FLAG
, H.BALANCED_JE_FLAG BALANCED_JE_FLAG
, H.BALANCING_SEGMENT_VALUE BALANCING_SEGMENT_VALUE
, LINES.ROWID ROW_ID
, LINES.LEDGER_ID LINE_SET_OF_BOOKS_ID
, /* BUG 4474691 */ LINES.JE_LINE_NUM LINE_JE_LINE_NUM
, LINES.CODE_COMBINATION_ID LINE_CODE_COMBINATION_ID
, LINES.PERIOD_NAME LINE_PERIOD_NAME
, LINES.EFFECTIVE_DATE LINE_EFFECTIVE_DATE
, LINES.ENTERED_DR LINE_ENTERED_DR_NUM
, LINES.ENTERED_CR LINE_ENTERED_CR_NUM
, LINES.ENTERED_DR LINE_ENTERED_DR
, LINES.ENTERED_CR LINE_ENTERED_CR
, LINES.ACCOUNTED_DR LINE_ACCOUNTED_DR
, LINES.ACCOUNTED_CR LINE_ACCOUNTED_CR
, LINES.DESCRIPTION LINE_DESCRIPTION
, LINES.REFERENCE_1 LINE_REFERENCE_1
, LINES.REFERENCE_2 LINE_REFERENCE_2
, LINES.REFERENCE_3 LINE_REFERENCE_3
, LINES.REFERENCE_4 LINE_REFERENCE_4
, LINES.REFERENCE_5 LINE_REFERENCE_5
, LINES.REFERENCE_6 LINE_REFERENCE_6
, LINES.REFERENCE_7 LINE_REFERENCE_7
, LINES.REFERENCE_8 LINE_REFERENCE_8
, LINES.REFERENCE_9 LINE_REFERENCE_9
, LINES.REFERENCE_10 LINE_REFERENCE_10
, LINES.USSGL_TRANSACTION_CODE LINE_USSGL_TRANSACTION_CODE
, LINES.SUBLEDGER_DOC_SEQUENCE_ID SUBLEDGER_DOC_SEQUENCE_ID
, LINES.SUBLEDGER_DOC_SEQUENCE_VALUE SUBLEDGER_DOC_SEQUENCE_VALUE
, LINES.LAST_UPDATE_DATE LAST_UPDATE_DATE
, LINES.LAST_UPDATED_BY LAST_UPDATED_BY
FROM GL_PERIOD_STATUSES PS
, GL_JE_LINES LINES
, GL_JE_HEADERS H
, GL_JE_BATCHES B
WHERE B.JE_BATCH_ID = H.JE_BATCH_ID
AND B.AVERAGE_JOURNAL_FLAG = 'N'
AND B.STATUS = 'P'
AND H.JE_HEADER_ID = LINES.JE_HEADER_ID
AND LINES.PERIOD_NAME = PS.PERIOD_NAME
AND LINES.LEDGER_ID = PS.LEDGER_ID /* BUG 4474691*/
AND PS.APPLICATION_ID = 101 UNION ALL SELECT P.ROWID BATCH_ROW_ID
, 'BC' SOURCE
, P.JE_BATCH_ID JE_BATCH_ID
, P.LEDGER_ID SET_OF_BOOKS_ID
, /* BUG 4474691 */ P.PERIOD_NAME PERIOD_NAME
, PS.PERIOD_YEAR PERIOD_YEAR
, PS.PERIOD_NUM PERIOD_NUM
, PS.EFFECTIVE_PERIOD_NUM EFFECTIVE_PERIOD_NUM
, P.ACTUAL_FLAG ACTUAL_FLAG
, P.STATUS_CODE BATCH_STATUS
, P.EFFECT_ON_FUNDS_CODE BUDGETARY_CONTROL_STATUS
, 'BC '||P.JE_SOURCE_NAME BATCH_NAME
, 'BC '||P.JE_SOURCE_NAME BATCH_DESCRIPTION
, SYSDATE BATCH_DATE_CREATED
, SYSDATE BATCH_POSTED_DATE
, P.USSGL_TRANSACTION_CODE BATCH_USSGL_TRANSACTION_CODE
, 'O' BATCH_STATUS_VERIFIED
, 'O' BATCH_STATUS_RESET_FLAG
, P.PACKET_ID PACKET_ID
, P.ROWID HEADER_ROW_ID
, P.JE_HEADER_ID JE_HEADER_ID
, 'BC '||P.JE_SOURCE_NAME HEADER_NAME
, P.JE_SOURCE_NAME JE_SOURCE
, P.JE_CATEGORY_NAME JE_CATEGORY
, 'BUDG CONTROL' HEADER_DESCRIPTION
, 'BUDG CONTROL' EXTERNAL_REFERENCE
, P.ENCUMBRANCE_TYPE_ID ENCUMBRANCE_TYPE_ID
, P.BUDGET_VERSION_ID BUDGET_VERSION_ID
, P.USSGL_TRANSACTION_CODE HEADER_USSGL_TRANSACTION_CODE
, P.CURRENCY_CODE CURRENCY_CODE
, 'O' ACCRUAL_REV_FLAG
, P.PERIOD_NAME ACCRUAL_REV_PERIOD_NAME
, 'O' ACCRUAL_REV_STATUS
, 'O' HEADER_STATUS
, P.LAST_UPDATE_DATE HEADER_DATE_CREATED
, 'O' MULTI_BAL_SEG_FLAG
, 'O' BALANCED_JE_FLAG
, '' BALANCING_SEGMENT_VALUE
, P.ROWID ROW_ID
, P.LEDGER_ID LINE_SET_OF_BOOKS_ID
, /* BUG 4474691 */ P.JE_LINE_NUM LINE_JE_LINE_NUM
, P.CODE_COMBINATION_ID LINE_CODE_COMBINATION_ID
, P.PERIOD_NAME LINE_PERIOD_NAME
, P.LAST_UPDATE_DATE LINE_EFFECTIVE_DATE
, P.ENTERED_DR LINE_ENTERED_DR_NUM
, P.ENTERED_CR LINE_ENTERED_CR_NUM
, P.ENTERED_DR LINE_ENTERED_DR
, P.ENTERED_CR LINE_ENTERED_CR
, P.ACCOUNTED_DR LINE_ACCOUNTED_DR
, P.ACCOUNTED_CR LINE_ACCOUNTED_CR
, 'BUDG CONTROL' LINE_DESCRIPTION
, P.REFERENCE1 LINE_REFERENCE_1
, P.REFERENCE2 LINE_REFERENCE_2
, P.REFERENCE3 LINE_REFERENCE_3
, P.REFERENCE4 LINE_REFERENCE_4
, P.REFERENCE5 LINE_REFERENCE_5
, P.REFERENCE6 LINE_REFERENCE_6
, P.REFERENCE7 LINE_REFERENCE_7
, P.REFERENCE8 LINE_REFERENCE_8
, P.REFERENCE9 LINE_REFERENCE_9
, P.REFERENCE10 LINE_REFERENCE_10
, P.USSGL_TRANSACTION_CODE LINE_USSGL_TRANSACTION_CODE
, 1 SUBLEDGER_DOC_SEQUENCE_ID
, 1 SUBLEDGER_DOC_SEQUENCE_VALUE
, P.LAST_UPDATE_DATE LAST_UPDATE_DATE
, P.LAST_UPDATED_BY LAST_UPDATED_BY
FROM GL_BC_PACKETS P
, GL_PERIOD_STATUSES PS
WHERE P.STATUS_CODE = 'A'
AND P.PERIOD_NAME = PS.PERIOD_NAME
AND P.LEDGER_ID = PS.LEDGER_ID /* BUG 4474691 */
AND PS.APPLICATION_ID = 101