[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';
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 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 ';
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 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 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 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 gjh.ledger_id = xrb.ledger_id
AND gjh.period_name = xrb.period_name
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);