[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| 12/30/2005 V. Kumar Modified code to select all event classes |
| for a transaction veiw. |
| 01/05/2005 V. Kumar Bug:4928256 Added missing column for Tax |
| info and Legal entity info |
| 01/19/2006 V. Swapna Bug 4755531. Modified the code to |
| calculate start and end dates for a period.|
| 01/20/2006 V. Swapna Bug 4725878. Added filter conditions to gl |
| and sla queries based on gl_batch_name. |
| 01/20/2006 S. Singhania Bug 4755531: Fixed SQLs |
| 01/30/2006 V. Swapna Bug 5000609: Add an outer join while |
| joining to party_type_code column in |
| joining to party_type_code column in |
| xla_ae_lines |
| 02/24/2006 V. Swapna Bug 5059634: Change a column name while |
| building parmaeter filter for gl |
| 03/31/2006 V. Swapna Bug 5097723: Correct a join condition of |
| gl_je_headers to fnd_sequences. Also,moved |
| statement populating p_party_details_col |
| from get_sla_query into beforeReport. |
| 04/03/2006 V. Swapna Bug 5122286: Correct the range paramters. |
| 04/23/2006 A. Wan 5072266 - replace po_vendors with |
| ap_suppliers |
| 04/26/2006 V. Kumar Bug 5127831: Modified constant C_TAX_QUERY |
| 06/06/2007 G.Praveen Bug 5895067: Added Code to fetch data from |
| reference_1 ,reference_4 from gl_je_lines |
| table and default_effective_date from |
| gl_je_batches table |
| 17-Apr-2008 rajose bug#6978940 changed the where clause for |
| p_include_zero_amount_flag from >0 to <> 0 |
| 16-Feb-2009 nksurana Instead of function calling new procedure |
| xla_report_utility_pkg.get_transaction_id |
| 12-Mar-2009 nksurana Added p_period_type to filter based on |
| whether period is Adjustment/Normal. |
| 11-Mar-2009 rajose bug#7834671 Journal Entries report showing |
| no data. |
| 20-Mar-2009 nksurana Added P_TRX_NUM_FROM,P_TRX_NUM_TO to filter|
| based on transaction number range.Also |
| modified filter on Post Acct. Program to |
| pick only the enabled Assignment. |
| 18-May-2009 VGOPISET 8250215: changed the value set of parameter|
| P_POSTING_STATUS_CODE to have values like |
| Posted, Not Posted, Transferred, Not Trans |
| ferred and show data accordingly |
| 10-Aug-2009 NKSURANA 8683445: Building new queries in the Report|
| based on the flag P_CUSTOM_QUERY_FLAG |
| 8638951: Added new column ORIG_LINE_NUMBER |
| in the extract to fetch the correct |
| AE_LINE_NUM for the TAX Query. |
| 08-Jun-2011 VGOPISET 12571692: Report to run when filtered by |
| Third Party Supplier Name. |
| 26-Jul-2011 NKSURANA 12663084: When report is filtered by Event |
| Class, also added filter on event_type_code|
| for better performance. |
+===========================================================================*/
--=============================================================================
-- **************** declarations ********************
--=============================================================================
-------------------------------------------------------------------------------
-- constant for getting flexfield segment value description
-------------------------------------------------------------------------------
C_SEG_DESC_JOIN CONSTANT VARCHAR2(1000) :=
' AND $alias$.flex_value_set_id = $flex_value_set_id$ AND '||
' $alias$.flex_value = $segment_column$ ';
'SELECT
nvl(sum(nvl(gll.accounted_dr,0)),0) YTD_ACTIVITY_DR
,nvl(sum(nvl(gll.accounted_cr,0)),0) YTD_ACTIVITY_CR
FROM
gl_je_headers glh
,gl_je_lines gll
,gl_ledgers glg
,gl_periods glp
,gl_code_combinations_kfv gcck
WHERE glg.ledger_id IN (:P_LEDGER_ID)
AND glh.period_name IN ( select distinct Period_name from gl_period_statuses where
ledger_id = :P_LEDGER_ID and period_num <
(select distinct Period_num from gl_period_statuses
where ledger_id = :P_LEDGER_ID and period_name = :P_PERIOD_FROM)
and period_year in
(select distinct Period_year from gl_period_statuses
where ledger_id = :P_LEDGER_ID and period_name = :P_PERIOD_FROM)
)
AND glh.ledger_id = glg.ledger_id
AND gll.je_header_id = glh.je_header_id
AND glp.period_name = glh.period_name
AND glp.period_set_name = glg.period_set_name
AND gcck.code_combination_id = gll.code_combination_id
AND glh.status = ''P''
AND glh.currency_code <> ''STAT'''; --added gcck for bug 12586551
'SELECT
0 YTD_ACTIVITY_DR,
0 YTD_ACTIVITY_CR
FROM
DUAL';
'SELECT /*+ index(xdl, XLA_DISTRIBUTION_LINKS_N3) */
zxl.tax_line_id TAX_LINE_ID
,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
,zxl.tax_jurisdiction_code TAX_JURISDICTION_CODE
,zxl.self_assessed_flag SELF_ASSESSED_FLAG
,zxl.hq_estb_reg_number HQ_ESTB_REG_NUMBER
,zrnd.rec_nrec_tax_dist_id REC_NREC_TAX_DIST_ID
,zrnd.recovery_type_code RECOVERY_TYPE_CODE
,zrnd.recovery_rate_code RECOVERY_RATE_CODE
,zrnd.rec_nrec_rate REC_NREC_RATE
,zrnd.recoverable_flag RECOVERABLE_FLAG
,zrnd.rec_nrec_tax_amt REC_NREC_TAX_AMT
,zrnd.rec_nrec_tax_amt_tax_curr REC_NREC_TAX_AMT_TAX_CURR
,zrnd.rec_nrec_tax_amt_funcl_curr REC_NREC_TAX_AMT_FUNCL_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
,zx_rec_nrec_dist zrnd
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
AND xdl.tax_rec_nrec_dist_ref_id = zrnd.rec_nrec_tax_dist_id(+)';
'SELECT NULL TAX_LINE_ID
,NULL TAX_REGIME
,NULL TAX
,NULL TAX_NAME
,NULL TAX_STATUS_NAME
,NULL TAX_RATE_NAME
,NULL TAX_RATE
,NULL TAX_RATE_TYPE_NAME
,NULL TAX_DETERMINE_DATE
,NULL TAX_POINT_DATE
,NULL TAX_TYPE_CODE
,NULL TAX_TYPE_NAME
,NULL TAX_CODE
,NULL TAX_REGISTRATION_NUMBER
,NULL TRX_CURRENCY_CODE
,NULL TAX_CURRENCY_CODE
,NULL TAX_AMOUNT
,NULL TAX_AMOUNT_TAX_CURRENCY
,NULL TAX_AMOUNT_FUNCTIONAL_CURR
,NULL TAXABLE_AMOUNT
,NULL TAXABLE_AMOUNT_TAX_CURRENCY
,NULL TAXABLE_AMT_FUNC_CURRENCY
,NULL UNROUNDED_TAXABLE_AMOUNT
,NULL UNROUNDED_TAX_AMOUNT
,NULL RECOVERABLE_TAX_AMOUNT
,NULL RECOVERABLE_TAX_AMT_TAX_CURR
,NULL RECOVERABLE_TAX_AMT_FUNC_CURR
,NULL NON_RECOVERABLE_TAX_AMOUNT
,NULL NON_REC_TAX_AMT_TAX_CURR
,NULL NON_REC_TAX_AMT_FUNC_CURR
,NULL TAX_JURISDICTION_CODE
,NULL SELF_ASSESSED_FLAG
,NULL HQ_ESTB_REG_NUMBER
,NULL REC_NREC_TAX_DIST_ID
,NULL RECOVERY_TYPE_CODE
,NULL RECOVERY_RATE_CODE
,NULL REC_NREC_RATE
,NULL RECOVERABLE_FLAG
,NULL REC_NREC_TAX_AMT
,NULL REC_NREC_TAX_AMT_TAX_CURR
,NULL REC_NREC_TAX_AMT_FUNCL_CURR
FROM DUAL
WHERE 1>2';
'select last_name||first_name LEGAL_CREATED_BY
from hr_employees
where employee_id =
(
select employee_id
from fnd_user
where user_id = :LEGAL_CREATED_ID
)';
'select NULL LEGAL_CREATED_BY from dual where 1>2';
'select last_name||first_name LEGAL_POSTED_BY
from hr_employees
where employee_id =
(
select employee_id
from fnd_user
where user_id = :LEGAL_POSTED_ID
)';
'select NULL LEGAL_POSTED_BY from dual where 1>2';
'select last_name||first_name LEGAL_APPROVED_BY
from hr_employees
where employee_id =
(
select employee_id
from fnd_user
where user_name =
(
select d.TEXT_VALUE
from wf_items t
,wf_item_attribute_values d
where d.item_key = t.item_key
and d.name = ''APPROVER_NAME''
and t.user_key = :GL_BATCH_NAME
AND d.item_type=''GLBATCH''
and t.begin_date in (select max(it.begin_date)
from wf_items it
,wf_item_attribute_values t1
,wf_item_attribute_values t
where it.user_key = :GL_BATCH_NAME
and it.item_key = t.item_key
and t1.item_type = ''GLBATCH''
and t1.item_key = t.item_key
and t.ITEM_TYPE = ''GLBATCH''
AND t.NAME = ''BATCH_NAME''
and t.text_value = :GL_BATCH_NAME
and t1.name = ''PERIOD_NAME''
and t1.text_value = :PERIOD_NAME)
)
)';
'select NULL LEGAL_APPROVED_BY from dual where 1>2';
'SELECT xler.registration_number 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(aeh XLA_AE_HEADERS_N5) no_index(ael MIS_XLA_AE_LINES_N1) */
to_char(aeh.accounting_date
,''YYYY-MM-DD'') GL_DATE
,fdu.user_name CREATED_BY
,aeh.created_by LEGAL_CREATED_ID
,gjb.posted_by LEGAL_POSTED_ID
,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
,null EXTERNAL_REFERENCE
,null REFERENCE_1
,null REFERENCE_4
,glp.period_year PERIOD_YEAR
,'''||g_period_year_start_date||''' PERIOD_YEAR_START_DATE
,'''||g_period_year_end_date||''' PERIOD_YEAR_END_DATE
,glp.period_num PERIOD_NUMBER
,aeh.period_name PERIOD_NAME
,to_char(glp.start_date
,''YYYY-MM-DD'') PERIOD_START_DATE
,to_char(glp.end_date
,''YYYY-MM-DD'') PERIOD_END_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.sequence_name DOCUMENT_SEQUENCE_NAME -- Bug 7043803 - Fetching sequence from FND_DOCUMENT_SEQUENCES
,fns.name DOCUMENT_SEQUENCE_NAME
,aeh.doc_sequence_value DOCUMENT_SEQUENCE_NUMBER
,aeh.application_id APPLICATION_ID
,fap.application_name APPLICATION_NAME
,aeh.ledger_id LEDGER_ID
,glg.short_name LEDGER_SHORT_NAME
,glg.description LEDGER_DESCRIPTION
,glg.NAME LEDGER_NAME
,glg.currency_code LEDGER_CURRENCY
,aeh.ae_header_id HEADER_ID
,aeh.description HEADER_DESCRIPTION
,xlk1.meaning JOURNAL_ENTRY_STATUS
,xlk2.meaning TRANSFER_TO_GL_STATUS
,aeh.balance_type_code BALANCE_TYPE_CODE
,xlk3.meaning BALANCE_TYPE
,glb.budget_name BUDGET_NAME
,get.encumbrance_type ENCUMBRANCE_TYPE
,xlk4.meaning FUND_STATUS
,gjct.user_je_category_name JE_CATEGORY_NAME
,gjst.user_je_source_name JE_SOURCE_NAME ';
(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';
/*FOR c1 in (select accounting_class_code
from xla_acct_class_assgns xac, xla_post_acct_progs_b xpa
where xpa.rowid = p_post_acct_program_rowid
and xac.program_owner_code = xpa.program_owner_code
and xac.program_code = xpa.program_code
)*/ -- Changed for bug 8337868
FOR c1 in (select accounting_class_code
from xla_acct_class_assgns xac, xla_post_acct_progs_b xpa,
xla_assignment_defns_b xad
where xpa.rowid = p_post_acct_program_rowid
and xac.program_owner_code = xpa.program_owner_code
and xac.program_code = xpa.program_code
and xad.program_code = xac.program_code
and xad.program_owner_code = xac.program_owner_code
and xad.assignment_code = xac.assignment_code
and xad.assignment_owner_code = xac.assignment_owner_code
and xad.enabled_flag = 'Y'
-- and nvl(xad.ledger_id, p_ledger_id) = p_ledger_id Removing this as not required
)
LOOP
l_post_programs := l_post_programs||
','''||c1.accounting_class_code||'''';
FOR c1 in (SELECT DISTINCT event_class_code
FROM xla_event_class_attrs
WHERE application_id = g_je_source_application_id
AND reporting_view_name = p_transaction_view
)
LOOP
l_event_classes := l_event_classes||
','''||c1.event_class_code||'''';
FOR c1 in (SELECT DISTINCT xet.event_type_code
FROM xla_event_class_attrs xeca, xla_event_types_b xet
WHERE xeca.application_id = g_je_source_application_id
AND xeca.reporting_view_name = p_transaction_view
AND xeca.event_class_code = xet.event_class_code
AND xeca.entity_code = xet.entity_code
AND xeca.application_id = xet.application_id
)
LOOP
l_event_types := l_event_types||
','''||c1.event_type_code||'''';
SELECT ' AND xet.event_class_code = '''||event_class_code||''' '
INTO l_event_classes
FROM xla_event_classes_b
WHERE rowid = p_event_class_rowid;
FOR c1 in (SELECT DISTINCT xet.event_type_code
FROM xla_event_classes_b xec, xla_event_types_b xet
WHERE xec.rowid = p_event_class_rowid
AND xec.event_class_code = xet.event_class_code
AND xec.entity_code = xet.entity_code
AND xec.application_id = xet.application_id
)
LOOP
l_event_types := l_event_types||
','''||c1.event_type_code||'''';
FOR c1 in (SELECT DISTINCT event_class_code
FROM xla_event_class_grps_b a
,xla_event_class_attrs b
WHERE a.application_id = b.application_id
AND a.event_class_group_code = b.event_class_group_code
AND a.rowid = p_process_category_rowid
)
LOOP
l_event_classes := l_event_classes||
','''||c1.event_class_code||'''';
FOR c1 in (SELECT DISTINCT xet.event_type_code
FROM xla_event_class_grps_b xecg
, xla_event_class_attrs xeca
, xla_event_types_b xet
WHERE xecg.application_id = xeca.application_id
AND xecg.event_class_group_code = xeca.event_class_group_code
AND xecg.rowid = p_process_category_rowid
AND xeca.event_class_code = xet.event_class_code
AND xeca.entity_code = xet.entity_code
AND xeca.application_id = xet.application_id
)
LOOP
l_event_types := l_event_types||
','''||c1.event_type_code||'''';
'SELECT /*+ leading(glg glp gcck) */ ';
'SELECT /*+ leading(glg glp) */ ';
,to_char(glh.last_update_date
,''YYYY-MM-DD'') LAST_UPDATE_DATE
,NULL GL_TRANSFER_DATE
,to_char(glh.reference_date
,''YYYY-MM-DD'') REFERENCE_DATE
,NULL COMPLETED_DATE
,glh.external_reference EXTERNAL_REFERENCE
,gll.reference_1 REFERENCE_1
,gll.reference_4 REFERENCE_4
,glp.period_year PERIOD_YEAR
,'''||g_period_year_start_date||''' PERIOD_YEAR_START_DATE
,'''||g_period_year_end_date||''' PERIOD_YEAR_END_DATE
,glp.period_num PERIOD_NUMBER
,glh.period_name PERIOD_NAME
,to_char(glp.start_date
,''YYYY-MM-DD'') PERIOD_START_DATE
,to_char(glp.end_date
,''YYYY-MM-DD'') PERIOD_END_DATE
,NULL TRANSACTION_NUMBER
,NULL TRANSACTION_DATE
,fsv1.header_name ACCOUNTING_SEQUENCE_NAME
,fsv1.version_name ACCOUNTING_SEQUENCE_VERSION
,glh.posting_acct_seq_value ACCOUNTING_SEQUENCE_NUMBER
,fsv2.header_name REPORTING_SEQUENCE_NAME
,fsv2.version_name REPORTING_SEQUENCE_VERSION
,glh.close_acct_seq_value REPORTING_SEQUENCE_NUMBER
,NULL DOCUMENT_CATEGORY
,NULL DOCUMENT_SEQUENCE_NAME
,NULL DOCUMENT_SEQUENCE_NUMBER
,NULL APPLICATION_ID
,NULL APPLICATION_NAME
,glh.ledger_id LEDGER_ID
,glg.short_name LEDGER_SHORT_NAME
,glg.description LEDGER_DESCRIPTION
,glg.NAME LEDGER_NAME
,glg.currency_code LEDGER_CURRENCY
,glh.je_header_id HEADER_ID
,glh.description HEADER_DESCRIPTION
,NULL JOURNAL_ENTRY_STATUS
,NULL TRANSFER_TO_GL_STATUS
,glh.actual_flag BALANCE_TYPE_CODE
,xlk.meaning BALANCE_TYPE
,gbv.budget_name BUDGET_NAME
,get.encumbrance_type ENCUMBRANCE_TYPE
,NULL FUND_STATUS
,gjct.user_je_category_name JE_CATEGORY_NAME
,gjst.user_je_source_name JE_SOURCE_NAME --bug12408239
,gjb.NAME GL_BATCH_NAME
,gjb.default_effective_date GL_DEFAULT_EFFECTIVE_DATE
,glk2.meaning GL_BATCH_STATUS
,to_char(glh.posted_date
,''YYYY-MM-DD'') POSTED_DATE
,glh.NAME GL_JE_NAME
-- ,fsq.sequence_name GL_DOC_SEQUENCE_NAME -- krsankar - Commented as part of Bug 7153425
,fsq.name GL_DOC_SEQUENCE_NAME
,glh.doc_sequence_value GL_DOC_SEQUENCE_VALUE
,gll.je_line_num GL_LINE_NUMBER
,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
,gll.je_line_num LINE_NUMBER
,gll.je_line_num ORIG_LINE_NUMBER
,NULL ACCOUNTING_CLASS_CODE
,NULL ACCOUNTING_CLASS_NAME
,gll.description LINE_DESCRIPTION
,gll.code_combination_id CODE_COMBINATION_ID
,gcck.concatenated_segments ACCOUNTING_CODE_COMBINATION
,xla_report_utility_pkg.get_ccid_desc(glg.chart_of_accounts_id
, gll.code_combination_id)
CODE_COMBINATION_DESCRIPTION
,gcck.gl_control_account CONTROL_ACCOUNT_FLAG
,glh.currency_code ENTERED_CURRENCY
,glh.currency_conversion_rate CONVERSION_RATE
,to_char(glh.currency_conversion_date
,''YYYY-MM-DD'') CONVERSION_RATE_DATE
,glh.currency_conversion_type CONVERSION_RATE_TYPE_CODE
,gdct.user_conversion_type CONVERSION_RATE_TYPE
,gll.entered_dr ENTERED_DR
,gll.entered_cr ENTERED_CR
,NULL UNROUNDED_ACCOUNTED_DR
,NULL UNROUNDED_ACCOUNTED_CR
,gll.accounted_dr ACCOUNTED_DR
,gll.accounted_cr ACCOUNTED_CR
,gll.stat_amount STATISTICAL_AMOUNT
,gll.jgzz_recon_ref_11i RECONCILIATION_REFERENCE
,gll.CONTEXT ATTRIBUTE_CATEGORY
,gll.attribute1 ATTRIBUTE1
,gll.attribute2 ATTRIBUTE2
,gll.attribute3 ATTRIBUTE3
,gll.attribute4 ATTRIBUTE4
,gll.attribute5 ATTRIBUTE5
,gll.attribute6 ATTRIBUTE6
,gll.attribute7 ATTRIBUTE7
,gll.attribute8 ATTRIBUTE8
,gll.attribute9 ATTRIBUTE9
,gll.attribute10 ATTRIBUTE10
,NULL PARTY_TYPE_CODE
,NULL PARTY_TYPE ';
l_select_str VARCHAR2(4000);
SELECT display_flag
FROM fnd_id_flex_segments fid
WHERE application_id = p_application_id
AND id_flex_code = p_id_flex_code
AND id_flex_num = p_id_flex_num
AND application_column_name = p_segment_code;
SELECT application_id
INTO g_je_source_application_id
FROM xla_subledgers
WHERE je_source_name = p_je_source;
SELECT object_type_code
INTO l_object_type
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 period_year, effective_period_num
INTO l_period_year, g_start_period_num
FROM gl_period_statuses
WHERE application_id = 101
AND set_of_books_id = l_ledger_id
AND period_name = p_period_from;
SELECT effective_period_num
INTO g_end_period_num
FROM gl_period_statuses
WHERE application_id = 101
AND ledger_id = l_ledger_id
AND period_name = p_period_to;
SELECT TO_CHAR(MIN(start_date),'YYYY-MM-DD')
,TO_CHAR(MAX(end_date),'YYYY-MM-DD')
INTO g_period_year_start_date
,g_period_year_end_date
FROM gl_period_statuses
WHERE application_id = 101
AND set_of_books_id = l_ledger_id
AND period_year = l_period_year
AND adjustment_period_flag = 'N';
p_custom_header_query := 'SELECT NULL FROM DUAL WHERE 1=2';
p_custom_line_query := 'SELECT NULL FROM DUAL WHERE 1=2';
FOR c1 IN (select user_sequence,column_name from xla_event_mappings_b
where application_id = g_je_source_application_id
and event_class_code in
(select event_class_code
from xla_event_class_attrs
where application_id = g_je_source_application_id
and reporting_view_name = p_transaction_view
and rownum = 1
)
and column_name in (p_user_trx_id_column_1
,p_user_trx_id_column_2
,p_user_trx_id_column_3
,p_user_trx_id_column_4
,p_user_trx_id_column_5)
order by user_sequence
)
LOOP
CASE c1.column_name
WHEN p_user_trx_id_column_1 THEN
l_user_trx_value := p_user_trx_id_value_1;
,TABLE1.LAST_UPDATE_DATE LAST_UPDATE_DATE
,TABLE1.GL_TRANSFER_DATE GL_TRANSFER_DATE
,TABLE1.REFERENCE_DATE REFERENCE_DATE
,TABLE1.COMPLETED_DATE COMPLETED_DATE
,TABLE1.EXTERNAL_REFERENCE EXTERNAL_REFERENCE
,TABLE1.REFERENCE_1 REFERENCE_1
,TABLE1.REFERENCE_4 REFERENCE_4
,TABLE1.PERIOD_YEAR PERIOD_YEAR
,TABLE1.PERIOD_YEAR_START_DATE PERIOD_YEAR_START_DATE
,TABLE1.PERIOD_YEAR_END_DATE PERIOD_YEAR_END_DATE
,TABLE1.PERIOD_NUMBER PERIOD_NUMBER
,TABLE1.PERIOD_NAME PERIOD_NAME
,TABLE1.PERIOD_START_DATE PERIOD_START_DATE
,TABLE1.PERIOD_END_DATE PERIOD_END_DATE
,TABLE1.TRANSACTION_NUMBER TRANSACTION_NUMBER
,TABLE1.TRANSACTION_DATE TRANSACTION_DATE
,TABLE1.ACCOUNTING_SEQUENCE_NAME ACCOUNTING_SEQUENCE_NAME
,TABLE1.ACCOUNTING_SEQUENCE_VERSION ACCOUNTING_SEQUENCE_VERSION
,TABLE1.ACCOUNTING_SEQUENCE_NUMBER ACCOUNTING_SEQUENCE_NUMBER
,TABLE1.REPORTING_SEQUENCE_NAME REPORTING_SEQUENCE_NAME
,TABLE1.REPORTING_SEQUENCE_VERSION REPORTING_SEQUENCE_VERSION
,TABLE1.REPORTING_SEQUENCE_NUMBER REPORTING_SEQUENCE_NUMBER
,TABLE1.DOCUMENT_CATEGORY DOCUMENT_CATEGORY
,TABLE1.DOCUMENT_SEQUENCE_NAME DOCUMENT_SEQUENCE_NAME
,TABLE1.DOCUMENT_SEQUENCE_NUMBER DOCUMENT_SEQUENCE_NUMBER
,TABLE1.APPLICATION_ID APPLICATION_ID
,TABLE1.APPLICATION_NAME APPLICATION_NAME
,TABLE1.LEDGER_ID LEDGER_ID
,TABLE1.LEDGER_SHORT_NAME LEDGER_SHORT_NAME
,TABLE1.LEDGER_DESCRIPTION LEDGER_DESCRIPTION
,TABLE1.LEDGER_NAME LEDGER_NAME
,TABLE1.LEDGER_CURRENCY LEDGER_CURRENCY
,TABLE1.HEADER_ID HEADER_ID
,TABLE1.HEADER_DESCRIPTION HEADER_DESCRIPTION
,TABLE1.JOURNAL_ENTRY_STATUS JOURNAL_ENTRY_STATUS
,TABLE1.TRANSFER_TO_GL_STATUS TRANSFER_TO_GL_STATUS
,TABLE1.BALANCE_TYPE_CODE BALANCE_TYPE_CODE
,TABLE1.BALANCE_TYPE BALANCE_TYPE
,TABLE1.BUDGET_NAME BUDGET_NAME
,TABLE1.ENCUMBRANCE_TYPE ENCUMBRANCE_TYPE
,TABLE1.FUND_STATUS FUND_STATUS
,TABLE1.JE_CATEGORY_NAME JE_CATEGORY_NAME
,TABLE1.JE_SOURCE_NAME JE_SOURCE_NAME
,TABLE1.GL_BATCH_NAME GL_BATCH_NAME
,TABLE1.GL_DEFAULT_EFFECTIVE_DATE GL_DEFAULT_EFFECTIVE_DATE
,TABLE1.GL_BATCH_STATUS GL_BATCH_STATUS
,TABLE1.POSTED_DATE POSTED_DATE
,TABLE1.GL_JE_NAME GL_JE_NAME
,TABLE1.GL_DOC_SEQUENCE_NAME GL_DOC_SEQUENCE_NAME
,TABLE1.GL_DOC_SEQUENCE_VALUE GL_DOC_SEQUENCE_VALUE
,TABLE1.GL_LINE_NUMBER GL_LINE_NUMBER
,TABLE1.EVENT_ID EVENT_ID
,TABLE1.EVENT_DATE EVENT_DATE
,TABLE1.EVENT_NUMBER EVENT_NUMBER
,TABLE1.EVENT_CLASS_CODE EVENT_CLASS_CODE
,TABLE1.EVENT_CLASS_NAME EVENT_CLASS_NAME
,TABLE1.EVENT_TYPE_CODE EVENT_TYPE_CODE
,TABLE1.EVENT_TYPE_NAME EVENT_TYPE_NAME
,TABLE1.LINE_NUMBER LINE_NUMBER
,TABLE1.ACCOUNTING_CLASS_CODE ACCOUNTING_CLASS_CODE
,TABLE1.ACCOUNTING_CLASS_NAME ACCOUNTING_CLASS_NAME
,TABLE1.LINE_DESCRIPTION LINE_DESCRIPTION
,TABLE1.CODE_COMBINATION_ID CODE_COMBINATION_ID
,TABLE1.ACCOUNTING_CODE_COMBINATION ACCOUNTING_CODE_COMBINATION
,TABLE1.CODE_COMBINATION_DESCRIPTION CODE_COMBINATION_DESCRIPTION
,TABLE1.CONTROL_ACCOUNT_FLAG CONTROL_ACCOUNT_FLAG
,TABLE1.ENTERED_CURRENCY ENTERED_CURRENCY
,TABLE1.CONVERSION_RATE CONVERSION_RATE
,TABLE1.CONVERSION_RATE_DATE CONVERSION_RATE_DATE
,TABLE1.CONVERSION_RATE_TYPE_CODE CONVERSION_RATE_TYPE_CODE
,TABLE1.CONVERSION_RATE_TYPE CONVERSION_RATE_TYPE
,TABLE1.ENTERED_DR ENTERED_DR
,TABLE1.ENTERED_CR ENTERED_CR
,TABLE1.UNROUNDED_ACCOUNTED_DR UNROUNDED_ACCOUNTED_DR
,TABLE1.UNROUNDED_ACCOUNTED_CR UNROUNDED_ACCOUNTED_CR
,TABLE1.ACCOUNTED_DR ACCOUNTED_DR
,TABLE1.ACCOUNTED_CR ACCOUNTED_CR
,TABLE1.STATISTICAL_AMOUNT STATISTICAL_AMOUNT
,TABLE1.RECONCILIATION_REFERENCE RECONCILIATION_REFERENCE
,TABLE1.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY
,TABLE1.ATTRIBUTE1 ATTRIBUTE1
,TABLE1.ATTRIBUTE2 ATTRIBUTE2
,TABLE1.ATTRIBUTE3 ATTRIBUTE3
,TABLE1.ATTRIBUTE4 ATTRIBUTE4
,TABLE1.ATTRIBUTE5 ATTRIBUTE5
,TABLE1.ATTRIBUTE6 ATTRIBUTE6
,TABLE1.ATTRIBUTE7 ATTRIBUTE7
,TABLE1.ATTRIBUTE8 ATTRIBUTE8
,TABLE1.ATTRIBUTE9 ATTRIBUTE9
,TABLE1.ATTRIBUTE10 ATTRIBUTE10
,TABLE1.PARTY_TYPE_CODE PARTY_TYPE_CODE
,TABLE1.PARTY_TYPE PARTY_TYPE';