XLA Subledger Period Close Exceptions (1)
Description
- Categories
- BI Publisher
- Application
- Subledger Accounting
- Source
- Subledger Period Close Exceptions Report
- Short Name
- XLAPEXRPT
- DB package
- XLA_PERIOD_CLOSE_EXP_PKG
Imported from BI Publisher
This report is called by the Period Close Validation Program to extract all those events which are either untransferred or are not accounted for.
This report is called by the Period Close Validation Program to extract all those events which are either untransferred or are not accounted for.
SQL 73 lines
SELECT TABLE1.LEDGER_ID LEDGER_ID ,TABLE1.LEDGER_SHORT_NAME LEDGER_SHORT_NAME ,TABLE1.LEDGER_NAME LEDGER_NAME ,TABLE1.LEDGER_DESCRIPTION LEDGER_DESCRIPTION ,TABLE1.LEDGER_CURRENCY LEDGER_CURRENCY ,TABLE1.PERIOD_YEAR PERIOD_YEAR ,TABLE1.PERIOD_NUMBER PERIOD_NUMBER ,TABLE1.PERIOD_NAME PERIOD_NAME ,TABLE1.APPLICATION_ID APPLICATION_ID ,TABLE1.JOURNAL_SOURCE JOURNAL_SOURCE ,TABLE1.USER_JE_SOURCE USER_JE_SOURCE ,TABLE1.EVENT_CLASS_CODE EVENT_CLASS_CODE ,TABLE1.EVENT_CLASS_NAME EVENT_CLASS_NAME ,TABLE1.JOURNAL_CATEGORY_NAME JOURNAL_CATEGORY_NAME ,TABLE1.USER_JE_CATEGORY_NAME USER_JE_CATEGORY_NAME ,TABLE1.EVENT_DATE EVENT_DATE ,TABLE1.EVENT_ID EVENT_ID ,TABLE1.EVENT_NUMBER EVENT_NUMBER ,TABLE1.CREATED_BY CREATED_BY ,TABLE1.USER_NAME USER_NAME ,TABLE1.LAST_UPDATE_DATE LAST_UPDATE_DATE ,TABLE1.CREATION_DATE CREATION_DATE ,TABLE1.TRANSACTION_NUMBER TRANSACTION_NUMBER ,TABLE1.TRANSACTION_DATE TRANSACTION_DATE ,TABLE1.ON_HOLD_FLAG ON_HOLD_FLAG ,TABLE1.ON_HOLD ON_HOLD ,TABLE1.EVENT_TYPE_CODE EVENT_TYPE_CODE ,TABLE1.EVENT_TYPE_NAME EVENT_TYPE_NAME ,TABLE1.BALANCE_TYPE_CODE BALANCE_TYPE_CODE ,TABLE1.BALANCE_TYPE BALANCE_TYPE ,TABLE1.PRINT_STATUS PRINT_STATUS ,substr(USERIDS,1,instr(USERIDS,'|',1,1)-1) USER_TRX_IDENTIFIER_NAME_1 ,substr(USERIDS,instr(USERIDS,'|',1,1)+1,(instr(USERIDS,'|',1,2)-1-instr(USERIDS,'|',1,1))) USER_TRX_IDENTIFIER_VALUE_1 ,substr(USERIDS,instr(USERIDS,'|',1,2)+1,(instr(USERIDS,'|',1,3)-1-instr(USERIDS,'|',1,2))) USER_TRX_IDENTIFIER_NAME_2 ,substr(USERIDS,instr(USERIDS,'|',1,3)+1,(instr(USERIDS,'|',1,4)-1-instr(USERIDS,'|',1,3))) USER_TRX_IDENTIFIER_VALUE_2 ,substr(USERIDS,instr(USERIDS,'|',1,4)+1,(instr(USERIDS,'|',1,5)-1-instr(USERIDS,'|',1,4))) USER_TRX_IDENTIFIER_NAME_3 ,substr(USERIDS,instr(USERIDS,'|',1,5)+1,(instr(USERIDS,'|',1,6)-1-instr(USERIDS,'|',1,5))) USER_TRX_IDENTIFIER_VALUE_3 ,substr(USERIDS,instr(USERIDS,'|',1,6)+1,(instr(USERIDS,'|',1,7)-1-instr(USERIDS,'|',1,6))) USER_TRX_IDENTIFIER_NAME_4 ,substr(USERIDS,instr(USERIDS,'|',1,7)+1,(instr(USERIDS,'|',1,8)-1-instr(USERIDS,'|',1,7))) USER_TRX_IDENTIFIER_VALUE_4 ,substr(USERIDS,instr(USERIDS,'|',1,8)+1,(instr(USERIDS,'|',1,9)-1-instr(USERIDS,'|',1,8))) USER_TRX_IDENTIFIER_NAME_5 ,substr(USERIDS,instr(USERIDS,'|',1,9)+1,(instr(USERIDS,'|',1,10)-1-instr(USERIDS,'|',1,9))) USER_TRX_IDENTIFIER_VALUE_5 ,substr(USERIDS,instr(USERIDS,'|',1,10)+1,(instr(USERIDS,'|',1,11)-1-instr(USERIDS,'|',1,10))) USER_TRX_IDENTIFIER_NAME_6 ,substr(USERIDS,instr(USERIDS,'|',1,11)+1,(instr(USERIDS,'|',1,12)-1-instr(USERIDS,'|',1,11))) USER_TRX_IDENTIFIER_VALUE_6 ,substr(USERIDS,instr(USERIDS,'|',1,12)+1,(instr(USERIDS,'|',1,13)-1-instr(USERIDS,'|',1,12))) USER_TRX_IDENTIFIER_NAME_7 ,substr(USERIDS,instr(USERIDS,'|',1,13)+1,(instr(USERIDS,'|',1,14)-1-instr(USERIDS,'|',1,13))) USER_TRX_IDENTIFIER_VALUE_7 ,substr(USERIDS,instr(USERIDS,'|',1,14)+1,(instr(USERIDS,'|',1,15)-1-instr(USERIDS,'|',1,14))) USER_TRX_IDENTIFIER_NAME_8 ,substr(USERIDS,instr(USERIDS,'|',1,15)+1,(instr(USERIDS,'|',1,16)-1-instr(USERIDS,'|',1,15))) USER_TRX_IDENTIFIER_VALUE_8 ,substr(USERIDS,instr(USERIDS,'|',1,16)+1,(instr(USERIDS,'|',1,17)-1-instr(USERIDS,'|',1,16))) USER_TRX_IDENTIFIER_NAME_9 ,substr(USERIDS,instr(USERIDS,'|',1,17)+1,(instr(USERIDS,'|',1,18)-1-instr(USERIDS,'|',1,17))) USER_TRX_IDENTIFIER_VALUE_9 ,substr(USERIDS,instr(USERIDS,'|',1,18)+1,(instr(USERIDS,'|',1,19)-1-instr(USERIDS,'|',1,18))) USER_TRX_IDENTIFIER_NAME_10 ,substr(USERIDS,instr(USERIDS,'|',1,19)+1,(length(USERIDS)-instr(USERIDS,'|',1,19))) USER_TRX_IDENTIFIER_VALUE_10 FROM (&C_EVENTS_COLS_QUERY &p_trx_identifiers_1 &p_trx_identifiers_2 &p_trx_identifiers_3 &p_trx_identifiers_4 &p_trx_identifiers_5 &C_EVENTS_FROM_QUERY &p_ledger_ids &p_event_filter &p_je_source_filter UNION &C_HEADERS_COLS_QUERY &p_trx_identifiers_1 &p_trx_identifiers_2 &p_trx_identifiers_3 &p_trx_identifiers_4 &p_trx_identifiers_5 &C_HEADERS_FROM_QUERY &p_ledger_ids &p_header_filter &p_je_source_filter) TABLE1 |
Parameters 6
| Parameter | Type | List of values SQL |
|---|---|---|
| Journal Source | LOV Oracle | |
| Ledger/Ledger Set | LOV Oracle | |
| Period From | LOV Oracle | |
| Period To | LOV Oracle | |
| Event Class | LOV Oracle | |
| Journal Category | LOV Oracle |