Home » Applications » Oracle Fusion Apps & E-Business Suite » Customize account analysis report (payables r12)
| Customize account analysis report [message #496055] |
Thu, 24 February 2011 02:22  |
nestor
Messages: 34
Registered: September 2010
|
Member |
|
|
Hi All,
I was assigned to create a customize account analysis report so i downloaded the XLAAARPT.XML file and edited it as shown below.
<?xml version = '1.0' encoding = 'UTF-8'?>
<!-- $Header: XLAAARPT.xml 120.29.12010000.8 2009/08/17 11:49:39 krsankar ship $ -->
<!-- dbdrv: exec java oracle/apps/xdo/oa/util XDOLoader.class java &phase=dat checkfile:~PROD:patch/115/publisher/defs:XLAAARPT.xml UPLOAD -DB_USERNAME &un_apps -DB_PASSWORD &pw_apps -JDBC_CONNECTION &jdbc_db_addr -LOB_TYPE DATA_TEMPLATE -APPS_SHORT_NAME ~PROD -LOB_CODE XLAAARPT -LANGUAGE 00 -XDO_FILE_TYPE XML-DATA-TEMPLATE -FILE_NAME &fullpath:~PROD:patch/115/publisher/defs:XLAAARPT.xml -->
<!-- dbdrv: exec java oracle/apps/xdo/oa/util XDOLoader.class java &phase=dat checkfile:~PROD:patch/115/publisher/defs:XLAAARPT.xml UPLOAD -DB_USERNAME &un_apps -DB_PASSWORD &pw_apps -JDBC_CONNECTION &jdbc_db_addr -LOB_TYPE DATA_TEMPLATE -APPS_SHORT_NAME JG -LOB_CODE JGSLAAANA -LANGUAGE 00 -XDO_FILE_TYPE XML-DATA-TEMPLATE -FILE_NAME &fullpath:~PROD:patch/115/publisher/defs:XLAAARPT.xml -->
<!-- dbdrv: exec java oracle/apps/xdo/oa/util XDOLoader.class java &phase=dat checkfile:~PROD:patch/115/publisher/defs:XLAAARPT.xml UPLOAD -DB_USERNAME &un_apps -DB_PASSWORD &pw_apps -JDBC_CONNECTION &jdbc_db_addr -LOB_TYPE DATA_TEMPLATE -APPS_SHORT_NAME JG -LOB_CODE JGSLAAAR -LANGUAGE 00 -XDO_FILE_TYPE XML-DATA-TEMPLATE -FILE_NAME &fullpath:~PROD:patch/115/publisher/defs:XLAAARPT.xml -->
<!-- dbdrv: exec java oracle/apps/xdo/oa/util XDOLoader.class java &phase=dat checkfile:~PROD:patch/115/publisher/defs:XLAAARPT.xml UPLOAD -DB_USERNAME &un_apps -DB_PASSWORD &pw_apps -JDBC_CONNECTION &jdbc_db_addr -LOB_TYPE DATA_TEMPLATE -APPS_SHORT_NAME CLA -LOB_CODE CLACNGLLEDGER -LANGUAGE 00 -XDO_FILE_TYPE XML-DATA-TEMPLATE -FILE_NAME &fullpath:~PROD:patch/115/publisher/defs:XLAAARPT.xml -->
<!-- dbdrv: exec java oracle/apps/xdo/oa/util XDOLoader.class java &phase=dat checkfile:~PROD:patch/115/publisher/defs:XLAAARPT.xml UPLOAD -DB_USERNAME &un_apps -DB_PASSWORD &pw_apps -JDBC_CONNECTION &jdbc_db_addr -LOB_TYPE DATA_TEMPLATE -APPS_SHORT_NAME CLA -LOB_CODE CLACNGLDAILYCASH -LANGUAGE 00 -XDO_FILE_TYPE XML-DATA-TEMPLATE -FILE_NAME &fullpath:~PROD:patch/115/publisher/defs:XLAAARPT.xml -->
<!-- GSCC_Category="Xdo Data Definition" -->
<dataTemplate name="NWCXLAAARPT" defaultPackage="XLA_ACCT_ANALYSIS_RPT_PKG" version="1.0">
<properties>
<property name="db_fetch_size" value="20" />
</properties>
<parameters>
<parameter name="P_RESP_APPLICATION_ID" dataType="number"/>
<parameter name="P_LEDGER_ID" dataType="character"/>
<parameter name="P_LEDGER" dataType="character"/>
<parameter name="P_COA_ID" dataType="number"/>
<parameter name="P_LEGAL_ENTITY_ID" dataType="number"/>
<parameter name="P_LEGAL_ENTITY" dataType="character"/>
<parameter name="P_PERIOD_FROM" dataType="character"/>
<parameter name="P_PERIOD_TO" dataType="character"/>
<parameter name="P_GL_DATE_FROM" dataType="date"/>
<parameter name="P_GL_DATE_TO" dataType="date"/>
<parameter name="P_BALANCE_TYPE_CODE" dataType="character"/>
<parameter name="P_BALANCE_TYPE" dataType="character"/>
<parameter name="P_DUMMY_BUDGET_VERSION" dataType="character"/>
<parameter name="P_BUDGET_VERSION_ID" dataType="number"/>
<parameter name="P_BUDGET_NAME" dataType="character"/>
<parameter name="P_DUMMY_ENCUMBRANCE_TYPE" dataType="character"/>
<parameter name="P_ENCUMBRANCE_TYPE_ID" dataType="number"/>
<parameter name="P_ENCUMBRANCE_TYPE" dataType="character"/>
<parameter name="P_BALANCE_SIDE_CODE" dataType="character"/>
<parameter name="P_BALANCE_SIDE" dataType="character"/>
<parameter name="P_BALANCE_AMOUNT_FROM" dataType="number"/>
<parameter name="P_BALANCE_AMOUNT_TO" dataType="number"/>
<parameter name="P_BALANCING_SEGMENT_FROM" dataType="character"/>
<parameter name="P_BALANCING_SEGMENT_TO" dataType="character"/>
<parameter name="P_ACCOUNT_SEGMENT_FROM" dataType="character"/>
<parameter name="P_ACCOUNT_SEGMENT_TO" dataType="character"/>
<parameter name="P_ACCOUNT_FLEXFIELD_FROM" dataType="character"/>
<parameter name="P_ACCOUNT_FLEXFIELD_TO" dataType="character"/>
<parameter name="P_INCLUDE_ZERO_AMOUNT_LINES" dataType="character"/>
<parameter name="P_INCLUDE_ZERO_AMT_LINES" dataType="character"/>
<parameter name="P_INCLUDE_USER_TRX_ID_FLAG" dataType="character"/>
<parameter name="P_INCLUDE_USER_TRX_ID" dataType="character"/>
<parameter name="P_INCLUDE_TAX_DETAILS_FLAG" dataType="character"/>
<parameter name="P_INCLUDE_TAX_DETAILS" dataType="character"/>
<parameter name="P_INCLUDE_LE_INFO_FLAG" dataType="character"/>
<parameter name="P_INCLUDE_LEGAL_ENTITY" dataType="character"/>
<parameter name="P_CUSTOM_PARAMETER_1" dataType="character"/>
<parameter name="P_CUSTOM_PARAMETER_2" dataType="character"/>
<parameter name="P_CUSTOM_PARAMETER_3" dataType="character"/>
<parameter name="P_CUSTOM_PARAMETER_4" dataType="character"/>
<parameter name="P_CUSTOM_PARAMETER_5" dataType="character"/>
<parameter name="P_CUSTOM_PARAMETER_6" dataType="character"/>
<parameter name="P_CUSTOM_PARAMETER_7" dataType="character"/>
<parameter name="P_CUSTOM_PARAMETER_8" dataType="character"/>
<parameter name="P_CUSTOM_PARAMETER_9" dataType="character"/>
<parameter name="P_CUSTOM_PARAMETER_10" dataType="character"/>
<parameter name="P_CUSTOM_PARAMETER_11" dataType="character"/>
<parameter name="P_CUSTOM_PARAMETER_12" dataType="character"/>
<parameter name="P_CUSTOM_PARAMETER_13" dataType="character"/>
<parameter name="P_CUSTOM_PARAMETER_14" dataType="character"/>
<parameter name="P_CUSTOM_PARAMETER_15" dataType="character"/>
<parameter name="P_INCLUDE_STAT_AMOUNT_LINES" dataType="character"/>
<parameter name="P_INCLUDE_STAT_AMT_LINES" dataType="character"/>
<parameter name="P_INCLUDE_ACCT_WITH_NO_ACT" dataType="character"/>
<parameter name="P_INC_ACCT_WITH_NO_ACT" dataType="character"/>
</parameters>
<dataQuery>
<sqlStatement name="Q_JELINES">
<![CDATA[
SELECT
A.GL_DATE GL_DATE
,A.CREATED_BY CREATED_BY
,A.CREATION_DATE CREATION_DATE
,A.LAST_UPDATE_DATE LAST_UPDATE_DATE
,A.GL_TRANSFER_DATE GL_TRANSFER_DATE
,A.REFERENCE_DATE REFERENCE_DATE
,A.COMPLETED_DATE COMPLETED_DATE
,A.TRANSACTION_NUMBER TRANSACTION_NUMBER
,A.TRANSACTION_DATE TRANSACTION_DATE
,A.ACCOUNTING_SEQUENCE_NAME ACCOUNTING_SEQUENCE_NAME
,A.ACCOUNTING_SEQUENCE_VERSION ACCOUNTING_SEQUENCE_VERSION
,A.ACCOUNTING_SEQUENCE_NUMBER ACCOUNTING_SEQUENCE_NUMBER
,A.REPORTING_SEQUENCE_NAME REPORTING_SEQUENCE_NAME
,A.REPORTING_SEQUENCE_VERSION REPORTING_SEQUENCE_VERSION
,A.REPORTING_SEQUENCE_NUMBER REPORTING_SEQUENCE_NUMBER
,A.DOCUMENT_CATEGORY DOCUMENT_CATEGORY
,A.DOCUMENT_SEQUENCE_NAME DOCUMENT_SEQUENCE_NAME
,A.DOCUMENT_SEQUENCE_NUMBER DOCUMENT_SEQUENCE_NUMBER
,A.APPLICATION_ID APPLICATION_ID
,A.APPLICATION_NAME APPLICATION_NAME
,A.HEADER_ID HEADER_ID
,A.HEADER_DESCRIPTION HEADER_DESCRIPTION
,A.FUND_STATUS FUND_STATUS
,A.JE_CATEGORY_NAME JE_CATEGORY_NAME
,A.JE_SOURCE_NAME JE_SOURCE_NAME
,A.EVENT_ID EVENT_ID
,A.EVENT_DATE EVENT_DATE
,A.EVENT_NUMBER EVENT_NUMBER
,A.EVENT_CLASS_CODE EVENT_CLASS_CODE
,A.EVENT_CLASS_NAME EVENT_CLASS_NAME
,A.EVENT_TYPE_CODE EVENT_TYPE_CODE
,A.EVENT_TYPE_NAME EVENT_TYPE_NAME
,A.GL_BATCH_NAME GL_BATCH_NAME
,A.POSTED_DATE POSTED_DATE
,A.GL_JE_NAME GL_JE_NAME
,A.GL_LINE_NUMBER GL_LINE_NUMBER
,A.LINE_NUMBER LINE_NUMBER
,A.ORIG_LINE_NUMBER ORIG_LINE_NUMBER
,A.ACCOUNTING_CLASS_CODE ACCOUNTING_CLASS_CODE
,A.ACCOUNTING_CLASS_NAME ACCOUNTING_CLASS_NAME
,A.LINE_DESCRIPTION LINE_DESCRIPTION
,A.ENTERED_CURRENCY ENTERED_CURRENCY
,A.CONVERSION_RATE CONVERSION_RATE
,A.CONVERSION_RATE_DATE CONVERSION_RATE_DATE
,A.CONVERSION_RATE_TYPE_CODE CONVERSION_RATE_TYPE_CODE
,A.CONVERSION_RATE_TYPE CONVERSION_RATE_TYPE
,A.ENTERED_DR ENTERED_DR
,A.ENTERED_CR ENTERED_CR
,A.UNROUNDED_ACCOUNTED_DR UNROUNDED_ACCOUNTED_DR
,A.UNROUNDED_ACCOUNTED_CR UNROUNDED_ACCOUNTED_CR
,A.ACCOUNTED_DR ACCOUNTED_DR
,A.ACCOUNTED_CR ACCOUNTED_CR
,A.STATISTICAL_AMOUNT STATISTICAL_AMOUNT
,A.RECONCILIATION_REFERENCE RECONCILIATION_REFERENCE
,A.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY
,A.ATTRIBUTE1 ATTRIBUTE1
,A.ATTRIBUTE2 ATTRIBUTE2
,A.ATTRIBUTE3 ATTRIBUTE3
,A.ATTRIBUTE4 ATTRIBUTE4
,A.ATTRIBUTE5 ATTRIBUTE5
,A.ATTRIBUTE6 ATTRIBUTE6
,A.ATTRIBUTE7 ATTRIBUTE7
,A.ATTRIBUTE8 ATTRIBUTE8
,A.ATTRIBUTE9 ATTRIBUTE9
,A.ATTRIBUTE10 ATTRIBUTE10
,A.PARTY_TYPE_CODE PARTY_TYPE_CODE
,A.PARTY_TYPE PARTY_TYPE
,PARTY_NUMBER
,PARTY_NAME
,PARTY_TYPE_TAXPAYER_ID
,PARTY_TAX_REGISTRATION_NUMBER
,PARTY_SITE_NUMBER
,PARTY_SITE_NAME
,PARTY_SITE_TAX_RGSTN_NUMBER
,USER_TRX_IDENTIFIER_NAME_1
,USER_TRX_IDENTIFIER_VALUE_1
,USER_TRX_IDENTIFIER_NAME_2
,USER_TRX_IDENTIFIER_VALUE_2
,USER_TRX_IDENTIFIER_NAME_3
,USER_TRX_IDENTIFIER_VALUE_3
,USER_TRX_IDENTIFIER_NAME_4
,USER_TRX_IDENTIFIER_VALUE_4
,USER_TRX_IDENTIFIER_NAME_5
,USER_TRX_IDENTIFIER_VALUE_5
,USER_TRX_IDENTIFIER_NAME_6
,USER_TRX_IDENTIFIER_VALUE_6
,USER_TRX_IDENTIFIER_NAME_7
,USER_TRX_IDENTIFIER_VALUE_7
,USER_TRX_IDENTIFIER_NAME_8
,USER_TRX_IDENTIFIER_VALUE_8
,USER_TRX_IDENTIFIER_NAME_9
,USER_TRX_IDENTIFIER_VALUE_9
,USER_TRX_IDENTIFIER_NAME_10
,USER_TRX_IDENTIFIER_VALUE_10
,A.LEDGER_ID LEDGER_ID
,A.LEDGER_SHORT_NAME LEDGER_SHORT_NAME
,A.LEDGER_DESCRIPTION LEDGER_DESCRIPTION
,A.LEDGER_NAME LEDGER_NAME
,A.LEDGER_CURRENCY LEDGER_CURRENCY
,A.PERIOD_YEAR PERIOD_YEAR
,A.PERIOD_NUMBER PERIOD_NUMBER
,A.PERIOD_NAME PERIOD_NAME
,A.PERIOD_START_DATE PERIOD_START_DATE
,A.PERIOD_END_DATE PERIOD_END_DATE
,A.BALANCE_TYPE_CODE BALANCE_TYPE_CODE
,A.BALANCE_TYPE BALANCE_TYPE
,A.BUDGET_NAME BUDGET_NAME
,A.ENCUMBRANCE_TYPE ENCUMBRANCE_TYPE
,A.BEGIN_BALANCE_DR BEGIN_BALANCE_DR
,A.BEGIN_BALANCE_CR BEGIN_BALANCE_CR
,B.PERIOD_NET_DR PERIOD_NET_DR
,B.PERIOD_NET_CR PERIOD_NET_CR
,A.CODE_COMBINATION_ID CODE_COMBINATION_ID
,A.ACCOUNTING_CODE_COMBINATION ACCOUNTING_CODE_COMBINATION
,A.CODE_COMBINATION_DESCRIPTION CODE_COMBINATION_DESCRIPTION
,A.CONTROL_ACCOUNT_FLAG CONTROL_ACCOUNT_FLAG
,A.CONTROL_ACCOUNT CONTROL_ACCOUNT
,A.BALANCING_SEGMENT BALANCING_SEGMENT
,A.NATURAL_ACCOUNT_SEGMENT NATURAL_ACCOUNT_SEGMENT
,A.COST_CENTER_SEGMENT COST_CENTER_SEGMENT
,A.MANAGEMENT_SEGMENT MANAGEMENT_SEGMENT
,A.INTERCOMPANY_SEGMENT INTERCOMPANY_SEGMENT
,A.BALANCING_SEGMENT_DESC BALANCING_SEGMENT_DESC
,A.NATURAL_ACCOUNT_DESC NATURAL_ACCOUNT_DESC
,A.COST_CENTER_DESC COST_CENTER_DESC
,A.MANAGEMENT_SEGMENT_DESC MANAGEMENT_SEGMENT_DESC
,A.INTERCOMPANY_SEGMENT_DESC INTERCOMPANY_SEGMENT_DESC
,A.SEGMENT1 SEGMENT1
,A.SEGMENT2 SEGMENT2
,A.SEGMENT3 SEGMENT3
,A.SEGMENT4 SEGMENT4
,A.SEGMENT5 SEGMENT5
,A.SEGMENT6 SEGMENT6
,A.SEGMENT7 SEGMENT7
,A.SEGMENT8 SEGMENT8
,A.SEGMENT9 SEGMENT9
,A.SEGMENT10 SEGMENT10
,A.SEGMENT11 SEGMENT11
,A.SEGMENT12 SEGMENT12
,A.SEGMENT13 SEGMENT13
,A.SEGMENT14 SEGMENT14
,A.SEGMENT15 SEGMENT15
,A.SEGMENT16 SEGMENT16
,A.SEGMENT17 SEGMENT17
,A.SEGMENT18 SEGMENT18
,A.SEGMENT19 SEGMENT19
,A.SEGMENT20 SEGMENT20
,A.SEGMENT21 SEGMENT21
,A.SEGMENT22 SEGMENT22
,A.SEGMENT23 SEGMENT23
,A.SEGMENT24 SEGMENT24
,A.SEGMENT25 SEGMENT25
,A.SEGMENT26 SEGMENT26
,A.SEGMENT27 SEGMENT27
,A.SEGMENT28 SEGMENT28
,A.SEGMENT29 SEGMENT29
,A.SEGMENT30 SEGMENT30
,A.BEGIN_RUNNING_TOTAL_CR BEGIN_RUNNING_TOTAL_CR
,A.BEGIN_RUNNING_TOTAL_DR BEGIN_RUNNING_TOTAL_DR
,A.END_RUNNING_TOTAL_CR END_RUNNING_TOTAL_CR
,A.END_RUNNING_TOTAL_DR END_RUNNING_TOTAL_DR
,A.LEGAL_ENTITY_ID LEGAL_ENTITY_ID
,A.LEGAL_ENTITY_NAME LEGAL_ENTITY_NAME
,A.LE_ADDRESS_LINE_1 LE_ADDRESS_LINE_1
,A.LE_ADDRESS_LINE_2 LE_ADDRESS_LINE_2
,A.LE_ADDRESS_LINE_3 LE_ADDRESS_LINE_3
,A.LE_CITY LE_CITY
,A.LE_REGION_1 LE_REGION_1
,A.LE_REGION_2 LE_REGION_2
,A.LE_REGION_3 LE_REGION_3
,A.LE_POSTAL_CODE LE_POSTAL_CODE
,A.LE_COUNTRY LE_COUNTRY
,A.LE_REGISTRATION_NUMBER LE_REGISTRATION_NUMBER
,A.LE_REGISTRATION_EFFECTIVE_FROM LE_REGISTRATION_EFFECTIVE_FROM
,A.LE_BR_DAILY_INSCRIPTION_NUMBER LE_BR_DAILY_INSCRIPTION_NUMBER
,A.LE_BR_DAILY_INSCRIPTION_DATE LE_BR_DAILY_INSCRIPTION_DATE
,A.LE_BR_DAILY_ENTITY LE_BR_DAILY_ENTITY
,A.LE_BR_DAILY_LOCATION LE_BR_DAILY_LOCATION
,A.LE_BR_DIRECTOR_NUMBER LE_BR_DIRECTOR_NUMBER
,A.LE_BR_ACCOUNTANT_NUMBER LE_BR_ACCOUNTANT_NUMBER
,A.LE_BR_ACCOUNTANT_NAME LE_BR_ACCOUNTANT_NAME
FROM
(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.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.HEADER_ID HEADER_ID
,TABLE1.HEADER_DESCRIPTION HEADER_DESCRIPTION
,TABLE1.FUND_STATUS FUND_STATUS
,TABLE1.JE_CATEGORY_NAME JE_CATEGORY_NAME
,TABLE1.JE_SOURCE_NAME JE_SOURCE_NAME
,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.GL_BATCH_NAME GL_BATCH_NAME
,TABLE1.POSTED_DATE POSTED_DATE
,TABLE1.GL_JE_NAME GL_JE_NAME
,TABLE1.GL_LINE_NUMBER GL_LINE_NUMBER
,TABLE1.LINE_NUMBER LINE_NUMBER
,TABLE1.ORIG_LINE_NUMBER ORIG_LINE_NUMBER
,TABLE1.ACCOUNTING_CLASS_CODE ACCOUNTING_CLASS_CODE
,TABLE1.ACCOUNTING_CLASS_NAME ACCOUNTING_CLASS_NAME
,TABLE1.LINE_DESCRIPTION LINE_DESCRIPTION
,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
,substr(PARTY_INFO,1,instr(PARTY_INFO,'|',1,1)-1 ) PARTY_NUMBER
,substr(PARTY_INFO,instr(PARTY_INFO,'|',1,1)+1,(instr(PARTY_INFO,'|',1,2)-1-instr(PARTY_INFO,'|',1,1))) PARTY_NAME
,substr(PARTY_INFO,instr(PARTY_INFO,'|',1,2)+1,(instr(PARTY_INFO,'|',1,3)-1-instr(PARTY_INFO,'|',1,2))) PARTY_TYPE_TAXPAYER_ID
,substr(PARTY_INFO,instr(PARTY_INFO,'|',1,3)+1,(instr(PARTY_INFO,'|',1,4)-1-instr(PARTY_INFO,'|',1,3))) PARTY_TAX_REGISTRATION_NUMBER
,substr(PARTY_INFO,instr(PARTY_INFO,'|',1,4)+1,(instr(PARTY_INFO,'|',1,5)-1-instr(PARTY_INFO,'|',1,4))) PARTY_SITE_NUMBER
,substr(PARTY_INFO,instr(PARTY_INFO,'|',1,5)+1,(instr(PARTY_INFO,'|',1,6)-1-instr(PARTY_INFO,'|',1,5))) PARTY_SITE_NAME
,substr(PARTY_INFO,instr(PARTY_INFO,'|',1,6)+1,(length(PARTY_INFO)- instr(PARTY_INFO,'|',1,6))) PARTY_SITE_TAX_RGSTN_NUMBER
,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
,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.PERIOD_YEAR PERIOD_YEAR
,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.BALANCE_TYPE_CODE BALANCE_TYPE_CODE
,TABLE1.BALANCE_TYPE BALANCE_TYPE
,TABLE1.BUDGET_NAME BUDGET_NAME
,TABLE1.ENCUMBRANCE_TYPE ENCUMBRANCE_TYPE
,TABLE1.BEGIN_BALANCE_DR BEGIN_BALANCE_DR
,TABLE1.BEGIN_BALANCE_CR BEGIN_BALANCE_CR
,TABLE1.PERIOD_NET_DR PERIOD_NET_DR
,TABLE1.PERIOD_NET_CR PERIOD_NET_CR
,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.CONTROL_ACCOUNT CONTROL_ACCOUNT
,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
,TABLE1.BEGIN_RUNNING_TOTAL_CR BEGIN_RUNNING_TOTAL_CR
,TABLE1.BEGIN_RUNNING_TOTAL_DR BEGIN_RUNNING_TOTAL_DR
,TABLE1.END_RUNNING_TOTAL_CR END_RUNNING_TOTAL_CR
,TABLE1.END_RUNNING_TOTAL_DR END_RUNNING_TOTAL_DR
,TABLE1.LEGAL_ENTITY_ID LEGAL_ENTITY_ID
,TABLE1.LEGAL_ENTITY_NAME LEGAL_ENTITY_NAME
,TABLE1.LE_ADDRESS_LINE_1 LE_ADDRESS_LINE_1
,TABLE1.LE_ADDRESS_LINE_2 LE_ADDRESS_LINE_2
,TABLE1.LE_ADDRESS_LINE_3 LE_ADDRESS_LINE_3
,TABLE1.LE_CITY LE_CITY
,TABLE1.LE_REGION_1 LE_REGION_1
,TABLE1.LE_REGION_2 LE_REGION_2
,TABLE1.LE_REGION_3 LE_REGION_3
,TABLE1.LE_POSTAL_CODE LE_POSTAL_CODE
,TABLE1.LE_COUNTRY LE_COUNTRY
,TABLE1.LE_REGISTRATION_NUMBER LE_REGISTRATION_NUMBER
,TABLE1.LE_REGISTRATION_EFFECTIVE_FROM LE_REGISTRATION_EFFECTIVE_FROM
,TABLE1.LE_BR_DAILY_INSCRIPTION_NUMBER LE_BR_DAILY_INSCRIPTION_NUMBER
,TABLE1.LE_BR_DAILY_INSCRIPTION_DATE LE_BR_DAILY_INSCRIPTION_DATE
,TABLE1.LE_BR_DAILY_ENTITY LE_BR_DAILY_ENTITY
,TABLE1.LE_BR_DAILY_LOCATION LE_BR_DAILY_LOCATION
,TABLE1.LE_BR_DIRECTOR_NUMBER LE_BR_DIRECTOR_NUMBER
,TABLE1.LE_BR_ACCOUNTANT_NUMBER LE_BR_ACCOUNTANT_NUMBER
,TABLE1.LE_BR_ACCOUNTANT_NAME LE_BR_ACCOUNTANT_NAME
FROM
(SELECT /*+ leading (glbgt gjl gjh gir ael aeh) */
to_char(aeh.accounting_date
,'YYYY-MM-DD') GL_DATE
,fdu.user_name CREATED_BY
,to_char(aeh.creation_date
,'YYYY-MM-DD"T"hh:mi:ss') CREATION_DATE
,to_char(aeh.last_update_date
,'YYYY-MM-DD') LAST_UPDATE_DATE
,to_char(aeh.gl_transfer_date
,'YYYY-MM-DD"T"hh:mi:ss') GL_TRANSFER_DATE
,to_char(aeh.reference_date
,'YYYY-MM-DD') REFERENCE_DATE
,to_char(aeh.completed_date
,'YYYY-MM-DD"T"hh:mi:ss') COMPLETED_DATE
,ent.transaction_number TRANSACTION_NUMBER
,to_char(xle.transaction_date
,'YYYY-MM-DD"T"hh:mi:ss') TRANSACTION_DATE
,fsv1.header_name ACCOUNTING_SEQUENCE_NAME
,fsv1.version_name ACCOUNTING_SEQUENCE_VERSION
,aeh.completion_acct_seq_value ACCOUNTING_SEQUENCE_NUMBER
,fsv2.header_name REPORTING_SEQUENCE_NAME
,fsv2.version_name REPORTING_SEQUENCE_VERSION
,aeh.close_acct_seq_value REPORTING_SEQUENCE_NUMBER
,NULL DOCUMENT_CATEGORY
,fns.name DOCUMENT_SEQUENCE_NAME
,aeh.doc_sequence_value DOCUMENT_SEQUENCE_NUMBER
,aeh.application_id APPLICATION_ID
,fap.application_name APPLICATION_NAME
,aeh.ae_header_id HEADER_ID
,aeh.description HEADER_DESCRIPTION
,xlk1.meaning FUND_STATUS
,gjct.user_je_category_name JE_CATEGORY_NAME
,gjst.user_je_source_name JE_SOURCE_NAME
,xle.event_id EVENT_ID
,to_char(xle.event_date
,'YYYY-MM-DD') EVENT_DATE
,xle.event_number EVENT_NUMBER
,xet.event_class_code EVENT_CLASS_CODE
,xect.NAME EVENT_CLASS_NAME
,aeh.event_type_code EVENT_TYPE_CODE
,xet.NAME EVENT_TYPE_NAME
,gjb.NAME GL_BATCH_NAME
,to_char(gjb.posted_date
,'YYYY-MM-DD') POSTED_DATE
,gjh.NAME GL_JE_NAME
,gjh.external_reference EXTERNAL_REFERENCE
,gjl.je_line_num GL_LINE_NUMBER
,ael.displayed_line_number LINE_NUMBER
,ael.ae_line_num ORIG_LINE_NUMBER
,ael.accounting_class_code ACCOUNTING_CLASS_CODE
,xlk2.meaning ACCOUNTING_CLASS_NAME
,ael.description LINE_DESCRIPTION
,ael.currency_code ENTERED_CURRENCY
,ael.currency_conversion_rate CONVERSION_RATE
,to_char(ael.currency_conversion_date
,'YYYY-MM-DD') CONVERSION_RATE_DATE
,ael.currency_conversion_type CONVERSION_RATE_TYPE_CODE
,gdct.user_conversion_type CONVERSION_RATE_TYPE
,ael.entered_dr ENTERED_DR
,ael.entered_cr ENTERED_CR
,ael.unrounded_accounted_dr UNROUNDED_ACCOUNTED_DR
,ael.unrounded_accounted_cr UNROUNDED_ACCOUNTED_CR
,ael.accounted_dr ACCOUNTED_DR
,ael.accounted_cr ACCOUNTED_CR
,ael.statistical_amount STATISTICAL_AMOUNT
,ael.jgzz_recon_ref RECONCILIATION_REFERENCE
,ael.attribute_category ATTRIBUTE_CATEGORY
,ael.attribute1 ATTRIBUTE1
,ael.attribute2 ATTRIBUTE2
,ael.attribute3 ATTRIBUTE3
,ael.attribute4 ATTRIBUTE4
,ael.attribute5 ATTRIBUTE5
,ael.attribute6 ATTRIBUTE6
,ael.attribute7 ATTRIBUTE7
,ael.attribute8 ATTRIBUTE8
,ael.attribute9 ATTRIBUTE9
,ael.attribute10 ATTRIBUTE10
,ael.party_type_code PARTY_TYPE_CODE
,NULL PARTY_TYPE
&p_party_columns PARTY_INFO
&p_trx_identifiers_1
&p_trx_identifiers_2
&p_trx_identifiers_3
&p_trx_identifiers_4
&p_trx_identifiers_5
,glbgt.ledger_id LEDGER_ID
,glbgt.ledger_short_name LEDGER_SHORT_NAME
,glbgt.ledger_description LEDGER_DESCRIPTION
,glbgt.ledger_name LEDGER_NAME
,glbgt.ledger_currency LEDGER_CURRENCY
,glbgt.period_year PERIOD_YEAR
,glbgt.period_number PERIOD_NUMBER
,glbgt.period_name PERIOD_NAME
,to_char(glbgt.period_start_date
,'YYYY-MM-DD') PERIOD_START_DATE
,to_char(glbgt.period_end_date
,'YYYY-MM-DD') PERIOD_END_DATE
,glbgt.balance_type_code BALANCE_TYPE_CODE
,glbgt.balance_type BALANCE_TYPE
,glbgt.budget_name BUDGET_NAME
,glbgt.encumbrance_type ENCUMBRANCE_TYPE
,glbgt.begin_balance_dr BEGIN_BALANCE_DR
,glbgt.begin_balance_cr BEGIN_BALANCE_CR
,glbgt.period_net_dr PERIOD_NET_DR
,glbgt.period_net_cr PERIOD_NET_CR
,glbgt.code_combination_id CODE_COMBINATION_ID
,glbgt.accounting_code_combination ACCOUNTING_CODE_COMBINATION
,glbgt.code_combination_description CODE_COMBINATION_DESCRIPTION
,glbgt.control_account_flag CONTROL_ACCOUNT_FLAG
,glbgt.control_account CONTROL_ACCOUNT
,glbgt.balancing_segment BALANCING_SEGMENT
,glbgt.natural_account_segment NATURAL_ACCOUNT_SEGMENT
,glbgt.cost_center_segment COST_CENTER_SEGMENT
,glbgt.management_segment MANAGEMENT_SEGMENT
,glbgt.intercompany_segment INTERCOMPANY_SEGMENT
,glbgt.balancing_segment_desc BALANCING_SEGMENT_DESC
,glbgt.natural_account_desc NATURAL_ACCOUNT_DESC
,glbgt.cost_center_desc COST_CENTER_DESC
,glbgt.management_segment_desc MANAGEMENT_SEGMENT_DESC
,glbgt.intercompany_segment_desc INTERCOMPANY_SEGMENT_DESC
,glbgt.segment1 SEGMENT1
,glbgt.segment2 SEGMENT2
,glbgt.segment3 SEGMENT3
,glbgt.segment4 SEGMENT4
,glbgt.segment5 SEGMENT5
,glbgt.segment6 SEGMENT6
,glbgt.segment7 SEGMENT7
,glbgt.segment8 SEGMENT8
,glbgt.segment9 SEGMENT9
,glbgt.segment10 SEGMENT10
,glbgt.segment11 SEGMENT11
,glbgt.segment12 SEGMENT12
,glbgt.segment13 SEGMENT13
,glbgt.segment14 SEGMENT14
,glbgt.segment15 SEGMENT15
,glbgt.segment16 SEGMENT16
,glbgt.segment17 SEGMENT17
,glbgt.segment18 SEGMENT18
,glbgt.segment19 SEGMENT19
,glbgt.segment20 SEGMENT20
,glbgt.segment21 SEGMENT21
,glbgt.segment22 SEGMENT22
,glbgt.segment23 SEGMENT23
,glbgt.segment24 SEGMENT24
,glbgt.segment25 SEGMENT25
,glbgt.segment26 SEGMENT26
,glbgt.segment27 SEGMENT27
,glbgt.segment28 SEGMENT28
,glbgt.segment29 SEGMENT29
,glbgt.segment30 SEGMENT30
,glbgt.begin_running_total_cr BEGIN_RUNNING_TOTAL_CR
,glbgt.begin_running_total_dr BEGIN_RUNNING_TOTAL_DR
,glbgt.end_running_total_cr END_RUNNING_TOTAL_CR
,glbgt.end_running_total_dr END_RUNNING_TOTAL_DR
,glbgt.legal_entity_id LEGAL_ENTITY_ID
,glbgt.legal_entity_name LEGAL_ENTITY_NAME
,glbgt.le_address_line_1 LE_ADDRESS_LINE_1
,glbgt.le_address_line_2 LE_ADDRESS_LINE_2
,glbgt.le_address_line_3 LE_ADDRESS_LINE_3
,glbgt.le_city LE_CITY
,glbgt.le_region_1 LE_REGION_1
,glbgt.le_region_2 LE_REGION_2
,glbgt.le_region_3 LE_REGION_3
,glbgt.le_postal_code LE_POSTAL_CODE
,glbgt.le_country LE_COUNTRY
,glbgt.le_registration_number LE_REGISTRATION_NUMBER
,glbgt.le_registration_effective_from LE_REGISTRATION_EFFECTIVE_FROM
,glbgt.le_br_daily_inscription_number LE_BR_DAILY_INSCRIPTION_NUMBER
,to_char(glbgt.le_br_daily_inscription_date
,'YYYY-MM-DD') LE_BR_DAILY_INSCRIPTION_DATE
,glbgt.le_br_daily_entity LE_BR_DAILY_ENTITY
,glbgt.le_br_daily_location LE_BR_DAILY_LOCATION
,glbgt.le_br_director_number LE_BR_DIRECTOR_NUMBER
,glbgt.le_br_accountant_number LE_BR_ACCOUNTANT_NUMBER
,glbgt.le_br_accountant_name LE_BR_ACCOUNTANT_NAME
FROM xla.xla_ae_headers aeh
,xla.xla_ae_lines ael
,apps.xla_lookups xlk1
,apps.xla_lookups xlk2
,xla.xla_events xle
,xla.xla_event_classes_tl xect
,xla.xla_event_types_tl xet
,applsys.fnd_user fdu
,xla.xla_transaction_entities ent
,applsys.fnd_application_tl fap
,fun.fun_seq_versions fsv1
,fun.fun_seq_versions fsv2
,applsys.fnd_document_sequences fns
,gl.gl_je_categories_tl gjct
,gl.gl_je_sources_tl gjst
,gl.gl_daily_conversion_types gdct
,gl.gl_import_references gir
,gl.gl_je_lines gjl
,gl.gl_je_headers gjh
,gl.gl_je_batches gjb
,xla.xla_report_balances_gt glbgt
WHERE &p_ledger_filters
--added as part of 6719948
AND gjl.ledger_id = glbgt.ledger_id
AND gjl.code_combination_id = glbgt.code_combination_id
AND gjl.effective_date BETWEEN glbgt.period_start_date AND glbgt.period_end_date
AND gjl.effective_date BETWEEN :P_GL_DATE_FROM AND :P_GL_DATE_TO
AND gjl.period_name = glbgt.period_name
AND gjl.je_header_id = gjh.je_header_id
AND gjl.period_name = gjh.period_name
AND gjl.je_header_id = gir.je_header_id
AND gjl.je_line_num = gir.je_line_num
AND gjh.je_header_id = gir.je_header_id
AND gjh.status = 'P'
AND NVL(gjh.je_from_sla_flag,'N') IN('Y','U')
AND gjb.je_batch_id = gir.je_batch_id
AND gjb.status = 'P'
AND gir.gl_sl_link_id = ael.gl_sl_link_id
AND gir.gl_sl_link_table = ael.gl_sl_link_table
--AND gjh.currency_code = glbgt.ledger_currency --added bug 6722505
AND gjct.je_category_name = aeh.je_category_name
AND gjct.LANGUAGE = USERENV('LANG')
AND gjst.je_source_name = gjh.je_source
AND gjst.LANGUAGE = USERENV('LANG')
AND aeh.accounting_entry_status_code = 'F'
AND aeh.gl_transfer_status_code = 'Y'
AND aeh.balance_type_code = glbgt.balance_type_code
AND NVL(aeh.budget_version_id,-19999) = NVL(glbgt.budget_version_id,-19999)
AND ael.application_id = aeh.application_id
AND ael.ae_header_id = aeh.ae_header_id
AND NVL(ael.encumbrance_type_id,-19999)= NVL(glbgt.encumbrance_type_id,-19999) -- 4458381
AND xlk1.lookup_type(+) = 'XLA_FUNDS_STATUS'
AND xlk1.lookup_code(+) = aeh.funds_status_code
AND xlk2.lookup_type = 'XLA_ACCOUNTING_CLASS'
AND xlk2.lookup_code = ael.accounting_class_code
AND xle.application_id = aeh.application_id
AND xle.event_id = aeh.event_id
AND xet.application_id = aeh.application_id
AND xet.event_type_code = aeh.event_type_code
AND xet.LANGUAGE = USERENV('LANG')
AND xect.application_id = xet.application_id
AND xect.entity_code = xet.entity_code
AND xect.event_class_code = xet.event_class_code
AND xect.LANGUAGE = USERENV('LANG')
AND ent.application_id = aeh.application_id
AND ent.entity_id = aeh.entity_id
--AND ent.ledger_id = aeh.ledger_id removed for Bug 7557990
AND fdu.user_id = ent.created_by
AND fap.application_id = aeh.application_id
AND fap.LANGUAGE = USERENV('LANG')
AND fsv1.seq_version_id(+) = aeh.completion_acct_seq_version_id
AND fsv2.seq_version_id(+) = aeh.close_acct_seq_version_id
AND fns.application_id(+) = aeh.application_id
AND fns.doc_sequence_id(+) = aeh.doc_sequence_id
AND gdct.conversion_type(+) = ael.currency_conversion_type
&p_sla_other_filter
--removed as part of 6719948
--AND ael.code_combination_id = glbgt.code_combination_id
--AND aeh.ledger_id = glbgt.ledger_id
--AND aeh.accounting_date BETWEEN glbgt.period_start_date AND glbgt.period_end_date
--AND aeh.accounting_date BETWEEN :P_GL_DATE_FROM AND :P_GL_DATE_TO
--AND aeh.period_name = glbgt.period_name
--AND gjh.ledger_id = aeh.ledger_id
UNION ALL
SELECT /*+ leading (glbgt gjl gjh gjb) */
to_char(gjh.default_effective_date
,'YYYY-MM-DD') GL_DATE
,fdu.user_name CREATED_BY
,to_char(gjh.creation_date
,'YYYY-MM-DD"T"hh:mi:ss') CREATION_DATE
,to_char(gjh.last_update_date
,'YYYY-MM-DD') LAST_UPDATE_DATE
,NULL GL_TRANSFER_DATE
,to_char(gjh.reference_date
,'YYYY-MM-DD') REFERENCE_DATE
,NULL COMPLETED_DATE
,NULL TRANSACTION_NUMBER
,NULL TRANSACTION_DATE
,fsv1.header_name ACCOUNTING_SEQUENCE_NAME
,fsv1.version_name ACCOUNTING_SEQUENCE_VERSION
,gjh.posting_acct_seq_value ACCOUNTING_SEQUENCE_NUMBER
,fsv2.header_name REPORTING_SEQUENCE_NAME
,fsv2.version_name REPORTING_SEQUENCE_VERSION
,gjh.close_acct_seq_value REPORTING_SEQUENCE_NUMBER
,NULL DOCUMENT_CATEGORY
,NULL DOCUMENT_SEQUENCE_NAME
,NULL DOCUMENT_SEQUENCE_NUMBER
,NULL APPLICATION_ID
,NULL APPLICATION_NAME
,gjh.je_header_id HEADER_ID
,gjh.description HEADER_DESCRIPTION
,NULL FUND_STATUS
,gjct.user_je_category_name JE_CATEGORY_NAME
,gjst.user_je_source_name JE_SOURCE_NAME
,NULL EVENT_ID
,NULL EVENT_DATE
,NULL EVENT_NUMBER
,NULL EVENT_CLASS_CODE
,NULL EVENT_CLASS_NAME
,NULL EVENT_TYPE_CODE
,NULL EVENT_TYPE_NAME
,gjb.NAME GL_BATCH_NAME
,to_char(gjb.posted_date
,'YYYY-MM-DD') POSTED_DATE
,gjh.NAME GL_JE_NAME
,gjh.external_reference EXTERNAL_REFERENCE
,gjl.je_line_num GL_LINE_NUMBER
,gjl.je_line_num LINE_NUMBER
,gjl.je_line_num ORIG_LINE_NUMBER
,NULL ACCOUNTING_CLASS_CODE
,NULL ACCOUNTING_CLASS_NAME
,gjl.description LINE_DESCRIPTION
,gjh.currency_code ENTERED_CURRENCY
,gjh.currency_conversion_rate CONVERSION_RATE
,to_char(gjh.currency_conversion_date
,'YYYY-MM-DD') CONVERSION_RATE_DATE
,gjh.currency_conversion_type CONVERSION_RATE_TYPE_CODE
,gdct.user_conversion_type CONVERSION_RATE_TYPE
,gjl.entered_dr ENTERED_DR
,gjl.entered_cr ENTERED_CR
,NULL UNROUNDED_ACCOUNTED_DR
,NULL UNROUNDED_ACCOUNTED_CR
,gjl.accounted_dr ACCOUNTED_DR
,gjl.accounted_cr ACCOUNTED_CR
,gjl.stat_amount STATISTICAL_AMOUNT
,gjl.jgzz_recon_ref_11i RECONCILIATION_REFERENCE
,gjl.context ATTRIBUTE_CATEGORY
,gjl.attribute1 ATTRIBUTE1
,gjl.attribute2 ATTRIBUTE2
,gjl.attribute3 ATTRIBUTE3
,gjl.attribute4 ATTRIBUTE4
,gjl.attribute5 ATTRIBUTE5
,gjl.attribute6 ATTRIBUTE6
,gjl.attribute7 ATTRIBUTE7
,gjl.attribute8 ATTRIBUTE8
,gjl.attribute9 ATTRIBUTE9
,gjl.attribute10 ATTRIBUTE10
,NULL PARTY_TYPE_CODE
,NULL PARTY_TYPE
,NULL PARTY_INFO
,NULL USERIDS
,glbgt.ledger_id LEDGER_ID
,glbgt.ledger_short_name LEDGER_SHORT_NAME
,glbgt.ledger_description LEDGER_DESCRIPTION
,glbgt.ledger_name LEDGER_NAME
,glbgt.ledger_currency LEDGER_CURRENCY
,glbgt.period_year PERIOD_YEAR
,glbgt.period_number PERIOD_NUMBER
,glbgt.period_name PERIOD_NAME
,to_char(glbgt.period_start_date
,'YYYY-MM-DD') PERIOD_START_DATE
,to_char(glbgt.period_end_date
,'YYYY-MM-DD') PERIOD_END_DATE
,glbgt.balance_type_code BALANCE_TYPE_CODE
,glbgt.balance_type BALANCE_TYPE
,glbgt.budget_name BUDGET_NAME
,glbgt.encumbrance_type ENCUMBRANCE_TYPE
,glbgt.begin_balance_dr BEGIN_BALANCE_DR
,glbgt.begin_balance_cr BEGIN_BALANCE_CR
,glbgt.period_net_dr PERIOD_NET_DR
,glbgt.period_net_cr PERIOD_NET_CR
,glbgt.code_combination_id CODE_COMBINATION_ID
,glbgt.accounting_code_combination ACCOUNTING_CODE_COMBINATION
,glbgt.code_combination_description CODE_COMBINATION_DESCRIPTION
,glbgt.control_account_flag CONTROL_ACCOUNT_FLAG
,glbgt.control_account CONTROL_ACCOUNT
,glbgt.balancing_segment BALANCING_SEGMENT
,glbgt.natural_account_segment NATURAL_ACCOUNT_SEGMENT
,glbgt.cost_center_segment COST_CENTER_SEGMENT
,glbgt.management_segment MANAGEMENT_SEGMENT
,glbgt.intercompany_segment INTERCOMPANY_SEGMENT
,glbgt.balancing_segment_desc BALANCING_SEGMENT_DESC
,glbgt.natural_account_desc NATURAL_ACCOUNT_DESC
,glbgt.cost_center_desc COST_CENTER_DESC
,glbgt.management_segment_desc MANAGEMENT_SEGMENT_DESC
,glbgt.intercompany_segment_desc INTERCOMPANY_SEGMENT_DESC
,glbgt.segment1 SEGMENT1
,glbgt.segment2 SEGMENT2
,glbgt.segment3 SEGMENT3
,glbgt.segment4 SEGMENT4
,glbgt.segment5 SEGMENT5
,glbgt.segment6 SEGMENT6
,glbgt.segment7 SEGMENT7
,glbgt.segment8 SEGMENT8
,glbgt.segment9 SEGMENT9
,glbgt.segment10 SEGMENT10
,glbgt.segment11 SEGMENT11
,glbgt.segment12 SEGMENT12
,glbgt.segment13 SEGMENT13
,glbgt.segment14 SEGMENT14
,glbgt.segment15 SEGMENT15
,glbgt.segment16 SEGMENT16
,glbgt.segment17 SEGMENT17
,glbgt.segment18 SEGMENT18
,glbgt.segment19 SEGMENT19
,glbgt.segment20 SEGMENT20
,glbgt.segment21 SEGMENT21
,glbgt.segment22 SEGMENT22
,glbgt.segment23 SEGMENT23
,glbgt.segment24 SEGMENT24
,glbgt.segment25 SEGMENT25
,glbgt.segment26 SEGMENT26
,glbgt.segment27 SEGMENT27
,glbgt.segment28 SEGMENT28
,glbgt.segment29 SEGMENT29
,glbgt.segment30 SEGMENT30
,glbgt.begin_running_total_cr BEGIN_RUNNING_TOTAL_CR
,glbgt.begin_running_total_dr BEGIN_RUNNING_TOTAL_DR
,glbgt.end_running_total_cr END_RUNNING_TOTAL_CR
,glbgt.end_running_total_dr END_RUNNING_TOTAL_DR
,glbgt.legal_entity_id LEGAL_ENTITY_ID
,glbgt.legal_entity_name LEGAL_ENTITY_NAME
,glbgt.le_address_line_1 LE_ADDRESS_LINE_1
,glbgt.le_address_line_2 LE_ADDRESS_LINE_2
,glbgt.le_address_line_3 LE_ADDRESS_LINE_3
,glbgt.le_city LE_CITY
,glbgt.le_region_1 LE_REGION_1
,glbgt.le_region_2 LE_REGION_2
,glbgt.le_region_3 LE_REGION_3
,glbgt.le_postal_code LE_POSTAL_CODE
,glbgt.le_country LE_COUNTRY
,glbgt.le_registration_number LE_REGISTRATION_NUMBER
,glbgt.le_registration_effective_from LE_REGISTRATION_EFFECTIVE_FROM
,glbgt.le_br_daily_inscription_number LE_BR_DAILY_INSCRIPTION_NUMBER
,to_char(glbgt.le_br_daily_inscription_date
,'YYYY-MM-DD') LE_BR_DAILY_INSCRIPTION_DATE
,glbgt.le_br_daily_entity LE_BR_DAILY_ENTITY
,glbgt.le_br_daily_location LE_BR_DAILY_LOCATION
,glbgt.le_br_director_number LE_BR_DIRECTOR_NUMBER
,glbgt.le_br_accountant_number LE_BR_ACCOUNTANT_NUMBER
,glbgt.le_br_accountant_name LE_BR_ACCOUNTANT_NAME
FROM applsys.fnd_user fdu
,fun.fun_seq_versions fsv1
,fun.fun_seq_versions fsv2
,gl.gl_je_categories_tl gjct
,gl.gl_je_sources_tl gjst
,gl.gl_daily_conversion_types gdct
,gl.gl_je_lines gjl
,gl.gl_je_headers gjh
,gl.gl_je_batches gjb
,xla.xla_report_balances_gt glbgt
WHERE gjl.ledger_id = glbgt.ledger_id
AND gjl.code_combination_id = glbgt.code_combination_id
AND gjl.effective_date BETWEEN glbgt.period_start_date AND glbgt.period_end_date
AND gjl.effective_date BETWEEN :P_GL_DATE_FROM AND :P_GL_DATE_TO
AND gjl.period_name = glbgt.period_name
AND gjh.je_header_id = gjl.je_header_id
AND gjh.actual_flag = glbgt.balance_type_code
AND decode(gjh.currency_code,'STAT',gjh.currency_code,glbgt.ledger_currency) = glbgt.ledger_currency --added bug 6686541
AND NVL(gjh.je_from_sla_flag,'N') = 'N'
AND NVL(gjh.budget_version_id,-19999)= NVL(glbgt.budget_version_id,-19999)
AND NVL(gjh.encumbrance_type_id,-19999)= NVL(glbgt.encumbrance_type_id,-19999)
AND gjb.je_batch_id = gjh.je_batch_id
AND gjb.status = 'P'
AND fdu.user_id = gjb.created_by
AND fsv1.seq_version_id(+) = gjh.posting_acct_seq_version_id
AND fsv2.seq_version_id(+) = gjh.close_acct_seq_version_id
AND gjct.je_category_name = gjh.je_category
AND gjct.LANGUAGE = USERENV('LANG')
AND gjst.je_source_name = gjh.je_source
AND gjst.language = USERENV('LANG')
AND gdct.conversion_type(+) = gjh.currency_conversion_type
&p_gl_other_filter
UNION ALL
SELECT /*+ leading (glbgt gjl gjh gjb) */
to_char(gjh.default_effective_date
,'YYYY-MM-DD') GL_DATE
,fdu.user_name CREATED_BY
,to_char(gjh.creation_date
,'YYYY-MM-DD"T"hh:mi:ss') CREATION_DATE
,to_char(gjh.last_update_date
,'YYYY-MM-DD') LAST_UPDATE_DATE
,NULL GL_TRANSFER_DATE
,to_char(gjh.reference_date
,'YYYY-MM-DD') REFERENCE_DATE
,NULL COMPLETED_DATE
,NULL TRANSACTION_NUMBER
,NULL TRANSACTION_DATE
,fsv1.header_name ACCOUNTING_SEQUENCE_NAME
,fsv1.version_name ACCOUNTING_SEQUENCE_VERSION
,gjh.posting_acct_seq_value ACCOUNTING_SEQUENCE_NUMBER
,fsv2.header_name REPORTING_SEQUENCE_NAME
,fsv2.version_name REPORTING_SEQUENCE_VERSION
,gjh.close_acct_seq_value REPORTING_SEQUENCE_NUMBER
,NULL DOCUMENT_CATEGORY
,NULL DOCUMENT_SEQUENCE_NAME
,NULL DOCUMENT_SEQUENCE_NUMBER
,NULL APPLICATION_ID
,NULL APPLICATION_NAME
,gjh.je_header_id HEADER_ID
,gjh.description HEADER_DESCRIPTION
,NULL FUND_STATUS
,gjct.user_je_category_name JE_CATEGORY_NAME
,gjst.user_je_source_name JE_SOURCE_NAME
,NULL EVENT_ID
,NULL EVENT_DATE
,NULL EVENT_NUMBER
,NULL EVENT_CLASS_CODE
,NULL EVENT_CLASS_NAME
,NULL EVENT_TYPE_CODE
,NULL EVENT_TYPE_NAME
,gjb.NAME GL_BATCH_NAME
,to_char(gjb.posted_date
,'YYYY-MM-DD') POSTED_DATE
,gjh.NAME GL_JE_NAME
,gjh.external_reference EXTERNAL_REFERENCE
,gjl.je_line_num GL_LINE_NUMBER
,gjl.je_line_num LINE_NUMBER
,gjl.je_line_num ORIG_LINE_NUMBER
,NULL ACCOUNTING_CLASS_CODE
,NULL ACCOUNTING_CLASS_NAME
,gjl.description LINE_DESCRIPTION
,gjh.currency_code ENTERED_CURRENCY
,gjh.currency_conversion_rate CONVERSION_RATE
,to_char(gjh.currency_conversion_date
,'YYYY-MM-DD') CONVERSION_RATE_DATE
,gjh.currency_conversion_type CONVERSION_RATE_TYPE_CODE
,gdct.user_conversion_type CONVERSION_RATE_TYPE
,gjl.entered_dr ENTERED_DR
,gjl.entered_cr ENTERED_CR
,NULL UNROUNDED_ACCOUNTED_DR
,NULL UNROUNDED_ACCOUNTED_CR
,gjl.accounted_dr ACCOUNTED_DR
,gjl.accounted_cr ACCOUNTED_CR
,gjl.stat_amount STATISTICAL_AMOUNT
,gjl.jgzz_recon_ref_11i RECONCILIATION_REFERENCE
,gjl.context ATTRIBUTE_CATEGORY
,gjl.attribute1 ATTRIBUTE1
,gjl.attribute2 ATTRIBUTE2
,gjl.attribute3 ATTRIBUTE3
,gjl.attribute4 ATTRIBUTE4
,gjl.attribute5 ATTRIBUTE5
,gjl.attribute6 ATTRIBUTE6
,gjl.attribute7 ATTRIBUTE7
,gjl.attribute8 ATTRIBUTE8
,gjl.attribute9 ATTRIBUTE9
,gjl.attribute10 ATTRIBUTE10
,NULL PARTY_TYPE_CODE
,NULL PARTY_TYPE
,NULL PARTY_INFO
,NULL USERIDS
,glbgt.ledger_id LEDGER_ID
,glbgt.ledger_short_name LEDGER_SHORT_NAME
,glbgt.ledger_description LEDGER_DESCRIPTION
,glbgt.ledger_name LEDGER_NAME
,glbgt.ledger_currency LEDGER_CURRENCY
,glbgt.period_year PERIOD_YEAR
,glbgt.period_number PERIOD_NUMBER
,glbgt.period_name PERIOD_NAME
,to_char(glbgt.period_start_date
,'YYYY-MM-DD') PERIOD_START_DATE
,to_char(glbgt.period_end_date
,'YYYY-MM-DD') PERIOD_END_DATE
,glbgt.balance_type_code BALANCE_TYPE_CODE
,glbgt.balance_type BALANCE_TYPE
,glbgt.budget_name BUDGET_NAME
,glbgt.encumbrance_type ENCUMBRANCE_TYPE
,glbgt.begin_balance_dr BEGIN_BALANCE_DR
,glbgt.begin_balance_cr BEGIN_BALANCE_CR
,glbgt.period_net_dr PERIOD_NET_DR
,glbgt.period_net_cr PERIOD_NET_CR
,glbgt.code_combination_id CODE_COMBINATION_ID
,glbgt.accounting_code_combination ACCOUNTING_CODE_COMBINATION
,glbgt.code_combination_description CODE_COMBINATION_DESCRIPTION
,glbgt.control_account_flag CONTROL_ACCOUNT_FLAG
,glbgt.control_account CONTROL_ACCOUNT
,glbgt.balancing_segment BALANCING_SEGMENT
,glbgt.natural_account_segment NATURAL_ACCOUNT_SEGMENT
,glbgt.cost_center_segment COST_CENTER_SEGMENT
,glbgt.management_segment MANAGEMENT_SEGMENT
,glbgt.intercompany_segment INTERCOMPANY_SEGMENT
,glbgt.balancing_segment_desc BALANCING_SEGMENT_DESC
,glbgt.natural_account_desc NATURAL_ACCOUNT_DESC
,glbgt.cost_center_desc COST_CENTER_DESC
,glbgt.management_segment_desc MANAGEMENT_SEGMENT_DESC
,glbgt.intercompany_segment_desc INTERCOMPANY_SEGMENT_DESC
,glbgt.segment1 SEGMENT1
,glbgt.segment2 SEGMENT2
,glbgt.segment3 SEGMENT3
,glbgt.segment4 SEGMENT4
,glbgt.segment5 SEGMENT5
,glbgt.segment6 SEGMENT6
,glbgt.segment7 SEGMENT7
,glbgt.segment8 SEGMENT8
,glbgt.segment9 SEGMENT9
,glbgt.segment10 SEGMENT10
,glbgt.segment11 SEGMENT11
,glbgt.segment12 SEGMENT12
,glbgt.segment13 SEGMENT13
,glbgt.segment14 SEGMENT14
,glbgt.segment15 SEGMENT15
,glbgt.segment16 SEGMENT16
,glbgt.segment17 SEGMENT17
,glbgt.segment18 SEGMENT18
,glbgt.segment19 SEGMENT19
,glbgt.segment20 SEGMENT20
,glbgt.segment21 SEGMENT21
,glbgt.segment22 SEGMENT22
,glbgt.segment23 SEGMENT23
,glbgt.segment24 SEGMENT24
,glbgt.segment25 SEGMENT25
,glbgt.segment26 SEGMENT26
,glbgt.segment27 SEGMENT27
,glbgt.segment28 SEGMENT28
,glbgt.segment29 SEGMENT29
,glbgt.segment30 SEGMENT30
,glbgt.begin_running_total_cr BEGIN_RUNNING_TOTAL_CR
,glbgt.begin_running_total_dr BEGIN_RUNNING_TOTAL_DR
,glbgt.end_running_total_cr END_RUNNING_TOTAL_CR
,glbgt.end_running_total_dr END_RUNNING_TOTAL_DR
,glbgt.legal_entity_id LEGAL_ENTITY_ID
,glbgt.legal_entity_name LEGAL_ENTITY_NAME
,glbgt.le_address_line_1 LE_ADDRESS_LINE_1
,glbgt.le_address_line_2 LE_ADDRESS_LINE_2
,glbgt.le_address_line_3 LE_ADDRESS_LINE_3
,glbgt.le_city LE_CITY
,glbgt.le_region_1 LE_REGION_1
,glbgt.le_region_2 LE_REGION_2
,glbgt.le_region_3 LE_REGION_3
,glbgt.le_postal_code LE_POSTAL_CODE
,glbgt.le_country LE_COUNTRY
,glbgt.le_registration_number LE_REGISTRATION_NUMBER
,glbgt.le_registration_effective_from LE_REGISTRATION_EFFECTIVE_FROM
,glbgt.le_br_daily_inscription_number LE_BR_DAILY_INSCRIPTION_NUMBER
,to_char(glbgt.le_br_daily_inscription_date
,'YYYY-MM-DD') LE_BR_DAILY_INSCRIPTION_DATE
,glbgt.le_br_daily_entity LE_BR_DAILY_ENTITY
,glbgt.le_br_daily_location LE_BR_DAILY_LOCATION
,glbgt.le_br_director_number LE_BR_DIRECTOR_NUMBER
,glbgt.le_br_accountant_number LE_BR_ACCOUNTANT_NUMBER
,glbgt.le_br_accountant_name LE_BR_ACCOUNTANT_NAME
FROM applsys.fnd_user fdu
,applsys.fnd_new_messages fnm
,fun.fun_seq_versions fsv1
,fun.fun_seq_versions fsv2
,gl.gl_je_categories_tl gjct
,gl.gl_je_sources_tl gjst
,gl.gl_daily_conversion_types gdct
,gl.gl_je_lines gjl
,gl.gl_je_headers gjh
,gl.gl_je_batches gjb
,xla.xla_report_balances_gt glbgt
WHERE gjl.ledger_id = glbgt.ledger_id
AND gjl.code_combination_id = glbgt.code_combination_id
AND gjl.effective_date BETWEEN glbgt.period_start_date AND glbgt.period_end_date
AND gjl.effective_date BETWEEN :P_GL_DATE_FROM AND :P_GL_DATE_TO
AND gjl.period_name = glbgt.period_name
AND gjh.je_header_id = gjl.je_header_id
AND gjh.actual_flag = glbgt.balance_type_code
AND decode(gjh.currency_code,'STAT',gjh.currency_code,glbgt.ledger_currency) = glbgt.ledger_currency --added bug 6686541
AND NVL(gjh.je_from_sla_flag,'N') = 'U'
AND fnm.application_id = 101
AND fnm.language_code = USERENV('LANG')
AND fnm.message_name in ('PPOS0220', 'PPOS0221', 'PPOS0222', 'PPOS0243', 'PPOS0222_G','PPOSO275')
AND gjl.description= fnm.message_text
AND NVL(gjh.budget_version_id,-19999) = NVL(glbgt.budget_version_id,-19999)
AND NVL(gjh.encumbrance_type_id,-19999) = NVL(glbgt.encumbrance_type_id,-19999)
AND gjb.je_batch_id = gjh.je_batch_id
AND gjb.status = 'P'
AND fdu.user_id = gjb.created_by
AND fsv1.seq_version_id(+) = gjh.posting_acct_seq_version_id
AND fsv2.seq_version_id(+) = gjh.close_acct_seq_version_id
AND gjct.je_category_name = gjh.je_category
AND gjct.LANGUAGE = USERENV('LANG')
AND gjst.je_source_name = gjh.je_source
AND gjst.language = USERENV('LANG')
AND gdct.conversion_type(+) = gjh.currency_conversion_type
AND not exists (select 'x' from gl.gl_import_references gir where
gir.je_header_id=gjl.je_header_id
and gir.je_line_num=gjl.je_line_num)
&p_gl_other_filter
&p_begin_balance_union_all
)TABLE1
)A,
(
SELECT
TABLE1.LEDGER_NAME LEDGER_NAME
,TABLE1.LEDGER_CURRENCY LEDGER_CURRENCY
,TABLE1.BALANCE_TYPE BALANCE_TYPE
,TABLE1.ACCOUNTING_CODE_COMBINATION ACCOUNTING_CODE_COMBINATION
,TABLE1.CODE_COMBINATION_DESCRIPTION CODE_COMBINATION_DESCRIPTION
,MIN(TO_NUMBER(TABLE1.PERIOD_YEAR || lpad(TABLE1.PERIOD_NUMBER,4,'0'))) PERIOD_REF
,SUM(TABLE1.PERIOD_NET_DR) PERIOD_NET_DR
,SUM(TABLE1.PERIOD_NET_CR) PERIOD_NET_CR
FROM
(SELECT /*+ leading (glbgt gjl gjh gir ael aeh) */
to_char(aeh.accounting_date
,'YYYY-MM-DD') GL_DATE
,fdu.user_name CREATED_BY
,to_char(aeh.creation_date
,'YYYY-MM-DD"T"hh:mi:ss') CREATION_DATE
,to_char(aeh.last_update_date
,'YYYY-MM-DD') LAST_UPDATE_DATE
,to_char(aeh.gl_transfer_date
,'YYYY-MM-DD"T"hh:mi:ss') GL_TRANSFER_DATE
,to_char(aeh.reference_date
,'YYYY-MM-DD') REFERENCE_DATE
,to_char(aeh.completed_date
,'YYYY-MM-DD"T"hh:mi:ss') COMPLETED_DATE
,ent.transaction_number TRANSACTION_NUMBER
,to_char(xle.transaction_date
,'YYYY-MM-DD"T"hh:mi:ss') TRANSACTION_DATE
,fsv1.header_name ACCOUNTING_SEQUENCE_NAME
,fsv1.version_name ACCOUNTING_SEQUENCE_VERSION
,aeh.completion_acct_seq_value ACCOUNTING_SEQUENCE_NUMBER
,fsv2.header_name REPORTING_SEQUENCE_NAME
,fsv2.version_name REPORTING_SEQUENCE_VERSION
,aeh.close_acct_seq_value REPORTING_SEQUENCE_NUMBER
,NULL DOCUMENT_CATEGORY
,fns.name DOCUMENT_SEQUENCE_NAME
,aeh.doc_sequence_value DOCUMENT_SEQUENCE_NUMBER
,aeh.application_id APPLICATION_ID
,fap.application_name APPLICATION_NAME
,aeh.ae_header_id HEADER_ID
,aeh.description HEADER_DESCRIPTION
,xlk1.meaning FUND_STATUS
,gjct.user_je_category_name JE_CATEGORY_NAME
,gjst.user_je_source_name JE_SOURCE_NAME
,xle.event_id EVENT_ID
,to_char(xle.event_date
,'YYYY-MM-DD') EVENT_DATE
,xle.event_number EVENT_NUMBER
,xet.event_class_code EVENT_CLASS_CODE
,xect.NAME EVENT_CLASS_NAME
,aeh.event_type_code EVENT_TYPE_CODE
,xet.NAME EVENT_TYPE_NAME
,gjb.NAME GL_BATCH_NAME
,to_char(gjb.posted_date
,'YYYY-MM-DD') POSTED_DATE
,gjh.NAME GL_JE_NAME
,gjh.external_reference EXTERNAL_REFERENCE
,gjl.je_line_num GL_LINE_NUMBER
,ael.displayed_line_number LINE_NUMBER
,ael.ae_line_num ORIG_LINE_NUMBER
,ael.accounting_class_code ACCOUNTING_CLASS_CODE
,xlk2.meaning ACCOUNTING_CLASS_NAME
,ael.description LINE_DESCRIPTION
,ael.currency_code ENTERED_CURRENCY
,ael.currency_conversion_rate CONVERSION_RATE
,to_char(ael.currency_conversion_date
,'YYYY-MM-DD') CONVERSION_RATE_DATE
,ael.currency_conversion_type CONVERSION_RATE_TYPE_CODE
,gdct.user_conversion_type CONVERSION_RATE_TYPE
,ael.entered_dr ENTERED_DR
,ael.entered_cr ENTERED_CR
,ael.unrounded_accounted_dr UNROUNDED_ACCOUNTED_DR
,ael.unrounded_accounted_cr UNROUNDED_ACCOUNTED_CR
,ael.accounted_dr ACCOUNTED_DR
,ael.accounted_cr ACCOUNTED_CR
,ael.statistical_amount STATISTICAL_AMOUNT
,ael.jgzz_recon_ref RECONCILIATION_REFERENCE
,ael.attribute_category ATTRIBUTE_CATEGORY
,ael.attribute1 ATTRIBUTE1
,ael.attribute2 ATTRIBUTE2
,ael.attribute3 ATTRIBUTE3
,ael.attribute4 ATTRIBUTE4
,ael.attribute5 ATTRIBUTE5
,ael.attribute6 ATTRIBUTE6
,ael.attribute7 ATTRIBUTE7
,ael.attribute8 ATTRIBUTE8
,ael.attribute9 ATTRIBUTE9
,ael.attribute10 ATTRIBUTE10
,ael.party_type_code PARTY_TYPE_CODE
,NULL PARTY_TYPE
&p_party_columns PARTY_INFO
&p_trx_identifiers_1
&p_trx_identifiers_2
&p_trx_identifiers_3
&p_trx_identifiers_4
&p_trx_identifiers_5
,glbgt.ledger_id LEDGER_ID
,glbgt.ledger_short_name LEDGER_SHORT_NAME
,glbgt.ledger_description LEDGER_DESCRIPTION
,glbgt.ledger_name LEDGER_NAME
,glbgt.ledger_currency LEDGER_CURRENCY
,glbgt.period_year PERIOD_YEAR
,glbgt.period_number PERIOD_NUMBER
,glbgt.period_name PERIOD_NAME
,to_char(glbgt.period_start_date
,'YYYY-MM-DD') PERIOD_START_DATE
,to_char(glbgt.period_end_date
,'YYYY-MM-DD') PERIOD_END_DATE
,glbgt.balance_type_code BALANCE_TYPE_CODE
,glbgt.balance_type BALANCE_TYPE
,glbgt.budget_name BUDGET_NAME
,glbgt.encumbrance_type ENCUMBRANCE_TYPE
,glbgt.begin_balance_dr BEGIN_BALANCE_DR
,glbgt.begin_balance_cr BEGIN_BALANCE_CR
,glbgt.period_net_dr PERIOD_NET_DR
,glbgt.period_net_cr PERIOD_NET_CR
,glbgt.code_combination_id CODE_COMBINATION_ID
,glbgt.accounting_code_combination ACCOUNTING_CODE_COMBINATION
,glbgt.code_combination_description CODE_COMBINATION_DESCRIPTION
,glbgt.control_account_flag CONTROL_ACCOUNT_FLAG
,glbgt.control_account CONTROL_ACCOUNT
,glbgt.balancing_segment BALANCING_SEGMENT
,glbgt.natural_account_segment NATURAL_ACCOUNT_SEGMENT
,glbgt.cost_center_segment COST_CENTER_SEGMENT
,glbgt.management_segment MANAGEMENT_SEGMENT
,glbgt.intercompany_segment INTERCOMPANY_SEGMENT
,glbgt.balancing_segment_desc BALANCING_SEGMENT_DESC
,glbgt.natural_account_desc NATURAL_ACCOUNT_DESC
,glbgt.cost_center_desc COST_CENTER_DESC
,glbgt.management_segment_desc MANAGEMENT_SEGMENT_DESC
,glbgt.intercompany_segment_desc INTERCOMPANY_SEGMENT_DESC
,glbgt.segment1 SEGMENT1
,glbgt.segment2 SEGMENT2
,glbgt.segment3 SEGMENT3
,glbgt.segment4 SEGMENT4
,glbgt.segment5 SEGMENT5
,glbgt.segment6 SEGMENT6
,glbgt.segment7 SEGMENT7
,glbgt.segment8 SEGMENT8
,glbgt.segment9 SEGMENT9
,glbgt.segment10 SEGMENT10
,glbgt.segment11 SEGMENT11
,glbgt.segment12 SEGMENT12
,glbgt.segment13 SEGMENT13
,glbgt.segment14 SEGMENT14
,glbgt.segment15 SEGMENT15
,glbgt.segment16 SEGMENT16
,glbgt.segment17 SEGMENT17
,glbgt.segment18 SEGMENT18
,glbgt.segment19 SEGMENT19
,glbgt.segment20 SEGMENT20
,glbgt.segment21 SEGMENT21
,glbgt.segment22 SEGMENT22
,glbgt.segment23 SEGMENT23
,glbgt.segment24 SEGMENT24
,glbgt.segment25 SEGMENT25
,glbgt.segment26 SEGMENT26
,glbgt.segment27 SEGMENT27
,glbgt.segment28 SEGMENT28
,glbgt.segment29 SEGMENT29
,glbgt.segment30 SEGMENT30
,glbgt.begin_running_total_cr BEGIN_RUNNING_TOTAL_CR
,glbgt.begin_running_total_dr BEGIN_RUNNING_TOTAL_DR
,glbgt.end_running_total_cr END_RUNNING_TOTAL_CR
,glbgt.end_running_total_dr END_RUNNING_TOTAL_DR
,glbgt.legal_entity_id LEGAL_ENTITY_ID
,glbgt.legal_entity_name LEGAL_ENTITY_NAME
,glbgt.le_address_line_1 LE_ADDRESS_LINE_1
,glbgt.le_address_line_2 LE_ADDRESS_LINE_2
,glbgt.le_address_line_3 LE_ADDRESS_LINE_3
,glbgt.le_city LE_CITY
,glbgt.le_region_1 LE_REGION_1
,glbgt.le_region_2 LE_REGION_2
,glbgt.le_region_3 LE_REGION_3
,glbgt.le_postal_code LE_POSTAL_CODE
,glbgt.le_country LE_COUNTRY
,glbgt.le_registration_number LE_REGISTRATION_NUMBER
,glbgt.le_registration_effective_from LE_REGISTRATION_EFFECTIVE_FROM
,glbgt.le_br_daily_inscription_number LE_BR_DAILY_INSCRIPTION_NUMBER
,to_char(glbgt.le_br_daily_inscription_date
,'YYYY-MM-DD') LE_BR_DAILY_INSCRIPTION_DATE
,glbgt.le_br_daily_entity LE_BR_DAILY_ENTITY
,glbgt.le_br_daily_location LE_BR_DAILY_LOCATION
,glbgt.le_br_director_number LE_BR_DIRECTOR_NUMBER
,glbgt.le_br_accountant_number LE_BR_ACCOUNTANT_NUMBER
,glbgt.le_br_accountant_name LE_BR_ACCOUNTANT_NAME
FROM xla.xla_ae_headers aeh
,xla.xla_ae_lines ael
,apps.xla_lookups xlk1
,apps.xla_lookups xlk2
,xla.xla_events xle
,xla.xla_event_classes_tl xect
,xla.xla_event_types_tl xet
,applsys.fnd_user fdu
,xla.xla_transaction_entities ent
,applsys.fnd_application_tl fap
,fun.fun_seq_versions fsv1
,fun.fun_seq_versions fsv2
,applsys.fnd_document_sequences fns
,gl.gl_je_categories_tl gjct
,gl.gl_je_sources_tl gjst
,gl.gl_daily_conversion_types gdct
,gl.gl_import_references gir
,gl.gl_je_lines gjl
,gl.gl_je_headers gjh
,gl.gl_je_batches gjb
,xla.xla_report_balances_gt glbgt
WHERE &p_ledger_filters
--added as part of 6719948
AND gjl.ledger_id = glbgt.ledger_id
AND gjl.code_combination_id = glbgt.code_combination_id
AND gjl.effective_date BETWEEN glbgt.period_start_date AND glbgt.period_end_date
AND gjl.effective_date BETWEEN :P_GL_DATE_FROM AND :P_GL_DATE_TO
AND gjl.period_name = glbgt.period_name
AND gjl.je_header_id = gjh.je_header_id
AND gjl.period_name = gjh.period_name
AND gjl.je_header_id = gir.je_header_id
AND gjl.je_line_num = gir.je_line_num
AND gjh.je_header_id = gir.je_header_id
AND gjh.status = 'P'
AND NVL(gjh.je_from_sla_flag,'N') IN('Y','U')
AND gjb.je_batch_id = gir.je_batch_id
AND gjb.status = 'P'
AND gir.gl_sl_link_id = ael.gl_sl_link_id
AND gir.gl_sl_link_table = ael.gl_sl_link_table
--AND gjh.currency_code = glbgt.ledger_currency --added bug 6722505
AND gjct.je_category_name = aeh.je_category_name
AND gjct.LANGUAGE = USERENV('LANG')
AND gjst.je_source_name = gjh.je_source
AND gjst.LANGUAGE = USERENV('LANG')
AND aeh.accounting_entry_status_code = 'F'
AND aeh.gl_transfer_status_code = 'Y'
AND aeh.balance_type_code = glbgt.balance_type_code
AND NVL(aeh.budget_version_id,-19999) = NVL(glbgt.budget_version_id,-19999)
AND ael.application_id = aeh.application_id
AND ael.ae_header_id = aeh.ae_header_id
AND NVL(ael.encumbrance_type_id,-19999)= NVL(glbgt.encumbrance_type_id,-19999) -- 4458381
AND xlk1.lookup_type(+) = 'XLA_FUNDS_STATUS'
AND xlk1.lookup_code(+) = aeh.funds_status_code
AND xlk2.lookup_type = 'XLA_ACCOUNTING_CLASS'
AND xlk2.lookup_code = ael.accounting_class_code
AND xle.application_id = aeh.application_id
AND xle.event_id = aeh.event_id
AND xet.application_id = aeh.application_id
AND xet.event_type_code = aeh.event_type_code
AND xet.LANGUAGE = USERENV('LANG')
AND xect.application_id = xet.application_id
AND xect.entity_code = xet.entity_code
AND xect.event_class_code = xet.event_class_code
AND xect.LANGUAGE = USERENV('LANG')
AND ent.application_id = aeh.application_id
AND ent.entity_id = aeh.entity_id
--AND ent.ledger_id = aeh.ledger_id removed for Bug 7557990
AND fdu.user_id = ent.created_by
AND fap.application_id = aeh.application_id
AND fap.LANGUAGE = USERENV('LANG')
AND fsv1.seq_version_id(+) = aeh.completion_acct_seq_version_id
AND fsv2.seq_version_id(+) = aeh.close_acct_seq_version_id
AND fns.application_id(+) = aeh.application_id
AND fns.doc_sequence_id(+) = aeh.doc_sequence_id
AND gdct.conversion_type(+) = ael.currency_conversion_type
&p_sla_other_filter
--removed as part of 6719948
--AND ael.code_combination_id = glbgt.code_combination_id
--AND aeh.ledger_id = glbgt.ledger_id
--AND aeh.accounting_date BETWEEN glbgt.period_start_date AND glbgt.period_end_date
--AND aeh.accounting_date BETWEEN :P_GL_DATE_FROM AND :P_GL_DATE_TO
--AND aeh.period_name = glbgt.period_name
--AND gjh.ledger_id = aeh.ledger_id
UNION ALL
SELECT /*+ leading (glbgt gjl gjh gjb) */
to_char(gjh.default_effective_date
,'YYYY-MM-DD') GL_DATE
,fdu.user_name CREATED_BY
,to_char(gjh.creation_date
,'YYYY-MM-DD"T"hh:mi:ss') CREATION_DATE
,to_char(gjh.last_update_date
,'YYYY-MM-DD') LAST_UPDATE_DATE
,NULL GL_TRANSFER_DATE
,to_char(gjh.reference_date
,'YYYY-MM-DD') REFERENCE_DATE
,NULL COMPLETED_DATE
,NULL TRANSACTION_NUMBER
,NULL TRANSACTION_DATE
,fsv1.header_name ACCOUNTING_SEQUENCE_NAME
,fsv1.version_name ACCOUNTING_SEQUENCE_VERSION
,gjh.posting_acct_seq_value ACCOUNTING_SEQUENCE_NUMBER
,fsv2.header_name REPORTING_SEQUENCE_NAME
,fsv2.version_name REPORTING_SEQUENCE_VERSION
,gjh.close_acct_seq_value REPORTING_SEQUENCE_NUMBER
,NULL DOCUMENT_CATEGORY
,NULL DOCUMENT_SEQUENCE_NAME
,NULL DOCUMENT_SEQUENCE_NUMBER
,NULL APPLICATION_ID
,NULL APPLICATION_NAME
,gjh.je_header_id HEADER_ID
,gjh.description HEADER_DESCRIPTION
,NULL FUND_STATUS
,gjct.user_je_category_name JE_CATEGORY_NAME
,gjst.user_je_source_name JE_SOURCE_NAME
,NULL EVENT_ID
,NULL EVENT_DATE
,NULL EVENT_NUMBER
,NULL EVENT_CLASS_CODE
,NULL EVENT_CLASS_NAME
,NULL EVENT_TYPE_CODE
,NULL EVENT_TYPE_NAME
,gjb.NAME GL_BATCH_NAME
,to_char(gjb.posted_date
,'YYYY-MM-DD') POSTED_DATE
,gjh.NAME GL_JE_NAME
,gjh.external_reference EXTERNAL_REFERENCE
,gjl.je_line_num GL_LINE_NUMBER
,gjl.je_line_num LINE_NUMBER
,gjl.je_line_num ORIG_LINE_NUMBER
,NULL ACCOUNTING_CLASS_CODE
,NULL ACCOUNTING_CLASS_NAME
,gjl.description LINE_DESCRIPTION
,gjh.currency_code ENTERED_CURRENCY
,gjh.currency_conversion_rate CONVERSION_RATE
,to_char(gjh.currency_conversion_date
,'YYYY-MM-DD') CONVERSION_RATE_DATE
,gjh.currency_conversion_type CONVERSION_RATE_TYPE_CODE
,gdct.user_conversion_type CONVERSION_RATE_TYPE
,gjl.entered_dr ENTERED_DR
,gjl.entered_cr ENTERED_CR
,NULL UNROUNDED_ACCOUNTED_DR
,NULL UNROUNDED_ACCOUNTED_CR
,gjl.accounted_dr ACCOUNTED_DR
,gjl.accounted_cr ACCOUNTED_CR
,gjl.stat_amount STATISTICAL_AMOUNT
,gjl.jgzz_recon_ref_11i RECONCILIATION_REFERENCE
,gjl.context ATTRIBUTE_CATEGORY
,gjl.attribute1 ATTRIBUTE1
,gjl.attribute2 ATTRIBUTE2
,gjl.attribute3 ATTRIBUTE3
,gjl.attribute4 ATTRIBUTE4
,gjl.attribute5 ATTRIBUTE5
,gjl.attribute6 ATTRIBUTE6
,gjl.attribute7 ATTRIBUTE7
,gjl.attribute8 ATTRIBUTE8
,gjl.attribute9 ATTRIBUTE9
,gjl.attribute10 ATTRIBUTE10
,NULL PARTY_TYPE_CODE
,NULL PARTY_TYPE
,NULL PARTY_INFO
,NULL USERIDS
,glbgt.ledger_id LEDGER_ID
,glbgt.ledger_short_name LEDGER_SHORT_NAME
,glbgt.ledger_description LEDGER_DESCRIPTION
,glbgt.ledger_name LEDGER_NAME
,glbgt.ledger_currency LEDGER_CURRENCY
,glbgt.period_year PERIOD_YEAR
,glbgt.period_number PERIOD_NUMBER
,glbgt.period_name PERIOD_NAME
,to_char(glbgt.period_start_date
,'YYYY-MM-DD') PERIOD_START_DATE
,to_char(glbgt.period_end_date
,'YYYY-MM-DD') PERIOD_END_DATE
,glbgt.balance_type_code BALANCE_TYPE_CODE
,glbgt.balance_type BALANCE_TYPE
,glbgt.budget_name BUDGET_NAME
,glbgt.encumbrance_type ENCUMBRANCE_TYPE
,glbgt.begin_balance_dr BEGIN_BALANCE_DR
,glbgt.begin_balance_cr BEGIN_BALANCE_CR
,glbgt.period_net_dr PERIOD_NET_DR
,glbgt.period_net_cr PERIOD_NET_CR
,glbgt.code_combination_id CODE_COMBINATION_ID
,glbgt.accounting_code_combination ACCOUNTING_CODE_COMBINATION
,glbgt.code_combination_description CODE_COMBINATION_DESCRIPTION
,glbgt.control_account_flag CONTROL_ACCOUNT_FLAG
,glbgt.control_account CONTROL_ACCOUNT
,glbgt.balancing_segment BALANCING_SEGMENT
,glbgt.natural_account_segment NATURAL_ACCOUNT_SEGMENT
,glbgt.cost_center_segment COST_CENTER_SEGMENT
,glbgt.management_segment MANAGEMENT_SEGMENT
,glbgt.intercompany_segment INTERCOMPANY_SEGMENT
,glbgt.balancing_segment_desc BALANCING_SEGMENT_DESC
,glbgt.natural_account_desc NATURAL_ACCOUNT_DESC
,glbgt.cost_center_desc COST_CENTER_DESC
,glbgt.management_segment_desc MANAGEMENT_SEGMENT_DESC
,glbgt.intercompany_segment_desc INTERCOMPANY_SEGMENT_DESC
,glbgt.segment1 SEGMENT1
,glbgt.segment2 SEGMENT2
,glbgt.segment3 SEGMENT3
,glbgt.segment4 SEGMENT4
,glbgt.segment5 SEGMENT5
,glbgt.segment6 SEGMENT6
,glbgt.segment7 SEGMENT7
,glbgt.segment8 SEGMENT8
,glbgt.segment9 SEGMENT9
,glbgt.segment10 SEGMENT10
,glbgt.segment11 SEGMENT11
,glbgt.segment12 SEGMENT12
,glbgt.segment13 SEGMENT13
,glbgt.segment14 SEGMENT14
,glbgt.segment15 SEGMENT15
,glbgt.segment16 SEGMENT16
,glbgt.segment17 SEGMENT17
,glbgt.segment18 SEGMENT18
,glbgt.segment19 SEGMENT19
,glbgt.segment20 SEGMENT20
,glbgt.segment21 SEGMENT21
,glbgt.segment22 SEGMENT22
,glbgt.segment23 SEGMENT23
,glbgt.segment24 SEGMENT24
,glbgt.segment25 SEGMENT25
,glbgt.segment26 SEGMENT26
,glbgt.segment27 SEGMENT27
,glbgt.segment28 SEGMENT28
,glbgt.segment29 SEGMENT29
,glbgt.segment30 SEGMENT30
,glbgt.begin_running_total_cr BEGIN_RUNNING_TOTAL_CR
,glbgt.begin_running_total_dr BEGIN_RUNNING_TOTAL_DR
,glbgt.end_running_total_cr END_RUNNING_TOTAL_CR
,glbgt.end_running_total_dr END_RUNNING_TOTAL_DR
,glbgt.legal_entity_id LEGAL_ENTITY_ID
,glbgt.legal_entity_name LEGAL_ENTITY_NAME
,glbgt.le_address_line_1 LE_ADDRESS_LINE_1
,glbgt.le_address_line_2 LE_ADDRESS_LINE_2
,glbgt.le_address_line_3 LE_ADDRESS_LINE_3
,glbgt.le_city LE_CITY
,glbgt.le_region_1 LE_REGION_1
,glbgt.le_region_2 LE_REGION_2
,glbgt.le_region_3 LE_REGION_3
,glbgt.le_postal_code LE_POSTAL_CODE
,glbgt.le_country LE_COUNTRY
,glbgt.le_registration_number LE_REGISTRATION_NUMBER
,glbgt.le_registration_effective_from LE_REGISTRATION_EFFECTIVE_FROM
,glbgt.le_br_daily_inscription_number LE_BR_DAILY_INSCRIPTION_NUMBER
,to_char(glbgt.le_br_daily_inscription_date
,'YYYY-MM-DD') LE_BR_DAILY_INSCRIPTION_DATE
,glbgt.le_br_daily_entity LE_BR_DAILY_ENTITY
,glbgt.le_br_daily_location LE_BR_DAILY_LOCATION
,glbgt.le_br_director_number LE_BR_DIRECTOR_NUMBER
,glbgt.le_br_accountant_number LE_BR_ACCOUNTANT_NUMBER
,glbgt.le_br_accountant_name LE_BR_ACCOUNTANT_NAME
FROM applsys.fnd_user fdu
,fun.fun_seq_versions fsv1
,fun.fun_seq_versions fsv2
,gl.gl_je_categories_tl gjct
,gl.gl_je_sources_tl gjst
,gl.gl_daily_conversion_types gdct
,gl.gl_je_lines gjl
,gl.gl_je_headers gjh
,gl.gl_je_batches gjb
,xla.xla_report_balances_gt glbgt
WHERE gjl.ledger_id = glbgt.ledger_id
AND gjl.code_combination_id = glbgt.code_combination_id
AND gjl.effective_date BETWEEN glbgt.period_start_date AND glbgt.period_end_date
AND gjl.effective_date BETWEEN :P_GL_DATE_FROM AND :P_GL_DATE_TO
AND gjl.period_name = glbgt.period_name
AND gjh.je_header_id = gjl.je_header_id
AND gjh.actual_flag = glbgt.balance_type_code
AND decode(gjh.currency_code,'STAT',gjh.currency_code,glbgt.ledger_currency) = glbgt.ledger_currency --added bug 6686541
AND NVL(gjh.je_from_sla_flag,'N') = 'N'
AND NVL(gjh.budget_version_id,-19999)= NVL(glbgt.budget_version_id,-19999)
AND NVL(gjh.encumbrance_type_id,-19999)= NVL(glbgt.encumbrance_type_id,-19999)
AND gjb.je_batch_id = gjh.je_batch_id
AND gjb.status = 'P'
AND fdu.user_id = gjb.created_by
AND fsv1.seq_version_id(+) = gjh.posting_acct_seq_version_id
AND fsv2.seq_version_id(+) = gjh.close_acct_seq_version_id
AND gjct.je_category_name = gjh.je_category
AND gjct.LANGUAGE = USERENV('LANG')
AND gjst.je_source_name = gjh.je_source
AND gjst.language = USERENV('LANG')
AND gdct.conversion_type(+) = gjh.currency_conversion_type
&p_gl_other_filter
UNION ALL
SELECT /*+ leading (glbgt gjl gjh gjb) */
to_char(gjh.default_effective_date
,'YYYY-MM-DD') GL_DATE
,fdu.user_name CREATED_BY
,to_char(gjh.creation_date
,'YYYY-MM-DD"T"hh:mi:ss') CREATION_DATE
,to_char(gjh.last_update_date
,'YYYY-MM-DD') LAST_UPDATE_DATE
,NULL GL_TRANSFER_DATE
,to_char(gjh.reference_date
,'YYYY-MM-DD') REFERENCE_DATE
,NULL COMPLETED_DATE
,NULL TRANSACTION_NUMBER
,NULL TRANSACTION_DATE
,fsv1.header_name ACCOUNTING_SEQUENCE_NAME
,fsv1.version_name ACCOUNTING_SEQUENCE_VERSION
,gjh.posting_acct_seq_value ACCOUNTING_SEQUENCE_NUMBER
,fsv2.header_name REPORTING_SEQUENCE_NAME
,fsv2.version_name REPORTING_SEQUENCE_VERSION
,gjh.close_acct_seq_value REPORTING_SEQUENCE_NUMBER
,NULL DOCUMENT_CATEGORY
,NULL DOCUMENT_SEQUENCE_NAME
,NULL DOCUMENT_SEQUENCE_NUMBER
,NULL APPLICATION_ID
,NULL APPLICATION_NAME
,gjh.je_header_id HEADER_ID
,gjh.description HEADER_DESCRIPTION
,NULL FUND_STATUS
,gjct.user_je_category_name JE_CATEGORY_NAME
,gjst.user_je_source_name JE_SOURCE_NAME
,NULL EVENT_ID
,NULL EVENT_DATE
,NULL EVENT_NUMBER
,NULL EVENT_CLASS_CODE
,NULL EVENT_CLASS_NAME
,NULL EVENT_TYPE_CODE
,NULL EVENT_TYPE_NAME
,gjb.NAME GL_BATCH_NAME
,to_char(gjb.posted_date
,'YYYY-MM-DD') POSTED_DATE
,gjh.NAME GL_JE_NAME
,gjh.external_reference EXTERNAL_REFERENCE
,gjl.je_line_num GL_LINE_NUMBER
,gjl.je_line_num LINE_NUMBER
,gjl.je_line_num ORIG_LINE_NUMBER
,NULL ACCOUNTING_CLASS_CODE
,NULL ACCOUNTING_CLASS_NAME
,gjl.description LINE_DESCRIPTION
,gjh.currency_code ENTERED_CURRENCY
,gjh.currency_conversion_rate CONVERSION_RATE
,to_char(gjh.currency_conversion_date
,'YYYY-MM-DD') CONVERSION_RATE_DATE
,gjh.currency_conversion_type CONVERSION_RATE_TYPE_CODE
,gdct.user_conversion_type CONVERSION_RATE_TYPE
,gjl.entered_dr ENTERED_DR
,gjl.entered_cr ENTERED_CR
,NULL UNROUNDED_ACCOUNTED_DR
,NULL UNROUNDED_ACCOUNTED_CR
,gjl.accounted_dr ACCOUNTED_DR
,gjl.accounted_cr ACCOUNTED_CR
,gjl.stat_amount STATISTICAL_AMOUNT
,gjl.jgzz_recon_ref_11i RECONCILIATION_REFERENCE
,gjl.context ATTRIBUTE_CATEGORY
,gjl.attribute1 ATTRIBUTE1
,gjl.attribute2 ATTRIBUTE2
,gjl.attribute3 ATTRIBUTE3
,gjl.attribute4 ATTRIBUTE4
,gjl.attribute5 ATTRIBUTE5
,gjl.attribute6 ATTRIBUTE6
,gjl.attribute7 ATTRIBUTE7
,gjl.attribute8 ATTRIBUTE8
,gjl.attribute9 ATTRIBUTE9
,gjl.attribute10 ATTRIBUTE10
,NULL PARTY_TYPE_CODE
,NULL PARTY_TYPE
,NULL PARTY_INFO
,NULL USERIDS
,glbgt.ledger_id LEDGER_ID
,glbgt.ledger_short_name LEDGER_SHORT_NAME
,glbgt.ledger_description LEDGER_DESCRIPTION
,glbgt.ledger_name LEDGER_NAME
,glbgt.ledger_currency LEDGER_CURRENCY
,glbgt.period_year PERIOD_YEAR
,glbgt.period_number PERIOD_NUMBER
,glbgt.period_name PERIOD_NAME
,to_char(glbgt.period_start_date
,'YYYY-MM-DD') PERIOD_START_DATE
,to_char(glbgt.period_end_date
,'YYYY-MM-DD') PERIOD_END_DATE
,glbgt.balance_type_code BALANCE_TYPE_CODE
,glbgt.balance_type BALANCE_TYPE
,glbgt.budget_name BUDGET_NAME
,glbgt.encumbrance_type ENCUMBRANCE_TYPE
,glbgt.begin_balance_dr BEGIN_BALANCE_DR
,glbgt.begin_balance_cr BEGIN_BALANCE_CR
,glbgt.period_net_dr PERIOD_NET_DR
,glbgt.period_net_cr PERIOD_NET_CR
,glbgt.code_combination_id CODE_COMBINATION_ID
,glbgt.accounting_code_combination ACCOUNTING_CODE_COMBINATION
,glbgt.code_combination_description CODE_COMBINATION_DESCRIPTION
,glbgt.control_account_flag CONTROL_ACCOUNT_FLAG
,glbgt.control_account CONTROL_ACCOUNT
,glbgt.balancing_segment BALANCING_SEGMENT
,glbgt.natural_account_segment NATURAL_ACCOUNT_SEGMENT
,glbgt.cost_center_segment COST_CENTER_SEGMENT
,glbgt.management_segment MANAGEMENT_SEGMENT
,glbgt.intercompany_segment INTERCOMPANY_SEGMENT
,glbgt.balancing_segment_desc BALANCING_SEGMENT_DESC
,glbgt.natural_account_desc NATURAL_ACCOUNT_DESC
,glbgt.cost_center_desc COST_CENTER_DESC
,glbgt.management_segment_desc MANAGEMENT_SEGMENT_DESC
,glbgt.intercompany_segment_desc INTERCOMPANY_SEGMENT_DESC
,glbgt.segment1 SEGMENT1
,glbgt.segment2 SEGMENT2
,glbgt.segment3 SEGMENT3
,glbgt.segment4 SEGMENT4
,glbgt.segment5 SEGMENT5
,glbgt.segment6 SEGMENT6
,glbgt.segment7 SEGMENT7
,glbgt.segment8 SEGMENT8
,glbgt.segment9 SEGMENT9
,glbgt.segment10 SEGMENT10
,glbgt.segment11 SEGMENT11
,glbgt.segment12 SEGMENT12
,glbgt.segment13 SEGMENT13
,glbgt.segment14 SEGMENT14
,glbgt.segment15 SEGMENT15
,glbgt.segment16 SEGMENT16
,glbgt.segment17 SEGMENT17
,glbgt.segment18 SEGMENT18
,glbgt.segment19 SEGMENT19
,glbgt.segment20 SEGMENT20
,glbgt.segment21 SEGMENT21
,glbgt.segment22 SEGMENT22
,glbgt.segment23 SEGMENT23
,glbgt.segment24 SEGMENT24
,glbgt.segment25 SEGMENT25
,glbgt.segment26 SEGMENT26
,glbgt.segment27 SEGMENT27
,glbgt.segment28 SEGMENT28
,glbgt.segment29 SEGMENT29
,glbgt.segment30 SEGMENT30
,glbgt.begin_running_total_cr BEGIN_RUNNING_TOTAL_CR
,glbgt.begin_running_total_dr BEGIN_RUNNING_TOTAL_DR
,glbgt.end_running_total_cr END_RUNNING_TOTAL_CR
,glbgt.end_running_total_dr END_RUNNING_TOTAL_DR
,glbgt.legal_entity_id LEGAL_ENTITY_ID
,glbgt.legal_entity_name LEGAL_ENTITY_NAME
,glbgt.le_address_line_1 LE_ADDRESS_LINE_1
,glbgt.le_address_line_2 LE_ADDRESS_LINE_2
,glbgt.le_address_line_3 LE_ADDRESS_LINE_3
,glbgt.le_city LE_CITY
,glbgt.le_region_1 LE_REGION_1
,glbgt.le_region_2 LE_REGION_2
,glbgt.le_region_3 LE_REGION_3
,glbgt.le_postal_code LE_POSTAL_CODE
,glbgt.le_country LE_COUNTRY
,glbgt.le_registration_number LE_REGISTRATION_NUMBER
,glbgt.le_registration_effective_from LE_REGISTRATION_EFFECTIVE_FROM
,glbgt.le_br_daily_inscription_number LE_BR_DAILY_INSCRIPTION_NUMBER
,to_char(glbgt.le_br_daily_inscription_date
,'YYYY-MM-DD') LE_BR_DAILY_INSCRIPTION_DATE
,glbgt.le_br_daily_entity LE_BR_DAILY_ENTITY
,glbgt.le_br_daily_location LE_BR_DAILY_LOCATION
,glbgt.le_br_director_number LE_BR_DIRECTOR_NUMBER
,glbgt.le_br_accountant_number LE_BR_ACCOUNTANT_NUMBER
,glbgt.le_br_accountant_name LE_BR_ACCOUNTANT_NAME
FROM applsys.fnd_user fdu
,applsys.fnd_new_messages fnm
,fun.fun_seq_versions fsv1
,fun.fun_seq_versions fsv2
,gl.gl_je_categories_tl gjct
,gl.gl_je_sources_tl gjst
,gl.gl_daily_conversion_types gdct
,gl.gl_je_lines gjl
,gl.gl_je_headers gjh
,gl.gl_je_batches gjb
,xla.xla_report_balances_gt glbgt
WHERE gjl.ledger_id = glbgt.ledger_id
AND gjl.code_combination_id = glbgt.code_combination_id
AND gjl.effective_date BETWEEN glbgt.period_start_date AND glbgt.period_end_date
AND gjl.effective_date BETWEEN :P_GL_DATE_FROM AND :P_GL_DATE_TO
AND gjl.period_name = glbgt.period_name
AND gjh.je_header_id = gjl.je_header_id
AND gjh.actual_flag = glbgt.balance_type_code
AND decode(gjh.currency_code,'STAT',gjh.currency_code,glbgt.ledger_currency) = glbgt.ledger_currency --added bug 6686541
AND NVL(gjh.je_from_sla_flag,'N') = 'U'
AND fnm.application_id = 101
AND fnm.language_code = USERENV('LANG')
AND fnm.message_name in ('PPOS0220', 'PPOS0221', 'PPOS0222', 'PPOS0243', 'PPOS0222_G','PPOSO275')
AND gjl.description= fnm.message_text
AND NVL(gjh.budget_version_id,-19999) = NVL(glbgt.budget_version_id,-19999)
AND NVL(gjh.encumbrance_type_id,-19999) = NVL(glbgt.encumbrance_type_id,-19999)
AND gjb.je_batch_id = gjh.je_batch_id
AND gjb.status = 'P'
AND fdu.user_id = gjb.created_by
AND fsv1.seq_version_id(+) = gjh.posting_acct_seq_version_id
AND fsv2.seq_version_id(+) = gjh.close_acct_seq_version_id
AND gjct.je_category_name = gjh.je_category
AND gjct.LANGUAGE = USERENV('LANG')
AND gjst.je_source_name = gjh.je_source
AND gjst.language = USERENV('LANG')
AND gdct.conversion_type(+) = gjh.currency_conversion_type
AND not exists (select 'x' from gl.gl_import_references gir where
gir.je_header_id=gjl.je_header_id
and gir.je_line_num=gjl.je_line_num)
&p_gl_other_filter
&p_begin_balance_union_all
)TABLE1
GROUP BY
TABLE1.LEDGER_NAME
,TABLE1.LEDGER_CURRENCY
,TABLE1.BALANCE_TYPE
,TABLE1.ACCOUNTING_CODE_COMBINATION
,TABLE1.CODE_COMBINATION_DESCRIPTION
)B
WHERE ( B.LEDGER_NAME = A.LEDGER_NAME AND
B.LEDGER_CURRENCY = A.LEDGER_CURRENCY AND
B.BALANCE_TYPE = A.BALANCE_TYPE AND
B.ACCOUNTING_CODE_COMBINATION = A.ACCOUNTING_CODE_COMBINATION AND
B.CODE_COMBINATION_DESCRIPTION = A.CODE_COMBINATION_DESCRIPTION )
AND TO_NUMBER(A.PERIOD_YEAR || lpad(A.PERIOD_NUMBER,4,'0')) = B.PERIOD_REF
]]>
</sqlStatement>
<sqlStatement name="Q_TAX">
<![CDATA[
SELECT /*+ index(xdl, xla.xla_distribution_links_N3) */
zxr.tax_regime_name TAX_REGIME
,zxl.tax TAX
,ztt.tax_full_name TAX_NAME
,zst.tax_status_name TAX_STATUS_NAME
,zrt.tax_rate_name TAX_RATE_NAME
,zxl.tax_rate TAX_RATE
,flk1.meaning TAX_RATE_TYPE_NAME
,to_char(zxl.tax_determine_date
,'YYYY-MM-DD') TAX_DETERMINE_DATE
,to_char(zxl.tax_point_date
,'YYYY-MM-DD') TAX_POINT_DATE
,zxl.tax_type_code TAX_TYPE_CODE
,flk2.meaning TAX_TYPE_NAME
,zxl.tax_code TAX_CODE
,zxl.tax_registration_number TAX_REGISTRATION_NUMBER
,zxl.trx_currency_code TRX_CURRENCY_CODE
,zxl.tax_currency_code TAX_CURRENCY_CODE
,zxl.tax_amt TAX_AMOUNT
,zxl.tax_amt_tax_curr TAX_AMOUNT_TAX_CURRENCY
,zxl.tax_amt_funcl_curr TAX_AMOUNT_FUNCTIONAL_CURR
,zxl.taxable_amt TAXABLE_AMOUNT
,zxl.taxable_amt_tax_curr TAXABLE_AMOUNT_TAX_CURRENCY
,zxl.taxable_amt_funcl_curr TAXABLE_AMT_FUNC_CURRENCY
,zxl.unrounded_taxable_amt UNROUNDED_TAXABLE_AMOUNT
,zxl.unrounded_tax_amt UNROUNDED_TAX_AMOUNT
,zxl.rec_tax_amt RECOVERABLE_TAX_AMOUNT
,zxl.rec_tax_amt_tax_curr RECOVERABLE_TAX_AMT_TAX_CURR
,zxl.rec_tax_amt_funcl_curr RECOVERABLE_TAX_AMT_FUNC_CURR
,zxl.nrec_tax_amt NON_RECOVERABLE_TAX_AMOUNT
,zxl.nrec_tax_amt_tax_curr NON_REC_TAX_AMT_TAX_CURR
,zxl.nrec_tax_amt_funcl_curr NON_REC_TAX_AMT_FUNC_CURR
FROM xla.xla_distribution_links xdl
,zx.zx_lines zxl
,zx.zx_regimes_tl zxr
,zx.zx_taxes_tl ztt
,zx.zx_status_tl zst
,zx.zx_rates_tl zrt
,apps.fnd_lookups flk1
,apps.fnd_lookups flk2
WHERE xdl.tax_line_ref_id = zxl.tax_line_id
AND zxr.tax_regime_id(+) = zxl.tax_regime_id
AND zxr.language(+) = USERENV('LANG')
AND ztt.tax_id(+) = zxl.tax_id
AND ztt.language(+) = USERENV('LANG')
AND zst.tax_status_id(+) = zxl.tax_status_id
AND zst.language(+) = USERENV('LANG')
AND zrt.tax_rate_id(+) = zxl.tax_rate_id
AND zrt.language(+) = USERENV('LANG')
AND flk1.lookup_type = 'ZX_RATE_TYPE'
AND flk1.lookup_code = zxl.tax_rate_type
AND flk2.lookup_type(+) = 'ZX_TAX_TYPE_CATEGORY'
AND flk2.lookup_code(+) = zxl.tax_type_code
AND xdl.application_id = :APPLICATION_ID
AND xdl.ae_header_id = :HEADER_ID
AND xdl.ae_line_num = :ORIG_LINE_NUMBER
]]>
</sqlStatement>
<sqlStatement name="Q_COMMERCIAL">
<![CDATA[ &p_commercial_query ]]>
</sqlStatement>
<sqlStatement name="Q_VAT_REGISTRATION">
<![CDATA[ &p_vat_registration_query ]]>
</sqlStatement>
</dataQuery>
<dataTrigger name="beforeReport" source="XLA_ACCT_ANALYSIS_RPT_PKG.beforeReport"/>
<dataStructure>
<group name="BALANCE_CCID" source = "Q_JELINES">
<element name="LEDGER_ID" value="LEDGER_ID"/>
<element name="LEDGER_SHORT_NAME" value="LEDGER_SHORT_NAME"/>
<element name="LEDGER_DESCRIPTION" value="LEDGER_DESCRIPTION"/>
<element name="LEDGER_NAME" value="LEDGER_NAME"/>
<element name="LEDGER_CURRENCY" value="LEDGER_CURRENCY"/>
<element name="BALANCE_TYPE_CODE" value="BALANCE_TYPE_CODE"/>
<element name="BALANCE_TYPE" value="BALANCE_TYPE"/>
<element name="BUDGET_NAME" value="BUDGET_NAME"/>
<element name="ENCUMBRANCE_TYPE" value="ENCUMBRANCE_TYPE"/>
<element name="ACCOUNTING_CODE_COMBINATION" value="ACCOUNTING_CODE_COMBINATION"/>
<element name="CODE_COMBINATION_DESCRIPTION" value="CODE_COMBINATION_DESCRIPTION"/>
<element name="PERIOD_NAME" value="PERIOD_NAME"/>
<element name="BEGIN_BALANCE_DR" value="BEGIN_BALANCE_DR"/>
<element name="BEGIN_BALANCE_CR" value="BEGIN_BALANCE_CR"/>
<element name="LEDGER_SUM_PR_DR" value="ACCT_SUM_PR_DR" function="SUM()"/>
<element name="LEDGER_SUM_PR_CR" value="ACCT_SUM_PR_CR" function="SUM()"/>
<element name="LEDGER_SUM_BAL_DR" value="ACCT_SUM_BAL_DR" function="SUM()"/>
<element name="LEDGER_SUM_BAL_CR" value="ACCT_SUM_BAL_CR" function="SUM()"/>
<group name="CCID_S" source = "Q_JELINES">
<element name="CODE_COMBINATION_ID" value="CODE_COMBINATION_ID"/>
<element name="ACCT_SUM_PR_DR" value="PERIOD_NET_DR" function="SUM()"/>
<element name="ACCT_SUM_PR_CR" value="PERIOD_NET_CR" function="SUM()"/>
<element name="ACCT_SUM_BAL_DR" value="BEGIN_BALANCE_DR" function="SUM()"/>
<element name="ACCT_SUM_BAL_CR" value="BEGIN_BALANCE_CR" function="SUM()"/>
<group name="PERIOD_S" source = "Q_JELINES">
<element name="PERIOD_YEAR" value="PERIOD_YEAR"/>
<element name="PERIOD_NUMBER" value="PERIOD_NUMBER"/>
<element name="PERIOD_START_DATE" value="PERIOD_START_DATE"/>
<element name="PERIOD_END_DATE" value="PERIOD_END_DATE"/>
<element name="PERIOD_NET_DR" value="PERIOD_NET_DR"/>
<element name="PERIOD_NET_CR" value="PERIOD_NET_CR"/>
<element name="CONTROL_ACCOUNT_FLAG" value="CONTROL_ACCOUNT_FLAG"/>
<element name="CONTROL_ACCOUNT" value="CONTROL_ACCOUNT"/>
<element name="BALANCING_SEGMENT" value="BALANCING_SEGMENT"/>
<element name="NATURAL_ACCOUNT_SEGMENT" value="NATURAL_ACCOUNT_SEGMENT"/>
<element name="COST_CENTER_SEGMENT" value="COST_CENTER_SEGMENT"/>
<element name="MANAGEMENT_SEGMENT" value="MANAGEMENT_SEGMENT"/>
<element name="INTERCOMPANY_SEGMENT" value="INTERCOMPANY_SEGMENT"/>
<element name="BALANCING_SEGMENT_DESC" value="BALANCING_SEGMENT_DESC"/>
<element name="NATURAL_ACCOUNT_DESC" value="NATURAL_ACCOUNT_DESC"/>
<element name="COST_CENTER_DESC" value="COST_CENTER_DESC"/>
<element name="MANAGEMENT_SEGMENT_DESC" value="MANAGEMENT_SEGMENT_DESC"/>
<element name="INTERCOMPANY_SEGMENT_DESC" value="INTERCOMPANY_SEGMENT_DESC"/>
<element name="SEGMENT1" value="SEGMENT1"/>
<element name="SEGMENT2" value="SEGMENT2"/>
<element name="SEGMENT3" value="SEGMENT3"/>
<element name="SEGMENT4" value="SEGMENT4"/>
<element name="SEGMENT5" value="SEGMENT5"/>
<element name="SEGMENT6" value="SEGMENT6"/>
<element name="SEGMENT7" value="SEGMENT7"/>
<element name="SEGMENT8" value="SEGMENT8"/>
<element name="SEGMENT9" value="SEGMENT9"/>
<element name="SEGMENT10" value="SEGMENT10"/>
<element name="SEGMENT11" value="SEGMENT11"/>
<element name="SEGMENT12" value="SEGMENT12"/>
<element name="SEGMENT13" value="SEGMENT13"/>
<element name="SEGMENT14" value="SEGMENT14"/>
<element name="SEGMENT15" value="SEGMENT15"/>
<element name="SEGMENT16" value="SEGMENT16"/>
<element name="SEGMENT17" value="SEGMENT17"/>
<element name="SEGMENT18" value="SEGMENT18"/>
<element name="SEGMENT19" value="SEGMENT19"/>
<element name="SEGMENT20" value="SEGMENT20"/>
<element name="SEGMENT21" value="SEGMENT21"/>
<element name="SEGMENT22" value="SEGMENT22"/>
<element name="SEGMENT23" value="SEGMENT23"/>
<element name="SEGMENT24" value="SEGMENT24"/>
<element name="SEGMENT25" value="SEGMENT25"/>
<element name="SEGMENT26" value="SEGMENT26"/>
<element name="SEGMENT27" value="SEGMENT27"/>
<element name="SEGMENT28" value="SEGMENT28"/>
<element name="SEGMENT29" value="SEGMENT29"/>
<element name="SEGMENT30" value="SEGMENT30"/>
<element name="BEGIN_RUNNING_TOTAL_CR" value="BEGIN_RUNNING_TOTAL_CR"/>
<element name="BEGIN_RUNNING_TOTAL_DR" value="BEGIN_RUNNING_TOTAL_DR"/>
<element name="END_RUNNING_TOTAL_CR" value="END_RUNNING_TOTAL_CR"/>
<element name="END_RUNNING_TOTAL_DR" value="END_RUNNING_TOTAL_DR"/>
<element name="LEGAL_ENTITY_ID" value="LEGAL_ENTITY_ID"/>
<element name="LEGAL_ENTITY_NAME" value="LEGAL_ENTITY_NAME"/>
<element name="LE_ADDRESS_LINE_1" value="LE_ADDRESS_LINE_1"/>
<element name="LE_ADDRESS_LINE_2" value="LE_ADDRESS_LINE_2"/>
<element name="LE_ADDRESS_LINE_3" value="LE_ADDRESS_LINE_3"/>
<element name="LE_CITY" value="LE_CITY"/>
<element name="LE_REGION_1" value="LE_REGION_1"/>
<element name="LE_REGION_2" value="LE_REGION_2"/>
<element name="LE_REGION_3" value="LE_REGION_3"/>
<element name="LE_POSTAL_CODE" value="LE_POSTAL_CODE"/>
<element name="LE_COUNTRY" value="LE_COUNTRY"/>
<element name="LE_REGISTRATION_NUMBER" value="LE_REGISTRATION_NUMBER"/>
<element name="LE_REGISTRATION_EFFECTIVE_FROM" value="LE_REGISTRATION_EFFECTIVE_FROM"/>
<element name="LE_BR_DAILY_INSCRIPTION_NUMBER" value="LE_BR_DAILY_INSCRIPTION_NUMBER"/>
<element name="LE_BR_DAILY_INSCRIPTION_DATE" value="LE_BR_DAILY_INSCRIPTION_DATE"/>
<element name="LE_BR_DAILY_ENTITY" value="LE_BR_DAILY_ENTITY"/>
<element name="LE_BR_DAILY_LOCATION" value="LE_BR_DAILY_LOCATION"/>
<element name="LE_BR_DIRECTOR_NUMBER" value="LE_BR_DIRECTOR_NUMBER"/>
<element name="LE_BR_ACCOUNTANT_NUMBER" value="LE_BR_ACCOUNTANT_NUMBER"/>
<element name="LE_BR_ACCOUNTANT_NAME" value="LE_BR_ACCOUNTANT_NAME"/>
<group name="JELINE_ROW" source="Q_JELINES">
<element name="GL_DATE" value="GL_DATE"/>
<element name="CREATED_BY" value="CREATED_BY"/>
<element name="CREATION_DATE" value="CREATION_DATE"/>
<element name="LAST_UPDATE_DATE" value="LAST_UPDATE_DATE"/>
<element name="GL_TRANSFER_DATE" value="GL_TRANSFER_DATE"/>
<element name="REFERENCE_DATE" value="REFERENCE_DATE"/>
<element name="COMPLETED_DATE" value="COMPLETED_DATE"/>
<element name="TRANSACTION_NUMBER" value="TRANSACTION_NUMBER"/>
<element name="TRANSACTION_DATE" value="TRANSACTION_DATE"/>
<element name="ACCOUNTING_SEQUENCE_NAME" value="ACCOUNTING_SEQUENCE_NAME"/>
<element name="ACCOUNTING_SEQUENCE_VERSION" value="ACCOUNTING_SEQUENCE_VERSION"/>
<element name="ACCOUNTING_SEQUENCE_NUMBER" value="ACCOUNTING_SEQUENCE_NUMBER"/>
<element name="REPORTING_SEQUENCE_NAME" value="REPORTING_SEQUENCE_NAME"/>
<element name="REPORTING_SEQUENCE_VERSION" value="REPORTING_SEQUENCE_VERSION"/>
<element name="REPORTING_SEQUENCE_NUMBER" value="REPORTING_SEQUENCE_NUMBER"/>
<element name="DOCUMENT_CATEGORY" value="DOCUMENT_CATEGORY"/>
<element name="DOCUMENT_SEQUENCE_NAME" value="DOCUMENT_SEQUENCE_NAME"/>
<element name="DOCUMENT_SEQUENCE_NUMBER" value="DOCUMENT_SEQUENCE_NUMBER"/>
<element name="APPLICATION_ID" value="APPLICATION_ID"/>
<element name="APPLICATION_NAME" value="APPLICATION_NAME"/>
<element name="HEADER_ID" value="HEADER_ID"/>
<element name="HEADER_DESCRIPTION" value="HEADER_DESCRIPTION"/>
<element name="FUND_STATUS" value="FUND_STATUS"/>
<element name="JE_CATEGORY_NAME" value="JE_CATEGORY_NAME"/>
<element name="JE_SOURCE_NAME" value="JE_SOURCE_NAME"/>
<element name="EVENT_ID" value="EVENT_ID"/>
<element name="EVENT_DATE" value="EVENT_DATE"/>
<element name="EVENT_NUMBER" value="EVENT_NUMBER"/>
<element name="EVENT_CLASS_CODE" value="EVENT_CLASS_CODE"/>
<element name="EVENT_CLASS_NAME" value="EVENT_CLASS_NAME"/>
<element name="EVENT_TYPE_CODE" value="EVENT_TYPE_CODE"/>
<element name="EVENT_TYPE_NAME" value="EVENT_TYPE_NAME"/>
<element name="GL_BATCH_NAME" value="GL_BATCH_NAME"/>
<element name="POSTED_DATE" value="POSTED_DATE"/>
<element name="GL_JE_NAME" value="GL_JE_NAME"/>
<element name="EXTERNAL_REFERENCE" value="EXTERNAL_REFERENCE"/>
<element name="GL_LINE_NUMBER" value="GL_LINE_NUMBER"/>
<element name="LINE_NUMBER" value="LINE_NUMBER"/>
<element name="ORIG_LINE_NUMBER" value="ORIG_LINE_NUMBER"/>
<element name="ACCOUNTING_CLASS_CODE" value="ACCOUNTING_CLASS_CODE"/>
<element name="ACCOUNTING_CLASS_NAME" value="ACCOUNTING_CLASS_NAME"/>
<element name="LINE_DESCRIPTION" value="LINE_DESCRIPTION"/>
<element name="ENTERED_CURRENCY" value="ENTERED_CURRENCY"/>
<element name="CONVERSION_RATE" value="CONVERSION_RATE"/>
<element name="CONVERSION_RATE_DATE" value="CONVERSION_RATE_DATE"/>
<element name="CONVERSION_RATE_TYPE_CODE" value="CONVERSION_RATE_TYPE_CODE"/>
<element name="CONVERSION_RATE_TYPE" value="CONVERSION_RATE_TYPE"/>
<element name="ENTERED_DR" value="ENTERED_DR"/>
<element name="ENTERED_CR" value="ENTERED_CR"/>
<element name="UNROUNDED_ACCOUNTED_DR" value="UNROUNDED_ACCOUNTED_DR"/>
<element name="UNROUNDED_ACCOUNTED_CR" value="UNROUNDED_ACCOUNTED_CR"/>
<element name="ACCOUNTED_DR" value="ACCOUNTED_DR"/>
<element name="ACCOUNTED_CR" value="ACCOUNTED_CR"/>
<element name="STATISTICAL_AMOUNT" value="STATISTICAL_AMOUNT"/>
<element name="RECONCILIATION_REFERENCE" value="RECONCILIATION_REFERENCE"/>
<element name="ATTRIBUTE_CATEGORY" value="ATTRIBUTE_CATEGORY"/>
<element name="ATTRIBUTE1" value="ATTRIBUTE1"/>
<element name="ATTRIBUTE2" value="ATTRIBUTE2"/>
<element name="ATTRIBUTE3" value="ATTRIBUTE3"/>
<element name="ATTRIBUTE4" value="ATTRIBUTE4"/>
<element name="ATTRIBUTE5" value="ATTRIBUTE5"/>
<element name="ATTRIBUTE6" value="ATTRIBUTE6"/>
<element name="ATTRIBUTE7" value="ATTRIBUTE7"/>
<element name="ATTRIBUTE8" value="ATTRIBUTE8"/>
<element name="ATTRIBUTE9" value="ATTRIBUTE9"/>
<element name="ATTRIBUTE10" value="ATTRIBUTE10"/>
<element name="PARTY_TYPE_CODE" value="PARTY_TYPE_CODE"/>
<element name="PARTY_TYPE" value="PARTY_TYPE"/>
<element name="PARTY_NUMBER" value="PARTY_NUMBER"/>
<element name="PARTY_NAME" value="PARTY_NAME"/>
<element name="PARTY_TYPE_TAXPAYER_ID" value="PARTY_TYPE_TAXPAYER_ID"/>
<element name="PARTY_TAX_REGISTRATION_NUMBER" value="PARTY_TAX_REGISTRATION_NUMBER"/>
<element name="PARTY_SITE_NUMBER" value="PARTY_SITE_NUMBER"/>
<element name="PARTY_SITE_NAME" value="PARTY_SITE_NAME"/>
<element name="PARTY_SITE_TYPE_TAXPAYER_ID" value="PARTY_SITE_TYPE_TAXPAYER_ID"/>
<element name="PARTY_SITE_TAX_RGSTN_NUMBER" value="PARTY_SITE_TAX_RGSTN_NUMBER"/>
<element name="USER_TRX_IDENTIFIER_NAME_1" value="USER_TRX_IDENTIFIER_NAME_1"/>
<element name="USER_TRX_IDENTIFIER_VALUE_1" value="USER_TRX_IDENTIFIER_VALUE_1"/>
<element name="USER_TRX_IDENTIFIER_NAME_2" value="USER_TRX_IDENTIFIER_NAME_2"/>
<element name="USER_TRX_IDENTIFIER_VALUE_2" value="USER_TRX_IDENTIFIER_VALUE_2"/>
<element name="USER_TRX_IDENTIFIER_NAME_3" value="USER_TRX_IDENTIFIER_NAME_3"/>
<element name="USER_TRX_IDENTIFIER_VALUE_3" value="USER_TRX_IDENTIFIER_VALUE_3"/>
<element name="USER_TRX_IDENTIFIER_NAME_4" value="USER_TRX_IDENTIFIER_NAME_4"/>
<element name="USER_TRX_IDENTIFIER_VALUE_4" value="USER_TRX_IDENTIFIER_VALUE_4"/>
<element name="USER_TRX_IDENTIFIER_NAME_5" value="USER_TRX_IDENTIFIER_NAME_5"/>
<element name="USER_TRX_IDENTIFIER_VALUE_5" value="USER_TRX_IDENTIFIER_VALUE_5"/>
<element name="USER_TRX_IDENTIFIER_NAME_6" value="USER_TRX_IDENTIFIER_NAME_6"/>
<element name="USER_TRX_IDENTIFIER_VALUE_6" value="USER_TRX_IDENTIFIER_VALUE_6"/>
<element name="USER_TRX_IDENTIFIER_NAME_7" value="USER_TRX_IDENTIFIER_NAME_7"/>
<element name="USER_TRX_IDENTIFIER_VALUE_7" value="USER_TRX_IDENTIFIER_VALUE_7"/>
<element name="USER_TRX_IDENTIFIER_NAME_8" value="USER_TRX_IDENTIFIER_NAME_8"/>
<element name="USER_TRX_IDENTIFIER_VALUE_8" value="USER_TRX_IDENTIFIER_VALUE_8"/>
<element name="USER_TRX_IDENTIFIER_NAME_9" value="USER_TRX_IDENTIFIER_NAME_9"/>
<element name="USER_TRX_IDENTIFIER_VALUE_9" value="USER_TRX_IDENTIFIER_VALUE_9"/>
<element name="USER_TRX_IDENTIFIER_NAME_10" value="USER_TRX_IDENTIFIER_NAME_10"/>
<element name="USER_TRX_IDENTIFIER_VALUE_10" value="USER_TRX_IDENTIFIER_VALUE_10"/>
<group name="TAX" source="Q_TAX">
<element name="TAX_REGIME" value="TAX_REGIME"/>
<element name="TAX" value="TAX"/>
<element name="TAX_NAME" value="TAX_NAME"/>
<element name="TAX_STATUS_NAME" value="TAX_STATUS_NAME"/>
<element name="TAX_RATE_NAME" value="TAX_RATE_NAME"/>
<element name="TAX_RATE" value="TAX_RATE"/>
<element name="TAX_RATE_TYPE_NAME" value="TAX_RATE_TYPE_NAME"/>
<element name="TAX_DETERMINE_DATE" value="TAX_DETERMINE_DATE"/>
<element name="TAX_POINT_DATE" value="TAX_POINT_DATE"/>
<element name="TAX_TYPE_CODE" value="TAX_TYPE_CODE"/>
<element name="TAX_TYPE_NAME" value="TAX_TYPE_NAME"/>
<element name="TAX_CODE" value="TAX_CODE"/>
<element name="TAX_REGISTRATION_NUMBER" value="TAX_REGISTRATION_NUMBER"/>
<element name="TRX_CURRENCY_CODE" value="TRX_CURRENCY_CODE"/>
<element name="TAX_CURRENCY_CODE" value="TAX_CURRENCY_CODE"/>
<element name="TAX_AMOUNT" value="TAX_AMOUNT"/>
<element name="TAX_AMOUNT_TAX_CURRENCY" value="TAX_AMOUNT_TAX_CURRENCY"/>
<element name="TAX_AMOUNT_FUNCTIONAL_CURR" value="TAX_AMOUNT_FUNCTIONAL_CURR"/>
<element name="TAXABLE_AMOUNT" value="TAXABLE_AMOUNT"/>
<element name="TAXABLE_AMOUNT_TAX_CURRENCY" value="TAXABLE_AMOUNT_TAX_CURRENCY"/>
<element name="TAXABLE_AMT_FUNC_CURRENCY" value="TAXABLE_AMT_FUNC_CURRENCY"/>
<element name="UNROUNDED_TAXABLE_AMOUNT" value="UNROUNDED_TAXABLE_AMOUNT"/>
<element name="UNROUNDED_TAX_AMOUNT" value="UNROUNDED_TAX_AMOUNT"/>
<element name="RECOVERABLE_TAX_AMOUNT" value="RECOVERABLE_TAX_AMOUNT"/>
<element name="RECOVERABLE_TAX_AMT_TAX_CURR" value="RECOVERABLE_TAX_AMT_TAX_CURR"/>
<element name="RECOVERABLE_TAX_AMT_FUNC_CURR" value="RECOVERABLE_TAX_AMT_FUNC_CURR"/>
<element name="NON_RECOVERABLE_TAX_AMOUNT" value="NON_RECOVERABLE_TAX_AMOUNT"/>
<element name="NON_REC_TAX_AMT_TAX_CURR" value="NON_REC_TAX_AMT_TAX_CURR"/>
<element name="NON_REC_TAX_AMT_FUNC_CURR" value="NON_REC_TAX_AMT_FUNC_CURR"/>
</group>
</group>
</group>
</group>
</group>
<group name="COMMERCIAL" source="Q_COMMERCIAL">
<element name="LEGAL_COMMERCIAL_NUMBER"
value="LEGAL_COMMERCIAL_NUMBER"/>
</group>
<group name="VAT_REGISTRATION" source="Q_VAT_REGISTRATION">
<element name="LEGAL_VAT_REGISTRATION_NUMBER"
value="LEGAL_VAT_REGISTRATION_NUMBER"/>
</group>
</dataStructure>
</dataTemplate>
Please see attachment for the required output. The problem is that when i run the concurrent program only the upper part is populated. Do i miss any step or procedure here. All the details are not populated
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Customize account analysis report [message #509797 is a reply to message #496177] |
Wed, 01 June 2011 01:52  |
nestor
Messages: 34
Registered: September 2010
|
Member |
|
|
yes i was able to resolve my issue. i edited XLAAARPT.XML and saved it into a new file name. i also revised the XLAAAR01.rtf and saved it into a new file name as well. below is the code i used in the data template.
<?xml version = '1.0' encoding = 'UTF-8'?>
<!-- $Header: NWC_ACCT_ANAL_REP.xml 120.29.12010000.8 2009/08/17 11:49:39 krsankar ship $ -->
<!-- dbdrv: exec java oracle/apps/xdo/oa/util XDOLoader.class java &phase=dat checkfile:~PROD:patch/115/publisher/defs:NWC_ACCT_ANAL_REP.xml UPLOAD -DB_USERNAME &un_apps -DB_PASSWORD &pw_apps -JDBC_CONNECTION &jdbc_db_addr -LOB_TYPE DATA_TEMPLATE -APPS_SHORT_NAME ~PROD -LOB_CODE NWC_ACCT_ANAL_REP -LANGUAGE 00 -XDO_FILE_TYPE XML-DATA-TEMPLATE -FILE_NAME &fullpath:~PROD:patch/115/publisher/defs:NWC_ACCT_ANAL_REP.xml -->
<!-- dbdrv: exec java oracle/apps/xdo/oa/util XDOLoader.class java &phase=dat checkfile:~PROD:patch/115/publisher/defs:NWC_ACCT_ANAL_REP.xml UPLOAD -DB_USERNAME &un_apps -DB_PASSWORD &pw_apps -JDBC_CONNECTION &jdbc_db_addr -LOB_TYPE DATA_TEMPLATE -APPS_SHORT_NAME JG -LOB_CODE JGSLAAANA -LANGUAGE 00 -XDO_FILE_TYPE XML-DATA-TEMPLATE -FILE_NAME &fullpath:~PROD:patch/115/publisher/defs:NWC_ACCT_ANAL_REP.xml -->
<!-- dbdrv: exec java oracle/apps/xdo/oa/util XDOLoader.class java &phase=dat checkfile:~PROD:patch/115/publisher/defs:NWC_ACCT_ANAL_REP.xml UPLOAD -DB_USERNAME &un_apps -DB_PASSWORD &pw_apps -JDBC_CONNECTION &jdbc_db_addr -LOB_TYPE DATA_TEMPLATE -APPS_SHORT_NAME JG -LOB_CODE JGSLAAAR -LANGUAGE 00 -XDO_FILE_TYPE XML-DATA-TEMPLATE -FILE_NAME &fullpath:~PROD:patch/115/publisher/defs:NWC_ACCT_ANAL_REP.xml -->
<!-- dbdrv: exec java oracle/apps/xdo/oa/util XDOLoader.class java &phase=dat checkfile:~PROD:patch/115/publisher/defs:NWC_ACCT_ANAL_REP.xml UPLOAD -DB_USERNAME &un_apps -DB_PASSWORD &pw_apps -JDBC_CONNECTION &jdbc_db_addr -LOB_TYPE DATA_TEMPLATE -APPS_SHORT_NAME CLA -LOB_CODE CLACNGLLEDGER -LANGUAGE 00 -XDO_FILE_TYPE XML-DATA-TEMPLATE -FILE_NAME &fullpath:~PROD:patch/115/publisher/defs:NWC_ACCT_ANAL_REP.xml -->
<!-- dbdrv: exec java oracle/apps/xdo/oa/util XDOLoader.class java &phase=dat checkfile:~PROD:patch/115/publisher/defs:NWC_ACCT_ANAL_REP.xml UPLOAD -DB_USERNAME &un_apps -DB_PASSWORD &pw_apps -JDBC_CONNECTION &jdbc_db_addr -LOB_TYPE DATA_TEMPLATE -APPS_SHORT_NAME CLA -LOB_CODE CLACNGLDAILYCASH -LANGUAGE 00 -XDO_FILE_TYPE XML-DATA-TEMPLATE -FILE_NAME &fullpath:~PROD:patch/115/publisher/defs:NWC_ACCT_ANAL_REP.xml -->
<!-- GSCC_Category="Xdo Data Definition" -->
<dataTemplate name="NWC_ACCT_ANAL_REP" defaultPackage="XLA_ACCT_ANALYSIS_RPT_PKG" version="1.0">
<properties>
<property name="db_fetch_size" value="20" />
</properties>
<parameters>
<parameter name="P_RESP_APPLICATION_ID" dataType="number"/>
<parameter name="P_LEDGER_ID" dataType="character"/>
<parameter name="P_LEDGER" dataType="character"/>
<parameter name="P_COA_ID" dataType="number"/>
<parameter name="P_LEGAL_ENTITY_ID" dataType="number"/>
<parameter name="P_LEGAL_ENTITY" dataType="character"/>
<parameter name="P_PERIOD_FROM" dataType="character"/>
<parameter name="P_PERIOD_TO" dataType="character"/>
<parameter name="P_GL_DATE_FROM" dataType="date"/>
<parameter name="P_GL_DATE_TO" dataType="date"/>
<parameter name="P_BALANCE_TYPE_CODE" dataType="character"/>
<parameter name="P_BALANCE_TYPE" dataType="character"/>
<parameter name="P_DUMMY_BUDGET_VERSION" dataType="character"/>
<parameter name="P_BUDGET_VERSION_ID" dataType="number"/>
<parameter name="P_BUDGET_NAME" dataType="character"/>
<parameter name="P_DUMMY_ENCUMBRANCE_TYPE" dataType="character"/>
<parameter name="P_ENCUMBRANCE_TYPE_ID" dataType="number"/>
<parameter name="P_ENCUMBRANCE_TYPE" dataType="character"/>
<parameter name="P_BALANCE_SIDE_CODE" dataType="character"/>
<parameter name="P_BALANCE_SIDE" dataType="character"/>
<parameter name="P_BALANCE_AMOUNT_FROM" dataType="number"/>
<parameter name="P_BALANCE_AMOUNT_TO" dataType="number"/>
<parameter name="P_BALANCING_SEGMENT_FROM" dataType="character"/>
<parameter name="P_BALANCING_SEGMENT_TO" dataType="character"/>
<parameter name="P_ACCOUNT_SEGMENT_FROM" dataType="character"/>
<parameter name="P_ACCOUNT_SEGMENT_TO" dataType="character"/>
<parameter name="P_ACCOUNT_FLEXFIELD_FROM" dataType="character"/>
<parameter name="P_ACCOUNT_FLEXFIELD_TO" dataType="character"/>
<parameter name="P_INCLUDE_ZERO_AMOUNT_LINES" dataType="character"/>
<parameter name="P_INCLUDE_ZERO_AMT_LINES" dataType="character"/>
<parameter name="P_INCLUDE_USER_TRX_ID_FLAG" dataType="character"/>
<parameter name="P_INCLUDE_USER_TRX_ID" dataType="character"/>
<parameter name="P_INCLUDE_TAX_DETAILS_FLAG" dataType="character"/>
<parameter name="P_INCLUDE_TAX_DETAILS" dataType="character"/>
<parameter name="P_INCLUDE_LE_INFO_FLAG" dataType="character"/>
<parameter name="P_INCLUDE_LEGAL_ENTITY" dataType="character"/>
<parameter name="P_CUSTOM_PARAMETER_1" dataType="character"/>
<parameter name="P_CUSTOM_PARAMETER_2" dataType="character"/>
<parameter name="P_CUSTOM_PARAMETER_3" dataType="character"/>
<parameter name="P_CUSTOM_PARAMETER_4" dataType="character"/>
<parameter name="P_CUSTOM_PARAMETER_5" dataType="character"/>
<parameter name="P_CUSTOM_PARAMETER_6" dataType="character"/>
<parameter name="P_CUSTOM_PARAMETER_7" dataType="character"/>
<parameter name="P_CUSTOM_PARAMETER_8" dataType="character"/>
<parameter name="P_CUSTOM_PARAMETER_9" dataType="character"/>
<parameter name="P_CUSTOM_PARAMETER_10" dataType="character"/>
<parameter name="P_CUSTOM_PARAMETER_11" dataType="character"/>
<parameter name="P_CUSTOM_PARAMETER_12" dataType="character"/>
<parameter name="P_CUSTOM_PARAMETER_13" dataType="character"/>
<parameter name="P_CUSTOM_PARAMETER_14" dataType="character"/>
<parameter name="P_CUSTOM_PARAMETER_15" dataType="character"/>
<parameter name="P_INCLUDE_STAT_AMOUNT_LINES" dataType="character"/>
<parameter name="P_INCLUDE_STAT_AMT_LINES" dataType="character"/>
<parameter name="P_INCLUDE_ACCT_WITH_NO_ACT" dataType="character"/>
<parameter name="P_INC_ACCT_WITH_NO_ACT" dataType="character"/>
</parameters>
<dataQuery>
<sqlStatement name="Q_JELINES">
<![CDATA[
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.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.HEADER_ID HEADER_ID
,TABLE1.HEADER_DESCRIPTION HEADER_DESCRIPTION
,TABLE1.FUND_STATUS FUND_STATUS
,TABLE1.JE_CATEGORY_NAME JE_CATEGORY_NAME
,TABLE1.JE_SOURCE_NAME JE_SOURCE_NAME
,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.GL_BATCH_NAME GL_BATCH_NAME
,TABLE1.POSTED_DATE POSTED_DATE
,TABLE1.GL_JE_NAME GL_JE_NAME
,TABLE1.GL_LINE_NUMBER GL_LINE_NUMBER
,TABLE1.LINE_NUMBER LINE_NUMBER
,TABLE1.ORIG_LINE_NUMBER ORIG_LINE_NUMBER
,TABLE1.ACCOUNTING_CLASS_CODE ACCOUNTING_CLASS_CODE
,TABLE1.ACCOUNTING_CLASS_NAME ACCOUNTING_CLASS_NAME
,TABLE1.LINE_DESCRIPTION LINE_DESCRIPTION
,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
,substr(PARTY_INFO,1,instr(PARTY_INFO,'|',1,1)-1 ) PARTY_NUMBER
,substr(PARTY_INFO,instr(PARTY_INFO,'|',1,1)+1,(instr(PARTY_INFO,'|',1,2)-1-instr(PARTY_INFO,'|',1,1))) PARTY_NAME
,substr(PARTY_INFO,instr(PARTY_INFO,'|',1,2)+1,(instr(PARTY_INFO,'|',1,3)-1-instr(PARTY_INFO,'|',1,2))) PARTY_TYPE_TAXPAYER_ID
,substr(PARTY_INFO,instr(PARTY_INFO,'|',1,3)+1,(instr(PARTY_INFO,'|',1,4)-1-instr(PARTY_INFO,'|',1,3))) PARTY_TAX_REGISTRATION_NUMBER
,substr(PARTY_INFO,instr(PARTY_INFO,'|',1,4)+1,(instr(PARTY_INFO,'|',1,5)-1-instr(PARTY_INFO,'|',1,4))) PARTY_SITE_NUMBER
,substr(PARTY_INFO,instr(PARTY_INFO,'|',1,5)+1,(instr(PARTY_INFO,'|',1,6)-1-instr(PARTY_INFO,'|',1,5))) PARTY_SITE_NAME
,substr(PARTY_INFO,instr(PARTY_INFO,'|',1,6)+1,(length(PARTY_INFO)- instr(PARTY_INFO,'|',1,6))) PARTY_SITE_TAX_RGSTN_NUMBER
,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
,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.BALANCE_TYPE_CODE BALANCE_TYPE_CODE
,TABLE1.BALANCE_TYPE BALANCE_TYPE
,TABLE1.BUDGET_NAME BUDGET_NAME
,TABLE1.ENCUMBRANCE_TYPE ENCUMBRANCE_TYPE
,TABLE2.BEGIN_BALANCE_DR BEGIN_BALANCE_DR
,TABLE2.BEGIN_BALANCE_CR BEGIN_BALANCE_CR
,TABLE1.CODE_COMBINATION_ID CODE_COMBINATION_ID
,TABLE1.ACCOUNTING_CODE_COMBINATION ACCOUNTING_CODE_COMBINATION
,TABLE1.CODE_COMBINATION_DESCRIPTION CODE_COMBINATION_DESCRIPTION
FROM
(SELECT /*+ leading (glbgt gjl gjh gir ael aeh) */
to_char(aeh.accounting_date
,'YYYY-MM-DD') GL_DATE
,fdu.user_name CREATED_BY
,to_char(aeh.creation_date
,'YYYY-MM-DD"T"hh:mi:ss') CREATION_DATE
,to_char(aeh.last_update_date
,'YYYY-MM-DD') LAST_UPDATE_DATE
,to_char(aeh.gl_transfer_date
,'YYYY-MM-DD"T"hh:mi:ss') GL_TRANSFER_DATE
,to_char(aeh.reference_date
,'YYYY-MM-DD') REFERENCE_DATE
,to_char(aeh.completed_date
,'YYYY-MM-DD"T"hh:mi:ss') COMPLETED_DATE
,ent.transaction_number TRANSACTION_NUMBER
,to_char(xle.transaction_date
,'YYYY-MM-DD"T"hh:mi:ss') TRANSACTION_DATE
,fsv1.header_name ACCOUNTING_SEQUENCE_NAME
,fsv1.version_name ACCOUNTING_SEQUENCE_VERSION
,aeh.completion_acct_seq_value ACCOUNTING_SEQUENCE_NUMBER
,fsv2.header_name REPORTING_SEQUENCE_NAME
,fsv2.version_name REPORTING_SEQUENCE_VERSION
,aeh.close_acct_seq_value REPORTING_SEQUENCE_NUMBER
,NULL DOCUMENT_CATEGORY
,fns.name DOCUMENT_SEQUENCE_NAME
,aeh.doc_sequence_value DOCUMENT_SEQUENCE_NUMBER
,aeh.application_id APPLICATION_ID
,fap.application_name APPLICATION_NAME
,aeh.ae_header_id HEADER_ID
,aeh.description HEADER_DESCRIPTION
,xlk1.meaning FUND_STATUS
,gjct.user_je_category_name JE_CATEGORY_NAME
,gjst.user_je_source_name JE_SOURCE_NAME
,xle.event_id EVENT_ID
,to_char(xle.event_date
,'YYYY-MM-DD') EVENT_DATE
,xle.event_number EVENT_NUMBER
,xet.event_class_code EVENT_CLASS_CODE
,xect.NAME EVENT_CLASS_NAME
,aeh.event_type_code EVENT_TYPE_CODE
,xet.NAME EVENT_TYPE_NAME
,gjb.NAME GL_BATCH_NAME
,to_char(gjb.posted_date
,'YYYY-MM-DD') POSTED_DATE
,gjh.NAME GL_JE_NAME
,gjh.external_reference EXTERNAL_REFERENCE
,gjl.je_line_num GL_LINE_NUMBER
,ael.displayed_line_number LINE_NUMBER
,ael.ae_line_num ORIG_LINE_NUMBER
,ael.accounting_class_code ACCOUNTING_CLASS_CODE
,xlk2.meaning ACCOUNTING_CLASS_NAME
,ael.description LINE_DESCRIPTION
,ael.currency_code ENTERED_CURRENCY
,ael.currency_conversion_rate CONVERSION_RATE
,to_char(ael.currency_conversion_date
,'YYYY-MM-DD') CONVERSION_RATE_DATE
,ael.currency_conversion_type CONVERSION_RATE_TYPE_CODE
,gdct.user_conversion_type CONVERSION_RATE_TYPE
,ael.entered_dr ENTERED_DR
,ael.entered_cr ENTERED_CR
,ael.unrounded_accounted_dr UNROUNDED_ACCOUNTED_DR
,ael.unrounded_accounted_cr UNROUNDED_ACCOUNTED_CR
,ael.accounted_dr ACCOUNTED_DR
,ael.accounted_cr ACCOUNTED_CR
,ael.statistical_amount STATISTICAL_AMOUNT
,ael.jgzz_recon_ref RECONCILIATION_REFERENCE
,ael.attribute_category ATTRIBUTE_CATEGORY
,ael.attribute1 ATTRIBUTE1
,ael.attribute2 ATTRIBUTE2
,ael.attribute3 ATTRIBUTE3
,ael.attribute4 ATTRIBUTE4
,ael.attribute5 ATTRIBUTE5
,ael.attribute6 ATTRIBUTE6
,ael.attribute7 ATTRIBUTE7
,ael.attribute8 ATTRIBUTE8
,ael.attribute9 ATTRIBUTE9
,ael.attribute10 ATTRIBUTE10
,ael.party_type_code PARTY_TYPE_CODE
,NULL PARTY_TYPE
&p_party_columns PARTY_INFO
&p_trx_identifiers_1
&p_trx_identifiers_2
&p_trx_identifiers_3
&p_trx_identifiers_4
&p_trx_identifiers_5
,glbgt.ledger_id LEDGER_ID
,glbgt.ledger_short_name LEDGER_SHORT_NAME
,glbgt.ledger_description LEDGER_DESCRIPTION
,glbgt.ledger_name LEDGER_NAME
,glbgt.ledger_currency LEDGER_CURRENCY
,glbgt.balance_type_code BALANCE_TYPE_CODE
,glbgt.balance_type BALANCE_TYPE
,glbgt.budget_name BUDGET_NAME
,glbgt.encumbrance_type ENCUMBRANCE_TYPE
,glbgt.code_combination_id CODE_COMBINATION_ID
,glbgt.accounting_code_combination ACCOUNTING_CODE_COMBINATION
,glbgt.code_combination_description CODE_COMBINATION_DESCRIPTION
FROM xla_ae_headers aeh
,xla_ae_lines ael
,xla_lookups xlk1
,xla_lookups xlk2
,xla_events xle
,xla_event_classes_tl xect
,xla_event_types_tl xet
,fnd_user fdu
,xla_transaction_entities ent
,fnd_application_tl fap
,fun_seq_versions fsv1
,fun_seq_versions fsv2
,fnd_document_sequences fns
,gl_je_categories_tl gjct
,gl_je_sources_tl gjst
,gl_daily_conversion_types gdct
,gl_import_references gir
,gl_je_lines gjl
,gl_je_headers gjh
,gl_je_batches gjb
,xla_report_balances_gt glbgt
WHERE &p_ledger_filters
--added as part of 6719948
AND gjl.ledger_id = glbgt.ledger_id
AND gjl.code_combination_id = glbgt.code_combination_id
AND gjl.effective_date BETWEEN glbgt.period_start_date AND glbgt.period_end_date
AND gjl.effective_date BETWEEN :P_GL_DATE_FROM AND :P_GL_DATE_TO
AND gjl.period_name = glbgt.period_name
AND gjl.je_header_id = gjh.je_header_id
AND gjl.period_name = gjh.period_name
AND gjl.je_header_id = gir.je_header_id
AND gjl.je_line_num = gir.je_line_num
AND gjh.je_header_id = gir.je_header_id
AND gjh.status = 'P'
AND NVL(gjh.je_from_sla_flag,'N') IN('Y','U')
AND gjb.je_batch_id = gir.je_batch_id
AND gjb.status = 'P'
AND gir.gl_sl_link_id = ael.gl_sl_link_id
AND gir.gl_sl_link_table = ael.gl_sl_link_table
--AND gjh.currency_code = glbgt.ledger_currency --added bug 6722505
AND gjct.je_category_name = aeh.je_category_name
AND gjct.LANGUAGE = USERENV('LANG')
AND gjst.je_source_name = gjh.je_source
AND gjst.LANGUAGE = USERENV('LANG')
AND aeh.accounting_entry_status_code = 'F'
AND aeh.gl_transfer_status_code = 'Y'
AND aeh.balance_type_code = glbgt.balance_type_code
AND NVL(aeh.budget_version_id,-19999) = NVL(glbgt.budget_version_id,-19999)
AND ael.application_id = aeh.application_id
AND ael.ae_header_id = aeh.ae_header_id
AND NVL(ael.encumbrance_type_id,-19999)= NVL(glbgt.encumbrance_type_id,-19999) -- 4458381
AND xlk1.lookup_type(+) = 'XLA_FUNDS_STATUS'
AND xlk1.lookup_code(+) = aeh.funds_status_code
AND xlk2.lookup_type = 'XLA_ACCOUNTING_CLASS'
AND xlk2.lookup_code = ael.accounting_class_code
AND xle.application_id = aeh.application_id
AND xle.event_id = aeh.event_id
AND xet.application_id = aeh.application_id
AND xet.event_type_code = aeh.event_type_code
AND xet.LANGUAGE = USERENV('LANG')
AND xect.application_id = xet.application_id
AND xect.entity_code = xet.entity_code
AND xect.event_class_code = xet.event_class_code
AND xect.LANGUAGE = USERENV('LANG')
AND ent.application_id = aeh.application_id
AND ent.entity_id = aeh.entity_id
--AND ent.ledger_id = aeh.ledger_id removed for Bug 7557990
AND fdu.user_id = ent.created_by
AND fap.application_id = aeh.application_id
AND fap.LANGUAGE = USERENV('LANG')
AND fsv1.seq_version_id(+) = aeh.completion_acct_seq_version_id
AND fsv2.seq_version_id(+) = aeh.close_acct_seq_version_id
AND fns.application_id(+) = aeh.application_id
AND fns.doc_sequence_id(+) = aeh.doc_sequence_id
AND gdct.conversion_type(+) = ael.currency_conversion_type
&p_sla_other_filter
--removed as part of 6719948
--AND ael.code_combination_id = glbgt.code_combination_id
--AND aeh.ledger_id = glbgt.ledger_id
--AND aeh.accounting_date BETWEEN glbgt.period_start_date AND glbgt.period_end_date
--AND aeh.accounting_date BETWEEN :P_GL_DATE_FROM AND :P_GL_DATE_TO
--AND aeh.period_name = glbgt.period_name
--AND gjh.ledger_id = aeh.ledger_id
UNION ALL
SELECT /*+ leading (glbgt gjl gjh gjb) */
to_char(gjh.default_effective_date
,'YYYY-MM-DD') GL_DATE
,fdu.user_name CREATED_BY
,to_char(gjh.creation_date
,'YYYY-MM-DD"T"hh:mi:ss') CREATION_DATE
,to_char(gjh.last_update_date
,'YYYY-MM-DD') LAST_UPDATE_DATE
,NULL GL_TRANSFER_DATE
,to_char(gjh.reference_date
,'YYYY-MM-DD') REFERENCE_DATE
,NULL COMPLETED_DATE
,NULL TRANSACTION_NUMBER
,NULL TRANSACTION_DATE
,fsv1.header_name ACCOUNTING_SEQUENCE_NAME
,fsv1.version_name ACCOUNTING_SEQUENCE_VERSION
,gjh.posting_acct_seq_value ACCOUNTING_SEQUENCE_NUMBER
,fsv2.header_name REPORTING_SEQUENCE_NAME
,fsv2.version_name REPORTING_SEQUENCE_VERSION
,gjh.close_acct_seq_value REPORTING_SEQUENCE_NUMBER
,NULL DOCUMENT_CATEGORY
,NULL DOCUMENT_SEQUENCE_NAME
,NULL DOCUMENT_SEQUENCE_NUMBER
,NULL APPLICATION_ID
,NULL APPLICATION_NAME
,gjh.je_header_id HEADER_ID
,gjh.description HEADER_DESCRIPTION
,NULL FUND_STATUS
,gjct.user_je_category_name JE_CATEGORY_NAME
,gjst.user_je_source_name JE_SOURCE_NAME
,NULL EVENT_ID
,NULL EVENT_DATE
,NULL EVENT_NUMBER
,NULL EVENT_CLASS_CODE
,NULL EVENT_CLASS_NAME
,NULL EVENT_TYPE_CODE
,NULL EVENT_TYPE_NAME
,gjb.NAME GL_BATCH_NAME
,to_char(gjb.posted_date
,'YYYY-MM-DD') POSTED_DATE
,gjh.NAME GL_JE_NAME
,gjh.external_reference EXTERNAL_REFERENCE
,gjl.je_line_num GL_LINE_NUMBER
,gjl.je_line_num LINE_NUMBER
,gjl.je_line_num ORIG_LINE_NUMBER
,NULL ACCOUNTING_CLASS_CODE
,NULL ACCOUNTING_CLASS_NAME
,gjl.description LINE_DESCRIPTION
,gjh.currency_code ENTERED_CURRENCY
,gjh.currency_conversion_rate CONVERSION_RATE
,to_char(gjh.currency_conversion_date
,'YYYY-MM-DD') CONVERSION_RATE_DATE
,gjh.currency_conversion_type CONVERSION_RATE_TYPE_CODE
,gdct.user_conversion_type CONVERSION_RATE_TYPE
,gjl.entered_dr ENTERED_DR
,gjl.entered_cr ENTERED_CR
,NULL UNROUNDED_ACCOUNTED_DR
,NULL UNROUNDED_ACCOUNTED_CR
,gjl.accounted_dr ACCOUNTED_DR
,gjl.accounted_cr ACCOUNTED_CR
,gjl.stat_amount STATISTICAL_AMOUNT
,gjl.jgzz_recon_ref_11i RECONCILIATION_REFERENCE
,gjl.context ATTRIBUTE_CATEGORY
,gjl.attribute1 ATTRIBUTE1
,gjl.attribute2 ATTRIBUTE2
,gjl.attribute3 ATTRIBUTE3
,gjl.attribute4 ATTRIBUTE4
,gjl.attribute5 ATTRIBUTE5
,gjl.attribute6 ATTRIBUTE6
,gjl.attribute7 ATTRIBUTE7
,gjl.attribute8 ATTRIBUTE8
,gjl.attribute9 ATTRIBUTE9
,gjl.attribute10 ATTRIBUTE10
,NULL PARTY_TYPE_CODE
,NULL PARTY_TYPE
,NULL PARTY_INFO
,NULL USERIDS
,glbgt.ledger_id LEDGER_ID
,glbgt.ledger_short_name LEDGER_SHORT_NAME
,glbgt.ledger_description LEDGER_DESCRIPTION
,glbgt.ledger_name LEDGER_NAME
,glbgt.ledger_currency LEDGER_CURRENCY
,glbgt.balance_type_code BALANCE_TYPE_CODE
,glbgt.balance_type BALANCE_TYPE
,glbgt.budget_name BUDGET_NAME
,glbgt.encumbrance_type ENCUMBRANCE_TYPE
,glbgt.code_combination_id CODE_COMBINATION_ID
,glbgt.accounting_code_combination ACCOUNTING_CODE_COMBINATION
,glbgt.code_combination_description CODE_COMBINATION_DESCRIPTION
FROM fnd_user fdu
,fun_seq_versions fsv1
,fun_seq_versions fsv2
,gl_je_categories_tl gjct
,gl_je_sources_tl gjst
,gl_daily_conversion_types gdct
,gl_je_lines gjl
,gl_je_headers gjh
,gl_je_batches gjb
,xla_report_balances_gt glbgt
WHERE gjl.ledger_id = glbgt.ledger_id
AND gjl.code_combination_id = glbgt.code_combination_id
AND gjl.effective_date BETWEEN glbgt.period_start_date AND glbgt.period_end_date
AND gjl.effective_date BETWEEN :P_GL_DATE_FROM AND :P_GL_DATE_TO
AND gjl.period_name = glbgt.period_name
AND gjh.je_header_id = gjl.je_header_id
AND gjh.actual_flag = glbgt.balance_type_code
AND decode(gjh.currency_code,'STAT',gjh.currency_code,glbgt.ledger_currency) = glbgt.ledger_currency --added bug 6686541
AND NVL(gjh.je_from_sla_flag,'N') = 'N'
AND NVL(gjh.budget_version_id,-19999)= NVL(glbgt.budget_version_id,-19999)
AND NVL(gjh.encumbrance_type_id,-19999)= NVL(glbgt.encumbrance_type_id,-19999)
AND gjb.je_batch_id = gjh.je_batch_id
AND gjb.status = 'P'
AND fdu.user_id = gjb.created_by
AND fsv1.seq_version_id(+) = gjh.posting_acct_seq_version_id
AND fsv2.seq_version_id(+) = gjh.close_acct_seq_version_id
AND gjct.je_category_name = gjh.je_category
AND gjct.LANGUAGE = USERENV('LANG')
AND gjst.je_source_name = gjh.je_source
AND gjst.language = USERENV('LANG')
AND gdct.conversion_type(+) = gjh.currency_conversion_type
&p_gl_other_filter
UNION ALL
SELECT /*+ leading (glbgt gjl gjh gjb) */
to_char(gjh.default_effective_date
,'YYYY-MM-DD') GL_DATE
,fdu.user_name CREATED_BY
,to_char(gjh.creation_date
,'YYYY-MM-DD"T"hh:mi:ss') CREATION_DATE
,to_char(gjh.last_update_date
,'YYYY-MM-DD') LAST_UPDATE_DATE
,NULL GL_TRANSFER_DATE
,to_char(gjh.reference_date
,'YYYY-MM-DD') REFERENCE_DATE
,NULL COMPLETED_DATE
,NULL TRANSACTION_NUMBER
,NULL TRANSACTION_DATE
,fsv1.header_name ACCOUNTING_SEQUENCE_NAME
,fsv1.version_name ACCOUNTING_SEQUENCE_VERSION
,gjh.posting_acct_seq_value ACCOUNTING_SEQUENCE_NUMBER
,fsv2.header_name REPORTING_SEQUENCE_NAME
,fsv2.version_name REPORTING_SEQUENCE_VERSION
,gjh.close_acct_seq_value REPORTING_SEQUENCE_NUMBER
,NULL DOCUMENT_CATEGORY
,NULL DOCUMENT_SEQUENCE_NAME
,NULL DOCUMENT_SEQUENCE_NUMBER
,NULL APPLICATION_ID
,NULL APPLICATION_NAME
,gjh.je_header_id HEADER_ID
,gjh.description HEADER_DESCRIPTION
,NULL FUND_STATUS
,gjct.user_je_category_name JE_CATEGORY_NAME
,gjst.user_je_source_name JE_SOURCE_NAME
,NULL EVENT_ID
,NULL EVENT_DATE
,NULL EVENT_NUMBER
,NULL EVENT_CLASS_CODE
,NULL EVENT_CLASS_NAME
,NULL EVENT_TYPE_CODE
,NULL EVENT_TYPE_NAME
,gjb.NAME GL_BATCH_NAME
,to_char(gjb.posted_date
,'YYYY-MM-DD') POSTED_DATE
,gjh.NAME GL_JE_NAME
,gjh.external_reference EXTERNAL_REFERENCE
,gjl.je_line_num GL_LINE_NUMBER
,gjl.je_line_num LINE_NUMBER
,gjl.je_line_num ORIG_LINE_NUMBER
,NULL ACCOUNTING_CLASS_CODE
,NULL ACCOUNTING_CLASS_NAME
,gjl.description LINE_DESCRIPTION
,gjh.currency_code ENTERED_CURRENCY
,gjh.currency_conversion_rate CONVERSION_RATE
,to_char(gjh.currency_conversion_date
,'YYYY-MM-DD') CONVERSION_RATE_DATE
,gjh.currency_conversion_type CONVERSION_RATE_TYPE_CODE
,gdct.user_conversion_type CONVERSION_RATE_TYPE
,gjl.entered_dr ENTERED_DR
,gjl.entered_cr ENTERED_CR
,NULL UNROUNDED_ACCOUNTED_DR
,NULL UNROUNDED_ACCOUNTED_CR
,gjl.accounted_dr ACCOUNTED_DR
,gjl.accounted_cr ACCOUNTED_CR
,gjl.stat_amount STATISTICAL_AMOUNT
,gjl.jgzz_recon_ref_11i RECONCILIATION_REFERENCE
,gjl.context ATTRIBUTE_CATEGORY
,gjl.attribute1 ATTRIBUTE1
,gjl.attribute2 ATTRIBUTE2
,gjl.attribute3 ATTRIBUTE3
,gjl.attribute4 ATTRIBUTE4
,gjl.attribute5 ATTRIBUTE5
,gjl.attribute6 ATTRIBUTE6
,gjl.attribute7 ATTRIBUTE7
,gjl.attribute8 ATTRIBUTE8
,gjl.attribute9 ATTRIBUTE9
,gjl.attribute10 ATTRIBUTE10
,NULL PARTY_TYPE_CODE
,NULL PARTY_TYPE
,NULL PARTY_INFO
,NULL USERIDS
,glbgt.ledger_id LEDGER_ID
,glbgt.ledger_short_name LEDGER_SHORT_NAME
,glbgt.ledger_description LEDGER_DESCRIPTION
,glbgt.ledger_name LEDGER_NAME
,glbgt.ledger_currency LEDGER_CURRENCY
,glbgt.balance_type_code BALANCE_TYPE_CODE
,glbgt.balance_type BALANCE_TYPE
,glbgt.budget_name BUDGET_NAME
,glbgt.encumbrance_type ENCUMBRANCE_TYPE
,glbgt.code_combination_id CODE_COMBINATION_ID
,glbgt.accounting_code_combination ACCOUNTING_CODE_COMBINATION
,glbgt.code_combination_description CODE_COMBINATION_DESCRIPTION
FROM fnd_user fdu
,fnd_new_messages fnm
,fun_seq_versions fsv1
,fun_seq_versions fsv2
,gl_je_categories_tl gjct
,gl_je_sources_tl gjst
,gl_daily_conversion_types gdct
,gl_je_lines gjl
,gl_je_headers gjh
,gl_je_batches gjb
,xla_report_balances_gt glbgt
WHERE gjl.ledger_id = glbgt.ledger_id
AND gjl.code_combination_id = glbgt.code_combination_id
AND gjl.effective_date BETWEEN glbgt.period_start_date AND glbgt.period_end_date
AND gjl.effective_date BETWEEN :P_GL_DATE_FROM AND :P_GL_DATE_TO
AND gjl.period_name = glbgt.period_name
AND gjh.je_header_id = gjl.je_header_id
AND gjh.actual_flag = glbgt.balance_type_code
AND decode(gjh.currency_code,'STAT',gjh.currency_code,glbgt.ledger_currency) = glbgt.ledger_currency --added bug 6686541
AND NVL(gjh.je_from_sla_flag,'N') = 'U'
AND fnm.application_id = 101
AND fnm.language_code = USERENV('LANG')
AND fnm.message_name in ('PPOS0220', 'PPOS0221', 'PPOS0222', 'PPOS0243', 'PPOS0222_G','PPOSO275')
AND gjl.description= fnm.message_text
AND NVL(gjh.budget_version_id,-19999) = NVL(glbgt.budget_version_id,-19999)
AND NVL(gjh.encumbrance_type_id,-19999) = NVL(glbgt.encumbrance_type_id,-19999)
AND gjb.je_batch_id = gjh.je_batch_id
AND gjb.status = 'P'
AND fdu.user_id = gjb.created_by
AND fsv1.seq_version_id(+) = gjh.posting_acct_seq_version_id
AND fsv2.seq_version_id(+) = gjh.close_acct_seq_version_id
AND gjct.je_category_name = gjh.je_category
AND gjct.LANGUAGE = USERENV('LANG')
AND gjst.je_source_name = gjh.je_source
AND gjst.language = USERENV('LANG')
AND gdct.conversion_type(+) = gjh.currency_conversion_type
AND not exists (select 'x' from gl_import_references gir where
gir.je_header_id=gjl.je_header_id
and gir.je_line_num=gjl.je_line_num)
&p_gl_other_filter
&p_begin_balance_union_all
)TABLE1,
( SELECT glbgt.ledger_id LEDGER_ID
, glbgt.code_combination_id CODE_COMBINATION_ID
, NVL(glbgt.BEGIN_BALANCE_DR,0) BEGIN_BALANCE_DR
, NVL(glbgt.BEGIN_BALANCE_CR,0) BEGIN_BALANCE_CR
FROM xla_report_balances_gt glbgt
WHERE glbgt.period_name = :P_PERIOD_FROM
UNION
SELECT glbgt2.ledger_id LEDGER_ID
, glbgt2.code_combination_id CODE_COMBINATION_ID
, 0 BEGIN_BALANCE_DR
, 0 BEGIN_BALANCE_CR
FROM xla_report_balances_gt glbgt2
WHERE NOT EXISTS (SELECT 1
FROM xla_report_balances_gt glbgt3
WHERE glbgt3.period_name = :P_PERIOD_FROM
AND glbgt3.code_combination_id = glbgt2.code_combination_id)) TABLE2
WHERE TABLE1.LEDGER_ID = TABLE2.LEDGER_ID
AND TABLE1.CODE_COMBINATION_ID = TABLE2.CODE_COMBINATION_ID
ORDER BY
TABLE1.LEDGER_NAME
,TABLE1.LEDGER_CURRENCY
,TABLE1.CODE_COMBINATION_ID
,TABLE1.BALANCE_TYPE_CODE
,TABLE1.BUDGET_NAME
,TABLE1.ENCUMBRANCE_TYPE
,TABLE1.JE_SOURCE_NAME
,TABLE1.HEADER_ID
]]>
</sqlStatement>
<sqlStatement name="Q_TAX">
<![CDATA[
SELECT /*+ index(xdl, XLA_DISTRIBUTION_LINKS_N3) */
zxr.tax_regime_name TAX_REGIME
,zxl.tax TAX
,ztt.tax_full_name TAX_NAME
,zst.tax_status_name TAX_STATUS_NAME
,zrt.tax_rate_name TAX_RATE_NAME
,zxl.tax_rate TAX_RATE
,flk1.meaning TAX_RATE_TYPE_NAME
,to_char(zxl.tax_determine_date
,'YYYY-MM-DD') TAX_DETERMINE_DATE
,to_char(zxl.tax_point_date
,'YYYY-MM-DD') TAX_POINT_DATE
,zxl.tax_type_code TAX_TYPE_CODE
,flk2.meaning TAX_TYPE_NAME
,zxl.tax_code TAX_CODE
,zxl.tax_registration_number TAX_REGISTRATION_NUMBER
,zxl.trx_currency_code TRX_CURRENCY_CODE
,zxl.tax_currency_code TAX_CURRENCY_CODE
,zxl.tax_amt TAX_AMOUNT
,zxl.tax_amt_tax_curr TAX_AMOUNT_TAX_CURRENCY
,zxl.tax_amt_funcl_curr TAX_AMOUNT_FUNCTIONAL_CURR
,zxl.taxable_amt TAXABLE_AMOUNT
,zxl.taxable_amt_tax_curr TAXABLE_AMOUNT_TAX_CURRENCY
,zxl.taxable_amt_funcl_curr TAXABLE_AMT_FUNC_CURRENCY
,zxl.unrounded_taxable_amt UNROUNDED_TAXABLE_AMOUNT
,zxl.unrounded_tax_amt UNROUNDED_TAX_AMOUNT
,zxl.rec_tax_amt RECOVERABLE_TAX_AMOUNT
,zxl.rec_tax_amt_tax_curr RECOVERABLE_TAX_AMT_TAX_CURR
,zxl.rec_tax_amt_funcl_curr RECOVERABLE_TAX_AMT_FUNC_CURR
,zxl.nrec_tax_amt NON_RECOVERABLE_TAX_AMOUNT
,zxl.nrec_tax_amt_tax_curr NON_REC_TAX_AMT_TAX_CURR
,zxl.nrec_tax_amt_funcl_curr NON_REC_TAX_AMT_FUNC_CURR
FROM xla_distribution_links xdl
,zx_lines zxl
,zx_regimes_tl zxr
,zx_taxes_tl ztt
,zx_status_tl zst
,zx_rates_tl zrt
,fnd_lookups flk1
,fnd_lookups flk2
WHERE xdl.tax_line_ref_id = zxl.tax_line_id
AND zxr.tax_regime_id(+) = zxl.tax_regime_id
AND zxr.language(+) = USERENV('LANG')
AND ztt.tax_id(+) = zxl.tax_id
AND ztt.language(+) = USERENV('LANG')
AND zst.tax_status_id(+) = zxl.tax_status_id
AND zst.language(+) = USERENV('LANG')
AND zrt.tax_rate_id(+) = zxl.tax_rate_id
AND zrt.language(+) = USERENV('LANG')
AND flk1.lookup_type = 'ZX_RATE_TYPE'
AND flk1.lookup_code = zxl.tax_rate_type
AND flk2.lookup_type(+) = 'ZX_TAX_TYPE_CATEGORY'
AND flk2.lookup_code(+) = zxl.tax_type_code
AND xdl.application_id = :APPLICATION_ID
AND xdl.ae_header_id = :HEADER_ID
AND xdl.ae_line_num = :ORIG_LINE_NUMBER
]]>
</sqlStatement>
<sqlStatement name="Q_COMMERCIAL">
<![CDATA[ &p_commercial_query ]]>
</sqlStatement>
<sqlStatement name="Q_VAT_REGISTRATION">
<![CDATA[ &p_vat_registration_query ]]>
</sqlStatement>
</dataQuery>
<dataTrigger name="beforeReport" source="XLA_ACCT_ANALYSIS_RPT_PKG.beforeReport"/>
<dataStructure>
<group name="BALANCE_CCID" source = "Q_JELINES">
<element name="LEDGER_ID" value="LEDGER_ID"/>
<element name="LEDGER_SHORT_NAME" value="LEDGER_SHORT_NAME"/>
<element name="LEDGER_DESCRIPTION" value="LEDGER_DESCRIPTION"/>
<element name="LEDGER_NAME" value="LEDGER_NAME"/>
<element name="LEDGER_CURRENCY" value="LEDGER_CURRENCY"/>
<element name="BALANCE_TYPE_CODE" value="BALANCE_TYPE_CODE"/>
<element name="BALANCE_TYPE" value="BALANCE_TYPE"/>
<element name="BUDGET_NAME" value="BUDGET_NAME"/>
<element name="ENCUMBRANCE_TYPE" value="ENCUMBRANCE_TYPE"/>
<element name="LEDGER_SUM_PR_DR" value="ACCT_SUM_PR_DR" function="SUM()"/>
<element name="LEDGER_SUM_PR_CR" value="ACCT_SUM_PR_CR" function="SUM()"/>
<element name="LEDGER_SUM_BAL_DR" value="ACCT_SUM_BAL_DR" function="SUM()"/>
<element name="LEDGER_SUM_BAL_CR" value="ACCT_SUM_BAL_CR" function="SUM()"/>
<group name="CCID_S" source = "Q_JELINES">
<element name="CODE_COMBINATION_ID" value="CODE_COMBINATION_ID"/>
<element name="ACCOUNTING_CODE_COMBINATION" value="ACCOUNTING_CODE_COMBINATION"/>
<element name="CODE_COMBINATION_DESCRIPTION" value="CODE_COMBINATION_DESCRIPTION"/>
<element name="ACCT_SUM_PR_DR" value="PERIOD_NET_DR" function="SUM()"/>
<element name="ACCT_SUM_PR_CR" value="PERIOD_NET_CR" function="SUM()"/>
<element name="ACCT_SUM_BAL_DR" value="BEGIN_BALANCE_DR" function="SUM()"/>
<element name="ACCT_SUM_BAL_CR" value="BEGIN_BALANCE_CR" function="SUM()"/>
<element name="ACCT_SUM_ACCT_DR" value="ACCOUNTED_DR" function="SUM()"/>
<element name="ACCT_SUM_ACCT_CR" value="ACCOUNTED_CR" function="SUM()"/>
<element name="BEGIN_BALANCE_DR" value="BEGIN_BALANCE_DR"/>
<element name="BEGIN_BALANCE_CR" value="BEGIN_BALANCE_CR"/>
<group name="JELINE_ROW" source="Q_JELINES">
<element name="GL_DATE" value="GL_DATE"/>
<element name="CREATED_BY" value="CREATED_BY"/>
<element name="CREATION_DATE" value="CREATION_DATE"/>
<element name="LAST_UPDATE_DATE" value="LAST_UPDATE_DATE"/>
<element name="GL_TRANSFER_DATE" value="GL_TRANSFER_DATE"/>
<element name="REFERENCE_DATE" value="REFERENCE_DATE"/>
<element name="COMPLETED_DATE" value="COMPLETED_DATE"/>
<element name="TRANSACTION_NUMBER" value="TRANSACTION_NUMBER"/>
<element name="TRANSACTION_DATE" value="TRANSACTION_DATE"/>
<element name="ACCOUNTING_SEQUENCE_NAME" value="ACCOUNTING_SEQUENCE_NAME"/>
<element name="ACCOUNTING_SEQUENCE_VERSION" value="ACCOUNTING_SEQUENCE_VERSION"/>
<element name="ACCOUNTING_SEQUENCE_NUMBER" value="ACCOUNTING_SEQUENCE_NUMBER"/>
<element name="REPORTING_SEQUENCE_NAME" value="REPORTING_SEQUENCE_NAME"/>
<element name="REPORTING_SEQUENCE_VERSION" value="REPORTING_SEQUENCE_VERSION"/>
<element name="REPORTING_SEQUENCE_NUMBER" value="REPORTING_SEQUENCE_NUMBER"/>
<element name="DOCUMENT_CATEGORY" value="DOCUMENT_CATEGORY"/>
<element name="DOCUMENT_SEQUENCE_NAME" value="DOCUMENT_SEQUENCE_NAME"/>
<element name="DOCUMENT_SEQUENCE_NUMBER" value="DOCUMENT_SEQUENCE_NUMBER"/>
<element name="APPLICATION_ID" value="APPLICATION_ID"/>
<element name="APPLICATION_NAME" value="APPLICATION_NAME"/>
<element name="HEADER_ID" value="HEADER_ID"/>
<element name="HEADER_DESCRIPTION" value="HEADER_DESCRIPTION"/>
<element name="FUND_STATUS" value="FUND_STATUS"/>
<element name="JE_CATEGORY_NAME" value="JE_CATEGORY_NAME"/>
<element name="JE_SOURCE_NAME" value="JE_SOURCE_NAME"/>
<element name="EVENT_ID" value="EVENT_ID"/>
<element name="EVENT_DATE" value="EVENT_DATE"/>
<element name="EVENT_NUMBER" value="EVENT_NUMBER"/>
<element name="EVENT_CLASS_CODE" value="EVENT_CLASS_CODE"/>
<element name="EVENT_CLASS_NAME" value="EVENT_CLASS_NAME"/>
<element name="EVENT_TYPE_CODE" value="EVENT_TYPE_CODE"/>
<element name="EVENT_TYPE_NAME" value="EVENT_TYPE_NAME"/>
<element name="GL_BATCH_NAME" value="GL_BATCH_NAME"/>
<element name="POSTED_DATE" value="POSTED_DATE"/>
<element name="GL_JE_NAME" value="GL_JE_NAME"/>
<element name="EXTERNAL_REFERENCE" value="EXTERNAL_REFERENCE"/>
<element name="GL_LINE_NUMBER" value="GL_LINE_NUMBER"/>
<element name="LINE_NUMBER" value="LINE_NUMBER"/>
<element name="ORIG_LINE_NUMBER" value="ORIG_LINE_NUMBER"/>
<element name="ACCOUNTING_CLASS_CODE" value="ACCOUNTING_CLASS_CODE"/>
<element name="ACCOUNTING_CLASS_NAME" value="ACCOUNTING_CLASS_NAME"/>
<element name="LINE_DESCRIPTION" value="LINE_DESCRIPTION"/>
<element name="ENTERED_CURRENCY" value="ENTERED_CURRENCY"/>
<element name="CONVERSION_RATE" value="CONVERSION_RATE"/>
<element name="CONVERSION_RATE_DATE" value="CONVERSION_RATE_DATE"/>
<element name="CONVERSION_RATE_TYPE_CODE" value="CONVERSION_RATE_TYPE_CODE"/>
<element name="CONVERSION_RATE_TYPE" value="CONVERSION_RATE_TYPE"/>
<element name="ENTERED_DR" value="ENTERED_DR"/>
<element name="ENTERED_CR" value="ENTERED_CR"/>
<element name="UNROUNDED_ACCOUNTED_DR" value="UNROUNDED_ACCOUNTED_DR"/>
<element name="UNROUNDED_ACCOUNTED_CR" value="UNROUNDED_ACCOUNTED_CR"/>
<element name="ACCOUNTED_DR" value="ACCOUNTED_DR"/>
<element name="ACCOUNTED_CR" value="ACCOUNTED_CR"/>
<element name="STATISTICAL_AMOUNT" value="STATISTICAL_AMOUNT"/>
<element name="RECONCILIATION_REFERENCE" value="RECONCILIATION_REFERENCE"/>
<element name="ATTRIBUTE_CATEGORY" value="ATTRIBUTE_CATEGORY"/>
<element name="ATTRIBUTE1" value="ATTRIBUTE1"/>
<element name="ATTRIBUTE2" value="ATTRIBUTE2"/>
<element name="ATTRIBUTE3" value="ATTRIBUTE3"/>
<element name="ATTRIBUTE4" value="ATTRIBUTE4"/>
<element name="ATTRIBUTE5" value="ATTRIBUTE5"/>
<element name="ATTRIBUTE6" value="ATTRIBUTE6"/>
<element name="ATTRIBUTE7" value="ATTRIBUTE7"/>
<element name="ATTRIBUTE8" value="ATTRIBUTE8"/>
<element name="ATTRIBUTE9" value="ATTRIBUTE9"/>
<element name="ATTRIBUTE10" value="ATTRIBUTE10"/>
<element name="PARTY_TYPE_CODE" value="PARTY_TYPE_CODE"/>
<element name="PARTY_TYPE" value="PARTY_TYPE"/>
<element name="PARTY_NUMBER" value="PARTY_NUMBER"/>
<element name="PARTY_NAME" value="PARTY_NAME"/>
<element name="PARTY_TYPE_TAXPAYER_ID" value="PARTY_TYPE_TAXPAYER_ID"/>
<element name="PARTY_TAX_REGISTRATION_NUMBER" value="PARTY_TAX_REGISTRATION_NUMBER"/>
<element name="PARTY_SITE_NUMBER" value="PARTY_SITE_NUMBER"/>
<element name="PARTY_SITE_NAME" value="PARTY_SITE_NAME"/>
<element name="PARTY_SITE_TYPE_TAXPAYER_ID" value="PARTY_SITE_TYPE_TAXPAYER_ID"/>
<element name="PARTY_SITE_TAX_RGSTN_NUMBER" value="PARTY_SITE_TAX_RGSTN_NUMBER"/>
<element name="USER_TRX_IDENTIFIER_NAME_1" value="USER_TRX_IDENTIFIER_NAME_1"/>
<element name="USER_TRX_IDENTIFIER_VALUE_1" value="USER_TRX_IDENTIFIER_VALUE_1"/>
<element name="USER_TRX_IDENTIFIER_NAME_2" value="USER_TRX_IDENTIFIER_NAME_2"/>
<element name="USER_TRX_IDENTIFIER_VALUE_2" value="USER_TRX_IDENTIFIER_VALUE_2"/>
<element name="USER_TRX_IDENTIFIER_NAME_3" value="USER_TRX_IDENTIFIER_NAME_3"/>
<element name="USER_TRX_IDENTIFIER_VALUE_3" value="USER_TRX_IDENTIFIER_VALUE_3"/>
<element name="USER_TRX_IDENTIFIER_NAME_4" value="USER_TRX_IDENTIFIER_NAME_4"/>
<element name="USER_TRX_IDENTIFIER_VALUE_4" value="USER_TRX_IDENTIFIER_VALUE_4"/>
<element name="USER_TRX_IDENTIFIER_NAME_5" value="USER_TRX_IDENTIFIER_NAME_5"/>
<element name="USER_TRX_IDENTIFIER_VALUE_5" value="USER_TRX_IDENTIFIER_VALUE_5"/>
<element name="USER_TRX_IDENTIFIER_NAME_6" value="USER_TRX_IDENTIFIER_NAME_6"/>
<element name="USER_TRX_IDENTIFIER_VALUE_6" value="USER_TRX_IDENTIFIER_VALUE_6"/>
<element name="USER_TRX_IDENTIFIER_NAME_7" value="USER_TRX_IDENTIFIER_NAME_7"/>
<element name="USER_TRX_IDENTIFIER_VALUE_7" value="USER_TRX_IDENTIFIER_VALUE_7"/>
<element name="USER_TRX_IDENTIFIER_NAME_8" value="USER_TRX_IDENTIFIER_NAME_8"/>
<element name="USER_TRX_IDENTIFIER_VALUE_8" value="USER_TRX_IDENTIFIER_VALUE_8"/>
<element name="USER_TRX_IDENTIFIER_NAME_9" value="USER_TRX_IDENTIFIER_NAME_9"/>
<element name="USER_TRX_IDENTIFIER_VALUE_9" value="USER_TRX_IDENTIFIER_VALUE_9"/>
<element name="USER_TRX_IDENTIFIER_NAME_10" value="USER_TRX_IDENTIFIER_NAME_10"/>
<element name="USER_TRX_IDENTIFIER_VALUE_10" value="USER_TRX_IDENTIFIER_VALUE_10"/>
<group name="TAX" source="Q_TAX">
<element name="TAX_REGIME" value="TAX_REGIME"/>
<element name="TAX" value="TAX"/>
<element name="TAX_NAME" value="TAX_NAME"/>
<element name="TAX_STATUS_NAME" value="TAX_STATUS_NAME"/>
<element name="TAX_RATE_NAME" value="TAX_RATE_NAME"/>
<element name="TAX_RATE" value="TAX_RATE"/>
<element name="TAX_RATE_TYPE_NAME" value="TAX_RATE_TYPE_NAME"/>
<element name="TAX_DETERMINE_DATE" value="TAX_DETERMINE_DATE"/>
<element name="TAX_POINT_DATE" value="TAX_POINT_DATE"/>
<element name="TAX_TYPE_CODE" value="TAX_TYPE_CODE"/>
<element name="TAX_TYPE_NAME" value="TAX_TYPE_NAME"/>
<element name="TAX_CODE" value="TAX_CODE"/>
<element name="TAX_REGISTRATION_NUMBER" value="TAX_REGISTRATION_NUMBER"/>
<element name="TRX_CURRENCY_CODE" value="TRX_CURRENCY_CODE"/>
<element name="TAX_CURRENCY_CODE" value="TAX_CURRENCY_CODE"/>
<element name="TAX_AMOUNT" value="TAX_AMOUNT"/>
<element name="TAX_AMOUNT_TAX_CURRENCY" value="TAX_AMOUNT_TAX_CURRENCY"/>
<element name="TAX_AMOUNT_FUNCTIONAL_CURR" value="TAX_AMOUNT_FUNCTIONAL_CURR"/>
<element name="TAXABLE_AMOUNT" value="TAXABLE_AMOUNT"/>
<element name="TAXABLE_AMOUNT_TAX_CURRENCY" value="TAXABLE_AMOUNT_TAX_CURRENCY"/>
<element name="TAXABLE_AMT_FUNC_CURRENCY" value="TAXABLE_AMT_FUNC_CURRENCY"/>
<element name="UNROUNDED_TAXABLE_AMOUNT" value="UNROUNDED_TAXABLE_AMOUNT"/>
<element name="UNROUNDED_TAX_AMOUNT" value="UNROUNDED_TAX_AMOUNT"/>
<element name="RECOVERABLE_TAX_AMOUNT" value="RECOVERABLE_TAX_AMOUNT"/>
<element name="RECOVERABLE_TAX_AMT_TAX_CURR" value="RECOVERABLE_TAX_AMT_TAX_CURR"/>
<element name="RECOVERABLE_TAX_AMT_FUNC_CURR" value="RECOVERABLE_TAX_AMT_FUNC_CURR"/>
<element name="NON_RECOVERABLE_TAX_AMOUNT" value="NON_RECOVERABLE_TAX_AMOUNT"/>
<element name="NON_REC_TAX_AMT_TAX_CURR" value="NON_REC_TAX_AMT_TAX_CURR"/>
<element name="NON_REC_TAX_AMT_FUNC_CURR" value="NON_REC_TAX_AMT_FUNC_CURR"/>
</group>
</group>
</group>
</group>
<group name="COMMERCIAL" source="Q_COMMERCIAL">
<element name="LEGAL_COMMERCIAL_NUMBER"
value="LEGAL_COMMERCIAL_NUMBER"/>
</group>
<group name="VAT_REGISTRATION" source="Q_VAT_REGISTRATION">
<element name="LEGAL_VAT_REGISTRATION_NUMBER"
value="LEGAL_VAT_REGISTRATION_NUMBER"/>
</group>
</dataStructure>
</dataTemplate>
|
|
|
|
Goto Forum:
Current Time: Wed Apr 24 06:01:36 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
