[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
,tbg.NON_AP_AMOUNT NON_AP_AMOUNT
,tbg.MANUAL_SLA_AMOUNT MANUAL_SLA_AMOUNT
$seg_desc_cols$
FROM xla_trial_balances_gt tbg
,fnd_application_vl app
,xla_entity_types_vl ett
,gl_code_combinations_kfv gcck
,gl_balances gb
$seg_desc_from$
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.code_combination_id = gcck.code_combination_id
$gl_balance_join$
$seg_desc_join$
';
C_SELECT_NONAP_AMOUNT CONSTANT VARCHAR2(32000) :=
'
WITH xtd AS
(
SELECT /*+ materialize */
DISTINCT p.ledger_id, d.code_combination_id,
p.period_name,
rpad(''x'',500) pad
FROM xla_tb_defn_details d,
xla_tb_definitions_vl vl,
gl_period_statuses p
WHERE d.definition_code = ''$p_definition_code$''
AND vl.definition_code = d.definition_code
AND p.application_id =200
AND p.ledger_id = vl.ledger_id
AND p.start_date >= NVL(:1, p.start_date + 1)
AND p.end_date <= NVL(:2, p.end_date + 1)
AND NVL(p.adjustment_period_flag,''N'')=''N''
)
SELECT /*+ leading(xtd,l,h,gcck) parallel(xtd) pq_distribute(l,broadcast,none)
use_nl(l,gcck,h) parallel(l) parallel(h) parallel(gcck)
*/
l.ledger_id,
l.code_combination_id,
sum(nvl(l.accounted_cr, 0))- sum(nvl(l.accounted_dr,0)) NONAP_AMOUNT
FROM gl_je_headers h
,gl_je_lines l
,gl_code_combinations_kfv gcck
,xtd
WHERE l.code_combination_id = gcck.code_combination_id
AND l.code_combination_id = xtd.code_combination_id
AND l.ledger_id = xtd.ledger_id
AND l.period_name = xtd.period_name
AND h.je_source <> ''Payables''
AND h.je_header_id = l.je_header_id
AND h.ledger_id = l.ledger_id
AND h.actual_flag = ''A''
AND h.status = ''P''
AND l.effective_date BETWEEN NVL(:3, l.effective_date )
AND NVL(:4, l.effective_date + 1 )
GROUP BY l.ledger_id,l.code_combination_id
';
C_SELECT_MANUAL_SLA_AMOUNT CONSTANT VARCHAR2(32000) :=
'
WITH xtd AS
(
SELECT /*+ materialize */ DISTINCT ledger_id, code_combination_id,
rpad(''x'',500) pad
FROM xla_tb_defn_details d,
xla_tb_definitions_vl vl
WHERE d.definition_code = ''$p_definition_code$''
AND vl.definition_code = d.definition_code
) ,
xe AS
(
SELECT xe1.application_id , xe1.event_id
FROM xla_events xe1
WHERE xe1.application_id = 200
AND xe1.process_status_code = ''P''
AND xe1.event_type_code = ''MANUAL''
)
SELECT /*+ ordered use_nl(xe,h,l,xtd,gcck) no_index(l mis_xla_ae_lines_n1) */
l.ledger_id,
l.code_combination_id,
sum(nvl(accounted_cr,0))-sum(nvl(accounted_dr,0)) MANUAL_SLA_AMOUNT
FROM xe,
xla_ae_headers h,
xla_ae_lines l,
xtd ,
gl_code_combinations_kfv gcck
WHERE gcck.code_combination_id = l.code_combination_id
AND h.application_id = 200
AND gcck.code_combination_id = xtd.code_combination_id
AND l.application_id = h.application_id
AND l.ae_header_id = h.ae_header_id
AND h.ledger_id = l.ledger_id
AND h.gl_transfer_status_code=''Y''
AND h.accounting_entry_status_code=''F''
AND h.event_type_code=''MANUAL''
AND h.balance_type_code=''A''
AND h.ledger_id = xtd.ledger_id
AND h.event_id = xe.event_id
AND h.application_id = xe.application_id
AND h.ledger_id = :1
AND h.accounting_date BETWEEN :2 AND :3
GROUP BY l.ledger_id,l.code_combination_id
';
C_SELECT_NONAP_SEGRANGES_AMT CONSTANT VARCHAR2(32000) :=
'WITH xtd AS
(
SELECT /*+ materialize */
DISTINCT gcck.code_combination_id, vl.ledger_id,
rpad(''x'',500) pad
FROM
xla_tb_definitions_vl vl,
xla_tb_def_seg_ranges xsr, -- added for bug#9926320
gl_code_combinations_kfv gcck
WHERE vl.definition_code = ''$p_definition_code$''
AND vl.definition_code = xsr.definition_code -- added for bug#9926320
$gcck_join$
AND EXISTS
( SELECT /*+ no_unnest */ 1
FROM xla_trial_balances xtb
WHERE xtb.code_combination_id = gcck.code_combination_id
AND xtb.definition_code = vl.definition_code
)
)
SELECT /*+ leading(xtd,l,p,h) parallel(xtd) pq_distribute(l,broadcast,none)
use_nl(l,h,p) parallel(p) ,parallel(l) parallel(h)
*/
l.ledger_id,
l.code_combination_id,
sum(nvl(l.accounted_cr, 0))- sum(nvl(l.accounted_dr,0)) NONAP_AMOUNT
FROM gl_je_headers h
,gl_je_lines l
,xtd
,gl_period_statuses p
WHERE l.code_combination_id = xtd.code_combination_id
AND l.ledger_id = xtd.ledger_id
AND l.period_name = p.period_name
AND h.je_source <> ''Payables''
AND h.je_header_id = l.je_header_id
AND h.ledger_id = l.ledger_id
AND h.actual_flag = ''A''
AND h.status = ''P''
AND l.effective_date BETWEEN NVL(:1, l.effective_date )
AND NVL(:2, l.effective_date + 1 )
AND p.application_id =200
AND p.ledger_id = xtd.ledger_id
AND NVL(p.adjustment_period_flag,''N'')=''N''
GROUP BY l.ledger_id,l.code_combination_id
';
C_SELECT_MANUAL_SEGRANGES_AMT CONSTANT VARCHAR2(32000) :=
'
WITH xtd AS
(
SELECT /*+ materialize */
DISTINCT gcck.code_combination_id, vl.ledger_id,
rpad(''x'',500) pad
FROM
xla_tb_definitions_vl vl,
xla_tb_def_seg_ranges xsr, -- added for bug#9926320
gl_code_combinations_kfv gcck
WHERE vl.definition_code = ''$p_definition_code$''
AND vl.definition_code = xsr.definition_code -- added for bug#9926320
$gcck_join$
AND EXISTS
( SELECT /*+ no_unnest */ 1
FROM xla_trial_balances xtb
WHERE xtb.code_combination_id = gcck.code_combination_id
AND xtb.definition_code = vl.definition_code
)
),
xe AS
(
SELECT xe1.application_id , xe1.event_id
FROM xla_events xe1
WHERE xe1.application_id = 200
AND xe1.process_status_code = ''P''
AND xe1.event_type_code = ''MANUAL''
)
SELECT /*+ ordered use_nl(xe,h,l,xtd) no_index(l mis_xla_ae_lines_n1) */
l.ledger_id,
l.code_combination_id,
sum(nvl(accounted_cr,0))-sum(nvl(accounted_dr,0)) MANUAL_SLA_AMOUNT
FROM xe,
xla_ae_headers h,
xla_ae_lines l,
xtd
WHERE h.application_id = 200
AND h.application_id = l.application_id
AND h.ae_header_id = l.ae_header_id
AND h.ledger_id = l.ledger_id
AND h.ledger_id = xtd.ledger_id
AND l.code_combination_id = xtd.code_combination_id
AND h.gl_transfer_status_code=''Y''
AND h.accounting_entry_status_code=''F''
AND h.event_type_code= ''MANUAL''
AND h.balance_type_code=''A''
AND h.ledger_id = xtd.ledger_id
AND h.event_id = xe.event_id
AND h.application_id = xe.application_id
AND h.ledger_id = :1
AND h.accounting_date BETWEEN :2 AND :3
GROUP BY l.ledger_id,l.code_combination_id
';
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
,tbg.NON_AP_AMOUNT NON_AP_AMOUNT
,tbg.MANUAL_SLA_AMOUNT MANUAL_SLA_AMOUNT
$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$
';
C_INSERT_GT_SUMMARY_STATEMENT CONSTANT VARCHAR2(32000) := '
INSERT INTO xla_trial_balances_gt
(definition_code
,ledger_id
,ledger_name
,ledger_short_name
,ledger_currency_code
,record_type_code
,source_application_id
,code_combination_id
,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
,balancing_segment_value
,natural_account_segment_value
,cost_center_segment_value
,intercompany_segment_value
,management_segment_value
,trx_currency_code) ';
C_INSERT_GT_SUMMARY_SELECT CONSTANT VARCHAR2(32000) := '
SELECT
summary_dat.definition_code,
summary_dat.ledger_id,
gl.name,
gl.short_name,
gl.currency_code,
''SUMMARY'',
summary_dat.source_application_id,
summary_dat.code_combination_id,
decode(gl.ledger_category_code,''PRIMARY'',summary_dat.SUM_acctd_unrounded_orig_amt ,0),
decode(gl.ledger_category_code,''PRIMARY'',summary_dat.SUM_acctd_rounded_orig_amt,0),
summary_dat.sum_entd_unrounded_rem_amount,
summary_dat.sum_entd_rounded_rem_amount,
summary_dat.sum_acctd_unrounded_rem_amount,
summary_dat.sum_acctd_rounded_rem_amount,
summary_dat.party_name,
summary_dat.party_id,
summary_dat.balancing_segment_value,
summary_dat.natural_account_segment_value,
summary_dat.cost_center_segment_value,
summary_dat.intercompany_segment_value,
summary_dat.management_segment_value,
gl.currency_code
FROM
(
SELECT
tb.definition_code,
tb.ledger_id,
tb.source_application_id,
tb.code_combination_id,
SUM(tb.entered_unrounded_rem_amount) SUM_ENTD_UNROUNDED_REM_AMOUNT,
SUM(tb.entered_rounded_rem_amount) SUM_entd_rounded_rem_amount ,
SUM(tb.acctd_unrounded_rem_amount) SUM_acctd_unrounded_rem_amount ,
SUM(tb.acctd_rounded_rem_amount) SUM_acctd_rounded_rem_amount ,
SUM(nvl(tiv.base_amount,tiv.invoice_amount)) SUM_acctd_unrounded_orig_amt,
SUM(nvl(tiv.base_amount,tiv.invoice_amount)) SUM_acctd_rounded_orig_amt,
tiv.party_name,
tb.party_id,
tb.balancing_segment_value,
tb.natural_account_segment_value,
tb.cost_center_segment_value,
tb.intercompany_segment_value,
tb.management_segment_value
FROM
AP_SLA_INVOICES_TRANSACTION_V tiv,
xla_transaction_entities xte,
-- inline view
( SELECT /*+ parallel(xtb) leading(xtb) NO_MERGE */ --added hint bug#8409806 bug9133956
xtb.definition_code,
nvl(xtb.applied_to_entity_id,xtb.source_entity_id) entity_id,
xtb.code_combination_id ,
xtb.source_application_id,
SUM (Nvl(xtb.entered_unrounded_cr,0)) - SUM (Nvl(xtb.entered_unrounded_dr,0)) entered_unrounded_rem_amount,
SUM (Nvl(xtb.entered_rounded_cr,0)) - SUM (Nvl(xtb.entered_rounded_dr,0)) entered_rounded_rem_amount,
SUM (Nvl(xtb.acctd_unrounded_cr,0)) - SUM (Nvl(xtb.acctd_unrounded_dr,0)) acctd_unrounded_rem_amount,
SUM (Nvl(xtb.acctd_rounded_cr,0)) - SUM (Nvl(xtb.acctd_rounded_dr,0)) acctd_rounded_rem_amount,
xtb.ledger_id,
xtb.party_id,
xtb.balancing_segment_value,
xtb.natural_account_segment_value,
xtb.cost_center_segment_value,
xtb.intercompany_segment_value,
xtb.management_segment_value
FROM xla_trial_balances xtb
where xtb.definition_code = :1
and xtb.source_application_id=200
and xtb.gl_date between :2 and :3
AND NVL(xtb.party_id,-99) = NVL(:4,NVL(xtb.party_id,-99))
GROUP BY xtb.definition_code,
nvl(xtb.applied_to_entity_id,xtb.source_entity_id) ,
xtb.code_combination_id ,
xtb.source_application_id,
xtb.ledger_id,
xtb.party_id,
xtb.balancing_segment_value,
xtb.natural_account_segment_value,
xtb.cost_center_segment_value,
xtb.intercompany_segment_value,
xtb.management_segment_value
HAVING SUM (Nvl(xtb.acctd_rounded_cr,0)) <> SUM (Nvl(xtb.acctd_rounded_dr,0))
) tb
--end of inline view
$account_tab$
WHERE tb.entity_id=xte.entity_id
AND tb.source_application_id=200
AND xte.entity_code=''AP_INVOICES''
AND xte.application_id=tb.source_application_id
AND nvl(xte.source_id_int_1,-99)=tiv.invoice_id
$security_valuation_join$
$account_range$
GROUP BY
tb.definition_code, tb.ledger_id, tb.source_application_id,
tb.code_combination_id, tiv.party_name,tb.party_id,
tb.balancing_segment_value, tb.natural_account_segment_value,
tb.cost_center_segment_value, tb.intercompany_segment_value,
tb.management_segment_value
) summary_dat ,
gl_ledgers gl
WHERE summary_dat.ledger_id=gl.ledger_id
';
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
,app.application_name SOURCE_TRX_APPLICATION_NAME
,tbg.entered_unrounded_orig_amount SUM_SRC_ENTD_UNROUND_ORG_AMT
,tbg.entered_unrounded_rem_amount SUM_SRC_ENTD_UNROUND_REM_AMT
,tbg.entered_rounded_orig_amount SUM_SRC_ENTD_ROUNDED_ORG_AMT
,tbg.entered_rounded_rem_amount SUM_SRC_ENTD_ROUNDED_REM_AMT
,tbg.acctd_unrounded_orig_amount SUM_SRC_ACCTD_UNROUND_ORG_AMT
,tbg.acctd_unrounded_rem_amount SUM_SRC_ACCTD_UNROUND_REM_AMT
,tbg.acctd_rounded_orig_amount SUM_SRC_ACCTD_ROUNDED_ORG_AMT
,tbg.acctd_rounded_rem_amount SUM_SRC_ACCTD_ROUNDED_REM_AMT
,tbg.NON_AP_AMOUNT NON_AP_AMOUNT
,tbg.MANUAL_SLA_AMOUNT MANUAL_SLA_AMOUNT
$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.source_application_id = app.application_id
AND tbg.code_combination_id = gcck.code_combination_id
$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
,NULL NON_AP_AMOUNT
,NULL MANUAL_SLA_AMOUNT
$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
,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
,balancing_segment_value
,natural_account_segment_value
,cost_center_segment_value
,intercompany_segment_value
,management_segment_value
,applied_to_entity_id
,trx_currency_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
tb.definition_code,
tb.ledger_id,
gl.name,
gl.short_name,
gl.currency_code,
''X'',
tb.source_application_id,
tb.entity_id,
xte.entity_code,
xte.transaction_number,
tb.code_combination_id,
tiv.invoice_date,
--added bug 7359012 original amounts would be displayed only for primary ledger.
decode(gl.ledger_category_code,''PRIMARY'',tiv.invoice_amount,0),
decode(gl.ledger_category_code,''PRIMARY'',tiv.invoice_amount,0),
decode(gl.ledger_category_code,''PRIMARY'',nvl(tiv.base_amount,tiv.invoice_amount),0),
decode(gl.ledger_category_code,''PRIMARY'',nvl(tiv.base_amount,tiv.invoice_amount),0),
--end bug 7359012
tb.entered_unrounded_rem_amount,
tb.entered_rounded_rem_amount,
tb.acctd_unrounded_rem_amount,
tb.acctd_rounded_rem_amount,
tiv.party_name,
tb.party_id,
tb.balancing_segment_value,
tb.natural_account_segment_value,
tb.cost_center_segment_value,
tb.intercompany_segment_value,
tb.management_segment_value,
tb.entity_id,
tiv.invoice_currency_code, --added for bug 8321482 Removed hard-coded USD
''Party Name'' USER_TRX_IDENTIFIER_NAME_1,
TIV.PARTY_NAME USER_TRX_IDENTIFIER_VALUE_1,
''Party Site Name'' USER_TRX_IDENTIFIER_NAME_2,
TIV.PARTY_SITE_NAME USER_TRX_IDENTIFIER_VALUE_2,
''Invoice Number'' USER_TRX_IDENTIFIER_NAME_3,
TIV.INVOICE_NUM USER_TRX_IDENTIFIER_VALUE_3,
''Invoice Amount'' USER_TRX_IDENTIFIER_NAME_4,
to_char(TIV.INVOICE_AMOUNT) USER_TRX_IDENTIFIER_VALUE_4,
--remod ''Invoice Currency'' USER_TRX_IDENTIFIER_NAME_5,
--remod TIV.INVOICE_CURRENCY_CODE USER_TRX_IDENTIFIER_VALUE_5,
''Due Days'' USER_TRX_IDENTIFIER_NAME_5,
TIV.DUE_DAYS USER_TRX_IDENTIFIER_VALUE_5,
''Invoice Ledger Amount'' USER_TRX_IDENTIFIER_NAME_6,
to_char(TIV.BASE_AMOUNT) USER_TRX_IDENTIFIER_VALUE_6,
''Payment Status'' USER_TRX_IDENTIFIER_NAME_7,
tiv.PAYMENT_STATUS USER_TRX_IDENTIFIER_VALUE_7,
''Invoice Date'' USER_TRX_IDENTIFIER_NAME_8,
to_char(TIV.INVOICE_DATE,''YYYY-MM-DD"T"hh:mi:ss'') USER_TRX_IDENTIFIER_VALUE_8,
''Cancelled Date'' USER_TRX_IDENTIFIER_NAME_9,
to_char(TIV.CANCELLED_DATE,''YYYY-MM-DD"T"hh:mi:ss'') USER_TRX_IDENTIFIER_VALUE_9,
''Invoice Description'' USER_TRX_IDENTIFIER_NAME_10,
TIV.DESCRIPTION USER_TRX_IDENTIFIER_VALUE_10
FROM
AP_SLA_INVOICES_TRANSACTION_V tiv,
xla_transaction_entities xte,
gl_ledgers gl,
-- inline view
( SELECT /*+ parallel(xtb) leading(xtb) NO_MERGE */ --added hint bug#8409806 -- leading hint for bug:9165098
/* added NO_MERGE for bug:9473043 */
xtb.definition_code,
nvl(xtb.applied_to_entity_id,xtb.source_entity_id) entity_id,
xtb.code_combination_id ,
xtb.source_application_id,
SUM (Nvl(xtb.entered_unrounded_cr,0)) - SUM (Nvl(xtb.entered_unrounded_dr,0)) entered_unrounded_rem_amount,
SUM (Nvl(xtb.entered_rounded_cr,0)) - SUM (Nvl(xtb.entered_rounded_dr,0)) entered_rounded_rem_amount,
SUM (Nvl(xtb.acctd_unrounded_cr,0)) - SUM (Nvl(xtb.acctd_unrounded_dr,0)) acctd_unrounded_rem_amount,
SUM (Nvl(xtb.acctd_rounded_cr,0)) - SUM (Nvl(xtb.acctd_rounded_dr,0)) acctd_rounded_rem_amount,
xtb.ledger_id,
xtb.party_id,
xtb.balancing_segment_value,
xtb.natural_account_segment_value,
xtb.cost_center_segment_value,
xtb.intercompany_segment_value,
xtb.management_segment_value
FROM xla_trial_balances xtb
where xtb.definition_code = :1
and xtb.source_application_id=200
and xtb.gl_date between :2 and :3
AND NVL(xtb.party_id,-99) = NVL(:4,NVL(xtb.party_id,-99))
GROUP BY xtb.definition_code,
nvl(xtb.applied_to_entity_id,xtb.source_entity_id) ,
xtb.code_combination_id ,
xtb.source_application_id,
xtb.ledger_id,
xtb.party_id,
xtb.balancing_segment_value,
xtb.natural_account_segment_value,
xtb.cost_center_segment_value,
xtb.intercompany_segment_value,
xtb.management_segment_value
HAVING SUM (Nvl(xtb.acctd_rounded_cr,0)) <> SUM (Nvl(xtb.acctd_rounded_dr,0))
) tb
$account_tab$
--end of inline view
where tb.entity_id=xte.entity_id
and tb.source_application_id=200
and xte.entity_code=''AP_INVOICES''
and xte.application_id=tb.source_application_id
--and xte.ledger_id=tb.ledger_id removed join to make report work for reporting/secondary ledger,Bug 7331692
and nvl(xte.source_id_int_1,-99)=tiv.invoice_id
and tb.ledger_id=gl.ledger_id
$account_range$
';
SELECT latest_opened_period_name , gp.start_date
FROM gl_ledgers gl , gl_periods gp
where ledger_id = p_ledger_id
AND gp.period_set_name = gl.period_set_name
AND gp.period_type = gl.accounted_period_type
AND NVL(gp.adjustment_period_flag,'N')='N'
AND gp.period_name = gl.latest_opened_period_name;
SELECT period_name
FROM gl_period_statuses
WHERE application_id =101
AND ledger_id = p_ledger_id
AND NVL(adjustment_period_flag,'N')='N'
AND closing_status = 'O'
AND effective_period_num =
(
SELECT max(effective_period_num)
FROM gl_period_statuses
WHERE application_id = 101
AND ledger_id = p_ledger_id
AND NVL(adjustment_period_flag,'N')='N'
AND closing_status = 'O'
);
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';*/
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'
AND EXISTS
( SELECT 1
FROM gl_balances gb
WHERE gb.ledger_id = gl.ledger_id
AND gb.period_name = gp.period_name
)
;
(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;
SELECT meaning
INTO P_REPORT_MODE_DSP
FROM xla_lookups
WHERE lookup_type = 'XLA_REPORT_LEVEL'
AND lookup_code = NVL(P_REPORT , 'D');
l_insert_gt_sql VARCHAR2(32000);
l_insert_gt_cust_sql VARCHAR2(32000);
l_insert_gt_supp_sql VARCHAR2(32000);
l_select_string VARCHAR2(4000);
SELECT event_class_code
,select_string
,from_string
,where_string
FROM xla_tb_user_trans_views
WHERE definition_code = p_definition_code;
,l_select_string
,l_from_string
,l_where_string;
l_insert_gt_sql := C_INSERT_GT_SUMMARY_STATEMENT || C_INSERT_GT_SUMMARY_SELECT;
l_insert_gt_sql := C_INSERT_GT_STATEMENT || C_INSERT_GT_SELECT;
select trunc(months_between(p_as_of_date, p_start_date))
INTO l_months_between
from dual;
l_insert_gt_sql:= REPLACE(l_insert_gt_sql,'$hint$','+ index(xtb XLA_TRIAL_BALANCES_N1) ');
l_insert_gt_sql:= REPLACE(l_insert_gt_sql,'$hint$','+ parallel(xtb) full(xtb)');
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_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_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
,'$security_valuation_join$'
, l_security_join ) ;
l_insert_gt_sql := REPLACE(l_insert_gt_sql
,'$security_valuation_join$'
,'') ;
l_insert_gt_sql := l_insert_gt_sql || l_security_join ;
(p_msg => 'l_insert_gl_sql: ' || substr(l_insert_gt_sql,1,3500)
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
(p_msg => 'l_insert_gl_sql: ' || substr(l_insert_gt_sql,3501,3500)
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
(p_msg => 'l_insert_gl_sql: ' || substr(l_insert_gt_sql,7001,3500)
,p_level => C_LEVEL_PROCEDURE
,p_module => l_log_module);
EXECUTE IMMEDIATE l_insert_gt_sql
USING p_definition_code
,trunc(p_start_date)
,trunc(p_as_of_date)
,l_party_id;
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;
SELECT application_column_name
FROM fnd_id_flex_segments
WHERE application_id = 101
AND id_flex_code = 'GL#'
AND id_flex_num = x_coa_id
AND enabled_flag = 'Y';
SELECT chart_of_accounts_id
INTO l_coa_id
FROM gl_ledgers
WHERE ledger_id = p_ledger_id;
FOR i IN ( SELECT *
FROM xla_tb_defn_details d
WHERE d.definition_code = p_definition_code
)
LOOP
l_join_gcck := l_join_gcck || ' AND gcck.'|| i.flexfield_segment_code || ' BETWEEN ' ||
'''' || i.segment_value_from || '''' || ' AND ' || '''' || i.segment_value_to || '''';
| 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_select_nonap_amount VARCHAR2(32000);
l_select_manual_sla_amount VARCHAR2(32000);
l_select_nonap_segranges_amt VARCHAR2(32000);
l_select_manual_segranges_amt VARCHAR2(32000);
SELECT code_combination_id
FROM xla_tb_defn_details
WHERE definition_code = p_definition_code;
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');
l_upg_sql := get_upg_select_clause;
l_select_nonap_amount := C_SELECT_NONAP_AMOUNT;
l_select_nonap_amount := REPLACE(l_select_nonap_amount,'$p_definition_code$',p_definition_code);
EXECUTE IMMEDIATE l_select_nonap_amount
BULK COLLECT INTO arr_ledgerid, arr_ccid, arr_non_ap_amount
USING trunc(p_start_date),
trunc(p_as_of_date),
trunc(p_start_date),
trunc(p_as_of_date);
UPDATE xla_trial_balances_gt
SET NON_AP_AMOUNT = arr_non_ap_amount(i)
WHERE code_combination_id = arr_ccid(i)
AND ledger_id = arr_ledgerid(i);
l_select_manual_sla_amount := C_SELECT_MANUAL_SLA_AMOUNT;
l_select_manual_sla_amount := REPLACE(l_select_manual_sla_amount,'$p_definition_code$',p_definition_code);
EXECUTE IMMEDIATE l_select_manual_sla_amount
BULK COLLECT INTO arr_ledgerid, arr_ccid, arr_manual_sla_amount
USING p_defn_info.ledger_id,
trunc(NVL(p_start_date,to_date('01-01-1950','DD-MM-YYYY'))),
trunc(NVL(p_as_of_date,to_date('31-12-9999','DD-MM-YYYY')));
UPDATE xla_trial_balances_gt
SET MANUAL_SLA_AMOUNT = arr_manual_sla_amount(i)
WHERE code_combination_id = arr_ccid(i)
AND ledger_id = arr_ledgerid(i);
l_select_nonap_segranges_amt := C_SELECT_NONAP_SEGRANGES_AMT;
l_select_nonap_segranges_amt := REPLACE(l_select_nonap_segranges_amt,'$p_definition_code$',p_definition_code);
l_select_nonap_segranges_amt := REPLACE(l_select_nonap_segranges_amt,'$gcck_join$',get_gcck_join(p_defn_info.ledger_id));
dump_text(p_text => l_select_nonap_segranges_amt );
EXECUTE IMMEDIATE l_select_nonap_segranges_amt
BULK COLLECT INTO arr_ledgerid, arr_ccid, arr_non_ap_amount
USING trunc(p_start_date),
trunc(p_as_of_date);
UPDATE xla_trial_balances_gt
SET NON_AP_AMOUNT = arr_non_ap_amount(i)
WHERE code_combination_id = arr_ccid(i)
AND ledger_id = arr_ledgerid(i);
l_select_manual_segranges_amt := C_SELECT_MANUAL_SEGRANGES_AMT;
l_select_manual_segranges_amt := REPLACE(l_select_manual_segranges_amt,'$p_definition_code$',p_definition_code);
l_select_manual_segranges_amt := REPLACE(l_select_manual_segranges_amt, '$gcck_join$' , get_gcck_join(p_defn_info.ledger_id));
dump_text(p_text => l_select_manual_segranges_amt );
EXECUTE IMMEDIATE l_select_manual_segranges_amt
BULK COLLECT INTO arr_ledgerid, arr_ccid, arr_manual_sla_amount
USING p_defn_info.ledger_id,
trunc(NVL(p_start_date,to_date('01-01-1950','DD-MM-YYYY'))),
trunc(NVL(p_as_of_date,to_date('31-12-9999','DD-MM-YYYY')));
UPDATE xla_trial_balances_gt
SET MANUAL_SLA_AMOUNT = arr_manual_sla_amount(i)
WHERE code_combination_id = arr_ccid(i)
AND ledger_id = arr_ledgerid(i);
(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');