[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
gcck.concatenated_segments ACCOUNT
,$gl_balance_cols$ GL_BALANCE
,tbg.code_combination_id CODE_COMBINATION_ID
,tbg.balancing_segment_value BALANCING_SEGMENT_VALUE
,tbg.natural_account_segment_value NATURAL_ACCOUNT_SEGMENT_VALUE
,tbg.cost_center_segment_value COST_CENTER_SEGMENT_VALUE
,tbg.management_segment_value MANAGEMENT_SEGMENT_VALUE
,tbg.intercompany_segment_value INTERCOMPANY_SEGMENT_VALUE
,tbg.ledger_id LEDGER_ID
,tbg.ledger_name LEDGER_NAME
,tbg.ledger_short_name LEDGER_SHORT_NAME
,tbg.ledger_currency_code LEDGER_CURRENCY_CODE
,tbg.third_party_name THIRD_PARTY_NAME
,tbg.third_party_number THIRD_PARTY_NUMBER
,tbg.third_party_type_code THIRD_PARTY_TYPE_CODE
,tbg.third_party_type THIRD_PARTY_TYPE
,tbg.third_party_site_name THIRD_PARTY_SITE_NAME
,tbg.source_application_id SOURCE_TRX_APPLICATION_ID
,tbg.source_entity_id SOURCE_ENTITY_ID
,app.application_name SOURCE_TRX_APPLICATION_NAME
,ett.name SOURCE_TRX_TYPE
,tbg.transaction_number SOURCE_TRX_NUMBER
,to_char(tbg.gl_date,''YYYY-MM-DD'') SOURCE_TRX_GL_DATE
,tbg.trx_currency_code SOURCE_TRX_CURR
,tbg.entered_unrounded_orig_amount SRC_ENTERED_UNROUNDED_ORIG_AMT
,tbg.entered_unrounded_rem_amount SRC_ENTERED_UNROUNDED_REM_AMT
,tbg.entered_rounded_orig_amount SRC_ENTERED_ROUNDED_ORIG_AMT
,tbg.entered_rounded_rem_amount SRC_ENTERED_ROUNDED_REM_AMT
,tbg.acctd_unrounded_orig_amount SRC_ACCTD_UNROUNDED_ORIG_AMT
,tbg.acctd_unrounded_rem_amount SRC_ACCTD_UNROUNDED_REM_AMT
,tbg.acctd_rounded_orig_amount SRC_ACCTD_ROUNDED_ORIG_AMT
,tbg.acctd_rounded_rem_amount SRC_ACCTD_ROUNDED_REM_AMT
,tbg.user_trx_identifier_name_1 USER_TRX_IDENTIFIER_NAME_1
,tbg.user_trx_identifier_name_2 USER_TRX_IDENTIFIER_NAME_2
,tbg.user_trx_identifier_name_3 USER_TRX_IDENTIFIER_NAME_3
,tbg.user_trx_identifier_name_4 USER_TRX_IDENTIFIER_NAME_4
,tbg.user_trx_identifier_name_5 USER_TRX_IDENTIFIER_NAME_5
,tbg.user_trx_identifier_name_6 USER_TRX_IDENTIFIER_NAME_6
,tbg.user_trx_identifier_name_7 USER_TRX_IDENTIFIER_NAME_7
,tbg.user_trx_identifier_name_8 USER_TRX_IDENTIFIER_NAME_8
,tbg.user_trx_identifier_name_9 USER_TRX_IDENTIFIER_NAME_9
,tbg.user_trx_identifier_name_10 USER_TRX_IDENTIFIER_NAME_10
,tbg.user_trx_identifier_value_1 USER_TRX_IDENTIFIER_VALUE_1
,tbg.user_trx_identifier_value_2 USER_TRX_IDENTIFIER_VALUE_2
,tbg.user_trx_identifier_value_3 USER_TRX_IDENTIFIER_VALUE_3
,tbg.user_trx_identifier_value_4 USER_TRX_IDENTIFIER_VALUE_4
,tbg.user_trx_identifier_value_5 USER_TRX_IDENTIFIER_VALUE_5
,tbg.user_trx_identifier_value_6 USER_TRX_IDENTIFIER_VALUE_6
,tbg.user_trx_identifier_value_7 USER_TRX_IDENTIFIER_VALUE_7
,tbg.user_trx_identifier_value_8 USER_TRX_IDENTIFIER_VALUE_8
,tbg.user_trx_identifier_value_9 USER_TRX_IDENTIFIER_VALUE_9
,tbg.user_trx_identifier_value_10 USER_TRX_IDENTIFIER_VALUE_10
$seg_desc_cols$
FROM (SELECT xtb.definition_code
,xtb.ledger_id
,xtb.ledger_name
,xtb.ledger_short_name
,xtb.ledger_currency_code
,xtb.record_type_code
,xtb.source_entity_id
,xtb.source_application_id
,xtb.source_entity_code
,xtb.applied_to_entity_id
,xtb.applied_to_application_id
,xtb.applied_to_entity_code
,xtb.event_class_code
,xtb.transaction_number
,xtb.code_combination_id
,xtb.gl_date
,xtb.trx_currency_code
,xtb.entered_unrounded_orig_amount
,xtb.entered_unrounded_rem_amount
,xtb.entered_rounded_orig_amount
,xtb.entered_rounded_rem_amount
,xtb.acctd_unrounded_orig_amount
,xtb.acctd_unrounded_rem_amount
,xtb.acctd_rounded_orig_amount
,xtb.acctd_rounded_rem_amount
,xtb.third_party_name
,xtb.third_party_number
,xtb.third_party_type_code
,xtb.third_party_type
,xtb.third_party_site_name
,xtb.third_party_account_number
,xtb.balancing_segment_value
,xtb.natural_account_segment_value
,xtb.cost_center_segment_value
,xtb.intercompany_segment_value
,xtb.management_segment_value
,xtb.user_trx_identifier_name_1
,xtb.user_trx_identifier_value_1
,xtb.user_trx_identifier_name_2
,xtb.user_trx_identifier_value_2
,xtb.user_trx_identifier_name_3
,xtb.user_trx_identifier_value_3
,xtb.user_trx_identifier_name_4
,xtb.user_trx_identifier_value_4
,xtb.user_trx_identifier_name_5
,xtb.user_trx_identifier_value_5
,xtb.user_trx_identifier_name_6
,xtb.user_trx_identifier_value_6
,xtb.user_trx_identifier_name_7
,xtb.user_trx_identifier_value_7
,xtb.user_trx_identifier_name_8
,xtb.user_trx_identifier_value_8
,xtb.user_trx_identifier_name_9
,xtb.user_trx_identifier_value_9
,xtb.user_trx_identifier_name_10
,xtb.user_trx_identifier_value_10
,sum(xtb.acctd_unrounded_rem_amount)
OVER(PARTITION BY definition_code,source_entity_id,code_combination_id)balances
FROM xla_trial_balances_gt xtb) tbg -- Bug 5932159
,fnd_application_vl app
,xla_entity_types_vl ett
,gl_code_combinations_kfv gcck
,gl_balances gb
$seg_desc_from$
WHERE tbg.record_type_code = ''SOURCE''
AND tbg.source_entity_code = ett.entity_code
AND tbg.source_application_id = ett.application_id
AND tbg.source_application_id = app.application_id
AND tbg.code_combination_id = gcck.code_combination_id
AND tbg.acctd_unrounded_rem_amount <> 0
AND tbg.balances <> 0 -- Bug 5932159
$gl_balance_join$
$seg_desc_join$
';
SELECT
gcck.concatenated_segments ACCOUNT
,$gl_balance_cols$ GL_BALANCE
,tbg.code_combination_id CODE_COMBINATION_ID
,tbg.balancing_segment_value BALANCING_SEGMENT_VALUE
,tbg.natural_account_segment_value NATURAL_ACCOUNT_SEGMENT_VALUE
,tbg.cost_center_segment_value COST_CENTER_SEGMENT_VALUE
,tbg.management_segment_value MANAGEMENT_SEGMENT_VALUE
,tbg.intercompany_segment_value INTERCOMPANY_SEGMENT_VALUE
,tbg.ledger_id LEDGER_ID
,tbg.ledger_name LEDGER_NAME
,tbg.ledger_short_name LEDGER_SHORT_NAME
,tbg.ledger_currency_code LEDGER_CURRENCY_CODE
,tbg.third_party_name THIRD_PARTY_NAME
,tbg.third_party_number THIRD_PARTY_NUMBER
,tbg.third_party_type_code THIRD_PARTY_TYPE_CODE
,tbg.third_party_type THIRD_PARTY_TYPE
,tbg.third_party_site_name THIRD_PARTY_SITE_NAME
,tbg.source_application_id SOURCE_TRX_APPLICATION_ID
,tbg.source_entity_id SOURCE_ENTITY_ID
,app.application_name SOURCE_TRX_APPLICATION_NAME
,''$write_off$'' SOURCE_TRX_TYPE
,tbg.transaction_number SOURCE_TRX_NUMBER
,to_char(tbg.gl_date,''YYYY-MM-DD'') SOURCE_TRX_GL_DATE
,tbg.trx_currency_code SOURCE_TRX_CURR
,tbg.entered_unrounded_orig_amount SRC_ENTERED_UNROUNDED_ORIG_AMT
,tbg.entered_unrounded_rem_amount SRC_ENTERED_UNROUNDED_REM_AMT
,tbg.entered_rounded_orig_amount SRC_ENTERED_ROUNDED_ORIG_AMT
,tbg.entered_rounded_rem_amount SRC_ENTERED_ROUNDED_REM_AMT
,tbg.acctd_unrounded_orig_amount SRC_ACCTD_UNROUNDED_ORIG_AMT
,tbg.acctd_unrounded_rem_amount SRC_ACCTD_UNROUNDED_REM_AMT
,tbg.acctd_rounded_orig_amount SRC_ACCTD_ROUNDED_ORIG_AMT
,tbg.acctd_rounded_rem_amount SRC_ACCTD_ROUNDED_REM_AMT
,tbg.user_trx_identifier_name_1 USER_TRX_IDENTIFIER_NAME_1
,tbg.user_trx_identifier_name_2 USER_TRX_IDENTIFIER_NAME_2
,tbg.user_trx_identifier_name_3 USER_TRX_IDENTIFIER_NAME_3
,tbg.user_trx_identifier_name_4 USER_TRX_IDENTIFIER_NAME_4
,tbg.user_trx_identifier_name_5 USER_TRX_IDENTIFIER_NAME_5
,tbg.user_trx_identifier_name_6 USER_TRX_IDENTIFIER_NAME_6
,tbg.user_trx_identifier_name_7 USER_TRX_IDENTIFIER_NAME_7
,tbg.user_trx_identifier_name_8 USER_TRX_IDENTIFIER_NAME_8
,tbg.user_trx_identifier_name_9 USER_TRX_IDENTIFIER_NAME_9
,tbg.user_trx_identifier_name_10 USER_TRX_IDENTIFIER_NAME_10
,tbg.user_trx_identifier_value_1 USER_TRX_IDENTIFIER_VALUE_1
,tbg.user_trx_identifier_value_2 USER_TRX_IDENTIFIER_VALUE_2
,tbg.user_trx_identifier_value_3 USER_TRX_IDENTIFIER_VALUE_3
,tbg.user_trx_identifier_value_4 USER_TRX_IDENTIFIER_VALUE_4
,tbg.user_trx_identifier_value_5 USER_TRX_IDENTIFIER_VALUE_5
,tbg.user_trx_identifier_value_6 USER_TRX_IDENTIFIER_VALUE_6
,tbg.user_trx_identifier_value_7 USER_TRX_IDENTIFIER_VALUE_7
,tbg.user_trx_identifier_value_8 USER_TRX_IDENTIFIER_VALUE_8
,tbg.user_trx_identifier_value_9 USER_TRX_IDENTIFIER_VALUE_9
,tbg.user_trx_identifier_value_10 USER_TRX_IDENTIFIER_VALUE_10
$seg_desc_cols$
FROM xla_trial_balances_gt tbg
,fnd_application_vl app
,gl_code_combinations_kfv gcck
,gl_balances gb
$seg_desc_from$
WHERE tbg.record_type_code = ''SOURCE''
AND tbg.source_application_id = app.application_id
AND tbg.code_combination_id = gcck.code_combination_id
AND tbg.acctd_rounded_rem_amount <> 0
AND tbg.acctd_unrounded_rem_amount = 0
$gl_balance_join$
$seg_desc_join$
';
SELECT
tbg.third_party_name THIRD_PARTY_NAME
,tbg.third_party_number THIRD_PARTY_NUMBER
,tbg.third_party_type_code THIRD_PARTY_TYPE_CODE
,tbg.third_party_type THIRD_PARTY_TYPE
,tbg.third_party_site_name THIRD_PARTY_SITE_NAME
,tbg.applied_to_entity_id APPLIED_TO_ENTITY_ID
,tbg.source_application_id APPLIED_TRX_APPLICATION_ID
,app.application_name APPLIED_TRX_APPLICATION_NAME
,ett.name APPLIED_TRX_TYPE
,tbg.transaction_number APPLIED_TRX_NUMBER
,tbg.gl_date APPLIED_TRX_GL_DATE
,tbg.trx_currency_code APPLIED_TRX_CURR
,tbg.entered_unrounded_orig_amount APPLIED_ENTERED_UNROUNDED_AMT
,tbg.entered_rounded_orig_amount APPLIED_ENTERED_ROUNDED_AMT
,tbg.acctd_unrounded_orig_amount APPLIED_ACCTD_UNROUNDED_AMT
,tbg.acctd_rounded_orig_amount APPLIED_ACCTD_ROUNDED_AMT
,tbg.user_trx_identifier_name_1 APPLIED_TRX_IDENTIFIER_NAME_1
,tbg.user_trx_identifier_name_2 APPLIED_TRX_IDENTIFIER_NAME_2
,tbg.user_trx_identifier_name_3 APPLIED_TRX_IDENTIFIER_NAME_3
,tbg.user_trx_identifier_name_4 APPLIED_TRX_IDENTIFIER_NAME_4
,tbg.user_trx_identifier_name_5 APPLIED_TRX_IDENTIFIER_NAME_5
,tbg.user_trx_identifier_name_6 APPLIED_TRX_IDENTIFIER_NAME_6
,tbg.user_trx_identifier_name_7 APPLIED_TRX_IDENTIFIER_NAME_7
,tbg.user_trx_identifier_name_8 APPLIED_TRX_IDENTIFIER_NAME_8
,tbg.user_trx_identifier_name_9 APPLIED_TRX_IDENTIFIER_NAME_9
,tbg.user_trx_identifier_name_10 APPLIED_TRX_IDENTIFIER_NAME_10
,tbg.user_trx_identifier_value_1 APPLIED_TRX_IDENTIFIER_VAL_1
,tbg.user_trx_identifier_value_2 APPLIED_TRX_IDENTIFIER_VAL_2
,tbg.user_trx_identifier_value_3 APPLIED_TRX_IDENTIFIER_VAL_3
,tbg.user_trx_identifier_value_4 APPLIED_TRX_IDENTIFIER_VAL_4
,tbg.user_trx_identifier_value_5 APPLIED_TRX_IDENTIFIER_VAL_5
,tbg.user_trx_identifier_value_6 APPLIED_TRX_IDENTIFIER_VAL_6
,tbg.user_trx_identifier_value_7 APPLIED_TRX_IDENTIFIER_VAL_7
,tbg.user_trx_identifier_value_8 APPLIED_TRX_IDENTIFIER_VAL_8
,tbg.user_trx_identifier_value_9 APPLIED_TRX_IDENTIFIER_VAL_9
,tbg.user_trx_identifier_value_10 APPLIED_TRX_IDENTIFIER_VAL_10
FROM xla_trial_balances_gt tbg
,fnd_application_vl app
,xla_entity_types_vl ett
WHERE tbg.source_entity_code = ett.entity_code
AND tbg.source_application_id = ett.application_id
AND tbg.source_application_id = app.application_id
AND tbg.record_type_code = ''APPLIED''
AND tbg.applied_to_entity_id = :SOURCE_ENTITY_ID
';*/
SELECT/*+ index(tbg XLA_TRIAL_BALANCES_GT_N1)*/
tbg.third_party_name THIRD_PARTY_NAME
,tbg.third_party_number THIRD_PARTY_NUMBER
,tbg.third_party_type_code THIRD_PARTY_TYPE_CODE
,tbg.third_party_type THIRD_PARTY_TYPE
,tbg.third_party_site_name THIRD_PARTY_SITE_NAME
,tbg.applied_to_entity_id APPLIED_TO_ENTITY_ID
,tbg.source_application_id APPLIED_TRX_APPLICATION_ID
,app.application_name APPLIED_TRX_APPLICATION_NAME
,ett.name APPLIED_TRX_TYPE
,tbg.transaction_number APPLIED_TRX_NUMBER
,tbg.gl_date APPLIED_TRX_GL_DATE
,tbg.trx_currency_code APPLIED_TRX_CURR
,tbg.entered_unrounded_orig_amount APPLIED_ENTERED_UNROUNDED_AMT
,tbg.entered_rounded_orig_amount APPLIED_ENTERED_ROUNDED_AMT
,tbg.acctd_unrounded_orig_amount APPLIED_ACCTD_UNROUNDED_AMT
,tbg.acctd_rounded_orig_amount APPLIED_ACCTD_ROUNDED_AMT
,tbg.user_trx_identifier_name_1 APPLIED_TRX_IDENTIFIER_NAME_1
,tbg.user_trx_identifier_name_2 APPLIED_TRX_IDENTIFIER_NAME_2
,tbg.user_trx_identifier_name_3 APPLIED_TRX_IDENTIFIER_NAME_3
,tbg.user_trx_identifier_name_4 APPLIED_TRX_IDENTIFIER_NAME_4
,tbg.user_trx_identifier_name_5 APPLIED_TRX_IDENTIFIER_NAME_5
,tbg.user_trx_identifier_name_6 APPLIED_TRX_IDENTIFIER_NAME_6
,tbg.user_trx_identifier_name_7 APPLIED_TRX_IDENTIFIER_NAME_7
,tbg.user_trx_identifier_name_8 APPLIED_TRX_IDENTIFIER_NAME_8
,tbg.user_trx_identifier_name_9 APPLIED_TRX_IDENTIFIER_NAME_9
,tbg.user_trx_identifier_name_10 APPLIED_TRX_IDENTIFIER_NAME_10
,tbg.user_trx_identifier_value_1 APPLIED_TRX_IDENTIFIER_VAL_1
,tbg.user_trx_identifier_value_2 APPLIED_TRX_IDENTIFIER_VAL_2
,tbg.user_trx_identifier_value_3 APPLIED_TRX_IDENTIFIER_VAL_3
,tbg.user_trx_identifier_value_4 APPLIED_TRX_IDENTIFIER_VAL_4
,tbg.user_trx_identifier_value_5 APPLIED_TRX_IDENTIFIER_VAL_5
,tbg.user_trx_identifier_value_6 APPLIED_TRX_IDENTIFIER_VAL_6
,tbg.user_trx_identifier_value_7 APPLIED_TRX_IDENTIFIER_VAL_7
,tbg.user_trx_identifier_value_8 APPLIED_TRX_IDENTIFIER_VAL_8
,tbg.user_trx_identifier_value_9 APPLIED_TRX_IDENTIFIER_VAL_9
,tbg.user_trx_identifier_value_10 APPLIED_TRX_IDENTIFIER_VAL_10
FROM xla_trial_balances_gt tbg
,fnd_application_vl app
,xla_entity_types_vl ett
WHERE tbg.source_entity_code = ett.entity_code
AND tbg.source_application_id = ett.application_id
AND tbg.source_application_id = app.application_id
AND tbg.record_type_code = ''APPLIED''
AND tbg.code_combination_id = :CODE_COMBINATION_ID
AND tbg.applied_to_entity_id = :SOURCE_ENTITY_ID
';
SELECT
gcck.concatenated_segments ACCOUNT
,$gl_balance_cols$ GL_BALANCE
,tb.code_combination_id CODE_COMBINATION_ID
,tb.balancing_segment_value BALANCING_SEGMENT_VALUE
,tb.natural_account_segment_value NATURAL_ACCOUNT_SEGMENT_VALUE
,tb.cost_center_segment_value COST_CENTER_SEGMENT_VALUE
,tb.management_segment_value MANAGEMENT_SEGMENT_VALUE
,tb.intercompany_segment_value INTERCOMPANY_SEGMENT_VALUE
,$ledger_cols$
,NULL THIRD_PARTY_NAME
,NULL THIRD_PARTY_NUMBER
,NULL THIRD_PARTY_TYPE_CODE
,NULL THIRD_PARTY_TYPE
,NULL THIRD_PARTY_SITE_NAME
,tb.source_application_id SOURCE_TRX_APPLICATION_ID
,tb.source_entity_id SOURCE_ENTITY_ID
,app.application_name SOURCE_TRX_APPLICATION_NAME
,''$initial_balance$'' SOURCE_TRX_TYPE
,NULL SOURCE_TRX_NUMBER
,to_char(tb.gl_date,''YYYY-MM-DD'') SOURCE_TRX_GL_DATE
,tb.trx_currency_code SOURCE_TRX_CURR
,$amount_cols$
,NULL USER_TRX_IDENTIFIER_NAME_1
,NULL USER_TRX_IDENTIFIER_NAME_2
,NULL USER_TRX_IDENTIFIER_NAME_3
,NULL USER_TRX_IDENTIFIER_NAME_4
,NULL USER_TRX_IDENTIFIER_NAME_5
,NULL USER_TRX_IDENTIFIER_NAME_6
,NULL USER_TRX_IDENTIFIER_NAME_7
,NULL USER_TRX_IDENTIFIER_NAME_8
,NULL USER_TRX_IDENTIFIER_NAME_9
,NULL USER_TRX_IDENTIFIER_NAME_10
,NULL USER_TRX_IDENTIFIER_VALUE_1
,NULL USER_TRX_IDENTIFIER_VALUE_2
,NULL USER_TRX_IDENTIFIER_VALUE_3
,NULL USER_TRX_IDENTIFIER_VALUE_4
,NULL USER_TRX_IDENTIFIER_VALUE_5
,NULL USER_TRX_IDENTIFIER_VALUE_6
,NULL USER_TRX_IDENTIFIER_VALUE_7
,NULL USER_TRX_IDENTIFIER_VALUE_8
,NULL USER_TRX_IDENTIFIER_VALUE_9
,NULL USER_TRX_IDENTIFIER_VALUE_10
$seg_desc_cols$
FROM xla_trial_balances tb
,fnd_application_vl app
,gl_code_combinations_kfv gcck
,gl_balances gb
$seg_desc_from$
WHERE tb.definition_code = ''$definition_code$''
AND tb.record_type_code = ''SOURCE''
AND tb.source_entity_id = -1
AND tb.source_application_id = app.application_id
AND tb.code_combination_id = gcck.code_combination_id
AND tb.gl_date >= NVL(''$p_start_date$'',tb.gl_date)
AND tb.gl_date <= NVL(''$p_as_of_date$'',tb.gl_date + 1)
$gl_balance_join$
$seg_desc_join$
';
C_INSERT_GT_STATEMENT CONSTANT VARCHAR2(32000) := '
INSERT INTO xla_trial_balances_gt
(definition_code
,event_class_code
,ledger_id
,ledger_name
,ledger_short_name
,ledger_currency_code
,record_type_code
,source_application_id
,source_entity_id
,source_entity_code
,transaction_number
,code_combination_id
,gl_date
,entered_unrounded_orig_amount
,entered_rounded_orig_amount
,acctd_unrounded_orig_amount
,acctd_rounded_orig_amount
,entered_unrounded_rem_amount
,entered_rounded_rem_amount
,acctd_unrounded_rem_amount
,acctd_rounded_rem_amount
,third_party_name
,third_party_number
,third_party_type_code
,third_party_type
,third_party_site_name
,third_party_account_number
,balancing_segment_value
,natural_account_segment_value
,cost_center_segment_value
,intercompany_segment_value
,management_segment_value
,trx_currency_code
,applied_to_entity_id
,applied_to_application_id
,applied_to_entity_code
,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)';
C_INSERT_GT_SELECT CONSTANT VARCHAR2(32000) := '
SELECT /*+ NO_REWRITE leading(cc) */
tb.definition_code definition_code
,tb.event_class_code event_class_code
,tb.ledger_id ledger_id
,gld.name ledger_name
,gld.short_name ledger_short_name
,gld.currency_code ledger_currency_code
,tb.record_type_code record_type_code
,ent.application_id source_application_id
,ent.entity_id source_entity_id
,ent.entity_code source_entity_code
,ent.transaction_number transaction_number
,tb.code_combination_id code_combination_id
,tb.gl_date gl_date
,tb.entered_unrounded_orig_amount entered_unrounded_orig_amount
,tb.entered_rounded_orig_amount entered_rounded_orig_amount
,tb.acctd_unrounded_orig_amount acctd_unrounded_orig_amount
,tb.acctd_rounded_orig_amount acctd_rounded_orig_amount
,DECODE(
ltb.applied_to_entity_id
,NULL
,tb.entered_unrounded_orig_amount
,tb.entered_unrounded_orig_amount
- NVL(ltb.entd_unrounded_appl_to_amount,0))
entered_unrounded_rem_amount
,DECODE(
ltb.applied_to_entity_id
,NULL
,tb.entered_rounded_orig_amount
,tb.entered_rounded_orig_amount
- NVL(ltb.entd_rounded_appl_to_amount,0))
entered_rounded_rem_amount
,DECODE(
ltb.applied_to_entity_id
,NULL
,tb.acctd_unrounded_orig_amount
,tb.acctd_unrounded_orig_amount
- NVL(ltb.acctd_unrounded_appl_to_amount,0))
acctd_unrounded_rem_amount
,DECODE(
ltb.applied_to_entity_id
,NULL
,tb.acctd_rounded_orig_amount
,tb.acctd_rounded_orig_amount
- NVL(ltb.acctd_rounded_appl_to_amount,0))
acctd_rounded_rem_amount
$party_col$
,tb.balancing_segment_value balancing_segment_value
,tb.natural_account_segment_value natural_account_segment_value
,tb.cost_center_segment_value cost_center_segment_value
,tb.intercompany_segment_value intercompany_segment_value
,tb.management_segment_value management_segment_value
,trx_currency_code trx_currency_code
,tea.entity_id applied_to_entity_id
,tea.application_id applied_to_application_id
,tea.entity_code applied_to_entity_code
--Bug 7118890
--$user_trx_id_cols$
,NULL user_trx_identifier_name_1
,NULL user_trx_identifier_value_1
,NULL user_trx_identifier_name_2
,NULL user_trx_identifier_value_2
,NULL user_trx_identifier_name_3
,NULL user_trx_identifier_value_3
,NULL user_trx_identifier_name_4
,NULL user_trx_identifier_value_4
,NULL user_trx_identifier_name_5
,NULL user_trx_identifier_value_5
,NULL user_trx_identifier_name_6
,NULL user_trx_identifier_value_6
,NULL user_trx_identifier_name_7
,NULL user_trx_identifier_value_7
,NULL user_trx_identifier_name_8
,NULL user_trx_identifier_value_8
,NULL user_trx_identifier_name_9
,NULL user_trx_identifier_value_9
,NULL user_trx_identifier_name_10
,NULL user_trx_identifier_value_10
FROM
xla_transaction_entities ent
,xla_transaction_entities tea
,gl_ledgers gld
$party_tab$
$user_trx_id_from$
$account_tab$
--
-- Beginning of the inline view
--
,(SELECT /*+ INDEX ( xtb1 xla_trial_balances_N2 ) no_expand */ xtb1.definition_code
,xtb1.event_class_code
,xtb1.ledger_id
,xtb1.record_type_code
,xtb1.code_combination_id
,max(xtb1.gl_date) gl_date
$tb_amount_col$
,xtb1.balancing_segment_value
,xtb1.natural_account_segment_value
,xtb1.cost_center_segment_value
,xtb1.intercompany_segment_value
,xtb1.management_segment_value
,xtb1.source_entity_id
,xtb1.source_application_id
,xtb1.applied_to_entity_id
,xtb1.applied_to_application_id
,xtb1.party_type_code
,xtb1.party_id
,xtb1.party_site_id
,xtb1.trx_currency_code
FROM xla_trial_balances xtb1
$account_tab$
WHERE xtb1.definition_code = :1
AND NVL(xtb1.party_id,-99) = NVL(:2,NVL(xtb1.party_id,-99))
AND xtb1.gl_date >= :3 -- GL Date From is required
AND xtb1.gl_date <= NVL(:4,TO_DATE(''9999-12-31'',''YYYY-MM-DD''))
$account_range_xtb1$
GROUP BY xtb1.definition_code
,xtb1.event_class_code
,xtb1.ledger_id
,xtb1.record_type_code
,xtb1.code_combination_id
,xtb1.balancing_segment_value
,xtb1.natural_account_segment_value
,xtb1.cost_center_segment_value
,xtb1.intercompany_segment_value
,xtb1.management_segment_value
,xtb1.source_entity_id
,xtb1.source_application_id
,xtb1.applied_to_entity_id
,xtb1.applied_to_application_id
,xtb1.party_type_code
,xtb1.party_id
,xtb1.party_site_id
,xtb1.trx_currency_code
) tb
,(SELECT /*+ INDEX ( xtb xla_trial_balances_N2 ) no_expand */ xtb.applied_to_entity_id applied_to_entity_id
,xtb.applied_to_application_id
,xtb.code_combination_id code_combination_id
$amount_col$
FROM xla_trial_balances xtb
$account_tab$
WHERE xtb.definition_code = :5
AND NVL(xtb.party_id,-99) = NVL(:6,NVL(xtb.party_id,-99))
AND xtb.gl_date >= :7
AND xtb.gl_date <= NVL(:8,TO_DATE(''9999-12-31'',''YYYY-MM-DD''))
$account_range_xtb$
GROUP BY xtb.applied_to_entity_id
,xtb.applied_to_application_id
,xtb.code_combination_id
) ltb
--
-- End of the inline view
--
WHERE tb.source_entity_id = ent.entity_id
AND tb.source_application_id = ent.application_id
AND tb.applied_to_entity_id = tea.entity_id (+)
AND tb.applied_to_application_id = tea.application_id (+)
AND ltb.applied_to_entity_id (+) = tb.source_entity_id
AND ltb.code_combination_id (+) = tb.code_combination_id
-- Commented out for GSI 7118890
--AND tb.event_class_code = :9
AND tb.ledger_id = gld.ledger_id
$party_where$
$user_trx_id_join$
$appl_where$
$account_range$
';
C_INSERT_GT_SELECT_APPLD_ONLY CONSTANT VARCHAR2(32000) := '
SELECT /*+ NO_REWRITE leading(cc) */
tb.definition_code definition_code
,''DUMMY'' event_class_code
,tb.ledger_id ledger_id
,gld.name ledger_name
,gld.short_name ledger_short_name
,gld.currency_code ledger_currency_code
,''SOURCE'' record_type_code -- to display as source trx
,tb.source_application_id source_application_id
,- tb.source_entity_id source_entity_id -- to avoid net out w/payment(APPLIED) in GT
,tea.entity_code source_entity_code
,tea.transaction_number transaction_number -- display original trx number
,tb.code_combination_id code_combination_id
,tb.gl_date gl_date
,0 entered_unrounded_orig_amount
,0 entered_rounded_orig_amount
,0 acctd_unrounded_orig_amount
,0 acctd_rounded_orig_amount
,- NVL(tb.entd_unrounded_appl_to_amount,0) entered_unrounded_rem_amount
,- NVL(tb.entd_rounded_appl_to_amount,0) entered_rounded_rem_amount
,- NVL(tb.acctd_unrounded_appl_to_amount,0) acctd_unrounded_rem_amount
,- NVL(tb.acctd_rounded_appl_to_amount,0) acctd_rounded_rem_amount
$party_col$
,tb.balancing_segment_value balancing_segment_value
,tb.natural_account_segment_value natural_account_segment_value
,tb.cost_center_segment_value cost_center_segment_value
,tb.intercompany_segment_value intercompany_segment_value
,tb.management_segment_value management_segment_value
,tb.trx_currency_code trx_currency_code
,tea.entity_id applied_to_entity_id
,tea.application_id applied_to_application_id
,tea.entity_code applied_to_entity_code
-- $user_trx_id_cols$
,NULL user_trx_identifier_name_1
,NULL user_trx_identifier_value_1
,NULL user_trx_identifier_name_2
,NULL user_trx_identifier_value_2
,NULL user_trx_identifier_name_3
,NULL user_trx_identifier_value_3
,NULL user_trx_identifier_name_4
,NULL user_trx_identifier_value_4
,NULL user_trx_identifier_name_5
,NULL user_trx_identifier_value_5
,NULL user_trx_identifier_name_6
,NULL user_trx_identifier_value_6
,NULL user_trx_identifier_name_7
,NULL user_trx_identifier_value_7
,NULL user_trx_identifier_name_8
,NULL user_trx_identifier_value_8
,NULL user_trx_identifier_name_9
,NULL user_trx_identifier_value_9
,NULL user_trx_identifier_name_10
,NULL user_trx_identifier_value_10
FROM
xla_transaction_entities tea
,gl_ledgers gld
$party_tab$
-- $user_trx_id_from$
$account_tab$
--
-- Beginning of the inline view
--
,(SELECT /*+ INDEX ( xtb xla_trial_balances_N2 ) no_expand */
xtb.definition_code definition_code
,xtb.ledger_id ledger_id
,xtb.source_application_id source_application_id
,xtb.source_entity_id source_entity_id
,xtb.applied_to_entity_id applied_to_entity_id
,xtb.applied_to_application_id applied_to_application_id
,max(xtb.gl_date) gl_date
,xtb.code_combination_id code_combination_id
,xtb.party_type_code party_type_code
,xtb.party_id party_id
,max(xtb.party_site_id) party_site_id
,xtb.balancing_segment_value balancing_segment_value
,xtb.natural_account_segment_value natural_account_segment_value
,xtb.cost_center_segment_value cost_center_segment_value
,xtb.intercompany_segment_value intercompany_segment_value
,xtb.management_segment_value management_segment_value
,max(xtb.trx_currency_code) trx_currency_code
$amount_col$
FROM xla_trial_balances xtb
$account_tab$
WHERE xtb.definition_code = :1
AND NVL(xtb.party_id,-99) = NVL(:2,NVL(xtb.party_id,-99))
AND xtb.gl_date >= :3
AND xtb.gl_date <= NVL(:4,TO_DATE(''9999-12-31'',''YYYY-MM-DD''))
AND (EXISTS
(SELECT /*+ INDEX ( xtb1 xla_trial_balances_N2 ) no_expand */ xtb1.definition_code
FROM xla_trial_balances xtb1
WHERE xtb1.definition_code = :5
AND NVL(xtb1.party_id,-99) = NVL(:6,NVL(xtb1.party_id,-99))
-- AND xtb1.gl_date <= :7 -- commented for bug:7376639
AND xtb1.gl_date < :7 -- added for bug:7376639
AND xtb.applied_to_entity_id = xtb1.source_entity_id
AND xtb.code_combination_id = xtb1.code_combination_id
)
OR
EXISTS
(SELECT /*+ INDEX ( xtb1 xla_trial_balances_N2 ) no_expand */ xtb1.definition_code
FROM xla_trial_balances xtb1
WHERE xtb1.definition_code = :8
AND NVL(xtb1.party_id,-99) = NVL(:9,NVL(xtb1.party_id,-99))
-- AND xtb1.gl_date >= NVL(:10,TO_DATE(''9999-12-31'',''YYYY-MM-DD'')) -- commented for bug:7376639
AND xtb1.gl_date > NVL(:10,TO_DATE(''9999-12-31'',''YYYY-MM-DD'')) -- added for bug:7376639
AND xtb.applied_to_entity_id = xtb1.source_entity_id
AND xtb.code_combination_id = xtb1.code_combination_id
)
)
$account_range_xtb$
GROUP BY xtb.definition_code
,xtb.ledger_id
,xtb.source_application_id
,xtb.source_entity_id
,xtb.applied_to_entity_id
,xtb.applied_to_application_id
,xtb.code_combination_id
,xtb.balancing_segment_value
,xtb.natural_account_segment_value
,xtb.cost_center_segment_value
,xtb.intercompany_segment_value
,xtb.management_segment_value
,xtb.party_type_code
,xtb.party_id
) tb
--
-- End of the inline view
--
WHERE tb.applied_to_entity_id = tea.entity_id (+)
AND tb.applied_to_application_id = tea.application_id (+)
AND tb.ledger_id = gld.ledger_id
$party_where$
-- $user_trx_id_join$
$appl_where$
$account_range_tb$
';
SELECT gp.period_name
INTO l_period_name
FROM gl_ledgers gl
,gl_periods gp
WHERE gl.ledger_id = p_ledger_id
AND gp.period_set_name = gl.period_set_name
AND gp.period_type = gl.accounted_period_type
AND NVL(TRUNC(l_as_of_date),TRUNC(sysdate)) BETWEEN gp.start_date AND gp.end_date
AND NVL(gp.adjustment_period_flag,'N')='N';
(p_select_sql IN VARCHAR2
,p_ledger_id IN NUMBER
,p_account_balance_code IN VARCHAR2
,p_balance_side_code IN VARCHAR2
,p_upg_flag IN VARCHAR2) RETURN VARCHAR2
IS
l_log_module VARCHAR2(240);
l_select_sql VARCHAR2(32000);
l_select_sql := p_select_sql;
l_select_sql := REPLACE(l_select_sql,'$gl_balance_cols$',l_balance_cols);
l_select_sql := REPLACE(l_select_sql,'$gl_balance_join$',l_balance_join);
RETURN l_select_sql;
| Replace party related string in C_INSERT_GT_SELECT. |
| |
+======================================================================*/
FUNCTION replace_party_string
(p_party_id IN NUMBER
,p_party_type_code IN VARCHAR2 --
,p_insert_sql IN VARCHAR2) RETURN VARCHAR2
IS
l_log_module VARCHAR2(240);
l_insert_gt_sql VARCHAR2(32000);
l_insert_gt_sql := p_insert_sql;
SELECT xlc.meaning
,xls.meaning
INTO l_cust_meaning
,l_supp_meaning
FROM xla_lookups xlc, xla_lookups xls
WHERE xlc.lookup_type = 'XLA_PARTY_TYPE'
AND xlc.lookup_code = 'C'
AND xls.lookup_type = 'XLA_PARTY_TYPE'
AND xls.lookup_code = 'S';
l_insert_gt_sql := REPLACE (l_insert_gt_sql
,'$party_col$'
,l_party_column_cust);
l_insert_gt_sql := REPLACE (l_insert_gt_sql
,'$party_tab$'
,C_PARTY_CUST_TABLE);
l_insert_gt_sql := REPLACE (l_insert_gt_sql
,'$party_where$'
,C_PARTY_CUST_WHERE);
l_insert_gt_sql := REPLACE (l_insert_gt_sql
,'$party_col$'
,l_party_column_supp);
l_insert_gt_sql := REPLACE (l_insert_gt_sql
,'$party_tab$'
,C_PARTY_SUPP_TABLE);
l_insert_gt_sql := REPLACE (l_insert_gt_sql
,'$party_where$'
,C_PARTY_SUPP_WHERE);
RETURN l_insert_gt_sql;
SELECT user_je_source_name
INTO p_journal_source_dsp
FROM gl_je_sources
WHERE je_source_name = p_journal_source;
SELECT NAME
INTO p_report_definition_dsp
FROM xla_tb_definitions_vl
WHERE definition_code = p_definition_code;
SELECT meaning
INTO p_show_trx_detail_dsp
FROM xla_lookups
WHERE lookup_code = p_show_trx_detail_flag
AND lookup_type = 'XLA_YES_NO';
SELECT meaning
INTO p_incl_write_off_dsp
FROM xla_lookups
WHERE lookup_code = p_incl_write_off_flag
AND lookup_type = 'XLA_YES_NO';
SELECT hzp.party_name
INTO p_third_party_name
FROM hz_parties hzp
,hz_cust_accounts hca
WHERE hzp.party_id = hca.party_id
AND hca.cust_account_id = l_party_id;
SELECT vendor_name
INTO p_third_party_name
FROM ap_suppliers
WHERE vendor_id = l_party_id;
SELECT meaning
INTO p_acct_balance_dsp
FROM xla_lookups
WHERE lookup_type = 'XLA_TB_ACCT_BALANCE'
AND lookup_code = p_acct_balance;
l_insert_gt_sql VARCHAR2(32000);
l_insert_gt_cust_sql VARCHAR2(32000);
l_insert_gt_supp_sql VARCHAR2(32000);
l_insert_gt_appld_sql VARCHAR2(32000);
l_insert VARCHAR2(32000);
l_select_string VARCHAR2(4000);
l_insert_gt_sql := C_INSERT_GT_SELECT;
l_insert_gt_appld_sql := C_INSERT_GT_SELECT_APPLD_ONLY;
l_insert_gt_sql := REPLACE (
l_insert_gt_sql
,'$account_range$'
,' AND cc.code_combination_id = tb.code_combination_id AND '||
l_flex_range_where);
l_insert_gt_sql := REPLACE (
l_insert_gt_sql
,'$account_range_xtb1$'
,' AND cc.code_combination_id = xtb1.code_combination_id AND '||
l_flex_range_where);
l_insert_gt_sql := REPLACE (
l_insert_gt_sql
,'$account_range_xtb$'
,' AND cc.code_combination_id = xtb.code_combination_id AND '||
l_flex_range_where);
l_insert_gt_sql := REPLACE(l_insert_gt_sql
,'$account_tab$'
,' ,gl_code_combinations cc ');
l_insert_gt_appld_sql := REPLACE (
l_insert_gt_appld_sql
,'$account_range_xtb$'
,' AND cc.code_combination_id = xtb.code_combination_id AND '||
l_flex_range_where);
l_insert_gt_appld_sql := REPLACE (
l_insert_gt_appld_sql
,'$account_range_tb$'
,' AND cc.code_combination_id = tb.code_combination_id AND '||
l_flex_range_where);
l_insert_gt_appld_sql := REPLACE(l_insert_gt_appld_sql
,'$account_tab$'
,' ,gl_code_combinations cc ');
l_insert_gt_sql := REPLACE(l_insert_gt_sql,'$account_range$', '');
l_insert_gt_sql := REPLACE(l_insert_gt_sql,'$account_range_xtb1$', '');
l_insert_gt_sql := REPLACE(l_insert_gt_sql,'$account_range_xtb$', '');
l_insert_gt_sql := REPLACE(l_insert_gt_sql,'$account_tab$','');
l_insert_gt_appld_sql := REPLACE(l_insert_gt_appld_sql, '$account_range_tb$', '');
l_insert_gt_appld_sql := REPLACE(l_insert_gt_appld_sql, '$account_range_xtb$', '');
l_insert_gt_appld_sql := REPLACE(l_insert_gt_appld_sql,'$account_tab$','');
(p_msg => 'After replace ledger col, l_insert_gl_sql ----'
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
l_insert_gt_sql := REPLACE (l_insert_gt_sql
,'$amount_col$'
,C_CR_APPLIED_AMT_COL);
l_insert_gt_sql := REPLACE (l_insert_gt_sql
,'$tb_amount_col$'
,C_TB_CR_AMOUNT_COLUMN);
l_insert_gt_appld_sql := REPLACE (l_insert_gt_appld_sql
,'$amount_col$'
,C_CR_APPLIED_AMT_COL);
l_insert_gt_sql := REPLACE (l_insert_gt_sql
,'$amount_col$'
,C_DR_APPLIED_AMT_COL);
l_insert_gt_sql := REPLACE (l_insert_gt_sql
,'$tb_amount_col$'
,C_TB_DR_AMOUNT_COLUMN);
l_insert_gt_appld_sql := REPLACE (l_insert_gt_appld_sql
,'$amount_col$'
,C_DR_APPLIED_AMT_COL);
(p_msg => 'After replace amount col, l_insert_gl_sql ----'
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
l_insert_gt_sql := l_insert_gt_sql ||' AND ent.valuation_method = '''
|| p_security_info.valuation_method ||'''';
l_insert_gt_sql := l_insert_gt_sql ||' AND ent.security_id_int_1 = '
|| p_security_info.security_id_int_1;
l_insert_gt_sql := l_insert_gt_sql ||' AND ent.security_id_char_1 = '''
|| p_security_info.security_id_char_1 ||'''';
l_insert_gt_sql := replace_party_string
(p_party_id => l_party_id
,p_party_type_code => l_party_type_code
,p_insert_sql => l_insert_gt_sql);
l_insert_gt_sql := replace_party_string
(p_party_id => l_party_id
,p_party_type_code => l_party_type_code
,p_insert_sql => l_insert_gt_sql);
l_insert_gt_appld_sql := replace_party_string
(p_party_id => l_party_id
,p_party_type_code => l_party_type_code
,p_insert_sql => l_insert_gt_appld_sql);
l_insert_gt_cust_sql := l_insert_gt_sql;
l_insert_gt_supp_sql := l_insert_gt_sql;
l_insert_gt_cust_sql := replace_party_string
(p_party_id => l_party_id
,p_party_type_code => 'C'
,p_insert_sql => l_insert_gt_cust_sql);
l_insert_gt_supp_sql := replace_party_string
(p_party_id => l_party_id
,p_party_type_code => 'S'
,p_insert_sql => l_insert_gt_supp_sql);
l_insert_gt_appld_sql := replace_party_string
(p_party_id => l_party_id
,p_party_type_code => 'S'
,p_insert_sql => l_insert_gt_appld_sql);
l_insert_gt_sql := l_insert_gt_supp_sql;
l_insert_gt_sql := l_insert_gt_cust_sql
|| ' UNION ALL '
|| l_insert_gt_supp_sql;
(p_msg => 'After replace third party, l_insert_gl_sql ----'
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
SELECT application_id
INTO l_application_id
FROM xla_subledgers
WHERE je_source_name = p_journal_source;
l_insert_gt_sql := REPLACE
(l_insert_gt_sql
,'$appl_where$'
,'');
l_insert_gt_appld_sql := REPLACE
(l_insert_gt_appld_sql
,'$appl_where$'
,'');
l_insert_gt_sql := REPLACE
(l_insert_gt_sql
,'$appl_where$'
,' and ent.application_id = '||
l_application_id ||
' and tea.application_id (+)= '||
l_application_id ||'');
l_insert_gt_appld_sql := REPLACE
(l_insert_gt_appld_sql
,'$appl_where$'
,' and tea.application_id (+)= '||
l_application_id ||'');
l_insert_gt_sql := REPLACE(l_insert_gt_sql
,'$user_trx_id_cols$'
,l_select_string);
l_insert_gt_sql := REPLACE(l_insert_gt_sql
,'$user_trx_id_from$'
,l_from_string);
l_insert_gt_sql := REPLACE(l_insert_gt_sql
,'$user_trx_id_join$'
,l_where_string);
l_insert_gt_sql := l_insert_gt_sql
|| ' UNION ALL '
|| l_insert_gt_appld_sql;
l_insert := C_INSERT_GT_STATEMENT||' ('||l_insert_gt_sql||')';
dump_text(p_text => l_insert);
print_logfile('>> Insert Into GT Table ');
print_logfile(l_insert);
EXECUTE IMMEDIATE l_insert
USING p_definition_code
,l_party_id
,trunc(p_start_date)
,trunc(p_as_of_date)
,p_definition_code
,l_party_id
,trunc(p_start_date)
,trunc(p_as_of_date)
-- for applied transactions when orig invoices out of the date range
,p_definition_code
,l_party_id
,trunc(p_start_date)
,trunc(p_as_of_date)
,p_definition_code
,l_party_id
,trunc(p_start_date)
,p_definition_code
,l_party_id
,trunc(p_as_of_date);
EXECUTE IMMEDIATE l_insert
USING p_definition_code
,l_party_id
,trunc(p_start_date)
,trunc(p_as_of_date)
,p_definition_code
,l_party_id
,trunc(p_start_date)
,trunc(p_as_of_date)
,l_event_class_code;
EXECUTE IMMEDIATE l_insert
USING p_definition_code
,l_party_id
,trunc(p_start_date)
,trunc(p_as_of_date)
,p_definition_code
,l_party_id
,trunc(p_start_date)
,trunc(p_as_of_date)
,l_event_class_code
,p_definition_code
,l_party_id
,trunc(p_start_date)
,trunc(p_as_of_date)
,p_definition_code
,l_party_id
,trunc(p_start_date)
,trunc(p_as_of_date)
,l_event_class_code;
print_logfile('<< Insert Into GT Table ');
print_logfile('# of rows inserted into GT table '
|| ' - ' || l_event_class_code
|| ' : ' || SQL%ROWCOUNT);
(p_msg => '# of rows inserted: ' || SQL%ROWCOUNT
,p_level => C_LEVEL_STATEMENT
,p_module => l_log_module);
| get_select_clause |
| |
| Returns the sql for the event class |
| |
+======================================================================*/
FUNCTION get_upg_select_clause
RETURN VARCHAR2 IS
l_upg_sql VARCHAR2(32000);
(p_select_sql => l_upg_sql
,p_ledger_id => g_defn_info.ledger_id
,p_account_balance_code => p_acct_balance -- Global Variable
,p_balance_side_code => g_defn_info.balance_side_code
,p_upg_flag => 'Y');
SELECT meaning
INTO l_init_balance_dsp
FROM xla_lookups
WHERE lookup_type = 'XLA_TB_TRX_TYPE';
END get_upg_select_clause;
| get_select_clause |
| |
| Returns the sql for the event class |
| |
+======================================================================*/
FUNCTION get_select_clause
(p_defn_info IN xla_tb_data_manager_pvt.r_definition_info
,p_show_trx_detail_flag IN VARCHAR2
,p_incl_write_off_flag IN VARCHAR2
,p_account_balance_code IN VARCHAR2)
RETURN BOOLEAN IS
l_log_module VARCHAR2(240);
l_log_module := C_DEFAULT_MODULE||'.get_select_clause';
(p_msg => 'BEGIN of get_select_clause'
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
SELECT meaning
INTO l_write_off
FROM xla_lookups
WHERE lookup_type = 'XLA_ACCOUNTING_CLASS'
AND lookup_code = 'WRITE_OFF';
l_app_source_sql := 'Select 1 from dual where 1=2';
(p_select_sql => l_source_sql
,p_ledger_id => p_defn_info.ledger_id
,p_account_balance_code => p_account_balance_code
,p_balance_side_code => p_defn_info.balance_side_code
,p_upg_flag => 'N');
(p_msg => 'END of get_select_clause'
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
(p_location => 'xla_tb_report_pvt.get_select_clause');
END get_select_clause;
SELECT application_id
INTO l_application_id
FROM xla_subledgers
WHERE je_source_name = l_journal_source;
print_logfile('>> get_select_clause');
l_return := get_select_clause
(p_defn_info => g_defn_info
,p_show_trx_detail_flag => p_show_trx_detail_flag
,p_incl_write_off_flag => p_incl_write_off_flag
,p_account_balance_code => p_acct_balance);
print_logfile('<< get_select_clause');