[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| 29-May-2009 rajose bug#8554433 Insert into _gt query taking |
| long time to execute. |
| 05-Jan-2010 nksurana Added new parameter p_tax_query to handle |
| the tax query in the package so that it is |
| executed only when tax flag is Y |
| 09-Aug-2010 nksurana Removed the clause for zero net period |
| activity from the condition on include |
| zero amounts. |
| 23-Dec-2010 nksurana Added new variables to move the logic from |
| xml to pkb to make the xml reuasable and |
| improve performance. |
| 16-Aug-2011 nksurana Added additional filter in the insert into |
| xla_report_balances_gt when the flag |
| P_INCLUDE_ACCT_WITH_NO_ACT is NULL or N. |
+===========================================================================*/
--=============================================================================
-- **************** declarations ********************
--=============================================================================
TYPE t_array_char IS TABLE OF VARCHAR2(80) INDEX BY BINARY_INTEGER;
'SELECT nvl(xler.registration_number,0) LEGAL_COMMERCIAL_NUMBER
FROM XLE_REGISTRATIONS_V xler
WHERE legislative_category = ''COMMERCIAL_LAW''
AND legal_entity_id = :P_LEGAL_ENTITY_ID';
'select NULL LEGAL_COMMERCIAL_NUMBER from dual where 1>2';
'SELECT zptp.REP_REGISTRATION_NUMBER LEGAL_VAT_REGISTRATION_NUMBER
FROM ZX_PARTY_TAX_PROFILE zptp ,XLE_ETB_PROFILES xetbp
WHERE zptp.PARTY_TYPE_CODE = ''LEGAL_ESTABLISHMENT''
AND xetbp.party_id=zptp.party_id
AND xetbp.MAIN_ESTABLISHMENT_FLAG = ''Y''
AND xetbp.LEGAL_ENTITY_ID = :P_LEGAL_ENTITY_ID' ;
'select NULL LEGAL_VAT_REGISTRATION_NUMBER from dual where 1>2';
'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 ';
'SELECT NULL FROM DUAL WHERE 1=2 ';
highly selective.
*/
--=============================================================================
-- ************** forward declaration *******************
--=============================================================================
--------------------------------------------------------------------------------
-- procedure to create the main SQL
--------------------------------------------------------------------------------
--=============================================================================
-- *********** Local Trace Routine **********
--=============================================================================
C_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
SELECT 'gcck.'||application_column_name seg
FROM fnd_id_flex_segments
WHERE application_id =101
AND id_flex_code ='GL#'
AND id_flex_num = p_coa_id
ORDER BY segment_num ;
SELECT gjst.je_source_name
INTO l_je_source_name
FROM xla_subledgers xls, gl_je_sources_tl gjst
WHERE xls.application_id = p_application_id
AND xls.je_source_name = gjst.je_source_name
AND gjst.language = USERENV('LANG');
SELECT object_type_code, USERENV('LANG')
INTO l_object_type, l_lang
FROM gl_ledgers
WHERE ledger_id = p_ledger_id;
l_ledgers := '(SELECT ledger_id '||
'FROM gl_ledger_set_assignments '||
'WHERE ledger_set_id = :P_LEDGER_ID)';
SELECT ledger_id
INTO l_ledger_id
FROM gl_ledger_set_assignments
WHERE ledger_set_id = p_ledger_id
AND ROWNUM = 1;
SELECT effective_period_num
,START_DATE
INTO l_start_period_num
,l_start_date
FROM gl_period_statuses
WHERE application_id = 101
AND ledger_id = l_ledger_id
AND period_name = p_period_from;
SELECT effective_period_num
,end_date
INTO l_end_period_num
,l_end_date
FROM gl_period_statuses
WHERE application_id = 101
AND ledger_id = l_ledger_id
AND period_name = p_period_to;
SELECT chart_of_accounts_id
INTO l_coa_id
FROM gl_ledgers
WHERE ledger_id = p_ledger_id;
(SELECT aps.segment1
||''|''||aps.vendor_name
||''|''||hzp.jgzz_fiscal_code
||''|''||hzp.tax_reference
||''|''||hps.party_site_number
||''|''||hps.party_site_name
||''|''||NULL
FROM ap_suppliers aps
,ap_supplier_sites_all apss
,hz_parties hzp
,hz_party_sites hps
,xla_ae_lines ael2
WHERE aps.vendor_id = ael2.party_id
AND hzp.party_id = aps.party_id
AND apss.vendor_site_id(+) = ael2.party_site_id
AND hps.party_site_id(+) = apss.party_site_id
AND ael2.application_id = ael.application_id
AND ael2.ae_header_id = ael.ae_header_id
AND ael2.ae_line_num = ael.ae_line_num )
WHEN ( ael.party_type_code = ''C'' AND ael.party_id is not null ) THEN
(SELECT hca.account_number
||''|''||hzp.party_name
||''|''||hzp.jgzz_fiscal_code
||''|''||hzp.tax_reference
||''|''||hps.party_site_number
||''|''||hps.party_site_name
||''|''||hzcu.tax_reference
FROM hz_cust_accounts hca
,hz_cust_acct_sites_all hcas
,hz_cust_site_uses_all hzcu
,hz_parties hzp
,hz_party_sites hps
,xla_ae_lines ael2
WHERE hca.cust_account_id = ael2.party_id
AND hzp.party_id = hca.party_id
AND hzcu.site_use_id(+) = ael2.party_site_id
AND hcas.cust_acct_site_id(+) = hzcu.cust_acct_site_id
AND hps.party_site_id(+) = hcas.party_site_id
AND ael2.application_id = ael.application_id
AND ael2.ae_header_id = ael.ae_header_id
AND ael2.ae_line_num = ael.ae_line_num )
ELSE
NULL
END PARTY_INFO'; --bug 10425976
,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.GL_DOCUMENT_SEQUENCE_NAME GL_DOCUMENT_SEQUENCE_NAME
,TABLE1.GL_DOCUMENT_SEQUENCE_NUMBER GL_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';
'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
,fns1.name GL_DOCUMENT_SEQUENCE_NAME
,gjh.doc_sequence_value GL_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';
'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
,fns.name GL_DOCUMENT_SEQUENCE_NAME
,gjh.doc_sequence_value GL_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';
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)';
SELECT NULL GL_DATE
,NULL CREATED_BY
,NULL CREATION_DATE
,NULL LAST_UPDATE_DATE
,NULL GL_TRANSFER_DATE
,NULL REFERENCE_DATE
,NULL COMPLETED_DATE
,NULL TRANSACTION_NUMBER
,NULL TRANSACTION_DATE
,NULL ACCOUNTING_SEQUENCE_NAME
,NULL ACCOUNTING_SEQUENCE_VERSION
,NULL ACCOUNTING_SEQUENCE_NUMBER
,NULL REPORTING_SEQUENCE_NAME
,NULL REPORTING_SEQUENCE_VERSION
,NULL REPORTING_SEQUENCE_NUMBER
,NULL DOCUMENT_CATEGORY
,NULL DOCUMENT_SEQUENCE_NAME
,NULL DOCUMENT_SEQUENCE_NUMBER
,NULL GL_DOCUMENT_SEQUENCE_NAME -- added bug 9925564 .
,NULL GL_DOCUMENT_SEQUENCE_NUMBER
,NULL APPLICATION_ID
,NULL APPLICATION_NAME
,NULL HEADER_ID
,NULL HEADER_DESCRIPTION
,NULL FUND_STATUS
,NULL JE_CATEGORY_NAME
,NULL 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
,NULL GL_BATCH_NAME
,NULL POSTED_DATE
,NULL GL_JE_NAME
,NULL EXTERNAL_REFERENCE
,NULL GL_LINE_NUMBER
,NULL LINE_NUMBER
,NULL ORIG_LINE_NUMBER
,NULL ACCOUNTING_CLASS_CODE
,NULL ACCOUNTING_CLASS_NAME
,NULL LINE_DESCRIPTION
,NULL ENTERED_CURRENCY
,NULL CONVERSION_RATE
,NULL CONVERSION_RATE_DATE
,NULL CONVERSION_RATE_TYPE_CODE
,NULL CONVERSION_RATE_TYPE
,NULL ENTERED_DR
,NULL ENTERED_CR
,NULL UNROUNDED_ACCOUNTED_DR
,NULL UNROUNDED_ACCOUNTED_CR
,NULL ACCOUNTED_DR
,NULL ACCOUNTED_CR
,NULL STATISTICAL_AMOUNT
,NULL RECONCILIATION_REFERENCE
,NULL ATTRIBUTE_CATEGORY
,NULL ATTRIBUTE1
,NULL ATTRIBUTE2
,NULL ATTRIBUTE3
,NULL ATTRIBUTE4
,NULL ATTRIBUTE5
,NULL ATTRIBUTE6
,NULL ATTRIBUTE7
,NULL ATTRIBUTE8
,NULL ATTRIBUTE9
,NULL 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 xla_report_balances_gt glbgt
WHERE nvl(period_net_dr,0) = 0
AND nvl(period_net_cr,0) = 0
AND (nvl(begin_balance_dr,0) - nvl(begin_balance_cr,0) ) <> 0';
INSERT INTO xla_report_balances_gt
(ledger_id
,ledger_short_name
,ledger_description
,ledger_name
,ledger_currency
,period_year
,period_number
,period_name
,period_start_date
,period_end_date
,balance_type_code
,balance_type
,budget_version_id
,budget_name
,encumbrance_type_id
,encumbrance_type
,begin_balance_dr
,begin_balance_cr
,period_net_dr
,period_net_cr
,code_combination_id
,accounting_code_combination
,code_combination_description
,control_account_flag
,control_account
,balancing_segment
,natural_account_segment
,cost_center_segment
,management_segment
,intercompany_segment
,balancing_segment_desc
,natural_account_desc
,cost_center_desc
,management_segment_desc
,intercompany_segment_desc
,segment1
,segment2
,segment3
,segment4
,segment5
,segment6
,segment7
,segment8
,segment9
,segment10
,segment11
,segment12
,segment13
,segment14
,segment15
,segment16
,segment17
,segment18
,segment19
,segment20
,segment21
,segment22
,segment23
,segment24
,segment25
,segment26
,segment27
,segment28
,segment29
,segment30
,legal_entity_id
,legal_entity_name
,le_address_line_1
,le_address_line_2
,le_address_line_3
,le_city
,le_region_1
,le_region_2
,le_region_3
,le_postal_code
,le_country
,le_registration_number
,le_registration_effective_from
,le_br_daily_inscription_number
,le_br_daily_inscription_date
,le_br_daily_entity
,le_br_daily_location
,le_br_director_number
,le_br_accountant_number
,le_br_accountant_name)
(
SELECT TABLE1.LEDGER_ID
,TABLE1.LEDGER_SHORT_NAME
,TABLE1.LEDGER_DESCRIPTION
,TABLE1.LEDGER_NAME
,TABLE1.LEDGER_CURRENCY
,TABLE1.PERIOD_YEAR
,TABLE1.PERIOD_NUMBER
,TABLE1.PERIOD_NAME
,TABLE1.PERIOD_START_DATE
,TABLE1.PERIOD_END_DATE
,TABLE1.BALANCE_TYPE_CODE
,TABLE1.BALANCE_TYPE
,TABLE1.BUDGET_VERSION_ID
,TABLE1.BUDGET_NAME
,TABLE1.ENCUMBRANCE_TYPE_ID
,TABLE1.ENCUMBRANCE_TYPE
,TABLE1.BEGIN_BALANCE_DR
,TABLE1.BEGIN_BALANCE_CR
,TABLE1.PERIOD_NET_DR
,TABLE1.PERIOD_NET_CR
,TABLE1.CODE_COMBINATION_ID
,TABLE1.ACCOUNTING_CODE_COMBINATION
,TABLE1.CODE_COMBINATION_DESCRIPTION
,TABLE1.CONTROL_ACCOUNT_FLAG
,TABLE1.CONTROL_ACCOUNT
,TABLE1.BALANCING_SEGMENT
,TABLE1.NATURAL_ACCOUNT_SEGMENT
,TABLE1.COST_CENTER_SEGMENT
,TABLE1.MANAGEMENT_SEGMENT
,TABLE1.INTERCOMPANY_SEGMENT
,TABLE1.BALANCING_SEGMENT_DESC
,TABLE1.NATURAL_ACCOUNT_DESC
,TABLE1.COST_CENTER_DESC
,TABLE1.MANAGEMENT_SEGMENT_DESC
,TABLE1.INTERCOMPANY_SEGMENT_DESC
,TABLE1.SEGMENT1
,TABLE1.SEGMENT2
,TABLE1.SEGMENT3
,TABLE1.SEGMENT4
,TABLE1.SEGMENT5
,TABLE1.SEGMENT6
,TABLE1.SEGMENT7
,TABLE1.SEGMENT8
,TABLE1.SEGMENT9
,TABLE1.SEGMENT10
,TABLE1.SEGMENT11
,TABLE1.SEGMENT12
,TABLE1.SEGMENT13
,TABLE1.SEGMENT14
,TABLE1.SEGMENT15
,TABLE1.SEGMENT16
,TABLE1.SEGMENT17
,TABLE1.SEGMENT18
,TABLE1.SEGMENT19
,TABLE1.SEGMENT20
,TABLE1.SEGMENT21
,TABLE1.SEGMENT22
,TABLE1.SEGMENT23
,TABLE1.SEGMENT24
,TABLE1.SEGMENT25
,TABLE1.SEGMENT26
,TABLE1.SEGMENT27
,TABLE1.SEGMENT28
,TABLE1.SEGMENT29
,TABLE1.SEGMENT30
$legal_entity_columns$
FROM
(SELECT $hint$
gl1.ledger_id LEDGER_ID
,gl1.short_name LEDGER_SHORT_NAME
,gl1.description LEDGER_DESCRIPTION
,gl1.NAME LEDGER_NAME
,glb.currency_code LEDGER_CURRENCY
,glb.period_year PERIOD_YEAR
,glb.period_num PERIOD_NUMBER
,glb.period_name PERIOD_NAME
,gl1.START_DATE PERIOD_START_DATE
,gl1.end_date PERIOD_END_DATE
,glb.actual_flag BALANCE_TYPE_CODE
,xlk.meaning BALANCE_TYPE
,glb.budget_version_id BUDGET_VERSION_ID
,glv.budget_name BUDGET_NAME
,glb.encumbrance_type_id ENCUMBRANCE_TYPE_ID
,get.encumbrance_type ENCUMBRANCE_TYPE
,NVL(glb.begin_balance_dr,0) BEGIN_BALANCE_DR
,NVL(glb.begin_balance_cr,0) BEGIN_BALANCE_CR
,NVL(glb.period_net_dr,0) PERIOD_NET_DR
,NVL(glb.period_net_cr,0) PERIOD_NET_CR
,glb.code_combination_id CODE_COMBINATION_ID
,$concat_segments$ ACCOUNTING_CODE_COMBINATION
,xla_report_utility_pkg.get_ccid_desc
(gl1.chart_of_accounts_id
,glb.code_combination_id) CODE_COMBINATION_DESCRIPTION
,gcck.reference3 CONTROL_ACCOUNT_FLAG
,NULL CONTROL_ACCOUNT
$seg_desc_column$
,gcck.segment1 SEGMENT1
,gcck.segment2 SEGMENT2
,gcck.segment3 SEGMENT3
,gcck.segment4 SEGMENT4
,gcck.segment5 SEGMENT5
,gcck.segment6 SEGMENT6
,gcck.segment7 SEGMENT7
,gcck.segment8 SEGMENT8
,gcck.segment9 SEGMENT9
,gcck.segment10 SEGMENT10
,gcck.segment11 SEGMENT11
,gcck.segment12 SEGMENT12
,gcck.segment13 SEGMENT13
,gcck.segment14 SEGMENT14
,gcck.segment15 SEGMENT15
,gcck.segment16 SEGMENT16
,gcck.segment17 SEGMENT17
,gcck.segment18 SEGMENT18
,gcck.segment19 SEGMENT19
,gcck.segment20 SEGMENT20
,gcck.segment21 SEGMENT21
,gcck.segment22 SEGMENT22
,gcck.segment23 SEGMENT23
,gcck.segment24 SEGMENT24
,gcck.segment25 SEGMENT25
,gcck.segment26 SEGMENT26
,gcck.segment27 SEGMENT27
,gcck.segment28 SEGMENT28
,gcck.segment29 SEGMENT29
,gcck.segment30 SEGMENT30
FROM (SELECT /*+ no_merge */
gll.ledger_id
,gll.short_name
,gll.description
,gll.name
,gll.currency_code
,gll.chart_of_accounts_id
,gls.period_name
,gls.start_date
,gls.end_date
FROM gl_ledgers gll
,gl_period_statuses gls
WHERE gls.ledger_id = gll.ledger_id
AND gls.application_id = 101
AND gls.effective_period_num BETWEEN :P_START_PERIOD_NUM AND :P_END_PERIOD_NUM
AND gll.ledger_id IN $ledger_id$
) gl1
,gl_balances glb
,gl_code_combinations gcck
,xla_lookups xlk
,gl_budget_versions glv
,gl_encumbrance_types get
$seg_desc_from$
WHERE glb.ledger_id = gl1.ledger_id
AND glb.currency_code $statistical$
AND glb.period_name = gl1.period_name
AND glb.template_id IS null
AND gcck.code_combination_id = glb.code_combination_id
AND gcck.chart_of_accounts_id = gl1.chart_of_accounts_id --12329939
AND xlk.lookup_type = ''XLA_BALANCE_TYPE''
AND xlk.lookup_code = glb.actual_flag
AND glv.budget_version_id(+) = glb.budget_version_id
AND get.encumbrance_type_id(+) = glb.encumbrance_type_id
$seg_desc_join$
$other_param_filter$) TABLE1
$legal_entity_from$
WHERE 1 = 1
$legal_entity_join$
)' ;
UPDATE xla_report_balances_gt xrb
SET (begin_running_total_cr
,begin_running_Total_dr
,end_running_total_cr
,end_running_total_dr) =
(SELECT SUM(CASE
WHEN gjl.effective_date < p_gl_date_from THEN
accounted_cr
ELSE
0
END ) BEGIN_RUNNING_TOTAL_CR
, SUM(CASE
WHEN gjl.effective_date < p_gl_date_from THEN
accounted_dr
ELSE
0
END ) BEGIN_RUNNING_TOTAL_DR
,SUM(CASE
WHEN gjl.effective_date > p_gl_date_to THEN
accounted_cr
ELSE
0
END ) END_RUNNING_TOTAL_CR
,SUM(CASE
WHEN gjl.effective_date > p_gl_date_to THEN
accounted_dr
ELSE
0
END ) END_RUNNING_TOTAL_DR
FROM gl_je_headers gjh
,gl_je_lines gjl
WHERE gjh.je_header_id = gjl.je_header_id
AND gjh.status = 'P'
AND gjl.status = 'P' -- Bug 9668652
AND gjh.ledger_id = xrb.ledger_id
AND gjl.period_name = xrb.period_name -- Bug 9668652
AND gjh.actual_flag = xrb.balance_type_code
AND gjl.code_combination_id = xrb.code_combination_id
)
WHERE xrb.period_name IN (p_period_from,p_period_to);