[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
'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';
l_select_str VARCHAR2(4000);
l_insert_query VARCHAR2(4000);
SELECT primary_ledger_id
FROM gl_ledger_relationships
WHERE target_ledger_id = l_ledger_id
AND relationship_type_code = 'SUBLEDGER'
AND target_ledger_category_code = 'ALC'
AND application_id =101;
SELECT application_id
INTO g_je_source_application_id
FROM xla_subledgers
WHERE je_source_name = p_je_source_name;
SELECT ''''||meaning||'''' INTO G_POSTING_FLAG
FROM FND_LOOKUPS
WHERE LOOKUP_TYPE='JGZZ_SRS_SLR_POST_STAT'
AND lookup_code = 'Y';
SELECT object_type_code, USERENV('LANG')
INTO l_object_type, l_lang
FROM gl_ledgers
WHERE ledger_id = p_ledger_id;
select application_id
bulk COLLECT into l_responsibility_ids
from xla_subledgers
WHERE control_account_type_code = 'Y' or control_account_type_code = P_PARTY_TYPE;
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;
INSERT INTO xla_report_balances_gt
(ledger_id
,ledger_short_name
,ledger_description
,ledger_name
,ledger_currency
,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_activity_code
,le_sub_activity_code
,le_contact_name
,le_contact_phone_number
,party_type_code
,party_id
,party_number
,party_name
,party_site_id
,party_site_number
,party_site_tax_regs_number
,party_type_taxpayer_id
,party_tax_registration_number
,party_address_1
,party_address_2
,party_address_3
,party_address_4
,party_city
,party_zip_code
,party_state
,party_province
,party_country
,party_county
,party_site_name
,party_site_address_line_1
,party_site_address_line_2
,party_site_address_line_3
,party_site_address_line_4
,party_site_city
,party_site_zip_code
,party_site_state
,party_site_province
,party_site_country
,party_site_county
,application_id
,application_name
,je_source_name
,period_year
,period_number
,period_name
,period_start_date
,period_end_date
,begin_balance_dr
,begin_balance_cr
,period_net_dr
,period_net_cr
,begin_draft_balance_dr
,begin_draft_balance_cr
,period_draft_net_dr
,period_draft_net_cr
,code_combination_id
,accounting_code_combination
,code_combination_description
,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)
(
SELECT 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
$legal_entity_columns$
,TABLE1.PARTY_TYPE_CODE PARTY_TYPE_CODE
,TABLE1.PARTY_ID PARTY_ID
,TABLE1.PARTY_NUMBER PARTY_NUMBER
,TABLE1.PARTY_NAME PARTY_NAME
,TABLE1.PARTY_SITE_ID PARTY_SITE_ID
,TABLE1.PARTY_SITE_NUMBER PARTY_SITE_NUMBER
,TABLE1.PARTY_SITE_TAX_REGS_NUMBER PARTY_SITE_TAX_REGS_NUMBER
,TABLE1.PARTY_TYPE_TAXPAYER_ID PARTY_TYPE_TAXPAYER_ID
,TABLE1.PARTY_TAX_REGISTRATION_NUMBER PARTY_TAX_REGISTRATION_NUMBER
,TABLE1.PARTY_ADDRESS_1 PARTY_ADDRESS_1
,TABLE1.PARTY_ADDRESS_2 PARTY_ADDRESS_2
,TABLE1.PARTY_ADDRESS_3 PARTY_ADDRESS_3
,TABLE1.PARTY_ADDRESS_4 PARTY_ADDRESS_4
,TABLE1.PARTY_CITY PARTY_CITY
,TABLE1.PARTY_ZIP_CODE PARTY_ZIP_CODE
,TABLE1.PARTY_STATE PARTY_STATE
,TABLE1.PARTY_PROVINCE PARTY_PROVINCE
,TABLE1.PARTY_COUNTRY PARTY_COUNTRY
,TABLE1.PARTY_COUNTY PARTY_COUNTY
,TABLE1.PARTY_SITE_NAME PARTY_SITE_NAME
,TABLE1.PARTY_SITE_ADDRESS_LINE_1 PARTY_SITE_ADDRESS_LINE_1
,TABLE1.PARTY_SITE_ADDRESS_LINE_2 PARTY_SITE_ADDRESS_LINE_2
,TABLE1.PARTY_SITE_ADDRESS_LINE_3 PARTY_SITE_ADDRESS_LINE_3
,TABLE1.PARTY_SITE_ADDRESS_LINE_4 PARTY_SITE_ADDRESS_LINE_4
,TABLE1.PARTY_SITE_CITY PARTY_SITE_CITY
,TABLE1.PARTY_SITE_ZIP_CODE PARTY_SITE_ZIP_CODE
,TABLE1.PARTY_SITE_STATE PARTY_SITE_STATE
,TABLE1.PARTY_SITE_PROVINCE PARTY_SITE_PROVINCE
,TABLE1.PARTY_SITE_COUNTRY PARTY_SITE_COUNTRY
,TABLE1.PARTY_SITE_COUNTY PARTY_SITE_COUNTY
,TABLE1.APPLICATION_ID APPLICATION_ID
,TABLE1.APPLICATION_NAME APPLICATION_NAME
,TABLE1.JE_SOURCE_NAME JE_SOURCE_NAME
,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.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.BEGIN_DRAFT_BALANCE_DR BEGIN_DRAFT_BALANCE_DR
,TABLE1.BEGIN_DRAFT_BALANCE_CR BEGIN_DRAFT_BALANCE_CR
,TABLE1.PERIOD_DRAFT_NET_DR PERIOD_DRAFT_NET_DR
,TABLE1.PERIOD_DRAFT_NET_CR PERIOD_DRAFT_NET_CR
,TABLE1.CODE_COMBINATION_ID CODE_COMBINATION_ID
,TABLE1.ACCOUNTING_CODE_COMBINATION ACCOUNTING_CODE_COMBINATION
,TABLE1.CODE_COMBINATION_DESCRIPTION CODE_COMBINATION_DESCRIPTION
,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
FROM
(SELECT gll.ledger_id LEDGER_ID
,gll.short_name LEDGER_SHORT_NAME
,gll.description LEDGER_DESCRIPTION
,gll.NAME LEDGER_NAME
,gll.currency_code LEDGER_CURRENCY
,xcb.party_type_code PARTY_TYPE_CODE
$party_col$
,hzp.jgzz_fiscal_code PARTY_TYPE_TAXPAYER_ID
,hzp.tax_reference PARTY_TAX_REGISTRATION_NUMBER
,hzp.address1 PARTY_ADDRESS_1
,hzp.address2 PARTY_ADDRESS_2
,hzp.address3 PARTY_ADDRESS_3
,hzp.address4 PARTY_ADDRESS_4
,hzp.city PARTY_CITY
,hzp.postal_code PARTY_ZIP_CODE
,hzp.state PARTY_STATE
,hzp.province PARTY_PROVINCE
,hzp.country PARTY_COUNTRY
,hzp.county PARTY_COUNTY
,hps.party_site_name PARTY_SITE_NAME
,hzl.address1 PARTY_SITE_ADDRESS_LINE_1
,hzl.address2 PARTY_SITE_ADDRESS_LINE_2
,hzl.address3 PARTY_SITE_ADDRESS_LINE_3
,hzl.address4 PARTY_SITE_ADDRESS_LINE_4
,hzl.city PARTY_SITE_CITY
,hzl.postal_code PARTY_SITE_ZIP_CODE
,hzl.state PARTY_SITE_STATE
,hzl.province PARTY_SITE_PROVINCE
,hzl.country PARTY_SITE_COUNTRY
,hzl.county PARTY_SITE_COUNTY
,xcb.application_id APPLICATION_ID
,fap.application_name APPLICATION_NAME
,gjst.user_je_source_name JE_SOURCE_NAME
,gls.period_year PERIOD_YEAR
,gls.period_num PERIOD_NUMBER
,xcb.period_name PERIOD_NAME
,trunc(gls.START_DATE) PERIOD_START_DATE
,trunc(gls.end_date) PERIOD_END_DATE
,NVL(xcb.beginning_balance_dr,0) BEGIN_BALANCE_DR
,NVL(xcb.beginning_balance_cr,0) BEGIN_BALANCE_CR
,NVL(xcb.period_balance_dr,0) PERIOD_NET_DR
,NVL(xcb.period_balance_cr,0) PERIOD_NET_CR
,NVL(xcb.draft_beginning_balance_dr,0) BEGIN_DRAFT_BALANCE_DR
,NVL(xcb.draft_beginning_balance_cr,0) BEGIN_DRAFT_BALANCE_CR
,NVL(xcb.period_draft_balance_dr,0) PERIOD_DRAFT_NET_DR
,NVL(xcb.period_draft_balance_cr,0) PERIOD_DRAFT_NET_CR
,xcb.code_combination_id CODE_COMBINATION_ID
,gcck.concatenated_segments ACCOUNTING_CODE_COMBINATION
,xla_report_utility_pkg.get_ccid_desc
(gll.chart_of_accounts_id
,xcb.code_combination_id) CODE_COMBINATION_DESCRIPTION
$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 gl_ledgers gll
,xla_control_balances xcb
,gl_period_statuses gls
,gl_code_combinations_kfv gcck
,hz_parties hzp
,hz_party_sites hps
,hz_locations hzl
,fnd_application_tl fap
,xla_subledgers xls
,gl_je_sources_tl gjst
$party_tab$
$seg_desc_from$
$l_ledger_set_from$
WHERE gls.ledger_id = gll.ledger_id
AND gls.application_id = 101
AND gls.effective_period_num =
(SELECT min(inps.effective_period_num)
FROM xla_control_balances incb
,gl_period_statuses inps
WHERE incb.ledger_id=xcb.ledger_id
AND incb.application_id= xcb.application_id
AND incb.code_combination_id =xcb.code_combination_id
AND incb.party_id = xcb.party_id
AND incb.party_site_id = xcb.party_site_id
AND inps.ledger_id = gll.ledger_id
AND inps.application_id = 101
AND incb.period_name = inps.period_name
AND inps.effective_period_num between :p_start_period_num and :p_end_period_num
)
AND xcb.ledger_id = gll.ledger_id
AND xcb.application_id = :G_JE_SOURCE_APPLICATION_ID
AND xcb.period_name = gls.period_name
AND gcck.code_combination_id = xcb.code_combination_id
AND hzl.location_id(+) = hps.location_id
AND fap.application_id = xcb.application_id
AND fap.LANGUAGE = :P_LANG
AND xls.application_id = xcb.application_id
AND gjst.je_source_name = xls.je_source_name
AND gjst.LANGUAGE = :P_LANG
$other_filter$
$p_party_join$
$seg_desc_join$
$l_ledger_set_where$
$account_range$) TABLE1
$legal_entity_from$
WHERE 1 = 1
$legal_entity_join$
)' ;