AR Sales Journal by GL Account
Description
- Categories
- BI Publisher
- Application
- Receivables
- Source
- Sales Journal by GL Account Report (XML)
- Short Name
- ARSJGLARPT
- DB package
- XLA_JELINES_RPT_PKG
Imported from BI Publisher
Sales Journal by GL Account Report using XLA Journal Entries Report data extract
Sales Journal by GL Account Report using XLA Journal Entries Report data extract
SQL 204 lines
SELECT TABLE1.GL_DATE GL_DATE ,TABLE1.CREATED_BY CREATED_BY ,TABLE1.CREATION_DATE CREATION_DATE ,TABLE1.LAST_UPDATE_DATE LAST_UPDATE_DATE ,TABLE1.GL_TRANSFER_DATE GL_TRANSFER_DATE ,TABLE1.REFERENCE_DATE REFERENCE_DATE ,TABLE1.COMPLETED_DATE COMPLETED_DATE ,TABLE1.EXTERNAL_REFERENCE EXTERNAL_REFERENCE ,TABLE1.REFERENCE_1 REFERENCE_1 ,TABLE1.REFERENCE_4 REFERENCE_4 ,TABLE1.PERIOD_YEAR PERIOD_YEAR ,TABLE1.PERIOD_YEAR_START_DATE PERIOD_YEAR_START_DATE ,TABLE1.PERIOD_YEAR_END_DATE PERIOD_YEAR_END_DATE ,TABLE1.PERIOD_NUMBER PERIOD_NUMBER ,TABLE1.PERIOD_NAME PERIOD_NAME ,TABLE1.PERIOD_START_DATE PERIOD_START_DATE ,TABLE1.PERIOD_END_DATE PERIOD_END_DATE ,TABLE1.TRANSACTION_NUMBER TRANSACTION_NUMBER ,TABLE1.TRANSACTION_DATE TRANSACTION_DATE ,TABLE1.ACCOUNTING_SEQUENCE_NAME ACCOUNTING_SEQUENCE_NAME ,TABLE1.ACCOUNTING_SEQUENCE_VERSION ACCOUNTING_SEQUENCE_VERSION ,TABLE1.ACCOUNTING_SEQUENCE_NUMBER ACCOUNTING_SEQUENCE_NUMBER ,TABLE1.REPORTING_SEQUENCE_NAME REPORTING_SEQUENCE_NAME ,TABLE1.REPORTING_SEQUENCE_VERSION REPORTING_SEQUENCE_VERSION ,TABLE1.REPORTING_SEQUENCE_NUMBER REPORTING_SEQUENCE_NUMBER ,TABLE1.DOCUMENT_CATEGORY DOCUMENT_CATEGORY ,TABLE1.DOCUMENT_SEQUENCE_NAME DOCUMENT_SEQUENCE_NAME ,TABLE1.DOCUMENT_SEQUENCE_NUMBER DOCUMENT_SEQUENCE_NUMBER ,TABLE1.APPLICATION_ID APPLICATION_ID ,TABLE1.APPLICATION_NAME APPLICATION_NAME ,TABLE1.LEDGER_ID LEDGER_ID ,TABLE1.LEDGER_SHORT_NAME LEDGER_SHORT_NAME ,TABLE1.LEDGER_DESCRIPTION LEDGER_DESCRIPTION ,TABLE1.LEDGER_NAME LEDGER_NAME ,TABLE1.LEDGER_CURRENCY LEDGER_CURRENCY ,TABLE1.HEADER_ID HEADER_ID ,TABLE1.HEADER_DESCRIPTION HEADER_DESCRIPTION ,TABLE1.JOURNAL_ENTRY_STATUS JOURNAL_ENTRY_STATUS ,TABLE1.TRANSFER_TO_GL_STATUS TRANSFER_TO_GL_STATUS ,TABLE1.BALANCE_TYPE_CODE BALANCE_TYPE_CODE ,TABLE1.BALANCE_TYPE BALANCE_TYPE ,TABLE1.BUDGET_NAME BUDGET_NAME ,TABLE1.ENCUMBRANCE_TYPE ENCUMBRANCE_TYPE ,TABLE1.FUND_STATUS FUND_STATUS ,TABLE1.JE_CATEGORY_NAME JE_CATEGORY_NAME ,TABLE1.JE_SOURCE_NAME JE_SOURCE_NAME ,TABLE1.GL_BATCH_NAME GL_BATCH_NAME ,TABLE1.GL_DEFAULT_EFFECTIVE_DATE GL_DEFAULT_EFFECTIVE_DATE ,TABLE1.GL_BATCH_STATUS GL_BATCH_STATUS ,TABLE1.POSTED_DATE POSTED_DATE ,TABLE1.GL_JE_NAME GL_JE_NAME ,TABLE1.GL_DOC_SEQUENCE_NAME GL_DOC_SEQUENCE_NAME ,TABLE1.GL_DOC_SEQUENCE_VALUE GL_DOC_SEQUENCE_VALUE ,TABLE1.GL_LINE_NUMBER GL_LINE_NUMBER ,TABLE1.EVENT_ID EVENT_ID ,TABLE1.EVENT_DATE EVENT_DATE ,TABLE1.EVENT_NUMBER EVENT_NUMBER ,TABLE1.EVENT_CLASS_CODE EVENT_CLASS_CODE ,TABLE1.EVENT_CLASS_NAME EVENT_CLASS_NAME ,TABLE1.EVENT_TYPE_CODE EVENT_TYPE_CODE ,TABLE1.EVENT_TYPE_NAME EVENT_TYPE_NAME ,TABLE1.LINE_NUMBER LINE_NUMBER ,TABLE1.ACCOUNTING_CLASS_CODE ACCOUNTING_CLASS_CODE ,TABLE1.ACCOUNTING_CLASS_NAME ACCOUNTING_CLASS_NAME ,TABLE1.LINE_DESCRIPTION LINE_DESCRIPTION ,TABLE1.CODE_COMBINATION_ID CODE_COMBINATION_ID ,TABLE1.ACCOUNTING_CODE_COMBINATION ACCOUNTING_CODE_COMBINATION ,TABLE1.CODE_COMBINATION_DESCRIPTION CODE_COMBINATION_DESCRIPTION ,TABLE1.CONTROL_ACCOUNT_FLAG CONTROL_ACCOUNT_FLAG ,TABLE1.ENTERED_CURRENCY ENTERED_CURRENCY ,TABLE1.CONVERSION_RATE CONVERSION_RATE ,TABLE1.CONVERSION_RATE_DATE CONVERSION_RATE_DATE ,TABLE1.CONVERSION_RATE_TYPE_CODE CONVERSION_RATE_TYPE_CODE ,TABLE1.CONVERSION_RATE_TYPE CONVERSION_RATE_TYPE ,TABLE1.ENTERED_DR ENTERED_DR ,TABLE1.ENTERED_CR ENTERED_CR ,TABLE1.UNROUNDED_ACCOUNTED_DR UNROUNDED_ACCOUNTED_DR ,TABLE1.UNROUNDED_ACCOUNTED_CR UNROUNDED_ACCOUNTED_CR ,TABLE1.ACCOUNTED_DR ACCOUNTED_DR ,TABLE1.ACCOUNTED_CR ACCOUNTED_CR ,TABLE1.STATISTICAL_AMOUNT STATISTICAL_AMOUNT ,TABLE1.RECONCILIATION_REFERENCE RECONCILIATION_REFERENCE ,TABLE1.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY ,TABLE1.ATTRIBUTE1 ATTRIBUTE1 ,TABLE1.ATTRIBUTE2 ATTRIBUTE2 ,TABLE1.ATTRIBUTE3 ATTRIBUTE3 ,TABLE1.ATTRIBUTE4 ATTRIBUTE4 ,TABLE1.ATTRIBUTE5 ATTRIBUTE5 ,TABLE1.ATTRIBUTE6 ATTRIBUTE6 ,TABLE1.ATTRIBUTE7 ATTRIBUTE7 ,TABLE1.ATTRIBUTE8 ATTRIBUTE8 ,TABLE1.ATTRIBUTE9 ATTRIBUTE9 ,TABLE1.ATTRIBUTE10 ATTRIBUTE10 ,TABLE1.PARTY_TYPE_CODE PARTY_TYPE_CODE ,TABLE1.PARTY_TYPE PARTY_TYPE &p_party_details_col ,TABLE1.BALANCING_SEGMENT BALANCING_SEGMENT ,TABLE1.NATURAL_ACCOUNT_SEGMENT NATURAL_ACCOUNT_SEGMENT ,TABLE1.COST_CENTER_SEGMENT COST_CENTER_SEGMENT ,TABLE1.MANAGEMENT_SEGMENT MANAGEMENT_SEGMENT ,TABLE1.INTERCOMPANY_SEGMENT INTERCOMPANY_SEGMENT ,TABLE1.BALANCING_SEGMENT_DESC BALANCING_SEGMENT_DESC ,TABLE1.NATURAL_ACCOUNT_DESC NATURAL_ACCOUNT_DESC ,TABLE1.COST_CENTER_DESC COST_CENTER_DESC ,TABLE1.MANAGEMENT_SEGMENT_DESC MANAGEMENT_SEGMENT_DESC ,TABLE1.INTERCOMPANY_SEGMENT_DESC INTERCOMPANY_SEGMENT_DESC ,TABLE1.SEGMENT1 SEGMENT1 ,TABLE1.SEGMENT2 SEGMENT2 ,TABLE1.SEGMENT3 SEGMENT3 ,TABLE1.SEGMENT4 SEGMENT4 ,TABLE1.SEGMENT5 SEGMENT5 ,TABLE1.SEGMENT6 SEGMENT6 ,TABLE1.SEGMENT7 SEGMENT7 ,TABLE1.SEGMENT8 SEGMENT8 ,TABLE1.SEGMENT9 SEGMENT9 ,TABLE1.SEGMENT10 SEGMENT10 ,TABLE1.SEGMENT11 SEGMENT11 ,TABLE1.SEGMENT12 SEGMENT12 ,TABLE1.SEGMENT13 SEGMENT13 ,TABLE1.SEGMENT14 SEGMENT14 ,TABLE1.SEGMENT15 SEGMENT15 ,TABLE1.SEGMENT16 SEGMENT16 ,TABLE1.SEGMENT17 SEGMENT17 ,TABLE1.SEGMENT18 SEGMENT18 ,TABLE1.SEGMENT19 SEGMENT19 ,TABLE1.SEGMENT20 SEGMENT20 ,TABLE1.SEGMENT21 SEGMENT21 ,TABLE1.SEGMENT22 SEGMENT22 ,TABLE1.SEGMENT23 SEGMENT23 ,TABLE1.SEGMENT24 SEGMENT24 ,TABLE1.SEGMENT25 SEGMENT25 ,TABLE1.SEGMENT26 SEGMENT26 ,TABLE1.SEGMENT27 SEGMENT27 ,TABLE1.SEGMENT28 SEGMENT28 ,TABLE1.SEGMENT29 SEGMENT29 ,TABLE1.SEGMENT30 SEGMENT30 ,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 &p_le_col ,TABLE1.ORIG_LINE_NUMBER ORIG_LINE_NUMBER ,TABLE1.LEGAL_CREATED_ID LEGAL_CREATED_ID ,TABLE1.LEGAL_POSTED_ID LEGAL_POSTED_ID FROM (&p_sla_col_1 &p_gl_columns &p_sla_col_2 &p_party_details &p_sla_qualifier_segment &p_sla_col_3 &p_trx_identifiers_1 &p_trx_identifiers_2 &p_trx_identifiers_3 &p_trx_identifiers_4 &p_trx_identifiers_5 &p_sla_legal_ent_col &p_sla_from &p_sla_seg_desc_from &p_gl_view &p_sla_legal_ent_from &p_party_from &p_sla_join &p_sla_seg_desc_join &p_other_param_filter &p_gl_join &p_sla_legal_ent_join &p_party_join &p_union_all &p_gl_col_1 &p_gl_party_details &p_gl_qualifier_segment &p_gl_col_2 &p_gl_legal_ent_col &p_gl_from &p_gl_seg_desc_from &p_gl_legal_ent_from &p_gl_where &p_gl_seg_desc_join &p_gl_legal_ent_join) TABLE1 &p_le_from WHERE 1 = 1 &p_trx_id_filter &p_le_join &p_order_by_clause |
Parameters 18
| Parameter | Type | List of values SQL |
|---|---|---|
| Ledger | LOV Oracle | |
| Operating Unit | LOV Oracle | |
| Order by | LOV Oracle | |
| GL Account Low | Char | |
| GL Account High | Char | |
| GL Date From | Date | |
| GL Date To | Date | |
| Entered Currency | LOV Oracle | |
| Transfer or Posting Status | LOV Oracle | |
| Customer Name | LOV Oracle | |
| Customer Number Low | LOV Oracle | |
| Customer Number High | LOV Oracle | |
| Transaction Date From | Date | |
| Transaction Date To | Date | |
| Transaction Number Low | LOV Oracle | |
| Transaction Number High | LOV Oracle | |
| Include Zero Amount Lines | LOV Oracle | |
| Match with GL | LOV Oracle |